PT BIA

??USE [MinovaES_BIA_Dev]

GO

/****** Object:  StoredProcedure [dbo].[CBIATMTOPYBINAMARGATARIF]    Script Date: 24/01/2023 11.14.32 ******/

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) = '12100109'

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

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

DECLARE @CTARIF TABLE

(

	EmployeeID VARCHAR(20)

	,PremiHelper DECIMAL(22,2)

	,UangMakan DECIMAL(22,2)

	,PremiOperator DECIMAL(22,2)

)

INSERT INTO @CTARIF

SELECT @EmployeeID, * FROM (SELECT FieldName, Tarif FROM CTARIFBINAMARGA) AS SourceTable

PIVOT (MAX(Tarif)

FOR FieldName IN

(	[PremiHelper],

	[UangMakan],

	[PremiOperator])

) AS pvt

DECLARE @TblResult TABLE

(

	EmployeeID VARCHAR(20)

	,PremiHelper VARCHAR(20)

	,UangMakan VARCHAR(20)

	,PremiOperator 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 

				,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 

LEFT JOIN @CTARIF AS tarif

	ON md.EmployeeID = tarif.EmployeeID

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