PT BIA

??ALTER PROCEDURE [dbo].[PRPTTMCUTITUNED]

(

    @Year VARCHAR(4) ,

    @EmployeeID VARCHAR(18)

)

AS

--DECLARE @Year VARCHAR(4) = '2024'

--DECLARE @EmployeeID VARCHAR(12) = '23044170'

DECLARE @now VARCHAR(10)= dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

DECLARE @startdate VARCHAR(12) = @year + '0101'

DECLARE @enddate VARCHAR(12) = @year + '1231'

select 

pa18.EmployeeID

,dbo.fn_formatdatetime(pa18.StartDate, 'dd/mm/yyyy') as StartDate_

,dbo.fn_formatdatetime(pa18.EndDate, 'dd/mm/yyyy') as EndDate_

,pa18.StartDate

,pa18.EndDate

,pa18.AbsenceType

,absence_type.AbsenceTypeDesc

,pa18.TotalAbsenceTaken

,pa18.Notes

into #absence_data

from PHRPA0018 pa18

left join PHRTMABSTYP absence_type on pa18.AbsenceType=absence_type.AbsenceType and absence_type.EndDate='99991231'

where pa18.EmployeeID=@EmployeeID and left(pa18.StartDate,4)=@Year

select 

pa17.EmployeeID

,pa17.PreviousQuota

,pa17.OriginalQuota 

,pa17.RemainQuota

,pa17.ExpiredQuota

into #absence_detail

from PHRPA0017 pa17 

where pa17.EmployeeID=@EmployeeID and pa17.StartDate <= @enddate AND pa17.EndDate >= @enddate

--select * from #absence_detail

DECLARE @EndDeductionDate VARCHAR(20) = (SELECT DISTINCT ExpiredQuota FROM #absence_detail)  

DECLARE @SumPrevQuotaNow DECIMAL(22,1) = (SELECT DISTINCT PreviousQuota FROM #absence_detail) 

DECLARE @RemaingQuotaNow DECIMAL(22,1) = (SELECT DISTINCT RemainQuota FROM #absence_detail) 

DECLARE @DurTaken DECIMAL(22,1)  --------- jumlah cuti yang terjadi di tahun sekarang

SELECT DISTINCT @DurTaken = SUM(TotalAbsenceTaken)

FROM #absence_data

DECLARE @DurQuotaSetengahLast DECIMAL(22,1) --------- jumlah cuti setengah hari yang terjadi di tahun kemarin 

SELECT DISTINCT @DurQuotaSetengahLast = ISNULL(SUM(TotalAbsenceTaken),0)

FROM #absence_data AS AbsDetail

WHERE EndDate <= @EndDeductionDate AND TotalAbsenceTaken < 1

DECLARE @DurQuotaSetengahNow DECIMAL(22,1) --------- jumlah cuti setengah hari yang terjadi di tahun sekarang 

SELECT DISTINCT @DurQuotaSetengahNow = ISNULL(SUM(TotalAbsenceTaken),0)

FROM #absence_data AS AbsDetail

WHERE EndDate > @EndDeductionDate AND TotalAbsenceTaken < 1

DECLARE @DurQuotaLast DECIMAL(22,1) --------- jumlah cuti yang terjadi sampai expired date yang ada di md17

SELECT DISTINCT @DurQuotaLast = ISNULL(SUM(TotalAbsenceTaken),0)

FROM #absence_data AS AbsDetail

WHERE EndDate <= @EndDeductionDate

DECLARE @DurQuotaNow DECIMAL(22,1) --------- jumlah cuti yang terjadi setelah expired date yang ada di md17

SELECT DISTINCT @DurQuotaNow = ISNULL(SUM(ISNULL(TotalAbsenceTaken,0)),0)

FROM #absence_data AS AbsDetailx

WHERE EndDate > @EndDeductionDate

DECLARE @Use_Prev_Quota DECIMAL(22,1) --------- jumlah cuti tahun yang menggunakan quota tahun kemarin

SET @Use_Prev_Quota = ( CASE WHEN @DurQuotaLast > @SumPrevQuotaNow THEN @SumPrevQuotaNow ELSE @DurQuotaLast END )

DECLARE @SisaCutiInExpiredDate DECIMAL(22,1)  --------- sisa kelebihan cuti yang termasuk dalam expired date

SET @SisaCutiInExpiredDate = CASE 

								 WHEN @SumPrevQuotaNow > 0 THEN

									 CASE 

										WHEN @DurQuotaLast > @SumPrevQuotaNow THEN @DurQuotaLast - @SumPrevQuotaNow ELSE 0 

									 END

								 ELSE 0

							 END

DECLARE @used_current_quota DECIMAL(22,1) --------- jumlah cuti tahun yang menggunakan quota tahun sekarang

SET @used_current_quota = @DurTaken - CASE WHEN @DurQuotaLast > @SumPrevQuotaNow THEN @SumPrevQuotaNow ELSE @DurQuotaLast END

DECLARE @Remaining_Quotas DECIMAL(22,1) -------- jumlah remaning quota di tahun sekarang setelah dikurangi cuti setengah hari & sisa cuti dari tahun kemarin

SET  @Remaining_Quotas = CASE 

							WHEN @now > @EndDeductionDate THEN @RemaingQuotaNow - (CASE WHEN @SumPrevQuotaNow > 0 THEN @DurQuotaSetengahNow ELSE @DurQuotaSetengahLast + @DurQuotaSetengahNow END) - @SisaCutiInExpiredDate