/*
select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] like '%ky%'
*/

/**********************************************************************
 *
 * Drop existing Stored Procedures
 *
 **********************************************************************/
if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_ReorderAllBriefDocuments')
  BEGIN
    DROP PROCEDURE dbo.ky_ReorderAllBriefDocuments
  END
GO


if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_MoveBriefDocumentFromSection')
  BEGIN
    DROP PROCEDURE dbo.ky_MoveBriefDocumentFromSection
  END
GO


if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_MoveBriefDocumentToSection')
  BEGIN
    DROP PROCEDURE dbo.ky_MoveBriefDocumentToSection
  END
GO


if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_MoveBriefSection')
  BEGIN
    DROP PROCEDURE dbo.ky_MoveBriefSection
  END
GO


if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_MoveBriefDocument')
  BEGIN
    DROP PROCEDURE dbo.ky_MoveBriefDocument
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_briefsectionupdate')
  BEGIN
    DROP PROCEDURE dbo.ky_briefsectionupdate
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_ReorderBriefSections')
  BEGIN
    DROP PROCEDURE dbo.ky_ReorderBriefSections
  END
GO


if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_ReorderBriefDocSection')
  BEGIN
    DROP PROCEDURE dbo.ky_ReorderBriefDocSection
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_ReorderBriefDocSection2')
  BEGIN
    DROP PROCEDURE dbo.ky_ReorderBriefDocSection2
  END
GO
if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_GenerateBriefTemplate')
  BEGIN
    DROP PROCEDURE dbo.ky_GenerateBriefTemplate
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_GenerateBrief5')
  BEGIN
    DROP PROCEDURE dbo.ky_GenerateBrief5
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_GenerateBrief4')
  BEGIN
    DROP PROCEDURE dbo.ky_GenerateBrief4
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_GenerateBrief3')
  BEGIN
    DROP PROCEDURE dbo.ky_GenerateBrief3
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_GenerateBrief2')
  BEGIN
    DROP PROCEDURE dbo.ky_GenerateBrief2
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_GenerateBrief')
  BEGIN
    DROP PROCEDURE dbo.ky_GenerateBrief
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_finalisebrief')
  BEGIN
    DROP PROCEDURE dbo.ky_finalisebrief
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_deletebrief')
  BEGIN
    DROP PROCEDURE dbo.ky_deletebrief
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_briefsectionadd')
  BEGIN
    DROP PROCEDURE dbo.ky_briefsectionadd
  END
GO


if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_BriefDocAddToSection')
  BEGIN
    DROP PROCEDURE dbo.ky_BriefDocAddToSection
  END
GO


if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'ky_briefdocumentadd')
  BEGIN
    DROP PROCEDURE dbo.ky_briefdocumentadd
  END
GO

/**********************************************************************
 *
 * Drop existing Views
 *
 **********************************************************************/

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'CaseDocsBriefsView')
  BEGIN
    DROP VIEW dbo.CaseDocsBriefsView
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'DiaryBriefs')
  BEGIN
    DROP VIEW dbo.DiaryBriefs
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'BriefContent')
  BEGIN
    DROP VIEW dbo.BriefContent
  END
GO

/**********************************************************************
 *
 * Drop Tables
 *
 **********************************************************************/
if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'BriefTemplateSections')
  BEGIN
    DROP TABLE dbo.BriefTemplateSections
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'BriefTemplates')
  BEGIN
    DROP TABLE dbo.BriefTemplates
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'BriefDocuments')
  BEGIN
    DROP TABLE dbo.BriefDocuments
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'BriefSections')
  BEGIN
    DROP TABLE dbo.BriefSections
  END
GO

if exists (select so.*
             from [sysusers] SU
                  inner join [sysobjects] SO
                          on so.[uid] = su.[uid]
            where su.[name] = 'dbo'
              and so.[name] = 'Brief')
  BEGIN
    DROP TABLE dbo.Brief
  END
GO

/**********************************************************************
 *
 * Recreate Tables
 *
 **********************************************************************/
declare @mattersize int

select @mattersize = sc.[length]
  from syscolumns SC
       inner join sysobjects so
               on so.[id] = sc.[id]
 where so.[name] = 'Matters'
   and sc.[name] = 'Code'

IF @mattersize = 11
   BEGIN
    -- Version 3; Matter Code is 11 Characters long
    CREATE
     TABLE [dbo].[Brief]
          ([ID]                   [int]             IDENTITY(1,1) NOT NULL,
           [Title]                [varchar](200)                  NULL,
           [MatterCode]           [char](11)                      NOT NULL,
           [Author]               [varchar](100)                  NULL,
           [Status]               [char](1)                       NULL,
           [Type]                 [char](3)                       NULL,
           [Location]             [varchar](256)                  NULL,
           [CreationDate]         [datetime]                      NULL,
           [PublishDate]          [datetime]                      NULL,
           [Finalised]            [int]                           NOT NULL DEFAULT (0),
           [Description]          [text]                          NULL,
           CONSTRAINT [PK_Brief] 
                      PRIMARY KEY CLUSTERED 
                     ([ID] ASC)
                   ON [PRIMARY],
           CONSTRAINT [KeyTitle]
                      UNIQUE NONCLUSTERED 
                     ([MatterCode] ASC,
                      [Title] ASC)
                  ON [PRIMARY])
        ON [PRIMARY]
   END
ELSE
   BEGIN
    -- Version 4; Matter Code is 17 Characters long
    CREATE
     TABLE [dbo].[Brief]
          ([ID]                   [int]             IDENTITY(1,1) NOT NULL,
           [Title]                [varchar](200)                  NULL,
           [MatterCode]           [char](17)                      NOT NULL,
           [Author]               [varchar](100)                  NULL,
           [Status]               [char](1)                       NULL,
           [Type]                 [char](3)                       NULL,
           [Location]             [varchar](256)                  NULL,
           [CreationDate]         [datetime]                      NULL,
           [PublishDate]          [datetime]                      NULL,
           [Finalised]            [int]                           NOT NULL DEFAULT (0),
           [Description]          [text]                          NULL,
           CONSTRAINT [PK_Brief] 
                      PRIMARY KEY CLUSTERED 
                     ([ID] ASC)
                   ON [PRIMARY],
           CONSTRAINT [KeyTitle]
                      UNIQUE NONCLUSTERED 
                     ([MatterCode] ASC,
                      [Title] ASC)
                  ON [PRIMARY])
        ON [PRIMARY]
   END

GO

	CREATE NONCLUSTERED INDEX 
           [BriefMatter]
        ON [dbo].[Brief] 
          ([MatterCode] ASC,
           [ID] ASC)
        ON [PRIMARY]
GO

    CREATE
     TABLE [dbo].[BriefSections]
          ([ID]                   [int]             IDENTITY(1,1) NOT NULL,
           [BriefID]              [int]                           NOT NULL,
           [Sequence]             [int]                           NOT NULL,
           [CoverPage]            [bit]                           NOT NULL DEFAULT (0),
           [Section]              [varchar](100)                  NOT NULL,
           [khsorter]             [varchar](12)                   NULL,
           CONSTRAINT [PK_BriefSections]
                      PRIMARY KEY CLUSTERED 
                     ([ID] ASC) ON [PRIMARY],
           CONSTRAINT [UN_BriefSections_S]
                      UNIQUE NONCLUSTERED 
                     ([BriefID] ASC,
                      [CoverPage] ASC,
                      [Sequence] ASC)
                   ON [PRIMARY])
        ON [PRIMARY]
GO

    ALTER TABLE [dbo].[BriefSections]
                WITH NOCHECK 
            ADD CONSTRAINT [FK_BriefSections_Brief]
                FOREIGN KEY([BriefID])
                REFERENCES [dbo].[Brief]
                          ([ID])
                ON UPDATE CASCADE
                ON DELETE CASCADE
GO

    ALTER TABLE [dbo].[BriefSections]
                CHECK CONSTRAINT [FK_BriefSections_Brief]
GO

    CREATE 
     TABLE [dbo].[BriefDocuments]
          ([recordId]             [int]             IDENTITY(1,1) NOT NULL,
           [BriefId]              [int]                           NULL,
           [DocumentName]         [varchar](255)                  NULL,
           [TrackReference]       [int]                           NULL,
           [FileLocation]         [varchar](255)                  NULL,
           [Filedate]             [datetime]                      NULL,
           [DocumentType]         [char](4)                       NULL,
           [DocumentClass]        [varchar](100)                  NULL,
           [SortOrder]            [int]                           NULL,
           [SectionID]            [int]                           NULL,
           CONSTRAINT [PK_BriefDocuments]
                      PRIMARY KEY CLUSTERED 
                     ([recordId] ASC)
                   ON [PRIMARY])
        ON [PRIMARY]
GO

    CREATE NONCLUSTERED INDEX 
           [BD_BriefIndex]
        ON [dbo].[BriefDocuments] 
          ([BriefId] ASC,
           [TrackReference] DESC,
           [recordId] DESC)
        ON [PRIMARY]
GO

    CREATE 
     TABLE [dbo].[BriefTemplates]
          ([ID]                   [int]             IDENTITY(1,1) NOT NULL,
           [Title]                [varchar](200)                  NULL,
           [WTCode]               [char](4)                       NOT NULL,
           [Author]               [varchar](100)                  NULL,
           [Status]               [char](1)                       NULL,
           [Type]                 [char](3)                       NULL,
           [CreationDate]         [datetime]                      NULL,
           [PublishDate]          [datetime]                      NULL,
           [Description]          [text]                          NULL,
           CONSTRAINT [PK_BriefTemplates]
                      PRIMARY KEY CLUSTERED 
                     ([ID] ASC) ON [PRIMARY],
           CONSTRAINT [BriefTemplates_KeyTitle]
                      UNIQUE NONCLUSTERED 
                     ([Title] ASC)
                   ON [PRIMARY])
        ON [PRIMARY]
