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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
| -- =============================================
| -- HRM 需求①②收尾 SQL 初始化脚本
| -- 覆盖：转正申请表、月度工时汇总表、员工级排班明细表、
| -- 绩效考核班组范围列（scope_team_ids）、新菜单与按钮权限
| -- 执行库：mom-xgdl（run-once，禁止重复执行）
| -- 规则：不含 tenant_id；菜单/权限使用一次性 INSERT
| -- =============================================
|
| -- =============================================
| -- 1. 转正申请表
| -- =============================================
| CREATE TABLE IF NOT EXISTS `hrm_regularize_application` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '申请ID',
| `no` varchar(32) NOT NULL COMMENT '申请单号',
| `user_id` bigint NOT NULL COMMENT '申请人ID（关联 system_users.id）',
| `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
| `post_id` bigint DEFAULT NULL COMMENT '岗位ID',
| `employment_type` tinyint DEFAULT NULL COMMENT '当前用工性质：1-正式，2-劳务，3-外包，4-实习，5-退休返聘，6-其他',
| `expected_date` date DEFAULT NULL COMMENT '拟转正日期',
| `reason` varchar(500) DEFAULT NULL COMMENT '转正原因',
| `status` tinyint NOT NULL DEFAULT 0 COMMENT '审批状态：0-草稿，10-审批中，20-已通过，30-已驳回，40-已取消',
| `process_instance_id` varchar(64) DEFAULT NULL COMMENT 'BPM流程实例ID',
| `approve_time` datetime DEFAULT NULL COMMENT '审批完成时间',
| `remark` varchar(500) DEFAULT NULL COMMENT '备注',
| `creator` varchar(64) DEFAULT '' COMMENT '创建者',
| `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
| `updater` varchar(64) DEFAULT '' COMMENT '更新者',
| `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
| `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_no` (`no`),
| KEY `idx_user_id` (`user_id`),
| KEY `idx_status` (`status`),
| KEY `idx_process_instance_id` (`process_instance_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HRM转正申请表';
|
| -- =============================================
| -- 2. 月度工时汇总表
| -- =============================================
| CREATE TABLE IF NOT EXISTS `hrm_workhour_summary` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '汇总ID',
| `employee_id` bigint NOT NULL COMMENT '员工ID',
| `dept_id` bigint DEFAULT NULL COMMENT '部门ID',
| `period` varchar(10) NOT NULL COMMENT '核算周期（yyyy-MM）',
| `work_days` int DEFAULT 0 COMMENT '出勤天数',
| `work_hours` decimal(10,2) DEFAULT 0 COMMENT '正常工时（小时）',
| `overtime_hours` decimal(10,2) DEFAULT 0 COMMENT '加班工时（小时）',
| `remark` varchar(500) DEFAULT NULL COMMENT '备注',
| `creator` varchar(64) DEFAULT '' COMMENT '创建者',
| `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
| `updater` varchar(64) DEFAULT '' COMMENT '更新者',
| `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
| `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
| PRIMARY KEY (`id`),
| UNIQUE KEY `uk_employee_period` (`employee_id`, `period`),
| KEY `idx_period` (`period`),
| KEY `idx_dept_id` (`dept_id`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HRM月度工时汇总表';
|
| -- =============================================
| -- 3. 员工级排班明细表
| -- =============================================
| CREATE TABLE IF NOT EXISTS `mes_cal_schedule_detail` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
| `plan_id` bigint DEFAULT NULL COMMENT '排班计划编号',
| `team_id` bigint DEFAULT NULL COMMENT '班组编号',
| `user_id` bigint NOT NULL COMMENT '班组成员用户编号（关联 system_users.id）',
| `day` datetime NOT NULL COMMENT '日期',
| `shift_id` bigint DEFAULT NULL COMMENT '班次编号',
| `shift_name` varchar(50) DEFAULT NULL COMMENT '班次名称（冗余）',
| `start_time` varchar(20) DEFAULT NULL COMMENT '开始时间（HH:mm，冗余）',
| `end_time` varchar(20) DEFAULT NULL COMMENT '结束时间（HH:mm，冗余）',
| `source` tinyint NOT NULL DEFAULT 1 COMMENT '来源：1-自动生成，2-人工调整',
| `remark` varchar(500) DEFAULT NULL COMMENT '备注',
| `creator` varchar(64) DEFAULT '' COMMENT '创建者',
| `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
| `updater` varchar(64) DEFAULT '' COMMENT '更新者',
| `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
| `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
| PRIMARY KEY (`id`),
| KEY `idx_plan_id` (`plan_id`),
| KEY `idx_team_id` (`team_id`),
| KEY `idx_user_id_day` (`user_id`, `day`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='MES员工级排班明细表';
|
| -- =============================================
| -- 4. 绩效考核表增加班组范围列（先检查再添加，兼容重复执行）
| -- =============================================
| SET @exists_scope_team_ids = (SELECT COUNT(*) FROM information_schema.columns
| WHERE table_schema = 'mom-xgdl' AND table_name = 'hrm_performance_appraise' AND column_name = 'scope_team_ids');
| SET @sql_scope_team_ids = IF(@exists_scope_team_ids = 0,
| 'ALTER TABLE `hrm_performance_appraise` ADD COLUMN `scope_team_ids` varchar(500) DEFAULT NULL COMMENT ''指定班组编号集合（JSON 数组，scope_type=2 时生效）'' AFTER `scope_type`',
| 'SELECT 1');
| PREPARE stmt_scope_team_ids FROM @sql_scope_team_ids;
| EXECUTE stmt_scope_team_ids;
| DEALLOCATE PREPARE stmt_scope_team_ids;
|
| -- =============================================
| -- 5. 菜单与按钮权限（一次性 INSERT）
| -- =============================================
|
| -- 5.1 转正申请菜单（父 6002 人事审批）+ 按钮
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES
| (6007, '转正申请', '', 2, 4, 6002, 'regularize', 'ep:promotion', 'hrm/regularize/index', 'HrmRegularizeApplication', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (600701, '转正申请查询', 'hrm:regularize:query', 3, 1, 6007, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (600702, '转正申请创建', 'hrm:regularize:create', 3, 2, 6007, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (600703, '转正申请更新', 'hrm:regularize:update', 3, 3, 6007, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (600704, '转正申请删除', 'hrm:regularize:delete', 3, 4, 6007, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (600705, '转正申请提交审批', 'hrm:regularize:submit', 3, 5, 6007, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (600706, '转正申请审批', 'hrm:regularize:audit', 3, 6, 6007, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 5.2 月度工时汇总菜单（父 6001 人力资源）+ 按钮
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES
| (1065700, '月度工时汇总', '', 2, 3, 6001, 'work-hour-summary', 'ep:data-line', 'hrm/workHourSummary/index', 'HrmWorkHourSummary', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (1065701, '月度工时汇总查询', 'hrm:work-hour:query', 3, 1, 1065700, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (1065702, '月度工时汇总生成', 'hrm:work-hour:generate', 3, 2, 1065700, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 5.3 薪酬结构菜单（父 6020 薪酬管理）+ 按钮
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES
| (1065710, '薪酬结构', '', 2, 4, 6020, 'salary-structure', 'ep:coins', 'hrm/salaryStructure/index', 'HrmSalaryStructure', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (1065711, '薪酬结构查询', 'hrm:salary:query', 3, 1, 1065710, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (1065712, '薪酬结构创建', 'hrm:salary:create', 3, 2, 1065710, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (1065713, '薪酬结构更新', 'hrm:salary:update', 3, 3, 1065710, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (1065714, '薪酬结构删除', 'hrm:salary:delete', 3, 4, 1065710, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 5.4 员工薪酬档案菜单（父 6020 薪酬管理）+ 按钮
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES
| (1065720, '员工薪酬档案', '', 2, 5, 6020, 'employee-salary', 'ep:wallet', 'hrm/employeeSalary/index', 'HrmEmployeeSalary', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (1065721, '员工薪酬档案查询', 'hrm:salary:query', 3, 1, 1065720, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (1065722, '员工薪酬档案创建', 'hrm:salary:create', 3, 2, 1065720, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (1065723, '员工薪酬档案更新', 'hrm:salary:update', 3, 3, 1065720, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (1065724, '员工薪酬档案删除', 'hrm:salary:delete', 3, 4, 1065720, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 5.5 员工级排班明细菜单（父 5200 计划排程）+ 按钮
| INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES
| (5270, '员工排班明细', '', 2, 4, 5200, 'schedule-detail', 'ep:tickets', 'mes/cal/scheduleDetail/index', 'MesCalScheduleDetail', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (5271, '员工排班明细查询', 'mes:cal-schedule-detail:query', 3, 1, 5270, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (5272, '员工排班明细生成', 'mes:cal-schedule-detail:generate', 3, 2, 5270, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0'),
| (5273, '员工排班明细调整', 'mes:cal-schedule-detail:update', 3, 3, 5270, '', '', '', '', 0, b'1', b'0', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- =============================================
| -- 6. 角色授权（与 HRM 现有 1065330 工时统计一致，授权给超管/管理员角色）
| -- =============================================
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES
| (1, 6007, '1', NOW(), '1', NOW(), b'0'),
| (1, 600701, '1', NOW(), '1', NOW(), b'0'),
| (1, 600702, '1', NOW(), '1', NOW(), b'0'),
| (1, 600703, '1', NOW(), '1', NOW(), b'0'),
| (1, 600704, '1', NOW(), '1', NOW(), b'0'),
| (1, 600705, '1', NOW(), '1', NOW(), b'0'),
| (1, 600706, '1', NOW(), '1', NOW(), b'0'),
| (2, 1065700, '1', NOW(), '1', NOW(), b'0'),
| (2, 1065701, '1', NOW(), '1', NOW(), b'0'),
| (2, 1065702, '1', NOW(), '1', NOW(), b'0'),
| (109, 1065700, '1', NOW(), '1', NOW(), b'0'),
| (109, 1065701, '1', NOW(), '1', NOW(), b'0'),
| (109, 1065702, '1', NOW(), '1', NOW(), b'0'),
| (111, 1065700, '1', NOW(), '1', NOW(), b'0'),
| (111, 1065701, '1', NOW(), '1', NOW(), b'0'),
| (111, 1065702, '1', NOW(), '1', NOW(), b'0');
|
|
