/// <summary>
/// TWD
/// </summary>
/// <param name="vehiclePointStatusInfo"></param>
/// <param name="twdCounterInfos"></param>
/// <param name="twdMaintainInhouseLogisticStandardInfos"></param>
/// <returns></returns>
private string TwdCounterDeal(VehiclePointStatusInfo vehiclePointStatusInfo, List <TwdPartBoxInfo> twdPartBoxInfos, List <MaintainInhouseLogisticStandardInfo> twdMaintainInhouseLogisticStandardInfos)
{
///
StringBuilder stringBuilder = new StringBuilder();
///根据车辆状态信息中的生产订单号①获取到对应的生产订单物料清单、此处为了保障执行效率，需要根据已获取的计数器的物料号⑩过滤获取物料清单
///ZORDNO = 生产订单号
///ZCOMNO = 物料图号
List <PullOrderBomInfo> pullOrderBomInfos = new PullOrderBomBLL().GetList("" +
"[ZORDNO] = N'" + vehiclePointStatusInfo.OrderNo + "' and " +
"[ZCOMNO] in ('" + string.Join("','", twdMaintainInhouseLogisticStandardInfos.Select(d => d.PartNo).ToArray()) + "')", string.Empty);
if (pullOrderBomInfos.Count == 0)
{
return(string.Empty);
}
///
foreach (TwdPartBoxInfo twdPartBoxInfo in twdPartBoxInfos)
{
///不是这个信息点的零件类忽略
if (vehiclePointStatusInfo.StatusPointCode != twdPartBoxInfo.StatusPointCode)
{
continue;
}
///本零件类对应的物料拉动信息
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandards = twdMaintainInhouseLogisticStandardInfos.Where(d => d.InhousePartClass == twdPartBoxInfo.PartBoxCode).ToList();
///若本零件类无物料拉动信息则返回
if (twdMaintainInhouseLogisticStandardInfos.Count == 0)
{
continue;
}
///零件类过滤后的物料拉动信息对应的物料清单
List <PullOrderBomInfo> pullOrderBoms = pullOrderBomInfos.Where(d => maintainInhouseLogisticStandards.Select(m => m.PartNo).Contains(d.Zcomno)).ToList();
///循环过滤后的物料清单
foreach (PullOrderBomInfo pullOrderBom in pullOrderBoms)
{
///匹配物料拉动信息的最小维度是 物料图号+供应商+工位，依次降低维度来获取唯一的物料拉动信息
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandard = maintainInhouseLogisticStandards.FirstOrDefault(d =>
d.PartNo == pullOrderBom.Zcomno && d.SupplierNum == pullOrderBom.SupplierNum && d.Location == pullOrderBom.Zloc);
///物料图号+供应商
if (maintainInhouseLogisticStandard == null)
{
maintainInhouseLogisticStandard = maintainInhouseLogisticStandards.FirstOrDefault(d => d.PartNo == pullOrderBom.Zcomno && d.SupplierNum == pullOrderBom.SupplierNum);
}
///物料图号
if (maintainInhouseLogisticStandard == null)
{
maintainInhouseLogisticStandard = maintainInhouseLogisticStandards.FirstOrDefault(d => d.PartNo == pullOrderBom.Zcomno);
}
///未能成功获取到正确的物料拉动信息
if (maintainInhouseLogisticStandard == null)
{
continue;
}
///根据物料拉动信息外键获取计数器，未能成功获取时需要创建
TwdCounterInfo twdCounterInfo = TwdCounterBLL.GetInfoByPartPullFid(maintainInhouseLogisticStandard.Fid);
if (twdCounterInfo == null)
{
///创建计数器
twdCounterInfo = TwdCounterBLL.CreateTwdCounterInfo(loginUser);
///以物料拉动信息填充计数器
TwdCounterBLL.GetTwdCounterInfo(maintainInhouseLogisticStandard, ref twdCounterInfo);
///以零件类信息填充计数器
TwdCounterBLL.GetTwdCounterInfo(twdPartBoxInfo, ref twdCounterInfo);
///
twdCounterInfo.Id = new TwdCounterBLL().InsertInfo(twdCounterInfo);
if (twdCounterInfo.Id == 0)
{
throw new Exception("MC:0x00000453");///时间窗计数器创建失败
}
}
///计数器状态未处于启用
if (twdCounterInfo.Status != (int)BasicDataStatusConstants.Enable)
{
continue;
}
stringBuilder.AppendLine(TwdCounterBLL.UpdateTwdCounter(maintainInhouseLogisticStandard, twdPartBoxInfo, pullOrderBom.Zqty.GetValueOrDefault(), twdCounterInfo.Id, loginUser));
///创建计数器日志
TwdCounterLogInfo twdCounterLogInfo = TwdCounterLogBLL.CreateTwdCounterLogInfo(twdCounterInfo.Fid.GetValueOrDefault(), loginUser);
///以车辆过点信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(vehiclePointStatusInfo, ref twdCounterLogInfo);
///以物料拉动信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(maintainInhouseLogisticStandard, ref twdCounterLogInfo);
///以零件类信息填充计数器日志
TwdCounterLogBLL.GetTwdCounterLogInfo(twdPartBoxInfo, ref twdCounterLogInfo);
///PART_QTY
twdCounterLogInfo.PartQty = pullOrderBom.Zqty.GetValueOrDefault();
///
stringBuilder.AppendLine(TwdCounterLogDAL.GetInsertSql(twdCounterLogInfo));
///触发层级拉动
stringBuilder.AppendLine(TwdCounterBLL.LevelPullRequirementCounter(
maintainInhouseLogisticStandard,
pullOrderBom.Zqty.GetValueOrDefault(),
loginUser,
twdCounterInfo.Fid.GetValueOrDefault(),
twdCounterInfo.PartBoxCode));
}
}
return(stringBuilder.ToString());
}
/// <summary>
/// JIS
/// </summary>
/// <param name="vehiclePointStatusInfo"></param>
/// <param name="jisPartBoxInfos"></param>
/// <param name="jisCounterInfos"></param>
/// <param name="jisMaintainInhouseLogisticStandardInfos"></param>
/// <returns></returns>
private string JisCounterDeal(VehiclePointStatusInfo vehiclePointStatusInfo, List <JisPartBoxInfo> jisPartBoxInfos, List <MaintainInhouseLogisticStandardInfo> jisMaintainInhouseLogisticStandardInfos)
{
///
StringBuilder stringBuilder = new StringBuilder();
///根据车辆状态信息中的生产订单号①获取到对应的生产订单物料清单、此处为了保障执行效率，需要根据已获取的物料拉动信息过滤获取物料清单
List <PullOrderBomInfo> pullOrderBomInfos = new PullOrderBomBLL().GetList("" +
"[ZORDNO] = N'" + vehiclePointStatusInfo.OrderNo + "' and " +
"[ZCOMNO] in ('" + string.Join("','", jisMaintainInhouseLogisticStandardInfos.Select(d => d.PartNo).ToArray()) + "')", string.Empty);
if (pullOrderBomInfos.Count == 0)
{
return(string.Empty);
}
///
foreach (JisPartBoxInfo jisPartBoxInfo in jisPartBoxInfos)
{
///不是这个信息点的零件类忽略
if (vehiclePointStatusInfo.StatusPointCode != jisPartBoxInfo.StatusPointCode)
{
continue;
}
///根据物料拉动信息外键获取计数器，未能成功获取时需要创建
JisCounterInfo jisCounterInfo = new JisCounterBLL().GetInfoByPartBoxFid(jisPartBoxInfo.Fid.GetValueOrDefault());
if (jisCounterInfo == null)
{
jisCounterInfo = CreateJisCounterInfo(jisPartBoxInfo);
}
///零件类对应的物料拉动信息
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandards = jisMaintainInhouseLogisticStandardInfos.Where(d => d.InhousePartClass == jisPartBoxInfo.PartBoxCode).ToList();
if (maintainInhouseLogisticStandards.Count == 0)
{
continue;
}
///零件类过滤后的物料拉动信息对应的物料清单
List <PullOrderBomInfo> pullOrderBoms = pullOrderBomInfos.Where(d => maintainInhouseLogisticStandards.Select(m => m.PartNo).Contains(d.Zcomno)).ToList();
if (pullOrderBoms.Count == 0)
{
continue;
}
///当排序计数器的累计方式⑧为按车辆累计时
if (jisPartBoxInfo.AccumulativeType.GetValueOrDefault() == (int)JisAccumulativeTypeConstants.VehicleAccumulative)
{
///本次可累计车辆数量
decimal vehicleQty = jisCounterInfo.AccumulativeQty.GetValueOrDefault() - jisCounterInfo.CurrentQty.GetValueOrDefault();
int vehicleCounterStatus = (int)JisCounterStatusConstants.Accumulating;
if (vehicleQty == 1)
{
vehicleCounterStatus = (int)JisCounterStatusConstants.AccumulativeCompletion;
}
///
stringBuilder.AppendLine(UpdateJisCounter(jisPartBoxInfo, jisCounterInfo, 1, vehicleCounterStatus));
}
///
foreach (PullOrderBomInfo pullOrderBom in pullOrderBoms)
{
///匹配物料拉动信息的最小维度是 物料图号+供应商+工位，依次降低维度来获取唯一的物料拉动信息
MaintainInhouseLogisticStandardInfo maintainInhouseLogisticStandard = maintainInhouseLogisticStandards.FirstOrDefault(d =>
d.PartNo == pullOrderBom.Zcomno && d.SupplierNum == pullOrderBom.SupplierNum && d.Location == pullOrderBom.Zloc);
///物料图号+供应商
if (maintainInhouseLogisticStandard == null)
{
maintainInhouseLogisticStandard = maintainInhouseLogisticStandards.FirstOrDefault(d => d.PartNo == pullOrderBom.Zcomno && d.SupplierNum == pullOrderBom.SupplierNum);
}
///物料图号
if (maintainInhouseLogisticStandard == null)
{
maintainInhouseLogisticStandard = maintainInhouseLogisticStandards.FirstOrDefault(d => d.PartNo == pullOrderBom.Zcomno);
}
///未能成功获取到正确的物料拉动信息
if (maintainInhouseLogisticStandard == null)
{
continue;
}
///当排序计数器的累计方式⑧为按车辆累计时
if (jisPartBoxInfo.AccumulativeType.GetValueOrDefault() == (int)JisAccumulativeTypeConstants.VehicleAccumulative)
{
stringBuilder.AppendLine(UpdateJisCounter(jisPartBoxInfo, jisCounterInfo, vehiclePointStatusInfo, maintainInhouseLogisticStandard, pullOrderBom.Zqty.GetValueOrDefault()));
}
///当排序计数器的累计方式⑧为按器具累计时
if (jisPartBoxInfo.AccumulativeType.GetValueOrDefault() == (int)JisAccumulativeTypeConstants.UtensilAccumulative)
{
///物料需求数量
decimal requireQty = pullOrderBom.Zqty.GetValueOrDefault();
while (requireQty > 0)
{
int jisCounterStatus = (int)JisCounterStatusConstants.Accumulating;
///本次可累计数量
decimal currentQty = jisCounterInfo.AccumulativeQty.GetValueOrDefault() - jisCounterInfo.CurrentQty.GetValueOrDefault();
if (currentQty == 0)
{
break;
}
///需求大于本次可累计
if (currentQty <= requireQty)
{
///剩余需累计数量
requireQty -= currentQty;
///累计完成
jisCounterStatus = (int)JisCounterStatusConstants.AccumulativeCompletion;
}
else
{
///将需求赋予本次
currentQty = requireQty;
///需求清空
requireQty = 0;
}
///
stringBuilder.AppendLine(UpdateJisCounter(jisPartBoxInfo, jisCounterInfo, currentQty, jisCounterStatus));
///
stringBuilder.AppendLine(UpdateJisCounter(jisPartBoxInfo, jisCounterInfo, vehiclePointStatusInfo, maintainInhouseLogisticStandard, currentQty));
///当剩余需求数量大于零时，需要创建计数器
if (requireQty > 0)
{
jisCounterInfo = CreateJisCounterInfo(jisPartBoxInfo);
}
}
}
///触发层级拉动
stringBuilder.AppendLine(TwdCounterBLL.LevelPullRequirementCounter(
maintainInhouseLogisticStandard,
pullOrderBom.Zqty.GetValueOrDefault(),
loginUser,
jisCounterInfo.Fid.GetValueOrDefault(),
jisCounterInfo.PartBoxCode));
}
}
return(stringBuilder.ToString());
}
/// <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
}
}