-- MySQL dump 10.13  Distrib 5.5.54, for debian-linux-gnu (i686)
--
-- Host: localhost    Database: anon_tmp
-- ------------------------------------------------------
-- Server version	5.5.54-0+deb7u2

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `additional_options`
--

DROP TABLE IF EXISTS `additional_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `additional_options` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `option_name` varchar(255) DEFAULT NULL,
  `option_label` varchar(255) DEFAULT NULL,
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `option_label` (`option_label`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `additional_options`
--

LOCK TABLES `additional_options` WRITE;
/*!40000 ALTER TABLE `additional_options` DISABLE KEYS */;
INSERT INTO `additional_options` (`id`, `option_name`, `option_label`, `is_active`) VALUES (1,'urgent','Urgent','yes'),(2,'doi','DoI','yes');
/*!40000 ALTER TABLE `additional_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `admin_messages`
--

DROP TABLE IF EXISTS `admin_messages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `admin_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `message` text NOT NULL,
  `valid_until` date DEFAULT NULL,
  `user_group` enum('internal','external','all') NOT NULL DEFAULT 'all',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `admin_messages`
--

LOCK TABLES `admin_messages` WRITE;
/*!40000 ALTER TABLE `admin_messages` DISABLE KEYS */;
/*!40000 ALTER TABLE `admin_messages` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `anatomical_sites`
--

DROP TABLE IF EXISTS `anatomical_sites`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `anatomical_sites` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `site_name` varchar(50) NOT NULL,
  `snomed` varchar(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `site_name` (`site_name`),
  KEY `snomed_code` (`snomed`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `anatomical_sites`
--

LOCK TABLES `anatomical_sites` WRITE;
/*!40000 ALTER TABLE `anatomical_sites` DISABLE KEYS */;
INSERT INTO `anatomical_sites` (`id`, `site_name`, `snomed`) VALUES (1,'lymph node','C77.9'),(2,'peripheral blood','C42.0');
/*!40000 ALTER TABLE `anatomical_sites` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `audit_request_categories`
--

DROP TABLE IF EXISTS `audit_request_categories`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `audit_request_categories` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL DEFAULT '',
  `active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `audit_request_categories`
--

LOCK TABLES `audit_request_categories` WRITE;
/*!40000 ALTER TABLE `audit_request_categories` DISABLE KEYS */;
INSERT INTO `audit_request_categories` (`id`, `description`, `active`) VALUES (1,'cytopenia','yes');
/*!40000 ALTER TABLE `audit_request_categories` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `audit_request_options`
--

DROP TABLE IF EXISTS `audit_request_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `audit_request_options` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL DEFAULT '',
  `category_id` smallint(6) NOT NULL DEFAULT '0',
  `active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `description` (`description`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `audit_request_options`
--

LOCK TABLES `audit_request_options` WRITE;
/*!40000 ALTER TABLE `audit_request_options` DISABLE KEYS */;
INSERT INTO `audit_request_options` (`id`, `description`, `category_id`, `active`) VALUES (1,'Anaemia',1,'yes');
/*!40000 ALTER TABLE `audit_request_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `authorised_reports_view`
--

DROP TABLE IF EXISTS `authorised_reports_view`;
/*!50001 DROP VIEW IF EXISTS `authorised_reports_view`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `authorised_reports_view` (
  `id` tinyint NOT NULL,
  `request_number` tinyint NOT NULL,
  `year` tinyint NOT NULL,
  `reg_date` tinyint NOT NULL,
  `last_name` tinyint NOT NULL,
  `first_name` tinyint NOT NULL,
  `dob` tinyint NOT NULL,
  `unit_number` tinyint NOT NULL,
  `age` tinyint NOT NULL,
  `nhs_number` tinyint NOT NULL,
  `location` tinyint NOT NULL,
  `organisation_code` tinyint NOT NULL,
  `parent_code` tinyint NOT NULL,
  `diagnosis` tinyint NOT NULL,
  `icdo3` tinyint NOT NULL,
  `status` tinyint NOT NULL,
  `auth_date` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `clinical_trials`
--

DROP TABLE IF EXISTS `clinical_trials`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `clinical_trials` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `trial_name` varchar(50) DEFAULT NULL,
  `active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `trial_name` (`trial_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `clinical_trials`
--

LOCK TABLES `clinical_trials` WRITE;
/*!40000 ALTER TABLE `clinical_trials` DISABLE KEYS */;
INSERT INTO `clinical_trials` (`id`, `trial_name`, `active`) VALUES (1,'NCRI: Myeloma X','yes'),(2,'HMDS outreach','yes');
/*!40000 ALTER TABLE `clinical_trials` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `consent_options`
--

DROP TABLE IF EXISTS `consent_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `consent_options` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `consent_name` varchar(255) NOT NULL DEFAULT '',
  `consent_label` varchar(255) NOT NULL DEFAULT '',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `consent_name` (`consent_name`),
  UNIQUE KEY `consent_label` (`consent_label`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `consent_options`
--

LOCK TABLES `consent_options` WRITE;
/*!40000 ALTER TABLE `consent_options` DISABLE KEYS */;
INSERT INTO `consent_options` (`id`, `consent_name`, `consent_label`, `is_active`) VALUES (1,'treatment','Treatment','yes'),(2,'research','Research','yes');
/*!40000 ALTER TABLE `consent_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `context_warning_diagnosis`
--

DROP TABLE IF EXISTS `context_warning_diagnosis`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `context_warning_diagnosis` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `diagnosis_id` smallint(6) NOT NULL DEFAULT '0',
  `context_warning_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `diagnosis_id` (`diagnosis_id`),
  KEY `context_warning_id` (`context_warning_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `context_warning_diagnosis`
--

LOCK TABLES `context_warning_diagnosis` WRITE;
/*!40000 ALTER TABLE `context_warning_diagnosis` DISABLE KEYS */;
/*!40000 ALTER TABLE `context_warning_diagnosis` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `deleted_requests`
--

DROP TABLE IF EXISTS `deleted_requests`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `deleted_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `request_number` smallint(6) NOT NULL DEFAULT '0',
  `year` year(4) NOT NULL DEFAULT '0000',
  `action` text,
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `deleted_requests`
--

LOCK TABLES `deleted_requests` WRITE;
/*!40000 ALTER TABLE `deleted_requests` DISABLE KEYS */;
/*!40000 ALTER TABLE `deleted_requests` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `diagnoses`
--

DROP TABLE IF EXISTS `diagnoses`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `diagnoses` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `icdo3` varchar(6) DEFAULT NULL,
  `diagnostic_category_id` smallint(6) DEFAULT NULL,
  `active` enum('yes','no') DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `icdo3` (`icdo3`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `diagnoses`
--

LOCK TABLES `diagnoses` WRITE;
/*!40000 ALTER TABLE `diagnoses` DISABLE KEYS */;
INSERT INTO `diagnoses` (`id`, `name`, `icdo3`, `diagnostic_category_id`, `active`) VALUES (1,'RAEB','9983/3',1,'yes'),(2,'CLL','9823/3',2,'yes');
/*!40000 ALTER TABLE `diagnoses` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `diagnosis_change_options`
--

DROP TABLE IF EXISTS `diagnosis_change_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `diagnosis_change_options` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `option_name` varchar(25) NOT NULL,
  `description` varchar(255) NOT NULL,
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `diagnosis_change_options`
--

LOCK TABLES `diagnosis_change_options` WRITE;
/*!40000 ALTER TABLE `diagnosis_change_options` DISABLE KEYS */;
INSERT INTO `diagnosis_change_options` (`id`, `option_name`, `description`, `is_active`) VALUES (1,'error','error','yes'),(2,'update','update','yes');
/*!40000 ALTER TABLE `diagnosis_change_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `diagnosis_context_warnings`
--

DROP TABLE IF EXISTS `diagnosis_context_warnings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `diagnosis_context_warnings` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL DEFAULT '',
  `is_active` enum('yes','no') DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `diagnosis_context_warnings`
--

LOCK TABLES `diagnosis_context_warnings` WRITE;
/*!40000 ALTER TABLE `diagnosis_context_warnings` DISABLE KEYS */;
/*!40000 ALTER TABLE `diagnosis_context_warnings` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `diagnosis_lab_test`
--

DROP TABLE IF EXISTS `diagnosis_lab_test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `diagnosis_lab_test` (
  `diagnosis_id` smallint(6) NOT NULL DEFAULT '0',
  `lab_test_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`diagnosis_id`,`lab_test_id`),
  KEY `diagnosis_id` (`diagnosis_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `diagnosis_lab_test`
--

LOCK TABLES `diagnosis_lab_test` WRITE;
/*!40000 ALTER TABLE `diagnosis_lab_test` DISABLE KEYS */;
/*!40000 ALTER TABLE `diagnosis_lab_test` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `diagnostic_categories`
--

DROP TABLE IF EXISTS `diagnostic_categories`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `diagnostic_categories` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  `category_type` enum('main','sub') DEFAULT NULL,
  `active` enum('yes','no') DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `diagnostic_categories`
--

LOCK TABLES `diagnostic_categories` WRITE;
/*!40000 ALTER TABLE `diagnostic_categories` DISABLE KEYS */;
INSERT INTO `diagnostic_categories` (`id`, `description`, `category_type`, `active`) VALUES (1,'Myelodysplastic','main','yes'),(2,'Lymphoproliferative','main','yes'),(3,'Non-diagnostic','main','yes');
/*!40000 ALTER TABLE `diagnostic_categories` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `draft_report_users`
--

DROP TABLE IF EXISTS `draft_report_users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `draft_report_users` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL DEFAULT 'username',
  `first_name` varchar(50) NOT NULL DEFAULT 'first_name',
  `last_name` varchar(50) NOT NULL DEFAULT 'last_name',
  `password` varchar(32) NOT NULL DEFAULT 'password',
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `login_id` (`first_name`,`last_name`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `draft_report_users`
--

LOCK TABLES `draft_report_users` WRITE;
/*!40000 ALTER TABLE `draft_report_users` DISABLE KEYS */;
/*!40000 ALTER TABLE `draft_report_users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `email_addresses`
--

DROP TABLE IF EXISTS `email_addresses`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `email_addresses` (
  `address` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `email_addresses`
--

LOCK TABLES `email_addresses` WRITE;
/*!40000 ALTER TABLE `email_addresses` DISABLE KEYS */;
INSERT INTO `email_addresses` (`address`) VALUES ('user@anywhere.net');
/*!40000 ALTER TABLE `email_addresses` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `email_contacts`
--

DROP TABLE IF EXISTS `email_contacts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `email_contacts` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `display_name` varchar(255) NOT NULL DEFAULT '',
  `referral_source_id` smallint(6) NOT NULL DEFAULT '0',
  `type` enum('mdt','report') DEFAULT NULL,
  `scope` enum('hospital','organisation','department') DEFAULT NULL,
  `department_id` smallint(6) DEFAULT NULL,
  `contact_address` varchar(255) NOT NULL DEFAULT '',
  `status` enum('all','new') NOT NULL DEFAULT 'all',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `src_type_department_address` (`referral_source_id`,`type`,`department_id`,`contact_address`),
  KEY `contact_address` (`contact_address`),
  KEY `referral_source_id` (`referral_source_id`),
  KEY `department_id` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `email_contacts`
--

LOCK TABLES `email_contacts` WRITE;
/*!40000 ALTER TABLE `email_contacts` DISABLE KEYS */;
/*!40000 ALTER TABLE `email_contacts` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `error_code_assignment`
--

DROP TABLE IF EXISTS `error_code_assignment`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `error_code_assignment` (
  `type` enum('patient','new_request','request_edit','errors') DEFAULT NULL,
  `error_code_id` smallint(6) NOT NULL DEFAULT '0',
  UNIQUE KEY `error_code_type` (`type`,`error_code_id`),
  KEY `error_code_id` (`error_code_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `error_code_assignment`
--

LOCK TABLES `error_code_assignment` WRITE;
/*!40000 ALTER TABLE `error_code_assignment` DISABLE KEYS */;
INSERT INTO `error_code_assignment` (`type`, `error_code_id`) VALUES ('patient',1),('new_request',1),('errors',2),('errors',3);
/*!40000 ALTER TABLE `error_code_assignment` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `error_codes`
--

DROP TABLE IF EXISTS `error_codes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `error_codes` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `code` varchar(2) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_unique` enum('yes','no') NOT NULL DEFAULT 'yes',
  `active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `error_codes`
--

LOCK TABLES `error_codes` WRITE;
/*!40000 ALTER TABLE `error_codes` DISABLE KEYS */;
INSERT INTO `error_codes` (`id`, `code`, `description`, `is_unique`, `active`) VALUES (1,'a','first_error','yes','yes'),(2,'b','second_error','no','yes'),(3,'c','third_error','yes','yes');
/*!40000 ALTER TABLE `error_codes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `events`
--

DROP TABLE IF EXISTS `events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `events` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `events`
--

LOCK TABLES `events` WRITE;
/*!40000 ALTER TABLE `events` DISABLE KEYS */;
INSERT INTO `events` (`id`, `description`) VALUES (1,'diagnosis');
/*!40000 ALTER TABLE `events` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `family_id_view`
--

DROP TABLE IF EXISTS `family_id_view`;
/*!50001 DROP VIEW IF EXISTS `family_id_view`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `family_id_view` (
  `request_id` tinyint NOT NULL,
  `family_id` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `hospital_departments`
--

DROP TABLE IF EXISTS `hospital_departments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `hospital_departments` (
  `id` smallint(6) NOT NULL DEFAULT '0',
  `display_name` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `display_name` (`display_name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `hospital_departments`
--

LOCK TABLES `hospital_departments` WRITE;
/*!40000 ALTER TABLE `hospital_departments` DISABLE KEYS */;
INSERT INTO `hospital_departments` (`id`, `display_name`) VALUES (100,'General Surgery'),(600,'General Medical Practice'),(823,'Haematology'),(999,'Unknown/other');
/*!40000 ALTER TABLE `hospital_departments` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `icdo_category`
--

DROP TABLE IF EXISTS `icdo_category`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `icdo_category` (
  `diagnostic_category_id` smallint(6) NOT NULL,
  `icdo3` varchar(6) NOT NULL,
  PRIMARY KEY (`diagnostic_category_id`,`icdo3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `icdo_category`
--

LOCK TABLES `icdo_category` WRITE;
/*!40000 ALTER TABLE `icdo_category` DISABLE KEYS */;
/*!40000 ALTER TABLE `icdo_category` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `imaging_options`
--

DROP TABLE IF EXISTS `imaging_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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 AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `imaging_options`
--

LOCK TABLES `imaging_options` WRITE;
/*!40000 ALTER TABLE `imaging_options` DISABLE KEYS */;
INSERT INTO `imaging_options` (`id`, `description`, `option_type`) VALUES (1,'mesenteric','lower_nodal'),(2,'spleen','lower_nodal'),(3,'mediastinal','upper_nodal'),(4,'waldeyer','upper_nodal'),(5,'thymus','upper_nodal'),(6,'cns','extranodal'),(7,'git','extranodal'),(8,'marrow','extranodal'),(9,'bulky','flag');
/*!40000 ALTER TABLE `imaging_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lab_section_sample_type`
--

DROP TABLE IF EXISTS `lab_section_sample_type`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lab_section_sample_type` (
  `lab_section_id` smallint(6) NOT NULL DEFAULT '0',
  `sample_type_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sample_type_id`,`lab_section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lab_section_sample_type`
--

LOCK TABLES `lab_section_sample_type` WRITE;
/*!40000 ALTER TABLE `lab_section_sample_type` DISABLE KEYS */;
INSERT INTO `lab_section_sample_type` (`lab_section_id`, `sample_type_id`) VALUES (1,1),(3,1),(2,2),(3,2);
/*!40000 ALTER TABLE `lab_section_sample_type` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lab_section_status_option`
--

DROP TABLE IF EXISTS `lab_section_status_option`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lab_section_status_option` (
  `lab_section_id` smallint(6) NOT NULL DEFAULT '0',
  `status_option_id` smallint(6) NOT NULL DEFAULT '0',
  `position` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`lab_section_id`,`status_option_id`),
  UNIQUE KEY `section_position` (`lab_section_id`,`position`),
  KEY `status_option_id` (`status_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lab_section_status_option`
--

LOCK TABLES `lab_section_status_option` WRITE;
/*!40000 ALTER TABLE `lab_section_status_option` DISABLE KEYS */;
INSERT INTO `lab_section_status_option` (`lab_section_id`, `status_option_id`, `position`) VALUES (1,4,NULL);
/*!40000 ALTER TABLE `lab_section_status_option` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lab_sections`
--

DROP TABLE IF EXISTS `lab_sections`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lab_sections` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `section_name` varchar(255) NOT NULL,
  `has_result_summary` enum('yes','no') NOT NULL DEFAULT 'no',
  `has_section_notes` enum('yes','no') NOT NULL DEFAULT 'no',
  `has_test_sign_out` enum('yes','no') NOT NULL DEFAULT 'no',
  `has_foreign_id` enum('yes','no') NOT NULL DEFAULT 'no',
  `has_results_import` enum('yes','no') NOT NULL DEFAULT 'no',
  `has_labels` enum('yes','no') NOT NULL DEFAULT 'no',
  `auto_expand` enum('yes','no') NOT NULL DEFAULT 'no',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `section_name` (`section_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lab_sections`
--

LOCK TABLES `lab_sections` WRITE;
/*!40000 ALTER TABLE `lab_sections` DISABLE KEYS */;
INSERT INTO `lab_sections` (`id`, `section_name`, `has_result_summary`, `has_section_notes`, `has_test_sign_out`, `has_foreign_id`, `has_results_import`, `has_labels`, `auto_expand`, `is_active`) VALUES (1,'Flow','yes','yes','no','yes','no','no','no','yes'),(2,'Histology','yes','yes','yes','no','no','no','no','yes'),(3,'Molecular','yes','no','yes','no','yes','no','yes','yes');
/*!40000 ALTER TABLE `lab_sections` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lab_test_data_type`
--

DROP TABLE IF EXISTS `lab_test_data_type`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lab_test_data_type` (
  `lab_test_id` smallint(6) NOT NULL DEFAULT '0',
  `data_type_id` smallint(6) NOT NULL DEFAULT '0',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`lab_test_id`),
  KEY `data_type_id` (`data_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lab_test_data_type`
--

LOCK TABLES `lab_test_data_type` WRITE;
/*!40000 ALTER TABLE `lab_test_data_type` DISABLE KEYS */;
INSERT INTO `lab_test_data_type` (`lab_test_id`, `data_type_id`, `is_active`) VALUES (6,2,'yes'),(7,2,'yes'),(8,1,'yes');
/*!40000 ALTER TABLE `lab_test_data_type` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lab_test_result_data_types`
--

DROP TABLE IF EXISTS `lab_test_result_data_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lab_test_result_data_types` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(25) NOT NULL DEFAULT '',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lab_test_result_data_types`
--

LOCK TABLES `lab_test_result_data_types` WRITE;
/*!40000 ALTER TABLE `lab_test_result_data_types` DISABLE KEYS */;
INSERT INTO `lab_test_result_data_types` (`id`, `description`, `is_active`) VALUES (1,'numeric','yes'),(2,'subjective','yes'),(3,'text','yes');
/*!40000 ALTER TABLE `lab_test_result_data_types` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lab_test_result_options`
--

DROP TABLE IF EXISTS `lab_test_result_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lab_test_result_options` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `data_type_id` smallint(6) NOT NULL DEFAULT '0',
  `value` varchar(30) NOT NULL DEFAULT '',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `data_type_value` (`data_type_id`,`value`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lab_test_result_options`
--

LOCK TABLES `lab_test_result_options` WRITE;
/*!40000 ALTER TABLE `lab_test_result_options` DISABLE KEYS */;
INSERT INTO `lab_test_result_options` (`id`, `data_type_id`, `value`, `is_active`) VALUES (1,1,'0','yes'),(2,1,'10','yes'),(3,1,'50','yes'),(4,2,'+','yes'),(5,2,'-','yes'),(6,2,'+/-','yes');
/*!40000 ALTER TABLE `lab_test_result_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lab_test_sample_type`
--

DROP TABLE IF EXISTS `lab_test_sample_type`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lab_test_sample_type` (
  `lab_test_id` smallint(6) NOT NULL DEFAULT '0',
  `sample_type_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sample_type_id`,`lab_test_id`),
  KEY `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lab_test_sample_type`
--

LOCK TABLES `lab_test_sample_type` WRITE;
/*!40000 ALTER TABLE `lab_test_sample_type` DISABLE KEYS */;
INSERT INTO `lab_test_sample_type` (`lab_test_id`, `sample_type_id`) VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(5,2),(6,2),(7,2),(8,2),(9,2);
/*!40000 ALTER TABLE `lab_test_sample_type` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lab_test_status_options`
--

DROP TABLE IF EXISTS `lab_test_status_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lab_test_status_options` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL DEFAULT '',
  `is_editable` enum('yes','no') NOT NULL DEFAULT 'yes',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lab_test_status_options`
--

LOCK TABLES `lab_test_status_options` WRITE;
/*!40000 ALTER TABLE `lab_test_status_options` DISABLE KEYS */;
INSERT INTO `lab_test_status_options` (`id`, `description`, `is_editable`, `is_active`) VALUES (1,'new','yes','yes'),(2,'complete','yes','yes'),(3,'delete','yes','yes'),(4,'setup','yes','yes');
/*!40000 ALTER TABLE `lab_test_status_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `lab_tests`
--

DROP TABLE IF EXISTS `lab_tests`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lab_tests` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `test_name` varchar(25) NOT NULL,
  `field_label` varchar(25) NOT NULL,
  `lab_section_id` smallint(6) NOT NULL DEFAULT '0',
  `test_type` enum('test','panel') DEFAULT NULL,
  `has_results` enum('yes','no') DEFAULT NULL,
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `label_section_type` (`lab_section_id`,`test_type`,`field_label`),
  UNIQUE KEY `name_section_type` (`lab_section_id`,`test_type`,`test_name`),
  KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lab_tests`
--

LOCK TABLES `lab_tests` WRITE;
/*!40000 ALTER TABLE `lab_tests` DISABLE KEYS */;
INSERT INTO `lab_tests` (`id`, `test_name`, `field_label`, `lab_section_id`, `test_type`, `has_results`, `is_active`) VALUES (1,'pnh','PNH',1,'panel','no','yes'),(2,'aml','AML',1,'panel','no','yes'),(3,'cml','CML',1,'panel','no','yes'),(4,'hiv','HIV',1,'panel','no','yes'),(5,'apml','APML',3,'panel','no','yes'),(6,'cd20','CD20',2,'test','yes','yes'),(7,'p53','p53',2,'test','yes','yes'),(8,'mib1','Mib1',2,'test','yes','yes'),(9,'auto','AUTO',2,'test','yes','yes');
/*!40000 ALTER TABLE `lab_tests` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `linked_lab_test`
--

DROP TABLE IF EXISTS `linked_lab_test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `linked_lab_test` (
  `parent_test_id` smallint(6) NOT NULL DEFAULT '0',
  `linked_test_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`parent_test_id`,`linked_test_id`),
  KEY `linked_lab_test` (`linked_test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `linked_lab_test`
--

LOCK TABLES `linked_lab_test` WRITE;
/*!40000 ALTER TABLE `linked_lab_test` DISABLE KEYS */;
/*!40000 ALTER TABLE `linked_lab_test` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `local_network_locations`
--

DROP TABLE IF EXISTS `local_network_locations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `local_network_locations` (
  `parent_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `local_network_locations`
--

LOCK TABLES `local_network_locations` WRITE;
/*!40000 ALTER TABLE `local_network_locations` DISABLE KEYS */;
INSERT INTO `local_network_locations` (`parent_id`) VALUES (1);
/*!40000 ALTER TABLE `local_network_locations` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `logins`
--

DROP TABLE IF EXISTS `logins`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `logins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `address` varchar(15) DEFAULT NULL,
  `browser` varchar(255) DEFAULT NULL,
  `session_id` char(32) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `logins`
--

LOCK TABLES `logins` WRITE;
/*!40000 ALTER TABLE `logins` DISABLE KEYS */;
/*!40000 ALTER TABLE `logins` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `new_national_code`
--

DROP TABLE IF EXISTS `new_national_code`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `new_national_code` (
  `national_code` varchar(8) NOT NULL,
  `type` enum('referrer','location') NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `national_code` (`national_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `new_national_code`
--

LOCK TABLES `new_national_code` WRITE;
/*!40000 ALTER TABLE `new_national_code` DISABLE KEYS */;
/*!40000 ALTER TABLE `new_national_code` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `panel_lab_test`
--

DROP TABLE IF EXISTS `panel_lab_test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `panel_lab_test` (
  `panel_test_id` smallint(6) NOT NULL DEFAULT '0',
  `lab_test_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`panel_test_id`,`lab_test_id`),
  KEY `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `panel_lab_test`
--

LOCK TABLES `panel_lab_test` WRITE;
/*!40000 ALTER TABLE `panel_lab_test` DISABLE KEYS */;
/*!40000 ALTER TABLE `panel_lab_test` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `parent_organisations`
--

DROP TABLE IF EXISTS `parent_organisations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `parent_organisations` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `parent_code` varchar(6) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL DEFAULT '',
  `referral_type_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `description` (`description`),
  UNIQUE KEY `parent_code` (`parent_code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `parent_organisations`
--

LOCK TABLES `parent_organisations` WRITE;
/*!40000 ALTER TABLE `parent_organisations` DISABLE KEYS */;
INSERT INTO `parent_organisations` (`id`, `parent_code`, `description`, `referral_type_id`) VALUES (1,'ABC','NEWTOWN NHS TRUST',3),(2,'B12345','OLD SURGERY, NT1 1TN',4),(3,'X99999','UNKNOWN HOSPITAL',3),(4,'V81999','UNKNOWN PRACTICE',4);
/*!40000 ALTER TABLE `parent_organisations` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `patient_case`
--

DROP TABLE IF EXISTS `patient_case`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `patient_case` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patient_id` int(11) NOT NULL DEFAULT '0',
  `referral_source_id` smallint(6) NOT NULL DEFAULT '0',
  `unit_number` varchar(255) NOT NULL DEFAULT 'UNKNOWN',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `case` (`patient_id`,`unit_number`,`referral_source_id`),
  KEY `patient_id` (`patient_id`),
  KEY `referral_source_id` (`referral_source_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `patient_case`
--

LOCK TABLES `patient_case` WRITE;
/*!40000 ALTER TABLE `patient_case` DISABLE KEYS */;
INSERT INTO `patient_case` (`id`, `patient_id`, `referral_source_id`, `unit_number`, `time`) VALUES (1,1,2,'5128','2017-05-23 20:28:45'),(2,2,1,'9345','2017-05-23 20:28:45');
/*!40000 ALTER TABLE `patient_case` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `patient_demographic_history`
--

DROP TABLE IF EXISTS `patient_demographic_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `patient_demographic_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patient_id` int(11) NOT NULL DEFAULT '0',
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `action` varchar(255) NOT NULL DEFAULT '',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `patient_demographic_history`
--

LOCK TABLES `patient_demographic_history` WRITE;
/*!40000 ALTER TABLE `patient_demographic_history` DISABLE KEYS */;
/*!40000 ALTER TABLE `patient_demographic_history` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `patient_demographics`
--

DROP TABLE IF EXISTS `patient_demographics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `patient_demographics` (
  `patient_id` int(11) NOT NULL DEFAULT '0',
  `address` varchar(255) DEFAULT NULL,
  `post_code` varchar(8) DEFAULT NULL,
  `contact_number` varchar(15) DEFAULT NULL,
  `gp_id` int(11) NOT NULL DEFAULT '0',
  `practice_id` smallint(6) NOT NULL DEFAULT '0',
  `status` enum('alive','dead') NOT NULL DEFAULT 'alive',
  `dod` date DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`patient_id`),
  KEY `practice_id` (`practice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `patient_demographics`
--

LOCK TABLES `patient_demographics` WRITE;
/*!40000 ALTER TABLE `patient_demographics` DISABLE KEYS */;
/*!40000 ALTER TABLE `patient_demographics` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `patient_event`
--

DROP TABLE IF EXISTS `patient_event`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `patient_event` (
  `patient_id` int(11) NOT NULL DEFAULT '0',
  `event_id` smallint(6) NOT NULL DEFAULT '0',
  `date` date DEFAULT NULL,
  PRIMARY KEY (`patient_id`,`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `patient_event`
--

LOCK TABLES `patient_event` WRITE;
/*!40000 ALTER TABLE `patient_event` DISABLE KEYS */;
INSERT INTO `patient_event` (`patient_id`, `event_id`, `date`) VALUES (1,1,'2017-05-23');
/*!40000 ALTER TABLE `patient_event` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `patient_imaging_event`
--

DROP TABLE IF EXISTS `patient_imaging_event`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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 AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `patient_imaging_event`
--

LOCK TABLES `patient_imaging_event` WRITE;
/*!40000 ALTER TABLE `patient_imaging_event` DISABLE KEYS */;
INSERT INTO `patient_imaging_event` (`id`, `patient_id`, `dataset`, `scan_type`, `stage`, `date`) VALUES (1,1,1,'ct','initial','2017-05-23');
/*!40000 ALTER TABLE `patient_imaging_event` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `patient_imaging_option`
--

DROP TABLE IF EXISTS `patient_imaging_option`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `patient_imaging_option`
--

LOCK TABLES `patient_imaging_option` WRITE;
/*!40000 ALTER TABLE `patient_imaging_option` DISABLE KEYS */;
INSERT INTO `patient_imaging_option` (`imaging_event_id`, `imaging_option_id`) VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9);
/*!40000 ALTER TABLE `patient_imaging_option` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `patient_trial`
--

DROP TABLE IF EXISTS `patient_trial`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `patient_trial` (
  `patient_id` int(11) NOT NULL DEFAULT '0',
  `trial_id` smallint(6) NOT NULL DEFAULT '0',
  `trial_number` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`patient_id`,`trial_id`),
  KEY `trial_id` (`trial_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `patient_trial`
--

LOCK TABLES `patient_trial` WRITE;
/*!40000 ALTER TABLE `patient_trial` DISABLE KEYS */;
/*!40000 ALTER TABLE `patient_trial` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `patients`
--

DROP TABLE IF EXISTS `patients`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `patients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(50) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `middle_name` varchar(50) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` enum('M','F','U') DEFAULT 'U',
  `nhs_number` varchar(10) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nhs_number` (`nhs_number`),
  KEY `name` (`last_name`,`first_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `patients`
--

LOCK TABLES `patients` WRITE;
/*!40000 ALTER TABLE `patients` DISABLE KEYS */;
INSERT INTO `patients` (`id`, `last_name`, `first_name`, `middle_name`, `dob`, `gender`, `nhs_number`, `created_at`, `updated_at`) VALUES (1,'richards','donald',NULL,'2000-02-27','M','4805751746','2017-05-23 19:28:44','2017-05-23 20:28:45'),(2,'brown','john',NULL,'1979-04-22','M','7378407380','2017-05-23 19:28:44','2017-05-23 20:28:45');
/*!40000 ALTER TABLE `patients` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `phone_log_options`
--

DROP TABLE IF EXISTS `phone_log_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `phone_log_options` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL DEFAULT '',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `option` (`description`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `phone_log_options`
--

LOCK TABLES `phone_log_options` WRITE;
/*!40000 ALTER TABLE `phone_log_options` DISABLE KEYS */;
INSERT INTO `phone_log_options` (`id`, `description`, `is_active`) VALUES (1,'final diagnosis given','yes'),(2,'request report copy','yes');
/*!40000 ALTER TABLE `phone_log_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pre_registration_lab_test`
--

DROP TABLE IF EXISTS `pre_registration_lab_test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pre_registration_lab_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reg_specimen_id` int(11) NOT NULL DEFAULT '0',
  `lab_test_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `reg_specimen_id` (`reg_specimen_id`,`lab_test_id`),
  KEY `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pre_registration_lab_test`
--

LOCK TABLES `pre_registration_lab_test` WRITE;
/*!40000 ALTER TABLE `pre_registration_lab_test` DISABLE KEYS */;
/*!40000 ALTER TABLE `pre_registration_lab_test` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pre_registration_specimen`
--

DROP TABLE IF EXISTS `pre_registration_specimen`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pre_registration_specimen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pre_reg_id` int(11) NOT NULL DEFAULT '0',
  `specimen_code` char(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `pre_reg_specimen` (`pre_reg_id`,`specimen_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pre_registration_specimen`
--

LOCK TABLES `pre_registration_specimen` WRITE;
/*!40000 ALTER TABLE `pre_registration_specimen` DISABLE KEYS */;
/*!40000 ALTER TABLE `pre_registration_specimen` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `referral_sources`
--

DROP TABLE IF EXISTS `referral_sources`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `referral_sources` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `display_name` varchar(255) NOT NULL DEFAULT '',
  `organisation_code` varchar(6) NOT NULL DEFAULT '',
  `parent_organisation_id` smallint(6) NOT NULL DEFAULT '0',
  `referral_type_id` smallint(6) NOT NULL DEFAULT '0',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `organisation_code` (`organisation_code`),
  UNIQUE KEY `display_name` (`display_name`),
  KEY `referral_type_id` (`referral_type_id`),
  KEY `parent_organisation_id` (`parent_organisation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `referral_sources`
--

LOCK TABLES `referral_sources` WRITE;
/*!40000 ALTER TABLE `referral_sources` DISABLE KEYS */;
INSERT INTO `referral_sources` (`id`, `display_name`, `organisation_code`, `parent_organisation_id`, `referral_type_id`, `is_active`) VALUES (1,'Newtown General Infirmary','ABC01',1,3,'yes'),(2,'The Surgery, Newtown, NT1 1NT','B12345',2,4,'yes'),(3,'Unknown hospital','X99999',3,3,'yes'),(4,'Unknown practice','V81999',4,4,'yes');
/*!40000 ALTER TABLE `referral_sources` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `referral_types`
--

DROP TABLE IF EXISTS `referral_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `referral_types` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL DEFAULT '',
  `prefix` varchar(2) DEFAULT NULL,
  `default_unknown` varchar(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `referral_types`
--

LOCK TABLES `referral_types` WRITE;
/*!40000 ALTER TABLE `referral_types` DISABLE KEYS */;
INSERT INTO `referral_types` (`id`, `description`, `prefix`, `default_unknown`) VALUES (1,'clinician','C','C9999998'),(2,'practitioner','G','G9999998'),(3,'hospital',NULL,'X99999'),(4,'practice',NULL,'V81999');
/*!40000 ALTER TABLE `referral_types` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `referrer_department`
--

DROP TABLE IF EXISTS `referrer_department`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `referrer_department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `referrer_id` int(11) NOT NULL DEFAULT '0',
  `parent_organisation_id` smallint(6) NOT NULL DEFAULT '0',
  `hospital_department_code` smallint(6) NOT NULL DEFAULT '0',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `referrer_parent_organisation` (`referrer_id`,`parent_organisation_id`),
  KEY `parent_organisation_id` (`parent_organisation_id`),
  KEY `hospital_department_code` (`hospital_department_code`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `referrer_department`
--

LOCK TABLES `referrer_department` WRITE;
/*!40000 ALTER TABLE `referrer_department` DISABLE KEYS */;
INSERT INTO `referrer_department` (`id`, `referrer_id`, `parent_organisation_id`, `hospital_department_code`, `is_active`) VALUES (1,1,1,823,'yes'),(2,2,2,600,'yes'),(3,3,1,100,'yes'),(4,4,3,999,'yes'),(5,5,4,600,'yes');
/*!40000 ALTER TABLE `referrer_department` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `referrers`
--

DROP TABLE IF EXISTS `referrers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `referrers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `national_code` varchar(8) NOT NULL DEFAULT '',
  `referral_type_id` smallint(6) NOT NULL DEFAULT '0',
  `active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `national_code` (`national_code`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `referrers`
--

LOCK TABLES `referrers` WRITE;
/*!40000 ALTER TABLE `referrers` DISABLE KEYS */;
INSERT INTO `referrers` (`id`, `name`, `national_code`, `referral_type_id`, `active`) VALUES (1,'Brown CC','C1234567',1,'yes'),(2,'Black DE','G1234567',2,'yes'),(3,'Cutter S','C7654321',1,'yes'),(4,'Unknown','C9999998',1,'yes'),(5,'Unknown','G9999998',2,'yes');
/*!40000 ALTER TABLE `referrers` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `report_error_codes`
--

DROP TABLE IF EXISTS `report_error_codes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `report_error_codes` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `code` varchar(2) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `report_error_codes`
--

LOCK TABLES `report_error_codes` WRITE;
/*!40000 ALTER TABLE `report_error_codes` DISABLE KEYS */;
/*!40000 ALTER TABLE `report_error_codes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `report_notification`
--

DROP TABLE IF EXISTS `report_notification`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `report_notification` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `type` enum('referrer','hospital','organisation') NOT NULL,
  `department_id` smallint(6) DEFAULT NULL,
  `identifier` varchar(50) NOT NULL,
  `status` enum('new','all') NOT NULL DEFAULT 'all',
  `contact_address` varchar(255) NOT NULL,
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `identity_address` (`name`,`contact_address`,`identifier`),
  KEY `department` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `report_notification`
--

LOCK TABLES `report_notification` WRITE;
/*!40000 ALTER TABLE `report_notification` DISABLE KEYS */;
/*!40000 ALTER TABLE `report_notification` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_audit`
--

DROP TABLE IF EXISTS `request_audit`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_audit` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `audit_request_option_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`request_id`,`audit_request_option_id`),
  KEY `audit_request_option_id` (`audit_request_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_audit`
--

LOCK TABLES `request_audit` WRITE;
/*!40000 ALTER TABLE `request_audit` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_audit` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_authorisation_diagnosis`
--

DROP TABLE IF EXISTS `request_authorisation_diagnosis`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_authorisation_diagnosis` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `report_diagnosis_id` smallint(6) NOT NULL DEFAULT '0',
  `auth_diagnosis_id` smallint(6) NOT NULL DEFAULT '0',
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`request_id`),
  KEY `report_diagnosis_id` (`report_diagnosis_id`),
  KEY `auth_diagnosis_id` (`auth_diagnosis_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_authorisation_diagnosis`
--

LOCK TABLES `request_authorisation_diagnosis` WRITE;
/*!40000 ALTER TABLE `request_authorisation_diagnosis` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_authorisation_diagnosis` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_consent`
--

DROP TABLE IF EXISTS `request_consent`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_consent` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `consent_id` smallint(6) NOT NULL DEFAULT '0',
  `status` enum('yes','no') NOT NULL DEFAULT 'no',
  PRIMARY KEY (`request_id`,`consent_id`),
  KEY `consent_id` (`consent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_consent`
--

LOCK TABLES `request_consent` WRITE;
/*!40000 ALTER TABLE `request_consent` DISABLE KEYS */;
INSERT INTO `request_consent` (`request_id`, `consent_id`, `status`) VALUES (1,1,'yes');
/*!40000 ALTER TABLE `request_consent` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_diagnosis_history`
--

DROP TABLE IF EXISTS `request_diagnosis_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_diagnosis_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL,
  `diagnosis_id` smallint(6) NOT NULL,
  `option_id` smallint(6) NOT NULL,
  `user_id` smallint(6) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_diagnosis_history`
--

LOCK TABLES `request_diagnosis_history` WRITE;
/*!40000 ALTER TABLE `request_diagnosis_history` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_diagnosis_history` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_dispatch_log`
--

DROP TABLE IF EXISTS `request_dispatch_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_dispatch_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `recipient` varchar(255) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `request_id` (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_dispatch_log`
--

LOCK TABLES `request_dispatch_log` WRITE;
/*!40000 ALTER TABLE `request_dispatch_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_dispatch_log` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_draft_report`
--

DROP TABLE IF EXISTS `request_draft_report`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_draft_report` (
  `user_id` smallint(6) NOT NULL,
  `request_id` int(11) NOT NULL,
  `request_notes` text,
  `clinical_details` text,
  `biopsy_site` varchar(255) DEFAULT NULL,
  `gross_description` varchar(255) DEFAULT NULL,
  `morphology` text,
  `comment` text,
  `status` enum('new','relapse','default') NOT NULL DEFAULT 'default',
  `diagnosis_id` smallint(6) NOT NULL,
  `secondary_diagnosis_id` smallint(6) DEFAULT NULL,
  `specimen_quality` enum('good','adequate','poor') DEFAULT 'adequate',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`request_id`,`user_id`),
  KEY `request_draft_report_ibfk_1` (`user_id`),
  KEY `request_draft_report_ibfk_2` (`diagnosis_id`),
  KEY `request_draft_report_ibfk_3` (`secondary_diagnosis_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_draft_report`
--

LOCK TABLES `request_draft_report` WRITE;
/*!40000 ALTER TABLE `request_draft_report` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_draft_report` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_error_code`
--

DROP TABLE IF EXISTS `request_error_code`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_error_code` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `error_code_id` smallint(6) NOT NULL DEFAULT '0',
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `error_code_id` (`error_code_id`),
  KEY `request_id` (`request_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_error_code`
--

LOCK TABLES `request_error_code` WRITE;
/*!40000 ALTER TABLE `request_error_code` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_error_code` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_external_ref`
--

DROP TABLE IF EXISTS `request_external_ref`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_external_ref` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `external_reference` varchar(255) NOT NULL DEFAULT '',
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_external_ref`
--

LOCK TABLES `request_external_ref` WRITE;
/*!40000 ALTER TABLE `request_external_ref` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_external_ref` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_general_notes`
--

DROP TABLE IF EXISTS `request_general_notes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_general_notes` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `detail` text,
  PRIMARY KEY (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_general_notes`
--

LOCK TABLES `request_general_notes` WRITE;
/*!40000 ALTER TABLE `request_general_notes` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_general_notes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_haematology`
--

DROP TABLE IF EXISTS `request_haematology`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_haematology` (
  `lab_number` char(8) NOT NULL,
  `status` enum('verified','default') NOT NULL DEFAULT 'default',
  `pda` varchar(6) NOT NULL,
  `wbc` char(4) NOT NULL,
  `wbc_f` tinyint(1) unsigned NOT NULL,
  `rbc` char(4) NOT NULL,
  `rbc_f` tinyint(1) unsigned NOT NULL,
  `hb` char(4) NOT NULL,
  `hb_f` tinyint(1) unsigned NOT NULL,
  `hct` char(4) NOT NULL,
  `hct_f` tinyint(1) unsigned NOT NULL,
  `mcv` char(4) NOT NULL,
  `mcv_f` tinyint(1) unsigned NOT NULL,
  `mch` char(4) NOT NULL,
  `mch_f` tinyint(1) unsigned NOT NULL,
  `mchc` char(4) NOT NULL,
  `mchc_f` tinyint(1) unsigned NOT NULL,
  `plt` char(4) NOT NULL,
  `plt_f` tinyint(1) unsigned NOT NULL,
  `lymph_percent` char(4) NOT NULL,
  `lymph_percent_f` tinyint(1) unsigned NOT NULL,
  `mixed_percent` char(4) NOT NULL,
  `mixed_percent_f` tinyint(1) unsigned NOT NULL,
  `neutr_percent` char(4) NOT NULL,
  `neutr_percent_f` tinyint(1) unsigned NOT NULL,
  `lymph` char(4) NOT NULL,
  `lymph_f` tinyint(1) unsigned NOT NULL,
  `mixed` char(4) NOT NULL,
  `mixed_f` tinyint(1) unsigned NOT NULL,
  `neutr` char(4) NOT NULL,
  `neutr_f` tinyint(1) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`lab_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_haematology`
--

LOCK TABLES `request_haematology` WRITE;
/*!40000 ALTER TABLE `request_haematology` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_haematology` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_history`
--

DROP TABLE IF EXISTS `request_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `action` varchar(255) NOT NULL DEFAULT '',
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `request_id` (`request_id`),
  KEY `action` (`action`),
  KEY `user_id` (`user_id`),
  KEY `time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_history`
--

LOCK TABLES `request_history` WRITE;
/*!40000 ALTER TABLE `request_history` DISABLE KEYS */;
INSERT INTO `request_history` (`id`, `request_id`, `action`, `user_id`, `time`) VALUES (1,1,'updated specimen gross description',1,'2017-05-23 20:28:50'),(2,2,'updated specimen gross description',1,'2017-05-23 20:28:50');
/*!40000 ALTER TABLE `request_history` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_initial_screen`
--

DROP TABLE IF EXISTS `request_initial_screen`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_initial_screen` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `screen_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`request_id`),
  KEY `screen_id` (`screen_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_initial_screen`
--

LOCK TABLES `request_initial_screen` WRITE;
/*!40000 ALTER TABLE `request_initial_screen` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_initial_screen` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_lab_section_foreign_id`
--

DROP TABLE IF EXISTS `request_lab_section_foreign_id`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_lab_section_foreign_id` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `lab_section_id` smallint(6) NOT NULL DEFAULT '0',
  `foreign_id` varchar(25) NOT NULL DEFAULT '',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`request_id`,`lab_section_id`),
  KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_lab_section_foreign_id`
--

LOCK TABLES `request_lab_section_foreign_id` WRITE;
/*!40000 ALTER TABLE `request_lab_section_foreign_id` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_lab_section_foreign_id` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_lab_section_notes`
--

DROP TABLE IF EXISTS `request_lab_section_notes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_lab_section_notes` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `lab_section_id` smallint(6) NOT NULL DEFAULT '0',
  `details` text,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`request_id`,`lab_section_id`),
  KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_lab_section_notes`
--

LOCK TABLES `request_lab_section_notes` WRITE;
/*!40000 ALTER TABLE `request_lab_section_notes` DISABLE KEYS */;
INSERT INTO `request_lab_section_notes` (`request_id`, `lab_section_id`, `details`, `time`) VALUES (1,1,'test detail','2017-05-23 20:28:47');
/*!40000 ALTER TABLE `request_lab_section_notes` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_lab_test_history`
--

DROP TABLE IF EXISTS `request_lab_test_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_lab_test_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `action` varchar(255) NOT NULL DEFAULT '',
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `request_id` (`request_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_lab_test_history`
--

LOCK TABLES `request_lab_test_history` WRITE;
/*!40000 ALTER TABLE `request_lab_test_history` DISABLE KEYS */;
INSERT INTO `request_lab_test_history` (`id`, `request_id`, `action`, `user_id`, `time`) VALUES (1,1,'set CML status to complete',1,'2017-05-23 20:28:48'),(2,1,'set AML status to complete',1,'2017-05-23 20:28:48'),(3,2,'set CML status to complete',1,'2017-05-23 20:28:48'),(4,2,'set AML status to complete',1,'2017-05-23 20:28:48'),(5,1,'set APML status to setup for USER',1,'2017-05-23 20:28:48'),(6,2,'deleted APML entry',1,'2017-05-23 20:28:49');
/*!40000 ALTER TABLE `request_lab_test_history` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_lab_test_results`
--

DROP TABLE IF EXISTS `request_lab_test_results`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_lab_test_results` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `lab_test_id` smallint(6) NOT NULL DEFAULT '0',
  `result` varchar(255) NOT NULL DEFAULT '',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_lab_test` (`request_id`,`lab_test_id`),
  KEY `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_lab_test_results`
--

LOCK TABLES `request_lab_test_results` WRITE;
/*!40000 ALTER TABLE `request_lab_test_results` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_lab_test_results` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_lab_test_status`
--

DROP TABLE IF EXISTS `request_lab_test_status`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_lab_test_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `lab_test_id` smallint(6) NOT NULL DEFAULT '0',
  `status_option_id` smallint(6) NOT NULL DEFAULT '0',
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `request_lab_test` (`request_id`,`lab_test_id`),
  KEY `lab_test_id` (`lab_test_id`),
  KEY `user_id` (`user_id`),
  KEY `status_option_id` (`status_option_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_lab_test_status`
--

LOCK TABLES `request_lab_test_status` WRITE;
/*!40000 ALTER TABLE `request_lab_test_status` DISABLE KEYS */;
INSERT INTO `request_lab_test_status` (`id`, `request_id`, `lab_test_id`, `status_option_id`, `user_id`, `time`) VALUES (1,1,1,1,1,'2017-05-23 20:28:47'),(2,1,2,2,1,'2017-05-23 20:28:48'),(3,1,3,2,1,'2017-05-23 20:28:48'),(4,1,4,1,1,'2017-05-23 20:28:47'),(5,1,5,4,2,'2017-05-23 20:28:48'),(6,2,1,1,1,'2017-05-23 20:28:47'),(7,2,2,2,1,'2017-05-23 20:28:48'),(8,2,3,2,1,'2017-05-23 20:28:48'),(9,2,4,1,1,'2017-05-23 20:28:47');
/*!40000 ALTER TABLE `request_lab_test_status` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_notification`
--

DROP TABLE IF EXISTS `request_notification`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_notification` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `datetime` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  PRIMARY KEY (`request_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_notification`
--

LOCK TABLES `request_notification` WRITE;
/*!40000 ALTER TABLE `request_notification` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_notification` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_option`
--

DROP TABLE IF EXISTS `request_option`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_option` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `option_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`request_id`,`option_id`),
  KEY `option_id` (`option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_option`
--

LOCK TABLES `request_option` WRITE;
/*!40000 ALTER TABLE `request_option` DISABLE KEYS */;
INSERT INTO `request_option` (`request_id`, `option_id`) VALUES (1,1),(2,2);
/*!40000 ALTER TABLE `request_option` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_phone_log`
--

DROP TABLE IF EXISTS `request_phone_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_phone_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `status` enum('inbound','outbound') DEFAULT NULL,
  `contact` varchar(255) NOT NULL DEFAULT '',
  `details` text NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `request_id` (`request_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_phone_log`
--

LOCK TABLES `request_phone_log` WRITE;
/*!40000 ALTER TABLE `request_phone_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_phone_log` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_print_log`
--

DROP TABLE IF EXISTS `request_print_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_print_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `request_id` (`request_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_print_log`
--

LOCK TABLES `request_print_log` WRITE;
/*!40000 ALTER TABLE `request_print_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_print_log` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_report_detail`
--

DROP TABLE IF EXISTS `request_report_detail`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_report_detail` (
  `request_id` int(11) NOT NULL,
  `clinical_details` text,
  `morphology` text,
  `comment` text,
  `status` enum('new','relapse','default') NOT NULL DEFAULT 'default',
  `diagnosis_id` smallint(6) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_report_detail`
--

LOCK TABLES `request_report_detail` WRITE;
/*!40000 ALTER TABLE `request_report_detail` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_report_detail` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_report_history`
--

DROP TABLE IF EXISTS `request_report_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_report_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `field` enum('morphology','comment') DEFAULT NULL,
  `content` text,
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `request_id` (`request_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_report_history`
--

LOCK TABLES `request_report_history` WRITE;
/*!40000 ALTER TABLE `request_report_history` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_report_history` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `request_report_patient_view`
--

DROP TABLE IF EXISTS `request_report_patient_view`;
/*!50001 DROP VIEW IF EXISTS `request_report_patient_view`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `request_report_patient_view` (
  `id` tinyint NOT NULL,
  `request_number` tinyint NOT NULL,
  `year` tinyint NOT NULL,
  `created_at` tinyint NOT NULL,
  `last_name` tinyint NOT NULL,
  `first_name` tinyint NOT NULL,
  `dob` tinyint NOT NULL,
  `unit_number` tinyint NOT NULL,
  `age` tinyint NOT NULL,
  `nhs_number` tinyint NOT NULL,
  `location` tinyint NOT NULL,
  `organisation_code` tinyint NOT NULL,
  `parent_code` tinyint NOT NULL,
  `diagnosis` tinyint NOT NULL,
  `icdo3` tinyint NOT NULL,
  `status` tinyint NOT NULL,
  `hmds_ref` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `request_report_view`
--

DROP TABLE IF EXISTS `request_report_view`;
/*!50001 DROP VIEW IF EXISTS `request_report_view`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `request_report_view` (
  `request_id` tinyint NOT NULL,
  `clinical_details` tinyint NOT NULL,
  `morphology` tinyint NOT NULL,
  `comment` tinyint NOT NULL,
  `status` tinyint NOT NULL,
  `diagnosis_id` tinyint NOT NULL,
  `gross_description` tinyint NOT NULL,
  `biopsy_site` tinyint NOT NULL,
  `specimen_quality` tinyint NOT NULL,
  `specimen_date` tinyint NOT NULL,
  `created_at` tinyint NOT NULL,
  `updated_at` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `request_result_summaries`
--

DROP TABLE IF EXISTS `request_result_summaries`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_result_summaries` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `lab_section_id` smallint(6) NOT NULL DEFAULT '0',
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `results_summary` text,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`request_id`,`lab_section_id`),
  KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_result_summaries`
--

LOCK TABLES `request_result_summaries` WRITE;
/*!40000 ALTER TABLE `request_result_summaries` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_result_summaries` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_secondary_diagnosis`
--

DROP TABLE IF EXISTS `request_secondary_diagnosis`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_secondary_diagnosis` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `secondary_diagnosis_id` smallint(6) NOT NULL,
  `time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`request_id`,`secondary_diagnosis_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_secondary_diagnosis`
--

LOCK TABLES `request_secondary_diagnosis` WRITE;
/*!40000 ALTER TABLE `request_secondary_diagnosis` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_secondary_diagnosis` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_specimen`
--

DROP TABLE IF EXISTS `request_specimen`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_specimen` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `specimen_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`request_id`,`specimen_id`),
  KEY `specimen_id` (`specimen_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_specimen`
--

LOCK TABLES `request_specimen` WRITE;
/*!40000 ALTER TABLE `request_specimen` DISABLE KEYS */;
INSERT INTO `request_specimen` (`request_id`, `specimen_id`) VALUES (1,1),(2,2),(1,4);
/*!40000 ALTER TABLE `request_specimen` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_specimen_detail`
--

DROP TABLE IF EXISTS `request_specimen_detail`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_specimen_detail` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `gross_description` text,
  `biopsy_site` text,
  `specimen_quality` enum('good','adequate','poor') DEFAULT NULL,
  `specimen_date` datetime DEFAULT NULL,
  PRIMARY KEY (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_specimen_detail`
--

LOCK TABLES `request_specimen_detail` WRITE;
/*!40000 ALTER TABLE `request_specimen_detail` DISABLE KEYS */;
INSERT INTO `request_specimen_detail` (`request_id`, `gross_description`, `biopsy_site`, `specimen_quality`, `specimen_date`) VALUES (1,'biopsy',NULL,NULL,'2017-05-22 20:28:44'),(2,'aspirate',NULL,NULL,NULL);
/*!40000 ALTER TABLE `request_specimen_detail` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `request_status_view`
--

DROP TABLE IF EXISTS `request_status_view`;
/*!50001 DROP VIEW IF EXISTS `request_status_view`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `request_status_view` (
  `request_id` tinyint NOT NULL,
  `status_option_id` tinyint NOT NULL,
  `action` tinyint NOT NULL,
  `username` tinyint NOT NULL,
  `time` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `request_storage`
--

DROP TABLE IF EXISTS `request_storage`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_storage` (
  `vialId` varchar(10) NOT NULL,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `specimen_id` smallint(6) NOT NULL DEFAULT '0',
  `sample` varchar(25) NOT NULL,
  `part_number` varchar(25) NOT NULL,
  `volume` smallint(6) DEFAULT '0',
  `concentration` smallint(6) DEFAULT '0',
  `source` varchar(25) NOT NULL,
  `method` varchar(25) DEFAULT NULL,
  `rack_id` smallint(6) DEFAULT NULL,
  `vial_location` varchar(3) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `signed_out` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`vialId`),
  KEY `specimen_id` (`specimen_id`),
  KEY `request_id` (`request_id`),
  KEY `rack_id` (`rack_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_storage`
--

LOCK TABLES `request_storage` WRITE;
/*!40000 ALTER TABLE `request_storage` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_storage` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `request_storage_view`
--

DROP TABLE IF EXISTS `request_storage_view`;
/*!50001 DROP VIEW IF EXISTS `request_storage_view`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `request_storage_view` (
  `vialId` tinyint NOT NULL,
  `request_id` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `request_trial`
--

DROP TABLE IF EXISTS `request_trial`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_trial` (
  `request_id` int(11) NOT NULL DEFAULT '0',
  `trial_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`request_id`),
  KEY `trial_id` (`trial_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_trial`
--

LOCK TABLES `request_trial` WRITE;
/*!40000 ALTER TABLE `request_trial` DISABLE KEYS */;
INSERT INTO `request_trial` (`request_id`, `trial_id`) VALUES (1,1);
/*!40000 ALTER TABLE `request_trial` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `request_view_log`
--

DROP TABLE IF EXISTS `request_view_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request_view_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` int(11) NOT NULL DEFAULT '0',
  `user_id` smallint(6) NOT NULL DEFAULT '0',
  `ip_address` varchar(255) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `request_id` (`request_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `request_view_log`
--

LOCK TABLES `request_view_log` WRITE;
/*!40000 ALTER TABLE `request_view_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `request_view_log` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `requests`
--

DROP TABLE IF EXISTS `requests`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_number` smallint(6) NOT NULL DEFAULT '0',
  `year` year(4) NOT NULL DEFAULT '0000',
  `patient_case_id` int(11) NOT NULL DEFAULT '0',
  `referrer_department_id` int(11) NOT NULL DEFAULT '0',
  `status_option_id` smallint(6) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lab_no` (`request_number`,`year`),
  KEY `patient_case_id` (`patient_case_id`),
  KEY `referrer_department_id` (`referrer_department_id`),
  KEY `status_option_id` (`status_option_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `requests`
--

LOCK TABLES `requests` WRITE;
/*!40000 ALTER TABLE `requests` DISABLE KEYS */;
INSERT INTO `requests` (`id`, `request_number`, `year`, `patient_case_id`, `referrer_department_id`, `status_option_id`, `created_at`, `updated_at`) VALUES (1,1,2017,1,2,1,'2017-05-23 19:28:44','2017-05-23 20:28:45'),(2,2,2017,2,1,1,'2017-05-23 19:28:44','2017-05-23 20:28:45');
/*!40000 ALTER TABLE `requests` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `result_summary_lab_test`
--

DROP TABLE IF EXISTS `result_summary_lab_test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `result_summary_lab_test` (
  `result_summary_id` smallint(6) NOT NULL,
  `lab_test_id` smallint(6) NOT NULL,
  PRIMARY KEY (`result_summary_id`,`lab_test_id`),
  KEY `result_summary_lab_test_ibfk2` (`lab_test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `result_summary_lab_test`
--

LOCK TABLES `result_summary_lab_test` WRITE;
/*!40000 ALTER TABLE `result_summary_lab_test` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_summary_lab_test` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `result_summary_options`
--

DROP TABLE IF EXISTS `result_summary_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `result_summary_options` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL DEFAULT '',
  `lab_section_id` smallint(6) NOT NULL DEFAULT '0',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `lab_section_description` (`description`,`lab_section_id`),
  KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `result_summary_options`
--

LOCK TABLES `result_summary_options` WRITE;
/*!40000 ALTER TABLE `result_summary_options` DISABLE KEYS */;
INSERT INTO `result_summary_options` (`id`, `description`, `lab_section_id`, `is_active`) VALUES (1,'Normal',1,'yes');
/*!40000 ALTER TABLE `result_summary_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `sample_types`
--

DROP TABLE IF EXISTS `sample_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sample_types` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `specimen_type` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `specimen_type` (`specimen_type`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `sample_types`
--

LOCK TABLES `sample_types` WRITE;
/*!40000 ALTER TABLE `sample_types` DISABLE KEYS */;
INSERT INTO `sample_types` (`id`, `specimen_type`) VALUES (2,'biopsy'),(1,'liquid');
/*!40000 ALTER TABLE `sample_types` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `screen_category`
--

DROP TABLE IF EXISTS `screen_category`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `screen_category` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `screen_category`
--

LOCK TABLES `screen_category` WRITE;
/*!40000 ALTER TABLE `screen_category` DISABLE KEYS */;
INSERT INTO `screen_category` (`id`, `name`, `is_active`) VALUES (1,'fresh','yes'),(2,'fixed','yes');
/*!40000 ALTER TABLE `screen_category` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `screen_lab_test`
--

DROP TABLE IF EXISTS `screen_lab_test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `screen_lab_test` (
  `screen_id` smallint(6) NOT NULL DEFAULT '0',
  `lab_test_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`screen_id`,`lab_test_id`),
  KEY `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `screen_lab_test`
--

LOCK TABLES `screen_lab_test` WRITE;
/*!40000 ALTER TABLE `screen_lab_test` DISABLE KEYS */;
INSERT INTO `screen_lab_test` (`screen_id`, `lab_test_id`) VALUES (2,1),(1,2),(1,5);
/*!40000 ALTER TABLE `screen_lab_test` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `screen_lab_test_detail`
--

DROP TABLE IF EXISTS `screen_lab_test_detail`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `screen_lab_test_detail` (
  `screen_id` smallint(6) NOT NULL DEFAULT '0',
  `lab_section_id` smallint(6) NOT NULL DEFAULT '0',
  `test_details` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`screen_id`,`lab_section_id`),
  KEY `lab_section_id` (`lab_section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `screen_lab_test_detail`
--

LOCK TABLES `screen_lab_test_detail` WRITE;
/*!40000 ALTER TABLE `screen_lab_test_detail` DISABLE KEYS */;
INSERT INTO `screen_lab_test_detail` (`screen_id`, `lab_section_id`, `test_details`) VALUES (1,1,'AML partial panel');
/*!40000 ALTER TABLE `screen_lab_test_detail` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `screens`
--

DROP TABLE IF EXISTS `screens`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `screens` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(50) DEFAULT NULL,
  `category_id` smallint(6) NOT NULL DEFAULT '0',
  `active` enum('yes','no') DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `category_description` (`category_id`,`description`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `screens`
--

LOCK TABLES `screens` WRITE;
/*!40000 ALTER TABLE `screens` DISABLE KEYS */;
INSERT INTO `screens` (`id`, `description`, `category_id`, `active`) VALUES (1,'AML',1,'yes'),(2,'PNH',1,'yes');
/*!40000 ALTER TABLE `screens` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `sessions`
--

DROP TABLE IF EXISTS `sessions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sessions` (
  `id` varchar(32) NOT NULL DEFAULT '',
  `userid` varchar(255) DEFAULT NULL,
  `a_session` text NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `sessions`
--

LOCK TABLES `sessions` WRITE;
/*!40000 ALTER TABLE `sessions` DISABLE KEYS */;
INSERT INTO `sessions` (`id`, `userid`, `a_session`, `time`) VALUES ('83a6a434b7263de47cc887601ce629bf',NULL,'$D = {\'_SESSION_ETIME\' => 86400,\'_SESSION_ID\' => \'83a6a434b7263de47cc887601ce629bf\',\'_SESSION_ATIME\' => \'1495571330\',\'_SESSION_REMOTE_ADDR\' => \'127.0.0.1\',\'__CAP_MessageStack_Stack\' => [],\'_SESSION_CTIME\' => \'1495571330\'};;$D','2017-05-23 20:28:50');
/*!40000 ALTER TABLE `sessions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `specimen_lab_test`
--

DROP TABLE IF EXISTS `specimen_lab_test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `specimen_lab_test` (
  `specimen_id` smallint(6) NOT NULL DEFAULT '0',
  `lab_test_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`specimen_id`,`lab_test_id`),
  KEY `lab_test_id` (`lab_test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `specimen_lab_test`
--

LOCK TABLES `specimen_lab_test` WRITE;
/*!40000 ALTER TABLE `specimen_lab_test` DISABLE KEYS */;
INSERT INTO `specimen_lab_test` (`specimen_id`, `lab_test_id`) VALUES (4,9);
/*!40000 ALTER TABLE `specimen_lab_test` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `specimen_sample_type`
--

DROP TABLE IF EXISTS `specimen_sample_type`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `specimen_sample_type` (
  `specimen_id` smallint(6) NOT NULL DEFAULT '0',
  `sample_type_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sample_type_id`,`specimen_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `specimen_sample_type`
--

LOCK TABLES `specimen_sample_type` WRITE;
/*!40000 ALTER TABLE `specimen_sample_type` DISABLE KEYS */;
INSERT INTO `specimen_sample_type` (`specimen_id`, `sample_type_id`) VALUES (1,1),(2,1),(3,1),(4,1),(4,2);
/*!40000 ALTER TABLE `specimen_sample_type` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `specimens`
--

DROP TABLE IF EXISTS `specimens`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `specimens` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `sample_code` varchar(4) DEFAULT NULL,
  `description` varchar(40) DEFAULT NULL,
  `active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `sample_code` (`sample_code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `specimens`
--

LOCK TABLES `specimens` WRITE;
/*!40000 ALTER TABLE `specimens` DISABLE KEYS */;
INSERT INTO `specimens` (`id`, `sample_code`, `description`, `active`) VALUES (1,'PB','peripheral blood','yes'),(2,'LU','lymph node, unfixed','yes'),(3,'BMA','bone marrow aspirate','yes'),(4,'BMAT','bone marrow aspirate & trephine','yes');
/*!40000 ALTER TABLE `specimens` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `status_options`
--

DROP TABLE IF EXISTS `status_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `status_options` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) NOT NULL DEFAULT '',
  `is_editable` enum('yes','no') NOT NULL DEFAULT 'yes',
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `status_options`
--

LOCK TABLES `status_options` WRITE;
/*!40000 ALTER TABLE `status_options` DISABLE KEYS */;
INSERT INTO `status_options` (`id`, `description`, `is_editable`, `is_active`) VALUES (1,'new','yes','yes'),(2,'screened','yes','yes'),(3,'reported','yes','yes'),(4,'authorised','yes','yes'),(5,'complete','yes','yes'),(6,'deleted','yes','yes');
/*!40000 ALTER TABLE `status_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `storage_racks`
--

DROP TABLE IF EXISTS `storage_racks`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `storage_racks` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `plateId` varchar(10) NOT NULL,
  `storage_location` varchar(50) DEFAULT NULL,
  `is_active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `plateID` (`plateId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `storage_racks`
--

LOCK TABLES `storage_racks` WRITE;
/*!40000 ALTER TABLE `storage_racks` DISABLE KEYS */;
/*!40000 ALTER TABLE `storage_racks` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user_functions`
--

DROP TABLE IF EXISTS `user_functions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_functions` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `function_name` varchar(20) DEFAULT NULL,
  `function_detail` varchar(255) DEFAULT NULL,
  `active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `function_name` (`function_name`),
  UNIQUE KEY `function_detail` (`function_detail`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_functions`
--

LOCK TABLES `user_functions` WRITE;
/*!40000 ALTER TABLE `user_functions` DISABLE KEYS */;
INSERT INTO `user_functions` (`id`, `function_name`, `function_detail`, `active`) VALUES (1,'search','search for requests','yes'),(2,'view_history','view request history','yes'),(3,'print_one','print a record','yes'),(4,'register','register requests','yes'),(5,'edit_pid','edit patient','yes'),(6,'modify_results','modify results','yes'),(7,'screen','screen requests','yes'),(8,'report','report requests','yes'),(9,'do_admin','admin functions','yes'),(10,'phone_log','view/edit phone log','yes'),(11,'log_errors','log errors','yes'),(12,'delete_record','delete requests','yes'),(13,'patient_merge','merge patients','yes'),(14,'view_unrestricted','view unauthorised entries','yes');
/*!40000 ALTER TABLE `user_functions` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user_group_function`
--

DROP TABLE IF EXISTS `user_group_function`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_group_function` (
  `group_id` smallint(6) NOT NULL,
  `function_id` smallint(6) NOT NULL,
  PRIMARY KEY (`group_id`,`function_id`),
  KEY `function` (`function_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_group_function`
--

LOCK TABLES `user_group_function` WRITE;
/*!40000 ALTER TABLE `user_group_function` DISABLE KEYS */;
INSERT INTO `user_group_function` (`group_id`, `function_id`) VALUES (1,1),(2,1),(3,1),(1,2),(2,2),(3,2),(1,3),(2,3),(3,3),(1,4),(3,4),(1,5),(1,6),(1,7),(1,8),(1,9),(1,10),(1,11),(1,12),(1,13),(1,14);
/*!40000 ALTER TABLE `user_group_function` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user_groups`
--

DROP TABLE IF EXISTS `user_groups`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_groups` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `group_name` varchar(10) DEFAULT NULL,
  `group_label` varchar(30) DEFAULT NULL,
  `group_detail` varchar(255) DEFAULT NULL,
  `active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_name` (`group_name`),
  UNIQUE KEY `group_label` (`group_label`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_groups`
--

LOCK TABLES `user_groups` WRITE;
/*!40000 ALTER TABLE `user_groups` DISABLE KEYS */;
INSERT INTO `user_groups` (`id`, `group_name`, `group_label`, `group_detail`, `active`) VALUES (1,'su','SuperUser','superuser description','yes'),(2,'guest','Guest','guest description','yes'),(3,'bms','BMS','bms description','yes');
/*!40000 ALTER TABLE `user_groups` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user_locations`
--

DROP TABLE IF EXISTS `user_locations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_locations` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `location_name` varchar(50) NOT NULL DEFAULT '',
  `region_code` varchar(6) DEFAULT NULL,
  `active` enum('yes','no') DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `location_name` (`location_name`),
  KEY `region_code` (`region_code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_locations`
--

LOCK TABLES `user_locations` WRITE;
/*!40000 ALTER TABLE `user_locations` DISABLE KEYS */;
INSERT INTO `user_locations` (`id`, `location_name`, `region_code`, `active`) VALUES (1,'HMDS','ABC','yes');
/*!40000 ALTER TABLE `user_locations` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user_message`
--

DROP TABLE IF EXISTS `user_message`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `recipient_id` smallint(6) NOT NULL DEFAULT '0',
  `sender_id` smallint(6) NOT NULL DEFAULT '0',
  `message` text NOT NULL,
  `received` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `acknowledged` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `recipient_id` (`recipient_id`),
  KEY `sender_id` (`sender_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_message`
--

LOCK TABLES `user_message` WRITE;
/*!40000 ALTER TABLE `user_message` DISABLE KEYS */;
/*!40000 ALTER TABLE `user_message` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `user_permission`
--

DROP TABLE IF EXISTS `user_permission`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user_permission` (
  `user_id` smallint(6) NOT NULL AUTO_INCREMENT,
  `function_id` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`,`function_id`),
  KEY `function_id` (`function_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user_permission`
--

LOCK TABLES `user_permission` WRITE;
/*!40000 ALTER TABLE `user_permission` DISABLE KEYS */;
/*!40000 ALTER TABLE `user_permission` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL DEFAULT 'username',
  `first_name` varchar(50) NOT NULL DEFAULT 'first_name',
  `last_name` varchar(50) NOT NULL DEFAULT 'last_name',
  `password` varchar(32) NOT NULL DEFAULT 'password',
  `email` varchar(50) DEFAULT NULL,
  `user_location_id` smallint(6) NOT NULL DEFAULT '0',
  `designation` varchar(255) NOT NULL DEFAULT 'designation',
  `group_id` smallint(6) NOT NULL DEFAULT '0',
  `last_login` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `active` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `login_id` (`first_name`,`last_name`),
  UNIQUE KEY `email` (`email`),
  KEY `group_id` (`group_id`),
  KEY `user_location_id` (`user_location_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` (`id`, `username`, `first_name`, `last_name`, `password`, `email`, `user_location_id`, `designation`, `group_id`, `last_login`, `active`) VALUES (1,'admin','admin_fname','admin_lname','pX4bLclVVdFwnQoyStFF3jIVAYI','admin@here.com',1,'administrator',1,'2017-05-23 20:28:47','yes'),(2,'user','user_fname','user_lname','pwd',NULL,1,'user',3,'2017-05-23 20:28:47','yes');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `zz_lab_tests`
--

DROP TABLE IF EXISTS `zz_lab_tests`;
/*!50001 DROP VIEW IF EXISTS `zz_lab_tests`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `zz_lab_tests` (
  `section_name` tinyint NOT NULL,
  `test_name` tinyint NOT NULL,
  `field_label` tinyint NOT NULL,
  `is_active` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `zz_view_lab_tests`
--

DROP TABLE IF EXISTS `zz_view_lab_tests`;
/*!50001 DROP VIEW IF EXISTS `zz_view_lab_tests`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `zz_view_lab_tests` (
  `section_name` tinyint NOT NULL,
  `test_name` tinyint NOT NULL,
  `field_label` tinyint NOT NULL,
  `section_active` tinyint NOT NULL,
  `test_active` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `zz_view_logins`
--

DROP TABLE IF EXISTS `zz_view_logins`;
/*!50001 DROP VIEW IF EXISTS `zz_view_logins`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `zz_view_logins` (
  `username` tinyint NOT NULL,
  `n` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Temporary table structure for view `zz_view_users`
--

DROP TABLE IF EXISTS `zz_view_users`;
/*!50001 DROP VIEW IF EXISTS `zz_view_users`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE TABLE `zz_view_users` (
  `first_name` tinyint NOT NULL,
  `last_name` tinyint NOT NULL,
  `email` tinyint NOT NULL,
  `user_group` tinyint NOT NULL,
  `location_name` tinyint NOT NULL
) ENGINE=MyISAM */;
SET character_set_client = @saved_cs_client;

