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