fannie/reports/NewMemsByZip/NewMemsByZipReport.php
<?php
/*******************************************************************************
Copyright 201666666e Foods Co-op
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
*********************************************************************************/
include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
include_once(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}
class NewMemsByZipReport extends FannieReportPage
{
public $description = '[New Members By Zip Code] lists the number of new owners by zip code for a given period';
public $report_set = 'Membership';
protected $title = "Fannie : New Members By Zip Code";
protected $header = "New Members By Zip Code";
protected $required_fields = array('date1', 'date2', 'dept');
protected $report_headers = array('Zip Code', 'Number of Members', 'Total Equity');
protected $sort_direction = 1;
protected $sort_column = 1;
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
try {
$args = array($this->form->date1, $this->form->date2, $this->form->dept);
} catch (Exception $ex) {
return array();
}
$store = FormLib::get('store', 0);
$args[] = $store;
$dlog = DTransactionsModel::selectDLog($this->form->date1, $this->form->date2);
$prep = $dbc->prepare('
SELECT LEFT(m.zip, 5) AS zip,
COUNT(DISTINCT d.card_no) AS numMembers,
SUM(total) AS ttl
FROM ' . $dlog . ' AS d
INNER JOIN meminfo AS m ON d.card_no=m.card_no
WHERE d.tdate BETWEEN ? AND ?
AND d.department=?
AND ' . DTrans::isStoreID($store, 'd') . '
GROUP BY LEFT(m.zip, 5)
');
$res = $dbc->execute($prep, $args);
$data = array();
while ($row = $dbc->fetchRow($res)) {
$data[] = $this->rowToRecord($row);
}
return $data;
}
private function rowToRecord($row)
{
return array(
$row['zip'],
$row['numMembers'],
sprintf('%.2f', $row['ttl']),
);
}
public function form_content()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$ret = preg_match_all("/[0-9]+/",$this->config->get('EQUITY_DEPARTMENTS'),$depts);
if ($ret == 0){
return '<div class="alert alert-danger">Error: can\'t read Equity department definitions</div>';
}
$eq_depts = array_pop($depts);
list($inStr, $args) = $dbc->safeInClause($eq_depts);
$prep = $dbc->prepare('SELECT dept_no, dept_name FROM departments WHERE dept_no IN (' . $inStr . ') ORDER BY dept_no');
$res = $dbc->execute($prep, $args);
$dOpts = '';
while ($row = $dbc->fetchRow($res)) {
$dOpts .= sprintf('<option value="%d">%s</option>', $row['dept_no'], $row['dept_name']);
}
$stores = FormLib::storePicker();
$dates = FormLib::standardDateFields();
return <<<HTML
<form method="get">
<div class="col-sm-5">
<div class="form-group">
<label>Made purchase in</label>
<select name="dept" class="form-control">
{$dOpts}
</select>
</div>
<div class="form-group">
<label>Store</label>
{$stores['html']}
</div>
<p>
<button type="submit" class="btn btn-default btn-core">Submit</button>
</p>
</div>
{$dates}
</form>
HTML;
}
}
FannieDispatch::conditionalExec();