Module: MalawiHivProgramReports::Utils::CommonSqlQueryUtils

Included in:
ArvRefillPeriods, Clinic::AppointmentsReport, Clinic::ExternalConsultationClients, Clinic::LimsResults, Clinic::OutcomeList, Clinic::PregnantPatients, Clinic::RegimenDispensationData, Clinic::RegimensAndFormulations, Clinic::Retention, Clinic::TptOutcome, Clinic::TxRtt, Clinic::ViralLoad, Clinic::ViralLoadResults, Clinic::VlCollection, Cohort::Tpt, Moh::CohortBuilder, Moh::CumulativeCohort, Moh::CumulativeOutcome, Moh::MohTpt, Pepfar::MaternalStatus, Pepfar::ScArvdisp, Pepfar::TbPrev3, Pepfar::TxCurrMmd, Pepfar::TxMl, Pepfar::TxRtt, Pepfar::ViralLoadCoverage2
Defined in:
app/services/malawi_hiv_program_reports/utils/common_sql_query_utils.rb

Instance Method Summary collapse

Instance Method Details

#current_occupation_query ⇒ Object 



45
46
47
48
49
50
51
52
53
54
55
56
 
# File 'app/services/malawi_hiv_program_reports/utils/common_sql_query_utils.rb', line 45

def current_occupation_query
  ActiveRecord::Base.connection.adapter_name.downcase
  <<~SQL
    SELECT a.person_id, a.value, a.site_id
    FROM person_attribute #{current_partition} a
    LEFT OUTER JOIN person_attribute #{current_partition} b
    ON a.person_attribute_id = b.person_attribute_id
    AND a.date_created < b.date_created
    AND b.voided = 0
    WHERE b.person_attribute_id IS NULL AND a.person_attribute_type_id = 13 AND a.voided = 0
  SQL
end

#external_client_query(end_date:) ⇒ Object 



27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
# File 'app/services/malawi_hiv_program_reports/utils/common_sql_query_utils.rb', line 27

def external_client_query(end_date:)
  end_date = ActiveRecord::Base.connection.quote(end_date)
  <<~SQL
    SELECT obs.person_id FROM obs,
    (SELECT person_id, Max(obs_datetime) AS obs_datetime, concept_id FROM obs
    WHERE concept_id IN (SELECT concept_id FROM concept_name WHERE name = 'Type of patient' AND voided = 0)
    AND DATE(obs_datetime) <= #{end_date}
    AND voided = 0
    GROUP BY person_id,concept_id) latest_record
    WHERE obs.person_id = latest_record.person_id
    AND obs.concept_id = latest_record.concept_id
    AND obs.obs_datetime = latest_record.obs_datetime
    AND obs.value_coded IN (SELECT concept_id FROM concept_name WHERE name = 'Drug refill' || name = 'External consultation')
    AND obs.voided = 0
    #{site_manager(operator: 'AND', column: 'obs.site_id', location: @location)}
  SQL
end

#occupation_filter(occupation:, field_name:, table_name: '', include_clause: true) ⇒ Object 



14
15
16
17
18
19
20
21
22
23
24
25
 
# File 'app/services/malawi_hiv_program_reports/utils/common_sql_query_utils.rb', line 14

def occupation_filter(occupation:, field_name:, table_name: '', include_clause: true)
  clause = 'WHERE' if include_clause
  table_name = "#{table_name}." unless table_name.blank?
  return '' if occupation.blank?
  return '' if occupation == 'All'
  if occupation == 'Military'
    return "#{clause} #{table_name}#{field_name} IN ('#{occupation}', 'MDF Reserve', 'MDF Retired', 'Soldier', 'Soldier/Police')"
  end
  return unless occupation == 'Civilian'

  "#{clause} #{table_name}#{field_name} NOT IN ('Military', 'MDF Reserve', 'MDF Retired', 'Soldier', 'Soldier/Police')"
end

#partition_by_site ⇒ Object 



58
59
60
61
62
 
# File 'app/services/malawi_hiv_program_reports/utils/common_sql_query_utils.rb', line 58

def partition_by_site
  @partition_by_site ||= Location.all.map do |location|
    "PARTITION p#{location.id} VALUES IN (#{location.id}) ENGINE = InnoDB"
  end.join(', ')
end

#process_occupation(start_date:, end_date:, occupation:, location:, definition: 'moh') ⇒ Object 



7
8
9
10
11
12
 
# File 'app/services/malawi_hiv_program_reports/utils/common_sql_query_utils.rb', line 7

def process_occupation(start_date:, end_date:, occupation:, location:, definition: 'moh')
  return if occupation.blank?

  MalawiHivProgramReports::Moh::CohortBuilder.new(outcomes_definition: definition, location:).init_temporary_tables(start_date, end_date,
                                                                                                                    occupation)
end