public function fetch_report_data() { $dbc = $this->connection; try { $vendorID = $this->form->id; $deptID = $this->form->category; } catch (Exception $ex) { return array(); } $prep = $dbc->prepare(' SELECT p.upc, p.brand, p.description, p.department, d.dept_name, p.cost, p.normal_price FROM vendorItems AS v INNER JOIN products AS p ON p.default_vendor_id=v.vendorID AND p.upc=v.upc LEFT JOIN departments AS d ON p.department=d.dept_no WHERE v.vendorID=? AND v.vendorDept=? '); $res = $dbc->execute($prep, array($vendorID, $deptID)); $data = array(); while ($row = $dbc->fetchRow($res)) { $data[] = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], $row['cost'], $row['normal_price'], sprintf('%.2f%%', \COREPOS\Fannie\API\item\Margin::toMargin($row['cost'], $row['normal_price']) * 100)); } return $data; }
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; }
private function calculateMargin($price, $cost, $deptID, $upc) { $dbc = $this->db(); $desired_margin = 'Unknown'; $dept = new DepartmentsModel($dbc); $dept->dept_no($deptID); $dept_name = 'n/a'; if ($dept->load()) { $desired_margin = $dept->margin() * 100; $dept_name = $dept->dept_name(); } $ret = "Desired margin on this department (" . $dept_name . ") is " . $desired_margin . "%"; $ret .= "<br />"; $vendorP = $dbc->prepare(' SELECT d.margin, n.vendorName, d.name, s.margin AS specialMargin FROM products AS p INNER JOIN vendorItems AS v ON p.upc=v.upc AND v.vendorID=p.default_vendor_id INNER JOIN vendorDepartments AS d ON v.vendorID=d.vendorID AND v.vendorDept=d.deptID INNER JOIN vendors AS n ON v.vendorID=n.vendorID LEFT JOIN VendorSpecificMargins AS s ON p.department=s.deptID AND p.default_vendor_id=s.vendorID WHERE p.upc = ? '); $vendorR = $dbc->execute($vendorP, array($upc)); if ($vendorR && $dbc->num_rows($vendorR) > 0) { $w = $dbc->fetch_row($vendorR); $desired_margin = $w['margin'] * 100; if ($w['specialMargin']) { $desired_margin = 100 * $w['specialMargin']; $w['name'] = 'Custom'; } $ret .= sprintf('Desired margin for this vendor category (%s) is %.2f%%<br />', $w['vendorName'] . ':' . $w['name'], $desired_margin); } $shippingP = $dbc->prepare(' SELECT v.shippingMarkup, v.discountRate, v.vendorName FROM products AS p INNER JOIN vendors AS v ON p.default_vendor_id = v.vendorID WHERE p.upc=?'); $shippingR = $dbc->execute($shippingP, array($upc)); $shipping_markup = 0.0; $vendor_discount = 0.0; if ($shippingR && $dbc->numRows($shippingR) > 0) { $w = $dbc->fetchRow($shippingR); if ($w['discountRate'] > 0) { $ret .= sprintf('Discount rate for this vendor (%s) is %.2f%%<br />', $w['vendorName'], $w['discountRate'] * 100); $vendor_discount = $w['discountRate']; } if ($w['shippingMarkup'] > 0) { $ret .= sprintf('Shipping markup for this vendor (%s) is %.2f%%<br />', $w['vendorName'], $w['shippingMarkup'] * 100); $shipping_markup = $w['discountRate']; } } $cost = Margin::adjustedCost($cost, $vendor_discount, $shipping_markup); $actual_margin = Margin::toMargin($cost, $price, array(100, 2)); if ($actual_margin > $desired_margin && is_numeric($desired_margin)) { $ret .= sprintf("<span class=\"alert-success\">Current margin on this item is %.2f%%</span><br />", $actual_margin); } elseif (!is_numeric($price)) { $ret .= "<span class=\"alert-danger\">No price has been saved for this item</span><br />"; } else { $ret .= sprintf("<span class=\"alert-danger\">Current margin on this item is %.2f%%</span><br />", $actual_margin); $srp = $this->getSRP($cost, $desired_margin / 100.0); $ret .= sprintf("Suggested price: \$%.2f ", $srp); $ret .= sprintf("(<a href=\"\" onclick=\"\$('.price-input').val(%.2f); \$('.price-input:visible').trigger('change'); return false;\">Use this price</a>)", $srp); } return $ret; }
public function get_id_view() { $this->addScript('pricing-batch.js'); $dbc = $this->connection; $dbc->selectDB($this->config->OP_DB); $superID = FormLib::get_form_value('super', 99); $queueID = FormLib::get('queueID'); $vendorID = $this->id; $filter = FormLib::get_form_value('filter') == 'Yes' ? True : False; /* lookup vendor and superdept names to build a batch name */ $sname = "All"; if ($superID != 99) { $smodel = new SuperDeptNamesModel($dbc); $smodel->superID($superID); $smodel->load(); $sname = $smodel->super_name(); } $vendor = new VendorsModel($dbc); $vendor->vendorID($vendorID); $vendor->load(); $batchName = $sname . " " . $vendor->vendorName() . " PC " . date('m/d/y'); /* find a price change batch type */ $types = new BatchTypeModel($dbc); $types->discType(0); $bType = 0; foreach ($types->find() as $obj) { $bType = $obj->batchTypeID(); break; } /* get the ID of the current batch. Create it if needed. */ $bidQ = $dbc->prepare("\n SELECT batchID \n FROM batches \n WHERE batchName=? \n AND batchType=? \n AND discounttype=0\n ORDER BY batchID DESC"); $bidR = $dbc->execute($bidQ, array($batchName, $bType)); $batchID = 0; if ($dbc->numRows($bidR) == 0) { $b = new BatchesModel($dbc); $b->batchName($batchName); $b->startDate('1900-01-01'); $b->endDate('1900-01-01'); $b->batchType($bType); $b->discountType(0); $b->priority(0); $batchID = $b->save(); } else { $bidW = $dbc->fetchRow($bidR); $batchID = $bidW['batchID']; } $ret = sprintf('<b>Batch</b>: <a href="%sbatches/newbatch/BatchManagementTool.php?startAt=%d">%s</a>', $this->config->URL, $batchID, $batchName); $ret .= sprintf("<input type=hidden id=vendorID value=%d />\n <input type=hidden id=batchID value=%d />\n <input type=hidden id=queueID value=%d />\n <input type=hidden id=superID value=%d />", $vendorID, $batchID, $queueID, $superID); $batchUPCs = array(); $batchList = new BatchListModel($dbc); $batchList->batchID($batchID); foreach ($batchList->find() as $obj) { $batchUPCs[$obj->upc()] = true; } $costSQL = Margin::adjustedCostSQL('v.cost', 'b.discountRate', 'b.shippingMarkup'); $marginSQL = Margin::toMarginSQL($costSQL, 'p.normal_price'); $p_def = $dbc->tableDefinition('products'); $marginCase = ' CASE WHEN g.margin IS NOT NULL AND g.margin <> 0 THEN g.margin WHEN s.margin IS NOT NULL AND s.margin <> 0 THEN s.margin ELSE d.margin END'; $srpSQL = Margin::toPriceSQL($costSQL, $marginCase); $query = "SELECT p.upc,\n p.description,\n v.cost,\n b.shippingMarkup,\n b.discountRate,\n p.normal_price,\n " . Margin::toMarginSQL($costSQL, 'p.normal_price') . " AS current_margin,\n " . Margin::toMarginSQL($costSQL, 'v.srp') . " AS desired_margin,\n " . $costSQL . " AS adjusted_cost,\n v.srp,\n " . $srpSQL . " AS rawSRP,\n v.vendorDept,\n x.variable_pricing,\n " . $marginCase . " AS margin\n FROM products AS p \n INNER JOIN vendorItems AS v ON p.upc=v.upc AND p.default_vendor_id=v.vendorID\n INNER JOIN vendors as b ON v.vendorID=b.vendorID\n LEFT JOIN departments AS d ON p.department=d.dept_no\n LEFT JOIN vendorDepartments AS s ON v.vendorDept=s.deptID AND v.vendorID=s.vendorID\n LEFT JOIN VendorSpecificMargins AS g ON p.department=g.deptID AND v.vendorID=g.vendorID\n LEFT JOIN prodExtra AS x on p.upc=x.upc "; $args = array($vendorID); if ($superID != 99) { $query .= " LEFT JOIN MasterSuperDepts AS m\n ON p.department=m.dept_ID "; } $query .= "WHERE v.cost > 0 \n AND v.vendorID=?\n AND p.inUse=1 "; if ($superID != 99) { $query .= " AND m.superID=? "; $args[] = $superID; } if ($filter === false) { $query .= " AND p.normal_price <> v.srp "; } if ($this->config->get('STORE_MODE') == 'HQ') { $query .= ' AND p.store_id=? '; $args[] = $this->config->get('STORE_ID'); } $query .= " ORDER BY p.upc"; if (isset($p_def['price_rule_id'])) { $query = str_replace('x.variable_pricing', 'p.price_rule_id AS variable_pricing', $query); } $prep = $dbc->prepare_statement($query); $result = $dbc->exec_statement($prep, $args); $ret .= "<table class=\"table table-bordered small\">"; $ret .= "<tr><td colspan=6> </td><th colspan=2>Current</th>\n <th colspan=3>Vendor</th></tr>"; $ret .= "<tr><th>UPC</th><th>Our Description</th>\n <th>Base Cost</th>\n <th>Shipping</th>\n <th>Discount%</th>\n <th>Adj. Cost</th>\n <th>Price</th><th>Margin</th><th>Raw</th><th>SRP</th>\n <th>Margin</th><th>Cat</th><th>Var</th>\n <th>Batch</th></tr>"; while ($row = $dbc->fetch_row($result)) { $background = "white"; if (isset($batchUPCs[$row['upc']])) { $background = 'selection'; } elseif ($row['variable_pricing'] == 0) { $background = $row['normal_price'] + 0.1 < $row['rawSRP'] ? 'red' : 'green'; if ($row['normal_price'] - 0.1 > $row['rawSRP']) { $background = $row['normal_price'] - 0.1 > $row['rawSRP'] ? 'yellow' : 'green'; } } if (isset($batchUPCs[$row['upc']])) { $icon = '<span class="glyphicon glyphicon-minus-sign" title="Remove from batch"> </span>'; } else { $icon = '<span class="glyphicon glyphicon-plus-sign" title="Add to batch"> </span>'; } $ret .= sprintf("<tr id=row%s class=%s>\n <td class=\"sub\"><a href=\"%sitem/ItemEditorPage.php?searchupc=%s\">%s</a></td>\n <td class=\"sub\">%s</td>\n <td class=\"sub cost\">%.2f</td>\n <td class=\"sub shipping\">%.2f%%</td>\n <td class=\"sub discount\">%.2f%%</td>\n <td class=\"sub adj-cost\">%.2f</td>\n <td class=\"sub price\">%.2f</td>\n <td class=\"sub cmargin\">%.2f%%</td>\n <td class=\"sub raw-srp\">%.2f</td>\n <td onclick=\"reprice('%s');\" class=\"sub srp\">%.2f</td>\n <td class=\"sub dmargin\">%.2f%%</td>\n <td class=\"sub\">%d</td>\n <td><input class=varp type=checkbox onclick=\"toggleV('%s');\" %s /></td>\n <td class=white>\n <a class=\"add-button %s\" href=\"\" \n onclick=\"addToBatch('%s'); return false;\">\n <span class=\"glyphicon glyphicon-plus-sign\"\n title=\"Add item to batch\"></span>\n </a>\n <a class=\"remove-button %s\" href=\"\" \n onclick=\"removeFromBatch('%s'); return false;\">\n <span class=\"glyphicon glyphicon-minus-sign\"\n title=\"Remove item from batch\"></span>\n </a>\n </td>\n </tr>", $row['upc'], $background, $this->config->URL, $row['upc'], $row['upc'], $row['description'], $row['cost'], $row['shippingMarkup'] * 100, $row['discountRate'] * 100, $row['adjusted_cost'], $row['normal_price'], 100 * $row['current_margin'], $row['rawSRP'], $row['upc'], $row['srp'], 100 * $row['desired_margin'], $row['vendorDept'], $row['upc'], $row['variable_pricing'] >= 1 ? 'checked' : '', isset($batchUPCs[$row['upc']]) ? 'collapse' : '', $row['upc'], !isset($batchUPCs[$row['upc']]) ? 'collapse' : '', $row['upc']); } $ret .= "</table>"; return $ret; }