PT BIA

ALTER PROCEDURE [dbo].[CBIATMTOPYOVERTIME]

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

	,@WSType VARCHAR(20)

)

AS

--DECLARE @EmployeeID VARCHAR(20) = '11100027'

--DECLARE @StartDate VARCHAR(20) = '20230101'

--DECLARE @EndDate VARCHAR(20) = '20230131'

--DECLARE @WSType VARCHAR(20) = '6D7H'

IF(@WSType = '6D7H')

BEGIN

	DECLARE @wscal TABLE

	(

	EmployeeID VARCHAR(20)

	,DateSpecified VARCHAR(20)

	,DayType VARCHAR(20)

	,FlagDay VARCHAR(20)

	,StartWorkTime VARCHAR(20)

	,EndWorkTime VARCHAR(20)

	)

	INSERT INTO	@wscal

	SELECT DISTINCT @EmployeeID, cal.DateSpecified, cal.DayType, dt.Flag, dt.StartTime, dt.EndTime

	FROM dbo.PHRTMWSCAL AS cal

	LEFT JOIN dbo.PHRTMDAYTYP AS dt

	ON dt.DayType = cal.DayType

	WHERE cal.WorkScheduleType = @WSType

	AND (cal.DateSpecified BETWEEN @StartDate AND @EndDate)

	DECLARE @TblMDOvt TABLE

	(

	EmployeeID VARCHAR(20)

	,StartDate VARCHAR(20)

	,StartTime VARCHAR(20)

	,EndTime VARCHAR(20)

	,Duration DECIMAL(18,2)

	,DayType VARCHAR(20)

	,FlagDay VARCHAR(20)

	,StartWorkTime VARCHAR(20)

	,EndWorkTime VARCHAR(20)

	,OvtDuration16 DECIMAL(18,2)

	)

	INSERT INTO @TblMDOvt

	SELECT DISTINCT pa16.EmployeeID, pa16.StartDate, pa16.StartTime, pa16.EndTime, pa16.Duration

				,wscal.DayType, wscal.FlagDay, wscal.StartWorkTime, wscal.EndWorkTime

				,dbo.fn_diffTime(pa16.StartDate, pa16.StartDate, pa16.StartTime, pa16.EndTime) AS OvtDuration16

	FROM PHRPA0016 AS pa16

	LEFT JOIN @wscal AS wscal

	ON pa16.StartDate = wscal.DateSpecified

	WHERE pa16.EmployeeID = @EmployeeID

	AND (pa16.StartDate BETWEEN @StartDate AND @EndDate)

	DECLARE @TblMDAtt TABLE

	(

	EmployeeID VARCHAR(20)

	,StartDate VARCHAR(20)

	,StartTime VARCHAR(20)

	,EndTime VARCHAR(20)

	,DayType VARCHAR(20)

	,FlagDay VARCHAR(20)

	,StartWorkTime VARCHAR(20)

	,EndWorkTime VARCHAR(20)

	,OvtDuration19 DECIMAL(18,2)

	)

	INSERT INTO @TblMDAtt

	SELECT DISTINCT pa19.EmployeeID, pa19.StartDate, pa19.StartTime, pa19.EndTime

				,wscal.DayType, wscal.FlagDay, wscal.StartWorkTime, wscal.EndWorkTime

				,CASE WHEN dbo.fn_diffTime(pa19.StartDate, pa19.StartDate, wscal.EndWorkTime, pa19.EndTime) >= 1 THEN 1 ELSE 0 END AS OvtDuration19

	FROM PHRPA0019 AS pa19

	LEFT JOIN @wscal AS wscal

	ON pa19.StartDate = wscal.DateSpecified

	WHERE pa19.EmployeeID = @EmployeeID

	AND (pa19.StartDate BETWEEN @StartDate AND @EndDate)

	DECLARE @TblOvtDur TABLE

	(

	EmployeeID VARCHAR(20)

	,StartDate VARCHAR(20)

	,FlagDay VARCHAR(20)

	,SumOvtDuration DECIMAL(18,2)

	)

	INSERT INTO @TblOvtDur

	SELECT DISTINCT ovt.EmployeeID, ovt.StartDate, ovt.FlagDay, --ovt.StartTime, ovt.EndTime, ovt.OvtDuration16, att.StartTime, att.EndTime, att.OvtDuration19,

				CASE WHEN ovt.StartTime <= '1700' THEN ovt.OvtDuration16 + att.OvtDuration19 - (dbo.fn_diffTime(ovt.StartDate, ovt.StartDate, ovt.StartTime, '1700')) ELSE ovt.OvtDuration16 + att.OvtDuration19 END AS OvtDuration

	FROM @TblMDOvt AS ovt

	LEFT JOIN @TblMDAtt AS att

	ON att.EmployeeID = ovt.EmployeeID

	WHERE ovt.StartDate = att.StartDate

	INSERT INTO @TblOvtDur

	SELECT DISTINCT mdovt.EmployeeID, mdovt.StartDate, mdovt.FlagDay, mdovt.OvtDuration16 

	FROM @TblMDOvt AS mdovt

	WHERE mdovt.StartDate NOT IN (SELECT DISTINCT StartDate FROM @TblOvtDur)

	DECLARE @TblSumOvtDur TABLE

	(

	EmployeeID VARCHAR(20)

	,StartDate VARCHAR(20)

	,FlagDay VARCHAR(20)

	,SumOvtDuration DECIMAL(18,2)

	,OVT150 DECIMAL(18,2)

	,OVT200 DECIMAL(18,2)

	,OVT300 DECIMAL(18,2)

	,OVT400 DECIMAL(18,2)

	)

	INSERT INTO @TblSumOvtDur

	SELECT DISTINCT * 

				,CASE 

					WHEN FlagDay = 'WORK' THEN CASE WHEN SumOvtDuration < 1 THEN SumOvtDuration ELSE 1 END

					ELSE 0

					END AS OVT150

				,CASE 

					WHEN FlagDay = 'WORK' THEN CASE WHEN SumOvtDuration >= 1 THEN (SumOvtDuration - 1) END

					WHEN FlagDay = 'FREE' THEN CASE WHEN SumOvtDuration < 7 THEN SumOvtDuration ELSE 7 END

					ELSE 0

					END AS OVT200

				,CASE 

					WHEN FlagDay = 'FREE' THEN CASE WHEN SumOvtDuration >= 8 THEN 1 WHEN SumOvtDuration BETWEEN 7 AND 8 THEN SumOvtDuration - 7 ELSE 0 END 

					ELSE 0

					END AS OVT300

				,CASE 

					WHEN FlagDay = 'FREE' THEN CASE WHEN SumOvtDuration >= 9 THEN (SumOvtDuration - 9) ELSE 0 END

					ELSE 0

					END AS OVT400

	FROM @TblOvtDur

	SELECT DISTINCT SumDur.EmployeeID

				,SUM(SumDur.OVT150) AS OVT150

				,SUM(SumDur.OVT200) AS OVT200

				,SUM(SumDur.OVT300) AS OVT300

				,SUM(SumDur.OVT400) AS OVT400

	FROM @TblSumOvtDur AS SumDur

	GROUP BY SumDur.EmployeeID

END

ELSE

BEGIN

	SELECT DISTINCT @EmployeeID

				,0 AS OVT150

				,0 AS OVT200

				,0 AS OVT300

				,0 AS OVT400

END