MAINHACK

Viewing / Editing File: show_report.php

<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: POST, GET");
header("Access-Control-Allow-Headers: Content-Type, Authorization");

include("../functions.php");

// Required input parameters
$school_code   = $_POST['school_code']   ?? $_GET['school_code']   ?? '';
$student_id    = $_POST['student_id']    ?? $_GET['student_id']    ?? '';
$form          = $_POST['form']          ?? $_GET['form']          ?? '';
$term          = $_POST['term']          ?? $_GET['term']          ?? '';
$year          = $_POST['academic_year'] ?? $_GET['academic_year'] ?? '';

if (!$school_code || !$student_id || !$form || !$term || !$year) {
    echo json_encode([
        "status" => "error",
        "message" => "Missing required parameters"
    ]);
    exit;
}

/* ----------------------------------------------------------
   STEP 1 — CHECK PAYMENT (ACCESS CODE)
---------------------------------------------------------- */

$sql = "
    SELECT access_code, codeexpired, student_name, classname, schoolID, unique_student_code
    FROM enrol_details
    WHERE (schoolID = ? OR unique_student_code = ?) 
      AND s_code = ?
    LIMIT 1
";

$stmt = $new->prepare($sql);
$stmt->bind_param("sss", $student_id, $student_id, $school_code);
$stmt->execute();
$res = $stmt->get_result();

if ($res->num_rows === 0) {
    echo json_encode(["status" => "error", "message" => "Student not found"]);
    exit;
}

$info = $res->fetch_assoc();
$today = date("Y-m-d");

// expired → block result
if (strtotime($info['codeexpired']) < strtotime($today)) {
    echo json_encode([
        "status" => "payment_required",
        "message" => "Your access code has expired.",
        "expired_on" => $info['codeexpired']
    ]);
    exit;
}

/* ----------------------------------------------------------
   STEP 2 — SCHOOL DETAILS
---------------------------------------------------------- */
$school_stmt = $con->prepare("SELECT * FROM schools WHERE school_code = ?");
$school_stmt->execute([$school_code]);
$school = $school_stmt->fetch();

/* ----------------------------------------------------------
   STEP 3 — GET STUDENT CLASS & HOUSE
---------------------------------------------------------- */

$class_sql = "
    SELECT 
        CASE WHEN TRIM(e.classname)='' OR e.classname IS NULL 
             THEN c.class_name ELSE e.classname END AS class_name
    FROM enrol_details e
    LEFT JOIN class c ON e.class_id = c.cid
    WHERE e.schoolID = ?
    LIMIT 1
";

$class_q = $new->prepare($class_sql);
$class_q->bind_param("s", $student_id);
$class_q->execute();
$class_res = $class_q->get_result()->fetch_assoc();
$class_name = $class_res['class_name'] ?? null;

/* ----------------------------------------------------------
   STEP 4 — HOUSE NAME
---------------------------------------------------------- */

$house_sql = "
    SELECT 
        CASE WHEN TRIM(e.housename)='' OR e.housename IS NULL 
             THEN h.house_name ELSE e.housename END AS house_name
    FROM enrol_details e
    LEFT JOIN house h ON e.house_id = h.hid
    WHERE e.schoolID = ?
    LIMIT 1
";
$house_q = $new->prepare($house_sql);
$house_q->bind_param("s", $student_id);
$house_q->execute();
$house_res = $house_q->get_result()->fetch_assoc();
$house_name = $house_res['house_name'] ?? null;

/* ----------------------------------------------------------
   STEP 5 — GET STUDENT TOTAL SCORE RANK IN CLASS
---------------------------------------------------------- */

$rank_sql = "
    SELECT index_number, SUM(total_score) AS total_score
    FROM student_result 
    WHERE form = ? AND term = ? AND academicyear = ?
    GROUP BY index_number
    ORDER BY total_score DESC
";

$rank_q = $new->prepare($rank_sql);
$rank_q->bind_param("sss", $form, $term, $year);
$rank_q->execute();
$rank_res = $rank_q->get_result();

$rank_list = [];
while ($row = $rank_res->fetch_assoc()) {
    $rank_list[] = $row;
}

$rank = 0;
$prev = null;
$skip = 0;

foreach ($rank_list as $i => $s) {
    if ($s['total_score'] != $prev) {
        $rank = $i + 1 + $skip;
        $skip = 0;
    } else {
        $skip++;
    }
    $rank_list[$i]["position"] = $rank;
    $prev = $s["total_score"];
}

