use [#DB_N#]
Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database...........: #DB_N#'
Print ' Executing SQL Script.....: JobMgr_30_ApplProceduresReports03_ListOfResults.sql Script'
Print ' #New.....................: 29.08.2013/J.Fes'
Print ' #LUp.....................: 07.03.2020/J.Fes'
Print ' #LUp.....................: 03.03.2022/J.Fes'
Print ' #LUp.....................: 11.08.2022/J.Fes'
Print '-------------------------------------------------------------------------'
Print '-- R E P O R T S - LIST OF Results  -------------------------------------'
Print '-------------------------------------------------------------------------'
Print ''


--------------------------------------------------------------------------------
Print '-- [Report_301_List_of_all_Datasets_With_ERRORS] ------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_301_List_of_all_Datasets_With_ERRORS")
Drop Procedure Report_301_List_of_all_Datasets_With_ERRORS
GO
CREATE PROCEDURE Report_301_List_of_all_Datasets_With_ERRORS
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )

-- Hinweis:
-- 25.04.2012 J.Fes dieser Report darf nur in Abstimmung mit den zugehörigen L&L Formularen angepasst werden.
--            |-> siehe ..\01-BinServer\Resources\ReportLayouts\Report_902_ProcessStatistic_And_ProcessResults

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_301_List_of_all_Datasets_With_ERRORS'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = 'DS' set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = 'IR' set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = 'I'  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

  -- 25.04.2012 08:25 New Columes: ObjectOwningSiteID,ObjectGroup,ObjectLastModUser,ObjectLastModDate,ObjectReleaseState,ObjectReleaseDate
  -- S.Or.Rep Object --> 0.SiteOwing.SorR
  -- List_Of_All_Datasets_With_Errors.S_Or_Rep Object -> List_Of_All_Datasets_With_Errors.0_SiteOwing_SorR
  -- 29.08.2013 16:10;LastCange:J.Fes
  set @ExecuteSQLstr =  'SELECT ' +
                        ' [ObjectFromSiteID] As [O.SiteID],' +

                        ' CASE [ObjectFromSiteID] ' +
                           ' WHEN [ObjectOwningSiteID] ' +
                           '   THEN ''1-SO (SiteObj)'' ' +
                           ' ELSE   ''2-RO (ReplObj)'' ' +
                           ' END                 AS [S.Or.Rep Object],' +

                        ' CASE [ObjectUgMaster_Type] ' +
                           ' WHEN ''SP'' ' +
                           '    THEN ''1-SP (Single Parts)'' ' +
                           ' WHEN ''AP'' ' +
                           '    THEN ''2-AP (Assembly)'' ' +
                           '    ELSE ''3-NP (No NX.Dataset)'' '  +
                           ' END                 AS [O.UGMaster_Type],'

                  if @ObjectClass <> "I"
                    begin
   set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' [IrName]            AS [O.IR.ID],' +
                           ' [ObjectName]        AS [O.Name],'
                     end

                  if @ObjectClass = "DS"
                    begin
   set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' [ObjectType]        AS [O.Type],'
                    end

   set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' [ObjectUgMaster_APLevels] As [O.AP.LevN],' +
                           ' [ObjectUgMaster_CompCount] As [O.AP.CompN],' +

                           ' [ObjectOwningSiteID] As [O.OwningSiteID],' +
                           ' [ObjectOwner] As [O.Owner],' +
                           ' [ObjectGroup] As [O.Group],' +
                           ' [ObjectLastModUser] As [O.LastModUser],' +
                           ' [ObjectLastModDate] As [O.LastModDate],' +
                           ' [ObjectReleaseState] As [O.ReleaseState],' +
                           ' [ObjectReleaseDate] As [O.ReleaseDate],' +

                           -- #New: 27.09.2012 J.Fes  + @ProcStateCol +
                           ' [' + @ProcStateCol + '] AS [P.State],' +
                           ' [ProcessResultCode] AS [P.Result.Code],' +
                           ' [ProcessResultMsg]  AS [P.Result.Message],' +
                           -- #New: 27.09.2012 J.Fes  + ProcessResultLink
                           ' [ProcessResultLink]  AS [P.Result.Link],' +
                           -- #New: 27.09.2012 J.Fes  +  ProcessResultHasWarning
                           ' [ProcessResultHasWarning]  AS [P.Result.WRN],' +
                           -- #New: 07.03.2017/J.Fes
                           ' [ProcessCMDLine]  AS [P.CMDLine],' +
                           ' [ProcessTimeStart]  AS [P.Time.Start],' +
                           ' [ProcessTimeEnd]  AS [P.Time.End]' +

                           ' from [dbo].[' + @AndProcessData_UDF + ']('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1) and' +
                           '        ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'

  -- 03.04.2012 15:28 J.Fes angepasst
  if CharIndex('%', @ObjectFromSiteID)>0
   begin
    if @ObjectFromSiteID <> '%'
     -- 05.03.2011 eingefuegt um bei % unnoetigen select like zu vermeiden
     begin
      -- Suche über [ObjectFromSiteID] mit Like Operator @ObjectFromSiteID mit Like Operator erforderlich
      set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' and ([ObjectFromSiteID]=' + '''' + @ObjectFromSiteID + '''' + ')'
     end
   end
   else
   begin
    if @ObjectFromSiteID <> ''
     -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
     begin
      set @ExecuteSQLstr = @ExecuteSQLstr +
                          ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
     end
   end

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
end
GO


