SELECT @section_id := id FROM lab_sections WHERE section_name = 'Multiplex Ligation-dependent Probe Amplification';
INSERT INTO lab_tests( test_name, field_label, lab_section_id, test_type, has_results, is_active ) VALUES
(LOWER('2p24_3_MYCN'), '2p24.3 (MYCN)', @section_id, 'test', 'yes', 'yes'),
(LOWER('2p16_1_REL'), '2p16.1 (REL)', @section_id, 'test', 'yes', 'yes'),
(LOWER('6q21_AIM1'), '6q21 (AIM1)', @section_id, 'test', 'yes', 'yes'),
(LOWER('6q21_SEC63'), '6q21 (SEC63)', @section_id, 'test', 'yes', 'yes'),
(LOWER('6q23_3_TNFAIP3'), '6q23.3 (TNFAIP3)', @section_id, 'test', 'yes', 'yes'),
(LOWER('6q25_1_LATS1'), '6q25.1 (LATS1)', @section_id, 'test', 'yes', 'yes'),
(LOWER('6q25_3_IGF2R'), '6q25.3 (IGF2R)', @section_id, 'test', 'yes', 'yes'),
(LOWER('6q26_PARK2'), '6q26 (PARK2)', @section_id, 'test', 'yes', 'yes'),
(LOWER('8p21_3_TNFRSF10B'), '8p21.3 (TNFRSF10B)', @section_id, 'test', 'yes', 'yes'),
(LOWER('8p21_3_TNFRSF10A'), '8p21.3 (TNFRSF10A)', @section_id, 'test', 'yes', 'yes'),
(LOWER('8q24_11_EIF3H'), '8q24.11 (EIF3H)', @section_id, 'test', 'yes', 'yes'),
(LOWER('8q24_21_MYC'), '8q24.21 (MYC)', @section_id, 'test', 'yes', 'yes'),
(LOWER('9p21_3_CDKN2A'), '9p21.3 (CDKN2A)', @section_id, 'test', 'yes', 'yes'),
(LOWER('9p21_3_CDKN2B'), '9p21.3 (CDKN2B)', @section_id, 'test', 'yes', 'yes'),
(LOWER('10q23_31_PTEN'), '10q23.31 (PTEN)', @section_id, 'test', 'yes', 'yes'),
(LOWER('11q22_3_ATM'), '11q22.3 (ATM)', @section_id, 'test', 'yes', 'yes'),
(LOWER('11q22_3_RDX'), '11q22.3 (RDX)', @section_id, 'test', 'yes', 'yes'),
(LOWER('11q23_1_PPP2R1B'), '11q23.1 (PPP2R1B)', @section_id, 'test', 'yes', 'yes'),
(LOWER('11q23_3_CADM1'), '11q23.3 (CADM1)', @section_id, 'test', 'yes', 'yes'),
(LOWER('12p13_32_CCND2'), '12p13.32 (CCND2)', @section_id, 'test', 'yes', 'yes'),
# (LOWER('12p13_31_CD27'), '12p13.31 (CD27)', @section_id, 'test', 'yes', 'yes'), # already exists
(LOWER('12p12_1_LRMP'), '12p12.1 (LRMP)', @section_id, 'test', 'yes', 'yes'),
(LOWER('12p13_3_STAT6'), '12p13.3 (STAT6)', @section_id, 'test', 'yes', 'yes'),
(LOWER('12q14_1_CDK4'), '12q14.1 (CDK4)', @section_id, 'test', 'yes', 'yes'),
(LOWER('12q14_3_HMGA2'), '12q14.3 (HMGA2)', @section_id, 'test', 'yes', 'yes'),
(LOWER('12q15_IFNG'), '12q15 (IFNG)', @section_id, 'test', 'yes', 'yes'),
(LOWER('12q23_2_IGF1'), '12q23.2 (IGF1)', @section_id, 'test', 'yes', 'yes'),
(LOWER('12q23_2_PAH'), '12q23.2 (PAH)', @section_id, 'test', 'yes', 'yes'),
(LOWER('12q24_33_CHFR'), '12q24.33 (CHFR)', @section_id, 'test', 'yes', 'yes'),
# (LOWER('13q14_2_RB1'), '13q14.2 (RB1)', @section_id, 'test', 'yes', 'yes'), # already exists
(LOWER('13q14_2_FNDC3A'), '13q14.2 (FNDC3A)', @section_id, 'test', 'yes', 'yes'),
(LOWER('13q14_2_DLEU2'), '13q14.2 (DLEU2)', @section_id, 'test', 'yes', 'yes'),
(LOWER('13q14_2_KCNRG'), '13q14.2 (KCNRG)', @section_id, 'test', 'yes', 'yes'),
(LOWER('13q14_2_MIR15A'), '13q14.2 (MIR15A)', @section_id, 'test', 'yes', 'yes'),
(LOWER('13q14_2_DLEU1'), '13q14.2 (DLEU1)', @section_id, 'test', 'yes', 'yes'),
(LOWER('13q14_3_DLEU7'), '13q14.3 (DLEU7)', @section_id, 'test', 'yes', 'yes'),
(LOWER('13q14_3_ATP7B'), '13q14.3 (ATP7B)', @section_id, 'test', 'yes', 'yes'),
(LOWER('14q32_33_AKT1'), '14q32.33 (AKT1)', @section_id, 'test', 'yes', 'yes'),
(LOWER('14q32_33_MTA1'), '14q32.33 (MTA1)', @section_id, 'test', 'yes', 'yes'),
(LOWER('14q32_33_KIAA0125'), '14q32.33 (KIAA0125)', @section_id, 'test', 'yes', 'yes'),
# (LOWER('17p13_1_TP53'), '17p13.1 (TP53)', @section_id, 'test', 'yes', 'yes'), # already exists
(LOWER('19p13_2_CDKN2D'), '19p13.2 (CDKN2D)', @section_id, 'test', 'yes', 'yes'),
(LOWER('19p13_2_LDLR'), '19p13.2 (LDLR)', @section_id, 'test', 'yes', 'yes'),
(LOWER('19q12_CCNE1'), '19q12 (CCNE1)', @section_id, 'test', 'yes', 'yes'),
(LOWER('19q13_2_AKT2'), '19q13.2 (AKT2)', @section_id, 'test', 'yes', 'yes'),
(LOWER('19q13_42_MIR498'), '19q13.42 (MIR498)', @section_id, 'test', 'yes', 'yes'),
(LOWER('SF3B1_K700E'), 'SF3B1 K700E', @section_id, 'test', 'yes', 'yes'),
(LOWER('MYD88_L265P'), 'MYD88 L265P', @section_id, 'test', 'yes', 'yes'),
(LOWER('NOTCH1_P2514_fs'), 'NOTCH1 P2514*fs', @section_id, 'test', 'yes', 'yes');
DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp(id INT);
INSERT INTO temp
SELECT id FROM lab_tests WHERE test_name IN (
'2p24_3_mycn',
'2p16_1_rel',
'6q21_aim1',
'6q21_sec63',
'6q23_3_tnfaip3',
'6q25_1_lats1',
'6q25_3_igf2r',
'6q26_park2',
'8p21_3_tnfrsf10b',
'8p21_3_tnfrsf10a',
'8q24_11_eif3h',
'8q24_21_myc',
'9p21_3_cdkn2a',
'9p21_3_cdkn2b',
'10q23_31_pten',
'11q22_3_atm',
'11q22_3_rdx',
'11q23_1_ppp2r1b',
'11q23_3_cadm1',
'12p13_32_ccnd2',
'12p12_1_lrmp',
'12p13_3_stat6',
'12q14_1_cdk4',
'12q14_3_hmga2',
'12q15_ifng',
'12q23_2_igf1',
'12q23_2_pah',
'12q24_33_chfr',
'13q14_2_fndc3a',
'13q14_2_dleu2',
'13q14_2_kcnrg',
'13q14_2_mir15a',
'13q14_2_dleu1',
'13q14_3_dleu7',
'13q14_3_atp7b',
'14q32_33_akt1',
'14q32_33_mta1',
'14q32_33_kiaa0125',
'19p13_2_cdkn2d',
'19p13_2_ldlr',
'19q12_ccne1',
'19q13_2_akt2',
'19q13_42_mir498'
) AND lab_section_id = @section_id;
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)
SELECT id, 1 FROM temp;
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)
SELECT id, 2 FROM temp;
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)
SELECT id, 3 FROM temp;
SELECT @type_id := id FROM lab_test_result_data_types WHERE description = 'mlpa';
INSERT INTO lab_test_data_type(lab_test_id, data_type_id, is_active)
SELECT id, @type_id, 'yes' FROM temp;
# new data type for last 3 lab-tests:
INSERT INTO lab_test_result_data_types(description,is_active) VALUES ('mlpa_alt', 'yes');
SELECT @type_id := id FROM lab_test_result_data_types WHERE description = 'mlpa_alt';
INSERT INTO lab_test_result_options(data_type_id,`value`,is_active) VALUES
(@type_id, 'mutated', 'yes'),
(@type_id, 'normal or wild-type', 'yes'),
(@type_id, 'suspicious', 'yes');
DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp(id INT);
INSERT INTO temp
SELECT id FROM lab_tests WHERE test_name IN (
'sf3b1_k700e',
'myd88_l265p',
'notch1_p2514_fs'
);
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)
SELECT id, 1 FROM temp;
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)
SELECT id, 2 FROM temp;
INSERT INTO lab_test_sample_type(lab_test_id, sample_type_id)
SELECT id, 3 FROM temp;
INSERT INTO lab_test_data_type(lab_test_id, data_type_id, is_active)
SELECT id, @type_id, 'yes' FROM temp;
