ALTER procedure [dbo].[CRPTATTANDANCE]

(
 @PeriodFrom VARCHAR (10),
 @PeriodTo VARCHAR (10),
 @AttendanceType VARCHAR (20),
 @CostCenter VARCHAR (50),
 @AbsenceType VARCHAR (50),
 @EmployeeID VARCHAR (20)
)
AS

--DECLARE @PeriodFrom VARCHAR (10)='20250101'
--DECLARE @PeriodTo VARCHAR (10)='20250131'
--DECLARE @AttendanceType VARCHAR (20)=''
--DECLARE @CostCenter VARCHAR (50)=''
--DECLARE @AbsenceType VARCHAR (50)=''
--DECLARE @EmployeeID VARCHAR (50)='05740202'

----select * from PHRTMDAYTYP
----select * from PHRPA0002

DECLARE @AttendanceTypeDesc VARChar (100) = (select top(1)AttendanceTypeDesc from PHRTMATTTYP where AttendanceType=@AttendanceType and EndDate='99991231')
DECLARE @CostCenterDesc VARCHAR (100) = (select top(1)ObjectDescription from PHROM0001 where ObjectID=@CostCenter and EndDate='99991231' and ObjectClass='CC')


DECLARE @Result TABLE 
(
    StartDate VARCHAR(20),
    EndDate VARCHAR(20),
    EmployeeID VARCHAR(20),
    FullName VARCHAR(MAX),
    CostCenter VARCHAR(MAX),
    CostCenterDescription VARCHAR(MAX),
    Position VARCHAR(MAX),
    PositionDescription VARCHAR(MAX),
    [Date] VARCHAR(MAX),
    AttendanceType VARCHAR(MAX),
    AttendanceTypeDesc VARCHAR(MAX),
    AbsenceType VARCHAR(MAX),
    AbsenceTypeDesc VARCHAR(MAX),
    StartTime VARCHAR(MAX),
    EndTime VARCHAR(MAX),
    AbsencePoinIn VARCHAR(MAX),
    AbsencePoinOut VARCHAR(MAX),
    WorkScheduleType VARCHAR(MAX),
    DayType VARCHAR(MAX),
    StartTimeDayType VARCHAR(MAX),
    DeviationIn VARCHAR(MAX),
    DeviationTimeIn VARCHAR(MAX),
    EndTimeDayType VARCHAR(MAX),
    DeviationOut VARCHAR(MAX),
    DeviationTimeOut VARCHAR(MAX),
    PRIMARY KEY (StartDate, EndDate, EmployeeID)
);

