private function getBatchType()
{
$type = new BatchTypeModel($this->connection);
$type->discType(0);
foreach ($type->find('batchTypeID') as $t) {
return $t->batchTypeID();
}
return false;
}
function body_content()
{
$this->addScript('type-editor.js');
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$model = new BatchTypeModel($dbc);
$ret = '<table class="table">';
$ret .= '<tr>
<th>ID#</th>
<th>Description</th>
<th>Discount Type</th>
<th>Dated Signs</th>
<th title="Special Order Eligible">SO Eligible</th>
<th>Editing Interface</th>
<th> </td>
</tr>';
foreach ($model->find('batchTypeID') as $obj) {
$ret .= sprintf('<tr>
<td>%d</td>
<td><input type="text" class="form-control" onchange="saveDesc.call(this,this.value,%d)" value="%s" /></td>
<td><select onchange="saveType.call(this, $(this).val(),%d);" class="form-control">', $obj->batchTypeID(), $obj->batchTypeID(), $obj->typeDesc(), $obj->batchTypeID());
$found = false;
foreach ($this->price_methods as $id => $desc) {
if ($id == $obj->discType()) {
$found = true;
$ret .= sprintf('<option value="%d" selected>%d %s</option>', $id, $id, $desc);
} else {
$ret .= sprintf('<option value="%d">%d %s</option>', $id, $id, $desc);
}
}
if (!$found) {
$ret .= sprintf('<option value="%d" selected>%d (Custom)</option>', $w['discType'], $w['discType']);
}
$ret .= '</select></td>';
$ret .= sprintf('<td align="center">
<input type="checkbox" %s onchange="saveDated.call(this, %d);" />
</td>', $obj->datedSigns() ? 'checked' : '', $obj->batchTypeID());
$ret .= sprintf('<td align="center">
<input type="checkbox" %s onchange="saveSO.call(this, %d);" />
</td>', $obj->specialOrderEligible() ? 'checked' : '', $obj->batchTypeID());
$ret .= sprintf('<td>
<select onchange="saveUI.call(this, $(this).val(),%d);" class="form-control">', $obj->batchTypeID());
foreach ($this->editor_uis as $id => $desc) {
$ret .= sprintf('<option %s value="%d">%s</option>', $id == $obj->editorUI() ? 'selected' : '', $id, $desc);
}
$ret .= '</select></td>';
$ret .= sprintf('<td><a href="BatchTypeEditor.php?deltype=yes&bid=%d"
class="btn btn-danger btn-sm"
onclick="return confirm(\'Are you sure?\');">%s</a>
</td></tr>', $obj->batchTypeID(), \COREPOS\Fannie\API\lib\FannieUI::deleteIcon());
}
$ret .= '</table>';
$ret .= '<p><button onclick="location=\'BatchTypeEditor.php?addtype=yes\';"
class="btn btn-default">Create New Type</button></p>';
return $ret;
}
function post_createBatch_handler()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$type = $this->form->batchType;
$name = $this->form->batchName;
$startdate = $this->form->startDate;
$enddate = $this->form->endDate;
$owner = $this->form->batchOwner;
$priority = 0;
$upcs = $this->form->upc;
$prices = $this->form->price;
$btype = new BatchTypeModel($dbc);
$btype->batchTypeID($type);
if (!$btype->load()) {
echo 'Invalid Batch Type ' . $type;
return false;
}
$discounttype = $btype->discType();
// make sure item data is present before creating batch
if (!is_array($upcs) || !is_array($prices) || count($upcs) != count($prices) || count($upcs) == 0) {
echo 'Invalid item data';
return false;
}
$batch = new BatchesModel($dbc);
$batch->startDate($startdate);
$batch->endDate($enddate);
$batch->batchName($name);
$batch->batchType($type);
$batch->discountType($discounttype);
$batch->priority($priority);
$batch->owner($owner);
$batchID = $batch->save();
if ($this->config->get('STORE_MODE') === 'HQ') {
StoreBatchMapModel::initBatch($batchID);
}
if ($dbc->tableExists('batchowner')) {
$insQ = $dbc->prepare_statement("insert batchowner values (?,?)");
$insR = $dbc->exec_statement($insQ, array($batchID, $owner));
}
// add items to batch
for ($i = 0; $i < count($upcs); $i++) {
$upc = $upcs[$i];
$price = isset($prices[$i]) ? $prices[$i] : 0.0;
$list = new BatchListModel($dbc);
$list->upc(BarcodeLib::padUPC($upc));
$list->batchID($batchID);
$list->salePrice($price);
$list->groupSalePrice($price);
$list->active(0);
$list->pricemethod(0);
$list->quantity(0);
$list->save();
}
/**
If tags were requested and it's price change batch, make them
Lookup vendor info for each item then add a shelftag record
*/
$tagset = $this->form->tagset;
if ($discounttype == 0 && $tagset !== '') {
$vendorID = $this->form->preferredVendor;
$tag = new ShelftagsModel($dbc);
$product = new ProductsModel($dbc);
for ($i = 0; $i < count($upcs); $i++) {
$upc = $upcs[$i];
$price = isset($prices[$i]) ? $prices[$i] : 0.0;
$product->upc($upc);
$info = $product->getTagData($price);
$tag->id($tagset);
$tag->upc($upc);
$tag->description($info['description']);
$tag->normal_price($price);
$tag->brand($info['brand']);
$tag->sku($info['sku']);
$tag->size($info['size']);
$tag->units($info['units']);
$tag->vendor($info['vendor']);
$tag->pricePerUnit($info['pricePerUnit']);
$tag->save();
}
}
return 'Location: newbatch/BatchManagementTool.php?startAt=' . $batchID;
}
protected function showBatchDisplay($id, $order = 'natural')
{
global $FANNIE_SERVER_DBMS, $FANNIE_URL;
$dbc = $this->connection;
$uid = getUID($this->current_user);
$uid = ltrim($uid, '0');
$orderby = '';
switch ($order) {
case 'upc_a':
$orderby = 'ORDER BY b.upc ASC';
break;
case 'upc_d':
$orderby = 'ORDER BY b.upc DESC';
break;
case 'desc_a':
$orderby = 'ORDER BY description ASC';
break;
case 'desc_d':
$orderby = 'ORDER BY description DESC';
break;
case 'price_a':
$orderby = 'ORDER BY p.normal_price ASC';
break;
case 'price_d':
$orderby = 'ORDER BY p.normal_price DESC';
break;
case 'sale_a':
$orderby = 'ORDER BY b.salePrice ASC';
break;
case 'sale_d':
$orderby = 'ORDER BY b.salePrice DESC';
break;
case 'loc_a':
$orderby = 'ORDER BY m.super_name,y.subsection,y.shelf_set,y.shelf';
break;
case 'loc_d':
$orderby = 'ORDER BY m.super_name DESC,y.subsection DESC,y.shelf_set DESC,y.shelf DESC';
break;
case 'brand_a':
$orderby = 'ORDER BY p.brand ASC';
break;
case 'brand_d':
$orderby = 'ORDER BY p.brand DESC';
break;
case 'natural':
default:
$orderby = 'ORDER BY b.listID DESC';
break;
}
$model = new BatchesModel($dbc);
$model->batchID($id);
$model->load();
$name = $model->batchName();
$type = $model->batchType();
$dtype = $model->discountType();
$start = strtotime($model->startDate());
$end = strtotime($model->endDate()) + 60 * 60 * 24;
$typeModel = new BatchTypeModel($dbc);
$typeModel->batchTypeID($type);
$typeModel->load();
if ($typeModel->editorUI() == 2) {
return $this->showPairedBatchDisplay($id, $name);
}
$limit = $model->transLimit();
$hasLimit = $limit > 0 ? true : false;
$saleHeader = "Sale Price";
if ($dtype == 3) {
$saleHeader = "\$ Discount";
} elseif ($dtype == 4) {
$saleHeader = "% Discount";
} elseif ($dtype == 0) {
$saleHeader = "New price";
}
$fetchArgs = array();
$fetchQ = "\n SELECT b.upc,\n CASE \n WHEN l.likeCode IS NULL THEN p.description\n ELSE l.likeCodeDesc \n END AS description,\n p.normal_price,\n b.salePrice,\n CASE WHEN c.upc IS NULL then 0 ELSE 1 END as isCut,\n b.quantity,\n b.pricemethod,\n m.super_name, \n y.subsection, \n y.shelf_set, \n y.shelf,\n p.brand\n FROM batchList AS b \n " . DTrans::joinProducts('b') . "\n LEFT JOIN likeCodes AS l ON b.upc = CONCAT('LC',CONVERT(l.likeCode,CHAR))\n LEFT JOIN batchCutPaste AS c ON b.upc=c.upc AND b.batchID=c.batchID\n LEFT JOIN prodPhysicalLocation AS y ON b.upc=y.upc\n LEFT JOIN superDeptNames AS m ON y.section=m.superID\n WHERE b.batchID = ? \n {$orderby}";
$fetchArgs[] = $id;
if ($dbc->dbms_name() == "mssql") {
$fetchQ = "select b.upc,\n case when l.likecode is null then p.description\n else l.likecodedesc end as description,\n p.normal_price,b.salePrice,\n CASE WHEN c.upc IS NULL then 0 ELSE 1 END as isCut,\n b.quantity,b.pricemethod\n from batchList as b \n " . DTrans::joinProducts('b') . "\n left join likeCodes as l on\n b.upc = 'LC'+convert(varchar,l.likecode)\n left join batchCutPaste as c ON\n b.upc=c.upc AND b.batchID=c.batchID\n where b.batchID = ? {$orderby}";
}
$sections = array();
if ($dbc->tableExists('FloorSections')) {
$floor = new FloorSectionsModel($dbc);
foreach ($floor->find() as $f) {
$sections[$f->floorSectionID()] = $f->name();
}
$fetchQ = str_replace('y.subsection', 'y.floorSectionID', $fetchQ);
}
$fetchP = $dbc->prepare_statement($fetchQ);
$fetchR = $dbc->exec_statement($fetchP, $fetchArgs);
$overlapP = $dbc->prepare('
SELECT b.batchID,
b.batchName
FROM batchList as l
INNER JOIN batches AS b ON b.batchID=l.batchID
WHERE l.upc=?
AND l.batchID <> ?
AND b.discounttype <> 0
AND (
(b.startDate BETWEEN ? AND ?)
OR
(b.endDate BETWEEN ? AND ?)
)
');
$overlap_args = array($model->startDate(), $model->endDate(), $model->startDate(), $model->endDate());
$cpCount = $dbc->prepare_statement("SELECT count(*) FROM batchCutPaste WHERE uid=?");
$res = $dbc->exec_statement($cpCount, array($uid));
$row = $dbc->fetch_row($res);
$cpCount = $row[0];
$ret = "<span class=\"newBatchBlack\"><b>Batch name</b>: {$name}</span> | ";
$ret .= '<b>Sale Dates</b>: ' . date('Y-m-d', strtotime($model->startDate())) . ' - ' . date('Y-m-d', strtotime($model->endDate())) . '<br />';
if ($this->config->get('STORE_MODE') === 'HQ') {
$stores = new StoresModel($dbc);
$mapP = $dbc->prepare('SELECT storeID FROM StoreBatchMap WHERE storeID=? AND batchID=?');
foreach ($stores->find('storeID') as $s) {
$mapR = $dbc->execute($mapP, array($s->storeID(), $id));
$checked = $mapR && $dbc->numRows($mapR) ? 'checked' : '';
$ret .= sprintf('<label>
<input type="checkbox" onchange="toggleStore(%d, %d);" %s />
%s
</label> | ', $s->storeID(), $id, $checked, $s->description());
}
$ret .= '<br />';
}
$ret .= "<a href=\"BatchListPage.php\">Back to batch list</a> | ";
$ret .= sprintf('<input type="hidden" id="batch-discount-type" value="%d" />', $model->discountType());
/**
Price change batches probably want the upcoming retail
rather than the current retail. Current sales will want
the current sale price; future sales will want the future
sale price. Past sales probably won't print signs under
normal circumstances.
*/
$future_mode = false;
if ($model->discountType() == 0) {
$future_mode = true;
} elseif (strtotime($model->startDate()) >= strtotime(mktime(0, 0, 0, date('n'), date('j'), date('Y')))) {
$future_mode = true;
}
$ret .= sprintf('<input type="hidden" id="batch-future-mode" value="%d" />', $future_mode ? 1 : 0);
$ret .= "<a href=\"../../admin/labels/SignFromSearch.php?batch={$id}\">Print Sale Signs</a> | ";
$ret .= "<a href=\"BatchSignStylesPage.php?id={$id}\">Sign Pricing</a> | ";
$ret .= "<a href=\"{$FANNIE_URL}admin/labels/BatchShelfTags.php?batchID%5B%5D={$id}\">Print Shelf Tags</a> | ";
$ret .= "<a href=\"\" onclick=\"generateTags({$id}); return false;\">Auto-tag</a> | ";
if ($cpCount > 0) {
$ret .= "<a href=\"EditBatchPage.php?id={$id}&paste=1\">Paste Items ({$cpCount})</a> | ";
}
if ($dtype == 0 || time() >= $start && time() <= $end) {
$ret .= "<a href=\"\" onclick=\"forceNow({$id}); return false;\">Force batch</a> | ";
}
if ($dtype != 0) {
$ret .= "<a href=\"\" onclick=\"unsaleNow({$id}); return false;\">Stop Sale</a> ";
}
if ($dtype == 0) {
$ret .= " <a href=\"\" onclick=\"trimPcBatch({$id}); return false;\">Trim Unchanged</a> ";
} else {
$ret .= " | <span id=\"edit-limit-link\"><a href=\"\" \n onclick=\"editTransLimit(); return false;\">" . ($hasLimit ? 'Edit' : 'Add') . " Limit</a></span>";
$ret .= "<span id=\"save-limit-link\" class=\"collapse\"><a href=\"\" onclick=\"saveTransLimit({$id}); return false;\">Save Limit</a></span>";
$ret .= " <span class=\"form-group form-inline\" id=\"currentLimit\" style=\"color:#000;\">{$limit}</span>";
}
$ret .= "<br />";
$ret .= "<table id=yeoldetable class=\"table\">";
$ret .= "<tr>";
if ($orderby != "ORDER BY b.upc ASC") {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=upc_a\">UPC</a></th>";
} else {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=upc_d\">UPC</a></th>";
}
if ($orderby != "ORDER BY p.brand ASC") {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=brand_a\">Brand</a></th>";
} else {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=brand_d\">Brand</a></th>";
}
if ($orderby != "ORDER BY description ASC") {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=desc_a\">Description</a></th>";
} else {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=desc_d\">Description</a></th>";
}
if ($orderby != "ORDER BY p.normal_price DESC") {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=price_d\">Normal Price</a></th>";
} else {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=price_a\">Normal Price</a></th>";
}
if ($orderby != "ORDER BY b.salePrice DESC") {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=sale_d\">{$saleHeader}</a></th>";
} else {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=sale_a\">{$saleHeader}</a></th>";
}
$ret .= "<th colspan=\"3\"> </th>";
if ($orderby != 'ORDER BY m.super_name,y.subsection,y.shelf_set,y.shelf') {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=loc_a\">Location</a></th>";
} else {
$ret .= "<th><a href=\"EditBatchPage.php?id={$id}&sort=loc_d\">Location</a></th>";
}
$ret .= "</tr>";
$colors = array('#ffffff', '#ffffcc');
$cur = 0;
while ($fetchW = $dbc->fetch_array($fetchR)) {
$cur = ($cur + 1) % 2;
$ret .= "<tr>";
$fetchW['upc'] = rtrim($fetchW['upc']);
if (substr($fetchW['upc'], 0, 2) == "LC") {
$likecode = rtrim(substr($fetchW['upc'], 2));
$ret .= "<td bgcolor={$colors[$cur]}>" . $fetchW['upc'];
$ret .= " <a href=\"\" onclick=\"\$('.lc-item-{$likecode}').toggle(); return false;\">[+]</a>";
$ret .= "</td>";
} else {
$conflict = '';
if ($dtype != 0) {
$overlapR = $dbc->execute($overlapP, array_merge(array($fetchW['upc'], $id), $overlap_args));
if ($overlapR && $dbc->numRows($overlapR)) {
$overlap = $dbc->fetchRow($overlapR);
$conflict = sprintf('<a href="EditBatchPage.php?id=%d" target="_batch%d"
title="Conflicts with batch %s" class="btn btn-xs btn-danger">
<span class="glyphicon glyphicon-exclamation-sign">
</span></a>', $overlap['batchID'], $overlap['batchID'], $overlap['batchName']);
}
}
$ret .= "<td bgcolor={$colors[$cur]}><a href=\"{$FANNIE_URL}item/ItemEditorPage.php?searchupc={$fetchW['upc']}\" \n target=\"_new{$fetchW['upc']}\">{$fetchW['upc']}</a>{$conflict}</td>";
}
$ret .= "<td bgcolor={$colors[$cur]}>{$fetchW['brand']}</td>";
$ret .= "<td bgcolor={$colors[$cur]}>{$fetchW['description']}</td>";
$ret .= "<td bgcolor={$colors[$cur]}>{$fetchW['normal_price']}</td>";
$qtystr = $fetchW['pricemethod'] > 0 && is_numeric($fetchW['quantity']) && $fetchW['quantity'] > 0 ? $fetchW['quantity'] . " for " : "";
$qty = is_numeric($fetchW['quantity']) && $fetchW['quantity'] > 0 ? $fetchW['quantity'] : 1;
$ret .= "<td bgcolor={$colors[$cur]} class=\"\">";
$ret .= '<span id="editable-text-' . $fetchW['upc'] . '">';
$ret .= '<span class="editable-' . $fetchW['upc'] . ($qty == 1 ? ' collapse ' : '') . '"' . ' id="item-qty-' . $fetchW['upc'] . '" data-name="qty">' . $qty . ' for </span>';
$ret .= "<span class=\"editable-{$fetchW['upc']}\" \n id=\"sale-price-{$fetchW['upc']}\" data-name=\"price\">" . sprintf("%.2f</span>", $fetchW['salePrice']);
$ret .= '</span>';
$ret .= '<div class="form-group form-inline collapse" id="editable-fields-' . $fetchW['upc'] . '">';
$ret .= '<div class="input-group">';
$ret .= sprintf('<input type="text" class="form-control" name="qty" value="%d" />', $qty);
$ret .= '<span class="input-group-addon">for</span>';
$ret .= '<span class="input-group-addon">$</span>';
$ret .= sprintf('<input text="text" class="form-control" name="price" value="%.2f" />', $fetchW['salePrice']);
$ret .= '</div></div></td>';
$ret .= "<td bgcolor={$colors[$cur]} id=editLink{$fetchW['upc']}>\n <a href=\"\" class=\"edit\" onclick=\"editUpcPrice('{$fetchW['upc']}'); return false;\">\n " . \COREPOS\Fannie\API\lib\FannieUI::editIcon() . "</a>\n <a href=\"\" class=\"save collapse\" onclick=\"saveUpcPrice('{$fetchW['upc']}'); return false;\">\n " . \COREPOS\Fannie\API\lib\FannieUI::saveIcon() . "</a>\n </td>";
$ret .= "<td bgcolor={$colors[$cur]}><a href=\"\" \n onclick=\"deleteUPC.call(this, {$id}, '{$fetchW['upc']}'); return false;\">" . \COREPOS\Fannie\API\lib\FannieUI::deleteIcon() . "</a>\n </td>";
if ($fetchW['isCut'] == 1) {
$ret .= "<td bgcolor={$colors[$cur]} id=cpLink{$fetchW['upc']}>\n <a href=\"\" id=\"unCut{$fetchW['upc']}\" onclick=\"cutItem('{$fetchW['upc']}',{$id},{$uid}, 0); return false;\">Undo</a>\n <a href=\"\" class=\"collapse\" id=\"doCut{$fetchW['upc']}\" onclick=\"cutItem('{$fetchW['upc']}',{$id},{$uid}, 1); return false;\">Cut</a>\n </td>";
} else {
$ret .= "<td bgcolor={$colors[$cur]} id=cpLink{$fetchW['upc']}>\n <a href=\"\" class=\"collapse\" id=\"unCut{$fetchW['upc']}\" onclick=\"cutItem('{$fetchW['upc']}',{$id},{$uid},0); return false;\">Undo</a>\n <a href=\"\" id=\"doCut{$fetchW['upc']}\" onclick=\"cutItem('{$fetchW['upc']}',{$id},{$uid},1); return false;\">Cut</a>\n </td>";
}
$loc = 'n/a';
if (!empty($fetchW['subsection'])) {
$loc = substr($fetchW['super_name'], 0, 4);
$loc .= $fetchW['subsection'] . ', ';
$loc .= 'Unit ' . $fetchW['shelf_set'] . ', ';
$loc .= 'Shelf ' . $fetchW['shelf'];
} elseif (!empty($fetchW['floorSectionID'])) {
$loc = $sections[$fetchW['floorSectionID']];
}
$ret .= "<td bgcolor={$colors[$cur]}>" . $loc . '</td>';
$ret .= '<input type="hidden" class="batch-hidden-upc" value="' . $fetchW['upc'] . '" />';
$ret .= "</tr>";
if (substr($fetchW['upc'], 0, 2) == "LC") {
$likecode = rtrim(substr($fetchW['upc'], 2));
$ret .= self::likeToTable($dbc, $likecode, $fetchW['salePrice']);
}
}
$ret .= "</table>";
$ret .= "<input type=hidden id=currentBatchID value={$id} />";
return $ret;
}
public function get_id_view()
{
$this->addScript('pricing-batch.js');
$dbc = $this->connection;
$dbc->selectDB($this->config->OP_DB);
$superID = FormLib::get_form_value('super', 99);
$queueID = FormLib::get('queueID');
$vendorID = $this->id;
$filter = FormLib::get_form_value('filter') == 'Yes' ? True : False;
/* lookup vendor and superdept names to build a batch name */
$sname = "All";
if ($superID != 99) {
$smodel = new SuperDeptNamesModel($dbc);
$smodel->superID($superID);
$smodel->load();
$sname = $smodel->super_name();
}
$vendor = new VendorsModel($dbc);
$vendor->vendorID($vendorID);
$vendor->load();
$batchName = $sname . " " . $vendor->vendorName() . " PC " . date('m/d/y');
/* find a price change batch type */
$types = new BatchTypeModel($dbc);
$types->discType(0);
$bType = 0;
foreach ($types->find() as $obj) {
$bType = $obj->batchTypeID();
break;
}
/* get the ID of the current batch. Create it if needed. */
$bidQ = $dbc->prepare("\n SELECT batchID \n FROM batches \n WHERE batchName=? \n AND batchType=? \n AND discounttype=0\n ORDER BY batchID DESC");
$bidR = $dbc->execute($bidQ, array($batchName, $bType));
$batchID = 0;
if ($dbc->numRows($bidR) == 0) {
$b = new BatchesModel($dbc);
$b->batchName($batchName);
$b->startDate('1900-01-01');
$b->endDate('1900-01-01');
$b->batchType($bType);
$b->discountType(0);
$b->priority(0);
$batchID = $b->save();
} else {
$bidW = $dbc->fetchRow($bidR);
$batchID = $bidW['batchID'];
}
$ret = sprintf('<b>Batch</b>:
<a href="%sbatches/newbatch/BatchManagementTool.php?startAt=%d">%s</a>', $this->config->URL, $batchID, $batchName);
$ret .= sprintf("<input type=hidden id=vendorID value=%d />\n <input type=hidden id=batchID value=%d />\n <input type=hidden id=queueID value=%d />\n <input type=hidden id=superID value=%d />", $vendorID, $batchID, $queueID, $superID);
$batchUPCs = array();
$batchList = new BatchListModel($dbc);
$batchList->batchID($batchID);
foreach ($batchList->find() as $obj) {
$batchUPCs[$obj->upc()] = true;
}
$costSQL = Margin::adjustedCostSQL('v.cost', 'b.discountRate', 'b.shippingMarkup');
$marginSQL = Margin::toMarginSQL($costSQL, 'p.normal_price');
$p_def = $dbc->tableDefinition('products');
$marginCase = '
CASE
WHEN g.margin IS NOT NULL AND g.margin <> 0 THEN g.margin
WHEN s.margin IS NOT NULL AND s.margin <> 0 THEN s.margin
ELSE d.margin
END';
$srpSQL = Margin::toPriceSQL($costSQL, $marginCase);
$query = "SELECT p.upc,\n p.description,\n v.cost,\n b.shippingMarkup,\n b.discountRate,\n p.normal_price,\n " . Margin::toMarginSQL($costSQL, 'p.normal_price') . " AS current_margin,\n " . Margin::toMarginSQL($costSQL, 'v.srp') . " AS desired_margin,\n " . $costSQL . " AS adjusted_cost,\n v.srp,\n " . $srpSQL . " AS rawSRP,\n v.vendorDept,\n x.variable_pricing,\n " . $marginCase . " AS margin\n FROM products AS p \n INNER JOIN vendorItems AS v ON p.upc=v.upc AND p.default_vendor_id=v.vendorID\n INNER JOIN vendors as b ON v.vendorID=b.vendorID\n LEFT JOIN departments AS d ON p.department=d.dept_no\n LEFT JOIN vendorDepartments AS s ON v.vendorDept=s.deptID AND v.vendorID=s.vendorID\n LEFT JOIN VendorSpecificMargins AS g ON p.department=g.deptID AND v.vendorID=g.vendorID\n LEFT JOIN prodExtra AS x on p.upc=x.upc ";
$args = array($vendorID);
if ($superID != 99) {
$query .= " LEFT JOIN MasterSuperDepts AS m\n ON p.department=m.dept_ID ";
}
$query .= "WHERE v.cost > 0 \n AND v.vendorID=?\n AND p.inUse=1 ";
if ($superID != 99) {
$query .= " AND m.superID=? ";
$args[] = $superID;
}
if ($filter === false) {
$query .= " AND p.normal_price <> v.srp ";
}
if ($this->config->get('STORE_MODE') == 'HQ') {
$query .= ' AND p.store_id=? ';
$args[] = $this->config->get('STORE_ID');
}
$query .= " ORDER BY p.upc";
if (isset($p_def['price_rule_id'])) {
$query = str_replace('x.variable_pricing', 'p.price_rule_id AS variable_pricing', $query);
}
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
$ret .= "<table class=\"table table-bordered small\">";
$ret .= "<tr><td colspan=6> </td><th colspan=2>Current</th>\n <th colspan=3>Vendor</th></tr>";
$ret .= "<tr><th>UPC</th><th>Our Description</th>\n <th>Base Cost</th>\n <th>Shipping</th>\n <th>Discount%</th>\n <th>Adj. Cost</th>\n <th>Price</th><th>Margin</th><th>Raw</th><th>SRP</th>\n <th>Margin</th><th>Cat</th><th>Var</th>\n <th>Batch</th></tr>";
while ($row = $dbc->fetch_row($result)) {
$background = "white";
if (isset($batchUPCs[$row['upc']])) {
$background = 'selection';
} elseif ($row['variable_pricing'] == 0) {
$background = $row['normal_price'] + 0.1 < $row['rawSRP'] ? 'red' : 'green';
if ($row['normal_price'] - 0.1 > $row['rawSRP']) {
$background = $row['normal_price'] - 0.1 > $row['rawSRP'] ? 'yellow' : 'green';
}
}
if (isset($batchUPCs[$row['upc']])) {
$icon = '<span class="glyphicon glyphicon-minus-sign"
title="Remove from batch">
</span>';
} else {
$icon = '<span class="glyphicon glyphicon-plus-sign"
title="Add to batch">
</span>';
}
$ret .= sprintf("<tr id=row%s class=%s>\n <td class=\"sub\"><a href=\"%sitem/ItemEditorPage.php?searchupc=%s\">%s</a></td>\n <td class=\"sub\">%s</td>\n <td class=\"sub cost\">%.2f</td>\n <td class=\"sub shipping\">%.2f%%</td>\n <td class=\"sub discount\">%.2f%%</td>\n <td class=\"sub adj-cost\">%.2f</td>\n <td class=\"sub price\">%.2f</td>\n <td class=\"sub cmargin\">%.2f%%</td>\n <td class=\"sub raw-srp\">%.2f</td>\n <td onclick=\"reprice('%s');\" class=\"sub srp\">%.2f</td>\n <td class=\"sub dmargin\">%.2f%%</td>\n <td class=\"sub\">%d</td>\n <td><input class=varp type=checkbox onclick=\"toggleV('%s');\" %s /></td>\n <td class=white>\n <a class=\"add-button %s\" href=\"\" \n onclick=\"addToBatch('%s'); return false;\">\n <span class=\"glyphicon glyphicon-plus-sign\"\n title=\"Add item to batch\"></span>\n </a>\n <a class=\"remove-button %s\" href=\"\" \n onclick=\"removeFromBatch('%s'); return false;\">\n <span class=\"glyphicon glyphicon-minus-sign\"\n title=\"Remove item from batch\"></span>\n </a>\n </td>\n </tr>", $row['upc'], $background, $this->config->URL, $row['upc'], $row['upc'], $row['description'], $row['cost'], $row['shippingMarkup'] * 100, $row['discountRate'] * 100, $row['adjusted_cost'], $row['normal_price'], 100 * $row['current_margin'], $row['rawSRP'], $row['upc'], $row['srp'], 100 * $row['desired_margin'], $row['vendorDept'], $row['upc'], $row['variable_pricing'] >= 1 ? 'checked' : '', isset($batchUPCs[$row['upc']]) ? 'collapse' : '', $row['upc'], !isset($batchUPCs[$row['upc']]) ? 'collapse' : '', $row['upc']);
}
$ret .= "</table>";
return $ret;
}
