Class: MalawiHivProgramReports::Moh::CohortDisaggregated

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

Overview

This class is used to add additional cohort disaggregated data rubocop:disable Metrics/ClassLength

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::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(name:, type:, start_date:, end_date:, rebuild:, **kwargs) ⇒ CohortDisaggregated

Returns a new instance of CohortDisaggregated.



11
12
13
14
15
16
17
18
19
20
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 11

def initialize(name:, type:, start_date:, end_date:, rebuild:, **kwargs)
  @name = name
  @type = type
  @start_date = start_date
  @end_date = end_date
  @rebuild = rebuild
  @occupation = kwargs[:occupation]
  @location = kwargs[:location]
  @adapter = ActiveRecord::Base.connection.adapter_name.downcase
end

Instance Method Details

#big_insert(data, age_group) ⇒ Object 



592
593
594
595
596
597
598
599
600
601
602
603
604
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 592

def big_insert(data, age_group)
  insert_array = []
  (data || []).each do |r|
    insert_array << "(#{r['patient_id']}, '#{age_group}', #{r['site_id']})"
  end

  return if insert_array.blank?

  ActiveRecord::Base.connection.execute <<~SQL
    INSERT INTO temp_disaggregated (patient_id, age_group, site_id)
    VALUES #{insert_array.join(',')};
  SQL
end

#build_report ⇒ Object 



26
27
28
29
30
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 26

def build_report
  builder = CohortDisaggregatedBuilder.new(outcomes_definition: 'moh', location: @location,
                                           occupation: @occupation)
  builder.build(nil, @start_date, @end_date)
end

#create_mysql_female_maternal_status ⇒ Object 



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
443
444
445
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
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 376

