PT BIA

??USE [MinovaES_BIA_Dev]

GO

/****** Object:  StoredProcedure [dbo].[CBIATMTOPYPERAWATANTARIF]    Script Date: 24/01/2023 11.54.31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[CBIATMTOPYPERAWATANTARIF]

(

	@EmployeeID VARCHAR(20)

	,@StartDate VARCHAR(20)

	,@EndDate VARCHAR(20)

)

AS

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

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

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

DECLARE @CTARIF TABLE

(

	EmployeeID VARCHAR(20)

    ,[KodeKegiatan] VARCHAR(20)

    ,[Tarif21] VARCHAR(20)

    ,[TargetKerjaPerHari] VARCHAR(20)

    ,[TargetKerjaHariSabtu] VARCHAR(20)

)

INSERT INTO @CTARIF

SELECT @EmployeeID

	,[KodeKegiatan]

	,[Tarif21]

	,[TargetKerjaPerHari]

	,[TargetKerjaHariSabtu] 

FROM CTARIFPERAWATAN

DECLARE @TblPerawatan Table

(

	EmployeeID VARCHAR(20)

	,StartDate VARCHAR(20)

	,HasilKerja1 DECIMAL(22,2)

	,HasilKerja2 DECIMAL(22,2)

	,HasilKerja3 DECIMAL(22,2)

	,HasilKerja4 DECIMAL(22,2)

	,HasilKerja5 DECIMAL(22,2)

	,TotalHasilKerja DECIMAL(22,2)

	,LebihTarget DECIMAL(22,2)

	,KurangTarget DECIMAL(22,2)

	,SelisihTarget DECIMAL(22,2)

	,TarifPerawatan21 DECIMAL(22,2)

	,UpahCapaiTarget DECIMAL(22,2)

	,PremiCapaiTarget DECIMAL(22,2)

	,TotalPerawatan DECIMAL(22,2) 

)

INSERT INTO @TblPerawatan

SELECT DISTINCT md.EmployeeID

				,md.StartDate

				,CASE WHEN md.HasilKerja1 <> '' THEN md.HasilKerja1 ELSE 0 END AS HasilKerja1

				,CASE WHEN md.HasilKerja2 <> '' THEN md.HasilKerja2 ELSE 0 END AS HasilKerja2

				,CASE WHEN md.HasilKerja3 <> '' THEN md.HasilKerja3 ELSE 0 END AS HasilKerja3

				,CASE WHEN md.HasilKerja4 <> '' THEN md.HasilKerja4 ELSE 0 END AS HasilKerja4

				,CASE WHEN md.HasilKerja5 <> '' THEN md.HasilKerja5 ELSE 0 END AS HasilKerja5

				,CASE WHEN md.TotalHasilKerja <> '' THEN md.TotalHasilKerja ELSE 0 END AS TotalHasilKerja

				,CASE WHEN md.LebihTarget <> '' THEN md.LebihTarget ELSE 0 END AS LebihTarget

				,CASE WHEN md.KurangTarget <> '' THEN md.KurangTarget ELSE 0 END AS KurangTarget

				,CASE WHEN CONVERT(DECIMAL(22,2),md.LebihTarget) > 0 THEN CONVERT(DECIMAL(22,2),md.LebihTarget) 

					  WHEN CONVERT(DECIMAL(22,2),md.KurangTarget) > 0 THEN CONVERT(DECIMAL(22,2),md.KurangTarget) * -1

					  ELSE 0 

				 END SelisihTarget

				,tarif.Tarif21 AS TarifPerawatan21

				,upah.UpahPokok AS UpahCapaiTarget

				,upah.PremiBasis AS PremiCapaiTarget

				,CONVERT(DECIMAL(22,0),

				 CASE WHEN CONVERT(DECIMAL(22,2),md.LebihTarget) > 0 THEN CONVERT(DECIMAL(22,2),upah.UpahPokok) + CONVERT(DECIMAL(22,2),upah.PremiBasis) + (CONVERT(DECIMAL(22,2),md.LebihTarget) * CONVERT(DECIMAL(22,2),tarif.Tarif21))

					  WHEN CONVERT(DECIMAL(22,2),md.KurangTarget) > 0 THEN CONVERT(DECIMAL(22,2),upah.UpahPokok) - (CONVERT(DECIMAL(22,2),md.KurangTarget) * CONVERT(DECIMAL(22,2),tarif.Tarif21))

					  ELSE CONVERT(DECIMAL(22,2),upah.UpahPokok) + CONVERT(DECIMAL(22,2),upah.PremiBasis)

				 END) AS TotalPerawatan

FROM dbo.CUSPYDATAPREMIPERAWATAN  AS md 

LEFT JOIN @CTARIF AS tarif

	ON md.EmployeeID = tarif.EmployeeID

LEFT JOIN CTARIFBRDPANEN AS upah

	ON upah.StartDate <= @EndDate AND upah.EndDate >= @EndDate

WHERE  md.EmployeeID = @EmployeeID

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

	AND (md.KodeKegiatan = tarif.KodeKegiatan)

SELECT DISTINCT result.EmployeeID

				,MAX(result.TarifPerawatan21) AS TarifPerawatan21

				,SUM(result.SelisihTarget) AS SelisihTarget

				,MAX(result.UpahCapaiTarget) AS UpahCapaiTarget

				,MAX(result.PremiCapaiTarget) AS PremiCapaiTarget

				,SUM(result.TotalPerawatan) AS TotalPerawatan

FROM @TblPerawatan AS result

GROUP BY EmployeeID