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

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

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

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.
 

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

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

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.

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,

End Sub

And then I added this to the maintenance file

Dim s As SiteSettings = SiteSettings.GetSharedSettings()

Try

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

' uncomment this code for ssl enabled smtp servers

' client.EnableSsl = true;

End Try

End Sub

And lastly this

Dim ads As AdsDataComponent.AdsDataTable = Nothing

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

Dim ad As AdsDataComponent.AdsRow

Next ad

End Using

End Sub

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

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

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.

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)

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 ?

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.

'Path of your xml file

doc.Load(path) 'Load XML file

'Assumes exact XML format you provided above

'Get date node from XML file

If Not (node Is Nothing) Then

'Set value of date node

'Save to the original file path

Dim sendNotification As Boolean = False

If oneDayAgo >= lastNotification Then

sendNotification = True

End If

If sendNotification Then

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