PT BIA

ALTER PROCEDURE CBIATMTOPYPOTKEHADIRAN

(

	@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 = 'CBIATMTOPYPOTKEHADIRANBASE'

DECLARE @AbsType VARCHAR(250)

SELECT @AbsType = Value1

FROM dbo.PCMEPGENPARAM WITH (NOLOCK)

WHERE Parameter = 'CBIATMTOPYPOTKEHADIRANABSENCETYPE'

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 PotonganKehadiran

FROM @TResult