Class: MalawiHivProgramReports::Moh::CohortBuilder

Inherits:
Object
  • Object
show all
Includes:
Adapters::Moh::Custom, Utils::CommonSqlQueryUtils, Utils::ModelUtils
Defined in:
app/services/malawi_hiv_program_reports/moh/cohort_builder.rb

Overview

This is the Cohort Builder class rubocop:disable Metrics/ClassLength

Direct Known Subclasses

CohortDisaggregatedBuilder

Constant Summary collapse

QUARTER_LENGTH = 
3.months
STATE_DIED = 
3
STATE_ON_TREATMENT = 
7

Instance Method Summary collapse

Methods included from Adapters::Moh::Custom

#cast_manager, #current_partition, #exe_create_drill_down_table, #exe_temp_cohort_members_table, #exe_temp_order_details_table, #exe_temp_other_patient_types, #exe_temp_register_start_date_table, #exe_tmp_patient_table, #function_manager, #group_by_columns, #in_manager, #interval_manager, #min_filt, #site_manager, #timestampdiff_manager

Methods included from Utils::CommonSqlQueryUtils

#current_occupation_query, #external_client_query, #occupation_filter, #partition_by_site, #process_occupation

Methods included from Utils::ModelUtils

#concept, #concept_id_to_name, #concept_name, #concept_name_to_id, #drug, #encounter_type, #global_property, #order_type, #patient_identifier_type, #program, #report_type, #user_property

Constructor Details

#initialize(outcomes_definition: 'moh', **kwargs) ⇒ CohortBuilder

Returns a new instance of CohortBuilder.



16
17
18
19
20
21
22
23
24
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 16

def initialize(outcomes_definition: 'moh', **kwargs)
  unless %w[moh pepfar].include?(outcomes_definition.downcase)
    raise ::ArgumentError, "Invalid outcomes_definition `#{outcomes_definition}` expected moh or pepfar"
  end

  @location = kwargs[:location]
  @adapter = ActiveRecord::Base.connection.adapter_name.downcase
  @outcomes_definition = outcomes_definition
end

Instance Method Details

#build(cohort_struct, start_date, end_date, occupation) ⇒ Object 



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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 41

