public function get_orderID_customer_handler()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$TRANS = $this->config->get('TRANS_DB') . $dbc->sep();
$orderID = $this->orderID;
$names = array();
$pn = 1;
$status_row = array('Type' => 'REG', 'status' => '');
$dbc->selectDB($this->config->get('TRANS_DB'));
$orderModel = new SpecialOrdersModel($dbc);
$orderModel->specialOrderID($orderID);
$orderModel->load();
$dbc->selectDB($this->config->get('OP_DB'));
// look up member id
$memNum = 0;
$findMem = $dbc->prepare_statement("SELECT card_no,voided FROM {$TRANS}CompleteSpecialOrder WHERE order_id=?");
$memR = $dbc->exec_statement($findMem, array($orderID));
if ($dbc->num_rows($memR) > 0) {
$memW = $dbc->fetch_row($memR);
$memNum = $memW['card_no'];
$pn = $memW['voided'];
}
// Get member info from custdata, non-member info from SpecialOrders
if ($memNum != 0) {
$namesP = $dbc->prepare_statement("SELECT personNum,FirstName,LastName FROM custdata\n WHERE CardNo=? ORDER BY personNum");
$namesR = $dbc->exec_statement($namesP, array($memNum));
while ($namesW = $dbc->fetch_row($namesR)) {
$names[$namesW['personNum']] = array($namesW['FirstName'], $namesW['LastName']);
}
$statusQ = $dbc->prepare_statement("SELECT Type FROM custdata WHERE CardNo=?");
$statusR = $dbc->exec_statement($statusQ, array($memNum));
$status_row = $dbc->fetch_row($statusR);
if ($status_row['Type'] == 'INACT') {
$status_row['status'] = 'Inactive';
} elseif ($status_row['Type'] == 'INACT2') {
$status_row['status'] = 'Inactive';
} elseif ($status_row['Type'] == 'TERM') {
$status_row['status'] = 'Terminated';
}
}
$q = $dbc->prepare_statement("SELECT entry_date FROM {$TRANS}SpecialOrderHistory \n WHERE order_id=? AND entry_type='CONFIRMED'");
$r = $dbc->exec_statement($q, array($orderID));
$confirm_date = "";
if ($dbc->num_rows($r) > 0) {
$confirm_date = array_pop($dbc->fetch_row($r));
}
$callback = 1;
$user = '******';
$orderDate = '';
$q = $dbc->prepare_statement("SELECT datetime,numflag,mixMatch FROM \n {$TRANS}CompleteSpecialOrder WHERE order_id=? AND trans_id=0");
$r = $dbc->exec_statement($q, array($orderID));
if ($dbc->num_rows($r) > 0) {
list($orderDate, $callback, $user) = $dbc->fetch_row($r);
}
$ret = "";
$ret .= sprintf('<input type="hidden" id="orderID" value="%d" />', $orderID);
$ret .= '<div class="row">
<div class="col-sm-6 text-left">';
$ret .= sprintf('<b>Owner Number</b>: %s', $memNum == 0 ? '' : $memNum);
$ret .= '<br />';
$ret .= '<b>Owner</b>: ' . ($status_row['Type'] == 'PC' ? 'Yes' : 'No');
$ret .= '<br />';
if (!empty($status_row['status'])) {
$ret .= '<b>Account status</b>: ' . $status_row['status'];
$ret .= '<br />';
}
$ret .= "<b>Taken by</b>: " . $user . "<br />";
$ret .= "<b>On</b>: " . date("M j, Y g:ia", strtotime($orderDate)) . "<br />";
$ret .= '</div>
<div class="col-sm-6 text-right">';
$ret .= '<b>Call to Confirm</b>: ';
if ($callback == 1) {
$ret .= 'Yes';
} else {
$ret .= 'No';
}
$ret .= '<br />';
$ret .= '<span id="confDateSpan">' . (!empty($confirm_date) ? 'Confirmed ' . $confirm_date : 'Not confirmed') . "</span> ";
$ret .= '<br />';
$ret .= '<a href="index.php" class="btn btn-default">Done</a>';
$ret .= '</div></div>';
$ret .= '<table class="table table-bordered table-striped">';
// names
if (empty($names)) {
$ret .= sprintf('<tr><th>First Name</th><td>%s
</td>', $orderModel->firstName(), $orderID);
$ret .= sprintf('<th>Last Name</th><td>%s
</td>', $orderModel->lastName(), $orderID);
} else {
$ret .= '<tr><th>Name</th><td colspan="2">';
foreach ($names as $p => $n) {
if ($p == $pn) {
$ret .= $n[0] . ' ' . $n[1];
}
}
$ret .= '</td>';
$ret .= '<td> </td>';
}
$super = new SuperDeptNamesModel($dbc);
$super->superID($orderModel->noteSuperID());
$super->load();
$ret .= '<td colspan="4">Notes for: ' . $super->super_name() . '</td></tr>';
// address
$street = $orderModel->street();
$street2 = '';
if (strstr($street, "\n")) {
list($street, $street2) = explode("\n", $street, 2);
}
$ret .= sprintf('<tr><th>Address</th><td>%s
</td><th>E-mail</th><td>%s</td>
<td rowspan="2" colspan="4">%s
</td></tr>
<tr><th>Addr (2)</th><td>%s
</td><th>City</th><td>%s
</td></tr>
<tr><th>Phone</th><td>%s</td>
<th>Alt. Phone</th><td>%s</td>
<th>State</th>
<td>%s</td>
<th>Zip</th><td>%s</td></tr>', $street, $orderModel->email(), $orderModel->notes(), $street2, $orderModel->city(), $orderModel->phone(), $orderModel->altPhone(), $orderModel->state(), $orderModel->zip());
$ret .= '</table>';
echo $ret;
return false;
}
public function get_id_view()
{
$this->addScript('pricing-batch.js');
$dbc = $this->connection;
$dbc->selectDB($this->config->OP_DB);
$superID = FormLib::get_form_value('super', 99);
$queueID = FormLib::get('queueID');
$vendorID = $this->id;
$filter = FormLib::get_form_value('filter') == 'Yes' ? True : False;
/* lookup vendor and superdept names to build a batch name */
$sname = "All";
if ($superID != 99) {
$smodel = new SuperDeptNamesModel($dbc);
$smodel->superID($superID);
$smodel->load();
$sname = $smodel->super_name();
}
$vendor = new VendorsModel($dbc);
$vendor->vendorID($vendorID);
$vendor->load();
$batchName = $sname . " " . $vendor->vendorName() . " PC " . date('m/d/y');
/* find a price change batch type */
$types = new BatchTypeModel($dbc);
$types->discType(0);
$bType = 0;
foreach ($types->find() as $obj) {
$bType = $obj->batchTypeID();
break;
}
/* get the ID of the current batch. Create it if needed. */
$bidQ = $dbc->prepare("\n SELECT batchID \n FROM batches \n WHERE batchName=? \n AND batchType=? \n AND discounttype=0\n ORDER BY batchID DESC");
$bidR = $dbc->execute($bidQ, array($batchName, $bType));
$batchID = 0;
if ($dbc->numRows($bidR) == 0) {
$b = new BatchesModel($dbc);
$b->batchName($batchName);
$b->startDate('1900-01-01');
$b->endDate('1900-01-01');
$b->batchType($bType);
$b->discountType(0);
$b->priority(0);
$batchID = $b->save();
} else {
$bidW = $dbc->fetchRow($bidR);
$batchID = $bidW['batchID'];
}
$ret = sprintf('<b>Batch</b>:
<a href="%sbatches/newbatch/BatchManagementTool.php?startAt=%d">%s</a>', $this->config->URL, $batchID, $batchName);
$ret .= sprintf("<input type=hidden id=vendorID value=%d />\n <input type=hidden id=batchID value=%d />\n <input type=hidden id=queueID value=%d />\n <input type=hidden id=superID value=%d />", $vendorID, $batchID, $queueID, $superID);
$batchUPCs = array();
$batchList = new BatchListModel($dbc);
$batchList->batchID($batchID);
foreach ($batchList->find() as $obj) {
$batchUPCs[$obj->upc()] = true;
}
$costSQL = Margin::adjustedCostSQL('v.cost', 'b.discountRate', 'b.shippingMarkup');
$marginSQL = Margin::toMarginSQL($costSQL, 'p.normal_price');
$p_def = $dbc->tableDefinition('products');
$marginCase = '
CASE
WHEN g.margin IS NOT NULL AND g.margin <> 0 THEN g.margin
WHEN s.margin IS NOT NULL AND s.margin <> 0 THEN s.margin
ELSE d.margin
END';
$srpSQL = Margin::toPriceSQL($costSQL, $marginCase);
$query = "SELECT p.upc,\n p.description,\n v.cost,\n b.shippingMarkup,\n b.discountRate,\n p.normal_price,\n " . Margin::toMarginSQL($costSQL, 'p.normal_price') . " AS current_margin,\n " . Margin::toMarginSQL($costSQL, 'v.srp') . " AS desired_margin,\n " . $costSQL . " AS adjusted_cost,\n v.srp,\n " . $srpSQL . " AS rawSRP,\n v.vendorDept,\n x.variable_pricing,\n " . $marginCase . " AS margin\n FROM products AS p \n INNER JOIN vendorItems AS v ON p.upc=v.upc AND p.default_vendor_id=v.vendorID\n INNER JOIN vendors as b ON v.vendorID=b.vendorID\n LEFT JOIN departments AS d ON p.department=d.dept_no\n LEFT JOIN vendorDepartments AS s ON v.vendorDept=s.deptID AND v.vendorID=s.vendorID\n LEFT JOIN VendorSpecificMargins AS g ON p.department=g.deptID AND v.vendorID=g.vendorID\n LEFT JOIN prodExtra AS x on p.upc=x.upc ";
$args = array($vendorID);
if ($superID != 99) {
$query .= " LEFT JOIN MasterSuperDepts AS m\n ON p.department=m.dept_ID ";
}
$query .= "WHERE v.cost > 0 \n AND v.vendorID=?\n AND p.inUse=1 ";
if ($superID != 99) {
$query .= " AND m.superID=? ";
$args[] = $superID;
}
if ($filter === false) {
$query .= " AND p.normal_price <> v.srp ";
}
if ($this->config->get('STORE_MODE') == 'HQ') {
$query .= ' AND p.store_id=? ';
$args[] = $this->config->get('STORE_ID');
}
$query .= " ORDER BY p.upc";
if (isset($p_def['price_rule_id'])) {
$query = str_replace('x.variable_pricing', 'p.price_rule_id AS variable_pricing', $query);
}
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
$ret .= "<table class=\"table table-bordered small\">";
$ret .= "<tr><td colspan=6> </td><th colspan=2>Current</th>\n <th colspan=3>Vendor</th></tr>";
$ret .= "<tr><th>UPC</th><th>Our Description</th>\n <th>Base Cost</th>\n <th>Shipping</th>\n <th>Discount%</th>\n <th>Adj. Cost</th>\n <th>Price</th><th>Margin</th><th>Raw</th><th>SRP</th>\n <th>Margin</th><th>Cat</th><th>Var</th>\n <th>Batch</th></tr>";
while ($row = $dbc->fetch_row($result)) {
$background = "white";
if (isset($batchUPCs[$row['upc']])) {
$background = 'selection';
} elseif ($row['variable_pricing'] == 0) {
$background = $row['normal_price'] + 0.1 < $row['rawSRP'] ? 'red' : 'green';
if ($row['normal_price'] - 0.1 > $row['rawSRP']) {
$background = $row['normal_price'] - 0.1 > $row['rawSRP'] ? 'yellow' : 'green';
}
}
if (isset($batchUPCs[$row['upc']])) {
$icon = '<span class="glyphicon glyphicon-minus-sign"
title="Remove from batch">
</span>';
} else {
$icon = '<span class="glyphicon glyphicon-plus-sign"
title="Add to batch">
</span>';
}
$ret .= sprintf("<tr id=row%s class=%s>\n <td class=\"sub\"><a href=\"%sitem/ItemEditorPage.php?searchupc=%s\">%s</a></td>\n <td class=\"sub\">%s</td>\n <td class=\"sub cost\">%.2f</td>\n <td class=\"sub shipping\">%.2f%%</td>\n <td class=\"sub discount\">%.2f%%</td>\n <td class=\"sub adj-cost\">%.2f</td>\n <td class=\"sub price\">%.2f</td>\n <td class=\"sub cmargin\">%.2f%%</td>\n <td class=\"sub raw-srp\">%.2f</td>\n <td onclick=\"reprice('%s');\" class=\"sub srp\">%.2f</td>\n <td class=\"sub dmargin\">%.2f%%</td>\n <td class=\"sub\">%d</td>\n <td><input class=varp type=checkbox onclick=\"toggleV('%s');\" %s /></td>\n <td class=white>\n <a class=\"add-button %s\" href=\"\" \n onclick=\"addToBatch('%s'); return false;\">\n <span class=\"glyphicon glyphicon-plus-sign\"\n title=\"Add item to batch\"></span>\n </a>\n <a class=\"remove-button %s\" href=\"\" \n onclick=\"removeFromBatch('%s'); return false;\">\n <span class=\"glyphicon glyphicon-minus-sign\"\n title=\"Remove item from batch\"></span>\n </a>\n </td>\n </tr>", $row['upc'], $background, $this->config->URL, $row['upc'], $row['upc'], $row['description'], $row['cost'], $row['shippingMarkup'] * 100, $row['discountRate'] * 100, $row['adjusted_cost'], $row['normal_price'], 100 * $row['current_margin'], $row['rawSRP'], $row['upc'], $row['srp'], 100 * $row['desired_margin'], $row['vendorDept'], $row['upc'], $row['variable_pricing'] >= 1 ? 'checked' : '', isset($batchUPCs[$row['upc']]) ? 'collapse' : '', $row['upc'], !isset($batchUPCs[$row['upc']]) ? 'collapse' : '', $row['upc']);
}
$ret .= "</table>";
return $ret;
}
