import pandas as pd
import numpy as np
from datetime import datetime, timedelta, time
import os
基础参数（文档约束）
CHANGE_TIME = 3.5 # 换模时间（小时）
REST_PERIODS = [ # 每日休息时段（小时）
(12, 12.5), # 12:00-12:30
(16.5, 17), # 16:30-17:00
(0, 1) # 0:00-1:00
]
class Order:
“”“订单类：封装订单属性与计算逻辑”“”
def init(self, order_id, product_id, quantity, delivery_date, capacity, attr):
self.order_id = order_id # 订单号
self.product_id = product_id # 产品号
self.quantity = quantity # 数量（pcs）
self.delivery_date = delivery_date # 发货日期（datetime）
self.capacity = capacity # 产能（pcs/h）
self.processing_hours = quantity / capacity # 生产工时（h）
self.start_time = None # 开始时间
self.end_time = None # 结束时间
self.machine = None # 分配机台
self.delay_days = 0 # 延期天数
self.satisfaction = 10.0 # 满意度（初始10分）
self.attr = attr # 产品属性（1:易搭配，3:难生产）
self.merged_orders = [order_id] # 合单包含的原始订单ID（合单专用")
class MergedOrder:
“”“合单任务类：封装合并后的生产任务”“”
def init(self, product_id, total_quantity, delivery_dates, capacity, attr, original_orders):
self.product_id = product_id # 产品号
self.total_quantity = total_quantity # 合并后总数量
self.delivery_dates = delivery_dates # 原始订单发货日期列表
self.capacity = capacity # 产能
self.processing_hours = total_quantity / capacity # 总生产工时
self.attr = attr # 产品属性
self.original_orders = original_orders # 原始订单对象列表
self.start_time = None # 开始时间
self.end_time = None # 结束时间
self.machine = None # 分配机台
class Machine:
“”“机台类：封装机台属性与状态”“”
def init(self, machine_id, initial_time=None):
self.machine_id = machine_id # 机台号
# 设置初始可用时间
if initial_time is None:
self.available_time = datetime(2025, 3, 1)
else:
self.available_time = initial_time
self.last_product = None # 上一生产产品
self.adjacent = [] # 相邻机台列表
self.schedule = [] # 排程计划（任务列表）
def load_official_data():
“”“加载正式数据（使用相对路径）”“”
try:
# 1. 读取附件1(1)数据
attachment1 = pd.ExcelFile(r"C:\Users\彭婉\Desktop\2025-08\附件1(1).xlsx")
# 1.1 订单表
orders_df = attachment1.parse(
sheet_name=“订单表”,
parse_dates=[“发货日期(DeliveryDate)”]
)
# 1.2 机台初始工作状态表
machine_initial_df = attachment1.parse(
sheet_name=“机台初始工作状态表”,
parse_dates=[“生产开始时间”]
)
machine_initial_times = {
row[“机台号”]: row[“生产开始时间”]
for _, row in machine_initial_df.iterrows()
}
# 1.3 放假日期表
holidays_df = attachment1.parse(sheet_name=“放假日期表”)
holidays =
# 2. 读取附件2(1)数据 attachment2 = pd.ExcelFile(r"C:\Users\彭婉\Desktop\2025-08\附件2(1).xlsx") # 2.1 产品工时计算参数表 product_capacity_df = attachment2.parse(sheet_name="产品工时计算参数表") product_capacity = { row["产品号"]: row["Capacity（pcs/h）"] for _, row in product_capacity_df.iterrows() } # 2.2 产品机台生产关系表 product_machine_df = attachment2.parse(sheet_name="产品机台生产关系表") product_machines = {} for _, row in product_machine_df.iterrows(): product_id = row["产品号"] # 找出值为1的机台列 valid_machines = [col for col in product_machine_df.columns if col != "产品号" and row[col] == 1] product_machines[product_id] = valid_machines # 2.3 机台关系表 machine_relation_df = attachment2.parse(sheet_name="机台关系表") machine_adjacent = {} for _, row in machine_relation_df.iterrows(): machine_id = row["机台号"] # 找出值为1的相邻机台列 adjacent_machines = [col for col in machine_relation_df.columns if col != "机台号" and row[col] == 1] machine_adjacent[machine_id] = adjacent_machines # 2.4 产品属性表 product_attr_df = attachment2.parse(sheet_name="产品属性表") product_attrs = { row["产品号"]: row["属性"] for _, row in product_attr_df.iterrows() } # 3. 初始化订单数据（补充产能列） orders_df["Capacity（pcs/h）"] = orders_df["产品号"].map(product_capacity) # 4. 初始化机台对象 machines = [] for mid in machine_initial_times.keys(): # 确保每个机台都有初始时间 initial_time = machine_initial_times.get(mid, datetime(2025, 3, 1)) machine = Machine(mid, initial_time) # 设置相邻机台关系 machine.adjacent = machine_adjacent.get(mid, []) machines.append(machine) return orders_df, product_machines, product_attrs, machines, holidays except Exception as e: print(f"加载数据错误: {e}") # 返回空数据集避免崩溃 return pd.DataFrame(), {}, {}, [], set()
def merge_orders(orders, merge_days):
“”“合单逻辑：相同产品号的订单，发货日期在merge_days内的合并为一个生产任务”“”
merged_tasks = []
merge_count = 0 # 合单次数（合并的组数）
# 按产品号分组 product_groups = {} for order in orders: if order.product_id not in product_groups: product_groups[order.product_id] = [] product_groups[order.product_id].append(order) # 对每个产品组内的订单按发货日期排序并合单 for product_id, group_orders in product_groups.items(): # 按发货日期升序排序 group_orders.sort(key=lambda x: x.delivery_date) if len(group_orders) == 1: # 单个订单无需合单，直接作为一个任务 merged_tasks.append(MergedOrder( product_id=product_id, total_quantity=group_orders[0].quantity, delivery_dates=[group_orders[0].delivery_date], capacity=group_orders[0].capacity, attr=group_orders[0].attr, original_orders=[group_orders[0]] )) continue # 合并逻辑：滑动窗口检查发货日期差 current_merge = [group_orders[0]] for i in range(1, len(group_orders)): # 计算当前订单与组内第一个订单的发货日期差 date_diff = (group_orders[i].delivery_date - current_merge[0].delivery_date).days if date_diff <= merge_days: current_merge.append(group_orders[i]) else: # 超过合单窗口，生成合并任务 merged_tasks.append(MergedOrder( product_id=product_id, total_quantity=sum(o.quantity for o in current_merge), delivery_dates=[o.delivery_date for o in current_merge], capacity=current_merge[0].capacity, attr=current_merge[0].attr, original_orders=current_merge )) merge_count += 1 # 记录一次合单 current_merge = [group_orders[i]] # 处理最后一组合单 if len(current_merge) >= 1: merged_tasks.append(MergedOrder( product_id=product_id, total_quantity=sum(o.quantity for o in current_merge), delivery_dates=[o.delivery_date for o in current_merge], capacity=current_merge[0].capacity, attr=current_merge[0].attr, original_orders=current_merge )) if len(current_merge) > 1: merge_count += 1 # 仅多订单合并才计数 return merged_tasks, merge_count
def calculate_end_time(start_time, processing_hours, holidays):
“”“计算考虑休息和放假后的生产结束时间”“”
current = start_time
remaining = processing_hours
while remaining > 0: # 跳过放假日期 if current.date() in holidays: # 直接跳到第二天0点 current = datetime.combine(current.date() + timedelta(days=1), time(0, 0)) continue # 计算当前时间点 current_time = current.time() current_date = current.date() day_start = datetime.combine(current_date, time(0, 0)) # 计算剩余工作时间 work_hours_today = 24.0 for rest_start, rest_end in REST_PERIODS: work_hours_today -= (rest_end - rest_start) # 如果剩余工时小于一天工作量 if remaining <= work_hours_today: # 按小时累加，跳过休息时间 for hour in np.arange(0, 24, 0.1): # 检查是否在休息时间 in_rest = False for rest_start, rest_end in REST_PERIODS: if rest_start <= hour < rest_end: in_rest = True break if not in_rest: current += timedelta(hours=0.1) remaining -= 0.1 if remaining <= 0: return current else: # 直接消耗全天工作时间 remaining -= work_hours_today # 跳到第二天0点 current = datetime.combine(current_date + timedelta(days=1), time(0, 0)) return current
def problem3_scheduling(merged_tasks, product_machines, product_attrs, machines, holidays):
“”“问题三排程：考虑产品属性、机台关系及合单”“”
# 排序：优先易搭配>难生产>无限制，同属性按最早发货日期排序
merged_tasks.sort(key=lambda x: (
x.attr, # 1:易搭配优先，3:难生产其次，2:无限制最后
min(x.delivery_dates) # 按最早发货日期排序
))
# 机台映射表（便于查询相邻机台） machine_map = {m.machine_id: m for m in machines} for task in merged_tasks: # 筛选可生产该产品的机台 candidate_machines = [ m for m in machines if m.machine_id in product_machines.get(task.product_id, []) ] # 易搭配产品优先分配M03机台 if task.attr == 1: m03 = next((m for m in candidate_machines if m.machine_id == "M03"), None) if m03: candidate_machines = [m03] + [m for m in candidate_machines if m.machine_id != "M03"] best_machine = None best_end = None best_start = None for machine in candidate_machines: # 计算换模时间（首单或不同产品需换模） change = CHANGE_TIME if machine.last_product != task.product_id else 0 initial_start = machine.available_time + timedelta(hours=change) end_time = calculate_end_time(initial_start, task.processing_hours, holidays) adjusted_start = initial_start # 难生产产品约束：相邻机台不能同时生产难生产产品 if task.attr == 3: for adj_id in machine.adjacent: adj_machine = machine_map.get(adj_id) if not adj_machine: continue # 检查相邻机台当前任务（如果有） if adj_machine.schedule: last_task = adj_machine.schedule[-1] # 如果相邻机台正在生产难生产产品且时间冲突 if last_task["product_attr"] == 3 and not (end_time <= last_task["start"] or adjusted_start >= last_task["end"]): # 调整开始时间至冲突任务结束后 adjusted_start = max(adjusted_start, last_task["end"]) end_time = calculate_end_time(adjusted_start, task.processing_hours, holidays) # 更新最优机台（最早结束时间） if best_end is None or end_time < best_end: best_end = end_time best_start = adjusted_start best_machine = machine # 分配机台并更新状态 if best_machine: task.start_time = best_start task.end_time = best_end task.machine = best_machine.machine_id # 更新原始订单的延期和满意度 for original_order in task.original_orders: original_order.start_time = best_start original_order.end_time = best_end original_order.machine = best_machine.machine_id original_order.delay_days = max(0, (best_end.date() - original_order.delivery_date.date()).days) # 按文档公式计算满意度 original_order.satisfaction = max(0, 10.0 - (original_order.delay_days // 3) * 0.1) # 更新机台状态 best_machine.available_time = best_end best_machine.last_product = task.product_id best_machine.schedule.append({ "product": task.product_id, "start": best_start, "end": best_end, "product_attr": task.attr, "original_orders": [o.order_id for o in task.original_orders] }) # 收集所有原始订单用于统计 all_original_orders = [] for task in merged_tasks: all_original_orders.extend(task.original_orders) # 统计结果 delay_orders = [o for o in all_original_orders if o.delay_days > 0] max_delay = max([o.delay_days for o in delay_orders]) if delay_orders else 0 avg_delay = round(np.mean([o.delay_days for o in delay_orders]) if delay_orders else 0, 1) avg_satisfaction = round(np.mean([o.satisfaction for o in all_original_orders]), 2) min_satisfaction = round(min([o.satisfaction for o in all_original_orders]), 2) return all_original_orders, { "延期订单总数": len(delay_orders), "最长延期天数": max_delay, "平均延期天数": avg_delay, "订单平均满意度": avg_satisfaction, "订单最差满意度": min_satisfaction }
def export_problem3_results(results, output_path=r"C:\Users\彭婉\Desktop\2025-08\工作簿1.xlsx"):
“”“导出问题三结果到Excel（按指定格式）”“”
# 创建结果DataFrame
result_df = pd.DataFrame(results)
# 重命名列以符合要求 result_df.rename(columns={ "合单类别": "合单类别", "合单次数": "合单次数", "延期订单总数": "延期订单总数", "最长延期天数": "最长前期天数", # 注意这里按要求改为"最长前期天数" "平均延期天数": "平均延期天数", "订单平均满意度": "订单平均满意度", "订单最差满意度": "订单最差满意度" }, inplace=True) # 设置列顺序 column_order = [ "合单类别", "合单次数", "延期订单总数", "最长前期天数", "平均延期天数", "订单平均满意度", "订单最差满意度" ] result_df = result_df[column_order] # 导出到Excel with pd.ExcelWriter(output_path, engine="openpyxl") as writer: result_df.to_excel(writer, sheet_name="生产排程结果", index=False) print(f"结果已导出至：{os.path.abspath(output_path)}") return result_df
def main():
# 加载数据
orders_df, product_machines, product_attrs, original_machines, holidays = load_official_data()
# 检查数据是否加载成功 if orders_df.empty or not original_machines: print("数据加载失败，请检查文件路径和格式") return # 初始化原始订单对象 original_orders = [] for _, row in orders_df.iterrows(): try: order = Order( row["订单号PO"], row["产品号"], row["订单数量"], row["发货日期(DeliveryDate)"], row["Capacity（pcs/h）"], product_attrs.get(row["产品号"], 2) # 默认属性为2（无限制） ) original_orders.append(order) except Exception as e: print(f"订单初始化错误: {e} - 行数据: {row}") # 问题三：分别处理7天、15天、30天合单 merge_days_list = [7, 15, 30] problem3_results = [] for merge_days in merge_days_list: print(f"\n===== 处理 {merge_days} 天合单 =====") # 复制机台（避免状态污染） machines = [] for m in original_machines: # 正确复制机台初始时间 new_machine = Machine(m.machine_id, m.available_time) new_machine.adjacent = m.adjacent.copy() new_machine.last_product = m.last_product machines.append(new_machine) # 1. 合单 merged_tasks, merge_count = merge_orders(original_orders.copy(), merge_days) print(f"合单次数：{merge_count}") # 2. 排程 try: all_orders, stats = problem3_scheduling( merged_tasks, product_machines, product_attrs, machines, holidays ) except Exception as e: print(f"排程错误: {e}") continue # 3. 收集结果 problem3_results.append({ "合单类别": f"{merge_days}天合单", "合单次数": merge_count, "延期订单总数": stats["延期订单总数"], "最长延期天数": stats["最长延期天数"], "平均延期天数": stats["平均延期天数"], "订单平均满意度": stats["订单平均满意度"], "订单最差满意度": stats["订单最差满意度"] }) # 打印结果 print("\n===== 问题三最终结果 =====") result_df = export_problem3_results(problem3_results) # 控制台输出表格 print("\n生产排程结果表:") print(result_df.to_string(index=False))
if name == “main”:
main()
补充修改一下这个代码，这个代码本身会输出一个内容，不要修改，它会输出的，但是增加一些，它可以多输出的一些东西，就满足我上述的这个要求，最终结果呈现应该要有订单号PO，产品号，需要工时 (Requested Time--Hours)，生产计划开始时间（Plan Start Time），生产计划预计完成时间（Plan Finish Time），发货日期(DeliveryDate)，订单数量，Weight/pcs(g)，Capacity（pcs/h），最迟开始时间 (Late Start Time)，合单序号（Joined Order Number），前置任务生产计划序号（Forward Plan Number），是否延期，延期天数
本文介绍了一个简单的程序设计，用于计算一个给定日期加上特定天数后的日期。通过逐日累加并处理月份和闰年的边界条件，实现日期的准确递增。代码使用C语言编写，适用于快速解决日期计算问题。
扫一扫
1312
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
