[hmrn_rebuild_tables]
DROP TABLE IF EXISTS `imaging_options`;
CREATE TABLE `imaging_options` (
`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
`description` VARCHAR(50) NOT NULL DEFAULT '',
`option_type` VARCHAR(50) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `patient_imaging_event`;
CREATE TABLE `patient_imaging_event` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`patient_id` INT(11) NOT NULL DEFAULT '0',
`dataset` SMALLINT(6) NOT NULL DEFAULT '1',
`scan_type` ENUM('ct','pet') DEFAULT NULL,
`stage` VARCHAR(50) NOT NULL default '',
`date` DATE NOT NULL DEFAULT '1901-01-01',
PRIMARY KEY (`id`)
)
ENGINE=InnoDB;
DROP TABLE IF EXISTS `events`;
CREATE TABLE `events` (
`id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
`description` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `patient_event`;
CREATE TABLE `patient_event` (
`patient_id` INT(11) NOT NULL DEFAULT '0',
`event_id` SMALLINT(6) NOT NULL DEFAULT '0',
`date` DATE,
PRIMARY KEY(`patient_id`,`event_id`)
)
ENGINE=InnoDB;
DROP TABLE IF EXISTS `patient_imaging_option`;
CREATE TABLE `patient_imaging_option` (
`imaging_event_id` INT(11) NOT NULL DEFAULT '0',
`imaging_option_id` SMALLINT(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`imaging_event_id`, `imaging_option_id`)
) ENGINE=InnoDB;
[hmrn_calculate_patient_age]
SELECT p.dob, pe.date as 'diagnosis'
FROM patients p
JOIN ( patient_event pe JOIN events e on pe.event_id = e.id
and e.description = 'diagnosis' )
on pe.patient_id = p.id
WHERE patient_id = ?
[hmrn_patient_diagnostic_categories]
SELECT
DISTINCT(t7.description)
FROM
requests t1
JOIN patient_case t2 ON (t1.patient_case_id = t2.id)
JOIN patients t3 ON (t2.patient_id = t3.id)
JOIN request_report t4 ON (t1.id = t4.request_id)
JOIN diagnoses t5 ON (t4.diagnosis_id = t5.id)
JOIN icdo_category t6 ON (t5.icdo3 = t6.icdo3)
JOIN diagnostic_categories t7 ON (t6.diagnostic_category_id = t7.id)
WHERE
t2.patient_id = ?
[hmrn_imaging_options]
SELECT id, option_type
FROM imaging_options