CREATE PROCEDURE dbo.[Forum_Subscriptions_ModPostToModerate]
    @PostID INT,
    @EmailQueueID INT
AS
    DECLARE @ThreadID INT,
        @ForumID INT,
        @GroupID INT,
        @PortalID INT,
        @ModuleID INT,
        @PermissionID INT
    SET @ThreadID = ( SELECT    ThreadID
                      FROM      dbo.Forum_Posts
                      WHERE     PostID = @PostID
                    )
    SET @ForumID = ( SELECT ForumID
                     FROM   dbo.Forum_Threads
                     WHERE  ThreadID = @ThreadID
                   )
    SET @GroupID = ( SELECT GroupID
                     FROM   dbo.Forum_Forums
                     WHERE  ForumID = @ForumID
                   )
    SET @PortalID = ( SELECT    PortalID
                      FROM      dbo.Forum_Groups
                      WHERE     GroupID = @GroupID
                    )
    SET @ModuleID = ( SELECT    ModuleID
                      FROM      dbo.Forum_Groups
                      WHERE     GroupID = @GroupID
                    )
    SET @PermissionID = ( SELECT    PermissionID
                          FROM      dbo.Forum_Permission
                          WHERE     PermissionKey = 'MODERATE'
                                    AND PermissionCode = 'MODERATE'
                        )
    -- Create a temp table to store the select results
    CREATE TABLE #EmailIndex
        (
          IndexID INT IDENTITY(1, 1)
                      NOT NULL,
          Email NVARCHAR(100) COLLATE database_default,
          EmailFormat INT,
          DisplayName NVARCHAR(100) COLLATE database_default
        )
    INSERT  INTO #EmailIndex
            (
              Email,
              EmailFormat,
              DisplayName
            )
            ( SELECT DISTINCT
                        ( Email ),
                        EmailFormat,
                        DisplayName
              FROM      dbo.vw_Users U
                        INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                        INNER JOIN dbo.UserRoles UR ON FU.UserID = UR.UserID
                        INNER JOIN dbo.Forum_ForumPermission FP ON UR.RoleID = FP.RoleID
              WHERE     FP.ForumID = @ForumID
                        AND FU.EnableModNotification = 1
                        AND FP.PermissionID = @PermissionID
                        AND FU.PortalID = @PortalID
                        AND ( ExpiryDate > GETDATE()
                              OR ExpiryDate IS NULL
                            )
                        AND ( EffectiveDate < GETDATE()
                              OR EffectiveDate IS NULL
                            )
                        AND IsDeleted = 0
                        AND Authorised = 1
            )
    -- Get individual users assigned perms at forum level
    INSERT  INTO #EmailIndex
            (
              Email,
              EmailFormat,
              DisplayName
            )
            ( SELECT DISTINCT
                        ( Email ),
                        EmailFormat,
                        DisplayName
              FROM      dbo.vw_Users U
                        INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                        INNER JOIN dbo.Forum_ForumPermission FP ON FU.UserID = FP.UserID
              WHERE     FP.ForumID = @ForumID
                        AND FU.EnableModNotification = 1
                        AND FP.PermissionID = @PermissionID
                        AND FU.PortalID = @PortalID
                        AND IsDeleted = 0
                        AND Authorised = 1
            )
    INSERT  INTO #EmailIndex
            (
              Email,
              EmailFormat,
              DisplayName
            )
            ( SELECT DISTINCT
                        ( Email ),
                        EmailFormat,
                        DisplayName
              FROM      dbo.vw_Users U
                        INNER JOIN dbo.Forum_Users FU ON U.UserID = FU.UserID
                        INNER JOIN dbo.UserRoles UR ON FU.UserID = UR.UserID
                        INNER JOIN dbo.ModulePermission MP ON UR.RoleID = MP.RoleID
                        INNER JOIN dbo.Permission P ON MP.PermissionID = P.PermissionID
              WHERE     MP.ModuleID = @ModuleID
                        AND FU.EnableModNotification = 1
                        AND ( P.PermissionCode = 'FORUMGLBMOD'
                              OR P.PermissionCode = 'FORUMADMIN'
                            )
                        AND FU.PortalID = @PortalID
                        AND ( ExpiryDate > GETDATE()
                              OR ExpiryDate IS NULL
                            )
                        AND ( EffectiveDate < GETDATE()
                              OR EffectiveDate IS NULL
                            )
                        AND IsDeleted = 0
                        AND Authorised = 1
            )
    IF @EmailQueueID > 0
        BEGIN
            INSERT  INTO dbo.Forum_EmailQueue_TaskEmails
                    (
                      EmailAddress,
                      EmailQueueID,
                      IsHTML,
                      DisplayName,
                      DateAdded
                    )
                    ( SELECT DISTINCT
                                ( Email ),
                                @EmailQueueID,
                                EmailFormat,
                                DisplayName,
                                GETDATE()
                      FROM      #EmailIndex
                      WHERE     Email NOT IN (
                                SELECT  Email
                                FROM    dbo.Forum_EmailQueue_TaskEmails
                                WHERE   EmailQueueID = @EmailQueueID )
                    )
        END
    ELSE
        BEGIN
            SELECT DISTINCT
                    ( Email ),
                    EmailFormat,
                    DisplayName
            FROM    #EmailIndex
        END

GO