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