Ad Expiry Notification System

Question

User-327685722 posted

I am working on an additional page in the admin interface which will allow Ad Expiry Notification configuration. Currently this is what I have in mind, a simple admin page say AdExpiry.aspx which will allow for:

 

a)      customization of the email notification message

b)      configure email notification threshold

c)      configure expired ad deletion threshold

 

The flow is subject to change but basically would be like: get expired ads or ads to be expired within 24 hours; I already started to work on the SQL, currently I have:

 

SELECT DISTINCT Classifieds_Ads.MemberId, Classifieds_Members.Id, Classifieds_Members.AspNetUsername

from Classifieds_Ads, Classifieds_Members

WHERE

ExpirationDate < getdate()

and Classifieds_Members.Id = Classifieds_Ads.MemberId

 

which gives me the ID and AspNetUsername of people with expired ads, I guess the next step would be to use the AspNetUsername and connect to the ASPNET database to get the userid GUID in aspnet_users, then using the userid GUID look up the email address in aspnet_Membership….

 

Ultimately I want to automate this entire process, I thought this would be a great community discussion as I am sure a lot of people are going to need this feature set J

Answer 1

User730446648 posted

Your right, I too will be implementing a similar procedure into my site.

Although I haven't gotten started on that yet.

It looks like you're on the right track, keep it up.

Please keep this post updated with your code, I'm sure I'm not the only one interested in adding this feature.

 

Thanks

 DarkKnight187

 

Answer 2

User1901745416 posted
Sounds like a great idea.  Would you please share you implementation when you're done and ready?  Thank you.

Answer 3

User1920548513 posted
Me too I'm very interested in this. Keep us updated on any progress please.. Thank you

Answer 4

User1920548513 posted

I have managed to implement the expiry notification system. Here are the steps I took:

step 1: I created a stored procedure named GetToExpireAds whose code is

CREATE PROCEDURE GetToExpireAds
@ExpirationDate smalldatetime
AS
SELECT * from ClassifiedsView_Ads
where ExpirationDate = @ExpirationDate
GO

Step 2: I added this strored procedure to the Ads.xsd file through VS.

Step 3: I added the method GetToExpireAds() to the AdsDB class in the Ads.cs file. Its implementation is
public static AdsDataComponent.AdsDataTable GetToExpireAds(DateTime expiration)
{
        using (AdsDataAdapter db = new AdsDataAdapter())
        {
          return db.GetToExpireAds(expiration);
        }
}

Step 4: I added the method CheckAdsToExpire() to the class Maintenance in the Maintenance.cs file. Its implementation is:
private static void CheckAdsToExpire()
{
       AdsDataComponent.AdsDataTable adsToExpire = null;
       DateTime expiration = DateTime.Today.AddDays(1);
       adsToExpire = AdsDB.GetToExpireAds(expiration);
       foreach (AdsDataComponent.AdsRow ad in adsToExpire.Rows)
       {
          MembershipUser member = Membership.GetUser(ad.MemberName);
          NotifyMemberByMail(ad.Title, member.Email);
       }
}

Step 5: I added the method NotifyMemberByMail() to the class Maintenance in the Maintenance.cs file. Its implementation is:
private static void NotifyMemberByMail(string title, string recipientEmail)
{
 SiteSettings s = SiteSettings.GetSharedSettings();

        try
        {
                MailMessage m = new MailMessage(s.SiteEmailFromField, recipientEmail);
                m.Subject = "Your ad will expire next day";
                m.Body = "Your ad with title '" + title +
                            "' will expire next day on " + DateTime.Today.AddDays(1); ;
                SmtpClient client = new SmtpClient();

                // uncomment this code for ssl enabled smtp servers
                // client.EnableSsl = true;

                client.Send(m);
        }
        catch(Exception ex)
       {
        }
}

Step 6: I modified the implementation of the method HourlyMaintenanceTimer() of the Maintenance class in the Maintenance.cs file. Its new
implementation is :
public static void HourlyMaintenanceTimer(object state)
{
       CheckAdExpirations();
       ProcessSummaryNotification();

        // execute this code only once a day
        if (DateTime.Now.Hour == 0)
         CheckAdsToExpire();
}

The content of the notification message probabaly needs to include more details. Hope this helps

Answer 5

User334621554 posted

Thanks for your post. 

I only recommend putting some guards on your objects as to avoid null reference errors. Also, why are you suppressing exceptions in your NotifyMemberByMail method?  Shouldn't it get logged or emailed to the Admin?  How can you audit failures?

