fannie/reports/Paycards/PcMonthlyReport.php
<?php
/*******************************************************************************
Copyright 2013 Whole 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
*********************************************************************************/
use COREPOS\Fannie\API\lib\Store;
include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
include(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}
class PcMonthlyReport extends FannieReportPage
{
public $description = '[Integrated Card Montly Reports] lists all integrated payment card transactions for a given month.';
public $report_set = 'Tenders';
public $themed = true;
protected $report_headers = array('Processor', 'Transaction Type',
'Sales (#)', 'Sales ($)',
'Returns (#)', 'Returns ($)',
'Total (#)', 'Total ($)');
protected $no_sort_but_style = true;
protected $sortable = false;
protected $title = "Fannie : Card Processing Report";
protected $header = "Card Processing Report";
protected $required_fields = array();
public function preprocess()
{
if ($this->report_format == 'html') {
$this->addJQuery();
}
return parent::preprocess();
}
public function report_description_content()
{
$ret = array(''); // spacer line
if ($this->report_format == 'html') {
$ret[] = $this->form_content();
}
return $ret;
}
public function fetch_report_data()
{
$this->month = FormLib::get('month', date('n'));
$this->year = FormLib::get('year', date('Y'));
$date1 = date('Y-m-d', mktime(0, 0, 0, $this->month, 1, $this->year));
$date2 = date('Y-m-t', mktime(0, 0, 0, $this->month, 1, $this->year));
$dateID1 = date('Ymd', mktime(0, 0, 0, $this->month, 1, $this->year));
$dateID2 = date('Ymt', mktime(0, 0, 0, $this->month, 1, $this->year));
$store = FormLib::get('store', false);
if ($store === false) {
$store = Store::getIdByIp();
}
$dbc = $this->connection;
$dbc->selectDB($this->config->get('TRANS_DB'));
$dataset = array();
$integrated_trans_ids = array();
$pt_ids = array();
/** get mercury transactions **/
// voids have trans_id from original dtrans record, not void dtrans record
$mercuryQ = "
SELECT cardType,
CASE
WHEN transType='Sale' THEN 'Sales'
WHEN transType='Return' THEN 'Returns'
WHEN transType='VOID' THEN 'Sales'
ELSE 'Unknown'
END AS transType,
issuer AS cardIssuer,
CASE WHEN transType IN ('VOID') THEN -amount ELSE amount END AS ttl,
CASE WHEN transType='VOID' THEN -1 ELSE 1 END AS num,
empNo AS emp,
registerNo AS reg,
transNo AS trans,
CASE WHEN transType='VOID' THEN transID+1 ELSE transID END AS tid,
paycardTransactionID
FROM PaycardTransactions
WHERE dateID BETWEEN ? AND ?
AND httpCode=200
AND xResultMessage LIKE '%approve%'
AND xResultMessage not like '%declined%'
AND empNo <> 9999
AND registerNo <> 99
AND processor='MercuryE2E'";
if ($store == 1) {
$mercuryQ .= ' AND registerNo BETWEEN 1 AND 10 ';
} elseif ($store == 2) {
$mercuryQ .= ' AND registerNo BETWEEN 11 AND 20 ';
}
$mercuryP = $dbc->prepare($mercuryQ);
$mercuryR = $dbc->execute($mercuryP, array($dateID1, $dateID2));
$proc = array();
while($mercuryW = $dbc->fetch_row($mercuryR)) {
$pos_trans_id = $mercuryW['emp'].'-'.$mercuryW['reg'].'-'.$mercuryW['trans'].'-'.$mercuryW['tid'];
$integrated_trans_ids[$pos_trans_id] = true;
$pt_id = $mercuryW['reg'] . '-' . $mercuryW['paycardTransactionID'];
$pt_ids[$pt_id] = true;
$cardType = $mercuryW['cardType'];
if (!isset($proc[$cardType])) {
$proc[$cardType] = array(
'Sales' => array('amt'=>0.0, 'num'=>0),
'Returns' => array('amt'=>0.0, 'num'=>0),
'Details' => array(),
);
}
$transType = $mercuryW['transType'];
$proc[$cardType][$transType]['amt'] += $mercuryW['ttl'];
$proc[$cardType][$transType]['num'] += $mercuryW['num'];
$issuer = $mercuryW['cardIssuer'];
if (!isset($proc[$cardType]['Details'][$issuer])) {
$proc[$cardType]['Details'][$issuer] = array(
'Sales' => array('amt'=>0.0, 'num'=>0),
'Returns' => array('amt'=>0.0, 'num'=>0),
);
}
$proc[$cardType]['Details'][$issuer][$transType]['amt'] += $mercuryW['ttl'];
$proc[$cardType]['Details'][$issuer][$transType]['num'] += $mercuryW['num'];
}
foreach($proc as $type => $info) {
$record = array('MERCURY',
$type,
$info['Sales']['num'],
sprintf('%.2f', $info['Sales']['amt']),
$info['Returns']['num'],
sprintf('%.2f', $info['Returns']['amt']),
$info['Sales']['num'] + $info['Returns']['num'],
sprintf('%.2f', $info['Sales']['amt'] + $info['Returns']['amt']),
);
$record['meta'] = FannieReportPage::META_BOLD;
$dataset[] = $record;
foreach($info['Details'] as $issuer => $subinfo) {
$record = array('',
$issuer,
$subinfo['Sales']['num'],
sprintf('%.2f', $subinfo['Sales']['amt']),
$subinfo['Returns']['num'],
sprintf('%.2f', $subinfo['Returns']['amt']),
$subinfo['Sales']['num'] + $subinfo['Returns']['num'],
sprintf('%.2f', $subinfo['Sales']['amt'] + $subinfo['Returns']['amt']),
);
$dataset[] = $record;
}
}
/** end mercury transactions **/
/** get GoE / FAPS transactions **/
// voids have trans_id from original dtrans record, not void dtrans record
$fapsQ = "
SELECT cardType,
CASE
WHEN transType='Sale' THEN 'Sales'
WHEN transType='Return' THEN 'Returns'
WHEN transType='VOID' THEN 'Sales'
ELSE 'Unknown'
END AS transType,
issuer AS cardIssuer,
CASE WHEN transType IN ('Return', 'VOID') THEN -amount ELSE amount END AS ttl,
CASE WHEN transType='VOID' THEN -1 ELSE 1 END AS num,
empNo AS emp,
registerNo AS reg,
transNo AS trans,
CASE WHEN transType='VOID' THEN transID+1 ELSE transID END AS tid,
paycardTransactionID
FROM PaycardTransactions
WHERE dateID BETWEEN ? AND ?
AND httpCode=200
AND (xResultMessage LIKE '%approved%' OR xResultMessage LIKE '%PENDING%')
AND xResultMessage not like '%declined%'
AND empNo <> 9999
AND registerNo <> 99
AND processor='GoEMerchant'";
if ($store == 1) {
$fapsQ .= ' AND registerNo BETWEEN 1 AND 6 ';
} elseif ($store == 2) {
$fapsQ .= ' AND registerNo BETWEEN 11 AND 15 ';
}
$fapsP = $dbc->prepare($fapsQ);
$fapsR = $dbc->execute($fapsP, array($dateID1, $dateID2));
$proc = array(
'Sales' => array('amt'=>0.0, 'num'=>0),
'Returns' => array('amt'=>0.0, 'num'=>0),
'Details' => array(),
);
while ($fapsW = $dbc->fetch_row($fapsR)) {
$pos_trans_id = $fapsW['emp'].'-'.$fapsW['reg'].'-'.$fapsW['trans'].'-'.$fapsW['tid'];
$integrated_trans_ids[$pos_trans_id] = true;
$pt_id = $fapsW['reg'] . '-' . $fapsW['paycardTransactionID'];
$pt_ids[$pt_id] = true;
$transType = $fapsW['transType'];
$issuer = $fapsW['cardIssuer'];
$proc[$transType]['amt'] += $fapsW['ttl'];
$proc[$transType]['num'] += $fapsW['num'];
if (!isset($proc['Details'][$issuer])) {
$proc['Details'][$issuer] = array(
'Sales' => array('amt'=>0.0, 'num'=>0),
'Returns' => array('amt'=>0.0, 'num'=>0),
);
}
$proc['Details'][$issuer][$transType]['amt'] += $fapsW['ttl'];
$proc['Details'][$issuer][$transType]['num'] += $fapsW['num'];
}
$record = array('FAPS',
'Credit',
$proc['Sales']['num'],
sprintf('%.2f', $proc['Sales']['amt']),
$proc['Returns']['num'],
sprintf('%.2f', $proc['Returns']['amt']),
$proc['Sales']['num'] + $proc['Returns']['num'],
sprintf('%.2f', $proc['Sales']['amt'] + $proc['Returns']['amt']),
);
$record['meta'] = FannieReportPage::META_BOLD;
$dataset[] = $record;
foreach($proc['Details'] as $issuer => $info) {
$record = array('',
$issuer,
$info['Sales']['num'],
sprintf('%.2f', $info['Sales']['amt']),
$info['Returns']['num'],
sprintf('%.2f', $info['Returns']['amt']),
$info['Sales']['num'] + $info['Returns']['num'],
sprintf('%.2f', $info['Sales']['amt'] + $info['Returns']['amt']),
);
$dataset[] = $record;
}
/** end get FAPS / goE **/
/** now get POS transaction records and check which are integrated **/
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "SELECT
CASE WHEN trans_subtype IN ('CC', 'AX') AND description='Debit Card' THEN 'Debit'
WHEN trans_subtype IN ('CC', 'AX') AND description<>'Debit Card' THEN 'Credit'
WHEN trans_subtype = 'EF' THEN 'EBT Food'
WHEN trans_subtype = 'EC' THEN 'EBT Cash'
ELSE 'Unknown' END as cardType,
CASE
WHEN trans_status='V' and total < 0 THEN 'Returns'
WHEN trans_status='V' AND total >= 0 THEN 'Sales'
WHEN total < 0 THEN 'Sales'
ELSE 'Returns'
END as transType,
'n/a' AS cardIssuer,
-total AS ttl,
CASE WHEN trans_status='V' THEN -1 ELSE 1 END AS num,
trans_num,
trans_id,
numflag,
charflag,
register_no
FROM $dlog AS d
WHERE tdate BETWEEN ? AND ?
AND trans_type = 'T'
AND total <> 0
AND " . DTrans::isStoreID($store, 'd') . "
AND trans_subtype IN ('CC', 'AX', 'EF', 'EC')";
$prep = $dbc->prepare($query);
$date = FormLib::get('date', date('Y-m-d'));
$result = $dbc->execute($prep, array($date1.' 00:00:00', $date2.' 23:59:59', $store));
$proc = array();
while($row = $dbc->fetch_row($result)) {
$cardType = $row['cardType'];
if (!isset($proc[$cardType])) {
$proc[$cardType] = array(
'Sales' => array('amt'=>0.0, 'num'=>0),
'Returns' => array('amt'=>0.0, 'num'=>0),
'Integrated' => array(
'Sales' => array('amt'=>0.0, 'num'=>0),
'Returns' => array('amt'=>0.0, 'num'=>0),
),
'Non' => array(
'Sales' => array('amt'=>0.0, 'num'=>0),
'Returns' => array('amt'=>0.0, 'num'=>0),
),
);
}
$transType = $row['transType'];
$proc[$cardType][$transType]['amt'] += $row['ttl'];
$proc[$cardType][$transType]['num'] += $row['num'];
$pos_trans_id = $row['trans_num'].'-'.$row['trans_id'];
// ebt trans_id is off by one from fsEligible record
if ($cardType == 'EBT Food') {
$pos_trans_id = $row['trans_num'].'-'. ($row['trans_id']-1);
}
$pt_id = $row['register_no'] . '-' . $row['numflag'];
if ($row['charflag'] == 'PT') {
$proc[$cardType]['Integrated'][$transType]['amt'] += $row['ttl'];
$proc[$cardType]['Integrated'][$transType]['num'] += $row['num'];
} else {
$proc[$cardType]['Non'][$transType]['amt'] += $row['ttl'];
$proc[$cardType]['Non'][$transType]['num'] += $row['num'];
}
}
foreach($proc as $type => $info) {
$non = $info['Non'];
if ($non['Sales']['amt'] == 0 && $non['Returns']['amt'] == 0) {
continue;
}
$record = array('NON-INTEGRATED',
$type,
$non['Sales']['num'],
sprintf('%.2f', $non['Sales']['amt']),
$non['Returns']['num'],
sprintf('%.2f', $non['Returns']['amt']),
$non['Sales']['num'] + $non['Returns']['num'],
sprintf('%.2f', $non['Sales']['amt'] + $non['Returns']['amt']),
);
$record['meta'] = FannieReportPage::META_BOLD;
$dataset[] = $record;
}
$dataset[] = array('meta'=>FannieReportPage::META_BLANK);
foreach($proc as $type => $info) {
$record = array('POS Total',
$type,
$info['Sales']['num'],
sprintf('%.2f', $info['Sales']['amt']),
$info['Returns']['num'],
sprintf('%.2f', $info['Returns']['amt']),
$info['Sales']['num'] + $info['Returns']['num'],
sprintf('%.2f', $info['Sales']['amt'] + $info['Returns']['amt']),
);
$record['meta'] = FannieReportPage::META_BOLD;
$dataset[] = $record;
$int = $info['Integrated'];
$record = array('',
'Integrated',
$int['Sales']['num'],
sprintf('%.2f', $int['Sales']['amt']),
$int['Returns']['num'],
sprintf('%.2f', $int['Returns']['amt']),
$int['Sales']['num'] + $int['Returns']['num'],
sprintf('%.2f', $info['Sales']['amt'] + $int['Returns']['amt']),
);
$dataset[] = $record;
$non = $info['Non'];
$record = array('',
'Non-Integrated',
$non['Sales']['num'],
sprintf('%.2f', $non['Sales']['amt']),
$non['Returns']['num'],
sprintf('%.2f', $non['Returns']['amt']),
$non['Sales']['num'] + $non['Returns']['num'],
sprintf('%.2f', $non['Sales']['amt'] + $non['Returns']['amt']),
);
$dataset[] = $record;
}
$dataset[] = array('meta'=>FannieReportPage::META_BLANK);
return $dataset;
}
public function calculate_footers($data) {
$pN = 0;
$pS = 0.0;
$tN = 0;
$tS = 0.0;
foreach($data as $row) {
if (!isset($row['meta']) || $row['meta'] != FannieReportPage::META_BOLD) {
continue;
}
if ($row[0] == 'POS Total') {
$tN += $row[6];
$tS += $row[7];
} else {
$pN += $row[6];
$pS += $row[7];
}
}
return array(
'',
'Submitted Total',
$pN,
sprintf('%.2f', $pS),
'',
'Tendered Total',
$tN,
sprintf('%.2f', $tS),
);
}
public function form_content()
{
$this->add_onload_command('$(\'#date\').datepicker({dateFormat:\'yy-mm-dd\'});');
$stores = FormLib::storePicker();
$this->month = FormLib::get('month', date('n'));
$this->year = FormLib::get('year', date('Y'));
return '<form method="get" action="PcMonthlyReport.php">
<div class="col-sm-6">
<div class="row form-group form-inline">
<label>Month</label> <input type="text" name="month" value="' . $this->month . '"
class="form-control" required />
<label>Year</label> <input type="text" name="year" value="' . $this->year . '"
class="form-control" required />
' . $stores['html'] . '
<button type="submit" class="btn btn-default">Get Report</button>
<a href="PcDailyReport.php">Switch to Daily</a>
</div>
</div>
</form>';
}
}
FannieDispatch::conditionalExec();