function preprocess()
{
global $FANNIE_OP_DB;
// custom: can delete items from report results
if (isset($_REQUEST['deleteItem'])) {
$upc = FormLib::get_form_value('deleteItem', '');
if (is_numeric($upc)) {
$upc = BarcodeLib::padUPC($upc);
}
$dbc = FannieDB::get($FANNIE_OP_DB);
$model = new ProductsModel($dbc);
$model->upc($upc);
$model->store_id(1);
$model->delete();
echo 'Deleted';
return false;
} elseif (FormLib::get('deactivate') !== '') {
$upc = BarcodeLib::padUPC(FormLib::get('deactivate'));
$dbc = FannieDB::get($FANNIE_OP_DB);
$model = new ProductsModel($dbc);
$model->upc($upc);
$model->store_id(1);
$model->inUse(0);
$model->save();
echo 'Deactivated';
}
$ret = parent::preprocess();
// custom: needs extra JS for delete option
if ($this->content_function == 'report_content' && $this->report_format == 'html') {
$this->add_script("../../src/javascript/jquery.js");
$this->add_script('delete.js');
}
return $ret;
}
function get_id_view()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$upc = BarcodeLib::padUPC($this->id);
$itemP = $dbc->prepare_statement('SELECT p.description,p.special_price,
CASE WHEN u.likeCode IS NULL THEN -1 ELSE u.likeCode END as lc
FROM products AS p LEFT JOIN upcLike AS u
ON p.upc=u.upc WHERE p.upc=?');
$itemR = $dbc->exec_statement($itemP, array($upc));
if ($dbc->num_rows($itemR) == 0) {
return '<div class="alert alert-danger">Item not found</div>';
}
$itemW = $dbc->fetch_row($itemR);
$ret = '<form method="post" action="EndItemSale.php">
<input type="hidden" name="id" value="' . $upc . '" />';
$ret .= sprintf('<p>%s is currently on sale for $%.2f', $itemW['description'], $itemW['special_price']);
$batchP = $dbc->prepare_statement("SELECT b.batchName, b.batchID, l.upc FROM batches AS b \n LEFT JOIN batchList as l\n on b.batchID=l.batchID WHERE '" . date('Y-m-d') . "' BETWEEN b.startDate\n AND b.endDate AND (l.upc=? OR l.upc=?)");
$batchR = $dbc->exec_statement($batchP, array($upc, 'LC' . $itemW['lc']));
if ($dbc->num_rows($batchR) == 0) {
$ret .= '<div class="alert alert-warning">The item does not appear to be in an active batch</div>';
} else {
$batchW = $dbc->fetch_row($batchR);
$ret .= '<br />The item will be removed from the batch <strong>' . $batchW['batchName'] . '</strong>';
$ret .= sprintf('<input type="hidden" name="batchID" value="%d" />
<input type="hidden" name="batchUPC" value="%s" />', $batchW['batchID'], $batchW['upc']);
}
$ret .= '<br /><button type="submit" class="btn btn-default" id="button">Take item off sale</button>';
$ret .= '</p>';
return $ret;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$upc = $this->form->upc;
$upc = BarcodeLib::padUPC($upc);
$query = 'SELECT i.sku, i.quantity, i.unitCost, i.caseSize,
i.quantity * i.unitCost * i.caseSize AS ttl,
o.vendorInvoiceID, v.vendorName, o.placedDate
FROM PurchaseOrderItems AS i
LEFT JOIN PurchaseOrder AS o ON i.orderID=o.orderID
LEFT JOIN vendors AS v ON o.vendorID=v.vendorID
WHERE i.internalUPC = ?
AND o.placedDate >= ?
ORDER BY o.placedDate';
$prep = $dbc->prepare($query);
$args = array($upc);
if (FormLib::get('all')) {
$args[] = '1900-01-01 00:00:00';
} else {
$args[] = date('Y-m-d', strtotime('92 days ago'));
}
$result = $dbc->execute($prep, $args);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array($row['placedDate'], $row['vendorName'], $row['vendorInvoiceID'], $row['sku'], $row['quantity'], $row['caseSize'], $row['unitCost'], $row['ttl']);
$data[] = $record;
}
return $data;
}
public function get_id_view()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$model = new ProductsModel($dbc);
$model->upc(BarcodeLib::padUPC($this->id));
if (!$model->load()) {
return '<div class="alert alert-danger">Item ' . $this->id . ' does not exist</dv>';
}
$ret = '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">
<input type="hidden" name="id" value="' . $model->upc() . '" />
<p>
Create a copy of ' . $model->upc() . ' (' . $model->description() . ')
</p>
<div class="form-group">
<label>New Item UPC</label>
<input type="text" name="new-upc" class="form-control" id="new-upc" required />
</div>
<p>
<button type="submit" class="btn btn-default">Clone Item</button>
</p>
</form>';
$this->addOnloadCommand("enableLinea('#new-upc');\n");
$this->addOnloadCommand("\$('#new-upc').focus();\n");
return $ret;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$upc = $this->form->upc;
$upc = BarcodeLib::padUPC($upc);
$query = '
SELECT b.batchName,
b.startDate,
b.endDate,
t.typeDesc,
l.salePrice,
b.batchID
FROM batchList AS l
INNER JOIN batches AS b ON b.batchID=l.batchID
LEFT JOIN batchType AS t ON b.batchType=t.batchTypeID
WHERE b.discountType <> 0
AND l.upc=?
ORDER BY b.startDate
';
$prep = $dbc->prepare($query);
$args = array($upc);
$result = $dbc->execute($prep, $args);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array($row['startDate'], $row['endDate'], sprintf('<a href="%s/batches/newbatch/EditBatchPage.php?id=%d" target="_batch%d">%s</a>', $this->config->get('URL'), $row['batchID'], $row['batchID'], $row['batchName']), $row['typeDesc'], $row['salePrice']);
$data[] = $record;
}
return $data;
}
public function get_id_first_last_handler()
{
if (empty($this->id) && empty($this->last) && empty($this->first)) {
return true;
// invalid search
}
if (!empty($this->id)) {
$account = \COREPOS\Fannie\API\member\MemberREST::get($this->id);
if ($account != false) {
header('Location: PIMemberPage.php?id=' . $this->id);
return false;
}
$json = array('idCardUPC' => BarcodeLib::padUPC($this->id));
$accounts = \COREPOS\Fannie\API\member\MemberREST::search($json, 0, true);
foreach ($accounts as $a) {
header('Location: PIMemberPage.php?id=' . $a['cardNo']);
return false;
}
} else {
$json = array('customers' => array(array('firstName' => $this->first, 'lastName' => $this->last)));
$accounts = \COREPOS\Fannie\API\member\MemberREST::search($json, 250, true);
if (count($accounts) == 1) {
header('Location: PIMemberPage.php?id=' . $accounts[0]['cardNo']);
return false;
} else {
$this->results = $accounts;
}
}
return true;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$upc = BarcodeLib::padUPC(FormLib::get('upc'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$q = $dbc->prepare("\n SELECT d.card_no,\n sum(quantity) as qty,\n sum(total) as amt\n FROM {$dlog} AS d \n WHERE d.upc=? AND \n tdate BETWEEN ? AND ?\n GROUP BY d.card_no\n ORDER BY d.card_no");
$r = $dbc->exec_statement($q, array($upc, $date1 . ' 00:00:00', $date2 . ' 23:59:59'));
$data = array();
while ($w = $dbc->fetch_row($r)) {
$account = \COREPOS\Fannie\API\member\MemberREST::get($w['card_no']);
if ($account == false) {
continue;
}
$customer = array();
foreach ($account['customers'] as $c) {
if ($c['accountHolder']) {
$customer = $c;
break;
}
}
$record = array($w['card_no'], $customer['lastName'] . ', ' . $customer['firstName'], $account['addressFirstLine'] . ' ' . $account['addressSecondLine'], $account['city'], $account['state'], $account['zip'], $customer['phone'], $customer['altPhone'], $customer['email'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['amt']));
$data[] = $record;
}
return $data;
}
function process_file($linedata)
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$upc_index = $this->get_column_index('upc');
$price_index = $this->get_column_index('price');
$cool_index = $this->get_column_index('cool');
$itemP = $dbc->prepare('
SELECT itemdesc,
description,
weight
FROM scaleItems AS s
LEFT JOIN products AS p ON s.plu=p.upc
WHERE plu=?');
$saveP = $dbc->prepare('
UPDATE scaleItems
SET price=?,
itemdesc=?,
modified=' . $dbc->now() . '
WHERE plu=?');
$product = new ProductsModel($dbc);
$prodPricing = FormLib::get('prodPricing') === '' ? false : true;
$scale_items = array();
foreach ($linedata as $line) {
$upc = trim($line[$upc_index]);
$upc = BarcodeLib::padUPC($upc);
$price = str_replace('$', '', $line[$price_index]);
$price = trim($price);
$cool = $line[$cool_index];
if (!is_numeric($upc) || !is_numeric($price)) {
continue;
}
$item = $dbc->getRow($itemP, array($upc));
if ($item === false) {
continue;
}
$itemdesc = !empty($item['itemdesc']) ? $item['itemdesc'] : $item['description'];
if (strstr($itemdesc, "\n")) {
list($line1, $line2) = explode("\n", $itemdesc);
$itemdesc = $line1 . "\n" . $cool;
} else {
$itemdesc .= "\n" . $cool;
}
$dbc->execute($itemP, array($price, $itemdesc, $upc));
if ($prodPricing) {
$product->upc($upc);
foreach ($product->find() as $obj) {
$obj->normal_price($price);
$obj->save();
}
}
$scale_info = array('RecordType' => 'ChangeOneItem', 'PLU' => substr($upc, 3, 4), 'Description' => $itemdesc, 'Price' => $price, 'Type' => $item['weight'] == 0 ? 'Random Weight' : 'Fixed Weight', 'ReportingClass' => 1);
$scale_items[] = $scale_info;
}
$scales = $this->getScales(FormLib::get('scales'));
HobartDgwLib::writeItemsToScales($scale_items, $scales);
EpScaleLib::writeItemsToScales($scale_items, $scales);
return true;
}
function get_search_handler()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$ret = array();
// search by vendor SKU
$skuQ = 'SELECT brand, description, size, units, cost, sku,
i.vendorID, vendorName
FROM vendorItems AS i LEFT JOIN vendors AS v ON
i.vendorID=v.vendorID WHERE sku LIKE ?';
$skuP = $dbc->prepare_statement($skuQ);
$skuR = $dbc->exec_statement($skuP, array('%' . $this->search . '%'));
while ($w = $dbc->fetch_row($skuR)) {
$result = array('sku' => $w['sku'], 'title' => '[' . $w['vendorName'] . '] ' . $w['brand'] . ' - ' . $w['description'], 'unitSize' => $w['size'], 'caseSize' => $w['units'], 'unitCost' => sprintf('%.2f', $w['cost']), 'caseCost' => sprintf('%.2f', $w['cost'] * $w['units']), 'vendorID' => $w['vendorID']);
$ret[] = $result;
}
if (count($ret) > 0) {
echo json_encode($ret);
return False;
}
// search by UPC
$upcQ = 'SELECT brand, description, size, units, cost, sku,
i.vendorID, vendorName
FROM vendorItems AS i LEFT JOIN vendors AS v ON
i.vendorID = v.vendorID WHERE upc=?';
$upcP = $dbc->prepare_statement($upcQ);
$upcR = $dbc->exec_statement($upcP, array(BarcodeLib::padUPC($this->search)));
while ($w = $dbc->fetch_row($upcR)) {
$result = array('sku' => $w['sku'], 'title' => '[' . $w['vendorName'] . '] ' . $w['brand'] . ' - ' . $w['description'], 'unitSize' => $w['size'], 'caseSize' => $w['units'], 'unitCost' => sprintf('%.2f', $w['cost']), 'caseCost' => sprintf('%.2f', $w['cost'] * $w['units']), 'vendorID' => $w['vendorID']);
$ret[] = $result;
}
if (count($ret) > 0) {
echo json_encode($ret);
return False;
}
// search by internalSKU / order code
$iskuQ = 'SELECT brand, description, size, units, cost, sku,
v.vendorID, vendorName
FROM internalSKUs as i
INNER JOIN vendorItems as v
ON i.vendor_sku = v.sku AND i.vendorID=v.vendorID
LEFT JOIN vendors AS n ON v.vendorID=n.vendorID
WHERE our_sku = ? ';
$iskuP = $dbc->prepare_statement($iskuQ);
$iskuR = $dbc->exec_statement($iskuP, array($this->search));
while ($w = $dbc->fetch_row($iskuR)) {
$result = array('sku' => $w['sku'], 'title' => '[' . $w['vendorName'] . '] ' . $w['brand'] . ' - ' . $w['description'], 'unitSize' => $w['size'], 'caseSize' => $w['units'], 'unitCost' => sprintf('%.2f', $w['cost']), 'caseCost' => sprintf('%.2f', $w['cost'] * $w['units']), 'vendorID' => $w['vendorID']);
$ret[] = $result;
}
if (count($ret) > 0) {
echo json_encode($ret);
return False;
}
echo '[]';
return False;
}
public function preprocess()
{
$dbc = $this->connection;
$settings = $this->config->get('PLUGIN_SETTINGS');
if (FormLib::get('action') === 'save') {
$upc = FormLib::get('upc');
$qty = FormLib::get('qty');
$dbc->selectDB($settings['ShelfAuditDB']);
$model = new SaListModel($dbc);
$model->upc(BarcodeLib::padUPC($upc));
$model->clear(0);
$entries = $model->find('date', true);
if (count($entries) > 0) {
$entries[0]->tdate(date('Y-m-d H:i:s'));
$entries[0]->quantity($qty);
$entries[0]->save();
} else {
$model->tdate(date('Y-m-d H:i:s'));
$model->quantity($qty);
$model->save();
}
echo $qty;
echo 'quantity updated';
return false;
}
if (FormLib::get('clear') === '1') {
$table = $settings['ShelfAuditDB'] . $dbc->sep() . 'SaList';
$res = $dbc->query('
UPDATE ' . $table . '
SET clear=1
');
return true;
}
$upc = FormLib::get_form_value('upc_in', '');
if ($upc !== '') {
$upc = BarcodeLib::padUPC($upc);
$this->current_item_data['upc'] = $upc;
$prep = $dbc->prepare('
SELECT p.description,
p.brand,
p.size,
COALESCE(s.quantity, 0) AS qty
FROM products AS p
LEFT JOIN ' . $settings['ShelfAuditDB'] . $dbc->sep() . 'SaList AS s ON p.upc=s.upc AND s.clear=0
WHERE p.upc=?
');
$row = $dbc->getRow($prep, array($upc));
if ($row) {
$this->current_item_data['desc'] = $row['brand'] . ' ' . $row['description'] . ' ' . $row['size'];
$this->current_item_data['qty'] = $row['qty'];
}
}
return true;
}
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 saveItem($store, $sec, $subsec, $sh_set, $shelf, $loc, $upc)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$upc = BarcodeLib::padUPC($upc);
$q = sprintf("DELETE FROM prodPhysicalLocation WHERE\n 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);
$q = $dbc->prepare_statement("INSERT INTO prodPhysicalLocation (upc,\n store_id,section,subsection,shelf_set,shelf,\n location) VALUES (?,?,?,?,?,?,?)");
$args = array($upc, $store, $sec, $subsec, $sh_set, $shelf, $loc);
$r = $dbc->exec_statement($q, $args);
}
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);
}
function SaveFormData($upc)
{
$upc = BarcodeLib::padUPC($upc);
$ret = '';
if (FormLib::get_form_value('newshelftag', '') != '') {
$ret .= "<script type=\"text/javascript\">";
$ret .= "testwindow= window.open (\"addShelfTag.php?upc={$upc}\", \"New Shelftag\",\"location=0,status=1,scrollbars=1,width=300,height=220\");";
$ret .= "testwindow.moveTo(50,50);";
$ret .= "</script>";
}
echo $ret;
// output javascript to result page
return True;
}
public function report_description_content()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$prod = new ProductsModel($dbc);
$prod->upc(BarcodeLib::padUPC(FormLib::get('upc')));
$prod->load();
$ret = array('Recent Sales For ' . $prod->upc() . ' ' . $prod->description() . '<br />');
if ($this->report_format == 'html') {
$ret[] = sprintf('<a href="../ItemLastQuarter/ItemLastQuarterReport.php?upc=%s">Weekly Sales Details</a> | ', $prod->upc());
$ret[] = sprintf('<a href="../ItemOrderHistory/ItemOrderHistoryReport.php?upc=%s">Recent Order History</a>', $prod->upc());
}
return $ret;
}
public function testItemFlags()
{
$config = FannieConfig::factory();
$connection = FannieDB::get($config->OP_DB);
/**
Setup preconditions for the test
*/
$upc = BarcodeLib::padUPC('16');
$product = new ProductsModel($connection);
$product->upc($upc);
$product->store_id(0);
$product->load();
if ($product->numflag() != 0) {
$product->numflag(0);
}
$product->save();
$module = new ItemFlagsModule();
$module->setConnection($connection);
$module->setConfig($config);
$form = new \COREPOS\common\mvc\ValueContainer();
$module->setForm($form);
$saved = $module->saveFormData($upc);
$this->assertEquals(true, $saved, 'Handled empty input');
$product->reset();
$product->upc($upc);
$product->load();
$this->assertEquals(0, $product->numflag(), 'Wrong numflag value ' . $product->numflag());
/**
Simulate real form input
*/
$form = new \COREPOS\common\mvc\ValueContainer();
$form->flags = array(1, 3);
// 0b101 == 5
$module->setForm($form);
$saved = $module->saveFormData($upc);
$this->assertEquals(true, $saved, 'Saving item flags failed');
$product->reset();
$product->upc($upc);
$product->load();
$this->assertEquals(5, $product->numflag(), 'Wrong numflag value ' . $product->numflag());
/* put record back to normal */
$product->numflag(0);
$product->save();
$form = new \COREPOS\common\mvc\ValueContainer();
$form->flags = 'not_an_array';
$module->setForm($form);
$saved = $module->saveFormData($upc);
$this->assertEquals(false, $saved, 'Accepted invalid input');
}
public function get_id_sku_plu_handler()
{
if (empty($this->sku) || empty($this->plu)) {
return true;
}
$dbc = FannieDB::get($this->config->get('OP_DB'));
$this->plu = BarcodeLib::padUPC($this->plu);
$skuP = $dbc->prepare('
SELECT upc
FROM vendorSKUtoPLU
WHERE vendorID=?
AND sku=?');
$skuR = $dbc->execute($skuP, array($this->id, $this->sku));
if ($skuR && $dbc->numRows($skuR) > 0) {
$upP = $dbc->prepare('
UPDATE vendorSKUtoPLU
SET upc=?
WHERE vendorID=?
AND sku=?');
$upR = $dbc->execute($upP, array($this->plu, $this->id, $this->sku));
$this->addOnloadCommand("showBootstrapAlert('#alert-area', 'success', 'Updated entry for SKU #{$this->sku}')\n");
return true;
}
$pluP = $dbc->prepare('
SELECT sku
FROM vendorSKUtoPLU
WHERE vendorID=?
AND upc=?');
$pluR = $dbc->execute($pluP, array($this->id, $this->plu));
if ($pluR && $dbc->numRows($pluR) > 0) {
$upP = $dbc->prepare('
UPDATE vendorSKUtoPLU
SET sku=?
WHERE vendorID=?
AND upc=?');
$upR = $dbc->execute($upP, array($this->sku, $this->id, $this->plu));
$this->addOnloadCommand("showBootstrapAlert('#alert-area', 'success', 'Updated entry for PLU #{$this->plu}')\n");
return true;
}
$insP = $dbc->prepare('
INSERT INTO vendorSKUtoPLU
(vendorID, sku, upc)
VALUES
(?, ?, ?)');
$insR = $dbc->execute($insP, array($this->id, $this->sku, $this->plu));
$this->addOnloadCommand("showBootstrapAlert('#alert-area', 'success', 'Added new entry for PLU #{$this->plu}')\n");
return true;
}
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()
{
global $FANNIE_OP_DB, $FANNIE_ARCHIVE_DB;
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$upc = $this->form->upc;
$upc = BarcodeLib::padUPC($upc);
$query = "SELECT \n l.quantity, l.total,\n l.percentageStoreSales, l.percentageSuperDeptSales,\n l.percentageDeptSales, l.weekLastQuarterID as wID,\n w.weekStart, w.weekEnd\n FROM products AS p\n LEFT JOIN " . $FANNIE_ARCHIVE_DB . $dbc->sep() . "productWeeklyLastQuarter AS l\n ON p.upc=l.upc\n LEFT JOIN " . $FANNIE_ARCHIVE_DB . $dbc->sep() . "weeksLastQuarter AS w\n ON l.weekLastQuarterID=w.weekLastQuarterID \n WHERE p.upc = ?\n ORDER BY l.weekLastQuarterID";
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, array($upc));
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array('Week ' . date('Y-m-d', strtotime($row['weekStart'])) . ' to ' . date('Y-m-d', strtotime($row['weekEnd'])), sprintf('%.2f', $row['quantity']), sprintf('%.2f', $row['total']), sprintf('%.4f%%', $row['percentageStoreSales'] * 100), sprintf('%.4f%%', $row['percentageSuperDeptSales'] * 100), sprintf('%.4f%%', $row['percentageDeptSales'] * 100));
$data[] = $record;
}
return $data;
}
public function showEditForm($upc, $display_mode = 1, $expand_mode = 1)
{
$FANNIE_LANES = FannieConfig::config('LANES');
$upc = BarcodeLib::padUPC($upc);
$queryItem = "SELECT * FROM products WHERE upc = ?";
$ret = '<div id="AllLanesFieldset" class="panel panel-default">';
$ret .= "<div class=\"panel-heading\"><a href=\"\" onclick=\"\$('#AllLanesFieldsetContent').toggle();return false;\">\n Lane Status\n </a></div>";
$css = $expand_mode == 1 ? '' : ' collapse';
$ret .= '<div id="AllLanesFieldsetContent" class="panel-body' . $css . '">';
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$f = $FANNIE_LANES[$i];
$sql = new SQLManager($f['host'], $f['type'], $f['op'], $f['user'], $f['pw']);
if (!is_object($sql) || $sql->connections[$f['op']] === False) {
$ret .= "<li class=\"alert-danger\">Can't connect to lane " . ($i + 1) . "</li>";
continue;
}
$prep = $sql->prepare_statement($queryItem);
$resultItem = $sql->exec_statement($prep, array($upc));
$num = $sql->num_rows($resultItem);
if ($num == 0) {
$ret .= "<li class=\"alert-danger\">Item <strong>{$upc}</strong> not found on Lane " . ($i + 1) . "</li>";
} else {
if ($num > 1) {
$ret .= "<li class=\"alert-danger\">Item <strong>{$upc}</strong> found multiple times on Lane " . ($i + 1);
$ret .= '<ul>';
while ($rowItem = $sql->fetch_array($resultItem)) {
$ret .= "<li>{$rowItem['upc']} {$rowItem['description']}</li>";
}
$ret .= '</ul></li>';
} else {
$rowItem = $sql->fetch_array($resultItem);
$ret .= "<li>Item <span style=\"color:red;\">{$upc}</span> on Lane " . ($i + 1) . "<ul>";
$ret .= "<li>Price: {$rowItem['normal_price']}</li>";
if ($rowItem['discounttype'] != 0) {
$ret .= "<li class=\"alert-success\">ON SALE: {$rowItem['special_price']}</li>";
}
$ret .= "</ul></li>";
}
}
}
$ret .= '</ul>';
$ret .= '</div>';
$ret .= '</div>';
return $ret;
}
public function process_file($linedata)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$dbc = $this->connection;
$settings = $this->config->get('PLUGIN_SETTINGS');
$dbc->selectDB($settings['WicDB']);
$upc_index = $this->get_column_index('upc');
$desc_index = $this->get_column_index('desc');
$size_index = $this->get_column_index('size');
$brand_index = $this->get_column_index('brand');
if ($desc_index === false && $brand_index === false) {
$this->error_details = 'Neither brand nor description provided; nothing to import!';
return false;
}
$ret = true;
$checks = FormLib::get_form_value('checks') == 'yes' ? true : false;
$insP = $dbc->prepare('
INSERT INTO WicItems
(upc, brand, description, size)
VALUES
(?, ?, ?, ?)');
$dbc->query('TRUNCATE TABLE WicItems');
foreach ($linedata as $line) {
$upc = $line[$upc_index];
// upc cleanup
$upc = str_replace(" ", "", $upc);
$upc = str_replace("-", "", $upc);
if (!is_numeric($upc)) {
continue;
}
// skip header(s) or blank rows
$this->stats['total']++;
// MN published spreadhsheet is impossibly dumb
if (strlen($upc) == 12) {
$upc = substr($upc, 0, strlen($upc) - 1);
}
$upc = BarcodeLib::padUPC($upc);
$brand = $brand_index !== false && isset($line[$brand_index]) ? $line[$brand_index] : '';
$desc = $desc_index !== false && isset($line[$desc_index]) ? $line[$desc_index] : '';
$size = $size_index !== false && isset($line[$size_index]) ? $line[$size_index] : '';
$dbc->execute($insP, array($upc, $brand, $desc, $size));
}
return $ret;
}
public function get_id_handler()
{
$upc = BarcodeLib::padUPC($this->id);
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$product = new ProductsModel($dbc);
$product->upc($upc);
$matches = $product->find('store_id');
if (count($matches) > 0) {
$product = $matches[0];
$stores = new StoresModel($dbc);
foreach ($stores->find('storeID') as $store) {
$product->store_id($store->storeID());
$product->save();
}
}
return '../ItemEditorPage.php?searchupc=' . $upc;
}
public function showEditForm($upc, $display_mode = 1, $expand_mode = 1)
{
$upc = BarcodeLib::padUPC($upc);
$ret = '';
$ret = '<div id="ItemFlagsFieldset" class="panel panel-default">';
$ret .= "<div class=\"panel-heading\">\n <a href=\"\" onclick=\"\$('#ItemFlagsContents').toggle();return false;\">\n Flags\n </a></div>";
$css = $expand_mode == 1 ? '' : ' collapse';
$ret .= '<div id="ItemFlagsContents" class="panel-body' . $css . '">';
// class="col-lg-1" works pretty well with META_WIDTH_HALF
$ret .= '<div id="ItemFlagsTable" class="col-sm-5">';
$dbc = $this->db();
$q = "\n SELECT f.description,\n f.bit_number,\n (1<<(f.bit_number-1)) & p.numflag AS flagIsSet\n FROM products AS p, \n prodFlags AS f\n WHERE p.upc=?\n AND f.active=1";
$p = $dbc->prepare_statement($q);
$r = $dbc->exec_statement($p, array($upc));
if ($dbc->num_rows($r) == 0) {
// item does not exist
$p = $dbc->prepare_statement('
SELECT f.description,
f.bit_number,
0 AS flagIsSet
FROM prodFlags AS f
WHERE f.active=1');
$r = $dbc->exec_statement($p);
}
$tableStyle = " style='border-spacing:5px; border-collapse: separate;'";
$ret .= "<table{$tableStyle}>";
$i = 0;
while ($w = $dbc->fetch_row($r)) {
if ($i == 0) {
$ret .= '<tr>';
}
if ($i != 0 && $i % 2 == 0) {
$ret .= '</tr><tr>';
}
$ret .= sprintf('<td><input type="checkbox" id="item-flag-%d" name="flags[]" value="%d" %s /></td>
<td><label for="item-flag-%d">%s</label></td>', $i, $w['bit_number'], $w['flagIsSet'] == 0 ? '' : 'checked', $i, $w['description']);
$i++;
}
$ret .= '</tr></table>';
$ret .= '</div>' . '<!-- /#ItemFlagsTable -->';
$ret .= '</div>' . '<!-- /#ItemFlagsContents -->';
$ret .= '</div>' . '<!-- /#ItemFlagsFieldset -->';
return $ret;
}
public function get_id_handler()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$upc = BarcodeLib::padUPC($this->id);
$product = new ProductsModel($dbc);
$product->upc($upc);
if (!$product->load()) {
$this->add_onload_command("showBootstrapAlert('#alert-area', 'danger', 'Item not found');\n");
$this->__route_stem = 'get';
} else {
$this->description = $product->description();
$this->cost = $product->cost();
$this->price = $product->normal_price();
$this->department = $product->department();
$this->upc = $upc;
}
return true;
}
public function get_id_view()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$start = FormLib::get('date1');
$end = FormLib::get('date2');
$query = 'SELECT o.placedDate, o.orderID, o.vendorInvoiceID,
v.vendorName, i.sku, i.internalUPC, i.description,
i.brand, i.quantity
FROM PurchaseOrderItems AS i
LEFT JOIN PurchaseOrder AS o ON i.orderID=o.orderID
LEFT JOIN vendors AS v ON o.vendorID=v.vendorID
WHERE (i.internalUPC=? OR i.sku LIKE ?) ';
if ($start !== '' && $end !== '') {
$query .= ' AND o.placedDate BETWEEN ? AND ? ';
}
$query .= 'ORDER BY o.placedDate DESC';
$args = array(BarcodeLib::padUPC($this->id), '%' . $this->id);
if ($start !== '' && $end !== '') {
$args[] = $start . ' 00:00:00';
$args[] = $end . ' 23:59:59';
}
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $args);
$ret = '<table class="table">';
$ret .= '<tr><th>Date</th><th>Invoice</th><th>Vendor</th>
<th>UPC</th><th>SKU</th><th>Brand</th><th>Desc</th>
<th>Qty</th></tr>';
while ($row = $dbc->fetch_row($res)) {
$ret .= sprintf('<tr>
<td><a href="ViewPurchaseOrders.php?id=%d">%s</a></td>
<td><a href="ViewPurchaseOrders.php?id=%d">%s</a></td>
<td>%s</td>
<td><a href="../item/ItemEditorPage.php?searchupc=%s">%s</a></td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%d</td>
</tr>', $row['orderID'], date('Y-m-d', strtotime($row['placedDate'])), $row['orderID'], $row['vendorInvoiceID'], $row['vendorName'], $row['internalUPC'], $row['internalUPC'], $row['sku'], $row['brand'], $row['description'], $row['quantity']);
}
$ret .= '</table>';
return $ret;
}
public function get_upc_date1_date2_handler()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$dlog = DTransactionsModel::selectDlog($this->date1, $this->date2);
$dtrans = DTransactionsModel::selectDtrans($this->date1, $this->date2);
$badP = $dbc->prepare("\n SELECT YEAR(datetime) AS year,\n MONTH(datetime) AS month,\n DAY(datetime) AS day,\n emp_no,\n register_no,\n trans_no\n FROM " . $dtrans . " AS d\n WHERE trans_type = 'L'\n AND upc = ?\n AND datetime BETWEEN ? AND ?\n AND description='BADSCAN'\n AND emp_no <> 9999\n AND register_no <> 99");
$args = array(BarcodeLib::padUPC($this->upc), date('Y-m-d 00:00:00', strtotime($this->date1)), date('Y-m-d 23:59:59', strtotime($this->date2)));
$openP = $dbc->prepare("\n SELECT upc,\n description,\n department,\n dept_name\n FROM " . $dlog . " AS d\n LEFT JOIN departments AS t ON d.department=t.dept_no\n WHERE tdate BETWEEN ? AND ?\n AND emp_no=?\n AND register_no=?\n AND trans_no=?\n AND trans_type = 'D'\n GROUP BY d.upc,\n d.description,\n d.department,\n t.dept_name");
$badR = $dbc->execute($badP, $args);
$this->receipts = array();
while ($badW = $dbc->fetch_row($badR)) {
$openArgs = array(date('Y-m-d 00:00:00', mktime(0, 0, 0, $badW['month'], $badW['day'], $badW['year'])), date('Y-m-d 23:59:59', mktime(0, 0, 0, $badW['month'], $badW['day'], $badW['year'])), $badW['emp_no'], $badW['register_no'], $badW['trans_no']);
$openR = $dbc->execute($openP, $openArgs);
$receipt = array('date' => date('Y-m-d', mktime(0, 0, 0, $badW['month'], $badW['day'], $badW['year'])), 'trans' => $badW['emp_no'] . '-' . $badW['register_no'] . '-' . $badW['trans_no'], 'rings' => array());
while ($openW = $dbc->fetch_row($openR)) {
$receipt['rings'][] = $openW;
}
$this->receipts[] = $receipt;
}
return true;
}
public function get_id_view()
{
$upc = BarcodeLib::padUPC($this->id);
$dbc = $this->connection;
$prep = $dbc->prepare('SELECT * FROM products WHERE upc=?');
$res = $dbc->execute($prep, array($upc));
if (!$res || $dbc->numRows($res) == 0) {
return '<div class="alert alert-danger">Item not found</div>' . $this->get_view();
}
$row = $dbc->fetchRow($res);
return '<div class="alert alert-warning">Delete this item?</div>
<p>
<a href="ItemEditorPage.php?searchupc=' . $upc . '">' . $upc . '</a>
- ' . $row['description'] . ' $' . sprintf('%.2f', $row['normal_price']) . '
</p>
<p>
<a href="?confirm=1&id=' . $upc . '" class="btn btn-default">Yes, delete this item</a>
<a href="DeleteItemPage.php" class="btn btn-default">No, keep this item</a>
</p>';
}
public function testBarcodeLib()
{
$pad = BarcodeLib::padUPC('1');
$this->assertEquals('0000000000001', $pad, 'BarcodeLib::padUPC failed');
$checks = array('12345678901' => '2', '123456789012' => '8', '1234567890123' => '1');
foreach ($checks as $barcode => $check_digit) {
$calc = BarcodeLib::getCheckDigit($barcode);
$this->assertEquals($check_digit, $calc, 'Failed check digit calculation for ' . $barcode);
$with_check = $barcode . $check_digit;
$without_check = $barcode . ($check_digit + 1) % 10;
$this->assertEquals(true, BarcodeLib::verifyCheckdigit($with_check));
$this->assertEquals(false, BarcodeLib::verifyCheckdigit($without_check));
}
$upc_a = BarcodeLib::UPCACheckDigit('12345678901');
$this->assertEquals('123456789012', $upc_a, 'Failed UPC A check digit calculation');
$ean_13 = BarcodeLib::EAN13CheckDigit('123456789012');
$this->assertEquals('1234567890128', $ean_13, 'Failed EAN 13 check digit calculation');
$norm = BarcodeLib::normalize13('12345678901');
$this->assertEquals('0123456789012', $norm, 'Failed normalizing UPC-A to 13 digits');
$norm = BarcodeLib::normalize13('123456789012');
$this->assertEquals('1234567890128', $norm, 'Failed normalizing EAN-13 to 13 digits');
}
public function SaveFormData($upc)
{
$upc = BarcodeLib::padUPC($upc);
$dbc = $this->db();
$model = new ProductsModel($dbc);
$model->upc($upc);
$model->store_id(1);
$method = FormLib::get_form_value('vp_method', 0);
$qty = FormLib::get_form_value('vp_qty', 0);
$price = FormLib::get_form_value('vp_price', 0);
$mixmatch = FormLib::get_form_value('vp_mm', 0);
$model->pricemethod($method);
$model->quantity($qty);
$model->groupprice($price);
$model->mixmatchcode($mixmatch);
$r1 = $model->save();
if ($r1 === false) {
return false;
} else {
return true;
}
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = FormLib::get_form_value('date1');
$date2 = FormLib::get_form_value('date2');
$upc = $this->form->upc;
if (is_numeric($upc)) {
$upc = BarcodeLib::padUPC($upc);
}
$table = 'prodUpdate';
$def = $dbc->tableDefinition('prodUpdate');
if (!isset($def['prodUpdateID'])) {
// older schema
$table = 'prodUpdateArchive';
}
$query = 'SELECT
upc,
description,
price,
salePrice,
cost,
dept,
tax,
fs,
scale,
likeCode,
modified,
user,
forceQty,
noDisc,
inUse
FROM ' . $table . '
WHERE upc = ?';
$args = array($upc);
if ($date1 !== '' && $date2 !== '') {
// optional: restrict report by date
$query .= ' AND modified BETWEEN ? AND ? ';
$args[] = $date1 . ' 00:00:00';
$args[] = $date2 . ' 23:59:59';
}
$query .= ' ORDER BY modified DESC';
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array($row['modified'], $row['description'], $row['price'], $row['cost'], $row['dept'], $row['tax'], $row['fs'], $row['scale'], $row['forceQty'], $row['noDisc'], $row['user']);
$data[] = $record;
}
return $data;
}