--
-- Final view structure for view `authorised_reports_view`
--

/*!50001 DROP TABLE IF EXISTS `authorised_reports_view`*/;
/*!50001 DROP VIEW IF EXISTS `authorised_reports_view`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`raj`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `authorised_reports_view` AS select `r`.`id` AS `id`,`r`.`request_number` AS `request_number`,`r`.`year` AS `year`,cast(`r`.`created_at` as date) AS `reg_date`,`p`.`last_name` AS `last_name`,`p`.`first_name` AS `first_name`,`p`.`dob` AS `dob`,`pc`.`unit_number` AS `unit_number`,((date_format(`r`.`created_at`,_utf8'%Y') - date_format(`p`.`dob`,_utf8'%Y')) - (date_format(`r`.`created_at`,_utf8'00-%m-%d') < date_format(`p`.`dob`,_utf8'00-%m-%d'))) AS `age`,`p`.`nhs_number` AS `nhs_number`,`rs`.`display_name` AS `location`,`rs`.`organisation_code` AS `organisation_code`,`po`.`parent_code` AS `parent_code`,`d`.`name` AS `diagnosis`,`d`.`icdo3` AS `icdo3`,`rr`.`status` AS `status`,cast(`rh`.`time` as date) AS `auth_date` from (((((((`requests` `r` join `patient_case` `pc` on((`r`.`patient_case_id` = `pc`.`id`))) join `referral_sources` `rs` on((`pc`.`referral_source_id` = `rs`.`id`))) join `parent_organisations` `po` on((`rs`.`parent_organisation_id` = `po`.`id`))) join `patients` `p` on((`pc`.`patient_id` = `p`.`id`))) join `request_report_detail` `rr` on((`rr`.`request_id` = `r`.`id`))) join `diagnoses` `d` on((`rr`.`diagnosis_id` = `d`.`id`))) join `request_history` `rh` on(((`rh`.`request_id` = `r`.`id`) and (`rh`.`action` = _latin1'authorised')))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `family_id_view`
--

/*!50001 DROP TABLE IF EXISTS `family_id_view`*/;
/*!50001 DROP VIEW IF EXISTS `family_id_view`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`raj`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `family_id_view` AS select `r`.`id` AS `request_id`,`tr`.`result` AS `family_id` from (`requests` `r` join (`request_lab_test_results` `tr` join `lab_tests` `lt` on(((`tr`.`lab_test_id` = `lt`.`id`) and (`lt`.`test_name` = 'family_id')))) on((`tr`.`request_id` = `r`.`id`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `request_report_patient_view`
--

/*!50001 DROP TABLE IF EXISTS `request_report_patient_view`*/;
/*!50001 DROP VIEW IF EXISTS `request_report_patient_view`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`raj`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `request_report_patient_view` AS select `r`.`id` AS `id`,`r`.`request_number` AS `request_number`,`r`.`year` AS `year`,`r`.`created_at` AS `created_at`,`p`.`last_name` AS `last_name`,`p`.`first_name` AS `first_name`,`p`.`dob` AS `dob`,`pc`.`unit_number` AS `unit_number`,((date_format(`r`.`created_at`,_utf8'%Y') - date_format(`p`.`dob`,_utf8'%Y')) - (date_format(`r`.`created_at`,_utf8'00-%m-%d') < date_format(`p`.`dob`,_utf8'00-%m-%d'))) AS `age`,`p`.`nhs_number` AS `nhs_number`,`rs`.`display_name` AS `location`,`rs`.`organisation_code` AS `organisation_code`,`po`.`parent_code` AS `parent_code`,`d`.`name` AS `diagnosis`,`d`.`icdo3` AS `icdo3`,`rr`.`status` AS `status`,concat_ws(_utf8'/',`r`.`request_number`,(`r`.`year` - 2000)) AS `hmds_ref` from ((((((`requests` `r` join `patient_case` `pc` on((`r`.`patient_case_id` = `pc`.`id`))) join `referral_sources` `rs` on((`pc`.`referral_source_id` = `rs`.`id`))) join `parent_organisations` `po` on((`rs`.`parent_organisation_id` = `po`.`id`))) join `patients` `p` on((`pc`.`patient_id` = `p`.`id`))) join `request_report_view` `rr` on((`rr`.`request_id` = `r`.`id`))) join `diagnoses` `d` on((`rr`.`diagnosis_id` = `d`.`id`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `request_report_view`
--

/*!50001 DROP TABLE IF EXISTS `request_report_view`*/;
/*!50001 DROP VIEW IF EXISTS `request_report_view`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`raj`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `request_report_view` AS select `t1`.`request_id` AS `request_id`,`t1`.`clinical_details` AS `clinical_details`,`t1`.`morphology` AS `morphology`,`t1`.`comment` AS `comment`,`t1`.`status` AS `status`,`t1`.`diagnosis_id` AS `diagnosis_id`,`t2`.`gross_description` AS `gross_description`,`t2`.`biopsy_site` AS `biopsy_site`,`t2`.`specimen_quality` AS `specimen_quality`,`t2`.`specimen_date` AS `specimen_date`,`t1`.`created_at` AS `created_at`,`t1`.`updated_at` AS `updated_at` from (`request_report_detail` `t1` join `request_specimen_detail` `t2` on((`t1`.`request_id` = `t2`.`request_id`))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `request_status_view`
--

/*!50001 DROP TABLE IF EXISTS `request_status_view`*/;
/*!50001 DROP VIEW IF EXISTS `request_status_view`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8 */;
/*!50001 SET character_set_results     = utf8 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`raj`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `request_status_view` AS select `rh`.`request_id` AS `request_id`,(case when (`rh`.`action` = 'registered') then 1 else `so`.`id` end) AS `status_option_id`,`rh`.`action` AS `action`,`u`.`username` AS `username`,`rh`.`time` AS `time` from ((`request_history` `rh` join `users` `u` on((`rh`.`user_id` = `u`.`id`))) left join `status_options` `so` on((`rh`.`action` = `so`.`description`))) where (`rh`.`action` in ('registered','screened','reported','authorised')) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `request_storage_view`
--

/*!50001 DROP TABLE IF EXISTS `request_storage_view`*/;
/*!50001 DROP VIEW IF EXISTS `request_storage_view`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`raj`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `request_storage_view` AS select `rs`.`vialId` AS `vialId`,`rs`.`request_id` AS `request_id` from (`requests` `r` join `request_storage` `rs` on((`rs`.`request_id` = `r`.`id`))) union select `tr`.`result` AS `vialId`,`tr`.`request_id` AS `request_id` from ((`requests` `r` join `request_lab_test_results` `tr` on((`tr`.`request_id` = `r`.`id`))) join `lab_tests` `lt` on(((`tr`.`lab_test_id` = `lt`.`id`) and (`lt`.`test_name` in ('edta1_fluidx','tumour_fluidx'))))) */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `zz_lab_tests`
--

