PT BIA

--ALTER PROCEDURE CBIARPTPYPAYSLIPABSENCE

--(

--    @PayPeriod VARCHAR(6) ,

--    @EmpID VARCHAR(20) ,

--    @Running VARCHAR(2)

--)

--AS

DECLARE @PayPeriod VARCHAR(6) = '202302'

DECLARE @EmpID VARCHAR(8) = '11050019'

DECLARE @Running VARCHAR(2) = '1'

DECLARE @Begda AS NVARCHAR(8)

DECLARE @Endda AS NVARCHAR(8)

SET @Begda = @PayPeriod + '01'

SET @Endda = CONVERT(NVARCHAR(8), DATEADD(d, -1, DATEADD(m, 1, @Begda)), 112)

DECLARE @TWorkSchedule TABLE

(

	EmployeeID VARCHAR(20)

	,StartDate VARCHAR(20)

	,EndDate VARCHAR(20)

	,DayType VARCHAR(20)

	,Flag VARCHAR(20)

)

INSERT INTO @TWorkSchedule

SELECT DISTINCT pa125.EmployeeID

				,pa125.StartDate

				,pa125.EndDate

				,pa125.DayType

				,daytype.Flag

FROM PHRPA0125 AS pa125 WITH(NOLOCK)

LEFT JOIN PHRTMDAYTYP AS daytype WITH(NOLOCK)

	On pa125.DayType = daytype.DayType

WHERE (pa125.EmployeeID = @EmpID OR @EmpID = '')

	AND pa125.StartDate BETWEEN @Begda AND @Endda

IF( (SELECT COUNT(StartDate) FROM @TWorkSchedule ) <= 0)

BEGIN

	INSERT INTO @TWorkSchedule

	SELECT DISTINCT pa25.EmployeeID

					,wscal.DateSpecified

					,wscal.DateSpecified

					,wscal.DayType

					,daytype.Flag

	FROM PHRPA0025 AS pa25 WITH(NOLOCK)

	LEFT JOIN PHRTMWSCAL AS wscal WITH(NOLOCK)

		ON wscal.WorkScheduleType = pa25.WorkScheduleType

		AND (wscal.DateSpecified BETWEEN @Begda AND @Endda) 

	LEFT JOIN PHRTMDAYTYP AS daytype WITH(NOLOCK)

		ON wscal.DayType = daytype.DayType

	WHERE (pa25.EmployeeID = @EmpID OR @EmpID = '')

		AND pa25.StartDate <= @Endda AND pa25.EndDate >= @Endda

END

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.StartDate

FROM dbo.PHRPA0018 AS pa18 WITH (NOLOCK)

LEFT JOIN @TWorkSchedule AS wscal

	ON wscal.StartDate BETWEEN pa18.StartDate AND pa18.EndDate

WHERE pa18.EmployeeID = @EmpID

	AND (pa18.StartDate BETWEEN @Begda AND @Endda OR pa18.EndDate BETWEEN @Begda AND @Endda)

	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.StartDate

FROM dbo.PHRPA0019 AS pa19 WITH (NOLOCK)

LEFT JOIN @TWorkSchedule AS wscal

	ON wscal.StartDate BETWEEN pa19.StartDate AND pa19.EndDate

WHERE pa19.EmployeeID = @EmpID

	AND (pa19.StartDate BETWEEN @Begda AND @Endda OR pa19.EndDate BETWEEN @Begda AND @Endda)

	AND wscal.Flag <> 'FREE'

DECLARE @TResult TABLE

(

	EmployeeID VARCHAR(20)

	,AbsType VARCHAR(50)

	,SumAbsence DECIMAL(18,0)

)

INSERT INTO @TResult

SELECT DISTINCT @EmpID, 'TanpaKeterangan' , 0

INSERT INTO @TResult

SELECT DISTINCT @EmpID, 'IzinPribadi' , 0

INSERT INTO @TResult

SELECT DISTINCT @EmpID, 'IzinSakit' , 0

UPDATE @TResult

SET SumAbsence = ISNULL((SELECT DISTINCT COUNT(wscal.StartDate) AS mangkir

FROM @TWorkSchedule AS wscal

WHERE wscal.StartDate NOT IN ( SELECT DISTINCT DateSpecified FROM @TPA18)

	AND wscal.StartDate NOT IN ( SELECT DISTINCT DateSpecified FROM @TPA19)

	AND wscal.Flag <> 'FREE'

GROUP BY wscal.EmployeeID),0)

WHERE AbsType = 'TanpaKeterangan'

UPDATE @TResult

SET SumAbsence = ISNULL(( SELECT DISTINCT COUNT([@TPA18].DateSpecified)

FROM @TPA18 

WHERE AbsenceType = '25'

GROUP BY EmployeeID),0)

WHERE AbsType = 'IzinPribadi'

UPDATE @TResult

SET SumAbsence = ISNULL((SELECT DISTINCT COUNT([@TPA18].DateSpecified)

FROM @TPA18 

WHERE AbsenceType = '40'

GROUP BY EmployeeID),0)

WHERE AbsType = 'IzinSakit'

SELECT * FROM 

(

	SELECT * FROM @TResult

)

AS SourceTable

PIVOT

(

	SUM(SumAbsence)

FOR AbsType IN

(	[TanpaKeterangan],

	[IzinPribadi],

	[IzinSakit] )

) AS pvt

ORDER BY EmployeeID