fannie/purchasing/InvoiceCostCompare.php
<?php
/*******************************************************************************
Copyright 2015 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_once(__DIR__ . '/../classlib2.0/FannieAPI.php');
}
class InvoiceCostCompare extends FannieRESTfulPage
{
protected $header = 'Compare Costs';
protected $title = 'Compare Costs';
public $description = '[Invoice Cost Comparison] reconciles invoice costs against
vendor catalog costs.';
public $themed = true;
public function preprocess()
{
$this->__routes[] = 'get<date1><date2>';
$this->__routes[] = 'get<vendors><date1><date2>';
return parent::preprocess();
}
public function post_view()
{
$dbc = $this->connection;
$dbc->setDefaultDB($this->config->get('OP_DB'));
$vendorItem = new VendorItemsModel($dbc);
$product = new ProductsModel($dbc);
$updates = FormLib::get('update');
$ret = '';
foreach ($updates as $update) {
$update = json_decode($update, true);
$vendorItem->reset();
$vendorItem->sku($update['sku']);
$vendorItem->vendorID($update['vendorID']);
if (!$vendorItem->load()) {
$ret .= '<div class="alert alert-danger">' . $update['sku'] . ' missing from catalog</div>';
continue;
}
$currentCost = $vendorItem->cost();
$vendorItem->cost($update['cost']);
$ret .= '<div class="alert alert-info">Changing ' . $update['sku'] . ' from ' . $currentCost . ' to ' . $update['cost'] . '</div>';
$vendorItem->save();
$product->reset();
$product->upc($vendorItem->upc());
if ($product->load() && $product->default_vendor_id() == $vendorItem->vendorID()) {
$ret .= '<div class="alert alert-info">Updating product cost as well</div>';
$product->cost($vendorItem->cost());
$product->save();
}
}
return $ret;
}
public function get_vendors_date1_date2_view()
{
$dbc = $this->connection;
$dbc->setDefaultDB($this->config->get('OP_DB'));
$catalogP = $dbc->prepare('
SELECT cost
FROM vendorItems
WHERE vendorID=?
AND sku=?');
$invoiceQ = '
SELECT o.vendorID,
sku,
MAX(i.description) AS description,
MAX(v.vendorName) as vendorName,
AVG(unitCost) AS cost
FROM PurchaseOrder AS o
INNER JOIN PurchaseOrderItems AS i ON o.orderID=i.orderID
INNER JOIN vendors AS v ON o.vendorID=v.vendorID
WHERE o.placedDate BETWEEN ? AND ?
AND o.vendorID IN (';
$args = array($this->date1, $this->date2);
$ids = '';
foreach ($this->vendors as $v) {
$args[] = $v;
$ids .= '?,';
}
$ids = substr($ids, 0, strlen($ids)-1);
$invoiceQ .= $ids . ')
GROUP BY o.vendorID,
i.sku';
$invoiceP = $dbc->prepare($invoiceQ);
$invoiceR = $dbc->execute($invoiceP, $args);
$ret = '<form method="post">
<table class="table table-bordered">
<thead>
<tr>
<th>Vendor</th>
<th>SKU</th>
<th>Item</th>
<th>Invoice Cost</th>
<th>Catalog Cost</th>
<th>Update Catalog</th>
</tr>
</thead>
<tbody>';
while ($w = $dbc->fetchRow($invoiceR)) {
$tr = sprintf('<tr __class__>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%.2f</td>',
$w['vendorName'],
$w['sku'],
$w['description'],
$w['cost']);
$catalogCost = 0;
$catalogR = $dbc->execute($catalogP, array($w['vendorID'], $w['sku']));
if ($catalogR && $dbc->numRows($catalogR)) {
$catalogW = $dbc->fetchRow($catalogR);
$catalogCost = $catalogW['cost'];
}
if ($w['cost'] == $catalogCost) {
// nothing to update
continue;
} elseif ($w['cost'] == 0) {
// invoice missing cost info
continue;
}
$updateJSON = json_encode(array(
'vendorID' => $w['vendorID'],
'sku' => $w['sku'],
'cost' => $w['cost'],
));
$checked = $catalogCost == 0 ? true : false;
$tr .= sprintf('<td>%.2f</td>
<td><input type="checkbox" name="update[]" value=\'%s\' %s /></td>
</tr>',
$catalogCost,
$updateJSON,
($checked ? 'checked' : '')
);
if ($catalogCost == 0) {
$tr = str_replace('__class__', 'class="success"', $tr);
} elseif ($w['cost'] > $catalogCost) {
$tr = str_replace('__class__', 'class="info"', $tr);
} else {
$tr = str_replace('__class__', 'class="warning"', $tr);
}
$ret .= $tr;
}
$ret .= '</tbody></table>
<p>
<button type="submit" class="btn btn-default">Update Selected</button>
</p>
</form>';
return $ret;
}
public function get_date1_date2_view()
{
$dbc = $this->connection;
$dbc->setDefaultDB($this->config->get('OP_DB'));
$prep = $dbc->prepare("
SELECT p.vendorID,
v.vendorName
FROM PurchaseOrder AS p
INNER JOIN vendors AS v ON p.vendorID=v.vendorID
WHERE p.placedDate BETWEEN ? AND ?
GROUP BY p.vendorID,
v.vendorName");
$vendors = $dbc->execute($prep, array($this->date1, $this->date2));
$ret = '<form method="get">
<input type="hidden" name="date1" value="' . $this->date1 . '" />
<input type="hidden" name="date2" value="' . $this->date2 . '" />
<select name="vendors[]" multiple size="15" class="form-control">';
while ($w = $dbc->fetchRow($vendors)) {
$ret .= sprintf('<option value="%d">%s</option>', $w['vendorID'], $w['vendorName']);
}
$ret .= '</select>
<p>
<button type="submit" class="btn btn-default">Submit</button>
</p>
</form>';
return $ret;
}
public function get_view()
{
return '<form method="get">
<div class="form-group">
<label>Start Date</label>
<input type="text" name="date1" class="form-control date-field" />
</div>
<div class="form-group">
<label>End Date</label>
<input type="text" name="date2" class="form-control date-field" />
</div>
<div class="form-group">
<button type="submit" class="btn btn-default">Submit</button>
</div>
</form>';
}
}
FannieDispatch::conditionalExec();