const express = require('express');
const XLSX = require('xlsx');
const ExcelJS = require('exceljs');
const fs = require('fs');
const multer = require('multer');
const bodyParser = require('body-parser');
const path = require('path');
const app = express();
const PORT = 3000;
// Middleware untuk parsing JSON dan URL-encoded
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.urlencoded({ extended: true }));
app.use(express.json());
// Penanganan favicon
const faviconPath = path.join(__dirname, 'public', 'favicon.ico');
if (!fs.existsSync(faviconPath)) {
fs.writeFileSync(faviconPath, ''); // Membuat file favicon kosong jika tidak ada
}
app.use('/favicon.ico', express.static(faviconPath));
// Membuat folder respons jika belum ada
if (!fs.existsSync('respons')) {
fs.mkdirSync('respons');
}
// Middleware untuk menyajikan file statis dari folder `uploads`
app.use('/temp', express.static(path.join(__dirname, 'temp')));
// Konfigurasi Multer
const storage = multer.diskStorage({
destination: (req, file, cb) => {
cb(null, 'temp/'); // File sementara disimpan di folder temp/
},
filename: (req, file, cb) => {
const uniqueName = `${Date.now()}-${file.originalname}`;
cb(null, uniqueName);
}
});
const upload = multer({
storage,
fileFilter: (req, file, cb) => {
const allowedTypes = ['image/jpeg', 'image/png', 'image/jpg'];
if (allowedTypes.includes(file.mimetype)) {
cb(null, true);
} else {
cb(new Error('Format file tidak didukung (JPEG, PNG, JPG saja).'));
}
}
});
// Middleware untuk melayani file statis
app.use(express.static('public'));
app.use('/temp', express.static('temp')); // Menyajikan file dari folder uploads
// Endpoint untuk form
app.get('/', (req, res) => {
res.sendFile(path.join(__dirname, 'templates', 'index.html'));
});
// Function untuk memindahkan file dan menyisipkan gambar di Excel
const insertImagesToExcel = async (files, subFolderName, columnLetter, worksheet, workbook, folderPath) => {
if (files && files.length > 0) {
const subFolderPath = path.join(folderPath, subFolderName);
if (!fs.existsSync(subFolderPath)) {
fs.mkdirSync(subFolderPath, { recursive: true });
}
// Menyisipkan gambar ke Excel
for (let i = 0; i < files.length && i < 5; i++) {
const file = files[i];
const newPath = path.join(subFolderPath, file.originalname);
fs.renameSync(file.path, newPath);
// Mengonversi gambar menjadi buffer
const imageBuffer = fs.readFileSync(newPath);
// Menambahkan gambar ke workbook
const imageId = workbook.addImage({
buffer: imageBuffer,
extension: file.mimetype.split('/')[1] // Format gambar berdasarkan mimetype
});
// Menentukan posisi gambar di worksheet
const colIndex = columnLetter.charCodeAt(0) - 65; // Kolom dihitung dari huruf
worksheet.addImage(imageId, {
tl: { col: colIndex, row: i }, // Posisi gambar
ext: { width: 100, height: 100 } // Ukuran gambar
});
console.log(`Gambar disisipkan: ${file.originalname} ke kolom ${columnLetter}, baris ${i + 1}`);
}
}
};
// Endpoint POST untuk /submit
app.post('/submit', upload.fields([
{ name: 'photos_whetherdoorisproperlygettingclosedornot', maxCount: 5 },
{ name: 'photos_door_filter_replaced_or_not', maxCount: 5 },
{ name: 'photos_door_filter_is_replaced', maxCount: 5 },
{ name: 'photos_properly_clean_up_the_rack_including_routing_of_cables', maxCount: 5 }
]), async (req, res) => {
try {
const {
pic, date, sitecategorytype, siteid, latlong, address, area, region, rack_type,
value_racktype_makeorelse, punchpointclerancesupportrequiredbyioh_racktype_makeorelse,
punchpointclearancebymsh_racktype_makeorelse, whether_door_is_properly_getting_closed_or_not,
value_whetherdoorisproperlygettingclosedornot, punchpointclerancesupportrequiredbyioh_whetherdoorisproperlygettingclosedornot,
punchpointclearancebymsh_whetherdoorisproperlygettingclosedornot,
grounding_or_rack_is_proper, value_grounding_or_rack_is_proper,
punchpointclerancesupportrequiredbyioh_grounding_or_rack_is_proper,
punchpointclearancebymsh_grounding_or_rack_is_proper,
door_filter_replaced_or_not, value_door_filter_replaced_or_not,
punchpointclerancesupportrequiredbyioh_door_filter_replaced_or_not,
punchpointclearancebymsh_door_filter_replaced_or_not,
any_water_seepage_indication, value_any_water_seepage_indication,
punchpointclerancesupportrequiredbyioh_any_water_seepage_indication,
punchpointclearancebymsh_any_water_seepage_indication,
any_other_opening_is_visible, value_any_other_opening_is_visible,
punchpointclerancesupportrequiredbyioh_any_other_opening_is_visible,
punchpointclearancebymsh_any_other_opening_is_visible,
lock_and_key_is_working, value_lock_and_key_is_working,
punchpointclerancesupportrequiredbyioh_lock_and_key_is_working,
punchpointclearancebymsh_lock_and_key_is_working,
door_filter_is_replaced, value_door_filter_is_replaced,
punchpointclerancesupportrequiredbyioh_door_filter_is_replaced,
punchpointclearancebymsh_door_filter_is_replaced,
cooling_fan_is_working_or_not, value_cooling_fan_is_working_or_not,
punchpointclerancesupportrequiredbyioh_cooling_fan_is_working_or_not,
punchpointclearancebymsh_cooling_fan_is_working_or_not,
properly_clean_up_the_rack_including_routing_of_cables,
value_properly_clean_up_the_rack_including_routing_of_cables,
punchpointclerancesupportrequiredbyioh_properly_clean_up_the_rack_including_routing_of_cables,
punchpointclearancebymsh_properly_clean_up_the_rack_including_routing_of_cables
} = req.body;
console.log('Files:', req.files);
console.log('Body:', req.body);
// Membuat nama folder baru
const folderName = `${pic}_${siteid}_${date}`;
const folderPath = path.join(__dirname, 'respons', folderName);
// Membuat folder jika belum ada
if (!fs.existsSync(folderPath)) {
fs.mkdirSync(folderPath, { recursive: true });
}
// Memproses data ke Excel dan menyimpannya
const filePath = 'Audit_Site.xlsx';
if (fs.existsSync(filePath)) {
const workbook = new ExcelJS.Workbook();
await workbook.xlsx.readFile(filePath);
// Akses sheet pertama
const worksheet = workbook.getWorksheet('Sheet1');
let worksheetSheet2 = workbook.getWorksheet('Sheet2');
if (!worksheetSheet2) {
worksheetSheet2 = workbook.addWorksheet('Sheet2');
}
// Menyisipkan gambar pada masing-masing foto
await insertImagesToExcel(req.files['photos_whetherdoorisproperlygettingclosedornot'], 'photos_whetherdoorisproperlygettingclosedornot', 'A', worksheetSheet2, workbook, folderPath);
await insertImagesToExcel(req.files['photos_door_filter_replaced_or_not'], 'photos_door_filter_replaced_or_not', 'B', worksheetSheet2, workbook, folderPath);
await insertImagesToExcel(req.files['photos_door_filter_is_replaced'], 'photos_door_filter_is_replaced', 'C', worksheetSheet2, workbook, folderPath);
await insertImagesToExcel(req.files['photos_properly_clean_up_the_rack_including_routing_of_cables'], 'photos_properly_clean_up_the_rack_including_routing_of_cables', 'D', worksheetSheet2, workbook, folderPath);
worksheet.getCell('B2').value = pic;
worksheet.getCell('A5').value = date;
worksheet.getCell('E5').value = sitecategorytype;
worksheet.getCell('E6').value = siteid;
worksheet.getCell('E7').value = latlong;
worksheet.getCell('E8').value = address;
worksheet.getCell('E9').value = area;
worksheet.getCell('E10').value = region;
worksheet.getCell('E11').value = value_racktype_makeorelse;
worksheet.getCell('F11').value = rack_type;
worksheet.getCell('H11').value = punchpointclerancesupportrequiredbyioh_racktype_makeorelse;
worksheet.getCell('I11').value = punchpointclearancebymsh_racktype_makeorelse;
worksheet.getCell('E12').value = value_whetherdoorisproperlygettingclosedornot;
worksheet.getCell('F12').value = whether_door_is_properly_getting_closed_or_not;
worksheet.getCell('H12').value = punchpointclerancesupportrequiredbyioh_whetherdoorisproperlygettingclosedornot;
worksheet.getCell('I12').value = punchpointclearancebymsh_whetherdoorisproperlygettingclosedornot;
worksheet.getCell('E13').value = value_grounding_or_rack_is_proper;
worksheet.getCell('F13').value = grounding_or_rack_is_proper;
worksheet.getCell('H13').value = punchpointclerancesupportrequiredbyioh_grounding_or_rack_is_proper;
worksheet.getCell('I13').value = punchpointclearancebymsh_grounding_or_rack_is_proper;
worksheet.getCell('E14').value = value_door_filter_replaced_or_not;
worksheet.getCell('F14').value = door_filter_replaced_or_not;
worksheet.getCell('H14').value = punchpointclerancesupportrequiredbyioh_door_filter_replaced_or_not;
worksheet.getCell('I14').value = punchpointclearancebymsh_door_filter_replaced_or_not;
worksheet.getCell('E15').value = value_any_water_seepage_indication;
worksheet.getCell('F15').value = any_water_seepage_indication;
worksheet.getCell('H15').value = punchpointclerancesupportrequiredbyioh_any_water_seepage_indication;
worksheet.getCell('I15').value = punchpointclearancebymsh_any_water_seepage_indication;
worksheet.getCell('E16').value = value_any_other_opening_is_visible;
worksheet.getCell('F16').value = any_other_opening_is_visible;
worksheet.getCell('H16').value = punchpointclerancesupportrequiredbyioh_any_other_opening_is_visible;
worksheet.getCell('I16').value = punchpointclearancebymsh_any_other_opening_is_visible;
worksheet.getCell('E17').value = value_lock_and_key_is_working;
worksheet.getCell('F17').value = lock_and_key_is_working;
worksheet.getCell('H17').value = punchpointclerancesupportrequiredbyioh_lock_and_key_is_working;
worksheet.getCell('I17').value = punchpointclearancebymsh_lock_and_key_is_working;
worksheet.getCell('E18').value = value_door_filter_is_replaced;
worksheet.getCell('F18').value = door_filter_is_replaced;
worksheet.getCell('H18').value = punchpointclerancesupportrequiredbyioh_door_filter_is_replaced;
worksheet.getCell('I18').value = punchpointclearancebymsh_door_filter_is_replaced;
worksheet.getCell('E19').value = value_cooling_fan_is_working_or_not;
worksheet.getCell('F19').value = cooling_fan_is_working_or_not;
worksheet.getCell('H19').value = punchpointclerancesupportrequiredbyioh_cooling_fan_is_working_or_not;
worksheet.getCell('I19').value = punchpointclearancebymsh_cooling_fan_is_working_or_not;
worksheet.getCell('E20').value = value_properly_clean_up_the_rack_including_routing_of_cables;
worksheet.getCell('F20').value = properly_clean_up_the_rack_including_routing_of_cables;
worksheet.getCell('H20').value = punchpointclerancesupportrequiredbyioh_properly_clean_up_the_rack_including_routing_of_cables;
worksheet.getCell('I20').value = punchpointclearancebymsh_properly_clean_up_the_rack_including_routing_of_cables;
// Simpan workbook ke Excel
const newExcelPath = path.join(folderPath, `${folderName}.xlsx`);
await workbook.xlsx.writeFile(newExcelPath);
console.log(`Data Excel disimpan di ${newExcelPath}`);
return res.json({ message: `Data dan file berhasil disimpan di folder: ${folderName}` });
} else {
return res.status(400).send('File Excel template tidak ditemukan.');
}
} catch (error) {
console.error('Error:', error);
res.status(500).send({ message: 'Terjadi kesalahan server.' });
}
});
// Menjalankan server
app.listen(PORT, () => {
console.log(`Server berjalan di http://localhost:${PORT}`);
});
