CREATE DATABASE `hospital` ;

use hospital;
CREATE TABLE IF NOT EXISTS `users` (
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(500) NOT NULL,
    `enabled` TINYINT(1) NOT NULL,
    PRIMARY KEY (`username`)
)  DEFAULT CHARSET=UTF8 COMMENT='用户账户';
CREATE TABLE IF NOT EXISTS `authorities` (
    `username` VARCHAR(50) NOT NULL,
    `authority` VARCHAR(50) NOT NULL,
    `id` INT(8) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`),
    UNIQUE KEY `ix_auth_username` (`username` , `authority`)
)  AUTO_INCREMENT=5 DEFAULT CHARSET=UTF8 COMMENT='用户权限';
CREATE TABLE IF NOT EXISTS `annual_plan` (
    `planID` INT(8) NOT NULL AUTO_INCREMENT COMMENT '计划编号',
    `planName` VARCHAR(40) COMMENT '计划名称',
    `planMeasure` INT(8) COMMENT '计划适用年份',
    `transactionGoal` VARCHAR(1000) COMMENT '业务目标',
    `projectGoal` VARCHAR(1000) COMMENT '科研目标',
    `manageGoal` VARCHAR(1000) COMMENT '管理目标',
    `financeGoal` VARCHAR(1000) COMMENT '财务目标',
    `talentBuildGoal` VARCHAR(1000) COMMENT '人才建设目标',
    `hardwareBuildGoal` VARCHAR(1000) COMMENT '硬件建设目标',
    `cultureBuildGoal` VARCHAR(1000) COMMENT '文化建设目标',
    `addedContent` VARCHAR(1000) COMMENT '补充内容',
    PRIMARY KEY (`planID`)
) DEFAULT CHARSET=UTF8 COMMENT='医院年度战略计划表';

CREATE TABLE IF NOT EXISTS `department` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '科室编号',
  `departmentName` varchar(40) DEFAULT NULL COMMENT '科室名称',
  `departmentFunction` varchar(1000) DEFAULT NULL COMMENT '科室职能描述',
  `type` int(8) NOT NULL COMMENT '业务类别 1 业务 2 职能',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='科室表';
CREATE TABLE IF NOT EXISTS `position` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '职位表',
  `positionName` varchar(40) DEFAULT NULL COMMENT '职位名称',
  `positionDepartment` int(8) NOT NULL COMMENT '所属科室',
  `positionSalary` int(8) NOT NULL COMMENT '职位起薪',
  `positionFunction` varchar(1000) NOT NULL COMMENT '职位职能描述',
  `positionContent` varchar(1000) NOT NULL COMMENT '职位工作内容',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='职位表';
CREATE TABLE `employee` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '员工ID',
  `employeeName` varchar(40) NOT NULL COMMENT '员工姓名',
  `employeeGender` varchar(2) NOT NULL COMMENT '员工性别',
  `employeeDepartment` int(8) COMMENT '员工科室',
  `employeePosition` int(8) COMMENT '员工职位',
  `employeeSalary` double NOT NULL COMMENT '员工工资',
  `directLeader` int(8) COMMENT '直接领导',
  `workNo` varchar(50) NOT NULL COMMENT '员工工号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_workNo` (`workNo`)
) DEFAULT CHARSET=UTF8 COMMENT='业务科室目标';
CREATE TABLE `service_dept_plan` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '计划编号',
  `planName` varchar(40) NOT NULL COMMENT '计划名称',
  `planStartDate` date NOT NULL COMMENT '计划起始日期',
  `planStopDate` date NOT NULL COMMENT '计划结束日期',
  `transactionGoal` varchar(1000) COMMENT '业务目标',
  `projectGoal` varchar(1000) COMMENT '科研目标',
  `financeGoal` varchar(1000) COMMENT '财务目标',
  `talentBuildGoal` varchar(1000) COMMENT '人才建设目标',
  `hardwareBuildGoal` varchar(1000) COMMENT '硬件建设目标',
  `cultureBuildGoal` varchar(1000) COMMENT '文化建设目标',
  `addedContent` varchar(1000) COMMENT '补充内容',
  `departmentID` int(8) COMMENT '部门ID',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='业务科室目标';
CREATE TABLE `personal_plan` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '计划编号',
  `planName` varchar(40) NOT NULL COMMENT '计划名称',
  `planStartDate` date NOT NULL COMMENT '计划起始日期',
  `planStopDate` date NOT NULL COMMENT '计划结束日期',
  `employeeID` int(8) COMMENT '员工工号',
  `transactionGoal` varchar(1000) COMMENT '业务目标',
  `projectGoal` varchar(1000) COMMENT '科研目标',
  `addedContent` varchar(1000) COMMENT '补充内容',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='个人目标设定表';
