Get Positions(Rank) for Pupils in Report App

Posted 8 months 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:

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)


         $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.

Please sign in or create an account to participate in this conversation.