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


CREATE PROCEDURE KAAS_SP_CreateDiary
                (@pStatus       Int=0,
                 @pDate         datetime,
                 @pDueDate      datetime,
                 @pCaseCode     varchar(20),
                 @pActionCode   char(15),
                 @pActionType   char(1),
                 @pActionStatus char(3),
                 @pPriority     char(1),
                 @pHighlighted  char(1),
                 @pBillable     int,
                 @pWorkProcess  int,
                 @pPublish      char(1),
                 @pFnCode       varchar(10),
                 @pTeamCode     varchar(10),
                 @pText1        varchar(MAX),
                 @pAttachments  char(1),
                 @pEmailAddress varchar(MAX),
                 @pAddressTo    varchar(MAX),
                 @pccTo         varchar(MAX),
                 @pbccTo        varchar(MAX),
                 @pemail        char(1),
                 @pSubject      varchar(MAX),
                 @pProcessType  char(1),
                 @pLocation     varchar(50),
                 @pDuration     int)
AS

/*******************************************************************************************************
* Create Diary Step suitable for Add Task as well as assigning a mail to Case, and applying a duration.* 
*                                                                                                      *
* Stored Procedure Name : [dbo].[KAAS_SP_CreateDiary]												   *
* Copied from			: [dbo].[KAAS_CreateDiaryStep] in AllDiaryStepProcedures                      *
*                                                                                                      *
* Modification History:                                                                              *
* 2019-04-26   Vinodhan K        Created  ([dbo].[KAAS_CreateDiaryStep])                                                                             *
* 2021-10-26   Sadiq             Copied from [dbo].[KAAS_CreateDiaryStep]                                                             *
*******************************************************************************************************/

