日计划用料清单增加条件

王 垚

2021-02-27 ebac4c0aaf96258fc67cd3ad6b3d1ac1991df632

 src/BLL/Demo.BillView/PRD/Pro_WorkBillPlatform.cs

@@ -1,9 +1,8 @@
using Demo.Model.Model;
using Demo.Model.Model.PODemandPlan;
using K3Cloud.Extend.Utils;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core;
using Kingdee.BOS.Core.Bill.PlugIn;
using Kingdee.BOS.Core.Bill;
using Kingdee.BOS.Core.Const;
using Kingdee.BOS.Core.DynamicForm;
using Kingdee.BOS.Core.DynamicForm.PlugIn;
@@ -19,25 +18,16 @@
using Kingdee.BOS.Util;
using Kingdee.K3.Core.SCM.STK;
using Kingdee.K3.SCM.ServiceHelper;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Web;
using System.Windows.Documents;
using ZD.Cloud.Logger;
using ZD.Cloud.WebApi;
using ZD.Share.Common;
@@ -52,11 +42,33 @@
        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();
@@ -70,7 +82,9 @@
                }
                if (e.BarItemKey.ToUpper() == "TBSEARCH")
                {
                    isSearched = false;
                    SearchList();
                    isSearched = true;
                }
                if (e.BarItemKey.ToUpper() == "TBLOCK")
                {
@@ -81,13 +95,25 @@
                {
                    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")
@@ -117,6 +143,7 @@
                {
                    Release();
                }

            }
            catch (Exception ex)
            {
@@ -128,7 +155,47 @@
            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>
@@ -146,17 +213,47 @@
                //生产计划平台拖期明细跳转
                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("FHMainICMOInterID", Convert.ToString(this.View.Model.GetValue("FHMainSourceInterID", e.Row).ToString()));
                formPa.CustomParams.Add("FHMainICMOEntryID", Convert.ToString(this.View.Model.GetValue("FHMainSourceEntryID", e.Row).ToString()));
                formPa.CustomParams.Add("FHICMOInterID", Convert.ToString(this.View.Model.GetValue("FDayPlanWorkID", e.Row).ToString()));
                formPa.CustomParams.Add("FMatrailId_Id", Convert.ToString((this.View.Model.GetValue("FMatrailId", e.Row) as DynamicObject)["Id"].ToString()));

                this.View.ShowForm(formPa);
            }

        }
        /// <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>
