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;
}
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);
if (!$logged) {
$success = false;
}
}
}
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;
}
}
}
/**
Log page load in usageStats table
@param $dbc [SQLManager] database connection
@return [boolean] success / fail
*/
protected static function logUsage(SQLManager $dbc, $op_db)
{
if (php_sapi_name() === 'cli') {
// don't log cli usage
return false;
}
$user = FannieAuth::checkLogin();
if ($user === false) {
$user = '******';
}
$prep = $dbc->prepare('INSERT INTO usageStats
(tdate, pageName, referrer, userHash, ipHash)
VALUES
(?, ?, ?, ?, ?)');
$args = array(date('Y-m-d H:i:s'), basename(filter_input(INPUT_SERVER, 'PHP_SELF')));
$referrer = isset($_SERVER['HTTP_REFERER']) ? basename($_SERVER['HTTP_REFERER']) : 'n/a';
$referrer = filter_input(INPUT_SERVER, 'HTTP_REFERER');
$args[] = $referrer === null ? 'n/a' : basename($referrer);
$args[] = sha1($user);
$ip_addr = filter_input(INPUT_SERVER, 'REMOTE_ADDR');
$args[] = sha1($ip_addr);
return $dbc->execute($prep, $args);
}
$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++) {
$where .= "(order_id=" . $order_ids[$i] . " AND trans_id=" . $trans_ids[$i] . ") ";
if ($i < count($order_ids) - 1) {
$where .= " OR ";
}
}
$where .= ")";
echo cron_msg("Found " . count($order_ids) . " order items");
// copy item rows to completed and delete from pending
$copyQ = "INSERT INTO CompleteSpecialOrder SELECT * FROM PendingSpecialOrder WHERE {$where}";
/**
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);
}
</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;
case 'cat1':
*********************************************************************************/
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
}
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");
if ($chk1 === false || $chk2 === false) {
echo cron_msg("Error reloading dlog_15");
} else {
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);
}