/*!50001 DROP TABLE IF EXISTS `zz_lab_tests`*/;
/*!50001 DROP VIEW IF EXISTS `zz_lab_tests`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`raj`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `zz_lab_tests` AS select `ls`.`section_name` AS `section_name`,`lt`.`test_name` AS `test_name`,`lt`.`field_label` AS `field_label`,`lt`.`is_active` AS `is_active` from (`lab_tests` `lt` join `lab_sections` `ls` on((`lt`.`lab_section_id` = `ls`.`id`))) order by `ls`.`section_name`,`lt`.`field_label` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `zz_view_lab_tests`
--

/*!50001 DROP TABLE IF EXISTS `zz_view_lab_tests`*/;
/*!50001 DROP VIEW IF EXISTS `zz_view_lab_tests`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`raj`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `zz_view_lab_tests` AS select `ls`.`section_name` AS `section_name`,`lt`.`test_name` AS `test_name`,`lt`.`field_label` AS `field_label`,`ls`.`is_active` AS `section_active`,`lt`.`is_active` AS `test_active` from (`lab_tests` `lt` join `lab_sections` `ls` on((`lt`.`lab_section_id` = `ls`.`id`))) where (`lt`.`is_active` = 'yes') order by `ls`.`section_name`,`lt`.`field_label` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `zz_view_logins`
--

/*!50001 DROP TABLE IF EXISTS `zz_view_logins`*/;
/*!50001 DROP VIEW IF EXISTS `zz_view_logins`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`raj`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `zz_view_logins` AS select `u`.`username` AS `username`,count(0) AS `n` from (`logins` `l` join `users` `u` on((`l`.`user_id` = `u`.`id`))) group by `u`.`id` order by count(0) desc */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;

