Print '--------------------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [REPORTS]'
Print ' Creation      : 22.03.2006/J.Fes'
Print ' Last.Update   : 02.09.2013/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
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 'ProcStateColMc' THEN [ProcStateColMc]
                         WHEN 'ProcStateColRf' THEN [ProcStateColRf]
                    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 '-- [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

CREATE PROCEDURE Report_101_Objects_Datasets_Sum
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
Begin

  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_101_Objects_Datasets_Sum'
  DECLARE @ExecuteSQLstr nvarchar(max)
  -- 07.12.2011  with (nolock) eingefuegt
  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)'' ' +
                          '  WHEN ''AP'' ' +
                          '     THEN ''2-AP (Assembly)'' '  +
                          '     ELSE ''3-NP (No CAD.Obj)'' '  +
                          '  END                   AS [O.UGMaster_Type],' +
                        ' [ObjectType]             AS [Dataset Type],' +
                        ' COUNT(ObjectType)        AS [Dataset Count]' +
                        ' from [dbo].[Objects_tbl] with (nolock)' +
                        ' WHERE (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 zu vermeiden
     begin
      set @ExecuteSQLstr = @ExecuteSQLstr +
                          ' and ([ObjectFromSiteID] = ' + '''' + @ObjectFromSiteID + '''' + ')'
     end
   end

   /* festellung 'group by' hat keine messbaren auswirkungen auf performance */
   set @ExecuteSQLstr = @ExecuteSQLstr +
                       ' GROUP BY ' +
                       '  ObjectFromSiteID , ObjectType , ObjectUgMaster_Type, ' +
                       
                       ' CASE [ObjectFromSiteID] ' +
                            '  WHEN [ObjectOwningSiteID] ' +
                            '    THEN ''1-SO (SiteObj)'' ' +
                            '  ELSE   ''2-RO (ReplObj)'' ' +
                            '  END , ' +
                       ' CASE ObjectUgMaster_Type ' +
                            '  WHEN ''SP'' ' +
                            '    THEN ''1-SP (Single Parts)'' ' +
                            '  WHEN ''SP'' ' +
                            '    THEN ''2-AP (Assembly)'' ' +
                            '    ELSE ''3-NP (No CAD.Obj)'' '  +
                            '  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 '-- [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
       )
AS
begin
-- #LUp 01.08.2018/J.Fes  SiteOwningType      AS [S.Or.Rep Object]
--                        ObjectUgMaster_Type AS [O.UGMaster_Type] SP AP
--                        convertet into DS_AndProcessDataForCount_UDF
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  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],' +
    ' ObjectUgMaster_Type AS [O.UGMaster_Type],' +
    ' ObjectType          AS [DataSet Type],'
   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'
    ' into ' + @TempTblName + -- einfügen in temp Tabelle!!
    ' from [dbo].[DS_AndProcessDataForCount_UDF](''' + @ProcStateCol + ''',''' + @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 +
                           ' 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],' +
                        ' [ObjectUgMaster_Type],' +
                        ' [ObjectType]'
  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],' +
     '[O.UGMaster_Type],' +
     '[DataSet Type],'
     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 %], ' +

      '[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 für die Anzeige mit count Daten
    set @ExecuteSQLstr = 'Select' +
     ' [SiteID],'
     if @GroupDataDo = 1
     begin
     set @ExecuteSQLstr = @ExecuteSQLstr +
     '[S.Or.Rep Object],' +
     '[O.UGMaster_Type],' +
     '[DataSet Type],'
     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],' +
      '[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)
       )
AS
begin
 Execute Rrt_200_ProcStatus @ObjectFromSiteID, @ProcStateCol, 'Percent', 1, @ObjectClass
end
GO

--------------------------------------------------------------------------------
Print '-- [Report_201_ProcStatus_Percent_TypeGroupNo] --------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_201_ProcStatus_Percent_TypeGroupNo")
Drop Procedure Report_201_ProcStatus_Percent_TypeGroupNo
GO
CREATE PROCEDURE Report_201_ProcStatus_Percent_TypeGroupNo
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_200_ProcStatus @ObjectFromSiteID, @ProcStateCol, 'Percent', 0, @ObjectClass
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)
       )
AS
begin
 Execute Rrt_200_ProcStatus @ObjectFromSiteID, @ProcStateCol, 'Count', 1, @ObjectClass
end
GO
--------------------------------------------------------------------------------
Print '-- [Report_202_ProcStatus_Count_TypeGroupNo] ----------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_202_ProcStatus_Count_TypeGroupNo")
Drop Procedure Report_202_ProcStatus_Count_TypeGroupNo
GO
CREATE PROCEDURE Report_202_ProcStatus_Count_TypeGroupNo
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_200_ProcStatus @ObjectFromSiteID, @ProcStateCol, 'Count', 0, @ObjectClass
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)
       )

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],' +
   '  CountUDF.ObjectUgMaster_Type AS [O.UGMaster_Type],' +
   '  CountUDF.ObjectType AS [DataSet Type],' +
   '  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,' +
   '  CountUDF.ObjectUgMaster_Type,' +
   '  CountUDF.ObjectType,' +
   '  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)
       )
AS
begin
 Execute Rrt_210_ProcStatus_Count @ObjectFromSiteID, @ProcStateCol, @ObjectClass
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_DatasetVersionAnalyse] --------------------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_113_DatasetVersionAnalyse")
Drop Procedure Report_113_DatasetVersionAnalyse
GO

CREATE PROCEDURE Report_113_DatasetVersionAnalyse
       (
        @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_DatasetVersionAnalyse'
  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


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





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



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