fannie/item/DepartmentDiscrepancyPage.php
<?php
/*******************************************************************************
Copyright 2013 Whole Foods Co-op, Duluth, MN
This file is part of CORE-POS.
IT CORE 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.
IT CORE 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
*********************************************************************************/
require(dirname(__FILE__) . '/../config.php');
if (!class_exists('FannieAPI')) {
include(__DIR__ . '/../classlib2.0/FannieAPI.php');
}
class DepartmentDiscrepancyPage extends FannieRESTfulPage {
protected $header = 'Discrepancies Within Departments';
protected $title = 'Discrepancies Within Departments';
public $description = '[Department Discrepancies] scan for tax, food-stamp,
and wic discrepancies by departments.';
public $themed = true;
function preprocess()
{
$this->__routes[] = 'get<dept>';
$this->__routes[] = 'post<dept><update>';
return parent::preprocess();
}
public function post_dept_update_view()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$upcs = FormLib::get('upc');
$depts = FormLib::get('deptno');
$taxes = FormLib::get('tax');
$foods = FormLib::get('foodstamp');
$wics = FormLib::get('wic');
$ret = array('error'=>NULL,'error_msg'=>NULL);
$prodP = $dbc->prepare('
UPDATE products
SET department=?,
tax=?,
foodstamp=?,
wicable=?,
modified=' . $dbc->now() . '
WHERE upc=?');
for ($i=0; $i<count($upcs); $i++) {
if ($ret['error'] == 0) {
$args = array(
$depts[$i],
$taxes[$i],
$foods[$i],
$wics[$i],
$upcs[$i],
);
$saved = $dbc->execute($prodP, $args);
if (!$saved) {
$ret['error'] = 1;
$ret['error_msg'] = 'Save failed';
} else {
echo '<span class="text-success">' . $upcs[$i] . ' successfully updated.</span><br>';
}
} else {
echo '<span class="text-danger">Data Did Not Save</span>';
}
}
echo '<a class="btn btn-default" href="DepartmentDiscrepancyPage.php">Back</a><br><br>';
$update = new ProdUpdateModel($dbc);
$update->logManyUpdates($upcs, 'EDIT');
}
public function get_dept_view()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$dept_no = FormLib::get('dept_no');
$dept_name = FormLib::get('dept_name');
$ret = '<h4 align="center">Department #' . $dept_no . ' - ' . $dept_name . '</h4>';
// Check for Department Discrapancies between stores
if ($msg = self::getDeptDiscrepancies($dbc) != "") {
$ret .= '
<div class="panel panel-warning>Department discrepancies discovered between stores.
<br>Please fix discrepancies before updating departments.
<br>' . $msg . '</div>';
}
// Get list of Departments
$query = $dbc->prepare('SELECT dept_no, dept_name FROM departments GROUP BY dept_no, dept_name ORDER BY dept_no;');
$result = $dbc->query($query);
$dept = array();
while ($row = $dbc->fetch_row($result)) {
//$dept_no[] = $row['dept_no'];
//$dept_name[] = $row['dept_name'];
$dept[$row['dept_no']] = $row['dept_name'];
}
// Get Product Info.
if (FormLib::get('order') == 'department') {
$order = 'p.brand';
} elseif (FormLib::get('order') == 'tax') {
$order = 'p.tax';
} elseif (FormLib::get('order') == 'foodstamp') {
$order = 'p.foodstamp';
} elseif (FormLib::get('order') == 'wic') {
$order = 'p.wicable';
}
$args = array($dept_no);
$query = $dbc->prepare('
SELECT
p.upc,
p.description,
p.brand,
u.description AS longDesc,
u.brand AS longBrand,
p.tax,
p.foodstamp,
p.wicable
FROM products AS p
LEFT JOIN productUser AS u ON p.upc=u.upc
WHERE p.department = ?
AND p.store_id = 1
ORDER BY ' . $order . '
');
$result = $dbc->execute($query, $args);
$ret .= '
<table class="table table-condensed small"><form method="post" class="form-inline">
<thead>
<th>UPC</th>
<th>Brand</th>
<th>Description</th>
<th>Department</th>
<th>Tax</th>
<th>Food Stamp</th>
<th>WIC</th>
</thead>
';
while ($row = $dbc->fetch_row($result)) {
$upc[] = $row['upc'];
$ret .= '<tr>';
$ret .= '<td><a href="ItemEditorPage.php?searchupc='
. $row['upc'] . '" target="_blank">' . $row['upc'] . '</a></td>';
if (isset($row['longBrand']) && $row['longBrand'] != '') {
$ret .= '<td>' . $row['longBrand'] . '</td>';
} else {
$ret .= '<td>' . $row['brand'] . '</td>';
}
if (isset($row['longDesc']) && $row['longDesc'] != '') {
$ret .= '<td>' . $row['longDesc'] . '</td>';
} else {
$ret .= '<td>' . $row['description'] . '</td>';
}
$ret .= '<td><select style="height:20px; width:150px" name="deptno[]">';
foreach ($dept as $department_no => $department_name) {
$ret .= '<option value="' . $department_no . '"';
if (($department_no) == $dept_no) {
$ret .= 'selected>' . $department_no . ' - ' . $department_name . '</option>';
} else {
$ret .= '>' . $department_no . ' - ' . $department_name . '</option>';
}
}
$ret .= '</select></td>';
$tax = $row['tax'];
if ($tax == 0) {
$color = 'default';
} elseif ($tax == 1) {
$color = 'warning';
} else {
$color = 'info';
}
$ret .= '<td><select style="height:20px;width:150px" class="alert-' . $color . '" name="tax[]">';
$ret .= '<option value="0"';
if ($tax == 0) {
$ret .= ' selected>No Tax</option>';
} else {
$ret .= '>No Tax</option>';
}
$ret .= '<option value="1"';
if ($tax == 1) {
$ret .= ' selected>Regular</option>';
} else {
$ret .= '>Regular</option>';
}
$ret .= '<option value="2"';
if ($tax == 2) {
$ret .= ' selected>Deli</option>';
} else {
$ret .= '>Deli</option>';
}
$ret .= '</select></td>';
$foodstamp = $row['foodstamp'];
if ($foodstamp == 0) {
$color = 'danger';
} else {
$color = 'success';
}
$ret .= '<td><select style="height:20px;width:150px" class="alert-' . $color . '" name="foodstamp[]">';
$ret .= '<option value="0"';
if ($foodstamp == 0) {
$ret .= ' selected>No - foodstamp</option>';
} else {
$ret .= '>No - foodstamp</option>';
}
$ret .= '<option value="1"';
if ($foodstamp == 1) {
$ret .= '" selected>Yes - foodstamp</option>';
} else {
$ret .= '>Yes - <i>foodstamp</option>';
}
$ret .= '</select></td>';
$wic = $row['wicable'];
if ($wic == 0) {
$color = 'danger';
} else {
$color = 'success';
}
$ret .= '<td><select style="height:20px;width:150px" class="alert-' . $color . '" name="wic[]">';
$ret .= '<option value="0"';
if ($wic == 0) {
$ret .= '" selected>No - wic</option>';
} else {
$ret .= '>No - wic</option>';
}
$ret .= '<option value="1"';
if ($wic == 1) {
$ret .= '" selected>Yes - wic</option>';
} else {
$ret .= '>Yes - wic</option>';
}
$ret .= '</select></td>';
$ret .= '<td><input type="hidden" name="upc[]" value="' . $row['upc'] . '"></td>';
}
$ret .= '<a href="DepartmentDiscrepancyPage.php">Back</a><br><br>';
$ret .= '</tr><tr><td><input type="submit" class="btn btn-default"
value="Update Products"></td>';
$ret .= '</table>
<input type="hidden" name="update" value="1"></td>
<input type="hidden" name="dept" value="' . $dept_no . '">
</form>';
return $ret;
}
public function get_view()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$ret = '';
$query = $dbc->prepare('
SELECT dept_no, dept_name
FROM departments
GROUP BY dept_no, dept_name ORDER BY dept_no;
');
$result = $dbc->query($query);
$ret .= '
<table class="table table-striped">
<thead>
<th style="width:150px;">Department</th>
<th style="width:150px;">Tax (0,R,D)</th>
<th style="width:150px;">Foodstamp (FALSE/TRUE)</th>
<th style="width:150px;">Wic (FALSE/TRUE)</th>
</thead>
';
$dept = array();
while ($row = $dbc->fetch_row($result)) {
$dept[$row['dept_no']] = $row['dept_name'];
}
foreach ($dept as $dept_no => $dept_name) {
$ret .= '<tr>';
$ret .= '<td><a href="DepartmentDiscrepancyPage.php?dept=1&dept_no='
. $dept_no . '&dept_name=' . $dept_name . '&order=department">' . $dept_no . ' ' . $dept_name . '</a></td>';
$queryD = $dbc->prepare('
SELECT
sum(CASE WHEN tax=0 THEN 1 ELSE 0 END) as noTax,
sum(CASE WHEN tax=1 THEN 1 ELSE 0 END) as regTax,
sum(CASE WHEN tax=2 THEN 1 ELSE 0 END) as delTax,
sum(CASE WHEN foodstamp=0 THEN 1 ELSE 0 END) as noFoodstamp,
sum(CASE WHEN foodstamp=1 THEN 1 ELSE 0 END) as yesFoodstamp,
sum(CASE WHEN wicable=0 THEN 1 ELSE 0 END) as noWic,
sum(CASE WHEN wicable=1 THEN 1 ELSE 0 END) as yesWic
FROM products
WHERE inUse = 1
AND store_id = 1
AND department = ?;
');
$resultD = $dbc->execute($queryD, $dept_no);
while ($row = $dbc->fetch_row($resultD)) {
$i = $row['noTax'];
$j = $row['regTax'];
$k = $row['delTax'];
// Find the lower / lowest number
if ($i>0 && $j>0 || $j>0 && $k>0 || $i>0 && $k>0) {
if ($i>0) $min = $i;
if ($j>0 && $j<$min) $min = $j;
if ($k>0 && $k<$min) $min = $k;
if ($min == $i) {
$i = '<span class="redcircle"><b>' . $i . '</b></span>';
} elseif ($min == $j) {
$j = '<span class="redcircle"><b>' . $j . '</b></span>';
} elseif ($min == $k) {
$k = '<span class="redcircle"><b>' . $k . '</b></span>';
}
}
$ret .= '<td><a href="DepartmentDiscrepancyPage.php?dept=1&dept_no='
. $dept_no . '&dept_name=' . $dept_name . '&order=tax">' . $i . ' ' . $j . ' ' . $k . '</a></td>'; //Tax
unset($k);
$i = $row['noFoodstamp'];
$j = $row['yesFoodstamp'];
if ($i>0 && $j>0) {
if ($i>0) $min = $i;
if ($j>0 && $j<$min) $min = $j;
}
if ($min == $i && $j != 0) {
$i = '<span class="redcircle"><b>' . $i . '</b></span>';
} elseif ($min == $j && $i != 0) {
$j = '<span class="redcircle"><b>' . $j . '</b></span>';
}
$ret .= '<td><a href="DepartmentDiscrepancyPage.php?dept=1&dept_no='
. $dept_no . '&dept_name=' . $dept_name . '&order=foodstamp">' . $i . ' ' . $j . '</a></td>'; //Foodstamp
unset($k);
$i = $row['noWic'];
$j = $row['yesWic'];
if ($i>0 && $j>0) {
if ($i>0) $min = $i;
if ($j>0 && $j<$min) $min = $j;
}
if ($min == $i && $j != 0) {
$i = '<span class="redcircle"><b>' . $i . '</b></span>';
} elseif ($min == $j && $i != 0) {
$j = '<span class="redcircle"><b>' . $j . '</b></span>';
}
$ret .= '<td><a href="DepartmentDiscrepancyPage.php?dept=1&dept_no='
. $dept_no . '&dept_name=' . $dept_name . '&order=wic">' . $i . ' ' . $j . '</a></td>'; //Wic
}
}
$ret .= '</table>';
foreach ($dept as $no => $name) {
}
return $ret;
}
private function getDeptDiscrepancies($dbc)
{
$itemA = array();
$itemB = array();
$queryA = $dbc->prepare('
SELECT upc, department
FROM products
WHERE store_id=1
AND department NOT BETWEEN 508 AND 998
AND department NOT BETWEEN 250 AND 259
AND department NOT BETWEEN 225 AND 234
AND department NOT BETWEEN 1 AND 25
AND department NOT BETWEEN 61 AND 78
AND department != 46
AND department != 150
AND department != 208
AND department != 235
AND department != 240
AND department != 500
');
$resultA = $dbc->execute($queryA);
while ($row = $dbc->fetch_row($resultA)) {
$itemA[$row[0]] = $row[1];
}
$queryB = $dbc->prepare('
SELECT upc, department
FROM products
WHERE store_id=2
AND department NOT BETWEEN 508 AND 998
AND department NOT BETWEEN 250 AND 259
AND department NOT BETWEEN 225 AND 234
AND department NOT BETWEEN 1 AND 25
AND department NOT BETWEEN 61 AND 78
AND department != 46
AND department != 150
AND department != 208
AND department != 235
AND department != 240
AND department != 500
');
$resultb = $dbc->execute($queryB);
while ($row = $dbc->fetch_row($resultB)) {
$itemB[$row[0]] = $row[1];
}
$count = 0;
foreach ($itemA as $upc => $department) {
if (isset($itemB[$upc])) {
if ($department != $itemB[$upc]) $count++;
}
}
$msg = "";
if ($count > 0 ) {
$msg = $count . " department discrepancies were discovered<br>";
foreach ($itemA as $upc => $department) {
$link = "<a href=ItemEditorPage.php?searchupc=" . $upc . " ";
if ($department != $itemB[$upc]) {
$msg .= $link . $department . " " . $itemB[$upc] . "</a><br>";
}
}
}
return $msg;
}
public function css_content()
{
return '
a {}
a:hover {
text-decoration: none;
color: lightblue;
}
/*
span.redcircle {
border: 1px dotted tomato;
border-radius: 100%;
padding: 3px;
}
*/
span.redcircle {
background-color: tomato;
color: white;
}
';
}
public function helpContent()
{
return '<p>
This page scans for the tax, foodstamp, and wic status of
all products that are currently in use. Click on a link to
view & update product information.
<ul>
<li>Click on a <b>department</b> name to view a list of products sorted by brand. </li>
<li>Click the <b>tax statistics</b> to view a list sorted by tax status. </li>
<li>Click <b>foodstamp</b> to view a list sorted by food-stamp-ability. </li>
<li>Click <b>WIC</b> to view a list sorted by WIC-ability. </li>
</ul>
</p>';
}
public function unitTest($phpunit)
{
$phpunit->assertInternalType('string', $this->css_content());
$phpunit->assertInternalType('string', $this->get_view());
}
}
FannieDispatch::conditionalExec();