性能优化 更改为批量保存方式插入

王 垚

2021-02-01 74670f25e311baeb25aa81190e228596fcd5af03

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

@@ -97,8 +97,8 @@
                {
                    try
                    {
                        COMPLETE();
                        DayPlanPPBomBill();
                        CompleteSetAnalysisBySQL();
                        DayPlanPPBomBillBatch();
                        Extraction();
                    }
                    catch (Exception ex)
@@ -280,7 +280,7 @@
            if (result > 0)
            {
                //锁定后生成日计划用料清单 及 提料计划
                DayPlanPPBomBill();
                DayPlanPPBomBillBatch();
                Extraction();
                SearchList();
                this.View.ShowMessage("操作成功");
@@ -403,7 +403,9 @@
                {
                    // 需要捕获错误，以确保处理结束时，关闭进度滚动界面
                    // 引入功能实际处理函数
                    this.CompleteSetAnalysis();
                    //this.CompleteSetAnalysis();
                    this.CompleteSetAnalysisBySQL();

                }
                finally
                {
@@ -420,310 +422,7 @@
            },
            (t) => { });
        }
        public void COMPLETEMAIN()
        {
            //获取当前即时库存 按照物料+货主+库存 展现
            DataSet ds = DBServiceHelper.ExecuteDataSet(Context, @" /*dialect*/  select DISTINCT (a.FBASEQTY- ISNULL(FHUseQty,0)) as FBASEQTY,a.FMATERIALID,a.FStockOrgId,a.FOwnerId from T_STK_Inventory a 
left join 
 (
  select FHMaterID,FHUseQty,FHStockOrgID,FOwnerId from JIT_MOMaterReadysBill
 where FIdentificat = 2 
 ) b on a.FMATERIALID=b.FHMaterID and a.FStockOrgId=b.FHStockOrgID and a.FOwnerId = b.FOwnerId 
 order by FBASEQTY");
            ListInventory = ds.ToModelList<PRD_Inventory>();

            ds = DBServiceHelper.ExecuteDataSet(Context, @" /*dialect*/ select   sum(FBASEQTY)FBASEQTY,FMATERIALID,FStockOrgId from 
 (
  select DISTINCT (a.FBASEQTY- ISNULL(FHUseQty,0)) as FBASEQTY,a.FMATERIALID,a.FStockOrgId,a.FOwnerId from T_STK_Inventory a 
left join 
 (
  select FHMaterID,FHUseQty,FHStockOrgID,FOwnerId from JIT_MOMaterReadysBill
 where FIdentificat = 2 
 ) b on a.FMATERIALID=b.FHMaterID and a.FStockOrgId=b.FHStockOrgID and a.FOwnerId = b.FOwnerId 
 
 ) TT
group by FMATERIALID, FStockOrgId ");
            ListInventoryByStockOrgId = ds.ToModelList<PRD_Inventory>();

            //获取单据体信息
            Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity");
            //单据体信息转换为列表集合
            DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity);
            EntryGrid grid = this.View.GetControl<EntryGrid>("FEntity");

            if (entityDataObjoct.Count > 0)
            {
                int m = 0;

                //删除齐套数量
                foreach (DynamicObject current in entityDataObjoct)
                {
                    //FDayPlanWorkID日计划工单ID去删除现有的齐套分析数据
                    DBServiceHelper.Execute(Context, "/*dialect*/delete JIT_MOMaterReadysBill where FHICMOInterID = '" + current["FDayPlanWorkID"].ToString() + "'");
                }

                foreach (DynamicObject current in entityDataObjoct)
                {
                    //计划总数量
                    decimal SumPlan = 0;
                    for (int i = 0; i <= 60; i++)
                    {
                        SumPlan += Convert.ToDecimal(current["FT" + "" + i + ""]);
                    }

                    double Sum = 100;
                    Sum = Convert.ToDouble(SumGetComplete(current["FMatrailId_Id"].ToString(), current["FHMainSourceInterID"].ToString(), current["FHMainSourceEntryID"].ToString(), current["FDayPlanWorkID"].ToString(), SumPlan, current["FORGID_Id"].ToString()));
                    this.View.Model.SetValue("FCompleteSetCount", Sum, m);
                    this.View.UpdateView("FCompleteSetCount");
                    //保存齐套数 
                    DBServiceHelper.Execute(Context, "/*dialect*/ update Sc_WorkBillSortBillMain set FCompleteCount='" + Sum + "',FDayPlanQuantity='" + SumPlan + "' where FID=" + current["FDayPlanWorkID"].ToString());
                    for (int i = 0; i <= 60; i++)
                    {
                        this.View.GetFieldEditor("FT" + i.ToString(), m).Enabled = false; //单据体行锁定字段
                        Sum = Sum - Convert.ToDouble(current["FT" + "" + i + ""]);
                        if (Sum >= 0)
                        {

                            grid.SetForecolor("FT" + "" + i + "", "#00FF00", m);
                        }
                        else
                        {
                            grid.SetForecolor("FT" + "" + i + "", "#FF0000", m);
                        }

                    }
                    m++;
                    //grid = this.View.GetControl[EntryGrid]("FSaleOrderEntry")#需要设置的单据体标识Key
                    //grid.SetRowBackcolor("#FFFF00", 0)#设置整行背景颜色
                    //grid.SetForecolor("FMaterialName", "#FF0000", 0)#需要设置字段标识Key[/code]
                    // grid.SetBackcolor("FT" + "" + i + "", "#FF0000", m); 

                    //this.View.Session["ProcessRateValue"] = Convert.ToInt32(this.View.Session["ProcessRateValue"]) + Convert.ToInt32(1 / entityDataObjoct.Count * 90);

                }

            }
            else
            {
                this.View.ShowMessage("请先加载数据！");
            }

        }
        /// <summary>
        /// 计算最大齐套数量
        /// </summary>
        /// <param name="FMATERIALID"> 物料Number</param>
        /// <param name="FMainInterID"> 生产订单主码</param>
        /// <param name="FMainEntryID"> 生产订单子码</param>
        /// <param name="FDayPlanWorkID"> 日计划工单FID</param>
        /// <param name="SumPlan"> 计划总数量</param>
        /// <param name="FStockOrgId"> 库存组织</param>
        /// <returns></returns>
        public string SumGetComplete(string FMATERIALID, string FMainInterID, string FMainEntryID, string FDayPlanWorkID, decimal SumPlan, string FStockOrgId)
        {
            //FDayPlanWorkID日计划工单ID去删除现有的齐套分析数据
            DBServiceHelper.Execute(Context, "/*dialect*/delete JIT_MOMaterReadysBill where FHICMOInterID = '" + FDayPlanWorkID + "'");

            #region 数据库直接算出所以需要数据字段
            /*
            --生产用料清单列表
SELECT T1.FID,T1.FBillNo,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty,
T2.FMATERIALID FMATERIALID2,T4.FNumber,T2.FNeedQty,t3.FBASEQTY,convert(decimal(18,3),FNeedQty/t1.FQty)UnitDosage,
floor(t3.FBASEQTY/convert(decimal(18,3),FNeedQty/t1.FQty)) SumGetComplete
 FROM T_PRD_PPBOM T1
LEFT JOIN (select FID,MAX(FENTRYID)FENTRYID,SUM(FNeedQty)FNeedQty,FMATERIALID  from T_PRD_PPBOMENTRY where FMoId='100007'
group by FMATERIALID,FID ) T2 on T1.FID=t2.FID
LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID
LEFT JOIN T_BD_MATERIAL T4 on T2.FMATERIALID=T4.FMATERIALID
where t1.FMoId='100007' and T1.FMaterialID='105771'
             */
            #endregion

            string sql = string.Format(@"
/*dialect*/
--生产用料清单列表
SELECT T1.FID,T1.FBillNo,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty,
T2.FMATERIALID FMATERIALID2,T4.FNumber,T2.FNeedQty
--,isnull(t3.FBASEQTY,0)FBASEQTY
 FROM T_PRD_PPBOM T1
LEFT JOIN (select FID,MAX(FENTRYID)FENTRYID,SUM(FNeedQty)FNeedQty,FMATERIALID  from T_PRD_PPBOMENTRY where FMoId='{0}'
group by FMATERIALID,FID ) T2 on T1.FID=t2.FID
--LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID
LEFT JOIN T_BD_MATERIAL T4 on T2.FMATERIALID=T4.FMATERIALID
where t1.FMoId='{0}' and T1.FMaterialID='{1}'
", FMainInterID, FMATERIALID);
            DataSet ds = DBServiceHelper.ExecuteDataSet(Context, sql);
            var PpbomList = ds.ToModelList<PRD_PPBOM>();
            //当前最大齐套数 下层需求数量/父级需求数量=单位用料
            //var SumGetComplete = Math.Floor(PpbomList.Min(t => t.FBASEQTY / (t.FNeedQty / t.FQty)));

            //最大齐套数
            decimal SumGetComplete;
            List<decimal> list = new List<decimal>();
            foreach (var t in PpbomList)
            {
                var ls = ListInventoryByStockOrgId.Where(c => c.FMATERIALID == t.FMATERIALID2 && c.FStockOrgId == FStockOrgId).FirstOrDefault();
                if (ls == null)
                {
                    list.Add(0);
                    continue;
                }

                SumGetComplete = Math.Floor(ls.FBASEQTY / (t.FNeedQty / t.FQty));
                list.Add(SumGetComplete);
            }
            //没有物料的库存信息
            SumGetComplete = list.Min();

            if (SumGetComplete > SumPlan)//系统可生产最大齐套数大于计划需求量时，取计划
                SumGetComplete = SumPlan;
            foreach (var item in PpbomList)
            {
                decimal jskc = 0;
                var ls = ListInventoryByStockOrgId.Where(c => c.FMATERIALID == item.FMATERIALID2 && c.FStockOrgId == FStockOrgId).FirstOrDefault();
                if (ls != null)
                    jskc = ls.FBASEQTY;
                /*
                及时库存数量 = 系统现有库存
                可用数量 = 及时库存数量
                数量字段 = 齐套量 * 单位用料
                */
                decimal UnitDosage = item.FNeedQty / item.FQty;//单位用量
                decimal xhsl = SumGetComplete * (UnitDosage);///消耗数量

                JObject _jsonRoot = new JObject();
                _jsonRoot.Add("Creator", "");
                _jsonRoot.Add("NeedUpDateFields", new JArray());
                _jsonRoot.Add("NeedReturnFields", new JArray());
                _jsonRoot.Add("IsDeleteEntry", "true");
                _jsonRoot.Add("SubSystemId", "");
                _jsonRoot.Add("IsVerifyBaseDataField", "");
                JObject _jsonModel = new JObject();
                _jsonModel.Add("FHMainICMOInterIDr", FMainInterID);//主生产订单主内码
                _jsonModel.Add("FHMainICMOEntryID", FMainEntryID);//主生产订单主内码
                _jsonModel.Add("FHICMOInterID", FDayPlanWorkID);//日计划订单主内码

                JObject _HMaterID = new JObject();
                _HMaterID.Add("FNumber", item.FNumber);
                _jsonModel.Add("FHMaterID", _HMaterID);//FHMaterID 物料

                _jsonModel.Add("FHStockQty", Convert.ToDecimal(jskc));//及时库存数量
                _jsonModel.Add("FHUseQty", Convert.ToDecimal(xhsl));//数量 
                _jsonModel.Add("FHLeftQty", Convert.ToDecimal(jskc));//可用数量
                _jsonModel.Add("FUnitDosage", Convert.ToDecimal(UnitDosage));//单位用量 .

                _jsonModel.Add("FSumPlanCount", Convert.ToDecimal(SumPlan));//FSumPlanCount 计划总数量  
                _jsonModel.Add("FCompleteCount", Convert.ToDecimal(SumGetComplete));//FCompleteCount 齐套数量  
                _jsonModel.Add("FPRDBILLNO", item.FMOBillNO);//FPRDBILLNO 工单编号  
                _jsonModel.Add("FIdentificat", "1");//标识 

                _jsonRoot.Add("Model", _jsonModel);

                CloudClient _cloudClient = new CloudClient("http://localhost/K3Cloud/");
                var _result = _cloudClient.Save("paez_CompleteAnalysisTemp", _jsonRoot.ToString());
                JObject _saveObj = JObject.Parse(_result);
                string _saveIsSuc = _saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();

                if (_saveIsSuc != "TRUE")
                {
                    this.View.ShowMessage("操作失败，联系管理员！");
                }

                //扣除总库存的数量

                if (ls != null)
                    ls.FBASEQTY -= xhsl;



                List<PRD_Inventory> _ListInventory = ListInventory.Where(c => c.FMATERIALID == item.FMATERIALID2 && c.FStockOrgId == FStockOrgId && c.FBASEQTY > 0).ToList();
                foreach (var Inventory in _ListInventory)
                {
                    //扣除库存数量
                    // Inventory.FBASEQTY;
                    if (xhsl <= 0)
                        break;

                    // var _ls = ListInventory.Where(c => c.FMATERIALID == item.FMATERIALID2).DefaultIfEmpty();
                    JObject jsonRoot = new JObject();
                    jsonRoot.Add("Creator", "");
                    jsonRoot.Add("NeedUpDateFields", new JArray());
                    jsonRoot.Add("NeedReturnFields", new JArray());
                    jsonRoot.Add("IsDeleteEntry", "true");
                    jsonRoot.Add("SubSystemId", "");
                    jsonRoot.Add("IsVerifyBaseDataField", "");
                    JObject jsonModel = new JObject();
                    jsonModel.Add("FHMainICMOInterIDr", FMainInterID);//主生产订单主内码
                    jsonModel.Add("FHMainICMOEntryID", FMainEntryID);//主生产订单主内码
                    jsonModel.Add("FHICMOInterID", FDayPlanWorkID);//日计划订单主内码

                    JObject HMaterID = new JObject();
                    HMaterID.Add("FNumber", item.FNumber);
                    jsonModel.Add("FHMaterID", HMaterID);//FHMaterID 物料


                    DataTable dt = DBServiceHelper.ExecuteDataSet(Context, @"
select FNUMBER
 from T_ORG_Organizations where FORGID='" + FStockOrgId + "'").Tables[0];
                    JObject FHStockOrgID = new JObject();
                    FHStockOrgID.Add("FNumber", dt.Rows[0]["FNUMBER"].ToString());
                    jsonModel.Add("FHStockOrgID", FHStockOrgID);//FHStockOrgID 库存组织

                    jsonModel.Add("FOwnerTypeId", "BD_OwnerOrg");//FHStockOrgID 货主类型

                    dt = DBServiceHelper.ExecuteDataSet(Context, @"
select FNUMBER
 from T_ORG_Organizations where FORGID='" + Inventory.FOwnerId + "'").Tables[0];

                    JObject FOwnerId = new JObject();
                    FOwnerId.Add("FNumber", dt.Rows[0]["FNUMBER"].ToString());
                    jsonModel.Add("FOwnerId", FOwnerId);//FOwnerId 货主

                    jsonModel.Add("FHStockQty", Convert.ToDecimal(Inventory.FBASEQTY));//及时库存数量
                    jsonModel.Add("FHUseQty", Convert.ToDecimal(xhsl > Inventory.FBASEQTY ? Inventory.FBASEQTY : xhsl));//数量 
                    jsonModel.Add("FHLeftQty", Convert.ToDecimal(Inventory.FBASEQTY));//可用数量
                    jsonModel.Add("FUnitDosage", Convert.ToDecimal(UnitDosage));//单位用量 .

                    jsonModel.Add("FSumPlanCount", Convert.ToDecimal(SumPlan));//FSumPlanCount 计划总数量  
                    jsonModel.Add("FCompleteCount", Convert.ToDecimal(SumGetComplete));//FCompleteCount 齐套数量  
                    jsonModel.Add("FPRDBILLNO", item.FMOBillNO);//FPRDBILLNO 工单编号  
                    jsonModel.Add("FIdentificat", "2");//标识 


                    jsonRoot.Add("Model", jsonModel);

                    CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/");
                    var result = cloudClient.Save("paez_CompleteAnalysisTemp", jsonRoot.ToString());
                    JObject saveObj = JObject.Parse(result);
                    string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();

                    if (saveIsSuc != "TRUE")
                    {
                        this.View.ShowMessage("操作失败，联系管理员！");
                    }


                    if (xhsl >= Inventory.FBASEQTY)
                    {
                        xhsl = xhsl - Inventory.FBASEQTY;
                        Inventory.FBASEQTY = 0;

                    }
                    else
                    {
                        Inventory.FBASEQTY -= xhsl;
                        xhsl = 0;
                    }





                }

            }
            return SumGetComplete.ToString();
        }
        /// <summary>
        /// 齐套分析
        /// </summary>
@@ -753,6 +452,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
--where T1.FMATERIALID in (105773)
");
                ListInventory = ds.ToModelList<PRD_Inventory>();
@@ -770,6 +471,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
--where T1.FMATERIALID in (105773)
group by T1.FStockOrgId,T1.FMATERIALID
");
@@ -901,6 +604,7 @@
                }
                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);
@@ -959,6 +663,472 @@
            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  from SC_WORKBILLSORTBILLMAIN a
join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
LEFT JOIN(select FID, MAX(FENTRYID) FENTRYID, SUM(FNeedQty) FNeedQty, FMATERIALID from T_PRD_PPBOMENTRY
group by FMATERIALID, FID ) T2 on T1.FID = t2.FID
 --LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID
JOIN T_BD_MATERIAL T4 on T2.FMATERIALID = T4.FMATERIALID
JOIN T_ORG_Organizations t5 on a.FHPRDORGID = t5.FORGID
--条件为今天往后的日计划 + 今天之前未关闭的日计划
where (FHEntryCloseDate is null and DATEDIFF(D,GETDATE(),FHMASTERDATE)<0)or DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0
ORDER BY b.FHMASTERDATE,a.FHOrderLev 
";
                ds = DBServiceHelper.ExecuteDataSet(Context, sql);
                List<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,//占用数量
                                    /* 2021 1.26 更改为need
                                    FComPlete = sjkcList.FBASEQTY >= _item.PlanCount ? "齐套" : "未齐套",
                                    */
                                    FComPlete = sjkcList.FBASEQTY >= need ? "齐套" : "未齐套",
                                    FPlanDate = item.FHMASTERDATE // 日计划日期
                                });
                                //扣减总库存数量
                                sjkcList.FBASEQTY = sjkcList.FBASEQTY >= fCompleteCount ? sjkcList.FBASEQTY - fCompleteCount : 0;
                                //剩余日计划需求数量
                                need = need >= Inventory.FBASEQTY ? need - Inventory.FBASEQTY : 0;
                                //扣减消耗库存
                                Inventory.FBASEQTY = Inventory.FBASEQTY - fCompleteCount;
                            }
                        }
                        else
                        {
                            decimal occupyMaterial = 0;
                            if (occupyDic.ContainsKey(_item.FMATERIALID2))
                                occupyMaterial = occupyDic[_item.FMATERIALID2];
                            completeAnalysisTempModel.Add(new Demo.Model.Model.CompleteAnalysisTempModel
                            {
                                FHMainICMOInterIDr = _item.FPRDMOMAINID,
                                FHMainICMOEntryID = _item.FPRDMOENTYID,
                                FHICMOInterID = _item.FID.ToString(),
                                FHICMOEntryID = _item.FEntryID.ToString(),
                                FHMaterID = _item.FNumber,
                                FHStockOrgID = _item.OrgFnumber,
                                FHPRDORGID = _item.OrgFnumber,
                                FPRDBillNo = _item.FMOBillNO,
                                FOwnerTypeId = "BD_OwnerOrg",
                                FUnitDosage = _item.dwyl.ToString(),//单位用量
                                FSumPlanCount = _item.PlanCount.ToString(),//计划总数量
                                FCompleteCount = 0, //齐套数量
                                FCompleteCount1 = 0, //齐套数量
                                FLackCount = _item.PlanCount,//缺料数量
                                FHStockQty = 0,//即时库存
                                FHLeftQty = 0,//可用数量
                                FOccupyCount = occupyMaterial,//占用数量
                                FComPlete = "未齐套",
                                FPlanDate = item.FHMASTERDATE // 日计划日期
                            });
                        }
                    }
                    //Thread.Sleep(100);
                    // this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(40) / DatePlanList.Count) * i);
                    i++;

                }
                LogHelper.Info($"齐套分析，数据准备完成，保存到Model实体，总行数：{completeAnalysisTempModel.Count}，耗时：" + sw.Elapsed);
                i = 1;


                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);
                    if (item.FOwnerId != null)
                        FBatchModel.Add("FOwnerId", new JObject() { ["FNUMBER"] = item.FOwnerId });
                    Fentity.Add(FBatchModel);
                    if (i >= 20 && (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();//开始计时
                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>();
                //需要齐套分析的数据
                sql = @"
/*dialect*/
select a.FPRDMOMAINID,a.FPRDMOENTYID,a.FID,b.FEntryID,a.FHPRDORGID FStockOrgId,t5.FNUMBER as OrgFnumber,a.FHOrderLev,b.FHQTY,b.FHMASTERDATE,T1.FBillNo,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty,
T2.FMATERIALID FMATERIALID2, T4.FNumber,T2.FNeedQty,FNeedQty/FQty dwyl,(FHQTY-ISNULL(FProductNum,0))*(FNeedQty/FQty) PlanCount  from SC_WORKBILLSORTBILLMAIN a
join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
LEFT JOIN(select FID, MAX(FENTRYID) FENTRYID, SUM(FNeedQty) FNeedQty, FMATERIALID from T_PRD_PPBOMENTRY
group by FMATERIALID, FID ) T2 on T1.FID = t2.FID
 --LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID
JOIN T_BD_MATERIAL T4 on T2.FMATERIALID = T4.FMATERIALID
JOIN T_ORG_Organizations t5 on a.FHPRDORGID = t5.FORGID
--条件为今天往后的日计划 + 今天之前未关闭的日计划
where (FHEntryCloseDate is null and DATEDIFF(D,GETDATE(),FHMASTERDATE)<0)or DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0
ORDER BY b.FHMASTERDATE,a.FHOrderLev 
";
                ds = DBServiceHelper.ExecuteDataSet(Context, sql);
                List<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.FMATERIALID2,
                                    FHStockOrgID = _item.FStockOrgId,
                                    FHPRDORGID = _item.FStockOrgId,
                                    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,
                                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>();
                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)
select id, '', 'A', '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', 'BD_OwnerOrg', '','{13}','{14}','{15}','{16}','{17}' 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);
                    sqlList.Add(sql);

                    //if (sql.Contains("559"))
                    //    LogHelper.Info("记录sql559：" + sql);

                }
                LogHelper.Info("批量执行sql开始：");
                DBServiceHelper.ExecuteBatch(Context, sqlList);
                LogHelper.Info("批量执行sql结束：");
                //执行完成后 更新日计划工单状态
                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>
