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; }
/** Helper: create a vendorItems record for an existing product if one does not exist */ public function createIfMissing($upc, $vendorID) { // look for entry directly by UPC or via SKU mapping $findP = $this->connection->prepare(' SELECT v.upc FROM vendorItems AS v LEFT JOIN vendorSKUtoPLU AS m ON v.vendorID=m.vendorID AND v.sku=m.sku WHERE v.vendorID=? AND (v.upc=? OR m.upc=?)'); $findR = $this->connection->execute($findP, array($vendorID, $upc, $upc)); if ($this->connection->num_rows($findR) == 0) { // create item from product $prod = new ProductsModel($this->connection); $prod->upc($upc); $prod->load(); $vend = new VendorItemsModel($this->connection); $vend->vendorID($vendorID); $vend->upc($upc); $vend->sku($upc); $vend->brand($prod->brand()); $vend->description($prod->description()); $vend->cost($prod->cost()); $vend->saleCost(0); $vend->vendorDept(0); $vend->units(1); $vend->size($prod->size() . $prod->unitofmeasure()); $vend->save(); } }
private function autoPopulate($vendorID) { global $FANNIE_OP_DB; $dbc = FannieDB::get($FANNIE_OP_DB); $query = ' SELECT p.upc, p.upc AS sku, p.brand, p.description, p.size, p.unitofmeasure, p.cost, 0.00 AS saleCost, 0 AS vendorDept FROM products AS p INNER JOIN vendors AS v ON p.default_vendor_id=v.vendorID WHERE v.vendorID=? AND p.upc NOT IN ( SELECT upc FROM vendorItems WHERE vendorID=? ) AND p.upc NOT IN ( SELECT upc FROM vendorSKUtoPLU WHERE vendorID=? )'; $prep = $dbc->prepare($query); $args = array($vendorID, $vendorID, $vendorID); $result = $dbc->execute($prep, $args); $item = new VendorItemsModel($dbc); while ($row = $dbc->fetch_row($result)) { $item->vendorID($vendorID); $item->upc($row['upc']); $item->sku($row['sku']); $item->brand($row['brand']); $item->description($row['description']); $item->units(1); $item->size($row['size'] . $row['unitofmeasure']); $item->cost($row['cost']); $item->saleCost(0); $item->vendorDept(0); $item->save(); } }
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; }