Skip to content

Instantly share code, notes, and snippets.

@denniskigen

denniskigen/mmp.sql

Last active July 17, 2020 13:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save denniskigen/b808818002e2a2ef18e400626cf5b442 to your computer and use it in GitHub Desktop.
Save denniskigen/b808818002e2a2ef18e400626cf5b442 to your computer and use it in GitHub Desktop.
Download ZIP
Stored procedure for the MM program
Raw
mmp.sql
CREATE DEFINER=`etl_user`@`%` PROCEDURE `generate_flat_multiple_myeloma_treatment_v1_2`(IN query_type varchar(50), IN queue_number int, IN queue_size int, IN cycle_size int)
BEGIN
SET @primary_table := "flat_multiple_myeloma_treatment";
SET @query_type := query_type;
SET @total_rows_written := 0;
SET @encounter_types := "(89,90,141)";
SET @clinical_encounter_types := "(89,90,141)";
SET @non_clinical_encounter_types := "(-1)";
SET @other_encounter_types := "(-1)";
SET @start := NOW();
SET @table_version := "flat_multiple_myeloma_treatment_v1.2";
SET session sort_buffer_size = 512000000;
SET @sep := " ## ";
SET @boundary := "!!";
SET @last_date_created := (SELECT max(max_date_created) FROM etl.flat_obs);
CREATE TABLE IF NOT EXISTS flat_multiple_myeloma_treatment (
date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
person_id INT,
encounter_id INT,
encounter_type INT,
encounter_date DATE,
visit_id INT,
location_id SMALLINT,
gender CHAR(100),
age SMALLINT,
identifiers VARCHAR(255),
person_name VARCHAR(255),
death_date DATE,
phone_number VARCHAR(50),
county VARCHAR(100),
purpose_of_visit_triage SMALLINT,
covered_by_nhif SMALLINT,
nhif_status SMALLINT,
type_of_cancer SMALLINT,
freetext_cancer_type VARCHAR(100),
bp_systolic DECIMAL(5, 1),
bp_diastolic DECIMAL(5, 1),
heart_rate DECIMAL(5, 1),
respiratory_rate DECIMAL(5, 1),
temperature DECIMAL(5, 1),
weight_kg DECIMAL(5, 1),
height_cm DECIMAL(5, 1),
blood_oxygen_saturation DECIMAL(5, 1),
body_mass_index DECIMAL(5, 1),
body_surface_area DECIMAL(5, 1),
illness_currently_being_treated_for SMALLINT, -- 6475 diabetes hepatitis HIV hypertension tb other
freetext_illness_currently_being_treated_for VARCHAR(100), -- 1915
medications_currently_being_used SMALLINT, -- 6789 antibiotics antiocoagulants anticonvulsants antihypertensive anti TB chemo factor IX factor VIII HAART hydroxyurea insulin melphalan opioid analgesics prednisone thalidomide vitamins other
other_medications_currently_being_used VARCHAR(100), -- 1915
allergic_to_penicillin SMALLINT, -- 6011 yes no
-- REPEATED CONCEPT; hence VARCHAR
specific_allergic_reaction_to_penicillin VARCHAR(150), -- 2085 angioedema cough pruritis rash swelling vomiting wheeze others
allergic_to_sulfa SMALLINT, -- 6012 yes no
specific_allergic_reaction_to_sulfa VARCHAR(150), -- 2085 angioedema cough pruritis rash swelling vomiting wheeze others
-- REPEATING GROUP
freetext_allergy SMALLINT, -- 1083
name_of_drug_or_product_allergic_to VARCHAR(100), -- 2089
specific_reaction_to_this_drug_or_product VARCHAR(100), -- 2085
-- MULTIPLE MYELOMA INITIAL ENCOUNTER
purpose_of_visit_mm_initial SMALLINT, -- 1834
referred_by SMALLINT, -- 6749 Self 978 Referred from clinic 7037 Non-MTRH Consultant 6479 Other 5622
religion SMALLINT, -- 6285 Catholic 8399 Protestant 8400 Muslim 8401 Traditionalist 8402 Hindu 8403 Buddhist 8404 None 1107 Other 5622
current_marital_status SMALLINT, -- 1054 N/A 1175 Divorced 1058 Living with a partner 1060 Married (monogamous) 5555 Married (polygamous) 6290 Never married 1057 Separated 1056 Widowed 1059
no_of_household_members SMALLINT, -- 6801
no_of_children SMALLINT, -- 1728
are_you_or_your_partner_currently_using_any_form_of_fp SMALLINT, -- 6683, yes no stopped
currently_breastfeeding SMALLINT, -- 2056 yes 1065 no 1066
clinic_travel_time SMALLINT, -- 5605 less than 30 mins 1049 30-60 mins 1050 1-2hrs 1051 More than 2 hours 1052
mode_of_transport_to_clinic SMALLINT, -- 6468 bicycle 6395 boat 7552 boda 6580 matatu 6416 motorcycle 6396 private car 6471 taxi 6470 walking 6415
main_occupation SMALLINT, -- 1972 casual worker farmer fishing formal employment health care provider house wife student self-employed teacher truck-driver unemployed other 5622
freetext_main_occupation VARCHAR(150),
average_monthly_income SMALLINT, -- 7003 less than 1000 1001-2000 2001-3000 3001-5000 5001-10000 More than 10000
level_of_education SMALLINT, -- 1605 no formal education pre-primary primary secondary school tertiary
next_of_kin_level_of_education SMALLINT, -- 8749 no formal education pre-primary primary secondary school tertiary
smokes_cigarettes SMALLINT, -- 2065 1066 no 1065 yes stopped
uses_tobacco SMALLINT, -- 7973 1066 no 1065 yes stopped
drinks_alcohol_sometimes SMALLINT, -- 1684 1066 no 1065 yes stopped
family_history_of_cancer_or_chronic_illness_history SMALLINT, -- 6802 1066 no 1065 yes
specific_chronic_illness VARCHAR(150), -- 1915
family_member_with_chronic_illness SMALLINT, -- 8254 father mother maternal aunt maternal uncle maternal grandparent paternal aunt paternal uncle paternal grandparent sibling
history_of_surgery VARCHAR(150),
freetext_history_of_surgery VARCHAR(150),
chief_complaint_review_of_systems SMALLINT, -- 5219 asymptomatic 5006 / symptomatic 1068
pain SMALLINT, -- 6613 yes 1065 no 1066
pain_score SMALLINT, -- 7080 between 0 - 10
-- MULTI-SELECTS
heent_findings VARCHAR(100), -- 1070 normal hearing difficulties swallowing diff. vision diff. other
-- evaluate need for listing other non coded concepts from looking at their frequency of occurrence.
general_findings VARCHAR(100), -- 1069 normal fatigue fever jaundice night sweats pain weightloss yellowing of eyes
cardiopulmonary_findings VARCHAR(100), -- 1071 normal chest pain cough shortnesss of breath shortness of breath at rest shortness of breath with exertion
-- evaluate whether to add chest pain duration and chest pain location based on frequency of occurrence of chest pain finding.
gastrointestinal_findings VARCHAR(100), -- 1078 normal abdominal pain bleeding per rectum constipation diarrhea dysphagia Jaundice Melena poor appetite
-- evaluate whether to add abdominal pain duration and abdominal pain location based on frequency of occurrence of abdominal pain finding.
genitourinary_findings VARCHAR(100), -- 1080 normal dysuria increased urine urgency increased urine volume priapism reduced urine volume
last_menstrual_period DATETIME, -- 1836
musculoskeletal_findings VARCHAR(100), -- 1081 normal edema of legs joint pain joint swelling leg ulcers muscle pain
-- evaluate whether to add muscle pain duration and muscle pain location based on frequency of occurrence of muscle pain finding.
chief_complaints_physical_exam VARCHAR(150),
freetext_chief_complaint VARCHAR(150),
ecog_performance_index SMALLINT,
general_exam VARCHAR(150),
heent_exam VARCHAR(150),
chest_exam VARCHAR(150),
heart_exam VARCHAR(150),
abdomen_exam VARCHAR(150),
urogenital_exam VARCHAR(150),
extremities_exam VARCHAR(150),
nodal_survey_exam VARCHAR(150),
masses_or_skin_lesions VARCHAR(150),
-- evaluate whether to add skin exam findings (detailed) based on frequency of occurrence of masses_or_skin_lesions as findings
musculoskeletal_exam SMALLINT,
neurologic_exam SMALLINT,
-- evaluate adding condition causing focal weakness
physical_examination_notes VARCHAR(500),
ct_scan_head INT,
ct_scan_neck INT,
ct_scan_chest INT,
ct_scan_spine INT,
ct_scan_abdominal INT,
ultrasound_renal INT,
ultrasound_hepatic INT,
obstetric_ultrasound INT,
ultrasound_abdomen INT,
breast_ultrasound INT,
xray_shoulder INT,
xray_pelvis INT,
xray_abdomen INT,
xray_skull INT,
xray_leg INT,
xray_hand INT,
xray_foot INT,
xray_chest INT,
xray_arm INT,
xray_spine INT,
echo_test INT,
mri_head INT,
mri_neck INT,
mri_arms INT,
mri_chest INT,
mri_spine INT,
mri_abdominal INT,
mri_pelvic INT,
mri_legs INT,
imaging_results_description VARCHAR(1000),
clinical_media_caption_text VARCHAR(1000),
other_imaging_results VARCHAR(1000),
lab_test_ordered_for_next_visit SMALLINT,
other_lab_test VARCHAR(500),
red_blood_cells_count DECIMAL(5, 1),
hemoglobin DECIMAL(5, 1),
mean_corpuscular_volume DECIMAL(5, 1),
mean_corpuscular_hemoglobin DECIMAL(5, 1),
mean_cell_hemoglobin_concentration DeCIMAL(5, 1),
red_cell_distribution_width DECIMAL(5, 1),
platelets_count DECIMAL(5, 1),
serum_white_blood_cells_count DECIMAL(5, 1),
absolute_neutrophil_count DECIMAL(5, 1),
hematocrit DECIMAL(5, 1),
serum_uric_acid_test DECIMAL(5, 1),
serum_creatinine DECIMAL(5, 1),
serum_sodium DECIMAL(5, 1),
serum_potassium DECIMAL(5, 1),
serum_chloride DECIMAL(5, 1),
serum_albumin DECIMAL(5, 1),
serum_alpha_one_globulin DECIMAL(5, 1),
serum_alpha_two_globulin DECIMAL(5, 1),
serum_beta_globulin DECIMAL(5, 1),
serum_gamma_globulin DECIMAL(5, 1),
serum_m_protein DECIMAL(5, 1),
urine_alpha_one_globulin DECIMAL(5, 1),
urine_alpha_two_globulin DECIMAL(5, 1),
urine_beta_globulin DECIMAL(5, 1),
urine_gamma_globulin DECIMAL(5, 1),
urinary_albumin DECIMAL(5, 1),
urine_m_protein DECIMAL(5, 1),
serum_calcium_level DECIMAL(5, 1),
kappa_light_chains DECIMAL(5, 1),
kappa_lambda_ratio DECIMAL(5, 1),
pus_cells_in_urine INT,
protein_in_urine INT,
leukocytes_in_urine INT,
ketones_in_urine INT,
glucose_in_urine INT,
nitrites_in_urine INT,
reticulocytes_percentage INT,
serum_total_bilirubin DECIMAL(5, 1),
serum_direct_bilirubin DECIMAL(5, 1),
gamma_glutamyl_transferase DECIMAL(5, 1),
serum_glutamic_oxaloacetic_transaminase DECIMAL(5, 1),
serum_glutamic_pyruvic_transaminase DECIMAL(5, 1),
serum_total_protein DECIMAL(5, 1),
serum_alkaline_phospahatase DECIMAL(5, 1),
serum_lactate_dehydrogenase DECIMAL(5, 1),
lab_results_notes VARCHAR(255),
oncology_treatment_plan INT,
other_treatment_plan VARCHAR(255),
remission_plan INT,
remission_start_date DATE,
mm_supportive_care_plan INT,
freetext_mm_supportive_care_plan VARCHAR(500),
mm_signs_symptoms INT,
freetext_mm_signs_symptoms VARCHAR(100),
chemotherapy_plan INT,
chemo_start_date DATETIME,
chemo_cycle INT,
reason_chemo_stop INT,
chemotherapy_regimen VARCHAR(255),
dosage_in_milligrams INT,
drug_route INT,
freetext_chemo_drug VARCHAR(150),
other_drugs INT,
other_medication INT,
reason_for_medication_use INT,
assessment_notes VARCHAR(1000),
education_given_today INT,
referral_ordered VARCHAR(150),
next_app_date DATETIME,
prev_encounter_datetime_multiple_myeloma DATETIME,
next_encounter_datetime_multiple_myeloma DATETIME,
prev_encounter_type_multiple_myeloma MEDIUMINT,
next_encounter_type_multiple_myeloma MEDIUMINT,
prev_clinical_datetime_multiple_myeloma DATETIME,
next_clinical_datetime_multiple_myeloma DATETIME,
prev_clinical_location_id_multiple_myeloma MEDIUMINT,
next_clinical_location_id_multiple_myeloma MEDIUMINT,
prev_clinical_rtc_date_multiple_myeloma DATETIME,
next_clinical_rtc_date_multiple_myeloma DATETIME,
PRIMARY KEY encounter_id (encounter_id),
INDEX person_date (person_id, encounter_date),
INDEX location_id_rtc_date (location_id , next_app_date),
INDEX loc_id_enc_date_next_clinical (location_id , encounter_date , next_clinical_datetime_multiple_myeloma),
INDEX encounter_type (encounter_type),
INDEX date_created (date_created)
);
IF (@query_type = "build") THEN
SELECT "BUILDING.............";
SET @write_table := CONCAT("flat_multiple_myeloma_treatment_temp_", queue_number);
SET @queue_table := CONCAT("flat_multiple_myeloma_treatment_build_queue_", queue_number);
SET @dyn_sql := CONCAT("CREATE TABLE IF NOT EXISTS ", @write_table, " LIKE ", @primary_table);
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @dyn_sql := CONCAT("CREATE TABLE IF NOT EXISTS ", @queue_table, " (SELECT * FROM flat_multiple_myeloma_treatment_build_queue limit ", queue_size, ");");
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @dyn_sql := CONCAT("DELETE t1 FROM flat_multiple_myeloma_treatment_build_queue t1 JOIN ", @queue_table, " t2 using (person_id);");
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END IF;
IF (@query_type = "sync") THEN
SELECT "SYNCING.............";
SET @write_table := "flat_multiple_myeloma_treatment";
SET @queue_table := "flat_multiple_myeloma_treatment_sync_queue";
CREATE TABLE IF NOT EXISTS flat_multiple_myeloma_treatment_sync_queue (
person_id INT PRIMARY KEY
);
SELECT
MAX(date_updated)
INTO @last_update FROM
etl.flat_log
WHERE
table_name = @table_version;
SELECT 'Finding patients in amrs.encounters...';
REPLACE INTO flat_multiple_myeloma_treatment_sync_queue
(SELECT distinct patient_id
FROM amrs.encounter
where date_changed > @last_update
);
SELECT 'Finding patients in flat_obs...';
REPLACE INTO flat_multiple_myeloma_treatment_sync_queue
(SELECT distinct person_id
FROM etl.flat_obs
where max_date_created > @last_update
);
SELECT 'Finding patients in flat_lab_obs...';
REPLACE INTO flat_multiple_myeloma_treatment_sync_queue
(SELECT distinct person_id
FROM etl.flat_lab_obs
where max_date_created > @last_update
);
SELECT 'Finding patients in flat_orders...';
REPLACE INTO flat_multiple_myeloma_treatment_sync_queue
(SELECT distinct person_id
FROM etl.flat_orders
where max_date_created > @last_update
);
REPLACE INTO flat_multiple_myeloma_treatment_sync_queue
(SELECT person_id
FROM amrs.person
where date_voided > @last_update
);
REPLACE INTO flat_multiple_myeloma_treatment_sync_queue
(SELECT person_id
FROM amrs.person
where date_changed > @last_update
);
END IF;
-- Remove test patients
SET @dyn_sql := CONCAT('DELETE t1 FROM ', @queue_table,' t1
JOIN amrs.person_attribute t2 USING (person_id)
WHERE t2.person_attribute_type_id = 28 AND value = "true" AND voided = 0');
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @person_ids_count := 0;
SET @dyn_sql := CONCAT('SELECT COUNT(*) INTO @person_ids_count FROM ', @queue_table);
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT @person_ids_count AS 'num patients to update';
SET @dyn_sql := CONCAT('DELETE t1 FROM ', @primary_table, ' t1 JOIN ', @queue_table,' t2 USING (person_id);');
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
DROP TEMPORARY TABLE IF EXISTS multiple_myeloma_patient_identifiers;
SET @dyn_sql := CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS multiple_myeloma_patient_identifiers (SELECT p.person_id,
GROUP_CONCAT(DISTINCT id.identifier
SEPARATOR ', ') AS identifiers FROM ",
@queue_table, " `p`
LEFT JOIN
amrs.patient_identifier `id` ON (p.person_id = id.patient_id
AND (id.voided IS NULL || id.voided = 0))
GROUP BY p.person_id);");
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
DROP TEMPORARY TABLE IF EXISTS multiple_myeloma_freetext_obs;
SET @dyn_sql := CONCAT(
"CREATE TEMPORARY TABLE IF NOT EXISTS multiple_myeloma_freetext_obs (SELECT e.patient_id,
CONCAT('## !!', g.concept_id, '=', o.value_text, '!!') AS obs_string FROM amrs.encounter `e`
LEFT JOIN
amrs.obs `o` ON (o.encounter_id = e.encounter_id AND (o.concept_id = 1915))
LEFT JOIN
amrs.obs `g` ON (g.obs_id = o.obs_group_id)
WHERE
e.encounter_type IN (89, 90, 141)
GROUP BY e.encounter_id);"
);
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @total_time := 0;
SET @cycle_number := 0;
WHILE @person_ids_count > 0 DO
SET @loop_start_time := NOW();
-- Create temporary table with a set of person ids
DROP TEMPORARY TABLE IF EXISTS flat_multiple_myeloma_treatment_build_queue__0;
SET @dyn_sql := CONCAT('CREATE TEMPORARY TABLE flat_multiple_myeloma_treatment_build_queue__0 (person_id INT PRIMARY KEY) (SELECT * FROM ', @queue_table,' limit ', cycle_size,');');
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
DROP TEMPORARY TABLE IF EXISTS flat_multiple_myeloma_treatment_0a;
SET @dyn_sql := CONCAT(
'CREATE TEMPORARY TABLE flat_multiple_myeloma_treatment_0a
(SELECT
t1.person_id,
t1.visit_id,
t1.encounter_id,
t1.encounter_datetime,
t1.encounter_type,
t1.location_id,
t1.obs,
t1.obs_datetimes,
CASE
WHEN t1.encounter_type in ', @clinical_encounter_types, ' THEN 1
ELSE null
END AS is_clinical_encounter,
CASE
WHEN t1.encounter_type in ', @non_clinical_encounter_types, ' THEN 20
WHEN t1.encounter_type in ', @clinical_encounter_types, ' THEN 10
WHEN t1.encounter_type in', @other_encounter_types, ' THEN 5
ELSE 1
END AS encounter_type_sort_index,
t2.orders
FROM etl.flat_obs t1
JOIN
flat_multiple_myeloma_treatment_build_queue__0 t0 USING (person_id)
LEFT JOIN
etl.flat_orders t2 using (encounter_id)
WHERE
t1.encounter_type in ', @encounter_types ,');'
);
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
INSERT INTO flat_multiple_myeloma_treatment_0a
(SELECT
t1.person_id,
null,
t1.encounter_id,
t1.test_datetime,
t1.encounter_type,
null,
t1.obs,
null,
-- in any visit, there many be multiple encounters. for this dataset, we want to include only clinical encounters (e.g. not lab or triage visit)
0 as is_clinical_encounter,
1 as encounter_type_sort_index,
null
FROM
etl.flat_lab_obs t1
JOIN
flat_multiple_myeloma_treatment_build_queue__0 t0 USING (person_id)
);
DROP TEMPORARY TABLE IF EXISTS flat_multiple_myeloma_treatment_0;
CREATE TEMPORARY TABLE flat_multiple_myeloma_treatment_0 (INDEX encounter_id (encounter_id), INDEX person_enc (person_id, encounter_datetime))
(SELECT
*
FROM
flat_multiple_myeloma_treatment_0a
ORDER BY
person_id, DATE(encounter_datetime), encounter_type_sort_index
);
SET @purpose_of_visit_triage := null;
SET @covered_by_nhif := null;
SET @nhif_status := null;
SET @type_of_cancer := null;
SET @freetext_cancer_type := null;
SET @bp_systolic := null;
SET @bp_diastolic := null;
SET @heart_rate := null;
SET @respiratory_rate := null;
SET @temperature := null;
SET @weight_kg := null;
SET @height_cm := null;
SET @blood_oxygen_saturation := null;
SET @body_mass_index := null;
SET @body_surface_area := null;
SET @illness_currently_being_treated_for := null;
SET @freetext_illness_currently_being_treated_for := null;
SET @medications_currently_being_used := null;
SET @other_medications_currently_being_used := null;
SET @allergic_to_penicillin := null;
SET @specific_allergic_reaction_to_penicillin := null;
SET @allergic_to_sulfa := null;
SET @specific_allergic_reaction_to_sulfa := null;
SET @freetext_allergy := null;
SET @name_of_drug_or_product_allergic_to := null;
SET @specific_reaction_to_this_drug_or_product := null;
SET @purpose_of_visit_mm_initial := null;
SET @referred_by := null;
SET @religion := null;
SET @current_marital_status := null;
SET @no_of_household_members := null;
SET @no_of_children := null;
SET @are_you_or_your_partner_currently_using_any_form_of_fp := null;
SET @currently_breastfeeding := null;
SET @clinic_travel_time := null;
SET @mode_of_transport_to_clinic := null;
SET @main_occupation := null;
SET @freetext_main_occupation := null;
SET @average_monthly_income := null;
SET @level_of_education := null;
SET @next_of_kin_level_of_education := null;
SET @smokes_cigarettes := null;
SET @uses_tobacco := null;
SET @drinks_alcohol_sometimes := null;
SET @family_history_of_cancer_or_chronic_illness_history := null;
SET @specific_chronic_illness := null;
SET @family_member_with_chronic_illness := null;
SET @history_of_surgery := null;
SET @freetext_history_of_surgery := null;
SET @chief_complaint_review_of_systems := null;
SET @pain := null;
SET @pain_score := null;
SET @heent_findings := null;
SET @general_findings := null;
SET @cardiopulmonary_findings := null;
SET @gastrointestinal_findings := null;
SET @genitourinary_findings := null;
SET @last_menstrual_period := null;
SET @musculoskeletal_findings := null;
SET @chief_complaints_physical_exam := null;
SET @freetext_chief_complaint := null;
SET @ecog_performance_index := null;
SET @general_exam := null;
SET @heent_exam := null;
SET @chest_exam := null;
SET @abdomen_exam := null;
SET @urogenital_exam := null;
SET @extremities_exam := null;
SET @nodal_survey_exam := null;
SET @masses_or_skin_lesions := null;
SET @musculoskeletal_exam := null;
SET @neurologic_exam := null;
SET @physical_examination_notes := null;
SET @ct_scan_head := null;
SET @ct_scan_neck := null;
SET @ct_scan_chest := null;
SET @ct_scan_spine := null;
SET @ct_scan_abdominal := null;
SET @ultrasound_renal := null;
SET @ultrasound_hepatic := null;
SET @obstetric_ultrasound := null;
SET @ultrasound_abdomen := null;
SET @breast_ultrasound := null;
SET @xray_shoulder := null;
SET @xray_pelvis := null;
SET @xray_abdomen := null;
SET @xray_skull := null;
SET @xray_leg := null;
SET @xray_hand := null;
SET @xray_foot := null;
SET @xray_chest := null;
SET @xray_arm := null;
SET @xray_spine := null;
SET @echo_test := null;
SET @mri_head := null;
SET @mri_neck := null;
SET @mri_arms := null;
SET @mri_chest := null;
SET @mri_spine := null;
SET @mri_abdominal := null;
SET @mri_pelvic := null;
SET @mri_legs := null;
SET @imaging_results_description := null;
SET @clinical_media_caption_text := null;
SET @other_imaging_results := null;
SET @lab_test_ordered_for_next_visit := null;
SET @other_lab_test := null;
SET @red_blood_cells_count := null;
SET @hemoglobin := null;
SET @mean_corpuscular_volume := null;
SET @mean_corpuscular_hemoglobin := null;
SET @mean_cell_hemoglobin_concentration := null;
SET @red_cell_distribution_width := null;
SET @platelets_count := null;
SET @serum_white_blood_cells_count := null;
SET @absolute_neutrophil_count := null;
SET @hematocrit := null;
SET @serum_uric_acid_test := null;
SET @serum_creatinine := null;
SET @serum_sodium := null;
SET @serum_potassium := null;
SET @serum_chloride := null;
SET @serum_albumin := null;
SET @serum_alpha_one_globulin := null;
SET @serum_alpha_two_globulin := null;
SET @serum_beta_globulin := null;
SET @serum_gamma_globulin := null;
SET @serum_m_protein := null;
SET @urine_alpha_one_globulin := null;
SET @urine_alpha_two_globulin := null;
SET @urine_beta_globulin := null;
SET @urine_gamma_globulin := null;
SET @urinary_albumin := null;
SET @urine_m_protein := null;
SET @serum_calcium_level := null;
SET @kappa_light_chains := null;
SET @kappa_lambda_ratio := null;
SET @pus_cells_in_urine := null;
SET @protein_in_urine := null;
SET @leukocytes_in_urine := null;
SET @ketones_in_urine := null;
SET @glucose_in_urine := null;
SET @nitrites_in_urine := null;
SET @reticulocytes_percentage := null;
SET @serum_total_bilirubin := null;
SET @serum_direct_bilirubin := null;
SET @gamma_glutamyl_transferase := null;
SET @serum_glutamic_oxaloacetic_transaminase := null;
SET @serum_glutamic_pyruvic_transaminase := null;
SET @serum_total_protein := null;
SET @serum_alkaline_phospahatase := null;
SET @serum_lactate_dehydrogenase := null;
SET @lab_results_notes := null;
SET @oncology_treatment_plan := null;
SET @other_treatment_plan := null;
SET @remission_plan := null;
SET @remission_start_date := null;
SET @mm_supportive_care_plan := null;
SET @freetext_mm_supportive_care_plan := null;
SET @mm_signs_symptoms := null;
SET @freetext_mm_signs_symptoms := null;
SET @chemotherapy_plan := null;
SET @chemo_start_date := null;
SET @chemo_cycle := null;
SET @reason_chemo_stop := null;
SET @chemotherapy_regimen := null;
SET @dosage_in_milligrams := null;
SET @drug_route := null;
SET @freetext_chemo_drug := null;
SET @other_drugs := null;
SET @other_medication := null;
SET @reason_for_medication_use := null;
SET @assessment_notes := null;
SET @education_given_today := null;
SET @referral_ordered := null;
SET @next_app_date := null;
DROP TEMPORARY TABLE IF EXISTS flat_multiple_myeloma_treatment_1;
CREATE TEMPORARY TABLE flat_multiple_myeloma_treatment_1
(SELECT
obs,
encounter_type_sort_index,
@prev_id = @cur_id as prev_id,
@cur_id = t1.person_id as cur_id,
t1.person_id,
t1.encounter_id,
t1.encounter_type,
t1.encounter_datetime,
t1.visit_id,
t1.location_id,
t1.is_clinical_encounter,
p.gender,
CASE
WHEN TIMESTAMPDIFF(YEAR, p.birthdate, curDATE()) > 0 THEN round(TIMESTAMPDIFF(YEAR, p.birthdate, curDATE()), 0)
ELSE ROUND(TIMESTAMPDIFF(MONTH, p.birthdate, curDATE()) / 12, 2)
END AS age,
mmpi.identifiers,
CONCAT(COALESCE(person_name.given_name, ''),
' ',
COALESCE(person_name.middle_name, ''),
' ',
COALESCE(person_name.family_name, '')) AS person_name,
p.death_date,
contacts.value AS phone_number,
person_address.address1 as county,
CASE
WHEN obs regexp "!!1834=7850!!" THEN @purpose_of_visit_triage := 1 -- Initial visit
WHEN obs regexp "!!1834=10037!!" THEN @purpose_of_visit_triage := 2 -- Second opinion
WHEN obs regexp "!!1834=2345!!" THEN @purpose_of_visit_triage := 3 -- Follow-up
WHEN obs regexp "!!1834=1068!!" THEN @purpose_of_visit_triage := 4 -- Symptomatic
WHEN obs regexp "!!1834=1246!!" THEN @purpose_of_visit_triage := 5 -- Scheduled visit
WHEN obs regexp "!!1834=5622!!" THEN @purpose_of_visit_triage := 6 -- Other (non-coded)
ELSE @purpose_of_visit_triage := null
END AS purpose_of_visit_triage,
CASE
WHEN obs regexp "!!6266=1065!!" THEN @covered_by_nhif := 1 -- Yes
WHEN obs regexp "!!6266=1066!!" THEN @covered_by_nhif := 2 -- No
ELSE @covered_by_nhif := null
END AS covered_by_nhif,
CASE
WHEN obs regexp "!!9745=9743!!" THEN @nhif_status := 1 -- Active
WHEN obs regexp "!!9745=9744!!" THEN @nhif_status := 2 -- Inactive
ELSE @nhif_status := null
END AS nhif_status,
CASE
WHEN obs regexp "!!7176=6544!!" THEN @type_of_cancer := 1 -- Breast cancer
WHEN obs regexp "!!7176=6520!!" THEN @type_of_cancer := 2 -- GI cancer
WHEN obs regexp "!!7176=6514!!" THEN @type_of_cancer := 3 -- GU cancer
WHEN obs regexp "!!7176=6536!!" THEN @type_of_cancer := 4 -- Gyenocological cancer
WHEN obs regexp "!!7176=6528!!" THEN @type_of_cancer := 5 -- Head and neck cancer
WHEN obs regexp "!!7176=7175!!" THEN @type_of_cancer := 6 -- Hematologic cancer
WHEN obs regexp "!!7176=216!!" THEN @type_of_cancer := 7 -- Leukemia
WHEN obs regexp "!!7176=6551!!" THEN @type_of_cancer := 8 -- Lymphoma
WHEN obs regexp "!!7176=10129!!" THEN @type_of_cancer := 9 -- Non small cell lung cancer
WHEN obs regexp "!!7176=6485!!" THEN @type_of_cancer := 10 -- Sarcoma
WHEN obs regexp "!!7176=6540!!" THEN @type_of_cancer := 11 -- Skin cancer
WHEN obs regexp "!!7176=10130!!" THEN @type_of_cancer := 12 -- Small cell lung cancer
WHEN obs regexp "!!7176=5622!!" THEN @type_of_cancer := 13 -- Other non-coded
ELSE @type_of_cancer := null
END AS type_of_cancer,
CASE
WHEN obs regexp "!!1915=" THEN @freetext_cancer_type := GetValues(mmfo.obs_string, 7177)
ELSE @freetext_cancer_type := null
END AS freetext_cancer_type,
CASE
WHEN obs regexp "!!5085=" THEN @bp_systolic := GetValues(obs, 5085)
ELSE @bp_systolic := null
END AS bp_systolic,
CASE
WHEN obs regexp "!!5086=" THEN @bp_diastolic := GetValues(obs, 5086)
ELSE @bp_diastolic := null
END AS bp_diastolic,
CASE
WHEN obs regexp "!!5087=" THEN @heart_rate := GetValues(obs, 5087)
ELSE @heart_rate := null
END AS heart_rate,
CASE
WHEN obs regexp "!!5242=" THEN @respiratory_rate := GetValues(obs, 5242)
ELSE @respiratory_rate := null
END AS respiratory_rate,
CASE
WHEN obs regexp "!!5088=" THEN @temperature := GetValues(obs, 5088)
ELSE @temperature := null
END AS temperature,
CASE
WHEN obs regexp "!!5089=" THEN @weight_kg := GetValues(obs, 5089)
ELSE @weight_kg := null
END AS weight_kg,
CASE
WHEN obs regexp "!!5090=" THEN @height_cm := GetValues(obs, 5090)
ELSE @height_cm := null
END AS height_cm,
CASE
WHEN obs regexp "!!5092=" THEN @blood_oxygen_saturation := GetValues(obs, 5092)
ELSE @blood_oxygen_saturation := null
END AS blood_oxygen_saturation,
CASE
WHEN obs regexp "!!1342=" THEN @body_mass_index := GetValues(obs, 1342)
ELSE @body_mass_index := null
END AS body_mass_index,
CASE
WHEN obs regexp "!!980=" THEN @body_surface_area := GetValues(obs, 980)
ELSE @body_surface_area := null
END AS body_surface_area,
CASE
WHEN obs regexp "!!6475=175!!" THEN @illness_currently_being_treated_for := 1 -- Diabetes mellitus
WHEN obs regexp "!!6475=29!!" THEN @illness_currently_being_treated_for := 2 -- Hepatitis
WHEN obs regexp "!!6475=884!!" THEN @illness_currently_being_treated_for := 3 -- HIV
WHEN obs regexp "!!6475=903!!" THEN @illness_currently_being_treated_for := 4 -- Hypertension
WHEN obs regexp "!!6475=58!!" THEN @illness_currently_being_treated_for := 5 -- Tuberculosis
WHEN obs regexp "!!6475=5622!!" THEN @illness_currently_being_treated_for := 6 -- Other (non-coded)
ELSE @illness_currently_being_treated_for := null
END AS illness_currently_being_treated_for,
CASE
WHEN obs regexp "!!1915=" THEN @freetext_illness_currently_being_treated_for := GetValues(mmfo.obs_string, 9891)
ELSE @freetext_illness_currently_being_treated_for := null
END AS freetext_illness_currently_being_treated_for,
CASE
WHEN obs regexp "!!6789=1195!!" THEN @medications_currently_being_used := 1 -- Antibiotics
WHEN obs regexp "!!6789=8410!!" THEN @medications_currently_being_used := 2 -- Anticoagulants
WHEN obs regexp "!!6789=6778!!" THEN @medications_currently_being_used := 3 -- Anticonvulsants
WHEN obs regexp "!!6789=1635!!" THEN @medications_currently_being_used := 4 -- Antihypertensives
WHEN obs regexp "!!6789=6176!!" THEN @medications_currently_being_used := 5 -- Anti-TB
WHEN obs regexp "!!6789=6575!!" THEN @medications_currently_being_used := 6 -- Chemotherapy
WHEN obs regexp "!!6789=8775!!" THEN @medications_currently_being_used := 7 -- Factor IX
WHEN obs regexp "!!6789=8774!!" THEN @medications_currently_being_used := 8 -- Factor VIII
WHEN obs regexp "!!6789=1085!!" THEN @medications_currently_being_used := 9 -- HAART
WHEN obs regexp "!!6789=7211!!" THEN @medications_currently_being_used := 10 -- Hydroxyurea
WHEN obs regexp "!!6789=6777!!" THEN @medications_currently_being_used := 11 -- Insulin
WHEN obs regexp "!!6789=7213!!" THEN @medications_currently_being_used := 12 -- Melphalan
WHEN obs regexp "!!6789=8408!!" THEN @medications_currently_being_used := 13 -- Opioid analgesics
WHEN obs regexp "!!6789=491!!" THEN @medications_currently_being_used := 14 -- Prednisone
WHEN obs regexp "!!6789=8479!!" THEN @medications_currently_being_used := 15 -- Thalidomide
WHEN obs regexp "!!6789=6339!!" THEN @medications_currently_being_used := 15 -- Vitamins
WHEN obs regexp "!!6789=5622!!" THEN @medications_currently_being_used := 16 -- Other (non-coded)
ELSE @medications_currently_being_used := null
END AS medications_currently_being_used,
CASE
WHEN obs regexp "!!1915=" THEN @other_medications_currently_being_used := GetValues(mmfo.obs_string, 6790)
ELSE @other_medications_currently_being_used := null
END AS other_medications_currently_being_used,
CASE
WHEN obs regexp "!!6011=1065!!" THEN @allergic_to_penicillin := 1 -- Yes
WHEN obs regexp "!!6011=1066!!" THEN @allergic_to_penicillin := 2 -- No
ELSE @allergic_to_penicillin := null
END AS allergic_to_penicillin,
CASE
WHEN obs regexp "!!2085=" THEN @specific_allergic_reaction_to_penicillin := GetValues(obs, 2085)
ELSE @specific_allergic_reaction_to_penicillin := null
END AS specific_allergic_reaction_to_penicillin,
CASE
WHEN obs regexp "!!6012=1065!!" THEN @allergic_to_sulfa := 1 -- Yes
WHEN obs regexp "!!6012=1066!!" THEN @allergic_to_sulfa := 2 -- No
ELSE @allergic_to_sulfa := null
END AS allergic_to_sulfa,
CASE
WHEN obs regexp "!!2085=" THEN @specific_allergic_reaction_to_sulfa := GetValues(obs, 2085)
ELSE @specific_allergic_reaction_to_sulfa := null
END AS specific_allergic_reaction_to_sulfa,
CASE
WHEN obs regexp "!!1083=1065!!" THEN @freetext_allergy := 1 -- Yes
WHEN obs regexp "!!1083=1066!!" THEN @freetext_allergy := 2 -- No
ELSE @freetext_allergy := null
END AS freetext_allergy,
CASE
WHEN obs regexp "!!2089=" THEN @name_of_drug_or_product_allergic_to := GetValues(obs, 2089)
ELSE @name_of_drug_or_product_allergic_to := null
END AS name_of_drug_or_product_allergic_to,
CASE
WHEN obs regexp "!!2085=" THEN @specific_reaction_to_this_drug_or_product := GetValues(obs, 2085)
ELSE @specific_reaction_to_this_drug_or_product := null
END AS specific_reaction_to_this_drug_or_product,
CASE
WHEN obs regexp "!!1834=7850!!" THEN @purpose_of_visit_mm_initial := 1 -- Initial visit
WHEN obs regexp "!!1834=10037!!" THEN @purpose_of_visit_mm_initial := 2 -- Second opinion
ELSE @purpose_of_visit_mm_initial := null
END AS purpose_of_visit_mm_initial,
CASE
WHEN obs regexp "!!6749=978!!" THEN @referred_by := 1 -- Self
WHEN obs regexp "!!6749=7037!!" THEN @referred_by := 2 -- Referred from clinic
WHEN obs regexp "!!6749=6479!!" THEN @referred_by := 3 -- Non-MTRH consultant
WHEN obs regexp "!!6749=5622!!" THEN @referred_by := 4 -- Other (non-coded)
ELSE @referred_by := null
END AS referred_by,
CASE
WHEN obs regexp "!!6285=8399!!" THEN @religion := 1 -- Catholic
WHEN obs regexp "!!6285=8400!!" THEN @religion := 2 -- Protestant
WHEN obs regexp "!!6285=8401!!" THEN @religion := 3 -- Muslim
WHEN obs regexp "!!6285=8402!!" THEN @religion := 4 -- Traditionalist
WHEN obs regexp "!!6285=8403!!" THEN @religion := 5 -- Hindu
WHEN obs regexp "!!6285=8404!!" THEN @religion := 6 -- Buddhist
WHEN obs regexp "!!6285=1107!!" THEN @religion := 7 -- None
WHEN obs regexp "!!6285=5622!!" THEN @religion := 8 -- Other (non-coded)
ELSE @religion := null
END AS religion,
CASE
WHEN obs regexp "!!1054=1175!!" THEN @current_marital_status := 1 -- Not applicable
WHEN obs regexp "!!1054=1058!!" THEN @current_marital_status := 2 -- Divorced
WHEN obs regexp "!!1054=1060!!" THEN @current_marital_status := 2 -- Living with partner
WHEN obs regexp "!!1054=5555!!" THEN @current_marital_status := 3 -- Married (monogamous)
WHEN obs regexp "!!1054=6290!!" THEN @current_marital_status := 4 -- Married (polygamous)
WHEN obs regexp "!!1054=1057!!" THEN @current_marital_status := 5 -- Never married
WHEN obs regexp "!!1054=1056!!" THEN @current_marital_status := 2 -- Separated
WHEN obs regexp "!!1054=1059!!" THEN @current_marital_status := 2 -- Widowed
ELSE @current_marital_status := null
END AS current_marital_status,
CASE
WHEN obs regexp "!!6801=" THEN @no_of_household_members := GetValues(obs, 6801)
ELSE @no_of_household_members := null
END AS no_of_household_members,
CASE
WHEN obs regexp "!!1728=" THEN @no_of_children := GetValues(obs, 1728)
ELSE @no_of_children := null
END AS no_of_children,
CASE
WHEN obs regexp "!!6683=1065!!" THEN @are_you_or_your_partner_currently_using_any_form_of_fp := 1 -- Yes
WHEN obs regexp "!!6683=1066!!" THEN @are_you_or_your_partner_currently_using_any_form_of_fp := 2 -- No
WHEN obs regexp "!!6683=1679!!" THEN @are_you_or_your_partner_currently_using_any_form_of_fp := 3 -- Stopped
ELSE @are_you_or_your_partner_currently_using_any_form_of_fp := null
END AS are_you_or_your_partner_currently_using_any_form_of_fp,
CASE
WHEN obs regexp "!!2056=1065!!" THEN @currently_breastfeeding := 1 -- Yes
WHEN obs regexp "!!2056=1066!!" THEN @currently_breastfeeding := 2 -- No
ELSE @currently_breastfeeding := null
END AS currently_breastfeeding,
CASE
WHEN obs regexp "!!5605=1049!!" THEN @clinic_travel_time := 1 -- Less than 30 mins
WHEN obs regexp "!!5605=1050!!" THEN @clinic_travel_time := 2 -- 30-60 mins
WHEN obs regexp "!!5605=1051!!" THEN @clinic_travel_time := 3 -- 1-2 hrs
WHEN obs regexp "!!5605=1052!!" THEN @clinic_travel_time := 4 -- More than 2 hrs
ELSE @clinic_travel_time := null
END AS clinic_travel_time,
CASE
WHEN obs regexp "!!6468=6395!!" THEN @mode_of_transport_to_clinic := 1 -- Bicycle
WHEN obs regexp "!!6468=7552!!" THEN @mode_of_transport_to_clinic := 2 -- Boat
WHEN obs regexp "!!6468=6580!!" THEN @mode_of_transport_to_clinic := 3 -- Boda boda
WHEN obs regexp "!!6468=6416!!" THEN @mode_of_transport_to_clinic := 4 -- Matatu
WHEN obs regexp "!!6468=6396!!" THEN @mode_of_transport_to_clinic := 5 -- Motorcycle
WHEN obs regexp "!!6468=6471!!" THEN @mode_of_transport_to_clinic := 6 -- Private car
WHEN obs regexp "!!6468=6470!!" THEN @mode_of_transport_to_clinic := 7 -- Taxi
WHEN obs regexp "!!6468=6415!!" THEN @mode_of_transport_to_clinic := 8 -- Walking
ELSE @mode_of_transport_to_clinic := null
END AS mode_of_transport_to_clinic,
CASE
WHEN obs regexp "!!1972=1966!!" THEN @main_occupation := 1 -- Casual worker
WHEN obs regexp "!!1972=1967!!" THEN @main_occupation := 2 -- Farmer
WHEN obs regexp "!!1972=6401!!" THEN @main_occupation := 3 -- Fishing
WHEN obs regexp "!!1972=6408!!" THEN @main_occupation := 4 -- Formal employment
WHEN obs regexp "!!1972=5619!!" THEN @main_occupation := 5 -- Health care provider
WHEN obs regexp "!!1972=1969!!" THEN @main_occupation := 6 -- Housewife
WHEN obs regexp "!!1972=8589!!" THEN @main_occupation := 7 -- Student
WHEN obs regexp "!!1972=6284!!" THEN @main_occupation := 8 -- Self-employed
WHEN obs regexp "!!1972=1968!!" THEN @main_occupation := 9 -- Teacher
WHEN obs regexp "!!1972=6966!!" THEN @main_occupation := 10 -- Truck driver
WHEN obs regexp "!!1972=1971!!" THEN @main_occupation := 11 -- Unemployed
WHEN obs regexp "!!1972=5622!!" THEN @main_occupation := 12 -- Other (non-coded)
ELSE @main_occupation := null
END AS main_occupation,
CASE
WHEN obs regexp "!!1915=" THEN @freetext_main_occupation := GetValues(mmfo.obs_string, 1973)
ELSE @freetext_main_occupation := null
END AS freetext_main_occupation,
CASE
WHEN obs regexp "!!7003=7002!!" THEN @average_monthly_income := 1 -- Less than 1000
WHEN obs regexp "!!7003=6313!!" THEN @average_monthly_income := 2 -- Kshs. 1001 to 2000
WHEN obs regexp "!!7003=6314!!" THEN @average_monthly_income := 3 -- Kshs. 2001 to 3000
WHEN obs regexp "!!7003=6315!!" THEN @average_monthly_income := 3 -- Kshs. 3001 to 5000
WHEN obs regexp "!!7003=6316!!" THEN @average_monthly_income := 3 -- Kshs. 5001 to 10000
WHEN obs regexp "!!7003=6317!!" THEN @average_monthly_income := 3 -- More than Kshs. 10000
ELSE @average_monthly_income := null
END AS average_monthly_income,
CASE
WHEN obs regexp "!!1605=1107!!" THEN @level_of_education := 1 -- No formal education
WHEN obs regexp "!!1605=7549!!" THEN @level_of_education := 2 -- Pre-primary
WHEN obs regexp "!!1605=6214!!" THEN @level_of_education := 3 -- Primary school
WHEN obs regexp "!!1605=6215!!" THEN @level_of_education := 4 -- Secondary school
WHEN obs regexp "!!1605=1604!!" THEN @level_of_education := 5 -- Tertiary
ELSE @level_of_education := null
END AS level_of_education,
CASE
WHEN obs regexp "!!8749=1107!!" THEN @next_of_kin_level_of_education := 1 -- None
WHEN obs regexp "!!8749=7549!!" THEN @next_of_kin_level_of_education := 2 -- Pre Unit
WHEN obs regexp "!!8749=6214!!" THEN @next_of_kin_level_of_education := 3 -- Primary school
WHEN obs regexp "!!8749=6215!!" THEN @next_of_kin_level_of_education := 4 -- Secondary school
WHEN obs regexp "!!8749=1604!!" THEN @next_of_kin_level_of_education := 5 -- University
ELSE @next_of_kin_level_of_education := null
END AS next_of_kin_level_of_education,
CASE
WHEN obs regexp "!!2065=1065!!" THEN @smokes_cigarettes := 1 -- Yes
WHEN obs regexp "!!2065=1066!!" THEN @smokes_cigarettes := 2 -- No
WHEN obs regexp "!!2065=1679!!" THEN @smokes_cigarettes := 3 -- Stopped
ELSE @smokes_cigarettes := null
END AS smokes_cigarettes,
CASE
WHEN obs regexp "!!7973=1065!!" THEN @uses_tobacco := 1 -- Yes
WHEN obs regexp "!!7973=1066!!" THEN @uses_tobacco := 2 -- No
WHEN obs regexp "!!7973=1679!!" THEN @uses_tobacco := 3 -- Stopped
ELSE @uses_tobacco := null
END AS uses_tobacco,
CASE
WHEN obs regexp "!!1684=1065!!" THEN @drinks_alcohol_sometimes := 1 -- Yes
WHEN obs regexp "!!1684=1066!!" THEN @drinks_alcohol_sometimes := 2 -- No
WHEN obs regexp "!!1684=1679!!" THEN @drinks_alcohol_sometimes := 3 -- Stopped
ELSE @drinks_alcohol_sometimes := null
END AS drinks_alcohol_sometimes,
CASE
WHEN obs regexp "!!6802=1065!!" THEN @family_history_of_cancer_or_chronic_illness_history := 1 -- Yes
WHEN obs regexp "!!6802=1066!!" THEN @family_history_of_cancer_or_chronic_illness_history := 2 -- No
ELSE @family_history_of_cancer_or_chronic_illness_history := null
END AS family_history_of_cancer_or_chronic_illness_history,
CASE
WHEN obs regexp "!!1915=" THEN @specific_chronic_illness := GetValues(mmfo.obs_string, 6805)
ELSE @specific_chronic_illness := null
END AS specific_chronic_illness,
CASE
WHEN obs regexp "!!8254=" THEN @family_member_with_chronic_illness := GetValues(obs, 8254)
ELSE @family_member_with_chronic_illness := null
END AS family_member_with_chronic_illness,
CASE
WHEN obs regexp "!!6478=" THEN @history_of_surgery := GetValues(obs, 6478)
ELSE @history_of_surgery := null
END AS history_of_surgery,
CASE
WHEN obs regexp "!!1915=" THEN @freetext_history_of_surgery := GetValues(mmfo.obs_string, 9217)
ELSE @freetext_history_of_surgery := null
END AS freetext_history_of_surgery,
CASE
WHEN obs regexp "!!5219=5006!!" THEN @chief_complaint_review_of_systems := 1 -- Asymptomatic
WHEN obs regexp "!!5219=1068!!" THEN @chief_complaint_review_of_systems := 2 -- Symptomatic
ELSE @chief_complaint_review_of_systems := null
END AS chief_complaint_review_of_systems,
CASE
WHEN obs regexp "!!6613=1065!!" THEN @pain := 1 -- Yes
WHEN obs regexp "!!6613=1066!!" THEN @pain := 2 -- No
ELSE @pain := null
END AS pain,
CASE
WHEN obs regexp "!!7080=" THEN @pain_score := GetValues(obs, 7080)
ELSE @pain_score := null
END AS pain_score,
CASE
WHEN obs regexp "!!1070=1115!!" THEN @heent_findings := 1 -- Normal
WHEN obs regexp "!!1070=861!!" THEN @heent_findings := 2 -- Hearing difficulties
WHEN obs regexp "!!1070=5954!!" THEN @heent_findings := 3 -- Swallowing difficulties
WHEN obs regexp "!!1070=5953!!" THEN @heent_findings := 4 -- Vision difficulties
WHEN obs regexp "!!1070=5622!!" THEN @heent_findings := 5 -- Other (non-coded)
ELSE @heent_findings := null
END AS heent_findings,
CASE
WHEN obs regexp "!!1069=1115!!" THEN @general_findings := 1 -- Normal
WHEN obs regexp "!!1069=5949!!" THEN @general_findings := 2 -- Fatigue
WHEN obs regexp "!!1069=5945!!" THEN @general_findings := 3 -- Fever
WHEN obs regexp "!!1069=215!!" THEN @general_findings := 4 -- Jaundice
WHEN obs regexp "!!1069=6029!!" THEN @general_findings := 5 -- Night sweats
WHEN obs regexp "!!1069=6613!!" THEN @general_findings := 6 -- Pain
WHEN obs regexp "!!1069=832!!" THEN @general_findings := 7 -- Weight loss
WHEN obs regexp "!!1069=5192!!" THEN @general_findings := 8 -- Yellowing of eyes
ELSE @general_findings := null
END AS general_findings,
CASE
WHEN obs regexp "!!1071=1115!!" THEN @cardiopulmonary_findings := 1 -- Normal
WHEN obs regexp "!!1071=136!!" THEN @cardiopulmonary_findings := 2 -- Chest pain
WHEN obs regexp "!!1071=107!!" THEN @cardiopulmonary_findings := 3 -- Cough
WHEN obs regexp "!!1071=5960!!" THEN @cardiopulmonary_findings := 4 -- Shortness of breath
WHEN obs regexp "!!1071=5961!!" THEN @cardiopulmonary_findings := 5 -- Shortness of breath at rest
WHEN obs regexp "!!1071=5963!!" THEN @cardiopulmonary_findings := 6 -- Shortness of breath with exertion
ELSE @cardiopulmonary_findings := null
END AS cardiopulmonary_findings,
CASE
WHEN obs regexp "!!1078=1115!!" THEN @gastrointestinal_findings := 1 -- Normal
WHEN obs regexp "!!1078=151!!" THEN @gastrointestinal_findings := 2 -- Abdominal pain
WHEN obs regexp "!!1078=6495!!" THEN @gastrointestinal_findings := 3 -- Bleeding per rectum
WHEN obs regexp "!!1078=996!!" THEN @gastrointestinal_findings := 4 -- Constipation
WHEN obs regexp "!!1078=16!!" THEN @gastrointestinal_findings := 5 -- Diarrhea
WHEN obs regexp "!!1078=881!!" THEN @gastrointestinal_findings := 6 -- Dysphagia
WHEN obs regexp "!!1078=215!!" THEN @gastrointestinal_findings := 7 -- Jaundice
WHEN obs regexp "!!1078=6494!!" THEN @gastrointestinal_findings := 8 -- Melena
WHEN obs regexp "!!1078=6031!!" THEN @gastrointestinal_findings := 9 -- Poor appetite
ELSE @gastrointestinal_findings := null
END AS gastrointestinal_findings,
CASE
WHEN obs regexp "!!1080=1115!" THEN @genitourinary_findings := 1 -- Normal
WHEN obs regexp "!!1080=6020!!" THEN @genitourinary_findings := 2 -- Dysuria
WHEN obs regexp "!!1080=8418!!" THEN @genitourinary_findings := 3 -- Increased urine urgency
WHEN obs regexp "!!1080=8417!!" THEN @genitourinary_findings := 4 -- Increased urine volume
WHEN obs regexp "!!1080=8767!!" THEN @genitourinary_findings := 5 -- Priapism
WHEN obs regexp "!!1080=6021!!" THEN @genitourinary_findings := 6 -- Reduced urine volume
ELSE @genitourinary_findings := null
END AS genitourinary_findings,
CASE
WHEN obs regexp "!!1836=" THEN @last_menstrual_period := GetValues(obs, 1836)
ELSE @last_menstrual_period := null
END AS last_menstrual_period,
CASE
WHEN obs regexp "!!1081=1115!!" THEN @musculoskeletal_findings := 1 -- Normal
WHEN obs regexp "!!1081=590!!" THEN @musculoskeletal_findings := 2 -- Edema of legs
WHEN obs regexp "!!1081=80!!" THEN @musculoskeletal_findings := 3 -- Joint pain
WHEN obs regexp "!!1081=5312!!" THEN @musculoskeletal_findings := 4 -- Joint swelling
WHEN obs regexp "!!1081=951!!" THEN @musculoskeletal_findings := 5 -- Leg ulcers
WHEN obs regexp "!!1081=6034!!" THEN @musculoskeletal_findings := 6 -- Muscle pain
ELSE @musculoskeletal_findings := null
END AS musculoskeletal_findings,
CASE
WHEN obs regexp "!!5219=" THEN @chief_complaints_physical_exam := GetValues(obs, 5219)
ELSE @chief_complaints_physical_exam := null
END AS chief_complaints_physical_exam,
CASE
WHEN obs regexp "!!1915=" THEN @freetext_chief_complaint := GetValues(mmfo.obs_string, 8916)
ELSE @freetext_chief_complaint := null
END AS freetext_chief_complaint,
CASE
WHEN obs regexp "!!6584=1115!!" THEN @ecog_performance_index := 1 -- Normal
WHEN obs regexp "!!6584=6585!!" THEN @ecog_performance_index := 2 -- Symptomatic but ambulatory
WHEN obs regexp "!!6584=6586!!" THEN @ecog_performance_index := 3 -- Debilitated, bedridden less than 50% of the day
WHEN obs regexp "!!6584=6587!!" THEN @ecog_performance_index := 4 -- Debilitated, bedridden greater than 50% of the day
WHEN obs regexp "!!6584=6588!!" THEN @ecog_performance_index := 5 -- Bedridden 100%
ELSE @ecog_performance_index := null
END AS ecog_performance_index,
CASE
WHEN obs regexp "!!1119=" THEN @general_exam := GetValues(obs, 1119)
ELSE @general_exam := null
END AS general_exam,
CASE
WHEN obs regexp "!!1122=" THEN @heent_exam := GetValues(obs, 1122)
ELSE @heent_exam := null
END AS heent_exam,
CASE
WHEN obs regexp "!!1123=" THEN @chest_exam := GetValues(obs, 1123)
ELSE @chest_exam := null
END AS chest_exam,
CASE
WHEN obs regexp "!!1124=" THEN @heart_exam := GetValues(obs, 1124)
ELSE @heart_exam := null
END AS heart_exam,
CASE
WHEN obs regexp "!!1125=" THEN @abdomen_exam := GetValues(obs, 1125)
ELSE @abdomen_exam := null
END AS abdomen_exam,
CASE
WHEN obs regexp "!!1126=" THEN @urogenital_exam := GetValues(obs, 1126)
ELSE @urogenital_exam := null
END AS urogenital_exam,
CASE
WHEN obs regexp "!!1127=" THEN @extremities_exam := GetValues(obs, 1127)
ELSE @extremities_exam := null
END AS extremities_exam,
CASE
WHEN obs regexp "!!1121=" THEN @nodal_survey_exam := GetValues(obs, 1121)
ELSE @nodal_survey_exam := null
END AS nodal_survey_exam,
CASE
WHEN obs regexp "!!1120=" THEN @masses_or_skin_lesions := GetValues(obs, 1120)
ELSE @masses_or_skin_lesions := null
END AS masses_or_skin_lesions,
CASE
WHEN obs regexp "!!1128=" THEN @musculoskeletal_exam := GetValues(obs, 1128)
ELSE @musculoskeletal_exam := null
END AS musculoskeletal_exam,
CASE
WHEN obs regexp "!!1129=" THEN @neurologic_exam := GetValues(obs, 1129)
ELSE @neurologic_exam := null
END AS neurologic_exam,
CASE
WHEN obs regexp "!!2018=" THEN @physical_examination_notes := GetValues(obs, 2018)
ELSE @physical_examination_notes := null
END AS physical_examination_notes,
CASE
WHEN obs REGEXP "!!846=1115!!" THEN @ct_scan_head := 1 -- Normal
WHEN obs REGEXP "!!846=1116!!" THEN @ct_scan_head := 2 -- Abnormal
ELSE @ct_scan_head := null
END AS ct_scan_head,
CASE
WHEN obs REGEXP "!!9839=1115!!" THEN @ct_scan_neck := 1 -- Normal
WHEN obs REGEXP "!!9839=1116!!" THEN @ct_scan_neck := 2 -- Abnormal
ELSE @ct_scan_neck := null
END AS ct_scan_neck,
CASE
WHEN obs REGEXP "!!7113=1115!!" THEN @ct_scan_chest := 1 -- Normal
WHEN obs REGEXP "!!7113=1116!!" THEN @ct_scan_chest := 2 -- Abnormal
ELSE @ct_scan_chest := null
END AS ct_scan_chest,
CASE
WHEN obs REGEXP "!!9840=1115!!" THEN @ct_scan_spine := 1 -- Normal
WHEN obs REGEXP "!!9840=1116!!" THEN @ct_scan_spine := 2 -- Abnormal
ELSE @ct_scan_spine := null
END AS ct_scan_spine,
CASE
WHEN obs REGEXP "!!7114=1115!!" THEN @ct_scan_abdominal := 1 -- Normal
WHEN obs REGEXP "!!7114=1116!!" THEN @ct_scan_abdominal := 2 -- Abnormal
ELSE @ct_scan_abdominal := null
END AS ct_scan_abdominal,
CASE
WHEN obs REGEXP "!!7115=1115!!" THEN @ultrasound_renal := 1 -- Normal
WHEN obs REGEXP "!!7115=1116!!" THEN @ultrasound_renal := 2 -- Abnormal
ELSE @ultrasound_renal := null
END AS ultrasound_renal,
CASE
WHEN obs REGEXP "!!852=1115!!" THEN @ultrasound_hepatic := 1 -- Normal
WHEN obs REGEXP "!!852=1116!!" THEN @ultrasound_hepatic := 2 -- Abnormal
ELSE @ultrasound_hepatic := null
END AS ultrasound_hepatic,
CASE
WHEN obs REGEXP "!!6221=1115!!" THEN @obstetric_ultrasound_ := 1 -- Normal
WHEN obs REGEXP "!!6221=1116!!" THEN @obstetric_ultrasound := 2 -- Abnormal
ELSE @obstetric_ultrasound := null
END AS obstetric_ultrasound,
CASE
WHEN obs REGEXP "!!845=1115!!" THEN @ultrasound_abdomen := 1 -- Normal
WHEN obs REGEXP "!!845=1116!!" THEN @ultrasound_abdomen := 2 -- Abnormal
WHEN obs REGEXP "!!845=5103!!" THEN @ultrasound_abdomen := 3 -- Abdominal mass
ELSE @ultrasound_abdomen := null
END AS ultrasound_abdomen,
CASE
WHEN obs REGEXP "!!9596=1115!!" THEN @breast_ultrasound := 1 -- Normal
WHEN obs REGEXP "!!9596=1116!!" THEN @breast_ultrasound := 2 -- Abnormal
ELSE @breast_ultrasound := null
END AS breast_ultrasound,
CASE
WHEN obs REGEXP "!!394=1115!!" THEN @xray_shoulder := 1 -- Normal
WHEN obs REGEXP "!!394=1116!!" THEN @xray_shoulder := 2 -- Abnormal
ELSE @xray_shoulder := null
END AS xray_shoulder,
CASE
WHEN obs REGEXP "!!392=1115!!" THEN @xray_pelvis := 1 -- Normal
WHEN obs REGEXP "!!392=1116!!" THEN @xray_pelvis := 2 -- Abnormal
ELSE @xray_pelvis := null
END AS xray_pelvis,
CASE
WHEN obs REGEXP "!!101=1115!!" THEN @xray_abdomen := 1 -- Normal
WHEN obs REGEXP "!!101=1116!!" THEN @xray_abdomen := 2 -- Abnormal
ELSE @xray_abdomen := null
END AS xray_abdomen,
CASE
WHEN obs REGEXP "!!386=1115!!" THEN @xray_skull := 1 -- Normal
WHEN obs REGEXP "!!386=1116!!" THEN @xray_skull := 2 -- Abnormal
ELSE @xray_skull := null
END AS xray_skull,
CASE
WHEN obs REGEXP "!!380=1115!!" THEN @xray_leg := 1 -- Normal
WHEN obs REGEXP "!!380=1116!!" THEN @xray_leg := 2 -- Abnormal
ELSE @xray_leg := null
END AS xray_leg,
CASE
WHEN obs REGEXP "!!382=1115!!" THEN @xray_hand := 1 -- Normal
WHEN obs REGEXP "!!382=1116!!" THEN @xray_hand := 2 -- Abnormal
ELSE @xray_hand := null
END AS xray_hand,
CASE
WHEN obs REGEXP "!!384=1115!!" THEN @xray_foot := 1 -- Normal
WHEN obs REGEXP "!!384=1116!!" THEN @xray_foot := 2 -- Abnormal
ELSE @xray_foot := null
END AS xray_foot,
CASE
WHEN obs REGEXP "!!12=1115!!" THEN @xray_chest := 1 -- Normal
WHEN obs REGEXP "!!12=1116!!" THEN @xray_chest := 2 -- Abnormal
ELSE @xray_chest := null
END AS xray_chest,
CASE
WHEN obs REGEXP "!!377=1115!!" THEN @xray_arm := 1 -- Normal
WHEN obs REGEXP "!!377=1116!!" THEN @xray_arm := 2 -- Abnormal
ELSE @xray_arm := null
END AS xray_arm,
CASE
WHEN obs REGEXP "!!390=1115!!" THEN @xray_spine := 1 -- Normal
WHEN obs REGEXP "!!390=1116!!" THEN @xray_spine:= 2 -- Abnormal
ELSE @xray_spine := null
END AS xray_spine,
CASE
WHEN obs REGEXP "!!1536=1115!!" THEN @echo_test := 1 -- Normal
WHEN obs REGEXP "!!1536=1116!!" THEN @echo_test := 2 -- Abnormal
WHEN obs REGEXP "!!1536=1538!!" THEN @echo_test := 3 -- Dilated cardiomyopathy
WHEN obs REGEXP "!!1536=1532!!" THEN @echo_test := 4 -- Left ventricular hypertrophy
WHEN obs REGEXP "!!1536=1533!!" THEN @echo_test := 5 -- Right ventricular hypertophy
WHEN obs REGEXP "!!1536=5622!!" THEN @echo_test := 6 -- Other (non-coded)
ELSE @echo_test := null
END AS echo_test,
CASE
WHEN obs REGEXP "!!9881=1115!!" THEN @mri_head := 1 -- Normal
WHEN obs REGEXP "!!9881=1116!!" THEN @mri_head := 2 -- Abnormal
ELSE @mri_head := null
END AS mri_head,
CASE
WHEN obs REGEXP "!!9882=1115!!" THEN @mri_neck := 1 -- Normal
WHEN obs REGEXP "!!9882=1116!!" THEN @mri_neck := 2 -- Abnormal
ELSE @mri_neck := null
END AS mri_neck,
CASE
WHEN obs REGEXP "!!9951=1115!!" THEN @mri_arms := 1 -- Normal
WHEN obs REGEXP "!!9951=1116!!" THEN @mri_arms := 2 -- Abnormal
ELSE @mri_arms := null
END AS mri_arms,
CASE
WHEN obs REGEXP "!!9883=1115!!" THEN @mri_chest := 1 -- Normal
WHEN obs REGEXP "!!9883=1116!!" THEN @mri_chest := 2 -- Abnormal
ELSE @mri_chest := null
END AS mri_chest,
CASE
WHEN obs REGEXP "!!9885=1115!!" THEN @mri_spine := 1 -- Normal
WHEN obs REGEXP "!!9885=1116!!" THEN @mri_spine := 2 -- Abnormal
ELSE @mri_spine := null
END AS mri_spine,
CASE
WHEN obs REGEXP "!!9884=1115!!" THEN @mri_abdominal := 1 -- Normal
WHEN obs REGEXP "!!9884=1116!!" THEN @mri_abdominal := 2 -- Abnormal
ELSE @mri_abdominal := null
END AS mri_abdominal,
CASE
WHEN obs REGEXP "!!9952=1115!!" THEN @mri_pelvic := 1 -- Normal
WHEN obs REGEXP "!!9952=1116!!" THEN @mri_pelvic := 2 -- Abnormal
ELSE @mri_pelvic := null
END AS mri_pelvic,
CASE
WHEN obs REGEXP "!!9953=1115!!" THEN @mri_legs := 1 -- Normal
WHEN obs REGEXP "!!9953=1116!!" THEN @mri_legs := 2 -- Abnormal
ELSE @mri_legs := null
END AS mri_legs,
CASE
WHEN obs REGEXP "!!10077=" THEN @imaging_results_description := GetValues(obs, 10077)
ELSE @imaging_results_description := null
END AS imaging_results_description,
CASE
WHEN obs REGEXP "!!9057=" THEN @clinical_media_caption_text := GetValues(obs, 9057)
ELSE @clinical_media_caption_text := null
END AS clinical_media_caption_text,
CASE
WHEN obs REGEXP "!!10124=" THEN @other_imaging_results := GetValues(obs, 10124)
ELSE @other_imaging_results := null
END AS other_imaging_results,
CASE
WHEN obs REGEXP "!!6583=1107!!" THEN @lab_test_ordered_for_next_visit := 1 -- None
WHEN obs REGEXP "!!6583=790!!" THEN @lab_test_ordered_for_next_visit := 2 -- Serum creatinine
WHEN obs REGEXP "!!6583=1019!!" THEN @lab_test_ordered_for_next_visit := 3 -- Complete blood count
WHEN obs REGEXP "!!6583=953!!" THEN @lab_test_ordered_for_next_visit := 4 -- Liver function tests
WHEN obs REGEXP "!!6583=10205!!" THEN @lab_test_ordered_for_next_visit := 5 -- Serum free light chain test
WHEN obs REGEXP "!!6583=8596!!" THEN @lab_test_ordered_for_next_visit := 6 -- Urine protein electrophoresis, 24hrs
WHEN obs REGEXP "!!6583=8595!!" THEN @lab_test_ordered_for_next_visit := 7 -- Serum protein electrophoresis
WHEN obs REGEXP "!!6583=5622!!" THEN @lab_test_ordered_for_next_visit := 8 -- Other (non-coded)
WHEN obs REGEXP "!!6583=1327!!" THEN @lab_test_ordered_for_next_visit := 9 -- Reticulocytes %, microscopic exam
WHEN obs REGEXP "!!6583=9009!!" THEN @lab_test_ordered_for_next_visit := 10 -- Hemoglobin with electrophoresis
ELSE @lab_test_ordered_for_next_visit := null
END AS lab_test_ordered_for_next_visit,
CASE
WHEN obs REGEXP "!!9538=" THEN @other_lab_test := GetValues(obs, 9538)
ELSE @other_lab_test := null
END AS other_lab_test,
CASE
WHEN obs REGEXP "!!679=" THEN @red_blood_cells_count := GetValues(obs, 679)
ELSE @red_blood_cells_count := null
END AS red_blood_cells_count,
CASE
WHEN obs REGEXP "!!21=" THEN @hemoglobin := GetValues(obs, 21)
ELSE @hemoglobin := null
END AS hemoglobin,
CASE
WHEN obs REGEXP "!!851=" THEN @mean_corpuscular_volume := GetValues(obs, 851)
ELSE @mean_corpuscular_volume := null
END AS mean_corpuscular_volume,
CASE
WHEN obs REGEXP "!!1018=" THEN @mean_corpuscular_hemoglobin := GetValues(obs, 1018)
ELSE @mean_corpuscular_hemoglobin := null
END AS mean_corpuscular_hemoglobin,
CASE
WHEN obs REGEXP "!!1017=" THEN @mean_cell_hemoglobin_concentration := GetValues(obs, 1017)
ELSE @mean_cell_hemoglobin_concentration := null
END AS mean_cell_hemoglobin_concentration,
CASE
WHEN obs REGEXP "!!1016=" THEN @red_cell_distribution_width := GetValues(obs, 1016)
ELSE @red_cell_distribution_width := null
END AS red_cell_distribution_width,
CASE
WHEN obs REGEXP "!!729=" THEN @platelets_count := GetValues(obs, 729)
ELSE @platelets_count := null
END AS platelets_count,
CASE
WHEN obs REGEXP "!!678=" THEN @serum_white_blood_cells_count := GetValues(obs, 678)
ELSE @serum_white_blood_cells_count := null
END AS serum_white_blood_cells_count,
CASE
WHEN obs REGEXP "!!1330=" THEN @absolute_neutrophil_count := GetValues(obs, 1330)
ELSE @absolute_neutrophil_count := null
END AS absolute_neutrophil_count,
CASE
WHEN obs regexp "!!1015=" THEN @hematocrit := GetValues(obs, 1015)
ELSE @hematocrit := null
END AS hematocrit,
CASE
WHEN obs REGEXP "!!6134=" THEN @serum_uric_acid_test := GetValues(obs, 6134)
ELSE @serum_uric_acid_test := null
END AS serum_uric_acid_test,
CASE
WHEN obs REGEXP "!!790=" THEN @serum_creatinine := GetValues(obs, 790)
ELSE @serum_creatinine := null
END AS serum_creatinine,
CASE
WHEN obs REGEXP "!!1132=" THEN @serum_sodium := GetValues(obs, 1132)
ELSE @serum_sodium := null
END AS serum_sodium,
CASE
WHEN obs REGEXP "!!1133=" THEN @serum_potassium := GetValues(obs, 1133)
ELSE @serum_potassium := null
END AS serum_potassium,
CASE
WHEN obs REGEXP "!!1134=" THEN @serum_chloride := GetValues(obs, 1134)
ELSE @serum_chloride := null
END AS serum_chloride,
CASE
WHEN obs REGEXP "!!848=" THEN @serum_albumin := GetValues(obs, 848)
ELSE @serum_albumin := null
END AS serum_albumin,
CASE
WHEN obs REGEXP "!!8732=" THEN @serum_alpha_one_globulin := GetValues(obs, 8732)
ELSE @serum_alpha_one_globulin := null
END AS serum_alpha_one_globulin,
CASE
WHEN obs REGEXP "!!8733=" THEN @serum_alpha_two_globulin := GetValues(obs, 8733)
ELSE @serum_alpha_two_globulin := null
END AS serum_alpha_two_globulin,
CASE
WHEN obs REGEXP "!!8734=" THEN @serum_beta_globulin := GetValues(obs, 8734)
ELSE @serum_beta_globulin := null
END AS serum_beta_globulin,
CASE
WHEN obs REGEXP "!!8735=" THEN @serum_gamma_globulin := GetValues(obs, 8735)
ELSE @serum_gamma_globulin := null
END AS serum_gamma_globulin,
CASE
WHEN obs REGEXP "!!8731=" THEN @serum_m_protein := GetValues(obs, 8731)
ELSE @serum_m_protein := null
END AS serum_m_protein,
CASE
WHEN obs REGEXP "!!8737=" THEN @urine_alpha_one_globulin := GetValues(obs, 8737)
ELSE @urine_alpha_one_globulin := null
END AS urine_alpha_one_globulin,
CASE
WHEN obs REGEXP "!!8738=" THEN @urine_alpha_two_globulin := GetValues(obs, 8738)
ELSE @urine_alpha_two_globulin := null
END AS urine_alpha_two_globulin,
CASE
WHEN obs REGEXP "!!8739=" THEN @urine_beta_globulin := GetValues(obs, 8739)
ELSE @urine_beta_globulin := null
END AS urine_beta_globulin,
CASE
WHEN obs REGEXP "!!8740=" THEN @urine_gamma_globulin := GetValues(obs, 8740)
ELSE @urine_gamma_globulin := null
END AS urine_gamma_globulin,
CASE
WHEN obs REGEXP "!!849=" THEN @urinary_albumin := GetValues(obs, 849)
ELSE @urinary_albumin := null
END AS urinary_albumin,
CASE
WHEN obs REGEXP "!!8736=" THEN @urine_m_protein := GetValues(obs, 8736)
ELSE @urine_m_protein := null
END AS urine_m_protein,
CASE
WHEN obs regexp "!!2324=" THEN @serum_calcium_level := GetValues(obs, 2324)
ELSE @serum_calcium_level := null
END AS serum_calcium_level,
CASE
WHEN obs REGEXP "!!10195=" THEN @kappa_light_chains := GetValues(obs, 10195)
ELSE @kappa_light_chains := null
END AS kappa_light_chains,
CASE
WHEN obs REGEXP "!!10197=" THEN @kappa_lambda_ratio := GetValues(obs, 10197)
ELSE @kappa_lambda_ratio := null
END AS kappa_lambda_ratio,
CASE
WHEN obs REGEXP "!!1984=664!!" THEN @pus_cells_in_urine := 1 -- Negative
WHEN obs REGEXP "!!1984=703!!" THEN @pus_cells_in_urine := 2 -- Positive
WHEN obs REGEXP "!!1984=2074!!" THEN @pus_cells_in_urine := 3 -- Strong positive
WHEN obs REGEXP "!!1984=2075!!" THEN @pus_cells_in_urine := 4 -- Stronger positive
ELSE @pus_cells_in_urine := null
END AS pus_cells_in_urine,
CASE
WHEN obs REGEXP "!!2339=664!!" THEN @protein_in_urine := 1 -- Negative
WHEN obs REGEXP "!!2339=703!!" THEN @protein_in_urine := 2 -- Positive
WHEN obs REGEXP "!!2339=2074!!" THEN @protein_in_urine := 3 -- Strong positive
WHEN obs REGEXP "!!2339=2075!!" THEN @protein_in_urine := 4 -- Stronger positive
ELSE @protein_in_urine := null
END AS protein_in_urine,
CASE
WHEN obs REGEXP "!!6337=664!!" THEN @leukocytes_in_urine := 1 -- Negative
WHEN obs REGEXP "!!6337=703!!" THEN @leukocytes_in_urine := 2 -- Positive
WHEN obs REGEXP "!!6337=2074!!" THEN @leukocytes_in_urine := 3 -- Strong positive
WHEN obs REGEXP "!!6337=2075!!" THEN @leukocytes_in_urine := 4 -- Stronger positive
WHEN obs REGEXP "!!6337=2301!!" THEN @leukocytes_in_urine := 5 -- 1+
WHEN obs REGEXP "!!6337=10900!!" THEN @leukocytes_in_urine := 6 -- Trace
ELSE @leukocytes_in_urine := null
END AS leukocytes_in_urine,
CASE
WHEN obs REGEXP "!!7276=664!!" THEN @ketones_in_urine := 1 -- Negative
WHEN obs REGEXP "!!7276=703!!" THEN @ketones_in_urine := 2 -- Positive
WHEN obs REGEXP "!!7276=2074!!" THEN @ketones_in_urine := 3 -- Strong positive
WHEN obs REGEXP "!!7276=2075!!" THEN @ketones_in_urine := 4 -- Stronger positive
WHEN obs REGEXP "!!7276=1138!!" THEN @ketones_in_urine := 5 -- Indeterminate
WHEN obs REGEXP "!!7276=10900!!" THEN @ketones_in_urine := 6 -- Trace
WHEN obs REGEXP "!!7276=2301!!" THEN @ketones_in_urine := 7 -- 1+
ELSE @ketones_in_urine := null
END AS ketones_in_urine,
CASE
WHEN obs REGEXP "!!2340=664!!" THEN @glucose_in_urine := 1 -- Negative
WHEN obs REGEXP "!!2340=703!!" THEN @glucose_in_urine := 2 -- Positive
WHEN obs REGEXP "!!2340=2074!!" THEN @glucose_in_urine := 3 -- Strong positive
WHEN obs REGEXP "!!2340=2075!!" THEN @glucose_in_urine := 4 -- Stronger positive
ELSE @glucose_in_urine := null
END AS glucose_in_urine,
CASE
WHEN obs REGEXP "!!9307=664!!" THEN @nitrites_in_urine := 1 -- Negative
WHEN obs REGEXP "!!9307=703!!" THEN @nitrites_in_urine := 2 -- Positive
WHEN obs REGEXP "!!9307=2074!!" THEN @nitrites_in_urine := 3 -- Strong positive
WHEN obs REGEXP "!!9307=2075!!" THEN @nitrites_in_urine := 4 -- Stronger positive
ELSE @nitrites_in_urine := null
END AS nitrites_in_urine,
CASE
WHEN obs REGEXP "!!1327=" THEN @reticulocytes_percentage := GetValues(obs, 1327)
ELSE @reticulocytes_percentage := null
END AS reticulocytes_percentage,
CASE
WHEN obs REGEXP "!!655=" THEN @serum_total_bilirubin := GetValues(obs, 655)
ELSE @serum_total_bilirubin := null
END AS serum_total_bilirubin,
CASE
WHEN obs REGEXP "!!1297=" THEN @serum_direct_bilirubin := GetValues(obs, 1297)
ELSE @serum_direct_bilirubin := null
END AS serum_direct_bilirubin,
CASE
WHEN obs REGEXP "!!6123=" THEN @gamma_glutamyl_transferase := GetValues(obs, 6123)
ELSE @gamma_glutamyl_transferase := null
END AS gamma_glutamyl_transferase,
CASE
WHEN obs REGEXP "!!653=" THEN @serum_glutamic_oxaloacetic_transaminase := GetValues(obs, 653)
ELSE @serum_glutamic_oxaloacetic_transaminase := null
END AS serum_glutamic_oxaloacetic_transaminase,
CASE
WHEN obs REGEXP "!!654=" THEN @serum_glutamic_pyruvic_transaminase := GetValues(obs, 654)
ELSE @serum_glutamic_pyruvic_transaminase := null
END AS serum_glutamic_pyruvic_transaminase,
CASE
WHEN obs REGEXP "!!717=" THEN @serum_total_protein := GetValues(obs, 717)
ELSE @serum_total_protein := null
END AS serum_total_protein,
CASE
WHEN obs REGEXP "!!785=" THEN @serum_alkaline_phospahatase := GetValues(obs, 785)
ELSE @serum_alkaline_phospahatase := null
END AS serum_alkaline_phospahatase,
CASE
WHEN obs REGEXP "!!1014=" THEN @serum_lactate_dehydrogenase := GetValues(obs, 1014)
ELSE @serum_lactate_dehydrogenase := null
END AS serum_lactate_dehydrogenase,
CASE
WHEN obs REGEXP "!!9538=" THEN @lab_results_notes := GetValues(obs, 9538)
ELSE @lab_results_notes := null
END AS lab_results_notes,
CASE
WHEN obs REGEXP "!!8723=10586!!" THEN @oncology_treatment_plan := 1 -- In remission
WHEN obs REGEXP "!!8723=6575!!" THEN @oncology_treatment_plan := 2 -- Chemotherapy
WHEN obs REGEXP "!!8723=1107!!" THEN @oncology_treatment_plan := 3 -- None
WHEN obs REGEXP "!!8723=5622!!" THEN @oncology_treatment_plan := 4 -- Other (non-coded)
END AS oncology_treatment_plan,
CASE
WHEN obs REGEXP "!!10039=" THEN @other_treatment_plan := GetValues(obs, 10039)
END AS other_treatment_plan,
CASE
WHEN obs REGEXP "!!10584=1260!!" THEN @remission_plan := 1 -- Stop all medications
WHEN obs REGEXP "!!10584=10581!!" THEN @remission_plan := 2 -- Continue drug holiday
WHEN obs REGEXP "!!10584=10582!!" THEN @remission_plan := 3 -- Start maintenance therapy
WHEN obs REGEXP "!!10584=10583!!" THEN @remission_plan := 4 -- Continue maintenance therapy
END AS remission_plan,
CASE
WHEN obs REGEXP "!!10585" THEN @remission_start_date := GetValues(obs, 10585)
END AS remission_start_date,
CASE
WHEN obs REGEXP "!!10198=88!!" THEN @mm_supportive_care_plan := 1 -- Aspirin
WHEN obs REGEXP "!!10198=257!!" THEN @mm_supportive_care_plan := 2 -- Folic acid
WHEN obs REGEXP "!!10198=8598!!" THEN @mm_supportive_care_plan := 3 -- Vitamin B12 supplement, injection
WHEN obs REGEXP "!!10198=8597!!" THEN @mm_supportive_care_plan := 4 -- Vitamin D supplements
WHEN obs REGEXP "!!10198=1195!!" THEN @mm_supportive_care_plan := 5 -- Antibiotics
WHEN obs REGEXP "!!10198=8410!!" THEN @mm_supportive_care_plan := 6 -- Anticoagulant medication
WHEN obs REGEXP "!!10198=7458!!" THEN @mm_supportive_care_plan := 7 -- IV fluid injection, drug route
WHEN obs REGEXP "!!10198=8479!!" THEN @mm_supportive_care_plan := 8 -- Thalidomide
WHEN obs REGEXP "!!10198=10140!!" THEN @mm_supportive_care_plan := 9 -- Zoledronic acid
WHEN obs REGEXP "!!10198=7207!!" THEN @mm_supportive_care_plan := 10 -- Decadron
WHEN obs REGEXP "!!10198=5622!!" THEN @mm_supportive_care_plan := 11 -- Other (non-coded)
ELSE @mm_supportive_care_plan := null
END AS mm_supportive_care_plan,
CASE
WHEN obs REGEXP "!!1915=" THEN @freetext_mm_supportive_care_plan := GetValues(mmfo.obs_string, 10199)
ELSE @freetext_mm_supportive_care_plan := null
END AS freetext_mm_supportive_care_plan,
CASE
WHEN obs REGEXP "!!10170=10141!!" THEN @mm_signs_symptoms := 1 -- Hypercalcemia
WHEN obs REGEXP "!!10170=1885!!" THEN @mm_signs_symptoms := 2 -- Renal failure
WHEN obs REGEXP "!!10170=3!!" THEN @mm_signs_symptoms := 3 -- Anemia
WHEN obs REGEXP "!!10170=8592!!" THEN @mm_signs_symptoms := 4 -- Lytic bone lesions
WHEN obs REGEXP "!!10170=5978!!" THEN @mm_signs_symptoms := 5 -- Nausea
WHEN obs REGEXP "!!10170=5949!!" THEN @mm_signs_symptoms := 6 -- Fatigue
WHEN obs REGEXP "!!10170=5622!!" THEN @mm_signs_symptoms := 7 -- Other (non-coded)
ELSE @mm_signs_symptoms := null
END AS mm_signs_symptoms,
CASE
WHEN obs REGEXP "!!1915=" THEN @freetext_mm_signs_symptoms := GetValues(mmfo.obs_string, 10171)
ELSE @freetext_mm_signs_symptoms := null
END AS freetext_mm_signs_symptoms,
CASE
WHEN obs REGEXP "!!9869=1256!!" THEN @chemotherapy_plan := 1 -- Start drugs
WHEN obs REGEXP "!!9869=1259!!" THEN @chemotherapy_plan := 2 -- Change regimen
WHEN obs REGEXP "!!9869=1260!!" THEN @chemotherapy_plan := 3 -- Stop all medications
WHEN obs REGEXP "!!9869=1257!!" THEN @chemotherapy_plan := 4 -- Continue regimen
WHEN obs REGEXP "!!9869=6576!!" THEN @chemotherapy_plan := 5 -- Chemotherapy regimen modifications
ELSE @chemotherapy_plan := null
END AS chemotherapy_plan,
CASE
WHEN obs REGEXP "!!1190=" THEN @chemo_start_date := GetValues(obs,1190)
ELSE @chemo_start_date := null
END AS chemo_start_date,
CASE
WHEN obs REGEXP "!!6643=[0-9]" THEN @chemo_cycle := CAST(GetValues(obs,6643) AS unsigned)
ELSE @chemo_cycle := null
END AS chemo_cycle,
CASE
WHEN obs REGEXP "!!9927=1267!!" THEN @reason_chemo_stop := 1 -- Completed
WHEN obs REGEXP "!!9927=7391!!" THEN @reason_chemo_stop := 2 -- Resistant
WHEN obs REGEXP "!!9927=6629!!" THEN @reason_chemo_stop := 3 -- Progressive disease
WHEN obs REGEXP "!!9927=6627!!" THEN @reason_chemo_stop := 4 -- Partial response
WHEN obs REGEXP "!!9927=1879!!" THEN @reason_chemo_stop := 5 -- Toxicity, cause
WHEN obs REGEXP "!!9927=5622!!" THEN @reason_chemo_stop := 6 -- Other (non-coded)
ELSE @reason_chemo_stop := null
END AS reason_chemo_stop,
CASE
WHEN obs REGEXP "!!9918=" THEN @chemotherapy_regimen := normalize_chemo_drugs(obs, '9918')
ELSE @chemotherapy_regimen := null
END AS chemotherapy_regimen,
CASE
WHEN obs REGEXP "!!1899=[0-9]" THEN @dosage_in_milligrams := CAST(GetValues(obs, 1899) AS unsigned)
ELSE @dosage_in_milligrams := null
END AS dosage_in_milligrams,
CASE
WHEN obs REGEXP "!!7463=7458!!" THEN @drug_route := 1 -- IV fluid injection
WHEN obs REGEXP "!!7463=10078!!" THEN @drug_route := 2 -- Intrathecal
WHEN obs REGEXP "!!7463=10079!!" THEN @drug_route := 3 -- Intra ommaya
WHEN obs REGEXP "!!7463=7597!!" THEN @drug_route := 4 -- Subcutaneous injection
WHEN obs REGEXP "!!7463=7581!!" THEN @drug_route := 5 -- Intramuscular injection
WHEN obs REGEXP "!!7463=7609!!" THEN @drug_route := 6 -- Intrarterial injection
WHEN obs REGEXP "!!7463=7616!!" THEN @drug_route := 7 -- Intradermal injection
WHEN obs REGEXP "!!7463=7447!!" THEN @drug_route := 8 -- Oral administration
ELSE @drug_route := null
END AS drug_route,
CASE
WHEN obs REGEXP "!!1915=" THEN @freetext_chemo_drug := GetValues(mmfo.obs_string, 9923)
ELSE @freetext_chemo_drug := null
END AS freetext_chemo_drug,
CASE
WHEN obs REGEXP "!!1895=" THEN @other_drugs := GetValues(obs, 1895)
ELSE @other_drugs := null
END AS other_drugs,
CASE
WHEN obs REGEXP "!!1779=" THEN @other_medication := GetValues(obs, 1779)
ELSE @other_medication := null
END AS other_medication,
CASE
WHEN obs REGEXP "!!2206=9220!!" THEN @reason_for_medication_use := 1 -- Adjuvant intent
WHEN obs REGEXP "!!2206=8428!!" THEN @reason_for_medication_use := 2 -- Curative care
ELSE @reason_for_medication_use := null
END AS reason_for_medication_use,
CASE
WHEN obs REGEXP "!!7222=" THEN @assessment_notes := GetValues(obs, 7222)
END AS assessment_notes,
CASE
WHEN obs REGEXP "!!6327=8728!!" THEN @education_given_today := 1 -- None
WHEN obs REGEXP "!!6327=8742!!" THEN @education_given_today := 2 -- Thalidomide and lenalidomide education
WHEN obs REGEXP "!!6327=1905!!" THEN @education_given_today := 3 -- Physiotherapy services
WHEN obs REGEXP "!!6327=10208!!" THEN @education_given_today := 4 -- Emergency return precaution
WHEN obs REGEXP "!!6327=8730!!" THEN @education_given_today := 5 -- Pregnancy prevention
WHEN obs REGEXP "!!6327=8371!!" THEN @education_given_today := 6 -- Treatment supporter preparation
WHEN obs REGEXP "!!6327=5622!!" THEN @education_given_today := 7 -- Other (non-coded)
ELSE @education_given_today := null
END AS education_given_today,
CASE
WHEN obs regexp "!!1272=" THEN @referrals_ordered := GetValues(obs, 1272)
ELSE @referral_ordered := null
END AS referral_ordered,
CASE
WHEN obs regexp "!!1915=" THEN @freetext_referrals_ordered := GetValues(mmfo.obs_string, 1932)
ELSE @freetext_referrals_ordered := null
END AS freetext_referrals_ordered,
CASE
WHEN obs regexp "!!5096=" THEN @next_app_date := GetValues(obs, 5096)
ELSE @next_app_date := null
END AS next_app_date
FROM
flat_multiple_myeloma_treatment_0 t1
JOIN
amrs.person `p` using (person_id)
LEFT JOIN
amrs.person_name `person_name` ON (t1.person_id = person_name.person_id
AND (person_name.voided IS NULL || person_name.voided = 0))
LEFT JOIN
multiple_myeloma_patient_identifiers `mmpi` ON t1.person_id = mmpi.person_id
LEFT JOIN
amrs.person_attribute `contacts` ON t1.person_id = contacts.person_id
AND (contacts.voided IS NULL || contacts.voided = 0) AND contacts.person_attribute_type_id = 10
LEFT JOIN
amrs.person_address `person_address` ON (t1.person_id = person_address.person_id)
LEFT JOIN
multiple_myeloma_freetext_obs `mmfo` ON (t1.person_id = mmfo.patient_id)
ORDER BY
person_id, DATE(encounter_datetime) DESC, encounter_type_sort_index DESC
);
SET @prev_id := NULL;
SET @cur_id := NULL;
SET @prev_encounter_datetime := null;
SET @cur_encounter_datetime := null;
SET @prev_clinical_datetime := null;
SET @cur_clinical_datetime := null;
SET @next_encounter_type := null;
SET @cur_encounter_type := null;
SET @prev_clinical_location_id := null;
SET @cur_clinical_location_id := null;
ALTER TABLE flat_multiple_myeloma_treatment_1 DROP prev_id, DROP cur_id;
DROP TABLE IF EXISTS flat_multiple_myeloma_treatment_2;
CREATE TEMPORARY TABLE flat_multiple_myeloma_treatment_2
(SELECT
*,
@prev_id := @cur_id as prev_id,
@cur_id := person_id as cur_id,
CASE
WHEN @prev_id = @cur_id THEN @prev_encounter_datetime := @cur_encounter_datetime
ELSE @prev_encounter_datetime := null
END AS next_encounter_datetime_multiple_myeloma,
@cur_encounter_datetime := encounter_datetime as cur_encounter_datetime,
CASE
when @prev_id = @cur_id THEN @next_encounter_type := @cur_encounter_type
ELSE @next_encounter_type := null
END AS next_encounter_type_multiple_myeloma,
@cur_encounter_type := encounter_type as cur_encounter_type,
CASE
when @prev_id = @cur_id THEN @prev_clinical_datetime := @cur_clinical_datetime
ELSE @prev_clinical_datetime := null
END AS next_clinical_datetime_multiple_myeloma,
CASE
when @prev_id = @cur_id THEN @prev_clinical_location_id := @cur_clinical_location_id
ELSE @prev_clinical_location_id := null
END AS next_clinical_location_id_multiple_myeloma,
CASE
when @prev_id = @cur_id THEN @prev_clinical_rtc_date := @cur_clinical_rtc_date
ELSE @prev_clinical_rtc_date := null
END AS next_clinical_rtc_date_multiple_myeloma,
CASE
when is_clinical_encounter THEN @cur_clinical_rtc_date := next_app_date
when @prev_id = @cur_id THEN @cur_clinical_rtc_date
ELSE @cur_clinical_rtc_date:= null
END AS cur_clinical_rtc_date
FROM
flat_multiple_myeloma_treatment_1
ORDER BY
person_id, DATE(encounter_datetime) DESC, encounter_type_sort_index DESC
);
ALTER TABLE flat_multiple_myeloma_treatment_2 DROP prev_id, DROP cur_id, DROP cur_encounter_type, DROP cur_encounter_datetime, DROP cur_clinical_rtc_date;
SET @prev_id := null;
SET @cur_id := null;
SET @prev_encounter_type := null;
SET @cur_encounter_type := null;
SET @prev_encounter_datetime := null;
SET @cur_encounter_datetime := null;
SET @prev_clinical_datetime := null;
SET @cur_clinical_datetime := null;
SET @prev_clinical_location_id := null;
SET @cur_clinical_location_id := null;
DROP TEMPORARY TABLE IF EXISTS flat_multiple_myeloma_treatment_3;
CREATE TEMPORARY TABLE flat_multiple_myeloma_treatment_3 (prev_encounter_datetime datetime, prev_encounter_type int, index person_enc (person_id, encounter_datetime DESC))
(SELECT
*,
@prev_id := @cur_id as prev_id,
@cur_id := t1.person_id as cur_id,
CASE
when @prev_id = @cur_id THEN @prev_encounter_type := @cur_encounter_type
ELSE @prev_encounter_type := null
END AS prev_encounter_type_multiple_myeloma,
@cur_encounter_type := encounter_type as cur_encounter_type,
CASE
when @prev_id = @cur_id THEN @prev_encounter_datetime := @cur_encounter_datetime
ELSE @prev_encounter_datetime := null
END AS prev_encounter_datetime_multiple_myeloma,
@cur_encounter_datetime := encounter_datetime as cur_encounter_datetime,
CASE
when @prev_id = @cur_id THEN @prev_clinical_datetime := @cur_clinical_datetime
ELSE @prev_clinical_datetime := null
END AS prev_clinical_datetime_multiple_myeloma,
CASE
when @prev_id = @cur_id THEN @prev_clinical_location_id := @cur_clinical_location_id
ELSE @prev_clinical_location_id := null
END AS prev_clinical_location_id_multiple_myeloma,
CASE
when @prev_id = @cur_id THEN @prev_clinical_rtc_date := @cur_clinical_rtc_date
ELSE @prev_clinical_rtc_date := null
END AS prev_clinical_rtc_date_multiple_myeloma,
CASE
when is_clinical_encounter THEN @cur_clinical_rtc_date := next_app_date
when @prev_id = @cur_id THEN @cur_clinical_rtc_date
ELSE @cur_clinical_rtc_date:= null
END AS cur_clinic_rtc_date
FROM
flat_multiple_myeloma_treatment_2 t1
ORDER BY
person_id, DATE(encounter_datetime), encounter_type_sort_index
);
SELECT
COUNT(*)
INTO @new_encounter_rows FROM
flat_multiple_myeloma_treatment_3;
SELECT @new_encounter_rows;
SET @total_rows_written := @total_rows_written + @new_encounter_rows;
SELECT @total_rows_written;
SET @dyn_sql := CONCAT("REPLACE INTO ", @write_table,
'(SELECT
null,
person_id,
encounter_id,
encounter_type,
DATE(t1.encounter_datetime) AS encounter_date,
visit_id,
location_id,
gender,
age,
identifiers,
person_name,
death_date,
phone_number,
county,
purpose_of_visit_triage,
covered_by_nhif,
nhif_status,
type_of_cancer,
freetext_cancer_type,
bp_systolic,
bp_diastolic,
heart_rate,
respiratory_rate,
temperature,
weight_kg,
height_cm,
blood_oxygen_saturation,
body_mass_index,
body_surface_area,
illness_currently_being_treated_for,
freetext_illness_currently_being_treated_for,
medications_currently_being_used,
other_medications_currently_being_used,
allergic_to_penicillin,
specific_allergic_reaction_to_penicillin,
allergic_to_sulfa,
specific_allergic_reaction_to_sulfa,
freetext_allergy,
name_of_drug_or_product_allergic_to,
specific_reaction_to_this_drug_or_product,
purpose_of_visit_mm_initial,
referred_by,
religion,
current_marital_status,
no_of_household_members,
no_of_children,
are_you_or_your_partner_currently_using_any_form_of_fp,
currently_breastfeeding,
clinic_travel_time,
mode_of_transport_to_clinic,
main_occupation,
freetext_main_occupation,
average_monthly_income,
level_of_education,
next_of_kin_level_of_education,
smokes_cigarettes,
uses_tobacco,
drinks_alcohol_sometimes,
family_history_of_cancer_or_chronic_illness_history,
specific_chronic_illness,
family_member_with_chronic_illness,
history_of_surgery,
freetext_history_of_surgery,
chief_complaint_review_of_systems,
pain,
pain_score,
heent_findings,
general_findings,
cardiopulmonary_findings,
gastrointestinal_findings,
genitourinary_findings,
last_menstrual_period,
musculoskeletal_findings,
chief_complaints_physical_exam,
freetext_chief_complaint,
ecog_performance_index,
general_exam,
heent_exam,
chest_exam,
heart_exam,
abdomen_exam,
urogenital_exam,
extremities_exam,
nodal_survey_exam,
masses_or_skin_lesions,
musculoskeletal_exam,
neurologic_exam,
physical_examination_notes,
ct_scan_head,
ct_scan_neck,
ct_scan_chest,
ct_scan_spine,
ct_scan_abdominal,
ultrasound_renal,
ultrasound_hepatic,
obstetric_ultrasound,
ultrasound_abdomen,
breast_ultrasound,
xray_shoulder,
xray_pelvis,
xray_abdomen,
xray_skull,
xray_leg,
xray_hand,
xray_foot,
xray_chest,
xray_arm,
xray_spine,
echo_test,
mri_head,
mri_neck,
mri_arms,
mri_chest,
mri_spine,
mri_abdominal,
mri_pelvic,
mri_legs,
imaging_results_description,
clinical_media_caption_text,
other_imaging_results,
lab_test_ordered_for_next_visit,
other_lab_test,
red_blood_cells_count,
hemoglobin,
mean_corpuscular_volume,
mean_corpuscular_hemoglobin,
mean_cell_hemoglobin_concentration,
red_cell_distribution_width,
platelets_count,
serum_white_blood_cells_count,
absolute_neutrophil_count,
hematocrit,
serum_uric_acid_test,
serum_creatinine,
serum_sodium,
serum_potassium,
serum_chloride,
serum_albumin,
serum_alpha_one_globulin,
serum_alpha_two_globulin,
serum_beta_globulin,
serum_gamma_globulin,
serum_m_protein,
urine_alpha_one_globulin,
urine_alpha_two_globulin,
urine_beta_globulin,
urine_gamma_globulin,
urinary_albumin,
urine_m_protein,
serum_calcium_level,
kappa_light_chains,
kappa_lambda_ratio,
pus_cells_in_urine,
protein_in_urine,
leukocytes_in_urine,
ketones_in_urine,
glucose_in_urine,
nitrites_in_urine,
reticulocytes_percentage,
serum_total_bilirubin,
serum_direct_bilirubin,
gamma_glutamyl_transferase,
serum_glutamic_oxaloacetic_transaminase,
serum_glutamic_pyruvic_transaminase,
serum_total_protein,
serum_alkaline_phospahatase,
serum_lactate_dehydrogenase,
lab_results_notes,
oncology_treatment_plan,
other_treatment_plan,
remission_plan,
remission_start_date,
mm_supportive_care_plan,
freetext_mm_supportive_care_plan,
mm_signs_symptoms,
freetext_mm_signs_symptoms,
chemotherapy_plan,
chemo_start_date,
chemo_cycle,
reason_chemo_stop,
chemotherapy_regimen,
dosage_in_milligrams,
drug_route,
freetext_chemo_drug,
other_drugs,
other_medication,
reason_for_medication_use,
assessment_notes,
education_given_today,
referral_ordered,
next_app_date,
prev_encounter_datetime_multiple_myeloma,
next_encounter_datetime_multiple_myeloma,
prev_encounter_type_multiple_myeloma,
next_encounter_type_multiple_myeloma,
prev_clinical_datetime_multiple_myeloma,
next_clinical_datetime_multiple_myeloma,
prev_clinical_location_id_multiple_myeloma,
next_clinical_location_id_multiple_myeloma,
prev_clinical_rtc_date_multiple_myeloma,
next_clinical_rtc_date_multiple_myeloma
FROM
flat_multiple_myeloma_treatment_3 t1
JOIN
amrs.location t2 USING (location_id))'
);
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @dyn_sql := CONCAT('DELETE t1 FROM ', @queue_table, ' t1 JOIN flat_multiple_myeloma_treatment_build_queue__0 t2 USING (person_id);');
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
-- SELECT @person_ids_count := (SELECT count(*) FROM flat_breast_cancer_screening_build_queue_2);
SET @dyn_sql := CONCAT('SELECT COUNT(*) INTO @person_ids_count FROM ', @queue_table, ';');
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
-- SELECT @person_ids_count as remaining_in_build_queue;
SET @cycle_length := TIMESTAMPDIFF(SECOND, @loop_start_time, NOW());
-- SELECT CONCAT('Cycle time: ',@cycle_length,' seconds');
SET @total_time := @total_time + @cycle_length;
SET @cycle_number := @cycle_number + 1;
-- SELECT ceil(@person_ids_count / cycle_size) as remaining_cycles;
SET @remaining_time := ceil((@total_time / @cycle_number) * ceil(@person_ids_count / cycle_size) / 60);
SELECT
@person_ids_count AS 'persons remaining',
@cycle_length AS 'Cycle time (s)',
CEIL(@person_ids_count / cycle_size) AS remaining_cycles,
@remaining_time AS 'Est time remaining (min)';
END WHILE;
IF (@query_type = "build") THEN
SET @dyn_sql := CONCAT('DROP TABLE ', @queue_table, ';');
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @total_rows_to_write := 0;
SET @dyn_sql := CONCAT("SELECT COUNT(*) INTO @total_rows_to_write FROM ", @write_table);
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @start_write := NOW();
SELECT
CONCAT(@start_write,
' : Writing ',
@total_rows_to_write,
' to ',
@primary_table);
SET @dyn_sql := CONCAT('REPLACE INTO ', @primary_table, '(SELECT * FROM ', @write_table, ');');
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET @finish_write := NOW();
SET @time_to_write := TIMESTAMPDIFF(SECOND, @start_write, @finish_write);
SELECT
CONCAT(@finish_write,
' : Completed writing rows. Time to write to primary table: ',
@time_to_write,
' seconds ');
SET @dyn_sql := CONCAT('drop table ', @write_table, ';');
PREPARE s1 FROM @dyn_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END IF;
SET @ave_cycle_length := ceil(@total_time / @cycle_number);
SELECT
CONCAT('Average Cycle Length: ',
@ave_cycle_length,
' second(s)');
SET @end := NOW();
INSERT INTO etl.flat_log VALUES (@start, @last_date_created, @table_version, TIMESTAMPDIFF(SECOND, @start, @end));
SELECT
CONCAT(@table_version,
': Time to complete: ',
TIMESTAMPDIFF(MINUTE, @start, @end),
' minutes');
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment