Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [TaskScheduler]'
Print ' Creation Date : 24.09.2008'
Print ' Autor         : A.Becker'
Print ' '
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO


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

CREATE PROCEDURE TaskScheduler_and_Objects_ForUltragrid
(
       @WhereTaskSchedulerFilter         nvarchar(max) ,
       @TaskSheduler_DataGridFilter_Enum nvarchar(32)
)
AS
BEGIN

     DECLARE @DebugPrintOn bit
     set @DebugPrintOn = 0
     DECLARE @MyProcName nvarchar(64)
     set @MyProcName='TaskScheduler_Objects_ForUltragrid'

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


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

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

     -- erzeugt die Tabellenstruktur
     SELECT TOP 0 * into #TaskScheduler_tbl FROM dbo.TaskScheduler_tbl


     -- if @TaskSheduler_DataGridFilter_Enum = 'All'
     begin
        set @ExecuteSQLstr = 'SELECT * FROM dbo.TaskScheduler_tbl WHERE (1=1)'
        if @WhereTaskSchedulerFilter <> ""
        set @ExecuteSQLstr = @ExecuteSQLstr + ' and (' + @WhereTaskSchedulerFilter + ')'
     end

     if @TaskSheduler_DataGridFilter_Enum = 'ActiveOnly'
     begin
        set @ExecuteSQLstr = 'SELECT * FROM dbo.TaskScheduler_tbl WHERE ([Active]=1 OR [Redo]=1)'
        if @WhereTaskSchedulerFilter <> ""
        set @ExecuteSQLstr = @ExecuteSQLstr + ' and (' + @WhereTaskSchedulerFilter + ')'
     end

     if @TaskSheduler_DataGridFilter_Enum = 'InActiveOnly'
     begin
        set @ExecuteSQLstr = 'SELECT * FROM dbo.TaskScheduler_tbl WHERE ([Active]<>1 AND [Redo]<>1)'
        if @WhereTaskSchedulerFilter <> ""
        set @ExecuteSQLstr = @ExecuteSQLstr + ' and (' + @WhereTaskSchedulerFilter + ')'
     end


     if @TaskSheduler_DataGridFilter_Enum = 'ActiveLast10' or @TaskSheduler_DataGridFilter_Enum = 'ActiveLast05'
     begin
        -- new 06.12.2009 15:10 J.Fes alle Aktiven und die nächstfolgenden 5 oder 10 nicht verarbeiteten
        set @ExecuteSQLstr = 'SELECT * FROM dbo.TaskScheduler_tbl WHERE ([Active]=1 OR [Redo]=1)'
        if @WhereTaskSchedulerFilter <> ""
        set @ExecuteSQLstr = @ExecuteSQLstr + ' and (' + @WhereTaskSchedulerFilter + ')'

        set @ExecuteSQLstr2 = 'SELECT TOP ' + Right(@TaskSheduler_DataGridFilter_Enum,2) +   -- filter 05 oder 10
                             + ' * FROM dbo.TaskScheduler_tbl WHERE ([Active]<>1 AND [Redo]<>1)'
        if @WhereTaskSchedulerFilter <> ""
        set @ExecuteSQLstr2 = @ExecuteSQLstr2 + ' and (' + @WhereTaskSchedulerFilter + ')'
     end
     

     if  @ExecuteSQLstr <> ''
     begin
       -- fuer alle abfragen ORDER BY anhaengen
       set @ExecuteSQLstr = @ExecuteSQLstr + ' ORDER BY [Order] DESC'
       -- execute sp_executesql @ExecuteSQLstr
       insert into #TaskScheduler_tbl Exec(@ExecuteSQLstr)
     end


     if  @ExecuteSQLstr2 <> ''
     begin
       -- fuer alle abfragen ORDER BY anhaengen
       set @ExecuteSQLstr2 = @ExecuteSQLstr2 + ' ORDER BY [Order] DESC'

       -- execute sp_executesql @ExecuteSQLstr2
       insert into #TaskScheduler_tbl Exec(@ExecuteSQLstr2)
     end

     if @DebugPrintOn = 1
     begin
      -- Clear Debug Table
      EXECUTE zzzzzDebugTbl_Empty
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@WhereTaskSchedulerFilter' ,         @WhereTaskSchedulerFilter
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '11-@ExecuteSQLstr' ,                    @ExecuteSQLstr
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '12-@ExecuteSQLstr2' ,                   @ExecuteSQLstr2
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '13-@TaskSheduler_DataGridFilter_Enum' , @TaskSheduler_DataGridFilter_Enum
     end


   -- Alle daten der gefilterten TaskScheduler_tbl ausgeben = Table-1
   SELECT * from #TaskScheduler_tbl;


   -- Alle verknuepften Objekte zu den Tasks ausgeben = Table 2
   SELECT
     dbo.Tasks_Objects_Rel_tbl.Id As Tasks_Objects_Rel_tbl_Id,
     dbo.#TaskScheduler_tbl.Id    As TaskScheduler_tbl_Id,
     dbo.Objects_tbl.Id           As Objects_tbl_Id,
     dbo.Objects_tbl.*
   FROM
     dbo.#TaskScheduler_tbl
     INNER JOIN dbo.Tasks_Objects_Rel_tbl ON (dbo.#TaskScheduler_tbl.Id = dbo.Tasks_Objects_Rel_tbl.TaskScheduler_tbl_Id)
     INNER JOIN dbo.Objects_tbl ON (dbo.Tasks_Objects_Rel_tbl.Objects_tbl_Id = dbo.Objects_tbl.Id)
   -- ORDER BY dbo.Objects_tbl.ObjectCliName
   ;


END

GO

-- gibt alle objekte die zu einer JobPlaner - TaskSchedulerDef gehören als Tabelle zurück
Print '-'
Print '-- [JobPlanerScheduleTask_ObjectsGet] -----------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "TaskSchedulerDef_ObjectsGet" and type = "P")
Drop Procedure TaskSchedulerDef_ObjectsGet
GO

If Exists (select Name
           from sysobjects
           where Name = "JobPlanerScheduleTask_ObjectsGet" and type = "P")
Drop Procedure JobPlanerScheduleTask_ObjectsGet
GO

CREATE PROCEDURE dbo.JobPlanerScheduleTask_ObjectsGet
                 @TaskSchedulerDef_ID uniqueidentifier

AS
BEGIN

    -- Liste der Objecte des Tasks
    SELECT
      dbo.DS_with_Irs_View.*
    FROM
      dbo.TaskScheduler_tbl
      INNER JOIN dbo.Tasks_Objects_Rel_tbl ON (dbo.TaskScheduler_tbl.Id = dbo.Tasks_Objects_Rel_tbl.TaskScheduler_tbl_Id)
      INNER JOIN dbo.DS_with_Irs_View ON (dbo.Tasks_Objects_Rel_tbl.Objects_tbl_Id = dbo.DS_with_Irs_View.ID)
    WHERE
      dbo.TaskScheduler_tbl.ID = @TaskSchedulerDef_ID
    ;

END
GO






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

Create Procedure dbo.TaskScheduler_tbl_OnDemandNumber_GetHighest

As
BEGIN

    declare @num nvarchar(10)
    
    -- höchste OD-nnnnnnn in @num holen
    SELECT @num = max(dbo.TaskScheduler_tbl.[Name])
    FROM   dbo.TaskScheduler_tbl
    WHERE  dbo.TaskScheduler_tbl.[Name] LIKE 'JP-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
    
    -- Nummer extrahieren
    SELECT @num = substring(@num, 4, 7);
    SELECT @num = Cast(@num AS int);
    
    -- und zurückgeben
    SELECT @num;
  
END
GO

-- Print ''
-- Print '##Debug:'
-- Print '-------------------------------------------------------------------------'
-- EXECUTE TaskScheduler_and_Objects_ForUltragrid 5, '' -- 'where ([Active] = 1 or [Redo] = 1) ORDER BY dbo.TaskScheduler_tbl.[Order] DESC'
GO

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