DROP Procedure [dbo].[ky_GenerateBriefTemplate]
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_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_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