BEGIN
    SET NOCOUNT ON
    DECLARE @strcasecode        nVarChar(20)
    DECLARE @intAssignno        int
    DECLARE @SubjectLen         int
    DECLARE @CaseCodeLen        int
    DECLARE @FELen              int
    DECLARE @TeamLen            int
    DECLARE @dDate              datetime
    DECLARE @pTime              char(10)
    DECLARE @pEndTime           char(10)
    DECLARE @dDueDate           datetime
    DECLARE @pDueTime           char(10)
    DECLARE @continue           int
    DECLARE @error              varchar(1000)
    DECLARE @NewActionID        int
    DECLARE @KYC                char(1)
    DECLARE @FirmDomainAddress  VARCHAR(50)

    SELECT @FirmDomainAddress = CONVERT(VARCHAR(50), ISNULL([SET].[KeyValue], '')) 
      FROM [dbo].[Settings] [SET]
     WHERE [SET].[KeyName] = 'FirmDomainAddress'
     
    DECLARE @CheckAddress VARCHAR(MAX)

    SET @CheckAddress = @pEmailAddress

    If CHARINDEX('@', @CheckAddress) > 1
      BEGIN
        SET @CheckAddress = SUBSTRING(@CheckAddress, CHARINDEX('@', @CheckAddress), LEN(@CheckAddress) - CHARINDEX('@', @CheckAddress) + 1)
        IF SUBSTRING(@CheckAddress, LEN(@CheckAddress), 1) IN ('>', ')', ']')
          BEGIN
            SET @CheckAddress = SUBSTRING(@CheckAddress, 1, LEN(@CheckAddress) - 1)
          END
        SET @CheckAddress = '%;' + @CheckAddress + ';%'
      END
    ELSE
      BEGIN
        SET @CheckAddress = ''
      END

    SET @FirmDomainAddress = ISNULL(@FirmDomainAddress, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')

    IF LEN(@FirmDomainAddress) > 0
      BEGIN
        IF SUBSTRING(@FirmDomainAddress, 1, 1) <> '@'
          BEGIN
            SET @FirmDomainAddress = '@' + @FirmDomainAddress
          END
      END
    
    IF @CheckAddress <> ''
      BEGIN
        IF ';' + @FirmDomainAddress + ';' LIKE @CheckAddress
          BEGIN
            SET @pProcessType = 'O'
          END
      END

    SET @continue = 0
    SET @error = ''
    SET @NewActionID = 0

    BEGIN TRANSACTION

    Begin Try
      SET @pDuration = ISNULL(@pDuration, 0)
      IF (@pDuration <= 0)
        BEGIN
          SET @pDuration = 30
        END
        SET @pDuration = @pDuration * 60
    End Try
    Begin Catch
      SET @continue = 1
      SET @error = 'invalid duration information'
      SET @NewActionID = 0
    End Catch

    IF (@continue = 0)
      BEGIN
        BEGIN TRY
          -- Get only the DATE portion of the date that was passed in
          IF (@pDate is null)
            SET @pDate = GETDATE()

          IF (@pDueDate is null)
            SET @pDueDate = @pDate

          SET @dDate = convert(datetime, convert(varchar, @pDate, 112))
          SET @pTime = convert(char(10), (1000 * convert(int, DATEDIFF(ms, @dDate, @pDate) / 10000)) + 1)
          SET @pEndTime = convert(char(10), (@pDuration * 100) + (1000 * convert(int, DATEDIFF(ms, @dDate, @pDate) / 10000)) + 1)

          SET @dDueDate = convert(datetime, convert(varchar, @pDueDate, 112))
          SET @pDueTime = convert(char(10), @pDuration + (10 * convert(int, DATEDIFF(ms, @dDueDate, @pDueDate) / 10000)))
        END TRY
        BEGIN CATCH
          SET @continue = 2
          SET @error = 'error processing date parameters'
          SET @NewActionID = 0
        END CATCH
      END

    IF (@continue = 0)
      BEGIN
        BEGIN TRY
          select @SubjectLen = CASE WHEN sc.xtype = 35
                                         THEN 150
                                    ELSE sc.[length] END
            from sysobjects so
                 inner join syscolumns sc
                         on sc.[id] = so.[id]
                        and sc.[name] = 'subject'
           where so.[name] = 'diary'

          select @CaseCodeLen = sc.[length]
            from sysobjects so
                 inner join syscolumns sc
                         on sc.[id] = so.[id]
                        and sc.[name] = 'casecode'
           where so.[name] = 'diary'

          select @FELen = sc.[length]
            from sysobjects so
                 inner join syscolumns sc
                         on sc.[id] = so.[id]
                        and sc.[name] = 'fncode'
           where so.[name] = 'diary'

          select @TeamLen = sc.[length]
            from sysobjects so
                 inner join syscolumns sc
                         on sc.[id] = so.[id]
                        and sc.[name] = 'teamcode'
           where so.[name] = 'diary'
        END TRY
        BEGIN CATCH
          SET @continue = 3
          SET @error = 'invalid duration information'
          SET @NewActionID = 0
        END CATCH
      END

-- Get CaseCode From Matters Table -----------------------------------------------------------------------------------------------------------------------------------------

    IF (@continue = 0)
      BEGIN
        BEGIN TRY
          SET @strCaseCode = NULL

          SELECT Top 1
                 @strCaseCode = MAT.[Code]
            From [dbo].[Matters] MAT
           WHERE MAT.[code] = @pCaseCode

          SET @strCaseCode = RTRIM(Isnull(@strCaseCode, ''))
          Set @pFNCode = RTRIM(@pFNCode)
          Set @pTeamCode = RTRIM(@pTeamCode)

          IF (@strCaseCode = '')
            BEGIN
              SET @continue = 4
              BEGIN TRY
                SET @pCaseCode = RTRIM(ISNULL(@pCaseCode, ''))
              END TRY
              BEGIN CATCH
                SET @pCaseCode = ''
              END CATCH
              SET @error = 'Invalid Case Code: ' + @pCaseCode
              SET @NewActionID = 0
            END

          IF (@continue = 0)
            BEGIN
              IF (LEN(@strCaseCode) < 2)
                BEGIN
                  SET @continue = 4
                  SET @error = 'Invalid parameters. Case Code too short'
                  SET @NewActionID = 0
                END
            END

          IF (@continue = 0)
            BEGIN
              IF (LEN(@strCaseCode) > @CaseCodeLen)
                BEGIN
                  SET @continue = 4
                  SET @error = 'Invalid parameters. Case Code too long'
                  SET @NewActionID = 0
                END
            END

          IF (@continue = 0)
            BEGIN
              IF (LEN(@pFNCode) > @FELen)
                BEGIN
                  SET @continue = 4
                  SET @error = 'Invalid parameters. FE code too long'
                  SET @NewActionID = 0
                END
            END

          IF (@continue = 0)
            BEGIN
              IF (LEN(@pTeamCode) > @TeamLen)
                BEGIN
                  SET @continue = 4
                  SET @error = 'Invalid parameters. TEAM code too long'
                  SET @NewActionID = 0
                END
            END

          -- Set Publish and Subject -----------------------------------------------------------------------------------------------------------------------------------
          IF (@continue = 0)
            BEGIN
              Select @pPublish = CASE WHEN RTRIM(ISNULL(@pPublish, '')) <> ''
                                           THEN @pPublish
                                      ELSE TAC.[PUBLISHER] END,
                     @KYC = TAC.[KYC],
                     @pProcessType = CASE WHEN RTRIM(ISNULL(@pProcessType, '')) <> ''
                                               THEN @pProcessType
                                          ELSE TAC.[PROCESSTYPE] END
                From TemplateActions TAC
               where TAC.[ACTIONCODE] = @pActionCode

              IF (RTRIM(IsNull(@pPublish, '')) = '')
                BEGIN
                  SET @pPublish = 'N'
                END

              IF (RTRIM(IsNull(@pProcessType, '')) = '')
                BEGIN
                  SET @pProcessType = ''
                END

              SET @pSubject = RTRIM(@pSubject)

              IF LEN(@pSubject) > @SubjectLen
                BEGIN
                  SET @pSubject = Substring(@pSubject, 1, @SubjectLen)
                END
            END

          -- Truncate potentially long fields -----------------------------------------------------------------------------------------------------------------------------------
          IF (@continue = 0)
            BEGIN
              SET @pEmailAddress = CONVERT(varchar(150), ISNULL(@pEmailAddress, ''))
              SET @pSubject = CONVERT(varchar(500), ISNULL(@pSubject, ''))
              SET @pAddressTo = CONVERT(varchar(2000), ISNULL(@pAddressTo, ''))
              SET @pccTo = CONVERT(varchar(2000), ISNULL(@pccTo, ''))
              SET @pbccTo = CONVERT(varchar(2000), ISNULL(@pbccTo, ''))
            END
        END TRY
        BEGIN CATCH
          SET @continue = 5
          SET @error = 'Invalid parameters Publish, ProcessType or Subject'
          SET @NewActionID = 0
        END CATCH

      END

    IF (@continue = 0)
      BEGIN
        BEGIN TRY
          -- Pino 2015-09-30 Start
          EXEC @NewActionID = [dbo].[ky_NETGetNextActionID]
          --SELECT @NewActionID = ISNULL(MAX(DIA.[ActionID]), 0) + 1
          --  FROM [dbo].[diary] DIA
          -- Pino 2015-09-30 End
        END TRY
        BEGIN CATCH
          SET @continue = 8
          SET @error = convert(varchar(1000), 'Error establishing new Action ID, ' + Error_Message())
          SET @NewActionID = 0
        END CATCH
      END

-- Write Diary Record -----------------------------------------------------------------------------------------------------------------------------------------
    IF (@continue = 0)
      BEGIN
        BEGIN TRY
          INSERT INTO [dbo].[Diary]
                     ([ActionID],
                      [Status],
                      [CaseCode],
                      [ActionCode],
                      [ActionStatus],
                      [PRIORITY],
                      [HIGHLIGHTED],
                      [BILLABLE],
                      [WORKPROCESS],
                      [FnCode],
                      [TeamCode],
                      [Text1],
                      [Attachments],
                      [EmailAddress],
                      [AddressTo],
                      [Location],
                      [ccTo],
                      [bccTo],
                      [email],
                      [Subject],
                      [Publish],
                      [ActionType],
                      [ProcessType],
                      [DYSTARTTIME],
                      [DYENDTIME],
                      [Date],
                      [DUEDATE],
                      [KYC])
               VALUES(@NewActionID,
                      @pStatus,
                      @pCaseCode,
                      @pActionCode,
                      @pActionStatus,
                      @pPriority,
                      @pHighlighted,
                      @pBillable,
                      @pWorkProcess,
                      @pFnCode,
                      @pTeamCode,
                      convert(text, @pText1),
                      @pAttachments,
                      @pEmailAddress,
                      @pAddressTo,
                      @pLocation,
                      @pccTo,
                      @pbccTo,
                      @pemail,
                      @pSubject,
                      @pPublish,
                      @pActionType,
                      @pProcessType,
                      @pTime,
                      @pEndTime,
					  dbo.KAAS_FNConvertDateToUTC(@pDate, @pFnCode),
					  dbo.KAAS_FNConvertDateToUTC(@pDueDate, @pFnCode),
                      @KYC)
        END TRY
        BEGIN CATCH
          SET @continue = 6
          SET @error = convert(varchar(1000), 'Error inserting Diary Entry, ' + Error_Message())
          SET @NewActionID = 0
        END CATCH
      END

-- Write the diary delegation record ------------------------------------------------------------
    IF (@continue = 0)
      BEGIN
        BEGIN TRY
          INSERT INTO [DiaryDelegations]
                     ([ACTIONID],
                      [HANDLER],
                      [TEAM],
                      [DATE],
                      [TIME],
                      [DATER],
                      [TIMER],
                      [DUEDATE],
                      [DUETIME],
                      [REVIEW],
                      [STATUS],
                      [OWNER],
                      [DELEGATE],
                      [DELEGATESTATUS],
                      [ActionType],
                      [FromHandler],
                      [ReturnedBy],
                      [DelType])
               VALUES(@NewActionID,
                      @pFnCode,
                      @pTeamCode,
                      @dDate,
                      @pTime,
                      @dDate,
                      @pTime,
                      @dDueDate,
                      @pDueTime,
                      ' ',
                      @pStatus,
                      'Y',
                      @pFnCode,
                      0,
                      @pActionType,
                      ' ',
                      ' ',
                      'Created')
        END TRY
        BEGIN CATCH
          SET @continue = 7
          SET @error = convert(varchar(1000), 'Error inserting Diary Delegation, ' + Error_Message())
          SET @NewActionID = 0
        END CATCH
      END
-- Commit Transaction ------------------------------------------------------------------------------------------------------------------

    IF (@continue = 0)
      BEGIN
        COMMIT
      END
    ELSE
      BEGIN
        SET @NewActionID = 0
        ROLLBACK
      END

    SELECT @continue AS [ErrorCode],
           @error AS [ErrorMessage],
           @NewActionID AS [NewActionID]

END

GO

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

Create Procedure
       [dbo].[KAAS_SP_GetAzureDATInfo]
      (@TrackReference INT,
       @Version        INT)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_SP_GetAzureDATInfo]  Copied from  KAAS_GetAzureDATInfo                                                                  *
 * Modification History                                                                                      *
 * 2018-11-12   Pino Carafa       Created                                                                    *
 * 2019-11-19   Pino Carafa       Add Link to DiaryAttachmentOriginalMatterLinks                             *
 * 2020-02-06   Pino Carafa       Filepath loses colon for file paths that are really local, and that don't  *
 *                                have a drive letter covered by [dbo].[UNCAlias]                            *
 * 2020-05-26   Pino Carafa       Add Matter Code and Client Code                                            *
 * 2020-06-08   Arun Vasu         Copied from Ky_NETSPGetAzureDATInfo                                             *
 * 2020-06-08   Sadiq			  Copied from KAAS_GetAzureDATInfo                                                                                                                        *
 *************************************************************************************************************/