def build(cohort_struct, start_date, end_date, occupation)
  # load_tmp_patient_table(cohort_struct)
  create_temp_cohort_members_table
  create_tmp_patient_table
  drop_temp_register_start_date_table
  drop_temp_other_patient_types
  drop_temp_order_details
  drop_art_start_date
  create_temp_other_patient_types(end_date)
  create_temp_register_start_date_table(end_date)
  create_temp_order_details(end_date)
  create_art_start_date(end_date)
  load_data_into_temp_earliest_start_date(end_date.to_date, occupation)

  # create_tmp_patient_table_2(end_date)

  time_started = Time.now.strftime('%Y-%m-%d %H:%M:%S')

  # create_temp_earliest_start_date_table(end_date)
  quarter_start_date = start_date.to_date

  # Get earliest date enrolled
  cum_start_date = get_cum_start_date

  cum_start_date = start_date if cum_start_date.blank?

  # Total registeres
  cohort_struct.total_registered = total_registered(start_date, end_date)
  cohort_struct.cum_total_registered = total_registered(cum_start_date, end_date)
  cohort_struct.quarterly_total_registered = total_registered(quarter_start_date, end_date)

  # Patients initiated on ART first time
  cohort_struct.initiated_on_art_first_time = initiated_on_art_first_time(start_date, end_date)
  cohort_struct.cum_initiated_on_art_first_time = initiated_on_art_first_time(cum_start_date, end_date)
  cohort_struct.quarterly_initiated_on_art_first_time = initiated_on_art_first_time(quarter_start_date, end_date)

  cohort_struct.males_initiated_on_art_first_time = males_initiated_on_art_first_time(start_date, end_date,
                                                                                      cohort_struct.initiated_on_art_first_time)
  cohort_struct.cum_males_initiated_on_art_first_time = males_initiated_on_art_first_time(cum_start_date,
                                                                                          end_date, cohort_struct.cum_initiated_on_art_first_time)

  # Patients re-initiated on ART
  cohort_struct.re_initiated_on_art = re_initiated_on_art(start_date, end_date)
  cohort_struct.cum_re_initiated_on_art = re_initiated_on_art(cum_start_date, end_date)
  cohort_struct.quarterly_re_initiated_on_art = re_initiated_on_art(quarter_start_date, end_date)

  # Patients transferred in on ART
  cohort_struct.transfer_in = transfer_in(start_date, end_date, cohort_struct.re_initiated_on_art)
  cohort_struct.cum_transfer_in = transfer_in(cum_start_date, end_date, cohort_struct.cum_re_initiated_on_art)
  cohort_struct.quarterly_transfer_in = transfer_in(quarter_start_date, end_date,
                                                    cohort_struct.quarterly_re_initiated_on_art)

  # All males
  cohort_struct.all_males = males(start_date, end_date)
  cohort_struct.cum_all_males = males(cum_start_date, end_date)
  cohort_struct.quarterly_all_males = males(quarter_start_date, end_date)

  # Pregnant females (all ages)
  create_temp_pregnant_obs(cum_start_date, end_date)
  cohort_struct.pregnant_females_all_ages = pregnant_females_all_ages(start_date, end_date)
  cohort_struct.cum_pregnant_females_all_ages = pregnant_females_all_ages(cum_start_date, end_date)
  cohort_struct.quarterly_pregnant_females_all_ages = pregnant_females_all_ages(quarter_start_date, end_date)

  cohort_struct.initial_pregnant_females_all_ages = initial_females_all_ages(start_date, end_date,
                                                                             cohort_struct.pregnant_females_all_ages)
  cohort_struct.cum_initial_pregnant_females_all_ages = initial_females_all_ages(cum_start_date, end_date,
                                                                                 cohort_struct.cum_pregnant_females_all_ages)

  # Non-pregnant females (all ages)
  # Unique PatientProgram entries at the current location for those patients with at least one state ON ARVs
  # and earliest start date of the 'ON ARVs' state within the quarter and having gender of
  # related PERSON entry as F for female and no entries of 'IS PATIENT PREGNANT?' observation answered 'YES'
  # in related HIV CLINIC CONSULTATION encounters not within 28 days from earliest registration date
  cohort_struct.non_pregnant_females = non_pregnant_females(start_date, end_date,
                                                            cohort_struct.pregnant_females_all_ages)
  cohort_struct.cum_non_pregnant_females = non_pregnant_females(cum_start_date, end_date,
                                                                cohort_struct.cum_pregnant_females_all_ages)
  cohort_struct.quarterly_non_pregnant_females = non_pregnant_females(quarter_start_date, end_date,
                                                                      cohort_struct.cum_pregnant_females_all_ages)

  cohort_struct.initial_non_pregnant_females_all_ages = initial_females_all_ages(start_date, end_date, cohort_struct.non_pregnant_females.map do |a|
                                                                                                         a['patient_id']
                                                                                                       end)
  cohort_struct.cum_initial_non_pregnant_females_all_ages = initial_females_all_ages(cum_start_date, end_date, cohort_struct.cum_non_pregnant_females.map do |a|
                                                                                                                 a['patient_id']
                                                                                                               end)

  # Children below 24 months at ART initiation
  cohort_struct.children_below_24_months_at_art_initiation = children_below_24_months_at_art_initiation(
    start_date, end_date
  )
  cohort_struct.cum_children_below_24_months_at_art_initiation = children_below_24_months_at_art_initiation(
    cum_start_date, end_date
  )
  cohort_struct.quarterly_children_below_24_months_at_art_initiation = children_below_24_months_at_art_initiation(
    quarter_start_date, end_date
  )

  # Children 24 months – 14 years at ART initiation
  cohort_struct.children_24_months_14_years_at_art_initiation = children_24_months_14_years_at_art_initiation(
    start_date, end_date
  )
  cohort_struct.cum_children_24_months_14_years_at_art_initiation = children_24_months_14_years_at_art_initiation(
    cum_start_date, end_date
  )
  cohort_struct.quarterly_children_24_months_14_years_at_art_initiation = children_24_months_14_years_at_art_initiation(
    quarter_start_date, end_date
  )

  # Adults at ART initiation
  cohort_struct.adults_at_art_initiation = adults_at_art_initiation(start_date, end_date)
  cohort_struct.cum_adults_at_art_initiation = adults_at_art_initiation(cum_start_date, end_date)
  cohort_struct.quarterly_adults_at_art_initiation = adults_at_art_initiation(quarter_start_date, end_date)

  # Unknown age
  cohort_struct.unknown_age = unknown_age(start_date, end_date)
  cohort_struct.cum_unknown_age = unknown_age(cum_start_date, end_date)
  cohort_struct.quarterly_unknown_age = unknown_age(quarter_start_date, end_date)

  # Unknown gender
  cohort_struct.unknown_gender = unknown_gender(start_date, end_date)
  cohort_struct.cum_unknown_gender = unknown_gender(cum_start_date, end_date)

  # Unique PatientProgram entries at the current location for those
  # patients with at least one state ON ARVs and earliest start date
  # of the 'ON ARVs' state within the quarter and having a
  # REASON FOR ELIGIBILITY observation with an answer as PRESUMED SEVERE HIV
  cohort_struct.presumed_severe_hiv_disease_in_infants = presumed_severe_hiv_disease_in_infants(start_date,
                                                                                                end_date)
  cohort_struct.cum_presumed_severe_hiv_disease_in_infants = presumed_severe_hiv_disease_in_infants(
    cum_start_date, end_date
  )
  cohort_struct.quarterly_presumed_severe_hiv_disease_in_infants = presumed_severe_hiv_disease_in_infants(
    quarter_start_date, end_date
  )

  # Confirmed HIV infection in infants (PCR)

  # Unique PatientProgram entries at the current location for those patients with at least one state ON ARVs
  # and earliest start date of the 'ON ARVs' state within the quarter and
  # having a REASON FOR ELIGIBILITY observation with an answer as HIV PCR
  cohort_struct.confirmed_hiv_infection_in_infants_pcr = confirmed_hiv_infection_in_infants_pcr(start_date,
                                                                                                end_date)
  cohort_struct.cum_confirmed_hiv_infection_in_infants_pcr = confirmed_hiv_infection_in_infants_pcr(
    cum_start_date, end_date
  )
  cohort_struct.quarterly_confirmed_hiv_infection_in_infants_pcr = confirmed_hiv_infection_in_infants_pcr(
    quarter_start_date, end_date
  )

  # WHO stage 1 or 2, CD4 below threshold
  # Unique PatientProgram entries at the current location for those patients with at least one state ON ARVs
  # and earliest start date of the 'ON ARVs' state within the quarter and having a REASON FOR ELIGIBILITY
  # observation with an answer as CD4 COUNT LESS THAN OR EQUAL TO 350 or CD4 COUNT LESS THAN OR EQUAL TO 750
  cohort_struct.who_stage_two = who_stage_two(start_date, end_date)
  cohort_struct.cum_who_stage_two = who_stage_two(cum_start_date, end_date)
  cohort_struct.quarterly_who_stage_two = who_stage_two(quarter_start_date, end_date)

  # Breastfeeding mothers

  # Unique PatientProgram entries at the current location for those patients with at least one state
  # ON ARVs and earliest start date of the 'ON ARVs' state within the quarter
  # and having a REASON FOR ELIGIBILITY observation with an answer as BREASTFEEDING
  cohort_struct.breastfeeding_mothers = breastfeeding_mothers(start_date, end_date)
  cohort_struct.cum_breastfeeding_mothers = breastfeeding_mothers(cum_start_date, end_date)
  cohort_struct.quarterly_breastfeeding_mothers = breastfeeding_mothers(quarter_start_date, end_date)

  # Pregnant women

  # Unique PatientProgram entries at the current location for those patients with at least one state ON ARVs
  # and earliest start date of the 'ON ARVs' state within the quarter
  # and having a REASON FOR ELIGIBILITY observation with an answer as PATIENT PREGNANT
  cohort_struct.pregnant_women = pregnant_women(start_date, end_date)
  cohort_struct.cum_pregnant_women = pregnant_women(cum_start_date, end_date)
  cohort_struct.quarterly_pregnant_women = pregnant_women(quarter_start_date, end_date)

  # WHO STAGE 3
  # Unique PatientProgram entries at the current location for those patients with at least
  # one state ON ARVs and earliest start date of the 'ON ARVs' state within the quarter
  # and having a REASON FOR ELIGIBILITY observation with an answer as WHO STAGE III
  cohort_struct.who_stage_three = who_stage_three(start_date, end_date)
  cohort_struct.cum_who_stage_three = who_stage_three(cum_start_date, end_date)
  cohort_struct.quarterly_who_stage_three = who_stage_three(quarter_start_date, end_date)

  # WHO STAGE 4
  # Unique PatientProgram entries at the current location for those patients with at least
  # one state ON ARVs and earliest start date of the 'ON ARVs' state within the quarter
  # and having a REASON FOR ELIGIBILITY observation with an answer as WHO STAGE IV
  cohort_struct.who_stage_four = who_stage_four(start_date, end_date)
  cohort_struct.cum_who_stage_four = who_stage_four(cum_start_date, end_date)
  cohort_struct.quarterly_who_stage_four = who_stage_four(quarter_start_date, end_date)

  # Asymptomatic
  # Unique PatientProgram entries at the current location for those patients with at least
  # one state ON ARVs and earliest start date of the 'ON ARVs' state within the quarter
  # and having a REASON FOR ELIGIBILITY observation with an answer as Lymphocytes
  # or LYMPHOCYTE COUNT BELOW THRESHOLD WITH WHO STAGE 2

  # For all those patients with WHO stage 1 and 2, only those that were enrolled
  # after or on 2016-04-01 revised_guidelines_start_date = "2016-04-01"
  cohort_struct.asymptomatic = asymptomatic(start_date, end_date)
  cohort_struct.cum_asymptomatic = asymptomatic(cum_start_date, end_date)
  cohort_struct.quarterly_asymptomatic = asymptomatic(quarter_start_date, end_date)

  # Unknown / other reason outside guidelines
  # Unique PatientProgram entries at the current location for those patients with at least one state ON ARVs
  # and earliest start date of the 'ON ARVs' state within the quarter
  # and having a REASON FOR ELIGIBILITY observation with an answer as UNKNOWN
  cohort_struct.unknown_other_reason_outside_guidelines = unknown_other_reason_outside_guidelines(start_date,
                                                                                                  end_date)
  cohort_struct.cum_unknown_other_reason_outside_guidelines = unknown_other_reason_outside_guidelines(
    cum_start_date, end_date
  )
  cohort_struct.quarterly_unknown_other_reason_outside_guidelines = unknown_other_reason_outside_guidelines(
    quarter_start_date, end_date
  )

  # Children 12-23 months

  # Unique PatientProgram entries at the current location for those patients with at least one state
  # ON ARVs and earliest start date of the 'ON ARVs' state within the quarter and having
  # Confirmed HIV Infection (HIV Rapid antibody test or DNA-PCR), regardless of WHO stage and CD4 Count
  cohort_struct.children_12_59_months = children_12_59_months(start_date, end_date)
  cohort_struct.cum_children_12_59_months = children_12_59_months(cum_start_date, end_date)
  cohort_struct.quarterly_children_12_59_months = children_12_59_months(quarter_start_date, end_date)

  # Current EPISODE OF TB

  # Unique PatientProgram entries at the current location for those patients with at least one state
  # ON ARVs and earliest start date of the 'ON ARVs' state within the quarter and having a
  # CURRENT EPISODE OF TB observation at the HIV staging encounter on the initiation date
  cohort_struct.current_episode_of_tb = current_episode_of_tb(start_date, end_date)
  cohort_struct.cum_current_episode_of_tb = current_episode_of_tb(cum_start_date, end_date)
  cohort_struct.quarterly_current_episode_of_tb = current_episode_of_tb(quarter_start_date, end_date)

  # TB within the last 2 years

  # Unique PatientProgram entries at the current location for those patients with at least one state ON ARVs
  # and earliest start date of the 'ON ARVs' state within the quarter
  # and having a TB WITHIN THE LAST 2 YEARS observation at the HIV staging encounter on the initiation date
  cohort_struct.tb_within_the_last_two_years = tb_within_the_last_two_years(cohort_struct.current_episode_of_tb,
                                                                            start_date, end_date)
  cohort_struct.cum_tb_within_the_last_two_years = tb_within_the_last_two_years(
    cohort_struct.cum_current_episode_of_tb, cum_start_date, end_date
  )
  cohort_struct.quarterly_tb_within_the_last_two_years = tb_within_the_last_two_years(
    cohort_struct.quarterly_current_episode_of_tb, quarter_start_date, end_date
  )

  # No TB
  # total_registered - (current_episode - tb_within_the_last_two_years)
  cohort_struct.no_tb = no_tb(cohort_struct.total_registered, cohort_struct.tb_within_the_last_two_years,
                              cohort_struct.current_episode_of_tb)
  cohort_struct.cum_no_tb = cum_no_tb(cohort_struct.cum_total_registered,
                                      cohort_struct.cum_tb_within_the_last_two_years, cohort_struct.cum_current_episode_of_tb)
  cohort_struct.quarterly_no_tb = cum_no_tb(cohort_struct.quarterly_total_registered,
                                            cohort_struct.quarterly_tb_within_the_last_two_years, cohort_struct.quarterly_current_episode_of_tb)

  # Kaposis Sarcoma
  #
  # Unique PatientProgram entries at the current location for those patients with at least one state ON ARVs
  # and earliest start date of the 'ON ARVs' state within the quarter and having a KAPOSIS SARCOMA observation
  # at the HIV staging encounter on the initiation date
  cohort_struct.kaposis_sarcoma = kaposis_sarcoma(start_date, end_date)
  cohort_struct.cum_kaposis_sarcoma = kaposis_sarcoma(cum_start_date, end_date)
  cohort_struct.quarterly_kaposis_sarcoma = kaposis_sarcoma(quarter_start_date, end_date)

  # From this point going down: we update temp_earliest_start_date cum_outcome field to have the latest Cumulative outcome
  update_cum_outcome(end_date)
  update_tb_status(end_date)
  update_patient_side_effects(end_date)

  # Total Alive and On ART
  # Unique PatientProgram entries at the current location for those patients with at least one state
  # ON ARVs and earliest start date of the 'ON ARVs' state less than or equal to end date of quarter
  # and latest state is ON ARVs  (Excluding defaulters)
  cohort_struct.total_alive_and_on_art                      = get_outcome('On antiretrovirals')
  cohort_struct.died_within_the_1st_month_of_art_initiation = died_in('1st month')
  cohort_struct.died_within_the_2nd_month_of_art_initiation = died_in('2nd month')
  cohort_struct.died_within_the_3rd_month_of_art_initiation = died_in('3rd month')
  cohort_struct.died_after_the_3rd_month_of_art_initiation  = died_in('4+ months')
  cohort_struct.died_total                                  = get_outcome('Patient died')
  cohort_struct.defaulted                                   = get_outcome('Defaulted')
  cohort_struct.stopped_art                                 = get_outcome('Treatment stopped')
  cohort_struct.transfered_out                              = get_outcome('Patient transferred out')
  cohort_struct.unknown_outcome                             = get_outcome('Pre-ART (Continue)')

  # ARV Regimen category
  # Alive and On ART and Value Coded of the latest 'Regimen Category' Observation
  # of each patient that is linked to the Dispensing encounter in the reporting period

  prescriptions = cal_regimem_category(cohort_struct.total_alive_and_on_art, end_date)

  # concepts = ->(names) { ::ConceptName.where(name: names).select(:concept_id) }
  # drugs = ->(concepts) { ::Drug.where(concept: concepts).select(:drug_id).collect(&:drug_id) }

  # lpv_granules = drugs[concepts[['LPV/r Pellets', 'LPV/r Granules']]]
  # lpv_tabs = drugs[concepts['LPV/r']]

  cohort_struct.zero_p            = filter_prescriptions_by_regimen(prescriptions, '0P')
  cohort_struct.zero_a            = filter_prescriptions_by_regimen(prescriptions, '0A')
  cohort_struct.two_p             = filter_prescriptions_by_regimen(prescriptions, '2P')
  cohort_struct.two_a             = filter_prescriptions_by_regimen(prescriptions, '2A')
  cohort_struct.four_a            = filter_prescriptions_by_regimen(prescriptions, '4A')
  cohort_struct.four_pp           = filter_prescriptions_by_regimen(prescriptions, '4PP')
  cohort_struct.four_pa           = filter_prescriptions_by_regimen(prescriptions, '4PA')
  cohort_struct.five_a            = filter_prescriptions_by_regimen(prescriptions, '5A')
  cohort_struct.six_a             = filter_prescriptions_by_regimen(prescriptions, '6A')
  cohort_struct.seven_a           = filter_prescriptions_by_regimen(prescriptions, '7A')
  cohort_struct.eight_a           = filter_prescriptions_by_regimen(prescriptions, '8A')
  cohort_struct.nine_a            = filter_prescriptions_by_regimen(prescriptions, '9A')
  cohort_struct.nine_pp           = filter_prescriptions_by_regimen(prescriptions, '9PP')
  cohort_struct.nine_pa           = filter_prescriptions_by_regimen(prescriptions, '9PA')
  cohort_struct.ten_a             = filter_prescriptions_by_regimen(prescriptions, '10A')
  cohort_struct.eleven_a          = filter_prescriptions_by_regimen(prescriptions, '11A')
  cohort_struct.eleven_pp         = filter_prescriptions_by_regimen(prescriptions, '11PP')
  cohort_struct.eleven_pa         = filter_prescriptions_by_regimen(prescriptions, '11PA')
  cohort_struct.twelve_a          = filter_prescriptions_by_regimen(prescriptions, '12A')
  cohort_struct.twelve_pp         = filter_prescriptions_by_regimen(prescriptions, '12PP')
  cohort_struct.twelve_pa         = filter_prescriptions_by_regimen(prescriptions, '12PA')
  cohort_struct.thirteen_a        = filter_prescriptions_by_regimen(prescriptions, '13A')
  cohort_struct.fourteen_pp       = filter_prescriptions_by_regimen(prescriptions, '14PP')
  cohort_struct.fourteen_pa       = filter_prescriptions_by_regimen(prescriptions, '14PA')
  cohort_struct.fourteen_a        = filter_prescriptions_by_regimen(prescriptions, '14A')
  cohort_struct.fifteen_pp        = filter_prescriptions_by_regimen(prescriptions, '15PP')
  cohort_struct.fifteen_pa        = filter_prescriptions_by_regimen(prescriptions, '15PA')
  cohort_struct.fifteen_a         = filter_prescriptions_by_regimen(prescriptions, '15A')
  cohort_struct.sixteen_p         = filter_prescriptions_by_regimen(prescriptions, '16P')
  cohort_struct.sixteen_a         = filter_prescriptions_by_regimen(prescriptions, '16A')
  cohort_struct.seventeen_pp      = filter_prescriptions_by_regimen(prescriptions, '17PP')
  cohort_struct.seventeen_pa      = filter_prescriptions_by_regimen(prescriptions, '17PA')
  cohort_struct.seventeen_a       = filter_prescriptions_by_regimen(prescriptions, '17A')
  cohort_struct.unknown_regimen   = filter_prescriptions_by_regimen(prescriptions, 'unknown_regimen')

  # Total patients with side effects:
  # Alive and On ART patients with DRUG INDUCED observations during their last HIV CLINIC CONSULTATION encounter up to the reporting period

  with_se, without_se, se_unknowns = patients_side_effects_status(cohort_struct.total_alive_and_on_art, end_date)
  cohort_struct.total_patients_with_side_effects = with_se
  cohort_struct.total_patients_without_side_effects = without_se
  cohort_struct.unknown_side_effects = se_unknowns

  # TB Status
  # Alive and On ART with 'TB Status' observation value of 'TB not Suspected' or 'TB Suspected'
  # or 'TB confirmed and on Treatment', or 'TB confirmed and not on Treatment' or 'Unknown TB status'
  # during their latest HIV Clinic Consultaiton encounter in the reporting period
  write_tb_status_indicators(cohort_struct, cohort_struct.total_alive_and_on_art, start_date, end_date)

  # ART adherence
  #
  # Alive and On ART with value of their 'Drug order adherence" observation during their latest Adherence
  # encounter in the reporting period  between 95 and 105
  adherent, not_adherent, unknown_adherence = latest_art_adherence(cohort_struct.total_alive_and_on_art,
                                                                   start_date, end_date)
  cohort_struct.patients_with_0_6_doses_missed_at_their_last_visit = adherent
  cohort_struct.patients_with_7_plus_doses_missed_at_their_last_visit = not_adherent
  cohort_struct.patients_with_unknown_adhrence = unknown_adherence

  # Pregnant and breastfeeding status during Consultaiton
  cohort_struct.total_pregnant_women = total_pregnant_women(cohort_struct.total_alive_and_on_art, start_date,
                                                            end_date)
  cohort_struct.total_breastfeeding_women = total_breastfeeding_women(cohort_struct.total_alive_and_on_art,
                                                                      cohort_struct.total_pregnant_women, start_date, end_date)
  cohort_struct.total_other_patients = total_other_patients(cohort_struct.total_alive_and_on_art,
                                                            cohort_struct.total_breastfeeding_women, cohort_struct.total_pregnant_women)

  # Patients with CPT dispensed at least once before end of quarter and on ARVs
  cohort_struct.total_patients_on_arvs_and_cpt = total_patients_on_arvs_and_cpt(
    cohort_struct.total_alive_and_on_art, start_date, end_date
  )

  # Patients with IPT dispensed at least once before end of quarter and on ARVS
  cohort_struct.total_patients_on_arvs_and_ipt = total_patients_on_arvs_and_ipt(
    cohort_struct.total_alive_and_on_art, start_date, end_date
  )

  # Patients on family planning methods at least once before end of quarter and on ARVs
  cohort_struct.total_patients_on_family_planning = total_patients_on_family_planning(
    cohort_struct.total_alive_and_on_art, quarter_start_date, end_date
  )

  # Patients whose BP was screened and are above 30 years least once before end of quarter and on ARVs
  cohort_struct.total_patients_with_screened_bp = total_patients_with_screened_bp(
    total_patients_alive_and_on_art_above_30_years(cohort_struct.total_alive_and_on_art,
                                                   end_date), start_date, end_date
  )

  # Patients who started TPT in current reporting period
  tpt = MalawiHivProgramReports::Cohort::Tpt.new(start_date:, end_date:, occupation:,
                                                 location: @location)
  # tpt newly initiated has a property last_tpt_start_date we need to use that to get those clients
  cohort_struct.newly_initiated_on_3hp = tpt.newly_initiated_on_3hp.select do |hash|
    hash['last_tpt_start_date'].nil?
  end
  cohort_struct.newly_initiated_on_ipt = tpt.newly_initiated_on_ipt.select do |hash|
    hash['last_tpt_start_date'].nil?
  end
  time_ended = Time.now.strftime('%Y-%m-%d %H:%M:%S')
  puts "Started at: #{time_started}. Finished at: #{time_ended}. Total time in minutes: #{(Time.parse(time_ended) - Time.parse(time_started)) / 60}"
  Rails.logger.info "Started at: #{time_started}. Finished at: #{Time.now.strftime('%Y-%m-%d %H:%M:%S')}. Total time in minutes: #{(Time.parse(time_ended) - Time.parse(time_started)) / 60}"
  cohort_struct
