matlab：function [schedule_table, best_metrics] = GA_schedule()
clc;
% 初始化py库
obj = py.importlib.import_module('bj');
py.importlib.reload(obj);
% 获取数据
orders_df = py.bj.fetch_orders();
production_lines_df = py.bj.fetch_production_lines();
% 转换函数
to_double = @(x) cellfun(@(v) double(py.float(v)), cell(py.list(x)), 'UniformOutput', false);
to_str = @(x) cellfun(@char, cell(py.list(x)), 'UniformOutput', false);
% 订单数据处理
order_ids = to_double(py.getattr(orders_df, 'ORDER_ID'));
order_dates = datetime(to_str(py.getattr(orders_df, 'ORDER_DATE')));
due_dates = datetime(to_str(py.getattr(orders_df, 'DUE_DATE')));
product_ids = to_double(py.getattr(orders_df, 'PRODUCT_ID'));
product_names = to_str(py.getattr(orders_df, 'PRODUCT_NAME'));
product_spec = to_str(py.getattr(orders_df, 'SPECIFICATION_INFO'));
order_quantities = to_double(py.getattr(orders_df, 'ORDER_QUANTITY'));
outstanding_quantities = to_double(py.getattr(orders_df, 'OUTSTANDING_QUANTITY'));
% 产线数据处理（LINE_ID 改为字符串）
line_names = to_str(py.getattr(production_lines_df, 'LINE_NAME'));
line_ids = to_str(py.getattr(production_lines_df, 'LINE_ID'));
line_rates = to_double(py.getattr(production_lines_df, 'LINE_RATE'));
yield_rates = to_double(py.getattr(production_lines_df, 'YIELD_RATE'));
status = to_str(py.getattr(production_lines_df, 'STATUS'));
production_ids = to_double(py.getattr(production_lines_df, 'PRODUCT_ID'));
% 构建订单结构体
num_orders = length(order_ids);
orders = struct([]);
for i = 1:num_orders
orders(i).OrderID = order_ids{i};
orders(i).OrderDate = order_dates(i);
orders(i).DueDate = due_dates(i);
orders(i).ProductID = product_ids{i};
orders(i).ProductName = product_names{i};
orders(i).Specification = product_spec{i};
orders(i).OrderQuantity = order_quantities{i};
orders(i).OutstandingQuantity = outstanding_quantities{i};
end
% 构建产线结构体
num_lines = length(line_ids);
productions = struct([]);
for j = 1:num_lines
productions(j).LineID = line_ids{j};
productions(j).LineName = line_names{j};
productions(j).LineRate = line_rates{j};
productions(j).YieldRate = yield_rates{j} / 100;
productions(j).Status = status{j};
productions(j).ProductID = production_ids{j};
end
% GA参数
ga_params.pop_size = 60;
ga_params.max_gen = 200;
ga_params.crossover_rate = 0.8;
ga_params.mutation_rate = 0.25;
ga_params.tournament_k = 3;
% 基准时间：设置为当前时间
baseline_start = datetime('now'); % 获取当前系统时间
line_id_to_index = containers.Map();
for j = 1:num_lines
line_id_to_index(productions(j).LineID) = j;
end
% 兼容产线：仍用索引存储
compatible_lines = cell(num_orders, 1);
for i = 1:num_orders
pid = orders(i).ProductID;
idxs = [];
for j = 1:num_lines
if isequal(productions(j).ProductID, pid) && strcmpi(productions(j).Status, '正常')
idxs(end+1) = j;
end
end
compatible_lines{i} = idxs;
end
% 启动并行池
if isempty(gcp('nocreate'))
parpool('local');
end
% 初始化种群 - 使用并行评估
population(ga_params.pop_size) = struct();
% 预分配序列和分配
seqs = cell(ga_params.pop_size, 1);
assigns = cell(ga_params.pop_size, 1);
% 生成种群个体
for p = 1:ga_params.pop_size
seqs{p} = randperm(num_orders);
assign = cell(num_orders, 1);
for i = 1:num_orders
cl = compatible_lines{i};
if ~isempty(cl)
selected_idx = cl(randi(length(cl)));
assign{i} = productions(selected_idx).LineID; % 存字符串
else
assign{i} = '';
end
end
assigns{p} = assign;
end
% 并行评估种群适应度
fitness_values = zeros(ga_params.pop_size, 1);
parfor p = 1:ga_params.pop_size
[~, metrics] = simulate_schedule_standalone(orders, productions, seqs{p}, assigns{p}, ...
compatible_lines, baseline_start, line_id_to_index);
finish_hours = hours(metrics.latest_finish - baseline_start);
fitness_values(p) = metrics.total_late_hours + 0.0001*finish_hours + metrics.penalty_unassigned;
end
% 构建种群结构体
for p = 1:ga_params.pop_size
population(p).seq = seqs{p};
population(p).assign = assigns{p};
population(p).fitness = fitness_values(p);
end
% 主循环 - 并行化新一代评估
best_history = nan(ga_params.max_gen, 1);
for gen = 1:ga_params.max_gen
newpop = population;
[~, idxs_pop] = sort([population.fitness]);
newpop(1) = population(idxs_pop(1));
% 预分配新一代个体
new_seqs = cell(ga_params.pop_size, 1);
new_assigns = cell(ga_params.pop_size, 1);
new_seqs{1} = newpop(1).seq;
new_assigns{1} = newpop(1).assign;
% 生成新一代个体（串行）
for slot = 2:ga_params.pop_size
p1 = tournament_select(population, ga_params.tournament_k);
p2 = tournament_select(population, ga_params.tournament_k);
p1_assign = p1.assign;
p2_assign = p2.assign;
child_seq = p1.seq;
child_assign = p1_assign;
if rand < ga_params.crossover_rate
child_seq = order_crossover(p1.seq, p2.seq);
child_assign = assign_crossover(p1_assign, p2_assign);
end
if rand < ga_params.mutation_rate
child_seq = swap_mutation(child_seq);
end
if rand < ga_params.mutation_rate
child_assign = assignment_mutation(child_assign, compatible_lines, productions, line_id_to_index);
end
new_seqs{slot} = child_seq;
new_assigns{slot} = child_assign;
end
% 并行评估新一代适应度
new_fitness = zeros(ga_params.pop_size, 1);
parfor slot = 1:ga_params.pop_size
if slot == 1
% 精英个体已经评估过
new_fitness(slot) = newpop(1).fitness;
else
[~, metrics] = simulate_schedule_standalone(orders, productions, new_seqs{slot}, new_assigns{slot}, ...
compatible_lines, baseline_start, line_id_to_index);
finish_hours = hours(metrics.latest_finish - baseline_start);
new_fitness(slot) = metrics.total_late_hours + 0.0001*finish_hours + metrics.penalty_unassigned;
end
end
% 更新新一代种群
for slot = 1:ga_params.pop_size
newpop(slot).seq = new_seqs{slot};
newpop(slot).assign = new_assigns{slot};
newpop(slot).fitness = new_fitness(slot);
end
population = newpop;
best_history(gen) = min([population.fitness]);
% 显示进度
if mod(gen, 10) == 0
fprintf('代数 %d/%d, 最佳适应度: %.2f\n', gen, ga_params.max_gen, best_history(gen));
end
end
% 提取最优解
[~, idxs] = sort([population.fitness]);
best_ind = population(idxs(1));
[best_schedule, best_metrics] = simulate_schedule_standalone(orders, productions, best_ind.seq, best_ind.assign, ...
compatible_lines, baseline_start, line_id_to_index);
% 转换为表格 - 修复数据类型问题
n = length(best_schedule);
Seq = zeros(n,1);
OrderID = zeros(n,1);
OrderDate = NaT(n,1);
DueDate = NaT(n,1);
ProductID = zeros(n,1);
ProductName = strings(n,1);
Specification = strings(n,1);
OrderQuantity = zeros(n,1);
OutstandingQuantity = zeros(n,1);
AssignedLineID = strings(n,1);
AssignedLineName = strings(n,1);
StartTime = NaT(n,1);
EndTime = NaT(n,1);
ProcDuration = duration(zeros(n,1),0,0);
for k = 1:n
s = best_schedule(k);
Seq(k) = s.Seq;
OrderID(k) = s.OrderID;
OrderDate(k) = s.OrderDate;
DueDate(k) = s.DueDate;
ProductID(k) = s.ProductID;
ProductName(k) = string(s.ProductName);
Specification(k) = string(s.Specification);
OrderQuantity(k) = s.OrderQuantity;
OutstandingQuantity(k) = s.OutstandingQuantity;
% 确保产线ID和名称都是字符串类型
if ischar(s.AssignedLineID)
AssignedLineID(k) = string(s.AssignedLineID);
else
AssignedLineID(k) = s.AssignedLineID;
end
if ischar(s.AssignedLineName)
AssignedLineName(k) = string(s.AssignedLineName);
else
AssignedLineName(k) = s.AssignedLineName;
end
StartTime(k) = s.StartTime;
EndTime(k) = s.EndTime;
ProcDuration(k) = s.ProcDuration;
end
% 分离有效与无效调度
valid_idx = ~isnat(StartTime);
invalid_idx = isnat(StartTime);
valid_indices = find(valid_idx);
invalid_indices = find(invalid_idx);
[~, sort_idx_valid] = sort(StartTime(valid_idx));
[~, sort_idx_invalid] = sort(OrderDate(invalid_idx));
final_idx = [valid_indices(sort_idx_valid); invalid_indices(sort_idx_invalid)];
% 重排
Seq = (1:n)';
OrderID = OrderID(final_idx);
OrderDate = OrderDate(final_idx);
DueDate = DueDate(final_idx);
ProductID = ProductID(final_idx);
ProductName = ProductName(final_idx);
Specification = Specification(final_idx);
OrderQuantity = OrderQuantity(final_idx);
OutstandingQuantity = OutstandingQuantity(final_idx);
AssignedLineID = AssignedLineID(final_idx);
AssignedLineName = AssignedLineName(final_idx);
StartTime = StartTime(final_idx);
EndTime = EndTime(final_idx);
ProcDuration = ProcDuration(final_idx);
% 输出表格
schedule_table = table(Seq, OrderID, OrderDate, DueDate, ProductID, ProductName, ...
Specification, OrderQuantity, OutstandingQuantity, AssignedLineID, ...
AssignedLineName, StartTime, EndTime, ProcDuration);
% 确保所有文本列都是统一的字符串类型
schedule_table.AssignedLineID = string(schedule_table.AssignedLineID);
schedule_table.AssignedLineName = string(schedule_table.AssignedLineName);
schedule_table.ProductName = string(schedule_table.ProductName);
schedule_table.Specification = string(schedule_table.Specification);
% 设置时间格式
schedule_table.StartTime.Format = 'yyyy-MM-dd HH:mm:ss';
schedule_table.EndTime.Format = 'yyyy-MM-dd HH:mm:ss';
schedule_table.OrderDate.Format = 'yyyy-MM-dd HH:mm:ss';
schedule_table.DueDate.Format = 'yyyy-MM-dd HH:mm:ss';
% 导出 Excel - 修复导出问题
try
% === 1. 获取脚本所在目录 ===
this_file = mfilename('fullpath');
if isempty(this_file)
error('请以 .m 文件方式运行此程序，不能在实时脚本或函数中直接运行');
end
script_dir = fileparts(this_file);
output_dir = fullfile(script_dir, 'downloads');
output_path = fullfile(output_dir, '排程结果.xlsx');
% === 2. 确保 downloads 文件夹存在 ===
if ~exist(output_dir, 'dir')
try
mkdir(output_dir);
disp(['创建目录成功: ' output_dir]);
catch ME
error('创建 downloads 失败，请检查权限：%s', ME.message);
end
end
% === 3. 如果 Excel 正在打开旧文件，则 writetable 会失败 → 先删掉它 ===
if isfile(output_path)
try
delete(output_path); % 主动删除，释放锁
disp('已删除旧文件');
catch ME
warning('文件可能被 Excel 占用，请关闭后再试！');
error(['无法删除 ' output_path '，错误: ' ME.message]);
end
end
% === 4. 导出表格（双保险方案）===
% --- 方案一：尝试标准 writetable ---
try
writetable(schedule_table, output_path, 'WriteMode', 'overwrite');
fprintf('成功导出到:\n%s\n', output_path);
catch
% --- 方案二：备用 writecell（兼容性最强）---
try
% 处理列名中文
temp_table = schedule_table;
headers = {'序号','订单号','订单日期','订单交期','产品编号','产品名称','产品规格',...
'订单数目','未交数目','分配产线编号','分配产线名称','预定开始时间',...
'预定结束时间','预期消耗时长'};
nCols = min(width(temp_table), length(headers));
temp_table.Properties.VariableNames(1:nCols) = headers(1:nCols);
% 转成 cell 并加表头
data_cell = table2cell(temp_table);
full_data = [temp_table.Properties.VariableNames; data_cell];
writecell(full_data, output_path);
fprintf('备用方案：成功导出到:\n%s\n', output_path);
catch ME2
rethrow(ME2); % 最终失败
end
end
% === 5. 打开文件夹提示用户 ===
web(fullfile('file://', output_dir), '-browser');
catch ME
% 统一报错
fprintf('导出失败！详细信息如下：\n');
fprintf('错误: %s\n', ME.message);
fprintf('路径: %s\n', output_path);
end
end
% 独立的局部函数（放在主函数外部）
function [schedule, metrics] = simulate_schedule_standalone(orders, productions, seq, assign, ...
compatible_lines, baseline_start, line_id_to_index)
% 加工时间计算函数
calc_proc_duration = @(qty, rate, yield) hours(qty ./ (rate .* yield));
num_orders_local = length(seq);
num_productions = length(productions);
line_available = repmat(baseline_start, num_productions, 1);
schedule = repmat(struct(), num_orders_local, 1);
total_late_hours = 0;
latest_finish = baseline_start;
penalty_unassigned = 0;
for k = 1:num_orders_local
i = seq(k);
schedule(k).Seq = k;
schedule(k).OrderID = orders(i).OrderID;
schedule(k).OrderDate = orders(i).OrderDate;
schedule(k).DueDate = orders(i).DueDate;
schedule(k).ProductID = orders(i).ProductID;
schedule(k).ProductName = orders(i).ProductName;
schedule(k).Specification = orders(i).Specification;
schedule(k).OrderQuantity = orders(i).OrderQuantity;
schedule(k).OutstandingQuantity = orders(i).OutstandingQuantity;
assigned_line_id = assign{i};
if isempty(assigned_line_id) || ~isKey(line_id_to_index, assigned_line_id)
% 统一使用字符串类型
schedule(k).AssignedLineID = "待确认";
schedule(k).AssignedLineName = "待确认";
schedule(k).StartTime = NaT;
schedule(k).EndTime = NaT;
schedule(k).ProcDuration = duration(0,0,0);
penalty_unassigned = penalty_unassigned + 1e6;
continue;
end
j = line_id_to_index(assigned_line_id); % 转为索引
% 检查是否兼容
cl = compatible_lines{i};
if ~ismember(j, cl)
% 统一使用字符串类型
schedule(k).AssignedLineID = string(assigned_line_id);
schedule(k).AssignedLineName = string(productions(j).LineName);
schedule(k).StartTime = NaT;
schedule(k).EndTime = NaT;
schedule(k).ProcDuration = duration(0,0,0);
penalty_unassigned = penalty_unassigned + 1e5;
continue;
end
qty = double(orders(i).OutstandingQuantity);
rate = double(productions(j).LineRate);
yld = double(productions(j).YieldRate);
if rate <= 0 || yld <= 0
proc_dur = hours(1e6);
else
proc_dur = calc_proc_duration(qty, rate, yld);
end
est_start = max(line_available(j), orders(i).OrderDate);
est_end = est_start + proc_dur;
line_available(j) = est_end;
% 统一使用字符串类型
schedule(k).AssignedLineID = string(assigned_line_id);
schedule(k).AssignedLineName = string(productions(j).LineName);
schedule(k).StartTime = est_start;
schedule(k).EndTime = est_end;
schedule(k).ProcDuration = proc_dur;
late_h = max(0, hours(est_end - orders(i).DueDate));
total_late_hours = total_late_hours + late_h;
if est_end > latest_finish
latest_finish = est_end;
end
end
metrics.total_late_hours = total_late_hours;
metrics.latest_finish = latest_finish;
metrics.penalty_unassigned = penalty_unassigned;
end
% 辅助函数（放在主函数外部）
function p = tournament_select(pop, k)
n = numel(pop);
idx = randi(n, [k,1]);
[~, bestidx] = min([pop(idx).fitness]);
p = pop(idx(bestidx));
end
function child_seq = order_crossover(a, b)
n = length(a);
p1 = sort(randi(n, [1,2]));
seg = a(p1(1):p1(2));
rest = b(~ismember(b, seg));
child_seq = [rest(1:p1(1)-1), seg, rest(p1(1):end)];
end
function child_assign = assign_crossover(a, b)
n = length(a);
cp = randi(n-1);
child_assign = [a(1:cp); b(cp+1:end)];
end
function s = swap_mutation(seq)
n = length(seq);
i = randi(n); j = randi(n);
tmp = seq(i); seq(i) = seq(j); seq(j) = tmp;
s = seq;
end
function asg = assignment_mutation(asg, compatible_lines, productions, line_id_to_index)
n = length(asg);
i = randi(n);
cl = compatible_lines{i};
if ~isempty(cl)
selected_idx = cl(randi(length(cl)));
asg{i} = productions(selected_idx).LineID;
else
asg{i} = '';
end
end bj.py:import pymysql
import pandas as pd
import matlab.engine
import os
import threading
_eng_lock = threading.Lock()
_eng = None
def getConn():
return pymysql.connect(
host='localhost',
user='baijing',
password='12345',
database='baijing',
charset='utf8mb4',
autocommit=False
)
def insertHtmlData(orderNum, orderDate, orderDeliveryDate, productName, productNum,
orderOutstandingQuantity, specificationInfo, remarks, orderQuantity):
"""插入订单数据"""
sql = """
INSERT INTO orderinfo
(orderNumber, orderDate, orderDeliveryDate, productName, productNumber,
outstandingOrderQuantity, specificationInfo, Remarks, orderQuantity)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
conn = getConn()
try:
with conn.cursor() as cursor:
cursor.execute(sql, (
orderNum, orderDate, orderDeliveryDate, productName, productNum,
int(orderOutstandingQuantity), specificationInfo, remarks or None, int(orderQuantity)
))
conn.commit()
except Exception as e:
conn.rollback()
print(f"[订单插入失败] {e}")
raise
finally:
conn.close()
def insertHtmlData1(productionLineName, productionLineNumb, productionCapacity, yieldRate,
opStatus, productNum, productName, productSpecification):
"""插入产线数据"""
sql = """
INSERT INTO productionlineinfo
(productionLineName, productionLineNumber, productionCapacity, yieldRate,
opStatus, productNum, productName, productSpecification)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
"""
conn = getConn()
try:
with conn.cursor() as cursor:
cursor.execute(sql, (
productionLineName,
productionLineNumb,
float(productionCapacity),
float(yieldRate),
opStatus,
productNum,
productName,
productSpecification or None
))
conn.commit()
except Exception as e:
conn.rollback()
print(f"[产线插入失败] {e}")
raise
finally:
conn.close()
# 批量导入
def clean_nan(val):
"""安全转换 NaN / None / 空字符串"""
if pd.isna(val):
return None
if isinstance(val, float) and val == float('inf'):
return None
return str(int(val)) if isinstance(val, float) and val.is_integer() else str(val)
def insert_batch_data(df):
"""批量导入订单 Excel"""
required_cols = ['orderNumber', 'orderDate', 'orderDeliveryDate', 'productName',
'productNumber', 'outstandingOrderQuantity', 'specificationInfo',
'Remarks', 'orderQuantity']
if len(df.columns) < len(required_cols):
raise ValueError(f"列数不足，期望 {len(required_cols)} 列")
df = df.iloc[:, :9]
df.columns = required_cols
conn = getConn()
try:
with conn.cursor() as cursor:
sql = """
INSERT INTO orderinfo
(orderNumber, orderDate, orderDeliveryDate, productName, productNumber,
outstandingOrderQuantity, specificationInfo, Remarks, orderQuantity)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
for idx, row in df.iterrows():
try:
cleaned = [clean_nan(row[col]) for col in required_cols]
cursor.execute(sql, tuple(cleaned))
except Exception as e:
print(f"跳过第 {idx + 1} 行: {e}")
continue
conn.commit()
except Exception as e:
conn.rollback()
raise
finally:
conn.close()
def insert_batch_data1(df):
"""批量导入产线 Excel"""
required_cols = ['productionLineName', 'productionLineNumber', 'productionCapacity',
'yieldRate', 'opStatus', 'productNum', 'productName', 'productSpecification']
if len(df.columns) < len(required_cols):
raise ValueError(f"产线表列数不足，期望 {len(required_cols)} 列")
df = df.iloc[:, :8]
df.columns = required_cols
conn = getConn()
try:
with conn.cursor() as cursor:
sql = """
INSERT INTO productionlineinfo
(productionLineName, productionLineNumber, productionCapacity, yieldRate,
opStatus, productNum, productName, productSpecification)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
"""
for idx, row in df.iterrows():
try:
cleaned = [clean_nan(row[col]) for col in required_cols]
cursor.execute(sql, tuple(cleaned))
except Exception as e:
print(f"跳过第 {idx + 1} 行: {e}")
continue
conn.commit()
except Exception as e:
conn.rollback()
raise
finally:
conn.close()
# 清空表
def clear_table(table_name):
allowed_tables = ["orderinfo", "productionlineinfo"]
if table_name not in allowed_tables:
raise ValueError("非法表名")
conn = getConn()
try:
with conn.cursor() as cursor:
cursor.execute(f"TRUNCATE TABLE `{table_name}`")
conn.commit()
finally:
conn.close()
# MATLAB 调度
def get_matlab_engine():
global _eng
with _eng_lock:
if _eng is None:
_eng = matlab.engine.start_matlab()
current_dir = os.path.dirname(os.path.abspath(__file__))
_eng.addpath(current_dir, nargout=0)
return _eng
def run_matlab():
try:
eng = get_matlab_engine()
current_dir = os.path.dirname(os.path.abspath(__file__))
output_folder = os.path.join(current_dir, "downloads")
os.makedirs(output_folder, exist_ok=True)
result = eng.GA_schedule(nargout=0)
excel_path = os.path.join(output_folder, "排程结果.xlsx")
if not os.path.exists(excel_path):
raise FileNotFoundError("排程结果文件未生成")
return excel_path
except Exception as e:
print(f"MATLAB 运行失败: {e}")
raise
# 订单数据库获取
def fetch_orders():
conn = getConn()
cursor = conn.cursor()
query = "SELECT * FROM orderinfo"
cursor.execute(query)
result = cursor.fetchall()
orders_df = pd.DataFrame(result, columns=["TABLELINE_ID", "ORDER_ID", "ORDER_DATE", "DUE_DATE", "PRODUCT_NAME", "PRODUCT_ID", "OUTSTANDING_QUANTITY", "SPECIFICATION_INFO", "REMARKS", "ORDER_QUANTITY",])
cursor.close()
conn.close()
return orders_df
# 从 MySQL 中获取生产线数据
def fetch_production_lines():
# 数据库连接
conn = getConn()
# 创建指向对象
cursor = conn.cursor()
# 读取生产线数据
query = "SELECT * FROM productionlineinfo"
cursor.execute(query)
result = cursor.fetchall()
# 将结果转换为 DataFrame（使用 pandas 方便处理）
production_lines_df = pd.DataFrame(result, columns=["TABLELINE_ID", "LINE_NAME", "LINE_ID", "LINE_RATE", "YIELD_RATE", "STATUS", "PRODUCT_ID", "PRODUCT_NAME", "PRODUCT_SPECIFICATION"])
# 关闭连接
cursor.close()
conn.close()
return production_lines_df在matlab报错：错误使用 generic>__getattr__ (第 6204 行)
Python 错误 AttributeError: 'DataFrame' object has no attribute 'ORDER_ID'
>>
本文介绍如何使用Python Pandas库调整DataFrame中列的顺序，并展示了一个具体示例，涉及date、extprice等字段。
扫一扫
5407
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
