php - Concrete5过滤SQL

Question

我正在尝试修改一个 Concrete5 插件，该插件会在用户的订阅即将到期时向用户发送警告电子邮件。它的工作原理是首先过滤掉已经收到通知的用户，然后选择截至今天的订阅距到期日期 (expirationDate) 给定天数 (emailOnExpirationDays) 的用户。

我的问题是我需要能够通知用户 3 次而不是一次。我想提前 30 天、5 天和 1 天通知他们。我的第一个障碍是仅过滤掉 5 天内收到通知的用户(25 天前没有故意通知)。我如何修改下面的 PHP 和 SQL 来完成此任务？注释掉的代码是我尝试过的。有关过滤功能的信息是 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

以下 SQL 应该适合您:

(
    (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())

基本上，您要检查过期日期减去警告期是否在 3 天前和现在之间。这允许作业中出现一些小(3 天)的问题而无法运行。具体来说，如果到期日期是 3 月 15 日，则它将在 2 月 15 日(假设为 30 天)和 2 月 18 日之间返回 true。但我们不想在此期间多次发送它(如果作业可能每天运行多次，这将是一个潜在的问题)，因此您还要检查 warnDate 是否至少是 4 天前。这很适合您的 30/5/1 周期。

要将其放入 filterBy...Warned() 函数中，您需要删除 $this->filter('warnedDate', null); 行，然后删除 DATE_SUB，然后您可以“直接”添加 SQL:

$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())");