Jump to content

I want my project result output to be like the attached image


Go to solution Solved by Olumide,

Recommended Posts

Hi,

Please I need help, I am working on an elementary result platform and everything has been done by my mentor but I want the result output to be like the image attached. The only difficulties I am having is the position, the functions and the sql code to generate the position has been written by my boss but it was written in another script.

I want to add the script to my results script so that I can have the output attached. Please kindly help as it has been given a sleepless night.

image.png.befe1a866fe3037aa72a358e954b5995.pngimage.png.befe1a866fe3037aa72a358e954b5995.png

 

Here is the script for the result which I would like to integrate the ordinal functions into:

 $semester = $_GET['semesterid'] ?? 0;
    
    $res = $pdo->prepare("SELECT ss.sessionname
                             , ss.id as sessionid
                             , sm.semestername
                             , sm.semestername+0 as termno
                             #, sm.date_until as nextterm   # modified
                             , sm.id as smid
                        FROM session ss
                             JOIN semester sm ON sm.sessionid = ss.id
                        WHERE sm.id = ?     
                        ");
    $res->execute([$semester]);
    $row = $res->fetch();

//    $sessionname = $row['sessionname'] ?? '';
    $session = $row['sessionid'] ?? 0;
//    $semestername = $row['semestername'] ?? '';
    $termno = $row['termno'] ?? 0;
    # $nextterm = $row['nextterm'] ?? 0;   # modified
    
    if ($clid == -1) {
        $res = $pdo->prepare("SELECT classid
                              FROM student_class
                              WHERE studentid = ?
                                AND semesterid = ?
                             ");
        $res->execute([$student, $semester]);
        $clid = $res->fetchColumn();
    }
    
    switch($termno) {
        case 1: $term_headings = "<th>1st<br>Term<br>100</th>
                                  <!--<th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>
                                  <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>-->";
                                  
                                  break;
        case 2: $term_headings = "<th>1st<br>Term<br>&nbsp;</th>
                                  <th>2nd<br>Term<br>100</th>
                                  <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>";
                                 
                                  break;
        default: $term_headings = "<th>1st<br>Term<br>&nbsp;</th>
                                  <th>2nd<br>Term<br>&nbsp;</th>
                                  <th>3rd<br>Term<br>100</th>";
                                  
    }
    
    $report_title = $termno == 3 ? "End of Year Results" : "End of Term Results";

   
    ################################################################################
    #  Get scores and put in array with required output structure                  #
    ################################################################################
    $res = $pdo->prepare("SELECT st.id as stid
                                 , concat_ws(' ', st.lastname, st.firstname, st.othername) as stname
                                 , st.image
                                 , cl.classname
                                 , st.dob
                                 , st.matricno
                                 , sc.classid
                                 , l.id as level
                                 , sn.sessionname
                                 , sm.semestername
                                 , sm.date_until 
                                 , sm.semestername+0 as term
                                 , c.subjectid
                                 , s.subjectname
                                 , exam
                                 , score
                            FROM result r
                                 JOIN 
                                 (
                                 student_class sc 
                                 JOIN class cl ON sc.classid = cl.id
                                 JOIN level l ON cl.levelid = l.id
                                 JOIN course c ON c.levelid = l.id
                                 JOIN student st ON sc.studentid = st.id
                                 JOIN semester sm ON sc.semesterid = sm.id
                                 JOIN session sn ON sm.sessionid = sn.id
                                 JOIN subject s ON c.subjectid = s.id
                                 ) ON r.studentclassid = sc.id AND r.courseid = c.id
                                 
                            WHERE sn.id = ?
                              AND studentid = ?
                              #AND sm.date_until = ?
                              AND sm.semestername+0 <= ?
                              AND cl.id = ?
                            ORDER BY c.levelid, sc.id, c.subjectid, sc.semesterid, exam
                            ");
    #############################################################################

  
    #############################################################################
    $res->execute( [ $session, $student, $termno, $clid ] );
    $data = [];
    // get data common to all rows from first row
    $r = $res->fetch();
    if ($r) {
        $studentname = $r['stname'];
        $studentdob = $r['dob'];
        $studentmatricno = $r['matricno'];
        $studentlevel = $r['classname'];
        $studentsession = $r['sessionname'];
        $studentterm = "- Term $termno";
        $nextterm = $r['date_until'];
        $passport = "images/" . $r['image'];                                                                      ### provide image path here
        $level = $r['level'];
        // then process the rest of the row data in the first and remaining rows
        do {
            if (!isset($data[ $r['subjectid'] ])) {
                $data[ $r['subjectid'] ] = [ 'name' => $r['subjectname'],
                                             #'exams' => ['CA1'=>'', 'CA2'=>'', 'CA3'=>'', 'Exam'=>''],
                                            'exams' => ['CA1'=>'', 'CA2'=>'', 'Exam'=>''],
                                             'scores'  => [ 1=>0, 0, 0 ],
                                             'avg' => 0
                                             //'rank' => 0 
                                           ];
            }   
            if ($r['term'] == $termno && isset($data[$r['subjectid'] ]['exams'][ $r['exam']])) {
                $data[ $r['subjectid'] ]['exams'][ $r['exam'] ] = $r['score'];
            }
            $data[ $r['subjectid'] ]['scores'][$r['term']] += $r['score'];
        } while ($r = $res->fetch());
    // get the avg scores for the class
        $avgs = classAverageScores($pdo, $clid, $session, $termno);
        foreach ($avgs as $s => $av) {
            if (isset($data[$s]))
                $data[$s]['avg'] = round($av,0);
        } 

        ###########my ranking here###############
        /***** manipulating here **************
         * 
         * 
         * 
         * /
         * */
   /********************************************
  * Derive ordinal suffix for $n
  * 
  * @param int $n     the number
  * @returns string   number with suffix eg 23rd   
  */
  function ordinal($n) 
  {
      $str = "$n";
      $t = $n > 9 ? substr($str,-2,1) : 0;
      $u = substr($str,-1);
      if ($t==1) 
        return $str . 'th';
      else 
          switch ($u) 
          {
              case 1: return $str . 'st';
              case 2: return $str . 'nd';
              case 3: return $str . 'rd';
              default: return $str . 'th';
          }
  }

  //function end
//$data = [];
  foreach ($res as $r) {
    if (!isset($data[$r['subjectname']])) {
        $data[$r['subjectname']]['students'] = [];
    }
    
    $position = ordinal($r['rank']);
    $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ];
}


    /*    //hffffffffff
     
        foreach ($res as $r) {
    if (!isset($data[$r['subjectname']])) {
        $data[$r['subjectname']]['students'] = [];
    }
    
    $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $r['rank'] ];
}  */
    ################################################################################
    #  Get pupil count                                                             #
    ################################################################################
    $res = $pdo->prepare("SELECT COUNT(DISTINCT stc.studentid) AS pupils
                                FROM student_class stc 
                                     JOIN semester sm ON sm.id = stc.semesterid
                                     JOIN result r ON stc.id = r.studentclassid
                                WHERE sm.id = ?
                                  AND stc.classid = ?
                        ");
    $res->execute([ $semester, $clid ]);
    $pupil_count = $res->fetchColumn();    
            
    ################################################################################
    #  Loop through the data array to construct the output table rows              #
    ################################################################################
    


        $tdata = '';
        $n = 1;
        $grand_total = 0;
        $subject_count = 0;
        foreach ($data as $subid => $subdata) {
            $tdata .= "<tr><td>$n</td><td>{$subdata['name']}</td>";
            foreach ($subdata['exams'] as $s) {
                $tdata .= "<td>" . ($s=='' ? '&ndash;' : $s) . "</td>";
            }
            foreach ($subdata['scores'] as $t => $s) {
                if ($s==0) $s = '';
                $tdata .= "<td>" . ($t <= $termno ? $s : '') . "</td>";
            }
            $temp = array_filter($subdata['scores']);
            $total = $temp ? round(array_sum($temp)/count($temp)) : 0;
            $grand_total += $total;
            if ($total) {
                list($grade, $comment) = getGradeComment($pdo, $total, $level);
                $subject_count++;
            }
            else {
                $grade = '-';
                $comment = '-';
            }
            $clr = GRADE_COLOUR[$grade] ?? '#000';
            $tdata .= "<td>$total</td><td>{$subdata['avg']}</td><td style='color:$clr; font-weight: 600;'>$grade</td><td>$comment</td></tr>\n";
            ++$n;
        }
    }
    else {
        $studentname = '';
        $studentdob = '';
        $studentmatricno = '';
        $studentlevel = '';
        $studentsession = '';
        $studentsemester = '';
        #$nextterm = '';
        $studentterm = '';
        $passport = '';
        $level = '4';
        $pupil_count = 0;
        $grand_total = 0;
        $subject_count = 1;
//      $clid = 0;
        $tdata = "<tr><td colspan='13'>No results found</td></tr>\n";
    }

    ################################################################################
    #  Get list of gradings                                                        #
    ################################################################################
    $res = $pdo->query("SELECT GROUP_CONCAT( grade, concat('&nbsp;(',comments,')'), '&nbsp;', concat(lomark,'&nbsp;-&nbsp;',himark)
                               ORDER BY id SEPARATOR ', ')
                        FROM examgrade
                        #WHERE level_group = ($level > 5)
                         WHERE level_group = ($level > 6)
                        ");
    $grade_list = $res->fetchColumn();

    #################################

    $res = $pdo->query('SELECT MIN(date_format(date_from, "%W %M %e, %Y")) as next_term

        FROM semester

            WHERE date_from > CURDATE()');
    $nextterm = $res->fetchColumn();
    #################################

################################################################################
#  Get end of term assessments                                                 #
################################################################################
$res = $pdo->prepare("SELECT    a.type
                              , a.assessname
                              , e.grade
                        FROM student_class stc
                               JOIN eot_assessment e ON e.studentclassid = stc.id
                               JOIN assessment a ON e.assessmentid = a.id 
                               #JOIN semester sm ON sm.id = stc.date_untilid 
                               JOIN semester sm ON sm.id = stc.semesterid
                        WHERE stc.studentid = ? 
                            AND sm.id = ? 
                        ");
$res->execute( [ $student, $semester ] );
$ass_data = $res->fetchAll(PDO::FETCH_GROUP);
$afflist = $psychlist = '';

if ($ass_data) {
    
    $afflist = "<table class='w3-table assess-tbl' >
                <tr><th>Domain</th><th>Grade</th></tr>\n";
    foreach ($ass_data['Affective'] as $agrades) {
        $afflist .= "<tr><td>{$agrades['assessname']}</td><td>{$agrades['grade']}</td></tr>\n";
    }
    $afflist .= "</table>\n";
    
    $psychlist = "<table class='w3-table assess-tbl' >
                  <tr><th>Domain</th><th>Grade</th></tr>\n";
    foreach ($ass_data['Psychomotor'] as $pgrades) {
        $psychlist .= "<tr><td>{$pgrades['assessname']}</td><td>{$pgrades['grade']}</td></tr>\n";
    }
    $psychlist .= "</table>\n";
}

################################################################################
#  Get end of term comments                                                 #
################################################################################

$comments = getEOTComments($pdo, $student, $semester);

?>

 

Here is the code for the ordinal suffix to generate position which I was unable to manipulate into the above script

switch ($midend) {
    case 'M': $res = $pdo->prepare("SELECT subjectname
                                         , stname
                                         , score
                                         , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
                                         , @rank := IF(score = @prev, @rank, @seq) as rank
                                         , @prev := score as prev
                                         , @prevs := subjectname as prevs
                                    FROM (
                                            SELECT cl.classname
                                                 , sb.subjectname
                                                 , concat(st.firstname, ' ', st.lastname) as stname
                                                 , round( sum( case exam when 'Exam' then score/70*100
                                                                         else score*10
                                                                         end 
                                                              ) / COUNT(distinct exam) ) as score
                                            FROM student_class stc 
                                                 JOIN student st ON stc.studentid = st.id
                                                 JOIN class cl ON stc.classid = cl.id
                                                 JOIN level l ON cl.levelid = l.id
                                                 JOIN course c ON l.id = c.levelid
                                                 JOIN subject sb ON c.subjectid = sb.id
                                                 JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
                                            WHERE stc.semesterid = ?
                                                    AND exam = 'CA1'
                                                    AND cl.id = ?
                                            GROUP BY c.id, sb.id, st.id
                                            ORDER BY subjectname, score DESC
                                            LIMIT 9223372036854775807        -- MariaDB bug workaround
                                         ) ordered
                                         JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
                                    ");
              $res->execute([ $semester, $class ]);
              $chkm = 'checked';
              $chke = $chky = '';
              break;
    case 'E':  $res = $pdo->prepare("SELECT subjectname
                                         , stname
                                         , score
                                         , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
                                         , @rank := IF(score = @prev, @rank, @seq) as rank
                                         , @prev := score as prev
                                         , @prevs := subjectname as prevs
                                    FROM (
                                            SELECT   sb.subjectname
                                                 , concat(st.firstname, ' ', st.lastname) as stname
                                                 , round( sum( score )) as score
                                            FROM student_class stc 
                                                 JOIN student st ON stc.studentid = st.id
                                                 JOIN class cl ON stc.classid = cl.id
                                                 JOIN level l ON cl.levelid = l.id
                                                 JOIN course c ON l.id = c.levelid
                                                 JOIN subject sb ON c.subjectid = sb.id
                                                 JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
                                            WHERE stc.semesterid = ?
                                                    AND cl.id = ?
                                            GROUP BY c.id, sb.id, st.id
                                            ORDER BY subjectname, score DESC
                                            LIMIT 9223372036854775807        -- MariaDB bug workaround
                                         ) ordered 
                                         JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
                                    ");
              $res->execute([ $semester, $class ]);
              $chke = 'checked';
              $chkm = $chky = '';
              break;       
    default:  $res = $pdo->prepare("SELECT subjectname
                                         , stname
                                         , score
                                         , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
                                         , @rank := IF(score = @prev, @rank, @seq) as rank
                                         , @prev := score as prev
                                         , @prevs := subjectname as prevs
                                    FROM (
                                            SELECT   sb.subjectname
                                                 , concat(st.firstname, ' ', st.lastname) as stname
                                                 , round( sum( score )/count(distinct sm.id)) as score
                                            FROM student_class stc 
                                                 JOIN student st ON stc.studentid = st.id
                                                 JOIN class cl ON stc.classid = cl.id
                                                 JOIN level l ON cl.levelid = l.id
                                                 JOIN course c ON l.id = c.levelid
                                                 JOIN subject sb ON c.subjectid = sb.id
                                                 JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
                                                 JOIN semester sm ON stc.semesterid = sm.id
                                                 JOIN session sn ON sm.sessionid = sn.id
                                            WHERE sm.sessionid = ?
                                                    AND cl.id = ?
                                            GROUP BY cl.id, sb.id, st.id
                                            ORDER BY subjectname, score DESC
                                            LIMIT 9223372036854775807        -- MariaDB bug workaround
                                         ) ordered 
                                         JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
                                    ORDER BY subjectname, score DESC     
                                    ");
              $res->execute([ $session, $class ]);
              $chky = 'checked';
              $chkm = $chke = '';
              break;       
}
/*function getOrdinalSuffix($score) {
    $score = abs($score) % 100;
    $lastChar = substr($score, -1, 1);
    switch ($lastChar) {
        case '1' : return ($score == '11') ? 'th' : 'st';
        case '2' : return ($score == '12') ? 'th' : 'nd';
        case '3' : return ($score == '13') ? 'th' : 'rd'; 
    }
    return 'th';  
}
$list = ''; 
for ($score = 1; $score < 150; $score++) { 
    $list .= "$score" . getOrdinalSuffix($score) . "\n"; 
}
///print "$list";*/
/********************************************
  * Derive ordinal suffix for $n
  * 
  * @param int $n     the number
  * @returns string   number with suffix eg 23rd   
  */
  /*
  function ordinal($n) 
  {
      $str = "$n";
      $t = $n > 9 ? substr($str,-2,1) : 0;
      $u = substr($str,-1);
      if ($t==1) 
        return $str . 'th';
      else 
          switch ($u) 
          {
              case 1: return $str . 'st';
              case 2: return $str . 'nd';
              case 3: return $str . 'rd';
              default: return $str . 'th';
          }
  }*/
  // calling function here
/********************************************
  * Derive ordinal suffix for $n
  * 
  * @param int $n     the number
  * @returns string   number with suffix eg 23rd   
  */
  function ordinal($n) 
  {
      $str = "$n";
      $t = $n > 9 ? substr($str,-2,1) : 0;
      $u = substr($str,-1);
      if ($t==1) 
        return $str . 'th';
      else 
          switch ($u) 
          {
              case 1: return $str . 'st';
              case 2: return $str . 'nd';
              case 3: return $str . 'rd';
              default: return $str . 'th';
          }
  }

  //function end
$data = [];
  foreach ($res as $r) {
    if (!isset($data[$r['subjectname']])) {
        $data[$r['subjectname']]['students'] = [];
    }
    
    $position = ordinal($r['rank']);
    $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ];
}

 

9 minutes ago, ginerjm said:

How about a SMALL sample of what it looks like now?

Attached is the sample of the output of the result which I could not generate the position tagged as "sampleA" while sample is the output of the script with the ordinal suffix which  I would like to copy the script into the result to have the position 1st, 2nd, 3rd, ... in the sampleA.

 

sampleA.png

sampleB.png

  • Solution
3 minutes ago, ginerjm said:

I asked for a sample of what you are currently doing to see how you want it to be altered.  Please make your statements more understandable though.  Your last made no sense at all.

 error_reporting(E_ALL);

        ini_set('display_errors', '1'); 

$semester = $_GET['semesterid'] ?? 0;
    
    $res = $pdo->prepare("SELECT ss.sessionname
                             , ss.id as sessionid
                             , sm.semestername
                             , sm.semestername+0 as termno
                             #, sm.date_until as nextterm   # modified
                             , sm.id as smid
                        FROM session ss
                             JOIN semester sm ON sm.sessionid = ss.id
                        WHERE sm.id = ?     
                        ");
    $res->execute([$semester]);
    $row = $res->fetch();

//    $sessionname = $row['sessionname'] ?? '';
    $session = $row['sessionid'] ?? 0;
//    $semestername = $row['semestername'] ?? '';
    $termno = $row['termno'] ?? 0;
    # $nextterm = $row['nextterm'] ?? 0;   # modified
    
    if ($clid == -1) {
        $res = $pdo->prepare("SELECT classid
                              FROM student_class
                              WHERE studentid = ?
                                AND semesterid = ?
                             ");
        $res->execute([$student, $semester]);
        $clid = $res->fetchColumn();
    }
    
    switch($termno) {
        case 1: $term_headings = "<th>1st<br>Term<br>100</th>
                                  <!--<th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>
                                  <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>-->";
                                  
                                  break;
        case 2: $term_headings = "<th>1st<br>Term<br>&nbsp;</th>
                                  <th>2nd<br>Term<br>100</th>
                                  <th>&ensp;&ensp;&ensp;&ensp;&ensp;</th>";
                                 
                                  break;
        default: $term_headings = "<th>1st<br>Term<br>&nbsp;</th>
                                  <th>2nd<br>Term<br>&nbsp;</th>
                                  <th>3rd<br>Term<br>100</th>";
                                  
    }
    
    $report_title = $termno == 3 ? "End of Year Results" : "End of Term Results";

   
    ################################################################################
    #  Get scores and put in array with required output structure                  #
    ################################################################################
    $res = $pdo->prepare("SELECT st.id as stid
                                 , concat_ws(' ', st.lastname, st.firstname, st.othername) as stname
                                 , st.image
                                 , cl.classname
                                 , st.dob
                                 , st.matricno
                                 , sc.classid
                                 , l.id as level
                                 , sn.sessionname
                                 , sm.semestername
                                 , sm.date_until 
                                 , sm.semestername+0 as term
                                 , c.subjectid
                                 , s.subjectname
                                 , exam
                                 , score
                            FROM result r
                                 JOIN 
                                 (
                                 student_class sc 
                                 JOIN class cl ON sc.classid = cl.id
                                 JOIN level l ON cl.levelid = l.id
                                 JOIN course c ON c.levelid = l.id
                                 JOIN student st ON sc.studentid = st.id
                                 JOIN semester sm ON sc.semesterid = sm.id
                                 JOIN session sn ON sm.sessionid = sn.id
                                 JOIN subject s ON c.subjectid = s.id
                                 ) ON r.studentclassid = sc.id AND r.courseid = c.id
                                 
                            WHERE sn.id = ?
                              AND studentid = ?
                              #AND sm.date_until = ?
                              AND sm.semestername+0 <= ?
                              AND cl.id = ?
                            ORDER BY c.levelid, sc.id, c.subjectid, sc.semesterid, exam
                            ");
    #############################################################################

  
    #############################################################################
    $res->execute( [ $session, $student, $termno, $clid ] );
    $data = [];
    // get data common to all rows from first row
    $r = $res->fetch();
    if ($r) {
        $studentname = $r['stname'];
        $studentdob = $r['dob'];
        $studentmatricno = $r['matricno'];
        $studentlevel = $r['classname'];
        $studentsession = $r['sessionname'];
        $studentterm = "- Term $termno";
        $nextterm = $r['date_until'];
        $passport = "images/" . $r['image'];                                                                      ### provide image path here
        $level = $r['level'];
        // then process the rest of the row data in the first and remaining rows
        do {
            if (!isset($data[ $r['subjectid'] ])) {
                $data[ $r['subjectid'] ] = [ 'name' => $r['subjectname'],
                                             #'exams' => ['CA1'=>'', 'CA2'=>'', 'CA3'=>'', 'Exam'=>''],
                                            'exams' => ['CA1'=>'', 'CA2'=>'', 'Exam'=>''],
                                             'scores'  => [ 1=>0, 0, 0 ],
                                             'avg' => 0
                                             //'rank' => 0 
                                           ];
            }   
            if ($r['term'] == $termno && isset($data[$r['subjectid'] ]['exams'][ $r['exam']])) {
                $data[ $r['subjectid'] ]['exams'][ $r['exam'] ] = $r['score'];
            }
            $data[ $r['subjectid'] ]['scores'][$r['term']] += $r['score'];
        } while ($r = $res->fetch());
    // get the avg scores for the class
        $avgs = classAverageScores($pdo, $clid, $session, $termno);
        foreach ($avgs as $s => $av) {
            if (isset($data[$s]))
                $data[$s]['avg'] = round($av,0);
        } 

        ###########my ranking here###############
        /***** manipulating here **************
         * 
         * 
         * 
         * /
         * */
   /********************************************
  * Derive ordinal suffix for $n
  * 
  * @param int $n     the number
  * @returns string   number with suffix eg 23rd   
  */
  function ordinal($n) 
  {
      $str = "$n";
      $t = $n > 9 ? substr($str,-2,1) : 0;
      $u = substr($str,-1);
      if ($t==1) 
        return $str . 'th';
      else 
          switch ($u) 
          {
              case 1: return $str . 'st';
              case 2: return $str . 'nd';
              case 3: return $str . 'rd';
              default: return $str . 'th';
          }
  }

  //function end
//$data = [];
  foreach ($res as $r) {
    if (!isset($data[$r['subjectname']])) {
        $data[$r['subjectname']]['students'] = [];
    }
    
    $position = ordinal($r['rank']);
    $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ];
}


    /*    //hffffffffff
     
        foreach ($res as $r) {
    if (!isset($data[$r['subjectname']])) {
        $data[$r['subjectname']]['students'] = [];
    }
    
    $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $r['rank'] ];
}  */
    ################################################################################
    #  Get pupil count                                                             #
    ################################################################################
    $res = $pdo->prepare("SELECT COUNT(DISTINCT stc.studentid) AS pupils
                                FROM student_class stc 
                                     JOIN semester sm ON sm.id = stc.semesterid
                                     JOIN result r ON stc.id = r.studentclassid
                                WHERE sm.id = ?
                                  AND stc.classid = ?
                        ");
    $res->execute([ $semester, $clid ]);
    $pupil_count = $res->fetchColumn();    
            
    ################################################################################
    #  Loop through the data array to construct the output table rows              #
    ################################################################################
    


        $tdata = '';
        $n = 1;
        $grand_total = 0;
        $subject_count = 0;
        foreach ($data as $subid => $subdata) {
            $tdata .= "<tr><td>$n</td><td>{$subdata['name']}</td>";
            foreach ($subdata['exams'] as $s) {
                $tdata .= "<td>" . ($s=='' ? '&ndash;' : $s) . "</td>";
            }
            foreach ($subdata['scores'] as $t => $s) {
                if ($s==0) $s = '';
                $tdata .= "<td>" . ($t <= $termno ? $s : '') . "</td>";
            }
            $temp = array_filter($subdata['scores']);
            $total = $temp ? round(array_sum($temp)/count($temp)) : 0;
            $grand_total += $total;
            if ($total) {
                list($grade, $comment) = getGradeComment($pdo, $total, $level);
                $subject_count++;
            }
            else {
                $grade = '-';
                $comment = '-';
            }
            $clr = GRADE_COLOUR[$grade] ?? '#000';
            $tdata .= "<td>$total</td><td>{$subdata['avg']}</td><td style='color:$clr; font-weight: 600;'>$grade</td><td>$comment</td></tr>\n";
            ++$n;
        }
    }
    else {
        $studentname = '';
        $studentdob = '';
        $studentmatricno = '';
        $studentlevel = '';
        $studentsession = '';
        $studentsemester = '';
        #$nextterm = '';
        $studentterm = '';
        $passport = '';
        $level = '4';
        $pupil_count = 0;
        $grand_total = 0;
        $subject_count = 1;
//      $clid = 0;
        $tdata = "<tr><td colspan='13'>No results found</td></tr>\n";
    }

    ################################################################################
    #  Get list of gradings                                                        #
    ################################################################################
    $res = $pdo->query("SELECT GROUP_CONCAT( grade, concat('&nbsp;(',comments,')'), '&nbsp;', concat(lomark,'&nbsp;-&nbsp;',himark)
                               ORDER BY id SEPARATOR ', ')
                        FROM examgrade
                        #WHERE level_group = ($level > 5)
                         WHERE level_group = ($level > 6)
                        ");
    $grade_list = $res->fetchColumn();

    #################################

    $res = $pdo->query('SELECT MIN(date_format(date_from, "%W %M %e, %Y")) as next_term

        FROM semester

            WHERE date_from > CURDATE()');
    $nextterm = $res->fetchColumn();
    #################################

################################################################################
#  Get end of term assessments                                                 #
################################################################################
$res = $pdo->prepare("SELECT    a.type
                              , a.assessname
                              , e.grade
                        FROM student_class stc
                               JOIN eot_assessment e ON e.studentclassid = stc.id
                               JOIN assessment a ON e.assessmentid = a.id 
                               #JOIN semester sm ON sm.id = stc.date_untilid 
                               JOIN semester sm ON sm.id = stc.semesterid
                        WHERE stc.studentid = ? 
                            AND sm.id = ? 
                        ");
$res->execute( [ $student, $semester ] );
$ass_data = $res->fetchAll(PDO::FETCH_GROUP);
$afflist = $psychlist = '';

if ($ass_data) {
    
    $afflist = "<table class='w3-table assess-tbl' >
                <tr><th>Domain</th><th>Grade</th></tr>\n";
    foreach ($ass_data['Affective'] as $agrades) {
        $afflist .= "<tr><td>{$agrades['assessname']}</td><td>{$agrades['grade']}</td></tr>\n";
    }
    $afflist .= "</table>\n";
    
    $psychlist = "<table class='w3-table assess-tbl' >
                  <tr><th>Domain</th><th>Grade</th></tr>\n";
    foreach ($ass_data['Psychomotor'] as $pgrades) {
        $psychlist .= "<tr><td>{$pgrades['assessname']}</td><td>{$pgrades['grade']}</td></tr>\n";
    }
    $psychlist .= "</table>\n";
}

################################################################################
#  Get end of term comments                                                 #
################################################################################

$comments = getEOTComments($pdo, $student, $semester);

?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>End Term Results</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" href="assets/css/w3.css">
<!--<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> -->
<script src="assets/js/jquery.js"></script>
<!--<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>-->
<script type='text/javascript'>
    $().ready( function() {
    })
</script>
<style type='text/css'>
   
#student-data   {
    width: 100%;
}
#student-data td { background:#eee;
 }

    #result-tbl {
        width: 100%;

    }
    #result-tbl tr:nth-child(2n) {
        background-color: #eee;
       
    }
    #result-tbl th {
        text-align: center;
        padding: 4px;
    }
    #result-tbl th:nth-child(2),
    #result-tbl th:nth-child(13) {
        text-align: left;
    }
    #result-tbl td {
        text-align: center;
        padding: 8px 2px;
        line-height: 0.5em;
    }
    #result-tbl td:nth-child(2),
    #result-tbl td:nth-child(13) {
        text-align: left;
    }
    #result-tbl td:nth-child(12) {
        padding-left: 25px;
        text-align: left;
    } 
    #result-tbl td:nth-child(1),
    #result-tbl td:nth-child(6),
    #result-tbl td:nth-child(9) {
        border-right: 1px solid gray;
        
    }
    
    .assess-tbl th {
        border-top: 1px solid gray;
        border-bottom: 1px solid gray;
    }
    #address {
        font-family: times ;
        font-size: 20px;
        line-height: 1.0em;
    }
    #bgd {
        width : 100vw;
        height: 100vh;
        z-index: -5;
        position: fixed;
        top: 0;
        left: 0;
        background-image: url("logo1.png"); 
        opacity: 0.2;
        background-repeat: no-repeat;
        background-attachment: fixed;
        background-position: center;
        background-size: 600px 600px;
    }
    .summaryhead {
        width: 65%;
        text-align: center;
        border: 1px solid blue;
    }
    @media print {
        .noprint {
            visibility: hidden;
        }
    }
