<?php
/*******************************************************************************

    Copyright 2009 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

*********************************************************************************/

/* --FUNCTIONALITY- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 * Show total sales by hour for today from dlog.
 * Show total of each type of tax for the day.
 * Offer dropdown of superdepartments and, on-select, display the same report for
 *  that superdept only
 *  showing the proportion of sales for the hour and day its sales represent.
 *  For each tax show the proportion of total tax of that type
 *   coming from the superdept.
 * This page extends FanniePage rather than FannieReportPage because it is
 *  is simpler than most reports and would be encumbered by the FannieReportPage
 *  structure.
*/

/* --COMMENTS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 * 28Nov13 EL card_no=0 are apparently all members, so OK as is.
 * 23Jul13 EL Commented code for lane2 times-in-future problem.
 *  2Jul13 EL Contains some development comments and apparatus.
*/

include(dirname(__FILE__) . '/../../config.php');
include_once(__DIR__ . '/../../classlib2.0/FannieAPI.php');

class SalesAndTaxTodayReport extends \COREPOS\Fannie\API\FannieReportTool 
{

    //protected $auth_classes = array('salesbyhour');
    public $description = '[Today\'s Sales and Tax] shows current day totals by hour and tax totals for the day.';
    public $report_set = 'Sales Reports';
    public $themed = true;

    protected $selected;
    protected $name = "";
    protected $supers;

    public function __construct(){
        global $FANNIE_AUTH_DEFAULT;
        parent::__construct();
        //if ( isset($FANNIE_AUTH_DEFAULT) )
        //  $this->must_authenticate = $FANNIE_AUTH_DEFAULT;

        // Should let fanadmin, cashier in but keep lydia out.
        $this->auth_classes[] = 'salesbyhour';
    }

    function preprocess()
    {
        $dbc = $this->connection;
        $dbc->selectDB($this->config->get('OP_DB'));
        // Should let fanadmin, cashier in but keep lydia out.
        // But it doesn't.
        //$this->auth_classes[] = 'salesbyhour';

        /* Populate an array of superdepartments from which to
         *  select for filtering this report in the next run
         *  and if a superdepartment was chosen for this run
         *  get its name.
        */
        $this->selected = (isset($_GET['super']))?$_GET['super']:-1;
        $superP = $dbc->prepare("SELECT superID,super_name FROM MasterSuperDepts ORDER BY super_name");
        $superR = $dbc->execute($superP);
        $this->supers = array();
        $this->supers[-1] = "All";
        while($row = $dbc->fetch_row($superR)){
            $this->supers[$row[0]] = $row[1];
            if ($this->selected == $row[0])
                $this->name = $row[1];
        }

        $this->title = "Fannie : Today's $this->name Sales and Taxes";
        $this->header = "Today's $this->name Sales and Taxes";

        $this->hasMenus(True);

        return True;

    // preprocess()
    }

