1. Forums
  3. Archive
  5. CBSubs - Paid Membership Management Solution
  7. Usage
  9. Expiration email question

Expiration email question

13 years 10 months ago #135411 by RCheesley
Replied by RCheesley on topic Re:Expiration email question
Creating the component
Paste this code into a new component - e.g. components/com_cbsubsnotifier/cbsubsnotifier.php

[code:1]
<?php

$db =& JFactory::getDBO();

$sql = 'SELECT #__cbsubs_subscriptions.id, #__comprofiler.firstname, #__comprofiler.lastname,
#__users.email, #__cbsubs_subscriptions.expiry_date
FROM `#__cbsubs_subscriptions`
INNER JOIN #__comprofiler ON #__cbsubs_subscriptions.user_id = #__comprofiler.user_id
INNER JOIN #__users ON #__comprofiler.user_id = #__users.id
WHERE #__cbsubs_subscriptions.expiry_date = date(date_add(NOW( ), INTERVAL 6 WEEK)) OR
#__cbsubs_subscriptions.expiry_date = date(date_add(NOW( ), INTERVAL 3 WEEK)) OR #__cbsubs_subscriptions.expiry_date = date(date_add(NOW( ),
INTERVAL 1 WEEK))

AND (
(

SELECT date(max( notified_datetime ))
FROM #__cbsubs_subscription_notification
WHERE #__cbsubs_subscription_notification.subscription_id = #__cbsubs_subscriptions.id
) <> date(NOW( ))
)';

$db->setQuery($sql);
$tonotifies = $db->loadAssocList();

print $db->_sql.'<br /><br /><br />';

var_dump($tonotifies);
if ($tonotifies){

$headers = 'From: YOUR FROM EMAIL ADDRESS' . "\r\n";
$headers .= 'Cc: YOUR CC EMAIL ADDRESS' . "\r\n";
$sqltext = 'select * from #__content where id=YOUR CONTENT ID NUMBER';
$db->setQuery($sqltext);
$content = $db->loadObject();
$content->introtext = str_replace (array('</p>','<br>','<br />'),"\n",$content->introtext);

$content->introtext = strip_tags($content->introtext);

foreach($tonotifies as $notify){

$res = mail ($notify,
$content->title,
$content->introtext,
$headers
);
print $notify.': '.$res.'<br />';


$inssql = 'insert into #__cbsubs_subscription_notification (subscription_id,notified_datetime) values '.
'('.$notify.",'".date('Y-m-d H:i:«»s',time())."')";
$db->setQuery($inssql);
$db->query();

}


}
die();

?>[/code:1]
Ruth Cheesley
Joomla Specialist
Virya Technologies Ltd.

Please Log in to join the conversation.

13 years 10 months ago #135413 by RCheesley
Replied by RCheesley on topic Re:Expiration email question
This reads the email text from content item, and sends to the user, CC'ing to an address you specify. It sends at 6 weeks, 3 weeks and 1 week intervals.

To call the cron tasks above I used the following (I think I had trouble calling the CBSubs notifier with php hence used another method) - replace * with w.g@et remove . and @:

[code:1]
php -f /home/YOURACCOUNT/public_html/administrator/components/com_sql2excel/helpers/schedule_cron.php >/dev/null 2>&1

* www.YOURURL.CO.UK/index.php?option=com_cbsubsnotifier >/dev/null 2>&1
[/code:1]
I hope this helps some!

Ruth

Post edited by: RCheesley, at: 2010/06/11 18:02
Ruth Cheesley
Joomla Specialist
Virya Technologies Ltd.

Please Log in to join the conversation.

13 years 10 months ago #135417 by rodsdesign
Replied by rodsdesign on topic Re:Expiration email questi
Thanks for your reply Ruth.

I took a slightly different approach (not expecting such a quick response!) :)

I wrote an external script that grabs everyone who's subs are expiring in 10 days, then again in 5 days. If they haven't re-upped - the expiration email gets sent from cbsubs.

I've removed the identifying info for privacy for my client...

[code:1]<?php
include ("data_connect.php"«»); // grabs the database info
$today=date('Y-m-d');