end

#create_art_start_date(end_date) ⇒ Object 



827
828
829
830
831
832
833
834
835
836
837
838
839
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 827

def create_art_start_date(end_date)
  ActiveRecord::Base.connection.execute <<-SQL
    CREATE TABLE temp_art_start_date (
      patient_id INT(11) NOT NULL,
      value_datetime DATE NOT NULL,
      site_id INT(11) NOT NULL,
      PRIMARY KEY (patient_id)
    )
  SQL
  ActiveRecord::Base.connection.execute 'CREATE INDEX tasd_date ON temp_art_start_date (value_datetime)'
  ActiveRecord::Base.connection.execute 'CREATE INDEX tasd_site ON temp_art_start_date (site_id)'
  load_art_start_date(end_date)
end

#create_temp_cohort_members_table ⇒ Object

This will hold crucial information for cohort members rubocop:disable Metrics/MethodLength rubocop:disable Metrics/AbcSize



751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 751

def create_temp_cohort_members_table
  ActiveRecord::Base.connection.execute('DROP TABLE IF EXISTS temp_cohort_members')
  exe_temp_cohort_members_table(adapter: @adapter)

  ActiveRecord::Base.connection.execute(
    'CREATE INDEX member_id_index ON temp_cohort_members (patient_id)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX member_site_index ON temp_cohort_members (site_id)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX member_id_site_index ON temp_cohort_members (patient_id, site_id)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX member_enrolled_index ON temp_cohort_members (date_enrolled)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX member_date_enrolled_index ON temp_cohort_members (patient_id, date_enrolled)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX member_start_date_index ON temp_cohort_members (earliest_start_date)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX member_start_date__date_enrolled_index ON temp_cohort_members (patient_id, earliest_start_date, date_enrolled, gender)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX member_reason ON temp_cohort_members (reason_for_starting_art)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX member_birthdate_idx ON temp_cohort_members (birthdate)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX member_occupation_idx ON temp_cohort_members (birthdate)'
  )
