public function run() { global $FANNIE_PLUGIN_SETTINGS, $FANNIE_LANES; /* When $FannieTask->arguments exists: $oneLane = $lane[0]; */ $oneLane = isset($this->arguments[0]) ? $this->arguments[0] : 0; if (!preg_match('/^\\d+$/', $oneLane)) { $this->cronMsg("lane argument <{$oneLane}> must be an integer."); return; } if ($oneLane > count($FANNIE_LANES)) { echo $this->cronMsg("oneLane: {$oneLane} is more than the " . count($FANNIE_LANES) . " that are configured."); return; } set_time_limit(0); /* This was written with the idea that the tables of the plugin * might be named in plugin settings, * but at this point they aren't. * Also that it might sync tables for more than one plugin. */ $plugins = array('CoopCred' => array('CCredPrograms', 'CCredMemberships')); foreach ($plugins as $plugin => $tables) { if (FanniePlugin::isEnabled($plugin)) { foreach ($tables as $table) { //echo $this->cronMsg("Doing: $table"); if (isset($FANNIE_PLUGIN_SETTINGS["{$plugin}LaneDatabase"])) { $dbs = $FANNIE_PLUGIN_SETTINGS["{$plugin}Database"] . '|' . $FANNIE_PLUGIN_SETTINGS["{$plugin}LaneDatabase"]; $result = SyncLanesForPlugin::pushTable("{$table}", "plugin:{$dbs}", SyncLanesForPlugin::TRUNCATE_DESTINATION, $oneLane); echo $this->cronMsg($result['messages']); } else { echo $this->cronMsg("Lane Database for Plugin: {$plugin} is not assigned."); } } } else { echo $this->cronMsg("{$plugin} is not enabled."); } // each plugin } echo $this->cronMsg(basename(__FILE__) . " done."); }
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\n 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\n CASE WHEN t.trans_subtype in {$tlist}\n THEN p.programID\n ELSE q.programID END\n AS programID,\n card_no,\n CASE WHEN t.trans_subtype IN {$tlist}\n THEN -total ELSE 0 END\n AS charges,\n CASE WHEN t.department IN {$dlist}\n THEN total ELSE 0 END\n AS payments,\n t.tdate,\n t.trans_num\n FROM {$FANNIE_TRANS_DB}.dlog_15 t\n LEFT JOIN {$coopCredDB}.CCredPrograms p\n ON t.trans_subtype = p.tenderType\n LEFT JOIN {$coopCredDB}.CCredPrograms q\n ON t.department = q.paymentDepartment\n 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\n FROM {$coopCredDB}.CCredHistory \n WHERE tdate=? AND transNum=? AND cardNo=? AND programID=?"); /* Add a dlog_15 transaction to History */ $addS = $dbc->prepare("INSERT INTO {$coopCredDB}.CCredHistory\n (programID, cardNo, charges, payments, tdate, transNum)\n 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\n SELECT programID, cardNo, SUM(charges), SUM(payments),\n SUM(charges)-SUM(payments),\n NULL\n FROM CCredHistory\n 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\n LEFT JOIN CCredLiveBalance AS n\n ON m.programID=n.programID AND m.cardNo=n.cardNo\n SET m.creditBalance = n.balance"; if ($FANNIE_SERVER_DBMS == "MSSQL") { $balQ = "UPDATE CCredMemberships\n SET m.creditBalance = n.balance\n FROM CCredMemberships AS m\n LEFT JOIN CCredLiveBalance AS n\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. } }
public function run() { global $FANNIE_LANES; global $FANNIE_PLUGIN_LIST; global $FANNIE_PLUGIN_SETTINGS; if (!FanniePlugin::isEnabled($this->pluginName)) { echo $this->cronMsg("Plugin '{$this->pluginName}' is not enabled."); return False; } if (!array_key_exists("{$this->pluginName}Database", $FANNIE_PLUGIN_SETTINGS) || empty($FANNIE_PLUGIN_SETTINGS["{$this->pluginName}Database"])) { echo $this->cronMsg("Setting: '{$this->pluginName}Database' is not set."); return False; } $server_db = $FANNIE_PLUGIN_SETTINGS["{$this->pluginName}Database"]; $dbc = FannieDB::get($server_db); if ($dbc === False) { echo $this->cronMsg("Unable to connect to {$server_db}."); return False; } // get balances that changed today $balanceData = array(); $fetchQ = "SELECT programID, cardNo, balance\n FROM CCredMemCreditBalance\n WHERE mark=1"; //orig: $fetchQ = "SELECT CardNo, balance FROM memChargeBalance WHERE mark=1"; $fetchR = $dbc->query($fetchQ); if ($fetchR === False) { echo $this->cronMsg("Fatal: {$fetchQ}"); flush(); return; } // Make a list of updates. while ($fetchW = $dbc->fetch_row($fetchR)) { $key = $fetchW['programID'] . '|' . $fetchW['cardNo']; // The order of elements as needed in the UPDATE statement. $balanceData["{$key}"] = array($fetchW['balance'], $fetchW['programID'], $fetchW['cardNo']); } /* Debug echo $this->cronMsg("Balance updates to do: " . count($balanceData)); */ /* Get CCredMemberships that have changed today. * Does not handle Members added today. */ $memberData = array(); $memberQ = "SELECT programID, cardNo, creditLimit, maxCreditBalance,\n creditOK, inputOK, transferOK, modified\n FROM CCredMemberships\n WHERE date(modified) = date(" . $dbc->now() . ")"; $memberR = $dbc->query($memberQ); if ($memberR === False) { echo $this->cronMsg("Failed: {$memberQ}"); $errors = True; } else { while ($memberW = $dbc->fetch_row($memberR)) { $key = $memberW['programID'] . '|' . $memberW['cardNo']; // The order of elements as needed in the UPDATE statement. $memberData["{$key}"] = array($memberW['creditLimit'], $memberW['maxCreditBalance'], $memberW['creditOK'], $memberW['inputOK'], $memberW['transferOK'], $memberW['modified'], $memberW['programID'], $memberW['cardNo']); } } /* Debug echo $this->cronMsg("Member updates to do: " . count($memberData)); */ /* Get CCredPrograms that have changed today. * Does not handle Programs added today. */ $programData = array(); $programQ = "SELECT programID, active, startDate, endDate,\n creditOK, inputOK, transferOK, maxCreditBalance,\n modified\n FROM CCredPrograms\n WHERE date(modified) = date(" . $dbc->now() . ")"; $programR = $dbc->query($programQ); if ($programR === False) { echo $this->cronMsg("Failed: {$programQ}"); $errors = True; } else { while ($programW = $dbc->fetch_row($programR)) { $key = $programW['programID']; // The order of elements as needed in the UPDATE statement. $programData["{$key}"] = array($programW['active'], $programW['startDate'], $programW['endDate'], $programW['creditOK'], $programW['inputOK'], $programW['transferOK'], $programW['maxCreditBalance'], $programW['modified'], $programW['programID']); } } /* Debug echo $this->cronMsg("Program updates to do: " . count($programData)); */ $errors = False; // connect to each lane and update balances foreach ($FANNIE_LANES as $lane) { $dbL = new SQLManager($lane['host'], $lane['type'], $lane['op'], $lane['user'], $lane['pw']); if ($dbL === False) { echo $this->cronMsg("Can't connect to lane: " . $lane['host'] . " db: {$lane['op']} ."); $errors = True; continue; } /* Find the name of the CoopCred db on the lane. * Why is PluginList in opdata.lane_config but PluginSettings isn't? * opdata.parameters has PluginList, CoopCredLaneDatabase */ $coopCredEnabled = 0; $laneDB = ""; $laneQ = "SELECT * FROM parameters\n WHERE param_key IN ('PluginList', 'CoopCredLaneDatabase')\n ORDER BY param_key, store_id, lane_id"; $laneR = $dbL->query($laneQ); if ($laneR === False) { echo $this->cronMsg("Failed query on: " . $lane['host'] . " query: {$query}"); $errors = True; continue; } /* Local values will override global. */ while ($laneP = $dbL->fetch_row($laneR)) { if ($laneP['param_key'] == 'PluginList') { $paramList = explode(',', $laneP['param_value']); if (in_array($this->pluginName, $paramList)) { $coopCredEnabled = 1; } } if ($laneP['param_key'] == 'CoopCredLaneDatabase') { $laneDB = $laneP['param_value']; } } if (!$coopCredEnabled) { echo $this->cronMsg("{$this->pluginName} is not enabled on: " . $lane['host']); continue; } if ($laneDB == '') { echo $this->cronMsg("No CoopCredDatabase named on: " . $lane['host']); continue; } // Change db on connection to the ccred db. $ccDB = $dbL->addConnection($lane['host'], $lane['type'], $laneDB, $lane['user'], $lane['pw']); if ($ccDB === False) { echo $this->cronMsg("Can't add connection to {$laneDB} on: " . $lane['host']); $errors = True; continue; } $dbL->default_db = $laneDB; foreach ($balanceData as $dt) { $upQ = "UPDATE CCredMemberships\n SET creditBalance=?,\n modified=" . $dbc->now() . ",\n modifiedBy=9998\n WHERE programID=? AND cardNo=?"; $upS = $dbL->prepare($upQ); $upR = $dbL->execute($upS, $dt); if ($upR === False) { echo $this->cronMsg("Balance update failed: member: {$dt[2]} " . "in program {$dt[1]} on lane: {$lane['host']}"); $errors = True; } /* Debug else { echo $this->cronMsg("Balance update OK: member: {$dt[2]} in program {$dt[1]} on lane: {$lane['host']}"); } */ } foreach ($memberData as $dt) { $upQ = "UPDATE CCredMemberships\n SET creditLimit=?,\n maxCreditBalance=?,\n creditOK=?,\n inputOK=?,\n transferOK=?,\n modified=?,\n modifiedBy=9999\n WHERE programID=? AND cardNo=?"; $upS = $dbL->prepare($upQ); $upR = $dbL->execute($upS, $dt); if ($upR === False) { echo $this->cronMsg("Member update failed: member: {$dt[7]} in program {$dt[6]} on lane: {$lane['host']}"); $errors = True; } /* Debug else { echo $this->cronMsg("Member update OK: member: {$dt[7]} ". "in program {$dt[6]} on lane: {$lane['host']}"); } */ } foreach ($programData as $dt) { $upQ = "UPDATE CCredPrograms\n SET active=?,\n startDate=?,\n endDate=?,\n creditOK=?,\n inputOK=?,\n transferOK=?,\n maxCreditBalance=?,\n modified=?,\n modifiedBy=9999\n WHERE programID=?"; $upS = $dbL->prepare($upQ); $upR = $dbL->execute($upS, $dt); if ($upR === False) { echo $this->cronMsg("Program update failed: program {$dt[8]} " . "on lane: {$lane['host']}"); $errors = True; } /* Debug else { echo $this->cronMsg("Program update OK: program {$dt[8]} ". "on lane: {$lane['host']}"); } */ } // each lane } if ($errors) { echo $this->cronMsg("There was an error pushing balances to the lanes."); flush(); } else { /* Debug echo $this->cronMsg("All OK."); */ $noop = 0; } // /run }