Tuesday, May 21, 2019
 Now reading ...
Feb
23
Mon
Posted By Subodh on Monday, February 23, 2009
10398 Views 10 Comments


Subodh.com Notify

DotNetNuke Notify after a reply to comment is posted I know I have been looking like a totally third class blogger (with DNN Blog) without the capability of notifying commenters when their comments have been replied to. Without the notification in place, nobody knows that you’ve replied to them and you cannot really build a meaningful discussion in the absence of a Notification. Even if people do come back and reply to your comment, the thought chain gets spread over multiple, in-between comments that make no sense to a passer-by. DNN Blog really needs a Notification. Oh well, DNN Blog needs a lot of things to be really up to par. Here’s my quick and dirty solution to add a ‘Notify’ solution to the DNN Blog module.

Be aware though, this solution is not for the faint of heart. You really need to know what you’re doing. As always, make a backup copy of the original Blog_AddComment stored procedure. Make sure you have setup the Database profile to send mails properly. If you need to know how to do this, just let me know in comments – I just think it is a separate thing that has nothing to do with DNN or the Blog module itself so I’ll skip the unnecessary details. The same goes for enabling Regular Expressions on your Database, if you need to know, please comment.

 

So, here’s our requirement. If I want to reply to somebody, I normally use an @Commenter_Name. Doing so should send an email notification to the original commenter (if he left an email address in the first place) along-with providing him an email link to follow-up the discussion. Why would one need this? Because in the social world, a reply-to chain builds a community. People are more likely to respond at the spur of the moment than days later (or worse, when they won’t even remember they asked/commented on something). one caveat though - No email addresses should be exposed

I could look at the DNN Blog code and try adding this. I did try in fact! A few minutes of dealing with the .NET 1.1 stuff, missing files, fail-to-compile mess and I realized I’m too lazy to write a full-fledged solution. I need something quick and dirty.

We start with modifying the Blog_AddComment stored procedure in the DNN database. Just look at the code below (don’t hit Ctrl+E yet).

 

