Ad Expiry Notification System RRS feed

  • 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


    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

    Thursday, October 12, 2006 9:54 PM

All replies

  • 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.





    Wednesday, October 18, 2006 3:06 PM
  • User1901745416 posted
    Sounds like a great idea.  Would you please share you implementation when you're done and ready?  Thank you.
    Saturday, October 28, 2006 4:10 AM
  • User1920548513 posted
    Me too I'm very interested in this. Keep us updated on any progress please.. Thank you
    Saturday, October 28, 2006 11:25 AM
  • 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

    @ExpirationDate smalldatetime
    SELECT * from ClassifiedsView_Ads
    where ExpirationDate = @ExpirationDate

    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();

                    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;

            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)

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

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

    Sunday, October 29, 2006 6:26 PM
  • 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.


    Monday, October 30, 2006 10:03 PM
  • 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)

     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

    Tuesday, October 31, 2006 10:05 AM
  • 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


    Saturday, July 28, 2007 9:27 AM
  • 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.


    So easy with the tool.

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

    Sunday, July 29, 2007 1:44 PM
  • 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)




    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()


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



    "Dear " + membername + ",")


    "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.")


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




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


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



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



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




    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;


    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
    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


    Sunday, August 12, 2007 4:04 PM
  • 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

    Sunday, March 9, 2008 1:40 AM
  • 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.


    Sunday, March 9, 2008 2:43 PM
  • User730446648 posted

    I just noticed that I also added another storedprocedure

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



    @ExpirationDate smalldatetime = getdate,

    @AdStatus int = 0


    SELECT * FROM ClassifiedsView_Ads


    ExpirationDate = @ExpirationDate AND

    (@AdStatus = 0 OR AdStatus = @AdStatus)

    Monday, March 10, 2008 7:31 PM
  • 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 ?

    Saturday, November 8, 2008 7:16 PM
  • 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(

    'Set value of date node


    '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



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


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

    End If

    End Sub


    Good Luck


    Sunday, November 9, 2008 10:25 AM
  • User1644461932 posted

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

    Sunday, June 21, 2009 4:44 AM
  • Sunday, June 21, 2009 9:11 AM