-- neu erstellt 02.03.2010 23:58
Print '-- [Report_302_List_of_all_Datasets_With_Warnings] ----------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_302_List_of_all_Datasets_With_Warnings")
Drop Procedure Report_302_List_of_all_Datasets_With_Warnings
GO
CREATE PROCEDURE Report_302_List_of_all_Datasets_With_Warnings
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_302_List_of_all_Datasets_With_Warnings'
  DECLARE @ExecuteSQLstr nvarchar(max)

  /*
  set @ExecuteSQLstr =  'SELECT [ObjectFromSiteID] As [SiteID] ,' +

                        ' CASE [ObjectFromSiteID] ' +
                           ' WHEN [ObjectOwningSiteID] ' +
                           '   THEN ''1-SO (SiteObj)'' ' +
                           ' ELSE   ''2-RO (ReplObj)'' ' +
                           ' END                 AS [S.Or.Rep.Object], ' +

                        ' CASE [ObjectUgMaster_Type] ' +
                           ' WHEN ''SP'' ' +
                           '    THEN ''1-SP (Single Parts)'' ' +
                           '    ELSE ''2-AP (Assembly)'' '  +
                           ' END                 AS [O.UGMaster_Type],' +

                           ' [IrName]            AS [IR.Id],' +
                           ' [ObjectName]        AS [O.Name],' +
                           ' [ObjectType]        AS [O.Type],' +
                           ' [ProcessResultCode] AS [P.Result.Code],' +
                           ' [ProcessResultMsg]  AS [P.Result.Message],' +
                           ' [ProcessResultHasWarning] AS [P.Result.HasWarning],' +
                           ' [ProcessResultLink] AS [Link to Result]' +
                           ' from [dbo].[DS_AndProcessData_UDF]('  + '''' + @ProcStateCol + '''' + ')' +

                           ' WHERE  ([Row_isLastRes]=1 or [Row_isLastRes] is null) and' +
                           '        (' + @ProcStateCol + ' like ''D%'') and ([ProcessResultHasWarning]=''1'') -- and (ProcessResultCode <>' + '''' + '''' + ')'
  --                                                       |-> 23.08.2012 15:44 J.Fes @ProcStateCol + 'like ''D*''  engetragen
  */
  /* 26.02.2014/J.Fes Check also LL Print Reports after Update Filed Names */
  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = 'DS' set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = 'IR' set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = 'I'  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

  set @ExecuteSQLstr =  'SELECT ' +
                        ' [ObjectFromSiteID] As [O.SiteID],' +

                        ' CASE [ObjectFromSiteID] ' +
                           ' WHEN [ObjectOwningSiteID] ' +
                           '   THEN ''1-SO (SiteObj)'' ' +
                           ' ELSE   ''2-RO (ReplObj)'' ' +
                           ' END                 AS [S.Or.Rep Object],' +

                        ' CASE [ObjectUgMaster_Type] ' +
                           ' WHEN ''SP'' ' +
                           '    THEN ''1-SP (Single Parts)'' ' +
                           ' WHEN ''AP'' ' +
                           '    THEN ''2-AP (Assembly)'' ' +
                           '    ELSE ''3-NP (No NX.Dataset)'' '  +
                           ' END                 AS [O.UGMaster_Type],'

                  if @ObjectClass <> "I"
                    begin
   set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' [IrName]            AS [O.IR.ID],' +
                           ' [ObjectName]        AS [O.Name],'
                     end

                  if @ObjectClass = "DS"
                    begin
   set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' [ObjectType]        AS [O.Type],'
                    end

   set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' [ObjectUgMaster_APLevels] As [O.AP.LevN],' +
                           ' [ObjectUgMaster_CompCount] As [O.AP.CompN],' +

                           ' [ObjectOwningSiteID] As [O.OwningSiteID],' +
                           ' [ObjectOwner] As [O.Owner],' +
                           ' [ObjectGroup] As [O.Group],' +
                           ' [ObjectLastModUser] As [O.LastModUser],' +
                           ' [ObjectLastModDate] As [O.LastModDate],' +
                           ' [ObjectReleaseState] As [O.ReleaseState],' +
                           ' [ObjectReleaseDate] As [O.ReleaseDate],' +

                           -- #New: 27.09.2012 J.Fes  + @ProcStateCol +
                           ' [' + @ProcStateCol + '] AS [P.State],' +
                           ' [ProcessResultCode] AS [P.Result.Code],' +
                           ' [ProcessResultMsg]  AS [P.Result.Message],' +
                           -- #New: 27.09.2012 J.Fes  + ProcessResultLink
                           ' [ProcessResultLink]  AS [P.Result.Link],' +
                           -- #New: 27.09.2012 J.Fes  +  ProcessResultHasWarning
                           ' [ProcessResultHasWarning]  AS [P.Result.WRN]' +

                           ' from [dbo].[' + @AndProcessData_UDF + ']('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1) and' +
                           '        ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode = ''0'') and (ProcessResultCode <>' + '''' + '''' + ')' +
                           ' and ([ProcessResultHasWarning]=''1'')'


  if CharIndex('%', @ObjectFromSiteID)>0
   begin
    if @ObjectFromSiteID <> '%'
     -- 05.03.2011 eingefuegt um bei % unnoetigen select like zu vermeiden
     begin
      -- Suche über [ObjectFromSiteID] mit Like Operator @ObjectFromSiteID mit Like Operator erforderlich
      set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' and ([ObjectFromSiteID] like ' + '''' + @ObjectFromSiteID + '''' + ')'
     end
   end
   else
   begin
    if @ObjectFromSiteID <> ''
     -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
     begin
      set @ExecuteSQLstr = @ExecuteSQLstr +
                          ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
     end
   end

  if @DebugPrintOn = 1
  begin
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
end
GO

Print '-- [Report_303_List_of_all_happend_ERRORS] ------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_303_List_of_all_happend_ERRORS")
Drop Procedure Report_303_List_of_all_happend_ERRORS
GO

CREATE PROCEDURE Report_303_List_of_all_happend_ERRORS
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 24.04.2012 15:21 J.Fes
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_303_List_of_all_happend_ERRORS'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = 'DS' set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = 'IR' set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = 'I'  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [ObjectFromSiteID] As [SiteID],' +
                           ' [ProcessResultCode] AS [P.Result.Code],' +
                           ' [ProcessResultMsg]  AS [P.Result.Message]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode is not Null) and (ProcessResultCode <>' + '''' + '''' + ')'
  -- 24.02.2014 08:36 J.Fes ' WHERE  ([Row_isLastRes]=1 or [Row_isLastRes] is null)' + angepasst
  -- 03.04.2012 15:28 J.Fes angepasst
  if CharIndex('%', @ObjectFromSiteID)>0
   begin
    if @ObjectFromSiteID <> '%'
     -- 05.03.2011 eingefuegt um bei % unnoetigen select like zu vermeiden
     begin
      -- Suche über [ObjectFromSiteID] mit Like Operator @ObjectFromSiteID mit Like Operator erforderlich
      set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
     end
   end
   else
   begin
    if @ObjectFromSiteID <> ''
     -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
     begin
      set @ExecuteSQLstr = @ExecuteSQLstr +
                          ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
     end
   end

  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY ObjectFromSiteID,ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_304_List_of_all_happend_ERRORS_forAllSites] ------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_304_List_of_all_happend_ERRORS_forAllSites")
Drop Procedure Report_304_List_of_all_happend_ERRORS_forAllSites
GO

CREATE PROCEDURE Report_304_List_of_all_happend_ERRORS_forAllSites
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 02.01.2014 10:45 J.Fes
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_304_List_of_all_happend_ERRORS_forAllSites'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'
--   24.02.2014 08:35 J.Fes angepasst ' WHERE  ([Row_isLastRes]=1 or [Row_isLastRes] is null)' +
--   -- 03.04.2012 15:28 J.Fes angepasst
--   if CharIndex('%', @ObjectFromSiteID)>0
--    begin
--     if @ObjectFromSiteID <> '%'
--      -- 05.03.2011 eingefuegt um bei % unnoetigen select like zu vermeiden
--      begin
--       -- Suche über [ObjectFromSiteID] mit Like Operator @ObjectFromSiteID mit Like Operator erforderlich
--       set @ExecuteSQLstr = @ExecuteSQLstr +
--                            ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
--      end
--    end
--    else
--    begin
--     if @ObjectFromSiteID <> ''
--      -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
--      begin
--       set @ExecuteSQLstr = @ExecuteSQLstr +
--                           ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
--      end
--    end

  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_305_List_of_all_ERRORS_GroupBy_Type_forAllSites] -------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_305_List_of_all_ERRORS_GroupBy_Type_forAllSites")
Drop Procedure Report_305_List_of_all_ERRORS_GroupBy_Type_forAllSites
GO

CREATE PROCEDURE Report_305_List_of_all_ERRORS_GroupBy_Type_forAllSites
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 26.02.2014 11:04 J.Fes
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_305_List_of_all_ERRORS_GroupBy_Type_forAllSites'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [Object_SiteOwningTyp] AS [O.S.Or.Rep Object],' +
                           ' [ObjectUgMaster_Type] AS [O.Nx.Type],' +
                           ' [ObjectType] AS [O.Type],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'


  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [Object_SiteOwningTyp],[ObjectType],[ObjectUgMaster_Type],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

---------------- #New 04.03.2014  from BSH: Krzysztof --------------------------
Print '-- [Report_305_List_of_all_ERRORS_GroupBy_Owner] ------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_305_List_of_all_ERRORS_GroupBy_Owner")
Drop Procedure Report_305_List_of_all_ERRORS_GroupBy_Owner
GO

