fannie/modules/plugins2.0/StatementsPlugin/StatementsPluginIndex.php
<?php
/*******************************************************************************
Copyright 2014 Whole Foods Co-op
This file is part of IT CORE.
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_once(dirname(__FILE__) . '/../../../config.php');
if (!class_exists('FannieAPI')) {
include(dirname(__FILE__) . '/../../../classlib2.0/FannieAPI.php');
}
class StatementsPluginIndex extends FannieRESTfulPage
{
public $page_set = 'Plugin :: StatementsPlugin';
protected $header = 'Statements';
protected $title = 'Statements';
public function preprocess()
{
$this->__routes[] = 'get<equityTab>';
$this->__routes[] = 'get<arTab>';
$this->__routes[] = 'get<termTab>';
$this->__routes[] = 'get<checkTab>';
$this->__routes[] = 'post<csv>';
return parent::preprocess();
}
public function get_arTab_handler()
{
echo $this->arForm();
return false;
}
public function get_equityTab_handler()
{
echo $this->reminderForm();
return false;
}
public function get_termTab_handler()
{
echo $this->termForm();
return false;
}
public function get_checkTab_handler()
{
echo $this->checkForm();
return false;
}
public function post_csv_handler()
{
$json = json_decode($this->csv);
header('Content-Type: application/ms-excel');
header('Content-Disposition: attachment; filename="'.$json->name.'.csv"');
foreach ($json->records as $record) {
for ($i=0; $i<count($record); $i++) {
echo '"' . $record[$i] . '"';
echo ($i < count($record)-1) ? ',' : "\r\n";
}
}
return false;
}
public function get_view()
{
$ret = '<div id="tabs">';
$ret .= '<ul>';
$ret .= '<li><a href="#welcomeTab">Paid In Full</a></li>';
$ret .= '<li><a href="' . $_SERVER['PHP_SELF'] . '?equityTab=1">Equity Reminders</a></li>';
$ret .= '<li><a href="' . $_SERVER['PHP_SELF'] . '?arTab=1">AR Notices</a></li>';
$ret .= '<li><a href="' . $_SERVER['PHP_SELF'] . '?termTab=1">Term Letters</a></li>';
$ret .= '<li><a href="' . $_SERVER['PHP_SELF'] . '?checkTab=1">Check Pick Ups</a></li>';
$ret .= '</ul>';
$ret .= '<div id="welcomeTab">';
$ret .= $this->welcomeForm();
$ret .= '</div>';
$ret .= '</div>';
$this->add_onload_command('$(\'#tabs\').tabs();');
return $ret;
}
private function welcomeForm()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$cutoff = mktime(0, 0, 0, date('n')-2, 1, date('Y'));
$query = 'SELECT m.card_no,
c.LastName,
' . $dbc->monthdiff($dbc->now(), 'h.mostRecent') . ' AS monthdiff
FROM memDates AS m
INNER JOIN custdata AS c ON m.card_no=c.CardNo AND c.personNum=1
INNER JOIN ' . $FANNIE_TRANS_DB . $dbc->sep() . 'equity_live_balance AS e ON m.card_no=e.memnum
INNER JOIN ' . $FANNIE_TRANS_DB . $dbc->sep() . 'equity_history_sum AS h ON m.card_no=h.card_no
WHERE h.mostRecent >= ?
AND e.payments >= 100';
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, array(date('Y-m-d', $cutoff)));
$opt_sets = array('', '', '');
while ($row = $dbc->fetch_row($result)) {
$option = sprintf('<option value="%d">%d %s</option>',
$row['card_no'], $row['card_no'], $row['LastName']);
$opt_sets[$row['monthdiff']] .= $option;
}
$ret = '<form id="welcomeForm" action="StatementsPluginPostCards.php" method="post">';
$ret .= '<select onchange="$(\'#welcomeAccounts\').html($(this.value).html());">';
$ret .= '<option value="#welcomeSet0">This Month</option>';
$ret .= '<option value="#welcomeSet1">Last Month</option>';
$ret .= '<option value="#welcomeSet2">Two Months Ago</option>';
$ret .= '</select>';
$ret .= '<button type="button" onclick="$(\'#welcomeAccounts option\').each(function(){$(this).attr(\'selected\', \'selected\');});
return false;">Select All</button>';
$ret .= '<button type="submit">Print</button>';
$ret .= '<button type="button" onclick="exportCSV(\'welcome\', \'#welcomeAccounts\');">Export List</button>';
$ret .= '<br />';
$ret .= '<select id="welcomeAccounts" name="id[]" multiple size="20">';
$ret .= $opt_sets[0];
$ret .= '</select>';
$ret .= '</form>';
for ($i=0; $i<3; $i++) {
$ret .= '<div id="welcomeSet' . $i . '" style="display:none;">';
$ret .= $opt_sets[$i];
$ret .= '</div>';
}
return $ret;
}
private function reminderForm()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$cutoff = mktime(0, 0, 0, date('n')+2, 1, date('Y'));
$query = 'SELECT m.card_no,
c.LastName,
' . $dbc->monthdiff($dbc->now(), 'm.end_date') . ' AS monthdiff
FROM memDates AS m
INNER JOIN custdata AS c ON m.card_no=c.CardNo AND c.personNum=1
INNER JOIN ' . $FANNIE_TRANS_DB . $dbc->sep() . 'equity_live_balance AS e ON m.card_no=e.memnum
WHERE m.end_date BETWEEN ? AND ?
AND c.Type NOT IN (\'REG\', \'TERM\', \'INACT2\')
AND e.payments < 100';
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, array(date('Y-m-01 00:00:00'), date('Y-m-t 23:59:59', $cutoff)));
$opt_sets = array('', '', '');
while ($row = $dbc->fetch_row($result)) {
$option = sprintf('<option value="%d">%d %s</option>',
$row['card_no'], $row['card_no'], $row['LastName']);
$opt_sets[abs($row['monthdiff'])] .= $option;
}
$ret = '<form id="reminderForm" action="StatementsPluginPostCards.php" method="post">';
$ret .= '<select onchange="$(\'#reminderAccounts\').html($(this.value).html());">';
$ret .= '<option value="#reminderSet0">This Month</option>';
$ret .= '<option value="#reminderSet1">Next Month</option>';
$ret .= '<option value="#reminderSet2">Two Months</option>';
$ret .= '</select>';
$ret .= '<button type="button" onclick="$(\'#reminderAccounts option\').each(function(){$(this).attr(\'selected\', \'selected\');});
return false;">Select All</button>';
$ret .= '<button type="submit">Print</button>';
$ret .= '<button type="button" onclick="exportCSV(\'reminder\', \'#reminderAccounts\');">Export List</button>';
$ret .= '<br />';
$ret .= '<select id="reminderAccounts" name="id[]" multiple size="20">';
$ret .= $opt_sets[0];
$ret .= '</select>';
$ret .= '</form>';
for ($i=0; $i<3; $i++) {
$ret .= '<div id="reminderSet' . $i . '" style="display:none;">';
$ret .= $opt_sets[$i];
$ret .= '</div>';
}
return $ret;
}
private function checkForm()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$res = $dbc->query("SELECT * FROM CheckPickUps");
$opts = '';
while ($row = $dbc->fetchRow($res)) {
$opts .= sprintf('<option value="%d">%s (%s)</option>',
$row['checkPickUpID'], $row['company'], $row['name']);
}
return <<<HTML
<form id="checkForm" action="StatementsPluginEmail.php" method="post">
<input type="hidden" name="check-pick-ups" value="1" />
<button type="button" onclick="jQuery('#checkForm option').each(function(){jQuery(this).prop('selected', true);});
return false;">Select All</button>
<button type="submit">Email</button>
<br />
<select id="arAccounts" name="id[]" multiple size="20">
{$opts}
</select>
</form>
HTML;
}
private function arForm()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$opt_sets = array('', '', '');
$q1 = 'SELECT a.card_no,
c.LastName,
m.email_1
FROM ' . $FANNIE_TRANS_DB . $dbc->sep() . 'ar_live_balance AS a
INNER JOIN custdata AS c ON a.card_no=c.CardNo AND c.personNum=1
LEFT JOIN suspensions AS s ON a.card_no=s.cardno
INNER JOIN meminfo AS m ON a.card_no=m.card_no
WHERE c.Type <> \'TERM\'
AND (c.memType=2 OR s.memtype1=2)
AND a.balance > 0
GROUP BY a.card_no,
c.LastName';
$r1 = $dbc->query($q1);
while ($row = $dbc->fetch_row($r1)) {
if (filter_var($row['email_1'], FILTER_VALIDATE_EMAIL)) {
$row['LastName'] .= ' ✉';
}
$option = sprintf('<option value="%d">%d %s</option>',
$row['card_no'], $row['card_no'], $row['LastName']);
$opt_sets[0] .= $option;
}
$q2 = 'SELECT a.cardno AS card_no,
c.LastName,
m.email_1
FROM ' . $FANNIE_TRANS_DB . $dbc->sep() . 'AR_EOM_Summary AS a
INNER JOIN custdata AS c ON a.cardno=c.CardNo AND c.personNum=1
LEFT JOIN suspensions AS s ON a.cardno=s.cardno
INNER JOIN meminfo AS m ON a.cardno=m.card_no
WHERE c.Type <> \'TERM\'
AND (c.memType=2 OR s.memtype1=2)
AND (a.lastMonthBalance <> 0 OR a.lastMonthCharges <> 0 OR a.lastMonthPayments <> 0)';
$r2 = $dbc->query($q2);
while ($row = $dbc->fetch_row($r2)) {
if (filter_var($row['email_1'], FILTER_VALIDATE_EMAIL)) {
$row['LastName'] .= ' ✉';
}
$option = sprintf('<option value="%d">%d %s</option>',
$row['card_no'], $row['card_no'], $row['LastName']);
$opt_sets[1] .= $option;
}
$q2 = 'SELECT a.b2bInvoiceID AS card_no,
c.LastName,
m.email_1
FROM ' . $FANNIE_TRANS_DB . $dbc->sep() . 'B2BInvoices AS a
INNER JOIN custdata AS c ON a.cardNo=c.CardNo AND c.personNum=1
LEFT JOIN suspensions AS s ON a.cardNo=s.cardno
INNER JOIN meminfo AS m ON a.cardNo=m.card_no
WHERE c.Type <> \'TERM\'
AND (c.memType=2 OR s.memtype1=2)
AND (a.isPaid=0)';
$r2 = $dbc->query($q2);
while ($row = $dbc->fetch_row($r2)) {
if (filter_var($row['email_1'], FILTER_VALIDATE_EMAIL)) {
$row['LastName'] .= ' ✉';
}
$option = sprintf('<option value="b2b%d">%d %s</option>',
$row['card_no'], $row['card_no'], $row['LastName']);
$opt_sets[2] .= $option;
}
$ret = '<form id="arForm" action="StatementsPluginBusiness.php" method="post">';
$ret .= '<select onchange="$(\'#arAccounts\').html($(this.value).html());">';
$ret .= '<option value="#arSet0">Business (Any Balance)</option>';
$ret .= '<option value="#arSet1">Business (EOM)</option>';
$ret .= '<option value="#arSet2">B2B Invoices</option>';
$ret .= '</select>';
$ret .= '<button type="button" onclick="$(\'#arAccounts option\').each(function(){$(this).prop(\'selected\', true);});
return false;">Select All</button>';
$ret .= '<button type="button" onclick="$(\'#arAccounts option\').each(function(){
if (/\u2709/.test($(this).html())) {
$(this).prop(\'selected\', true);
} else {
$(this).prop(\'selected\', false);
}
}); return false;">Select Email</button>';
$ret .= '<button type="button" onclick="$(\'#arAccounts option\').each(function(){
if (/\u2709/.test($(this).html()) == false) {
$(this).prop(\'selected\', true);
} else {
$(this).prop(\'selected\', false);
}
}); return false;">Select Paper</button>';
$ret .= '<button onclick="$(\'#arForm\').attr(\'action\', \'StatementsPluginBusiness.php\');" type="submit">Print</button>';
$ret .= '<button onclick="$(\'#arForm\').attr(\'action\', \'StatementsPluginEmail.php\');" type="submit">Email</button>';
$ret .= '<button type="button" onclick="exportCSV(\'ar_statements\', \'#arAccounts\');">Export List</button>';
$ret .= '<br />';
$ret .= '<select id="arAccounts" name="id[]" multiple size="20">';
$ret .= $opt_sets[0];
$ret .= '</select>';
$ret .= '</form>';
for ($i=0; $i<3; $i++) {
$ret .= '<div id="arSet' . $i . '" style="display:none;">';
$ret .= $opt_sets[$i];
$ret .= '</div>';
}
return $ret;
}
private function termForm()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$query = "SELECT c.CardNo AS card_no,
c.LastName
FROM custdata AS c
LEFT JOIN suspensions AS s ON c.CardNo=s.cardno
WHERE c.personNum=1
AND c.Type IN ('INACT','INACT2')
AND s.reasoncode & 64 <> 0
ORDER BY c.CardNo";
$r1 = $dbc->query($query);
$options = '';
while ($row = $dbc->fetch_row($r1)) {
$option = sprintf('<option value="%d">%d %s</option>',
$row['card_no'], $row['card_no'], $row['LastName']);
$options .= $option;
}
$ret = '<form id="termForm" action="StatementsPluginTerm.php" method="post">';
$ret .= '<button type="button" onclick="$(\'#termAccounts option\').each(function(){$(this).attr(\'selected\', \'selected\');});
return false;">Select All</button>';
$ret .= '<button type="submit">Print</button>';
$ret .= '<button type="button" onclick="exportCSV(\'term_letters\', \'#termAccounts\');">Export List</button>';
$ret .= '<br />';
$ret .= '<select id="termAccounts" name="id[]" multiple size="20">';
$ret .= $options;
$ret .= '</select>';
$ret .= '</form>';
return $ret;
}
public function javascript_content()
{
ob_start();
?>
function exportCSV(name, select_elem)
{
var data = []
$(select_elem+' option').each(function(){
var record = [
$(this).val(),
$(this).html()
];
data[data.length] = record;
});
var obj = {
name: name+".csv",
records: data
};
var form = $('<form method="post"/>');
var field = $('<input name="csv" type="hidden"/>').val(JSON.stringify(obj));
form.append(field);
form.appendTo('body').submit();
}
<?php
return ob_get_clean();
}
}
FannieDispatch::conditionalExec();