/* update existing lab sections */
UPDATE `genomics`.`lab_sections` SET `has_test_sign_out`='Yes' WHERE
`section_name`='Genetics';
UPDATE `genomics`.`lab_sections` SET `section_name`='Transfer to genetics' WHERE
`section_name`='Genetics';
UPDATE `genomics`.`lab_sections` SET `section_name`='Specimens - blood' WHERE
`section_name`='Specimens';
UPDATE `genomics`.`lab_sections` SET `section_name`='DNA - blood' WHERE
`section_name`='DNA';
/* update existing lab-tests */
UPDATE `genomics`.`lab_tests` SET `test_name`='total_samples',
`field_label`='Total samples in group',
`lab_section_id`='9' 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';
/* inactive existing section 5 lab-tests, insert 2 new ones below: */
UPDATE `genomics`.`lab_tests` SET `is_active`='no' WHERE `lab_section_id` = 5;
/* new lab sections */
INSERT INTO `genomics`.`lab_sections` (`section_name`, `has_test_sign_out`) VALUES
('Approach', 'yes'), /* 9 */
('Specimens - tumour','yes'), /* 10 */
('Specimens - FFPE','yes'), /* 11 */
('Specimens - frozen','yes'), /* 12 */
('Consent withdrawal','yes'), /* 13 */
('DNA - tumour','yes'); /* 14 */
/* set permitted sample type for 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`;
/* new lab-tests - check lab_sections.id: */
INSERT INTO `genomics`.`lab_tests` (`test_name`, `field_label`, `lab_section_id`,
`test_type`, `has_results`) VALUES
('edta10', 'EDTA 10ml', '2', 'test', 'yes'),
('sent_from_histopathology', 'Sent from histopathology', 5, 'test', 'yes'),
('received_by_cytogenetics', 'Received by cytogenetics', 5, 'test', 'yes'),
('omics_sent_ccp', 'Omics sent to CCP', '8', 'test', 'yes'),
('omics_arrived_ccp', 'Omics arrived CCP', '8', 'test', 'yes'),
('omics_consignment_number', 'Omics consignment number', '8', 'test', 'yes'),
('tracking_number', 'Tracking number', '8', 'test', 'yes'),
('approach_date', 'Approach date', '9', 'test', 'yes'),
('approach_method', 'Approach method', '9', 'test', 'yes'),
('first_outcome', 'First outcome', '9', 'test', 'yes'),
('followup_date', 'Follow-up date', '9', 'test', 'yes'),
('second_outcome', 'Second outcome', '9', 'test', 'yes'),
('appointment_date', 'Appointment date', '9', 'test', 'yes'),
('family_id', 'Family ID', '9', 'test', 'yes'),
('tumour_sample_id', 'Sample ID', '10', 'test', 'yes'),
('clinic_sample_type', 'Clinic sample type', '10', 'test', 'yes'),
('tumour_sample_taken', 'Sample taken', '10', 'test', 'yes'),
('tumour_type', 'Tumour type', '10', 'test', 'yes'),
('tissue_source', 'Tissue source', '10', 'test', 'yes'),
('number_of_biopsies', 'Number of biopsies', '10', 'test', 'yes'),
('biopsy_gauge', 'Gauge of biopsies', '10', 'test', 'yes'),
('excision_margin', 'Excision margin', '10', 'test', 'yes'),
('tumour_size', 'Tumour size', '10', 'test', 'yes'),
('dna_extraction_protocol', 'DNA extraction protocol', '10', 'test', 'yes'),
('prolonged_storage_method', 'Prolonged storage method', '10', 'test', 'yes'),
('unsent_sample_reason', 'Unsent sample reason', '10', 'test', 'yes'),
('tumour_sample_type', 'Tumour sample type', '10', 'test', 'yes'),
('number_of_sections', 'Number of sections', '10', 'test', 'yes'),
('section_thickness', 'Section thickness', '10', 'test', 'yes'),
('number_of_blocks', 'Number of blocks', '10', 'test', 'yes'),
('ffpe_macrodissected', 'Macrodissected', '11', 'test', 'yes'),
('ffpe_dissection_details', 'Macrodissection details', '11', 'test', 'yes'),
('fixative_type', 'Fixative type', '11', 'test', 'yes'),
('fixation_start', 'Fixation start', '11', 'test', 'yes'),
('fixation_end', 'Fixation end', '11', 'test', 'yes'),
('processing_schedule', 'Processing schedule', '11', 'test', 'yes'),
('fixation_comments', 'Fixation comments', '11', 'test', 'yes'),
('formalin_duration', 'Time in formalin', '11', 'test', 'yes'),
('pre_invasive_elements', 'Pre-invasive elements', '11', 'test', 'yes'),
('frozen_macrodissected', 'Macrodissected', '12', 'test', 'yes'),
('frozen_dissection_details', 'Macrodissection details', '12', 'test', 'yes'),
('snap_freezing_start', 'Snap freezing start', '12', 'test', 'yes'),
('withdrawal_date', 'Date of withdrawal', '13', 'test', 'yes'),
('withdrawal_form', 'Withdrawal form', '13', 'test', 'yes'),
('withdrawal_option', 'Withdrawal option', '13', 'test', 'yes'),
('tumour_lab_number', 'DNA lab number', '14', 'test', 'yes'),
('tumour_volume_stored', 'Volume stored', '14', 'test', 'yes');
/* convert unused EDTA#2 tests to DNA-tumour section tests */
UPDATE `genomics`.`lab_tests` SET `test_name`='tumour_fluidx',
`field_label`='Tumour Fluidx code', `lab_section_id`='14', `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`='14', `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`='14', `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`='14', `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`='14', `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`='14', `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`='14', `is_active`='yes'
WHERE `test_name`='edta2_fluidx_rack_well';
/* 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;
/* disable fixation - used by inactive Histopathology section test */
UPDATE `genomics`.`lab_test_result_data_types` SET `is_active`='no' WHERE `id`=2;
/* new drop-down select names */
INSERT INTO `genomics`.`lab_test_result_data_types` (description,is_active) VALUES
('approach_response', 'yes'), /* 14 */
('withdrawal_form', 'yes'), /* 15 */
('withdrawal_option', 'yes'), /* 16 */
('approach_method', 'yes'), /* 17 */
('fixative_type', 'yes'), /* 18 */
('processing_schedule', 'yes'), /* 19 */
('clinic_sample_type', 'yes'), /* 20 */
('tumour_sample_type', 'yes'), /* 21 */
('tumour_type', 'yes'), /* 22 */
('tissue_source', 'yes'), /* 23 */
('excision_margin', 'yes'), /* 24 */
('extraction_protocol', 'yes'), /* 25 */
('prolonged_storage', 'yes'), /* 26 */
('unsent_sample_reason', 'yes'); /* 27 */
/* check lab_test_result_data_types.id: */
INSERT INTO `genomics`.`lab_test_result_options` (data_type_id, `value`, is_active) VALUES
(14, 'Interested', 'yes'),
(14, 'Declined', 'yes'),
(14, 'Considering', 'yes'),
(15, 'Adult/Child', 'yes'),
(15, 'Consultee', 'yes'),
(15, 'Additional findings', 'yes'),
(16, 'Partial', 'yes'),
(16, 'Full', 'yes'),
(17, 'Letter', 'yes'),
(17, 'Telephone', 'yes'),
(17, 'Appointment', 'yes'),
(18, 'Formal saline', 'yes'),
(18, 'Neutral buffered formalin', 'yes'),
(18, 'UMFix', 'yes'),
(18, 'Paxgene', 'yes'),
(18, 'Other', 'yes'),
(19, 'Overnight', 'yes'),
(19, 'Urgent', 'yes'),
(19, 'Extended', 'yes'),
(20, 'DNA Fibroblast', 'yes'),
(20, 'DNA FF Germline', 'yes'),
(20, 'DNA FFPE Tumour', 'yes'),
(20, 'DNA FF Tumour', 'yes'),
(20, 'DNA Blood Tumour', 'yes'),
(21, 'Section', 'yes'),
(21, 'Block', 'yes'),
(22, 'Metastatic', 'yes'),
(22, 'Primary', 'yes'),
(22, 'Recurrence', 'yes'),
(23, 'surgical resection', 'yes'),
(23, 'USS-guided biopsy', 'yes'),
(23, 'non-guided biopsy', 'yes'),
(23, 'CT-guided biopsy', 'yes'),
(23, 'endoscopic biopsy', 'yes'),
(23, 'stereotactically guided biopsy', 'yes'),
(23, 'MRI-guided biopsy', 'yes'),
(24, 'Clear: distance not stated', 'yes'),
(24, 'Clear: >5mm from margin', 'yes'),
(24, 'Clear: >1 but <=5mm', 'yes'),
(24, "<=1mm but doesn't reach margin", 'yes'),
(24, 'Tumour reaches excision margin', 'yes'),
(24, 'Uncertain', 'yes'),
(24, 'Margin not involved =>1mm', 'yes'),
(24, 'Margin not involved <1mm', 'yes'),
(24, 'Margin not involved 1-5mm', 'yes'),
(24, 'Not applicable', 'yes'),
(24, 'Not known', 'yes'),
(25, 'Covaris', 'yes'),
(25, 'Qiagen_80', 'yes'),
(25, 'Qiagen_90', 'yes'),
(25, 'Fresh_Frozen', 'yes'),
(26, 'refrigeration', 'yes'),
(26, 'vac_pack', 'yes'),
(26, 'refrigeration_and_vac_pack', 'yes'),
(26, 'room_temp', 'yes'),
(27, 'Tumour sample not taken', 'yes'),
(27, 'Tumour type not eligible', 'yes'),
(27, 'Insufficient sample post-Tx', 'yes'),
(27, 'Poorly cellular tumour', 'yes'),
(27, 'Insufficient DNA', 'yes'),
(27, 'No Cancer Diagnosed', 'yes');
/* run script/genomics_lab_test_result_options_map.pl */
