门店考勤_围栏与WiFi打卡配置.sql
2.8 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
-- 门店正常打卡：围栏 + Wi-Fi 成对配置（SSID+BSSID）
-- 可重复执行。会删除已废弃列 F_AttendanceWifiSsids、F_AttendanceWifiBssids（数据请先迁移到 F_AttendanceWifiPairs）
-- 执行前：USE 你的库名;
DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_patch_lq_mdxx_attendance_wifi`$$
CREATE PROCEDURE `sp_patch_lq_mdxx_attendance_wifi`()
BEGIN
DECLARE dbname VARCHAR(64);
SET dbname = DATABASE();
-- 1) 基础列：围栏开关、Wi-Fi 开关
IF NOT EXISTS (
SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceCheckFence'
) THEN
ALTER TABLE `lq_mdxx`
ADD COLUMN `F_AttendanceCheckFence` int NULL DEFAULT NULL
COMMENT '正常打卡是否启用围栏校验：1是 0否；NULL 表示沿用旧逻辑(有围栏则校验)'
AFTER `fence_polygons`;
END IF;
IF NOT EXISTS (
SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceCheckWifi'
) THEN
ALTER TABLE `lq_mdxx`
ADD COLUMN `F_AttendanceCheckWifi` int NULL DEFAULT NULL
COMMENT '正常打卡是否启用Wi-Fi校验：1是 0否；NULL 视为0'
AFTER `F_AttendanceCheckFence`;
END IF;
-- 2) 删除废弃列（独立 SSID / BSSID JSON 列，已由 F_AttendanceWifiPairs 替代）
IF EXISTS (
SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceWifiBssids'
) THEN
ALTER TABLE `lq_mdxx` DROP COLUMN `F_AttendanceWifiBssids`;
END IF;
IF EXISTS (
SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceWifiSsids'
) THEN
ALTER TABLE `lq_mdxx` DROP COLUMN `F_AttendanceWifiSsids`;
END IF;
-- 3) 成对配置列（加在 Wi-Fi 开关后；此时已不存在旧列）
IF NOT EXISTS (
SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceWifiPairs'
) THEN
ALTER TABLE `lq_mdxx`
ADD COLUMN `F_AttendanceWifiPairs` varchar(4000) NULL DEFAULT NULL
COMMENT 'Wi-Fi成对配置 JSON [{"ssid":"","bssid":""}]'
AFTER `F_AttendanceCheckWifi`;
END IF;
IF NOT EXISTS (
SELECT 1 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = 'lq_mdxx' AND COLUMN_NAME = 'F_AttendanceWifiVerifyPair'
) THEN
ALTER TABLE `lq_mdxx`
ADD COLUMN `F_AttendanceWifiVerifyPair` int NULL DEFAULT 0
COMMENT '1=校验SSID与BSSID为同一AP；0=SSID或BSSID命中任一行即可'
AFTER `F_AttendanceWifiPairs`;
END IF;
END$$
DELIMITER ;
CALL `sp_patch_lq_mdxx_attendance_wifi`();
DROP PROCEDURE IF EXISTS `sp_patch_lq_mdxx_attendance_wifi`;