def create_mysql_female_maternal_status
  ActiveRecord::Base.connection.execute <<~SQL
    DROP FUNCTION IF EXISTS female_maternal_status;
  SQL

  ActiveRecord::Base.connection.execute <<~SQL
    CREATE FUNCTION female_maternal_status(my_patient_id int, end_datetime datetime, my_site_id int) RETURNS VARCHAR(20)
    DETERMINISTIC
    BEGIN

    DECLARE breastfeeding_date DATETIME;
    DECLARE pregnant_date DATETIME;
    DECLARE maternal_status VARCHAR(20);
    DECLARE obs_value_coded INT(11);


    SET @reason_for_starting = (SELECT concept_id FROM concept_name WHERE LOWER(name) = LOWER('Reason for ART eligibility') LIMIT 1);

    SET @pregnant_concepts := (SELECT GROUP_CONCAT(concept_id) FROM concept_name WHERE LOWER(name) IN (LOWER('Is patient pregnant?'), LOWER('Patient pregnant')));
    SET @breastfeeding_concept := (SELECT GROUP_CONCAT(concept_id) FROM concept_name WHERE LOWER(name) = 'Breastfeeding');

    SET pregnant_date = (SELECT MAX(obs_datetime) FROM obs WHERE concept_id IN (@pregnant_concepts) AND voided = 0 AND person_id = my_patient_id AND obs_datetime <= end_datetime AND site_id = my_site_id);
    SET breastfeeding_date = (SELECT MAX(obs_datetime) FROM obs WHERE concept_id IN (@breastfeeding_concept) AND voided = 0 AND person_id = my_patient_id AND obs_datetime <= end_datetime AND site_id = my_site_id);

    IF pregnant_date IS NULL THEN
      SET pregnant_date = (SELECT MAX(obs_datetime) FROM obs WHERE concept_id = @reason_for_starting AND voided = 0 AND person_id = my_patient_id AND obs_datetime <= end_datetime AND value_coded IN(1755) AND site_id = my_site_id);
    END IF;

    IF breastfeeding_date IS NULL THEN
      SET breastfeeding_date = (SELECT MAX(obs_datetime) FROM obs WHERE concept_id = @reason_for_starting AND voided = 0 AND person_id = my_patient_id AND obs_datetime <= end_datetime AND value_coded IN(834,5632) AND site_id = my_site_id);
    END IF;

    IF pregnant_date IS NULL AND breastfeeding_date IS NULL THEN SET maternal_status = "FNP";
    ELSEIF pregnant_date IS NOT NULL AND breastfeeding_date IS NOT NULL THEN SET maternal_status = "Unknown";
    ELSEIF pregnant_date IS NULL AND breastfeeding_date IS NOT NULL THEN SET maternal_status = "Check BF";
    ELSEIF pregnant_date IS NOT NULL AND breastfeeding_date IS NULL THEN SET maternal_status = "Check FP";
    END IF;

    IF maternal_status = 'Unknown' THEN

      IF breastfeeding_date <= pregnant_date THEN
        SET obs_value_coded = (SELECT value_coded FROM obs WHERE concept_id IN(@pregnant_concepts) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = pregnant_date AND site_id = my_site_id LIMIT 1);
        IF obs_value_coded = 1065 THEN SET maternal_status = 'FP';
        ELSEIF obs_value_coded = 1066 THEN SET maternal_status = 'FNP';
        END IF;
      END IF;

      IF breastfeeding_date > pregnant_date THEN
        SET obs_value_coded = (SELECT value_coded FROM obs WHERE concept_id IN(@breastfeeding_concept) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = breastfeeding_date AND site_id = my_site_id LIMIT 1);
        IF obs_value_coded = 1065 THEN SET maternal_status = 'FBf';
        ELSEIF obs_value_coded = 1066 THEN SET maternal_status = 'FNP';
        END IF;
      END IF;

      IF DATE(breastfeeding_date) = DATE(pregnant_date) AND maternal_status = 'FNP' THEN
        SET obs_value_coded = (SELECT value_coded FROM obs WHERE concept_id IN(@breastfeeding_concept) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = breastfeeding_date AND site_id = my_site_id LIMIT 1);
        IF obs_value_coded = 1065 THEN SET maternal_status = 'FBf';
        ELSEIF obs_value_coded = 1066 THEN SET maternal_status = 'FNP';
        END IF;
      END IF;
    END IF;

    IF maternal_status = 'Check FP' THEN

      SET obs_value_coded = (SELECT value_coded FROM obs WHERE concept_id IN(@pregnant_concepts) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = pregnant_date AND site_id = my_site_id LIMIT 1);
      IF obs_value_coded = 1065 THEN SET maternal_status = 'FP';
      ELSEIF obs_value_coded = 1066 THEN SET maternal_status = 'FNP';
      END IF;

      IF obs_value_coded IS NULL THEN
        SET obs_value_coded = (SELECT GROUP_CONCAT(value_coded) FROM obs WHERE concept_id IN(7563) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = pregnant_date AND site_id = my_site_id);
        IF obs_value_coded IN(1755) THEN SET maternal_status = 'FP';
        END IF;
      END IF;

      IF maternal_status = 'Check FP' THEN SET maternal_status = 'FNP';
      END IF;
    END IF;

    IF maternal_status = 'Check BF' THEN

      SET obs_value_coded = (SELECT value_coded FROM obs WHERE concept_id IN(@breastfeeding_concept) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = breastfeeding_date AND site_id = my_site_id LIMIT 1);
      IF obs_value_coded = 1065 THEN SET maternal_status = 'FBf';
      ELSEIF obs_value_coded = 1066 THEN SET maternal_status = 'FNP';
      END IF;

      IF obs_value_coded IS NULL THEN
        SET obs_value_coded = (SELECT GROUP_CONCAT(value_coded) FROM obs WHERE concept_id IN(7563) AND voided = 0 AND person_id = my_patient_id AND obs_datetime = breastfeeding_date AND site_id = my_site_id);
        IF obs_value_coded IN(834,5632) THEN SET maternal_status = 'FBf';
        END IF;
      END IF;

      IF maternal_status = 'Check BF' THEN SET maternal_status = 'FNP';
      END IF;
    END IF;



    RETURN maternal_status;
    END;
  SQL
end

#disaggregated(quarter, age_group) ⇒ Object 



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
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 51

