/********************************************************************************

 Table Changes for Document Management - With support for IManage

 ********************************************************************************/
GO

IF NOT EXISTS
  (SELECT *
     FROM [sys].[objects] SO
    WHERE SO.[name] = 'IManageCfg'
      AND SO.[type] = 'U')
BEGIN
    CREATE TABLE
           [dbo].[IManageCfg]
          ([IManageInstance]          varchar(50),
           [IManageDatabase]          varchar(50),
           [MatterField]              varchar(50))

END
GO

IF NOT EXISTS
  (SELECT *
     FROM [sys].[objects] SO
    WHERE SO.[name] = 'IManage'
      AND SO.[type] = 'U')
BEGIN
CREATE TABLE 
       [dbo].[IManage]
      ([ACTIVE]          [int])
END
GO

IF NOT EXISTS
  (SELECT *
     FROM [sys].[objects] SO
    WHERE SO.[name] = 'DAIMList'
      AND SO.[type] = 'U')
BEGIN
  CREATE
   TABLE [dbo].[DAIMList]
        ([ID]              int                  identity(1, 1)     not null,
         [HANDLER]         VARCHAR(10),
         [IMDocID]         VARCHAR(500),
         [Description]     VARCHAR(2000),
         [DocumentDate]    datetime,
         [IMType]          VARCHAR(20),
         [IMClass]         VARCHAR(20),
         [OriginalDesc]    VARCHAR(1000),
         [EmailFrom]       VARCHAR(200),
         [EmailTo]         VARCHAR(1000),
         [EmailSent]       datetime,
         CONSTRAINT [PK_DAIMList] PRIMARY KEY CLUSTERED
                   ([id] ASC))
        
END

IF NOT EXISTS
  (SELECT *
     FROM sys.objects SO
          inner join sys.indexes SI
                  ON SI.[object_id] = SO.[object_id]
                 AND SI.[name] = 'IX_Handler'
    WHERE SO.[name] = 'DAIMList'
      AND SO.[type] = 'U')
BEGIN
  CREATE NONCLUSTERED INDEX 
         [IX_Handler] 
      ON [dbo].[DAIMList] 
        ([HANDLER]         ASC)
         INCLUDE ([IMDocID],
                  [DocumentDate])
      ON [PRIMARY]
END
GO

IF NOT EXISTS
  (SELECT *
     FROM SYS.[objects] SO
          INNER JOIN SYS.[columns] SC
                  ON SC.[object_id] = SO.[object_id]
                 AND SC.[name] = 'IMDOCID'
    WHERE SO.[name] = 'BriefDocuments')
BEGIN
  ALTER
  TABLE [dbo].[BriefDocuments]
    ADD [IMDOCID] varchar(500)
END
GO

/********************************************************************************

 Delete Procedures and Functions that will be re-generated in this script

 ********************************************************************************/
