Class: MalawiHivProgramReports::Clinic::Retention

Inherits:
Object
  • Object
show all
Includes:
Utils::CommonSqlQueryUtils
Defined in:
app/services/malawi_hiv_program_reports/clinic/retention.rb

Constant Summary collapse

DAYS_IN_MONTH = 
28
MONTHS = 
[1, 3, 6].freeze

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Utils::CommonSqlQueryUtils

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

Constructor Details

#initialize(start_date:, end_date:, **kwargs) ⇒ Retention

Returns a new instance of Retention.



15
16
17
18
19
20
21
22
 
# File 'app/services/malawi_hiv_program_reports/clinic/retention.rb', line 15

def initialize(start_date:, end_date:, **kwargs)
  @start_date = start_date.to_s
  @end_date = end_date.to_s
  @use_filing_number = ::GlobalProperty.find_by(property: 'use.filing.numbers')
                                       &.property_value
                                       &.casecmp?('true')
  @occupation = kwargs[:occupation]
end

Instance Attribute Details

#end_date ⇒ Object (readonly)

Returns the value of attribute end_date.



8
9
10
 
# File 'app/services/malawi_hiv_program_reports/clinic/retention.rb', line 8

def end_date
  @end_date
end

#start_date ⇒ Object (readonly)

Returns the value of attribute start_date.



8
9
10
 
# File 'app/services/malawi_hiv_program_reports/clinic/retention.rb', line 8

def start_date
  @start_date
end

Instance Method Details

#all_patients(as_of:) ⇒ Object 



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
 
# File 'app/services/malawi_hiv_program_reports/clinic/retention.rb', line 117

