[hmrn_rebuild_tables]
DROP TABLE IF EXISTS `staging_sites`;
CREATE TABLE `staging_sites` (
`id` int(11) NOT NULL auto_increment,
`description` varchar(255) NOT NULL default '',
`location` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `patient_staging_site`;
CREATE TABLE `patient_staging_site` (
`patient_id` int(11) NOT NULL default '0',
`site_id` int(11) NOT NULL default '0',
PRIMARY KEY (`patient_id`,`site_id`),
KEY `site_id` (`site_id`)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS `patient_chronologies`;
CREATE TABLE `patient_chronologies` (
`patient_id` int(11) NOT NULL default '0',
`diagnosed` date default NULL,
PRIMARY KEY (`patient_id`)
) ENGINE=InnoDB;
[hmrn_calculate_patient_age]
SELECT p.dob, c.diagnosed
FROM patients p
JOIN patient_chronologies c on c.patient_id = p.id
WHERE patient_id = ?
[hmrn_patient_diagnostic_categories_map]
SELECT
DISTINCT(t7.description), 1
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_staging_sites]
SELECT description,location
FROM staging_sites