public function get_id_break_handler()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$model = new VendorBreakdownsModel($dbc);
$model->vendorID($this->id);
$original = new VendorItemsModel($dbc);
$product = new ProductsModel($dbc);
foreach ($model->find() as $obj) {
$original->vendorID($this->id);
$original->sku($obj->sku());
if (!$original->load()) {
$this->addOnloadCommand("showBootstrapAlert('#alert-area', 'danger', 'Vendor SKU #" . $obj->sku() . " not found');\n");
continue;
}
$split_factor = false;
$unit_size = '';
if (preg_match('/^\\d+$/', $original->size())) {
$split_factor = $original->size();
} elseif (preg_match('/(\\d+)\\s*\\/\\s*(.+)/', $original->size(), $matches)) {
$split_factor = $matches[1];
$unit_size = $matches[2];
} elseif (preg_match('/(\\d+)\\s*CT/', $original->size(), $matches)) {
$split_factor = $matches[1];
} elseif (preg_match('/(\\d+)\\s*PKT/', $original->size(), $matches)) {
$split_factor = $matches[1];
}
if (!$split_factor) {
$this->addOnloadCommand("showBootstrapAlert('#alert-area', 'danger', 'Vendor SKU #" . $original->size() . " cannot be broken down');\n");
continue;
}
// add an entry using the store UPC/PLU in place of the vendor SKU
// since two records from the same vendor with same SKU are not
// permitted in the table
$original->sku($obj->upc());
$original->upc($obj->upc());
$original->units(1);
$original->size($unit_size);
$original->cost($original->cost() / $split_factor);
$original->saleCost($original->saleCost() / $split_factor);
if ($original->save()) {
// update cost in products table, too
$product->reset();
$product->upc($obj->upc());
foreach ($product->find('store_id') as $p) {
if ($p->load() && $p->default_vendor_id() == $this->id) {
$p->cost($original->cost());
$p->save();
$original->description($p->description());
$original->save();
}
}
} else {
$this->addOnloadCommand("showBootstrapAlert('#alert-area', 'success', 'Error saving vendor SKU #" . $obj->sku() . "');\n");
}
}
return true;
}
/**
Helper: create a vendorItems record for an existing
product if one does not exist
*/
public function createIfMissing($upc, $vendorID)
{
// look for entry directly by UPC or via SKU mapping
$findP = $this->connection->prepare('
SELECT v.upc
FROM vendorItems AS v
LEFT JOIN vendorSKUtoPLU AS m ON v.vendorID=m.vendorID AND v.sku=m.sku
WHERE v.vendorID=?
AND (v.upc=? OR m.upc=?)');
$findR = $this->connection->execute($findP, array($vendorID, $upc, $upc));
if ($this->connection->num_rows($findR) == 0) {
// create item from product
$prod = new ProductsModel($this->connection);
$prod->upc($upc);
$prod->load();
$vend = new VendorItemsModel($this->connection);
$vend->vendorID($vendorID);
$vend->upc($upc);
$vend->sku($upc);
$vend->brand($prod->brand());
$vend->description($prod->description());
$vend->cost($prod->cost());
$vend->saleCost(0);
$vend->vendorDept(0);
$vend->units(1);
$vend->size($prod->size() . $prod->unitofmeasure());
$vend->save();
}
}
private function autoPopulate($vendorID)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$query = '
SELECT p.upc,
p.upc AS sku,
p.brand,
p.description,
p.size,
p.unitofmeasure,
p.cost,
0.00 AS saleCost,
0 AS vendorDept
FROM products AS p
INNER JOIN vendors AS v ON p.default_vendor_id=v.vendorID
WHERE v.vendorID=?
AND p.upc NOT IN (
SELECT upc FROM vendorItems WHERE vendorID=?
) AND p.upc NOT IN (
SELECT upc FROM vendorSKUtoPLU WHERE vendorID=?
)';
$prep = $dbc->prepare($query);
$args = array($vendorID, $vendorID, $vendorID);
$result = $dbc->execute($prep, $args);
$item = new VendorItemsModel($dbc);
while ($row = $dbc->fetch_row($result)) {
$item->vendorID($vendorID);
$item->upc($row['upc']);
$item->sku($row['sku']);
$item->brand($row['brand']);
$item->description($row['description']);
$item->units(1);
$item->size($row['size'] . $row['unitofmeasure']);
$item->cost($row['cost']);
$item->saleCost(0);
$item->vendorDept(0);
$item->save();
}
}
public function post_id_handler()
{
$dbc = FannieDB::get($this->config->get('OP_DB'));
$ret = array('error' => false);
$date = FormLib::get('order-date', date('Y-m-d'));
$po_num = FormLib::get('po-number');
$inv_num = FormLib::get('inv-number');
$sku = FormLib::get('sku', array());
$upc = FormLib::get('upc', array());
$cases = FormLib::get('cases', array());
$caseSize = FormLib::get('case-size', array());
$total = FormLib::get('total', array());
$brand = FormLib::get('brand', array());
$description = FormLib::get('description', array());
if (count($sku) == 0) {
$ret['error'] = true;
$ret['message'] = 'Order must have at least one item';
echo json_encode($ret);
return false;
}
/**
Queries to check for vendorItems entries
*/
$skuP = $dbc->prepare('
SELECT size
FROM vendorItems
WHERE vendorID=?
AND sku=?');
$upcP = $dbc->prepare('
SELECT size
FROM vendorItems
WHERE vendorID=?
AND upc=?');
$vitem = new VendorItemsModel($dbc);
/**
Create parent record for the order
*/
$po = new PurchaseOrderModel($dbc);
$po->vendorID($this->id);
$po->creationDate($date);
$po->placed(1);
$po->placedDate($date);
$po->userID(FannieAuth::getUID());
$po->vendorOrderID($po_num);
$po->vendorInvoiceID($inv_num);
// if an orderID is supplied, update the existing order
if (FormLib::get('order-id') !== '' && is_numeric(FormLib::get('order-id'))) {
$orderID = FormLib::get('order-id');
$po->orderID($orderID);
$po->save();
} else {
$orderID = $po->save();
}
if (!$orderID) {
$ret['error'] = true;
$ret['message'] = 'Could not create new order';
echo json_encode($ret);
return false;
}
/**
Create item records for the order
*/
$pitem = new PurchaseOrderItemsModel($dbc);
for ($i = 0; $i < count($sku); $i++) {
$pitem->reset();
$pitem->orderID($orderID);
$pitem->sku($sku[$i]);
$units = $caseSize[$i];
$qty = $cases[$i];
$unitCost = $total[$i] / $qty / $units;
/**
Multiple same-SKU records
Sum the quantities and costs to merge
into a single record
*/
if ($pitem->load()) {
$qty += $pitem->receivedQty();
$total[$i] += $pitem->receivedTotalCost();
}
$pitem->quantity($qty);
$pitem->caseSize($units);
$pitem->unitSize('');
$pitem->unitCost($unitCost);
$pitem->receivedDate($date);
$pitem->receivedQty($qty);
$pitem->receivedTotalCost($total[$i]);
$pitem->brand($brand[$i]);
$pitem->description($description[$i]);
$pitem->internalUPC($upc[$i]);
/**
Try to look up unit size using
vendorID+sku or vendorID+upc.
This avoids making unit size a required
field *and* checks for an existing
vendorItems record
*/
$size = false;
$skuR = $dbc->execute($skuP, array($this->id, $sku[$i]));
if ($skuR && $dbc->numRows($skuR)) {
$size = true;
$w = $dbc->fetchRow($skuR);
$pitem->unitSize($w['size']);
}
if ($size === false) {
$upcR = $dbc->execute($upcP, array($this->id, $upc[$i]));
if ($upcR && $dbc->numRows($upcR)) {
$size = true;
$w = $dbc->fetchRow($upcR);
$pitem->unitSize($w['size']);
}
}
$pitem->save();
/**
If no vendorItems record exists for this
SKU or UPC then create one
*/
if ($size === false) {
$vitem->reset();
$vitem->vendorID($this->id);
$vitem->sku($sku[$i]);
$vitem->upc($upc[$i]);
$vitem->brand($brand[$i]);
$vitem->description($description[$i]);
$vitem->size('');
$vitem->units($qty);
$vitem->cost($unitCost);
$vitem->saleCost(0.0);
$vitem->vendorDept(0);
$vitem->save();
}
}
$ret['order_id'] = $orderID;
echo json_encode($ret);
return false;
}
