IF NOT EXISTS
  (SELECT *
     FROM sys.objects SO
    WHERE SO.[name] = 'MessageHashXRef'
      AND SO.[type] = 'U')
BEGIN
  CREATE TABLE 
         [dbo].[MessageHashXRef]
        ([id]                      int identity(1, 1),
         [Hash]                    varchar(32),
         [Sent]                    datetime,
         [TrackReference]          int,
         CONSTRAINT [PK_MessageHashXRef] PRIMARY KEY CLUSTERED
                   ([id] ASC))
      ON [PRIMARY]
         
END
GO

IF NOT EXISTS
  (SELECT *
     FROM sys.objects SO
          inner join sys.indexes SI
                  ON SI.[object_id] = SO.[object_id]
                 AND SI.[name] = 'IX_TrackReference'
    WHERE SO.[name] = 'MessageHashXRef'
      AND SO.[type] = 'U')
BEGIN
  CREATE NONCLUSTERED INDEX 
         [IX_TrackReference] 
      ON [dbo].[MessageHashXRef] 
        ([TrackReference]         ASC)
         INCLUDE ([Hash],
                  [Sent])
      ON [PRIMARY]
END
GO

IF NOT EXISTS
  (SELECT *
     FROM sys.objects SO
          inner join sys.indexes SI
                  ON SI.[object_id] = SO.[object_id]
                 AND SI.[name] = 'IX_HashSent'
    WHERE SO.[name] = 'MessageHashXRef'
      AND SO.[type] = 'U')
BEGIN
  CREATE NONCLUSTERED INDEX 
         [IX_HashSent] 
      ON [dbo].[MessageHashXRef] 
        ([Hash]         ASC,
         [Sent]         ASC)
         INCLUDE ([TrackReference])
      ON [PRIMARY]
END
GO

IF EXISTS
  (SELECT *
     FROM sys.[objects] SO
    WHERE SO.[name] = 'DeleteHashOnDATDelete'
      AND SO.[type] = 'TR')
BEGIN
  DROP TRIGGER [dbo].[DeleteHashOnDATDelete]
END
GO

CREATE TRIGGER [dbo].[DeleteHashOnDATDelete]
    ON [dbo].[DiaryAttachments]
   FOR DELETE
AS
BEGIN
  BEGIN TRY
    DELETE H
      FROM deleted D
           INNER JOIN [dbo].[MessageHashXRef] H
                   ON H.[TrackReference] = D.[TRACKREFERENCE]
  END TRY
  BEGIN CATCH
  END CATCH
END
GO

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

CREATE PROCEDURE
       [dbo].[ky_NETSPSaveDATHash]
      (@TrackReference        INT,
       @HASH                  VARCHAR(32),
       @SENT                  VARCHAR(17))
AS
/*********************************************************************************
 *
 * ky_NETSPSaveDATHash
 * Saves a Subject + Body "Hash" of an outlook message, so that the message can
 * be retrieved even when Outlook fails to determine the Sender's SMTP Address
 * from a .msg file
 *
 *********************************************************************************/
BEGIN
  INSERT
    INTO [dbo].[MessageHashXRef]
        ([Hash],
         [Sent],
         [TrackReference])
  VALUES(@HASH,
         @SENT,
         @TrackReference)
END
GO

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

CREATE PROCEDURE
       [dbo].[ky_NETSPCheckExistingCases]
      (@SENT                  VARCHAR(17),
       @HASH                  VARCHAR(32),
       @MATTER                VARCHAR(20),
       @SenderSMTPAddress     VARCHAR(MAX))
AS
/*************************************************************************
 *
 * ky_NETSPCheckExistingCases
 * Checks whether an e-mail may already have been saved to case
 *
 *************************************************************************/
BEGIN

DECLARE @cases TABLE
       ([MatterCode]             VARCHAR(20),
        [ClientCode]             VARCHAR(20),
        [MatterDescription]      VARCHAR(500),
        [ClientName]             VARCHAR(500),
        [ActionID]               INT,
        [Subject]                VARCHAR(1000))

SET @SenderSMTPAddress = RTRIM(ISNULL(@SenderSMTPAddress, ''))
SET @MATTER = RTRIM(ISNULL(@MATTER, ''))

