CORE-POS/IS4C

View on GitHub
fannie/modules/plugins2.0/OpenBookFinancingV2/ObfWeeklyReportV2.php

Summary

Maintainability
F
1 wk
Test Coverage
<?php
/*******************************************************************************

    Copyright 2014 Whole Foods Co-op

    This file is part of IT CORE.

    IT CORE 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.

    IT CORE 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\Operators as Op;

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

class ObfWeeklyReportV2 extends ObfWeeklyReport
{
    protected $sortable = false;
    protected $no_sort_but_style = true;
    public $discoverable = false;

    protected $report_headers = array(
        array('', 'Last Year', 'Plan Goal', '% Store', 'Trend', 'Actual', '% Growth', '% Store', 'Current O/U', 'Long-Term O/U'),
        array('', 'Last Year', 'Plan Goal', '% Store', 'Trend', 'Actual', '% Growth', '% Store', 'Current O/U', 'Long-Term O/U'),
        array('', 'Last Year', 'Plan Goal', '% Store', 'Trend', 'Actual', '% Growth', '% Store', 'Current O/U', 'Long-Term O/U'),
        array('', 'Last Year', 'Plan Goal', '% Store', 'Trend', 'Actual', '% Growth', '% Store', 'Current O/U', 'Long-Term O/U'),
        array('', 'Last Year', 'Plan Goal', '% Store', 'Trend', 'Actual', '% Growth', '% Store', 'Current O/U', 'Long-Term O/U'),
        array('', 'Last Year', 'Plan Goal', '% Store', 'Trend', 'Actual', '% Growth', '% Store', 'Current O/U', 'Long-Term O/U'),
        array('', 'Last Year', 'Plan Goal', '% Store', 'Trend', 'Actual', '% Growth', '% Store', 'Current O/U', 'Long-Term O/U'),
        array('', 'Last Year', 'Plan Goal', '% Store', 'Trend', 'Actual', '% Growth', '% Store', 'Current O/U', 'Long-Term O/U'),
        array('', 'Current Year', 'Last Year', '', '', '', '', '', '', ''),
    );

    protected $class_lib = 'ObfLibV2';

    protected $OU_START = 162;
    protected $PLAN_CACHE1 = null;
    protected $PLAN_CACHE2 = null;

    protected function getOuStart($weekID)
    {
        if ($weekID >= 214) {
            return 214;
        } elseif ($weekID >= 201) {
            return 201;
        } elseif ($weekID >= 188) {
            return 188;
        } elseif ($weekID >= 175) { // Week of Oct 2, 2017
            return 175;
        }

        return 162;
    }

    /** previous numbers
    protected $PLAN_SALES = array(
        '1,6' => 48125.67,      // Hillside Produce
        '2,10' => 11037.90,     // Hillside Deli
        '2,11' => 30002.96,
        '2,16' => 12231.91,
        '3,1' => 24806.33,      // Hillside Grocery
        '3,4' => 61459.93,
        '3,5' => 23038.55,
        '3,7' => 98.48,
        '3,8' => 17579.95,
        '3,9' => 3313.16,
        '3,13' => 14085.38,
        '3,17' => 25413.22,
        '7,6' => 16406.47,      // Denfeld Produce
        '8,10' => 4049.92,      // Denfeld Deli
        '8,11' => 12211.43,
        '8,16' => 4768.70,
        '9,1' => 8281.40,       // Denfeld Grocery
        '9,4' => 24726.33,
        '9,5' => 9070.20,
        '9,7' => 45.52,
        '9,8' => 5975.21,
        '9,9' => 1310.53,
        '9,13' => 4589.22,
        '9,17' => 8823.08,
    );
    */

    protected $PLAN_SALES = array(
        '1,6' => 51193.05,      // Hillside Produce
        '2,10' => 11416.48,     // Hillside Deli
        '2,11' => 31032.00,
        '2,16' => 12651.44,
        '3,1' => 24391.70,      // Hillside Grocery
        '3,4' => 59349.56,
        '3,5' => 22467.52,
        '3,7' => 187.91,
        '3,8' => 16600.59,
        '3,9' => 2591.48,
        '3,13' => 14267.70,
        '3,17' => 25043.57,
        '7,6' => 18247.03,      // Denfeld Produce
        '8,10' => 4173.27,      // Denfeld Deli
        '8,11' => 12583.37,
        '8,16' => 4913.95,
        '9,1' => 8065.54,       // Denfeld Grocery
        '9,4' => 24245.34,
        '9,5' => 8415.72,
        '9,7' => 81.00,
        '9,8' => 5655.49,
        '9,9' => 990.29,
        '9,13' => 4578.06,
        '9,17' => 8308.91,
    );

    protected $PLAN_SALES_Q1_2018 = array(
        '1,6' => 53904.29,      // Hillside Produce
        '2,10' => 12187.19,     // Hillside Deli
        '2,11' => 33128.32,
        '2,16' => 13505.62,
        '3,1' => 25019.71,      // Hillside Grocery
        '3,4' => 60877.32,
        '3,5' => 23046.19,
        '3,7' => 192.84,
        '3,8' => 17028.21,
        '3,9' => 2657.68,
        '3,13' => 14635.17,
        '3,17' => 25688.49,
        '7,6' => 19084.56,      // Denfeld Produce
        '8,10' => 4516.25,      // Denfeld Deli
        '8,11' => 13618.01,
        '8,16' => 5318.20,
        '9,1' => 8168.40,       // Denfeld Grocery
        '9,4' => 24552.79,
        '9,5' => 8522.84,
        '9,7' => 82.03,
        '9,8' => 5726.79,
        '9,9' => 1002.57,
        '9,13' => 4636.12,
        '9,17' => 8414.48,
    );

    protected $PLAN_SALES_Q2_2018 = array(
        '1,6' => 51031.00,      // Hillside Produce
        '2,10' => 11448.32,     // Hillside Deli
        '2,11' => 31119.86,
        '2,16' => 12686.82,
        '3,1' => 26430.32,      // Hillside Grocery
        '3,4' => 64309.57,
        '3,5' => 24345.53,
        '3,7' => 203.71,
        '3,8' => 17988.26,
        '3,9' => 2807.52,
        '3,13' => 15460.30,
        '3,17' => 27136.80,
        '7,6' => 17975.00,      // Denfeld Produce
        '8,10' => 4383.48,      // Denfeld Deli
        '8,11' => 13217.67,
        '8,16' => 5161.85,
        '9,1' => 8470.24,       // Denfeld Grocery
        '9,4' => 25460.06,
        '9,5' => 8837.77,
        '9,7' => 85.06,
        '9,8' => 5938.41,
        '9,9' => 1039.62,
        '9,13' => 4807.43,
        '9,17' => 8725.41,
    );

    protected $PLAN_SALES_Q3_2018 = array(
        '1,6' => 51510.00,      // Hillside Produce
        '2,10' => 11676.94,     // Hillside Deli
        '2,11' => 31742.34,
        '2,16' => 12940.72,
        '3,1' => 25497.83,      // Hillside Grocery
        '3,4' => 62041.83,
        '3,5' => 23487.33,
        '3,7' => 196.81,
        '3,8' => 17353.57,
        '3,9' => 2708.96,
        '3,13' => 14914.74,
        '3,17' => 26179.90,
        '7,6' => 20085.00,      // Denfeld Produce
        '8,10' => 4514.67,      // Denfeld Deli
        '8,11' => 13615.10,
        '8,16' => 5317.08,
        '9,1' => 8949.35,       // Denfeld Grocery
        '9,4' => 26900.87,
        '9,5' => 9338.17,
        '9,7' => 89.81,
        '9,8' => 6274.05,
        '9,9' => 1098.86,
        '9,13' => 5079.05,
        '9,17' => 9218.78,
    );

    protected $PLAN_SALES_Q4_2018 = array(
        '1,6' => 52231.00,      // Hillside Produce
        '2,10' => 11840.47,     // Hillside Deli
        '2,11' => 32186.37,
        '2,16' => 13122.16,
        '3,1' => 25854.77,      // Hillside Grocery
        '3,4' => 62910.11,
        '3,5' => 23815.64,
        '3,7' => 199.76,
        '3,8' => 17596.82,
        '3,9' => 2746.91,
        '3,13' => 15123.69,
        '3,17' => 26546.32,
        '7,6' => 20708.00,      // Denfeld Produce
        '8,10' => 4654.99,      // Denfeld Deli
        '8,11' => 14037.15,
        '8,16' => 5481.86,
        '9,1' => 9226.50,       // Denfeld Grocery
        '9,4' => 27735.16,
        '9,5' => 9627.56,
        '9,7' => 92.79,
        '9,8' => 6468.55,
        '9,9' => 1133.08,
        '9,13' => 5236.49,
        '9,17' => 9504.87,
    );

    public function preprocess()
    {
        $this->addScript('../../../src/javascript/Chart.min.js');
        $this->addScript('summary.js?date=20210706');

        return FannieReportPage::preprocess();
    }

    private function weekToYM($weekID)
    {
        $prep = $this->connection->prepare('SELECT startDate
            FROM ' . FannieDB::fqn('ObfWeeks', 'plugin:ObfDatabaseV2') . '
            WHERE obfWeekID=?');
        $date = $this->connection->getValue($prep, array($weekID));
        $nowNext = array(0, 0);
        $stamp = strtotime($date);
        $cur = strtotime($date);
        for ($i=0; $i<7; $i++) {
            if (date('n', $cur) == date('n', $stamp)) {
                $nowNext[0]++;
            } else {
                $nowNext[1]++;
            }
            $stamp = mktime(0, 0, 0, date('n', $stamp), date('j', $stamp)+1, date('Y', $stamp));
        }
        if ($nowNext[0] > $nowNext[1]) {
            $stamp = time();
        }

        return array(date('Y', $stamp), date('n', $stamp));
    }

    protected function getPlanRange($startWeek, $endWeek, $categoryID, $superID)
    {
        $ret = 0;
        for ($weekID = $startWeek; $weekID <= $endWeek; $weekID++) {
            if ($weekID >= 218) {
                if (!is_array($this->PLAN_CACHE1)) {
                    $this->PLAN_CACHE1 = $this->getPlanSales($weekID);
                }
                $ret += isset($this->PLAN_CACHE1[$categoryID . ',' . $superID]) ? $this->PLAN_CACHE1[$categoryID . ',' . $superID] : 0;
            } elseif ($weekID >= 214) {
                if (!is_array($this->PLAN_CACHE2)) {
                    $this->PLAN_CACHE2 = $this->getPlanSales($weekID);
                }
                $ret += isset($this->PLAN_CACHE2[$categoryID . ',' . $superID]) ? $this->PLAN_CACHE2[$categoryID . ',' . $superID] : 0;
            } elseif ($weekID >= 201) {
                $ret += $this->PLAN_SALES_Q4_2018[$categoryID . ',' . $superID];
            } elseif ($weekID >= 188) {
                $ret += $this->PLAN_SALES_Q3_2018[$categoryID . ',' . $superID];
            } elseif ($weekID >= 175) {
                $ret += $this->PLAN_SALES_Q2_2018[$categoryID . ',' . $superID];
            } elseif ($weekID >= 162) {
                $ret += $this->PLAN_SALES_Q1_2018[$categoryID . ',' . $superID];
            } else {
                $ret += $this->PLAN_SALES[$categoryID . ',' . $superID];
            }
        }

        return $ret;
    }

    private function getPlanSales($weekID)
    {
        if ($weekID >= 374) {
            $prep = $this->connection->prepare("select c.obfCategoryID, m.superID, b.storeID, b.planGoal  
                from " . FannieDB::fqn('ObfBudget', 'plugin:ObfDatabaseV2') . " AS b 
                    left join " . FannieDB::fqn('ObfCategorySuperDeptMap', 'plugin:ObfDatabaseV2') . " AS m ON b.superID=m.superID 
                    left join " . FannieDB::fqn('ObfCategories', 'plugin:ObfDatabaseV2') . " AS c ON m.obfCategoryID=c.obfCategoryID AND b.storeID=c.storeID 
                WHERE c.hasSales=1 and obfWeekID=?");
            $res = $this->connection->execute($prep, array($weekID));
            $ret = array();
            while ($row = $this->connection->fetchRow($res)) {
                $key = $row['obfCategoryID'] . ',' . $row['superID'];
                $ret[$key] = $row['planGoal'];
            }
            return $ret;
        } elseif ($weekID >= 218) {
            $prep = $this->connection->prepare("
                SELECT l.obfCategoryID, s.superID, (1+l.growthTarget)*s.lastYearSales AS plan
                FROM " . FannieDB::fqn('ObfLabor', 'plugin:ObfDatabaseV2') . " AS l
                    INNER JOIN " . FannieDB::fqn('ObfCategories', 'plugin:ObfDatabaseV2') . " AS c ON l.obfCategoryID=c.obfCategoryID
                    INNER JOIN " . FannieDB::fqn('ObfSalesCache', 'plugin:ObfDatabaseV2') . " AS s
                        ON c.obfCategoryID=s.obfCategoryID AND l.obfWeekID=s.obfWeekID
                WHERE l.obfWeekID=?");
            $res = $this->connection->execute($prep, array($weekID));
            $ret = array();
            while ($row = $this->connection->fetchRow($res)) {
                $key = $row['obfCategoryID'] . ',' . $row['superID'];
                $ret[$key] = $row['plan'];
            }
            return $ret;
        } elseif ($weekID >= 214) {
            list($year, $month) = $this->weekToYM($weekID);
            $ret = array();
            $prep = $this->connection->prepare('SELECT c.obfCategoryID, m.superID, p.planGoal
                FROM ' . FannieDB::fqn('ObfCategories', 'plugin:ObfDatabaseV2') . ' AS c
                INNER JOIN ' . FannieDB::fqn('ObfCategorySuperDeptMap', 'plugin:ObfDatabaseV2') . ' AS m ON c.obfCategoryID=m.obfCategoryID
                INNER JOIN ' . FannieDB::fqn('ObfPlans', 'plugin:ObfDatabaseV2') . ' AS p ON c.storeID=p.storeID AND m.superID=p.superID
                WHERE c.hasSales=1 and month=? and year=?');
            $res = $this->connection->execute($prep, array($month, $year)); 
            $days = date('t', mktime(0,0,0,$month,1,$year));
            while ($row = $this->connection->fetchRow($res)) {
                $key = $row['obfCategoryID'] . ',' . $row['superID'];
                $ret[$key] = ($row['planGoal'] / $days) * 7;
            }
            return $ret;
        } elseif ($weekID >= 201) {
            return $this->PLAN_SALES_Q4_2018;
        } elseif ($weekID >= 188) {
            return $this->PLAN_SALES_Q3_2018;
        } elseif ($weekID >= 175) {
            return $this->PLAN_SALES_Q2_2018;
        } elseif ($weekID >= 162) {
            return $this->PLAN_SALES_Q1_2018;
        } else {
            return $this->PLAN_SALES;
        }
    }

    public function fetch_report_data()
    {
        $class_lib = $this->class_lib;
        $dbc = $class_lib::getDB();
        
        $week = $class_lib::getWeek($dbc);
        $week->obfWeekID($this->form->weekID);
        $exists = $week->load();
        if (!$exists) {
            return array(array('Week does not exist', null));
        }

        $labor = new ObfLaborModelV2($dbc);
        $labor->obfWeekID($week->obfWeekID());


        $store = FormLib::get('store', 1);
        
        /**
           Timestamps for the start and end of
           the current week
        */
        $start_ts = strtotime($week->startDate());
        $end_ts = mktime(0, 0, 0, date('n', $start_ts), date('j', $start_ts)+6, date('Y', $start_ts));
        list($year, $month) = $this->findYearMonth($start_ts, $end_ts);

        $start_ly = mktime(0, 0, 0, date('n',$start_ts), date('j', $start_ts), $year-1);
        while (date('N', $start_ly) != 1) {
            $start_ly = mktime(0,0,0, date('n', $start_ly), date('j', $start_ly)+1, date('Y', $start_ly));
        }
        $end_ly = mktime(0, 0, 0, date('n', $start_ly), date('j', $start_ly)+6, date('Y', $start_ly));

        $future = $end_ts >= strtotime(date('Y-m-d')) ? true: false;

        /**
          Sales information is cached to avoid expensive
          aggregate queries
        */
        $sales = $class_lib::getCache($dbc);
        $sales->obfWeekID($week->obfWeekID());
        $sales->actualSales(0, '>');
        $num_cached = $sales->find();
        $sales->reset();
        $sales->obfWeekID($week->obfWeekID());
        $sales->lastYearSales(0, '>');
        $ly_cached = $sales->find();
        if (count($num_cached) == 0 || count($ly_cached) == 0) {
            $dateInfo = array(
                'start_ts' => $start_ts,
                'end_ts' => $end_ts,
                'start_ly' => $start_ly,
                'end_ly' => $end_ly,
                'averageWeek' => false,
            );
            $this->updateSalesCache($week, array($num_cached, $ly_cached), $dateInfo);
        }

        $PLAN_SALES = $this->getPlanSales($this->form->weekID);

        // record set to return
        $data = array();                

        $total_sales = $this->initTotalSales();
        $total_trans = $this->initTotalTrans();
        $total_hours = $this->initTotalHours();
        $total_wages = $this->initTotalWages();
        $qtd_sales_ou = 0;
        $qtd_hours_ou = 0;

        $this->prepareStatements($dbc);
        $this->prepTrendsStatement($dbc, $week);

        /**
          LOOP ONE
          Examine OBF Categories that have sales. These will include
          both sales and labor information
        */
        $categories = new ObfCategoriesModelV2($dbc);
        $categories->hasSales(1);
        $categories->storeID($store);
        foreach ($categories->find('seq', true) as $category) {
            $data[] = $this->headerRow($category->name(), 'black', array($category->obfCategoryID(), $week->obfWeekID()));
            $sum = array(0.0, 0.0);
            $dept_proj = 0.0;
            $dept_trend = 0;
            $salesR = $dbc->execute($this->salesP, array($week->obfWeekID(), $category->obfCategoryID()));
            $qtd_dept_plan = 0;
            $qtd_dept_sales = 0;
            $qtd_dept_ou = 0;
            /**
              Go through sales records for the category
            */
            while ($row = $dbc->fetch_row($salesR)) {
                $projIndex = $category->obfCategoryID() . ',' . $row['superID'];
                $proj = isset($PLAN_SALES[$projIndex]) ? $PLAN_SALES[$projIndex] : 0;
                $trend1 = $this->calculateTrend($dbc, $category->obfCategoryID(), $row['superID']);
                $dept_trend += $trend1;
                $total_sales->trend += $trend1;

                $quarter = $dbc->getRow($this->quarterSalesP, 
                    array($week->obfQuarterID(), $category->obfCategoryID(), $row['superID'], date('Y-m-d 00:00:00', $end_ts))
                );
                if ($quarter === false) {
                    $quarter = array('actual'=>0, 'lastYear'=>0, 'plan'=>0, 'trans'=>0, 'ly_trans'=>0);
                }
                $ou_weeks = ($week->obfWeekID() - $this->getOuStart($week->obfWeekID())) + 1;
                $ou_plan = $this->getPlanRange($this->getOuStart($week->obfWeekID()), $week->obfWeekID(), $category->obfCategoryID(), $row['superID']);
                //$qtd_dept_plan += ($proj * $ou_weeks);
                $qtd_dept_plan += $ou_plan;
                $qtd_dept_sales += $quarter['actual'];
                $total_trans->quarterThisYear = $quarter['trans'];
                $total_trans->quarterLastYear = $quarter['ly_trans'];

                $record = array(
                    $row['super_name'],
                    number_format($row['lastYearSales'], 0),
                    number_format($proj, 0),
                    number_format($proj, 0), // converts to % of sales
                    number_format($trend1, 0),
                    number_format($row['actualSales'], 0),
                    sprintf('%.2f%%', $this->percentGrowth($row['actualSales'], $row['lastYearSales'])),
                    number_format($row['actualSales'], 0), // converts to % of sales
                    number_format($row['actualSales'] - $proj, 0),
                    //number_format($quarter['actual'] - ($proj * $ou_weeks), 0),
                    number_format($quarter['actual'] - ($ou_plan), 0),
                    'meta' => FannieReportPage::META_COLOR,
                    'meta_background' => $this->colors[0],
                    'meta_foreground' => 'black',
                );
                $sum[0] += $row['actualSales'];
                $sum[1] += $row['lastYearSales'];
                $total_sales->thisYear += $row['actualSales'];
                $total_sales->lastYear += $row['lastYearSales'];
                if ($total_trans->thisYear == 0) {
                    $total_trans->thisYear = $row['transactions'];
                }
                if ($total_trans->lastYear == 0) {
                    $total_trans->lastYear = $row['lastYearTransactions'];
                }
                $total_sales->projected += $proj;
                $dept_proj += $proj;
                $total_sales->quarterProjected += ($proj * $ou_weeks);
                $total_sales->quarterActual += $quarter['actual'];
                $qtd_sales_ou += ($quarter['actual'] - ($proj * $ou_weeks));
                $qtd_dept_ou += ($quarter['actual'] - ($proj * $ou_weeks));
                $data[] = $record;
            }

            /** total sales for the category **/
            $record = array(
                'Total',
                number_format($sum[1], 0),
                number_format($dept_proj, 0),
                number_format($dept_proj, 0), // % of store sales re-written later
                number_format($dept_trend, 0),
                number_format($sum[0], 0),
                sprintf('%.2f%%', $this->percentGrowth($sum[0], $sum[1])),
                number_format($sum[0], 0),
                number_format($sum[0] - $dept_proj, 0),
                number_format($qtd_dept_ou, 0),
                'meta' => FannieReportPage::META_COLOR | FannieReportPage::META_BOLD,
                'meta_background' => $this->colors[0],
                'meta_foreground' => 'black',
            );
            $data[] = $record;

            /**
              Now labor values based on sales calculationsabove
            */
            $labor->obfCategoryID($category->obfCategoryID());
            $labor->load();
            // use SPLH instead of pre-allocated
            list($proj_hours, $trend_hours) = $this->projectHours($labor->splhTarget(), $dept_proj, $dept_trend);
            // approximate wage to convert hours into dollars
            list($proj_wages, $trend_wages) = $this->projectWages($labor, $proj_hours, $trend_hours);

            $quarter = $dbc->getRow($this->quarterLaborP, 
                array($week->obfLaborQuarterID(), $labor->obfCategoryID(), date('Y-m-d 00:00:00', $end_ts))
            );
            if ($quarter === false) {
                $quarter = array('hours'=>0, 'wages'=>0, 'laborTarget'=>0, 'hoursTarget'=>0, 'actualSales' => 0);
            }
            $qt_splh = $dbc->getRow($this->quarterSplhP,
                array($week->obfLaborQuarterID(), $labor->obfCategoryID(), date('Y-m-d 00:00:00', $end_ts))
            );
            if ($qt_splh !== false) {
                $quarter['actualSales'] = $qt_splh['actualSales'];
                $quarter['planSales'] = $qt_splh['planSales'];
            }
            $qt_average_wage = $quarter['hours'] == 0 ? 0 : $quarter['wages'] / ((float)$quarter['hours']);
            $qt_proj_hours = Op::div(($dept_proj * $ou_weeks), $labor->splhTarget());
            $qt_proj_labor = $qt_proj_hours * $qt_average_wage;
            $total_hours->quarterActual += $quarter['hours'];
            $total_hours->quarterProjected += $qt_proj_hours;
            $total_sales->quarterLaborSales += $quarter['actualSales'];

            /*
            $data[] = array(
                'Hours',
                '',
                number_format($proj_hours, 0),
                '',
                number_format($trend_hours, 0),
                number_format($labor->hours(), 0),
                sprintf('%.2f%%', $this->percentGrowth($labor->hours(), $proj_hours)),
                '',
                number_format($labor->hours() - $proj_hours, 0),
                number_format($quarter['hours'] - $qt_proj_hours, 0),
                'meta' => FannieReportPage::META_COLOR,
                'meta_background' => $this->colors[0],
                'meta_foreground' => 'black',
            );
             */
            $total_hours->actual += $labor->hours();
            $qtd_hours_ou += ($quarter['hours'] - $qt_proj_hours);

            $total_hours->projected += $proj_hours;
            $total_hours->trend += $trend_hours;

            $quarter_actual_sph = $quarter['hours'] == 0 ? 0 : ($qtd_dept_sales)/($quarter['hours']);
            $quarter_proj_sph = ($qt_proj_hours == 0) ? 0 : ($qtd_dept_plan)/($qt_proj_hours);
            /*
            $data[] = array(
                'Sales per Hour',
                '',
                number_format(Op::div($dept_proj, $proj_hours), 2),
                '',
                number_format(Op::div($dept_trend, $trend_hours), 2),
                number_format($labor->hours() == 0 ? 0 : $sum[0] / $labor->hours(), 2),
                sprintf('%.2f%%', $this->percentGrowth(Op::div($sum[0], $labor->hours()), Op::div($dept_proj,$proj_hours))),
                '',
                number_format(Op::div($sum[0], $labor->hours()) - Op::div($dept_proj, $proj_hours), 2),
                number_format($quarter_actual_sph - $quarter_proj_sph, 2),
                'meta' => FannieReportPage::META_COLOR,
                'meta_background' => $this->colors[0],
                'meta_foreground' => 'black',
            );
             */

            /*
            $data[] = array(
                'Labor % of Sales',
                '',
                '',
                '',
                '',
                sprintf('%.2f%%', $labor->wages() / $sum[0] * 100),
                '',
                '',
                '',
                '',
                'meta' => FannieReportPage::META_COLOR,
                'meta_background' => $this->colors[0],
                'meta_foreground' => 'black',
            );
            */

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

            if (count($this->colors) > 1) {
                array_shift($this->colors);
            }
        }

        $data = $this->rewritePercentageOfSales($data, $total_sales);

        /**
          LOOP TWO
          Examine OBF Categories without sales. These will include
          only labor information
        */
        $cat = new ObfCategoriesModelV2($dbc);
        $cat->hasSales(0);
        $cat->storeID($store);
        $cat->name('Admin', '<>');
        foreach ($cat->find('name') as $c) {
            continue; // no longer rendering labor data
            $data[] = $this->headerRow($c->name());
            $labor->obfCategoryID($c->obfCategoryID());
            $labor->load();

            $quarter = $dbc->getRow($this->quarterLaborP, 
                array($week->obfLaborQuarterID(), $labor->obfCategoryID(), date('Y-m-d 00:00:00', $end_ts))
            );
            if ($quarter === false) {
                $quarter = array('hours'=>0, 'wages'=>0, 'laborTarget'=>0, 'hoursTarget'=>0);
            }
            $qt_average_wage = $quarter['hours'] == 0 ? 0 : $quarter['wages'] / ((float)$quarter['hours']);
            $qt_proj_hours = $total_sales->quarterProjected / $c->salesPerLaborHourTarget();
            $qt_proj_labor = $qt_proj_hours * $qt_average_wage;
            $total_hours->quarterActual += $quarter['hours'];
            $total_hours->quarterProjected += $qt_proj_hours;

            list($proj_hours, $trend_hours) = $this->projectHours($labor->splhTarget(), $total_sales->projected, $total_sales->trend);
            list($proj_wages, $trend_wages) = $this->projectWages($labor, $proj_hours, $trend_hours);

            $data[] = array(
                'Hours',
                '',
                number_format($proj_hours, 0),
                '',
                number_format($trend_hours, 0),
                number_format($labor->hours(), 0),
                '',
                '',
                number_format($labor->hours() - $proj_hours, 0),
                number_format($quarter['hours'] - $qt_proj_hours, 0),
                'meta' => FannieReportPage::META_COLOR,
                'meta_background' => $this->colors[0],
                'meta_foreground' => 'black',
            );
            $qtd_hours_ou += ($quarter['hours'] - $qt_proj_hours);

            $quarter_actual_sph = $quarter['hours'] == 0 ? 0 : ($total_sales->quarterActual)/($quarter['hours']);
            $quarter_proj_sph = $qt_proj_hours == 0 ? 0 : ($total_sales->quarterProjected)/($qt_proj_hours);
            $data[] = array(
                'Sales per Hour',
                '',
                sprintf('%.2f', Op::div($total_sales->projected, $proj_hours)),
                '',
                sprintf('%.2f', Op::div($total_sales->trend, $trend_hours)),
                number_format($labor->hours() == 0 ? 0 : $total_sales->thisYear / $labor->hours(), 2),
                '',
                '',
                number_format(Op::div($total_sales->thisYear, $labor->hours()) - Op::div($total_sales->projected, $proj_hours), 2),
                number_format($quarter_actual_sph - $quarter_proj_sph, 2),
                'meta' => FannieReportPage::META_COLOR,
                'meta_background' => $this->colors[0],
                'meta_foreground' => 'black',
            );

            /*
            $data[] = array(
                'Labor % of Sales',
                '',
                '',
                '',
                '',
                sprintf('%.2f%%', $labor->wages() / $total_sales->thisYear * 100),
                '',
                '',
                '',
                '',
                'meta' => FannieReportPage::META_COLOR,
                'meta_background' => $this->colors[0],
                'meta_foreground' => 'black',
            );
            */

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

            $total_hours->actual += $labor->hours();
            $total_hours->projected += $proj_hours;
            $total_hours->trend += $trend_hours;

            if (count($this->colors) > 1) {
                array_shift($this->colors);
            }
        }

        /**
           Storewide totals section
        */
        $data[] = $this->headerRow('Total Store');

        $data[] = array(
            'Sales',
            number_format($total_sales->lastYear, 0),
            number_format($total_sales->projected, 0),
            '',
            number_format($total_sales->trend, 0),
            number_format($total_sales->thisYear, 0),
            sprintf('%.2f%%', $this->percentGrowth($total_sales->thisYear, $total_sales->lastYear)),
            '',
            number_format($total_sales->thisYear - $total_sales->projected, 0),
            number_format($qtd_sales_ou, 0),
            'meta' => FannieReportPage::META_COLOR,
            'meta_background' => $this->colors[0],
            'meta_foreground' => 'black',
        );

        /*
        $data[] = array(
            'Hours',
            '',
            number_format($total_hours->projected, 0),
            '',
            number_format($total_hours->trend, 0),
            number_format($total_hours->actual, 0),
            '',
            '',
            number_format($total_hours->actual - $total_hours->projected, 0),
            number_format($qtd_hours_ou, 0),
            'meta' => FannieReportPage::META_COLOR,
            'meta_background' => $this->colors[0],
            'meta_foreground' => 'black',
        );

        $quarter_actual_sph = $total_hours->quarterActual == 0 ? 0 : ($total_sales->quarterActual)/($total_hours->quarterActual);
        $quarter_proj_sph = $total_hours->quarterProjected == 0 ? 0 : ($total_sales->quarterProjected)/($total_hours->quarterProjected);
        $data[] = array(
            'Sales per Hour',
            '',
            sprintf('%.2f', Op::div($total_sales->projected, $total_hours->projected)),
            '',
            sprintf('%.2f', Op::div($total_sales->trend, $total_hours->trend)),
            number_format($total_hours->actual == 0 ? 0 : $total_sales->thisYear / $total_hours->actual, 2),
            '',
            '',
            number_format(Op::div($total_sales->thisYear, $total_hours->actual) - Op::div($total_sales->projected, $total_hours->projected), 2),
            number_format($quarter_actual_sph - $quarter_proj_sph, 2),
            'meta' => FannieReportPage::META_COLOR,
            'meta_background' => $this->colors[0],
            'meta_foreground' => 'black',
        );
         */

        $transGrowth = $store == 1 ? 0.925 : 1.0;
        $proj_trans = $total_trans->lastYear * $transGrowth;
        $qtd_proj_trans = $total_trans->quarterLastYear * $transGrowth;
        $data[] = array(
            'Transactions',
            number_format($total_trans->lastYear),
            number_format($proj_trans),
            '',
            '',
            number_format($total_trans->thisYear),
            sprintf('%.2f%%', $this->percentGrowth($total_trans->thisYear, $total_trans->lastYear)),
            '',
            number_format($total_trans->thisYear - $proj_trans),
            number_format($total_trans->quarterThisYear - $qtd_proj_trans),
            'meta' => FannieReportPage::META_COLOR,
            'meta_background' => $this->colors[0],
            'meta_foreground' => 'black',
        );

        $data[] = array(
            'Average Basket',
            number_format($total_sales->lastYear / $total_trans->lastYear, 2),
            number_format($total_sales->projected / $proj_trans, 2),
            '',
            '',
            number_format($total_trans->thisYear == 0 ? 0 : $total_sales->thisYear / $total_trans->thisYear, 2),
            sprintf('%.2f%%', $this->percentGrowth($total_trans->thisYear == 0 ? 0 : $total_sales->thisYear/$total_trans->thisYear, $total_sales->lastYear/$total_trans->lastYear)),
            '',
            number_format(($total_trans->thisYear == 0 ? 0 : $total_sales->thisYear/$total_trans->thisYear) - ($total_sales->projected/$proj_trans), 2),
            number_format(($total_sales->quarterActual/$total_trans->quarterThisYear) - ($total_sales->quarterProjected/$qtd_proj_trans), 2),
            'meta' => FannieReportPage::META_COLOR,
            'meta_background' => $this->colors[0],
            'meta_foreground' => 'black',
        );

        if (count($this->colors) > 1) {
            array_shift($this->colors);
        }

        $otherStore = $this->getOtherStore($store, $week->obfWeekID());

        //$data[] = array('meta'=>FannieReportPage::META_REPEAT_HEADERS);
        $cat = new ObfCategoriesModelV2($dbc);
        $cat->hasSales(0);
        $cat->name('Admin');
        foreach ($cat->find('name') as $c) {
            continue; // no longer doing labor data
            $data[] = $this->headerRow($c->name());
            $labor->obfCategoryID($c->obfCategoryID());
            $labor->load();

            $quarter = $dbc->getRow($this->quarterLaborP, 
                array($week->obfLaborQuarterID(), $labor->obfCategoryID(), date('Y-m-d 00:00:00', $end_ts))
            );
            if ($quarter === false) {
                $quarter = array('hours'=>0, 'wages'=>0, 'laborTarget'=>0, 'hoursTarget'=>0);
            }
            $qt_average_wage = $quarter['hours'] == 0 ? 0 : $quarter['wages'] / ((float)$quarter['hours']);
            $qt_proj_hours = $c->salesPerLaborHourTarget() == 0 ? 0 : $total_sales->quarterProjected / $c->salesPerLaborHourTarget();
            $qt_proj_labor = $qt_proj_hours * $qt_average_wage;
            $total_hours->quarterActual += $quarter['hours'];
            $total_hours->quarterProjected += $qt_proj_hours;

            list($proj_hours, $trend_hours) = $this->projectHours($labor->splhTarget(), $total_sales->projected+$otherStore['plan'], $total_sales->trend);
            list($proj_wages, $trend_wages) = $this->projectWages($labor, $proj_hours, $trend_hours);

            $data[] = array(
                'Hours',
                '',
                number_format($proj_hours, 0),
                '',
                '',//number_format($trend_hours, 0),
                number_format($labor->hours(), 0),
                '',
                '',
                number_format($labor->hours() - $proj_hours, 0),
                '',//number_format($quarter['hours'] - $qt_proj_hours, 0),
                'meta' => FannieReportPage::META_COLOR,
                'meta_background' => $this->colors[0],
                'meta_foreground' => 'black',
            );
            $qtd_hours_ou += ($quarter['hours'] - $qt_proj_hours);

            $quarter_actual_sph = $quarter['hours'] == 0 ? 0 : ($total_sales->quarterActual)/($quarter['hours']);
            $quarter_proj_sph = $qt_proj_hours == 0 ? 0 : ($total_sales->quarterProjected)/($qt_proj_hours);
            $data[] = array(
                'Sales per Hour',
                '',
                sprintf('%.2f', $proj_hours == 0 ? 0 : ($total_sales->projected+$otherStore['plan']) / $proj_hours),
                '',
                '',//sprintf('%.2f', $total_sales->trend / $trend_hours),
                number_format($labor->hours() == 0 ? 0 : ($total_sales->thisYear+$otherStore['actual']) / $labor->hours(), 2),
                '',
                '',
                number_format(Op::div($total_sales->thisYear, $labor->hours()) - Op::div($total_sales->projected, $proj_hours), 2),
                '',//number_format($quarter_actual_sph - $quarter_proj_sph, 2),
                'meta' => FannieReportPage::META_COLOR,
                'meta_background' => $this->colors[0],
                'meta_foreground' => 'black',
            );

            /*
            $data[] = array(
                'Labor % of Sales',
                '',
                '',
                '',
                '',
                sprintf('%.2f%%', $labor->wages() / ($total_sales->thisYear + $otherStore['actual']) * 100),
                '',
                '',
                '',
                '',
                'meta' => FannieReportPage::META_COLOR,
                'meta_background' => $this->colors[0],
                'meta_foreground' => 'black',
            );
            */

            $total_hours->actual += $labor->hours();
            $total_hours->projected += $proj_hours;
            $total_hours->trend += $trend_hours;

            if (count($this->colors) > 1) {
                array_shift($this->colors);
            }
        }

        $data[] = array('meta'=>FannieReportPage::META_REPEAT_HEADERS);
        /**
           Organization totals section
        */
        $data[] = $this->headerRow('Total Organization');

        $data[] = array(
            'Sales',
            number_format($total_sales->lastYear+$otherStore['lastYear'], 0),
            number_format($total_sales->projected+$otherStore['plan'], 0),
            '',
            '',
            number_format($total_sales->thisYear+$otherStore['actual'], 0),
            sprintf('%.2f%%', $this->percentGrowth($total_sales->thisYear+$otherStore['actual'], $total_sales->lastYear+$otherStore['lastYear'])),
            '',
            number_format(($total_sales->thisYear+$otherStore['actual']) - ($total_sales->projected+$otherStore['plan']), 0),
            '',
            'meta' => FannieReportPage::META_COLOR,
            'meta_background' => $this->colors[0],
            'meta_foreground' => 'black',
        );

        $data[] = $this->discountsThisWeek($dbc, $start_ts, $end_ts, $start_ly, $end_ly);

        $data[] = array(
            'Hours',
            '',
            number_format($total_hours->projected+$otherStore['planHours'], 0),
            '',
            '',
            number_format($total_hours->actual+$otherStore['hours'], 0),
            '',
            '',
            number_format(($total_hours->actual+$otherStore['hours']) - ($total_hours->projected+$otherStore['planHours']), 0),
            '',
            'meta' => FannieReportPage::META_COLOR,
            'meta_background' => $this->colors[0],
            'meta_foreground' => 'black',
        );

        $quarter_actual_sph = $total_hours->quarterActual == 0 ? 0 : ($total_sales->quarterActual)/($total_hours->quarterActual);
        $quarter_proj_sph = $total_hours->quarterProjected == 0 ? 0 : ($total_sales->quarterProjected)/($total_hours->quarterProjected);
        $data[] = array(
            'Sales per Hour',
            '',
            sprintf('%.2f', Op::div($total_sales->projected+$otherStore['plan'], ($total_hours->projected+$otherStore['planHours']))),
            '',
            '',
            number_format($total_hours->actual == 0 ? 0 : ($total_sales->thisYear+$otherStore['actual']) / ($total_hours->actual+$otherStore['hours']), 2),
            '',
            '',
            number_format($total_hours->actual == 0 ? 0 : (($total_sales->thisYear+$otherStore['actual'])/($total_hours->actual+$otherStore['hours'])) - (($total_sales->projected+$otherStore['plan'])/($total_hours->projected+$otherStore['planHours'])), 2),
            '',//number_format($quarter_actual_sph - $quarter_proj_sph, 2),
            'meta' => FannieReportPage::META_COLOR,
            'meta_background' => $this->colors[0],
            'meta_foreground' => 'black',
        );

        if (count($this->colors) > 1) {
            array_shift($this->colors);
        }

        $data[] = array('meta'=>FannieReportPage::META_REPEAT_HEADERS);
        $owners = $this->ownershipThisWeek($dbc, $start_ts, $end_ts, $start_ly, $end_ly, false);
        $data[] = array($owners[0], $owners[2], 13.5, '', '', $owners[1], '', '', '', '', 
            'meta' => $owners['meta'], 'meta_background' => $owners['meta_background']);
        $owners = $this->ownershipThisYear($dbc, $end_ts);
        $data[] = array($owners[0], $owners[2], 700, '', '', $owners[1], '', '', '', 
            'meta' => $owners['meta'], 'meta_background' => $owners['meta_background']);
        $owners = $this->newEquityThisWeek($dbc, $start_ts, $end_ts, $start_ly, $end_ly);
        $data[] = array($owners[0], $owners[2], 1050, '', '', $owners[1], '', '', '', 
            'meta' => $owners['meta'], 'meta_background' => $owners['meta_background']);

        $json = $this->chartData($dbc, $this->form->weekID, $store);
        $this->addOnloadCommand("obfSummary.drawChart('" . json_encode($json) . "')");

        return $data;
    }

    private function discountsThisWeek($dbc, $start_ts, $end_ts, $start_ly, $end_ly)
    {
        $date1 = date('Y-m-d', $start_ts);
        $date2 = date('Y-m-d', $end_ts);
        $date3 = date('Y-m-d', $start_ly);
        $date4 = date('Y-m-d', $end_ly);

        $dlog = DTransactionsModel::selectDlog($date1, $date2);
        $dlogLY = DTransactionsModel::selectDlog($date3, $date4);
        $opdb = $this->config->get('OP_DB') . $dbc->sep();

        $discountQ = "
            SELECT SUM(d.total)
            FROM __DLOG__ AS d
                LEFT JOIN {$opdb}houseCoupons AS h ON RIGHT(d.upc,5) = h.coupID
            WHERE d.tdate BETWEEN ? AND ?
                AND (
                    (d.upc='DISCOUNT' AND d.memType=5)
                    OR
                    (d.upc LIKE '00499999%' AND h.memberOnly=1)
                )
        ";

        $discountP = $dbc->prepare(str_replace('__DLOG__', $dlog, $discountQ));
        $total = $dbc->getValue($discountP, array($date1 . ' 00:00:00', $date2 . ' 23:59:59'));
        $discountP = $dbc->prepare(str_replace('__DLOG__', $dlogLY, $discountQ));
        $totalLY = $dbc->getValue($discountP, array($date3 . ' 00:00:00', $date4 . ' 23:59:59'));

        return array(
            'Owner Discounts',
            sprintf('%.0f', $totalLY),
            '',
            '',
            '',
            sprintf('%.0f', $total),
            sprintf('%.2f%%', $this->percentGrowth($total, $totalLY)),
            '',
            '',
            '',
            'meta' => FannieReportPage::META_COLOR,
            'meta_background' => $this->colors[0],
            'meta_foreground' => 'black',
        );
    }

    private function chartData($dbc, $weekID, $storeID)
    {
        $begin = $weekID - 12;
        $json = array(
            'labels' => array(),
            'sales' => array(),
            'lySales' => array(),
            'hours' => array(),
            'lyHours' => array(),
            'splh' => array(),
            'lySplh' => array(),
        );

        $hourP = $dbc->prepare("SELECT SUM(hours) 
            FROM ObfLabor AS l
                LEFT JOIN ObfCategories AS c ON l.obfCategoryID=c.obfCategoryID
            WHERE obfWeekID=?
                    AND c.storeID=?");

        $infoP = $dbc->prepare("
            SELECT o.obfWeekID,
                SUM(o.actualSales) AS sales,
                SUM(o.lastYearSales) AS lySales,
                MAX(w.startDate) AS startDate,
                MAX(w.endDate) AS endDate
            FROM ObfSalesCache AS o
                LEFT JOIN ObfWeeks AS w ON o.obfWeekID=w.obfWeekID
                LEFT JOIN ObfCategories AS c ON o.obfCategoryID=c.obfCategoryID
            WHERE o.obfWeekID BETWEEN ? AND ?
                AND c.storeID=?
            GROUP BY o.obfWeekID
            ORDER BY o.obfWeekID");
        $infoR = $dbc->execute($infoP, array($begin, $weekID, $storeID));
        while ($infoW = $dbc->fetchRow($infoR)) {
            $dstr = date('m/d', strtotime($infoW['startDate']))
                . ' - '
                . date('m/d', strtotime($infoW['endDate']));
            if (!in_array($dstr, $json['labels'])) {
                $json['labels'][] = $dstr;
            }
            if ($infoW['sales'] > 0) {
                $json['sales'][] = $infoW['sales'];
            }
            $json['lySales'][] = $infoW['lySales'];

            $hours = $dbc->getValue($hourP, array($infoW['obfWeekID'], $storeID));
            $lyHours = $dbc->getValue($hourP, array($infoW['obfWeekID'] - 52, $storeID));
            if ($hours > 0) {
                $json['hours'][] = $hours;
                $json['splh'][] = $hours == 0 ? 0 : $infoW['sales'] / $hours;
            }
            $json['lyHours'][] = $lyHours;
            $json['lySplh'][] = $lyHours == 0 ? 0 : $infoW['lySales'] / $lyHours;
        }

        return $json;
    }

    private function getOtherStore($storeID, $weekID)
    {
        $dbc = $this->connection;
        $conf = $this->config->get('PLUGIN_SETTINGS');
        $dbc->selectDB($conf['ObfDatabaseV2']);
        $PLAN_SALES = $this->getPlanSales($weekID);
        /**
          Get sales, plan, and transactions from cache
          Loops through categories to project hours for
          each individual category based on sales
        */
        $query = $dbc->prepare('
            SELECT SUM(actualSales) AS actual,
                SUM(lastYearSales) AS lastYear,
                MAX(transactions) AS trans,
                MAX(lastYearTransactions) AS lyTrans,
                SUM(lastYearSales * (1+s.growthTarget)) AS plan,
                s.obfCategoryID AS catID
            FROM ObfSalesCache AS s
                INNER JOIN ObfCategories AS c ON s.obfCategoryID=c.obfCategoryID
            WHERE s.obfWeekID=?
                AND c.storeID=?
            GROUP BY s.obfCategoryID');
        $args = array($weekID, $storeID==1?2:1);
        $info = array('actual'=>0, 'lastYear'=>0, 'trans'=>0, 'lyTrans'=>0, 'plan'=>0);
        $res = $dbc->execute($query, $args);
        $cat = new ObfCategoriesModelV2($dbc);
        $plan = array();
        while ($row = $dbc->fetchRow($res)) {
            $info['actual'] += $row['actual'];
            $info['lastYear'] += $row['lastYear'];
            $info['trans'] = $row['trans'];
            $info['lyTrans'] = $row['lyTrans'];
            $catPlan = 0;
            foreach ($PLAN_SALES as $planID => $planVal) {
                if (strpos($planID, $row['catID'] . ',') === 0) {
                    $info['plan'] += $planVal;
                    $catPlan += $planVal;
                }
            }
            $cat->obfCategoryID($row['catID']);
            $cat->load();
            $plan[$row['catID']] = $this->projectHours($cat->salesPerLaborHourTarget(), $catPlan, $catPlan);
        }

        /**
          Get additional hours & wages from non-inventory labor
          Plan hours is built from hours projected in the previous loop
          If plan hours was NOT previously calculated it means the category has
          no sales and should use total store sales for projecting instead
        */
        $extra = $dbc->prepare('
            SELECT hours,
                l.obfCategoryID AS catID
            FROM ObfLabor AS l
                INNER JOIN ObfCategories AS c ON l.obfCategoryID=c.obfCategoryID
            WHERE l.obfWeekID=?
                AND c.storeID=?');
        $info['hours'] = 0;
        $info['planHours'] = 0;
        $res = $dbc->execute($extra, $args);
        while ($row = $dbc->fetchRow($res)) {
            $info['hours'] += $row['hours'];
            if (isset($plan[$row['catID']])) {
                $info['planHours'] += $plan[$row['catID']][0];
            } else {
                $cat->obfCategoryID($row['catID']);
                $cat->load();
                list($tmpP, $tmpT) = $this->projectHours($cat->salesPerLaborHourTarget(), $info['plan'], $info['plan']);
                $info['planHours'] += $tmpP;
            }
        }

        return $info;
    }
}

FannieDispatch::conditionalExec();