CLIENT SUPPORT » ROHTO BUGS

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.CompanyID

LEFT 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 = @PayrollGroup

LEFT 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 <= @EndDate

INSERT 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 )
*/