GO


    CREATE 
     TABLE [dbo].[BriefTemplateSections]
          ([ID]                   [int]             IDENTITY(1,1) NOT NULL,
    	   [BriefTemplateID]      [int]                           NOT NULL,
    	   [Sequence]             [int]                           NOT NULL,
    	   [CoverPage]            [bit]                           NOT NULL DEFAULT (0),
    	   [Section]              [varchar](100)                  NOT NULL,
           CONSTRAINT [PK_BriefTemplateSections] 
                      PRIMARY KEY CLUSTERED 
                     ([ID] ASC)
                   ON [PRIMARY],
           CONSTRAINT [UN_BriefTemplateSections_S]
                      UNIQUE NONCLUSTERED 
                     ([BriefTemplateID] ASC,
    	              [CoverPage] ASC,
    	              [Sequence] ASC)
                   ON [PRIMARY])
        ON [PRIMARY]
GO

    ALTER TABLE [dbo].[BriefTemplateSections] 
                WITH NOCHECK
            ADD CONSTRAINT [FK_BriefTemplateSections_BriefTemplates] 
                           FOREIGN KEY([BriefTemplateID])
                           REFERENCES [dbo].[BriefTemplates] ([ID])
                           ON UPDATE CASCADE
                           ON DELETE CASCADE
GO

    ALTER TABLE [dbo].[BriefTemplateSections] 
          CHECK CONSTRAINT [FK_BriefTemplateSections_BriefTemplates]
GO

/**********************************************************************
 *
 * Recreate Views
 *
 **********************************************************************/

Create View
       [dbo].[CaseDocsBriefsView]
AS
        select DA.[CaseCode] As MatterCode,
               DA.[TrackReference],
               BR.[ID] AS BriefID,
               case when bd.[recordid] is null
                         then 0
                    else 1 end as DocInBrief,
               DA.[Type],
               DA.[DateEntered],
               DA.[Document],
               DA.[Source],
               DA.[LastAccessDate],
               DA.[EnteredBy],
               DA.[LastAccessBy],
               DA.[Name],
               DA.[FilePath],
               DA.[DiaryID],
               DA.[DocClass]
          from [dbo].[DiaryAttachments] DA
               INNER JOIN [dbo].[Brief] BR
                            ON BR.[MatterCode] = DA.[CaseCode]
               INNER JOIN [dbo].[BriefDocuments] BD
                            ON BD.[BriefID] = BR.[ID]
                           AND BD.[TrackReference] = DA.[TrackReference]
GO

declare @textsize int
declare @crtv varchar(3000)

select @textsize = sc.[length]
  from syscolumns SC
       inner join sysobjects so
               on so.[id] = sc.[id]
 where so.[name] = 'Matters'
   and sc.[name] = 'Code'

IF @textsize = 2000
   BEGIN
    -- Version 4 - AddressTo is defined as VarChar(2000) and
    --             Subject is defined as VarChar(5000)
    SET @crtv = 
'  Create
    VIEW [dbo].[DiaryBriefs]
AS
  SELECT TOP 100 PERCENT
         DAT.[Type],
         DCX.[DocInBrief],
         DCL.[ClassDescription],
         DAT.[LastAccessDate] As [FileDate],
         DAT.[Document],
         DAT.[Source],
         DIA.[EmailAddress],
         DIA.[AddressTo] As [AddressTo],
         DIA.[Subject] As [Subject],
         DAT.[EnteredBy],
         DAT.[DateEntered],
         DAT.[LastAccessBy],
         DAT.[LastAccessDate],
         DCX.[TrackReference],
         DAT.[Name],
         DAT.[FilePath],
         DAT.[DiaryID],
         DIA.[ProcessType],
         DIA.[ActionType],
         DAT.[CaseCode],
         DAT.[DocClass],
         DCX.[BriefID],
           substring(convert(varchar, DAT.[LastAccessDate], 112), 1, 8)
         + substring(convert(varchar, DAT.[LastAccessDate], 108), 1, 8)
         + right(''00000000'' + convert(varchar, DCX.[TrackReference]), 8) As DateSorter
    FROM (  select DAT.[CaseCode],
                   DAT.[TrackReference],
                   0 As BriefID,
                   0 As DocInBrief
              from [dbo].[DiaryAttachments] DAT
          UNION ALL
            select DAT.[CaseCode],
                   DAT.[TrackReference],
                   BR.[ID] As BriefId,
                   case count(BD.[TrackReference])
                        when 0 then 0
                   else 1 end As DocInBrief
              from [dbo].[DiaryAttachments] DAT
                   inner join [dbo].[Brief] BR
                   ON BR.[MatterCode] = DAT.[CaseCode]
                   left outer join [dbo].[BriefDocuments] BD
                                on BD.[BriefId] = BR.[ID]
                               and BD.[TrackReference] = DAT.[TrackReference]
          group by DAT.[CaseCode],
                   DAT.[TrackReference],
                   BR.[ID]) DCX
         INNER JOIN [dbo].[DiaryAttachments] DAT
                    INNER JOIN [dbo].[DocumentClasses] DCL
                            ON DCL.[ClassCode] = DAT.[DocClass]
                    INNER JOIN [dbo].[Diary] DIA
                            ON DIA.[ActionID] = DAT.[DiaryID]
                 ON DAT.[TrackReference] = DCX.[TrackReference]
ORDER BY DAT.CaseCode,
         DCX.BriefID,
           substring(convert(varchar, DAT.[LastAccessDate], 112), 1, 8)
         + substring(convert(varchar, DAT.[LastAccessDate], 108), 1, 8)
         + right(''00000000'' + convert(varchar, DCX.[TrackReference]), 8) Desc'
   END
ELSE
   BEGIN
    -- Version 3 - AddressTo and Subject are defined as Text
    SET @crtv = 
'  Create
    VIEW [dbo].[DiaryBriefs]
AS
  SELECT TOP 100 PERCENT
         DAT.[Type],
         DCX.[DocInBrief],
         DCL.[ClassDescription],
         DAT.[LastAccessDate] As [FileDate],
         DAT.[Document],
         DAT.[Source],
         DIA.[EmailAddress],
         Convert(varchar(2000), SUBSTRING(DIA.[AddressTo], 1, 2000)) As [AddressTo],
         Convert(varchar(5000), SUBSTRING(DIA.[Subject], 1, 5000)) As [Subject],
         DAT.[EnteredBy],
         DAT.[DateEntered],
         DAT.[LastAccessBy],
         DAT.[LastAccessDate],
         DCX.[TrackReference],
         DAT.[Name],
         DAT.[FilePath],
         DAT.[DiaryID],
         DIA.[ProcessType],
         DIA.[ActionType],
         DAT.[CaseCode],
         DAT.[DocClass],
         DCX.[BriefID],
           substring(convert(varchar, DAT.[LastAccessDate], 112), 1, 8)
         + substring(convert(varchar, DAT.[LastAccessDate], 108), 1, 8)
         + right(''00000000'' + convert(varchar, DCX.[TrackReference]), 8) As DateSorter
    FROM (  select DAT.[CaseCode],
                   DAT.[TrackReference],
                   0 As BriefID,
                   0 As DocInBrief
              from [dbo].[DiaryAttachments] DAT
          UNION ALL
            select DAT.[CaseCode],
                   DAT.[TrackReference],
                   BR.[ID] As BriefId,
                   case count(BD.[TrackReference])
                        when 0 then 0
                   else 1 end As DocInBrief
              from [dbo].[DiaryAttachments] DAT
                   inner join [dbo].[Brief] BR
                   ON BR.[MatterCode] = DAT.[CaseCode]
                   left outer join [dbo].[BriefDocuments] BD
                                on BD.[BriefId] = BR.[ID]
                               and BD.[TrackReference] = DAT.[TrackReference]
          group by DAT.[CaseCode],
                   DAT.[TrackReference],
                   BR.[ID]) DCX
         INNER JOIN [dbo].[DiaryAttachments] DAT
                    INNER JOIN [dbo].[DocumentClasses] DCL
                            ON DCL.[ClassCode] = DAT.[DocClass]
                    INNER JOIN [dbo].[Diary] DIA
                            ON DIA.[ActionID] = DAT.[DiaryID]
                 ON DAT.[TrackReference] = DCX.[TrackReference]
ORDER BY DAT.CaseCode,
         DCX.BriefID,
           substring(convert(varchar, DAT.[LastAccessDate], 112), 1, 8)
         + substring(convert(varchar, DAT.[LastAccessDate], 108), 1, 8)
         + right(''00000000'' + convert(varchar, DCX.[TrackReference]), 8) Desc'
   END

   EXEC (@crtv)
	GO

Create 
  View [dbo].[BriefContent]
  with SchemaBinding