I'd also write a new delegate for the timer to run (callback) everry 24 hours or so, or better yet, make it configurable as a setting that the admin can set or turn off!  This way, you don't have to change any code to turn configure it (turn it off or on and specify the hours).  Just a couple suggestions.

Thanks.
 

Answer 6

User1920548513 posted

Thank you for your suggestions. But Maintenance.HourlyMaintenanceTimer is  a callback that runs every hour by the timer so there is no need to create another callback that runs every 24 hours. We can just reuse the first one with the addition of the condition:

 if (DateTime.Now.Hour == 0)
         CheckAdsToExpire();

 Also there is no logging system in the application so far to audit the exceptions, it would be useful to implement one. Hope this helps

Answer 7

User730446648 posted

So did anyone figure out how to do this in VB.

I am going to need this feature, and it would be greatly appreciated if anyone already got it.

Thank you

Daniel

Answer 8

User730446648 posted

So I just finished converting the code to VB, and for anyone else out there,

I have come across a great website that will convert C# to VB.

 http://www.developerfusion.co.uk/utilities/convertcsharptovb.aspx

So easy with the tool.

I still have to test the expire notification, but I'll post back with the results.

Answer 9

User730446648 posted

So I have finally been able to get this to work right.

I used the above mentioned tool to convert the C# to VB, and andrews code didn't quite work.

So this is what worked for me in VB of course, first off I ended up not using andrews step 1, step 2, or step 3. The process is already built into the code.

The only file that needs to be modified is the maintenance file. And as I am posting the only thing that I have not tested is to get it to run daily.

By default it will run every hour, which makes it easier for testing.

So I modified the hourly maintenance timer to this,

 

Public Shared Sub HourlyMaintenanceTimer(ByVal state As Object)

CheckAdExpirations()

ProcessSummaryNotification()

CheckAdsToExpire()

End Sub

And then I added this to the maintenance file

Private Shared Sub NotifyMemberByMail(ByVal title As String, ByVal recipientEmail As String, ByVal address As String, ByVal location As String, ByVal categoryname As String, ByVal id As String, ByVal membername As String)

Dim s As SiteSettings = SiteSettings.GetSharedSettings()

Dim messageBody As New StringBuilder()

messageBody.AppendLine()

messageBody.Append("RE: Detelli Property Network Listing " + ClassifiedsHttpApplication.SiteUrl)messageBody.AppendFormat("ShowAd.aspx?id={0}", id)

messageBody.AppendLine()

messageBody.AppendLine()

messageBody.AppendLine("Dear " + membername + ",")

messageBody.AppendLine()

messageBody.AppendLine("Thank you for listing your property with Detelli Property Network.")messageBody.AppendLine("This message has been sent to inform you that your listing is about to expire.")

messageBody.AppendLine()

messageBody.AppendLine("Your Listing will expire on " + DateTime.Today.AddDays(1))

messageBody.AppendLine()

messageBody.AppendLine(title)

messageBody.AppendLine(address)

messageBody.AppendLine(location + ", " + categoryname)

messageBody.AppendLine()

messageBody.AppendLine("To extend your listing, Please click the link below.")

messageBody.AppendLine()

messageBody.Append(ClassifiedsHttpApplication.SiteUrl)

messageBody.AppendFormat("EditAd.aspx?id={0}", id)

messageBody.AppendLine()

messageBody.AppendLine()

messageBody.AppendLine("Or log on to www.detelli.com and go to the 'my detelli' page and choose edit listing.")

messageBody.AppendLine()

 

Try

Dim m As New MailMessage(s.SiteEmailFromField, recipientEmail)

m.Subject = "Your property listing will expire Soon"

m.Body = messageBody.ToString()

Dim client As New SmtpClient()

' uncomment this code for ssl enabled smtp servers

' client.EnableSsl = true;

client.Send(m)

Catch ex As Exception

End Try

End Sub

And lastly this

 

Private Shared Sub CheckAdsToExpire()

Dim ads As AdsDataComponent.AdsDataTable = Nothing

Using db As New AdsDataAdapter()

Dim expiration As DateTime = DateTime.Now.AddDays(1)

ads = db.GetExpiredAds(expiration, CInt(AdStatus.Activated))

Dim ad As AdsDataComponent.AdsRow

For Each ad In ads.RowsDim member As MembershipUser = Membership.GetUser(ad.MemberName)

NotifyMemberByMail(ad.Title, member.Email, ad.Address, ad.Location, ad.CategoryName, ad.Id, ad.MemberName)

Next ad

End Using

