PT BIA

??USE [MinovaES_BIA_Dev]

GO

/****** Object:  StoredProcedure [dbo].[CBIATMTOPYBINAMARGATARIF]    Script Date: 11/05/2023 15.48.46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CBIATMTOPYBINAMARGATARIF]

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

)

AS

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

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

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

DECLARE @CTARIF TABLE

(

	EmployeeID VARCHAR(20)

	,DayFlag VARCHAR(20)

	,PremiHelper DECIMAL(22,2)

	,UangMakan DECIMAL(22,2)

	,PremiOperator DECIMAL(22,2)

)

INSERT INTO @CTARIF

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

PIVOT (MAX(TarifHariKerja)

FOR FieldName IN

(	[PremiHelper],

	[UangMakan],

	[PremiOperator])

) AS pvt

INSERT INTO @CTARIF

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

PIVOT (MAX(TarifHariLibur)

FOR FieldName IN

(	[PremiHelper],

	[UangMakan],

	[PremiOperator])

) 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)

	,PremiHelper VARCHAR(20)

	,UangMakan VARCHAR(20)

	,PremiOperator VARCHAR(20)

	,StartDate VARCHAR(20)

	,TarifPremiHelper DECIMAL(22,0)

	,TarifUangMakan DECIMAL(22,0)

	,TarifPremiOperator DECIMAL(22,0)

)

INSERT INTO @TblResult

SELECT DISTINCT md.EmployeeID, md.PremiHelper,  md.UangMakan,  md.PremiOperator, md.StartDate

				,CONVERT(DECIMAL(22,2),CASE WHEN md.PremiHelper <> '' THEN md.PremiHelper ELSE '0' END) * tarif.PremiHelper AS TarifPremiHelper

				,CONVERT(DECIMAL(22,2),CASE WHEN md.UangMakan <> '' THEN md.UangMakan ELSE '0' END) * tarif.UangMakan AS TarifUangMakan

				,CONVERT(DECIMAL(22,2),CASE WHEN md.PremiOperator <> '' THEN md.PremiOperator ELSE '0' END) * tarif.PremiOperator AS TarifPremiOperator

FROM CUSPYDATAPREMIBINAMARGA AS md WITH (NOLOCK)

LEFT JOIN @TWSCal AS twscal

	ON twscal.DateSpecified = md.StartDate

LEFT JOIN @CTARIF AS tarif

	ON tarif.DayFlag = twscal.DayFlag

WHERE  md.EmployeeID = @EmployeeID

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

SELECT DISTINCT EmployeeID

				,SUM(TarifPremiHelper) AS TarifPremiHelper

				,SUM(TarifUangMakan) AS TarifUangMakan

				,SUM(TarifPremiOperator) AS TarifPremiOperator

FROM @TblResult

GROUP BY EmployeeID