IF NOT EXISTS(SELECT TOP 1 1  FROM Settings WHERE  KeyName = 'PrintMandatoryfields')
BEGIN
	Insert into Settings values('PrintMandatoryfields', 'True')
END

GO


IF OBJECT_ID(N'PrintJobTracker',N'U') IS NULL
BEGIN
	 CREATE TABLE [PrintJobTracker](
	 [RecordID] [int] IDENTITY(1,1) NOT NULL,
	 [MatterCode] [VARCHAR](20) NULL,
	 [Handler] [VARCHAR](50) NOT NULL,
	 [JobID] [Varchar](100) NOT NULL,
	 [FileName] [Varchar](200) NOT NULL,
	 [DeviceName] [Varchar](max) NOT NULL,
	 [NoOfPages] [int] NOT NULL,
	 [LoggedInUser] [Varchar](300) NULL, 
	 [LastUpdated] [Datetime] NOT NULL,
	 CONSTRAINT [PK_PrintJobTracker] PRIMARY KEY (RecordID)
)
END


GO

IF OBJECT_ID(N'[ky_NETCONInsertPrintJobDetails]',N'P')IS NOT NULL
	DROP PROCEDURE [ky_NETCONInsertPrintJobDetails]
GO 
CREATE PROCEDURE [ky_NETCONInsertPrintJobDetails] (
	 @CASECODE		VARCHAR(20)
	,@HANDLER		VARCHAR(50)
	,@JOBID			VARCHAR(100)
	,@FILENAME		VARCHAR(200)
	,@DEVICENAME	VARCHAR(MAX)
	,@NOOFPAGES		INT
	,@LOGGEDINUSER	VARCHAR(300)	
	,@FUNCTION		VARCHAR(50) 
	)
	/*****************************************************************************

Stored Procedure Name: [ky_NETCONInsertPrintJobDetails]
Description: 	This stored procedure is used to insert or update the Print Job
				tracker details

			
**********************************************************************************/
AS
BEGIN
SET NOCOUNT ON

		DECLARE @JOBNAME AS VARCHAR(max)
		Set @JOBNAME= @DEVICENAME + CONVERT(VARCHAR(10),@JOBID)
	
		IF (SELECT TOP 1 1 FROM PRINTJOBTRACKER WHERE DEVICENAME+CONVERT(VARCHAR(10),JOBID)=@JOBNAME)IS  NULL
		BEGIN
			INSERT INTO
				PRINTJOBTRACKER	
				([MATTERCODE],
				[HANDLER],
				[JOBID],
				[FILENAME],
				[DEVICENAME],
				[NOOFPAGES],
				[LOGGEDINUSER], 
				[LASTUPDATED])
			VALUES 
			(@CASECODE, @HANDLER, @JOBID, @FILENAME, @DEVICENAME, @NOOFPAGES, @LOGGEDINUSER, GetDate())	
	   END
	   
	   ELSE
	   BEGIN
			UPDATE
				PRINTJOBTRACKER
			SET
				[MATTERCODE]=@CASECODE,
				[HANDLER]=@HANDLER,			
				[LOGGEDINUSER]=@LOGGEDINUSER,
				[LASTUPDATED]=GETDATE()
			WHERE
				DEVICENAME+CONVERT(VARCHAR(10),JOBID)=@JOBNAME
	   END

SET NOCOUNT OFF
END

GO

IF OBJECT_ID(N'[ky_NETFetchCaseForPrintJob]',N'P')IS NOT NULL
	DROP PROCEDURE [ky_NETFetchCaseForPrintJob]
GO
CREATE PROCEDURE [ky_NETFetchCaseForPrintJob] 

	(@JOBNAME			VARCHAR(MAX))
	/*****************************************************************************        
        
Stored Procedure Name: ky_NETFetchCaseForPrintJob  
Description:  this procedure used to fetch Matter of a document printed.   

*****************************************************************************/
AS
BEGIN
SET NOCOUNT ON
    SELECT
        MATTERCODE
    FROM
        PRINTJOBTRACKER
    WHERE
       (DEVICENAME + CONVERT(VARCHAR(20),JOBID))=@JOBNAME
SET NOCOUNT OFF
END
GO