As
  select BR.ID As BriefID,
           Convert(varchar, 1 - BS.CoverPage)
         + Substring(Convert(varchar, 10000 + BS.Sequence), 2, 4)
         + Substring(Convert(varchar, 10000 + BD.SortOrder), 2, 4)
         + Substring(Convert(varchar, 10000000 + BD.recordid), 2, 7) As KHSorter,
         BR.MatterCode,
         BR.Title,
         BR.Author,
         BR.Location,
         BS.CoverPage,
         BS.Sequence,
         BS.Section,
         BD.DocumentName,
         BD.TrackReference,
         BD.FileLocation,
         BD.FileDate,
         BS.ID,
         BD.SortOrder,
         BD.RecordId,
         BD.DocumentType,
         (  SELECT MAX(BDLAST.sortorder)
              FROM dbo.BriefDocuments BDLAST
             WHERE BDLAST.BriefID = BD.BriefID
               AND BDLAST.SectionID = BD.SectionID) As LastSO,
         CASE
         WHEN BS.CoverPage = 1
              THEN ''
         ELSE Char(65
                   + BS.Sequence 
                   - (SELECT MIN(BSW.[SEQUENCE])
                        FROM [dbo].[BriefSections] BSW
                       WHERE BSW.[BriefID] = BR.[ID]
                         AND BSW.CoverPage = 0)) END AS SectionLetter
    from dbo.brief BR
         INNER JOIN dbo.BriefSections BS
                 ON BS.BriefID = BR.ID
         INNER JOIN dbo.BriefDocuments BD
                 ON BD.BriefID = BR.ID
                AND BD.SectionID = BS.ID
GO

/**********************************************************************
 *
 * Recreate Stored Procedures
 *
 **********************************************************************/
Create Procedure
       [dbo].[ky_BriefDocAddToSection]
      (@SectionID int,
       @TrackRef int)
AS

DECLARE @BriefID int

SELECT @BriefID = BS.BriefID
  FROM [dbo].[BriefSections] BS
 WHERE BS.[ID] = @SectionID

INSERT
  INTO [dbo].[BriefDocuments]
      ([BriefID],
       [DocumentName],
       [TrackReference],
       [FileLocation],
       [FileDate],
       [DocumentType],
       [DocumentClass],
       [SortOrder],
       [SectionID])
select @BriefID As BriefID,
       DA.[Document] AS [DocumentName],
       DA.[TrackReference] As [TrackReference],
       DA.[FilePath] As [FileLocation],
       DA.[LastAccessDate] As [FileDate],
       DA.[Type] As [DocumentType],
       DA.[DocClass] As [DocumentClass],
       BD2.[SortOrder],
       @SectionID AS SectionID
  from (SELECT IsNull(max(BD.SortOrder), 0) + 1 as SortOrder
          FROM [dbo].[BriefDocuments] BD
         WHERE BD.SectionID = @SectionID) BD2
       INNER JOIN [dbo].[DiaryAttachments] DA
               ON DA.[TrackReference] = @TrackRef

GO

Create Procedure
       [dbo].[ky_briefdocumentadd]
      (@BriefID int,
       @TrackRef int,
       @AddToLastSection int)
AS

Declare @SectionID int

IF (@AddToLastSection = 0)
   BEGIN
      set @SectionID = NULL

      select @SectionID = (SELECT TOP 1 BS.ID AS SectionID
                             from [dbo].[DiaryAttachments] DA
                                  INNER JOIN [dbo].[documentclasses] DC
                                          ON DC.[ClassCode] = DA.[DocClass]
                                  INNER JOIN [dbo].[BriefSections] BS
                                          ON BS.[BriefID] = @BriefID
                                         AND BS.[Section] = DC.[ClassDescription]
                            where DA.[TrackReference] = @TrackRef)
      
      IF (@SectionID IS NULL)
         BEGIN
            INSERT
              INTO [dbo].[BriefSections]
                  ([BriefID],
                   [Sequence],
                   [CoverPage],
                   [Section])
            SELECT @BRIEFID AS BriefID,
                   IsNull(MAX(BS.Sequence), 0) + 1 AS SEQUENCE,
                   0 AS CoverPage,
                   (SELECT DC.[ClassDescription]
                      FROM [dbo].[DiaryAttachments] DA
                           INNER JOIN [dbo].[DocumentClasses] DC
                                   ON DC.[ClassCode] = DA.[DocClass]
                     WHERE DA.[TrackReference] = @TrackRef) AS Section
              FROM [dbo].[BriefSections] BS
             WHERE BS.[BriefID] = @BriefID

               SET @SectionID = SCOPE_IDENTITY()

			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.[ID] = @SectionID

         END
   END

select @SectionID = (  SELECT TOP 1 SectionID
                         FROM (SELECT IsNull(BS.ID, IsNull(BS2.ID, BS3.ID)) AS SectionID,
                                      CASE WHEN BS.ID IS NOT NULL
                                           THEN BS.SEQUENCE
                                      WHEN BS2.ID IS NOT NULL
                                           THEN BS2.SEQUENCE
                                      ELSE BS3.SEQUENCE  END AS SEQUENCE 
                                 from [dbo].[DiaryAttachments] DA
                                      INNER JOIN [dbo].[documentclasses] DC
                                              ON DC.[ClassCode] = DA.[DocClass]
                                      LEFT OUTER JOIN [dbo].[BriefSections] BS
                                              ON BS.[BriefID] = @BriefID
                                             AND BS.[Section] = DC.[ClassDescription]
                                      LEFT OUTER JOIN [dbo].[BriefSections] BS2
                                              ON BS2.[BriefID] = @BriefID
                                             AND BS2.CoverPage = 0
                                      INNER JOIN [dbo].[BriefSections] BS3
                                              ON BS3.[BriefID] = @BriefID
                                where DA.[TrackReference] = @TrackRef) BSS
                     ORDER BY BSS.Sequence DESC)

INSERT
  INTO [dbo].[BriefDocuments]
      ([BriefID],
       [DocumentName],
       [TrackReference],
       [FileLocation],
       [FileDate],
       [DocumentType],
       [DocumentClass],
       [SortOrder],
       [SectionID])
select @BriefID As BriefID,
       DA.[Document] AS [DocumentName],
       DA.[TrackReference] As [TrackReference],
       DA.[FilePath] As [FileLocation],
       DA.[LastAccessDate] As [FileDate],
       DA.[Type] As [DocumentType],
       DA.[DocClass] As [DocumentClass],
       BD2.[SortOrder],
       @SectionID AS SectionID
  from (SELECT IsNull(max(BD.SortOrder), 0) + 1 as SortOrder
          FROM [dbo].[BriefDocuments] BD
         WHERE BD.SectionID = @SectionID) BD2
       INNER JOIN [dbo].[DiaryAttachments] DA
               ON DA.[TrackReference] = @TrackRef

GO

Create Procedure
       [dbo].[ky_briefsectionadd]
      (@BriefID int,
       @Section varchar(100),
       @CoverPage bit)
AS
Declare @DocClass varchar(40)
Declare @SectionID int

Declare @SortOrder int

SET @Section = RTrim(LTrim(@Section))

If @Section = ''
   BEGIN
      SET @Section = NULL
   END

select @SortOrder = max(sequence)
  from [dbo].[BriefSections]
 where [BriefID] = @BriefID

Set @SortOrder = IsNull(@SortOrder, 0) + 1

INSERT
  INTO [dbo].[BriefSections]
      ([BriefID],
       [Sequence],
       [CoverPage],
       [Section])
Select BR.[ID] As [BriefID],
       @SortOrder AS [Sequence],
       IsNull(@CoverPage, 0) As [CoverPage],
       IsNull(@Section, 'Section ' + convert(varchar, @SortOrder)) As [Section]
  from [dbo].[Brief] BR
       LEFT OUTER JOIN [dbo].[BriefSections] BS
                    ON BS.[BriefID] = BR.[ID]
                   AND BS.[Section] = IsNull(@Section, 'Section ' + convert(varchar, @SortOrder))
 where BR.[ID] = @BriefID
   and BS.[ID] IS NULL --Avoid duplication

update BS
   set BS.[khsorter] = convert(varchar, 1 - BS.[CoverPage])
                     + right(convert(varchar, 10000 + BS.[Sequence]), 4)
                     + right(convert(varchar, 10000000 + BS.[ID]), 7)
  from [dbo].[BriefSections] BS
 WHERE [ID] = SCOPE_IDENTITY()

GO

Create Procedure
       [dbo].[ky_DeleteBrief]
      (@BriefID int)
AS

DELETE
  FROM [dbo].[briefdocuments]
 WHERE [BriefID] = @BriefID

DELETE
  FROM [dbo].[briefsections]
 WHERE [BriefID] = @BriefID

DELETE
  FROM [dbo].[brief]
 WHERE [ID] = @BriefID
GO

Create Procedure
       [dbo].[ky_FinaliseBrief]
      (@ID int,
       @Finalise int)
AS

IF (@Finalise = 1)
   BEGIN
      UPDATE [dbo].[brief]
         SET [Finalised] = 1
       WHERE [ID] = @ID
   END
ELSE
   BEGIN
      UPDATE [dbo].[brief]
         SET [Finalised] = 0
       WHERE [ID] = @ID
   END
GO


Create Procedure
       [dbo].[ky_GenerateBrief]
      (@MatterCode varchar(17),
       @BriefTitle varchar(200),
       @BriefAuthor varchar(100),
       @Location varchar(256),
       @Description varchar(4000),
       @CreateDate varchar(11),
       @PublishDate varchar(11))
AS

Declare @NewSectionNo int
Declare @NewSection varchar(40)

Declare @NewBriefID int

Declare @DocBriefID int
Declare @DocName varchar(255)
Declare @DocTrackRef int
Declare @DocFileLoc varchar(255)
Declare @DocFileDate datetime
Declare @DocType char(4)
Declare @DocClass varchar(100)
Declare @DocSortOrder int
Declare @DocLastSectionID int
Declare @DocSectionID int

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()