USE [DNN]
GO
/****** Object:  StoredProcedure [dbo].[Blog_AddComment]    Script Date: 02/23/2009 11:00:34 ******/
/* This procedure was modified by Subodh on 2/21/2009; Any subsequent upgrade/update of DNN Blog module will overwrite this procedure
It is advisable that you create a backup copy of this procedure before modifying this stored proc and do a database backup
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
ALTER PROCEDURE [dbo].[Blog_AddComment]
    @EntryID int,    @UserID int,    @Title nvarchar(255),    @Comment ntext,    @Author nvarchar(50),    @Approved bit,    @Website nvarchar(255),    @Email nvarchar(255)
 
AS
 
DECLARE @ID int
INSERT INTO dbo.Blog_Comments (
    [EntryID],    [UserID],    [Title],    [Comment],    [Author],    [Approved],    [AddedDate],    [Website],    [Email]
 
) VALUES (
    @EntryID,    @UserID,    @Title,    @Comment,    @Author,    @Approved,    GetUTCDate(),    @Website,    @Email
)
 
select @ID= SCOPE_IDENTITY() -- Store the @@IDENTITY for later. The Blog module needs this to be returned from this proc
 
 
BEGIN TRY
    declare @body1 varchar(8000), @subject varchar(100), @replyto varchar(100), @replyfrom varchar(100), @theysaid varchar(1000), @yousaid varchar(1000), @theysaidtitle varchar(1000),
        @yousaidtitle varchar(1000),  @replytoMail varchar(100), @replyfromMail varchar(100), @extrainfo varchar(100), @PermaLink varchar(100), @EntryTitle  varchar(100), @PortalID int
    Select  @theysaid=cast(Comment as varchar(max)), @theysaidTitle=Title, @ReplyFromMail=EMail, 
        @EntryID=EntryID, @replyfrom=Author from Blog_Comments Where CommentID=@ID
    select @PortalID=BB.PortalID, @Permalink=be.Permalink, @EntryTitle=be.Title from Blog_Entries be, Blog_Blogs bb
            where be.BlogID=bb.BlogID and be.EntryID=@EntryID
    IF @PortalID=2 —- Change this to restrict to a portal ID you are testing on first.
    BEGIN
        SELECT @replyto=SubmatchValue
            FROM dbo.RegexFind ('@(\w+)',@theysaid,1,1)
            IF @replyto IS NOT NULL
            BEGIN
                select top 1 @ReplyTo=Author, @ReplyToMail=Email,@yousaid=Comment,@yousaidTitle=Title from Blog_Comments where EntryID=@EntryID and Author like @replyTo+'%' and commentid<@id
                    order by commentID desc
                IF @replytoMail IS NOT NULL
                BEGIN
                    SET @body1=dbo.Blog_FormatEmail('notify',@replyto, @replyfrom , @theysaid , 
                                        @yousaid , @theysaidtitle ,
                                        @yousaidtitle ,  @PermaLink , @EntryTitle)
                    SET @subject='Reply to your comment'
 
                    DECLARE @Toaddr varchar(100)
                    Set @toAddr=@replyTo +' <'+@replytoMail+'>; webmaster@yourdomain.com'
 
                    insert into messages([message]) values ('mail sent to '+@ToAddr)
 
                    EXEC msdb.dbo.sp_send_dbmail @recipients=@toAddr,
                        @subject = @subject,
                        @body = @body1 ,
                        @body_format = 'HTML' ;
 
                END
            END
    END
END TRY
BEGIN CATCH 
    insert into messages([message]) values ('Error Occured'+ERROR_MESSAGE())
END CATCH
--End changes
Select @ID
 

 

Couple of things to note here. You would need the Regular Expressions function RegexFind. If you do not have or use one in your database I guess you won’t be able to use the solution above(Not without much heart-burn). If you’re feeling adventurous, grab a copy from Robyn Page and Phil Factor’s TSQL Regex page (you just need the RegexFind, nothing else), give the appropriate rights and allow MSSQL to use OLE. You would also need to write another function to format the message body (sample given below).

USE [DNN]
GO
/****** Object:  UserDefinedFunction [dbo].[Blog_FormatEmail]    Script Date: 02/23/2009 13:02:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Blog_FormatEmail](@messageType varchar(100),    @replyto varchar(100), @replyfrom varchar(100), @theysaid varchar(1000), 
                    @yousaid varchar(1000), @theysaidtitle varchar(1000),
                    @yousaidtitle varchar(1000),  @PermaLink varchar(100), @EntryTitle  varchar(100)
)
returns varchar(8000)
as
 
BEGIN
    DECLARE @body1 varchar(8000)
    if @messageType ='notify'
    BEGIN
 
        set @body1 = '
        
        
              

 '+@replyfrom+' replied to a comment you left at Subodh.com They replied to: '+@yousaidtitle+'

              You 
              wrote '+@yousaidtitle+'
'
        +@yousaid+'
        

'+@theysaidtitle+'


'+
        @theysaid+'

        


You may follow up here '">'+@EntryTitle+'

        '
    END
 
    RETURN @body1
END
;

 

There, you’re all set to send a nice little email when somebody replies to a comment. Watch out of course though, some people may not like it since that’s not what they were told when they gave their email address. You might want to change the line that reads ‘Email (Optional) used for Gravatar only’ to something more appropriate.

To do so, open the ViewEntry.ascx.resx resource file under DesktopModules\Blog\App_Localresources and change the last few lines as in below (or another resource file if you’re using localization):

 

  <data name="lblEmailExplanation.Text" xml:space="preserve">
    <value>(Optional) Email used to show <a href="http://www.gravatar.com" target="_new">Gravatar</a> or send notification upon reply to your comment.value>
  data>

 

While you’re at it, make sure you create a temporary Messages table that serves only error messages/logging purposes. I keep just two fields in it, a Primary Key ID and a varchar(8000) Message field.

Needless to say, this is a clumsy way of getting the notifications done. And I’m too lazy to bother changing the Blog code. Comments and flame welcome.

Categories

  
 You may also be interested in
  
 Comments & Discussions

  • Gravatar
    Monday, February 23, 2009 at 10:41 PM
    Notification on Reply to Comment in DotNetNuke Blog module
    I know I have been looking like a totally third class blogger (with DNN Blog) without the ability of notifying commenters when their comments have been replied to. Without the notification in place, nobody knows that you’ve replied to them and you c...
    # Subodh's Blog

  • Gravatar
    Monday, February 23, 2009 at 10:41 PM
    Notification on Reply to Comment in DotNetNuke Blog module
    I know I have been looking like a totally third class blogger (with DNN Blog) without the ability of notifying commenters when their comments have been replied to. Without the notification in place, nobody knows that you’ve replied to them and you c...
    # Subodh's Blog

  • Gravatar
    Ritesh Tuesday, February 24, 2009 at 12:20 AM
    Re: Notify on reply to Comment in DNN Blog Module
    Take a bow Sir. You seem to have solution for everything. By the way, I would prefer to have a checkbox while adding comment if somebody would like to get notified and have the unsubscribe option in the notification mail

    • Gravatar
      Subodh Tuesday, February 24, 2009 at 12:24 AM
      Re: Notify on reply to Comment in DNN Blog Module
      @Ritesh

      uh, whaa? Ok ok, I know I'll have to do that which basically means fiddle with the Blog code. /happy

      • Gravatar
        Ritzy Monday, March 16, 2009 at 8:35 PM
        Re: Notify on reply to Comment in DNN Blog Module
        You seem to have fixed,rejigged and enhanced so much of the blog module.
        You rock man!!!

        • Gravatar
          Baldwin Saturday, May 30, 2009 at 1:01 AM
          Re: Notify on reply to Comment in DNN Blog Module
          Wow, Great post. Thanks for sharing....

  • Gravatar
    Don Worthley Tuesday, February 24, 2009 at 12:31 AM
    Re: Notify on reply to Comment in DNN Blog Module
    Thanks Subodh,

    I really appreciate you taking the time to write this up!

    Don

    • Gravatar
      Subodh Tuesday, February 24, 2009 at 12:39 AM
      Re: Notify on reply to Comment in DNN Blog Module
      @Don Worthley

      Oh anytime Sir. I didn't realize it was worth writing/any value to others until you said you liked it winking I just presumed the next Blog version will have it.

  • Gravatar
    Acai Burn Thursday, May 21, 2009 at 2:49 AM
    Re: Notify on reply to Comment in DNN Blog Module
    Wow! is this current? Are the procedures the same with the latest release of the blog module?

    • Gravatar
      Subodh Thursday, May 21, 2009 at 2:51 AM
      Re: Notify on reply to Comment in DNN Blog Module
      These were written for 3.05.01; Even then, you should make a backup, just in case!

  
Locations of visitors to this page Clicky Web Analytics 

Subodh's Blog Rating

 

DISCLAIMER

The opinion expressed
on this page 
is strictly that
of the page author
who has a
habit of animating
day-dreaming
and
fictionalizing
out of thin air.
 

The contents of this page
have not been
reviewed 
nor
approved
by 
Yahoo!

 Follow this blog
  
 Tag Cloud
  
Archives
 

Top 5 Posts of Last year
Copyright © 1995-2009 Subodh Shakya. All rights reserved.{Powered by SpeedBlog}