public function studentgrad(Request $request)
{
/*
$statistics = StudentGrade::where('student_grades.semester_id', semester()->id)
->select('subject_id', 'student_id', DB::raw('SUM(value) as sgrade'))
->with(['subject' , 'student'])
->groupBy('student_id' , 'subject_id')
->orderBy('student_id');
if (request('mark_from')){
$statistics->having('sgrade', '>=', request('mark_from'));
}
if (request('mark_to')){
$statistics->having('sgrade', '<', request('mark_to'));
}
*/
$statistics = Student::select(\DB::raw('SUM(value) as sgrad , subject_subjects.name as sub_name, subject_subjects.id as sub_id , students.id as stud_id, students.username, students.name'))->join('student_grades', 'student_grades.student_id', '=', 'students.id')->join('subject_subjects', 'subject_subjects.id', '=', 'student_grades.subject_id')->where('student_grades.semester_id', semester()->id)->groupBy('student_id', 'subject_id');
if (request('mark_from')) {
$statistics->havingRaw('SUM(value)>=' . request('mark_from'));
}
if (request('mark_to')) {
$statistics->havingRaw('SUM(value)<' . request('mark_to'));
}
if (request('student_id')) {
$statistics->where('students.id', request('student_id'));
}
if (request('subject_id')) {
$statistics->where('subject_subjects.id', request('subject_id'));
}
$per_page = request('per_page') ? request('per_page') : 50;
$statistics = $statistics->paginate($per_page);
$statistics->appends($request->except("page"));
$subjects = Subject::lists('name', 'id')->toArray();
return view('exams::reports.studentgrad', compact('statistics', 'subjects'));
}
public function reset(Request $request)
{
$input = $request->all();
$username = substr($input['username'], -5, 5);
$student = Student::select('students.id', 'students.registration_id', 'students.name', 'students.email', 'students.mobile', 'registrations.contact_email')->leftJoin('registrations', 'registrations.id', '=', 'students.registration_id')->where('students.username', $username)->groupBy('students.id')->where('students.national_id', $input['nationalid'])->first();
if (!$student) {
return response()->json(['success' => false], 200, [], JSON_NUMERIC_CHECK);
}
if ($student && $student->id == 10001) {
$password = substr(md5(microtime()), rand(0, 26), 8);
$student->password = bcrypt($password);
$student->save();
event(new StudentPasswordChanged($student, $password));
}
return response()->json(['success' => true], 200, [], JSON_NUMERIC_CHECK);
}
public function store(SendStudentMessageRequest $request)
{
$subjects_students = [];
$classrooms_students = [];
$years_students = [];
$unchosen_classrooms_ids = [];
$unchosen_centers_ids = [];
$student_list_ids = [];
$students = [$request->input('students')];
if ($request->has('subjects')) {
$subjects_students = Student::select('id')->isStudying()->whereHas('subjects', function ($query) use($request) {
$query->whereIn('subject_subjects.id', $request->input('subjects', []));
})->pluck('id')->toArray();
}
if ($request->has('student_list')) {
$student_list = preg_split('/\\r\\n|[\\r\\n]/', $request->input('student_list'));
$student_list_ids = Student::select('id')->whereIn('username', $student_list)->pluck('id')->toArray();
}
if ($request->has('unchosen_centers')) {
$unchosen_centers_ids = Student::select('id')->isStudying()->has('centers', '=', 0)->pluck('id')->toArray();
}
// if($request->has('unchosen_classrooms')) {
// $unchosen_classrooms_ids = [];
// $un_students = Student::select('id')->isStudying()->get();
// foreach ($un_students as $student) {
// $subjects_count = StudentSubject::where([
// 'semester_id' => semester()->id,
// 'student_id' => $student->id,
// 'state' => 'study'
// ])
// ->count();
// $classrooms_count = ClassroomStudent::where([
// 'semester_id' => semester()->id,
// 'student_id' => $student->id
// ])
// ->count();
// if ($classrooms_count < $subjects_count) {
// // $unchosen_classrooms_ids[] = $student->id;
// }
// }
// // dd(count($unchosen_classrooms_ids));
// }
// exit;
if ($request->has('classrooms')) {
$classrooms_students = Student::isStudying()->select('id')->whereHas('classrooms', function ($query) use($request) {
$query->whereIn('classrooms.id', $request->input('classrooms', []));
})->pluck('id')->toArray();
}
if ($request->has('years')) {
$years_students = Student::isStudying()->select('students.id')->inYear($request->get('years'))->pluck('id')->toArray();
}
$students_ids = array_unique(array_merge($classrooms_students, $subjects_students, $students, $years_students, $unchosen_classrooms_ids, $student_list_ids, $unchosen_centers_ids));
// exit(var_dump($students_ids));
$email = $request->input('email');
$sms = substr($request->input('sms'), 0, 201);
$attachment = '';
if ($request->hasFile('attachment') && $request->file('attachment')->isValid()) {
$file = $request->file('attachment');
$filename = 'attachment_' . time() . "." . $file->getClientOriginalExtension();
$file->move('temp', $filename);
$attachment = asset('temp/' . $filename);
}
foreach (array_chunk($students_ids, 50) as $chunk) {
$job = (new SendStudentsMessage($chunk, $email, $sms, user()->id, $attachment))->onConnection('redis')->onQueue('messages');
$this->dispatch($job);
}
return redirect()->route('students.messages.index')->with('success', 'جاري إرسال الرسالة للمحاضرين الذين حددتهم، ستظهر الرسائل على صفحة الإدارة فور انتهاء عملية الارسال');
}
public function getAcademicCalendar($background = false)
{
$student = Student::select('students.*')->joinTermName()->findOrFail($this->student_id);
$exams = Exam::inCurrentSemester()->ofStudent($this->student_id)->select('ss.name', 'exams.start_at')->get();
$pdf = $this->preparePdf($background);
// ---- >
$html = '<h2>التقويم الأكاديمي</h2>';
$pdf->writeHTMLCell(210, 20, 0, 50, $html, 0, 0, false, true, "C");
$pdf->SetFontSize(12);
$html = 'بتاريخ : ' . Date::now()->format('l d F Y');
$pdf->writeHTMLCell(60, 10, 150, 40, $html, 0, 0, false, true, "C");
$pdf->SetFontSize(13, 'B');
$html = '<table cellpadding="3">
<tr><td align="right">إسم لطالب</td><td align="right">' . $student->name . '</td></tr>
<tr><td align="right">الرقم الجامعي</td><td align="right">' . $student->code . '</td></tr>
<tr><td align="right">تاريخ الإلتحاق</td><td align="right">' . Date::parse($student->created_at)->format('l d F Y') . '</td></tr>
<tr><td align="right">السنة الدراسية</td><td align="right">' . $student->year_name . '</td></tr>
</table>';
$pdf->writeHTMLCell(100, 20, 10, 70, $html, 0, 0, false, true, "R");
$pdf->SetFontSize(15, 'B');
$pdf->writeHTMLCell(170, 20, 20, 110, view('students::documents._examtable', compact('exams'))->render(), 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, 90, 250, $html, 0, 0, false, true, "C");
ob_clean();
return $pdf;
}
public function unpaid_report($semester_id, request $request)
{
$statistics = Student::select('name', 'username', 'email', 'mobile', 'gender', 'national_id')->where('state', 'active')->where('free_percent', 0)->whereNotIn('id', function ($query) {
$query->select('student_id')->from('financial_invoices')->where('semester_id', 9)->where('type', 'credit');
})->whereIn('id', function ($query) {
$query->select('student_id')->from('student_subjects')->where('semester_id', 9)->where('state', 'study')->distinct();
});
if ($request->has('name')) {
$statistics->where('name', 'LIKE', "%" . $request->input('name') . '%');
}
if ($request->has('code')) {
$statistics->where('username', $request->input('code'));
}
$statistics = $statistics->orderBy('name', 'ASC')->get();
/*
$statistics = DB::select("
SELECT name , username , email, mobile , gender , national_id
from students
where
state = 'active'
AND
free_percent = 0
AND
id not IN
( SELECT student_id from financial_invoices where semester_id = 9 and type = 'credit')
AND
id IN
( SELECT DISTINCT student_id from student_subjects where semester_id = 9 and state = 'study')
");
*/
return view('financials::reports.unpaid', compact('statistics'));
}
private function prepQuery($input = [])
{
// dd(substr($input['code'],0,3));
$input['username'] = (!empty($input['code']) and substr($input['code'], 0, 3) != 'e20' and strlen($input['code']) >= 5) ? strtoupper(substr($input['code'], -5, 5)) : (!empty($input['code']) ? $input['code'] : null);
if (empty($input['username'])) {
unset($input['username']);
}
unset($input['code']);
$students = Student::select('lists_countries.name as country_name', 'lists_countries.iso_3166_2 as country_code', 'students.*', 'academystructure_departments.term_id', 'academystructure_departments.spec_id', 'registrations.nationality_country_id', \DB::raw('GROUP_CONCAT(academystructure_departments.spec_id, "") as sps'), 'registrations.contact_country_id', 'registrations.nationality_city_id')->leftJoin('academystructure_departments', 'academystructure_departments.id', '=', 'students.academystructure_department_id')->leftJoin('registrations', 'registrations.id', '=', 'students.registration_id')->leftJoin('lists_countries', 'lists_countries.id', '=', 'registrations.nationality_country_id')->orderBy('students.id', 'desc');
$filters = ['username', 'email', 'name', 'state', 'mobile', 'national_id', 'gender', 'state', 'study_state'];
if (!empty($input)) {
foreach ($filters as $filter) {
if (!empty($input[$filter])) {
$students->where("students." . $filter, 'LIKE', "%" . $input[$filter] . "%");
}
}
}
/*
if(!empty($input)) {
foreach ($filters as $filter) {
if(!empty($input[$filter]) && !in_array($filter, ['name'])) {
$students->where("students.".$filter, 'LIKE',"%".$input[$filter]."%");
} elseif($filter=='name' && !empty($input['name'])) {
$name_array = explode(' ',$input[$filter]);
$students->where(function($query) use ($name_array) {
foreach($name_array as $name_chunk)
$query->orWhere('students.name', 'LIKE', '%'.$name_chunk.'%');
});
}
}
}
*/
if (request('year_term')) {
$students->where('academystructure_departments.term_id', request('year_term'));
}
if (request('spec_id')) {
$students->where('academystructure_departments.spec_id', request('spec_id'));
}
if (request('nationality_country_id')) {
$students->where('registrations.nationality_country_id', request('nationality_country_id'));
}
if (request('contact_country_id')) {
$students->where('registrations.contact_country_id', request('contact_country_id'));
}
if (request('nationality_city_id')) {
$students->where('registrations.nationality_city_id', request('nationality_city_id'));
}
$students = $students->joinTermName()->groupBy('students.id');
return $students;
}
private function prepQuery($input = [])
{
$input['username'] = (!empty($input['code']) and substr($input['code'], 0, 3) != 'e20' and strlen($input['code']) >= 5) ? strtoupper(substr($input['code'], -5, 5)) : (!empty($input['code']) ? $input['code'] : null);
if (empty($input['username'])) {
unset($input['username']);
}
unset($input['code']);
$students = Student::select('lists_countries.name as country_name', 'lists_countries.iso_3166_2 as country_code', 'students.*', 'academystructure_departments.term_id', 'academystructure_departments.spec_id', 'registrations.nationality_country_id', \DB::raw('GROUP_CONCAT(academystructure_departments.spec_id, "") as sps'), 'registrations.contact_country_id', 'registrations.nationality_city_id')->leftJoin('academystructure_departments', 'academystructure_departments.id', '=', 'students.academystructure_department_id')->leftJoin('registrations', 'registrations.id', '=', 'students.registration_id')->leftJoin('lists_countries', 'lists_countries.id', '=', 'registrations.nationality_country_id')->where('state', 'active')->orderBy('students.id', 'desc');
$filters = ['username', 'email', 'name', 'state', 'mobile', 'national_id', 'gender'];
if (!empty($input)) {
foreach ($filters as $filter) {
if (!empty($input[$filter])) {
$students->where("students." . $filter, 'LIKE', "%" . $input[$filter] . "%");
}
}
}
if (request('year_term')) {
$students->where('academystructure_departments.term_id', request('year_term'));
}
if (request('spec_id')) {
$students->where('academystructure_departments.spec_id', request('spec_id'));
}
if (request('exam_center_id')) {
$students->where('exam_center_student.exam_center_id', request('exam_center_id'));
}
if (request('exam_center_state')) {
if (request('exam_center_state') == 1) {
$students->whereNotNull('exam_center_student.exam_center_id');
} else {
$students->whereNull('exam_center_student.exam_center_id');
}
}
// if(request('contact_country_id')) {
// $students->where('registrations.contact_country_id', request('contact_country_id'));
// }
// if(request('nationality_city_id')) {
// $students->where('registrations.nationality_city_id', request('nationality_city_id'));
// }
$students = $students->JoinTermName()->groupBy('students.id');
return $students;
}