CREATE PROCEDURE Report_305_List_of_all_ERRORS_GroupBy_Owner
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 04.03.2014 13:00 K.Duszkiewicz//BSH
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_305_List_of_all_ERRORS_GroupBy_Owner'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [ObjectFromSiteID] As [SiteID],' +
                           ' [Object_SiteOwningTyp] AS [O.S.Or.Rep Object],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'

  if CharIndex('%', @ObjectFromSiteID)>0
     begin
      if @ObjectFromSiteID <> '%'
        -- 05.03.2011 eingefuegt um bei % unnoetigen select like zu vermeiden
      begin
        -- Suche über [ObjectFromSiteID] mit Like Operator @ObjectFromSiteID mit Like Operator erforderlich
        set @ExecuteSQLstr = @ExecuteSQLstr +
                             ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
       end
      end
      else
      begin
      if @ObjectFromSiteID <> ''
      -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
      begin
       set @ExecuteSQLstr = @ExecuteSQLstr +
                            ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
       end
      end

	  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [ObjectFromSiteID],[Object_SiteOwningTyp],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_306_List_of_all_ERRORS_GroupBy_Owner_forAllSites] ------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_306_List_of_all_ERRORS_GroupBy_Owner_forAllSites")
Drop Procedure Report_306_List_of_all_ERRORS_GroupBy_Owner_forAllSites
GO

CREATE PROCEDURE Report_306_List_of_all_ERRORS_GroupBy_Owner_forAllSites
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 04.03.2014 13:00 K.Duszkiewicz//BSH
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_306_List_of_all_ERRORS_GroupBy_Owner_forAllSites'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [Object_SiteOwningTyp] AS [O.S.Or.Rep Object],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'


  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [Object_SiteOwningTyp],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_307_List_of_all_ERRORS_GroupBy_NXType] -----------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_307_List_of_all_ERRORS_GroupBy_NXType")
Drop Procedure Report_307_List_of_all_ERRORS_GroupBy_NXType
GO

CREATE PROCEDURE Report_307_List_of_all_ERRORS_GroupBy_NXType
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 04.03.2014 13:00 K.Duszkiewicz//BSH
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_307_List_of_all_ERRORS_GroupBy_NXType'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [ObjectFromSiteID] As [SiteID],' +
                           ' [ObjectUgMaster_Type] AS [O.Nx.Type],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'

  if CharIndex('%', @ObjectFromSiteID)>0
     begin
      if @ObjectFromSiteID <> '%'
        -- 05.03.2011 eingefuegt um bei % unnoetigen select like zu vermeiden
      begin
        -- Suche über [ObjectFromSiteID] mit Like Operator @ObjectFromSiteID mit Like Operator erforderlich
        set @ExecuteSQLstr = @ExecuteSQLstr +
                             ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
       end
      end
      else
      begin
      if @ObjectFromSiteID <> ''
      -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
      begin
       set @ExecuteSQLstr = @ExecuteSQLstr +
                            ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
       end
      end

	  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [ObjectFromSiteID],[ObjectUgMaster_Type],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_308_List_of_all_ERRORS_GroupBy_NXType_forAllSites] -----------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_308_List_of_all_ERRORS_GroupBy_NXType_forAllSites")
Drop Procedure Report_308_List_of_all_ERRORS_GroupBy_NXType_forAllSites
GO

CREATE PROCEDURE Report_308_List_of_all_ERRORS_GroupBy_NXType_forAllSites
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 04.03.2014 13:00 K.Duszkiewicz//BSH
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_308_List_of_all_ERRORS_GroupBy_NXType_forAllSites'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [ObjectUgMaster_Type] AS [O.Nx.Type],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'


  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [ObjectUgMaster_Type],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_309_List_of_all_ERRORS_GroupBy_DSType] -----------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_309_List_of_all_ERRORS_GroupBy_DSType")
Drop Procedure Report_309_List_of_all_ERRORS_GroupBy_DSType
GO

CREATE PROCEDURE Report_309_List_of_all_ERRORS_GroupBy_DSType
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 04.03.2014 13:00 K.Duszkiewicz//BSH
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_309_List_of_all_ERRORS_GroupBy_DSType'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [ObjectFromSiteID] As [SiteID],' +
                           ' [ObjectType] AS [O.Type],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'

  if CharIndex('%', @ObjectFromSiteID)>0
     begin
      if @ObjectFromSiteID <> '%'
        -- 05.03.2011 eingefuegt um bei % unnoetigen select like zu vermeiden
      begin
        -- Suche über [ObjectFromSiteID] mit Like Operator @ObjectFromSiteID mit Like Operator erforderlich
        set @ExecuteSQLstr = @ExecuteSQLstr +
                             ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
       end
      end
      else
      begin
      if @ObjectFromSiteID <> ''
      -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
      begin
       set @ExecuteSQLstr = @ExecuteSQLstr +
                            ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
       end
      end

	  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [ObjectFromSiteID],[ObjectType],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_310_List_of_all_ERRORS_GroupBy_DSType_forAllSites] -----------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_310_List_of_all_ERRORS_GroupBy_DSType_forAllSites")
Drop Procedure Report_310_List_of_all_ERRORS_GroupBy_DSType_forAllSites
GO

CREATE PROCEDURE Report_310_List_of_all_ERRORS_GroupBy_DSType_forAllSites
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 04.03.2014 13:00 K.Duszkiewicz//BSH
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_310_List_of_all_ERRORS_GroupBy_DSType_forAllSites'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [ObjectType] AS [O.Type],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'


  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [ObjectType],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_311_List_of_all_ERRORS_GroupBy_Owner_NXType] -----------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_311_List_of_all_ERRORS_GroupBy_Owner_NXType")
Drop Procedure Report_311_List_of_all_ERRORS_GroupBy_Owner_NXType
GO

CREATE PROCEDURE Report_311_List_of_all_ERRORS_GroupBy_Owner_NXType
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 04.03.2014 13:00 K.Duszkiewicz//BSH
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_311_List_of_all_ERRORS_GroupBy_Owner_NXType'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [ObjectFromSiteID] As [SiteID],' +
                           ' [Object_SiteOwningTyp] AS [O.S.Or.Rep Object],' +
                           ' [ObjectUgMaster_Type] AS [O.Nx.Type],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'

  if CharIndex('%', @ObjectFromSiteID)>0
     begin
      if @ObjectFromSiteID <> '%'
        -- 05.03.2011 eingefuegt um bei % unnoetigen select like zu vermeiden
      begin
        -- Suche über [ObjectFromSiteID] mit Like Operator @ObjectFromSiteID mit Like Operator erforderlich
        set @ExecuteSQLstr = @ExecuteSQLstr +
                             ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
       end
      end
      else
      begin
      if @ObjectFromSiteID <> ''
      -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
      begin
       set @ExecuteSQLstr = @ExecuteSQLstr +
                            ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
       end
      end

	  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [ObjectFromSiteID],[Object_SiteOwningTyp],[ObjectUgMaster_Type],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_312_List_of_all_ERRORS_GroupBy_Owner_NXType_forAllSites] -----'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_312_List_of_all_ERRORS_GroupBy_Owner_NXType_forAllSites")
Drop Procedure Report_312_List_of_all_ERRORS_GroupBy_Owner_NXType_forAllSites
GO

CREATE PROCEDURE Report_312_List_of_all_ERRORS_GroupBy_Owner_NXType_forAllSites
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 04.03.2014 13:00 K.Duszkiewicz//BSH
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_312_List_of_all_ERRORS_GroupBy_Owner_NXType_forAllSites'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [Object_SiteOwningTyp] AS [O.S.Or.Rep Object],' +
                           ' [ObjectUgMaster_Type] AS [O.Nx.Type],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'


  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [Object_SiteOwningTyp],[ObjectUgMaster_Type],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_313_List_of_all_ERRORS_GroupBy_Owner_NXType_DSType] ----------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_313_List_of_all_ERRORS_GroupBy_Owner_NXType_DSType")
Drop Procedure Report_313_List_of_all_ERRORS_GroupBy_Owner_NXType_DSType
GO

