提料计划报表代码提交

王 垚

2020-12-18 99b85a43de68895a9f73a28768e273b78a52975e

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

@@ -25,6 +25,7 @@
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Runtime.CompilerServices;
@@ -47,7 +48,6 @@
        string PathName = "日计划导入模板.xlsx";
        List<PRD_Inventory> ListInventory;//库存明细
        List<PRD_Inventory> ListInventoryByStockOrgId;//物料总库存

        public override void BarItemClick(BarItemClickEventArgs e)
        {
            base.BarItemClick(e);
@@ -83,7 +83,11 @@
                {
                    Lock();
                }

                //解锁
                if (e.BarItemKey.ToUpper() == "TBRELIEVELOCK")
                {
                    RelieveLock();
                }
                if (e.BarItemKey.ToUpper() == "BTNSAVE")
                {
                    Save();
@@ -91,7 +95,8 @@

                if (e.BarItemKey.ToUpper() == "TBCOMPLETE")
                {
                    COMPLETEMAIN();
                    CompleteSetAnalysis();
                    //COMPLETEMAIN();
                    //  COMPLETE();
                }
                //提料计划
@@ -112,13 +117,13 @@

            if (e.BarItemKey.ToUpper() == "TBEDIT")
            {
                SearchList222();
                SearchListEdit();
            }
        }
        /// <summary>
        /// 行双击 跳出齐套分析明细列表
        /// </summary>
        /// <param name="e"></param>
        /// <param name="e"></param>    
        public override void EntityRowDoubleClick(Kingdee.BOS.Core.DynamicForm.PlugIn.Args.EntityRowClickEventArgs e)
        {
            var m = e.Row;
@@ -140,7 +145,6 @@
            formPa.CustomParams.Add("FMatrailId_Id", Convert.ToString((this.View.Model.GetValue("FMatrailId", e.Row) as DynamicObject)["Id"].ToString()));
            this.View.ShowForm(formPa);
        }

        /// <summary>
        /// 导出数据到Excel
        /// </summary>
@@ -224,8 +228,6 @@
            }

        }


        private string _fileId = string.Empty;
        /// <summary>
        /// 弹出下载提示窗，下载文件至客户端本地。
@@ -246,7 +248,6 @@
            jObject.Put("urltitle", "这是文件名");
            this.View.AddAction(JSAction.openUrlWindow, new JSONArray { jObject });
        }

        /// <summary>
        /// 锁定
        /// </summary>
@@ -271,8 +272,49 @@
            string sql;
            foreach (var item in entityDataObjoct)
            {
                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())   and t1.FID = { Convert.ToString(item["FDayPlanWorkID"])} ";

                sql = $"/*dialect*/update Sc_WorkBillSortBillSub set FHLockedSub=1    where FEntryID in  (select TOP({day}) FEntryID from Sc_WorkBillSortBillSub where FHLOCKEDSUB = 0 AND FID = { Convert.ToString(item["FDayPlanWorkID"])} AND DATEDIFF(DAY, GETDATE(), FHMASTERDATE) >= 0 ORDER BY FEntryID)  ";
                //sql = $"/*dialect*/update Sc_WorkBillSortBillSub set FHLockedSub=1    where FEntryID in  (select TOP({day}) FEntryID from Sc_WorkBillSortBillSub where FHLOCKEDSUB = 0 AND FID = { Convert.ToString(item["FDayPlanWorkID"])} AND DATEDIFF(DAY, GETDATE(), FHMASTERDATE) >= 0 ORDER BY FEntryID)  ";
                sqlList.Add(sql);

            }

            int result = DBServiceHelper.ExecuteBatch(Context, sqlList);
            if (result > 0)
            {
                SearchList222();
                this.View.ShowMessage("操作成功");
            }
            else
                this.View.ShowMessage("操作失败，联系管理员！");
        }
        /// <summary>
        /// 解锁
        /// </summary>
        public void RelieveLock()
        {
            int day = Convert.ToInt32(this.Model.GetValue("FLockDays"));

            string sqlwhere = " and 1=1";
            string FSalOrder = Convert.ToString((this.Model.GetValue("FSalOrder")));
            if (string.IsNullOrEmpty(FSalOrder))
            {

            }
            else
                sqlwhere += $" and FHSeOrderBillNo='{FSalOrder}'";

            //获取单据体信息
            Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity");
            //单据体信息转换为列表集合
            DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity);
            List<string> sqlList = new List<string>();
            string sql;
            foreach (var item in entityDataObjoct)
            {
                sql = $"/*dialect*/update t1 set t1.FHLockedSub=0 from Sc_WorkBillSortBillSub t1  join Sc_WorkBillSortBillMain t2 on  t1.FID = t2.FID  where  FHMASTERDATE between  DATEADD(day,-1,getdate()) and DATEADD(day,{day - 1},getdate())   and t1.FID = { Convert.ToString(item["FDayPlanWorkID"])} ";

                //sql = $"/*dialect*/update Sc_WorkBillSortBillSub set FHLockedSub=1    where FEntryID in  (select TOP({day}) FEntryID from Sc_WorkBillSortBillSub where FHLOCKEDSUB = 0 AND FID = { Convert.ToString(item["FDayPlanWorkID"])} AND DATEDIFF(DAY, GETDATE(), FHMASTERDATE) >= 0 ORDER BY FEntryID)  ";
                sqlList.Add(sql);

            }
