CLIENT SUPPORT » ARITA Bugs

??USE [MinovaES_Arita_Dev]

GO

/****** Object:  StoredProcedure [dbo].[CARITATTPKETERLAMBATAN]    Script Date: 06/03/2023 14.42.15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CARITATTPKETERLAMBATAN]

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

)

AS

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

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

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

DECLARE @WSType VARCHAR(20)

SELECT DISTINCT @WSType = WorkScheduleType FROM dbo.PHRPA0025

WHERE EmployeeID = @EmployeeID AND StartDate <= @EndDate AND EndDate >= @EndDate

DECLARE @TPA19 TABLE

(

	DateSpecified VARCHAR(20)

	,EmployeeID VARCHAR(20)

	,StartTime19 VARCHAR(20)

	,EndTime19 VARCHAR(20)

	,StartTimeDay VARCHAR(20)

	,EndTimeDay VARCHAR(20)

	,MenitTelat DECIMAL(18,0)

)

INSERT INTO @TPA19

SELECT DISTINCT wscal.DateSpecified, pa19.EmployeeID, pa19.StartTime, pa19.EndTime, daytype.StartTime AS StartTimeDay, daytype.EndTime AS EndTimeDay

				,CASE 

					WHEN pa19.StartTime <> '' AND pa19.EndTime <> '' AND pa19.StartTime > daytype.StartTime THEN ROUND((dbo.fn_diffTime(wscal.DateSpecified, wscal.DateSpecified, daytype.StartTime, pa19.StartTime) * 60),0) 

					WHEN pa19.StartTime = '' OR pa19.EndTime = '' THEN 1000

					ELSE 0

				 END AS MenitTelat

FROM dbo.PHRTMWSCAL AS wscal

INNER JOIN 

( 

SELECT DISTINCT EmployeeID, StartDate, EndDate, StartTime, EndTime

FROM dbo.PHRPA0019

WHERE EmployeeID = @EmployeeID --AND StartTime > '0700'

) AS pa19

ON wscal.DateSpecified = pa19.StartDate

LEFT JOIN dbo.PHRTMDAYTYP AS daytype

	ON wscal.DayType = daytype.DayType

WHERE wscal.WorkScheduleType = @WSType AND daytype.Flag <> 'FREE'

AND wscal.DateSpecified >= @StartDate AND wscal.DateSpecified <= @EndDate

SELECT DISTINCT reftelat.EmployeeID, reftelat.EndLate

				,COUNT(tpa19.HariTelat) AS HariTelat

				,COUNT(tpa19.HariTelat) * MIN(reftelat.Amount) AS AmountTelat

FROM 

(SELECT DISTINCT @EmployeeID AS EmployeeID, *

FROM CREFKETERLAMBATAN ) AS reftelat 

LEFT JOIN

( SELECT DISTINCT pa19.EmployeeID

			,pa19.DateSpecified

			,pa19.MenitTelat

			,CASE 

				WHEN StartTime19 <> '' AND EndTime19 <> '' AND StartTime19 > StartTimeDay THEN 1 

				WHEN StartTime19 = '' OR EndTime19 = '' THEN 1

				ELSE 0

			 END AS HariTelat

FROM @TPA19 AS pa19 ) AS tpa19

ON tpa19.EmployeeID = reftelat.EmployeeID

WHERE tpa19.MenitTelat BETWEEN reftelat.StartLate AND reftelat.EndLate

GROUP BY reftelat.EmployeeID, reftelat.EndLate

--SELECT DISTINCT reftelat.EmployeeID, reftelat.EndLate

--				,COUNT(pa19.DateSpecified) AS HariTelat

--				,COUNT(pa19.DateSpecified) * MIN(reftelat.Amount) AS AmountTelat

--FROM 

--(SELECT DISTINCT @EmployeeID AS EmployeeID, *

--FROM CREFKETERLAMBATAN ) AS reftelat 

--LEFT JOIN @TPA19 AS pa19

--	ON pa19.EmployeeID = reftelat.EmployeeID

--WHERE pa19.MenitTelat BETWEEN reftelat.StartLate AND reftelat.EndLate

--GROUP BY reftelat.EmployeeID, reftelat.EndLate