<?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

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

include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
    include(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}

class GeneralSalesReport extends FannieReportPage 
{

    public $description = '[General Sales Report] shows total sales per department for a given date range in dollars as well as a percentage of store-wide sales.';
    public $report_set = 'Sales Reports';

    private $grandTTL = 1;
    protected $title = "Fannie : General Sales Report";
    protected $header = "General Sales Report";
    protected $report_cache = 'none';
    protected $multi_report_mode = false;
    protected $sortable = false;
    protected $no_sort_but_style = true;
    protected $chart_data_columns = array(1);
    protected $report_headers = array('','Sales','Quantity','% Sales','Dept %');
    protected $required_fields = array('date1', 'date2');

    public function preprocess()
    {
        parent::preprocess();
        if ($this->content_function == 'report_content' && $this->report_format == 'html') {
            $this->addScript('../../src/javascript/Chart.min.js');
            $this->addScript('../../src/javascript/CoreChart.js');
            $this->addOnloadCommand('pieChart();');
        }

        return true;
    }

    public function javascript_content()
    {
        if ($this->report_format != 'html') {
            return '';
        }
        return <<<JAVASCRIPT
function pieChart() {
    var labels = $('.d3Label').toArray().map(x => x.innerHTML.trim());
    var data = $('.d3Data').toArray().map(x => Number(x.innerHTML));
    $('body').append('<div style="width:50%"><canvas id="pieCanvas"></canvas></div>');
    CoreChart.pieChart('pieCanvas', labels, data);
}
JAVASCRIPT;
    }

    private function thenQuery($dbc, $dlog, $store)
    {
        $superR = $dbc->query('SELECT dept_ID FROM MasterSuperDepts WHERE superID=0');
        $omitDepts = array();
        while ($superW = $dbc->fetch_row($superR)) {
            $omitDepts[] = $superW['dept_ID'];
        }
        list($omitVals, $omitDepts) = $dbc->safeInClause($omitDepts);

        $sales = "SELECT d.dept_name,
                    sum(t.total) AS ttl,
                    " . DTrans::sumQuantity('t') . " AS qty,
                    s.superID,
                    s.super_name
                FROM $dlog AS t 
                    LEFT JOIN departments AS d ON d.dept_no=t.department 
                    LEFT JOIN MasterSuperDepts AS s ON t.department=s.dept_ID
                WHERE 
                    t.department NOT IN ($omitVals)
                    AND t.trans_type IN ('I', 'D')
                    AND (tdate BETWEEN ? AND ?)
                    AND " . DTrans::isStoreID($store, 't') . "
                    AND t.memType NOT IN " . DTrans::memTypeIgnore($dbc) . "
                GROUP BY s.superID,s.super_name,d.dept_name,t.department
                ORDER BY s.superID,t.department";
        
        return array($sales, $omitDepts);
    }

    private function nowQuery($dbc, $dlog, $store)
    {
        $sales = "SELECT 
            CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name end AS dept_name,
            sum(t.total) AS ttl,
            " . DTrans::sumQuantity('t') . " AS qty,
            CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end AS superID,
            CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END AS super_name
            FROM $dlog AS t 
                " . DTrans::joinProducts() . " AND p.upc <> '0000000000000'
                LEFT JOIN departments AS d ON d.dept_no=t.department 
                LEFT JOIN departments AS e ON p.department=e.dept_no 
                LEFT JOIN MasterSuperDepts AS s ON s.dept_ID=p.department 
                LEFT JOIN MasterSuperDepts AS r ON r.dept_ID=t.department
            WHERE
                t.trans_type IN ('I', 'D')
                AND (s.superID > 0 OR (s.superID IS NULL AND r.superID > 0)
                OR (s.superID IS NULL AND r.superID IS NULL))
                AND (tdate BETWEEN ? AND ?)
                AND " . DTrans::isStoreID($store, 't') . "
            GROUP BY
            CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,
            CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END,
            CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name end,
            CASE WHEN e.dept_no IS NULL THEN d.dept_no ELSE e.dept_no end
            ORDER BY
            CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID end,
            CASE WHEN e.dept_no IS NULL THEN d.dept_no ELSE e.dept_no end";
        $omitDepts = array();

        return array($sales, $omitDepts);
    }

