I have this method I want to display values in a JSON object and some of the values are being fetched from the array and passed through DB queries by for loop in order to check each value in the array but the answer is displayed only one value after passing through the loop but I want to response based in each grade passed in a for loop
public function TODreport($school_id, $date){
$school = School::where('id', $school_id)->first();
$school_education_level = $school->educationLevel;
$levels =[];
$grade = null;
if($school_education_level == 'Secondary'){
array_push($levels, 'Form One', 'Form Two', 'Form Three', 'Form Four');
foreach($levels as $level){
$total_students = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('grade', $level )
->count();
$total_boys_present_in_class = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'male');
})
->count();
$total_girls_present_in_class = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'female');
})
->count();
$total_boys_absent_in_class = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'male');
})
->count();
$total_girls_absent_in_class = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'female');
})
->count();
}
$grade = $level;
}else{
array_push($levels,'Standard One','Standard Two', 'Standard Three','Standard Four','Standard Five', 'Standard Six', 'Standard Seven');
foreach($levels as $level){
$grade = $level;
$total_students = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('grade', $level )
->count();
$total_boys_present_in_class = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'male');
})
->count();
$total_girls_present_in_class = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'female');
})
->count();
$total_boys_absent_in_class = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'male');
})
->count();
$total_girls_absent_in_class = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'female');
})
->count();
}
}
//-------total number of students called in attendance in that date-------//
$attendanceschool_fetched = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->get();
$total_students_in_school = $attendanceschool_fetched->count();
//-------total number of students called in attendance in that date-------//
//--------total present students fetched in that date-------------------------------//
$attendance_fetched_present = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")->get();
$total_present_students = $attendance_fetched_present->count();
//--------total present students fetched in that date-------------------------------//
//---------total present boys-----------------------------------------------------//
$total_boys_present = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->whereHas('student' , function($query){
return $query->where('gender', 'male');
})
->count();
//---------total present girls-----------------------------------------------------//
$total_girls_present = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->whereHas('student' , function($query){
return $query->where('gender', 'female');
})
->count();
//---------------total absent students in a school-------------------------------//
$attendance_fetched_absent = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->get();
$total_absent_students = $attendance_fetched_absent->count();
//------------total absent boys in a school---------------------------------//
$total_boys_absent = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->whereHas('student' , function($query){
return $query->where('gender', 'male');
})
->count();
//-----------------total absent girls in a school ---------------------------//
$total_girls_absent = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->whereHas('student' , function($query){
return $query->where('gender', 'female');
})
->count();
return response()->json([
'message'=> 'Attendance Report in School',
$grade => [
'TotalStudents' => $total_students,
'TotalPresentBoys'=> $total_boys_present_in_class,
'TotalPresentGirls'=> $total_girls_present_in_class,
'TotalAbsentBoys'=> $total_boys_absent_in_class,
'TotalAbsentGirls'=> $total_girls_absent_in_class
]
,
'TotalStudents'=> $total_students_in_school,
'TotalPresentStudents'=> $total_present_students,
'TotalAbsentStudents'=> $total_absent_students,
'TotalPresentBoys'=> $total_boys_present,
'TotalPresentGirls'=> $total_girls_present,
'TotalAbsentBoys'=> $total_boys_absent,
'TotalAbsentGirls'=> $total_girls_absent
]);
}
and this gives me this JSON body below which is just one grade value data
{
"message": "Attendance Report in School",
"Standard Seven": {
"TotalStudents": 0,
"TotalPresentBoys": 0,
"TotalPresentGirls": 0,
"TotalAbsentBoys": 0,
"TotalAbsentGirls": 0
},
"TotalStudents": 1,
"TotalPresentStudents": 0,
"TotalAbsentStudents": 1,
"TotalPresentBoys": 0,
"TotalPresentGirls": 0,
"TotalAbsentBoys": 1,
"TotalAbsentGirls": 0
}
but I want it to be able to display all grades instead of the last grades in the loop as this below
{
"message": "attendance report in school",
"Standard One":
{
"total_students": 10,
"total_present_students": 10,
"total_boys_present": 5,
"total_girls_present": 5,
"total_absent_students": 5,
"total_girls_absent": 5,
"total_boys_absent": 5
},
"Standard Three":
{
"total_students": 10,
"total_present_students": 10,
"total_boys_present": 5,
"total_girls_present": 5,
"total_absent_students": 5,
"total_girls_absent": 5,
"total_boys_absent": 5
},
"Standard Four":
{
"total_students": 10,
"total_present_students": 10,
"total_boys_present": 5,
"total_girls_present": 5,
"total_absent_students": 5,
"total_girls_absent": 5,
"total_boys_absent": 5
},
"Standard Two":
{
"total_students": 10,
"total_present_students": 10,
"total_boys_present": 5,
"total_girls_present": 5,
"total_absent_students": 5,
"total_girls_absent": 5,
"total_boys_absent": 5
},
"total students in a school": 200,
"total number of present in a school": 100,
"total number of absent in a school": 22,
"total number of present boys in a school": 22,
"total number of present girls in a school": 22
}
CodePudding user response:
public function TODreport($school_id, $date)
{
$school = School::where('id', $school_id)->first();
$school_education_level = $school->educationLevel;
$result =['message' => 'Attendance Report in School'];
$levels = [
'Standard One','Standard Two', 'Standard Three','Standard Four',
'Standard Five', 'Standard Six', 'Standard Seven',
];
if($school_education_level == 'Secondary') {
$levels = ['Form One', 'Form Two', 'Form Three', 'Form Four'];
}
foreach($levels as $level) {
$result[$level] = [];
$result[$level]['total_students'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('grade', $level )
->count();
$result[$level]['total_boys_present_in_class'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'male');
})
->count();
$result[$level]['total_girls_present_in_class'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'female');
})
->count();
$result[$level]['total_boys_absent_in_class'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'male');
})
->count();
$result[$level]['total_girls_absent_in_class'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->where('grade', $level )
->whereHas('student' , function($query){
return $query->where('gender', 'female');
})
->count();
}
//-------total number of students called in attendance in that date-------//
$result['attendanceschool_fetched'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->get();
$result['total_students_in_school'] = $result['attendanceschool_fetched']->count();
//-------total number of students called in attendance in that date-------//
//--------total present students fetched in that date-------------------------------//
$result['attendance_fetched_present'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->get();
$result['total_present_students'] = $result['attendance_fetched_present']->count();
//--------total present students fetched in that date-------------------------------//
//---------total present boys-----------------------------------------------------//
$result['total_boys_present'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->whereHas('student' , function($query) {
return $query->where('gender', 'male');
})
->count();
//---------total present girls-----------------------------------------------------//
$result['total_girls_present'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "1")
->whereHas('student' , function($query) {
return $query->where('gender', 'female');
})
->count();
//---------------total absent students in a school-------------------------------//
$result['attendance_fetched_absent'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->get();
$result['total_absent_students'] = $result['attendance_fetched_absent']->count();
//------------total absent boys in a school---------------------------------//
$result['total_boys_absent'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->whereHas('student' , function($query) {
return $query->where('gender', 'male');
})
->count();
//-----------------total absent girls in a school ---------------------------//
$result['total_girls_absent'] = AttendanceStudent::where('created_at', 'LIKE', $date.'%')
->where('school_id', $school_id)
->where('attendance_id' , "2")
->whereHas('student' , function($query) {
return $query->where('gender', 'female');
})
->count();
return response()->json($result);
}
The number of queries can be reduced by using grouping. and this method is better split into several, because it is very difficult to read such code