BEGIN
  DECLARE @FilePath       VARCHAR(500)
  DECLARE @Document       VARCHAR(100)
  DECLARE @DotPos         INT
  DECLARE @SlashPos       INT
  DECLARE @FileExtension  VARCHAR(10)
  DECLARE @MatterUniqueID INT
  DECLARE @IsCurrent      BIT
  DECLARE @Type           VARCHAR(10)
  DECLARE @MatterCode     VARCHAR(20)
  DECLARE @ClientCode     VARCHAR(10)

  SELECT @TrackReference = [DAT].[TrackReference],
         @Document = RTRIM(ISNULL([DAT].[DOCUMENT], '')),
         @FilePath = REVERSE(RTRIM(ISNULL([DAV].[Filepath], ISNULL([DAT].[FILEPATH], '')))),
         @MatterUniqueID = ISNULL([DAOML].[OriginalUniqueID], [MAT].[uniqueid]),
         @Version = CASE WHEN [DAV].[Version] IS NULL
                              THEN [CV].[CurrentVersion]
                         ELSE [DAV].[Version] END,
         @IsCurrent = CASE WHEN [DAV].[Version] IS NULL
                               THEN 1
                          ELSE 0 END,
         @Type = [DAT].[TYPE],
         @MatterCode = RTRIM(ISNULL([MAT].[Code], '')),
         @ClientCode = RTRIM(ISNULL([MAT].[ClientCode], ''))
    FROM [dbo].[DiaryAttachments] [DAT]
         LEFT OUTER JOIN [dbo].[DiaryAttachmentOriginalMatterLinks] [DAOML]
                      ON [DAOML].[TrackReference] = [DAT].[TrackReference]
         INNER JOIN [dbo].[diary] [DIA]
                    INNER JOIN [dbo].[matters] [MAT]
                            ON [MAT].[Code] = [DIA].[CASECODE]
                 ON [DIA].[ACTIONID] = [DAT].[DiaryID]
         LEFT OUTER JOIN [dbo].[DiaryAttachmentVersioning] [DAV]
                      ON [DAV].[TrackReference] = [DAT].[TrackReference]
                     AND [DAV].[Version] = @Version
         CROSS APPLY (SELECT ISNULL(MAX([DAV2].[Version]), 0) + 1 AS [CurrentVersion]
                        FROM [dbo].[DiaryAttachmentVersioning] [DAV2]
                       WHERE [DAV2].[TrackReference] = [DAT].[TrackReference]) [CV]
   WHERE [DAT].[TrackReference] = @TrackReference

  SET @DotPos = CHARINDEX('.', @FilePath)
  SET @SlashPos = CHARINDEX('\', @FilePath)

  IF @DotPos < 1
    BEGIN
      SET @FileExtension = ''
    END
  ELSE
    IF @DotPos < @SlashPos
      BEGIN
        SET @FileExtension = REVERSE(SUBSTRING(@FilePath, 1, @DotPos))
      END
    ELSE
      BEGIN
        SET @FileExtension = ''
      END

  IF @Document = ''
    BEGIN
      IF @SlashPos > 0
        BEGIN
          SET @Document = REVERSE(SUBSTRING(@FilePath, 1, @SlashPos - 1))
          SET @Document = SUBSTRING(@Document, 1, LEN(@Document) - LEN(@FileExtension))
          IF @Document LIKE '%_' + CONVERT(VARCHAR(10), @TrackReference)
            BEGIN
              SET @Document = SUBSTRING(@Document, 1, LEN(@Document) - LEN('_' + CONVERT(VARCHAR(10), @TrackReference)))
            END
        END
      ELSE
        BEGIN
          SET @Document = 'File'
        END
    END

  SET @FilePath = REVERSE(@FilePath)
  IF LEN(@FilePath) > 1
    BEGIN
      IF SUBSTRING(@FilePath, 2, 1) = ':'
        BEGIN
          IF (SELECT [UNC].[UNC] FROM [dbo].[UNCAlias] [UNC] WHERE [UNC].[Drive] = SUBSTRING(@FilePath, 1, 1)) IS NOT NULL
            BEGIN
              SELECT @FilePath = ISNULL((SELECT [UNC].[UNC] FROM [dbo].[UNCAlias] [UNC] WHERE [UNC].[Drive] = SUBSTRING(@FilePath, 1, 1)), SUBSTRING(@FilePath, 1, 1)) + SUBSTRING(@FilePath, 3, LEN(@FilePath) - 2)
            END
        END
    END

  SELECT @TrackReference AS [TrackReference],
         @Document       AS [Document],
         @FileExtension  AS [FileExtension],
         @MatterUniqueID AS [MatterUniqueID],
         @FilePath       AS [FilePath],
         @Version        AS [RetrievedVersion],
         @IsCurrent      AS [IsCurrent],
         @Type           AS [Type],
         @MatterCode     AS [MatterCode],
         @ClientCode     AS [ClientCode]
END

GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_SP_GetCaseCodeUsingTrackRef'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KAAS_SP_GetCaseCodeUsingTrackRef]
  END
GO
CREATE PROCEDURE
       [dbo].[KAAS_SP_GetCaseCodeUsingTrackRef]
      (@TrackReference INT)
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_SP_GetCaseCodeUsingTrackRef]                                                                  *
 *                                                                                                           *
 * Use to get the Case code using Document Track Reference									                 *
 *                                                                                                           *                                                                                  *
 * Modification History                                                                                      *             
 * 2022-03-28   Sadiq             Created                                                                    *
 *************************************************************************************************************/
