public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
if (!is_array($this->form->ids)) {
$this->form->ids = array($this->form->ids);
}
$data = array();
$model = new BatchesModel($dbc);
foreach ($this->form->ids as $batchID) {
$model->batchID($batchID);
if ($model->load() === false) {
continue;
}
list($start, $time) = explode(' ', $model->startDate(), 2);
list($end, $time) = explode(' ', $model->endDate(), 2);
$dlog = DTransactionsModel::selectDlog($start, $end);
$q = "SELECT MIN(tdate) as weekStart, MAX(tdate) as weekEnd,\n batchName, sum(total) as sales, sum(d.quantity) as qty\n FROM {$dlog} AS d INNER JOIN\n batchList AS l ON l.upc=d.upc\n LEFT JOIN batches AS b ON l.batchID=b.batchID\n WHERE l.batchID = ?\n AND d.tdate BETWEEN ? AND ?\n GROUP BY " . $dbc->week('tdate') . ", batchName\n ORDER BY batchName, MIN(tdate)";
$p = $dbc->prepare_statement($q);
$r = $dbc->exec_statement($p, array($batchID, $start . ' 00:00:00', $end . ' 23:59:59'));
while ($w = $dbc->fetch_row($r)) {
list($s, $time) = explode(' ', $w['weekStart'], 2);
list($e, $time) = explode(' ', $w['weekEnd'], 2);
$record = array($s, $e, $w['batchName'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['sales']));
$data[] = $record;
}
}
return $data;
}
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();
}
$out .= addItemUPCInput('true');
$out .= '`';
$out .= showBatchDisplay($id);
break;
case 'redisplay':
$mode = $_GET['mode'];
$out .= batchListDisplay('', $mode);
break;
case 'forceBatch':
$id = $_GET['id'];
$model = new BatchesModel($sql);
$model->forceStartBatch($id);
break;
case 'unsale':
$id = $_GET['id'];
$model = new BatchesModel($sql);
$model->forceStopBatch($id);
break;
case 'switchToLC':
$out .= addItemLCInput();
break;
case 'switchFromLC':
$out .= addItemUPCInput();
break;
case 'redisplayWithOrder':
$id = $_GET['id'];
$order = $_GET['order'];
$out .= showBatchDisplay($id, $order);
break;
case 'expand':
$likecode = $_GET['likecode'];
/**
Create a one-item sale. Requires sample data
for item, batch types
*/
public function unitTest($phpunit)
{
$this->u = array('0001878777132');
//14.99
$this->post_u_handler();
$phpunit->assertEquals(1, count($this->upcs));
$post = $this->post_u_view();
$phpunit->assertNotEquals(0, strlen($post));
$form = new \COREPOS\common\mvc\ValueContainer();
$form->upc = $this->u;
$form->preferredVendor = 0;
$this->setForm($form);
ob_start();
$this->post_redoSRPs_handler();
$json = ob_get_clean();
$arr = json_decode($json, true);
$phpunit->assertInternalType('array', $arr);
$phpunit->assertEquals(1, count($arr));
$phpunit->assertEquals($this->u[0], $arr[0]['upc']);
$phpunit->assertEquals(0, $arr[0]['srp']);
$form->startDate = date('Y-m-d');
$form->endDate = date('Y-m-d');
$form->batchName = 'Test BatchFromSearch';
$form->batchType = 3;
// price change batch means tags get created
$form->batchOwner = 'IT';
$form->price = array(1.99);
$form->tagset = 1;
$this->setForm($form);
$this->post_createBatch_handler();
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$batch = new BatchesModel($dbc);
$batch->batchName('Test BatchFromSearch');
$phpunit->assertEquals(1, count($batch->find()));
$sale = new BatchListModel($dbc);
$sale->upc($this->u[0]);
$sale->salePrice(1.99);
$phpunit->assertEquals(1, count($sale->find()));
$tag = new ShelftagsModel($dbc);
$tag->id(1);
$tag->upc($this->u[0]);
$phpunit->assertEquals(true, $tag->load());
}
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;
}
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;
}
/**
Create, update, and delete a batch
Try each mode with and without an owner filter
*/
public function unitTest($phpunit)
{
$get = $this->get_view();
$phpunit->assertNotEquals(0, strlen($get));
$this->connection->selectDB($this->config->get('OP_DB'));
$model = new BatchesModel($this->connection);
$this->newType = 1;
$this->newName = 'Test BatchListPage';
$this->newStart = date('Y-m-d 00:00:00');
$this->newEnd = date('Y-m-d 00:00:00');
$this->newOwner = 'IT';
ob_start();
$this->post_newType_newName_newStart_newEnd_newOwner_handler();
ob_end_clean();
$model->batchName($this->newName);
$matches = $model->find();
$phpunit->assertEquals(1, count($matches));
$model->reset();
$model->batchID($matches[0]->batchID());
$phpunit->assertEquals(true, $model->load());
$phpunit->assertEquals($this->newType, $model->batchType());
$phpunit->assertEquals($this->newName, $model->batchName());
$phpunit->assertEquals($this->newStart, $model->startDate());
$phpunit->assertEquals($this->newEnd, $model->endDate());
$phpunit->assertEquals($this->newOwner, $model->owner());
$this->id = $model->batchID();
$this->batchName = 'Change BatchListPage';
$this->batchType = 2;
$this->startDate = date('Y-m-d 00:00:00', strtotime('yesterday'));
$this->endDate = $this->startDate;
$this->owner = 'Admin';
ob_start();
$this->post_id_batchName_batchType_startDate_endDate_owner_handler();
ob_end_clean();
$model->reset();
$model->batchID($this->id);
$phpunit->assertEquals(true, $model->load());
$phpunit->assertEquals($this->batchType, $model->batchType());
$phpunit->assertEquals($this->batchName, $model->batchName());
$phpunit->assertEquals($this->startDate, $model->startDate());
$phpunit->assertEquals($this->endDate, $model->endDate());
$phpunit->assertEquals($this->owner, $model->owner());
$this->delete = 1;
ob_start();
$this->post_delete_id_handler();
ob_end_clean();
$model->reset();
$model->batchID($this->id);
$phpunit->assertEquals(false, $model->load());
$modes = array('pending', 'current', 'historical', 'all');
foreach ($modes as $m) {
$get = $this->batchListDisplay('', $m, rand(0, 50));
$phpunit->assertNotEquals(0, strlen($get));
$get = $this->batchListDisplay('IT', $m, rand(0, 50));
$phpunit->assertNotEquals(0, strlen($get));
}
}
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();
}
}
}
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$bStart = FormLib::get_form_value('date1', '');
$bEnd = FormLib::get_form_value('date2', '');
$model = new BatchesModel($dbc);
/**
Assemble argument array and appropriate string
for an IN clause in a prepared statement
*/
$batchID = $this->form->batchID;
if (!is_array($batchID)) {
$batchID = array($batchID);
}
$inArgs = array();
$inClause = '(';
$upcs = array();
foreach ($batchID as $bID) {
$inClause .= '?,';
$inArgs[] = $bID;
$upcs = array_merge($upcs, $model->getUPCs($bID));
}
$upcs = array_unique($upcs);
$inClause = rtrim($inClause, ',') . ')';
$batchInfoQ = '
SELECT batchName,
year(startDate) as sy,
month(startDate) as sm,
day(startDate) as sd,
year(endDate) as ey,
month(endDate) as em,
day(endDate) as ed
FROM batches
WHERE batchID IN ' . $inClause;
$batchInfoP = $dbc->prepare($batchInfoQ);
$batchInfoR = $dbc->execute($batchInfoP, $inArgs);
$bName = "";
while ($batchInfoW = $dbc->fetchRow($batchInfoR)) {
$bName .= $batchInfoW['batchName'] . " ";
if (empty($bStart)) {
$bStart = sprintf("%d-%02d-%02d", $batchInfoW['sy'], $batchInfoW['sm'], $batchInfoW['sd']);
}
if (empty($bEnd)) {
$bEnd = sprintf("%d-%02d-%02d", $batchInfoW['ey'], $batchInfoW['em'], $batchInfoW['ed']);
}
}
$dlog = DTransactionsModel::selectDlog($bStart, $bEnd);
$bStart .= ' 00:00:00';
$bEnd .= ' 23:59:59';
$reportArgs = array($bStart, $bEnd);
list($in_sql, $reportArgs) = $dbc->safeInClause($upcs, $reportArgs);
$salesBatchQ = "\n SELECT d.upc, \n p.description, \n l.floorSectionID,\n f.name AS location,\n SUM(d.total) AS sales, " . DTrans::sumQuantity('d') . " AS quantity, \n SUM(CASE WHEN trans_status IN('','0','R') THEN 1 WHEN trans_status='V' THEN -1 ELSE 0 END) as rings\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'INNER') . "\n LEFT JOIN prodPhysicalLocation AS l ON l.upc=p.upc\n LEFT JOIN FloorSections as f ON f.floorSectionID=l.floorSectionID\n WHERE d.tdate BETWEEN ? AND ?\n AND d.upc IN ({$in_sql})\n GROUP BY d.upc, \n p.description\n ORDER BY d.upc";
$salesBatchP = $dbc->prepare($salesBatchQ);
$inArgs[] = $bStart;
$inArgs[] = $bEnd;
$salesBatchR = $dbc->execute($salesBatchP, $reportArgs);
/**
Simple report
Issue a query, build array of results
*/
$ret = array();
while ($row = $dbc->fetchRow($salesBatchR)) {
$record = array();
$record[] = $row['upc'];
$record[] = $row['description'];
$record[] = sprintf('%.2f', $row['sales']);
$record[] = sprintf('%.2f', $row['quantity']);
$record[] = $row['rings'];
$record[] = $row['location'] === null ? '' : $row['location'];
$ret[] = $record;
}
return $ret;
}
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;
}
