/*************************************************************************************************************
 *
 * Clear out previous versions of the procedures that will be created in this script.
 *
 *************************************************************************************************************/
IF EXISTS(SELECT *
            FROM sysobjects SO
           WHERE SO.[name] = 'ky_WDAddBriefDocuments'
             AND SO.[xtype] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[ky_WDAddBriefDocuments]
END
GO

IF EXISTS(SELECT *
            FROM sysobjects SO
           WHERE SO.[name] = 'ky_WDRemoveBriefDocuments'
             AND SO.[xtype] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[ky_WDRemoveBriefDocuments]
END
GO

IF EXISTS(SELECT *
            FROM sysobjects SO
           WHERE SO.[name] = 'ky_WDMoveBriefDocuments'
             AND SO.[xtype] = 'P')
BEGIN
  DROP PROCEDURE [dbo].[ky_WDMoveBriefDocuments]
END
GO

IF EXISTS(SELECT *
            FROM sysobjects SO
           WHERE SO.[name] = 'ky_WDBriefSectionList'
             AND SO.[xtype] = 'TF')
BEGIN
  DROP FUNCTION [dbo].[ky_WDBriefSectionList]
END
GO

IF EXISTS(SELECT *
            FROM sysobjects SO
           WHERE SO.[name] = 'ky_WDBriefReorderSections'
             AND SO.[xtype] = 'TF')
BEGIN
  DROP FUNCTION [dbo].[ky_WDBriefReorderSections]
END
GO

/*************************************************************************************************************
 *
 * (Re)Create the new procedures for the WinDev Brief functionality
 *
 *************************************************************************************************************/
GO

create function
       [dbo].[ky_WDBriefReorderSections]
      (@BriefID int)
RETURNS @TableInOrder TABLE
       (sectionid int,
        coverpage int,
        newsortorder int,
        newkhsorter varchar(12))
AS
/*******************************************************************************

      WinDEV function to assist in re-ordering a brief.
 
 *******************************************************************************/
  BEGIN
  
  DECLARE @newsortorder int

  SET @newsortorder = 0

    INSERT
      INTO @TableInOrder
          (sectionid,
           coverpage,
           newsortorder,
           newkhsorter)
    SELECT BRS.[ID],
           BRS.[coverpage],
           0,
           ''
      from [dbo].[BriefSections] BRS
     WHERE BRS.[BriefID] = @BriefID
  ORDER BY BRS.[coverpage] DESC,
           BRS.[sequence] ASC,
           BRS.[ID] ASC

  UPDATE BRD
     SET @newsortorder = @newsortorder + 1,
         BRD.[newsortorder] = @newsortorder,
         BRD.[newkhsorter] = convert(varchar(1), 1 - BRD.[coverpage])
                           + right(convert(varchar(5), 10000 + @newsortorder), 4)
                           + right(convert(varchar(8), 10000000 + BRD.[sectionid]), 7)
    FROM @TableInOrder BRD

  RETURN
  END


GO

/*************************************************************************************************************
 *
 * ky_WDBriefSectionList - creates the top level "treeview" for the Brief
 *
 *************************************************************************************************************/
Create Function 
       [dbo].[ky_WDBriefSectionList]
      (@CurrBrief     int)
RETURNS @SECTIONS TABLE
       ([BriefID]                 int,
        [BriefName]               varchar(200),
        [BriefSectionID]          int,
        [BriefSectionName]        varchar(100),
        [BriefSectionSequence]    int,
        [BriefSectionCoverPage]   int)
AS
BEGIN
  INSERT
    INTO @SECTIONS
        ([BriefID],
         [BriefName],
         [BriefSectionID],
         [BriefSectionName],
         [BriefSectionSequence],
         [BriefSectionCoverPage])
  SELECT BRI.[ID] AS [BriefID],
         BRI.[Title] AS [BriefName],
         BRS.[ID] AS [BriefSectionID],
         RTRIM(ISNULL(BRS.[Section], '')) AS [BriefSectionName],
         BRS.[Sequence] AS [BriefSectionSequence],
         convert(int, BRS.[CoverPage]) AS [BriefSectionCoverPage]
    FROM [dbo].[Brief] BRI
         INNER JOIN [dbo].[BriefSections] BRS
      ON BRS.[BriefID] = BRI.[ID]
   WHERE BRI.[ID] = @CurrBrief
   
  INSERT
    INTO @SECTIONS
        ([BriefID],
         [BriefName],
         [BriefSectionID],
         [BriefSectionName],
         [BriefSectionSequence],
         [BriefSectionCoverPage])
  SELECT BRI.[ID] AS [BriefID],
         BRI.[Title] AS [BriefName],
         0 AS [BriefSectionID],
         'Not included in this Brief' AS [BriefSectionName],
         999999 AS [BriefSectionSequence],
         2 AS [BriefSectionCoverPage]
    FROM [dbo].[Brief] BRI
   WHERE BRI.[ID] = @CurrBrief
   
RETURN

END
GO

/*************************************************************************************************************
 *
 * ky_WDMoveBriefDocuments - Move documents from one Brief section to the other
 *
 *************************************************************************************************************/
create procedure
       [dbo].[ky_WDMoveBriefDocuments]
      (@FromSection           int,
       @ToSection             int,
       @doclist               varchar(max))
AS
BEGIN
  DECLARE @idoc int
  DECLARE @continue int

  DECLARE @SO TABLE
         ([SO]         int     identity(1, 1),
          [recordid]   int)
  
  DECLARE @OSO TABLE
         ([SO]         int     identity(1, 1),
          [recordid]   int)
  
  BEGIN TRANSACTION
  
  SET @continue = 0
  
  BEGIN TRY
    BEGIN
      EXEC sp_xml_preparedocument @idoc OUTPUT, @doclist 

        INSERT
          INTO @SO
              ([recordid])
        SELECT BRD.[recordid]
          FROM [dbo].[BriefDocuments] BRD
         WHERE BRD.[SectionID] = @ToSection
      ORDER BY BRD.[SortOrder]
      
      IF (@@ERROR <> 0)
        SET @continue = 1

      IF (@continue = 0)
        BEGIN
          INSERT
            INTO @SO
                ([recordid])
          SELECT BRD.[recordid]
            from OPENXML(@idoc, 'docs/doc', 2)
                    WITH([recordid] int '.') DCS
                 INNER JOIN [dbo].[BriefDocuments] BRD
                         ON BRD.[recordId] = DCS.[recordid]
                        AND BRD.[SectionID] = @FromSection
                 INNER JOIN [dbo].[BriefSections] BRS
                         ON BRS.[ID] = BRD.[SectionID]
                 INNER JOIN [dbo].[BriefSections] SSS
                         ON SSS.[BriefID] = BRS.[BriefID]
                        AND SSS.[ID] = @ToSection
        END

      IF (@@ERROR <> 0)
        SET @continue = 1

      IF (@continue = 0)
        BEGIN
          UPDATE BRD
             SET BRD.[SortOrder] = SO.[SO],
                 BRD.[SectionID] = @ToSection
            FROM @SO SO
                 INNER JOIN [dbo].[BriefDocuments] BRD
                         ON BRD.[recordId] = SO.[recordid]
        END

      IF (@@ERROR <> 0)
        SET @continue = 1

      IF (@continue = 0)
        BEGIN
            INSERT
              INTO @OSO
                  ([recordid])
            SELECT BRD.[recordId]
              FROM [dbo].[BriefDocuments] BRD
             WHERE BRD.[SectionID] = @FromSection
          ORDER BY BRD.[SortOrder]
        END

      IF (@@ERROR <> 0)
        SET @continue = 1

      IF (@continue = 0)
        BEGIN
          UPDATE BRD
             SET BRD.[SortOrder] = SO.[SO]
            FROM @OSO SO
                 INNER JOIN [dbo].[BriefDocuments] BRD
                         ON BRD.[recordId] = SO.[recordid]
        END

      EXEC sp_xml_removedocument @idoc
    END
  END TRY
  BEGIN CATCH
    BEGIN
      set @continue = 1
    END
  END CATCH
  
  IF (@continue = 0)
    BEGIN
      COMMIT
    END
  ELSE
    BEGIN
      ROLLBACK
    END
  
END
GO

/*************************************************************************************************************
 *
 * ky_WDRemoveBriefDocuments - Removes documents from a brief section.
 *
 *************************************************************************************************************/
create procedure
       [dbo].[ky_WDRemoveBriefDocuments]
      (@FromSection           int,
       @doclist               varchar(max))
AS
BEGIN
  DECLARE @idoc int
  DECLARE @continue int

  DECLARE @SO TABLE
         ([SO]         int     identity(1, 1),
          [recordid]   int)
  
  DECLARE @OSO TABLE
         ([SO]         int     identity(1, 1),
          [recordid]   int)
  
  BEGIN TRANSACTION
  
  SET @continue = 0
  
  BEGIN TRY
    BEGIN
      EXEC sp_xml_preparedocument @idoc OUTPUT, @doclist 

      IF (@continue = 0)
        BEGIN
          DELETE BRD
            from OPENXML(@idoc, 'docs/doc', 2)
                    WITH([recordid] int '.') DCS
                 INNER JOIN [dbo].[BriefDocuments] BRD
                         ON BRD.[recordId] = DCS.[recordid]
                        AND BRD.[SectionID] = @FromSection
                 INNER JOIN [dbo].[BriefSections] BRS
                         ON BRS.[ID] = BRD.[SectionID]
        END

      IF (@@ERROR <> 0)
        SET @continue = 1

      IF (@continue = 0)
        BEGIN
            INSERT
              INTO @OSO
                  ([recordid])
            SELECT BRD.[recordId]
              FROM [dbo].[BriefDocuments] BRD
             WHERE BRD.[SectionID] = @FromSection
          ORDER BY BRD.[SortOrder]
        END

      IF (@@ERROR <> 0)
        SET @continue = 1

      IF (@continue = 0)
        BEGIN
          UPDATE BRD
             SET BRD.[SortOrder] = SO.[SO]
            FROM @OSO SO
                 INNER JOIN [dbo].[BriefDocuments] BRD
                         ON BRD.[recordId] = SO.[recordid]
        END

      EXEC sp_xml_removedocument @idoc
    END
  END TRY
  BEGIN CATCH
    BEGIN
      set @continue = 1
    END
  END CATCH
  
  IF (@continue = 0)
    BEGIN
      COMMIT
    END
  ELSE
    BEGIN
      ROLLBACK
    END
  
END
GO

/*************************************************************************************************************
 *
 * ky_WDAddBriefDocuments - Adds new documents to a brief section.
 *
 *************************************************************************************************************/
Create Procedure
       [dbo].[ky_WDAddBriefDocuments]
      (@SectionID   int,
       @doclist     varchar(max))
AS

DECLARE @BriefID int
DECLARE @idoc int
DECLARE @MaxSO int
DECLARE @continue int

DECLARE @SO TABLE
       ([SO]                  int     identity(1, 1),
        [NEWSO]               int,
        [TrackReference]      int)

SELECT @MaxSO = ISNULL(Max(BRD.[SortOrder]), 0)
  FROM [dbo].[BriefDocuments] BRD
 WHERE BRD.SectionID = @SectionID

BEGIN TRANSACTION

EXEC sp_xml_preparedocument @idoc OUTPUT, @doclist 

SET @continue = 0

BEGIN TRY
  BEGIN
    IF (@continue = 0)
      BEGIN
        SELECT @BriefID = BS.BriefID
          FROM [dbo].[BriefSections] BS
         WHERE BS.[ID] = @SectionID
 
        IF (@@Error <> 0)
          SET @continue = 1
          
      END
      
    IF (@continue = 0)
      BEGIN
        INSERT
          INTO @SO
              ([TrackReference])
        SELECT DCS.[trackreference]
          from OPENXML(@idoc, 'docs/tr', 2)
                  WITH([trackreference] int '.') DCS

        IF (@@Error <> 0)
          SET @continue = 1
          
      END

    IF (@continue = 0)
      BEGIN
        UPDATE SO
           SET [NEWSO] = @MaxSO + SO.[SO]
          FROM @SO SO

        IF (@@Error <> 0)
          SET @continue = 1
          
      END

    IF (@continue = 0)
      BEGIN
        INSERT
          INTO [dbo].[BriefDocuments]
              ([BriefID],
               [DocumentName],
               [TrackReference],
               [FileLocation],
               [FileDate],
               [DocumentType],
               [DocumentClass],
               [SortOrder],
               [SectionID])
        select @BriefID As BriefID,
               CASE WHEN DA.[Type] = 'MSG'
                         THEN convert(char(255),
                                      SubString(  'E-mail from '
                                                + CASE WHEN RTRIM(LTRIM(IsNull(DIA.[EMAILADDRESS], '(undisclosed)'))) = ''
                                                            THEN '(undisclosed)'
                                                       ELSE RTRIM(LTRIM(IsNull(DIA.[EMAILADDRESS], '(undisclosed)'))) END
                                                + ' to '
                                                + CASE WHEN RTRIM(LTRIM(IsNull(SubString(DIA.[ADDRESSTO], 1, 255), '(undisclosed)'))) = ''
                                                            THEN '(undisclosed)'
                                                       ELSE RTRIM(LTRIM(IsNull(SubString(DIA.[ADDRESSTO], 1, 255), '(undisclosed)'))) END
                                                + ': '
                                                + CASE WHEN RTRIM(LTRIM(IsNull(SubString(DIA.[SUBJECT], 1, 255), '(no subject)'))) = ''
                                                            THEN '(no subject)'
                                                       ELSE RTRIM(LTRIM(IsNull(SubString(DIA.[SUBJECT], 1, 255), '(no subject)'))) END,
                                                1,
                                                255))
                      ELSE DA.[Document] END,
               DA.[TrackReference] As [TrackReference],
               RTRIM(IsNull(DA.[FilePath], '')) As [FileLocation],
               DA.[LastAccessDate] As [FileDate],
               DA.[Type] As [DocumentType],
               RTRIM(ISNULL(DCL.[CLASSDESCRIPTION], '')) As [DocumentClass],
               SO.[NEWSO],
               @SectionID AS SectionID
          from @SO SO
               INNER JOIN [dbo].[Brief] BRI
                       ON BRI.[ID] = @BriefID
               INNER JOIN [dbo].[DiaryAttachments] DA
                          INNER JOIN [dbo].[Diary] DIA
                                  ON DIA.[ActionID] = DA.[DiaryID]
                          LEFT OUTER JOIN [dbo].[DocumentClasses] DCL
                                       ON DCL.[CLASSCODE] = DA.[DOCCLASS]
                       ON DA.[TrackReference] = SO.[TrackReference]
               LEFT OUTER JOIN [dbo].[BriefDocuments] BDN
                            ON BDN.[BriefId] = @BriefID
                           AND BDN.[TrackReference] = SO.[TrackReference]
         WHERE BDN.[TrackReference] is null
           AND DIA.[CASECODE] = BRI.[MatterCode]

        IF (@@Error <> 0)
          SET @continue = 1
          
      END

  END 
END TRY
BEGIN CATCH
  BEGIN
    SET @continue = 1
  END
END CATCH

IF (@continue = 0)
  BEGIN
    COMMIT
  END
ELSE
  BEGIN
    ROLLBACK
  END
  
EXEC sp_xml_removedocument @idoc

GO