CLIENT SUPPORT » KJL BUGS

--USE [MinovaES_KJL]

--GO

--/****** Object:  StoredProcedure [dbo].[PRPTTMATTENDANCE]    Script Date: 5/21/2024 1:20:09 PM ******/

--SET ANSI_NULLS ON

--GO

--SET QUOTED_IDENTIFIER ON

--GO

--ALTER PROCEDURE [dbo].[PRPTTMATTENDANCE]

--    (

--      @StartDate VARCHAR(18) ,

--      @EndDate VARCHAR(18) ,

--      @EmployeeID VARCHAR(50) ,

--      @EmployeeArea VARCHAR(18) ,

--      @EmployeeOffice VARCHAR(18) ,

--      @Organization VARCHAR(18) ,

--      @CompanyID VARCHAR(20) ,

--      @MenuID VARCHAR(10) ,

--      @UserID VARCHAR(10)

--    )

--AS 

-------- Rpt_Product_HR_TM_Attendace		3000008 --------    

DECLARE @Organization VARCHAR(8) = ''

DECLARE @StartDate VARCHAR(8) = '20240422'

DECLARE @EndDate VARCHAR(8) = '20240521'

DECLARE @CompanyID VARCHAR(8) = '1000'

DECLARE @EmployeeArea VARCHAR(18) = ''

DECLARE @EmployeeOffice VARCHAR(18) = ''

DECLARE @EmployeeID VARCHAR(18) = '17030021'

DECLARE @MenuID VARCHAR(10) = 'TMR0002'

DECLARE @UserID VARCHAR(10) = 'shofwan'

DECLARE @date_min VARCHAR(12) = '18000101'

DECLARE @date_max VARCHAR(12) = '99991231'

------------------ // Get Otorisasi //---------------

DECLARE @TableResult TABLE ( EmployeeID VARCHAR(20) )

INSERT  INTO @TableResult

        EXEC dbo.GETEMPIDAUTHBYMENU @MenuID = @MenuID, -- varchar(max)

            @UserID = @UserID

 -- varchar(max)

------------------ // End Get Otorisasi //---------------

IF ( LTRIM(RTRIM(@StartDate)) <> '' )

    BEGIN 

        SET @date_min = @StartDate

        IF ( LTRIM(RTRIM(@EndDate)) <> '' )

            SET @date_max = @EndDate

        ELSE

            SET @date_max = @StartDate

    END

DECLARE @now VARCHAR(8) 

SET @now = dbo.fn_formatdatetime(GETDATE(), 'yyyymmdd')

------------get organisasi-----------

DECLARE @cal AS TABLE

    (

      EmployeeID VARCHAR(50) ,

      DateSpecified VARCHAR(8) 

      --,DayType VARCHAR(8)

    )

INSERT  INTO @cal

        SELECT  md25.EmployeeID ,

                cal.DateSpecified 

                --,cal.DayType

        FROM    dbo.PHRPA0025 md25

                LEFT JOIN dbo.PHRTMWSCAL cal ON cal.WorkScheduleType = md25.WorkScheduleType

                                                AND cal.DateSpecified >= @StartDate

                                                AND cal.DateSpecified <= @EndDate

        WHERE   md25.StartDate <= SUBSTRING(@StartDate, 1, 6) + '31'

                AND md25.EndDate >= SUBSTRING(@StartDate, 1, 6) + '31'

                AND ( md25.EmployeeID = @EmployeeID

                      OR @EmployeeID = ''

                    )

---------------------------------------------// Declare Time //--------------------------------------------------------

select distinct

EmployeeID,StartDate,EndDate,CreateDate

,CAST((Left(StartTime,2)*60+RIGHT(StartTime,2)) AS FLOAT)  AS MinuteStart

,CAST((Left(EndTime,2)*60+RIGHT(EndTime,2)) AS FLOAT) AS MinuteEnd

into #MinutesTable

from PHRPA0019

where 

( EmployeeID = @EmployeeID OR @EmployeeID = '')

and

(@StartDate<=StartDate or @StartDate='')

and

(@EndDate>=EndDate-1 or @EndDate='')

select

mnt.EmployeeID,mnt.StartDate,mnt.EndDate,mnt.MinuteStart,mnt.MinuteEnd,mnt.CreateDate,

case

when mnt.MinuteEnd =0 then 0

when mnt.MinuteStart=0 then 0

when mnt.MinuteEnd>mnt.MinuteStart then MinuteEnd-MinuteStart

when mnt.MinuteEnd<mnt.MinuteStart then MinuteEnd+(1440-MinuteStart)

end as MinuteDuration

into #MinutesTable2

from #MinutesTable mnt

select 

mnt2.EmployeeID,mnt2.StartDate,mnt2.EndDate,mnt2.CreateDate

,FLOOR(mnt2.MinuteDuration/60) AS HourDuration

