Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [REPORTS]'
Print ' Creation Date : 22.03.2006'
Print ' Autor         : J.Feuerstein'

use [#DB_N#]

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

-- 26.02.2010 16:52 #temp_tbl loeschen da sonst bei abbruch die Reports nicht importiert werden koennen!!
IF (OBJECT_ID ('tempdb..##temp_tbl', N'U') IS NOT NULL) exec ('drop table ##temp_tbl')
Go


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
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))
-- UDF Optimiert für Statistik Abfragen
-- 07.12.2011 with (nolock) eingefuegt
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 = @ProcessProcStateCol) as JobControl_ObjectsProcessData_tbl_AS
             ON (Objects_tbl.ID = JobControl_ObjectsProcessData_tbl_AS.Objects_tbl_ID)
                 where Objects_tbl.ObjectClass = 'DS'
       )
Go

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

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

CREATE PROCEDURE dbo.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 '-'
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

--------------------------------------------------------------------------------
-- Alte Reports aus der Datenbank löschen !!
Declare @ReportToDelete nvarchar(4000)
Declare @ReportToDeleteExce nvarchar(4000)
While (select Top(1) Name from sysobjects where Name like "Report[_]%" and type = "P")<>""
 begin
  select Top(1) @ReportToDelete=Name from sysobjects where Name like "Report[_]%" and type = "P"
  -- Print '--'
  -- Print ' Delete Report:' + @ReportToDelete
  -- Print '--'
  set @ReportToDeleteExce="Drop PROCEDURE " + @ReportToDelete
  execute (@ReportToDeleteExce)
 end
GO
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Print '-'
Print '-- [Report_101_Objects_Datasets_Sum] ------------------------------------'
GO
CREATE PROCEDURE Report_101_Objects_Datasets_Sum
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(32)
       )
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)'' ' +
                           '     ELSE ''2-AP (Assembly)'' '  +
                           '  END                  As [O.UGMaster_Type],' +
                        ' [ObjectType]             As [Dataset Type],' +
                        ' COUNT(ObjectType)        AS [Dataset Count]' +
                        ' from [dbo].[Objects_tbl] with (nolock)' +
                        ' WHERE (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 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)'' ' +
                           '    ELSE ''2-AP (Assembly)'' ' +
                           '  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 '-'
Print '-- [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'
       )
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)
--   IF (OBJECT_ID ('tempdb..' + @TempTblName, N'U') IS NOT NULL) exec ('drop table ' + 'tempdb..' + @TempTblName)
  set @TempTblName = 'tempdb..##temp_tbl'
  IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

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

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

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

    ' ObjectType               As [DataSet Type], ' +
    ' COUNT(ObjectType)        AS [DsSum], ' +        -- 'Datasets (sum)'

--    ' Sum(CASE WHEN (['+ @ProcStateCol + '] not in (''X'',''N'',''P'',''D'',''M''))  THEN 1 ELSE 0 END) As [PStatOther],' + -- 'P.Stat:? (other)'
    ' 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)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'')  THEN 1 ELSE 0 END)                                                  As [PStatD],' +     -- 'P.Stat:D (Done)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [ProcessResultCode] = ''0'')  THEN 1 ELSE 0 END)                  As [PResOk],' +     -- 'Done:OK'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [ProcessResultHasWarning] = 1 AND [ProcessResultCode] = ''0'') THEN 1 ELSE 0 END) As [PResHasW], ' +   -- 'Done:ProcessResultHasWarning'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [ProcessResultCode] not in (''0'',''640010'')) THEN 1 ELSE 0 END) As [PResErr],' +    -- 'Done:Err'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' 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 + ''')' +
    ' 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], ' +
                       ' CASE [ObjectFromSiteID] ' +
                           '  WHEN [ObjectOwningSiteID] ' +
                           '    THEN ''1-SO (SiteObj)'' ' +
                           '  ELSE   ''2-RO (ReplObj)'' ' +
                           '  END , ' +
                       '  CASE [ObjectUgMaster_Type] ' +
                           '  WHEN ''SP'' ' +
                           '     THEN ''1-SP (Single Parts)'' ' +
                           '     ELSE ''2-AP (Assembly)'' ' +
                           '  END , ' +
                           '  ObjectType '
  ------------ ## 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
