/**
Lots of options on this report.
*/
function fetch_report_data()
{
global $FANNIE_OP_DB;
$date1 = FormLib::get_form_value('date1', date('Y-m-d'));
$date2 = FormLib::get_form_value('date2', date('Y-m-d'));
$dbc = FannieDB::get($FANNIE_OP_DB);
$mustCodeP = $dbc->prepare('
SELECT i.orderID,
i.sku
FROM PurchaseOrderItems AS i
WHERE i.receivedDate BETWEEN ? AND ?
AND (i.salesCode IS NULL OR i.salesCode=0)
');
$codeR = $dbc->execute($mustCodeP, array($date1 . ' 00:00:00', $date2 . ' 23:59:59'));
$model = new PurchaseOrderItemsModel($dbc);
while ($w = $dbc->fetchRow($codeR)) {
$model->orderID($w['orderID']);
$model->sku($w['sku']);
if ($model->load()) {
$code = $model->guessCode();
$model->salesCode($code);
$model->save();
}
}
$accounting = $this->config->get('ACCOUNTING_MODULE');
if (!class_exists($accounting)) {
$accounting = '\\COREPOS\\Fannie\\API\\item\\Accounting';
}
$codingQ = 'SELECT o.orderID,
o.salesCode,
i.vendorInvoiceID,
SUM(o.receivedTotalCost) as rtc,
MAX(o.receivedDate) AS rdate
FROM PurchaseOrderItems AS o
LEFT JOIN PurchaseOrder as i ON o.orderID=i.orderID
WHERE i.vendorID=?
AND i.userID=0
AND o.receivedDate BETWEEN ? AND ?
GROUP BY o.orderID, o.salesCode, i.vendorInvoiceID
ORDER BY rdate, i.vendorInvoiceID, o.salesCode';
$codingP = $dbc->prepare($codingQ);
$report = array();
$invoice_sums = array();
$vendorID = FormLib::get('vendorID');
$codingR = $dbc->execute($codingP, array($vendorID, $date1 . ' 00:00:00', $date2 . ' 23:59:59'));
$orders = array();
while ($codingW = $dbc->fetch_row($codingR)) {
if ($codingW['rtc'] == 0) {
// skip zero lines (tote charges)
continue;
}
$code = $accounting::toPurchaseCode($codingW['salesCode']);
if (empty($code) && $this->report_format == 'html') {
$code = 'n/a';
}
$record = array('UNFI', '<a href="../ViewPurchaseOrders.php?id=' . $codingW['orderID'] . '">' . $codingW['vendorInvoiceID'] . '</a>', $codingW['rdate'], 0.0, sprintf('%.2f', $codingW['rtc']), $code);
if (!isset($invoice_sums[$codingW['vendorInvoiceID']])) {
$invoice_sums[$codingW['vendorInvoiceID']] = 0;
}
$invoice_sums[$codingW['vendorInvoiceID']] += $codingW['rtc'];
if (!isset($orders[$codingW['orderID']])) {
$orders[$codingW['orderID']] = array();
}
$orders[$codingW['orderID']][] = $record;
}
$po = new PurchaseOrderModel($dbc);
foreach ($orders as $id => $data) {
$invTTL = 0;
for ($i = 0; $i < count($data); $i++) {
$row = $data[$i];
$invTTL += $row[4];
}
foreach ($orders[$id] as $row) {
$row[3] = $invTTL;
$report[] = $row;
}
}
/*
for ($i=0; $i<count($report); $i++) {
$inv = $report[$i][1];
$report[$i][3] = sprintf('%.2f', $invoice_sums[$inv]);
}
*/
return $report;
}
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;
}
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;
}
/**
Create purchase orders from zipfile
@param $zipfile filename
@param $vendorID integer vendor ID
@param $repeat this date has been previously imported
*/
public static function import($zipfile, $vendorID, $repeat = false)
{
global $FANNIE_OP_DB;
$za = new ZipArchive();
$try = $za->open($zipfile);
if ($try !== true) {
// invalid file
return $try;
}
$dbc = FannieDB::get($FANNIE_OP_DB);
$create = $dbc->prepare('INSERT INTO PurchaseOrder (vendorID, creationDate, placed,
placedDate, userID, vendorOrderID, vendorInvoiceID) VALUES
(?, ?, 1, ?, 0, ?, ?)');
$find = $dbc->prepare('SELECT orderID FROM PurchaseOrder WHERE vendorID=? AND userID=0
AND vendorInvoiceID=?');
$plu = $dbc->prepare('SELECT upc FROM vendorSKUtoPLU WHERE vendorID=? AND sku LIKE ?');
$clear = $dbc->prepare('DELETE FROM PurchaseOrderItems WHERE orderID=?');
for ($i = 0; $i < $za->numFiles; $i++) {
$info = $za->statIndex($i);
if (substr(strtolower($info['name']), -4) != '.csv') {
// skip non-csv file
continue;
}
$fp = $za->getStream($info['name']);
$header_info = array();
$item_info = array();
while (!feof($fp)) {
$line = fgetcsv($fp);
if (strtolower($line[0]) == 'header') {
$header_info = self::parseHeader($line);
} else {
if (strtolower($line[0]) == 'detail') {
$item = self::parseItem($line, $vendorID);
$item_info[] = $item;
}
}
}
if (count($item_info) > 0) {
$id = false;
// check whether order already exists
$idR = $dbc->execute($find, array($vendorID, $header_info['vendorInvoiceID']));
if ($dbc->num_rows($idR) > 0) {
$idW = $dbc->fetch_row($idR);
$id = $idW['orderID'];
$dbc->execute($clear, array($id));
}
if (!$id) {
// date has not been downloaded before OR
// date previously did not include this invoice
$dbc->execute($create, array($vendorID, $header_info['placedDate'], $header_info['placedDate'], $header_info['vendorOrderID'], $header_info['vendorInvoiceID']));
$id = $dbc->insert_id();
}
foreach ($item_info as $item) {
$model = new PurchaseOrderItemsModel($dbc);
$model->orderID($id);
$model->sku($item['sku']);
if ($model->load()) {
// sometimes an invoice contains multiple
// lines with the same product SKU
// sum those so the single record in
// PurchaseOrderItems is correct
$item['quantity'] += $model->quantity();
$item['receivedQty'] += $model->receivedQty();
$item['receivedTotalCost'] += $model->receivedTotalCost();
}
$model->quantity($item['quantity']);
$model->receivedQty($item['receivedQty']);
$model->receivedTotalCost($item['receivedTotalCost']);
$model->unitCost($item['unitCost']);
$model->caseSize($item['caseSize']);
$model->receivedDate($header_info['receivedDate']);
$model->unitSize($item['unitSize']);
$model->brand($item['brand']);
$model->description($item['description']);
$model->internalUPC($item['upc']);
$pluCheck = $dbc->execute($plu, array($vendorID, $item['sku']));
if ($dbc->num_rows($pluCheck) > 0) {
$pluInfo = $dbc->fetch_row($pluCheck);
$model->internalUPC($pluInfo['upc']);
}
if ($model->salesCode() == '') {
$code = $model->guessCode();
$model->salesCode($code);
}
$model->save();
}
}
}
return true;
}
/**
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;
}
function get_id_sku_index_handler()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$orderID = $this->getOrderID($this->id, FannieAuth::getUID($this->current_user));
$ret = array('qty' => 0, 'index' => $this->index);
$item = new PurchaseOrderItemsModel($dbc);
$item->orderID($orderID);
$item->sku($this->sku);
if ($item->load()) {
$ret['qty'] = $item->quantity();
}
echo json_encode($ret);
return false;
}
