iYBdTzkU

1class AddPrescriptions < ActiveRecord::Migration
2  def self.up
3# Good chance this should be in the fixtures
4ActiveRecord::Base.connection.execute <<EOF
5DROP TABLE IF EXISTS prescription_time_periods;
6EOF
7
8ActiveRecord::Base.connection.execute <<EOF
9CREATE TABLE prescription_time_periods (
10  `id` int(11) NOT NULL auto_increment,
11  `time_period` varchar(255) NOT NULL,
12  `time_period_days` int(11) NOT NULL,
13  PRIMARY KEY  (`id`),
14  KEY `time_period_index` (`time_period`)
15) ENGINE=InnoDB DEFAULT CHARSET=utf8;
16EOF
17
18#This applies the appropriate buffer period
19ActiveRecord::Base.connection.execute <<EOF
20INSERT INTO prescription_time_periods (time_period, time_period_days) VALUES
21  ('2 weeks', 15),
22  ('1 month', 30),
23  ('2 months', 58),
24  ('3 months', 86),
25  ('4 months', 114),
26  ('5 months', 142),
27  ('6 months', 170);
28EOF
29
30ActiveRecord::Base.connection.execute <<EOF
31DROP TABLE IF EXISTS prescription_frequencies;
32EOF
33
34ActiveRecord::Base.connection.execute <<EOF
35CREATE TABLE prescription_frequencies (
36  `id` int(11) NOT NULL auto_increment,
37  `frequency` varchar(255) NOT NULL,
38  `frequency_days` int(11) NOT NULL,
39  PRIMARY KEY  (`id`),
40  KEY `frequency_index` (`frequency`)
41) ENGINE=InnoDB DEFAULT CHARSET=utf8;
42EOF
43
44ActiveRecord::Base.connection.execute <<EOF
45INSERT INTO prescription_frequencies (frequency, frequency_days) VALUES
46  ('Once', 1),
47  ('Morning', 1),
48  ('Evening', 1),
49  ('Weekly', 7);
50EOF
51
52# Eventually prescription time periods should be value_coded
53# Eventually prescription frequencies should be value_coded
54# 375 = Concept "Prescribed dose"
55# 345 = Concept "Presription time period"
56# 363 = Concept "Whole tablets remaining and brought to clinic"
57# 2 = Encounter Type "ART Visit"
58ActiveRecord::Base.connection.execute <<EOF
59DROP VIEW IF EXISTS patient_prescriptions;
60EOF
61
62ActiveRecord::Base.connection.execute <<EOF
63CREATE VIEW patient_prescriptions (patient_id, encounter_id, prescription_datetime, drug_id, frequency, dose_amount, time_period, quantity, daily_consumption) AS
64  SELECT encounter.patient_id, 
65         encounter.encounter_id, 
66         prescribed_dose.obs_datetime, 
67         prescribed_dose.value_drug,
68         prescribed_dose.value_text,
69         prescribed_dose.value_numeric,
70         prescribed_time_period.value_text,
71         (prescribed_dose.value_numeric * (prescription_time_periods.time_period_days / prescription_frequencies.frequency_days)),
72         (prescribed_dose.value_numeric / prescription_frequencies.frequency_days)
73  FROM encounter
74  INNER JOIN obs AS prescribed_dose ON 
75    prescribed_dose.concept_id = 375 AND 
76    prescribed_dose.encounter_id = encounter.encounter_id AND 
77    prescribed_dose.value_drug IS NOT NULL AND 
78    prescribed_dose.voided = 0
79  INNER JOIN obs AS prescribed_time_period ON 
80    prescribed_time_period.concept_id = 345 AND
81    prescribed_time_period.encounter_id = encounter.encounter_id AND 
82    prescribed_time_period.voided = 0
83  INNER JOIN prescription_frequencies ON prescription_frequencies.frequency = prescribed_dose.value_text  
84  INNER JOIN prescription_time_periods ON prescription_time_periods.time_period = prescribed_time_period.value_text  
85  WHERE encounter.encounter_type = 2;
86EOF
87  end
88
89  def self.down
90ActiveRecord::Base.connection.execute <<EOF
91DROP TABLE IF EXISTS prescription_time_periods;
92EOF
93
94ActiveRecord::Base.connection.execute <<EOF
95DROP TABLE IF EXISTS prescription_frequencies;
96EOF
97
98ActiveRecord::Base.connection.execute <<EOF
99DROP VIEW IF EXISTS patient_prescriptions;
100EOF
101  end
102end