Print '--------------------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [REPORTS]'
Print ' Creation      : 22.03.2006/J.Fes'
Print ' Last.Update   : 25.12.2021/J.Fes'

use [#DB_N#]

Print '--------------------------------------------------------------------------------------'
Print '--------------------- R E P O R T S 02 Base ------------------------------------------'
Print '--------------------------------------------------------------------------------------'


Print '-- [DS_AndProcessDataForCount_UDF] ---------------------------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "DS_AndProcessDataForCount_UDF" and type = "IF")
DROP FUNCTION DS_AndProcessDataForCount_UDF
GO

CREATE FUNCTION DS_AndProcessDataForCount_UDF
               (@ProcessProcStateCol nvarchar(32), @ObjectClass nvarchar(2))
-- UDF Optimiert für Statistik Abfragen
-- 07.12.2011 with (nolock) eingefuegt
-- 28.08.2013 #Doc: @ProcessProcStateCol_ObjectClass ist zusammen gesetzt aus
--                  @ProcessProcStateCol = Zeichen 1-14
--                  @ObjectClass         = Zeichen 16-18
-- 07.11.2014/J.Fes #Doc: add ProcStateCol11..20
-- 03.03.2016/J.Fes #Doc: Version without Process Results Performance
-- 01.08.2018/J.Fes #LUp: Update ObjectUgMaster_Type add SiteOwningType
-- #LUp: 25.07.2023 J.Fes add ProcStateCol21 .. ProcStateCol40
RETURNS TABLE AS

RETURN (SELECT
        ObjectFromSiteID,
        ObjectOwningSiteID,
        ObjectClass,
        ObjectUgMaster_Type = CASE [ObjectUgMaster_Type]
           WHEN 'SP' THEN '1-SP (Single Parts)'
           WHEN 'AP' THEN '2-AP (Assembly)'
                     ELSE '3-NP (No CAD.Obj)'
          END,
        ObjectType,
        SiteOwningType = CASE [ObjectFromSiteID]
           WHEN [ObjectOwningSiteID] THEN '1-SO (SiteObj)'
                                     ELSE '2-RO (ReplObj)'
          END,
        ProcStateValue = CASE @ProcessProcStateCol
                         WHEN 'ProcStateCol01' THEN [ProcStateCol01]
                         WHEN 'ProcStateCol02' THEN [ProcStateCol02]
                         WHEN 'ProcStateCol03' THEN [ProcStateCol03]
                         WHEN 'ProcStateCol04' THEN [ProcStateCol04]
                         WHEN 'ProcStateCol05' THEN [ProcStateCol05]
                         WHEN 'ProcStateCol06' THEN [ProcStateCol06]
                         WHEN 'ProcStateCol07' THEN [ProcStateCol07]
                         WHEN 'ProcStateCol08' THEN [ProcStateCol08]
                         WHEN 'ProcStateCol09' THEN [ProcStateCol09]
                         WHEN 'ProcStateCol10' THEN [ProcStateCol10]
                         WHEN 'ProcStateCol11' THEN [ProcStateCol11]
                         WHEN 'ProcStateCol12' THEN [ProcStateCol12]
                         WHEN 'ProcStateCol13' THEN [ProcStateCol13]
                         WHEN 'ProcStateCol14' THEN [ProcStateCol14]
                         WHEN 'ProcStateCol15' THEN [ProcStateCol15]
                         WHEN 'ProcStateCol16' THEN [ProcStateCol16]
                         WHEN 'ProcStateCol17' THEN [ProcStateCol17]
                         WHEN 'ProcStateCol18' THEN [ProcStateCol18]
                         WHEN 'ProcStateCol19' THEN [ProcStateCol19]
                         WHEN 'ProcStateCol20' THEN [ProcStateCol20]
                         WHEN 'ProcStateCol21' THEN [ProcStateCol21]
                         WHEN 'ProcStateCol22' THEN [ProcStateCol22]
                         WHEN 'ProcStateCol23' THEN [ProcStateCol23]
                         WHEN 'ProcStateCol24' THEN [ProcStateCol24]
                         WHEN 'ProcStateCol25' THEN [ProcStateCol25]
                         WHEN 'ProcStateCol26' THEN [ProcStateCol26]
                         WHEN 'ProcStateCol27' THEN [ProcStateCol27]
                         WHEN 'ProcStateCol28' THEN [ProcStateCol28]
                         WHEN 'ProcStateCol29' THEN [ProcStateCol29]
                         WHEN 'ProcStateCol30' THEN [ProcStateCol30]
                         WHEN 'ProcStateCol31' THEN [ProcStateCol31]
                         WHEN 'ProcStateCol32' THEN [ProcStateCol32]
                         WHEN 'ProcStateCol33' THEN [ProcStateCol33]
                         WHEN 'ProcStateCol34' THEN [ProcStateCol34]
                         WHEN 'ProcStateCol35' THEN [ProcStateCol35]
                         WHEN 'ProcStateCol36' THEN [ProcStateCol36]
                         WHEN 'ProcStateCol37' THEN [ProcStateCol37]
                         WHEN 'ProcStateCol38' THEN [ProcStateCol38]
                         WHEN 'ProcStateCol39' THEN [ProcStateCol39]
                         WHEN 'ProcStateCol40' THEN [ProcStateCol40]
                         WHEN 'ProcStateColMc' THEN [ProcStateColMc]
                         WHEN 'ProcStateColRf' THEN [ProcStateColRf]
                         WHEN 'ProcStateColUn' THEN [ProcStateColUn]
                    END COLLATE Latin1_General_CI_AS
   FROM Objects_tbl with (nolock)
   where Objects_tbl.ObjectClass = @ObjectClass
 )
GO
-- 27.10.2016 10:01/S.Gueth added COLLATE Latin1_General_CI_AS to ProcStateValue


Print '-- [DS_AndProcessDataProcCategoryForCount_UDF] ---------------------------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "DS_AndProcessDataProcCategoryForCount_UDF" and type = "IF")
DROP FUNCTION DS_AndProcessDataProcCategoryForCount_UDF
GO

CREATE FUNCTION [dbo].[DS_AndProcessDataProcCategoryForCount_UDF]
               (@ProcessProcStateCol nvarchar(32), @ObjectClass nvarchar(2))
-- created 16.01.2023 S. Gueth
RETURNS TABLE AS

-- #LUp: 25.07.2023 J.Fes add ProcStateCol21 .. ProcStateCol40
RETURN (SELECT
        ObjectFromSiteID,
        ObjectOwningSiteID,
        ObjectClass,
		      ProcessCategory,
        ObjectUgMaster_Type = CASE [ObjectUgMaster_Type]
           WHEN 'SP' THEN '1-SP (Single Parts)'
           WHEN 'AP' THEN '2-AP (Assembly)'
                     ELSE '3-NP (No CAD.Obj)'
          END,
        ObjectType,
        SiteOwningType = CASE [ObjectFromSiteID]
           WHEN [ObjectOwningSiteID] THEN '1-SO (SiteObj)'
                                     ELSE '2-RO (ReplObj)'
          END,
        ProcStateValue = CASE @ProcessProcStateCol
                         WHEN 'ProcStateCol01' THEN [ProcStateCol01]
                         WHEN 'ProcStateCol02' THEN [ProcStateCol02]
                         WHEN 'ProcStateCol03' THEN [ProcStateCol03]
                         WHEN 'ProcStateCol04' THEN [ProcStateCol04]
                         WHEN 'ProcStateCol05' THEN [ProcStateCol05]
                         WHEN 'ProcStateCol06' THEN [ProcStateCol06]
                         WHEN 'ProcStateCol07' THEN [ProcStateCol07]
                         WHEN 'ProcStateCol08' THEN [ProcStateCol08]
                         WHEN 'ProcStateCol09' THEN [ProcStateCol09]
                         WHEN 'ProcStateCol10' THEN [ProcStateCol10]
                         WHEN 'ProcStateCol11' THEN [ProcStateCol11]
                         WHEN 'ProcStateCol12' THEN [ProcStateCol12]
                         WHEN 'ProcStateCol13' THEN [ProcStateCol13]
                         WHEN 'ProcStateCol14' THEN [ProcStateCol14]
                         WHEN 'ProcStateCol15' THEN [ProcStateCol15]
                         WHEN 'ProcStateCol16' THEN [ProcStateCol16]
                         WHEN 'ProcStateCol17' THEN [ProcStateCol17]
                         WHEN 'ProcStateCol18' THEN [ProcStateCol18]
                         WHEN 'ProcStateCol19' THEN [ProcStateCol19]
                         WHEN 'ProcStateCol20' THEN [ProcStateCol20]
                         WHEN 'ProcStateCol21' THEN [ProcStateCol21]
                         WHEN 'ProcStateCol22' THEN [ProcStateCol22]
                         WHEN 'ProcStateCol23' THEN [ProcStateCol23]
                         WHEN 'ProcStateCol24' THEN [ProcStateCol24]
                         WHEN 'ProcStateCol25' THEN [ProcStateCol25]
                         WHEN 'ProcStateCol26' THEN [ProcStateCol26]
                         WHEN 'ProcStateCol27' THEN [ProcStateCol27]
                         WHEN 'ProcStateCol28' THEN [ProcStateCol28]
                         WHEN 'ProcStateCol29' THEN [ProcStateCol29]
                         WHEN 'ProcStateCol30' THEN [ProcStateCol30]
                         WHEN 'ProcStateCol31' THEN [ProcStateCol31]
                         WHEN 'ProcStateCol32' THEN [ProcStateCol32]
                         WHEN 'ProcStateCol33' THEN [ProcStateCol33]
                         WHEN 'ProcStateCol34' THEN [ProcStateCol34]
                         WHEN 'ProcStateCol35' THEN [ProcStateCol35]
                         WHEN 'ProcStateCol36' THEN [ProcStateCol36]
                         WHEN 'ProcStateCol37' THEN [ProcStateCol37]
                         WHEN 'ProcStateCol38' THEN [ProcStateCol38]
                         WHEN 'ProcStateCol39' THEN [ProcStateCol39]
                         WHEN 'ProcStateCol40' THEN [ProcStateCol40]
                         WHEN 'ProcStateColMc' THEN [ProcStateColMc]
                         WHEN 'ProcStateColRf' THEN [ProcStateColRf]
                         WHEN 'ProcStateColUn' THEN [ProcStateColUn]
                    END COLLATE Latin1_General_CI_AS
   FROM DS_AndProcessData_GroupedByProcCategory_UDF(@ProcessProcStateCol) Obj_tbl
   where Obj_tbl.ObjectClass = @ObjectClass
 )
 GO



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

CREATE PROCEDURE ReportPageNamesInit
  @ReportName nvarchar(100),
  @ReportDescription nvarchar(max)
AS
BEGIN
    -- new 18.10.2010A.Becker 
    TRUNCATE TABLE dbo.ReportPageNames_temp_tbl
    INSERT INTO dbo.ReportPageNames_temp_tbl (Position, Name, Description)
                         VALUES (0, @ReportName, @ReportDescription)
    Return 1
END
GO

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

CREATE PROCEDURE ReportPageNamesAdd
  /*@Position int,*/
  @ReportPageName nvarchar(100),
  @ReportPageDescription nvarchar(max)
AS
BEGIN
    -- new 18.10.2010A.Becker
    DECLARE @Position int;
    SELECT @Position = Max(Position)+1
    FROM dbo.ReportPageNames_temp_tbl

    INSERT INTO dbo.ReportPageNames_temp_tbl (Position, Name, Description)
                         VALUES (@Position, @ReportPageName, @ReportPageDescription)
    Return 1
END
GO
--------------------------------------------------------------------------------
Print '-- [ReportPageNamesGet] --------------------------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ReportPageNamesGet" and type = "P")
Drop Procedure ReportPageNamesGet
GO
CREATE PROCEDURE ReportPageNamesGet
AS
BEGIN
  -- new 18.10.2010 A.Becker
  SELECT *
  FROM dbo.ReportPageNames_temp_tbl
  ORDER BY Position
END
GO
--------------------------------------------------------------------------------
Print '-- [Report_101_Objects_Datasets_Sum] -------------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Report_101_Objects_Datasets_Sum" and type = "P")
Drop Procedure Report_101_Objects_Datasets_Sum
GO

-- l.up: 09.01.2026:A.Stein added GroupByIRType
CREATE PROCEDURE Report_101_Objects_Datasets_Sum
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10),
        @GroupByAP_SP Bit,
        @GroupByObjectType Bit,
        @GroupByIRType Bit
       )
