CREATE
PROCEDURE
SP_DAY_SERVICE
AS
UPDATE RES_USER
SET RES_USER.BC = CQ_HB.XBC
FROM RES_USER,CQ_HB
WHERE RES_USER.ACCOUNT_ID = CQ_HB.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_HB.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_HB WHERE DATEDIFF ( DAY ,CQ_HB.SXRQ, GETDATE ()) >= 0 -- 換班
UPDATE RES_USER SET RES_USER.ZW = CQ_JS.XZW
FROM RES_USER,CQ_JS
WHERE RES_USER.ACCOUNT_ID = CQ_JS.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_JS.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_JS WHERE DATEDIFF ( DAY ,CQ_JS.SXRQ, GETDATE ()) >= 0 -- 晉升
UPDATE RES_USER
SET RES_USER.BMID = CQ_YD.XZBM,RES_USER.KBID = CQ_YD.XZKB,
RES_USER.XBID = CQ_YD.XZZX,RES_USER.ZW = CQ_YD.XZZW
FROM RES_USER,CQ_YD
WHERE RES_USER.ACCOUNT_ID = CQ_YD.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_YD.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_YD WHERE DATEDIFF ( DAY ,CQ_YD.SXRQ, GETDATE ()) >= 0 -- 異動
UPDATE RES_USER
SET RES_USER.STATUS = 1
FROM RES_USER,CQ_LZ
WHERE RES_USER.ACCOUNT_ID = CQ_LZ.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_LZ.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_LZ WHERE DATEDIFF ( DAY ,CQ_LZ.SXRQ, GETDATE ()) >= 0 -- 離職
-- 生成崗位除名數據
INSERT INTO CQ_GWHF(ACCOUNT_ID,BM,KB,ZXB,FULL_NAME)
SELECT TMP0033.ACCOUNT_ID, RES_USER.BMID,RES_USER.KBID,RES_USER.XBID,RES_USER.FULL_NAME FROM
(
SELECT ACCOUNT_ID
FROM
(
SELECT ACCOUNT_ID, SUM (T) AS KGT
FROM
(
SELECT ACCOUNT_ID,
ST,ET, datediff (HH,ST,ET) as T,
CASE WHEN ( DATEDIFF ( DAY ,ST, GETDATE ()) >= 0 ) AND ( DATEDIFF ( DAY ,ET, GETDATE ()) <= 0 ) THEN 1 ELSE 0 END AS KG
from
(
select ACCOUNT_ID,
case when KSSJ > convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' ) then KSSJ else convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' ) end as ST,
case when JSSJ < dateadd ( day , day ( dateadd ( day , - 1 , convert ( char ( 8 ), dateadd ( month , 1 , getdate () ), 120 ) + ' 1 ' )) - 1 , convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' )) then JSSJ else dateadd ( day , day ( dateadd ( day , - 1 , convert ( char ( 8 ), dateadd ( month , 1 , getdate () ), 120 ) + ' 1 ' )) - 1 , convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' )) end as ET
from CQ_KG
where KSSJ > convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' )
) as Tmp02
) AS TMP31
GROUP BY ACCOUNT_ID
) AS TMP0032
WHERE KGT > 72 -- 三天時間
) AS TMP0033
LEFT JOIN RES_USER
ON TMP0033.ACCOUNT_ID = RES_USER.ACCOUNT_ID
/**/ /*- RES_USER 表中的STATUS
0 表示 在職
1 崗位除名狀態中
2 離職
*/
UPDATE RES_USER
SET RES_USER.STATUS = 2
FROM RES_USER,CQ_GWHF
WHERE RES_USER.ACCOUNT_ID = CQ_GWHF.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_GWHF.CMRQ, GETDATE ()) >= 15 AND CQ_GWHF.STATUS = 0 -- 崗位除名 離職
UPDATE CQ_GWHF
SET STATUS = 1
WHERE DATEDIFF ( DAY ,HFRQ, GETDATE ()) = 0 AND STATUS = 0
UPDATE RES_USER
SET RES_USER.STATUS = 0
FROM RES_USER,CQ_GWHF
WHERE RES_USER.ACCOUNT_ID = CQ_GWHF.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_GWHF.HFRQ, GETDATE ()) = 0 AND CQ_GWHF.STATUS = 1 -- 崗位恢復
UPDATE RES_USER -- 新進
SET RES_USER.CBID = CQ_XJ.XCB,RES_USER.BMID = CQ_XJ.XBM,RES_USER.KBID = CQ_XJ.XKB,RES_USER.XBID = CQ_XJ.XXB,
RES_USER.ZW = CQ_XJ.XZW,RES_USER.BC = CQ_XJ.XBC
FROM RES_USER,CQ_XJ
WHERE RES_USER.BMID = ' NA ' AND RES_USER.ACCOUNT_ID = CQ_XJ.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_XJ WHERE ACCOUNT_ID IN ( SELECT ACCOUNT_ID FROM RES_USER ) AND DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
INSERT INTO RES_USER(ACCOUNT_ID,FULL_NAME,CBID,BMID,KBID,XBID,ZW,BC)
SELECT ACCOUNT_ID,FULL_NAME,XCB,XBM,XKB,XXB,XZW,XBC
FROM CQ_XJ
WHERE DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_XJ WHERE DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
INSERT INTO MFDREPORT(ACCOUNT_ID,FULL_NAME,CBID,BMID,KBID,XBID,BC,RQ)
SELECT ACCOUNT_ID,FULL_NAME,CBID,BMID,KBID,XBID,BC, CONVERT ( NVARCHAR ( 10 ), GETDATE (), 111 ) AS RQ
FROM RES_USER
WHERE BMID <> ' NA ' AND STATUS <> 2
GO
UPDATE RES_USER
SET RES_USER.BC = CQ_HB.XBC
FROM RES_USER,CQ_HB
WHERE RES_USER.ACCOUNT_ID = CQ_HB.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_HB.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_HB WHERE DATEDIFF ( DAY ,CQ_HB.SXRQ, GETDATE ()) >= 0 -- 換班
UPDATE RES_USER SET RES_USER.ZW = CQ_JS.XZW
FROM RES_USER,CQ_JS
WHERE RES_USER.ACCOUNT_ID = CQ_JS.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_JS.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_JS WHERE DATEDIFF ( DAY ,CQ_JS.SXRQ, GETDATE ()) >= 0 -- 晉升
UPDATE RES_USER
SET RES_USER.BMID = CQ_YD.XZBM,RES_USER.KBID = CQ_YD.XZKB,
RES_USER.XBID = CQ_YD.XZZX,RES_USER.ZW = CQ_YD.XZZW
FROM RES_USER,CQ_YD
WHERE RES_USER.ACCOUNT_ID = CQ_YD.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_YD.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_YD WHERE DATEDIFF ( DAY ,CQ_YD.SXRQ, GETDATE ()) >= 0 -- 異動
UPDATE RES_USER
SET RES_USER.STATUS = 1
FROM RES_USER,CQ_LZ
WHERE RES_USER.ACCOUNT_ID = CQ_LZ.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_LZ.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_LZ WHERE DATEDIFF ( DAY ,CQ_LZ.SXRQ, GETDATE ()) >= 0 -- 離職
-- 生成崗位除名數據
INSERT INTO CQ_GWHF(ACCOUNT_ID,BM,KB,ZXB,FULL_NAME)
SELECT TMP0033.ACCOUNT_ID, RES_USER.BMID,RES_USER.KBID,RES_USER.XBID,RES_USER.FULL_NAME FROM
(
SELECT ACCOUNT_ID
FROM
(
SELECT ACCOUNT_ID, SUM (T) AS KGT
FROM
(
SELECT ACCOUNT_ID,
ST,ET, datediff (HH,ST,ET) as T,
CASE WHEN ( DATEDIFF ( DAY ,ST, GETDATE ()) >= 0 ) AND ( DATEDIFF ( DAY ,ET, GETDATE ()) <= 0 ) THEN 1 ELSE 0 END AS KG
from
(
select ACCOUNT_ID,
case when KSSJ > convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' ) then KSSJ else convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' ) end as ST,
case when JSSJ < dateadd ( day , day ( dateadd ( day , - 1 , convert ( char ( 8 ), dateadd ( month , 1 , getdate () ), 120 ) + ' 1 ' )) - 1 , convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' )) then JSSJ else dateadd ( day , day ( dateadd ( day , - 1 , convert ( char ( 8 ), dateadd ( month , 1 , getdate () ), 120 ) + ' 1 ' )) - 1 , convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' )) end as ET
from CQ_KG
where KSSJ > convert ( smalldatetime , CONVERT ( NVARCHAR ( 4 ), GETDATE (), 111 ) + ' / ' + CONVERT ( NVARCHAR ( 2 ), MONTH ( GETDATE ())) + ' /01 ' )
) as Tmp02
) AS TMP31
GROUP BY ACCOUNT_ID
) AS TMP0032
WHERE KGT > 72 -- 三天時間
) AS TMP0033
LEFT JOIN RES_USER
ON TMP0033.ACCOUNT_ID = RES_USER.ACCOUNT_ID
/**/ /*- RES_USER 表中的STATUS
0 表示 在職
1 崗位除名狀態中
2 離職
*/
UPDATE RES_USER
SET RES_USER.STATUS = 2
FROM RES_USER,CQ_GWHF
WHERE RES_USER.ACCOUNT_ID = CQ_GWHF.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_GWHF.CMRQ, GETDATE ()) >= 15 AND CQ_GWHF.STATUS = 0 -- 崗位除名 離職
UPDATE CQ_GWHF
SET STATUS = 1
WHERE DATEDIFF ( DAY ,HFRQ, GETDATE ()) = 0 AND STATUS = 0
UPDATE RES_USER
SET RES_USER.STATUS = 0
FROM RES_USER,CQ_GWHF
WHERE RES_USER.ACCOUNT_ID = CQ_GWHF.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_GWHF.HFRQ, GETDATE ()) = 0 AND CQ_GWHF.STATUS = 1 -- 崗位恢復
UPDATE RES_USER -- 新進
SET RES_USER.CBID = CQ_XJ.XCB,RES_USER.BMID = CQ_XJ.XBM,RES_USER.KBID = CQ_XJ.XKB,RES_USER.XBID = CQ_XJ.XXB,
RES_USER.ZW = CQ_XJ.XZW,RES_USER.BC = CQ_XJ.XBC
FROM RES_USER,CQ_XJ
WHERE RES_USER.BMID = ' NA ' AND RES_USER.ACCOUNT_ID = CQ_XJ.ACCOUNT_ID AND
DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_XJ WHERE ACCOUNT_ID IN ( SELECT ACCOUNT_ID FROM RES_USER ) AND DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
INSERT INTO RES_USER(ACCOUNT_ID,FULL_NAME,CBID,BMID,KBID,XBID,ZW,BC)
SELECT ACCOUNT_ID,FULL_NAME,XCB,XBM,XKB,XXB,XZW,XBC
FROM CQ_XJ
WHERE DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
DELETE FROM CQ_XJ WHERE DATEDIFF ( DAY ,CQ_XJ.SXRQ, GETDATE ()) >= 0
INSERT INTO MFDREPORT(ACCOUNT_ID,FULL_NAME,CBID,BMID,KBID,XBID,BC,RQ)
SELECT ACCOUNT_ID,FULL_NAME,CBID,BMID,KBID,XBID,BC, CONVERT ( NVARCHAR ( 10 ), GETDATE (), 111 ) AS RQ
FROM RES_USER
WHERE BMID <> ' NA ' AND STATUS <> 2
GO