@@ -1205,8 +1375,9 @@
        /// </summary>
        public void DayPlanPPBomBill()
        {
            LogHelper.Info("生产用料清单准备数据");

            Stopwatch sw = new Stopwatch();
            sw.Start();//开始计时
            LogHelper.Info("批量，生成日计划用料清单准备数据");
            //锁定的日计划不生成
            try
            {
@@ -1224,8 +1395,10 @@
 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>();
@@ -1257,8 +1430,8 @@
                            FHQtyScrap = dr["FSCRAPRATE"].ToString(),
                        });
                    }
                    LogHelper.Info($"批量，日计划用料清单，数据准备完成，保存到Model实体，总行数：{DayPlanMaterial.Count}，耗时：" + sw.Elapsed);
                    var fidList = DayPlanMaterial.Select(x => x.FEntryID).Distinct().ToList();
                    LogHelper.Error("生产用料清单准备数据" + fidList.Count);
                    foreach (var item in fidList)
                    {
                        var _DayPlanMaterial = DayPlanMaterial.Where(x => x.FEntryID == item).ToList();
@@ -1313,12 +1486,143 @@
                        }
                    }
                }
                sw.Stop();//结束计时
                LogHelper.Info("批量，生成日计划用料清单运行总时长：" + sw.Elapsed);
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.Message.ToString());
                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.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();
                    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();
                        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);
                        Fentity.Add(model);
                        if (i >= 20  && (i % 20 == 0 || i == fidList.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_Sc_DayPlanPPBomBill", 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();
                        }
                        i++;
                    }
                }
                sw.Stop();//结束计时
                LogHelper.Info("生成日计划用料清单运行总时长：" + sw.Elapsed);
            }
            catch (Exception ex)
            {
                LogHelper.Error("日计划用料清单+" + ex.Message.ToString());
            }
        }

        /// <summary>
        /// 提料计划
        /// </summary>