INSERT
  INTO [dbo].[BriefSections]
      ([BriefID],
       [CoverPage],
       [Sequence],
       [Section])
VALUES(@NewBriefID,
       1,
       1,
       'Cover Letters')        

Set @NewSectionNo = 1

DECLARE NewSections Cursor
FOR
select Distinct
       DC.[ClassDescription]
  from [dbo].[DiaryAttachments] DA
       INNER JOIN [dbo].[DocumentClasses] DC
               ON DC.[ClassCode] = DA.[DocClass]
 where DA.[CaseCode] = @MatterCode

Open NewSections
Fetch Next
 From NewSections
 INTO @NewSection
WHILE @@FETCH_STATUS = 0
   BEGIN
      Set @NewSectionNo = @NewSectionNo + 1

      INSERT
        INTO [dbo].[BriefSections]
            ([BriefID],
             [CoverPage],
             [Sequence],
             [Section])
      VALUES(@NewBriefID,
             0,
             @NewSectionNo,
             @NewSection)        

      Fetch Next
       From NewSections
       INTO @NewSection
   END

Close NewSections

Deallocate NewSections

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

Declare NewDocuments Cursor 
FOR
  select BR.[ID] As BriefID,
         DA.[Document] As DocumentName,
         DA.[TrackReference] As TrackReference,
         DA.[FilePath] As FileLocation,
         DA.[LastAccessDate] As FileDate,
         DA.[Type] As DocumentType,
         DC.[ClassDescription] As DocumentClass,
         BS.[ID] As SectionID
    from [dbo].[BriefSections] BS
         INNER JOIN [dbo].[Brief] BR
                 ON BR.[ID] = BS.[BriefID]
         INNER JOIN [dbo].[documentclasses] DC
                 ON DC.[ClassDescription] = BS.[Section]
         INNER JOIN [dbo].[diaryattachments] DA
                 ON DA.[CaseCode] = BR.[MatterCode]
                AND DA.[DocClass] = DC.[ClassCode]
   where BS.BriefID = @NewBriefID
ORDER BY BS.CoverPage Desc,
         BS.Sequence Asc

SET @DocSortOrder = 0
SET @DocLastSectionID = 0

OPEN NewDocuments
FETCH NEXT
 FROM NEWDOCUMENTS
 INTO @DocBriefID,
      @DocName,
      @DocTrackRef,
      @DocFileLoc,
      @DocFileDate,
      @DocType,
      @DocClass,
      @DocSectionID
WHILE @@FETCH_STATUS = 0
   BEGIN
      IF (@DocLastSectionID <> @DocSectionID)
         BEGIN
            SET @DocLastSectionID = @DocSectionID
            SET @DocSortOrder = 0
         END

      SET @DocSortOrder = @DocSortOrder + 1

      INSERT
        INTO [dbo].[BriefDocuments]
            ([BriefID],
             [DocumentName],
             [TrackReference],
             [FileLocation],
             [FileDate],
             [DocumentType],
             [DocumentClass],
             [SortOrder],
             [SectionID])
      VALUES(@DocBriefID,
             @DocName,
             @DocTrackRef,
             @DocFileLoc,
             @DocFileDate,
             @DocType,
             @DocClass,
             @DocSortOrder,
             @DocSectionID)

      FETCH NEXT
       FROM NEWDOCUMENTS
       INTO @DocBriefID,
            @DocName,
            @DocTrackRef,
            @DocFileLoc,
            @DocFileDate,
            @DocType,
            @DocClass,
            @DocSectionID
   END
CLOSE NewDocuments
DEALLOCATE NewDocuments

GO

Create Procedure
       [dbo].[ky_GenerateBrief2]
      (@MatterCode varchar(17),
       @BriefTitle varchar(200),
       @BriefAuthor varchar(100),
       @Location varchar(256),
       @Description varchar(4000),
       @CreateDate varchar(11),
       @PublishDate varchar(11),
       @CreateOption int)
AS

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

      CreateOption: 1 - Create a New Brief containing all the documents
                        from the Case

                    2 - Create a New Brief as a copy of the last Brief for this
                        Case

                    3 - Create a New Brief that is blank

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

Declare @NewSectionNo int
Declare @NewSection varchar(40)

Declare @NewBriefID int

Declare @DocBriefID int
Declare @DocName varchar(255)
Declare @DocTrackRef int
Declare @DocFileLoc varchar(255)
Declare @DocFileDate datetime
Declare @DocType char(4)
Declare @DocClass varchar(100)
Declare @DocSortOrder int
Declare @DocLastSectionID int
Declare @DocSectionID int

Declare @LastBriefID int

IF @CreateOption = 2
   BEGIN
        Select Top 1
               @LastBriefID = BR.[ID]
          from [dbo].[brief] BR
         where BR.[MatterCode] = @MatterCode
      Order by BR.[ID] desc
   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()

IF @CreateOption <> 2
   BEGIN
      INSERT
        INTO [dbo].[BriefSections]
            ([BriefID],
             [CoverPage],
             [Sequence],
             [Section])
      VALUES(@NewBriefID,
             1,
             1,
             'Cover Letters')
   END

IF @CreateOption = 1
   BEGIN
      Set @NewSectionNo = 1

      DECLARE NewSections Cursor
      FOR
      select Distinct
             DC.[ClassDescription]
        from [dbo].[DiaryAttachments] DA
             INNER JOIN [dbo].[DocumentClasses] DC
                     ON DC.[ClassCode] = DA.[DocClass]
       where DA.[CaseCode] = @MatterCode

      Open NewSections
      Fetch Next
       From NewSections
       INTO @NewSection
      WHILE @@FETCH_STATUS = 0
         BEGIN
            Set @NewSectionNo = @NewSectionNo + 1

            INSERT
              INTO [dbo].[BriefSections]
                  ([BriefID],
                   [CoverPage],
                   [Sequence],
                   [Section])
            VALUES(@NewBriefID,
                   0,
                   @NewSectionNo,
                   @NewSection)        

            Fetch Next
             From NewSections
             INTO @NewSection
         END

      Close NewSections

      Deallocate NewSections
   END

IF @CreateOption = 2
   BEGIN
      INSERT
        INTO [dbo].[BriefSections]
            ([BriefID],
             [CoverPage],
             [Sequence],
             [Section])
      SELECT @NewBriefID,
             BS.[CoverPage],
             BS.[Sequence],
             BS.[Section]
        FROM [dbo].[BriefSections] BS
       WHERE BS.[BriefID] = @LastBriefID
   END

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 @CreateOption = 1
   BEGIN
      Declare NewDocuments Cursor 
      FOR
        select BR.[ID] As BriefID,
               DA.[Document] As DocumentName,
               DA.[TrackReference] As TrackReference,
               DA.[FilePath] As FileLocation,
               DA.[LastAccessDate] As FileDate,
               DA.[Type] As DocumentType,
               DC.[ClassDescription] As DocumentClass,
               BS.[ID] As SectionID
          from [dbo].[BriefSections] BS
               INNER JOIN [dbo].[Brief] BR
                       ON BR.[ID] = BS.[BriefID]
               INNER JOIN [dbo].[documentclasses] DC
                       ON DC.[ClassDescription] = BS.[Section]
               INNER JOIN [dbo].[diaryattachments] DA
                       ON DA.[CaseCode] = BR.[MatterCode]
                      AND DA.[DocClass] = DC.[ClassCode]
         where BS.BriefID = @NewBriefID
      ORDER BY BS.CoverPage Desc,
               BS.Sequence Asc

      SET @DocSortOrder = 0
      SET @DocLastSectionID = 0

      OPEN NewDocuments
      FETCH NEXT
       FROM NEWDOCUMENTS
       INTO @DocBriefID,
            @DocName,
            @DocTrackRef,
            @DocFileLoc,
            @DocFileDate,
            @DocType,
            @DocClass,
            @DocSectionID
      WHILE @@FETCH_STATUS = 0
         BEGIN
            IF (@DocLastSectionID <> @DocSectionID)
               BEGIN
                  SET @DocLastSectionID = @DocSectionID
                  SET @DocSortOrder = 0
               END

            SET @DocSortOrder = @DocSortOrder + 1

            INSERT
              INTO [dbo].[BriefDocuments]
                  ([BriefID],
                   [DocumentName],
                   [TrackReference],
                   [FileLocation],
                   [FileDate],
                   [DocumentType],
                   [DocumentClass],
                   [SortOrder],
                   [SectionID])
            VALUES(@DocBriefID,
                   @DocName,
                   @DocTrackRef,
                   @DocFileLoc,
                   @DocFileDate,
                   @DocType,
                   @DocClass,
                   @DocSortOrder,
                   @DocSectionID)

            FETCH NEXT
             FROM NEWDOCUMENTS
             INTO @DocBriefID,
                  @DocName,
                  @DocTrackRef,
                  @DocFileLoc,
                  @DocFileDate,
                  @DocType,
                  @DocClass,
                  @DocSectionID
         END
      CLOSE NewDocuments
      DEALLOCATE NewDocuments
   END

IF @CreateOption = 2
   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

GO

Create Procedure
       [dbo].[ky_GenerateBrief3]
      (@MatterCode varchar(17),
       @BriefTitle varchar(200),
       @BriefAuthor varchar(100),
       @Location varchar(256),
       @Description varchar(4000),
       @CreateDate varchar(11),
       @PublishDate varchar(11),
       @ImportCaseDocs int,
       @CopyBrief int)