    public function fetch_report_data()
    {
        $dbc = $this->connection;
        $dbc->selectDB($this->config->get('OP_DB'));
        $date1 = $this->form->date1;
        $date2 = $this->form->date2;
        $dept = FormLib::get('dept', 0);
        $store = FormLib::get('store');

        $dlog = DTransactionsModel::selectDlog($date1,$date2);

        list($sales, $omitDepts) = $this->thenQuery($dbc, $dlog, $store);
        if ($dept == 1){
            list($sales, $omitDepts) = $this->nowQuery($dbc, $dlog, $store);
        }
        $supers = array();
        $prep = $dbc->prepare($sales);
        $args = $omitDepts;
        $args[] = $date1 . ' 00:00:00';
        $args[] = $date2 . ' 23:59:59';
        $args[] = $store;
        $salesR = $dbc->execute($prep, $args);
    
        $curSuper = 0;
        $grandTotal = 0;
        while ($row = $dbc->fetchRow($salesR)){
            $curSuper = $row['superID'];
            if (!isset($supers[$curSuper])) {
                $supers[$curSuper] = array('sales'=>0.0,'qty'=>0.0,'name'=>$row['super_name'],'depts'=>array());
            }
            $supers[$curSuper]['sales'] += $row['ttl'];
            $supers[$curSuper]['qty'] += $row['qty'];
            $supers[$curSuper]['depts'][] = array('name'=>$row['dept_name'],'sales'=>$row['ttl'],'qty'=>$row['qty']);
            $grandTotal += $row[1];
        }

        $this->grandTTL = $grandTotal;

        return $this->toReportData($supers, $grandTotal);
    }

    private function toReportData($supers, $grandTotal)
    {
        $data = array();
        $num = 1;
        foreach ($supers as $s) {
            if ($s['sales']==0) {
                $num++;
                continue;
            }

            $superSum = $s['sales'];
            foreach($s['depts'] as $d) {
                $record = array(
                    $d['name'],
                    sprintf('%.2f',$d['sales']),
                    sprintf('%.2f',$d['qty']),
                    sprintf('%.2f',($d['sales'] / $grandTotal) * 100),
                    sprintf('%.2f',($d['sales'] / $superSum) * 100)
                );
                $data[] = $record;
            }

            $record = array(
                $s['name'],
                sprintf('%.2f', $s['sales']),
                sprintf('%.2f', $s['qty']),
                '',
                sprintf('%.2f%%', ($s['sales'] / $grandTotal) * 100),
            );
            $record['meta'] = FannieReportPage::META_BOLD | FannieReportPage::META_CHART_DATA;

            $data[] = $record;

            $data[] = array('meta'=>FannieReportPage::META_BLANK);

            if ($num < count($supers)) {
                $data[] = array('meta'=>FannieReportPage::META_REPEAT_HEADERS);
            }
            $num++;
        }

        return $data;
    }

    public function calculate_footers($data)
    {
        $sumQty = 0.0;
        $sumSales = 0.0;
        foreach($data as $row) {
            if (isset($row['meta'])) {
                continue;
            }
            $sumQty += $row[2];
            $sumSales += $row[1];
        }
        return array('Total',number_format($sumSales,2),number_format($sumQty,2), '', null);
    }

    public function form_content()
    {
        list($lastMonday, $lastSunday) = \COREPOS\Fannie\API\lib\Dates::lastWeek();
        $store = FormLib::storePicker();
        ob_start();
        ?>
        <form action=GeneralSalesReport.php method=get class="form-horizontal">
        <div class="row">
            <div class="col-sm-6">
                <p>
                    <label>Start Date</label>
                    <input class="form-control date-field" type=text id=date1 name=date1 value="<?php echo $lastMonday; ?>" />
                </p>
                <p>
                    <label>End Date</label>
                    <input class="form-control date-field" type=text id=date2 name=date2 value="<?php echo $lastSunday; ?>" />
                </p>
                <p>
                    <label>Departments</label>
                    <select name=dept class="form-control">
                        <option value=0>Use department settings at time of sale</option>
                        <option value=1>Use current department settings</option>
                    </select>
                </p>
                <p>
                    <label>Store(s)</label>
                    <?php echo $store['html']; ?>
                </p>
            </div>
            <div class="col-sm-6">
                <p>
                <?php echo FormLib::date_range_picker(); ?>
                </p>
            </div>
        </div>
        <p>
            <button type=submit name=submit value="Submit" class="btn btn-default">Submit</button>
            <label><input type=checkbox name=excel /> Excel</label>
        </p>
        </form>
        <?php

        return ob_get_clean();
    }

    public function helpContent()
    {
        return '<p>General Sales is an overview with totals for each POS
            deaprtment and subtotals for each super department. In this
            context, individual departments are only counted once under
            their home super department so that the grand total accurately
            reflects total sales.</p>
            <p>The <em>Use department settings...</em> option may change
            line item totals but should not alter the grand total. If an
            item used to be in department #1 but now is in department #2,
            this option controls where its sales appear in the report.</p>';
    }

    public function unitTest($phpunit)
    {
        $supers = array(
            0=>array('sales'=>0),
            1=>array('sales'=>1, 'qty'=>1, 'name'=>'foo', 'depts'=>array(array('name'=>'bar', 'sales'=>1, 'qty'=>1))),
        );
        $data = $this->toReportData($supers, 1);
        $phpunit->assertInternalType('array', $data);
        $phpunit->assertInternalType('array', $this->calculate_footers($data));
    }

}

FannieDispatch::conditionalExec();

