pongpon pitisuk
207 lines
12 KiB
PHP
207 lines
12 KiB
PHP
<?php
|
|
$page_title = 'นำเข้า CSV';
|
|
require_once __DIR__ . '/../includes/functions.php';
|
|
requireLogin();
|
|
|
|
$student_id = intval($_GET['student_id'] ?? 0);
|
|
|
|
if (isset($_GET['template'])) {
|
|
header('Content-Type: text/csv; charset=utf-8');
|
|
header('Content-Disposition: attachment; filename=import_template.csv');
|
|
echo "\xEF\xBB\xBF";
|
|
$cols = $student_id ? ['course_code','course_name','credits','grade','course_type','semester','academic_year','lecture','practice','self_study','notes','group_id'] : ['student_code','course_code','course_name','credits','grade','course_type','semester','academic_year','lecture','practice','self_study','notes','group_id'];
|
|
echo implode(',', $cols) . "\n";
|
|
if ($student_id) {
|
|
echo "1012301,การเขียนโปรแกรมเบื้องต้น,3,A,เรียนปกติ,1,2569,2,1,3,,\n";
|
|
} else {
|
|
echo "STU001,1012301,การเขียนโปรแกรมเบื้องต้น,3,A,เรียนปกติ,1,2569,2,1,3,,\n";
|
|
}
|
|
exit;
|
|
}
|
|
|
|
if ($student_id) {
|
|
requireStudentAccess($student_id);
|
|
$student = getStudent($student_id);
|
|
if (!$student) { echo '<div class="alert alert-danger">ไม่พบนักศึกษา</div>'; require_once __DIR__ . '/../includes/footer.php'; exit; }
|
|
}
|
|
|
|
require_once __DIR__ . '/../includes/header.php';
|
|
|
|
$results = [];
|
|
$total_success = 0;
|
|
$total_error = 0;
|
|
|
|
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['csv_file']) && $_FILES['csv_file']['error'] === UPLOAD_ERR_OK) {
|
|
$tmp = $_FILES['csv_file']['tmp_name'];
|
|
// Read all rows at once
|
|
$all_rows = [];
|
|
$handle = fopen($tmp, 'r');
|
|
if ($handle) {
|
|
while (($r = fgetcsv($handle)) !== false) $all_rows[] = $r;
|
|
fclose($handle);
|
|
}
|
|
if (empty($all_rows)) { $results[] = 'ไฟล์ CSV ว่างเปล่า'; $total_error++; }
|
|
else {
|
|
$first = $all_rows[0];
|
|
$first[0] = preg_replace('/^\xEF\xBB\xBF/', '', $first[0]);
|
|
$first = array_map(function($v) { return trim($v, " \t\n\r\0\x0B\""); }, $first);
|
|
// Auto-detect header: if first cell starts with a digit, it's data (no header)
|
|
$has_header = !preg_match('/^\d/', $first[0]);
|
|
if ($has_header) {
|
|
$header_norm = array_map(function($v) {
|
|
return preg_replace('/[^a-z0-9_]/', '', str_replace(' ', '_', strtolower(trim($v))));
|
|
}, $first);
|
|
$results[] = "พบหัวคอลัมน์: " . implode(', ', $first);
|
|
$col_map = [];
|
|
$expected = $student_id ? ['course_code','course_name','credits','grade','course_type','semester','academic_year','lecture','practice','self_study','notes','group_id'] : ['student_code','course_code','course_name','credits','grade','course_type','semester','academic_year','lecture','practice','self_study','notes','group_id'];
|
|
foreach ($expected as $e) {
|
|
$idx = array_search($e, $header_norm);
|
|
if ($idx !== false) $col_map[$e] = $idx;
|
|
}
|
|
array_shift($all_rows);
|
|
} else {
|
|
$results[] = "ไม่พบแถวหัวคอลัมน์ — ใช้ตำแหน่งคอลัมน์ตามลำดับ";
|
|
if ($student_id) {
|
|
$col_map = ['course_code' => 0, 'course_name' => 1, 'credits' => 2, 'grade' => 3, 'course_type' => 4, 'semester' => 5, 'academic_year' => 6, 'lecture' => 7, 'practice' => 8, 'self_study' => 9, 'notes' => 10, 'group_id' => 11];
|
|
} else {
|
|
$col_map = ['student_code' => 0, 'course_code' => 1, 'course_name' => 2, 'credits' => 3, 'grade' => 4, 'course_type' => 5, 'semester' => 6, 'academic_year' => 7, 'lecture' => 8, 'practice' => 9, 'self_study' => 10, 'notes' => 11, 'group_id' => 12];
|
|
}
|
|
}
|
|
$db = getDB();
|
|
foreach ($all_rows as $idx => $row) {
|
|
$line = $idx + 1;
|
|
$c = fn($k) => $col_map[$k] ?? null;
|
|
if ($student_id) {
|
|
$student = getStudent($student_id);
|
|
$student_code = $student['student_code'];
|
|
} else {
|
|
$student_code = trim($row[$c('student_code')] ?? '');
|
|
}
|
|
$course_code = trim($row[$c('course_code')] ?? '');
|
|
$course_name = trim($row[$c('course_name')] ?? '');
|
|
$credits = floatval($row[$c('credits')] ?? 0);
|
|
$grade = trim($row[$c('grade')] ?? '');
|
|
$course_type_col = trim($row[$c('course_type')] ?? '');
|
|
$semester = trim($row[$c('semester')] ?? '');
|
|
$academic_year = trim($row[$c('academic_year')] ?? '');
|
|
$lecture = intval($row[$c('lecture')] ?? 0);
|
|
$practice = intval($row[$c('practice')] ?? 0);
|
|
$self_study = intval($row[$c('self_study')] ?? 0);
|
|
$notes = trim($row[$c('notes')] ?? '');
|
|
$group_id = intval($row[$c('group_id')] ?? 0);
|
|
|
|
if (empty($course_code) || empty($course_name) || $credits <= 0) {
|
|
$results[] = "แถวที่ $line: ข้าม (ข้อมูลไม่ครบ: course_code, course_name, credits)";
|
|
$total_error++;
|
|
continue;
|
|
}
|
|
|
|
try {
|
|
if (!$student_id) {
|
|
$stmt = $db->prepare("SELECT id, curriculum_id, advisor_id FROM students WHERE student_code = ?");
|
|
$stmt->execute([$student_code]);
|
|
$student = $stmt->fetch();
|
|
if (!$student) {
|
|
$results[] = "แถวที่ $line: ไม่พบรหัสนักศึกษา '$student_code'";
|
|
$total_error++;
|
|
continue;
|
|
}
|
|
if (isAdvisor() && $student['advisor_id'] != getCurrentUserId()) {
|
|
$results[] = "แถวที่ $line: นักศึกษา '$student_code' ไม่อยู่ในที่ปรึกษาของคุณ";
|
|
$total_error++;
|
|
continue;
|
|
}
|
|
}
|
|
|
|
$curriculum_course = findCourseInCurriculum($student['curriculum_id'], $course_code);
|
|
$course_id = null;
|
|
if ($curriculum_course) {
|
|
$course_id = $curriculum_course['id'];
|
|
} elseif ($group_id > 0) {
|
|
$stmt = $db->prepare("SELECT id FROM courses WHERE group_id = ? AND code = ?");
|
|
$stmt->execute([$group_id, $course_code]);
|
|
$existing = $stmt->fetch();
|
|
if ($existing) {
|
|
$course_id = $existing['id'];
|
|
} else {
|
|
$stmt = $db->prepare("INSERT INTO courses (group_id, code, name_th, credits, lecture_hours, practice_hours, self_study_hours, sort_order) VALUES (?, ?, ?, ?, ?, ?, ?, 0)");
|
|
$stmt->execute([$group_id, $course_code, $course_name, $credits, $lecture, $practice, $self_study]);
|
|
$course_id = $db->lastInsertId();
|
|
}
|
|
}
|
|
|
|
$stmt = $db->prepare("INSERT INTO student_courses (student_id, course_id, course_code, course_name_th, credits, lecture_hours, practice_hours, self_study_hours, grade, course_type, semester, academic_year, source_type, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'manual', ?)");
|
|
$stmt->execute([$student['id'], $course_id, $course_code, $course_name, $credits, $lecture, $practice, $self_study, $grade ?: null, $course_type_col ?: null, $semester ?: null, $academic_year ?: null, $notes]);
|
|
$results[] = "แถวที่ $line: ✓ $student_code - $course_code ($course_name)";
|
|
$total_success++;
|
|
} catch (PDOException $e) {
|
|
$results[] = "แถวที่ $line: ผิดพลาด - " . $e->getMessage();
|
|
$total_error++;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
?>
|
|
<div class="page-heading">
|
|
<div>
|
|
<h4><i class="bi bi-upload"></i> นำเข้ารายวิชาจากไฟล์ CSV</h4>
|
|
<div class="text-muted small">รองรับไฟล์ .csv ตามรูปแบบคอลัมน์ที่ระบบกำหนด</div>
|
|
</div>
|
|
<a href="<?= $student_id ? 'view.php?id=' . $student_id : 'list.php' ?>" class="btn btn-secondary"><i class="bi bi-arrow-left"></i> กลับ</a>
|
|
</div>
|
|
|
|
<?php if ($student_id): ?>
|
|
<div class="alert alert-info">กำลังนำเข้าสำหรับนักศึกษา: <strong><?= htmlspecialchars($student['student_code'] ?? '') ?> - <?= htmlspecialchars($student['name_th'] ?? '') ?></strong> (ไม่ต้องระบุ student_code ใน CSV)</div>
|
|
<?php endif; ?>
|
|
|
|
<div class="row g-4">
|
|
<div class="col-md-6">
|
|
<div class="card">
|
|
<div class="card-header bg-primary text-white">
|
|
<h5 class="mb-0"><i class="bi bi-filetype-csv"></i> เลือกไฟล์สำหรับนำเข้า</h5>
|
|
</div>
|
|
<div class="card-body">
|
|
<form method="POST" enctype="multipart/form-data">
|
|
<div class="mb-3">
|
|
<label class="form-label">เลือกไฟล์ CSV</label>
|
|
<input type="file" name="csv_file" class="form-control" accept=".csv" required>
|
|
</div>
|
|
<div class="mb-3">
|
|
<label class="form-label">รูปแบบไฟล์</label>
|
|
<p class="text-muted small mb-2">ไฟล์ CSV ต้องมีหัวคอลัมน์ด้านล่างนี้ (คั่นด้วย comma):</p>
|
|
<code class="d-block p-2 bg-light rounded small"><?= $student_id ? 'course_code,course_name,credits,grade,course_type,semester,academic_year,lecture,practice,self_study,notes,group_id' : 'student_code,course_code,course_name,credits,grade,course_type,semester,academic_year,lecture,practice,self_study,notes,group_id' ?></code>
|
|
<p class="text-muted small mt-2 mb-0">คอลัมน์ที่จำเป็น: <strong><?= $student_id ? 'course_code, course_name, credits' : 'student_code, course_code, course_name, credits' ?></strong></p>
|
|
</div>
|
|
<button type="submit" class="btn btn-primary"><i class="bi bi-cloud-upload"></i> นำเข้า</button>
|
|
</form>
|
|
</div>
|
|
</div>
|
|
<div class="card mt-3">
|
|
<div class="card-body">
|
|
<p class="mb-1 fw-bold">📥 ดาวน์โหลด Template</p>
|
|
<a href="import_csv.php?template=1<?= $student_id ? '&student_id=' . $student_id : '' ?>" class="btn btn-outline-success btn-sm"><i class="bi bi-download"></i> template.csv</a>
|
|
</div>
|
|
</div>
|
|
</div>
|
|
|
|
<div class="col-md-6">
|
|
<?php if ($total_success > 0 || $total_error > 0): ?>
|
|
<div class="card">
|
|
<div class="card-header bg-<?= $total_error > 0 ? 'warning' : 'success' ?> text-white d-flex justify-content-between">
|
|
<span><i class="bi bi-list-check"></i> ผลลัพธ์</span>
|
|
<span>สำเร็จ <?= $total_success ?> | ล้มเหลว <?= $total_error ?></span>
|
|
</div>
|
|
<div class="card-body" style="max-height:500px;overflow-y:auto">
|
|
<ul class="list-unstyled mb-0 small">
|
|
<?php foreach ($results as $r): ?>
|
|
<li class="mb-1 <?= str_starts_with($r, 'แถว') && str_contains($r, '✓') ? 'text-success' : (str_starts_with($r, 'แถว') ? 'text-danger' : 'text-muted') ?>"><?= htmlspecialchars($r) ?></li>
|
|
<?php endforeach; ?>
|
|
</ul>
|
|
</div>
|
|
</div>
|
|
<?php endif; ?>
|
|
</div>
|
|
</div>
|
|
|
|
<?php require_once __DIR__ . '/../includes/footer.php'; ?>
|