代码上传

王 垚

2021-08-04 ad5269c07378e5e324edc42d2526ce17d1a1e607

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

@@ -390,7 +390,7 @@
            DayPlanPPBomBill();
            //Extraction();
            //SearchList();
            

            //}
            //else
            //    this.View.ShowMessage("操作失败，联系管理员！");
@@ -407,7 +407,7 @@
            DynamicObjectCollection entityDataObjoct = this.View.Model.GetEntityDataObject(entity);
            List<string> sqlList = new List<string>();
            string 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())";
              DBServiceHelper.Execute(Context, sql);
            DBServiceHelper.Execute(Context, 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"])} ";
@@ -519,7 +519,7 @@
                {
                    // 需要捕获错误，以确保处理结束时，关闭进度滚动界面
                    // 引入功能实际处理函数
                    this.CompleteSetAnalysisBySQL();
                    this.CompleteSetAnalysisByDataTable();
                }
                finally
                {
@@ -1254,6 +1254,272 @@

            }
        }
        public void CompleteSetAnalysisByDataTable()
        {
            LogHelper.Info("齐套分析sql模式开始准备数据：");
            string sql = "";
            try
            {
                Stopwatch sw = new Stopwatch();
                sw.Start();//开始计时
                int maxFid = DBServiceHelper.ExecuteScalar<int>(Context, "select max(FID)FID FROM  JIT_MOMaterReadysBill", 0);
                //清空齐套临时表
                DBServiceHelper.Execute(Context, "/*dialect*/  delete JIT_MOMaterReadysBill ");
                //更新状态
                DBServiceHelper.Execute(Context, "/*dialect*/  update Sc_WorkBillSortBillsub set FCOMPLETE='' ");
                //获取当前即时库存 按照物料+库存组织+货主+库存数量  
                DataSet ds = DBServiceHelper.ExecuteDataSet(Context, @" 
/*dialect*/ 
  select  isnull(a.FBASEQTY,0)-ISNULL(b.FHUseQty,0) FBASEQTY,T1.FStockOrgId,T1.FMATERIALID,a.FOWNERID,c.FNUMBER from 
(
select a.FHPRDORGID FStockOrgId,T2.FMATERIALID from SC_WORKBILLSORTBILLMAIN a
join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
LEFT JOIN(select FID,FMATERIALID from T_PRD_PPBOMENTRY
group by FMATERIALID,FID) T2 on T1.FID = t2.FID
group by a.FHPRDORGID,T2.FMATERIALID
) T1 
LEFT JOIN T_STK_Inventory a on T1.FStockOrgId = a.FStockOrgId and T1.FMATERIALID =a.FMATERIALID 
LEFT JOIN JIT_MOMaterReadysBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID and a.FOwnerId = b.FOwnerId 
LEFT join T_ORG_Organizations c on a.FOWNERID = c.FORGID
join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
where d.FAvailableComplete<>0
--where T1.FMATERIALID in (105773)
");
                ListInventory = ds.ToModelList<PRD_Inventory>();
                //总库存 物料+库存组织+库存数量
                ds = DBServiceHelper.ExecuteDataSet(Context, @" 
/*dialect*/ 
 select  sum(isnull(a.FBASEQTY,0)-ISNULL(b.FHUseQty,0))FBASEQTY,T1.FStockOrgId,T1.FMATERIALID from 
(
select a.FHPRDORGID FStockOrgId,T2.FMATERIALID from SC_WORKBILLSORTBILLMAIN a
join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
LEFT JOIN(select FID,FMATERIALID from T_PRD_PPBOMENTRY
group by FMATERIALID,FID) T2 on T1.FID = t2.FID
group by a.FHPRDORGID,T2.FMATERIALID
) T1 
LEFT JOIN T_STK_Inventory a on T1.FStockOrgId = a.FStockOrgId and T1.FMATERIALID =a.FMATERIALID 
LEFT JOIN JIT_MOMaterReadysBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID
join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
where d.FAvailableComplete<>0
--where T1.FMATERIALID in (105773)
group by T1.FStockOrgId,T1.FMATERIALID
");
                ListInventoryByStockOrgId = ds.ToModelList<PRD_Inventory>();
                //需要齐套分析的数据
                sql = @"
 /*dialect*/
select a.FPRDMOMAINID,a.FPRDMOENTYID,a.FID,b.FEntryID,a.FHPRDORGID FStockOrgId,t5.FNUMBER as OrgFnumber,a.FHOrderLev,b.FHQTY,b.FHMASTERDATE,T1.FBillNo,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty,
T2.FMATERIALID FMATERIALID2, T4.FNumber,T2.FNeedQty,FNeedQty/FQty dwyl,(FHQTY-ISNULL(FProductNum,0))*(FNeedQty/FQty) PlanCount,TBase.FErpClsID  from SC_WORKBILLSORTBILLMAIN a
join SC_WORKBILLSORTBILLSub b on a.FID = b.FID
join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID
LEFT JOIN(select FID, MAX(FENTRYID) FENTRYID, SUM(FNeedQty) FNeedQty, FMATERIALID from T_PRD_PPBOMENTRY
group by FMATERIALID, FID ) T2 on T1.FID = t2.FID
 --LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID
JOIN T_BD_MATERIAL T4 on T2.FMATERIALID = T4.FMATERIALID
JOIN t_BD_MaterialBase TBase on T2.FMATERIALID = TBase.FMATERIALID
JOIN T_ORG_Organizations t5 on a.FHPRDORGID = t5.FORGID
--条件为今天往后的日计划 + 今天之前未关闭的日计划
where (FHEntryCloseDate is null and DATEDIFF(D,GETDATE(),FHMASTERDATE)<0)or DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0
and b.FHQTY>0
ORDER BY b.FHMASTERDATE,a.FHOrderLev 
";
                ds = DBServiceHelper.ExecuteDataSet(Context, sql);
                List<PRD_PPBOM> BomList = ds.ToModelList<PRD_PPBOM>();
                //日计划+生产订单子表编码ID
                var DatePlanList = BomList.GroupBy(p => new { p.FHMASTERDATE, p.FPRDMOENTYID }).Select(x => new PRD_PPBOM { FHMASTERDATE = x.Key.FHMASTERDATE, FPRDMOENTYID = x.Key.FPRDMOENTYID }).ToList();
                //CompleteAnalysisTempModel 用于存储临时需要插入数据
                List<CompleteAnalysisTempModel> completeAnalysisTempModel = new List<CompleteAnalysisTempModel>();
                int i = 1;
                //创建字典 用于储存物料 + 占用数量
                Dictionary<string, decimal> occupyDic = new Dictionary<string, decimal>();
                LogHelper.Info("齐套分析准备数据完成：" + sw.Elapsed);
                foreach (var item in DatePlanList)
                {
                    //筛选出当日计划数据
                    var _BomList = BomList.Where(x => x.FHMASTERDATE == item.FHMASTERDATE && x.FPRDMOENTYID == item.FPRDMOENTYID).ToList();
                    foreach (var _item in _BomList)
                    {
                        //该日计划物料需要数 
                        decimal need = _item.PlanCount;
                        var sjkcList = ListInventoryByStockOrgId.Where(x => x.FMATERIALID == _item.FMATERIALID2 && x.FStockOrgId == _item.FStockOrgId).FirstOrDefault();
                        //真正需要取计算的数量（拆分货主）
                        //货主匹配
                        List<PRD_Inventory> _ListInventory = ListInventory.Where(c => c.FMATERIALID == _item.FMATERIALID2 && c.FStockOrgId == _item.FStockOrgId && c.FBASEQTY > 0).ToList();
                        if (_ListInventory.Count > 0)
                        {
                            decimal _fLackCount = _item.PlanCount > sjkcList.FBASEQTY ? (_item.PlanCount - sjkcList.FBASEQTY) : 0;//缺料数量
                            foreach (var Inventory in _ListInventory)
                            {
                                if (need == 0)
                                    break;
                                decimal fCompleteCount = need >= Inventory.FBASEQTY ? Inventory.FBASEQTY : need;//齐套数量
                                decimal occupyMaterial = 0;
                                if (!occupyDic.ContainsKey(_item.FMATERIALID2))
                                {
                                    occupyDic.Add(_item.FMATERIALID2, fCompleteCount);
                                }
                                else
                                {
                                    occupyMaterial = occupyDic[_item.FMATERIALID2];
                                    occupyDic[_item.FMATERIALID2] = occupyMaterial + fCompleteCount;
                                }
                                completeAnalysisTempModel.Add(new Demo.Model.Model.CompleteAnalysisTempModel
                                {
                                    FHMainICMOInterIDr = _item.FPRDMOMAINID,
                                    FHMainICMOEntryID = _item.FPRDMOENTYID,
                                    FHICMOInterID = _item.FID.ToString(),
                                    FHICMOEntryID = _item.FEntryID.ToString(),
                                    FHMaterID = _item.FMATERIALID2,
                                    FHStockOrgID = _item.FStockOrgId,
                                    FHPRDORGID = _item.FStockOrgId,
                                    FErpClsID = _item.FErpClsID,
                                    FPRDBillNo = _item.FMOBillNO,
                                    FOwnerTypeId = "BD_OwnerOrg",
                                    FOwnerId = Inventory.FOwnerId,
                                    FUnitDosage = _item.dwyl.ToString(),//单位用量
                                    FSumPlanCount = _item.PlanCount.ToString(),//计划总数量
                                    FCompleteCount1 = fCompleteCount, //齐套数量
                                    FLackCount = _fLackCount,//缺料数量
                                    FHStockQty = sjkcList.FBASEQTY,//即时库存
                                    FHLeftQty = Inventory.FBASEQTY,//可用数量
                                    FOccupyCount = occupyMaterial,//占用数量
                                    /* 2021 1.26 更改为need
                                    FComPlete = sjkcList.FBASEQTY >= _item.PlanCount ? "齐套" : "未齐套",
                                    */
                                    FComPlete = sjkcList.FBASEQTY >= need ? "齐套" : "未齐套",
                                    FPlanDate = item.FHMASTERDATE // 日计划日期
                                });
                                //扣减总库存数量
                                sjkcList.FBASEQTY = sjkcList.FBASEQTY >= fCompleteCount ? sjkcList.FBASEQTY - fCompleteCount : 0;
                                //剩余日计划需求数量
                                need = need >= Inventory.FBASEQTY ? need - Inventory.FBASEQTY : 0;
                                //扣减消耗库存
                                Inventory.FBASEQTY = Inventory.FBASEQTY - fCompleteCount;
                            }
                        }
                        else
                        {
                            decimal occupyMaterial = 0;
                            if (occupyDic.ContainsKey(_item.FMATERIALID2))
                            {
                                occupyMaterial = occupyDic[_item.FMATERIALID2];
                            }
                            completeAnalysisTempModel.Add(new Demo.Model.Model.CompleteAnalysisTempModel
                            {
                                FHMainICMOInterIDr = _item.FPRDMOMAINID,
                                FHMainICMOEntryID = _item.FPRDMOENTYID,
                                FHICMOInterID = _item.FID.ToString(),
                                FHICMOEntryID = _item.FEntryID.ToString(),
                                FHMaterID = _item.FMATERIALID2,
                                FErpClsID = _item.FErpClsID,
                                FHStockOrgID = _item.FStockOrgId,
                                FHPRDORGID = _item.FStockOrgId,
                                FPRDBillNo = _item.FMOBillNO,
                                FOwnerTypeId = "BD_OwnerOrg",
                                FUnitDosage = _item.dwyl.ToString(),//单位用量
                                FSumPlanCount = _item.PlanCount.ToString(),//计划总数量
                                FCompleteCount1 = 0, //齐套数量
                                FLackCount = _item.PlanCount,//缺料数量
                                FHStockQty = 0,//即时库存
                                FHLeftQty = 0,//可用数量
                                FOccupyCount = occupyMaterial,//占用数量
                                FComPlete = "未齐套",
                                FPlanDate = item.FHMASTERDATE // 日计划日期
                            });
                        }
                    }
                    //Thread.Sleep(100);
                    // this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(40) / DatePlanList.Count) * i);
                    i++;

                }
                LogHelper.Info($"齐套分析，数据准备完成，保存到Model实体，总行数：{completeAnalysisTempModel.Count}，耗时：" + sw.Elapsed);
                List<string> sqlList = new List<string>();
                DataTable insertDT = new DataTable();
                insertDT.TableName = "JIT_MOMaterReadysBill";
                insertDT.Columns.Add("FID", typeof(long));
                //insertDT.Columns.Add("FBILLNO", typeof(string));
                insertDT.Columns.Add("FDOCUMENTSTATUS", typeof(string));
                insertDT.Columns.Add("FHMAINICMOINTERIDR", typeof(long));
                insertDT.Columns.Add("FHMAINICMOENTRYID", typeof(long));
                insertDT.Columns.Add("FHICMOINTERID", typeof(long));
                insertDT.Columns.Add("FHICMOENTRYID", typeof(long));
                insertDT.Columns.Add("FHMATERID", typeof(long));
                insertDT.Columns.Add("FHSTOCKORGID", typeof(long));
                insertDT.Columns.Add("FHSTOCKQTY", typeof(decimal));
                insertDT.Columns.Add("FHLEFTQTY", typeof(decimal));
                insertDT.Columns.Add("FHPRDORGID", typeof(long));
                insertDT.Columns.Add("FUNITDOSAGE", typeof(decimal));
                insertDT.Columns.Add("FSUMPLANCOUNT", typeof(decimal));
                insertDT.Columns.Add("FPRDBILLNO", typeof(string));
                insertDT.Columns.Add("FOWNERID", typeof(long));
                insertDT.Columns.Add("FOWNERTYPEID", typeof(string));

                //insertDT.Columns.Add("FIDENTIFICAT", typeof(long));

                insertDT.Columns.Add("FPLANDATE", typeof(DateTime));
                insertDT.Columns.Add("FCOMPLETE", typeof(string));
                insertDT.Columns.Add("FLACKCOUNT", typeof(decimal));
                insertDT.Columns.Add("FOCCUPYCOUNT", typeof(decimal));
                insertDT.Columns.Add("FCOMPLETECOUNT1", typeof(decimal));
                insertDT.Columns.Add("FERPCLSID", typeof(string));

                int jdtmmm = 1;
                foreach (var item in completeAnalysisTempModel)
                {
                    DataRow dr = insertDT.NewRow();
                    dr["FID"] = maxFid + jdtmmm;
                    //dr["FBILLNO"] = "";
                    dr["FDOCUMENTSTATUS"] = "A";
                    dr["FHMAINICMOINTERIDR"] = item.FHMainICMOInterIDr;
                    dr["FHMAINICMOENTRYID"] = item.FHMainICMOEntryID;
                    dr["FHICMOINTERID"] = item.FHICMOInterID;
                    dr["FHICMOENTRYID"] = item.FHICMOEntryID;
                    dr["FHMATERID"] = item.FHMaterID;
                    dr["FHSTOCKORGID"] = item.FHStockOrgID;
                    dr["FHSTOCKQTY"] = item.FHStockQty;
                    dr["FHLEFTQTY"] = item.FHLeftQty;
                    dr["FHPRDORGID"] = item.FHPRDORGID;
                    dr["FUNITDOSAGE"] = item.FUnitDosage;
                    dr["FSUMPLANCOUNT"] = item.FSumPlanCount;
                    dr["FPRDBILLNO"] = item.FPRDBillNo;
                    dr["FOWNERID"] = item.FOwnerId == null ? "0" : item.FOwnerId;
                    dr["FOWNERTYPEID"] = "BD_OwnerOrg";

                    //dr["FIDENTIFICAT"] = "";

                    dr["FPLANDATE"] = item.FPlanDate.ToString("yyyy-MM-dd");
                    dr["FCOMPLETE"] = item.FComPlete;
                    dr["FLACKCOUNT"] = item.FLackCount;
                    dr["FOCCUPYCOUNT"] = item.FOccupyCount;
                    dr["FCOMPLETECOUNT1"] = item.FCompleteCount1;
                    dr["FERPCLSID"] = item.FErpClsID;
                    insertDT.Rows.Add(dr);
                    this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(80) / completeAnalysisTempModel.Count) * jdtmmm);
                    jdtmmm++;
                }
                insertDT.EndLoadData(); // 灌入数据结束
                                        // 批量插入到数据库
                DBServiceHelper.BulkInserts(this.Context, string.Empty, string.Empty, insertDT);
                //DBServiceHelper.ExecuteBatch(Context, sqlList);
                //执行完成后 更新日计划工单状态
                DBServiceHelper.Execute(Context, @"
/*dialect*/update Sc_WorkBillSortBillSub set FComplete = '未齐套' where FEntryID IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBill where isnull(FCOMPLETE,'未齐套') ='未齐套')
");
                DBServiceHelper.Execute(Context, @"
/*dialect*/update Sc_WorkBillSortBillSub set FComplete = '齐套' where FEntryID NOT IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBill where isnull(FCOMPLETE,'未齐套') ='未齐套')
");
                sw.Stop();//结束计时
                LogHelper.Info("齐套分析运行总时长：" + sw.Elapsed);
            }
            catch (Exception ex)
            {
                LogHelper.Error("齐套分析跳出：" + ex.Message.ToString());

            }
        }

        /// <summary>
        /// 虚拟齐套
