Module: MalawiHivProgramReports::Adapters::Moh::Custom

Included in:
MalawiHivProgramReports::ArvRefillPeriods, Clinic::RegimenDispensationData, Cohort::Disaggregated, Cohort::Outcomes, Cohort::Regimens, Cohort::SideEffects, Cohort::Tpt, Moh::ArtCohort, Moh::CohortBuilder, Moh::CohortDisaggregated, Pepfar::MaternalStatus, Pepfar::PatientStartVl, Pepfar::RegimenSwitch, Pepfar::ScArvdisp, Pepfar::TbPrev, Pepfar::TbPrev3, Pepfar::TxCurrMmd, Pepfar::TxMl, Pepfar::TxNew, Pepfar::TxRtt, Pepfar::TxTb, Pepfar::ViralLoadCoverage2
Defined in:
app/services/malawi_hiv_program_reports/adapters/moh/custom.rb

Overview

Custom Raw SQL Queries

Instance Method Summary collapse

Instance Method Details

#cast_manager(column:, type:) ⇒ Object 



170
171
172
173
174
175
176
177
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 170

def cast_manager(column:, type:)
  adapter = ActiveRecord::Base.connection.adapter_name.downcase
  return column if adapter == 'mysql2'

  return "CAST(#{column} AS #{type})" if adapter == 'postgresql'

  column
end

#current_partition ⇒ Object 



190
191
192
193
194
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 190

def current_partition
  return "PARTITION (p#{@location})" unless @location.blank?

  ''
end

#exe_create_drill_down_table(adapter:) ⇒ Object 



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

def exe_create_drill_down_table(adapter:)
  if adapter == 'mysql2'
    ActiveRecord::Base.connection.execute <<~SQL
      CREATE TABLE IF NOT EXISTS cohort_drill_down(
        `reporting_report_design_resource_id` int(11) NOT NULL,
        `patient_id` int(11) NOT NULL,
        `site_id` int(11) DEFAULT 1,
        PRIMARY KEY (`site_id`, `patient_id`, `reporting_report_design_resource_id`),
        KEY `drilldown_report_value` (`reporting_report_design_resource_id`)
      )
    SQL
  else
    ActiveRecord::Base.connection.execute <<~SQL
      CREATE TABLE IF NOT EXISTS cohort_drill_down(
            reporting_report_design_resource_id INT NOT NULL,
            patient_id INT NOT NULL,
            site_id INT DEFAULT 1,
            PRIMARY KEY (site_id, patient_id, reporting_report_design_resource_id)
      );
    SQL
    ActiveRecord::Base.connection.execute <<~SQL
      CREATE INDEX IF NOT EXISTS drilldown_report_value ON cohort_drill_down(reporting_report_design_resource_id);
    SQL
  end
end

#exe_temp_cohort_members_table(adapter:) ⇒ Object

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



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 10

def exe_temp_cohort_members_table(adapter:)
  ActiveRecord::Base.connection.execute <<~SQL
    CREATE TABLE temp_cohort_members (
      patient_id INT PRIMARY KEY,
      site_id INT DEFAULT NULL,
      date_enrolled DATE,
      earliest_start_date DATE,
      recorded_start_date DATE DEFAULT NULL,
      birthdate DATE DEFAULT NULL,
      birthdate_estimated BOOLEAN,
      death_date DATE,
      gender VARCHAR(32),
      age_at_initiation INT DEFAULT NULL,
      age_in_days INT DEFAULT NULL,
      reason_for_starting_art INT DEFAULT NULL,
      occupation VARCHAR(255) DEFAULT NULL
    ) #{adapter == 'mysql2' ? 'ENGINE=InnoDB DEFAULT CHARSET=utf8' : ''};
  SQL
end

#exe_temp_order_details_table(adapter:) ⇒ Object 



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/adapters/moh/custom.rb', line 97

def exe_temp_order_details_table(adapter:)
  ActiveRecord::Base.connection.execute <<-SQL
  CREATE TABLE temp_order_details (
    patient_id INT NOT NULL,
    site_id INT NOT NULL,
    start_date DATE NOT NULL,
    PRIMARY KEY (patient_id, site_id)
  ) #{adapter == 'mysql2' ? 'ENGINE=InnoDB DEFAULT CHARSET=utf8' : ''};
  SQL

  # create indexes for the temp_order_details table
  ActiveRecord::Base.connection.execute <<-SQL
    CREATE INDEX tod_patient_id ON temp_order_details(patient_id);
  SQL

  ActiveRecord::Base.connection.execute <<-SQL
    CREATE INDEX tod_site_id ON temp_order_details(site_id);
  SQL
end

#exe_temp_other_patient_types(adapter:) ⇒ Object 



75
76
77
78
79
80
81
82
83
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 75

def exe_temp_other_patient_types(adapter:)
  ActiveRecord::Base.connection.execute <<~SQL
    CREATE TABLE temp_other_patient_types (
      patient_id INT NOT NULL,
      site_id INT DEFAULT NULL,
      PRIMARY KEY (patient_id)
    ) #{adapter == 'mysql2' ? 'ENGINE=InnoDB DEFAULT CHARSET=utf8' : ''};
  SQL
