IF NOT EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
    WHERE [SO].[name] = 'DiaryAttachmentOriginalMatterLinks'
      AND [SO].[type] = 'U')
  BEGIN
    EXEC ('CREATE TABLE
       [dbo].[DiaryAttachmentOriginalMatterLinks]
      ([TrackReference]      INT            NOT NULL,
       [OriginalUniqueID]    INT            NOT NULL,
       CONSTRAINT [PK_DAOML]
                  PRIMARY KEY CLUSTERED
                 ([TrackReference]))
    ON [PRIMARY]')
  END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM sys.objects [SO]
          LEFT OUTER JOIN sys.indexes [SI]
                       ON [SI].object_id = [SO].object_id
                      AND [SI].[name] = 'IDX_UniqueID'
    WHERE [SO].[name] = 'DiaryAttachmentOriginalMatterLinks'
      AND [SO].[type] = 'U'
      AND [SI].[index_id] IS NULL)
  BEGIN
    EXEC ('    CREATE NONCLUSTERED
     INDEX [IDX_UniqueID]
        ON [dbo].[DiaryAttachmentOriginalMatterLinks]
          ([OriginalUniqueID])
           INCLUDE ([TrackReference])')
  END
GO

DELETE [DAT]
  FROM [dbo].[DiaryAttachments] [DAT]
       LEFT OUTER JOIN [dbo].[Diary] [DIA]
                    ON [DIA].[ActionID] = [DAT].[DiaryID]
 WHERE [DIA].[ActionID] IS NULL
GO

INSERT
  INTO [dbo].[DiaryAttachmentOriginalMatterLinks]
      ([TrackReference],
       [OriginalUniqueID])
SELECT [DAT].[TrackReference],
       [MAT].[UniqueID]
  FROM [dbo].[DiaryAttachments] [DAT]
       INNER JOIN [dbo].[matters] [MAT]
               ON [MAT].[Code] = [DAT].[CASECODE]
       LEFT OUTER JOIN [dbo].[DiaryAttachmentOriginalMatterLinks] [DAOML]
                    ON [DAOML].[TrackReference] = [DAT].[TrackReference]
 WHERE [DAOML].[TrackReference] IS NULL
GO




IF OBJECT_ID('DocumentPopups','U') IS NOT NULL
BEGIN
	IF NOT EXISTS(
		SELECT  TOP 1 1 
		FROM    SYS.columns 
		WHERE   OBJECT_NAME(object_id) = 'DocumentPopups' AND
				[name] = 'Title')
	BEGIN
		ALTER TABLE DocumentPopups
		ADD [Title] VARCHAR(100)
	END	
END

GO



IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_GetFileName'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KAAS_GetFileName]
  END
GO

CREATE PROCEDURE
       [dbo].[KAAS_GetFileName]
       (@TrackReference    INT)
AS

/*******************************************************************************************************
* Fetches the file path using Track Reference No.                                                      *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_GetFileName]											           *
*                                                                                                      *
* Modification History	:                                                                              *
* 2020-06-19	Arun V		Created                                                                *
********************************************************************************************************/

BEGIN
    SELECT
	    [Document]
	FROM
	    [DiaryAttachments]
	WHERE
	    [TrackReference] = @TrackReference
END
GO

IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KEYHM_GetFileName'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KEYHM_GetFileName]
  END
GO

CREATE PROCEDURE
       [dbo].[KEYHM_GetFileName]
       (@TrackReference    INT)
AS

/*******************************************************************************************************
* Fetches the file path using Track Reference No.                                                      *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KEYHM_GetFileName]											           *
*                                                                                                      *
* Modification History	:                                                                              *
* 2020-06-19	Arun V		Created                                                                *
********************************************************************************************************/

BEGIN
    SELECT
	    [Document]
	FROM
	    [DiaryAttachments]
	WHERE
	    [TrackReference] = @TrackReference
END
GO


IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_LogDocumentAccess'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KAAS_LogDocumentAccess]
  END
GO

CREATE PROCEDURE
       [dbo].[KAAS_LogDocumentAccess]
      (@TrackReference          INT,
       @Handler                 VARCHAR(10),
       @Comment                 VARCHAR(2000))
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[ky_NETSPDocumentAccess]                                                                            *
 *                                                                                                           *
 * Log Document Access                                                                                       *
 *                                                                                                           *
 * Compatibility information - PLEASE update older versions if necessary to ensure the compatible software   *
 *                                    remains fully functional                                               *
 *   *****************************************************************************************************   *
 *   *                                                                                                   *   *
 *   * Supersedes:                              -                                                        *   *
 *   * First compatible version:                5.5.1.2                                                  *   *
 *   * Last compatible software version:        -                                                        *   *
 *   * Superseded by:                           -                                                        *   *
 *   *                                                                                                   *   *
 *   *****************************************************************************************************   *
 *                                                                                                           *
 * Modification History                                                                                      *
 * 2017-10-02   Pino Carafa       Created                                                                    *
 * 2018-08-09   Pino Carafa       Performance Improvement                                                    *
 * 2019-03-20   Pino Carafa       Azure Compatibility                                                        *
 * 2019-05-05	Arun V			  Copied from ky_NETSPDocumentAccess                                                                                                           *
 *************************************************************************************************************/
BEGIN

SET @TrackReference = ISNULL(@TrackReference, 0)

IF @TrackReference = 0
  BEGIN
    RETURN
  END

IF (SELECT TOP 1 1 FROM [dbo].[DiaryAttachments] [DAT] WITH (NOLOCK) WHERE [DAT].[TrackReference] = @TrackReference) IS NULL
  BEGIN
    RETURN
  END

IF EXISTS
  (SELECT TOP 1 1
     FROM [dbo].[Handlers] [HAN] WITH (NOLOCK)
    WHERE [HAN].[CODE] = RTRIM(ISNULL(@Handler, '')))
  BEGIN
    SELECT @Handler     = RTRIM(ISNULL([HAN].[Code], ''))
      FROM [dbo].[Handlers] [HAN] WITH (NOLOCK)
     WHERE [HAN].[CODE] = RTRIM(ISNULL(@Handler, ''))
  END
ELSE
  BEGIN
    SET @Handler = ''
  END

IF RTRIM(ISNULL(@Handler, '')) = ''
  BEGIN
    SELECT @Handler = CONVERT(VARCHAR(10),
                              CASE WHEN CHARINDEX(CHAR(0),
                                                  CONVERT(VARCHAR(MAX),
                                                          [P].[context_info])) > 0
                                        THEN SUBSTRING(CONVERT(VARCHAR(MAX),
                                                               [P].[context_info]),
                                                       1,
                                                       CHARINDEX(CHAR(0),
                                                                 CONVERT(VARCHAR(MAX),
                                                                 [P].[context_info])) - 1)
                                   ELSE CONVERT(VARCHAR(MAX),
                                                [P].[context_info]) END)
      FROM [master].[dbo].[sysprocesses] P
     WHERE P.[spid] = @@SPID
  END

SET @Handler = RTRIM(ISNULL(@Handler, 'ADM'))

SET @Comment        = CASE WHEN RTRIM(ISNULL(@Comment, '')) = ''
                                THEN 'No reason given'
                           ELSE RTRIM(ISNULL(@Comment, '')) END

  INSERT
    INTO [dbo].[DiaryAttachmentAccess]
        ([TrackReference],
         [Handler],
         [Comment])
  VALUES(@TrackReference,
         @Handler,
         @Comment)

END

GO


IF OBJECT_ID(N'KAAS_GetAPIVersion',N'P')IS NOT NULL
	DROP PROCEDURE KAAS_GetAPIVersion
GO
CREATE PROCEDURE KAAS_GetAPIVersion
AS
/**********************************************************
KAAS_GetAPIVersion

This procedure is used to fetch the latest version of the script

Modification History:
---------------------

DATE		NAME		DESCRIPTION
--------------------------------------------------------------
2019AUG22	ARUN		Created	
2019JUN25   Arun V      Added SQL Server
***********************************************************/
BEGIN
	DECLARE @SqlServerVersion NVARCHAR(500)

	SELECT @SqlServerVersion = @@VERSION
	SELECT TOP 1 
		[Version] as DBVersion, 
		[ReleaseDate] as ReleaseDate, 
		@SqlServerVersion as SQLVersion
	FROM 
		[Common].[Release] 
	ORDER BY 
		ReleaseID DESC
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KAAS_GetSPHistory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[KAAS_GetSPHistory]
GO

CREATE PROCEDURE [dbo].[KAAS_GetSPHistory] (
                @SPTYPE   VARCHAR(20)
)
AS
/*************************************************************************************
*	
*	Stored Procedure Name : [dbo].[KAAS_GetSPHistory]						   
*
*	To get list of all stored procedures belonging to a category provided
*	Categories - KEYSAAS : Keyhouse web app
*				 KEYHM: Keyhouse Mobile
*	
*	Modifications: 
2020-06-15      Aakif M      Created KAAS_GetSPHistory
2020-06-25      Arun V       To get all stored procedure from DB using like search.
******************************************************************************************/ 
BEGIN
DECLARE @SEARCHTEXT VARCHAR(20)
	
IF (@SPTYPE IS NOT NULL AND @SPTYPE != '')
	BEGIN
	  SET @SEARCHTEXT = @SPTYPE + '%';
	  SELECT ROW_NUMBER()  OVER (ORDER BY [create_date]) as SerialNo,
			 [NAME] AS [Name],
			 [type_desc] as [Description],
		     [create_date] as [DateOfCreation],
			 [modify_date] as [ModificationDate]
	  FROM sys.objects 
	  WHERE 
		[NAME] LIKE @SEARCHTEXT
	  ORDER BY 
		[create_date],
		[modify_date],
		[type_desc],
		[Name]
	END
END

GO