<!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)