SQL
USE [PrintMonitor]
GO
/****** Object:  StoredProcedure [dbo].[OnJobDeleted]    Script Date: 10/15/2008 20:59:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[OnJobDeleted]
/* -- [OnJobDeleted]------------------------------------------------------------------
 * Triggered when a job is deleted and a PRINTER_CHANGE_JOB_DELETED 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 @PrinterId int,
        @Printer_Reboot_count 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
             /* 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,
                    2, --JOB_DELETED 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


                UPDATE dbo.[Print Jobs]
                   SET Deleted = @Deleted,
                       Printed = @Printed,
                       [Pages Printed] = @PagesPrinted
                 where [Printer Id] = @PrinterId
                   and [Job Id] = @JobId
                   and [Printer Reboot Count] = @printer_reboot_count
                   
        END 
   

END

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

Comments

No comments yet.