CREATE PROCEDURE Report_313_List_of_all_ERRORS_GroupBy_Owner_NXType_DSType
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 04.03.2014 13:00 K.Duszkiewicz//BSH
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_313_List_of_all_ERRORS_GroupBy_Owner_NXType_DSType'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [ObjectFromSiteID] As [SiteID],' +
                           ' [Object_SiteOwningTyp] AS [O.S.Or.Rep Object],' +
                           ' [ObjectUgMaster_Type] AS [O.Nx.Type],' +
                           ' [ObjectType] AS [O.Type],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'

  if CharIndex('%', @ObjectFromSiteID)>0
     begin
      if @ObjectFromSiteID <> '%'
        -- 05.03.2011 eingefuegt um bei % unnoetigen select like zu vermeiden
      begin
        -- Suche über [ObjectFromSiteID] mit Like Operator @ObjectFromSiteID mit Like Operator erforderlich
        set @ExecuteSQLstr = @ExecuteSQLstr +
                             ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
       end
      end
      else
      begin
      if @ObjectFromSiteID <> ''
      -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
      begin
       set @ExecuteSQLstr = @ExecuteSQLstr +
                            ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
       end
      end

	  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [ObjectFromSiteID],[Object_SiteOwningTyp],[ObjectType],[ObjectUgMaster_Type],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

Print '-- [Report_314_List_of_all_ERRORS_GroupBy_Owner_NXType_DSType_forAllSites]'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_314_List_of_all_ERRORS_GroupBy_Owner_NXType_DSType_forAllSites")
Drop Procedure Report_314_List_of_all_ERRORS_GroupBy_Owner_NXType_DSType_forAllSites
GO

CREATE PROCEDURE Report_314_List_of_all_ERRORS_GroupBy_Owner_NXType_DSType_forAllSites
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 26.02.2014 11:04 J.Fes
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_314_List_of_all_ERRORS_GroupBy_Owner_NXType_DSType_forAllSites'
  DECLARE @ExecuteSQLstr nvarchar(max)

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

--                 S.Or.Rep Object
--                 O.UGMaster_Type  -- DS.ObjectUgMaster_Type   OK
--                 DataSet Type     -- DS.ObjectType            OK
--                 P.Result.Code    -- OK
--                 P.Result.Message -- OK
--                 R.Error.Count    -- OK


  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [Object_SiteOwningTyp] AS [O.S.Or.Rep Object],' +
                           ' [ObjectUgMaster_Type] AS [O.Nx.Type],' +
                           ' [ObjectType] AS [O.Type],' +
                           ' [ProcessResultCode] AS [P.Res.Code],' +
                           ' [ProcessResultMsg]  AS [P.Res.Msg]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1)' +
                           '        and ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'


  set @ExecuteSQLstr = @ExecuteSQLstr +
                           ' GROUP BY [Object_SiteOwningTyp],[ObjectType],[ObjectUgMaster_Type],ProcessResultCode,ProcessResultMsg'

  if @DebugPrintOn = 1
  begin

   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
GO

---------------- #New 24.06.2014  for BSH: Krzysztof --------------------------------------------------------------------
Print '-- [Report_401_MultiSite_all_DS_OK_in_OwingSite_Site_but_ERR_in_RemoteSite]'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_401_MultiSite_all_DS_OK_in_OwingSite_Site_but_ERR_in_RemoteSite")
Drop Procedure Report_401_MultiSite_all_DS_OK_in_OwingSite_Site_but_ERR_in_RemoteSite
GO

CREATE PROCEDURE Report_401_MultiSite_all_DS_OK_in_OwingSite_Site_but_ERR_in_RemoteSite
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 24.06.2014 J.Fes
BEGIN

  if @ObjectFromSiteID = '*' set @ObjectFromSiteID = '%'

SELECT
  SO.IrName AS [IR,id],
  SO.ObjectType AS [O.Type],
  SO.ObjectName AS [SO.Name],
  SO.ObjectFromSiteID AS [SO.SiteId],
  SO_RES.ProcessResultCode AS [SO.ResCode],
  RO.ObjectFromSiteID AS [RO.SiteId],
  RO.ObjectName AS [RO.Name],
  RO_RES.ProcessResultCode AS [Ro.ResCode],
  RO_RES.ProcessResultMsg AS [Ro.ResMsg]
FROM
  dbo.DS_with_Irs_View SO
  INNER JOIN dbo.JobControl_ObjectsProcessData_tbl SO_RES ON (SO.ID = SO_RES.Objects_tbl_ID)
  INNER JOIN dbo.DS_with_Irs_View RO ON (SO.ObjectPuid = RO.ObjectPuid)
  INNER JOIN dbo.JobControl_ObjectsProcessData_tbl RO_RES ON (RO_RES.Objects_tbl_ID = RO.ID)
WHERE
  SO_RES.ProcessResultCode = '0' AND
  SO_RES.Row_isLastRes = '1' AND
  SO.ObjectOwningSiteID = SO.ObjectFromSiteID AND
  SO.ObjectFromSiteID like @ObjectFromSiteID AND
  RO_RES.ProcessResultCode <> '0' AND
  RO_RES.Row_isLastRes = '1' AND
  RO.ObjectOwningSiteID <> RO.ObjectFromSiteID
ORDER BY
  SO.ObjectFromSiteID,
  RO.ObjectFromSiteID,
  SO.IrName,
  SO.ObjectName

END
GO
---------------- #New 04.03.2014  from BSH: Krzysztof --------------------------------------------------------------------


Print '-- [Report_902_ProcessStatistic_And_ProcessResults] ---------------------'
If Exists (select Name
           from sysobjects
           where Name = "Report_902_ProcessStatistic_And_ProcessResults" and type = "P")
Drop Procedure Report_902_ProcessStatistic_And_ProcessResults
GO
CREATE PROCEDURE Report_902_ProcessStatistic_And_ProcessResults
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(32),
        @GroupByAP_SP Bit,
		      @GroupByObjectType Bit
       )
AS
BEGIN
  -- new 25.04.2012 20:16
  -- #LUp: 31.10.2021/J.Fes  bo.Report_201_ProcStatus_Percent repaired
  -- Step Setup Base Layout do not with numbers do not use -
  EXEC dbo.ReportPageNamesInit 'Process Results',                    'Process Statistic and Lists of results'

  EXEC dbo.ReportPageNamesAdd  'Process_Statistic',                  'Statistic:[Summary of all processed datasets]'
  EXEC dbo.ReportPageNamesAdd  'List_Of_All_Happend_Errors',         'Result:[List of all happened errors]'
  EXEC dbo.ReportPageNamesAdd  'List_Of_All_Datasets_With_Errors',   'Result:[List of all datasets with errors]'
  EXEC dbo.ReportPageNamesAdd  'List_Of_All_Datasets_With_Warnings', 'Result:[List of all datasets with warnings]'
  EXEC dbo.ReportPageNamesGet

  -- EXEC dbo.Report_204_ProcStatus_by_DataSets_MultiSiteYes_Count @ObjectFromSiteID, @ProcStateCol, 'DS'
  EXEC dbo.Report_201_ProcStatus_Percent                        @ObjectFromSiteID, @ProcStateCol, 'DS', @GroupByAP_SP, @GroupByObjectType
  EXEC dbo.Report_303_List_of_all_happend_ERRORS                @ObjectFromSiteID, @ProcStateCol, 'DS'
  EXEC dbo.Report_301_List_of_all_Datasets_With_ERRORS          @ObjectFromSiteID, @ProcStateCol, 'DS'
  EXEC dbo.Report_302_List_of_all_Datasets_With_Warnings        @ObjectFromSiteID, @ProcStateCol, 'DS'

END
GO


