public function fetch_report_data()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$qty = FormLib::get('qty', 1);
$create = $dbc->prepare_statement("CREATE TABLE groupingTempBS (upc VARCHAR(13), quantity double, total decimal(10,2), trans_num varchar(50))");
$dbc->exec_statement($create);
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$setupQ = $dbc->prepare_statement("INSERT INTO groupingTempBS\n SELECT upc, quantity, total, trans_num\n FROM {$dlog} AS d WHERE tdate BETWEEN ? AND ?\n AND trans_type IN ('I','D')\n GROUP BY year(tdate),month(tdate),day(tdate),trans_num \n HAVING COUNT(*) <= ?");
$dbc->exec_statement($setupQ, array($date1 . ' 00:00:00', $date2 . ' 23:59:59', $qty));
$reportQ = $dbc->prepare_statement('
SELECT g.upc,
p.description,
SUM(g.quantity) AS qty,
COUNT(DISTINCT trans_num) AS num,
SUM(total) AS ttl
FROM groupingTempBS as g ' . DTrans::joinProducts('g', 'p') . '
GROUP BY g.upc,
p.description
HAVING sum(total) <> 0
ORDER BY count(*) DESC
');
$reportR = $dbc->exec_statement($reportQ);
$data = array();
while ($w = $dbc->fetch_row($reportR)) {
$record = array($w['upc'], empty($w['description']) ? 'n/a' : $w['description'], $w[3], sprintf('%.2f', $w[2]), sprintf('%.2f', $w[4]));
$data[] = $record;
}
$drop = $dbc->prepare_statement("DROP TABLE groupingTempBS");
$dbc->exec_statement($drop);
return $data;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$query = '
SELECT b.batchID,
b.batchName,
l.upc,
p.normal_price,
l.salePrice,
CASE WHEN b.startDate > ' . $dbc->curdate() . ' THEN 0 ELSE 1 END AS current
FROM batches AS b
INNER JOIN batchList AS l ON b.batchID=l.batchID
' . DTrans::joinProducts('l', 'p', 'INNER') . '
WHERE b.endDate >= ' . $dbc->curdate() . '
AND b.discounttype <> 0
AND l.salePrice >= p.normal_price';
$result = $dbc->query($query);
$data = array();
while ($w = $dbc->fetchRow($result)) {
$record = array('<a href="' . $this->config->get('URL') . 'batches/newbatch/EditBatchPage.php?id=' . $w['batchID'] . '">' . $w['batchName'] . '</a>', $w['upc'], $w['current'] == 1 ? 'Yes' : 'No', $w['normal_price'], $w['salePrice']);
$data[] = $record;
}
return $data;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$deptStart = FormLib::get('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get('buyer', '');
// args/parameters differ with super
// vs regular department
$args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
$where = ' 1=1 ';
if ($buyer !== '') {
if ($buyer == -2) {
$where .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$where .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
$where .= $conditional;
}
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "\n SELECT d.upc,\n p.brand,\n p.description," . DTrans::sumQuantity('d') . " AS qty,\n CASE WHEN memDiscount <> 0 AND memType <> 0 THEN unitPrice - memDiscount ELSE unitPrice END as price,\n d.department, \n t.dept_name, \n SUM(total) AS total\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't');
// join only needed with specific buyer
if ($buyer !== '' && $buyer > -1) {
$query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$query .= "\n WHERE tdate BETWEEN ? AND ?\n AND {$where}\n GROUP BY d.upc,p.description,price,d.department,t.dept_name\n ORDER BY d.upc";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($query, $args);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['price']), sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']));
$data[] = $record;
}
// bold items that sold at multiple prices
for ($i = 0; $i < count($data); $i++) {
if (!isset($data[$i + 1])) {
continue;
}
if ($data[$i][0] == $data[$i + 1][0]) {
$data[$i]['meta'] = FannieReportPage::META_BOLD;
$data[$i + 1]['meta'] = FannieReportPage::META_BOLD;
}
}
return $data;
}
public function fetch_report_data()
{
global $FANNIE_OP_DB, $FANNIE_SERVER_DBMS;
// creates a temporary table so requesting a writable connection
// does make sense here
$dbc = FannieDB::get($FANNIE_OP_DB);
$depts = FormLib::get('depts', array());
$upc = FormLib::get('upc');
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$filters = FormLib::get('filters', array());
list($dClause, $dArgs) = $dbc->safeInClause($depts);
$where = "d.department IN ({$dClause})";
$inv = "d.department NOT IN ({$dClause})";
if ($upc != "") {
$upc = BarcodeLib::padUPC($upc);
$where = "d.upc = ?";
$inv = "d.upc <> ?";
$dArgs = array($upc);
}
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$filter = "";
$fArgs = array();
if (is_array($filters) && count($filters) > 0) {
$fClause = "";
foreach ($filters as $f) {
$fClause .= "?,";
$fArgs[] = $f;
}
$fClause = "(" . rtrim($fClause, ",") . ")";
$filter = "AND d.department IN {$fClause}";
}
$query = $dbc->prepare_statement("CREATE TABLE groupingTemp (tdate varchar(11), emp_no int, register_no int, trans_no int)");
$dbc->exec_statement($query);
$dateConvertStr = $FANNIE_SERVER_DBMS == 'MSSQL' ? 'convert(char(11),d.tdate,110)' : 'convert(date(d.tdate),char)';
$loadQ = $dbc->prepare_statement("INSERT INTO groupingTemp\n SELECT {$dateConvertStr} as tdate,\n emp_no,register_no,trans_no FROM {$dlog} AS d\n WHERE {$where} AND tdate BETWEEN ? AND ?\n GROUP BY {$dateConvertStr}, emp_no,register_no,trans_no");
$dArgs[] = $date1 . ' 00:00:00';
$dArgs[] = $date2 . ' 23:59:59';
$dbc->exec_statement($loadQ, $dArgs);
$dataQ = $dbc->prepare_statement("\n SELECT d.upc,\n p.description,\n t.dept_no,\n t.dept_name,\n SUM(d.quantity) AS quantity\n FROM {$dlog} AS d \n INNER JOIN groupingTemp AS g ON \n {$dateConvertStr} = g.tdate\n AND g.emp_no = d.emp_no\n AND g.register_no = d.register_no\n AND g.trans_no = d.trans_no " . DTrans::joinProducts('d', 'p') . DTrans::joinDepartments('d', 't') . "\n WHERE {$inv} \n AND trans_type IN ('I','D')\n AND d.tdate BETWEEN ? AND ?\n AND d.trans_status=''\n {$filter}\n GROUP BY d.upc,\n p.description,\n t.dept_no,\n t.dept_name\n ORDER BY SUM(d.quantity) DESC");
foreach ($fArgs as $f) {
$dArgs[] = $f;
}
$dataR = $dbc->exec_statement($dataQ, $dArgs);
$data = array();
while ($dataW = $dbc->fetch_row($dataR)) {
$record = array($dataW['upc'], $dataW['description'], $dataW['dept_no'] . ' ' . $dataW['dept_name'], sprintf('%.2f', $dataW['quantity']));
$data[] = $record;
}
$drop = $dbc->prepare_statement("DROP TABLE groupingTemp");
$dbc->exec_statement($drop);
return $data;
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$month1 = $this->form->month1;
$month2 = $this->form->month2;
$year1 = FormLib::get_form_value('year1', date('Y'));
$year2 = FormLib::get_form_value('year2', date('Y'));
$date1 = date('Y-m-d', mktime(0, 0, 0, $month1, 1, $year1));
$date2 = date('Y-m-t', mktime(0, 0, 0, $month2, 1, $year2));
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$date1 .= ' 00:00:00';
$date2 .= ' 00:00:00';
$qArgs = array($date1, $date2);
$query = "";
$type = FormLib::get_form_value('mtype', 'upc');
if ($type == 'upc') {
$inClause = "(";
$vals = preg_split("/\\D+/", FormLib::get_form_value('upcs', ''));
foreach ($vals as $v) {
$qArgs[] = BarcodeLib::padUPC($v);
$inClause .= "?,";
}
$inClause = rtrim($inClause, ",") . ")";
$query = "SELECT t.upc,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(total) AS sales, \n MONTH(tdate) AS month, \n YEAR(tdate) AS year\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p') . " \n WHERE t.trans_status <> 'M'\n AND tdate BETWEEN ? AND ?\n AND t.upc IN {$inClause}\n GROUP BY YEAR(tdate),\n MONTH(tdate),\n t.upc,\n p.description\n ORDER BY YEAR(tdate),\n MONTH(tdate),\n t.upc,\n p.description";
} else {
$dept1 = FormLib::get_form_value('dept1', 1);
$dept2 = FormLib::get_form_value('dept2', 1);
$qArgs[] = $dept1;
$qArgs[] = $dept2;
$query = "SELECT t.department,d.dept_name,SUM(t.quantity) as qty,\n SUM(total) as sales, MONTH(tdate) as month, YEAR(tdate) as year\n FROM {$dlog} AS t\n LEFT JOIN departments AS d ON t.department=d.dept_no\n WHERE t.trans_status <> 'M'\n AND tdate BETWEEN ? AND ?\n AND t.department BETWEEN ? AND ?\n GROUP BY YEAR(tdate),MONTH(tdate),t.department,d.dept_name\n ORDER BY YEAR(tdate),MONTH(tdate),t.department,d.dept_name";
}
$queryP = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($queryP, $qArgs);
$ret = array();
while ($row = $dbc->fetch_array($result)) {
if (!isset($ret[$row[0]])) {
$ret[$row[0]] = array('num' => $row[0], 'desc' => $row[1]);
foreach ($this->months as $mkey) {
$ret[$row[0]][$mkey] = 0;
}
}
if (FormLib::get_form_value('results', 'Sales') == 'Sales') {
$ret[$row[0]][$row['year'] . '-' . $row['month']] = $row['sales'];
} else {
$ret[$row[0]][$row['year'] . '-' . $row['month']] = $row['qty'];
}
}
return $this->dekey_array($ret);
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$manu = FormLib::get_form_value('manu', '');
$type = FormLib::get_form_value('type', '');
$groupby = FormLib::get_form_value('groupby', 'upc');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$type_condition = "p.brand LIKE ?";
$args = array('%' . $manu . '%');
if ($type == 'prefix') {
$type_condition = 't.upc LIKE ?';
}
$query = "";
$args[] = $date1 . ' 00:00:00';
$args[] = $date2 . ' 23:59:59';
switch ($groupby) {
case 'upc':
$query = "\n SELECT t.upc,\n p.brand,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n d.dept_no,\n d.dept_name,\n s.superID\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no = s.dept_ID\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY t.upc,\n p.description,\n d.dept_no,\n d.dept_name,\n s.superID\n ORDER BY SUM(t.total) DESC";
break;
case 'date':
$query = "\n SELECT YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . "\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)\n ORDER BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)";
break;
case 'dept':
$query = "\n SELECT d.dept_no,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n s.superID\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no=s.dept_ID\n WHERE {$type_condition}\n AND t.tdate BETWEEN ? AND ?\n GROUP BY d.dept_no,\n d.dept_name,\n s.superID\n ORDER BY SUM(t.total) DESC";
break;
}
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
$ret = array();
while ($row = $dbc->fetch_array($result)) {
$record = array();
if ($groupby == "date") {
$record[] = $row['month'] . '/' . $row['day'] . '/' . $row['year'];
$record[] = number_format($row['qty'], 2);
$record[] = number_format($row['ttl'], 2);
} else {
for ($i = 0; $i < $dbc->num_fields($result); $i++) {
if ($dbc->field_name($result, $i) == 'qty' || $dbc->field_name($result, $i) == 'ttl') {
$row[$i] = sprintf('%.2f', $row[$i]);
}
$record[] .= $row[$i];
}
}
$ret[] = $record;
}
return $ret;
}
function lookupItem($store, $sec, $subsec, $sh_set, $shelf, $loc)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$q = $dbc->prepare_statement("SELECT l.upc,p.description FROM prodPhysicalLocation AS l\n " . DTrans::joinProducts('l') . "\n WHERE l.store_id=? AND section=? AND subsection=?\n AND shelf_set=? AND shelf=? AND location=?");
$args = array($store, $sec, $subsec, $sh_set, $shelf, $loc);
$r = $dbc->exec_statement($q, $args);
$ret = array('upc' => '', 'description' => 'no item at this location');
if ($dbc->num_rows($r) > 0) {
$w = $dbc->fetch_row($r);
$ret['upc'] = $w['upc'];
$ret['description'] = $w['description'];
}
return $ret;
}
public function guessCode()
{
$dbc = $this->connection;
// case 1: item exists in products
$deptP = $dbc->prepare('
SELECT d.salesCode
FROM products AS p
INNER JOIN departments AS d ON p.department=d.dept_no
WHERE p.upc=?');
$deptR = $dbc->execute($deptP, array($this->internalUPC()));
if ($dbc->numRows($deptR)) {
$w = $dbc->fetchRow($deptR);
return $w['salesCode'];
}
$order = new PurchaseOrderModel($dbc);
$order->orderID($this->orderID());
$order->load();
// case 2: item is SKU-mapped but the order record
// does not reflect the internal PLU
$deptP = $dbc->prepare('
SELECT d.salesCode
FROM vendorSKUtoPLU AS v
' . DTrans::joinProducts('v', 'p', 'INNER') . '
INNER JOIN departments AS d ON p.department=d.dept_no
WHERE v.sku=?
AND v.vendorID=?');
$deptR = $dbc->execute($deptP, array($this->sku(), $order->vendorID()));
if ($dbc->numRows($deptR)) {
$w = $dbc->fetchRow($deptR);
return $w['salesCode'];
}
// case 3: item is not normally carried but is in a vendor catalog
// that has vendor => POS department mapping
$deptP = $dbc->prepare('
SELECT d.salesCode
FROM vendorItems AS v
INNER JOIN vendorDepartments AS z ON v.vendorDept=z.deptID AND v.vendorID=z.vendorID
INNER JOIN departments AS d ON z.posDeptID=d.dept_no
WHERE v.sku=?
AND v.vendorID=?');
$deptR = $dbc->execute($deptP, array($this->sku(), $order->vendorID()));
if ($dbc->numRows($deptR)) {
$w = $dbc->fetchRow($deptR);
return $w['salesCode'];
}
return false;
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$upc = $this->form->upc;
if (is_numeric($upc)) {
$upc = BarcodeLib::padUPC($upc);
}
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "SELECT \n MONTH(t.tdate),\n DAY(t.tdate),\n YEAR(t.tdate),\n t.upc,\n p.brand,\n p.description,\n " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS total\n FROM {$dlog} AS t \n " . DTrans::joinProducts('t', 'p', 'LEFT') . "\n WHERE t.upc = ? AND\n t.tdate BETWEEN ? AND ?\n GROUP BY \n YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate),\n t.upc,\n p.description\n ORDER BY year(t.tdate),month(t.tdate),day(t.tdate)";
$args = array($upc, $date1 . ' 00:00:00', $date2 . ' 23:59:59');
if (strtolower($upc) == "rrr" || $upc == "0000000000052") {
if ($dlog == "dlog_90_view" || $dlog == "dlog_15") {
$dlog = "transarchive";
} else {
$dlog = "trans_archive.bigArchive";
}
$query = "select MONTH(datetime),DAY(datetime),YEAR(datetime),\n upc,'' AS brand,'RRR' AS description,\n sum(case when upc <> 'rrr' then quantity when volSpecial is null or volSpecial > 9999 then 0 else volSpecial end) as qty,\n sum(t.total) AS total from\n {$dlog} as t\n where upc = ?\n AND datetime BETWEEN ? AND ?\n and emp_no <> 9999 and register_no <> 99\n and trans_status <> 'X'\n GROUP BY YEAR(datetime),MONTH(datetime),DAY(datetime)\n ORDER BY YEAR(datetime),MONTH(datetime),DAY(datetime)";
} else {
if (!is_numeric($upc)) {
$dlog = DTransactionsModel::selectDTrans($date1, $date2);
$query = "select MONTH(datetime),DAY(datetime),YEAR(datetime),\n upc,'' AS brand, description,\n sum(CASE WHEN quantity=0 THEN 1 ELSE quantity END) as qty,\n sum(t.total) AS total from\n {$dlog} as t\n where upc = ?\n AND datetime BETWEEN ? AND ?\n and emp_no <> 9999 and register_no <> 99\n and (trans_status <> 'X' || trans_type='L')\n GROUP BY YEAR(datetime),MONTH(datetime),DAY(datetime)";
}
}
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
/**
Simple report
Issue a query, build array of results
*/
$ret = array();
while ($row = $dbc->fetch_array($result)) {
$record = array();
$record[] = $row[0] . "/" . $row[1] . "/" . $row[2];
$record[] = $row['upc'];
$record[] = $row['brand'] === null ? '' : $row['brand'];
$record[] = $row['description'] === null ? '' : $row['description'];
$record[] = sprintf('%.2f', $row['qty']);
$record[] = sprintf('%.2f', $row['total']);
$ret[] = $record;
}
return $ret;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$vendor = FormLib::get_form_value('vendor', '');
$groupby = FormLib::get_form_value('groupby', 'upc');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "";
switch ($groupby) {
case 'upc':
$query = "\n SELECT t.upc,\n COALESCE(p.brand, x.manufacturer) AS brand,\n p.description, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n d.dept_no,\n d.dept_name,\n s.super_name\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no = s.dept_ID\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY t.upc,\n COALESCE(p.brand, x.manufacturer),\n p.description,\n d.dept_no,\n d.dept_name,\n s.super_name\n ORDER BY SUM(t.total) DESC";
break;
case 'date':
$query = "\n SELECT YEAR(t.tdate) AS year,\n MONTH(t.tdate) AS month,\n DAY(t.tdate) AS day, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)\n ORDER BY YEAR(t.tdate),\n MONTH(t.tdate),\n DAY(t.tdate)";
break;
case 'dept':
$query = "\n SELECT d.dept_no,\n d.dept_name, " . DTrans::sumQuantity('t') . " AS qty,\n SUM(t.total) AS ttl,\n s.super_name\n FROM {$dlog} AS t " . DTrans::joinProducts('t', 'p', 'INNER') . DTrans::joinDepartments('t', 'd') . "\n LEFT JOIN vendors AS v ON p.default_vendor_id = v.vendorID\n LEFT JOIN MasterSuperDepts AS s ON d.dept_no=s.dept_ID\n LEFT JOIN prodExtra AS x ON p.upc=x.upc\n WHERE (v.vendorName LIKE ? OR x.distributor LIKE ?)\n AND t.tdate BETWEEN ? AND ?\n GROUP BY d.dept_no,\n d.dept_name,\n s.super_name\n ORDER BY SUM(t.total) DESC";
break;
}
$args = array('%' . $vendor . '%', '%' . $vendor . '%', $date1 . ' 00:00:00', $date2 . ' 23:59:59');
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
$ret = array();
while ($row = $dbc->fetch_array($result)) {
$record = array();
if ($groupby == "date") {
$record[] = $row['month'] . '/' . $row['day'] . '/' . $row['year'];
$record[] = number_format($row['qty'], 2);
$record[] = number_format($row['ttl'], 2);
} else {
for ($i = 0; $i < $dbc->num_fields($result); $i++) {
if ($dbc->field_name($result, $i) == 'qty' || $dbc->field_name($result, $i) == 'ttl') {
$row[$i] = number_format($row[$i], 2);
}
$record[] .= $row[$i];
}
}
$ret[] = $record;
}
return $ret;
}
public function run()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$yesterday = date('Y-m-d', strtotime('yesterday'));
$url = $this->config->get('URL');
$host = php_uname('n');
$dtrans = DTransactionsModel::selectDTrans($yesterday);
$findP = $dbc->prepare('
SELECT d.upc,
d.description,
d.department,
p.inUse,
count(*) AS occurences
FROM ' . $dtrans . ' AS d ' . DTrans::joinProducts('d') . '
WHERE d.trans_type=\'L\'
AND d.trans_subtype=\'OG\'
AND d.charflag=\'IU\'
AND d.emp_no <> 9999
AND d.register_no <> 99
AND d.datetime BETWEEN ? AND ?
GROUP BY d.upc,
d.description,
d.department,
p.inUse
HAVING SUM(d.quantity) <> 0
');
$findR = $dbc->execute($findP, array($yesterday . ' 00:00:00', $yesterday . ' 23:59:59'));
while ($w = $dbc->fetchRow($findR)) {
$msg = sprintf('%s (%s) was sold %d times while not inUse', $w['description'], $w['upc'], $w['occurences']);
$email = \COREPOS\Fannie\API\lib\AuditLib::getAddresses($w['department']);
if ($email) {
$subject = 'Not In Use Report';
$from = "From: automail\r\n";
$msg .= "\n";
$msg .= "http://{$host}/{$url}item/ItemEditorPage.php?searchupc={$w['upc']}\n";
mail($email, $subject, $msg, $from);
} else {
$this->cronMsg($msg);
}
}
}
public function form_content()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$set = FormLib::get('deal-set');
$optsR = $dbc->query('
SELECT dealSet
FROM CoopDealsItems
GROUP BY dealSet
ORDER BY MAX(coopDealsItemID) DESC');
$opts = '';
while ($optsW = $dbc->fetchRow($optsR)) {
if ($set === '') {
$set = $optsW['dealSet'];
}
$opts .= sprintf('<option %s>%s</option>', $set == $optsW['dealSet'] ? 'selected' : '', $optsW['dealSet']);
}
$query = $dbc->prepare_statement("\n SELECT\n t.upc,\n p.brand,\n p.description,\n t.price,\n CASE WHEN s.super_name IS NULL THEN 'sale' ELSE s.super_name END as batch,\n t.abtpr as subbatch\n FROM CoopDealsItems as t\n " . DTrans::joinProducts('t', 'p', 'INNER') . "\n LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID\n WHERE t.dealSet=?\n AND p.inUse=1\n ORDER BY s.super_name,t.upc\n ");
$result = $dbc->exec_statement($query, array($set));
$ret = "<form action=CoopDealsReviewPage.php method=post>\n <div class=\"form-group\">\n <label>Month</label>\n <select name=\"deal-set\" class=\"form-control\" \n onchange=\"location='?deal-set='+this.value;\">\n " . $opts . "\n </select>\n </div>\n <table class=\"table table-bordered table-striped tablesorter tablesorter-core small\">\n <thead>\n <tr><th>UPC</th><th>Brand</th><th>Desc</th><th>Sale Price</th>\n <th>New Batch Name</th></tr>\n\n </thead><tbody>";
while ($row = $dbc->fetch_row($result)) {
$ret .= sprintf('<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%.2f</td>
<td><span class="superNameSpan">%s </span>Co-op Deals %s</td>
</tr>' . "\n", $row['upc'], $row['brand'], $row['description'], $row['price'], $row['batch'], $row['subbatch']);
}
$ret .= <<<html
</tbody>
</table><p />
<div class="row form-horizontal form-group">
<label class="col-sm-2 control-label">A Start</label>
<div class="col-sm-4">
<input type="text" name="start" id="start" class="form-control date-field" />
</div>
<label class="col-sm-2 control-label">B Start</label>
<div class="col-sm-4">
<input type="text" name="bstart" id="bstart" class="form-control date-field" />
</div>
</div>
<div class="row form-horizontal form-group">
<label class="col-sm-2 control-label">A End</label>
<div class="col-sm-4">
<input type="text" name="end" id="end" class="form-control date-field" />
</div>
<label class="col-sm-2 control-label">B End</label>
<div class="col-sm-4">
<input type="text" name="bend" id="bend" class="form-control date-field" />
</div>
</div>
<div class="row form-horizontal form-group">
<label class="col-sm-2 control-label">Month</label>
<div class="col-sm-4">
<input type="text" name="naming" class="form-control" value="{{set}}" />
</div>
<label class="col-sm-6">
<input type="checkbox" name="group_by_superdepts" checked="true"
onchange="\$('.superNameSpan').toggle(); " />
Group sale batches by Superdepartment
</label>
</div>
<p>
<button type=submit class="btn btn-default">Create Batch(es)</button>
<a href="CoopDealsMergePage.php" class="pull-right btn btn-default">Merge New Items into Existing Batch(es)</a>
</p>
</form>
html;
$ret = str_replace('{{set}}', $set, $ret);
return $ret;
}
public function get_view()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$set = FormLib::get('deal-set');
$optsR = $dbc->query('
SELECT dealSet
FROM CoopDealsItems
GROUP BY dealSet
ORDER BY MAX(coopDealsItemID) DESC');
$deal_opts = '';
while ($optsW = $dbc->fetchRow($optsR)) {
if ($set === '') {
$set = $optsW['dealSet'];
}
$deal_opts .= sprintf('<option %s>%s</option>', $set == $optsW['dealSet'] ? 'selected' : '', $optsW['dealSet']);
}
$query = $dbc->prepare_statement("\n SELECT\n t.upc,\n p.description,\n p.brand,\n t.price,\n CASE WHEN s.super_name IS NULL THEN 'sale' ELSE s.super_name END as batch,\n t.abtpr as subbatch,\n multiplier\n FROM\n CoopDealsItems as t\n " . DTrans::joinProducts('t', 'p', 'INNER') . "\n LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID\n WHERE p.inUse=1\n AND t.price < p.normal_price\n AND t.dealSet=?\n ORDER BY s.super_name,t.upc\n ");
$result = $dbc->exec_statement($query, array($set));
$upcomingP = $dbc->prepare('
SELECT batchName
FROM batchList AS l
INNER JOIN batches AS b ON l.batchID=b.batchID
WHERE l.upc=?
AND b.endDate >= ' . $dbc->curdate());
$allR = $dbc->query('
SELECT batchID,
batchName
FROM batches
WHERE endDate >= ' . $dbc->curdate());
$opts = array();
while ($allW = $dbc->fetchRow($allR)) {
$opts[$allW['batchID']] = $allW['batchName'];
}
$ret = "<form action=CoopDealsMergePage.php method=post>\n <div class=\"form-group\">\n <label>Month</label>\n <select name=\"deal-set\" class=\"form-control\" \n onchange=\"location='?deal-set='+this.value;\">\n " . $deal_opts . "\n </select>\n </div>\n <table class=\"table table-bordered table-striped tablesorter tablesorter-core small\">\n <thead>\n <tr><th>UPC</th><th>Brand</th><th>Desc</th><th>Sale Price</th>\n <th>Add to Batch</th></tr>\n\n </thead><tbody>";
while ($row = $dbc->fetch_row($result)) {
$upcoming = $dbc->getValue($upcomingP, array($row['upc']));
if ($upcoming) {
continue;
}
$name = $row['batch'] . ' Co-op Deals ' . $row['subbatch'];
$ret .= sprintf('<tr>
<td><input type="hidden" name="upc[]" value="%s"/>%s
<input type="hidden" name="mult[]" value="%d" />
</td>
<td>%s</td>
<td>%s</td>
<td><input type="hidden" name="price[]" value="%.2f"/>%.2f</td>
<td><select class="form-control input-sm" name="batchID[]">
<option value="">Select batch...</option>', $row['upc'], \COREPOS\Fannie\API\lib\FannieUI::itemEditorLink($row['upc']), $row['multiplier'], $row['brand'], $row['description'], $row['price'], $row['price']);
foreach ($opts as $id => $batch) {
$ret .= sprintf('<option %s value="%d">%s</option>', strstr($batch, $name) ? 'selected' : '', $id, $batch);
}
$ret .= '</select></td></tr>';
}
$ret .= <<<html
</tbody>
</table>
<p>
<button type=submit class="btn btn-default">Merge Items into Batch(es)</button>
<a href="CoopDealsReviewPage.php" class="pull-right btn btn-default">Create New Batch(es)</a>
</p>
</form>
html;
return $ret;
}
/**
Lots of options on this report.
*/
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$deptStart = FormLib::get_form_value('deptStart', '');
$deptEnd = FormLib::get_form_value('deptEnd', '');
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get_form_value('buyer', '');
$groupby = FormLib::get_form_value('sort', 'PLU');
$store = FormLib::get('store', 0);
$superP = $dbc->prepare('SELECT dept_ID FROM superdepts WHERE superID=?');
/**
Build a WHERE condition for later.
Superdepartment (buyer) takes precedence over
department and negative values have special
meaning
Extra lookup to write condition in terms of
transaction.department seems to result in
better index utilization and faster queries
*/
$filter_condition = 't.department BETWEEN ? AND ?';
$args = array($deptStart, $deptEnd);
if (count($deptMulti) > 0) {
$filter_condition = 't.department IN (';
$args = array();
foreach ($deptMulti as $d) {
$filter_condition .= '?,';
$args[] = $d;
}
$filter_condition = substr($filter_condition, 0, strlen($filter_condition) - 1) . ')';
}
if ($buyer !== "" && $buyer > 0) {
$filter_condition .= ' AND s.superID=? ';
$args[] = $buyer;
/*
$superR = $dbc->execute($superP, array($buyer));
$filter_condition = 't.department IN (';
$args = array();
while ($superW = $dbc->fetch_row($superR)) {
$filter_condition .= '?,';
$args[] = $superW['dept_ID'];
}
$filter_condition = substr($filter_condition, 0, strlen($filter_condition)-1) . ')';
$filter_condition .= ' AND s.superID=?';
$args[] = $buyer;
*/
} else {
if ($buyer !== "" && $buyer == -1) {
$filter_condition = "1=1";
$args = array();
} else {
if ($buyer !== "" && $buyer == -2) {
$superR = $dbc->execute($superP, array(0));
$filter_condition = 't.department NOT IN (0,';
$args = array();
while ($superW = $dbc->fetch_row($superR)) {
$filter_condition .= '?,';
$args[] = $superW['dept_ID'];
}
$filter_condition = substr($filter_condition, 0, strlen($filter_condition) - 1) . ')';
$filter_condition .= ' AND s.superID <> 0';
}
}
}
/**
* Provide more WHERE conditions to filter irrelevant
* transaction records, as a stop-gap until this is
* handled more uniformly across the application.
*/
$filter_transactions = "t.trans_status NOT IN ('D','X','Z')\n AND t.emp_no <> 9999\n AND t.register_no <> 99";
$filter_transactions = DTrans::isValid() . ' AND ' . DTrans::isNotTesting();
/**
Select a summary table. For UPC results, per-unique-ring
summary is needed. For date/dept/weekday results the
per-department summary is fine (and a smaller table)
*/
$dlog = DTransactionsModel::selectDlog($date1, $date2);
/**
Build an appropriate query depending on the grouping option
*/
$query = "";
$superTable = $buyer !== "" && $buyer > 0 ? 'superdepts' : 'MasterSuperDepts';
$args[] = $date1 . ' 00:00:00';
$args[] = $date2 . ' 23:59:59';
$args[] = $store;
switch ($groupby) {
case 'PLU':
$query = "SELECT t.upc,\n CASE WHEN p.description IS NULL THEN t.description ELSE p.description END as description, \n SUM(CASE WHEN trans_status IN('','0','R') THEN 1 WHEN trans_status='V' THEN -1 ELSE 0 END) as rings," . DTrans::sumQuantity('t') . " as qty,\n SUM(t.total) AS total,\n d.dept_no,d.dept_name,s.superID,\n COALESCE(v.vendorName,x.distributor) AS distributor\n FROM {$dlog} as t " . DTrans::joinProducts() . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON t.department = s.dept_ID\n LEFT JOIN prodExtra as x on t.upc = x.upc\n LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID\n WHERE {$filter_condition}\n AND t.trans_type IN ('I', 'D')\n AND tdate BETWEEN ? AND ?\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY t.upc,\n CASE WHEN p.description IS NULL THEN t.description ELSE p.description END,\n CASE WHEN t.trans_status = 'R' THEN 'Refund' ELSE 'Sale' END,\n d.dept_no,d.dept_name,s.superID,distributor ORDER BY SUM(t.total) DESC";
break;
case 'Department':
$query = "SELECT t.department,d.dept_name," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales \n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department \n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY t.department,d.dept_name ORDER BY SUM(total) DESC";
break;
case 'Date':
$query = "SELECT year(tdate),month(tdate),day(tdate)," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales ,\n MAX(" . $dbc->dayofweek('tdate') . ") AS dow\n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department\n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY year(tdate),month(tdate),day(tdate) \n ORDER BY year(tdate),month(tdate),day(tdate)";
break;
case 'Weekday':
$cols = $dbc->dayofweek("tdate") . ",CASE \n WHEN " . $dbc->dayofweek("tdate") . "=1 THEN 'Sun'\n WHEN " . $dbc->dayofweek("tdate") . "=2 THEN 'Mon'\n WHEN " . $dbc->dayofweek("tdate") . "=3 THEN 'Tue'\n WHEN " . $dbc->dayofweek("tdate") . "=4 THEN 'Wed'\n WHEN " . $dbc->dayofweek("tdate") . "=5 THEN 'Thu'\n WHEN " . $dbc->dayofweek("tdate") . "=6 THEN 'Fri'\n WHEN " . $dbc->dayofweek("tdate") . "=7 THEN 'Sat'\n ELSE 'Err' END";
$query = "SELECT {$cols}," . DTrans::sumQuantity('t') . " as qty,\n SUM(total) as Sales \n FROM {$dlog} as t " . DTrans::joinDepartments() . "LEFT JOIN {$superTable} AS s ON s.dept_ID = t.department \n WHERE {$filter_condition}\n AND tdate BETWEEN ? AND ?\n AND t.trans_type IN ('I', 'D')\n AND {$filter_transactions}\n AND " . DTrans::isStoreID($store, 't') . "\n GROUP BY {$cols}\n ORDER BY " . $dbc->dayofweek('tdate');
break;
}
/**
Copy the results into an array. Date requires a
special case to combine year, month, and day into
a single field
*/
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
$ret = array();
while ($row = $dbc->fetch_array($result)) {
$record = array();
if ($groupby == "Date") {
$record[] = $row[1] . "/" . $row[2] . "/" . $row[0];
$record[] = date('l', strtotime($record[0]));
$record[] = sprintf('%.2f', $row[3]);
$record[] = sprintf('%.2f', $row[4]);
} else {
for ($i = 0; $i < $dbc->num_fields($result); $i++) {
if (preg_match('/^\\d+\\.\\d+$/', $row[$i])) {
$row[$i] = sprintf('%.2f', $row[$i]);
}
$record[] .= $row[$i];
}
}
$ret[] = $record;
}
return $ret;
}
function get_view()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
/**
Excludes:
Values with spaces (fixed in lanecode going forward)
One and two digit PLUs (likely simply miskeys)
Values with no leading zeroes (EAN-13 and UPC-A should have
at least one. I do have some values with no leading zeroes
but not sure yet what they are. Do not appear to be GTIN-14).
*/
$dbc = FannieDB::get($FANNIE_OP_DB);
$query = "SELECT t.upc, COUNT(t.upc) AS instances,\n MIN(datetime) as oldest,\n MAX(datetime) as newest,\n p.description as prod,\n MAX(v.description) as vend, MAX(n.vendorName) as vendorName, MAX(v.srp) as srp\n FROM " . $FANNIE_TRANS_DB . $dbc->sep() . "transarchive AS t\n " . DTrans::joinProducts('t') . "\n LEFT JOIN vendorItems AS v ON t.upc=v.upc\n LEFT JOIN vendors AS n ON v.vendorID=n.vendorID\n WHERE t.trans_type='L' AND t.description='BADSCAN'\n AND t.upc NOT LIKE '% %'\n AND t.upc NOT LIKE '00000000000%'\n AND (t.upc NOT LIKE '00000000%' OR p.upc IS NOT NULL OR v.upc IS NOT NULL)";
if ($this->date_restrict) {
$query .= ' AND datetime >= ' . date('\'Y-m-d 00:00:00\'', strtotime('-8 days'));
}
$query .= "GROUP BY t.upc, p.description\n ORDER BY t.upc DESC";
if ($this->date_restrict == 2) {
$query = str_replace('transarchive', 'dtransactions', $query);
}
$result = $dbc->query($query);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$data[] = $row;
}
// stick a total in the cache along with SQL results
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$query = "SELECT COUNT(*) FROM transarchive WHERE trans_type='I' AND upc <> '0'";
$result = $dbc->query($query);
$row = $dbc->fetch_row($result);
$data['itemTTL'] = $row[0];
$ret = '';
$ret .= '<div class="nav">';
$ret .= '<a href="BadScanTool.php?lastquarter=1"
class="btn btn-default navbar-btn' . (!$this->date_restrict ? ' active' : '') . '">View Last Quarter</a>';
$ret .= ' ';
$ret .= '<a href="BadScanTool.php"
class="btn btn-default navbar-btn' . ($this->date_restrict == 1 ? ' active' : '') . '">View Last Week</a>';
$ret .= ' ';
$ret .= '<a href="BadScanTool.php?today=1"
class="btn btn-default navbar-btn' . ($this->date_restrict == 2 ? ' active' : '') . '">View Today</a>';
$ret .= '</div>';
$ret .= '<br /><b>Show</b>: ';
$ret .= '<input type="radio" name="rdo" id="rdoa" onclick="showAll();" />
<label for="rdoa">All</label>';
$ret .= ' ';
$ret .= '<input type="radio" name="rdo" id="rdom" onclick="showMultiple();" />
<label for="rdom">Repeats</label>';
$ret .= ' ';
$ret .= '<input type="radio" name="rdo" id="rdof" onclick="showFixable();" checked />
<label for="rdof">Fixable</label>';
$ret .= '<br />';
$ret .= '<div class="well">';
$ret .= '<span class="alert-success">Green items have been entered in POS</span>. ';
$ret .= '<span class="alert-danger">Red items can be added from vendor catalogs</span>. ';
$ret .= '<span class="alert-info">Blue items can also be added from vendor catalogs but
may not be needed. All scans are within a 5 minute window. May indicate a special
order case scanned by mistake or a bulk purchase in a barcoded container.</span> ';
$ret .= 'Other items are not identifiable with available information';
$ret .= '</div>';
$ret .= '<table id="scantable" class="table"><thead>';
$ret .= '<tr id="tableheader"><th>UPC</th><th># Scans</th><th>Oldest</th><th>Newest</th>
<th>In POS</th><th>In Vendor Catalog</th><th>SRP</th></tr>';
$ret .= '</thead><tbody>';
$scanCount = 0;
foreach ($data as $row) {
if (count($row) == 1) {
// cached item total
continue;
}
$css = '';
$fixButton = '';
$span = strtotime($row['newest']) - strtotime($row['oldest']);
if (!empty($row['prod'])) {
$css = 'class="fixed alert alert-success collapse"';
} else {
if (!empty($row['vend']) && !empty($row['srp'])) {
if ($span > 300) {
$css = 'class="fixable alert alert-danger"';
} else {
$css = 'class="semiFixable alert alert-info"';
}
$fixButton = ' <a href="ItemEditorPage.php?searchupc= ' . $row['upc'] . '" target="_new' . $row['upc'] . '">ADD</a>';
} else {
if ($row['instances'] == 1) {
$css = 'class="loner collapse"';
} else {
$css = 'class="collapse"';
}
}
}
$ret .= sprintf('<tr %s><td>%s</td><td>%d</td><td>%s</td><td>%s</td>
<td>%s</td><td>%s</td><td>%s</td>
<td><a href="OpenRingReceipts.php?upc=%s&date1=%s&date2=%s">View Receipts</a></td>
</tr>', $css, $row['upc'], $row['instances'], $row['oldest'], $row['newest'], !empty($row['prod']) ? "Yes ({$row['prod']})" : 'No', !empty($row['vend']) ? "Yes ({$row['vendorName']} {$row['vend']})" : 'No', !empty($row['srp']) ? $row['srp'] . $fixButton : 'n/a', $row['upc'], $row['oldest'], $row['newest']);
$scanCount += $row['instances'];
}
$ret .= '</tbody></table>';
$ret .= '<div id="ratio">';
$ret .= sprintf('Approx. bad scan rate: %.2f%%', $data['itemTTL'] == 0 ? 0 : (double) $scanCount / ((double) $data['itemTTL'] != 0) * 100);
$ret .= '</div>';
$this->addScript('../src/javascript/tablesorter/jquery.tablesorter.min.js');
$this->addOnloadCommand("\$('#scantable').tablesorter();\n");
return $ret;
}
public function run()
{
global $argv, $FANNIE_OP_DB, $FANNIE_PLUGIN_SETTINGS;
$dbc = FannieDB::get($FANNIE_OP_DB);
$iso_week = date('W');
$iso_week--;
$year = date('Y');
if ($iso_week <= 0) {
$iso_week = 52;
$year--;
}
$upload = true;
/**
Handle additional args
*/
if (isset($argv) && is_array($argv)) {
foreach ($argv as $arg) {
if (is_numeric($arg)) {
$iso_week = $arg;
} else {
if ($arg == '--file') {
$upload = false;
}
}
}
}
/**
Keep SPINS week number separate for logging purposes
*/
$spins_week = $iso_week;
if (isset($FANNIE_PLUGIN_SETTINGS['SpinsOffset'])) {
$iso_week += $FANNIE_PLUGIN_SETTINGS['SpinsOffset'];
}
// First day of ISO week is a Monday
$start = strtotime($year . 'W' . str_pad($iso_week, 2, '0', STR_PAD_LEFT));
// if the SpinsOffset results in non-existant week 0,
// use ISO week 1 and go back seven days
if ($iso_week == 0) {
$start = strtotime($year . 'W01');
$start = mktime(0, 0, 0, date('n', $start), date('j', $start) - 7, date('Y', $start));
}
// walk forward to Sunday
$end = $start;
while (date('w', $end) != 0) {
$end = mktime(0, 0, 0, date('n', $end), date('j', $end) + 1, date('Y', $end));
}
$dlog = DTransactionsModel::selectDlog(date('Y-m-d', $start), date('Y-m-d', $end));
$lastDay = date("M d, Y", $end) . ' 11:59PM';
$this->cronMsg('SPINS data for week #' . $spins_week . '(' . date('Y-m-d', $start) . ' to ' . date('Y-m-d', $end) . ')', FannieLogger::INFO);
// Odd "CASE" statement is to deal with special order
// line items the have case size & number of cases
$dataQ = "SELECT d.upc, p.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} AS d\n " . DTrans::joinProducts('d', 'p', 'INNER') . "\n WHERE p.Scale = 0\n AND d.upc > '0000000999999' \n AND tdate BETWEEN ? AND ?\n GROUP BY d.upc, p.description";
$filename = date('mdY', $end) . '.csv';
$outfile = sys_get_temp_dir() . "/" . $filename;
$fp = fopen($outfile, "w");
$dataP = $dbc->prepare($dataQ);
$args = array(date('Y-m-d 00:00:00', $start), date('Y-m-d 23:59:59', $end));
$dataR = $dbc->execute($dataP, $args);
while ($row = $dbc->fetch_row($dataR)) {
for ($i = 0; $i < 4; $i++) {
if ($i == 2 || $i == 3) {
$row[$i] = sprintf('%.2f', $row[$i]);
}
fwrite($fp, "\"" . $row[$i] . "\",");
}
fwrite($fp, "\"" . $row[4] . "\"\n");
}
fclose($fp);
if ($upload) {
$conn_id = ftp_connect('ftp.spins.com');
$login_id = ftp_login($conn_id, $FANNIE_PLUGIN_SETTINGS['SpinsFtpUser'], $FANNIE_PLUGIN_SETTINGS['SpinsFtpPw']);
if (!$conn_id || !$login_id) {
$this->cronMsg('FTP Connection failed', FannieLogger::ERROR);
} else {
ftp_chdir($conn_id, "data");
ftp_pasv($conn_id, true);
$uploaded = ftp_put($conn_id, $filename, $outfile, FTP_ASCII);
if (!$uploaded) {
$this->cronMsg('FTP upload failed', FannieLogger::ERROR);
} else {
$this->cronMsg('FTP upload successful', FannieLogger::INFO);
}
ftp_close($conn_id);
}
unlink($outfile);
} else {
rename($outfile, './' . $filename);
$this->cronMsg('Generated file: ' . $filename, FannieLogger::INFO);
}
}
protected function get_id_view()
{
$query = '
SELECT l.upc,
CASE WHEN u.brand IS NOT NULL AND u.brand <> \'\' THEN u.brand ELSE p.brand END as brand,
CASE WHEN u.description IS NOT NULL AND u.description <> \'\' THEN u.description ELSE p.description END as description,
p.normal_price,
l.salePrice,
l.signMultiplier
FROM batchList AS l
' . DTrans::joinProducts('l', 'p', 'INNER') . '
LEFT JOIN productUser AS u ON l.upc=u.upc
WHERE l.batchID=? ';
$args = array($this->id);
if ($this->config->get('STORE_MODE') === 'HQ') {
$query .= ' AND p.store_id=? ';
$args[] = $this->config->get('STORE_ID');
}
$query .= ' ORDER BY l.upc';
$prep = $this->connection->prepare($query);
$res = $this->connection->execute($prep, $args);
$rows = array();
while ($row = $this->connection->fetchRow($res)) {
$rows[] = $row;
}
$query = '
SELECT l.upc,
\'\' AS brand,
c.likeCodeDesc AS description,
0 AS normal_price,
l.salePrice,
l.signMultiplier
FROM batchList AS l
LEFT JOIN likeCodes AS c ON l.upc=' . $this->connection->concat("'LC'", 'c.likeCode', '') . '
WHERE l.batchID=?
AND l.upc LIKE \'LC%\'';
$args = array($this->id);
$prep = $this->connection->prepare($query);
$res = $this->connection->execute($prep, $args);
while ($row = $this->connection->fetchRow($res)) {
$rows[] = $row;
}
$args = array($this->id);
while ($row = $this->connection->fetchRow($res)) {
$rows[] = $row;
}
$ret = '<form method="post">
<table class="table table-bordered"><thead><tr>
<th>UPC</th><th>Brand</th><th>Description</th>
<th>Normal Price</th><th>Sale Price</th><th>Sign</th>
</tr></thead><tbody>';
$styles = $this->getStyles();
foreach ($rows as $row) {
$ret .= sprintf('<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%.2f</td>
<td>%.2f</td>
<td><select class="form-control" name="style[]">
%s
</select>
<input type="hidden" name="upc[]" value="%s" />
</td>
</tr>', \COREPOS\Fannie\API\lib\FannieUI::itemEditorLink($row['upc']), $row['brand'], $row['description'], $row['normal_price'], $row['salePrice'], $this->styleToOptions($styles, $row['signMultiplier']), $row['upc']);
}
$ret .= '</tbody></table>
<p>
<button type="submit" class="btn btn-default btn-core">Save</button>
<a href="EditBatchPage.php?id=' . $this->id . '" class="btn btn-default btn-reset">Back to Batch</a>
</p>
<input type="hidden" name="id" value="' . $this->id . '" />
</form>';
return $ret;
}
CORE-POS is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
in the file license.txt along with IT CORE; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*********************************************************************************/
include dirname(__FILE__) . '/../../config.php';
if (!class_exists('FannieAPI')) {
include_once $FANNIE_ROOT . 'classlib2.0/FannieAPI.php';
}
if (basename($_SERVER['PHP_SELF']) != basename(__FILE__)) {
return;
}
$dbc = FannieDB::get($FANNIE_OP_DB);
switch (FormLib::get_form_value('action')) {
case 'fetch':
$prep = $dbc->prepare_statement("SELECT u.upc,p.description FROM\n upcLike AS u \n " . DTrans::joinProducts('u', 'p', 'INNER') . "\n WHERE u.likeCode=?\n ORDER BY p.description");
$res = $dbc->exec_statement($prep, array(FormLib::get_form_value('lc', 0)));
$ret = "";
while ($row = $dbc->fetch_row($res)) {
$ret .= "<a style=\"font-size:90%;\" href={$FANNIE_URL}item/itemMaint.php?upc={$row['0']}>";
$ret .= $row[0] . "</a> " . substr($row[1], 0, 25) . "<br />";
}
echo $ret;
break;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$deptStart = FormLib::get('deptStart', 1);
$deptEnd = FormLib::get('deptEnd', 1);
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get('buyer', '');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
/**
Report Query notes:
* Combining vendorName and prodExtra.distributor is a nod to
legacy data. Eventually data should be fully normalized on
products.default_vendor_id
* Excluding prodExtra.distributor empty string combines those
records with SQL NULL. Having two different "blank" rows
is confusing for users.
* Joins are only needed is a super department condition is
involved. WHERE clause changes similarly.
*/
$query = '
SELECT COALESCE(v.vendorName, x.distributor) AS vendor,
' . DTrans::sumQuantity('t') . ' AS qty,
SUM(t.total) AS ttl
FROM ' . $dlog . ' AS t
' . DTrans::joinProducts('t', 'p', 'LEFT') . '
LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID
LEFT JOIN prodExtra AS x ON p.upc=x.upc
';
if ($buyer !== '' && $buyer > -1) {
$query .= ' LEFT JOIN superdepts AS s ON t.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= ' LEFT JOIN MasterSuperDepts AS s ON t.department=s.dept_ID ';
}
$query .= '
WHERE t.tdate BETWEEN ? AND ?
AND t.trans_type IN (\'I\',\'D\') ';
$args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
if ($buyer !== '') {
if ($buyer == -2) {
$query .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$query .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args, 't');
$query .= $conditional;
}
$query .= '
GROUP BY COALESCE(v.vendorName, x.distributor)
ORDER BY SUM(total) DESC';
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, $args);
$data = array();
$total_sales = 0.0;
while ($w = $dbc->fetch_row($result)) {
$data[] = array($w['vendor'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['ttl']), 0.0);
$total_sales += $w['ttl'];
}
for ($i = 0; $i < count($data); $i++) {
$data[$i][3] = sprintf('%.2f%%', $data[$i][2] / $total_sales * 100);
$data[$i][2] = '$' . $data[$i][2];
}
return $data;
}
public function process_file($linedata)
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$UPC = $this->get_column_index('upc');
$SKU = $this->get_column_index('sku');
$PRICE = $this->get_column_index('price');
$rm_checks = FormLib::get('rm_cds') != '' ? true : false;
$ruleType = FormLib::get('ruleType');
$review = FormLib::get('reviewDate');
$upcP = $dbc->prepare('SELECT upc, price_rule_id FROM products WHERE upc=? AND inUse=1');
$ruleP = $dbc->prepare('SELECT * FROM PriceRules WHERE priceRuleID=?');
$skuP = $dbc->prepare('
SELECT s.upc,
p.price_rule_id
FROM vendorSKUtoPLU AS s
INNER JOIN vendors AS v ON s.vendorID=v.vendorID
' . DTrans::joinProducts('s', 'p', 'INNER') . '
WHERE s.sku=?
AND v.vendorName LIKE \'%UNFI%\'');
$insP = $dbc->prepare('
INSERT INTO PriceRules
(priceRuleTypeID, maxPrice, reviewDate, details)
VALUES
(?, ?, ?, ?)
');
$upP = $dbc->prepare('
UPDATE PriceRules
SET priceRuleTypeID=?,
maxPrice=?,
reviewDate=?,
details=?
WHERE priceRuleID=?');
$extraP = $dbc->prepare('UPDATE prodExtra SET variable_pricing=1 WHERE upc=?');
$prodP = $dbc->prepare('UPDATE products SET price_rule_id=? WHERE upc=?');
foreach ($linedata as $data) {
if (!is_array($data)) {
continue;
}
$upc = str_replace("-", "", $data[$UPC]);
$upc = str_replace(" ", "", $upc);
if ($rm_checks) {
$upc = substr($upc, 0, strlen($upc) - 1);
}
$upc = BarcodeLib::padUPC($upc);
$rule_id = 0;
// try to find item by SKU if not in products
$lookup = $dbc->execute($upcP, array($upc));
if ($dbc->numRows($lookup) == 0 && $SKU !== false) {
$sku = str_replace('-', '', $data[$SKU]);
$found = false;
$look2 = $dbc->exec_statement($skuP, array($sku));
if ($dbc->numRows($look2)) {
$w = $dbc->fetchRow($look2);
$upc = $w['upc'];
$rule_id = $w['price_rule_id'];
$found = true;
}
$sku = str_pad($sku, 7, '0', STR_PAD_LEFT);
$look3 = $dbc->execute($skuP, array($sku));
if ($dbc->numRows($look3)) {
$w = $dbc->fetchRow($look3);
$upc = $w['upc'];
$rule_id = $w['price_rule_id'];
$found = true;
}
if (!$found) {
continue;
}
} else {
$w = $dbc->fetchRow($lookup);
$rule_id = $w['price_rule_id'];
}
$price = trim($data[$PRICE], "\$ ");
if (strstr($price, '-')) {
// pull the max from a range of prices
list($garbage, $price) = explode('-', $price, 2);
$price = trim($price);
}
if (!is_numeric($price)) {
continue;
}
$ruleR = $dbc->execute($ruleP, array($rule_id));
if ($rule_id > 1 && $dbc->numRows($ruleR)) {
// update existing rule with latest price
$args = array($ruleType, $price, $review, 'NCG MAX ' . $price, $rule_id);
$dbc->execute($upP, $args);
$dbc->execute($extraP, array($upc));
} else {
// create a new pricing rule
// attach it to the item
$args = array($ruleType, $price, $review, 'NCG MAX ' . $price);
$dbc->execute($insP, $args);
$rule_id = $dbc->insertID();
$dbc->execute($extraP, array($upc));
$dbc->execute($prodP, array($rule_id, $upc));
}
}
return true;
}
private function upcQuery($dlog, $items)
{
$args = array_filter($items, function ($i) {
return trim($i) !== '' ? true : false;
});
$args = array_map(function ($i) {
$item = trim($i);
// trim check digit based on dashes or spacing
if (strlen($item) > 2 && ($item[strlen($item) - 2] == ' ' || $item[strlen($item) - 2] == '-') && is_numeric($item[strlen($item) - 1])) {
$item = substr($item, 0, strlen($item) - 2);
}
$item = str_replace(' ', '', $item);
$item = str_replace('-', '', $item);
return BarcodeLib::padUPC($item);
}, $items);
$inStr = str_repeat('?,', count($args));
$inStr = substr($inStr, 0, strlen($inStr) - 1);
$query = '
SELECT t.upc,
p.brand,
p.description,
' . DTrans::sumQuantity('t') . ' as qty,
CASE WHEN p.scale=1 THEN \'LB\' ELSE p.size END AS size,
p.cost,
p.normal_price
FROM ' . $dlog . ' AS t
' . DTrans::joinProducts() . '
WHERE t.upc IN (' . $inStr . ')
AND t.tdate BETWEEN ? AND ?
GROUP BY t.upc,
p.brand,
p.description';
return array($query, $args);
}
private function getList()
{
$settings = $this->config->get('PLUGIN_SETTINGS');
$prep = $this->connection->prepare('
SELECT s.upc,
p.brand,
p.description,
p.size,
s.quantity as qty,
v.sku,
n.vendorName
FROM ' . $settings['ShelfAuditDB'] . $this->connection->sep() . 'SaList AS s
' . DTrans::joinProducts('s') . '
LEFT JOIN vendorItems AS v ON p.upc=v.upc AND p.default_vendor_id=v.vendorID
LEFT JOIN vendors AS n ON p.default_vendor_id=n.vendorID
WHERE s.clear=0
AND s.quantity <> 0
ORDER BY s.tdate DESC
');
$res = $this->connection->execute($prep);
$ret = '
<div class="table-responsive">
<table class="table table-bordered table-striped small">
<tr>
<th>UPC</th>
<th>SKU</th>
<th>Vendor</th>
<th>Brand</th>
<th>Description</th>
<th>Size</th>
<th>Qty</th>
</tr>';
while ($row = $this->connection->fetchRow($res)) {
$ret .= sprintf('<tr>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%d</td>
</tr>', $row['upc'], $row['sku'], $row['vendorName'], $row['brand'], $row['description'], $row['size'], $row['qty']);
}
$ret .= '</table></div>';
$ret .= '<p>
<a href="?clear=1" class="btn btn-default btn-danger"
onclick="return window.confirm(\'Clear list?\');">
Clear List
</a>
</p>';
return $ret;
}
public static function batchNotification($batchID, $upc, $type, $is_likecode = false)
{
$conf = \FannieConfig::factory();
$dbc = \FannieDB::getReadOnly($conf->get('OP_DB'));
$likecode = '';
$desc = '';
$dept = 0;
if ($is_likecode) {
if (substr($upc, 0, 2) == 'LC') {
$likecode = substr($upc, 2);
} else {
$likecode = $upc;
}
// upc is a like code. find the description
// and a valid upc (hence inner join)
$infoQ = 'SELECT p.department,
l.likeCodeDesc
FROM upcLike AS u
' . DTrans::joinProducts('u', 'p', 'INNER') . '
LEFT JOIN likeCodes AS l ON u.likeCode=l.likeCode
WHERE u.likeCode=?';
$infoP = $dbc->prepare($infoQ);
$infoR = $dbc->execute($infoP, array($likecode));
if ($dbc->num_rows($infoR) == 0) {
// invalid like code
return false;
}
$infoW = $dbc->fetch_row($infoR);
$desc = $infoW['likeCodeDesc'];
$dept = $infoW['department'];
} else {
$product = new \ProductsModel($dbc);
$product->upc($upc);
$product->load();
$desc = $product->description();
$dept = $product->department();
}
$to_addr = self::getAddresses($dept);
if ($to_addr === false) {
// no one set to receive notices
return false;
}
$batch = new \BatchesModel($dbc);
$batch->batchID($batchID);
$batch->load();
$batchList = new \BatchListModel($dbc);
$batchList->upc($upc);
$batchList->batchID($batchID);
$batchList->load();
$subject = "Batch Update notification: " . $batch->batchName();
$message = "Batch " . $batch->batchName() . " has been changed\n";
if ($is_likecode) {
$message .= 'Like code ' . $likecode . '(' . $desc . ') ';
} else {
$message .= 'Item ' . $upc . '(' . $desc . ') ';
}
switch ($type) {
case self::BATCH_ADD:
$message .= "has been added to the batch\n";
$message .= 'Sale Price: $' . $batchList->salePrice() . "\n";
break;
case self::BATCH_EDIT:
$message .= "has been re-priced\n";
$message .= 'Sale Price: $' . $batchList->salePrice() . "\n";
break;
case self::BATCH_DELETE:
$message .= "has been deleted from the batch\n";
break;
default:
$message .= "may have experienced unknown changes\n";
return false;
// remove after testing; don't send lots of these in error
break;
}
$message .= "\n";
$message .= "View this batch:\n";
$url = $conf->get('URL');
$server_name = filter_input(INPUT_SERVER, 'SERVER_NAME');
$message .= "http://{$server_name}{$url}batches/newbatch/EditBatchPage.php?id={$batchID}\n";
$message .= "\n";
$message .= "View this item:\n";
$message .= "http://{$server_name}/{$url}item/ItemEditorPage.php?searchupc={$upc}\n";
$message .= "\n";
$username = \FannieAuth::checkLogin();
if (!$username) {
$username = '******';
}
$message .= "This change was made by user {$username}\n";
$from = "From: automail\r\n";
mail($to_addr, $subject, $message, $from);
return true;
}
protected function unrollLikeCode($dbc, $code, $item)
{
$likeP = $dbc->prepare('
SELECT u.upc,
' . \COREPOS\Fannie\API\item\ItemText::longBrandSQL('s', 'p') . ',
CASE WHEN s.description IS NULL OR s.description=\'\' THEN l.likeCodeDesc ELSE s.description END AS likeCodeDesc,
p.normal_price,
p.scale,
p.numflag,
p.size,
COALESCE(s.signCount, 1) AS signCount
FROM upcLike AS u
INNER JOIN likeCodes AS l ON u.likeCode=l.likeCode
' . \DTrans::joinProducts('u', 'p', 'INNER') . '
LEFT JOIN productUser AS s ON u.upc=s.upc
WHERE u.likeCode=?
ORDER BY u.upc
');
$info = $dbc->getRow($likeP, array($code));
$item['description'] = $info['likeCodeDesc'];
$item['brand'] = $info['brand'];
$item['posDescription'] = $info['likeCodeDesc'];
$item['nonSalePrice'] = $info['normal_price'];
$item['scale'] = $info['scale'];
$item['numflag'] = $info['numflag'];
$item['upc'] = $info['upc'];
$item['size'] = $info['size'];
$item['signCount'] = $info['signCount'];
return $item;
}
/**
Generate FROM and WHERE clauses with appropriate parameters
and joins based on the standard form submissions.
@return [keyed array]
- query [string] from and where clauses
- args [array] corresponding parameters
*/
public static function standardItemFromWhere()
{
$op_db = FannieConfig::config('OP_DB');
$dbc = FannieDB::getReadOnly($op_db);
$start_date = self::getDate('date1', date('Y-m-d'));
$end_date = self::getDate('date2', date('Y-m-d'));
$dlog = DTransactionsModel::selectDlog($start_date, $end_date);
$lookupType = self::get('lookup-type', 'dept');
$query = '
FROM ' . $dlog . ' AS t
LEFT JOIN departments AS d ON t.department=d.dept_no
' . DTrans::joinProducts('t') . '
LEFT JOIN MasterSuperDepts AS m ON t.department=m.dept_ID
LEFT JOIN subdepts AS b ON p.subdept=b.subdept_no
LEFT JOIN vendors AS v ON p.default_vendor_id=v.vendorID
LEFT JOIN prodExtra AS x ON t.upc=x.upc ';
$args = array();
switch ($lookupType) {
case 'dept':
$super = FormLib::get('super-dept');
if ($super !== '' && $super >= 0) {
$query .= ' LEFT JOIN superdepts AS s ON t.department=s.dept_ID ';
}
break;
case 'manu':
break;
case 'vendor':
$query .= ' LEFT JOIN vendors AS z ON x.distributor=z.vendorName ';
break;
case 'likecode':
$query .= ' LEFT JOIN upcLike AS u ON t.upc=u.upc ';
break;
}
$query .= ' WHERE t.tdate BETWEEN ? AND ? ';
$args[] = $start_date . ' 00:00:00';
$args[] = $end_date . ' 23:59:59';
switch ($lookupType) {
case 'dept':
$super = FormLib::get('super-dept');
if ($super !== '' && $super >= 0) {
$query .= ' AND s.superID=? ';
$args[] = $super;
if (is_array(FormLib::get('departments')) && count(FormLib::get('departments')) > 0) {
$query .= ' AND t.department IN (';
foreach (FormLib::get('departments') as $d) {
$query .= '?,';
$args[] = $d;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
} elseif (FormLib::get('dept-start') !== '' && FormLib::get('dept-end') !== '') {
$query .= ' AND t.department BETWEEN ? AND ? ';
$args[] = FormLib::get('dept-start');
$args[] = FormLib::get('dept-end');
}
} elseif ($super !== '' && $super == -2) {
$query .= ' AND m.superID <> 0 ';
if (is_array(FormLib::get('departments')) && count(FormLib::get('departments')) > 0) {
$query .= ' AND t.department IN (';
foreach (FormLib::get('departments') as $d) {
$query .= '?,';
$args[] = $d;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
} elseif (FormLib::get('dept-start') !== '' && FormLib::get('dept-end') !== '') {
$query .= ' AND t.department BETWEEN ? AND ? ';
$args[] = FormLib::get('dept-start');
$args[] = FormLib::get('dept-end');
}
} elseif ($super === '') {
if (is_array(FormLib::get('departments')) && count(FormLib::get('departments')) > 0) {
$query .= ' AND t.department IN (';
foreach (FormLib::get('departments') as $d) {
$query .= '?,';
$args[] = $d;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
} else {
$query .= ' AND t.department BETWEEN ? AND ? ';
$args[] = FormLib::get('dept-start', 1);
$args[] = FormLib::get('dept-end', 1);
}
}
if (is_array(FormLib::get('subdepts')) && count(FormLib::get('subdepts')) > 0) {
$query .= ' AND p.subdept IN (';
foreach (FormLib::get('subdepts') as $s) {
$query .= '?,';
$args[] = $s;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
}
break;
case 'manu':
$mtype = FormLib::get('mtype');
if ($mtype == 'prefix') {
$query .= ' AND t.upc LIKE ? ';
$args[] = '%' . FormLib::get('manufacturer') . '%';
} else {
$query .= ' AND (p.brand LIKE ? OR x.manufacturer LIKE ?) ';
$manu = '%' . FormLib::get('manufacturer') . '%';
$args[] = $manu;
$args[] = $manu;
$optimizeP = $dbc->prepare('
SELECT p.department
FROM products AS p
LEFT JOIN prodExtra AS x ON p.upc=x.upc
WHERE (p.brand LIKE ? OR x.manufacturer LIKE ?)
GROUP BY p.department');
$optimizeR = $dbc->execute($optimizeP, array($manu, $manu));
$dept_in = '';
while ($optimizeW = $dbc->fetch_row($optimizeR)) {
$dept_in .= '?,';
$args[] = $optimizeW['department'];
}
if ($dept_in !== '') {
$dept_in = substr($dept_in, 0, strlen($dept_in) - 1);
$query .= ' AND t.department IN (' . $dept_in . ') ';
}
}
break;
case 'vendor':
$query .= ' AND (p.default_vendor_id=? OR z.vendorID=?) ';
$vID = FormLib::get('vendor', 1);
$args[] = $vID;
$args[] = $vID;
$optimizeP = $dbc->prepare('
SELECT p.department
FROM products AS p
LEFT JOIN prodExtra AS x ON p.upc=x.upc
LEFT JOIN vendors AS v ON x.distributor=v.vendorName
WHERE (p.default_vendor_id=? OR v.vendorID=?
GROUP BY p.department');
$optimizeR = $dbc->execute($optimizeP, array($vID, $vID));
$dept_in = '';
while ($optimizeW = $dbc->fetch_row($optimizeR)) {
$dept_in .= '?,';
$args[] = $optimizeW['department'];
}
if ($dept_in !== '') {
$dept_in = substr($dept_in, 0, strlen($dept_in) - 1);
$query .= ' AND t.department IN (' . $dept_in . ') ';
}
break;
case 'likecode':
$query .= ' AND u.likeCode BETWEEN ? AND ? ';
$args[] = FormLib::get('lc-start', 1);
$args[] = FormLib::get('lc-end', 1);
$optimizeP = $dbc->prepare('
SELECT p.department
FROM products AS p
INNER JOIN upcLike AS u ON p.upc=u.upc
WHERE u.likeCode BETWEEN ? AND ?
GROUP BY p.department');
$optimizeR = $dbc->execute($optimizeP, array(FormLib::get('lc-start', 1), FormLib::get('lc-end', 1)));
$dept_in = '';
while ($optimizeW = $dbc->fetch_row($optimizeR)) {
$dept_in .= '?,';
$args[] = $optimizeW['department'];
}
if ($dept_in !== '') {
$dept_in = substr($dept_in, 0, strlen($dept_in) - 1);
$query .= ' AND t.department IN (' . $dept_in . ') ';
}
break;
case 'u':
$upcs = FormLib::get('u', array());
if (count($upcs) == 0) {
$upcs[] = 'NOTREALUPC';
}
$query .= ' AND t.upc IN (';
foreach ($upcs as $u) {
$query .= '?,';
$args[] = BarcodeLib::padUPC($u);
}
$query = substr($query, 0, strlen($query) - 1) . ') ';
break;
}
return array('query' => $query, 'args' => $args);
}
public function run()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB, $FANNIE_ARCHIVE_DB;
$sql = FannieDB::get($FANNIE_OP_DB);
$chk = $sql->query("TRUNCATE TABLE batchMergeTable");
if ($chk === false) {
$this->cronMsg("Could not truncate batchMergeTable", FannieLogger::WARNING);
}
$chk = $sql->query("INSERT INTO batchMergeTable\n SELECT b.startDate,b.endDate,p.upc,p.description,b.batchID\n FROM batches AS b LEFT JOIN batchList AS l\n ON b.batchID=l.batchID \n " . DTrans::joinProducts('l', 'p', 'INNER'));
if ($chk === false) {
$this->cronMsg("Could not load batch reporting data for UPCs", FannieLogger::WARNING);
}
$chk = $sql->query("INSERT INTO batchMergeTable \n SELECT b.startDate, b.endDate, p.upc, p.description, b.batchID\n FROM batchList AS l LEFT JOIN batches AS b\n ON b.batchID=l.batchID INNER JOIN upcLike AS u\n ON l.upc = " . $sql->concat("'LC'", $sql->convert('u.likeCode', 'CHAR'), '') . "\n " . DTrans::joinProducts('u', 'p', 'INNER') . "\n WHERE p.upc IS NOT NULL");
if ($chk === false) {
$this->cronMsg("Could not load batch reporting data for likecodes", FannieLogger::WARNING);
}
$sql = FannieDB::get($FANNIE_TRANS_DB);
$cashierPerformanceSQL = "\n SELECT\n min(tdate) as proc_date,\n max(emp_no) as emp_no,\n max(trans_num) as Trans_Num,\n min(tdate) as startTime,\n max(tdate) as endTime,\n CASE WHEN " . $sql->seconddiff('min(tdate)', 'max(tdate)') . " =0 \n then 1 else \n " . $sql->seconddiff('min(tdate)', 'max(tdate)') . "\n END as transInterval,\n sum(CASE WHEN abs(quantity) > 30 THEN 1 else abs(quantity) END) as items,\n Count(upc) as rings,\n SUM(case when trans_status = 'V' then 1 ELSE 0 END) AS Cancels,\n max(card_no) as card_no\n from dlog_90_view \n where trans_type IN ('I','D','0','C')\n group by year(tdate),month(tdate),day(tdate),trans_num";
if (!$sql->isView('CashPerformDay')) {
$chk = $sql->query("TRUNCATE TABLE CashPerformDay");
if ($chk === false) {
$this->cronMsg("Could not truncate CashPerformDay", FannieLogger::WARNING);
}
$chk = $sql->query("INSERT INTO CashPerformDay " . $cashierPerformanceSQL);
if ($chk === false) {
$this->cronMsg("Could not load data for CashPerformDay", FannieLogger::WARNING);
}
}
if ($sql->tableExists('CashPerformDay_cache')) {
$chk = $sql->query("TRUNCATE TABLE CashPerformDay_cache");
if ($chk === false) {
$this->cronMsg("Could not truncate CashPerformDay_cache", FannieLogger::WARNING);
}
$chk = $sql->query("INSERT INTO CashPerformDay_cache " . $cashierPerformanceSQL);
if ($chk === false) {
$this->cronMsg("Could not load data for CashPerformDay_cache", FannieLogger::WARNING);
}
}
$sql = FannieDB::get($FANNIE_ARCHIVE_DB);
if ($sql->table_exists("reportDataCache")) {
$sql->query("DELETE FROM reportDataCache WHERE expires < " . $sql->now());
}
$daily = \COREPOS\Fannie\API\data\DataCache::fileCacheDir('daily');
if ($daily) {
$dh = opendir($daily);
while (($file = readdir($dh)) !== false) {
if (is_file($daily . '/' . $file)) {
unlink($daily . '/' . $file);
}
}
closedir($dh);
}
$monthly = \COREPOS\Fannie\API\data\DataCache::fileCacheDir('monthly');
if ($monthly && date('j') == 1) {
$dh = opendir($monthly);
while (($file = readdir($dh)) !== false) {
if (is_file($monthly . '/' . $file)) {
unlink($monthly . '/' . $file);
}
}
closedir($dh);
}
}
/**
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, 'field') || !property_exists($args, 'search')) {
// missing required arguments
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
} else {
if (strlen($args->search) < 1) {
// search term is too short
$ret['error'] = array('code' => -32602, 'message' => 'Invalid parameters');
return $ret;
}
}
$dbc = \FannieDB::getReadOnly(\FannieConfig::factory()->get('OP_DB'));
switch (strtolower($args->field)) {
case 'item':
$res = false;
if (!is_numeric($args->search)) {
$prep = $dbc->prepare('SELECT p.upc,
p.description
FROM products AS p
LEFT JOIN productUser AS u ON u.upc=p.upc
WHERE p.description LIKE ?
OR p.brand LIKE ?
OR u.description LIKE ?
OR u.brand LIKE ?
GROUP BY p.upc,
p.description
ORDER BY p.description');
$term = '%' . $args->search . '%';
$res = $dbc->execute($prep, array($term, $term, $term, $term));
} elseif (ltrim($args->search, '0') != '') {
$prep = $dbc->prepare('
SELECT p.upc,
p.upc AS description
FROM products AS p
WHERE p.upc LIKE ?
GROUP BY p.upc');
$res = $dbc->execute($prep, array('%' . $args->search . '%'));
}
while ($res && ($row = $dbc->fetch_row($res))) {
$ret[] = array('label' => $row['description'], 'value' => $row['upc']);
}
case 'brand':
$prep = $dbc->prepare('SELECT brand
FROM products
WHERE brand LIKE ?
GROUP BY brand
ORDER BY brand');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['brand'];
}
return $ret;
case 'long_brand':
$prep = $dbc->prepare('
SELECT u.brand
FROM productUser AS u
' . DTrans::joinProducts('u', 'p', 'INNER') . '
WHERE u.brand LIKE ?
GROUP BY u.brand
ORDER BY u.brand');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['brand'];
}
return $ret;
case 'vendor':
$prep = $dbc->prepare('SELECT vendorID,
vendorName
FROM vendors
WHERE vendorName LIKE ?
ORDER BY vendorName');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['vendorName'];
}
if ($dbc->tableExists('prodExtra')) {
$prep = $dbc->prepare('SELECT distributor
FROM prodExtra
WHERE distributor LIKE ?
GROUP BY distributor
ORDER BY distributor');
$res = $dbc->execute($prep, array($args->search . '%'));
while ($row = $dbc->fetch_row($res)) {
if (!in_array($row['distributor'], $ret)) {
$ret[] = $row['distributor'];
}
}
}
return $ret;
case 'mfirstname':
case 'mlastname':
case 'maddress':
case 'mcity':
case 'memail':
return \COREPOS\Fannie\API\member\MemberREST::autoComplete($args->field, $args->search);
case 'sku':
$query = 'SELECT sku
FROM vendorItems
WHERE sku LIKE ? ';
$param = array($args->search . '%');
if (property_exists($args, 'vendor_id')) {
$query .= ' AND vendorID=? ';
$param[] = $args->vendor_id;
}
$query .= 'GROUP BY sku
ORDER BY sku';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $param);
while ($row = $dbc->fetch_row($res)) {
$ret[] = $row['sku'];
if (count($ret) > 50) {
break;
}
}
return $ret;
case 'unit':
$query = '
SELECT unitofmeasure
FROM products
WHERE unitofmeasure LIKE ?
GROUP BY unitofmeasure
ORDER BY unitofmeasure';
$param = array($args->search . '%');
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $param);
while ($row = $dbc->fetchRow($res)) {
$ret[] = $row['unitofmeasure'];
if (count($ret) > 50) {
break;
}
}
return $ret;
default:
return $ret;
}
}
public function fetch_report_data()
{
/* provide a department range and date range to
get history for all products in those departments
for that time period AND current price
provide just a upc to get history for that upc
*/
$dept1 = FormLib::get('dept1');
$dept2 = FormLib::get('dept2');
$upc = FormLib::get('upc');
if ($upc !== '') {
$upc = BarcodeLib::padUPC($upc);
}
$start_date = FormLib::get('date1', date('Y-m-d'));
$end_date = FormLib::get('date2', date('Y-m-d'));
$manu = FormLib::get('manufacturer');
$mtype = FormLib::get('mtype', 'upc');
$q = "";
$args = array();
$sql = $this->connection;
$sql->selectDB($this->config->get('OP_DB'));
$type = FormLib::get('type');
if ($type === '') {
// not set
$q = "\n SELECT h.upc,\n p.description,\n price,\n h.modified,\n p.normal_price \n FROM prodPriceHistory AS h \n " . DTrans::joinProducts('h') . "\n WHERE h.upc = ?\n ORDER BY h.upc,\n h.modified DESC";
$args = array($upc);
} else {
if ($type == 'upc') {
$q = "\n SELECT h.upc,\n p.description,\n price,\n h.modified,\n p.normal_price \n FROM prodPriceHistory AS h \n " . DTrans::joinProducts('h') . "\n WHERE h.upc = ?\n AND h.modified BETWEEN ? AND ?\n ORDER BY h.upc,\n h.modified DESC";
$args = array($upc, $start_date . ' 00:00:00', $end_date . ' 23:59:59');
} else {
if ($type == 'department') {
$q = "\n SELECT h.upc,\n p.description,\n price,\n h.modified,\n p.normal_price \n FROM prodPriceHistory AS h \n " . DTrans::joinProducts('h') . "\n WHERE department BETWEEN ? AND ?\n AND h.modified BETWEEN ? AND ?\n ORDER BY h.upc,\n h.modified DESC";
$args = array($dept1, $dept2, $start_date . ' 00:00:00', $end_date . ' 23:59:59');
$upc = '';
// if UPC and dept submitted, unset UPC
} else {
if ($mtype == 'upc') {
$q = "\n SELECT h.upc,\n p.description,\n price,\n h.modified,\n p.normal_price \n FROM prodPriceHistory AS h \n " . DTrans::joinProducts('h') . "\n WHERE h.upc LIKE ?\n AND h.modified BETWEEN ? AND ?\n ORDER BY h.upc,\n h.modified DESC";
$args = array('%' . $manu . '%', $start_date . ' 00:00:00', $end_date . ' 23:59:59');
} else {
$q = "\n SELECT h.upc,\n p.description,\n price,\n h.modified,\n p.normal_price \n FROM prodPriceHistory AS h \n " . DTrans::joinProducts('h') . "\n WHERE x.brand LIKE ?\n AND h.modified BETWEEN ? AND ?\n ORDER BY h.upc,\n h.modified DESC";
$args = array($manu, $start_date . ' 00:00:00', $end_date . ' 23:59:59');
}
$upc = '';
// if UPC and manu submitted, unset UPC
}
}
}
$def = $sql->tableDefinition('prodPriceHistory');
if (isset($def['storeID']) && $this->config->get('STORE_ID')) {
$q = str_replace('h.upc=p.upc', 'h.upc=p.upc AND h.storeID=p.store_id', $q);
}
$p = $sql->prepare_statement($q);
$r = $sql->exec_statement($p, $args);
if ($upc !== '') {
$this->report_headers[] = 'Current Price';
}
$data = array();
while ($row = $sql->fetch_array($r)) {
$record = array($row['upc'], $row['description'], sprintf('%.2f', $row['price']), $row['modified']);
if ($upc !== '') {
$record[] = $row['normal_price'];
}
$data[] = $record;
}
return $data;
}
public function get_id_view()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$prep = $dbc->prepare("\n SELECT m.sku,\n m.upc,\n v.description AS vendorDescript,\n p.description as storeDescript\n FROM VendorBreakdowns AS m\n " . DTrans::joinProducts('m') . "\n LEFT JOIN vendorItems AS v ON v.sku=m.sku AND v.vendorID=m.vendorID\n WHERE m.vendorID = ?\n ORDER BY m.upc\n ");
$ret = '';
$ret .= '<form action="' . $_SERVER['PHP_SELF'] . '" method="get">';
$ret .= '<div class="form-group form-inline">
<label>SKU</label>
<input type="text" class="form-control" name="sku" placeholder="Vendor SKU" />
<label>PLU/UPC</label>
<input type="text" class="form-control" name="plu" placeholder="Our PLU" />
<button type="submit" class="btn btn-default">Add Entry</button>
<input type="hidden" name="id" value="' . $this->id . '" />
<a href="?id=' . $this->id . '&break=1" class="btn btn-default">Run Breakdowns</a>
<a href="VendorIndexPage.php?vid=' . $this->id . '" class="btn btn-default">Home</a>
</div>
</form>';
$ret .= '<table class="table table-bordered">';
$ret .= '<thead><tr>
<th>Vendor SKU</th>
<th>Our PLU</th>
<th>Vendor Description</th>
<th>Our Description</th>
<th> </th>
</tr></thead><tbody>';
$res = $dbc->execute($prep, array($this->id));
while ($row = $dbc->fetchRow($res)) {
if (empty($row['vendorDescript'])) {
$row['vendorDescript'] = '<span class="alert-danger">Discontinued by vendor?</span>';
}
if (empty($row['storeDescript'])) {
$row['storeDescript'] = '<span class="alert-danger">Discontinued by us?</span>';
}
$ret .= sprintf('
<tr>
<td>%s</td>
<td><a href="../ItemEditorPage.php?searchupc=%s">%s</a></td>
<td>%s</td>
<td>%s</td>
<td>
<a href="?_method=delete&id=%d&sku=%s&plu=%s"
onclick="return confirm(\'Delete entry for PLU #%s?\');">%s</a>
</td>
</tr>', $row['sku'], $row['upc'], $row['upc'], $row['vendorDescript'], $row['storeDescript'], $this->id, $row['sku'], $row['upc'], $row['upc'], FannieUI::deleteIcon());
}
$ret .= '</tbody></table>';
$this->addScript('../../src/javascript/tablesorter/jquery.tablesorter.js');
$this->addOnloadCommand("\$('.table').tablesorter([[1,0]]);\n");
return $ret;
}
function SaveFormData($upc)
{
$upc = BarcodeLib::padUPC($upc);
$brand = FormLib::get('lf_brand');
$desc = FormLib::get('lf_desc');
$origin = FormLib::get('origin', 0);
$floorID = FormLib::get('floor-id', 0);
$text = FormLib::get('lf_text');
$text = str_replace("\r", '', $text);
$text = str_replace("\n", '<br />', $text);
// strip non-ASCII (word copy/paste artifacts)
$text = preg_replace("/[^-]/", "", $text);
$signs = FormLib::get('sign-count', 1);
if ($signs < 1) {
$signs = 1;
}
$dbc = $this->db();
$loc = new ProdPhysicalLocationModel($dbc);
$loc->upc($upc);
$loc->floorSectionID($floorID);
$loc->save();
$model = new ProductUserModel($dbc);
$model->upc($upc);
$model->brand($brand);
$model->description($desc);
$model->long_text($text);
$model->signCount($signs);
$multiOrigin = FormLib::get('otherOrigin', array());
$originMap = array();
if ($origin != 0) {
$originMap[] = $origin;
}
foreach ($multiOrigin as $originID) {
if ($originID != 0) {
$originMap[] = $originID;
}
}
$mapP = $dbc->prepare('DELETE FROM ProductOriginsMap WHERE upc=?');
$addP = $dbc->prepare('INSERT INTO ProductOriginsMap
(originID, upc, active)
VALUES (?, ?, 1)');
$lcP = $dbc->prepare('SELECT u.upc
FROM upcLike AS u
' . DTrans::joinProducts('u', 'p', 'INNER') . '
WHERE u.likeCode IN (
SELECT l.likeCode
FROM upcLike AS l
WHERE l.upc = ?
)');
$lcR = $dbc->execute($lcP, array($upc));
$items = array($upc);
while ($w = $dbc->fetch_row($lcR)) {
if ($w['upc'] == $upc) {
continue;
}
$items[] = $w['upc'];
}
$prod = new ProductsModel($dbc);
$stores = new StoresModel($dbc);
foreach ($items as $item) {
$prod->upc($item);
$prod->store_id(1);
$prod->current_origin_id($origin);
$prod->save();
$dbc->execute($mapP, array($item));
foreach ($originMap as $originID) {
$dbc->execute($addP, array($originID, $item));
}
}
return $model->save();
}
