public function index()
{
$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();
// }
// return $semester;
$students_by_years = Year::selectRaw("\n academystructure_years.name,\n SUM(IF(stu.gender='m',1,0)) AS males,\n SUM(IF(stu.gender='f',1,0)) AS females")->leftJoin('academystructure_terms AS at', 'at.year_id', '=', 'academystructure_years.id')->leftJoin('academystructure_departments AS ad', 'ad.term_id', '=', 'at.id')->leftJoin('student_histories AS sh', function ($join) {
$join->on('sh.academystructure_department_id', '=', 'ad.id')->where('academycycle_semester_id', '=', semester()->id);
})->leftJoin('students AS stu', 'stu.id', '=', 'sh.student_id')->groupBy('academystructure_years.id')->get();
$students_by_state = Student::selectRaw("DISTINCT students.id,\n students.state,\n \tSUM(IF(students.state='active',1,0)) AS active_all_students,\n \tSUM(IF(students.state='delayed',1,0)) AS delayed_all_students,\n \tSUM(IF(students.state='discontinuous',1,0)) AS discontinuous_all_students,\n \tSUM(IF(students.state='withdrawn',1,0)) AS withdrawn_all_students,\n \tSUM(IF(students.state='fired',1,0)) AS fired_all_students,\n \tSUM(IF(students.state='graduate',1,0)) AS graduate_all_students,\n\n \tSUM(IF(students.gender='m' AND students.state='active',1,0)) AS active_male_students,\n \tSUM(IF(students.gender='m' AND students.state='delayed',1,0)) AS delayed_male_students,\n \tSUM(IF(students.gender='m' AND students.state='discontinuous',1,0)) AS discontinuous_male_students,\n \tSUM(IF(students.gender='m' AND students.state='withdrawn',1,0)) AS withdrawn_male_students,\n \tSUM(IF(students.gender='m' AND students.state='fired',1,0)) AS fired_male_students,\n \tSUM(IF(students.gender='m' AND students.state='graduate',1,0)) AS graduate_male_students,\n\n \tSUM(IF(students.gender='f' AND students.state='active',1,0)) AS active_female_students,\n \tSUM(IF(students.gender='f' AND students.state='delayed',1,0)) AS delayed_female_students,\n \tSUM(IF(students.gender='f' AND students.state='discontinuous',1,0)) AS discontinuous_female_students,\n \tSUM(IF(students.gender='f' AND students.state='withdrawn',1,0)) AS withdrawn_female_students,\n \tSUM(IF(students.gender='f' AND students.state='fired',1,0)) AS fired_female_students,\n \tSUM(IF(students.gender='f' AND students.state='graduate',1,0)) AS graduate_female_students\n \t")->first();
// return $students_by_state;
$genders = Student::selectRaw("\n SUM(IF(students.gender='m',1,0)) AS males,\n SUM(IF(students.gender='f',1,0)) AS females")->join('student_histories AS sh', function ($join) {
$join->on('sh.student_id', '=', 'students.id')->whereIn('sh.study_state', ['active', 'success', 'fail'])->where('academycycle_semester_id', '=', semester()->id);
})->isStudying()->first();
$sessions = ClassroomSession::selectRaw("DATE(start_at) as start_at,SUM(IF(canceled=0,1,0)) AS held, SUM(IF(canceled=1,1,0)) AS canceled")->whereDate('start_at', '>=', date('Y-m-d', strtotime('-30 days')))->groupBy(\DB::raw('DATE(start_at)'))->get();
$session_per_days = [];
for ($i = 30; $i > 0; $i--) {
if ($session = $sessions->whereLoose('start_at', date('Y-m-d 00:00:00', strtotime("-" . $i . " days")))->first()) {
$session_per_days[] = $session;
} else {
$session_per_days[] = ['held' => 0, 'canceled' => 0, 'start_at' => date('Y-m-d 00:00:00', strtotime("-" . $i . " days"))];
}
}
return view('dashboards.admin', compact('semester', 'genders', 'students_by_years', 'students_by_state', 'session_per_days'));
}
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 classroomchoicen(Request $request)
{
$student_unchosen = Student::selectRaw('students.name, students.username, students.id')->whereHas('subjects', function ($query) {
$query->where('state', '=', 'study');
})->with(['classrooms' => function ($query) {
$query->where('classrooms.semester_id', semester()->id);
}, 'subjects' => function ($query) {
$query->where('semester_id', semester()->id);
$query->where('state', 'study')->where('is_quran', 0);
}]);
$per_page = request('per_page') ? request('per_page') : 800;
$student_unchosen = $student_unchosen->paginate($per_page);
//dd($rrr);;
return view('classrooms::reports.classroomchoicen', compact('student_unchosen'));
}
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'));
}
