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 
							WHEN @now <= @EndDeductionDate THEN @RemaingQuotaNow - ( @DurQuotaSetengahNow + @DurQuotaSetengahLast )
						 END


select
pa1.EmployeeID
,pa1.FullName
,pa1.ExternalID
,pa2.Position
,om1.ObjectDescription as PositionDescription
, dbo.fn_formatdatetime(absence.StartDate, 'yyyy/mm/dd') AS StartDate
, dbo.fn_formatdatetime(absence.EndDate, 'yyyy/mm/dd') AS EndDate
,absence.AbsenceTypeDesc
,absence.TotalAbsenceTaken
,absence.Notes
,@DurTaken AS totaaal
,@Use_Prev_Quota AS Use_Prev_Quota
,@used_current_quota AS used_current_quota
,detail.OriginalQuota
,detail.PreviousQuota AS previous_remaining_quota
,@Remaining_Quotas AS Remaining_Quotas
from PHRPA0001 pa1 
left join PHRPA0002 pa2 on pa1.EmployeeID=pa2.EmployeeID and @Year+'1231' between pa2.StartDate and pa2.EndDate
left join PHROM0001 om1 on pa2.Position=om1.ObjectID and om1.ObjectClass='P' and om1.EndDate='99991231'
left join #absence_data absence on pa1.EmployeeID=absence.EmployeeID
left join #absence_detail detail  on pa1.EmployeeID=detail.EmployeeID
where 
pa1.EmployeeID=@EmployeeID and @Year+'1231' between pa1.StartDate and pa1.EndDate

drop table #absence_data
drop table #absence_detail