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