function list_content()
{
global $FANNIE_OP_DB, $FANNIE_URL;
$dbc = FannieDB::get($FANNIE_OP_DB);
$supertype = FormLib::get_form_value('supertype', 'dept');
$manufacturer = FormLib::get_form_value('manufacturer', '');
$mtype = FormLib::get_form_value('mtype', 'prefix');
$deptStart = FormLib::get_form_value('deptStart', 0);
$deptEnd = FormLib::get_form_value('deptEnd', 0);
$deptMulti = FormLib::get('departments', array());
$subDepts = FormLib::get('subdepts', array());
$super = FormLib::get_form_value('deptSub');
$vendorID = FormLib::get('vendor');
$upc_list = FormLib::get('u', array());
$inUse = FormLib::get('inUse', 1);
$store = FormLib::get('store', 0);
$sort = FormLib::get_form_value('sort', 'Department');
$order = 'dept_name';
if ($sort === 'UPC') {
$order = 'i.upc';
} elseif ($sort === 'Description') {
$order = 'i.description, i.upc';
}
$ret = 'Report sorted by ' . $sort . '<br />';
if ($supertype == 'dept' && $super === '') {
$ret .= 'Department ' . $deptStart . ' to ' . $deptEnd . '<br />';
} else {
if ($supertype == 'dept') {
$ret .= 'Sub department ' . $super . '<br />';
} else {
if ($supertype == 'manu') {
$ret .= _('Brand') . ' ' . $manufacturer . '<br />';
} else {
if ($supertype == 'vendor') {
$vendor = new VendorsModel($dbc);
$vendor->vendorID($vendorID);
$vendor->load();
$ret .= 'Vendor ' . $vendor->vendorName() . '<br />';
}
}
}
}
$ret .= date("F j, Y, g:i a") . '<br />';
$page_url = sprintf('ProductListPage.php?supertype=%s&deptStart=%s&deptEnd=%s&deptSub=%s&manufacturer=%s&mtype=%s&vendor=%d', $supertype, $deptStart, $deptEnd, $super, $manufacturer, $mtype, $vendorID);
if (!$this->excel) {
$ret .= '<form action="' . filter_input(INPUT_SERVER, 'PHP_SELF') . '" method="post" id="excel-form">
<input type="hidden" name="supertype" value="' . $supertype . '" />
<input type="hidden" name="deptStart" value="' . $deptStart . '" />
<input type="hidden" name="deptEnd" value="' . $deptEnd . '" />
<input type="hidden" name="deptSub" value="' . $super . '" />
<input type="hidden" name="manufacturer" value="' . $manufacturer . '" />
<input type="hidden" name="mtype" value="' . $mtype . '" />
<input type="hidden" name="vendor" value="' . $vendorID . '" />
<input type="hidden" name="inUse" value="' . $inUse . '" />
<input type="hidden" name="excel" value="yes" />';
if (is_array($subDepts)) {
foreach ($subDepts as $s) {
$ret .= '<input type="hidden" name="subdepts[]" value="' . $s . '" />';
}
}
if (is_array($upc_list)) {
foreach ($upc_list as $u) {
$ret .= '<input type="hidden" name="u[]" value="' . $u . '" />';
}
}
$ret .= '</form>';
$ret .= sprintf('<a href="" onclick="$(\'#excel-form\').submit();return false;">Save to Excel</a>
<a href="%s">Back</a><br />', basename(__FILE__));
}
/** base select clause and joins **/
$query = "\n SELECT i.upc,\n i.description,\n i.brand,\n d.dept_name as department,\n i.normal_price,\n (CASE WHEN i.tax = 1 THEN 'X' WHEN i.tax=0 THEN '-' ELSE LEFT(t.description,1) END) as Tax, \n (CASE WHEN i.foodstamp = 1 THEN 'X' ELSE '-' END) as FS,\n (CASE WHEN i.discount = 0 THEN '-' ELSE 'X'END) as DISC,\n (CASE WHEN i.scale = 1 THEN 'X' ELSE '-' END) as WGHd,\n (CASE WHEN i.local > 0 AND o.originID IS NULL THEN 'X' \n WHEN i.local > 0 AND o.originID IS NOT NULL THEN LEFT(o.shortName,1) ELSE '-' END) as local,\n COALESCE(v.vendorName, x.distributor) AS distributor,\n i.cost,\n i.store_id,\n l.description AS storeName\n FROM products as i \n LEFT JOIN departments as d ON i.department = d.dept_no\n LEFT JOIN taxrates AS t ON t.id = i.tax\n LEFT JOIN prodExtra as x on i.upc = x.upc\n LEFT JOIN vendors AS v ON i.default_vendor_id=v.vendorID\n LEFT JOIN Stores AS l ON i.store_id=l.storeID\n LEFT JOIN origins AS o ON i.local=o.originID";
/** add extra joins if this lookup requires them **/
if ($supertype == 'dept' && $super !== '') {
if ($super >= 0) {
$query .= ' LEFT JOIN superdepts AS s ON i.department=s.dept_ID ';
} elseif ($super == -2) {
$query .= ' LEFT JOIN MasterSuperDepts AS s ON i.department=s.dept_ID ';
}
} elseif ($supertype == 'vendor') {
$query .= ' LEFT JOIN vendors AS z ON z.vendorName=x.distributor ';
}
/** build where clause and parameters based on
the lookup type **/
$query .= ' WHERE 1=1 ';
$args = array();
if ($supertype == 'dept' && $super !== '') {
if ($super >= 0) {
$query .= ' AND s.superID=? ';
$args = array($super);
} elseif ($super == -2) {
$query .= ' AND s.superID <> 0 ';
}
if ($deptStart != 0 && $deptEnd != 0) {
$query .= ' AND i.department BETWEEN ? AND ? ';
$args[] = $deptStart;
$args[] = $deptEnd;
} elseif (count($deptMulti) > 0) {
$query .= ' AND i.department IN (';
foreach ($deptMulti as $d) {
$query .= '?,';
$args[] = $d;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
}
if (is_array($subDepts) && count($subDepts) > 0) {
$query .= ' AND i.subdept IN (';
foreach ($subDepts as $s) {
$query .= '?,';
$args[] = $s;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
}
} elseif ($supertype == 'manu' && $mtype == 'prefix') {
$query .= ' AND i.upc LIKE ? ';
$args = array('%' . $manufacturer . '%');
} elseif ($supertype == 'manu' && $mtype != 'prefix') {
$query .= ' AND (i.brand LIKE ? OR x.manufacturer LIKE ?) ';
$args = array('%' . $manufacturer . '%', '%' . $manufacturer . '%');
} elseif ($supertype == 'vendor') {
$query .= ' AND (i.default_vendor_id=? OR z.vendorID=?) ';
$args = array($vendorID, $vendorID);
} elseif ($supertype == 'upc') {
$inp = '';
foreach ($upc_list as $u) {
$inp .= '?,';
$args[] = $u;
}
$inp = substr($inp, 0, strlen($inp) - 1);
$query .= ' AND i.upc IN (' . $inp . ') ';
} else {
$query .= ' AND i.department BETWEEN ? AND ? ';
$args = array($deptStart, $deptEnd);
if (is_array($subDepts) && count($subDepts) > 0) {
$query .= ' AND i.subdept IN (';
foreach ($subDepts as $s) {
$query .= '?,';
$args[] = $s;
}
}
}
if ($inUse == 1) {
$query .= ' AND i.inUse=1 ';
} else {
$query .= ' AND i.inUse=0 ';
}
if ($store > 0) {
$query .= ' AND i.store_id=? ';
$args[] = $store;
}
/** finish building query w/ order clause **/
$query .= 'ORDER BY ' . $order;
if ($order != "i.upc") {
$query .= ",i.upc";
}
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
if ($result === false || $dbc->num_rows($result) == 0) {
return 'No data found!';
}
$ret .= '<table class="table table-striped table-bordered tablesorter small">
<thead>
<tr>';
$ret .= "<th>UPC</th><th>Brand</th><th>Description</th><th>Dept</th><th>" . _('Vendor') . "</th><th>Cost</th><th>Price</th>";
$ret .= "<th>Tax</th><th>FS</th><th>Disc</th><th>Wg'd</th><th>Local</th>";
if (!$this->excel && $this->canEditItems !== false) {
$ret .= '<th> </th>';
}
$ret .= "</tr></thead><tbody>";
$multi = $this->config->get('STORE_MODE') == 'HQ' ? true : false;
while ($row = $dbc->fetch_row($result)) {
$ret .= '<tr id="' . $row[0] . '">';
$enc = base64_encode($row[1]);
if (!$this->excel) {
$ret .= "<td align=center class=\"td_upc\"><a href=ItemEditorPage.php?searchupc={$row['0']}>{$row['0']}</a>";
if ($multi) {
$ret .= ' (' . substr($row['storeName'], 0, 1) . ')';
}
if ($this->canDeleteItems !== false) {
$ret .= " <a href=\"\" onclick=\"deleteCheck('{$row['0']}','{$enc}'); return false;\">";
$ret .= \COREPOS\Fannie\API\lib\FannieUI::deleteIcon() . '</a>';
}
$ret .= '</td>';
$ret .= '<input type="hidden" class="hidden_upc" value="' . $row[0] . '" />';
$ret .= '<input type="hidden" class="hidden_store_id" value="' . $row['store_id'] . '" />';
} else {
$ret .= "<td align=center>{$row['0']}</td>";
}
$ret .= "<td align=center class=\"td_brand clickable\">{$row['brand']}</td>";
$ret .= "<td align=center class=\"td_desc clickable\">{$row['description']}</td>";
$ret .= "<td align=center class=\"td_dept clickable\">{$row['department']}</td>";
$ret .= "<td align=center class=\"td_supplier clickable\">{$row['distributor']}</td>";
$ret .= "<td align=center class=\"td_cost clickable\">" . sprintf('%.2f', $row['cost']) . "</td>";
$ret .= "<td align=center class=\"td_price clickable\">{$row['normal_price']}</td>";
$ret .= "<td align=center class=td_tax>{$row['Tax']}</td>";
$ret .= "<td align=center class=td_fs>{$row['FS']}</td>";
$ret .= "<td align=center class=td_disc>{$row['DISC']}</td>";
$ret .= "<td align=center class=td_wgt>{$row['WGHd']}</td>";
$ret .= "<td align=center class=td_local>{$row['local']}</td>";
if (!$this->excel && $this->canEditItems !== False) {
$ret .= "<td align=center class=td_cmd><a href=\"\" \n class=\"edit-link\"\n onclick=\"edit(\$(this).closest('tr')); return false;\">" . \COREPOS\Fannie\API\lib\FannieUI::editIcon() . '</a>
<a href="" class="save-link collapse"
onclick="save($(this).closest(\'tr\')); return false;">' . \COREPOS\Fannie\API\lib\FannieUI::saveIcon() . '</a></td>';
}
$ret .= "</tr>\n";
}
$ret .= '</tbody></table>';
if ($this->excel) {
header('Content-Type: application/ms-excel');
header('Content-Disposition: attachment; filename="itemList.csv"');
$array = \COREPOS\Fannie\API\data\DataConvert::htmlToArray($ret);
$ret = \COREPOS\Fannie\API\data\DataConvert::arrayToCsv($array);
} else {
$this->add_script('../src/javascript/tablesorter/jquery.tablesorter.min.js');
$this->add_onload_command("\$('.tablesorter').tablesorter();\n");
}
return $ret;
}
}
printf('<tr><th>Ttl</th><th>%s</th>
<th>$%.2f</th><th>%.2f%%</th>
<th>$%.2f</th><th>%.2f%%</th>
<th>$%.2f</th></tr>', $sname, $slocal, 100 * ($slocal / $sttl), $sc, 100 * ($sc / $sttl), $sttl);
printf('<tr><td colspan=7> </td></tr>
<tr><th>Ttl</th><th>Store</th>
<th>$%.2f</th><th>%.2f%%</th>
<th>$%.2f</th><th>%.2f%%</th>
<th>$%.2f</th></tr>', $master_totals[0], 100 * ($master_totals[0] / $master_totals[2]), $master_totals[1], 100 * ($master_totals[1] / $master_totals[2]), $master_totals[2]);
echo '</table>';
if (isset($_REQUEST['excel'])) {
$output = ob_get_contents();
ob_end_clean();
$array = \COREPOS\Fannie\API\data\DataConvert::htmlToArray($output);
$xls = \COREPOS\Fannie\API\data\DataConvert::arrayToExcel($array);
echo $xls;
}
} else {
$page_title = "Fannie : Local Sales Report";
$header = "Local Sales Report";
include $FANNIE_ROOT . 'src/header.html';
$lastMonday = "";
$lastSunday = "";
$ts = mktime(0, 0, 0, date("n"), date("j") - 1, date("Y"));
while ($lastMonday == "" || $lastSunday == "") {
if (date("w", $ts) == 1 && $lastSunday != "") {
$lastMonday = date("Y-m-d", $ts);
} elseif (date("w", $ts) == 0) {
$lastSunday = date("Y-m-d", $ts);
}
return;
}
header("Content-Disposition: inline; filename=mailingList.csv");
header("Content-Description: PHP3 Generated Data");
header("Content-type: application/vnd.ms-excel; name='excel'");
include '../config.php';
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.php';
$dbc = FannieDB::get($FANNIE_OP_DB);
$query = $dbc->prepare_statement("SELECT CardNo, \n LastName, \n FirstName, \n street,\n city,\n state,\n zip,\n phone,\n memType,\n end_date\n FROM custdata AS c\n LEFT JOIN meminfo AS m\n ON c.CardNo=m.card_no\n LEFT JOIN memDates AS d\n ON c.CardNo=d.card_no\n WHERE \n c.Type='PC'\n AND (end_date > " . $dbc->now() . " \n or end_date = '' \n or end_date is null\n or end_date='1900-01-01 00:00:00'\n or end_date='0000-00-00 00:00:00')\n AND ads_OK = 1\n AND personNum = 1\n AND LastName <> 'NEW MEMBER'\n order by m.card_no");
$result = $dbc->exec_statement($query);
$ret = array();
while ($row = $dbc->fetch_row($result)) {
$new = array(11);
$new[0] = $row[0];
$new[1] = $row[1];
$new[2] = $row[2];
if (strstr($row[3], "\n") === False) {
$new[3] = $row[3];
$new[4] = "";
} else {
$pts = explode("\n", $row[3]);
$new[3] = $pts[0];
$new[4] = $pts[1];
}
for ($i = 5; $i <= 10; $i++) {
$new[$i] = $row[$i - 1];
}
$ret[] = $new;
}
$xls = \COREPOS\Fannie\API\data\DataConvert::arrayToCsv($ret);
echo $xls;
/**
Helper: check default export args
*/
protected function formatCheck()
{
if (FormLib::get('excel') === 'xls') {
$this->report_format = 'xls';
$this->window_dressing = false;
/**
Verify whether Excel support is available. If it is not,
fall back to CSV output. Should probably
generate some kind of log message or notification.
*/
if (!\COREPOS\Fannie\API\data\DataConvert::excelSupport()) {
$this->report_format = 'csv';
}
} elseif (FormLib::get('excel') === 'csv') {
$this->report_format = 'csv';
$this->window_dressing = false;
}
}