def disaggregated(quarter, age_group)
  if quarter == 'pepfar'
    start_date = @start_date
    end_date = @end_date

    begin
      records = ActiveRecord::Base.connection.select_one("SELECT count(*) rec_count FROM temp_patient_outcomes #{site_manager(
        operator: 'AND', column: 'site_id', location: @location
      )}")
      @rebuild = true if records['rec_count'].to_i < 1
    rescue StandardError => e
      Rails.logger.error "Error: #{e.message}"
      initialize_disaggregated
      rebuild_outcomes 'pepfar'
    end

    if @rebuild
      initialize_disaggregated
      rebuild_outcomes 'pepfar'
    end

  else
    start_date, end_date = generate_start_date_and_end_date(quarter)

    if @rebuild
      initialize_disaggregated
      art_service = CohortBuilder.new
      art_service.init_temporary_tables(@start_date, @end_date, @occupation)
      art_service.update_tb_status(end_date)
    end
  end

  tmp = get_age_groups(age_group, start_date, end_date)

  # A hack to get female that were pregnant / breastfeeding at the beginning of the reporting period + those are currently the same state
  if age_group == 'Pregnant'
    tmp_arr = []
    (tmp || []).each do |data|
      begin
        date_enrolled = data['date_enrolled'].to_date
      rescue StandardError => e
        Rails.logger.error "Error: #{e.message}"
        raise data.inspect
      end
      earliest_start_date = begin
        data['earliest_start_date']
      rescue StandardError
        date_enrolled
      end

      imstaus = data['initial_maternal_status']
      mstatus = data['mstatus']

      if (date_enrolled >= start_date && date_enrolled <= end_date) && imstaus == 'FP' && (date_enrolled == earliest_start_date)
        tmp_arr << data
      elsif mstatus == 'FP'
        tmp_arr << data
      end
    end

    tmp = tmp_arr
  end

  if age_group == 'Breastfeeding'
    tmp_arr = []
    (tmp || []).each do |data|
      begin
        date_enrolled = data['date_enrolled'].to_date
      rescue StandardError
        raise data.inspect
      end
      earliest_start_date = begin
        data['earliest_start_date']
      rescue StandardError
        date_enrolled
      end

      imstaus = data['initial_maternal_status']
      mstatus = data['mstatus']

      if (date_enrolled >= start_date && date_enrolled <= end_date) && imstaus == 'FBf' && (date_enrolled == earliest_start_date)
        tmp_arr << data
      elsif mstatus == 'FBf'
        tmp_arr << data
      end
    end

    tmp = tmp_arr
  end
  # ........................... Hack ends .......... Will clean up later

  on_art = []
  all_clients = []
  all_clients_outcomes = {}

  (tmp || []).each do |pat|
    patient_id = pat['patient_id'].to_i
    outcome = pat['outcome']

    on_art << patient_id if outcome == 'On antiretrovirals'
    all_clients << patient_id
    all_clients_outcomes[patient_id] = outcome
  end

  list = {}

  if all_clients.blank? && %w[Breastfeeding Pregnant].include?(age_group)
    list[age_group] = {}
    list[age_group]['F'] = {
      tx_new: [], tx_curr: [],
      tx_screened_for_tb: [],
      tx_given_ipt: []
    }
    return list
  elsif all_clients.blank?
    return {}
  end

  big_insert tmp, age_group if /year|month/i.match?(age_group)

  (tmp || []).each do |r|
    gender = r['gender']&.first || 'Unknown'
    patient_id = r['patient_id'].to_i
    tx_new, tx_curr, tx_given_ipt, tx_screened_for_tb = get_numbers(r, age_group, start_date, end_date,
                                                                    all_clients_outcomes)

    list[age_group] = {} if list[age_group].blank?

    if list[age_group][gender].blank?
      list[age_group][gender] = {
        tx_new: [], tx_curr: [],
        tx_screened_for_tb: [],
        tx_given_ipt: []
      }
    end

    list[age_group][gender][:tx_new] << r['patient_id'] if tx_new
    list[age_group][gender][:tx_curr] << r['patient_id'] if tx_curr
    list[age_group][gender][:tx_given_ipt] << r['patient_id'] if tx_given_ipt
    list[age_group][gender][:tx_screened_for_tb] << r['patient_id'] if tx_screened_for_tb

    date_enrolled = r['date_enrolled'].to_date

    if gender == 'F' && all_clients_outcomes[patient_id] == 'On antiretrovirals'
      insert_female_maternal_status(patient_id, age_group, end_date)
    elsif gender == 'F' && (date_enrolled >= start_date && date_enrolled <= end_date)
      insert_female_maternal_status(patient_id, age_group, end_date)
    end
  end

  list
