fannie/modules/plugins2.0/CoopCred/tasks/CoopCredHistoryTask.php
<?php
/*******************************************************************************
Copyright 2013 Whole Foods Co-op
Copyright 2014 West End Food Co-op, Toronto
This file is part of Fannie.
Fannie is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
Fannie is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
in the file license.txt along with IT CORE; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*********************************************************************************/
use COREPOS\Fannie\API\FanniePlugin;
class CoopCredHistoryTask extends FannieTask
{
public $name = 'Coop Cred History';
public $pluginName = 'CoopCred';
/* Keep lines to 60 chars for the cron manager popup window.
* -------------- 60 edge-v */
public $description = '
- Extracts Coop Cred charge and payment transactions in the
previous 15 days and adds new ones to dedicated history
tables.
- Updates the history summary table.
- Can be safely run repeatedly without repeating
transactions.
- Normally run after rotating dtransactions data
(Transation Archiving/nightly.dtrans.php) and before
syncing CCredMemberships to lanes.
- Updates Fannie CCredMemberships creditBalances to current,
"live" values.
';
public $default_schedule = array(
'min' => 0,
'hour' => 0,
'day' => '1',
'month' => '1',
'weekday' => '*',
);
public function __construct() {
$this->description = $this->name . "\n" . $this->description;
//parent::__construct();
}
public function run()
{
global $FANNIE_TRANS_DB, $FANNIE_SERVER_DBMS;
global $FANNIE_PLUGIN_LIST, $FANNIE_PLUGIN_SETTINGS;
if (!FanniePlugin::isEnabled($this->pluginName)) {
echo $this->cronMsg("Plugin '{$this->pluginName}' is not enabled.");
return False;
}
if (!array_key_exists('CoopCredDatabase', $FANNIE_PLUGIN_SETTINGS) ||
empty($FANNIE_PLUGIN_SETTINGS['CoopCredDatabase'])) {
echo $this->cronMsg("No Coop Cred Database is named");
return False;
}
$coopCredDB = $FANNIE_PLUGIN_SETTINGS['CoopCredDatabase'];
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$dbc = FannieDB::get($coopCredDB);
if ($dbc === False || !is_object($dbc)) {
echo $this->cronMsg("Failed connecting to CoopCredDatabase: " .
$coopCredDB);
return False;
}
$programQ = "SELECT programID, paymentDepartment, tenderType
FROM {$coopCredDB}.CCredPrograms";
$programS = $dbc->prepare($programQ);
if ($programS === False) {
echo $this->cronMsg("prepare() failed: $programQ");
return False;
}
$args = array();
$programR = $dbc->execute($programS,$args);
if ($programR === False) {
echo $this->cronMsg("execute() failed: $programQ: " . implode('::',$args));
return False;
}
if ($dbc->numRows($programR) == 0) {
echo $this->cronMsg("No Programs defined.");
return False;
}
/* Build the argument sets for the transaction query. */
$dlist = '';
$tlist = '';
$sep = '';
$case_args = array();
$where_args = array();
//c: t,t,d
//w: d,t
$inc=$dbc->numRows($programR);
// Empty argument placeholders.
for ($cr=0 ; $cr<($inc*3);$cr++) {
$case_args[] = 0;
}
for ($wr=0 ; $wr<($inc*2);$wr++) {
$where_args[] = 0;
}
// Actual arguments.
$r=0;
$cr=0;
$wr=0;
while($programW = $dbc->fetch_row($programR)) {
$tlist .= "{$sep}?";
$dlist .= "{$sep}?";
$sep = ',';
$cr=$r;
$case_args[$cr] = $programW['tenderType'];
$cr += $inc;
$case_args[$cr] = $programW['tenderType'];
$cr += $inc;
$case_args[$cr] = $programW['paymentDepartment'];
//
$wr=$r;
$where_args[$wr] = $programW['paymentDepartment'];
$wr += $inc;
$where_args[$wr] = $programW['tenderType'];
$r++;
}
if ($dlist == '') {
echo $this->cronMsg("No payment departments defined.");
return False;
}
$dlist = "({$dlist})";
if ($tlist == '') {
echo $this->cronMsg("No tenders defined.");
return False;
}
$tlist = "({$tlist})";
$lookupQ = "SELECT
CASE WHEN t.trans_subtype in $tlist
THEN p.programID
ELSE q.programID END
AS programID,
card_no,
CASE WHEN t.trans_subtype IN $tlist
THEN -total ELSE 0 END
AS charges,
CASE WHEN t.department IN $dlist
THEN total ELSE 0 END
AS payments,
t.tdate,
t.trans_num
FROM {$FANNIE_TRANS_DB}.dlog_15 t
LEFT JOIN {$coopCredDB}.CCredPrograms p
ON t.trans_subtype = p.tenderType
LEFT JOIN {$coopCredDB}.CCredPrograms q
ON t.department = q.paymentDepartment
WHERE t.department IN $dlist OR t.trans_subtype IN $tlist";
$lookupS = $dbc->prepare($lookupQ);
$args = array();
foreach($case_args as $ca) {
$args[] = $ca;
}
foreach($where_args as $wa) {
$args[] = $wa;
}
$lookupR = $dbc->execute($lookupS, $args);
/* Statements used in each iteration of seeing whether an
* item should be added to the History table.
*/
/* Look for one transaction in History */
$checkS = $dbc->prepare("SELECT charges, payments
FROM {$coopCredDB}.CCredHistory
WHERE tdate=? AND transNum=? AND cardNo=? AND programID=?");
/* Add a dlog_15 transaction to History */
$addS = $dbc->prepare("INSERT INTO {$coopCredDB}.CCredHistory
(programID, cardNo, charges, payments, tdate, transNum)
VALUES (?, ?, ?, ?, ?, ?)");
// foreach dlog_15 item
$added = 0;
$skipped = 0;
while($lookupW = $dbc->fetch_row($lookupR)) {
// check whether dlog_15 transaction is known in CCredHistory
$checkR = $dbc->execute($checkS,
array($lookupW['tdate'],
$lookupW['trans_num'],
$lookupW['card_no'],
$lookupW['programID']
));
/* The transaction is there. Check further for amount match.
* When would there be more than one History record? Tender and Change.
* Allows for both charge and payment in same item?
* Allows for multiple same-type tenders in a item?
* Allows for multiple tender types in a item?
*/
if ($dbc->numRows($checkR) != 0) {
$exists = False;
while($checkW = $dbc->fetch_row($checkR)) {
if ($checkW['charges'] == $lookupW['charges'] &&
$checkW['payments'] == $lookupW['payments']) {
// Amount matches. Prevent adding again.
$exists = true;
break;
}
}
if ($exists) {
$skipped++;
continue;
}
}
// Not already in Coop Cred History so add it.
$try = $addR = $dbc->execute($addS,
array(
$lookupW['programID'],
$lookupW['card_no'],
$lookupW['charges'],
$lookupW['payments'],
$lookupW['tdate'],
$lookupW['trans_num']
));
/* Debug.
*/
$added++;
if ($try === False) {
echo $this->cronMsg('Error adding Coop Cred History entry >'.
$lookupW['programID']. '< '.
$lookupW['tdate']. ' '.
$lookupW['trans_num']);
}
}
/* Debug
*/
echo $this->cronMsg("Done adding: $added to CCredHistory skipped: $skipped ");
/* Rebuild Coop Cred history sum table
*/
$dbc->query("TRUNCATE TABLE CCredHistorySum");
$query = "INSERT INTO CCredHistorySum
SELECT programID, cardNo, SUM(charges), SUM(payments),
SUM(charges)-SUM(payments),
NULL
FROM CCredHistory
GROUP BY programID, cardNo";
$try = $dbc->query($query);
if ($try === False) {
echo $this->cronMsg('Error rebuilding CCredHistorySum table');
}
/* Debug
*/
echo $this->cronMsg('Done rebuilding CCredHistorySum table');
/* Update Member creditBalance field
*/
$balQ = "UPDATE CCredMemberships AS m
LEFT JOIN CCredLiveBalance AS n
ON m.programID=n.programID AND m.cardNo=n.cardNo
SET m.creditBalance = n.balance";
if ($FANNIE_SERVER_DBMS == "MSSQL"){
$balQ = "UPDATE CCredMemberships
SET m.creditBalance = n.balance
FROM CCredMemberships AS m
LEFT JOIN CCredLiveBalance AS n
ON m.programID=n.programID AND m.cardNo=n.cardNo";
}
$try = $dbc->query($balQ);
if ($try === False) {
echo $this->cronMsg('Error reloading Member balances');
}
/* Debug
*/
echo $this->cronMsg('Done updating CCredMemberships balances');
echo $this->cronMsg('Finished every-day tasks.');
/* turnover view/cache base tables for WFC end-of-month reports */
if (date('j') == 1) {
if ($dbc->table_exists('CCredHistoryBackup')) {
$dbc->query("TRUNCATE TABLE CCredHistoryBackup");
$dbc->query("INSERT INTO CCredHistoryBackup SELECT * FROM CCredHistory");
}
echo $this->cronMsg('First of month: Done rebuilding CCredHistoryBackup');
// First-of-month operations.
}
}
// CoopCredHistoryTask class
}