public function get_id_view()
{
$dbc = $this->connection;
$date = FormLib::get('date', false);
$args = array($this->id);
if ($date) {
$this->itemQ .= ' AND r.reviewDate BETWEEN ? AND ?';
$args[] = $date . ' 00:00:00';
$args[] = $date . ' 23:59:59';
}
$itemP = $dbc->prepare($this->itemQ);
$itemR = $dbc->execute($itemP, $args);
if ($dbc->numRows($itemR) == 0) {
return '<div class="alert alert-warning">No applicable items</div>' . $this->get_view();
}
$typeID = $this->getBatchType();
if ($typeID === false) {
return '<div class="alert alert-danger">Cannot create a price change batch</div>' . $this->get_view();
}
$batch = new BatchesModel($dbc);
$batch->batchName('EDLP Price Change');
$yesterday = date('Y-m-d', strtotime('yesterday'));
$batch->startDate($yesterday);
$batch->endDate($yesterday);
$batch->batchType($typeID);
$batch->discountType(0);
$batchID = $batch->save();
$list = new BatchListModel($dbc);
$list->batchID($batchID);
while ($itemW = $dbc->fetchRow($itemR)) {
$list->upc($itemW['upc']);
$list->salePrice($itemW['maxPrice']);
$list->save();
}
return sprintf('<div class="alert alert-success">Created Batch.
<a href="../newbatch/EditBatchPage.php?id=%d">View it</a>.</div>', $batchID) . $this->get_view();
}
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;
}
function post_newbatch_tags_upcs_newprices_handler()
{
global $FANNIE_OP_DB, $FANNIE_URL;
$this->upcs = json_decode($this->upcs);
$this->newprices = json_decode($this->newprices);
$dbc = FannieDB::get($FANNIE_OP_DB);
// try to lookup batch owner
// failure is ok
$owner = '';
$ownerP = $dbc->prepare('SELECT super_name FROM superDeptNames WHERE superID=?');
$ownerR = $dbc->execute($ownerP, array($this->tags));
if ($dbc->num_rows($ownerR) > 0) {
$ownerW = $dbc->fetch_row($ownerR);
$owner = $ownerW['super_name'];
}
// try to lookup correct discount type
// failure is ok
$btype = 0;
$btypeR = $dbc->query('SELECT batchTypeID FROM batchType WHERE discType=0 ORDER BY batchTypeID');
if ($dbc->num_rows($btypeR) > 0) {
$btypeW = $dbc->fetch_row($btypeR);
$btype = $btypeW['batchTypeID'];
}
// create batch. date 'yesterday' ensures it doesn't
// run automatically unless the user re-schedules it
$b = new BatchesModel($dbc);
$b->batchName($this->newbatch);
$b->startDate(date('Y-m-d', strtotime('yesterday')));
$b->endDate(date('Y-m-d', strtotime('yesterday')));
$b->batchType($btype);
$b->discountType(0);
$b->priority(0);
$b->owner($owner);
$id = $b->save();
// maintain @deprecated table if present
if ($dbc->tableExists('batchowner')) {
$insQ = $dbc->prepare_statement("insert batchowner values (?,?)");
$insR = $dbc->exec_statement($insQ, array($id, $owner));
}
// add items to batch
for ($i = 0; $i < count($this->upcs); $i++) {
$upc = $this->upcs[$i];
if (!isset($this->newprices[$i])) {
continue;
// should not happen
}
$price = $this->newprices[$i];
$bl = new BatchListModel($dbc);
$bl->upc(BarcodeLib::padUPC($upc));
$bl->batchID($id);
$bl->salePrice($price);
$bl->active(0);
$bl->pricemethod(0);
$bl->quantity(0);
$bl->save();
}
// did not select "none" for tags
// so create some shelftags
if ($this->tags != -1) {
$lookup = $dbc->prepare('SELECT p.description, v.brand, v.sku, v.size, v.units, n.vendorName
FROM products AS p LEFT JOIN vendorItems AS v ON p.upc=v.upc
LEFT JOIN vendors AS n ON v.vendorID=n.vendorID
WHERE p.upc=? ORDER BY v.vendorID');
$tag = new ShelfTagModel($dbc);
for ($i = 0; $i < count($this->upcs); $i++) {
$upc = $this->upcs[$i];
if (!isset($this->newprices[$i])) {
continue;
// should not happen
}
$price = $this->newprices[$i];
$info = array('description' => '', 'brand' => '', 'sku' => '', 'size' => '', 'units' => 1, 'vendorName' => '');
$lookupR = $dbc->execute($lookup, array($upc));
if ($dbc->num_rows($lookupR) > 0) {
$info = $dbc->fetch_row($lookupR);
}
$ppo = $info['size'] !== '' ? \COREPOS\Fannie\API\lib\PriceLib::pricePerUnit($price, $info['size']) : '';
$tag->id($this->tags);
$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['vendorName']);
$tag->pricePerUnit($ppo);
$tag->save();
}
}
echo $FANNIE_URL . 'newbatch/BatchManagementTool.php?startAt=' . $id;
return false;
}
protected function post_id_batchName_batchType_startDate_endDate_owner_handler()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$infoQ = $dbc->prepare("SELECT discType \n FROM batchType \n WHERE batchTypeID=?");
$infoR = $dbc->exec_statement($infoQ, array($this->batchType));
$infoW = $dbc->fetch_row($infoR);
$discounttype = $infoW['discType'];
$model = new BatchesModel($dbc);
$model->batchID($this->id);
$model->batchType($this->batchType);
$model->batchName($this->batchName);
$model->startDate($this->startDate);
$model->endDate($this->endDate);
$model->discountType($discounttype);
$model->owner($this->owner);
$saved = $model->save();
if ($dbc->tableExists('batchowner')) {
$checkQ = $dbc->prepare_statement("select batchID from batchowner where batchID=?");
$checkR = $dbc->exec_statement($checkQ, array($this->id));
if ($dbc->num_rows($checkR) == 0) {
$insQ = $dbc->prepare_statement("insert batchowner values (?,?)");
$insR = $dbc->exec_statement($insQ, array($this->id, $this->owner));
} else {
$upQ = $dbc->prepare_statement("update batchowner set owner=? where batchID=?");
$upR = $dbc->exec_statement($upQ, array($this->owner, $this->id));
}
}
$json = array('error' => 0, 'msg' => 'Saved batch ' . $this->batchName);
if ($saved === false) {
$json['error'] = 1;
$json['msg'] = 'Error saving batch ' . $this->batchName;
}
echo json_encode($json);
return false;
}
protected function hookAddColumnowner()
{
// copy existing values from batchowner.owner to
// new batches.owner column
if ($this->connection->table_exists('batchowner')) {
$dataR = $this->connection->query('SELECT batchID, owner FROM batchowner');
$tempModel = new BatchesModel($this->connection);
while ($dataW = $this->connection->fetch_row($dataR)) {
$tempModel->reset();
$tempModel->batchID($dataW['batchID']);
if ($tempModel->load()) {
$tempModel->owner($dataW['owner']);
$tempModel->save();
}
}
}
}
protected function post_id_qualifiers_discount_handler()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$batch = new BatchesModel($dbc);
$batch->batchID($this->id);
if (FormLib::get('member') == '1') {
$batch->discountType(2);
} else {
$batch->discountType(1);
}
$save1 = $batch->save();
$pmethod = 4;
if (FormLib::get('split') == '1') {
$pmethod = 3;
}
$upQ2 = $dbc->prepare_statement("UPDATE batchList SET\n quantity=?,pricemethod=?,\n salePrice=? WHERE batchID=?\n AND salePrice >= 0");
$upQ3 = $dbc->prepare_statement("UPDATE batchList SET\n quantity=?,pricemethod=?,\n salePrice=? WHERE batchID=?\n AND salePrice < 0");
$save2 = $dbc->exec_statement($upQ2, array($this->qualifiers + 1, $pmethod, $this->discount, $this->id));
$save3 = $dbc->exec_statement($upQ3, array($this->qualifiers + 1, $pmethod, -1 * $this->discount, $this->id));
$json['error'] = 0;
if (!$save1 || !$save2 || !$save3) {
$json['error'] = 'Error saving paired sale settings';
}
echo json_encode($json);
return false;
}
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;
}
