ALTER PROCEDURE CBIATMTOPYPOTKESEHATAN
(
	@EmployeeID VARCHAR(20)
	,@StartDate VARCHAR(20)
	,@EndDate VARCHAR(20)
)
AS

--DECLARE @EmployeeID VARCHAR(20) = '17010491'
--DECLARE @StartDate VARCHAR(20) = '20221201'
--DECLARE @EndDate VARCHAR(20) = '20221231'

DECLARE @WTBase VARCHAR(250)
SELECT @WTBase = Value1
FROM dbo.PCMEPGENPARAM WITH (NOLOCK)
WHERE Parameter = 'CBIATMTOPYPOTKESEHATANBASE'

DECLARE @AbsType VARCHAR(250)
SELECT @AbsType = Value1
FROM dbo.PCMEPGENPARAM WITH (NOLOCK)
WHERE Parameter = 'CBIATMTOPYPOTKESEHATANABSENCETYPE'

DECLARE @BaseAmount DECIMAL(22,0) = 0
SELECT DISTINCT @BaseAmount = cu304.Amount
FROM dbo.PHRPA0003 AS pa3 WITH (NOLOCK)
LEFT JOIN dbo.PHRPA0004 AS pa4 WITH (NOLOCK)
	ON pa3.EmployeeID = pa4.EmployeeID
	AND pa4.StartDate <= @EndDate AND pa4.EndDate >= @EndDate
LEFT JOIN dbo.PHRPYCU0304 AS cu304 WITH (NOLOCK)
	ON (CASE WHEN cu304.PayrollType = '*' THEN pa3.PayrollType ELSE cu304.PayrollType END) = pa3.PayrollType
	AND (CASE WHEN cu304.PayrollArea = '*' THEN pa3.PayrollArea ELSE cu304.PayrollArea END) = pa3.PayrollArea
	AND (CASE WHEN cu304.PayrollClass = '*' THEN pa3.PayrollClass ELSE cu304.PayrollClass END) = pa3.PayrollClass
	AND (CASE WHEN cu304.PayrollGrade = '*' THEN pa3.PayrollGrade ELSE cu304.PayrollGrade END) = pa3.PayrollGrade
	AND pa4.WageType = cu304.WageType
WHERE pa3.EmployeeID = @EmployeeID
	AND pa3.StartDate <= @EndDate AND pa3.EndDate >= @EndDate
	AND pa4.WageType IN (SELECT DISTINCT * FROM dbo.Split(',', @WTBase))

SELECT DISTINCT @BaseAmount = @BaseAmount + cu304.Amount
FROM dbo.PHRPA0003 AS pa3 WITH (NOLOCK)
LEFT JOIN dbo.PHRPA0005 AS pa5 WITH (NOLOCK)
	ON pa3.EmployeeID = pa5.EmployeeID
	AND pa5.StartDate <= @EndDate AND pa5.EndDate >= @EndDate
LEFT JOIN dbo.PHRPYCU0304 AS cu304 WITH (NOLOCK)
	ON (CASE WHEN cu304.PayrollType = '*' THEN pa3.PayrollType ELSE cu304.PayrollType END) = pa3.PayrollType
	AND (CASE WHEN cu304.PayrollArea = '*' THEN pa3.PayrollArea ELSE cu304.PayrollArea END) = pa3.PayrollArea
	AND (CASE WHEN cu304.PayrollClass = '*' THEN pa3.PayrollClass ELSE cu304.PayrollClass END) = pa3.PayrollClass
	AND (CASE WHEN cu304.PayrollGrade = '*' THEN pa3.PayrollGrade ELSE cu304.PayrollGrade END) = pa3.PayrollGrade
	AND pa5.WageType = cu304.WageType
WHERE pa3.EmployeeID = @EmployeeID
	AND pa3.StartDate <= @EndDate AND pa3.EndDate >= @EndDate
	AND pa5.WageType IN (SELECT DISTINCT * FROM dbo.Split(',', @WTBase))

