SQL
USE [PrintMonitor]
GO
/****** Object:  StoredProcedure [dbo].[OnJobSet]    Script Date: 10/15/2008 21:01:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[OnJobSet]
/* -- [OnJobDeleted] -------------------------------------------------------------------
 * Triggered when a job settings are changed and a PRINTER_CHANGE_JOB_SET event is raised
 * Parameters: 
 *  PrinterName As String
 *  Username As String
 *  Document As String
 *  Machinename As String
 *  Pages As Integer
 *  Copies As Integer
 *  Submitted As DateTime
 *  Status As String
 *  PagesPrinted As Integer
 *  Paused As Boolean
 *  Deleted As Boolean
 *  Deleting As Boolean
 *  Printed As Boolean
 *  Printing As Boolean
 *  InError As Boolean
 *  Offline As Boolean
 *  PaperOut As Boolean
 *  UserInterventionRequired As Boolean
 *  QueuedTime As Integer
 *  JobSize As Integer
 *  JobId As Integer
*/
(
 @PrinterName varchar(220),
 @Username varchar(255),
 @Document varchar(255),
 @Machinename varchar(255),
 @Pages numeric(18,0),
 @Copies numeric(18,0),
 @Submitted smalldatetime,
 @Status varchar(255),
 @PagesPrinted numeric(18,0),
 @Paused bit,
 @Deleted bit,
 @Deleting bit,
 @Printed bit,
 @Printing bit,
 @InError bit,
 @Offline bit,
 @PaperOut bit,
 @UserInterventionRequired bit,
 @QueuedTime int = 0,
 @JobSize int = 0,
 @JobId numeric(18,0)
)
AS

DECLARE @printer_reboot_count int,
        @PrinterId int

BEGIN
  SET NOCOUNT ON


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

IF @Pages <= 0
  select @pages = 1
  
IF @Printed = 1 AND @PagesPrinted <= 0
  select @PagesPrinted = @Pages
  
SELECT @PrinterId = dbo.Printers.Id,
       @printer_reboot_count = ISNULL([Reboot Count],1)
   FROM dbo.Printers
  WHERE dbo.Printers.Name = @PrinterName
  
    IF ISNULL(@JobId,0) > 0
        BEGIN
        
        update dbo.[Print Jobs] 
            SET [User name] =@Username,
                [Document Name] = @Document,
                [Machine name] = @MachineName,
                [Pages] = @Pages,
                [Copies] = @Copies,
                Deleted = @Deleted,
                Printed = @Printed,
                [Pages Printed] = @PagesPrinted
             where dbo.[Print Jobs].[Printer Id] = @PrinterId
               and dbo.[Print Jobs].[Job Id] = @JobId
               and dbo.[Print Jobs].[Printer Reboot Count] = @printer_reboot_count
            
             /* Record this print job event */
             INSERT INTO dbo.[Print Job Events]
                    (
                     [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 
                   @PrinterId,
                   @printer_reboot_count,
                   @JobId,
                   @Submitted,
                    3, --JOB_SET event
                    @Paused, --Paused
                    @Deleted, --Deleted
                    @Deleting, --Deleting
                    @Printed, --Printed
                    @Printing, --Printing
                    @Offline, --Offline
                    @PaperOut, --Paper Out
                    @UserInterventionRequired, --User intervention required
                    0,  --Position
                    @PagesPrinted,  -- Pages Printed
                    @QueuedTime,
                    @JobSize

        END 
   
END

Last edited Oct 15, 2008 at 9:04 PM by Merrion, version 1

Comments

No comments yet.