@@ -164,13 +261,20 @@
        /// </summary>
        public void ExportExcel()
        {
            string[] Title = new string[] {"销售订单号", "生产订单号", "生产组织", "生产车间", "生产资源", "产品代码", "产品名称", "规格型号", "计量单位",
                "批次号","销售订单数量","生产订单数量","总齐套数量","交货日期","订单等级","T0","T1","T2","T3","T4","T5","T6","T7",
            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"};

                "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;
@@ -219,7 +323,7 @@

            //设置列名
            HSSFRow row = (HSSFRow)sheet.CreateRow(0);
            for (int i = 0; i < Title.Length; i++)
            for (int i = 0; i < Title.Count; i++)
            {
                ICell cell = (ICell)row.CreateCell(i);
                var rowName = Title[i];
@@ -229,7 +333,7 @@

            //单元格赋值
            //列宽自适应，只对英文和数字有效
            for (int i = 0; i <= Title.Length; i++)
            for (int i = 0; i <= Title.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
@@ -272,16 +376,16 @@
            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("操作失败，联系管理员！");
            //if (result > 0)
            //{
            //锁定后生成日计划用料清单 及 提料计划
            DayPlanPPBomBillBatch();
            Extraction();
            SearchList();
            this.View.ShowMessage("操作成功");
            //}
            //else
            //    this.View.ShowMessage("操作失败，联系管理员！");
        }
        /// <summary>
        /// 解锁
@@ -300,72 +404,80 @@
                sql = $"/*dialect*/update t1 set t1.FHLockedSub=0 from Sc_WorkBillSortBillSub t1  join Sc_WorkBillSortBillMain t2 on  t1.FID = t2.FID  where  FHMASTERDATE between  DATEADD(day,-1,getdate()) and DATEADD(day,{day - 1},getdate())   and t1.FID = { Convert.ToString(item["FDayPlanWorkID"])} ";
                sqlList.Add(sql);
            }
            int result = DBServiceHelper.ExecuteBatch(Context, sqlList);
            if (result > 0)
            {
                SearchList();
                this.View.ShowMessage("操作成功");
            }
            else
                this.View.ShowMessage("操作失败，联系管理员！");
            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 = DateTime.Now;

            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)
            {
                sql = $"/*dialect*/ update Sc_WorkBillSortBillMain set FOrderQuantity={current["FOrderQuantity"]},FDayPlanQuantity={current["FDayPlanQuantity"]},FNoScheduled={current["FNoScheduled"]} where FID = {current["FDayPlanWorkID"]}";
                sqlList.Add(sql);
                DateTime FBzDate = Convert.ToDateTime(Convert.ToDateTime(current["FBzDate"]));
                DateTime end = Convert.ToDateTime(Convert.ToDateTime(current["FBzDate"]).AddDays(60).ToShortDateString());//最大日期
                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++)
                {
                    DateTime start = Convert.ToDateTime(date.AddDays(i).ToShortDateString());
                    TimeSpan getDay = end.Subtract(start);
                    //编制日期+61天>date.AddDays(i).ToShortDateString() continue
                    if (getDay.Days < 0)
                        break;
                    sql = $"/*dialect*/update t1 set FHQTY='{Convert.ToString(current["FT" + "" + i + ""])}' from Sc_WorkBillSortBillSub t1 join " +
                        $" Sc_WorkBillSortBillMain t2 on  t1.FID = t2.FID where FHMASTERDATE='{date.AddDays(i).ToShortDateString()}' and t2.FID='{Convert.ToString(current["FDayPlanWorkID"])}'";
                    sqlList.Add(sql);
                    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("数据储存失败！请联系管理员！");
                }
            }

            int result = DBServiceHelper.ExecuteBatch(Context, sqlList);
            if (result > 0)
            {
                this.View.ShowMessage("操作成功");
                SearchList();
            }
            else
                this.View.ShowMessage("操作失败，联系管理员！");

            this.View.ShowMessage("操作成功");
            SearchList();
        }
        /// <summary>
        /// 齐套分析
@@ -415,7 +527,6 @@
            },
            (t) => { });
        }

        /// <summary>
        /// 齐套分析
        /// </summary>
@@ -638,7 +749,7 @@
                    if (item.FOwnerId != null)
                        jsonModel.Add("FOwnerId", new JObject() { ["FNUMBER"] = item.FOwnerId });
                    jsonRoot.Add("Model", jsonModel);
                    CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/");
                    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();
@@ -889,7 +1000,7 @@
                            ["IsVerifyBaseDataField"] = "false",
                            ["Model"] = Fentity
                        };
                        CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/");
                        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();
@@ -946,8 +1057,8 @@
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
join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
where d.FAvailableComplete<>0
--where T1.FMATERIALID in (105773)
");
                ListInventory = ds.ToModelList<PRD_Inventory>();
@@ -965,8 +1076,8 @@
) 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
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
");
@@ -1136,6 +1247,233 @@

            }
        }

        /// <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>
