public function classroom_chose_subject(Request $request)
{
$chosen_subjects = StudentSubject::whereIn('student_subjects.state', ['study'])->selectRaw('student_subjects.student_id,students.name,students.username, students.name,students.mobile ,student_subjects.subject_id')->join('students', 'student_subjects.student_id', '=', 'students.id')->join('classrooms', function ($j) {
$j->on('classrooms.subject_subject_id', '=', 'student_subjects.subject_id')->where('classrooms.semester_id', '=', semester()->id);
})->leftJoin('classroom_students', function ($j) {
$j->on('classrooms.id', '=', 'classroom_students.classroom_id')->on('classroom_students.student_id', '=', 'student_subjects.student_id');
})->groupBy('student_subjects.subject_id', 'student_subjects.student_id');
if ($request->subject_subject_id) {
$chosen_subjects->where('student_subjects.subject_id', '=', $request->subject_subject_id);
}
if ($request->is_choise) {
$chosen_subjects->havingRaw('COUNT(classroom_students.id)=' . $request->is_choise);
}
$per_page = request('per_page') ? request('per_page') : 30;
$chosen_subjects = $chosen_subjects->paginate($per_page);
$subjects = Subject::pluck('name', 'id')->toArray();
$classroom_select = config('classrooms.classroom_select');
return view('classrooms::reports.classroom_chose_subject', compact('subjects', 'classroom_select', 'chosen_subjects'));
}
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 getGrades()
{
$student = Student::select('students.*')->with('registration', 'registration.nationalitycountry')->joinTermName()->findOrFail($this->student_id);
$registration = $student->registration;
$semesters = get_student_grades($student->id);
// $gpa = GPA($semesters);
$gpa = GPA($semesters);
$valuation = check_final_valuation($gpa);
$subject_ids = Department::where('spec_id', $student->specialty_id)->pluck('subject_ids');
$subject_ids = array_unique(json_decode(str_replace("][", ",", implode("", json_decode($subject_ids, TRUE))), TRUE));
$success_subjects = StudentSubject::whereIn('subject_id', $subject_ids)->where('state', 'success')->groupBy('subject_id')->where('student_id', $student->id)->pluck('subject_id');
$studied_subjects = StudentSubject::whereIn('subject_id', $subject_ids)->whereIn('state', ['success', 'fail'])->where('student_id', $student->id)->pluck('subject_id');
$student_hours = Subject::whereIn("id", $subject_ids)->sum('hour');
$fail_hours = Subject::whereIn("id", $studied_subjects)->sum('hour');
$success_hours = Subject::whereIn("id", $success_subjects)->sum('hour');
/** setup pdf library for arabic content */
$pdf = $this->preparePdf();
// test some inline CSS
$pdf->SetFontSize(22);
// $html = '<table border="0" width="100%"><tbody><tr><td>كشف درجات الطالب : '.$student->name.'</td></tr></tbody></table>';
// $pdf->writeHTMLCell(210,20,0,10,$html, 0, 0, false, true, "C");
if ($student->gender == 'f') {
$name = $student->registration->first_name . ' بنت ' . $student->registration->second_name . ' بن ' . $student->registration->third_name . ' ' . $student->registration->last_name;
} else {
$name = $name = $student->registration->first_name . ' بن ' . $student->registration->second_name . ' بن ' . $student->registration->third_name . ' ' . $student->registration->last_name;
}
$html = '<table cellspacing="0" cellpadding="2" border="1">
<tbody>
<tr>
<td bgcolor="#D9DEE4" align="center">اسم الطالب</td>
<td align="center" colspan="2">' . $name . '</td>
<td bgcolor="#D9DEE4" align="center">الجنسية</td>
<td align="center">' . (!empty($registration->nationalitycountry) ? $registration->nationalitycountry->name : 'غير محدد') . '</td>
<td bgcolor="#D9DEE4" align="center">تاريخ الميلاد</td>
<td align="center">' . ($registration ? $registration->birthday : "") . '</td>
</tr>
<tr>
<td bgcolor="#D9DEE4" align="center">التخصص</td>
<td align="center" colspan="2">' . $student->specialty_name . '</td>
<td bgcolor="#D9DEE4" align="center">الرقم الجامعي</td>
<td align="center">' . $student->code . '</td>
<td bgcolor="#D9DEE4" align="center">الحالة الدراسية</td>
<td align="center">' . config('students.state.' . $student->state) . '</td>
</tr>
<tr>
<td bgcolor="#D9DEE4" align="center">الساعات المعتمدة</td>
<td align="center">' . $student_hours . '</td>
<td bgcolor="#D9DEE4" align="center">الساعات المكتسبة</td>
<td align="center">' . $success_hours . '</td>
<td bgcolor="#D9DEE4" align="center" colspan="2">المعدل التراكمي</td>
<td align="center">' . $gpa . '</td>
</tr>
<tr>
<td bgcolor="#D9DEE4" align="center">الساعات المسجلة</td>
<td align="center">' . $fail_hours . '</td>
<td bgcolor="#D9DEE4" align="center">الساعات المحولة</td>
<td align="center"> ... </td>
<td bgcolor="#D9DEE4" align="center" colspan="2">التقدير العام</td>
<td align="center">' . $valuation . '</td>
</tr>
</tbody>
</table>';
$pdf->SetFontSize(11);
$chunks = $semesters->groupBy('year_name');
$accumulation_points = 0;
$accumulation_hours = 0;
$break = 1;
$j = 1;
// for each year
$all_before_failed_subjects = [];
$before_hours = 0;
$before_points = 0;
foreach ($chunks as $i => $chunk) {
// for each sem
foreach ($chunk as $term_index => $term) {
if ($term->semester_id == Semester()->id) {
break;
}
// donot calculate quran hour if sem <= 7
$hours = $term->subjects->sum(function ($s) use($term, &$all_before_failed_subjects, &$before_hours, &$before_points) {
if ($s->subject_state == 'fail' && ($term->semester_id >= 7 && $s->is_quran || !$s->is_quran)) {
$plus_hours = 0;
$plus_points = 0;
if (isset($all_before_failed_subjects[$s->subject_id])) {
$plus_hours = $all_before_failed_subjects[$s->subject_id]['hours'];
$plus_points = $all_before_failed_subjects[$s->subject_id]['points'];
}
$all_before_failed_subjects[$s->subject_id] = ['hours' => $s->subject_hours + $plus_hours, 'points' => $s->details->points + $plus_points];
} else {
if (in_array($s->subject_id, array_keys($all_before_failed_subjects)) && $s->subject_state == 'success') {
$before_hours += $all_before_failed_subjects[$s->subject_id]['hours'];
$before_points += $all_before_failed_subjects[$s->subject_id]['points'];
unset($all_before_failed_subjects[$s->subject_id]);
}
}
return $term->semester_id >= 7 && $s->is_quran || !$s->is_quran ? $s->subject_hours : 0;
});
$points = number_format($term->subjects->sum(function ($s) use($term) {
return $term->semester_id >= 7 && $s->is_quran || !$s->is_quran ? $s->details->points : 0;
}), 2);
// var_dump($before_hours, $before_points);
$accumulation_hours += $hours - $before_hours;
$accumulation_points += $points - $before_points;
$before_points = 0;
$before_hours = 0;
// first or second sem in page
if ($break % 2 == 1) {
$pdf->writeHTMLCell(180, 20, 15, 50, $html, 0, 0, false, true, "C");
$pdf->writeHTMLCell(180, 20, 15, 85, view('students::documents._semesters', ["term" => $term, 'accumulation_hours' => $accumulation_hours, 'accumulation_points' => $accumulation_points])->render(), 0, 0, false, true, "C");
} else {
$pdf->writeHTMLCell(180, 20, 15, 85 * 2 + 10, view('students::documents._semesters', ["term" => $term, 'accumulation_hours' => $accumulation_hours, 'accumulation_points' => $accumulation_points])->render(), 0, 0, false, true, "C");
}
if ($break % 2 == 0 && $term->semester_id != Semester()->id - 1) {
$pdf->AddPage();
}
$break++;
}
$j++;
}
// exit;
// dd($all_before_failed_subjects);
// print signeture
$pdf->SetFontSize(20);
$html = '<table border="0" width="100%"><tbody><tr><td>القبول و التسجيل</td></tr></tbody></table>';
$pdf->writeHTMLCell(40, 5, 15, 245, $html, 0, 0, false, true, "C");
$html = '<table border="0" width="100%"><tbody><tr><td>المشرف العام على التعليم عن بعد</td></tr>
<tr><td>مستشار معالي وزير الأوقاف والشؤون الدينية</td></tr></tbody></table>';
$pdf->writeHTMLCell(130, 5, 80, 245, $html, 0, 0, false, true, "C");
ob_clean();
return $pdf;
}
