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

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

class ProductSummarizeLastQuarter extends FannieTask
{
    public $name = 'Summarize Product Sales for the last quarter';

    public $description = 'Recalculates totals, quantities, and percentage of sales
last thirteen weeks';

    public $default_schedule = array(
        'min' => 0,
        'hour' => 3,
        'day' => '*',
        'month' => '*',
        'weekday' => '*',
    );

    private function initWeeks($dbc)
    {
        $today = strtotime('today');
        $this_monday = $today;
        while(date('N', $this_monday) != 1) {
            $this_monday = mktime(0, 0, 0, date('n', $this_monday), date('j', $this_monday) - 1, date('Y', $this_monday));
        }
        $last_monday = mktime(0, 0, 0, date('n', $this_monday), date('j', $this_monday) - 7, date('Y', $this_monday));

        $this->cronMsg('Determining applicable weeks', FannieLogger::INFO);
        $dbc->query('TRUNCATE TABLE weeksLastQuarter');
        $ins = $dbc->prepare('INSERT INTO weeksLastQuarter (weekLastQuarterID, weekStart, weekEnd) VALUES (?, ?, ?)');

        $weeks = array();

        // week zero
        $this_sunday = mktime(0, 0, 0, date('n', $this_monday), date('j', $this_monday) + 6, date('Y', $this_monday));
        $args = array(0, date('Y-m-d 00:00:00', $this_monday), date('Y-m-d 23:59:59', $this_sunday));
        $weeks[] = array($this_monday, $this_sunday);
        $dbc->execute($ins, $args);
        // database may be uncooperative about a zero value in the increment column
        // so make sure its assigned correctly
        $dbc->query('UPDATE weeksLastQuarter SET weekLastQuarterID=0');

        // other weeks
        for ($i=0; $i<13; $i++) {
            $monday = mktime(0, 0, 0, date('n', $last_monday), date('j', $last_monday) - ($i*7), date('Y', $last_monday));
            $sunday = mktime(0, 0, 0, date('n', $monday), date('j', $monday) + 6, date('Y', $monday));
            $args = array($i+1, date('Y-m-d 00:00:00', $monday), date('Y-m-d 23:59:59', $sunday));
            $dbc->execute($ins, $args);
            $weeks[] = array($monday, $sunday);
        }

        return $weeks;
    }

