虚拟齐套分析

王 垚

2021-02-28 dda3dd912d5507be2fc56f917d217096bd2775a4

虚拟齐套分析

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

@@ -18,14 +18,13 @@
    public class ProCompleteAnalysisDetail : AbstractDynamicFormPlugIn
    {
        bool did = false;
        string FHMainICMOInterIDr = "";
        string FHMainICMOEntryID = "";
        string FHICMOInterID = "";
        string FHICMOInterID = "";//日计划工单内码
        string tempName = "JIT_MOMaterReadysBill";//表名称
        public override void OnLoad(EventArgs e)
        {
            LogHelper.Info("[齐套分析明细加载列表]");
            base.OnLoad(e);
            if (this.View.OpenParameter.GetCustomParameter("FHMainICMOInterID") == null)
            if (this.View.OpenParameter.GetCustomParameter("FHICMOInterID") == null)
            {
                SearchList();
                ChangeColor();
@@ -34,10 +33,10 @@
            {
                if (did)
                    return;
                  FHMainICMOInterIDr = this.View.OpenParameter.GetCustomParameter("FHMainICMOInterID").ToString();//主生产订单主内码
                  FHMainICMOEntryID = this.View.OpenParameter.GetCustomParameter("FHMainICMOEntryID").ToString();// 主生产订单子内码
                if (this.View.OpenParameter.GetCustomParameter("tempName") != null)
                    tempName = this.View.OpenParameter.GetCustomParameter("tempName")?.ToString();//表名
                  FHICMOInterID = this.View.OpenParameter.GetCustomParameter("FHICMOInterID").ToString();//日计划订单主内码
                string sql = string.Format(@"/*dialect*/select * from view_JIT_MOMaterReadysBill where FHMainICMOInterIDr ='{0}' and FHMainICMOEntryID ='{1}' and FHICMOInterID='{2}' order by FPlanDate, FHStockQty desc ,FHMaterID ", FHMainICMOInterIDr, FHMainICMOEntryID, FHICMOInterID);
                string sql = string.Format(@"/*dialect*/select * from view_{0} where  FHICMOInterID='{1}' order by FPlanDate, FHStockQty desc ,FHMaterID ", tempName, FHICMOInterID);
                DataTable dt = DBServiceHelper.ExecuteDataSet(this.Context, sql).Tables[0];
                if (dt.Rows.Count > 0)
                {
@@ -63,13 +62,12 @@
                        this.Model.SetValue("FErpClsID", dt.Rows[i]["FErpClsID"], i);//物料属性 
                    }
                }

                ChangeColor();
                 did = true;
            }
        }

        private void ChangeColor() {
        private void ChangeColor()
        {
            Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity");
            //单据体信息转换为列表集合
            DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity);
@@ -79,13 +77,11 @@
            {
                if (Convert.ToString(current["FCompleteDetailStatus"]) == "未齐套")
                {
                   // grid.SetRowBackcolor("#FF0000", _i);
                    grid.SetRowBackcolor("#71FF98CB", _i);
                }
                _i++;
            }
        }
       
        public override void BarItemClick(BarItemClickEventArgs e)
        {
            base.BarItemClick(e);
@@ -106,11 +102,6 @@
            base.EntityRowClick(e);
            DynamicFormShowParameter formPa = new DynamicFormShowParameter();
            formPa.FormId = "paez_CompleteAnalysisMateriel";
            /* jsonModel.Add("FHMainICMOInterID", FMainInterID);//主生产订单主内码
             jsonModel.Add("FHMainICMOEntryID", FMainEntryID);//主生产订单子内码
             jsonModel.Add("FHICMOInterID", FDayPlanWorkID);//日计划订单主内码
            current["FMatrailId_Id"].ToString(), current["FHMainSourceInterID"].ToString(), current["FHMainSourceEntryID"].ToString(), current["FDayPlanWorkID"].ToString())
            */
            formPa.CustomParams.Add("FHMainICMOInterIDr", Convert.ToString(this.View.Model.GetValue("FHMainICMOInterIDr", e.Row).ToString()));
            formPa.CustomParams.Add("FMatrailId", Convert.ToString((this.View.Model.GetValue("FMateralDetailId", e.Row) as DynamicObject)["Id"].ToString()));
            this.View.ShowForm(formPa);
@@ -120,8 +111,8 @@
        /// </summary>
        public void SearchList()
        {
            string sqlwhere = $" where FHMainICMOInterIDr ='{FHMainICMOInterIDr}' and FHMainICMOEntryID ='{FHMainICMOEntryID}' and FHICMOInterID='{FHICMOInterID}'";
            string sql = string.Format(@"/*dialect*/select * from view_JIT_MOMaterReadysBill  ");
            string sqlwhere = $" where  FHICMOInterID='{FHICMOInterID}'";
            string sql = string.Format($"/*dialect*/select * from view_{tempName}");
            //物料
            if ((this.Model.GetValue("FMateralId") as DynamicObject) != null)
            {
@@ -188,5 +179,4 @@
        }
    }
}

 

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

@@ -42,7 +42,6 @@
        List<PRD_Inventory> ListInventoryByStockOrgId;//物料总库存

        private bool isSearched = false;

        public override void DataChanged(DataChangedEventArgs e)
        {
            if (!isSearched) return;
@@ -93,14 +92,25 @@
                {
                    RelieveLock();
                }
                //保存
                if (e.BarItemKey.ToUpper() == "BTNSAVE")
                {
                    Save();
                }
                //齐套分析
                if (e.BarItemKey.ToUpper() == "TBCOMPLETE")
                {
                    COMPLETE();

                }
                //tbXnqt虚拟齐套
                if (e.BarItemKey.ToUpper() == "TBXNQT")
                {
                    Xnqt();
                }
                //tb_CKXNQT 查看虚拟齐套
                if (e.BarItemKey.ToUpper() == "TB_CKXNQT")
                {
                    XnqtLook();
                }
                //提料计划
                if (e.BarItemKey.ToUpper() == "TBEXTRACTION")
@@ -130,6 +140,7 @@
                {
                    Release();
                }

            }
            catch (Exception ex)
            {
@@ -166,12 +177,26 @@
            {
                //齐套分析明细列表跳转
                formPa.FormId = "paez_CompleteAnalysisDetail";
                formPa.CustomParams.Add("FHMainICMOInterID", Convert.ToString(this.View.Model.GetValue("FHMainSourceInterID", e.Row).ToString()));
                formPa.CustomParams.Add("FHMainICMOEntryID", Convert.ToString(this.View.Model.GetValue("FHMainSourceEntryID", e.Row).ToString()));
                formPa.CustomParams.Add("FHICMOInterID", Convert.ToString(this.View.Model.GetValue("FDayPlanWorkID", e.Row).ToString()));
                formPa.CustomParams.Add("FMatrailId_Id", Convert.ToString((this.View.Model.GetValue("FMatrailId", e.Row) as DynamicObject)["Id"].ToString()));

            }
            this.View.ShowForm(formPa);
        }
        /// <summary>
        /// 查看虚拟齐套
        /// </summary>
        public void XnqtLook()
        {
            var _key = this.View.Model.GetEntryCurrentRowIndex("FEntity");
            if (_key < 0)
            {
                this.View.ShowErrMessage("请选择需要查看的单据行！");
                return;
            }
            DynamicFormShowParameter formPa = new DynamicFormShowParameter();
            //齐套分析明细列表跳转
            formPa.FormId = "paez_CompleteAnalysisDetail";
            formPa.CustomParams.Add("FHICMOInterID", Convert.ToString(this.View.Model.GetValue("FDayPlanWorkID", _key).ToString()));
            formPa.CustomParams.Add("tempName", "JIT_MOMaterReadysBillXn");
            this.View.ShowForm(formPa);
        }
        /// <summary>
@@ -445,7 +470,6 @@
            },
            (t) => { });
        }

        /// <summary>
        /// 齐套分析
        /// </summary>