IF (@SenderSMTPAddress <> '')
  BEGIN
    IF (@MATTER <> '')
      BEGIN
        INSERT
          INTO @cases
              ([MatterCode],
               [ClientCode],
               [MatterDescription],
               [ClientName],
               [ActionID],
               [Subject])
          SELECT RTRIM(ISNULL(MAT.[Code], '')) As [MatterCode],
                 RTRIM(ISNULL(MAT.[ClientCode], '')) As [ClientCode],
                 RTRIM(ISNULL(MAT.[Description], '')) As [MatterDescription],
                 RTRIM(ISNULL(CON.[name], '')) As [ClientName],
                 ISNULL(DIA.[ActionID], 0) AS [ActionID],
                 RTRIM(ISNULL(CONVERT(VARCHAR(1000), DIA.[Subject]), '')) As [Subject]
            FROM [dbo].[matters] MAT
                 INNER JOIN [dbo].[contacts] CON
                         ON CON.[code] = MAT.[ClientCode]
                 OUTER APPLY (SELECT TOP 1 DI2.[ActionID],
                                           DI2.[Subject]
                                FROM [dbo].[diary] DI2
                               WHERE DI2.[CASECODE] = MAT.[CODE]
                                 AND DI2.[TxmDate] >= DATEADD(ss, -60, @SENT)
                                 AND DI2.[TxmDate] <= DATEADD(ss, 60, @SENT)
                                 AND (   DI2.[EMAILADDRESS] LIKE '%<' + @SenderSMTPAddress + '>%'
                                      OR DI2.[EMAILADDRESS] LIKE '%(' + @SenderSMTPAddress + ')%'
                                      OR DI2.[EMAILADDRESS] LIKE '%' + @SenderSMTPAddress + ' <%>%'
                                      OR DI2.[EMAILADDRESS] LIKE '%' + @SenderSMTPAddress + ' (%)%'
                                      OR DI2.[EMAILADDRESS] = @SenderSMTPAddress
                                      OR DI2.[EMAILADDRESS] LIKE [dbo].[ky_InterpretExchangeAddress](@SenderSMTPAddress))
                                 AND DI2.[EMAIL] = 'Y') DIA
           WHERE MAT.[code] = @MATTER
      END
    ELSE
      BEGIN
        INSERT
          INTO @cases
              ([MatterCode],
               [ClientCode],
               [MatterDescription],
               [ClientName],
               [ActionID],
               [Subject])
          SELECT RTRIM(ISNULL(MAT.[Code], '')) As [MatterCode],
                 RTRIM(ISNULL(MAT.[ClientCode], '')) As [ClientCode],
                 RTRIM(ISNULL(MAT.[Description], '')) As [MatterDescription],
                 RTRIM(ISNULL(CON.[name], '')) As [ClientName],
                 ISNULL(MAX(DIA.[ActionID]), 0) AS [ActionID],
                 RTRIM(ISNULL(CONVERT(VARCHAR(1000), DIA.[Subject]), '')) As [Subject]
            FROM [dbo].[diary] DIA
                 INNER JOIN [dbo].[matters] MAT
                            INNER JOIN [dbo].[contacts] CON
                                    ON CON.[code] = MAT.[ClientCode]
                         ON MAT.[code] = DIA.[CASECODE]
           WHERE DIA.[TxmDate] >= DATEADD(ss, -60, @SENT)
             AND DIA.[TxmDate] <= DATEADD(ss, 60, @SENT)
             AND (   DIA.[EMAILADDRESS] LIKE '%<' + @SenderSMTPAddress + '>%'
                  OR DIA.[EMAILADDRESS] LIKE '%(' + @SenderSMTPAddress + ')%'
                  OR DIA.[EMAILADDRESS] LIKE '%' + @SenderSMTPAddress + ' <%>%'
                  OR DIA.[EMAILADDRESS] LIKE '%' + @SenderSMTPAddress + ' (%)%'
                  OR DIA.[EMAILADDRESS] = @SenderSMTPAddress
                  OR DIA.[EMAILADDRESS] LIKE [dbo].[ky_InterpretExchangeAddress](@SenderSMTPAddress))
             AND DIA.[EMAIL] = 'Y'
        GROUP BY RTRIM(ISNULL(MAT.[Description], '')),
                 RTRIM(ISNULL(MAT.[Code], '')),
                 RTRIM(ISNULL(MAT.[ClientCode], '')),
                 RTRIM(ISNULL(CON.[name], '')),
                 RTRIM(ISNULL(CONVERT(VARCHAR(1000), DIA.[Subject]), ''))
      END
  END

  IF NOT EXISTS
    (SELECT *
       FROM @cases)
    BEGIN
      INSERT
        INTO @cases
            ([MatterCode],
             [ClientCode],
             [MatterDescription],
             [ClientName],
             [ActionID],
             [Subject])
      SELECT RTRIM(ISNULL(DIA.[CASECODE], '')) AS [MatterCode],
             RTRIM(ISNULL(MAT.[ClientCode], '')) As [ClientCode],
             RTRIM(ISNULL(MAT.[Description], '')) As [MatterDescription],
             RTRIM(ISNULL(CON.[name], '')) As [ClientName],
             ISNULL(DIA.[ActionID], 0) AS [ActionID],
             RTRIM(ISNULL(CONVERT(VARCHAR(1000), DIA.[Subject]), '')) As [Subject]
        FROM [dbo].[MessageHashXRef] H
             INNER JOIN [dbo].[DiaryAttachments] DAT
                        INNER JOIN [dbo].[diary] DIA
                                   INNER JOIN [dbo].[matters] MAT
                                              INNER JOIN [dbo].[contacts] CON
                                                      ON CON.[code] = MAT.[ClientCode]
                                           ON MAT.[code] = DIA.[CASECODE]
                                ON DIA.[ACTIONID] = DAT.[DIARYID]
                     ON DAT.[TRACKREFERENCE] = H.[TrackReference]
       WHERE H.[Hash] = @Hash
         AND H.[Sent] >= DATEADD(ss, -60, @SENT)
         AND H.[Sent] <= DATEADD(ss, 60, @SENT)
    END

  SELECT *
    FROM @cases