@@ -1151,11 +1489,11 @@
            //销售订单号
            string FSalOrder = Convert.ToString((this.Model.GetValue("FSalOrder")));
            if (!string.IsNullOrEmpty(FSalOrder))
                sqlwhere += $" and FHSeOrderBillNo='{FSalOrder}'";
                sqlwhere += $" and FHSeOrderBillNo like '%{FSalOrder}%'";
            //生产订单号
            string FScOrderMainNo = Convert.ToString((this.Model.GetValue("FScOrderMainNo")));
            if (!string.IsNullOrEmpty(FScOrderMainNo))
                sqlwhere += $" and FSCORDERNO='{FScOrderMainNo}'";
                sqlwhere += $" and FSCORDERNO like '%{FScOrderMainNo}%'";
            //物料
            if ((this.Model.GetValue("FMatralId") as DynamicObject) != null)
            {
@@ -1169,7 +1507,8 @@
                sqlwhere += $" and FPreparatDate='{FPreparatDate}'";
            try
            {
                string sql = @"/*dialect*/ select t1.*,isnull((select SUM(isnull(FNotProductNum,0)) from SC_WORKBILLSORTBILLSub t2 where t2.FID = t1.FID and DATEDIFF(D,FHMASTERDATE,GETDATE())>0),0)FNotProductNum from  Sc_WorkBillSortBillMain t1
                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];
@@ -1180,8 +1519,10 @@
                {
                    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);//计量单位
@@ -1200,7 +1541,8 @@
                    this.Model.SetValue("FProductWorkShopId", dt.Rows[i]["FHWorkShopID"].ToString(), i);//生产车间
                    this.Model.SetValue("FCompleteSetCount", dt.Rows[i]["FCompleteCount"].ToString(), i);//齐套数量
                    this.Model.SetValue("FOrderQuantity", dt.Rows[i]["FOrderQuantity"].ToString(), i);//生产订单数量
                    this.Model.SetValue("FDayPlanQuantity", dt.Rows[i]["FDayPlanQuantity"].ToString(), i);//日计划数量
                    this.Model.SetValue("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();
@@ -1216,7 +1558,7 @@
                        if (dr["FHLockedSub"].ToString() == "1")
                            this.View.GetFieldEditor("FT" + getDay.Days.ToString(), i).Enabled = false; //单据体行锁定字段
                    }
                    this.View.InvokeFieldUpdateService("FMatrailId", i);//调用数量值更新
                    this.View.InvokeFieldUpdateService("FMatrailId", i);//GetControl
                }
                this.View.UpdateView();
                for (int j = 0; j < dt.Rows.Count; j++)
@@ -1270,20 +1612,12 @@
            }
            //销售订单号
            string FSalOrder = Convert.ToString((this.Model.GetValue("FSalOrder")));
            if (string.IsNullOrEmpty(FSalOrder))
            {

            }
            else
                sqlwhere += $" and FHSeOrderBillNo='{FSalOrder}'";
            if (!string.IsNullOrEmpty(FSalOrder))
                sqlwhere += $" and FHSeOrderBillNo like '%{FSalOrder}%'";
            //生产订单号
            string FScOrderMainNo = Convert.ToString((this.Model.GetValue("FScOrderMainNo")));
            if (string.IsNullOrEmpty(FScOrderMainNo))
            {

            }
            else
                sqlwhere += $" and FSCORDERNO='{FScOrderMainNo}'";
            if (!string.IsNullOrEmpty(FScOrderMainNo))
                sqlwhere += $" and FSCORDERNO like '%{FScOrderMainNo}%'";

            //物料
            if ((this.Model.GetValue("FMatralId") as DynamicObject) != null)
@@ -1298,7 +1632,8 @@
                sqlwhere += $" and FPreparatDate='{FPreparatDate}'";
            try
            {
                string sql = @"/*dialect*/ select t1.*,isnull((select SUM(isnull(FNotProductNum,0)) from SC_WORKBILLSORTBILLSub t2 where t2.FID = t1.FID and DATEDIFF(D,FHMASTERDATE,GETDATE())>0),0)FNotProductNum from  Sc_WorkBillSortBillMain t1
                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];
@@ -1309,8 +1644,10 @@
                {
                    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);//计量单位