BEGIN

SELECT [CASECODE] 
	FROM DiaryAttachments 
	WHERE TrackReference=@TrackReference



END
GO

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

CREATE PROCEDURE
       [dbo].[KAAS_SP_GetCaseFolders]
      (@matter                   VARCHAR(20),
       @documentnamingmethod     VARCHAR(1))
AS
BEGIN

/*******************************************************************************************************
* Returns the default location for any documents stored against a case/matter						   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_SP_GetCaseFolders]												   *
* Copied from			: [dbo].[KAAS_GetCaseFolders]												   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-11	Vinodhan K		Created   ([dbo].[KAAS_GetCaseFolders])                                                                             *
* 2021-10-26	Sadiq 		    Copied from [dbo].[KAAS_GetCaseFolders]                                                                *
 *******************************************************************************************************/

DECLARE @continue                 INT
DECLARE @strDefaultFolder         VARCHAR(255)
DECLARE @error                    VARCHAR(1000)
DECLARE @strFileName              VARCHAR(255)
DECLARE @strClientCode            VARCHAR(20)
DECLARE @strMatterNumber          VARCHAR(20)
DECLARE @storagemethod            VARCHAR(1)
DECLARE @docFolder                VARCHAR(255)

SET @continue = 0

SELECT @storagemethod = SCF.[StorageMethod]
  FROM [dbo].[SystemConfig] SCF

