fannie/item/vendors/BrowseVendorItems.php
<?php
/*******************************************************************************
Copyright 2009 Whole Foods Co-op
This file is part of CORE-POS.
CORE-POS is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
CORE-POS is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
in the file license.txt along with IT CORE; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*********************************************************************************/
include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
include(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}
class BrowseVendorItems extends FanniePage
{
protected $title = "Fannie : Browse Vendor Catalog";
protected $header = "Browse Vendor Catalog";
public $description = '[Vendor Items] lists items in the vendor\'s catalog. Must be
accessed via the Vendor Editor.';
protected $must_authenticate = true;
protected $auth_classes = array('pricechange');
function preprocess(){
$ajax = FormLib::get_form_value('action');
if ($ajax !== ''){
$this->ajax_callbacks($ajax);
return False;
}
return True;
}
function ajax_callbacks($action){
global $FANNIE_OP_DB;
switch($action){
case 'getCategoryBrands':
$this->getCategoryBrands(FormLib::get_form_Value('vid'),FormLib::get_form_value('deptID'));
break;
case 'showCategoryItems':
$ret = array();
$ret['tags'] = $this->guessSuper(
FormLib::get('vid'),
FormLib::get('deptID'),
FormLib::get('brand')
);
$ret['items'] = $this->showCategoryItems(
FormLib::get_form_value('vid'),
FormLib::get_form_value('deptID'),
FormLib::get_form_value('brand'),
$ret['tags']
);
echo json_encode($ret);
break;
case 'addPosItem':
$this->addToPos(
FormLib::get_form_value('upc'),
FormLib::get_form_value('vid'),
FormLib::get_form_value('price'),
FormLib::get_form_value('dept'),
FormLib::get('tags', -1)
);
break;
default:
echo 'bad request';
break;
}
}
private function getCategoryBrands($vid,$did)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$query = "SELECT brand FROM vendorItems AS v
LEFT JOIN vendorDepartments AS d ON
v.vendorDept=d.deptID WHERE v.vendorID=?";
$args = array($vid);
if($did != 'All'){
$query .= ' AND vendorDept=? ';
$args[] = $did;
}
$query .= "GROUP BY brand ORDER BY brand";
$ret = "<option value=\"\">Select a brand...</option>";
$p = $dbc->prepare($query);
$result = $dbc->execute($p,$args);
while($row=$dbc->fetch_row($result))
$ret .= "<option>$row[0]</option>";
echo $ret;
}
private function guessSuper($vid, $did, $brand)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$args = array($vid, $brand);
$guess = 'SELECT s.superID
FROM products AS p
LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID
INNER JOIN vendorItems AS v ON p.upc=v.upc
WHERE v.vendorID=?
AND s.superID IS NOT NULL
AND v.brand=? ';
if ($did != 'All') {
$guess .= ' AND v.vendorDept=? ';
$args[] = $did;
}
$guess .= ' GROUP BY s.superID ORDER BY count(*) DESC';
$prep = $dbc->prepare($guess);
$result = $dbc->execute($prep, $args);
$defaultSuper = -999;
if ($dbc->num_rows($result) > 0) {
$row = $dbc->fetch_row($result);
if ($row['superID'] != '') {
$defaultSuper = $row['superID'];
}
}
if ($defaultSuper == -999 && $did != 'All') {
$guess = 'SELECT s.superID
FROM products AS p
LEFT JOIN MasterSuperDepts AS s ON p.department=s.dept_ID
INNER JOIN vendorItems AS v ON p.upc=v.upc
WHERE v.vendorID=?
AND v.vendorDept=?
GROUP BY s.superID
ORDER BY count(*) DESC';
$prep = $dbc->prepare($guess);
$result = $dbc->execute($prep, array($vid, $did));
if ($dbc->num_rows($result) > 0) {
$row = $dbc->fetch_row($result);
$defaultSuper = $row['superID'];
}
}
return $defaultSuper;
}
private function showCategoryItems($vid,$did,$brand,$ds=-999)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$defaultSuper = $ds;
$depts = "";
$p = $dbc->prepare("SELECT dept_no,dept_name
FROM departments AS d
LEFT JOIN MasterSuperDepts AS s ON d.dept_no=s.dept_ID
ORDER BY
CASE WHEN s.superID=? THEN 0 ELSE 1 END,
dept_no");
$rp = $dbc->execute($p, array($defaultSuper));
while($rw = $dbc->fetch_row($rp))
$depts .= "<option value=$rw[0]>$rw[0] $rw[1]</option>";
$query = "
SELECT v.upc,
v.brand,
v.description,
v.size,
v.cost as cost,
CASE WHEN d.margin IS NULL THEN 0 ELSE d.margin END as margin,
v.srp
FROM vendorItems AS v
LEFT JOIN vendorDepartments AS d ON d.deptID=v.vendorDept AND d.vendorID=v.vendorID
WHERE v.vendorID=?
AND v.brand=? ";
$args = array($vid,$brand);
if ($did != 'All'){
$query .= ' AND vendorDept=? ';
$args[] = $did;
}
$query .= "ORDER BY v.upc";
$posP = $dbc->prepare('
SELECT upc,
normal_price,
department,
d.dept_name
FROM products AS p
LEFT JOIN departments AS d ON p.department=d.dept_no
WHERE upc=?');
$ret = "<table class=\"table table-bordered\">";
$ret .= "<tr><th>UPC</th><th>Brand</th><th>Description</th>";
$ret .= "<th>Size</th><th>Cost</th><th>Price</th>
<th class=\"form-inline\">Dept. <select onchange=\"\$('.dept-select').val(this.value);\"
class=\"form-control small\">{$depts}</select></th>
<th> </th></tr>";
$p = $dbc->prepare($query);
$result = $dbc->execute($p,$args);
while ($row = $dbc->fetch_row($result)) {
$inPOS = $dbc->execute($posP, array($row['upc']));
if ($inPOS && $dbc->numRows($inPOS) > 0) {
$pos = $dbc->fetchRow($inPOS);
$ret .= sprintf("<tr class=\"alert-success\">
<td><a href=\"../ItemEditorPage.php?searchupc=%s\">%s</a></td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>\$%.2f</td>
<td>\$%.2f</td>
<td>%d %s</td>
<td> </td>
</tr>",
$row['upc'], $row['upc'],
$row['brand'],
$row['description'],
$row['size'],
$row['cost'],
$pos['normal_price'],
$pos['department'], $pos['dept_name']
);
} else {
$srp = !empty($row['srp']) ? $row['srp'] : $this->getSRP($row['cost'],$row['margin']);
$ret .= sprintf("<tr id=row%s>
<td><a href=\"../ItemEditorPage.php?searchupc=%s\">%s</a></td>
<td>%s</td><td>%s</td>
<td>%s</td><td>\$%.2f</td>
<td class=\"col-sm-1\">
<div class=\"input-group\">
<span class=\"input-group-addon\">$</span>
<input type=text size=5 value=%.2f id=price%s
class=\"form-control price-field\" />
</div>
</td><td><select id=\"dept%s\" class=\"form-control dept-select\">%s</select></td>
<td id=button%s>
<button type=button value=\"Add to POS\" class=\"btn btn-default\"
onclick=\"addToPos('%s');\">Add to POS</button></td>
</tr>",
$row['upc'],
$row['upc'], $row['upc'],
$row['brand'],$row['description'],
$row['size'],$row['cost'],$srp,$row['upc'],
$row['upc'],$depts,$row['upc'],$row['upc']);
}
}
$ret .= "</table>";
return $ret;
}
private function addToPos($upc,$vid,$price,$dept,$tags=-1)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$p = $dbc->prepare("SELECT i.*,v.vendorName FROM vendorItems AS i
LEFT JOIN vendors AS v ON v.vendorID=i.vendorID
WHERE i.vendorID=? AND upc=?");
$vinfo = $dbc->execute($p, array($vid,$upc));
$vinfo = $dbc->fetch_row($vinfo);
$p = $dbc->prepare("SELECT * FROM departments WHERE dept_no=?");
$dinfo = $dbc->execute($p,array($dept));
$dinfo = $dbc->fetch_row($dinfo);
$model = new ProductsModel($dbc);
$model->upc(BarcodeLib::padUPC($upc));
$model->description(substr($vinfo['description'], 0, 30));
$model->normal_price($price);
$model->department($dept);
$model->tax($dinfo['dept_tax']);
$model->foodstamp($dinfo['dept_fs']);
$model->cost($vinfo['cost']);
$model->default_vendor_id($vid);
$model->brand(substr($vinfo['brand'], 0, 30));
$model->store_id(1);
$model->save();
$xInsQ = $dbc->prepare("INSERT INTO prodExtra (upc,manufacturer,distributor,cost,margin,variable_pricing,location,
case_quantity,case_cost,case_info) VALUES
(?,?,?,?,0.00,0,'','',0.00,'')");
$args = array($upc,$vinfo['brand'],
$vinfo['vendorName'],$vinfo['cost']);
$dbc->execute($xInsQ,$args);
if ($tags !== -1) {
$tag = new ShelftagsModel($dbc);
$tag->id($tags);
$tag->upc($upc);
$info = $model->getTagData();
$tag->normal_price($info['normal_price']);
$tag->description($info['description']);
$tag->brand($info['brand']);
$tag->vendor($info['vendor']);
$tag->sku($info['sku']);
$tag->size($info['size']);
$tag->units($info['units']);
$tag->pricePerUnit($info['pricePerUnit']);
$tag->save();
}
echo "Item added";
}
private function getSRP($cost,$margin)
{
$srp = sprintf("%.2f",$cost/(1-$margin));
while (substr($srp,strlen($srp)-1,strlen($srp)) != "5" &&
substr($srp,strlen($srp)-1,strlen($srp)) != "9")
$srp += 0.01;
return $srp;
}
function body_content()
{
global $FANNIE_OP_DB, $FANNIE_URL;
$vid = FormLib::get_form_value('vid');
if ($vid === ''){
return "<i>Error: no vendor selected</i>";
}
$dbc = FannieDB::get($FANNIE_OP_DB);
$cats = "";
$p = $dbc->prepare("SELECT i.vendorDept, d.name
FROM vendorItems AS i
LEFT JOIN vendorDepartments AS d
ON i.vendorID=d.vendorID AND i.vendorDept=d.deptID
WHERE i.vendorID=?
GROUP BY i.vendorDept, d.name
ORDER BY i.vendorDept");
$rp = $dbc->execute($p,array($vid));
while ($rw = $dbc->fetch_row($rp)) {
if ($rw['vendorDept'] == 0 && empty($rw['name'])) {
continue;
}
$cats .= "<option value=$rw[0]>$rw[0] $rw[1]</option>";
}
if ($cats =="") {
$cats = "<option value=\"\">Select a subcategory...</option><option selected>All</option>";
$this->addOnloadCommand('catchange()');
} else {
$cats = "<option value=\"\">Select a subcategory...</option>".$cats;
}
ob_start();
?>
<div id="categorydiv" class="form-inline">
<select id=categoryselect onchange="catchange();" class="form-control">
<?php echo $cats ?>
</select>
<select id=brandselect onchange="brandchange();" class="form-control">
<option>Select a subcategory first...</option>
</select>
<select id="shelftags" class="form-control">
<option value="-1">Shelf Tag Page</option>
<?php
$queues = new ShelfTagQueuesModel($dbc);
echo $queues->toOptions();
?>
</select>
</div>
<hr />
<div id="contentarea">
<?php if (isset($_REQUEST['did'])){
echo showCategoryItems($vid,$_REQUEST['did']);
}
?>
</div>
<div id="loading-bar" class="col-sm-6 collapse">
<div class="progress">
<div class="progress-bar progress-bar-striped active" style="width:100%;"
role="progressbar" aria-valuenow="100" aria-valuemin="0" aria-valuemax="100">
<span class="sr-only">Loading</span>
</div>
</div>
</div>
<input type="hidden" id="vendorID" value="<?php echo $vid; ?>" />
<input type="hidden" id="urlpath" value="<?php echo $FANNIE_URL; ?>" />
<p><a href="VendorIndexPage.php?vid=<?php echo $vid; ?>" class="btn btn-default">Home</a></p>
<?php
$this->addScript('browse.js?date=20200219');
return ob_get_clean();
}
public function helpContent()
{
return '<p>This tool is used to create POS products from
entries in the vendor\'s catalog of items. Selecting
a subcategory and brand first is necessary to keep the
list of available items a manageable size.
</p>
<p>Green rows are already in POS as products. Other
items can be added by entering an appropriate price
and department. CORE will try to guess correct values
but the default selections should still be verified.
</p>
<p>The third, rightmost dropdown box at the top controls
where new shelf tags are created for the added products.
Again CORE will try to guess the correct set.
</p>';
}
public function unitTest($phpunit)
{
$phpunit->assertNotEquals(0, strlen($this->body_content()));
$phpunit->assertEquals('1.99', $this->getSRP(1.96, 0));
$phpunit->assertNotEquals(0, strlen($this->showCategoryItems(1,1,'test')));
$guess = is_numeric($this->guessSuper(1, 1, 'test'));
ob_start();
$this->getCategoryBrands(1, 1);
$phpunit->assertNotEquals(0, strlen(ob_get_clean()));
}
}
FannieDispatch::conditionalExec();