IF OBJECT_ID(N'PrintJobTracker',N'U') IS NULL
BEGIN
	 CREATE TABLE [PrintJobTracker](
	 [RecordID] [int] IDENTITY(1,1) NOT NULL,
	 [MatterCode] [NVARCHAR](20) NULL,
	 [Handler] [NVARCHAR](50) NULL,
	 [JobID] [NVarchar](100) NULL,
	 [FileName] [NVarchar](200) NULL,
	 [DeviceName] [NVarchar](500) NULL,
	 [NoOfPages] [int] NULL,
	 [LoggedInUser] [NVarchar](300) NULL, 
	 [LastUpdated] [Datetime] NULL,
	 [Status] [NVARCHAR](20) NULL,
	 [SystemIP] [NVARCHAR](30) NULL,
	 [Machinename] [NVARCHAR](50) NULL,
	 [JobUniqueID] UNIQUEIDENTIFIER DEFAULT NEWID(),
	 [PageSize] NVARCHAR(50) NULL,
	 [IsColorPrinter] BIT NULL,
	 CONSTRAINT [PK_PrintJobTracker] PRIMARY KEY (RecordID)
)
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.columns WHERE OBJECT_NAME(OBJECT_ID) = 'PrintJobTracker' and [name] = 'PageSize')
BEGIN
	ALTER TABLE PrintJobTracker
	ADD PageSize NVARCHAR(50) NULL
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.columns WHERE OBJECT_NAME(OBJECT_ID) = 'PrintJobTracker' and [name] = 'IsColorPrinter')
BEGIN
	ALTER TABLE PrintJobTracker
	ADD IsColorPrinter BIT NULL
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.columns WHERE OBJECT_NAME(OBJECT_ID) = 'PrintJobTracker' and [name] = 'ApplicationType')
BEGIN
	ALTER TABLE PrintJobTracker
	ADD ApplicationType NVARCHAR(60) NULL
END

GO

IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.columns WHERE OBJECT_NAME(OBJECT_ID) = 'PrintJobTracker' and [name] = 'OrginalFileName')
BEGIN
	ALTER TABLE PrintJobTracker
	ADD OrginalFileName NVARCHAR(200) NULL
END

GO


IF OBJECT_ID(N'[ky_NETCONInsertPrintJobDetails]',N'P')IS NOT NULL
	DROP PROCEDURE [ky_NETCONInsertPrintJobDetails]
GO 
CREATE PROCEDURE [ky_NETCONInsertPrintJobDetails] (
	 @CASECODE		NVARCHAR(20)
	,@HANDLER		NVARCHAR(50)
	,@JOBID			NVARCHAR(100)
	,@FILENAME		NVARCHAR(200)
	,@DEVICENAME	NVARCHAR(500)
	,@NOOFPAGES		INT
	,@LOGGEDINUSER	NVARCHAR(300)	
	,@FUNCTION		NVARCHAR(50)
	,@STATUS        NVARCHAR(20)  
    ,@SYSTEMIP      NVARCHAR(30)
    ,@Machinename   NVARCHAR(50)
    ,@PageSize		NVARCHAR(50)
    ,@IsColorPrinter BIT
	)
