/* post insert BHODS data */
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 ('-', '.');
UPDATE bristol.request_report_detail SET clinical_details = NULL, updated_at = updated_at
WHERE clinical_details 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 bristol.user_functions
WHERE function_name = 'upload_data_files';
INSERT INTO bristol.user_group_function (group_id, function_id)
SELECT id, @function_id FROM bristol.user_groups WHERE group_name IN
('bms', 'cs', 'admin');
INSERT INTO bristol.user_permission (user_id, function_id)
SELECT DISTINCT up.user_id, @function_id
FROM bristol.user_permission up
JOIN bristol.users u on up.user_id = u.id
JOIN bristol.user_groups ug on u.group_id = ug.id
WHERE ug.group_name in ('bms', 'cs', 'admin');
UPDATE bristol.user_functions SET function_detail = 'email report'
WHERE function_name = 'email_report';
UPDATE bristol.users SET user_location_id = 26 /* BHODS */
WHERE user_location_id IN (2,3); /* Blackpool & Boston */
DELETE FROM bristol.user_locations WHERE location_name NOT IN (
'Torbay',
'BHODS', 'HMDS', /* make these central labs in config */
'North Bristol',
'Musgrove Park Taunton',
'Royal Devon and Exeter',
'Royal United Hospital Bath',
'University Hospital Bristol'
);
UPDATE `bristol`.`user_locations` SET `region_code`='RH8'
WHERE location_name = 'Royal Devon and Exeter';
UPDATE `bristol`.`user_locations` SET `region_code`='RBA'
WHERE location_name = 'Musgrove Park Taunton';
UPDATE `bristol`.`user_locations` SET `region_code`='RA7'
WHERE location_name = 'University Hospital Bristol';
UPDATE `bristol`.`user_locations` SET `region_code`='RVJ'
WHERE location_name = 'North Bristol';
UPDATE `bristol`.`user_locations` SET `region_code`='RA9'
WHERE location_name = 'Torbay';
UPDATE `bristol`.`user_locations` SET `region_code`='RD1'
WHERE location_name = 'Royal United Hospital Bath';
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 bristol.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 bristol.specimens;
INSERT INTO bristol.lab_test_result_data_types (description) VALUES
('decimal'),
('integer'),
('date'),
('datetime');
/* switch all lab-test results off & delete all lab-test data-types: */
UPDATE bristol.lab_tests SET has_results = 'no';
DELETE FROM bristol.lab_test_data_type;
/* only want 'recieved' in Additional tests section to take a result: */
UPDATE bristol.lab_tests SET has_results = 'yes' WHERE test_name = 'received';
SELECT @lab_test_id := id FROM bristol.lab_tests WHERE test_name = 'received';
SELECT @data_type_id := id FROM bristol.lab_test_result_data_types
WHERE description = 'date';
INSERT INTO bristol.lab_test_data_type(lab_test_id, data_type_id, is_active)
VALUES(@lab_test_id, @data_type_id, 'yes');
/* don't need this if MO section not taking results:
SELECT @data_type_id := id FROM lab_test_result_data_types
WHERE description = 'molecular text';
INSERT INTO lab_test_data_type(lab_test_id, data_type_id, is_active)
SELECT lt.id, @data_type_id, 'yes'
FROM lab_tests lt
JOIN lab_sections ls on lt.lab_section_id = ls.id
WHERE ls.section_name = 'Molecular oncology'
and has_results = 'yes';
*/
/* 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,'/','_');
*/
DELETE FROM bristol.sessions; /* causes storable incompatibility */
/* 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;