--   select * from ##temp_tbl ;
  -- reihenfolge angepasst 27.10.2009 14:21
  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
    Select
     [SiteID] ,
     [S.Or.Rep Object], -- As [S.Or.Rep Object],  -- as [Site.Replica] ,
     [O.UGMaster_Type] ,
     [DataSet Type] ,
     [DsSum] As [Datasets:(sum)] ,

      -- cast([PStatN] as varchar)     + ' (' + Str((100.00*[PStatN]/[DsSum]),6,2) + '%)'     As [P.Stat:N (not.Proc)] ,
      [PStatN]               As [P.Stat:N] ,
      Str((100.00*[PStatN]/[DsSum]),6,2) + '%' As [P.Stat:N %] ,
--      cast([PStatOther] as varchar) + ' (' + Str((100.00*[PStatOther]/[DsSum]),6,2) + '%)' As [P.Stat:(Other)],
      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)] ,
      [PStatM]              As [P.Stat:M] ,
      Str((100.00*[PStatM]/[DsSum]),6,2) + '%' As [P.Stat:M %] ,

      -- cast([PStatP] as varchar)     + ' (' + Str((100.00*[PStatP]/[DsSum]),6,2) + '%)'     As [P.Stat:P (in.Proc)],
      [PStatP]              As [P.Stat:P] ,
      Str((100.00*[PStatP]/[DsSum]),6,2) + '%' As [P.Stat:P %] ,

      -- cast([PStatD] as varchar)     + ' (' + Str((100.00*[PStatD]/[DsSum]),6,2) + '%)'     As [P.Stat:D (Done)],
      [PStatD]              As [P.Stat:D] ,
      Str((100.00*[PStatD]/[DsSum]),6,2) + '%' As [P.Stat:D %] ,

      -- cast([PResOk] as varchar) + CASE WHEN ([PStatD] <> ''0'') THEN ' (' + Str((100.00*[PResOk]/[PStatD]),6,2) + '%)'  ELSE 0 END
      -- orig cast([PResOk] as varchar)     + ' (' + Str((100.00*[PResOk]/[DsSum]),6,2) + '%)'    As [Done:OK] ,
      -- cast([PResOk] as varchar) CASE WHEN ([PStatD] <> ''0'') THEN ' (' + Str((100.00*[PResOk]/[PStatD]),6,2) + '%)'  ELSE '~' END  As [Done:OK] ,

      -- cast([PResOk] as varchar)     + ' (' + Str((100.00*[PResOk]/[DsSum]),6,2) + '%)'    As [Done:OK] ,
      [PResOk]              As [Res.D.OK] ,
      Str((100.00*[PResOk]/[DsSum]),6,2) + '%' As [Res.D.OK %] ,

      -- cast([PResHasW] as varchar)   + ' (' + Str((100.00*[PResHasW]/[DsSum]),6,2) + '%)'  As [D.W] ,
      [PResHasW]            As [Res.D.W] ,
      Str((100.00*[PResHasW]/[DsSum]),6,2) + '%' As [Res.D.W %] ,

      -- cast([PResErr] as varchar)    + ' (' + Str((100.00*[PResErr]/[DsSum]),6,2) + '%)'   As [Done:Err] ,
      [PResErr]              As [Res.D.Err] ,
      Str((100.00*[PResErr]/[DsSum]),6,2) + '%' As [Res.D.Err %] ,

      -- cast([PResDonNoWriteAccess] as varchar)    + ' (' + Str((100.0*[PResDonNoWriteAccess]/[DsSum]),6,2) + '%)'  As [D.NoWriAccess]
      [PResDonNoWriteAccess]              As [Res.D.NoWriAcs] ,
      Str((100.00*[PResDonNoWriteAccess]/[DsSum]),6,2) + '%' As [Res.D.NoWriAcs %]

    from ##temp_tbl
   end
  else
   begin
    -- Select für die Anzeige mit count Daten
    Select
     [SiteID] ,
     [S.Or.Rep Object],
     [O.UGMaster_Type] ,
     [DataSet Type] ,
     [DsSum]      As [Datasets:(sum)]  ,
     [PStatN]     As [P.Stat:N (Not.Proc)] ,
