public function edit($user_id)
{
$subjects = Subject::where('is_quran', 1)->pluck('name', 'id')->toArray();
$students = Student::isStudying()->pluck('name', 'id')->toArray();
$user = User::with('quran_subjects', 'quran_students')->findOrFail($user_id);
return view('quran::evaluators.edit', compact('user', 'subjects', 'students'));
}
public function index(Request $request)
{
$semester_id = $request->input('semester_id');
$students = Student::isStudying()->selectRaw('students.*,
SUM(invoices.amount) as total,
SUM(IF(type="debit",invoices.amount,0)) AS total_debit,
SUM(IF(type="credit",invoices.amount,0)) AS total_credit
')->leftJoin('financial_invoices AS invoices', function ($j) use($semester_id) {
$j->on('invoices.student_id', '=', 'students.id');
if ($semester_id) {
$j->where('invoices.semester_id', '=', $semester_id);
}
})->groupBy('students.id');
$total_amounts = Student::selectRaw('
SUM(IF(type="debit",invoices.amount,0)) AS total_debit,
SUM(IF(type="credit",invoices.amount,0)) AS total_credit
')->leftJoin('financial_invoices AS invoices', 'invoices.student_id', '=', 'students.id');
$semesters = Semester::selectRaw('as.id, CONCAT(as.name, " ",ay.name) as name')->join('academycycle_years as ay', 'as.academycycle_year_id', '=', 'ay.id')->from('academycycle_semesters as as')->groupBy('as.id')->pluck('name', 'id');
$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();
if ($request->has('name')) {
$students->where('students.name', 'LIKE', "%" . $request->input('name') . "%");
}
if ($request->has('pay_type')) {
$students->where('invoices.pay_type', 'LIKE', "%" . $request->input('pay_type') . "%");
}
if ($request->has('has_credit')) {
$students->havingRaw('(SUM(IF(type="debit",invoices.amount,0))-SUM(IF(type="credit",invoices.amount,0)))>0');
}
if ($request->has('has_debit')) {
$students->havingRaw('(SUM(IF(type="credit",invoices.amount,0))-SUM(IF(type="debit",invoices.amount,0)))>0');
}
if ($request->has('code')) {
$students->where(function ($query) use($request) {
return $query->orWhere('students.username', 'LIKE', "%" . substr($request->input('code'), -5, 5) . "%")->orWhere('students.username_prefix', 'LIKE', "%" . substr($request->input('code'), -5, 5) . "%");
});
}
if ($request->has('year_term')) {
$students->whereHas('department', function ($query) use($request) {
$query->where('term_id', '=', $request->input('year_term'));
});
}
if ($semester_id) {
$students->where(function ($query) use($semester_id) {
$query->orWhereNull('invoices.semester_id')->orWhere('semester_id', $semester_id);
});
$total_amounts->where('semester_id', $semester_id);
}
$has_search = count($request->except('page'));
$students = $students->paginate(50);
// return $students;
$total_amounts = $total_amounts->first();
return view('financials::reports.index', compact('students', 'has_search', 'semesters', 'total_amounts', 'year_term'));
}
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 classrooms(Request $request, $export = 0)
{
$students = Student::isStudying()->with(['subjects' => function ($q) use($request) {
$q->wherePivot('state', 'study');
$q->where('semester_id', semester()->id);
if ($request->has('subject_subject_id')) {
$q->where('subject_subjects.id', $request->input('subject_subject_id'));
}
}, 'classrooms' => function ($q) {
$q->where('classrooms.semester_id', semester()->id);
}]);
if ($request->has('subject_subject_id')) {
$students->whereHas('subjects', function ($q) use($request) {
$q->where('subject_subjects.id', $request->input('subject_subject_id'))->where('semester_id', semester()->id)->where('state', 'study');
});
}
if ($request->has('classroom_select_id')) {
$selected = request('classroom_select_id');
$subject_id = request('subject_subject_id');
$students = $students->whereHas('classrooms', function ($query) use($subject_id) {
$query->where('subject_subject_id', $subject_id);
}, $selected);
}
if (request('spec_id')) {
$spec_id = request('spec_id');
$students->wherehas('department', function ($q) use($spec_id) {
$q->where('academystructure_departments.spec_id', $spec_id);
});
}
if (request('nationality_country_id')) {
$nationality_country_id = request('nationality_country_id');
$students->whereHas('registration', function ($q) use($nationality_country_id) {
$q->where('registrations.nationality_country_id', $nationality_country_id);
});
}
if (request('contact_country_id')) {
$contact_country_id = request('contact_country_id');
$students->whereHas('registration', function ($q) use($contact_country_id) {
$q->where('registrations.contact_country_id', $contact_country_id);
});
}
if ($export) {
$students = $students->get();
Excel::create('students', function ($excel) use($students) {
$excel->sheet('stu', function ($sheet) use($students) {
$sheet->loadView('classrooms::reports.export', compact('students'));
})->download('xlsx');
});
return redirect()->route('classrooms.sessions.index', $request->all());
}
$per_page = request('per_page') ? request('per_page') : 30;
$students = $students->paginate($per_page);
$students = $students->appends($request->except("page"));
$total_chosen = Student::join('classroom_students as cs', 'cs.student_id', '=', 'students.id')->join('classrooms as c', function ($j) {
$j->on('c.id', '=', 'cs.classroom_id')->where('c.semester_id', '=', semester()->id);
})->join('student_subjects as ss', function ($j) {
$j->on('ss.subject_id', '=', 'c.subject_subject_id')->on('ss.student_id', '=', 'students.id')->where('ss.state', '=', 'study');
})->groupBy('students.id');
if ($request->has('subject_subject_id')) {
$total_chosen->where('c.subject_subject_id', $request->input('subject_subject_id'));
}
if ($request->has('classroom_id')) {
$total_chosen->where('c.id', $request->input('classroom_id'));
}
$total_chosen = $total_chosen->get()->count();
$total_unchosen = StudentSubject::whereIn('student_subjects.state', ['study'])->selectRaw('student_subjects.student_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');
})->havingRaw('COUNT(classroom_students.id)=0')->groupBy('student_subjects.subject_id', 'student_subjects.student_id');
if ($request->has('subject_subject_id')) {
$total_unchosen->where('student_subjects.subject_id', $request->input('subject_subject_id'));
}
$total_unchosen = $total_unchosen->get()->groupBy('student_id')->count();
$subjects = Subject::where('is_quran', 0)->pluck('name', 'id')->toArray();
$classrooms = Classroom::inCurrentSemester()->pluck('code', 'id')->toArray();
$classroom_select = config('classrooms.classroom_select');
$spec_id = Specialty::pluck('name', 'id')->toArray();
$county = Country::pluck('name', 'id')->toArray();
$inputs = request()->all();
return view('classrooms::reports.classrooms', compact('students', 'subjects', 'classrooms', 'total_chosen', 'total_unchosen', 'classroom_select', 'spec_id', 'county', 'inputs'));
}