$ten = strtotime($today) + 240*3600;
$five = strtotime($today) + 120*3600;
$expire= date('Y-m-d', $ten);
$expire2= date('Y-m-d', $five);


$q="SELECT joscbsubs_subscriptions.status, joscbsubs_subscriptions.user_id, joscbsubs_subscriptions.plan_id, joscbsubs_subscriptions.expiry_date, joscomprofiler.firstname, joscomprofiler.lastname, josusers.email
FROM joscbsubs_subscriptions, josusers, joscomprofiler
WHERE joscbsubs_subscriptions.user_id = josusers.id
AND joscbsubs_subscriptions.status = 'A'
AND joscbsubs_subscriptions.expiry_date > '$expire 00:00:00'
AND joscbsubs_subscriptions.expiry_date < '$expire 23:59:59'
AND joscbsubs_subscriptions.user_id = josusers.id
AND joscomprofiler.user_id = josusers.id";

$r=mysql_query($q);

while ($rr=mysql_fetch_array($r)) {

$day=$rr[expiry_date];
$ex_date=date('l, F j, Y',strtotime($day));

$message="Dear $rr[firstname],


rest of the message here";

$subject="subject";
$recipient = $rr[email];

$body = "<html>\n";
$body .= "<body style=\"font-family:Verdana, Verdana, Geneva, sans-serif; font-size:12px; color:#000;\">\n";
$body = $message;
$body .= "</body>\n";
$body .= "</html>\n";

$headers = "From: my company <info@mycompany.com>\r\n";
$headers .= "Reply-To: info@mycompany.com\r\n";
$headers .= "Return-Path: info@mycompany.com\r\n";
$headers .= 'MIME-Version: 1.0' . "\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

return mail($recipient, $subject, $message, $headers);

}

// 5 days

$q="SELECT joscbsubs_subscriptions.status, joscbsubs_subscriptions.user_id, joscbsubs_subscriptions.plan_id, joscbsubs_subscriptions.expiry_date, joscomprofiler.firstname, joscomprofiler.lastname, josusers.email
FROM joscbsubs_subscriptions, josusers, joscomprofiler
WHERE joscbsubs_subscriptions.user_id = josusers.id
AND joscbsubs_subscriptions.status = 'A'
AND joscbsubs_subscriptions.expiry_date > '$expire2 00:00:00'
AND joscbsubs_subscriptions.expiry_date < '$expire2 23:59:59'
AND joscbsubs_subscriptions.user_id = josusers.id
AND joscomprofiler.user_id = josusers.id";

$r=mysql_query($q);

while ($rr=mysql_fetch_array($r)) {

$day=$rr[expiry_date];
$ex_date=date('l, F j, Y',strtotime($day));



$message="Dear $rr[firstname],


rest of message";

$subject="Your subscription is about to expire.";
$recipient = $rr[email];

$body = "<html>\n";
$body .= "<body style=\"font-family:Verdana, Verdana, Geneva, sans-serif; font-size:12px; color:#000;\">\n";
$body = $message;
$body .= "</body>\n";
$body .= "</html>\n";

$headers = "From: my company <info@mycompany.com>\r\n";
$headers .= "Reply-To: info@mycompany.com\r\n";
$headers .= "Return-Path: info@mycompany.com\r\n";
$headers .= 'MIME-Version: 1.0' . "\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

return mail($recipient, $subject, $message, $headers);
}

?>[/code:1]
Its a quick fix...not pretty - but its automated and works :)

thanks again for your input... I'll see about integrating the two!

Please Log in to join the conversation.

13 years 10 months ago #135448 by krileon
Replied by krileon on topic Re:Expiration email questi
Thank you both for providing your current workarounds. Hopefully the proposed feature to send an email before Grace Period goes in affect will be sufficient to your needs (this API will also help extend functionality to other integrations).

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

13 years 7 months ago #142197 by ThaiM
Replied by ThaiM on topic Re:Expiration email questi
How do I use this script? Save it as somepage.php and add it as a cron task?



rodsdesign wrote:

Thanks for your reply Ruth.

I took a slightly different approach (not expecting such a quick response!) :)

I wrote an external script that grabs everyone who's subs are expiring in 10 days, then again in 5 days. If they haven't re-upped - the expiration email gets sent from cbsubs.