end

#find_report ⇒ Object 



22
23
24
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 22

def find_report
  build_report
end

#generate_start_date_and_end_date(quarter) ⇒ Object 



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
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 204

def generate_start_date_and_end_date(quarter)
  return [@start_date, @end_date] if quarter == 'Custom'

  quarter, quarter_year = quarter.humanize.split

  quarter_start_dates = [
    "#{quarter_year}-01-01".to_date,
    "#{quarter_year}-04-01".to_date,
    "#{quarter_year}-07-01".to_date,
    "#{quarter_year}-10-01".to_date
  ]

  quarter_end_dates = [
    "#{quarter_year}-03-31".to_date,
    "#{quarter_year}-06-30".to_date,
    "#{quarter_year}-09-30".to_date,
    "#{quarter_year}-12-31".to_date
  ]

  current_quarter   = (quarter.match(/\d+/).to_s.to_i - 1)
  quarter_beginning = quarter_start_dates[current_quarter]
  quarter_ending    = quarter_end_dates[current_quarter]

  [quarter_beginning, quarter_ending]
end

#get_age_groups(age_group, _start_date, _end_date) ⇒ Object 



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
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 306

def get_age_groups(age_group, _start_date, _end_date)
  if age_group != 'Pregnant' && age_group != 'FNP' && age_group != 'Not pregnant' && age_group != 'Breastfeeding'

    age_group_patients = ActiveRecord::Base.connection.select_all <<~SQL
      SELECT
        patient_id, `disaggregated_age_group`(date(birthdate), date('#{@end_date}')) AS age_group
      FROM temp_earliest_start_date e #{site_manager(operator: 'WHERE', column: 'e.site_id', location: @location)}
      GROUP BY e.patient_id
      HAVING #{@adapter == 'mysql2' ? 'age_group' : "`disaggregated_age_group`(date(birthdate), date('#{@end_date}'))"} = '#{age_group}';
    SQL
    age_group_patient_ids = [0]
    (age_group_patients || []).each do |patient|
      age_group_patient_ids << patient['patient_id'].to_i
    end

    results = ActiveRecord::Base.connection.select_all <<~SQL
      SELECT o.cum_outcome AS outcome, e.*
      FROM temp_earliest_start_date e
      LEFT JOIN temp_patient_outcomes o ON o.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
      WHERE date_enrolled <= '#{@end_date}' #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
      AND e.patient_id IN(#{age_group_patient_ids.join(',')})
      GROUP BY #{@adapter == 'mysql2' ? 'e.patient_id' : 'o.cum_outcome, e.*'};
    SQL
  elsif age_group == 'Pregnant'
    create_mysql_female_maternal_status
    results = ActiveRecord::Base.connection.select_all <<~SQL
      SELECT
        e.*, maternal_status AS mstatus,
        t2.initial_maternal_status,
        t3.cum_outcome AS outcome
      FROM temp_earliest_start_date e
      INNER JOIN temp_disaggregated t2 ON t2.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't2.site_id', location: @location)}
      INNER JOIN temp_patient_outcomes t3 ON t3.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't3.site_id', location: @location)}
      WHERE maternal_status = 'FP' OR initial_maternal_status = 'FP' #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
      GROUP BY #{@adapter == 'mysql2' ? 'e.patient_id' : 't3.cum_outcome, maternal_status, t2.initial_maternal_status, e.*'};
    SQL

  elsif age_group == 'Breastfeeding'
    create_mysql_female_maternal_status
    results = ActiveRecord::Base.connection.select_all <<~SQL
      SELECT
        e.*, maternal_status AS mstatus,
        initial_maternal_status,
        t3.cum_outcome AS outcome
      FROM temp_earliest_start_date e
      INNER JOIN temp_disaggregated t2 ON t2.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't2.site_id', location: @location)}
      INNER JOIN temp_patient_outcomes t3 ON t3.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't3.site_id', location: @location)}
      WHERE maternal_status = 'FBf' OR initial_maternal_status = 'FBf' #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
      GROUP BY #{@adapter == 'mysql2' ? 'e.patient_id' : 't3.cum_outcome, maternal_status, initial_maternal_status, e.*'};
    SQL

  elsif age_group == 'FNP'
    create_mysql_female_maternal_status
    results = ActiveRecord::Base.connection.select_all <<~SQL
      SELECT
        e.*, maternal_status AS mstatus,
        initial_maternal_status,
        t3.cum_outcome AS outcome
      FROM temp_earliest_start_date e
      INNER JOIN temp_disaggregated t2 ON t2.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't2.site_id', location: @location)}
      INNER JOIN temp_patient_outcomes t3 ON t3.patient_id = e.patient_id #{site_manager(operator: 'AND', column: 't3.site_id', location: @location)}
      WHERE maternal_status = 'FNP' #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
      GROUP BY #{@adapter == 'mysql2' ? 'e.patient_id' : 't3.cum_outcome, maternal_status, initial_maternal_status, e.*'};
    SQL

  end

  results
