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

use [#DB_N#]
GO


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

Print '-'
Print '-- [DS_with_Irs_View] -----------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "DS_with_Irs_View" and type = "V")
Drop View DS_with_Irs_View
GO

-- !! Namen nicht aendern !! siehe auch QuellCode ..\JobServer.DataSource\JobServer_DataSource.vb  Objects_tbl_UpdateProcessState
-- !! alter name war: ObjectsDataSets_View
CREATE VIEW [DS_with_Irs_View]
-- BasisView
-- Angezeigt werden die Datasets mit Irs
-- 31.12.2008 DS.ImportObjectErr eingefügt
AS
SELECT
  -- IRs.Id As IrId,
  IRs.ObjectName         AS IrName,
  IRs.ObjectName2        AS IrName2,
  IRs.ObjectDescription  AS IrDescription,

  IRs.ObjectPuid         AS IrPuid,
  -- 10.01.2012 11:58 eingefuegt
  Irs.ObjectType         AS IrType,

  DS.Id AS ID,
  DS.ObjectName,
  DS.ObjectDescription,
  DS.ObjectType,
-- 10.01.2012 11:58 entnommen das DS.ObjecIrType wieder entnommen wurde
--  DS.ObjectIrType        AS IrType,
  DS.ObjectCliName,
  DS.ObjectFromSiteID,
  DS.ObjectClass,
  DS.ObjectOwningSiteID,
  DS.ObjectOwner,
  DS.ObjectGroup,
  DS.ObjectCreateDate,
  DS.ObjectLastModUser,
  DS.ObjectLastModDate,
  DS.ObjectSelectByMarker,
  DS.ObjectUgMaster_Type,
  DS.ObjectUgMaster_CompCount,
  DS.ObjectUgMaster_CompCountUnique, -- new 22.08.2012 a.becker (KION)
  DS.ObjectUgMaster_APLevels,
  DS.ObjectUgMaster_FsHitCount,
--  DS.ObjectUgMaster_FsHitCount_YN,
  DS.ObjectUgMaster_PartFamMemName,
  DS.ObjectReleaseState,
  DS.ObjectReleaseDate,
  DS.ObjectFs_DPNE,
  DS.ObjectFs_Vol,
  DS.ObjectFs_NE,
  DS.ObjectFs_SizeBytes, -- new 22.08.2012 a.becker (KION)
  DS.ObjectFs_HashCode,  -- new 22.08.2012 a.becker (KION)
  DS.ObjectCADVersion,   -- new 22.08.2012 a.becker (KION)
  DS.ObjectNote,
  DS.ProcStateCol01,
  DS.ProcStateCol02,
  DS.ProcStateCol03,
  DS.ProcStateCol04,
  DS.ProcStateCol05,
  DS.ProcStateCol06,
  DS.ProcStateCol07,
  DS.ProcStateCol08,
  DS.ProcStateCol09,
  DS.ProcStateCol10,
  DS.ProcStateColRf,
-- 20.12.2011 entfernt
--   DS.ObjectAttr01,
--   DS.ObjectAttr02,
--   DS.ObjectAttr03,
--   DS.ObjectAttr04,
--   DS.ObjectAttr05,
--   DS.ObjectAttr06,
--   DS.ObjectAttr07,
--   DS.ObjectAttr08,
--   DS.ObjectAttr09,
--   DS.ObjectAttr10,
  DS.ImportObjectErr,

  -- nur temporär
  DS.ObjectPuid
FROM
  -- 27.09.2011 WITH (nolock) engefuegt J.Fes
  Objects_tbl DS WITH (nolock)
  INNER JOIN Objects_Ir_Rel_tbl IrRel ON (DS.Id = IrRel.ObjectId)
  INNER JOIN Objects_tbl Irs ON (IrRel.IrId = Irs.Id)
GO


----------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
Print '-'
Print '-- [IRs_View] -----------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "IRs_View" and type = "V")
Drop View IRs_View
GO

CREATE VIEW [IRs_View]
-- BasisView: Ausgegeben werden die IRs
AS
SELECT
  dbo.Objects_tbl.Id,
  dbo.Objects_tbl.ObjectPuid,
  dbo.Objects_tbl.ObjectName , -- wird zusäzliche benötigt
  dbo.Objects_tbl.ObjectPuid As IrPuid,  -- 06.02.2011 13:33 eingefuegt
  dbo.Objects_tbl.ObjectName As IrName,
  dbo.Objects_tbl.ObjectName2 As IrName2,
  dbo.Objects_tbl.ObjectDescription As IrDescription,
  dbo.Objects_tbl.ObjectFromSiteID,
  dbo.Objects_tbl.ObjectClass,
  dbo.Objects_tbl.ObjectType As IrType, -- 20.12.2011 04:48 eingefuegt
  dbo.Objects_tbl.ObjectCliName,
  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.ObjectSelectByMarker,
  dbo.Objects_tbl.ObjectUgMaster_Type,
  dbo.Objects_tbl.ObjectUgMaster_CompCount,
  dbo.Objects_tbl.ObjectUgMaster_CompCountUnique,   -- new 22.08.2012 a.becker (KION)
  dbo.Objects_tbl.ObjectUgMaster_APLevels,
  dbo.Objects_tbl.ObjectUgMaster_FsHitCount,
--   dbo.Objects_tbl.ObjectUgMaster_FsHitCount_YN,
  dbo.Objects_tbl.ObjectFs_DPNE,
  dbo.Objects_tbl.ObjectFs_Vol,
  dbo.Objects_tbl.ObjectFs_NE,
  dbo.Objects_tbl.ObjectFs_SizeBytes, -- new 22.08.2012 a.becker (KION)
  dbo.Objects_tbl.ObjectFs_HashCode,  -- new 22.08.2012 a.becker (KION)
  dbo.Objects_tbl.ObjectCADVersion,   -- new 22.08.2012 a.becker (KION)
  dbo.Objects_tbl.ObjectUgMaster_PartFamMemName,
  dbo.Objects_tbl.ObjectReleaseState,
  dbo.Objects_tbl.ObjectReleaseDate,
  dbo.Objects_tbl.ObjectNote,
  dbo.Objects_tbl.ImportObjectErr,
  dbo.Objects_tbl.ProcStateCol01,
  dbo.Objects_tbl.ProcStateCol02,
  dbo.Objects_tbl.ProcStateCol03,
  dbo.Objects_tbl.ProcStateCol04,
  dbo.Objects_tbl.ProcStateCol05,
  dbo.Objects_tbl.ProcStateCol06,
  dbo.Objects_tbl.ProcStateCol07,
  dbo.Objects_tbl.ProcStateCol08,
  dbo.Objects_tbl.ProcStateCol09,
  dbo.Objects_tbl.ProcStateCol10,
  dbo.Objects_tbl.ProcStateColRf
--   dbo.Objects_tbl.ObjectAttr01,
--   dbo.Objects_tbl.ObjectAttr02,
--   dbo.Objects_tbl.ObjectAttr03,
--   dbo.Objects_tbl.ObjectAttr04,
--   dbo.Objects_tbl.ObjectAttr05,
--   dbo.Objects_tbl.ObjectAttr06,
--   dbo.Objects_tbl.ObjectAttr07,
--   dbo.Objects_tbl.ObjectAttr08,
--   dbo.Objects_tbl.ObjectAttr09,
--   dbo.Objects_tbl.ObjectAttr10
FROM
  --  WITH (nolock) engefuegt 09.07.2010 17:06 J.Fes
  dbo.Objects_tbl WITH (nolock)
WHERE
  dbo.Objects_tbl.ObjectClass = 'IR'
GO



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

CREATE FUNCTION DS_AndProcessData_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  DS_with_Irs_View.*,
--  JobControl_ObjectsProcessData_tbl.JobControlRuleName AS ID_JobControl_ObjectsProcessData_tbl ,
  JobControl_ObjectsProcessData_tbl_as.ID As zzzPDat_DRID ,
  JobControl_ObjectsProcessData_tbl_as.Objects_tbl_ID ,
  JobControl_ObjectsProcessData_tbl_as.JobControlRuleName,
  JobControl_ObjectsProcessData_tbl_as.ProcessPacketName,
  JobControl_ObjectsProcessData_tbl_as.ProcessOnSiteID,
  JobControl_ObjectsProcessData_tbl_as.ProcessState,
  JobControl_ObjectsProcessData_tbl_as.ProcessTimeStart,
  JobControl_ObjectsProcessData_tbl_as.ProcessTimeEnd,
  JobControl_ObjectsProcessData_tbl_as.ProcessCMDLine,
  JobControl_ObjectsProcessData_tbl_as.ProcessOnComputer ,
  JobControl_ObjectsProcessData_tbl_as.ProcessResultCode,
  JobControl_ObjectsProcessData_tbl_as.ProcessResultMsg ,
-- engetragen 12.07.2009 J.Fes
  JobControl_ObjectsProcessData_tbl_as.ProcessResultHasWarning,
  JobControl_ObjectsProcessData_tbl_as.ProcessResultLink ,
--  eingetragen 22.10.2008 J.Fes
--  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_ID,
  JobControl_ObjectsProcessData_tbl_as.ProcessProcStateCol ,
--  eingetragen 22.2.2009 J.Fes
  JobControl_ObjectsProcessData_tbl_as.Row_isLastRes
FROM
  --  27.09.2011  WITH (nolock) eingefuegt J.Fes
  DS_with_Irs_View  WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock) WHERE ProcessProcStateCol=@ProcessProcStateCol) as JobControl_ObjectsProcessData_tbl_as
  ON (DS_with_Irs_View.ID = JobControl_ObjectsProcessData_tbl_as.Objects_tbl_ID)
)
GO

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

