$j = 0;
for ($i = 0; $i < 30; $i++) {
$j++;
$productFlags .= "\n,CASE WHEN ((1<<{$i}) & p.numflag) = 0 THEN 0 ELSE 1 END pFlag{$j}";
}
/* 'a The select is in the order of the final excel format.
* Column aliases are extracted for column heads.
*/
$pQ = "SELECT\n p.upc upc,\n w.search_description searchDesc,\n v.vendorID distribId,\n e.distributor distribName,\n w.order_code orderCode,\n p.department deptId,\n d.dept_name deptName,\n u.brand brand,\n p.inUse inUse,\n p.qttyEnforced qttyEnforced,\n p.mixmatchcode mixmatchCode,\n p.quantity groupCount,\n p.groupprice groupPrice,\n p.pricemethod priceMethod,\n p.discount discount,\n p.discounttype discountType,\n w.description description,\n e.case_cost caseCost,\n v.units caseSize,\n p.size unitSize,\n p.unitofmeasure unitOfMeasure,\n p.normal_price setPrice,\n p.tax taxType,\n CASE WHEN e.margin > 1.00 THEN e.margin ELSE ((1/e.margin)/((1/e.margin)-1)) END markup,\n CASE WHEN p.scale = 1 THEN 'BULK' ELSE 'COUNT' END scale,\n v.sku SKU,\n p.special_price salePrice,\n p.specialpricemethod salePriceMethod,\n p.specialgroupprice saleGroupPrice,\n p.specialquantity saleQuantity,\n p.start_date saleStartDate,\n p.end_date saleEndDate{$productFlags}\n FROM\n core_op.products AS p\n LEFT JOIN core_op.productUser AS u ON p.upc = u.upc\n LEFT JOIN core_op.prodExtra AS e ON p.upc = e.upc\n LEFT JOIN core_op.products_WEFC_Toronto AS w ON p.upc = w.upc\n LEFT JOIN core_op.vendorItems AS v ON p.upc = v.upc\n LEFT JOIN core_op.departments AS d ON p.department = d.dept_no\n WHERE\n p.upc < '9000000000000'\n ORDER BY\n p.upc";
// Create column head labels from field aliases
$hstr = preg_replace("/\t|\n/", "", $pQ);
$hstr = preg_replace("/(^SELECT)(.*)(FROM.*\$)/", "\$2", $hstr);
$hraw = explode(",", $hstr);
$headers = preg_replace("/^.*( [a-zA-Z0-9]*\$)/", "\$1", $hraw);
//select_to_table($pQ,array(),1,'ffffff');
select_to_table2($pQ, array(), 1, 'ffffff', '', '0', '2', $headers, False);
//select_to_table2($query,$arguments,$border,$bgcolor,$width="120",$spacing="0",$padding="0",$headers=array(),$nostart=False)
} else {
$page_title = "Fannie : WEFC Toronto Products Export Report";
$header = "WEFC Toronto Products Export Report";
include $FANNIE_ROOT . 'src/header.html';
?>
<form action=index.php method=get>
<style type="text/css">
/* This makes the input and label look like they have the same baseline. */
input[type="radio"] ,
input[type="checkbox"] {
height: 8px;
}
</style>
<?php
include '../../../config.php';
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.php';
if (!class_exists("SQLManager")) {
require_once $FANNIE_ROOT . "src/SQLManager.php";
}
include $FANNIE_ROOT . 'src/functions.php';
if (isset($_GET['excel'])) {
header('Content-Type: application/ms-excel');
header('Content-Disposition: attachment; filename="agedTrialBalances.xls"');
$_SERVER['REQUEST_URI'] = $_SERVER['PHP_SELF'];
}
$cached_output = \COREPOS\Fannie\API\data\DataCache::getFile("monthly");
if ($cached_output) {
echo $cached_output;
return;
}
ob_start();
$query = "select a.* from is4c_trans.AR_EOM_Summary as a\n left join custdata as c on a.cardno=c.CardNo and c.personNum=1\n where c.Type <> 'TERM' \n and (\n priorBalance <> 0 or\n threeMonthCharges <> 0 or\n threeMonthPayments <> 0 or\n threeMonthBalance <> 0 or\n twoMonthCharges <> 0 or\n twoMonthPayments <> 0 or\n twoMonthBalance <> 0 or\n lastMonthCharges <> 0 or\n lastMonthPayments <> 0 or\n lastMonthBalance <> 0\n )\n order by a.cardno";
$headers = array('Mem Num', 'Name', 'Prior Balance', 'Charge', 'Payment', 'Balance', 'Charge', 'Payment', 'Balance', 'Charge', 'Payment', 'Balance');
echo "<table border=1 cellpadding=0 cellspacing=0>\n";
echo "<tr><th colspan=3> </th>";
echo "<th colspan=3>3 Months Prior</th>";
echo "<th colspan=3>2 Months Prior</th>";
echo "<th colspan=3>Last Month</th></tr>";
select_to_table2($query, array(), 0, '#ffffcc', 120, 0, 0, $headers, True);
$output = ob_get_contents();
ob_end_clean();
\COREPOS\Fannie\API\data\DataCache::putFile('monthly', $output);
echo $output;