    public function run()
    {
        global $FANNIE_OP_DB, $FANNIE_TRANS_DB, $FANNIE_ARCHIVE_DB;

        $dbc = FannieDB::get($FANNIE_ARCHIVE_DB);
        $weeks = $this->initWeeks($dbc);

        $addP = $dbc->prepare('INSERT INTO productWeeklyLastQuarter 
                            (upc, weekLastQuarterID, quantity, total,
                            percentageStoreSales, percentageSuperDeptSales,
                            percentageDeptSales, storeID, saleQuantity, saleTotal)
                            VALUES
                            (?,   ?,                 ?,        ?,
                            ?,                    ?,
                            ?,                   ?,       ?,            ?)');
        $products = $FANNIE_OP_DB . $dbc->sep() . 'products';
        $supers = $FANNIE_OP_DB . $dbc->sep() . 'MasterSuperDepts';
        $dbc->query('TRUNCATE TABLE productWeeklyLastQuarter');
        $dbc->startTransaction();
        foreach($weeks as $weekID => $limits) {
            $store_sales = array();
            $super_sales = array();
            $dept_sales = array();
            $upcs = array();
            $this->cronMsg('Processing week #'.$weekID, FannieLogger::INFO);
            $dlog = DTransactionsModel::selectDlog(date('Y-m-d', $limits[0]), date('Y-m-d', $limits[1]));
            $dataP = $dbc->prepare("SELECT d.upc, SUM(total) as ttl, "
                                . DTrans::sumQuantity('d') . " as qty,
                                SUM(CASE WHEN d.discounttype > 0 THEN total ELSE 0 END) AS saleTTL,
                                SUM(CASE WHEN d.discounttype = 0 THEN 0
                                    WHEN d.trans_status='M' THEN 0
                                    WHEN d.trans_subtype='OG' THEN 0
                                    WHEN d.unitPrice=0.01 THEN 1
                                    ELSE d.quantity END) AS saleQty,
                                d.store_id,
                                MAX(p.department) as dept, MAX(s.superID) as superDept
                                FROM $dlog AS d 
                                    " . DTrans::joinProducts('d', 'p', 'INNER') . "
                                    LEFT JOIN $supers AS s ON p.department = s.dept_ID
                                WHERE tdate BETWEEN ? AND ?
                                GROUP BY d.upc, d.store_id");
            $args = array(date('Y-m-d 00:00:00', $limits[0]), date('Y-m-d 23:59:59', $limits[1]));
            $result = $dbc->execute($dataP, $args);
            // accumulate all info for the week
            // in order to calculate percentages
            while($row = $dbc->fetch_row($result)) {
                // normally miskeys that were voided
                // and not useful information
                if ($row['ttl'] == 0) continue;

                $upcs[$row['upc'].':'.$row['store_id']] = array(
                    'ttl' => $row['ttl'],
                    'qty' => $row['qty'],
                    'dept' => $row['dept'],
                    'super' => $row['superDept'],
                    'saleTTL' => $row['saleTTL'],
                    'saleQty' => $row['saleQty'],
                );

                if (!isset($store_sales[$row['store_id']])) {
                    $store_sales[$row['store_id']] = 0;
                }
                $store_sales[$row['store_id']] += $row['ttl'];

                if (!isset($super_sales[$row['superDept'] . ':' . $row['store_id']])) {
                    $super_sales[$row['superDept'] . ':' . $row['store_id']] = 0.0;
                }
                $super_sales[$row['superDept'] . ':' . $row['store_id']] += $row['ttl'];

                if (!isset($dept_sales[$row['dept'] . ':' . $row['store_id']])) {
                    $dept_sales[$row['dept'] . ':' . $row['store_id']] = 0.0;
                }
                $dept_sales[$row['dept'] . ':' . $row['store_id']] += $row['ttl'];

                if ($this->test_mode) {
                    break;
                }
            }

            // add entries for this week's items
            foreach($upcs as $key => $info) {
                list($upc, $storeID) = explode(':', $key, 2);
                $d_ttl = $dept_sales[$info['dept'] . ':' . $storeID];
                $s_ttl = $super_sales[$info['super'] . ':' . $storeID];

                $args = array(
                    $upc,
                    $weekID,
                    $info['qty'],
                    $info['ttl'],
                    $store_sales[$storeID] == 0 ? 0.0 : $info['ttl'] / $store_sales[$storeID],
                    $s_ttl == 0 ? 0.0 : $info['ttl'] / $s_ttl,
                    $d_ttl == 0 ? 0.0 : $info['ttl'] / $d_ttl,
                    $storeID,
                    $info['saleQty'],
                    $info['saleTTL'],
                );
                $dbc->execute($addP, $args);
            }

            if ($this->test_mode) {
                break;
            }
        } // end loop on weeks
        $dbc->commitTransaction();

        $this->weightedAverages($dbc);
    }

    private function weightedAverages($dbc)
    {
        // now do weighted averages
        $this->cronMsg('Calculating weighted averages', FannieLogger::INFO);
        $dbc->query('TRUNCATE TABLE productSummaryLastQuarter');
        $res = $dbc->query("SELECT q.upc, storeID, p.department, m.superID
            FROM productWeeklyLastQuarter AS q
                INNER JOIN " . FannieDB::fqn('products', 'op') . " AS p ON q.upc=p.upc AND q.storeID=p.store_id
                INNER JOIN " . FannieDB::fqn('MasterSuperDepts', 'op') . " AS m ON p.department=m.dept_ID
            GROUP BY q.upc, storeID, department, m.superID
            ORDER BY q.storeID");
        $insP = $dbc->prepare("INSERT INTO productSummaryLastQuarter
            (upc, storeID, qtyThisWeek, totalThisWeek, qtyLastQuarter,
            totalLastQuarter, percentageStoreSales, percentageSuperDeptSales,
            percentageDeptSales, percentageStoreSalesWeek, percentageSuperDeptSalesWeek,
            percentageDeptSalesWeek, percentageStoreSales2Week,
            percentageSuperDeptSales2Week, percentageDeptSales2Week,
            percentageStoreSales5Week, percentageSuperDeptSales5Week,
            percentageDeptSales5Week)
            VALUES (?, ?, 0, 0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $totalsP = $dbc->prepare("SELECT weekLastQuarterID, storeID,
            SUM(total) AS store,
            SUM(CASE WHEN m.superID=? THEN total ELSE 0 END) AS super,
            SUM(CASE WHEN p.department=? THEN total ELSE 0 END) AS dept
            FROM productWeeklyLastQuarter AS q
                INNER JOIN " . FannieDB::fqn('products', 'op') . " AS p ON q.upc=p.upc AND q.storeID=p.store_id
                INNER JOIN " . FannieDB::fqn('MasterSuperDepts', 'op') . " AS m ON p.department=m.dept_ID
            WHERE storeID=?
            GROUP BY weekLastQuarterID, storeID");
        $totalCache = array(
            'store' => array(),
            'super' => array(),
            'dept' => array(),
        );
        $totalCache1W = array(
            'store' => array(),
            'super' => array(),
            'dept' => array(),
        );
        $totalCache2W = array(
            'store' => array(),
            'super' => array(),
            'dept' => array(),
        );
        $totalCache5W = array(
            'store' => array(),
            'super' => array(),
            'dept' => array(),
        );
        $weekP = $dbc->prepare("SELECT * FROM productWeeklyLastQuarter WHERE upc=? AND storeID=? AND weekLastQuarterID=?");
        $rows = $dbc->numRows($res);
        $count = 1;
        $dbc->startTransaction();
        echo "Found $rows\n";
        while ($row = $dbc->fetchRow($res)) {
            //echo "$count/$rows\r";
            $upc = $row['upc'];
            $storeID = $row['storeID'];
            $sales = array(
                'store' => 0,
                'super' => 0,
                'dept' => 0,
            );
            $sales1W = array(
                'store' => 0,
                'super' => 0,
                'dept' => 0,
            );
            $sales2W = array(
                'store' => 0,
                'super' => 0,
                'dept' => 0,
            );
            $sales5W = array(
                'store' => 0,
                'super' => 0,
                'dept' => 0,
            );
            $realSales = 0;
            $scaledSales = 0;
            $scaledSalesWeek = 0;
            $scaledSales2Week = 0;
            $scaledSales5Week = 0;
            $realQty = 0;
            $factor = 1.0;
            for ($i=1; $i<=13; $i++) {
                $week = $dbc->getRow($weekP, array($upc, $storeID, $i));
                if ($week) {
                    $realSales += $week['total'];
                    $realQty += $week['quantity'];
                    $scaledSales += ($factor * $week['total']);
                    if ($i <= 1) {
                        $scaledSalesWeek += ($factor * $week['total']);
                    }
                    if ($i <= 2) {
                        $scaledSales2Week += ($factor * $week['total']);
                    }
                    if ($i <= 5) {
                        $scaledSales5Week += ($factor * $week['total']);
                    }
                }
                if (!isset($totalCache['store'][$storeID])) {
                    $totalCache['store'][$storeID] = array();
                    $totalCache1W['store'][$storeID] = array();
                    $totalCache2W['store'][$storeID] = array();
                    $totalCache5W['store'][$storeID] = array();
                }
                if (!isset($totalCache['super'][$row['superID']])) {
                    $totalCache['super'][$row['superID']] = array();
                    $totalCache1W['super'][$row['superID']] = array();
                    $totalCache2W['super'][$row['superID']] = array();
                    $totalCache5W['super'][$row['superID']] = array();
                }
                if (!isset($totalCache['dept'][$row['department']])) {
                    $totalCache['dept'][$row['department']] = array();
                    $totalCache1W['dept'][$row['department']] = array();
                    $totalCache2W['dept'][$row['department']] = array();
                    $totalCache5W['dept'][$row['department']] = array();
                }
                if (!isset($totalCache['store'][$storeID][$i])) {
                    $totalCache['super'] = array();
                    $totalCache['dept'] = array();
                    $totalCache1W['super'] = array();
                    $totalCache1W['dept'] = array();
                    $totalCache2W['super'] = array();
                    $totalCache2W['dept'] = array();
                    $totalCache5W['super'] = array();
                    $totalCache5W['dept'] = array();
                    $totalR = $dbc->execute($totalsP, array($row['superID'], $row['department'], $storeID));
                    while ($totals = $dbc->fetchRow($totalR)) {
                        $weekID = $totals['weekLastQuarterID'];
                        $totalCache['store'][$storeID][$weekID] = $totals['store'];
                        $totalCache['super'][$row['superID']][$weekID] = $totals['super'];
                        $totalCache['dept'][$row['department']][$weekID] = $totals['dept'];
                        if ($weekID <= 1) {
                            $totalCache1W['store'][$storeID][$weekID] = $totals['store'];
                            $totalCache1W['super'][$row['superID']][$weekID] = $totals['super'];
                            $totalCache1W['dept'][$row['department']][$weekID] = $totals['dept'];
                        }
                        if ($weekID <= 2) {
                            $totalCache2W['store'][$storeID][$weekID] = $totals['store'];
                            $totalCache2W['super'][$row['superID']][$weekID] = $totals['super'];
                            $totalCache2W['dept'][$row['department']][$weekID] = $totals['dept'];
                        }
                        if ($weekID <= 5) {
                            $totalCache5W['store'][$storeID][$weekID] = $totals['store'];
                            $totalCache5W['super'][$row['superID']][$weekID] = $totals['super'];
                            $totalCache5W['dept'][$row['department']][$weekID] = $totals['dept'];
                        }
                    }
                }
                $sales['store'] += $factor * $totalCache['store'][$storeID][$i];
                if ($i <= 1) {
                    $sales1W['store'] += $factor * $totalCache1W['store'][$storeID][$i];
                }
                if ($i <= 2) {
                    $sales2W['store'] += $factor * $totalCache2W['store'][$storeID][$i];
                }
                if ($i <= 5) {
                    $sales5W['store'] += $factor * $totalCache5W['store'][$storeID][$i];
                }
                if (!isset($totalCache['super'][$row['superID']][$i])) {
                    $totalR = $dbc->execute($totalsP, array($row['superID'], $row['department'], $storeID));
                    while ($totals = $dbc->fetchRow($totalR)) {
                        $weekID = $totals['weekLastQuarterID'];
                        $totalCache['super'][$row['superID']][$weekID] = $totals['super'];
                        $totalCache['dept'][$row['department']][$weekID] = $totals['dept'];
                        if ($i <= 1) {
                            $totalCache1W['super'][$row['superID']][$weekID] = $totals['super'];
                            $totalCache1W['dept'][$row['department']][$weekID] = $totals['dept'];
                        }
                        if ($i <= 2) {
                            $totalCache2W['super'][$row['superID']][$weekID] = $totals['super'];
                            $totalCache2W['dept'][$row['department']][$weekID] = $totals['dept'];
                        }
                        if ($i <= 5) {
                            $totalCache5W['super'][$row['superID']][$weekID] = $totals['super'];
                            $totalCache5W['dept'][$row['department']][$weekID] = $totals['dept'];
                        }
                    }
                }
                $sales['super'] += $factor * $totalCache['super'][$row['superID']][$i];
                if ($i <= 1) {
                    $sales1W['super'] += $factor * $totalCache1W['super'][$row['superID']][$i];
                }
                if ($i <= 2) {
                    $sales2W['super'] += $factor * $totalCache2W['super'][$row['superID']][$i];
                }
                if ($i <= 5) {
                    $sales5W['super'] += $factor * $totalCache5W['super'][$row['superID']][$i];
                }
                if (!isset($totalCache['dept'][$row['department']][$i])) {
                    $totalR = $dbc->execute($totalsP, array($row['superID'], $row['department'], $storeID));
                    while ($totals = $dbc->fetchRow($totalR)) {
                        $weekID = $totals['weekLastQuarterID'];
                        $totalCache['dept'][$row['department']][$weekID] = $totals['dept'];
                        if ($i <= 1) {
                            $totalCache1W['dept'][$row['department']][$weekID] = $totals['dept'];
                        }
                        if ($i <= 2) {
                            $totalCache2W['dept'][$row['department']][$weekID] = $totals['dept'];
                        }
                        if ($i <= 5) {
                            $totalCache5W['dept'][$row['department']][$weekID] = $totals['dept'];
                        }
                    }
                }
                $sales['dept'] += $factor * $totalCache['dept'][$row['department']][$i];
                if ($i <= 1) {
                    $sales1W['dept'] += $factor * $totalCache1W['dept'][$row['department']][$i];
                }
                if ($i <= 2) {
                    $sales2W['dept'] += $factor * $totalCache2W['dept'][$row['department']][$i];
                }
                if ($i <= 5) {
                    $sales5W['dept'] += $factor * $totalCache5W['dept'][$row['department']][$i];
                }
                $factor -= 0.05;
            }
            $args = array(
                $upc,
                $storeID,
                $realQty,
                $realSales,
                $sales['store'] != 0 ? $scaledSales / $sales['store'] : 0,
                $sales['super'] != 0 ? $scaledSales / $sales['super'] : 0,
                $sales['dept'] != 0 ? $scaledSales / $sales['dept'] : 0,
                $sales1W['store'] != 0 ? $scaledSalesWeek / $sales1W['store'] : 0,
                $sales1W['super'] != 0 ? $scaledSalesWeek / $sales1W['super'] : 0,
                $sales1W['dept'] != 0 ? $scaledSalesWeek / $sales1W['dept'] : 0,
                $sales2W['store'] != 0 ? $scaledSales2Week / $sales2W['store'] : 0,
                $sales2W['super'] != 0 ? $scaledSales2Week / $sales2W['super'] : 0,
                $sales2W['dept'] != 0 ? $scaledSales2Week / $sales2W['dept'] : 0,
                $sales5W['store'] != 0 ? $scaledSales5Week / $sales5W['store'] : 0,
                $sales5W['super'] != 0 ? $scaledSales5Week / $sales5W['super'] : 0,
                $sales5W['dept'] != 0 ? $scaledSales5Week / $sales5W['dept'] : 0,
            );
            $dbc->execute($insP, $args);

            $count++;
        }
        $dbc->commitTransaction();
        /*
        $dbc->query('INSERT INTO productSummaryLastQuarter
                   (upc, storeID, qtyThisWeek, totalThisWeek, qtyLastQuarter,
                    totalLastQuarter, percentageStoreSales,
                    percentageSuperDeptSales, percentageDeptSales)
                   SELECT upc, 
                   storeID,
                   SUM(CASE WHEN weekLastQuarterID=0 THEN quantity ELSE 0 END) as qtyThisWeek,
                   SUM(CASE WHEN weekLastQuarterID=0 THEN total ELSE 0 END) as totalThisWeek,
                   SUM(CASE WHEN weekLastQuarterID<>0 THEN quantity ELSE 0 END) as qtyLastQuarter,
                   SUM(CASE WHEN weekLastQuarterID<>0 THEN total ELSE 0 END) as totalLastQuarter,
                   SUM(CASE WHEN weekLastQuarterID=1 THEN percentageStoreSales ELSE 0 END),
                   SUM(CASE WHEN weekLastQuarterID=1 THEN percentageSuperDeptSales ELSE 0 END),
                   SUM(CASE WHEN weekLastQuarterID=1 THEN percentageDeptSales ELSE 0 END)
                   FROM productWeeklyLastQuarter
                   GROUP BY upc, storeID');
         */
    }
}

