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_ObjectClass nvarchar(32))
-- 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
RETURNS TABLE AS

Return (SELECT
             ObjectFromSiteID,
             ObjectOwningSiteID,
             ObjectUgMaster_Type,
             ObjectType,
             ObjectUgMaster_FsHitCount,
             ProcStateCol01,
             ProcStateCol02,
             ProcStateCol03,
             ProcStateCol04,
             ProcStateCol05,
             ProcStateCol06,
             ProcStateCol07,
             ProcStateCol08,
             ProcStateCol09,
             ProcStateColRf,
             JobControl_ObjectsProcessData_tbl_As.ProcessOnSiteID,
            -- JobControl_ObjectsProcessData_tbl_AS.ProcessState,
             JobControl_ObjectsProcessData_tbl_AS.ProcessResultCode,
             JobControl_ObjectsProcessData_tbl_AS.ProcessProcStateCol,
             JobControl_ObjectsProcessData_tbl_AS.ProcessResultHasWarning,
             JobControl_ObjectsProcessData_tbl_AS.Row_isLastRes
        FROM Objects_tbl with (nolock)
             Left JOIN
             (SELECT Objects_tbl_ID, ProcessOnSiteID, ProcessState, ProcessResultCode, ProcessProcStateCol, ProcessResultHasWarning, Row_isLastRes
                FROM JobControl_ObjectsProcessData_tbl with (nolock)
                WHERE ProcessProcStateCol = substring(@ProcessProcStateCol_ObjectClass,1,14)
               ) as JobControl_ObjectsProcessData_tbl_AS
             ON (Objects_tbl.ID = JobControl_ObjectsProcessData_tbl_AS.Objects_tbl_ID)
                 where Objects_tbl.ObjectClass = substring(@ProcessProcStateCol_ObjectClass,16,2) -- 'DS' 'IR' 'I'
       )
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

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

  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 1
  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 NX.Dataset)'' '  +
                          '  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 NX.Dataset)'' '  +
                            '  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_by_DataSets_MultiSiteYes] -------------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Rrt_200_ProcStatus_by_DataSets_MultiSiteYes")
Drop Procedure Rrt_200_ProcStatus_by_DataSets_MultiSiteYes
If Exists (select Name
           from sysobjects
           where Name = "Rrt_200_ProcStatus_by_DataSets_MultiSiteYes" and type = "P")
Drop Procedure Rrt_200_ProcStatus_by_DataSets_MultiSiteYes
GO