</style>
</head>
<body>
    <header class='w3-row'>
        <div class='w3-col m2'><img class='w3-image w3-left w3-padding' src='logo1.png' alt='logo'></div>
        <div class='w3-col m8 w3-padding w3-center' id='address'>
            <strong></strong><br> 
            <small><i></i></small><br>                
                        
            <h2><?=$report_title?></h2>
        </div>
        <div class='w3-col m2'><img class='w3-image w3-right w3-padding' src='<?= $passport ?>' width='160' alt='student photo'></div>
    </header>

    <form class='w3-bar w3-light-gray noprint'>
        <label class='w3-bar-item'>Term</label>
        <select class='w3-bar-item w3-border' name='semesterid' onchange='this.form.submit()'>
            <?= semesterOptions($pdo, 0, $semester)?>
        </select>
            <?php  if (isset($_SESSION['staff_id']))  {   ?>
                        <label class='w3-bar-item'>Class</label>
                        <select class='w3-bar-item w3-border' name='classid' id='classid' onchange='this.form.submit()'>
                            <?= classOptions($pdo, $session, $staff, $clid)?>
                        </select>
                        <label class='w3-bar-item'>Student</label>
                        <select class='w3-bar-item w3-border' name='studentid' id='studentid' onchange='this.form.submit()'>
                            <?= studentOptions($pdo, $semester, $clid, $staff, $student)?>
                        </select>
            <?php  }  ?>
        <button class='w3-button w3-bar-item w3-blue-gray w3-right' onclick='window.print()'>Print</button>
    </form>
    <!-- my table here -->

