MES.git - Gitblit

			@@ -108,6 +108,11 @@
			{
			COMPLETE();
			}
			//齐套分析
			if (e.BarItemKey.ToUpper() == "TBCOMPLETEBOM")
			{
			COMPLETEBOM();
			}
			//tbXnqt虚拟齐套
			if (e.BarItemKey.ToUpper() == "TBXNQT")
			{
			@@ -239,6 +244,9 @@
			}
			else
			{
			var type = this.View.Model.GetValue("FQTTYPE");
			if(type =="标准Bom")
			formPa.CustomParams.Add("tempName", "JIT_MOMaterReadysBomBill");
			//齐套分析明细列表跳转
			formPa.FormId = "paez_CompleteAnalysisDetail";
			formPa.CustomParams.Add("FHICMOInterID", Convert.ToString(this.View.Model.GetValue("FDayPlanWorkID", e.Row).ToString()));
			@@ -655,6 +663,53 @@
			},
			(t) => { });
			}
			private void COMPLETEBOM()
			{
			// 显示一个进度显示界面：显示一个不停滚动的模拟进度
			// bUseTruePro参数：是否显示真实的进度。
			// bUseTruePro = false ：
			// 显示一个不停滚动的模拟进度，与实际处理进度没有关联。
			// 此方案优点：实际处理代码无需计算进度
			// 此方案缺点：进度不准确，且进度页面不会自动关闭。
			// bUseTruePro = true: 进度界面显示真实进度
			// 此方案优点：进度真实
			// 此方案缺点：需要在处理代码中，不断的更新真实进度，更新语句
			// this.View.Session["ProcessRateValue"] = 100;
			// 特别说明，当进度更新到100时，进度界面会自动关闭
			// 本案例选用此方案
			var processForm = this.View.ShowProcessForm(
			new Action<FormResult>(t => { }),
			true,
			"正在生成，请稍候...");

			// 开启一个异步线程，处理引入功能
			// using Kingdee.BOS.KDThread;
			MainWorker.QuequeTask(() =>
			{
			var resuult = "";
			try
			{
			// 需要捕获错误，以确保处理结束时，关闭进度滚动界面
			// 引入功能实际处理函数
			resuult = this.CompleteSetAnalysisByDataTableBom();
			}
			finally
			{
			// 确保标记进度已经到达100%
			this.View.Session["ProcessRateValue"] = 100;
			// 引入完毕，关闭进度显示页面
			var processView = this.View.GetView(processForm.PageId);
			if (processView != null)
			{
			processView.Close();
			this.View.SendDynamicFormAction(processView);
			this.View.ShowMessage(resuult);
			}
			}
			},
			(t) => { });
			}

			/// <summary>
			/// 齐套分析
			/// </summary>
			@@ -1373,6 +1428,7 @@

			}
			}
			//生产用料清单齐套分析
			public string CompleteSetAnalysisByDataTable()
			{
			LogHelper.Info("齐套分析sql模式开始准备数据：");
			@@ -1611,6 +1667,7 @@
			dr["FCOMPLETECOUNT1"] = item.FCompleteCount1;
			dr["FERPCLSID"] = item.FErpClsID;
			insertDT.Rows.Add(dr);
			//Thread.Sleep(6000);
			this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(80) / completeAnalysisTempModel.Count) * jdtmmm);
			jdtmmm++;
			}
			@@ -1636,6 +1693,272 @@

			}
			}
			//物料用料清单齐套分析
			public string CompleteSetAnalysisByDataTableBom()
			{
			LogHelper.Info("齐套分析(bom)sql模式开始准备数据：");
			string sql = "";
			try
			{
			Stopwatch sw = new Stopwatch();
			sw.Start();//开始计时
			int maxFid = DBServiceHelper.ExecuteScalar<int>(Context, "select max(FID)FID FROM JIT_MOMaterReadysBomBill", 0);
			//清空齐套临时表
			DBServiceHelper.Execute(Context, "/dialect/ delete JIT_MOMaterReadysBomBill ");
			//更新状态
			DBServiceHelper.Execute(Context, "/dialect/ update Sc_WorkBillSortBillsub set FCOMPLETEBOM='' ");
			//获取当前即时库存按照物料+库存组织+货主+库存数量
			DataSet ds = DBServiceHelper.ExecuteDataSet(Context, @"
			/dialect/
			select isnull(a.FBASEQTY,0)-ISNULL(b.FHUseQty,0) FBASEQTY,T1.FStockOrgId,T1.FMATERIALID,a.FOWNERID,c.FNUMBER from
			(
			select a.FHPRDORGID FStockOrgId,T2.FMATERIALID from SC_WORKBILLSORTBILLMAIN a
			join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
			join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
			LEFT JOIN(select FID,FMATERIALID from T_PRD_PPBOMENTRY
			group by FMATERIALID,FID) T2 on T1.FID = t2.FID
			group by a.FHPRDORGID,T2.FMATERIALID
			) T1
			LEFT JOIN T_STK_Inventory a on T1.FStockOrgId = a.FStockOrgId and T1.FMATERIALID =a.FMATERIALID
			LEFT JOIN JIT_MOMaterReadysBomBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID and a.FOwnerId = b.FOwnerId
			LEFT join T_ORG_Organizations c on a.FOWNERID = c.FORGID
			join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
			where d.FAvailableComplete<>0
			--where T1.FMATERIALID in (105773)
			");
			ListInventory = ds.ToModelList<PRD_Inventory>();
			//总库存物料+库存组织+库存数量
			ds = DBServiceHelper.ExecuteDataSet(Context, @"
			/dialect/
			select sum(isnull(a.FBASEQTY,0)-ISNULL(b.FHUseQty,0))FBASEQTY,T1.FStockOrgId,T1.FMATERIALID from
			(
			select a.FHPRDORGID FStockOrgId,T2.FMATERIALID from SC_WORKBILLSORTBILLMAIN a
			join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
			join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
			LEFT JOIN(select FID,FMATERIALID from T_PRD_PPBOMENTRY
			group by FMATERIALID,FID) T2 on T1.FID = t2.FID
			group by a.FHPRDORGID,T2.FMATERIALID
			) T1
			LEFT JOIN T_STK_Inventory a on T1.FStockOrgId = a.FStockOrgId and T1.FMATERIALID =a.FMATERIALID
			LEFT JOIN JIT_MOMaterReadysBomBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID
			join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
			where d.FAvailableComplete<>0
			--where T1.FMATERIALID in (105773)
			group by T1.FStockOrgId,T1.FMATERIALID
			");
			ListInventoryByStockOrgId = ds.ToModelList<PRD_Inventory>();
			//需要齐套分析(bom)的数据
			sql = @"
			/dialect/
			select a.FPRDMOMAINID,a.FPRDMOENTYID,a.FID,b.FEntryID,a.FHPRDORGID FStockOrgId,t5.FNUMBER as OrgFnumber,a.FHOrderLev,b.FHQTY,b.FHMASTERDATE, a.FSCORDERNO as FMOBillNO,
			T2.FMATERIALID FMATERIALID2, T4.FNumber,t2.FNUMERATOR/t2.FDENOMINATOR dwyl,(FHQTY-ISNULL(FProductNum,0))*(t2.FNUMERATOR/t2.FDENOMINATOR ) PlanCount,TBase.FErpClsID
			from SC_WORKBILLSORTBILLMAIN a
			join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
			--join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
			LEFT JOIN T_ENG_BOMCHILD T2 on a.FBOMID = t2.FID
			--LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID
			JOIN T_BD_MATERIAL T4 on T2.FMATERIALID = T4.FMATERIALID
			JOIN t_BD_MaterialBase TBase on T2.FMATERIALID = TBase.FMATERIALID
			JOIN T_ORG_Organizations t5 on a.FHPRDORGID = t5.FORGID
			--条件为今天往后的日计划 + 今天之前未关闭的日计划
			where (FHEntryCloseDate is null and DATEDIFF(D,GETDATE(),FHMASTERDATE)<0)or DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0
			and b.FHQTY>0
			ORDER BY b.FHMASTERDATE,a.FHOrderLev
			";
			ds = DBServiceHelper.ExecuteDataSet(Context, sql);
			List<PRD_PPBOM> BomList = ds.ToModelList<PRD_PPBOM>();
			//日计划+生产订单子表编码ID
			var DatePlanList = BomList.GroupBy(p => new { p.FHMASTERDATE, p.FPRDMOENTYID }).Select(x => new PRD_PPBOM { FHMASTERDATE = x.Key.FHMASTERDATE, FPRDMOENTYID = x.Key.FPRDMOENTYID }).ToList();
			//CompleteAnalysisTempModel 用于存储临时需要插入数据
			List<CompleteAnalysisTempModel> completeAnalysisTempModel = new List<CompleteAnalysisTempModel>();
			int i = 1;
			//创建字典用于储存物料 + 占用数量
			Dictionary<string, decimal> occupyDic = new Dictionary<string, decimal>();
			LogHelper.Info("齐套分析(bom)准备数据完成：" + sw.Elapsed);
			foreach (var item in DatePlanList)
			{
			//筛选出当日计划数据
			var _BomList = BomList.Where(x => x.FHMASTERDATE == item.FHMASTERDATE && x.FPRDMOENTYID == item.FPRDMOENTYID).ToList();
			foreach (var _item in _BomList)
			{
			//该日计划物料需要数
			decimal need = _item.PlanCount;
			var sjkcList = ListInventoryByStockOrgId.Where(x => x.FMATERIALID == _item.FMATERIALID2 && x.FStockOrgId == _item.FStockOrgId).FirstOrDefault();
			//真正需要取计算的数量（拆分货主）
			//货主匹配
			List<PRD_Inventory> _ListInventory = ListInventory.Where(c => c.FMATERIALID == _item.FMATERIALID2 && c.FStockOrgId == _item.FStockOrgId && c.FBASEQTY > 0).ToList();
			if (_ListInventory.Count > 0)
			{
			decimal _fLackCount = _item.PlanCount > sjkcList.FBASEQTY ? (_item.PlanCount - sjkcList.FBASEQTY) : 0;//缺料数量
			foreach (var Inventory in _ListInventory)
			{
			if (need == 0)
			break;
			decimal fCompleteCount = need >= Inventory.FBASEQTY ? Inventory.FBASEQTY : need;//齐套数量
			decimal occupyMaterial = 0;
			if (!occupyDic.ContainsKey(_item.FMATERIALID2))
			{
			occupyDic.Add(_item.FMATERIALID2, fCompleteCount);
			}
			else
			{
			occupyMaterial = occupyDic[_item.FMATERIALID2];
			occupyDic[_item.FMATERIALID2] = occupyMaterial + fCompleteCount;
			}
			completeAnalysisTempModel.Add(new Demo.Model.Model.CompleteAnalysisTempModel
			{
			FHMainICMOInterIDr = _item.FPRDMOMAINID,
			FHMainICMOEntryID = _item.FPRDMOENTYID,
			FHICMOInterID = _item.FID.ToString(),
			FHICMOEntryID = _item.FEntryID.ToString(),
			FHMaterID = _item.FMATERIALID2,
			FHStockOrgID = _item.FStockOrgId,
			FHPRDORGID = _item.FStockOrgId,
			FErpClsID = _item.FErpClsID,
			FPRDBillNo = _item.FMOBillNO,
			FOwnerTypeId = "BD_OwnerOrg",
			FOwnerId = Inventory.FOwnerId,
			FUnitDosage = _item.dwyl.ToString(),//单位用量
			FSumPlanCount = _item.PlanCount.ToString(),//计划总数量
			FCompleteCount1 = fCompleteCount, //齐套数量
			FLackCount = _fLackCount,//缺料数量
			FHStockQty = sjkcList.FBASEQTY,//即时库存
			FHLeftQty = Inventory.FBASEQTY,//可用数量
			FOccupyCount = occupyMaterial,//占用数量
			/* 2021 1.26 更改为need
			FComPlete = sjkcList.FBASEQTY >= _item.PlanCount ? "齐套" : "未齐套",
			*/
			FComPlete = sjkcList.FBASEQTY >= need ? "齐套" : "未齐套",
			FPlanDate = item.FHMASTERDATE // 日计划日期
			});
			//扣减总库存数量
			sjkcList.FBASEQTY = sjkcList.FBASEQTY >= fCompleteCount ? sjkcList.FBASEQTY - fCompleteCount : 0;
			//剩余日计划需求数量
			need = need >= Inventory.FBASEQTY ? need - Inventory.FBASEQTY : 0;
			//扣减消耗库存
			Inventory.FBASEQTY = Inventory.FBASEQTY - fCompleteCount;
			}
			}
			else
			{
			decimal occupyMaterial = 0;
			if (occupyDic.ContainsKey(_item.FMATERIALID2))
			{
			occupyMaterial = occupyDic[_item.FMATERIALID2];
			}
			completeAnalysisTempModel.Add(new Demo.Model.Model.CompleteAnalysisTempModel
			{
			FHMainICMOInterIDr = _item.FPRDMOMAINID,
			FHMainICMOEntryID = _item.FPRDMOENTYID,
			FHICMOInterID = _item.FID.ToString(),
			FHICMOEntryID = _item.FEntryID.ToString(),
			FHMaterID = _item.FMATERIALID2,
			FErpClsID = _item.FErpClsID,
			FHStockOrgID = _item.FStockOrgId,
			FHPRDORGID = _item.FStockOrgId,
			FPRDBillNo = _item.FMOBillNO,
			FOwnerTypeId = "BD_OwnerOrg",
			FUnitDosage = _item.dwyl.ToString(),//单位用量
			FSumPlanCount = _item.PlanCount.ToString(),//计划总数量
			FCompleteCount1 = 0, //齐套数量
			FLackCount = _item.PlanCount,//缺料数量
			FHStockQty = 0,//即时库存
			FHLeftQty = 0,//可用数量
			FOccupyCount = occupyMaterial,//占用数量
			FComPlete = "未齐套",
			FPlanDate = item.FHMASTERDATE // 日计划日期
			});
			}
			}
			//Thread.Sleep(100);
			// this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(40) / DatePlanList.Count) * i);
			i++;

			}
			LogHelper.Info($"齐套分析(bom)，数据准备完成，保存到Model实体，总行数：{completeAnalysisTempModel.Count}，耗时：" + sw.Elapsed);
			List<string> sqlList = new List<string>();
			DataTable insertDT = new DataTable();
			insertDT.TableName = "JIT_MOMaterReadysBomBill";
			insertDT.Columns.Add("FID", typeof(long));
			//insertDT.Columns.Add("FBILLNO", typeof(string));
			insertDT.Columns.Add("FDOCUMENTSTATUS", typeof(string));
			insertDT.Columns.Add("FHMAINICMOINTERIDR", typeof(long));
			insertDT.Columns.Add("FHMAINICMOENTRYID", typeof(long));
			insertDT.Columns.Add("FHICMOINTERID", typeof(long));
			insertDT.Columns.Add("FHICMOENTRYID", typeof(long));
			insertDT.Columns.Add("FHMATERID", typeof(long));
			insertDT.Columns.Add("FHSTOCKORGID", typeof(long));
			insertDT.Columns.Add("FHSTOCKQTY", typeof(decimal));
			insertDT.Columns.Add("FHLEFTQTY", typeof(decimal));
			insertDT.Columns.Add("FHPRDORGID", typeof(long));
			insertDT.Columns.Add("FUNITDOSAGE", typeof(decimal));
			insertDT.Columns.Add("FSUMPLANCOUNT", typeof(decimal));
			insertDT.Columns.Add("FPRDBILLNO", typeof(string));
			insertDT.Columns.Add("FOWNERID", typeof(long));
			insertDT.Columns.Add("FOWNERTYPEID", typeof(string));
			//insertDT.Columns.Add("FIDENTIFICAT", typeof(long));
			insertDT.Columns.Add("FPLANDATE", typeof(DateTime));
			insertDT.Columns.Add("FCOMPLETE", typeof(string));
			insertDT.Columns.Add("FLACKCOUNT", typeof(decimal));
			insertDT.Columns.Add("FOCCUPYCOUNT", typeof(decimal));
			insertDT.Columns.Add("FCOMPLETECOUNT1", typeof(decimal));
			insertDT.Columns.Add("FERPCLSID", typeof(string));
			int jdtmmm = 1;
			foreach (var item in completeAnalysisTempModel)
			{
			DataRow dr = insertDT.NewRow();
			dr["FID"] = maxFid + jdtmmm;
			//dr["FBILLNO"] = "";
			dr["FDOCUMENTSTATUS"] = "A";
			dr["FHMAINICMOINTERIDR"] = item.FHMainICMOInterIDr;
			dr["FHMAINICMOENTRYID"] = item.FHMainICMOEntryID;
			dr["FHICMOINTERID"] = item.FHICMOInterID;
			dr["FHICMOENTRYID"] = item.FHICMOEntryID;
			dr["FHMATERID"] = item.FHMaterID;
			dr["FHSTOCKORGID"] = item.FHStockOrgID;
			dr["FHSTOCKQTY"] = item.FHStockQty;
			dr["FHLEFTQTY"] = item.FHLeftQty;
			dr["FHPRDORGID"] = item.FHPRDORGID;
			dr["FUNITDOSAGE"] = item.FUnitDosage;
			dr["FSUMPLANCOUNT"] = item.FSumPlanCount;
			dr["FPRDBILLNO"] = item.FPRDBillNo;
			dr["FOWNERID"] = item.FOwnerId == null ? "0" : item.FOwnerId;
			dr["FOWNERTYPEID"] = "BD_OwnerOrg";
			//dr["FIDENTIFICAT"] = "";
			dr["FPLANDATE"] = item.FPlanDate.ToString("yyyy-MM-dd");
			dr["FCOMPLETE"] = item.FComPlete;
			dr["FLACKCOUNT"] = item.FLackCount;
			dr["FOCCUPYCOUNT"] = item.FOccupyCount;
			dr["FCOMPLETECOUNT1"] = item.FCompleteCount1;
			dr["FERPCLSID"] = item.FErpClsID;
			insertDT.Rows.Add(dr);
			//Thread.Sleep(6000);
			this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(80) / completeAnalysisTempModel.Count) * jdtmmm);
			jdtmmm++;
			}
			insertDT.EndLoadData(); // 灌入数据结束
			// 批量插入到数据库
			DBServiceHelper.BulkInserts(this.Context, string.Empty, string.Empty, insertDT);
			//DBServiceHelper.ExecuteBatch(Context, sqlList);
			//执行完成后更新日计划工单状态
			DBServiceHelper.Execute(Context, @"
			/dialect/update Sc_WorkBillSortBillSub set FCompletebom = '未齐套' where FEntryID IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBomBill where isnull(FCOMPLETE,'未齐套') ='未齐套')
			");
			DBServiceHelper.Execute(Context, @"
			/dialect/update Sc_WorkBillSortBillSub set FCompletebom = '齐套' where FEntryID NOT IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBomBill where isnull(FCOMPLETE,'未齐套') ='未齐套')
			");
			sw.Stop();//结束计时
			LogHelper.Info("齐套分析(bom)运行总时长：" + sw.Elapsed);
			return "操作成功";
			}
			catch (Exception ex)
			{
			LogHelper.Error("齐套分析(bom)跳出：" + ex.Message.ToString());
			return "操作失败，" + ex.Message.ToString();

			}
			}


			/// <summary>
			/// 虚拟齐套