Concrete5 Filter SQL

Question

I am trying to modify a Concrete5 Plugin that sends users a warning email when their subscription is about to expire. It works by filtering out the users that have already been notified first, then selecting the users whose subscriptions as of today are a given number of days (emailOnExpirationDays) away from the expiration date (expirationDate).

My problem is that I need to be able to notify users 3 times instead of once. I would like to notify them 30 days prior, 5 days prior, and 1 day prior. My first obstacle is filtering out only the users that were notified within 5 days (not notified intentionally 25 days ago). How can I modify the PHP and SQL below to accomplish this? Code commented out are things I have tried. Information on the filter function is here.

The collection is instantiated here:

$expiringTxns = new LMemTxnList();
$expiringTxns->filterByProductWithExpirationEmailsThatShouldBeWarned();

......

public function filterByProductWithExpirationEmailsThatShouldBeWarned() {
    $this->setupProductExpirationQueries();//displays all with account

    //we haven't already sent out an expiration warning or, for that matter, a notice
    $this->filter('notifiedDate', null);
    $this->filter('warnedDate', null); //Caitlin commented out to allow more than one warning
    //$this->filter('warnedDate', '!= BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY) AND NOW()');


    // the emailOnExpirationDays has been properly configured
    $this->filter('emailOnExpirationDays', null, '!=');
    $this->filter('emailOnExpirationDays', 0, '>');

    //the expirationDate - warning is between 5 days ago (don't want to send a bunch of emails) and now
    //  the warning code should be getting run after the notice code, so we shouldn't have to worry about 3 day warnings and sending warning and notice at the same time
    $this->filter(null, 'DATE_SUB(expirationDate, INTERVAL emailOnExpirationDays DAY) BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY) AND NOW()');
  //$this->filter(null, 'DATE_SUB(expirationDate, INTERVAL 30 DAY) BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY) AND NOW()');

}


protected function setupProductExpirationQueries() {
    $this->addToQuery(' INNER JOIN LMemProducts ON txns.productID = LMemProducts.ID');

    // the expiration date exists (some products don't expire)
    $this->filter('expirationDate', null, '!=');

    // we're supposed to send emails
    $this->filter('emailOnExpiration', true);

    $this->filter('emailOnExpirationDays', null, '!=');
    $this->filter('emailOnExpirationDays', 0, '!=');
}

}

Answer 1

The following SQL should work for you:

(
    (DATE_SUB(expirationDate, 30 DAYS) BETWEEN DATE_SUB(NOW(), 3 DAYS) AND NOW())
    OR
    (DATE_SUB(expirationDate, 5 DAYS) BETWEEN DATE_SUB(NOW(), 3 DAYS) AND NOW())
    OR
    (DATE_SUB(expirationDate, 1 DAYS) BETWEEN DATE_SUB(NOW(), 3 DAYS) AND NOW())
)
AND
(DATE_ADD(warnedDate, 4 DAYS) <= NOW())

Basically, you're checking if the expiration date minus the warning period is between 3 days ago and now. This allows for small (3 day) blips in the job not getting run. Specifically, if the expiration date is March 15th, it'll return true between Feb 15th (let's assume that's 30 days) and Feb 18th. But we don't want to send it multiple times in this period (which would be a potential problem if the job could possibly run more than once a day), so you also check that the warnedDate is at least 4 days ago. This plays well with your 30/5/1 periods.

To put that into the filterBy...Warned() function, you would remove the $this->filter('warnedDate', null); line and then one with the DATE_SUB, and then you'd add the SQL in "directly":

$this->filter(null, "(
    (DATE_SUB(expirationDate, 30 DAYS) BETWEEN DATE_SUB(NOW(), 3 DAYS) AND NOW())
    OR
    (DATE_SUB(expirationDate, 5 DAYS) BETWEEN DATE_SUB(NOW(), 3 DAYS) AND NOW())
    OR
    (DATE_SUB(expirationDate, 1 DAYS) BETWEEN DATE_SUB(NOW(), 3 DAYS) AND NOW())
)
AND
(DATE_ADD(warnedDate, 4 DAYS) <= NOW())");