--     [PStatOther] As [P.Stat:..(Other)],
     [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 [Res:D.OK] ,
     [PResHasW]   As [Res:Warning] ,
     [PResErr]    As [Res:D.Err] ,
     [PResDonNoWriteAccess] As [Done:NoWriAccess]
    from ##temp_tbl
   end

End
GO
--------------------------------------------------------------------------------
Print '-'
Print '-- [Report_203_ProcStatus_by_DataSets_MultiSiteYes_Percent] -------------'
GO

CREATE PROCEDURE Report_203_ProcStatus_by_DataSets_MultiSiteYes_Percent
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )
AS
begin
 Print '-'
 Execute Rrt_200_ProcStatus_by_DataSets_MultiSiteYes @ObjectFromSiteID , @ProcStateCol, 'Percent'
end
GO
--------------------------------------------------------------------------------
Print '-'
Print '-- [Report_212_ProcStatus_by_DataSets_MultiSiteYes_Count] ---------------'
GO
CREATE PROCEDURE Report_212_ProcStatus_by_DataSets_MultiSiteYes_Count
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )
AS
begin
 Print '-'
 Execute Rrt_200_ProcStatus_by_DataSets_MultiSiteYes @ObjectFromSiteID , @ProcStateCol , 'Count'
end
GO
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

Print '-'
Print '-- [Rrt_200_ProcStatus_by_DataSets_MultiSiteNo] -------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Rrt_200_ProcStatus_by_DataSets_MultiSiteNo" and type = "P")
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'
       )
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'
  --  + '_' + @MyProcName
  IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

  set @ExecuteSQLstr =
   'SELECT ' +
 --  'ObjectFromSiteID          AS [SiteID] ,' +
    ' CASE [ObjectUgMaster_Type] ' +
       '  WHEN ''SP'' ' +
       '     THEN ''1-SP (Single Parts)'' ' +
       '     ELSE ''2-AP (Assembly)'' '  +
       '  END                  As [O.UGMaster_Type], ' +
    ' ObjectType               As [DataSet Type], ' +
    ' 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 + '] not in (''X'',''N'',''P'',''D'',''M''))  THEN 1 ELSE 0 END) As [PStatOther],' + -- 'P.Stat:? (other)'
--     ' Sum(CASE WHEN (['+ @ProcStateCol + ']=''X'')  THEN 1 ELSE 0 END)                                                  As [PStatX],' +     -- 'P.Stat:X (eXcludet)'
--     ' Sum(CASE WHEN (['+ @ProcStateCol + ']=''M'')  THEN 1 ELSE 0 END)                                                  As [PStatM],' +     -- 'P.Stat:M (Manuell)'
--     ' Sum(CASE WHEN (['+ @ProcStateCol + ']=''N'')  THEN 1 ELSE 0 END)                                                  As [PStatN],' +     -- 'P.Stat:N (not.Proc)'
--     ' Sum(CASE WHEN (['+ @ProcStateCol + ']=''P'')  THEN 1 ELSE 0 END)                                                  As [PStatP],' +     -- 'P.Stat:P (in.Proc)'
--     ' Sum(CASE WHEN (['+ @ProcStateCol + ']=''D'')  THEN 1 ELSE 0 END)                                                  As [PStatD],' +     -- 'P.Stat:D (Done)'
--     ' Sum(CASE WHEN (['+ @ProcStateCol + ']=''D'' AND [ProcessResultCode] = ''0'')                   THEN 1 ELSE 0 END) As [PResOk], ' +    -- 'Done:OK'
--     ' Sum(CASE WHEN (['+ @ProcStateCol + ']=''D'' AND [ProcessResultHasWarning] = 1)                 THEN 1 ELSE 0 END) As [PResHasW], ' +   -- 'Done:ProcessResultHasWarning'
--     ' Sum(CASE WHEN (['+ @ProcStateCol + ']=''D'' AND [ProcessResultCode] not in (''0'',''640010'')) THEN 1 ELSE 0 END) As [PResErr],' +    -- 'Done:Err'
--     ' Sum(CASE WHEN (['+ @ProcStateCol + ']=''D'' AND [ProcessResultCode]     in (''640010''))       THEN 1 ELSE 0 END) As [PResDonNoWriteAccess] ' + -- 'Done:Err:No write access'

