function fetch_report_data()
{
global $FANNIE_ARCHIVE_METHOD, $FANNIE_ARCHIVE_DB, $FANNIE_OP_DB, $FANNIE_TRANS_DB;
$date1 = FormLib::get_form_value('date1', date('Y-m-d'));
$date2 = FormLib::get_form_value('date2', date('Y-m-d'));
$card_no = FormLib::get_form_value('card_no', '0');
$dbc = FannieDB::get($FANNIE_ARCHIVE_DB);
$OP = $FANNIE_OP_DB . $dbc->sep();
$TRANS = $FANNIE_TRANS_DB . $dbc->sep();
// date1='' is program-epoch.
$date1 = $date1 == '' ? $this->programStartDate : $date1;
// date2='' is now.
$date2 = $date2 == '' ? date('Y-m-d') : $date2;
$dlog = DTransactionsModel::select_dlog($date1, $date2);
$dte = 'tdate';
/* If today's transactions are wanted.
* May not work if archive method is partitions.
*/
if ($date1 != date('Y-m-d') && $date2 >= date('Y-m-d')) {
$dlog_spec = " UNION ALL SELECT * FROM {$TRANS}dlog";
if (substr($dlog, 0, 1) == '(') {
$dlog = '(' . trim($dlog, '()') . $dlog_spec . ')';
} else {
$dlog = '(SELECT * FROM ' . $dlog . $dlog_spec . ')';
}
} else {
// Should not be a subquery.
if ($FANNIE_ARCHIVE_METHOD == 'partitions') {
$dlog = trim($dlog, '()');
}
}
$cardOp = $card_no == 0 ? ">" : "=";
$query = "SELECT d.card_no,\n d.{$dte},\n DATE_FORMAT(d.{$dte},'%Y %m %d %l:%i') AS 'SortDate',\n DATE_FORMAT(d.{$dte},'%M %e, %Y %l:%i%p') AS 'When',\n CASE WHEN (d.card_no BETWEEN {$this->bankerMin} AND {$this->bankerMax})\n THEN a.LastName\n ELSE CONCAT(a.FirstName,' ',a.LastName)\n END AS 'Who',\n trans_status,\n unitPrice,\n quantity,\n total,\n CASE WHEN (d.description != m.dept_name)\n THEN d.description ELSE '' END\n AS 'Comment'\n FROM {$dlog} d\n LEFT JOIN {$OP}custdata a ON a.CardNo = d.card_no\n LEFT JOIN {$OP}departments m ON m.dept_no = d.department\n WHERE d.department = {$this->paymentDepartment}\n AND ({$dte} BETWEEN ? AND ?)\n ORDER BY DATE_FORMAT(d.{$dte}, '%Y-%m-%d %H:%i')";
$args = array();
$args[] = $date1 . ' 00:00:00';
$args[] = $date2 . ' 23:59:59';
$prep = $dbc->prepare_statement($query);
if ($prep === False) {
$dbc->logger("\nprep failed:\n{$query}");
}
$result = $dbc->exec_statement($prep, $args);
if ($result === False) {
$dbc->logger("\nexec failed:\n{$query}\nargs:", implode(" : ", $args));
}
/**
Build array of results, without totals.
*/
$ret = array();
$transferOut = 0;
$otherOut = 0;
$rowCount = 0;
while ($row = $dbc->fetch_array($result)) {
$memberNumber = $row['card_no'];
$suffix = "";
if ($row['trans_status'] == 'V') {
$suffix = " Void";
}
/* Refunds:
* They are clutter in Bank but not in Member.
*/
if ($row['trans_status'] == 'R') {
if ($memberNumber == $this->programBankID) {
/* $transferOut is not used.
$transferOut += $row['total'];
*/
$suffix = " Refund";
continue;
} else {
/* $otherOut is not used.
$otherOut += $row['total'];
*/
$suffix = " Reversed";
}
}
$record = array();
if ($this->sortable) {
$record[] = $row['SortDate'];
}
$record[] = $row['When'];
$record[] = "<a href='../Activity/ActivityReport.php?" . "memNum={$row['card_no']}&programID={$this->programID}'" . " target='_CCR_{$row['card_no']}' title='Details for this member'>" . "{$row['card_no']}</a>";
$record[] = "<a href='../Activity/ActivityReport.php?" . "memNum={$row['card_no']}&programID={$this->programID}'" . " target='_CCR_{$row['card_no']}' title='Details for this member'>" . "{$row['Who']}</a>";
$record[] = ($memberNumber == $this->programBankID ? "Input" : "Payment") . $suffix;
$record[] = sprintf("%.2f", $memberNumber == $this->programBankID ? $row['total'] : -1 * $row['total']);
$record[] = $row['Comment'];
$ret[] = $record;
$rowCount++;
}
return $ret;
// /fetch_report_data()
}
function fetch_report_data()
{
global $FANNIE_OP_DB, $FANNIE_COOP_ID, $FANNIE_TRANS_DB, $FANNIE_URL;
try {
$d1 = $this->form->date1;
$d2 = $this->form->date2;
} catch (Exception $ex) {
return array();
}
$dlog = DTransactionsModel::select_dlog($d1, $d2);
if (isset($FANNIE_COOP_ID) && $FANNIE_COOP_ID == 'WEFC_Toronto') {
$shrinkageUsers = " AND (card_no not between 99900 and 99998)";
} else {
$shrinkageUsers = "";
}
// New structure.
$mdata = array();
// Ancestor structures
$card_no = array();
$total = array();
// Total Spent for desired Range
$numTran = array();
// Number of transactions for selected Range for each Owner
$dbc = $this->connection;
$dbc->selectDB($this->config->get('TRANS_DB'));
$limit = "";
if ($this->top_n) {
$limit = " LIMIT " . $this->top_n;
}
/* Total purchases for each member.
* Old way: create parallel arrays $card_no of .card_no, $total of total.
* New way: creat mdata, array of arrays.
*/
$query = "SELECT card_no,\n sum(total) as tpurch \n FROM {$dlog} dx\n WHERE\n (tdate BETWEEN ? AND ?)\n AND trans_type in ('I','D','S'){$shrinkageUsers}\n GROUP BY card_no \n ORDER BY tpurch desc{$limit}\n ;";
$statement = $dbc->prepare_statement($query);
$args = array($d1 . ' 00:00:00', $d2 . ' 23:59:59');
$result = $dbc->exec_statement($statement, $args);
$mdata = array();
// New
while ($row = $dbc->fetch_row($result)) {
$card_no[] = $row['card_no'];
$total[] = $row['tpurch'];
//New
$mem_num = $row['card_no'];
//N.B. index is string.
$mdata["{$mem_num}"] = array($row['tpurch'], 0);
}
/* Number of transactions for each member in the previous search.
* First option, search repeated for each member.
* OK to do this way if $top_n <= ~10 and if date range is small,
* but not for many or over a long range if unions involved.
* Why all the other arguments?
* Needed to distinguish transactions: emp-reg-trans on y-m-d.
* Why does that matter within the date range?
*/
$unionCount = substr_count($dlog, ' union ');
if ($this->top_n > 0 && $this->top_n <= 10 && $unionCount <= 2) {
$query = "SELECT trans_num,\n month(tdate) as mt, day(tdate) as dt, year(tdate) as yt \n FROM {$dlog} dx\n WHERE\n (tdate BETWEEN ? AND ?)\n AND card_no = ? AND trans_type = 'A'\n GROUP BY trans_num, mt, dt, yt \n ORDER BY mt, dt, yt\n ;";
$statement = $dbc->prepare_statement($query);
$args = array($d1 . ' 00:00:00', $d2 . ' 23:59:59');
for ($i = 0; $i < count($card_no); $i++) {
$args[2] = $card_no[$i];
$result = $dbc->exec_statement($statement, $args);
$mdata["{$card_no[$i]}"][1] = $dbc->num_rows($result);
$numTran[] = $dbc->num_rows($result);
}
} else {
/* Number of transactions for each member in the previous search. */
$query = "SELECT count(card_no) AS ct, card_no\n FROM {$dlog} dx\n WHERE\n (tdate BETWEEN ? AND ?)\n AND (trans_type = 'A'){$shrinkageUsers}\n GROUP BY card_no\n ;";
$statement = $dbc->prepare_statement($query);
$args = array($d1 . ' 00:00:00', $d2 . ' 23:59:59');
$result = $dbc->exec_statement($statement, $args);
$count = 0;
if ($this->top_n > 0) {
while ($row = $dbc->fetch_row($result)) {
$mem_num = $row['card_no'];
if (isset($mdata["{$mem_num}"])) {
$mdata["{$mem_num}"][1] = $row['ct'];
$count++;
}
}
} else {
while ($row = $dbc->fetch_row($result)) {
$mem_num = $row['card_no'];
$mdata["{$mem_num}"][1] = $row['ct'];
$count++;
}
}
}
if (isset($mdata['99999'])) {
$this->non_member = $mdata['99999'];
}
// Compose the rows of the report in a 2D array.
$info = array();
foreach ($mdata as $mem => $mbits) {
$table_row = array($mem, sprintf("%0.2f", $mbits[0]), sprintf("%0.2f", $mbits[0] / $mbits[1]), $mbits[1]);
$info[] = $table_row;
}
return $info;
// fetch_report_data()
}
