7rWsyjd8

1DROP TABLE IF EXISTS patient_prescription_totals;
2CREATE TABLE patient_prescription_totals (
3  `id` int(11) NOT NULL auto_increment,
4  `patient_id` int(11) NOT NULL,
5  `drug_id` int(11) NOT NULL,
6  `prescription_date` DATE NOT NULL,
7  `daily_consumption` int(11) NOT NULL,
8  PRIMARY KEY  (`id`),
9  KEY `patient_id_drug_id_presciption_date` (`patient_id`, `drug_id`, `prescription_date`)
10) ENGINE=InnoDB DEFAULT CHARSET=utf8;
11
12INSERT INTO patient_prescription_totals (patient_id, drug_id, prescription_date, daily_consumption)
13  SELECT patient_id, drug_id, DATE(prescription_datetime) as prescription_date, SUM(daily_consumption) AS daily_consumption 
14  FROM patient_prescriptions
15  GROUP BY patient_id, drug_id, prescription_date;
16
17DROP TABLE IF EXISTS patient_whole_tablets_remaining_and_brought;
18CREATE TABLE patient_whole_tablets_remaining_and_brought (
19  `id` int(11) NOT NULL auto_increment,
20  `patient_id` int(11) NOT NULL,
21  `drug_id` int(11) NOT NULL,
22  `visit_date` DATE NOT NULL,
23  `total_remaining` int(11) NOT NULL,
24  PRIMARY KEY  (`id`),
25  KEY `patient_id_drug_id_presciption_date` (`patient_id`, `drug_id`, `visit_date`)
26) ENGINE=InnoDB DEFAULT CHARSET=utf8;
27
28INSERT INTO patient_whole_tablets_remaining_and_brought (patient_id, drug_id, visit_date, total_remaining)
29  SELECT patient_id, value_drug, DATE(obs_datetime) as visit_date, value_numeric 
30  FROM obs
31  WHERE obs.concept_id = 363 AND obs.voided = 0
32  GROUP BY patient_id, value_drug, visit_date
33  ORDER BY obs_id DESC;
34
35
36DROP VIEW IF EXISTS patient_dispensations_and_prescriptions;
37CREATE VIEW patient_dispensations_and_prescriptions (patient_id, encounter_id, visit_date, drug_id, total_dispensed, total_remaining, daily_consumption) AS
38  SELECT encounter.patient_id, 
39         encounter.encounter_id, 
40         DATE(encounter.encounter_datetime),
41         drug.drug_id,
42         drug_order.quantity AS total_dispensed,
43         whole_tablets_remaining_and_brought.total_remaining AS total_remaining,
44         patient_prescription_totals.daily_consumption AS daily_consumption
45  FROM encounter
46  INNER JOIN orders ON orders.encounter_id = encounter.encounter_id AND orders.voided = 0
47  INNER JOIN drug_order ON drug_order.order_id = orders.order_id 
48  INNER JOIN drug ON drug_order.drug_inventory_id = drug.drug_id
49  INNER JOIN concept_set as arv_drug_concepts ON
50    arv_drug_concepts.concept_set = 460 AND
51    arv_drug_concepts.concept_id = drug.concept_id
52  LEFT JOIN patient_whole_tablets_remaining_and_brought AS whole_tablets_remaining_and_brought ON
53    whole_tablets_remaining_and_brought.patient_id = encounter.patient_id AND
54    whole_tablets_remaining_and_brought.visit_date = DATE(encounter.encounter_datetime) AND    
55    whole_tablets_remaining_and_brought.drug_id = drug.drug_id
56  LEFT JOIN patient_prescription_totals ON   
57    patient_prescription_totals.drug_id = drug.drug_id AND
58    patient_prescription_totals.patient_id = encounter.patient_id AND
59    patient_prescription_totals.prescription_date = DATE(encounter.encounter_datetime);
60    
61DROP TABLE IF EXISTS patient_visit_default_dates;
62CREATE TABLE patient_visit_default_dates (
63  `id` int(11) NOT NULL auto_increment,
64  `patient_id` int(11) NOT NULL,
65  `drug_id` int(11) NOT NULL,
66  `visit_date` DATE NOT NULL,
67  `drugs_run_out_date` DATE NOT NULL,
68  `default_date` DATE NOT NULL,
69  PRIMARY KEY  (`id`),
70  KEY `patient_id_visit_date_default_date` (`patient_id`, `visit_date`, `default_date`)
71) ENGINE=InnoDB DEFAULT CHARSET=utf8;
72
73-- 28 is the default number of days if we don't know the prescription or total tablets remaining (fallback to MOH paper approach)
74DELETE FROM patient_visit_default_dates;
75INSERT INTO patient_visit_default_dates (patient_id, drug_id, visit_date, drugs_run_out_date, default_date)
76  SELECT patient_id, 
77         drug_id, 
78         visit_date, 
79         ADDDATE(visit_date, INTERVAL IFNULL(((total_remaining + total_dispensed) / daily_consumption), 28) DAY) as drugs_run_out_date,
80         ADDDATE(visit_date, INTERVAL IFNULL(((total_remaining + total_dispensed) / daily_consumption), 28) + 56 DAY) as default_date
81  FROM patient_dispensations_and_prescriptions;
82
83    
84  SELECT * 
85  FROM (
86    SELECT * 
87    FROM patient_visit_default_dates
88    GROUP BY patient_id
89    ORDER BY visit_date DESC) AS patient_last_visit_default_dates
90  WHERE 
91  NOT EXISTS (
92    SELECT * FROM patient_visit_default_dates AS next_dispensation  
93    WHERE next_dispensation.patient_id = patient_last_visit_default_dates.patient_id AND
94          next_dispensation.visit_date > patient_last_visit_default_dates.visit_date AND
95          next_dispensation.visit_date <= patient_last_visit_default_dates.default_date) AND 
96  NOT EXISTS (
97    SELECT * FROM obs 
98    WHERE obs.concept_id = 28 AND
99          obs.patient_id = patient_last_visit_default_dates.patient_id AND
100          obs.obs_datetime >= patient_last_visit_default_dates.visit_date AND 
101          obs.obs_datetime <= patient_last_visit_default_dates.default_date)
102GROUP BY patient_id) t;
103
104
105
106-- SELECT count(*) 
107-- FROM obs 
108-- WHERE obs.concept_id = 28 AND patient_id = 12304 AND obs.obs_datetime > "2008-04-01";
109
110
111-- ALTER TABLE obs ADD KEY better_obs_datetime (concept_id, patient_id, obs_datetime);