--   ' Sum(CASE WHEN (['+ @ProcStateCol + '] not in (''X'',''N'',''P'',''D'',''M''))  THEN 1 ELSE 0 END) As [PStatOther],' + -- 'P.Stat:? (other)'

    ' 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%'')  THEN 1 ELSE 0 END)                                                 As [PStatD],' +     -- 'P.Stat:D (Done)'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [ObjectUgMaster_FsHitCount] > 0)  THEN 1 ELSE 0 END)             As [PStatD_FsHc],' +     -- 'P.Stat:D (Done)'

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

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

    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' AND [ProcessResultCode] not in (''0'',''640010'')) THEN 1 ELSE 0 END) As [PResErr],' +    -- 'Done:Err'
    ' Sum(CASE WHEN (['+ @ProcStateCol + '] like ''D%'' 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 [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 + ''')' +
    ' 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], ' +
                       '  CASE [ObjectUgMaster_Type] ' +
                           '  WHEN ''SP'' ' +
                           '     THEN ''1-SP (Single Parts)'' ' +
                           '     ELSE ''2-AP (Assembly)'' ' +
                           '  END , ' +
                           '  ObjectType '
  ------------ ## 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
  -- reihenfolge angepasst 27.10.2009 14:21
  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
    Select
   --  [SiteID] ,
   --  [S.Or.Rep Object], -- As [S.Or.Rep Object],  -- as [Site.Replica] ,
     [O.UGMaster_Type] ,
     [DataSet Type] ,
     [DsSum] As [Datasets:(sum)] ,
--       cast([PStatN] as varchar)     + ' (' + Str((100.00*[PStatN]/[DsSum]),6,2) + '%)'     As [P.Stat:N (not.Proc)] ,
      [PStatN] As [P:N Count] ,
      Str((100.00*[PStatN]/[DsSum]),6,2) + '%' As [P:N %] ,
--       cast([PStatOther] as varchar) + ' (' + Str((100.00*[PStatOther]/[DsSum]),6,2) + '%)' As [P.Stat:..(Other)],
      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([PResHasW] as varchar)   + ' (' + Str((100.00*[PResHasW]/[DsSum]),6,2) + '%)'   As [Done:Warning] ,
      cast([PResErr] as varchar)    + ' (' + Str((100.00*[PResErr]/[DsSum]),6,2) + '%)'    As [Done:Err] ,
      cast([PResDonNoWriteAccess] as varchar)    + ' (' + Str((100.0*[PResDonNoWriteAccess]/[DsSum]),6,2) + '%)' As [Done:NoWriAccess]
    from ##temp_tbl_Rrt200
                 --   Rrt_200_ProcStatus_by_DataSets_MultiSiteNo'
   end
  else
   begin
    -- Select für die Anzeige mit count Daten
    Select
    -- [SiteID] ,
    -- [S.Or.Rep Object], -- As [S.Or.Rep Object],  -- as [Site.Replica] ,
     [O.UGMaster_Type],
     [DataSet Type],
     [DsSum]       As [Datasets:(sum)],
     [DsSum_FsHc]  As [Datasets.FsHc:(sum)],
     [PStatN]      As [P:N (Not.Proc)],
     [PStatN_FsHc] As [P:N.FsHc (Not.Proc)],