CREATE FUNCTION IRs_AndProcessData_UDF(@ProcessProcStateCol nvarchar(32))
RETURNS TABLE AS
Return (
SELECT
  IRs_View.*,
--  DS_with_Irs_View.*,
--  JobControl_ObjectsProcessData_tbl.JobControlRuleName AS ID_JobControl_ObjectsProcessData_tbl ,
  JobControl_ObjectsProcessData_tbl_as.ID As zzzPDat_DRID ,
  JobControl_ObjectsProcessData_tbl_as.Objects_tbl_ID ,
  JobControl_ObjectsProcessData_tbl_as.JobControlRuleName,
  JobControl_ObjectsProcessData_tbl_as.ProcessPacketName,
  JobControl_ObjectsProcessData_tbl_as.ProcessOnSiteID,
  JobControl_ObjectsProcessData_tbl_as.ProcessState,
  JobControl_ObjectsProcessData_tbl_as.ProcessTimeStart,
  JobControl_ObjectsProcessData_tbl_as.ProcessTimeEnd,
  JobControl_ObjectsProcessData_tbl_as.ProcessCMDLine,
  JobControl_ObjectsProcessData_tbl_as.ProcessOnComputer ,
  JobControl_ObjectsProcessData_tbl_as.ProcessResultCode,
  JobControl_ObjectsProcessData_tbl_as.ProcessResultMsg ,

-- engetragen 12.07.2009 J.Fes
  JobControl_ObjectsProcessData_tbl_as.ProcessResultHasWarning,
  JobControl_ObjectsProcessData_tbl_as.ProcessResultLink ,

--  eingetragen 22.10.2008 J.Fes
--  JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_ID,
  JobControl_ObjectsProcessData_tbl_as.ProcessProcStateCol ,
--  eingetragen 22.2.2009 J.Fes
  JobControl_ObjectsProcessData_tbl_as.Row_isLastRes
FROM
  -- DS_with_Irs_View
  -- 06.02.2011 13:27 WITH (nolock) WHERE Proc.. eingefuegt J.Fes
  -- 27.09.2011 20:37 IRs_View WITH (nolock) engefuegt J.Fes
  IRs_View WITH (nolock)
  Left JOIN
     (SELECT * FROM JobControl_ObjectsProcessData_tbl WITH (nolock) WHERE ProcessProcStateCol=@ProcessProcStateCol) as JobControl_ObjectsProcessData_tbl_as
  ON (IRs_View.ID = JobControl_ObjectsProcessData_tbl_as.Objects_tbl_ID)
)
GO

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


