/**
* 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]);
}
