OnJobAdded

Triggered whenever a print job is added to a printer being monitored.

Parameters

* ClientId - The client for which this print job was added
* PrinterName - The name of the printer the job is sent to
* Username - The name of the user that submitted the print job
* Document - The document name in the print queue
* Machinename - The name of the source machine that the job came from
* Pages - Number of pages in the print job
* Copies - Number of copies to print
* Submitted - Date and time the print job was submitted
* PaperType - The paper type to print on
* JobId - The unique number of this print job
* Landscape - True to print in lanscape mode
* Colour - True to print in colour
* PaperSource - The paper tray to use for this job
* PrinterResolution - The resolution for the print job
* DataType - The data type (Raw, EMF, XPS) of the spool file
* PaperWidth - Width of the selected paper type
* PrinterResolutionX - Resolution in the X axis
* PrinterResolutionY - Resolution in the Y axis
* NotifyUserName - The user (if any) to notify of the job progress
* PrintProcessorName - The printer name of the print processor that is printing the print job
* DriverName - The name of the printer driver that is printing the print job
* Parameters - Any extra parameters passed to the printer driver with the print job
* QueuedTime - How long the print job has been queued
* JobSize - The size of the print job (in bytes)


Code :-
CREATE PROCEDURE [OnJobAdded]
/* -- [ OnJobAdded]---------------------------------------------------------------------
 * Called when a job is added to a printer queue and triggers a PRINTER_CHANGE_JOB_ADDED event
 *    Parameters: 
 *        ClientId As GUID
 *        PrinterName As String, 
 *        Username As String
 *        Document As String
 *        Machinename As String
 *        Pages As Integer
 *        Copies As Integer
 *        Submitted As DateTime
 *        PaperType As String
 *        JobId As Integer 
 *        Landscape As Boolean,
 *        Colour As Boolean
 *        PaperSource As String
 *        PrinterResolution As String
 *        DataType
 *        PaperWidth
 *        PrinterResolutionX
 *        PrinterResolutionY
 *        NotifyUserName
 *        PrintProcessorName
 *        DriverName
 *        Parameters
 *        QueuedTime
 *        JobSize
 */
(
   @ClientId uniqueidentifier,
   @PrinterName varchar(220), 
   @Username varchar(255),
   @Document varchar(255),
   @Machinename varchar(255),
   @Pages numeric(18,0) = 1, --Default to one page
   @Copies numeric(18,0) = 1, --Default to one copy
   @Submitted datetime,
   @PaperType varchar(40),
   @JobId numeric(18,0) ,
   @Landscape bit,
   @Colour  bit,
   @PaperSource varchar(120),
   @PrinterResolution varchar(100),
   @Paused bit,
   @Deleted bit,
   @Deleting bit,
   @Printed bit,
   @Printing bit,
   @InError bit,
   @Offline bit,
   @PaperOut bit,
   @UserInterventionRequired bit,
   @DataType varchar(120) = '',
   @PaperWidth int = 0, 
   @PaperHeight int = 0,
   @PrinterResolutionX int = 0,
   @PrinterResolutionY int = 0,
   @NotifyUserName varchar(255) = '',
   @PrintProcessorName varchar(255) = '',
   @DriverName varchar(255) = '',
   @Parameters varchar(255) = '',
   @QueuedTime int = 0, 
   @JobSize int = 0
)
 AS

BEGIN

DECLARE @next_printer INT,
        @printer_reboot_count INT

	SET NOCOUNT ON 

             IF NOT EXISTS(SELECT 'A' FROM dbo.Printers WHERE Name = @PrinterName AND [Client Id] = @clientId)
                BEGIN
                   SELECT @next_printer = ISNULL(MAX(dbo.Printers.[Printer Id]),1)  + 1
                      FROM dbo.Printers
                     WHERE [Client Id] = @clientId

                  INSERT INTO [Printers] (
                                      [Client Id],
                                      [Printer Id],
                                      Name
                                     )
                             VALUES(
                                      @clientId,
                                      @next_printer,
                                      @PrinterName
                                        )    
                END
             ELSE
               SELECT @next_printer = dbo.Printers.[Printer Id]
                 FROM dbo.Printers
                WHERE Name = @PrinterName
                  AND [Client Id] = @clientId
                               
   /* Get the current reboot count for the printer */
   SELECT @printer_reboot_count = ISNULL([Reboot Count], 1)
     FROM dbo.Printers
     WHERE [Printer Id] = @next_printer
       AND [Client Id] = @clientId
   
   /* If the JobId has already been used get the next possible number */
   IF EXISTS(SELECT 'A' 
                       FROM dbo.[Print Jobs]
                      WHERE dbo.[Print Jobs].[Printer Id] = @next_printer
                            AND dbo.[Print Jobs].[Job Id] = @JobId
                            AND dbo.[Print Jobs].[Printer Reboot Count] = @printer_reboot_count
                            AND [Client Id] = @clientId
                   )
                      BEGIN
                         set @printer_reboot_count = @printer_reboot_count + 1
                         UPDATE dbo.[Printers]
                            SET [Reboot Count] = @printer_reboot_count
                          WHERE [Printer Id] = @next_printer
                            AND [Client Id] = @clientId
                      END


/* Deal with incorrect input params */
If @copies <= 0
  select @copies = 1

IF @Pages <= 0
  select @pages = 1

   INSERT INTO dbo.[Print Jobs] 
       (
         [Client Id],
         [Printer Id],
         [Printer Reboot Count],
         [Job Id],
         [Submitted],
         [User Name],
         [Document Name],
         [Machine Name],
         [Pages],
         [Copies],
         [Paper Type],
         [Landscape],
         [Colour],
         [Paper Source],
         [Resolution],
         [Data Type],
         [Paper Width],
         [Paper Height],
         [Printer Resolution X],
         [Printer Resolution Y],
         [Notify User Name],
         [Print Processor Name],
         [Driver Name],
         [Parameters],
         [Printed],
         [Deleted],
         [Pages Printed]
       )
       SELECT 
         @clientId,
         @next_printer,
         @printer_reboot_count,
         @JobId,
         @Submitted,
         @Username,
         @Document,
         @MachineName,
         @Pages,
         @Copies,
         @PaperType ,
         @Landscape,
         @Colour,
         @PaperSource,
         @PrinterResolution, 
         @DataType,
         @PaperWidth, 
         @PaperHeight,
         @PrinterResolutionX,
         @PrinterResolutionY,
         @NotifyUserName,
         @PrintProcessorName,
         @DriverName,
         @Parameters,
         0, --printed
         0, --deleted
         0 -- Pages printed


   INSERT INTO dbo.[Print Job Events]
       (
        [Client Id],
        [Printer Id],
        [Printer Reboot Count],
        [Job Id],
        [Submitted],
        [Event],
        [Paused],
        [Deleted],
        [Deleting],
        [Printed],
        [Printing],
        [Offline],
        [Paper Out],
        [User Intervention Required],
        [Position],
        [Pages Printed],
        [Queued Time],
        [Job Size] 
       )
       SELECT 
         @clientId,
         @next_printer,
         @printer_reboot_count,
         @JobId,
         @Submitted,
          1, --JOB_ADDED event
          @Paused,
          @Deleted,
          @Deleting,
          @Printed,
          @Printing,
          @Offline,
          @PaperOut,
          @UserInterventionRequired,
          0,  --Position
          0, -- Pages Printed
          @QueuedTime,
          @JobSize

   RETURN

END

Last edited Dec 4, 2009 at 7:57 AM by Merrion, version 8

Comments

No comments yet.