fannie/reports/PatronageOverDates/PatronageOverDatesReport.php
<?php
/*******************************************************************************
Copyright 2013 Whole Foods Co-op
Copyright 2015 West End Food Co-op, Toronto
This file is part of CORE-POS.
CORE-POS is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
CORE-POS is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
in the file license.txt along with IT CORE; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*********************************************************************************/
/* TODO
* Eliminate the multi-array code left over from the ancestor of this report.
*/
include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
include(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}
class PatronageOverDatesReport extends FannieReportPage
{
public $description = '[Patronage Over Date Range] lists top, or all, customers by purchases/avg basket over a range of dates';
public $report_set = 'Membership :: Patronage';
public $themed = true;
protected $report_headers = array();
protected $sort_direction = 1;
protected $sort_column = 1;
protected $title = "";
protected $header = "";
protected $required_fields = array('date1','date2');
protected $top_n = 0;
protected $non_member = array();
public function preprocess()
{
$this->report_headers = array(_('Member'), '$Total Purchased', '$Average per Receipt',
'#Receipts');
$this->title = "Fannie : Patronage over Date Range Report";
$this->header = "Patronage over Date Range Report";
if (is_numeric(FormLib::get_form_value('top_n',0))) {
$this->top_n = FormLib::get_form_value('top_n',0);
}
return parent::preprocess();
}
/* Text at the top of the report, below the standard heading.
*/
public function report_description_content()
{
$desc = array();
$line = "<p><b>Patronage over Date Range: " .
(($this->top_n > 0) ? "Top " . $this->top_n : "All") .
" Customers, by Total Purchases</b></p>";
$desc[] = $line;
//$line = "Var: " . $this->top_n . " Form:" . FormLib::get_form_value('top_n',0);
$line = "<a href='" . $_SERVER['PHP_SELF'] ."'>Start over</a>";
$desc[] = $line;
return $desc;
}
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,
sum(total) as tpurch
FROM $dlog dx
WHERE
(tdate BETWEEN ? AND ?)
AND trans_type in ('I','D','S'){$shrinkageUsers}
GROUP BY card_no
ORDER BY tpurch desc{$limit}
;";
$statement = $dbc->prepare($query);
$args = array($d1.' 00:00:00', $d2.' 23:59:59');
$result = $dbc->execute($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,
month(tdate) as mt, day(tdate) as dt, year(tdate) as yt
FROM $dlog dx
WHERE
(tdate BETWEEN ? AND ?)
AND card_no = ? AND trans_type = 'A'
GROUP BY trans_num, mt, dt, yt
ORDER BY mt, dt, yt
;";
$statement = $dbc->prepare($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->execute($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
FROM $dlog dx
WHERE
(tdate BETWEEN ? AND ?)
AND (trans_type = 'A'){$shrinkageUsers}
GROUP BY card_no
;";
$statement = $dbc->prepare($query);
$args = array($d1.' 00:00:00', $d2.' 23:59:59');
$result = $dbc->execute($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()
}
/**
Sum the quantity and total columns for a footer of one or more rows.
Also set up headers and in-table (column-head) sorting.
*/
function calculate_footers($data)
{
// no data; don't bother
if (empty($data)) {
return array();
}
/* Initial sequence of the report.
* May not be the same as the sequence of composition, driven, say,
* by an ORDER BY clause.
*/
$this->sort_column = 1; // First = 0
$this->sort_direction = 1; // 1 = ASC
$sumSales = 0.0;
$sumAvgBskt = 0.0;
$sumTransactions = 0;
foreach($data as $row) {
$sumSales += $row[1];
$sumAvgBskt += $row[2];
$sumTransactions += $row[3];
}
$rowCount = count($data);
$avgBskt = sprintf("%0.2f", ($sumSales / $sumTransactions));
$avgBskts = sprintf("%0.2f", ($sumTransactions / $rowCount));
/* Means as well as averages? */
$ret = array();
$ret[] = array('#Customers','$ Grand Total Purchases','$ Overall Average per Receipt',
'# Grand Total Receipts');
$ret[] = array(number_format($rowCount),
'$ '.number_format($sumSales,2),
'$ '.number_format($avgBskt,2),
number_format($sumTransactions)
);
$ret[] = array('','','Average Receipts per Customer','');
$ret[] = array('','',number_format($avgBskts,2),'');
if (isset($this->non_member[0])) {
$ret[] = array('Without non-member purchases:',null,null,null);
$sumSalesM = $sumSales - $this->non_member[0];
$sumTransactionsM = $sumTransactions - $this->non_member[1];
$rowCountM = $rowCount - 1;
$avgBsktM = sprintf("%0.2f", ($sumSalesM / $sumTransactionsM));
$avgBsktsM = sprintf("%0.2f", ($sumTransactionsM / $rowCountM));
$ret[] = array('#Members','$ Grand Total Purchases','$ Overall Average per Receipt',
'# Grand Total Receipts');
$ret[] = array(number_format($rowCountM),
'$ '.number_format($sumSalesM,2),
'$ '.number_format($avgBsktM,2),
number_format($sumTransactionsM)
);
$ret[] = array('','','Average Receipts per Member','');
$ret[] = array('','',number_format($avgBskts,2),'');
// Proportion of activity by Members.
$ret[] = array('','% of Purchases by Members',
'Relative size of Member Receipt',
'% of Receipts to Members');
$propPurchM = sprintf("%0.2f %%",(($sumSalesM / $sumSales)*100));
$propBsktM = sprintf("%00.2f %%",(($avgBsktM / $avgBskt)*100));
$propTransM = sprintf("%0.2f %%",(($sumTransactionsM / $sumTransactions)*100));
$ret[] = array('',$propPurchM,$propBsktM,$propTransM);
}
return $ret;
// calculate_footers()
}
function form_content()
{
ob_start();
list($lastMonday, $lastSunday) = \COREPOS\Fannie\API\lib\Dates::lastWeek();
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get" id="form1">
<!-- Left column -->
<div class="col-sm-5">
<div class="form-group">
<label>Top how many <?php echo _("members"); ?>?
<br />(Leave empty for all.)</label>
<input type="text" name="top_n" value="" class="form-control"
id="top_n" />
</div>
<div class="form-group">
<label>Start Date</label>
<input type=text id=date1 name=date1 class="form-control date-field"
value="<?php echo $lastMonday; ?>" />
</div>
<div class="form-group">
<label>End Date</label>
<input type=text id=date2 name=date2 class="form-control date-field"
value="<?php echo $lastSunday; ?>" />
</div>
<div class="form-group">
<label>Sortable
<input type="checkbox" name="sortable" />
</label>
</div>
<div class="form-group">
<p>
<button type="submit" class="btn btn-default">Prepare Report</button>
</p>
</div>
</div>
<!-- Right column -->
<div class="col-sm-5">
<p>
<br/>
<br/>
<br/>
</p>
<?php echo FormLib::date_range_picker(); ?>
</div>
</form>
<?php
$this->add_onload_command('$(\'#top_n\').focus()');
return ob_get_clean();
// form_content()
}
public function helpContent()
{
return '<p>
List ' . _("members") . ' by total purchases and number of transactions over a range of dates.
Can choose to show only the Top (highest dollar-value) <i>N</i> or all
who shopped at all.
<br />End-of-report has totals and averages with and without non-member purchases
and proportion of member to non-member purchases.
</p>';
}
}
FannieDispatch::conditionalExec();