AS
Begin

  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0

  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_101_Objects_Datasets_Sum'

  DECLARE @ExecuteSQLstr nvarchar(max)

  /* =========================
     SELECT-Teil
     ========================= */

  set @ExecuteSQLstr = 'SELECT ' +
                       '  DS.ObjectFromSiteID AS [SiteID], ' +
                       '  CASE DS.[ObjectFromSiteID] ' +
                       '    WHEN DS.[ObjectOwningSiteID] THEN ''1-SO (SiteObj)'' ' +
                       '    ELSE ''2-RO (ReplObj)'' ' +
                       '  END AS [S.Or.Rep Object], '

  if @GroupByAP_SP = 1
  begin
    set @ExecuteSQLstr = @ExecuteSQLstr +
      '  CASE DS.[ObjectUgMaster_Type] ' +
      '    WHEN ''SP'' THEN ''1-SP (Single Parts)'' ' +
      '    WHEN ''AP'' THEN ''2-AP (Assembly)'' ' +
      '    ELSE ''3-NP (No CAD.Obj)'' ' +
      '  END AS [O.UGMaster_Type], '
  end

  /* Wenn GroupByIRType=1, soll IR.ObjectType statt DS.ObjectType selektiert werden (wie in deinem Ziel-SQL) */
  if @GroupByIRType = 1
  begin
    set @ExecuteSQLstr = @ExecuteSQLstr +
      '  IR.[ObjectType] AS [ItemRev Type], '
  end


    if @GroupByObjectType = 1
    begin
      set @ExecuteSQLstr = @ExecuteSQLstr +
        '  DS.[ObjectType] AS [Dataset Type], '
    end


  set @ExecuteSQLstr = @ExecuteSQLstr +
    '  COUNT(DS.ObjectType) AS [Dataset Count] '

  /* =========================
     FROM / JOIN-Teil
     ========================= */

  set @ExecuteSQLstr = @ExecuteSQLstr +
    'FROM [dbo].[Objects_tbl] DS WITH (NOLOCK) '

  if @GroupByIRType = 1
  begin
    set @ExecuteSQLstr = @ExecuteSQLstr +
      'LEFT JOIN dbo.Objects_Ir_Rel_tbl rel WITH (NOLOCK) ON (DS.Id = rel.ObjectId) ' +
      'LEFT JOIN dbo.Objects_tbl IR WITH (NOLOCK) ON (rel.IrId = IR.Id) '
  end


  /* =========================
     WHERE-Teil
     ========================= */

  set @ExecuteSQLstr = @ExecuteSQLstr +
    'WHERE (DS.ObjectClass = ''' + @ObjectClass + ''') '

  if CharIndex('%', @ObjectFromSiteID) > 0
  begin
    if @ObjectFromSiteID <> '%'
    begin
      set @ExecuteSQLstr = @ExecuteSQLstr +
        'AND (DS.[ObjectFromSiteID] LIKE ''' + @ObjectFromSiteID + ''') '
    end
  end
  else
  begin
    if @ObjectFromSiteID <> ''
    begin
      set @ExecuteSQLstr = @ExecuteSQLstr +
        'AND (DS.[ObjectFromSiteID] = ''' + @ObjectFromSiteID + ''') '
    end
  end

/*
  if @CompRange > 0
  begin
    set @ExecuteSQLstr = @ExecuteSQLstr +
      'AND (DS.ObjectUgMaster_CompCountUnique <= ' + @CompRange + ') '
  end
*/

  /* =========================
     GROUP BY-Teil
     ========================= */

  set @ExecuteSQLstr = @ExecuteSQLstr +
    'GROUP BY ' +
    '  DS.ObjectFromSiteID, '

  if @GroupByAP_SP = 1
  begin
    set @ExecuteSQLstr = @ExecuteSQLstr +
      '  DS.ObjectUgMaster_Type, '
  end

  if @GroupByIRType = 1
  begin
    set @ExecuteSQLstr = @ExecuteSQLstr +
      '  IR.ObjectType, '
  end
  

  if @GroupByObjectType = 1
    begin
      set @ExecuteSQLstr = @ExecuteSQLstr +
        '  DS.ObjectType, '
    end


  set @ExecuteSQLstr = @ExecuteSQLstr +
    '  CASE DS.[ObjectFromSiteID] ' +
    '    WHEN DS.[ObjectOwningSiteID] THEN ''1-SO (SiteObj)'' ' +
    '    ELSE ''2-RO (ReplObj)'' ' +
    '  END '

  /* BUGFIX: WHEN 'AP' statt fälschlich nochmal WHEN 'SP' */
  if @GroupByAP_SP = 1
  begin
    set @ExecuteSQLstr = @ExecuteSQLstr +
      ', CASE DS.ObjectUgMaster_Type ' +
      '    WHEN ''SP'' THEN ''1-SP (Single Parts)'' ' +
      '    WHEN ''AP'' THEN ''2-AP (Assembly)'' ' +
      '    ELSE ''3-NP (No CAD.Obj)'' ' +
      '  END '
  end

  /* =========================
     Debug (optional)
     ========================= */

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

    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 '-- [Rrt_200_ProcStatus] -------------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Rrt_200_ProcStatus")
Drop Procedure Rrt_200_ProcStatus
If Exists (select Name
           from sysobjects
           where Name = "Rrt_200_ProcStatus" and type = "P")
Drop Procedure Rrt_200_ProcStatus
GO



CREATE PROCEDURE Rrt_200_ProcStatus
       (
        @ObjectFromSiteID nvarchar(32),
        @ProcStateCol nvarchar(14),
        @ViewFormat nvarchar(14), -- Valid Values "CountPercent" 'Count'
        @GroupDataDo bit,  -- 29.09.2012 New J.Fes
        @ObjectClass nvarchar(10), -- #New 28.08.2013/J.Fes,
        @GroupByAP_SP Bit,         -- #New 05.05.2021/A.Stein,
      	@GroupByObjectType Bit,     -- #New 05.05.2021/A.Stein,
        @GroupByProcessCategory Bit -- #New 03.11.2022/S.Gueth,
       )
AS
begin
  -- #Doc: Update Info
  -- #LUp: 25.12.2021 add Colume Done:OK+WRN

  DECLARE @DebugPrintOn bit
  --set @DebugPrintOn = 1
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Rrt_200_ProcStatus'

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

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

  if @GroupDataDo = 1
  begin
  set @ExecuteSQLstr = @ExecuteSQLstr +
    ' SiteOwningType      AS [S.Or.Rep Object],'

    if @GroupByProcessCategory = 1
	begin
	set @ExecuteSQLstr = @ExecuteSQLstr +
		' ProcessCategory     AS [Process Category],'
	end

	if @GroupByAP_SP = 1
	begin
	set @ExecuteSQLstr = @ExecuteSQLstr +
		' ObjectUgMaster_Type AS [O.UGMaster_Type],'
	end

	if @GroupByObjectType = 1
	begin
	set @ExecuteSQLstr = @ExecuteSQLstr +
		' ObjectType          AS [DataSet Type],'
	end

   end

   set @ExecuteSQLstr = @ExecuteSQLstr +
    ' COUNT(ObjectType)        AS [DsSum], ' +        -- 'Datasets (sum)'
    ' Sum(CASE WHEN ([ProcStateValue] = ''N'')  THEN 1 ELSE 0 END)        AS [PStatN],' +      -- 'P.Stat:N (not.Proc)'
    ' Sum(CASE WHEN ([ProcStateValue] like ''X%'')  THEN 1 ELSE 0 END)    AS [PStatX],' +      -- 'P.Stat:X (eXcludet)'
    ' Sum(CASE WHEN ([ProcStateValue] like ''T%'')  THEN 1 ELSE 0 END)    AS [PStatT],' +      -- 'P.Stat:X (eXcludet)'
    ' Sum(CASE WHEN ([ProcStateValue] like ''R%'')  THEN 1 ELSE 0 END)    AS [PStatR],' +      -- 'P.Stat:R (Run* not.Proc)'
    ' Sum(CASE WHEN ([ProcStateValue] like ''P%'')  THEN 1 ELSE 0 END)    AS [PStatP],' +      -- 'P.Stat:P (in.Proc)'
    ' Sum(CASE WHEN ([ProcStateValue] like ''D%'' ) THEN 1 ELSE 0 END)    AS [PStatD],' +      -- 'P.Stat:D (Done)'
    ' Sum(CASE WHEN ([ProcStateValue] like ''D%.OK'')  THEN 1 ELSE 0 END) AS [PResOk],' +      -- 'Done:OK'
    ' Sum(CASE WHEN ([ProcStateValue] like ''D%.WRN'') THEN 1 ELSE 0 END) AS [PResHasW],' +    -- 'Done:ProcessResultHasWarning'
    ' Sum(CASE WHEN ([ProcStateValue] like ''D%.ERR'') THEN 1 ELSE 0 END) AS [PResERR],' +     -- 'Done:Err'
    ' Sum(CASE WHEN ([ProcStateValue] not like ''[TXNRPD]%'') THEN 1 ELSE 0 END) AS [PResO]' + -- 'Special' all other
    ' into ' + @TempTblName --+ -- einf?gen in temp Tabelle!!
    --' from [dbo].[DS_AndProcessDataForCount_UDF](''' + @ProcStateCol + ''',''' + @ObjectClass + ''')'

	if @GroupByProcessCategory = 0
	begin
	set @ExecuteSQLstr = @ExecuteSQLstr +
		' from [dbo].[DS_AndProcessDataForCount_UDF](''' + @ProcStateCol + ''',''' + @ObjectClass + ''')'
	end
	if @GroupByProcessCategory = 1
	begin
	set @ExecuteSQLstr = @ExecuteSQLstr +
		' from [dbo].[DS_AndProcessDataProcCategoryForCount_UDF](''' + @ProcStateCol + ''',''' + @ObjectClass + ''')'
	end

    --     ' Sum(CASE WHEN (([ProcStateValue] like ''D%.WRN'') or ([ProcStateValue] like ''D%.OK''))  THEN 1 ELSE 0 END) AS [PResOkAndHasW],' +    -- 'Done:ProcessResultHasWarning'


  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 +
                           ' WHERE ([ObjectFromSiteID] like ' + '''' + @ObjectFromSiteID + '''' + ')'
     end
   end
   else
   begin
    if @ObjectFromSiteID <> ''
     -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
     begin
      set @ExecuteSQLstr = @ExecuteSQLstr +
                          ' WHERE ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
     end
   end

   /* feststellung: group by hat keine messbaren auswirkungen auf performance */
   set @ExecuteSQLstr = @ExecuteSQLstr +
                       ' GROUP BY ' +
                       ' [ObjectFromSiteID] '

  if @GroupDataDo = 1
	begin
   set @ExecuteSQLstr = @ExecuteSQLstr + ',' +
                        ' [SiteOwningType]'

 	if @GroupByProcessCategory = 1
	begin
	set @ExecuteSQLstr = @ExecuteSQLstr + ',' +
		' [ProcessCategory]'
	end

	if @GroupByAP_SP = 1
	begin
	set @ExecuteSQLstr = @ExecuteSQLstr + ',' +
		' [ObjectUgMaster_Type]'
	end

	if @GroupByObjectType = 1
	begin
	set @ExecuteSQLstr = @ExecuteSQLstr + ',' +
		' [ObjectType]'
	end

  end
  ------------ ## 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


  if @ViewFormat = 'Percent'
   begin
    -- Select f?r die Anzeige mit % Daten
    -- 01.11.2010/J.Fes #Doc: die Prozentzahlen f?r Done:OK + Done:Warning + Done:Err +
    --                  #Doc: werden jetzt basierend auf den Werten von [PStatD] berechnet

    set @ExecuteSQLstr = 'Select' +
     ' [SiteID],'
     if @GroupDataDo = 1
     begin
     set @ExecuteSQLstr = @ExecuteSQLstr +
     '[S.Or.Rep Object],'

	 if @GroupByAP_SP = 1
	 begin
	 set @ExecuteSQLstr = @ExecuteSQLstr +
		'[O.UGMaster_Type],'
	 end

	 if @GroupByObjectType = 1
	 begin
	 set @ExecuteSQLstr = @ExecuteSQLstr +
		'[DataSet Type],'
	 end

	 if @GroupByProcessCategory = 1
	 begin
	 set @ExecuteSQLstr = @ExecuteSQLstr +
		'[Process Category],'
	 end


     end

     set @ExecuteSQLstr = @ExecuteSQLstr +
     -- 07.11.2014/J.Fes '[DsSum] AS [Datasets:(sum)],' +
     '[DsSum] AS [Obj.:(sum)],' +

       -- 07.11.2014/J.Fes change from:[DsSum] to:[PStatD]
       -- die Prozentangaben beziehen sich jetzt auf die verarbeiteten Objekte
      '[PStatX]               AS [P.Stat:X],' +
      ' case when [DsSum] > 0 then (Str((100.00*[PStatX]/([DsSum])),6,2) + ''%'') else ''~'' end AS [P.Stat:X %],' +

      '[PStatT]               AS [P.Stat:T],' +
      ' case when [DsSum] > 0 then (Str((100.00*[PStatT]/([DsSum])),6,2) + ''%'') else ''~'' end AS [P.Stat:T %],' +


      '[PStatN]               AS [P.Stat:N],' +
      ' case when [DsSum] > 0 then (Str((100.00*[PStatN]/([DsSum])),6,2) + ''%'') else ''~'' end AS [P.Stat:N %],' +

      '[PStatR]               AS [P.Stat:ToRun],' +
      ' case when [DsSum] > 0 then (Str((100.00*[PStatR]/([DsSum])),6,2) + ''%'') else ''~'' end AS [P.Stat:ToRun %],' +

      '[PResO]      AS [P.Stat:O (Other)],' +
     ' case when [DsSum] > 0 then (Str((100.00*[PResO]/([DsSum])),6,2) + ''%'') else ''~'' end AS [P.Stat:O %],' +

--       '[PStatM]               AS [P.Stat:M] ,' +
--       ' case when [DsSum] > 0 then (Str((100.00*[PStatM]/([DsSum])),6,2) + ''%'') else ''~'' end AS [P.Stat:M %] ,' +

      '[PStatP]               AS [P.Stat:P],' +
      ' case when [DsSum] > 0 then (Str((100.00*[PStatP]/([DsSum])),6,2) + ''%'') else ''~'' end AS [P.Stat:P %],' +

      '[PStatD]               AS [Done:All],' +
      ' case when [DsSum] > 0 then (Str((100.00*[PStatD]/([DsSum])),6,2) + ''%'') else ''~'' end AS [Done:All %],' +

      '[PResOk]               AS [Done:OK],' +
      ' case when [PStatD] > 0 then (Str((100.00*[PResOk]/([PStatD])),6,2) + ''%'') else ''~'' end AS [Done:OK %],' +

      '[PResHasW]             AS [Done:WRN],' +
      ' case when [PStatD] > 0 then (Str((100.00*[PResHasW]/([PStatD])),6,2) + ''%'') else ''~'' end AS [Done:WRN %], ' +

      '[PResOk] + [PResHasW]  AS [Done:OK+WRN],' +   -- #LUp: 25.12.2021 add Colume Done:OK+WRN
      ' case when [PStatD] > 0 then (Str((100.00*([PResOk] + [PResHasW])/([PStatD])),6,2) + ''%'') else ''~'' end AS [Done:OK+WRN %], ' +


      '[PResERR]              AS [Done:ERR],' +
      ' case when [PStatD] > 0 then (Str((100.00*[PResERR]/([PStatD])),6,2) + ''%'') else ''~'' end AS [Done:ERR %]' +

    'from ' + @TempTblName
   end
  else
   begin
    -- Select for view only count data
    set @ExecuteSQLstr = 'Select' +
     ' [SiteID],'
     if @GroupDataDo = 1
     begin
      set @ExecuteSQLstr = @ExecuteSQLstr +
      '[S.Or.Rep Object],'

    	 if @GroupByAP_SP = 1
    	 begin
    	 set @ExecuteSQLstr = @ExecuteSQLstr +
    		'[O.UGMaster_Type],'
    	 end

    	 if @GroupByObjectType = 1
    	 begin
    	 set @ExecuteSQLstr = @ExecuteSQLstr +
    		'[DataSet Type],'
    	 end

		 if @GroupByProcessCategory = 1
		 begin
		 set @ExecuteSQLstr = @ExecuteSQLstr +
			'[Process Category],'
		 end

     end

     set @ExecuteSQLstr = @ExecuteSQLstr +
      '[DsSum]      AS [Obj.:(sum)],' +
      '[PStatN]     AS [P.Stat:N (Not.Proc)],' +
      '[PStatX]     AS [P.Stat:X (eXcludet)],' +
      '[PStatT]     AS [P.Stat:T (Test)],' +  -- #LUp 15.05.2018 J.Fes Repair Test Colume from [PStatX] to [PStatT]
      '[PStatR]     AS [P.Stat:R (to.Run)],' +
      '[PResO]      AS [P.Stat:O (Other)],' +
      -- '[PStatM]     AS [P.Stat:M (Manuell)],' +
      '[PStatP]     AS [P.Stat:P (in.Proc)],' +
      '[PStatD]     AS [P.Stat:D (Done)],' +
      '[PResOk]     AS [Done:OK],' +
      '[PResHasW]   AS [Done:WRN],' +
      '[PResOk] + [PResHasW] AS [Done:OK+WRN],' +   -- #LUp: 25.12.2021 add Colume Done:OK+WRN
      '[PResERR]    AS [Done:ERR]' +
      'from ' + @TempTblName
    end

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

   execute sp_executesql @ExecuteSQLstr
End
Go

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

CREATE PROCEDURE Report_201_ProcStatus_Percent
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10),
		      @GroupByAP_SP Bit,
		      @GroupByObjectType Bit,
        @GroupByProcessCategory Bit
       )
AS
begin
 Execute Rrt_200_ProcStatus @ObjectFromSiteID, @ProcStateCol, 'Percent', 1, @ObjectClass, @GroupByAP_SP, @GroupByObjectType, @GroupByProcessCategory
end
GO


--------------------------------------------------------------------------------
Print '-- [Report_202_ProcStatus_Count] ----------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_202_ProcStatus_Count")
Drop Procedure Report_202_ProcStatus_Count
GO
CREATE PROCEDURE Report_202_ProcStatus_Count
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10),
      		@GroupByAP_SP Bit,
      		@GroupByObjectType Bit,
        @GroupByProcessCategory Bit
       )
AS
begin
 Execute Rrt_200_ProcStatus @ObjectFromSiteID, @ProcStateCol, 'Count', 1, @ObjectClass, @GroupByAP_SP, @GroupByObjectType, @GroupByProcessCategory
end
GO


--------------------------------------------------------------------------------
Print '-- [Rrt_210_ProcStatus_Count #New 01.08.2018] ---------------------------'
If Exists (select Name
           from sysobjects
           where Name = 'Rrt_210_ProcStatus_Count' and type = 'P')
Drop Procedure Rrt_210_ProcStatus_Count
GO

CREATE PROCEDURE Rrt_210_ProcStatus_Count
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10),
        @GroupByAP_SP Bit,
		      @GroupByObjectType Bit
       )

AS
begin
  DECLARE @DebugPrintOn bit
 -- set @DebugPrintOn = 1
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Rrt_210_ProcStatus_Count'
  DECLARE @ExecuteSQLstr nvarchar(max)

  set @ExecuteSQLstr = +
   'SELECT ' +
   '  CountUDF.ObjectFromSiteID AS [SiteID],' +
   '  CountUDF.SiteOwningType AS [S.Or.Rep Object],'
   
   
	   if @GroupByAP_SP = 1
	   begin
	   set @ExecuteSQLstr = @ExecuteSQLstr + '  CountUDF.ObjectUgMaster_Type AS [O.UGMaster_Type],'
	   end

	   if @GroupByObjectType = 1
	   begin
	   set @ExecuteSQLstr = @ExecuteSQLstr +  '  CountUDF.ObjectType AS [DataSet Type],'
	   end

   --'  CountUDF.ObjectUgMaster_Type AS [O.UGMaster_Type],' +
   --'  CountUDF.ObjectType AS [DataSet Type],' +
   set @ExecuteSQLstr = @ExecuteSQLstr +
   '  CountUDF.ProcStateValue AS [P.Stat.],' +
   '  Count(*) AS [P.Stat.:Count(n)]' +
   ' FROM ' +
   '  dbo.DS_AndProcessDataForCount_UDF(''' + @ProcStateCol + ''',''' + @ObjectClass + ''') AS CountUDF' +
   ' WHERE' +
   '  CountUDF.ProcStateValue IS NOT NULL'

  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

  set @ExecuteSQLstr = @ExecuteSQLstr +
   ' GROUP BY' +
   '  CountUDF.ObjectFromSiteID,' +
   '  CountUDF.SiteOwningType,' 
   
   
   
	   if @GroupByAP_SP = 1
	   begin
	   set @ExecuteSQLstr = @ExecuteSQLstr + '  CountUDF.ObjectUgMaster_Type,' 
	   end

	   if @GroupByObjectType = 1
	   begin
	   set @ExecuteSQLstr = @ExecuteSQLstr +  '  CountUDF.ObjectType,'
	   end

   --'  CountUDF.ObjectUgMaster_Type,' +
   --'  CountUDF.ObjectType,' +

   set @ExecuteSQLstr = @ExecuteSQLstr +
   '  CountUDF.ProcStateValue'

--     +
--    ' ORDER BY' +
--    '  CountUDF.ObjectFromSiteID,' +
--    '  CountUDF.OwningType,'
--    '  CountUDF.ObjectUgMaster_Type,' +
--    '  [O.UGMaster_Type] DESC,' +
--    '  OType'

  if @DebugPrintOn = 1
  begin
   -- 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_210_ProcStatus_byPStatCount #New 01.08.2018] -----------------'

If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_210_ProcStatus_Count")
Drop Procedure Report_210_ProcStatus_Count
GO

If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_210_ProcStatus_byPStatCount")
Drop Procedure Report_210_ProcStatus_byPStatCount
GO
CREATE PROCEDURE Report_210_ProcStatus_byPStatCount
       (
        @ObjectFromSiteID nvarchar(32),
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10),
		@GroupByAP_SP Bit,      --always false
		@GroupByObjectType Bit  -- always false
       )
AS
begin
 Execute Rrt_210_ProcStatus_Count @ObjectFromSiteID, @ProcStateCol, @ObjectClass, @GroupByAP_SP, @GroupByObjectType
end
GO

--------------------------------------------------------------------------------



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

CREATE PROCEDURE Report_101_VolumeStatistic
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )

AS
begin

  -- #New: 10.11.2014/J.Fes
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_101_VolumeStatistic'
  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @TempTblName   nvarchar(32)
--   set @TempTblName = '##temp_tbl'
--   IF (OBJECT_ID ('tempdb..' + @TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)
--   set @TempTblName = 'tempdb..##temp_tbl'
--   IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

  set @ExecuteSQLstr = +
        'SELECT ' +
        '  dbo.Objects_tbl.ObjectFromSiteID AS [SiteId], ' +
        '  dbo.Objects_tbl.ObjectFs_Vol AS [Volume.Name], ' +
        '  COUNT(dbo.Objects_tbl.ObjectFs_Vol) AS [has n (latest Part.Files) on Volume] ' +
        'FROM ' +
        '  dbo.Objects_tbl ' +
        'WHERE' +
        '  Objects_tbl.ObjectClass = ''DS'''


  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

   set @ExecuteSQLstr = @ExecuteSQLstr +
  'GROUP BY' +
  ' dbo.Objects_tbl.ObjectFs_Vol, ' +
  ' dbo.Objects_tbl.ObjectFromSiteID ' +
  'Order By' +
  ' dbo.Objects_tbl.ObjectFromSiteID, ' +
  ' dbo.Objects_tbl.ObjectFs_Vol '


  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_102_VolumeStatistic_DSType_All] ------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Report_102_VolumeStatistic_DSType_All" and type = "P")
