pongpon pitisuk
103 lines
4.1 KiB
PHP
103 lines
4.1 KiB
PHP
<?php
|
||
require_once __DIR__ . '/config.php';
|
||
|
||
try {
|
||
// สร้างฐานข้อมูล
|
||
$pdo = new PDO("mysql:host=" . DB_HOST, DB_USER, DB_PASS);
|
||
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
||
$pdo->exec("CREATE DATABASE IF NOT EXISTS `" . DB_NAME . "` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
|
||
$pdo->exec("USE `" . DB_NAME . "`");
|
||
|
||
// สร้างตาราง
|
||
$pdo->exec("
|
||
CREATE TABLE IF NOT EXISTS curricula (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
code VARCHAR(10) NOT NULL UNIQUE,
|
||
name_th VARCHAR(255) NOT NULL,
|
||
name_en VARCHAR(255) NOT NULL,
|
||
total_credits INT NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
||
");
|
||
|
||
$pdo->exec("
|
||
CREATE TABLE IF NOT EXISTS course_groups (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
curriculum_id INT NOT NULL,
|
||
parent_id INT DEFAULT NULL,
|
||
code VARCHAR(50) NOT NULL,
|
||
name_th VARCHAR(255) NOT NULL,
|
||
min_credits INT NOT NULL DEFAULT 0,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
FOREIGN KEY (curriculum_id) REFERENCES curricula(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (parent_id) REFERENCES course_groups(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
||
");
|
||
|
||
$pdo->exec("
|
||
CREATE TABLE IF NOT EXISTS courses (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
group_id INT NOT NULL,
|
||
code VARCHAR(20) NOT NULL UNIQUE,
|
||
name_th VARCHAR(255) NOT NULL,
|
||
name_en VARCHAR(255) DEFAULT '',
|
||
credits DECIMAL(3,1) NOT NULL,
|
||
lecture_hours INT NOT NULL DEFAULT 0,
|
||
practice_hours INT NOT NULL DEFAULT 0,
|
||
self_study_hours INT NOT NULL DEFAULT 0,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
FOREIGN KEY (group_id) REFERENCES course_groups(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
||
");
|
||
|
||
$pdo->exec("
|
||
CREATE TABLE IF NOT EXISTS students (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
student_code VARCHAR(20) NOT NULL UNIQUE,
|
||
name_th VARCHAR(255) NOT NULL,
|
||
name_en VARCHAR(255) DEFAULT '',
|
||
faculty VARCHAR(255) DEFAULT '',
|
||
graduated_institution VARCHAR(255) DEFAULT '',
|
||
curriculum_id INT NOT NULL,
|
||
enrollment_year INT NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (curriculum_id) REFERENCES curricula(id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
||
");
|
||
|
||
$pdo->exec("
|
||
CREATE TABLE IF NOT EXISTS student_courses (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
student_id INT NOT NULL,
|
||
course_id INT DEFAULT NULL,
|
||
course_code VARCHAR(20) NOT NULL,
|
||
course_name_th VARCHAR(255) NOT NULL,
|
||
credits DECIMAL(3,1) NOT NULL,
|
||
lecture_hours INT NOT NULL DEFAULT 0,
|
||
practice_hours INT NOT NULL DEFAULT 0,
|
||
self_study_hours INT NOT NULL DEFAULT 0,
|
||
source_type ENUM('transfer','manual') NOT NULL DEFAULT 'manual',
|
||
source_file VARCHAR(255) DEFAULT NULL,
|
||
notes TEXT DEFAULT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE SET NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
|
||
");
|
||
|
||
echo "✅ Database and tables created successfully!\n";
|
||
|
||
// ตรวจสอบว่ามีข้อมูลหลักสูตรหรือยัง
|
||
$stmt = $pdo->query("SELECT COUNT(*) FROM curricula");
|
||
if ($stmt->fetchColumn() == 0) {
|
||
echo "⏳ Importing curriculum data...\n";
|
||
require_once __DIR__ . '/import_curriculum.php';
|
||
echo "✅ Curriculum data imported!\n";
|
||
} else {
|
||
echo "ℹ️ Curriculum data already exists, skipping import.\n";
|
||
}
|
||
|
||
} catch (PDOException $e) {
|
||
die("❌ Error: " . $e->getMessage() . "\n");
|
||
}
|