1271 lines
41 KiB
JavaScript
1271 lines
41 KiB
JavaScript
import express from 'express';
|
|
import path from 'path';
|
|
import { fileURLToPath } from 'url';
|
|
import fs from 'fs';
|
|
import multer from 'multer';
|
|
import mysql from 'mysql2/promise';
|
|
import dotenv from 'dotenv';
|
|
|
|
dotenv.config();
|
|
|
|
const __filename = fileURLToPath(import.meta.url);
|
|
const __dirname = path.dirname(__filename);
|
|
|
|
const app = express();
|
|
const port = process.env.PORT || 3003;
|
|
|
|
const buildPath = path.join(__dirname, 'dist');
|
|
const assetsPath = path.join(buildPath, 'assets');
|
|
|
|
// MySQL Connection Pool
|
|
const pool = mysql.createPool({
|
|
host: process.env.DB_HOST || 'localhost',
|
|
port: parseInt(process.env.DB_PORT) || 3306,
|
|
user: process.env.DB_USER || 'root',
|
|
password: process.env.DB_PASS || '',
|
|
database: process.env.DB_NAME || 'db_spjkomite',
|
|
waitForConnections: true,
|
|
connectionLimit: 10,
|
|
queueLimit: 0
|
|
});
|
|
|
|
// Test database connection on startup
|
|
async function testDatabaseConnection() {
|
|
try {
|
|
const connection = await pool.getConnection();
|
|
console.log('✅ Database MySQL terhubung ke:', process.env.DB_NAME);
|
|
connection.release();
|
|
return true;
|
|
} catch (error) {
|
|
console.error('❌ Gagal terhubung ke database:', error.message);
|
|
return false;
|
|
}
|
|
}
|
|
|
|
// Initialize database tables
|
|
async function initializeDatabase() {
|
|
try {
|
|
const schemaPath = path.join(__dirname, 'database', 'schema.sql');
|
|
if (fs.existsSync(schemaPath)) {
|
|
const schema = fs.readFileSync(schemaPath, 'utf8');
|
|
const statements = schema.split(';').filter(s => s.trim());
|
|
|
|
for (const statement of statements) {
|
|
if (statement.trim()) {
|
|
await pool.query(statement);
|
|
}
|
|
}
|
|
console.log('✅ Database schema initialized');
|
|
}
|
|
} catch (error) {
|
|
console.error('⚠️ Error initializing database schema:', error.message);
|
|
}
|
|
}
|
|
|
|
// Pemeriksaan Kunci: Pastikan folder 'dist' ada.
|
|
if (!fs.existsSync(buildPath)) {
|
|
console.error('================================================================');
|
|
console.error('ERROR: Folder "dist" tidak ditemukan.');
|
|
console.error('Ini berarti aplikasi belum di-build untuk production.');
|
|
console.error('Harap jalankan "npm run build" sebelum memulai server.');
|
|
console.error('================================================================');
|
|
process.exit(1);
|
|
}
|
|
|
|
// Pastikan folder assets ada
|
|
if (!fs.existsSync(assetsPath)) {
|
|
fs.mkdirSync(assetsPath, { recursive: true });
|
|
}
|
|
|
|
// JSON body parser with increased limit for base64 images
|
|
app.use(express.json({ limit: '50mb' }));
|
|
|
|
// Configure multer for file uploads
|
|
const storage = multer.diskStorage({
|
|
destination: function (req, file, cb) {
|
|
cb(null, assetsPath);
|
|
},
|
|
filename: function (req, file, cb) {
|
|
const uploadType = req.params.type;
|
|
const ext = path.extname(file.originalname).toLowerCase() || '.png';
|
|
cb(null, uploadType + ext);
|
|
}
|
|
});
|
|
|
|
const upload = multer({
|
|
storage: storage,
|
|
limits: { fileSize: 5 * 1024 * 1024 },
|
|
fileFilter: function (req, file, cb) {
|
|
const allowedTypes = /jpeg|jpg|png|gif|webp/;
|
|
const extname = allowedTypes.test(path.extname(file.originalname).toLowerCase());
|
|
const mimetype = allowedTypes.test(file.mimetype);
|
|
if (extname && mimetype) {
|
|
return cb(null, true);
|
|
}
|
|
cb(new Error('Hanya file gambar yang diperbolehkan (jpg, png, gif, webp)'));
|
|
}
|
|
});
|
|
|
|
// ============================================
|
|
// API ENDPOINTS FOR MYSQL DATABASE
|
|
// ============================================
|
|
|
|
// Test database connection
|
|
app.get('/api/test', async (req, res) => {
|
|
try {
|
|
const connection = await pool.getConnection();
|
|
connection.release();
|
|
res.json({ success: true, data: { message: 'Koneksi ke Database MySQL berhasil!' } });
|
|
} catch (error) {
|
|
res.json({ success: false, data: { error: 'Gagal terhubung ke database: ' + error.message } });
|
|
}
|
|
});
|
|
|
|
// Get next document number
|
|
app.get('/api/next-nomor', async (req, res) => {
|
|
try {
|
|
const currentYear = new Date().getFullYear().toString();
|
|
|
|
// Get or create counter for current year
|
|
await pool.query(
|
|
'INSERT INTO nomor_counter (tahun_anggaran, counter) VALUES (?, 0) ON DUPLICATE KEY UPDATE tahun_anggaran=tahun_anggaran',
|
|
[currentYear]
|
|
);
|
|
|
|
// Increment and get new counter
|
|
await pool.query(
|
|
'UPDATE nomor_counter SET counter = counter + 1 WHERE tahun_anggaran = ?',
|
|
[currentYear]
|
|
);
|
|
|
|
const [rows] = await pool.query(
|
|
'SELECT counter FROM nomor_counter WHERE tahun_anggaran = ?',
|
|
[currentYear]
|
|
);
|
|
|
|
const counter = rows[0]?.counter || 1;
|
|
const formattedNumber = String(counter).padStart(3, '0');
|
|
|
|
res.json({ success: true, data: formattedNumber });
|
|
} catch (error) {
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Helper function to map table names
|
|
function getTableName(sheetName) {
|
|
const tableMap = {
|
|
'No. Kode': 'kode_kegiatan',
|
|
'Pesanan': 'pesanan',
|
|
'Berita Pemeriksaan': 'bap',
|
|
'BAST': 'bast',
|
|
'Nota': 'nota',
|
|
'Manajemen': 'pengaturan',
|
|
'Foto': 'foto',
|
|
'RKS': 'rks',
|
|
'SPJ Lomba': 'spj_lomba'
|
|
};
|
|
return tableMap[sheetName] || sheetName.toLowerCase();
|
|
}
|
|
|
|
// Helper function to map column names from frontend to database
|
|
function mapColumnsToDB(tableName, data) {
|
|
const columnMaps = {
|
|
'kode_kegiatan': {
|
|
'Kode': 'kode',
|
|
'Uraian': 'uraian'
|
|
},
|
|
'pengaturan': {
|
|
'Nama Ketua Komite': 'nama_ketua_komite',
|
|
'Bendahara': 'bendahara',
|
|
'Nama Tim Teknis Pemeriksaan A': 'nama_tim_teknis_a',
|
|
'Jabatan Tim Teknis Pemeriksaan A': 'jabatan_tim_teknis_a',
|
|
'Nama Tim Teknis Pemeriksaan B': 'nama_tim_teknis_b',
|
|
'Jabatan Tim Teknis Pemeriksaan B': 'jabatan_tim_teknis_b',
|
|
'Nama Tim Teknis Pemeriksaan C': 'nama_tim_teknis_c',
|
|
'Jabatan Tim Teknis Pemeriksaan C': 'jabatan_tim_teknis_c',
|
|
'Tahun Anggaran': 'tahun_anggaran'
|
|
},
|
|
'pesanan': {
|
|
'Tanggal Pesanan': 'tanggal_pesanan',
|
|
'Nomor Pesanan': 'nomor_pesanan',
|
|
'Penyedia': 'penyedia',
|
|
'Nama/Jenis Barang': 'nama_jenis_barang',
|
|
'Spesifikasi Teknis/Tipe': 'spesifikasi_teknis',
|
|
'Volume': 'volume',
|
|
'Satuan': 'satuan',
|
|
'Keterangan': 'keterangan',
|
|
'Waktu Pengerjaan': 'waktu_pengerjaan',
|
|
'Batas Tanggal Pengerjaan': 'batas_tanggal_pengerjaan',
|
|
'Penerima Pesanan': 'penerima_pesanan',
|
|
'Yang Memesan': 'yang_memesan'
|
|
},
|
|
'bap': {
|
|
'Nomor BAP': 'nomor_bap',
|
|
'Pekerjaan': 'pekerjaan',
|
|
'Tanggal Pemeriksaan': 'tanggal_pemeriksaan',
|
|
'Nama Tim Teknis Pemeriksaan A': 'nama_tim_teknis_a',
|
|
'Nama Tim Teknis Pemeriksaan B': 'nama_tim_teknis_b',
|
|
'Nama Tim Teknis Pemeriksaan C': 'nama_tim_teknis_c',
|
|
'Jabatan Tim Teknis Pemeriksaan A': 'jabatan_tim_teknis_a',
|
|
'Jabatan Tim Teknis Pemeriksaan B': 'jabatan_tim_teknis_b',
|
|
'Jabatan Tim Teknis Pemeriksaan C': 'jabatan_tim_teknis_c',
|
|
'Tujuan': 'tujuan',
|
|
'Alamat Penyedia': 'alamat_penyedia',
|
|
'Ketua Komite': 'ketua_komite',
|
|
'Nomor Pesanan': 'nomor_pesanan',
|
|
'Kode Kegiatan': 'kode_kegiatan',
|
|
'Kode Sub Kegiatan': 'kode_sub_kegiatan',
|
|
'Kode Rekening': 'kode_rekening'
|
|
},
|
|
'bast': {
|
|
'Nomor BAST': 'nomor_bast',
|
|
'Tanggal BAST': 'tanggal_bast',
|
|
'Nomor BAP': 'nomor_bap'
|
|
},
|
|
'nota': {
|
|
'No.': 'nomor',
|
|
'Volume': 'volume',
|
|
'Satuan': 'satuan',
|
|
'Nama Barang': 'nama_barang',
|
|
'Harga Satuan': 'harga_satuan',
|
|
'Jumlah': 'jumlah',
|
|
'Nomor BAP': 'nomor_bap'
|
|
},
|
|
'foto': {
|
|
'Nomor BAP': 'nomor_bap',
|
|
'Related ID': 'related_id',
|
|
'Filename': 'filename',
|
|
'URL': 'url'
|
|
},
|
|
'rks': {
|
|
'Pekerjaan': 'pekerjaan'
|
|
},
|
|
'spj_lomba': {
|
|
'Nomor SPJ': 'nomor_spj',
|
|
'Tanggal': 'tanggal',
|
|
'Nama Kegiatan': 'nama_kegiatan',
|
|
'Jenis Pembayaran': 'jenis_pembayaran',
|
|
'Penerima': 'penerima',
|
|
'Nama Bank': 'nama_bank',
|
|
'Nomor Rekening': 'nomor_rekening',
|
|
'Atas Nama': 'atas_nama',
|
|
'Nominal': 'nominal',
|
|
'Keterangan': 'keterangan',
|
|
'Bukti Transfer': 'bukti_transfer',
|
|
'Kode Kegiatan': 'kode_kegiatan',
|
|
'Kode Sub Kegiatan': 'kode_sub_kegiatan',
|
|
'Kode Rekening': 'kode_rekening'
|
|
}
|
|
};
|
|
|
|
const map = columnMaps[tableName];
|
|
if (!map) return data;
|
|
|
|
const mapped = {};
|
|
for (const [key, value] of Object.entries(data)) {
|
|
const dbColumn = map[key];
|
|
if (dbColumn) {
|
|
mapped[dbColumn] = value;
|
|
}
|
|
}
|
|
return mapped;
|
|
}
|
|
|
|
// Helper function to map column names from database to frontend
|
|
function mapColumnsToFrontend(tableName, data) {
|
|
const columnMaps = {
|
|
'kode_kegiatan': {
|
|
'kode': 'Kode',
|
|
'uraian': 'Uraian'
|
|
},
|
|
'pengaturan': {
|
|
'nama_ketua_komite': 'Nama Ketua Komite',
|
|
'bendahara': 'Bendahara',
|
|
'nama_tim_teknis_a': 'Nama Tim Teknis Pemeriksaan A',
|
|
'jabatan_tim_teknis_a': 'Jabatan Tim Teknis Pemeriksaan A',
|
|
'nama_tim_teknis_b': 'Nama Tim Teknis Pemeriksaan B',
|
|
'jabatan_tim_teknis_b': 'Jabatan Tim Teknis Pemeriksaan B',
|
|
'nama_tim_teknis_c': 'Nama Tim Teknis Pemeriksaan C',
|
|
'jabatan_tim_teknis_c': 'Jabatan Tim Teknis Pemeriksaan C',
|
|
'tahun_anggaran': 'Tahun Anggaran'
|
|
},
|
|
'pesanan': {
|
|
'tanggal_pesanan': 'Tanggal Pesanan',
|
|
'nomor_pesanan': 'Nomor Pesanan',
|
|
'penyedia': 'Penyedia',
|
|
'nama_jenis_barang': 'Nama/Jenis Barang',
|
|
'spesifikasi_teknis': 'Spesifikasi Teknis/Tipe',
|
|
'volume': 'Volume',
|
|
'satuan': 'Satuan',
|
|
'keterangan': 'Keterangan',
|
|
'waktu_pengerjaan': 'Waktu Pengerjaan',
|
|
'batas_tanggal_pengerjaan': 'Batas Tanggal Pengerjaan',
|
|
'penerima_pesanan': 'Penerima Pesanan',
|
|
'yang_memesan': 'Yang Memesan'
|
|
},
|
|
'bap': {
|
|
'nomor_bap': 'Nomor BAP',
|
|
'pekerjaan': 'Pekerjaan',
|
|
'tanggal_pemeriksaan': 'Tanggal Pemeriksaan',
|
|
'nama_tim_teknis_a': 'Nama Tim Teknis Pemeriksaan A',
|
|
'nama_tim_teknis_b': 'Nama Tim Teknis Pemeriksaan B',
|
|
'nama_tim_teknis_c': 'Nama Tim Teknis Pemeriksaan C',
|
|
'jabatan_tim_teknis_a': 'Jabatan Tim Teknis Pemeriksaan A',
|
|
'jabatan_tim_teknis_b': 'Jabatan Tim Teknis Pemeriksaan B',
|
|
'jabatan_tim_teknis_c': 'Jabatan Tim Teknis Pemeriksaan C',
|
|
'tujuan': 'Tujuan',
|
|
'alamat_penyedia': 'Alamat Penyedia',
|
|
'ketua_komite': 'Ketua Komite',
|
|
'nomor_pesanan': 'Nomor Pesanan',
|
|
'kode_kegiatan': 'Kode Kegiatan',
|
|
'kode_sub_kegiatan': 'Kode Sub Kegiatan',
|
|
'kode_rekening': 'Kode Rekening'
|
|
},
|
|
'bast': {
|
|
'nomor_bast': 'Nomor BAST',
|
|
'tanggal_bast': 'Tanggal BAST',
|
|
'nomor_bap': 'Nomor BAP'
|
|
},
|
|
'nota': {
|
|
'nomor': 'No.',
|
|
'volume': 'Volume',
|
|
'satuan': 'Satuan',
|
|
'nama_barang': 'Nama Barang',
|
|
'harga_satuan': 'Harga Satuan',
|
|
'jumlah': 'Jumlah',
|
|
'nomor_bap': 'Nomor BAP'
|
|
},
|
|
'foto': {
|
|
'nomor_bap': 'Nomor BAP',
|
|
'related_id': 'Related ID',
|
|
'filename': 'Filename',
|
|
'url': 'URL'
|
|
},
|
|
'rks': {
|
|
'pekerjaan': 'Pekerjaan'
|
|
},
|
|
'spj_lomba': {
|
|
'nomor_spj': 'Nomor SPJ',
|
|
'tanggal': 'Tanggal',
|
|
'nama_kegiatan': 'Nama Kegiatan',
|
|
'jenis_pembayaran': 'Jenis Pembayaran',
|
|
'penerima': 'Penerima',
|
|
'nama_bank': 'Nama Bank',
|
|
'nomor_rekening': 'Nomor Rekening',
|
|
'atas_nama': 'Atas Nama',
|
|
'nominal': 'Nominal',
|
|
'keterangan': 'Keterangan',
|
|
'bukti_transfer': 'Bukti Transfer',
|
|
'kode_kegiatan': 'Kode Kegiatan',
|
|
'kode_sub_kegiatan': 'Kode Sub Kegiatan',
|
|
'kode_rekening': 'Kode Rekening'
|
|
}
|
|
};
|
|
|
|
const map = columnMaps[tableName];
|
|
if (!map) return data;
|
|
|
|
const mapped = {};
|
|
if (data.id !== undefined) {
|
|
mapped.id = data.id;
|
|
}
|
|
|
|
for (const [key, value] of Object.entries(data)) {
|
|
const frontendColumn = map[key];
|
|
if (frontendColumn) {
|
|
mapped[frontendColumn] = value;
|
|
}
|
|
}
|
|
return mapped;
|
|
}
|
|
|
|
// Get data from table
|
|
app.get('/api/data/:sheetName', async (req, res) => {
|
|
try {
|
|
const sheetName = req.params.sheetName;
|
|
const tableName = getTableName(sheetName);
|
|
|
|
const [rows] = await pool.query(`SELECT * FROM ${tableName}`);
|
|
|
|
// Map column names back to frontend format
|
|
const mappedRows = rows.map(row => mapColumnsToFrontend(tableName, row));
|
|
|
|
res.json({ success: true, data: mappedRows });
|
|
} catch (error) {
|
|
console.error('Error getting data:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Save single data to table
|
|
app.post('/api/save-data', async (req, res) => {
|
|
try {
|
|
const { sheetName, data } = req.body;
|
|
const tableName = getTableName(sheetName);
|
|
const mappedData = mapColumnsToDB(tableName, data);
|
|
|
|
const columns = Object.keys(mappedData);
|
|
const values = Object.values(mappedData);
|
|
const placeholders = columns.map(() => '?').join(', ');
|
|
|
|
const query = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${placeholders})`;
|
|
await pool.query(query, values);
|
|
|
|
res.json({ success: true, data: { message: 'Data berhasil disimpan!' } });
|
|
} catch (error) {
|
|
console.error('Error saving data:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Save batch data to table
|
|
app.post('/api/save-batch-data', async (req, res) => {
|
|
try {
|
|
const { sheetName, data } = req.body;
|
|
const tableName = getTableName(sheetName);
|
|
|
|
if (!Array.isArray(data) || data.length === 0) {
|
|
return res.json({ success: false, data: { error: 'Data harus berupa array dan tidak boleh kosong' } });
|
|
}
|
|
|
|
const connection = await pool.getConnection();
|
|
await connection.beginTransaction();
|
|
|
|
try {
|
|
for (const item of data) {
|
|
const mappedData = mapColumnsToDB(tableName, item);
|
|
const columns = Object.keys(mappedData);
|
|
const values = Object.values(mappedData);
|
|
const placeholders = columns.map(() => '?').join(', ');
|
|
|
|
const query = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${placeholders})`;
|
|
await connection.query(query, values);
|
|
}
|
|
|
|
await connection.commit();
|
|
res.json({ success: true, data: { message: 'Data batch berhasil disimpan!' } });
|
|
} catch (error) {
|
|
await connection.rollback();
|
|
throw error;
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
} catch (error) {
|
|
console.error('Error saving batch data:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Get nota items by BAP number
|
|
app.get('/api/nota/:nomorBap', async (req, res) => {
|
|
try {
|
|
const nomorBap = decodeURIComponent(req.params.nomorBap);
|
|
const [rows] = await pool.query('SELECT * FROM nota WHERE nomor_bap = ?', [nomorBap]);
|
|
|
|
const mappedData = rows.map(row => mapColumnsToFrontend('nota', row));
|
|
res.json({ success: true, data: mappedData });
|
|
} catch (error) {
|
|
console.error('Error fetching nota:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Update/replace nota for a BAP (delete old and insert new)
|
|
app.put('/api/nota/:nomorBap', async (req, res) => {
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
const nomorBap = decodeURIComponent(req.params.nomorBap);
|
|
const { data } = req.body;
|
|
|
|
if (!Array.isArray(data) || data.length === 0) {
|
|
return res.json({ success: false, data: { error: 'Data tidak valid' } });
|
|
}
|
|
|
|
await connection.beginTransaction();
|
|
|
|
// Delete existing nota for this BAP
|
|
await connection.query('DELETE FROM nota WHERE nomor_bap = ?', [nomorBap]);
|
|
|
|
// Insert new nota items
|
|
for (const item of data) {
|
|
const mappedData = mapColumnsToDB('nota', item);
|
|
if (Object.keys(mappedData).length > 0) {
|
|
const columns = Object.keys(mappedData);
|
|
const values = Object.values(mappedData);
|
|
const placeholders = columns.map(() => '?').join(', ');
|
|
|
|
const query = `INSERT INTO nota (${columns.join(', ')}) VALUES (${placeholders})`;
|
|
await connection.query(query, values);
|
|
}
|
|
}
|
|
|
|
await connection.commit();
|
|
res.json({ success: true, data: { message: 'Nota berhasil diperbarui!' } });
|
|
} catch (error) {
|
|
await connection.rollback();
|
|
console.error('Error updating nota:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
});
|
|
|
|
// Update pesanan pengerjaan details
|
|
app.patch('/api/pesanan/:nomor', async (req, res) => {
|
|
try {
|
|
const nomor = req.params.nomor;
|
|
const { waktuPengerjaan, batasTanggalPengerjaan } = req.body;
|
|
|
|
const query = `
|
|
UPDATE pesanan
|
|
SET waktu_pengerjaan = ?, batas_tanggal_pengerjaan = ?
|
|
WHERE nomor_pesanan = ?
|
|
`;
|
|
|
|
await pool.query(query, [waktuPengerjaan, batasTanggalPengerjaan, nomor]);
|
|
|
|
res.json({ success: true, data: { message: 'Data pengerjaan berhasil diperbarui!' } });
|
|
} catch (error) {
|
|
console.error('Error updating pesanan pengerjaan:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Delete pesanan and potentially revert counter
|
|
app.delete('/api/pesanan/:nomor', async (req, res) => {
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
const nomor = req.params.nomor;
|
|
await connection.beginTransaction();
|
|
|
|
// 1. Extract counter from nomor (format: .../SP/00X/...)
|
|
const parts = nomor.split('/');
|
|
let counterPart = parts.find(p => p.match(/^\d+$/));
|
|
|
|
if (counterPart) {
|
|
const orderCounter = parseInt(counterPart);
|
|
const currentYear = new Date().getFullYear().toString();
|
|
|
|
// 2. Check current counter in DB
|
|
const [rows] = await connection.query(
|
|
'SELECT counter FROM nomor_counter WHERE tahun_anggaran = ?',
|
|
[currentYear]
|
|
);
|
|
|
|
if (rows.length > 0 && rows[0].counter === orderCounter) {
|
|
// Revert counter if this was the last issued number
|
|
await connection.query(
|
|
'UPDATE nomor_counter SET counter = counter - 1 WHERE tahun_anggaran = ? AND counter > 0',
|
|
[currentYear]
|
|
);
|
|
}
|
|
}
|
|
|
|
// 3. Delete from pesanan table
|
|
await connection.query('DELETE FROM pesanan WHERE nomor_pesanan = ?', [nomor]);
|
|
|
|
await connection.commit();
|
|
res.json({ success: true, message: 'Pesanan berhasil dihapus dan nomor counter disesuaikan.' });
|
|
} catch (error) {
|
|
await connection.rollback();
|
|
console.error('Error deleting pesanan:', error);
|
|
res.status(500).json({ success: false, error: error.message });
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
});
|
|
|
|
// Update pesanan batch (for editing)
|
|
app.put('/api/pesanan/:nomor', async (req, res) => {
|
|
const connection = await pool.getConnection();
|
|
try {
|
|
const originalNomor = decodeURIComponent(req.params.nomor);
|
|
const { data } = req.body; // Array of pesanan items
|
|
|
|
if (!Array.isArray(data) || data.length === 0) {
|
|
return res.json({ success: false, data: { error: 'Data tidak valid' } });
|
|
}
|
|
|
|
await connection.beginTransaction();
|
|
|
|
// 1. Delete old items
|
|
await connection.query('DELETE FROM pesanan WHERE nomor_pesanan = ?', [originalNomor]);
|
|
|
|
// 2. Insert new items
|
|
for (const item of data) {
|
|
const mappedData = mapColumnsToDB('pesanan', item);
|
|
|
|
// Only insert if we have valid mapped data
|
|
if (Object.keys(mappedData).length > 0) {
|
|
const columns = Object.keys(mappedData);
|
|
const values = Object.values(mappedData);
|
|
const placeholders = columns.map(() => '?').join(', ');
|
|
|
|
const query = `INSERT INTO pesanan (${columns.join(', ')}) VALUES (${placeholders})`;
|
|
await connection.query(query, values);
|
|
}
|
|
}
|
|
|
|
await connection.commit();
|
|
res.json({ success: true, data: { message: 'Pesanan berhasil diperbarui!' } });
|
|
} catch (error) {
|
|
await connection.rollback();
|
|
console.error('Error updating pesanan:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
});
|
|
|
|
// Save BAP with photos
|
|
app.post('/api/save-bap-with-photos', async (req, res) => {
|
|
try {
|
|
const { bapData, photos } = req.body;
|
|
|
|
const connection = await pool.getConnection();
|
|
await connection.beginTransaction();
|
|
|
|
try {
|
|
// Save BAP data
|
|
const mappedBap = mapColumnsToDB('bap', bapData);
|
|
const bapColumns = Object.keys(mappedBap);
|
|
const bapValues = Object.values(mappedBap);
|
|
const bapPlaceholders = bapColumns.map(() => '?').join(', ');
|
|
|
|
const bapQuery = `INSERT INTO bap (${bapColumns.join(', ')}) VALUES (${bapPlaceholders})`;
|
|
await connection.query(bapQuery, bapValues);
|
|
|
|
// Save photos if any
|
|
if (photos && photos.length > 0) {
|
|
const nomorBap = bapData['Nomor BAP'];
|
|
for (let i = 0; i < photos.length; i++) {
|
|
const photo = photos[i];
|
|
const photoQuery = `INSERT INTO foto (nomor_bap, related_id, filename, url) VALUES (?, ?, ?, ?)`;
|
|
// Store as data URL format
|
|
const dataUrl = `data:${photo.mimeType};base64,${photo.data}`;
|
|
await connection.query(photoQuery, [nomorBap, photo.relatedId || photo.related_id, photo.filename, dataUrl]);
|
|
}
|
|
}
|
|
|
|
await connection.commit();
|
|
res.json({ success: true, data: { message: 'BAP dan foto berhasil disimpan!' } });
|
|
} catch (error) {
|
|
await connection.rollback();
|
|
throw error;
|
|
} finally {
|
|
connection.release();
|
|
}
|
|
} catch (error) {
|
|
console.error('Error saving BAP with photos:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Update pengaturan
|
|
app.post('/api/update-pengaturan', async (req, res) => {
|
|
try {
|
|
const data = req.body;
|
|
const mappedData = mapColumnsToDB('pengaturan', data);
|
|
|
|
const setClause = Object.keys(mappedData)
|
|
.map(key => `${key} = ?`)
|
|
.join(', ');
|
|
const values = [...Object.values(mappedData), 1];
|
|
|
|
const query = `UPDATE pengaturan SET ${setClause} WHERE id = ?`;
|
|
await pool.query(query, values);
|
|
|
|
res.json({ success: true, data: { message: 'Pengaturan berhasil diperbarui!' } });
|
|
} catch (error) {
|
|
console.error('Error updating pengaturan:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Delete kode kegiatan
|
|
app.delete('/api/kode/:id', async (req, res) => {
|
|
try {
|
|
const id = req.params.id;
|
|
await pool.query('DELETE FROM kode_kegiatan WHERE id = ?', [id]);
|
|
res.json({ success: true, data: { message: 'Kode berhasil dihapus!' } });
|
|
} catch (error) {
|
|
console.error('Error deleting kode:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Delete all kode kegiatan (for reimport)
|
|
app.delete('/api/kode', async (req, res) => {
|
|
try {
|
|
await pool.query('DELETE FROM kode_kegiatan');
|
|
res.json({ success: true, data: { message: 'Semua kode berhasil dihapus!' } });
|
|
} catch (error) {
|
|
console.error('Error deleting all kode:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Delete one RKS
|
|
app.delete('/api/rks/:id', async (req, res) => {
|
|
try {
|
|
const id = req.params.id;
|
|
await pool.query('DELETE FROM rks WHERE id = ?', [id]);
|
|
res.json({ success: true, data: { message: 'RKS berhasil dihapus!' } });
|
|
} catch (error) {
|
|
console.error('Error deleting rks:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Delete all RKS (for reimport)
|
|
app.delete('/api/rks', async (req, res) => {
|
|
try {
|
|
await pool.query('DELETE FROM rks');
|
|
res.json({ success: true, data: { message: 'Semua RKS berhasil dihapus!' } });
|
|
} catch (error) {
|
|
console.error('Error deleting all rks:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Delete batch RKS
|
|
app.post('/api/rks/delete-batch', async (req, res) => {
|
|
try {
|
|
const { ids } = req.body;
|
|
if (!Array.isArray(ids) || ids.length === 0) {
|
|
return res.status(400).json({ success: false, data: { error: 'No IDs provided' } });
|
|
}
|
|
await pool.query('DELETE FROM rks WHERE id IN (?)', [ids]);
|
|
res.json({ success: true, data: { message: `${ids.length} RKS berhasil dihapus!` } });
|
|
} catch (error) {
|
|
console.error('Error delete batch rks:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
|
|
|
|
// Seed initial kode kegiatan data
|
|
async function seedKodeKegiatan() {
|
|
try {
|
|
const [rows] = await pool.query('SELECT COUNT(*) as count FROM kode_kegiatan');
|
|
if (rows[0].count === 0) {
|
|
const initialKodes = [
|
|
{ kode: '400.3.8.1', uraian: 'Kurikulum' },
|
|
{ kode: '400.3.8.2', uraian: 'Bahan Ajar' },
|
|
{ kode: '400.3.8.3', uraian: 'Pelatihan' },
|
|
{ kode: '400.3.8.4', uraian: 'Block grant' },
|
|
{ kode: '400.3.8.5', uraian: 'Bimbingan teknis/sosialisasi' },
|
|
{ kode: '400.3.8.6', uraian: 'Lomba, Sayembara, festival' },
|
|
{ kode: '400.3.8.8', uraian: 'Bantuan siswa miskin' },
|
|
{ kode: '400.3.13.1', uraian: 'Prasarana Pendidikan' },
|
|
{ kode: '400.3.13.2', uraian: 'Sarana Pendidikan' },
|
|
{ kode: '400.3.13.3', uraian: 'Monitoring dan Evaluasi Sarana dan Prasana Pendidikan' },
|
|
{ kode: '400.3.11', uraian: 'Penilaian Akademik' },
|
|
{ kode: '400.3.12', uraian: 'Penilaian Non Akademik' },
|
|
{ kode: '400.3.13', uraian: 'Analisis dan Sistem Informasi Penilaian' },
|
|
{ kode: '400.3.12.1', uraian: 'Data peserta didik, pendidik dan tenaga kependidikan' },
|
|
{ kode: '400.3.12.2', uraian: 'Data Satuan Pendidikan dan ProsesPembelajaran' },
|
|
{ kode: '400.3.10.6', uraian: 'Peningkatan kesejahteraan guru dan tenaga pendidik' }
|
|
];
|
|
|
|
for (const kode of initialKodes) {
|
|
await pool.query('INSERT INTO kode_kegiatan (kode, uraian) VALUES (?, ?)', [kode.kode, kode.uraian]);
|
|
}
|
|
console.log('✅ Kode kegiatan seed data inserted');
|
|
}
|
|
} catch (error) {
|
|
console.error('⚠️ Error seeding kode kegiatan:', error.message);
|
|
}
|
|
}
|
|
|
|
// Seed initial RKS data
|
|
async function seedRks() {
|
|
try {
|
|
const [rows] = await pool.query('SELECT COUNT(*) as count FROM rks');
|
|
if (rows[0].count === 0) {
|
|
const initialRks = [
|
|
{ pekerjaan: 'Pengadaan Alat Tulis Kantor (ATK) - ATK KBM' },
|
|
{ pekerjaan: 'Pengadaan Alat Tulis Kantor (ATK) - ATK Kantor' },
|
|
{ pekerjaan: 'Pengadaan Alat Tulis Kantor (ATK) - ATK Administrasi' },
|
|
{ pekerjaan: 'Pengadaan Bahan Praktik Siswa - Tata Boga' },
|
|
{ pekerjaan: 'Pengadaan Bahan Praktik Siswa - Tata Kecantikan' },
|
|
{ pekerjaan: 'Pengadaan Bahan Praktik Siswa - Multimedia' },
|
|
{ pekerjaan: 'Pengadaan Bahan Praktik Siswa - Tata Busana' },
|
|
{ pekerjaan: 'Pengadaan Alat Kebersihan dan Bahan Pembersih' },
|
|
{ pekerjaan: 'Pengadaan Suku Cadang Alat Kantor' },
|
|
{ pekerjaan: 'Pengadaan Bahan Cetak dan Penggandaan' },
|
|
{ pekerjaan: 'Pengadaan Pakaian Dinas/Seragam' },
|
|
{ pekerjaan: 'Pengadaan Komponen Listrik/Elektronik' },
|
|
{ pekerjaan: 'Pengadaan Barang Inventaris' },
|
|
{ pekerjaan: 'Pemeliharaan Bangunan Gedung' },
|
|
{ pekerjaan: 'Pemeliharaan Sarana dan Prasarana Pendukung' },
|
|
{ pekerjaan: 'Jasa Tenaga Kebersihan' },
|
|
{ pekerjaan: 'Jasa Tenaga Keamanan' },
|
|
{ pekerjaan: 'Jasa Langganan Daya dan Jasa' },
|
|
{ pekerjaan: 'Honorarium Panitia Kegiatan' },
|
|
{ pekerjaan: 'Belanja Perjalanan Dinas' },
|
|
{ pekerjaan: 'Belanja Kursus/Pelatihan' },
|
|
{ pekerjaan: 'Belanja Jasa Konsultansi' },
|
|
{ pekerjaan: 'Penyelenggaraan Rapat Koordinasi' }
|
|
];
|
|
|
|
const values = initialRks.map(r => [r.pekerjaan]);
|
|
await pool.query('INSERT INTO rks (pekerjaan) VALUES ?', [values]);
|
|
console.log('✅ RKS data seeded successfully');
|
|
}
|
|
} catch (error) {
|
|
console.error('⚠️ Error seeding RKS data:', error.message);
|
|
}
|
|
}
|
|
|
|
|
|
// ============================================
|
|
// SPJ STATISTICS ENDPOINT
|
|
// ============================================
|
|
|
|
// API: Get SPJ statistics per month
|
|
app.get('/api/spj-stats', async (req, res) => {
|
|
try {
|
|
const year = req.query.year || new Date().getFullYear();
|
|
|
|
// Get BAP data with month info
|
|
const [bapRows] = await pool.query(`
|
|
SELECT
|
|
nomor_bap,
|
|
tanggal_pemeriksaan,
|
|
pekerjaan,
|
|
MONTH(STR_TO_DATE(tanggal_pemeriksaan, '%Y-%m-%d')) as bulan,
|
|
YEAR(STR_TO_DATE(tanggal_pemeriksaan, '%Y-%m-%d')) as tahun
|
|
FROM bap
|
|
WHERE YEAR(STR_TO_DATE(tanggal_pemeriksaan, '%Y-%m-%d')) = ?
|
|
ORDER BY tanggal_pemeriksaan
|
|
`, [year]);
|
|
|
|
// Get nota totals per BAP
|
|
const [notaRows] = await pool.query(`
|
|
SELECT
|
|
nomor_bap,
|
|
SUM(CAST(jumlah AS DECIMAL(15,2))) as total_nilai
|
|
FROM nota
|
|
GROUP BY nomor_bap
|
|
`);
|
|
|
|
// Get SPJ Lomba data with month info
|
|
const [spjLombaRows] = await pool.query(`
|
|
SELECT
|
|
nomor_spj,
|
|
tanggal,
|
|
nama_kegiatan,
|
|
CAST(nominal AS DECIMAL(15,2)) as nominal,
|
|
MONTH(STR_TO_DATE(tanggal, '%Y-%m-%d')) as bulan,
|
|
YEAR(STR_TO_DATE(tanggal, '%Y-%m-%d')) as tahun
|
|
FROM spj_lomba
|
|
WHERE YEAR(STR_TO_DATE(tanggal, '%Y-%m-%d')) = ?
|
|
ORDER BY tanggal
|
|
`, [year]);
|
|
|
|
// Create nota total map
|
|
const notaTotals = {};
|
|
for (const nota of notaRows) {
|
|
notaTotals[nota.nomor_bap] = parseFloat(nota.total_nilai) || 0;
|
|
}
|
|
|
|
// Initialize monthly stats (1-12)
|
|
const monthlyStats = {};
|
|
const monthNames = [
|
|
'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni',
|
|
'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'
|
|
];
|
|
|
|
for (let i = 1; i <= 12; i++) {
|
|
monthlyStats[i] = {
|
|
bulan: i,
|
|
namaBulan: monthNames[i - 1],
|
|
jumlahSPJ: 0,
|
|
totalNilai: 0,
|
|
jumlahSPJLomba: 0,
|
|
totalNilaiSPJLomba: 0,
|
|
details: [],
|
|
detailsSpjLomba: []
|
|
};
|
|
}
|
|
|
|
// Populate stats from BAP data
|
|
let grandTotalSPJ = 0;
|
|
let grandTotalNilai = 0;
|
|
|
|
for (const bap of bapRows) {
|
|
const bulan = bap.bulan;
|
|
if (bulan && monthlyStats[bulan]) {
|
|
const totalNilai = notaTotals[bap.nomor_bap] || 0;
|
|
|
|
monthlyStats[bulan].jumlahSPJ++;
|
|
monthlyStats[bulan].totalNilai += totalNilai;
|
|
monthlyStats[bulan].details.push({
|
|
nomorBap: bap.nomor_bap,
|
|
tanggal: bap.tanggal_pemeriksaan,
|
|
pekerjaan: bap.pekerjaan,
|
|
totalNilai: totalNilai
|
|
});
|
|
|
|
grandTotalSPJ++;
|
|
grandTotalNilai += totalNilai;
|
|
}
|
|
}
|
|
|
|
// Populate stats from SPJ Lomba data
|
|
let grandTotalSPJLomba = 0;
|
|
let grandTotalNilaiSPJLomba = 0;
|
|
|
|
for (const spj of spjLombaRows) {
|
|
const bulan = spj.bulan;
|
|
if (bulan && monthlyStats[bulan]) {
|
|
const nominal = parseFloat(spj.nominal) || 0;
|
|
|
|
monthlyStats[bulan].jumlahSPJLomba++;
|
|
monthlyStats[bulan].totalNilaiSPJLomba += nominal;
|
|
monthlyStats[bulan].detailsSpjLomba.push({
|
|
nomorSpj: spj.nomor_spj,
|
|
tanggal: spj.tanggal,
|
|
namaKegiatan: spj.nama_kegiatan,
|
|
nominal: nominal
|
|
});
|
|
|
|
grandTotalSPJLomba++;
|
|
grandTotalNilaiSPJLomba += nominal;
|
|
}
|
|
}
|
|
|
|
// Get available years for dropdown (from both BAP and SPJ Lomba)
|
|
const [yearRows] = await pool.query(`
|
|
SELECT DISTINCT tahun FROM (
|
|
SELECT YEAR(STR_TO_DATE(tanggal_pemeriksaan, '%Y-%m-%d')) as tahun
|
|
FROM bap
|
|
WHERE tanggal_pemeriksaan IS NOT NULL AND tanggal_pemeriksaan != ''
|
|
UNION
|
|
SELECT YEAR(STR_TO_DATE(tanggal, '%Y-%m-%d')) as tahun
|
|
FROM spj_lomba
|
|
WHERE tanggal IS NOT NULL AND tanggal != ''
|
|
) as combined_years
|
|
ORDER BY tahun DESC
|
|
`);
|
|
|
|
const availableYears = yearRows.map(r => r.tahun).filter(y => y);
|
|
|
|
res.json({
|
|
success: true,
|
|
data: {
|
|
year: parseInt(year),
|
|
availableYears,
|
|
monthly: Object.values(monthlyStats),
|
|
grandTotal: {
|
|
jumlahSPJ: grandTotalSPJ,
|
|
totalNilai: grandTotalNilai,
|
|
jumlahSPJLomba: grandTotalSPJLomba,
|
|
totalNilaiSPJLomba: grandTotalNilaiSPJLomba,
|
|
totalKeseluruhan: grandTotalNilai + grandTotalNilaiSPJLomba
|
|
}
|
|
}
|
|
});
|
|
} catch (error) {
|
|
console.error('Error getting SPJ stats:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
|
|
// ============================================
|
|
// DATABASE-BASED ASSET MANAGEMENT ENDPOINTS
|
|
// ============================================
|
|
|
|
// API: Get available assets from database (logo and kop)
|
|
app.get('/api/assets', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query('SELECT tipe, data_url FROM aset_aplikasi');
|
|
const assets = {
|
|
logo: null,
|
|
kop: null
|
|
};
|
|
|
|
for (const row of rows) {
|
|
if (row.tipe === 'logo' && row.data_url) {
|
|
assets.logo = row.data_url;
|
|
} else if (row.tipe === 'kop' && row.data_url) {
|
|
assets.kop = row.data_url;
|
|
}
|
|
}
|
|
|
|
res.json(assets);
|
|
} catch (error) {
|
|
console.error('Error getting assets:', error);
|
|
res.json({ logo: null, kop: null });
|
|
}
|
|
});
|
|
|
|
// API: Upload/save asset to database (logo or kop)
|
|
app.post('/api/assets/:type', async (req, res) => {
|
|
const assetType = req.params.type;
|
|
|
|
if (assetType !== 'logo' && assetType !== 'kop') {
|
|
return res.status(400).json({ error: 'Tipe asset tidak valid. Gunakan "logo" atau "kop".' });
|
|
}
|
|
|
|
const { dataUrl } = req.body;
|
|
|
|
if (!dataUrl) {
|
|
return res.status(400).json({ error: 'Data URL gambar diperlukan.' });
|
|
}
|
|
|
|
try {
|
|
await pool.query(
|
|
'INSERT INTO aset_aplikasi (tipe, data_url) VALUES (?, ?) ON DUPLICATE KEY UPDATE data_url = ?',
|
|
[assetType, dataUrl, dataUrl]
|
|
);
|
|
|
|
res.json({
|
|
success: true,
|
|
message: assetType.toUpperCase() + ' berhasil disimpan ke database!'
|
|
});
|
|
} catch (error) {
|
|
console.error('Error saving asset:', error);
|
|
res.status(500).json({ error: 'Gagal menyimpan asset: ' + error.message });
|
|
}
|
|
});
|
|
|
|
// API: Delete asset from database (logo or kop)
|
|
app.delete('/api/assets/:type', async (req, res) => {
|
|
const assetType = req.params.type;
|
|
|
|
if (assetType !== 'logo' && assetType !== 'kop') {
|
|
return res.status(400).json({ error: 'Tipe asset tidak valid.' });
|
|
}
|
|
|
|
try {
|
|
await pool.query(
|
|
'UPDATE aset_aplikasi SET data_url = NULL WHERE tipe = ?',
|
|
[assetType]
|
|
);
|
|
|
|
res.json({ success: true, message: assetType.toUpperCase() + ' berhasil dihapus dari database!' });
|
|
} catch (error) {
|
|
console.error('Error deleting asset:', error);
|
|
res.status(500).json({ error: 'Gagal menghapus asset: ' + error.message });
|
|
}
|
|
});
|
|
|
|
// ============================================
|
|
// PENYEDIA (SUPPLIER) MANAGEMENT ENDPOINTS
|
|
// ============================================
|
|
|
|
// API: Get all penyedia
|
|
app.get('/api/penyedia', async (req, res) => {
|
|
try {
|
|
const [rows] = await pool.query('SELECT id, nama, alamat, kop_url FROM penyedia ORDER BY nama');
|
|
res.json({ success: true, data: rows });
|
|
} catch (error) {
|
|
console.error('Error getting penyedia:', error);
|
|
res.json({ success: false, error: error.message });
|
|
}
|
|
});
|
|
|
|
// API: Get single penyedia by name
|
|
app.get('/api/penyedia/by-name/:nama', async (req, res) => {
|
|
try {
|
|
const nama = req.params.nama;
|
|
const [rows] = await pool.query('SELECT id, nama, alamat, kop_url FROM penyedia WHERE nama = ?', [nama]);
|
|
if (rows.length > 0) {
|
|
res.json({ success: true, data: rows[0] });
|
|
} else {
|
|
res.json({ success: true, data: null });
|
|
}
|
|
} catch (error) {
|
|
console.error('Error getting penyedia by name:', error);
|
|
res.json({ success: false, error: error.message });
|
|
}
|
|
});
|
|
|
|
// API: Save or update penyedia
|
|
app.post('/api/penyedia', async (req, res) => {
|
|
try {
|
|
const { nama, alamat, kop_url } = req.body;
|
|
|
|
if (!nama || !nama.trim()) {
|
|
return res.status(400).json({ success: false, error: 'Nama penyedia diperlukan' });
|
|
}
|
|
|
|
// Check if penyedia already exists
|
|
const [existing] = await pool.query('SELECT id FROM penyedia WHERE nama = ?', [nama.trim()]);
|
|
|
|
if (existing.length > 0) {
|
|
// Update existing penyedia
|
|
const updateFields = ['alamat = ?'];
|
|
const updateValues = [alamat || null];
|
|
|
|
if (kop_url !== undefined) {
|
|
updateFields.push('kop_url = ?');
|
|
updateValues.push(kop_url);
|
|
}
|
|
|
|
updateValues.push(existing[0].id);
|
|
|
|
await pool.query(
|
|
`UPDATE penyedia SET ${updateFields.join(', ')} WHERE id = ?`,
|
|
updateValues
|
|
);
|
|
|
|
res.json({ success: true, message: 'Penyedia berhasil diperbarui!', id: existing[0].id });
|
|
} else {
|
|
// Insert new penyedia
|
|
const [result] = await pool.query(
|
|
'INSERT INTO penyedia (nama, alamat, kop_url) VALUES (?, ?, ?)',
|
|
[nama.trim(), alamat || null, kop_url || null]
|
|
);
|
|
|
|
res.json({ success: true, message: 'Penyedia baru berhasil disimpan!', id: result.insertId });
|
|
}
|
|
} catch (error) {
|
|
console.error('Error saving penyedia:', error);
|
|
res.status(500).json({ success: false, error: error.message });
|
|
}
|
|
});
|
|
|
|
// API: Update penyedia KOP only
|
|
app.post('/api/penyedia/:id/kop', async (req, res) => {
|
|
try {
|
|
const id = req.params.id;
|
|
const { kop_url } = req.body;
|
|
|
|
await pool.query('UPDATE penyedia SET kop_url = ? WHERE id = ?', [kop_url, id]);
|
|
|
|
res.json({ success: true, message: 'KOP penyedia berhasil diperbarui!' });
|
|
} catch (error) {
|
|
console.error('Error updating penyedia KOP:', error);
|
|
res.status(500).json({ success: false, error: error.message });
|
|
}
|
|
});
|
|
|
|
// API: Delete penyedia
|
|
app.delete('/api/penyedia/:id', async (req, res) => {
|
|
try {
|
|
const id = req.params.id;
|
|
await pool.query('DELETE FROM penyedia WHERE id = ?', [id]);
|
|
res.json({ success: true, message: 'Penyedia berhasil dihapus!' });
|
|
} catch (error) {
|
|
console.error('Error deleting penyedia:', error);
|
|
res.status(500).json({ success: false, error: error.message });
|
|
}
|
|
});
|
|
|
|
// ============================================
|
|
// SPJ LOMBA ENDPOINTS
|
|
// ============================================
|
|
|
|
// Update SPJ Lomba
|
|
app.put('/api/spj-lomba/:nomor', async (req, res) => {
|
|
try {
|
|
const originalNomor = decodeURIComponent(req.params.nomor);
|
|
const { data } = req.body;
|
|
|
|
if (!data) {
|
|
return res.status(400).json({ success: false, data: { error: 'Data tidak valid' } });
|
|
}
|
|
|
|
const mappedData = mapColumnsToDB('spj_lomba', data);
|
|
|
|
// Build UPDATE query dynamically
|
|
const setClause = Object.keys(mappedData)
|
|
.map(key => `${key} = ?`)
|
|
.join(', ');
|
|
const values = [...Object.values(mappedData), originalNomor];
|
|
|
|
const query = `UPDATE spj_lomba SET ${setClause} WHERE nomor_spj = ?`;
|
|
await pool.query(query, values);
|
|
|
|
res.json({ success: true, data: { message: 'SPJ Lomba berhasil diperbarui!' } });
|
|
} catch (error) {
|
|
console.error('Error updating SPJ Lomba:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// Delete SPJ Lomba
|
|
app.delete('/api/spj-lomba/:nomor', async (req, res) => {
|
|
try {
|
|
const nomor = decodeURIComponent(req.params.nomor);
|
|
await pool.query('DELETE FROM spj_lomba WHERE nomor_spj = ?', [nomor]);
|
|
res.json({ success: true, data: { message: 'SPJ Lomba berhasil dihapus!' } });
|
|
} catch (error) {
|
|
console.error('Error deleting SPJ Lomba:', error);
|
|
res.json({ success: false, data: { error: error.message } });
|
|
}
|
|
});
|
|
|
|
// ============================================
|
|
// FILE-BASED ASSET FOR SUPPLIER KOP ONLY
|
|
// ============================================
|
|
|
|
// API: Upload supplier kop (file-based, stored locally)
|
|
app.post('/api/upload/:type', (req, res) => {
|
|
const uploadType = req.params.type;
|
|
|
|
// Only allow 'supplier' type for file-based upload
|
|
if (uploadType !== 'supplier') {
|
|
return res.status(400).json({ error: 'Tipe upload tidak valid. Gunakan API /api/assets/:type untuk logo dan kop.' });
|
|
}
|
|
|
|
const extensions = ['.png', '.jpg', '.jpeg', '.gif', '.webp'];
|
|
for (const ext of extensions) {
|
|
const filePath = path.join(assetsPath, uploadType + ext);
|
|
if (fs.existsSync(filePath)) {
|
|
fs.unlinkSync(filePath);
|
|
}
|
|
}
|
|
|
|
upload.single('file')(req, res, function (err) {
|
|
if (err instanceof multer.MulterError) {
|
|
if (err.code === 'LIMIT_FILE_SIZE') {
|
|
return res.status(400).json({ error: 'Ukuran file terlalu besar. Maksimal 5MB.' });
|
|
}
|
|
return res.status(400).json({ error: 'Upload error: ' + err.message });
|
|
} else if (err) {
|
|
return res.status(400).json({ error: err.message });
|
|
}
|
|
|
|
if (!req.file) {
|
|
return res.status(400).json({ error: 'Tidak ada file yang diupload.' });
|
|
}
|
|
|
|
const fileUrl = '/assets/' + req.file.filename + '?t=' + Date.now();
|
|
res.json({
|
|
success: true,
|
|
message: uploadType.toUpperCase() + ' berhasil diupload!',
|
|
url: fileUrl
|
|
});
|
|
});
|
|
});
|
|
|
|
// Sajikan file statis dari direktori build React ('dist')
|
|
app.use(express.static(buildPath));
|
|
|
|
// Handler "catchall": untuk setiap request, kirim kembali file index.html React.
|
|
app.get('*', (req, res) => {
|
|
res.sendFile(path.join(buildPath, 'index.html'));
|
|
});
|
|
|
|
// Start server
|
|
app.listen(port, async () => {
|
|
console.log(`Server berjalan di http://localhost:${port}`);
|
|
console.log(`Menyajikan konten dari: ${buildPath}`);
|
|
console.log(`Assets tersedia di: ${assetsPath}`);
|
|
|
|
// Test and initialize database
|
|
const isConnected = await testDatabaseConnection();
|
|
if (isConnected) {
|
|
await initializeDatabase();
|
|
await seedKodeKegiatan();
|
|
await seedRks();
|
|
}
|
|
});
|