Reminder and expiration notice by email strategy

[eluser]sikkle[/eluser]
Hello guys,

I'm not looking around for some code but more for a good deployment strategy.

Let's say you have a system, where user (pay) subscribe, that mean you have an expiration date.

Let's say you have to send them reminder at 90-45-30-15-5 days reminder and indeed a Expiration notice when account didnt receive any payment in time.

I presume we can call this a cron job, still i need to make it also accessible and executable from backend.



Things i know from start :

1. Database field who keep track of expiration date.
2. I think system work better if you do some kind of limit(50) request per mass sending.
3. Need to track what have been done to don't re-select the same on the next query (limit(50).
4. Need to find a way to handle each 90-45-30-15-5 days reminder and the expiration notice.



Some advanced user here can give some suggestion ?

thanks!

[eluser]Matthew Lanham[/eluser]
Heres some suggestion:

Create a table:

member_id -> INT
subscription_start_date -> DATE
subscription_payment_status -> VARCHAR(10) (options something like paid, unpaid)
reminder_email_stage -> int (options 90 / 45 / 30 / 15 / 5)
reminder_email_last_sent -> DATE

So some example SQL's:

This will return all the payments outstanding (i would set this up as a cron to run every day)
SELECT * from tblSubscriptionTracker where subscription_payment_status = 'unpaid'

Then you need to see how long it has been outstanding for using a datediff function:

http://www.ilovejackdaniels.com/php/php-...-function/

so then you can see by calculating the difference between the subscription start date and todays date how many days the difference is, then you can have something like:

switch($days){
case 90:
//Check that stage does not already = 90, and reminder_email_last_sent does not = today, and if not
//Sent 90 day email, set reminder_email_stage = 90, set reminder_email_last_sent = date('Y-m-d')
break;

case 45:
//Check that stage does not already = 45, and reminder_email_last_sent does not = today, and if not
//Sent 45 day email, set reminder_email_stage = 45, set reminder_email_last_sent = date('Y-m-d')
break;

case 30:
//Check that stage does not already = 30, and reminder_email_last_sent does not = today, and if not
//Sent 30 day email, set reminder_email_stage = 30, set reminder_email_last_sent = date('Y-m-d')
break;

case 15:
//Check that stage does not already = 15, and reminder_email_last_sent does not = today, and if not
//Sent 15 day email, set reminder_email_stage = 15, set reminder_email_last_sent = date('Y-m-d')
break;

case 5:
//Check that stage does not already = 5, and reminder_email_last_sent does not = today, and if not
//Sent 5 day email, set reminder_email_stage = 5, set reminder_email_last_sent = date('Y-m-d')
break;
}

Hopefully this gives you a rough idea of what to do..you shouldn't have problems with your limit 50 part as there are a couple of checks in there to prevent multiple emails, however if i were you i wouldn't limit it, it is only useful if you are sending 100s of emails to the same ISP i.e. all @aol.com email addresses ..

[eluser]sikkle[/eluser]
I really appreciate the time you take to do that.

I'll find a way to handle a little bit more safe rminder_email_last_sent, but still that confirm me what i though from start.

If someone else already having made another implementation of the same process, don't be shy COMON!



thanks.