CS2007 and Email Subscriptions

Could someone acknowledge that this is a problem, or if it is not considered one?

Thank you for following up on this.

hi,

in the meanwhile, is there a workaround to this issue? our company is using cs2007.1 and recently purchased the mail gateway license.

Which issue are you referring to?  There were two different ones. 

Both please, thanks!

The issue with emailing banned users has already been fixed and can be corrected with just a stored procedure update.

The other issue hasn't been resolved yet and would require a code change, so there is no quick workaround.

For the banned users, just run this SQL script:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Emails_TrackingThread]&#39 and OBJECTPROPERTY(id, N'IsProcedure&#39 = 1)
drop procedure [dbo].[cs_Emails_TrackingThread]
GO



CREATE PROCEDURE [dbo].cs_Emails_TrackingThread
(
    @PostID INT,
    @SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @SectionID INT
DECLARE @UserID INT
DECLARE @PostLevel INT
DECLARE @ThreadID INT

-- First get the post info
SELECT
    @SectionID = SectionID,
    @UserID = UserID,
    @PostLevel = PostLevel,
    @ThreadID = ThreadID
FROM
    cs_Posts (nolock)
WHERE
    PostID = @PostID and SettingsID = @SettingsID


-- Check if this is a PM message
IF (@SectionID = 0)
BEGIN
    
    -- we have to bind to the PM users for this ThreadID
    SELECT
        U.UserID,
        U.Email,
        U.EnableHtmlEmail,
        U.[PublicToken],
        U.[UserName]
    FROM
        cs_TrackedThreads T
        JOIN cs_vw_Users_FullUser U (nolock) ON U.UserID = T.UserID
                JOIN cs_PrivateMessages PM ON PM.UserID = T.UserID AND PM.ThreadID = @ThreadID
    WHERE
        T.ThreadID = @ThreadID and T.SettingsID = @SettingsID and U.SettingsID = @SettingsID and PM.SettingsID = @SettingsID and
        U.EnableEmail = 1 and
        U.UserAccountStatus = 1 and
        U.EnableThreadTracking = 1

END
ELSE BEGIN

    SELECT
        U.UserID,
        U.Email,
        U.EnableHtmlEmail,
        U.[PublicToken],
        U.[UserName]
    FROM
        cs_TrackedThreads T
        JOIN cs_vw_Users_FullUser U (nolock) ON U.UserID = T.UserID            
    WHERE
        T.ThreadID = @ThreadID and T.SettingsID = @SettingsID and U.SettingsID = @SettingsID and
        U.EnableEmail = 1 and
        U.UserAccountStatus = 1 and
        U.EnableThreadTracking = 1
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

grant execute on [dbo].cs_Emails_TrackingThread to public
go

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Emails_TrackingForum]&#39 and OBJECTPROPERTY(id, N'IsProcedure&#39 = 1)
drop procedure [dbo].[cs_Emails_TrackingForum]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Emails_TrackingSection]&#39 and OBJECTPROPERTY(id, N'IsProcedure&#39 = 1)
drop procedure [dbo].[cs_Emails_TrackingSection]
GO