end

#get_numbers(data, age_group, start_date, end_date, outcomes) ⇒ Object 



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
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 264

def get_numbers(data, age_group, start_date, end_date, outcomes)
  patient_id = data['patient_id'].to_i
  tx_new = false
  tx_curr = false
  tx_screened_for_tb = false
  tx_given_ipt = false
  outcome = outcomes[patient_id]

  begin
    date_enrolled = data['date_enrolled'].to_date
  rescue StandardError
    raise data.inspect
  end
  earliest_start_date = begin
    data['earliest_start_date'].to_date
  rescue StandardError
    nil
  end

  if date_enrolled >= start_date && date_enrolled <= end_date
    tx_new = true if !earliest_start_date.blank? && (date_enrolled == earliest_start_date)

    tx_curr = true if outcome == 'On antiretrovirals'
  elsif outcome == 'On antiretrovirals'
    tx_curr = true
  end

  if age_group == 'Pregnant'
    tx_new = false if data['initial_maternal_status'] != 'FP' && tx_new

    tx_curr = false if data['mstatus'] != 'FP'
  end

  if age_group == 'Breastfeeding'
    tx_new = false if data['initial_maternal_status'] != 'FBf' && tx_new

    tx_curr = false if data['mstatus'] != 'FBf'
  end

  [tx_new, tx_curr, tx_given_ipt, tx_screened_for_tb]
end

#given_ipt(my_patient_id, age_group, start_date, end_date) ⇒ Object 



247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 247

def given_ipt(my_patient_id, age_group, start_date, end_date)
  data = ActiveRecord::Base.connection.select_one <<~SQL
    SELECT patient_given_ipt(#{my_patient_id},
      '#{start_date.to_date}', '#{end_date.to_date}', #{@location}) AS given;
  SQL

  given = data['given'].to_i

  ActiveRecord::Base.connection.execute <<~SQL
    UPDATE temp_disaggregated SET given_ipt =  #{given} ,
    age_group = '#{age_group}'
    WHERE patient_id = #{my_patient_id} AND site_id = #{@location};
  SQL

  given
end

#initialize_disaggregated ⇒ Object 



32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 32

def initialize_disaggregated
  ActiveRecord::Base.connection.execute('DROP TABLE IF EXISTS temp_disaggregated')

  ActiveRecord::Base.connection.execute(
    'CREATE TABLE IF NOT EXISTS temp_disaggregated (
       patient_id INTEGER NOT NULL,
       age_group VARCHAR(20),
       initial_maternal_status VARCHAR(10),
       maternal_status VARCHAR(10),
       given_ipt INT(1),
       screened_for_tb INT(1),
       site_id INT NOT NULL DEFAULT 1,
       PRIMARY KEY(patient_id, site_id)
    );'
  )

  { temp_disaggregated: 'created' }
end

#insert_female_maternal_status(patient_id, age_group, end_date) ⇒ Object 



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
527
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
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 484

