} 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': $owner = $_GET['owner']; $out .= batchListDisplay($owner);
} 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)); } } } echo "<table>"; echo "<tr><td align=right><b>UPC</b></td><td><font color='red'>" . $row[0] . "</font><input type=hidden value='{$row['0']}' name=upc></td>"; echo "</tr><tr><td><b>Description</b></td><td>{$row['1']}</td>";
/** 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 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; } }
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"); } }
$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) { echo cron_msg("Changes logged in prodUpdate"); } else { echo cron_msg("Error logging changes"); }