@@ -1329,7 +1666,8 @@
                    this.Model.SetValue("FProductWorkShopId", dt.Rows[i]["FHWorkShopID"].ToString(), i);//生产车间
                    this.Model.SetValue("FCompleteSetCount", dt.Rows[i]["FCompleteCount"].ToString(), i);//齐套数量
                    this.Model.SetValue("FOrderQuantity", dt.Rows[i]["FOrderQuantity"].ToString(), i);//生产订单数量
                    this.Model.SetValue("FDayPlanQuantity", dt.Rows[i]["FDayPlanQuantity"].ToString(), i);//日计划数量
                    this.Model.SetValue("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();
@@ -1480,7 +1818,7 @@
                            ["Model"] = model
                        };

                        CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/");
                        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();
@@ -1509,11 +1847,14 @@
            {
                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
 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
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
@@ -1522,6 +1863,9 @@
 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
 and t9.FID not in(
 select distinct FID from  Sc_DayPlanPPBomBillSub WHERE FTranslateQty >0 or FPickedQty >0
 )
"
);
                LogHelper.Info("生成日计划用料清单准备数据sql:" + sql);
@@ -1548,12 +1892,24 @@
                            FHQtyMust = dr["FHQtyMust"].ToString(),
                            FHQtySub = dr["FHQtyMust"].ToString(),
                            FHPPBomInterID = dr["FPPBOM"].ToString(),
                            FHPPBomEntryID = dr["FENTRYID"].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()
                        });
                    }
                    LogHelper.Info($"日计划用料清单，数据准备完成，保存到Model实体，总行数：{DayPlanMaterial.Count}，耗时：" + sw.Elapsed);
@@ -1564,6 +1920,9 @@
                    {
                        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 });//物料
@@ -1571,7 +1930,10 @@
                        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();
@@ -1587,11 +1949,23 @@
                            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("FSEQ", FSEQ);//  
                            Entry.Add(ModelEnty);
                            FSEQ++;

                        }
                        model.Add("FEntity", Entry);
                        //if (!string.IsNullOrEmpty(_DayPlanMaterial[0].FID))
                        //    model.Add("FID", _DayPlanMaterial[0].FID);

                        Fentity.Add(model);
                        if ((i >= 20 || i == fidList.Count) && (i % 20 == 0 || i == fidList.Count))
                        {
@@ -1601,18 +1975,40 @@
                                ["Creator"] = "",
                                ["NeedUpDateFields"] = new JArray(),
                                ["NeedReturnFields"] = new JArray(),
                                ["IsDeleteEntry"] = "false",
                                ["IsDeleteEntry"] = "true",
                                ["SubSystemId"] = "",
                                ["IsVerifyBaseDataField"] = "false",
                                ["Model"] = Fentity
                            };
                            CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/");
                            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);
                                //JArray array = JArray.Parse(m);
                                //for (int mm = 0; mm < array.Count; mm++)
                                //{
                                //    JObject b = JObject.Parse(array[mm].ToString());
                                //    var id = b["ID"].ToString();
                                //}

                            }
                            if (saveIsSuc != "TRUE")
                            {
                                LogHelper.Error(jsonRoot.ToString());
                                LogHelper.Error("日计划用料清单列表生成失败:" + jsonRoot.ToString());
                                this.View.ShowMessage(saveObj["Result"]["ResponseStatus"]["Errors"][0]["Message"].ToString());
                            }
                            Fentity = new JArray();
                        }
@@ -1656,13 +2052,14 @@
                //采购订单数据
                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
 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);
@@ -1685,7 +2082,7 @@
", 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 
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
@@ -1694,7 +2091,7 @@
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=1 
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}) 
@@ -1705,7 +2102,7 @@
                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 == "162305").ToList();
                var _MaterialIDList = MaterialIDList.Where(p => p.FMATERIALID == "163165").ToList();
                LogHelper.Info("[提料计划准备阶段]总物料行数" + MaterialIDList.Count);
                //提料计划数据集临时存储集合
                List<PODemandPlanTemp> PODemandPlanTemp = new List<PODemandPlanTemp>();
