/** 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; }
/** 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; }
public function post_id_sku_recode_handler() { $dbc = $this->connection; $dbc->selectDB($this->config->get('OP_DB')); $model = new PurchaseOrderItemsModel($dbc); $model->orderID($this->id); for ($i = 0; $i < count($this->sku); $i++) { if (!isset($this->recode[$i])) { continue; } $model->sku($this->sku[$i]); $model->salesCode($this->recode[$i]); $model->save(); } return $_SERVER['PHP_SELF'] . '?id=' . $this->id; }