/* run this 1st to check no existing request lab-tests: */
SELECT *
FROM genomics.request_lab_test_results t1
WHERE t1.lab_test_id in (
SELECT lt.id
FROM genomics.lab_tests lt
JOIN genomics.lab_sections ls on lt.lab_section_id = ls.id
WHERE ls.section_name IN ('Genetics','Histopathology','Operating theatre','Storage')
);
/* dump data for comparison - live and test server post-modifications: */
SELECT r.request_number, r.year, ls.section_name, ls.is_active, lt.test_name,
lt.is_active, t1.result
FROM requests r
JOIN request_lab_test_results t1 on t1.request_id = r.id
JOIN lab_tests lt on t1.lab_test_id = lt.id
JOIN lab_sections ls on lt.lab_section_id = ls.id
ORDER BY t1.id
#===============================================================================
/* update existing lab sections */
UPDATE `genomics`.`lab_sections` SET `section_name`='Specimens - blood' WHERE
`section_name`='Specimens'; /* id = 2 */
UPDATE `genomics`.`lab_sections` SET `section_name`='Specimens - tumour' WHERE
`section_name`='Operating theatre'; /* id = 3 */
UPDATE `genomics`.`lab_sections` SET `section_name`='Specimens - FFPE' WHERE
`section_name`='Histopathology'; /* id = 4 */
UPDATE `genomics`.`lab_sections` SET `section_name`='Specimens - frozen' WHERE
`section_name`='Genetics'; /* id = 5 */
UPDATE `genomics`.`lab_sections` SET `section_name`='Approach' WHERE
`section_name`='Storage'; /* id = 6 */
UPDATE `genomics`.`lab_sections` SET `section_name`='DNA - blood' WHERE
`section_name`='DNA'; /* id = 7 */
/* new lab sections */
INSERT INTO `genomics`.`lab_sections` (`section_name`, `has_test_sign_out`) VALUES
('DNA - tumour','yes'), /* id = 9 */
('Consent withdrawal','yes'), /* id = 10 */
('Transfer to genetics', 'yes'); /* id = 11 */
/* set has_test_sign_out & is_active globally */
UPDATE `genomics`.`lab_sections` SET `has_test_sign_out`='Yes'
WHERE has_test_sign_out = 'No';
UPDATE `genomics`.`lab_sections` SET `is_active`='Yes' WHERE `is_active`='No';
/* set permitted sample type for all lab sections: */
TRUNCATE TABLE `genomics`.`lab_section_sample_type`;
INSERT INTO `genomics`.`lab_section_sample_type` (lab_section_id, sample_type_id)
SELECT `id`, 1 FROM `genomics`.`lab_sections`;
/* update existing lab-tests */
UPDATE `genomics`.`lab_tests` SET `test_name`='total_samples',
`field_label`='Total samples in group',
`lab_section_id`='6' WHERE `test_name`='additional_samples';
UPDATE `genomics`.`lab_tests` SET `test_name`='dna_sent_ccp',
`field_label`='DNA sent to CCP' WHERE `test_name`='sent_ccp';
UPDATE `genomics`.`lab_tests` SET `test_name`='dna_arrived_ccp',
`field_label`='DNA arrived CCP' WHERE `test_name`='arrived_ccp';
UPDATE `genomics`.`lab_tests` SET `test_name`='dna_consignment_number',
`field_label`='DNA consignment number' WHERE `test_name`='consignment_number';
UPDATE `genomics`.`lab_tests` SET `is_active`='no' WHERE `test_name` = 'edta1_qc_source';
/* rename existing unused lab-tests: */
UPDATE `genomics`.`lab_tests` SET `test_name`='edta10',`field_label`='EDTA 10ml',
`lab_section_id`=2, `is_active` = 'yes' WHERE `test_name`='blood_sample_taken';
UPDATE `genomics`.`lab_tests` SET `test_name`='sent_from_histopathology',
`field_label`='Sent from histopathology', `lab_section_id`=11, is_active='yes'
WHERE `test_name`='histopath_unfixed';
UPDATE `genomics`.`lab_tests` SET `test_name`='received_by_cytogenetics',
`field_label`='Received by cytogenetics', `lab_section_id`=11, is_active='yes'
WHERE `test_name`='histopathology_specimen';
UPDATE `genomics`.`lab_tests` SET `test_name`='tumour_fluidx',
`field_label`='Tumour Fluidx code', `lab_section_id`=9, `is_active`='yes'
WHERE `test_name`='edta2_fluidx';
UPDATE `genomics`.`lab_tests` SET `test_name`='tumour_qc_date',
`field_label`='QC datetime', `lab_section_id`=9, `is_active`='yes'
WHERE `test_name`='edta2_qc_date';
UPDATE `genomics`.`lab_tests` SET `test_name`='tumour_qc_type',
`field_label`='QC test type', `lab_section_id`=9, `is_active`='yes'
WHERE `test_name`='edta2_qc_type';
UPDATE `genomics`.`lab_tests` SET `test_name`='tumour_qc_result',
`field_label`='QC test result', `lab_section_id`=9, `is_active`='yes'
WHERE `test_name`='edta2_qc_result';
UPDATE `genomics`.`lab_tests` SET `test_name`='tumour_fluidx_vol',
`field_label`='Tumour FluidX vol', `lab_section_id`=9, `is_active`='yes'
WHERE `test_name`='edta2_fluidx_vol';
UPDATE `genomics`.`lab_tests` SET `test_name`='tumour_fluidx_rack_id',
`field_label`='Tumour DNA rack ID', `lab_section_id`=9, `is_active`='yes'
WHERE `test_name`='edta2_fluidx_rack_id';
UPDATE `genomics`.`lab_tests` SET `test_name`='tumour_fluidx_rack_well',
`field_label`='Tumour DNA well pos', `lab_section_id`=9, `is_active`='yes'
WHERE `test_name`='edta2_fluidx_rack_well';
UPDATE `genomics`.`lab_tests` SET `test_name`='family_id', `lab_section_id` = 6,
`field_label`='Family ID' WHERE `test_name`='tissue_section_scanned';
UPDATE `genomics`.`lab_tests` SET `test_name`='frozen_macrodissected',
`lab_section_id` = 5, `field_label`='Macrodissected'
WHERE `test_name`='tumour_cell_content';
UPDATE `genomics`.`lab_tests` SET `test_name`='frozen_dissection_details',
`lab_section_id` = 5, `field_label`='Macrodissection details'
WHERE `test_name`='blood_sample_recieved';
UPDATE `genomics`.`lab_tests` SET `test_name`='snap_freezing_start',
`lab_section_id` = 5, `field_label`='Snap freezing start'
WHERE `test_name`='sent_to_genetics';
UPDATE `genomics`.`lab_tests` SET `test_name`='tumour_lab_number',
`lab_section_id` = 9, `field_label`='DNA lab number'
WHERE `test_name`='dna_extraction_blood';
UPDATE `genomics`.`lab_tests` SET `test_name`='tumour_volume_stored',
`lab_section_id` = 9, `field_label`='Volume stored'
WHERE `test_name`='dna_extraction_tissue';
UPDATE `genomics`.`lab_tests` SET `test_name`='omics_consignment_number',
`lab_section_id` = 8, `field_label`='Omics consignment number'
WHERE `test_name`='specimen_from_theatre';
UPDATE `genomics`.`lab_tests` SET `test_name`='tracking_number',
`lab_section_id` = 8, `field_label`='Tracking number'
WHERE `test_name`='tissue_sample_received';
/* section 6 tests */
UPDATE `genomics`.`lab_tests` SET `test_name`='approach_date',
`field_label`='Approach date' WHERE `test_name`='part_number';
UPDATE `genomics`.`lab_tests` SET `test_name`='approach_method',
`field_label`='Approach method' WHERE `test_name`='rack_id';
UPDATE `genomics`.`lab_tests` SET `test_name`='first_outcome',
`field_label`='First outcome' WHERE `test_name`='rack_location';
UPDATE `genomics`.`lab_tests` SET `test_name`='followup_date',
`field_label`='Follow-up date' WHERE `test_name`='sample';
UPDATE `genomics`.`lab_tests` SET `test_name`='second_outcome',
`field_label`='Second outcome' WHERE `test_name`='source';
UPDATE `genomics`.`lab_tests` SET `test_name`='appointment_date',
`field_label`='Appointment date' WHERE `test_name`='vial_id';
/* new lab-tests - check lab_sections.id: */
INSERT INTO `genomics`.`lab_tests` (`test_name`, `field_label`, `lab_section_id`,
`test_type`, `has_results`) VALUES
/* Consent widthdrawal tests */
('withdrawal_date', 'Withdrawal date', '10', 'test', 'yes'),
('withdrawal_form', 'Withdrawal form', '10', 'test', 'yes'),
('withdrawal_option', 'Withdrawal option', '10', 'test', 'yes'),
/* Dispatch tests */
('omics_sent_ccp', 'Omics sent to CCP', '8', 'test', 'yes'),
('omics_arrived_ccp', 'Omics arrived CCP', '8', 'test', 'yes'),
/* Specimens - tumour tests */
('tumour_sample_id', 'Sample ID', '3', 'test', 'yes'),
('clinic_sample_type', 'Clinic sample type', '3', 'test', 'yes'),
('tumour_sample_taken', 'Sample taken', '3', 'test', 'yes'),
('tumour_type', 'Tumour type', '3', 'test', 'yes'),
('tissue_source', 'Tissue source', '3', 'test', 'yes'),
('number_of_biopsies', 'Number of biopsies', '3', 'test', 'yes'),
('biopsy_gauge', 'Gauge of biopsies', '3', 'test', 'yes'),
('excision_margin', 'Excision margin', '3', 'test', 'yes'),
('tumour_size', 'Tumour size', '3', 'test', 'yes'),
('dna_extraction_protocol', 'DNA extraction protocol', '3', 'test', 'yes'),
('prolonged_storage_method', 'Prolonged storage method', '3', 'test', 'yes'),
('unsent_sample_reason', 'Unsent sample reason', '3', 'test', 'yes'),
('tumour_sample_type', 'Tumour sample type', '3', 'test', 'yes'),
('number_of_sections', 'Number of sections', '3', 'test', 'yes'),
('section_thickness', 'Section thickness', '3', 'test', 'yes'),
('number_of_blocks', 'Number of blocks', '3', 'test', 'yes'),
/* Specimens - FFPE tests */
('ffpe_macrodissected', 'Macrodissected', '4', 'test', 'yes'),
('ffpe_dissection_details', 'Macrodissection details', '4', 'test', 'yes'),
('fixative_type', 'Fixative type', '4', 'test', 'yes'),
('fixation_start', 'Fixation start', '4', 'test', 'yes'),
('fixation_end', 'Fixation end', '4', 'test', 'yes'),
('processing_schedule', 'Processing schedule', '4', 'test', 'yes'),
('fixation_comments', 'Fixation comments', '4', 'test', 'yes'),
('formalin_duration', 'Time in formalin', '4', 'test', 'yes'),
('pre_invasive_elements', 'Pre-invasive elements', '4', 'test', 'yes');
/* ensure all tests have results entry */
UPDATE `genomics`.`lab_tests` SET `has_results`='yes' WHERE `test_type` = 'test';
/* set permitted sample type for lab tests: */
TRUNCATE TABLE `genomics`.`lab_test_sample_type`;
INSERT INTO `genomics`.`lab_test_sample_type` (lab_test_id, sample_type_id)
SELECT `id`, 1 FROM `genomics`.`lab_tests`;
/* update screens table with cancer types: */
SET foreign_key_checks = 0;
TRUNCATE TABLE `genomics`.`screens`;
INSERT INTO `genomics`.`screens` (description, category_id, active) VALUES
('Rare disease', 1, 'yes'),
('Adult Glioma', 2, 'yes'),
('Bladder Cancer', 2, 'yes'),
('Breast Cancer', 2, 'yes'),
('Carcinoma of Unknown Primary', 2, 'yes'),
('Childhood Cancer', 2, 'yes'),
('Colorectal Cancer', 2, 'yes'),
('Endometrial Carcinoma', 2, 'yes'),
('Hepatopancreatobiliary Cancer', 2, 'yes'),
('Lung Cancer', 2, 'yes'),
('Malignant Melanoma', 2, 'yes'),
('Ovarian Cancer', 2, 'yes'),
('Prostate Cancer', 2, 'yes'),
('Renal Cancer', 2, 'yes'),
('Sarcoma', 2, 'yes'),
('Testicular Germ Cell Tumour', 2, 'yes'),
('Upper Gastrointestinal Cancer', 2, 'yes'),
('Unknown Cancer', 2, 'yes');
SET foreign_key_checks = 1;
# UPDATE `genomics`.`request_initial_screen` SET `screen_id` = 1 WHERE `screen_id` = 3;
/* change lab_test_result_data_types 'fixation' to 'approach_response' */
UPDATE `genomics`.`lab_test_result_data_types` SET `description`='approach_response'
WHERE `description`='fixation';
UPDATE `genomics`.`lab_test_result_options` SET value = 'Interested'
WHERE value = 'fixed' and data_type_id = 2;
UPDATE `genomics`.`lab_test_result_options` SET value = 'Declined'
WHERE value = 'unfixed' and data_type_id = 2;
/* new lab_test_result_data_types drop-down select names */
INSERT INTO `genomics`.`lab_test_result_data_types` (description,is_active) VALUES
('withdrawal_form', 'yes'), /* 14 */
('withdrawal_option', 'yes'), /* 15 */
('approach_method', 'yes'), /* 16 */
('fixative_type', 'yes'), /* 17 */
('processing_schedule', 'yes'), /* 18 */
('clinic_sample_type', 'yes'), /* 19 */
('tumour_sample_type', 'yes'), /* 20 */
('tumour_type', 'yes'), /* 21 */
('tissue_source', 'yes'), /* 22 */
('excision_margin', 'yes'), /* 23 */
('extraction_protocol', 'yes'), /* 24 */
('prolonged_storage', 'yes'), /* 25 */
('unsent_sample_reason', 'yes'), /* 26 */
('numeric_range[1-36]', 'yes'), /* 27 */
('numeric_range[1-20]', 'yes'), /* 28 */
('numeric_range[1-15]', 'yes'); /* 29 */
('numeric_range[1-10]', 'yes'); /* 30 */
INSERT INTO `genomics`.`lab_test_result_options` (data_type_id, `value`, is_active) VALUES
(2, 'Considering', 'yes'),
(14, 'Adult/Child', 'yes'),
(14, 'Consultee', 'yes'),
(14, 'Additional findings', 'yes'),
(15, 'Partial', 'yes'),
(15, 'Full', 'yes'),
(16, 'Letter', 'yes'),
(16, 'Telephone', 'yes'),
(16, 'Appointment', 'yes'),
(17, 'Formal saline', 'yes'),
(17, 'Neutral buffered formalin', 'yes'),
(17, 'UMFix', 'yes'),
(17, 'Paxgene', 'yes'),
(17, 'Other', 'yes'),
(18, 'Overnight', 'yes'),
(18, 'Urgent', 'yes'),
(18, 'Extended', 'yes'),
(19, 'DNA Fibroblast', 'yes'),
(19, 'DNA FF Germline', 'yes'),
(19, 'DNA FFPE Tumour', 'yes'),
(19, 'DNA FF Tumour', 'yes'),
(19, 'DNA Blood Tumour', 'yes'),
(20, 'Section', 'yes'),
(20, 'Block', 'yes'),
(21, 'Metastatic', 'yes'),
(21, 'Primary', 'yes'),
(21, 'Recurrence', 'yes'),
(22, 'surgical resection', 'yes'),
(22, 'USS-guided biopsy', 'yes'),
(22, 'non-guided biopsy', 'yes'),
(22, 'CT-guided biopsy', 'yes'),
(22, 'endoscopic biopsy', 'yes'),
(22, 'stereotactically guided biopsy', 'yes'),
(22, 'MRI-guided biopsy', 'yes'),
(23, 'Clear: distance not stated', 'yes'),
(23, 'Clear: >5mm from margin', 'yes'),
(23, 'Clear: >1 but <=5mm', 'yes'),
(23, "<=1mm but doesn't reach margin", 'yes'),
(23, 'Tumour reaches excision margin', 'yes'),
(23, 'Uncertain', 'yes'),
(23, 'Margin not involved =>1mm', 'yes'),
(23, 'Margin not involved <1mm', 'yes'),
(23, 'Margin not involved 1-5mm', 'yes'),
(23, 'Not applicable', 'yes'),
(23, 'Not known', 'yes'),
(24, 'Covaris', 'yes'),
(24, 'Qiagen_80', 'yes'),
(24, 'Qiagen_90', 'yes'),
(24, 'Fresh_Frozen', 'yes'),
(25, 'refrigeration', 'yes'),
(25, 'vac_pack', 'yes'),
(25, 'refrigeration_and_vac_pack', 'yes'),
(25, 'room_temp', 'yes'),
(26, 'Tumour sample not taken', 'yes'),
(26, 'Tumour type not eligible', 'yes'),
(26, 'Insufficient sample post-Tx', 'yes'),
(26, 'Poorly cellular tumour', 'yes'),
(26, 'Insufficient DNA', 'yes'),
(26, 'No Cancer Diagnosed', 'yes');
/* run script/genomics_lab_test_result_options_map.pl */
