USE [MinovaES_BIA_Dev]
GO
/****** Object:  StoredProcedure [dbo].[CBIATMTOPYCPOTARIF]    Script Date: 11/05/2023 15.47.43 ******/
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) = '16020412'
--DECLARE @StartDate VARCHAR(20) = '20230101'
--DECLARE @EndDate VARCHAR(20) = '20230131'

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