ALTER TABLE mortals_xhx_staff ADD COLUMN `score` decimal(10,2) default 0.00 COMMENT '绩效分数' AFTER serverIndexCode; ALTER TABLE mortals_xhx_dept ADD COLUMN `score` decimal(10,2) default 0.00 COMMENT '绩效分数' AFTER updateTime; -- ---------------------------- 2023-7-05 -- ---------------------------- -- ---------------------------- -- 绩效记录申诉信息表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_perform_attend_appeal`; CREATE TABLE mortals_xhx_perform_attend_appeal( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `checkRecordId` bigint(20) COMMENT '核查记录Id', `staffId` bigint(20) COMMENT '员工ID', `staffName` varchar(64) NOT NULL COMMENT '员工姓名', `workNum` varchar(128) COMMENT '工号', `deptId` bigint(20) COMMENT '所属部门', `deptName` varchar(128) COMMENT '所属部门名称', `attendanceGroupId` bigint(20) COMMENT '所属考勤组ID', `attendanceGroupName` varchar(128) COMMENT '所属考勤组名称', `attendanceDate` datetime COMMENT '扣分时间', `ruleId` bigint(20) COMMENT '绩效规则id', `ruleName` varchar(128) COMMENT '规则名称', `subMethod` tinyint(1) DEFAULT '1' COMMENT '扣分方式(1.系统自动,2.人工添加,3.大厅巡查)', `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)', `score` decimal(10,2) DEFAULT '0.00' COMMENT '扣分或增加分值', `goOffTimeStr` varchar(128) COMMENT '上下班时间', `errorTime` datetime COMMENT '异常时间', `actualAttendTime` datetime COMMENT '实际打卡时间', `errorResult` varchar(128) COMMENT '异常处理结果', `checkPerson` varchar(128) COMMENT '核查人员', `checkTime` datetime COMMENT '核查时间', `checkDesc` varchar(512) COMMENT '核查说明', `checkResult` varchar(128) COMMENT '核查结果', `processStatus` tinyint(1) DEFAULT '1' COMMENT '处理状态(1.未处理,2.已处理)', `appealDesc` varchar(128) COMMENT '申诉说明', `appealTime` datetime COMMENT '申诉时间', `appealResult` tinyint(1) DEFAULT '2' COMMENT '申诉结果(1.通过,2.不通过)', `remark` varchar(512) COMMENT '说明', `createUserId` bigint(20) NOT NULL COMMENT '创建用户', `createTime` datetime NOT NULL COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', `violationType` varchar(64) COMMENT '违规类型', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效记录申诉信息'; -- ---------------------------- -- 绩效记录申诉附件信息表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_perform_attend_appeal_files`; CREATE TABLE mortals_xhx_perform_attend_appeal_files( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `appealId` bigint(20) COMMENT '申诉Id', `fileName` varchar(64) NOT NULL COMMENT '文件名称', `filePath` varchar(128) COMMENT '文件地址', `createUserId` bigint(20) NOT NULL COMMENT '创建用户', `createTime` datetime NOT NULL COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效记录申诉附件信息'; -- ---------------------------- -- 绩效反馈记录信息表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_feedback`; CREATE TABLE mortals_xhx_feedback( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `title` varchar(64) NOT NULL COMMENT '问卷标题', `feedbackTimeStart` datetime COMMENT '反馈开始时间', `feedbackTimeEnd` datetime COMMENT '反馈结束时间', `inviteNum` int(4) DEFAULT '0' COMMENT '邀请人数', `feedbackNum` int(4) DEFAULT '0' COMMENT '反馈人数', `processStatus` tinyint(2) DEFAULT '0' COMMENT '状态 (0.未开始,1.进行中,2.已结束)', `weblink` tinyint(2) DEFAULT '0' COMMENT '问卷是否外链(0.否,1.是)', `webUrl` varchar(255) COMMENT '问题外链地址', `formContent` mediumtext COMMENT '表单内容', `remark` varchar(255) COMMENT '备注说明', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效反馈记录信息'; -- ---------------------------- -- 绩效反馈问题表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_feedback_question`; CREATE TABLE mortals_xhx_feedback_question( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `feedbackId` bigint(20) DEFAULT '0' COMMENT '反馈问卷id号', `content` varchar(64) NOT NULL COMMENT '问题内容', `questionType` tinyint(2) DEFAULT '1' COMMENT '问题类型 (1.单项选择,2.多项选择,3.文本框,4.多项文本框,5.文字,6.上传附件)', `require` tinyint(2) DEFAULT '0' COMMENT '是否必填(0.否,1.是)', `style` varchar(255) COMMENT '样式', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效反馈问题'; -- ---------------------------- -- 绩效问题选项表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_feedback_option`; CREATE TABLE mortals_xhx_feedback_option( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `questionId` bigint(20) DEFAULT '0' COMMENT '问题id号', `content` varchar(64) NOT NULL COMMENT '选项内容', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效问题选项'; -- ---------------------------- -- 绩效问题回答表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_feedback_answer`; CREATE TABLE mortals_xhx_feedback_answer( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `feedbackId` bigint(20) DEFAULT '0' COMMENT '反馈问卷id号', `questionId` bigint(20) DEFAULT '0' COMMENT '问题id号', `staffId` bigint(20) DEFAULT '0' COMMENT '用户id号', `questionType` tinyint(2) DEFAULT '1' COMMENT '问题类型 (1.单项选择,2.多项选择,3.文本框,4.多项文本框,5.文字,6.上传附件)', `content` varchar(64) NOT NULL COMMENT '适用于问题内容为文本内容', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效问题回答'; -- ---------------------------- -- 绩效问题回答选项表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_feedback_answer_option`; CREATE TABLE mortals_xhx_feedback_answer_option( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `answerId` bigint(20) DEFAULT '0' COMMENT '回答问题id号', `optionId` bigint(20) DEFAULT '0' COMMENT '选项id号', `optionContent` varchar(64) NOT NULL COMMENT '选项内容', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效问题回答选项'; -- ---------------------------- -- 员工绩效统计表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_staff_perform_summary`; CREATE TABLE mortals_xhx_staff_perform_summary( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID,主键,自增长', `staffId` bigint(20) DEFAULT '0' COMMENT '人员id', `workNum` varchar(64) COMMENT '工号', `staffName` varchar(512) COMMENT '人员名称', `deptId` bigint(20) DEFAULT '0' COMMENT '部门id号', `deptName` varchar(64) COMMENT '部门名称', `attendScore` decimal(10,2) COMMENT '考勤绩效指标分数', `assessScore` decimal(10,2) COMMENT '评价绩效指标分数', `workScore` decimal(10,2) COMMENT '办件绩效分数', `effectScore` decimal(10,2) COMMENT '效能绩效分数', `otherScore` decimal(10,2) COMMENT '其它绩效分数', `errorScore` decimal(10,2) COMMENT '累计异常分数', `totalScore` decimal(10,2) COMMENT '当月得分数', `remark` varchar(255) COMMENT '备注', `year` int(9) NOT NULL COMMENT '年', `month` tinyint(2) NOT NULL COMMENT '月', `createTime` datetime COMMENT '创建时间', `createUserId` bigint(20) COMMENT '创建人id', `updateTime` datetime COMMENT '更新时间', `updateUserId` bigint(20) COMMENT '更新人id', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工绩效统计'; -- ---------------------------- -- 绩效记录申诉信息资源路径 SQL -- ---------------------------- INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效记录申诉信息-菜单管理-查看', '/perform/attend/appeal/list,/perform/attend/appeal/view,/perform/attend/appeal/info,/perform/attend/appeal/export,/perform/attend/appeal/exportExcel,/perform/attend/appeal/downloadTemplate,/perform/attend/appeal/download', 3, 0, NULL, NULL, NULL, 0); INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效记录申诉信息-菜单管理-维护', '/perform/attend/appeal/add,/perform/attend/appeal/edit,/perform/attend/appeal/delete,/perform/attend/appeal/logicDelete,/perform/attend/appeal/save,/perform/attend/appeal/importData', 3, 0, NULL, NULL, NULL, 0); -- ---------------------------- -- 绩效记录申诉信息参数 SQL -- ---------------------------- INSERT INTO `mortals_xhx_param` VALUES (null, '扣分方式', 'PerformAttendAppeal', 'subMethod', '1', '系统自动', 1, 4, 0, 'subMethod', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '扣分方式', 'PerformAttendAppeal', 'subMethod', '2', '人工添加', 1, 4, 0, 'subMethod', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '增减类型', 'PerformAttendAppeal', 'subAddType', '1', '增加', 1, 4, 0, 'subAddType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '增减类型', 'PerformAttendAppeal', 'subAddType', '2', '扣除', 1, 4, 0, 'subAddType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '处理状态', 'PerformAttendAppeal', 'processStatus', '1', '未处理', 1, 4, 0, 'processStatus', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '处理状态', 'PerformAttendAppeal', 'processStatus', '2', '已处理', 1, 4, 0, 'processStatus', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '申诉结果', 'PerformAttendAppeal', 'appealResult', '1', '通过', 1, 4, 0, 'appealResult', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '申诉结果', 'PerformAttendAppeal', 'appealResult', '2', '不通过', 1, 4, 0, 'appealResult', NULL, NULL, NULL); -- ---------------------------- -- 绩效记录申诉附件信息资源路径 SQL -- ---------------------------- INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效记录申诉附件信息-菜单管理-查看', '/perform/attend/appeal/files/list,/perform/attend/appeal/files/view,/perform/attend/appeal/files/info,/perform/attend/appeal/files/export,/perform/attend/appeal/files/exportExcel,/perform/attend/appeal/files/downloadTemplate,/perform/attend/appeal/files/download', 3, 0, NULL, NULL, NULL, 0); INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效记录申诉附件信息-菜单管理-维护', '/perform/attend/appeal/files/add,/perform/attend/appeal/files/edit,/perform/attend/appeal/files/delete,/perform/attend/appeal/files/logicDelete,/perform/attend/appeal/files/save,/perform/attend/appeal/files/importData', 3, 0, NULL, NULL, NULL, 0); -- ---------------------------- -- 绩效反馈记录信息资源路径 SQL -- ---------------------------- INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效反馈记录信息-菜单管理-查看', '/feedback/list,/feedback/view,/feedback/info,/feedback/export,/feedback/exportExcel,/feedback/downloadTemplate,/feedback/download', 3, 0, NULL, NULL, NULL, 0); INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效反馈记录信息-菜单管理-维护', '/feedback/add,/feedback/edit,/feedback/delete,/feedback/logicDelete,/feedback/save,/feedback/importData', 3, 0, NULL, NULL, NULL, 0); -- ---------------------------- -- 绩效反馈记录信息参数 SQL -- ---------------------------- INSERT INTO `mortals_xhx_param` VALUES (null, '状态 ', 'Feedback', 'processStatus', '0', '未开始', 1, 4, 0, 'processStatus', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '状态 ', 'Feedback', 'processStatus', '1', '进行中', 1, 4, 0, 'processStatus', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '状态 ', 'Feedback', 'processStatus', '2', '已结束', 1, 4, 0, 'processStatus', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '问卷是否外链', 'Feedback', 'weblink', '0', '否', 1, 4, 0, 'weblink', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '问卷是否外链', 'Feedback', 'weblink', '1', '是', 1, 4, 0, 'weblink', NULL, NULL, NULL); -- ---------------------------- -- 绩效反馈问题资源路径 SQL -- ---------------------------- INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效反馈问题-菜单管理-查看', '/feedback/question/list,/feedback/question/view,/feedback/question/info,/feedback/question/export,/feedback/question/exportExcel,/feedback/question/downloadTemplate,/feedback/question/download', 3, 0, NULL, NULL, NULL, 0); INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效反馈问题-菜单管理-维护', '/feedback/question/add,/feedback/question/edit,/feedback/question/delete,/feedback/question/logicDelete,/feedback/question/save,/feedback/question/importData', 3, 0, NULL, NULL, NULL, 0); -- ---------------------------- -- 绩效反馈问题参数 SQL -- ---------------------------- INSERT INTO `mortals_xhx_param` VALUES (null, '问题类型 ', 'FeedbackQuestion', 'questionType', '1', '单项选择', 1, 4, 0, 'questionType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '问题类型 ', 'FeedbackQuestion', 'questionType', '2', '多项选择', 1, 4, 0, 'questionType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '问题类型 ', 'FeedbackQuestion', 'questionType', '3', '文本框', 1, 4, 0, 'questionType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '问题类型 ', 'FeedbackQuestion', 'questionType', '4', '多项文本框', 1, 4, 0, 'questionType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '问题类型 ', 'FeedbackQuestion', 'questionType', '5', '文字', 1, 4, 0, 'questionType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '问题类型 ', 'FeedbackQuestion', 'questionType', '6', '上传附件', 1, 4, 0, 'questionType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '是否必填', 'FeedbackQuestion', 'require', '0', '否', 1, 4, 0, 'require', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '是否必填', 'FeedbackQuestion', 'require', '1', '是', 1, 4, 0, 'require', NULL, NULL, NULL); -- ---------------------------- -- 绩效问题选项资源路径 SQL -- ---------------------------- INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效问题选项-菜单管理-查看', '/feedback/option/list,/feedback/option/view,/feedback/option/info,/feedback/option/export,/feedback/option/exportExcel,/feedback/option/downloadTemplate,/feedback/option/download', 3, 0, NULL, NULL, NULL, 0); INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效问题选项-菜单管理-维护', '/feedback/option/add,/feedback/option/edit,/feedback/option/delete,/feedback/option/logicDelete,/feedback/option/save,/feedback/option/importData', 3, 0, NULL, NULL, NULL, 0); -- ---------------------------- -- 绩效问题回答资源路径 SQL -- ---------------------------- INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效问题回答-菜单管理-查看', '/feedback/answer/list,/feedback/answer/view,/feedback/answer/info,/feedback/answer/export,/feedback/answer/exportExcel,/feedback/answer/downloadTemplate,/feedback/answer/download', 3, 0, NULL, NULL, NULL, 0); INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效问题回答-菜单管理-维护', '/feedback/answer/add,/feedback/answer/edit,/feedback/answer/delete,/feedback/answer/logicDelete,/feedback/answer/save,/feedback/answer/importData', 3, 0, NULL, NULL, NULL, 0); -- ---------------------------- -- 绩效问题回答选项资源路径 SQL -- ---------------------------- INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效问题回答选项-菜单管理-查看', '/feedback/answer/option/list,/feedback/answer/option/view,/feedback/answer/option/info,/feedback/answer/option/export,/feedback/answer/option/exportExcel,/feedback/answer/option/downloadTemplate,/feedback/answer/option/download', 3, 0, NULL, NULL, NULL, 0); INSERT INTO `mortals_xhx_resource` VALUES (null, '绩效问题回答选项-菜单管理-维护', '/feedback/answer/option/add,/feedback/answer/option/edit,/feedback/answer/option/delete,/feedback/answer/option/logicDelete,/feedback/answer/option/save,/feedback/answer/option/importData', 3, 0, NULL, NULL, NULL, 0); -- ---------------------------- -- 员工绩效统计资源路径 SQL -- ---------------------------- INSERT INTO `mortals_xhx_resource` VALUES (null, '员工绩效统计-菜单管理-查看', '/staff/perform/summary/list,/staff/perform/summary/view,/staff/perform/summary/info,/staff/perform/summary/export,/staff/perform/summary/exportExcel,/staff/perform/summary/downloadTemplate,/staff/perform/summary/download', 3, 0, NULL, NULL, NULL, 0); INSERT INTO `mortals_xhx_resource` VALUES (null, '员工绩效统计-菜单管理-维护', '/staff/perform/summary/add,/staff/perform/summary/edit,/staff/perform/summary/delete,/staff/perform/summary/logicDelete,/staff/perform/summary/save,/staff/perform/summary/importData', 3, 0, NULL, NULL, NULL, 0); -- ---------------------------- -- 员工反馈问卷表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_feedback_staff`; CREATE TABLE mortals_xhx_feedback_staff( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `feedbackId` bigint(20) DEFAULT '0' COMMENT '反馈问卷id号', `staffId` bigint(20) DEFAULT '0' COMMENT '用户id', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工反馈问卷'; -- ---------------------------- -- 员工反馈问卷资源路径 SQL -- ---------------------------- INSERT INTO `mortals_xhx_resource` VALUES (null, '员工反馈问卷-菜单管理-查看', '/feedback/staff/list,/feedback/staff/view,/feedback/staff/info,/feedback/staff/export,/feedback/staff/exportExcel,/feedback/staff/downloadTemplate,/feedback/staff/download', 3, 0, NULL, NULL, NULL, 0); INSERT INTO `mortals_xhx_resource` VALUES (null, '员工反馈问卷-菜单管理-维护', '/feedback/staff/add,/feedback/staff/edit,/feedback/staff/delete,/feedback/staff/logicDelete,/feedback/staff/save,/feedback/staff/importData', 3, 0, NULL, NULL, NULL, 0); -- ---------------------------- 2023-7-08 -- ---------------------------- ALTER TABLE mortals_xhx_user ADD COLUMN `customerId` bigint(20) DEFAULT -1 COMMENT '员工id' AFTER status; ALTER TABLE mortals_xhx_perform_attend_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_perform_attend_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_check_attend_record ADD COLUMN `remark` varchar(256) DEFAULT '' COMMENT '备注' AFTER checkStatus; ALTER TABLE mortals_xhx_check_attend_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_check_attend_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_perform_review_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_perform_review_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_check_review_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_check_review_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_perform_complain_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_perform_complain_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_check_complain_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_check_complain_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_perform_gowork_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_perform_gowork_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_check_gowork_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_check_gowork_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_perform_effect_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_perform_effect_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_check_effect_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_check_effect_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_perform_other_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_perform_other_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; ALTER TABLE mortals_xhx_check_other_record ADD COLUMN `fileNames` varchar(256) DEFAULT '' COMMENT '附件名称,多个逗号分割' AFTER remark; ALTER TABLE mortals_xhx_check_other_record ADD COLUMN `filePaths` varchar(256) DEFAULT '' COMMENT '附件下载地址,多个逗号分割' AFTER fileNames; -- ---------------------------- 2023-7-09 -- ---------------------------- ALTER TABLE mortals_xhx_feedback ADD COLUMN `ruleContent` varchar(1024) DEFAULT '' COMMENT '规则内容' AFTER formContent; ALTER TABLE mortals_xhx_perform_attend_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_perform_attend_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_check_attend_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_check_attend_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_perform_review_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_perform_review_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_check_review_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_check_review_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_perform_complain_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_perform_complain_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_check_complain_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_check_complain_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_perform_gowork_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_perform_gowork_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_check_gowork_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_check_gowork_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_perform_effect_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_perform_effect_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_check_effect_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_check_effect_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_perform_other_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_perform_other_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_check_other_record ADD COLUMN `categoryId` bigint(20) COMMENT '绩效规则分类id' AFTER filePaths; ALTER TABLE mortals_xhx_check_other_record ADD COLUMN `categoryName` varchar(128) COMMENT '规则名称' AFTER categoryId; ALTER TABLE mortals_xhx_perform_rules ADD COLUMN `ruleCode` varchar(64) COMMENT '规则编码,唯一,默认规则类型内容简称拼音首字母。'AFTER id; -- ---------------------------- 2023-7-10 -- ---------------------------- ALTER TABLE mortals_xhx_perform_review_record ADD COLUMN `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)' AFTER deductTime; ALTER TABLE mortals_xhx_check_review_record ADD COLUMN `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)' AFTER deductTime; ALTER TABLE mortals_xhx_perform_complain_record ADD COLUMN `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)' AFTER deductTime; ALTER TABLE mortals_xhx_check_complain_record ADD COLUMN `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)' AFTER deductTime; ALTER TABLE mortals_xhx_perform_gowork_record ADD COLUMN `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)' AFTER deductTime; ALTER TABLE mortals_xhx_check_gowork_record ADD COLUMN `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)' AFTER deductTime; ALTER TABLE mortals_xhx_perform_effect_record ADD COLUMN `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)' AFTER deductTime; ALTER TABLE mortals_xhx_check_effect_record ADD COLUMN `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)' AFTER deductTime; ALTER TABLE mortals_xhx_perform_other_record ADD COLUMN `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)' AFTER deductTime; ALTER TABLE mortals_xhx_check_other_record ADD COLUMN `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)' AFTER deductTime; -- ---------------------------- 2023-7-10 2 -- ---------------------------- ALTER TABLE mortals_xhx_dept_perform_stat ADD COLUMN `totalAddScore` decimal(10,2) DEFAULT 0.0 COMMENT '部门绩效加分总分数汇总' AFTER otherScoreSub; ALTER TABLE mortals_xhx_dept_perform_stat ADD COLUMN `totalSubScore` decimal(10,2) DEFAULT 0.0 COMMENT '部门绩效减分总分数汇总' AFTER totalAddScore; ALTER TABLE mortals_xhx_dept_perform_stat ADD COLUMN `complainScoreAdd` decimal(10,2) DEFAULT 0.0 COMMENT '投诉绩效指标增加分数' AFTER totalSubScore; ALTER TABLE mortals_xhx_dept_perform_stat ADD COLUMN `complainScoreSub` decimal(10,2) DEFAULT 0.0 COMMENT '投诉绩效指标扣减分数' AFTER complainScoreAdd; ALTER TABLE mortals_xhx_dept_perform_stat ADD COLUMN `averageScore` decimal(10,2) DEFAULT 0.0 COMMENT '部门绩效平均分数,根据部门所属人数平均' AFTER complainScoreSub; ALTER TABLE mortals_xhx_staff_perform_stat ADD COLUMN `totalAddScore` decimal(10,2) DEFAULT 0.0 COMMENT '员工绩效加分总分数汇总' AFTER otherScoreSub; ALTER TABLE mortals_xhx_staff_perform_stat ADD COLUMN `totalSubScore` decimal(10,2) DEFAULT 0.0 COMMENT '员工绩效减分总分数汇总' AFTER totalAddScore; ALTER TABLE mortals_xhx_staff_perform_stat ADD COLUMN `complainScoreAdd` decimal(10,2) DEFAULT 0.0 COMMENT '员工绩效指标增加分数' AFTER totalSubScore; ALTER TABLE mortals_xhx_staff_perform_stat ADD COLUMN `complainScoreSub` decimal(10,2) DEFAULT 0.0 COMMENT '员工绩效指标扣减分数' AFTER complainScoreAdd; -- ---------------------------- -- 绩效记录申诉信息表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_perform_attend_appeal`; CREATE TABLE mortals_xhx_perform_attend_appeal( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `checkRecordId` bigint(20) COMMENT '核查记录Id', `staffId` bigint(20) COMMENT '员工ID', `staffName` varchar(64) NOT NULL COMMENT '员工姓名', `workNum` varchar(128) COMMENT '工号', `deptId` bigint(20) COMMENT '所属部门', `deptName` varchar(128) COMMENT '所属部门名称', `attendanceGroupId` bigint(20) COMMENT '所属考勤组ID', `attendanceGroupName` varchar(128) COMMENT '所属考勤组名称', `attendanceDate` datetime COMMENT '扣分时间', `ruleId` bigint(20) COMMENT '绩效规则id', `ruleName` varchar(128) COMMENT '规则名称', `subMethod` tinyint(1) DEFAULT '1' COMMENT '扣分方式(1.系统自动,2.人工添加,3.大厅巡查)', `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)', `score` decimal(10,2) DEFAULT '0.00' COMMENT '扣分或增加分值', `goOffTimeStr` varchar(128) COMMENT '上下班时间', `errorTime` datetime COMMENT '异常时间', `actualAttendTime` datetime COMMENT '实际打卡时间', `errorResult` varchar(128) COMMENT '异常处理结果', `checkPerson` varchar(128) COMMENT '核查人员', `checkTime` datetime COMMENT '核查时间', `checkDesc` varchar(512) COMMENT '核查说明', `checkResult` varchar(128) COMMENT '核查结果', `processStatus` tinyint(1) DEFAULT '1' COMMENT '处理状态(1.未处理,2.已处理)', `appealDesc` varchar(128) COMMENT '申诉说明', `appealTime` datetime COMMENT '申诉时间', `appealResult` tinyint(1) DEFAULT '2' COMMENT '申诉结果(1.通过,2.不通过)', `remark` varchar(512) COMMENT '说明', `violationType` varchar(64) COMMENT '违规类型,', `reviewResult` tinyint(1) COMMENT '评价结果(1.非常不满意,2.差评)', `reviewTime` datetime COMMENT '评价时间', `reviewSource` varchar(64) COMMENT '评价来源(窗口评价系统,导视系统,自助服务系统,微官网,其它)', `reviewDevice` varchar(128) COMMENT '评价设备', `complainTitle` varchar(128) COMMENT '投诉标题', `complainContent` varchar(512) COMMENT '投诉内容', `complainRealName` varchar(128) COMMENT '投诉人真实姓名', `contact` varchar(128) COMMENT '联系电话', `complainTime` datetime COMMENT '投诉时间', `complainSource` varchar(128) COMMENT '投诉来源', `complainDevice` varchar(128) COMMENT '投诉设备', `goworkCode` varchar(128) COMMENT '办件编码', `goworkDepts` varchar(512) COMMENT '办件所属部门', `matterlName` varchar(256) COMMENT '事项名称', `goworkTime` datetime COMMENT '办理时间', `irregularType` tinyint(1) COMMENT '违规类型(1.脱岗,2.离岗,3.玩手机,4.睡觉)', `happenTime` datetime COMMENT '发生时间', `duration` int(9) DEFAULT '0' COMMENT '持续时间,秒', `alarmTime` datetime COMMENT '报警时间', `snapPath` varchar(255) COMMENT '图片凭证地址', `irregularOtherType` tinyint(1) COMMENT '违规类型(1.工作纪律)', `performType` varchar(64) COMMENT '绩效类型【attend:考勤绩效,review:评价差评绩效,complain:评价投诉绩效,gowork:办件绩效,effect:效能绩效,other:其它绩效】', `createUserId` bigint(20) NOT NULL COMMENT '创建用户', `createTime` datetime NOT NULL COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效记录申诉信息'; -- ---------------------------- -- 窗口负责人详细表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_window_owner_detail`; CREATE TABLE mortals_xhx_window_owner_detail( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `ownerId` bigint(20) DEFAULT '0' COMMENT '负责人Id', `staffId` bigint(20) DEFAULT '0' COMMENT '员工id号', `remark` varchar(255) COMMENT '备注', `createTime` datetime COMMENT '创建时间', `createUserId` bigint(20) COMMENT '创建用户', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='窗口负责人详细'; -- ---------------------------- -- 员工绩效统计表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_staff_perform_stat`; CREATE TABLE mortals_xhx_staff_perform_stat( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID,主键,自增长', `staffId` bigint(20) DEFAULT '0' COMMENT '部门id号', `staffName` varchar(64) COMMENT '部门名称', `totalScore` decimal(10,2) NOT NULL COMMENT '员工绩效总分数', `totalAddScore` decimal(10,2) NOT NULL COMMENT '员工绩效加分总分数汇总', `totalSubScore` decimal(10,2) NOT NULL COMMENT '员工绩效减分总分数汇总', `attendScoreAdd` decimal(10,2) COMMENT '考勤绩效指标增加分数', `attendScoreSub` decimal(10,2) COMMENT '考勤绩效指标扣减分数', `assessScoreAdd` decimal(10,2) COMMENT '评价绩效指标增加分数', `assessScoreSub` decimal(10,2) COMMENT '评价绩效指标扣减分数', `complainScoreAdd` decimal(10,2) COMMENT '投诉绩效指标增加分数', `complainScoreSub` decimal(10,2) COMMENT '投诉绩效指标扣减分数', `workScoreAdd` decimal(10,2) COMMENT '办件绩效指标增加分数', `workScoreSub` decimal(10,2) COMMENT '办件绩效指标扣减分数', `effectScoreAdd` decimal(10,2) COMMENT '效能绩效指标增加分数', `effectScoreSub` decimal(10,2) COMMENT '效能绩效指标扣减分数', `otherScoreAdd` decimal(10,2) COMMENT '其它绩效指标增加分数', `otherScoreSub` decimal(10,2) COMMENT '其它绩效指标扣减分数', `remark` varchar(255) COMMENT '备注', `year` int(9) NOT NULL COMMENT '年', `month` tinyint(2) NOT NULL COMMENT '月', `day` tinyint(2) NOT NULL COMMENT '日', `createTime` datetime COMMENT '创建时间', `createUserId` bigint(20) COMMENT '创建人id', `updateTime` datetime COMMENT '更新时间', `updateUserId` bigint(20) COMMENT '更新人id', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工绩效统计'; -- ---------------------------- 2023-7-14 -- ---------------------------- -- ---------------------------- -- 部门绩效分数统计表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_dept_perform_stat`; CREATE TABLE mortals_xhx_dept_perform_stat( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID,主键,自增长', `deptId` bigint(20) DEFAULT '0' COMMENT '部门id号', `deptName` varchar(64) COMMENT '部门名称', `totalScore` decimal(10,2) NOT NULL COMMENT '部门绩效总分数', `totalAddScore` decimal(10,2) NOT NULL COMMENT '部门绩效加分总分数汇总', `totalSubScore` decimal(10,2) NOT NULL COMMENT '部门绩效减分总分数汇总', `attendScoreAdd` decimal(10,2) COMMENT '考勤绩效指标增加分数', `attendScoreSub` decimal(10,2) COMMENT '考勤绩效指标扣减分数', `reviewScoreAdd` decimal(10,2) COMMENT '评价绩效指标增加分数', `reviewScoreSub` decimal(10,2) COMMENT '评价绩效指标扣减分数', `complainScoreAdd` decimal(10,2) COMMENT '投诉绩效指标增加分数', `complainScoreSub` decimal(10,2) COMMENT '投诉绩效指标扣减分数', `goworkScoreAdd` decimal(10,2) COMMENT '办件绩效指标增加分数', `goworkScoreSub` decimal(10,2) COMMENT '办件绩效指标扣减分数', `effectScoreAdd` decimal(10,2) COMMENT '效能绩效指标增加分数', `effectScoreSub` decimal(10,2) COMMENT '效能绩效指标扣减分数', `otherScoreAdd` decimal(10,2) COMMENT '其它绩效指标增加分数', `otherScoreSub` decimal(10,2) COMMENT '其它绩效指标扣减分数', `averageScore` decimal(10,2) NOT NULL COMMENT '部门绩效平均分数,根据部门所属人数平均', `remark` varchar(255) COMMENT '备注', `year` int(9) NOT NULL COMMENT '年', `month` tinyint(2) NOT NULL COMMENT '月', `day` tinyint(2) NOT NULL COMMENT '日', `createTime` datetime COMMENT '创建时间', `createUserId` bigint(20) COMMENT '创建人id', `updateTime` datetime COMMENT '更新时间', `updateUserId` bigint(20) COMMENT '更新人id', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门绩效分数统计'; -- ---------------------------- -- 员工绩效统计表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_staff_perform_stat`; CREATE TABLE mortals_xhx_staff_perform_stat( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID,主键,自增长', `staffId` bigint(20) DEFAULT '0' COMMENT '部门id号', `staffName` varchar(64) COMMENT '部门名称', `totalScore` decimal(10,2) NOT NULL COMMENT '员工绩效总分数', `totalAddScore` decimal(10,2) NOT NULL COMMENT '员工绩效加分总分数汇总', `totalSubScore` decimal(10,2) NOT NULL COMMENT '员工绩效减分总分数汇总', `attendScoreAdd` decimal(10,2) COMMENT '考勤绩效指标增加分数', `attendScoreSub` decimal(10,2) COMMENT '考勤绩效指标扣减分数', `reviewScoreAdd` decimal(10,2) COMMENT '评价绩效指标增加分数', `reviewScoreSub` decimal(10,2) COMMENT '评价绩效指标扣减分数', `complainScoreAdd` decimal(10,2) COMMENT '投诉绩效指标增加分数', `complainScoreSub` decimal(10,2) COMMENT '投诉绩效指标扣减分数', `goworkScoreAdd` decimal(10,2) COMMENT '办件绩效指标增加分数', `goworkScoreSub` decimal(10,2) COMMENT '办件绩效指标扣减分数', `effectScoreAdd` decimal(10,2) COMMENT '效能绩效指标增加分数', `effectScoreSub` decimal(10,2) COMMENT '效能绩效指标扣减分数', `otherScoreAdd` decimal(10,2) COMMENT '其它绩效指标增加分数', `otherScoreSub` decimal(10,2) COMMENT '其它绩效指标扣减分数', `remark` varchar(255) COMMENT '备注', `year` int(9) NOT NULL COMMENT '年', `month` tinyint(2) NOT NULL COMMENT '月', `day` tinyint(2) NOT NULL COMMENT '日', `createTime` datetime COMMENT '创建时间', `createUserId` bigint(20) COMMENT '创建人id', `updateTime` datetime COMMENT '更新时间', `updateUserId` bigint(20) COMMENT '更新人id', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工绩效统计'; -- ---------------------------- -- 员工绩效统计表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_staff_perform_summary`; CREATE TABLE mortals_xhx_staff_perform_summary( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID,主键,自增长', `staffId` bigint(20) DEFAULT '0' COMMENT '人员id', `workNum` varchar(64) COMMENT '工号', `staffName` varchar(512) COMMENT '人员名称', `deptId` bigint(20) DEFAULT '0' COMMENT '部门id号', `deptName` varchar(64) COMMENT '部门名称', `attendScore` decimal(10,2) COMMENT '考勤绩效指标分数', `reviewScore` decimal(10,2) COMMENT '评价绩效指标分数', `complainScore` decimal(10,2) COMMENT '投诉绩效指标分数', `goworkScore` decimal(10,2) COMMENT '办件绩效分数', `effectScore` decimal(10,2) COMMENT '效能绩效分数', `otherScore` decimal(10,2) COMMENT '其它绩效分数', `errorScore` decimal(10,2) COMMENT '累计异常分数', `totalScore` decimal(10,2) COMMENT '当月得分数', `remark` varchar(255) COMMENT '备注', `year` int(9) NOT NULL COMMENT '年', `month` tinyint(2) NOT NULL COMMENT '月', `createTime` datetime COMMENT '创建时间', `createUserId` bigint(20) COMMENT '创建人id', `updateTime` datetime COMMENT '更新时间', `updateUserId` bigint(20) COMMENT '更新人id', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工绩效统计'; -- ---------------------------- 2023-7-15 -- ---------------------------- ALTER TABLE mortals_xhx_perform_attend_appeal ADD COLUMN `deductPerson` varchar(128) DEFAULT '' COMMENT '扣分人员' AFTER subMethod; ALTER TABLE mortals_xhx_perform_attend_appeal ADD COLUMN `deductTime` datetime COMMENT '扣分时间' AFTER deductPerson; ALTER TABLE mortals_xhx_user ADD COLUMN `dingUserId` varchar(64) DEFAULT '' COMMENT '钉钉userId' AFTER customerId; ALTER TABLE mortals_xhx_check_attend_record ADD COLUMN `deductPerson` varchar(128) DEFAULT '' COMMENT '扣分人员' AFTER subMethod; ALTER TABLE mortals_xhx_check_attend_record ADD COLUMN `deductTime` datetime COMMENT '扣分时间' AFTER deductPerson; -- ---------------------------- -- 员工入职信息表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_staff_onboard`; CREATE TABLE mortals_xhx_staff_onboard( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `staffId` bigint(20) COMMENT '员工ID', `staffName` varchar(64) NOT NULL COMMENT '员工姓名', `gender` tinyint(2) DEFAULT '1' COMMENT '性别(1.男,2.女)', `birthday` datetime COMMENT '出生日期', `photoPath` varchar(255) COMMENT '照片', `phoneNumber` varchar(128) COMMENT '联系电话', `idCard` varchar(128) COMMENT '身份证号码', `workNum` varchar(128) COMMENT '工号', `filePath` varchar(128) COMMENT '入职登记表', `politicalstatus` tinyint(2) DEFAULT '1' COMMENT '政治面貌 (1.中共党员,2.中共预备党员,3.共青团员,4.群众,5.其它)', `deptId` bigint(20) COMMENT '所属部门', `deptName` varchar(128) COMMENT '所属部门名称', `jobId` bigint(20) COMMENT '职位ID', `jobName` varchar(128) COMMENT '职位名称', `staffType` tinyint(2) DEFAULT '1' COMMENT '员工类型(1.全职,2.兼职,3.实习)', `onBoardStatus` tinyint(2) DEFAULT '1' COMMENT '员工状态(1.待入职,2.试用中,3.超期)', `entryDate` datetime COMMENT '入职时间', `createUserId` bigint(20) NOT NULL COMMENT '创建用户', `createTime` datetime NOT NULL COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工入职信息'; ALTER TABLE mortals_xhx_perform_attend_record ADD COLUMN `windowNum` varchar(64) DEFAULT '' COMMENT '窗口编号' AFTER deductPerson; ALTER TABLE mortals_xhx_check_attend_record ADD COLUMN `windowNum` varchar(64) DEFAULT '' COMMENT '窗口编号' AFTER deductPerson; -- ---------------------------- 2023-7-16 -- ---------------------------- -- ---------------------------- -- 员工入职信息菜单 SQL -- ---------------------------- INSERT INTO `mortals_xhx_menu` VALUES (null, '员工入职信息', '/staff/onboard/list', 0,'', 1, 1, 0, 0,'',NULL, NULL, NULL, 0, 0, 1, NULL, NULL, NULL); -- ---------------------------- -- 员工入职信息资源路径 SQL -- ---------------------------- INSERT INTO `mortals_xhx_resource` VALUES (null, '员工入职信息-菜单管理-查看', '/staff/onboard/list,/staff/onboard/view,/staff/onboard/info,/staff/onboard/export,/staff/onboard/exportExcel,/staff/onboard/downloadTemplate,/staff/onboard/download', 3, 0, NULL, NULL, NULL, 0); INSERT INTO `mortals_xhx_resource` VALUES (null, '员工入职信息-菜单管理-维护', '/staff/onboard/add,/staff/onboard/edit,/staff/onboard/delete,/staff/onboard/logicDelete,/staff/onboard/save,/staff/onboard/importData', 3, 0, NULL, NULL, NULL, 0); -- ---------------------------- -- 员工入职信息参数 SQL -- ---------------------------- INSERT INTO `mortals_xhx_param` VALUES (null, '性别', 'StaffOnboard', 'gender', '1', '男', 1, 4, 0, 'gender', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '性别', 'StaffOnboard', 'gender', '2', '女', 1, 4, 0, 'gender', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '员工类型', 'StaffOnboard', 'staffType', '1', '全职', 1, 4, 0, 'staffType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '员工类型', 'StaffOnboard', 'staffType', '2', '兼职', 1, 4, 0, 'staffType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '员工类型', 'StaffOnboard', 'staffType', '3', '实习', 1, 4, 0, 'staffType', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '员工状态', 'StaffOnboard', 'onBoardStatus', '1', '待入职', 1, 4, 0, 'onBoardStatus', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '员工状态', 'StaffOnboard', 'onBoardStatus', '2', '试用中', 1, 4, 0, 'onBoardStatus', NULL, NULL, NULL); INSERT INTO `mortals_xhx_param` VALUES (null, '员工状态', 'StaffOnboard', 'onBoardStatus', '3', '超期', 1, 4, 0, 'onBoardStatus', NULL, NULL, NULL); -- ---------------------------- 2023-7-17 -- ---------------------------- -- ---------------------------- -- 请假记录信息表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_attendance_leave_record`; CREATE TABLE mortals_xhx_attendance_leave_record( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `leavePersonId` bigint(20) NOT NULL COMMENT '请假人id', `leavePerson` varchar(64) COMMENT '请假人', `deptId` bigint(20) COMMENT '所属部门id', `deptName` varchar(256) COMMENT '所属部门', `phoneNumber` varchar(20) COMMENT '电话号码', `leaveType` tinyint(2) COMMENT '请假类型(1.事假,2.调休,3.病假,4.年假,5.产假,6.陪产假,7.婚假,8.例假,9.哺乳假,10.丧假,11.回单位,12.因公请假,13.外出勘验,14.值班补班,15.体检,16.隔离,17.因公外出,18.公休,19.育儿假,20.调回单位,21.探亲假)', `startTime` datetime COMMENT '开始时间', `endTime` datetime COMMENT '结束时间', `duration` int(9) COMMENT '时长,单位秒', `reason` text COMMENT '请假事由', `approverId` bigint(20) COMMENT '审批负责人Id', `approver` varchar(64) COMMENT '审批负责人', `attachment` varchar(255) COMMENT '附件', `attachmentPath` varchar(255) COMMENT '附件路径', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', `appealTime` datetime COMMENT '申请时间', `auditResult` tinyint(1) COMMENT '审核结果(1.申请通过,2.申请不通过)', `auditDesc` varchar(64) COMMENT '审核说明', `auditTime` datetime COMMENT '审核时间', `processStatus` tinyint(1) DEFAULT '1' COMMENT '处理状态', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='请假记录信息'; ALTER TABLE mortals_xhx_attendance_stat ADD COLUMN `phoneNumer` varchar(64) default '' COMMENT '手机号码' AFTER staffName; ALTER TABLE mortals_xhx_attendance_stat ADD COLUMN `goTimes` int(9) default 0 COMMENT '出勤天数' AFTER phoneNumer; ALTER TABLE mortals_xhx_attendance_stat ADD COLUMN `morningTimes` int(9) default 0 COMMENT '上午缺卡次数' AFTER goTimes; ALTER TABLE mortals_xhx_attendance_stat ADD COLUMN `afternoonTimes` int(9) default 0 COMMENT '下午缺卡次数' AFTER morningTimes; ALTER TABLE mortals_xhx_staff_perform_summary ADD COLUMN `belongCenter` varchar(512) DEFAULT '宜宾市民中心' COMMENT '所属中心' AFTER staffName; ALTER TABLE mortals_xhx_feedback ADD COLUMN `feedbackFileId` bigint(20) default 0 COMMENT '图片id' ; ALTER TABLE mortals_xhx_feedback ADD COLUMN `feedbackFilePath` varchar(512) default '' COMMENT '图片路径' ; -- ---------------------------- -- 绩效反馈附件表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_feedback_file`; CREATE TABLE mortals_xhx_feedback_file( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `title` varchar(64) NOT NULL COMMENT '标题', `sort` int(4) DEFAULT '0' COMMENT '排序', `top` tinyint(2) DEFAULT '0' COMMENT '置顶 (0.否,1.是)', `feedbackFileName` varchar(1024) COMMENT '图片名称', `feedbackFilePath` varchar(512) COMMENT '图片路径', `remark` varchar(512) COMMENT '备注说明', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效反馈附件'; ALTER TABLE mortals_xhx_attendance_record ADD COLUMN `signInResult` tinyint(2) DEFAULT '0' COMMENT '签到结果(0.否,1.是)' ; ALTER TABLE mortals_xhx_attendance_record ADD COLUMN `signOutResult` tinyint(2) DEFAULT '0' COMMENT '签退结果(0.否,1.是)'; ALTER TABLE mortals_xhx_attendance_record ADD COLUMN `attendType` varchar(128) DEFAULT '0' COMMENT '考勤类型' ; ALTER TABLE mortals_xhx_attendance_record ADD COLUMN `punchResult` tinyint(2) DEFAULT '0' COMMENT '打卡结果(0.否,1.是)'; ALTER TABLE mortals_xhx_attendance_record_hik ADD COLUMN `signInResult` tinyint(2) DEFAULT '0' COMMENT '签到结果(0.否,1.是)' ; ALTER TABLE mortals_xhx_attendance_record_hik ADD COLUMN `signOutResult` tinyint(2) DEFAULT '0' COMMENT '签退结果(0.否,1.是)'; ALTER TABLE mortals_xhx_attendance_record_hik ADD COLUMN `attendType` varchar(128) DEFAULT '0' COMMENT '考勤类型'; ALTER TABLE mortals_xhx_attendance_record_hik ADD COLUMN `punchResult` tinyint(2) DEFAULT '0' COMMENT '打卡结果(0.否,1.是)'; -- ---------------------------- -- 07-27 -- ---------------------------- ALTER TABLE mortals_xhx_check_attend_record ADD COLUMN `auditStatus` tinyint(1) DEFAULT '1' COMMENT '审核状态(1.同意,2.不同意)'; ALTER TABLE mortals_xhx_check_review_record ADD COLUMN `auditStatus` tinyint(1) DEFAULT '1' COMMENT '审核状态(1.同意,2.不同意)'; ALTER TABLE mortals_xhx_check_complain_record ADD COLUMN `auditStatus` tinyint(1) DEFAULT '1' COMMENT '审核状态(1.同意,2.不同意)'; ALTER TABLE mortals_xhx_check_gowork_record ADD COLUMN `auditStatus` tinyint(1) DEFAULT '1' COMMENT '审核状态(1.同意,2.不同意)'; ALTER TABLE mortals_xhx_check_effect_record ADD COLUMN `auditStatus` tinyint(1) DEFAULT '1' COMMENT '审核状态(1.同意,2.不同意)'; ALTER TABLE mortals_xhx_check_other_record ADD COLUMN `auditStatus` tinyint(1) DEFAULT '1' COMMENT '审核状态(1.同意,2.不同意)'; ALTER TABLE mortals_xhx_perform_attend_appeal ADD COLUMN `windowNum` varchar(255) DEFAULT '' COMMENT '窗口编号'; -- ---------------------------- -- 考勤打卡记录信息表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_attendance_record`; CREATE TABLE mortals_xhx_attendance_record( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `staffId` bigint(20) COMMENT '员工ID', `staffName` varchar(64) NOT NULL COMMENT '员工姓名', `workNum` varchar(128) COMMENT '工号', `deptId` bigint(20) COMMENT '所属部门', `deptName` varchar(128) COMMENT '所属部门名称', `positionId` bigint(20) COMMENT '职位ID', `positionName` varchar(128) COMMENT '职位名称', `attendanceGroupId` bigint(20) COMMENT '所属考勤组ID', `attendanceGroupName` varchar(128) COMMENT '所属考勤组名称', `attendanceDate` datetime COMMENT '打卡日期', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) NOT NULL COMMENT '创建用户', `createTime` datetime NOT NULL COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', `signInResult` tinyint(2) DEFAULT '0' COMMENT '签到结果(0.否,1.是)', `signOutResult` tinyint(2) DEFAULT '0' COMMENT '签退结果(0.否,1.是)', `attendType` varchar(128) DEFAULT '' COMMENT '考勤类型', `punchResult` tinyint(2) DEFAULT '0' COMMENT '打卡结果(0.否,1.是)', `classId` bigint(20) COMMENT '班次ID', `className` varchar(128) COMMENT '班次名称', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='考勤打卡记录信息'; ALTER TABLE mortals_xhx_attendance_leave_record ADD COLUMN `sourceDingTime` varchar(64) DEFAULT '' COMMENT '钉钉原始请假时间记录'; -- ---------------------------- -- 2023-10-09 -- ---------------------------- -- ---------------------------- -- 节假日 -- ---------------------------- DROP TABLE IF EXISTS `mortals_sys_holiday`; CREATE TABLE `mortals_sys_holiday` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号,主键,自增长', `siteId` bigint(20) DEFAULT NULL COMMENT '站点ID', `siteName` varchar(255) DEFAULT NULL COMMENT '站点名称', `name` varchar(64) DEFAULT NULL COMMENT '节日名称', `summary` varchar(256) DEFAULT NULL COMMENT '备注', `year` int(4) DEFAULT NULL COMMENT '年份', `startTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '开始时间', `endTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '结束时间', `workorholiday` tinyint(2) DEFAULT '0' COMMENT '是否工作日(0.节假日,1.工作日)', `createTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', `createUserId` bigint(20) DEFAULT NULL COMMENT '创建用户', `updateTime` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '节假日表' ROW_FORMAT = Dynamic; -- -------------- -- 2023-12-05 员工绩效分数初始化任务 -- -------------- INSERT INTO `mortals_xhx_task` (`name`, `taskKey`, `status`, `excuteService`, `excuteParam`, `excuteHost`, `excuteStrategy`, `excuteDate`, `excuteTime`, `remark`, `lastExcuteHost`, `lastExcuteTime`, `interimExcuteStatus`, `createTime`, `createUserId`, `createUserName`) VALUES ('员工绩效分数初始化任务', 'StaffPerformInitDataTask', '0', 'StaffPerformInitDataTask', NULL, NULL, '1', '0', '00:10', NULL, NULL, NULL, '0', NOW(), '1', '系统管理员'); -- ---------- -- 2024-01-08 更新人员信息离职时间,人员信息表增加考核授权 -- ---------- update mortals_xhx_staff s INNER JOIN mortals_xhx_staff_leave l ON s.id = l.staffId SET s.leaveDate = l.leaveDate; ALTER TABLE `mortals_xhx_staff` ADD COLUMN `attendCheck` TINYINT (2) DEFAULT '0' COMMENT '考勤授权(0.不启用,1.启用)', ADD COLUMN `complainCheck` TINYINT (2) DEFAULT '0' COMMENT '评价授权(0.不启用,1.启用)', ADD COLUMN `reviewCheck` TINYINT (2) DEFAULT '0' COMMENT '投诉授权(0.不启用,1.启用)', ADD COLUMN `goworkCheck` TINYINT (2) DEFAULT '0' COMMENT '办件授权(0.不启用,1.启用)', ADD COLUMN `effectCheck` TINYINT (2) DEFAULT '0' COMMENT '效能授权(0.不启用,1.启用)', ADD COLUMN `otherCheck` TINYINT (2) DEFAULT '0' COMMENT '其他授权(0.不启用,1.启用)'; -- ---------- -- 2024-01-11 人员信息表增加所属大厅 -- ---------- ALTER TABLE `mortals_xhx_staff` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅', ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称'; ALTER TABLE `mortals_xhx_attendance_leave_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅', ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称'; ALTER TABLE `mortals_xhx_attendance_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅', ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称'; ALTER TABLE `mortals_xhx_attendance_record_error` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅', ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称'; ALTER TABLE `mortals_xhx_attendance_record_hik` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅', ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称'; ALTER TABLE `mortals_xhx_attendance_stat` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅', ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称'; -- ---------- -- 2024-01-17 大厅窗口考核 -- ---------- -- ---------------------------- -- 窗口考核登记表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_window_perform`; CREATE TABLE mortals_xhx_window_perform( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `year` int(8) COMMENT '考核年度', `month` int(4) COMMENT '考核月份', `windowId` bigint(20) COMMENT '窗口ID', `windowName` varchar(64) COMMENT '窗口名称', `ownerId` bigint(20) COMMENT '窗口负责人id', `ownerName` varchar(64) COMMENT '窗口负责人名称', `deptId` bigint(20) COMMENT '所属部门id', `deptName` varchar(256) COMMENT '所属部门名称', `salaId` bigint(20) COMMENT '所属大厅', `salaName` varchar(128) COMMENT '所属大厅名称', `workSummary` varchar(255) COMMENT '工作总结', `bonusReport` varchar(255) COMMENT '加分申报', `proof` varchar(128) COMMENT '加分佐证附件名称', `proofUrl` varchar(128) COMMENT '加分佐证附件地址', `deductStatement` varchar(255) COMMENT '扣分情况', `deductScore` decimal(10,2) DEFAULT '0' COMMENT '扣分分值', `discipline` decimal(10,2) DEFAULT '0' COMMENT '工作纪律自评得分', `specification` decimal(10,2) DEFAULT '0' COMMENT '服务规范自评得分', `bonusScore` decimal(10,2) DEFAULT '0' COMMENT '加分', `sumScore` decimal(10,2) DEFAULT '0' COMMENT '合计得分', `selfLevel` int(4) COMMENT '自评等级1红旗窗口2黑旗窗口', `remark` varchar(255) COMMENT '备注', `annex` varchar(128) COMMENT '自评附件名称', `annexUrl` varchar(128) COMMENT '自评附件地址', `fillDate` datetime COMMENT '填报时间', `fillStatus` int(4) DEFAULT '0' COMMENT '填报状态0暂存1提交', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', `windowCode` varchar(64) COMMENT '窗口编号', PRIMARY KEY (`id`) ,KEY `year` (`year`) USING BTREE ,KEY `windowName` (`windowName`) USING BTREE ,KEY `deptName` (`deptName`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='窗口考核登记'; -- ---------------------------- -- 窗口人员考核汇总登记表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_window_workman_perform`; CREATE TABLE mortals_xhx_window_workman_perform( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `year` int(8) COMMENT '考核年度', `month` int(4) COMMENT '考核月份', `windowId` bigint(20) COMMENT '窗口ID', `windowName` varchar(64) COMMENT '窗口名称', `ownerId` bigint(20) COMMENT '窗口负责人id', `ownerName` varchar(64) COMMENT '窗口负责人名称', `deptId` bigint(20) COMMENT '所属部门id', `deptName` varchar(256) COMMENT '所属部门名称', `salaId` bigint(20) COMMENT '所属大厅', `salaName` varchar(128) COMMENT '所属大厅名称', `remark` varchar(255) COMMENT '备注', `annex` varchar(128) COMMENT '附件名称', `annexUrl` varchar(128) COMMENT '附件地址', `fillDate` datetime COMMENT '填报时间', `fillStatus` int(4) DEFAULT '0' COMMENT '填报状态0暂存1提交', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', `windowCode` varchar(64) COMMENT '窗口编号', PRIMARY KEY (`id`) ,KEY `year` (`year`) USING BTREE ,KEY `windowName` (`windowName`) USING BTREE ,KEY `deptName` (`deptName`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='窗口人员考核汇总登记'; DROP TABLE IF EXISTS `mortals_xhx_window_workman_perform_detail`; CREATE TABLE mortals_xhx_window_workman_perform_detail( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `performId` bigint(20) NOT NULL COMMENT '人员考核汇总表主键', `staffId` bigint(20) COMMENT '工作人员id', `staffName` varchar(64) COMMENT '工作人员名称', `discipline` decimal(10,2) DEFAULT '0' COMMENT '工作纪律得分', `specification` decimal(10,2) DEFAULT '0' COMMENT '服务规范得分', `management` decimal(10,2) DEFAULT '0' COMMENT '综合管理得分', `evaluation` decimal(10,2) DEFAULT '0' COMMENT '群众评议得分', `efficiency` decimal(10,2) DEFAULT '0' COMMENT '工作效能得分', `bonusScore` decimal(10,2) DEFAULT '0' COMMENT '加分', `sumScore` decimal(10,2) DEFAULT '0' COMMENT '合计得分', `examineLevel` int(4) COMMENT '考核等级1好2较好3一般4较差', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ,KEY `performId` (`performId`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='窗口人员考核汇总明细'; DROP TABLE IF EXISTS `mortals_xhx_window_owner`; CREATE TABLE mortals_xhx_window_owner( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `deptId` bigint(20) DEFAULT '0' COMMENT '部门id号', `deptName` varchar(64) COMMENT '部门名称', `salaId` bigint(20) COMMENT '所属大厅', `salaName` varchar(128) COMMENT '所属大厅名称', `staffId` bigint(20) COMMENT '窗口负责人id', `staffName` varchar(64) COMMENT '窗口负责人名称', `number` varchar(64) COMMENT '工号', `phone` varchar(64) COMMENT '电话号码', `windowCount` int(9) COMMENT '负责窗口数量', `remark` varchar(255) COMMENT '备注', `createTime` datetime COMMENT '创建时间', `createUserId` bigint(20) COMMENT '创建用户', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='窗口负责人'; DROP TABLE IF EXISTS `mortals_xhx_window_owner_detail`; CREATE TABLE mortals_xhx_window_owner_detail( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `ownerId` bigint(20) DEFAULT '0' COMMENT '负责人Id', `windowId` bigint(20) DEFAULT '0' COMMENT '窗口id', `windowName` varchar(64) COMMENT '窗口名称', `windowCode` varchar(64) COMMENT '窗口编号', `building` tinyint(2) DEFAULT '1' COMMENT '所属楼栋', `level` tinyint(2) DEFAULT '1' COMMENT '所属楼层', `remark` varchar(255) COMMENT '备注', `createTime` datetime COMMENT '创建时间', `createUserId` bigint(20) COMMENT '创建用户', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='窗口负责人详细'; DROP TABLE IF EXISTS `mortals_xhx_check_window_perform`; CREATE TABLE mortals_xhx_check_window_perform( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `recordId` bigint(20) COMMENT '记录ID', `year` int(8) COMMENT '考核年度', `month` int(4) COMMENT '考核月份', `windowId` bigint(20) COMMENT '窗口ID', `windowName` varchar(64) COMMENT '窗口名称', `ownerId` bigint(20) COMMENT '窗口负责人id', `ownerName` varchar(64) COMMENT '窗口负责人名称', `deptId` bigint(20) COMMENT '所属部门id', `deptName` varchar(256) COMMENT '所属部门名称', `salaId` bigint(20) COMMENT '所属大厅', `salaName` varchar(128) COMMENT '所属大厅名称', `fromName` varchar(128) COMMENT '考核表单名称', `sumScore` decimal(10,2) DEFAULT '0' COMMENT '合计得分', `submitDate` datetime COMMENT '提交时间', `manageCheckPerson` varchar(128) COMMENT '管理组核查人员', `manageCheckTime` datetime COMMENT '管理组核查时间', `manageCheckDesc` varchar(512) COMMENT '管理组核查说明', `manageCheckResult` varchar(128) COMMENT '管理组核查结果(1.通过,2.不通过)', `leaderCheckPerson` varchar(128) COMMENT '领导组核查人员', `leaderCheckTime` datetime COMMENT '领导组核查时间', `leaderCheckDesc` varchar(512) COMMENT '领导组核查说明', `leaderCheckResult` varchar(128) COMMENT '领导组核查结果(1.通过,2.不通过)', `checkStatus` tinyint(1) DEFAULT '1' COMMENT '处理状态(1.未处理,2.已处理)', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ,KEY `year` (`year`) USING BTREE ,KEY `windowId` (`windowId`) USING BTREE ,KEY `ownerId` (`ownerId`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='窗口考核核查'; DROP TABLE IF EXISTS `mortals_xhx_check_window_workman_perform`; CREATE TABLE mortals_xhx_check_window_workman_perform( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `recordId` bigint(20) COMMENT '记录ID', `year` int(8) COMMENT '考核年度', `month` int(4) COMMENT '考核月份', `windowId` bigint(20) COMMENT '窗口ID', `windowName` varchar(64) COMMENT '窗口名称', `ownerId` bigint(20) COMMENT '窗口负责人id', `ownerName` varchar(64) COMMENT '窗口负责人名称', `deptId` bigint(20) COMMENT '所属部门id', `deptName` varchar(256) COMMENT '所属部门名称', `salaId` bigint(20) COMMENT '所属大厅', `salaName` varchar(128) COMMENT '所属大厅名称', `fromName` varchar(128) COMMENT '考核表单名称', `submitDate` datetime COMMENT '提交时间', `manageCheckPerson` varchar(128) COMMENT '管理组核查人员', `manageCheckTime` datetime COMMENT '管理组核查时间', `manageCheckDesc` varchar(512) COMMENT '管理组核查说明', `manageCheckResult` varchar(128) COMMENT '管理组核查结果(1.通过,2.不通过)', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) COMMENT '创建用户', `createTime` datetime COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ,KEY `year` (`year`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='窗口人员考核汇总核查'; -- ---------------------------- -- 绩效异常记录信息表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_perform_error_record`; CREATE TABLE mortals_xhx_perform_error_record( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `staffId` bigint(20) COMMENT '员工ID', `staffName` varchar(64) NOT NULL COMMENT '员工姓名', `workNum` varchar(128) COMMENT '工号', `windowNum` varchar(128) COMMENT '窗口编号', `deptId` bigint(20) COMMENT '所属部门', `deptName` varchar(128) COMMENT '所属部门名称', `irregularType` tinyint(1) COMMENT '违规类型(1.脱岗,2.离岗,3.玩手机,4.睡觉)', `happenTime` datetime COMMENT '发生时间', `duration` int(9) DEFAULT '0' COMMENT '持续时间,秒', `alarmTime` datetime COMMENT '报警时间', `snapPath` varchar(255) COMMENT '图片凭证地址', `categoryId` bigint(20) COMMENT '绩效规则分类id', `categoryName` varchar(128) COMMENT '规则名称', `ruleId` bigint(20) COMMENT '绩效规则id', `ruleName` varchar(128) COMMENT '规则名称', `subMethod` tinyint(1) DEFAULT '2' COMMENT '扣分方式(1.系统自动,2.人工添加)', `deductPerson` varchar(128) COMMENT '扣分人员', `deductTime` datetime COMMENT '扣分时间', `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)', `score` decimal(10,2) DEFAULT '0.00' COMMENT '扣分或增加分值', `processStatus` tinyint(1) DEFAULT '1' COMMENT '处理状态(1.未处理,2.已处理)', `fileNames` varchar(256) COMMENT '附件名称,多个逗号分割', `filePaths` varchar(512) COMMENT '附件下载地址,多个逗号分割', `remark` varchar(255) COMMENT '备注', `createUserId` bigint(20) NOT NULL COMMENT '创建用户', `createTime` datetime NOT NULL COMMENT '创建时间', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效异常记录信息'; ALTER TABLE `mortals_xhx_window_workman_perform_detail` ADD COLUMN `deptId` bigint(20) COMMENT '所属部门id' AFTER `staffName`, ADD COLUMN `deptName` varchar(256) COMMENT '所属部门名称' AFTER `deptId`, ADD COLUMN `salaId` bigint(20) COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) COMMENT '所属大厅名称' AFTER `salaId`; -- ------------------ -- 2024-03-12 -- 绩效表增加人员大厅字段 -- ------------------ ALTER TABLE `mortals_xhx_attendance_vacation_balance` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_check_attend_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_check_complain_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_check_effect_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_check_gowork_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_check_other_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_check_review_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_perform_attend_appeal` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_perform_attend_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_perform_complain_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_perform_effect_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_perform_error_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_perform_gowork_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_perform_other_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_perform_review_record` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_staff_perform_stat` ADD COLUMN `deptId` bigint(20) COMMENT '所属部门id' AFTER `staffName`, ADD COLUMN `deptName` varchar(256) COMMENT '所属部门名称' AFTER `deptId`, ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; ALTER TABLE `mortals_xhx_staff_perform_summary` ADD COLUMN `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅' AFTER `deptName`, ADD COLUMN `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称' AFTER `salaId`; -- ---------------------------- 2024-03-18 -- ---------------------------- -- ---------------------------- -- 门禁设备表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_door`; CREATE TABLE mortals_xhx_door( `id` bigint(20) AUTO_INCREMENT COMMENT '序号,主键,自增长', `deviceName` varchar(64) COMMENT '设备名称', `deviceCode` varchar(64) COMMENT '设备编码', `ip` varchar(64) COMMENT 'Ip地址', `port` int(9) COMMENT '端口', `username` varchar(64) COMMENT '用户名', `password` varchar(64) COMMENT '密码', `createTime` datetime COMMENT '创建时间', `createUserId` bigint(20) COMMENT '创建用户', `updateUserId` bigint(20) COMMENT '更新用户', `updateTime` datetime COMMENT '修改时间', `deptId` bigint (20) COMMENT '所属部门id', `deptName` varchar(64) COMMENT '所属部门名称', `remark` varchar(256) COMMENT '备注', PRIMARY KEY (`id`) ,KEY `deviceCode` (`deviceCode`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='门禁设备'; -- ---------------------------- 2024-04-26 -- ---------------------------- -- ---------------------------- -- 窗口负责人 -- ---------------------------- ALTER TABLE `mortals_xhx_window_owner` ADD COLUMN `roleType` tinyint(2) COMMENT '角色类型(首席代表,运维,审核,其他)' AFTER `remark`, ADD COLUMN `inspect` tinyint(2) DEFAULT '0' COMMENT '是否允许巡检' AFTER `roleType`, ADD COLUMN `staffIds` varchar(255) COMMENT '管辖人员' AFTER `inspect`; -- ---------------------------- 2024-04-28 -- ---------------------------- -- ---------------------------- -- 绩效异常信息表 -- ---------------------------- DROP TABLE IF EXISTS `mortals_xhx_perform_error_message`; CREATE TABLE `mortals_xhx_perform_error_message` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号,主键,自增长', `checkRecordId` bigint(20) DEFAULT NULL COMMENT '核查记录Id', `deptId` bigint(20) DEFAULT '0' COMMENT '部门id号', `deptName` varchar(64) DEFAULT NULL COMMENT '部门名称', `salaId` bigint(20) DEFAULT NULL COMMENT '所属大厅', `salaName` varchar(128) DEFAULT NULL COMMENT '所属大厅名称', `staffId` bigint(20) DEFAULT NULL COMMENT '绩效责任人id', `staffName` varchar(64) DEFAULT NULL COMMENT '绩效负责人名称', `workNum` varchar(64) DEFAULT NULL COMMENT '工号', `errorTime` datetime DEFAULT NULL COMMENT '异常时间', `ruleId` bigint(20) DEFAULT NULL COMMENT '绩效规则id', `ruleName` varchar(128) DEFAULT NULL COMMENT '规则名称', `subAddType` tinyint(1) DEFAULT '1' COMMENT '增减类型(1.增加,2.扣除)', `score` decimal(10,2) DEFAULT '0.00' COMMENT '扣分或增加分值', `readStatus` tinyint(1) DEFAULT '0' COMMENT '阅读状态0未读1已读', `checkStatus` tinyint(1) DEFAULT '0' COMMENT '核查处理状态1.未处理,2.已处理', `appealStatus` tinyint(1) DEFAULT '0' COMMENT '申诉状态(0.未申诉,1.申诉中,2.申诉拒绝,3.申诉通过)', `performType` varchar(64) DEFAULT NULL COMMENT '绩效类型【attend:考勤绩效,review:评价差评绩效,complain:评价投诉绩效,gowork:办件绩效,effect:效能绩效,other:其它绩效】', `remark` varchar(255) DEFAULT NULL COMMENT '备注', `createTime` datetime DEFAULT NULL COMMENT '创建时间', `createUserId` bigint(20) DEFAULT NULL COMMENT '创建用户', `updateUserId` bigint(20) DEFAULT NULL COMMENT '更新用户', `updateTime` datetime DEFAULT NULL COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='绩效异常信息'; ALTER TABLE `mortals_xhx_perform_attend_appeal` ADD COLUMN `appealPersonId` bigint(20) COMMENT '申诉人id' AFTER `performType`, ADD COLUMN `appealPerson` varchar(64) COMMENT '申诉人姓名' AFTER `appealPersonId`, ADD KEY `appealPerson` (`appealPerson`) USING BTREE;