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

/*!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' */;

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               5.5.41-0+wheezy1 - (Debian)
-- Server OS:                    debian-linux-gnu
-- HeidiSQL Version:             9.1.0.4928
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!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 structure for view views.clinical_trials
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `clinical_trials` (
	`trial_name` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci',
	`active` ENUM('yes','no') NOT NULL COLLATE 'latin1_swedish_ci',
	`f` BIGINT(21) NOT NULL
) ENGINE=MyISAM;


-- Dumping structure for view 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 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 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 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 views.mailed_reports_today
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `mailed_reports_today` (
	`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 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 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 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 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 views.clinical_trials
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `clinical_trials`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
    VIEW `clinical_trials` AS select `ct`.`trial_name` AS `trial_name`,
    `ct`.`active` AS `active`,count(`rt`.`request_id`) AS `f`
    from (`hilis4`.`clinical_trials` `ct` left join `hilis4`.`request_trial`
    `rt` on((`rt`.`trial_id` = `ct`.`id`))) group by `ct`.`id`
    order by `ct`.`trial_name`;
/*!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 */;


-- Dumping structure for view 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_sub_category` `ic` on((`d`.`icdo3` = `ic`.`icdo3`)))
        join `hilis4`.`diagnostic_categories` `dc` on((`ic`.`diagnostic_category_id` = `dc`.`id`)))
    where (`d`.`active` = _latin1'yes') order by `d`.`name`;


-- Dumping structure for view 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 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 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 views.mailed_reports_today
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `mailed_reports_today`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
    VIEW `hilis4_views`.`mailed_reports_today` AS
    select `t1`.`request_number` AS `request_number`,`t1`.`year` AS `year`,
        `t1`.`diagnosis` AS `diagnosis`, `t1`.`status` AS `status`,
        `t1`.`location` AS `location`,`t4`.`name` AS `referrer`,
        `t5`.`display_name` AS `department`
    from (((((`hilis4`.`authorised_reports_view` `t1`
        join `hilis4`.`requests` `t2` on((`t1`.`id` = `t2`.`id`)))
        join `hilis4`.`referrer_department` `t3` on((`t2`.`referrer_department_id` = `t3`.`id`)))
        join `hilis4`.`referrers` `t4` on((`t3`.`referrer_id` = `t4`.`id`)))
        join `hilis4`.`hospital_departments` `t5` on((`t3`.`hospital_department_code` = `t5`.`id`)))
        join `hilis4`.`request_history` `t6` on(((`t6`.`request_id` = `t1`.`id`)
            and (`t6`.`action` like _latin1'dispatched report%'))))
    where
        /* (cast(`t6`.`time` as date) = curdate()) # way slower than: */
        `t6`.`time` >= curdate()
    order by `t1`.`location`;


-- Dumping structure for view 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 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 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 `t2`.`field_label` AS `name`,`t3`.`section_name` AS `section`,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 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`;

-- Dumping structure for view views.outreach_patients
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `outreach_patients` (
	`last_name` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci',
	`first_name` VARCHAR(50) NULL COLLATE 'latin1_swedish_ci',
	`dob` DATE NULL,
	`nhs_number` VARCHAR(10) NULL COLLATE 'latin1_swedish_ci',
	`status` ENUM('alive','dead') NOT NULL COLLATE 'latin1_swedish_ci',
	`GP` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
	`practice` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
	`organisation_code` VARCHAR(6) NOT NULL COLLATE 'latin1_swedish_ci',
	`address` VARCHAR(255) NULL COLLATE 'latin1_swedish_ci',
	`post_code` VARCHAR(8) NULL COLLATE 'latin1_swedish_ci'
) ENGINE=MyISAM;

-- Dumping structure for view views.outreach_patients
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `outreach_patients`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
    VIEW `hilis4_views`.`outreach_patients` AS
    select `p`.`id` as `patient_id`, UPPER(`p`.`last_name`), `p`.`first_name`,
        `p`.`dob`, `p`.`nhs_number`, pd.`status`, `r`.`name` AS `GP`,
        `rs`.`display_name` AS `practice`, `rs`.`organisation_code`, pd.address,
        pd.post_code
    from ((((`outreach`.`patient_dispatch_detail` `pdd`
        join `hilis4`.`patients` `p` on((`pdd`.`patient_id` = `p`.`id`)))
        join `hilis4`.`patient_demographics` `pd` on((`pd`.`patient_id` = `p`.`id`)))
        join `hilis4`.`referral_sources` `rs` on((`pd`.`practice_id` = `rs`.`id`)))
        join `hilis4`.`referrers` `r` on((`pd`.`gp_id` = `r`.`id`)))
    order by `p`.`last_name`,`p`.`first_name`;

-- Dumping structure for view views.request_forms
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `request_forms` (
	`last_name` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
	`first_name` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
	`nhs_number` VARCHAR(10) NOT NULL COLLATE 'latin1_swedish_ci',
	`location_name` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
	`referrer` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
	`user` VARCHAR(101) NOT NULL COLLATE 'latin1_swedish_ci',
	`created` TIMESTAMP NOT NULL,
	`imported` DATETIME NULL
) ENGINE=MyISAM;


-- Dumping structure for view views.request_forms
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `request_forms`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
    VIEW `request_forms` AS
    select `rf`.`last_name` AS `last_name`,
        `rf`.`first_name` AS `first_name`,`rf`.`nhs_number` AS `nhs_number`,
        `rf`.`location_name` AS `location_name`,`rf`.`referrer` AS `referrer`,
        concat(`u`.`first_name`,' ',`u`.`last_name`) AS `user`,`rf`.`created`
        AS `created`,`rf`.`imported` AS `imported`
    from (`hilis4`.`request_form` `rf` join `hilis4`.`users` `u`
        on((`rf`.`user_id` = `u`.`id`)));

-- Dumping structure for view views.hmrn_permissions
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `hmrn_permissions` (
	`username` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
	`location_name` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
	`functions` TEXT NULL COLLATE 'latin1_swedish_ci'
) ENGINE=MyISAM;


-- Dumping structure for view views.hmrn_permissions
-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `hmrn_permissions`;
CREATE ALGORITHM=UNDEFINED DEFINER=`raj`@`%` SQL SECURITY DEFINER
    VIEW `hmrn_permissions` AS
    select `u`.`username` AS `username`,`ul`.`location_name` AS `location_name`,
    group_concat(uf.function_name order by uf.function_name) as functions
    from (((`hilis4`.`users` `u` join `hilis4`.`user_locations` `ul`
            on((`u`.`user_location_id` = `ul`.`id`)))
        join `hilis4`.`user_permission` `up` on((`up`.`user_id` = `u`.`id`)))
        join `hilis4`.`user_functions` `uf` on((`up`.`function_id` = `uf`.`id`)))
    where ((`uf`.`function_name` regexp 'hmrn|clinical') and (`u`.`active` = 'yes'))
    group by `u`.`id` order by `u`.`username`,`uf`.`function_name`;


/*!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 */;