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')); }