AS
/*****************************************************************************

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

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

		DECLARE @JOBNAME AS NVARCHAR(500)
		Set @JOBNAME= ISNULL(@DEVICENAME,'') + ISNULL(@JOBID,'')+ISNULL(@LOGGEDINUSER,'')+ISNULL(@Machinename,'')+ISNULL(@FILENAME,'')
	
		IF (SELECT TOP 1 1 FROM PRINTJOBTRACKER 
			WHERE ISNULL(DEVICENAME,'') + ISNULL(JOBID,'')+ISNULL(LOGGEDINUSER,'')+ISNULL(Machinename,'')+ISNULL([OrginalFileName],'')=@JOBNAME)IS  NULL
		BEGIN
			INSERT INTO
				PRINTJOBTRACKER	
				([MATTERCODE],
				[HANDLER],
				[JOBID],
				[FILENAME],
				[DEVICENAME],
				[NOOFPAGES],
				[LOGGEDINUSER], 
				[LASTUPDATED],
				[STATUS],  
                [SYSTEMIP],
                [Machinename],
                [PageSize],
                [IsColorPrinter],
                [OrginalFileName])
			VALUES 
			(
				@CASECODE, 
				@HANDLER, 
				@JOBID, 
				@FILENAME, 
				@DEVICENAME, 
				@NOOFPAGES, 
				@LOGGEDINUSER, 
				GetDate(),
				@STATUS,
				@SYSTEMIP,
				@Machinename,
				@PageSize,
				@IsColorPrinter,
				@FILENAME 
			)	
			
	   END
	   
	  -- ELSE
	  -- BEGIN
			--UPDATE
			--	PRINTJOBTRACKER
			--SET
			--	[MATTERCODE]=Case when @CASECODE <> '' then @CASECODE Else Mattercode END,
			--	[HANDLER]=CASE when @HANDLER <> '' Then @HANDLER Else Handler END,				
			--	--[LASTUPDATED]=GETDATE(),
			--	[STATUS]=CASE WHEN @STATUS <> '' then @STATUS Else [Status] END,
			--	[SystemIP]=CASE WHEN @SYSTEMIP <> '' then @SYSTEMIP Else [SystemIP] END
			--	--[NoOfPages]=CASE WHEN @NOOFPAGES <> 0 then @NOOFPAGES Else [NoOfPages] END						
			--WHERE
			--	DEVICENAME+JOBID+LoggedInUser+ISNULL(Machinename,'')=@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))

AS
/*****************************************************************************        
        
Stored Procedure Name: ky_NETFetchCaseForPrintJob  
Description:  this procedure used to fetch Matter of a document printed.   

*****************************************************************************/
BEGIN
SET NOCOUNT ON
    SELECT
        MATTERCODE,
        Handler,
        JOBID,
        FileName,
        DeviceName,
        NoOfPages,
        LoggedInUser,
        Machinename
    FROM
        PRINTJOBTRACKER
    WHERE
       (DEVICENAME + CONVERT(VARCHAR(20),JOBID))=@JOBNAME
SET NOCOUNT OFF
END

GO

IF OBJECT_ID(N'[ky_NETPGetMostRecentMatterHeader]',N'P')IS NOT NULL
	DROP PROCEDURE [ky_NETPGetMostRecentMatterHeader]
GO
CREATE PROCEDURE [ky_NETPGetMostRecentMatterHeader]
      (@handler      VARCHAR(10))
AS
/*****************************************************************************

 ky_NETPGetMostRecentMatterHeader

 Get the Header details for the Most Recent Matter for a handler

 Modification History:
 2015-05-28 Pino      Add Childcare Column

 *****************************************************************************/
BEGIN
    SELECT TOP 1
           [KHD].[CODE],
           [KHD].[ClientCode],
           [KHD].[MatterNo],
           [KHD].[YourRef],
           [KHD].[DESCRIPTION],
           [KHD].[FECode],
           [KHD].[FEName],
           [KHD].[Name],
           [KHD].[TelNo],
           [KHD].[Address],
           [KHD].[Closed],
           [KHD].[TOOLTIP],
           [KHD].[IsChildcare]
      FROM [dbo].[RecentMatterList] [RML]
           CROSS APPLY [dbo].[ky_NETTFMatterHeaderDetails](RML.[MATTER]) [KHD]
     WHERE [RML].[FEE] = @handler
  ORDER BY [RML].[DateField] DESC
END

GO

IF OBJECT_ID(N'FetchJobCollection',N'P')IS NOT NULL
	DROP PROCEDURE FetchJobCollection
GO
CREATE PROCEDURE FetchJobCollection
(
	@MachineName VARCHAR(50) 
)
AS
/*****************************************************************************

 FetchJobCollection

 This procesure will fetch all the job which status is equal to empty

 Modification History:
 exec FetchJobCollection 'd01smc097'
 *****************************************************************************/
BEGIN
		SELECT 
			[JobUniqueID],  
			[JobID],
			[FileName],
			[DeviceName],
			CONVERT(VARCHAR(20),[NoOfPages]),
			[LoggedInUser],
			[Machinename],
			[status]
			
		FROM 
			dbo.PrintJobTracker  
		WHERE [Status] = '' AND [Machinename] = @MachineName

END
GO