CREATE TABLE `function_dept_plan` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '计划编号',
  `planName` varchar(40) NOT NULL COMMENT '计划名称',
  `planStartDate` date NOT NULL COMMENT '计划起始日期',
  `planStopDate` date NOT NULL COMMENT '计划结束日期',
  `manageGoal` varchar(1000) COMMENT '管理目标',
  `cultureBuildGoal` varchar(1000) COMMENT '文化建设目标',
  `addedContent` varchar(1000) COMMENT '补充内容',
   `departmentID` int(8) COMMENT '部门ID',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='职能科室目标设定表';
CREATE TABLE `review_reference` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '回顾资料编号',
  `referenceName` varchar(40) NOT NULL COMMENT '回顾资料名称',
  `referenceDate` date COMMENT '回顾资料日期',
  `referenceType` int(8) COMMENT '回顾资料类型',
  `referenceContent` varchar(1000) COMMENT '回顾资料内容',
   `employeeID` int(8) COMMENT '员工工号',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='日常业绩记录表';
CREATE TABLE `kpi_index` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '指标编号',
  `indexName` varchar(40) NOT NULL COMMENT '指标名称',
  `indexCategoryID` int NOT NULL COMMENT '指标类别编号',
  `indexCategoryName` varchar(40) NOT NULL COMMENT '指标类别名称',
  `indexComparativeWeight` int(8) DEFAULT NULL COMMENT '指标相对权重',
  `indexRemark` varchar(40) DEFAULT NULL COMMENT '指标备注',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='绩效评价指标表';
CREATE TABLE `work_report` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '述职报告编号',
  `reportName` varchar(40)  COMMENT '述职报告名称',
   `employeeID` int(8)  COMMENT '员工工号',
  `reportDate` int(8) NOT NULL COMMENT '述职报告日期',
  `reportContent` varchar(1000)  COMMENT '述职报告内容',   
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='述职报告表';
CREATE TABLE `conduct_method` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '实施方案编号',
  `conductName` varchar(40) NOT NULL COMMENT '实施方案名称',
  `rankMethodID` int(8) NOT NULL COMMENT '等级方案编号',
  `conductApplyDate` int(8) NOT NULL COMMENT '适用日期',
  `managerAssessmentRate` int(8) NOT NULL COMMENT '领导评价占比',
  `360AssessmentRate` int(8) NOT NULL COMMENT '领导评价占比',
  `addedContent` varchar(1000) NOT NULL COMMENT '补充内容',
  PRIMARY KEY (`id`)
)   DEFAULT CHARSET=UTF8 COMMENT='考核实施方案表';
CREATE TABLE `conduct_adopt_kpi` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `conduct_method_id` int(8) NOT NULL COMMENT '实施方案编号',
  `indexCategoryID` int(8) NOT NULL COMMENT '部门采用指标',
  `indexCategoryWeight` int(8) NOT NULL COMMENT '指标类别权重',
  `type` int(8) NOT NULL COMMENT '部门类型 1，业务 2，职能',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='考核实施方案采用绩效评价指标表';

CREATE TABLE `kpi_performance_assessment` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `performance_assessment_id` int(8) NOT NULL COMMENT '绩效评价指标支持业绩评价表ID',
  `indexID` int(8) NOT NULL COMMENT '指标编号',
  `indexScore` int(8) NOT NULL COMMENT '指标得分',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='绩效评价指标支持业绩评价表';

CREATE TABLE `performance_assessment` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `assessmentApplyDate` int(8) NOT NULL COMMENT '适用时间',
  `employeeID` int(8) NOT NULL COMMENT '员工工号',
  `directLeaderAssessmentContent` varchar(1000) COMMENT '直接领导评价内容',
  `scoreSource` int(8) NOT NULL COMMENT '评价类型1，领导 2，员工',
  `scoreEmployeeID` int(8) NOT NULL COMMENT '打分人工号',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='绩效评价指标支持业绩评价表';

CREATE TABLE `salary_modification_plan` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `employeeID` int(8) NOT NULL COMMENT '员工工号',
  `modificationApplyDate` int(8) COMMENT '薪资调整适用日期',
  `modificationPercent` int(8) NOT NULL COMMENT '薪资调整百分比,可正可负',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='绩效评价指标支持业绩评价表';


CREATE TABLE `task` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `employeeID` int(8) NOT NULL COMMENT '员工工号',
  `directorID` int(8) COMMENT '领导编号',
  `applyDate` int(8) NOT NULL COMMENT '适用季度',
  `state` int(0) NOT NULL COMMENT '状态 0 未处理 1 已处理',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=UTF8 COMMENT='待办任务';

