exec("CREATE TABLE IF NOT EXISTS teacher_assignments (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
teacher_id INT NOT NULL,
class_id INT NOT NULL,
subject_id INT NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_assignment (teacher_id, class_id, subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
$action = $_GET['action'] ?? 'list';
$id = (int)($_GET['id'] ?? 0);
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$post_action = $_POST['action'] ?? $action;
$post_id = (int)($_POST['id'] ?? $id);
if ($post_action === 'delete' && $post_id > 0) {
require_permission('assignments', 'delete');
$stmt = db()->prepare("DELETE FROM teacher_assignments WHERE id = ?");
$stmt->execute([$post_id]);
header('Location: ' . app_url('admin.php', ['page' => 'assignments']));
exit;
}
if ($post_action === 'add') {
$teacher_id = (int)($_POST['teacher_id'] ?? 0);
$class_id = (int)($_POST['class_id'] ?? 0);
$subject_id = (int)($_POST['subject_id'] ?? 0);
if ($teacher_id > 0 && $class_id > 0) {
try {
$stmt = db()->prepare("INSERT INTO teacher_assignments (teacher_id, class_id, subject_id) VALUES (?, ?, ?)");
$stmt->execute([$teacher_id, $class_id, $subject_id]);
} catch (PDOException $e) {
// Ignore duplicate key error
}
}
header('Location: ' . app_url('admin.php', ['page' => 'assignments']));
exit;
}
}
// Fetch all for dropdowns
$teachers = db()->query("SELECT id, name FROM teachers ORDER BY name")->fetchAll(PDO::FETCH_ASSOC);
$classes = db()->query("SELECT id, name_en, name_ar FROM classes ORDER BY name_en")->fetchAll(PDO::FETCH_ASSOC);
$subjects = db()->query("SELECT id, title_en, title_ar, class_id FROM subjects ORDER BY title_en")->fetchAll(PDO::FETCH_ASSOC);
// Fetch assignments for table
$search = $_GET['search'] ?? '';
$page_num = max(1, (int)($_GET['p'] ?? 1));
$limit = 10;
$offset = ($page_num - 1) * $limit;
$where = "";
$params = [];
if ($search !== '') {
$where = "WHERE t.name LIKE ? OR c.name_en LIKE ? OR s.title_en LIKE ?";
$params[] = "%$search%";
$params[] = "%$search%";
$params[] = "%$search%";
}
$count_sql = "
SELECT COUNT(*)
FROM teacher_assignments ta
JOIN teachers t ON ta.teacher_id = t.id
JOIN classes c ON ta.class_id = c.id
LEFT JOIN subjects s ON ta.subject_id = s.id
$where
";
$total_stmt = db()->prepare($count_sql);
$total_stmt->execute($params);
$total = $total_stmt->fetchColumn();
$pages = ceil($total / $limit);
$sql = "
SELECT ta.id, t.name as teacher_name, c.name_en as class_name_en, c.name_ar as class_name_ar, s.title_en as subject_title_en, s.title_ar as subject_title_ar, ta.subject_id
FROM teacher_assignments ta
JOIN teachers t ON ta.teacher_id = t.id
JOIN classes c ON ta.class_id = c.id
LEFT JOIN subjects s ON ta.subject_id = s.id
$where
ORDER BY ta.id DESC
LIMIT $limit OFFSET $offset
";
$stmt = db()->prepare($sql);
$stmt->execute($params);
$items = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
1): ?>