/* new mlpa lab-section deployment */
/* new lab-section */
INSERT INTO lab_sections( section_name, has_result_summary, has_section_notes, has_test_sign_out,
has_results_import, has_foreign_id, has_labels, auto_expand )
VALUES( 'Multiplex Ligation-dependent Probe Amplification', 'yes', 'no', 'no', 'no', 'no', 'no', 'no' );
/* get new lab-section id: */
SELECT @mlpa_section_id := id FROM lab_sections WHERE section_name LIKE 'Multiplex Ligation%';
/* set default lab_section_sample_types (all 3): */
INSERT INTO lab_section_sample_type( lab_section_id, sample_type_id )
VALUES
( @mlpa_section_id, 1 ),
( @mlpa_section_id, 2 ),
( @mlpa_section_id, 3 );
/* new data type entry 'mlpa': */
INSERT INTO lab_test_result_data_types( description ) VALUES ( 'mlpa' );
/* get new lab_test_result_data_types id: */
SELECT @data_type_id := id FROM lab_test_result_data_types WHERE description = 'mlpa';
/* new result options for mlpa tests: */
INSERT INTO lab_test_result_options( data_type_id, value )
VALUES
( @data_type_id, 'Gain' ),
( @data_type_id, 'Loss' ),
( @data_type_id, 'Suspicious' );
/* for new Molecular section lab-test 'MLPA quantification': */
SELECT @molecular_section_id := id FROM lab_sections WHERE section_name = 'Molecular';
/* new mlpa section lab-tests: */
INSERT INTO lab_tests
(test_name,field_label,lab_section_id,test_type,has_results)
VALUES
('mlpa_myeloma', 'MLPA myeloma', @mlpa_section_id, 'panel', 'no'),
('1p12_fam46c', '1p12 (FAM46C)', @mlpa_section_id, 'test', 'yes'),
('1p21_1_col11a1', '1p21.1 (COL11A1)', @mlpa_section_id, 'test', 'yes'),
('1p21_3_dpyd', '1p21.3 (DPYD)', @mlpa_section_id, 'test', 'yes'),
('1p31_3_rpe65', '1p31.3 (RPE65)', @mlpa_section_id, 'test', 'yes'),
('1p31_3_lepr_5', '1p31.3 (LEPR-5)', @mlpa_section_id, 'test', 'yes'),
('1p32_1_dab1', '1p32.1 (DAB1)', @mlpa_section_id, 'test', 'yes'),
('1p32_2_ppap2b', '1p32.2 (PPAP2B)', @mlpa_section_id, 'test', 'yes'),
('1p32_3_cdkn2c', '1p32.3 (CDKN2C)', @mlpa_section_id, 'test', 'yes'),
('1p32_3_faf1', '1p32.3 (FAF1)', @mlpa_section_id, 'test', 'yes'),
('1q21_3_cks1b1', '1q21.3 (CKS1B1)', @mlpa_section_id, 'test', 'yes'),
('1q23_3_nuf2', '1q23.3 (NUF2)', @mlpa_section_id, 'test', 'yes'),
('1q23_3_pbx1', '1q23.3 (PBX1)', @mlpa_section_id, 'test', 'yes'),
('1q23_3_rp11_541J2', '1q23.3 (RP11-541J2)', @mlpa_section_id, 'test', 'yes'),
('1q23_3_rp11_480n10', '1q23.3 (RP11-480N10)', @mlpa_section_id, 'test', 'yes'),
('5q31_3_pcdha1', '5q31.3 (PCDHA1)', @mlpa_section_id, 'test', 'yes'),
('5q31_3_pcdhac1', '5q31.3 (PCDHAC1)', @mlpa_section_id, 'test', 'yes'),
('5q31_3_pcdhb2', '5q31.3 (PCDHB2)', @mlpa_section_id, 'test', 'yes'),
('5q31_3_pcdhb10', '5q31.3 (PCDHB10)', @mlpa_section_id, 'test', 'yes'),
('5q31_3_slc25a2', '5q31.3 (SLC25A2)', @mlpa_section_id, 'test', 'yes'),
('5q31_3_pcdhga11', '5q31.3 (PCDHGA11)', @mlpa_section_id, 'test', 'yes'),
('9p24_1_jak2', '9p24.1 (JAK2)', @mlpa_section_id, 'test', 'yes'),
('9q34_3_col5a1', '9q34.3 (COL5A1)', @mlpa_section_id, 'test', 'yes'),
('12p13_31_cd27', '12p13.31 (CD27)', @mlpa_section_id, 'test', 'yes'),
('12p13_31_vamp1', '12p13.31 (VAMP1)', @mlpa_section_id, 'test', 'yes'),
('12p13_31_ncapd2', '12p13.31 (NCAPD2)', @mlpa_section_id, 'test', 'yes'),
('12p13_31_chd4', '12p13.31 (CHD4)', @mlpa_section_id, 'test', 'yes'),
('13q14_2_rb1', '13q14.2 (RB1)', @mlpa_section_id, 'test', 'yes'),
('13q14_2_dleu2_intr_1', '13q14.2 (DLEU2-intr 1)', @mlpa_section_id, 'test', 'yes'),
('13q22_1_dis3', '13q22.1 (DIS3)', @mlpa_section_id, 'test', 'yes'),
('14q32_32_traf3', '14q32.32 (TRAF3)', @mlpa_section_id, 'test', 'yes'),
('15q12_gabrb3', '15q12 (GABRB3)', @mlpa_section_id, 'test', 'yes'),
('15q26_3_igf1r', '15q26.3 (IGF1R)', @mlpa_section_id, 'test', 'yes'),
('16q12_1_cyld', '16q12.1 (CYLD)', @mlpa_section_id, 'test', 'yes'),
('16q23_1_wwox', '16q23.1 (WWOX)', @mlpa_section_id, 'test', 'yes'),
('17p13_1_tp53', '17p13.1 (TP53)', @mlpa_section_id, 'test', 'yes'),
('mlpa_quantification', 'MLPA quantification', @molecular_section_id, 'test', 'no');
/* register all 3 sample types for all mlpa lab-tests: */
INSERT INTO lab_test_sample_type( lab_test_id, sample_type_id )
SELECT id, 1 FROM lab_tests WHERE lab_section_id = @mlpa_section_id;
INSERT INTO lab_test_sample_type( lab_test_id, sample_type_id )
SELECT id, 2 FROM lab_tests WHERE lab_section_id = @mlpa_section_id;
INSERT INTO lab_test_sample_type( lab_test_id, sample_type_id )
SELECT id, 3 FROM lab_tests WHERE lab_section_id = @mlpa_section_id;
/* set data type = 'mlpa' for all mlpa lab-tests: */
INSERT INTO lab_test_data_type( lab_test_id, data_type_id )
SELECT id, @data_type_id FROM lab_tests WHERE lab_section_id = @mlpa_section_id;
/* link 'mlpa myeloma' to Molecular 'mlpa quantification': */
SELECT @mlpa_myeloma_id := id FROM lab_tests WHERE test_name = 'mlpa_myeloma';
SELECT @mlpa_quant_id := id FROM lab_tests WHERE test_name = 'mlpa_quantification';
INSERT INTO linked_lab_test( parent_test_id, linked_test_id )
VALUES (@mlpa_myeloma_id, @mlpa_quant_id);
/* register all 3 sample types for MLPA quantification: */
INSERT INTO lab_test_sample_type( lab_test_id, sample_type_id )
VALUES
(@mlpa_quant_id, 1),
(@mlpa_quant_id, 2),
(@mlpa_quant_id, 3);
/* lab section status options */
SELECT @setup_id := id FROM lab_test_status_options WHERE description = 'setup';
SELECT @pcr_id := id FROM lab_test_status_options WHERE description = 'PCR';
SELECT @analyser_id := id FROM lab_test_status_options WHERE description = 'Analyser';
SELECT @prim_rep_id := id FROM lab_test_status_options WHERE description = 'Primary report';
INSERT INTO lab_section_status_option( lab_section_id, status_option_id, position)
VALUES
(@mlpa_section_id, @setup_id, 1),
(@mlpa_section_id, @pcr_id, 2),
(@mlpa_section_id, @analyser_id, 3),
(@mlpa_section_id, @prim_rep_id, 4);
