PT BIA

??USE [MinovaES_BIA_Dev]

GO

/****** Object:  StoredProcedure [dbo].[CBIATMTOPYTRIPHINOTARIF]    Script Date: 11/05/2023 15.47.14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CBIATMTOPYTRIPHINOTARIF]

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

)

AS

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

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

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

DECLARE @CTARIF TABLE

(

	EmployeeID VARCHAR(20)

	,DayFlag VARCHAR(20)

	,TipeTrip01 DECIMAL(22,2)

	,TipeTrip02 DECIMAL(22,2)

	,TipeTrip03 DECIMAL(22,2)

	,TipeTrip04 DECIMAL(22,2)

	,TipeTrip05 DECIMAL(22,2)

	,TipeTrip06 DECIMAL(22,2)

	,TipeTrip07 DECIMAL(22,2)

	,TipeTrip08 DECIMAL(22,2)

	,TipeTrip09 DECIMAL(22,2)

	,TipeTrip10 DECIMAL(22,2)

	,TipeTrip11 DECIMAL(22,2)

	,TipeTrip12 DECIMAL(22,2)

	,TipeTrip13 DECIMAL(22,2)

	,TipeTrip14 DECIMAL(22,2)

	,TipeTrip15 DECIMAL(22,2)

	,TipeTrip16 DECIMAL(22,2)

	,TipeTrip17 DECIMAL(22,2)

	,TipeTrip18 DECIMAL(22,2)

	,TipeTrip19 DECIMAL(22,2)

	,TipeTrip20 DECIMAL(22,2)

)

INSERT INTO @CTARIF

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

PIVOT (MAX(Tarif)

FOR FieldName IN

(	[TipeTrip01],

	[TipeTrip02],

	[TipeTrip03],

	[TipeTrip04],

	[TipeTrip05],

	[TipeTrip06],

	[TipeTrip07],

	[TipeTrip08],

	[TipeTrip09],

	[TipeTrip10],

	[TipeTrip11],

	[TipeTrip12],

	[TipeTrip13],

	[TipeTrip14],

	[TipeTrip15],

	[TipeTrip16],

	[TipeTrip17],

	[TipeTrip18],

	[TipeTrip19],

	[TipeTrip20])

) AS pvt

INSERT INTO @CTARIF

SELECT @EmployeeID, 'FREE', * FROM (SELECT FieldName, TarifHariKerja AS Tarif FROM CTARIFTRIPHINODUMPTRUCK) AS SourceTable

PIVOT (MAX(Tarif)

FOR FieldName IN

(	[TipeTrip01],

	[TipeTrip02],

	[TipeTrip03],

	[TipeTrip04],

	[TipeTrip05],

	[TipeTrip06],

	[TipeTrip07],

	[TipeTrip08],

	[TipeTrip09],

	[TipeTrip10],

	[TipeTrip11],

	[TipeTrip12],

	[TipeTrip13],

	[TipeTrip14],

	[TipeTrip15],

	[TipeTrip16],

	[TipeTrip17],

	[TipeTrip18],

	[TipeTrip19],

	[TipeTrip20])

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

	,StartDate VARCHAR(20)

	,TipeTrip01 VARCHAR(20)

	,TipeTrip02 VARCHAR(20)

	,TipeTrip03 VARCHAR(20)

	,TipeTrip04 VARCHAR(20)

	,TipeTrip05 VARCHAR(20)

	,TipeTrip06 VARCHAR(20)

	,TipeTrip07 VARCHAR(20)

	,TipeTrip08 VARCHAR(20)

	,TipeTrip09 VARCHAR(20)

	,TipeTrip10 VARCHAR(20)

	,TipeTrip11 VARCHAR(20)

	,TipeTrip12 VARCHAR(20)

	,TipeTrip13 VARCHAR(20)

	,TipeTrip14 VARCHAR(20)

	,TipeTrip15 VARCHAR(20)

	,TipeTrip16 VARCHAR(20)

	,TipeTrip17 VARCHAR(20)

	,TipeTrip18 VARCHAR(20)