USE [HRIS]
GO
/****** Object:  StoredProcedure [dbo].[usp_CleansingSisaCuti]    Script Date: 08/06/2022 17:11:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Lius
-- Create date: 15 Juli 2021
-- Description:	Untuk Cleansing Cuti
-- =============================================
ALTER PROCEDURE [dbo].[usp_CleansingSisaCuti]
@NIP VARCHAR(32)
AS
BEGIN
SET NOCOUNT ON

select* into #temp_table_migrasi_rekap_cuti from (
select b.Urutan_NIP, a.NIP,
	ROW_NUMBER() OVER(PARTITION BY a.NIP ORDER BY a.NIP ASC, a.TANGGAL_BERLAKU ASC, a.ID ASC) as Urutan, b.TGL_MASUK, a.sts_cut_off,
	case when coalesce(a.sts_cut_off, 0)=0 then 'Timbul Hak Cuti' else 'Cut Off' end as Keterangan,
	JNS_CUTI='1', a.TANGGAL_BERLAKU, a.TANGGAL_BERAKHIR, SISA_CUTI=0, HAK_CUTI=coalesce(a.HAK_CUTI, 0),
	TOTAL_CUTI=0, HUTANG_CUTI=0
from SPG_REKAP_CUTI a
inner join (select ROW_NUMBER() OVER(ORDER BY NIP ASC) as Urutan_NIP, NIP, TGL_MASUK from SPG_DATA_CURRENT) b
	on a.NIP=b.NIP
where a.JNS_CUTI='1' and a.NIP=@NIP
)a


DECLARE @Urutan_NIP_Value INT, @Max_Nip INT, @Urutan_Value INT, @Max_Urutan INT
SET @Urutan_NIP_Value = 1
SET @Max_Nip = (select coalesce(max(Urutan_NIP), 0) as Max_Nip from #temp_table_migrasi_rekap_cuti)

WHILE @Urutan_NIP_Value <= @Max_Nip
BEGIN
	SET @Urutan_Value = 1
	SET @Max_Urutan = (select coalesce(max(Urutan), 0) as Max_Urutan from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value)

	WHILE @Urutan_Value <= @Max_Urutan
	BEGIN

		update #temp_table_migrasi_rekap_cuti
		set TOTAL_CUTI=coalesce(b.TOTAL_CUTI, 0), HUTANG_CUTI=coalesce(c.TOTAL_CUTI, 0)
		from #temp_table_migrasi_rekap_cuti a
		left join (select NIP, JNS_CUTI, TOTAL_CUTI=coalesce(sum(JUMLAH_CUTI), 0) from SPG_CUTI_PEGAWAI
		where NIP=(select NIP from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value)
			and JNS_CUTI=(select JNS_CUTI from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value)
			and TGL_AWAL_CUTI_PEG between
			(select TANGGAL_BERLAKU from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value) and
			coalesce((select DATEADD(day, -1, TANGGAL_BERLAKU) as TANGGAL_BERLAKU from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value+1),
			(select TANGGAL_BERAKHIR from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value))
			and isnull(tidak_akumulasi,0) <> 1
			group by NIP, JNS_CUTI) b on a.NIP=b.NIP and a.JNS_CUTI=b.JNS_CUTI
		left join (select NIP, JNS_CUTI, TOTAL_CUTI=coalesce(sum(JUMLAH_CUTI), 0) from SPG_CUTI_PEGAWAI
		where NIP=(select NIP from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value)
			and JNS_CUTI=(select JNS_CUTI from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value)
			and TGL_AWAL_CUTI_PEG <
			(select TANGGAL_BERLAKU from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value)
			and isnull(tidak_akumulasi,0) <> 1
			group by NIP, JNS_CUTI) c on a.NIP=c.NIP and a.JNS_CUTI=c.JNS_CUTI and Urutan='1'
		where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value

		update #temp_table_migrasi_rekap_cuti
		set SISA_CUTI = coalesce(case when HAK_CUTI + case when Keterangan='Cut Off' then 0 else
		coalesce((select SISA_CUTI from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value-1), 0) end
		> 24 then 24 else HAK_CUTI + case when Keterangan='Cut Off' then 0 else
		coalesce((select SISA_CUTI from #temp_table_migrasi_rekap_cuti where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value-1), 0) end end
		 - TOTAL_CUTI - case when Keterangan='Cut Off' then 0 else HUTANG_CUTI end, 0)
		where Urutan_NIP=@Urutan_NIP_Value and Urutan=@Urutan_Value and
			(TANGGAL_BERAKHIR<getdate() or (TANGGAL_BERAKHIR is null and Urutan<>@Max_Urutan))

		SET @Urutan_Value = @Urutan_Value + 1
	END

	SET @Urutan_NIP_Value = @Urutan_NIP_Value + 1
END

--select* from #temp_table_migrasi_rekap_cuti

update SPG_REKAP_CUTI
set SISA_CUTI=b.SISA_CUTI
from SPG_REKAP_CUTI a
inner join #temp_table_migrasi_rekap_cuti b on a.NIP=b.NIP and a.JNS_CUTI=b.JNS_CUTI and
	a.TANGGAL_BERLAKU=b.TANGGAL_BERLAKU and (a.TANGGAL_BERAKHIR=b.TANGGAL_BERAKHIR or a.sts_cut_off=b.sts_cut_off)


drop table #temp_table_migrasi_rekap_cuti

END