using Demo.Model.Model;
|
using Demo.Model.Model.PODemandPlan;
|
using K3Cloud.Extend.Utils;
|
using Kingdee.BOS.Core;
|
using Kingdee.BOS.Core.Bill;
|
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.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.ComponentModel;
|
using System.Data;
|
using System.Diagnostics;
|
using System.Linq;
|
using System.Web;
|
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<PRD_Inventory> ListInventory;//库存明细
|
List<PRD_Inventory> ListInventoryByStockOrgId;//物料总库存
|
|
private bool isSearched = false;
|
public override void DataChanged(DataChangedEventArgs e)
|
{
|
if (!isSearched) return;
|
base.DataChanged(e);
|
var columns = new List<string>() { "", "" };
|
for (int i = 0; i < 100; i++)
|
{
|
columns.Add("FT" + i.ToString());
|
}
|
var model = this.View.Model;
|
if (columns.Contains(e.Field.Key))
|
{
|
var sum = columns.Select(x => Convert.ToDouble(model.GetValue(x, e.Row))).Sum();//日计划总和
|
var scOrderCount = Convert.ToDouble(model.GetValue("FOrderQuantity", e.Row));//生产订单数量
|
var FQTYLS = Convert.ToDouble(model.GetValue("FQTYLS", e.Row));//历史
|
this.View.Model.SetValue("FNoScheduled", scOrderCount - sum - FQTYLS, e.Row);
|
this.View.Model.SetValue("FDayPlanQuantity", sum, e.Row);
|
}
|
}
|
public override void BarItemClick(BarItemClickEventArgs e)
|
{
|
base.BarItemClick(e);
|
try
|
{
|
LogHelper.Info("生产计划平台按钮");
|
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")
|
{
|
isSearched = false;
|
SearchList();
|
isSearched = true;
|
}
|
if (e.BarItemKey.ToUpper() == "TBLOCK")
|
{
|
Lock();
|
}
|
//解锁
|
if (e.BarItemKey.ToUpper() == "TBRELIEVELOCK")
|
{
|
RelieveLock();
|
}
|
//保存
|
if (e.BarItemKey.ToUpper() == "BTNSAVE")
|
{
|
Save();
|
}
|
//齐套分析
|
if (e.BarItemKey.ToUpper() == "TBCOMPLETE")
|
{
|
COMPLETE();
|
}
|
//tbXnqt虚拟齐套
|
if (e.BarItemKey.ToUpper() == "TBXNQT")
|
{
|
Xnqt();
|
}
|
//tb_CKXNQT 查看虚拟齐套
|
if (e.BarItemKey.ToUpper() == "TB_CKXNQT")
|
{
|
XnqtLook();
|
}
|
//提料计划
|
if (e.BarItemKey.ToUpper() == "TBEXTRACTION")
|
{
|
try
|
{
|
CompleteSetAnalysisBySQL();
|
DayPlanPPBomBillBatch();
|
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")
|
{
|
isSearched = false;
|
SearchListEdit();
|
isSearched = true;
|
}
|
}
|
/// <summary>
|
/// button按钮点击事件
|
/// </summary>
|
/// <param name="e"></param>
|
public override void ButtonClick(ButtonClickEventArgs e)
|
{
|
base.ButtonClick(e);
|
if (e.Key.ToUpper() == "FTBXNQT")
|
{
|
try
|
{
|
List<string> sqlList = new List<string>();
|
string sql = $"truncate table Sc_WorkBillMainXnqt";
|
sqlList.Add(sql);
|
sql = $"truncate table Sc_WorkBillSubXnqt";
|
sqlList.Add(sql);
|
string columnName = DBServiceHelper.ExecuteScalar<string>(Context, "/*dialect*/ select stuff((select ',' + name from syscolumns Where id = object_Id('Sc_WorkBillMainXnqt') for xml path('')),1,1,'') as name ", "");
|
sql = $"insert into Sc_WorkBillMainXnqt({columnName}) select {columnName} from Sc_WorkBillSortBillMain";
|
LogHelper.Info("测试1：" + sql);
|
sqlList.Add(sql);
|
columnName = DBServiceHelper.ExecuteScalar<string>(Context, "/*dialect*/ select stuff((select ',' + name from syscolumns Where id = object_Id('Sc_WorkBillSubXnqt') for xml path('')),1,1,'') as name ", "");
|
sql = $"insert into Sc_WorkBillSubXnqt({columnName}) select {columnName} from Sc_WorkBillSortBillSub";
|
LogHelper.Info("测试1：" + sql);
|
|
sqlList.Add(sql);
|
sql = $"update Sc_WorkBillSubXnqt set FComplete=''";
|
sqlList.Add(sql);
|
|
DBServiceHelper.ExecuteBatch(Context, sqlList);
|
}
|
catch (Exception ex)
|
{
|
this.View.ShowErrMessage(ex.Message);
|
return;
|
}
|
this.View.ShowMessage("操作成功");
|
}
|
}
|
/// <summary>
|
/// 行双击 跳出齐套分析明细列表
|
/// </summary>
|
/// <param name="e"></param>
|
public override void EntityRowDoubleClick(Kingdee.BOS.Core.DynamicForm.PlugIn.Args.EntityRowClickEventArgs e)
|
{
|
LogHelper.Info("[行双击事件]");
|
|
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()));
|
this.View.ShowForm(formPa);
|
}
|
else if (e.ColKey == "FQTYLS")
|
{
|
//生产计划平台
|
string pageId = Guid.NewGuid().ToString();
|
BillShowParameter showParameter = new BillShowParameter();
|
showParameter.FormId = "Paez_Sc_WorkBillAutoSortBill";
|
showParameter.OpenStyle.ShowType = ShowType.MainNewTabPage;
|
showParameter.PageId = pageId;
|
showParameter.Status = OperationStatus.EDIT;
|
// 传入需要修改的日计划工单内码，
|
showParameter.PKey = Convert.ToString(this.View.Model.GetValue("FDayPlanWorkID", e.Row).ToString());
|
this.View.ShowForm(showParameter);
|
|
}
|
else
|
{
|
//齐套分析明细列表跳转
|
formPa.FormId = "paez_CompleteAnalysisDetail";
|
formPa.CustomParams.Add("FHICMOInterID", Convert.ToString(this.View.Model.GetValue("FDayPlanWorkID", e.Row).ToString()));
|
this.View.ShowForm(formPa);
|
}
|
|
}
|
/// <summary>
|
/// 查看虚拟齐套
|
/// </summary>
|
public void XnqtLook()
|
{
|
var _key = this.View.Model.GetEntryCurrentRowIndex("FEntity");
|
if (_key < 0)
|
{
|
this.View.ShowErrMessage("请选择需要查看的单据行！");
|
return;
|
}
|
DynamicFormShowParameter formPa = new DynamicFormShowParameter();
|
//齐套分析明细列表跳转
|
formPa.FormId = "paez_CompleteAnalysisDetail";
|
formPa.CustomParams.Add("FHICMOInterID", Convert.ToString(this.View.Model.GetValue("FDayPlanWorkID", _key).ToString()));
|
formPa.CustomParams.Add("tempName", "JIT_MOMaterReadysBillXn");
|
this.View.ShowForm(formPa);
|
}
|
/// <summary>
|
/// 导出数据到Excel
|
/// </summary>
|
public void ExportExcel()
|
{
|
List<string> Title = new List<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"
|
*/
|
DateTime date = DateTime.Now;
|
for (int i = 0; i < 61; i++)
|
{
|
Title.Add(date.AddDays(i).ToString("yyyy-MM-dd"));
|
}
|
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.Count; i++)
|
{
|
ICell cell = (ICell)row.CreateCell(i);
|
var rowName = Title[i];
|
cell.SetCellValue(rowName);
|
cell.CellStyle = style;
|
}
|
|
//单元格赋值
|
//列宽自适应，只对英文和数字有效
|
for (int i = 0; i <= Title.Count; 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;
|
/// <summary>
|
/// 弹出下载提示窗，下载文件至客户端本地。
|
/// </summary>
|
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 });
|
}
|
/// <summary>
|
/// 锁定
|
/// </summary>
|
public void Lock()
|
{
|
int day = Convert.ToInt32(this.Model.GetValue("FLockDays"));
|
List<string> sqlList = new List<string>();
|
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)
|
//{
|
//锁定后生成日计划用料清单 及 提料计划
|
DayPlanPPBomBillBatch();
|
Extraction();
|
SearchList();
|
this.View.ShowMessage("操作成功");
|
//}
|
//else
|
// this.View.ShowMessage("操作失败，联系管理员！");
|
}
|
/// <summary>
|
/// 解锁
|
/// </summary>
|
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<string> sqlList = new List<string>();
|
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);
|
}
|
SearchList();
|
this.View.ShowMessage("操作成功");
|
|
}
|
/// <summary>
|
/// 保存功能
|
/// </summary>
|
public void Save()
|
{
|
List<string> sqlList = new List<string>();
|
string sql;
|
//获取单据体信息
|
Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity");
|
//单据体信息转换为列表集合
|
DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity);
|
DateTime date = Convert.ToDateTime(DateTime.Now.ToString("d"));
|
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)
|
{
|
JObject jsonRoot = new JObject();
|
jsonRoot.Add("Creator", "");
|
jsonRoot.Add("NeedUpDateFields", new JArray());
|
jsonRoot.Add("NeedReturnFields", new JArray());
|
jsonRoot.Add("IsDeleteEntry", "false");
|
jsonRoot.Add("SubSystemId", "");
|
jsonRoot.Add("IsVerifyBaseDataField", "");
|
JObject jsonModel = new JObject();
|
jsonModel.Add("FID", Convert.ToString(current["FDayPlanWorkID"]));
|
jsonModel.Add("FOrderQuantity", Convert.ToString(current["FOrderQuantity"]));
|
jsonModel.Add("FDayPlanQuantity", Convert.ToDecimal(current["FDayPlanQuantity"]) + Convert.ToDecimal(current["FQTYLS"]));
|
jsonModel.Add("FNoScheduled", Convert.ToString(current["FNoScheduled"]));
|
sql = $" /*dialect*/ select FEntryID, FHMASTERDATE from SC_WORKBILLSORTBILLSub where FID={Convert.ToString(current["FDayPlanWorkID"])} ";
|
List<DayPlanModel> dayPlanModel = new List<DayPlanModel>();
|
dayPlanModel = DBServiceHelper.ExecuteDataSet(Context, sql).ToModelList<DayPlanModel>();
|
JArray Entry = new JArray();
|
for (int i = 0; i <= 60; i++)
|
{
|
if (Convert.ToString(current["FT" + "" + i + ""]) == "" || Convert.ToString(current["FT" + "" + i + ""]) == "0")
|
continue;
|
JObject jsonFPOOrderEntry = new JObject();
|
jsonFPOOrderEntry.Add("FHMasterDate", date.AddDays(i).ToShortDateString()); ;//主日期
|
jsonFPOOrderEntry.Add("FHQty ", Convert.ToString(current["FT" + "" + i + ""])); ;//日计划数量
|
List<DayPlanModel> _dayPlanModel = dayPlanModel.Where(x => x.FHMASTERDATE == date.AddDays(i)).ToList();
|
if (_dayPlanModel.Count > 0)
|
{
|
jsonFPOOrderEntry.Add("FEntryID", _dayPlanModel.FirstOrDefault().FEntryID); ;//序号
|
}
|
Entry.Add(jsonFPOOrderEntry);
|
}
|
jsonModel.Add("FEntity", Entry);
|
jsonRoot.Add("Model", jsonModel);
|
CloudClient cloudClient = new CloudClient("http://localhost//k3cloud/");
|
var result = cloudClient.Save("Paez_Sc_WorkBillAutoSortBill", jsonRoot.ToString());
|
JObject saveObj = JObject.Parse(result);
|
string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
|
if (saveIsSuc != "TRUE")
|
{
|
LogHelper.Error("生产计划平台保存失败:" + jsonRoot.ToString());
|
this.View.ShowMessage("数据储存失败！请联系管理员！");
|
}
|
}
|
|
this.View.ShowMessage("操作成功");
|
SearchList();
|
}
|
/// <summary>
|
/// 齐套分析
|
/// </summary>
|
///
|
private void COMPLETE()
|
{
|
// 显示一个进度显示界面：显示一个不停滚动的模拟进度
|
// 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(() =>
|
{
|
try
|
{
|
// 需要捕获错误，以确保处理结束时，关闭进度滚动界面
|
// 引入功能实际处理函数
|
this.CompleteSetAnalysisBySQL();
|
}
|
finally
|
{
|
// 确保标记进度已经到达100%
|
this.View.Session["ProcessRateValue"] = 100;
|
// 引入完毕，关闭进度显示页面
|
var processView = this.View.GetView(processForm.PageId);
|
if (processView != null)
|
{
|
processView.Close();
|
this.View.SendDynamicFormAction(processView);
|
}
|
}
|
},
|
(t) => { });
|
}
|
/// <summary>
|
/// 齐套分析
|
/// </summary>
|
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
|
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_MOMaterReadysBill 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>();
|
//需要齐套分析的数据
|
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,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(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_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>();
|
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<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.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,//占用数量
|
FErpClsID = _item.FErpClsID,
|
/* 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, // 日计划日期
|
FErpClsID = _item.FErpClsID
|
|
});
|
}
|
}
|
//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);
|
jsonModel.Add("FErpClsID", item.FErpClsID);
|
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 CompleteSetAnalysisBatch()
|
{
|
LogHelper.Info("齐套分析批量开始准备数据：");
|
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
|
--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_MOMaterReadysBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID
|
--where T1.FMATERIALID in (105773)
|
--join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
|
--where d.FAvailableComplete<>0
|
group by T1.FStockOrgId,T1.FMATERIALID
|
");
|
ListInventoryByStockOrgId = ds.ToModelList<PRD_Inventory>();
|
//需要齐套分析的数据
|
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,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(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_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>();
|
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.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,//占用数量
|
FErpClsID = _item.FErpClsID,
|
/* 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(),//计划总数量
|
FErpClsID = _item.FErpClsID,
|
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;
|
|
|
JArray Fentity = new JArray();
|
foreach (var item in completeAnalysisTempModel)
|
{
|
JObject FBatchModel = new JObject();
|
FBatchModel.Add("FHMainICMOInterIDr", item.FHMainICMOInterIDr);
|
FBatchModel.Add("FHMainICMOEntryID", item.FHMainICMOEntryID);
|
FBatchModel.Add("FHICMOInterID", item.FHICMOInterID);
|
FBatchModel.Add("FHICMOEntryID", item.FHICMOEntryID);
|
FBatchModel.Add("FHMaterID", new JObject() { ["FNUMBER"] = item.FHMaterID });
|
FBatchModel.Add("FHUseQty", item.FHUseQty);
|
FBatchModel.Add("FHStockOrgID", new JObject() { ["FNUMBER"] = item.FHStockOrgID });
|
FBatchModel.Add("FHStockQty", item.FHStockQty);
|
FBatchModel.Add("FHLeftQty", item.FHLeftQty);
|
FBatchModel.Add("FHPRDORGID", new JObject() { ["FNUMBER"] = item.FHStockOrgID });
|
FBatchModel.Add("FUnitDosage", item.FUnitDosage);
|
FBatchModel.Add("FSumPlanCount", item.FSumPlanCount);
|
FBatchModel.Add("FCompleteCount", item.FCompleteCount);
|
FBatchModel.Add("FCompleteCount1", item.FCompleteCount1);
|
FBatchModel.Add("FPRDBillNo", item.FPRDBillNo);
|
FBatchModel.Add("FOwnerTypeId", item.FOwnerTypeId);
|
FBatchModel.Add("FComPlete", item.FComPlete);
|
FBatchModel.Add("FLackCount", item.FLackCount);
|
FBatchModel.Add("FOccupyCount", item.FOccupyCount);
|
FBatchModel.Add("FPlanDate", item.FPlanDate);
|
FBatchModel.Add("FErpClsID", item.FErpClsID);
|
if (item.FOwnerId != null)
|
FBatchModel.Add("FOwnerId", new JObject() { ["FNUMBER"] = item.FOwnerId });
|
Fentity.Add(FBatchModel);
|
if ((i >= 20 || i == completeAnalysisTempModel.Count) && (i % 20 == 0 || i == completeAnalysisTempModel.Count))
|
{
|
LogHelper.Info("齐套分析批量执行，记录循环的当前条数" + i);
|
JObject jsonRoot = new JObject()
|
{
|
["Creator"] = "",
|
["NeedUpDateFields"] = new JArray(),
|
["NeedReturnFields"] = new JArray(),
|
["IsDeleteEntry"] = "false",
|
["SubSystemId"] = "",
|
["IsVerifyBaseDataField"] = "false",
|
["Model"] = Fentity
|
};
|
CloudClient cloudClient = new CloudClient("http://localhost//k3cloud/");
|
var result = cloudClient.BatchSave("paez_CompleteAnalysisTemp", jsonRoot.ToString());
|
JObject saveObj = JObject.Parse(result);
|
string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
|
if (saveIsSuc != "TRUE")
|
{
|
LogHelper.Error(jsonRoot.ToString());
|
}
|
Fentity = new JArray();
|
}
|
|
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 CompleteSetAnalysisBySQL()
|
{
|
LogHelper.Info("齐套分析sql模式开始准备数据：");
|
string sql = "";
|
try
|
{
|
Stopwatch sw = new Stopwatch();
|
sw.Start();//开始计时
|
//清空齐套临时表
|
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
|
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_MOMaterReadysBill 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>();
|
//需要齐套分析的数据
|
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,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(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_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("齐套分析准备数据完成：" + 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($"齐套分析，数据准备完成，保存到Model实体，总行数：{completeAnalysisTempModel.Count}，耗时：" + sw.Elapsed);
|
List<string> sqlList = new List<string>();
|
int jdtmmm = 1;
|
foreach (var item in completeAnalysisTempModel)
|
{
|
sql = string.Format(@"
|
/*dialect*/
|
delete z_JIT_MOMaterReadysBill
|
insert into z_JIT_MOMaterReadysBill (column1)
|
values('')
|
insert into JIT_MOMaterReadysBill
|
(FID, FBILLNO, FDOCUMENTSTATUS, FHMAINICMOINTERIDR, FHMAINICMOENTRYID, FHICMOINTERID, FHICMOENTRYID, FHMATERID, FHSTOCKORGID, FHSTOCKQTY, FHLEFTQTY, FHPRDORGID, FUNITDOSAGE, FSUMPLANCOUNT, FPRDBILLNO, FOWNERID, FOWNERTYPEID, FIDENTIFICAT, FPLANDATE, FCOMPLETE, FLACKCOUNT, FOCCUPYCOUNT, FCOMPLETECOUNT1,FErpClsID)
|
select id, '', 'A', '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', 'BD_OwnerOrg', '','{13}','{14}','{15}','{16}','{17}',{18} from z_JIT_MOMaterReadysBill
|
delete z_JIT_MOMaterReadysBill
|
", item.FHMainICMOInterIDr, item.FHMainICMOEntryID, item.FHICMOInterID, item.FHICMOEntryID, item.FHMaterID, item.FHStockOrgID, item.FHStockQty, item.FHLeftQty, item.FHPRDORGID, item.FUnitDosage, item.FSumPlanCount, item.FPRDBillNo, item.FOwnerId == null ? "0" : item.FOwnerId, item.FPlanDate.ToString("yyyy-MM-dd"), item.FComPlete, item.FLackCount, item.FOccupyCount, item.FCompleteCount1, item.FErpClsID);
|
sqlList.Add(sql);
|
this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(98) / completeAnalysisTempModel.Count) * jdtmmm);
|
jdtmmm++;
|
}
|
DBServiceHelper.ExecuteBatch(Context, sqlList);
|
//执行完成后 更新日计划工单状态
|
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());
|
|
}
|
}
|
|
/// <summary>
|
/// 虚拟齐套
|
/// </summary>
|
public void Xnqt()
|
{
|
LogHelper.Info("虚拟齐套分析sql模式开始准备数据：");
|
string sql = "";
|
try
|
{
|
Stopwatch sw = new Stopwatch();
|
sw.Start();//开始计时
|
LogHelper.Info("虚拟齐套分析开始准备数据：" + sw.Elapsed);
|
//清空齐套临时表
|
DBServiceHelper.Execute(Context, "/*dialect*/ delete JIT_MOMaterReadysBillXn ");
|
////更新状态
|
//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_MOMaterReadysBillXn 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_MOMaterReadysBillXn 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>();
|
//需要虚拟齐套分析的数据
|
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,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(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_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>();
|
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($"虚拟齐套分析，数据准备完成，保存到Model实体，总行数：{completeAnalysisTempModel.Count}，耗时：" + sw.Elapsed);
|
List<string> sqlList = new List<string>();
|
int jdtmmm = 1;
|
foreach (var item in completeAnalysisTempModel)
|
{
|
sql = string.Format(@"
|
/*dialect*/
|
delete z_JIT_MOMaterReadysBillXn
|
insert into z_JIT_MOMaterReadysBillXn (column1)
|
values('')
|
insert into JIT_MOMaterReadysBillXn
|
(FID, FBILLNO, FDOCUMENTSTATUS, FHMAINICMOINTERIDR, FHMAINICMOENTRYID, FHICMOINTERID, FHICMOENTRYID, FHMATERID, FHSTOCKORGID, FHSTOCKQTY, FHLEFTQTY, FHPRDORGID, FUNITDOSAGE, FSUMPLANCOUNT, FPRDBILLNO, FOWNERID, FOWNERTYPEID, FIDENTIFICAT, FPLANDATE, FCOMPLETE, FLACKCOUNT, FOCCUPYCOUNT, FCOMPLETECOUNT1,FErpClsID)
|
select id, '', 'A', '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', 'BD_OwnerOrg', '','{13}','{14}','{15}','{16}','{17}',{18} from z_JIT_MOMaterReadysBillXn
|
delete z_JIT_MOMaterReadysBillXn
|
", item.FHMainICMOInterIDr, item.FHMainICMOEntryID, item.FHICMOInterID, item.FHICMOEntryID, item.FHMaterID, item.FHStockOrgID, item.FHStockQty, item.FHLeftQty, item.FHPRDORGID, item.FUnitDosage, item.FSumPlanCount, item.FPRDBillNo, item.FOwnerId == null ? "0" : item.FOwnerId, item.FPlanDate.ToString("yyyy-MM-dd"), item.FComPlete, item.FLackCount, item.FOccupyCount, item.FCompleteCount1, item.FErpClsID);
|
sqlList.Add(sql);
|
//this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(98) / completeAnalysisTempModel.Count) * jdtmmm);
|
jdtmmm++;
|
}
|
DBServiceHelper.ExecuteBatch(Context, sqlList);
|
// //执行完成后 更新日计划工单状态
|
// DBServiceHelper.Execute(Context, @"
|
///*dialect*/update Sc_WorkBillSortBillSub set FComplete = '未齐套' where FEntryID IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBillXn where isnull(FCOMPLETE,'未齐套') ='未齐套')
|
//");
|
// DBServiceHelper.Execute(Context, @"
|
///*dialect*/update Sc_WorkBillSortBillSub set FComplete = '齐套' where FEntryID NOT IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBillXn where isnull(FCOMPLETE,'未齐套') ='未齐套')
|
//");
|
sw.Stop();//结束计时
|
LogHelper.Info("虚拟齐套分析运行总时长：" + sw.Elapsed);
|
}
|
catch (Exception ex)
|
{
|
this.View.ShowErrMessage("异常：" + ex.Message.ToString());
|
LogHelper.Error("虚拟齐套分析跳出：" + ex.Message.ToString());
|
}
|
finally
|
{
|
this.View.ShowMessage("执行完成！");
|
}
|
}
|
|
/// <summary>
|
/// 查询
|
/// </summary>
|
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 like '%{FSalOrder}%'";
|
//生产订单号
|
string FScOrderMainNo = Convert.ToString((this.Model.GetValue("FScOrderMainNo")));
|
if (!string.IsNullOrEmpty(FScOrderMainNo))
|
sqlwhere += $" and FSCORDERNO like '%{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 ,isnull((select SUM(isnull(FHQTY,0)) from SC_WORKBILLSORTBILLSub t2 where t2.FID = t1.FID and DATEDIFF(D,FHMASTERDATE,GETDATE())>0),0)FQTYLS
|
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<EntryGrid>("FEntity");
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
this.Model.CreateNewEntryRow("FEntity");
|
this.Model.SetValue("FSEQ", i + 1, i);//序号
|
this.Model.SetValue("FMOFBILLTYPE", Convert.ToString(dt.Rows[i]["FMOFBILLTYPE"]), i);//生产订单单据类型
|
this.Model.SetValue("FCompleteSetCount", "", i);//总齐套数量
|
this.Model.SetValue("FProOrderCount", dt.Rows[i]["FHOrderNeedQty"].ToString(), i);//生产订单数量
|
this.Model.SetValue("FSRCBILLENTRYSEQ", dt.Rows[i]["FSRCBILLENTRYSEQ"].ToString(), i);//
|
this.Model.SetValue("FSalOrderCount", dt.Rows[i]["FHOrderQty"].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", Convert.ToDecimal(dt.Rows[i]["FDAYPLANQUANTITY"]) - Convert.ToDecimal(dt.Rows[i]["FQTYLS"]), i);//日计划数量
|
this.Model.SetValue("FQTYLS", dt.Rows[i]["FQTYLS"].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);//GetControl
|
}
|
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());
|
}
|
}
|
/// <summary>
|
/// 编辑
|
/// </summary>
|
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))
|
sqlwhere += $" and FHSeOrderBillNo like '%{FSalOrder}%'";
|
//生产订单号
|
string FScOrderMainNo = Convert.ToString((this.Model.GetValue("FScOrderMainNo")));
|
if (!string.IsNullOrEmpty(FScOrderMainNo))
|
sqlwhere += $" and FSCORDERNO like '%{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 ,isnull((select SUM(isnull(FHQTY,0)) from SC_WORKBILLSORTBILLSub t2 where t2.FID = t1.FID and DATEDIFF(D,FHMASTERDATE,GETDATE())>0),0)FQTYLS
|
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<EntryGrid>("FEntity");
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
this.Model.CreateNewEntryRow("FEntity");
|
this.Model.SetValue("FSEQ", i + 1, i);//序号
|
this.Model.SetValue("FMOFBILLTYPE", Convert.ToString(dt.Rows[i]["FMOFBILLTYPE"]), i);//生产订单单据类型
|
this.Model.SetValue("FCompleteSetCount", "", i);//总齐套数量
|
this.Model.SetValue("FProOrderCount", dt.Rows[i]["FHOrderNeedQty"].ToString(), i);//生产订单数量
|
this.Model.SetValue("FSRCBILLENTRYSEQ", dt.Rows[i]["FSRCBILLENTRYSEQ"].ToString(), i);//
|
this.Model.SetValue("FSalOrderCount", dt.Rows[i]["FHOrderQty"].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", Convert.ToDecimal(dt.Rows[i]["FOrderQuantity"]) - Convert.ToDecimal(dt.Rows[i]["FQTYLS"]), i);//日计划数量
|
this.Model.SetValue("FQTYLS", dt.Rows[i]["FQTYLS"].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());
|
}
|
|
}
|
|
/// <summary>
|
/// 日计划用料清单生成
|
/// </summary>
|
public void DayPlanPPBomBill()
|
{
|
Stopwatch sw = new Stopwatch();
|
sw.Start();//开始计时
|
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
|
WHERE t1.FHQTY>0
|
"
|
);
|
LogHelper.Info("批量，生成日计划用料清单准备数据sql:" + sql);
|
DataSet ds = DBServiceHelper.ExecuteDataSet(Context, sql);
|
DataTable dt = ds.Tables[0];
|
List<DayPlanMaterial> DayPlanMaterial = new List<DayPlanMaterial>();
|
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(),
|
});
|
}
|
LogHelper.Info($"批量，日计划用料清单，数据准备完成，保存到Model实体，总行数：{DayPlanMaterial.Count}，耗时：" + sw.Elapsed);
|
var fidList = DayPlanMaterial.Select(x => x.FEntryID).Distinct().ToList();
|
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());
|
|
}
|
}
|
}
|
sw.Stop();//结束计时
|
LogHelper.Info("批量，生成日计划用料清单运行总时长：" + sw.Elapsed);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Error("批量，生成日计划用料清单+" + ex.Message.ToString());
|
}
|
}
|
public void DayPlanPPBomBillBatch()
|
{
|
Stopwatch sw = new Stopwatch();
|
sw.Start();//开始计时
|
LogHelper.Info("生成日计划用料清单准备数据");
|
//锁定的日计划不生成
|
try
|
{
|
string sql = string.Format(@"
|
/*dialect*/
|
select t1.FID,t1.FSEQ FDayPlanFSEQ,t1.FEntryID,T2.FBILLNO AS FDayPlanFBILLNO,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 FPPBOMENTRYID,isnull(T9.FBillNo,'')FBillNo,t10.FNUMBER FORGNUMBER,t11.FNUMBER FHSOURCENUMBER,FSCRAPRATE,FFIXSCRAPQTY,T9.FID AS DayPlanFID,t2.FSCORDERNO,
|
t3.FMOENTRYID,t3.FBILLNO FPPBOMBILLNO,T4.FSEQ,tc.FOWNERTYPEID,tc.FOWNERID,tc.FEntrustPickOrgId,torg.FNUMBER as FORGNUMBER,t3.FMOID,FIssueType,FMaterialType,td.FNUMBER as FWORKSHOPID
|
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_PRD_PPBOMENTRY_C Tc ON T4.FENTRYID=Tc.FENTRYID
|
join T_ORG_Organizations torg ON tc.FOWNERID = torg.FORGID
|
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
|
left join T_BD_DEPARTMENT td on t3.FWORKSHOPID=td.FDEPTID
|
WHERE t1.FEntryID not in
|
(
|
select distinct FDAYPLANFID from Sc_DayPlanPPBomBillMain where FID IN (
|
select distinct FDAYPLANFID from T_PRD_PICKMTRLDATA)
|
)
|
and T4.FMustQty>0
|
-- (
|
-- select FDayPlanFID FROM Sc_DayPlanPPBomBillMain WHERE FID IN ( select distinct FID from Sc_DayPlanPPBomBillSub WHERE FTranslateQty >0 or FPickedQty >0)
|
-- )
|
"
|
);
|
LogHelper.Info("生成日计划用料清单准备数据sql:" + sql);
|
DataSet ds = DBServiceHelper.ExecuteDataSet(Context, sql);
|
DataTable dt = ds.Tables[0];
|
List<DayPlanMaterial> DayPlanMaterial = new List<DayPlanMaterial>();
|
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["FPPBOMENTRYID"].ToString(),
|
FHSourceID = dr["FHSOURCENUMBER"].ToString(),
|
FNumerator = dr["FNumerator"].ToString(),
|
FDenominator = dr["FDenominator"].ToString(),
|
FFIXSCRAPQTY = dr["FFIXSCRAPQTY"].ToString(),
|
FHQtyScrap = dr["FSCRAPRATE"].ToString(),
|
FMOBILLNO = dr["FSCORDERNO"].ToString(),
|
FMOENTRYID = dr["FMOENTRYID"].ToString(),
|
FPPBOMBILLNO = dr["FPPBOMBILLNO"].ToString(),
|
FPPBOMFSEQ = dr["FSEQ"].ToString(),
|
FOwnerTypeId = dr["FOWNERTYPEID"].ToString(),
|
FOwnerID = dr["FORGNUMBER"].ToString(),
|
FEntrustPickOrgId = dr["FORGNUMBER"].ToString(),
|
FMOID = dr["FMOID"].ToString(),
|
FIssueType = dr["FIssueType"].ToString(),
|
FMaterialType = dr["FMaterialType"].ToString(),
|
FDayPlanFSEQ = dr["FDayPlanFSEQ"].ToString(),
|
FDayPlanFBILLNO = dr["FDayPlanFBILLNO"].ToString(),
|
FWORKSHOPID = dr["FWORKSHOPID"].ToString()
|
});
|
}
|
LogHelper.Info($"日计划用料清单，数据准备完成，保存到Model实体，总行数：{DayPlanMaterial.Count}，耗时：" + sw.Elapsed);
|
var fidList = DayPlanMaterial.Select(x => x.FEntryID).Distinct().ToList();
|
int i = 1;
|
JArray Fentity = new JArray();
|
foreach (var item in fidList)
|
{
|
var _DayPlanMaterial = DayPlanMaterial.Where(x => x.FEntryID == item).ToList();
|
JObject model = new JObject();
|
|
if (!string.IsNullOrEmpty(_DayPlanMaterial[0].FID))
|
model.Add("FID", _DayPlanMaterial[0].FID);
|
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);//日计划工单内码
|
model.Add("FDayPlanFSEQ", _DayPlanMaterial[0].FDayPlanFSEQ);//日计划工单明细行号
|
model.Add("FDayPlanFBillNo", _DayPlanMaterial[0].FDayPlanFBILLNO);//日计划工单单据编号
|
JArray Entry = new JArray();
|
int FSEQ = 1;
|
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);//变动损耗率%
|
ModelEnty.Add("FMOBILLNO", _item.FMOBILLNO);//
|
ModelEnty.Add("FMOENTRYIDINT", _item.FMOENTRYID);//
|
ModelEnty.Add("FPPBOMBILLNO", _item.FPPBOMBILLNO);//
|
ModelEnty.Add("FPPBOMFSEQINT", _item.FPPBOMFSEQ);//
|
ModelEnty.Add("FOwnerTypeId", _item.FOwnerTypeId);//
|
ModelEnty.Add("FOwnerID", new JObject() { ["Fnumber"] = _item.FOwnerID });//
|
ModelEnty.Add("FEntrustPickOrgId", new JObject() { ["Fnumber"] = _item.FEntrustPickOrgId });//
|
ModelEnty.Add("FMOID", _item.FMOID);//
|
ModelEnty.Add("FISSUETYPE", _item.FIssueType);//
|
ModelEnty.Add("FMaterialType", _item.FMaterialType);//
|
ModelEnty.Add("FWORKSHOPID", new JObject() { ["Fnumber"] = _item.FWORKSHOPID });//
|
|
Entry.Add(ModelEnty);
|
FSEQ++;
|
|
}
|
model.Add("FEntity", Entry);
|
|
Fentity.Add(model);
|
if ((i >= 20 || i == fidList.Count) && (i % 20 == 0 || i == fidList.Count))
|
{
|
LogHelper.Info("日计划用料清单批量执行，记录循环的当前条数" + i);
|
JObject jsonRoot = new JObject()
|
{
|
["Creator"] = "",
|
["NeedUpDateFields"] = new JArray(),
|
["NeedReturnFields"] = new JArray(),
|
["IsDeleteEntry"] = "true",
|
["SubSystemId"] = "",
|
["IsVerifyBaseDataField"] = "false",
|
["Model"] = Fentity
|
};
|
CloudClient cloudClient = new CloudClient("http://localhost//k3cloud/");
|
var result = cloudClient.BatchSave("Paez_Sc_DayPlanPPBomBill", jsonRoot.ToString());
|
JObject saveObj = JObject.Parse(result);
|
string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
|
if (saveIsSuc == "TRUE")
|
{
|
var Success = saveObj["Result"]["ResponseStatus"]["SuccessEntitys"].ToString().TrimStart('{').TrimEnd('}');
|
var allList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(Success);
|
List<string> ls = new List<string>();
|
foreach (var it in allList)
|
{
|
it.TryGetValue("Id", out string value);
|
ls.Add(value);
|
}
|
var resultAA = String.Join(",", ls.ToArray());
|
string resSql = string.Format(@"/*dialect*/ update Sc_WorkBillSortBillSub set Sc_WorkBillSortBillSub.FDAYPLANPPBOMBILLNO = Sc_DayPlanPPBomBillMain.FBILLNO from Sc_WorkBillSortBillSub join Sc_DayPlanPPBomBillMain on Sc_WorkBillSortBillSub.FEntryID = Sc_DayPlanPPBomBillMain.FDayPlanFID where Sc_DayPlanPPBomBillMain.FID in ({0})", resultAA);
|
DBServiceHelper.Execute(Context, resSql);
|
|
}
|
if (saveIsSuc != "TRUE")
|
{
|
LogHelper.Error("日计划用料清单列表生成失败:" + jsonRoot.ToString());
|
this.View.ShowMessage(saveObj["Result"]["ResponseStatus"]["Errors"][0]["Message"].ToString());
|
}
|
Fentity = new JArray();
|
}
|
i++;
|
}
|
}
|
sw.Stop();//结束计时
|
LogHelper.Info("生成日计划用料清单运行总时长：" + sw.Elapsed);
|
}
|
catch (Exception ex)
|
{
|
LogHelper.Error("日计划用料清单+" + ex.Message.ToString());
|
}
|
}
|
|
/// <summary>
|
/// 提料计划
|
/// </summary>
|
public void Extraction()
|
{
|
LogHelper.Info("[提料计划准备阶段]：测试测试测试测试测试测试测试测试");
|
try
|
{
|
Stopwatch sw = new Stopwatch();
|
sw.Start();//开始计时
|
string sql = string.Format(@"/*dialect*/ select distinct FHICMOINTERID FID from JIT_MOMaterReadysBill ");
|
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);
|
LogHelper.Info("[提料计划准备阶段]提料计划预处理sql：" + 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')
|
and t1.FBillTypeID in('83d822ca3e374b4ab01e5dd46a0062bd','6d01d059713d42a28bb976c90a121142')
|
";
|
List<PurchaseInventory> PurchaseInventory = DBServiceHelper.ExecuteDataSet(Context, sql).ToModelList<PurchaseInventory>();
|
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);
|
sql = string.Format(@"
|
/*dialect*/
|
SELECT T1.FID,FHMASTERDATE,t1.FEntryID,T1.FBILLNO, FNeedQty,(FLeadtime+FLeadTtime2)FFIXLEADTIME,t5.FMAXPOQTY,FJITBatch FMINPOQTY,FJITMATERIALGROUP,FJITMaterielDemand,FJITSafeStock,t1.FMATERIALID,t6.FNumber,NeedQty, FStockOrgId ,FErpClsID
|
FROM (
|
select FHICMOINTERID FID,FHICMOENTRYID FEntryID,FPlanDate FHMASTERDATE,FLackCount NeedQty,FHMATERID FMATERIALID,FHStockOrgID FStockOrgId,FPRDBILLNO FBillNo,FSumPlanCount FNeedQty from JIT_MOMaterReadysBill a
|
right join (select max(FID)FID from JIT_MOMaterReadysBill GROUP BY FHICMOENTRYID,FHMATERID) b on a.FID = b.FID
|
)T1
|
JOIN T_BD_MATERIAL T6 ON T1.FMATERIALID = T6.FMATERIALID
|
join t_BD_MaterialBase t4 on T1.FMATERIALID = t4.FMATERIALID
|
join t_BD_MaterialPlan t5 on T1.FMATERIALID = t5.FMATERIALID
|
WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE) >=0
|
AND FERPCLSID in ('1','3')
|
AND T1.NeedQty>0
|
AND T1.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> DayPlanPpbom = ds.ToModelList<DayPlanPpbom>();
|
//物料+库存组织分类
|
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 == "163165").ToList();
|
LogHelper.Info("[提料计划准备阶段]总物料行数" + MaterialIDList.Count);
|
//提料计划数据集临时存储集合
|
List<PODemandPlanTemp> PODemandPlanTemp = new List<PODemandPlanTemp>();
|
//sql集合 更新采购订单占用的提料计划数量
|
List<string> sqlList = new List<string>();
|
//MaterialIDList = new List<string> { "105773" };
|
foreach (var item in MaterialIDList)
|
{
|
//当前物料没有采购订单时 直接跳出
|
List<PurchaseInventory> _PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).OrderBy(x => x.FENTRYID).ToList();
|
if (_PurchaseInventory.Count == 0)
|
{
|
LogHelper.Info("[提料计划开始]当前物料无采购信息：" + item.FMATERIALID + " 库存组织：" + item.FStockOrgId);
|
continue;
|
}
|
//LogHelper.Info(item.FMATERIALID.ToString());
|
//当前物料和库存组织对应的需进行提料计划的数据
|
List<DayPlanPpbom> _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 = 0;
|
//记录在最小采购量需求下的日计划明细FentyrID
|
List<int> FEntryIdList = new List<int>();
|
foreach (var _item in _DayPlanPpbom)
|
{
|
i++;
|
_PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).OrderBy(x => x.FENTRYID).ToList();
|
if (_PurchaseInventory.Count == 0)
|
{
|
//LogHelper.Info("[提料计划开始]当前物料明细无采购信息：" + item.FMATERIALID + " 库存组织：" + item.FStockOrgId);
|
break;
|
}
|
|
if (NeedQty == 0)
|
DATE = Convert.ToDateTime(_item.FHMASTERDATE).AddDays(-FFIXLEADTIME);
|
FEntryIdList.Add(_item.FENTRYID);
|
NeedQty += _item.NeedQty;
|
|
//LogHelper.Info("记录明细id：" + _item.FENTRYID + "");
|
//LogHelper.Info("总需求量：" + NeedQty + "");
|
//LogHelper.Info("最小起订量：" + FMINPOQTY + "");
|
if (NeedQty <= FMINPOQTY)
|
{
|
LogHelper.Info("[提料计划]当前物料明细无采购信息：" + item.FMATERIALID + ",数量：" + NeedQty + "，最小起订量：" + FMINPOQTY + "");
|
}
|
|
if ((FJITmaterialGroup == "总量控制规格类" || FJITmaterialGroup == "订单专用个性类") && FEntryIdList.Count < Convert.ToInt32(FJITMaterielDemand) && i < _DayPlanPpbom.Count) //订单专用个性类
|
continue;
|
|
//if (NeedQty >= FMINPOQTY)
|
//{
|
foreach (var Purchase in _PurchaseInventory)
|
{
|
//LogHelper.Info("采购订单号测试：" + Purchase.FBillNo);
|
//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,
|
FErpClsID = DayPlanPpbomls.FErpClsID,
|
|
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<int> mmm = new List<int>();
|
foreach (int id in FEntryIdList)
|
{
|
var DayPlanPpbomls = DayPlanPpbom.Where(c => c.FENTRYID == id && c.FMATERIALID == _item.FMATERIALID).FirstOrDefault();
|
decimal Qty = DayPlanPpbomls.NeedQty;//订单数量
|
//if (Qty == 0)
|
// LogHelper.Info("订单数量为0");
|
if (Qty > _NeedQty)
|
Qty = _NeedQty; //订单数量>采购订单数量 取采购订单
|
|
//if (_NeedQty == 0)
|
// LogHelper.Info("订单数量为0");
|
|
_NeedQty = _NeedQty - Qty;
|
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,
|
FErpClsID = DayPlanPpbomls.FErpClsID,
|
|
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 = 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<int> mmm = new List<int>();
|
// 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<int> mmm = new List<int>();
|
// 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
|
|
}
|
}
|
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);
|
int tlmmm = 1;
|
JArray FinalyResult = new JArray();
|
foreach (var item in PODemandPlanList)
|
{
|
DateTime date = item.FHMASTERDATE;
|
string PurchseFNUMBER = item.PurchseFNUMBER;
|
JObject model = new JObject();
|
model.Add("FHDate", date);
|
model.Add("FSettleCurrId", new JObject() { ["Fnumber"] = "PRE001" });
|
model.Add("FSupplierID", new JObject() { ["Fnumber"] = PurchseFNUMBER });
|
|
JArray Fentity = new JArray();
|
List<PODemandPlanTemp> _PODemandPlanList = PODemandPlanTemp.Where(x => x.FHMASTERDATE == date && x.PurchseFNUMBER == PurchseFNUMBER).ToList();
|
int FSEQ = 1;
|
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
|
FentityModel.Add("FERPCLSID", _item.FErpClsID); //发料方式
|
//FentityModel.Add("FSEQ", FSEQ); //发料方式
|
FentityModel.Add("FUnitID", new JObject() { ["Fnumber"] = "Pcs" }); //发料方式
|
|
Fentity.Add(FentityModel);
|
FSEQ++;
|
}
|
model.Add("FEntity", Fentity);
|
FinalyResult.Add(model);
|
if ((tlmmm >= 20 || tlmmm == PODemandPlanList.Count) && (tlmmm % 20 == 0 || tlmmm == PODemandPlanList.Count))
|
{
|
LogHelper.Info("提料计划新增批量执行，记录循环的当前条数" + tlmmm);
|
JObject jsonRoot = new JObject()
|
{
|
["Creator"] = "",
|
["NeedUpDateFields"] = new JArray(),
|
["NeedReturnFields"] = new JArray(),
|
["IsDeleteEntry"] = "false",
|
["SubSystemId"] = "",
|
["IsVerifyBaseDataField"] = "false",
|
["Model"] = FinalyResult
|
};
|
CloudClient cloudClient = new CloudClient("http://localhost//k3cloud/");
|
var result = cloudClient.BatchSave("paez_PODemandPlan", jsonRoot.ToString());
|
JObject saveObj = JObject.Parse(result);
|
string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
|
if (saveIsSuc != "TRUE")
|
{
|
LogHelper.Error(jsonRoot.ToString());
|
}
|
FinalyResult = new JArray();
|
}
|
tlmmm++;
|
}
|
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();
|
}
|
/// <summary>
|
/// 1.锁库，具体字段根据业务单据决定，‘Inv’表示即时库存，‘FInvDetailID’为即时库存ID
|
/// </summary>
|
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<PRD_Inventory>();
|
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<PRD_PPBOM>();
|
//当前最大齐套数 下层需求数量/父级需求数量=单位用料
|
//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<string> sqlList = new List<string>();
|
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<LockStockArgs> list = new List<LockStockArgs>();
|
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());
|
}
|
}
|
/// <summary>
|
/// 释放
|
/// </summary>
|
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
|
