Ayomaid08
1 year ago

# Get Positions(Rank) for Pupils in Report App

Posted 1 year ago by Ayomaid08

I am building a report card app. My view for pupil's report sheet is like so:

---------.-------------------------------------------------------------------------------------------------------- SUBJECTS| CA 1| CA2| CA AVG |TOTAL PERCENT| SUBJECT POSITION

Math | 75 |90 | 84.00 | 86.50 | English | 45 |78 | 61.50 | 66.75| Biology | 34 |89 | 61.50 | 74.25|

Check here for clearer view pls: https://i.stack.imgur.com/qhgsP.png

In my results table in DB, I have columns: student_id, studentClass, CA1, CA2, Examination and other essentials. I do not a total percentage, CA Average nor Overall Percentage column. The Overall Percentage is gotten by Total Percentage/Number of Subjects (in the above example : 75.83). The CA Average and Total Percentage are calculated on the fly using query builder as you will find in my controller below.

What I need is to calculate

1. Positions in class e.g Elementary 1A. Each pupil is assigned a position based on Overall Percentage. like 1st, 2nd, 3rd etc. And of course if there's a tie, the next position is skipped.
2. Positions across arms e.g Eementary can have Elementary 1A, 1B, 1C etc. I need to assign positions to pupils by overall percentage in comparison to all pupils in Elementary(1A, 1B, 1C etc.).
3. Subject Positions: This is calculated using Total Percentage scored by a pupil in each subject, in comparison with other pupils in same class, e.g Elementary 1A. It's the last column in my view above. I could find a fix for it either.

I have searched for solutions.What I have seen so far were solutions where the Position(rank) is calculated using a column on the DB table. In my case I do not have such column, as I used QB to calculate on the fly. I did try to implement that method by calculating at least CA Average and Total Percentage during insert but got 'unsupported operand' error.

My Controller:

,,, public function studentresultsheet(Request \$request){

``````        \$stclass = \$request->get('stclass');
\$myclass = Stclass::find(\$request->get('stclass'))->name;
\$studentId = \$request->get('studentId');
\$studentname = Student::find(\$request->get('studentId'))->surname;

\$session= \$request->get('sessionnow');
\$term = \$request->get('term');

\$myids = Stclass::find(\$stclass)->students;

// \$dodo = DB::table('results')
//           -> where('studentClass', \$stclass)->where('session', \$session)->where('term', \$term)
//           ->selectRaw('results.studentId')->distinct()->get();

\$resultpercent = DB::table('results')
-> where('studentClass', \$stclass)->where('session', \$session)->where('term', \$term)->where('studentId', \$studentId)
->selectRaw("SUM((results.exam + (results.test1 + results.test2)/2)/2)/(COUNT(results.subject)) as overallpercent")->get();

\$dodo = DB::table('results')
-> where('studentClass', \$stclass)->where('session', \$session)->where('term', \$term)
->selectRaw('results.studentId')->get();

\$results = DB::table('results')
->where('studentClass', \$stclass)->where('session', \$session)->where('term', \$term)->where('studentId', \$studentId)
->selectRaw('results.*, (results.test1 + results.test2)/2 as cav, (results.exam + (results.test1 + results.test2)/2)/2 as totalmark')->get();

return view('admin.results.studentresultsheet', compact('results', 'myclass', 'studentname','resultpercent', 'countsubjects'));
``````

}

''' Thanks.