Bug #983
[HR-TIME DATA] Report Time Sheet Recap masih ada keliru untuk membaca keterlambatan nya
Description
dear tim developer mohon support nya untuk perbaikan pada report timesheet recap karena pada kolom2 tersebut masih ada yang perlu diperbaiki.
contoh sampel employeeid 00000003 pada blan februari memiliki keterlambatan 31 hari padahal di bulan feb tanggal hanya 28 hari.
rohto production
remote.minovais.com:61121
database sql server 2019
remote.minovais.com, 1445\MSSQLSERVER2019
stored procedures:
Rpt_Rohto_HR_TM_TimeSheetRecap
Files
Updated by Muhammad Bintar over 2 years ago
update sp:
USE [MinovaES_Rohto_Prod]
GO
/****** Object: StoredProcedure [dbo].[Rpt_Rohto_HR_TM_TimeSheetRecap] Script Date: 17/03/2022 16:27:46 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--USE [MinovaHR_ESS_Rohto_Development]
--GO
--/*** Object: StoredProcedure [dbo].[Rpt_Rohto_HR_TM_TimeSheetRecap] Script Date: 12/23/2015 16:05:04 */
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
ALTER PROCEDURE [dbo].[Rpt_Rohto_HR_TM_TimeSheetRecap]
(
@EmployeeID varchar (20)
, @StartDate varchar (20)
,@EndDate varchar (20)
,@CompanyID VARCHAR(20)
,@PayrollGroup varchar (20)
,@EmployeeOffice varchar (20)
,@EmployeeArea varchar (20)
)
AS--DECLARE @EmployeeID varchar (8)= '00000003'
--DECLARE @StartDate varchar (8) = '20220201'
--DECLARE @EndDate varchar (8) = '20220228'
--DECLARE @CompanyID VARCHAR = '1000'
--DECLARE @PayrollGroup varchar (8) = ''
--DECLARE @EmployeeOffice varchar (8) = ''
--DECLARE @EmployeeArea varchar (8) = ''
DECLARE @t_emp TABLE
(
emp_id VARCHAR(10)
,emp_name VARCHAR(200)
,comp_id VARCHAR(10)
,paygroup VARCHAR(10)
,area VARCHAR(10)
,office VARCHAR(10)
,org VARCHAR(10)
,cc VARCHAR(10)
,pos VARCHAR(10)
,emp_status VARCHAR(10)
,comp_desc VARCHAR(200)
,paygroup_desc VARCHAR(200)
,area_desc VARCHAR(200)
,office_desc VARCHAR(200)
,org_desc VARCHAR(200)
,cc_desc VARCHAR(200)
,pos_desc VARCHAR(200)
,emp_status_desc VARCHAR(200)
,ws_type VARCHAR(20)
,ws_type_desc VARCHAR(200)
,pygrup_desc VARCHAR(200)
,are_desc VARCHAR(200)
,offs_desc VARCHAR(200)
,coms_desc VARCHAR(200)
)DECLARE @t_ws TABLE
(
emp_id VARCHAR(10)
,ws_type VARCHAR(10)
,datespecified VARCHAR(20)
,day_type VARCHAR(20)
,time_start VARCHAR(10)
)DECLARE @t_att_detail TABLE
(
emp_id VARCHAR(10)
,start_date VARCHAR(20)
,end_date VARCHAR(20)
,time_start VARCHAR(10)
,att_type VARCHAR(20)
,cek_telat DECIMAL(18,0)
,flag_telat VARCHAR(50)
,jml_telat DECIMAL(18,0)
)DECLARE @t_att TABLE
(
emp_id VARCHAR(10)
,att_type VARCHAR(20)
,tot_att DECIMAL(18,0)
)DECLARE @t_emp_telat TABLE
(
emp_id VARCHAR(10)
,frek_telat DECIMAL(18,0)
,sum_telat DECIMAL(18,0)
)DECLARE @t_abs TABLE
(
emp_id VARCHAR(10),abs_type VARCHAR(20)
,tot_abs DECIMAL(18,0)
)DECLARE @t_header TABLE
(
emp_id VARCHAR(10)
,att_type VARCHAR(20)
,att_desc VARCHAR(250)
,abs_type VARCHAR(20)
,abs_desc VARCHAR(250)
)DECLARE @t_att_head TABLE
(
emp_id VARCHAR(10)
,att_type VARCHAR(20)
)DECLARE @t_abs_head TABLE
(
emp_id VARCHAR(10)
,abs_type VARCHAR(20)
)DECLARE @t_att_fix TABLE
(
emp_id VARCHAR(20)
,att_type VARCHAR(10)
,tot_att DECIMAL(18,0)
)DECLARE @t_result TABLE
(
stdate VARCHAR
,endate VARCHAR
,emp_id VARCHAR
,att_type VARCHAR
,att_desc VARCHAR
,abs_type VARCHAR
,abs_desc VARCHAR
,tot_att DECIMAL
,tot_abs DECIMAL
,flag_telat VARCHAR
,sum_telat DECIMAL
,frek_telat DECIMAL
,emp_name VARCHAR
,comp_desc VARCHAR
,paygroup_desc VARCHAR
,area_desc VARCHAR
,office_desc VARCHAR
,org_desc VARCHAR
,pos_desc VARCHAR
,emp_statu_desc VARCHAR
,ws_type_desc VARCHAR
,pygrup_desc VARCHAR
,are_desc VARCHAR
,offs_desc VARCHAR
,coms_desc VARCHAR
)
DECLARE @menit_telat DECIMAL
DECLARE @menit_awal DECIMAL
SELECT DISTINCT @menit_awal = Value2 , @menit_telat = Value1 FROM dbo.PCMEPGENPARAM WHERE Parameter = 'HR_TM_MIN_TELAT'
------------------ // get data employee // -------------------
INSERT INTO @t_emp
SELECT DISTINCT
md2.EmployeeID
,md1.FullName
,md2. CompanyID
,md2.PayrollGroup
,md2.EmployeeArea
,md2.EmployeeOffice
,md2.Organization
,md2.CostCenter
,md2.Position
,md2.EmployeeStatus
,comp.CompanyDescription
,pygroup.PayrollGroupDescription
,area.EmployeeAreaDescription
,office.EmployeeOfficeDesc
,o.ObjectDescription
,cc.ObjectDescription
,p.ObjectDescription
,sta.EmployeeStatusDescription
,md25.WorkScheduleType
,ws.WorkScheduleTypeDesc
,pygrup.PayrollGroupDescription
,are.EmployeeAreaDescription
,offs.EmployeeOfficeDesc
,com.CompanyDescription
--SELECT * FROM PHRPA0025 WHERE EmployeeID = '00000003'
FROM dbo.PHRPA0002 AS md2
LEFT JOIN dbo.PHRPA0001 AS md1
--ON md1.landscape = md2.landscape
ON md1.EmployeeID = md2.EmployeeID
AND md1.StartDate <= @EndDate
AND md1.EndDate >= @EndDate
LEFT JOIN dbo.PCMEPCOMPID AS comp
--ON comp. = md2.landscape
ON comp.CompanyID = md2.CompanyIDLEFT JOIN dbo.PCMEPCOMPID AS com
--ON comp. = md2.landscape
ON com.CompanyID = @CompanyID
LEFT JOIN dbo.PHRPYPGRUP AS pygroup
-- ON pygroup.landscape = md2.landscape
ON pygroup.PayrollGroup = md2.PayrollGroup
LEFT JOIN dbo.PHRPYPGRUP AS pygrup
-- ON pygroup.landscape = md2.landscape
ON pygrup.PayrollGroup = @PayrollGroupLEFT JOIN dbo.PCMEPEMPAREA AS area
ON
--area.landscape = md2.landscape
--AND
area.EmployeeArea = md2.EmployeeArea
LEFT JOIN dbo.PCMEPEMPAREA AS are
ON
--area.landscape = md2.landscape
--AND
are.EmployeeArea = @EmployeeArea
LEFT JOIN dbo.PCMEPEMPOFF AS office
--ON office. = md2.landscape
--AND
ON
office.EmployeeOffice = md2.EmployeeOffice
LEFT JOIN dbo.PCMEPEMPOFF AS offs
--ON office. = md2.landscape
--AND
ON
offs.EmployeeOffice = @EmployeeOffice
LEFT JOIN dbo.PCMEPEMPSTAT AS sta
ON
--sta.landscape = md2.landscape
--AND
sta.EmployeeStatus = md2.EmployeeStatus
LEFT JOIN dbo.PHROM0001 AS o
ON
--o.landscape = md2.landscape
--AND
o.ObjectClass= 'O'
AND o.ObjectID = md2.Organization
--AND o.StartDate<= @EndDate
--AND o.EndDate >= @EndDate
LEFT JOIN dbo.PHROM0001 AS cc
--ON cc.landscape = md2.landscape
ON cc.ObjectClass = 'CC'
AND cc.ObjectID = md2.costcenter
--AND cc.StartDate <= @EndDate
--AND cc.EndDate >= @EndDate
LEFT JOIN dbo.PHROM0001 AS p
--ON p.landscape = md2.landscape
ON
p.ObjectClass = 'P'
AND p.ObjectID = md2.Position
--AND p.StartDate <= @EndDate
--AND p.EndDate >= @EndDate
INNER JOIN dbo.PHRPA0025 AS md25
ON
--md25.landscape = md2.landscape
md25.EmployeeID = md2.EmployeeID
AND md25.StartDate <= @StartDate
AND md25.EndDate >= @StartDate
LEFT JOIN dbo.PHRTMWSTYP AS ws
--ON ws.landscape = md2.landscape
ON ws.WorkScheduleType = md25.WorkScheduleType
WHERE
--md2.landscape = @landscape
(md2.EmployeeID = @EmployeeID OR @EmployeeID = '')
AND (md2.CompanyID = @CompanyID OR @CompanyID = '')
AND (md2.PayrollGroup = @PayrollGroup OR @PayrollGroup = '')
AND (md2.EmployeeArea = @EmployeeArea OR @EmployeeArea = '')
AND (md2.EmployeeOffice =@EmployeeOffice OR @EmployeeOffice = '')
AND md2.StartDate <= @EndDate
AND md2.EndDate >= @EndDate
----------------- // get detail hari kerja per employee // ----------------
INSERT INTO @t_ws
SELECT DISTINCT
t_emp.emp_id
,t_emp.ws_type
,cal.datespecified
,cal.DayType
,daytipe.StartTime
FROM @t_emp AS t_emp
INNER JOIN dbo.PHRTMWSCAL AS cal
ON
--cal.landscape = t_emp.landscape
--cal.Country = 'IDN'
--AND
cal.WorkScheduleType = t_emp.ws_type
LEFT JOIN dbo.PHRTMDAYTYP AS daytipe
ON
--daytipe.landscape = t_emp.landscape
daytipe.DayType = cal.DayType
WHERE cal.DateSpecified >= @StartDate AND cal.DateSpecified <= @EndDateINSERT INTO @t_header
SELECT DISTINCT
'666'
,b.AttendanceType
,b.AttendanceTypeDesc
,c.AbsenceType
,c.AbsenceTypeDesc
FROM dbo.PHRTMATTTYP AS b
LEFT JOIN dbo.PHRTMABSTYP AS c
ON b.StartDate= c.StartDate
--WHERE b.landscape = @landscape
--INSERT INTO @t_header
--SELECT DISTINCT a.landscape
-- ,a.emp_id
-- ,b.atendancetype
-- ,b.description
-- ,c.absencetype
-- ,c.description
--FROM @t_ws AS a
--LEFT JOIN dbo.base_cust_ref_attendancetype AS b
-- ON a.landscape = b.landscape
--LEFT JOIN dbo.base_cust_ref_absencetype AS c
-- ON a.landscape = c.landscape
------------------// get data attendance per employee //----------------------
INSERT INTO @t_att_detail
--SELECT * FROM PHRPA0019 WHERE EmployeeID= '00000003' AND StartDate >= '20220201' AND EndDate <= '20220228'
SELECT DISTINCT
md19.EmployeeID
,md19.StartDate
,md19.EndDate
,t_ws.time_start
,md19.AttendanceType
,CONVERT,dbo.fn_diffTime(md19.StartDate,md19.StartDate,t_ws.time_start,md19.StartTime) * 60) AS cek_telat
,CASE
WHEN CONVERT,dbo.fn_diffTime(md19.StartDate,md19.StartDate,t_ws.time_start,md19.StartTime) * 60) > @menit_telat
THEN 'Telat'
END AS flag_telat
,CASE
WHEN CONVERT,dbo.fn_diffTime(md19.StartDate,md19.StartDate,t_ws.time_start,md19.StartTime) * 60) > @menit_telat
THEN CONVERT,dbo.fn_diffTime(md19.StartDate,md19.StartDate,t_ws.time_start,md19.StartTime) * 60) - @menit_awal
END AS jml_telat
FROM dbo.PHRPA0019 AS md19
INNER JOIN @t_emp AS t_emp1
ON
--md19.landscape = t_emp1.landscape
md19.EmployeeID = t_emp1.emp_id
LEFT JOIN @t_ws AS t_ws
ON
--t_ws.landscape = md19.landscape
t_ws.emp_id = md19.EmployeeID
WHERE
--md19.landscape = @landscape
md19.StartDate >= @StartDate AND md19.EndDate <= @EndDate
AND
md19.StartDate = t_ws.datespecified
------------------// menghitung jumlah attendace setiap employee //----------------
INSERT INTO @t_att
SELECT DISTINCT
t_att_det1.emp_id
,t_att_det1.att_type
,COUNT
FROM @t_att_detail AS t_att_det1
GROUP BY
t_att_det1.emp_id
,t_att_det1.att_type
ORDER BY t_att_det1.emp_id
------------------// menghitung jumlah telat setiap employee //----------------
INSERT INTO @t_emp_telat
SELECT DISTINCT
t_att_det.emp_id
,COUNT AS frekuensi_telat
,SUM AS tot_telat
FROM @t_att_detail AS t_att_det
WHERE t_att_det.flag_telat = 'Telat'
GROUP BY
t_att_det.emp_id
------------------// menghitung jumlah absence setiap employee //----------------
INSERT INTO @t_abs
SELECT DISTINCT
md18.EmployeeID
,md18.AbsenceType
,COUNT AS jml_abs
FROM dbo.PHRPA0018 AS md18
INNER JOIN @t_ws AS t_ws1
ON
--md18.landscape = t_ws1.landscape
md18.EmployeeID = t_ws1.emp_id
WHERE
--md18.landscape = @landscape
md18.StartDate >= @StartDate AND md18.EndDate <= @EndDate
AND
t_ws1.datespecified >= md18.StartDate AND t_ws1.datespecified <= md18.EndDate
AND t_ws1.day_type <> 'OFF'
GROUP BY
md18.EmployeeID
,md18.AbsenceType
ORDER BY md18.EmployeeID
--INSERT INTO @t_result
--SELECT DISTINCT dbo.fn_formatdatetime(@StartDate, 'dd mmmm yyyy') AS stdate
-- ,dbo.fn_formatdatetime(@EndDate , 'dd mmmm yyyy') AS endate
-- ,h.emp_id
-- ,h.att_type
-- ,h.att_desc
-- ,h.abs_type
-- ,h.abs_desc
-- ,3
-- ,3
-- ,'telat' AS telat
-- ,0
-- ,0
-- ,''
-- ,''
-- ,''
-- ,''
-- ,''
-- ,''
-- ,''
-- ,''
-- ,''
-- ,''
-- ,''
-- ,''
-- ,''
--FROM @t_header AS h WHERE emp_id = '666'
INSERT INTO @t_result
SELECT DISTINCT dbo.fn_formatdatetime(@StartDate, 'dd mmmm yyyy') AS stdate
,dbo.fn_formatdatetime(@EndDate , 'dd mmmm yyyy') AS endate
,at.emp_id
,at.att_type
,DeskripsiAtt.att_desc
,ab.abs_type
,Deskripsiabs.abs_desc
,at.tot_att
,ab.tot_abs
,'telat' AS telat
,tel.sum_telat
,telat.frek_telat
,te.emp_name
,te.comp_desc
,te.paygroup_desc
,te.area_desc
,te.office_desc
,te.org_desc
,te.pos_desc
,te.emp_status_desc
,te.ws_type_desc
,te.pygrup_desc
,te.are_desc
,te.offs_desc
,te.coms_desc
FROM @t_att AS at
LEFT JOIN @t_abs AS ab
ON at.emp_id = ab.emp_id
LEFT JOIN @t_emp_telat AS tel
ON at.emp_id = tel.emp_id
LEFT JOIN @t_emp_telat AS telat
ON at.emp_id = telat.emp_id
LEFT JOIN @t_emp AS te
ON
--te.landscape = at.landscape
te.emp_id = at.emp_id
LEFT JOIN @t_header AS DeskripsiAtt
ON at.att_type = DeskripsiAtt.att_type
AND DeskripsiAtt.emp_id = '666'
LEFT JOIN @t_header AS Deskripsiabs
ON ab.abs_type = Deskripsiabs.abs_type
AND DeskripsiAtt.emp_id = '666'WHERE ( tot_att > 0 )
SELECT * FROM @t_result
/*
SELECT DISTINCT dbo.fn_formatdatetime(@start_date, 'dd mmmm yyyy') AS stdate
,dbo.fn_formatdatetime(@end_date, 'dd mmmm yyyy') AS endate
,head.landscape
,head.emp_id
,head.att_type
,head.att_desc
,head.abs_type
,head.abs_desc
,at.tot_att
,ab.tot_abs
,'telat' AS telat
,tel.sum_telat
,telat.frek_telat
,te.emp_name
,te.comp_desc
,te.paygroup_desc
,te.area_desc
,te.office_desc
,te.org_desc
,te.pos_desc
,te.emp_status_desc
,te.ws_type_desc
FROM @t_header AS head
LEFT JOIN @t_att AS at
ON head.emp_id = at.emp_id
AND head.att_type = at.att_type
LEFT JOIN @t_abs AS ab
ON head.emp_id = ab.emp_id
AND head.abs_type = ab.abs_type
LEFT JOIN @t_emp_telat AS tel
ON head.emp_id = tel.emp_id
LEFT JOIN @t_emp_telat AS telat
ON head.emp_id = telat.emp_id
LEFT JOIN @t_emp AS te
ON te.landscape = head.landscape
AND te.emp_id = head.emp_id
WHERE ( tot_att > 0 )
*/
Updated by M Azid Wahyudi over 2 years ago
- Status changed from New to QA Test
- Assignee changed from M Azid Wahyudi to Muhammad Bintar
dear mas bintar
perubahan ada di sp