public function get_id_view()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$id = $this->id;
$delQ = $dbc->prepare_statement("DELETE FROM vendorSRPs WHERE vendorID=?");
$delR = $dbc->exec_statement($delQ, array($id));
$query = '
SELECT v.upc,
v.sku,
v.cost,
CASE
WHEN a.margin IS NOT NULL THEN a.margin
WHEN b.margin IS NOT NULL THEN b.margin
ELSE 0
END AS margin,
COALESCE(n.shippingMarkup, 0) as shipping,
COALESCE(n.discountRate, 0) as discount
FROM vendorItems as v
LEFT JOIN vendorDepartments AS a ON v.vendorID=a.vendorID AND v.vendorDept=a.deptID
INNER JOIN vendors AS n ON v.vendorID=n.vendorID
LEFT JOIN products as p ON v.upc=p.upc AND v.vendorID=p.default_vendor_id
LEFT JOIN departments AS b ON p.department=b.dept_no
WHERE v.vendorID=?
AND (a.margin IS NOT NULL OR b.margin IS NOT NULL)';
$fetchP = $dbc->prepare($query);
$fetchR = $dbc->exec_statement($fetchP, array($id));
$upP = $dbc->prepare('
UPDATE vendorItems
SET srp=?,
modified=' . $dbc->now() . '
WHERE vendorID=?
AND sku=?');
$insP = false;
if ($dbc->tableExists('vendorSRPs')) {
$insP = $dbc->prepare_statement('INSERT INTO vendorSRPs VALUES (?,?,?)');
}
$rounder = new \COREPOS\Fannie\API\item\PriceRounder();
while ($fetchW = $dbc->fetch_array($fetchR)) {
// calculate a SRP from unit cost and desired margin
$adj = \COREPOS\Fannie\API\item\Margin::adjustedCost($fetchW['cost'], $fetchW['discount'], $fetchW['shipping']);
$srp = \COREPOS\Fannie\API\item\Margin::toPrice($adj, $fetchW['margin']);
$srp = $rounder->round($srp);
$upR = $dbc->execute($upP, array($srp, $id, $fetchW['sku']));
if ($insP) {
$insR = $dbc->exec_statement($insP, array($id, $fetchW['upc'], $srp));
}
}
$ret = "<b>SRPs have been updated</b><br />";
$ret .= sprintf('<p>
<a class="btn btn-default" href="index.php">Price Batch Tools</a>
<a class="btn btn-default"
href="%s/item/vendors/VendorIndexPage.php?vid=%d">Vendor Settings & Catalog</a>
</p>', $this->config->get('URL'), $id);
return $ret;
}
public function fetch_report_data()
{
$count = 0;
$item = array();
$upc = array();
$desc = array();
$cost = array();
$price = array();
$marg = array();
$var = array();
$srp = array();
$movement = array();
$dept = array();
$deptID = array();
$vendor = array();
$deptMarg = array();
//The Margin We're Using
$devMarg = array();
$devPrice = array();
$uMarg = array();
//UNFI margin
$dMarg = array();
//Department margin
$roundSRP = array();
// Connect
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
// Create List of Items
$query = "SELECT P.upc, P.description, P.cost, P.normal_price, P.department, \n P.modified, V.vendorDept, V.vendorID, D.margin as uMarg, D.vendorID, \n D.deptID, S.margin as dMarg, P.auto_par\n FROM products as P\n LEFT JOIN vendorItems as V ON P.upc = V.upc AND P.default_vendor_id = V.vendorID\n LEFT JOIN vendorDepartments as D ON (V.vendorID = D.vendorID) AND (D.deptID = V.vendorDept) \n LEFT JOIN departments as S ON (P.department = S.dept_no)\n WHERE P.inUse = 1 AND P.price_rule_id = 0\n AND P.cost <> 0 ";
if (FormLib::get('dept') == 1) {
$query .= " AND (P.department >= 1 AND P.department <= 25) OR (P.department >= 239 AND P.department <= 259) ";
} else {
if (FormLib::get('dept') == 2) {
$query .= " AND (P.department >= 26 AND P.department <= 59) ";
} else {
if (FormLib::get('dept') == 3) {
$query .= " AND (P.department >= 151 AND P.department <= 191) ";
} else {
if (FormLib::get('dept') == 4) {
$query .= " AND (P.department >= 86 AND P.department <= 128) ";
} else {
if (FormLib::get('dept') == 5) {
$query .= " AND (P.department >= 240 AND P.department <= 250) ";
}
}
}
}
}
$query .= " GROUP BY P.upc \n ORDER BY P.modified\n ; ";
$result = $dbc->query($query);
while ($row = $dbc->fetch_row($result)) {
$upc[] = $row['upc'];
$desc[] = $row['description'];
$cost[] = $row['cost'];
$price[] = $row['normal_price'];
$dept[] = $row['department'];
$movement[] = $row['auto_par'];
$uMarg = $row['uMarg'];
$dMarg = $row['dMarg'];
if ($uMarg == NULL) {
$deptMarg[] = $dMarg;
} else {
$deptMarg[] = $uMarg;
}
}
echo count($upc) . " items found<br>";
$rounder = new \COREPOS\Fannie\API\item\PriceRounder();
// Calculations
for ($i = 0; $i < count($upc); $i++) {
$marg[] = $price[$i] == 0 ? 0 : ($price[$i] - $cost[$i]) / $price[$i];
$devMarg[] = $marg[$i] - $deptMarg[$i];
$desiredPrice = 0;
$desiredPrice = $deptMarg[$i] == 1 ? 0 : $cost[$i] / (1 - $deptMarg[$i]);
$srp[] = $desiredPrice;
$roundSRP[] = $rounder->round($desiredPrice);
$devPrice[] = $price[$i] - $srp[$i];
}
for ($i = 0; $i < count($upc); $i++) {
if ($upc[$i] != NULL && $srp[$i] > 0 && $devPrice[$i] > 0 && $devMarg[$i] >= $this->form->degree) {
$item[] = array($upc[$i], $desc[$i], sprintf('%.2f', $movement[$i]), $cost[$i], $price[$i], sprintf('%.3f%%', $deptMarg[$i] * 100), sprintf('%.3f%%', $marg[$i] * 100), sprintf('%.3f%%', $devMarg[$i] * 100), sprintf('%.2f', $srp[$i]), $roundSRP[$i], sprintf('%.2f', $devPrice[$i]));
}
}
return $item;
}
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;
}