end

#create_temp_order_details(end_date) ⇒ Object 



859
860
861
862
863
864
865
866
867
868
869
870
871
872
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 859

def create_temp_order_details(end_date)
  exe_temp_order_details_table(adapter: @adapter)
  ActiveRecord::Base.connection.execute <<~SQL
    INSERT INTO temp_order_details
    SELECT o.patient_id, o.site_id, DATE(MIN(o.start_date)) start_date#{'  '}
    FROM orders o
    INNER JOIN drug_order do ON do.order_id = o.order_id AND do.quantity > 0 #{site_manager(operator: 'AND', column: 'do.site_id', location: @location)}
    LEFT JOIN temp_register_start_date trsd ON trsd.patient_id  = o.patient_id #{site_manager(operator: 'AND', column: 'trsd.site_id', location: @location)}
    WHERE o.concept_id  IN (SELECT concept_id FROM concept_set WHERE concept_set = 1085)
    AND o.start_date < #{interval_manager(date: end_date, value: 1, interval: 'DAY', operator: '+')} AND o.start_date >= COALESCE(trsd.start_date, DATE('1901-01-01'))
    and o.order_type_id = 1 ANd o.voided  = 0 #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
    GROUP BY o.patient_id;
  SQL
end

#create_temp_other_patient_types(_end_date) ⇒ Object

