示例#1
0
 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'));
 }