SET @storagemethod = ISNULL(@storagemethod, 'C')

SELECT @strClientCode = MAT.[ClientCode],
       @strMatterNumber = MAT.[Matter]
  FROM [dbo].[matters] MAT
 WHERE MAT.[Code] = @matter

    IF (@continue = 0)
      BEGIN
        BEGIN TRY
          SELECT @strDefaultFolder = (SELECT RTRIM(ISNULL(SC.[SCWPFOLDER], ''))
                                        FROM [dbo].[SystemConfig] SC)
          SET @strDefaultFolder = ISNULL(@strDefaultFolder, '')

          IF (@strDefaultFolder = '')
            BEGIN
              SET @continue = 4
              SET @error = 'SCWPFOLDER not SET in System Config'
            END
        END TRY
        BEGIN CATCH
          SET @continue = 5
          SET @error = 'Error retrieving SCWPFOLDER: ' + Error_Message()
        END CATCH
      END

    IF (@continue = 0)
      BEGIN
        BEGIN TRY
          SELECT @strFileName = RTRIM(ISNULL(CLT.[DCOUMENTFOLDER], ''))
            FROM [dbo].[client] CLT
           WHERE CLT.[CLCODE] = @strClientCode

          SET @strFileName = RTRIM(ISNULL(@strFileName, ''))

          IF (@strFileName = '')
            BEGIN
              SET @strFileName = @strDefaultFolder + '\' + @strClientCode

              UPDATE CLT
                 SET CLT.[DCOUMENTFOLDER] = @strFileName
                FROM [dbo].[client] CLT
               WHERE CLT.[CLCODE] = @strClientCode
            END
        END TRY
        BEGIN CATCH
          SET @continue = 1
          SET @error = 'Failed to establish Client Document Folder'
        END CATCH
    END

IF (@storagemethod = 'D')
BEGIN
  SET @docFolder = @strDefaultFolder + '\' +
                   SUBSTRING(CONVERT(VARCHAR(5), 10000 + YEAR(GETDATE())), 2, 4) +
                   SUBSTRING(CONVERT(VARCHAR(3), 100 + MONTH(GETDATE())), 2, 4)
END
IF (@storagemethod = 'C')
BEGIN
  SET @docFolder = @strFileName + '\' + @strMatterNumber
END

SET @docFolder = ISNULL(@docFolder, '')

SELECT RSL.[Continue],
       RSL.[Error],
       @strDefaultFolder AS [SCWPFolder],
       CASE WHEN UNCS.[Drive] IS NULL
                 THEN @strDefaultFolder
            ELSE UNCS.[UNC] + SUBSTRING(@strDefaultFolder, 3, LEN(@strDefaultFolder) - 2) END AS [UNCSCWPFolder],
       RSL.[ClientFolder],
       CASE WHEN UNC.[Drive] IS NULL
                 THEN RSL.[ClientFolder]
            ELSE UNC.[UNC] + SUBSTRING(RSL.[ClientFolder], 3, LEN(RSL.[ClientFolder]) - 2) END AS [UNCClientFolder],
       @docFolder As [DocumentFolder],
       CASE WHEN UNCD.[Drive] IS NULL
                 THEN @docFolder
            ELSE UNCD.[UNC] + SUBSTRING(@docFolder, 3, LEN(@docFolder) - 2) END AS [UNCDocumentFolder],
       @storagemethod AS [StorageMethod],
       @documentnamingmethod AS [DocumentNamingMethod]
  FROM (SELECT @continue AS [Continue],
               RTRIM(ISNULL(@strFileName, '')) AS [ClientFolder],
               RTRIM(ISNULL(@error, '')) AS [Error]) RSL
       LEFT OUTER JOIN [dbo].[UNCAlias] UNC
                    ON UNC.[Drive] = CASE WHEN SUBSTRING(RSL.[ClientFolder], 2, 1) = ':'
                                               THEN LEFT(RSL.[ClientFolder], 1)
                                          ELSE '' END
       LEFT OUTER JOIN [dbo].[UNCAlias] UNCS
                    ON UNCS.[Drive] = CASE WHEN SUBSTRING(@strDefaultFolder, 2, 1) = ':'
                                                THEN LEFT(@strDefaultFolder, 1)
                                           ELSE '' END
       LEFT OUTER JOIN [dbo].[UNCAlias] UNCD
                    ON UNCD.[Drive] = CASE WHEN SUBSTRING(@docFolder, 2, 1) = ':'
                                                THEN LEFT(@docFolder, 1)
                                           ELSE '' END
END

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

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

/*******************************************************************************************************
* Fetches the file path using Track Reference No.                                                      *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_SP_GetFilePath] copied from [dbo].[KAAS_GetFilePath]												           *
*                                                                                                      *
* Modification History	:                                                                            *
* 2019-09-26	Vinodhan K		Created  ([dbo].[KAAS_GetFilePath]	)                                                                              *
* 2021-10-26	Sadiq		    copied from [dbo].[KAAS_GetFilePath]                                                                *
********************************************************************************************************/

BEGIN
    SELECT
	    [FilePath]
	FROM
	    [DiaryAttachments]
	WHERE
	    [TrackReference] = @TrackReference
END

GO
IF EXISTS
  (SELECT TOP 1 1
     FROM SYS.[objects] SO
    WHERE SO.[name] = N'KAAS_SP_GetNextDocumentVersion'
      AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KAAS_SP_GetNextDocumentVersion]
  END
GO
CREATE PROCEDURE
       [dbo].[KAAS_SP_GetNextDocumentVersion]
      (@TrackReference INT,
       @Comment        VARCHAR(MAX))
