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

CREATE PROCEDURE 
       [dbo].[KAAS_FetchDiaryAttachments]
      (@DIARYID Integer)
/*******************************************************************************************************
* This procedure is used load the data for TaskAddAction page Attachment Grid.						   *
*                                                                                                      *
* Stored Procedure Name	: [dbo].[KAAS_FetchDiaryAttachments]										   *
* Copied from			: [dbo].[ky_NETCCFetchDiaryAttachments]										   *
*                                                                                                      *
* Modification History	:                                                                              *
* 2019-04-12	Vinodhan K		Created                                                                *
*******************************************************************************************************/ 
AS
BEGIN

SET NOCOUNT ON

DECLARE @Handler VARCHAR(10)

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

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

INSERT
  INTO [dbo].[DiaryAttachmentAccess]
      ([TrackReference],
       [Handler],
       [Comment])
SELECT [DAT].[TrackReference],
       @Handler,
       'Listed for Action'
  FROM [dbo].[DiaryAttachments] [DAT]
 WHERE [DAT].[DiaryID] = @DIARYID

  SELECT [DAT].[DIARYID]                                                                   AS [DIARYID],
         [DAT].[TRACKREFERENCE]                                                            AS [TRACKREFERENCE],
         RTRIM(ISNULL([DAT].[CASECODE], ''))                                               AS [CASECODE],
         RTRIM(ISNULL([DAT].[NAME], ''))                                                   AS [NAME],
         RTRIM(ISNULL([DAT].[DOCUMENT], ''))                                               AS [DOCUMENT],
         CASE WHEN [UNC].[UNC] IS NULL
                   THEN RTRIM(ISNULL([DAT].[FILEPATH], ''))
              ELSE   [UNC].[UNC]
                   + SUBSTRING(RTRIM(ISNULL([DAT].[FILEPATH],
                                            '')),
                               3,
                                 LEN(RTRIM(ISNULL([DAT].[FILEPATH],
                                                  '')))
                               - 2) END                                                    AS [FILEPATH],
         CASE WHEN [UNC].[UNC] IS NULL
                   THEN RTRIM(ISNULL([DAT].[FILEPATH], ''))
              ELSE   [UNC].[UNC]
                   + SUBSTRING(RTRIM(ISNULL([DAT].[FILEPATH],
                                            '')),
                               3,
                                 LEN(RTRIM(ISNULL([DAT].[FILEPATH],
                                                  '')))
                               - 2) END                                                    AS [LFILEPATH],
         RTRIM(ISNULL([DAT].[TYPE], ''))                                                   AS [TYPE],
         RTRIM(ISNULL([DCL].[CLASSCODE], ''))                                              AS [DOCCLASS],
         RTRIM(ISNULL([DCL].[CLASSDESCRIPTION], ''))                                       AS [DOCCLASSDESC],
         CONVERT(VARCHAR, [DAT].[DATEENTERED], 20)                                         AS [DATEENTERED],
         RTRIM(ISNULL([DAT].[SYSTEM], 'N'))                                                AS [SYSTEM],
         RTRIM(ISNULL([DAT].[DOCCLASS], ''))                                               AS [DOCCLASS],
         RTRIM(ISNULL([DAT].[ENTEREDBY], ''))                                              AS [ENTEREDBY],
         CONVERT(VARCHAR, [DAT].[LASTACCESSDATE], 20)                                      AS [LASTACCESSDATE],
         RTRIM(ISNULL([DAT].[LASTACCESSBY], ''))                                           AS [LASTACCESSBY],
         RTRIM(ISNULL([DAT].[DICTATIONFILE], ''))                                          AS [DICTATIONFILE],
         RTRIM(ISNULL([DAT].[SOURCE], ''))                                                 AS [Source],
         RTRIM(ISNULL([DAT].[Fees], ''))                                                   AS [Fees],
         RTRIM(ISNULL([DAT].[Outlay], ''))                                                 AS [Outlay],
         [DV].[CurrentVersion]                                                             AS [CurrentVersion],
         RTRIM(ISNULL([DX].[IMDocID], ''))                                                 AS [IMDocID],
         [MAT].[uniqueid]                                                                  AS [UniqueId]
    FROM [dbo].[DiaryAttachments] [DAT]
         INNER JOIN [dbo].[diary] [DIA]
                    INNER JOIN [dbo].[matters] [MAT]
                            ON [MAT].[Code] = [DIA].[CASECODE]
                 ON [DIA].[ActionID] = [DAT].[DiaryID]
         CROSS APPLY (SELECT ISNULL(MAX([DAV].[Version]), 0) + 1 AS [CurrentVersion]
                        FROM [dbo].[DiaryAttachmentVersioning] [DAV]
                       WHERE [DAV].[TrackReference] = [DAT].[TRACKREFERENCE]) DV
         LEFT OUTER JOIN [dbo].[DocumentClasses] [DCL]
                      ON [DCL].[CLASSCODE] = [DAT].[DOCCLASS]
         LEFT OUTER JOIN [dbo].[UNCAlias] [UNC]
                      ON SUBSTRING([DAT].[FilePath], 2, 1) = ':'
                     AND [UNC].[Drive] = SUBSTRING([DAT].[FilePath], 1, 1)
         LEFT OUTER JOIN [dbo].[DAIMXRef] [DX]
                      ON [DX].[TrackReference] = [DAT].[TRACKREFERENCE]
   WHERE [DAT].[DIARYID] = @DIARYID
ORDER BY [DAT].[TRACKREFERENCE] DESC

SET NOCOUNT OFF
END

GO