<?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 PcDailyReport extends FannieReportPage 
{
    public $description = '[Integrated Card Reports] lists all integrated payment card transactions for a given day.';
    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();
    protected $no_jquery = true;

    public function report_description_content()
    {
        $ret = array(''); // spacer line
        if ($this->report_format == 'html') {
            $ret[] = $this->form_content();
        }

        return $ret;
    }

    protected function getTransactions($date_id, $store, $processor, $invertReturns=false)
    {
        // voids have trans_id from original dtrans record, not void dtrans record
        $invert = $invertReturns ? "'VOID','Return'" : "'VOID'";
        $mercuryQ = "
            SELECT cardType,
                CASE 
                    WHEN transType IN ('Sale', 'R.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 ({$invert}) 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=?
                AND httpCode=200
                AND (xResultMessage LIKE '%approve%' OR xResultMessage LIKE '%PENDING%' OR xResultMessage='AP')
                AND xResultMessage not like '%declined%'
                AND empNo <> 9999
                AND registerNo <> 99
                AND processor=?";
        if ($store == 1) {
            $mercuryQ .= ' AND (registerNo BETWEEN 1 AND 10 OR registerNo=31) ';
        } elseif ($store == 2) {
            $mercuryQ .= ' AND (registerNo BETWEEN 11 AND 20 OR registerNo=32) ';
        }
        $mercuryP = $this->connection->prepare($mercuryQ);
        $mercuryR = $this->connection->execute($mercuryP, array($date_id, $processor));
        $proc = array();
        while($mercuryW = $this->connection->fetchRow($mercuryR)) {
            $pos_trans_id = $mercuryW['emp'].'-'.$mercuryW['reg'].'-'.$mercuryW['trans'].'-'.$mercuryW['tid'];
            $this->integratedIDs[$pos_trans_id] = true;
            $pt_id = $mercuryW['reg'] . '-' . $mercuryW['paycardTransactionID'];
            $this->ptIDs[$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'];
            if (isset($proc[$cardType]) && isset($proc[$cardType][$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'];
            }
        }

        return $proc;
    }

    protected function procToDataset($dataset, $proc, $name)
    {
        foreach ($proc as $type => $info) {
            $record = array($name, 
                        $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;
            }
        }

        return $dataset;
    }

    public function fetch_report_data()
    {
        $date_id = date('Ymd', strtotime(FormLib::get('date', date('Y-m-d'))));
        $date_str = date('Y-m-d', strtotime(FormLib::get('date', date('Y-m-d'))));
        $store = FormLib::get('store', false);
        if ($store === false) {
            $store = Store::getIdByIp();
        }

        $dbc = $this->connection;
        $dbc->selectDB($this->config->get('TRANS_DB'));

        $dataset = array();
        $this->integratedIDs = array();
        $this->ptIDs = array();

        $recon = array('CC' => 0, 'Amex' => 0, 'Bankcard' => 0);

        $proc = $this->getTransactions($date_id, $store, 'MercuryE2E');
        $dataset = $this->procToDataset($dataset, $proc, 'Mercury');

        $proc = $this->getTransactions($date_id, $store, 'RapidConnect');
        foreach ($proc as $type => $info) {
            foreach ($info['Details'] as $issuer => $subinfo) {
                if ($issuer == 'AMEX') {
                    $recon['Amex'] += ($subinfo['Sales']['amt'] + $subinfo['Returns']['amt']);
                } else {
                    $recon['CC'] += ($subinfo['Sales']['amt'] + $subinfo['Returns']['amt']);
                }
            }
        }
        $dataset = $this->procToDataset($dataset, $proc, 'First Data');

        $proc = $this->getTransactions($date_id, $store, 'GoEMerchant', true);
        foreach ($proc as $type => $info) {
            foreach ($info['Details'] as $issuer => $subinfo) {
                if ($issuer == 'American Express') {
                    $recon['Amex'] += ($subinfo['Sales']['amt'] + $subinfo['Returns']['amt']);
                } else {
                    $recon['Bankcard'] += ($subinfo['Sales']['amt'] + $subinfo['Returns']['amt']);
                }
            }
        }
        $dataset = $this->procToDataset($dataset, $proc, 'FAPS');

        $doubleCheckP = $dbc->prepare("
            SELECT transID
            FROM PaycardTransactions
            WHERE dateID=?
                AND registerNo=?
                AND empNo=?
                AND transNo=?
                AND amount=?
                AND (xResultMessage LIKE '%approved%' OR xResultMessage LIKE '%PENDING%')
                AND xResultMessage not like '%declined%'");

        /** now get POS transaction records and check which are integrated **/
        $dlog = DTransactionsModel::selectDlog(FormLib::get('date', date('Y-m-d')));
        $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,
                    emp_no,
                    trans_no,
                    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($date.' 00:00:00', $date.' 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' && isset($this->ptIDs[$pt_id])) {
                $proc[$cardType]['Integrated'][$transType]['amt'] += $row['ttl'];
                $proc[$cardType]['Integrated'][$transType]['num'] += $row['num'];
                $this->integratedIDs[$pos_trans_id] = 'found';
            } elseif (isset($this->integratedIDs[$pos_trans_id])) {
                $proc[$cardType]['Integrated'][$transType]['amt'] += $row['ttl'];
                $proc[$cardType]['Integrated'][$transType]['num'] += $row['num'];
                $this->integratedIDs[$pos_trans_id] = 'found';
            } else {
                $dcR = $dbc->execute($doubleCheckP, array($date_id, $row['register_no'], $row['emp_no'], $row['trans_no'], $row['ttl']));
                if ($dbc->numRows($dcR) === 1 && $row['charflag'] == 'PT') {
                    $dcW = $dbc->fetchRow($dcR);
                    $pos_trans_id = $row['trans_num'] . '-' . $dcW['transID'];
                    $proc[$cardType]['Integrated'][$transType]['amt'] += $row['ttl'];
                    $proc[$cardType]['Integrated'][$transType]['num'] += $row['num'];
                    $this->integratedIDs[$pos_trans_id] = 'found';
                } 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;
            $recon['CC'] += ($non['Sales']['amt'] + $non['Returns']['amt']);
        }

        $dataset[] = array('meta'=>FannieReportPage::META_BLANK);
        foreach ($this->integratedIDs as $pos_trans_id => $found) {
            if ($found === true) {
                $trans = rtrim($pos_trans_id, '0123456789');
                $trans = rtrim($trans, '-');
                $dataset[] = array(
                    'Suspect Transaction',
                    $date_str,
                    $trans,
                    $pos_trans_id,
                );
            }
        }

        $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);

        $record = array(
            'Recon Total',
            '',
            '',
            '',
            '',
            '',
            '',
            array_sum($recon),
        );
        $record['meta'] = FannieReportPage::META_BOLD;
        $dataset[] = $record;
        foreach ($recon as $type => $amt) {
            $dataset[] = array(
                '',
                $type,
                '',
                '',
                '',
                '',
                '',
                $amt,
            );
        }

        $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 += is_numeric($row[6]) ? $row[6] : 0;
                $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\'});');
        $date = FormLib::get('date', date('Y-m-d'));
        $stores = FormLib::storePicker();
        return '<form method="get" action="PcDailyReport.php">
            <div class="col-sm-6">
            <div class="row form-group form-inline">
            <label>Change Date</label> <input type="text" name="date" id="date" 
                value="' . $date . '" class="form-control" required />
            ' . $stores['html'] . '
            <button type="submit" class="btn btn-default">Get Report</button>
            <a href="PcMonthlyReport.php">Switch to Monthly</a>
            </div>
            </div>
            </form>';
    }

}

FannieDispatch::conditionalExec();