END
GO

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

CREATE PROCEDURE
       [dbo].[ky_NETSPListCaseContacts]
      (@CaseCode VARCHAR(20))
AS
/**********************************************************************************************
 *
 * List Case Contacts for Outlook Add-in
 *
 **********************************************************************************************/
BEGIN

  SELECT RES.[CODE],
         CASE WHEN RES.[NAME] = ''
                   THEN RES.[COMPANY]
              ELSE RES.[NAME] END AS [NAME],
         RES.[COMPANY],
         RES.[EMAIL],
         RES.[NAMECODE],
         RES.[CASECODE],
         RES.[TYPE]
    FROM (SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL(CAN.[CODE], ''))) AS [CODE],
                 CONVERT(VARCHAR(300), RTRIM(ISNULL(CAN.[NAME], ''))) AS [NAME],
                 CONVERT(VARCHAR(50), RTRIM(ISNULL(CAN.[COMPANY], ''))) AS [COMPANY],
                 CONVERT(VARCHAR(100), RTRIM(ISNULL(CAN.[EMAIL], ''))) AS [EMAIL],
                 CONVERT(VARCHAR(10), RTRIM(ISNULL(CC.[NAMECODE], ''))) AS [NAMECODE],
                 CONVERT(VARCHAR(20), RTRIM(ISNULL(CC.[CASECODE], ''))) AS [CASECODE],
                 CONVERT(VARCHAR(20), RTRIM(ISNULL(CAN.[TYPE], ''))) AS [TYPE]
            FROM [dbo].[CaseContacts] CC
                 INNER JOIN [dbo].[CaseAssociatesNames] CAN 
                         ON CAN.[CODE] = CC.[NameCODE]
                        AND RTRIM(ISNULL(CAN.[EMAIL], '')) <> ''
           WHERE CC.[CASECODE] = @CaseCode
          UNION
          SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL(CTC.[CLCODE], ''))) AS CODE,
                 CONVERT(VARCHAR(300), RTRIM(ISNULL(CTC.[FIRSTNAME], '')) + ' ' + RTRIM(ISNULL(CTC.[SURNAME], ''))) AS [NAME], 
                 CONVERT(VARCHAR(50), RTRIM(ISNULL(CTC.[CLNAMECON], ''))) AS [COMPANY],
                 CONVERT(VARCHAR(100), RTRIM(ISNULL(CTC.[EmailAddress], ''))) AS [EMAIL],
                 CONVERT(VARCHAR(10), '') AS [NAMECODE],
                 CONVERT(VARCHAR(20), @CaseCode) AS [CASECODE],
                 CONVERT(VARCHAR(20), 'Client Contact') AS [TYPE]
            FROM [dbo].[matters] MAT
                 INNER JOIN [dbo].[ClientContacts] CTC
                         ON CTC.[CLCODE] = MAT.[ClientCode]
           WHERE MAT.[CODE] = @CaseCode
          UNION
          SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL(CAN.[CODE], ''))) AS [CODE],
                 CONVERT(VARCHAR(300), RTRIM(ISNULL(CAC.[NAME], ''))) AS [NAME],
                 CONVERT(VARCHAR(50), RTRIM(ISNULL(CAN.[COMPANY], ''))) AS [COMPANY],
                 CONVERT(VARCHAR(100), RTRIM(ISNULL(CAC.[EMAIL], ''))) AS [EMAIL],
                 CONVERT(VARCHAR(10), RTRIM(ISNULL(CC.[NAMECODE], ''))) AS [NAMECODE],
                 CONVERT(VARCHAR(20), RTRIM(ISNULL(CC.[CASECODE], ''))) AS [CASECODE],
                 CONVERT(VARCHAR(20), RTRIM(ISNULL(CC.[CONTYPE], ''))) AS [TYPE]
            FROM [dbo].[CaseContacts] CC
                 INNER JOIN [dbo].[CaseAssociatesNames] CAN
                            INNER JOIN [dbo].[CaseAssociatesContacts] CAC
                                    ON CAC.[NAMECODE] = CAN.[CODE]
                                   AND RTRIM(ISNULL(CAC.[EMAIL], '')) <> ''
                         ON CAN.[CODE] = CC.[NameCODE]
           WHERE CC.[CASECODE] = @CaseCode
          UNION
          SELECT CONVERT(VARCHAR(10), RTRIM(ISNULL(CTC.[CODE], ''))) AS CODE,
                 CONVERT(VARCHAR(300), RTRIM(ISNULL(CTC.[Name], ''))) AS [NAME], 
                 CONVERT(VARCHAR(50), '') AS [COMPANY],
                 CONVERT(VARCHAR(100), RTRIM(ISNULL(CTC.[email], ''))) AS [EMAIL],
                 CONVERT(VARCHAR(10), '') AS [NAMECODE],
                 CONVERT(VARCHAR(20), @CaseCode) AS [CASECODE],
                 CONVERT(VARCHAR(20), 'Client') AS [TYPE]
            FROM [dbo].[matters] MAT
                 INNER JOIN [dbo].[Contacts] CTC
                         ON CTC.[CODE] = MAT.[ClientCode]
           WHERE MAT.[CODE] = @CaseCode) RES
