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; }