The problem is when you press the Save/Edit button and add a problem and enter the required information. And the time was lost. When I pressed save, I found that it recorded duplicate data in the section that already had a breakdownId (the data with the breakdownId will be data that was pulled from another database). The newly added data will not have a breakdownId. It will be NULL to use the breakdownId data to pull data from another database here. And there is a function to add data yourself and fix the problem as follows, such as
ID | breakdownId | Date | MachineCode | DocNo | Cause | bdst | bden | UpdatedAt | Downtime | notes | lastupdate | SourceDB
182 | 10458939 | 2024-10-02 | COM007-1 | NULL | Waiting for crane | 2024-10-02 11:19:00.000 | 2024-10-02 12:11:00.000 | NULL | 52 | Waiting for crane from TWO ROLLER | 2024-10-02 12:11:31.153 | mswplus
When I pressed the save button on the web app, even though nothing had been edited. It added this data itself
ID breakdownId Date MachineCode DocNo Cause bdst bden UpdatedAt Downtime notes lastupdate SourceDB
ID | breakdownId | Date | MachineCode | DocNo | Cause | bdst | bden | UpdatedAt | Downtime | notes | lastupdate | SourceDB
189 | NULL | 2024-10-02 | COM007 | NULL | Waiting for crane | NULL | NULL | 2024-10-02 15:24:06.497 | 52 | NULL | NULL | NULL
Which is like a duplicate record. How can I fix it so that it does not duplicate record data with breakdownId?
And here is the code in this web app.
function editProblem(machineCode, date, docNo) {
const row = document.querySelector(`#barChartTable tr[data-machine="${machineCode}"][data-date="${date}"]`);
if (row) {
const problemDetailsCell = row.querySelector('.problem-details');
let currentProblems = Array.from(problemDetailsCell.querySelectorAll('div')).map(div => {
const match = div.textContent.match(/(.*) ((d+) นาที)/);
return {
description: match ? match[1].trim() : div.textContent,
downtime: match ? parseInt(match[2]) : 0,
breakdownId: div.dataset.breakdownid || null,
id: div.dataset.id || null,
originalDowntime: match ? parseInt(match[2]) : 0
};
});
// เพิ่มส่วนนี้เพื่อกำหนด isExisting และ isNew
currentProblems = currentProblems.map(problem => {
if (problem.breakdownId) {
return { ...problem, isExisting: true };
} else if (problem.id) {
return { ...problem, isExisting: true };
} else {
return { ...problem, isNew: true };
}
});
let formHTML = '<form id="editProblemForm">';
currentProblems.forEach((problem, index) => {
formHTML += `
<div>
<input type="text" name="description[]" value="${problem.description}" readonly>
<input type="number" name="downtime[]" value="${problem.downtime}" min="0" ${problem.breakdownId ? '' : 'readonly'}> นาที
<input type="hidden" name="breakdownId[]" value="${problem.breakdownId || ''}">
<input type="hidden" name="id[]" value="${problem.id || ''}">
<input type="hidden" name="originalDowntime[]" value="${problem.originalDowntime}">
<input type="hidden" name="isExisting[]" value="${problem.isExisting ? 'true' : 'false'}">
<input type="hidden" name="isNew[]" value="${problem.isNew ? 'true' : 'false'}">
</div>
`;
});
formHTML += '<button type="button" onclick="addNewProblemField()">เพิ่มปัญหา</button>';
formHTML += '<button type="submit">บันทึก</button></form>';
showEditModal(formHTML);
document.getElementById('editProblemForm').onsubmit = function(e) {
e.preventDefault();
const formData = new FormData(e.target);
const problems = [];
const descriptions = formData.getAll('description[]');
const downtimes = formData.getAll('downtime[]');
const breakdownIds = formData.getAll('breakdownId[]');
const ids = formData.getAll('id[]');
const originalDowntimes = formData.getAll('originalDowntime[]');
const isExistings = formData.getAll('isExisting[]');
const isNews = formData.getAll('isNew[]');
let hasChanges = false;
for (let i = 0; i < descriptions.length; i++) {
if (descriptions[i] && downtimes[i]) {
const currentDowntime = parseInt(downtimes[i]);
const originalDowntime = parseInt(originalDowntimes[i]);
if (isExistings[i] === 'true') {
// ถ้าเป็นข้อมูลที่มีอยู่แล้วและ downtime เปลี่ยน
if (currentDowntime !== originalDowntime) {
problems.push({
description: descriptions[i],
downtime: currentDowntime,
breakdownId: breakdownIds[i] || null,
id: ids[i] || null,
isExisting: true
});
hasChanges = true;
}
} else if (isNews[i] === 'true') {
// ถ้าเป็นข้อมูลใหม่
problems.push({
description: descriptions[i],
downtime: currentDowntime,
isNew: true
});
hasChanges = true;
}
}
}
if (hasChanges) {
console.log('Problems to be saved:', problems);
saveProblem(null, machineCode, date, docNo, problems);
} else {
alert('ไม่มีการเปลี่ยนแปลงข้อมูล');
closeEditModal();
}
};
}
}
function addNewProblemField() {
const form = document.getElementById('editProblemForm');
const newField = document.createElement('div');
newField.innerHTML = `
<input type="text" name="description[]" placeholder="รายละเอียดปัญหา">
<input type="number" name="downtime[]" placeholder="เวลาที่เสีย" min="0"> นาที
<input type="hidden" name="breakdownId[]" value="">
<input type="hidden" name="id[]" value="">
<input type="hidden" name="originalDowntime[]" value="0">
<input type="hidden" name="isExisting[]" value="false">
<input type="hidden" name="isNew[]" value="true">
`;
form.insertBefore(newField, form.lastElementChild);
}
function removeProblem(button) {
button.parentElement.remove();
}
function showEditModal(content) {
const modal = document.createElement('div');
modal.id = 'editModal';
modal.style.position = 'fixed';
modal.style.left = '50%';
modal.style.top = '50%';
modal.style.transform = 'translate(-50%, -50%)';
modal.style.backgroundColor = 'white';
modal.style.padding = '20px';
modal.style.border = '1px solid black';
modal.style.zIndex = '1000';
modal.innerHTML = content;
document.body.appendChild(modal);
}
function closeEditModal() {
const modal = document.getElementById('editModal');
if (modal) modal.remove();
}
async function refreshTableData() {
const rows = document.querySelectorAll('#barChartTable tbody tr');
const causeData = await fetchCauseData(selectedDate);
rows.forEach(row => {
const machineCode = row.getAttribute('data-machine');
const docNo = row.getAttribute('data-docno');
const relevantCause = causeData.find(c => c.MachineCode === machineCode && c.DocNo === docNo);
if (relevantCause) {
const problemDetailsCell = row.querySelector('.problem-details');
const downtimeCell = row.querySelector('td:nth-child(7)'); // เปลี่ยนเป็น 7 เนื่องจากลำดับคอลัมน์เปลี่ยนไป
if (problemDetailsCell && downtimeCell) {
const causeText = relevantCause.Causes.join(', ');
problemDetailsCell.textContent = causeText;
problemDetailsCell.setAttribute('data-total-downtime', relevantCause.TotalDowntime);
downtimeCell.textContent = relevantCause.TotalDowntime;
}
}
const actionCell = row.querySelector('td:nth-child(8)'); // ปรับตามลำดับคอลัมน์จริง
if (actionCell) {
actionCell.innerHTML = `<button onclick="editProblem('${machineCode}', '${docNo}')">แก้ไขปัญหา</button>`;
}
});
// เพิ่มการเรียกใช้ฟังก์ชันปรับความกว้าง header หลังจากอัปเดตข้อมูล
adjustHeaderWidth();
}
function saveCause() {
const causeData = [];
// รวบรวมข้อมูล Cause จากตาราง
document.querySelectorAll('#barChartTable tbody tr').forEach(row => {
const docNo = row.getAttribute('data-docno');
const causeInput = row.querySelector('input[name="cause"]');
if (causeInput && causeInput.value) {
causeData.push({
date: selectedDate,
machineCode: row.cells[0].textContent,
docNo: docNo,
cause: causeInput.value
});
}
})
fetch('/api/saveAllCause', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ data: causeData}),
})
.then(response => response.json())
.then(data => {
if (data.message) {
alert('บันทึก Cause สำเร็จ');
} else {
alert('เกิดข้อผิดพลาดในการบันทึก Cause');
}
})
.catch((error) => {
console.error('Error:', error);
alert('เกิดข้อผิดพลาดในการบันทึก Cause');
});
}
API =
router.post('/updateCausesMswPlus', async (req, res) => {
const { date, machineCode, problems } = req.body;
console.log('ได้รับคำขอสำหรับ updateCausesMswPlus:', { date, machineCode, problems });
if (!date || !machineCode || !Array.isArray(problems)) {
return res.status(400).json({ success: false, message: 'ข้อมูลไม่ถูกต้อง' });
}
try {
if (!dbSQL) {
throw new Error('การเชื่อมต่อฐานข้อมูลไม่ได้ถูกสร้างขึ้น');
}
const transaction = new sql.Transaction(dbSQL);
await transaction.begin();
try {
const updatedProblems = [];
for (const problem of problems) {
if (problem.isExisting) {
// อัพเดท Downtime สำหรับปัญหาที่มีอยู่แล้ว
if (problem.breakdownId) {
await transaction.request()
.input('breakdownId', sql.Int, parseInt(problem.breakdownId))
.input('Downtime', sql.Float, parseFloat(problem.downtime))
.query`
UPDATE [Production_Analytics].[dbo].[DailyProductionCausesMswPlus]
SET Downtime = @Downtime, UpdatedAt = GETDATE()
WHERE breakdownId = @breakdownId
`;
} else if (problem.id) {
await transaction.request()
.input('Id', sql.Int, parseInt(problem.id))
.input('Downtime', sql.Float, parseFloat(problem.downtime))
.query`
UPDATE [Production_Analytics].[dbo].[DailyProductionCausesMswPlus]
SET Downtime = @Downtime, UpdatedAt = GETDATE()
WHERE ID = @Id
`;
}
updatedProblems.push(problem);
} else if (problem.isNew) {
// เพิ่มข้อมูลใหม่
const result = await transaction.request()
.input('Date', sql.Date, new Date(date))
.input('MachineCode', sql.NVarChar(50), machineCode)
.input('Cause', sql.NVarChar(500), problem.description)
.input('Downtime', sql.Float, parseFloat(problem.downtime))
.query`
INSERT INTO [Production_Analytics].[dbo].[DailyProductionCausesMswPlus]
(Date, MachineCode, Cause, Downtime, UpdatedAt)
OUTPUT INSERTED.ID
VALUES (@Date, @MachineCode, @Cause, @Downtime, GETDATE())
`;
const newId = result.recordset[0].ID;
updatedProblems.push({ ...problem, id: newId });
}
}
await transaction.commit();
console.log('Updated problems:', updatedProblems);
res.json({ success: true, message: 'อัปเดตสาเหตุสำเร็จ', updatedProblems });
} catch (error) {
await transaction.rollback();
throw error;
}
} catch (error) {
console.error('เกิดข้อผิดพลาดในการอัปเดตสาเหตุ:', error);
res.status(500).json({ success: false, message: 'ไม่สามารถอัปเดตสาเหตุได้', error: error.message });
}
});