end

#exe_temp_register_start_date_table(adapter:) ⇒ Object 



85
86
87
88
89
90
91
92
93
94
95
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 85

def exe_temp_register_start_date_table(adapter:)
  ActiveRecord::Base.connection.execute <<-SQL
  CREATE TABLE temp_register_start_date (
    patient_id INT NOT NULL,
    site_id INT NOT NULL,
    start_date DATE NOT NULL,
    PRIMARY KEY (patient_id, site_id)
  ) #{adapter == 'mysql2' ? 'ENGINE=InnoDB DEFAULT CHARSET=utf8' : ''};
  SQL
  ActiveRecord::Base.connection.execute 'CREATE INDEX trsd_date ON temp_register_start_date (start_date)'
end

#exe_tmp_patient_table(adapter:) ⇒ Object 



56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 56

def exe_tmp_patient_table(adapter:)
  ActiveRecord::Base.connection.execute <<~SQL
    CREATE TABLE IF NOT EXISTS temp_earliest_start_date (
       patient_id INT PRIMARY KEY,
       site_id INT DEFAULT NULL,
       date_enrolled DATE,
       earliest_start_date DATE,
       recorded_start_date DATE DEFAULT NULL,
       birthdate DATE DEFAULT NULL,
       birthdate_estimated BOOLEAN,
       death_date DATE,
       gender VARCHAR(32),
       age_at_initiation INT DEFAULT NULL,
       age_in_days INT DEFAULT NULL,
       reason_for_starting_art INT DEFAULT NULL
    ) #{adapter == 'mysql2' ? 'ENGINE=InnoDB DEFAULT CHARSET=utf8' : ''};
  SQL
end

#function_manager(function:, location:, args:) ⇒ Object 



127
128
129
130
131
132
133
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 127

def function_manager(function:, location:, args:)
  adapter = ActiveRecord::Base.connection.adapter_name.downcase
  args = args.gsub('::int', '').gsub('::date', '') if adapter == 'mysql2'
  args_array = args.split(',')
  args_array = args_array[0...-1] if location.blank?
  "#{function}(#{args_array.join(',')})"
end

#group_by_columns(columns) ⇒ Object 



163
164
165
166
167
168
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 163

def group_by_columns(columns)
  adapter = ActiveRecord::Base.connection.adapter_name.downcase
  return ",#{columns}" if adapter != 'mysql2'

  ''
end

#in_manager(column:, values:, negation: false) ⇒ Object

This will be used to manage the IN and NOT IN SQL operators @column: string value @values: array or string value @negation: boolean value default to false



155
156
157
158
159
160
161
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 155

def in_manager(column:, values:, negation: false)
  ActiveRecord::Base.connection.adapter_name.downcase
  values = values.join(',') if values.is_a?(Array)
  return "#{column} NOT IN (#{values})" if negation

  "#{column} IN (#{values})"
end

#interval_manager(date:, value:, interval:, operator:) ⇒ Object

We will be returning an equivalent of interval for both of mysql and postgres @date: date column @value: integer value @interval: string value @operator: string value



140
141
142
143
144
145
146
147
148
149
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 140

def interval_manager(date:, value:, interval:, operator:)
  adapter = ActiveRecord::Base.connection.adapter_name.downcase
  return "DATE('#{date.to_date}') #{operator} INTERVAL #{value} #{interval}" if adapter == 'mysql2'

  "(DATE('#{date.to_date}') #{operator} INTERVAL '#{value} #{interval}')"
rescue StandardError
  return "DATE(#{date}) #{operator} INTERVAL #{value} #{interval}" if adapter == 'mysql2'

  "(DATE(#{date}) #{operator} INTERVAL '#{value} #{interval}')"
end

#min_filt(occupation) ⇒ Object

this is a min filter @occupation: object



198
199
200
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 198

def min_filt(occupation)
  occupation.blank? || occupation.casecmp('all').zero? ? 'WHERE' : 'AND'
end

#site_manager(operator:, column:, location:) ⇒ Object

Generates site filters @operator: string value i.e ‘AND’, ‘WHERE’, ‘OR’ @column: string value @location: integer value



121
122
123
124
125
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 121

def site_manager(operator:, column:, location:)
  return '' if location.blank?

  "#{operator} #{column} = #{location}"
end

#timestampdiff_manager(date1:, date2:, interval:) ⇒ Object

This will be used to manage the TIMESTAMPDIFF function @date1: string value @date2: string value @interval: string value like ‘YEAR’, ‘MONTH’, ‘DAY’



183
184
185
186
187
188
 
# File 'app/services/malawi_hiv_program_reports/adapters/moh/custom.rb', line 183

def timestampdiff_manager(date1:, date2:, interval:)
  adapter = ActiveRecord::Base.connection.adapter_name.downcase
  return "TIMESTAMPDIFF(#{interval}, #{date1}, #{date2})" if adapter == 'mysql2'

  "DATE_PART('#{interval}', #{date2}::timestamp - #{date1}::timestamp)" if adapter == 'postgresql'
end