Php code to generate data

mail@pastecode.io avatar
unknown
php
2 years ago
1.2 kB
2
Indexable
Never
Route::get('/test', function () {
  $data = \App\Student::query()
    ->selectRaw("DATE_FORMAT(startdate, '%Y-%m') AS course_date,
	                         YEAR(birthday) AS birthday_year,
	                         count(*) AS total")
    ->join('courses', 'course_id', 'courses.id')

    ->whereNull('courses.deleted_at')
    ->whereNull('students.deleted_at')
    ->where('courses.status', '<>', 5)
    ->where('coursetype_id', 1)
    ->where('provider_id', 2)

    ->groupBy('course_date', 'birthday_year')
    ->orderBy('course_date')
    ->orderBy('birthday_year')

    ->get();

  $months = $data->pluck('course_date')->sort()->unique()->values()->toArray();
  $years = $data->pluck('birthday_year')->unique()->values()->toArray();


  $items = [];

  foreach ($years as $key => $year) {
    $filtered = $data->where('birthday_year', $year)->values();
    $items[$key]['year'] = $year;

    foreach ($months as $month) {
      $items[$key]['months'][] = $filtered->where('course_date', $month)->sum('total');
    }
  }

  $items = collect($items)->sortBy('year')->values();

  return $items;

  return view('table', compact('items', 'months'));
});