@@ -1804,7 +2070,7 @@
            DBServiceHelper.ExecuteBatch(Context, zxSqlList);
            Stopwatch sw = new Stopwatch();
            sw.Start();//开始计时
            

            LogHelper.Info("生成日计划用料清单准备数据");
            //锁定的日计划不生成
            try
@@ -2441,7 +2707,7 @@
        /// <summary>
        /// 1.锁库，具体字段根据业务单据决定，‘Inv’表示即时库存，‘FInvDetailID’为即时库存ID
        /// </summary>
        public void Reserve()   
        public void Reserve()
        {
            try
            {
@@ -2646,7 +2912,8 @@
            }
        }

        public void SelectMenu() {
        public void SelectMenu()
        {
            //获取单据体信息
            Entity entity = this.View.BillBusinessInfo.GetEntity("FEntity");
            //单据体信息转换为列表集合
@@ -2672,7 +2939,7 @@
                        foreach (var listSelectedRow in listSelectedRowCollection)
                        {
                            this.Model.CreateNewEntryRow("FEntity");
                            this.View.Model.SetValue("FORGID",100, Fseq);
                            this.View.Model.SetValue("FORGID", 100, Fseq);
                            this.View.Model.SetValue("FSCOrderNo", listSelectedRow.DataRow["FBillNo"].ToString(), Fseq);
                            this.View.Model.SetValue("FSRCBILLENTRYSEQ", listSelectedRow.DataRow["t1_FSeq"].ToString(), Fseq);
                            this.View.Model.SetValue("FMatrailId", listSelectedRow.DataRow["FMaterialId_Id"].ToString(), Fseq);