rubocop:enable Metrics/AbcSize rubocop:enable Metrics/MethodLength



789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 789

def create_temp_other_patient_types(_end_date)
  type_of_patient_concept = concept('Type of patient').concept_id
  drug_refill_concept = concept('Drug refill').concept_id
  external_concept = concept('External consultation').concept_id
  exe_temp_other_patient_types(adapter: @adapter)

  ActiveRecord::Base.connection.execute <<~SQL
    INSERT INTO temp_other_patient_types (patient_id, site_id)
    SELECT pp.patient_id as patient_id, pp.site_id as site_id
    FROM patient_program pp
    INNER JOIN obs o ON pp.patient_id = o.person_id AND o.concept_id = #{type_of_patient_concept} #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
    AND #{in_manager(column: 'o.value_coded', values: [drug_refill_concept, external_concept])}
    AND o.voided = 0
    WHERE pp.program_id = 1
    AND pp.voided = 0
    #{site_manager(operator: 'AND', column: 'pp.site_id', location: @location)}
    GROUP BY pp.patient_id #{@adapter == 'mysql2' ? '' : ', pp.site_id'}
  SQL
end

#create_temp_register_start_date_table(end_date) ⇒ Object 



874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 874

def create_temp_register_start_date_table(end_date)
  type_of_patient_concept = concept('Type of patient').concept_id
  new_patient_concept = concept('New patient').concept_id
  exe_temp_register_start_date_table(adapter: @adapter)
  ActiveRecord::Base.connection.execute <<-SQL
    INSERT INTO temp_register_start_date (patient_id, start_date, site_id)
    SELECT pp.patient_id as patient_id, MIN(o.obs_datetime) AS start_date, pp.site_id as site_id
    FROM patient_program pp
    INNER JOIN temp_other_patient_types tmp ON tmp.patient_id = pp.patient_id #{site_manager(operator: 'AND', column: 'pp.site_id', location: @location)}
    INNER JOIN obs o ON pp.patient_id = o.person_id AND o.concept_id = #{type_of_patient_concept} #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
    AND o.value_coded = #{new_patient_concept}
    AND o.voided = 0 AND o.obs_datetime < DATE('#{end_date}') + INTERVAL 1 DAY
    WHERE pp.program_id = 1 #{site_manager(operator: 'AND', column: 'pp.site_id', location: @location)}
    GROUP BY pp.patient_id #{@adapter == 'mysql2' ? '' : ', pp.site_id'}
  SQL
end

#create_tmp_patient_table ⇒ Object

rubocop:enable Metrics/MethodLength rubocop:enable Metrics/AbcSize



946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 946

def create_tmp_patient_table
  ActiveRecord::Base.connection.execute('DROP TABLE IF EXISTS temp_earliest_start_date')
  exe_tmp_patient_table(adapter: @adapter)

  ActiveRecord::Base.connection.execute(
    'CREATE INDEX patient_id_index ON temp_earliest_start_date (patient_id)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX site_id_tesd ON temp_earliest_start_date (site_id)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX patient_id__site_id_index ON temp_earliest_start_date (patient_id, site_id)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX date_enrolled_index ON temp_earliest_start_date (date_enrolled)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX patient_id__date_enrolled_index ON temp_earliest_start_date (patient_id, date_enrolled)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX earliest_start_date_index ON temp_earliest_start_date (earliest_start_date)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX earliest_start_date__date_enrolled_index ON temp_earliest_start_date (patient_id, earliest_start_date, date_enrolled, gender)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX idx_reason_for_art ON temp_earliest_start_date (reason_for_starting_art)'
  )
  ActiveRecord::Base.connection.execute(
    'CREATE INDEX birthdate_idx ON temp_earliest_start_date (birthdate)'
  )
end

#drop_art_start_date ⇒ Object 



821
822
823
824
825
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 821

def drop_art_start_date
  ActiveRecord::Base.connection.execute <<~SQL
    DROP TABLE IF EXISTS temp_art_start_date
  SQL
end

#drop_temp_order_details ⇒ Object 



815
816
817
818
819
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 815

def drop_temp_order_details
  ActiveRecord::Base.connection.execute <<~SQL
    DROP TABLE IF EXISTS temp_order_details
  SQL
end

#drop_temp_other_patient_types ⇒ Object 



809
810
811
812
813
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 809