<div class="w3-responsive">

<table border='2' id='student-data'>
  <tr class="bg-dark text-white">
    <td><strong>Name</strong></td>
    <td ><strong><?= $studentname ?></strong></td>
    <td><strong>Class: </strong></td>
    <td><strong><?= $studentlevel ?></strong></td>
    <td><strong>RegNo.: </strong></td>
    <td><strong><?= $studentmatricno ?></strong></td>
    <td><strong>DOB: </strong></td>
    <td><strong><?= $studentdob ?></strong></td>

<!--
    <td><strong>Name: <?= $studentname ?></strong></td>
    <td><strong>Class: <?= $studentlevel ?></strong></td>
    <td><strong>RegNo.: <?= $studentmatricno ?></strong></td>
    <td><strong>DOB: <?= $studentdob ?></strong></td>-->
  </tr>
  <tr>
    
    <td>Session/Term: </td>
    <td><strong><?= $studentsession ?> <?=$studentterm?> </strong></td>
    <td>Total Students in class: </td>
    <td><strong> <?=$pupil_count?></strong></td>
    <td>Score:  </td>
    <td><strong> <?=sprintf('%d/%d', $grand_total, $subject_count*100)?></strong></td>
    
    <td>Percentage: </td>
    <td><strong> <?=round($grand_total/$subject_count, 2).'%'?></strong></td>

    <!--

 <td>Session/Term: <?= $studentsession ?> <?=$studentterm?></td>
    <td>Score:  <?=sprintf('%d/%d', $grand_total, $subject_count*100)?></td>
    <td>Total Students in class: <?=$pupil_count?></td>
    <td>Percentage: <?=round($grand_total/$subject_count, 2).'%'?></td>
    -->
  </tr>
  <tr>
