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

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

/* --COMMENTS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 * 09Oct13 AT Duplicate existing report using FannieReportPage
    => First pass: I turned off the multiple option and altered the fetch data
       routine to return a single report. This is fairly close to matching but
       the formatting isn't as nice. I also added a bit more gating to avoid
       potential (if unlikely) divide by zero warnings.
    => Second pass: I added constants to FannieReportClass that can be used
       to trigger differently formatting and behavior for a particular record.
       This let me match EL's bolding and spacing. In a delightful surprise,
       jQuery's sorting understands the segmentation and sorts the
       superdepartments separately.

 * 22Jul13 EL Attempt to use dlog views must wait until they include cost.
*/
include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
    include(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}

class StoreSummaryReportAlt extends FannieReportPage {

    protected $title = "Fannie : Store Summary Report";
    protected $header = "Store Summary Report";

    protected $report_headers = array('','Qty','Costs','% Costs','DeptC%','Sales','% Sales','DeptS %',
                'Margin %','GST','HST');
    protected $required_fields = array('date1', 'date2');

    public $description = '[Store Summary Report] shows total sales, costs and taxes per department for a given date range in dollars as well as a percentage of store-wide sales and costs. It uses actual item cost if known and estimates cost from price and department margin if not; relies on department margins being accurate.';
    public $discoverable = false; // this is a duplicate of StoreSummaryReport.php
                                  // intended as a code comparison/example
    public $report_set = 'Sales Reports';
    public $themed = true;

    function preprocess()
    {
        // custom: optional sorting
        if (FormLib::get_form_value('sortable') !== '') {
            $this->sortable = true;
        } else {
            $this->sortable = false;
        }
        $this->cellTextAlign = 'right';

        return parent::preprocess();
    }

    function report_description_content(){
        $ret = array();
        if (FormLib::get_form_value('dept',0) == 0){
            $ret[] = "<p>Using the department# the upc was assigned to at time of sale</p>";
        }
        else{
            $ret[] = "<p>Using the department# the upc is assigned to now</p>";
        }
        $ret[] = "<p>Note: For items where cost is not recorded the margin in the deptMargin table is relied on.</p>";
        return $ret;
    }