,ROUND((mnt2.MinuteDuration/60-FLOOR(mnt2.MinuteDuration/60))*60,0) AS MinuteDuration

into #MinutesTable3

from #MinutesTable2 mnt2

select

EmployeeID,StartDate,EndDate,CreateDate

,CAST(HourDuration AS VARCHAR) AS HourDuration

,RIGHT('00' + CAST(MinuteDuration AS VARCHAR(2)), 2) AS MinuteDuration

into #MinutesTable4

from #MinutesTable3

---------------------------------------------// Declare Time //--------------------------------------------------------

SELECT DISTINCT

        DENSE_RANK() OVER ( ORDER BY cal.EmployeeID ) AS GroupSeq ,

        cal.DateSpecified ,

        cal.EmployeeID ,

		dbo.fn_formatdatetime_indonesia(@StartDate, 'DD MMMM YYYY') as letterDate1,

		dbo.fn_formatdatetime_indonesia(@EndDate, 'DD MMMM YYYY') as letterDate2,

        --ROW_NUMBER() OVER ( PARTITION BY cal.EmployeeID ORDER BY cal.EmployeeID ASC ) AS seq ,

		'' AS seq,

        dbo.fn_formatdatetime(CASE WHEN md19.StartDate = ''

                                        OR md19.StartDate IS NULL

                                   THEN cal.DateSpecified

                                   ELSE md19.StartDate

                              END, 'dd/mm/yyyy') AS stdt ,

        dbo.fn_formatdatetime(CASE WHEN md19.EndDate = ''

                                        OR md19.StartDate IS NULL

                                   THEN cal.DateSpecified

                                   ELSE md19.EndDate

                              END, 'dd/mm/yyyy') AS endt ,

        CASE WHEN md19.StartDate = ''

                  OR md19.StartDate IS NULL THEN cal.DateSpecified

             ELSE md19.StartDate

        END AS StartDate ,

        CASE WHEN md19.EndDate = ''

                  OR md19.StartDate IS NULL THEN cal.DateSpecified

             ELSE md19.EndDate

        END AS EndDate ,

		--, cal.DayType,

        SUBSTRING(md19.StartTime,1,2) + '.' + SUBSTRING(md19.StartTime,3,2) AS StartTime,

        SUBSTRING(md19.EndTime,1,2) + '.' + SUBSTRING(md19.EndTime,3,2) AS EndTime,

        md19.AttendanceType ,

		md19.CreateDate,

        md2.EmployeeArea ,

        md2.EmployeeOffice ,

        md2.CompanyID ,

        md1.FullName ,

        att.AttendanceTypeDesc AS att_desc ,

		mnt4.HourDuration*60 + mnt4.MinuteDuration as MinuteDuration,

		CASE WHEN mnt4.HourDuration is null and mnt4.MinuteDuration is null then '0.00'

		ELSE CONCAT(mnt4.HourDuration,'.', mnt4.MinuteDuration) END AS Duration,

        md19.CreateBy ,

        md19.ChangeBy

FROM    @cal AS cal

        LEFT JOIN dbo.PHRPA0019 md19 ON md19.EmployeeID = cal.EmployeeID

                                        AND cal.DateSpecified = md19.StartDate

        LEFT JOIN dbo.PHRPA0002 md2 ON md2.EmployeeID = cal.EmployeeID

                                       AND md2.StartDate <= @now

                                       AND md2.EndDate ='99991231'

									   and md2.EmployeeStatus='01'

        LEFT JOIN dbo.PHRPA0001 md1 ON md1.EmployeeID = cal.EmployeeID

                                       AND md1.StartDate <= @now

                                       AND md1.EndDate >= @now

        LEFT JOIN dbo.PHRTMATTTYP att ON md19.AttendanceType = att.AttendanceType

        LEFT JOIN @TableResult AS lt ON lt.EmployeeID = md2.EmployeeID

		LEFT JOIN #MinutesTable4 AS mnt4 ON cal.EmployeeID = mnt4.EmployeeID and md19.StartDate = mnt4.StartDate and md19.EndDate = mnt4.EndDate and md19.CreateDate=mnt4.CreateDate

WHERE   

        ( md2.CompanyID = @CompanyID

       OR @CompanyID = '')

         AND ( md2.EmployeeArea = @EmployeeArea

              OR @EmployeeArea = ''

            )

        AND ( md2.EmployeeOffice = @EmployeeOffice

              OR @EmployeeOffice = ''

            )

        AND ( cal.DateSpecified >= @date_min

              AND cal.DateSpecified <= @date_max

            )

ORDER BY cal.EmployeeID ,

        cal.DateSpecified ASC

--select * from PHRPA0019

drop table #MinutesTable

drop table #MinutesTable2

drop table #MinutesTable3

drop table #MinutesTable4