beginTransaction();
// 1. Verify Admission Number Exists and is active
$stmt = $DBH->prepare("
SELECT * FROM tbl_user
WHERE registration_no = :reg_no
AND teacher = 'false'
AND status = 'Active'
");
$stmt->execute(['reg_no' => $registration_no]);
$student = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$student) {
throw new Exception("Invalid admission number or account not active");
}
// 2. Verify PIN exists and is either unassigned or assigned to this registration number
$stmt = $DBH->prepare("
SELECT * FROM tbl_pin
WHERE pin = :pin
AND term = :term
AND year = :session
AND (reg_no IS NULL OR reg_no = '' OR reg_no = :reg_no)
");
$stmt->execute([
'pin' => $pin,
'term' => $term,
'session' => $session,
'reg_no' => $registration_no
]);
$pinRecord = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$pinRecord) {
throw new Exception("PIN not found, already assigned to another student, or wrong Academic Session selected. Please purchase a new PIN from the school or verify the selected session.");
}
// 3. Update PIN record with student details
$stmt = $DBH->prepare("
UPDATE tbl_pin
SET reg_no = :reg_no,
class = :class,
date_time = NOW(),
dt_stamp_start = NOW(),
dt_stamp_end = DATE_ADD(NOW(), INTERVAL 3 MONTH)
WHERE pin = :pin
AND term = :term
AND year = :year
AND (reg_no IS NULL OR reg_no = '' OR reg_no = :reg_no)
");
$stmt->execute([
'reg_no' => $registration_no,
'class' => $class,
'pin' => $pin,
'term' => $term,
'year' => $session
]);
// Check if any rows were affected
if ($stmt->rowCount() === 0) {
throw new Exception("Failed to update PIN record. It may already be assigned to another student.");
}
// 4. Update Session
$_SESSION['student'] = $student;
$_SESSION['exam_info'] = [
'class' => $class,
'term' => $term,
'session' => $session,
'pin' => $pin
];
$DBH->commit();
header("Location: welcome.php");
exit();
} catch (Exception $e) {
$DBH->rollBack();
$error = $e->getMessage();
}
}
?>
BKMS-BLC CBT Portal - Login