    function fetch_report_data(){
        global $FANNIE_OP_DB, $FANNIE_COOP_ID;
        $d1 = $this->form->date1;
        $d2 = $this->form->date2;
        $dept = FormLib::get_form_value('dept',0);

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

        $dtrans = DTransactionsModel::selectDtrans($d1,$d2);
        $datestamp = $dbc->identifierEscape('datetime');

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

        // The eventual return value.
        $data = array();

        $taxNames = array(0 => '');
        $tQ = $dbc->prepare("SELECT id, rate, description FROM taxrates WHERE id > 0 ORDER BY id");
        $tR = $dbc->execute($tQ);
        // Try generating code in this loop for use in SELECT and reporting.
        //  See SalesAndTaxTodayReport.php
        while ( $trow = $dbc->fetchRow($tR) ) {
            $taxNames[$trow['id']] = $trow['description'];
        }

        /* Using department settings at the time of sale.
         * I.e. The department# from the transaction.
         *  If that department# no longer exists or is different then the report will be wrong.
         *  This does not use a departments table contemporary with the transactions.
         * [0]Dept_name [1]Cost, [2]HST, [3]GST, [4]Sales, [x]Qty, [x]superID, [x]super_name
        */
        $departments = $dbc->tableDefinition('departments');
        if ($dept == 0){
            // Change varname to sales or totals
            $costs = "SELECT
                    d.dept_name dname,
                    sum(CASE WHEN t.trans_type = 'I' THEN t.cost 
                         WHEN t.trans_type = 'D' AND d.margin > 0.00 
                         THEN t.total - (t.total * d.margin) END) AS costs,
                    sum(CASE WHEN t.tax = 1 THEN t.total * x.rate ELSE 0 END) AS taxes1,
                    sum(CASE WHEN t.tax = 2 THEN t.total * x.rate ELSE 0 END) AS taxes2,
                    sum(t.total) AS sales,
                    sum(t.quantity) AS qty,
                    s.superID AS sid,
                    s.super_name AS sname
                FROM
                    $dtrans AS t LEFT JOIN
                    departments AS d ON d.dept_no=t.department LEFT JOIN
                    MasterSuperDepts AS s ON t.department=s.dept_ID LEFT JOIN ";
                // use margin column from departments if present
                if (!isset($departments['margin']) && $dbc->tableExists('deptMargin')) {
                    $costs .= ' deptMargin AS m ON t.department=m.dept_id LEFT JOIN ';
                    $costs = str_replace('d.margin', 'm.margin', $costs);
                }
                $costs .= " taxrates AS x ON t.tax=x.id
                WHERE 
                    ($datestamp BETWEEN ? AND ?)
                    AND (s.superID > 0 OR s.superID IS NULL) 
                    AND t.trans_type in ('I','D')
                    AND t.trans_status not in ('D','X','Z')
                    AND t.emp_no not in (9999){$shrinkageUsers}
                    AND t.register_no != 99
                    AND t.upc != 'DISCOUNT'
                    AND t.trans_subtype not in ('CP','IC')
                GROUP BY
                    s.superID, s.super_name, d.dept_name, t.department
                ORDER BY
                    s.superID, t.department";

        }
        /* Using current department settings.
         * I.e. The department for the upc from the current products table.
         *  This does not use a departments table contemporary with the transactions.
        */
        elseif ($dept == 1){
            $costs = "SELECT
                CASE WHEN e.dept_name IS NULL THEN d.dept_name ELSE e.dept_name END AS dname,
                sum(CASE WHEN t.trans_type = 'I' THEN t.cost 
                     WHEN t.trans_type = 'D' AND d.margin > 0.00 
                     THEN t.total - (t.total * d.margin) END) AS costs,
                sum(CASE WHEN t.tax = 1 THEN t.total * x.rate ELSE 0 END) AS taxes1,
                sum(CASE WHEN t.tax = 2 THEN t.total * x.rate ELSE 0 END) AS taxes2,
                sum(t.total) AS sales,
                sum(t.quantity) AS qty,
                CASE WHEN s.superID IS NULL THEN r.superID ELSE s.superID END AS sid,
                CASE WHEN s.super_name IS NULL THEN r.super_name ELSE s.super_name END AS sname
            FROM
                $dlog AS t LEFT JOIN
                products AS p ON t.upc=p.upc 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 LEFT JOIN ";
            // use margin column from departments if present
            if (!isset($departments['margin']) && $dbc->tableExists('deptMargin')) {
                $costs .= ' deptMargin AS m ON p.department=m.dept_id LEFT JOIN ';
                $costs = str_replace('d.margin', 'm.margin', $costs);
            }
            $costs .= "taxrates AS x ON t.tax=x.id
            WHERE
                ($datestamp BETWEEN ? AND ?)
                AND (s.superID > 0 OR (s.superID IS NULL AND r.superID > 0)
                    OR (s.superID IS NULL AND r.superID IS NULL))
                AND t.trans_type in ('I','D')
                AND t.trans_status not in ('D','X','Z')
                AND t.emp_no not in (9999){$shrinkageUsers}
                AND t.register_no != 99
                AND t.upc != 'DISCOUNT'
                AND t.trans_subtype not in ('CP','IC')
            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";
        }
        $costsP = $dbc->prepare($costs);
        $costArgs = array($d1.' 00:00:00', $d2.' 23:59:59');
        $costsR = $dbc->execute($costsP, $costArgs);

        // Array in which totals used in the report are accumulated.
        $supers = array();
        $curSuper = 0;
        $grandTotal = 0;
        $this->grandCostsTotal = 0;
        $this->grandSalesTotal = 0;
        $this->grandTax1Total = 0;
        $this->grandTax2Total = 0;

        while($row = $dbc->fetchRow($costsR)){
            if ($curSuper != $row['sid']){
                $curSuper = $row['sid'];
            }
            if (!isset($supers[$curSuper])) {
                $supers[$curSuper] = array(
                'name'=>$row['sname'],
                'qty'=>0.0,'costs'=>0.0,'sales'=>0.0,
                'taxes1'=>0.0,'taxes2'=>0.0,
                'depts'=>array());
            }
            $supers[$curSuper]['qty'] += $row['qty'];
            $supers[$curSuper]['costs'] += $row['costs'];
            $supers[$curSuper]['sales'] += $row['sales'];
            $supers[$curSuper]['taxes1'] += $row['taxes1'];
            $supers[$curSuper]['taxes2'] += $row['taxes2'];
            $this->grandCostsTotal += $row['costs'];
            $this->grandSalesTotal += $row['sales'];
            $this->grandTax1Total += $row['taxes1'];
            $this->grandTax2Total += $row['taxes2'];
            // GROUP BY produces 1 row per dept. Values are sums.
            $supers[$curSuper]['depts'][] = array('name'=>$row['dname'],
                'qty'=>$row['qty'],
                'costs'=>$row['costs'],
                'sales'=>$row['sales'],
                'taxes1'=>$row['taxes1'],
                'taxes2'=>$row['taxes2']);
        }

        $superCount=1;
        foreach($supers as $s){
            if ($s['sales']==0) continue;

            // add department records
            $superCostsSum = $s['costs'];
            $superSalesSum = $s['sales'];
            foreach($s['depts'] as $d){
                $record = array(
                    $d['name'],
                    sprintf('%.2f',$d['qty']),
                    sprintf('$%.2f',$d['costs'])
                );

                $costPercent = 'n/a';
                if ($this->grandCostsTotal > 0)
                    $costPercent = sprintf('%.2f %%',($d['costs'] / $this->grandCostsTotal) * 100);
                $record[] = $costPercent;

                $costPercent = 'n/a';
                if ($superCostsSum > 0)
                    $costPercent = sprintf('%.2f %%',($d['costs'] / $superCostsSum) * 100);
                $record[] = $costPercent;
    
                $record[] = sprintf('$%.2f',$d['sales']);

                $salePercent = 'n/a';
                if ($this->grandSalesTotal > 0)
                    $salePercent = sprintf('%.2f %%',($d['sales'] / $this->grandSalesTotal) * 100);
                $record[] = $salePercent;

                $salePercent = 'n/a';
                if ($superSalesSum > 0)
                    $salePercent = sprintf('%.2f %%',($d['sales'] / $superSalesSum) * 100);
                $record[] = $salePercent;

                $margin = 'n/a';
                if ($d['sales'] > 0 && $d['costs'] > 0)
                    $margin = sprintf('%.2f %%', (100 * ($d['sales']-$d['costs']) / $d['sales']));
                $record[] = $margin;

                $record[] = sprintf('%.2f',$d['taxes2']);
                $record[] = sprintf('%.2f',$d['taxes1']);

                $data[] = $record;
            }

            // add super record
            $record = array($s['name'],
                    sprintf('%.2f',$s['qty']),
                    sprintf('%.2f',$s['costs'])
            );
            $costPercent = 'n/a';
            if ($this->grandCostsTotal > 0)
                $costPercent = sprintf('%.2f %%',($s['costs'] / $this->grandCostsTotal) * 100);
            $record[] = $costPercent;
            $record[] = '';
            $record[] = sprintf('%.2f',$s['sales']);
            $salePercent = 'n/a';
            if ($this->grandSalesTotal > 0)
                $salePercent = sprintf('%.2f %%',($s['sales'] / $this->grandSalesTotal) * 100);
            $record[] = $salePercent;
            $record[] = '';
            $margin = 'n/a';
            if ($s['sales'] > 0 && $s['costs'] > 0)
                $margin = sprintf('%.2f %%', (100 * ($s['sales']-$s['costs']) / $s['sales']));
            $record[] = $margin;
            $record[] = sprintf('%.2f',$s['taxes2']);
            $record[] = sprintf('%.2f',$s['taxes1']);

            $record['meta'] = FannieReportPage::META_BOLD;

            $data[] = $record;

            // Rather than start a new report, I'm just inserting a blank
            // line between supers
            $data[] = array('meta'=>FannieReportPage::META_BLANK);

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

        // The summary of grand totals proportions.

        // Headings
        $record = array(
            '',
            '',
            'Costs',
            '',
            '',
            'Sales',
            'Profit',
            '',
            'Margin %',
            isset($taxNames['2']) ? $taxNames['2'] : 'n/a',
            isset($taxNames['1']) ? $taxNames['1'] : 'n/a',
        );
        $record['meta'] = FannieReportPage::META_BOLD;
        $data[] = $record;

        // Grand totals
        $record = array(
            'WHOLE STORE',
            '',
            '$ '.number_format($this->grandCostsTotal,2),
            '',
            '',
            '$ '.number_format($this->grandSalesTotal,2),
            '$ '.number_format(($this->grandSalesTotal - $this->grandCostsTotal),2),
            ''
        );
        $margin = 'n/a';
        if ($this->grandSalesTotal > 0)
            $margin = number_format(((($this->grandSalesTotal - $this->grandCostsTotal) / $this->grandSalesTotal) * 100),2).' %';
        $record[] = $margin;
        $record[] = '$ '.number_format($this->grandTax2Total,2);
        $record[] = '$ '.number_format($this->grandTax1Total,2);
        $data[] = $record;

        $this->grandTTL = $grandTotal;
        return $data;

    // fetch_report_data()
    }

    public function calculate_footers($data)
    {
        return array();
    // calculate_footers()
    }

    function form_content()
    {
        list($lastMonday, $lastSunday) = \COREPOS\Fannie\API\lib\Dates::lastWeek();
        ob_start();
        ?>
        <form action=StoreSummaryReportAlt.php method=get>
        <div class="col-sm-5">
            <div class="form-group">
                <label>Start Date</label>
                <input type=text id=date1 name=date1 class="form-control date-field" 
                    value="<?php echo $lastMonday; ?>" />
            </div>
            <div class="form-group">
                <label>End Date</label>
                <input type=text id=date2 name=date2 class="form-control date-field" 
                    value="<?php echo $lastSunday; ?>" />
            </div>
            <div class="form-group">
                <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>
            </div>
            <div class="form-group">
                <label>Sortable
                    <input type="checkbox" name="sortable" />
                </label>
            </div>
            <p>
                <button type="submit" class="btn btn-default">Submit</button>
            </p>
        </div>
        <div class="col-sm-5">
            <?php echo FormLib::date_range_picker(); ?>
        </div>
        </form>
        <?php

        return ob_get_clean();
    // form_content()
    }

// StoreSummaryReport
}

FannieDispatch::conditionalExec();

