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');
SELECT @sample_type_id := id FROM sample_types WHERE specimen_type = 'generic'; /* it's the only 1 */
INSERT INTO `bristol`.`lab_section_sample_type` (lab_section_id, sample_type_id)
SELECT t2.id, @sample_type_id
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 t1.id, @sample_type_id FROM bristol.lab_tests t1;
INSERT INTO `bristol`.`specimen_sample_type` (`specimen_id`, `sample_type_id`)
SELECT id, @sample_type_id FROM specimens;
/* specific lab tests: // no longer required, lab_tests src table updated
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: // no longer required, lab_tests src table updated
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,'/','_');
*/
/* remove temp tables: */
SET FOREIGN_KEY_CHECKS = 0;
/* // 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;
DROP TABLE bristol._request_diagnosis_history;
DROP TABLE bristol._request_gross_description;
DROP TABLE bristol._request_report;
SET FOREIGN_KEY_CHECKS = 1;
