CREATE procedure _QU_isProcedureRunning

      @sDatabaseName               varchar(100),
      @sProcedureName              varchar(100),
      @bIsProdecureAlreadyRunning  int output,
      @bVerbose                    int

AS
begin
  set nocount on


  ------------------------
  -- Is the procedure already running?
  set @bIsProdecureAlreadyRunning  = (
                                       select  _bIsCurrentlyRunning
                                         from  _Logistics.dbo._JobControl 
                                        where  rtrim(_sDatabaseName) = rtrim(@sDatabaseName)
                                          and  rtrim(_sJobName)      = rtrim(@sProcedureName)
                                     )

  ------------------------
  if( @bVerbose > 0 )
    begin
      if( @bIsProdecureAlreadyRunning > 0)
        begin
          print char(39) + @sProcedureName + char(39) + ' is Set = ' + cast(@bIsProdecureAlreadyRunning  as char)
        end
      else
        begin
          print char(39) + @sProcedureName + char(39) + ' is ReSet = ' + cast(@bIsProdecureAlreadyRunning  as char)
        end
    end

end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE __Example__Job_UPS_Monitor
AS
begin
  UPDATE  _Logistics.dbo._JobControl
     SET  _bIsCurrentlyRunning = 0
   where  _sDatabaseName = '_Logistics'
     and  _sJobName      = '__Job_UPS_Monitor'

  exec _Logistics.dbo.__Job_UPS_Monitor
end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE procedure __Job_SystemSTARTUP
as
begin

  declare
    @sProcedureName    varchar(200),
    @sDatabaseName     varchar(100)

  set @sDatabaseName       = '_Logistics'
  set @sProcedureName      = 'Global'

  UPDATE  _Logistics.dbo._JobControl
     SET  _bTerminate = 0
   where  rtrim(_sDatabaseName) = rtrim(@sDatabaseName)
     and  rtrim(_sJobName)      = rtrim(@sProcedureName)

