private void button3_Click(object sender, EventArgs e) { string orderNo = this.textBox1.Text; PullOrdersInfo pullOrdersInfo = new PullOrdersBLL().GetInfoByOrderNo(orderNo); MesVehiclePointScanInfo mesVehiclePointScanInfo = new MesVehiclePointScanInfo(); mesVehiclePointScanInfo.Fid = Guid.NewGuid(); ///ENTERPRISE 工厂编号 mesVehiclePointScanInfo.Enterprise = new PlantBLL().GetSapPlantByPlantCode(pullOrdersInfo.Werk); ///车间 mesVehiclePointScanInfo.SiteNo = null; ///AREA_NO 生产线编号 mesVehiclePointScanInfo.AreaNo = new AssemblyLineBLL().GetSapAssemblyLineByAssemblyLine(pullOrdersInfo.AssemblyLine); //UNIT_NO 采集点编号 mesVehiclePointScanInfo.UnitNo = this.button3.Text; //DMS_SEQ 过点顺序号 mesVehiclePointScanInfo.DmsSeq = Convert.ToInt32(pullOrdersInfo.VehicleOrder); //DMS_NO 计划订单号 mesVehiclePointScanInfo.DmsNo = pullOrdersInfo.OrderNo; mesVehiclePointScanInfo.ProcessFlag = (int)ProcessFlagResuitsConstants.Created; //SEND_TIME 发送时间 mesVehiclePointScanInfo.SendTime = DateTime.Now; mesVehiclePointScanInfo.ValidFlag = true; mesVehiclePointScanInfo.CreateDate = DateTime.Now; mesVehiclePointScanInfo.CreateUser = "******"; label3.Text = new MesVehiclePointScanBLL().InsertInfo(mesVehiclePointScanInfo).ToString(); }
/// <summary> /// 主函数 /// </summary> public void Handler() { ///当系统配置标记启用时,获取车辆状态点中状态为物料替换的过点记录 if (vehicleStatusRepleacePorderBom.ToLower() == "true") { List <VehiclePointStatusInfo> vehiclePointStatusInfos = new VehiclePointStatusBLL().GetList("and [VEHICLE_STATUS]=" + (int)VehicleStatusTypeConstants.MaterialReplacement + "", string.Empty); if (vehiclePointStatusInfos.Count == 0) { return; } ///根据其生产订单号判断该生产订单是否已经进行过物料替换,若已替换则不能重复替换 List <PullOrdersInfo> pullOrdersInfos = new PullOrdersBLL().GetList("" + " and [ORDER_NO] in ('" + string.Join("','", vehiclePointStatusInfos.Select(d => d.OrderNo).ToArray()) + "')" + " and [CHANGE_FLAG]=" + (int)ChangeFlagConstants.NotReplaced + "", string.Empty); if (pullOrdersInfos.Count == 0) { return; } foreach (PullOrdersInfo pullOrdersInfo in pullOrdersInfos) { StringBuilder @string = new StringBuilder(); ///更改单 TT_BPM_BOM_CHANGE_ORDER @string.AppendFormat(new BomChangeOrderBLL().TransitionBreakpoint(pullOrdersInfo, loginUser)); ///替换条件 TT_BPM_BOM_REPLEACE_CONDITION @string.AppendFormat(new BomRepleaceConditionBLL().ReplacementCriteria(pullOrdersInfo, loginUser)); if (@string.Length > 0) { ///更改生产订单状态为已替换 @string.AppendFormat("update [LES].[TT_BAS_PULL_ORDERS] set " + "[CHANGE_FLAG]=" + (int)ChangeFlagConstants.Replaced + "," + "[MODIFY_DATE] = GETDATE()," + "[MODIFY_USER] = N'" + loginUser + "'" + "where [ID]=" + pullOrdersInfo.Id + ""); using (TransactionScope trans = new TransactionScope()) { BLL.LES.CommonBLL.ExecuteNonQueryBySql(@string.ToString()); trans.Complete(); } } } } }
/// <summary> /// 确认按钮点击时 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button1_Click(object sender, EventArgs e) { string orderNo = this.textBox1.Text; if (!string.IsNullOrEmpty(orderNo)) { PullOrdersInfo pullOrdersInfo = new PullOrdersBLL().GetInfoByOrderNo(orderNo); if (pullOrdersInfo == null) { MessageBox.Show("生产单号不存在!"); } else { new StocksBLL().CheckMaterialStock(orderNo, pullOrdersInfo.AssemblyLine, "SimulationoOverPoint", true); this.button2.Visible = true; this.button3.Visible = true; this.button4.Visible = true; } } }
/// <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 } }
/// <summary> /// Handler /// </summary> public void Handler() { ///获取排序零件类中状态㉖为已启用的数据 List <JisPartBoxInfo> jisPartBoxInfos = new JisPartBoxBLL().GetList(" and [STATUS] =" + (int)BasicDataStatusConstants.Enable + "", string.Empty); if (jisPartBoxInfos.Count == 0) { return; } ///获取零件类对应的计数器 List <JisCounterInfo> jisCounterInfos = new JisCounterBLL().GetList("and [PART_BOX_FID] in ('" + string.Join("','", jisPartBoxInfos.Select(d => d.Fid).ToArray()) + "')", string.Empty); if (jisCounterInfos.Count == 0) { return; } ///对应的排序计数器日志 List <JisCounterLogInfo> jisCounterLogInfos = new JisCounterLogBLL().GetList("and [COUNTER_FID] in ('" + string.Join("','", jisCounterInfos.Select(d => d.Fid).ToArray()) + "')", string.Empty); if (jisCounterLogInfos.Count == 0) { return; } ///计数器日志对应的生产订单 List <PullOrdersInfo> pullOrdersInfos = new PullOrdersBLL().GetList("and [ORDER_NO] in ('" + string.Join("','", jisCounterLogInfos.Select(d => d.SourceData).ToArray()) + "')", string.Empty); if (pullOrdersInfos.Count == 0) { return; } ///对应的物料拉动信息 List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos = new MaintainInhouseLogisticStandardBLL().GetList("and [STATUS] =" + (int)BasicDataStatusConstants.Enable + "" + " and [INHOUSE_PART_CLASS] in ('" + string.Join("','", jisPartBoxInfos.Select(d => d.PartBoxCode).ToArray()) + "')" + " and [PART_NO] in ('" + string.Join("','", jisCounterLogInfos.Select(d => d.PartNo).ToArray()) + "')", string.Empty); if (maintainInhouseLogisticStandardInfos.Count == 0) { return; } foreach (JisPartBoxInfo jisPartBoxInfo in jisPartBoxInfos) { ///零件类对应的物料拉动信息 List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandards = maintainInhouseLogisticStandardInfos.Where(d => d.InhousePartClass == jisPartBoxInfo.PartBoxCode).ToList(); ///计数器 List <JisCounterInfo> jisCounters = new List <JisCounterInfo>(); ///获取状态为20.累计完成的排序计数器 jisCounters = jisCounterInfos.Where(d => d.Status == (int)JisCounterStatusConstants.AccumulativeCompletion && d.PartBoxFid == jisPartBoxInfo.Fid).ToList(); ///若此时未能成功获取到状态为20.累计完成的排序计数器时,需要获取状态为10.正在累计的排序计数器,判断其创建时间加上最大累积时间是否已超过当前时间 if (jisCounters.Count == 0) { jisCounters = jisCounterInfos.Where(d => d.Status == (int)JisCounterStatusConstants.Accumulating && d.PartBoxFid == jisPartBoxInfo.Fid && d.CreateDate.AddMinutes(jisPartBoxInfo.MaxAccumulativeTime.GetValueOrDefault()) > DateTime.Now).ToList(); } if (jisCounters.Count == 0) { continue; } ///若已超过则需要首先标记排序计数器状态为20.累计完成,之后的逻辑按部就班走 BLL.LES.CommonBLL.ExecuteNonQueryBySql(@"update[LES].[TT_MPM_JIS_COUNTER] set [STATUS]=" + (int)JisCounterStatusConstants.AccumulativeCompletion + ",[MODIFY_DATE] = getdate(),[MODIFY_USER] = '" + loginUser + "' where [ID] in (" + string.Join(",", jisCounters.Select(d => d.Id).ToArray()) + ");"); ///根据计数器生成拉动单 foreach (JisCounterInfo jisCounterInfo in jisCounters) { ///sql StringBuilder stringBuilder = new StringBuilder(); ///对应的排序计数器日志 List <JisCounterLogInfo> jisCounterLogs = jisCounterLogInfos.Where(d => d.CounterFid == jisCounterInfo.Fid).ToList(); if (jisCounterLogs.Count == 0) { continue; } ///计数器日志物料对应的物料拉动信息 List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogistics = (from maintainInhouseLogisticStandard in maintainInhouseLogisticStandards join jisCounterLog in jisCounterLogs on maintainInhouseLogisticStandard.PartNo equals jisCounterLog.PartNo select maintainInhouseLogisticStandard).ToList(); if (maintainInhouseLogistics.Count == 0) { continue; } switch (jisPartBoxInfo.JisPullMode.GetValueOrDefault()) { ///排序拉动 case (int)JisPullModeConstants.JisPull: stringBuilder.AppendFormat(JisPull(jisPartBoxInfo, jisCounterInfo, jisCounterLogs, maintainInhouseLogistics, pullOrdersInfos, true)); break; ///物料成套拉动 case (int)JisPullModeConstants.CompletePull: stringBuilder.AppendFormat(JisPull(jisPartBoxInfo, jisCounterInfo, jisCounterLogs, maintainInhouseLogistics, pullOrdersInfos, false)); break; default: continue; } ///数据库执行语句 if (stringBuilder.Length > 0) { stringBuilder.AppendFormat(@"update[LES].[TT_MPM_JIS_COUNTER] set [STATUS]=" + (int)JisCounterStatusConstants.GeneratedDocuments + ",[MODIFY_DATE] = getdate(),[MODIFY_USER] = '" + loginUser + "' where [ID] = " + jisCounterInfo.Id + "; "); using (TransactionScope trans = new TransactionScope()) { BLL.LES.CommonBLL.ExecuteNonQueryBySql(stringBuilder.ToString()); trans.Complete(); } } } } }
public void Handler() { #region 基础变量 ///最近一条的中间表数据状态 int processFlag = 0; ///本次线程已处理的中间表主键 List <long> dealedIds = new List <long>(); //数据库执行语句 StringBuilder @string = new StringBuilder(); ///物料需求提前天数 int.TryParse(materialRequireAdvanceDays, out int intMaterialRequrieAdvanceDays); intMaterialRequrieAdvanceDays = 0 - intMaterialRequrieAdvanceDays; #endregion while (processFlag != 10) { ///开始处理时间 DateTime startExecuteTime = DateTime.Now; ///获取状态⑮为10.未处理的SAP生产订单数据 ///因为后续处理过程较为复杂,所以一次获取一条ID主键最靠前的10.未处理或30.挂起或40.逆处理状态数据 ///当上一条处理数据为30.挂起状态时需要继续处理下一条,否则执行结束 ///也就意味着30.挂起的数据将优先处理且为了保障挂起数据不影响正常未处理数据而设定的逻辑 #region 获取待处理的数据 string textWhere = "[PROCESS_FLAG] in (" + (int)ProcessFlagConstants.Untreated + "," + (int)ProcessFlagConstants.Suspend + "," + (int)ProcessFlagConstants.ConverseProgress + ")"; ///集合大于0,排除 if (dealedIds.Count > 0) { textWhere += "and [ID] not in (" + string.Join(",", dealedIds.ToArray()) + ")"; } /// SapProductOrderInfo sapProductOrderInfo = new SapProductOrderBLL().GetTopOneInfo(textWhere, "[ID] asc"); if (sapProductOrderInfo == null) { throw new Exception("MC:3x00000015");///没有已启用的SAP生产订单信息 } if (sapProductOrderInfo.OnlineDate == null) { throw new Exception("MC:3x00000033");///SAP生产订单上线日期信息错误 } Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + ":Start " + sapProductOrderInfo.Aufnr); ///SAP生产订单上线日期 string sapProductOrderDate = sapProductOrderInfo.OnlineDate.GetValueOrDefault().ToString("yyyyMMdd"); ///处理状态 processFlag = sapProductOrderInfo.ProcessFlag.GetValueOrDefault(); /// dealedIds.Add(sapProductOrderInfo.Id); #endregion #region SAP生产订单物料清单 ///获取SAP生产订单中订单号⑤=③对应的SAP生产订单物料清单 SapProductOrderBomInfo sapProductOrderBomInfo = new SapProductOrderBomBLL().GetInfoByAufnr(sapProductOrderInfo.Aufnr); ///若此时未能获取到数据则表示该SAP生产订单的物料清单还未能从SAP成功接收到 if (sapProductOrderBomInfo == null) { ///需要终止该条生产订单的处理,并且标记为挂起状态⑮ new SapProductOrderBLL().UpdateInfo("[PROCESS_FLAG] = " + (int)ProcessFlagConstants.Suspend + ",[MODIFY_USER] = N'" + loginUser + "',[MODIFY_DATE] = GETDATE()", sapProductOrderInfo.Id); continue; } #endregion #region 订单物料清单XML解析 ///SAP生产订单物料清单中的物料信息⑦需要XML解析后并逐条以工厂③=②、物料号①=⑦.MATNR、供应商②=⑦.LIFNR XmlWrapper xmlWrapper = new XmlWrapper(sapProductOrderBomInfo.Matnrs, LoadType.FromString); List <object> objMatnrs = xmlWrapper.XmlToList("/MatnrsAll/Matnrs", typeof(Matnrs)); if (objMatnrs.Count == 0) { throw new Exception("0x00000182");///无订单物料清单 } List <string> partNos = new List <string>(); List <string> supplierNums = new List <string>(); foreach (Matnrs matnr in objMatnrs) { partNos.Add(matnr.Matnr); supplierNums.Add(matnr.Lifnr); } List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsBLL().GetListForInterfaceDataSync(partNos); if (maintainPartsInfos.Count == 0) { throw new Exception("0x00000182");///无订单物料清单 } List <SupplierInfo> supplierInfos = new SupplierBLL().GetListForInterfaceDataSync(supplierNums); #endregion ///需根据SAP生产订单中的上线日期⑩判定供货计划TT_ATP_SUPPLY_PLAN中的对应日期列是否存在 ///日期列的列名规则为yyyyMMdd数据类型为decimal(18,4),不存在则需要新建列 ProcessSupplyPlanDate(sapProductOrderInfo.OnlineDate.GetValueOrDefault().AddDays(intMaterialRequrieAdvanceDays)); /// string plant = new PlantBLL().GetPlantBySapPlantCode(sapProductOrderInfo.Dwerk); if (string.IsNullOrEmpty(plant)) { throw new Exception("3x00000016");///工厂不存在 } string assemblyLine = new AssemblyLineBLL().GetAssemblyLineBySapAssemblyLine(sapProductOrderInfo.Verid); if (string.IsNullOrEmpty(assemblyLine)) { throw new Exception("3x00000017");///产线不存在 } ///根据生产订单号⑤=①获取生产订单TT_BAS_PULL_ORDERS数据 PullOrdersInfo pullOrdersInfo = new PullOrdersBLL().GetInfoByOrderNo(sapProductOrderInfo.Aufnr); ///生产订单上线日期 string productOrderDate = sapProductOrderDate; ///供货日期 string materialRequireDate = sapProductOrderInfo.OnlineDate.GetValueOrDefault().AddDays(intMaterialRequrieAdvanceDays).ToString("yyyyMMdd"); #region 首次下发 ///若此时未能成功获取数据则表示该生产订单为首次下发,且不是删除的生产订单 if (pullOrdersInfo == null && string.IsNullOrEmpty(sapProductOrderInfo.Zsc)) { if (calculateSupplyPlanFlag.ToLower() == "true") { #region 供货计划 foreach (Matnrs matnr in objMatnrs) { decimal partQty = 0; decimal.TryParse(matnr.Bdmng, out partQty); MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == matnr.Matnr); string partCname = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartCname.Replace("'", "''"); string partPurchaser = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartPurchaser; SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == matnr.Lifnr); string supplierName = supplierInfo == null ? string.Empty : supplierInfo.SupplierName; ///若不存在则insert,再以工厂③、物料号①、供应商②更新其对应日期的物料数量 @string.AppendFormat(@" if not exists (select 1 from [LES].[TT_ATP_SUPPLY_PLAN] with(nolock) where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}' and [VALID_FLAG] = 1) begin insert into [LES].[TT_ATP_SUPPLY_PLAN] ([FID],[PART_NO],[PART_CNAME],[PART_PURCHASER],[SUPPLIER_NUM],[SUPPLIER_NAME],[PLANT],[VALID_FLAG],[CREATE_USER],[CREATE_DATE]) values (NEWID(),N'{0}',N'{6}',N'{7}',N'{1}',N'{8}',N'{2}',1,N'{5}',GETDATE()); end update [LES].[TT_ATP_SUPPLY_PLAN] set [PART_CNAME] = N'{6}',[PART_PURCHASER] = N'{7}',[SUPPLIER_NAME] = N'{8}',[{3}] = ISNULL([{3}] , 0) + {4},[MODIFY_USER] = N'{5}',[MODIFY_DATE] = GETDATE() where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}';" , matnr.Matnr, matnr.Lifnr, plant, materialRequireDate, partQty, loginUser, partCname, partPurchaser, supplierName); } #endregion } #region 生产订单 /// pullOrdersInfo = PullOrdersBLL.CreatePullOrdersInfo(loginUser); /// PullOrdersBLL.GetPullOrdersInfo(sapProductOrderInfo, ref pullOrdersInfo); ///WERK,接口_工厂 pullOrdersInfo.Werk = plant; ///ORDER_DATE,订单日期 pullOrdersInfo.OrderDate = BLL.LES.CommonBLL.TryParseDatetime(sapProductOrderDate); ///ASSEMBLY_LINE,工厂模型_流水线 pullOrdersInfo.AssemblyLine = assemblyLine; ///PLAN_EXECUTE_TIME,计划执行时间 pullOrdersInfo.PlanExecuteTime = BLL.LES.CommonBLL.TryParseDatetime(sapProductOrderDate); /// @string.AppendLine(PullOrdersDAL.GetInsertSql(pullOrdersInfo)); ///并批量插入生产订单物料清单(参见TT_BAS_PULL_ORDER_BOM备注中的对应关系) WmsVmiProductOrderInfo wmsVmiProductOrderInfo = WmsVmiProductOrderBLL.CreateWmsVmiProductOrderInfo(loginUser); /// WmsVmiProductOrderBLL.GetWmsVmiProductOrderInfo(pullOrdersInfo, ref wmsVmiProductOrderInfo); /// wmsVmiProductOrderInfo.DownLineTime = sapProductOrderInfo.OfflineDate; wmsVmiProductOrderInfo.OnlineTime = sapProductOrderInfo.OnlineDate; wmsVmiProductOrderInfo.LockFlag = sapProductOrderInfo.LockFlag; @string.AppendLine(WmsVmiProductOrderDAL.GetInsertSql(wmsVmiProductOrderInfo)); @string.AppendLine(BLL.LES.CommonBLL.GetCreateOutboundLogSql("VMI", wmsVmiProductOrderInfo.LogFid.GetValueOrDefault(), "LES-WMS-012", wmsVmiProductOrderInfo.OrderNo, loginUser)); foreach (Matnrs matnr in objMatnrs) { decimal partQty = 0; decimal.TryParse(matnr.Bdmng, out partQty); MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == matnr.Matnr); string partCname = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartCname; @string.AppendFormat(@"insert into [LES].[TT_BAS_PULL_ORDER_BOM] ([FID],[ORDERFID],[ZORDNO],[ZKWERK],[ZBOMID],[ZCOMNO],[ZQTY],[ZLOC],[SUPPLIER_NUM],[PLATFORM],[CREATE_USER],[CREATE_DATE],[VALID_FLAG],[ZCOMDS],[ZDATE]) values (NEWID(),N'{0}',N'{1}',N'{2}',N'{3}',N'{4}',{5},N'{6}',N'{7}',N'{8}',N'{9}',GETDATE(),1,N'{10}',N'{11}');" , pullOrdersInfo.Fid.GetValueOrDefault(), sapProductOrderInfo.Aufnr, plant, matnr.Aennr, matnr.Matnr, partQty, matnr.Ebort, matnr.Lifnr, matnr.Platform, loginUser, partCname, sapProductOrderDate); } #endregion #region 计划拉动状态 ///获取已启用的计划零件类的零件类外键① ///并将TT_BAS_PULL_ORDERS的订单外键②写入TT_MPM_PLAN_PULL_CREATE_STATUS ///其中的状态③为10.未生成(20.已生成,在系统代码中创建CREATE_STATUS,还需修改计划拉动单生成逻辑中的对应枚举项) List <PlanPartBoxInfo> planPartBoxInfos = new PlanPartBoxBLL().GetList("[STATUS] = " + (int)BasicDataStatusConstants.Enable + "", string.Empty); /// foreach (PlanPartBoxInfo planPartBoxInfo in planPartBoxInfos) { @string.AppendFormat(@"insert into [LES].[TT_MPM_PLAN_PULL_CREATE_STATUS] (FID,PART_BOX_FID,ORDER_FID,STATUS,CREATE_USER,CREATE_DATE,VALID_FLAG) values (NEWID(),N'{0}',N'{1}',{2},'{3}',GETDATE(),1);" , planPartBoxInfo.Fid.GetValueOrDefault(), pullOrdersInfo.Fid.GetValueOrDefault(), (int)CreateStatusConstants.NotGenerated, loginUser); } #endregion ///同时更新SAP生产订单物料清单处理状态⑮为20.已处理 @string.AppendFormat(@"update [LES].[TI_IFM_SAP_PRODUCT_ORDER_BOM] set PROCESS_FLAG = {0},PROCESS_TIME = GETDATE(),[MODIFY_USER] = N'{1}',[MODIFY_DATE] = GETDATE() where [ID] = {2};" , (int)ProcessFlagConstants.Processed, loginUser, sapProductOrderBomInfo.Id); } #endregion #region 是首次下发 else { if (calculateSupplyPlanFlag.ToLower() == "true") { ///若之前成功获取了生产订单则比对上线日期⑩⑤是否较SAP生产订单有变化 #region 供货计划 ///获取生产订单物料清单 List <PullOrderBomInfo> pullOrderBomInfos = new PullOrderBomBLL().GetList("[ZORDNO] = N'" + pullOrdersInfo.OrderNo + "'", string.Empty); ///SAP订单删除,需要重新计算供货计划 if (sapProductOrderInfo.Zsc.ToUpper() == "X") { ///需要根据生产订单物料清单TT_BAS_PULL_ORDER_BOM和生产订单的订单日期⑤扣减供货计划 foreach (PullOrderBomInfo pullOrderBomInfo in pullOrderBomInfos) { @string.AppendLine("update [LES].[TT_ATP_SUPPLY_PLAN] " + "set [" + materialRequireDate + "] = ISNULL([" + materialRequireDate + "] , 0) - " + pullOrderBomInfo.Zqty.GetValueOrDefault() + "," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [PART_NO] = N'" + pullOrderBomInfo.Zcomno + "' and " + "[SUPPLIER_NUM] = N'" + pullOrderBomInfo.SupplierNum + "' and " + "[PLANT] = N'" + plant + "';"); } } else { ///若日期无变化⑩=⑤处理状态⑮为10.未处理或30.挂起时不需要更新供货计划 if (sapProductOrderInfo.OnlineDate.GetValueOrDefault() == pullOrdersInfo.OrderDate.GetValueOrDefault()) { ///若处理状态⑮为40.逆处理时 if (processFlag == (int)ProcessFlagConstants.ConverseProgress) { ///根据SAP生产订单上线日期⑩及物料清单扣减供货计划 foreach (Matnrs matnr in objMatnrs) { decimal partQty = 0; decimal.TryParse(matnr.Bdmng, out partQty); @string.AppendFormat(@"update [LES].[TT_ATP_SUPPLY_PLAN] set [{3}] = ISNULL([{3}] , 0) - {4},[MODIFY_USER] = N'{5}',[MODIFY_DATE] = GETDATE() where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}';" , matnr.Matnr, matnr.Lifnr, plant, materialRequireDate, partQty, loginUser); } ///再以生产订单⑤日期及物料清单累加供货计划 foreach (PullOrderBomInfo pullOrderBomInfo in pullOrderBomInfos) { MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == pullOrderBomInfo.Zcomno); string partCname = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartCname; string partPurchaser = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartPurchaser; SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == pullOrderBomInfo.SupplierNum); string supplierName = supplierInfo == null ? string.Empty : supplierInfo.SupplierName; @string.AppendFormat(@"update [LES].[TT_ATP_SUPPLY_PLAN] set [PART_CNAME] = N'{6}',[PART_PURCHASER] = N'{7}',[SUPPLIER_NAME] = N'{8}',[{3}] = ISNULL([{3}] , 0) + {4},[MODIFY_USER] = N'{5}',[MODIFY_DATE] = GETDATE() where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}';" , pullOrderBomInfo.Zcomno, pullOrderBomInfo.SupplierNum, plant, materialRequireDate, pullOrderBomInfo.Zqty.GetValueOrDefault(), loginUser, partCname, partPurchaser, supplierName); } } } ///日期有变化⑩<>⑤ else { ///SAP生产订单处理状态⑮为10.未处理或30.挂起时 if (processFlag == (int)ProcessFlagConstants.Untreated || processFlag == (int)ProcessFlagConstants.Suspend) { ///需要根据生产订单物料清单TT_BAS_PULL_ORDER_BOM和生产订单的订单日期⑤扣减供货计划 foreach (PullOrderBomInfo pullOrderBomInfo in pullOrderBomInfos) { @string.AppendFormat(@"update [LES].[TT_ATP_SUPPLY_PLAN] set [{3}] = ISNULL([{3}] , 0) - {4},[MODIFY_USER] = N'{5}',[MODIFY_DATE] = GETDATE() where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}';" , pullOrderBomInfo.Zcomno, pullOrderBomInfo.SupplierNum, plant, materialRequireDate, pullOrderBomInfo.Zqty.GetValueOrDefault(), loginUser); } ///同时根据SAP生产订单上线日期⑩及物料清单累加供货计划 foreach (Matnrs matnr in objMatnrs) { decimal partQty = 0; decimal.TryParse(matnr.Bdmng, out partQty); MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == matnr.Matnr); string partCname = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartCname; string partPurchaser = maintainPartsInfo == null ? string.Empty : maintainPartsInfo.PartPurchaser; SupplierInfo supplierInfo = supplierInfos.FirstOrDefault(d => d.SupplierNum == matnr.Lifnr); string supplierName = supplierInfo == null ? string.Empty : supplierInfo.SupplierName; @string.AppendFormat(@"update [LES].[TT_ATP_SUPPLY_PLAN] set [PART_CNAME] = N'{6}',[PART_PURCHASER] = N'{7}',[SUPPLIER_NAME] = N'{8}',[{3}] = ISNULL([{3}] , 0) + {4},[MODIFY_USER] = N'{5}',[MODIFY_DATE] = GETDATE() where [PART_NO] = N'{0}' and [SUPPLIER_NUM] = N'{1}' and [PLANT] = N'{2}';" , matnr.Matnr, matnr.Lifnr, plant, materialRequireDate, partQty, loginUser, partCname, partPurchaser, supplierName); } } } } #endregion } #region 更新生产订单 ///生产订单删除 if (sapProductOrderInfo.Zsc.ToUpper() == "X") { @string.AppendLine("update [LES].[TT_BAS_PULL_ORDERS] " + "set [VALID_FLAG] = 0," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ID] = " + sapProductOrderInfo.Id + " and " + "[VALID_FLAG] = 1;"); @string.AppendLine("update [LES].[TT_BAS_PULL_ORDER_BOM] " + "set [VALID_FLAG] = 0," + "[MODIFY_USER] = N'" + loginUser + "'," + "[MODIFY_DATE] = GETDATE() " + "where [ZORDNO] = N'" + pullOrdersInfo.OrderNo + "' and " + "[VALID_FLAG] = 1;"); } else { ///更新生产订单时版本号⑨累加,同时更新生产订单内容、以及SAP生产订单处理状态⑮为20.已处理 @string.AppendFormat("update [LES].[TT_BAS_PULL_ORDERS] set " + "[WERK] = N'{1}'," + "[MODEL_YEAR] = N'{2}'," + "[VEHICLE_ORDER] = N'{3}'," + "[ORDER_DATE] = N'{4}'," + "[ASSEMBLY_LINE] = N'{5}'," + "[PART_NO] = N'{6}'," + "[VERSION] = ISNULL([VERSION],0) + 1," + "[MODIFY_USER] = N'{7}'," + "[MODIFY_DATE] = GETDATE()," + "[PLAN_EXECUTE_TIME] = N'{8}' " + "where [ID] = {0};", pullOrdersInfo.Id, plant, sapProductOrderInfo.CarColor, sapProductOrderInfo.OnlineSeq, sapProductOrderDate, assemblyLine, sapProductOrderInfo.Matnr, loginUser, sapProductOrderDate); } ///TODO:在下发给WMS时也需要提供生产订单删除的逻辑标识 WmsVmiProductOrderInfo wmsVmiProductOrderInfo = WmsVmiProductOrderBLL.CreateWmsVmiProductOrderInfo(loginUser); /// PullOrdersBLL.GetPullOrdersInfo(sapProductOrderInfo, ref pullOrdersInfo); ///WERK,接口_工厂 pullOrdersInfo.Werk = plant; ///ORDER_DATE,订单日期 pullOrdersInfo.OrderDate = BLL.LES.CommonBLL.TryParseDatetime(sapProductOrderDate); ///ASSEMBLY_LINE,工厂模型_流水线 pullOrdersInfo.AssemblyLine = assemblyLine; ///PLAN_EXECUTE_TIME,计划执行时间 pullOrdersInfo.PlanExecuteTime = BLL.LES.CommonBLL.TryParseDatetime(sapProductOrderDate); ///VERSION,版本号 pullOrdersInfo.Version = pullOrdersInfo.Version.GetValueOrDefault() + 1; /// WmsVmiProductOrderBLL.GetWmsVmiProductOrderInfo(pullOrdersInfo, ref wmsVmiProductOrderInfo); /// wmsVmiProductOrderInfo.DownLineTime = sapProductOrderInfo.OfflineDate; wmsVmiProductOrderInfo.OnlineTime = sapProductOrderInfo.OnlineDate; wmsVmiProductOrderInfo.LockFlag = sapProductOrderInfo.LockFlag; @string.AppendLine(WmsVmiProductOrderDAL.GetInsertSql(wmsVmiProductOrderInfo)); @string.AppendLine(BLL.LES.CommonBLL.GetCreateOutboundLogSql("VMI", wmsVmiProductOrderInfo.LogFid.GetValueOrDefault(), "LES-WMS-012", wmsVmiProductOrderInfo.OrderNo, loginUser)); #endregion } #endregion #region 更新SAP生产订单 @string.AppendFormat(@"update [LES].[TI_IFM_SAP_PRODUCT_ORDER] set PROCESS_FLAG = {0},PROCESS_TIME = GETDATE(),[MODIFY_USER] = N'{1}',[MODIFY_DATE] = GETDATE() where [ID] = {2};" , (int)ProcessFlagConstants.Processed, loginUser, sapProductOrderInfo.Id); #endregion #region 数据库语句执行 using (TransactionScope trans = new TransactionScope()) { if (@string.Length > 0) { BLL.LES.CommonBLL.ExecuteNonQueryBySql(@string.ToString()); } trans.Complete(); } ///这个很重要 @string.Clear(); #endregion ///订单计算用时 TimeSpan ts = new TimeSpan(); ts = DateTime.Now - startExecuteTime; Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + ":End " + sapProductOrderInfo.Aufnr + "," + ts.TotalSeconds + "s"); } }
public void Handler() { ///获取获取车辆过点扫描数据中状态为10.待处理的数据 List <MesVehiclePointScanInfo> mesVehiclePointScanInfos = new MesVehiclePointScanBLL().GetList("[PROCESS_FLAG]=" + (int)ProcessFlagConstants.Untreated + "", string.Empty); if (mesVehiclePointScanInfos.Count == 0) { return; } ///MES工厂集合 List <PlantInfo> plantInfos = new PlantBLL().GetList("and [SAP_PLANT_CODE] in ('" + string.Join("','", mesVehiclePointScanInfos.Select(d => d.Enterprise).ToArray()) + "')", string.Empty); ///扫描点数据集合 List <ScanPointInfo> scanPointInfos = new ScanPointBLL().GetList("and [PLANT] in ('" + string.Join("','", plantInfos.Select(d => d.Plant).ToArray()) + "')" + " and [MES_SCAN_POINT_CODE] in ('" + string.Join("','", mesVehiclePointScanInfos.Select(d => d.UnitNo).ToArray()) + "')", string.Empty); if (scanPointInfos.Count == 0) { return; } ///状态点集合 List <StatusPointInfo> statusPointInfos = new StatusPointBLL().GetList(string.Empty, string.Empty); if (statusPointInfos.Count == 0) { return; } ///生产订单集合 List <PullOrdersInfo> pullOrdersInfos = new PullOrdersBLL().GetList("and [ORDER_NO] in ('" + string.Join("','", mesVehiclePointScanInfos.Select(d => d.DmsNo).ToArray()) + "')", string.Empty); if (pullOrdersInfos.Count == 0) { return; } ///在系统配置中增加LES_VEHICLE_SEQ_STEP,默认为100,需要根据可在线车辆数量计算出这个值进行设定,在100%保障的情况下该值应被设定为最大可同时在线车辆数量 string lesVehicleSeqStep = new ConfigBLL().GetValueByCode("LES_VEHICLE_SEQ_STEP"); if (!int.TryParse(lesVehicleSeqStep, out int intLesVehicleSeqStep)) { intLesVehicleSeqStep = 100; } ///逐条进行处理 foreach (MesVehiclePointScanInfo mesVehiclePointScanInfo in mesVehiclePointScanInfos.OrderBy(d => d.Id).ToList()) { ///sql语句 StringBuilder stringBuilder = new StringBuilder(); ///生产订单 PullOrdersInfo pullOrdersInfo = pullOrdersInfos.Where(d => d.OrderNo == mesVehiclePointScanInfo.DmsNo).FirstOrDefault(); if (pullOrdersInfo == null) { continue; } ///逐条处理车辆过点信息 stringBuilder.AppendFormat(VehicleCrossingScanSql(mesVehiclePointScanInfo, scanPointInfos, statusPointInfos, intLesVehicleSeqStep, pullOrdersInfo, plantInfos)); ///执行 if (stringBuilder.Length > 0) { stringBuilder.AppendFormat(@"update [LES].[TI_IFM_MES_VEHICLE_POINT_SCAN] set [PROCESS_FLAG]=" + (int)ProcessFlagConstants.Processed + ",[PROCESS_TIME]=GETDATE(),[MODIFY_USER]='" + loginUser + "',[MODIFY_DATE]=GETDATE()"); using (TransactionScope trans = new TransactionScope()) { BLL.LES.CommonBLL.ExecuteNonQueryBySql(stringBuilder.ToString()); trans.Complete(); } } } }