AS
/*************************************************************************************************************
 *                                                                                                           *
 * [dbo].[KAAS_SP_GetNextDocumentVersion]                                                                    *
 *                                                                                                           *
 * Inserts a row for the next version of a document and return the new row including the id. After calling   *
 * this procedure the calling software will copy files to their new locations, and if that fails it will     *
 * delete the newly created line as a "rollback" using ky_NETSPDeleteDocumentVersion                         *
 *                                                                                                           *                                                                                  *
 * Modification History                                                                                      *
 * 2017-06-30   Pino Carafa       Created                                                                    *
 * 2017-10-02   Pino Carafa       Access Logging                                                             *
 * 2019-03-20   Pino Carafa       Azure compatibility                                                        *
 * 2019-10-09   John Ginnane      KEYD-6197 - Replaced sysprocesses code with function                       *
 * 2020-10-19   Pino Carafa       Make sure it doesn't try to write a null timestamp                         *
 * 2021-01-22   Kowshik.R         Stored Procedure taken from ky_NETSPGetNextDocumentVersion                 *
 * 2022-03-28   Sadiq             Stored Procedure taken from KEYHM_GetNextDocumentVersion                                                                                                            *
 *************************************************************************************************************/
BEGIN

SET NOCOUNT ON

DECLARE @MaxVersion INT
DECLARE @FilePath VARCHAR(255)

SELECT @MaxVersion = ISNULL(MAX([DATV].[Version]), 0)
  FROM [dbo].[DiaryAttachments] [DAT]
       LEFT OUTER JOIN [dbo].[DiaryAttachmentVersioning] [DATV]
                    ON [DATV].[TrackReference] = [DAT].[TrackReference]
 WHERE [DAT].[TrackReference] = @TrackReference

SELECT @FilePath
     = CASE WHEN UNC.[UNC] IS NULL
                 THEN RTRIM(ISNULL(ISNULL([DXR].[FilePath],
                                          [DAT].[FilePath]), ''))
            ELSE   UNC.[UNC]
                 + SUBSTRING(RTRIM(ISNULL(ISNULL([DXR].[FilePath],
                                                 [DAT].[FilePath]),
                                          '')),
                             3,
                             LEN(RTRIM(ISNULL(ISNULL([DXR].[FilePath],
                                                     [DAT].[FilePath]),
                                              ''))) - 2) END
  FROM [dbo].[DiaryAttachments] [DAT]
       LEFT OUTER JOIN [dbo].[DAIMXRef] [DXR]
                    ON [DXR].[TrackReference] = [DAT].[TrackReference]
       LEFT OUTER JOIN [dbo].[UNCAlias] UNC
                    ON SUBSTRING(ISNULL([DXR].[FilePath],
                                        [DAT].[FilePath]),
                                 2,
                                 1) = ':'
                   AND UNC.[Drive] = SUBSTRING(ISNULL([DXR].[FilePath],
                                                      [DAT].[FilePath]),
                                               1,
                                               1)
 WHERE [DAT].[TrackReference] = @TrackReference

SELECT @FilePath = REVERSE(@FilePath)
DECLARE @DOTPOS INT

SET @DOTPOS = CHARINDEX('.', @FilePath)
IF @DOTPOS = 0
  BEGIN
    SET @FilePath = REVERSE(@FilePath) + '_V' + CONVERT(VARCHAR, @MaxVersion)
  END
ELSe
  BEGIN
    SET @FilePath = REVERSE(SUBSTRING(@FilePath, @DOTPOS + 1, LEN(@FilePath) - @DotPos)) + '_V' + CONVERT(VARCHAR, @MaxVersion + 1) + REVERSE(SUBSTRING(@FilePath, 1, @DOTPOS))
  END

DECLARE @Handler VARCHAR(10)

SET @Handler = [dbo].[ky_NETFNGetLoggedOnHandler]()

INSERT
  INTO [dbo].[DiaryAttachmentAccess]
      ([TrackReference],
       [Handler],
       [Comment])
SELECT @TrackReference,
       @Handler,
       'Inserted New Version ' + Convert(VARCHAR(10), @MaxVersion + 1)
  FROM [dbo].[DiaryAttachments] [DAT]
 WHERE [DAT].[TrackReference] = @TrackReference

INSERT
  INTO [dbo].[DiaryAttachmentVersioning]
      ([TrackReference],
       [IMDocID],
       [Version],
       [FilePath],
       [Comments],
       [TimeStamp],
       [Handler])
SELECT [DAT].[TrackReference]                                                  AS [TrackReference],
       ISNULL([DXR].[IMDocID], '')                                             AS [IMDocID],
       @MaxVersion + 1                                                         AS [Version],
       @FilePath                                                               AS [FILEPATH],
       @Comment                                                                AS [Comments],
       ISNULL(ISNULL([DAT].[LASTACCESSDATE], [DAT].[DATEENTERED]), GETDATE())  AS [TimeStamp],
       [DAT].[ENTEREDBY]                                                       AS [Handler]
  FROM [dbo].[DiaryAttachments] [DAT]
       LEFT OUTER JOIN [dbo].[DAIMXRef] [DXR]
                    ON [DXR].[TrackReference] = [DAT].[TrackReference]
 WHERE [DAT].[TrackReference] = @TrackReference

  SELECT TOP 1
         [DATV].[id],
         [DATV].[TrackReference],
         [DATV].[IMDocID],
         [DATV].[Version],
         [DATV].[Comments],
         [DATV].[FilePath],
         [DATV].[TimeStamp],
         [DATV].[Handler]
    FROM [dbo].[DiaryAttachmentVersioning] [DATV]
   WHERE [DATV].[TrackReference] = @TrackReference
ORDER BY [DATV].[Version] DESC



SET NOCOUNT OFF

END
GO

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