--      [PStatOther] As [P.Stat:..(Other)],
     [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 ##temp_tbl_Rrt200
   end
  -- 'Count'

End
GO
--------------------------------------------------------------------------------
Print '-'
Print '-- [Report_202_ProcStatus_by_DataSets_MultiSiteNo_Percent] --------------------------'
GO
CREATE PROCEDURE Report_202_ProcStatus_by_DataSets_MultiSiteNo_Percent
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )
AS
begin
 Print '-'
 Execute Rrt_200_ProcStatus_by_DataSets_MultiSiteNo @ObjectFromSiteID , @ProcStateCol, 'Percent'
end
GO
--------------------------------------------------------------------------------
Print '-'
Print '-- [Report_201_ProcStatus_by_DataSets_MultiSiteNo_Count] ----------------------------'
GO
CREATE PROCEDURE Report_201_ProcStatus_by_DataSets_MultiSiteNo_Count
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )
AS
begin
 Print '-'
 Execute Rrt_200_ProcStatus_by_DataSets_MultiSiteNo @ObjectFromSiteID , @ProcStateCol , 'Count'
end
GO

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Print '-'
Print '-- [Report_301_List_of_all_Datasets_With_Errors] ----------------------------------------'
GO
CREATE PROCEDURE Report_301_List_of_all_Datasets_With_Errors
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )

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

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 1
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_301_List_of_all_Datasets_With_Errors'
  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)

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

                        ' CASE [ObjectFromSiteID] ' +
                           ' WHEN [ObjectOwningSiteID] ' +
                           '   THEN ''1-SO (SiteObj)'' ' +
                           ' ELSE   ''2-RO (ReplObj)'' ' +
                           ' END                 AS [0.SiteOwing.SorR], ' +

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

                           ' [IrName]            AS [O.IR.ID],' +
                           ' [ObjectName]        AS [O.Name],' +
                           ' [ObjectType]        AS [O.Type],' +
                           ' [ObjectUgMaster_APLevels] As [O.AP.LevN],' +
                           ' [ObjectUgMaster_CompCount] As [O.AP.CompN],' +

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

                           ' [ProcessResultCode] AS [P.Result.Code],' +
                           ' [ProcessResultMsg]  AS [P.Result.Message]' +
                           ' from [dbo].[DS_AndProcessData_UDF]('  + '''' + @ProcStateCol + '''' + ')' +

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

  if @DebugPrintOn = 1
  begin

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

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
end
go
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- neu erstellt 02.03.2010 23:58

Print '-'
Print '-- [Report_302_List_of_all_Datasets_With_Warnings] ----------------------------------------'
GO
CREATE PROCEDURE Report_302_List_of_all_Datasets_With_Warnings
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 1
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_302_List_of_all_Datasets_With_Warnings'
  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 [ObjectFromSiteID] As [SiteID] ,' +

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

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

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

                           ' WHERE  ([Row_isLastRes]=1 or [Row_isLastRes] is null) and' +
                           '        (' + @ProcStateCol + '= ''D'') and ([ProcessResultHasWarning]=''1'') -- and (ProcessResultCode <>' + '''' + '''' + ')'

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

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

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


-- neu erstellt 02.03.2010 23:58

Print '-'
Print '-- [Report_102_VolumeStatistic] ----------------------------------------'
GO
CREATE PROCEDURE Report_102_VolumeStatistic
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14)
       )

AS
begin
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 1
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_102_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 '-'
Print '-- [Report_303_List_of_all_happend_Errors] ---------------------'
GO

