query("SELECT department_id FROM lecturers WHERE id = $lecturerId") or die($conn->error);
if ($lecturerDepartmentQuery) {
$lecturerDepartment = $lecturerDepartmentQuery->fetch_assoc();
$lecturerDepartmentId = $lecturerDepartment['department_id'];
} else {
die("Error fetching lecturer's department: " . $conn->error);
}
// Fetch academic years, levels, and semesters
$academicYears = $conn->query("SELECT * FROM academic_years") or die($conn->error);
$levels = $conn->query("SELECT * FROM levels WHERE department_id = $lecturerDepartmentId") or die($conn->error); // Only levels for the lecturer's department
$semesters = $conn->query("SELECT * FROM semesters") or die($conn->error);
// Initialize variables for form data
$selectedAcademicYearId = isset($_POST['academic_year_id']) ? intval($_POST['academic_year_id']) : 0;
$selectedLevelId = isset($_POST['level_id']) ? intval($_POST['level_id']) : 0;
$selectedSemesterId = isset($_POST['semester_id']) ? intval($_POST['semester_id']) : 0;
$selectedCourseId = isset($_POST['course_id']) ? intval($_POST['course_id']) : 0;
// Fetch courses assigned to the lecturer based on the selected academic year, level, and semester
$assignedCourses = [];
if ($selectedAcademicYearId && $selectedLevelId && $selectedSemesterId) {
$assignedCourses = $conn->query("
SELECT c.id, c.course_name
FROM assigned_courses ac
JOIN courses c ON ac.course_id = c.id
WHERE ac.lecturer_id = $lecturerId
AND c.academic_year_id = $selectedAcademicYearId
AND c.level_id = $selectedLevelId
AND c.semester_id = $selectedSemesterId
") or die($conn->error);
}
// Fetch carry-over courses for the selected level and semester
$carryOverCourses = [];
if ($selectedAcademicYearId && $selectedLevelId && $selectedSemesterId) {
$carryOverCourses = $conn->query("
SELECT c.id, c.course_name
FROM carry_over co
JOIN courses c ON co.course_id = c.id
WHERE co.student_id IN (
SELECT student_id FROM registrations WHERE course_id = c.id
)
AND c.level_id = $selectedLevelId
AND c.semester_id = $selectedSemesterId
AND c.department_id = $lecturerDepartmentId
") or die($conn->error);
}
// Combine assigned courses and carry-over courses
$allCourses = [];
if ($assignedCourses) {
while ($course = $assignedCourses->fetch_assoc()) {
$allCourses[] = $course;
}
}
if ($carryOverCourses) {
while ($course = $carryOverCourses->fetch_assoc()) {
$allCourses[] = $course;
}
}
// Fetch students registered for the selected course (including carry-over students)
$students = [];
if ($selectedCourseId && isset($_POST['get_students'])) {
$stmt = $conn->prepare("
SELECT s.id, s.admission_number, s.first_name, s.last_name, s.passport
FROM students s
JOIN registrations r ON s.id = r.student_id
WHERE r.course_id = ?
UNION
SELECT s.id, s.admission_number, s.first_name, s.last_name, s.passport
FROM students s
JOIN carry_over co ON s.id = co.student_id
WHERE co.course_id = ?
");
$stmt->bind_param("ii", $selectedCourseId, $selectedCourseId);
$stmt->execute();
$students = $stmt->get_result();
}
// Process form submission for uploading scores
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['upload_scores'])) {
// Validate inputs
if (empty($selectedCourseId)) {
die("Please select a course.");
}
// Get total marks and minimum score for the course
$totalMarks = floatval($_POST['total_marks']);
$minimumScore = floatval($_POST['minimum_score']);
// Fetch the department ID for the selected course
$courseQuery = $conn->query("SELECT department_id FROM courses WHERE id = $selectedCourseId") or die($conn->error);
$courseData = $courseQuery->fetch_assoc();
$departmentId = $courseData['department_id'];
// Loop through each student and upload their score
foreach ($_POST['student_scores'] as $studentId => $marksObtained) {
$marksObtained = floatval($marksObtained);
// Check if the student has a previous result for this course
$stmt = $conn->prepare("SELECT id FROM results WHERE student_id = ? AND course_id = ?");
$stmt->bind_param("ii", $studentId, $selectedCourseId);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
// Update the existing result
$stmt = $conn->prepare("UPDATE results SET marks_obtained = ?, total_marks = ?, minimum_score = ? WHERE student_id = ? AND course_id = ?");
$stmt->bind_param("dddii", $marksObtained, $totalMarks, $minimumScore, $studentId, $selectedCourseId);
} else {
// Insert a new result
$stmt = $conn->prepare("INSERT INTO results (student_id, course_id, marks_obtained, total_marks, minimum_score) VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param("iiddd", $studentId, $selectedCourseId, $marksObtained, $totalMarks, $minimumScore);
}
if (!$stmt->execute()) {
die("Error uploading result: " . $stmt->error);
}
// Check if the student passed the course
if ($marksObtained >= $minimumScore) {
// Remove the course from carry_over if the student passes
$stmt = $conn->prepare("DELETE FROM carry_over WHERE student_id = ? AND course_id = ?");
$stmt->bind_param("ii", $studentId, $selectedCourseId);
if (!$stmt->execute()) {
die("Error updating carry-over: " . $stmt->error);
}
} else {
// Add the course to carry_over if the student fails
$stmt = $conn->prepare("INSERT INTO carry_over (student_id, course_id, semester_id, academic_year_id, department_id, level_id, status) VALUES (?, ?, ?, ?, ?, ?, 'Failed')");
$stmt->bind_param("iiiiii", $studentId, $selectedCourseId, $selectedSemesterId, $selectedAcademicYearId, $departmentId, $selectedLevelId);
if (!$stmt->execute()) {
die("Error updating carry-over: " . $stmt->error);
}
// Delete the course from the student's registration to allow re-registration
$stmt = $conn->prepare("DELETE FROM registrations WHERE student_id = ? AND course_id = ?");
$stmt->bind_param("ii", $studentId, $selectedCourseId);
if (!$stmt->execute()) {
die("Error deleting course from registration: " . $stmt->error);
}
}
}
echo "Scores uploaded successfully for all students!";
}
?>
Upload Student Scores