-- Control Perf Objects
-- #New 26.03.2020/J.Fes + Krzysztof
-- #LUp 08.08.2023/J.Fes 08.08.2023/J.Fes Check how long time ago the Status has not been changed
-- \..\Custom_SettingsGlobal\30-TSQLScripts\CBPerf_GetNextPacket.SQL

DECLARE @POrRUN1 INT

Declare @processTimeStart01 DATETIME
Declare @processDura1 integer
Declare @processDuraDifMax integer

-- #DOC: find how many counts with status P% or with status RUN.1
select @POrRUN1=count(*)
FROM dbo.Objects_tbl WITH (nolock)
WHERE
  Objects_tbl.#JobRule.ProcStateCol#='#ProcStateVal#'
  or
  Objects_tbl.#JobRule.ProcStateCol# like 'P%'

-- #DOC: If no Object is anymore in status RUN.1 then ..
if @POrRUN1 = 0
begin
 -- #Step: update all with Status RUN.1.W to RUN.1
 UPDATE dbo.Objects_tbl
   SET Objects_tbl.#JobRule.ProcStateCol#='#ProcStateVal#'
 WHERE
   Objects_tbl.#JobRule.ProcStateCol#='#ProcStateVal#.W'
end

-- add 10.12.2021 reset to RUN.1.W if more then 1 P
if @POrRUN1 > 1
begin
 -- #Step: update all with Status RUN.1.W to RUN.1
 UPDATE dbo.Objects_tbl
   SET Objects_tbl.#JobRule.ProcStateCol#='#ProcStateVal#'
 WHERE
   Objects_tbl.#JobRule.ProcStateCol# like 'P%'
end

if 1=1
 begin
  -- #New: 08.08.2023/J.Fes Check how long time ago the Status has not been changed
  select Top 1 @processTimeStart01 = [ProcessTimeStart] from [dbo].[JobControl_ObjectsProcessData_tbl]
  where 1=1
   and ([ProcessProcStateCol] = '#JobRule.ProcStateCol#')
   and [Row_isLastRes] = 1
   and [ProcessState] like 'P%'
  order by [ProcessTimeStart] desc

  -- #Doc: get duration 2 - via Convert(datetime,'2021-12-10 11:34:24',120)
  set @processDura1 = DateDiff(ss, @processTimeStart01, #DateTimeNow_ForSqlServer#)
  if @processDura1 is null set @processDura1 = 0

  -- #Doc set 1 h max duration time = 3600 sec
  set @processDuraDifMax = 3600 * 1
  -- set @processDuraDifMax = 30 * 1

  -- if Duration > 1h then reset all to RUN.1
  if @processDura1 >= @processDuraDifMax
  begin
   -- PRINT 'Duration ..1: ' + convert(varchar(max),@processDura1) + ' sec'
   -- #Step: update all with Status P% to RUN.1
   UPDATE dbo.Objects_tbl
      SET Objects_tbl.#JobRule.ProcStateCol#='#ProcStateVal#.W' -- '--Debug:' + convert(varchar(max),@processDura1)
   WHERE
      Objects_tbl.#JobRule.ProcStateCol# like 'P%'

   UPDATE dbo.JobControl_ObjectsProcessData_tbl
     SET JobControl_ObjectsProcessData_tbl.ProcessState='RESET'
   WHERE 1=1
    and ([ProcessProcStateCol] = '#JobRule.ProcStateCol#')
    -- and [Row_isLastRes] = 1
    and [ProcessState] like 'P%'

  end
end


--- Do the selection
SELECT TOP #JobObjectsN#
 Objects_tbl.*,
 Ir.ObjectPuid AS IrPuid,
 Ir.ObjectName AS IrName
FROM
 dbo.Objects_tbl WITH (nolock)
 LEFT OUTER JOIN dbo.Objects_Ir_Rel_tbl WITH (nolock) ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
 LEFT OUTER JOIN dbo.Objects_tbl Ir WITH (nolock) ON (dbo.Objects_Ir_Rel_tbl.IrId = Ir.Id)
WHERE
 Objects_tbl.ObjectClass='DS'
 AND Objects_tbl.#JobRule.ProcStateCol#='#ProcStateVal#'
 AND Objects_tbl.ObjectType IN ('UGMASTER')
ORDER BY
 (CASE Objects_tbl.ObjectUgMaster_Type
  WHEN 'SP' THEN 11
  WHEN 'AP' THEN 12
  ELSE 19 END) ASC,
 Ir.ObjectUgMaster_APLevels ASC,
 Ir.ObjectUgMaster_CompCount ASC,
 Ir.ObjectPuid,
 (CASE Objects_tbl.ObjectType
  WHEN 'UGMASTER' THEN 11
  WHEN 'UGALTREP' THEN 12
  WHEN 'UGMANIFESTATION' THEN 13
  WHEN 'UGPART' THEN 14
  ELSE 19 END) ASC,
 Objects_tbl.ObjectName ASC