    function body_content()
    {
        global $FANNIE_OP_DB, $FANNIE_TRANS_DB, $FANNIE_COOP_ID;
        $dbc = $this->connection;
        $dbc->selectDB($this->config->get('OP_DB'));
        $today = date("Y-m-d");
        $table = 'dlog';    // i.e. dlog. dlog_15 if $today is before today.
        $ddiff = 0; // i.e. 0. -n if $today is before today.
/*
$table = 'dlog_15'; // i.e. dlog. dlog_15 if $today is before today.
$today = "2013-11-27";
$ddiff = -1;    // i.e. 0. -n if $today is before today.
echo "<br />Jiggered date, table= $table, datediff= $ddiff for $today";
//
$key = 'APACHE_RUN_USER';
if (isset($_ENV["$key"]))
    echo "<br />$key is {$_ENV["$key"]}";
else
    echo "<br />$key doesn't exist";
$val = getenv("$key");
//$val = apache_getenv("$key");
echo "<br />val of key $key is $val";
$id=exec("/usr/bin/id");
echo "<br />id $id";
*/

        $hourNames = array();
        for ($n=0;$n<24;++$n) {
            if ($n==0)
                $hourNames["$n"] = "Midnight";
            elseif ($n<12) {
                $hIndex = sprintf("%02d",$n);
                $hourNames["$hIndex"] = "{$n}am";
            }
            elseif ($n==12)
                $hourNames["$n"] = "Noon";
            else {
                $p = ($n-12);
                $hourNames["$n"] = "{$p}pm";
            }
        }

        $queryArgs = array();
        if ( $this->selected != -1 ) {
            $queryArgs[]=$this->selected;
            $queryArgs[]=$this->selected;
        }
        // Array pointer to the last column before taxes. Not used.
        //$colIndex = ($this->selected == -1)?1:2;
        // taxNames1 is all the taxes, with key/index agreeing with taxrates.id
        // I'm trying to not use it and to replace its use in StoreSummary with
        //  and array like taxNames.
        //x$taxNames1 = array(0 => '');
        // Treated as hash. Value is array.
        $taxNames = array();
        //x$taxCol = array(0 => '');
        $taxColName = '';
        $taxColNameSuper = '';
        $taxQuery = '';
        $tQ = $dbc->prepare("SELECT id, rate, description
            FROM taxrates
            WHERE id > 0 ORDER BY id");
        $tR = $dbc->execute($tQ);
        while ( $trow = $dbc->fetchRow($tR) ) {
            $taxId = $trow['id'];
            //x$taxNames1[$taxId] = $trow['description'];
            if ( $trow['rate'] > 0 ) {
                $taxNames[$taxId] = array();
                $taxNames[$taxId]['name'] = $trow['description'];
                $taxNames[$taxId]['sum'] = 0;
                $taxColName = 'taxes'.$taxId;
                $taxNames[$taxId]['colName'] = $taxColName;
                // $taxNames[$taxId]['index'] = ++$colIndex; // prefer to index by name; not used.
                // [queryAll] is not used outside this loop.  Needed?
                $taxNames[$taxId]['queryAll'] = 
                    ", sum(CASE WHEN d.tax = $taxId THEN d.total * x.rate ELSE 0 END) $taxColName";
                $taxQuery .= $taxNames[$taxId]['queryAll'];
                if ( $this->selected != -1 ) {
                    $taxNames[$taxId]['sum2'] = 0;
                    $taxColNameSuper = 'taxes'.$taxId.'s';
                    $taxNames[$taxId]['colNameSuper'] = $taxColNameSuper;
                    // [querySuper] is not used outside this loop.  Needed?
                    $taxNames[$taxId]['querySuper'] = 
                        ", sum(CASE WHEN d.tax = $taxId AND t.superID=? THEN d.total * x.rate ELSE 0 END) $taxColNameSuper";
                    $taxQuery .= $taxNames[$taxId]['querySuper'];
                    // For the tax query, which isn't using subs.
                    $queryArgs[] = $this->selected;
                }
            }
        }
//xecho "<br />"; print_r($taxNames);

        if ( isset($FANNIE_COOP_ID) && $FANNIE_COOP_ID == 'WEFC_Toronto' )
            $shrinkageUsers = " AND d.card_no not between 99900 and 99998";
        else
            $shrinkageUsers = "";

        // Plain, no superdepartment.
        /*
                    AND tdate < '2013-07-23 23:00:00'
                    AND tdate like '2013-07-23 %'
                    AND tdate >" . $dbc->now() . "
                WHERE ".$dbc->datediff('tdate',$dbc->now())."=$ddiff
        */
        if ($this->selected == -1){
            $query1="SELECT ".$dbc->hour('tdate')." as Hour, 
                    sum(total)as Sales,
                    sum(case when d.card_no = 99999 then total else 0 end) as nms
                    $taxQuery
                FROM ".$FANNIE_TRANS_DB.$dbc->sep()."$table AS d
                    LEFT JOIN MasterSuperDepts AS t ON d.department = t.dept_ID
                    LEFT JOIN taxrates AS x ON d.tax=x.id
                WHERE ".$dbc->datediff('tdate',$dbc->now())."=$ddiff
                    AND (trans_type ='I' OR trans_type = 'D' or trans_type='M')
                    AND (t.superID > 0 or t.superID IS NULL){$shrinkageUsers}
                GROUP BY ".$dbc->hour('tdate')."
                ORDER BY ".$dbc->hour('tdate');
        }
        // For a superdepartment.
        else {
            $query1="SELECT ".$dbc->hour('tdate')." as Hour, 
                    sum(total)as Sales,
                    sum(case when t.superID=? then total else 0 end) as superdeptSales,
                    sum(case when t.superID=? AND card_no = 99999 then total else 0 end) as nms
                    $taxQuery
                FROM ".$FANNIE_TRANS_DB.$dbc->sep()."$table AS d
                    LEFT JOIN MasterSuperDepts AS t ON d.department = t.dept_ID
                    LEFT JOIN taxrates AS x ON d.tax=x.id
                WHERE ".$dbc->datediff('tdate',$dbc->now())."=$ddiff
                    AND (trans_type ='I' OR trans_type = 'D' or trans_type='M')
                    AND t.superID > 0{$shrinkageUsers}
                GROUP BY ".$dbc->hour('tdate')."
                ORDER BY ".$dbc->hour('tdate');
        }
//echo "<br /> $query1 <br />"; print_r($queryArgs);

        $prep = $dbc->prepare($query1);
        $result = $dbc->execute($query1,$queryArgs);

        echo "<div class=\"text-center container\"><h1>Today's <span style=\"color:green;\">$this->name</span> Sales!</h1>";
        $today_time = date("l F j, Y g:i A");
        echo "<h3>$today_time</h3>";
        echo "<table class=\"table table-bordered no-bs-table\">";
        printf("<tr><td><strong>Hour</strong></td>
        <td class=\"text-right\"><b>Sales</b></td>
        <td class=\"text-right\" title='%% of Sales to Members'><b>Member%%</b></td>
        <td class=\"text-right\" title='%% of Sales to Non-Members'><b>Non-Member%%</b></td>
        <td>%s</td></tr>",
            ($this->selected==-1)?"":"<b>Proportion</b>");
        $sum = 0;
        $sum2 = 0;  // If report by SuperDept.
        $member_sum = 0;
        $member_sum2 = 0;
        $non_member_sum = 0;
        $non_member_sum2 = 0;
        $pcell="<td class=\"text-right\">%.2f%%</td>";
        $dcell = "<td class=\"text-right\">\$ %s</td>";
        while($row=$dbc->fetch_row($result)){
            printf("<tr><td>%s</td>{$dcell}{$pcell}{$pcell}<td class='%s'>%.2f%%</td></tr>",
                $hourNames["{$row['Hour']}"],
                ($this->selected==-1)?number_format($row['Sales'],2):number_format($row['superdeptSales'],2),
                (($row['Sales']-$row['nms'])==0)?0.00:($row['Sales']-$row['nms'])/$row['Sales']*100,
                ($row['nms']==0)?0.00:$row['nms']/$row['Sales']*100,
                ($this->selected==-1)?'collapse':'text-right',  
                ($this->selected==-1)?0.00:$row['superdeptSales']/$row['Sales']*100
                );
            $sum += $row['Sales'];
            $non_member_sum += $row['nms'];
            $member_sum += ($row['Sales']-$row['nms']);
            if($this->selected != -1) {
                $sum2 += $row['superdeptSales'];
                $non_member_sum2 += $row['nms'];
                $member_sum2 += ($row['superdeptSales']-$row['nms']);
            }
            foreach($taxNames as $k=>$tx) {
                $taxNames[$k]['sum'] += $row[$tx['colName']];
                if($this->selected != -1)
                    $taxNames[$k]['sum2'] += $row[$tx['colNameSuper']];
            }
        }

        // Total Sales
        $grandMemberSalesProp = ($this->selected==-1)?
            (($sum==0)?0.00:($member_sum/$sum)):
            (($sum2==0)?0.00:($member_sum2/$sum2));
        $grandNonMemberSalesProp = ($this->selected==-1)?
            (($sum==0)?0.00:($non_member_sum/$sum)):
            (($sum2==0)?0.00:($non_member_sum2/$sum2));
        printf("<tr><td width=60px class=\"text-left\"><strong>%s</strong></td>
        {$dcell}{$pcell}{$pcell}<td class='%s'><strong>%.2f%%</strong></td></tr>",
                'Total',
                ($this->selected==-1)?number_format($sum,2):number_format($sum2,2),
                number_format(($grandMemberSalesProp*100),2),
                number_format(($grandNonMemberSalesProp*100),2),
                ($this->selected==-1)?'collapse':'text-right',  
                (($this->selected==-1)?0.00:($sum==0))?0.00:round($sum2/$sum*100,2)
                );

        // Total of each Tax
        foreach($taxNames as $k=>$tx) {
            printf("<tr><td width=60px class='text-left'><strong>%s</strong></td>
            <td class='text-right'>\$ %s</td><td class='%s'>%.2f%%</td></tr>",
                $tx['name'],
                ($this->selected==-1)?number_format($tx['sum'],2):number_format($tx['sum2'],2),
                ($this->selected==-1)?'collapse':'text-right',  
                ($this->selected==-1)?0.00:$this->taxProportion($this->selected,$tx['sum'],$tx['sum2'])
                );
                // Nested test doesn't work.
                //($this->selected==-1)?0.00:($tx['sum']==0)?0.00:round($tx['sum2']/$tx['sum']*100,2)
        }

        echo "</table>";

        echo '<div class="form-group form-inline">Also available: 
                <select onchange="top.location=\''
                .basename($_SERVER['PHP_SELF'])
                .'?super=\'+this.value;" class="form-control">';
        foreach($this->supers as $k=>$v){
            echo "<option value=$k";
            if ($k == $this->selected)
                echo " selected";
            echo ">$v</option>";
        }
        echo "</select></div>";

        echo '</div>';

    // body_content()
    }

    function taxProportion ($selected, $sum, $sum2) {
        $retVal = 0.00;
        if ($selected==-1)
            $retVal = 0.00;
        else
            if ($sum==0)
                $retVal = 0.00;
            else 
                $retVal = round($sum2/$sum*100,2);

        return($retVal);
    }

    public function helpContent()
    {
        return '<p>
            Show hourly sales for the current day. Also includes
            information about sales tax collected.
            </p>';
    }

// SalesAndTaxTodayReport
}

FannieDispatch::conditionalExec(false);

