-- 考勤定位字段迁移。旧规则保持 location_required=0，先配置地点再启用范围限制。
|
DELIMITER $$
|
DROP PROCEDURE IF EXISTS xgdl_hrm_attendance_add_column_20261024$$
|
CREATE PROCEDURE xgdl_hrm_attendance_add_column_20261024(
|
IN p_table_name VARCHAR(64), IN p_column_name VARCHAR(64), IN p_ddl_sql VARCHAR(1000))
|
BEGIN
|
IF NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table_name AND COLUMN_NAME = p_column_name) THEN
|
SET @hrm_location_ddl = p_ddl_sql;
|
PREPARE hrm_location_stmt FROM @hrm_location_ddl;
|
EXECUTE hrm_location_stmt;
|
DEALLOCATE PREPARE hrm_location_stmt;
|
END IF;
|
END$$
|
DELIMITER ;
|
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','location_name',
|
'ALTER TABLE hrm_attendance_rule ADD COLUMN location_name varchar(100) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','location_address',
|
'ALTER TABLE hrm_attendance_rule ADD COLUMN location_address varchar(255) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','longitude',
|
'ALTER TABLE hrm_attendance_rule ADD COLUMN longitude decimal(10,7) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','latitude',
|
'ALTER TABLE hrm_attendance_rule ADD COLUMN latitude decimal(10,7) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','radius_meters',
|
'ALTER TABLE hrm_attendance_rule ADD COLUMN radius_meters int DEFAULT 10');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','location_required',
|
'ALTER TABLE hrm_attendance_rule ADD COLUMN location_required bit(1) NOT NULL DEFAULT b''0''');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_rule','coordinate_system',
|
'ALTER TABLE hrm_attendance_rule ADD COLUMN coordinate_system varchar(20) DEFAULT ''WGS84''');
|
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_in_longitude',
|
'ALTER TABLE hrm_attendance_record ADD COLUMN clock_in_longitude decimal(10,7) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_in_latitude',
|
'ALTER TABLE hrm_attendance_record ADD COLUMN clock_in_latitude decimal(10,7) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_in_accuracy',
|
'ALTER TABLE hrm_attendance_record ADD COLUMN clock_in_accuracy decimal(8,2) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_in_distance',
|
'ALTER TABLE hrm_attendance_record ADD COLUMN clock_in_distance decimal(10,2) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_in_address',
|
'ALTER TABLE hrm_attendance_record ADD COLUMN clock_in_address varchar(255) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_out_longitude',
|
'ALTER TABLE hrm_attendance_record ADD COLUMN clock_out_longitude decimal(10,7) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_out_latitude',
|
'ALTER TABLE hrm_attendance_record ADD COLUMN clock_out_latitude decimal(10,7) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_out_accuracy',
|
'ALTER TABLE hrm_attendance_record ADD COLUMN clock_out_accuracy decimal(8,2) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_out_distance',
|
'ALTER TABLE hrm_attendance_record ADD COLUMN clock_out_distance decimal(10,2) NULL');
|
CALL xgdl_hrm_attendance_add_column_20261024('hrm_attendance_record','clock_out_address',
|
'ALTER TABLE hrm_attendance_record ADD COLUMN clock_out_address varchar(255) NULL');
|
|
DROP PROCEDURE xgdl_hrm_attendance_add_column_20261024;
|
|
-- 对齐考勤规则与打卡按钮权限码，确保 Web 和 APP 普通角色可以授权使用。
|
UPDATE `system_menu` SET `permission` = 'hrm:attendance-rule:query' WHERE `id` = 60131;
|
UPDATE `system_menu` SET `permission` = 'hrm:attendance-rule:create' WHERE `id` = 60132;
|
UPDATE `system_menu` SET `permission` = 'hrm:attendance-rule:update' WHERE `id` = 60133;
|
UPDATE `system_menu` SET `permission` = 'hrm:attendance-rule:delete' WHERE `id` = 60134;
|
UPDATE `system_menu` SET `permission` = 'hrm:attendance-rule:export' WHERE `id` = 60135;
|
|
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-record:clock', 3, 4, 6011, '', '', '', '',
|
0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0')
|
ON DUPLICATE KEY UPDATE
|
`name` = VALUES(`name`), `permission` = VALUES(`permission`), `status` = 0, `deleted` = b'0';
|
|
-- 已拥有“考勤记录”菜单的角色自动获得定位打卡按钮权限。
|
INSERT INTO `system_role_menu`
|
(`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `tenant_id`)
|
SELECT DISTINCT parent_role.`role_id`, 60114, '1', NOW(), '1', NOW(), b'0', parent_role.`tenant_id`
|
FROM `system_role_menu` parent_role
|
WHERE parent_role.`menu_id` = 6011 AND parent_role.`deleted` = b'0'
|
AND NOT EXISTS (
|
SELECT 1 FROM `system_role_menu` current_grant
|
WHERE current_grant.`role_id` = parent_role.`role_id`
|
AND current_grant.`menu_id` = 60114
|
AND current_grant.`tenant_id` = parent_role.`tenant_id`
|
AND current_grant.`deleted` = b'0'
|
);
|
