CREATE PROCEDURE `sp_create_hiv_report_table`()
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DECLARE out_id BIGINT(20) default NULL;
DECLARE out_district_patient_id VARCHAR(20);
DECLARE out_patient_name VARCHAR(100);
DECLARE out_encounter_date DATE;
DECLARE out_cd4_done BIT(1);
DECLARE out_cd4_date DATE;
DECLARE out_cd4_result INTEGER(11);
DECLARE out_hgb_date DATE;
DECLARE out_hgb_result FLOAT(9,3);
DECLARE out_who_screen VARCHAR(20);
DECLARE out_referral_to_art VARCHAR(30);
DECLARE out_pmtct_regimen VARCHAR(30);
DECLARE out_ega_weeks INTEGER(11);
DECLARE out_date_next_visit DATE;
DROP TABLE IF EXISTS `hiv_report`;
CREATE TABLE IF NOT EXISTS `hiv_report` (
  `id` BIGINT(20),
  `district_patient_id` VARCHAR(20),
  `patient_name` VARCHAR(100),
  `encounter_date` DATE,
  `cd4_done` BIT(1),
  `cd4_date` DATE,
  `cd4_result` INTEGER(11),
  `hgb_date` DATE,
  `hgb_result` FLOAT,
  `regimen_visit_date` DATE,
  `who_screen` VARCHAR(20),
  `referral_to_art` VARCHAR(30),
  `pmtct_regimen` VARCHAR(30),
  `ega_weeks` VARCHAR(8),
  `date_next_visit` DATE,
  `site_id` INTEGER(11),
  `encounter_id` INTEGER(11),
  `lab_type` char(20),
  KEY `id` (`id`)
);

INSERT INTO hiv_report (id, district_patient_id, patient_name, encounter_date,
cd4_done, regimen_visit_date, who_screen, referral_to_art, pmtct_regimen, site_id, encounter_id, lab_type)
select patient.id, patient.district_patient_id,
CONCAT_WS(' ',patient.first_name,patient.surname) AS patient_name, regimen_visit_date,
cd4tested, regimen_visit_date, who_stage, referred_art_clinic, regimen, patient_status.site_id,
encounter.id AS encounter_id, 'a'
from zeprs.patient, zeprs.patient_status, zeprs.encounter, zeprs.arvregimen
where patient.id = patient_status.id
and arvregimen.id = encounter.id
and patient.id = encounter.patient_id
and patient.hiv_positive > 0
order by patient.id, encounter.id;

INSERT INTO hiv_report (id, district_patient_id, patient_name, encounter_date,
cd4_result, cd4_date, site_id, encounter_id, lab_type)
select patient.id, patient.district_patient_id,
CONCAT_WS(' ',patient.first_name,patient.surname) AS patient_name, dateLabRequest,
cd4count, dateLabRequest, patient_status.site_id, encounter.id AS encounter_id, 'c'
from zeprs.patient, zeprs.patient_status, zeprs.encounter, zeprs.labtest
where patient.id = patient_status.id
and labtest.id = encounter.id
and patient.id = encounter.patient_id
and patient.hiv_positive > 0
and labType = 3042
and labtest.cd4count is not null
order by patient.id, encounter.id;


INSERT INTO hiv_report (id, district_patient_id, patient_name, encounter_date,
hgb_date, hgb_result, site_id, encounter_id, lab_type)
select patient.id, patient.district_patient_id,
CONCAT_WS(' ',patient.first_name,patient.surname) AS patient_name, dateLabRequest,
dateLabRequest, resultsNumeric, patient_status.site_id,
encounter.id AS encounter_id, 'h'
from zeprs.patient, zeprs.patient_status, zeprs.encounter, zeprs.labtest
where patient.id = patient_status.id
and labtest.id = encounter.id
and patient.id = encounter.patient_id
and patient.hiv_positive > 0
and (labType = 2925 OR labType = 2926)
order by patient.id, encounter.id;


END;