CREATE PROCEDURE [dbo].cs_Emails_TrackingSection
(
    @PostID    INT,
    @SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
DECLARE @SectionID INT
DECLARE @UserID INT
DECLARE @PostLevel INT
DECLARE @ThreadID INT

-- First get the post info
SELECT
    @SectionID = SectionID,
    @UserID = UserID,
    @PostLevel = PostLevel,
    @ThreadID = ThreadID
FROM
    cs_Posts (nolock)
WHERE
    PostID = @PostID and SettingsID = @SettingsID

-- Check if its a new thread or not
IF (@PostLevel = 1)
BEGIN
    -- this is a new thread (1 & 2)
   
    -- Check if this is a PM message
    IF (@SectionID = 0)
    BEGIN
       
        -- we have to bind to the PM users for this ThreadID
        SELECT
            U.UserID,
            U.Email,
            U.EnableHtmlEmail,
            F.SubscriptionType
        FROM
            cs_TrackedSections F
            JOIN cs_vw_Users_FullUser U (nolock) ON U.UserID = F.UserID
            JOIN cs_PrivateMessages PM ON PM.UserID = F.UserID AND PM.ThreadID = @ThreadID
        WHERE
            F.SectionID IN (-1, 0) AND F.SettingsID = @SettingsID and U.SettingsID = @SettingsID and
            U.EnableThreadTracking = 1 and
            U.EnableEmail = 1 and
            U.UserAccountStatus = 1 and
            F.SubscriptionType & 3 <> 0
    END
    ELSE BEGIN

        SELECT
            U.UserID,
            U.Email,
            U.EnableHtmlEmail,
            F.SubscriptionType
        FROM
            cs_TrackedSections F
            JOIN cs_vw_Users_FullUser U (nolock) ON U.UserID = F.UserID
        WHERE
            F.SectionID = @SectionID AND F.SettingsID = @SettingsID and U.SettingsID = @SettingsID and
            U.EnableThreadTracking = 1 and
            U.EnableEmail = 1 and
            U.UserAccountStatus = 1 and
            F.SubscriptionType & 3 <> 0
    END
END
ELSE BEGIN
    -- this is a reply to an existing post (2)

    -- Check if this is a PM message
    IF (@SectionID = 0)
    BEGIN
       
        -- we have to bind to the PM users for this ThreadID
        SELECT
            U.UserID,
            U.Email,
            U.EnableHtmlEmail,
            F.SubscriptionType
        FROM
            cs_TrackedSections F
            JOIN cs_vw_Users_FullUser U (nolock) ON U.UserID = F.UserID
            JOIN cs_PrivateMessages PM ON PM.UserID = F.UserID AND PM.ThreadID = @ThreadID
        WHERE
            F.SectionID IN (-1, 0) AND U.SettingsID = @SettingsID and F.SettingsID = @SettingsID and
            U.EnableThreadTracking = 1 and
            U.EnableEmail = 1 and
            U.UserAccountStatus = 1 and
            F.SubscriptionType & 3 = 3

    END
    ELSE BEGIN

        SELECT
            U.UserID,
            U.Email,
            U.EnableHtmlEmail,
            F.SubscriptionType
        FROM
            cs_TrackedSections F
            JOIN cs_vw_Users_FullUser U (nolock) ON U.UserID = F.UserID
        WHERE
            F.SectionID = @SectionID AND U.SettingsID = @SettingsID and F.SettingsID = @SettingsID and
            U.EnableThreadTracking = 1 and
            U.EnableEmail = 1 and
            U.UserAccountStatus = 1 and
            F.SubscriptionType & 3 = 3
    END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

grant execute on [dbo].cs_Emails_TrackingSection to public
go

Great!! Thanks for the prompt response. We will try this out!

Is this correct?  For people who have subscribed to a section, this part at the end means that you will only receive an email if you subscribed to the section AND you have turned on notification for all replies to your own posts -- or am I not reading this correctly? 

WHERE
            F.SectionID = @SectionID AND U.SettingsID = @SettingsID and F.SettingsID = @SettingsID and
            U.EnableThreadTracking = 1 and
            U.EnableEmail = 1 and
            U.UserAccountStatus = 1 and
            F.SubscriptionType & 3 = 3

The label in my profile is:  "Enable Email Notifications of forum/thread subscriptions and replies to my posts. "

Does the "and replies to my posts" part actually do that??  

If so, does this mean I can only use a forum or thread subscription if I also get emails about every reply to every post? (Replies to every post of mine, I mean)

Yes. In CS 2007, that setting also triggered you to be auto-subscribed to every thread you post in. In CS 2008, it doesn't anymore, but the checkbox to be subscribed will always be checked, just not controlled by that option.

Can you toss me a clue where I would fix this in CS2007?  It is really causing me a headache.  My moderators can't subscribe to the reporting forum to get emails when a post is reported.... because they are inundated with emails about EVERY reply to EVERY post they make.  Basically if you are a prolific poster at all, this makes the subscription feature unusuable because you get a huge number of emails. 

Thanks, I really appreciate any help with this.