def drop_temp_other_patient_types
  ActiveRecord::Base.connection.execute <<~SQL
    DROP TABLE IF EXISTS temp_other_patient_types
  SQL
end

#drop_temp_register_start_date_table ⇒ Object 



891
892
893
894
895
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 891

def drop_temp_register_start_date_table
  ActiveRecord::Base.connection.execute <<~SQL
    DROP TABLE IF EXISTS temp_register_start_date
  SQL
end

#drug_refills_and_external_consultation_list(end_date) ⇒ Object

this just gives all clients who are truly external or drug refill rubocop:disable Metrics/MethodLength rubocop:disable Metrics/AbcSize



907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 907

def drug_refills_and_external_consultation_list(end_date)
  to_remove = [0]

  type_of_patient_concept = concept('Type of patient').concept_id
  new_patient_concept = concept('New patient').concept_id
  drug_refill_concept = concept('Drug refill').concept_id
  external_concept = concept('External consultation').concept_id
  hiv_clinic_registration_id = ::EncounterType.find_by_name('HIV CLINIC REGISTRATION').encounter_type_id

  ActiveRecord::Base.connection.select_all("SELECT e.patient_id
  FROM temp_cohort_members e
  LEFT JOIN encounter as hiv_registration ON hiv_registration.patient_id = e.patient_id
    AND hiv_registration.encounter_datetime < DATE(#{end_date})
    AND hiv_registration.encounter_type = #{hiv_clinic_registration_id}
    AND hiv_registration.voided = 0 #{site_manager(operator: 'AND', column: 'hiv_registration.site_id',
                                                   location: @location)}
  LEFT JOIN (SELECT * FROM obs WHERE concept_id = #{type_of_patient_concept} AND voided = 0 AND value_coded = #{new_patient_concept} AND obs_datetime < #{interval_manager(
    date: end_date, value: 1, interval: 'DAY', operator: '+'
  )} #{site_manager(operator: 'AND', column: 'site_id',
                    location: @location)}) AS new_patient ON e.patient_id = new_patient.person_id
  LEFT JOIN (SELECT * FROM obs WHERE concept_id = #{type_of_patient_concept} AND voided = 0 AND value_coded = #{drug_refill_concept} AND obs_datetime < #{interval_manager(
    date: end_date, value: 1, interval: 'DAY', operator: '+'
  )} #{site_manager(operator: 'AND', column: 'site_id',
                    location: @location)}) AS refill ON e.patient_id = refill.person_id
  LEFT JOIN (SELECT * FROM obs WHERE concept_id = #{type_of_patient_concept} AND voided = 0 AND value_coded = #{external_concept} AND DATE(obs_datetime) < #{interval_manager(
    date: end_date, value: 1, interval: 'DAY', operator: '+'
  )} #{site_manager(operator: 'AND', column: 'site_id',
                    location: @location)}) AS external ON e.patient_id = external.person_id
  WHERE (refill.value_coded IS NOT NULL OR external.value_coded IS NOT NULL)
  AND NOT (hiv_registration.encounter_id IS NOT NULL OR new_patient.value_coded IS NOT NULL)
  GROUP BY e.patient_id
  ORDER BY MAX(hiv_registration.encounter_datetime) DESC, MAX(refill.obs_datetime) DESC, MAX(external.obs_datetime) DESC;").each do |record|
    to_remove << record['patient_id'].to_i
  end
  to_remove.join(',')
end

#get_disaggregated_cohort(start_date, end_date, gender, ag) ⇒ Object

private



446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 446

def get_disaggregated_cohort(start_date, end_date, gender, ag)
  case ag
  when '50+ years'
    diff = [50, 1000]
    iu = 'year'
  when /years/i
    diff = ag.sub(' years', '').split('-')
    iu = 'year'
  when /months/i
    diff = ag.sub(' months', '').split('-')
    iu = 'month'
  else
    case gender
    when 'M'
      diff = [0, 1000]
      iu = 'year'
      gender = 'M'
    when 'FNP'
      diff = [0, 1000]
      iu = 'year'
      gender = 'F'
    when 'FP'
      diff = [0, 1000]
      iu = 'year'
      gender = 'F'
    when 'FBf'
      diff = [0, 1000]
      iu = 'year'
      gender = 'F'
    end
  end

  data = ActiveRecord::Base.connection.select_all(
    "SELECT patient_id  FROM temp_earliest_start_date
     WHERE earliest_start_date BETWEEN '#{start_date.to_date}' AND '#{end_date.to_date}'
      AND (earliest_start_date) = (date_enrolled) AND gender = '#{gender.first}' #{site_manager(operator: 'AND',
                                                                                                column: 'site_id', location: @location)}
      AND timestampdiff(#{iu}, birthdate, date_enrolled) BETWEEN #{diff[0].to_i} AND #{diff[1].to_i}"
  )

  data1 = ActiveRecord::Base.connection.select_all(
    "SELECT t1.patient_id
    FROM temp_earliest_start_date t1
    INNER JOIN temp_patient_outcomes t2 ON t1.patient_id = t2.patient_id #{site_manager(operator: 'AND',
                                                                                        column: 't2.site_id', location: @location)}
    WHERE date_enrolled <= '#{end_date.to_date}' AND gender = '#{gender.first}'
      AND cum_outcome = 'On antiretrovirals' #{site_manager(operator: 'AND', column: 't1.site_id',
                                                            location: @location)}
      AND timestampdiff(#{iu}, birthdate, date_enrolled) BETWEEN #{diff[0].to_i} AND #{diff[1].to_i}"
  )

  ::EncounterType.find_by_name('DISPENSING').id
  amount_dispensed = concept('Amount dispensed').concept_id
  ipt_drug_ids = ::Drug.find_all_by_concept_id(656).map(&:drug_id)

  patient_ids = []
  (data1 || {}).each_key do |x|
    patient_ids << x['patient_id'].to_i
  end

  unless patient_ids.blank?
    data2 = ActiveRecord::Base.connection.select_all(
      "SELECT e.patient_id
      FROM encounter e
      INNER JOIN temp_patient_outcomes o ON o.patient_id = e.patient_id
        AND o.cum_outcome = 'On antiretrovirals' #{site_manager(operator: 'AND', column: 'o.site_id',
                                                                location: @location)}
      INNER JOIN obs ON obs.encounter_id = e.encounter_id
        AND obs.concept_id = #{amount_dispensed} #{site_manager(operator: 'AND', column: 'obs.site_id',
                                                                location: @location)}
      WHERE value_drug IN(#{ipt_drug_ids.join(',')})
        AND e.patient_id IN(#{patient_ids.join(',')})
        AND e.encounter_datetime BETWEEN '#{start_date.to_date.strftime('%Y-%m-%d 00:00:00')}'
        AND '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}' #{site_manager(operator: 'AND',
                                                                               column: 'e.site_id', location: @location)}
      GROUP BY e.patient_id"
    )
  end

  [data&.length || 0, data1&.length || 0, data2.length || 0, 0]
end

#init_temporary_tables(_start_date, end_date, occupation) ⇒ Object 



26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 26

