USP_DATALIST_BUSINESSPROCESSES_SCHEDULEDTORUN

CREATE procedure [dbo].[USP_DATALIST_BUSINESSPROCESSES_SCHEDULEDTORUN]
(
    @LOCKSELECTEDSCHEDULES bit = 0,
    @DBREVKEY nvarchar(500) = null
)
as
begin
  set nocount on;

  declare @UPGRADEINPROGESS bit = 0;
  select @UPGRADEINPROGESS = 1 From sys.extended_properties where name = 'bb_DisableScheduledProcesses' and class_desc = 'DATABASE' and value = 'true';

  if @DBREVKEY is not null
  begin
    declare @CURRENTREVKEY nvarchar(500)= dbo.UFN_SCHEMA_DB_GETDBREVKEY();
    if @DBREVKEY <> @CURRENTREVKEY
        raiserror('BBERR_DBREVKEYDOESNOTMATCH', 13, 1);
  end

  if @UPGRADEINPROGESS = 0
  begin

      --Create a lock to prevent simultaneous runs

      declare @LOCKRESULT int;
      exec @LOCKRESULT = sp_getapplock 'C6FCAB60-2339-4162-9B12-CBEA63D71CBB', 'Exclusive', 'Session', 0;

      if @LOCKRESULT >= 0
      begin

        --Define constants

        declare @LOOKAHEADPARAMETERDEFINITION nvarchar(max) = N'@PARAMETERSETID uniqueidentifier, @RESULT bit out';  

        --Gather time data

        declare @NOW datetime = GETDATE();
        declare @TODAY date = @NOW;

        --Define table for schedules that are ready to be run

        declare @READYSCHEDULES table (BUSINESSPROCESSID uniqueidentifier, SCHEDULEID uniqueidentifier, BUSINESSPROCESSPARAMETERSETID uniqueidentifier, LOOKAHEADFUNCTIONNAME nvarchar(max));

        --Select schedules that are ready to be run

        with XMLNAMESPACES('bb_appfx_businessprocess' as bp, 'bb_appfx_globalchange' as gc)
        insert into @READYSCHEDULES
        select  BPS.BUSINESSPROCESSID, 
                S.ID, 
                BPS.BUSINESSPROCESSPARAMETERSETID, 
                ISNULL(GCC.GLOBALCHANGESPECXML.value('(/gc:GlobalChangeSpec/@LookaheadFunctionName)[1]', 'nvarchar(max)'),BPC.BUSINESSPROCESSSPECXML.value('(/bp:BusinessProcessSpec/@LookaheadFunctionName)[1]', 'nvarchar(max)')) as LOOKAHEADFUNCTIONNAME
        from dbo.BUSINESSPROCESSSCHEDULE BPS
        inner join dbo.BUSINESSPROCESSCATALOG BPC on BPC.ID = BPS.BUSINESSPROCESSID
        inner join dbo.SCHEDULE S on S.ID = BPS.SCHEDULEID
        left join dbo.GLOBALCHANGE GC on BPC.ID = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA' AND GC.ID = BPS.BUSINESSPROCESSPARAMETERSETID
        left join dbo.GLOBALCHANGECATALOG GCC on GCC.ID = GC.GLOBALCHANGECATALOGID
        where S.ENABLED = 1
        and (S.ENDDATE >= @TODAY or S.ENDDATE is null)
        and S.STARTDATE <= @TODAY
        and dbo.UFN_SCHEDULE_ISTIMETORUNSCHEDULE(@NOW, S.TYPECODE, S.FREQUENCY, S.SUBDAYFREQUENCYTYPECODE, S.TIME, S.SUBDAYFREQUENCY, S.SUBDAYSTARTINGTIME, S.SUBDAYENDINGTIME, S.DAYSOFWEEK, S.DAYOFMONTH, S.WEEKCODE, S.DAYOFWEEKCODE, S.MONTHCODE, S.STARTDATE, S.ENDDATE, S.LASTRUNTIME) = 1
        and APPLOCK_TEST('public',upper(cast(S.ID as nvarchar(36))),'Exclusive','Session') = 1;


        --Loop through selected schedules and create locks

        declare @SCHEDULEID uniqueidentifier, @BUSINESSPROCESSID uniqueidentifier, @BUSINESSPROCESSPARAMETERSETID uniqueidentifier, @LOOKAHEADFUNCTIONNAME nvarchar(max);
        declare SCHEDULECURSOR cursor local FAST_FORWARD for
        select BUSINESSPROCESSID, SCHEDULEID, BUSINESSPROCESSPARAMETERSETID, LOOKAHEADFUNCTIONNAME from @READYSCHEDULES;

        open SCHEDULECURSOR;
        fetch next from SCHEDULECURSOR into @BUSINESSPROCESSID, @SCHEDULEID, @BUSINESSPROCESSPARAMETERSETID, @LOOKAHEADFUNCTIONNAME; 
        declare @SELECTEDSCHEDULES table (BUSINESSPROCESSID uniqueidentifier, SCHEDULEID uniqueidentifier, BUSINESSPROCESSPARAMETERSETID uniqueidentifier);
        while @@FETCH_STATUS = 0
        begin

            declare @LOOKAHEADRESULT bit = 0;
            if @LOOKAHEADFUNCTIONNAME is not null
            begin
                declare @LOOKAHEADSTATEMENT nvarchar(max) = 'select @RESULT=dbo.[' + @LOOKAHEADFUNCTIONNAME + '](@PARAMETERSETID);';
                exec sp_executesql @LOOKAHEADSTATEMENT, @LOOKAHEADPARAMETERDEFINITION, @PARAMETERSETID=@BUSINESSPROCESSPARAMETERSETID, @RESULT=@LOOKAHEADRESULT out;
            end
            else
                set @LOOKAHEADRESULT = 1;

            if @LOOKAHEADRESULT = 1
            begin
                -- The lookahead returned true so lock the schedule

                -- and add it to the selected list.

                declare @SCHEDULELOCKRESULT int;
                if @LOCKSELECTEDSCHEDULES = 1
                begin
                    declare @N nvarchar(36) = upper(cast(@SCHEDULEID as nvarchar(36)));
                    exec @SCHEDULELOCKRESULT = sp_getapplock @N, 'Exclusive', 'Session', 0;
                end

                if (@LOCKSELECTEDSCHEDULES = 0) or (@SCHEDULELOCKRESULT >= 0) 
                    insert into @SELECTEDSCHEDULES values(@BUSINESSPROCESSID, @SCHEDULEID, @BUSINESSPROCESSPARAMETERSETID);
            end
            else
            begin
                -- The lookahead returned false so update the last run time to now and skip it.

                update dbo.SCHEDULE
                set LASTRUNTIME = @NOW
                from dbo.SCHEDULE S
                where S.ID = @SCHEDULEID;
            end

            fetch next from SCHEDULECURSOR into @BUSINESSPROCESSID, @SCHEDULEID, @BUSINESSPROCESSPARAMETERSETID, @LOOKAHEADFUNCTIONNAME;
        end

        close SCHEDULECURSOR;
        deallocate SCHEDULECURSOR;


        --Update last run date for selected schedules

        update dbo.SCHEDULE
        set LASTRUNTIME = @NOW
        from dbo.SCHEDULE S
        inner join @SELECTEDSCHEDULES SEL on SEL.SCHEDULEID = S.ID;


        --Select out selected schedules

        select BUSINESSPROCESSID, SCHEDULEID, BUSINESSPROCESSPARAMETERSETID
        from @SELECTEDSCHEDULES;

        exec dbo.USP_BUSINESSPROCESSSCHEDULELASTRUN_UPDATE;

        --Release app lock

        exec @LOCKRESULT = sp_releaseapplock 'C6FCAB60-2339-4162-9B12-CBEA63D71CBB', 'Session';
      end

  end

end