$update->upc($upc);
$update->logUpdate(ProdUpdateModel::UPDATE_BATCH);
} else {
$lc = substr($upc, 2);
$unsaleQ = $sql->prepare("UPDATE products AS p LEFT JOIN upcLike as u on p.upc=u.upc\n LEFT JOIN batchList as b ON b.upc=concat('LC',convert(u.likeCode,char))\n set p.discounttype=0,special_price=0,start_date=0,end_date=0 \n WHERE u.likeCode=? and b.batchID=?");
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$unsaleQ = $sql->prepare("update products set discounttype=0,special_price=0,start_date=0,end_date=0\n from products as p, batches as b, upcLike as u\n where u.likeCode=? and u.upc=p.upc and b.startdate=p.start_date and b.enddate=p.end_date\n and b.batchID=?");
}
$unsaleR = $sql->execute($unsaleQ, array($lc, $id));
$prep = $sql->prepare('SELECT upc FROM upcLike WHERE likeCode=?');
$all = $sql->execute($prep, array($lc));
while ($row = $sql->fetch_row($all)) {
$model = new ProductsModel($sql);
$model->upc($row['upc']);
$model->pushToLanes();
$update = new ProdUpdateModel($sql);
$update->upc($upc);
$update->logUpdate(ProdUpdateModel::UPDATE_BATCH);
}
}
$delQ = $sql->prepare("delete from batchList where batchID=? and upc=?");
$delR = $sql->execute($delQ, array($id, $upc));
$delQ = $sql->prepare("delete from batchBarcodes where upc=? and batchID=?");
$delR = $sql->execute($delQ, array($upc, $id));
$audited = $_GET['audited'];
if ($audited == "1") {
\COREPOS\Fannie\API\lib\AuditLib::batchNotification($id, $upc, \COREPOS\Fannie\API\lib\AuditLib::BATCH_DELETE, substr($upc, 0, 2) == 'LC' ? true : false);
}
$out .= showBatchDisplay($id);
break;
case 'refilter':
UPDATE products AS p SET
p.normal_price = ?,
p.special_price = ?,
p.modified = ?,
p.specialpricemethod = ?,
p.specialquantity = ?,
p.specialgroupprice = ?,
p.discounttype = ?,
p.mixmatchcode = ?,
p.start_date = ?,
p.end_date = ?
WHERE p.upc = ?';
/**
Update all records on each lane before proceeding
to the next lane. Hopefully faster / more efficient
*/
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;
}
$updateP = $lane_sql->prepare($updateQ);
foreach ($upcs as $upc => $data) {
$lane_sql->execute($updateP, array($data['normal_price'], $data['special_price'], $data['modified'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['start_date'], $data['end_date'], $upc));
}
}
$update = new ProdUpdateModel($sql);
$updateType = $batchInfoW['discountType'] == 0 ? ProdUpdateModel::UPDATE_PC_BATCH : ProdUpdateModel::UPDATE_BATCH;
$update->logManyUpdates(array_keys($upcs), $updateType);
echo "Batch {$batchID} has been forced";
}
}
if (!empty($likeCode)) {
if ($likeCode == -1) {
$updateLikeQ = $sql->prepare("delete from upcLike where upc=?");
$updateLikeR = $sql->execute($updateLikeQ, array($upc));
} else {
if (!isset($update)) {
//Update all like coded items to $upc
$likeQuery = $sql->prepare("UPDATE products SET normal_price = ?,department = ?,tax = ?,scale=?,foodstamp=?,inUse=?, modified = ?,\n pricemethod=?,groupprice=?,quantity=?,local=?\n WHERE upc IN (SELECT u.upc FROM upcLike AS u WHERE u.likeCode = ?)");
$likeResult = $sql->execute($likeQuery, array($price, $dept, $tax, $Scale, $FS, $inUse, $modDate, $price_method, $vol_price, $vol_qtty, $local, $likeCode));
//INSERTED HERE TO INSERT UPDATE INTO prodUpdate for likecoded items.
$selectQ = $sql->prepare("SELECT * FROM upcLike WHERE likecode = ?");
//echo $selectQ;
$selectR = $sql->execute($selectQ, array($likeCode));
$prodUpdate = new ProdUpdateModel($sql);
while ($selectW = $sql->fetch_array($selectR)) {
$upcL = $selectW['upc'];
if ($upcL != $upc) {
$prodUpdate->reset();
$prodUpdate->upc($upcL);
$prodUpdate->logUpdate(ProdUpdateModel::UPDATE_EDIT);
$p_model = new ProductsModel($sql);
$p_model->upc($upcL);
$p_model->pushToLanes();
}
}
$delQ = $sql->prepare("DELETE FROM upcLike WHERE upc = ?");
$delR = $sql->execute($delQ, array($upc));
$updateLikeQ = $sql->prepare("INSERT INTO upcLike VALUES(?,?)");
$updateLikeR = $sql->execute($updateLikeQ, array($upc, $likeCode));
$success = false;
}
}
if ($success) {
echo cron_msg("Price change batches run successfully");
} else {
echo cron_msg("Error running price change batches");
}
// log updates to prodUpdate table
$success = true;
$likeP = $sql->prepare('SELECT upc FROM upcLike WHERE likeCode=?');
$batchQ = 'SELECT upc FROM batchList as l LEFT JOIN batches AS b
ON l.batchID=b.batchID WHERE b.discounttype=0
AND ' . $sql->datediff($sql->now(), 'b.startDate') . ' = 0';
$batchR = $sql->query($batchQ);
$prodUpdate = new ProdUpdateModel($sql);
while ($batchW = $sql->fetch_row($batchR)) {
$upcs = array();
$upc = $batchW['upc'];
// unpack likecodes to UPCs
if (substr($upc, 0, 2) == 'LC') {
$likeR = $sql->execute($likeP, array(substr($upc, 2)));
while ($likeW = $sql->fetch_row($likeR)) {
$upcs[] = $likeW['upc'];
}
} else {
$upcs[] = $upc;
}
foreach ($upcs as $item) {
$prodUpdate->reset();
$prodUpdate->upc($item);
/**
Log deletes to prodUpdate
Delete corresponding records from other tables
*/
public function delete()
{
$update = new ProdUpdateModel($this->connection);
$update->upc($this->upc());
$update->logUpdate(ProdUpdateModel::UPDATE_DELETE);
$try = parent::delete();
if ($try) {
if ($this->connection->tableExists('prodExtra')) {
$extra = new ProdExtraModel($this->connection);
$extra->upc($this->upc());
$extra->delete();
}
$user = new ProductUserModel($this->connection);
$user->upc($this->upc());
$user->delete();
}
return $try;
}
function process_file($linedata)
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$idP = $dbc->prepare("\n SELECT vendorID \n FROM vendors \n WHERE vendorName='GARDEN OF LIFE' \n ORDER BY vendorID");
$idR = $dbc->execute($idP);
if ($dbc->num_rows($idR) == 0) {
$this->error_details = 'Cannot find vendor';
return false;
}
$idW = $dbc->fetchRow($idR);
$VENDOR_ID = $idW['vendorID'];
$clean = $dbc->prepare('
DELETE
FROM vendorItems
WHERE
vendorID=?
AND upc NOT IN (
SELECT upc
FROM VendorBreakdowns
WHERE vendorID=?
)');
$dbc->execute($clean, array($VENDOR_ID, $VENDOR_ID));
$SKU = $this->get_column_index('sku');
$BRAND = $this->get_column_index('brand');
$DESCRIPTION = $this->get_column_index('desc');
$UPC = $this->get_column_index('upc');
$REG_COST = $this->get_column_index('cost');
$SIZE = $this->get_column_index('size');
$TYPE = $this->get_column_index('type');
$extraP = $dbc->prepare_statement("update prodExtra set cost=? where upc=?");
$prodP = $dbc->prepare('
UPDATE products
SET cost=?,
modified=' . $dbc->now() . '
WHERE upc=?
AND default_vendor_id=?');
$itemP = $dbc->prepare("\n INSERT INTO vendorItems (\n brand, \n sku,\n size,\n upc,\n units,\n cost,\n description,\n vendorDept,\n vendorID,\n saleCost,\n modified,\n srp\n ) VALUES (\n 'GARDEN OF LIFE',\n ?,\n ?,\n ?,\n ?,\n ?,\n ?,\n 0,\n ?,\n 0,\n ?,\n 0\n )");
$updated_upcs = array();
$CARTON_PATTERN = '/\\s*\\((\\d+) .+ per carton\\)\\s*/i';
$TRAY_PATTERN = '/\\s*\\((\\d+) .+\\)\\s*/';
foreach ($linedata as $data) {
if (!is_array($data)) {
continue;
}
if (!isset($data[$UPC])) {
continue;
}
// grab data from appropriate columns
$sku = $SKU !== false ? $data[$SKU] : '';
$description = $data[$DESCRIPTION];
$upc = str_replace(' ', '', $data[$UPC]);
$upc = substr($upc, 0, strlen($upc) - 1);
$upc = BarcodeLib::padUPC($upc);
$size = $SIZE !== false ? $data[$SIZE] : '';
if (is_numeric($size)) {
$size .= 'CT';
}
$type = strtolower($data[$TYPE]);
$qty = 1;
// zeroes isn't a real item, skip it
$reg = trim($data[$REG_COST]);
// blank spreadsheet cell
// can't process items w/o price (usually promos/samples anyway)
if (empty($reg)) {
continue;
}
// syntax fixes. kill apostrophes in text fields,
// trim $ off amounts as well as commas for the
// occasional > $1,000 item
$description = str_replace("'", "", $description);
$reg = str_replace('$', "", $reg);
$reg = str_replace(",", "", $reg);
$reg = trim($reg);
// skip the item if prices aren't numeric
// this will catch the 'label' line in the first CSV split
// since the splits get returned in file system order,
// we can't be certain *when* that chunk will come up
if (!is_numeric($reg)) {
continue;
}
$description = preg_replace("/[^-]/", "", $description);
if ($type == 'tray') {
if (preg_match($TRAY_PATTERN, $description, $matches)) {
$size = $matches[1] . 'CT';
$description = preg_replace($TRAY_PATTERN, '', $description);
} elseif (preg_match('/(\\d+)/', $description, $matches)) {
$size = $matches[1] . 'CT';
}
} elseif ($type == 'carton') {
if (preg_match($CARTON_PATTERN, $description, $matches)) {
$size = $matches[1] . 'CT';
$description = preg_replace($CARTON_PATTERN, '', $description);
} elseif (preg_match('/(\\d+)/', $description, $matches)) {
$size = $matches[1] . 'CT';
}
}
// need unit cost, not case cost
$reg_unit = $reg / $qty;
$dbc->execute($extraP, array($reg_unit, $upc));
$dbc->execute($prodP, array($reg_unit, $upc, $VENDOR_ID));
$updated_upcs[] = $upc;
$args = array($sku === false ? '' : $sku, $size === false ? '' : $size, $upc, $qty, $reg_unit, $description, $VENDOR_ID, date('Y-m-d H:i:s'));
$dbc->execute($itemP, $args);
}
$updateModel = new ProdUpdateModel($dbc);
$updateModel->logManyUpdates($updated_upcs, ProdUpdateModel::UPDATE_EDIT);
return true;
}
function forceBatch($batchID)
{
global $sql, $FANNIE_SERVER_DBMS, $FANNIE_LANES;
$batchInfoQ = $sql->prepare("SELECT batchType,discountType FROM batches WHERE batchID = ?");
$batchInfoR = $sql->execute($batchInfoQ, array($batchID));
$batchInfoW = $sql->fetch_array($batchInfoR);
$forceQ = "";
$forceLCQ = "";
$forceMMQ = "";
if ($batchInfoW['discountType'] != 0) {
$forceQ = "UPDATE products AS p\n INNER JOIN batchList AS l\n ON p.upc=l.upc\n INNER JOIN batches AS b\n ON l.batchID=b.batchID\n SET p.start_date = b.startDate, \n p.end_date=b.endDate,\n p.special_price=l.salePrice,\n p.specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n p.specialpricemethod=l.pricemethod,\n p.specialquantity=l.quantity,\n p.discounttype=b.discounttype,\n p.mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(l.batchID,char)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(-1*l.batchID,char)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN concat('b',convert(l.batchID,char))\n ELSE p.mixmatchcode \n END \n WHERE l.upc not like 'LC%'\n and l.batchID = ?";
$forceLCQ = "UPDATE products AS p\n INNER JOIN upcLike AS v \n ON v.upc=p.upc\n INNER JOIN batchList as l \n ON l.upc=concat('LC',convert(v.likecode,char))\n INNER JOIN batches AS b \n ON b.batchID=l.batchID\n set p.special_price = l.salePrice,\n p.end_date = b.endDate,p.start_date=b.startDate,\n p.specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n p.specialpricemethod=l.pricemethod,\n p.specialquantity=l.quantity,\n p.discounttype = b.discounttype,\n p.mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(l.batchID,char)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(-1*l.batchID,char)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN concat('b',convert(l.batchID,char))\n ELSE p.mixmatchcode \n END \n where l.batchID=?";
if ($FANNIE_SERVER_DBMS == 'MSSQL') {
$forceQ = "UPDATE products\n SET start_date = b.startDate, \n end_date=b.endDate,\n special_price=l.salePrice,\n specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n specialpricemethod=l.pricemethod,\n specialquantity=l.quantity,\n discounttype=b.discounttype,\n mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(varchar,l.batchID)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(varchar,-1*l.batchID)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN 'b'+convert(varchar,l.batchID)\n ELSE p.mixmatchcode \n END \n FROM products as p, \n batches as b, \n batchList as l \n WHERE l.upc = p.upc\n and l.upc not like 'LC%'\n and b.batchID = l.batchID\n and b.batchID = ?";
$forceLCQ = "update products set special_price = l.salePrice,\n end_date = b.endDate,start_date=b.startDate,\n discounttype = b.discounttype,\n specialpricemethod=l.pricemethod,\n specialquantity=l.quantity,\n specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(varchar,l.batchID)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(varchar,-1*l.batchID)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN 'b'+convert(varchar,l.batchID)\n ELSE p.mixmatchcode \n END \n from products as p left join\n upcLike as v on v.upc=p.upc left join\n batchList as l on l.upc='LC'+convert(varchar,v.likecode)\n left join batches as b on b.batchID = l.batchID\n where b.batchID=?";
}
} else {
$forceQ = "UPDATE products AS p\n INNER JOIN batchList AS l\n ON l.upc=p.upc\n SET p.normal_price = l.salePrice,\n p.modified = now()\n WHERE l.upc not like 'LC%'\n AND l.batchID = ?";
$forceLCQ = "UPDATE products AS p\n INNER JOIN upcLike AS v\n ON v.upc=p.upc INNER JOIN\n batchList as b on b.upc=concat('LC',convert(v.likecode,char))\n set p.normal_price = b.salePrice,\n p.modified=now()\n where b.batchID=?";
if ($FANNIE_SERVER_DBMS == 'MSSQL') {
$forceQ = "UPDATE products\n SET normal_price = l.salePrice,\n modified = getdate()\n FROM products as p,\n batches as b,\n batchList as l\n WHERE l.upc = p.upc\n AND l.upc not like 'LC%'\n AND b.batchID = l.batchID\n AND b.batchID = ?";
$forceLCQ = "update products set normal_price = b.salePrice,\n modified=getdate()\n from products as p left join\n upcLike as v on v.upc=p.upc left join\n batchList as b on b.upc='LC'+convert(varchar,v.likecode)\n where b.batchID=?";
}
}
$forceP = $sql->prepare($forceQ);
$forceR = $sql->execute($forceP, array($batchID));
$forceLCP = $sql->prepare($forceLCQ);
$forceLCR = $sql->execute($forceLCP, array($batchID));
$columnsP = $sql->prepare('
SELECT p.upc,
p.normal_price,
p.special_price,
p.modified,
p.specialpricemethod,
p.specialquantity,
p.specialgroupprice,
p.discounttype,
p.mixmatchcode,
p.start_date,
p.end_date
FROM products AS p
INNER JOIN batchList AS b ON p.upc=b.upc
WHERE b.batchID=?');
$lcColumnsP = $sql->prepare('
SELECT p.upc,
p.normal_price,
p.special_price,
p.modified,
p.specialpricemethod,
p.specialquantity,
p.specialgroupprice,
p.discounttype,
p.mixmatchcode,
p.start_date,
p.end_date
FROM products AS p
INNER JOIN upcLike AS u ON p.upc=u.upc
INNER JOIN batchList AS b
ON b.upc = ' . $sql->concat("'LC'", $sql->convert('u.likeCode', 'CHAR'), '') . '
WHERE b.batchID=?');
/**
Get changed columns for each product record
*/
$upcs = array();
$columnsR = $sql->execute($columnsP, array($batchID));
while ($w = $sql->fetch_row($columnsR)) {
$upcs[$w['upc']] = $w;
}
$columnsR = $sql->execute($lcColumnsP, array($batchID));
while ($w = $sql->fetch_row($columnsR)) {
$upcs[$w['upc']] = $w;
}
$updateQ = '
UPDATE products AS p SET
p.normal_price = ?,
p.special_price = ?,
p.modified = ?,
p.specialpricemethod = ?,
p.specialquantity = ?,
p.specialgroupprice = ?,
p.discounttype = ?,
p.mixmatchcode = ?,
p.start_date = ?,
p.end_date = ?
WHERE p.upc = ?';
/**
Update all records on each lane before proceeding
to the next lane. Hopefully faster / more efficient
*/
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;
}
$updateP = $lane_sql->prepare($updateQ);
foreach ($upcs as $upc => $data) {
$lane_sql->execute($updateP, array($data['normal_price'], $data['special_price'], $data['modified'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['start_date'], $data['end_date'], $upc));
}
}
$update = new ProdUpdateModel($sql);
$updateType = $batchInfoW['discountType'] == 0 ? ProdUpdateModel::UPDATE_PC_BATCH : ProdUpdateModel::UPDATE_BATCH;
$update->logManyUpdates(array_keys($upcs), $updateType);
}
function ajax_response()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
switch (FormLib::get_form_value('ajax')) {
case 'save':
$upc = FormLib::get_form_value('upc');
$store_id = FormLib::get('store_id');
$upc = BarcodeLib::padUPC($upc);
$values = array();
$model = new ProductsModel($dbc);
$model->upc($upc);
$model->store_id($store_id);
$brand = FormLib::get('brand');
if ($brand !== '') {
$model->brand($brand);
}
$desc = FormLib::get_form_value('desc');
if ($desc !== '') {
$model->description($desc);
}
$dept = FormLib::get_form_value('dept');
if ($dept !== '') {
$model->department($dept);
}
$price = rtrim(FormLib::get_form_value('price'), ' ');
if ($price !== '') {
$model->normal_price($price);
}
$cost = rtrim(FormLib::get_form_value('cost'), ' ');
if ($cost !== '') {
$model->cost($cost);
}
$tax = FormLib::get_form_value('tax');
if ($tax !== '') {
$model->tax($tax);
}
$fsx = FormLib::get_form_value('fs');
if ($fsx !== '') {
$model->foodstamp($fsx);
}
$disc = FormLib::get_form_value('disc');
if ($disc !== '') {
$model->discount($disc);
}
$wgt = FormLib::get_form_value('wgt');
if ($wgt !== '') {
$model->scale($wgt);
}
$loc = FormLib::get_form_value('local');
if ($loc !== '') {
$model->local($loc);
}
$supplier = FormLib::get_form_value('supplier');
/**
Normalize free-form supplier text
Look up corresponding vendor ID
*/
$vendorID = '';
$vendors = new VendorsModel($dbc);
$vendors->vendorName($supplier);
foreach ($vendors->find() as $obj) {
$vendorID = $obj->vendorID();
break;
}
if ($vendorID !== '') {
$model->default_vendor_id($vendorID);
}
$model->save();
$chkP = $dbc->prepare('SELECT upc FROM prodExtra WHERE upc=?');
$chkR = $dbc->execute($chkP, array($upc));
if ($dbc->num_rows($chkR) > 0) {
$extraP = $dbc->prepare_statement('UPDATE prodExtra SET manufacturer=?, distributor=? WHERE upc=?');
$dbc->exec_statement($extraP, array($brand, $supplier, $upc));
} else {
$extraP = $dbc->prepare('INSERT INTO prodExtra
(upc, variable_pricing, margin, manufacturer, distributor)
VALUES
(?, 0, 0, ?, ?)');
$dbc->execute($extraP, array($upc, $brand, $supplier));
}
if ($vendorID !== '') {
$item = new VendorItemsModel($dbc);
$item->createIfMissing($upc, $vendorID);
$item->updateCostByUPC($upc, $cost, $vendorID);
}
updateProductAllLanes($upc);
break;
case 'deleteCheck':
$upc = FormLib::get_form_value('upc');
$upc = BarcodeLib::padUPC($upc);
$encoded_desc = FormLib::get_form_value('desc');
$desc = base64_decode($encoded_desc);
$fetchP = $dbc->prepare_statement("select normal_price,\n special_price,t.description,\n case when foodstamp = 1 then 'Yes' else 'No' end as fs,\n case when scale = 1 then 'Yes' else 'No' end as s\n from products as p left join taxrates as t\n on p.tax = t.id\n where upc=? and p.description=?");
$fetchR = $dbc->exec_statement($fetchP, array($upc, $desc));
$fetchW = $dbc->fetch_array($fetchR);
$ret = "Delete item {$upc} - {$desc}?\n";
$ret .= "Normal price: " . rtrim($fetchW[0]) . "\n";
$ret .= "Sale price: " . rtrim($fetchW[1]) . "\n";
$ret .= "Tax: " . rtrim($fetchW[2]) . "\n";
$ret .= "Foodstamp: " . rtrim($fetchW[3]) . "\n";
$ret .= "Scale: " . rtrim($fetchW[4]) . "\n";
$json = array('alertBox' => $ret, 'upc' => ltrim($upc, '0'), 'enc_desc' => $encoded_desc);
echo json_encode($json);
break;
case 'doDelete':
$upc = FormLib::get_form_value('upc');
$upc = BarcodeLib::padUPC($upc);
$desc = base64_decode(FormLib::get_form_value('desc'));
$update = new ProdUpdateModel($dbc);
$update->upc($upc);
$update->logUpdate(ProdUpdateModel::UPDATE_DELETE);
$model = new ProductsModel($dbc);
$model->upc($upc);
$model->delete();
$model = new ProductUserModel($dbc);
$model->upc($upc);
$model->delete();
$model = new ScaleItemsModel($dbc);
$model->plu($upc);
$model->delete();
$delP = $dbc->prepare_statement("delete from prodExtra where upc=?");
$delXR = $dbc->exec_statement($delP, array($upc));
$delP = $dbc->prepare_statement("DELETE FROM upcLike WHERE upc=?");
$delR = $dbc->exec_statement($delP, array($upc));
deleteProductAllLanes($upc);
break;
default:
echo 'Unknown Action';
break;
}
}
function process_file($linedata)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$idP = $dbc->prepare_statement("SELECT vendorID FROM vendors WHERE vendorName='UNFI' ORDER BY vendorID");
$idR = $dbc->exec_statement($idP);
if ($dbc->num_rows($idR) == 0) {
$this->error_details = 'Cannot find vendor';
return False;
}
$idW = $dbc->fetchRow($idR);
$VENDOR_ID = $idW['vendorID'];
$SKU = $this->get_column_index('sku');
$BRAND = $this->get_column_index('brand');
$DESCRIPTION = $this->get_column_index('desc');
$QTY = $this->get_column_index('qty');
$SIZE1 = $this->get_column_index('size');
$UPC = $this->get_column_index('upc');
$CATEGORY = $this->get_column_index('cat');
$REG_COST = $this->get_column_index('cost');
$NET_COST = $this->get_column_index('saleCost');
$SRP = $this->get_column_index('srp');
$FLAGS = $this->get_column_index('flags');
// PLU items have different internal UPCs
// map vendor SKUs to the internal PLUs
$SKU_TO_PLU_MAP = array();
$skusP = $dbc->prepare_statement('SELECT sku, upc FROM vendorSKUtoPLU WHERE vendorID=?');
$skusR = $dbc->execute($skusP, array($VENDOR_ID));
while ($skusW = $dbc->fetch_row($skusR)) {
$SKU_TO_PLU_MAP[$skusW['sku']] = $skusW['upc'];
}
$extraP = $dbc->prepare_statement("update prodExtra set cost=? where upc=?");
$prodP = $dbc->prepare('
UPDATE products
SET cost=?,
numflag= numflag | ? | ?,
modified=' . $dbc->now() . '
WHERE upc=?
AND default_vendor_id=?');
$itemP = $dbc->prepare("\n INSERT INTO vendorItems (\n brand, \n sku,\n size,\n upc,\n units,\n cost,\n description,\n vendorDept,\n vendorID,\n saleCost,\n modified,\n srp\n ) VALUES (\n ?,\n ?,\n ?,\n ?,\n ?,\n ?,\n ?,\n ?,\n ?,\n ?,\n ?,\n ?\n )");
$srpP = false;
if ($dbc->tableExists('vendorSRPs')) {
$srpP = $dbc->prepare_statement("INSERT INTO vendorSRPs (vendorID, upc, srp) VALUES (?,?,?)");
}
$updated_upcs = array();
$rounder = new \COREPOS\Fannie\API\item\PriceRounder();
foreach ($linedata as $data) {
if (!is_array($data)) {
continue;
}
if (!isset($data[$UPC])) {
continue;
}
// grab data from appropriate columns
$sku = $SKU !== false ? $data[$SKU] : '';
$sku = str_pad($sku, 7, '0', STR_PAD_LEFT);
$brand = $data[$BRAND];
$description = $data[$DESCRIPTION];
$qty = $data[$QTY];
$size = $SIZE1 !== false ? $data[$SIZE1] : '';
$prodInfo = $FLAGS !== false ? $data[$FLAGS] : '';
$flag = 0;
$upc = substr($data[$UPC], 0, 13);
// zeroes isn't a real item, skip it
if ($upc == "0000000000000") {
continue;
}
if (isset($SKU_TO_PLU_MAP[$sku])) {
$upc = $SKU_TO_PLU_MAP[$sku];
if (substr($size, -1) == '#' && substr($upc, 0, 3) == '002') {
$qty = trim($size, '# ');
$size = '#';
} elseif (substr($size, -2) == 'LB' && substr($upc, 0, 3) == '002') {
$qty = trim($size, 'LB ');
$size = 'LB';
}
}
$category = $data[$CATEGORY];
$reg = trim($data[$REG_COST]);
$net = $NET_COST !== false ? trim($data[$NET_COST]) : 0.0;
// blank spreadsheet cell
if (empty($net)) {
$net = 0;
}
$srp = trim($data[$SRP]);
// can't process items w/o price (usually promos/samples anyway)
if (empty($reg) or empty($srp)) {
continue;
}
// syntax fixes. kill apostrophes in text fields,
// trim $ off amounts as well as commas for the
// occasional > $1,000 item
$brand = str_replace("'", "", $brand);
$description = str_replace("'", "", $description);
$reg = str_replace('$', "", $reg);
$reg = str_replace(",", "", $reg);
$net = str_replace('$', "", $net);
$net = str_replace(",", "", $net);
$srp = str_replace('$', "", $srp);
$srp = str_replace(",", "", $srp);
// sale price isn't really a discount
if ($reg == $net) {
$net = 0;
}
// skip the item if prices aren't numeric
// this will catch the 'label' line in the first CSV split
// since the splits get returned in file system order,
// we can't be certain *when* that chunk will come up
if (!is_numeric($reg) or !is_numeric($srp)) {
continue;
}
$srp = $rounder->round($srp);
// set organic flag on OG1 (100%) or OG2 (95%)
$organic_flag = 0;
if (strstr($prodInfo, 'OG2') || strstr($prodInfo, 'OG1')) {
$organic_flag = 17;
}
// set gluten-free flag on g
$gf_flag = 0;
if (strstr($prodInfo, 'g')) {
$gf_flag = 18;
}
// need unit cost, not case cost
$reg_unit = $reg / $qty;
$net_unit = $net / $qty;
$dbc->exec_statement($extraP, array($reg_unit, $upc));
$dbc->exec_statement($prodP, array($reg_unit, $organic_flag, $gf_flag, $upc, $VENDOR_ID));
$updated_upcs[] = $upc;
$args = array($brand, $sku === false ? '' : $sku, $size === false ? '' : $size, $upc, $qty, $reg_unit, $description, $category, $VENDOR_ID, $net_unit, date('Y-m-d H:i:s'), $srp);
$dbc->execute($itemP, $args);
if ($srpP) {
$dbc->exec_statement($srpP, array($VENDOR_ID, $upc, $srp));
}
}
$updateModel = new ProdUpdateModel($dbc);
$updateModel->logManyUpdates($updated_upcs, ProdUpdateModel::UPDATE_EDIT);
return true;
}
public function run()
{
$sql = FannieDB::get($this->config->get('OP_DB'));
$chk_vital = array();
$chk_opt = array();
/* change prices
*/
if (strstr($this->config->get('SERVER_DBMS'), "MYSQL")) {
$chk_vital[] = $sql->query("UPDATE products AS p LEFT JOIN\n batchList AS l ON l.upc=p.upc LEFT JOIN\n batches AS b ON b.batchID=l.batchID\n SET p.normal_price = l.salePrice\n WHERE l.batchID=b.batchID AND l.upc=p.upc\n AND l.upc NOT LIKE 'LC%'\n AND b.discounttype = 0\n AND " . $sql->datediff($sql->now(), 'b.startDate') . " = 0");
} else {
$chk_vital[] = $sql->query("UPDATE products SET\n normal_price = l.salePrice\n FROM products AS p, batches AS b, batchList AS l\n WHERE l.batchID=b.batchID AND l.upc=p.upc\n AND l.upc NOT LIKE 'LC%'\n AND b.discounttype = 0\n AND " . $sql->datediff($sql->now(), 'b.startDate') . " = 0");
}
/* likecoded items differentiated
for char concatenation
*/
if (strstr($this->config->get('SERVER_DBMS'), "MYSQL")) {
$chk_vital[] = $sql->query("UPDATE products AS p LEFT JOIN\n upcLike AS v ON v.upc=p.upc LEFT JOIN\n batchList AS l ON l.upc=concat('LC',convert(v.likeCode,char))\n LEFT JOIN batches AS b ON b.batchID = l.batchID\n SET p.normal_price = l.salePrice\n WHERE l.upc LIKE 'LC%'\n AND b.discounttype = 0\n AND " . $sql->datediff($sql->now(), 'b.startDate') . " = 0");
} else {
$chk_vital[] = $sql->query("UPDATE products SET normal_price = l.salePrice\n FROM products AS p LEFT JOIN\n upcLike AS v ON v.upc=p.upc LEFT JOIN\n batchList AS l ON l.upc='LC'+convert(varchar,v.likecode)\n LEFT JOIN batches AS b ON b.batchID = l.batchID\n WHERE l.upc LIKE 'LC%'\n AND b.discounttype = 0\n AND " . $sql->datediff($sql->now(), 'b.startDate') . " = 0");
}
$success = true;
foreach ($chk_vital as $chk) {
if ($chk === false) {
$success = false;
break;
}
}
if ($success) {
$this->cronMsg("Price change batches run successfully");
} else {
$this->cronMsg("Error running price change batches");
}
// log updates to prodUpdate table
$success = true;
$likeP = $sql->prepare('SELECT upc FROM upcLike WHERE likeCode=?');
$batchQ = 'SELECT upc FROM batchList as l LEFT JOIN batches AS b
ON l.batchID=b.batchID WHERE b.discounttype=0
AND ' . $sql->datediff($sql->now(), 'b.startDate') . ' = 0';
$batchR = $sql->query($batchQ);
$prodUpdate = new ProdUpdateModel($sql);
while ($batchW = $sql->fetch_row($batchR)) {
$upcs = array();
$upc = $batchW['upc'];
// unpack likecodes to UPCs
if (substr($upc, 0, 2) == 'LC') {
$likeR = $sql->execute($likeP, array(substr($upc, 2)));
while ($likeW = $sql->fetch_row($likeR)) {
$upcs[] = $likeW['upc'];
}
} else {
$upcs[] = $upc;
}
foreach ($upcs as $item) {
$prodUpdate->reset();
$prodUpdate->upc($item);
$logged = $prodUpdate->logUpdate(ProdUpdateModel::UPDATE_PC_BATCH, 1001);
if (!$logged) {
$success = false;
}
}
}
if ($success) {
$this->cronMsg("Changes logged in prodUpdate");
} else {
$this->cronMsg("Error logging changes");
}
}
/**
Cleanup after forcibly starting or stopping a sales batch
- Update lane item records to reflect on/off sale
- Log changes to prodUpdate
@param $id [int] batchID
@param $updateType [cost] ProdUpdateModel update type
@param $has_limit [boolean] products.special_limit and batches.transLimit
columns are present
Separate method since it's identical for starting
and stopping
*/
private function finishForce($id, $updateType, $has_limit = true)
{
$columnsP = $this->connection->prepare('
SELECT p.upc,
p.normal_price,
p.special_price,
p.modified,
p.specialpricemethod,
p.specialquantity,
p.specialgroupprice,
' . ($has_limit ? 'p.special_limit,' : '') . '
p.discounttype,
p.mixmatchcode,
p.start_date,
p.end_date
FROM products AS p
INNER JOIN batchList AS b ON p.upc=b.upc
WHERE b.batchID=?');
$lcColumnsP = $this->connection->prepare('
SELECT p.upc,
p.normal_price,
p.special_price,
p.modified,
p.specialpricemethod,
p.specialquantity,
p.specialgroupprice,
' . ($has_limit ? 'p.special_limit,' : '') . '
p.discounttype,
p.mixmatchcode,
p.start_date,
p.end_date
FROM products AS p
INNER JOIN upcLike AS u ON p.upc=u.upc
INNER JOIN batchList AS b
ON b.upc = ' . $this->connection->concat("'LC'", $this->connection->convert('u.likeCode', 'CHAR'), '') . '
WHERE b.batchID=?');
/**
Get changed columns for each product record
*/
$upcs = array();
$columnsR = $this->connection->execute($columnsP, array($id));
while ($w = $this->connection->fetch_row($columnsR)) {
$upcs[$w['upc']] = $w;
}
$columnsR = $this->connection->execute($lcColumnsP, array($id));
while ($w = $this->connection->fetch_row($columnsR)) {
$upcs[$w['upc']] = $w;
}
$updateQ = '
UPDATE products AS p SET
p.normal_price = ?,
p.special_price = ?,
p.modified = ?,
p.specialpricemethod = ?,
p.specialquantity = ?,
p.specialgroupprice = ?,
p.discounttype = ?,
p.mixmatchcode = ?,
p.start_date = ?,
p.end_date = ?
' . ($has_limit ? ',p.special_limit = ?' : '') . '
WHERE p.upc = ?';
/**
Update all records on each lane before proceeding
to the next lane. Hopefully faster / more efficient
*/
$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;
}
$updateP = $lane_sql->prepare($updateQ);
foreach ($upcs as $upc => $data) {
$args = array($data['normal_price'], $data['special_price'], $data['modified'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['start_date'], $data['end_date']);
if ($has_limit) {
$args[] = $data['special_limit'];
}
$args[] = $upc;
$lane_sql->execute($updateP, $args);
}
}
$update = new ProdUpdateModel($this->connection);
$update->logManyUpdates(array_keys($upcs), $updateType);
}
function process_file($linedata)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$idP = $dbc->prepare_statement("SELECT vendorID FROM vendors WHERE vendorName='UNFI' ORDER BY vendorID");
$VENDOR_ID = $dbc->getValue($idP);
if ($VENDOR_ID === false) {
$this->error_details = 'Cannot find vendor';
return False;
}
$VENDOR_ID = $idW['vendorID'];
$ruleType = FormLib::get('ruleType');
$review = FormLib::get('reviewDate');
$SKU = $this->get_column_index('sku');
$QTY = $this->get_column_index('qty');
$UPC = $this->get_column_index('upc');
$REG_COST = $this->get_column_index('cost');
$NET_COST = $this->get_column_index('saleCost');
$SRP = $this->get_column_index('srp');
// PLU items have different internal UPCs
// map vendor SKUs to the internal PLUs
$SKU_TO_PLU_MAP = array();
$skusP = $dbc->prepare_statement('SELECT sku, upc FROM vendorSKUtoPLU WHERE vendorID=?');
$skusR = $dbc->execute($skusP, array($VENDOR_ID));
while ($skusW = $dbc->fetch_row($skusR)) {
$SKU_TO_PLU_MAP[$skusW['sku']] = $skusW['upc'];
}
$extraP = $dbc->prepare_statement("update prodExtra set cost=?,variable_pricing=1 where upc=?");
$prodP = $dbc->prepare('
UPDATE products
SET cost=?,
modified=' . $dbc->now() . '
WHERE upc=?
AND default_vendor_id=?');
$itemP = $dbc->prepare('
UPDATE vendorItems
SET cost=?,
saleCost=?,
srp=?,
modified=?
WHERE sku=?
AND vendorID=?');
$srpP = false;
if ($dbc->tableExists('vendorSRPs')) {
$srpP = $dbc->prepare_statement("INSERT INTO vendorSRPs (vendorID, upc, srp) VALUES (?,?,?)");
}
$updated_upcs = array();
$upcP = $dbc->prepare('SELECT price_rule_id FROM products WHERE upc=? AND inUse=1');
$ruleP = $dbc->prepare('SELECT * FROM PriceRules WHERE priceRuleID=?');
$ruleInsP = $dbc->prepare('
INSERT INTO PriceRules
(priceRuleTypeID, maxPrice, reviewDate, details)
VALUES
(?, ?, ?, ?)
');
$ruleUpP = $dbc->prepare('
UPDATE PriceRules
SET priceRuleTypeID=?,
maxPrice=?,
reviewDate=?,
details=?
WHERE priceRuleID=?');
$prodRuleP = $dbc->prepare('UPDATE products SET price_rule_id=? WHERE upc=?');
foreach ($linedata as $data) {
if (!is_array($data)) {
continue;
}
if (!isset($data[$UPC])) {
continue;
}
// grab data from appropriate columns
$sku = $SKU !== false ? $data[$SKU] : '';
$sku = str_pad($sku, 7, '0', STR_PAD_LEFT);
$qty = $data[$QTY];
$upc = substr($data[$UPC], 0, 13);
// zeroes isn't a real item, skip it
if ($upc == "0000000000000") {
continue;
}
if (isset($SKU_TO_PLU_MAP[$sku])) {
$upc = $SKU_TO_PLU_MAP[$sku];
if (substr($size, -1) == '#' && substr($upc, 0, 3) == '002') {
$qty = trim($size, '# ');
} elseif (substr($size, -2) == 'LB' && substr($upc, 0, 3) == '002') {
$qty = trim($size, 'LB ');
}
}
$reg = trim($data[$REG_COST]);
$net = $NET_COST !== false ? trim($data[$NET_COST]) : 0.0;
// blank spreadsheet cell
if (empty($net)) {
$net = 0;
}
$srp = trim($data[$SRP]);
// can't process items w/o price (usually promos/samples anyway)
if (empty($reg) or empty($srp)) {
continue;
}
// syntax fixes. kill apostrophes in text fields,
// trim $ off amounts as well as commas for the
// occasional > $1,000 item
$reg = str_replace('$', "", $reg);
$reg = str_replace(",", "", $reg);
$net = str_replace('$', "", $net);
$net = str_replace(",", "", $net);
$srp = str_replace('$', "", $srp);
$srp = str_replace(",", "", $srp);
// sale price isn't really a discount
if ($reg == $net) {
$net = 0;
}
// skip the item if prices aren't numeric
// this will catch the 'label' line in the first CSV split
// since the splits get returned in file system order,
// we can't be certain *when* that chunk will come up
if (!is_numeric($reg) or !is_numeric($srp)) {
continue;
}
// need unit cost, not case cost
$reg_unit = $reg / $qty;
$net_unit = $net / $qty;
$dbc->exec_statement($extraP, array($reg_unit, $upc));
$dbc->exec_statement($prodP, array($reg_unit, $upc, $VENDOR_ID));
$updated_upcs[] = $upc;
$args = array($reg_unit, $net_unit, $srp, date('Y-m-d H:i:s'), $sku, $VENDOR_ID);
$dbc->execute($itemP, $args);
if ($srpP) {
$dbc->exec_statement($srpP, array($VENDOR_ID, $upc, $srp));
}
$rule_id = $dbc->getValue($upcP, array($upc));
$ruleR = $dbc->execute($ruleP, array($rule_id));
if ($rule_id > 1 && $dbc->numRows($ruleR)) {
// update existing rule with latest price
$args = array($ruleType, $srp, $review, 'NCG MAX ' . $srp, $rule_id);
$dbc->execute($ruleUpP, $args);
} else {
// create a new pricing rule
// attach it to the item
$args = array($ruleType, $srp, $review, 'NCG MAX ' . $srp);
$dbc->execute($ruleInsP, $args);
$rule_id = $dbc->insertID();
$dbc->execute($prodRuleP, array($rule_id, $upc));
}
}
$updateModel = new ProdUpdateModel($dbc);
$updateModel->logManyUpdates($updated_upcs, ProdUpdateModel::UPDATE_EDIT);
return true;
}
/**
Scan prodUpdate from dept changes and log them
in prodDepartmentHistory
@param $dbc [SQLManager] db connection
@param $offset [optional int] start scanning from this prodUpdateID
*/
private function scanDeptChanges($dbc, $offset = 0)
{
$prodUpdateQ = 'SELECT prodUpdateID FROM prodUpdate ';
$args = array();
if ($offset > 0) {
$prodUpdateQ .= ' WHERE prodUpdateID > ? ';
$args[] = $offset;
}
$prodUpdateQ .= ' ORDER BY upc, modified';
$prodUpdateP = $dbc->prepare($prodUpdateQ);
$prodUpdateR = $dbc->execute($prodUpdateP, $args);
$chkP = $dbc->prepare("SELECT modified,dept_ID FROM\n prodDepartmentHistory WHERE upc=?\n ORDER BY modified DESC");
$upc = null;
$prevDept = null;
$update = new ProdUpdateModel($dbc);
$history = new ProdDepartmentHistoryModel($dbc);
/**
See scanPriceChanges()
*/
while ($prodUpdateW = $dbc->fetch_row($prodUpdateR)) {
$update->reset();
$update->prodUpdateID($prodUpdateW['prodUpdateID']);
$update->load();
if ($upc === null || $upc != $update->upc()) {
$upc = $update->upc();
$prevDept = null;
$chkR = $dbc->execute($chkP, array($upc));
if ($dbc->num_rows($chkR) > 0) {
$chkW = $dbc->fetch_row($chkR);
$prevDept = $chkW['dept_ID'];
}
}
if ($prevDept != $update->dept()) {
$history->reset();
$history->upc($upc);
$history->storeID($update->storeID());
$history->modified($update->modified());
$history->dept_ID($update->dept());
$history->uid($update->user());
$history->prodUpdateID($update->prodUpdateID());
$this->cronMsg('Add dept change #' . $update->prodUpdateID(), FannieLogger::INFO);
$history->save();
}
$prevDept = $update->dept();
}
}
