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