[GH]轉結至12月底加班補休時數

------------------------------------
/*
STEP 1
先準備 HIHI_TRAN 的資料
example:

CREATE TABLE HIHI_TRAN (
PER_SERIL_NO dbo.PER_SERIL_NO
,DEP_CODE_ACT VARCHAR(100)
,DEP_NAME_ACT NVARCHAR(100)
,EMP_ID VARCHAR(100)
,CNAME NVARCHAR(100)
,FINAL_HR FLOAT
)
GO
--/***************************** HIHI_TRAN ***********************************************/
delete from HIHI_TRAN
GO
raiserror('Now Insert HIHI_TRAN Datas .... ',1,1)
insert into HIHI_TRAN ([PER_SERIL_NO],[DEP_CODE_ACT],[DEP_NAME_ACT],[EMP_ID],[CNAME],[FINAL_HR])  Values (N'P3637',N'1100',N'公○○部門',N'3637',N'馮○○',25)
insert into HIHI_TRAN ([PER_SERIL_NO],[DEP_CODE_ACT],[DEP_NAME_ACT],[EMP_ID],[CNAME],[FINAL_HR])  Values (N'P4689',N'1100',N'公○○部門',N'4689',N'林○○',0)

*/
------------------------------------

------------------------------------
/*
STEP 2
作完下面語法後，再執行
http://mrhihi-paul.blogspot.com/2011/03/gh.html
去更新補休時數
*/
------------------------------------
DECLARE @TEST CHAR(1); SET @TEST = 'N'

/* 先把上次轉檔的資料清掉,準備重轉 */
IF @TEST<>'Y'
    DELETE LOL_OT WHERE APPLY_DATE = '2010/01/01'
    AND PER_SERIL_NO IN (
    SELECT PER_SERIL_NO FROM HIHI_TRAN
    )
IF @TEST<>'Y'
    DELETE LOL_LEV WHERE APPLY_DATE ='2010/01/01'
    AND LEV_TYPE_COD = '00014'
    AND PER_SERIL_NO IN (
    SELECT PER_SERIL_NO FROM HIHI_TRAN
    )

/* 到12月底所有人加班時數(含轉檔) */
DECLARE @OT TABLE (
    PER_SERIL_NO dbo.PER_SERIL_NO
    ,OT_COMPEN_HR FLOAT
    ,OT_COMPEN_RHR FLOAT
)
INSERT INTO @OT
SELECT PER_SERIL_NO
     , SUM(OT_COMPEN_HR)
     , SUM(OT_COMPEN_RHR)
  FROM LOL_OT
 WHERE SDATE <='2011/01/01 06:00'
   AND EFORM_STATUS_COD IN ('W','P')
 GROUP BY PER_SERIL_NO

/* 到12月底所有人補休請假時數(含轉檔) */
DECLARE @LEV TABLE (
    PER_SERIL_NO dbo.PER_SERIL_NO
    ,LEV_HR FLOAT
    ,LEV_BORROW_HR FLOAT
)
INSERT INTO @LEV
SELECT PER_SERIL_NO
     , SUM(LEV_HR)
     , SUM(LEV_BORROW_HR)
  FROM LOL_LEV
 WHERE LEV_TYPE_COD = '00014'
   AND EFORM_STATUS_COD IN ('W','P')
   AND ASSIGN_DATE <= '2010/12/31'
 GROUP BY PER_SERIL_NO

DECLARE @TMP_OT TABLE(
    PER_SERIL_NO dbo.PER_SERIL_NO
    ,DEP_SERIL_NO dbo.DEP_SERIL_NO
    ,OT_HR FLOAT
)
INSERT INTO @TMP_OT
SELECT A.PER_SERIL_NO
     , [DEP_SERIL_NO]=EMP.DEP_SERIL_NO_ACT
     , [OT_HR]=A.FINAL_HR -(ISNULL(B.OT_COMPEN_HR,0) - ISNULL(C.LEV_HR,0))
  FROM HIHI_TRAN A
  JOIN EMP_EMPLOYEE EMP ON A.PER_SERIL_NO = EMP.PER_SERIL_NO
  LEFT JOIN @OT B ON A.PER_SERIL_NO = B.PER_SERIL_NO
  LEFT JOIN @LEV C ON A.PER_SERIL_NO = C.PER_SERIL_NO
 WHERE 1=1
   --AND A.FINAL_HR >= 0
   --AND (B.OT_COMPEN_HR - C.LEV_HR)>=0
   AND (ISNULL(B.OT_COMPEN_HR,0) - ISNULL(C.LEV_HR,0))<= A.FINAL_HR