I've removed the identifying info for privacy for my client...

[code:1]<?php
include ("data_connect.php"«»); // grabs the database info
$today=date('Y-m-d');


$ten = strtotime($today) + 240*3600;
$five = strtotime($today) + 120*3600;
$expire= date('Y-m-d', $ten);
$expire2= date('Y-m-d', $five);


$q="SELECT joscbsubs_subscriptions.status, joscbsubs_subscriptions.user_id, joscbsubs_subscriptions.plan_id, joscbsubs_subscriptions.expiry_date, joscomprofiler.firstname, joscomprofiler.lastname, josusers.email
FROM joscbsubs_subscriptions, josusers, joscomprofiler
WHERE joscbsubs_subscriptions.user_id = josusers.id
AND joscbsubs_subscriptions.status = 'A'
AND joscbsubs_subscriptions.expiry_date > '$expire 00:00:00'
AND joscbsubs_subscriptions.expiry_date < '$expire 23:59:59'
AND joscbsubs_subscriptions.user_id = josusers.id
AND joscomprofiler.user_id = josusers.id";

$r=mysql_query($q);

while ($rr=mysql_fetch_array($r)) {

$day=$rr[expiry_date];
$ex_date=date('l, F j, Y',strtotime($day));

$message="Dear $rr[firstname],


rest of the message here";

$subject="subject";
$recipient = $rr[email];

$body = "<html>\n";
$body .= "<body style=\"font-family:Verdana, Verdana, Geneva, sans-serif; font-size:12px; color:#000;\">\n";
$body = $message;
$body .= "</body>\n";
$body .= "</html>\n";

$headers = "From: my company <info@mycompany.com>\r\n";
$headers .= "Reply-To: info@mycompany.com\r\n";
$headers .= "Return-Path: info@mycompany.com\r\n";
$headers .= 'MIME-Version: 1.0' . "\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

return mail($recipient, $subject, $message, $headers);

}

// 5 days

$q="SELECT joscbsubs_subscriptions.status, joscbsubs_subscriptions.user_id, joscbsubs_subscriptions.plan_id, joscbsubs_subscriptions.expiry_date, joscomprofiler.firstname, joscomprofiler.lastname, josusers.email
FROM joscbsubs_subscriptions, josusers, joscomprofiler
WHERE joscbsubs_subscriptions.user_id = josusers.id
AND joscbsubs_subscriptions.status = 'A'
AND joscbsubs_subscriptions.expiry_date > '$expire2 00:00:00'
AND joscbsubs_subscriptions.expiry_date < '$expire2 23:59:59'
AND joscbsubs_subscriptions.user_id = josusers.id
AND joscomprofiler.user_id = josusers.id";

$r=mysql_query($q);

while ($rr=mysql_fetch_array($r)) {

$day=$rr[expiry_date];
$ex_date=date('l, F j, Y',strtotime($day));



$message="Dear $rr[firstname],


rest of message";

$subject="Your subscription is about to expire.";
$recipient = $rr[email];

$body = "<html>\n";
$body .= "<body style=\"font-family:Verdana, Verdana, Geneva, sans-serif; font-size:12px; color:#000;\">\n";
$body = $message;
$body .= "</body>\n";
$body .= "</html>\n";

$headers = "From: my company <info@mycompany.com>\r\n";
$headers .= "Reply-To: info@mycompany.com\r\n";
$headers .= "Return-Path: info@mycompany.com\r\n";
$headers .= 'MIME-Version: 1.0' . "\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";

return mail($recipient, $subject, $message, $headers);
}

?>[/code:1]
Its a quick fix...not pretty - but its automated and works :)

thanks again for your input... I'll see about integrating the two!

Please Log in to join the conversation.

13 years 7 months ago #142325 by rodsdesign
Replied by rodsdesign on topic Re:Expiration email questi
Hi ThaiM

yes - save it as a file then run a cron job against it once a day.

You'll need to put your database settings in the actual scripts, or do an include like I did...

if you need help implementing it - send me your contact info and I can help.

Rod

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Archive
  5. CBSubs - Paid Membership Management Solution
  7. Usage
  9. Expiration email question
Time to create page: 0.230 seconds

Search

Login / Logout

User Menu