DECLARE @TWSCALEMP TABLE
(
	EmployeeID VARCHAR(20)
	,WSType VARCHAR(20)
	,DateSpecified VARCHAR(20)
	,DayType VARCHAR(20)
	,Flag VARCHAR(20)
)

INSERT INTO @TWSCALEMP
SELECT DISTINCT pa25.EmployeeID, pa25.WorkScheduleType, wscal.DateSpecified, wscal.DayType, daytype.Flag
FROM dbo.PHRPA0025 AS pa25 WITH (NOLOCK)
LEFT JOIN dbo.PHRTMWSCAL AS wscal WITH (NOLOCK)
	ON wscal.WorkScheduleType = pa25.WorkScheduleType
LEFT JOIN dbo.PHRTMDAYTYP AS daytype
	ON wscal.DayType = daytype.DayType
WHERE EmployeeID = @EmployeeID
	AND pa25.StartDate <= @EndDate AND pa25.EndDate = @EndDate
	AND (wscal.DateSpecified BETWEEN @StartDate AND @EndDate)

DECLARE @TPA18 TABLE
(
	StartDate VARCHAR(20)
	,EndDate VARCHAR(20)
	,EmployeeID VARCHAR(20)
	,AbsenceType VARCHAR(20)
	,DateSpecified VARCHAR(20)
)

INSERT INTO @TPA18
SELECT DISTINCT pa18.StartDate, pa18.EndDate, pa18.EmployeeID, pa18.AbsenceType, wscal.DateSpecified
FROM dbo.PHRPA0018 AS pa18 WITH (NOLOCK)
LEFT JOIN @TWSCALEMP AS wscal
	ON wscal.DateSpecified BETWEEN pa18.StartDate AND pa18.EndDate
WHERE pa18.EmployeeID = @EmployeeID
	AND (pa18.StartDate BETWEEN @StartDate AND @EndDate OR pa18.EndDate BETWEEN @StartDate AND @EndDate)
	AND wscal.Flag <> 'FREE'
	
DECLARE @TPA19 TABLE
(
	StartDate VARCHAR(20)
	,EndDate VARCHAR(20)
	,EmployeeID VARCHAR(20)
	,AbsenceType VARCHAR(20)
	,DateSpecified VARCHAR(20)
)

INSERT INTO @TPA19
SELECT DISTINCT pa19.StartDate, pa19.EndDate, pa19.EmployeeID, pa19.AttendanceType, wscal.DateSpecified
FROM dbo.PHRPA0019 AS pa19 WITH (NOLOCK)
LEFT JOIN @TWSCALEMP AS wscal
	ON wscal.DateSpecified BETWEEN pa19.StartDate AND pa19.EndDate
WHERE pa19.EmployeeID = @EmployeeID
	AND (pa19.StartDate BETWEEN @StartDate AND @EndDate OR pa19.EndDate BETWEEN @StartDate AND @EndDate)
	AND wscal.Flag <> 'FREE'

DECLARE @TResult TABLE
(
	EmployeeID VARCHAR(20)
	,AbsType VARCHAR(50)
	,SumAbsence DECIMAL(18,0)
)

INSERT INTO @TResult
SELECT DISTINCT wscal.EmployeeID, 'mangkir' AS AbsType, COUNT(wscal.DateSpecified) AS mangkir
FROM @TWSCALEMP AS wscal
WHERE wscal.DateSpecified NOT IN ( SELECT DISTINCT DateSpecified FROM @TPA18)
	AND wscal.DateSpecified NOT IN ( SELECT DISTINCT DateSpecified FROM @TPA19)
	AND wscal.Flag <> 'FREE'
GROUP BY wscal.EmployeeID

INSERT INTO @TResult
SELECT DISTINCT EmployeeID, 'izin sakit' AS AbsType, COUNT([@TPA18].DateSpecified)
FROM @TPA18 
WHERE AbsenceType IN (SELECT DISTINCT * FROM dbo.Split(',', @AbsType))
GROUP BY EmployeeID

SELECT SUM(SumAbsence) AS SumAbsence , @BaseAmount AS AmountBase, SUM(SumAbsence) * @BaseAmount AS PotonganKesehatan
FROM @TResult