--USE [MinovaES_BIA_Dev]
--GO
--/****** Object:  StoredProcedure [dbo].[CBIATMTOPYHINOTARIF]    Script Date: 24/01/2023 11.16.34 ******/
--SET ANSI_NULLS ON
--GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CBIATMTOPYHINOTARIF]
(
	@EmployeeID VARCHAR(20)
	,@StartDate VARCHAR(20)
	,@EndDate VARCHAR(20)
)
AS

--DECLARE @EmployeeID VARCHAR(20) = '14060255'
--DECLARE @StartDate VARCHAR(20) = '20230101'
--DECLARE @EndDate VARCHAR(20) = '20230131'

DECLARE @CTARIF TABLE
(
	EmployeeID VARCHAR(20)
	,QtyTrip1 DECIMAL(22,0)
	,QtyTrip2 DECIMAL(22,0)
	,QtyTrip3 DECIMAL(22,0)
)

INSERT INTO @CTARIF
SELECT @EmployeeID, * FROM (SELECT FieldName, Tarif FROM CTARIFHINO) AS SourceTable
PIVOT (MAX(Tarif)
FOR FieldName IN
(	[QtyTrip1],
	[QtyTrip2],
	[QtyTrip3])
) AS pvt


DECLARE @TblResult TABLE
(
	EmployeeID VARCHAR(20)
	,QtyTrip1 VARCHAR(20)
	,QtyTrip2 VARCHAR(20)
	,QtyTrip3 VARCHAR(20)
	,TarifQtyTrip1 DECIMAL(22,0)
	,TarifQtyTrip2 DECIMAL(22,0)
	,TarifQtyTrip3 DECIMAL(22,0)
)

INSERT INTO @TblResult
SELECT DISTINCT md.EmployeeID, md.QtyTrip1,  md.QtyTrip2,  md.QtyTrip3 
				,md.QtyTrip1 * tarif.QtyTrip1 AS TarifQtyTrip1
				,md.QtyTrip2 * tarif.QtyTrip2 AS TarifQtyTrip2
				,md.QtyTrip3 * tarif.QtyTrip3 AS TarifQtyTrip3
FROM dbo.CUSPYDATAPREMIHINO 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(TarifQtyTrip1) AS TarifQtyTrip1
				,SUM(TarifQtyTrip2) AS TarifQtyTrip2
				,SUM(TarifQtyTrip3) AS TarifQtyTrip3
FROM @TblResult
GROUP BY EmployeeID