CREATE PROCEDURE 
       [dbo].[KAAS_SP_GetNextTrackReferenceInDiary]
AS
/*******************************************************************************************************
* Gets next available track referene number                                                            *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_SP_GetNextTrackReferenceInDiary]                         *
* Copied from			: [dbo].[KAAS_GetNextTrackReferenceInDiaryAttachments]                            *
*                                                                                                      *
* Modification History	:                                                                             *
* 2019-04-10	Vinodhan K		Created  ([dbo].[KAAS_GetNextTrackReferenceInDiaryAttachments])                                                                                *
* 2021-10-26	Sadiq		    Copied from [dbo].[KAAS_GetNextTrackReferenceInDiaryAttachments]                                               *
 *******************************************************************************************************/
BEGIN
	DECLARE @TrackReference INT

	EXEC @TrackReference = [dbo].[ky_NETGetNextTrackReference]

	SELECT @TrackReference AS [TRACKREFERENCE]
	--SELECT
	--	ISNULL(MAX(TRACKREFERENCE), 0)+1    AS    TRACKREFERENCE
	--FROM
	--	dbo.DiaryAttachments  --ORDER BY TRACKREFERENCE DESC
END
GO
IF EXISTS (SELECT TOP 1 1 FROM SYS.[objects] SO WHERE SO.[name] = 'KAAS_SP_InsertDiaryAttachment' AND SO.[type] = 'P')
  BEGIN
    DROP PROCEDURE [dbo].[KAAS_SP_InsertDiaryAttachment]
  END
GO

CREATE PROCEDURE 
       [dbo].[KAAS_SP_InsertDiaryAttachment]
      (@DIARYID            INT,
       @TRACKREFERENCE     INT,
       @CASECODE           VARCHAR(20),
       @NAME               VARCHAR(10),
       @DOCUMENT           VARCHAR(100),
       @FILEPATH           VARCHAR(255),
       @TYPE               VARCHAR(5),
       @DOCCLASS           VARCHAR(3),
       @DATEENTERED        DATETIME,
       @ENTEREDBY          VARCHAR(8),
       @LASTACCESSDATE     DATETIME,
       @LASTACCESSBY       VARCHAR(8),
       @SYSTEM             VARCHAR(1),
       @DICTATIONFILE      VARCHAR(255),
       @Source             VARCHAR(20),
       @Fees               INT,
       @Outlay             INT,
       @Result             VARCHAR(255) OUTPUT)    
AS
/*******************************************************************************************************
* This stored procedure is used to insert or update the Client Case Action                             *
* Attachment information.IF ID is already exist then it will update that record. otherwise             *
* new record will insert.                                                                              *    
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_SP_InsertDiaryAttachment]                                           *
* Copied from			: [dbo].[KAAS_InsertDiaryAttchement]                                        *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-10	Vinodhan K		Created                                                                *
* 2020-05-26    Arun V          Modified the type extenaion size from 3 to 5                           *
* 2021-10-06    Arun V          Set Context info                                                                                *
* 2021-10-26	Sadiq           Copied from  [dbo].[KAAS_InsertDiaryAttchement]                                                       *
 *******************************************************************************************************/
