/// <summary>
/// 缺件生产订单生成
/// </summary>
public void CheckLackProductionOrder()
{
///获取状态⑦为50已发布的缺件表数据，记录开始计算时间⑧并将状态⑦更新为60处理中
///数据库执行语句
StringBuilder sqlText = new StringBuilder();
///获取状态⑦为50反馈完成的缺件表
List <LackOfMaterialInfo> lackOfMaterialInfos = lackOfMaterialBLL.GetListByPage("" +
"[STATUS] = " + (int)LackOfMaterialStatusConstants.Feedbacked + "",
"[ID]", 1, 1, out int dataCnt);
///没有需要计算的缺件表
if (lackOfMaterialInfos.Count == 0)
{
return;
}
LackOfMaterialInfo lackOfMaterialInfo = lackOfMaterialInfos.FirstOrDefault();
///获取状态⑦为50已发布的缺件表数据，记录开始计算时间⑧并将状态⑦更新为60处理中
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.OrderCalculating, lackOfMaterialInfo.Id, "", loginUser);
///工厂
string plantCondition = string.Empty;
if (!string.IsNullOrEmpty(lackOfMaterialInfo.Plant))
{
plantCondition = " and [WERK] = N'" + lackOfMaterialInfo.Plant + "' ";
}
///根据开始日期⑤与结束日期⑥、工厂代码④范围获取生产订单列表，按生产日期+生产顺序从晚到早进行排列
///供货计划与生产订单日期相差一天
List <PullOrdersInfo> pullOrdersInfos = new PullOrdersBLL().GetList("" + plantCondition + " and " +
"[ORDER_DATE] between " +
"N'" + lackOfMaterialInfo.StartDate.GetValueOrDefault().AddDays(materialRequrieAdvanceDays) + "' and " +
"N'" + lackOfMaterialInfo.EndDate.GetValueOrDefault().AddDays(materialRequrieAdvanceDays) + "'",
"[ORDER_DATE] desc,[VEHICLE_ORDER] desc");
///没有生产订单时计算失败
if (pullOrdersInfos.Count == 0)
{
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.OrderCalculateFailed, lackOfMaterialInfo.Id, "没有生产订单时计算失败", loginUser);
return;
}
///根据缺件表明细中的物料号②、供应商代码③获取该生产订单的订单BOM中的数据
List <LackOfMaterialDetailInfo> lackOfMaterialDetailInfos = new LackOfMaterialDetailBLL().GetList("" +
"[LACK_ORDER_FID] = N'" + lackOfMaterialInfo.Fid.GetValueOrDefault() + "' and " +
"[FEEDBACK_FLAG] = 1", string.Empty);
if (lackOfMaterialDetailInfos.Count == 0)
{
lackOfMaterialBLL.UpdateStatus((int)LackOfMaterialStatusConstants.OrderCalculateFailed, lackOfMaterialInfo.Id, "缺件表中无物料缺件信息", loginUser);
return;
}
///BOM
List <PullOrderBomInfo> pullOrderBomInfos = new PullOrderBomBLL().GetList("" +
"[ORDERFID] in ('" + string.Join("','", pullOrdersInfos.Select(d => d.Fid.GetValueOrDefault()).ToArray()) + "') and " +
"[ZCOMNO] in ('" + string.Join("','", lackOfMaterialDetailInfos.Select(d => d.PartNo).ToArray()) + "') and " +
"[SUPPLIER_NUM] in ('" + string.Join("','", lackOfMaterialDetailInfos.Select(d => d.SupplierNum).ToArray()) + "')", string.Empty);
///历史缺件生产订单 ORDER_DATE
List <PorderLackMaterialInfo> lackOfMaterialProductionOrderInfos = new PorderLackMaterialBLL().GetList(
"[ORDER_DATE] between " +
"N'" + lackOfMaterialInfo.StartDate.GetValueOrDefault().AddDays(materialRequrieAdvanceDays) + "' and " +
"N'" + lackOfMaterialInfo.EndDate.GetValueOrDefault().AddDays(materialRequrieAdvanceDays) + "'", string.Empty);
///LOG_FID
Guid logFid = Guid.NewGuid();
foreach (PullOrdersInfo pullOrdersInfo in pullOrdersInfos)
{
///并将该数据集合中的消耗数量与反馈缺件数量⑦进行对比，当反馈缺件数量⑦ – 消耗数量 >= 0 时
List <PullOrderBomInfo> pullOrderBoms = pullOrderBomInfos.Where(d => d.Orderfid.GetValueOrDefault() == pullOrdersInfo.Fid.GetValueOrDefault()).ToList();
if (pullOrderBoms.Count == 0)
{
continue;
}
///此时需要记录对比结果到生产订单缺件明细中，物料号③、供应商代码④、工厂代码⑤、需求数量⑥即为BOM消耗数量
var pullOrderBomQuery = pullOrderBoms
.GroupBy(b => new { b.Zcomno, b.SupplierNum })
.Select(d => new { PartNo = d.Key.Zcomno, d.Key.SupplierNum, Zqty = d.Sum(x => x.Zqty.GetValueOrDefault()) }).ToList();
///缺件数量⑦即当反馈缺件数量⑦ – 消耗数量 >= 0 是为消耗数量，否则为反馈缺件剩余数量⑦
///同时记录缺件生产订单，生产订单号③、生产订单外键②、工厂代码④、车间代码⑤、生产线代码⑥从生产订单中继承、缺件表外键①从缺件表继承、缺件标记⑦为True
var lackDetailInfos = (from l in lackOfMaterialDetailInfos
join p in pullOrderBomQuery
on new { l.PartNo, l.SupplierNum } equals new { p.PartNo, p.SupplierNum }
where l.FeedbackLackQty > 0
select new { l.PartNo, l.SupplierNum, l.Plant, l.PartPurchaser, p.Zqty, l.FeedbackLackQty, l.Fid, l.LackOrderFid }).ToList();
///历史生产订单中是否存在此订单号
PorderLackMaterialInfo lackOfMaterialProductionOrderInfo
= lackOfMaterialProductionOrderInfos.FirstOrDefault(d =>
d.ProductionOrderFid.GetValueOrDefault() == pullOrdersInfo.Fid.GetValueOrDefault());
///当缺件明细中的反馈缺件数量全部被扣除到小于等于零时，不再记录生产订单缺件明细，仅记录缺件生产订单，此时开始缺件标记⑦为False
Guid lackOfMaterialProductionOrderFid = Guid.NewGuid();
#region TT_ATP_PORDER_LACK_MATERIAL
if (lackOfMaterialProductionOrderInfo == null)
{
///不存在、直接插入
sqlText.AppendFormat("insert into [LES].[TT_ATP_PORDER_LACK_MATERIAL] (" +
"[FID],[LACK_ORDER_FID],[PRODUCTION_ORDER_FID],[PRODUCTION_ORDER_NO],[PLANT],[ASSEMBLY_LINE]," +
"[VALID_FLAG],[LACK_FLAG],[CREATE_USER],[CREATE_DATE],[ORDER_DATE]) values (" +
"'{8}','{0}','{1}','{2}','{3}','{4}'," +
"1,{7},'{5}',GETDATE(),'{6}');",
lackOfMaterialInfo.Fid, ///LACK_ORDER_FID,0
pullOrdersInfo.Fid, ///PRODUCTION_ORDER_FID,1
pullOrdersInfo.OrderNo, ///PRODUCTION_ORDER_NO,2
pullOrdersInfo.Werk, ///PLANT,3
pullOrdersInfo.AssemblyLine, ///ASSEMBLY_LINE,4
loginUser, ///CREATE_USER,5
pullOrdersInfo.OrderDate.GetValueOrDefault(), ///ORDER_DATE,6
(lackDetailInfos.Count == 0 ? 0 : 1), ///LACK_FLAG,7
lackOfMaterialProductionOrderFid ///FID,8
);
}
else
{
lackOfMaterialProductionOrderFid = lackOfMaterialProductionOrderInfo.Fid.GetValueOrDefault();
///已存在、更新状态
sqlText.AppendFormat("update [LES].[TT_ATP_PORDER_LACK_MATERIAL] set " +
"[LACK_FLAG] = {4}," +
"[LACK_ORDER_FID] = N'{0}'," +
"[ORDER_DATE] = N'{3}'," +
"[MODIFY_USER] = N'{1}'," +
"[MODIFY_DATE] = GETDATE() where " +
"[FID] = N'{2}';",
lackOfMaterialInfo.Fid.GetValueOrDefault(), ///LACK_ORDER_FID,0
loginUser, ///MODIFY_USER,1
lackOfMaterialProductionOrderFid, ///FID,2
pullOrdersInfo.OrderDate.GetValueOrDefault(), ///ORDER_DATE,3
(lackDetailInfos.Count == 0 ? 0 : 1) ///LACK_FLAG,4
);
///删除之前计算的生产订单缺件明细
sqlText.AppendFormat("delete from [LES].[TT_ATP_PORDER_LACK_MATERIAL_DETAIL] where " +
"[LACK_PORDER_FID] = N'{0}';",
lackOfMaterialProductionOrderFid///LACK_PORDER_FID,0
);
///移除
//lackOfMaterialProductionOrderInfos.Remove(lackOfMaterialProductionOrderInfo);
}
#endregion
#region TI_IFM_SAP_PRODUCTION_ORDER_LACK_MATERIAL
///插入到SAP中间表
if (lackMaterialProductionOrdersToSap.ToLower() == "true")
{
sqlText.AppendFormat(" insert into [LES].[TI_IFM_SAP_PRODUCTION_ORDER_LACK_MATERIAL] (" +
"[FID],[ENTERPRISE],[AREA_NO],[DMS_NO],[MATERIAL_CHECK],[SEND_TIME],[VALID_FLAG],[CREATE_USER],[CREATE_DATE],[PROCESS_FLAG],[LOG_FID]) values (" +
"NEWID(),'{0}','{1}','{2}',0,GETDATE(),1,'{3}',GETDATE(),{5},'{4}');",
pullOrdersInfo.Werk, ///ENTERPRISE,0
pullOrdersInfo.AssemblyLine, ///AREA_NO,1
pullOrdersInfo.OrderNo, ///DMS_NO,2
loginUser, /// CREATE_USER,3
logFid, ///LOG_FID,4
(int)ProcessFlagConstants.Untreated ///PROCESS_FLAG,5
);
}
#endregion
#region TI_IFM_MES_PRODUCTION_ORDER_LACK_MATERIAL
///插入到MES中间表
if (lackMaterialProductionOrdersToMes.ToLower() == "true")
{
sqlText.AppendFormat("insert into [LES].[TI_IFM_MES_PRODUCTION_ORDER_LACK_MATERIAL] (" +
"[FID],[ENTERPRISE],[AREA_NO],[DMS_NO],[MATERIAL_CHECK],[SEND_TIME],[VALID_FLAG],[CREATE_USER],[CREATE_DATE],[PROCESS_FLAG],[LOG_FID]) values (" +
"NEWID(),'{0}','{1}','{2}',0,GETDATE(),1,'{3}',GETDATE(),{5},'{4}');",
pullOrdersInfo.Werk, ///ENTERPRISE,0
pullOrdersInfo.AssemblyLine, ///AREA_NO,1
pullOrdersInfo.OrderNo, ///DMS_NO,2
loginUser, /// CREATE_USER,3
logFid, ///LOG_FID,4
(int)ProcessFlagConstants.Untreated ///PROCESS_FLAG,5
);
}
#endregion
#region TT_ATP_PORDER_LACK_MATERIAL_DETAIL
if (lackDetailInfos.Count > 0)
{
///写入生产订单缺件明细
foreach (var lackDetailInfo in lackDetailInfos)
{
sqlText.AppendFormat("insert into [LES].[TT_ATP_PORDER_LACK_MATERIAL_DETAIL] (" +
"[FID],[LACK_PORDER_FID],[PRODUCTION_ORDER_FID],[LACK_DETAIL_FID],[PART_NO],[SUPPLIER_NUM],[PLANT],[REQUIRE_QTY],[LACK_QTY]," +
"[VALID_FLAG],[CREATE_USER],[CREATE_DATE]," +
"[PART_PURCHASER],[PRODUCTION_ORDER_NO],[ASSEMBLY_LINE],[ORDER_DATE],[LACK_ORDER_FID]) values " +
"(NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}',{6},{7}," +
"1,'{8}',GETDATE(),'{9}','{10}','{11}','{12}','{13}');",
lackOfMaterialProductionOrderFid, ///LACK_PORDER_FID,0
pullOrdersInfo.Fid.GetValueOrDefault(), ///PRODUCTION_ORDER_FID,1
lackDetailInfo.Fid.GetValueOrDefault(), ///LACK_DETAIL_FID,2
lackDetailInfo.PartNo, ///PART_NO,3
lackDetailInfo.SupplierNum, ///SUPPLIER_NUM,4
lackDetailInfo.Plant, ///PLANT,5
lackDetailInfo.Zqty, ///REQUIRE_QTY,6
lackDetailInfo.FeedbackLackQty >= lackDetailInfo.Zqty ? lackDetailInfo.Zqty : lackDetailInfo.FeedbackLackQty, ///LACK_QTY,7
loginUser, ///CREATE_USER,8
lackDetailInfo.PartPurchaser, ///PART_PURCHASER,9
pullOrdersInfo.OrderNo, ///PRODUCTION_ORDER_NO,10
pullOrdersInfo.AssemblyLine, ///ASSEMBLY_LINE,11
pullOrdersInfo.OrderDate.GetValueOrDefault(), ///ORDER_DATE,12
lackDetailInfo.LackOrderFid.GetValueOrDefault() ///LACK_ORDER_FID,13
);
}
}
#endregion
///将差异值作为缺件数量以用于下一份生产订单的比对
lackOfMaterialDetailInfos = (from l in lackOfMaterialDetailInfos
join p in pullOrderBomQuery
on new { l.PartNo, l.SupplierNum } equals new { p.PartNo, p.SupplierNum } into temp
from tt in temp.DefaultIfEmpty()
select new LackOfMaterialDetailInfo {
PartNo = l.PartNo, SupplierNum = l.SupplierNum, Plant = l.Plant, PartPurchaser = l.PartPurchaser, FeedbackLackQty = l.FeedbackLackQty - (tt == null ? 0 : tt.Zqty), Fid = l.Fid, LackOrderFid = l.LackOrderFid
}).ToList();
}
#region 从检查日期范围内移出的生产订单
///移除的历史生产订单
var lackOfMaterialProductionOrderRemoveInfos = (from l in lackOfMaterialProductionOrderInfos
where !(from p in pullOrdersInfos where p.OrderNo == l.ProductionOrderNo select p).Any()
select l).ToList();
foreach (var lackOfMaterialProductionOrderRemoveInfo in lackOfMaterialProductionOrderRemoveInfos)
{
///更新状态
sqlText.AppendFormat("update [LES].[TT_ATP_PORDER_LACK_MATERIAL] set " +
"[LACK_FLAG] = {3}," +
"[LACK_ORDER_FID] = N'{0}'," +
"[MODIFY_USER] = N'{1}'," +
"[MODIFY_DATE] = GETDATE() where " +
"[PRODUCTION_ORDER_FID] = N'{2}';",
lackOfMaterialInfo.Fid.GetValueOrDefault(), ///LACK_ORDER_FID,0
loginUser, ///MODIFY_USER,1
lackOfMaterialProductionOrderRemoveInfo.ProductionOrderFid.GetValueOrDefault(), ///PRODUCTION_ORDER_FID,2
0 ///LACK_FLAG,3
);
///删除明细
sqlText.AppendFormat("delete from [LES].[TT_ATP_PORDER_LACK_MATERIAL_DETAIL] where " +
"[PRODUCTION_ORDER_FID] = N'{0}';",
lackOfMaterialProductionOrderRemoveInfo.ProductionOrderFid.GetValueOrDefault()///PRODUCTION_ORDER_FID,0
);
///插入到SAP中间表
if (lackMaterialProductionOrdersToSap.ToLower() == "true")
{
sqlText.AppendFormat(" insert into [LES].[TI_IFM_SAP_PRODUCTION_ORDER_LACK_MATERIAL] (" +
"[FID],[ENTERPRISE],[AREA_NO],[DMS_NO],[MATERIAL_CHECK],[SEND_TIME],[VALID_FLAG],[CREATE_USER],[CREATE_DATE],[PROCESS_FLAG],[LOG_FID]) values (" +
"NEWID(),'{0}','{1}','{2}',{6},GETDATE(),1,'{3}',GETDATE(),{5},'{4}');",
lackOfMaterialProductionOrderRemoveInfo.Plant, ///ENTERPRISE,0
lackOfMaterialProductionOrderRemoveInfo.AssemblyLine, ///AREA_NO,1
lackOfMaterialProductionOrderRemoveInfo.ProductionOrderNo, ///DMS_NO,2
loginUser, /// CREATE_USER,3
logFid, ///LOG_FID,4
(int)ProcessFlagConstants.Untreated, ///PROCESS_FLAG,5
0 ///MATERIAL_CHECK,6
);
}
///插入到MES中间表
if (lackMaterialProductionOrdersToMes.ToLower() == "true")
{
sqlText.AppendFormat("insert into [LES].[TI_IFM_MES_PRODUCTION_ORDER_LACK_MATERIAL] (" +
"[FID],[ENTERPRISE],[AREA_NO],[DMS_NO],[MATERIAL_CHECK],[SEND_TIME],[VALID_FLAG],[CREATE_USER],[CREATE_DATE],[PROCESS_FLAG],[LOG_FID]) values (" +
"NEWID(),'{0}','{1}','{2}',{6},GETDATE(),1,'{3}',GETDATE(),{5},'{4}');",
lackOfMaterialProductionOrderRemoveInfo.Plant, ///ENTERPRISE,0
lackOfMaterialProductionOrderRemoveInfo.AssemblyLine, ///AREA_NO,1
lackOfMaterialProductionOrderRemoveInfo.ProductionOrderNo, ///DMS_NO,2
loginUser, /// CREATE_USER,3
logFid, ///LOG_FID,4
(int)ProcessFlagConstants.Untreated, ///PROCESS_FLAG,5
0 ///MATERIAL_CHECK,6
);
}
}
#endregion
///
if (sqlText.Length > 0)
{
///调用发送数据
string keyValue = lackOfMaterialInfo.StartDate.GetValueOrDefault().ToString("yyyyMMdd") + "~" + lackOfMaterialInfo.EndDate.GetValueOrDefault().ToString("yyyyMMdd");
if (lackMaterialProductionOrdersToSap.ToLower() == "true")
{
sqlText.AppendFormat(BLL.LES.CommonBLL.GetCreateOutboundLogSql("SAP", logFid, "LES-SAP-017", keyValue, loginUser));
}
if (lackMaterialProductionOrdersToMes.ToLower() == "true")
{
sqlText.AppendFormat(BLL.LES.CommonBLL.GetCreateOutboundLogSql("MES", logFid, "LES-MES-002", keyValue, loginUser));
}
///计算完成后记录计算结束时间⑨并更新状态⑦为70已完成，同时将缺件生产订单及生产订单缺件明细一次性写入数据库中
sqlText.AppendFormat("update [LES].[TT_ATP_LACK_OF_MATERIAL] set " +
"[STATUS] = {2}," +
"[EXECUTE_END_TIME] = GETDATE()," +
"[MODIFY_USER] = N'{0}'," +
"[MODIFY_DATE] = GETDATE() where " +
"[ID] = {1};",
loginUser, ///MODIFY_USER,0
lackOfMaterialInfo.Id, ///ID,1
(int)LackOfMaterialStatusConstants.OrderCompleted ///STATUS,2
);
#region 执行
using (TransactionScope trans = new TransactionScope())
{
BLL.LES.CommonBLL.ExecuteNonQueryBySql(sqlText.ToString());
trans.Complete();
}
#endregion
}
}
public long InsertInfo(PorderLackMaterialInfo info)
{
return(dal.Add(info));
}