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