PT BIA

??USE [MinovaES_BIA_Dev]

GO

/****** Object:  StoredProcedure [dbo].[CBIATMTOPYCPOTARIF]    Script Date: 22/06/2023 15.51.00 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CBIATMTOPYCPOTARIF]

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

)

AS

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

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

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

DECLARE @CTARIF TABLE

(

	EmployeeID VARCHAR(20)

	,DayFlag VARCHAR(20)

	,TipeCPO VARCHAR(20)

	,PKS01 DECIMAL(22,2)

	,PKS02 DECIMAL(22,2)

	,PKS03 DECIMAL(22,2)

)

INSERT INTO @CTARIF

SELECT @EmployeeID, 'WORK', * FROM (SELECT TipeCPO, FieldName, TarifHariKerja FROM CTARIFCPO ) AS SourceTable

PIVOT (MAX(TarifHariKerja)

FOR FieldName IN

(	[PKS01],

	[PKS02],

	[PKS03])

) AS pvt

INSERT INTO @CTARIF

SELECT @EmployeeID, 'FREE', * FROM (SELECT TipeCPO, FieldName, TarifHariLibur FROM CTARIFCPO ) AS SourceTable

PIVOT (MAX(TarifHariLibur)

FOR FieldName IN

(	[PKS01],

	[PKS02],

	[PKS03])

) AS pvt

DECLARE @TWSCal TABLE

(

	DateSpecified VARCHAR(20)

	,DayFlag VARCHAR(50)

)

INSERT INTO @TWSCal

SELECT DISTINCT pa125.StartDate, dt.Flag

FROM dbo.PHRPA0125 AS pa125 WITH (NOLOCK)

LEFT JOIN dbo.PHRTMDAYTYP AS dt

	ON dt.DayType = pa125.DayType

WHERE pa125.EmployeeID = @EmployeeID

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

AND dt.Flag = 'WORK'

IF( (SELECT COUNT(DateSpecified) FROM @TWSCal) = 0 ) 

BEGIN

	INSERT INTO @TWSCal

	SELECT DISTINCT wscal.DateSpecified, dt.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 dt

		ON wscal.DayType = dt.DayType

	WHERE pa25.EmployeeID = @EmployeeID

		AND (pa25.StartDate <= @EndDate AND pa25.EndDate >= @EndDate)

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

END

DECLARE @TblResult TABLE

(

	EmployeeID VARCHAR(20)

	,CPORett VARCHAR(20)

	,CPO2Rett VARCHAR(20)

	,CPO3Rett VARCHAR(20)

	,StartDate VARCHAR(20)

	,TarifCPORett DECIMAL(22,0)

	,TarifCPO2Rett DECIMAL(22,0)

	,TarifCPO3Rett DECIMAL(22,0)

)

INSERT INTO @TblResult

SELECT DISTINCT md.EmployeeID, md.CPORett,  md.CPO2Rett,  md.CPO3Rett, md.StartDate

				,CONVERT(DECIMAL(22,2),CONVERT(DECIMAL(18,2),CASE WHEN md.PKS1Tersus <> '' THEN md.PKS1Tersus ELSE 0 END) * tarif.PKS01) AS TarifCPORett

				,CONVERT(DECIMAL(22,2),CONVERT(DECIMAL(18,2),CASE WHEN md.PKS2Tersus <> '' THEN md.PKS2Tersus ELSE 0 END) * tarif.PKS02) AS TarifCPO2Rett

				,CONVERT(DECIMAL(22,2),CONVERT(DECIMAL(18,2),CASE WHEN md.PKS3Tersus <> '' THEN md.PKS3Tersus ELSE 0 END) * tarif.PKS03) AS TarifCPO3Rett

FROM dbo.CUSPYDATACPO  AS md 

LEFT JOIN @TWSCal AS twscal

	ON md.StartDate = twscal.DateSpecified

LEFT JOIN @CTARIF AS tarif

	ON md.EmployeeID = tarif.EmployeeID

	AND twscal.DayFlag = tarif.DayFlag

WHERE  md.EmployeeID = @EmployeeID

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

	AND (md.NoUnit = tarif.TipeCPO)

SELECT DISTINCT EmployeeID

				,SUM(TarifCPORett) AS TarifCPORett

				,SUM(TarifCPO2Rett) AS TarifCPO2Rett

				,SUM(TarifCPO3Rett) AS TarifCPO3Rett

FROM @TblResult

GROUP BY EmployeeID