public function testStats()
{
$this->assertEquals(0, \COREPOS\Fannie\API\lib\Stats::percentGrowth(50, 0));
$this->assertEquals(100.0, \COREPOS\Fannie\API\lib\Stats::percentGrowth(50, 25));
$points = array(array(1, 1), array(2, 2), array(3, 3), array(4, 4), array(5, 5));
$res = \COREPOS\Fannie\API\lib\Stats::removeOutliers($points);
$this->assertEquals(array(array(2, 2), array(3, 3), array(4, 4)), $res);
$this->assertEquals(array(), \COREPOS\Fannie\API\lib\Stats::removeOutliers(array()));
$lsq = \COREPOS\Fannie\API\lib\Stats::leastSquare($points);
$this->assertEquals(array('slope' => 1, 'y_intercept' => 0), $lsq);
$exp = \COREPOS\Fannie\API\lib\Stats::exponentialFit($points);
$this->assertInternalType('object', $exp);
}
protected function percentGrowth($a, $b)
{
return \COREPOS\Fannie\API\lib\Stats::percentGrowth($a, $b);
}
public function fetch_report_data()
{
$class_lib = $this->class_lib;
$dbc = $class_lib::getDB();
$week = $class_lib::getWeek($dbc);
$week->obfWeekID($this->form->weekID);
$week->load();
$colors = array('#CDB49B', '#99C299', '#CDB49B', '#99C299', '#CDB49B', '#99C299', '#CDB49B', '#99C299', '#CDB49B', '#99C299', '#CDB49B', '#99C299', '#CDB49B', '#99C299');
$labor = new ObfLaborModelV2($dbc);
$labor->obfWeekID($week->obfWeekID());
/**
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);
/**
Use the entire month from the previous calendar year
as the time period for year-over-year comparisons
*/
$start_ly = mktime(0, 0, 0, $month, 1, $year - 1);
$end_ly = mktime(0, 0, 0, $month, date('t', $start_ly), $year - 1);
$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();
if (count($num_cached) == 0 || true) {
$dateInfo = array('start_ts' => $start_ts, 'end_ts' => $end_ts, 'start_ly' => $start_ly, 'end_ly' => $end_ly);
$this->updateSalesCache($week, $num_cached, $dateInfo);
}
// record set to return
$data = array();
/**
Information about sales
- thisYear => sales for the current week
- lastYear => sales for the same week last year
- projected => planned sales for the current week
based on sales growth goals
- trend => expected sales for the current week based
on recent history sales trends
- quarterActual => actual sales for the quarter
- quarterProjected => planned sales for the quarter based
on sales growth goals
- quarterLaborSales => actual sales for the quarter as
defined by labor measurements
"Quarter" is not necessarily a calendar quarter. It's
whatever period is currently defined for the "long-term"
over under column. This period can be defined separately
for sales and labor. A separate sales number is always
tracked in concert with the long-term labor period so that
the long-term sales per labor hour number makes sense.
*/
$total_sales = new stdClass();
$total_sales->thisYear = 0.0;
$total_sales->lastYear = 0.0;
$total_sales->projected = 0.0;
$total_sales->trend = 0.0;
$total_sales->quarterActual = 0.0;
$total_sales->quarterProjected = 0.0;
$total_sales->quarterLaborSales = 0.0;
/**
Information about number of transactions
- thisYear => transactions for the current week
- lastYear => transactions for the same week last year
- quarterThisYear => transactions for the quarter
- quarterLastYear => transactions for the same quarter
year-over-year
See sales above for more info about "Quarters"
*/
$total_trans = new stdClass();
$total_trans->thisYear = 0;
$total_trans->lastYear = 0;
$total_trans->quarterThisYear = 0;
$total_trans->quarterLastYear = 0;
/**
Information about labor hours
- actual => actual hours for the current week
- projected => planned hours for the current week
based on sales growth and SPLH goals
- trend => expected hours for the current week based
on recent history sales trends and
SPH goals
- quarterActual => actual hours for the quarter
- quarterProjected => planned hours for the quarter
based on sales growth goals
See sales above for more info about "Quarters"
*/
$total_hours = new stdClass();
$total_hours->actual = 0.0;
$total_hours->projected = 0.0;
$total_hours->trend = 0.0;
$total_hours->quarterActual = 0.0;
$total_hours->quarterProjected = 0.0;
$qtd_sales_ou = 0;
$qtd_hours_ou = 0;
/**
Look up sales for the week in a given category
*/
$salesP = $dbc->prepare('SELECT s.actualSales,
s.lastYearSales,
s.growthTarget,
n.super_name,
s.superID,
s.transactions,
s.lastYearTransactions
FROM ObfSalesCache AS s
LEFT JOIN ' . $this->config->get('OP_DB') . $dbc->sep() . 'superDeptNames
AS n ON s.superID=n.superID
WHERE s.obfWeekID=?
AND s.obfCategoryID=?
ORDER BY s.superID,n.super_name');
/**
Look up sales for the [sales] quarter in a given category
*/
$quarterSalesP = $dbc->prepare('SELECT SUM(s.actualSales) AS actual,
SUM(s.lastYearSales) AS lastYear,
SUM(s.lastYearSales * (1+s.growthTarget)) AS plan,
SUM(s.transactions) AS trans,
SUM(s.lastYearTransactions) AS ly_trans
FROM ObfSalesCache AS s
INNER JOIN ObfWeeks AS w ON s.obfWeekID=w.obfWeekID
WHERE w.obfQuarterID = ?
AND s.obfCategoryID = ?
AND s.superID=?
AND w.endDate <= ?');
/**
Look up labor for the [labor] quarter in a given category
*/
$quarterLaborP = $dbc->prepare('SELECT SUM(l.hours) AS hours,
SUM(l.wages) AS wages,
AVG(l.laborTarget) as laborTarget,
AVG(l.averageWage) as averageWage,
SUM(l.hoursTarget) as hoursTarget
FROM ObfLabor AS l
INNER JOIN ObfWeeks AS w ON l.obfWeekID=w.obfWeekID
WHERE w.obfLaborQuarterID=?
AND l.obfCategoryID=?
AND w.endDate <= ?');
/**
Look up sales for the [labor] quarter in a given category
Since the "quarter" can differ for long-term sales and
long-term labor, this value is needed to calculate
long-term SPLH correctly.
*/
$quarterSplhP = $dbc->prepare('SELECT SUM(c.actualSales) AS actualSales,
SUM(c.lastYearSales * (1+c.growthTarget)) AS planSales
FROM ObfLabor AS l
INNER JOIN ObfWeeks AS w ON l.obfWeekID=w.obfWeekID
INNER JOIN ObfSalesCache AS c ON c.obfWeekID=l.obfWeekID
AND c.obfCategoryID=l.obfCategoryID
WHERE w.obfLaborQuarterID=?
AND l.obfCategoryID=?
AND w.endDate <= ?');
/**
Trends are based on the previous
thirteen weeks that contain sales data.
First build a list of week IDs, then
prepare statement to query a specific category
of sales data.
*/
$splhWeeks = '(';
$splhWeekQ = '
SELECT c.obfWeekID
FROM ObfSalesCache AS c
INNER JOIN ObfWeeks AS w ON c.obfWeekID=w.obfWeekID
WHERE c.obfWeekID < ?
GROUP BY c.obfWeekID
HAVING SUM(c.actualSales) > 0
ORDER BY MAX(w.endDate) DESC';
$splhWeekQ = $dbc->add_select_limit($splhWeekQ, 13);
$splhWeekP = $dbc->prepare($splhWeekQ);
$splhWeekR = $dbc->execute($splhWeekP, array($week->obfWeekID()));
while ($splhWeekW = $dbc->fetch_row($splhWeekR)) {
$splhWeeks .= sprintf('%d,', $splhWeekW['obfWeekID']);
}
if ($splhWeeks == '(') {
$splhWeeks .= '-99999';
}
$splhWeeks = substr($splhWeeks, 0, strlen($splhWeeks) - 1) . ')';
$trendQ = '
SELECT
actualSales,
lastYearSales
FROM ObfSalesCache AS c
WHERE c.obfCategoryID = ?
AND c.superID = ?
AND c.actualSales > 0
AND c.obfWeekID IN ' . $splhWeeks . '
ORDER BY c.obfWeekID';
$trendP = $dbc->prepare($trendQ);
/**
LOOP ONE
Examine OBF Categories that have sales. These will include
both sales and labor information
*/
$categories = new ObfCategoriesModelV2($dbc);
$categories->hasSales(1);
foreach ($categories->find('name') as $category) {
$data[] = array($category->name(), '', '', '', '', '', '', '', '', '', 'meta' => FannieReportPage::META_BOLD | FannieReportPage::META_COLOR, 'meta_background' => $colors[0], 'meta_foreground' => 'black');
$sum = array(0.0, 0.0);
$dept_proj = 0.0;
$dept_trend = 0;
$salesR = $dbc->execute($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)) {
$proj = $row['lastYearSales'] * $row['growthTarget'] + $row['lastYearSales'];
$trendR = $dbc->execute($trendP, array($category->obfCategoryID(), $row['superID']));
$trend_data = array();
$t_count = 0;
while ($trendW = $dbc->fetchRow($trendR)) {
$trend_data[] = array($t_count, $trendW['actualSales']);
$t_count++;
}
$trend_data = \COREPOS\Fannie\API\lib\Stats::removeOutliers($trend_data);
$exp = \COREPOS\Fannie\API\lib\Stats::exponentialFit($trend_data);
$trend1 = exp($exp->a) * exp($exp->b * $t_count);
$dept_trend += $trend1;
$total_sales->trend += $trend1;
$quarter = $dbc->execute($quarterSalesP, array($week->obfQuarterID(), $category->obfCategoryID(), $row['superID'], date('Y-m-d 00:00:00', $end_ts)));
if ($dbc->num_rows($quarter) == 0) {
$quarter = array('actual' => 0, 'lastYear' => 0, 'plan' => 0, 'trans' => 0, 'ly_trans' => 0);
} else {
$quarter = $dbc->fetch_row($quarter);
}
$qtd_dept_plan += $quarter['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), number_format($trend1, 0), number_format($row['actualSales'], 0), sprintf('%.2f%%', $this->percentGrowth($row['actualSales'], $row['lastYearSales'])), number_format($row['actualSales'], 0), number_format($row['actualSales'] - $proj, 0), number_format($quarter['actual'] - $quarter['plan'], 0), 'meta' => FannieReportPage::META_COLOR, 'meta_background' => $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 += $quarter['plan'];
$total_sales->quarterActual += $quarter['actual'];
$qtd_sales_ou += $quarter['actual'] - $quarter['plan'];
$qtd_dept_ou += $quarter['actual'] - $quarter['plan'];
$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), 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' => $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
$proj_hours = $dept_proj / $category->salesPerLaborHourTarget();
$trend_hours = $dept_trend / $category->salesPerLaborHourTarget();
// approximate wage to convert hours into dollars
$average_wage = 0;
if ($labor->hours() != 0) {
$average_wage = $labor->wages() / (double) $labor->hours();
}
$proj_wages = $proj_hours * $average_wage;
$trend_wages = $trend_hours * $average_wage;
$quarter = $dbc->execute($quarterLaborP, array($week->obfLaborQuarterID(), $labor->obfCategoryID(), date('Y-m-d 00:00:00', $end_ts)));
if ($dbc->num_rows($quarter) == 0) {
$quarter = array('hours' => 0, 'wages' => 0, 'laborTarget' => 0, 'hoursTarget' => 0, 'actualSales' => 0);
} else {
$quarter = $dbc->fetch_row($quarter);
}
$qt_splh = $dbc->execute($quarterSplhP, array($week->obfLaborQuarterID(), $labor->obfCategoryID(), date('Y-m-d 00:00:00', $end_ts)));
if ($dbc->num_rows($qt_splh)) {
$row = $dbc->fetch_row($qt_splh);
$quarter['actualSales'] = $row['actualSales'];
$quarter['planSales'] = $row['planSales'];
}
$qt_average_wage = $quarter['hours'] == 0 ? 0 : $quarter['wages'] / (double) $quarter['hours'];
$qt_proj_hours = $quarter['planSales'] / $category->salesPerLaborHourTarget();
$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' => $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($dept_proj / $proj_hours, 2), '', number_format($dept_trend / $trend_hours, 2), number_format($labor->hours() == 0 ? 0 : $sum[0] / $labor->hours(), 2), sprintf('%.2f%%', $this->percentGrowth($labor->hours() == 0 ? 0 : $sum[0] / $labor->hours(), $dept_proj / $proj_hours)), '', number_format(($labor->hours() == 0 ? 0 : $sum[0] / $labor->hours()) - $dept_proj / $proj_hours, 2), number_format($quarter_actual_sph - $quarter_proj_sph, 2), 'meta' => FannieReportPage::META_COLOR, 'meta_background' => $colors[0], 'meta_foreground' => 'black');
$data[] = array('meta' => FannieReportPage::META_REPEAT_HEADERS);
if (count($colors) > 1) {
array_shift($colors);
}
}
/**
Now that total sales for the all categories have been calculated,
go back and divide specific columns by total sales to get
percentage of sales
*/
for ($i = 0; $i < count($data); $i++) {
if (isset($data[$i][7]) && preg_match('/^[\\d,]+$/', $data[$i][7])) {
$amt = str_replace(',', '', $data[$i][7]);
$percentage = $total_sales->thisYear == 0 ? 0.0 : (double) $amt / (double) $total_sales->thisYear;
$data[$i][7] = number_format($percentage * 100, 2) . '%';
}
if (isset($data[$i][3]) && preg_match('/^[\\d,]+$/', $data[$i][3])) {
$amt = str_replace(',', '', $data[$i][3]);
$percentage = (double) $amt / (double) $total_sales->projected;
$data[$i][3] = number_format($percentage * 100, 2) . '%';
}
}
/**
LOOP TWO
Examine OBF Categories without sales. These will include
only labor information
*/
$cat = new ObfCategoriesModelV2($dbc);
$cat->hasSales(0);
$cat->name('Admin', '<>');
foreach ($cat->find('name') as $c) {
$data[] = array($c->name(), '', '', '', '', '', '', '', '', '', 'meta' => FannieReportPage::META_BOLD | FannieReportPage::META_COLOR, 'meta_background' => $colors[0], 'meta_foreground' => 'black');
$labor->obfCategoryID($c->obfCategoryID());
$labor->load();
$quarter = $dbc->execute($quarterLaborP, array($week->obfLaborQuarterID(), $labor->obfCategoryID(), date('Y-m-d 00:00:00', $end_ts)));
if ($dbc->num_rows($quarter) == 0) {
$quarter = array('hours' => 0, 'wages' => 0, 'laborTarget' => 0, 'hoursTarget' => 0);
} else {
$quarter = $dbc->fetch_row($quarter);
}
$qt_average_wage = $quarter['hours'] == 0 ? 0 : $quarter['wages'] / (double) $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;
$average_wage = 0;
if ($labor->hours() != 0) {
$average_wage = $labor->wages() / (double) $labor->hours();
}
// use SPLH instead of pre-allocated
$proj_hours = $total_sales->projected / $c->salesPerLaborHourTarget();
$proj_wages = $proj_hours * $average_wage;
$trend_hours = $total_sales->trend / $c->salesPerLaborHourTarget();
$trend_wages = $trend_hours * $average_wage;
$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' => $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', $total_sales->projected / $proj_hours), '', sprintf('%.2f', $total_sales->trend / $trend_hours), number_format($labor->hours() == 0 ? 0 : $total_sales->thisYear / $labor->hours(), 2), '', '', number_format(($labor->hours() == 0 ? 0 : $total_sales->thisYear / $labor->hours()) - $total_sales->projected / $proj_hours, 2), number_format($quarter_actual_sph - $quarter_proj_sph, 2), 'meta' => FannieReportPage::META_COLOR, 'meta_background' => $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($colors) > 1) {
array_shift($colors);
}
}
/**
Storewide totals section
*/
$data[] = array('Total Store', '', '', '', '', '', '', '', '', '', 'meta' => FannieReportPage::META_BOLD | FannieReportPage::META_COLOR, 'meta_background' => $colors[0], 'meta_foreground' => 'black');
$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' => $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' => $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', $total_sales->projected / $total_hours->projected), '', sprintf('%.2f', $total_sales->trend / $total_hours->trend), number_format($total_hours->actual == 0 ? 0 : $total_sales->thisYear / $total_hours->actual, 2), '', '', number_format(($total_hours->actual == 0 ? 0 : $total_sales->thisYear / $total_hours->actual) - $total_sales->projected / $total_hours->projected, 2), number_format($quarter_actual_sph - $quarter_proj_sph, 2), 'meta' => FannieReportPage::META_COLOR, 'meta_background' => $colors[0], 'meta_foreground' => 'black');
$proj_trans = $total_trans->lastYear * 1.05;
$qtd_proj_trans = $total_trans->quarterLastYear * 1.05;
$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' => $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' => $colors[0], 'meta_foreground' => 'black');
if (count($colors) > 1) {
array_shift($colors);
}
$data[] = array('meta' => FannieReportPage::META_REPEAT_HEADERS);
$cat = new ObfCategoriesModelV2($dbc);
$cat->hasSales(0);
$cat->name('Admin');
foreach ($cat->find('name') as $c) {
$data[] = array($c->name(), '', '', '', '', '', '', '', '', '', 'meta' => FannieReportPage::META_BOLD | FannieReportPage::META_COLOR, 'meta_background' => $colors[0], 'meta_foreground' => 'black');
$labor->obfCategoryID($c->obfCategoryID());
$labor->load();
$quarter = $dbc->execute($quarterLaborP, array($week->obfLaborQuarterID(), $labor->obfCategoryID(), date('Y-m-d 00:00:00', $end_ts)));
if ($dbc->num_rows($quarter) == 0) {
$quarter = array('hours' => 0, 'wages' => 0, 'laborTarget' => 0, 'hoursTarget' => 0);
} else {
$quarter = $dbc->fetch_row($quarter);
}
$qt_average_wage = $quarter['hours'] == 0 ? 0 : $quarter['wages'] / (double) $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;
$average_wage = 0;
if ($labor->hours() != 0) {
$average_wage = $labor->wages() / (double) $labor->hours();
}
// use SPLH instead of pre-allocated
$proj_hours = $total_sales->projected / $c->salesPerLaborHourTarget();
$proj_wages = $proj_hours * $average_wage;
$trend_hours = $total_sales->trend / $c->salesPerLaborHourTarget();
$trend_wages = $trend_hours * $average_wage;
$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' => $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', $total_sales->projected / $proj_hours), '', sprintf('%.2f', $total_sales->trend / $trend_hours), number_format($labor->hours() == 0 ? 0 : $total_sales->thisYear / $labor->hours(), 2), '', '', number_format(($labor->hours() == 0 ? 0 : $total_sales->thisYear / $labor->hours()) - $total_sales->projected / $proj_hours, 2), number_format($quarter_actual_sph - $quarter_proj_sph, 2), 'meta' => FannieReportPage::META_COLOR, 'meta_background' => $colors[0], 'meta_foreground' => 'black');
$total_hours->actual += $labor->hours();
$total_hours->projected += $proj_hours;
$total_hours->trend += $trend_hours;
if (count($colors) > 1) {
array_shift($colors);
}
}
$data[] = array('meta' => FannieReportPage::META_REPEAT_HEADERS);
/**
Organization totals section
*/
$data[] = array('Total Organization', '', '', '', '', '', '', '', '', '', 'meta' => FannieReportPage::META_BOLD | FannieReportPage::META_COLOR, 'meta_background' => $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' => $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', $total_sales->projected / $total_hours->projected), '', sprintf('%.2f', $total_sales->trend / $total_hours->trend), number_format($total_hours->actual == 0 ? 0 : $total_sales->thisYear / $total_hours->actual, 2), '', '', number_format(($total_hours->actual == 0 ? 0 : $total_sales->thisYear / $total_hours->actual) - $total_sales->projected / $total_hours->projected, 2), number_format($quarter_actual_sph - $quarter_proj_sph, 2), 'meta' => FannieReportPage::META_COLOR, 'meta_background' => $colors[0], 'meta_foreground' => 'black');
$data[] = array('meta' => FannieReportPage::META_REPEAT_HEADERS);
$stockP = $dbc->prepare('
SELECT SUM(stockPurchase) AS ttl
FROM ' . $this->config->get('TRANS_DB') . $dbc->sep() . 'stockpurchases
WHERE tdate BETWEEN ? AND ?
AND dept=992
AND trans_num NOT LIKE \'1001-30-%\'
');
$args1 = array(date('Y-07-01 00:00:00', $end_ts), date('Y-m-d 23:59:59', $end_ts));
if (date('n', $end_ts) < 7) {
$args1[0] = date('Y', $end_ts) - 1 . '-07-01 00:00:00';
}
$last_year = mktime(0, 0, 0, date('n', $end_ts), date('j', $end_ts), date('Y', $end_ts) - 1);
$args2 = array(date('Y-07-01 00:00:00', $last_year), date('Y-m-d 23:59:59', $last_year));
if (date('n', $last_year) < 7) {
$args2[0] = date('Y', $last_year) - 1 . '-07-01 00:00:00';
}
$args3 = array(date('Y-m-d 00:00:00', $start_ts), date('Y-m-d 23:59:59', $end_ts));
$args4 = array(date('Y-m-d 00:00:00', $start_ly), date('Y-m-d 23:59:59', $end_ly));
$current = $dbc->execute($stockP, $args1);
$prior = $dbc->execute($stockP, $args2);
$this_week = $dbc->execute($stockP, $args3);
$last_week = $dbc->execute($stockP, $args4);
if ($dbc->num_rows($current) > 0) {
$current = $dbc->fetch_row($current);
$current = $current['ttl'] / 20;
} else {
$current = 0;
}
if ($dbc->num_rows($prior) > 0) {
$prior = $dbc->fetch_row($prior);
$prior = $prior['ttl'] / 20;
} else {
$prior = 0;
}
if ($dbc->num_rows($this_week) > 0) {
$this_week = $dbc->fetch_row($this_week);
$this_week = $this_week['ttl'] / 20;
} else {
$this_week = 0;
}
if ($dbc->num_rows($last_week) > 0) {
$last_week = $dbc->fetch_row($last_week);
$last_week = $last_week['ttl'] / 20;
$num_days = (double) date('t', $start_ly);
$last_week = round($last_week / $num_days * 7);
} else {
$last_week = 0;
}
$data[] = array('Ownership This Week', number_format($this_week, 0), number_format($last_week, 0), '', '', '', '', '', '', '', 'meta' => FannieReportPage::META_COLOR, 'meta_background' => $colors[0], 'meta_foreground' => 'black');
$data[] = array('Ownership This Year', number_format($current, 0), number_format($prior, 0), '', '', '', '', '', '', '', 'meta' => FannieReportPage::META_COLOR, 'meta_background' => $colors[0], 'meta_foreground' => 'black');
return $data;
}
