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