IF EXISTS
  (SELECT *
     FROM SYS.OBJECTS SO
    WHERE SO.[NAME] = 'ky_NETGetCaseFolders'
      AND SO.[TYPE] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[ky_NETGetCaseFolders]
END
GO

 IF EXISTS
  (SELECT *
     FROM SYS.OBJECTS SO
    WHERE SO.[NAME] = 'ky_NETToggleBriefSectionType'
      AND SO.[TYPE] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[ky_NETToggleBriefSectionType]
END
GO

 IF EXISTS
  (SELECT *
     FROM SYS.OBJECTS SO
    WHERE SO.[NAME] = 'ky_NETReorderAllBriefDocuments'
      AND SO.[TYPE] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[ky_NETReorderAllBriefDocuments]
END
GO

IF EXISTS
  (SELECT *
     FROM SYS.OBJECTS SO
    WHERE SO.[NAME] = 'ky_IMMoveBriefDocumentWithinSection'
      AND SO.[TYPE] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[ky_IMMoveBriefDocumentWithinSection]
END
GO

IF EXISTS
  (SELECT *
     FROM SYS.OBJECTS SO
    WHERE SO.[NAME] = 'ky_IMMoveBriefDocuments'
      AND SO.[TYPE] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[ky_IMMoveBriefDocuments]
END
GO

IF EXISTS
  (SELECT *
     FROM SYS.OBJECTS SO
    WHERE SO.[NAME] = 'ky_IMGenerateBrief'
      AND SO.[TYPE] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[ky_IMGenerateBrief]
END
GO

IF EXISTS
  (SELECT *
     FROM SYS.OBJECTS SO
    WHERE SO.[NAME] = 'ky_GetDocumentsIM'
      AND SO.[TYPE] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[ky_GetDocumentsIM]
END
GO

/********************************************************************************

 Create Procedures and Functions

 ********************************************************************************/
GO

/***********************************************************************************
 *
 * ky_GetDocumentsIM
 * gets Documents for Document Manager, with support for IManage integration
 *  CASECODE: keyhouse Case Code
 *  HANDLER:  keyhouse handler
 *  PAGE:     (paging support) page to return
 *  PAGESIZE: (paging support) number of documents per page
 *  ORDER:  --0 = Diary Date DESC
 *          --1 = Document Date DESC
 *          --2 = IMDocID DESC                  - on sites with IManage the only
 *                                                  sensible order is 2
 *  CLASSFILTER: Document class codes separated by ***. Pass in NULL or '' when no
 *               filter is applied. Example: '' - no filter
 *                                           '***ATTNOTES***GEN***' - return
 *                                           IManage "attendance notes" or Keyhouse
 *                                           General documents.
 *  TYPEFILTER: Document types separated by *** Example: '' - no filter
 *                                              '***DOC***DOCX***RTF***' Word docs.
 *
 ***********************************************************************************/
CREATE PROCEDURE
       [dbo].[ky_GetDocumentsIM]
      (@handler VARCHAR(10),
       @casecode VARCHAR(20),
       @page int,
       @pagesize int,
       @order int,
       @classfilter VARCHAR(MAX),
       @typefilter VARCHAR(MAX),
       @search VARCHAR(MAX))
AS
BEGIN

SET @classfilter = ISNULL(@classfilter, '')
SET @typefilter = ISNULL(@typefilter, '')
SET @search = ISNULL(@search, '')

DECLARE @startno int
DECLARE @totaldocs int
DECLARE @totalpages int
DECLARE @enddoc int
DECLARE @OTHFilters varchar(max)
DECLARE @PDFFilters varchar(max)
DECLARE @DOCFilters varchar(max)
DECLARE @PPTFilters varchar(max)
DECLARE @MSGFilters varchar(max)
DECLARE @XLSFilters varchar(max)
DECLARE @IMGFilters varchar(max)

IF (ISNULL(@pagesize, 0) < 1)
  SET @pagesize = 100

IF (ISNULL(@page, 0) < 1)
  SET @page = 1

SET @startno = (@page - 1) * @pagesize + 1

IF (@startno < 1)
  SET @startno = 1

DECLARE @classes table
       ([KeyhouseClass]     int,
        [IMClass]           int,
        [ClassCode]         varchar(20),
        [ClassDescription]  varchar(100))

DECLARE @concatclasses table
       ([ClassCode]         varchar(max),
        [ClassDescription]  varchar(100))

DECLARE @res table
       ([id]                       int identity(1, 1),
        [id2]                      int,
        [ActionId]                 int,
        [TrackReference]           int,
        [IMDocID]                  varchar(500),
        [IMClass]                  varchar(20),
        [DocClass]                 varchar(10),
        [Document]                 varchar(2000),
        [OriginalDesc]             varchar(1000),
        [DiaryDate]                datetime,
        [DocumentDate]             datetime,
        [TYPE]                     varchar(20),
        [EMAIL]                    varchar(1),
        [EMAILFROM]                varchar(200),
        [EMAILSENT]                datetime,
        [EMAILTO]                  varchar(1000))

  INSERT
    INTO @res
        ([ActionID],
         [TrackReference],
         [IMDocID],
         [IMClass],
         [DocClass],
         [Document],
         [OriginalDesc],
         [DiaryDate],
         [DocumentDate],
         [TYPE],
         [EMAIL],
         [EMAILSENT],
         [EMAILFROM],
         [EMAILTO])
  SELECT RES.[ActionID],
         RES.[TrackReference],
         RES.[IMDocID],
         RES.[IMClass],
         RES.[DocClass],
         RES.[Document],
         RES.[OriginalDesc],
         RES.[DiaryDate],
         RES.[DocumentDate],
         RES.[TYPE],
         RES.[EMAIL],
         RES.[EMAILSENT],
         RES.[EMAILFROM],
         RES.[EMAILTO]
    FROM (SELECT convert(int, NULL) AS [ActionID],
                 convert(int, NULL) AS [TrackReference],
                 convert(varchar(500), DAL.[IMDocID]) AS [IMDocID],
                 convert(varchar(20), DAL.[IMClass]) AS [IMClass],
                 convert(varchar(10), NULL) AS [DocClass],
                 convert(varchar(2000), DAL.[Description]) AS [Document],
                 convert(varchar(1000), DAL.[OriginalDesc]) AS [OriginalDesc],
                 convert(datetime, ISNULL(DAL.[DocumentDate], DAL.[EmailSent])) as [DiaryDate],
                 convert(datetime, ISNULL(DAL.[DocumentDate], DAL.[EmailSent])) as [DocumentDate],
                 convert(datetime, DAL.[EmailSent]) as [EMAILSENT],
                 CASE convert(varchar(20), DAL.[IMTYPE])
                      WHEN 'WORD' THEN 'DOC'
                      WHEN 'WORDX' THEN 'DOC'
                      WHEN 'WPF' THEN 'DOC'
                      WHEN 'ANSI' THEN 'DOC'
                      WHEN 'ACROBAT' THEN 'PDF'
                      WHEN 'EXCEL' THEN 'XLS'
                      WHEN 'EXCELX' THEN 'XLS'
                      WHEN 'MIME' THEN CASE WHEN DAL.[IMClass] = 'E-Mail'
                                                 THEN 'MSG'
                                            ELSE 'MIME' END
                      ELSE DAL.[IMTYPE] END AS [TYPE],
                 convert(varchar(1), CASE WHEN DAL.[IMClass] = 'E-Mail'
                                               THEN 'Y'
                                          ELSE 'N' END) As [EMAIL],
                 CASE WHEN DAL.[IMClass] = 'E-Mail'
                           THEN DAL.[EMAILFROM]
                      ELSE CONVERT(varchar(200), NULL) END AS [EMAILFROM],
                 CASE WHEN DAL.[IMClass] = 'E-Mail'
                           THEN DAL.[EMAILTO]
                      ELSE CONVERT(varchar(1000), NULL) END AS [EMAILTO]
            FROM [dbo].[DAIMList] DAL
                 LEFT OUTER JOIN [dbo].[DAIMXref] DX
                              ON DX.[IMDocID] = DAL.[IMDocID]
           WHERE DAL.[HANDLER] = @handler
             AND DX.[TrackReference] IS NULL
          UNION
          SELECT convert(int, DA.[DIARYID]) AS [ActionID],
                 convert(int, DA.[TrackReference]) AS [TrackReference],
                 convert(varchar(500), ISNULL(DX.[IMDocID], '')) AS [IMDocID],
                 convert(varchar(20), ISNULL(DL.[IMClass], '')) AS [IMClass],
                 convert(varchar(10), ISNULL(DA.[DocClass], '')) AS [DocClass],
                 RTRIM(convert(varchar(2000), ISNULL(DA.[Document], ''))) AS [Document],
                 RTRIM(convert(varchar(1000), ISNULL(DA.[Document], ''))) AS [OriginalDesc],
                 convert(datetime, DIA.[DATE]) As [DiaryDate],
                 convert(datetime, DA.[DATEENTERED]) AS [DocumentDate],
                 convert(datetime, ISNULL(DIA.[TxmDate], DIA.[Date])) As [EMAILSENT],
                 convert(varchar(20), UPPER(RTRIM(ISNULL(DA.[TYPE], '')))) AS [TYPE],
                 convert(varchar(1), CASE WHEN RTRIM(ISNULL(DIA.[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL],
                 convert(varchar(200), RTRIM(ISNULL(DIA.[EMAILADDRESS], ''))) AS [EMAILFROM],
                 RTRIM(ISNULL(CONVERT(varchar(1000), DIA.[ADDRESSTO]), '')) AS [EMAILTO]
            FROM [dbo].[diary] DIA
                 INNER JOIN [dbo].[DiaryAttachments] DA
                         ON DA.[DiaryID] = DIA.[ActionID]
                 LEFT OUTER JOIN [dbo].[DAIMXRef] DX
                                 LEFT OUTER JOIN [dbo].[DAIMList] DL
                                              ON DL.[handler] = @handler
                                             AND DL.[IMDocID] = DX.[IMDocID]
                              ON DX.[TrackReference] = DA.[TrackReference]
           WHERE DIA.[CASECODE] = @casecode)  RES
ORDER BY CASE WHEN @order = 2
                   THEN RES.[IMDocID]
              ELSE '' END DESC,
         CASE WHEN @order = 0
                   THEN RES.[DiaryDate]
              ELSE convert(datetime, null) END DESC,
         CASE WHEN @order = 0
                   THEN RES.[ActionID]
              ELSE RES.[TrackReference] END DESC,
         CASE WHEN @order = 0
                   THEN RES.[TrackReference]
              ELSE 0 END ASC,
         RES.[EMAIL] DESC

INSERT
  INTO @classes
      ([KeyhouseClass],
       [IMClass],
       [ClassCode],
       [ClassDescription])
SELECT CLS.[KeyhouseClass],
       CLS.[IMClass],
       CLS.[ClassCode],
       CASE WHEN CLS.[KeyhouseClass] = 1 
                 THEN RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], ''))
            ELSE CASE WHEN CLS.[ClassCode] = 'ATTNOTES'
                           THEN 'Attendance Notes'
                      WHEN CLS.[ClassCode] = 'BILL'
                           THEN 'Bill'
                      WHEN CLS.[ClassCode] = 'FAX'
                           THEN 'Fax'
                      WHEN CLS.[ClassCode] = 'LEGAL'
                           THEN 'Legal'
                      WHEN CLS.[ClassCode] = 'SCAN'
                           THEN 'Scan'
                      WHEN CLS.[ClassCode] = 'COMPARE'
                           THEN 'Compare Document'
                      WHEN CLS.[ClassCode] = 'DISCUSSION'
                           THEN 'Discussion'
                      WHEN CLS.[ClassCode] = 'DOC'
                           THEN 'Document'
                      WHEN CLS.[ClassCode] = 'E-MAIL'
                           THEN 'E-Mail'
                      WHEN CLS.[ClassCode] = 'EVENT'
                           THEN 'Event'
                      WHEN CLS.[ClassCode] = 'LETTER'
                           THEN 'Letter'
                      WHEN CLS.[ClassCode] = 'MEMO'
                           THEN 'Memo'
                      WHEN CLS.[ClassCode] = 'PAGE_ICON'
                           THEN 'Page Icon'
                      WHEN CLS.[ClassCode] = 'TASK'
                           THEN 'Task'
                      WHEN CLS.[ClassCode] = 'TEXT'
                           THEN 'Text File'
                      WHEN CLS.[ClassCode] = 'WEBDOC'
                           THEN 'InfoLink Web Page'
                      ELSE ISNULL(CLS.[CLASSCODE], '') END END AS [ClassDescription]
  FROM (SELECT DISTINCT
               1 AS [KeyhouseClass],
               0 AS [IMClass],
               CONVERT(VARCHAR(20), RES.[DocClass]) AS [ClassCode]
          FROM @res RES
         WHERE ISNULL(RES.[DocClass], '') <> ''
        UNION
        SELECT DISTINCT
               0 AS [KeyhouseClass],
               1 AS [IMClass],
               CONVERT(VARCHAR(20), RES.[IMClass]) AS [ClassCode]
          FROM @res RES
         WHERE ISNULL(RES.[IMClass], '') <> '') CLS
       LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
                    ON CLS.[KeyhouseClass] = 1
                   AND DCL.[CLASSCODE] = CLS.[ClassCode]

INSERT
  INTO @concatclasses
      ([ClassCode],
       [ClassDescription])
SELECT DISTINCT
       '***' AS [ClassCode],
       [CLS].[ClassDescription]
  FROM @classes [CLS]

DECLARE @CONCATCODE VARCHAR(MAX)
DECLARE @CLASSDESCRIPTION VARCHAR(100)

SET @CLASSDESCRIPTION = ''
SELECT TOP 1 @CLASSDESCRIPTION = [CCT].[ClassDescription] FROM @concatclasses [CCT] WHERE [CCT].[ClassCode] = '***'
WHILE @CLASSDESCRIPTION <> ''
BEGIN
  SET @CONCATCODE = '***'
  SELECT @CONCATCODE = @CONCATCODE + [CLS].[ClassCode] + '***' FROM @classes [CLS] WHERE [CLS].[ClassDescription] = @CLASSDESCRIPTION
  UPDATE [CCT] SET [CCT].[ClassCode] = @CONCATCODE FROM @concatclasses [CCT] WHERE [CCT].[ClassDescription] = @CLASSDESCRIPTION
  SET @CLASSDESCRIPTION = ''
  SELECT TOP 1 @CLASSDESCRIPTION = [CCT].[ClassDescription] FROM @concatclasses [CCT] WHERE [CCT].[ClassCode] = '***'
END

--SELECT *
--  FROM @classes CLS
       
SELECT *
  FROM @concatclasses CLS

SELECT @OTHFilters = '***'
SELECT @PDFFilters = '***'
SELECT @DOCFilters = '***'
SELECT @PPTFilters = '***'
SELECT @MSGFilters = '***'
SELECT @XLSFilters = '***'
SELECT @IMGFilters = '***'

SELECT @OTHFilters = CASE WHEN RSZ.[Type] <> 'Other' THEN @OTHFilters ELSE @OTHFilters + RSZ.[DocType] + '***' END,
       @PDFFilters = CASE WHEN RSZ.[Type] <> 'Adobe PDF' THEN @PDFFilters ELSE @PDFFilters + RSZ.[DocType] + '***' END,
       @DOCFilters = CASE WHEN RSZ.[Type] <> 'Document' THEN @DOCFilters ELSE @DOCFilters + RSZ.[DocType] + '***' END,
       @PPTFilters = CASE WHEN RSZ.[Type] <> 'Presentation' THEN @PPTFilters ELSE @PPTFilters + RSZ.[DocType] + '***' END,
       @MSGFilters = CASE WHEN RSZ.[Type] <> 'E-Mail' THEN @MSGFilters ELSE @MSGFilters + RSZ.[DocType] + '***' END,
       @XLSFilters = CASE WHEN RSZ.[Type] <> 'Spreadsheet' THEN @XLSFilters ELSE @XLSFilters + RSZ.[DocType] + '***' END,
       @IMGFilters = CASE WHEN RSZ.[Type] <> 'Image' THEN @IMGFilters ELSE @IMGFilters + RSZ.[DocType] + '***' END
  FROM (SELECT DISTINCT 
               RES.[TYPE] AS [DocType],
               CASE WHEN RES.[TYPE] = 'PDF'
                         THEN 'Adobe PDF'
                    WHEN RES.[TYPE] = 'BMP'
                         THEN 'Image'
                    WHEN RES.[TYPE] = 'GIF'
                         THEN 'Image'
                    WHEN RES.[TYPE] = 'G3N'
                         THEN 'Image'
                    WHEN RES.[TYPE] = 'JPG'
                         THEN 'Image'
                    WHEN RES.[TYPE] = 'JPEG'
                         THEN 'Image'
                    WHEN RES.[TYPE] = 'JPE'
                         THEN 'Image'
                    WHEN RES.[TYPE] = 'PNG'
                         THEN 'Image'
                    WHEN RES.[TYPE] = 'TIF'
                         THEN 'Image'
                    WHEN RES.[TYPE] = 'TIFF'
                         THEN 'Image'
                    WHEN RES.[TYPE] = 'XLS'
                         THEN 'Spreadsheet'
                    WHEN RES.[TYPE] = 'XLSX'
                         THEN 'Spreadsheet'
                    WHEN RES.[TYPE] = 'CSV'
                         THEN 'Spreadsheet'
                    WHEN RES.[TYPE] = 'ODS'
                         THEN 'Spreadsheet'
                    WHEN RES.[TYPE] = 'MSG'
                         THEN 'E-Mail'
                    WHEN RES.[TYPE] = 'EML'
                         THEN 'E-Mail'
                    WHEN RES.[TYPE] = 'DOC'
                         THEN 'Document'
                    WHEN RES.[TYPE] = 'DOCX'
                         THEN 'Document'
                    WHEN RES.[TYPE] = 'RTF'
                         THEN 'Document'
                    WHEN RES.[TYPE] = 'TXT'
                         THEN 'Document'
                    WHEN RES.[TYPE] = 'INI'
                         THEN 'Document'
                    WHEN RES.[TYPE] = 'ODT'
                         THEN 'Document'
                    WHEN RES.[TYPE] = 'PPT'
                         THEN 'Presentation'
                    WHEN RES.[TYPE] = 'PPTX'
                         THEN 'Presentation'
                    WHEN RES.[TYPE] = 'ODP'
                         THEN 'Presentation'
                    ELSE 'Other' END AS [Type] FROM @res RES) RSZ

SELECT *
  FROM (SELECT DISTINCT 
               CASE WHEN RES.[TYPE] = 'PDF'
                    THEN 'Adobe PDF'
               WHEN RES.[TYPE] = 'BMP'
                    THEN 'Image'
               WHEN RES.[TYPE] = 'GIF'
                    THEN 'Image'
               WHEN RES.[TYPE] = 'G3N'
                    THEN 'Image'
               WHEN RES.[TYPE] = 'JPG'
                    THEN 'Image'
               WHEN RES.[TYPE] = 'JPEG'
                    THEN 'Image'
               WHEN RES.[TYPE] = 'JPE'
                    THEN 'Image'
               WHEN RES.[TYPE] = 'PNG'
                    THEN 'Image'
               WHEN RES.[TYPE] = 'TIF'
                    THEN 'Image'
               WHEN RES.[TYPE] = 'TIFF'
                    THEN 'Image'
               WHEN RES.[TYPE] = 'XLS'
                    THEN 'Spreadsheet'
               WHEN RES.[TYPE] = 'XLSX'
                    THEN 'Spreadsheet'
               WHEN RES.[TYPE] = 'CSV'
                    THEN 'Spreadsheet'
               WHEN RES.[TYPE] = 'ODS'
                    THEN 'Spreadsheet'
               WHEN RES.[TYPE] = 'MSG'
                    THEN 'E-Mail'
               WHEN RES.[TYPE] = 'EML'
                    THEN 'E-Mail'
               WHEN RES.[TYPE] = 'DOC'
                    THEN 'Document'
               WHEN RES.[TYPE] = 'DOCX'
                    THEN 'Document'
               WHEN RES.[TYPE] = 'RTF'
                    THEN 'Document'
               WHEN RES.[TYPE] = 'TXT'
                    THEN 'Document'
               WHEN RES.[TYPE] = 'INI'
                    THEN 'Document'
               WHEN RES.[TYPE] = 'ODT'
                    THEN 'Document'
               WHEN RES.[TYPE] = 'PPT'
                    THEN 'Presentation'
               WHEN RES.[TYPE] = 'PPTX'
                    THEN 'Presentation'
               WHEN RES.[TYPE] = 'ODP'
                    THEN 'Presentation'
               ELSE 'Other' END AS [Type],
               CASE WHEN RES.[TYPE] = 'PDF'
                    THEN @PDFFilters
               WHEN RES.[TYPE] = 'BMP'
                    THEN @IMGFilters
               WHEN RES.[TYPE] = 'GIF'
                    THEN @IMGFilters
               WHEN RES.[TYPE] = 'G3N'
                    THEN @IMGFilters
               WHEN RES.[TYPE] = 'JPG'
                    THEN @IMGFilters
               WHEN RES.[TYPE] = 'JPEG'
                    THEN @IMGFilters
               WHEN RES.[TYPE] = 'JPE'
                    THEN @IMGFilters
               WHEN RES.[TYPE] = 'PNG'
                    THEN @IMGFilters
               WHEN RES.[TYPE] = 'TIF'
                    THEN @IMGFilters
               WHEN RES.[TYPE] = 'TIFF'
                    THEN @IMGFilters
               WHEN RES.[TYPE] = 'XLS'
                    THEN @XLSFilters
               WHEN RES.[TYPE] = 'XLSX'
                    THEN @XLSFilters
               WHEN RES.[TYPE] = 'CSV'
                    THEN @XLSFilters
               WHEN RES.[TYPE] = 'ODS'
                    THEN @XLSFilters
               WHEN RES.[TYPE] = 'MSG'
                    THEN @MSGFilters
               WHEN RES.[TYPE] = 'EML'
                    THEN @MSGFilters
               WHEN RES.[TYPE] = 'DOC'
                    THEN @DOCFilters
               WHEN RES.[TYPE] = 'DOCX'
                    THEN @DOCFilters
               WHEN RES.[TYPE] = 'RTF'
                    THEN @DOCFilters
               WHEN RES.[TYPE] = 'TXT'
                    THEN @DOCFilters
               WHEN RES.[TYPE] = 'INI'
                    THEN @DOCFilters
               WHEN RES.[TYPE] = 'ODT'
                    THEN @DOCFilters
               WHEN RES.[TYPE] = 'PPT'
                    THEN @PPTFilters
               WHEN RES.[TYPE] = 'PPTX'
                    THEN @PPTFilters
               WHEN RES.[TYPE] = 'ODP'
                    THEN @PPTFilters
               ELSE @OTHFilters END AS [Filter] FROM @res RES) RSZ
ORDER BY CASE WHEN RSZ.[Type] = 'Other' THEN 'ZZZZZZ' ELSE RSZ.[Type] END

IF (@classfilter <> '')
BEGIN
  DELETE RES
    FROM @res RES
   WHERE @classfilter NOT LIKE '%***' + ISNULL(RES.[IMClass], '') + '***%'
     AND @classfilter NOT LIKE '%***' + ISNULL(RES.[DocClass], '') + '***%'
END

IF (@typefilter <> '')
BEGIN
  DELETE RES
    FROM @res RES
   WHERE @typefilter NOT LIKE '%***' + ISNULL(RES.[TYPE], '') + '***%'
END

IF (@search <> '')
BEGIN
  SET @search = '%' + @search + '%'
  SET @search = REPLACE(@search, '%%', '%')
  SET @search = REPLACE(@search, '%%', '%')

  DELETE RES
    FROM @res RES
   WHERE RES.[Document] NOT LIKE @search
     AND ISNULL(RES.[EMAILFROM], '') NOT LIKE @search
     AND ISNULL(RES.[EMAILTO], '') NOT LIKE @search
END

UPDATE RES
   sET RES.[id2] = MEH.[TOTAL]
  FROM @res RES
       CROSS APPLY (SELECT COUNT(1) AS TOTAL FROM @res RES2 WHERE RES2.[id] <= RES.[id]) MEH

SELECT @totaldocs = COUNT(1) FROM @res
IF (@totaldocs > 0)
  BEGIN
    WHILE @startno > @totaldocs
      BEGIN
        SET @startno = @startno - @pagesize
        SET @page = @page - 1
        IF (@startno < 1)
          SET @startno = 1
      END
  END
ELSE
  BEGIN
    SET @page = 0
    SET @startno = 0
  END

DELETE @RES
 WHERE [id2] < @startno
    OR [id2] > @startno + @pagesize - 1

SELECT @enddoc = ISNULL(MAX([id2]), 0) FROM @res

SELECT @totalpages = FLOOR((convert(real, @totaldocs) - convert(real, 1)) / convert(real, @pagesize)) + 1

SELECT @page AS [page],
       @startno AS [firstdoc],
       @enddoc AS [lastdoc],
       @totaldocs As [totaldocs],
       @totalpages As [totalpages]

UPDATE RES
   SET RES.[EMAIL] = CASE WHEN (    ISNULL(RES.[EMAIL], '') = 'Y'
                                AND RES.[TrackReference] = OMS.[TrackReference])
                               THEN 'Y'
                          WHEN (    ISNULL(RES.[TYPE], '') = 'MSG'
                                AND RES.[TrackReference] = OMS.[TrackReference])
                               THEN 'Y'
                          ELSE 'N' END
  FROM @res RES
       OUTER APPLY (  SELECT TOP 1
                             MST.[TrackReference] AS [TrackReference],
                             MST.[Type] AS [Type]
                        FROM [dbo].[diaryattachments] MST
                       WHERE MST.[diaryid] = RES.[ActionID]
                    ORDER BY CASE WHEN MST.Type = 'MSG'
                                       THEN 0
                                  ELSE 1 END,
                             MST.[TrackReference]) OMS
 WHERE RES.[ACTIONID] IS NOT NULL

SELECT RES.[id],
       RES.[id2],
       RES.[DiaryDate],
       ISNULL(RES.[ActionID], 0) AS [ACTIONID],
       RES.[DocumentDate],
       RES.[IMDocID],
       RES.[IMClass],
       CLS.[ClassDescription] AS [DocumentClassDescription],
       RES.[DocClass],
       CASE WHEN DAT.[TrackReference] IS NULL
                 THEN RES.[Document]
            WHEN RES.[EMAIL] <> 'Y'
                 THEN RES.[Document]
            WHEN RTRIM(ISNULL(DIA.[EMAILADDRESS], '')) <> '' AND RTRIM(ISNULL(CONVERT(VARCHAR(1000), DIA.[ADDRESSTO]), '')) <> ''
                 THEN CONVERT(varchar(2000), 'E-mail from ' + RTRIM(ISNULL(DIA.[EMAILADDRESS], '')) + ' to ' + RTRIM(ISNULL(CONVERT(VARCHAR(2000), DIA.[ADDRESSTO]), ''))) --+ ': ' + RTRIM(ISNULL(DIA.[SUBJECT], '')))
            WHEN RTRIM(ISNULL(DIA.[EMAILADDRESS], '')) <> ''
                 THEN CONVERT(VARCHAR(2000), 'E-mail from ' + RTRIM(ISNULL(DIA.[EMAILADDRESS], ''))) --+ ': ' + RTRIM(ISNULL(DIA.[SUBJECT], '')))
            WHEN RTRIM(ISNULL(CONVERT(VARCHAR(2000), DIA.[ADDRESSTO]), '')) <> ''
                 THEN 'E-mail to ' + RTRIM(ISNULL(CONVERT(VARCHAR(2000), DIA.[ADDRESSTO]), '')) --+ ': ' + RTRIM(ISNULL(DIA.[SUBJECT], '')))
            ELSE RES.[Document] END AS [Document],
       RES.[OriginalDesc] AS [OriginalDocumentName],
       ISNULL(RES.[TrackReference], 0) AS [TRACKREFERENCE],
       RES.[TYPE],
       RES.[EMAIL],
       RTRIM(ISNULL(CONVERT(varchar(200), SUBSTRING(ISNULL(DIA.[EMAILADDRESS], RES.[EMAILFROM]), 1, 200)), '')) AS [EMAILADDRESS],
       RTRIM(ISNULL(CONVERT(varchar(1000), SUBSTRING(ISNULL(DIA.[ADDRESSTO], RES.[EMAILTO]), 1, 1000)), '')) AS [ADDRESSTO],
       RES.[EMAILSENT],
       ISNULL(CONVERT(varchar(1000), SUBSTRING(ISNULL(DIA.[SUBJECT], CASE WHEN RES.[EMAIL] = 'Y' THEN RES.[OriginalDesc] ELSE '' END), 1, 1000)), '') AS [SUBJECT],
       RTRIM(ISNULL(DAT.[NAME], '')) AS [NAME],
       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],
       RTRIM(ISNULL(DIA.[PROCESSTYPE], '')) AS [PROCESSTYPE],
       RTRIM(ISNULL(DIA.[ACTIONTYPE], '')) AS [ACTIONTYPE],
       CASE WHEN DIA.[ACTIONTYPE] = 'A'
                 THEN 'Action'
            WHEN DIA.[ACTIONTYPE] = 'N'
                 THEN 'Note'
            WHEN DIA.[ACTIONTYPE] = 'P'
                 THEN 'Appointment'
            WHEN DIA.[ACTIONTYPE] = 'R'
                 THEN 'Reminder'
            WHEN DIA.[ACTIONTYPE] = 'E'
                 THEN 'Email'
            WHEN DIA.[ACTIONTYPE] = 'T'
                 THEN 'Phone Message'
            WHEN DIA.[ACTIONTYPE] = 'D'
                 THEN 'Dictation'
            WHEN DIA.[ACTIONTYPE] = 'U'
                 THEN 'Undertaking'
            WHEN DIA.[ACTIONTYPE] = 'S'
                 THEN 'Statute Date'
            WHEN DIA.[ACTIONTYPE] = 'C'
                 THEN 'Critical Date'
            WHEN DIA.[ACTIONTYPE] = 'O'
                 THEN 'Court Date'
            WHEN DIA.[ACTIONTYPE] = 'M'
                 THEN 'Scanned Post/Mail'
            WHEN RTRIM(ISNULL(DIA.[ACTIONTYPE], '')) = ''
                 THEN CASE WHEN RES.[EMAIL] = 'Y'
                                THEN 'Email' 
                           WHEN RES.[IMDocID] <> ''
                                THEN 'IManage document'
                           ELSE 'Unknown' END
            ELSE 'Action Type: ' + DIA.[ACTIONTYPE] END AS [ActionTypeDescription],
       RTRIM(ISNULL(DAT.[SOURCE], CASE WHEN DIA.[ACTIONID] IS NULL THEN 'IManage' ELSE '' END)) AS [DOCUMENTSOURCE]
  from @res RES
       LEFT OUTER JOIN [dbo].[DiaryAttachments] DAT
                       INNER JOIN [dbo].[diary] DIA
                               ON DIA.[ACTIONID] = DAT.[DIARYID]
                       LEFT OUTER JOIN [dbo].[UNCAlias] UNC
                                     ON SUBSTRING(DAT.[FilePath], 2, 1) = ':'
                                   AND UNC.[Drive] = SUBSTRING(DAT.[FilePath], 1, 1)
                    ON DAT.[TRACKREFERENCE] = RES.[TrackReference]
       LEFT OUTER JOIN @classes CLS
                    ON CLS.[KeyhouseClass] = CASE WHEN ISNULL(RES.[IMClass], '') = '' THEN 1 ELSE 0 END
                   AND CLS.[IMClass] = CASE WHEN ISNULL(RES.[IMClass], '') = '' THEN 0 ELSE 1 END
                   AND CLS.[ClassCode] = CASE WHEN ISNULL(RES.[IMClass], '') = '' THEN RES.[DocClass] ELSE RES.[IMClass] END
END
GO

CREATE PROCEDURE
       [dbo].[ky_IMGenerateBrief]
      (@handler varchar(10),
       @MatterCode varchar(20),
       @BriefTitle varchar(200),
       @Description varchar(4000),
       @BriefAuthor varchar(100),
       @Location varchar(256),
       @CreateDate varchar(11),
       @PublishDate varchar(11),
       @CopyBrief int,
       @CopyBriefID int,
       @BriefTemplateID int,
       @ImportCaseDocs int,
       @ImportNonClassified int)
AS
/*******************************************************************************

      .NET version of the Generate Brief procedure - with IManage support

      ImportCaseDocs: 1 - Include all case documents in this brief (if brief is created
                          as a copy of the last brief, include missing ones)

                      0 - Leave out missing case documents

      CopyBrief:    1 - Create a New Brief as a copy of the last Brief for this
                        Case

                    0 - Create a New Brief that is blank


      Nonclassified:    1 - Add all documents into the brief, creating new sections for
                            document classes not already represented in the brief sections

                        0 - Add only documents that have a document class corresponding to
                            a Brief Section that already exists in the document.

 *******************************************************************************/
BEGIN

  DECLARE @NewBriefID int
  DECLARE @LastBriefID int
  DECLARE @NewSectionNo int
  DECLARE @NewSection varchar(100) 
  DECLARE @DocSortOrder int
  DECLARE @DocLastSectionID int
  DECLARE @DocBriefID int
  DECLARE @DocName varchar(255)
  DECLARE @DocTrackRef int
  DECLARE @DocFileLoc varchar(255)
  DECLARE @DocFileDate datetime
  DECLARE @DocType char(4)
  DECLARE @DocSectionID int
  DECLARE @DocClass varchar(100)

  DECLARE @MaxSectionSequence int

  BEGIN TRANSACTION

  DECLARE @NewSections Table
         ([ID]               int    identity(1, 1),
          [Section]          varchar(100))

  DECLARE @BriefDocs TABLE
         ([ID]             int           identity(1, 1),
          [DocumentName]   varchar(255),
          [TrackReference] int,
          [IMDocID]        varchar(500),
          [FileLocation]   varchar(255),
          [FileDate]       datetime,
          [DocumentType]   varchar(4),
          [DocumentClass]  varchar(100),
          [SortOrder]      int,
          [SectionID]      int)

  BEGIN TRY
    IF (@CopyBrief = 1)
      BEGIN
          SELECT @LastBriefID = BRI.[ID]
            FROM [dbo].[Brief] BRI
           WHERE BRI.[MatterCode] = @MatterCode
             AND BRI.[ID] = IsNull(@CopyBriefID, BRI.[ID])
        ORDER BY BRI.[CreationDate] DESC,
                 BRI.[ID] DESC

        IF (@LastBriefID IS NULL)
          BEGIN
            RAISERROR(N'Copy Brief Requested but no current brief present', 20, 1);
          END
      END

    INSERT
      INTO [dbo].[Brief]
          ([Title],
           [MatterCode],
           [Author],
           [Location],
           [CreationDate],
           [PublishDate],
           [Description])
    VALUES(@BriefTitle,
           @MatterCode,
           @BriefAuthor,
           @Location,
           convert(datetime, @createdate),
           convert(datetime, @publishdate),
           @Description)
    
    Set @NewBriefID = SCOPE_IDENTITY()

    UPDATE BRI
       SET BRI.[Location] = Replace(BRI.[Location], 
                                    '.pdf',
                                    '_' + convert(varchar(8), BRI.[ID]) + '.pdf')
      FROM [dbo].[Brief] BRI
     WHERE BRI.[ID] = @NewBriefID

    /* Use Brief Template to create Brief Sections if requested */
    IF IsNull(@BriefTemplateID, 0) <> 0
       BEGIN
          INSERT
            INTO [dbo].[BriefSections]
                ([BriefID],
                 [CoverPage],
                 [Sequence],
                 [Section])
          SELECT @NewBriefID,
                 BTS.[CoverPage],
                 BTS.[Sequence],
                 RTRIM(ISNULL(BTS.[Section], '')) AS [Section]
            FROM [dbo].[BriefTemplateSections] BTS
           WHERE BTS.[BriefTemplateID] = @BriefTemplateID
       END  

    /* Create a Cover Letters Section so that the Brief will have at least ONE section*/
    IF IsNull(@BriefTemplateID, 0) = 0
       BEGIN
          INSERT
            INTO [dbo].[BriefSections]
                ([BriefID],
                 [CoverPage],
                 [Sequence],
                 [Section])
          VALUES(@NewBriefID,
                 1,
                 1,
                 'Cover Letters')
       END

      IF @ImportNonClassified = 1
         BEGIN
           SELECT @MaxSectionSequence = MAX(BS.[Sequence])
             FROM [dbo].[BriefSections] BS
            WHERE BS.[BriefID] = @NewBriefID
       
           SELECT @MaxSectionSequence = ISNULL(@MaxSectionSequence, 0)

           INSERT
             INTO @NewSections
                 ([Section])
           SELECT DISTINCT
                  RTRIM(ISNULL(DC.[ClassDescription], '')) AS [Section]
             FROM [dbo].[diary] DIA
                  INNER JOIN [dbo].[DiaryAttachments] DA
                             INNER JOIN [dbo].[DocumentClasses] DC
                                        LEFT OUTER JOIN [dbo].[BriefSections] BS2
                                                     ON BS2.[BriefID] = @NewBriefID
                                                    AND BS2.[Section] = DC.[ClassDescription]
                                     ON DC.[ClassCode] = DA.[DocClass]
                          ON DA.[DIARYID] = DIA.[ACTIONID] 
            WHERE DIA.[CaseCode] = @MatterCode
              AND BS2.[id] is null

            INSERT
              INTO [dbo].[BriefSections]
                  ([BriefID],
                   [CoverPage],
                   [Sequence],
                   [Section])
            SELECT @NewBriefID,
                   0,
                   NS.[ID] + @MaxSectionSequence,
                   RTRIM(ISNULL(NS.[Section], '')) AS [Section]
              FROM @NewSections NS
         END

    /* If this is a copy of an earlier brief, make sure that all the sections from the
       old Brief are also present in the new Brief. Avoid duplicates. */
    IF @CopyBrief = 1
       BEGIN
          INSERT
            INTO [dbo].[BriefSections]
                ([BriefID],
                 [CoverPage],
                 [Sequence],
                 [Section])
          SELECT @NewBriefID,
                 BS.[CoverPage],
                 BS.[Sequence],
                 RTRIM(ISNULL(BS.[Section], '')) AS [Section]
            FROM [dbo].[BriefSections] BS
                 LEFT OUTER JOIN [dbo].[BriefSections] BS2
                              ON BS2.[BriefID] = @NewBriefID
                             AND BS2.[Section] = BS.[Section]
           WHERE BS.[BriefID] = @LastBriefID
             AND BS2.[ID] IS NULL
       END

    /* For backward compatibility with the Clarion version, we will be updating these
       'sorter' fields that allow the Clarion program to show the sections in the correct order.
       the WinDev/.NET programs can handle this themselves and they don't need this */
    UPDATE BS
       SET BS.khsorter = convert(varchar, 1-coverpage) 
                       + right(convert(varchar, 10000 + sequence), 4) 
                       + right(convert(varchar, 10000000 + ID), 7)
      from briefsections BS
     where BS.BriefID = @NewBriefID

    /* If this is a copy from an older Brief, copy the Brief documents from the old Brief
       into the new Brief */
    IF @CopyBrief = 1
       BEGIN
          INSERT
            INTO [dbo].[BriefDocuments]
                ([BriefID],
                 [DocumentName],
                 [TrackReference],
                 [FileLocation],
                 [FileDate],
                 [DocumentType],
                 [DocumentClass],
                 [SortOrder],
                 [SectionID])
          SELECT @NewBriefID,
                 BD.[DocumentName],
                 BD.[TrackReference],
                 BD.[FileLocation],
                 BD.[Filedate],
                 BD.[DocumentType],
                 BD.[DocumentClass],
                 BD.[SortOrder],
                 BS2.[ID]
            FROM [dbo].[BriefDocuments] BD
                 INNER JOIN [dbo].[BriefSections] BS1
                            INNER JOIN [dbo].[BriefSections] BS2
                                    ON BS2.[BriefID] = @NewBriefID
                                   AND BS2.[CoverPage] = BS1.[CoverPage]
                                   AND BS2.[Sequence] = BS1.[Sequence]
                                   AND BS2.[Section] = BS1.[Section]
                         ON BS1.[ID] = BD.SectionID
           WHERE BD.BriefID = @LastBriefID
      END

    /* Import the Case Documents into the new Brief if requested. Importing of non-classified documents
       implies importing ALL documents. */
    IF (@ImportCaseDocs = 1) OR (@ImportNonClassified = 1)
      BEGIN
        INSERT
          INTO @BriefDocs
              ([DocumentName],
               [TrackReference],
               [IMDocID],
               [FileLocation],
               [FileDate],
               [DocumentType],
               [DocumentClass],
               [SectionID])
          SELECT CASE WHEN RES.[TrackReference] IS NULL
                           THEN CASE WHEN RES.[Document] = '' THEN '(no name)' ELSE RES.[Document] END
                      WHEN RES.[EMAIL] <> 'Y'
                           THEN CASE WHEN RES.[Document] = '' THEN '(no name)' ELSE RES.[Document] END
                      WHEN RES.[TYPE] <> 'MSG'
                           THEN CASE WHEN RES.[Document] = '' THEN '(no name)' ELSE RES.[Document] END
                      WHEN (RTRIM(ISNULL(RES.[EMAILFROM], '')) <> '') AND (RTRIM(ISNULL(CONVERT(VARCHAR(255), RES.[EMAILTO]), '')) <> '')
                           THEN CONVERT(VARCHAR(255), 'E-mail from ' + RTRIM(ISNULL(RES.[EMAILFROM], '')) + RTRIM(ISNULL(CONVERT(VARCHAR(255), RES.[EMAILTO]), '')) + ': ' + RTRIM(ISNULL(RES.[Document], '')))
                      WHEN RTRIM(ISNULL(RES.[EMAILFROM], '')) <> ''
                           THEN CONVERT(VARCHAR(255), 'E-mail from ' + RTRIM(ISNULL(RES.[EMAILFROM], '')) + ': ' + RTRIM(ISNULL(RES.[Document], '')))
                      WHEN RTRIM(ISNULL(CONVERT(VARCHAR(255), RES.[EMAILTO]), '')) <> ''
                           THEN CONVERT(VARCHAR(255), 'E-mail to ' + RTRIM(ISNULL(CONVERT(VARCHAR(255), RES.[EMAILTo]), '')) + ': ' + RTRIM(ISNULL(RES.[Document], '')))
                      ELSE CASE WHEN RES.[Document] = '' THEN '(no name)' ELSE RES.[Document] END END AS [DocumentName],
                 RES.[TrackReference],
                 RES.[IMDocID],
                 RES.[FileLocation],
                 RES.[DocumentDate] AS [FileDate],
                 RES.[TYPE] As [DocumentType],
                 RES.[DocumentClass] As [DocumentClass],
                 BS.[ID] AS [SectionID]
            FROM (SELECT convert(int, NULL) AS [TrackReference],
                         convert(varchar(500), DAL.[IMDocID]) AS [IMDocID],
                         convert(varchar(20), DAL.[IMClass]) AS [IMClass],
                         convert(varchar(10), NULL) AS [DocClass],
                         convert(varchar(255), DAL.[Description]) AS [Document],
                         convert(datetime, ISNULL(DAL.[DocumentDate], DAL.[EmailSent])) as [DocumentDate],
                         CASE convert(varchar(20), DAL.[IMTYPE])
                              WHEN 'WORD' THEN 'DOC'
                              WHEN 'WORDX' THEN 'DOC'
                              WHEN 'WPF' THEN 'DOC'
                              WHEN 'ANSI' THEN 'DOC'
                              WHEN 'ACROBAT' THEN 'PDF'
                              WHEN 'EXCEL' THEN 'XLS'
                              WHEN 'EXCELX' THEN 'XLS'
                              WHEN 'MIME' THEN CASE WHEN DAL.[IMClass] = 'E-Mail'
                                                         THEN 'MSG'
                                                    ELSE 'MIME' END
                              ELSE DAL.[IMTYPE] END AS [TYPE],
                         convert(varchar(1), CASE WHEN DAL.[IMClass] = 'E-Mail'
                                                       THEN 'Y'
                                                  ELSE 'N' END) As [EMAIL],
                         CASE WHEN DAL.[IMClass] = 'E-Mail'
                                   THEN DAL.[EMAILFROM]
                              ELSE CONVERT(varchar(200), NULL) END AS [EMAILFROM],
                         CASE WHEN DAL.[IMClass] = 'E-Mail'
                                   THEN DAL.[EMAILTO]
                              ELSE CONVERT(varchar(255), NULL) END AS [EMAILTO],
                         '' AS [FileLocation],
                         CASE WHEN DAL.[IMClass] = 'ATTNOTES'
                                   THEN 'Attendance Notes'
                              WHEN DAL.[IMClass] = 'BILL'
                                   THEN 'Bill'
                              WHEN DAL.[IMClass] = 'FAX'
                                   THEN 'Fax'
                              WHEN DAL.[IMClass] = 'LEGAL'
                                   THEN 'Legal'
                              WHEN DAL.[IMClass] = 'SCAN'
                                   THEN 'Scan'
                              WHEN DAL.[IMClass] = 'COMPARE'
                                   THEN 'Compare Document'
                              WHEN DAL.[IMClass] = 'DISCUSSION'
                                   THEN 'Discussion'
                              WHEN DAL.[IMClass] = 'DOC'
                                   THEN 'Document'
                              WHEN DAL.[IMClass] = 'E-MAIL'
                                   THEN 'E-Mail'
                              WHEN DAL.[IMClass] = 'EVENT'
                                   THEN 'Event'
                              WHEN DAL.[IMClass] = 'LETTER'
                                   THEN 'Letter'
                              WHEN DAL.[IMClass] = 'MEMO'
                                   THEN 'Memo'
                              WHEN DAL.[IMClass] = 'PAGE_ICON'
                                   THEN 'Page Icon'
                              WHEN DAL.[IMClass] = 'TASK'
                                   THEN 'Task'
                              WHEN DAL.[IMClass] = 'TEXT'
                                   THEN 'Text File'
                              WHEN DAL.[IMClass] = 'WEBDOC'
                                   THEN 'InfoLink Web Page'
                              ELSE '' END AS [DocumentClass]
                    FROM [dbo].[DAIMList] DAL
                         LEFT OUTER JOIN [dbo].[DAIMXref] DX
                                      ON DX.[IMDocID] = DAL.[IMDocID]
                   WHERE DAL.[HANDLER] = @handler
                     AND DX.[TrackReference] IS NULL
                  UNION
                  SELECT convert(int, DA.[TrackReference]) AS [TrackReference],
                         convert(varchar(500), ISNULL(DX.[IMDocID], '')) AS [IMDocID],
                         convert(varchar(20), ISNULL(DL.[IMClass], '')) AS [IMClass],
                         convert(varchar(10), ISNULL(DA.[DocClass], '')) AS [DocClass],
                         RTRIM(convert(varchar(255), ISNULL(DA.[Document], ''))) AS [Document],
                         convert(datetime, DA.[DATEENTERED]) AS [DocumentDate],
                         convert(varchar(20), UPPER(RTRIM(ISNULL(DA.[TYPE], '')))) AS [TYPE],
                         convert(varchar(1), CASE WHEN RTRIM(ISNULL(DIA.[EMAIL], 'N')) = 'Y' THEN 'Y' ELSE 'N' END) AS [EMAIL],
                         convert(varchar(200), RTRIM(ISNULL(DIA.[EMAILADDRESS], ''))) AS [EMAILFROM],
                         RTRIM(ISNULL(CONVERT(varchar(255), DIA.[ADDRESSTO]), '')) AS [EMAILTO],
                         RTRIM(ISNULL(CASE WHEN UNC.[UNC] IS NULL 
                                                THEN DA.[FilePath]
                                           ELSE UNC.[UNC] + SUBSTRING(DA.[FilePath], 3, LEN(DA.[FilePath]) - 2) END, '')) AS [FileLocation],
                         RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) AS [DocumentClass]
                    FROM [dbo].[diary] DIA
                         INNER JOIN [dbo].[DiaryAttachments] DA
                                 ON DA.[DiaryID] = DIA.[ActionID]
                                AND RTRIM(ISNULL(DA.[FilePath], '')) <> ''
                         LEFT OUTER JOIN [dbo].[DAIMXRef] DX
                                         LEFT OUTER JOIN [dbo].[DAIMList] DL
                                                      ON DL.[handler] = @handler
                                                     AND DL.[IMDocID] = DX.[IMDocID]
                                      ON DX.[TrackReference] = DA.[TrackReference]
                         LEFT OUTER JOIN [dbo].[UNCAlias] UNC
                                      ON UNC.[Drive] = CASE WHEN SUBSTRING(DA.[FilePath], 2, 1) = ':'
                                                                 THEN LEFT(DA.[FilePath], 1)
                                                            ELSE '' END
                         LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
                                      ON DCL.[CLASSCODE] = DA.[DOCCLASS]
                   WHERE DIA.[CASECODE] = @MatterCode) RES
                 INNER JOIN [dbo].[BriefSections] BS
                         ON BS.[BriefID] = @NewBriefID
                        AND BS.[Section] = RES.[DocumentClass]
        ORDER BY BS.[CoverPage] DESC,
                 BS.[ID]

        INSERT
          INTO [dbo].[BriefDocuments]
              ([BriefID],
               [DocumentName],
               [TrackReference],
               [FileLocation],
               [Filedate],
               [DocumentType],
               [DocumentClass],
               [SortOrder],
               [SectionID],
               [IMDOCID])
        SELECT @NewBriefID,
               BD.[DocumentName],
               BD.[TrackReference],
               BD.[FileLocation],
               BD.[Filedate],
               BD.[DocumentType],
               BD.[DocumentClass],
               BD.[ID] - BT.[TOTAL],
               BD.[SectionID],
               BD.[IMDOCID]
          FROM @BriefDocs BD
               CROSS APPLY (SELECT COUNT(1) AS TOTAL
                              FROM @BriefDocs BD2
                             WHERE BD2.[SectionID] < BD.[SectionID]) BT
      END

    COMMIT
  END TRY
  BEGIN CATCH
    ROLLBACK
  END CATCH
END

GO


CREATE PROCEDURE
       [dbo].[ky_IMMoveBriefDocuments]
      (@docs VARCHAR(MAX))
AS
/*************************************************************************************************************
 *
 * ky_IMMoveBriefDocuments - Move documents from one Brief section to the other
 *
 *************************************************************************************************************/
BEGIN

BEGIN TRANSACTION

DECLARE @idoc int
DECLARE @SectionID INT
DECLARE @BriefID INT

DECLARE @MoveDocs TABLE
       ([recordid]       int,
        [BriefID]        int,
        [DocumentName]   VARCHAR(255),
        [TrackReference] int,
        [FileLocation]   VARCHAR(255),
        [Filedate]       datetime,
        [DocumentType]   VARCHAR(4),
        [DocumentClass]  VARCHAR(100),
        [SortOrder]      int                 identity(1, 1),
        [SectionID]      int,
        [IMDOCID]        VARCHAR(500))

DECLARE @DelDocs TABLE
       ([recordid]       int)

DECLARE @NewSort TABLE
       ([recordid]       int,
        [SectionID]      int,
        [ID]             int     identity(1, 1),
        [NewSort]        int)

BEGIN TRY
EXEC sp_xml_preparedocument @idoc OUTPUT, @docs

SELECT TOP 1 @SectionID = [tosection],
             @BriefID = [briefid]
  FROM OPENXML(@idoc, 'docs/doc', 2)
          WITH([briefid] int '../@briefid',
               [tosection] int '../@tosection') DCS

IF (@SectionID <> 0)
BEGIN
INSERT
  INTO @MoveDocs
      ([recordid],
       [BriefID],
       [DocumentName],
       [TrackReference],
       [FileLocation],
       [Filedate],
       [DocumentType],
       [DocumentClass],
       [SectionID],
       [IMDOCID])
SELECT BRD.[recordid],
       BRD.[BriefID],
       RTRIM(ISNULL(BRD.[DocumentName], '')) AS [DocumentName],
       BRD.[TrackReference],
       CASE WHEN UNC.[Drive] IS NULL 
                 THEN RTRIM(ISNULL(BRD.[FileLocation], ''))
            ELSE RTRIM(ISNULL(UNC.[UNC] + SUBSTRING(BRD.[FileLocation], 3, LEN(BRD.[FileLocation]) - 2), '')) END AS [FileLocation],
       BRD.[Filedate],
       UPPER(RTRIM(ISNULL(BRD.[DocumentType], ''))) AS [DocumentType],
       RTRIM(ISNULL(BRD.[DocumentClass], '')) AS [DocumentClass],
       BRD.[SectionID],
       RTRIM(ISNULL(BRD.[IMDOCID], '')) AS [IMDOCID]
  FROM [dbo].[BriefDocuments] BRD
       LEFT OUTER JOIN [dbo].[UNCAlias] UNC
                    ON UNC.[Drive] = CASE WHEN SUBSTRING(BRD.[FileLocation], 2, 1) = ':'
                                               THEN LEFT(BRD.[FileLocation], 1)
                                          ELSE '' END
 WHERE BRD.[SectionID] = @SectionID

INSERT
  INTO @MoveDocs
      ([recordid],
       [BriefID],
       [DocumentName],
       [TrackReference],
       [FileLocation],
       [Filedate],
       [DocumentType],
       [DocumentClass],
       [SectionID],
       [IMDOCID])
SELECT 0 AS [recordid],
       @BriefID AS [BriefID],
       DCS.[DocumentName],
       DCS.[TrackReference],
       DCS.[FileLocation],
       DCS.[Filedate],
       DCS.[DocumentType],
       DCS.[DocumentClass],
       @SectionID AS [SectionID],
       DCS.[IMDOCID]
  FROM OPENXML(@idoc, 'docs/doc', 2)
          WITH([DocumentName] varchar(255) '@DocumentName',
               [Filedate] datetime '@DocumentDate',
               [FileLocation] varchar(255) '@FileLocation',
               [DocumentType] varchar(4) '@DocumentType',
               [DocumentClass] varchar(100) '@DocumentClass',
               [trackreference] int '@TrackReference',
               [IMDOCID] varchar(500) '@IMDocID') DCS
END

INSERT
  INTO @DelDocs
      ([recordid])
SELECT DCS.[recordid] AS [recordid]
  FROM OPENXML(@idoc, 'docs/doc', 2)
          WITH([recordid] int '@recordid') DCS
 WHERE DCS.[recordid] <> 0

EXEC sp_xml_removedocument @idoc

DELETE BRD
  FROM @DelDocs DD
       INNER JOIN [dbo].[BriefDocuments] BRD
               ON BRD.[recordId] = DD.[recordid]

INSERT
  INTO [dbo].[BriefDocuments]
      ([BriefID],
       [DocumentName],
       [TrackReference],
       [FileLocation],
       [Filedate],
       [DocumentType],
       [DocumentClass],
       [SortOrder],
       [SectionID],
       [IMDOCID])
SELECT ND.[BriefID],
       ND.[DocumentName],
       ND.[TrackReference],
       ND.[FileLocation],
       ND.[Filedate],
       ND.[DocumentType],
       ND.[DocumentClass],
       ND.[SortOrder],
       ND.[SectionID],
       ND.[IMDOCID]
  FROM @MoveDocs ND
 WHERE ND.[recordid] = 0

  INSERT
    INTO @NewSort
        ([recordid],
         [sectionid])
  SELECT BRD.[recordId],
         BRD.[SectionID]
    FROM [dbo].[BriefDocuments] BRD
   WHERE BRD.[BriefId] = @BriefID
ORDER BY BRD.[SectionID],
         BRD.[SortOrder]

  UPDATE NS
     SET NS.[NewSort] = NS.[ID] - NST.[TOTAL]
    FROM @NewSort NS
         CROSS APPLY (SELECT COUNT(1) AS [TOTAL]
                        FROM @NewSort NS2
                       WHERE NS2.[SectionID] < NS.[SectionID]) NST
UPDATE BRD
   SET BRD.[SortOrder] = NS.[NewSort]
  FROM @NewSort NS
       INNER JOIN [dbo].[BriefDocuments] BRD
               ON BRD.[recordid] = NS.[recordid]

COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH

END
GO

CREATE PROCEDURE
       [dbo].[ky_IMMoveBriefDocumentWithinSection]
      (@MoveID INT,
       @DIRECTION INT,
       @MOVESIZE INT)
AS
/*************************************************************************************************************
 *
 * ky_IMMoveBriefDocumentWithinSection - Move documents up or down within a Section
 * 
 * @MoveID - the recordid of the Brief Document that is being moved
 * @DIRECTION - 0 is up, 1 is Down
 * @MOVESIZE - 0: Move the document only one item up or down
 *             1 (not 0): Move the document to the top c.q. bottom of the section
 *
 *************************************************************************************************************/
BEGIN
DECLARE @SectionID INT
DECLARE @SortOrder INT

BEGIN TRANSACTION

BEGIN TRY

IF (@MOVESIZE = 0)
  BEGIN
    SET @MOVESIZE = 1
  END
ELSE
  BEGIN
    SET @MOVESIZE = 1000000
  END

  SELECT @SECTIONID = BRD.[SectionID],
         @SortOrder = BRD.[SortOrder]
    FROM [dbo].[BriefDocuments] BRD
   WHERE BRD.[recordId] = @MoveID
ORDER BY BRD.[SectionID],
         BRD.[SortOrder]

DECLARE @Reor TABLE
       ([recordid]               int,
        [SortOrder]              int     identity(1, 1))

IF (@DIRECTION = 0)
  BEGIN
    INSERT
      INTO @Reor
          ([recordid])
        SELECT BRD.[recordid]
          FROM [dbo].[BriefDocuments] BRD
         WHERE BRD.[SectionID] = @SectionID
           AND BRD.[SortOrder] < @SortOrder - @MOVESIZE
      ORDER BY BRD.[SortOrder]
  END
ELSE
  BEGIN
    INSERT
      INTO @Reor
          ([recordid])
      SELECT BRD.[recordid]
        FROM [dbo].[BriefDocuments] BRD
       WHERE BRD.[SectionID] = @SectionID
         AND BRD.[SortOrder] <= @SortOrder + @MOVESIZE
         AND BRD.[recordId] <> @MoveID
    ORDER BY BRD.[SortOrder]
  END

INSERT
    INTO @Reor
        ([recordid])
VALUES(@MoveID)

IF (@DIRECTION = 0)
  BEGIN
    INSERT
      INTO @Reor
          ([recordid])
        SELECT BRD.[recordid]
          FROM [dbo].[BriefDocuments] BRD
         WHERE BRD.[SectionID] = @SectionID
           AND BRD.[SortOrder] >= @SortOrder - @MOVESIZE
           AND BRD.[recordId] <> @MoveID
      ORDER BY BRD.[SortOrder]
  END
ELSE
  BEGIN
    INSERT
      INTO @Reor
          ([recordid])
      SELECT BRD.[recordid]
        FROM [dbo].[BriefDocuments] BRD
       WHERE BRD.[SectionID] = @SectionID
         AND BRD.[SortOrder] > @SortOrder + @MOVESIZE
    ORDER BY BRD.[SortOrder]
  END

UPDATE BRD
   SET BRD.[SortOrder] = REO.[SortOrder]
  FROM @Reor REO
       INNER JOIN [dbo].[BriefDocuments] BRD
               ON BRD.[recordId] = REO.[recordid]

  SELECT *
    FROM [dbo].[BriefDocuments] BRD
   WHERE BRD.[SectionID] = @SectionID
ORDER BY BRD.[SortOrder]

COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH

END
GO

CREATE PROCEDURE 
       [dbo].[ky_NETReorderAllBriefDocuments]
      (@BriefID INT)
AS
/*************************************************************************************

 ky_NETReorderAllBriefDocuments

 Reorders all Brief Documents within each section so they appear in Date order

 *************************************************************************************/
BEGIN

BEGIN TRANSACTION

BEGIN TRY

DECLARE @reorg TABLE
       ([ID]            int     identity(1, 1),
        [SectionID]     int,
        [recordid]      int)

INSERT
  INTO @reorg
      ([SectionID],
       [recordid])
  SELECT BD.[SectionID],
         BD.[recordId]
    FROM [dbo].[Brief] B
         INNER JOIN [dbo].[BriefSections] BS
                    INNER JOIN [dbo].[BriefDocuments] BD
                            ON BD.[SectionID] = BS.[ID]
                 ON BS.[BriefID] = B.[ID]
   WHERE B.[ID] = @BriefID
ORDER BY BS.[ID],
         BD.[FileDate],
         BD.[TrackReference],
         BD.[IMDOCID],
         BD.[SortOrder]

UPDATE BD
   SET BD.[SortOrder] = R1.[ID] - RTOT.[TOTAL]
  FROM @reorg R1
       CROSS APPLY (SELECT COUNT(1) AS [TOTAL]
                      FROM @reorg R2
                     WHERE R2.[SectionID] < R1.[SectionID]) RTOT
       INNER JOIN [dbo].[BriefDocuments] BD
               ON BD.[recordId] = R1.[recordid]

COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END

GO

CREATE PROCEDURE 
       [dbo].[ky_NETToggleBriefSectionType]
      (@SectionID INT)
AS
/*************************************************************************************

 ky_NETToggleBriefSectionType

 Toggles the Main Section / Cover Page attribute of a Brief Section

 *************************************************************************************/
BEGIN

BEGIN TRANSACTION

DECLARE @reorg TABLE
       ([ID]           int    identity(1, 1),
        [SectionID]    int)

BEGIN TRY

UPDATE BS
   SET BS.[CoverPage] = 1 - BS.[CoverPage],
       BS.[Sequence] = 0
  FROM [dbo].[BriefSections] BS
 WHERE BS.[ID] = @SectionID

INSERT
  INTO @reorg
      ([SectionID])
  SELECT BS.[ID]
    FROM [dbo].[BriefSections] BS1
         INNER JOIN [dbo].[BriefSections] BS
                 ON BS.[BriefID] = BS1.[BriefID]
   WHERE BS1.[ID] = @SectionID
ORDER BY BS.[CoverPage] DESC,
         BS.[Sequence]

UPDATE BS
   SET BS.[Sequence] = R.[ID],
       BS.[khsorter] = CONVERT(VARCHAR(1), BS.[CoverPage])
                     + SUBSTRING(CONVERT(VARCHAR(5), 10000 + R.[ID]), 2, 4)
                     + SUBSTRING(CONVERT(VARCHAR(8), 10000000 + R.[SectionID]), 2, 7)
  FROM @reorg R
       INNER JOIN [dbo].[BriefSections] BS
               ON BS.[ID] = R.[SectionID]

COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH

END
GO

CREATE PROCEDURE
       [dbo].[ky_NETGetCaseFolders]
      (@matter                   varchar(20),
       @documentnamingmethod     varchar(1))
AS
BEGIN
/*************************************************************************************************
 *
 * ky_NETGetCaseFolders
 *
 * Returns the default location for any documents stored against a case/matter
 *
 *************************************************************************************************/

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