CREATE PROCEDURE ObjectsAndProcessData
(
               @IrDsView              nvarchar(10) ,
               @WhereIrObjectNameSql  nvarchar(max) ,
               @WhereObjectDataSql    nvarchar(max) ,
               @WhereProcessDataSql   nvarchar(max) ,
               @SelectTop             integer ,
               @ProcessProcStateCol   nvarchar(32)
) AS

BEGIN


  -- Hinweise:
  -- Evtl. Indices für die #Temp Tabellen erzeugen
  -- Regel: immer die Datensätze die die Datenmengen einschränken zuerst berücksistigen


  DECLARE @DebugPrintOn bit
  set @DebugPrintOn = 0
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName='ObjectsAndProcessData'

  DECLARE @ExecuteSQLstr      nvarchar(max)
  DECLARE @WhereObjectsSQLstr nvarchar(max)

  DECLARE @SelectTopSQLstr    nvarchar(max)

  DECLARE @CrTableSQL         nvarchar(max)
  DECLARE @CrTableName        nvarchar(24)

  set @CrTableName = '##zzcopyTableStruc'

  if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@IrDsView' ,             @IrDsView
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@WhereIrObjectNameSql' , @WhereIrObjectNameSql
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@WhereObjectDataSql' ,   @WhereObjectDataSql
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@WhereProcessDataSql' ,  @WhereProcessDataSql
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05-@SelectTop' ,            @SelectTop
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '06-@ProcessProcStateCol' ,  @ProcessProcStateCol
  end

  -- SelectTop zusammenstellen
  if @SelectTop > 0
     begin
       set @SelectTopSQLstr = 'SELECT TOP ' + CONVERT(nvarchar(64), @SelectTop) + ' '
     end
  else
     begin
       set @SelectTopSQLstr = 'SELECT '
     end

  set @WhereObjectsSQLstr = ' where' + ' [ObjectClass]=' + '''' +  @IrDsView  + ''''
  if @WhereIrObjectNameSql <> ''
     begin
      set @WhereObjectsSQLstr = @WhereObjectsSQLstr + ' and ' + @WhereIrObjectNameSql

      if @WhereObjectDataSql <> '' and @WhereProcessDataSql <> ''
         begin
          -- diesen Fall beachten wegen ... and ... and ...
          set @WhereObjectsSQLstr = @WhereObjectsSQLstr + ' and (' + @WhereObjectDataSql + ' and ' + @WhereProcessDataSql + ')'
          -- diese Variablen zurücksetzen damit der weiter unten folgende Code kein Problem macht
          set @WhereObjectDataSql= ''
          set @WhereProcessDataSql = ''
         end
     end

  if @WhereObjectDataSql <> ''
     begin
       set @WhereObjectsSQLstr = @WhereObjectsSQLstr + ' and ' + @WhereObjectDataSql
     end

  if @WhereProcessDataSql <> ''
     begin
       set @WhereObjectsSQLstr = @WhereObjectsSQLstr + ' and ' + @WhereProcessDataSql
     end

  -- wenn #zztmpERG2_tbl vorhanden dann diese loeschen !!
  if (object_id('tempdb..#zztmpERG2_tbl') is not null)  Drop table #zztmpERG2_tbl

  if @IrDsView = 'DS'
     begin
       -- wenn 'DS'
      set @ExecuteSQLstr = @SelectTopSQLstr
             + ' *'
             + ' from dbo.DS_AndProcessData_UDF(' + '''' + @ProcessProcStateCol + '''' + ') '
             + @WhereObjectsSQLstr
            -- entnommen 24.02.09 J.Fes  ' ORDER BY IrName, ObjectName, ProcessTimeStart DESC'
            -- SQL erstsellen um #zztmpObj1_tbl mit gleicher struktur erzeugen wie die Struktur von dbo.DS_AndProcessData_View
            -- set @CrTableSQL='select top 0 * Into ' + @CrTableName + ' from dbo.DS_AndProcessData_View'
       set @CrTableSQL='select top 0 * Into ' + @CrTableName + ' from dbo.DS_AndProcessData_UDF(' + ''' + @ProcessProcStateCol + ''' + ')'
     end
   else
     begin
       -- wenn 'IR' oder andere
      set @ExecuteSQLstr = @SelectTopSQLstr
             + ' *'
             + ' from dbo.IRs_AndProcessData_UDF(' + '''' + @ProcessProcStateCol + '''' + ') '
             + @WhereObjectsSQLstr
           --  entnommen 24.02.09 J.Fes + ' ORDER BY IrName, ObjectName, ProcessTimeStart DESC'
       -- SQL erstsellen um #zztmpObj1_tbl mit gleicher struktur erzeugen wie die Struktur von dbo.IRs_AndProcessData_View
       set @CrTableSQL='select top 0 * Into ' + @CrTableName + ' from dbo.IRs_AndProcessData_UDF(' + ''' + @ProcessProcStateCol + ''' + ')'
     end
   --------------------- #zztmpObj1_tbl Tabelle erzeugen --------------------------------
   --  @CrTableName löschen wenn vorhanden !
   IF (OBJECT_ID ('tempdb..' + @CrTableName, N'U') IS NOT NULL) exec ('drop table ' + @CrTableName)
   -- zztmpObj1_tbl Tabelle erstellen wie in @CrTableSQL angegeben
   execute sp_executesql @CrTableSQL
   -- #zztmpObj1_tbl Tabelle kopieren wie zzcopyTableStruc Tabelle
   select top 0 * Into #zztmpERG2_tbl from ##zzcopyTableStruc
   --  @CrTableName löschen
   IF (OBJECT_ID ('tempdb..' + @CrTableName, N'U') IS NOT NULL) exec ('drop table ' + @CrTableName)
   --------------------- #zztmpObj1_tbl Tabelle erzeugen --------------------------------

   if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '11-@ExecuteSQLstr' , @ExecuteSQLstr
   -- Daten via insert into an #zztmpObj1_tbl anfuegen
   insert into #zztmpERG2_tbl Exec(@ExecuteSQLstr)

   -- Ausgabe
   Select * from #zztmpERG2_tbl