BEGIN

	SET NOCOUNT OFF


	DECLARE @HandlerBin VARBINARY(128)
	SET @HandlerBin = CONVERT(VARBINARY(128), convert(varchar(8),@LASTACCESSBY))
	SET CONTEXT_INFO @HandlerBin


	IF NOT EXISTS
	(SELECT 1 
		FROM [dbo].[DiaryAttachments] DAT
		WHERE DAT.[TRACKREFERENCE] = @TRACKREFERENCE) 
	BEGIN
	SET @FILEPATH = dbo.KAAS_FN_GETFILEPATH (SUBSTRING(@FILEPATH,LEN(@FILEPATH)-CHARINDEX ('\',REVERSE(@FILEPATH))+2,LEN(@FILEPATH)) ,@FILEPATH )  
	IF NOT(@FILEPATH = 'NOCHANGE')  
		BEGIN
		INSERT 
		INTO [dbo].[DiaryAttachments]
			([DIARYID],
				[TRACKREFERENCE],
				[CASECODE],
				[NAME],
				[DOCUMENT],
				[FILEPATH],
				[TYPE],
				[DOCCLASS],
				[DATEENTERED],
				[ENTEREDBY],
				[LASTACCESSDATE],
				[LASTACCESSBY],
				[SYSTEM],
				[DICTATIONFILE],
				[Source],
				[Fees],
				[Outlay])
		VALUES(@DIARYID,
				@TRACKREFERENCE,
				@CASECODE,
				@NAME,
				@DOCUMENT,
				@FILEPATH,
				@TYPE,
				@DOCCLASS,
				@DATEENTERED,
				@ENTEREDBY,
				@LASTACCESSDATE,
				@LASTACCESSBY,
				@SYSTEM,
				@DICTATIONFILE,
				@Source,
				@Fees,
				@Outlay)
		IF EXISTS(SELECT * FROM diary WHERE ACTIONID = @DIARYID AND ACTIONTYPE = 'U')
		BEGIN
			DECLARE @UserName VARCHAR(20)
			DECLARE @Description VARCHAR(500)
			SET @UserName = (SELECT NAME FROM Handlers WHERE Code = @ENTEREDBY)
			SET @Description = 'Attachment Added: ' + CHAR(13) + CHAR(10)
							+  'Date:  ' + CONVERT(VARCHAR(11),CONVERT(DATETIME,@DATEENTERED),106) + CHAR(13) + CHAR(10)
						+ 'Document Name: ' + @DOCUMENT +CHAR(13) + CHAR(10)
						+ 'Document Class: ' + @DOCCLASS + CHAR(13) + CHAR(10)
			INSERT INTO
				[dbo].[UndertakingLog]
				([UndertakingID],
				[FieldChanges],
				[FromValue],
				[ToValue],
				[ByWho],
				[ModifiedDate])
			VALUES(@DIARYID,
				'New Documents Added',
				@Description,
				' - NIL - ',
				@UserName,
				CONVERT(DATETIME,GETDATE(),112))
		END
               
		SET @Result = @FILEPATH 
		END
		ELSE  
		BEGIN  
			SET @Result = 'NOCHANGE'  
		END  
	END 
	--ELSE 
	--  BEGIN
	--    UPDATE DAT
	--       SET DAT.[TRACKREFERENCE]           =    @TRACKREFERENCE,
	--           DAT.[CASECODE]                 =    @CASECODE,
	--           DAT.[NAME]                     =    @NAME,
	--           DAT.[DOCUMENT]                 =    @DOCUMENT,
	--           DAT.[FILEPATH]                 =    @FILEPATH,
	--           DAT.[TYPE]                     =    @TYPE,
	--           DAT.[DOCCLASS]                 =    @DOCCLASS,
	--           DAT.[DATEENTERED]              =    @DATEENTERED,
	--           DAT.[ENTEREDBY]                =    @ENTEREDBY,
	--           DAT.[LASTACCESSDATE]           =    @LASTACCESSDATE,
	--           DAT.[LASTACCESSBY]             =    @LASTACCESSBY,
	--           DAT.[SYSTEM]                   =    @SYSTEM,
	--           DAT.[DICTATIONFILE]            =    @DICTATIONFILE,
	--           DAT.[Source]                   =    @Source,
	--           DAT.[Fees]                     =    @Fees,
	--           DAT.[Outlay]                   =    @Outlay
	--      FROM [dbo].[DiaryAttachments] DAT
	--     WHERE DAT.[TRACKREFERENCE]           =    @TRACKREFERENCE
	--  END

END
GO

IF NOT((SELECT SERVERPROPERTY('Edition') AS	[Edition]) = 'SQL Azure')
BEGIN
	IF EXISTS
		(SELECT TOP 1 1
		 FROM SYS.[objects] SO
		 WHERE SO.[name] = N'KAAS_SP_SaveDiaryAttachement'
		 AND SO.[type] = 'P')
	BEGIN
		DROP PROCEDURE [dbo].[KAAS_SP_SaveDiaryAttachement]
	END

	DECLARE @NCOMMAND NVARCHAR(MAX)
	
	SET @NCOMMAND = 
	N'CREATE PROCEDURE [dbo].[KAAS_SP_SaveDiaryAttachement] (
		@FilePath	VARCHAR(1000),
		@FileName	VARCHAR(1000),
		@FileData	VARBINARY(MAX),
		@Result		INT		OUTPUT
	   )
	AS
	/*******************************************************************************************************
	* To save the attachment to the physical path                                                          *
	*                                                                                                      *
	* Stored Procedure Name	: [dbo].[KAAS_SP_SaveDiaryAttachement]    
	*						   copied from [dbo].[KAAS_SaveDiaryAttachement]                               *
	*                                                                                                      *
	* Modification History	:                                                                              *
	* 2019-04-12	Vinodhan K		Created   ([dbo].[KAAS_SaveDiaryAttachement])                                                                            *
	* 2021-10-26	Sadiq 			Copied from [dbo].[KAAS_SaveDiaryAttachement]                          *
	 *******************************************************************************************************/
	BEGIN
		DECLARE @FullPath NVARCHAR (2000);
		SET @FullPath = @FilePath + @FileName;

		DECLARE @Obj INT;
 
		SET NOCOUNT ON

		BEGIN TRY
			DECLARE @IsFolderExist AS INT
 
			DECLARE @FileResult TABLE
			(
				IsFileExist				INT,
				IsFileDirectory			INT,
				IsParentDirectoryExist	INT
			)
 
			INSERT INTO
				@FileResult
				(
				IsFileExist,
				IsFileDirectory,
				IsParentDirectoryExist
				)
			EXECUTE XP_FILEEXIST @FilePath
     
			SELECT
				@IsFolderExist = IsFileDirectory
			FROM
				@FileResult
     
			--Script to create directory       
			IF(@IsFolderExist = 0)
				BEGIN
					EXECUTE XP_CREATE_SUBDIR @FilePath

					INSERT INTO
						@FileResult
						(
						IsFileExist,
						IsFileDirectory,
						IsParentDirectoryExist
						)
					EXECUTE XP_FILEEXIST @FilePath

					SELECT
						@IsFolderExist = IsFileDirectory
					FROM
						@FileResult
				END


			IF(@IsFolderExist = 1)
				BEGIN
					EXEC SP_OACREATE		''ADODB.Stream'' ,@Obj OUTPUT;
					EXEC SP_OASETPROPERTY	@Obj ,''Type'',1;
					EXEC SP_OAMETHOD		@Obj,''Open'';
					EXEC SP_OAMETHOD		@Obj,''Write'', NULL, @FileData;
					EXEC SP_OAMETHOD		@Obj,''SaveToFile'', NULL, @FullPath, 2;
					EXEC SP_OAMETHOD		@Obj,''Close'';
					EXEC SP_OADESTROY		@Obj;
					SELECT @Result = 1;
					RETURN;
				END
			ELSE
				BEGIN
				SELECT @Result = 0;
					RETURN;
				END
		END TRY
    
		BEGIN CATCH
			EXEC SP_OADESTROY @Obj;
			SELECT @Result = 1;
			RETURN;
		END CATCH
 
	   SET NOCOUNT OFF
	END'

	EXECUTE SP_EXECUTESQL @NCOMMAND
END
GO