/**
* Show the application dashboard.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$_schedule_masters = Master::orderBy('id', 'desc')->lists('name', 'id');
$master_id = array_keys(head($_schedule_masters))[0];
$data_pie = DB::select('SELECT da.code, count(sc.id) total_class
FROM (
SELECT isc.*
FROM schedules isc
GROUP BY isc.section_key
) sc
INNER JOIN semesters se ON sc.semester_id = se.id
INNER JOIN plans pl ON se.plan_id = pl.id
INNER JOIN curriculums cu ON pl.curriculum_id = cu.id
INNER JOIN courses co ON pl.course_id = co.id
INNER JOIN departments da ON co.department_id = da.id
WHERE sc.master_id = ' . $master_id . '
GROUP BY da.id');
$data_query = DB::select('SELECT count(sc.id) total
FROM (
SELECT isc.*
FROM schedules isc
GROUP BY isc.room_id
) sc
WHERE sc.master_id = ' . $master_id);
$data_room = [];
if ($data_query) {
$data_room['used'] = $data_query[0]->total;
$data_room['total'] = \App\Room::count();
}
$data_query = DB::select('SELECT count(sc.id) total
FROM (
SELECT isc.*
FROM schedules isc
WHERE not faculty_id is null
GROUP BY isc.faculty_id
) sc
WHERE sc.master_id = ' . $master_id);
$data_faculty = [];
if ($data_query) {
$data_faculty['used'] = $data_query[0]->total;
$data_faculty['total'] = \App\Faculty::count();
}
$data_query = DB::select('SELECT count(sc.id) total
FROM (
SELECT isc.*
FROM schedules isc
INNER JOIN semesters se ON isc.semester_id = se.id
INNER JOIN plans pl ON se.plan_id = pl.id
INNER JOIN curriculums cu ON pl.curriculum_id = cu.id
INNER JOIN courses co ON pl.course_id = co.id
GROUP BY co.id
) sc
WHERE sc.master_id = ' . $master_id);
$data_course = [];
if ($data_query) {
$data_course['used'] = $data_query[0]->total;
$data_course['total'] = \App\Course::count();
}
$data_query = DB::select('SELECT count(sc.id) total
FROM (
SELECT isc.*
FROM schedules isc
WHERE faculty_id is null
OR room_id is null
GROUP BY isc.key
) sc
WHERE sc.master_id = ' . $master_id);
$data_unallocated = [];
if ($data_query) {
$data_unallocated['used'] = $data_query[0]->total;
$data_query = DB::select('SELECT count(sc.id) total
FROM (
SELECT isc.*
FROM schedules isc
GROUP BY isc.key
) sc
WHERE sc.master_id = ' . $master_id);
$data_unallocated['total'] = $data_query[0]->total;
}
return view('dashboard.index', compact('data_pie', 'data_room', 'data_faculty', 'data_course', 'data_unallocated'));
}