1476 lines
53 KiB
JavaScript
Executable File
1476 lines
53 KiB
JavaScript
Executable File
// Production Server - Serves both Frontend and API
|
||
import express from 'express';
|
||
import cors from 'cors';
|
||
import path from 'path';
|
||
import { fileURLToPath } from 'url';
|
||
import dotenv from 'dotenv';
|
||
import mysql from 'mysql2/promise';
|
||
|
||
const __filename = fileURLToPath(import.meta.url);
|
||
const __dirname = path.dirname(__filename);
|
||
|
||
// Optional compression for better bandwidth efficiency
|
||
let compression;
|
||
try {
|
||
compression = (await import('compression')).default;
|
||
console.log('✅ Compression enabled');
|
||
} catch (e) {
|
||
console.log('ℹ️ Compression not found. Run "npm install compression" for better performance.');
|
||
}
|
||
|
||
dotenv.config({ path: path.join(__dirname, '.env') });
|
||
|
||
const app = express();
|
||
if (compression) {
|
||
app.use(compression());
|
||
}
|
||
const PORT = process.env.API_PORT || process.env.PORT || 3001;
|
||
|
||
// Database connection pool
|
||
const pool = mysql.createPool({
|
||
host: process.env.DB_HOST || 'localhost',
|
||
user: process.env.DB_USER || 'root',
|
||
password: process.env.DB_PASSWORD?.replace(/^['"]|['"]$/g, '') || '',
|
||
database: process.env.DB_NAME || 'jurnal_db',
|
||
port: process.env.DB_PORT || 3306,
|
||
waitForConnections: true,
|
||
connectionLimit: 10,
|
||
queueLimit: 0
|
||
});
|
||
|
||
// Test database connection and initialize tables
|
||
const initializeDatabase = async () => {
|
||
let connection;
|
||
try {
|
||
connection = await pool.getConnection();
|
||
console.log('✅ Database connected successfully!');
|
||
|
||
// 1. Users Table
|
||
await connection.query(`
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL,
|
||
identifier VARCHAR(50) UNIQUE,
|
||
password VARCHAR(255) NOT NULL,
|
||
role ENUM('ADMIN', 'GURU', 'SEKRETARIS') NOT NULL DEFAULT 'GURU',
|
||
class_name VARCHAR(20) NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_identifier (identifier)
|
||
) ENGINE=InnoDB
|
||
`);
|
||
|
||
// 2. Teachers Table
|
||
await connection.query(`
|
||
CREATE TABLE IF NOT EXISTS teachers (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL,
|
||
nip VARCHAR(30) UNIQUE,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_nip (nip)
|
||
) ENGINE=InnoDB
|
||
`);
|
||
|
||
// 3. Students Table
|
||
await connection.query(`
|
||
CREATE TABLE IF NOT EXISTS students (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL,
|
||
nis VARCHAR(20) UNIQUE,
|
||
class_name VARCHAR(20) NOT NULL,
|
||
gender ENUM('L', 'P') NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_nis (nis),
|
||
INDEX idx_class (class_name)
|
||
) ENGINE=InnoDB
|
||
`);
|
||
|
||
// 4. Subjects Table
|
||
await connection.query(`
|
||
CREATE TABLE IF NOT EXISTS subjects (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(100) NOT NULL UNIQUE,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB
|
||
`);
|
||
|
||
// 5. Journal Entries Table
|
||
await connection.query(`
|
||
CREATE TABLE IF NOT EXISTS journal_entries (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
journal_id VARCHAR(50) UNIQUE NOT NULL,
|
||
date DATE NOT NULL,
|
||
teacher_name VARCHAR(100) NOT NULL,
|
||
nip VARCHAR(30),
|
||
class_name VARCHAR(20) NOT NULL,
|
||
subject VARCHAR(100) NOT NULL,
|
||
topic TEXT,
|
||
start_time VARCHAR(10),
|
||
end_time VARCHAR(10),
|
||
students_present INT DEFAULT 0,
|
||
sakit INT DEFAULT 0,
|
||
izin INT DEFAULT 0,
|
||
alpha INT DEFAULT 0,
|
||
dispen INT DEFAULT 0,
|
||
notes TEXT,
|
||
photo_url LONGTEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_journal_id (journal_id),
|
||
INDEX idx_date (date),
|
||
INDEX idx_class (class_name)
|
||
) ENGINE=InnoDB
|
||
`);
|
||
|
||
// 6. Class Journal Entries Table
|
||
await connection.query(`
|
||
CREATE TABLE IF NOT EXISTS class_journal_entries (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
journal_id VARCHAR(50) UNIQUE NOT NULL,
|
||
date DATE NOT NULL,
|
||
teacher_name VARCHAR(100) NOT NULL,
|
||
subject VARCHAR(100) NOT NULL,
|
||
class_name VARCHAR(20) NOT NULL,
|
||
teacher_presence ENUM('Hadir', 'Tugas', 'Tidak Hadir') DEFAULT 'Hadir',
|
||
start_time VARCHAR(10),
|
||
end_time VARCHAR(10),
|
||
students_present INT DEFAULT 0,
|
||
sakit INT DEFAULT 0,
|
||
izin INT DEFAULT 0,
|
||
alpha INT DEFAULT 0,
|
||
dispen INT DEFAULT 0,
|
||
notes TEXT,
|
||
class_secretary VARCHAR(100),
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_journal_id (journal_id),
|
||
INDEX idx_date (date),
|
||
INDEX idx_class (class_name)
|
||
) ENGINE=InnoDB
|
||
`);
|
||
|
||
// 7. Attendance Details Table
|
||
await connection.query(`
|
||
CREATE TABLE IF NOT EXISTS attendance_details (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
journal_type ENUM('teacher', 'class') NOT NULL,
|
||
journal_id VARCHAR(50) NOT NULL,
|
||
student_name VARCHAR(100) NOT NULL,
|
||
status CHAR(1) NOT NULL DEFAULT 'H',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_journal (journal_type, journal_id)
|
||
) ENGINE=InnoDB
|
||
`);
|
||
|
||
// 8. School Settings Table
|
||
await connection.query(`
|
||
CREATE TABLE IF NOT EXISTS school_settings (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
setting_key VARCHAR(50) UNIQUE NOT NULL,
|
||
setting_value TEXT,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB
|
||
`);
|
||
|
||
// 9. Classes Table (for class management)
|
||
await connection.query(`
|
||
CREATE TABLE IF NOT EXISTS classes (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(50) NOT NULL UNIQUE,
|
||
grade VARCHAR(10) NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
INDEX idx_name (name),
|
||
INDEX idx_grade (grade)
|
||
) ENGINE=InnoDB
|
||
`);
|
||
|
||
// Ensure default settings exist
|
||
await connection.query(`
|
||
INSERT IGNORE INTO school_settings (setting_key, setting_value) VALUES
|
||
('semester', 'Ganjil'), ('academic_year', '2025/2026'),
|
||
('headmaster_name', ''), ('headmaster_nip', ''),
|
||
('logo_url', ''), ('kop_url', '')
|
||
`);
|
||
|
||
// Alter photo_url column to LONGTEXT if it exists (for existing databases)
|
||
try {
|
||
await connection.query(`ALTER TABLE journal_entries MODIFY COLUMN photo_url LONGTEXT`);
|
||
console.log('✅ photo_url column altered to LONGTEXT');
|
||
} catch (alterErr) {
|
||
// Column might already be LONGTEXT or table doesn't exist yet - ignore
|
||
console.log('ℹ️ photo_url column alteration skipped (may already be correct type)');
|
||
}
|
||
|
||
// Ensure Admin user exists
|
||
await connection.query(`
|
||
INSERT IGNORE INTO users (name, identifier, password, role) VALUES
|
||
('Administrator', 'AdminJurnal#1', 'Smanab100%', 'ADMIN')
|
||
`);
|
||
|
||
console.log('✅ Tables initialized/verified successfully');
|
||
connection.release();
|
||
return true;
|
||
} catch (error) {
|
||
if (connection) connection.release();
|
||
console.error('❌ Database initialization failed:', error.message);
|
||
return false;
|
||
}
|
||
};
|
||
|
||
// Middleware
|
||
app.use(cors({
|
||
origin: true,
|
||
methods: ['GET', 'POST', 'PUT', 'DELETE', 'OPTIONS'],
|
||
allowedHeaders: ['Content-Type', 'Authorization'],
|
||
credentials: true
|
||
}));
|
||
|
||
app.use(express.json({ limit: '50mb' }));
|
||
app.use(express.urlencoded({ extended: true, limit: '50mb' }));
|
||
|
||
// Logging middleware
|
||
app.use((req, res, next) => {
|
||
console.log(`${new Date().toISOString()} - ${req.method} ${req.url}`);
|
||
next();
|
||
});
|
||
|
||
// Serve static files
|
||
app.use('/images', express.static(path.join(__dirname, 'public/images')));
|
||
app.use('/uploads', express.static(path.join(__dirname, 'public/uploads')));
|
||
|
||
// ==================== CACHE IMPLEMENTATION ====================
|
||
// Simple in-memory cache for frequently accessed data
|
||
const cache = {
|
||
data: {},
|
||
ttl: {},
|
||
set(key, value, ttlSeconds = 60) {
|
||
this.data[key] = value;
|
||
this.ttl[key] = Date.now() + (ttlSeconds * 1000);
|
||
},
|
||
get(key) {
|
||
if (this.ttl[key] && Date.now() > this.ttl[key]) {
|
||
delete this.data[key];
|
||
delete this.ttl[key];
|
||
return null;
|
||
}
|
||
return this.data[key] || null;
|
||
},
|
||
clear(pattern) {
|
||
if (pattern) {
|
||
Object.keys(this.data).forEach(key => {
|
||
if (key.includes(pattern)) {
|
||
delete this.data[key];
|
||
delete this.ttl[key];
|
||
}
|
||
});
|
||
} else {
|
||
this.data = {};
|
||
this.ttl = {};
|
||
}
|
||
}
|
||
};
|
||
|
||
// ==================== API ROUTES ====================
|
||
|
||
// Health check
|
||
app.get('/api/health', async (req, res) => {
|
||
const dbConnected = await initializeDatabase();
|
||
res.json({
|
||
status: 'ok',
|
||
database: dbConnected ? 'connected' : 'disconnected',
|
||
timestamp: new Date().toISOString()
|
||
});
|
||
});
|
||
|
||
// ==================== OPTIMIZED ENDPOINTS FOR FASTER LOADING ====================
|
||
|
||
// Fast login endpoint - only fetches user data, not entire database
|
||
app.post('/api/auth/login', async (req, res) => {
|
||
try {
|
||
const { identifier, password, role } = req.body;
|
||
|
||
if (!identifier || !password) {
|
||
return res.status(400).json({ status: 'error', message: 'Identifier dan password wajib diisi' });
|
||
}
|
||
|
||
// For ADMIN login
|
||
if (role === 'ADMIN' || (!role && identifier.toLowerCase().includes('admin'))) {
|
||
const [users] = await pool.query(
|
||
'SELECT * FROM users WHERE identifier = ? AND password = ? AND role = ?',
|
||
[identifier, password, 'ADMIN']
|
||
);
|
||
if (users.length > 0) {
|
||
return res.json({
|
||
status: 'success',
|
||
data: {
|
||
id: users[0].id,
|
||
name: users[0].name,
|
||
role: 'ADMIN',
|
||
identifier: users[0].identifier
|
||
}
|
||
});
|
||
}
|
||
}
|
||
|
||
// For GURU login (using NIP) - password is same as NIP
|
||
if (role === 'GURU') {
|
||
const [teachers] = await pool.query('SELECT * FROM teachers WHERE nip = ?', [identifier]);
|
||
if (teachers.length > 0 && password === identifier) {
|
||
return res.json({
|
||
status: 'success',
|
||
data: {
|
||
id: teachers[0].id,
|
||
name: teachers[0].name,
|
||
role: 'GURU',
|
||
identifier: teachers[0].nip
|
||
}
|
||
});
|
||
}
|
||
}
|
||
|
||
// For SEKRETARIS (student class secretary) login (using NIS) - password is same as NIS
|
||
if (role === 'SEKRETARIS') {
|
||
const [students] = await pool.query('SELECT * FROM students WHERE nis = ?', [identifier]);
|
||
if (students.length > 0 && password === identifier) {
|
||
return res.json({
|
||
status: 'success',
|
||
data: {
|
||
id: students[0].id,
|
||
name: students[0].name,
|
||
role: 'SEKRETARIS',
|
||
identifier: students[0].nis,
|
||
className: students[0].class_name
|
||
}
|
||
});
|
||
}
|
||
}
|
||
|
||
res.status(401).json({ status: 'error', message: 'Identifier atau password salah' });
|
||
} catch (error) {
|
||
console.error('Error during login:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// Get student by NIS - fast lookup
|
||
app.get('/api/students/nis/:nis', async (req, res) => {
|
||
try {
|
||
const { nis } = req.params;
|
||
const [students] = await pool.query(
|
||
'SELECT * FROM students WHERE nis = ?',
|
||
[nis]
|
||
);
|
||
|
||
if (students.length === 0) {
|
||
return res.status(404).json({ status: 'error', message: 'Siswa tidak ditemukan' });
|
||
}
|
||
|
||
res.json({ status: 'success', data: students[0] });
|
||
} catch (error) {
|
||
console.error('Error fetching student by NIS:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// Get students by class with pagination - for large datasets
|
||
app.get('/api/students/class/:className', async (req, res) => {
|
||
try {
|
||
const { className } = req.params;
|
||
const page = parseInt(req.query.page) || 1;
|
||
const limit = parseInt(req.query.limit) || 50;
|
||
const offset = (page - 1) * limit;
|
||
|
||
// Check cache first
|
||
const cacheKey = `students_${className}_${page}_${limit}`;
|
||
const cached = cache.get(cacheKey);
|
||
if (cached) {
|
||
return res.json(cached);
|
||
}
|
||
|
||
const [students] = await pool.query(
|
||
'SELECT id, name, nis, class_name, gender FROM students WHERE class_name = ? ORDER BY name LIMIT ? OFFSET ?',
|
||
[className, limit, offset]
|
||
);
|
||
|
||
const [countResult] = await pool.query(
|
||
'SELECT COUNT(*) as total FROM students WHERE class_name = ?',
|
||
[className]
|
||
);
|
||
|
||
const response = {
|
||
status: 'success',
|
||
data: students,
|
||
pagination: {
|
||
page,
|
||
limit,
|
||
total: countResult[0].total,
|
||
totalPages: Math.ceil(countResult[0].total / limit)
|
||
}
|
||
};
|
||
|
||
// Cache for 30 seconds
|
||
cache.set(cacheKey, response, 30);
|
||
|
||
res.json(response);
|
||
} catch (error) {
|
||
console.error('Error fetching students by class:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// ==================== ULTRA-LITE ENDPOINT - For Fast Initial Load ====================
|
||
// This endpoint returns ONLY the minimum data needed for the login screen
|
||
// NO journals, NO attendance details - those are loaded after login
|
||
app.get('/api/data/ultra-lite', async (req, res) => {
|
||
try {
|
||
// Check cache first - cache for 5 minutes since this is static data
|
||
const cached = cache.get('data_ultra_lite');
|
||
if (cached) {
|
||
return res.json(cached);
|
||
}
|
||
|
||
// Parallel queries for essential data only
|
||
const [
|
||
[teachers],
|
||
[classesFromTable],
|
||
[classNamesFromStudents],
|
||
[subjects],
|
||
[settingsRows],
|
||
[students]
|
||
] = await Promise.all([
|
||
pool.query('SELECT id, name, nip FROM teachers ORDER BY name'),
|
||
pool.query('SELECT id, name, grade FROM classes ORDER BY grade, name'),
|
||
pool.query('SELECT DISTINCT class_name FROM students WHERE class_name IS NOT NULL AND class_name != "" ORDER BY class_name'),
|
||
pool.query('SELECT name FROM subjects ORDER BY name'),
|
||
pool.query('SELECT setting_key, setting_value FROM school_settings'),
|
||
pool.query('SELECT id, name, nis, class_name, gender FROM students ORDER BY class_name, name')
|
||
]);
|
||
|
||
const settings = {};
|
||
settingsRows.forEach(row => {
|
||
settings[row.setting_key] = row.setting_value;
|
||
});
|
||
|
||
// Merge classes
|
||
const classSet = new Set();
|
||
classesFromTable.forEach(c => classSet.add(c.name));
|
||
classNamesFromStudents.forEach(c => {
|
||
if (c.class_name) classSet.add(c.class_name);
|
||
});
|
||
const mergedClasses = Array.from(classSet).sort();
|
||
|
||
const response = {
|
||
status: 'success',
|
||
data: {
|
||
guru: teachers.map(t => ({ 'Nama Guru': t.name, 'NIP': t.nip || '' })),
|
||
murid: students.map(s => ({ 'Nama Murid': s.name, 'NIS': s.nis || '', 'Kelas': s.class_name, 'L/P': s.gender || '' })),
|
||
classes: mergedClasses,
|
||
classesData: classesFromTable,
|
||
mapel: subjects.map(s => ({ 'Mata Pelajaran': s.name })),
|
||
// NO jurnal - loaded after login
|
||
jurnal: [],
|
||
jurnalKelas: [],
|
||
settings: {
|
||
'Semester': settings.semester || 'Ganjil',
|
||
'TahunPelajaran': settings.academic_year || '2024/2025',
|
||
'NamaKepalaSekolah': settings.headmaster_name || '',
|
||
'NipKepalaSekolah': settings.headmaster_nip || '',
|
||
'LogoUrl': settings.logo_url || '',
|
||
'KopUrl': settings.kop_url || ''
|
||
}
|
||
}
|
||
};
|
||
|
||
// Cache for 5 minutes - this data rarely changes
|
||
cache.set('data_ultra_lite', response, 300);
|
||
|
||
res.json(response);
|
||
} catch (error) {
|
||
console.error('Error fetching ultra-lite data:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// Lightweight data endpoint - only essential data for initial load
|
||
app.get('/api/data/lite', async (req, res) => {
|
||
try {
|
||
// Check cache first
|
||
const cached = cache.get('data_lite');
|
||
if (cached) {
|
||
return res.json(cached);
|
||
}
|
||
|
||
// Parallel queries for faster loading - now includes journals for recap
|
||
const [
|
||
[teachers],
|
||
[classesFromTable],
|
||
[classNamesFromStudents],
|
||
[subjects],
|
||
[settingsRows],
|
||
[students],
|
||
[journals],
|
||
[classJournals]
|
||
] = await Promise.all([
|
||
pool.query('SELECT id, name, nip FROM teachers ORDER BY name'),
|
||
pool.query('SELECT id, name, grade FROM classes ORDER BY grade, name'),
|
||
pool.query('SELECT DISTINCT class_name FROM students WHERE class_name IS NOT NULL AND class_name != "" ORDER BY class_name'),
|
||
pool.query('SELECT name FROM subjects ORDER BY name'),
|
||
pool.query('SELECT setting_key, setting_value FROM school_settings'),
|
||
pool.query('SELECT id, name, nis, class_name, gender FROM students ORDER BY class_name, name'),
|
||
// Load ALL journal entries - periodical data must be complete to prevent double entries
|
||
pool.query("SELECT *, DATE_FORMAT(date, '%Y-%m-%d') as formatted_date FROM journal_entries ORDER BY created_at DESC"),
|
||
pool.query("SELECT *, DATE_FORMAT(date, '%Y-%m-%d') as formatted_date FROM class_journal_entries ORDER BY created_at DESC")
|
||
]);
|
||
|
||
const settings = {};
|
||
settingsRows.forEach(row => {
|
||
settings[row.setting_key] = row.setting_value;
|
||
});
|
||
|
||
// Merge classes from both sources (prefer classes table, supplement with student class names)
|
||
const classSet = new Set();
|
||
classesFromTable.forEach(c => classSet.add(c.name));
|
||
classNamesFromStudents.forEach(c => {
|
||
if (c.class_name) classSet.add(c.class_name);
|
||
});
|
||
const mergedClasses = Array.from(classSet).sort();
|
||
|
||
// Fetch attendance details for journals
|
||
const journalIds = journals.map(j => j.journal_id);
|
||
const classJournalIds = classJournals.map(j => j.journal_id);
|
||
|
||
let teacherAttendanceMap = {};
|
||
let classAttendanceMap = {};
|
||
|
||
const attendanceQueries = [];
|
||
if (journalIds.length > 0) {
|
||
attendanceQueries.push(
|
||
pool.query('SELECT journal_id, student_name, status FROM attendance_details WHERE journal_type = ? AND journal_id IN (?)', ['teacher', journalIds])
|
||
);
|
||
} else {
|
||
attendanceQueries.push(Promise.resolve([[]]));
|
||
}
|
||
|
||
if (classJournalIds.length > 0) {
|
||
attendanceQueries.push(
|
||
pool.query('SELECT journal_id, student_name, status FROM attendance_details WHERE journal_type = ? AND journal_id IN (?)', ['class', classJournalIds])
|
||
);
|
||
} else {
|
||
attendanceQueries.push(Promise.resolve([[]]));
|
||
}
|
||
|
||
const [[teacherAttDetails], [classAttDetails]] = await Promise.all(attendanceQueries);
|
||
|
||
teacherAttDetails.forEach(ad => {
|
||
if (!teacherAttendanceMap[ad.journal_id]) teacherAttendanceMap[ad.journal_id] = [];
|
||
teacherAttendanceMap[ad.journal_id].push({ studentName: ad.student_name, status: ad.status });
|
||
});
|
||
|
||
classAttDetails.forEach(ad => {
|
||
if (!classAttendanceMap[ad.journal_id]) classAttendanceMap[ad.journal_id] = [];
|
||
classAttendanceMap[ad.journal_id].push({ studentName: ad.student_name, status: ad.status });
|
||
});
|
||
|
||
const response = {
|
||
status: 'success',
|
||
data: {
|
||
guru: teachers.map(t => ({ 'Nama Guru': t.name, 'NIP': t.nip || '' })),
|
||
murid: students.map(s => ({ 'Nama Murid': s.name, 'NIS': s.nis || '', 'Kelas': s.class_name, 'L/P': s.gender || '' })),
|
||
classes: mergedClasses,
|
||
classesData: classesFromTable,
|
||
mapel: subjects.map(s => ({ 'Mata Pelajaran': s.name })),
|
||
jurnal: journals.map(j => ({
|
||
'ID Jurnal': j.journal_id,
|
||
'Tanggal': j.formatted_date || j.date,
|
||
'Timestamp': j.created_at,
|
||
'Nama Guru': j.teacher_name,
|
||
'NIP': j.nip || '',
|
||
'Kelas': j.class_name,
|
||
'Mata Pelajaran': j.subject,
|
||
'Materi': j.topic || '',
|
||
'Jam Ke': `${j.start_time || ''} - ${j.end_time || ''}`,
|
||
'Jumlah Hadir': j.students_present,
|
||
'Jumlah Sakit': j.sakit,
|
||
'Jumlah Izin': j.izin,
|
||
'Jumlah Alpha': j.alpha,
|
||
'Jumlah Dispen': j.dispen,
|
||
'Keterangan': j.notes || '',
|
||
'Foto': j.photo_url || '',
|
||
'Detail Absensi': teacherAttendanceMap[j.journal_id] || []
|
||
})),
|
||
jurnalKelas: classJournals.map(j => ({
|
||
'ID Jurnal Kelas': j.journal_id,
|
||
'Tanggal': j.formatted_date || j.date,
|
||
'Timestamp': j.created_at,
|
||
'Nama Guru': j.teacher_name,
|
||
'Mata Pelajaran': j.subject,
|
||
'Kelas': j.class_name,
|
||
'Kehadiran Guru': j.teacher_presence,
|
||
'Jam Ke': `${j.start_time || ''} - ${j.end_time || ''}`,
|
||
'Jumlah Hadir': j.students_present,
|
||
'Jumlah Sakit': j.sakit,
|
||
'Jumlah Izin': j.izin,
|
||
'Jumlah Alpha': j.alpha,
|
||
'Jumlah Dispen': j.dispen,
|
||
'Keterangan': j.notes || '',
|
||
'Sekretaris Kelas': j.class_secretary || '',
|
||
'Detail Absensi': classAttendanceMap[j.journal_id] || []
|
||
})),
|
||
settings: {
|
||
'Semester': settings.semester || 'Ganjil',
|
||
'TahunPelajaran': settings.academic_year || '2024/2025',
|
||
'NamaKepalaSekolah': settings.headmaster_name || '',
|
||
'NipKepalaSekolah': settings.headmaster_nip || '',
|
||
'LogoUrl': settings.logo_url || '',
|
||
'KopUrl': settings.kop_url || ''
|
||
}
|
||
}
|
||
};
|
||
|
||
// Cache for 30 seconds (same as full data)
|
||
cache.set('data_lite', response, 30);
|
||
|
||
res.json(response);
|
||
} catch (error) {
|
||
console.error('Error fetching lite data:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
|
||
// ==================== TEACHERS CRUD ====================
|
||
|
||
// GET all teachers
|
||
app.get('/api/teachers', async (req, res) => {
|
||
try {
|
||
const [teachers] = await pool.query('SELECT * FROM teachers ORDER BY name');
|
||
res.json({ status: 'success', data: teachers });
|
||
} catch (error) {
|
||
console.error('Error fetching teachers:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// POST new teacher
|
||
app.post('/api/teachers', async (req, res) => {
|
||
try {
|
||
const { name, nip } = req.body;
|
||
if (!name) {
|
||
return res.status(400).json({ status: 'error', message: 'Nama guru wajib diisi' });
|
||
}
|
||
const [result] = await pool.query('INSERT INTO teachers (name, nip) VALUES (?, ?)', [name, nip || null]);
|
||
res.json({ status: 'success', message: 'Guru berhasil ditambahkan', data: { id: result.insertId, name, nip } });
|
||
} catch (error) {
|
||
if (error.code === 'ER_DUP_ENTRY') {
|
||
return res.status(400).json({ status: 'error', message: 'NIP sudah terdaftar' });
|
||
}
|
||
console.error('Error adding teacher:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// PUT update teacher
|
||
app.put('/api/teachers/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
const { name, nip } = req.body;
|
||
await pool.query('UPDATE teachers SET name = ?, nip = ? WHERE id = ?', [name, nip, id]);
|
||
res.json({ status: 'success', message: 'Data guru berhasil diupdate' });
|
||
} catch (error) {
|
||
console.error('Error updating teacher:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// DELETE teacher
|
||
app.delete('/api/teachers/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
console.log(`[DELETE] Deleting teacher with ID: ${id}`);
|
||
await pool.query('DELETE FROM teachers WHERE id = ?', [id]);
|
||
res.json({ status: 'success', message: 'Guru berhasil dihapus' });
|
||
} catch (error) {
|
||
console.error('Error deleting teacher:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// ==================== STUDENTS CRUD ====================
|
||
|
||
// GET all students
|
||
app.get('/api/students', async (req, res) => {
|
||
try {
|
||
const { className } = req.query;
|
||
let query = 'SELECT * FROM students ORDER BY class_name, name';
|
||
let params = [];
|
||
if (className) {
|
||
query = 'SELECT * FROM students WHERE class_name = ? ORDER BY name';
|
||
params = [className];
|
||
}
|
||
const [students] = await pool.query(query, params);
|
||
res.json({ status: 'success', data: students });
|
||
} catch (error) {
|
||
console.error('Error fetching students:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// GET unique class names
|
||
app.get('/api/students/classes', async (req, res) => {
|
||
try {
|
||
const [classes] = await pool.query('SELECT DISTINCT class_name FROM students ORDER BY class_name');
|
||
res.json({ status: 'success', data: classes.map(c => c.class_name) });
|
||
} catch (error) {
|
||
console.error('Error fetching classes:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// POST new student
|
||
app.post('/api/students', async (req, res) => {
|
||
try {
|
||
const { name, nis, class_name, gender } = req.body;
|
||
if (!name || !class_name) {
|
||
return res.status(400).json({ status: 'error', message: 'Nama dan kelas wajib diisi' });
|
||
}
|
||
const [result] = await pool.query(
|
||
'INSERT INTO students (name, nis, class_name, gender) VALUES (?, ?, ?, ?)',
|
||
[name, nis || null, class_name, gender || null]
|
||
);
|
||
res.json({ status: 'success', message: 'Siswa berhasil ditambahkan', data: { id: result.insertId, name, nis, class_name, gender } });
|
||
} catch (error) {
|
||
if (error.code === 'ER_DUP_ENTRY') {
|
||
return res.status(400).json({ status: 'error', message: 'NIS sudah terdaftar' });
|
||
}
|
||
console.error('Error adding student:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// PUT update student
|
||
app.put('/api/students/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
const { name, nis, class_name, gender } = req.body;
|
||
await pool.query(
|
||
'UPDATE students SET name = ?, nis = ?, class_name = ?, gender = ? WHERE id = ?',
|
||
[name, nis, class_name, gender, id]
|
||
);
|
||
res.json({ status: 'success', message: 'Data siswa berhasil diupdate' });
|
||
} catch (error) {
|
||
console.error('Error updating student:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// DELETE student
|
||
app.delete('/api/students/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
console.log(`[DELETE] Deleting student with ID: ${id}`);
|
||
await pool.query('DELETE FROM students WHERE id = ?', [id]);
|
||
res.json({ status: 'success', message: 'Siswa berhasil dihapus' });
|
||
} catch (error) {
|
||
console.error('Error deleting student:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// ==================== CLASSES CRUD ====================
|
||
|
||
// GET all classes
|
||
app.get('/api/classes', async (req, res) => {
|
||
try {
|
||
const [classes] = await pool.query('SELECT * FROM classes ORDER BY grade, name');
|
||
res.json({ status: 'success', data: classes });
|
||
} catch (error) {
|
||
console.error('Error fetching classes:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// POST new class
|
||
app.post('/api/classes', async (req, res) => {
|
||
try {
|
||
const { name, grade } = req.body;
|
||
if (!name || !grade) {
|
||
return res.status(400).json({ status: 'error', message: 'Nama dan tingkat kelas wajib diisi' });
|
||
}
|
||
const [result] = await pool.query('INSERT INTO classes (name, grade) VALUES (?, ?)', [name, grade]);
|
||
res.json({ status: 'success', message: 'Kelas berhasil ditambahkan', data: { id: result.insertId, name, grade } });
|
||
} catch (error) {
|
||
if (error.code === 'ER_DUP_ENTRY') {
|
||
return res.status(400).json({ status: 'error', message: 'Nama kelas sudah ada' });
|
||
}
|
||
console.error('Error adding class:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// PUT update class
|
||
app.put('/api/classes/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
const { name, grade } = req.body;
|
||
await pool.query('UPDATE classes SET name = ?, grade = ? WHERE id = ?', [name, grade, id]);
|
||
res.json({ status: 'success', message: 'Data kelas berhasil diupdate' });
|
||
} catch (error) {
|
||
console.error('Error updating class:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// DELETE class
|
||
app.delete('/api/classes/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
await pool.query('DELETE FROM classes WHERE id = ?', [id]);
|
||
res.json({ status: 'success', message: 'Kelas berhasil dihapus' });
|
||
} catch (error) {
|
||
console.error('Error deleting class:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// POST promote classes (Naik Kelas)
|
||
app.post('/api/promote-classes', async (req, res) => {
|
||
const connection = await pool.getConnection();
|
||
try {
|
||
await connection.beginTransaction();
|
||
|
||
// 1. XII -> Lulus
|
||
// Update students in classes with grade XII to class_name 'LULUS'
|
||
await connection.query(`
|
||
UPDATE students
|
||
SET class_name = 'LULUS'
|
||
WHERE class_name IN (SELECT name FROM classes WHERE grade = 'XII')
|
||
`);
|
||
|
||
// 2. XI -> XII
|
||
// We need to find the "next" class name.
|
||
// Usually it's replacing 'XI' with 'XII' in the name.
|
||
const [xiClasses] = await connection.query("SELECT name FROM classes WHERE grade = 'XI'");
|
||
for (const cls of xiClasses) {
|
||
const nextClassName = cls.name.replace('XI', 'XII');
|
||
|
||
// Check if XII class exists
|
||
const [exists] = await connection.query("SELECT id FROM classes WHERE name = ?", [nextClassName]);
|
||
if (exists.length > 0) {
|
||
await connection.query("UPDATE students SET class_name = ? WHERE class_name = ?", [nextClassName, cls.name]);
|
||
}
|
||
}
|
||
|
||
// 3. X -> XI
|
||
const [xClasses] = await connection.query("SELECT name FROM classes WHERE grade = 'X'");
|
||
for (const cls of xClasses) {
|
||
const nextClassName = cls.name.replace('X', 'XI');
|
||
|
||
// Check if XI class exists
|
||
const [exists] = await connection.query("SELECT id FROM classes WHERE name = ?", [nextClassName]);
|
||
if (exists.length > 0) {
|
||
await connection.query("UPDATE students SET class_name = ? WHERE class_name = ?", [nextClassName, cls.name]);
|
||
}
|
||
}
|
||
|
||
// 4. Update the grades of the classes themselves?
|
||
// User said "mempermudah periodikal".
|
||
// Usually, we don't change class names (e.g., X MIPA 1 is always X MIPA 1).
|
||
// But students move from X MIPA 1 to XI MIPA 1.
|
||
// So my above logic of updating students' class_name is correct.
|
||
|
||
await connection.commit();
|
||
res.json({ status: 'success', message: 'Proses kenaikan kelas berhasil diselesaikan' });
|
||
} catch (error) {
|
||
await connection.rollback();
|
||
console.error('Error promoting classes:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
} finally {
|
||
connection.release();
|
||
}
|
||
});
|
||
|
||
// ==================== SUBJECTS CRUD ====================
|
||
|
||
// GET all subjects
|
||
app.get('/api/subjects', async (req, res) => {
|
||
try {
|
||
const [subjects] = await pool.query('SELECT * FROM subjects ORDER BY name');
|
||
res.json({ status: 'success', data: subjects });
|
||
} catch (error) {
|
||
console.error('Error fetching subjects:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// POST new subject
|
||
app.post('/api/subjects', async (req, res) => {
|
||
try {
|
||
const { name } = req.body;
|
||
if (!name) {
|
||
return res.status(400).json({ status: 'error', message: 'Nama mata pelajaran wajib diisi' });
|
||
}
|
||
const [result] = await pool.query('INSERT INTO subjects (name) VALUES (?)', [name]);
|
||
res.json({ status: 'success', message: 'Mata pelajaran berhasil ditambahkan', data: { id: result.insertId, name } });
|
||
} catch (error) {
|
||
if (error.code === 'ER_DUP_ENTRY') {
|
||
return res.status(400).json({ status: 'error', message: 'Mata pelajaran sudah ada' });
|
||
}
|
||
console.error('Error adding subject:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// PUT update subject
|
||
app.put('/api/subjects/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
const { name } = req.body;
|
||
await pool.query('UPDATE subjects SET name = ? WHERE id = ?', [name, id]);
|
||
res.json({ status: 'success', message: 'Mata pelajaran berhasil diupdate' });
|
||
} catch (error) {
|
||
console.error('Error updating subject:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// DELETE subject
|
||
app.delete('/api/subjects/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
console.log(`[DELETE] Deleting subject with ID: ${id}`);
|
||
await pool.query('DELETE FROM subjects WHERE id = ?', [id]);
|
||
res.json({ status: 'success', message: 'Mata pelajaran berhasil dihapus' });
|
||
} catch (error) {
|
||
console.error('Error deleting subject:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// ==================== DATA ENDPOINT ====================
|
||
|
||
app.get('/api/data', async (req, res) => {
|
||
try {
|
||
// Check cache first
|
||
const cached = cache.get('data_full');
|
||
if (cached) {
|
||
return res.json(cached);
|
||
}
|
||
|
||
// Parallel queries for faster loading
|
||
const [
|
||
[teachers],
|
||
[students],
|
||
[subjects],
|
||
[classes],
|
||
[journals],
|
||
[classJournals],
|
||
[settingsRows]
|
||
] = await Promise.all([
|
||
pool.query('SELECT id, name, nip FROM teachers ORDER BY name'),
|
||
pool.query('SELECT id, name, nis, class_name, gender FROM students ORDER BY class_name, name'),
|
||
pool.query('SELECT name FROM subjects ORDER BY name'),
|
||
pool.query('SELECT * FROM classes ORDER BY grade, name'),
|
||
pool.query("SELECT *, DATE_FORMAT(date, '%Y-%m-%d') as formatted_date FROM journal_entries ORDER BY created_at DESC"),
|
||
pool.query("SELECT *, DATE_FORMAT(date, '%Y-%m-%d') as formatted_date FROM class_journal_entries ORDER BY created_at DESC"),
|
||
pool.query('SELECT setting_key, setting_value FROM school_settings')
|
||
]);
|
||
|
||
// Fetch attendance details efficiently only for the journals returned
|
||
let teacherAttendanceMap = {};
|
||
let classAttendanceMap = {};
|
||
|
||
const journalIds = journals.map(j => j.journal_id);
|
||
const classJournalIds = classJournals.map(j => j.journal_id);
|
||
|
||
// Optimized attendance details fetching using IN clause instead of fetching everything
|
||
const attendanceQueries = [];
|
||
|
||
if (journalIds.length > 0) {
|
||
attendanceQueries.push(
|
||
pool.query('SELECT journal_id, student_name, status FROM attendance_details WHERE journal_type = ? AND journal_id IN (?)', ['teacher', journalIds])
|
||
);
|
||
} else {
|
||
attendanceQueries.push(Promise.resolve([[]]));
|
||
}
|
||
|
||
if (classJournalIds.length > 0) {
|
||
attendanceQueries.push(
|
||
pool.query('SELECT journal_id, student_name, status FROM attendance_details WHERE journal_type = ? AND journal_id IN (?)', ['class', classJournalIds])
|
||
);
|
||
} else {
|
||
attendanceQueries.push(Promise.resolve([[]]));
|
||
}
|
||
|
||
const [[teacherAttDetails], [classAttDetails]] = await Promise.all(attendanceQueries);
|
||
|
||
// Group teacher attendance details
|
||
teacherAttDetails.forEach(ad => {
|
||
if (!teacherAttendanceMap[ad.journal_id]) teacherAttendanceMap[ad.journal_id] = [];
|
||
teacherAttendanceMap[ad.journal_id].push({
|
||
studentName: ad.student_name,
|
||
status: ad.status
|
||
});
|
||
});
|
||
|
||
// Group class attendance details
|
||
classAttDetails.forEach(ad => {
|
||
if (!classAttendanceMap[ad.journal_id]) classAttendanceMap[ad.journal_id] = [];
|
||
classAttendanceMap[ad.journal_id].push({
|
||
studentName: ad.student_name,
|
||
status: ad.status
|
||
});
|
||
});
|
||
|
||
const settings = {};
|
||
settingsRows.forEach(row => {
|
||
settings[row.setting_key] = row.setting_value;
|
||
});
|
||
|
||
const response = {
|
||
status: 'success',
|
||
data: {
|
||
guru: teachers.map(t => ({ 'Nama Guru': t.name, 'NIP': t.nip || '' })),
|
||
murid: students.map(s => ({ 'Nama Murid': s.name, 'NIS': s.nis || '', 'Kelas': s.class_name, 'L/P': s.gender || '' })),
|
||
mapel: subjects.map(s => ({ 'Mata Pelajaran': s.name })),
|
||
classes: classes,
|
||
jurnal: journals.map(j => ({
|
||
'ID Jurnal': j.journal_id,
|
||
'Tanggal': j.formatted_date || j.date,
|
||
'Timestamp': j.created_at,
|
||
'Nama Guru': j.teacher_name,
|
||
'NIP': j.nip || '',
|
||
'Kelas': j.class_name,
|
||
'Mata Pelajaran': j.subject,
|
||
'Materi': j.topic || '',
|
||
'Jam Ke': `${j.start_time || ''} - ${j.end_time || ''}`,
|
||
'Jumlah Hadir': j.students_present,
|
||
'Jumlah Sakit': j.sakit,
|
||
'Jumlah Izin': j.izin,
|
||
'Jumlah Alpha': j.alpha,
|
||
'Jumlah Dispen': j.dispen,
|
||
'Keterangan': j.notes || '',
|
||
'Foto': j.photo_url || '',
|
||
'Detail Absensi': teacherAttendanceMap[j.journal_id] || []
|
||
})),
|
||
jurnalKelas: classJournals.map(j => ({
|
||
'ID Jurnal Kelas': j.journal_id,
|
||
'Tanggal': j.formatted_date || j.date,
|
||
'Timestamp': j.created_at,
|
||
'Nama Guru': j.teacher_name,
|
||
'Mata Pelajaran': j.subject,
|
||
'Kelas': j.class_name,
|
||
'Kehadiran Guru': j.teacher_presence,
|
||
'Jam Ke': `${j.start_time || ''} - ${j.end_time || ''}`,
|
||
'Jumlah Hadir': j.students_present,
|
||
'Jumlah Sakit': j.sakit,
|
||
'Jumlah Izin': j.izin,
|
||
'Jumlah Alpha': j.alpha,
|
||
'Jumlah Dispen': j.dispen,
|
||
'Keterangan': j.notes || '',
|
||
'Sekretaris Kelas': j.class_secretary || '',
|
||
'Detail Absensi': classAttendanceMap[j.journal_id] || []
|
||
})),
|
||
settings: {
|
||
'Semester': settings.semester || 'Ganjil',
|
||
'TahunPelajaran': settings.academic_year || '2024/2025',
|
||
'NamaKepalaSekolah': settings.headmaster_name || '',
|
||
'NipKepalaSekolah': settings.headmaster_nip || '',
|
||
'LogoUrl': settings.logo_url || '',
|
||
'KopUrl': settings.kop_url || ''
|
||
}
|
||
}
|
||
};
|
||
|
||
// Cache the full data for 30 seconds to reduce DB load
|
||
cache.set('data_full', response, 30);
|
||
|
||
res.json(response);
|
||
} catch (error) {
|
||
console.error('Error fetching data:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// ==================== JOURNAL ENDPOINTS ====================
|
||
|
||
// POST save journal (guru)
|
||
app.post('/api/jurnal', async (req, res) => {
|
||
try {
|
||
const body = req.body;
|
||
// Handle both direct data and nested payload format from frontend
|
||
const data = body.payload || body;
|
||
const journalId = data.idJurnal || `JRN-${Date.now()}`;
|
||
|
||
// Parse jamKe if it's combined format "08:00 - 09:00"
|
||
let startTime = data.startTime;
|
||
let endTime = data.endTime;
|
||
if (data.jamKe && data.jamKe.includes('-')) {
|
||
const parts = data.jamKe.split('-').map(s => s.trim());
|
||
startTime = parts[0] || '';
|
||
endTime = parts[1] || '';
|
||
}
|
||
|
||
// Map field names from frontend format to database format
|
||
const teacherName = data.teacherName || data.namaGuru || '';
|
||
const nip = data.nip || '-';
|
||
const className = data.className || data.kelas || '';
|
||
const subject = data.subject || data.mapel || '';
|
||
const topic = data.topic || data.materi || '';
|
||
const studentsPresent = data.studentsPresent || data.jmlHadir || 0;
|
||
const sakit = data.sakit || data.jmlSakit || 0;
|
||
const izin = data.izin || data.jmlIzin || 0;
|
||
const alpha = data.alpha || data.jmlAlpha || 0;
|
||
const dispen = data.dispen || data.jmlDispen || 0;
|
||
const notes = data.notes || data.keterangan || '';
|
||
const photoUrl = data.photoUrl || data.photoBase64 || null;
|
||
const date = data.date || new Date().toISOString().split('T')[0];
|
||
|
||
await pool.query(`
|
||
INSERT INTO journal_entries
|
||
(journal_id, date, teacher_name, nip, class_name, subject, topic, start_time, end_time,
|
||
students_present, sakit, izin, alpha, dispen, notes, photo_url)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
`, [
|
||
journalId, date, teacherName, nip, className, subject,
|
||
topic, startTime, endTime, studentsPresent,
|
||
sakit, izin, alpha, dispen, notes, photoUrl
|
||
]);
|
||
|
||
// Save attendance details if provided (wrapped in try-catch to not fail main journal save)
|
||
const attendanceDetails = data.detailAbsensi || data.attendanceDetails || [];
|
||
if (Array.isArray(attendanceDetails) && attendanceDetails.length > 0) {
|
||
try {
|
||
const attendanceValues = attendanceDetails.map(ad => [
|
||
'teacher',
|
||
journalId,
|
||
ad.studentName || ad.nama || '',
|
||
ad.status || 'H'
|
||
]);
|
||
await pool.query(`
|
||
INSERT INTO attendance_details (journal_type, journal_id, student_name, status)
|
||
VALUES ?
|
||
`, [attendanceValues]);
|
||
} catch (attErr) {
|
||
console.log('Could not save attendance details (table may not exist):', attErr.message);
|
||
}
|
||
}
|
||
|
||
// Clear cache to ensure new data is visible immediately
|
||
cache.clear('data');
|
||
|
||
res.json({ status: 'success', message: 'Jurnal berhasil disimpan', journalId });
|
||
} catch (error) {
|
||
console.error('Error saving journal:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
|
||
// POST save class journal (sekretaris)
|
||
app.post('/api/jurnal-kelas', async (req, res) => {
|
||
try {
|
||
const body = req.body;
|
||
// Handle both direct data and nested payload format from frontend
|
||
const data = body.payload || body;
|
||
const journalId = data.idJurnal || `JKL-${Date.now()}`;
|
||
|
||
// Parse jamKe if it's combined format "08:00 - 09:00"
|
||
let startTime = data.startTime;
|
||
let endTime = data.endTime;
|
||
if (data.jamKe && data.jamKe.includes('-')) {
|
||
const parts = data.jamKe.split('-').map(s => s.trim());
|
||
startTime = parts[0] || '';
|
||
endTime = parts[1] || '';
|
||
}
|
||
|
||
// Map field names from frontend format to database format
|
||
const teacherName = data.teacherName || data.namaGuru || '';
|
||
const subject = data.subject || data.mapel || '';
|
||
const className = data.className || data.kelas || '';
|
||
const teacherPresence = data.teacherPresence || data.kehadiranGuru || 'Hadir';
|
||
const studentsPresent = data.studentsPresent || data.jmlHadir || 0;
|
||
const sakit = data.sakit || data.jmlSakit || 0;
|
||
const izin = data.izin || data.jmlIzin || 0;
|
||
const alpha = data.alpha || data.jmlAlpha || 0;
|
||
const dispen = data.dispen || data.jmlDispen || 0;
|
||
const notes = data.notes || data.keterangan || '';
|
||
const classSecretary = data.classSecretary || data.sekretaris || '';
|
||
const date = data.date || new Date().toISOString().split('T')[0];
|
||
|
||
await pool.query(`
|
||
INSERT INTO class_journal_entries
|
||
(journal_id, date, teacher_name, subject, class_name, teacher_presence, start_time, end_time,
|
||
students_present, sakit, izin, alpha, dispen, notes, class_secretary)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
`, [
|
||
journalId, date, teacherName, subject, className,
|
||
teacherPresence, startTime, endTime, studentsPresent,
|
||
sakit, izin, alpha, dispen, notes, classSecretary
|
||
]);
|
||
|
||
// Save attendance details if provided (wrapped in try-catch to not fail main journal save)
|
||
const attendanceDetails = data.detailAbsensi || data.attendanceDetails || [];
|
||
if (Array.isArray(attendanceDetails) && attendanceDetails.length > 0) {
|
||
try {
|
||
const attendanceValues = attendanceDetails.map(ad => [
|
||
'class',
|
||
journalId,
|
||
ad.studentName || ad.nama || '',
|
||
ad.status || 'H'
|
||
]);
|
||
await pool.query(`
|
||
INSERT INTO attendance_details (journal_type, journal_id, student_name, status)
|
||
VALUES ?
|
||
`, [attendanceValues]);
|
||
} catch (attErr) {
|
||
console.log('Could not save attendance details (table may not exist):', attErr.message);
|
||
}
|
||
}
|
||
|
||
// Clear cache to ensure new data is visible immediately
|
||
cache.clear('data');
|
||
|
||
res.json({ status: 'success', message: 'Jurnal kelas berhasil disimpan', journalId });
|
||
} catch (error) {
|
||
console.error('Error saving class journal:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// DELETE journal
|
||
app.delete('/api/jurnal/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
// Delete attendance details first (ignore error if table doesn't exist)
|
||
try {
|
||
await pool.query('DELETE FROM attendance_details WHERE journal_type = ? AND journal_id = ?', ['teacher', id]);
|
||
} catch (err) { /* ignore */ }
|
||
await pool.query('DELETE FROM journal_entries WHERE journal_id = ? OR id = ?', [id, id]);
|
||
res.json({ status: 'success', message: 'Jurnal berhasil dihapus' });
|
||
} catch (error) {
|
||
console.error('Error deleting journal:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// DELETE class journal
|
||
app.delete('/api/jurnal-kelas/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
// Delete attendance details first (ignore error if table doesn't exist)
|
||
try {
|
||
await pool.query('DELETE FROM attendance_details WHERE journal_type = ? AND journal_id = ?', ['class', id]);
|
||
} catch (err) { /* ignore */ }
|
||
await pool.query('DELETE FROM class_journal_entries WHERE journal_id = ? OR id = ?', [id, id]);
|
||
res.json({ status: 'success', message: 'Jurnal kelas berhasil dihapus' });
|
||
} catch (error) {
|
||
console.error('Error deleting class journal:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// PUT update journal
|
||
app.put('/api/jurnal/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
const body = req.body;
|
||
// Handle both direct data and nested payload format from frontend
|
||
const data = body.payload || body;
|
||
|
||
// Parse jamKe if it's combined format "08:00 - 09:00"
|
||
let startTime = data.startTime;
|
||
let endTime = data.endTime;
|
||
if (data.jamKe && data.jamKe.includes('-')) {
|
||
const parts = data.jamKe.split('-').map(s => s.trim());
|
||
startTime = parts[0] || '';
|
||
endTime = parts[1] || '';
|
||
}
|
||
|
||
// Map field names from frontend format to database format
|
||
const teacherName = data.teacherName || data.namaGuru || '';
|
||
const nip = data.nip || '-';
|
||
const className = data.className || data.kelas || '';
|
||
const subject = data.subject || data.mapel || '';
|
||
const topic = data.topic || data.materi || '';
|
||
const studentsPresent = data.studentsPresent || data.jmlHadir || 0;
|
||
const sakit = data.sakit || data.jmlSakit || 0;
|
||
const izin = data.izin || data.jmlIzin || 0;
|
||
const alpha = data.alpha || data.jmlAlpha || 0;
|
||
const dispen = data.dispen || data.jmlDispen || 0;
|
||
const notes = data.notes || data.keterangan || '';
|
||
const photoUrl = data.photoUrl || data.foto || '';
|
||
const date = data.date || new Date().toISOString().split('T')[0];
|
||
|
||
await pool.query(`
|
||
UPDATE journal_entries SET
|
||
date = ?, teacher_name = ?, nip = ?, class_name = ?, subject = ?, topic = ?,
|
||
start_time = ?, end_time = ?, students_present = ?, sakit = ?, izin = ?,
|
||
alpha = ?, dispen = ?, notes = ?, photo_url = ?
|
||
WHERE journal_id = ? OR id = ?
|
||
`, [
|
||
date, teacherName, nip, className, subject, topic,
|
||
startTime, endTime, studentsPresent, sakit, izin, alpha, dispen, notes, photoUrl,
|
||
id, id
|
||
]);
|
||
|
||
// Clear cache to reflect changes
|
||
cache.clear('data');
|
||
|
||
res.json({ status: 'success', message: 'Jurnal berhasil diupdate' });
|
||
} catch (error) {
|
||
console.error('Error updating journal:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
|
||
// PUT update class journal
|
||
app.put('/api/jurnal-kelas/:id', async (req, res) => {
|
||
try {
|
||
const { id } = req.params;
|
||
const body = req.body;
|
||
// Handle both direct data and nested payload format from frontend
|
||
const data = body.payload || body;
|
||
|
||
// Parse jamKe if it's combined format "1 - 2"
|
||
let startTime = data.startTime;
|
||
let endTime = data.endTime;
|
||
if (data.jamKe && data.jamKe.includes('-')) {
|
||
const parts = data.jamKe.split('-').map(s => s.trim());
|
||
startTime = parts[0] || '';
|
||
endTime = parts[1] || '';
|
||
}
|
||
|
||
// Map field names from frontend format to database format
|
||
const teacherName = data.teacherName || data.namaGuru || '';
|
||
const subject = data.subject || data.mapel || '';
|
||
const className = data.className || data.kelas || '';
|
||
const teacherPresence = data.teacherPresence || data.kehadiranGuru || 'Hadir';
|
||
const studentsPresent = data.studentsPresent || data.jmlHadir || 0;
|
||
const sakit = data.sakit || data.jmlSakit || 0;
|
||
const izin = data.izin || data.jmlIzin || 0;
|
||
const alpha = data.alpha || data.jmlAlpha || 0;
|
||
const dispen = data.dispen || data.jmlDispen || 0;
|
||
const notes = data.notes || data.keterangan || '';
|
||
const classSecretary = data.classSecretary || data.sekretaris || '';
|
||
const date = data.date || new Date().toISOString().split('T')[0];
|
||
|
||
await pool.query(`
|
||
UPDATE class_journal_entries SET
|
||
date = ?, teacher_name = ?, subject = ?, class_name = ?, teacher_presence = ?,
|
||
start_time = ?, end_time = ?, students_present = ?, sakit = ?, izin = ?,
|
||
alpha = ?, dispen = ?, notes = ?, class_secretary = ?
|
||
WHERE journal_id = ? OR id = ?
|
||
`, [
|
||
date, teacherName, subject, className, teacherPresence,
|
||
startTime, endTime, studentsPresent, sakit, izin, alpha, dispen, notes, classSecretary,
|
||
id, id
|
||
]);
|
||
|
||
// Clear cache to reflect changes
|
||
cache.clear('data');
|
||
|
||
res.json({ status: 'success', message: 'Jurnal kelas berhasil diupdate' });
|
||
} catch (error) {
|
||
console.error('Error updating class journal:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
|
||
// ==================== SETTINGS ENDPOINTS ====================
|
||
|
||
app.get('/api/settings', async (req, res) => {
|
||
try {
|
||
const [rows] = await pool.query('SELECT setting_key, setting_value FROM school_settings');
|
||
const settings = {};
|
||
rows.forEach(row => {
|
||
settings[row.setting_key] = row.setting_value;
|
||
});
|
||
res.json({ status: 'success', data: settings });
|
||
} catch (error) {
|
||
console.error('Error fetching settings:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
app.post('/api/settings', async (req, res) => {
|
||
try {
|
||
const body = req.body;
|
||
// Handle both direct data and nested payload format from frontend
|
||
const data = body.payload || body;
|
||
|
||
// Map field names from frontend format (Indonesian) to database format
|
||
const semester = data.semester || data.Semester;
|
||
const academicYear = data.academicYear || data.TahunPelajaran;
|
||
const headmasterName = data.headmasterName || data.NamaKepalaSekolah;
|
||
const headmasterNip = data.headmasterNip || data.NipKepalaSekolah;
|
||
const logoUrl = data.logoUrl || data.LogoUrl;
|
||
const kopUrl = data.kopUrl || data.KopUrl;
|
||
|
||
const settingsToSave = [
|
||
['semester', semester],
|
||
['academic_year', academicYear],
|
||
['headmaster_name', headmasterName],
|
||
['headmaster_nip', headmasterNip],
|
||
['logo_url', logoUrl],
|
||
['kop_url', kopUrl]
|
||
];
|
||
|
||
for (const [key, value] of settingsToSave) {
|
||
if (value !== undefined) {
|
||
await pool.query(
|
||
'INSERT INTO school_settings (setting_key, setting_value) VALUES (?, ?) ON DUPLICATE KEY UPDATE setting_value = ?',
|
||
[key, value, value]
|
||
);
|
||
}
|
||
}
|
||
|
||
res.json({ status: 'success', message: 'Settings berhasil disimpan' });
|
||
} catch (error) {
|
||
console.error('Error saving settings:', error);
|
||
res.status(500).json({ status: 'error', message: error.message });
|
||
}
|
||
});
|
||
|
||
// ==================== END API ROUTES ====================
|
||
|
||
// Serve static frontend files from dist folder (AFTER API routes)
|
||
app.use(express.static(path.join(__dirname, 'dist')));
|
||
|
||
// SPA fallback - all other routes serve index.html
|
||
app.use((req, res) => {
|
||
if (req.path.startsWith('/api')) {
|
||
return res.status(404).json({ error: 'API endpoint not found' });
|
||
}
|
||
res.sendFile(path.join(__dirname, 'dist', 'index.html'));
|
||
});
|
||
|
||
// Start server
|
||
const startServer = async () => {
|
||
const dbConnected = await initializeDatabase();
|
||
|
||
if (!dbConnected) {
|
||
console.warn('⚠️ Warning: Database connection failed.');
|
||
}
|
||
|
||
app.listen(PORT, () => {
|
||
console.log(`
|
||
╔═══════════════════════════════════════════════════════════╗
|
||
║ 🏫 Jurnal Kelas & Mengajar - Production Server ║
|
||
╠═══════════════════════════════════════════════════════════╣
|
||
║ Server running on: http://localhost:${PORT} ║
|
||
║ Database: ${dbConnected ? '✅ Connected' : '❌ Disconnected'} ║
|
||
║ ║
|
||
║ API Endpoints (ALL INLINE - FULL CRUD): ║
|
||
║ - GET/POST/PUT/DELETE /api/students ║
|
||
║ - GET/POST/PUT/DELETE /api/teachers ║
|
||
║ - GET/POST/PUT/DELETE /api/subjects ║
|
||
║ - GET/POST/PUT/DELETE /api/jurnal ║
|
||
║ - GET/POST/PUT/DELETE /api/jurnal-kelas ║
|
||
║ - GET/POST /api/settings ║
|
||
║ - GET /api/data ║
|
||
║ - GET /api/health ║
|
||
╚═══════════════════════════════════════════════════════════╝
|
||
`);
|
||
});
|
||
};
|
||
|
||
startServer(); |