[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

[GH]補有排假但沒有請假檔

DECLARE @PER_SERIL_NO PER_SERIL_NO
      , @LOL_DATE DATETIME
      , @SHIFT_SDATE DATETIME
      , @USER_ID USRID

BEGIN TRAN

    DECLARE vendor_cursor CURSOR Local FAST_FORWARD READ_ONLY FOR
        SELECT PER_SERIL_NO , LOL_DATE , SHIFT_SDATE , '(20101124)'
        FROM dbo.LOL_DAYSHIFT X
        WHERE X.SHIFT_CODE='X1' AND ISNULL(X.LE_APPLY_NO,'')=''
    OPEN vendor_cursor
    FETCH NEXT FROM vendor_cursor
    INTO @PER_SERIL_NO , @LOL_DATE , @SHIFT_SDATE , @USER_ID
    WHILE @@FETCH_STATUS = 0
    BEGIN

            exec dbo.uspInsertLevByShiftLev @PER_SERIL_NO=@PER_SERIL_NO
            , @LOL_DATE=@LOL_DATE
            , @SHIFT_SDATE=@SHIFT_SDATE
            , @strUSR_ID=@USER_ID

        FETCH NEXT FROM vendor_cursor
        INTO @PER_SERIL_NO , @LOL_DATE , @SHIFT_SDATE , @USER_ID
    END
    CLOSE vendor_cursor
    DEALLOCATE vendor_cursor

COMMIT

[UNIX] Terminal mode 下找透過 WINS 用名稱找 IP

linux 下

nmblookup -R -U 172.16.1.2 hrispd-t

mac LION 下

smbutil lookup hrispd-t

[GH] 檢查加班補休時數是否一致

DECLARE @OT TABLE
(
  PER_SERIL_NO PER_SERIL_NO
, OT_COMPEN_HR FLOAT
, OT_COMPEN_RHR 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)
        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

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
      , B.LEV_HR
      , B.LEV_BORROW_HR
FROM    @OT A
JOIN    @LEV B ON A.PER_SERIL_NO = B.PER_SERIL_NO
WHERE   1 = 1
--AND A.PER_SERIL_NO = 'some one'
        AND ( A.OT_COMPEN_HR + B.LEV_BORROW_HR ) != ( A.OT_COMPEN_RHR + B.LEV_HR )

SELECT  *
FROM    LOL_OT
WHERE   PER_SERIL_NO = 'some one'
SELECT  *
FROM    LOL_LEV
WHERE   PER_SERIL_NO = 'some one'
        AND LEV_TYPE_COD = '00014'
        AND EFORM_STATUS_COD IN ( 'W', 'P' )
SELECT  *
FROM    LOL_LEV_OT
WHERE   OT_APPLY_NO = 'some one'

[GH] 重算補休資料(個人)

BEGIN TRAN

    DECLARE @PER_SERIL_NO PER_SERIL_NO; SET @PER_SERIL_NO= ''

    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 dbo.ufnIsContains(X.PER_SERIL_NO,@PER_SERIL_NO,',','Y')='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 dbo.ufnIsContains(A.PER_SERIL_NO,@PER_SERIL_NO,',','Y')='Y'

    UPDATE A
       SET LEV_BORROW_HR = 0
      FROM LOL_LEV A
     WHERE EFORM_STATUS_COD IN ('P','W')
       AND dbo.ufnIsContains(A.PER_SERIL_NO,@PER_SERIL_NO,',','Y')='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 
     WHERE LEV_TYPE_COD = '00014' 
       AND EFORM_STATUS_COD IN ('W','P')
       AND dbo.ufnIsContains(PER_SERIL_NO,@PER_SERIL_NO,',','Y')='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, 'HIHI', 0
            FETCH NEXT FROM lev_cursor INTO @strOT_PER_SERIL_NO, @strOT_LE_APPLY_NO
        END
    CLOSE lev_cursor
    DEALLOCATE lev_cursor

--ROLLBACK
--COMMIT