END
GO

------------------------------------------------------------------
-- Abschnitt: Objectdaten abfragen
------------------------------------------------------------------
Print '-'
Print '-- [DataSetSelectByWhereClause] --------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "DataSetSelectByWhereClause" and type = "P")
Drop Procedure DataSetSelectByWhereClause
GO

CREATE Procedure DataSetSelectByWhereClause @WhereClauseSql nvarchar(max)
AS
BEGIN

/*
  -- Beispiel ObjectsDataSetSelectByWhereClause
  -- EXECUTE DataSetSelectByWhereClause 'where IrName LIKE ''07811002/%'' AND dbo.DS_with_Irs_View.ObjectType IN (''UGMASTER'',''UGPART'')'
*/

  DECLARE @ExecuteSQLstr nvarchar(max)
  DECLARE @MyProcName nvarchar(32)
  set @MyProcName = 'DataSetSelectByWhereClause'

  -- Debug
  -- EXECUTE zzzzzDebugTbl_Empty

  set @ExecuteSQLstr = 'SELECT '
                      + ' dbo.DS_with_Irs_View.IrName,'
                      + ' dbo.DS_with_Irs_View.ID,'
                      + ' dbo.DS_with_Irs_View.ObjectCliName,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol01,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol02,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol03,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol04,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol05,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol06,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol07,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol08,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol09,'
                      + ' dbo.DS_with_Irs_View.ProcStateCol10'
                      + ' FROM'
                      + ' dbo.DS_with_Irs_View'

  if @WhereClauseSql <> ''
             begin
                    set @ExecuteSQLstr = @ExecuteSQLstr + ' ' +  @WhereClauseSql
             end

  -- EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , N'1-@ExecuteSQLstr' , @ExecuteSQLstr
  execute sp_executesql @ExecuteSQLstr

END
GO
-- EXECUTE DataSetSelectByWhereClause 'where IrName LIKE ''07811002/%'' AND dbo.DS_with_Irs_View.ObjectType IN (''UGMASTER'',''UGPART'')'

Print ''
Print '----------------------------------------------------'
Print ' Procedures imported to #DB_N#'
Print '----------------------------------------------------'
GO