IF @TEST<>'Y'
    INSERT INTO LOL_OT (FLOW_ID, FLOW_INST_ID, CMP_SERIL_NO, PER_SERIL_NO, OT_APPLY_NO, SEQ, DEP_SERIL_NO, SHIFT_CODE, OTAPPLY_KND_COD, APPLY_DATE, OT_TYPE_COD, SDATE, EDATE, OT_HR, SAGT_SERIL_NO, REASON, ORG_OT_COMPEN, EXTRA_OT_COMPEN, OT_COMPEN_HR, OT_COMPEN_DATE, OT_COMPEN_RHR, OT_FEE_HR, EFORM_STATUS_COD, IS_APPLY_EXP, CUT_FLG, CHK_FLG, KP_SDATE, KP_EDATE, KP_OT_HR, KP_ORG_OT_COMPEN, KP_OT_FEE_HR, ASSIGN_DATE, MOD_STATUS, CRE_DTE, CRE_USR, MOD_DTE, MOD_USR, MOD_REASON, RECHK_FLG, OT_CREDIT_YM)
    SELECT '','','GH100601',PER_SERIL_NO,'OT'+RTRIM(PER_SERIL_NO),1,DEP_SERIL_NO,'24','2','2010/01/01','1','2010/01/01','2010/10/31',OT_HR,'','',OT_HR,0.00,OT_HR,'2012/01/01',OT_HR,0.00,'P','N','Y','Y','2010/01/01','2010/10/31',OT_HR,OT_HR,0.00,'2010/01/01','',GETDATE(),'轉檔(20110106)',GETDATE(),'轉檔(20110106)','','N',''
    FROM @TMP_OT

-------------------------------------------
DELETE @OT
INSERT INTO @OT
SELECT PER_SERIL_NO
     , SUM(OT_COMPEN_HR)
     , SUM(OT_COMPEN_RHR)
  FROM LOL_OT
 WHERE SDATE <='2011/01/01 06:00'
   AND EFORM_STATUS_COD IN ('W','P')
 GROUP BY PER_SERIL_NO

DELETE @LEV
INSERT INTO @LEV
SELECT PER_SERIL_NO
     , SUM(LEV_HR)
     , SUM(LEV_BORROW_HR)
  FROM LOL_LEV
 WHERE LEV_TYPE_COD = '00014'
   AND EFORM_STATUS_COD IN ('W','P')
   AND ASSIGN_DATE <= '2010/12/31'
 GROUP BY PER_SERIL_NO
-------------------------------------------

DECLARE @TMP_LEV TABLE (
    PER_SERIL_NO dbo.PER_SERIL_NO
    ,LEV_HR FLOAT
)
INSERT INTO @TMP_LEV
SELECT A.PER_SERIL_NO
     , [LEV_HR]=ISNULL(B.OT_COMPEN_HR,0) - ISNULL(C.LEV_HR,0) - A.FINAL_HR
  FROM HIHI_TRAN A
  LEFT JOIN @OT B ON A.PER_SERIL_NO = B.PER_SERIL_NO
  LEFT JOIN @LEV C ON A.PER_SERIL_NO = C.PER_SERIL_NO
 WHERE A.FINAL_HR <> (ISNULL(B.OT_COMPEN_HR,0) - ISNULL(C.LEV_HR,0))

IF @TEST<>'Y'
    INSERT INTO LOL_LEV(CMP_SERIL_NO, LE_APPLY_NO, PER_SERIL_NO, SEQ, FLOW_ID, FLOW_INST_ID, LEVAPPLY_KND_COD, APPLY_DATE, DEP_SERIL_NO, SHIFT_CODE, LEV_TYPE_COD, SDATE, EDATE, LEV_MINUTE, LEV_HR, LEV_DAY, AGT_SERIL_NO, SAGT_SERIL_NO, REASON, EFORM_STATUS_COD, CUT_FLG, OTHER_OPTION1_NAME, OTHER_OPTION2_NAME, OTHER_OPTION3_NAME, IS_APPLY_TRAVEL, OCCUR_DATE, ASSIGN_DATE, MOD_STATUS, CRE_DTE, CRE_USR, MOD_DTE, MOD_USR, MOD_REASON, L_LE_APPLY_NO, LEV_BORROW_HR)
    SELECT 'GH100601','LE'+RTRIM(A.PER_SERIL_NO),A.PER_SERIL_NO,1,'','0','2','2010/01/01',B.DEP_SERIL_NO_ACT,'24','00014','2010/01/01','2010/10/31 23:59:00',LEV_HR*60,LEV_HR,LEV_HR/8,'','','','P','Y','','','','N',NULL,'2010/01/01','',GETDATE(),'轉檔(20110116)',GETDATE(),'轉檔(20110116)','',NULL,0.00
      FROM @TMP_LEV A
      JOIN EMP_EMPLOYEE B ON A.PER_SERIL_NO = B.PER_SERIL_NO

[GH]重算補休時數(針對時數不合的人)

DECLARE @IS_SHOW_ONLY YN; SELECT @IS_SHOW_ONLY = 'N'
DECLARE @重算所有人 YN; SELECT @重算所有人 = 'N'

