Print ''
Print '---------------------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [REPORTS_ListOfReports]'
Print ' Creation      : 29.08.2013/J.Fes'
Print ' Last.Update   : 29.08.2013/J.Fes'

use [#DB_N#]

Print ''
Print ' '
Print '---------------------------------------------------------------------------------------'
Print '--------------------- R E P O R T S - LIST OF Results  --------------------------------'
Print '---------------------------------------------------------------------------------------'
Print ''

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

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

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

  set @ExecuteSQLstr =  'SELECT DISTINCT' +
                           ' [ObjectFromSiteID] As [SiteID],' +
                           ' [ProcessResultCode] AS [P.Result.Code],' +
                           ' [ProcessResultMsg]  AS [P.Result.Message]' +
                           ', COUNT([ProcessResultMsg]) AS [R.Error.Count]' +
                           ' from ' + @AndProcessData_UDF + '('  + '''' + @ProcStateCol + '''' + ')' +
                           ' WHERE  ([Row_isLastRes]=1 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
--------------------------------------------------------------------------------
Print '-- [Report_301_List_of_all_Datasets_With_Errors] --------------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_301_List_of_all_Datasets_With_Errors")
Drop Procedure Report_301_List_of_all_Datasets_With_Errors
GO
CREATE PROCEDURE Report_301_List_of_all_Datasets_With_Errors
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )

-- Hinweis:
-- 25.04.2012 J.Fes dieser Report darf nur in Abstimmung mit den zugehörigen L&L Formularen angepasst werden.
--            |-> siehe ..\01-BinServer\Resources\ReportLayouts\Report_902_ProcessStatistic_And_ProcessResults_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 @AndProcessData_UDF nvarchar(32)
  set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "DS" set @AndProcessData_UDF='DS_AndProcessData_UDF'
  if @ObjectClass = "IR" set @AndProcessData_UDF='IRs_AndProcessData_UDF'
  if @ObjectClass = "I"  set @AndProcessData_UDF='ITEMs_AndProcessData_UDF'

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

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

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

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

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

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

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

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

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

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

  if @DebugPrintOn = 1
  begin

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

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


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

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

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

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

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

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

                           ' WHERE  ([Row_isLastRes]=1 or [Row_isLastRes] is null) and' +
                           '        (' + @ProcStateCol + ' like ''D%'') and ([ProcessResultHasWarning]=''1'') -- and (ProcessResultCode <>' + '''' + '''' + ')'
  --                                                       |-> 23.08.2012 15:44 J.Fes @ProcStateCol + 'like ''D*''  engetragen
  */

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

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

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

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

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

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

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

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

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

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


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

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

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


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

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

  EXEC dbo.Report_204_ProcStatus_by_DataSets_MultiSiteYes_Count @ObjectFromSiteID, @ProcStateCol
  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 '-- [Report_801_ProcessTime_perSite] ---------------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "Report_801_ProcessTime_perSite" and type = "P")
Drop Procedure Report_801_ProcessTime_perSite
GO

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

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

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

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

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

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

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

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

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

  execute sp_executesql @ExecuteSQLstr

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

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

  execute sp_executesql @ExecuteSQLstr
END
GO


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

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

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

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

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

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

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

  set @ExecuteSQLstr =
      'SELECT' +
      ' ' + @AndProcessData_UDF + '.ObjectFromSiteID    AS [ObjectFromSiteID],' +
      ' ' + @AndProcessData_UDF + '.ObjectUgMaster_Type AS [ObjectUgMaster_Type],' +
      ' min(' + @AndProcessData_UDF + '.ProcessTimeEnd) AS [PTimeStart],' +
      ' max(' + @AndProcessData_UDF + '.ProcessTimeEnd) AS [PTimeEnd],' +
      ' cast(DateDiff(ss,min(' + @AndProcessData_UDF + '.ProcessTimeEnd), max(' + @AndProcessData_UDF + '.ProcessTimeEnd)) As integer) As [PDur_sec],' +
      ' COUNT(' + @AndProcessData_UDF + '.ObjectFromSiteID) AS [Datasets_n]' +
      ' INTO' +
      '  ' + @TempTblName +
      ' FROM' +
      ' ' + @AndProcessData_UDF + '(''' + @ProcStateCol + ''')' +
      ' WHERE' +
      ' ' + @AndProcessData_UDF + '.Row_isLastRes = 1 and' +
      ' ' + @AndProcessData_UDF + '.ProcessTimeStart IS NOT NULL and' +
      ' ' + @AndProcessData_UDF + '.ProcessTimeEnd > ' + @AndProcessData_UDF + '.ProcessTimeStart and' +
      ' ' + @AndProcessData_UDF + '.ObjectFromSiteID like ''' +   @ObjectFromSiteID + '''' +
      '  and DateDiff(ss, ' + @AndProcessData_UDF + '.ProcessTimeStart, ' + @AndProcessData_UDF + '.ProcessTimeEnd) > 10' +
      ' GROUP BY' +
      ' ' + @AndProcessData_UDF + '.ObjectFromSiteID,' +
      ' ' + @AndProcessData_UDF + '.ObjectUgMaster_Type,' +
      '  year(' + @AndProcessData_UDF + '.ProcessTimeEnd),' +
      '  month(' + @AndProcessData_UDF + '.ProcessTimeEnd),'  +
      '  day(' + @AndProcessData_UDF + '.ProcessTimeEnd),' +
      '  datepart(hh,' + @AndProcessData_UDF + '.ProcessTimeEnd)' +
      ' Order BY [PTimeEnd]'


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

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

  execute sp_executesql @ExecuteSQLstr

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


   -- Abfrage 2 Temp Tabelle zusammenfassen !
  set @ExecuteSQLstr =
  'Select ' +
  ' ObjectFromSiteID                  As [Site],' +
  ' ObjectUgMaster_Type               As [O.UgMaster Type],' +
  ' PTimeStart                        As [P.Start Date/Time],' +
  ' PTimeEND                          As [P.End Date/Time],' +
  ' PDur_sec                          As [P.Dur.(sec)],' +
  ' datename(month,  [PTimeEND])      AS [P.Month],' +
  ' datepart(week,   [PTimeEND])      AS [P.Week of Year],' +
  ' (cast(datepart(w,[PTimeEND]) AS nvarchar(1)) +' + '''-''' + '+ datename(weekday,[PTimeEND]))' +
                                    ' AS [P.Weekday],' +
  ' convert(nvarchar, [PTimeEND],104) AS [P.Date/Day],' +
  ' datepart(hh,     [PTimeEND])      AS [P.Houre],' +
  '  Datasets_n                       As [O.Processed (n)]' +
  ' FROM ' + @TempTblName +
  ' WHERE ' +
  '  PDur_sec > 0' +
  ' ORDER BY' +
  '  PTimeEND'


  --

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

  execute sp_executesql @ExecuteSQLstr

END
GO

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