-- First Insert
INSERT INTO @Result
SELECT
    pa19.StartDate
    ,pa19.EndDate
    ,pa19.EmployeeID 
    ,pa1.FullName
    ,pa2.CostCenter
    ,ccdesc.ObjectDescription as CostCenterDescription
    ,pa2.Position
    ,posdesc.ObjectDescription as PositionDescription
    ,dbo.fn_formatdatetime(pa19.StartDate, 'DD MMMM YYYY') as [Date]
    ,pa19.AttendanceType
    ,atttyp.AttendanceTypeDesc
    ,NULL as AbsenceType
    ,NULL as AbsenceTypeDesc
    ,LEFT(pa19.StartTime, 2) + ':' + RIGHT(pa19.StartTime, 2) as StartTime
    ,LEFT(pa19.EndTime, 2) + ':' + RIGHT(pa19.EndTime, 2) as EndTime
    ,case when pa19.StartTime is null then '' when pa19.StartTime ='' then '' else '0.5' end as AbsencePoinIn 
    ,case when pa19.EndTime is null then '' when pa19.EndTime ='' then '' else '0.5' end as AbsencePoinOut 
    ,pa25.WorkScheduleType
    ,wscal.DayType
    ,daytype.StartTime as StartTimeDayType
    ,case when pa19.StartTime<=daytype.StartTime then 'Tidak Telat' when pa19.StartTime>daytype.StartTime then 'Telat' end as DeviationIn
    ,CASE 
        WHEN ISDATE('1900-01-01 ' + SUBSTRING(pa19.StartTime, 1, 2) + ':' + SUBSTRING(pa19.StartTime, 3, 2)) = 1 AND ISDATE('1900-01-01 ' + SUBSTRING(daytype.StartTime, 1, 2) + ':' + SUBSTRING(daytype.StartTime, 3, 2)) = 1 
            THEN 
                CASE 
                    WHEN DATEDIFF(MINUTE, 
                                  CAST('1900-01-01 ' + SUBSTRING(daytype.StartTime, 1, 2) + ':' + SUBSTRING(daytype.StartTime, 3, 2) AS DATETIME),
                                  CAST('1900-01-01 ' + SUBSTRING(pa19.StartTime, 1, 2) + ':' + SUBSTRING(pa19.StartTime, 3, 2) AS DATETIME)
                                 ) <= 0 THEN 0
                    ELSE DATEDIFF(MINUTE, 
                                  CAST('1900-01-01 ' + SUBSTRING(daytype.StartTime, 1, 2) + ':' + SUBSTRING(daytype.StartTime, 3, 2) AS DATETIME),
                                  CAST('1900-01-01 ' + SUBSTRING(pa19.StartTime, 1, 2) + ':' + SUBSTRING(pa19.StartTime, 3, 2) AS DATETIME)
                                 )
                END
        ELSE NULL 
    END AS DeviationTimeIn
    ,daytype.EndTime as EndTimeDayType
    ,case when pa19.EndTime<daytype.EndTime then 'Cepat' when pa19.EndTime>=daytype.EndTime then 'Tidak Cepat' end as DeviationOut
    ,CASE 
        WHEN ISDATE('1900-01-01 ' + SUBSTRING(pa19.EndTime, 1, 2) + ':' + SUBSTRING(pa19.EndTime, 3, 2)) = 1 AND ISDATE('1900-01-01 ' + SUBSTRING(daytype.EndTime, 1, 2) + ':' + SUBSTRING(daytype.EndTime, 3, 2)) = 1 
            THEN 
                CASE 
                    WHEN DATEDIFF(MINUTE, 
                                  CAST('1900-01-01 ' + SUBSTRING(pa19.EndTime, 1, 2) + ':' + SUBSTRING(pa19.EndTime, 3, 2) AS DATETIME), 
                                  CAST('1900-01-01 ' + SUBSTRING(daytype.EndTime, 1, 2) + ':' + SUBSTRING(daytype.EndTime, 3, 2) AS DATETIME)
                                 ) <= 0 THEN 0
                    ELSE DATEDIFF(MINUTE, 
                                  CAST('1900-01-01 ' + SUBSTRING(pa19.EndTime, 1, 2) + ':' + SUBSTRING(pa19.EndTime, 3, 2) AS DATETIME), 
                                  CAST('1900-01-01 ' + SUBSTRING(daytype.EndTime, 1, 2) + ':' + SUBSTRING(daytype.EndTime, 3, 2) AS DATETIME)
                                 )
                END
        ELSE NULL 
    END AS DeviationTimeOut
from PHRPA0019 pa19
left join PHRPA0001 pa1 on pa19.EmployeeID=pa1.EmployeeID and pa1.EndDate='99991231'
left join PHRPA0002 pa2 on pa19.EmployeeID = pa2.EmployeeID and pa2.EndDate='99991231'
left join PHROM0001 ccdesc on pa2.CostCenter=ccdesc.ObjectID and ccdesc.EndDate='99991231' and ccdesc.ObjectClass='CC'
left join PHROM0001 posdesc on pa2.Position=posdesc.ObjectID and posdesc.EndDate='99991231' and posdesc.ObjectClass='P'
left join PHRTMATTTYP atttyp on pa19.AttendanceType = atttyp.AttendanceType and atttyp.EndDate='99991231'
left join PHRPA0025 pa25 on pa19.EmployeeID=pa25.EmployeeID and (pa19.StartDate between pa25.StartDate and pa25.EndDate)
left join PHRTMWSCAL wscal on wscal.Country='0001' and pa19.StartDate = wscal.DateSpecified and pa25.WorkScheduleType=wscal.WorkScheduleType
left join PHRTMDAYTYP daytype on wscal.DayType=daytype.DayType  and daytype.EndDate='99991231'
where 
    pa19.StartDate BETWEEN @PeriodFrom and @PeriodTO
    and (pa2.EmployeeID = @EmployeeID or @EmployeeID='')
    and (pa2.CostCenter=@CostCenter or @CostCenter='')

