//给全局变量tableFlag赋值：
//tableFlag=0时，续排登记缺件表1，PAB登记daefault._store_1,否则登记缺件表，PAB登记daefault._store_
private bool GetTableFlag(PlanItem item)
{
int Count = 0;
sql = $"select count(*) from switch where gongZLH='{item.gongZLH}' and qiH='{item.qiH}' and xuH={item.xuH}";
cmd.CommandText = sql;
try
{
Count = Convert.ToInt32(cmd.ExecuteScalar());
}
catch (Exception e)
{
log.Error(string.Format($"select switch error!\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
if (Count == 0)
{
tableFlag = 0;
sql = $"insert into switch (gongZLH, qiH, xuH, ciSh) values ('{item.gongZLH}','{item.qiH}' , {item.xuH}, 0)";
cmd.CommandText = sql;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.Error(string.Format($"insert table switch error!\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
return(true);
}
sql = $"select ciSh from switch where gongZLH='{item.gongZLH}' and qiH='{item.qiH}' and xuH={item.xuH}";
cmd.CommandText = sql;
try
{
Count = Convert.ToInt32(cmd.ExecuteScalar());
//tableFlag=0时，续排登记缺件表1，PAB登记daefault._store_1,否则登记缺件表，PAB登记daefault._store_
tableFlag = Count % 2;
}
catch (Exception e)
{
log.Error(string.Format($"select switch error!\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
return(true);
}
/// <summary>
/// 计算整个物料树各物料节点的实际需求数量，登记到stocks.shuL
/// </summary>
private void Calculate(int seqno, PlanItem item)
{
decimal multiple = 0.00m; //当前物料不足的数量，也是其子物料需要乘以的倍数
decimal surplus = 0.00m; //当前剩余物料的数量
decimal shuL = item.shuL;
var stock = stocks.Find(m => m.wuLBM == item.wuLBM);
stock.flag = seqno;
surplus = stock.PAB - stock.shuL; //当前剩余的数量
stock.shuL += shuL; //累计实际需要的数量
//当前剩余大于本次所需，不需进一步处理
if (surplus - shuL > -0.00005m)
{
return;
}
//当前没有剩余
if (surplus < 0.00005m)
{
multiple = shuL;
}//当前有剩余，但不足本次所需
else if (surplus - shuL < 0.00005m)
{
multiple = shuL - surplus;
}
//从BOM树种找到子物料，再逐个遍历，累计更新stacks相应物料的需求数量shuL
foreach (Bom bomnode in bomTree.Where(m => m.materielIdentfication == item.wuLBM))
{
PlanItem planItem = new PlanItem
{
wuLBM = bomnode.CmId, //物料编码
shuL = bomnode.CNum * multiple, //数量
gongZLH = item.gongZLH, //工作令号
qiH = item.qiH, //期号
xuH = item.xuH, //序号
jiaoHQ = item.jiaoHQ //交货期
};
Calculate(seqno, planItem);
}
}
private bool HandlePlanFlag(PlanItem item)
{
if (option == 1 || option == 3)//重排，预重排
{
sql = $"delete from switch where gongZLH='{item.gongZLH}' and qiH='{item.qiH}' and xuH={item.xuH}; " +
$"insert into switch (gongZLH, qiH, xuH, ciSh) values ('{item.gongZLH}', '{item.qiH}', {item.xuH}, 0);";
}
else
{
sql = $"update switch set ciSh = ciSh + 1 where gongZLH='{item.gongZLH}' and qiH='{item.qiH}' and xuH={item.xuH};";
}
cmd.CommandText = sql;
try
{
cmd.ExecuteNonQuery();
}
catch
{
log.Error(string.Format($"update switch Error!\nsql[{sql}]\n"));
return(false);
}
return(true);
}
private bool ProcessOrder(int i, PlanItem item)
{
decimal POH = 0.00m; //预计在库量
decimal NR = 0.00m; //净需求数量
decimal PORC = 0.00m; //缺件数量
foreach (Stock stock in stocks.Where(m => m.flag == i))
{
log.Error("process order 0");
//预计在库量 POH
POH = stock.PAB - stock.shuL;
//计算净需求数量NR
if (POH - stock.SS > -0.00005m)
{
NR = 0.0000m;
}
else
{
NR = stock.SS - POH;
}
log.Error("NR =" + NR + "LS = " + stock.LS);
//计算计划订单收料量PORC,即为缺件数量
PORC = 0.0000m;
if (NR > 0.00005m)
{
do
{
PORC += stock.LS;
} while (PORC - NR < -0.00005m);
}
log.Error("PORC =" + PORC);
//计算本期库存数量PAB = 计划订单收料量PORC + 预计在库量POH
stock.PAB = PORC + POH;
log.Error("process order 1");
//所有的生产形式都要登记缺件表
var tableName = "";
switch (option)
{
case 1: //重排
tableName = "ullageTempSingle";
break;
case 2: //续排
tableName = (tableFlag == 0) ? "ullageTempSingle2" : "ullageTempSingle";
break;
case 3: //预重排
tableName = "ullageTempSingleTemp";
break;
case 4: //预续排
tableName = "ullageTempSingle2Temp";
break;
default:
break;
}
sql = $"delete from {tableName} where gongZLH='{item.gongZLH}' and qiH='{item.qiH}' and wuLBM={stock.wuLBM}; " +
$"insert into {tableName} (gongZLH, qiH, xuH, wuLBM, gongZLBSh, mingCh, guiG, tuH, shengChXSh, gongShSh, queJShL, jiHBB, chunJHQJ ) values ('{item.gongZLH}', '{item.qiH}', 0, {stock.wuLBM},'','','','',{stock.shengChXSh},0.00,{PORC},'',0.00);";
cmd.CommandText = sql;
try
{
var result = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
log.Error(string.Format($"update ullageTempSingle Error\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
log.Error("process order 2");
//预排不再进行后续处理
if (option == 3 || option == 4)
{
return(true);
}
switch (stock.shengChXSh)
{
case "0": //原材料
// update caiGShJB001 采购数据表
sql = $"select count(wuLBM) from caiGShJB001 where wuLBM = {stock.wuLBM}";
cmd.CommandText = sql;
try
{
var result = Convert.ToInt32(cmd.ExecuteScalar());
if (result == 1) //找到记录，更新原记录
{
if (option == 1 || option == 3) //重排，预重排 覆盖原记录计划数量的值
{
sql = $"update caiGShJB001 set jiHShL = {PORC}, wanChShL = 0.00, jinE = 0.00, heJJE = 0.00 where wuLBM = {stock.wuLBM}";
}
else //续排 在原记录基础上累加 完成数量wanChShL和累计下达数量leiJXDShL如何赋值??
{
sql = $"update caiGShJB001 set jiHShL = jiHShL + {PORC} where wuLBM = {stock.wuLBM}";
}
}
else //找不到则登记新记录,计划数量= 缺件数量，其它为0
{
sql = $"insert into caiGShJB001 (wuLBM,jiHShL, wanChShL,leiJXDShL,jinE,heJJE,caiGY) values ({stock.wuLBM},{PORC},0.00,0.00,0.00,0.00,'')";
}
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (result != 1)
{
log.Error(string.Format($"update/insert caiGShJB001 Error\nsql[{sql}]\n"));
return(false);
}
}
catch (Exception e)
{
log.Error(string.Format($"Select caiGShJB001 Error\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
break;
case "1": //自制件
//工序转移准备单主表 gongXZhYZhBDZhB
sql = $"select count(wuLBM) from gongXZhYZhBDZhB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and wuLBM = {stock.wuLBM}";
cmd.CommandText = sql;
try
{
var result = Convert.ToInt32(cmd.ExecuteScalar());
sql = "";
if (result == 1) //找到记录，更新原记录 ???不等于1如何处理
{
if (option == 1 || option == 3) //重排，预重排 覆盖原记录计划数量的值
{
//根据令号，期号和物料编码删除原记录
sql = $"delete from gongXZhYZhBDZhB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and wuLBM = {stock.wuLBM}; ";
}
else //续排 报错
{
log.Error(string.Format($"续排，物料为自制件，工序转移准备单主表已有记录\nsql[{sql}]\n"));
return(false);
}
}
//产品名称：物料的模板编码
var chanPMCh = "";
var pos = stock.wuLBM.ToString().IndexOf("9");
if (pos >= 0)
{
chanPMCh = stock.wuLBM.ToString().Substring(0, pos);
}
sql = sql + $"insert into gongXZhYZhBDZhB (wuLBM,chanPMCh,gongXBSh) values ({stock.wuLBM},'{chanPMCh}',{stock.zum});";
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (result != 1)
{
log.Error(string.Format($"update/insert gongXZhYZhBDZhB Error\nsql[{sql}]\n"));
return(false);
}
}
catch (Exception e)
{
log.Error(string.Format($"process gongXZhYZhBDZhB Error\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
//工序转移准备单从表 gongXZhYZhBDCB
sql = $"select count(wuLBM) from gongXZhYZhBDCB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and xuH = {item.xuH} and wuLBM = {stock.wuLBM}";
cmd.CommandText = sql;
try
{
var result = Convert.ToInt32(cmd.ExecuteScalar());
sql = "";
if (result == 1) //找到记录，更新原记录
{
if (option == 1 || option == 3) //重排，预重排 覆盖原记录计划数量的值
{
//
sql = $"delete from gongXZhYZhBDCB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and xuH = {item.xuH} and wuLBM = {stock.wuLBM}; ";
}
else //续排 报错
{
log.Error(string.Format($"续排，物料为自制件，工序转移准备单从表已有记录\nsql[{sql}]\n"));
return(false);
}
}
sql = sql + $"insert into gongXZhYZhBDCB (gongZLH, qiH, xuH, wuLBM, jiHShL,tuH, guiG ) values ('{item.gongZLH}','{item.qiH}',{item.xuH},{stock.wuLBM},{PORC},'{stock.tuhao}','{stock.guige}');";
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (result != 1)
{
log.Error(string.Format($"update/insert gongXZhYZhBDCB Error\nsql[{sql}]\n"));
return(false);
}
}
catch (Exception e)
{
log.Error(string.Format($"process gongXZhYZhBDCB Error\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
// 更新投料采购下达 touLCGXD
sql = $"select count(wuLBM) from touLCGXD where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and wuLBM = {stock.wuLBM}";
cmd.CommandText = sql;
try
{
var result = Convert.ToInt32(cmd.ExecuteScalar());
if (result == 1) //找到记录，更新原记录
{
if (option == 1 || option == 3) //重排，预重排 覆盖原记录计划数量的值
{
sql = $"update touLCGXD set queJShL = {PORC}, chunJHQJ = 0.00, leiJXDShL = 0.00 where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and wuLBM = {stock.wuLBM}";
}
else //续排 在原记录基础上累加??
{
sql = $"update touLCGXD set queJShL = queJShL + {PORC} where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and wuLBM = {stock.wuLBM}";
}
}
else //找不到则登记新记录:缺件数量=PORC
{
sql = $"insert into touLCGXD (wuLBM,queJShL, chunJHQJ,qiH,gongZLH,shengChXSh,touLBSh) values ({stock.wuLBM},{PORC},0.00,'{item.qiH}','{item.gongZLH}','{stock.shengChXSh}',{stock.touLBSh})";
}
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (result != 1)
{
log.Error(string.Format($"update/insert touLCGXD Error\nsql[{sql}]\n"));
return(false);
}
}
catch (Exception e)
{
log.Error(string.Format($"process touLCGXD Error\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
break;
case "2": //外协
//外协出库准备单主表waiXChKZhBDZB
sql = $"select count(wuLBM) from waiXChKZhBDZB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and xuH = {item.xuH} and wuLBM = {stock.wuLBM}";
cmd.CommandText = sql;
try
{
var result = Convert.ToInt32(cmd.ExecuteScalar());
sql = "";
if (result == 1) //找到记录，更新原记录
{
if (option == 1 || option == 3) //重排，预重排 覆盖原记录计划数量的值
{
//
sql = $"delete from waiXChKZhBDZB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and xuH = {item.xuH} and wuLBM = {stock.wuLBM}; ";
}
else //续排 报错
{
log.Error(string.Format($"续排，物料为外协件，外协出库准备单主表已有记录r\nsql[{sql}]\n"));
return(false);
}
}
sql = sql + $"insert into waiXChKZhBDZB (gongZLH, qiH, xuH, wuLBM, tuH, guiG ) values ('{item.gongZLH}','{item.qiH}',{item.xuH},{stock.wuLBM},'{stock.tuhao}','{stock.guige}');";
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (result != 1)
{
log.Error(string.Format($"update/insert waiXChKZhBDZB Error\nsql[{sql}]\n"));
return(false);
}
}
catch (Exception e)
{
log.Error(string.Format($"process waiXChKZhBDZB Error\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
//外协出库准备单从表 waiXChKZhBDCB
sql = $"select count(wuLBM) from waiXChKZhBDCB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and xuH = {item.xuH} and wuLBM = {stock.wuLBM}";
cmd.CommandText = sql;
try
{
var result = Convert.ToInt32(cmd.ExecuteScalar());
sql = "";
if (result == 1) //找到记录，更新原记录
{
if (option == 1 || option == 3) //重排，预重排 覆盖原记录计划数量的值
{
//
sql = $"delete from waiXChKZhBDCB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and xuH = {item.xuH} and wuLBM = {stock.wuLBM}; ";
}
else //续排 报错
{
log.Error(string.Format($"续排，物料为外协件，外协出库准备单从表已有记录r\nsql[{sql}]\n"));
return(false);
}
}
sql = sql + $"insert into waiXChKZhBDCB (gongZLH, qiH, xuH, wuLBM, tuH, guiG, jiHShL ) values ('{item.gongZLH}','{item.qiH}',{item.xuH},{stock.wuLBM},'{stock.tuhao}','{stock.guige}',{PORC})";
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (result != 1)
{
log.Error(string.Format($"update/insert waiXChKZhBDCB Error\nsql[{sql}]\n"));
return(false);
}
}
catch (Exception e)
{
log.Error(string.Format($"process waiXChKZhBDCB Error\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
break;
case "3": //半成品
//工序转移准备单主表 gongXZhYZhBDZhB
sql = $"select count(wuLBM) from gongXZhYZhBDZhB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and wuLBM = {stock.wuLBM}";
cmd.CommandText = sql;
try
{
var result = Convert.ToInt32(cmd.ExecuteScalar());
sql = "";
if (result == 1) //找到记录，更新原记录
{
if (option == 1 || option == 3) //重排，预重排 覆盖原记录计划数量的值
{
//?? 给哪些字段赋值
sql = $"delete from gongXZhYZhBDZhB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and wuLBM = {stock.wuLBM}; ";
}
else //续排 报错??应该是查到续排前有重复的领号，期号，序号的记录就报错
{
log.Error(string.Format($"续排，物料为自制件，工序转移准备单主表已有记录r\nsql[{sql}]\n"));
return(false);
}
}
//产品名称：物料的模板编码
var chanPMCh = "";
var pos = stock.wuLBM.ToString().IndexOf("9");
if (pos >= 0)
{
chanPMCh = stock.wuLBM.ToString().Substring(0, pos);
}
sql = sql + $"insert into gongXZhYZhBDZhB (wuLBM,chanPMCh,gongXBSh) values ({stock.wuLBM},'{chanPMCh}',{stock.zum});";
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (result != 1)
{
log.Error(string.Format($"update/insert gongXZhYZhBDZhB Error\nsql[{sql}]\n"));
return(false);
}
}
catch (Exception e)
{
log.Error(string.Format($"process gongXZhYZhBDZhB Error\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
//工序转移准备单从表 gongXZhYZhBDCB
sql = $"select count(wuLBM) from gongXZhYZhBDCB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and xuH = {item.xuH} and wuLBM = {stock.wuLBM}";
cmd.CommandText = sql;
try
{
var result = Convert.ToInt32(cmd.ExecuteScalar());
sql = "";
if (result == 1) //找到记录，更新原记录
{
if (option == 1 || option == 3) //重排，预重排 覆盖原记录计划数量的值
{
//
sql = $"delete from gongXZhYZhBDCB where gongZLH = '{item.gongZLH}' and qiH = '{item.qiH}' and xuH = {item.xuH} and wuLBM = {stock.wuLBM}; ";
}
else //续排 报错
{
log.Error(string.Format($"续排，物料为自制件，工序转移准备单从表已有记录\nsql[{sql}]\n"));
return(false);
}
}
sql = sql + $"insert into gongXZhYZhBDCB (gongZLH, qiH, xuH, wuLBM, jiHShL,tuH, guiG ) values ('{item.gongZLH}','{item.qiH}',{item.xuH},{stock.wuLBM},{PORC},'{stock.tuhao}','{stock.guige}')";
cmd.CommandText = sql;
result = cmd.ExecuteNonQuery();
if (result != 1)
{
log.Error(string.Format($"update/insert gongXZhYZhBDCB Error\nsql[{sql}]\n"));
return(false);
}
}
catch (Exception e)
{
log.Error(string.Format($"process gongXZhYZhBDCB Error\nsql[{sql}]\nError[{e.StackTrace}]"));
return(false);
}
break;
case "4": //成品
break;
default:
break;
}
log.Error("process order 3");
}
log.Error("process order end");
return(true);
}
//初始化BOM树列表bomTree
private bool InitBomTree(PlanItem item)
{
bomTree.Clear();
return(AddBomItem(item.wuLBM, item.qiH, item.gongZLH));
}
private bool CheckData(int i, PlanItem item)
{
return(true);
}