DECLARE @OT TABLE
(
  PER_SERIL_NO PER_SERIL_NO
, OT_COMPEN_HR FLOAT
, OT_COMPEN_RHR FLOAT
, KP_ORG_OT_COMPEN FLOAT
)
DECLARE @LEV TABLE
(
  PER_SERIL_NO PER_SERIL_NO
, LEV_HR FLOAT
, LEV_BORROW_HR FLOAT
)
INSERT  INTO @OT
        SELECT  PER_SERIL_NO
              , [OT_COMPEN_HR] = SUM(OT_COMPEN_HR)
              , [OT_COMPEN_RHR] = SUM(OT_COMPEN_RHR)
              , [KP_ORG_OT_COMPEN] = SUM(KP_ORG_OT_COMPEN)
        FROM    LOL_OT
        WHERE   EFORM_STATUS_COD IN ( 'W', 'P' )
        GROUP BY PER_SERIL_NO

INSERT  INTO @LEV
        SELECT  PER_SERIL_NO
              , [LEV_HR] = SUM(LEV_HR)
              , [LEV_BORROW_HR] = SUM(LEV_BORROW_HR)
        FROM    LOL_LEV
        WHERE   EFORM_STATUS_COD IN ( 'W', 'P' )
                AND LEV_TYPE_COD = '00014'
        GROUP BY PER_SERIL_NO

DECLARE @NO_MATCH TABLE (
    PER_SERIL_NO PER_SERIL_NO
)
INSERT INTO @NO_MATCH
SELECT  A.PER_SERIL_NO
FROM    @OT A
JOIN    @LEV B ON A.PER_SERIL_NO = B.PER_SERIL_NO
WHERE   1 = 1
        AND ( A.OT_COMPEN_HR + B.LEV_BORROW_HR ) != ( A.OT_COMPEN_RHR + B.LEV_HR )

IF @IS_SHOW_ONLY ='Y' BEGIN

    SELECT  A.PER_SERIL_NO
          , A.OT_COMPEN_HR + B.LEV_BORROW_HR
          , A.OT_COMPEN_RHR + B.LEV_HR
          , A.OT_COMPEN_HR
          , A.OT_COMPEN_RHR
          , A.KP_ORG_OT_COMPEN
          , B.LEV_HR
          , B.LEV_BORROW_HR
    FROM    @OT A
    JOIN    @LEV B ON A.PER_SERIL_NO = B.PER_SERIL_NO
    JOIN    @NO_MATCH C ON A.PER_SERIL_NO = C.PER_SERIL_NO

END ELSE BEGIN
    SET NOCOUNT ON
    DELETE A
      FROM dbo.LOL_LEV_OT A
     WHERE EXISTS (SELECT 'X'
                     FROM LOL_LEV X
                    WHERE A.LE_APPLY_NO=X.LE_APPLY_NO
                      AND (EXISTS (SELECT 'X' FROM @NO_MATCH XX WHERE X.PER_SERIL_NO = XX.PER_SERIL_NO) OR @重算所有人='Y')
        )

    UPDATE A
       SET OT_COMPEN_RHR = OT_COMPEN_HR
         , MOD_REASON = REPLACE(MOD_REASON,N'加班補休假撤回',N'')
      FROM dbo.LOL_OT A
     WHERE EFORM_STATUS_COD IN ('P','W')
       AND (EXISTS (SELECT 'X' FROM @NO_MATCH XX WHERE A.PER_SERIL_NO = XX.PER_SERIL_NO) OR @重算所有人='Y')

    UPDATE A
       SET LEV_BORROW_HR = 0
      FROM LOL_LEV A
     WHERE EFORM_STATUS_COD IN ('P','W')
       AND (EXISTS (SELECT 'X' FROM @NO_MATCH XX WHERE A.PER_SERIL_NO = XX.PER_SERIL_NO) OR @重算所有人='Y')

    DECLARE @strOT_PER_SERIL_NO PER_SERIL_NO,
            @strOT_LE_APPLY_NO CHAR(20)

    DECLARE lev_cursor CURSOR FOR
    SELECT PER_SERIL_NO, LE_APPLY_NO
      FROM LOL_LEV A
     WHERE LEV_TYPE_COD = '00014'
       AND EFORM_STATUS_COD IN ('W','P')
       AND (EXISTS (SELECT 'X' FROM @NO_MATCH XX WHERE A.PER_SERIL_NO = XX.PER_SERIL_NO) OR @重算所有人='Y')
     ORDER BY PER_SERIL_NO , SDATE

    OPEN lev_cursor
    FETCH NEXT FROM lev_cursor INTO @strOT_PER_SERIL_NO, @strOT_LE_APPLY_NO
    WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC spApplyLeaveOT '', @strOT_PER_SERIL_NO, @strOT_LE_APPLY_NO, NULL, NULL, 0, 'RECALC-20110222', 0
            FETCH NEXT FROM lev_cursor INTO @strOT_PER_SERIL_NO, @strOT_LE_APPLY_NO
        END
    CLOSE lev_cursor
    DEALLOCATE lev_cursor
    SET NOCOUNT OFF
END