CREATE PROCEDURE Rrt_200_ProcStatus_by_DataSets_MultiSiteYes
       (
        @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


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

  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 +
    ' CASE [ObjectFromSiteID] ' +
       '  WHEN [ObjectOwningSiteID] ' +
       '    THEN ''1-SO (SiteObj)'' ' +
       '  ELSE   ''2-RO (ReplObj)'' ' +
       '  END           AS [S.Or.Rep Object], ' +

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

   set @ExecuteSQLstr = @ExecuteSQLstr +
    ' COUNT(ObjectType)        AS [DsSum], ' +        -- 'Datasets (sum)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''X%'')  THEN 1 ELSE 0 END)                                                  AS [PStatX],' +     -- 'P.Stat:X (eXcludet)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''M%'')  THEN 1 ELSE 0 END)                                                  AS [PStatM],' +     -- 'P.Stat:M (Manuell)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''N%'')  THEN 1 ELSE 0 END)                                                  AS [PStatN],' +     -- 'P.Stat:N (not.Proc)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''P%'')  THEN 1 ELSE 0 END)                                                  AS [PStatP],' +     -- 'P.Stat:P (in.Proc)'
    -- 30.09.2012 21:20 J.Fes AND [ProcessResultCode] <> '''' eingefuegt
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] <> '''') THEN 1 ELSE 0 END) AS [PStatD],' +     -- 'P.Stat:D (Done)'
    -- 26.09.2012 J.Fes angepasst WARNING werden jetzt auch den OKs hinzugezaehlt
    --  ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [ProcessResultCode] = ''0'' AND [ProcessResultHasWarning]<>1)  THEN 1 ELSE 0 END) AS [PResOk],' +     -- 'Done:OK'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] = ''0'') THEN 1 ELSE 0 END)                                   AS [PResOk],' +     -- 'Done:OK'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] not in (''0'',''640010'')) THEN 1 ELSE 0 END)                 AS [PResErr],' +    -- 'Done:Err'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] = ''0'' AND [ProcessResultHasWarning]=1)   THEN 1 ELSE 0 END) AS [PResHasW], ' +   -- 'Done:ProcessResultHasWarning'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] in (''640010'')) THEN 1 ELSE 0 END)                           AS [PResDonNoWriteAccess] ' + -- 'Done:Err:No write access'
    ' into ' + @TempTblName + -- einfügen in temp Tabelle!!
    ' from [dbo].[DS_AndProcessDataForCount_UDF](''' + @ProcStateCol + '#' + @ObjectClass + ''')' +
    ' WHERE ([Row_isLastRes]=1 or [Row_isLastRes] is 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

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

  if @GroupDataDo = 1
  begin
   set @ExecuteSQLstr = @ExecuteSQLstr + ',' +
                       ' CASE [ObjectFromSiteID] ' +
                           '  WHEN [ObjectOwningSiteID] ' +
                           '    THEN ''1-SO (SiteObj)'' ' +
                           '  ELSE   ''2-RO (ReplObj)'' ' +
                           '  END , ' +
                       '  CASE [ObjectUgMaster_Type] ' +
                           '  WHEN ''SP'' ' +
                           '     THEN ''1-SP (Single Parts)'' ' +
                           '  WHEN ''AP'' ' +
                           '     THEN ''2-AP (Assembly)'' ' +
                           '     ELSE ''3-NP (No NX.Dataset)'' ' +
                           '  END , ' +
                           '  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 die Prozentzahlen für Done:OK + Done:Warning + Done:Err + Done:NoWriAccess werden jetzt basierend auf den [PStatD P.Stat:D (Done)] 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 +
     '[DsSum] AS [Datasets:(sum)],' +

      '[PStatN]               AS [P.Stat:N],' +
      'Str((100.00*[PStatN]/[DsSum]),6,2) + ''%'' AS [P.Stat:N %] ,' +
      'cast([PStatX] as varchar)     + '' ('' + Str((100.00*[PStatX]/[DsSum]),6,2) + ''%)''     AS [P.Stat:X(eXcludet)],' +

      '[PStatM]              AS [P.Stat:M] ,' +
      'Str((100.00*[PStatM]/[DsSum]),6,2) + ''%'' AS [P.Stat:M %] ,' +

      '[PStatP]              AS [P.Stat:P] ,' +
      'Str((100.00*[PStatP]/[DsSum]),6,2) + ''%'' AS [P.Stat:P %] ,' +

      '[PStatD]              AS [P.Stat:D] ,' +
      'Str((100.00*[PStatD]/[DsSum]),6,2) + ''%'' AS [P.Stat:D %] ,' +

      '[PResOk]              AS [Done:OK] ,' +
      'Str((100.00*[PResOk]/[DsSum]),6,2) + ''%'' AS [Done:OK %] ,' +

      '[PResErr]              AS [Done:ERR] ,' +
      'Str((100.00*[PResErr]/[DsSum]),6,2) + ''%'' AS [Done:ERR %] ,' +

      '[PResHasW]            AS [Done:WRN] ,' +
      'Str((100.00*[PResHasW]/[DsSum]),6,2) + ''%'' AS [Done:WRN %] ,' +

      '[PResDonNoWriteAccess]              AS [Done:NoWriAcs] ,' +
      'Str((100.00*[PResDonNoWriteAccess]/[DsSum]),6,2) + ''%'' AS [Done:NoWriAcs %]' +

    '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 [Datasets:(sum)],' +
      '[PStatN]     AS [P.Stat:N (Not.Proc)],' +
      '[PStatX]     AS [P.Stat:X (eXcludet)],' +
      '[PStatM]     AS [P.Stat:M (Manuell)],' +
      '[PStatP]     AS [P.Stat:P (in.Proc)],' +
      '[PStatD]     AS [P.Stat:D (Done)],' +
      '[PResOk]     AS [Done:OK],' +
      '[PResErr]    AS [Done:ERR],' +
      '[PResHasW]   AS [Done:WRN],' +
      '[PResDonNoWriteAccess] AS [Done:NoWriAccess]' +
      'from ' + @TempTblName
    end
   execute sp_executesql @ExecuteSQLstr
End
GO
--------------------------------------------------------------------------------
Print '-- [Report_203_ProcStatus_by_DataSets_MultiSiteYes_Percent] --------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_203_ProcStatus_by_DataSets_MultiSiteYes_Percent")
Drop Procedure Report_203_ProcStatus_by_DataSets_MultiSiteYes_Percent
GO
CREATE PROCEDURE Report_203_ProcStatus_by_DataSets_MultiSiteYes_Percent
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_200_ProcStatus_by_DataSets_MultiSiteYes @ObjectFromSiteID, @ProcStateCol, 'Percent', 1, @ObjectClass
end
GO

--------------------------------------------------------------------------------
Print '-- [Report_203_ProcStatus_by_DataSets_MultiSiteYes_Percent_TypeGroupNo] --------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_203_ProcStatus_by_DataSets_MultiSiteYes_Percent_TypeGroupNo")
Drop Procedure Report_203_ProcStatus_by_DataSets_MultiSiteYes_Percent_TypeGroupNo
GO
CREATE PROCEDURE Report_203_ProcStatus_by_DataSets_MultiSiteYes_Percent_TypeGroupNo
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_200_ProcStatus_by_DataSets_MultiSiteYes @ObjectFromSiteID, @ProcStateCol, 'Percent', 0, @ObjectClass
end
GO
--------------------------------------------------------------------------------
Print '-- [Report_204_ProcStatus_by_DataSets_MultiSiteYes_Count] ----------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_204_ProcStatus_by_DataSets_MultiSiteYes_Count")
Drop Procedure Report_204_ProcStatus_by_DataSets_MultiSiteYes_Count
GO
CREATE PROCEDURE Report_204_ProcStatus_by_DataSets_MultiSiteYes_Count
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_200_ProcStatus_by_DataSets_MultiSiteYes @ObjectFromSiteID, @ProcStateCol, 'Count', 1, @ObjectClass
end
GO
--------------------------------------------------------------------------------
Print '-- [Report_204_ProcStatus_by_DataSets_MultiSiteYes_Count_TypeGroupNo] ----------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_204_ProcStatus_by_DataSets_MultiSiteYes_Count_TypeGroupNo")
Drop Procedure Report_204_ProcStatus_by_DataSets_MultiSiteYes_Count_TypeGroupNo
GO
CREATE PROCEDURE Report_204_ProcStatus_by_DataSets_MultiSiteYes_Count_TypeGroupNo
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_200_ProcStatus_by_DataSets_MultiSiteYes @ObjectFromSiteID, @ProcStateCol, 'Count', 0, @ObjectClass
end
GO
--------------------------------------------------------------------------------
Print '-- [Rrt_200_ProcStatus_by_DataSets_MultiSiteNo] --------------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Rrt_200_ProcStatus_by_DataSets_MultiSiteNo")
Drop Procedure Rrt_200_ProcStatus_by_DataSets_MultiSiteNo
GO

CREATE PROCEDURE Rrt_200_ProcStatus_by_DataSets_MultiSiteNo
       (
        @ObjectFromSiteID nvarchar(32),
        @ProcStateCol nvarchar(14),
        @ViewFormat nvarchar(14), -- Valid Values "CountPercent" 'Count'
        @GroupDataDo bit,  -- 29.09.2012 New J.Fes
        @ObjectClass nvarchar(10)
       )
AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 1

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

  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @TempTblName   nvarchar(32)

  -- set @TempTblName = 'tempdb..##temp_tbl_Rrt200'
  set @TempTblName = 'tempdb..##temp_tbl_' + @MyProcName
  --  + '_' + @MyProcName
  IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

  set @ExecuteSQLstr = 'SELECT '  +
      ' CASE [ObjectUgMaster_Type] ' +
         '  WHEN ''SP'' ' +
         '     THEN ''1-SP (Single Parts)'' ' +
         '  WHEN ''AP'' ' +
         '     THEN ''2-AP (Assembly)'' '  +
         '     ELSE ''3-NP (No NX.Dataset)'' '  +
         '  END                  AS [O.UGMaster_Type],'
  if @GroupDataDo = 1
   begin
    set @ExecuteSQLstr = @ExecuteSQLstr +
      ' ObjectType               AS [DataSet Type], '
   end

  set @ExecuteSQLstr = @ExecuteSQLstr +

    ' COUNT(ObjectType)        AS [DsSum], ' +        -- 'Datasets (sum)'
    ' Sum(CASE WHEN ([ObjectUgMaster_FsHitCount] > 0)  THEN 1 ELSE 0 END)   AS [DsSum_FsHc],' +     -- 'Datasets (sum) with hit.counts'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''X%'')  THEN 1 ELSE 0 END)                                                 AS [PStatX],' +     -- 'P.Stat:X (eXcludet)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''X%'' AND [ObjectUgMaster_FsHitCount] > 0)  THEN 1 ELSE 0 END)             AS [PStatX_FsHc],' + -- 'P.Stat:X (eXcludet)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''M%'')  THEN 1 ELSE 0 END)                                                 AS [PStatM],' +     -- 'P.Stat:M (Manuell)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''M%'' AND [ObjectUgMaster_FsHitCount] > 0)  THEN 1 ELSE 0 END)             AS [PStatM_FsHc],' +     -- 'P.Stat:M (Manuell)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''N%'')  THEN 1 ELSE 0 END)                                                 AS [PStatN],' +     -- 'P.Stat:N (not.Proc)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''N%'' AND [ObjectUgMaster_FsHitCount] > 0)  THEN 1 ELSE 0 END)             AS [PStatN_FsHc],' +     -- 'P.Stat:N (not.Proc)'

    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''P%'')  THEN 1 ELSE 0 END)                                                 AS [PStatP],' +          -- 'P.Stat:P (in.Proc)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''P%'' AND [ObjectUgMaster_FsHitCount] > 0)  THEN 1 ELSE 0 END)             AS [PStatP_FsHc],' +     -- 'P.Stat:P (in.Proc)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] <> '''') THEN 1 ELSE 0 END)                                                                     AS [PStatD],' +          -- 'P.Stat:D (Done)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] <> '''' AND [ObjectUgMaster_FsHitCount] > 0)  THEN 1 ELSE 0 END)                                AS [PStatD_FsHc],' +     -- 'P.Stat:D (Done)'

    -- 26.09.2012 J.Fes angepasst WARNING werden jetzt auch den OKs hinzugezaehlt
    -- ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [ProcessResultCode] = ''0'' AND [ProcessResultHasWarning]<>1)   THEN 1 ELSE 0 END) AS [PResOk],' +       -- 'Done:OK'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] = ''0'' )                                   THEN 1 ELSE 0 END)                                  AS [PResOk],' +       -- 'Done:OK'

    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] = ''0'' AND [ObjectUgMaster_FsHitCount]>0)  THEN 1 ELSE 0 END)                                  AS [PResOk_FsHc],' +  -- 'Done:OK'

    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] = ''0'' AND [ProcessResultHasWarning]=1)    THEN 1 ELSE 0 END)                                  AS [PResHasW], ' +        -- 'Done:ProcessResultHasWarning'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] = ''0'' AND [ProcessResultHasWarning]=1 AND [ObjectUgMaster_FsHitCount] > 0) THEN 1 ELSE 0 END) AS [PResHasW_FsHc], ' +   -- 'Done:ProcessResultHasWarning'

    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] not in (''0'',''640010'')) THEN 1 ELSE 0 END)                                                   AS [PResErr],' +    -- 'Done:Err'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] not in (''0'',''640010'') AND [ObjectUgMaster_FsHitCount] > 0) THEN 1 ELSE 0 END)               AS [PResErr_FsHc],' +    -- 'Done:Err'

    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [Row_isLastRes]=1 AND [ProcessResultCode] in (''640010'')) THEN 1 ELSE 0 END)                                                   AS [PResDonNoWriteAccess] ' + -- 'Done:Err:No write access'


    ' into ' + @TempTblName + -- einfügen in temp Tabelle!!
    -- ' from [dbo].[DS_AndProcessDataForCount_UDF](''' + @ProcStateCol + ''')' +
    ' from [dbo].[DS_AndProcessDataForCount_UDF](''' + @ProcStateCol + '#' + @ObjectClass + ''')' +
    ' WHERE ([Row_isLastRes]=1 or [Row_isLastRes] is 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 ' +
                    '  CASE [ObjectUgMaster_Type] ' +
                        '  WHEN ''SP'' ' +
                        '     THEN ''1-SP (Single Parts)'' ' +
                        '  WHEN ''AP'' ' +
                        '     THEN ''2-AP (Assembly)'' ' +
                        '     ELSE ''3-NP (No NX.Dataset)'' ' +
                        '  END '

  if @GroupDataDo = 1
  begin
   /* festellung group by hat keine messbaren auswirkungen auf performance */
   set @ExecuteSQLstr = @ExecuteSQLstr +
                           ', 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 die Prozentzahlen für Done:OK + Done:Warning + Done:Err + Done:NoWriAccess werden jetzt basierend auf den [PStatD P.Stat:D (Done)] berechnet
    set @ExecuteSQLstr = 'Select' +
     ' [O.UGMaster_Type],'

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

     set @ExecuteSQLstr = @ExecuteSQLstr +
     ' [DsSum] AS [Datasets:(sum)],' +
     ' [PStatN] AS [P:N Count],' +
     '                                        Str((100.00*[PStatN]/[DsSum]),6,2) + ''%''        AS [P:N %],' +
     ' cast([PStatX] as varchar)     + '' ('' + Str((100.00*[PStatX]/[DsSum]),6,2) + ''%)''     AS [P.Stat:X (eXcludet)],' +
     ' cast([PStatM] as varchar)     + '' ('' + Str((100.00*[PStatM]/[DsSum]),6,2) + ''%)''     AS [P.Stat:M (Manuell)],' +
     ' cast([PStatP] as varchar)     + '' ('' + Str((100.00*[PStatP]/[DsSum]),6,2) + ''%)''     AS [P.Stat:P (in.Proc)],' +
     ' cast([PStatD] as varchar)     + '' ('' + Str((100.00*[PStatD]/[DsSum]),6,2) + ''%)''     AS [P.Stat:D (Done)],' +
     ' cast([PResOk] as varchar)     + '' ('' + Str((100.00*[PResOk]/[DsSum]),6,2) + ''%)''     AS [Done:OK],' +
     ' cast([PResErr] as varchar)    + '' ('' + Str((100.00*[PResErr]/[DsSum]),6,2) + ''%)''    AS [Done:ERR],' +
     ' cast([PResHasW] as varchar)   + '' ('' + Str((100.00*[PResHasW]/[DsSum]),6,2) + ''%)''   AS [Done:WRN],' +
     ' cast([PResDonNoWriteAccess] as varchar) + '' ('' + Str((100.0*[PResDonNoWriteAccess]/[DsSum]),6,2) + ''%)'' AS [Done:NoWriAccess]' +
     ' from ' + @TempTblName
   end
  else
   begin
    -- Select für die Anzeige mit count Daten
    set @ExecuteSQLstr = 'Select' +
      ' [O.UGMaster_Type],'

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

     set @ExecuteSQLstr = @ExecuteSQLstr +
      ' [DsSum]                AS [Datasets:(sum)],' +
      ' [DsSum_FsHc]           AS [Datasets.FsHc:(sum)],' +
      ' [PStatN]               AS [P:N (Not.Proc)],' +
      ' [PStatN_FsHc]          AS [P:N.FsHc (Not.Proc)],' +
      ' [PStatX]               AS [P:X (eXcludet)],' +
      ' [PStatX_FsHc]          AS [P:X.FsHc (eXcludet)],' +
      ' [PStatM]               AS [P:M (Manuell)],' +
      ' [PStatM_FsHc]          AS [P:M.FsHc (Manuell)],' +
      ' [PStatP]               AS [P:P (in.Proc)],' +
      ' [PStatP_FsHc]          AS [P:P.FsHc(in.Proc)],' +
      ' [PStatD]               AS [D (Done sum)],' +
      ' [PStatD_FsHc]          AS [D.FsHc (Done sum)],' +
      ' [PResOk]               AS [D:OK],' +
      ' [PResOk_FsHc]          AS [D:OK.FsHc],' +
      ' [PResHasW]             AS [D:WRN],' +
      ' [PResHasW_FsHc]        AS [D:WRN.FsHc],' +
      ' [PResErr]              AS [D:ERR],' +
      ' [PResErr_FsHc]         AS [D:ERR.FsHc],' +
      ' [PResDonNoWriteAccess] AS [D:NoWriAccess]' +
      ' from ' + @TempTblName
    end
    execute sp_executesql @ExecuteSQLstr

End
GO

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

--------------------------------------------------------------------------------
Print '-- [Report_202_ProcStatus_by_DataSets_MultiSiteNo_Percent] ---------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_202_ProcStatus_by_DataSets_MultiSiteNo_Percent")
Drop Procedure Report_202_ProcStatus_by_DataSets_MultiSiteNo_Percent
GO
CREATE PROCEDURE Report_202_ProcStatus_by_DataSets_MultiSiteNo_Percent
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_200_ProcStatus_by_DataSets_MultiSiteNo @ObjectFromSiteID, @ProcStateCol, 'Percent', 1, @ObjectClass
end
GO


Print '-- [Report_201_ProcStatus_by_DataSets_MultiSiteNo_Count_TypeGroupNo] -----------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_201_ProcStatus_by_DataSets_MultiSiteNo_Count_TypeGroupNo")
Drop Procedure Report_201_ProcStatus_by_DataSets_MultiSiteNo_Count_TypeGroupNo
-- new 29.09.2012
GO
CREATE PROCEDURE Report_201_ProcStatus_by_DataSets_MultiSiteNo_Count_TypeGroupNo
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )
AS
begin
 Execute Rrt_200_ProcStatus_by_DataSets_MultiSiteNo @ObjectFromSiteID, @ProcStateCol, 'Count', 0, @ObjectClass
end
GO

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


  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 = 1
  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_402_PFM_without_PFMTemplate new 16.11.2010] ----------------------------'
-- GO
-- CREATE PROCEDURE Report_402_PFM_without_PFMTemplate
--        (
--         @ObjectFromSiteID nvarchar(32) ,
--         @ProcStateCol nvarchar(14)
--        )
--
-- AS
-- begin
--   -- selectiert alle PFM die ohne PFM Template in der DB abgelegt sind
--   DECLARE @DebugPrintOn bit
--   set @DebugPrintOn = 1
--   DECLARE @MyProcName nvarchar(32)
--   set @MyProcName='Report_402_PFM_without_PFMTemplate'
--   DECLARE @ExecuteSQLstr nvarchar(max)
--   DECLARE @TempTblName   nvarchar(32)
--   set @TempTblName = '##temp_tbl'
--
--   SELECT DISTINCT
--     dbo.Objects_tbl.ObjectName AS ItemRev,
--     dbo.Objects_tbl.ObjectFromSiteID,
--     dbo.Objects_tbl.ObjectOwningSiteID,
--     dbo.Objects_tbl.ObjectOwner,
--     dbo.Objects_tbl.ObjectGroup,
--     dbo.Objects_tbl.ObjectCreateDate,
--     dbo.Objects_tbl.ObjectLastModUser,
--     dbo.Objects_tbl.ObjectLastModDate,
--     dbo.Objects_tbl.ObjectUgMaster_PartFamMemName AS [PF.Name],
--     dbo.Objects_tbl.ObjectCliName
--   FROM
--     dbo.Objects_tbl
--   WHERE
--     dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID AND
--     dbo.Objects_tbl.ObjectUgMaster_PartFamMemName NOT IN ('','~') AND
--     dbo.Objects_tbl.ObjectUgMaster_PartFamMemName +'/' NOT IN
--     (SELECT
--       SUBSTRING(dbo.Objects_tbl.ObjectName, 1, CHARINDEX('/', dbo.Objects_tbl.ObjectName)) AS [PfMName]
--       FROM dbo.Objects_tbl
--       WHERE dbo.Objects_tbl.Objectclass = 'IR' and
--             dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID)
--   -- Doku SUBSTRING(dbo.Objects_tbl.ObjectName, 1, CHARINDEX('/', dbo.Objects_tbl.ObjectName) mach aus 4711/A --> 4711
-- 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_by_DataSets_MultiSiteYes 'tceng' , 'ProcStateCol07'
-- go
--   execute Report_203_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_by_DataSets_MultiSiteYes 'plm2' , 'ProcStateColRf'
--   Execute  Report_101_Objects_Datasets_Sum '%tc%' , ''
--
--   Execute JF_ShowTimeDiffToNow 'Rrt_200_ProcStatus_by_DataSets_MultiSiteYes 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