-- Convert the period parameters to DATE
DECLARE @PeriodFromDate DATE = CONVERT(DATE, @PeriodFrom, 112);
DECLARE @PeriodToDate DATE = CONVERT(DATE, @PeriodTo, 112);

-- CTE to generate the sequence of dates
;WITH DateSequence AS (
    SELECT @PeriodFromDate AS DateValue
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateSequence
    WHERE DateValue < @PeriodToDate
)

-- Use MERGE to conditionally insert rows and update AbsenceTypeDesc
MERGE INTO @Result AS target
USING (
    SELECT 
        CONVERT(VARCHAR(10), ds.DateValue, 112) AS StartDate
        ,CONVERT(VARCHAR(10), ds.DateValue, 112) AS EndDate
        ,pa18.EmployeeID
        ,pa1.FullName
        ,pa2.CostCenter
        ,ccdesc.ObjectDescription as CostCenterDescription
        ,pa2.Position
        ,posdesc.ObjectDescription as PositionDescription
        ,dbo.fn_formatdatetime(CONVERT(VARCHAR(10), ds.DateValue, 112), 'DD MMMM YYYY') as [Date]
        ,NULL as AttendanceType
        ,NULL as AttendanceTypeDesc
        ,pa18.AbsenceType 
        ,CASE WHEN daytype.Flag = 'WORK' THEN abs_type.AbsenceTypeDesc ELSE 'Libur' END AbsenceTypeDesc
        ,NULL as StartTime
        ,NULL as EndTime
        ,NULL as AbsencePoinIn
        ,NULL as AbsencePoinOut
        ,NULL as WorkScheduleType
        ,NULL as DayType
        ,NULL as StartTimeDayType
        ,NULL as DeviationIn
        ,NULL as DeviationTimeIn
        ,NULL as EndTimeDayType
        ,NULL as DeviationOut
        ,NULL as DeviationTimeOut
    FROM 
        PHRPA0018 pa18
        INNER JOIN DateSequence ds 
			ON ds.DateValue BETWEEN CONVERT(DATE, pa18.StartDate, 112) AND CONVERT(DATE, pa18.EndDate, 112)
        left join PHRPA0001 pa1 on pa18.EmployeeID=pa1.EmployeeID and pa1.EndDate='99991231'
        left join PHRPA0002 pa2 on pa18.EmployeeID = pa2.EmployeeID and pa2.EndDate='99991231'
        left join PHROM0001 ccdesc on pa2.CostCenter=ccdesc.ObjectID and ccdesc.EndDate='99991231' and ccdesc.ObjectClass='CC'
        left join PHROM0001 posdesc on pa2.Position=posdesc.ObjectID and posdesc.EndDate='99991231' and posdesc.ObjectClass='P'
        left join PHRTMABSTYP abs_type on pa18.AbsenceType = abs_type.AbsenceType and abs_type.EndDate='99991231'
		LEFT JOIN dbo.PHRPA0025 pa25 ON pa1.EmployeeID = pa25.EmployeeID AND pa25.StartDate <= @PeriodTo AND pa25.EndDate >= @PeriodTo
		LEFT JOIN dbo.PHRTMWSCAL wscal ON pa25.WorkScheduleType = wscal.WorkScheduleType
		LEFT JOIN dbo.PHRTMDAYTYP daytype ON wscal.DayType = daytype.DayType 
    WHERE 
        CONVERT(DATE, pa18.StartDate, 112) >= @PeriodFromDate
        AND CONVERT(DATE, pa18.EndDate, 112) <= @PeriodToDate
        and (pa2.EmployeeID = @EmployeeID or @EmployeeID='')
        and (pa2.CostCenter=@CostCenter or @CostCenter='')
		AND wscal.DateSpecified >= pa18.StartDate AND wscal.DateSpecified <= pa18.EndDate
		AND CONVERT(DATE, wscal.DateSpecified, 112) = ds.DateValue
		AND daytype.DayType <> 'OFF'
) AS source
ON (target.StartDate = source.StartDate AND target.EndDate = source.EndDate AND target.EmployeeID = source.EmployeeID)
WHEN MATCHED THEN
     UPDATE SET 
        target.AbsenceTypeDesc = source.AbsenceTypeDesc,
        target.AbsencePoinIn = source.AbsencePoinIn,
        target.AbsencePoinOut = source.AbsencePoinOut
