If you need a quick fix I got this working using sql2excel pro component, some jiggery pokery with an sql query, and a cron task.

It's a cludge, but it works in the absence of any other workable system for the foreseeable future.

Sql2excel Pro also allows you to send worksheets as attachments so I use it to fill another gap, namely that there is no notification to tell you know that a user has got to the basket stage, nor is there any way of doing regular mailings/reports for members due to expire or full membership lists (eg to check against other recorrds, run mail merges against etc.

Drop me a PM and I might be persuaded to dig out the sql & code I used

Ruth

Aly wrote:

Please advise how I can set this up so that my annual members receive notification in advance of their expiration date?

Has this been resolved/fixed/worked around? One of my clients has a site where a ton of people will face this email issue... (and I agree - what's the purpose of a grace period if the email doesn't go out until the end of it???)

I didn't realize this was the issue until I started looking... so any insight would be very much appreciated at this point.

NOTE TO MODS: I had major problems writing this response hence why it's over numerous posts. Feel free to combine them if you want.

rodsdesign,

I couldn't wait for the "well we might put it in a future release" either, so here is how I addressed it:

Notifing administrators of impending expirations

Get SQL2Excel Pro - it is not very expensive at all and a very nifty bit of kit

Set up your sections and categories however you want, then create the worksheets you require.

For my client, they wanted All members, expiring in 7 days, expiring in 14 days, Expiring in 1 month which uses the following:

All users
[code:1]
SELECT comp.user_id, comp.firstname, comp.lastname, comp.approved, comp.confirmed, comp.lastupdatedate, comp.twittername, comp.cb_organisationname, comp.cb_address1, comp.cb_address2, comp.cb_address3, comp.cb_address4, comp.cb_town, comp.cb_county, cb_postcode, cb_telephone, cb_faxnumber, cb_website, cb_secondwebaddress, cb_employees, cb_shortdescription, comp.cb_businesscategories, comp.cb_referralsource, comp.cb_subs_inv_first_name, comp.cb_subs_inv_last_name, comp.cb_subs_inv_payer_business_name, comp.cb_subs_inv_address_street, comp.cb_subs_inv_address_city, comp.cb_subs_inv_address_state, comp.cb_subs_inv_address_zip, comp.cb_subs_inv_address_country, comp.cb_subs_inv_contact_phone, comp.cb_subs_inv_vat_number, subs.user_id, subs.status, subs.plan_id, subs.subscription_date, subs.last_renewed_date, subs.expiry_date, subs.previous_expiry_date, subs.previous_status

FROM jos_comprofiler comp, jos_cbsubs_subscriptions subs

WHERE comp.user_id=subs.user_id

ORDER BY comp.user_id
[/code:1]

Expiring in 7 days
[code:1]
As above but with

WHERE comp.user_id=subs.user_id AND subs.expiry_date <= date_add(NOW(),INTERVAL 7 DAY) AND subs.status='A'

ORDER BY comp.user_id
[/code:1]

Expiring in 14 days
[code:1]
As above but with

WHERE comp.user_id=subs.user_id AND subs.expiry_date <= date_add(NOW(),INTERVAL 14 DAY) AND subs.status='A'

ORDER BY comp.user_id
[/code:1]

Expiring in 1 month
[code:1]
As above but with

WHERE comp.user_id=subs.user_id AND subs.expiry_date <= date_add(NOW(),INTERVAL 30 DAY) AND subs.status='A'

ORDER BY comp.user_id
[/code:1]

The above can be created into a workbook to send a list to the admin's of the users that are due to expire in the future.

Post edited by: RCheesley, at: 2010/06/11 18:00

Creating an email to send to users when they are about to expire

This was a bit more tricky, and it involves using a cron task (well so does the task above if you intend on mailing the spreadsheet) but here goes:

Read the thread here: www.joomlapolis.com/component/option,com_joomlaboard/Itemid,38/func,view/catid,88/id,125043/#125043 which is where this mostly comes from. Thanks to everyone who contributed.

Database changes NOTE YOUR DATABASE MUST BE RUNNING MySQL 5 FOR THIS TO WORK:
[code:1]
DROP TABLE IF EXISTS `jos_cbsubs_subscription_notification`;
CREATE TABLE IF NOT EXISTS `jos_cbsubs_subscription_notification` (
`id` int(11) NOT NULL auto_increment,
`subscription_id` int(11) NOT NULL,
`notified_datetime` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `subscription_id` (`subscription_id`),
KEY `notified` (`notified_datetime`)
) TYPE=InnoDB AUTO_INCREMENT=10 ;
[/code:1]

Post edited by: RCheesley, at: 2010/06/11 18:00