/**
* Display a listing of the resource.
* @return Response
*/
public function index(Request $request)
{
$excuses = ExamExcuse::where('semester_id', semester()->id)->with('student', 'subject', 'exam');
if (request('subject_id')) {
$subject_id = request('subject_id');
$excuses->whereHas('exam.subject', function ($q) use($subject_id) {
$q->where('subject_subjects.id', $subject_id);
});
}
if (request('exam_year_term')) {
$excuses->whereIn('subject_id', explode(',', request('exam_year_term')));
}
$per_page = request('per_page') ? request('per_page') : 50;
$excuses = $excuses->paginate($per_page);
$excuses->appends($request->except("page"));
$subjects = Subject::lists('name', 'id')->toArray();
$year_term = Year::join('academystructure_terms', 'academystructure_years.id', '=', 'academystructure_terms.year_id')->join('academystructure_departments', 'academystructure_terms.id', '=', 'academystructure_departments.term_id')->select(\DB::raw('CONCAT(academystructure_years.name, "-", academystructure_terms.name) as name,
GROUP_CONCAT(DISTINCT(academystructure_departments.subject_ids)) as sid'))->groupBy('academystructure_terms.name', 'academystructure_years.name')->get()->toArray();
$year_term_options;
foreach ($year_term as $key => &$aa) {
$a = implode(',', array_unique(array_merge(json_decode(str_replace('],[', ',', $aa['sid']), TRUE))));
$year_term_options[$a] = $aa['name'];
}
return view('exams::excuses.index', compact('excuses', 'subjects', 'year_term_options'));
}
public function index(Request $request)
{
$per_page = request('per_page', 20);
$students = $this->prepQuery($request->all());
$students = $students->paginate($per_page);
$students->appends($request->except('page'));
$year_term = Year::join('academystructure_terms', 'academystructure_years.id', '=', 'academystructure_terms.year_id')->select(\DB::raw('CONCAT(academystructure_years.name, "-", academystructure_terms.name) as name,
academystructure_terms.id as tid'))->groupBy('academystructure_terms.name', 'academystructure_years.name')->orderBy('academystructure_terms.id')->pluck('name', 'tid')->toArray();
$spec_id = Specialty::pluck('name', 'id')->toArray();
$county = Country::pluck('name', 'id')->toArray();
$city = City::pluck('name', 'id')->toArray();
// return $students->pluck('sps');
return view('students::students.index', compact('students', 'year_term', 'spec_id', 'county', 'city'));
}
public function index(Request $request)
{
$exams = Exam::selectRaw('exams.*,COUNT(DISTINCT er.id) AS resultsCount,COUNT(DISTINCT eques.id) AS questionsCount')->leftJoin('exam_results AS er', function ($join) {
$join->on('er.exam_id', '=', 'exams.id');
})->leftJoin('exam_questions AS eques', function ($join) {
$join->on('eques.exam_id', '=', 'exams.id');
})->groupBy('exams.id')->orderBy('exams.id', 'desc');
if (request('exam_subject')) {
$exams->where('subject_id', request('exam_subject'));
}
if (request('exam_type')) {
$exams->where('type', request('exam_type'));
}
if ($request->has('start_at')) {
$exams->whereDate('start_at', '=', $request->input('start_at'));
}
if (request('exam_semester')) {
$exams->where('exams.semester_id', request('exam_semester'));
} else {
//$exams->inCurrentSemester();
}
if (request('exam_year_term')) {
$exams->whereIn('subject_id', explode(',', request('exam_year_term')));
}
$per_page = request('per_page') ? request('per_page') : 30;
$exams = $exams->paginate($per_page);
$exams->appends($request->except("page"));
$subjects = Subject::lists('name', 'id')->toArray();
$years = Year::lists('name', 'id')->toArray();
$semesters = Semester::pluck('name', 'id')->toArray();
$year_term = Year::join('academystructure_terms', 'academystructure_years.id', '=', 'academystructure_terms.year_id')->join('academystructure_departments', 'academystructure_terms.id', '=', 'academystructure_departments.term_id')->select(\DB::raw('CONCAT(academystructure_years.name, "-", academystructure_terms.name) as name,
GROUP_CONCAT(DISTINCT(academystructure_departments.subject_ids)) as sid'))->groupBy('academystructure_terms.name', 'academystructure_years.name')->get()->toArray();
$year_term_options;
foreach ($year_term as $key => &$aa) {
$a = implode(',', array_unique(array_merge(json_decode(str_replace('],[', ',', $aa['sid']), TRUE))));
$year_term_options[$a] = $aa['name'];
}
return view('exams::exams.index', compact('exams', 'subjects', 'years', 'semesters', 'year_term_options'));
}
public function studentexam(Request $request)
{
$subjects = Subject::lists('name', 'id')->toArray();
$semesters = Semester::select(\DB::raw('CONCAT(academycycle_semesters.name , "-", academycycle_years.name) as name , academycycle_semesters.id as id'))->join('academycycle_years', 'academycycle_years.id', '=', 'academycycle_semesters.academycycle_year_id')->pluck('name', 'id')->toArray();
$year_term = Year::join('academystructure_terms', 'academystructure_years.id', '=', 'academystructure_terms.year_id')->join('academystructure_departments', 'academystructure_terms.id', '=', 'academystructure_departments.term_id')->select(\DB::raw('CONCAT(academystructure_years.name, "-", academystructure_terms.name) as name,
GROUP_CONCAT(DISTINCT(academystructure_departments.subject_ids)) as sid'))->groupBy('academystructure_terms.name', 'academystructure_years.name')->get()->toArray();
$year_term_options;
foreach ($year_term as $key => &$aa) {
$a = implode(',', array_unique(array_merge(json_decode(str_replace('],[', ',', $aa['sid']), TRUE))));
$year_term_options[$a] = $aa['name'];
}
$types = config('exams.types');
if (request('student_id')) {
$semester_id = semester()->id;
$student_id = request('student_id');
$exam_type = request('exam_type');
$camera_work = request('camera_work');
$results = DB::select("SELECT student_subjects.subject_id,\n exams.id as exam_id,\n exam_results.id as exam_result_id,\n s.id,\n s.name,\n s.username,\n s.mobile,\n exam_results.id as attendees,\n exam_results.enter_at,\n exam_results.exit_at,\n exam_results.created_at,\n exam_results.filename,\n student_grades.value,subject_subjects.name as subject_name,\n exam_recordings.id as record,\n (select COUNT(*) from exam_result_answers where exam_result_answers.exam_result_id = exam_results.id and exam_result_answers.answer != '') as count_q\n\n from students as s\n inner join student_subjects on student_subjects.student_id = s.id\n and student_subjects.state = 'study' and student_subjects.semester_id = " . $semester_id . "\n left join subject_subjects on subject_subjects.id = student_subjects.subject_id\n inner join exams on student_subjects.subject_id = exams.subject_id and exams.semester_id = " . $semester_id . "\n and (exams.type in ('midterm', 'remidterm') or exams.id IN (\n SELECT ce.exam_id FROM classrooms_exam as ce\n JOIN classrooms as c ON c.id = ce.classroom_id\n JOIN classroom_students as cs ON cs.classroom_id = c.id\n WHERE exam_id = exams.id AND cs.student_id = s.id GROUP BY ce.exam_id)\n )\n left join exam_results on exams.id = exam_results.exam_id and s.id = exam_results.student_id\n left join `exam_recordings` on `exam_recordings`.`student_id` = exam_results.`student_id` and `exam_recordings`.`exam_id` = exam_results.`exam_id` \n \n left join student_grades on s.id = student_grades.student_id and student_grades.ref_key = 'exam' and exams.id = student_grades.ref_value\n where s.id = " . $student_id . " and finish_at < '" . date("Y/m/d H:i:s") . "' and exams.type = '" . $exam_type . "' group by exam_id ORDER BY s.id ASC");
$exams = collect($results);
return view('exams::reports.studentexamdetails', compact('subjects', 'semesters', 'year_term_options', 'types', 'exams'));
} else {
if (request('exam_attendee') == 1) {
$exams = Exam::selectRaw('exam_recordings.student_id, s.id , exams.id as exam_id,
er.id as erid,
s.name,
s.username,
er.id as attendees,
er.enter_at,
er.created_at ,
er.exit_at,
er.filename,
sg.value,exam_recordings.id as record,
(select COUNT(*) from exam_result_answers where exam_result_answers.exam_result_id = er.id and exam_result_answers.answer != "") as count_q')->leftJoin('exam_results AS er', function ($join) {
$join->on('er.exam_id', '=', 'exams.id');
})->leftJoin('students AS s', function ($join) {
$join->on('s.id', '=', 'student_id');
})->leftJoin('student_grades AS sg', function ($join) {
$join->on('sg.student_id', '=', 's.id');
$join->on('exam_id', '=', 'sg.ref_value');
})->leftJoin('exam_recordings', function ($join) {
$join->on('exam_recordings.student_id', '=', 'er.student_id');
$join->on('exam_recordings.exam_id', '=', 'er.exam_id');
})->where('finish_at', '<', date("Y/m/d H:i:s"))->where('exams.subject_id', request('exam_subject'))->where('exams.semester_id', request('exam_semester'))->where('exams.type', request('exam_type'));
if (request('camera_work') == 'not_work') {
$exams = $exams->where('exam_recordings.id', '=', null);
} else {
$exams = $exams->where('exam_recordings.id', '!=', null);
}
$exams = $exams->groupBy('s.id')->orderBy('exams.id', 'desc')->get();
} else {
$exams = StudentSubject::whereIn('student_subjects.state', ['study'])->selectRaw('s.id,
s.name,
s.username')->leftJoin('students AS s', function ($join) {
$join->on('student_subjects.student_id', '=', 's.id');
})->where('student_subjects.semester_id', request('exam_semester'))->where('subject_id', request('exam_subject'))->whereNotIn('s.id', function ($query) {
$query->select('s.id')->from('exams')->leftJoin('exam_results AS er', function ($join) {
$join->on('er.exam_id', '=', 'exams.id');
})->leftJoin('students AS s', function ($join) {
$join->on('s.id', '=', 'student_id');
})->where('finish_at', '<', date("Y/m/d H:i:s"))->where('exams.subject_id', request('exam_subject'))->where('exams.semester_id', request('exam_semester'))->where('exams.type', request('exam_type'))->orderBy('exams.id', 'desc')->get();
})->get();
}
return view('exams::reports.studentexam', compact('subjects', 'semesters', 'year_term_options', 'types', 'exams'));
}
}
public function report(Request $request)
{
# code...
$per_page = request('per_page', 20);
$students = $this->prepQuery($request->all());
$students = $students->paginate($per_page);
$students->appends($request->except('page'));
$year_term = Year::join('academystructure_terms', 'academystructure_years.id', '=', 'academystructure_terms.year_id')->select(\DB::raw('CONCAT(academystructure_years.name, "-", academystructure_terms.name) as name,
academystructure_terms.id as tid'))->groupBy('academystructure_terms.name', 'academystructure_years.name')->orderBy('academystructure_terms.id')->pluck('name', 'tid')->toArray();
$spec_id = Specialty::pluck('name', 'id')->toArray();
$exam_center_id = ExamCenter::pluck('name', 'id')->toArray();
// return $students->pluck('sps');
return view('exams::centers.report', compact('students', 'year_term', 'spec_id', 'exam_center_id'));
}
