public function post_vendorID_invoice_num_po_num_invoice_date_handler() { $dbc = FannieDB::get($this->config->get('OP_DB')); $skus = FormLib::get('sku', array()); $upcs = FormLib::get('upc', array()); $descriptions = FormLib::get('desc', array()); $cases = FormLib::get('qty', array()); $units = FormLib::get('units', array()); $sizes = FormLib::get('size', array()); $costs = FormLib::get('unitCost', array()); $totals = FormLib::get('totalCost', array()); $order = new PurchaseOrderModel($dbc); $order->vendorID($this->vendorID); $order->creationDate($this->invoice_date); $order->placed(1); $order->placedDate($this->invoice_date); $order->vendorOrderID($this->po_num); $order->vendorInvoiceID($this->invoice_num); $order->userID(0); $orderID = $order->save(); $checkP = $dbc->prepare(' SELECT v.sku FROM vendorItems AS v WHERE v.vendorID=? AND v.upc <> \'0000000000000\' AND v.upc <> \'\' AND v.upc IS NOT NULL AND v.sku=?'); $vendorItem = new VendorItemsModel($dbc); $item = new PurchaseOrderItemsModel($dbc); for ($i = 0; $i < count($skus); $i++) { $sku = $skus[$i]; $upc = BarcodeLib::padUPC(isset($upcs[$i]) ? $upcs[$i] : ''); $qty = isset($cases[$i]) ? $cases[$i] : 1; $caseSize = isset($units[$i]) ? $units[$i] : 1; $unitSize = isset($sizes[$i]) ? $sizes[$i] : ''; $unitCost = isset($costs[$i]) ? $costs[$i] : 0; $totalCost = isset($totals[$i]) ? $totals[$i] : 0; $desc = isset($descriptions[$i]) ? substr($descriptions[$i], 0, 50) : ''; $item->reset(); $item->orderID($orderID); $item->sku($sku); $item->quantity($qty); $item->unitCost($unitCost); $item->caseSize($caseSize); $item->receivedDate($this->invoice_date); $item->receivedQty($qty); $item->receivedTotalCost($totalCost); $item->unitSize($unitSize); $item->brand(''); $item->description($desc); $item->internalUPC($upc); $item->save(); /** Add entries to vendor catalog if they don't exist */ $checkR = $dbc->execute($checkP, array($this->vendorID, $sku)); if ($checkR && $dbc->numRows($checkR) == 0) { $vendorItem->vendorID($this->vendorID); $vendorItem->sku($sku); $vendorItem->upc($upc); $vendorItem->description($desc); $vendorItem->brand(''); $vendorItem->units($caseSize); $vendorItem->size($unitSize); $vendorItem->cost($unitCost); $vendorItem->vendorDept(0); $vendorItem->save(); } } header('Location: ' . filter_input(INPUT_SERVER, 'PHP_SELF') . '?complete=' . $orderID); return false; }
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; }
/** 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; }
/** 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; }
/** 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; }