人事调动离职填报表.sql
5.14 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
-- =============================================================================
-- 人事调动/离职填报表 lq_personnel_report
-- 说明：
-- 1. 主键 F_Id 须由应用层 YitIdHelper.NextId().ToString() 生成。
-- 2. 独立待办填报，不联动 BASE_USER、员工档案、流程、考勤、算薪等主业务。
-- 3. 来源组织/门店、提交人由服务端从当前登录用户快照写入。
-- 4. 幂等：CREATE TABLE IF NOT EXISTS + 索引通过 information_schema 判断后创建。
-- =============================================================================
CREATE TABLE IF NOT EXISTS `lq_personnel_report` (
`F_Id` varchar(50) NOT NULL COMMENT '主键',
`F_ReportNo` varchar(32) NOT NULL COMMENT '填报编号（对外展示，唯一）',
`F_ApplicationType` int NOT NULL COMMENT '申请类型：1调动 2离职',
`F_ApplicantUserId` varchar(50) NOT NULL COMMENT '被填报员工用户ID（BASE_USER.F_Id）',
`F_ApplicantUserName` varchar(128) DEFAULT NULL COMMENT '被填报员工姓名快照',
`F_ApplicantUserMobile` varchar(32) DEFAULT NULL COMMENT '被填报员工手机号快照',
`F_SourceOrganizeId` varchar(50) DEFAULT NULL COMMENT '来源组织ID（提交人当前组织快照）',
`F_SourceOrganizeName` varchar(200) DEFAULT NULL COMMENT '来源组织名称快照',
`F_SourceMdid` varchar(50) DEFAULT NULL COMMENT '来源门店ID（提交人当前门店快照）',
`F_SourceMdName` varchar(200) DEFAULT NULL COMMENT '来源门店名称快照',
`F_SubmitterUserId` varchar(50) DEFAULT NULL COMMENT '提交人用户ID',
`F_SubmitterUserName` varchar(128) DEFAULT NULL COMMENT '提交人姓名',
`F_SubmitTime` datetime NOT NULL COMMENT '提交时间',
`F_TargetMdid` varchar(50) DEFAULT NULL COMMENT '目标门店ID（调动时必填）',
`F_TargetMdName` varchar(200) DEFAULT NULL COMMENT '目标门店名称快照',
`F_TransferDate` date DEFAULT NULL COMMENT '调动日期（调动时必填）',
`F_ResignationDate` date DEFAULT NULL COMMENT '离职日期（离职时必填）',
`F_ResignationReason` varchar(500) DEFAULT NULL COMMENT '离职原因（离职时必填）',
`F_Remark` varchar(500) DEFAULT NULL COMMENT '备注',
`F_HandleStatus` int NOT NULL DEFAULT 0 COMMENT '处理状态：0待处理 1已处理',
`F_HandleUserId` varchar(50) DEFAULT NULL COMMENT '处理人ID',
`F_HandleUserName` varchar(128) DEFAULT NULL COMMENT '处理人姓名',
`F_HandleTime` datetime DEFAULT NULL COMMENT '处理时间',
`F_HandleRemark` varchar(500) DEFAULT NULL COMMENT '处理备注',
`F_CreateTime` datetime DEFAULT NULL COMMENT '创建时间',
`F_UpdateTime` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`F_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人事调动/离职填报（独立待办）';
-- 唯一索引：填报编号
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_personnel_report' AND index_name = 'UK_lq_personnel_report_No'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_personnel_report` ADD UNIQUE KEY `UK_lq_personnel_report_No` (`F_ReportNo`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 联合索引：处理状态 + 提交时间（列表/工作台待办）
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_personnel_report' AND index_name = 'IDX_lq_personnel_report_Handle_SubmitTime'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_personnel_report` ADD KEY `IDX_lq_personnel_report_Handle_SubmitTime` (`F_HandleStatus`, `F_SubmitTime`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 联合索引：来源组织 + 提交时间（权限过滤列表）
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_personnel_report' AND index_name = 'IDX_lq_personnel_report_SourceOrg_SubmitTime'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_personnel_report` ADD KEY `IDX_lq_personnel_report_SourceOrg_SubmitTime` (`F_SourceOrganizeId`, `F_SubmitTime`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 联合索引：申请类型 + 处理状态
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_personnel_report' AND index_name = 'IDX_lq_personnel_report_Type_Handle'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_personnel_report` ADD KEY `IDX_lq_personnel_report_Type_Handle` (`F_ApplicationType`, `F_HandleStatus`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 普通索引：被填报员工
SET @idx_exists := (
SELECT COUNT(1) FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'lq_personnel_report' AND index_name = 'IDX_lq_personnel_report_ApplicantUserId'
);
SET @sql := IF(@idx_exists = 0,
'ALTER TABLE `lq_personnel_report` ADD KEY `IDX_lq_personnel_report_ApplicantUserId` (`F_ApplicantUserId`)',
'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;