WHEN NOT MATCHED BY TARGET THEN
    INSERT (
        StartDate, EndDate, EmployeeID, FullName, CostCenter, CostCenterDescription, Position, PositionDescription, [Date], AttendanceType, AttendanceTypeDesc, AbsenceType, AbsenceTypeDesc, StartTime, EndTime, AbsencePoinIn, AbsencePoinOut, WorkScheduleType, DayType, StartTimeDayType, DeviationIn, DeviationTimeIn, EndTimeDayType, DeviationOut, DeviationTimeOut
    )
    VALUES (
        source.StartDate, source.EndDate, source.EmployeeID, source.FullName, source.CostCenter, source.CostCenterDescription, source.Position, source.PositionDescription, source.[Date], source.AttendanceType, source.AttendanceTypeDesc, source.AbsenceType, source.AbsenceTypeDesc, source.StartTime, source.EndTime, source.AbsencePoinIn, source.AbsencePoinOut, source.WorkScheduleType, source.DayType, source.StartTimeDayType, source.DeviationIn, source.DeviationTimeIn, source.EndTimeDayType, source.DeviationOut, source.DeviationTimeOut
    )
OPTION (MAXRECURSION 0);  -- Allow the recursion to run for a larger range


	
	--select * from @Result

	select 
	dbo.fn_formatdatetime(@PeriodFrom, 'DD MMMM YYYY') as PeriodFrom
	,dbo.fn_formatdatetime(@PeriodTo, 'DD MMMM YYYY') as PeriodTo
	,@AttendanceTypeDesc as AttDescHeader
	,@CostCenterDesc as CCHeader
	,StartDate
	,EndDate
	,EmployeeID
	,FullName
	,CostCenter
	,CostCenterDescription
	,Position
	,PositionDescription
	,Date
	,AttendanceType
	--,AttendanceTypeDesc
	--,AbsenceType
	--,AbsenceTypeDesc
	,case when AbsenceTypeDesc is null then AttendanceTypeDesc else AbsenceTypeDesc end as AttendanceTypeDesc
	,StartTime
	,EndTime
	,AbsencePoinIn
	,AbsencePoinOut
	,WorkScheduleType
	,DayType
	,StartTimeDayType
	,case when AttendanceType ='1000' then DeviationIn else NULL end as DeviationIn
	--,DeviationIn
	,case when AttendanceType ='1000' then DeviationTimeIn else NULL end as DeviationTimeIn
	--,DeviationTimeIn
	,EndTimeDayType
	-- Dinas, training, WFH, izin datang telat, izin pulang cepat
	,case when AttendanceType ='1000' then DeviationOut else NULL end as DeviationOut
	--,DeviationOut
	,case when AttendanceType ='1000' then DeviationTimeOut else NULL end as DeviationTimeOut
	--,DeviationTimeOut
	,case when AttendanceType ='1000' then (case when DeviationTimeIn is null then 0 else DeviationTimeIn end)+(case when DeviationTimeOut is null then 0 else DeviationTimeOut end) else NULL end as TotalDeviation
	--, as TotalDeviation
	from
	@Result
	where
	(AttendanceType=@AttendanceType or @AttendanceType='')
	order by StartDate,CostCenter,EmployeeID

	--drop table #Result

	--select * from PHRTMATTTYP