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 <<~SQL
SELECT orders.patient_id,
patient_identifier.identifier AS arv_number,
person.birthdate AS birthdate,
disaggregated_age_group(person.birthdate, #{end_date}) AS age_group,
person.gender AS gender,
orders.start_date AS order_date,
specimen_type.name AS specimen,
COALESCE(orders.discontinued_date, orders.start_date) AS specimen_drawn_date,
test_results_obs.obs_datetime AS result_date,
COALESCE(test_result_measure_obs.value_modifier, '=') AS result_modifier,
COALESCE(test_result_measure_obs.value_numeric, test_result_measure_obs.value_text) AS result,
patient_current_regimen(orders.patient_id, orders.start_date) AS current_regimen
FROM orders
INNER JOIN concept_name AS specimen_type
ON specimen_type.concept_id = orders.concept_id
AND specimen_type.name IN ('Blood', 'DBS (Free drop to DBS card)', 'DBS (Using capillary tube)', 'Plasma')
AND specimen_type.voided = 0
LEFT JOIN patient_identifier
ON patient_identifier.patient_id = orders.patient_id
AND patient_identifier.voided = 0
AND patient_identifier.identifier_type IN (
SELECT patient_identifier_type_id FROM patient_identifier_type WHERE name = 'ARV Number' AND retired = 0
)
INNER JOIN person
ON person.person_id = orders.patient_id
AND person.voided = 0
LEFT JOIN (#{current_occupation_query}) AS a ON a.person_id = orders.patient_id
/* For each lab order find an HIV Viral Load test */
INNER JOIN obs AS test_obs
ON test_obs.order_id = orders.order_id
AND test_obs.concept_id IN (
SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)
WHERE concept_name.name = 'Test type' AND concept.retired = 0 AND concept_name.voided = 0
)
AND test_obs.value_coded IN (
SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)
WHERE concept_name.name = 'Viral load' AND concept.retired = 0 AND concept_name.voided = 0
)
AND test_obs.voided = 0
/* Select each test's results */
INNER JOIN obs AS test_results_obs
ON test_results_obs.obs_group_id = test_obs.obs_id
AND test_results_obs.concept_id IN (
SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)
WHERE concept_name.name = 'Lab test result' AND concept.retired = 0 AND concept_name.voided = 0
)
AND test_results_obs.voided = 0
AND test_results_obs.obs_datetime >= DATE(#{start_date})
AND test_results_obs.obs_datetime < DATE(#{end_date}) + INTERVAL 1 DAY
/* Limit the test result's to each patient's most recent result. */
INNER JOIN (
SELECT MAX(obs_datetime) AS obs_datetime,
person_id
FROM obs
INNER JOIN orders
ON orders.order_id = obs.order_id
AND orders.order_type_id IN (SELECT order_type_id FROM order_type WHERE name = 'Lab' AND retired = 0)
AND orders.concept_id IN (
SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)
WHERE concept_name.name IN ('Blood', 'DBS (Free drop to DBS card)', 'DBS (Using capillary tube)', 'Plasma')
AND concept.retired = 0 AND concept_name.voided = 0
)
AND orders.voided = 0
WHERE obs.concept_id IN (
SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)
WHERE concept_name.name = 'Lab test result' AND concept.retired = 0 AND concept_name.voided = 0
)
AND obs.voided = 0
AND obs.obs_datetime >= DATE(#{start_date})
AND obs.obs_datetime < DATE(#{end_date}) + INTERVAL 1 DAY
GROUP BY person_id
) AS max_test_results
ON max_test_results.obs_datetime = test_results_obs.obs_datetime
AND max_test_results.person_id = test_results_obs.person_id
/* Find a viral load measure that can be classified as High on the test results */
INNER JOIN obs AS test_result_measure_obs
ON test_result_measure_obs.obs_group_id = test_results_obs.obs_id
AND test_result_measure_obs.concept_id IN (
SELECT concept_id FROM concept_name INNER JOIN concept USING (concept_id)
WHERE concept_name.name = 'Viral load' AND concept.retired = 0 AND concept_name.voided = 0
)
AND (test_result_measure_obs.value_numeric IS NOT NULL
OR test_result_measure_obs.value_text IS NOT NULL)
AND test_result_measure_obs.voided = 0
AND (#{query_range})
WHERE orders.order_type_id IN (SELECT order_type_id FROM order_type WHERE name = 'Lab' AND retired = 0)
AND orders.voided = 0 #{%w[Military Civilian].include?(@occupation) ? 'AND' : ''} #{occupation_filter(occupation: @occupation, field_name: 'value', table_name: 'a', include_clause: false)}
GROUP BY orders.patient_id
SQL
end
|