public function get_id_break_handler() { $dbc = FannieDB::get($this->config->get('OP_DB')); $model = new VendorBreakdownsModel($dbc); $model->vendorID($this->id); $original = new VendorItemsModel($dbc); $product = new ProductsModel($dbc); foreach ($model->find() as $obj) { $original->vendorID($this->id); $original->sku($obj->sku()); if (!$original->load()) { $this->addOnloadCommand("showBootstrapAlert('#alert-area', 'danger', 'Vendor SKU #" . $obj->sku() . " not found');\n"); continue; } $split_factor = false; $unit_size = ''; if (preg_match('/^\\d+$/', $original->size())) { $split_factor = $original->size(); } elseif (preg_match('/(\\d+)\\s*\\/\\s*(.+)/', $original->size(), $matches)) { $split_factor = $matches[1]; $unit_size = $matches[2]; } elseif (preg_match('/(\\d+)\\s*CT/', $original->size(), $matches)) { $split_factor = $matches[1]; } elseif (preg_match('/(\\d+)\\s*PKT/', $original->size(), $matches)) { $split_factor = $matches[1]; } if (!$split_factor) { $this->addOnloadCommand("showBootstrapAlert('#alert-area', 'danger', 'Vendor SKU #" . $original->size() . " cannot be broken down');\n"); continue; } // add an entry using the store UPC/PLU in place of the vendor SKU // since two records from the same vendor with same SKU are not // permitted in the table $original->sku($obj->upc()); $original->upc($obj->upc()); $original->units(1); $original->size($unit_size); $original->cost($original->cost() / $split_factor); $original->saleCost($original->saleCost() / $split_factor); if ($original->save()) { // update cost in products table, too $product->reset(); $product->upc($obj->upc()); foreach ($product->find('store_id') as $p) { if ($p->load() && $p->default_vendor_id() == $this->id) { $p->cost($original->cost()); $p->save(); $original->description($p->description()); $original->save(); } } } else { $this->addOnloadCommand("showBootstrapAlert('#alert-area', 'success', 'Error saving vendor SKU #" . $obj->sku() . "');\n"); } } return true; }
/** 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; }