public function index()
{
$invoices = FinancialInvoice::select('fi.amount', 'fi.semester_id', 'sem.name', 'fi.id', 'fi.created_at')->where('fi.type', 'credit')->leftJoin('academycycle_semesters as sem', 'sem.id', '=', 'fi.semester_id')->where('fi.student_id', $this->student->id)->groupBy('fi.id')->from('financial_invoices as fi')->get();
foreach ($invoices as $invoice) {
$invoice->subjects = StudentSubject::join('subject_subjects as sub', function ($query) {
$query->on('sub.id', '=', 'student_subjects.subject_id');
})->select('sub.name', 'sub.hour', 'sub.is_quran')->where('student_id', $this->student->id)->where('semester_id', $invoice->semester_id)->where('student_subjects.state', '!=', 'equal')->groupBy('sub.id')->get();
}
return $invoices;
}
/**
* Execute the job.
*
* @return void
*/
public function handle()
{
$student = Student::find($this->student_id);
$currentSemester = semester();
if ($student) {
$history = StudentHistory::where('academycycle_semester_id', $currentSemester->id)->where('student_id', $student->id)->first();
if ($history) {
$history->update(['state' => 'fired', 'study_state' => 'stop']);
}
StudentSubject::where('semester_id', $currentSemester->id)->where('student_id', $student->id)->update(['state' => 'fail']);
$student->update(['state' => 'fired', 'study_state' => 'stop']);
}
}
/**
* Handle the event.
*
* @param UserAuthenticated $event
* @return void
*/
public function handle(DelayOrderAccepted $event)
{
// dd('STOP HERE');
$delay_order = $event->order;
//drop subject
/*
$subject_ids = Subject::student($delay_order->student_id)
->Where('semester_id' , semester()->id)
->Where('state' , 'study')
->pluck('id')->toArray();
StudentSubject::whereIn('subject_id', $subject_ids)->update(array("state" => "drop"));
*/
StudentSubject::where('student_id', $delay_order->student_id)->Where('semester_id', $delay_order->semester_id)->whereIn('state', ['study', 'fail'])->update(['state' => 'drop', 'study_state' => 'stop']);
}
/**
* Execute the job.
*
* @return void
*/
public function handle()
{
$student = Student::find($this->student_id);
if ($student) {
$failed_subjects = [];
$success_subjects = [];
$semester = semester();
$subjects = StudentSubject::selectRaw("subsub.id as subject_id,SUM(stugr.value) AS subject_points,student_subjects.state as subject_state,subsub.name AS subject_name, subsub.hour AS subject_hours")->join('subject_subjects AS subsub', function ($join) {
$join->on('subsub.id', '=', 'student_subjects.subject_id');
})->where('student_subjects.student_id', $student->id)->leftJoin('student_grades AS stugr', function ($join) use($student, $semester) {
$join->on('stugr.subject_id', '=', 'subsub.id')->where('stugr.semester_id', '=', $semester->id)->where('stugr.student_id', '=', $student->id);
})->whereIn('state', ['study', 'success', 'fail'])->where('student_subjects.semester_id', '=', $semester->id)->where('student_subjects.student_id', $student->id)->groupBy('subsub.id')->get();
$subjects->each(function ($subject) use(&$failed_subjects, $success_subjects) {
$subject->details = grade_details($subject->subject_points, $subject->subject_hours);
if ((double) $subject->subject_points < 50) {
$failed_subjects[] = $subject->subject_id;
} else {
$success_subjects[] = $subject->subject_id;
}
});
$semester->load('year');
$currentYear = $semester->year;
$nextYear = AcademycycleYear::where('start_at', '>', $currentYear->start_at)->first();
$repeatSemester = Semester::where('order', $semester->order)->where('academycycle_year_id', $nextYear->id)->first();
$currentHistory = StudentHistory::where('student_id', $student->id)->where('academycycle_semester_id', $semester->id)->first();
$repeatSubjects = [];
foreach ($failed_subjects as $subject_id) {
$repeatSubjects[$subject_id] = ['semester_id' => $repeatSemester->id, 'academystructure_department_id' => $currentHistory->academystructure_department_id, 'state' => 'study'];
}
$student->subjects()->attach($repeatSubjects);
StudentSubject::where('semester_id', $semester->id)->where('student_id', $student->id)->whereIn('subject_id', $success_subjects)->update(['state' => 'success']);
StudentSubject::where('semester_id', $semester->id)->where('student_id', $student->id)->whereIn('subject_id', $failed_subjects)->update(['state' => 'fail']);
$currentHistory->study_state = 'repeat';
$currentHistory->save();
$repeatHistory = $currentHistory->replicate();
$repeatHistory->study_state = 'active';
$repeatHistory->academycycle_semester_id = $repeatSemester->id;
$repeatHistory->save();
$student->study_state = 'repeat';
$student->save();
}
}
/**
* Handle the event.
*
* @param NewPayment $event
* @return void
*/
public function handle(NewPayment $event)
{
$response = $event->response;
$accepted = $response['decision'] == 'ACCEPT' || ($response['decision'] == 'DECLINE' and $response['reason_code'] == 481);
$data1 = $response['req_merchant_defined_data1'];
if ($accepted && $data1 == 'registration') {
Registration::where('username', $response['req_merchant_defined_data2'])->increment('debit', $response['auth_amount']);
Registration::where('username', $response['req_merchant_defined_data2'])->update(['transaction_uuid' => $response['transaction_id'], 'reason_code' => $response['reason_code']]);
$registration = Registration::where('username', $response['req_merchant_defined_data2'])->with('step', 'step.children')->first();
if ($step = $registration->step and !$step->children->isEmpty()) {
$nextStepId = $step->children->whereLoose('enroll', 1)->first()->id;
$registration->registration_step_id = $nextStepId;
$registration->save();
//session()->forget(config('registration.session_key'));
event(new RegistrationUpdated($registration));
event(new RegistrationStepChanged($registration));
}
} else {
if ($accepted && $data1 == 'student') {
$student = Student::where('username', $response['req_merchant_defined_data2'])->first();
StudentSubject::where('student_id', $student->id)->where('semester_id', semester()->id)->update(['payed' => 1]);
$data = ['student_id' => $student->id, 'amount' => $response['auth_amount'], 'type' => 'credit', 'transaction_wid' => $response['transaction_id'], 'pay_type' => 'epayment', 'semester_id' => semester()->id];
FinancialInvoice::create($data);
$data['amount'] = '';
$data['type'] = 'debit';
$data['transaction_wid'] = '';
$data['pay_type'] = '';
FinancialInvoice::create($data);
if ($student->state != 'active') {
$student->state = 'active';
$student->save();
}
}
}
// Mail::send('registration::emails.new_payment' ,compact('response'), function ($message) {
// $message->to('*****@*****.**')
// ->subject('new payment '.date('Y-m-d H:i:s'));
// });
}
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 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 approve($order_id)
{
$order = OrderChangeDepartment::findOrFail($order_id);
$student_id = $order->student_id;
$depid = $order->to;
$current_dep = $order->from;
$StudentHistory = StudentHistory::where('student_id', $student_id)->where('academycycle_semester_id', semester()->id)->update(['academystructure_department_id' => $depid]);
$student = Student::with('subjects')->where('id', $student_id)->first();
$student->academystructure_department_id = $depid;
$student->save();
$department = Department::findOrFail($current_dep);
$current_subject_ids = json_decode($department->subject_ids, TRUE);
$current_payed_subject_ids = StudentSubject::select('subject_id')->where('semester_id', semester()->id)->where('student_id', $student_id)->where('state', 'study')->where('payed', 1)->get();
$cuttent_invoice_amount = 0;
if ($current_payed_subject_ids->count() > 0) {
$cuttent_invoice_amount = Subject::select(DB::raw('sum(hour*amount) as total_amount'))->whereIn('id', $current_payed_subject_ids)->first();
}
$new_department = Department::findOrFail($depid);
$new_subject_ids = json_decode($new_department->subject_ids, TRUE);
//if($current_dep == 8 or $current_dep == 13 or $current_dep =14 ){
//update department subjects
StudentSubject::where('semester_id', semester()->id)->where('student_id', $student_id)->where('state', 'study')->whereIn('subject_id', $current_subject_ids)->update(['state' => 'drop']);
foreach ($new_subject_ids as $new_subject_id) {
$new_student_subject = new StudentSubject();
$new_student_subject->subject_id = $new_subject_id;
$new_student_subject->student_id = $student_id;
$new_student_subject->semester_id = semester()->id;
$new_student_subject->academystructure_department_id = $depid;
$new_student_subject->state = 'study';
$new_student_subject->save();
}
if ($current_payed_subject_ids->count() > 0) {
$new_payed_subject_ids = StudentSubject::select('subject_id')->where('semester_id', semester()->id)->where('student_id', $student_id)->where('state', 'study')->get();
$new_invoice_amount = Subject::select(DB::raw('sum(hour*amount) as total_amount'))->whereIn('id', $new_payed_subject_ids)->first();
if ($new_invoice_amount < $cuttent_invoice_amount) {
StudentSubject::where('semester_id', semester()->id)->where('student_id', $student_id)->where('state', 'study')->update(['payed' => 1]);
$invoiceData = ['ref_key' => 'Order_Change_AC_Departments', 'ref_value' => $order_id, 'student_id' => $student_id, 'amount' => $cuttent_invoice_amount->total_amount - $new_invoice_amount->total_amount, 'type' => 'credit', 'semester_id' => semester()->id, 'note' => 'فرق تغيير التخصص'];
FinancialInvoice::create($invoiceData);
}
if ($new_invoice_amount > $cuttent_invoice_amount) {
StudentSubject::where('semester_id', semester()->id)->where('student_id', $student_id)->where('state', 'study')->update(['payed' => 1]);
}
if ($new_invoice_amount > $cuttent_invoice_amount) {
StudentSubject::where('semester_id', semester()->id)->where('student_id', $student_id)->where('state', 'study')->where('subject_id', '!=', 35)->update(['payed' => 1]);
}
}
//updtae remain subjects (not in dep id) to be in same depid
StudentSubject::where('semester_id', semester()->id)->where('student_id', $student_id)->where('state', 'study')->update(['academystructure_department_id' => $depid]);
$order->state = 1;
$order->save();
$msg = 'تم تغيير التخصص ';
return redirect()->route('orders.change.department.index')->with('success', $msg);
// }else{
// }
}
public function index(Request $request)
{
$years = Year::pluck('name', 'id')->toArray();
$semester = semester();
$current_semester_id = $semester->id;
$statuses = ["r" => trans('students::success.repeaters'), 's' => trans('students::success.successful')];
$students = Student::selectRaw('sh2.study_state as shstudy,students.*, GROUP_CONCAT(at.id) AS terms,
GROUP_CONCAT(sh.academycycle_semester_id) AS semesters')->join('student_histories AS sh', function ($join) use($current_semester_id) {
$join->on('sh.student_id', '=', 'students.id')->where('sh.academycycle_semester_id', '<=', $current_semester_id);
})->leftJoin('student_histories AS sh2', function ($join) use($current_semester_id) {
$join->on('sh2.student_id', '=', 'students.id')->where('sh2.academycycle_semester_id', '=', $current_semester_id);
})->join('academystructure_departments AS ad', 'ad.id', '=', 'sh.academystructure_department_id')->join('academystructure_terms AS at', 'at.id', '=', 'ad.term_id')->join('academystructure_years AS ay', 'ay.id', '=', 'at.year_id')->where('students.state', 'active')->whereNotIn('sh2.study_state', ['success', 'fail', 'stop', 'repeat'])->isStudying();
if ($request->has('year')) {
//$students->where('ay.id', $request->input('year'));
}
$students = $students->groupBy('students.id')->paginate(50);
$students->each(function ($student) use($current_semester_id) {
$terms_arr = explode(",", $student->terms);
$sems_arr = explode(",", $student->semesters);
$all_failed_subjects = StudentSubject::groupBy('subject_id')->where(function ($query) {
return $query->orWhere(function ($query) {
return $query->where('state', 'fail')->where('semester_id', '<', semester()->id);
})->orWhere(function ($query) {
return $query->where('state', 'study')->where('semester_id', semester()->id);
});
})->where('student_id', $student->id)->pluck('subject_id')->toArray();
// dd($all_failed_subjects);
$all_passed_subjects = StudentSubject::groupBy('subject_id')->where('state', 'success')->where('student_id', $student->id)->pluck('subject_id')->toArray();
$accumulation_hours = 0;
$term_average = 0;
$accumulation_points = 0;
$terms = collect();
foreach ($terms_arr as $term_id) {
$terms->push(Term::find($term_id));
}
$failed_subjects = [];
$passed_subjects = [];
// beging foreach terms
foreach ($terms as $key => $term) {
$semester_id = $sems_arr[$key];
$term->subjects = StudentSubject::selectRaw("subsub.id as subject_id,SUM(stugr.value) AS subject_points,\n\t\t\t\tstudent_subjects.state as subject_state,subsub.name AS subject_name, subsub.hour AS subject_hours")->join('subject_subjects AS subsub', function ($join) {
$join->on('subsub.id', '=', 'student_subjects.subject_id');
})->where('student_subjects.student_id', $student->id)->leftJoin('student_grades AS stugr', function ($join) use($semester_id, $student) {
$join->on('stugr.subject_id', '=', 'subsub.id')->where('stugr.semester_id', '=', $semester_id)->where('stugr.student_id', '=', $student->id);
})->whereIn('state', ['study', 'success', 'fail'])->where('student_subjects.semester_id', '=', $semester_id)->where('student_subjects.student_id', $student->id)->groupBy('subject_id')->get();
$term->subjects->each(function ($subject) use(&$passed_subjects, &$failed_subjects, $student, $current_semester_id, $semester_id, &$all_failed_subjects, &$all_passed_subjects) {
$subject->details = grade_details($subject->subject_points, $subject->subject_hours);
if ($semester_id == $current_semester_id) {
if ((double) $subject->subject_points < 50) {
$all_failed_subjects[] = $subject->subject_id;
$failed_subjects[] = $subject->subject_id;
} else {
$all_passed_subjects[] = $subject->subject_id;
$passed_subjects[] = $subject->subject_id;
}
}
});
$hours = $term->subjects->sum('subject_hours');
$quran_hours = $term->subjects->sum(function ($s) {
return $s->is_quran ? $s->subject_hours : 0;
});
$points = number_format($term->subjects->sum(function ($subject) {
return $subject->details->points;
}), 2);
$term_average = $hours ? number_format($points / ($hours - $quran_hours), 2) : 0.0;
$student->term_average = $term_average;
$accumulation_points += $points;
$accumulation_hours += $hours - $quran_hours;
if ($key == $terms->count() - 2 && $terms->count() > 1) {
$student->term_previous = $term_average;
$student->accum_previous = $accumulation_hours ? number_format($accumulation_points / $accumulation_hours, 2) : 0;
}
}
// $all_failed_subjects = array_diff($all_failed_subjects, $failed_subjects);
$old_failed_subjects = array_diff(array_unique(array_diff($all_failed_subjects, $failed_subjects)), array_unique($all_passed_subjects));
// dd($failed_subjects);
$student->old_failed_subjects = count($old_failed_subjects);
// end foreach terms
if ($accumulation_hours) {
$student->accumulation_average = number_format($accumulation_points / $accumulation_hours, 2);
}
$student->terms = $terms;
$student->failed = $student->accumulation_average < 1.67 || $student->accum_previous < 2.0 && $student->accum_previous > 1.67 && ($student->accumulation_average < 2.0 && $student->accumulation_average > 1.67);
$student->failed_subjects = count(array_unique($failed_subjects));
$student->passed_subjects = count(array_unique($passed_subjects));
if (semester()->order != 'summer' && count($old_failed_subjects) + count($failed_subjects) >= 4) {
$student->failed = true;
}
if (semester()->order == 'summer') {
$student->failed = false;
}
});
$students = $students->filter(function ($student) use($request) {
return $student->failed == ($request->input('status', 's') == 'r') ? 1 : 0;
});
return view('students::success.index', compact('years', 'students', 'statuses', 'semester'));
}
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;
}
public function semester_report($semester_id)
{
$statistics = StudentSubject::select(DB::raw('sum(subject_subjects.amount * subject_subjects.hour) as amount,
sum(subject_subjects.hour) as hours,
academystructure_years.name as year_name ,
term_id ,
payed ,
count(distinct(students.id)) as studentcount'))->where('semester_id', $semester_id)->where('student_subjects.state', 'study')->where('students.state', 'active')->where('students.free_percent', '=', 0)->whereIn('students.study_state', ['active', 'repeat'])->join('students', 'students.id', '=', 'student_subjects.student_id')->join('subject_subjects', 'subject_subjects.id', '=', 'student_subjects.subject_id')->join('academystructure_departments', 'academystructure_departments.id', '=', 'students.academystructure_department_id')->join('academystructure_terms', 'academystructure_departments.term_id', '=', 'academystructure_terms.id')->join('academystructure_years', 'academystructure_terms.year_id', '=', 'academystructure_years.id')->groupBy('academystructure_years.id')->groupBy('payed')->get();
//return $statistics ;
$free_study = Student::where('free_percent', '>', 0)->where('state', 'active')->get();
return view('financials::reports.semester', compact('statistics', 'free_study'));
}
/**
* Handle the event.
*
* @param RegistrationStepChanged $event
* @return void
*/
public function handle(RegistrationStepChanged $event)
{
$registration = $event->registration;
$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();
}
if (empty($semester)) {
return false;
}
$registration->load('step', 'student', 'contactcountry', 'files');
if ($registration->step->enroll == 1 && !$registration->student) {
$equation_subjects_ids = RegistrationEquationSubject::whereNotNull('subject_id')->where('status', 'accepted')->whereHas('equation', function ($w) use($registration) {
$w->where('registration_id', $registration->id);
})->pluck('subject_id')->toArray();
$subject_ids = [];
$i = 0;
$department = 0;
while (count(array_diff($subject_ids, $equation_subjects_ids)) == 0) {
$department = Department::where('spec_id', $registration->academystructure_specialty_id)->where(function ($w) use($department) {
$w->orWhere('parent_id', !empty($department->id) ? $department->id : 0);
$w->orWhereNull('parent_id');
})->first();
$subject_ids = json_decode($department->subject_ids, TRUE);
$i++;
if ($i == 20) {
break;
}
}
if (empty($subject_ids)) {
return false;
}
$subjects = Subject::whereIn('id', $subject_ids)->get();
$mobile = ($registration->contactcountry ? $registration->contactcountry->calling_code : "968") . $registration->contact_mobile;
$data = ['username' => $registration->username, 'username_prefix' => $registration->enroll_code, 'password' => bcrypt($registration->contact_mobile), 'name' => $registration->fullname, 'email' => $registration->contact_email, 'mobile' => $mobile, 'state' => 'active', 'study_state' => 'active', 'national_id' => $registration->national_id, 'gender' => $registration->gender, 'registration_id' => $registration->id, 'academystructure_department_id' => $department->id, 'registration_type_id' => $registration->registration_type_id];
if ($registration->files) {
$photo = $registration->files->first(function ($key, $value) {
return $key == 'photo';
});
if ($photo) {
// $data['photo'] = asset($photo->file->url());
}
}
Eloquent::unguard();
$student = Student::create($data);
$invoiceItem = FinancialInvoiceItem::where('slug', 'study_fee')->first();
$study_fee = $registration->study_fee > 0 ? $registration->study_fee : $invoiceItem->amount;
$total_amount = $subjects->sum(function ($s) use($equation_subjects_ids, $study_fee) {
$amount = 0;
if (!in_array($s->id, $equation_subjects_ids)) {
$amount = !$s->is_quran ? $s->hour * (int) $study_fee : $s->hour * ((int) $study_fee / 2);
}
return $amount;
});
if ($student) {
if ($registration->debit && $invoiceItem) {
$invoiceData = ['ref_key' => 'registrations', 'ref_value' => $registration->id, 'student_id' => $student->id, 'amount' => $registration->debit, 'type' => 'credit', 'item_id' => $invoiceItem->id, 'pay_type' => 'epay', 'bank_code' => $registration->reason_code, 'transaction_wid' => $registration->transaction_uuid, 'note' => 'ايصال دفع مصاريف التسجيل.'];
FinancialInvoice::create($invoiceData);
// create debit invoice
$invoiceData = ['ref_key' => 'registrations', 'ref_value' => $registration->id, 'student_id' => $student->id, 'amount' => $total_amount, 'type' => 'debit', 'item_id' => $invoiceItem->id, 'pay_type' => 'epay', 'note' => 'فاتةورة التسجيل.'];
FinancialInvoice::create($invoiceData);
}
foreach ($subjects as $subject) {
$subject_state = in_array($subject->id, $equation_subjects_ids) ? 'equal' : 'study';
$subject_payed = in_array($subject->id, $equation_subjects_ids) ? 0 : 1;
if (!$registration->debit && $registration->step->make_payment) {
$subject_payed = 0;
}
$data = ['student_id' => $student->id, 'subject_id' => $subject->id, 'semester_id' => $semester->id, 'academystructure_department_id' => $department->id, 'state' => $subject_state, 'note' => '', 'payed' => $subject_payed];
$studentSubject = StudentSubject::create($data);
}
$historyData = ['student_id' => $student->id, 'state' => 'active', 'study_state' => 'active', 'academycycle_semester_id' => $semester->id, 'academystructure_department_id' => $department->id];
StudentHistory::create($historyData);
$this->createStudentEmail($student, $registration->contact_mobile);
try {
if (!empty($photo)) {
$student->photo = $photo;
$student->save();
}
} catch (\Symfony\Component\HttpFoundation\File\Exception\FileNotFoundException $e) {
}
}
//\Log::info('turn to student '.$response);
}
}
public function equalsubject(Request $request)
{
// for all_complete or part complete or all_refuse
$complete = 0;
$refuse = 0;
$equal_subject;
$order_state = '';
foreach ($request->order_equal_subject_id as $key => $value) {
$equal_subject = EqualSubject::with('order')->findOrFail($value);
if ($request->equal_subject_id[$key] != '-1') {
$equal_subject->equal_subject_id = $request->equal_subject_id[$key];
$equal_subject->is_equal = 1;
$complete = 1;
// convert student_subject state to equal
if ($equal_subject->save()) {
StudentSubject::where("subject_id", $equal_subject->equal_subject_id)->where('student_id', $equal_subject->order->student_id)->update(['state' => 'equal']);
}
} else {
$equal_subject->is_equal = -1;
$refuse = 1;
$equal_subject->save();
}
}
//convert order_equal state to (complete or part complete)
$equal_order = Equal::findOrFail($equal_subject->equal_order_id);
if ($complete == 0 && $refuse == 0) {
// no subjetct sent
} elseif ($complete == 1 && $refuse == 0) {
$order_state = 'قبول كلى';
} elseif ($complete == 0 && $refuse == 1) {
$order_state = 'رفض كلى';
} else {
$order_state = 'قبول جزئي';
}
$equal_order->state = $order_state;
$equal_order->save();
//save in history
$order_history = new Orderhistory();
$order_history->ref_key = 'order_equals';
$order_history->ref_value = $equal_order->id;
$order_history->state = $order_state;
//$order_history ->note =$request->note;
$order_history->created_by = user()->id;
$order_history->save();
return redirect()->route('equal.show', $equal_order->id)->with('success', trans('orders::order.change_success'));
}
public function show($student_id)
{
$student = Student::with(['classrooms', 'registration', 'registration.type', 'department', 'subjects'])->select('students.*')->joinTermName()->findOrFail($student_id);
$grades = StudentGrade::with('semester', 'subject')->inCurrentSemester()->where('student_id', $student->id)->get();
$registration = $student->registration;
//$study_plan = getspecialtystruct(1);
if ($student->department->spec_id) {
$study_plan = getspecialtystruct($student->department->spec_id);
} else {
$study_plan = getspecialtystruct(2);
}
//dd($study_plan);
$departments = Department::select(\DB::raw('GROUP_CONCAT(subject_ids) as sid , id as name'))->where('spec_id', 1)->get()->toArray();
$departments_subjects;
foreach ($departments as $key => &$aa) {
$a = implode(',', array_unique(array_merge(json_decode(str_replace('],[', ',', $aa['sid']), TRUE))));
$departments_subjects = explode(',', $a);
}
$student_subjects = StudentSubject::select(\DB::raw('GROUP_CONCAT(subject_id) as sid'))->where('student_id', $student_id)->where('state', 'success')->get()->toArray();
$student_subject_ids = explode(',', $student_subjects[0]['sid']);
$result = array_diff($departments_subjects, $student_subject_ids);
$complete_deplome_subjects = empty($result);
return view('students::students.show', compact('student', 'registration', 'grades', 'study_plan', 'study_subject', 'state_complite_sub', 'complete_deplome_subjects'));
}
/**
* Execute the job.
*
* @return void
*/
public function handle()
{
$student = Student::find($this->student_id);
$currentSemester = semester();
$summerSemester = Semester::where('id', '>', $currentSemester->id)->where('order', 'summer')->orderBy('id', 'ASC')->first();
// Log::info($student->id, $currentSemester->id, $summerSemester->id);
if ($student && $currentSemester && $summerSemester) {
$nextSemester = Semester::where('id', '>', $currentSemester->id)->where('order', '!=', 'summer')->orderBy('id', 'ASC')->first();
if (!$nextSemester) {
return false;
}
$current_department_id = $student->academystructure_department_id;
if ($currentSemester->order != 'summer') {
$nextDepartment = Department::where('parent_id', $student->academystructure_department_id)->first();
if ($nextDepartment) {
$student->state = 'active';
$student->study_state = 'success';
$student->academystructure_department_id = $nextDepartment->id;
}
}
$history = StudentHistory::where('student_id', $student->id)->orderBy('id', 'desc')->where('academycycle_semester_id', $currentSemester->id)->first();
if ($history) {
$history->study_state = 'success';
$history->save();
}
if ($student->save()) {
// get current semester subjects and make them fail or success for current semester
$subjects = StudentSubject::selectRaw("subsub.id as subject_id,SUM(stugr.value) AS subject_points,student_subjects.state as subject_state,subsub.name AS subject_name, subsub.hour AS subject_hours")->join('subject_subjects AS subsub', function ($join) {
$join->on('subsub.id', '=', 'student_subjects.subject_id');
})->where('student_subjects.student_id', $student->id)->leftJoin('student_grades AS stugr', function ($join) use($currentSemester, $student) {
$join->on('stugr.subject_id', '=', 'subsub.id')->where('stugr.semester_id', '=', $currentSemester->id)->where('stugr.student_id', '=', $student->id);
})->whereIn('state', ['study'])->where('student_subjects.semester_id', '=', $currentSemester->id)->where('student_subjects.student_id', $student->id)->groupBy('subsub.id')->get();
$failed_subjects_ids = [];
$passed_subjects_ids = [];
foreach ($subjects as $subject) {
if ($subject->subject_points < 50) {
$failed_subjects_ids[] = $subject->subject_id;
} elseif ($subject->subject_points >= 50) {
$passed_subjects_ids[] = $subject->subject_id;
}
}
StudentSubject::where('student_id', $student->id)->whereIn('subject_id', $failed_subjects_ids)->update(['state' => 'fail']);
StudentSubject::where('student_id', $student->id)->whereIn('subject_id', $passed_subjects_ids)->update(['state' => 'success']);
// end success and fail
if ($currentSemester->order != 'summer' && $nextDepartment) {
$next_subject_ids = json_decode($nextDepartment->subject_ids, TRUE);
$equal_subject_ids = EqualSubject::whereHas('order', function ($w) use($student) {
$w->where('student_id', $student->id);
})->pluck('equal_subject_id')->toArray();
$next_subject_study_ids = array_diff($next_subject_ids, $equal_subject_ids);
$next_subject_equal_ids = array_intersect($next_subject_ids, $equal_subject_ids);
$student->subjects()->attach($next_subject_study_ids, ['semester_id' => $nextSemester->id, 'academystructure_department_id' => $nextDepartment->id, 'state' => 'study']);
$student->subjects()->attach($next_subject_equal_ids, ['semester_id' => $nextSemester->id, 'academystructure_department_id' => $nextDepartment->id, 'state' => 'equal']);
$data = ['state' => 'active', 'study_state' => 'active', 'academycycle_semester_id' => $nextSemester->id, 'academystructure_department_id' => $nextDepartment->id];
$student->histories()->create($data);
// create invoice for student
if ($next_subject_equal_ids) {
$item = FinancialInvoiceItem::where('slug', 'study_fee')->first();
$subjects_count = Subject::whereIn('id', $next_subject_equal_ids)->where('is_quran', 0)->count();
$data = ['student_id' => $student->id, 'type' => 'debit', 'item_id' => $item->id, 'pay_type' => NULL, 'semester_id' => $nextSemester->id, 'amount' => $subjects_count * $item->amount];
FinancialInvoice::create($data);
}
}
// move any failed subjects if less than 3 to summer semester
$all_failed_subjects = StudentSubject::groupBy('subject_id')->where('state', 'fail')->pluck('subject_id')->toArray();
$all_passed_subjects = StudentSubject::groupBy('subject_id')->where('state', 'success')->pluck('subject_id')->toArray();
$failed_subjects = array_diff($all_failed_subjects, $all_passed_subjects);
if (COUNT($failed_subjects) <= 3) {
$summerSemester = Semester::where('id', '>', $currentSemester->id)->where('order', 'summer')->orderBy('id', 'ASC')->first();
$student->subjects()->attach($failed_subjects, ['semester_id' => $summerSemester->id, 'academystructure_department_id' => $current_department_id, 'state' => 'study']);
$data = ['state' => 'active', 'study_state' => 'active', 'academycycle_semester_id' => $summerSemester->id, 'academystructure_department_id' => $current_department_id];
$student->histories()->create($data);
}
}
}
}
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
Model::unguard();
// echo 'in';
if (StudentSubject::count()) {
// return;
}
// $user_subjects = DB::connection('old')
// ->table('user_subject')
// // ->where('us_userid', 148)
// ->get();
// echo DB::connection('old')->table('user_subject')->count();
// exit;
// $this->call("OthersTableSeeder");
DB::statement('SET FOREIGN_KEY_CHECKS=0;');
StudentSubject::where('student_id', '!=', 10000)->delete();
// StudentSubject::truncate();
StudentGrade::where('student_id', '!=', 10000)->delete();
// StudentGrade::truncate();
$all_user_subjects = DB::connection('old')->table('user_subject')->leftJoin('users', 'users.user_id', '=', 'user_subject.us_userid')->select('users.user_academyid', 'us_state', 'us_subid', 'us_userid', 'us_semid', 'us_result')->get();
// echo 'got it';
$new_departments = $new_department = Department::select('ad.id', 'ay.id AS ay_id', 'as.code')->join('academystructure_terms as at', 'at.id', '=', 'ad.term_id')->join('academystructure_years as ay', function ($j) {
$j->on('at.year_id', '=', 'ay.id');
// ->where('ay.id', '=',$academy['academy_year']);
})->join('academystructure_specialties as as', function ($j) {
$j->on('as.id', '=', 'ad.spec_id');
// ->where('as.code', '=', $dep_sepcialty_code);
})->from('academystructure_departments as ad')->get()->toArray();
$all_old_academies = $academy = DB::connection('old')->table('sub_academy')->select('academy_department', 'academy_year', 'academy_term')->select('academy.*', 'sub_academy.sub_acad_subid')->join('academy', 'sub_acad_academyid', '=', 'academy_id')->get();
$all_old_academies = array_map(function ($a) {
return (array) $a;
}, $all_old_academies);
$student_subjects = [];
$student_grades = [];
// var_dump(count($all_user_subjects));
foreach ($all_user_subjects as $user_subject) {
$student_subject = [];
$student_grade = [];
$state = $user_subject->us_state == 'sucess' ? 'success' : $user_subject->us_state;
$state = $state == 'edu' ? 'study' : $state;
$state = $state == 'equ' ? 'equal' : $state;
if ($user_subject->us_semid == 9) {
$academy = array_filter($all_old_academies, function ($a) use($user_subject) {
return $user_subject->user_academyid == $a['academy_id'];
});
} else {
$academy = array_filter($all_old_academies, function ($a) use($user_subject) {
return $user_subject->us_subid == $a['sub_acad_subid'];
});
}
$academy = current($academy);
if (empty($academy)) {
continue;
}
$dep_sepcialty_code = 'D';
if ($academy['academy_department'] == 'اصول الفقة') {
$dep_sepcialty_code = 'O';
} elseif ($academy['academy_department'] == 'الدرسات الاسلامية') {
$dep_sepcialty_code = 'I';
}
$new_departments_filtered = array_filter($new_departments, function ($d) use($academy, $dep_sepcialty_code) {
$exist = $d['ay_id'] == $academy['academy_year'] && $d['code'] == $dep_sepcialty_code;
if ($exist) {
// var_dump($d);
}
return $exist;
});
if ($academy['academy_term'] == 2) {
$new_department = array_pop($new_departments_filtered);
} else {
$new_department = array_shift($new_departments_filtered);
}
$student_subject['student_id'] = $user_subject->us_userid;
$student_subject['subject_id'] = $user_subject->us_subid;
$student_subject['semester_id'] = $user_subject->us_semid;
$student_subject['state'] = $state;
$student_subject['academystructure_department_id'] = $new_department['id'];
$student_grade['student_id'] = $user_subject->us_userid;
$student_grade['subject_id'] = $user_subject->us_subid;
$student_grade['semester_id'] = $user_subject->us_semid;
$student_grade['notes'] = "تم جلبها من النظام القديم";
$student_grade['value'] = $user_subject->us_result;
$student_grade['created_at'] = new DateTime();
$student_grade['updated_at'] = new DateTime();
if ($user_subject->us_result >= 0) {
$student_grades[] = $student_grade;
}
$student_subjects[] = $student_subject;
}
foreach (array_chunk($student_subjects, 1000) as $chunk) {
DB::table('student_subjects')->insert($chunk);
}
foreach (array_chunk($student_grades, 1000) as $chunk) {
DB::table('student_grades')->insert($chunk);
}
}
