CRPTATTANDANCE_202506251429.sql - PERTALIFE Bugs - Redmine

Bug #3680 » CRPTATTANDANCE_202506251429.sql

Tri Rizqiaty, 06/25/2025 02:53 PM

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

(6-6/7)

Project

General

Profile

CLIENT SUPPORT » PERTALIFE Bugs

Bug #3680 » CRPTATTANDANCE_202506251429.sql