end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE procedure obsolete__Job_STARTUP_AnsweringVoiceFax
as
-- NOTE: In order to get the system permission to run stored procedure '__Job_CleanStart'; this stored procedure has to run at the command 
--       line 'CleanStart.exe', which calls back (i.e.: executes the stored procedure '__Job_CleanStart'
begin
  set nocount on
  declare @sDosCommand   varchar(400)
  set @sDosCommand = 'C:\BITWARE\BFRECV.EXE'
  EXECUTE master.dbo.xp_cmdshell @sDosCommand, no_output
end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE procedure _MNT_checkPointDatabase
AS
begin
  set nocount on
  declare 
    @bVerbose int
  
  
  -----------------------------------
  set @bVerbose = rtrim((select _bVerbose from _COMMON_GlobalVariables))
  
  
  --------------------------
  -- Does user want to see ANSI warnings ? If not, then hide them
  if( @bVerbose < 1 )
    begin
      SET ANSI_WARNINGS OFF
    end
  
  
  --------------------------
  if( @bVerbose > 0 )
    begin
      print ''
      
      
      print '---------------------------'
      print 'Check pointing the _StockMarket database'
      
      
      print '---------------------------'
      print ''
    end
  
  
  -----------------------------------
  -- Make sure that when checkpointing the inactive portion of the log is trucated
  EXEC sp_dboption @dbname = '_StockMarket', @optname = 'trunc. log on chkpt.', @optvalue = 'TRUE'
  
  
  -----------------------------------------------------
  -- Make sure to write all outstanding/non-committed data to disk
  CHECKPOINT
end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE procedure _PROD_logStatus
         @procedureName varchar(200),
         @bStartEnd     int,
         @bVerbose      int
AS
begin

  declare @commandLine   varchar(400)
  
  
  ------------------------------------
  -- Create status Line
  if( @bStartEnd > 0 )
    begin
     if @bVerbose > 0
       begin
         print 'START -> ' + @procedureName + ';  Time is: ' + char(34) + rtrim(CAST( GETDATE() AS char)) + char(34)
         print char(13)
       end
     set @procedureName = 'START: ' + @procedureName + ';   Time: ' + rtrim(CAST( GETDATE() AS char))
     
     
     ------------------------------------
     -- Log the time
     set @commandLine = 'echo ' + @procedureName + ' >> ' + 
                         char(34) + 
                         rtrim((select _sLOG_FileDrive from _Logistics.dbo._COMMON_GlobalVariables)) + 
                         rtrim((select _sLOG_FilePath  from _Logistics.dbo._COMMON_GlobalVariables))  +
                         'LogStatus.txt' + 
                         char(34)
     EXECUTE master.dbo.xp_cmdshell @commandLine, no_output

    end
  else
    begin

      if @bVerbose > 0
        begin
          print '__END -> ' + @procedureName + ';  Time is: ' + char(34) + rtrim(CAST( GETDATE() AS char)) + char(34)
          print char(13)
          set @procedureName = '__END: ' + @procedureName + ';   Time: ' + rtrim(CAST( GETDATE() AS char))
          
          
          ------------------------------------
          -- Log the time
          set @commandLine = 'echo '+ @procedureName + ' >> ' + 
                         char(34) +
                         rtrim((select _sLOG_FileDrive from _Logistics.dbo._COMMON_GlobalVariables)) + 
                         rtrim((select _sLOG_FilePath  from _Logistics.dbo._COMMON_GlobalVariables))  +
                         'LogStatus.txt' + 
                         char(34)
          EXECUTE master.dbo.xp_cmdshell @commandLine, no_output
        end

    end
end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE procedure _PROD_setProcedureStatus

      @sDatabaseName                  varchar(50),
      @sProcedureName                 varchar(200),
      @bSetProcedureStatus            int,
      @bInhibitProcedureFromStarting  int output,
      @bWasPreviouslyKilled           int output

AS
------------------------
-- NOTE: The the meaning of 'input' and 'output' flags and the responsibility(s) of the calling procedure are defined below:
--
------------
--
--       @bSetProcedureStatus =  1  means that the calling procedure wants to start.
--                                  (get the its previously 'state' information from its '_state_<<DatabaseName>_<ProcedureName>>' table to allow it to 
--                                  start properly)
------------
--
--       @bSetProcedureStatus =  0  means that the calling procedure has completed all of its assigned task(s) NORMALLY (i.e.: NOT prematurely)
--
------------
--
--       @bSetProcedureStatus = -1  means that the calling procedure was told to terminate itself and has complied but has noted that it did not finish
--                                  (when a procedure does not finish to completion, it saves its 'state' information in its 
--                                  '_state_<<DatabaseName>_<ProcedureName>>' table to allow it to start where it last left off, the next time it starts)
------------------------
-- NOTE: 
--       It is the calling procedures responsibility to set or reset ANY/ALL of its own variables NO MATTER under what state that it exits its run
--       (i.e.: If it exits under the '_bTerminate' flag, then it needs to record where it is, in its own '_state_<<DatabaseName>_<ProcedureName>>' table,
--    OR
--       If it exits NORMALLY, then it needs to records the <default> values to start the next time in its own '_state_<<DatabaseName>_<ProcedureName>>'
--       table)
------------------------

begin

  set nocount on

  ------------------------
  declare 
    @dtToday                         smalldatetime,
    @bIsProdecureAlreadyLogged       int,
    @bAreAnyProceduresStillrunning   int,
    @bIsProcedureCurrentlyRunning    int,
    @isItTimeToDie                   int,
    @bVerbose                        int


  ------------------------
  set @bInhibitProcedureFromStarting = 0
  set @bWasPreviouslyKilled          = 0
  set @bVerbose                      = rtrim((select _bVerbose from _Logistics.dbo._COMMON_GlobalVariables))


  ------------------------
  -- Is the calling/registering procedure trying to start? If so, then check to see if that procedure is ALLOWed to start
  if( @bSetProcedureStatus > 0 )
    begin

      ------------------------
      exec _Logistics.dbo._QU_isItTimeToDie
        @sDatabaseName,
        @sProcedureName,
        @isItTimeToDie output,
        @bVerbose

      ------------------------
      -- If the calling/registering procedure is NOT allowed to start, then tell that procedure NOT to start AND don't register the procedure
      if( @isItTimeToDie > 0 )
       begin
         set @bInhibitProcedureFromStarting = 1

         ------------
         if( @bVerbose > 0 )
           begin
             print rtrim(@sDatabaseName) + '.dbo.' + rtrim(@sProcedureName) + ' was unable to register because either its or the global _bTerminate flag was set'
           end

         goto the_end
       end

    end


  ------------------------
  -- Is the calling/registering procedure already running?
  exec _Logistics.dbo._QU_isProcedureRunning
        @sDatabaseName,
        @sProcedureName,
        @bIsProcedureCurrentlyRunning output,
        @bVerbose


  ------------------------
  -- If the calling/registering procedure is already running, then tell that procedure NOT to start AND don't re-register the procedure
  if( (@bSetProcedureStatus > 0) and (@bIsProcedureCurrentlyRunning > 0) )
   begin

     ------------
     if( @bVerbose > 0 )
       begin
         print rtrim(@sDatabaseName) + '.dbo.' + rtrim(@sProcedureName) + ' was unable to register because it is currently running'
       end

     ------------
     set @bInhibitProcedureFromStarting = 1
     goto the_end
   end


  ------------------------
  -- Initialize varialbles
  set @dtToday                    = getdate()

  set @bIsProdecureAlreadyLogged  = (
                                      select  count(*) 
                                        from  _Logistics.dbo._JobControl 
                                       where  rtrim(_sDatabaseName) = rtrim(@sDatabaseName)
                                         and  rtrim(_sJobName)      = rtrim(@sProcedureName)
                                    )

  ------------------------
  -- If the procedure is not already logged, then Insert the record
  if( @bIsProdecureAlreadyLogged < 1)
    begin

      ----------------
      INSERT INTO _Logistics.dbo._JobControl
        ( 
              _bTerminate,
              _bWasPreviouslyKilled,
              _bIsCurrentlyRunning,
              _sDatabaseName,
              _sJobName,
              _dtJobStartTime,
              _dtJobStopTime
        )
      VALUES(
              0,
              0,
              @bSetProcedureStatus,
              rtrim(@sDatabaseName),
              rtrim(@sProcedureName),
              @dtToday,
              @dtToday
            )

      ------------
      if( @bVerbose > 0 )
      begin
        print ''
        print '----------------------'
        print '_PROD_setProcedureStatus'
        print rtrim(@sDatabaseName) + '.dbo.' + rtrim(@sProcedureName) + ' was successfully added to the list of registered jobs with the status of: starting'
      end

    end
  else
    begin

      ----------------
      -- Is procedure starting?
      if( @bSetProcedureStatus > 0 )
        begin

          ----------------
          set @bWasPreviouslyKilled =  (
                                          select  _bWasPreviouslyKilled
                                            from  _Logistics.dbo._JobControl 
                                           where  rtrim(_sDatabaseName) = rtrim(@sDatabaseName)
                                             and  rtrim(_sJobName)      = rtrim(@sProcedureName)
                                        )


          ----------------
          -- If job is starting log the current time as the '_dtJobStartTime'
          UPDATE  _Logistics.dbo._JobControl
             SET
                  _bIsCurrentlyRunning  = @bSetProcedureStatus,
                  _dtJobStartTime       = @dtToday,
                  _bWasPreviouslyKilled = 0  -- Reset this because the calling procedure is being notified and is responsible for handling this condition
           where  rtrim(_sDatabaseName) = rtrim(@sDatabaseName)
             and  rtrim(_sJobName)      = rtrim(@sProcedureName)

          ------------
          if( @bVerbose > 0 )
            begin
              print ''
              print '----------------------'
              print '_PROD_setProcedureStatus'
              print rtrim(@sDatabaseName) + '.dbo.' + rtrim(@sProcedureName) + char(39) + 's registration was updated successfully with the status of: starting'
            end
        end
      else
        begin

          ------------------------
          -- Was the procedure Killed off abnormally? If so, then note it
          if( @bSetProcedureStatus < 0)
            begin
              set @bWasPreviouslyKilled = 1
              set @bSetProcedureStatus  = 0

              ------------
              if( @bVerbose > 0 )
                begin
                  print ''
                  print '----------------------'
                  print '_PROD_setProcedureStatus'
                  print rtrim(@sDatabaseName) + '.dbo.' + rtrim(@sProcedureName) + char(39) + 's registration was updated successfully with the status of: stopped by termination request'
                end
            end
          else
            begin
              set @bWasPreviouslyKilled = 0

              ------------
              if( @bVerbose > 0 )
                begin
                  print ''
                  print '----------------------'
                  print '_PROD_setProcedureStatus'
                  print rtrim(@sDatabaseName) + '.dbo.' + rtrim(@sProcedureName) + char(39) + 's registration was updated successfully with the status of: stopped NORMALLY'
                end
            end


          ----------------
          -- Else job is stopping, therefore log the current time as the '_dtJobStopTime'
          UPDATE  _Logistics.dbo._JobControl
             SET
                  _bIsCurrentlyRunning  = @bSetProcedureStatus,
                  _dtJobStopTime        = @dtToday,
                  _bWasPreviouslyKilled = @bWasPreviouslyKilled
           where  rtrim(_sDatabaseName) = rtrim(@sDatabaseName)
             and  rtrim(_sJobName)      = rtrim(@sProcedureName)

        end
    end



  ----------------
  -- Are ANY procedures still running?
  set @bAreAnyProceduresStillrunning = (
                                         select  count(*) 
                                           from  _Logistics.dbo._JobControl 
                                          where  (_bIsCurrentlyRunning = 1) 
                                            and  (lower(rtrim(_sJobName)) <> 'global')
                                       )

  ----------------
  -- Update the global '_bIsCurrentlyRunning' flag (which indicates if ANY process is still running)
  if( @bAreAnyProceduresStillrunning > 0 )
    begin

      ----------------
      UPDATE  _Logistics.dbo._JobControl
         SET
              _bIsCurrentlyRunning = @bAreAnyProceduresStillrunning,
              _dtJobStartTime      = @dtToday
       where  (lower(rtrim(_sJobName)) = 'global')

    end
  else
    begin

      ----------------
      UPDATE  _Logistics.dbo._JobControl
         SET
              _bIsCurrentlyRunning = @bAreAnyProceduresStillrunning,
              _dtJobStopTime       = @dtToday
       where  (lower(rtrim(_sJobName)) = 'global')

    end

  the_end:
end


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE procedure __Job_UPS_Monitor
AS
/* 
  NOTE: 'shutdown.exe' sets and resets '_bTerminate where _sJobName = 'Global' in table '_Logistics.dbo._JobControl' as needed
*/
begin

  set nocount on

  -------------------
  declare 
    @sSourceDrive                   varchar(10),
    @sSourcePath                    varchar(400),
    @commandLine                    varchar(400),
    @bVerbose                       int
  

  -----------------------------------------------------------------
  set @commandLine   = rtrim((select _sEXE_SupportUtilityDrive  from _Logistics.dbo._COMMON_GlobalVariables)) + 
                       rtrim((select _sEXE_SupportUtilityPath   from _Logistics.dbo._COMMON_GlobalVariables)) + 'ShutDown.exe ' + 
                       rtrim((select _sUPS_Drive                from _Logistics.dbo._COMMON_GlobalVariables)) +
                       rtrim((select _sUPS_Path                 from _Logistics.dbo._COMMON_GlobalVariables)) + ' tripp.dat tripp.cfg ' + 
                       rtrim((select _sPROD_ShutdownDrive       from _Logistics.dbo._COMMON_GlobalVariables)) + 
                       rtrim((select _sPROD_ShutdownPath        from _Logistics.dbo._COMMON_GlobalVariables)) + ' ' + 
                       rtrim((select _sSYS_Password             from _Logistics.dbo._COMMON_GlobalVariables)) + 
                       ' 1 1 120' -- Parameters are as follows: 
                                  --   1 minute of ignoring the power failure to see if it will go away
                                  --   1 minute of time to allow all running procedure(s)/program(s) to stop (_bTerminate is set per note above)
                                  --   120 seconds to allow the OS to finished up before the POWER is Actually cut

  set @bVerbose      = rtrim((select _bVerbose                  from _Logistics.dbo._COMMON_GlobalVariables))
  set @sSourceDrive  = rtrim((select _sPROD_ShutdownDrive       from _Logistics.dbo._COMMON_GlobalVariables))
  set @sSourcePath   = rtrim((select _sPROD_ShutdownPath        from _Logistics.dbo._COMMON_GlobalVariables))


  --------------------------
  -- Does user want to see ANSI warnings ? If not, then hide them
  if( @bVerbose < 1 )
    begin
      SET ANSI_WARNINGS OFF
    end

  
  -----------------------
  -- Did the UPS says that the power has failed?
  if( @bVerbose > 0 )
    begin
      print '@commandLine = ' + @commandLine
      EXECUTE master.dbo.xp_cmdshell @commandLine
    end
 else
    begin
      EXECUTE master.dbo.xp_cmdshell @commandLine, no_output
    end

end
GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE procedure obsolete__Example_PROD_createProcessActiveIndicatorFile
AS
begin
  declare
          @sDrive            varchar(10),
          @sDrivePath        varchar(400),
          @sProcessName      varchar(100),
          @sFileExtension    varchar(100),
          @bVerbose          int

  set @sDrive         = rtrim((select _sPROD_ShutdownDrive from _Logistics.dbo._COMMON_GlobalVariables))
  set @sDrivePath     = rtrim((select _sPROD_ShutdownPath from _Logistics.dbo._COMMON_GlobalVariables))
  set @sProcessName   = 'testProcess'
  set @sFileExtension = 'pid'
  set @bVerbose       = rtrim((select _bVerbose from _COMMON_GlobalVariables ))

  exec _PROD_createProcessActiveIndicatorFile
                  @sDrive,
                  @sDrivePath,
                  @sProcessName,
                  @sFileExtension,
                  @bVerbose
end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE procedure obsolete__Job_UPS_CleanUpPreviousShutdownResidue
AS
begin

  declare
    @commandLine                 varchar(400),
    @bVerbose                    int,
    @sProcedureName              varchar(200),
    @bSetProcedureStatus         int,
    @bIsProdecureAlreadyRunning  int
  
  
  -----------------------------------------------------------------
  set nocount on
  set @sProcedureName      = '__Job_UPS_CleanUpPreviousShutdownResidue'
  
  
  -----------------------------------------------------------------
  set @bVerbose    = rtrim((select _bVerbose from _COMMON_GlobalVariables ))
  set @commandLine = 'del ' + 
                     char(34) + 
                     rtrim((select _sEXE_SupportUtilityDrive from _Logistics.dbo._COMMON_GlobalVariables)) + 
                     rtrim((select _sEXE_SupportUtilityPath  from _Logistics.dbo._COMMON_GlobalVariables))  + 
                     'MgmtFlag.001' + 
                     char(34) 
  
  
  --------------------------
  -- Does user want to see ANSI warnings ? If not, then hide them
  if( @bVerbose < 1 )
    begin
      SET ANSI_WARNINGS OFF
    end
  
  
  ------------------------
  -- Give UPS time to append new records with NON-zero volts being listed
  WAITFOR DELAY '000:01:15'
  
  
  ------------------------
  -- Clean up previous management lock flag file 
  EXECUTE master.dbo.xp_cmdshell @commandLine, no_output
  if @bVerbose > 0
    begin
      print '@commandLine = ' + @commandLine
    end
end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE __Example_PROD_setProcedureStatus
AS
begin

  -------------------
  declare
    @sDatabaseName                  varchar(100),
    @sProcedureName                 varchar(100),
    @bSetProcedureStatus            int,
    @bInhibitProcedureFromStarting  int,
    @bWasPreviouslyKilled           int


  -------------------
  set @sDatabaseName       = '_StockMarket'
  set @sProcedureName      = 'TestProcedure'


  -------------------
  set @bSetProcedureStatus = 1
  exec _Logistics.dbo._PROD_setProcedureStatus
      @sDatabaseName,
      @sProcedureName,
      @bSetProcedureStatus,
      @bInhibitProcedureFromStarting output,
      @bWasPreviouslyKilled output


  -------------------
  print '@bInhibitProcedureFromStarting = ' + rtrim(cast(@bInhibitProcedureFromStarting as varchar(10)))
  print '@bWasPreviouslyKilled          = ' + rtrim(cast(@bWasPreviouslyKilled          as varchar(10)))

  SELECT  *
    FROM  _Logistics.dbo._JobControl
  -------------------


  -------------------
  -- Wait 1 minute (allows the time to change so that the getdate() function will show a '_dtStoptime' that is different from the '_dtStartTime'
  WAITFOR DELAY '000:01:00'


  -------------------
  set @bSetProcedureStatus = 0
  exec _Logistics.dbo._PROD_setProcedureStatus
      @sDatabaseName,
      @sProcedureName,
      @bSetProcedureStatus,
      @bInhibitProcedureFromStarting output,
      @bWasPreviouslyKilled output


  -------------------
  print '@bInhibitProcedureFromStarting = ' + rtrim(cast(@bInhibitProcedureFromStarting as varchar(10)))
  print '@bWasPreviouslyKilled          = ' + rtrim(cast(@bWasPreviouslyKilled          as varchar(10)))

  SELECT  *
    FROM  _Logistics.dbo._JobControl
  -------------------

end

GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO