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`;
UPDATE bristol.request_report_detail SET comment = NULL, updated_at = updated_at
WHERE comment IN ( '-', '.');
/* upload_data_files user functions: */
INSERT INTO bristol.user_functions (function_name, function_detail)
VALUES ('upload_data_files', 'upload and delete data files');
SELECT @function_id := id FROM user_functions WHERE function_name = 'upload_data_files';
INSERT INTO bristol.user_group_function (group_id, function_id) VALUES
SELECT id, @function_id FROM user_groups WHERE group_name IN
('bms', 'cs', 'admin');
INSERT INTO user_permission (user_id, function_id)
SELECT DISTINCT up.user_id, @function_id
FROM user_permission up
JOIN users u on up.user_id = u.id
JOIN user_groups ug on u.group_id = ug.id
WHERE ug.group_name in ('bms', 'cs', 'admin');
UPDATE user_functions SET function_detail = 'email report'
WHERE function_name = 'email_report';
DELETE FROM bristol.user_locations WHERE location_name NOT IN (
"BHODS",
"Blackpool",
"Boston",
"HMDS",
"Musgrove Park Taunton",
"North Bristol",
"Royal Devon and Exeter",
"Royal United Hospital Bath",
"Torbay",
"University Hospital Bristol"
);
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;
INSERT INTO lab_test_result_data_types (description) VALUES
('decimal'),
('integer'),
('date'),
('datetime');
/* 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;