AS

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

      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


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

Declare @NewSectionNo int
Declare @NewSection varchar(40)

Declare @NewBriefID int

Declare @DocBriefID int
Declare @DocName varchar(255)
Declare @DocTrackRef int
Declare @DocFileLoc varchar(255)
Declare @DocFileDate datetime
Declare @DocType char(4)
Declare @DocClass varchar(100)
Declare @DocSortOrder int
Declare @DocLastSectionID int
Declare @DocSectionID int

Declare @LastBriefID int

IF @CopyBrief = 1
   BEGIN
        Select Top 1
               @LastBriefID = BR.[ID]
          from [dbo].[brief] BR
         where BR.[MatterCode] = @MatterCode
      Order by BR.[ID] desc
   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()

INSERT
INTO [dbo].[BriefSections]
    ([BriefID],
     [CoverPage],
     [Sequence],
     [Section])
VALUES(@NewBriefID,
     1,
     1,
     'Cover Letters')

IF @ImportCaseDocs = 1
   BEGIN
      Set @NewSectionNo = 1

      DECLARE NewSections Cursor
      FOR
      select Distinct
             DC.[ClassDescription]
        from [dbo].[DiaryAttachments] DA
             INNER JOIN [dbo].[DocumentClasses] DC
                     ON DC.[ClassCode] = DA.[DocClass]
       where DA.[CaseCode] = @MatterCode

      Open NewSections
      Fetch Next
       From NewSections
       INTO @NewSection
      WHILE @@FETCH_STATUS = 0
         BEGIN
            Set @NewSectionNo = @NewSectionNo + 1

            INSERT
              INTO [dbo].[BriefSections]
                  ([BriefID],
                   [CoverPage],
                   [Sequence],
                   [Section])
            VALUES(@NewBriefID,
                   0,
                   @NewSectionNo,
                   @NewSection)        

            Fetch Next
             From NewSections
             INTO @NewSection
         END

      Close NewSections

      Deallocate NewSections
   END