@@ -1633,7 +1937,9 @@
                }
                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);
                LogHelper.Info($"提料计划，数据准备完成，保存到Model实体，总行数：{PODemandPlanList.Count}，耗时：" + sw.Elapsed);
                int tlmmm = 1;
                JArray FinalyResult = new JArray();
                foreach (var item in PODemandPlanList)
                {
                    DateTime date = item.FHMASTERDATE;
@@ -1664,25 +1970,31 @@
                        Fentity.Add(FentityModel);
                    }
                    model.Add("FEntity", Fentity);
                    JObject jsonRoot = new JObject()
                    FinalyResult.Add(model);
                    if (tlmmm >= 20 && (tlmmm % 20 == 0 || tlmmm == PODemandPlanList.Count))
                    {
                        ["Creator"] = "",
                        ["NeedUpDateFields"] = new JArray(),
                        ["NeedReturnFields"] = new JArray(),
                        ["IsDeleteEntry"] = "false",
                        ["SubSystemId"] = "",
                        ["IsVerifyBaseDataField"] = "false",
                        ["Model"] = model
                    };
                    CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/");
                    var result = cloudClient.Save("paez_PODemandPlan", jsonRoot.ToString());
                    JObject saveObj = JObject.Parse(result);
                    string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
                    if (saveIsSuc != "TRUE")
                    {
                        LogHelper.Info(jsonRoot.ToString());
                        LogHelper.Error(saveIsSuc);
                        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);