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