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
| -- =============================================
| -- HRM 考勤管理补充 SQL 初始化脚本
| -- 包含节假日配置、考勤统计汇总表、菜单权限
| -- =============================================
|
| -- 1. 节假日配置表
| CREATE TABLE IF NOT EXISTS `hrm_attendance_holiday` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '节假日ID',
| `name` varchar(100) NOT NULL COMMENT '节假日名称',
| `date` date NOT NULL COMMENT '日期',
| `type` tinyint NOT NULL DEFAULT 1 COMMENT '类型：1-休息日，2-工作日',
| `is_legal` tinyint NOT NULL DEFAULT 0 COMMENT '是否法定节假日：0-否，1-是',
| `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_date` (`date`, `deleted`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HRM考勤节假日配置表';
|
| -- 2. 考勤统计汇总表
| CREATE TABLE IF NOT EXISTS `hrm_attendance_summary` (
| `id` bigint NOT NULL AUTO_INCREMENT COMMENT '汇总ID',
| `user_id` bigint NOT NULL COMMENT '员工ID',
| `dept_id` bigint NOT NULL COMMENT '部门ID',
| `year_month` varchar(7) NOT NULL COMMENT '年月，格式：yyyy-MM',
| `work_days` int NOT NULL DEFAULT 0 COMMENT '应出勤天数',
| `actual_days` int NOT NULL DEFAULT 0 COMMENT '实际出勤天数',
| `late_count` int NOT NULL DEFAULT 0 COMMENT '迟到次数',
| `early_count` int NOT NULL DEFAULT 0 COMMENT '早退次数',
| `absent_count` int NOT NULL DEFAULT 0 COMMENT '缺卡次数',
| `overtime_hours` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '加班时长（小时）',
| `leave_hours` decimal(10,2) NOT NULL DEFAULT 0.00 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_user_year_month` (`user_id`, `year_month`, `deleted`),
| KEY `idx_dept_year_month` (`dept_id`, `year_month`)
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='HRM考勤统计汇总表';
|
| -- =============================================
| -- 3. 菜单权限
| -- =============================================
|
| -- 3.1 考勤规则菜单
| 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 (6013, '考勤规则', '', 2, 0, 6010, 'rule', 'ep:setting', 'hrm/attendance/rule/index', 'HrmAttendanceRule', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60131, '考勤规则查询', 'hrm:attendance:query', 3, 1, 6013, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60132, '考勤规则新增', 'hrm:attendance:create', 3, 2, 6013, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60133, '考勤规则修改', 'hrm:attendance:update', 3, 3, 6013, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60134, '考勤规则删除', 'hrm:attendance:delete', 3, 4, 6013, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60135, '考勤规则导出', 'hrm:attendance:export', 3, 5, 6013, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 3.2 节假日配置菜单
| 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 (6014, '节假日配置', '', 2, 3, 6010, 'holiday', 'ep:calendar', 'hrm/attendance/holiday/index', 'HrmAttendanceHoliday', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60141, '节假日配置查询', 'hrm:attendance:query', 3, 1, 6014, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60142, '节假日配置新增', 'hrm:attendance:create', 3, 2, 6014, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60143, '节假日配置修改', 'hrm:attendance:update', 3, 3, 6014, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60144, '节假日配置删除', 'hrm:attendance:delete', 3, 4, 6014, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60145, '节假日配置导出', 'hrm:attendance:export', 3, 5, 6014, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 3.3 考勤统计汇总菜单
| 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 (6015, '考勤统计', '', 2, 4, 6010, 'summary', 'ep:data-analysis', 'hrm/attendance/summary/index', 'HrmAttendanceSummary', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60151, '考勤统计查询', 'hrm:attendance:query', 3, 1, 6015, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60152, '考勤统计生成', 'hrm:attendance:calculate', 3, 2, 6015, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| 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 (60153, '考勤统计导出', 'hrm:attendance:export', 3, 3, 6015, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- 3.4 打卡权限
| 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 (60114, '打卡', 'hrm:attendance:clock', 3, 4, 6011, '', '', '', NULL, 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0');
|
| -- =============================================
| -- 4. 角色菜单关联
| -- =============================================
|
| -- 给角色 ID=1（超级管理员）分配新增的菜单权限
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 6013, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60131, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60132, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60133, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60134, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60135, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 6014, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60141, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60142, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60143, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60144, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60145, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 6015, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60151, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60152, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60153, '1', NOW(), '1', NOW(), b'0');
| INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (1, 60114, '1', NOW(), '1', NOW(), b'0');
|
| -- =============================================
| -- 5. BPM 审批分类配置（可选）
| -- =============================================
|
| -- 补卡审批分类
| INSERT INTO `bpm_category` (`id`, `name`, `code`, `description`, `status`, `sort`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
| VALUES (200, '考勤异常审批', 'hrm_attendance_approve', 'HRM考勤异常审批分类', 0, 50, '1', NOW(), '1', NOW(), b'0');
|
| -- =============================================
| -- 执行完成提示
| -- =============================================
| SELECT 'HRM 考勤管理补充 SQL 初始化完成！' AS result;
|
|
