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; }