function export_order($id) { global $FANNIE_OP_DB, $FANNIE_ROOT; $dbc = FannieDB::get($FANNIE_OP_DB); $order = new PurchaseOrderModel($dbc); $order->orderID($id); $order->load(); $items = new PurchaseOrderItemsModel($dbc); $items->orderID($id); $vendor = new VendorsModel($dbc); $vendor->vendorID($order->vendorID()); $vendor->load(); $contact = new VendorContactModel($dbc); $contact->vendorID($order->vendorID()); $contact->load(); if (!class_exists('FPDF')) { include_once $FANNIE_ROOT . 'src/fpdf/fpdf.php'; } $pdf = new FPDF('P', 'mm', 'Letter'); $pdf->AddPage(); $pdf->SetFont('Arial', '', '12'); $pdf->Cell(100, 5, 'Vendor: ' . $vendor->vendorName(), 0, 0); $pdf->Cell(100, 5, 'Date: ' . date('Y-m-d'), 0, 0); $pdf->Ln(); $pdf->Cell(100, 5, 'Phone: ' . $contact->phone(), 0, 0); $pdf->Cell(100, 5, 'Fax: ' . $contact->fax(), 0, 0); $pdf->Ln(); $pdf->Cell(100, 5, 'Email: ' . $contact->email(), 0, 0); $pdf->Cell(100, 5, 'Website: ' . $contact->website(), 0, 0); $pdf->Ln(); $pdf->MultiCell(0, 5, "Ordering Info:\n" . $contact->notes(), 'B'); $pdf->Ln(); $cur_page = 0; $pdf->SetFontSize(10); foreach ($items->find() as $obj) { if ($cur_page != $pdf->PageNo()) { $cur_page = $pdf->PageNo(); $pdf->Cell(25, 5, 'SKU', 0, 0); $pdf->Cell(20, 5, 'Order Qty', 0, 0); $pdf->Cell(30, 5, 'Brand', 0, 0); $pdf->Cell(65, 5, 'Description', 0, 0); $pdf->Cell(20, 5, 'Case Size', 0, 0); $pdf->Cell(20, 5, 'Est. Cost', 0, 0); $pdf->Ln(); } $pdf->Cell(25, 5, $obj->sku(), 0, 0); $pdf->Cell(20, 5, $obj->quantity(), 0, 0, 'C'); $pdf->Cell(30, 5, $obj->brand(), 0, 0); $pdf->Cell(65, 5, $obj->description(), 0, 0); $pdf->Cell(20, 5, $obj->caseSize(), 0, 0, 'C'); $pdf->Cell(20, 5, sprintf('%.2f', $obj->caseSize() * $obj->unitCost() * $obj->quantity()), 0, 0); $pdf->Ln(); } $pdf->Output('order_export.pdf', 'D'); }
function export_order($id) { global $FANNIE_OP_DB; $dbc = FannieDB::get($FANNIE_OP_DB); $order = new PurchaseOrderModel($dbc); $order->orderID($id); $order->load(); $items = new PurchaseOrderItemsModel($dbc); $items->orderID($id); $vendor = new VendorsModel($dbc); $vendor->vendorID($order->vendorID()); $vendor->load(); echo 'Vendor,"' . $vendor->vendorName() . '",Order Date,' . date('Y-m-d') . "\r\n"; echo "\r\n"; echo "SKU,\"Order Qty\",Brand,Description,\"Case Size\",\"Est. Cost\"\r\n"; foreach ($items->find() as $obj) { echo $obj->sku() . ','; echo $obj->quantity() . ','; echo '"' . $obj->brand() . '",'; echo '"' . $obj->description() . '",'; echo '"' . $obj->caseSize() . '",'; printf('%.2f', $obj->unitCost() * $obj->caseSize() * $obj->quantity()); echo "\r\n"; } }
public function guessCode() { $dbc = $this->connection; // case 1: item exists in products $deptP = $dbc->prepare(' SELECT d.salesCode FROM products AS p INNER JOIN departments AS d ON p.department=d.dept_no WHERE p.upc=?'); $deptR = $dbc->execute($deptP, array($this->internalUPC())); if ($dbc->numRows($deptR)) { $w = $dbc->fetchRow($deptR); return $w['salesCode']; } $order = new PurchaseOrderModel($dbc); $order->orderID($this->orderID()); $order->load(); // case 2: item is SKU-mapped but the order record // does not reflect the internal PLU $deptP = $dbc->prepare(' SELECT d.salesCode FROM vendorSKUtoPLU AS v ' . DTrans::joinProducts('v', 'p', 'INNER') . ' INNER JOIN departments AS d ON p.department=d.dept_no WHERE v.sku=? AND v.vendorID=?'); $deptR = $dbc->execute($deptP, array($this->sku(), $order->vendorID())); if ($dbc->numRows($deptR)) { $w = $dbc->fetchRow($deptR); return $w['salesCode']; } // case 3: item is not normally carried but is in a vendor catalog // that has vendor => POS department mapping $deptP = $dbc->prepare(' SELECT d.salesCode FROM vendorItems AS v INNER JOIN vendorDepartments AS z ON v.vendorDept=z.deptID AND v.vendorID=z.vendorID INNER JOIN departments AS d ON z.posDeptID=d.dept_no WHERE v.sku=? AND v.vendorID=?'); $deptR = $dbc->execute($deptP, array($this->sku(), $order->vendorID())); if ($dbc->numRows($deptR)) { $w = $dbc->fetchRow($deptR); return $w['salesCode']; } return false; }
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; }
function process_file($linedata) { global $FANNIE_OP_DB; $dbc = FannieDB::get($FANNIE_OP_DB); $skuCol = $this->get_column_index('sku'); $costCol = $this->get_column_index('cost'); $uQtyCol = $this->get_column_index('unitQty'); $cQtyCol = $this->get_column_index('caseQty'); $uSizeCol = $this->get_column_index('unitSize'); $cSizeCol = $this->get_column_index('caseSize'); $brandCol = $this->get_column_index('brand'); $descCol = $this->get_column_index('desc'); $upcCol = $this->get_column_index('upc'); $upccCol = $this->get_column_index('upcc'); $vendorID = FormLib::get('vendorID'); $inv = FormLib::get('identifier', ''); $orderDate = FormLib::get('orderDate', date('Y-m-d H:i:s')); $recvDate = FormLib::get('recvDate', ''); $order = new PurchaseOrderModel($dbc); $order->vendorID($vendorID); $order->creationDate($orderDate); $order->placedDate($orderDate); $order->placed(1); $order->userID(FannieAuth::getUID()); $order->vendorOrderID($inv); $order->vendorInvoiceID($inv); $orderID = $order->save(); $item = new PurchaseOrderItemsModel($dbc); $info = new VendorItemsModel($dbc); $ret = ''; foreach ($linedata as $line) { if (!isset($line[$skuCol])) { continue; } if (!isset($line[$costCol])) { continue; } $sku = $line[$skuCol]; $cost = $line[$costCol]; $cost = trim($cost, ' '); $cost = trim($cost, '$'); if (!is_numeric($cost)) { $ret .= "<i>Omitting item {$sku}. Cost {$cost} isn't a number</i><br />"; continue; } $unitQty = $uQtyCol !== false && isset($line[$uQtyCol]) ? $line[$uQtyCol] : 0; $caseQty = $cQtyCol !== false && isset($line[$cQtyCol]) ? $line[$cQtyCol] : 0; if ($unitQty == 0 && $caseQty == 0) { // no qty specified. continue; } $unitSize = $uSizeCol !== false && isset($line[$uSizeCol]) ? $line[$uSizeCol] : 0; $caseSize = $cSizeCol !== false && isset($line[$cSizeCol]) ? $line[$cSizeCol] : 0; $brand = $brandCol !== '' && isset($line[$brandCol]) ? $line[$brandCol] : ''; $desc = $descCol !== false && isset($line[$descCol]) ? $line[$descCol] : ''; $upc = ''; if ($upcCol !== false && isset($line[$upcCol])) { $upc = BarcodeLib::padUPC($line[$upcCol]); } elseif ($upccCol !== false && isset($line[$upccCol])) { $upc = BarcodeLib::padUPC($line[$upccCol]); $upc = '0' . substr($upc, 0, 12); } $info->reset(); $info->vendorID($vendorID); $info->sku($sku); if ($info->load()) { if ($brand === '') { $brand = $info->brand(); } if ($desc === '') { $desc = $info->description(); } if ($unitSize === 0) { $unitSize = $info->size(); } if ($caseSize === 0) { $caseSize = $info->units(); } $upc = $info->upc(); } if ($caseQty == 0 && $unitQty != 0) { if ($caseSize == 0) { $caseQty = $unitQty; $caseSize = 1; } else { $caseQty = $unitQty / $caseSize; } } elseif ($caseQty != 0 && $unitQty == 0) { if ($caseSize == 0) { $unitQty = $caseQty; $caseSize = 1; } else { $unitQty = $caseQty * $caseSize; } } elseif ($caseQty != 0 && $unitQty != 0) { if ($caseSize == 0) { $caseSize = $caseQty / $unitQty; } } $unitCost = $cost / $unitQty; $item->orderID($orderID); $item->sku($sku); if ($item->load()) { // multiple records for same item $item->quantity($caseQty + $item->quantity()); if ($recvDate !== '') { $item->receivedTotalCost($cost + $item->receivedTotalCost()); $item->receivedQty($caseQty + $item->receivedQty()); $item->receivedDate($recvDate); } } else { $item->quantity($caseQty); if ($recvDate !== '') { $item->receivedTotalCost($cost); $item->receivedQty($caseQty); $item->receivedDate($recvDate); } } $item->unitCost($unitCost); $item->caseSize($caseSize); $item->brand($brand); $item->description($desc); $item->internalUPC($upc); $item->save(); } $ret .= "<p>Import Complete"; $ret .= '<br />'; $ret .= '<a href="' . $this->config->get('URL') . 'purchasing/ViewPurchaseOrders.php?id=' . $orderID . '">View Order</a></p>'; $this->results = $ret; return true; }
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; }
/** Receiving AJAX callback. Lookup item in the order and display form fields to enter required info */ public function get_id_sku_handler() { $dbc = $this->connection; $model = new PurchaseOrderItemsModel($dbc); $model->orderID($this->id); $model->sku($this->sku); // lookup by SKU but if nothing is found // try using the value as a UPC instead $found = false; if ($model->load()) { $found = true; } else { $model->reset(); $model->orderID($this->id); $model->internalUPC(BarcodeLib::padUPC($this->sku)); $matches = $model->find(); if (count($matches) == 1) { $model = $matches[0]; $found = true; } } // item not in order. need all fields to add it. if (!$found) { echo '<div class="alert alert-danger">SKU not found in order</div>'; echo '<form onsubmit="saveReceive(); return false;">'; echo '<table class="table table-bordered">'; echo '<tr><th>SKU</th><th>UPC</th><th>Brand</th><th>Description</th> <th>Qty Ordered</th><th>Cost (est)</th><th>Qty Received</th><th>Cost Received</th></tr>'; $order = new PurchaseOrderModel($dbc); $order->orderID($this->id); $order->load(); $item = new VendorItemsModel($dbc); $item->vendorID($order->vendorID()); $item->sku($this->sku); $item->load(); printf('<tr> <td>%s<input type="hidden" name="sku" value="%s" /></td> <td><input type="text" class="form-control" name="upc" value="%s" /></td> <td><input type="text" class="form-control" name="brand" value="%s" /></td> <td><input type="text" class="form-control" name="description" value="%s" /></td> <td><input type="text" class="form-control" name="orderQty" value="%s" /></td> <td><input type="text" class="form-control" name="orderCost" value="%.2f" /></td> <td><input type="text" class="form-control" name="receiveQty" value="%s" /></td> <td><input type="text" class="form-control" name="receiveCost" value="%.2f" /></td> <td><button type="submit" class="btn btn-default">Add New Item</button><input type="hidden" name="id" value="%d" /></td> </tr>', $this->sku, $this->sku, $item->upc(), $item->brand(), $item->description(), 1, $item->cost() * $item->units(), 0, 0, $this->id); echo '</table>'; echo '</form>'; } else { // item in order. just need received qty and cost echo '<form onsubmit="saveReceive(); return false;">'; echo '<table class="table table-bordered">'; echo '<tr><th>SKU</th><th>UPC</th><th>Brand</th><th>Description</th> <th>Qty Ordered</th><th>Cost (est)</th><th>Qty Received</th><th>Cost Received</th></tr>'; if ($model->receivedQty() === null) { $model->receivedQty($model->quantity()); } if ($model->receivedTotalCost() === null) { $model->receivedTotalCost($model->quantity() * $model->unitCost() * $model->caseSize()); } printf('<tr> <td>%s<input type="hidden" name="sku" value="%s" /></td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%s</td> <td>%.2f</td> <td><input type="text" class="form-control" name="qty" value="%s" /></td> <td><input type="text" class="form-control" name="cost" value="%.2f" /></td> <td><button type="submit" class="btn btn-default">Save</button><input type="hidden" name="id" value="%d" /></td> </tr>', $this->sku, $this->sku, $model->internalUPC(), $model->brand(), $model->description(), $model->quantity(), $model->quantity() * $model->unitCost() * $model->caseSize(), $model->receivedQty(), $model->receivedTotalCost(), $this->id); echo '</table>'; echo '</form>'; } return false; }