ORDER BY CASE WHEN RES.[NAME] = ''
                   THEN RES.[COMPANY]
              ELSE RES.[NAME] END,
         RES.[TYPE]
END
GO

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

CREATE PROCEDURE
       [dbo].[ky_NETSPGetUnhashedMessages]
AS
/**********************************************************************************************
 *
 * Gets the next 1000 emails from DiaryAttachments that need to be hashed
 *
 **********************************************************************************************/
BEGIN
  SELECT TOP 1000
         DAT.[TRACKREFERENCE],
         RTRIM(ISNULL(DAT.[FILEPATH], '')) AS [FilePath]
    FROM [dbo].[DiaryAttachments] DAT
         LEFT OUTER JOIN [dbo].[ReindexStatus] RST
                      ON RST.[TrackReference] = DAT.[TRACKREFERENCE]
                     AND RST.[STATUS] = 4
         LEFT OUTER JOIN [dbo].[MessageHashXRef] H
                      ON H.[TrackReference] = DAT.[TRACKREFERENCE]
   WHERE RTRIM(ISNULL(DAT.[FILEPATH], '')) LIKE '%.MSG'
     AND H.[TrackReference] IS NULL
     AND RST.[TrackReference] IS NULL
ORDER BY DAT.[TRACKREFERENCE] DESC
END
GO



/******************************************************************************************************************
 *
 * 2014-08-15 END
 *
 ******************************************************************************************************************/