--
-- Final view structure for view `zz_view_users`
--

/*!50001 DROP TABLE IF EXISTS `zz_view_users`*/;
/*!50001 DROP VIEW IF EXISTS `zz_view_users`*/;
/*!50001 SET @saved_cs_client          = @@character_set_client */;
/*!50001 SET @saved_cs_results         = @@character_set_results */;
/*!50001 SET @saved_col_connection     = @@collation_connection */;
/*!50001 SET character_set_client      = utf8mb4 */;
/*!50001 SET character_set_results     = utf8mb4 */;
/*!50001 SET collation_connection      = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`raj`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `zz_view_users` AS select `u`.`first_name` AS `first_name`,`u`.`last_name` AS `last_name`,`u`.`email` AS `email`,`ug`.`group_label` AS `user_group`,`ul`.`location_name` AS `location_name` from ((`users` `u` join `user_locations` `ul` on((`u`.`user_location_id` = `ul`.`id`))) join `user_groups` `ug` on((`u`.`group_id` = `ug`.`id`))) order by `u`.`last_name` */;
/*!50001 SET character_set_client      = @saved_cs_client */;
/*!50001 SET character_set_results     = @saved_cs_results */;
/*!50001 SET collation_connection      = @saved_col_connection */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-06-02 16:29:12