def insert_female_maternal_status(patient_id, age_group, end_date)
  encounter_types = []
  encounter_types << encounter_type('HIV CLINIC CONSULTATION').encounter_type_id
  encounter_types << encounter_type('HIV STAGING').encounter_type_id

  pregnant_concepts = []
  pregnant_concepts << concept_name('Is patient pregnant?').concept_id
  pregnant_concepts << concept_name('patient pregnant').concept_id

  results = ActiveRecord::Base.connection.select_all <<~SQL
    SELECT person_id, obs.value_coded value_coded
    FROM obs obs
    INNER JOIN encounter enc ON enc.encounter_id = obs.encounter_id AND enc.encounter_type IN(#{encounter_types.join(',')}) AND enc.voided = 0 AND enc.program_id = 1 #{site_manager(operator: 'AND', column: 'enc.site_id', location: @location)}
    WHERE obs.person_id = #{patient_id}
    AND obs.obs_datetime <= '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}'
    AND obs.concept_id IN(#{pregnant_concepts.join(',')})
    AND obs.voided = 0 #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
    AND DATE(obs.obs_datetime) = (SELECT MAX(DATE(o.obs_datetime)) FROM obs o
                  INNER JOIN encounter e ON e.encounter_id = o.encounter_id
                  AND e.program_id = 1 AND e.voided = 0 #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
                  WHERE o.concept_id IN(#{pregnant_concepts.join(',')})
                  AND o.voided = 0 AND o.person_id = obs.person_id #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
                  AND o.obs_datetime <= '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}')
    GROUP BY #{@adapter == 'mysql2' ? 'obs.person_id' : 'obs.person_id, obs.value_coded'}
    HAVING #{@adapter == 'mysql2' ? 'value_coded' : 'obs.value_coded'} = 1065
    ORDER BY MAX(obs.obs_datetime) DESC
  SQL

  female_maternal_status = results.blank? ? 'FNP' : 'FP'

  if female_maternal_status == 'FNP'

    breastfeeding_concepts = []
    breastfeeding_concepts <<  concept_name('Breast feeding?').concept_id
    breastfeeding_concepts <<  concept_name('Breast feeding').concept_id
    breastfeeding_concepts <<  concept_name('Breastfeeding').concept_id

    results2 = ActiveRecord::Base.connection.select_all <<~SQL
      SELECT person_id, obs.value_coded value_coded
      FROM obs obs
      INNER JOIN encounter enc ON enc.encounter_id = obs.encounter_id AND enc.encounter_type IN(#{encounter_types.join(',')}) AND enc.voided = 0 AND enc.program_id = 1 #{site_manager(operator: 'AND', column: 'enc.site_id', location: @location)}
      WHERE obs.person_id =#{patient_id}
      AND obs.obs_datetime <= '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}'
      AND obs.concept_id IN(#{breastfeeding_concepts.join(',')})
      AND obs.voided = 0 #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
      AND DATE(obs.obs_datetime) = (SELECT MAX(DATE(o.obs_datetime)) FROM obs o
                    INNER JOIN encounter e ON e.encounter_id = o.encounter_id
                    AND e.program_id = 1 AND e.voided = 0 #{site_manager(operator: 'AND', column: 'e.site_id', location: @location)}
                    WHERE o.concept_id IN(#{breastfeeding_concepts.join(',')}) AND o.voided = 0
                    AND o.person_id = obs.person_id #{site_manager(operator: 'AND', column: 'o.site_id', location: @location)}
                    AND o.obs_datetime <='#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}')
      GROUP BY #{@adapter == 'mysql2' ? 'obs.person_id' : 'obs.person_id, obs.value_coded'}
      HAVING #{@adapter == 'mysql2' ? 'value_coded' : 'obs.value_coded'} = 1065
      ORDER BY MAX(obs.obs_datetime) DESC
    SQL

    female_maternal_status = results2.blank? ? 'FNP' : 'FBf'
  end

  results = ActiveRecord::Base.connection.select_all <<~SQL
    SELECT person_id, obs.value_coded value_coded
    FROM obs obs
    INNER JOIN encounter enc ON enc.encounter_id = obs.encounter_id AND enc.encounter_type IN(#{encounter_types.join(',')}) AND enc.voided = 0 AND enc.program_id = 1 #{site_manager(operator: 'AND', column: 'enc.site_id', location: @location)}
    WHERE obs.person_id = #{patient_id}
    AND obs.obs_datetime <= '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}'
    AND obs.concept_id IN(#{pregnant_concepts.join(',')})
    AND obs.voided = 0 #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
    AND DATE(obs.obs_datetime) = (SELECT DATE(es.earliest_start_date) FROM temp_earliest_start_date es
                                  WHERE es.patient_id = obs.person_id #{site_manager(operator: 'AND', column: 'es.site_id', location: @location)})
    GROUP BY #{@adapter == 'mysql2' ? 'obs.person_id' : 'obs.person_id, obs.value_coded'}
    HAVING #{@adapter == 'mysql2' ? 'value_coded' : 'obs.value_coded'} = 1065
    ORDER BY MAX(obs.obs_datetime) DESC
  SQL

  initial_female_maternal_status = results.blank? ? 'FNP' : 'FP'

  if initial_female_maternal_status == 'FNP'

    breastfeeding_concepts = []
    breastfeeding_concepts <<  concept_name('Breast feeding?').concept_id
    breastfeeding_concepts <<  concept_name('Breast feeding').concept_id
    breastfeeding_concepts <<  concept_name('Breastfeeding').concept_id

    results2 = ActiveRecord::Base.connection.select_all <<~SQL
      SELECT person_id, obs.value_coded value_coded
      FROM obs obs
      INNER JOIN encounter enc ON enc.encounter_id = obs.encounter_id AND enc.encounter_type IN(#{encounter_types.join(',')}) AND enc.voided = 0 AND enc.program_id = 1 #{site_manager(operator: 'AND', column: 'enc.site_id', location: @location)}
      WHERE obs.person_id =#{patient_id}
      AND obs.obs_datetime <= '#{end_date.to_date.strftime('%Y-%m-%d 23:59:59')}'
      AND obs.concept_id IN(#{breastfeeding_concepts.join(',')})
      AND obs.voided = 0 #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
      AND DATE(obs.obs_datetime) = (SELECT DATE(es.earliest_start_date) FROM temp_earliest_start_date es
                                    WHERE es.patient_id = obs.person_id #{site_manager(operator: 'AND', column: 'es.site_id', location: @location)})
      GROUP BY #{@adapter == 'mysql2' ? 'obs.person_id' : 'obs.person_id, obs.value_coded'}
      HAVING #{@adapter == 'mysql2' ? 'value_coded' : 'obs.value_coded'} = 1065
      ORDER BY MAX(obs.obs_datetime) DESC
    SQL

    initial_female_maternal_status = results2.blank? ? 'FNP' : 'FBf'
  end

  ActiveRecord::Base.connection.execute <<~SQL
    UPDATE temp_disaggregated SET maternal_status =  '#{female_maternal_status}',
      initial_maternal_status = '#{initial_female_maternal_status}',
       age_group = '#{age_group}' WHERE patient_id = #{patient_id} AND site_id = #{@location};
  SQL
end

#rebuild_outcomes(report_type) ⇒ Object 



479
480
481
482
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 479

def rebuild_outcomes(report_type)
  MalawiHivProgramReports::Moh::CohortBuilder.new(outcomes_definition: report_type, location: @location).init_temporary_tables(@start_date,
                                                                                                                               @end_date, @occupation)
end

#screened_for_tb(my_patient_id, age_group, start_date, end_date) ⇒ Object 



230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
 
# File 'app/services/malawi_hiv_program_reports/moh/cohort_disaggregated.rb', line 230

def screened_for_tb(my_patient_id, age_group, start_date, end_date)
  data = ActiveRecord::Base.connection.select_one <<~SQL
    SELECT patient_screened_for_tb(#{my_patient_id},
      '#{start_date.to_date}', '#{end_date.to_date}', #{@location}) AS screened;
  SQL

  screened = data['screened'].to_i

  ActiveRecord::Base.connection.execute <<~SQL
    UPDATE temp_disaggregated SET screened_for_tb =  #{screened},
    age_group = '#{age_group}'
    WHERE patient_id = #{my_patient_id} AND site_id = #{@location};
  SQL

  screened
end