SQL
USE [PrintMonitor]
GO
/****** Object:  StoredProcedure [dbo].[Automated_Document_Categorisation]    Script Date: 10/15/2008 20:37:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Automated_Document_Categorisation]	
AS
	declare @last_run_date DATETIME
	
	-- GET THE DATE/TIME THIS PROC WAS LAST RUN
	SELECT @last_run_date =  isnull(convert(datetime,[Parameter Value]), '1900-01-01')
	                        from [PUMA Parameters]
	                        where [Parameter Name] = 'Last Document Run'
	                        
	
	-- Add new categorised documents according to field restriction on 'User name'
	INSERT INTO [Categorised Documents]
	  SELECT a.[Category Type],
	         a.[Category Name],
	         b.[Printer Id],
	         b.[Printer Reboot Count],
	         b.[Job Id],
	         b.Submitted
	    FROM dbo.[Automatic Document Categorisation] a,
	         dbo.[Print Jobs] b
	    WHERE b.Submitted >= @last_run_date
	      AND a.[Document Field Name] = 'User name'
	      AND 
	        (
	          (
	            a.[Restriction Type] = 'Contains'
	          AND
	            b.[User name] like '%' + a.[Restriction Value] + '%'
	           ) 
	         OR
	          (
	            a.[Restriction Type] = 'Equals'
	          AND
	            b.[User name] =  a.[Restriction Value] 
	          )
	         OR
	          (
	            a.[Restriction Type] = 'Not Equals'
	           AND
	            b.[User name] != a.[Restriction Value]
	          )
	         )
	         
	-- Add new categorised documents according to field restriction on 'Document name'
	INSERT INTO [Categorised Documents]
	  SELECT a.[Category Type],
	         a.[Category Name],
	         b.[Printer Id],
	         b.[Printer Reboot Count],
	         b.[Job Id],
	         b.Submitted
	    FROM dbo.[Automatic Document Categorisation] a,
	         dbo.[Print Jobs] b
	    WHERE b.Submitted >= @last_run_date
	      AND a.[Document Field Name] = 'Document Name'
	      AND 
	        (
	          (
	            a.[Restriction Type] = 'Contains'
	          AND
	            b.[Document Name] like '%' + a.[Restriction Value] + '%'
	           ) 
	         OR
	          (
	            a.[Restriction Type] = 'Equals'
	          AND
	            b.[Document Name] =  a.[Restriction Value] 
	          )
	         OR
	          (
	            a.[Restriction Type] = 'Not Equals'
	           AND
	            b.[Document Name] != a.[Restriction Value]
	          )
	         )  
	
	-- Add new categorised documents according to field restriction on 'Machine Name'
	INSERT INTO [Categorised Documents]
	  SELECT a.[Category Type],
	         a.[Category Name],
	         b.[Printer Id],
	         b.[Printer Reboot Count],
	         b.[Job Id],
	         b.Submitted
	    FROM dbo.[Automatic Document Categorisation] a,
	         dbo.[Print Jobs] b
	    WHERE b.Submitted >= @last_run_date
	      AND a.[Document Field Name] = 'Machine Name'
	      AND 
	        (
	          (
	            a.[Restriction Type] = 'Contains'
	          AND
	            b.[Machine Name] like '%' + a.[Restriction Value] + '%'
	           ) 
	         OR
	          (
	            a.[Restriction Type] = 'Equals'
	          AND
	            b.[Machine Name] =  a.[Restriction Value] 
	          )
	         OR
	          (
	            a.[Restriction Type] = 'Not Equals'
	           AND
	            b.[Machine Name] != a.[Restriction Value]
	          )
	         )
	         
	-- UPDATE THE DATE/TIME THIS PROC WAS LAST RUN
	IF @last_run_date = '1900-01-01' 
	   INSERT INTO [PUMA Parameters] VALUES
	   ('Last Document Run', convert(varchar(255),getdate()), 'The date and time the last time the stored proc Automated_Document_Categorisation as run')
    ELSE
       UPDATE [PUMA Parameters]
          SET [Parameter Value] = convert(varchar(255), getdate())
        WHERE [Parameter Name] = 'Last Document Run'                        
	                        
	
	RETURN 

Last edited Oct 15, 2008 at 8:38 PM by Merrion, version 1

Comments

No comments yet.