homeless.specialorder.php
Check for SOs w/o a department
and spam out email until someone fixes it
*/
include dirname(__FILE__) . '/../config.php';
if (!class_exists('FannieAPI')) {
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.php';
}
if (!function_exists('cron_msg')) {
include $FANNIE_ROOT . 'src/cron_msg.php';
}
set_time_limit(0);
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$OP = $FANNIE_OP_DB . ($FANNIE_SERVER_DBMS == "MSSQL" ? 'dbo.' : '.');
$q = "\nselect s.order_id,description,datetime,\ncase when c.lastName ='' then b.LastName else c.lastName END as name\nfrom PendingSpecialOrder\nas s left join SpecialOrders as c on s.order_id=c.specialOrderID\nleft join {$OP}custdata as b on s.card_no=b.CardNo and s.voided=b.personNum\nwhere s.order_id in (\nselect p.order_id from PendingSpecialOrder as p\nleft join SpecialOrders as n\non p.order_id=n.specialOrderID\nwhere notes LIKE ''\ngroup by p.order_id\nhaving max(department)=0 and max(noteSuperID)=0\nand max(trans_id) > 0\n)\nand trans_id > 0\norder by datetime\n";
$r = $sql->query($q);
if ($sql->num_rows($r) > 0) {
$msg_body = "Homeless orders detected!\n\n";
while ($w = $sql->fetch_row($r)) {
$msg_body .= $w['datetime'] . ' - ' . (empty($w['name']) ? '(no name)' : $w['name']) . ' - ' . $w['description'] . "\n";
$msg_body .= "http://key" . $FANNIE_URL . "ordering/view.php?orderID=" . $w['order_id'] . "\n\n";
}
$msg_body .= "These messages will be sent daily until orders get departments\n";
$msg_body .= "or orders are closed\n";
$to = "buyers, michael";
$subject = "Incomplete SO(s)";
mail($to, $subject, $msg_body);
}
}
}
if ($success) {
echo cron_msg("Price change batches run successfully");
} else {
echo cron_msg("Error running price change batches");
}
// log updates to prodUpdate table
$success = true;
$likeP = $sql->prepare('SELECT upc FROM upcLike WHERE likeCode=?');
$batchQ = 'SELECT upc FROM batchList as l LEFT JOIN batches AS b
ON l.batchID=b.batchID WHERE b.discounttype=0
AND ' . $sql->datediff($sql->now(), 'b.startDate') . ' = 0';
$batchR = $sql->query($batchQ);
$prodUpdate = new ProdUpdateModel($sql);
while ($batchW = $sql->fetch_row($batchR)) {
$upcs = array();
$upc = $batchW['upc'];
// unpack likecodes to UPCs
if (substr($upc, 0, 2) == 'LC') {
$likeR = $sql->execute($likeP, array(substr($upc, 2)));
while ($likeW = $sql->fetch_row($likeR)) {
$upcs[] = $likeW['upc'];
}
} else {
$upcs[] = $upc;
}
foreach ($upcs as $item) {
$prodUpdate->reset();
$prodUpdate->upc($item);
$logged = $prodUpdate->logUpdate(ProdUpdateModel::UPDATE_PC_BATCH, 1001);
*********************************************************************************/
/* HELP
monthly.nabs.php
Make AR payments on nabs accounts
to clear end-of-month balance
probably not relevant for anyone else
*/
include dirname(__FILE__) . '/../config.php';
if (!class_exists('FannieAPI')) {
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.php';
}
if (!function_exists('cron_msg')) {
include $FANNIE_ROOT . 'src/cron_msg.php';
}
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$insQ = "INSERT INTO dtransactions SELECT * FROM nabsAdjustView";
$insR = $sql->query($insQ);
// fix trans_no values
$tn = 1;
$fixQ = "SELECT card_no FROM dtransactions WHERE register_no=20\n AND emp_no=1001";
$fixR = $sql->query($fixQ);
while ($fixW = $sql->fetch_row($fixR)) {
$transQ = "UPDATE dtransactions SET trans_no={$tn}\n WHERE register_no=20 and emp_no=1001\n AND card_no=" . $fixW['card_no'];
$transR = $sql->query($transQ);
$tn++;
}
$lastDay = date("M d, Y", $tstamp) . " 11:59PM";
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$dataQ = "SELECT d.upc as upc, p.description as description,\n sum(CASE WHEN d.quantity <> d.ItemQtty AND d.ItemQtty <> 0 THEN d.quantity*d.ItemQtty ELSE d.quantity END) as quantity,\n sum(d.total) as dollars,\n '{$lastDay}' as lastDay\n FROM dlog_90_view as d inner join \n {$FANNIE_OP_DB}.dbo.products as p\n on d.upc=p.upc\n WHERE p.scale = 0\n AND d.upc > '0000000999999'\n AND datepart(ww,tdate) = {$week}\n group by d.upc, p.description";
// mysql handles week # differently by default
if (strstr($FANNIE_SERVER_DBMS, "MYSQL")) {
$dataQ = "SELECT d.upc as upc, p.description as description,\n sum(CASE WHEN d.quantity <> d.ItemQtty AND d.ItemQtty <> 0 THEN d.quantity*d.ItemQtty ELSE d.quantity END) as quantity,\n sum(d.total) as dollars,\n '{$lastDay}' as lastDay\n FROM dlog_90_view as d inner join \n {$FANNIE_OP_DB}.products as p\n on d.upc=p.upc\n WHERE p.scale = 0\n AND d.upc > '0000000999999'\n AND week(tdate) = " . ($week - 1) . "\n group by d.upc, p.description";
}
/* SPINS numbering is non-standard in 2012
so week is offset by one in the filename
this may change back next year
*/
$filename = date('mdY.csv', $tstamp);
$outfile = sys_get_temp_dir() . "/" . $filename;
$fp = fopen($outfile, "w");
$dataR = $sql->query($dataQ);
while ($row = $sql->fetch_row($dataR)) {
for ($i = 0; $i < 4; $i++) {
fwrite($fp, "\"" . $row[$i] . "\",");
}
fwrite($fp, "\"" . $row[4] . "\"\n");
}
fclose($fp);
$conn_id = ftp_connect($SPINS_SERVER);
$login_id = ftp_login($conn_id, $SPINS_USER, $SPINS_PW);
if (!$conn_id or !$login_id) {
echo "FTP connect failed!";
}
ftp_chdir($conn_id, "data");
ftp_pasv($conn_id, True);
$upload = ftp_put($conn_id, $filename, $outfile, FTP_ASCII);
if (!$upload) {
if (!class_exists('FannieAPI')) {
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.php';
}
if (!function_exists('cron_msg')) {
include $FANNIE_ROOT . 'src/cron_msg.php';
}
set_time_limit(0);
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$TRANS = $FANNIE_SERVER_DBMS == "MSSQL" ? $FANNIE_TRANS_DB . ".dbo." : $FANNIE_TRANS_DB . ".";
$sql->query("TRUNCATE TABLE memcoupon");
$insQ = "INSERT INTO memcoupon\n select card_no, tdate, total,trans_num \n from {$TRANS}dlog_90_view\n where (trans_subtype = 'MA' or upc like '%MAD Coupon%') \n and ceiling(month(tdate)/3.0) = ceiling(month(" . $sql->now() . ")/3.0)\n and year(tdate) = year(" . $sql->now() . ")\n and total > -2.52 and total < 2.52\n order by card_no";
$insR = $sql->query($insQ);
$resetQ = "update custdata set memCoupons=1 where Type='PC'";
$resetR = $sql->query($resetQ);
$bl = "CONCAT( CONVERT(CardNo,char), ' ', LastName )";
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$bl = "RTRIM(CardNo) + ' ' + RTRIM(LastName)";
}
$resetQ = "update custdata set memCoupons=0,blueLine={$bl} where Type<>'PC'";
$resetR = $sql->query($resetQ);
$usedQ = "SELECT cardno FROM memcoupon GROUP BY cardno HAVING SUM(total) <> 0";
$usedR = $sql->query($usedQ);
while ($usedW = $sql->fetch_row($usedR)) {
$upR = $sql->query("UPDATE custdata SET memCoupons=0 WHERE CardNo=" . $usedW['cardno']);
}
$bl = "CONCAT( CONVERT(CardNo,char), ' ', LastName, ' Coup(', CONVERT(memCoupons,char), ')' )";
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$bl = "RTRIM(CardNo) + ' ' + RTRIM(LastName) + ' Coup(' + CONVERT(varchar,memCoupons) + ')'";
}
$blQ = "update custdata\n SET blueLine = {$bl}\n WHERE Type = 'PC'";
$blR = $sql->query($blQ);
$which_connection = $this->default_db;
return $result_object->FetchField($index);
}
*/
// Little tests of is4c connection.
if (0) {
$selectIs4c = "SELECT CardNo, LastName from custdata LIMIT 5;";
$customers = $dbConn2->query("{$selectIs4c}");
// ->errno probably doesn't exist in SQLManager
if ($dbConn2->errno) {
$message = sprintf("Select failed: %s\n", $dbConn->error);
dieHere($message);
}
// Quick test.
echo "IS4C Numbered\n";
while ($row = $dbConn2->fetch_row($customers)) {
// Why does $row contain each field twice?
// Because $row is a BOTH list and hash; use hash syntax.
// array_keys gets both names and numbers.
//$flds = array_keys($row);
$flds = getNameKeys($row);
$lineOut = implode("\t", $flds) . "\n";
echo $lineOut;
// echo "count:", count($row), "\n";
$vals = getNameValues($row);
$lineOut = implode("\t", $vals) . "\n";
echo $lineOut;
$lineOut = implode("\t", array($row[CardNo], $row[LastName])) . "\n";
echo $lineOut;
}
die("IS4C OK, bailing ...");
*/
if (empty($tempMemberLastName)) {
$tempMemberLastName = 'NEW MEMBER';
}
$i_selectMembers = "SELECT CardNo, LastChange\nFROM custdata\nWHERE CardNo between {$is4cMin} AND {$is4cMax}\n AND (LastName IS NULL OR LastName != '{$tempMemberLastName}')\n AND NumberOfChecks != 9";
$i_members = $dbConn2->query("{$i_selectMembers}");
if ($dbConn2->errno) {
$message = sprintf("Select failed: %s\n", $dbConn->error);
dieHere($message, $dieMail);
}
if (!$i_members) {
$msg = sprintf("Failed on: %s", $i_selectMembers);
dieHere("{$msg}", $dieMail);
}
$lastCardNo = "";
while ($i_row = $dbConn2->fetch_row($i_members)) {
if ($i_row[CardNo] == $lastCardNo) {
continue;
}
$allMembers[] = sprintf("%05d|%s|I", $i_row[CardNo], $i_row[LastChange]);
$lastCardNo = $i_row[CardNo];
}
sort($allMembers);
/* This file isn't used except during development.
*/
foreach ($allMembers as $item) {
fwrite($outer, "{$item}\n");
}
if (count($problems) > 0) {
fwrite($outer, implode("\n", $problems));
echo implode("\n", $problems), "\n";
/**
Get an available dtransactions.trans_no value
@param $connection [SQLManager] database connection
@param $emp_no [int] employee number
@param $register_no [int] register number
@return [int] trans_no
*/
public static function getTransNo(SQLManager $connection, $emp_no = false, $register_no = false)
{
$config = FannieConfig::factory();
if ($emp_no === false) {
$emp_no = $config->get('EMP_NO');
}
if ($register_no === false) {
$register_no = $config->get('REGISTER_NO');
}
$prep = $connection->prepare('
SELECT MAX(trans_no) AS trans
FROM ' . $config->get('TRANS_DB') . $connection->sep() . 'dtransactions
WHERE emp_no=?
AND register_no=?');
$result = $connection->execute($prep, array($emp_no, $register_no));
if (!$result || $connection->num_rows($result) == 0) {
return 1;
} else {
$row = $connection->fetch_row($result);
if ($row['trans'] == '') {
return 1;
} else {
return $row['trans'] + 1;
}
}
}
*
* 18Oct12 EL Keep this comment block from appearing in the Help popup.
* Reformat first SQL statement.
* 17Jun12 EL Fix Help to make it appropriate to this program.
* Was a copy of reactivate.equity.php.
*/
set_time_limit(0);
ini_set('memory_limit', '256M');
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$TRANS = $FANNIE_TRANS_DB . ($FANNIE_SERVER_DBMS == "MSSQL" ? 'dbo.' : '.');
$custdata = $sql->table_definition('custdata');
$meminfoQ = "UPDATE meminfo AS m LEFT JOIN\n custdata AS c ON m.card_no=c.CardNo\n LEFT JOIN {$TRANS}ar_live_balance AS s\n ON c.cardno=s.card_no LEFT JOIN suspensions AS p\n ON c.cardno=p.cardno LEFT JOIN {$TRANS}AR_EOM_Summary AS a\n ON m.card_no=a.cardno\n SET m.ads_OK=p.mailflag\n WHERE c.Type = 'INACT' and p.reasoncode IN (1)\n AND s.balance < a.twoMonthBalance";
$sql->query($meminfoQ);
$custQ = "UPDATE custdata AS c LEFT JOIN {$TRANS}ar_live_balance AS s\n ON c.CardNo=s.card_no LEFT JOIN suspensions AS p\n ON c.CardNo=p.cardno LEFT JOIN {$TRANS}AR_EOM_Summary AS a\n ON c.CardNo=a.cardno\n SET c.Discount=p.discount,c.MemDiscountLimit=p.chargelimit,\n c.ChargeLimit=p.chargelimit,\n c.memType=p.memtype1,c.Type=p.memtype2,chargeOk=1\n WHERE c.Type = 'INACT' and p.reasoncode IN (1)\n AND s.balance < a.twoMonthBalance";
if (!isset($custdata['ChargeLimit'])) {
$custQ = str_replace('c.ChargeLimit=p.chargelimit,', '', $custQ);
}
$sql->query($custQ);
$histQ = "insert into suspension_history\n select 'AR paid'," . $sql->now() . ",\n 'Account reactivated',c.CardNo,0 from\n suspensions as s left join\n custdata as c on s.cardno=c.CardNo\n and c.personNum=1\n where c.Type not in ('INACT','INACT2') and s.type='I'";
$sql->query($histQ);
$clearQ = "select c.CardNo from\n suspensions as s left join\n custdata as c on s.cardno=c.CardNo\n where c.Type not in ('INACT','INACT2') and s.type='I'\n AND c.personNum=1";
$clearR = $sql->query($clearQ);
$cns = "(";
while ($clearW = $sql->fetch_row($clearR)) {
$cns .= $clearW[0] . ",";
}
$cns = rtrim($cns, ",") . ")";
if (strlen($cns) > 2) {
$delQ = "DELETE FROM suspensions WHERE cardno IN {$cns}";
$delR = $sql->query($delQ);
}
// What is the DB function to do this? FannieDB::get()?
// We're not in the right environment for that.
$dbc = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
/* 'i9el. Update likecodes */
/* update the item's likecode if specified
also update other items in the likecode
if the appropriate box isn't checked */
if (isset($_REQUEST['likeCode']) && $_REQUEST['likeCode'] != -1) {
$dbc->query("DELETE FROM upcLike WHERE upc='{$upc}'");
$lcQ = "INSERT INTO upcLike (upc,likeCode) VALUES ('{$upc}',{$_REQUEST['likeCode']})";
$dbc->query($lcQ);
if (!isset($_REQUEST['update'])) {
$upcsQ = "SELECT upc FROM upcLike WHERE likeCode={$_REQUEST['likeCode']} AND upc <> '{$upc}'";
$upcsR = $dbc->query($upcsQ);
unset($up_array['description']);
while ($upcsW = $dbc->fetch_row($upcsR)) {
$dbc->smart_update('products', $up_array, "upc='{$upcsW['0']}' AND store_id={$FANNIE_STORE_ID}");
updateProductAllLanes($upcsW[0]);
}
}
} elseif (isset($_REQUEST['likeCode']) && $_REQUEST['likeCode'] == -1) {
$dbc->query("DELETE FROM upcLike WHERE upc='{$upc}'");
}
/* 11. Display the post-update values and an input for the next edit. */
/* Display some of the post-update values and an input for the next edit.
* The page contains form elements but there is no submit for the them.
* The record-select input is also displayed in a proper form with a submit.
*/
$deptQ = "SELECT dept_no, dept_name FROM departments ORDER BY dept_no";
$deptR = $dbc->query($deptQ);
$row = $dbc->fetch_array($deptR);
}
include '../../config.php';
if (!class_exists('FannieAPI')) {
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.php';
}
set_time_limit(0);
ini_set('memory_limit', '256M');
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$upc = null;
$prevDept = null;
$q = "select u.upc,u.modified,dept,user from prodUpdate\nas u inner join products as p on p.upc=u.upc\norder by u.upc,u.modified";
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$q = str_replace("user", "[user]", $q);
}
$r = $sql->query($q);
while ($w = $sql->fetch_row($r)) {
if ($upc === null || $upc != $w['upc']) {
// next item, get previous
// date and price from compressed
// history if available
$upc = $w['upc'];
$prevDept = null;
$prevDate = null;
$chkR = $sql->query("SELECT modified,dept_ID FROM\n prodDepartmentHistory WHERE upc='{$upc}'\n ORDER BY modified DESC");
if ($sql->num_rows($chkR) > 0) {
$chk = $sql->fetch_row($chkR);
$prevDate = $chk['modified'];
$prevDept = $chk['dept_ID'];
}
}
if ($prevDept != $w['dept']) {
}
if (!isset($FANNIE_LANES) || !is_array($FANNIE_LANES)) {
$FANNIE_LANES = array();
}
set_time_limit(0);
ini_set('memory_limit', '256M');
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
// get balances that changed today
$data = array();
$fetchQ = "SELECT CardNo, balance FROM memChargeBalance WHERE mark=1";
$fetchR = $sql->query($fetchQ);
if ($fetchR === False) {
echo cron_msg("Failed: {$fetchQ}");
return;
}
while ($fetchW = $sql->fetch_row($fetchR)) {
$data[$fetchW['CardNo']] = $fetchW['balance'];
}
$errors = False;
// connect to each lane and update balances
foreach ($FANNIE_LANES as $lane) {
$db = new SQLManager($lane['host'], $lane['type'], $lane['op'], $lane['user'], $lane['pw']);
if ($db === False) {
echo cron_msg("Can't connect to lane: " . $lane['host']);
$errors = True;
continue;
}
foreach ($data as $cn => $bal) {
$upQ = sprintf("UPDATE custdata SET Balance=%.2f WHERE CardNo=%d", $bal, $cn);
$rslt = $db->query($upQ);
if ($rslt === False) {
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
}
if (!function_exists('cron_msg')) {
include $FANNIE_ROOT . 'src/cron_msg.php';
}
set_time_limit(0);
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$cols = $sql->table_definition('dtransactions');
if (isset($cols['date_id'])) {
$sql->query("UPDATE dtransactions SET date_id=DATE_FORMAT(datetime,'%Y%m%d')");
}
/* Find date(s) in dtransactions */
$datesR = $sql->query('SELECT YEAR(datetime) AS year, MONTH(datetime) as month, DAY(datetime) as day
FROM dtransactions
GROUP BY YEAR(datetime), MONTH(datetime), DAY(datetime)
ORDER BY YEAR(datetime), MONTH(datetime), DAY(datetime)');
$dates = array();
while ($datesW = $sql->fetch_row($datesR)) {
$dates[] = sprintf('%d-%02d-%02d', $datesW['year'], $datesW['month'], $datesW['day']);
}
$UPDATED_DLOG_SCHEMA = false;
$table_def = $sql->table_definition('dlog');
if (isset($table_def['description'])) {
// most likely
$UPDATED_DLOG_SCHEMA = true;
}
/* Load dtransactions into the archive, trim to 90 days */
$chkP = $sql->prepare("INSERT INTO transarchive SELECT * FROM dtransactions WHERE " . $sql->datediff('datetime', '?') . '= 0');
$chk1 = false;
foreach ($dates as $date) {
$chk1 = $sql->execute($chkP, array($date));
}
$chk2 = $sql->query("DELETE FROM transarchive WHERE " . $sql->datediff($sql->now(), 'datetime') . " > 92");
if ($file == "." || $file == "..") {
continue;
}
if (!is_file($cachepath . $file)) {
continue;
}
unlink($cachepath . $file);
}
closedir($dh);
}
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
// auto-close called/waiting after 30 days
$subquery = "select p.order_id from PendingSpecialOrder as p\n left join SpecialOrders as s\n on p.order_id=s.specialOrderID\n where p.trans_id=0 and s.statusFlag=1\n and " . $sql->datediff($sql->now(), 'datetime') . " > 30";
$cwIDs = "(";
$r = $sql->query($subquery);
while ($w = $sql->fetch_row($r)) {
$cwIDs .= $w['order_id'] . ",";
}
$cwIDs = rtrim($cwIDs, ",") . ")";
if (strlen($cwIDs) > 2) {
// transfer to completed orders
$copyQ = "INSERT INTO CompleteSpecialOrder\n SELECT p.* FROM PendingSpecialOrder AS p\n WHERE p.order_id IN {$cwIDs}";
// make note in history table
$historyQ = "INSERT INTO SpecialOrderHistory\n (order_id, entry_date, entry_type, entry_value)\n SELECT p.order_id,\n " . $sql->now() . ",\n 'AUTOCLOSE',\n 'Call/Waiting 30'\n FROM PendingSpecialOrder AS p\n WHERE p.order_id IN {$cwIDs}\n GROUP BY p.order_id";
$sql->query($historyQ);
// clear from pending
$sql->query($copyQ);
$delQ = "DELETE FROM PendingSpecialOrder\n WHERE order_id IN {$cwIDs}";
$sql->query($delQ);
}
// end auto-close
/**
Fetch data for the specified report
@param [string] $report_class_name name of report
@param [FannieConfig] $config current configuration
@param [SQLManager] $connection database connection
@return [array] report records or [boolean] false
if this source cannot handle the request
*/
public function fetchReportData($report_class_name, \FannieConfig $config, \SQLManager $connection)
{
$date1 = \FormLib::get_form_value('date1', date('Y-m-d'));
if ($date1 == date('Y-m-d')) {
// warehouse cannot handle current day requests
return false;
}
$originalDB = $connection->defaultDatabase();
$plugin_settings = $config->get('PLUGIN_SETTINGS');
$connection->selectDB($plugin_settings['WarehouseDatabase']);
$args = array($this->dateToID($date1));
$reconciliation = array(array('Tenders', 0.0), array('Sales', 0.0), array('Discounts', 0.0), array('Tax', 0.0));
$prep = $connection->prepare('
SELECT t.TenderName,
s.quantity,
s.total
FROM sumTendersByDay AS s
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'tenders AS t
ON s.trans_subtype=t.TenderCode
WHERE date_id=?
ORDER BY t.TenderName');
$res = $connection->execute($prep, $args);
$tenders = array();
while ($w = $connection->fetchRow($res)) {
$tenders[] = array($w['TenderName'], $w['quantity'], $w['total']);
$reconciliation[0][1] += $w['total'];
}
/**
Always join into department settings twice
but swap priority depening on user request
*/
$then_prefix = 'a';
$now_prefix = 'b';
if (\FormLib::get('report-departments') == 'Current') {
$then_prefix = 'b';
$now_prefix = 'a';
}
$prep = $connection->prepare('
SELECT COALESCE(a.super_name, b.super_name) AS super_name,
SUM(s.quantity) AS quantity,
SUM(s.total) AS total
FROM sumRingSalesByDay AS s
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'products AS p
ON s.upc=p.upc
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'MasterSuperDepts AS ' . $then_prefix . '
ON s.department=' . $then_prefix . '.dept_ID
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'MasterSuperDepts AS ' . $now_prefix . '
ON p.department=' . $now_prefix . '.dept_ID
WHERE date_id=?
GROUP BY COALESCE(a.super_name, b.super_name)
ORDER BY COALESCE(a.super_name, b.super_name)');
$res = $connection->execute($prep, $args);
$sales = array();
while ($w = $connection->fetchRow($res)) {
$sales[] = array($w['super_name'], $w['quantity'], $w['total']);
$reconciliation[1][1] += $w['total'];
}
$prep = $connection->prepare('
SELECT m.memDesc,
s.transCount AS quantity,
s.total AS total
FROM sumDiscountsByDay AS s
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'memtype AS m
ON s.memType=m.memtype
WHERE s.date_id=?
ORDER BY m.memDesc');
$res = $connection->execute($prep, $args);
$discounts = array();
while ($w = $connection->fetchRow($res)) {
$discounts[] = array($w['memDesc'], $w['quantity'], $w['total']);
$reconciliation[2][1] += $w['total'];
}
$dtrans = \DTransactionsModel::selectDTrans($date1);
$dlog = \DTransactionsModel::selectDlog($date1);
$dates = array($date1 . ' 00:00:00', $date1 . ' 23:59:59');
$lineItemQ = $connection->prepare("\n SELECT description,\n SUM(regPrice) AS ttl\n FROM {$dtrans} AS d\n WHERE datetime BETWEEN ? AND ?\n AND d.upc='TAXLINEITEM'\n AND " . \DTrans::isNotTesting('d') . "\n GROUP BY d.description\n ");
$lineItemR = $connection->execute($lineItemQ, $dates);
$taxes = array();
while ($lineItemW = $connection->fetchRow($lineItemR)) {
$taxes[] = array($lineItemW['description'] . ' (est. owed)', sprintf('%.2f', $lineItemW['ttl']));
}
$taxSumQ = $connection->prepare("SELECT sum(total) as tax_collected\n FROM {$dlog} as d \n WHERE d.tdate BETWEEN ? AND ?\n AND (d.upc = 'tax')\n GROUP BY d.upc");
$taxR = $connection->execute($taxSumQ, $dates);
while ($taxW = $connection->fetch_row($taxR)) {
$taxes[] = array('Total Tax Collected', round($taxW['tax_collected'], 2));
$reconciliation[3][1] += $taxW['tax_collected'];
}
$prep = $connection->prepare('
SELECT m.memDesc,
COUNT(*) AS numTrans,
SUM(retailQty + nonRetailQty) AS totalItems,
AVG(retailQty + nonRetailQty) AS avgItems,
SUM(retailTotal + nonRetailTotal) AS total,
AVG(retailTotal + nonRetailTotal) AS avg
FROM transactionSummary AS t
LEFT JOIN ' . $config->get('OP_DB') . $connection->sep() . 'memtype AS m
ON t.memType=m.memtype
WHERE date_id=?
GROUP BY m.memDesc
ORDER BY m.memDesc');
$res = $connection->execute($prep, $args);
$transactions = array();
while ($w = $connection->fetchRow($res)) {
$transactions[] = array($w['memDesc'], $w['numTrans'], sprintf('%.2f', $w['totalItems']), sprintf('%.2f', $w['avgItems']), sprintf('%.2f', $w['total']), sprintf('%.2f', $w['avg']));
}
$ret = preg_match_all("/[0-9]+/", $config->get('EQUITY_DEPARTMENTS'), $depts);
$equity = array();
if ($ret != 0) {
/* equity departments exist */
$depts = array_pop($depts);
$dlist = "(";
foreach ($depts as $d) {
$dates[] = $d;
// add query param
$dlist .= '?,';
}
$dlist = substr($dlist, 0, strlen($dlist) - 1) . ")";
$equityQ = $connection->prepare("\n SELECT d.card_no,\n t.dept_name, \n sum(total) as total \n FROM {$dlog} as d\n INNER JOIN " . $config->get('OP_DB') . $connection->sep() . "departments as t ON d.department = t.dept_no\n WHERE d.tdate BETWEEN ? AND ?\n AND d.department IN {$dlist}\n GROUP BY d.card_no, \n t.dept_name \n ORDER BY d.card_no, \n t.dept_name");
$equityR = $connection->execute($equityQ, $dates);
while ($equityW = $connection->fetchRow($equityR)) {
$record = array($equityW['card_no'], $equityW['dept_name'], sprintf('%.2f', $equityW['total']));
$equity[] = $record;
}
}
$connection->setDefaultDB($originalDB);
return array($tenders, $sales, $discounts, $taxes, $reconciliation, $transactions, $equity);
}
