import pandas as pd
import numpy as np
from datetime import datetime, timedelta, time
import os
from typing import List, Dict, Optional
# ====================== 基础参数 ======================
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, weight, 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.weight = weight # 产品重量(g)
self.processing_hours = quantity / capacity # 生产工时(h)
self.start_time = None # 开始时间
self.end_time = None # 结束时间
self.machine = None # 分配机台
self.delay_days = 0 # 延期天数
self.attr = attr # 产品属性(1:易搭配,3:难生产)
self.merged_id = None # 合单序号
self.plan_number = None # 计划序号
self.forward_plan = None # 前置任务计划序号
class MergedOrder:
def __init__(self, product_id, total_quantity, delivery_dates, capacity, weight, attr, original_orders, merged_id):
self.product_id = product_id # 产品号
self.total_quantity = total_quantity # 合并后总数量
self.delivery_dates = delivery_dates # 原始订单发货日期列表
self.capacity = capacity # 产能
self.weight = weight # 产品重量
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 # 分配机台
self.merged_id = merged_id # 合单序号
self.plan_number = None # 计划序号
class Machine:
def __init__(self, machine_id, initial_time=None):
self.machine_id = machine_id # 机台号
self.available_time = initial_time or datetime(2025, 3, 1) # 初始可用时间
self.last_product = None # 上一生产产品
self.adjacent = [] # 相邻机台列表
self.schedule = [] # 排程计划(任务列表)
self.last_plan_number = None # 上一任务计划序号
# ====================== 数据加载 ======================
def load_official_data(data_dir: str = "."):
"""
加载附件数据
需修改data_dir为附件所在目录(例如:"C:/Users/你的用户名/Desktop/附件")
"""
try:
# 读取附件1
attachment1 = pd.ExcelFile(os.path.join(data_dir, "附件1(1).xlsx"))
orders_df = attachment1.parse(
sheet_name="订单表",
parse_dates=["发货日期(DeliveryDate)"],
usecols=["订单号", "产品号", "订单数量", "发货日期(DeliveryDate)", "Weight/pcs(g)"]
)
machine_initial_df = attachment1.parse(
sheet_name="机台初始工作状态表",
parse_dates=["生产开始时间"]
)
holidays_df = attachment1.parse(sheet_name="放假日期表")
holidays = {pd.to_datetime(row["放假日期"]).date() for _, row in holidays_df.iterrows()}
# 读取附件2
attachment2 = pd.ExcelFile(os.path.join(data_dir, "附件2(1).xlsx"))
product_capacity_df = attachment2.parse(sheet_name="产品工时计算参数表")
product_machine_df = attachment2.parse(sheet_name="产品机台生产关系表")
machine_relation_df = attachment2.parse(sheet_name="机台关系表")
product_attr_df = attachment2.parse(sheet_name="产品属性表")
# 构建数据映射
product_capacity = {row["产品号"]: row["Capacity(pcs/h)"] for _, row in product_capacity_df.iterrows()}
product_weight = {row["产品号"]: row["Weight/pcs(g)"] for _, row in product_capacity_df.iterrows()}
product_machines = {}
for _, row in product_machine_df.iterrows():
valid_machines = [col for col in product_machine_df.columns if col != "产品号" and row[col] == 1]
product_machines[row["产品号"]] = valid_machines
machine_adjacent = {}
for _, row in machine_relation_df.iterrows():
adjacent_machines = [col for col in machine_relation_df.columns if col != "机台号" and row[col] == 1]
machine_adjacent[row["机台号"]] = adjacent_machines
product_attrs = {row["产品号"]: row["属性"] for _, row in product_attr_df.iterrows()}
# 补充订单表产能和重量
orders_df["Capacity(pcs/h)"] = orders_df["产品号"].map(product_capacity)
orders_df["Weight/pcs(g)"] = orders_df["产品号"].map(product_weight)
# 初始化机台
machine_initial_times = {row["机台号"]: row["生产开始时间"] for _, row in machine_initial_df.iterrows()}
machines = []
for mid in machine_initial_times.keys():
machine = Machine(mid, machine_initial_times[mid])
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}")
raise
# ====================== 合单逻辑 ======================
def merge_orders(orders: List[Order], merge_days: int) -> tuple[List[MergedOrder], int]:
merged_tasks = []
merge_count = 0
merged_id_counter = 1
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)
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,
weight=current_merge[0].weight,
attr=current_merge[0].attr,
original_orders=current_merge,
merged_id=merged_id_counter
))
merge_count += 1 if len(current_merge) > 1 else 0
merged_id_counter += 1
current_merge = [group_orders[i]]
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,
weight=current_merge[0].weight,
attr=current_merge[0].attr,
original_orders=current_merge,
merged_id=merged_id_counter
))
if len(current_merge) > 1:
merge_count += 1
merged_id_counter += 1
return merged_tasks, merge_count
# ====================== 排程逻辑 ======================
def calculate_end_time(start_time: datetime, processing_hours: float, holidays: set) -> datetime:
current = start_time
remaining = processing_hours
while remaining > 0:
if current.date() in holidays:
current = datetime.combine(current.date() + timedelta(days=1), time(0, 0))
continue
day_start = datetime.combine(current.date(), time(0, 0))
intervals = []
prev_end = day_start
for rest_start, rest_end in REST_PERIODS:
rest_start_time = day_start + timedelta(hours=rest_start)
rest_end_time = day_start + timedelta(hours=rest_end)
if prev_end < rest_start_time:
intervals.append((prev_end, rest_start_time))
prev_end = rest_end_time
if prev_end < day_start + timedelta(hours=24):
intervals.append((prev_end, day_start + timedelta(hours=24)))
for (s, e) in intervals:
if current < s:
current = s
if current >= e:
continue
available = (e - current).total_seconds() / 3600
use = min(remaining, available)
current += timedelta(hours=use)
remaining -= use
if remaining <= 0:
return current
current = datetime.combine(current.date() + timedelta(days=1), time(0, 0))
return current
def problem3_scheduling(merged_tasks: List[MergedOrder], product_machines: Dict, machines: List[Machine], holidays: set) -> tuple[List[Order], List[Dict]]:
merged_tasks.sort(key=lambda x: (x.attr, min(x.delivery_dates)))
machine_map = {m.machine_id: m for m in machines}
plan_number = 1
detailed_results = []
for task in merged_tasks:
candidate_machines = [m for m in machines if m.machine_id in product_machines.get(task.product_id, [])]
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 adj_machine and 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
task.plan_number = plan_number
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.merged_id = task.merged_id
original_order.plan_number = plan_number
original_order.forward_plan = best_machine.last_plan_number
original_order.delay_days = max(0, (best_end.date() - original_order.delivery_date.date()).days)
detailed_results.append({
"计划序号(Plan Number)": plan_number,
"生产计划安排机台号": best_machine.machine_id,
"订单号PO": original_order.order_id,
"产品号": original_order.product_id,
"需要工时 (Requested Time--Hours)": round(original_order.processing_hours, 2),
"生产计划开始时间": original_order.start_time.strftime("%Y-%m-%d %H:%M:%S"),
"生产计划预计完成时间": original_order.end_time.strftime("%Y-%m-%d %H:%M:%S"),
"发货日期(DeliveryDate)": original_order.delivery_date.strftime("%Y-%m-%d"),
"订单数量": original_order.quantity,
"Weight/pcs(g)": original_order.weight,
"Capacity(pcs/h)": original_order.capacity,
"最迟开始时间 (Late Start Time)": (original_order.delivery_date - timedelta(
hours=original_order.processing_hours
)).strftime("%Y-%m-%d %H:%M:%S"),
"合单序号(Joined Order Number)": task.merged_id,
"前置任务生产计划序号": original_order.forward_plan or "",
"是否延期": "是" if original_order.delay_days > 0 else "否",
"延期天数": original_order.delay_days
})
best_machine.available_time = best_end
best_machine.last_product = task.product_id
best_machine.last_plan_number = plan_number
best_machine.schedule.append({
"product": task.product_id,
"start": best_start,
"end": best_end,
"product_attr": task.attr
})
plan_number += 1
return detailed_results
# ====================== 导出结果 ======================
def export_results(detailed_results: List[Dict], output_path: str = "附件5_生产排程计划表.xlsx"):
"""
导出完整排程结果到Excel
需修改output_path为期望的导出路径(例如:"C:/Users/你的用户名/Desktop/附件5_结果.xlsx")
"""
df = pd.DataFrame(detailed_results)
column_order = [
"计划序号(Plan Number)", "生产计划安排机台号", "订单号PO", "产品号",
"需要工时 (Requested Time--Hours)", "生产计划开始时间",
"生产计划预计完成时间", "发货日期(DeliveryDate)", "订单数量",
"Weight/pcs(g)", "Capacity(pcs/h)", "最迟开始时间 (Late Start Time)",
"合单序号(Joined Order Number)", "前置任务生产计划序号",
"是否延期", "延期天数"
]
df = df[column_order]
df.to_excel(output_path, index=False)
print(f"结果已导出至:{os.path.abspath(output_path)}")
# ====================== 主函数 ======================
def main():
# ---------------------- 需要您修改的部分 ----------------------
# 1. 附件所在目录:将"."改为附件实际存放路径(例如:"C:/Users/你的用户名/Desktop/附件")
DATA_DIR = "."
# 2. 导出文件路径:将"附件5_生产排程计划表.xlsx"改为期望的导出路径(例如:"C:/Users/你的用户名/Desktop/结果.xlsx")
OUTPUT_PATH = "附件5_生产排程计划表.xlsx"
# 3. 合单天数:可根据需要修改(当前为7天,可改为15或30天)
MERGE_DAYS = 7
# -------------------------------------------------------------
try:
orders_df, product_machines, product_attrs, machines, holidays = load_official_data(DATA_DIR)
original_orders = []
for _, row in orders_df.iterrows():
try:
order = Order(
order_id=row["订单号"],
product_id=row["产品号"],
quantity=row["订单数量"],
delivery_date=row["发货日期(DeliveryDate)"],
capacity=row["Capacity(pcs/h)"],
weight=row["Weight/pcs(g)"],
attr=product_attrs.get(row["产品号"], 2)
)
original_orders.append(order)
except Exception as e:
print(f"订单初始化失败: {e} - 行数据: {row}")
if not original_orders:
raise ValueError("没有有效订单,请检查数据格式")
merged_tasks, merge_count = merge_orders(original_orders, MERGE_DAYS)
print(f"合单完成,共合并 {merge_count} 次")
detailed_results = problem3_scheduling(merged_tasks, product_machines, machines, holidays)
print(f"排程完成,共生成 {len(detailed_results)} 条计划")
export_results(detailed_results, OUTPUT_PATH)
except Exception as e:
print(f"程序执行失败: {e}")
if __name__ == "__main__":
main()
最终结果呈现应该要有计划序号生产计划安排机台号,订单号,产品号需要工时生产,计划开始时间,生产计划预计完成时间,发货日期,订单数量,重量,产能,最迟开始时间,合单序号,前置任务生产计划序号,是否延期,延期天数