/// <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 } }