@@ -1166,6 +1190,233 @@

            }
        }

        /// <summary>
        /// 虚拟齐套
        /// </summary>
        public void Xnqt()
        {
            LogHelper.Info("虚拟齐套分析sql模式开始准备数据：");
            string sql = "";
            try
            {
                Stopwatch sw = new Stopwatch();
                sw.Start();//开始计时
                LogHelper.Info("虚拟齐套分析开始准备数据：" + sw.Elapsed);
                //清空齐套临时表
                DBServiceHelper.Execute(Context, "/*dialect*/  delete JIT_MOMaterReadysBillXn ");
                ////更新状态
                //DBServiceHelper.Execute(Context, "/*dialect*/  update Sc_WorkBillSortBillsub set FCOMPLETE='' ");
                //获取当前即时库存 按照物料+库存组织+货主+库存数量  
                DataSet ds = DBServiceHelper.ExecuteDataSet(Context, @" 
/*dialect*/ 
  select  isnull(a.FBASEQTY,0)-ISNULL(b.FHUseQty,0) FBASEQTY,T1.FStockOrgId,T1.FMATERIALID,a.FOWNERID,c.FNUMBER from 
(
select a.FHPRDORGID FStockOrgId,T2.FMATERIALID from SC_WORKBILLSORTBILLMAIN a
join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
LEFT JOIN(select FID,FMATERIALID from T_PRD_PPBOMENTRY
group by FMATERIALID,FID) T2 on T1.FID = t2.FID
group by a.FHPRDORGID,T2.FMATERIALID
) T1 
LEFT JOIN T_STK_Inventory a on T1.FStockOrgId = a.FStockOrgId and T1.FMATERIALID =a.FMATERIALID 
LEFT JOIN JIT_MOMaterReadysBillXn B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID and a.FOwnerId = b.FOwnerId 
LEFT join T_ORG_Organizations c on a.FOWNERID = c.FORGID
join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
where d.FAvailableComplete<>0
--where T1.FMATERIALID in (105773)
");
                ListInventory = ds.ToModelList<PRD_Inventory>();
                //总库存 物料+库存组织+库存数量
                ds = DBServiceHelper.ExecuteDataSet(Context, @" 
/*dialect*/ 
 select  sum(isnull(a.FBASEQTY,0)-ISNULL(b.FHUseQty,0))FBASEQTY,T1.FStockOrgId,T1.FMATERIALID from 
(
select a.FHPRDORGID FStockOrgId,T2.FMATERIALID from SC_WORKBILLSORTBILLMAIN a
join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
LEFT JOIN(select FID,FMATERIALID from T_PRD_PPBOMENTRY
group by FMATERIALID,FID) T2 on T1.FID = t2.FID
group by a.FHPRDORGID,T2.FMATERIALID
) T1 
LEFT JOIN T_STK_Inventory a on T1.FStockOrgId = a.FStockOrgId and T1.FMATERIALID =a.FMATERIALID 
LEFT JOIN JIT_MOMaterReadysBillXn B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID
join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
where d.FAvailableComplete<>0
--where T1.FMATERIALID in (105773)
group by T1.FStockOrgId,T1.FMATERIALID
");
                ListInventoryByStockOrgId = ds.ToModelList<PRD_Inventory>();
                //需要虚拟齐套分析的数据
                sql = @"
 /*dialect*/
select a.FPRDMOMAINID,a.FPRDMOENTYID,a.FID,b.FEntryID,a.FHPRDORGID FStockOrgId,t5.FNUMBER as OrgFnumber,a.FHOrderLev,b.FHQTY,b.FHMASTERDATE,T1.FBillNo,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty,
T2.FMATERIALID FMATERIALID2, T4.FNumber,T2.FNeedQty,FNeedQty/FQty dwyl,(FHQTY-ISNULL(FProductNum,0))*(FNeedQty/FQty) PlanCount,TBase.FErpClsID  from SC_WORKBILLSORTBILLMAIN a
join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
LEFT JOIN(select FID, MAX(FENTRYID) FENTRYID, SUM(FNeedQty) FNeedQty, FMATERIALID from T_PRD_PPBOMENTRY
group by FMATERIALID, FID ) T2 on T1.FID = t2.FID
 --LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID
JOIN T_BD_MATERIAL T4 on T2.FMATERIALID = T4.FMATERIALID
JOIN t_BD_MaterialBase TBase on T2.FMATERIALID = TBase.FMATERIALID
JOIN T_ORG_Organizations t5 on a.FHPRDORGID = t5.FORGID
--条件为今天往后的日计划 + 今天之前未关闭的日计划
where (FHEntryCloseDate is null and DATEDIFF(D,GETDATE(),FHMASTERDATE)<0)or DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0
and b.FHQTY>0
ORDER BY b.FHMASTERDATE,a.FHOrderLev 
";
                ds = DBServiceHelper.ExecuteDataSet(Context, sql);
                List<PRD_PPBOM> BomList = ds.ToModelList<PRD_PPBOM>();
                //日计划+生产订单子表编码ID
                var DatePlanList = BomList.GroupBy(p => new { p.FHMASTERDATE, p.FPRDMOENTYID }).Select(x => new PRD_PPBOM { FHMASTERDATE = x.Key.FHMASTERDATE, FPRDMOENTYID = x.Key.FPRDMOENTYID }).ToList();
                //CompleteAnalysisTempModel 用于存储临时需要插入数据
                List<CompleteAnalysisTempModel> completeAnalysisTempModel = new List<CompleteAnalysisTempModel>();
                int i = 1;
                //创建字典 用于储存物料 + 占用数量
                Dictionary<string, decimal> occupyDic = new Dictionary<string, decimal>();
                foreach (var item in DatePlanList)
                {

                    //筛选出当日计划数据
                    var _BomList = BomList.Where(x => x.FHMASTERDATE == item.FHMASTERDATE && x.FPRDMOENTYID == item.FPRDMOENTYID).ToList();
                    foreach (var _item in _BomList)
                    {
                        //该日计划物料需要数 
                        decimal need = _item.PlanCount;
                        var sjkcList = ListInventoryByStockOrgId.Where(x => x.FMATERIALID == _item.FMATERIALID2 && x.FStockOrgId == _item.FStockOrgId).FirstOrDefault();
                        //真正需要取计算的数量（拆分货主）
                        //货主匹配
                        List<PRD_Inventory> _ListInventory = ListInventory.Where(c => c.FMATERIALID == _item.FMATERIALID2 && c.FStockOrgId == _item.FStockOrgId && c.FBASEQTY > 0).ToList();
                        if (_ListInventory.Count > 0)
                        {
                            decimal _fLackCount = _item.PlanCount > sjkcList.FBASEQTY ? (_item.PlanCount - sjkcList.FBASEQTY) : 0;//缺料数量
                            foreach (var Inventory in _ListInventory)
                            {

                                if (need == 0)
                                    break;
                                decimal fCompleteCount = need >= Inventory.FBASEQTY ? Inventory.FBASEQTY : need;//齐套数量
                                decimal occupyMaterial = 0;
                                if (!occupyDic.ContainsKey(_item.FMATERIALID2))
                                {
                                    occupyDic.Add(_item.FMATERIALID2, fCompleteCount);
                                }
                                else
                                {
                                    occupyMaterial = occupyDic[_item.FMATERIALID2];
                                    occupyDic[_item.FMATERIALID2] = occupyMaterial + fCompleteCount;
                                }
                                completeAnalysisTempModel.Add(new Demo.Model.Model.CompleteAnalysisTempModel
                                {
                                    FHMainICMOInterIDr = _item.FPRDMOMAINID,
                                    FHMainICMOEntryID = _item.FPRDMOENTYID,
                                    FHICMOInterID = _item.FID.ToString(),
                                    FHICMOEntryID = _item.FEntryID.ToString(),
                                    FHMaterID = _item.FMATERIALID2,
                                    FHStockOrgID = _item.FStockOrgId,
                                    FHPRDORGID = _item.FStockOrgId,
                                    FErpClsID = _item.FErpClsID,
                                    FPRDBillNo = _item.FMOBillNO,
                                    FOwnerTypeId = "BD_OwnerOrg",
                                    FOwnerId = Inventory.FOwnerId,
                                    FUnitDosage = _item.dwyl.ToString(),//单位用量
                                    FSumPlanCount = _item.PlanCount.ToString(),//计划总数量
                                    FCompleteCount1 = fCompleteCount, //齐套数量
                                    FLackCount = _fLackCount,//缺料数量
                                    FHStockQty = sjkcList.FBASEQTY,//即时库存
                                    FHLeftQty = Inventory.FBASEQTY,//可用数量
                                    FOccupyCount = occupyMaterial,//占用数量
                                    /* 2021 1.26 更改为need
                                    FComPlete = sjkcList.FBASEQTY >= _item.PlanCount ? "齐套" : "未齐套",
                                    */
                                    FComPlete = sjkcList.FBASEQTY >= need ? "齐套" : "未齐套",
                                    FPlanDate = item.FHMASTERDATE // 日计划日期
                                });
                                //扣减总库存数量
                                sjkcList.FBASEQTY = sjkcList.FBASEQTY >= fCompleteCount ? sjkcList.FBASEQTY - fCompleteCount : 0;
                                //剩余日计划需求数量
                                need = need >= Inventory.FBASEQTY ? need - Inventory.FBASEQTY : 0;
                                //扣减消耗库存
                                Inventory.FBASEQTY = Inventory.FBASEQTY - fCompleteCount;
                            }
                        }
                        else
                        {
                            decimal occupyMaterial = 0;
                            if (occupyDic.ContainsKey(_item.FMATERIALID2))
                            {
                                occupyMaterial = occupyDic[_item.FMATERIALID2];
                            }
                            completeAnalysisTempModel.Add(new Demo.Model.Model.CompleteAnalysisTempModel
                            {
                                FHMainICMOInterIDr = _item.FPRDMOMAINID,
                                FHMainICMOEntryID = _item.FPRDMOENTYID,
                                FHICMOInterID = _item.FID.ToString(),
                                FHICMOEntryID = _item.FEntryID.ToString(),
                                FHMaterID = _item.FMATERIALID2,
                                FErpClsID = _item.FErpClsID,
                                FHStockOrgID = _item.FStockOrgId,
                                FHPRDORGID = _item.FStockOrgId,
                                FPRDBillNo = _item.FMOBillNO,
                                FOwnerTypeId = "BD_OwnerOrg",
                                FUnitDosage = _item.dwyl.ToString(),//单位用量
                                FSumPlanCount = _item.PlanCount.ToString(),//计划总数量
                                FCompleteCount1 = 0, //齐套数量
                                FLackCount = _item.PlanCount,//缺料数量
                                FHStockQty = 0,//即时库存
                                FHLeftQty = 0,//可用数量
                                FOccupyCount = occupyMaterial,//占用数量
                                FComPlete = "未齐套",
                                FPlanDate = item.FHMASTERDATE // 日计划日期
                            });
                        }
                    }
                    //Thread.Sleep(100);
                    // this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(40) / DatePlanList.Count) * i);
                    i++;

                }
                LogHelper.Info($"虚拟齐套分析，数据准备完成，保存到Model实体，总行数：{completeAnalysisTempModel.Count}，耗时：" + sw.Elapsed);
                List<string> sqlList = new List<string>();
                int jdtmmm = 1;
                foreach (var item in completeAnalysisTempModel)
                {
                    sql = string.Format(@"
/*dialect*/
delete   z_JIT_MOMaterReadysBillXn
insert into z_JIT_MOMaterReadysBillXn (column1)
values('')
insert into JIT_MOMaterReadysBillXn
(FID, FBILLNO, FDOCUMENTSTATUS, FHMAINICMOINTERIDR, FHMAINICMOENTRYID, FHICMOINTERID, FHICMOENTRYID, FHMATERID, FHSTOCKORGID, FHSTOCKQTY, FHLEFTQTY,  FHPRDORGID, FUNITDOSAGE, FSUMPLANCOUNT,  FPRDBILLNO, FOWNERID, FOWNERTYPEID, FIDENTIFICAT, FPLANDATE, FCOMPLETE, FLACKCOUNT, FOCCUPYCOUNT, FCOMPLETECOUNT1,FErpClsID)
select id, '', 'A', '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', 'BD_OwnerOrg', '','{13}','{14}','{15}','{16}','{17}',{18} from z_JIT_MOMaterReadysBillXn
delete   z_JIT_MOMaterReadysBillXn
", item.FHMainICMOInterIDr, item.FHMainICMOEntryID, item.FHICMOInterID, item.FHICMOEntryID, item.FHMaterID, item.FHStockOrgID, item.FHStockQty, item.FHLeftQty, item.FHPRDORGID, item.FUnitDosage, item.FSumPlanCount, item.FPRDBillNo, item.FOwnerId == null ? "0" : item.FOwnerId, item.FPlanDate.ToString("yyyy-MM-dd"), item.FComPlete, item.FLackCount, item.FOccupyCount, item.FCompleteCount1, item.FErpClsID);
                    sqlList.Add(sql);
                    //this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(98) / completeAnalysisTempModel.Count) * jdtmmm);
                    jdtmmm++;
                }
                DBServiceHelper.ExecuteBatch(Context, sqlList);
                //                //执行完成后 更新日计划工单状态
                //                DBServiceHelper.Execute(Context, @"
                ///*dialect*/update Sc_WorkBillSortBillSub set FComplete = '未齐套' where FEntryID IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBillXn where isnull(FCOMPLETE,'未齐套') ='未齐套')
                //");
                //                DBServiceHelper.Execute(Context, @"
                ///*dialect*/update Sc_WorkBillSortBillSub set FComplete = '齐套' where FEntryID NOT IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBillXn where isnull(FCOMPLETE,'未齐套') ='未齐套')
                //");
                sw.Stop();//结束计时
                LogHelper.Info("虚拟齐套分析运行总时长：" + sw.Elapsed);
            }
            catch (Exception ex)
            {
                this.View.ShowErrMessage("异常："+ ex.Message.ToString());
                LogHelper.Error("虚拟齐套分析跳出：" + ex.Message.ToString());
            }
            finally
            {
                this.View.ShowMessage("执行完成！");
            }
        }

        /// <summary>
        /// 查询
        /// </summary>