ALTER PROCEDURE [dbo].[PRPTTMABSENCEPHOTO]
(
	@EmployeeID VARCHAR (20),
	----@Period VARCHAR (6),	--// komen by Tri 20250728
	@CompanyID VARCHAR(20)
	,@StartDate VARCHAR(20)		--// add by Tri 20250728
	,@EndDate VARCHAR(20)		--// add by Tri 20250728
)
AS

--DECLARE @EmployeeID VARCHAR (20)='00000012'
----DECLARE @Period VARCHAR (6)='202505'		--// komen by Tri 20250728
--DECLARE @CompanyID VARCHAR(20)  = '1004'
--DECLARE @StartDate VARCHAR(20) = '20240101'	--// add by Tri 20250728
--DECLARE @EndDate VARCHAR(20) = '20250801'		--// add by Tri 20250728

--DECLARE @EndDate VARCHAR(20) = dbo.fn_formatdatetime(DATEADD(ms, -3, dateadd(mm, datediff(mm, 0, (@Period+'01')) + 1, 0)), 'yyyymmdd')

select 
dbo.fn_formatdatetime(pa19.StartDate , 'dd MMMM yyyy') as  StartDate
,dbo.fn_formatdatetime(pa19.EndDate , 'dd MMMM yyyy') as  EndDate
,pa19.EmployeeID
,pa1.FullName
,att.AttendanceTypeDesc as AttendanceType
,left(pa19.StartTime,2)+':'+right(pa19.StartTime,2) as StartTime
,pa19.StartLocation
,pa68.FileDataClockIn
,left(pa19.EndTime,2)+':'+right(pa19.EndTime,2) as EndTime
,pa19.EndLocation
,pa68.FileDataClockOut
,pa19.AuditorFlag 
from PHRPA0019  pa19
left join PHRPA0068 pa68 on pa19.AuditorFlag=pa68.id and pa68.EmployeeID = pa19.EmployeeID
left join PHRTMATTTYP att on pa19.AttendanceType=att.AttendanceType and att.EndDate='99991231'
left join PHRPA0001 pa1 on pa1.EmployeeID=pa19.EmployeeID and pa1.EndDate='99991231'
LEFT JOIN dbo.PHRPA0002 pa2 ON pa2.EmployeeID = pa1.EmployeeID AND pa2.StartDate <= @EndDate AND pa2.EndDate >= @EndDate
where
(pa19.EmployeeID=@EmployeeID or @EmployeeID='')
----and left(pa19.StartDate,6)=@Period							--// komen by Tri 20250728
AND pa19.StartDate >= @StartDate AND pa19.StartDate <= @EndDate --// add by Tri 20250728
AND (pa2.CompanyID = @CompanyID OR @CompanyID = '')
order by pa19.StartDate,pa19.EmployeeID
