-- --------------------------------------------------------
-- Host:                         163.160.171.164
-- Server version:               5.0.67-log - SUSE MySQL RPM
-- Server OS:                    suse-linux-gnu
-- HeidiSQL Version:             8.1.0.4545
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!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' */;

-- Dumping database structure for hilis4_views
CREATE DATABASE IF NOT EXISTS `hilis4_views` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `hilis4_views`;


-- Dumping structure for view hilis4_views.diagnostic_sub_categories
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `diagnostic_sub_categories` (
	`name` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
	`icdo3` VARCHAR(6) NULL COLLATE 'latin1_swedish_ci',
	`sub-category` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci'
) ENGINE=MyISAM;


-- Dumping structure for view hilis4_views.error_code_assignments
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `error_code_assignments` (
	`type` ENUM('patient','new_request','request_edit','errors') NULL COLLATE 'latin1_swedish_ci',
	`code` VARCHAR(2) NULL COLLATE 'latin1_swedish_ci',
	`description` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci'
) ENGINE=MyISAM;


-- Dumping structure for view hilis4_views.local_network_locations
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `local_network_locations` (
	`display_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
	`organisation_code` VARCHAR(6) NOT NULL COLLATE 'latin1_swedish_ci',
	`parent_code` VARCHAR(6) NOT NULL COLLATE 'latin1_swedish_ci'
) ENGINE=MyISAM;


-- Dumping structure for view hilis4_views.logins
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `logins` (
	`id` INT(11) NOT NULL,
	`username` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
	`user_id` SMALLINT(6) NOT NULL,
	`IP` VARCHAR(15) NULL COLLATE 'latin1_swedish_ci',
	`time` TIMESTAMP NOT NULL,
	`browser` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
	`session_id` CHAR(32) NULL COLLATE 'latin1_swedish_ci'
) ENGINE=MyISAM;


-- Dumping structure for view hilis4_views.mailed_reports
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `mailed_reports` (
	`request_number` SMALLINT(6) NOT NULL,
	`year` YEAR NOT NULL,
	`diagnosis` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
	`status` ENUM('new','relapse','default') NOT NULL COLLATE 'latin1_swedish_ci',
	`location` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
	`referrer` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
	`department` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci'
) ENGINE=MyISAM;


-- Dumping structure for view hilis4_views.no_report_table_entry
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `no_report_table_entry` (
	`id` INT(11) NOT NULL,
	`request_number` SMALLINT(6) NOT NULL,
	`year` YEAR NOT NULL,
	`registered` DATE NULL,
	`screened` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci',
	`trial_name` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci'
) ENGINE=MyISAM;


-- Dumping structure for view hilis4_views.outreach_no_demographics
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `outreach_no_demographics` (
	`id` INT(11) NOT NULL,
	`request_number` SMALLINT(6) NOT NULL,
	`year` YEAR NOT NULL,
	`patient_case_id` INT(11) NOT NULL,
	`referrer_department_id` INT(11) NOT NULL,
	`status_option_id` SMALLINT(6) NOT NULL,
	`created_at` TIMESTAMP NOT NULL,
	`updated_at` TIMESTAMP NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view hilis4_views.outstanding_lab_tests
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `outstanding_lab_tests` (
	`section_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
	`test_name` VARCHAR(25) NOT NULL COLLATE 'latin1_swedish_ci',
	`f` BIGINT(21) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view hilis4_views.panel_tests
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `panel_tests` (
	`section_name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
	`panel` VARCHAR(25) NOT NULL COLLATE 'latin1_swedish_ci',
	`test` VARCHAR(25) NOT NULL COLLATE 'latin1_swedish_ci'
) ENGINE=MyISAM;


-- Dumping structure for view hilis4_views.diagnostic_sub_categories
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `diagnostic_sub_categories`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER VIEW `hilis4_views`.`diagnostic_sub_categories` AS select `d`.`name` AS `name`,`d`.`icdo3` AS `icdo3`,`dc`.`description` AS `sub-category` from ((`hilis4`.`diagnoses` `d` join `hilis4`.`icdo_category` `ic` on((`d`.`icdo3` = `ic`.`icdo3`))) join `hilis4`.`diagnostic_categories` `dc` on((`ic`.`diagnostic_category_id` = `dc`.`id`))) where ((`dc`.`category_type` = _latin1'sub') and (`d`.`active` = _latin1'yes')) order by `d`.`name`;


-- Dumping structure for view hilis4_views.error_code_assignments
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `error_code_assignments`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER VIEW `hilis4_views`.`error_code_assignments` AS select `t1`.`type` AS `type`,`t2`.`code` AS `code`,`t2`.`description` AS `description` from (`hilis4`.`error_code_assignment` `t1` join `hilis4`.`error_codes` `t2` on((`t1`.`error_code_id` = `t2`.`id`))) order by `t1`.`type`,`t2`.`code`;


-- Dumping structure for view hilis4_views.local_network_locations
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `local_network_locations`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER VIEW `hilis4_views`.`local_network_locations` AS select `t3`.`display_name` AS `display_name`,`t3`.`organisation_code` AS `organisation_code`,`t2`.`parent_code` AS `parent_code` from ((`hilis4`.`local_network_locations` `t1` join `hilis4`.`parent_organisations` `t2` on((`t1`.`parent_id` = `t2`.`id`))) join `hilis4`.`referral_sources` `t3` on((`t3`.`parent_organisation_id` = `t2`.`id`))) order by `t2`.`parent_code`;


-- Dumping structure for view hilis4_views.logins
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `logins`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER VIEW `hilis4_views`.`logins` AS select `l`.`id` AS `id`,`u`.`username` AS `username`,`l`.`user_id` AS `user_id`,`l`.`address` AS `IP`,`l`.`time` AS `time`,`l`.`browser` AS `browser`,`l`.`session_id` AS `session_id` from (`hilis4`.`logins` `l` join `hilis4`.`users` `u` on((`l`.`user_id` = `u`.`id`))) order by `l`.`time` desc;


-- Dumping structure for view hilis4_views.mailed_reports
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `mailed_reports`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER VIEW `hilis4_views`.`mailed_reports` AS select `r1`.`request_number` AS `request_number`,`r1`.`year` AS `year`,`d`.`name` AS `diagnosis`,`rr`.`status` AS `status`,`rs`.`display_name` AS `location`,`r2`.`name` AS `referrer`,`hd`.`display_name` AS `department` from ((((((((`hilis4`.`requests` `r1` join `hilis4`.`patient_case` `pc` on((`r1`.`patient_case_id` = `pc`.`id`))) join `hilis4`.`referral_sources` `rs` on((`pc`.`referral_source_id` = `rs`.`id`))) join `hilis4`.`referrer_department` `rd` on((`r1`.`referrer_department_id` = `rd`.`id`))) join `hilis4`.`referrers` `r2` on((`rd`.`referrer_id` = `r2`.`id`))) join `hilis4`.`hospital_departments` `hd` on((`rd`.`hospital_department_code` = `hd`.`id`))) join `hilis4`.`request_history` `rh` on((`rh`.`request_id` = `r1`.`id`))) join `hilis4`.`request_report_view` `rr` on((`rr`.`request_id` = `r1`.`id`))) join `hilis4`.`diagnoses` `d` on((`rr`.`diagnosis_id` = `d`.`id`))) where ((date_format(`rh`.`time`,_utf8'%Y-%m-%d') = curdate()) and (`rh`.`action` like _latin1'dispatched report%')) order by `rs`.`display_name`;


-- Dumping structure for view hilis4_views.no_report_table_entry
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `no_report_table_entry`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER VIEW `hilis4_views`.`no_report_table_entry` AS select `r`.`id` AS `id`,`r`.`request_number` AS `request_number`,`r`.`year` AS `year`,cast(`r`.`created_at` as date) AS `registered`,`s`.`description` AS `screened`,`ct`.`trial_name` AS `trial_name` from (((`hilis4`.`requests` `r` left join `hilis4`.`request_report_view` `rr` on((`rr`.`request_id` = `r`.`id`))) left join (`hilis4`.`request_trial` `rt` join `hilis4`.`clinical_trials` `ct` on((`rt`.`trial_id` = `ct`.`id`))) on((`rt`.`request_id` = `r`.`id`))) left join (`hilis4`.`request_initial_screen` `ris` join `hilis4`.`screens` `s` on((`ris`.`screen_id` = `s`.`id`))) on((`ris`.`request_id` = `r`.`id`))) where (isnull(`rr`.`request_id`) and (cast(`r`.`created_at` as date) < (curdate() - interval 7 day)));


-- Dumping structure for view hilis4_views.outreach_no_demographics
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `outreach_no_demographics`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER VIEW `hilis4_views`.`outreach_no_demographics` AS select `r`.`id` AS `id`,`r`.`request_number` AS `request_number`,`r`.`year` AS `year`,`r`.`patient_case_id` AS `patient_case_id`,`r`.`referrer_department_id` AS `referrer_department_id`,`r`.`status_option_id` AS `status_option_id`,`r`.`created_at` AS `created_at`,`r`.`updated_at` AS `updated_at` from (((((`hilis4`.`requests` `r` join `hilis4`.`patient_case` `pc` on((`r`.`patient_case_id` = `pc`.`id`))) join `hilis4`.`patients` `p` on((`pc`.`patient_id` = `p`.`id`))) left join `hilis4`.`patient_demographics` `pd` on((`pd`.`patient_id` = `p`.`id`))) join `hilis4`.`request_initial_screen` `ris` on((`ris`.`request_id` = `r`.`id`))) join `hilis4`.`screens` `s` on((`ris`.`screen_id` = `s`.`id`))) where ((`s`.`description` in (_latin1'community monitoring',_latin1'outreach')) and isnull(`pd`.`patient_id`));


-- Dumping structure for view hilis4_views.outstanding_lab_tests
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `outstanding_lab_tests`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER VIEW `hilis4_views`.`outstanding_lab_tests` AS select `t3`.`section_name` AS `section_name`,`t2`.`test_name` AS `test_name`,count(0) AS `f` from ((`hilis4`.`request_lab_test_status` `t1` join `hilis4`.`lab_tests` `t2` on((`t1`.`lab_test_id` = `t2`.`id`))) join `hilis4`.`lab_sections` `t3` on((`t2`.`lab_section_id` = `t3`.`id`))) where (`t1`.`status_option_id` <> 2) group by `t1`.`lab_test_id` order by `t2`.`test_name`;


-- Dumping structure for view hilis4_views.panel_tests
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `panel_tests`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER VIEW `hilis4_views`.`panel_tests` AS select `t4`.`section_name` AS `section_name`,`t2`.`field_label` AS `panel`,`t3`.`field_label` AS `test` from (((`hilis4`.`panel_lab_test` `t1` join `hilis4`.`lab_tests` `t2` on((`t1`.`panel_test_id` = `t2`.`id`))) join `hilis4`.`lab_tests` `t3` on((`t1`.`lab_test_id` = `t3`.`id`))) join `hilis4`.`lab_sections` `t4` on((`t2`.`lab_section_id` = `t4`.`id`))) order by `t2`.`field_label`;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;