@@ -288,7 +330,7 @@
        }
        /// <summary>
        /// 保存功能
        /// </summary>
        /// </summary>  
        public void Save()
        {

@@ -299,8 +341,25 @@
            //单据体信息转换为列表集合
            DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity);
            DateTime date = DateTime.Now;

            string _result = "";

            foreach (DynamicObject current in entityDataObjoct)
            {
                if (Convert.ToInt32(current["FNoScheduled"]) < 0)
                    _result += "序号：" + Convert.ToString(current["SEQ"]) + ",日计划总数量超出生产订单数量";

            }
            if (_result != "")
            {
                this.View.ShowErrMessage(_result);
                return;
            }

            foreach (DynamicObject current in entityDataObjoct)
            {
                sql = $"/*dialect*/ update Sc_WorkBillSortBillMain set FOrderQuantity={current["FOrderQuantity"]},FDayPlanQuantity={current["FDayPlanQuantity"]},FNoScheduled={current["FNoScheduled"]} where FID = {current["FDayPlanWorkID"]}";
                sqlList.Add(sql);
                DateTime FBzDate = Convert.ToDateTime(Convert.ToDateTime(current["FBzDate"]));
                DateTime end = Convert.ToDateTime(Convert.ToDateTime(current["FBzDate"]).AddDays(60).ToShortDateString());//最大日期
                for (int i = 0; i <= 60; i++)
@@ -327,7 +386,6 @@
                this.View.ShowMessage("操作失败，联系管理员！");

        }

        /// <summary>
        /// 齐套分析
        /// </summary>
@@ -385,10 +443,26 @@
        public void COMPLETEMAIN()
        {
            //获取当前即时库存 按照物料+货主+库存 展现
            DataSet ds = DBServiceHelper.ExecuteDataSet(Context, @" /*dialect*/ select DISTINCT FBASEQTY,FMATERIALID,FStockOrgId,FOwnerId from T_STK_Inventory order by FBASEQTY");
            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 T_STK_Inventory group by FMATERIALID, FStockOrgId ");
            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>();

            //获取单据体信息
@@ -400,6 +474,14 @@
            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)
                {
                    //计划总数量
@@ -414,7 +496,7 @@
                    this.View.Model.SetValue("FCompleteSetCount", Sum, m);
                    this.View.UpdateView("FCompleteSetCount");
                    //保存齐套数 
                    DBServiceHelper.Execute(Context, "/*dialect*/ update Sc_WorkBillSortBillMain set FCompleteCount='" + Sum + "',FPlanCount='" + SumPlan + "' where FID=" + current["FDayPlanWorkID"].ToString());
                    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; //单据体行锁定字段
@@ -427,7 +509,6 @@
                        else
                        {
                            grid.SetForecolor("FT" + "" + i + "", "#FF0000", m);

                        }

                    }
@@ -448,7 +529,6 @@
            }

        }

        /// <summary>
        /// 计算最大齐套数量
        /// </summary>
@@ -456,7 +536,7 @@
        /// <param name="FMainInterID"> 生产订单主码</param>
        /// <param name="FMainEntryID"> 生产订单子码</param>
        /// <param name="FDayPlanWorkID"> 日计划工单FID</param>
        /// <param name="FDayPlanWorkID"> 计划总数量</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)
