考勤打卡记录表新增外勤定位照片补卡字段.sql
8.67 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
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
-- 考勤打卡记录表新增外勤/定位/照片/补卡字段
-- 执行库：lqerp_dev / lqerp
-- 说明：用于真实打卡、外勤打卡、地图查看和后台补卡
SET @db_name = DATABASE();
-- 1. 上下班打卡类型
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchInType') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchInType` int DEFAULT NULL COMMENT '上班打卡类型（1正常上班，2外勤）' AFTER `F_PunchOutTime`;",
"SELECT 'lq_attendance_record.F_PunchInType 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchOutType') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchOutType` int DEFAULT NULL COMMENT '下班打卡类型（1正常上班，2外勤）' AFTER `F_PunchInType`;",
"SELECT 'lq_attendance_record.F_PunchOutType 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 2. 上班定位、地址、照片
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchInLongitude') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchInLongitude` decimal(18,6) DEFAULT NULL COMMENT '上班打卡经度' AFTER `F_PunchOutType`;",
"SELECT 'lq_attendance_record.F_PunchInLongitude 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchInLatitude') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchInLatitude` decimal(18,6) DEFAULT NULL COMMENT '上班打卡纬度' AFTER `F_PunchInLongitude`;",
"SELECT 'lq_attendance_record.F_PunchInLatitude 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchInAddress') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchInAddress` varchar(500) DEFAULT NULL COMMENT '上班打卡地址' AFTER `F_PunchInLatitude`;",
"SELECT 'lq_attendance_record.F_PunchInAddress 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchInPhotoUrl') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchInPhotoUrl` varchar(500) DEFAULT NULL COMMENT '上班打卡照片地址' AFTER `F_PunchInAddress`;",
"SELECT 'lq_attendance_record.F_PunchInPhotoUrl 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 3. 下班定位、地址、照片
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchOutLongitude') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchOutLongitude` decimal(18,6) DEFAULT NULL COMMENT '下班打卡经度' AFTER `F_PunchInPhotoUrl`;",
"SELECT 'lq_attendance_record.F_PunchOutLongitude 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchOutLatitude') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchOutLatitude` decimal(18,6) DEFAULT NULL COMMENT '下班打卡纬度' AFTER `F_PunchOutLongitude`;",
"SELECT 'lq_attendance_record.F_PunchOutLatitude 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchOutAddress') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchOutAddress` varchar(500) DEFAULT NULL COMMENT '下班打卡地址' AFTER `F_PunchOutLatitude`;",
"SELECT 'lq_attendance_record.F_PunchOutAddress 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_PunchOutPhotoUrl') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_PunchOutPhotoUrl` varchar(500) DEFAULT NULL COMMENT '下班打卡照片地址' AFTER `F_PunchOutAddress`;",
"SELECT 'lq_attendance_record.F_PunchOutPhotoUrl 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 4. 围栏校验结果
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_IsPunchInFenceValid') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_IsPunchInFenceValid` int DEFAULT NULL COMMENT '上班打卡是否通过围栏校验（1通过0未通过）' AFTER `F_PunchOutPhotoUrl`;",
"SELECT 'lq_attendance_record.F_IsPunchInFenceValid 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_IsPunchOutFenceValid') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_IsPunchOutFenceValid` int DEFAULT NULL COMMENT '下班打卡是否通过围栏校验（1通过0未通过）' AFTER `F_IsPunchInFenceValid`;",
"SELECT 'lq_attendance_record.F_IsPunchOutFenceValid 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 5. 早退分钟
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_EarlyLeaveMinutes') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_EarlyLeaveMinutes` int NOT NULL DEFAULT 0 COMMENT '早退分钟数' AFTER `F_LateMinutes`;",
"SELECT 'lq_attendance_record.F_EarlyLeaveMinutes 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
-- 6. 补卡信息
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_SupplementRemark') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_SupplementRemark` varchar(500) DEFAULT NULL COMMENT '补卡备注' AFTER `F_Remark`;",
"SELECT 'lq_attendance_record.F_SupplementRemark 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_SupplementOperatorId') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_SupplementOperatorId` varchar(50) DEFAULT NULL COMMENT '补卡操作人ID' AFTER `F_SupplementRemark`;",
"SELECT 'lq_attendance_record.F_SupplementOperatorId 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_SupplementOperatorName') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_SupplementOperatorName` varchar(100) DEFAULT NULL COMMENT '补卡操作人姓名' AFTER `F_SupplementOperatorId`;",
"SELECT 'lq_attendance_record.F_SupplementOperatorName 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_SupplementTime') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_SupplementTime` datetime DEFAULT NULL COMMENT '补卡操作时间' AFTER `F_SupplementOperatorName`;",
"SELECT 'lq_attendance_record.F_SupplementTime 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql = IF(
(SELECT COUNT(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'lq_attendance_record' AND COLUMN_NAME = 'F_SupplementWorkflowId') = 0,
"ALTER TABLE `lq_attendance_record` ADD COLUMN `F_SupplementWorkflowId` varchar(50) DEFAULT NULL COMMENT '关联补卡流程ID' AFTER `F_SupplementTime`;",
"SELECT 'lq_attendance_record.F_SupplementWorkflowId 已存在';"
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;