INSERT INTO bristol.request_specimen_detail (request_id, gross_description, specimen_quality)
SELECT t1.request_id, t2.detail, t1.specimen_quality
FROM bristol.request_report t1 LEFT JOIN bristol.request_gross_description t2
on t1.request_id = t2.request_id ORDER BY t1.request_id;
INSERT INTO bristol.request_report_detail (request_id, clinical_details, comment,
status, diagnosis_id, created_at, updated_at)
SELECT request_id, clinical_details, comment, status, diagnosis_id, created_at,
updated_at FROM bristol.request_report ORDER BY request_id;
RENAME TABLE bristol.`request_report` TO `_request_report`;
RENAME TABLE bristol.`request_gross_description` TO `_request_gross_description`;
INSERT INTO bristol.`screen_category` (`id`, `name`, `is_active`) VALUES
(1, 'fresh tissue samples', 'yes'),
(2, 'fixed tissue samples', 'yes'),
(3, 'acute leukaemias', 'yes'),
(4, 'chronic myeloid disorders', 'yes'),
(5, 'mature lymphoid disorders', 'yes'),
(6, 'clinical trial samples', 'yes'),
(7, 'miscellaneous', 'yes');
INSERT INTO bristol.screens_new(id,description,category_id,active)
SELECT id,description, IF(sample_type = 'fresh', 1, 2) as category_id, active FROM bristol.screens;
RENAME TABLE bristol.`screens` TO `_screens`;
RENAME TABLE bristol.`screens_new` TO `screens`;
INSERT INTO bristol.request_diagnosis_history_new(id,request_id,diagnosis_id,option_id,user_id,time)
SELECT id,request_id,diagnosis_id, IF(reason = 'error', 1, 2), user_id, time FROM bristol.request_diagnosis_history;
RENAME TABLE bristol.`request_diagnosis_history` TO `_request_diagnosis_history`;
RENAME TABLE bristol.`request_diagnosis_history_new` TO `request_diagnosis_history`;
# configure specimen types for lab sections (just using Generic sample type):
INSERT INTO `bristol`.`sample_types` (`specimen_type`) VALUES ('generic');
INSERT INTO bristol.lab_section_sample_type (lab_section_id, sample_type_id)
SELECT t2.id, 1
FROM bristol.lab_sections t2
WHERE t2.section_name <> 'Additional Information';
INSERT INTO bristol.lab_test_sample_type (lab_test_id, sample_type_id)
SELECT t3.id, t1.sample_type_id
FROM bristol.lab_section_sample_type t1
JOIN bristol.lab_sections t2 on t1.lab_section_id = t2.id
JOIN bristol.lab_tests t3 on t3.lab_section_id = t2.id;
SELECT @sample_type_id := id FROM sample_types WHERE specimen_type = 'generic'; /* it's the only 1 */
INSERT INTO `bristol`.`specimen_sample_type` (`specimen_id`, `sample_type_id`)
SELECT id, @sample_type_id FROM specimens;
SET FOREIGN_KEY_CHECKS = 0;
/* specific lab tests: */
UPDATE bristol.lab_tests SET test_name='mo_additional_testing' WHERE test_name='MO - Additional testing';
UPDATE bristol.lab_tests SET test_name='col1a1_pdgfb', field_label='COL1A1/PDGFB [(t(17;22)]'
WHERE test_name='COL1A1/PDGFB [(t(17;22';
UPDATE bristol.lab_tests SET test_name='igh_fgfr3' WHERE test_name='IGH@/FGFR3[t(4;14)]';
UPDATE bristol.lab_tests SET test_name='igh_maf' WHERE test_name='IGH@/MAF[t(14;16)]';
/* generic lab tests: */
UPDATE bristol.lab_tests SET test_name = LCASE(test_name);
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,' ','_');
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,';','_');
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,'(','');
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,')','');
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,'-','_');
UPDATE bristol.lab_tests SET test_name = REPLACE(test_name,'/','_');
/* temp tables: */
DROP TABLE bristol._request_diagnosis_history;
DROP TABLE bristol._request_gross_description;
DROP TABLE bristol._request_report;
/* // don't need if foreign_key_checks set to 0
ALTER TABLE `request_initial_screen`
DROP FOREIGN KEY `request_initial_screen_ibfk_2`;
ALTER TABLE `request_initial_screen`
ADD CONSTRAINT `request_initial_screen_ibfk_2` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`);
ALTER TABLE `screen_lab_test`
DROP FOREIGN KEY `screen_lab_test_ibfk_1`;
ALTER TABLE `screen_lab_test`
ADD CONSTRAINT `screen_lab_test_ibfk_1` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`);
ALTER TABLE `screen_lab_test_detail`
DROP FOREIGN KEY `screen_lab_test_detail_ibfk_1`;
ALTER TABLE `screen_lab_test_detail`
ADD CONSTRAINT `screen_lab_test_detail_ibfk_1` FOREIGN KEY (`screen_id`) REFERENCES `screens` (`id`);
*/
DROP TABLE bristol._screens;
SET FOREIGN_KEY_CHECKS = 1;