CREATE PROCEDURE Report_303_List_of_all_happend_Errors
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(32)
       )


AS
-- new 24.04.2012 15:21 J.Fes
BEGIN
  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 1
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='Report_303_List_of_all_happend_Errors'
  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @TempTblName   nvarchar(32)

  set @TempTblName = 'tempdb..##temp_tbl'
  IF (OBJECT_ID (@TempTblName, N'U') IS NOT NULL) exec ('drop table ' + @TempTblName)

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

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

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

                          -- ' [IrName]            AS [IR.Id],' +
                          -- ' [ObjectName]        AS [O.Name],' +
                          -- ' [ObjectType]        AS [O.Type],' +
                          -- ' [ObjectUgMaster_APLevels] As [O.AP.LevN],' +
                          -- ' [ObjectUgMaster_CompCount] As [O.AP.CompN],' +
                           ' [ProcessResultCode] AS [P.Result.Code],' +
                           ' [ProcessResultMsg]  AS [P.Result.Message]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from [dbo].[DS_AndProcessData_UDF]('  + '''' + @ProcStateCol + '''' + ')' +

                           ' WHERE  ([Row_isLastRes]=1 or [Row_isLastRes] is null) and' +
                           '        ([' + @ProcStateCol + '] like ''D%'') and (ProcessResultCode <> ''0'') and (ProcessResultCode <>' + '''' + '''' + ')'


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

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



  if @DebugPrintOn = 1
  begin

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

   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ObjectFromSiteID' ,  @ObjectFromSiteID
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ProcStateCol' ,      @ProcStateCol
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '@ExecuteSQLstr' ,     @ExecuteSQLstr
  end
 execute sp_executesql @ExecuteSQLstr
END
go
--------------------------------------------------------------------------------
/*
CREATE PROCEDURE dbo.Report_901_MulitReportExample
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(32)
       )
AS
BEGIN

  -- Step Setup Base Layout do not statt with numbers do not use -
  EXEC dbo.ReportPageNamesInit 'Multi Report Example',         'This report is for testing purposes only'
  EXEC dbo.ReportPageNamesAdd  'Objects_Datasets_Sum',         'Sum of all object Datasets'
  EXEC dbo.ReportPageNamesAdd  'ProcStatus_by_DataSets_Count', 'count of ProcStatus grouped by DataSets'
  EXEC dbo.ReportPageNamesAdd  'ProcessErrorList',             'List of all errors occoured while processing the data'
  EXEC dbo.ReportPageNamesGet

  EXEC dbo.Report_101_Objects_Datasets_Sum                      @ObjectFromSiteID, @ProcStateCol
  EXEC dbo.Report_212_ProcStatus_by_DataSets_MultiSiteYes_Count @ObjectFromSiteID, @ProcStateCol
  EXEC dbo.Report_301_List_of_all_Datasets_With_Errors                          @ObjectFromSiteID, @ProcStateCol

END
-- go
*/
--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Report_902_ProcessStatistic_And_ProcessResults_MultiSiteYes
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(32)
       )
AS
BEGIN



  -- Step Setup Base Layout do not with numbers do not use -
  EXEC dbo.ReportPageNamesInit 'Process Results',                    'Process Statistic and Lists of results'

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

--  EXEC dbo.Report_101_Objects_Datasets_Sum                      @ObjectFromSiteID, @ProcStateCol
  EXEC dbo.Report_212_ProcStatus_by_DataSets_MultiSiteYes_Count @ObjectFromSiteID, @ProcStateCol
  EXEC dbo.Report_303_List_of_all_happend_Errors                @ObjectFromSiteID, @ProcStateCol
  EXEC dbo.Report_301_List_of_all_Datasets_With_Errors          @ObjectFromSiteID, @ProcStateCol
  EXEC dbo.Report_302_List_of_all_Datasets_With_Warnings        @ObjectFromSiteID, @ProcStateCol

END
GO

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