public function rawSum($filter)
{
//dd(__METHOD__."(".__LINE__.")",compact('filter'));
/*
* Note: Group by should really be groupBy(['year(dateStamp)', 'month(dateStamp)', 'day(dateStamp)', 'hour(dateStamp)'])
* but eloquent places backticks (`) around the field names, and MYSQL does not like
* group by `year(dateStamp)`, `month(dateStamp)`, `day(dateStamp)`, `hour(dateStamp)`;
* as it thinks that `year(dateStamp)` refers to a field name, not a date function on field dateStamp.
*
* Therefor we are trying to get away with group by dateStamp. Hoping that the populating functions do not add minutes, seconds into the dateStamp.
*/
if (isset($filter['groupBy']) and $filter['groupBy'] == 'dateStamp') {
$query = PerformanceTally::groupBy(DB::raw('year(dateStamp), month(dateStamp), day(dateStamp), hour(dateStamp)'))->orderBy('dateStamp', 'asc')->selectRaw('date_format(dateStamp,"%Y-%m-%d %H:%i") as dateStamp, sum(receivedUnits) as receivedUnits, sum(putAwayRec) as putAwayRec, sum(putAwayRplComb) as putAwayRplComb, sum(putAwayRplSngl) as putAwayRplSngl, sum(putAwayReserve) as putAwayReserve, sum(replenTotes) as replenTotes');
} elseif (isset($filter['groupBy']) and $filter['groupBy'] == 'userName') {
$query = PerformanceTally::groupBy('userName')->orderBy('userName', 'asc')->selectRaw('userName, sum(receivedUnits) as receivedUnits, sum(putAwayRec) as putAwayRec, sum(putAwayRplComb) as putAwayRplComb, sum(putAwayRplSngl) as putAwayRplSngl, sum(putAwayReserve) as putAwayReserve, sum(replenTotes) as replenTotes');
} else {
$query = PerformanceTally::selectRaw('sum(receivedUnits) as receivedUnits, sum(putAwayRec) as putAwayRec, sum(putAwayRplComb) as putAwayRplComb, sum(putAwayRplSngl) as putAwayRplSngl, sum(putAwayReserve) as putAwayReserve, sum(replenTotes) as replenTotes');
}
if (isset($filter['fromDate']) && strlen($filter['fromDate']) > 4) {
$query = $query->where('dateStamp', '>=', $filter['fromDate']);
}
if (isset($filter['toDate']) && strlen($filter['toDate']) > 4) {
$query = $query->where('dateStamp', '<=', $filter['toDate']);
}
if (isset($filter['userName']) && strlen($filter['userName']) > 2) {
$query = $query->where('userName', $filter['userName']);
}
return $query;
}