def init_temporary_tables(_start_date, end_date, occupation)
  create_temp_cohort_members_table
  create_tmp_patient_table
  drop_temp_register_start_date_table
  drop_temp_other_patient_types
  drop_temp_order_details
  drop_art_start_date
  create_temp_other_patient_types(end_date)
  create_temp_register_start_date_table(end_date)
  create_temp_order_details(end_date)
  create_art_start_date(end_date)
  load_data_into_temp_earliest_start_date(end_date.to_date, occupation)
  update_cum_outcome(end_date)
end

#load_art_start_date(end_date) ⇒ Object 



841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 841

def load_art_start_date(end_date)
  ActiveRecord::Base.connection.execute <<-SQL
    INSERT INTO temp_art_start_date
    SELECT o.person_id, DATE(MIN(o.value_datetime)) value_datetime, o.site_id
    FROM encounter e
    INNER JOIN obs o ON o.encounter_id = e.encounter_id#{' '}
      AND o.concept_id = 2516#{' '}
      AND e.encounter_type = 9#{' '}
      AND e.program_id = 1#{' '}
      AND e.voided = 0 #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
      AND e.encounter_datetime < DATE('#{end_date}') + INTERVAL 1 DAY
      AND o.obs_datetime < (DATE('#{end_date}') + INTERVAL 1 DAY)
      AND e.voided = 0 #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
    WHERE e.voided = 0 #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
    GROUP BY o.person_id
  SQL
end

#load_data_into_temp_cohort_members_table(end_date) ⇒ Object

rubocop:disable Metrics/MethodLength



682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 682

def load_data_into_temp_cohort_members_table(end_date)
  end_date = ActiveRecord::Base.connection.quote(end_date)

  concept('Type of patient').concept_id
  concept('New patient').concept_id
  concept('Drug refill').concept_id
  concept('External Consultation').concept_id
  program('HIV program').id

  ActiveRecord::Base.connection.execute <<~SQL
    INSERT INTO temp_cohort_members
    SELECT patient_program.patient_id, #{@location} as site_id,
           DATE(MIN(art_order.start_date)) AS date_enrolled,
           DATE(COALESCE(MIN(art_start_date_obs.value_datetime), MIN(art_order.start_date))) AS earliest_start_date,
           DATE(MIN(art_start_date_obs.value_datetime)) AS recorded_start_date,
           person.birthdate,
          #{@adapter == 'mysql2' ? 'person.birthdate_estimated' : '(CASE WHEN person.birthdate_estimated = 0 THEN false ELSE true END)'} AS birthdate_estimated,
           person.death_date,
           LEFT(person.gender, 1) gender, #{
             if @adapter == 'mysql2'
               <<~SQL
                 IF(person.birthdate IS NOT NULL, TIMESTAMPDIFF(YEAR, person.birthdate,  DATE(COALESCE(art_start_date_obs.value_datetime, MIN(art_order.start_date)))), NULL) AS age_at_initiation,
                 IF(person.birthdate IS NOT NULL, TIMESTAMPDIFF(DAY, person.birthdate,  DATE(COALESCE(art_start_date_obs.value_datetime, MIN(art_order.start_date)))), NULL) AS age_in_days,
               SQL
             else
               <<~SQL
                 CASE
                 WHEN person.birthdate IS NOT NULL THEN
                     EXTRACT(YEAR FROM AGE(COALESCE(MIN(art_start_date_obs.value_datetime)::DATE, MIN(art_order.start_date)), person.birthdate))
                 ELSE NULL
                 END AS age_at_initiation,
                 CASE
                 WHEN person.birthdate IS NOT NULL THEN
                     EXTRACT(DAY FROM AGE(COALESCE(MIN(art_start_date_obs.value_datetime)::DATE, MIN(art_order.start_date)), person.birthdate))
                 ELSE NULL
                 END AS age_in_days,
               SQL
             end
           }
           (SELECT value_coded FROM obs
            WHERE concept_id = 7563 AND person_id = patient_program.patient_id AND voided = 0 #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
            AND obs_datetime <= DATE(#{end_date}) + INTERVAL 1 DAY
            ORDER BY obs_datetime DESC, date_created DESC LIMIT 1) as reason_for_starting_art,
           pa.value AS occupation
    FROM patient_program
    INNER JOIN person ON person.person_id = patient_program.patient_id AND person.voided = 0 #{site_manager(operator: 'AND', column: 'person.site_id', location: @location)}
    LEFT JOIN (#{current_occupation_query}) pa ON pa.person_id = patient_program.patient_id #{site_manager(operator: 'AND', column: 'pa.site_id', location: @location)}
    LEFT JOIN patient_state AS outcome
      ON outcome.patient_program_id = patient_program.patient_program_id #{site_manager(operator: 'AND', column: 'outcome.site_id', location: @location)}
    LEFT JOIN temp_art_start_date AS art_start_date_obs
      ON art_start_date_obs.patient_id = patient_program.patient_id #{site_manager(operator: 'AND', column: 'art_start_date_obs.site_id', location: @location)}
    INNER JOIN temp_order_details AS art_order ON art_order.patient_id = patient_program.patient_id #{site_manager(operator: 'AND', column: 'art_order.site_id', location: @location)}
     AND art_order.start_date <= DATE(#{end_date})
    WHERE patient_program.voided = 0
      AND outcome.voided = 0
      AND patient_program.program_id = 1
      AND outcome.state = 7
      AND outcome.start_date IS NOT NULL #{site_manager(operator: 'AND', column: 'patient_program.site_id', location: @location)}
    GROUP by patient_program.patient_id #{@adapter == 'mysql2' ? '' : ',person.birthdate, person.birthdate_estimated, person.death_date, person.gender, pa.value'}
    HAVING reason_for_starting_art IS NOT NULL
  SQL
  remove_drug_refills_and_external_consultation(end_date)
end

#load_data_into_temp_earliest_start_date(end_date, occupation = nil) ⇒ Object 



672
673
674
675
676
677
678
679
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 672

def load_data_into_temp_earliest_start_date(end_date, occupation = nil)
  load_data_into_temp_cohort_members_table(end_date)
  ActiveRecord::Base.connection.execute <<~SQL
    INSERT INTO temp_earliest_start_date
    SELECT patient_id, site_id, date_enrolled, earliest_start_date, recorded_start_date, birthdate, birthdate_estimated, death_date, gender, age_at_initiation, age_in_days, reason_for_starting_art
    FROM temp_cohort_members #{occupation_filter(occupation:, field_name: 'occupation')} #{site_manager(operator: min_filt(occupation).to_s, column: 'site_id', location: @location)}
  SQL
end

#on_art_patients_with_no_arvs_dispensations(start_date, end_date) ⇒ Object 



567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 567

def on_art_patients_with_no_arvs_dispensations(start_date, end_date)
  arv_drugs = MedicationService.arv_drugs
  arv_drugs = arv_drugs.map(&:concept_id)

  start_date.to_date
  end_date.to_date

  data = ActiveRecord::Base.connection.select_all(
    "SELECT patient_id
    FROM orders o
    INNER JOIN drug_order drg ON drg.order_id = o.order_id #{site_manager(operator: 'AND', column: 'drg.site_id',
                                                                          location: @location)}
    AND o.voided = 0
    WHERE drug_inventory_id IN(
      SELECT drug_id FROM drug
      WHERE concept_id IN(#{arv_drugs.join(',')})
    ) #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
    GROUP BY patient_id"
  )

  patient_ids = data.map { |d| d['patient_id'].to_i }

  begin
    patients = ActiveRecord::Base.connection.select_all(
      "SELECT * FROM temp_earliest_start_date WHERE patient_id NOT IN(#{patient_ids.join(',')}) #{site_manager(
        operator: 'AND', column: 'site_id', location: @location
      )}"
    )
  rescue StandardError
    raise 'Try running the revised cohort before this report'
  end

  reason_for_starting = concept('REASON FOR ART ELIGIBILITY')
  data = {}

  (patients || []).each do |p|
    patient = ::Patient.find(p['patient_id'].to_i)
    reason_for_starting = ::PatientService.reason_for_art_eligibility(patient)
    # next unless reason_for_starting.blank?

    patient_obj = ::PatientService.get_patient(patient.person)
    data[patient_obj.patient_id] = {
      arv_number: patient_obj.arv_number,
      earliest_start_date: p['earliest_start_date'],
      date_enrolled: p['date_enrolled'].to_date,
      name: patient_obj.name,
      gender: patient_obj.sex,
      birthdate: patient_obj.birth_date,
      outcome: p['outcome']
    }
  end

  data
