26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
|
# File 'app/services/malawi_hiv_program_reports/clinic/viral_load_results.rb', line 26
def find_report
start_date = ActiveRecord::Base.connection.quote(@start_date)
end_date = ActiveRecord::Base.connection.quote(@end_date)
ActiveRecord::Base.connection.select_all " SELECT orders.patient_id,\n patient_identifier.identifier AS arv_number,\n person.birthdate AS birthdate,\n disaggregated_age_group(person.birthdate, \#{end_date}) AS age_group,\n person.gender AS gender,\n orders.start_date AS order_date,\n specimen_type.name AS specimen,\n COALESCE(orders.discontinued_date, orders.start_date) AS specimen_drawn_date,\n test_results_obs.obs_datetime AS result_date,\n COALESCE(test_result_measure_obs.value_modifier, '=') AS result_modifier,\n COALESCE(test_result_measure_obs.value_numeric, test_result_measure_obs.value_text) AS result,\n patient_current_regimen(orders.patient_id, orders.start_date) AS current_regimen\n FROM orders\n INNER JOIN concept_name AS specimen_type\n ON specimen_type.concept_id = orders.concept_id\n AND specimen_type.name IN ('Blood', 'DBS (Free drop to DBS card)', 'DBS (Using capillary tube)', 'Plasma')\n AND specimen_type.voided = 0\n LEFT JOIN patient_identifier\n ON patient_identifier.patient_id = orders.patient_id\n AND patient_identifier.voided = 0\n AND patient_identifier.identifier_type IN (\n SELECT patient_identifier_type_id FROM patient_identifier_type WHERE name = 'ARV Number' AND retired = 0\n )\n INNER JOIN person\n ON person.person_id = orders.patient_id\n AND person.voided = 0\n LEFT JOIN (\#{current_occupation_query}) AS a ON a.person_id = orders.patient_id\n /* For each lab order find an HIV Viral Load test */\n INNER JOIN obs AS test_obs\n ON test_obs.order_id = orders.order_id\n AND test_obs.concept_id IN (\n SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)\n WHERE concept_name.name = 'Test type' AND concept.retired = 0 AND concept_name.voided = 0\n )\n AND test_obs.value_coded IN (\n SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)\n WHERE concept_name.name = 'Viral load' AND concept.retired = 0 AND concept_name.voided = 0\n )\n AND test_obs.voided = 0\n /* Select each test's results */\n INNER JOIN obs AS test_results_obs\n ON test_results_obs.obs_group_id = test_obs.obs_id\n AND test_results_obs.concept_id IN (\n SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)\n WHERE concept_name.name = 'Lab test result' AND concept.retired = 0 AND concept_name.voided = 0\n )\n AND test_results_obs.voided = 0\n AND test_results_obs.obs_datetime >= DATE(\#{start_date})\n AND test_results_obs.obs_datetime < DATE(\#{end_date}) + INTERVAL 1 DAY\n /* Limit the test result's to each patient's most recent result. */\n INNER JOIN (\n SELECT MAX(obs_datetime) AS obs_datetime,\n person_id\n FROM obs\n INNER JOIN orders\n ON orders.order_id = obs.order_id\n AND orders.order_type_id IN (SELECT order_type_id FROM order_type WHERE name = 'Lab' AND retired = 0)\n AND orders.concept_id IN (\n SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)\n WHERE concept_name.name IN ('Blood', 'DBS (Free drop to DBS card)', 'DBS (Using capillary tube)', 'Plasma')\n AND concept.retired = 0 AND concept_name.voided = 0\n )\n AND orders.voided = 0\n WHERE obs.concept_id IN (\n SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)\n WHERE concept_name.name = 'Lab test result' AND concept.retired = 0 AND concept_name.voided = 0\n )\n AND obs.voided = 0\n AND obs.obs_datetime >= DATE(\#{start_date})\n AND obs.obs_datetime < DATE(\#{end_date}) + INTERVAL 1 DAY\n GROUP BY person_id\n ) AS max_test_results\n ON max_test_results.obs_datetime = test_results_obs.obs_datetime\n AND max_test_results.person_id = test_results_obs.person_id\n /* Find a viral load measure that can be classified as High on the test results */\n INNER JOIN obs AS test_result_measure_obs\n ON test_result_measure_obs.obs_group_id = test_results_obs.obs_id\n AND test_result_measure_obs.concept_id IN (\n SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)\n WHERE concept_name.name = 'Viral load' AND concept.retired = 0 AND concept_name.voided = 0\n )\n AND (test_result_measure_obs.value_numeric IS NOT NULL\n OR test_result_measure_obs.value_text IS NOT NULL)\n AND test_result_measure_obs.voided = 0\n AND (\#{query_range})\n WHERE orders.order_type_id IN (SELECT order_type_id FROM order_type WHERE name = 'Lab' AND retired = 0)\n AND orders.voided = 0 \#{%w[Military Civilian].include?(@occupation) ? 'AND' : ''} \#{occupation_filter(occupation: @occupation, field_name: 'value', table_name: 'a', include_clause: false)}\n GROUP BY orders.patient_id\n SQL\nend\n"
|