End Sub

End Class

 

I'll find out at midnight tonight if the if statement in the hourlymaintenancetimer will work or not.

If DateTime.Now.Hour = 0 Then
CheckAdsToExpire()
End If

If that works properly it will only run CheckAdsToExpire once a day, and if not I will post a fix for it.

Also note my modifications to NotifyMembersByEmail I have added to my database suchas Address, so if you do not have, or want address in the email you will have to delete it there and in the last entry.

I just thought a lot of other users could benefit from my work.

Good Luck to all.

Daniel Meis

 

Answer 10

User1920548513 posted

Hi darknight,

Why my code didn't work? I tested it and it worked. What about your code did it work? What about ("EditAd.aspx?id={0}", id) in the link which is sent to the user with the message? Did you implement this page EditAd.aspx ? Thanks

Answer 11

User730446648 posted

Mine works fine, but I did make a few modifications,

For one declaring lastNotification does not really work that well on newer sites, since if there is no activity for several hours your host servers cache of your site will expire.

I use an xml file to determine the last notification.

I am currently working on an update for this code, as is above, it will send one email per ads about to expire.

If a user has 5 about to expire they then get 5 emails.

I am trying to figure out how to update it to send one email with the five ads in it.

But the code above does work.

 

Answer 12

User730446648 posted

I just noticed that I also added another storedprocedure

ads = db.GetToExpireAds(expiration, CInt(AdStatus.Activated))

 

ALTER PROCEDURE GetToExpireAds

@ExpirationDate smalldatetime = getdate,

@AdStatus int = 0

AS

SELECT * FROM ClassifiedsView_Ads

WHERE

ExpirationDate = @ExpirationDate AND

(@AdStatus = 0 OR AdStatus = @AdStatus)

Answer 13

User1575715132 posted

hi darknight187

I tried your code and yes it works.

But there is a little problem about it.

It sends notification email periodically. I mean I am receiving email for expiring ad every 5 minutes. So there are a lot of same mails in mailbox.

What should be done in order to send just 1 mail and skip that user ?

Answer 14

User730446648 posted

I've made a lot of upgrades since I posted that fix above.

1. I moved all the email processes into a specific class.
2. I am using a xml file to log when the last email was sent.
3. I set it up to process email only on click of a button, this I click every morning.
4. I dramatically changed the email sent to include alternate views, html and plain text.
5. And I included another funtion that runs every 20 days, 30 days, 45 days, or not at all.
    This function sends the user a listing summary of every thing they have listed.

With out seeing your actual code I can't say exactly what your issue is.
If it's sending every five minutes, something is triggering it.

Below is a sample of connecting to a xml file on a button click,
so even if you click twice, it will only process once.

If you would like me to fix it for you I will do it with all the above mentioned additions for $40.
And if you send me a private message with your email address,
I'll send you a sample of what my emails look like.

But try this.

Protected Sub ListingsNotificationButton_Click(ByVal sender As Object, ByVal e As EventArgs)

'Path of your xml file

Dim path As String = Server.MapPath("~/MyFolderForXml/NotificationTime.xml") Dim doc As System.Xml.XmlDocument = New XmlDocument()

doc.Load(path) 'Load XML file

'Assumes exact XML format you provided above

Dim node As System.Xml.XmlNode = doc.SelectSingleNode("Settings/ListingsNotificationDateTime")

'Get date node from XML file

If Not (node Is Nothing) Then

lblNotification.Text = node.InnerText

node.InnerText = DateTime.Now.ToString("MM/dd/yy")

'Set value of date node

doc.Save(path)

'Save to the original file path

End If

 

Dim lastNotification As DateTime = lblNotification.Text

Dim sendNotification As Boolean = False

Dim oneDayAgo As DateTime = DateTime.Today.Subtract(TimeSpan.FromDays(1))

If oneDayAgo >= lastNotification Then

sendNotification = True

End If

If sendNotification Then

CheckAdsToExpire()

CurrentListingsSummary()

HeaderLabel.Text = "Email Listing Summaries Have Now Been Sent"

Else

HeaderLabel.Text = "Email Listing Summaries Have Already Been Sent Today."

End If

End Sub

 

Good Luck

Daniel

Answer 15

User1644461932 posted

Hi ! thats a great idea , frankly speaking I realy need such an idea for my project I am working On.........

Answer 16

User-1171043462 posted

You can take help of SQL Server to do this

http://www.aspsnippets.com/post/2009/03/08/Automated-Email-Notifications-using-SQL-Server-Job-Schedular.aspx