PT BIA

??USE [MinovaES_BIA_Dev]

GO

/****** Object:  StoredProcedure [dbo].[CBIATMTOPYPERAWATANTARIF]    Script Date: 30/03/2023 09.14.22 ******/

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

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

	,Seq DECIMAL(18,0)

	,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

				,md.Sequence

				,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.HasilKerja4 <> '' 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 md.LebihTarget LIKE '%.%' THEN 'A' ELSE 'B' END

				,CASE WHEN md.LebihTarget > 0 THEN md.LebihTarget

					  WHEN md.KurangTarget > 0 THEN md.KurangTarget * -1

					  ELSE 0 

				 END SelisihTarget

				--,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 md.LebihTarget > 0 THEN CONVERT(DECIMAL(22,2),upah.UpahPokok) + upah.PremiBasis + (CONVERT(DECIMAL(22,2),md.LebihTarget) * CONVERT(DECIMAL(22,2),tarif.Tarif21))

					  WHEN 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

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