gistfile1.sql

DROP TABLE IF EXISTS patient_prescription_totals;
CREATE TABLE patient_prescription_totals (
  `id` int(11) NOT NULL auto_increment,
  `patient_id` int(11) NOT NULL,
  `drug_id` int(11) NOT NULL,
  `prescription_date` DATE NOT NULL,
  `daily_consumption` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `patient_id_drug_id_presciption_date` (`patient_id`, `drug_id`, `prescription_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO patient_prescription_totals (patient_id, drug_id, prescription_date, daily_consumption)
  SELECT patient_id, drug_id, DATE(prescription_datetime) as prescription_date, SUM(daily_consumption) AS daily_consumption 
  FROM patient_prescriptions
  GROUP BY patient_id, drug_id, prescription_date;

DROP TABLE IF EXISTS patient_whole_tablets_remaining_and_brought;
CREATE TABLE patient_whole_tablets_remaining_and_brought (
  `id` int(11) NOT NULL auto_increment,
  `patient_id` int(11) NOT NULL,
  `drug_id` int(11) NOT NULL,
  `visit_date` DATE NOT NULL,
  `total_remaining` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `patient_id_drug_id_presciption_date` (`patient_id`, `drug_id`, `visit_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO patient_whole_tablets_remaining_and_brought (patient_id, drug_id, visit_date, total_remaining)
  SELECT patient_id, value_drug, DATE(obs_datetime) as visit_date, value_numeric 
  FROM obs
  WHERE obs.concept_id = 363 AND obs.voided = 0
  GROUP BY patient_id, value_drug, visit_date
  ORDER BY obs_id DESC;


DROP VIEW IF EXISTS patient_dispensations_and_prescriptions;
CREATE VIEW patient_dispensations_and_prescriptions (patient_id, encounter_id, visit_date, drug_id, total_dispensed, total_remaining, daily_consumption) AS
  SELECT encounter.patient_id, 
         encounter.encounter_id, 
         DATE(encounter.encounter_datetime),
         drug.drug_id,
         drug_order.quantity AS total_dispensed,
         whole_tablets_remaining_and_brought.total_remaining AS total_remaining,
         patient_prescription_totals.daily_consumption AS daily_consumption
  FROM encounter
  INNER JOIN orders ON orders.encounter_id = encounter.encounter_id AND orders.voided = 0
  INNER JOIN drug_order ON drug_order.order_id = orders.order_id 
  INNER JOIN drug ON drug_order.drug_inventory_id = drug.drug_id
  INNER JOIN concept_set as arv_drug_concepts ON
    arv_drug_concepts.concept_set = 460 AND
    arv_drug_concepts.concept_id = drug.concept_id
  LEFT JOIN patient_whole_tablets_remaining_and_brought AS whole_tablets_remaining_and_brought ON
    whole_tablets_remaining_and_brought.patient_id = encounter.patient_id AND
    whole_tablets_remaining_and_brought.visit_date = DATE(encounter.encounter_datetime) AND    
    whole_tablets_remaining_and_brought.drug_id = drug.drug_id
  LEFT JOIN patient_prescription_totals ON   
    patient_prescription_totals.drug_id = drug.drug_id AND
    patient_prescription_totals.patient_id = encounter.patient_id AND
    patient_prescription_totals.prescription_date = DATE(encounter.encounter_datetime);
    
DROP TABLE IF EXISTS patient_visit_default_dates;
CREATE TABLE patient_visit_default_dates (
  `id` int(11) NOT NULL auto_increment,
  `patient_id` int(11) NOT NULL,
  `drug_id` int(11) NOT NULL,
  `visit_date` DATE NOT NULL,
  `drugs_run_out_date` DATE NOT NULL,
  `default_date` DATE NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `patient_id_visit_date_default_date` (`patient_id`, `visit_date`, `default_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 28 is the default number of days if we don't know the prescription or total tablets remaining (fallback to MOH paper approach)
DELETE FROM patient_visit_default_dates;
INSERT INTO patient_visit_default_dates (patient_id, drug_id, visit_date, drugs_run_out_date, default_date)
  SELECT patient_id, 
         drug_id, 
         visit_date, 
         ADDDATE(visit_date, INTERVAL IFNULL(((total_remaining + total_dispensed) / daily_consumption), 28) DAY) as drugs_run_out_date,
         ADDDATE(visit_date, INTERVAL IFNULL(((total_remaining + total_dispensed) / daily_consumption), 28) + 56 DAY) as default_date
  FROM patient_dispensations_and_prescriptions;

    
  SELECT * 
  FROM (
    SELECT * 
    FROM patient_visit_default_dates
    GROUP BY patient_id
    ORDER BY visit_date DESC) AS patient_last_visit_default_dates
  WHERE 
  NOT EXISTS (
    SELECT * FROM patient_visit_default_dates AS next_dispensation  
    WHERE next_dispensation.patient_id = patient_last_visit_default_dates.patient_id AND
          next_dispensation.visit_date > patient_last_visit_default_dates.visit_date AND
          next_dispensation.visit_date <= patient_last_visit_default_dates.default_date) AND 
  NOT EXISTS (
    SELECT * FROM obs 
    WHERE obs.concept_id = 28 AND
          obs.patient_id = patient_last_visit_default_dates.patient_id AND
          obs.obs_datetime >= patient_last_visit_default_dates.visit_date AND 
          obs.obs_datetime <= patient_last_visit_default_dates.default_date)
GROUP BY patient_id) t;



-- SELECT count(*) 
-- FROM obs 
-- WHERE obs.concept_id = 28 AND patient_id = 12304 AND obs.obs_datetime > "2008-04-01";


-- ALTER TABLE obs ADD KEY better_obs_datetime (concept_id, patient_id, obs_datetime);