Drop Procedure Report_102_VolumeStatistic_DSType_All
GO

CREATE PROCEDURE Report_102_VolumeStatistic_DSType_All
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_102_VolumeStatistic_DSType_All'
  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @TempTblName   nvarchar(32)
--   set @TempTblName = '##temp_tbl'
--   IF (OBJECT_ID ('tempdb..' + @TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)
--   set @TempTblName = 'tempdb..##temp_tbl'
--   IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

  set @ExecuteSQLstr = +
        'SELECT ' +
        '  dbo.Objects_tbl.ObjectFromSiteID AS [SiteId], ' +
        '  dbo.Objects_tbl.ObjectFs_Vol AS [Volume.Name], ' +
        '  dbo.Objects_tbl.ObjectType AS   [O.Type], ' +
        '  COUNT(dbo.Objects_tbl.ObjectFs_Vol) AS [has n (latest Part.Files) on Volume] ' +
        'FROM ' +
        '  dbo.Objects_tbl ' +
        'WHERE' +
        '  Objects_tbl.ObjectClass = ''DS'''


  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

   set @ExecuteSQLstr = @ExecuteSQLstr +
  'GROUP BY' +
  ' dbo.Objects_tbl.ObjectFs_Vol, ' +
  ' dbo.Objects_tbl.ObjectType, ' +
  ' dbo.Objects_tbl.ObjectFromSiteID ' +
  'Order By' +
  ' dbo.Objects_tbl.ObjectFromSiteID, ' +
  ' dbo.Objects_tbl.ObjectFs_Vol '


  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_112_GroupStatistic_DSType_All] -------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Report_112_GroupStatistic_DSType_All" and type = "P")
