需求：
公司經常加班通宵，日常不算加班（不寫加班流程），如果淩晨6點走的，那麼當天可以不用來上班。
初始方案一：加班自動調休；方案二：加班自動外出；方案三：自動生成打卡記錄（采納）
方案：
一、晚走晚到，班次配置；
1.下班卡是21:00點後，允許晚到30分鐘 9:30；
2.下班卡是23:00點後，允許晚到60分鐘10:00；
二、打卡規則，班次配置（标準支援三條規則）
1.上班時間9點，120分鐘前開始打卡，479分鐘結束打卡；
2.下班時間18點，60分鐘前開始打卡，779分鐘結束打卡；
三、自動生成打卡記錄，根據打卡記錄輸出視圖，同步插入。
1.0:30---2:00 生成9：00和13:30
2.2:00---3:00 生成9：00和14:00
3.3:00---4:00 生成9：00和15:00
4.4:00---5:00 生成9：00和16:00
5.5:00---6:00 生成9：00和17:00
6.6:00---7:00 生成9：00和18:00
7.附加規則：1--5，如果9---18點沒有打卡記錄的，不生成打卡記錄。
8.附加規則：address 字段後拼接"晚走晚到淩晨後自動生成打卡記錄"字樣。
自動生成打卡記錄視圖輸出過程：
1.需求字段：人員編号,簽到日期,簽到時間，經度，緯度，地理位置
2.表：hrmschedulesign 人員ID，簽到日期，簽到時間，經度，緯度，位址位置
SELECT
USERID ,
SIGNDATE ,
SIGNTIME ,
ADDR ,
LONGITUDE,
LATITUDE
FROM
HRMSCHEDULESIGN
3.表：hrmresource 人員ID，人員編号，姓名，部門，分部。
SELECT ID, WORKCODE, LASTNAME, DEPARTMENTID, SUBCOMPANYID1 FROM HRMRESOURCE
4.找出0點半---7點的打卡記錄，轉為9點；
SELECT
USERID ,
SIGNDATE ,
'09:00:00' AS SIGNTIME,
ADDR ,
LONGITUDE ,
LATITUDE ,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'07:00:00'
5.對照表生成下午打卡記錄（注意02:00:00 是24小時制，2:00:00 是12小時制）
SELECT
USERID,
SIGNDATE,
CASE WHEN SIGNTIME <'02:00:00' THEN '13:30:00'
WHEN SIGNTIME <'03:00:00' THEN '14:00:00'
WHEN SIGNTIME <'04:00:00' THEN '15:00:00'
WHEN SIGNTIME <'05:00:00' THEN '16:00:00'
WHEN SIGNTIME <'06:00:00' THEN '17:00:00'
WHEN SIGNTIME <'07:00:00' THEN '18:00:00' ELSE SIGNTIME END AS SIGNTIME,
ADDR ,
LONGITUDE,
LATITUDE,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'07:00:00'
6.查詢7---18點是否有打卡記錄
SELECT
USERID
||'-'
||SIGNDATE
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
7.自動生成9點打卡記錄，增加附加規則
SELECT
USERID ,
SIGNDATE ,
'09:00:00' AS SIGNTIME,
ADDR
||'晚走晚到淩晨簽卡自動生成的打卡記錄' as ADDR,
LONGITUDE ,
LATITUDE ,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'18:00:00'
AND USERID
||'-'
||SIGNDATE IN
(
SELECT
USERID
||'-'
||SIGNDATE
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
)
8.對照表生成下午打卡記錄，增加附加規則
SELECT
USERID,
SIGNDATE,
CASE WHEN SIGNTIME <'02:00:00' THEN '13:30:00'
WHEN SIGNTIME <'03:00:00' THEN '14:00:00'
WHEN SIGNTIME <'04:00:00' THEN '15:00:00'
WHEN SIGNTIME <'05:00:00' THEN '16:00:00'
WHEN SIGNTIME <'06:00:00' THEN '17:00:00'
WHEN SIGNTIME <'07:00:00' THEN '18:00:00' ELSE SIGNTIME END AS SIGNTIME,
ADDR
||'晚走晚到淩晨簽卡自動生成的打卡記錄' as ADDR,
LONGITUDE,
LATITUDE,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'07:00:00'
AND USERID
||'-'
||SIGNDATE IN
(
SELECT
USERID
||'-'
||SIGNDATE
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
)
9.兩次記錄拼接UNION ALL
SELECT
USERID ,
SIGNDATE ,
'09:00:00' AS SIGNTIME,
ADDR
||'晚走晚到淩晨簽卡自動生成的打卡記錄' as ADDR,
LONGITUDE ,
LATITUDE ,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'07:00:00'
AND USERID
||'-'
||SIGNDATE IN
(
SELECT
USERID
||'-'
||SIGNDATE
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
) UNION ALL
SELECT
USERID,
SIGNDATE,
CASE WHEN SIGNTIME <'02:00:00' THEN '13:30:00'
WHEN SIGNTIME <'03:00:00' THEN '14:00:00'
WHEN SIGNTIME <'04:00:00' THEN '15:00:00'
WHEN SIGNTIME <'05:00:00' THEN '16:00:00'
WHEN SIGNTIME <'06:00:00' THEN '17:00:00'
WHEN SIGNTIME <'07:00:00' THEN '18:00:00' ELSE SIGNTIME END AS SIGNTIME,
ADDR
||'晚走晚到淩晨簽卡自動生成的打卡記錄' as ADDR,
LONGITUDE,
LATITUDE,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'07:00:00'
AND USERID
||'-'
||SIGNDATE IN
(
SELECT
USERID
||'-'
||SIGNDATE
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
12.拼接人員資訊
SELECT
H.WORKCODE ,
H.LASTNAME ,
H.DEPARTMENTID ,
H.SUBCOMPANYID1,
T.USERID ,
T.SIGNDATE ,
T.SIGNTIME ,
T.ADDR ,
T.LONGITUDE ,
T.LATITUDE ,
T.YSIGNTIME
FROM
(SELECT
USERID ,
SIGNDATE ,
'09:00:00' AS SIGNTIME,
ADDR
||'晚走晚到淩晨簽卡自動生成的打卡記錄' as ADDR,
LONGITUDE ,
LATITUDE ,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'18:00:00'
AND USERID
||'-'
||SIGNDATE IN
(
SELECT
USERID
||'-'
||SIGNDATE
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
) UNION ALL
SELECT
USERID,
SIGNDATE,
CASE WHEN SIGNTIME <'02:00:00' THEN '13:30:00'
WHEN SIGNTIME <'03:00:00' THEN '14:00:00'
WHEN SIGNTIME <'04:00:00' THEN '15:00:00'
WHEN SIGNTIME <'05:00:00' THEN '16:00:00'
WHEN SIGNTIME <'06:00:00' THEN '17:00:00'
WHEN SIGNTIME <'07:00:00' THEN '18:00:00' ELSE SIGNTIME END AS SIGNTIME,
ADDR
||'晚走晚到淩晨簽卡自動生成的打卡記錄' as ADDR,
LONGITUDE,
LATITUDE,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'18:00:00'
AND USERID
||'-'
||SIGNDATE IN
(
SELECT
USERID
||'-'
||SIGNDATE
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
)
)
T,
HRMRESOURCE H
WHERE
H.ID=T.USERID
11.建立視圖
CREATE VIEW KQ_WZWDAUTOSIGN AS SELECT
H.WORKCODE ,
H.LASTNAME ,
H.DEPARTMENTID ,
H.SUBCOMPANYID1,
T.USERID ,
T.SIGNDATE ,
T.SIGNTIME ,
T.ADDR ,
T.LONGITUDE ,
T.LATITUDE ,
T.YSIGNTIME
FROM
(SELECT
USERID ,
SIGNDATE ,
'09:00:00' AS SIGNTIME,
ADDR
||'晚走晚到淩晨簽卡自動生成的打卡記錄' as ADDR,
LONGITUDE ,
LATITUDE ,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'18:00:00'
AND USERID
||'-'
||SIGNDATE IN
(
SELECT
USERID
||'-'
||SIGNDATE
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
) UNION ALL
SELECT
USERID,
SIGNDATE,
CASE WHEN SIGNTIME <'02:00:00' THEN '13:30:00'
WHEN SIGNTIME <'03:00:00' THEN '14:00:00'
WHEN SIGNTIME <'04:00:00' THEN '15:00:00'
WHEN SIGNTIME <'05:00:00' THEN '16:00:00'
WHEN SIGNTIME <'06:00:00' THEN '17:00:00'
WHEN SIGNTIME <'07:00:00' THEN '18:00:00' ELSE SIGNTIME END AS SIGNTIME,
ADDR
||'晚走晚到淩晨簽卡自動生成的打卡記錄' as ADDR,
LONGITUDE,
LATITUDE,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'18:00:00'
AND USERID
||'-'
||SIGNDATE IN
(
SELECT
USERID
||'-'
||SIGNDATE
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
)
)
T,
HRMRESOURCE H
WHERE
H.ID=T.USERID;
12.查詢測試：
select * from KQ_WZWDAUTOSIGN
13.優化主鍵重複：做個主鍵,優化MYSQL下字元串拼接
CREATE OR REPLACE VIEW KQ_WZWDAUTOSIGN AS SELECT
CONCAT(T.USERID,T.SIGNDATE ,T.SIGNTIME ,'-',T.ID) AS IID,
H.WORKCODE ,
H.LASTNAME ,
H.DEPARTMENTID ,
H.SUBCOMPANYID1,
T.USERID ,
T.SIGNDATE ,
T.SIGNTIME ,
T.ADDR ,
T.LONGITUDE ,
T.LATITUDE ,
T.YSIGNTIME
FROM
(SELECT ID,
USERID ,
SIGNDATE ,
'09:00:00' AS SIGNTIME,
CONCAT(ADDR,'晚走晚到淩晨簽卡自動生成的打卡記錄') as ADDR,
LONGITUDE ,
LATITUDE ,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'07:00:00'
AND CONCAT(USERID
,'-'
,SIGNDATE) IN
(
SELECT
CONCAT(USERID
,'-'
,SIGNDATE)
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
) UNION ALL
SELECT ID,
USERID,
SIGNDATE,
CASE WHEN SIGNTIME <'02:00:00' THEN '13:30:00'
WHEN SIGNTIME <'03:00:00' THEN '14:00:00'
WHEN SIGNTIME <'04:00:00' THEN '15:00:00'
WHEN SIGNTIME <'05:00:00' THEN '16:00:00'
WHEN SIGNTIME <'06:00:00' THEN '17:00:00'
WHEN SIGNTIME <'07:00:00' THEN '18:00:00' ELSE SIGNTIME END AS SIGNTIME,
CONCAT(ADDR,'晚走晚到淩晨簽卡自動生成的打卡記錄') as ADDR,
LONGITUDE,
LATITUDE,
SIGNTIME AS YSIGNTIME
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'00:30:00'
AND SIGNTIME<'07:00:00'
AND CONCAT(USERID
,'-'
,SIGNDATE) IN
(
SELECT
CONCAT(USERID
,'-'
,SIGNDATE)
FROM
HRMSCHEDULESIGN
WHERE
SIGNTIME>'07:00:00'
AND SIGNTIME<'18:00:00'
)
)
T,
HRMRESOURCE H
WHERE
H.ID=T.USERID;
虛拟表效果圖：
同步配置：