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;
}
function process_file($linedata)
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$skuCol = $this->get_column_index('sku');
$costCol = $this->get_column_index('cost');
$uQtyCol = $this->get_column_index('unitQty');
$cQtyCol = $this->get_column_index('caseQty');
$uSizeCol = $this->get_column_index('unitSize');
$cSizeCol = $this->get_column_index('caseSize');
$brandCol = $this->get_column_index('brand');
$descCol = $this->get_column_index('desc');
$upcCol = $this->get_column_index('upc');
$upccCol = $this->get_column_index('upcc');
$vendorID = FormLib::get('vendorID');
$inv = FormLib::get('identifier', '');
$orderDate = FormLib::get('orderDate', date('Y-m-d H:i:s'));
$recvDate = FormLib::get('recvDate', '');
$order = new PurchaseOrderModel($dbc);
$order->vendorID($vendorID);
$order->creationDate($orderDate);
$order->placedDate($orderDate);
$order->placed(1);
$order->userID(FannieAuth::getUID());
$order->vendorOrderID($inv);
$order->vendorInvoiceID($inv);
$orderID = $order->save();
$item = new PurchaseOrderItemsModel($dbc);
$info = new VendorItemsModel($dbc);
$ret = '';
foreach ($linedata as $line) {
if (!isset($line[$skuCol])) {
continue;
}
if (!isset($line[$costCol])) {
continue;
}
$sku = $line[$skuCol];
$cost = $line[$costCol];
$cost = trim($cost, ' ');
$cost = trim($cost, '$');
if (!is_numeric($cost)) {
$ret .= "<i>Omitting item {$sku}. Cost {$cost} isn't a number</i><br />";
continue;
}
$unitQty = $uQtyCol !== false && isset($line[$uQtyCol]) ? $line[$uQtyCol] : 0;
$caseQty = $cQtyCol !== false && isset($line[$cQtyCol]) ? $line[$cQtyCol] : 0;
if ($unitQty == 0 && $caseQty == 0) {
// no qty specified.
continue;
}
$unitSize = $uSizeCol !== false && isset($line[$uSizeCol]) ? $line[$uSizeCol] : 0;
$caseSize = $cSizeCol !== false && isset($line[$cSizeCol]) ? $line[$cSizeCol] : 0;
$brand = $brandCol !== '' && isset($line[$brandCol]) ? $line[$brandCol] : '';
$desc = $descCol !== false && isset($line[$descCol]) ? $line[$descCol] : '';
$upc = '';
if ($upcCol !== false && isset($line[$upcCol])) {
$upc = BarcodeLib::padUPC($line[$upcCol]);
} elseif ($upccCol !== false && isset($line[$upccCol])) {
$upc = BarcodeLib::padUPC($line[$upccCol]);
$upc = '0' . substr($upc, 0, 12);
}
$info->reset();
$info->vendorID($vendorID);
$info->sku($sku);
if ($info->load()) {
if ($brand === '') {
$brand = $info->brand();
}
if ($desc === '') {
$desc = $info->description();
}
if ($unitSize === 0) {
$unitSize = $info->size();
}
if ($caseSize === 0) {
$caseSize = $info->units();
}
$upc = $info->upc();
}
if ($caseQty == 0 && $unitQty != 0) {
if ($caseSize == 0) {
$caseQty = $unitQty;
$caseSize = 1;
} else {
$caseQty = $unitQty / $caseSize;
}
} elseif ($caseQty != 0 && $unitQty == 0) {
if ($caseSize == 0) {
$unitQty = $caseQty;
$caseSize = 1;
} else {
$unitQty = $caseQty * $caseSize;
}
} elseif ($caseQty != 0 && $unitQty != 0) {
if ($caseSize == 0) {
$caseSize = $caseQty / $unitQty;
}
}
$unitCost = $cost / $unitQty;
$item->orderID($orderID);
$item->sku($sku);
if ($item->load()) {
// multiple records for same item
$item->quantity($caseQty + $item->quantity());
if ($recvDate !== '') {
$item->receivedTotalCost($cost + $item->receivedTotalCost());
$item->receivedQty($caseQty + $item->receivedQty());
$item->receivedDate($recvDate);
}
} else {
$item->quantity($caseQty);
if ($recvDate !== '') {
$item->receivedTotalCost($cost);
$item->receivedQty($caseQty);
$item->receivedDate($recvDate);
}
}
$item->unitCost($unitCost);
$item->caseSize($caseSize);
$item->brand($brand);
$item->description($desc);
$item->internalUPC($upc);
$item->save();
}
$ret .= "<p>Import Complete";
$ret .= '<br />';
$ret .= '<a href="' . $this->config->get('URL') . 'purchasing/ViewPurchaseOrders.php?id=' . $orderID . '">View Order</a></p>';
$this->results = $ret;
return true;
}
private static function parseItem($line, $vendorID)
{
global $FANNIE_OP_DB;
$UPC = 3;
$SKU = 4;
$RECVQTY = 5;
$CASESIZE = 6;
$UNITSIZE = 7;
$BRAND = 8;
$DESCRIPTION = 9;
$TOTALCOST = 13;
$UNITCOST = 15;
$ORDERQTY = 18;
// remove non-digits and check digits
// then pad to length
$upc = str_replace('-', '', $line[$UPC]);
$upc = str_replace(' ', '', $upc);
$upc = substr($upc, 0, strlen($upc) - 1);
$upc = BarcodeLib::padUPC($upc);
$caseSize = $line[$CASESIZE];
// invoice does not include proper case size
// try to find actual size in vendorItems table
// via SKU or UPC
if (strtoupper($caseSize) == 'CS') {
$dbc = FannieDB::get($FANNIE_OP_DB);
$vmodel = new VendorItemsModel($dbc);
$vmodel->sku($line[$SKU]);
$vmodel->vendorID($vendorID);
$vmodel->load();
if ($vmodel->units() != '') {
$caseSize = $vmodel->units();
} else {
$vmodel->reset();
$vmodel->upc($upc);
$vmodel->vendorID($vendorID);
foreach ($vmodel->find() as $item) {
if ($item->units() != '') {
$caseSize = $item->units();
}
}
}
}
// never found a valid size
if (!is_numeric($caseSize)) {
$caseSize = 1;
}
return array('sku' => $line[$SKU], 'quantity' => $line[$ORDERQTY], 'unitCost' => $line[$UNITCOST], 'caseSize' => $caseSize, 'receivedQty' => $line[$RECVQTY], 'receivedTotalCost' => $line[$TOTALCOST], 'unitSize' => $line[$UNITSIZE], 'brand' => $line[$BRAND], 'description' => $line[$DESCRIPTION], 'upc' => $upc);
}
/**
Receiving AJAX callback.
Lookup item in the order and display form fields
to enter required info
*/
public function get_id_sku_handler()
{
$dbc = $this->connection;
$model = new PurchaseOrderItemsModel($dbc);
$model->orderID($this->id);
$model->sku($this->sku);
// lookup by SKU but if nothing is found
// try using the value as a UPC instead
$found = false;
if ($model->load()) {
$found = true;
} else {
$model->reset();
$model->orderID($this->id);
$model->internalUPC(BarcodeLib::padUPC($this->sku));
$matches = $model->find();
if (count($matches) == 1) {
$model = $matches[0];
$found = true;
}
}
// item not in order. need all fields to add it.
if (!$found) {
echo '<div class="alert alert-danger">SKU not found in order</div>';
echo '<form onsubmit="saveReceive(); return false;">';
echo '<table class="table table-bordered">';
echo '<tr><th>SKU</th><th>UPC</th><th>Brand</th><th>Description</th>
<th>Qty Ordered</th><th>Cost (est)</th><th>Qty Received</th><th>Cost Received</th></tr>';
$order = new PurchaseOrderModel($dbc);
$order->orderID($this->id);
$order->load();
$item = new VendorItemsModel($dbc);
$item->vendorID($order->vendorID());
$item->sku($this->sku);
$item->load();
printf('<tr>
<td>%s<input type="hidden" name="sku" value="%s" /></td>
<td><input type="text" class="form-control" name="upc" value="%s" /></td>
<td><input type="text" class="form-control" name="brand" value="%s" /></td>
<td><input type="text" class="form-control" name="description" value="%s" /></td>
<td><input type="text" class="form-control" name="orderQty" value="%s" /></td>
<td><input type="text" class="form-control" name="orderCost" value="%.2f" /></td>
<td><input type="text" class="form-control" name="receiveQty" value="%s" /></td>
<td><input type="text" class="form-control" name="receiveCost" value="%.2f" /></td>
<td><button type="submit" class="btn btn-default">Add New Item</button><input type="hidden" name="id" value="%d" /></td>
</tr>', $this->sku, $this->sku, $item->upc(), $item->brand(), $item->description(), 1, $item->cost() * $item->units(), 0, 0, $this->id);
echo '</table>';
echo '</form>';
} else {
// item in order. just need received qty and cost
echo '<form onsubmit="saveReceive(); return false;">';
echo '<table class="table table-bordered">';
echo '<tr><th>SKU</th><th>UPC</th><th>Brand</th><th>Description</th>
<th>Qty Ordered</th><th>Cost (est)</th><th>Qty Received</th><th>Cost Received</th></tr>';
if ($model->receivedQty() === null) {
$model->receivedQty($model->quantity());
}
if ($model->receivedTotalCost() === null) {
$model->receivedTotalCost($model->quantity() * $model->unitCost() * $model->caseSize());
}
printf('<tr>
<td>%s<input type="hidden" name="sku" value="%s" /></td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%s</td>
<td>%.2f</td>
<td><input type="text" class="form-control" name="qty" value="%s" /></td>
<td><input type="text" class="form-control" name="cost" value="%.2f" /></td>
<td><button type="submit" class="btn btn-default">Save</button><input type="hidden" name="id" value="%d" /></td>
</tr>', $this->sku, $this->sku, $model->internalUPC(), $model->brand(), $model->description(), $model->quantity(), $model->quantity() * $model->unitCost() * $model->caseSize(), $model->receivedQty(), $model->receivedTotalCost(), $this->id);
echo '</table>';
echo '</form>';
}
return false;
}
/**
AJAX call: ?id=<vendor ID>&sku=<vendor SKU>&qty=<# of cases>
Add the given SKU & qty to the order
*/
function get_id_sku_qty_handler()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$orderID = $this->getOrderID($this->id, FannieAuth::getUID($this->current_user));
$vitem = new VendorItemsModel($dbc);
$vitem->vendorID($this->id);
$vitem->sku($this->sku);
$vitem->load();
$pitem = new PurchaseOrderItemsModel($dbc);
$pitem->orderID($orderID);
$pitem->sku($this->sku);
$pitem->quantity($this->qty);
$pitem->unitCost($vitem->cost());
$pitem->caseSize($vitem->units());
$pitem->unitSize($vitem->size());
$pitem->brand($vitem->brand());
$pitem->description($vitem->description());
$pitem->internalUPC($vitem->upc());
$pitem->save();
$ret = array();
$pitem->reset();
$pitem->orderID($orderID);
$pitem->sku($this->sku);
if (count($pitem->find()) == 0) {
$ret['error'] = 'Error saving entry';
} else {
$ret['sidebar'] = $this->calculate_sidebar();
}
echo json_encode($ret);
return False;
}
/**
AJAX call: ?id=<vendor ID>&sku=<vendor SKU>&qty=<# of cases>
Add the given SKU & qty to the order
*/
function get_id_sku_qty_handler()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$orderID = $this->getOrderID($this->id, FannieAuth::getUID($this->current_user));
$vitem = new VendorItemsModel($dbc);
$vitem->vendorID($this->id);
$vitem->sku($this->sku);
$vitem->load();
$pitem = new PurchaseOrderItemsModel($dbc);
$pitem->orderID($orderID);
$pitem->sku($this->sku);
if ($this->qty == 0) {
$pitem->delete();
} else {
$pitem->quantity($this->qty);
$pitem->unitCost($vitem->cost());
$pitem->caseSize($vitem->units());
$pitem->unitSize($vitem->size());
$pitem->brand($vitem->brand());
$pitem->description($vitem->description());
$pitem->internalUPC($vitem->upc());
$pitem->save();
}
$ret = array();
$pitem->reset();
$pitem->orderID($orderID);
$pitem->sku($this->sku);
if (count($pitem->find()) == 0 && $this->qty != 0) {
$ret['error'] = 'Error saving entry';
} else {
$q = 'SELECT count(*) as rows,
SUM(unitCost*caseSize*quantity) as estimatedCost
FROM PurchaseOrderItems WHERE orderID=?';
$p = $dbc->prepare_statement($q);
$r = $dbc->exec_statement($p, array($orderID));
$w = $dbc->fetch_row($r);
$ret['count'] = $w['rows'];
$ret['cost'] = sprintf('%.2f', $w['estimatedCost']);
}
echo json_encode($ret);
return False;
}