IF NOT EXISTS(SELECT TOP 1 1 FROM SYS.columns WHERE OBJECT_NAME(OBJECT_ID) = 'PrintJobTracker' and [name] = 'TrackReference')
BEGIN
	ALTER TABLE PrintJobTracker
	ADD TrackReference INT NULL
END

GO

IF OBJECT_ID(N'ky_UpdateMatterDetailsForPrintJob',N'P')IS NOT NULL
	DROP PROCEDURE ky_UpdateMatterDetailsForPrintJob
GO
CREATE PROCEDURE ky_UpdateMatterDetailsForPrintJob
(
		@JobId			 NVARCHAR(10),
		@MatterCode		 Nvarchar(20),
		@FileName		 NVARCHAR(100),
		@SystemIP		 NVARCHAR(15),
		@Status			 NVARCHAR(20),
		@JobUnqiueId	 NVARCHAR(50),
		@Handler		 NVARCHAR(20),
		@ApplicationType NVARCHAR(60)
)
AS
/*****************************************************************************

 ky_UpdateMatterDetailsForPrintJob

 This will update matter details from client exe.

 Modification History:
 21Nov2016 Arun Have added Track reference column to update using filename from diary attachment table
 29Nov2016 Arun Have corrected the Trackreference number fetch method
 *****************************************************************************/
BEGIN
	SET NOCOUNT ON
		IF EXISTS(SELECT TOP 1 1 FROM PrintJobTracker where JobUniqueID = @JobUnqiueId  )
		BEGIN
		
		--<<<Start Split Track Reference  Number  Start>>>----
		
			--File Name Test Cases
			--set @FileName= 'Letter to Client enclosing draft lease_272.DOC'  ->> Return 272 as Trackreference Number
			--set @FileName= 'Letter to Client enclosing draft lease272.DOC' ->> Return 0 as Trackreference Number
			--set @FileName= 'Letter to Client enclosing draft lease_272' ->> Return 272 as Trackreference Number
			--set @FileName= 'Letter to Client enclosing draft lease272' ->> Return 0 as Trackreference Number
			--set @FileName= '' ->> Return 0 as Trackreference Number


			DECLARE @FileNameWithExt NVARCHAR(100)
			DECLARE @SplitTrackRefNumber NVARCHAR(50)
			DECLARE @TrackReference NVARCHAR(20)
			SET @TrackReference = 0 

			IF(CHARINDEX('_', REVERSE(@FileName)) <> 0)
			BEGIN
				SET @SplitTrackRefNumber = (SELECT REVERSE(LEFT(REVERSE(@FileName), CHARINDEX('_', REVERSE(@FileName)) -1)))

				SET @TrackReference  = (SELECT SUBSTRING(@SplitTrackRefNumber,0,charindex('.',@SplitTrackRefNumber)))

				IF(@TrackReference = '')
				BEGIN
					SET @TrackReference = @SplitTrackRefNumber 
				END
				
			END

			IF NOT EXISTS(SELECT TOP 1 1 FROM DiaryAttachments where CONVERT(VARCHAR(10),TrackReference) =  @TrackReference)
			BEGIN
				SET @TrackReference = 0
			END
			
		--<<<End Split Track Reference  Number  End>>>----
			

			UPDATE 
				PrintJobTracker 
			SET 
				[MatterCode]		= @MatterCode, 
				[FileName]			= @FileName, 
				[SystemIP]			= @SystemIP, 
				[Status]			= @Status,
				[Handler]			= @Handler,
				[ApplicationType]	= @ApplicationType,
				[TrackReference]	= @TrackReference
			WHERE 
				JobUniqueID = @JobUnqiueId
				
				
			IF EXISTS(SELECT TOP 1 1 FROM RecentMatterList WHERE MATTER = @MatterCode AND FEE = @HANDLER )
			BEGIN
				UPDATE 
					RecentMatterList
				SET 
					DateField = GETDATE()
				WHERE 
					MATTER = @MatterCode AND 
					FEE = @HANDLER 
			END
			ELSE
			BEGIN
				INSERT INTO 
					RecentMatterList
					(
						FEE, 
						MATTER, 
						DateField 
					) VALUES
					(
						@HANDLER, 
						@MatterCode, 
						GETDATE()
					)
			END
		END
	SET NOCOUNT OFF
END
GO