fannie/mem/patronage/CreatePatronageSnapshot.php
<?php
/*******************************************************************************
Copyright 2011 Whole Foods Co-op, Duluth, MN
This file is part of CORE-POS.
IT CORE 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.
IT CORE 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 CreatePatronageSnapshot extends FannieRESTfulPage
{
protected $header = 'Create Patronage Snapshot';
protected $title = 'Create Patronage Snapshot';
public $themed = true;
public $description = '[Patronage Snapshot] extracts a year of summarized transaction data into a working table
to perform further patronage calculations and adjustments.';
public function preprocess()
{
$this->__routes[] = 'get<date1><date2><mtype><stype>';
return parent::preprocess();
}
protected function get_date1_date2_mtype_stype_handler()
{
global $FANNIE_TRANS_DB, $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_TRANS_DB);
include(__DIR__ . '/../../install/db.php');
$mtype = "(";
$mArgs = array();
foreach ($this->mtype as $m) {
$mtype .= '?,';
$mArgs[] = (int)$m;
}
$mtype = rtrim($mtype,",").")";
$stype = '(';
$sArgs = array();
foreach ($this->stype as $s) {
$stype .= '?,';
$sArgs[] = (int)$s;
}
$stype = rtrim($stype, ',') . ')';
if (count($sArgs) == 0) {
$stype = '(?)';
$sArgs = array('-9999');
}
$dlog = DTransactionsModel::selectDlog($this->date1, $this->date2);
if ($dbc->table_exists("dlog_patronage")) {
$drop = $dbc->prepare("DROP TABLE dlog_patronage");
$dbc->execute($drop);
}
$create = $dbc->prepare('CREATE TABLE dlog_patronage (card_no INT, trans_type VARCHAR(2),
trans_subtype VARCHAR(2), total DECIMAL(10,2), min_year INT, max_year INT,
primary key (card_no, trans_type, trans_subtype))');
$dbc->execute($create);
$insQ = sprintf("
INSERT INTO dlog_patronage
SELECT d.card_no,
trans_type,
trans_subtype,
sum(total),
YEAR(MIN(tdate)) AS firstDate,
YEAR(MAX(tdate)) AS lastDate
FROM %s AS d
LEFT JOIN %s%scustdata AS c ON c.CardNo=d.card_no AND c.personNum=1
LEFT JOIN %s%ssuspensions AS s ON d.card_no=s.cardno
LEFT JOIN %s%sMasterSuperDepts AS m ON d.department=m.dept_ID
WHERE d.trans_type IN ('I','D','S','T')
AND d.total <> 0
AND (s.memtype1 IN %s OR c.memType IN %s)
AND (m.superID IS NULL OR m.superID NOT IN %s)
AND d.tdate BETWEEN ? AND ?
GROUP BY d.card_no, trans_type, trans_subtype",
$dlog,$FANNIE_OP_DB,$dbc->sep(),
$FANNIE_OP_DB,$dbc->sep(),
$FANNIE_OP_DB,$dbc->sep(),
$mtype,$mtype,
$stype);
$args = $mArgs;
foreach ($mArgs as $m) $args[] = $m; // need them twice
foreach ($sArgs as $s) $args[] = $s;
$args[] = $this->date1 . ' 00:00:00';
$args[] = $this->date2 . ' 23:59:59';
$prep = $dbc->prepare($insQ);
$worked = $dbc->execute($prep,$args);
if ($worked) {
$this->add_onload_command("showBootstrapAlert('#alert-area', 'success', 'Patronage Snapshot Created');\n");
} else {
$this->add_onload_command("showBootstrapAlert('#alert-area', 'danger', 'Error creating snapshot');\n");
}
return true;
}
protected function get_date1_date2_mtype_stype_view()
{
return '
<div id="alert-area"></div>
<p>
<button type="button" class="btn btn-default"
onclick="location=\'index.php\'; return false;">Patronage Menu</button>
<button type="button" class="btn btn-default"
onclick="location=\'CreatePatronageSnapshot.php\'; return false;">Create a new Snapshot</button>
</p>';
}
public function get_view()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
ob_start();
?>
<div class="well">
Step one: gather member transactions for the year. Dates specify the start and
end of the year. Inactive and terminated memberships will be included if their type,
prior to suspension, matches one of the requested types.
<b>Note: this step may take a couple minutes</b>.
</div>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
<label>Start Date</label>
<input type="text" name="date1" id="date1" class="form-control date-field" required />
<label>End Date</label>
<input type="text" name="date2" id="date2" class="form-control date-field" required />
<label>Member Type(s) to include</label>
<div class="form-group well">
<?php
$typeQ = $dbc->prepare("
SELECT memtype,
memDesc,
custdataType
FROM ".$FANNIE_OP_DB.$dbc->sep()."memtype
ORDER BY memtype");
$typeR = $dbc->execute($typeQ);
while ($typeW = $dbc->fetch_row($typeR)) {
printf('<input class="checkbox-inline" type="checkbox" value="%d" name="mtype[]"
id="mtype%d" %s /> <label for="mtype%d">%s</label><br />',
$typeW['memtype'],$typeW['memtype'],
(strtoupper($typeW['custdataType']) == 'PC' ? 'checked' : ''),
$typeW['memtype'],$typeW['memDesc']
);
}
echo '</div>';
echo '<label>Super Department(s) to exclude from purchases</label>
<div class="form-group well">';
$msd = $dbc->query('
SELECT superID,
super_name
FROM MasterSuperDepts
GROUP BY superID,
super_name
ORDER BY super_name');
while ($m = $dbc->fetchRow($msd)) {
printf('<input class="checkbox-inline" type="checkbox" value="%d" name="stype[]"
id="style%d" %s /> <label for="stype%d">%s</label><br />',
$m['superID'], $m['superID'],
($m['superID'] == 0 ? 'checked' : ''),
$m['superID'], $m['super_name']
);
}
echo '</div>';
echo '<p><button type="submit" class="btn btn-default">Create Table</button></p>';
echo '</form>';
return ob_get_clean();
}
public function helpContent()
{
return '<p>The first step of calculating patronage involves
compiling member transaction data for the year. This will
speed up many subsequent steps since it can be a lot of
information to go through.</p>
<p>The fiscal year is defined by the start and end dates.
Only members in the select member type(s) will be included
in the patronage calculation. Sales that are in the
selected super departments will be <em>omitted</em> from
members\' total purchases.</p>
<p>Member purchase totals are organized by transaction
type and subtype. Records whose department belong to
super department number zero are excluded in keeping with
the convention that super department zero contains
non-inventory sales.</p>';
}
}
FannieDispatch::conditionalExec();