<td>Number of Time School Open</td>
<td>  <?= $comments['school_open'] ?>  </td>
<td>Number of Time Present</td>
<td>  <?= $comments['student_attendance'] ?>  </td>
  </tr>
</table>

</div>

    <!-- my table end here -->
   <!-- <div id='bgd'>&nbsp;</div>
    <div class='w3-container w3-padding ' id='wrapper'>
        <div class='w3-row'>
            <div class='w3-col'>
                <b>Name: <?= $studentname ?></b>
            </div>
        </div> 
        

        <div class='w3-row'>
            <div class='w3-col'>
                <b>DOB: <?= $studentdob ?></b>
            </div>
        </div> 
        <div class='w3-row'>
            <div class='w3-col'>
                <b>RegNo.: <?= $studentmatricno ?></b>
            </div>
        </div>     
        
        <div class='w3-row'>
            <div class='w3-col w3-third'>
                Class: <?= $studentlevel ?><br>
                Session: <?= $studentsession ?> <?=$studentterm?><br>
            </div>
            
           <div class='w3-col w3-quarter w3-center'>
                <div class='w3-panel w3-blue summaryhead' >Pupils in class</div>
                <div class='w3-panel w3-xlarge summaryhead'>
                    <?=$pupil_count?>
                </div>
            </div>
           
            <div class='w3-col w3-third'>
                <div class='w3-panel w3-blue summaryhead' >Percentage</div>
                <div class='w3-panel w3-xlarge summaryhead'>
                    <?=round($grand_total/$subject_count, 2).'%'?>
                </div>
            </div>
            
            <div class='w3-col w3-third w3-center'>
                <div class='w3-panel w3-blue summaryhead' >Score</div>
                <div class='w3-panel w3-xlarge summaryhead'>
                    <?=sprintf('%d/%d', $grand_total, $subject_count*100)?>
                </div>
            </div>
        
        </div>-->
        <div class='w3-responsive'>
        <table border='0'  id='result-tbl'>
            <tr class='w3-border-bottom w3-dark-gray'>
                <th>&nbsp;</th>
                <th>Subject</th>
                <th>CA 1<br>&nbsp;<br>20</th>
                <th>CA 2<br>&nbsp;<br>20</th>
                <!--<th>CA 3<br>&nbsp;<br>10</th>-->
                <th>Exam<br>&nbsp;<br>60</th>
                <?=$term_headings?>
            <!--    <th>1st<br>Term<br>&nbsp;</th>
                <th>2nd<br>Term<br>&nbsp;</th>
                <th>3rd<br>Term<br>100</th>        -->
                <th>Total</th>
                <th>Class<br>Avg</th>
                <th>Position</th>
                <th>Grade</th>
                <th>Comment</th>
            </tr>
            <?= $tdata ?>
        </table>
        </div>
        <div class='w3-panel w3-padding w3-small'>
             <b>Grades: </b><i><?= $grade_list ?></i>
        </div>
        <h4><b>Assessments</b></h4>
        <div class='w3-row-padding w3-small'>
            <div class='w3-col m5 w3-padding'>
                <b>Affective</b><br>
                <?= $afflist ?>
            </div>
            <div class='w3-col m1 w3-padding'>
                &nbsp;
            </div>
            <div class='w3-col m6 w3-padding'>
                <b>Psychomotor</b><br>
                <?= $psychlist ?>
                <br>
                <b>Comments</b><br>
                <div class='w3-container'>
                    <b class='w3-small'>Teacher</b>
                    <div class='w3-padding w3-border' ><?= $comments['teacher'] ?></div >
                    <b class='w3-small'>Head</b>
                    <div class='w3-padding w3-border' ><?= $comments['head'] ?></div >
                    <b class='w3-small'>Next Term Begins on: </b>
                   
                    <?=
        
                    $nextterm?>
                </div>
            </div>
        </div>
    </div>