def all_patients(as_of:)
  start_date = ActiveRecord::Base.connection.quote(self.start_date)
  as_of = ActiveRecord::Base.connection.quote(as_of)

  ::Order.find_by_sql(
    <<~SQL
      SELECT initial_order.patient_id AS patient_id,
             DATE(initial_order.start_date) AS start_date,
             patient_identifier.identifier AS arv_number,
             disaggregated_age_group(p.birthdate, DATE('#{@end_date}')) age_group,
             p.gender gender
      FROM orders initial_order
      INNER JOIN encounter initial_encounter ON initial_encounter.encounter_id = initial_order.encounter_id AND initial_encounter.program_id = 1
      INNER JOIN person p ON p.person_id = initial_encounter.patient_id
      LEFT JOIN patient_identifier ON patient_identifier.patient_id = initial_order.patient_id AND patient_identifier.identifier_type = #{patient_identifier_type_id}
      LEFT JOIN (#{current_occupation_query}) a ON a.person_id = initial_order.patient_id
      WHERE initial_order.start_date BETWEEN #{as_of} AND #{start_date}
        AND initial_order.voided = 0
        AND initial_order.auto_expire_date IS NOT NULL
        AND initial_order.order_type_id = #{drug_order_type_id}
        AND p.voided = 0 #{%w[Military Civilian].include?(@occupation) ? 'AND' : ''} #{occupation_filter(occupation: @occupation, field_name: 'value', table_name: 'a', include_clause: false)}
        AND initial_order.patient_id NOT IN (
          SELECT orders.patient_id
          FROM orders
            INNER JOIN encounter ON encounter.encounter_id = orders.encounter_id AND encounter.program_id = 1 AND encounter.voided = 0
          WHERE start_date < #{as_of} AND order_type_id = #{drug_order_type_id} AND orders.voided = 0
        )
      GROUP BY initial_order.patient_id
    SQL
  )
end

#drug_order_type_id ⇒ Object

::Order.find_by_sql(

  <<~SQL
    SELECT initial_order.patient_id AS patient_id,
           initial_order.start_date AS start_date,
           patient_identifier.identifier AS arv_number,
           disaggregated_age_group(p.birthdate, DATE('#{@end_date}')) age_group,
           p.gender gender
    FROM orders initial_order
      INNER JOIN encounter initial_encounter ON initial_encounter.encounter_id = initial_order.encounter_id AND initial_encounter.program_id = 1
      INNER JOIN person p ON p.person_id = initial_encounter.patient_id
      LEFT JOIN patient_identifier ON patient_identifier.patient_id = initial_order.patient_id AND patient_identifier.identifier_type = #{patient_identifier_type_id}
    WHERE initial_order.start_date BETWEEN #{as_of} AND #{start_date}
      AND initial_order.voided = 0
      AND initial_order.auto_expire_date IS NOT NULL
      AND initial_order.order_type_id = #{drug_order_type_id}
      AND p.voided = 0
      AND initial_order.start_date = (
        SELECT MIN(start_date) FROM orders
        WHERE patient_id = initial_order.patient_id
          AND start_date BETWEEN #{as_of} AND #{start_date}
          AND order_type_id = #{drug_order_type_id}
          AND voided = 0
      )
      AND initial_order.patient_id NOT IN (
        SELECT orders.patient_id
        FROM orders
          INNER JOIN encounter ON encounter.encounter_id = orders.encounter_id AND encounter.program_id = 1
        WHERE start_date < #{as_of} AND order_type_id = #{drug_order_type_id} AND orders.voided = 0
      )
    GROUP BY initial_order.patient_id
  SQL
)

end



232
233
234
 
# File 'app/services/malawi_hiv_program_reports/clinic/retention.rb', line 232

def drug_order_type_id
  @drug_order_type_id ||= ::OrderType.find_by_name('Drug order').order_type_id
end

#find_patients_retention_period(patients) ⇒ Object 



61
62
63
64
65
66
67
68
69
70
71
 
# File 'app/services/malawi_hiv_program_reports/clinic/retention.rb', line 61

def find_patients_retention_period(patients)
  patients.each do |patient|
    retention_period = MONTHS.find do |period|
      (start_date..end_date).cover?((patient.start_date + period.months).to_s)
    end

    next unless retention_period

    yield retention_period, patient
  end
end

#find_report ⇒ Object 



24
25
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
 
# File 'app/services/malawi_hiv_program_reports/clinic/retention.rb', line 24

def find_report
  matched_patients = MONTHS.each_with_object({}) do |month, hash|
    hash[month] = { retained: [], all: [] }
  end

  find_patients_retention_period(retained_patients(as_of: Date.parse(start_date) - MONTHS.max.months)) do |period, patient|
    matched_patients[period][:retained] << {
      patient_id: patient.patient_id,
      arv_number: patient.arv_number,
      start_date: patient.start_date,
      gender: begin
        patient.gender.upcase.first
      rescue StandardError
        nil
      end,
      age_group: patient.age_group,
      end_date: patient.start_date + period.months
    }
  end

  find_patients_retention_period(all_patients(as_of: Date.parse(start_date) - MONTHS.max.months)) do |period, patient|
    matched_patients[period][:all] << {
      patient_id: patient.patient_id,
      arv_number: patient.arv_number,
      gender: begin
        patient.gender.upcase.first
      rescue StandardError
        nil
      end,
      age_group: patient.age_group,
      start_date: patient.start_date
    }
  end

  matched_patients
end

#patient_identifier_type_id ⇒ Object 



236
237
238
239
240
241
242
243
 
# File 'app/services/malawi_hiv_program_reports/clinic/retention.rb', line 236

def patient_identifier_type_id
  return @patient_identifier_type_id if @patient_identifier_type_id

  identifier_type_name = @use_filing_number ? 'Filing Number' : 'ARV Number'
  identifier_type = ::PatientIdentifierType.find_by_name!(identifier_type_name)

  @patient_identifier_type_id ||= ActiveRecord::Base.connection.quote(identifier_type.id)
end

#retained_patients(as_of:) ⇒ Object

Pull all patients who started medication before the current reporting period but after the given ‘as_of` date and have any dispensation that ends in the current reporting period… That’s a mouthful woah!!!



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
 
# File 'app/services/malawi_hiv_program_reports/clinic/retention.rb', line 76

def retained_patients(as_of:)
  start_date = ActiveRecord::Base.connection.quote(self.start_date)
  end_date = ActiveRecord::Base.connection.quote(self.end_date)
  as_of = ActiveRecord::Base.connection.quote(as_of)
  ::Order.find_by_sql(
    <<~SQL
      SELECT
        initial_orders.patient_id AS patient_id,
        DATE(initial_orders.start_date) AS start_date,
        last_orders.auto_expire_date AS auto_expire_date,
        patient_identifier.identifier AS arv_number,
        disaggregated_age_group(p.birthdate, DATE('#{@end_date}')) age_group,
        p.gender gender
      FROM orders initial_orders
      INNER JOIN encounter initial_encounter ON initial_encounter.encounter_id = initial_orders.encounter_id AND initial_encounter.voided = 0 AND initial_encounter.program_id = 1
      INNER JOIN person p ON p.person_id = initial_orders.patient_id AND p.voided = 0
      LEFT JOIN patient_identifier ON patient_identifier.patient_id = initial_orders.patient_id AND patient_identifier.identifier_type = #{patient_identifier_type_id}
      INNER JOIN (
        SELECT last_order.patient_id, MAX(last_order.auto_expire_date) AS auto_expire_date
          FROM orders last_order
          INNER JOIN encounter last_encounter ON last_encounter.encounter_id = last_order.encounter_id AND last_encounter.voided = 0 AND last_encounter.program_id = 1
          WHERE last_order.auto_expire_date BETWEEN #{start_date} AND #{end_date}
          AND last_order.order_type_id = #{drug_order_type_id}
        AND last_order.voided = 0
          GROUP BY last_order.patient_id
      ) last_orders ON initial_orders.patient_id = last_orders.patient_id
      LEFT JOIN (#{current_occupation_query}) a ON a.person_id = initial_orders.patient_id
      WHERE initial_orders.start_date BETWEEN #{as_of} AND #{start_date}
      AND initial_orders.order_type_id = #{drug_order_type_id} #{%w[Military Civilian].include?(@occupation) ? 'AND' : ''} #{occupation_filter(occupation: @occupation, field_name: 'value', table_name: 'a', include_clause: false)}
      AND initial_orders.auto_expire_date IS NOT NULL
      AND initial_orders.patient_id NOT IN (
        SELECT o.patient_id
        FROM orders o
        INNER JOIN encounter e ON e.encounter_id = o.encounter_id AND e.voided = 0 AND e.program_id = 1
        WHERE o.order_type_id =  #{drug_order_type_id} AND o.start_date < #{as_of} AND o.auto_expire_date IS NOT NULL AND o.voided = 0
      )
      GROUP BY initial_orders.patient_id
    SQL
  )
end