File "complete_hall_allocation_setup.php"
Full path: /home2/santhos2/public_html/bharath-coe.firstmatrix.in.net/api/seed/complete_hall_allocation_setup.php
File size: 23.81 KiB (24384 bytes)
MIME-type: text/x-php
Charset: utf-8
<?php
require_once '../../config/config.php';
// Ensure $pdo is accessible
global $pdo;
header('Content-Type: application/json');
function respond($ok, $msg, $extra = []) {
echo json_encode(($ok ? ['status' => 'success'] : ['status' => 'error']) + ['message' => $msg] + $extra);
exit;
}
try {
$academicYear = trim($_POST['academic_year'] ?? $_GET['academic_year'] ?? '2025-2026 Odd');
$force = (int)($_POST['force'] ?? $_GET['force'] ?? 0);
$count = (int)($_POST['count'] ?? $_GET['count'] ?? 8);
// Ensure we have a valid college_id
$collegeId = 1;
// Check if college exists by code, if not create one
$collegeCheck = $pdo->prepare("SELECT college_id FROM colleges WHERE college_code = ?");
$collegeCheck->execute(['INEC']);
$existingCollege = $collegeCheck->fetch();
if ($existingCollege) {
// Use existing college
$collegeId = $existingCollege['college_id'];
} else {
// Create a default college
$createCollege = $pdo->prepare("INSERT INTO colleges (college_name, college_code, status) VALUES (?, ?, ?)");
$createCollege->execute([
'Isaac Newton Engineering College',
'INEC',
'Active'
]);
$collegeId = $pdo->lastInsertId();
}
$results = [
'halls' => ['created' => 0, 'skipped' => 0],
'settings' => ['created' => 0, 'skipped' => 0],
'exams' => ['created' => 0, 'skipped' => 0],
'timetable' => ['created' => 0, 'skipped' => 0],
'registrations' => ['created' => 0, 'skipped' => 0],
'allocations' => ['created' => 0, 'assigned_students' => 0],
'debug_info' => []
];
// Step 1: Seed Halls and Settings
$hallsData = [
[
'hall_name' => 'Main Hall A',
'capacity' => 120,
'block_name' => 'Block A',
'college_id' => $collegeId,
'settings' => [
'rows_count' => 10, 'columns_count' => 12, 'seating_method' => 'Parallel',
'bench_capacity' => 1, 'max_students' => 120, 'row_labeling' => 'Numeric',
'internal_invigilators_required' => 2, 'external_invigilators_required' => 1,
'aisle_every_n_cols' => 4, 'aisle_every_n_rows' => 0
]
],
[
'hall_name' => 'Main Hall B',
'capacity' => 100,
'block_name' => 'Block B',
'college_id' => $collegeId,
'settings' => [
'rows_count' => 8, 'columns_count' => 12, 'seating_method' => 'Parallel',
'bench_capacity' => 1, 'max_students' => 96, 'row_labeling' => 'Numeric',
'internal_invigilators_required' => 2, 'external_invigilators_required' => 1,
'aisle_every_n_cols' => 4, 'aisle_every_n_rows' => 0
]
],
[
'hall_name' => 'Seminar Hall',
'capacity' => 80,
'block_name' => 'Block A',
'college_id' => $collegeId,
'settings' => [
'rows_count' => 8, 'columns_count' => 10, 'seating_method' => 'Curved',
'bench_capacity' => 1, 'max_students' => 80, 'row_labeling' => 'Alphabetic',
'internal_invigilators_required' => 1, 'external_invigilators_required' => 1,
'aisle_every_n_cols' => 5, 'aisle_every_n_rows' => 0
]
],
[
'hall_name' => 'Computer Lab Hall',
'capacity' => 60,
'block_name' => 'Block B',
'college_id' => $collegeId,
'settings' => [
'rows_count' => 6, 'columns_count' => 10, 'seating_method' => 'Parallel',
'bench_capacity' => 1, 'max_students' => 60, 'row_labeling' => 'Numeric',
'internal_invigilators_required' => 1, 'external_invigilators_required' => 0,
'aisle_every_n_cols' => 5, 'aisle_every_n_rows' => 0
]
],
[
'hall_name' => 'Auditorium',
'capacity' => 200,
'block_name' => 'Central Block',
'college_id' => $collegeId,
'settings' => [
'rows_count' => 15, 'columns_count' => 13, 'seating_method' => 'Curved',
'bench_capacity' => 1, 'max_students' => 195, 'row_labeling' => 'Alphabetic',
'internal_invigilators_required' => 3, 'external_invigilators_required' => 2,
'aisle_every_n_cols' => 6, 'aisle_every_n_rows' => 5
]
],
[
'hall_name' => 'Small Hall C',
'capacity' => 40,
'block_name' => 'Block C',
'college_id' => $collegeId,
'settings' => [
'rows_count' => 5, 'columns_count' => 8, 'seating_method' => 'Parallel',
'bench_capacity' => 1, 'max_students' => 40, 'row_labeling' => 'Numeric',
'internal_invigilators_required' => 1, 'external_invigilators_required' => 0,
'aisle_every_n_cols' => 4, 'aisle_every_n_rows' => 0
]
],
[
'hall_name' => 'Library Hall',
'capacity' => 50,
'block_name' => 'Library Block',
'college_id' => $collegeId,
'settings' => [
'rows_count' => 5, 'columns_count' => 10, 'seating_method' => 'Parallel',
'bench_capacity' => 1, 'max_students' => 50, 'row_labeling' => 'Numeric',
'internal_invigilators_required' => 1, 'external_invigilators_required' => 0,
'aisle_every_n_cols' => 5, 'aisle_every_n_rows' => 0
]
],
[
'hall_name' => 'Conference Hall',
'capacity' => 30,
'block_name' => 'Admin Block',
'college_id' => $collegeId,
'settings' => [
'rows_count' => 3, 'columns_count' => 10, 'seating_method' => 'Parallel',
'bench_capacity' => 1, 'max_students' => 30, 'row_labeling' => 'Numeric',
'internal_invigilators_required' => 1, 'external_invigilators_required' => 0,
'aisle_every_n_cols' => 5, 'aisle_every_n_rows' => 0
]
]
];
foreach (array_slice($hallsData, 0, $count) as $hallData) {
$settings = $hallData['settings'];
unset($hallData['settings']);
// Check if hall already exists
$existingHall = $pdo->prepare("SELECT hall_id FROM halls WHERE hall_name = ?");
$existingHall->execute([$hallData['hall_name']]);
$existing = $existingHall->fetch();
if ($existing && !$force) {
$results['halls']['skipped']++;
continue;
}
if ($existing && $force) {
// Update existing hall
$updateHall = $pdo->prepare("UPDATE halls SET capacity = ?, block_name = ? WHERE hall_id = ?");
$updateHall->execute([$hallData['capacity'], $hallData['block_name'], $existing['hall_id']]);
$hallId = $existing['hall_id'];
} else {
// Insert new hall
$insertHall = $pdo->prepare("INSERT INTO halls (hall_name, capacity, block_name, college_id) VALUES (?, ?, ?, ?)");
$insertHall->execute([$hallData['hall_name'], $hallData['capacity'], $hallData['block_name'], $hallData['college_id']]);
$hallId = $pdo->lastInsertId();
}
if ($hallId) {
$results['halls']['created']++;
// Handle hall settings
$existingSettings = $pdo->prepare("SELECT hall_setting_id FROM hall_settings WHERE hall_id = ?");
$existingSettings->execute([$hallId]);
$existingSetting = $existingSettings->fetch();
if ($existingSetting && !$force) {
$results['settings']['skipped']++;
continue;
}
if ($existingSetting && $force) {
// Update existing settings
$updateSettings = $pdo->prepare("
UPDATE hall_settings SET
rows_count = ?, columns_count = ?, seating_method = ?, bench_capacity = ?,
max_students = ?, row_labeling = ?, internal_invigilators_required = ?,
external_invigilators_required = ?, aisle_every_n_cols = ?, aisle_every_n_rows = ?
WHERE hall_id = ?
");
$updateSettings->execute([
$settings['rows_count'], $settings['columns_count'], $settings['seating_method'],
$settings['bench_capacity'], $settings['max_students'], $settings['row_labeling'],
$settings['internal_invigilators_required'], $settings['external_invigilators_required'],
$settings['aisle_every_n_cols'], $settings['aisle_every_n_rows'], $hallId
]);
} else {
// Insert new settings
$insertSettings = $pdo->prepare("
INSERT INTO hall_settings (
hall_id, rows_count, columns_count, seating_method, bench_capacity,
max_students, row_labeling, internal_invigilators_required,
external_invigilators_required, aisle_every_n_cols, aisle_every_n_rows
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
");
$insertSettings->execute([
$hallId, $settings['rows_count'], $settings['columns_count'], $settings['seating_method'],
$settings['bench_capacity'], $settings['max_students'], $settings['row_labeling'],
$settings['internal_invigilators_required'], $settings['external_invigilators_required'],
$settings['aisle_every_n_cols'], $settings['aisle_every_n_rows']
]);
}
$results['settings']['created']++;
}
}
// Step 2: Generate Exam Schedule
$yearOnly = preg_match('/\b\d{4}-\d{4}\b/', $academicYear, $m) ? $m[0] : '2025-2026';
// Check if exam type exists
$examTypeStmt = $pdo->prepare("SELECT exam_type_id FROM exam_types WHERE type_name = 'SEM' LIMIT 1");
$examTypeStmt->execute();
$examTypeId = $examTypeStmt->fetchColumn();
if (!$examTypeId) {
$insertExamType = $pdo->prepare("INSERT INTO exam_types (type_name, description) VALUES ('SEM', 'Semester Examination')");
$insertExamType->execute();
$examTypeId = $pdo->lastInsertId();
}
// Generate exams for semesters 1, 3, 5, 7
$semesters = [1, 3, 5, 7];
foreach ($semesters as $semester) {
$examName = "SEM - AY $yearOnly - Sem $semester";
// Check for existing exam by academic year, semester, and exam type to prevent duplicates
$existingExam = $pdo->prepare("
SELECT exam_id FROM exams
WHERE academic_year = ? AND semester = ? AND exam_type_id = ?
");
$existingExam->execute([$yearOnly, $semester, $examTypeId]);
$existing = $existingExam->fetch();
if ($existing && !$force) {
$results['exams']['skipped']++;
continue;
}
if ($existing && $force) {
$examId = $existing['exam_id'];
} else {
$insertExam = $pdo->prepare("INSERT INTO exams (exam_name, exam_type_id, academic_year, semester, status) VALUES (?, ?, ?, ?, 'Active')");
$insertExam->execute([$examName, $examTypeId, $yearOnly, $semester]);
$examId = $pdo->lastInsertId();
}
if ($examId) {
$results['exams']['created']++;
// Generate exam subjects and timetable
$subjectsStmt = $pdo->prepare("SELECT subject_id FROM subjects WHERE semester = ? AND (type <> 'Lab' OR type IS NULL) LIMIT 5");
$subjectsStmt->execute([$semester]);
$subjects = $subjectsStmt->fetchAll(PDO::FETCH_COLUMN);
foreach ($subjects as $subjectId) {
// Create exam subject - check for duplicates more thoroughly
$existingExamSubject = $pdo->prepare("
SELECT es.exam_subject_id
FROM exam_subjects es
JOIN exams e ON es.exam_id = e.exam_id
WHERE es.subject_id = ? AND e.academic_year = ? AND e.semester = ?
");
$existingExamSubject->execute([$subjectId, $yearOnly, $semester]);
$existingES = $existingExamSubject->fetch();
if ($existingES) {
$examSubjectId = $existingES['exam_subject_id'];
continue; // Skip creating duplicate exam subject
} else {
$insertExamSubject = $pdo->prepare("INSERT INTO exam_subjects (exam_id, subject_id) VALUES (?, ?)");
$insertExamSubject->execute([$examId, $subjectId]);
$examSubjectId = $pdo->lastInsertId();
}
if ($examSubjectId) {
// Create timetable entries (FN and AN sessions)
$sessions = ['FN', 'AN'];
foreach ($sessions as $session) {
// Check for existing timetable entry more thoroughly
$existingTimetable = $pdo->prepare("
SELECT et.timetable_id
FROM exam_timetable et
JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
JOIN exams e ON es.exam_id = e.exam_id
WHERE es.subject_id = ? AND e.academic_year = ? AND e.semester = ? AND et.session = ?
");
$existingTimetable->execute([$subjectId, $yearOnly, $semester, $session]);
$existingTT = $existingTimetable->fetch();
if ($existingTT && !$force) {
$results['timetable']['skipped']++;
continue;
}
// Generate consecutive exam dates: 2025-09-14, 2025-09-15, 2025-09-16
$baseDate = '2025-09-14';
$dayOffset = ($semester - 1) % 3; // Map semesters 1,3,5,7 to days 0,1,2,0
$examDate = date('Y-m-d', strtotime("$baseDate +{$dayOffset} days"));
$startTime = $session === 'FN' ? '09:00:00' : '14:00:00';
$endTime = $session === 'FN' ? '12:00:00' : '17:00:00';
if ($existingTT && $force) {
$updateTimetable = $pdo->prepare("UPDATE exam_timetable SET exam_date = ?, start_time = ?, end_time = ? WHERE timetable_id = ?");
$updateTimetable->execute([$examDate, $startTime, $endTime, $existingTT['timetable_id']]);
$results['timetable']['created']++;
} else if (!$existingTT) {
try {
$insertTimetable = $pdo->prepare("INSERT INTO exam_timetable (exam_subject_id, exam_date, session, start_time, end_time) VALUES (?, ?, ?, ?, ?)");
$insertTimetable->execute([$examSubjectId, $examDate, $session, $startTime, $endTime]);
$results['timetable']['created']++;
} catch (PDOException $e) {
if ($e->getCode() == 23000) { // Duplicate entry
$results['timetable']['skipped']++;
error_log("Duplicate timetable entry skipped: exam_subject_id={$examSubjectId}, session={$session}");
} else {
throw $e;
}
}
}
}
}
}
}
}
// Step 3: Generate Student Registrations
$studentsStmt = $pdo->query("SELECT student_id, course_id FROM students WHERE status = 'Active' LIMIT 50");
$students = $studentsStmt->fetchAll(PDO::FETCH_ASSOC);
$examSubjectsStmt = $pdo->prepare("SELECT es.exam_subject_id, es.exam_id, s.course_id FROM exam_subjects es JOIN subjects s ON es.subject_id = s.subject_id JOIN exams e ON es.exam_id = e.exam_id WHERE e.academic_year = ?");
$examSubjectsStmt->execute([$yearOnly]);
$examSubjects = $examSubjectsStmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($students as $student) {
foreach ($examSubjects as $examSubject) {
if ($student['course_id'] == $examSubject['course_id']) {
// Check if registration already exists
$existingReg = $pdo->prepare("SELECT registration_id FROM student_registrations WHERE student_id = ? AND exam_subject_id = ?");
$existingReg->execute([$student['student_id'], $examSubject['exam_subject_id']]);
$existing = $existingReg->fetch();
if ($existing && !$force) {
$results['registrations']['skipped']++;
continue;
}
if ($existing && $force) {
continue; // Skip existing registrations
}
// Get fee structure
$feeStmt = $pdo->prepare("SELECT fee_structure_id FROM course_fee_structure WHERE course_id = ? LIMIT 1");
$feeStmt->execute([$student['course_id']]);
$feeStructureId = $feeStmt->fetchColumn();
$insertReg = $pdo->prepare("INSERT INTO student_registrations (student_id, exam_subject_id, is_arrear, fee_structure_id) VALUES (?, ?, 0, ?)");
$insertReg->execute([$student['student_id'], $examSubject['exam_subject_id'], $feeStructureId]);
$results['registrations']['created']++;
}
}
}
// Step 4: Generate Hall Allocations
$timetablesStmt = $pdo->prepare("
SELECT et.timetable_id, et.session, et.exam_subject_id, es.exam_id
FROM exam_timetable et
JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id
JOIN exams e ON es.exam_id = e.exam_id
WHERE e.academic_year = ?
");
$timetablesStmt->execute([$yearOnly]);
$timetables = $timetablesStmt->fetchAll(PDO::FETCH_ASSOC);
$hallsStmt = $pdo->query("
SELECT h.hall_id, h.hall_name, h.capacity,
COALESCE(hs.max_students, h.capacity) as effective_capacity
FROM halls h
LEFT JOIN hall_settings hs ON h.hall_id = hs.hall_id
ORDER BY effective_capacity DESC
");
$halls = $hallsStmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($timetables as $tt) {
$isArrear = ($tt['session'] === 'AN') ? 1 : 0;
// Get registrations for this exam_subject and session
$regsStmt = $pdo->prepare("SELECT registration_id FROM student_registrations WHERE exam_subject_id = ? AND is_arrear = ?");
$regsStmt->execute([$tt['exam_subject_id'], $isArrear]);
$registrations = $regsStmt->fetchAll(PDO::FETCH_COLUMN);
if (empty($registrations)) continue;
$remainingStudents = count($registrations);
$studentIndex = 0;
// Clear existing allocations
if ($force) {
$oldAllocStmt = $pdo->prepare("SELECT allocation_id FROM hall_allocations WHERE timetable_id = ?");
$oldAllocStmt->execute([$tt['timetable_id']]);
$oldAllocIds = $oldAllocStmt->fetchAll(PDO::FETCH_COLUMN);
if ($oldAllocIds) {
$in = implode(',', array_fill(0, count($oldAllocIds), '?'));
$pdo->prepare("DELETE FROM student_seating WHERE allocation_id IN ($in)")->execute($oldAllocIds);
$pdo->prepare('DELETE FROM hall_allocations WHERE timetable_id = ?')->execute([$tt['timetable_id']]);
}
}
// Allocate students to halls
foreach ($halls as $hall) {
if ($remainingStudents <= 0) break;
$studentsToAllocate = min($remainingStudents, $hall['effective_capacity']);
// Create hall allocation
$allocStmt = $pdo->prepare("INSERT INTO hall_allocations (timetable_id, hall_id, num_students) VALUES (?, ?, ?)");
$allocStmt->execute([$tt['timetable_id'], $hall['hall_id'], $studentsToAllocate]);
$allocationId = $pdo->lastInsertId();
if ($allocationId) {
$results['allocations']['created']++;
// Get hall settings for seat numbering
$hallSettingsStmt = $pdo->prepare("SELECT * FROM hall_settings WHERE hall_id = ?");
$hallSettingsStmt->execute([$hall['hall_id']]);
$hallSettings = $hallSettingsStmt->fetch(PDO::FETCH_ASSOC);
// Create student seating
for ($i = 0; $i < $studentsToAllocate; $i++) {
$regId = $registrations[$studentIndex + $i];
if ($hallSettings) {
// Generate seat number based on hall layout
$rows = (int)$hallSettings['rows_count'];
$cols = (int)$hallSettings['columns_count'];
$bench = (int)$hallSettings['bench_capacity'];
$cellsPerRow = $cols * $bench;
$rowIndex = (int)floor($i / $cellsPerRow) + 1;
$rem = $i % $cellsPerRow;
$colIndex = (int)floor($rem / $bench) + 1;
$seatIndex = $rem % $bench + 1;
$rowLabel = ($hallSettings['row_labeling'] === 'Alphabetic') ? chr(64 + $rowIndex) : 'R' . $rowIndex;
$seatNumber = $rowLabel . '-C' . $colIndex . '-S' . $seatIndex;
} else {
// Simple sequential numbering
$seatNumber = 'S' . ($i + 1);
}
$seatingStmt = $pdo->prepare("INSERT INTO student_seating (registration_id, allocation_id, seat_number) VALUES (?, ?, ?)");
$seatingStmt->execute([$regId, $allocationId, $seatNumber]);
$results['allocations']['assigned_students']++;
}
$studentIndex += $studentsToAllocate;
$remainingStudents -= $studentsToAllocate;
}
}
}
// Debug information
$results['debug_info'] = [
'total_halls' => $pdo->query('SELECT COUNT(*) FROM halls')->fetchColumn(),
'total_hall_settings' => $pdo->query('SELECT COUNT(*) FROM hall_settings')->fetchColumn(),
'total_exams' => (function() use ($pdo, $yearOnly) { $stmt = $pdo->prepare('SELECT COUNT(*) FROM exams WHERE academic_year = ?'); $stmt->execute([$yearOnly]); return $stmt->fetchColumn(); })(),
'total_timetable_entries' => (function() use ($pdo, $yearOnly) { $stmt = $pdo->prepare('SELECT COUNT(*) FROM exam_timetable et JOIN exam_subjects es ON et.exam_subject_id = es.exam_subject_id JOIN exams e ON es.exam_id = e.exam_id WHERE e.academic_year = ?'); $stmt->execute([$yearOnly]); return $stmt->fetchColumn(); })(),
'total_registrations' => $pdo->query('SELECT COUNT(*) FROM student_registrations')->fetchColumn(),
'total_allocations' => $pdo->query('SELECT COUNT(*) FROM hall_allocations')->fetchColumn(),
'total_seating' => $pdo->query('SELECT COUNT(*) FROM student_seating')->fetchColumn()
];
respond(true, 'Complete hall allocation setup completed successfully', $results);
} catch (Throwable $e) {
http_response_code(500);
respond(false, $e->getMessage());
}
?>