@@ -664,9 +744,207 @@
            }
            return SumGetComplete.ToString();
        }



        /// <summary>
        /// 齐套分析
        /// </summary>
        public void CompleteSetAnalysis()
        {
            try
            {
                Stopwatch sw = new Stopwatch();
                sw.Start();//开始计时
                //清空齐套临时表
                DBServiceHelper.Execute(Context, "/*dialect*/  delete JIT_MOMaterReadysBill ");
                //获取当前即时库存 按照物料+库存组织+货主+库存数量  
                DataSet ds = DBServiceHelper.ExecuteDataSet(Context, @" 
/*dialect*/ 
  select  isnull(a.FBASEQTY,0)-ISNULL(b.FHUseQty,0) FBASEQTY,T1.FStockOrgId,T1.FMATERIALID,a.FOWNERID,c.FNUMBER from 
(
select a.FHPRDORGID FStockOrgId,T2.FMATERIALID from SC_WORKBILLSORTBILLMAIN a
join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
LEFT JOIN(select FID,FMATERIALID from T_PRD_PPBOMENTRY
group by FMATERIALID,FID) T2 on T1.FID = t2.FID
group by a.FHPRDORGID,T2.FMATERIALID
) T1 
LEFT JOIN T_STK_Inventory a on T1.FStockOrgId = a.FStockOrgId and T1.FMATERIALID =a.FMATERIALID 
LEFT JOIN JIT_MOMaterReadysBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID and a.FOwnerId = b.FOwnerId 
LEFT join T_ORG_Organizations c on a.FOWNERID = c.FORGID
where T1.FMATERIALID in (105773)
");
                ListInventory = ds.ToModelList<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)
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*(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 FPRDMOMAINID = 100010
where  t4.FMATERIALID in (105773
)
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>();
                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;
                        //实际总库存 
                        decimal sjkc = 0;
                        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;//齐套数量
                                //总计划数量 和 总库存数量比对 
                                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, //齐套数量
                                    FLackCount = _fLackCount,//缺料数量
                                    FHStockQty = sjkcList.FBASEQTY,//即时库存
                                    FHLeftQty = Inventory.FBASEQTY,//可用数量
                                    FComPlete = sjkcList.FBASEQTY >= _item.PlanCount ? "齐套" : "未齐套",
                                    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
                        {
                            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, //齐套数量
                                FLackCount = _item.PlanCount,//缺料数量
                                FHStockQty = 0,//即时库存
                                FHLeftQty = 0,//可用数量
                                FComPlete = "未齐套",
                                FPlanDate = item.FHMASTERDATE // 日计划日期
                            });
                        }
                    }
                }
                foreach (var item in completeAnalysisTempModel)
                {
                    JObject jsonRoot = new JObject();
                    jsonRoot.Add("Creator", "");
                    jsonRoot.Add("NeedUpDateFields", new JArray());
                    jsonRoot.Add("NeedReturnFields", new JArray());
                    jsonRoot.Add("IsDeleteEntry", "true");
                    jsonRoot.Add("SubSystemId", "");
                    jsonRoot.Add("IsVerifyBaseDataField", "");
                    JObject jsonModel = new JObject();
                    jsonModel.Add("FHMainICMOInterIDr", item.FHMainICMOInterIDr);
                    jsonModel.Add("FHMainICMOEntryID", item.FHMainICMOEntryID);
                    jsonModel.Add("FHICMOInterID", item.FHICMOInterID);
                    jsonModel.Add("FHICMOEntryID", item.FHICMOEntryID);
                    jsonModel.Add("FHMaterID", new JObject() { ["FNUMBER"] = item.FHMaterID });
                    jsonModel.Add("FHUseQty", item.FHUseQty);
                    jsonModel.Add("FHStockOrgID", new JObject() { ["FNUMBER"] = item.FHStockOrgID });
                    jsonModel.Add("FHStockQty", item.FHStockQty);
                    jsonModel.Add("FHLeftQty", item.FHLeftQty);
                    jsonModel.Add("FHPRDORGID", new JObject() { ["FNUMBER"] = item.FHStockOrgID });
                    jsonModel.Add("FUnitDosage", item.FUnitDosage);
                    jsonModel.Add("FSumPlanCount", item.FSumPlanCount);
                    jsonModel.Add("FCompleteCount", item.FCompleteCount);
                    jsonModel.Add("FPRDBillNo", item.FPRDBillNo);
                    jsonModel.Add("FOwnerTypeId", item.FOwnerTypeId);
                    jsonModel.Add("FComPlete", item.FComPlete);
                    jsonModel.Add("FLackCount", item.FLackCount);
                    jsonModel.Add("FPlanDate", item.FPlanDate);
                    if (item.FOwnerId != null)
                        jsonModel.Add("FOwnerId", new JObject() { ["FNUMBER"] = item.FOwnerId });
                    jsonRoot.Add("Model", jsonModel);
                    CloudClient cloudClient = new CloudClient("http://localhost/K3Cloud/");
                    var result = cloudClient.Save("paez_CompleteAnalysisTemp", jsonRoot.ToString());
                    JObject saveObj = JObject.Parse(result);
                    string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();
                    if (saveIsSuc != "TRUE")
                    {
                        LogHelper.Error(jsonRoot.ToString());
                    }
                }
                //执行完成后 更新日计划工单状态
                DBServiceHelper.Execute(Context, @"
/*dialect*/update Sc_WorkBillSortBillSub set FComplete = '未齐套' where FEntryID IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBill where isnull(FCOMPLETE,'未齐套') ='未齐套')
");
                DBServiceHelper.Execute(Context, @"
/*dialect*/update Sc_WorkBillSortBillSub set FComplete = '齐套' where FEntryID NOT IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBill where isnull(FCOMPLETE,'未齐套') ='未齐套')
");
                sw.Stop();//结束计时
                LogHelper.Info("运行总时长：" + sw.Elapsed);
            }
            catch (Exception ex)
            {
                LogHelper.Error(ex.Message.ToString());
            }
        }
        /// <summary>
        /// 查询
        /// </summary>
