-- JobMgr Query to get JT Data Report
-- #New: 15 May 2025 / Josef Feuerstein
Declare @JT_All_n integer,
 @HAS_JT_UntilLMod_n integer,
 @NxJT_All integer,
 @NxJT_UntilLMod  integer,
 @ProeJT_All integer,
 @ProeJT_UntilLMod integer,
 @ProNxJT_and_ProeJT integer,
 @JT_only integer,
 @XX integer


-- select FORMATMESSAGE('v1=%i, name=[%10s], v2=%d', @JT_All_n, @HAS_JT_UntilLMod_n, @)

-- DECLARE @res TABLE ([JT_All (n)] INT, [NxJT_UntilLMod:2023-10-16 (n)] INT)
-- DECLARE @JT_ALL INT

-- (SELECT TOP 1 [JT_All (n)] FROM @res)

--INSERT INTO @res
SELECT 
    @JT_All_n=SUM(Q.HAS_JT) -- AS []
  , @HAS_JT_UntilLMod_n=SUM(Q.HAS_JT_UntilLMod) -- AS []
  , @NxJT_All = SUM(Q.HAS_Nx) -- AS []
  , @NxJT_UntilLMod=SUM(Q.HAS_Nx_UntilLMod) -- AS []
  , @ProeJT_All = SUM(Q.HAS_Proe) -- AS []
  , @ProeJT_UntilLMod = SUM(Q.HAS_Proe_UntilLMod) --  AS []
  , @ProNxJT_and_ProeJT=SUM(CASE WHEN Q.HAS_Nx=1 AND Q.Has_Proe=1 THEN 1 ELSE 0 END) -- AS []
  , @JT_only = SUM(CASE WHEN Q.HAS_Nx=0 AND Q.Has_Proe=0 THEN 1 ELSE 0 END) -- AS []
FROM (
  SELECT IR.Id IR_ID, IR.ObjectCliName IR_CLINAME
     , 1 AS HAS_JT
	 , MAX(CASE WHEN DS.ObjectType IN ('DirectModel', 'DirectModelAssembly') AND DS.ObjectLastModDate <= {d'2023-10-16'} THEN 1 ELSE 0 END) AS Has_JT_UntilLMod
     , MAX(CASE WHEN DS.ObjectType = 'UGMASTER'            THEN 1 ELSE 0 END) AS HAS_Nx
	 , MAX(CASE WHEN DS.ObjectType = 'UGMASTER' AND DS.ObjectLastModDate <= {d'2023-10-16'} THEN 1 ELSE 0 END) AS HAS_Nx_UntilLMod
     , MAX(CASE WHEN DS.ObjectType IN ('ProPrt', 'ProAsm') THEN 1 ELSE 0 END) AS HAS_Proe
     , MAX(CASE WHEN DS.ObjectType IN ('ProPrt', 'ProAsm') AND DS.ObjectLastModDate <= {d'2024-08-05'} THEN 1 ELSE 0 END) AS Has_Proe_UntilLMod
  FROM [Objects_tbl]        IR
  JOIN [Objects_Ir_Rel_tbl] REL ON REL.IrId=IR.Id
  JOIN [Objects_tbl]        DS ON DS.Id=REL.ObjectId AND DS.ObjectClass = 'DS'
  WHERE IR.ObjectClass = 'IR'
    AND EXISTS (SELECT 1 
             FROM [Objects_Ir_Rel_tbl] JT_REL 
    JOIN [Objects_tbl]        JT_DS  ON JT_DS.Id=JT_REL.ObjectId AND JT_DS.ObjectClass = 'DS'
             WHERE JT_REL.IrId=IR.Id
      AND JT_DS.ObjectType IN ('DirectModel', 'DirectModelAssembly')
    )
  GROUP BY IR.Id, IR.ObjectCliName
) Q


DECLARE @res TABLE ([CaseType] Varchar(41), [counts (n)] INT)
INSERT INTO @res VALUES 
   ('01 - JT_All (n).........................:', @JT_All_n)
 , ('02 - NxJT_UntilLMod:2023-10-16 (n)......:', @HAS_JT_UntilLMod_n)
 , ('03 - NxJT_All (n).......................:', @NxJT_All)
 , ('04 - NxJT_UntilLMod:2023-10-16 (n)......:', @NxJT_UntilLMod)
 , ('05 - ProeJT_All (n).....................:', @ProeJT_All)
 , ('06 - ProeJT_UntilLMod:2024-08-05 (n)....:', @ProeJT_UntilLMod)
 , ('07 - ProNxJT_and_ProeJT (n).............:', @ProNxJT_and_ProeJT)
 , ('08 - JT_only (n)........................:', @JT_only)
 , ('09 - Check (03 + 05 + 08 - 07) = 01.....:', @NxJT_All + @ProeJT_All + @JT_only - @ProNxJT_and_ProeJT)
    ------------------------------------------ 

 select * from @res