Drop Procedure Report_112_GroupStatistic_DSType_All
GO


CREATE PROCEDURE Report_112_GroupStatistic_DSType_All
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_112_GroupStatistic_DSType_All'
  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @TempTblName   nvarchar(32)
--   set @TempTblName = '##temp_tbl'
--   IF (OBJECT_ID ('tempdb..' + @TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)
--   set @TempTblName = 'tempdb..##temp_tbl'
--   IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

  set @ExecuteSQLstr = +
        'SELECT ' +
        '  dbo.Objects_tbl.ObjectFromSiteID AS [SiteId], ' +
        '  dbo.Objects_tbl.ObjectGroup AS [Group], ' +
        '  dbo.Objects_tbl.ObjectType AS  [O.Type], ' +
        '  COUNT(dbo.Objects_tbl.ObjectFs_Vol) AS [has n (latest Part.Files) in Group] ' +
        'FROM ' +
        '  dbo.Objects_tbl ' +
        'WHERE' +
        '  Objects_tbl.ObjectClass = ''DS'''


  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

   set @ExecuteSQLstr = @ExecuteSQLstr +
  'GROUP BY' +
  ' dbo.Objects_tbl.ObjectGroup, ' +
  ' dbo.Objects_tbl.ObjectType, ' +
  ' dbo.Objects_tbl.ObjectFromSiteID ' +
  'Order By' +
  ' dbo.Objects_tbl.ObjectFromSiteID, ' +
  ' dbo.Objects_tbl.ObjectGroup '


  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 09.06.2016  from BSH: Krzysztof/J.Fes -----------------------------
Print '-- [Report_113_Analyse_DatasetVersion] --------------------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_113_Analyse_DatasetVersion")
Drop Procedure Report_113_Analyse_DatasetVersion
GO

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

  DECLARE @AndProcessData_UDF nvarchar(32)
SELECT
  dbo.Objects_tbl.ObjectFromSiteID As SiteId,
  dbo.Objects_tbl.ObjectDs_VerKeepLimt As [Keep.Limit],
  dbo.Objects_tbl.ObjectDs_VersN  As [DS.Ver(n)],
  COUNT([ObjectFromSiteID]) AS [Happend N.times]
FROM
  dbo.Objects_tbl
WHERE
  dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID AND
  dbo.Objects_tbl.ObjectClass = 'DS'
GROUP BY
  dbo.Objects_tbl.ObjectFromSiteID,
  dbo.Objects_tbl.ObjectDs_VersN,
  dbo.Objects_tbl.ObjectDs_VerKeepLimt
END
GO

---------------- #New 29.08.2024  from BSH: Krzysztof/J.Fes -----------------------------
Print '-- [Report_113_Analyse_DatasetVersionGroupByVols] --------------------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_113_Analyse_DatasetVersionGroupByVols")
Drop Procedure Report_113_Analyse_DatasetVersionGroupByVols
GO

CREATE PROCEDURE Report_113_Analyse_DatasetVersionGroupByVols
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
-- new 29.08.2024 J.Fes+K.Duszkiewicz//BSH
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_113_Analyse_DatasetVersionGroupByVols'
  DECLARE @ExecuteSQLstr nvarchar(max)

DECLARE @AndProcessData_UDF nvarchar(32)

SELECT
  dbo.Objects_tbl.ObjectFromSiteID As SiteId,
  dbo.Objects_tbl.ObjectFs_Vol As [Volume],
  dbo.Objects_tbl.ObjectDs_VerKeepLimt As [Keep.Limit],
  dbo.Objects_tbl.ObjectDs_VersN  As [DS.Ver(n)],
  COUNT([ObjectFromSiteID]) AS [Happend N.times]
FROM
  dbo.Objects_tbl
WHERE
  dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID AND
  dbo.Objects_tbl.ObjectClass = 'DS'
GROUP BY
  dbo.Objects_tbl.ObjectFromSiteID,
  dbo.Objects_tbl.ObjectFs_Vol,
  dbo.Objects_tbl.ObjectDs_VersN,
  dbo.Objects_tbl.ObjectDs_VerKeepLimt
END

GO



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

CREATE PROCEDURE Report_103_VolumeStatistic_DSType_NX
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_103_VolumeStatistic_DSType_NX'
  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @TempTblName   nvarchar(32)
--   set @TempTblName = '##temp_tbl'
--   IF (OBJECT_ID ('tempdb..' + @TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)
--   set @TempTblName = 'tempdb..##temp_tbl'
--   IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

  set @ExecuteSQLstr = +
        'SELECT ' +
        '  dbo.Objects_tbl.ObjectFromSiteID AS [SiteId], ' +
        '  dbo.Objects_tbl.ObjectFs_Vol AS [Volume.Name], ' +
        '  COUNT(dbo.Objects_tbl.ObjectFs_Vol) AS [has n (latest Part.Files) on Volume] ' +
        'FROM ' +
        '  dbo.Objects_tbl ' +
        'WHERE' +
        '  Objects_tbl.ObjectClass = ''DS''' +
        '  and dbo.Objects_tbl.ObjectType not like ''Ideas%''' +
        '  and dbo.Objects_tbl.ObjectType not like ''Pro%'''


  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

   set @ExecuteSQLstr = @ExecuteSQLstr +
  'GROUP BY' +
  ' dbo.Objects_tbl.ObjectFs_Vol, ' +
  ' dbo.Objects_tbl.ObjectFromSiteID ' +
  'Order By' +
  ' dbo.Objects_tbl.ObjectFromSiteID, ' +
  ' dbo.Objects_tbl.ObjectFs_Vol '


  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_104_VolumeStatistic_DSType_Ideas] ----------------------------'
If Exists (select Name
           from sysobjects
           where Name = 'Report_104_VolumeStatistic_DSType_Ideas' and type = 'P')
Drop Procedure Report_104_VolumeStatistic_DSType_Ideas
GO

CREATE PROCEDURE Report_104_VolumeStatistic_DSType_Ideas
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_104_VolumeStatistic_DSType_Ideas'
  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @TempTblName   nvarchar(32)
--   set @TempTblName = '##temp_tbl'
--   IF (OBJECT_ID ('tempdb..' + @TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)
--   set @TempTblName = 'tempdb..##temp_tbl'
--   IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

  set @ExecuteSQLstr = +
        'SELECT ' +
        '  dbo.Objects_tbl.ObjectFromSiteID AS [SiteId], ' +
        '  dbo.Objects_tbl.ObjectFs_Vol AS [Volume.Name], ' +
        '  COUNT(dbo.Objects_tbl.ObjectFs_Vol) AS [has n (latest Part.Files) on Volume] ' +
        'FROM ' +
        '  dbo.Objects_tbl ' +
        'WHERE' +
        '  Objects_tbl.ObjectClass = ''DS''' +
        '  and dbo.Objects_tbl.ObjectType like ''Ideas%'''


  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

   set @ExecuteSQLstr = @ExecuteSQLstr +
  'GROUP BY' +
  ' dbo.Objects_tbl.ObjectFs_Vol, ' +
  ' dbo.Objects_tbl.ObjectFromSiteID ' +
  'Order By' +
  ' dbo.Objects_tbl.ObjectFromSiteID, ' +
  ' dbo.Objects_tbl.ObjectFs_Vol '


  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_105_VolumeStatistic_DSType_ProE #New 05.03.2014] -------------'
If Exists (select Name
           from sysobjects
           where Name = 'Report_105_VolumeStatistic_DSType_ProE' and type = 'P')
Drop Procedure Report_105_VolumeStatistic_DSType_ProE
GO

CREATE PROCEDURE Report_105_VolumeStatistic_DSType_ProE
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_105_VolumeStatistic_DSType_ProE'
  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @TempTblName   nvarchar(32)
--   set @TempTblName = '##temp_tbl'
--   IF (OBJECT_ID ('tempdb..' + @TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)
--   set @TempTblName = 'tempdb..##temp_tbl'
--   IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

  set @ExecuteSQLstr = +
        'SELECT ' +
        '  dbo.Objects_tbl.ObjectFromSiteID AS [SiteId], ' +
        '  dbo.Objects_tbl.ObjectFs_Vol AS [Volume.Name], ' +
        '  COUNT(dbo.Objects_tbl.ObjectFs_Vol) AS [has n (latest Part.Files) on Volume] ' +
        'FROM ' +
        '  dbo.Objects_tbl ' +
        'WHERE' +
        '  Objects_tbl.ObjectClass = ''DS''' +
        '  and dbo.Objects_tbl.ObjectType like ''Pro%'''


  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

   set @ExecuteSQLstr = @ExecuteSQLstr +
  'GROUP BY' +
  ' dbo.Objects_tbl.ObjectFs_Vol, ' +
  ' dbo.Objects_tbl.ObjectFromSiteID ' +
  'Order By' +
  ' dbo.Objects_tbl.ObjectFromSiteID, ' +
  ' dbo.Objects_tbl.ObjectFs_Vol '


  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 05.05.2021/A.Stein,
Print '-- [Report_114_Analyse_Assembly #New 03.05.20121] -------------'
If Exists (select Name
           from sysobjects
           where Name = 'Report_114_Analyse_Assembly' and type = 'P')
Drop Procedure Report_114_Analyse_Assembly
GO

Create PROCEDURE [dbo].[Report_114_Analyse_Assembly]
    (
	-- Add the parameters for the stored procedure here
	    @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10),
		      @GroupByObjectType Bit,
        @CompRange int = 0
    )
AS
BEGIN
	DECLARE @DebugPrintOn bit
	--set @DebugPrintOn = 1
	DECLARE @MyProcName nvarchar(32)
	set @MyProcName='Report_114_Analyse_Assembly'
	DECLARE @ExecuteSQLstr nvarchar(max)
    DECLARE @CompRangeAsVarChar varchar(max)
    
  set @ExecuteSQLstr =
   'SELECT ' +
   ' ObjectFromSiteID AS [SiteID],' +
   ' ObjectUgMaster_APLevels AS [APLevels],'
   
   if @CompRange > 0
   begin 
   set @CompRangeAsVarChar = cast(@CompRange as varchar)
   set @ExecuteSQLstr = @ExecuteSQLstr + ' cast((ObjectUgMaster_CompCount/'+@CompRangeAsVarChar+')*'+@CompRangeAsVarChar+' as varchar) + ''-'' + cast((ObjectUgMaster_CompCount/'+@CompRangeAsVarChar+')*'+@CompRangeAsVarChar+'+'+cast(@CompRange-1 as varchar)+' as varchar) as [ComponentsRange],'
   end
   --' cast((ObjectUgMaster_CompCount/100)*100 as varchar) + ''-'' + cast((ObjectUgMaster_CompCount/100)*100+99 as varchar) as [ComponentsRange],' + 
   
   set @ExecuteSQLstr = @ExecuteSQLstr +
   ' SUM(ObjectUgMaster_CompCount) as [CompCount (Sum)],' +
   ' SUM(ObjectUgMaster_CompCountUnique) as [UniqueCompCount (Sum)],' +
   ' SUM(ObjectUgMaster_Lev1CompN) as [Lev1CompCount (Sum)],' + 
   ' SUM(ObjectUgMaster_Lev1CompUniqueN) as [Lev1UniqueCompCount (Sum)],'
   
   --'  CountUDF.SiteOwningType AS [S.Or.Rep Object],'

   if @GroupByObjectType = 1
   begin
		set @ExecuteSQLstr = @ExecuteSQLstr + ' ObjectType,'
   end

   set @ExecuteSQLstr = @ExecuteSQLstr + ' count(*) as [Object Count]' + 

   ' from Objects_tbl' +
   ' where' +
   ' ObjectUgMaster_Type = ''AP''' +
   ' and ObjectClass =''' + @ObjectClass + ''''
   
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

set @ExecuteSQLstr = @ExecuteSQLstr + 
   ' group by ObjectFromSiteID, ObjectUgMaster_APLevels'

   if @CompRange > 0
   begin 
   set @ExecuteSQLstr = @ExecuteSQLstr + ', ObjectUgMaster_CompCount/' +  cast(@CompRange as varchar)
   end
   
   if @GroupByObjectType = 1
   begin
		set @ExecuteSQLstr = @ExecuteSQLstr + ', ObjectType'
   end

   set @ExecuteSQLstr = @ExecuteSQLstr + 
   ' order by' +
   ' ObjectUgMaster_APLevels'


  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 05.05.2021/A.Stein,
Print '-- [Report_115_Analyse_LastModifiedByDate #New 04.05.20121] -------------'
If Exists (select Name
           from sysobjects
           where Name = 'Report_115_Analyse_LastModifiedByDate' and type = 'P')
Drop Procedure Report_115_Analyse_LastModifiedByDate
GO

CREATE PROCEDURE [dbo].[Report_115_Analyse_LastModifiedByDate]
       (
        @ObjectFromSiteID nvarchar(32),
        @ObjectClass nvarchar(10),
		@GroupByAP_SP Bit,
		@GroupByObjectType Bit,
		@Interval nvarchar(20)
       )
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @DebugPrintOn bit
	--set @DebugPrintOn = 1
	DECLARE @MyProcName nvarchar(32)
	set @MyProcName='Report_115_Analyse_LastModifiedByDate'
	DECLARE @ExecuteSQLstr nvarchar(max)
	DECLARE @IntervalSelectSQL nvarchar(max)
	DECLARE @IntervalGroupBy nvarchar(max)
	DECLARE @IntervalFormat nvarchar(max)
	

-- format interval Query
IF @Interval = 'daily'
begin
	SET @IntervalFormat = 'FORMAT(ObjectLastModDate, ''yyyy-MM-dd'')'
	SET @IntervalSelectSQL = @IntervalFormat + ' as [TimeSpan], FORMAT(ObjectLastModDate, ''yyyy'') as [Year], FORMAT(ObjectLastModDate, ''MM'') as [Month], FORMAT(ObjectLastModDate, ''dd'') as [Day]'
	SET @IntervalGroupBy =  @IntervalFormat + ' , FORMAT(ObjectLastModDate, ''yyyy''), FORMAT(ObjectLastModDate, ''MM''), FORMAT(ObjectLastModDate, ''dd'')'
end
IF @Interval = 'weekly'
begin
	SET @IntervalFormat  = 'FORMAT(ObjectLastModDate, ''yyyy'') + ''-CW'' + RIGHT(''00'' + CAST(DATEPART(week, ObjectLastModDate) AS VARCHAR(10)),2)'
	SET @IntervalSelectSQL = @IntervalFormat + ' as [TimeSpan], FORMAT(ObjectLastModDate, ''yyyy'') as [Year], RIGHT(''00'' + CAST(DATEPART(week, ObjectLastModDate) AS VARCHAR(10)),2) as [CalendarWeek]'
	SET @IntervalGroupBy = @IntervalFormat + ', FORMAT(ObjectLastModDate, ''yyyy''), RIGHT(''00'' + CAST(DATEPART(week, ObjectLastModDate) AS VARCHAR(10)),2)'
end
IF @Interval = 'monthly'
begin
	SET @IntervalFormat  = 'FORMAT(ObjectLastModDate, ''yyyy-MM'')'
	SET @IntervalSelectSQL = @IntervalFormat + ' as [TimeSpan], FORMAT(ObjectLastModDate, ''yyyy'') as [Year], FORMAT(ObjectLastModDate, ''MM'') as [Month]'
	SET @IntervalGroupBy = @IntervalFormat + ',FORMAT(ObjectLastModDate, ''yyyy''), FORMAT(ObjectLastModDate, ''MM'')'
end
IF @Interval = 'quarterly'
begin
	SET @IntervalFormat  = 'FORMAT(ObjectLastModDate, ''yyyy'') + ''-Q'' + CONVERT(NVARCHAR(8), DATEPART(QUARTER, ObjectLastModDate))'
	SET @IntervalSelectSQL = @IntervalFormat + ' as [TimeSpan], FORMAT(ObjectLastModDate, ''yyyy'') as [Year],DATEPART(QUARTER, ObjectLastModDate) as [Quarter]'
	SET @IntervalGroupBy = @IntervalFormat + ',FORMAT(ObjectLastModDate, ''yyyy''),DATEPART(QUARTER, ObjectLastModDate)'
end
IF @Interval = 'yearly'
begin
	SET @IntervalFormat  = 'FORMAT(ObjectLastModDate, ''yyyy'')'
	SET @IntervalSelectSQL = @IntervalFormat + ' as [TimeSpan]'
	SET @IntervalGroupBy = @IntervalFormat
end
	set @ExecuteSQLstr = +
	'SELECT ObjectFromSiteID,' +
	@IntervalSelectSQL + ','

	   if @GroupByAP_SP = 1
	   begin
	   set @ExecuteSQLstr = @ExecuteSQLstr + '  ObjectUgMaster_Type,' 
	   end

	   if @GroupByObjectType = 1
	   begin
	   set @ExecuteSQLstr = @ExecuteSQLstr +  '  ObjectType,'
	   end



	--'ObjectUgMaster_Type,'+
	--'ObjectType,' +
set @ExecuteSQLstr = @ExecuteSQLstr +
	' count(*) as [ObjectCount]'+
	' from Objects_tbl' + 
	' where '

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 + ' ([ObjectFromSiteID] like ' + '''' + @ObjectFromSiteID + '''' + ') and'
		 end
	 end
	 else
	 begin
		if @ObjectFromSiteID <> ''
		 -- 05.03.2011 eingefuegt um bei '' unnoetigen select like zu vermeiden
		 begin
		  set @ExecuteSQLstr = @ExecuteSQLstr + ' ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ') and'
		 end
	 end
	
	
set @ExecuteSQLstr = @ExecuteSQLstr + 
	--' and ObjectFromSiteID = ''%''' +

	' ObjectClass=''' + @ObjectClass + '''' + 
	' group by ObjectFromSiteID,' + @IntervalGroupBy
	
	
	if @GroupByAP_SP = 1
	begin
	set @ExecuteSQLstr = @ExecuteSQLstr + ', ObjectUgMaster_Type' 
	end

	if @GroupByObjectType = 1
	begin
	set @ExecuteSQLstr = @ExecuteSQLstr + ', ObjectType' 
	end
	
	--' ObjectUgMaster_Type,' + 
	set @ExecuteSQLstr = @ExecuteSQLstr +
	--' ObjectType'+
	' order by ' + @IntervalFormat


if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@Interval1' ,  @Interval  
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end

 execute sp_executesql @ExecuteSQLstr



END
GO







--------------------------------------------------------------------------------



-- Print ('Execute Report_905_Analysis_AllSites_IR_inconsistent')
-- GO
-- Execute Report_905_Analysis_AllSites_IR_inconsistent
-- GO
-- Execute Report_203_SingleSite_RefileStatus_by_DataSets
-- Go
--  Execute Report_204_SingleSite_RefileErrorList
--  Go
-- Execute Rrt_200_ProcStatus 'tceng' , 'ProcStateCol07'
-- go
--   execute Report_201_ProcStatus_by_DataSets_Count 'tceng' , 'ProcStateCol07'
--   go

-- Execute Report_301_List_of_all_Datasets_With_Errors 'tceng' , 'ProcStateCol07'
-- GO
--   declare @DateTimeStart datetime
--   set @DateTimeStart = GetDate()
--   -- DBCC DROPCLEANBUFFERS
--
--   Print ('Report_101_Objects_Datasets_Sum')
--   -- Execute  Rrt_200_ProcStatus 'plm2' , 'ProcStateColRf'
--   Execute  Report_101_Objects_Datasets_Sum '%tc%' , ''
--
--   Execute JF_ShowTimeDiffToNow 'Rrt_200_ProcStatus Delta:', @DateTimeStart
--   go
--
-- Declare @ExecuteSQLstr nvarchar(max)
-- select @ExecuteSQLstr=Value from zzzzzDebug_tbl  where Name = '@ExecuteSQLstr'
-- Print ''
-- Print ''
-- Print '@ExecuteSQLstr:'
-- Print '---------------'
-- Print @ExecuteSQLstr

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

