# MySQL-Front 5.1  (Build 1.48)

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='STRICT_ALL_TABLES' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;


# Host: andlinux    Database: hilis_new
# ------------------------------------------------------
# Server version 5.0.45-Debian_1ubuntu3.4

#
# Source for table case
#

CREATE TABLE `case` (
  `patient_id` int(11) unsigned NOT NULL,
  `location_id` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`patient_id`,`location_id`),
  KEY `location` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table case
#

INSERT INTO `case` VALUES (1,1);
INSERT INTO `case` VALUES (1,2);

#
# Source for table case_unit_number
#

CREATE TABLE `case_unit_number` (
  `case_patient_id` int(11) unsigned NOT NULL,
  `case_location_id` int(11) unsigned NOT NULL,
  `unit_number` varchar(255) NOT NULL,
  PRIMARY KEY  (`case_patient_id`,`case_location_id`,`unit_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table case_unit_number
#

INSERT INTO `case_unit_number` VALUES (1,1,'1011');
INSERT INTO `case_unit_number` VALUES (1,2,'1111');

#
# Source for table clinician_region
#

CREATE TABLE `clinician_region` (
  `region_id` int(11) unsigned NOT NULL default '0',
  `referrer_id` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`region_id`,`referrer_id`),
  KEY `referrer` (`referrer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table clinician_region
#

INSERT INTO `clinician_region` VALUES (1,1);

#
# Source for table general_practice
#

CREATE TABLE `general_practice` (
  `location_id` int(11) unsigned NOT NULL,
  `address` varchar(255) default '',
  `postal_code` varchar(10) NOT NULL,
  PRIMARY KEY  (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table general_practice
#

INSERT INTO `general_practice` VALUES (2,'64, Zoo Lane','AB1 2CD');

#
# Source for table hospital
#

CREATE TABLE `hospital` (
  `location_id` int(11) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `region_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`location_id`),
  KEY `region_id` (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table hospital
#

INSERT INTO `hospital` VALUES (1,'Leeds General Infirmary',1);

#
# Source for table hospital_posting
#

CREATE TABLE `hospital_posting` (
  `referrer_id` int(11) unsigned NOT NULL,
  `hospital_id` int(11) unsigned NOT NULL,
  `speciality_national_code` int(11) unsigned NOT NULL,
  KEY `referrer_posting` (`referrer_id`,`hospital_id`),
  KEY `speciality` (`speciality_national_code`),
  KEY `hospital` (`hospital_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table hospital_posting
#

INSERT INTO `hospital_posting` VALUES (1,1,100);

#
# Source for table location
#

CREATE TABLE `location` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `organisation_code` char(6) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

#
# Dumping data for table location
#

INSERT INTO `location` VALUES (1,'RR801');
INSERT INTO `location` VALUES (2,'B10000');

#
# Source for table patient
#

CREATE TABLE `patient` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `last_name` varchar(25) NOT NULL,
  `first_name` varchar(30) NOT NULL,
  `dob` date default NULL,
  `gender` enum('M','F','U') NOT NULL,
  `nhs_number` varchar(10) default NULL,
  `time` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

#
# Dumping data for table patient
#

INSERT INTO `patient` VALUES (1,'green','alan','1966-02-01','M','1111111111',NULL);

#
# Source for table referrer
#

CREATE TABLE `referrer` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `national_code` varchar(8) default NULL,
  `referrer_type` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

#
# Dumping data for table referrer
#

INSERT INTO `referrer` VALUES (1,'Brown, CC','C0123456','clinician');
INSERT INTO `referrer` VALUES (2,'Green, LL','G1213456','practitioner');

#
# Source for table referrer_posting
#

CREATE TABLE `referrer_posting` (
  `referrer_id` int(11) unsigned NOT NULL default '0',
  `location_id` int(11) unsigned NOT NULL default '0',
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `inactivated_at` datetime default NULL,
  `active` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`referrer_id`,`location_id`),
  KEY `location` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table referrer_posting
#

INSERT INTO `referrer_posting` VALUES (1,1,'2009-06-03 11:08:45',NULL,1);
INSERT INTO `referrer_posting` VALUES (2,2,'2009-06-03 11:38:29',NULL,1);

#
# Source for table region
#

CREATE TABLE `region` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `code` varchar(3) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

#
# Dumping data for table region
#

INSERT INTO `region` VALUES (1,'RR8','Leeds');

#
# Source for table request
#

CREATE TABLE `request` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `created_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `year` int(5) NOT NULL,
  `request_number` int(5) NOT NULL,
  `case_location_id` int(11) unsigned NOT NULL,
  `case_patient_id` int(11) unsigned NOT NULL,
  `case_unit_number_unit_number` varchar(255) NOT NULL,
  `referrer_id` int(11) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `lab_number` (`year`,`request_number`),
  KEY `referrer_posting` (`case_location_id`,`referrer_id`),
  KEY `case_unit_number` (`case_patient_id`,`case_location_id`,`case_unit_number_unit_number`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

#
# Dumping data for table request
#

INSERT INTO `request` VALUES (1,'2009-06-03 11:08:17',2009,1,1,1,'1011',1);
INSERT INTO `request` VALUES (2,'2009-06-03 11:38:54',2009,2,2,1,'1111',2);

#
# Source for table speciality
#

CREATE TABLE `speciality` (
  `national_code` int(11) unsigned NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY  (`national_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table speciality
#

INSERT INTO `speciality` VALUES (100,'General Surgery');
INSERT INTO `speciality` VALUES (600,'General Practice');

#
#  Foreign keys for table case
#

ALTER TABLE `case`
ADD CONSTRAINT `case_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`),
ADD CONSTRAINT `case_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`);

#
#  Foreign keys for table case_unit_number
#

ALTER TABLE `case_unit_number`
ADD CONSTRAINT `case` FOREIGN KEY (`case_patient_id`, `case_location_id`) REFERENCES `case` (`patient_id`, `location_id`);

#
#  Foreign keys for table clinician_region
#

ALTER TABLE `clinician_region`
ADD CONSTRAINT `clinician_region_ibfk_1` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`),
ADD CONSTRAINT `clinician_region_ibfk_2` FOREIGN KEY (`referrer_id`) REFERENCES `referrer` (`id`);

#
#  Foreign keys for table general_practice
#

ALTER TABLE `general_practice`
ADD CONSTRAINT `general_practice_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`);

#
#  Foreign keys for table hospital
#

ALTER TABLE `hospital`
ADD CONSTRAINT `hospital_ibfk_2` FOREIGN KEY (`region_id`) REFERENCES `region` (`id`),
ADD CONSTRAINT `hospital_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`);

#
#  Foreign keys for table hospital_posting
#

ALTER TABLE `hospital_posting`
ADD CONSTRAINT `hospital_posting_ibfk_1` FOREIGN KEY (`referrer_id`, `hospital_id`) REFERENCES `referrer_posting` (`referrer_id`, `location_id`),
ADD CONSTRAINT `hospital_posting_ibfk_2` FOREIGN KEY (`speciality_national_code`) REFERENCES `speciality` (`national_code`),
ADD CONSTRAINT `hospital_posting_ibfk_3` FOREIGN KEY (`hospital_id`) REFERENCES `hospital` (`location_id`);

#
#  Foreign keys for table referrer_posting
#

ALTER TABLE `referrer_posting`
ADD CONSTRAINT `location` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
ADD CONSTRAINT `referrer` FOREIGN KEY (`referrer_id`) REFERENCES `referrer` (`id`);

#
#  Foreign keys for table request
#

ALTER TABLE `request`
ADD CONSTRAINT `request_ibfk_1` FOREIGN KEY (`case_location_id`, `referrer_id`) REFERENCES `referrer_posting` (`location_id`, `referrer_id`),
ADD CONSTRAINT `request_ibfk_2` FOREIGN KEY (`case_patient_id`, `case_location_id`, `case_unit_number_unit_number`) REFERENCES `case_unit_number` (`case_patient_id`, `case_location_id`, `unit_number`);


/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

#explain
select 
req.request_number,
req.year,
p.last_name,
p.first_name,
p.dob,
p.nhs_number,
req.case_unit_number_unit_number as 'unit_number',
ref.name as 'referrer',
#if ( ref.referrer_type = 'practitioner', concat(g.address, ', ', g.postal_code), h.name ) as 'source', 
case when ref.referrer_type = 'practitioner' then concat(g.address, ', ', g.postal_code) else h.name end as 'source', 
#h.name, #g.address,#ref.referrer_type,
l.organisation_code,
s.description as 'department'
from request req
     join patient p on req.case_patient_id = p.id     
     join referrer_posting rp on rp.referrer_id = req.referrer_id     
     join referrer ref on rp.referrer_id = ref.id      
     join location l on rp.location_id = l.id
     left join hospital h on h.location_id = rp.location_id          
     left join general_practice g on g.location_id = rp.location_id   
     left join hospital_posting hp on hp.referrer_id = rp.referrer_id     
     left join speciality s on hp.speciality_national_code = s.national_code