96 lines
3.2 KiB
Plaintext
96 lines
3.2 KiB
Plaintext
CREATE PROC dbo.GEX_CALCSALARY
|
|
(
|
|
@P1 VARCHAR(100),
|
|
@P2 VARCHAR(100),
|
|
@P3 VARCHAR(100),
|
|
@P4 VARCHAR(100),
|
|
@P5 VARCHAR(100),
|
|
@P6 VARCHAR(100),
|
|
@P7 VARCHAR(100),
|
|
@P8 VARCHAR(100),
|
|
@P9 VARCHAR(100), --預留
|
|
@P10 VARCHAR(100), --預留
|
|
@P11 VARCHAR(100),
|
|
@P12 VARCHAR(100),
|
|
@LOGINID VARCHAR(30)
|
|
)
|
|
/*
|
|
Function : 將薪資計算由前端程式改寫為 SP
|
|
Description: 利用參數切換, 可以保留原程式, 或改為 SP 計算
|
|
@P1 人員起
|
|
@P2 人員迄
|
|
@P3 部門起
|
|
@P4 部門迄
|
|
@P5 日期起
|
|
@P6 日期迄
|
|
@P7 薪資年月
|
|
@P8 薪資期數
|
|
@P11 特殊日期
|
|
@P12 特殊日期
|
|
@LOGINID 登入ID(少用)
|
|
|
|
Build Date : 2013/05/23
|
|
|
|
Modify History
|
|
Item Who Date Modify Docs
|
|
==== ========== ========== ================================================
|
|
|
|
*/
|
|
AS
|
|
|
|
IF @P1 = '*' SET @P1 = ''
|
|
IF @P2 = '*' SET @P2 = 'zzzzzz'
|
|
IF @P3 = '*' SET @P3 = ''
|
|
IF @P4 = '*' SET @P4 = 'zzzzzz'
|
|
IF @P7 = '*' SET @P7 = dbo.CALC_YEARMONTH(@P5)
|
|
IF @P8 = '*' SET @P8 = '0'
|
|
|
|
--0.刪除當月當期資料
|
|
DELETE FROM ESSYEARFINAL WHERE YEARMONTH = @P7 AND SALTERM = @P8 AND PERSONID >= @P1 AND PERSONID <= @P2
|
|
DELETE FROM ESSCALCFORMULA_LOG --WHERE LOGINID = @LOGINID
|
|
|
|
--1.將薪資變數, 執行後填入暫存檔備用 ESS_TMPC1 LOGINID,VARID,PERSONID,RESULT_VALUE
|
|
EXEC GEX_CALCSALARY_STEP1 @P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@LOGINID
|
|
|
|
--2.呼叫 GEX_SALFORMULA_V3, 展開每一個 user 每個科目的計算公式(loop)
|
|
EXEC GEX_CALCSALARY_STEP2 @P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@LOGINID;
|
|
|
|
--計算後, 整理 LOG 檔的內容, 寫回年檔(人/科目的最末筆)
|
|
WITH ESS1
|
|
AS
|
|
(
|
|
SELECT PERSONID,ESS_SUBJECTID,SALTERM,MAX(SEQNO) SEQNO FROM ESSCALCFORMULA_LOG
|
|
WHERE LOGINID = @LOGINID AND YEARMONTH = @P7 AND SALTERM = @P8
|
|
GROUP BY PERSONID,ESS_SUBJECTID,SALTERM
|
|
), ESS2
|
|
AS
|
|
(
|
|
SELECT A.PERSONID,A.ESS_SUBJECTID,A.SEQNO,ISNULL(B.CALCAMOUNT,0) CALCAMOUNT FROM ESS1 A
|
|
LEFT JOIN ESSCALCFORMULA_LOG B ON A.PERSONID = B.PERSONID AND A.ESS_SUBJECTID = B.ESS_SUBJECTID AND A.SALTERM = B.SALTERM AND A.SEQNO = B.SEQNO
|
|
WHERE B.LOGINID = @LOGINID AND B.YEARMONTH = @P7 AND B.SALTERM = @P8
|
|
)
|
|
|
|
INSERT INTO ESSYEARFINAL (PERSONID,YEARMONTH,SALTERM,ESS_SUBJECTID,SALARY)
|
|
SELECT DISTINCT PERSONID,@P7,@P8,ESS_SUBJECTID,CALCAMOUNT
|
|
FROM ESS2
|
|
|
|
--還有非固定薪要 Update 回去
|
|
SELECT PERSONID,ESS_SUBJECTID,SALTERM,SUM(SALARY) SALARY
|
|
INTO #TMP1 FROM ESSNONEFIXSAL
|
|
WHERE PERSONID >= @P1 AND PERSONID <= @P2 AND WDAY >= @P5 AND WDAY <= @P6 AND SALTERM = @P8
|
|
GROUP BY PERSONID,ESS_SUBJECTID,SALTERM
|
|
|
|
--若已存在, 則累加
|
|
UPDATE ESSYEARFINAL SET SALARY = SALARY + ISNULL((SELECT SALARY FROM #TMP1 WHERE ESSYEARFINAL.PERSONID = #TMP1.PERSONID AND ESSYEARFINAL.YEARMONTH = @P7 AND ESSYEARFINAL.SALTERM = @P8 AND ESSYEARFINAL.ESS_SUBJECTID = #TMP1.ESS_SUBJECTID),0)
|
|
WHERE PERSONID >= @P1 AND PERSONID <= @P2 AND YEARMONTH = @P7 AND SALTERM = @P8
|
|
|
|
INSERT INTO ESSYEARFINAL (PERSONID,YEARMONTH,SALTERM,ESS_SUBJECTID,SALARY)
|
|
SELECT PERSONID,@P7,@P8,ESS_SUBJECTID,SALARY
|
|
FROM #TMP1
|
|
WHERE NOT EXISTS
|
|
(
|
|
SELECT * FROM ESSYEARFINAL WHERE ESSYEARFINAL.PERSONID = #TMP1.PERSONID AND ESSYEARFINAL.YEARMONTH = @P7 AND ESSYEARFINAL.SALTERM = @P8 AND ESSYEARFINAL.ESS_SUBJECTID = #TMP1.ESS_SUBJECTID
|
|
)
|
|
|
|
DROP TABLE #TMP1
|