public function index()
{
$semester = semester();
// $finish = $semester ? $semester->finish_at : date('Y-m-d');
// if ($semester->order != 'first') {
// $semester= Semester::where('order', 'first')
// ->where('start_at', '>', $finish)
// ->orderBy('start_at', 'ASC')
// ->first();
// }
// return $semester;
$students_by_years = Year::selectRaw("\n academystructure_years.name,\n SUM(IF(stu.gender='m',1,0)) AS males,\n SUM(IF(stu.gender='f',1,0)) AS females")->leftJoin('academystructure_terms AS at', 'at.year_id', '=', 'academystructure_years.id')->leftJoin('academystructure_departments AS ad', 'ad.term_id', '=', 'at.id')->leftJoin('student_histories AS sh', function ($join) {
$join->on('sh.academystructure_department_id', '=', 'ad.id')->where('academycycle_semester_id', '=', semester()->id);
})->leftJoin('students AS stu', 'stu.id', '=', 'sh.student_id')->groupBy('academystructure_years.id')->get();
$students_by_state = Student::selectRaw("DISTINCT students.id,\n students.state,\n \tSUM(IF(students.state='active',1,0)) AS active_all_students,\n \tSUM(IF(students.state='delayed',1,0)) AS delayed_all_students,\n \tSUM(IF(students.state='discontinuous',1,0)) AS discontinuous_all_students,\n \tSUM(IF(students.state='withdrawn',1,0)) AS withdrawn_all_students,\n \tSUM(IF(students.state='fired',1,0)) AS fired_all_students,\n \tSUM(IF(students.state='graduate',1,0)) AS graduate_all_students,\n\n \tSUM(IF(students.gender='m' AND students.state='active',1,0)) AS active_male_students,\n \tSUM(IF(students.gender='m' AND students.state='delayed',1,0)) AS delayed_male_students,\n \tSUM(IF(students.gender='m' AND students.state='discontinuous',1,0)) AS discontinuous_male_students,\n \tSUM(IF(students.gender='m' AND students.state='withdrawn',1,0)) AS withdrawn_male_students,\n \tSUM(IF(students.gender='m' AND students.state='fired',1,0)) AS fired_male_students,\n \tSUM(IF(students.gender='m' AND students.state='graduate',1,0)) AS graduate_male_students,\n\n \tSUM(IF(students.gender='f' AND students.state='active',1,0)) AS active_female_students,\n \tSUM(IF(students.gender='f' AND students.state='delayed',1,0)) AS delayed_female_students,\n \tSUM(IF(students.gender='f' AND students.state='discontinuous',1,0)) AS discontinuous_female_students,\n \tSUM(IF(students.gender='f' AND students.state='withdrawn',1,0)) AS withdrawn_female_students,\n \tSUM(IF(students.gender='f' AND students.state='fired',1,0)) AS fired_female_students,\n \tSUM(IF(students.gender='f' AND students.state='graduate',1,0)) AS graduate_female_students\n \t")->first();
// return $students_by_state;
$genders = Student::selectRaw("\n SUM(IF(students.gender='m',1,0)) AS males,\n SUM(IF(students.gender='f',1,0)) AS females")->join('student_histories AS sh', function ($join) {
$join->on('sh.student_id', '=', 'students.id')->whereIn('sh.study_state', ['active', 'success', 'fail'])->where('academycycle_semester_id', '=', semester()->id);
})->isStudying()->first();
$sessions = ClassroomSession::selectRaw("DATE(start_at) as start_at,SUM(IF(canceled=0,1,0)) AS held, SUM(IF(canceled=1,1,0)) AS canceled")->whereDate('start_at', '>=', date('Y-m-d', strtotime('-30 days')))->groupBy(\DB::raw('DATE(start_at)'))->get();
$session_per_days = [];
for ($i = 30; $i > 0; $i--) {
if ($session = $sessions->whereLoose('start_at', date('Y-m-d 00:00:00', strtotime("-" . $i . " days")))->first()) {
$session_per_days[] = $session;
} else {
$session_per_days[] = ['held' => 0, 'canceled' => 0, 'start_at' => date('Y-m-d 00:00:00', strtotime("-" . $i . " days"))];
}
}
return view('dashboards.admin', compact('semester', 'genders', 'students_by_years', 'students_by_state', 'session_per_days'));
}