IF @CopyBrief = 1
   BEGIN
      INSERT
        INTO [dbo].[BriefSections]
            ([BriefID],
             [CoverPage],
             [Sequence],
             [Section])
      SELECT @NewBriefID,
             BS.[CoverPage],
             BS.[Sequence],
             BS.[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

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 @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

IF @ImportCaseDocs = 1
   BEGIN
      Declare NewDocuments Cursor 
      FOR
        select BR.[ID] As BriefID,
               DA.[Document] As DocumentName,
               DA.[TrackReference] As TrackReference,
               DA.[FilePath] As FileLocation,
               DA.[LastAccessDate] As FileDate,
               DA.[Type] As DocumentType,
               DC.[ClassDescription] As DocumentClass,
               BS.[ID] As SectionID
          from [dbo].[BriefSections] BS
               INNER JOIN [dbo].[Brief] BR
                       ON BR.[ID] = BS.[BriefID]
               INNER JOIN [dbo].[documentclasses] DC
                       ON DC.[ClassDescription] = BS.[Section]
               INNER JOIN [dbo].[diaryattachments] DA
                       ON DA.[CaseCode] = BR.[MatterCode]
                      AND DA.[DocClass] = DC.[ClassCode]
               LEFT OUTER JOIN [dbo].[BriefDocuments] BD
                            ON BD.[BriefID] = BR.[ID]
                           AND BD.[DocumentName] = DA.[Document]
         where BS.BriefID = @NewBriefID
           AND BD.[recordid] IS NULL
      ORDER BY BS.CoverPage Desc,
               BS.Sequence Asc

      SET @DocSortOrder = 0
      SET @DocLastSectionID = 0

      OPEN NewDocuments
      FETCH NEXT
       FROM NEWDOCUMENTS
       INTO @DocBriefID,
            @DocName,
            @DocTrackRef,
            @DocFileLoc,
            @DocFileDate,
            @DocType,
            @DocClass,
            @DocSectionID
      WHILE @@FETCH_STATUS = 0
         BEGIN
            IF (@DocLastSectionID <> @DocSectionID)
               BEGIN
                  SET @DocLastSectionID = @DocSectionID
                  SET @DocSortOrder = 0
               END

            SET @DocSortOrder = @DocSortOrder + 1

            INSERT
              INTO [dbo].[BriefDocuments]
                  ([BriefID],
                   [DocumentName],
                   [TrackReference],
                   [FileLocation],
                   [FileDate],
                   [DocumentType],
                   [DocumentClass],
                   [SortOrder],
                   [SectionID])
            VALUES(@DocBriefID,
                   @DocName,
                   @DocTrackRef,
                   @DocFileLoc,
                   @DocFileDate,
                   @DocType,
                   @DocClass,
                   @DocSortOrder,
                   @DocSectionID)

            FETCH NEXT
             FROM NEWDOCUMENTS
             INTO @DocBriefID,
                  @DocName,
                  @DocTrackRef,
                  @DocFileLoc,
                  @DocFileDate,
                  @DocType,
                  @DocClass,
                  @DocSectionID
         END
      CLOSE NewDocuments
      DEALLOCATE NewDocuments
   END


GO

Create Procedure
       [dbo].[ky_GenerateBrief4]
      (@MatterCode varchar(17),
       @BriefTitle varchar(200),
       @BriefAuthor varchar(100),
       @Location varchar(256),
       @Description varchar(4000),
       @CreateDate varchar(11),
       @PublishDate varchar(11),
       @ImportCaseDocs int,
       @CopyBrief int,
       @BriefTemplateID int)
AS

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

      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


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

Declare @NewSectionNo int
Declare @NewSection varchar(40)

Declare @NewBriefID int

Declare @DocBriefID int
Declare @DocName varchar(255)
Declare @DocTrackRef int
Declare @DocFileLoc varchar(255)
Declare @DocFileDate datetime
Declare @DocType char(4)
Declare @DocClass varchar(100)
Declare @DocSortOrder int
Declare @DocLastSectionID int
Declare @DocSectionID int

Declare @LastBriefID int

IF @CopyBrief = 1
   BEGIN
        Select Top 1
               @LastBriefID = BR.[ID]
          from [dbo].[brief] BR
         where BR.[MatterCode] = @MatterCode
      Order by BR.[ID] desc
   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()

IF IsNull(@BriefTemplateID, 0) <> 0
   BEGIN
      INSERT
        INTO [dbo].[BriefSections]
            ([BriefID],
             [CoverPage],
             [Sequence],
             [Section])
      SELECT @NewBriefID,
             BTS.[CoverPage],
             BTS.[Sequence],
             BTS.[Section]
        FROM [dbo].[BriefTemplateSections] BTS
       WHERE BTS.[BriefTemplateID] = @BriefTemplateID
   END

IF IsNull(@BriefTemplateID, 0) = 0
   BEGIN
      INSERT
        INTO [dbo].[BriefSections]
            ([BriefID],
             [CoverPage],
             [Sequence],
             [Section])
      VALUES(@NewBriefID,
             1,
             1,
             'Cover Letters')
   END

IF @ImportCaseDocs = 1
   BEGIN
      Select @NewSectionNo = MAX(sequence)
        FROM [dbo].[BriefSections] BS
       WHERE BS.[BriefID] = @NewBriefID

      DECLARE NewSections Cursor
      FOR
      select Distinct
             DC.[ClassDescription]
        from [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]
       where DA.[CaseCode] = @MatterCode
         and BS2.[id] is null

      Open NewSections
      Fetch Next
       From NewSections
       INTO @NewSection
      WHILE @@FETCH_STATUS = 0
         BEGIN
            Set @NewSectionNo = @NewSectionNo + 1

            INSERT
              INTO [dbo].[BriefSections]
                  ([BriefID],
                   [CoverPage],
                   [Sequence],
                   [Section])
            VALUES(@NewBriefID,
                   0,
                   @NewSectionNo,
                   @NewSection)        

            Fetch Next
             From NewSections
             INTO @NewSection
         END

      Close NewSections

      Deallocate NewSections
   END

IF @CopyBrief = 1
   BEGIN
      INSERT
        INTO [dbo].[BriefSections]
            ([BriefID],
             [CoverPage],
             [Sequence],
             [Section])
      SELECT @NewBriefID,
             BS.[CoverPage],
             BS.[Sequence],
             BS.[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

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 @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

IF @ImportCaseDocs = 1
   BEGIN
      Declare NewDocuments Cursor 
      FOR
        select BR.[ID] As BriefID,
               DA.[Document] As DocumentName,
               DA.[TrackReference] As TrackReference,
               DA.[FilePath] As FileLocation,
               DA.[LastAccessDate] As FileDate,
               DA.[Type] As DocumentType,
               DC.[ClassDescription] As DocumentClass,
               BS.[ID] As SectionID
          from [dbo].[BriefSections] BS
               INNER JOIN [dbo].[Brief] BR
                       ON BR.[ID] = BS.[BriefID]
               INNER JOIN [dbo].[documentclasses] DC
                       ON DC.[ClassDescription] = BS.[Section]
               INNER JOIN [dbo].[diaryattachments] DA
                       ON DA.[CaseCode] = BR.[MatterCode]
                      AND DA.[DocClass] = DC.[ClassCode]
               LEFT OUTER JOIN [dbo].[BriefDocuments] BD
                            ON BD.[BriefID] = BR.[ID]
                           AND BD.[DocumentName] = DA.[Document]
         where BS.BriefID = @NewBriefID
           AND BD.[recordid] IS NULL
      ORDER BY BS.CoverPage Desc,
               BS.Sequence Asc

      SET @DocSortOrder = 0
      SET @DocLastSectionID = 0

      OPEN NewDocuments
      FETCH NEXT
       FROM NEWDOCUMENTS
       INTO @DocBriefID,
            @DocName,
            @DocTrackRef,
            @DocFileLoc,
            @DocFileDate,
            @DocType,
            @DocClass,
            @DocSectionID
      WHILE @@FETCH_STATUS = 0
         BEGIN
            IF (@DocLastSectionID <> @DocSectionID)
               BEGIN
                  SET @DocLastSectionID = @DocSectionID
                  SET @DocSortOrder = 0
               END

            SET @DocSortOrder = @DocSortOrder + 1

            INSERT
              INTO [dbo].[BriefDocuments]
                  ([BriefID],
                   [DocumentName],
                   [TrackReference],
                   [FileLocation],
                   [FileDate],
                   [DocumentType],
                   [DocumentClass],
                   [SortOrder],
                   [SectionID])
            VALUES(@DocBriefID,
                   @DocName,
                   @DocTrackRef,
                   @DocFileLoc,
                   @DocFileDate,
                   @DocType,
                   @DocClass,
                   @DocSortOrder,
                   @DocSectionID)

            FETCH NEXT
             FROM NEWDOCUMENTS
             INTO @DocBriefID,
                  @DocName,
                  @DocTrackRef,
                  @DocFileLoc,
                  @DocFileDate,
                  @DocType,
                  @DocClass,
                  @DocSectionID
         END
      CLOSE NewDocuments
      DEALLOCATE NewDocuments
   END


GO

Create Procedure
       [dbo].[ky_GenerateBrief5]
      (@MatterCode varchar(17),
       @BriefTitle varchar(200),
       @BriefAuthor varchar(100),
       @Location varchar(256),
       @Description varchar(4000),
       @CreateDate varchar(11),
       @PublishDate varchar(11),
       @ImportCaseDocs int,
       @CopyBrief int,
       @NonClassified int,
       @BriefTemplateID int)
AS

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

      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.

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

Declare @NewSectionNo int
Declare @NewSection varchar(40)

Declare @NewBriefID int

Declare @DocBriefID int
Declare @DocName varchar(255)
Declare @DocTrackRef int
Declare @DocFileLoc varchar(255)
Declare @DocFileDate datetime
Declare @DocType char(4)
Declare @DocClass varchar(100)
Declare @DocSortOrder int
Declare @DocLastSectionID int
Declare @DocSectionID int

Declare @LastBriefID int

IF @CopyBrief = 1
   BEGIN
        Select Top 1
               @LastBriefID = BR.[ID]
          from [dbo].[brief] BR
         where BR.[MatterCode] = @MatterCode
      Order by BR.[ID] desc
   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()

IF IsNull(@BriefTemplateID, 0) <> 0
   BEGIN
      INSERT
        INTO [dbo].[BriefSections]
            ([BriefID],
             [CoverPage],
             [Sequence],
             [Section])
      SELECT @NewBriefID,
             BTS.[CoverPage],
             BTS.[Sequence],
             BTS.[Section]
        FROM [dbo].[BriefTemplateSections] BTS
       WHERE BTS.[BriefTemplateID] = @BriefTemplateID
   END

IF IsNull(@BriefTemplateID, 0) = 0
   BEGIN
      INSERT
        INTO [dbo].[BriefSections]
            ([BriefID],
             [CoverPage],
             [Sequence],
             [Section])
      VALUES(@NewBriefID,
             1,
             1,
             'Cover Letters')
   END

IF @ImportCaseDocs = 1
   BEGIN
      IF @NonClassified = 1
         BEGIN
            Select @NewSectionNo = MAX(sequence)
              FROM [dbo].[BriefSections] BS
             WHERE BS.[BriefID] = @NewBriefID

            DECLARE NewSections Cursor
            FOR
            select Distinct
                   DC.[ClassDescription]
              from [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]
             where DA.[CaseCode] = @MatterCode
               and BS2.[id] is null

            Open NewSections
            Fetch Next
             From NewSections
             INTO @NewSection
            WHILE @@FETCH_STATUS = 0
               BEGIN
                  Set @NewSectionNo = @NewSectionNo + 1

                  INSERT
                    INTO [dbo].[BriefSections]
                        ([BriefID],
                         [CoverPage],
                         [Sequence],
                         [Section])
                  VALUES(@NewBriefID,
                         0,
                         @NewSectionNo,
                         @NewSection)        

                  Fetch Next
                   From NewSections
                   INTO @NewSection
               END

            Close NewSections

            Deallocate NewSections
         END
   END

IF @CopyBrief = 1
   BEGIN
      INSERT
        INTO [dbo].[BriefSections]
            ([BriefID],
             [CoverPage],
             [Sequence],
             [Section])
      SELECT @NewBriefID,
             BS.[CoverPage],
             BS.[Sequence],
             BS.[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

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 @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

IF @ImportCaseDocs = 1
   BEGIN
      Declare NewDocuments Cursor 
      FOR
        select BR.[ID] As BriefID,
               DA.[Document] As DocumentName,
               DA.[TrackReference] As TrackReference,
               DA.[FilePath] As FileLocation,
               DA.[LastAccessDate] As FileDate,
               DA.[Type] As DocumentType,
               DC.[ClassDescription] As DocumentClass,
               BS.[ID] As SectionID
          from [dbo].[BriefSections] BS
               INNER JOIN [dbo].[Brief] BR
                       ON BR.[ID] = BS.[BriefID]
               INNER JOIN [dbo].[documentclasses] DC
                       ON DC.[ClassDescription] = BS.[Section]
               INNER JOIN [dbo].[diaryattachments] DA
                       ON DA.[CaseCode] = BR.[MatterCode]
                      AND DA.[DocClass] = DC.[ClassCode]
               LEFT OUTER JOIN [dbo].[BriefDocuments] BD
                            ON BD.[BriefID] = BR.[ID]
                           AND BD.[DocumentName] = DA.[Document]
         where BS.BriefID = @NewBriefID
           AND BD.[recordid] IS NULL
      ORDER BY BS.CoverPage Desc,
               BS.Sequence Asc,
               DA.[LastAccessDate] Asc

      SET @DocSortOrder = 0
      SET @DocLastSectionID = 0

      OPEN NewDocuments
      FETCH NEXT
       FROM NEWDOCUMENTS
       INTO @DocBriefID,
            @DocName,
            @DocTrackRef,
            @DocFileLoc,
            @DocFileDate,
            @DocType,
            @DocClass,
            @DocSectionID
      WHILE @@FETCH_STATUS = 0
         BEGIN
            IF (@DocLastSectionID <> @DocSectionID)
               BEGIN
                  SET @DocLastSectionID = @DocSectionID
                  SET @DocSortOrder = 0
               END

            SET @DocSortOrder = @DocSortOrder + 1

            INSERT
              INTO [dbo].[BriefDocuments]
                  ([BriefID],
                   [DocumentName],
                   [TrackReference],
                   [FileLocation],
                   [FileDate],
                   [DocumentType],
                   [DocumentClass],
                   [SortOrder],
                   [SectionID])
            VALUES(@DocBriefID,
                   @DocName,
                   @DocTrackRef,
                   @DocFileLoc,
                   @DocFileDate,
                   @DocType,
                   @DocClass,
                   @DocSortOrder,
                   @DocSectionID)

            FETCH NEXT
             FROM NEWDOCUMENTS
             INTO @DocBriefID,
                  @DocName,
                  @DocTrackRef,
                  @DocFileLoc,
                  @DocFileDate,
                  @DocType,
                  @DocClass,
                  @DocSectionID
         END
      CLOSE NewDocuments
      DEALLOCATE NewDocuments
   END
GO

Create Procedure
       [dbo].[ky_GenerateBriefTemplate]
      (@BriefID int,
       @Title varchar(200),
       @Author varchar(100),
       @Description varchar(2000))
AS

Declare @NewTemplateID Int

DELETE BTS
  from [dbo].[BriefTemplates] BT
       INNER JOIN [dbo].[BriefTemplateSections] BTS
               ON BTS.[BriefTemplateID] = BT.[ID]
 where [BT].[Title] = @Title

DELETE BT
  from [dbo].[BriefTemplates] BT
 where [BT].[Title] = @Title

INSERT
  INTO [dbo].[BriefTemplates]
      (Title,
       WTCode,
       Author,
       CreationDate,
       PublishDate,
       Description)
SELECT @Title As Title,
       CM.[CSWktCode] As WTCode,
       @Author As Author,
       getDate() As CreationDate,
       getDate() As PublishDate,
       @Description As Description 
  from [dbo].[Brief] BR
       INNER JOIN [dbo].[Matters] MAT
                  LEFT OUTER JOIN [dbo].[CaseMaster] CM
                               ON CM.[CSCode] = MAT.[Code]
               ON MAT.[Code] = BR.[MatterCode] 
 where BR.[id] = @BriefID

SET @NewTemplateID = SCOPE_IDENTITY()

INSERT
  INTO [dbo].[BriefTemplateSections]
      (BriefTemplateID,
       Sequence,
       CoverPage,
       Section)
SELECT @NewTemplateID,
       BS.[Sequence],
       BS.[CoverPage],
       BS.[Section]
  From [dbo].[BriefSections] BS
 WHERE BS.[BriefID] = @BriefID
GO

Create Procedure
       [dbo].[ky_ReorderBriefDocSection]
      (@RecID Integer)
AS

/**************************************************************
 *                                                            *
 * ky_ReorderBriefDocSection                                  *
 *                                                            *
 * Rearranges the documents in a brief section so they        *
 * get consecutive sort order numbers starting with 1         *
 *                                                            *
 * Parameters: RecID, the recordid column of the document     *
 *                                                            *
 **************************************************************/

DECLARE @counter integer
DECLARE @CurRecID Integer
DECLARE @CurSO Integer

DECLARE CURREORDER CURSOR
FOR
  SELECT BD2.recordid,
         BD2.sortorder
    FROM BriefDocuments BD
         INNER JOIN BriefDocuments BD2
                 ON BD2.BriefID = BD.BriefID
                AND BD2.SectionID = BD.SectionID
   WHERE BD.recordid = @RecID
ORDER BY BD2.sortorder,
         BD2.recordid

OPEN CURREORDER

SET @Counter = 0
FETCH NEXT FROM CURREORDER INTO @CurRecID, @CurSO
WHILE @@FETCH_STATUS = 0
	BEGIN
        SET @Counter = @Counter + 1

        UPDATE BriefDocuments SET sortorder = @Counter WHERE recordid = @CurRecID

		FETCH NEXT FROM CURREORDER INTO @CurRecID, @CurSO
    END 

CLOSE CURREORDER

DEALLOCATE CURREORDER
GO

Create Procedure
       [dbo].[ky_ReorderBriefDocSection2]
      (@SectionID Integer)
AS

/**************************************************************
 *                                                            *
 * ky_ReorderBriefDocSection2                                 *
 *                                                            *
 * Rearranges the documents in a brief section so they        *
 * get consecutive sort order numbers starting with 1         *
 *                                                            *
 * Parameters: SectionID, reorder docs in this section        *
 *                                                            *
 **************************************************************/

DECLARE @counter integer
DECLARE @CurRecID Integer
DECLARE @CurSO Integer

DECLARE CURREORDER CURSOR
FOR
  SELECT BD2.recordid,
         BD2.sortorder
    FROM BriefSections BS
         INNER JOIN BriefDocuments BD2
                 ON BD2.BriefID = BS.BriefID
                AND BD2.SectionID = BS.[ID]
   WHERE BS.[ID] = @SectionID
ORDER BY BD2.sortorder,
         BD2.recordid

OPEN CURREORDER

SET @Counter = 0
FETCH NEXT FROM CURREORDER INTO @CurRecID, @CurSO
WHILE @@FETCH_STATUS = 0
	BEGIN
        SET @Counter = @Counter + 1

        UPDATE BriefDocuments SET sortorder = @Counter WHERE recordid = @CurRecID

		FETCH NEXT FROM CURREORDER INTO @CurRecID, @CurSO
    END 

CLOSE CURREORDER

DEALLOCATE CURREORDER
GO
Create Procedure
       [dbo].[ky_ReorderBriefSections]
      (@BriefID Integer)
AS

/**************************************************************
 *                                                            *
 * ky_ReorderBriefSections                                    *
 *                                                            *
 * Rearranges the sections in a brief so they                 *
 * get consecutive sequence numbers starting with 1           *
 *                                                            *
 * Parameters: BriefID, for which brief to re-order sections  *
 *                                                            *
 **************************************************************/

DECLARE @counter integer
DECLARE @CurRecID Integer
DECLARE @MaxSeq integer

SELECT @MaxSeq = max(sequence)
  FROM [dbo].[BriefSections]
 Where [BriefID] = @BriefID

/* To avoid unique key constraint problems, we get the maximum
   sequence number (M) used in this brief, and we start by re-numbering
   them M+1, M+2, M+3, etc...

   At the end we subtract M from all sequence numbers, reverting them back
   to 1, 2, 3, 4 again */
Set @MaxSeq = IsNull(@MaxSeq, 0)

DECLARE CURREORDER CURSOR
FOR
  SELECT BS.[id]
    FROM [dbo].[BriefSections] BS
   WHERE BS.[BriefID] = @BriefID
ORDER BY BS.[CoverPage] Desc,
         BS.[Sequence] Asc,
         BS.[id] Asc

OPEN CURREORDER

/* To avoid unique key constraint problems, we get the maximum
   sequence number (M) used in this brief, and we start by re-numbering
   them M+1, M+2, M+3, etc...

   At the end we subtract M from all sequence numbers, reverting them back
   to 1, 2, 3, 4 again */
SET @Counter = @MaxSeq
FETCH NEXT FROM CURREORDER INTO @CurRecID
WHILE @@FETCH_STATUS = 0
	BEGIN
        SET @Counter = @Counter + 1

        UPDATE BS
           SET BS.[sequence] = @Counter
          FROM [dbo].[BriefSections] BS
         WHERE BS.[id] = @CurRecID

		FETCH NEXT FROM CURREORDER INTO @CurRecID
    END 

CLOSE CURREORDER

DEALLOCATE CURREORDER

/* To avoid unique key constraint problems, we get the maximum
   sequence number (M) used in this brief, and we start by re-numbering
   them M+1, M+2, M+3, etc...

   At the end we subtract M from all sequence numbers, reverting them back
   to 1, 2, 3, 4 again */
UPDATE BS
   SET BS.[Sequence] = BS.[Sequence] - @MaxSeq,
       BS.[khsorter] = convert(varchar, 1 - BS.[coverpage])
                     + right(convert(varchar, 10000 + BS.[Sequence] - @MaxSeq), 4)
                     + right(convert(varchar, 10000000 + BS.[ID]), 7)
  FROM [dbo].[BriefSections] BS
 WHERE BS.[BriefID] = @BriefID

 
GO

Create Procedure
       [dbo].[ky_briefsectionupdate]
      (@ID int,
       @Section varchar(100),
       @CoverPage bit)
AS

DECLARE @BriefID int

SELECT @BriefID = BS.[BriefID]
  FROM [dbo].[BriefSections] BS
 WHERE BS.[ID] = @ID

update BS
   set BS.[CoverPage] = @CoverPage,
       BS.[Section] = @Section,
       BS.[khsorter] = convert(varchar, 1 - @CoverPage)
                     + right(convert(varchar, 10000 + BS.[Sequence]), 4)
                     + right(convert(varchar, 10000000 + BS.[ID]), 7)
  from [dbo].[BriefSections] BS
 WHERE BS.[ID] = @ID

EXEC dbo.ky_ReorderBriefSections @BriefID

GO

Create Procedure
       [dbo].[ky_MoveBriefDocumentFromSection]
      (@RecID Integer,
       @Direction Integer)
AS

/**************************************************************
 *                                                            *
 * ky_MoveBriefDocumentFromSection                            *
 *                                                            *
 * Moves a document up or down to another section of a brief  *
 *                                                            *
 * Parameters: RecID, the recordid column of the document     *
 *             Direction, the direction to move               *
 *                        1 = Down                            *
 *                        2 = Up                              *
 *                                                            *
 **************************************************************/

Declare @SecSeq Int
Declare @BriefID Int
Declare @SortOrder Int

select @BriefID = BS.BriefID,
       @SecSeq = BS.Sequence
  from [dbo].[briefdocuments] BD
       inner join [dbo].[BriefSections] BS
               on BS.[ID] = BD.[SectionID]
 where BD.[recordid] = @RecID

select @SecSeq = IsNull(BS2.[ID], BS1.[ID]),
       @SortOrder = CASE WHEN BS2.ID IS NULL
                              THEN BD.SortOrder
                         WHEN @Direction = 1
                              THEN 999999
                         ELSE 0 END
  from [dbo].[briefsections] BS1
       INNER JOIN [dbo].[BriefDocuments] BD
               ON BD.recordid = @RecID
       left outer join [dbo].[briefsections] BS2
                    on BS2.[BriefID] = BS1.[BriefID]
                   and BS2.[Sequence] = BS1.[Sequence]
                                      + CASE WHEN @Direction = 1
                                                  THEN -1
                                             ELSE 1 END
 where BS1.[BriefID] = @BriefID
   and BS1.[Sequence] = @SecSeq

UPDATE BD
   SET BD.SortOrder = @SortOrder,
       BD.SectionID = @SecSeq
  FROM [dbo].[BriefDocuments] BD
 WHERE BD.[recordid] = @RecID

EXEC dbo.ky_ReorderBriefDocSection @RecID

GO

Create Procedure
       [dbo].[ky_MoveBriefDocumentToSection]
      (@RecID Integer,
       @Section Integer)
AS

/**************************************************************
 *                                                            *
 * ky_MoveBriefDocumentToSection                              *
 *                                                            *
 * Moves a document to another section of a brief             *
 *                                                            *
 * Parameters: RecID, the recordid column of the document     *
 *             Section, the section to which to move the doc  *
 *                                                            *
 **************************************************************/
Declare @OLDSection int

SELECT @OLDSection = BD.SectionID
  FROM [dbo].BriefDocuments BD
 WHERE BD.[recordid] = @RecID

UPDATE BD
   SET BD.SortOrder = 0,
       BD.SectionID = @Section
  FROM [dbo].[BriefDocuments] BD
 WHERE BD.[recordid] = @RecID

EXEC dbo.ky_ReorderBriefDocSection @OLDSection

EXEC dbo.ky_ReorderBriefDocSection @RecID

GO

Create Procedure
       [dbo].[ky_MoveBriefSection]
      (@RecID Integer,
       @Direction Integer)
AS

/**************************************************************
 *                                                            *
 * ky_MoveBriefSection                                        *
 *                                                            *
 * Moves a section up or down within a brief                  *
 *                                                            *
 * Parameters: RecID, the ID column of the section            *
 *             Direction, the direction to move               *
 *                        1 = Down                            *
 *                        2 = Up                              *
 *                        3 = Beginning                       *
 *                        4 = End                             *
 *                                                            *
 **************************************************************/

DECLARE @BriefID int
DECLARE @CoverPage int
DECLARE @MinSeq int
DECLARE @MaxSeq int
DECLARE @CurSeq int

SELECT @BriefID = [BriefID],
       @CoverPage = [CoverPage]
  FROM [dbo].[BriefSections]
 WHERE [ID] = @RecID

IF @BriefID IS NULL
   BEGIN
      RETURN
   END

/* To make sure we don't get any nasty surprises, we first
   ensure that the sections are neatly numbered 1, 2, 3, 4... */
EXEC dbo.ky_ReorderBriefSections @BriefID

  SELECT @MinSeq = Min(BS2.[Sequence]),
         @MaxSeq = Max(BS2.[Sequence]),
         @CurSeq = BS1.[Sequence]
    FROM [dbo].[BriefSections] BS1
         INNER JOIN [dbo].[BriefSections] BS2
                 ON BS2.[BriefID] = @BriefID
                AND BS2.[CoverPage] = @CoverPage
   WHERE BS1.[ID] = @RecID
GROUP BY BS1.[Sequence]

IF @Direction = 1
   IF @CurSeq < @MaxSeq
      BEGIN
         UPDATE [dbo].[BriefSections]
            SET [Sequence] = 0
          WHERE [BriefID] = @BriefID
            AND [Sequence] = @CurSeq

         UPDATE [dbo].[BriefSections]
            SET [Sequence] = @CurSeq
          WHERE [BriefID] = @BriefID
            AND [Sequence] = @CurSeq + 1

         UPDATE [dbo].[BriefSections]
            SET [Sequence] = @CurSeq + 1
          WHERE [BriefID] = @BriefID
            AND [Sequence] = 0
      END

IF @Direction = 2
   IF @CurSeq > @MinSeq
      BEGIN
         UPDATE [dbo].[BriefSections]
            SET [Sequence] = 0
          WHERE [BriefID] = @BriefID
            AND [Sequence] = @CurSeq

         UPDATE [dbo].[BriefSections]
            SET [Sequence] = @CurSeq
          WHERE [BriefID] = @BriefID
            AND [Sequence] = @CurSeq - 1

         UPDATE [dbo].[BriefSections]
            SET [Sequence] = @CurSeq - 1
          WHERE [BriefID] = @BriefID
            AND [Sequence] = 0
      END

IF @Direction = 3
   IF @CurSeq > @MinSeq
      BEGIN
         UPDATE [dbo].[BriefSections]
            SET [Sequence] = 0
          WHERE [BriefID] = @BriefID
            AND [Sequence] = @CurSeq

      END

IF @Direction = 4
   IF @CurSeq < @MaxSeq
      BEGIN
         UPDATE [dbo].[BriefSections]
            SET [Sequence] = @MaxSeq + 1
          WHERE [BriefID] = @BriefID
            AND [Sequence] = @CurSeq

      END

EXEC dbo.ky_ReorderBriefSections @BriefID

GO

Create Procedure
       [dbo].[ky_MoveBriefDocument]
      (@RecID Integer,
       @Direction Integer)
AS

/**************************************************************
 *                                                            *
 * ky_MoveBriefDocument                                       *
 *                                                            *
 * Moves a document up or down within a section of a brief    *
 *                                                            *
 * Parameters: RecID, the recordid column of the document     *
 *             Direction, the direction to move               *
 *                        1 = Down                            *
 *                        2 = Up                              *
 *                        3 = Beginning                       *
 *                        4 = End                             *
 *                                                            *
 **************************************************************/

DECLARE @FirstRecID Integer
DECLARE @FirstSO Integer

DECLARE @PreviousRecID Integer
DECLARE @PreviousSO Integer

DECLARE @NextRecID Integer
DECLARE @NextSO Integer

DECLARE @LastRecID Integer
DECLARE @LastSO Integer

DECLARE @CurRecID Integer
DECLARE @CurSO Integer

EXEC dbo.ky_ReorderBriefDocSection @RecID

SELECT @CurRecID = @RecID,
       @CurSO = X.SortOrder,
       @FirstRecID = BD6.recordid,
       @FirstSO = BD6.SortOrder,
       @PreviousRecID = BD4.recordid,
       @PreviousSO = BD4.SortOrder,
       @NextRecID = BD5.recordid,
       @NextSO = BD5.SortOrder,
       @LastRecID = BD7.recordid,
       @LastSO = BD7.SortOrder
  FROM (  select BD.BriefID,
                 BD.SectionID,
                 BD.sortorder,
                 min(BD2.sortorder) As FirstSO,
                 max(BD2.sortorder) As PreviousSO,
                 min(BD3.sortorder) As NextSO,
                 max(BD3.sortorder) As LastSO
            from dbo.BriefDocuments BD
                 left outer join BriefDocuments BD2
                         ON BD2.briefID = BD.briefid
                        AND BD2.sectionID = BD.sectionID
                        AND BD2.sortorder < BD.sortorder
                 left outer join BriefDocuments BD3
                         ON BD3.briefID = BD.briefid
                        AND BD3.sectionID = BD.sectionID
                        AND BD3.sortorder > BD.sortorder
           Where BD.recordid = @RecID
        group by BD.BriefID,
                 BD.SectionID,
                 BD.sortorder) X
       LEFT OUTER JOIN BriefDocuments BD6
               ON BD6.BriefID = X.BriefID
              AND BD6.SectionID = X.SectionID
              AND BD6.sortorder = X.FirstSO
       LEFT OUTER JOIN BriefDocuments BD4
               ON BD4.BriefID = X.BriefID
              AND BD4.SectionID = X.SectionID
              AND BD4.sortorder = X.PreviousSO
       LEFT OUTER JOIN BriefDocuments BD5
               ON BD5.BriefID = X.BriefID
              AND BD5.SectionID = X.SectionID
              AND BD5.sortorder = X.NextSO
       LEFT OUTER JOIN BriefDocuments BD7
               ON BD7.BriefID = X.BriefID
              AND BD7.SectionID = X.SectionID
              AND BD7.sortorder = X.LastSO

IF @Direction = 1
   IF @PreviousRecID Is Not NULL
     BEGIN
        UPDATE BriefDocuments
           SET sortorder = @PreviousSO
         WHERE recordid = @CurRecID
  
        UPDATE BriefDocuments
           SET sortorder = @CurSO
         WHERE recordid = @PreviousRecID
  
     END
   ELSE
     EXEC dbo.ky_MoveBriefDocumentFromSection @RecID, @Direction

IF @Direction = 2
   IF @NextRecID Is Not NULL
     BEGIN
        UPDATE BriefDocuments
           SET sortorder = @NextSO
         WHERE recordid = @CurRecID
  
        UPDATE BriefDocuments
           SET sortorder = @CurSO
         WHERE recordid = @NextRecID
  
     END
   ELSE
     EXEC dbo.ky_MoveBriefDocumentFromSection @RecID, @Direction

IF @Direction = 3
   IF @FirstRecID Is Not NULL
      IF @FirstRecID <> @CurRecID
         BEGIN
            UPDATE BriefDocuments
               SET sortorder = @FirstSO - 1
             WHERE recordid = @CurRecID
  
            EXEC dbo.ky_ReorderBriefDocSection @RecID
         END


IF @Direction = 4
   IF @LastRecID Is Not NULL
      IF @LastRecID <> @CurRecID
         BEGIN
            UPDATE BriefDocuments
               SET sortorder = @LastSO + 1
             WHERE recordid = @CurRecID
  
            EXEC dbo.ky_ReorderBriefDocSection @RecID
         END


GO


Create Procedure
       [dbo].[ky_ReorderAllBriefDocuments]
      (@BriefID int)
AS
BEGIN

   DECLARE @recordID INT
   DECLARE @sectionID INT
   DECLARE @LastSectionID INT
   DECLARE @sortOrder INT

   DECLARE DOCLISTCSR
   CURSOR 
   FOR
     select BD.recordID, BD.sectionID
       from [dbo].[BriefSections] BS
            inner join [dbo].[BriefDocuments] BD
                    on BD.[BriefId] = BS.[BriefId]
                   and BD.[SectionID] = BS.[Id]
      where BS.[BriefId] = @BriefID
   order by 1 - BS.CoverPage,
            BS.Sequence,
            BD.FileDate

   SET @LastSectionID = 0

   OPEN DOCLISTCSR

   FETCH NEXT
    FROM DOCLISTCSR
    INTO @recordID,
         @sectionID

   WHILE @@FETCH_STATUS = 0
      BEGIN
         IF @LastSectionID <> @sectionID
            BEGIN
               SET @LastSectionID = @sectionID
               SET @sortOrder = 0
            END

         set @sortOrder = @sortOrder + 1

         EXEC('update [dbo].[BriefDocuments] set SortOrder = ' + @SortOrder + ' where recordID = ' + @recordID)

         FETCH NEXT
          FROM DOCLISTCSR
          INTO @recordID,
               @sectionID
      END

   CLOSE DOCLISTCSR
   DEALLOCATE DOCLISTCSR
END
GO