grant select, insert, update, delete on *.* to 'hospital'  IDENTIFIED BY 'hospital' WITH GRANT OPTION;;

INSERT INTO `users` (`username`,`password`,`enabled`) VALUES ('010001','$2a$10$qN7V/CnZprfgS3CIlS4veOQ.UsiL/8lbpHq.xrGaj0CTNviDR2/bS',1);
INSERT INTO `users` (`username`,`password`,`enabled`) VALUES ('010002','$2a$10$yX3qPYKbBxwFj5NXqC5dYebLwaIhifGxurwEI3rp2PuywTXkc285W',1);
INSERT INTO `users` (`username`,`password`,`enabled`) VALUES ('010003','$2a$10$SF9mChWOFROeAJizJTHtpum1JuomKEg6oZ8eHk2camvcJFAFBmSOu',1);
INSERT INTO `users` (`username`,`password`,`enabled`) VALUES ('010004','$2a$10$DthQ4w9cDk0Wp3Ua.X8Ty.W4FjUfWzoJis2A/gD6fg9h5uRGreOLO',1);
INSERT INTO `users` (`username`,`password`,`enabled`) VALUES ('010005','$2a$10$P2FZtYWbH2XLWv1rU9aMVejGgSrGwRe0FqPZevOyqR1a9LwfJo3fa',1);
INSERT INTO `users` (`username`,`password`,`enabled`) VALUES ('010006','$2a$10$/S7Wbyims2/IM5eJGGxhdOygSs7xJG9iG6qiUdeFFoeDcDVrFYgoi',1);

INSERT INTO `authorities` (`username`,`authority`,`id`) VALUES ('010001','ROLE_R1',1);
INSERT INTO `authorities` (`username`,`authority`,`id`) VALUES ('010002','ROLE_R2',2);
INSERT INTO `authorities` (`username`,`authority`,`id`) VALUES ('010003','ROLE_R3',3);
INSERT INTO `authorities` (`username`,`authority`,`id`) VALUES ('010004','ROLE_R4',4);
INSERT INTO `authorities` (`username`,`authority`,`id`) VALUES ('010005','ROLE_R5',5);
INSERT INTO `authorities` (`username`,`authority`,`id`) VALUES ('010006','ROLE_R3',6);

INSERT INTO `employee` (`id`,`employeeName`,`employeeGender`,`employeeDepartment`,`employeePosition`,`employeeSalary`,`directLeader`,`workNo`) VALUES (1,'王宏','M',2,NULL,20000,NULL,'010001');
INSERT INTO `employee` (`id`,`employeeName`,`employeeGender`,`employeeDepartment`,`employeePosition`,`employeeSalary`,`directLeader`,`workNo`) VALUES (2,'张丽','F',3,NULL,5000,NULL,'010002');
INSERT INTO `employee` (`id`,`employeeName`,`employeeGender`,`employeeDepartment`,`employeePosition`,`employeeSalary`,`directLeader`,`workNo`) VALUES (3,'陈科','M',1,NULL,10000,1,'010003');
INSERT INTO `employee` (`id`,`employeeName`,`employeeGender`,`employeeDepartment`,`employeePosition`,`employeeSalary`,`directLeader`,`workNo`) VALUES (4,'钟晓霞','F',1,NULL,8000,3,'010004');
INSERT INTO `employee` (`id`,`employeeName`,`employeeGender`,`employeeDepartment`,`employeePosition`,`employeeSalary`,`directLeader`,`workNo`) VALUES (5,'刘娜','F',1,NULL,5000,4,'010005');
INSERT INTO `employee` (`id`,`employeeName`,`employeeGender`,`employeeDepartment`,`employeePosition`,`employeeSalary`,`directLeader`,`workNo`) VALUES (6,'张风','M',4,NULL,9000,4,'010006');

INSERT INTO `department` (`id`,`departmentName`,`departmentFunction`,`type`) VALUES (1,'急诊科','负责急诊看病',1);
INSERT INTO `department` (`id`,`departmentName`,`departmentFunction`,`type`) VALUES (2,'院长办公室','日常管理',3);
INSERT INTO `department` (`id`,`departmentName`,`departmentFunction`,`type`) VALUES (3,'人事部','人事管理',2);
INSERT INTO `department` (`id`,`departmentName`,`departmentFunction`,`type`) VALUES (4,'后勤部','后勤管理',2);

INSERT INTO `hospital`.`kpi_index` (`id`, `indexName`, `indexCategoryID`, `indexCategoryName`, `indexComparativeWeight`, `indexRemark`) VALUES ('1', '工作表现', '1', '日常', '30', '备注');