</body>
</html>

The above code is where I am having issue with. The position column.

Here is the code to generate the position, but I don't know where to insert the code below into the above scripts, I have tried several ways but to no avail

 

 error_reporting(E_ALL);

        ini_set('display_errors', '1'); 


case 'E':  $res = $pdo->prepare("SELECT subjectname
                                         , stname
                                         , score
                                         , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
                                         , @rank := IF(score = @prev, @rank, @seq) as rank
                                         , @prev := score as prev
                                         , @prevs := subjectname as prevs
                                    FROM (
                                            SELECT   sb.subjectname
                                                 , concat(st.firstname, ' ', st.lastname) as stname
                                                 , round( sum( score )) as score
                                            FROM student_class stc 
                                                 JOIN student st ON stc.studentid = st.id
                                                 JOIN class cl ON stc.classid = cl.id
                                                 JOIN level l ON cl.levelid = l.id
                                                 JOIN course c ON l.id = c.levelid
                                                 JOIN subject sb ON c.subjectid = sb.id
                                                 JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
                                            WHERE stc.semesterid = ?
                                                    AND cl.id = ?
                                            GROUP BY c.id, sb.id, st.id
                                            ORDER BY subjectname, score DESC
                                            LIMIT 9223372036854775807        -- MariaDB bug workaround
                                         ) ordered 
                                         JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
                                    ");
              $res->execute([ $semester, $class ]);
              $chke = 'checked';
              $chkm = $chky = '';
              break;       
    default:  $res = $pdo->prepare("SELECT subjectname
                                         , stname
                                         , score
                                         , @seq := IF(subjectname = @prevs, @seq+1, 1) as seq
                                         , @rank := IF(score = @prev, @rank, @seq) as rank
                                         , @prev := score as prev
                                         , @prevs := subjectname as prevs
                                    FROM (
                                            SELECT   sb.subjectname
                                                 , concat(st.firstname, ' ', st.lastname) as stname
                                                 , round( sum( score )/count(distinct sm.id)) as score
                                            FROM student_class stc 
                                                 JOIN student st ON stc.studentid = st.id
                                                 JOIN class cl ON stc.classid = cl.id
                                                 JOIN level l ON cl.levelid = l.id
                                                 JOIN course c ON l.id = c.levelid
                                                 JOIN subject sb ON c.subjectid = sb.id
                                                 JOIN result r ON r.courseid = c.id AND r.studentclassid = stc.id
                                                 JOIN semester sm ON stc.semesterid = sm.id
                                                 JOIN session sn ON sm.sessionid = sn.id
                                            WHERE sm.sessionid = ?
                                                    AND cl.id = ?
                                            GROUP BY cl.id, sb.id, st.id
                                            ORDER BY subjectname, score DESC
                                            LIMIT 9223372036854775807        -- MariaDB bug workaround
                                         ) ordered 
                                         JOIN (SELECT @prevs:='', @prev:=0, @seq:=0, @rank:=0) init
                                    ORDER BY subjectname, score DESC     
                                    ");
              $res->execute([ $session, $class ]);
              $chky = 'checked';
              $chkm = $chke = '';
              break;       
}
/*function getOrdinalSuffix($score) {
    $score = abs($score) % 100;
    $lastChar = substr($score, -1, 1);
    switch ($lastChar) {
        case '1' : return ($score == '11') ? 'th' : 'st';
        case '2' : return ($score == '12') ? 'th' : 'nd';
        case '3' : return ($score == '13') ? 'th' : 'rd'; 
    }
    return 'th';  
}
$list = ''; 
for ($score = 1; $score < 150; $score++) { 
    $list .= "$score" . getOrdinalSuffix($score) . "\n"; 
}
///print "$list";*/
/********************************************
  * Derive ordinal suffix for $n
  * 
  * @param int $n     the number
  * @returns string   number with suffix eg 23rd   
  */
  /*
  function ordinal($n) 
  {
      $str = "$n";
      $t = $n > 9 ? substr($str,-2,1) : 0;
      $u = substr($str,-1);
      if ($t==1) 
        return $str . 'th';
      else 
          switch ($u) 
          {
              case 1: return $str . 'st';
              case 2: return $str . 'nd';
              case 3: return $str . 'rd';
              default: return $str . 'th';
          }
  }*/
  // calling function here
/********************************************
  * Derive ordinal suffix for $n
  * 
  * @param int $n     the number
  * @returns string   number with suffix eg 23rd   
  */
  function ordinal($n) 
  {
      $str = "$n";
      $t = $n > 9 ? substr($str,-2,1) : 0;
      $u = substr($str,-1);
      if ($t==1) 
        return $str . 'th';
      else 
          switch ($u) 
          {
              case 1: return $str . 'st';
              case 2: return $str . 'nd';
              case 3: return $str . 'rd';
              default: return $str . 'th';
          }
  }

  //function end
$data = [];
  foreach ($res as $r) {
    if (!isset($data[$r['subjectname']])) {
        $data[$r['subjectname']]['students'] = [];
    }
    
    $position = ordinal($r['rank']);
    $data[$r['subjectname']]['students'][] = [ 'name' => $r['stname'], 'score' => $r['score'], 'rank' => $position ];
}

 

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.