fannie/item/likecodes/LikeCodePriceUploadPage.php
<?php
/*******************************************************************************
Copyright 2014 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
*********************************************************************************/
include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
include(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}
class LikeCodePriceUploadPage extends \COREPOS\Fannie\API\FannieUploadPage
{
protected $title = "Fannie :: Upload Likecode Prices";
protected $header = "Upload Likecode Prices";
public $description = '[Like Code Prices] uploads a spreadsheet of like codes and prices
and immediately updates the prices for those like coded items.';
protected $preview_opts = array(
'likecode' => array(
'display_name' => 'Like Code #',
'default' => 0,
'required' => true
),
'description' => array(
'display_name' => 'Description',
'default' => 4,
),
'price' => array(
'display_name' => 'Price',
'default' => 1,
'required' => true
),
'cost' => array(
'display_name' => 'Cost (Unit)',
'default' => 2,
),
'scale' => array(
'display_name' => 'LB / EA',
'default' => -1,
),
'department' => array(
'display_name' => 'Department',
'default' => -1,
),
'wicable' => array(
'display_name' => 'WIC',
'default' => -1,
),
'numflag' => array(
'display_name' => 'Organic',
'default' => -1,
),
'local' => array(
'display_name' => 'Local',
'default' => -1,
),
'vendor' => array(
'display_name' => 'Preferred Vendor',
'default' => 3,
),
);
public function process_file($linedata, $indexes)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$ret = true;
$getUPCs = $dbc->prepare("SELECT upc FROM upcLike WHERE likeCode=?");
$getItem = $dbc->prepare('SELECT description, normal_price, cost, department, numflag, scale, wicable, local FROM products WHERE upc=?');
$getAttr = $dbc->prepare('SELECT attributes FROM ProductAttributes WHERE upc=? ORDER BY modified DESC');
$setAttr = $dbc->prepare('INSERT INTO ProductAttributes (upc, modified, attributes) VALUES (?, ?, ?)');
$setVend = $dbc->prepare('UPDATE likeCodes SET preferredVendorID=? WHERE likeCode=?');
$setDesc = $dbc->prepare('UPDATE likeCodes SET likeCodeDesc=? WHERE likeCode=?');
// build update query based on selected columns
$update = "UPDATE products SET normal_price=?";
$updateCols = array();
$numflag = false;
if ($indexes['cost'] !== false) {
$update .= ', cost=?';
$updateCols[] = 'cost';
}
if ($indexes['department'] !== false) {
$update .= ', department=?';
$updateCols[] = 'department';
}
if ($indexes['scale'] !== false) {
$update .= ', scale=?';
$updateCols[] = 'scale';
}
if ($indexes['wicable'] !== false) {
$update .= ', wicable=?';
$updateCols[] = 'wic';
}
if ($indexes['local'] !== false) {
$update .= ', local=?';
$updateCols[] = 'local';
}
if ($indexes['numflag'] !== false) {
$update .= ', numflag=?';
$numflag = true;
}
$update .= ' WHERE upc=?';
$this->stats = array('done' => 0, 'error' => array());
$upcs = array();
$sets = array();
$dbc->startTransaction();
foreach ($linedata as $line) {
$likecode = trim($line[$indexes['likecode']]);
$price = trim($line[$indexes['price']], ' $');
if (!is_numeric($likecode)) continue; // skip header(s) or blank rows
$upcR = $dbc->execute($getUPCs, array($likecode));
// compare each item in the likecode to
// the uploaded values to flag which UPCs
// are actually changing
while ($upcW = $dbc->fetchRow($upcR)) {
$changed = false;
$args = array($price);
$item = $dbc->getRow($getItem, array($upcW['upc']));
foreach ($updateCols as $col) {
$normalize = trim(strtoupper($line[$indexes[$col]]));
if ($normalize === 'Y' || $normalize === 'YES' || $normalize === 'X') {
$line[$indexes[$col]] = 1;
} elseif ($normalize === 'N' || $normalize === 'NO' || $normalize === '') {
$line[$indexes[$col]] = 0;
}
$args[] = $line[$indexes[$col]];
if ($line[$indexes[$col]] != $item[$col]) {
$changed = true;
}
}
// numflag needs special handling since we're only
// dealing with organic. This sets or unsets the
// appropriate bit in numflag then if necessary
// adds a new record to ProductAttributes
if ($numflag) {
$current = $item['numflag'];
$flag = (1 << 16);
if ($line[$indexes['numflag']]) {
$new = $current | (1 << 16);
} else {
$new = $current & (~$flag);
}
$args[] = $new;
if ($current != $new) {
$changed = true;
$attr = $dbc->getRow($getAttr, array($upcW['upc']));
$newAttrs = json_decode($attr['attributes'], true);
$newAttrs['Organic'] = ($line[$indexes['numflag']]) ? true : false;
$dbc->execute($setAttr, array($upcW['upc'], date('Y-m-d H:i:s'), json_encode($newAttrs)));
}
}
$args[] = $upcW['upc'];
$dbc->execute($update, $args);
if ($changed) {
$upcs[] = $upcW['upc'];
$sets[] = array($price, $upcW['upc']);
}
}
if ($indexes['vendor'] !== false) {
$abbr = strtoupper(trim($line[$indexes['vendor']]));
$vID = -1;
if ($abbr == 'ALBERTS') {
$vID = 28;
} elseif ($abbr == 'CPW') {
$vID = 25;
} elseif ($abbr == 'RDW') {
$vID = 136;
} elseif ($abbr == 'UNFI') {
$vID = 1;
}
$dbc->execute($setVend, array($vID, $likecode));
}
if ($indexes['description'] !== false) {
$desc = trim($line[$indexes['description']]);
if ($desc != '') {
$dbc->execute($setDesc, array($desc, $likecode));
}
}
$this->stats['done']++;
}
// log the updates
$model = new ProdUpdateModel($dbc);
$model->logManyUpdates($upcs, 'EDIT');
$dbc->commitTransaction();
// push updates to local lanes
$FANNIE_LANES = FannieConfig::config('LANES');
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$lane_sql = new SQLManager($FANNIE_LANES[$i]['host'],$FANNIE_LANES[$i]['type'],
$FANNIE_LANES[$i]['op'],$FANNIE_LANES[$i]['user'],
$FANNIE_LANES[$i]['pw']);
if (!isset($lane_sql->connections[$FANNIE_LANES[$i]['op']]) || $lane_sql->connections[$FANNIE_LANES[$i]['op']] === false) {
// connect failed
continue;
}
$upP = $lane_sql->prepare('UPDATE products SET normal_price=? WHERE upc=?');
foreach ($sets as $set) {
$upR = $lane_sql->execute($upP, $set);
}
}
// push updates to other stores
if (FannieConfig::config('STORE_MODE') === 'HQ' && class_exists('\\Datto\\JsonRpc\\Http\\Client')) {
$prep = $this->connection->prepare('
SELECT webServiceUrl FROM Stores WHERE hasOwnItems=1 AND storeID<>?
');
$res = $this->connection->execute($prep, array(\FannieConfig::config('STORE_ID')));
while ($row = $this->connection->fetchRow($res)) {
$client = new \Datto\JsonRpc\Http\Client($row['webServiceUrl']);
$client->query(time(), 'COREPOS\\Fannie\\API\\webservices\\FannieItemLaneSync', array('upc'=>$upcs, 'fast'=>true));
$client->send();
}
}
return $ret;
}
function form_content()
{
return '<div class="well"><legend>Instructions</legend>
Upload a CSV or XLS file containing likecode #s and prices. Cost
may also optionally be included.
<br />A preview helps you to choose and map columns to the database.
<br />The uploaded file will be deleted after the load.
</div><br />';
}
function results_content()
{
\COREPOS\Fannie\API\data\SyncLanes::pushTable('products');
$ret = '<p>Import Complete</p>';
$ret .= '<div class="alert alert-success">Updated ' . $this->stats['done'] . ' likecodes</div>';
if (count($this->stats['error']) > 0) {
$ret .= '<div class="alert alert-danger"><ul>';
foreach ($this->stats['error'] as $error) {
$ret .= '<li>' . $error . '</li>';
}
$ret .= '</ul></div>';
}
return $ret;
}
public function unitTest($phpunit)
{
$data = array(1, 1.99, 0.99, 'foo', 1, 1, 0, 1, 0);
$indexes = array('likecode'=>0, 'price'=>1, 'cost'=>2, 'description'=>3,
'scale'=>4, 'department'=>5, 'wicable'=>6, 'numflag'=>7, 'local'=>8);
$this->process_file(array($data), $indexes);
}
}
FannieDispatch::conditionalExec();