end

#patient_with_missing_start_reasons(start_date, end_date) ⇒ Object 



528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 528

def patient_with_missing_start_reasons(start_date, end_date)
  art_patients = ActiveRecord::Base.connection.select_all(
    "SELECT e.*, #{function_manager(function: 'patient_reason_for_starting_art_text', location: @location,
                                    args: "e.patient_id, #{@location}")} reason
    FROM temp_earliest_start_date e
    WHERE date_enrolled BETWEEN '#{start_date.to_date}' AND '#{end_date.to_date}' #{site_manager(operator: 'AND',
                                                                                                 column: 'e.site_id', location: @location)}"
  )

  data = {}
  art_patients.each do |p|
    patient = ::Patient.find(p['patient_id'].to_i)
    reason_for_starting = p['reason']
    next unless reason_for_starting.blank?

    data[patient.patient_id] = {
      arv_number: patient.arv_number,
      earliest_start_date: (begin
        p['earliest_start_date'].to_date
      rescue StandardError
        nil
      end),
      date_enrolled: (begin
        p['date_enrolled'].to_date
      rescue StandardError
        nil
      end),
      name: patient.person.name,
      gender: patient.person.gender,
      birthdate: patient.person.birth_date,
      outcome: p['outcome']
    }
  end

  data
rescue StandardError
  raise 'Try running the revised cohort before this report'
end

#patients_with_pre_art_or_unknown_outcome(_start_date, _end_date) ⇒ Object 



622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 622

def patients_with_pre_art_or_unknown_outcome(_start_date, _end_date)
  begin
    patients = ActiveRecord::Base.connection.select_all(
      "SELECT e.*, cum_outcome, #{function_manager(function: 'patient_reason_for_starting_art_text',
                                                   location: @location, args: "e.patient_id, #{@location}")} reason_for_starting
      FROM temp_patient_outcomes o
      INNER JOIN temp_earliest_start_date e ON e.patient_id = o.patient_id #{site_manager(operator: 'AND',
                                                                                          column: 'e.site_id', location: @location)}
      WHERE cum_outcome LIKE '%Pre-%' OR cum_outcome LIKE '%Unknown%' #{site_manager(operator: 'AND',
                                                                                     column: 'o.site_id', location: @location)}"
    )
  rescue StandardError
    raise 'Try running the revised cohort before this report'
  end

  data = {}

  (patients || []).each do |p|
    ::Patient.find(p['patient_id'].to_i)

    patient_outcome = p['cum_outcome']
    person = ::Person.find(p['patient_id'])

    patient_obj = ::PatientService.get_patient(person)
    data[patient_obj.patient_id] = {
      arv_number: patient_obj.arv_number,
      earliest_start_date: (begin
        p['earliest_start_date'].to_date
      rescue StandardError
        nil
      end),
      date_enrolled: (begin
        p['date_enrolled'].to_date
      rescue StandardError
        nil
      end),
      name: patient_obj.name,
      gender: patient_obj.sex,
      birthdate: patient_obj.birth_date,
      reason_for_starting: p['reason_for_starting'],
      outcome: patient_outcome['outcome']
    }
  end

  data
end

#remove_drug_refills_and_external_consultation(end_date) ⇒ Object 



897
898
899
900
901
902
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 897

def remove_drug_refills_and_external_consultation(end_date)
  ActiveRecord::Base.connection.execute <<~SQL
    DELETE FROM temp_cohort_members
    WHERE #{in_manager(column: 'patient_id', values: drug_refills_and_external_consultation_list(end_date))}
  SQL
end

#update_cum_outcome(end_date) ⇒ Object 



979
980
981
982
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 979

def update_cum_outcome(end_date)
  MalawiHivProgramReports::Cohort::Outcomes.new(end_date:, definition: @outcomes_definition, location: @location)
                                           .update_cummulative_outcomes
end

#update_patient_side_effects(end_date) ⇒ Object 



1017
1018
1019
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 1017

def update_patient_side_effects(end_date)
  MalawiHivProgramReports::Cohort::SideEffects.new.update_side_effects(end_date, @location)
end

#update_tb_status(end_date) ⇒ Object 



984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_builder.rb', line 984

def update_tb_status(end_date)
  ActiveRecord::Base.connection.execute(
    'DROP TABLE IF EXISTS temp_patient_tb_status'
  )

  ActiveRecord::Base.connection.execute <<~SQL
    CREATE TABLE temp_patient_tb_status (
      patient_id INT PRIMARY KEY,
      site_id INT DEFAULT #{@location},
      tb_status INT
    )
  SQL

  ActiveRecord::Base.connection.execute('CREATE INDEX tb_status_index ON temp_patient_tb_status (tb_status)')
  ActiveRecord::Base.connection.execute('CREATE INDEX site_id_tpts ON temp_patient_tb_status (site_id)')
  ActiveRecord::Base.connection.execute('CREATE INDEX patient_id_site_id_index ON temp_patient_tb_status (patient_id, site_id)')
  ActiveRecord::Base.connection.execute('CREATE INDEX patient_id_tb_status_index ON temp_patient_tb_status (patient_id, tb_status)')

  prepare_latest_tb_status_table
  create_temp_latest_tb_status(end_date)

  ActiveRecord::Base.connection.execute <<~SQL
    INSERT INTO temp_patient_tb_status
    SELECT e.person_id, e.site_id, obs.value_coded
    FROM temp_latest_tb_status e
    INNER JOIN obs ON obs.person_id = e.person_id AND obs.voided = 0
      AND obs.concept_id = 7459 #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
      AND obs.obs_datetime = e.obs_datetime #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
    #{site_manager(operator: 'WHERE', column: 'e.site_id', location: @location)}
    GROUP BY e.person_id;
  SQL
end