# frozen_string_literal: true module MalawiHivProgramReports module Pepfar ## Viral Load Coverage Report # 1. given the start and end dates, this report will go back 12 months using the end date # 2. pick all clients that are due in the mentioned period # 3. the picked clients should also include those that are new on ART 6 months before the end date # 4. for the sample drawns available pick the latest sample drawn within the reporting period # 5. for the results pick the latest result within the reporting period class ViralLoadCoverage2 include Utils include MalawiHivProgramReports::Utils::CommonSqlQueryUtils include MalawiHivProgramReports::Adapters::Moh::Custom include MalawiHivProgramReports::Utils::ModelUtils attr_reader :start_date, :end_date, :location def initialize(start_date:, end_date:, **kwargs) @start_date = start_date&.to_date raise InvalidParameterError, 'start_date is required' unless @start_date @end_date = end_date&.to_date || (@start_date + 12.months) raise InvalidParameterError, "start_date can't be greater than end_date" if @start_date > @end_date @occupation = kwargs.delete(:occupation) @type = kwargs.delete(:application) @location = kwargs.delete(:location) end def find_report report = init_report build_report(report) report end def vl_maternal_status(patient_list) return { FP: [], FBf: [] } if patient_list.blank? pregnant = pregnant_women(patient_list).map { |woman| woman['person_id'].to_i } return { FP: pregnant, FBf: [] } if (patient_list - pregnant).blank? feeding = breast_feeding(patient_list - pregnant).map { |woman| woman['person_id'].to_i } { FP: pregnant, FBf: feeding } end # rubocop:disable Metrics/AbcSize # rubocop:disable Metrics/CyclomaticComplexity # rubocop:disable Metrics/MethodLength def process_due_people @clients = [] start = Time.now results = clients_on_art # get all clients that are females from results @maternal_status = vl_maternal_status(results.filter_map do |patient| patient['patient_id'] if patient['gender'] == 'F' end) if @type.blank? || @type == 'poc' Parallel.each(results, in_threads: 20) do |patient| process_client_eligibility(patient) end end results.each { |patient| process_client_eligibility(patient) } if @type == 'emastercard' end_time = Time.now Rails.logger.info "Time taken to process #{results.length} clients: #{end_time - start} seconds. These are the clients returned: #{@clients.length}" @clients end # rubocop:enable Metrics/AbcSize # rubocop:enable Metrics/CyclomaticComplexity # rubocop:enable Metrics/MethodLength private # rubocop:disable Metrics/AbcSize # rubocop:disable Metrics/MethodLength # rubocop:disable Metrics/CyclomaticComplexity # rubocop:disable Metrics/PerceivedComplexity # rubocop:disable Layout/LineLength def process_client_eligibility(patient) result = extra_information(patient['patient_id']) patient['defaulter_date'] = result['defaulter_date'] patient['current_regimen'] = result['current_regimen'] patient['art_start_date'] = result['art_start_date'] patient['maternal_status'] = if @maternal_status[:FP].include?(patient['patient_id']) 'FP' else (@maternal_status[:FBf].include?(patient['patient_id']) ? 'FBf' : nil) end return if !patient['defaulter_date'].blank? && (patient['defaulter_date'].to_date < end_date - 12.months) return if result['art_start_date'].blank? return if result['art_start_date'].to_date > end_date - 6.months return if remove_adverse_outcome_patient?(patient) @clients << patient end def remove_adverse_outcome_patient?(patient) return false unless adverse_outcomes.include?(patient['state'].to_i) last_date = patient['vl_order_date'] || patient['art_start_date'] if patient['vl_order_date'].present? && last_date.to_date >= start_date && last_date.to_date <= end_date return false end length = 12 length = 6 if patient['maternal_status'] == 'FP' length = 6 if patient['maternal_status'] == 'FBf' length = 6 if /P/i.match?(patient['current_regimen'].to_s) if patient['vl_order_date'] && patient['vl_order_date'].to_date >= end_date - 12.months && patient['vl_order_date'].to_date <= end_date return false end return false if last_date.to_date + length.months < patient['outcome_date'].to_date true end def pregnant_women(patient_list) ActiveRecord::Base.connection.select_all <<~SQL SELECT o.person_id, o.value_coded FROM obs #{current_partition} o LEFT JOIN obs #{current_partition} a ON a.person_id = o.person_id AND a.obs_datetime > o.obs_datetime AND a.concept_id IN (#{pregnant_concepts.to_sql}) AND a.voided = 0 AND a.obs_datetime >= DATE(#{ActiveRecord::Base.connection.quote(start_date)}) AND a.obs_datetime < DATE(#{ActiveRecord::Base.connection.quote(end_date)}) + INTERVAL 1 DAY WHERE a.obs_id is null AND o.obs_datetime >= DATE(#{ActiveRecord::Base.connection.quote(start_date)}) AND o.obs_datetime < DATE(#{ActiveRecord::Base.connection.quote(end_date)}) + INTERVAL 1 DAY AND o.voided = 0 AND o.concept_id in (#{pregnant_concepts.to_sql}) AND o.person_id IN (#{patient_list.join(',')}) AND o.value_coded IN (#{yes_concepts.join(',')}) GROUP BY o.person_id SQL end def breast_feeding(patient_list) ActiveRecord::Base.connection.select_all <<~SQL SELECT o.person_id, o.value_coded FROM obs #{current_partition} o LEFT JOIN obs #{current_partition} a ON a.person_id = o.person_id AND a.obs_datetime > o.obs_datetime AND a.concept_id IN (#{breast_feeding_concepts.to_sql}) AND a.voided = 0 AND a.obs_datetime >= DATE(#{ActiveRecord::Base.connection.quote(start_date)}) AND a.obs_datetime < DATE(#{ActiveRecord::Base.connection.quote(end_date)}) + INTERVAL 1 DAY WHERE a.obs_id is null AND o.obs_datetime >= DATE(#{ActiveRecord::Base.connection.quote(start_date)}) AND o.obs_datetime < DATE(#{ActiveRecord::Base.connection.quote(end_date)}) + INTERVAL 1 DAY AND o.voided = 0 AND o.concept_id IN (#{breast_feeding_concepts.to_sql}) AND o.person_id IN (#{patient_list.join(',')}) AND o.value_coded IN (#{yes_concepts.join(',')}) GROUP BY o.person_id SQL end def build_report(report) refresh_outcomes_table load_tx_curr_into_report(report, create_patients_alive_and_on_art_query) clients = process_due_people clients.each do |patient| report[patient['age_group']][patient['gender'].to_sym][:due_for_vl] << patient['patient_id'] end load_patient_tests_into_report(report, clients.map { |patient| patient['patient_id'] }) end def refresh_outcomes_table MalawiHivProgramReports::Moh::CohortBuilder.new(outcomes_definition: 'pepfar', location: @location) .init_temporary_tables(@start_date, @end_date, nil) end def create_patients_alive_and_on_art_query ActiveRecord::Base.connection.select_all( <<~SQL SELECT tpo.patient_id, LEFT(tesd.gender, 1) AS gender, disaggregated_age_group(tesd.birthdate, DATE('#{end_date.to_date}')) age_group FROM cdr_temp_patient_outcomes #{current_partition} tpo INNER JOIN cdr_temp_cohort_members #{current_partition} tesd ON tesd.patient_id = tpo.patient_id WHERE tpo.cum_outcome = 'On antiretrovirals' SQL ) end def load_tx_curr_into_report(report, patients) report.each_key do |age_group| %i[M F].each do |gender| report[age_group][gender][:tx_curr] ||= [] report[age_group][gender][:tx_curr] = populate_tx_curr(patients, age_group, gender) || [] end end end def populate_tx_curr(patients, age_group, gender) patients.select do |patient| (patient['age_group'] == age_group && patient['gender'].to_sym == gender) && patient['patient_id'] end&.map { |a| a['patient_id'] } end def load_patient_tests_into_report(report, clients) find_patients_with_viral_load(clients).each do |patient| age_group = patient['age_group'] gender = patient['gender'].to_sym reason_for_test = (patient['reason_for_test'] || 'Routine').match?(/Routine/i) ? :routine : :targeted report[age_group][gender][:drawn][reason_for_test] << patient['patient_id'] next unless patient['result_value'] if patient['result_value'].casecmp?('LDL') report[age_group][gender][:low_vl][reason_for_test] << patient['patient_id'] elsif patient['result_value'].to_i < 1000 report[age_group][gender][:low_vl][reason_for_test] << patient['patient_id'] else report[age_group][gender][:high_vl][reason_for_test] << patient['patient_id'] end end end # rubocop:enable Metrics/AbcSize # rubocop:enable Metrics/MethodLength ## This method prepares the response structure for the report def init_report pepfar_age_groups.each_with_object({}) do |age_group, report| report[age_group] = %i[F M].each_with_object({}) do |gender, hash| hash[gender] = { due_for_vl: [], drawn: { routine: [], targeted: [] }, high_vl: { routine: [], targeted: [] }, low_vl: { routine: [], targeted: [] } } end end end def due_for_viral_load ActiveRecord::Base.connection.select_all <<~SQL (#{find_patients_with_overdue_viral_load}) UNION (#{find_patients_due_for_initial_viral_load}) SQL end def adverse_outcomes @adverse_outcomes ||= ActiveRecord::Base.connection.select_all( <<~SQL SELECT pws.program_workflow_state_id state FROM program_workflow #{current_partition} pw INNER JOIN concept_name #{current_partition} pcn ON pcn.concept_id = pw.concept_id AND pcn.concept_name_type = 'FULLY_SPECIFIED' AND pcn.voided = 0 INNER JOIN program_workflow_state #{current_partition} pws ON pws.program_workflow_id = pw.program_workflow_id AND pws.retired = 0 INNER JOIN concept_name #{current_partition} cn ON cn.concept_id = pws.concept_id AND cn.concept_name_type = 'FULLY_SPECIFIED' AND cn.voided = 0 WHERE pw.program_id = 1 AND pw.retired = 0 AND pws.terminal = 1 SQL ).map { |state| state['state'] } end def clients_on_art ActiveRecord::Base.connection.select_all <<~SQL SELECT ab.patient_id, disaggregated_age_group(p.birthdate, DATE(#{ActiveRecord::Base.connection.quote(end_date)})) AS age_group, p.birthdate, p.gender, pid.identifier AS arv_number, current_state.state, current_state.start_date outcome_date, current_order.start_date vl_order_date FROM orders #{current_partition} ab INNER JOIN person #{current_partition} p ON p.person_id = ab.patient_id AND p.voided = 0 #{site_manager(operator: 'AND', column: 'p.site_id', location: @location)} INNER JOIN drug_order #{current_partition} dor ON dor.order_id = ab.order_id AND dor.quantity > 0 #{site_manager(operator: 'AND', column: 'dor.site_id', location: @location)} INNER JOIN arv_drug #{current_partition} ad ON dor.drug_inventory_id = ad.drug_id INNER JOIN patient_program #{current_partition} pp ON pp.patient_id = ab.patient_id AND pp.voided = 0 AND pp.program_id = 1 #{site_manager(operator: 'AND', column: 'pp.site_id', location: @location)} INNER JOIN ( SELECT a.site_id, a.patient_program_id, a.state, a.start_date, a.end_date FROM patient_state #{current_partition} a LEFT OUTER JOIN patient_state #{current_partition} b ON a.patient_program_id = b.patient_program_id #{site_manager(operator: 'AND', column: 'b.site_id', location: @location)} AND a.start_date < b.start_date #{site_manager(operator: 'AND', column: 'a.site_id', location: @location)} AND b.voided = 0 WHERE b.patient_program_id IS NULL AND a.end_date IS NULL AND a.voided = 0 ) current_state ON current_state.patient_program_id = pp.patient_program_id #{site_manager(operator: 'AND', column: 'current_state.site_id', location: @location)} LEFT OUTER JOIN orders #{current_partition} b ON ab.patient_id = b.patient_id AND ab.order_id = b.order_id AND ab.auto_expire_date < b.auto_expire_date AND b.voided = 0 AND b.order_type_id = 1 #{site_manager(operator: 'AND', column: 'b.site_id', location: @location)} LEFT JOIN (#{current_occupation_query}) a ON a.person_id = ab.patient_id #{site_manager(operator: 'AND', column: 'a.site_id', location: @location)} LEFT JOIN patient_identifier #{current_partition} pid ON pid.patient_id = pp.patient_id AND pid.identifier_type IN (#{pepfar_patient_identifier_type.to_sql}) AND pid.voided = 0 #{site_manager(operator: 'AND', column: 'pid.site_id', location: @location)} LEFT JOIN ( SELECT ab.patient_id, MAX(ab.start_date) start_date FROM orders #{current_partition} ab INNER JOIN concept_name #{current_partition} ON concept_name.concept_id = ab.concept_id AND concept_name.name IN ('Blood', 'DBS (Free drop to DBS card)', 'DBS (Using capillary tube)', '50:50 Normal Plasma') AND concept_name.voided = 0 #{site_manager(operator: 'AND', column: 'ab.site_id', location: @location)} LEFT OUTER JOIN orders #{current_partition} b ON ab.patient_id = b.patient_id AND ab.order_id = b.order_id AND ab.start_date < b.start_date AND b.voided = 0 #{site_manager(operator: 'AND', column: 'b.site_id', location: @location)} WHERE b.patient_id IS NULL AND ab.voided = 0 AND ab.order_type_id = 4 AND ab.start_date < DATE(#{ActiveRecord::Base.connection.quote(end_date)}) + INTERVAL 1 DAY GROUP BY ab.patient_id ) current_order ON current_order.patient_id = ab.patient_id WHERE b.patient_id IS NULL AND ab.voided = 0 #{%w[Military Civilian].include?(@occupation) ? 'AND' : ''} #{occupation_filter(occupation: @occupation, field_name: 'value', table_name: 'a', include_clause: false)} AND ab.start_date < DATE(#{ActiveRecord::Base.connection.quote(end_date)}) + INTERVAL 1 DAY AND p.person_id NOT IN (#{drug_refills_and_external_consultation_list}) #{site_manager(operator: 'AND', column: 'ab.site_id', location: @location)} AND ((current_state.state IN (#{adverse_outcomes.join(',')}) AND current_state.start_date >= (DATE(#{ActiveRecord::Base.connection.quote(end_date)}) - INTERVAL 12 MONTH)) OR current_state.state IN (7, 1, 87, 120, 136)) GROUP BY ab.patient_id; SQL end def extra_information(patient_id) ActiveRecord::Base.connection.select_one <<~SQL SELECT #{function_manager(function: 'patient_current_regimen', location: @location, args: "#{patient_id}, DATE(#{ActiveRecord::Base.connection.quote(end_date)}), #{@location}")} AS current_regimen, #{function_manager(function: 'date_antiretrovirals_started', location: @location, args: "#{patient_id}, DATE(#{ActiveRecord::Base.connection.quote(end_date)}), #{@location}")} AS art_start_date, #{function_manager(function: 'current_pepfar_defaulter_date', location: @location, args: "#{patient_id}, DATE(#{ActiveRecord::Base.connection.quote(end_date)}), #{@location}")} AS defaulter_date SQL end ## # Find all patients that are on treatment with at least one VL before end of reporting period. def find_patients_with_viral_load(clients) ActiveRecord::Base.connection.select_all <<~SQL SELECT orders.patient_id, disaggregated_age_group(patient.birthdate, DATE(#{ActiveRecord::Base.connection.quote(end_date)})) AS age_group, patient.birthdate, patient.gender, patient_identifier.identifier AS arv_number, orders.start_date AS order_date, COALESCE(orders.discontinued_date, orders.start_date) AS sample_draw_date, COALESCE(reason_for_test_value.name, reason_for_test.value_text) AS reason_for_test, result.value_modifier AS result_modifier, COALESCE(result.value_numeric, result.value_text) AS result_value FROM orders #{current_partition} INNER JOIN person #{current_partition} patient ON patient.person_id = orders.patient_id AND patient.voided = 0 #{site_manager(operator: 'AND', column: 'patient.site_id', location: @location)} INNER JOIN order_type #{current_partition} ON order_type.order_type_id = orders.order_type_id AND order_type.name = 'Lab' AND order_type.retired = 0 INNER JOIN concept_name #{current_partition} ON concept_name.concept_id = orders.concept_id AND concept_name.name IN ('Blood', 'DBS (Free drop to DBS card)', 'DBS (Using capillary tube)', 'Plasma') AND concept_name.voided = 0 LEFT JOIN obs #{current_partition} AS reason_for_test ON reason_for_test.order_id = orders.order_id AND reason_for_test.concept_id IN (SELECT concept_id FROM concept_name #{current_partition} WHERE name LIKE 'Reason for test' AND voided = 0) AND reason_for_test.voided = 0 #{site_manager(operator: 'AND', column: 'reason_for_test.site_id', location: @location)} LEFT JOIN concept_name #{current_partition} AS reason_for_test_value ON reason_for_test_value.concept_id = reason_for_test.value_coded AND reason_for_test_value.voided = 0 LEFT JOIN obs #{current_partition} AS result ON result.order_id = orders.order_id AND result.concept_id IN (SELECT concept_id FROM concept_name #{current_partition} WHERE name LIKE 'HIV Viral load' AND voided = 0) AND result.voided = 0 AND (result.value_text IS NOT NULL OR result.value_numeric IS NOT NULL) #{site_manager(operator: 'AND', column: 'result.site_id', location: @location)} INNER JOIN ( /* Get the latest order dates for each patient */ SELECT orders.patient_id, MAX(orders.start_date) AS start_date FROM orders #{current_partition} INNER JOIN order_type #{current_partition} ON order_type.order_type_id = orders.order_type_id AND order_type.name = 'Lab' AND order_type.retired = 0 INNER JOIN concept_name #{current_partition} ON concept_name.concept_id = orders.concept_id AND concept_name.name IN ('Blood', 'DBS (Free drop to DBS card)', 'DBS (Using capillary tube)', 'Plasma') AND concept_name.voided = 0 WHERE orders.start_date < DATE(#{ActiveRecord::Base.connection.quote(end_date)}) + INTERVAL 1 DAY #{site_manager(operator: 'AND', column: 'orders.site_id', location: @location)} AND orders.start_date >= DATE(#{ActiveRecord::Base.connection.quote(start_date)}) - INTERVAL 12 MONTH AND orders.voided = 0 GROUP BY orders.patient_id ) AS latest_patient_order_date ON latest_patient_order_date.patient_id = orders.patient_id AND latest_patient_order_date.start_date = orders.start_date LEFT JOIN patient_identifier #{current_partition} ON patient_identifier.patient_id = orders.patient_id AND patient_identifier.identifier_type IN (#{pepfar_patient_identifier_type.to_sql}) AND patient_identifier.voided = 0 WHERE orders.start_date < DATE(#{ActiveRecord::Base.connection.quote(end_date)}) + INTERVAL 1 DAY AND orders.start_date >= DATE(#{ActiveRecord::Base.connection.quote(start_date)}) - INTERVAL 12 MONTH AND orders.voided = 0 AND orders.patient_id IN (#{clients.push(0).join(',')}) #{site_manager(operator: 'AND', column: 'orders.site_id', location: @location)} GROUP BY orders.patient_id SQL end def yes_concepts @yes_concepts ||= ::ConceptName.where(name: 'Yes').select(:concept_id).map do |record| record['concept_id'].to_i end end def pregnant_concepts @pregnant_concepts ||= ::ConceptName.where(name: ['Is patient pregnant?', 'patient pregnant']) .select(:concept_id) end def breast_feeding_concepts @breast_feeding_concepts ||= ::ConceptName.where(name: ['Breast feeding?', 'Breast feeding', 'Breastfeeding']) .select(:concept_id) end def encounter_types @encounter_types ||= ::EncounterType.where(name: ['HIV CLINIC CONSULTATION', 'HIV STAGING']) .select(:encounter_type_id) end end end end