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