Print '-- [Report_801_ProcessTime_perSite] -------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Report_801_ProcessTime_perSite" and type = "P")
Drop Procedure Report_801_ProcessTime_perSite
GO

CREATE PROCEDURE Report_801_ProcessTime_perSite
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )

AS
-- new 24.04.2012 15:21 J.Fes
BEGIN

  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_801_ProcessTime_perSite'

  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @TempTblName   nvarchar(32)
  set @TempTblName = 'tempdb..##temp_tbl_' + @MyProcName
  -- set @TempTblName = 'zztmp_Report_801_ProcessTime_perSite_inWork_tbl'
  IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

  -- exec Report_101_Objects_Datasets_Sum @ObjectFromSiteID, @ProcStateCol, @ObjectClass

  DECLARE @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

  set @ExecuteSQLstr =
      'SELECT' +
      ' ' + @AndProcessData_UDF + '.ObjectFromSiteID,' +
      ' ' + @AndProcessData_UDF + '.ProcessPacketName,' +
      ' ' + @AndProcessData_UDF + '.ObjectUgMaster_Type    AS ObjectUgMaster_Type,' +
      '  COUNT(' + @AndProcessData_UDF + '.ObjectFromSiteID)  AS Datasets_n,' +
      '  min(' + @AndProcessData_UDF + '.ProcessTimeStart)    As FirstJobStart,' +
      '  max(' + @AndProcessData_UDF + '.ProcessTimeEnd)      As LastJobFinish,' +
      '  DateDiff(ss, ' + @AndProcessData_UDF + '.ProcessTimeStart, '
                        + @AndProcessData_UDF + '.ProcessTimeEnd) AS DurationForPaket_sec' +
      ' INTO' +
      '  ' + @TempTblName +
      ' FROM' +
      ' ' + @AndProcessData_UDF + '(''' + @ProcStateCol + ''')' +
      ' WHERE' +
      ' ' + @AndProcessData_UDF + '.Row_isLastRes = 1 and' +
      ' ' + @AndProcessData_UDF + '.ProcessTimeStart IS NOT NULL and' +
      ' ' + @AndProcessData_UDF + '.ProcessTimeEnd > ' + @AndProcessData_UDF + '.ProcessTimeStart and' +
      ' ' + @AndProcessData_UDF + '.ObjectFromSiteID like ''' +   @ObjectFromSiteID + '''' +
      '  and DateDiff(ss, ' + @AndProcessData_UDF + '.ProcessTimeStart, ' + @AndProcessData_UDF + '.ProcessTimeEnd) > 10' +
      ' GROUP BY' +
      ' ' + @AndProcessData_UDF + '.ObjectFromSiteID,' +
      ' ' + @AndProcessData_UDF + '.ProcessPacketName,' +
      ' ' + @AndProcessData_UDF + '.ObjectUgMaster_Type,' +
      '  DateDiff(ss, ' + @AndProcessData_UDF + '.ProcessTimeStart,' +
      '               ' + @AndProcessData_UDF + '.ProcessTimeEnd)'

  ------------ ## Debug -----------------------
  if @DebugPrintOn = 1
  begin
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
   set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,   @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,       @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,      @ExecuteSQLstr
  end

  execute sp_executesql @ExecuteSQLstr

     -- Abfrage 2 Temp Tabelle zusammenfassen !
    set @ExecuteSQLstr =
    'Select ' +
    '  ObjectFromSiteID As [Site],' +
    '  ObjectUgMaster_Type As [O.UgMaster Type],' +
    '  sum(Datasets_n) As [total (Objects)],' +
    '  sum(cast(DurationForPaket_sec As float))/3600 As ' + '''P.Time total [h]''' + ',' +
    '  min(FirstJobStart) As ' + '''First job start Date/Time''' + ',' +
    '  max(LastJobFinish) As ' + '''Last job finish Date/Time''' + ',' +
    '  (cast(DateDiff(ss, min(FirstJobStart), max(LastJobFinish)) As float) / 3600 ) As ' +
                                 '''Timeframe between First Job start and Last Job finish [h]''' +
    ' FROM ' + @TempTblName +
    ' GROUP BY' +
    '  ObjectFromSiteID, ObjectUgMaster_Type' +
    ' ORDER BY' +
    '   ObjectFromSiteID'

  if @DebugPrintOn = 1
  begin
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,      @ExecuteSQLstr
  end

  execute sp_executesql @ExecuteSQLstr
END
GO


Print '-- [Rrt_810_ProcessPerHour] ---------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Rrt_810_ProcessPerHour" and type = "P")
Drop Procedure Rrt_810_ProcessPerHour
GO
CREATE PROCEDURE Rrt_810_ProcessPerHour
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10),
        @Row_isLastRes bit,
        -- #New 28.04.2021 A.Stein
        @ProcessTimeStart DATETIME,
        @ProcessTimeEnd DATETIME,
        @GroupByAP_SP bit,
        @GroupByObjectType bit
       )

AS
-- #New 01.08.2018 J.Fes
-- #Doc: Base Report for Report_810_ProcessPerHour_LastResult and Report_811_ProcessPerHour_AllResult
-- #New: 31.10.2021 J.Fes add @GroupByObjectType
BEGIN


   DECLARE @DebugPrintOn bit
   set @DebugPrintOn = 0
   DECLARE @MyProcName nvarchar(32)
   set @MyProcName='Rrt_810_ProcessPerHour'

   DECLARE @minDate DATETIME = '1753-01-01T00:00:00.000'
   DECLARE @ExecuteSQLstr nvarchar(max)
   DECLARE @TempTblName   nvarchar(32)
   set @TempTblName = 'zztmp_Report_810_ProcessPerHour_LastResult_tbl'
   IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

   DECLARE @AndProcessData_UDF nvarchar(32)

   if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
   if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
   if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

 -- set @AndProcessData_UDF='DS_AndProcessDataForCount_UDF'


   set @ExecuteSQLstr =
       'SELECT' +
       ' ' + @AndProcessData_UDF + '.ObjectFromSiteID    AS [ObjectFromSiteID],'

  -- #New 28.04.2021 A.Stein Include Optional GroupBy AP/SP
   if @GroupByAP_SP = 1
   begin
    set @ExecuteSQLstr =  @ExecuteSQLstr +
         ' ' + @AndProcessData_UDF + '.ObjectUgMaster_Type AS [ObjectUgMaster_Type],'
   end

   -- #New: 31.10.2021 J.Fes add @GroupByObjectType
   if @GroupByObjectType = 1
 	 begin
 	 set @ExecuteSQLstr = @ExecuteSQLstr +
        ' ' + @AndProcessData_UDF + '.ObjectType AS [Dataset Type],'
 		     -- ' [ObjectType]             AS [Dataset Type],'

 	 end


   set @ExecuteSQLstr =  @ExecuteSQLstr +
      ' min(' + @AndProcessData_UDF + '.ProcessTimeEnd) AS [PTimeStart],' +
      ' max(' + @AndProcessData_UDF + '.ProcessTimeEnd) AS [PTimeEnd],' +
      ' cast(max(' + @AndProcessData_UDF + '.ProcessTimeEnd) AS Date) AS [PDateEnd],' +
      ' cast(DateDiff(ss,min(' + @AndProcessData_UDF + '.ProcessTimeEnd), max(' + @AndProcessData_UDF + '.ProcessTimeEnd)) As integer) AS [PDur_sec],' +
      ' COUNT(' + @AndProcessData_UDF + '.ObjectFromSiteID) AS [OProcessedN_SUM_n],' +
     -- ' Sum(CASE WHEN('+ @AndProcessData_UDF + '.ProcessState like ''%OK'')  THEN 1 ELSE 0 END) AS [OProcessedN_OK_n],' +
     -- ' Sum(CASE WHEN('+ @AndProcessData_UDF + '.ProcessState like ''%ERR'') THEN 1 ELSE 0 END) AS [OProcessedN_ERR_n]' +
      ' Sum(CASE WHEN('+ @AndProcessData_UDF + '.ObjProcStateValue like ''D%OK'')  THEN 1 ELSE 0 END) AS [OProcessedN_OK_n],' +
      ' Sum(CASE WHEN('+ @AndProcessData_UDF + '.ObjProcStateValue like ''D%WRN'') THEN 1 ELSE 0 END) AS [OProcessedN_WRN_n],' +
      ' Sum(CASE WHEN('+ @AndProcessData_UDF + '.ObjProcStateValue like ''D%ERR'') THEN 1 ELSE 0 END) AS [OProcessedN_ERR_n]' +
      ' INTO' +
      '  ' + @TempTblName +
      ' FROM' +
      ' ' + @AndProcessData_UDF + '(''' + @ProcStateCol + ''')' +
      ' WHERE'

      -- #New 28.04.2021 A.Stein  Incldue ProcessTimeStart and ProcessTimeEnd
	  if @ProcessTimeStart > @minDate -- '1753-01-01T00:00:00.000' --'1/2/1753'
	  begin
      set @ExecuteSQLstr =  @ExecuteSQLstr +
           ' ' + @AndProcessData_UDF + '.ProcessTimeStart > ''' + convert(nvarchar,@ProcessTimeStart,127) + ''' AND'
	  end

	  if @ProcessTimeEnd > @minDate
	  begin
      set @ExecuteSQLstr =  @ExecuteSQLstr +
          ' '  + @AndProcessData_UDF + '.ProcessTimeEnd < ''' + convert(nvarchar,@ProcessTimeEnd,127) + ''' AND'
   end


   if @Row_isLastRes = 1
   begin
     set @ExecuteSQLstr =  @ExecuteSQLstr +
         ' ' + @AndProcessData_UDF + '.Row_isLastRes = 1 AND'
   end

   set @ExecuteSQLstr =  @ExecuteSQLstr +
      ' ' + @AndProcessData_UDF + '.ObjProcStateValue like ''D%'' AND' +
      ' ' + @AndProcessData_UDF + '.ProcessTimeStart IS NOT NULL AND' +
      ' ' + @AndProcessData_UDF + '.ProcessTimeEnd > ' + @AndProcessData_UDF + '.ProcessTimeStart AND' +
      ' ' + @AndProcessData_UDF + '.ObjectFromSiteID like ''' +   @ObjectFromSiteID + '''' +
      '  and DateDiff(ms, ' + @AndProcessData_UDF + '.ProcessTimeStart, ' + @AndProcessData_UDF + '.ProcessTimeEnd) > 10' +
      ' GROUP BY' +
      ' ' + @AndProcessData_UDF + '.ObjectFromSiteID,' 

	  -- #New 28.04.2021 A.Stein Include Optional GroupBy AP/SP
	  if @GroupByAP_SP = 1
   begin
     set @ExecuteSQLstr =  @ExecuteSQLstr +
         ' ' + @AndProcessData_UDF + '.ObjectUgMaster_Type,'
   end

   -- #New: 31.10.2021 J.Fes add @GroupByObjectType
   if @GroupByObjectType = 1
	  begin
    	set @ExecuteSQLstr = @ExecuteSQLstr +
         ' ' + @AndProcessData_UDF + '.ObjectType,'
     --     + ',' + ' [ObjectType]'
	  end

   set @ExecuteSQLstr =  @ExecuteSQLstr +
      '  year(' + @AndProcessData_UDF + '.ProcessTimeEnd),' +
      '  month(' + @AndProcessData_UDF + '.ProcessTimeEnd),'  +
      '  day(' + @AndProcessData_UDF + '.ProcessTimeEnd),' +
      '  datepart(hh,' + @AndProcessData_UDF + '.ProcessTimeEnd)' +
      ' Order BY [PTimeEnd]'


  ------------ ## Debug -----------------------
   if @DebugPrintOn = 1
   begin
    set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"     "," ")
    set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"    "," ")
    set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"   "," ")
    set @ExecuteSQLstr = Replace(@ExecuteSQLstr,"  "," ")

    -- Clear Debug Table
    EXECUTE zzzzzDebugTbl_Empty
    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,   @ObjectFromSiteID
    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,       @ProcStateCol
    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,      @ExecuteSQLstr
   end

   execute sp_executesql @ExecuteSQLstr

   set @ExecuteSQLstr =
       'SELECT' +
       ' * from ' + @TempTblName
   execute sp_executesql @ExecuteSQLstr

   -- Abfrage 2 Temp Tabelle zusammenfassen !
   set @ExecuteSQLstr =
    'Select ' +
    ' ObjectFromSiteID                  As [Site],'

   -- #New 28.04.2021 A.Stein Include Optional GroupBy AP/SP
   if @GroupByAP_SP = 1
   begin
   set @ExecuteSQLstr =  @ExecuteSQLstr +
       ' ObjectUgMaster_Type               As [O.UgMaster Type],'
   end

   -- #New: 31.10.2021 J.Fes add @GroupByObjectType
   if @GroupByObjectType = 1
 	 begin
 	  set @ExecuteSQLstr = @ExecuteSQLstr +
   	  	'[DataSet Type],'
 	 end
   -- #LUp:02.03.2022/J.Fes add new result ' ([OProcessedN_OK_n] + [OProcessedN_WRN_n]) As [O.Done.OK+WRN],'
   -- #LUp:11.08.2022/J.Fes add new result ' (PDur_sec/OProcessedN_SUM_n)      As [Dura/O.Done (sec)]' +
   -- 22.07.2024 change convert(nvarchar, [PTimeEND],104) AS [P.Date/Day],' + to: cast([PTimeEND] AS Date)
   -- ' cast([PTimeEND] AS Date)          AS [P.Date/Day],' +
   set @ExecuteSQLstr =  @ExecuteSQLstr +
    ' PTimeStart                        AS [P.Start Date/Time],' +
    ' PTimeEND                          AS [P.End Date/Time],' +
    ' PDateEND                          AS [P.Date/Day],' +
    ' PDur_sec                          AS [P.Dur.(sec)],' +
    ' datename(month,  [PTimeEND])      AS [P.Month],' +
    ' datepart(week,   [PTimeEND])      AS [P.Week of Year],' +
    ' (cast(datepart(w,[PTimeEND]) AS nvarchar(1)) +' + '''-''' + '+ datename(weekday,[PTimeEND]))' +
                                      ' AS [P.Weekday],' +
        ' datepart(hh,[PTimeEND])           AS [P.Houre],' +
    ' OProcessedN_SUM_n                 AS [O.Done (n)],' +
    ' OProcessedN_OK_n                  AS [O.Done.OK],' +
    ' OProcessedN_WRN_n                 AS [O.Done.WRN],' +
    ' ([OProcessedN_OK_n] + [OProcessedN_WRN_n])' +
                                      ' AS [O.Done.OK+WRN (n)],' +
    ' OProcessedN_ERR_n                 AS [O.Done.ERR], ' +
    ' Cast(((PDur_sec*1.00)/OProcessedN_SUM_n) AS DECIMAL(16,2)) As [Dura (sec/per One.Obj)] ' +
    'FROM ' + @TempTblName +
    ' WHERE ' +
    '  PDur_sec > 0' +
    ' ORDER BY' +
    '  PTimeEND'
    -- for ''P.Dur./O.Done''

   if @DebugPrintOn = 1
   begin
    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName, '@ExecuteSQLstr2', @ExecuteSQLstr
   end

   execute sp_executesql @ExecuteSQLstr

END
GO


Print '-- [Report_810_ProcessPerHour_LastResult] -------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Report_810_ProcessPerHour_LastResult" and type = "P")
Drop Procedure Report_810_ProcessPerHour_LastResult
GO
CREATE PROCEDURE Report_810_ProcessPerHour_LastResult
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10),
        @ProcessTimeStart DATETIME,
        @ProcessTimeEnd DATETIME,
        @GroupByAP_SP bit,
        @GroupByObjectType bit
       )

AS
-- #New: 24.04.2012 15:21 J.Fes
-- #LUp: 04.02.2018 new: .ObjProcStateValue like ''D%ERR''
-- #LUp: 28.03.2018 S.Gueth update name from '810_ProcessPerHoure' to 'Report_810_ProcessPerHour_LastResult'
-- #LUp: 01.08.2018 J.Fes uses now Rrt_810_ProcessPerHour
-- #New: 31.10.2021 J.Fes add @GroupByObjectType
BEGIN
 -- #Doc:
 Execute Rrt_810_ProcessPerHour @ObjectFromSiteID, @ProcStateCol,  @ObjectClass , 1, @ProcessTimeStart, @ProcessTimeEnd, @GroupByAP_SP, @GroupByObjectType
END
GO

-- new 28.03.2018 S.Gueth
Print '-- [Report_811_ProcessPerHour_AllResults] -------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Report_811_ProcessPerHour_AllResults" and type = "P")
Drop Procedure Report_811_ProcessPerHour_AllResults
GO
CREATE PROCEDURE Report_811_ProcessPerHour_AllResults
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10),
        -- #New 28.04.2021 A.Stein
        @ProcessTimeStart DATETIME,
        @ProcessTimeEnd DATETIME,
        @GroupByAP_SP bit,
        @GroupByObjectType bit
       )

AS
-- new 28.03.2018 S.Gueth
-- #LUP: 01.08.2018 J.Fes uses now Rrt_810_ProcessPerHour
-- #New: 31.10.2021 J.Fes add @GroupByObjectType
BEGIN
  Execute Rrt_810_ProcessPerHour @ObjectFromSiteID, @ProcStateCol,  @ObjectClass , 0, @ProcessTimeStart, @ProcessTimeEnd, @GroupByAP_SP, @GroupByObjectType

END
GO




Print '-- [LatestProcessResultsUntilDate] --------------------------------------'
If Exists (select Name from sysobjects
        where Name = 'LatestProcessResultsUntilDate'
        and type = 'TF') Drop Function LatestProcessResultsUntilDate
GO


-- =============================================
-- Author:		Alexander Becker
-- Create date: 31.10.2019
-- Description:	Returns the latest result of each object until a specified date
-- =============================================
Create FUNCTION dbo.LatestProcessResultsUntilDate
(	
  @ProcessTimeEnd DATETIME,
  @ProcessProcStateCol NVARCHAR(32),
  @ObjectClass NVARCHAR(10) = NULL,
  @ObjectGroupGuidList NVARCHAR(MAX) = NULL -- sample: 'CA644377-F747-4F67-B78D-01C2E8623D0E;825B6C1D-D516-4CAB-8F1E-0444D1D81556;...'
)
RETURNS
@res TABLE 
(
	-- Add the column definitions for the TABLE variable here
	Objects_tbl_ID UNIQUEIDENTIFIER, 
	ProcessTimeEnd DATETIME,
	ProcessState NVARCHAR(34)
)
AS
BEGIN
	-- needed Index on dbo.JobControl_ObjectsProcessData_tbl: ProcessStateCol, ProcessTimeEnd, Objects_tbl_ID

	-- default ObjectClass
	SET @objectClass = ISNULL(@ObjectClass, 'I')

	-- split group ids
	DECLARE @groupIds TABLE (groupId UNIQUEIDENTIFIER)
	DECLARE @anyGroupIds BIT = 0

	INSERT INTO @groupIds(groupId)
	  SELECT cast(Value as UNIQUEIDENTIFIER)
	  FROM dbo.SplitStringToTable_UDF(@ObjectGroupGuidList, ';')
	  WHERE Value LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'

	IF EXISTS (SELECT 1 FROM @groupIds)
	  set @anyGroupIds = 1

	INSERT INTO @res (Objects_tbl_ID, ProcessTimeEnd, ProcessState)
	SELECT OPD.Objects_tbl_ID, OPD.ProcessTimeEnd, OPD.ProcessState
	FROM dbo.JobControl_ObjectsProcessData_tbl OPD
	WHERE 
      (
	     -- entweder alle Objekte, oder die einer Gruppe
		 @anyGroupIds = 0 
		 OR
		 EXISTS (SELECT 1 FROM dbo.Objects_ObjectGroups_Rel_tbl WHERE @anyGroupIds=1 AND ObjectGroupId IN (SELECT groupId FROM @groupIds) AND ObjectId=OPD.Objects_tbl_ID)
	  )
	  
	  AND

	  -- ObjectClass
	  EXISTS (SELECT 1 FROM dbo.Objects_tbl OBJ WHERE OBJ.Id=OPD.Objects_tbl_ID AND OBJ.ObjectClass=@ObjectClass)
	  
	  AND

	  -- ProcStateCol
	  OPD.ProcessProcStateCol = @ProcessProcStateCol

	  AND
	  
	  -- Zeitbereich einschränken
	  OPD.ProcessTimeEnd <= @ProcessTimeEnd
	  
	  AND

	  -- nur zeitlich letztes gültige Ergebnis
	  OPD.ProcessTimeEnd
		= (
		SELECT MAX(OPD2.ProcessTimeEnd)
		FROM dbo.JobControl_ObjectsProcessData_tbl OPD2
		WHERE OPD2.Objects_tbl_ID = OPD.Objects_tbl_ID 
			AND OPD2.ProcessTimeEnd <= @ProcessTimeEnd AND OPD2.ProcessProcStateCol=@ProcessProcStateCol
			AND OPD2.ProcessTimeEnd IS NOT NULL
		)

	RETURN

END    
GO

Print '-- [LatestProcessResultsUntilDateGroupedByState] ------------------------'
If Exists (select Name from sysobjects
        where Name = 'LatestProcessResultsUntilDateGroupedByState'
        and type ='IF') Drop Function LatestProcessResultsUntilDateGroupedByState
GO
       
-- =============================================
-- Author:		Alexander Becker
-- Create date: 31.10.2019
-- Description:	Returns the counts of each latest result state over all objects until a specified date
-- =============================================
CREATE FUNCTION dbo.LatestProcessResultsUntilDateGroupedByState 
(	
  @ProcessTimeEnd DATETIME,
  @ProcessProcStateCol NVARCHAR(32),
  @ObjectClass NVARCHAR(10) = NULL,
  @ObjectGroupGuidList NVARCHAR(MAX) = NULL -- sample: 'CA644377-F747-4F67-B78D-01C2E8623D0E;825B6C1D-D516-4CAB-8F1E-0444D1D81556;...'
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT 
		SUM(CASE WHEN Q.CleanedStatus = N'D' THEN 1 ELSE 0 END) AS CountD,
		SUM(CASE WHEN Q.CleanedStatus = N'D.OK' THEN 1 ELSE 0 END) AS CountOK,
		SUM(CASE WHEN Q.CleanedStatus = N'D.WRN' THEN 1 ELSE 0 END) AS CountWRN,
		SUM(CASE WHEN Q.CleanedStatus = N'D.ERR' THEN 1 ELSE 0 END) AS CountERR,
		SUM(CASE WHEN Q.CleanedStatus = N'X' THEN 1 ELSE 0 END) AS CountX,
		SUM(CASE WHEN Q.CleanedStatus = N'T' THEN 1 ELSE 0 END) AS CountT,
		SUM(CASE WHEN Q.CleanedStatus = N'R' THEN 1 ELSE 0 END) AS CountR,
		SUM(CASE WHEN Q.CleanedStatus = N'P' THEN 1 ELSE 0 END) AS CountP,
		SUM(CASE WHEN Q.CleanedStatus = N'UNKNOWN' THEN 1 ELSE 0 END) AS CountUNKN,
		SUM(1) AS CountAllProcessed

	FROM (
		SELECT 
		CASE WHEN ProcessState LIKE N'D%.OK' THEN 'D.OK' ELSE
		CASE WHEN ProcessState LIKE N'D%.WRN' THEN 'D.WRN' ELSE
		CASE WHEN ProcessState LIKE N'D%.ERR' THEN 'D.ERR' ELSE
		CASE WHEN ProcessState LIKE N'[XTRPD]%' THEN SUBSTRING(ProcessState,1,1) ELSE
		     'UNKNOWN' END END END END AS CleanedStatus
		
		FROM dbo.LatestProcessResultsUntilDate(@ProcessTimeEnd, @ProcessProcStateCol, @ObjectClass, @ObjectGroupGuidList)
	) Q
)
GO

   

-- #New 01.11.2019 09:18:06
-- =============================================
-- Author:		Alexander Becker
-- Create date: 31.10.2019
-- Description:	Returns an intervalled report of the counts of each latest result state over all objects until a specified date
-- =============================================


Print '-- [ObjectsProcessDataCummulatedResultCounts] ---------------------------'
If Exists (select Name from sysobjects where type = 'P' and
           Name = 'Report_903_ObjectsProcessDataCummulatedResultCounts')
Drop Procedure Report_903_ObjectsProcessDataCummulatedResultCounts
GO

CREATE PROCEDURE dbo.Report_903_ObjectsProcessDataCummulatedResultCounts
	-- Add the parameters for the stored procedure here
	@ProcessTimeEnd DATETIME,
	@ProcessProcStateCol NVARCHAR(32),
	@interval NVARCHAR(100) = 'daily', -- daily, weekly, monthly, querterly, yearly
	@objectClass NVARCHAR(10),
 @ObjectGroupGuidList NVARCHAR(MAX) = NULL -- sample: 'CA644377-F747-4F67-B78D-01C2E8623D0E;825B6C1D-D516-4CAB-8F1E-0444D1D81556;...'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- split group ids
	DECLARE @groupIds TABLE (groupId UNIQUEIDENTIFIER)
	DECLARE @anyGroupIds BIT = 0

	INSERT INTO @groupIds(groupId)
	  SELECT cast(Value as UNIQUEIDENTIFIER)
	  FROM dbo.SplitStringToTable_UDF(@ObjectGroupGuidList, ';')
	  WHERE Value LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'

	IF EXISTS (SELECT 1 FROM @groupIds)
	  set @anyGroupIds = 1

	-- get first date from process results
	DECLARE @firstResultDate DATETIME
	SELECT @firstResultDate=MIN(OPD.ProcessTimeEnd)
	FROM dbo.JobControl_ObjectsProcessData_tbl OPD
	WHERE OPD.ProcessTimeEnd IS NOT NULL AND
	      OPD.ProcessProcStateCol=@ProcessProcStateCol AND
		  EXISTS (SELECT 1 FROM dbo.Objects_tbl OBJ WHERE OBJ.Id=OPD.Objects_tbl_ID AND OBJ.ObjectClass=@ObjectClass) AND
		  (
			 -- entweder alle Objekte, oder die einer Gruppe
			 @anyGroupIds = 0
			 OR
			 EXISTS (SELECT 1 FROM dbo.Objects_ObjectGroups_Rel_tbl WHERE @anyGroupIds=1 AND ObjectGroupId IN (SELECT groupId from @groupIds) AND ObjectId=OPD.Objects_tbl_ID)
		  )
	DECLARE @intervalDate DATETIME
	DECLARE @intervalLabel NVARCHAR(100)

	-- compute object count
	DECLARE @objectCount INT
	SELECT @objectCount=COUNT(1)
	FROM dbo.Objects_tbl OBJ
	WHERE OBJ.ObjectClass=@ObjectClass AND
		  (
			 -- entweder alle Objekte, oder die einer Gruppe
			 @anyGroupIds = 0
			 OR
			 EXISTS (SELECT 1 FROM dbo.Objects_ObjectGroups_Rel_tbl WHERE @anyGroupIds=1 AND ObjectGroupId IN (SELECT groupId from @groupIds) AND ObjectId=OBJ.Id)
		  )

	-- result table
	DECLARE @res TABLE (intervalDate DATETIME, intervalLabel NVARCHAR(100), 
	                    AllObjectsCount INT, NotProcessedCount INT, 
						CountD INT, CountOK INT, CountWRN INT, CountERR INT, CountX INT, CountT INT, CountR INT, CountP INT, CountUNKN INT)

	-- computer start date
	IF @interval = 'daily'
		SET @intervalDate = @firstResultDate;
	IF @interval = 'weekly'
		SET @intervalDate = DATEADD(week, DATEDIFF(week, 0, @firstResultDate), 0);
	IF @interval = 'monthly'
		SET @intervalDate = DATEADD(month, DATEDIFF(month, 0, @firstResultDate), 0)
	IF @interval = 'quarterly'
		SET @intervalDate = DATEADD(quarter, DATEDIFF(quarter, 0, @firstResultDate), 0)
	IF @interval = 'yearly'
		SET @intervalDate = DATEADD(year, DATEDIFF(year, 0, @firstResultDate), 0)

	-- täglich
	WHILE @intervalDate <= @ProcessTimeEnd
	BEGIN
		
		-- format interval label
		IF @interval = 'daily'
			SET @intervalLabel = FORMAT(@intervalDate, 'yyyy-MM-dd')
		IF @interval = 'weekly'
			SET @intervalLabel = FORMAT(@intervalDate, 'yyyy') + '-CW' + RIGHT('00' + CAST(DATEPART(week, @intervalDate) AS VARCHAR(10)),2)
		IF @interval = 'monthly'
			SET @intervalLabel = FORMAT(@intervalDate, 'yyyy-MM-')
		IF @interval = 'quarterly'
			SET @intervalLabel = FORMAT(@intervalDate, 'yyyy-MM')
		IF @interval = 'yearly'
			SET @intervalLabel = FORMAT(@intervalDate, 'yyyy')

		-- add interval data
		INSERT INTO @res (intervalDate, intervalLabel, AllObjectsCount, NotProcessedCount, CountD, CountOK, CountWRN, CountERR, CountX, CountT, CountR, CountP, CountUNKN)
		SELECT @intervalDate, @intervalLabel, @objectCount, @objectCount-CountAllProcessed, CountD, CountOK, CountWRN, CountERR, CountX, CountT, CountR, CountP, CountUNKN
		FROM dbo.LatestProcessResultsUntilDateGroupedByState(@intervalDate, @ProcessProcStateCol, @ObjectClass, @ObjectGroupGuidList)

		-- increase interval date
		IF @interval = 'daily'
			SET @intervalDate = DATEADD(day, 1, @intervalDate)
		IF @interval = 'weekly'
			SET @intervalDate = DATEADD(week, 1, @intervalDate)
		IF @interval = 'monthly'
			SET @intervalDate = DATEADD(month, 1, @intervalDate)
		IF @interval = 'quarterly'
			SET @intervalDate = DATEADD(quarter, 1, @intervalDate)
		IF @interval = 'yearly'
			SET @intervalDate = DATEADD(year, 1, @intervalDate)
	END
	
	SELECT * FROM @res
END
GO

Print ''
Print '-------------------------------------------------------------------------'
Print ' R E P O R T S imported to #DB_N#'
Print '-------------------------------------------------------------------------'
GO

