Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [TaskScheduler]'
Print ' Creation Date : 24.09.2008 / A.Becker'
Print ' #LUp          : 09.12.2022 / J.Fes'
Print ' #LUp          : 09.01.2025 / J.Fes'
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO


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 @ExecuteSQLCols     nvarchar(max)
     DECLARE @ExecuteSQLstr      nvarchar(max)
     DECLARE @ExecuteSQLstr1     nvarchar(max)
     DECLARE @ExecuteSQLstr2     nvarchar(max)
     DECLARE @WhereObjectsSQLstr nvarchar(max)

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

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

     set @ExecuteSQLCols = ' dbo.TaskScheduler_tbl.TaskExcuteOnComputers,' +
                           ' dbo.TaskScheduler_tbl.AssignedToTaskInst,' +
                          ' dbo.TaskScheduler_tbl.ProcessStepControl,' +
                          ' dbo.TaskScheduler_tbl.ReDo,' +
                          -- ' dbo.TaskScheduler_tbl.ProcessTaskParameterPacket,' +
                          ' dbo.TaskScheduler_tbl.ProcessObjectsProcStateCol,' +
                          ' dbo.TaskScheduler_tbl.ProcessOwner,' +
                          ' dbo.TaskScheduler_tbl.ProcessType,' +
                          ' dbo.TaskScheduler_tbl.Note,' +
                          ' dbo.TaskScheduler_tbl.ProcessLastRunEnd,' +
                          ' dbo.TaskScheduler_tbl.ProcessLastRunStart,' +
                          ' dbo.TaskScheduler_tbl.ProcessLastMsg,' +
                          ' dbo.TaskScheduler_tbl.ProcessState,' +
                          ' dbo.TaskScheduler_tbl.cmdPara,' +
                          ' dbo.TaskScheduler_tbl.cmd,' +
                          ' dbo.TaskScheduler_tbl.TimeSchedule,' +
                          ' dbo.TaskScheduler_tbl.Name,' +
                          ' dbo.TaskScheduler_tbl.Description,' +
                          ' dbo.TaskScheduler_tbl.Active,' +
                          ' dbo.TaskScheduler_tbl.[Order],' +
                          ' dbo.TaskScheduler_tbl.Id'


     -- erzeugt die Tabellenstruktur
     set @ExecuteSQLstr1 = 'SELECT TOP 0 ' + @ExecuteSQLCols + ' into ##TaskScheduler_tbl FROM dbo.TaskScheduler_tbl'
     execute sp_executesql @ExecuteSQLstr1
     -- 'insert into ##TaskScheduler_tbl Exec(@ExecuteSQLstr)


     -- if @TaskSheduler_DataGridFilter_Enum = 'All'
     begin
        -- set @ExecuteSQLstr = 'SELECT * FROM dbo.TaskScheduler_tbl WHERE (1=1)'
        set @ExecuteSQLstr = 'SELECT ' + @ExecuteSQLCols + ' 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)'
        set @ExecuteSQLstr = 'SELECT ' + @ExecuteSQLCols + ' 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)'
        set @ExecuteSQLstr = 'SELECT ' + @ExecuteSQLCols + ' 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 ' + @ExecuteSQLCols + ' 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
                              @ExecuteSQLCols + ' FROM dbo.TaskScheduler_tbl WHERE ([Active]<>1 AND [Redo]<>1)'
        if @WhereTaskSchedulerFilter <> "" set @ExecuteSQLstr2 = @ExecuteSQLstr2 + ' and (' + @WhereTaskSchedulerFilter + ')'
     end
     
     if @DebugPrintOn = 1
     begin
      -- Clear Debug Table
      EXECUTE zzzzzDebugTbl_Empty
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@TaskSheduler_DataGridFilter_Enum' , @TaskSheduler_DataGridFilter_Enum
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@WhereTaskSchedulerFilter' ,         @WhereTaskSchedulerFilter
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '11-@ExecuteSQLstr' ,                    @ExecuteSQLstr
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '12-@ExecuteSQLstr1' ,                   @ExecuteSQLstr1
      EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '13-@ExecuteSQLstr2' ,                   @ExecuteSQLstr2
     end


     if  @ExecuteSQLstr <> ''
     begin
       -- fuer alle abfragen ORDER BY anhaengen
       set @ExecuteSQLstr = @ExecuteSQLstr + ' ORDER BY [Order] DESC'
       Print ('' + @ExecuteSQLstr)
       -- 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


   -- 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
-- EIntrag entfernt 04.12.2013
-- 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 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 '-- [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'
-- EXECUTE TaskScheduler_and_Objects_ForUltragrid '' , 'ActiveOnly' -- 'where ([Active] = 1 or [Redo] = 1) ORDER BY dbo.TaskScheduler_tbl.[Order] DESC'

GO

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

