function expireNonRecurringSubs()
{
$db =& JFactory::getDBO();
$db->setQuery("SELECT s.id AS subid, userid\n\tFROM `fabsubs_subscriptions` AS s\n\tINNER JOIN fabsubs_plans AS p ON p.id = s.plan\n\t WHERE status = 'Active' AND lifetime = 0 AND recurring = 0 AND (CASE\n\t\tWHEN p.period_unit = 'Y' THEN datediff(date_add(lastpay_date , interval p.duration year), now())\n\t\tWHEN p.period_unit = 'M' THEN datediff(date_add(lastpay_date , interval p.duration month), now())\n\t\tWHEN p.period_unit = 'W' THEN datediff(date_add(lastpay_date , interval p.duration week), now())\n\t\tWHEN p.period_unit = 'D' THEN datediff(date_add(lastpay_date , interval p.duration day), now())\n\t END <= 0\n\n\t OR ( expiration != '0000-00-00 00:00:00' AND\n\t CASE\n\t\tWHEN p.period_unit = 'Y' THEN datediff(date_add(expiration , interval p.duration year), now())\n\t\tWHEN p.period_unit = 'M' THEN datediff(date_add(expiration , interval p.duration month), now())\n\t\tWHEN p.period_unit = 'W' THEN datediff(date_add(expiration , interval p.duration week), now())\n\t\tWHEN p.period_unit = 'D' THEN datediff(date_add(expiration , interval p.duration day), now())\n\t END <= 0\n\t ))\n\t ");
$ipn = new fabrikPayPalIPN();
$rows =& $db->loadObjectList();
$now = JFactory::getDate()->toMySQL();
$sub =& JTable::getInstance('Subscriptions', 'Table');
$report = $db->getErrorMsg() . "<br>";
foreach ($rows as $row) {
$report .= "<p>subid = {$row->subid} userid = {$row->userid}</p>";
$sub->load($row->subid);
$sub->status = 'Expired';
$sub->eot_date = $now;
$sub->store();
$ipn->fallbackPlan($sub);
}
JUtility::sendMail($mailfrom, $fromname, '*****@*****.**', 'Expired ' . JURI::base() . ' subs:', $report, true);
}
}
$res = JUtility::sendMail($mailfrom, $fromname, $row->email, $mail->subject, $mail->body, true);
}
}
//send email reminders to active subs in old acctexpt table:
//last date this should be used from is 09/03/2011
$db->setQuery("SELECT s.id AS subid, email, name, `type` AS subscription, username,\ndatediff(expiration, now()) AS daysleft, expiration as renew_date\nFROM `#__acctexp_subscr` as s\nleft join #__users as u on s.userid = u.id\nwhere status = 'Active' and plan != 2 and plan !=3\n");
$res = $db->loadObjectList();
foreach ($res as $row) {
if (array_key_exists($row->daysleft, $expiration_mails)) {
$mail = clone $expiration_mails[$row->daysleft];
foreach ($row as $k => $v) {
$mail->subject = str_replace('{' . $k . '}', $v, $mail->subject);
$mail->body = str_replace('{' . $k . '}', $v, $mail->body);
}
$res = JUtility::sendMail($mailfrom, $fromname, $row->email, $mail->subject, $mail->body, true);
}
}
//expire non recurring subs that have expired. Create fall back plan if required
$db->setQuery("SELECT s.id AS subid\nFROM `fabsubs_subscriptions` AS s\nINNER JOIN fabsubs_plans AS p ON p.id = s.plan\n WHERE status = 'Active' AND lifetime = 0 AND recurring = 0 AND CASE\n\tWHEN p.period_unit = 'Y' THEN datediff(date_add(lastpay_date , interval p.duration year), now())\n\tWHEN p.period_unit = 'M' THEN datediff(date_add(lastpay_date , interval p.duration month), now())\n\tWHEN p.period_unit = 'W' THEN datediff(date_add(lastpay_date , interval p.duration week), now())\n\tWHEN p.period_unit = 'D' THEN datediff(date_add(lastpay_date , interval p.duration day), now())\n END <= 0\n\n OR ( expiration != '0000-00-00 00:00:00' AND\n CASE\n\tWHEN p.period_unit = 'Y' THEN datediff(date_add(expiration , interval p.duration year), now())\n\tWHEN p.period_unit = 'M' THEN datediff(date_add(expiration , interval p.duration month), now())\n\tWHEN p.period_unit = 'W' THEN datediff(date_add(expiration , interval p.duration week), now())\n\tWHEN p.period_unit = 'D' THEN datediff(date_add(expiration , interval p.duration day), now())\n END <= 0\n )\n ");
$ipn = new fabrikPayPalIPN();
$rows = $db->loadObjectList();
$now = JFactory::getDate()->toSql();
$sub = FabTable::getInstance('Subscriptions', 'FabrikTable');
foreach ($rows as $row) {
$sub->load($row->subid);
$sub->status = 'Expired';
$sub->eot_date = $now;
$sub->store();
$ipn->fallbackPlan($sub);
}