function redoCard($cardno) { global $FANNIE_LANES, $sql; $upcQ = $sql->prepare("SELECT upc FROM memberCards WHERE card_no=?"); $upcR = $sql->execute($upcQ, array($cardno)); $upc = ""; if ($sql->num_rows($upcR) > 0) { $upcW = $sql->fetch_row($upcR); $upc = $upcW['upc']; } foreach ($FANNIE_LANES as $lane) { $tmp = new SQLManager($lane['host'], $lane['type'], $lane['op'], $lane['user'], $lane['pw']); $delQ = $tmp->prepare("DELETE FROM memberCards WHERE card_no=?"); $delR = $tmp->execute($delQ, array($cardno)); if (!empty($upc)) { $ins = $tmp->prepare("INSERT INTO memberCards (card_no, upc)\n VALUES (?, ?)"); $tmp->execute($ins, array($cardno, $upc)); } } }
function allLanes($upc) { if (!class_exists("SQLManager")) { require_once "../../src/SQLManager.php"; } include '../lanedefs.php'; $queryItem = ''; $args = array(); if (is_numeric($upc)) { $upc = str_pad($upc, 13, 0, STR_PAD_LEFT); $queryItem = "SELECT * FROM products WHERE upc = ?"; $args = array($upc); } else { $queryItem = "SELECT * FROM products WHERE description LIKE ? ORDER BY description"; $args = array('%' . $upc . '%'); } for ($i = 0; $i < count($lanes); $i++) { $currentLane = $lanes[$i]; if (substr($currentLane, 0, 3) == "POS") { $currentLane = "129.103.2.1" . substr($currentLane, -1); } $sql = new SQLManager($currentLane, $types[$i], $dbs[$i], $users[$i], $pws[$i]); //continue; $prep = $sql->prepare($queryItem); $resultItem = $sql->execute($prep, $args); $num = $sql->num_rows($resultItem); if ($num == 0) { echo "Item <font color='red'>{$upc}</font> not found on Lane " . ($i + 1) . "<br />"; } else { if ($num > 1) { echo "Item <font color='red'>{$upc}</font> found multiple times on Lane " . ($i + 1) . "<br />"; while ($rowItem = $sql->fetch_array($resultItem)) { echo "{$rowItem['upc']} {$rowItem['description']}<br />"; } } else { $rowItem = $sql->fetch_array($resultItem); echo "Item <font color='red'>{$upc}</font> on Lane " . ($i + 1) . "<br />"; echo "Price: {$rowItem['normal_price']}"; if ($rowItem['special_price'] != 0) { echo " <font color=green>ON SALE: {$rowItem['special_price']}</font>"; } echo "<br />"; } } if ($i < count($lanes) - 1) { echo "<hr />"; } } }
/** 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')); $date2 = \FormLib::get_form_value('date2', date('Y-m-d')); $type = \FormLib::get_form_value('report-basis', 'Purchases'); $exclude = \FormLib::get_form_value('excludes', ''); if ($type == 'Join Date') { return false; } $ex = preg_split('/\\D+/', $exclude, 0, PREG_SPLIT_NO_EMPTY); $exCondition = ''; $exArgs = array(); foreach ($ex as $num) { $exCondition .= '?,'; $exArgs[] = $num; } $exCondition = substr($exCondition, 0, strlen($exCondition) - 1); $originalDB = $connection->defaultDatabase(); $plugin_settings = $config->get('PLUGIN_SETTINGS'); $connection->selectDB($plugin_settings['WarehouseDatabase']); $query = "\n SELECT \n CASE WHEN m.zip='' THEN 'none' ELSE m.zip END as zipcode,\n COUNT(*) as num_trans, \n SUM(total) as spending,\n COUNT(DISTINCT s.card_no) as uniques\n FROM sumMemSalesByDay AS s \n INNER JOIN " . $config->get('OP_DB') . $connection->sep() . "meminfo AS m ON s.card_no=m.card_no \n WHERE "; if (!empty($exArgs)) { $query .= "s.card_no NOT IN ({$exCondition}) AND "; } $query .= "s.date_id BETWEEN ? AND ?\n GROUP BY zipcode\n ORDER BY SUM(total) DESC"; $exArgs[] = $this->dateToID($date1); $exArgs[] = $this->dateToID($date2); $prep = $connection->prepare($query); $result = $connection->execute($prep, $exArgs); while ($row = $connection->fetchRow($result)) { $record = array($row['zipcode'], $row['num_trans'], $row['uniques'], $row['spending']); $data[] = $record; } $connection->setDefaultDB($originalDB); return $data; }
$chk_vital[] = $sql->query("UPDATE products SET normal_price = l.salePrice\n FROM products AS p LEFT JOIN\n upcLike AS v ON v.upc=p.upc LEFT JOIN\n batchList AS l ON l.upc='LC'+convert(varchar,v.likecode)\n LEFT JOIN batches AS b ON b.batchID = l.batchID\n WHERE l.upc LIKE 'LC%'\n AND b.discounttype = 0\n AND " . $sql->datediff($sql->now(), 'b.startDate') . " = 0"); } $success = true; foreach ($chk_vital as $chk) { if ($chk === false) { $success = false; } } 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 {
function forceBatch($batchID) { global $sql, $FANNIE_SERVER_DBMS, $FANNIE_LANES; $batchInfoQ = $sql->prepare("SELECT batchType,discountType FROM batches WHERE batchID = ?"); $batchInfoR = $sql->execute($batchInfoQ, array($batchID)); $batchInfoW = $sql->fetch_array($batchInfoR); $forceQ = ""; $forceLCQ = ""; $forceMMQ = ""; if ($batchInfoW['discountType'] != 0) { $forceQ = "UPDATE products AS p\n INNER JOIN batchList AS l\n ON p.upc=l.upc\n INNER JOIN batches AS b\n ON l.batchID=b.batchID\n SET p.start_date = b.startDate, \n p.end_date=b.endDate,\n p.special_price=l.salePrice,\n p.specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n p.specialpricemethod=l.pricemethod,\n p.specialquantity=l.quantity,\n p.discounttype=b.discounttype,\n p.mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(l.batchID,char)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(-1*l.batchID,char)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN concat('b',convert(l.batchID,char))\n ELSE p.mixmatchcode \n END \n WHERE l.upc not like 'LC%'\n and l.batchID = ?"; $forceLCQ = "UPDATE products AS p\n INNER JOIN upcLike AS v \n ON v.upc=p.upc\n INNER JOIN batchList as l \n ON l.upc=concat('LC',convert(v.likecode,char))\n INNER JOIN batches AS b \n ON b.batchID=l.batchID\n set p.special_price = l.salePrice,\n p.end_date = b.endDate,p.start_date=b.startDate,\n p.specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n p.specialpricemethod=l.pricemethod,\n p.specialquantity=l.quantity,\n p.discounttype = b.discounttype,\n p.mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(l.batchID,char)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(-1*l.batchID,char)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN concat('b',convert(l.batchID,char))\n ELSE p.mixmatchcode \n END \n where l.batchID=?"; if ($FANNIE_SERVER_DBMS == 'MSSQL') { $forceQ = "UPDATE products\n SET start_date = b.startDate, \n end_date=b.endDate,\n special_price=l.salePrice,\n specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n specialpricemethod=l.pricemethod,\n specialquantity=l.quantity,\n discounttype=b.discounttype,\n mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(varchar,l.batchID)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(varchar,-1*l.batchID)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN 'b'+convert(varchar,l.batchID)\n ELSE p.mixmatchcode \n END \n FROM products as p, \n batches as b, \n batchList as l \n WHERE l.upc = p.upc\n and l.upc not like 'LC%'\n and b.batchID = l.batchID\n and b.batchID = ?"; $forceLCQ = "update products set special_price = l.salePrice,\n end_date = b.endDate,start_date=b.startDate,\n discounttype = b.discounttype,\n specialpricemethod=l.pricemethod,\n specialquantity=l.quantity,\n specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(varchar,l.batchID)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(varchar,-1*l.batchID)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN 'b'+convert(varchar,l.batchID)\n ELSE p.mixmatchcode \n END \n from products as p left join\n upcLike as v on v.upc=p.upc left join\n batchList as l on l.upc='LC'+convert(varchar,v.likecode)\n left join batches as b on b.batchID = l.batchID\n where b.batchID=?"; } } else { $forceQ = "UPDATE products AS p\n INNER JOIN batchList AS l\n ON l.upc=p.upc\n SET p.normal_price = l.salePrice,\n p.modified = now()\n WHERE l.upc not like 'LC%'\n AND l.batchID = ?"; $forceLCQ = "UPDATE products AS p\n INNER JOIN upcLike AS v\n ON v.upc=p.upc INNER JOIN\n batchList as b on b.upc=concat('LC',convert(v.likecode,char))\n set p.normal_price = b.salePrice,\n p.modified=now()\n where b.batchID=?"; if ($FANNIE_SERVER_DBMS == 'MSSQL') { $forceQ = "UPDATE products\n SET normal_price = l.salePrice,\n modified = getdate()\n FROM products as p,\n batches as b,\n batchList as l\n WHERE l.upc = p.upc\n AND l.upc not like 'LC%'\n AND b.batchID = l.batchID\n AND b.batchID = ?"; $forceLCQ = "update products set normal_price = b.salePrice,\n modified=getdate()\n from products as p left join\n upcLike as v on v.upc=p.upc left join\n batchList as b on b.upc='LC'+convert(varchar,v.likecode)\n where b.batchID=?"; } } $forceP = $sql->prepare($forceQ); $forceR = $sql->execute($forceP, array($batchID)); $forceLCP = $sql->prepare($forceLCQ); $forceLCR = $sql->execute($forceLCP, array($batchID)); $columnsP = $sql->prepare(' SELECT p.upc, p.normal_price, p.special_price, p.modified, p.specialpricemethod, p.specialquantity, p.specialgroupprice, p.discounttype, p.mixmatchcode, p.start_date, p.end_date FROM products AS p INNER JOIN batchList AS b ON p.upc=b.upc WHERE b.batchID=?'); $lcColumnsP = $sql->prepare(' SELECT p.upc, p.normal_price, p.special_price, p.modified, p.specialpricemethod, p.specialquantity, p.specialgroupprice, p.discounttype, p.mixmatchcode, p.start_date, p.end_date FROM products AS p INNER JOIN upcLike AS u ON p.upc=u.upc INNER JOIN batchList AS b ON b.upc = ' . $sql->concat("'LC'", $sql->convert('u.likeCode', 'CHAR'), '') . ' WHERE b.batchID=?'); /** Get changed columns for each product record */ $upcs = array(); $columnsR = $sql->execute($columnsP, array($batchID)); while ($w = $sql->fetch_row($columnsR)) { $upcs[$w['upc']] = $w; } $columnsR = $sql->execute($lcColumnsP, array($batchID)); while ($w = $sql->fetch_row($columnsR)) { $upcs[$w['upc']] = $w; } $updateQ = ' UPDATE products AS p SET p.normal_price = ?, p.special_price = ?, p.modified = ?, p.specialpricemethod = ?, p.specialquantity = ?, p.specialgroupprice = ?, p.discounttype = ?, p.mixmatchcode = ?, p.start_date = ?, p.end_date = ? WHERE p.upc = ?'; /** Update all records on each lane before proceeding to the next lane. Hopefully faster / more efficient */ for ($i = 0; $i < count($FANNIE_LANES); $i++) { $lane_sql = new SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']); if (!isset($lane_sql->connections[$FANNIE_LANES[$i]['op']]) || $lane_sql->connections[$FANNIE_LANES[$i]['op']] === false) { // connect failed continue; } $updateP = $lane_sql->prepare($updateQ); foreach ($upcs as $upc => $data) { $lane_sql->execute($updateP, array($data['normal_price'], $data['special_price'], $data['modified'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['start_date'], $data['end_date'], $upc)); } } $update = new ProdUpdateModel($sql); $updateType = $batchInfoW['discountType'] == 0 ? ProdUpdateModel::UPDATE_PC_BATCH : ProdUpdateModel::UPDATE_BATCH; $update->logManyUpdates(array_keys($upcs), $updateType); }
/** 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; } } }
/** Lookup custom permissions for a page */ protected static function authOverride(SQLManager $dbc, $op_db, $page_class) { $prep = $dbc->prepare(' SELECT authClass FROM PagePermissons WHERE pageClass=?', $op_db); $auth = $dbc->getValue($prep, array($page_class), $op_db); return $auth ? $auth : false; }
} $allIDs = rtrim($allIDs, ",") . ")"; if (strlen($allIDs) > 2) { // copy to completed orders $copyQ = "INSERT INTO CompleteSpecialOrder\n SELECT p.* FROM PendingSpecialOrder AS p\n WHERE p.order_id IN {$allIDs}"; $sql->query($copyQ); // 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 '90 Days'\n FROM PendingSpecialOrder AS p\n WHERE p.order_id IN {$allIDs}\n GROUP BY p.order_id"; // remove from pending orders $delQ = "DELETE FROM PendingSpecialOrder\n WHERE order_id IN {$allIDs}"; $sql->query($delQ); } // end auto-close $query = "SELECT CASE WHEN matched > 10 THEN matched ELSE mixMatch END as mixMatch,\n CASE WHEN matched > 10 THEN mixMatch ELSE matched END AS matched,\n MAX(datetime) as tdate,\n MAX(emp_no) as emp,\n MAX(register_no) AS reg,\n MAX(trans_no) AS trans \n FROM transarchive\n WHERE charflag='SO' \n AND emp_no <> 9999 \n AND register_no <> 99 \n AND trans_status NOT IN ('X','Z')\n GROUP BY mixMatch,matched\n HAVING sum(total) <> 0"; $result = $sql->query($query); $checkP = $sql->prepare("SELECT order_id\n FROM SpecialOrderHistory\n WHERE order_id=?\n AND entry_type='PURCHASED'\n AND entry_date=?\n AND entry_value=?"); $historyP = $sql->prepare("INSERT INTO SpecialOrderHistory\n (order_id, entry_date, entry_type, entry_value)\n VALUES\n (?, ?, 'PURCHASED', ?)"); $order_ids = array(); $trans_ids = array(); while ($row = $sql->fetch_row($result)) { $order_ids[] = (int) $row['mixMatch']; $trans_ids[] = (int) $row['matched']; // log to history if entry doesn't already exist $args = array((int) $row['mixMatch'], $row['tdate'], $row['emp'] . '-' . $row['reg'] . '-' . $row['trans']); $checkR = $sql->execute($checkP, $args); if ($checkR && $sql->num_rows($checkR) == 0) { $sql->execute($historyP, $args); } } $where = "( "; for ($i = 0; $i < count($order_ids); $i++) {
/** Cleanup after forcibly starting or stopping a sales batch - Update lane item records to reflect on/off sale - Log changes to prodUpdate @param $id [int] batchID @param $updateType [cost] ProdUpdateModel update type @param $has_limit [boolean] products.special_limit and batches.transLimit columns are present Separate method since it's identical for starting and stopping */ private function finishForce($id, $updateType, $has_limit = true) { $columnsP = $this->connection->prepare(' SELECT p.upc, p.normal_price, p.special_price, p.modified, p.specialpricemethod, p.specialquantity, p.specialgroupprice, ' . ($has_limit ? 'p.special_limit,' : '') . ' p.discounttype, p.mixmatchcode, p.start_date, p.end_date FROM products AS p INNER JOIN batchList AS b ON p.upc=b.upc WHERE b.batchID=?'); $lcColumnsP = $this->connection->prepare(' SELECT p.upc, p.normal_price, p.special_price, p.modified, p.specialpricemethod, p.specialquantity, p.specialgroupprice, ' . ($has_limit ? 'p.special_limit,' : '') . ' p.discounttype, p.mixmatchcode, p.start_date, p.end_date FROM products AS p INNER JOIN upcLike AS u ON p.upc=u.upc INNER JOIN batchList AS b ON b.upc = ' . $this->connection->concat("'LC'", $this->connection->convert('u.likeCode', 'CHAR'), '') . ' WHERE b.batchID=?'); /** Get changed columns for each product record */ $upcs = array(); $columnsR = $this->connection->execute($columnsP, array($id)); while ($w = $this->connection->fetch_row($columnsR)) { $upcs[$w['upc']] = $w; } $columnsR = $this->connection->execute($lcColumnsP, array($id)); while ($w = $this->connection->fetch_row($columnsR)) { $upcs[$w['upc']] = $w; } $updateQ = ' UPDATE products AS p SET p.normal_price = ?, p.special_price = ?, p.modified = ?, p.specialpricemethod = ?, p.specialquantity = ?, p.specialgroupprice = ?, p.discounttype = ?, p.mixmatchcode = ?, p.start_date = ?, p.end_date = ? ' . ($has_limit ? ',p.special_limit = ?' : '') . ' WHERE p.upc = ?'; /** Update all records on each lane before proceeding to the next lane. Hopefully faster / more efficient */ $FANNIE_LANES = FannieConfig::config('LANES'); for ($i = 0; $i < count($FANNIE_LANES); $i++) { $lane_sql = new SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']); if (!isset($lane_sql->connections[$FANNIE_LANES[$i]['op']]) || $lane_sql->connections[$FANNIE_LANES[$i]['op']] === false) { // connect failed continue; } $updateP = $lane_sql->prepare($updateQ); foreach ($upcs as $upc => $data) { $args = array($data['normal_price'], $data['special_price'], $data['modified'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['start_date'], $data['end_date']); if ($has_limit) { $args[] = $data['special_limit']; } $args[] = $upc; $lane_sql->execute($updateP, $args); } } $update = new ProdUpdateModel($this->connection); $update->logManyUpdates(array_keys($upcs), $updateType); }
<script src="price_compare.js" type="text/javascript"></script> </head> <?php } //Click to create Excel page... echo "<a href=price_compare.php?excel=1&buyer={$buyID}&filter={$filter}>Dump to Excel</a><br>"; //Connect to mysql server $mysql = new SQLManager('localhost', 'MYSQL', 'IS4C', 'root', 'is4c'); $getCatQ = "SELECT unfi_cat FROM unfi_cat"; $getCatArgs = array(); if ($buyID != 99) { $getCatQ = "SELECT unfi_cat FROM unfi_cat WHERE buyer = ?"; $getCatArgs = array($buyID); } //echo $getCatQ; $getCatP = $mysql->prepare($getCatQ); $getCatR = $mysql->execute($getCatP, $getCatArgs); $sort = isset($_GET['sort']) ? $_GET['sort'] : ''; // validate sort option switch ($sort) { case 'cat': case 'diff': case 'variable_pricing': if ($sort === 0 || $sort === True) { $sort = 'cat'; } $sort .= ' ASC'; if ($sort != 'cat') { $sort .= ', cat'; } break;
*********************************************************************************/ include dirname(__FILE__) . '/../../config.php'; require_once dirname(__FILE__) . '/generic.mysql.php'; /** Sync the whole table using mysqldump. Then look up employees numbers that are valid for this store and construct a DELETE query to remove all others. Run the DELETE query at each lane. */ $config = FannieConfig::factory(); $dbc = FannieDB::get($config->get('OP_DB')); $map = new StoreEmployeeMapModel($dbc); $map->storeID($config->get('STORE_ID')); $query = ' DELETE FROM employees WHERE emp_no NOT IN ('; $args = array(); foreach ($map->find() as $obj) { $query .= '?,'; $args[] = $obj->empNo(); } $query = substr($query, 0, strlen($query) - 1) . ')'; foreach ($FANNIE_LANES as $lane) { $sql = new SQLManager($lane['host'], $lane['type'], $lane['op'], $lane['user'], $lane['pw']); if ($sql->connections[$lane['op']] !== false) { $prep = $sql->prepare($query); $sql->execute($prep, $args); } } echo "<li>Employees table synched</li>";
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 }
$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 ($chk1 === false) { echo cron_msg("Error loading data into transarchive"); } elseif ($chk2 === false) { echo cron_msg("Error trimming transarchive"); } else { echo cron_msg("Data rotated into transarchive"); } /* reload all the small snapshot */ $chk1 = $sql->query("TRUNCATE TABLE dlog_15"); $chk2 = $sql->query("INSERT INTO dlog_15 SELECT * FROM dlog_90_view WHERE " . $sql->datediff($sql->now(), 'tdate') . " <= 15");
UPDATE products AS p SET p.normal_price = ?, p.special_price = ?, p.modified = ?, p.specialpricemethod = ?, p.specialquantity = ?, p.specialgroupprice = ?, p.discounttype = ?, p.mixmatchcode = ?, p.start_date = ?, p.end_date = ? WHERE p.upc = ?'; /** Update all records on each lane before proceeding to the next lane. Hopefully faster / more efficient */ for ($i = 0; $i < count($FANNIE_LANES); $i++) { $lane_sql = new SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']); if (!isset($lane_sql->connections[$FANNIE_LANES[$i]['op']]) || $lane_sql->connections[$FANNIE_LANES[$i]['op']] === false) { // connect failed continue; } $updateP = $lane_sql->prepare($updateQ); foreach ($upcs as $upc => $data) { $lane_sql->execute($updateP, array($data['normal_price'], $data['special_price'], $data['modified'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['start_date'], $data['end_date'], $upc)); } } $update = new ProdUpdateModel($sql); $updateType = $batchInfoW['discountType'] == 0 ? ProdUpdateModel::UPDATE_PC_BATCH : ProdUpdateModel::UPDATE_BATCH; $update->logManyUpdates(array_keys($upcs), $updateType); echo "Batch {$batchID} has been forced";
/** Do whatever the service is supposed to do. Should override this. @param $args array of data @return an array of data */ public function run($args = array()) { $ret = array(); if (!property_exists($args, 'upc')) { // missing required arguments $ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters needs type'); return $ret; } if (!is_array($args->upc)) { $args->upc = array($args->upc); } $dbc = \FannieDB::get(\FannieConfig::config('OP_DB')); $storeID = \FannieConfig::get('STORE_ID'); /** In "fast" mode, look up the items and run UPDATE queries on each lane. This reduces overhead substantially but will overlook brand-new items since there's no check whether the item exists on the lane. If "fast" is not specified, each UPC record is copied to the lane exactly using models. This mode is preferrable unless performance becomes an issue. */ if (property_exists($args, 'fast')) { $upc_data = array(); $query = ' SELECT normal_price, pricemethod, quantity, groupprice, special_price, specialpricemethod, specialquantity, specialgroupprice, discounttype, mixmatchcode, department, tax, foodstamp, discount, qttyEnforced, idEnforced, inUse, upc FROM products WHERE store_id=? AND upc IN ('; $params = array($storeID); foreach ($args->upc as $upc) { $query .= '?,'; $params[] = \BarcodeLib::padUPC($upc); } $query = substr($query, 0, strlen($query) - 1); $prep = $dbc->prepare($query); $result = $dbc->execute($prep, $params); while ($w = $dbc->fetchRow($result)) { $upc_data[$w['upc']] = $w; } $updateQ = ' UPDATE products AS p SET p.normal_price = ?, p.pricemethod = ?, p.quantity = ?, p.groupprice = ?, p.special_price = ?, p.specialpricemethod = ?, p.specialquantity = ?, p.specialgroupprice = ?, p.discounttype = ?, p.mixmatchcode = ?, p.department = ?, p.tax = ?, p.foodstamp = ?, p.discount=?, p.qttyEnforced=?, p.idEnforced=?, p.inUse=? WHERE p.upc = ?'; $FANNIE_LANES = \FannieConfig::config('LANES'); for ($i = 0; $i < count($FANNIE_LANES); $i++) { $lane_sql = new \SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']); if (!isset($lane_sql->connections[$FANNIE_LANES[$i]['op']]) || $lane_sql->connections[$FANNIE_LANES[$i]['op']] === false) { // connect failed continue; } $updateP = $lane_sql->prepare($updateQ); foreach ($upc_data as $upc => $data) { $lane_args = array($data['normal_price'], $data['pricemethod'], $data['quantity'], $data['groupprice'], $data['special_price'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['department'], $data['tax'], $data['foodstamp'], $data['discount'], $data['qttyEnforced'], $data['idEnforced'], $data['inUse'], $upc); $lane_sql->execute($updateP, $lane_args); } } } else { $product = new \ProductsModel($dbc); $ret['synced'] = array(); foreach ($args->upc as $upc) { $upc = \BarcodeLib::padUPC($upc); $product->upc($upc); $product->store_id($storeID); if ($product->load()) { $product->pushToLanes(); $ret['synced'][] = $upc; } } } return $ret; }
public static function aggregateStruct(SQLManager $connection, $dlog, $start_date, $end_date, stdclass $where, $groupby = array()) { $base_table = self::selectStruct($dlog, $start_date, $end_date); $dt_col = $dlog ? 'tdate' : 'datetime'; $clone_table = $dlog ? 'dlog_15' : 'transarchive'; /** Grouping is required */ if (!is_array($groupby) || count($groupby) == 0) { return $base_table; } /** Validate group by columns */ $model = new DTransactionsModel(null); $columns = $model->getColumns(); $insert_cols = array(); $select_cols = array(); for ($i = 0; $i < count($groupby); $i++) { $group = $groupby[$i]; if (isset($columns[$group])) { $insert_cols[] = $group; $select_cols[] = $group; } elseif (preg_match('/(.+)\\s+AS\\s+(\\w+)$/', $group, $matches)) { $col_definition = $matches[1]; $col_alias = $matches[2]; if (isset($columns[$col_alias])) { $insert_cols[] = $col_alias; $select_cols[] = $group; $groupby[$i] = $col_definition; } else { return $base_table; } } else { return $base_table; } } /** Always include a datetime column */ if (!in_array($dt_col, $insert_cols)) { $insert_cols[] = $dt_col; $select_cols[] = 'MAX(' . $dt_col . ') AS ' . $dt_col; } /** Create randomly named temporary table based on the structure of dlog_15 or transachive */ $config = FannieConfig::factory(); $sep = $connection->sep(); $random_name = uniqid('temp' . rand(1000, 9999)); $temp_table = $config->get('ARCHIVE_DB') . $sep . $random_name; $clone_table = $config->get('TRANS_DB') . $sep . $clone_table; $temp_name = $connection->temporaryTable($temp_table, $clone_table); if ($temp_name === false) { return $base_table; } /** Build a query to insert aggregated rows into the temporary table */ $query = 'INSERT INTO ' . $temp_name . '('; foreach ($insert_cols as $c) { $query .= $c . ','; } $query .= 'total, quantity) '; $query .= ' SELECT '; foreach ($select_cols as $c) { $query .= $c . ','; } /** Always aggregate by total & quantity */ $query .= ' SUM(total) AS total, ' . DTrans::sumQuantity() . ' AS quantity FROM __TRANSACTION_TABLE__ WHERE ' . $dt_col . ' BETWEEN ? AND ? '; $params = array($start_date . ' 00:00:00', $end_date . ' 23:59:59'); /** Add a where clause if one has been specified */ if (property_exists($where, 'sql') && is_array($where->sql)) { foreach ($where->sql as $sql) { $query .= ' AND ' . $sql; } } if (property_exists($where, 'params') && is_array($where->params)) { foreach ($where->params as $p) { $params[] = $p; } } /** Add the group by clause */ $query .= ' GROUP BY '; foreach ($groupby as $group) { $query .= $group . ','; } $query = substr($query, 0, strlen($query) - 1); /** Split monthly archive union if needed */ $source_tables = array(); if (strstr($base_table, ' UNION ')) { preg_match_all('/\\s+FROM\\s+(\\w+)\\s+/', $base_table, $matches); foreach ($matches[1] as $m) { $source_tables[] = $m; } } else { $source_tables = array($base_table); } /** Load data into temporary table from source table(s) using built query */ foreach ($source_tables as $source_table) { $insertQ = str_replace('__TRANSACTION_TABLE__', $source_table, $query); $prep = $connection->prepare($insertQ); if (!$connection->execute($prep, $params)) { return $base_table; } } return $temp_name; }
/** 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); }