搜索到
103
篇与
的结果
-
MYsql绑定参数案例 SQL 语句的结构sqlCopy CodeINSERT INTO complete (part_number, quantity, time)VALUES (?, ?, ?)ON DUPLICATE KEY UPDATE estimated_delivery_date = ?, quantity = ?, zt = ?INSERT INTO complete (part_number, quantity, time)指定要插入数据的表 complete 和对应的列：part_number（零件编号）、quantity（数量）、time（时间）。VALUES (?, ?, ?)使用占位符 ? 表示需要动态插入的值。这些值会在后续通过 bind_param 方法绑定。ON DUPLICATE KEY UPDATE如果插入的数据与表中已有的某条记录的主键或唯一键冲突（即重复），则执行更新操作，而不是插入新记录。estimated_delivery_date = ?, quantity = ?, zt = ?如果发生冲突，更新 estimated_delivery_date（预计交付日期）、quantity（数量）和 zt（某个字段，可能是状态或其他信息）的值。这些值也通过占位符 ? 表示。$stmt = $mysqli->prepare(...)使用 $mysqli->prepare 方法准备 SQL 语句，返回一个 mysqli_stmt 对象（即预处理语句对象）。预处理语句可以提高 SQL 执行效率，并防止 SQL 注入攻击。bind_param 方法phpCopy Code$stmt->bind_param("ssissssi", $partNumber, $quantity, $time);将动态值绑定到 SQL 语句中的占位符 ?。第一个参数 "ssissssi"指定每个占位符的数据类型：s：字符串i：整数根据 SQL 语句，这里可能需要调整数据类型。后续参数按顺序传入要绑定的值：$partNumber、$quantity、$time 等。ON DUPLICATE KEY UPDATE 的逻辑如果 part_number 是表的主键或唯一键，且插入的 part_number 已存在，则执行更新操作。更新的字段是 estimated_delivery_date、quantity 和 zt，其值通过占位符 ? 绑定。代码中的问题bind_param 的参数数量不匹配SQL 语句中有 6 个占位符（?, ?, ?, ?, ?, ?），但 bind_param 只绑定了 3 个值（$partNumber, $quantity, $time）。需要补充 estimated_delivery_date、quantity 和 zt 的值。数据类型可能不匹配确保 bind_param 的第一个参数（如 "ssissssi"）与传入值的数据类型一致。缺少更新字段的值在 ON DUPLICATE KEY UPDATE 中，estimated_delivery_date、quantity 和 zt 的值需要在循环中动态绑定。 -
批量更新SQL语句 $placeholders = implode(',', array_fill(0, count($receivedOrderNumbers), '?')); $sql = "UPDATE orders SET zt = CASE WHEN order_number IN ($placeholders) THEN 'open' ELSE 'close' END"; 快速模式 回答完成 这段代码的目的是生成一个 UPDATE SQL 查询，根据 order_number 的值动态更新 zt 字段为 'open' 或 'close'。以下是代码的详细解析和注意事项： 代码解析： $placeholders = implode(',', array_fill(0, count($receivedOrderNumbers), '?')): 生成一个包含 ? 占位符的字符串，占位符的数量与 $receivedOrderNumbers 数组的长度一致。 例如，如果 $receivedOrderNumbers 有 3 个元素，$placeholders 的值将是 '?,?,?'。 $sql = "UPDATE orders SET zt = CASE WHEN order_number IN ($placeholders) THEN 'open' ELSE 'close' END": 构建一个 UPDATE 查询，使用 CASE 语句动态更新 zt 字段。 如果 order_number 在 $receivedOrderNumbers 中，则将 zt 设置为 'open'，否则设置为 'close'。 生成的 SQL 示例： 假设 $receivedOrderNumbers 是 [101, 102, 103]，生成的 SQL 查询将是： sql Copy Code UPDATE orders SET zt = CASE WHEN order_number IN (?, ?, ?) THEN 'open' ELSE 'close' END 使用场景： 这段代码适用于批量更新数据库中某个字段的值，根据条件动态设置不同的值。例如： 将特定订单的状态更新为 'open'，其他订单的状态更新为 'close'。 注意事项： 参数绑定： 使用 ? 占位符时，必须将 $receivedOrderNumbers 的值作为参数绑定到查询中，以避免 SQL 注入风险。 示例（使用 PDO）： php Copy Code $stmt = $pdo->prepare($sql); $stmt->execute($receivedOrderNumbers); ELSE 的作用： 如果没有 ELSE，所有不满足 WHEN 条件的记录将不会被更新。 如果希望未匹配的记录保持原值，可以移除 ELSE 'close'。 性能问题： 如果 $receivedOrderNumbers 包含大量元素，生成的 SQL 可能会很长，影响性能。 可以考虑分批更新或优化查询逻辑。 改进建议： 如果只需要更新匹配的记录，可以简化查询： php Copy Code $placeholders = implode(',', array_fill(0, count($receivedOrderNumbers), '?')); $sql = "UPDATE orders SET zt = 'open' WHERE order_number IN ($placeholders)"; 这样只会更新 order_number 在 $receivedOrderNumbers 中的记录，其他记录不会被修改。
-
自动排产最终版本 <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>缠布缺件排产</title> <script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script> <style> body { font-family: Arial, sans-serif; margin: 20px; line-height: 1.6; } .container { max-width: 1200px; margin: 0 auto; } h1, h2 { color: #333; } .section { margin-bottom: 30px; padding: 20px; border: 1px solid #ddd; border-radius: 5px; background-color: #f9f9f9; } table { width: 100%; border-collapse: collapse; margin-top: 15px; } th, td { border: 1px solid #ddd; padding: 8px; text-align: left; } th { background-color: #f2f2f2; } tr:nth-child(even) { background-color: #f9f9f9; } .warning { color: #d9534f; font-weight: bold; } .input-group { margin-bottom: 15px; } label { display: inline-block; width: 200px; margin-right: 10px; } input, button { padding: 8px; margin-right: 10px; } button { background-color: #4CAF50; color: white; border: none; cursor: pointer; border-radius: 4px; } button:hover { background-color: #45a049; } #fileInput { display: none; } .file-upload { display: inline-block; padding: 8px 12px; background: #337ab7; color: white; border-radius: 4px; cursor: pointer; } .file-upload:hover { background: #286090; } #loading { display: none; margin-left: 10px; color: #337ab7; font-weight: bold; } .summary { margin-top: 15px; padding: 10px; background-color: #e7f3fe; border-left: 5px solid #2196F3; } .stats { margin-top: 10px; font-weight: bold; } .on-time { color: #5cb85c; } .late { color: #d9534f; } </style> </head> <body> <div> <h1>缠布缺件排产</h1> <div> <h2>产能设置</h2> <div> <label for="workers">每日生产人数:</label> <input type="number" id="workers" value="3" min="1"> </div> <div> <label for="productivity">每人每日产量:</label> <input type="number" id="productivity" value="70" min="1"> </div> <div> <label for="startDate">排产开始日期:</label> <input type="date" id="startDate"> </div> </div> <div> <h2>数据导入</h2> <div> <label>Excel数据导入:</label> <label for="fileInput">选择Excel文件</label> <input type="file" id="fileInput" accept=".xlsx, .xls" onchange="handleFileUpload(this.files)"> <span id="fileName" style="margin-left:10px;"></span> </div> <div> <label>Excel格式要求:</label> <span>第一列:产品编号, 第二列:交付日期, 第三列:缺件数量</span> </div> </div> <div> <h2>原始数据</h2> <div id="rawDataSummary"></div> <table id="rawDataTable"> <thead> <tr> <th>产品编号</th> <th>交期处理（提前一天）</th> <th>缺件数量</th> </tr> </thead> <tbody></tbody> </table> </div> <div> <h2>排产计划</h2> <button onclick="generateSchedule()" id="generateBtn">生成排产计划</button> <span id="loading">正在计算中，请稍候...</span> <div id="scheduleSummary"></div> <div id="scheduleStats"></div> <table id="scheduleTable"> <thead> <tr> <th>产品编号</th> <th>缺件数量</th> <th>交付日期</th> <th>排产日期</th> <th>是否超期</th> </tr> </thead> <tbody></tbody> </table> </div> <div> <h2>预警清单</h2> <div id="warningSummary"></div> <table id="warningTable"> <thead> <tr> <th>产品编号</th> <th>缺件数量</th> <th>交付日期</th> <th>预计排产日期</th> <th>超期天数</th> </tr> </thead> <tbody></tbody> </table> </div> </div> <script> // 全局变量 let rawData = []; let scheduleData = []; let warningData = []; // 页面加载时设置默认日期为今天 window.onload = function() { const today = new Date(); const formattedDate = today.toISOString().split('T')[0]; document.getElementById('startDate').value = formattedDate; }; // 解析Excel日期数字 function parseExcelDate(excelDate) { const utcDays = Math.floor(excelDate - 25569); const utcValue = utcDays * 86400 * 1000; let date = new Date(utcValue); if (excelDate >= 60) { date.setTime(date.getTime() - 86400 * 1000); } return date.toISOString().split('T')[0]; } // 处理Excel文件上传 function handleFileUpload(files) { if (files.length === 0) return; const file = files[0]; document.getElementById('fileName').textContent = file.name; const reader = new FileReader(); reader.onload = function(e) { try { const data = new Uint8Array(e.target.result); const workbook = XLSX.read(data, { type: 'array' }); const firstSheet = workbook.Sheets[workbook.SheetNames[0]]; const jsonData = XLSX.utils.sheet_to_json(firstSheet, { header: 1 }); rawData = []; for (let i = 1; i < jsonData.length; i++) { if (jsonData[i].length >= 3) { let deliveryDate = ''; const dateValue = jsonData[i][1]; if (typeof dateValue === 'number') { deliveryDate = parseExcelDate(dateValue); } else if (dateValue instanceof Date) { deliveryDate = dateValue.toISOString().split('T')[0]; } else if (typeof dateValue === 'string') { const parsedDate = new Date(dateValue); if (!isNaN(parsedDate.getTime())) { deliveryDate = parsedDate.toISOString().split('T')[0]; } } let quantity = 0; if (typeof jsonData[i][2] === 'number') { quantity = Math.floor(jsonData[i][2]); } else if (typeof jsonData[i][2] === 'string') { quantity = parseInt(jsonData[i][2]) || 0; } rawData.push({ productId: String(jsonData[i][0] || ''), deliveryDate: deliveryDate, quantity: quantity, originalQuantity: quantity // 保存原始数量用于显示 }); } } console.log("解析后的数据:", rawData); displayRawData(); updateRawDataSummary(); } catch (error) { console.error("解析Excel文件时出错:", error); alert('解析Excel文件时出错: ' + error.message); } }; reader.onerror = function() { alert('读取文件时出错'); }; reader.readAsArrayBuffer(file); } // 显示原始数据 function displayRawData() { const tbody = document.querySelector('#rawDataTable tbody'); tbody.innerHTML = ''; rawData.forEach(item => { const row = document.createElement('tr'); row.innerHTML = ` <td>${item.productId}</td> <td>${item.deliveryDate}</td> <td>${item.originalQuantity}</td> `; tbody.appendChild(row); }); } // 更新原始数据摘要 function updateRawDataSummary() { const summary = document.getElementById('rawDataSummary'); const totalItems = rawData.length; const totalQuantity = rawData.reduce((sum, item) => sum + item.originalQuantity, 0); summary.innerHTML = ` 共 ${totalItems} 条记录，总缺件数量: ${totalQuantity} 件 ${rawData.some(item => !item.deliveryDate) ? '<span> (警告: 部分记录缺少交付日期)</span>' : ''} `; } // 生成排产计划（精确交付版） function generateSchedule() { const btn = document.getElementById('generateBtn'); const loading = document.getElementById('loading'); btn.disabled = true; loading.style.display = 'inline'; setTimeout(() => { try { console.log("开始生成精确交付排产计划..."); // 验证数据 if (rawData.length === 0) { alert('请先导入Excel数据'); return; } const invalidData = rawData.filter(item => !item.deliveryDate || isNaN(new Date(item.deliveryDate).getTime()) || isNaN(item.originalQuantity) ); if (invalidData.length > 0) { console.error("无效数据:", invalidData); alert(`发现 ${invalidData.length} 条无效记录，请检查数据`); return; } // 获取产能参数 const workers = parseInt(document.getElementById('workers').value) || 3; const productivity = parseInt(document.getElementById('productivity').value) || 70; const startDateStr = document.getElementById('startDate').value; if (!startDateStr) { alert('请设置排产开始日期'); return; } const dailyCapacity = workers * productivity; scheduleData = []; warningData = []; // 1. 按交付日期升序排序，数量小的优先 const sortedData = [...rawData].sort((a, b) => { // 先按交付日期排序 const dateDiff = new Date(a.deliveryDate) - new Date(b.deliveryDate); if (dateDiff !== 0) return dateDiff; // 同一天交付的，数量小的优先 return a.originalQuantity - b.originalQuantity; }); console.log("排序后的数据:", sortedData); // 2. 初始化生产日历 let currentDate = new Date(startDateStr); let dailyRemaining = dailyCapacity; // 3. 逐个处理每个订单（即使产品相同也单独处理） for (const item of sortedData) { // 重置数量为原始值（因为前面的处理可能修改了quantity） item.quantity = item.originalQuantity; let productionDate = new Date(currentDate); let quantityRemaining = item.quantity; let isLate = false; // 分配生产日期 while (quantityRemaining > 0) { if (dailyRemaining === 0) { // 转到下一天 currentDate.setDate(currentDate.getDate() + 1); dailyRemaining = dailyCapacity; productionDate = new Date(currentDate); } const allocate = Math.min(quantityRemaining, dailyRemaining); // 检查是否超期（生产日期 > 交付日期） const currentIsLate = productionDate > new Date(item.deliveryDate); isLate = isLate || currentIsLate; scheduleData.push({ productId: item.productId, quantity: allocate, deliveryDate: item.deliveryDate, productionDate: productionDate.toISOString().split('T')[0], isLate: currentIsLate }); quantityRemaining -= allocate; dailyRemaining -= allocate; } // 如果整个订单超期，添加到预警 if (isLate) { const lastProdDate = new Date( scheduleData.filter(x => x.productId === item.productId && x.deliveryDate === item.deliveryDate ) .reduce((latest, curr) => new Date(curr.productionDate) > new Date(latest.productionDate) ? curr : latest ).productionDate ); const lateDays = Math.ceil( (lastProdDate - new Date(item.deliveryDate)) / (1000 * 60 * 60 * 24) ); warningData.push({ productId: item.productId, quantity: item.originalQuantity, deliveryDate: item.deliveryDate, productionDate: lastProdDate.toISOString().split('T')[0], lateDays: lateDays }); } } // 4. 显示结果 displaySchedule(); displayWarnings(); updateSummary(); console.log("精确交付排产计划生成完成"); console.log("排产数据:", scheduleData); console.log("预警数据:", warningData); } catch (error) { console.error("生成排产计划时出错:", error); alert('生成排产计划时出错: ' + error.message); } finally { btn.disabled = false; loading.style.display = 'none'; } }, 100); } // 更新摘要信息 function updateSummary() { const scheduleSummary = document.getElementById('scheduleSummary'); const warningSummary = document.getElementById('warningSummary'); const statsElement = document.getElementById('scheduleStats'); const totalOrders = rawData.length; const totalQuantity = rawData.reduce((sum, item) => sum + item.originalQuantity, 0); const lateOrders = warningData.length; scheduleSummary.innerHTML = ` 共 ${totalOrders} 个订单需要生产，总数量: ${totalQuantity} 件 `; const warningQuantity = warningData.reduce((sum, item) => sum + item.quantity, 0); warningSummary.innerHTML = ` 共 ${lateOrders} 个订单需要预警，总数量: ${warningQuantity} 件 `; // 计算按时完成率 const onTimeRate = totalOrders > 0 ? ((totalOrders - lateOrders) / totalOrders * 100).toFixed(2) : 0; statsElement.innerHTML = ` <span>按时完成率: ${onTimeRate}% (${totalOrders - lateOrders}/${totalOrders})</span> | <span>超期订单: ${lateOrders}</span> `; } // 显示排产计划 function displaySchedule() { const tbody = document.querySelector('#scheduleTable tbody'); tbody.innerHTML = ''; // 按交付日期分组显示 const deliveryGroups = [...new Set(rawData.map(item => item.deliveryDate))].sort((a, b) => new Date(a) - new Date(b) ); for (const deliveryDate of deliveryGroups) { // 获取该交付日期的所有订单 const orders = rawData.filter(item => item.deliveryDate === deliveryDate); // 检查该交付日期是否有超期订单 const hasLateOrders = warningData.some(item => item.deliveryDate === deliveryDate); // 添加交付日期标题行 const headerRow = document.createElement('tr'); headerRow.style.backgroundColor = '#f0f0f0'; headerRow.innerHTML = ` <td colspan="5"> <strong>交付日期: ${deliveryDate}</strong> ${hasLateOrders ? '<span> (有超期订单)</span>' : '<span> (全部按时)</span>'} </td> `; tbody.appendChild(headerRow); // 添加该交付日期的所有订单 for (const order of orders) { // 获取该订单的所有排产记录 const orderSchedule = scheduleData.filter(item => item.productId === order.productId && item.deliveryDate === order.deliveryDate ); const isLate = orderSchedule.some(item => item.isLate); orderSchedule.forEach((item, index) => { const row = document.createElement('tr'); if (isLate) { row.classList.add('warning'); } row.innerHTML = ` <td>${index === 0 ? item.productId : ''}</td> <td>${item.quantity}</td> <td>${index === 0 ? item.deliveryDate : ''}</td> <td>${item.productionDate}</td> <td>${index === 0 ? (isLate ? '是' : '否') : ''}</td> `; tbody.appendChild(row); }); } } } // 显示预警清单 function displayWarnings() { const tbody = document.querySelector('#warningTable tbody'); tbody.innerHTML = ''; // 按交付日期分组显示预警 const warningGroups = warningData.reduce((groups, item) => { if (!groups[item.deliveryDate]) { groups[item.deliveryDate] = []; } groups[item.deliveryDate].push(item); return groups; }, {}); const sortedDeliveryDates = Object.keys(warningGroups).sort((a, b) => new Date(a) - new Date(b) ); for (const deliveryDate of sortedDeliveryDates) { const groupItems = warningGroups[deliveryDate]; // 添加分组标题行 const headerRow = document.createElement('tr'); headerRow.style.backgroundColor = '#f0f0f0'; headerRow.innerHTML = ` <td colspan="5"> <strong>超期交付日期: ${deliveryDate}</strong> <span> (超期 ${groupItems[0].lateDays} 天)</span> </td> `; tbody.appendChild(headerRow); // 添加订单行 for (const item of groupItems) { const row = document.createElement('tr'); row.classList.add('warning'); row.innerHTML = ` <td>${item.productId}</td> <td>${item.quantity}</td> <td>${item.deliveryDate}</td> <td>${item.productionDate}</td> <td>${item.lateDays}</td> `; tbody.appendChild(row); } } } </script> </body> </html>
-
排产代码优化备份 <!DOCTYPE html><meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>缺件排产优化工具</title> <script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script> <style> /* 保持原有样式不变 */ </style> <!-- 保持原有HTML结构不变 --> <script> // [之前的所有函数保持不变...] // 修改后的生成排产计划函数（优化版） function generateSchedule() { const btn = document.getElementById('generateBtn'); const loading = document.getElementById('loading'); btn.disabled = true; loading.style.display = 'inline'; setTimeout(() => { try { console.log("开始生成优化排产计划..."); // [之前的参数检查和数据验证保持不变...] const workers = parseInt(document.getElementById('workers').value) || 3; const productivity = parseInt(document.getElementById('productivity').value) || 70; const startDateStr = document.getElementById('startDate').value; const dailyCapacity = workers * productivity; // 1. 按交付日期升序排序（最早交付的优先） const sortedData = [...rawData].sort((a, b) => { return new Date(a.deliveryDate) - new Date(b.deliveryDate); }); // 2. 计算每个项目的最晚开始日期（交付日期前一天） const itemsWithDeadline = sortedData.map(item => { const deadline = new Date(item.deliveryDate); deadline.setDate(deadline.getDate() - 1); // 交付前一天 return { ...item, deadline: deadline, productionDays: Math.ceil(item.quantity / dailyCapacity) }; }); // 3. 初始化排产计划 scheduleData = []; warningData = []; let currentDate = new Date(startDateStr); let dailyRemaining = dailyCapacity; // 4. 优化排产算法 - 尽可能在截止日期前完成 for (const item of itemsWithDeadline) { let productionDate = new Date(currentDate); let quantityRemaining = item.quantity; let isLate = false; // 计算最合理的开始生产日期 // 尝试从截止日期倒推需要的生产天数 const requiredDays = Math.ceil(item.quantity / dailyCapacity); let idealStartDate = new Date(item.deadline); idealStartDate.setDate(idealStartDate.getDate() - requiredDays + 1); // 如果理想开始日期比当前日期早，就从当前日期开始 if (idealStartDate < currentDate) { idealStartDate = new Date(currentDate); } productionDate = new Date(idealStartDate); // 分配生产日期 while (quantityRemaining > 0) { if (dailyRemaining === 0) { // 转到下一天 productionDate.setDate(productionDate.getDate() + 1); dailyRemaining = dailyCapacity; } const allocate = Math.min(quantityRemaining, dailyRemaining); // 检查是否超期 const currentIsLate = productionDate > item.deadline; isLate = isLate || currentIsLate; scheduleData.push({ productId: item.productId, quantity: allocate, deliveryDate: item.deliveryDate, productionDate: productionDate.toISOString().split('T')[0], isLate: currentIsLate }); quantityRemaining -= allocate; dailyRemaining -= allocate; } // 更新当前日期到最后生产日期的下一天 currentDate = new Date(productionDate); currentDate.setDate(currentDate.getDate() + 1); dailyRemaining = dailyCapacity; // 如果整个项目超期，添加到预警 if (isLate) { const lastProdDate = new Date( scheduleData.filter(x => x.productId === item.productId) .reduce((latest, curr) => new Date(curr.productionDate) > new Date(latest.productionDate) ? curr : latest ).productionDate ); const lateDays = Math.ceil( (new Date(lastProdDate) - new Date(item.deadline)) / (1000 * 60 * 60 * 24) ); warningData.push({ productId: item.productId, quantity: item.quantity, deliveryDate: item.deliveryDate, productionDate: lastProdDate, lateDays: lateDays }); } } // [保持原有的显示和摘要更新函数不变...] } catch (error) { console.error("生成排产计划时出错:", error); alert('生成排产计划时出错: ' + error.message); } finally { btn.disabled = false; loading.style.display = 'none'; } }, 100); } // [其余函数保持不变...] </script>
-
缺件排产代码备份 <!DOCTYPE html><meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>缺件排产</title> <script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script> <style> body { font-family: Arial, sans-serif; margin: 20px; line-height: 1.6; } .container { max-width: 1200px; margin: 0 auto; } h1, h2 { color: #333; } .section { margin-bottom: 30px; padding: 20px; border: 1px solid #ddd; border-radius: 5px; background-color: #f9f9f9; } table { width: 100%; border-collapse: collapse; margin-top: 15px; } th, td { border: 1px solid #ddd; padding: 8px; text-align: left; } th { background-color: #f2f2f2; } tr:nth-child(even) { background-color: #f9f9f9; } .warning { color: #d9534f; font-weight: bold; } .input-group { margin-bottom: 15px; } label { display: inline-block; width: 200px; margin-right: 10px; } input, button { padding: 8px; margin-right: 10px; } button { background-color: #4CAF50; color: white; border: none; cursor: pointer; border-radius: 4px; } button:hover { background-color: #45a049; } #fileInput { display: none; } .file-upload { display: inline-block; padding: 8px 12px; background: #337ab7; color: white; border-radius: 4px; cursor: pointer; } .file-upload:hover { background: #286090; } #loading { display: none; margin-left: 10px; color: #337ab7; font-weight: bold; } .summary { margin-top: 15px; padding: 10px; background-color: #e7f3fe; border-left: 5px solid #2196F3; } </style> <div class="container"> <h1>缺件排产分析工具</h1> <div class="section"> <h2>产能设置</h2> <div class="input-group"> <label for="workers">每日生产人数:</label> <input type="number" id="workers" value="3" min="1"> </div> <div class="input-group"> <label for="productivity">每人每日产量:</label> <input type="number" id="productivity" value="70" min="1"> </div> <div class="input-group"> <label for="startDate">排产开始日期:</label> <input type="date" id="startDate"> </div> </div> <div class="section"> <h2>数据导入</h2> <div class="input-group"> <label>Excel数据导入:</label> <label for="fileInput" class="file-upload">选择Excel文件</label> <input type="file" id="fileInput" accept=".xlsx, .xls" onchange="handleFileUpload(this.files)"> <span id="fileName" style="margin-left:10px;"></span> </div> <div class="input-group"> <label>Excel格式要求:</label> <span>第一列:产品编号, 第二列:交付日期, 第三列:缺件数量</span> </div> </div> <div class="section"> <h2>原始数据</h2> <div id="rawDataSummary" class="summary"></div> <table id="rawDataTable"> <thead> <tr> <th>产品编号</th> <th>交付日期</th> <th>缺件数量</th> </tr> </thead> <tbody></tbody> </table> </div> <div class="section"> <h2>排产计划</h2> <button onclick="generateSchedule()" id="generateBtn">生成排产计划</button> <span id="loading">正在计算中，请稍候...</span> <div id="scheduleSummary" class="summary"></div> <table id="scheduleTable"> <thead> <tr> <th>产品编号</th> <th>缺件数量</th> <th>交付日期</th> <th>排产日期</th> <th>是否超期</th> </tr> </thead> <tbody></tbody> </table> </div> <div class="section"> <h2>预警清单</h2> <div id="warningSummary" class="summary"></div> <table id="warningTable"> <thead> <tr> <th>产品编号</th> <th>缺件数量</th> <th>交付日期</th> <th>预计排产日期</th> <th>超期天数</th> </tr> </thead> <tbody></tbody> </table> </div> </div> <script> // 全局变量 let rawData = []; let scheduleData = []; let warningData = []; // 页面加载时设置默认日期为今天 window.onload = function() { const today = new Date(); const formattedDate = today.toISOString().split('T')[0]; document.getElementById('startDate').value = formattedDate; }; // 解析Excel日期数字 function parseExcelDate(excelDate) { // Excel日期是从1900年1月1日开始的天数 // 注意：Excel错误地认为1900年是闰年，所以需要调整 const utcDays = Math.floor(excelDate - 25569); const utcValue = utcDays * 86400 * 1000; let date = new Date(utcValue); // 处理Excel的1900年闰年错误 if (excelDate >= 60) { date.setTime(date.getTime() - 86400 * 1000); } return date.toISOString().split('T')[0]; } // 处理Excel文件上传 function handleFileUpload(files) { if (files.length === 0) return; const file = files[0]; document.getElementById('fileName').textContent = file.name; const reader = new FileReader(); reader.onload = function(e) { try { const data = new Uint8Array(e.target.result); const workbook = XLSX.read(data, { type: 'array' }); // 假设数据在第一个工作表 const firstSheet = workbook.Sheets[workbook.SheetNames[0]]; const jsonData = XLSX.utils.sheet_to_json(firstSheet, { header: 1 }); // 提取数据 (假设第一行是标题) rawData = []; for (let i = 1; i < jsonData.length; i++) { if (jsonData[i].length >= 3) { // 处理日期 - 可能是数字或字符串 let deliveryDate = ''; const dateValue = jsonData[i][1]; if (typeof dateValue === 'number') { // Excel数字日期 deliveryDate = parseExcelDate(dateValue); } else if (dateValue instanceof Date) { // 已经是Date对象 deliveryDate = dateValue.toISOString().split('T')[0]; } else if (typeof dateValue === 'string') { // 尝试解析字符串日期 const parsedDate = new Date(dateValue); if (!isNaN(parsedDate.getTime())) { deliveryDate = parsedDate.toISOString().split('T')[0]; } } // 处理数量 let quantity = 0; if (typeof jsonData[i][2] === 'number') { quantity = Math.floor(jsonData[i][2]); } else if (typeof jsonData[i][2] === 'string') { quantity = parseInt(jsonData[i][2]) || 0; } rawData.push({ productId: String(jsonData[i][0] || ''), deliveryDate: deliveryDate, quantity: quantity }); } } console.log("解析后的数据:", rawData); displayRawData(); updateRawDataSummary(); } catch (error) { console.error("解析Excel文件时出错:", error); alert('解析Excel文件时出错: ' + error.message); } }; reader.onerror = function() { alert('读取文件时出错'); }; reader.readAsArrayBuffer(file); } // 显示原始数据 function displayRawData() { const tbody = document.querySelector('#rawDataTable tbody'); tbody.innerHTML = ''; rawData.forEach(item => { const row = document.createElement('tr'); row.innerHTML = ` <td>${item.productId}</td> <td>${item.deliveryDate}</td> <td>${item.quantity}</td> `; tbody.appendChild(row); }); } // 更新原始数据摘要 function updateRawDataSummary() { const summary = document.getElementById('rawDataSummary'); const totalItems = rawData.length; const totalQuantity = rawData.reduce((sum, item) => sum + item.quantity, 0); summary.innerHTML = ` 共 ${totalItems} 条记录，总缺件数量: ${totalQuantity} 件 ${rawData.some(item => !item.deliveryDate) ? '<span class="warning"> (警告: 部分记录缺少交付日期)</span>' : ''} `; } // 生成排产计划 function generateSchedule() { const btn = document.getElementById('generateBtn'); const loading = document.getElementById('loading'); btn.disabled = true; loading.style.display = 'inline'; // 使用setTimeout让UI有机会更新 setTimeout(() => { try { console.log("开始生成排产计划..."); if (rawData.length === 0) { alert('请先导入Excel数据'); return; } // 检查无效数据 const invalidData = rawData.filter(item => !item.deliveryDate || isNaN(new Date(item.deliveryDate).getTime()) || isNaN(item.quantity) ); if (invalidData.length > 0) { console.error("无效数据:", invalidData); alert(`发现 ${invalidData.length} 条无效记录，请检查数据`); return; } const workers = parseInt(document.getElementById('workers').value) || 3; const productivity = parseInt(document.getElementById('productivity').value) || 70; const startDateStr = document.getElementById('startDate').value; if (!startDateStr) { alert('请设置排产开始日期'); return; } const dailyCapacity = workers * productivity; let currentDate = new Date(startDateStr); let dailyRemaining = dailyCapacity; scheduleData = []; warningData = []; // 按交付日期排序 const sortedData = [...rawData].sort((a, b) => { return new Date(a.deliveryDate) - new Date(b.deliveryDate); }); console.log("排序后的数据:", sortedData); // 分配生产日期 for (const item of sortedData) { let productionDate = new Date(currentDate); let quantityRemaining = item.quantity; while (quantityRemaining > 0) { if (dailyRemaining === 0) { // 转到下一天 currentDate.setDate(currentDate.getDate() + 1); dailyRemaining = dailyCapacity; productionDate = new Date(currentDate); console.log(`转到下一天: ${productionDate.toISOString().split('T')[0]}, 剩余产能: ${dailyRemaining}`); } const allocate = Math.min(quantityRemaining, dailyRemaining); const isLate = productionDate > new Date(item.deliveryDate); scheduleData.push({ productId: item.productId, quantity: allocate, deliveryDate: item.deliveryDate, productionDate: productionDate.toISOString().split('T')[0], isLate: isLate }); quantityRemaining -= allocate; dailyRemaining -= allocate; console.log(`分配 ${allocate} 件, 剩余 ${quantityRemaining} 件, 当日剩余产能: ${dailyRemaining}`); } } // 生成预警清单 generateWarnings(); // 显示结果 displaySchedule(); displayWarnings(); updateSummary(); console.log("排产计划生成完成"); console.log("排产数据:", scheduleData); console.log("预警数据:", warningData); } catch (error) { console.error("生成排产计划时出错:", error); alert('生成排产计划时出错: ' + error.message); } finally { btn.disabled = false; loading.style.display = 'none'; } }, 100); } // 生成预警清单 function generateWarnings() { warningData = []; // 按产品分组 const productGroups = scheduleData.reduce((groups, item) => { if (!groups[item.productId]) { groups[item.productId] = []; } groups[item.productId].push(item); return groups; }, {}); // 检查每个产品的最后生产日期 for (const productId in productGroups) { const items = productGroups[productId]; const lastItem = items.reduce((latest, curr) => { return new Date(curr.productionDate) > new Date(latest.productionDate) ? curr : latest; }); if (lastItem.isLate) { const lateDays = Math.ceil( (new Date(lastItem.productionDate) - new Date(lastItem.deliveryDate)) / (1000 * 60 * 60 * 24) ); warningData.push({ productId: productId, quantity: items.reduce((sum, item) => sum + item.quantity, 0), deliveryDate: lastItem.deliveryDate, productionDate: lastItem.productionDate, lateDays: lateDays }); } } } // 更新摘要信息 function updateSummary() { const scheduleSummary = document.getElementById('scheduleSummary'); const warningSummary = document.getElementById('warningSummary'); const totalItems = scheduleData.length; const totalQuantity = scheduleData.reduce((sum, item) => sum + item.quantity, 0); const lateItems = scheduleData.filter(item => item.isLate).length; scheduleSummary.innerHTML = ` 共 ${totalItems} 条排产记录，总排产量: ${totalQuantity} 件，超期项目: ${lateItems} 个 `; const warningCount = warningData.length; const warningQuantity = warningData.reduce((sum, item) => sum + item.quantity, 0); warningSummary.innerHTML = ` 共 ${warningCount} 个产品需要预警，总数量: ${warningQuantity} 件 `; } // 显示排产计划 function displaySchedule() { const tbody = document.querySelector('#scheduleTable tbody'); tbody.innerHTML = ''; scheduleData.forEach(item => { const row = document.createElement('tr'); if (item.isLate) { row.classList.add('warning'); } row.innerHTML = ` <td>${item.productId}</td> <td>${item.quantity}</td> <td>${item.deliveryDate}</td> <td>${item.productionDate}</td> <td>${item.isLate ? '是' : '否'}</td> `; tbody.appendChild(row); }); } // 显示预警清单 function displayWarnings() { const tbody = document.querySelector('#warningTable tbody'); tbody.innerHTML = ''; // 按产品分组，只显示每个产品的最后一条记录 const groupedWarnings = warningData.reduce((acc, curr) => { acc[curr.productId] = curr; return acc; }, {}); Object.values(groupedWarnings).forEach(item => { const row = document.createElement('tr'); row.classList.add('warning'); row.innerHTML = ` <td>${item.productId}</td> <td>${item.quantity}</td> <td>${item.deliveryDate}</td> <td>${item.productionDate}</td> <td>${item.lateDays}</td> `; tbody.appendChild(row); }); } </script>
0:00