using Demo.Model.Model; using Demo.Model.Model.PODemandPlan; using K3Cloud.Extend.Utils; using Kingdee.BOS.App.Data; using Kingdee.BOS.Core; using Kingdee.BOS.Core.Bill.PlugIn; using Kingdee.BOS.Core.Const; using Kingdee.BOS.Core.DynamicForm; using Kingdee.BOS.Core.DynamicForm.PlugIn; using Kingdee.BOS.Core.DynamicForm.PlugIn.Args; using Kingdee.BOS.Core.DynamicForm.PlugIn.ControlModel; using Kingdee.BOS.Core.Metadata; using Kingdee.BOS.Core.Metadata.EntityElement; using Kingdee.BOS.JSON; using Kingdee.BOS.KDThread; using Kingdee.BOS.Orm.DataEntity; using Kingdee.BOS.ServiceHelper; using Kingdee.BOS.ServiceHelper.FileServer; using Kingdee.BOS.Util; using Kingdee.K3.Core.SCM.STK; using Kingdee.K3.SCM.ServiceHelper; using Newtonsoft.Json.Linq; using NPOI.HSSF.UserModel; using NPOI.SS.Formula.Functions; using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Diagnostics; using System.IO; using System.Linq; using System.Runtime.CompilerServices; using System.Runtime.InteropServices; using System.Text; using System.Threading; using System.Threading.Tasks; using System.Web; using System.Windows.Documents; using ZD.Cloud.Logger; using ZD.Cloud.WebApi; using ZD.Share.Common; namespace Demo.DynamicForm.PRD { [Description("生产计划平台按钮功能")] [HotUpdate] public class Pro_WorkBillPlatform : AbstractDynamicFormPlugIn { string _OutServicePath = ""; string PathName = "日计划导入模板.xlsx"; List ListInventory;//库存明细 List ListInventoryByStockOrgId;//物料总库存 public override void BarItemClick(BarItemClickEventArgs e) { base.BarItemClick(e); try { if (e.BarItemKey.ToUpper() == "BTEXPORT") { ExportExcel(); string path = PathUtils.GetServerPath(KeyConst.TEMPFILEPATH, PathName); DynamicFormShowParameter dynamicForm = new DynamicFormShowParameter(); dynamicForm.FormId = "BOS_FileDownLoad"; dynamicForm.OpenStyle.ShowType = ShowType.Modal; dynamicForm.CustomParams.Add("IsExportData", "true"); dynamicForm.CustomParams.Add("url", path); this.View.ShowForm(dynamicForm); } if (e.BarItemKey.ToUpper() == "TBSEARCH") { SearchList(); } if (e.BarItemKey.ToUpper() == "TBLOCK") { Lock(); } //解锁 if (e.BarItemKey.ToUpper() == "TBRELIEVELOCK") { RelieveLock(); } if (e.BarItemKey.ToUpper() == "BTNSAVE") { Save(); } if (e.BarItemKey.ToUpper() == "TBCOMPLETE") { //CompleteSetAnalysis(); //COMPLETEMAIN(); COMPLETE(); SearchList(); } //提料计划 if (e.BarItemKey.ToUpper() == "TBEXTRACTION") { try { COMPLETE(); DayPlanPPBomBill(); Extraction(); } catch (Exception ex) { this.View.ShowErrMessage(ex.Message.ToString()); } finally { this.View.ShowMessage("操作成功！"); } } //预留 if (e.BarItemKey.ToUpper() == "TBRESERVE") { Reserve(); } //释放 if (e.BarItemKey.ToUpper() == "TBRELEASE") { Release(); } } catch (Exception ex) { this.View.ShowErrMessage(ex.Message.ToString()); } } public override void EntryBarItemClick(BarItemClickEventArgs e) { base.EntryBarItemClick(e); if (e.BarItemKey.ToUpper() == "TBEDIT") { SearchListEdit(); } } /// /// 行双击 跳出齐套分析明细列表 /// /// public override void EntityRowDoubleClick(Kingdee.BOS.Core.DynamicForm.PlugIn.Args.EntityRowClickEventArgs e) { base.EntityRowClick(e); DynamicFormShowParameter formPa = new DynamicFormShowParameter(); if (e.ColKey == "FDELAYLNUM") { //生产计划平台拖期明细跳转 formPa.FormId = "paez_TardinessDetails"; formPa.CustomParams.Add("FID", Convert.ToString(this.View.Model.GetValue("FDayPlanWorkID", e.Row).ToString())); } else { //齐套分析明细列表跳转 formPa.FormId = "paez_CompleteAnalysisDetail"; formPa.CustomParams.Add("FHMainICMOInterID", Convert.ToString(this.View.Model.GetValue("FHMainSourceInterID", e.Row).ToString())); formPa.CustomParams.Add("FHMainICMOEntryID", Convert.ToString(this.View.Model.GetValue("FHMainSourceEntryID", e.Row).ToString())); formPa.CustomParams.Add("FHICMOInterID", Convert.ToString(this.View.Model.GetValue("FDayPlanWorkID", e.Row).ToString())); formPa.CustomParams.Add("FMatrailId_Id", Convert.ToString((this.View.Model.GetValue("FMatrailId", e.Row) as DynamicObject)["Id"].ToString())); } this.View.ShowForm(formPa); } /// /// 导出数据到Excel /// public void ExportExcel() { string[] Title = new string[] {"销售订单号", "生产订单号", "生产组织", "生产车间", "生产资源", "产品代码", "产品名称", "规格型号", "计量单位", "批次号","销售订单数量","生产订单数量","总齐套数量","交货日期","订单等级","T0","T1","T2","T3","T4","T5","T6","T7", "T8","T9","T10","T11","T12","T13","T14","T15","T16","T17", "T18","T19","T20","T21","T22","T23","T24","T25","T26","T27","T28","T29", "T30","T31","T32","T33","T34","T35","T36","T37","T38","T39","T40","T41","T42", "T43","T44","T45","T46","T47","T48","T49","T50","T51","T52","T53","T54","T55","T56","T57","T58","T59","T60"}; string sheetname = "日计划导入模板.xlsx"; string file = ""; file = @"D:\办公\K3Cloud\WebSite\TempfilePath\" + sheetname; //@"D:\办公\K3Cloud\WebSite\"; //通过Directory类的CreateDirectory方法创建。 string activeDir = AppDomain.CurrentDomain.BaseDirectory; string newPath = System.IO.Path.Combine(activeDir, "TempfilePath"); System.IO.Directory.CreateDirectory(newPath); file = newPath + "\\" + sheetname; //Server.MapPath("/"); DataTable dt = null; HSSFWorkbook xssfworkbook = new HSSFWorkbook();//建立Excel2003对象 HSSFSheet sheet = (HSSFSheet)xssfworkbook.CreateSheet(sheetname);//新建一个名称为sheetname的工作簿 //设置基本样式 ICellStyle style = xssfworkbook.CreateCellStyle(); style.WrapText = true; IFont font = xssfworkbook.CreateFont(); font.FontHeightInPoints = 9; font.FontName = "Arial"; style.SetFont(font); //设置统计样式 ICellStyle style1 = xssfworkbook.CreateCellStyle(); style1.WrapText = true; IFont font1 = xssfworkbook.CreateFont(); font1.FontHeightInPoints = 9; font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font1.FontName = "Arial"; style1.SetFont(font1); //设置大类样式 ICellStyle style2 = xssfworkbook.CreateCellStyle(); style2.WrapText = true; //style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Gold.Index; //style2.FillPattern = FillPattern.SolidForeground; IFont font2 = xssfworkbook.CreateFont(); font2.FontHeightInPoints = 9; font2.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font2.FontName = "Arial"; style2.SetFont(font2); //设置列名 HSSFRow row = (HSSFRow)sheet.CreateRow(0); for (int i = 0; i < Title.Length; i++) { ICell cell = (ICell)row.CreateCell(i); var rowName = Title[i]; cell.SetCellValue(rowName); cell.CellStyle = style; } //单元格赋值 //列宽自适应，只对英文和数字有效 for (int i = 0; i <= Title.Length; i++) { sheet.AutoSizeColumn(i); } using (System.IO.Stream stream = System.IO.File.OpenWrite(file)) { xssfworkbook.Write(stream); stream.Close(); _OutServicePath = file; } } private string _fileId = string.Empty; /// /// 弹出下载提示窗，下载文件至客户端本地。 /// public void ShowDownload() { _fileId = "2000avbbb"; string url = FileServerHelper.GetAppSiteOuterNetUrl(this.Context, HttpContext.Current.Request); //fileId文件编码可由 T_BAS_ATTACHMENT 附件明细表查得，此处直接拿上面上传文件的编码来做示例。 string fileurl = string.Format("{0}FileUpLoadServices/download.aspx?fileId={1}&token={2}", url, _fileId, this.Context.UserToken); JSONObject jObject = new JSONObject(); jObject.Put("url", HttpUtility.UrlEncode(fileurl)); jObject.Put("title", "文件下载"); jObject.Put("desc", "请点击打开附件："); jObject.Put("urltitle", "这是文件名"); this.View.AddAction(JSAction.openUrlWindow, new JSONArray { jObject }); } /// /// 锁定 /// public void Lock() { int day = Convert.ToInt32(this.Model.GetValue("FLockDays")); List sqlList = new List(); string sql = $"/*dialect*/update t1 set t1.FHLockedSub=1 from Sc_WorkBillSortBillSub t1 join Sc_WorkBillSortBillMain t2 on t1.FID = t2.FID where FHMASTERDATE between DATEADD(day,-1,getdate()) and DATEADD(day,{day - 1},getdate())"; sqlList.Add(sql); int result = DBServiceHelper.ExecuteBatch(Context, sqlList); if (result > 0) { //锁定后生成日计划用料清单 及 提料计划 DayPlanPPBomBill(); Extraction(); SearchList(); this.View.ShowMessage("操作成功"); } else this.View.ShowMessage("操作失败，联系管理员！"); } /// /// 解锁 /// public void RelieveLock() { int day = Convert.ToInt32(this.Model.GetValue("FLockDays")); //获取单据体信息 Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity"); //单据体信息转换为列表集合 DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity); List sqlList = new List(); string sql; foreach (var item in entityDataObjoct) { sql = $"/*dialect*/update t1 set t1.FHLockedSub=0 from Sc_WorkBillSortBillSub t1 join Sc_WorkBillSortBillMain t2 on t1.FID = t2.FID where FHMASTERDATE between DATEADD(day,-1,getdate()) and DATEADD(day,{day - 1},getdate()) and t1.FID = { Convert.ToString(item["FDayPlanWorkID"])} "; sqlList.Add(sql); } int result = DBServiceHelper.ExecuteBatch(Context, sqlList); if (result > 0) { SearchList(); this.View.ShowMessage("操作成功"); } else this.View.ShowMessage("操作失败，联系管理员！"); } /// /// 保存功能 /// public void Save() { List sqlList = new List(); string sql; //获取单据体信息 Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity"); //单据体信息转换为列表集合 DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity); DateTime date = DateTime.Now; string _result = ""; foreach (DynamicObject current in entityDataObjoct) { if (Convert.ToInt32(current["FNoScheduled"]) < 0) _result += "序号：" + Convert.ToString(current["SEQ"]) + ",日计划总数量超出生产订单数量"; } if (_result != "") { this.View.ShowErrMessage(_result); return; } foreach (DynamicObject current in entityDataObjoct) { sql = $"/*dialect*/ update Sc_WorkBillSortBillMain set FOrderQuantity={current["FOrderQuantity"]},FDayPlanQuantity={current["FDayPlanQuantity"]},FNoScheduled={current["FNoScheduled"]} where FID = {current["FDayPlanWorkID"]}"; sqlList.Add(sql); DateTime FBzDate = Convert.ToDateTime(Convert.ToDateTime(current["FBzDate"])); DateTime end = Convert.ToDateTime(Convert.ToDateTime(current["FBzDate"]).AddDays(60).ToShortDateString());//最大日期 for (int i = 0; i <= 60; i++) { DateTime start = Convert.ToDateTime(date.AddDays(i).ToShortDateString()); TimeSpan getDay = end.Subtract(start); //编制日期+61天>date.AddDays(i).ToShortDateString() continue if (getDay.Days < 0) break; sql = $"/*dialect*/update t1 set FHQTY='{Convert.ToString(current["FT" + "" + i + ""])}' from Sc_WorkBillSortBillSub t1 join " + $" Sc_WorkBillSortBillMain t2 on t1.FID = t2.FID where FHMASTERDATE='{date.AddDays(i).ToShortDateString()}' and t2.FID='{Convert.ToString(current["FDayPlanWorkID"])}'"; sqlList.Add(sql); } } int result = DBServiceHelper.ExecuteBatch(Context, sqlList); if (result > 0) { this.View.ShowMessage("操作成功"); SearchList(); } else this.View.ShowMessage("操作失败，联系管理员！"); } /// /// 齐套分析 /// /// private void COMPLETE() { // 显示一个进度显示界面：显示一个不停滚动的模拟进度 // bUseTruePro参数：是否显示真实的进度。 // bUseTruePro = false ： // 显示一个不停滚动的模拟进度，与实际处理进度没有关联。 // 此方案优点：实际处理代码无需计算进度 // 此方案缺点：进度不准确，且进度页面不会自动关闭。 // bUseTruePro = true: 进度界面显示真实进度 // 此方案优点：进度真实 // 此方案缺点：需要在处理代码中，不断的更新真实进度，更新语句 // this.View.Session["ProcessRateValue"] = 100; // 特别说明，当进度更新到100时，进度界面会自动关闭 // 本案例选用此方案 var processForm = this.View.ShowProcessForm( new Action(t => { }), true, "正在生成，请稍候..."); // 开启一个异步线程，处理引入功能 // using Kingdee.BOS.KDThread; MainWorker.QuequeTask(() => { try { // 需要捕获错误，以确保处理结束时，关闭进度滚动界面 // 引入功能实际处理函数 this.CompleteSetAnalysis(); } finally { // 确保标记进度已经到达100% this.View.Session["ProcessRateValue"] = 100; // 引入完毕，关闭进度显示页面 var processView = this.View.GetView(processForm.PageId); if (processView != null) { processView.Close(); this.View.SendDynamicFormAction(processView); } } }, (t) => { }); } public void COMPLETEMAIN() { //获取当前即时库存 按照物料+货主+库存 展现 DataSet ds = DBServiceHelper.ExecuteDataSet(Context, @" /*dialect*/ select DISTINCT (a.FBASEQTY- ISNULL(FHUseQty,0)) as FBASEQTY,a.FMATERIALID,a.FStockOrgId,a.FOwnerId from T_STK_Inventory a left join ( select FHMaterID,FHUseQty,FHStockOrgID,FOwnerId from JIT_MOMaterReadysBill where FIdentificat = 2 ) b on a.FMATERIALID=b.FHMaterID and a.FStockOrgId=b.FHStockOrgID and a.FOwnerId = b.FOwnerId order by FBASEQTY"); ListInventory = ds.ToModelList(); ds = DBServiceHelper.ExecuteDataSet(Context, @" /*dialect*/ select sum(FBASEQTY)FBASEQTY,FMATERIALID,FStockOrgId from ( select DISTINCT (a.FBASEQTY- ISNULL(FHUseQty,0)) as FBASEQTY,a.FMATERIALID,a.FStockOrgId,a.FOwnerId from T_STK_Inventory a left join ( select FHMaterID,FHUseQty,FHStockOrgID,FOwnerId from JIT_MOMaterReadysBill where FIdentificat = 2 ) b on a.FMATERIALID=b.FHMaterID and a.FStockOrgId=b.FHStockOrgID and a.FOwnerId = b.FOwnerId ) TT group by FMATERIALID, FStockOrgId "); ListInventoryByStockOrgId = ds.ToModelList(); //获取单据体信息 Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity"); //单据体信息转换为列表集合 DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity); EntryGrid grid = this.View.GetControl("FEntity"); if (entityDataObjoct.Count > 0) { int m = 0; //删除齐套数量 foreach (DynamicObject current in entityDataObjoct) { //FDayPlanWorkID日计划工单ID去删除现有的齐套分析数据 DBServiceHelper.Execute(Context, "/*dialect*/delete JIT_MOMaterReadysBill where FHICMOInterID = '" + current["FDayPlanWorkID"].ToString() + "'"); } foreach (DynamicObject current in entityDataObjoct) { //计划总数量 decimal SumPlan = 0; for (int i = 0; i <= 60; i++) { SumPlan += Convert.ToDecimal(current["FT" + "" + i + ""]); } double Sum = 100; Sum = Convert.ToDouble(SumGetComplete(current["FMatrailId_Id"].ToString(), current["FHMainSourceInterID"].ToString(), current["FHMainSourceEntryID"].ToString(), current["FDayPlanWorkID"].ToString(), SumPlan, current["FORGID_Id"].ToString())); this.View.Model.SetValue("FCompleteSetCount", Sum, m); this.View.UpdateView("FCompleteSetCount"); //保存齐套数 DBServiceHelper.Execute(Context, "/*dialect*/ update Sc_WorkBillSortBillMain set FCompleteCount='" + Sum + "',FDayPlanQuantity='" + SumPlan + "' where FID=" + current["FDayPlanWorkID"].ToString()); for (int i = 0; i <= 60; i++) { this.View.GetFieldEditor("FT" + i.ToString(), m).Enabled = false; //单据体行锁定字段 Sum = Sum - Convert.ToDouble(current["FT" + "" + i + ""]); if (Sum >= 0) { grid.SetForecolor("FT" + "" + i + "", "#00FF00", m); } else { grid.SetForecolor("FT" + "" + i + "", "#FF0000", m); } } m++; //grid = this.View.GetControl[EntryGrid]("FSaleOrderEntry")#需要设置的单据体标识Key //grid.SetRowBackcolor("#FFFF00", 0)#设置整行背景颜色 //grid.SetForecolor("FMaterialName", "#FF0000", 0)#需要设置字段标识Key[/code] // grid.SetBackcolor("FT" + "" + i + "", "#FF0000", m); //this.View.Session["ProcessRateValue"] = Convert.ToInt32(this.View.Session["ProcessRateValue"]) + Convert.ToInt32(1 / entityDataObjoct.Count * 90); } } else { this.View.ShowMessage("请先加载数据！"); } } /// /// 计算最大齐套数量 /// /// 物料Number /// 生产订单主码 /// 生产订单子码 /// 日计划工单FID /// 计划总数量 /// 库存组织 /// public string SumGetComplete(string FMATERIALID, string FMainInterID, string FMainEntryID, string FDayPlanWorkID, decimal SumPlan, string FStockOrgId) { //FDayPlanWorkID日计划工单ID去删除现有的齐套分析数据 DBServiceHelper.Execute(Context, "/*dialect*/delete JIT_MOMaterReadysBill where FHICMOInterID = '" + FDayPlanWorkID + "'"); #region 数据库直接算出所以需要数据字段 /* --生产用料清单列表 SELECT T1.FID,T1.FBillNo,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty, T2.FMATERIALID FMATERIALID2,T4.FNumber,T2.FNeedQty,t3.FBASEQTY,convert(decimal(18,3),FNeedQty/t1.FQty)UnitDosage, floor(t3.FBASEQTY/convert(decimal(18,3),FNeedQty/t1.FQty)) SumGetComplete FROM T_PRD_PPBOM T1 LEFT JOIN (select FID,MAX(FENTRYID)FENTRYID,SUM(FNeedQty)FNeedQty,FMATERIALID from T_PRD_PPBOMENTRY where FMoId='100007' group by FMATERIALID,FID ) T2 on T1.FID=t2.FID LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID LEFT JOIN T_BD_MATERIAL T4 on T2.FMATERIALID=T4.FMATERIALID where t1.FMoId='100007' and T1.FMaterialID='105771' */ #endregion string sql = string.Format(@" /*dialect*/ --生产用料清单列表 SELECT T1.FID,T1.FBillNo,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty, T2.FMATERIALID FMATERIALID2,T4.FNumber,T2.FNeedQty --,isnull(t3.FBASEQTY,0)FBASEQTY FROM T_PRD_PPBOM T1 LEFT JOIN (select FID,MAX(FENTRYID)FENTRYID,SUM(FNeedQty)FNeedQty,FMATERIALID from T_PRD_PPBOMENTRY where FMoId='{0}' group by FMATERIALID,FID ) T2 on T1.FID=t2.FID --LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID LEFT JOIN T_BD_MATERIAL T4 on T2.FMATERIALID=T4.FMATERIALID where t1.FMoId='{0}' and T1.FMaterialID='{1}' ", FMainInterID, FMATERIALID); DataSet ds = DBServiceHelper.ExecuteDataSet(Context, sql); var PpbomList = ds.ToModelList(); //当前最大齐套数 下层需求数量/父级需求数量=单位用料 //var SumGetComplete = Math.Floor(PpbomList.Min(t => t.FBASEQTY / (t.FNeedQty / t.FQty))); //最大齐套数 decimal SumGetComplete; List list = new List(); foreach (var t in PpbomList) { var ls = ListInventoryByStockOrgId.Where(c => c.FMATERIALID == t.FMATERIALID2 && c.FStockOrgId == FStockOrgId).FirstOrDefault(); if (ls == null) { list.Add(0); continue; } SumGetComplete = Math.Floor(ls.FBASEQTY / (t.FNeedQty / t.FQty)); list.Add(SumGetComplete); } //没有物料的库存信息 SumGetComplete = list.Min(); if (SumGetComplete > SumPlan)//系统可生产最大齐套数大于计划需求量时，取计划 SumGetComplete = SumPlan; foreach (var item in PpbomList) { decimal jskc = 0; var ls = ListInventoryByStockOrgId.Where(c => c.FMATERIALID == item.FMATERIALID2 && c.FStockOrgId == FStockOrgId).FirstOrDefault(); if (ls != null) jskc = ls.FBASEQTY; /* 及时库存数量 = 系统现有库存 可用数量 = 及时库存数量 数量字段 = 齐套量 * 单位用料 */ decimal UnitDosage = item.FNeedQty / item.FQty;//单位用量 decimal xhsl = SumGetComplete * (UnitDosage);///消耗数量 JObject _jsonRoot = new JObject(); _jsonRoot.Add("Creator", ""); _jsonRoot.Add("NeedUpDateFields", new JArray()); _jsonRoot.Add("NeedReturnFields", new JArray()); _jsonRoot.Add("IsDeleteEntry", "true"); _jsonRoot.Add("SubSystemId", ""); _jsonRoot.Add("IsVerifyBaseDataField", ""); JObject _jsonModel = new JObject(); _jsonModel.Add("FHMainICMOInterIDr", FMainInterID);//主生产订单主内码 _jsonModel.Add("FHMainICMOEntryID", FMainEntryID);//主生产订单主内码 _jsonModel.Add("FHICMOInterID", FDayPlanWorkID);//日计划订单主内码 JObject _HMaterID = new JObject(); _HMaterID.Add("FNumber", item.FNumber); _jsonModel.Add("FHMaterID", _HMaterID);//FHMaterID 物料 _jsonModel.Add("FHStockQty", Convert.ToDecimal(jskc));//及时库存数量 _jsonModel.Add("FHUseQty", Convert.ToDecimal(xhsl));//数量 _jsonModel.Add("FHLeftQty", Convert.ToDecimal(jskc));//可用数量 _jsonModel.Add("FUnitDosage", Convert.ToDecimal(UnitDosage));//单位用量 . _jsonModel.Add("FSumPlanCount", Convert.ToDecimal(SumPlan));//FSumPlanCount 计划总数量 _jsonModel.Add("FCompleteCount", Convert.ToDecimal(SumGetComplete));//FCompleteCount 齐套数量 _jsonModel.Add("FPRDBILLNO", item.FMOBillNO);//FPRDBILLNO 工单编号 _jsonModel.Add("FIdentificat", "1");//标识 _jsonRoot.Add("Model", _jsonModel); CloudClient _cloudClient = new CloudClient("http://localhost/K3Cloud/"); var _result = _cloudClient.Save("paez_CompleteAnalysisTemp", _jsonRoot.ToString()); JObject _saveObj = JObject.Parse(_result); string _saveIsSuc = _saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper(); if (_saveIsSuc != "TRUE") { this.View.ShowMessage("操作失败，联系管理员！"); } //扣除总库存的数量 if (ls != null) ls.FBASEQTY -= xhsl; List _ListInventory = ListInventory.Where(c => c.FMATERIALID == item.FMATERIALID2 && c.FStockOrgId == FStockOrgId && c.FBASEQTY > 0).ToList(); foreach (var Inventory in _ListInventory) { //扣除库存数量 // Inventory.FBASEQTY; if (xhsl <= 0) break; // var _ls = ListInventory.Where(c => c.FMATERIALID == item.FMATERIALID2).DefaultIfEmpty(); JObject jsonRoot = new JObject(); jsonRoot.Add("Creator", ""); jsonRoot.Add("NeedUpDateFields", new JArray()); jsonRoot.Add("NeedReturnFields", new JArray()); jsonRoot.Add("IsDeleteEntry", "true"); jsonRoot.Add("SubSystemId", ""); jsonRoot.Add("IsVerifyBaseDataField", ""); JObject jsonModel = new JObject(); jsonModel.Add("FHMainICMOInterIDr", FMainInterID);//主生产订单主内码 jsonModel.Add("FHMainICMOEntryID", FMainEntryID);//主生产订单主内码 jsonModel.Add("FHICMOInterID", FDayPlanWorkID);//日计划订单主内码 JObject HMaterID = new JObject(); HMaterID.Add("FNumber", item.FNumber); jsonModel.Add("FHMaterID", HMaterID);//FHMaterID 物料 DataTable dt = DBServiceHelper.ExecuteDataSet(Context, @" select FNUMBER from T_ORG_Organizations where FORGID='" + FStockOrgId + "'").Tables[0]; JObject FHStockOrgID = new JObject(); FHStockOrgID.Add("FNumber", dt.Rows[0]["FNUMBER"].ToString()); jsonModel.Add("FHStockOrgID", FHStockOrgID);//FHStockOrgID 库存组织 jsonModel.Add("FOwnerTypeId", "BD_OwnerOrg");//FHStockOrgID 货主类型 dt = DBServiceHelper.ExecuteDataSet(Context, @" select FNUMBER from T_ORG_Organizations where FORGID='" + Inventory.FOwnerId + "'").Tables[0]; JObject FOwnerId = new JObject(); FOwnerId.Add("FNumber", dt.Rows[0]["FNUMBER"].ToString()); jsonModel.Add("FOwnerId", FOwnerId);//FOwnerId 货主 jsonModel.Add("FHStockQty", Convert.ToDecimal(Inventory.FBASEQTY));//及时库存数量 jsonModel.Add("FHUseQty", Convert.ToDecimal(xhsl > Inventory.FBASEQTY ? Inventory.FBASEQTY : xhsl));//数量 jsonModel.Add("FHLeftQty", Convert.ToDecimal(Inventory.FBASEQTY));//可用数量 jsonModel.Add("FUnitDosage", Convert.ToDecimal(UnitDosage));//单位用量 . jsonModel.Add("FSumPlanCount", Convert.ToDecimal(SumPlan));//FSumPlanCount 计划总数量 jsonModel.Add("FCompleteCount", Convert.ToDecimal(SumGetComplete));//FCompleteCount 齐套数量 jsonModel.Add("FPRDBILLNO", item.FMOBillNO);//FPRDBILLNO 工单编号 jsonModel.Add("FIdentificat", "2");//标识 jsonRoot.Add("Model", jsonModel); CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/"); var result = cloudClient.Save("paez_CompleteAnalysisTemp", jsonRoot.ToString()); JObject saveObj = JObject.Parse(result); string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper(); if (saveIsSuc != "TRUE") { this.View.ShowMessage("操作失败，联系管理员！"); } if (xhsl >= Inventory.FBASEQTY) { xhsl = xhsl - Inventory.FBASEQTY; Inventory.FBASEQTY = 0; } else { Inventory.FBASEQTY -= xhsl; xhsl = 0; } } } return SumGetComplete.ToString(); } /// /// 齐套分析 /// public void CompleteSetAnalysis() { try { Stopwatch sw = new Stopwatch(); sw.Start();//开始计时 LogHelper.Info("齐套分析开始准备数据：" + sw.Elapsed); //清空齐套临时表 DBServiceHelper.Execute(Context, "/*dialect*/ delete JIT_MOMaterReadysBill "); //更新状态 DBServiceHelper.Execute(Context, "/*dialect*/ update Sc_WorkBillSortBillsub set FCOMPLETE='' "); //获取当前即时库存 按照物料+库存组织+货主+库存数量 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_MOMaterReadysBill 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 --where T1.FMATERIALID in (105773) "); ListInventory = ds.ToModelList(); //总库存 物料+库存组织+库存数量 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_MOMaterReadysBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID --where T1.FMATERIALID in (105773) group by T1.FStockOrgId,T1.FMATERIALID "); ListInventoryByStockOrgId = ds.ToModelList(); //需要齐套分析的数据 string sql = @" /*dialect*/ select a.FPRDMOMAINID,a.FPRDMOENTYID,a.FID,b.FEntryID,a.FHPRDORGID FStockOrgId,t5.FNUMBER as OrgFnumber,a.FHOrderLev,b.FHQTY,b.FHMASTERDATE,T1.FBillNo,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty, T2.FMATERIALID FMATERIALID2, T4.FNumber,T2.FNeedQty,FNeedQty/FQty dwyl,(FHQTY-ISNULL(FProductNum,0))*(FNeedQty/FQty) PlanCount 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, MAX(FENTRYID) FENTRYID, SUM(FNeedQty) FNeedQty, FMATERIALID from T_PRD_PPBOMENTRY group by FMATERIALID, FID ) T2 on T1.FID = 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_ORG_Organizations t5 on a.FHPRDORGID = t5.FORGID --条件为今天往后的日计划 + 今天之前未关闭的日计划 where (FHEntryCloseDate is null and DATEDIFF(D,GETDATE(),FHMASTERDATE)<0)or DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0 ORDER BY b.FHMASTERDATE,a.FHOrderLev "; ds = DBServiceHelper.ExecuteDataSet(Context, sql); List BomList = ds.ToModelList(); //日计划+生产订单子表编码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 = new List(); int i = 1; //创建字典 用于储存物料 + 占用数量 Dictionary occupyDic = new Dictionary(); foreach (var item in DatePlanList) { this.View.Session["ProcessRateValue"] = Convert.ToInt32(this.View.Session["ProcessRateValue"]) + Convert.ToInt32((decimal)(1.0 / DatePlanList.Count)); //筛选出当日计划数据 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 _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.FNumber, FHStockOrgID = _item.OrgFnumber, FHPRDORGID = _item.OrgFnumber, FPRDBillNo = _item.FMOBillNO, FOwnerTypeId = "BD_OwnerOrg", FOwnerId = Inventory.Fnumber, FUnitDosage = _item.dwyl.ToString(),//单位用量 FSumPlanCount = _item.PlanCount.ToString(),//计划总数量 FCompleteCount = fCompleteCount, //齐套数量 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.FNumber, FHStockOrgID = _item.OrgFnumber, FHPRDORGID = _item.OrgFnumber, FPRDBillNo = _item.FMOBillNO, FOwnerTypeId = "BD_OwnerOrg", FUnitDosage = _item.dwyl.ToString(),//单位用量 FSumPlanCount = _item.PlanCount.ToString(),//计划总数量 FCompleteCount = 0, //齐套数量 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($"齐套分析，数据准备完成，保存到Model实体，总行数：{completeAnalysisTempModel.Count}，耗时：" + sw.Elapsed); i = 1; foreach (var item in completeAnalysisTempModel) { // this.View.Session["ProcessRateValue"] = Convert.ToInt32(this.View.Session["ProcessRateValue"]) + Convert.ToInt32(1 / completeAnalysisTempModel.Count * 90); JObject jsonRoot = new JObject(); jsonRoot.Add("Creator", ""); jsonRoot.Add("NeedUpDateFields", new JArray()); jsonRoot.Add("NeedReturnFields", new JArray()); jsonRoot.Add("IsDeleteEntry", "true"); jsonRoot.Add("SubSystemId", ""); jsonRoot.Add("IsVerifyBaseDataField", ""); JObject jsonModel = new JObject(); jsonModel.Add("FHMainICMOInterIDr", item.FHMainICMOInterIDr); jsonModel.Add("FHMainICMOEntryID", item.FHMainICMOEntryID); jsonModel.Add("FHICMOInterID", item.FHICMOInterID); jsonModel.Add("FHICMOEntryID", item.FHICMOEntryID); jsonModel.Add("FHMaterID", new JObject() { ["FNUMBER"] = item.FHMaterID }); jsonModel.Add("FHUseQty", item.FHUseQty); jsonModel.Add("FHStockOrgID", new JObject() { ["FNUMBER"] = item.FHStockOrgID }); jsonModel.Add("FHStockQty", item.FHStockQty); jsonModel.Add("FHLeftQty", item.FHLeftQty); jsonModel.Add("FHPRDORGID", new JObject() { ["FNUMBER"] = item.FHStockOrgID }); jsonModel.Add("FUnitDosage", item.FUnitDosage); jsonModel.Add("FSumPlanCount", item.FSumPlanCount); jsonModel.Add("FCompleteCount", item.FCompleteCount); jsonModel.Add("FCompleteCount1", item.FCompleteCount1); jsonModel.Add("FPRDBillNo", item.FPRDBillNo); jsonModel.Add("FOwnerTypeId", item.FOwnerTypeId); jsonModel.Add("FComPlete", item.FComPlete); jsonModel.Add("FLackCount", item.FLackCount); jsonModel.Add("FOccupyCount", item.FOccupyCount); jsonModel.Add("FPlanDate", item.FPlanDate); if (item.FOwnerId != null) jsonModel.Add("FOwnerId", new JObject() { ["FNUMBER"] = item.FOwnerId }); jsonRoot.Add("Model", jsonModel); CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/"); var result = cloudClient.Save("paez_CompleteAnalysisTemp", jsonRoot.ToString()); JObject saveObj = JObject.Parse(result); string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper(); if (saveIsSuc != "TRUE") { LogHelper.Error(jsonRoot.ToString()); } this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(100) / completeAnalysisTempModel.Count) * i); i++; } //执行完成后 更新日计划工单状态 DBServiceHelper.Execute(Context, @" /*dialect*/update Sc_WorkBillSortBillSub set FComplete = '未齐套' where FEntryID IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBill where isnull(FCOMPLETE,'未齐套') ='未齐套') "); DBServiceHelper.Execute(Context, @" /*dialect*/update Sc_WorkBillSortBillSub set FComplete = '齐套' where FEntryID NOT IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBill where isnull(FCOMPLETE,'未齐套') ='未齐套') "); sw.Stop();//结束计时 LogHelper.Info("齐套分析运行总时长：" + sw.Elapsed); } catch (Exception ex) { LogHelper.Error("齐套分析跳出：" + ex.Message.ToString()); } } /// /// 查询 /// public void SearchList() { string sqlwhere = " where 1=1"; if ((this.Model.GetValue("FProWorkShopId") as DynamicObject) != null) { string FProWorkShopId = (this.Model.GetValue("FProWorkShopId") as DynamicObject)["Id"].ToString(); if (!string.IsNullOrEmpty(FProWorkShopId)) sqlwhere += $" and FHWorkShopID='{FProWorkShopId}'"; } //销售订单号 string FSalOrder = Convert.ToString((this.Model.GetValue("FSalOrder"))); if (!string.IsNullOrEmpty(FSalOrder)) sqlwhere += $" and FHSeOrderBillNo='{FSalOrder}'"; //生产订单号 string FScOrderMainNo = Convert.ToString((this.Model.GetValue("FScOrderMainNo"))); if (!string.IsNullOrEmpty(FScOrderMainNo)) sqlwhere += $" and FSCORDERNO='{FScOrderMainNo}'"; //物料 if ((this.Model.GetValue("FMatralId") as DynamicObject) != null) { string FMatralId = (this.Model.GetValue("FMatralId") as DynamicObject)["Number"].ToString(); if (!string.IsNullOrEmpty(FMatralId)) sqlwhere += $" and t3.FNumber='{FMatralId}'"; } //编制日期 string FPreparatDate = Convert.ToString((this.Model.GetValue("FPreparatDate"))); if (!string.IsNullOrEmpty(FPreparatDate)) sqlwhere += $" and FPreparatDate='{FPreparatDate}'"; try { string sql = @"/*dialect*/ select t1.*,isnull((select SUM(isnull(FNotProductNum,0)) from SC_WORKBILLSORTBILLSub t2 where t2.FID = t1.FID and DATEDIFF(D,FHMASTERDATE,GETDATE())>0),0)FNotProductNum from Sc_WorkBillSortBillMain t1 left join T_BD_MATERIAL t3 on t1.FHMaterID=t3.FMATERIALID " + sqlwhere; DataTable dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0]; DateTime DateNow = DateTime.Now;//获取当前日期 this.Model.DeleteEntryData("FEntity"); EntryGrid grid = this.View.GetControl("FEntity"); for (int i = 0; i < dt.Rows.Count; i++) { this.Model.CreateNewEntryRow("FEntity"); this.Model.SetValue("FSEQ", i + 1, i);//序号 this.Model.SetValue("FCompleteSetCount", "", i);//总齐套数量 this.Model.SetValue("FProOrderCount", dt.Rows[i]["FHOrderNeedQty"].ToString(), i);//生产订单数量 this.Model.SetValue("FSalOrderCount", dt.Rows[i]["FHSeOrderBillNo"].ToString(), i);//销售订单数量 this.Model.SetValue("FBatch", dt.Rows[i]["FBatch"].ToString(), i);//批次号 this.Model.SetValue("FUnit", dt.Rows[i]["FHUnitID"].ToString(), i);//计量单位 this.Model.SetValue("FORGID", dt.Rows[i]["FHPRDORGID"].ToString(), i);//生产组织 this.Model.SetValue("FMatrailId", dt.Rows[i]["FHMaterID"].ToString(), i);//产品代码 this.Model.SetValue("FDeliverydate", dt.Rows[i]["FHOrderCommitDate"].ToString(), i);//交货日期 this.Model.SetValue("FOrderLevel", dt.Rows[i]["FHOrderLev"].ToString(), i);//订单等级 this.Model.SetValue("FSCOrderNo", dt.Rows[i]["FSCOrderNo"].ToString(), i);//生产订单号 this.Model.SetValue("FSalOrderNo", dt.Rows[i]["FHSEORDERBILLNO"].ToString(), i);//销售订单号 this.Model.SetValue("FBzDate", dt.Rows[i]["FPreparatDate"].ToString(), i);//编制日期 this.Model.SetValue("FProOrderCount", dt.Rows[i]["FHOrderNeedQty"].ToString(), i);//生产订单数量 this.Model.SetValue("FHMainSourceInterID", dt.Rows[i]["FPRDMOMAINID"].ToString(), i);//源单主内码FHMainSourceInterID this.Model.SetValue("FHMainSourceEntryID", dt.Rows[i]["FPRDMOENTYID"].ToString(), i);//源单子内码FHMainSourceEntryID this.Model.SetValue("FDayPlanWorkID", dt.Rows[i]["FID"].ToString(), i);//日计划工单FID this.Model.SetValue("FResourcesId", dt.Rows[i]["FHSourceID"].ToString(), i);//生产资源 this.Model.SetValue("FProductWorkShopId", dt.Rows[i]["FHWorkShopID"].ToString(), i);//生产车间 this.Model.SetValue("FCompleteSetCount", dt.Rows[i]["FCompleteCount"].ToString(), i);//齐套数量 this.Model.SetValue("FOrderQuantity", dt.Rows[i]["FOrderQuantity"].ToString(), i);//生产订单数量 this.Model.SetValue("FDayPlanQuantity", dt.Rows[i]["FDayPlanQuantity"].ToString(), i);//日计划数量 this.Model.SetValue("FNoScheduled", dt.Rows[i]["FNoScheduled"].ToString(), i);//未排数量 this.Model.SetValue("FDelaylnUM", dt.Rows[i]["FNotProductNum"].ToString(), i);//拖期数量 sql = @"/*dialect*/SELECT * FROM Sc_WorkBillSortBillSub WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0 and FID=" + dt.Rows[i]["FID"].ToString(); DataTable _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0]; foreach (DataRow dr in _dt.Rows) { DateTime start = Convert.ToDateTime(DateNow.ToShortDateString()); DateTime end = Convert.ToDateTime(Convert.ToDateTime(dr["FHMASTERDATE"]).ToShortDateString()); TimeSpan getDay = end.Subtract(start); if (getDay.Days > 60) continue; this.Model.SetValue("FT" + getDay.Days.ToString(), dr["FHQTY"].ToString(), i);//日计划工单FID if (dr["FHLockedSub"].ToString() == "1") this.View.GetFieldEditor("FT" + getDay.Days.ToString(), i).Enabled = false; //单据体行锁定字段 } this.View.InvokeFieldUpdateService("FMatrailId", i);//调用数量值更新 } this.View.UpdateView(); for (int j = 0; j < dt.Rows.Count; j++) { sql = @"/*dialect*/SELECT * FROM Sc_WorkBillSortBillSub WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0 and FID=" + dt.Rows[j]["FID"].ToString(); DataTable _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0]; foreach (DataRow dr in _dt.Rows) { DateTime start = Convert.ToDateTime(DateNow.ToShortDateString()); DateTime end = Convert.ToDateTime(Convert.ToDateTime(dr["FHMASTERDATE"]).ToShortDateString()); TimeSpan getDay = end.Subtract(start); if (getDay.Days > 60) continue; if (dr["FHLockedSub"].ToString() == "1") this.View.GetFieldEditor("FT" + getDay.Days.ToString(), j).Enabled = false; //单据体行锁定字段 if (dr["FCOMPLETE"].ToString() == "未齐套") { this.View.GetFieldEditor("FT" + getDay.Days.ToString(), j).Enabled = false; //单据体行锁定字段 grid.SetForecolor("FT" + "" + getDay.Days.ToString() + "", "#FF0000", j); } else if (dr["FCOMPLETE"].ToString() == "齐套") { this.View.GetFieldEditor("FT" + getDay.Days.ToString(), j).Enabled = false; //单据体行锁定字段 grid.SetForecolor("FT" + "" + getDay.Days.ToString() + "", "#00FF00", j); } } } for (int m = 0; m <= 60; m++) { this.View.GetControl("FT" + "" + m + "").Text = DateNow.AddDays(m).ToShortDateString(); } } catch (Exception ex) { this.View.ShowErrMessage(ex.Message.ToString()); } } /// /// 编辑 /// public void SearchListEdit() { string sqlwhere = " where 1=1"; if ((this.Model.GetValue("FProWorkShopId") as DynamicObject) != null) { string FProWorkShopId = (this.Model.GetValue("FProWorkShopId") as DynamicObject)["Id"].ToString(); if (!string.IsNullOrEmpty(FProWorkShopId)) sqlwhere += $" and FHWorkShopID='{FProWorkShopId}'"; } //销售订单号 string FSalOrder = Convert.ToString((this.Model.GetValue("FSalOrder"))); if (string.IsNullOrEmpty(FSalOrder)) { } else sqlwhere += $" and FHSeOrderBillNo='{FSalOrder}'"; //生产订单号 string FScOrderMainNo = Convert.ToString((this.Model.GetValue("FScOrderMainNo"))); if (string.IsNullOrEmpty(FScOrderMainNo)) { } else sqlwhere += $" and FSCORDERNO='{FScOrderMainNo}'"; //物料 if ((this.Model.GetValue("FMatralId") as DynamicObject) != null) { string FMatralId = (this.Model.GetValue("FMatralId") as DynamicObject)["Number"].ToString(); if (!string.IsNullOrEmpty(FMatralId)) sqlwhere += $" and t3.FNumber='{FMatralId}'"; } //编制日期 string FPreparatDate = Convert.ToString((this.Model.GetValue("FPreparatDate"))); if (!string.IsNullOrEmpty(FPreparatDate)) sqlwhere += $" and FPreparatDate='{FPreparatDate}'"; try { string sql = @"/*dialect*/ select t1.*,isnull((select SUM(isnull(FNotProductNum,0)) from SC_WORKBILLSORTBILLSub t2 where t2.FID = t1.FID and DATEDIFF(D,FHMASTERDATE,GETDATE())>0),0)FNotProductNum from Sc_WorkBillSortBillMain t1 left join T_BD_MATERIAL t3 on t1.FHMaterID=t3.FMATERIALID " + sqlwhere; DataTable dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0]; DateTime DateNow = DateTime.Now;//获取当前日期 this.Model.DeleteEntryData("FEntity"); EntryGrid grid = this.View.GetControl("FEntity"); for (int i = 0; i < dt.Rows.Count; i++) { this.Model.CreateNewEntryRow("FEntity"); this.Model.SetValue("FSEQ", i + 1, i);//序号 this.Model.SetValue("FCompleteSetCount", "", i);//总齐套数量 this.Model.SetValue("FProOrderCount", dt.Rows[i]["FHOrderNeedQty"].ToString(), i);//生产订单数量 this.Model.SetValue("FSalOrderCount", dt.Rows[i]["FHSeOrderBillNo"].ToString(), i);//销售订单数量 this.Model.SetValue("FBatch", dt.Rows[i]["FBatch"].ToString(), i);//批次号 this.Model.SetValue("FUnit", dt.Rows[i]["FHUnitID"].ToString(), i);//计量单位 this.Model.SetValue("FORGID", dt.Rows[i]["FHPRDORGID"].ToString(), i);//生产组织 this.Model.SetValue("FMatrailId", dt.Rows[i]["FHMaterID"].ToString(), i);//产品代码 this.Model.SetValue("FDeliverydate", dt.Rows[i]["FHOrderCommitDate"].ToString(), i);//交货日期 this.Model.SetValue("FOrderLevel", dt.Rows[i]["FHOrderLev"].ToString(), i);//订单等级 this.Model.SetValue("FSCOrderNo", dt.Rows[i]["FSCOrderNo"].ToString(), i);//生产订单号 this.Model.SetValue("FSalOrderNo", dt.Rows[i]["FHSEORDERBILLNO"].ToString(), i);//销售订单号 this.Model.SetValue("FBzDate", dt.Rows[i]["FPreparatDate"].ToString(), i);//编制日期 this.Model.SetValue("FProOrderCount", dt.Rows[i]["FHOrderNeedQty"].ToString(), i);//生产订单数量 this.Model.SetValue("FHMainSourceInterID", dt.Rows[i]["FPRDMOMAINID"].ToString(), i);//源单主内码FHMainSourceInterID this.Model.SetValue("FHMainSourceEntryID", dt.Rows[i]["FPRDMOENTYID"].ToString(), i);//源单子内码FHMainSourceEntryID this.Model.SetValue("FDayPlanWorkID", dt.Rows[i]["FID"].ToString(), i);//日计划工单FID this.Model.SetValue("FResourcesId", dt.Rows[i]["FHSourceID"].ToString(), i);//生产资源 this.Model.SetValue("FProductWorkShopId", dt.Rows[i]["FHWorkShopID"].ToString(), i);//生产车间 this.Model.SetValue("FCompleteSetCount", dt.Rows[i]["FCompleteCount"].ToString(), i);//齐套数量 this.Model.SetValue("FOrderQuantity", dt.Rows[i]["FOrderQuantity"].ToString(), i);//生产订单数量 this.Model.SetValue("FDayPlanQuantity", dt.Rows[i]["FDayPlanQuantity"].ToString(), i);//日计划数量 this.Model.SetValue("FNoScheduled", dt.Rows[i]["FNoScheduled"].ToString(), i);//未排数量 this.Model.SetValue("FDelaylnUM", dt.Rows[i]["FNotProductNum"].ToString(), i);//拖期数量 sql = @"/*dialect*/SELECT * FROM Sc_WorkBillSortBillSub WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0 and FID=" + dt.Rows[i]["FID"].ToString(); DataTable _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0]; foreach (DataRow dr in _dt.Rows) { DateTime start = Convert.ToDateTime(DateNow.ToShortDateString()); DateTime end = Convert.ToDateTime(Convert.ToDateTime(dr["FHMASTERDATE"]).ToShortDateString()); TimeSpan getDay = end.Subtract(start); if (getDay.Days > 60) continue; this.Model.SetValue("FT" + getDay.Days.ToString(), dr["FHQTY"].ToString(), i);//日计划工单FID if (dr["FHLockedSub"].ToString() == "1") this.View.GetFieldEditor("FT" + getDay.Days.ToString(), i).Enabled = false; //单据体行锁定字段 } this.View.InvokeFieldUpdateService("FMatrailId", i);//调用数量值更新 } this.View.UpdateView(); for (int j = 0; j < dt.Rows.Count; j++) { sql = @"/*dialect*/SELECT * FROM Sc_WorkBillSortBillSub WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0 and FID=" + dt.Rows[j]["FID"].ToString(); DataTable _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0]; foreach (DataRow dr in _dt.Rows) { DateTime start = Convert.ToDateTime(DateNow.ToShortDateString()); DateTime end = Convert.ToDateTime(Convert.ToDateTime(dr["FHMASTERDATE"]).ToShortDateString()); TimeSpan getDay = end.Subtract(start); if (getDay.Days > 60) continue; if (dr["FHLockedSub"].ToString() == "1") this.View.GetFieldEditor("FT" + getDay.Days.ToString(), j).Enabled = false; //单据体行锁定字段 } } for (int m = 0; m <= 60; m++) { this.View.GetControl("FT" + "" + m + "").Text = DateNow.AddDays(m).ToShortDateString(); } } catch (Exception ex) { this.View.ShowErrMessage(ex.Message.ToString()); } } /// /// 日计划用料清单生成 /// public void DayPlanPPBomBill() { LogHelper.Info("生产用料清单准备数据"); //锁定的日计划不生成 try { string sql = string.Format(@" /*dialect*/ select t1.FID,t1.FEntryID,t3.FMATERIALID,T5.FNUMBER,t1.FHMASTERDATE,t1.FHQTY,t4.FMATERIALID FMATERIALID2,T6.FNUMBER FNUMBER2,T7.FNUMBER FUNITNUMBER,FNumerator,FDenominator,convert(decimal(18,2),(FNumerator/FDenominator) *t1.FHQTY) FHQtyMust,T4.FUnitID,t8.FNUMBER FUNITNUMBER2,T3.FID FPPBOM,T4.FENTRYID,isnull(T9.FBillNo,'')FBillNo,t10.FNUMBER FORGNUMBER,t11.FNUMBER FHSOURCENUMBER,FSCRAPRATE,FFIXSCRAPQTY,T9.FID AS DayPlanFID FROM Sc_WorkBillSortBillSub t1 JOIN Sc_WorkBillSortBillMAIN t2 on t1.FID = t2.FID JOIN T_PRD_PPBOM T3 on T2.FPRDMOMAINID=t3.FMOID AND T2.FPRDMOENTYID=T3.FMOENTRYID JOIN T_PRD_PPBOMENTRY T4 ON T3.FID=T4.FID JOIN T_BD_MATERIAL T5 ON T3.FMATERIALID=T5.FMATERIALID JOIN T_BD_MATERIAL T6 ON T4.FMATERIALID=T6.FMATERIALID LEFT JOIN T_BD_UNIT T7 ON T3.FUNITID = T7.FUNITID LEFT JOIN T_BD_UNIT T8 ON T4.FUNITID = T8.FUNITID LEFT JOIN Sc_DayPlanPPBomBillMain T9 ON T1.FEntryID=T9.FDayPlanFID LEFT join T_ORG_Organizations t10 on t2.FHPRDORGID = t10.FORGID LEFT join T_ENG_WORKCENTER t11 on t2.FHSOURCEID = t11.FID " ); DataSet ds = DBServiceHelper.ExecuteDataSet(Context, sql); DataTable dt = ds.Tables[0]; List DayPlanMaterial = new List(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { DayPlanMaterial.Add(new Demo.Model.Model.DayPlanMaterial { FID = dr["DayPlanFID"].ToString(), FEntryID = dr["FEntryID"].ToString(), FHDate = DateTime.Now.ToString(), FOrgId = dr["FORGNUMBER"].ToString(), FHMaterID = dr["FNUMBER"].ToString(), FHWorkDate = dr["FHMASTERDATE"].ToString(), FHQty = dr["FHQTY"].ToString(), FHUnitID = dr["FUNITNUMBER"].ToString(), FDayPlanFID = dr["FEntryID"].ToString(), FHMaterSubID = dr["FNUMBER2"].ToString(), FHUnitSubID = dr["FUNITNUMBER2"].ToString(), FHQtyMust = dr["FHQtyMust"].ToString(), FHQtySub = dr["FHQtyMust"].ToString(), FHPPBomInterID = dr["FPPBOM"].ToString(), FHPPBomEntryID = dr["FENTRYID"].ToString(), FHSourceID = dr["FHSOURCENUMBER"].ToString(), FNumerator = dr["FNumerator"].ToString(), FDenominator = dr["FDenominator"].ToString(), FFIXSCRAPQTY = dr["FFIXSCRAPQTY"].ToString(), FHQtyScrap = dr["FSCRAPRATE"].ToString(), }); } var fidList = DayPlanMaterial.Select(x => x.FEntryID).Distinct().ToList(); LogHelper.Error("生产用料清单准备数据" + fidList.Count); foreach (var item in fidList) { var _DayPlanMaterial = DayPlanMaterial.Where(x => x.FEntryID == item).ToList(); JObject model = new JObject(); model.Add("FHDate", DateTime.Now); model.Add("FOrgId", new JObject() { ["Fnumber"] = _DayPlanMaterial[0].FOrgId });//发料组织 model.Add("FHMaterID", new JObject() { ["Fnumber"] = _DayPlanMaterial[0].FHMaterID });//物料 model.Add("FHWorkDate", _DayPlanMaterial[0].FHWorkDate);//日工单日期 FHWorkDate model.Add("FHQty", _DayPlanMaterial[0].FHQty);//主产品数量：FHQty model.Add("FHUnitID", new JObject() { ["Fnumber"] = _DayPlanMaterial[0].FHUnitID });//单位：FHUnitID model.Add("FDayPlanFID", _DayPlanMaterial[0].FDayPlanFID);//日计划工单内码 JArray Entry = new JArray(); foreach (var _item in _DayPlanMaterial) { JObject ModelEnty = new JObject(); ModelEnty.Add("FHMaterSubID", new JObject() { ["Fnumber"] = _item.FHMaterSubID });//子物料FHMaterSubID ModelEnty.Add("FHUnitSubID", new JObject() { ["Fnumber"] = _item.FHUnitSubID });//单位FHUnitSubID ModelEnty.Add("FHQtyMust", _item.FHQtyMust);//应发数量FHQtyMust ModelEnty.Add("FHQtySub", _item.FHQtySub);//数量FHQtySub ModelEnty.Add("FHPPBomInterID", _item.FHPPBomInterID);//用料清单主内码FHPPBomInterID ModelEnty.Add("FHPPBomEntryID", _item.FHPPBomEntryID);//用料清单子内码FHPPBomEntryID ModelEnty.Add("FHSourceID", new JObject() { ["Fnumber"] = _item.FHSourceID });//生产资源FHSourceID ModelEnty.Add("FNumerator", _item.FNumerator);//分母 ModelEnty.Add("FDenominator", _item.FDenominator);//分子 ModelEnty.Add("FDwyl", Convert.ToDecimal(_item.FNumerator) / Convert.ToDecimal(_item.FDenominator));//单位用料 ModelEnty.Add("FFIXSCRAPQTY", _item.FFIXSCRAPQTY);//固定损耗 ModelEnty.Add("FHQtyScrap", _item.FHQtyScrap);//变动损耗率% Entry.Add(ModelEnty); } model.Add("FEntity", Entry); //if (!string.IsNullOrEmpty(_DayPlanMaterial[0].FID)) // model.Add("FID", _DayPlanMaterial[0].FID); JObject jsonRoot = new JObject() { ["Creator"] = "", ["NeedUpDateFields"] = new JArray(), ["NeedReturnFields"] = new JArray(), ["IsDeleteEntry"] = "true", ["SubSystemId"] = "", ["IsVerifyBaseDataField"] = "false", ["Model"] = model }; CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/"); var result = cloudClient.Save("Paez_Sc_DayPlanPPBomBill", jsonRoot.ToString()); JObject saveObj = JObject.Parse(result); string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper(); if (saveIsSuc != "TRUE") { LogHelper.Error(jsonRoot.ToString()); } } } } catch (Exception ex) { LogHelper.Error(ex.Message.ToString()); } } /// /// 提料计划 /// public void Extraction() { try { Stopwatch sw = new Stopwatch(); sw.Start();//开始计时 string sql = string.Format(@"/*dialect*/ select t1.FID from Sc_WorkBillSortBillMain t1"); DataTable fidDt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0]; string FDayPlanWorkID = ""; foreach (DataRow dr in fidDt.Rows) { FDayPlanWorkID += Convert.ToString(dr["FID"]) + ","; } FDayPlanWorkID = FDayPlanWorkID.Substring(0, FDayPlanWorkID.Length - 1); //提料计划预处理 清理提料计划单数据 更新采购订单提料计划数量 sql = string.Format(@" /*dialect*/ exec [提料计划预处理] '{0}' ", FDayPlanWorkID.Replace(",", "-")); DBServiceHelper.Execute(Context, sql); //采购订单数据 sql = @" /*dialect*/ select t1.FID,t1.FBillNo,t2.FENTRYID,t1.FSUPPLIERID,t3.fnumber,t2.FMATERIALID,(t2.FQTY-t2.FPODemandPlanCount)FQTY,FPurchaseOrgId FStockOrgId,t5.FNUMBER FORGNumber,FSTOCKINQTY,FReceiveQty,FCloseStatus,FMRPCLOSESTATUS from t_PUR_POOrder t1 join t_PUR_POOrderEntry t2 on t1.FID = t2.FID join t_BD_Supplier t3 on t1.FSUPPLIERID = t3.FSUPPLIERID join T_PUR_POORDERENTRY_R t4 on t2.FENTRYID = t4.FENTRYID join T_ORG_Organizations t5 on t1.FPurchaseOrgId = t5.FORGID where t2.FQTY-t2.FPODemandPlanCount>0 and FCloseStatus in('A') and FMRPCLOSESTATUS in('A') "; List PurchaseInventory = DBServiceHelper.ExecuteDataSet(Context, sql).ToModelList(); LogHelper.Info("[提料计划准备阶段]可用采购订单数据sql：" + sql); sql = string.Format(@" /*dialect*/ SELECT T1.FID,FHMATERID,FHMASTERDATE,FHQTY,FCOMPLETECOUNT,FPRDMOMAINID,T2.FENTRYID,T1.FBILLNO, FNeedQty,(FLeadtime+FLeadTtime2)FFIXLEADTIME,t5.FMAXPOQTY,FJITBatch FMINPOQTY,FJITMATERIALGROUP,FJITMaterielDemand,FJITSafeStock,t3.FMATERIALID,t6.FNumber,FHQTY*FNeedQty NeedQty,FHPRDORGID FStockOrgId FROM SC_WORKBILLSORTBILLMAIN T1 JOIN SC_WORKBILLSORTBILLSUB T2 ON T1.FID=T2.FID LEFT JOIN(SELECT T1.FID,max(t2.FMATERIALID)FMATERIALIDMAIN, max(t2.FMoId)FMoId,MAX(FENTRYID) FENTRYID, SUM(convert(decimal(18, 2), FBASENUMERATOR / FBASEDENOMINATOR))FNeedQty, T1.FMATERIALID from T_PRD_PPBOMENTRY T1 JOIN T_PRD_PPBOM T2 on t1.FID = t2.FID group by T1.FMATERIALID, T1.FID) T3 on T1.FPRDMOMAINID = t3.FMoId and T1.FHMATERID = t3.FMATERIALIDMAIN JOIN T_BD_MATERIAL T6 ON T3.FMATERIALID = T6.FMATERIALID join t_BD_MaterialBase t4 on t3.FMATERIALID = t4.FMATERIALID join t_BD_MaterialPlan t5 on t3.FMATERIALID = t5.FMATERIALID WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE) >=0 AND FComplete ='未齐套' AND FERPCLSID=1 AND T2.FENTRYID not in (select distinct FHSOURCEENTRYID from CG_PODEMANDPLANBILLSUB where FHSOURCEINTERID IN ({0})) AND T1.FID in ({0}) order by FHMASTERDATE ", FDayPlanWorkID); LogHelper.Info("[提料计划准备阶段]需要提料数据sql：" + sql); DataSet ds = DBServiceHelper.ExecuteDataSet(Context, sql); List DayPlanPpbom = ds.ToModelList(); //物料+库存组织分类 var MaterialIDList = DayPlanPpbom.GroupBy(p => new { p.FMATERIALID, p.FStockOrgId }).Select(x => new PODemandPlanTemp { FMATERIALID = x.Key.FMATERIALID, FStockOrgId = x.Key.FStockOrgId }).ToList(); var _MaterialIDList = MaterialIDList.Where(p => p.FMATERIALID == "105773").ToList(); LogHelper.Info("[提料计划准备阶段]总物料行数" + MaterialIDList.Count); //提料计划数据集临时存储集合 List PODemandPlanTemp = new List(); //sql集合 更新采购订单占用的提料计划数量 List sqlList = new List(); //MaterialIDList = new List { "105773" }; foreach (var item in MaterialIDList) { //当前物料没有采购订单时 直接跳出 List _PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).ToList(); if (_PurchaseInventory.Count == 0) { LogHelper.Info("[提料计划开始]当前物料无采购信息：" + item.FMATERIALID + " 库存组织：" + item.FStockOrgId); continue; } //LogHelper.Info(item.FMATERIALID.ToString()); //当前物料和库存组织对应的需进行提料计划的数据 List _DayPlanPpbom = DayPlanPpbom.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId).ToList(); decimal FMINPOQTY = _DayPlanPpbom.FirstOrDefault().FMINPOQTY;//最小起订量 double FFIXLEADTIME = _DayPlanPpbom.FirstOrDefault().FFIXLEADTIME;//提前期 string FJITmaterialGroup = _DayPlanPpbom.FirstOrDefault().FJITmaterialGroup;//jit物料分类 string FJITMaterielDemand = _DayPlanPpbom.FirstOrDefault().FJITMaterielDemand; //JIT物料需求供货周期 string FJITSafeStock = _DayPlanPpbom.FirstOrDefault().FJITSafeStock; //JIT安全库存 decimal NeedQty = 0; DateTime DATE = _DayPlanPpbom.FirstOrDefault().FHMASTERDATE;//第一个订货起始日期 int i = 1; //记录在最小采购量需求下的日计划明细FentyrID List FEntryIdList = new List(); foreach (var _item in _DayPlanPpbom) { if (NeedQty == 0) DATE = Convert.ToDateTime(_item.FHMASTERDATE).AddDays(-FFIXLEADTIME); FEntryIdList.Add(_item.FENTRYID); NeedQty += _item.NeedQty; if ((FJITmaterialGroup == "总量控制规格类" || FJITmaterialGroup == "订单专用个性类") && FEntryIdList.Count < Convert.ToInt32(FJITMaterielDemand)) //订单专用个性类 continue; if (NeedQty >= FMINPOQTY) { /*修改前 判断方式 List _PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).ToList(); if (_PurchaseInventory.Count == 0) { NeedQty = 0; break; } */ foreach (var Purchase in _PurchaseInventory) { //LogHelper.Info("Purchase"); //LogHelper.Info("物料：" + Purchase.FMATERIALID + ",数量：" + Purchase.FQTY); if (Purchase.FQTY >= NeedQty) { foreach (int id in FEntryIdList) { var DayPlanPpbomls = DayPlanPpbom.Where(c => c.FENTRYID == id && c.FMATERIALID == _item.FMATERIALID).FirstOrDefault(); PODemandPlanTemp.Add(new Demo.Model.Model.PODemandPlan.PODemandPlanTemp { FID = DayPlanPpbomls.FID, FHMASTERDATE = DATE, FQty = DayPlanPpbomls.NeedQty, FBILLNO = DayPlanPpbomls.FBILLNO, FENTRYID = DayPlanPpbomls.FENTRYID, FMATERIALID = DayPlanPpbomls.FMATERIALID, FNumber = DayPlanPpbomls.FNumber, OLDDATE = DayPlanPpbomls.FHMASTERDATE, FFIXLEADTIME = FFIXLEADTIME, PurchseFID = Purchase.FID, PurchseFentryID = Purchase.FENTRYID, FSUPPLIERID = Purchase.FSUPPLIERID, PurchseFNUMBER = Purchase.FNUMBER, PurchseFBillNo = Purchase.FBillNo, PurchseFqty = Purchase.FQTY, FStockOrgId = Purchase.FStockOrgId, //采购组织 FORGNumber = Purchase.FORGNumber }); //扣除日计划明细已被分配的数量 DayPlanPpbomls.NeedQty = 0; } FEntryIdList.Clear(); //更新采购订单 sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount =isnull(FPODemandPlanCount,0)+ '{NeedQty}' where FENTRYID = {Purchase.FENTRYID}"); //扣除当前行对应采购订单数量 var ls = PurchaseInventory.Where(c => c.FENTRYID == Purchase.FENTRYID).FirstOrDefault(); ls.FQTY -= NeedQty; break; } else { //更新总需求数量 NeedQty = NeedQty - Purchase.FQTY; decimal _NeedQty = Purchase.FQTY;//采购订单数量 List mmm = new List(); foreach (int id in FEntryIdList) { var DayPlanPpbomls = DayPlanPpbom.Where(c => c.FENTRYID == id && c.FMATERIALID == _item.FMATERIALID).FirstOrDefault(); decimal Qty = DayPlanPpbomls.NeedQty;//订单数量 if (Qty > _NeedQty) Qty = _NeedQty; //订单数量>采购订单数量 取采购订单 _NeedQty = _NeedQty - DayPlanPpbomls.NeedQty; PODemandPlanTemp.Add(new Demo.Model.Model.PODemandPlan.PODemandPlanTemp { FID = DayPlanPpbomls.FID, FHMASTERDATE = DATE, FQty = Qty, FBILLNO = DayPlanPpbomls.FBILLNO, FENTRYID = DayPlanPpbomls.FENTRYID, FMATERIALID = DayPlanPpbomls.FMATERIALID, FNumber = DayPlanPpbomls.FNumber, OLDDATE = DayPlanPpbomls.FHMASTERDATE, FFIXLEADTIME = FFIXLEADTIME, PurchseFID = Purchase.FID, PurchseFentryID = Purchase.FENTRYID, FSUPPLIERID = Purchase.FSUPPLIERID, PurchseFNUMBER = Purchase.FNUMBER, PurchseFBillNo = Purchase.FBillNo, PurchseFqty = Purchase.FQTY, FStockOrgId = Purchase.FStockOrgId, //采购组织 FORGNumber = Purchase.FORGNumber }); //更新计划数量（剩余需要排的） 继续去计算下一个采购订单 DayPlanPpbomls.NeedQty -= Qty; //更新采购订单 sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount = isnull(FPODemandPlanCount,0)+ '{Qty}' where FENTRYID = {Purchase.FENTRYID}"); //更新Model var ls = PurchaseInventory.Where(c => c.FENTRYID == Purchase.FENTRYID).FirstOrDefault(); ls.FQTY -= Qty; if (_NeedQty < 0) { foreach (var mm in mmm) { FEntryIdList.Remove(mm); } break; } mmm.Add(id); } foreach (var mm in mmm) { FEntryIdList.Remove(mm); } } } NeedQty = 0; } #region 剩下不足时按最小采购批次生成 //剩下不足时按最小采购批次生成 //else if (i == _DayPlanPpbom.Count && NeedQty > 0) //{ // NeedQty = FMINPOQTY; // List _PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).ToList(); // if (_PurchaseInventory.Count == 0) // { // NeedQty = 0; // break; // } // foreach (var Purchase in _PurchaseInventory) // { // if (Purchase.FQTY >= NeedQty) // { // List mmm = new List(); // foreach (int id in FEntryIdList) // { // mmm.Add(id); // var DayPlanPpbomls = DayPlanPpbom.Where(c => c.FENTRYID == id && c.FMATERIALID == _item.FMATERIALID).FirstOrDefault(); // PODemandPlanTemp.Add(new Demo.Model.Model.PODemandPlan.PODemandPlanTemp // { // FID = DayPlanPpbomls.FID, // FHMASTERDATE = DATE, // FQty = DayPlanPpbomls.NeedQty, // FBILLNO = DayPlanPpbomls.FBILLNO, // FENTRYID = DayPlanPpbomls.FENTRYID, // FMATERIALID = DayPlanPpbomls.FMATERIALID, // FNumber = DayPlanPpbomls.FNumber, // OLDDATE = DayPlanPpbomls.FHMASTERDATE, // FFIXLEADTIME = FFIXLEADTIME, // PurchseFID = Purchase.FID, // PurchseFentryID = Purchase.FENTRYID, // FSUPPLIERID = Purchase.FSUPPLIERID, // PurchseFNUMBER = Purchase.FNUMBER, // PurchseFBillNo = Purchase.FBillNo, // PurchseFqty = Purchase.FQTY // }); // //扣除日计划明细已被分配的数量 // DayPlanPpbomls.NeedQty = 0; // } // FEntryIdList.Clear(); // //更新采购订单 // sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount =isnull(FPODemandPlanCount,0)+ '{NeedQty}' where FENTRYID = {Purchase.FENTRYID}"); // //扣除当前行对应采购订单数量 // var ls = PurchaseInventory.Where(c => c.FENTRYID == Purchase.FENTRYID).FirstOrDefault(); // ls.FQTY -= NeedQty; // break; // } // else // { // NeedQty = NeedQty - Purchase.FQTY; // decimal _NeedQty = Purchase.FQTY;//采购订单数量 // List mmm = new List(); // foreach (int id in FEntryIdList) // { // var DayPlanPpbomls = DayPlanPpbom.Where(c => c.FENTRYID == id && c.FMATERIALID == _item.FMATERIALID).FirstOrDefault(); // decimal Qty = DayPlanPpbomls.NeedQty; // if (Qty > _NeedQty) // Qty = _NeedQty; // _NeedQty = _NeedQty - DayPlanPpbomls.NeedQty; // PODemandPlanTemp.Add(new Demo.Model.Model.PODemandPlan.PODemandPlanTemp // { // FID = DayPlanPpbomls.FID, // FHMASTERDATE = DATE, // FQty = Qty, // FBILLNO = DayPlanPpbomls.FBILLNO, // FENTRYID = DayPlanPpbomls.FENTRYID, // FMATERIALID = DayPlanPpbomls.FMATERIALID, // FNumber = DayPlanPpbomls.FNumber, // OLDDATE = DayPlanPpbomls.FHMASTERDATE, // FFIXLEADTIME = FFIXLEADTIME, // PurchseFID = Purchase.FID, // PurchseFentryID = Purchase.FENTRYID, // FSUPPLIERID = Purchase.FSUPPLIERID, // PurchseFNUMBER = Purchase.FNUMBER, // PurchseFBillNo = Purchase.FBillNo, // PurchseFqty = Purchase.FQTY // }); // DayPlanPpbomls.NeedQty -= Qty; // //更新采购订单 // sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount = isnull(FPODemandPlanCount,0)+ '{Qty}' where FENTRYID = {Purchase.FENTRYID}"); // //更新Model // var ls = PurchaseInventory.Where(c => c.FENTRYID == Purchase.FENTRYID).FirstOrDefault(); // ls.FQTY -= Qty; // if (_NeedQty < 0) // { // foreach (var mm in mmm) // { // FEntryIdList.Remove(mm); // } // break; // } // mmm.Add(id); // } // foreach (var mm in mmm) // { // FEntryIdList.Remove(mm); // } // } // } // NeedQty = 0; //} #endregion i++; } } var PODemandPlanList222 = PODemandPlanTemp.GroupBy(p => new PODemandPlanTemp { PurchseFNUMBER = p.PurchseFNUMBER, FHMASTERDATE = p.FHMASTERDATE }).Select(x => new PODemandPlanTemp { PurchseFNUMBER = x.Key.PurchseFNUMBER, FHMASTERDATE = x.Key.FHMASTERDATE }).ToList(); var PODemandPlanList = PODemandPlanTemp.GroupBy(p => new { p.PurchseFNUMBER, p.FHMASTERDATE }).Select(x => new PODemandPlanTemp { PurchseFNUMBER = x.Key.PurchseFNUMBER, FHMASTERDATE = x.Key.FHMASTERDATE }).ToList(); LogHelper.Info($"提料计划，数据准备完成，保存到Model实体，总行数：{PODemandPlanTemp.Count}，耗时：" + sw.Elapsed); foreach (var item in PODemandPlanList) { DateTime date = item.FHMASTERDATE; string PurchseFNUMBER = item.PurchseFNUMBER; JObject model = new JObject(); model.Add("FHDate", date); model.Add("FHRemark", "生产订单号：" + "测试呢呢"); model.Add("FSupplierID", new JObject() { ["Fnumber"] = PurchseFNUMBER }); JArray Fentity = new JArray(); List _PODemandPlanList = PODemandPlanTemp.Where(x => x.FHMASTERDATE == date && x.PurchseFNUMBER == PurchseFNUMBER).ToList(); foreach (var _item in _PODemandPlanList) { JObject FentityModel = new JObject(); FentityModel.Add("FHPURCHASEORGID", new JObject() { ["Fnumber"] = _item.FORGNumber });//采购组织 FentityModel.Add("FHMaterID", new JObject() { ["Fnumber"] = _item.FNumber });//物料 FentityModel.Add("FHQty", _item.FQty);//数量 FentityModel.Add("FHSourceInterID", _item.FID);//日计划工单FID FentityModel.Add("FHSourceEntryID", _item.FENTRYID);//日计划工单FENTRYID FentityModel.Add("FHSourceBillNo", _item.FBILLNO);//日计划工单单号 FentityModel.Add("FHSourceBillType", "");//日计划工单类型 FentityModel.Add("FDayPlanDate", _item.OLDDATE);//日计划工单类型 FentityModel.Add("FHRelationQty", _item.PurchseFqty);//关联数量 FentityModel.Add("FFIXLEADTIME", _item.FFIXLEADTIME);//提前期 FentityModel.Add("FPURCHASEORGID", new JObject() { ["Fnumber"] = _item.FORGNumber });//采购组织 FentityModel.Add("FHPOOrderInterID", _item.PurchseFID);// 采购订单内码：FHPOOrderInterID FentityModel.Add("FHPOOrderEntryID", _item.PurchseFentryID); //采购订单子内码：FHPOOrderEntryID FentityModel.Add("FHPOOrderBillNo", _item.PurchseFBillNo); //采购订单号：FHPOOrderBillNo Fentity.Add(FentityModel); } model.Add("FEntity", Fentity); JObject jsonRoot = new JObject() { ["Creator"] = "", ["NeedUpDateFields"] = new JArray(), ["NeedReturnFields"] = new JArray(), ["IsDeleteEntry"] = "false", ["SubSystemId"] = "", ["IsVerifyBaseDataField"] = "false", ["Model"] = model }; CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/"); var result = cloudClient.Save("paez_PODemandPlan", jsonRoot.ToString()); JObject saveObj = JObject.Parse(result); string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper(); if (saveIsSuc != "TRUE") { LogHelper.Info(jsonRoot.ToString()); LogHelper.Error(saveIsSuc); } } sw.Stop();//结束计时 LogHelper.Info("提料计划执行完成，运行总时长：" + sw.Elapsed); DBServiceHelper.ExecuteBatch(Context, sqlList); } catch (Exception ex) { LogHelper.Info("提料计划跳出：" + ex.Message.ToString()); } // var PODemandPlanList22 = PODemandPlanTemp.GroupBy(p => new {p.FSUPPLIERID, p.FHMASTERDATE }).ToList(); } /// /// 1.锁库，具体字段根据业务单据决定，‘Inv’表示即时库存，‘FInvDetailID’为即时库存ID /// public void Reserve() { try { decimal ReserveCount = Convert.ToDecimal((this.Model.GetValue("FReservedCount"))); if (ReserveCount == 0) { this.View.ShowMessage("请输入预留数量"); return; } //获取单据体信息 Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity"); //单据体信息转换为列表集合 DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity); DynamicObjectCollection filterResult = entityDataObjoct.Clone(); filterResult.Clear(); entityDataObjoct.Where(x => Convert.ToBoolean(x["FFOCUS"])).ToList().ForEach(x => filterResult.Add(x)); if (filterResult.Count == 0) { this.View.ShowMessage("请选择需要预留的单据！"); return; } //总库存 物料+库存组织+库存数量 DataSet 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_MOMaterReadysBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID --where T1.FMATERIALID in (105773) group by T1.FStockOrgId,T1.FMATERIALID "); ListInventoryByStockOrgId = ds.ToModelList(); foreach (DynamicObject current in entityDataObjoct) { if (Convert.ToString(current["FFOCUS"]) == "False") continue; string sql = string.Format(@" /*dialect*/ --生产用料清单列表 SELECT T1.FID,T1.FBillNo,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty,FPRDORGID FStockOrgId, T2.FMATERIALID FMATERIALID2,T4.FNumber,T2.FNeedQty,convert(decimal(18,2),FNeedQty/FQty ) as dwyl --,isnull(t3.FBASEQTY,0)FBASEQTY FROM T_PRD_PPBOM T1 LEFT JOIN (select FID,MAX(FENTRYID)FENTRYID,SUM(FNeedQty)FNeedQty,FMATERIALID from T_PRD_PPBOMENTRY where FMoId='{0}' group by FMATERIALID,FID ) T2 on T1.FID=t2.FID --LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID LEFT JOIN T_BD_MATERIAL T4 on T2.FMATERIALID=T4.FMATERIALID where t1.FMoId='{0}' and T1.FMaterialID='{1}' ", current["FHMainSourceInterID"].ToString(), current["FMatrailId_Id"].ToString()); ds = DBServiceHelper.ExecuteDataSet(Context, sql); var PpbomList = ds.ToModelList(); //当前最大齐套数 下层需求数量/父级需求数量=单位用料 //var SumGetComplete = Math.Floor(PpbomList.Min(t => t.FBASEQTY / (t.FNeedQty / t.FQty))); //最大齐套数 var result = (from a in PpbomList join b in ListInventoryByStockOrgId on new { materialId = a.FMATERIALID2, stockOrgId = a.FStockOrgId } equals new { materialId = b.FMATERIALID, stockOrgId = b.FStockOrgId } into g from b in g.DefaultIfEmpty() select new { value = (b?.FBASEQTY / a.dwyl) ?? 0 }).ToList().Min(x => x.value); if (result < ReserveCount) { this.View.ShowErrMessage("当前库存超出可预留最大数：" + result + ""); return; }; //将预留数量分配到日计划工单 sql = string.Format(@"/*dialect*/select FEntryID,FHQTY from Sc_WorkBillSortBillSub where FID='{0}' and isnull(FCOMPLETE,'未齐套')='未齐套'", current["FDayPlanWorkID"].ToString()); DataTable dayPlanDt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0]; var count = ReserveCount; List sqlList = new List(); if (dayPlanDt.Rows.Count > 0) { foreach (DataRow dr in dayPlanDt.Rows) { if (count > 0) { sql = string.Format("/*dialect*/ update Sc_WorkBillSortBillSub set FReservedCount = {0} where FEntryID = {1}", count >= Convert.ToDecimal(dr["FHQTY"]) ? Convert.ToDecimal(dr["FHQTY"]) : count, dr["FEntryID"].ToString()); sqlList.Add(sql); count = count - Convert.ToDecimal(dr["FHQTY"]); } else break; } DBServiceHelper.ExecuteBatch(Context, sqlList); } sql = string.Format(@"/*dialect*/ --生产用料清单列表 SELECT T1.FID,T1.FBillNo,T2.FENTRYID,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty,FPRDORGID FStockOrgId, T2.FMATERIALID FMATERIALID2,T4.FNumber,T2.FNeedQty,convert(decimal(18,2),FNeedQty/T1.FQty ) as dwyl, t5.FBASEUNITID,t6.FSTOREUNITID,t7.FID FSTKID,t7.FOwnerID,t7.FOwnerTypeID,t7.FSTOCKID,t7.FBASEQTY --,isnull(t3.FBASEQTY,0)FBASEQTY FROM T_PRD_PPBOM T1 LEFT JOIN (select FID,MAX(FENTRYID)FENTRYID,SUM(FNeedQty)FNeedQty,FMATERIALID from T_PRD_PPBOMENTRY where FMoId='{0}' group by FMATERIALID,FID ) T2 on T1.FID=t2.FID --LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID LEFT JOIN T_BD_MATERIAL T4 on T2.FMATERIALID=T4.FMATERIALID join t_BD_MaterialBase t5 on t4.FMATERIALID = t5.FMATERIALID join t_BD_MaterialStock t6 on t4.FMATERIALID = t6.FMATERIALID join T_STK_INVENTORY t7 on t4.FMATERIALID = t7.FMATERIALID and t1.FPRDORGID=t7.FSTOCKORGID where t1.FMoId='{0}' and T1.FMaterialID='{1}' ", current["FHMainSourceInterID"].ToString(), current["FMatrailId_Id"].ToString()); DataTable skDt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0]; //锁库 List list = new List(); foreach (DataRow dr in skDt.Rows) { LockStockArgs lockStockArgs = new LockStockArgs(); lockStockArgs.ObjectId = "PRD_PPBOM"; //字段 FDEMANDFORMID = 'PRD_PPBOM' lockStockArgs.BillId = dr["FID"].ToString(); //生产用料清单主表ID lockStockArgs.BillDetailID = dr["FENTRYID"].ToString();//生产用料清单明细表ID lockStockArgs.BillNo = dr["FBillNo"].ToString();//生产用料清单单据号 lockStockArgs.FEntryID = Convert.ToInt32(current["FHMainSourceEntryID"]); ; lockStockArgs.FID = Convert.ToInt32(current["FHMainSourceInterID"]); //生产订单行ID lockStockArgs.BaseQty = decimal.Parse(dr["FBASEQTY"].ToString());//基本单位数量 lockStockArgs.BaseUnitID = int.Parse(dr["FBASEUNITID"].ToString());//基本单位 //lockStockArgs.Lot = Convert.ToString(current["FBatch"]); ;//批次 FBatch lockStockArgs.MaterialID = long.Parse(dr["FMATERIALID2"].ToString()); lockStockArgs.OwnerID = long.Parse(dr["FOwnerID"].ToString()); lockStockArgs.OwnerTypeID = dr["FOwnerTypeID"].ToString(); lockStockArgs.Qty = Convert.ToDecimal(dr["dwyl"].ToString()) * ReserveCount;//数量 lockStockArgs.STOCKID = long.Parse(dr["FSTOCKID"].ToString());//仓库 lockStockArgs.StockOrgID = long.Parse(dr["FStockOrgId"].ToString());//库存组织 lockStockArgs.UnitID = long.Parse(dr["FSTOREUNITID"].ToString());//单位 lockStockArgs.LockBaseQty = Convert.ToDecimal(dr["dwyl"].ToString()) * ReserveCount; lockStockArgs.LockQty = Convert.ToDecimal(dr["dwyl"].ToString()) * ReserveCount;//基本单位数量 lockStockArgs.FInvDetailID = Convert.ToString(dr["FSTKID"].ToString()); list.Add(lockStockArgs); } var sa = StockServiceHelper.SaveLockInfo(Context, list, "Inv", false); sql = @"/*dialect*/ update a set FPARENTFORMID ='PRD_MO', FPARENTINTERID =b.FMOID, FPARENTENTRYID =b.FMOENTRYID, FPARENTBILLNO = b.FMOBILLNO, FSRCFORMID = 'PRD_MO', FSRCINTERID = b.FMOID, FSRCENTRYID = b.FMOENTRYID, FSRCBILLNO = b.FMOBILLNO from T_PLN_RESERVELINK a join T_PRD_PPBOM b on a.FDEMANDINTERID = b.FID where a.FPARENTINTERID = '' and a.FPARENTENTRYID =''"; DBServiceHelper.Execute(Context, sql); this.View.ShowMessage("操作成功！"); } } catch (Exception ex) { LogHelper.Error(ex.Message.ToString()); this.View.ShowErrMessage(ex.Message.ToString()); } } /// /// 释放 /// public void Release() { try { //获取单据体信息 Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity"); //单据体信息转换为列表集合 DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity); DynamicObjectCollection filterResult = entityDataObjoct.Clone(); filterResult.Clear(); entityDataObjoct.Where(x => Convert.ToBoolean(x["FFOCUS"])).ToList().ForEach(x => filterResult.Add(x)); if (filterResult.Count == 0) { this.View.ShowMessage("请选择需要预留的单据！"); return; } foreach (DynamicObject current in filterResult) { if (Convert.ToString(current["FFOCUS"]) == "False") continue; string sql = string.Format(@"/*dialect*/ update Sc_WorkBillSortBillSub set FReservedCount = 0 where FID='{0}'", current["FDayPlanWorkID"].ToString()); DBServiceHelper.Execute(Context, sql); //删除锁库表(不用更新库存 库存表没有更) sql = string.Format(@"/*dialect*/ delete T_PLN_RESERVELINKENTRY where FID IN (SELECT FID FROM T_PLN_RESERVELINK WHERE FPARENTINTERID='{0}' AND FPARENTENTRYID='{1}') delete T_PLN_RESERVELINK WHERE FPARENTINTERID='{0}' AND FPARENTENTRYID='{1}'", Convert.ToString(current["FHMainSourceInterID"]), Convert.ToString(current["FHMainSourceEntryID"])); } this.View.ShowMessage("操作成功！"); } catch (Exception ex) { LogHelper.Error(ex.Message.ToString()); this.View.ShowErrMessage(ex.Message.ToString()); } } } } //tbReleaseReserve