$student_rank = null;
foreach ($rank_list as $s) {
    if ($s["index_number"] == $student_id) {
        $student_rank = $s;
        break;
    }
}

/* ----------------------------------------------------------
   STEP 6 — SUBJECT RESULTS (CORE + ELECTIVE)
---------------------------------------------------------- */

function fetch_subjects($type, $new, $school_code, $form, $term, $year, $student_id) {
    $query = "
        SELECT 
            sr.subject,
            sr.class_score,
            sr.exams_score,
            sr.total_score,
            sr.grading,
            sr.remarks,
            sr.teacher,

ROUND((
                SELECT AVG(sr2.class_score*0.3 + sr2.exams_score*0.7)
                FROM student_result sr2
                WHERE sr2.s_code = sr.s_code 
                  AND sr2.form = ?
                  AND sr2.term = ?
                  AND sr2.academicyear = ?
                  AND sr2.subject = sr.subject
            ), 2) AS class_average,

(
                SELECT COUNT(*) + 1
                FROM student_result r2
                WHERE r2.s_code = sr.s_code
                  AND r2.form = ?
                  AND r2.term = ?
                  AND r2.academicyear = ?
                  AND r2.subject = sr.subject
                  AND (r2.class_score*0.3 + r2.exams_score*0.7) >
                      (sr.class_score*0.3 + sr.exams_score*0.7)
            ) AS subject_position

FROM student_result sr
        WHERE sr.s_code = ?
          AND sr.form = ?
          AND sr.term = ?
          AND sr.academicyear = ?
          AND sr.index_number = ?
          AND sr.r_status = ?
        ORDER BY sr.subject ASC
    ";

$stmt = $new->prepare($query);
    $stmt->bind_param(
        "sssssssssss",
        $form, $term, $year,
        $form, $term, $year,
        $school_code, $form, $term, $year, $student_id, $type
    );
    $stmt->execute();
    return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}

$core_subjects = fetch_subjects('c', $new, $school_code, $form, $term, $year, $student_id);
$elective_subjects = fetch_subjects('e', $new, $school_code, $form, $term, $year, $student_id);

/* ----------------------------------------------------------
   STEP 7 — AGGREGATE (BEST 6)
---------------------------------------------------------- */

$grade_points = [
    "A1" => 1, "B2" => 2, "B3" => 3,
    "C4" => 4, "C5" => 5, "C6" => 6,
    "D7" => 7, "E8" => 8, "F9" => 9
];

$sub_sql = "
    SELECT subject, total_score, grading 
    FROM student_result
    WHERE s_code = ? AND form = ? AND term = ? AND academicyear = ? AND index_number = ?
";

$subs = $new->prepare($sub_sql);
$subs->bind_param("sssss", $school_code, $form, $term, $year, $student_id);
$subs->execute();
$sub_res = $subs->get_result();

$final = [];
while ($r = $sub_res->fetch_assoc()) {
    $grade = strtoupper(trim($r["grading"]));
    if (isset($grade_points[$grade])) {
        $final[] = [
            "subject" => $r["subject"],
            "points"  => $grade_points[$grade],
            "score"   => $r["total_score"]
        ];
    }
}

usort($final, fn($a,$b)=>$a["points"]<=>$b["points"]);
$best6 = array_slice($final, 0, 6);

$aggregate_points = array_sum(array_column($best6, "points"));
$aggregate_average = array_sum(array_column($best6, "score")) / count($best6);

/* ----------------------------------------------------------
   STEP 8 — SEND JSON RESPONSE
---------------------------------------------------------- */

echo json_encode([
    "status" => "success",
    "student" => [
        "id" => $student_id,
        "name" => $info["student_name"],
        "class" => $class_name,
        "house" => $house_name
    ],
    "school" => [
        "name" => $school["school_name"],
        "logo" => $school["logo"],
        "location" => $school["school_location"],
        "email" => $school["school_email"],
        "phone" => $school["phone_num"]
    ],
    "semester" => [
        "form" => $form,
        "term" => $term,
        "academic_year" => $year
    ],
    "rank" => $student_rank,
    "subjects" => [
        "core" => $core_subjects,
        "elective" => $elective_subjects
    ],
    "aggregate" => [
        "best6" => $best6,
        "aggregate_points" => $aggregate_points,
        "best6_average" => round($aggregate_average, 2)
    ]
], JSON_PRETTY_PRINT);

Cancel / Back

[ MAINHACK ]

Viewing / Editing File: show_report.php

Server Info

System Features

User Info