/** * Update a new task: load date and forward to view * * @param Request $request, Task $task * @return view */ public function stats(Request $request) { $countercategories = Countercategory::All(['id', 'name']); $months = DB::table('counters')->select(DB::raw('distinct CONCAT(YEAR(date), "-", MONTH(date)) AS date'))->orderby('date')->get(); $counters = DB::table('counters')->leftjoin('countercategories', 'counters.counter_category_id', '=', 'countercategories.id')->select('counters.id as cid', 'countercategories.name as cname', 'countercategories.id as ccid', DB::raw('count(counters.id) as items'), DB::raw('CONCAT(YEAR(date), "-", MONTH(date)) AS condate'))->where('countercategories.inactive', '!=', 1)->groupBy('counters.counter_category_id')->orderBy('items', 'DESC')->get(); //select //CONCAT( YEAR( date ) , '-', MONTH( date ) ) AS thedate //, counter_category_id, count(id) from counters group by thedate, counter_category_id //select //distinct CONCAT( YEAR( date ) , '-', MONTH( date ) ) AS thedate from counters //month: 2016-01, cat1: 10, cat2: 20 //month: 2016-02, cat1: 10, cat2: 20 /*print "<pre>"; $m2 = array(); foreach ($months as $month) { $m['date'] = $month->date; foreach ($countercategories as $cat) { $items = 0; foreach ($counters as $c) { if ($c->condate == $month->date AND $c->ccid == $cat->id) { $items = $c->items; break; } } $m[$cat->id] = $items; } array_push($m2, $m); } foreach ($m2 as $m0) { print_r($m0); }*/ return view('counters.stats', ['countercategories' => $countercategories, 'cats' => $counters]); }