@@ -1715,14 +2112,14 @@
                foreach (var item in MaterialIDList)
                {
                    //当前物料没有采购订单时 直接跳出
                    List<PurchaseInventory> _PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).ToList();
                    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;//提前期
@@ -1737,7 +2134,7 @@
                    foreach (var _item in _DayPlanPpbom)
                    {
                        i++;
                        _PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).ToList();
                        _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);
@@ -1749,9 +2146,9 @@
                        FEntryIdList.Add(_item.FENTRYID);
                        NeedQty += _item.NeedQty;

                        LogHelper.Info("记录明细id：" + _item.FENTRYID + "");
                        LogHelper.Info("总需求量：" + NeedQty + "");
                        LogHelper.Info("最小起订量：" + FMINPOQTY + "");
                        //LogHelper.Info("记录明细id：" + _item.FENTRYID + "");
                        //LogHelper.Info("总需求量：" + NeedQty + "");
                        //LogHelper.Info("最小起订量：" + FMINPOQTY + "");
                        if (NeedQty <= FMINPOQTY)
                        {
                            LogHelper.Info("[提料计划]当前物料明细无采购信息：" + item.FMATERIALID + ",数量：" + NeedQty + "，最小起订量：" + FMINPOQTY + "");
@@ -1764,7 +2161,8 @@
                        //{
                        foreach (var Purchase in _PurchaseInventory)
                        {
                            LogHelper.Info("物料：" + Purchase.FMATERIALID + ",采购订单数量：" + Purchase.FQTY);
                            LogHelper.Info("采购订单号测试：" + Purchase.FBillNo);
                            //LogHelper.Info("物料：" + Purchase.FMATERIALID + ",采购订单数量：" + Purchase.FQTY);
                            if (Purchase.FQTY >= NeedQty)
                            {
                                foreach (int id in FEntryIdList)
@@ -1781,6 +2179,7 @@
                                        FNumber = DayPlanPpbomls.FNumber,
                                        OLDDATE = DayPlanPpbomls.FHMASTERDATE,
                                        FFIXLEADTIME = FFIXLEADTIME,
                                        FErpClsID = DayPlanPpbomls.FErpClsID,

                                        PurchseFID = Purchase.FID,
                                        PurchseFentryID = Purchase.FENTRYID,
@@ -1813,13 +2212,13 @@
                                {
                                    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 == 0)
                                    //    LogHelper.Info("订单数量为0");
                                    if (Qty > _NeedQty)
                                        Qty = _NeedQty; //订单数量>采购订单数量 取采购订单

                                    if (_NeedQty == 0)
                                        LogHelper.Info("订单数量为0");
                                    //if (_NeedQty == 0)
                                    //    LogHelper.Info("订单数量为0");

                                    _NeedQty = _NeedQty - Qty;
                                    PODemandPlanTemp.Add(new Demo.Model.Model.PODemandPlan.PODemandPlanTemp
@@ -1833,6 +2232,7 @@
                                        FNumber = DayPlanPpbomls.FNumber,
                                        OLDDATE = DayPlanPpbomls.FHMASTERDATE,
                                        FFIXLEADTIME = FFIXLEADTIME,
                                        FErpClsID = DayPlanPpbomls.FErpClsID,

                                        PurchseFID = Purchase.FID,
                                        PurchseFentryID = Purchase.FENTRYID,
@@ -1986,10 +2386,12 @@
                    string PurchseFNUMBER = item.PurchseFNUMBER;
                    JObject model = new JObject();
                    model.Add("FHDate", date);
                    model.Add("FHRemark", "生产订单号：" + "测试呢呢");
                    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();
@@ -2007,7 +2409,12 @@
                        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);
@@ -2024,7 +2431,7 @@
                            ["IsVerifyBaseDataField"] = "false",
                            ["Model"] = FinalyResult
                        };
                        CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/");
                        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();