@@ -720,6 +998,7 @@
                DataTable dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                DateTime DateNow = DateTime.Now;//获取当前日期
                this.Model.DeleteEntryData("FEntity");
                EntryGrid grid = this.View.GetControl<EntryGrid>("FEntity");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    this.Model.CreateNewEntryRow("FEntity");
@@ -745,10 +1024,14 @@
                    this.Model.SetValue("FProductWorkShopId", dt.Rows[i]["FHWorkShopID"].ToString(), i);//生产车间

                    this.Model.SetValue("FCompleteSetCount", dt.Rows[i]["FCompleteCount"].ToString(), i);//齐套数量
                    this.Model.SetValue("FOrderQuantity", dt.Rows[i]["FOrderQuantity"].ToString(), i);//生产订单数量
                    this.Model.SetValue("FDayPlanQuantity", dt.Rows[i]["FDayPlanQuantity"].ToString(), i);//日计划数量
                    this.Model.SetValue("FNoScheduled", dt.Rows[i]["FNoScheduled"].ToString(), i);//未排数量


                    sql = @"/*dialect*/SELECT * FROM Sc_WorkBillSortBillSub WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0 and FID=" + dt.Rows[i]["FID"].ToString();
                    DataTable _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];

                    foreach (DataRow dr in _dt.Rows)
                    {
                        /*
@@ -764,11 +1047,8 @@
                        this.Model.SetValue("FT" + getDay.Days.ToString(), dr["FHQTY"].ToString(), i);//日计划工单FID
                        if (dr["FHLockedSub"].ToString() == "1")
                            this.View.GetFieldEditor("FT" + getDay.Days.ToString(), i).Enabled = false; //单据体行锁定字段

                    }

                    this.View.InvokeFieldUpdateService("FMatrailId", i);//调用数量值更新

                }
                this.View.UpdateView();
                for (int j = 0; j < dt.Rows.Count; j++)
@@ -785,6 +1065,16 @@

                        if (dr["FHLockedSub"].ToString() == "1")
                            this.View.GetFieldEditor("FT" + getDay.Days.ToString(), j).Enabled = false; //单据体行锁定字段
                        if (dr["FCOMPLETE"].ToString() == "未齐套")
                        {
                            this.View.GetFieldEditor("FT" + getDay.Days.ToString(), j).Enabled = false; //单据体行锁定字段
                            grid.SetForecolor("FT" + "" + getDay.Days.ToString() + "", "#FF0000", j);
                        }
                        else if (dr["FCOMPLETE"].ToString() == "齐套")
                        {
                            this.View.GetFieldEditor("FT" + getDay.Days.ToString(), j).Enabled = false; //单据体行锁定字段
                            grid.SetForecolor("FT" + "" + getDay.Days.ToString() + "", "#00FF00", j);
                        }
                    }
                }

@@ -799,152 +1089,129 @@
            }


        }

        }
        /// <summary>
        /// 编辑
        /// </summary>
        public void SearchListEdit()
        {
            string sqlwhere = " where 1=1";

            if ((this.Model.GetValue("FProWorkShopId") as DynamicObject) != null)
            {
                string FProWorkShopId = (this.Model.GetValue("FProWorkShopId") as DynamicObject)["Id"].ToString();
                if (!string.IsNullOrEmpty(FProWorkShopId))
                    sqlwhere += $" and FHWorkShopID='{FProWorkShopId}'";
            }
            //销售订单号
            string FSalOrder = Convert.ToString((this.Model.GetValue("FSalOrder")));
            if (string.IsNullOrEmpty(FSalOrder))
            {

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

            }
            else
                sqlwhere += $" and FSCORDERNO='{FScOrderMainNo}'";

            //物料
            if ((this.Model.GetValue("FMatralId") as DynamicObject) != null)
            {
                string FMatralId = (this.Model.GetValue("FMatralId") as DynamicObject)["Number"].ToString();
                if (!string.IsNullOrEmpty(FMatralId))
                    sqlwhere += $" and t3.FNumber='{FMatralId}'";
            }
            //编制日期
            string FPreparatDate = Convert.ToString((this.Model.GetValue("FPreparatDate")));
            if (!string.IsNullOrEmpty(FPreparatDate))
                sqlwhere += $" and FPreparatDate='{FPreparatDate}'";
            try
            {
                string sql = @"/*dialect*/select t1.* from  Sc_WorkBillSortBillMain t1
   left join T_BD_MATERIAL t3 on t1.FHMaterID=t3.FMATERIALID
" + sqlwhere;
                DataTable dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                DateTime DateNow = DateTime.Now;//获取当前日期
                this.Model.DeleteEntryData("FEntity");
                EntryGrid grid = this.View.GetControl<EntryGrid>("FEntity");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    this.Model.CreateNewEntryRow("FEntity");
                    this.Model.SetValue("FSEQ", i + 1, i);//序号
                    this.Model.SetValue("FCompleteSetCount", "", i);//总齐套数量
                    this.Model.SetValue("FProOrderCount", dt.Rows[i]["FHOrderNeedQty"].ToString(), i);//生产订单数量 
                    this.Model.SetValue("FSalOrderCount", dt.Rows[i]["FHSeOrderBillNo"].ToString(), i);//销售订单数量
                    this.Model.SetValue("FBatch", dt.Rows[i]["FBatch"].ToString(), i);//批次号
                    this.Model.SetValue("FUnit", dt.Rows[i]["FHUnitID"].ToString(), i);//计量单位
                    this.Model.SetValue("FORGID", dt.Rows[i]["FHPRDORGID"].ToString(), i);//生产组织
                    this.Model.SetValue("FMatrailId", dt.Rows[i]["FHMaterID"].ToString(), i);//产品代码
                    this.Model.SetValue("FDeliverydate", dt.Rows[i]["FHOrderCommitDate"].ToString(), i);//交货日期
                    this.Model.SetValue("FOrderLevel", dt.Rows[i]["FHOrderLev"].ToString(), i);//订单等级
                    this.Model.SetValue("FSCOrderNo", dt.Rows[i]["FSCOrderNo"].ToString(), i);//生产订单号
                    this.Model.SetValue("FSalOrderNo", dt.Rows[i]["FHSEORDERBILLNO"].ToString(), i);//销售订单号
                    this.Model.SetValue("FBzDate", dt.Rows[i]["FPreparatDate"].ToString(), i);//编制日期
                    this.Model.SetValue("FProOrderCount", dt.Rows[i]["FHOrderNeedQty"].ToString(), i);//生产订单数量
                    this.Model.SetValue("FHMainSourceInterID", dt.Rows[i]["FPRDMOMAINID"].ToString(), i);//源单主内码FHMainSourceInterID
                    this.Model.SetValue("FHMainSourceEntryID", dt.Rows[i]["FPRDMOENTYID"].ToString(), i);//源单子内码FHMainSourceEntryID
                    this.Model.SetValue("FDayPlanWorkID", dt.Rows[i]["FID"].ToString(), i);//日计划工单FID
                    this.Model.SetValue("FResourcesId", dt.Rows[i]["FHSourceID"].ToString(), i);//生产资源
                    this.Model.SetValue("FProductWorkShopId", dt.Rows[i]["FHWorkShopID"].ToString(), i);//生产车间
                    this.Model.SetValue("FCompleteSetCount", dt.Rows[i]["FCompleteCount"].ToString(), i);//齐套数量
                    this.Model.SetValue("FOrderQuantity", dt.Rows[i]["FOrderQuantity"].ToString(), i);//生产订单数量
                    this.Model.SetValue("FDayPlanQuantity", dt.Rows[i]["FDayPlanQuantity"].ToString(), i);//日计划数量
                    this.Model.SetValue("FNoScheduled", dt.Rows[i]["FNoScheduled"].ToString(), i);//未排数量
                    sql = @"/*dialect*/SELECT * FROM Sc_WorkBillSortBillSub WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0 and FID=" + dt.Rows[i]["FID"].ToString();
                    DataTable _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                    foreach (DataRow dr in _dt.Rows)
                    {
                        DateTime start = Convert.ToDateTime(DateNow.ToShortDateString());
                        DateTime end = Convert.ToDateTime(Convert.ToDateTime(dr["FHMASTERDATE"]).ToShortDateString());
                        TimeSpan getDay = end.Subtract(start);
                        if (getDay.Days > 60)
                            continue;
                        this.Model.SetValue("FT" + getDay.Days.ToString(), dr["FHQTY"].ToString(), i);//日计划工单FID
                        if (dr["FHLockedSub"].ToString() == "1")
                            this.View.GetFieldEditor("FT" + getDay.Days.ToString(), i).Enabled = false; //单据体行锁定字段
                    }
                    this.View.InvokeFieldUpdateService("FMatrailId", i);//调用数量值更新
                }
                this.View.UpdateView();
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    sql = @"/*dialect*/SELECT * FROM Sc_WorkBillSortBillSub WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0 and FID=" + dt.Rows[j]["FID"].ToString();
                    DataTable _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                    foreach (DataRow dr in _dt.Rows)
                    {
                        DateTime start = Convert.ToDateTime(DateNow.ToShortDateString());
                        DateTime end = Convert.ToDateTime(Convert.ToDateTime(dr["FHMASTERDATE"]).ToShortDateString());
                        TimeSpan getDay = end.Subtract(start);
                        if (getDay.Days > 60)
                            continue;
                        if (dr["FHLockedSub"].ToString() == "1")
                            this.View.GetFieldEditor("FT" + getDay.Days.ToString(), j).Enabled = false; //单据体行锁定字段
                    }
                }
                for (int m = 0; m <= 60; m++)
                {
                    this.View.GetControl("FT" + "" + m + "").Text = DateNow.AddDays(m).ToShortDateString();
                }
            }
            catch (Exception ex)
            {
                this.View.ShowErrMessage(ex.Message.ToString());
            }

        }
        /// <summary>
        /// 提料计划
        /// </summary>

        public void Extraction2()
        {

            //获取单据体信息
            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)
            {
                this.View.ShowMessage("请先加载数据！");
            }
            else
            {
                foreach (DynamicObject current in entityDataObjoct)
                {

                    string FDayPlanWorkID = Convert.ToString(current["FDayPlanWorkID"]);//日工单ID
                    string FPRDMOMAINID = Convert.ToString(current["FHMainSourceInterID"]);//生产订单id
                    //是否有过齐套分析 找出齐套数量 日计划数量
                    string sql = string.Format(@"
/*dialect*/
 SELECT T1.FID,FHMATERID,FHMASTERDATE,FHQTY,FCOMPLETECOUNT,FPRDMOMAINID,T2.FENTRYID,T1.FBILLNO FROM  SC_WORKBILLSORTBILLMAIN T1 
JOIN SC_WORKBILLSORTBILLSUB T2 ON T1.FID=T2.FID
WHERE  DATEDIFF(D,GETDATE(),FHMASTERDATE) >=0
AND FPLANCOUNT>FCOMPLETECOUNT
AND T1.FID='{0}' AND FPRDMOMAINID='{1}'
", FDayPlanWorkID, FPRDMOMAINID);
                    DataSet ds = DBServiceHelper.ExecuteDataSet(Context, sql);
                    DataTable dt = ds.Tables[0];
                    if (dt.Rows.Count > 0)
                    {
                        string FMATERIALID = dt.Rows[0]["FHMaterID"].ToString();//物料编码
                        decimal FCompleteCount = Convert.ToDecimal(dt.Rows[0]["FCompleteCount"].ToString());//齐套数量

                        //按主物料查询子物料单位用料 最小采购 和 提前期
                        sql = string.Format(@"
/*dialect*/
SELECT  FNeedQty,t5.FFIXLEADTIME,t5.FMAXPOQTY,t5.FMINPOQTY,t2.FMATERIALID,t3.FNumber FROM T_PRD_PPBOM T1
LEFT JOIN(SELECT FID, MAX(FENTRYID) FENTRYID, SUM(convert(decimal(18, 2), FBASENUMERATOR / FBASEDENOMINATOR))FNeedQty, FMATERIALID from T_PRD_PPBOMENTRY where FMoId = '{0}'
group by FMATERIALID, FID) T2 on T1.FID = t2.FID
JOIN T_BD_MATERIAL T3 ON T2.FMATERIALID = t3.FMATERIALID
join t_BD_MaterialBase t4 on t3.FMATERIALID = t4.FMATERIALID
join t_BD_MaterialPlan t5 on t3.FMATERIALID = t5.FMATERIALID
WHERE T1.FMaterialID = '{1}'
AND FMoId = '{0}'
AND FERPCLSID=1  
", FPRDMOMAINID, FMATERIALID);
                        DataTable _dt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0];
                        //根据bom去循环
                        foreach (DataRow drDetail in _dt.Rows)
                        {
                            string DetailFmaterialID = drDetail["FNumber"].ToString();//子物料
                            int FfixleadTime = Convert.ToInt32(drDetail["FFIXLEADTIME"].ToString());//提前天数
                            decimal FminPoqty = Convert.ToDecimal(drDetail["FMINPOQTY"].ToString());//最小采购量
                            decimal FNeedQty = Convert.ToDecimal(drDetail["FNeedQty"].ToString());//单位用料

                            //日计划数量(套数)
                            decimal FHQTY = 0;
                            foreach (DataRow dr in dt.Rows)
                            {
                                //日计划数量
                                decimal DayFHQTY = Convert.ToDecimal(dr["FHQTY"].ToString());
                                if (FCompleteCount >= DayFHQTY)
                                {
                                    FCompleteCount -= DayFHQTY;
                                    continue;
                                }
                                else if (FCompleteCount != 0)
                                {
                                    FHQTY = DayFHQTY - FCompleteCount;
                                    FCompleteCount = 0;
                                }
                                //提料计划日期
                                DateTime FHDate;
                                //套数*单位用料是否满足最小需求量
                                if (FHQTY * FNeedQty >= FminPoqty)
                                {
                                    FHDate = Convert.ToDateTime(dr["FHMASTERDATE"]);

                                    JObject model = new JObject();
                                    model.Add("FHDate", FHDate.AddDays(-FfixleadTime));
                                    model.Add("FHRemark", "生产订单号：" + "测试呢呢");

                                    JArray Fentity = new JArray();
                                    JObject FentityModel = new JObject();
                                    FentityModel.Add("FHMaterID", new JObject() { ["Fnumber"] = DetailFmaterialID });//物料
                                    FentityModel.Add("FHQty", FHQTY * FNeedQty);//数量
                                    FentityModel.Add("FHSourceInterID", dr["FID"].ToString());//日计划工单FID
                                    FentityModel.Add("FHSourceEntryID", dr["FENTRYID"].ToString());//日计划工单FENTRYID
                                    FentityModel.Add("FHSourceBillNo", dr["FBILLNO"].ToString());//日计划工单单号
                                    FentityModel.Add("FHSourceBillType", "");//日计划工单类型
                                    Fentity.Add(FentityModel);

                                    model.Add("FEntity", Fentity);
                                    JObject jsonRoot = new JObject()
                                    {
                                        ["Creator"] = "",
                                        ["NeedUpDateFields"] = new JArray(),
                                        ["NeedReturnFields"] = new JArray(),
                                        ["IsDeleteEntry"] = "false",
                                        ["SubSystemId"] = "",
                                        ["IsVerifyBaseDataField"] = "false",
                                        ["Model"] = model
                                    };

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

                                    if (saveIsSuc == "TRUE")
                                    {
                                        //清空值
                                        FHQTY = 0;
                                        LogHelper.Info("单据日期:" + FHDate.AddDays(-FfixleadTime).ToString());
                                    }
                                    else
                                    {
                                        LogHelper.Error(saveIsSuc);

                                    }

                                }
                                else
                                {
                                    FHQTY += DayFHQTY;
                                    continue;
                                }

                            }

                        }

                    }
                }
            }

        }

        public void Extraction3()
        {

@@ -988,6 +1255,8 @@
 and FCloseStatus in('A') and FMRPCLOSESTATUS in('A')
";
            List<PurchaseInventory> PurchaseInventory = DBServiceHelper.ExecuteDataSet(Context, sql).ToModelList<PurchaseInventory>();
            LogHelper.Info("采购订单数据" + sql);


            sql = string.Format(@"
 /*dialect*/
@@ -1000,13 +1269,13 @@
join t_BD_MaterialBase t4 on t3.FMATERIALID = t4.FMATERIALID
join t_BD_MaterialPlan t5 on t3.FMATERIALID = t5.FMATERIALID
WHERE  DATEDIFF(D,GETDATE(),FHMASTERDATE) >=0
AND FPLANCOUNT>FCOMPLETECOUNT
AND FDayPlanQuantity>FCOMPLETECOUNT
AND FERPCLSID=1 
AND T2.FENTRYID not in (select distinct FHSOURCEENTRYID from CG_PODEMANDPLANBILLSUB where FHSOURCEINTERID IN ({0}))
AND T1.FID in ({0})
order by FHMASTERDATE
", FDayPlanWorkID);

            LogHelper.Info("提料数据" + sql);
            DataSet ds = DBServiceHelper.ExecuteDataSet(Context, sql);
            List<DayPlanPpbom> DayPlanPpbom = ds.ToModelList<DayPlanPpbom>();

@@ -1045,8 +1314,8 @@



            sql = @"select T1.FID,FPLANCOUNT,FCOMPLETECOUNT,t2.FHQTY, FHPRDORGID FStockOrgId from    SC_WORKBILLSORTBILLMAIN T1 
JOIN SC_WORKBILLSORTBILLSUB T2 ON T1.FID=T2.FID WHERE  DATEDIFF(D,GETDATE(),FHMASTERDATE) >=0 AND FPLANCOUNT>FCOMPLETECOUNT AND T1.FID in (" + FDayPlanWorkID + ")";
            sql = @"select T1.FID,FDayPlanQuantity,FCOMPLETECOUNT,t2.FHQTY, FHPRDORGID FStockOrgId from    SC_WORKBILLSORTBILLMAIN T1 
JOIN SC_WORKBILLSORTBILLSUB T2 ON T1.FID=T2.FID WHERE  DATEDIFF(D,GETDATE(),FHMASTERDATE) >=0 AND FDayPlanQuantity>FCOMPLETECOUNT AND T1.FID in (" + FDayPlanWorkID + ")";
            DataSet Complete = DBServiceHelper.ExecuteDataSet(Context, sql);
            List<DayPlanPpbom> CompleteList = ds.ToModelList<DayPlanPpbom>();
            #region
@@ -1093,6 +1362,8 @@
            var MaterialIDList = DayPlanPpbom.GroupBy(p => new { p.FMATERIALID, p.FStockOrgId }).Select(x => new PODemandPlanTemp { FMATERIALID = x.Key.FMATERIALID, FStockOrgId = x.Key.FStockOrgId }).ToList();
            var _MaterialIDList = MaterialIDList.Where(p => p.FMATERIALID == "105773").ToList();

            LogHelper.Info("物料行数" + MaterialIDList.Count);

            //提料计划数据集临时存储集合
            List<PODemandPlanTemp> PODemandPlanTemp = new List<PODemandPlanTemp>();
            //sql集合 更新采购订单占用的提料计划数量
@@ -1100,6 +1371,7 @@
            //MaterialIDList = new List<string> { "105773" };
            foreach (var item in MaterialIDList)
            {
                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;//最小起订量
@@ -1111,6 +1383,7 @@
                List<int> FEntryIdList = new List<int>();
                foreach (var _item in _DayPlanPpbom)
                {

                    if (NeedQty == 0)
                        DATE = Convert.ToDateTime(_item.FHMASTERDATE).AddDays(-FFIXLEADTIME);

@@ -1128,6 +1401,9 @@

                        foreach (var Purchase in _PurchaseInventory)
                        {
                            LogHelper.Info("Purchase");
                            LogHelper.Info("物料：" + Purchase.FMATERIALID + ",数量：" + Purchase.FQTY);

                            if (Purchase.FQTY >= NeedQty)
                            {
                                List<int> mmm = new List<int>();
@@ -1413,6 +1689,8 @@
                JObject saveObj = JObject.Parse(result);
                string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper();

                LogHelper.Info("最后");
                LogHelper.Info(jsonRoot.ToString());
                if (saveIsSuc == "TRUE")
                {
                }
@@ -1429,3 +1707,5 @@
        }
    }
}