public function run()
{
$settings = $this->config->PLUGIN_SETTINGS;
$dbc = FannieDB::get($settings['TargetedPromosDB']);
$warehouse = $settings['WarehouseDatabase'];
$rules = new DefectorRulesModel($dbc);
$all_rules = $rules->find('defectorRulesID', true);
if (count($all_rules) == 0) {
$rules->couponUPC('');
$rules->save();
$all_rules = $rules->find('defectorRulesID', true);
}
$rules = $all_rules[0];
$targets = new DefectorTargetsModel($dbc);
$inactive_period = new stdClass();
$inactive_period->start = date('Ymd', strtotime($rules->emptyDays() . ' days ago'));
$inactive_period->end = date('Ymd');
$active_period = new stdClass();
$active_period->start = date('Ymd', strtotime($rules->activeDays() . ' days ago'));
$active_period->end = date('Ymd', strtotime($rules->emptyDays() + 1 . ' days ago'));
$statusP = $dbc->prepare('
SELECT Type,
memType,
staff
FROM ' . $this->config->OP_DB . $dbc->sep() . 'custdata
WHERE CardNo=?
AND personNum=1
');
$meminfoP = $dbc->prepare('
SELECT card_no
FROM ' . $this->config->OP_DB . $dbc->sep() . 'meminfo
WHERE ads_OK = 1
AND (zip LIKE \'55%\' OR zip LIKE \'56%\')
AND card_no=?
');
/**
Lookup accounts with activity matching
the developer criteria
*/
$activityP = $dbc->prepare('
SELECT s.card_no
FROM ' . $warehouse . $dbc->sep() . 'sumMemSalesByDay AS s
WHERE date_id BETWEEN ? AND ?
GROUP BY s.card_no
HAVING SUM(CASE WHEN date_id BETWEEN ? AND ? THEN transCount ELSE 0 END) = 0
AND SUM(CASE WHEN date_id BETWEEN ? AND ? THEN transCount ELSE 0 END) >= ?
AND SUM(CASE WHEN date_id BETWEEN ? AND ? THEN total ELSE 0 END) >= ?
');
$args = array($active_period->start, $inactive_period->end, $inactive_period->start, $inactive_period->end, $active_period->start, $active_period->end, $rules->minVisits() + 1, $active_period->start, $active_period->end, $rules->minPurchases());
$activityR = $dbc->execute($activityP, $args);
$dev_targets = new DeveloperTargetsModel($dbc);
while ($w = $dbc->fetchRow($activityR)) {
/**
Enforce rules about account status
*/
$statusR = $dbc->execute($statusP, array($w['card_no']));
if (!$statusR || $dbc->numRows($statusR) == 0) {
continue;
}
$status = $dbc->fetchRow($statusR);
if ($rules->memberOnly() && $status['Type'] != 'PC') {
continue;
}
if (!$rules->includeStaff() && $status['staff']) {
continue;
}
$infoP = $dbc->execute($meminfoP, array($w['card_no']));
if (!$infoP || $dbc->numRows($infoP) == 0) {
continue;
}
echo $w['card_no'] . "\n";
$targets->reset();
$targets->card_no($w['card_no']);
$dev_targets->card_no($w['card_no']);
if (!$dev_targets->load()) {
echo "Adding " . $w['card_no'] . "\n";
$targets->addedDate(date('Y-m-d H:i:s'));
$targets->card_no($w['card_no']);
$targets->save();
}
}
}
public function run()
{
$settings = $this->config->PLUGIN_SETTINGS;
$dbc = FannieDB::get($settings['TargetedPromosDB']);
/**
Lookup developer coupon usage for yesterday
and update targets table
*/
$rules = new DeveloperRulesModel($dbc);
$all_rules = $rules->find('developerRuleID', true);
if (count($all_rules) > 0) {
$rules = $all_rules[0];
$couponUPC = $rules->couponUPC();
$prep = $dbc->prepare('
SELECT card_no,
SUM(quantity) AS qty,
MAX(tdate) AS tdate
FROM ' . $this->config->TRANS_DB . $dbc->sep() . 'dlog_90_view
WHERE ' . $dbc->datediff($dbc->curdate(), 'tdate') . ' <> 0
AND trans_type=\'T\'
AND trans_subtype=\'IC\'
AND upc=?
GROUP BY card_no');
echo $couponUPC . "\n";
$res = $dbc->execute($prep, array($couponUPC));
$updateP = $dbc->prepare('
UPDATE DeveloperTargets
SET redeemed = redeemed + ?,
lastRedeemDate=?
WHERE card_no=?');
while ($w = $dbc->fetchRow($res)) {
echo $w['card_no'] . "\n";
$dbc->execute($updateP, array($w['qty'], $w['tdate'], $w['card_no']));
}
}
/**
Lookup defector coupon usage for yesterday
and update targets table
*/
$rules = new DefectorRulesModel($dbc);
$all_rules = $rules->find('defectorRuleID', true);
if (count($all_rules) > 0) {
$rules = $all_rules[0];
$couponUPC = $rules->couponUPC();
$prep = $dbc->prepare('
SELECT card_no,
SUM(quantity) AS qty,
MAX(tdate) AS tdate
FROM ' . $this->config->TRANS_DB . $dbc->sep() . 'dlog_90_view
WHERE ' . $dbc->datediff($dbc->curdate(), 'tdate') . ' <> 0
AND trans_type=\'T\'
AND trans_subtype=\'IC\'
AND upc=?
GROUP BY card_no');
$res = $dbc->execute($prep, array($couponUPC));
$updateP = $dbc->prepare('
UPDATE DefectorTargets
SET redeemed = redeemed + ?
WHERE card_no=?');
while ($w = $dbc->fetchRow($res)) {
$dbc->execute($updateP, array($w['qty'], $w['card_no']));
}
}
}
