齐套分析增加物料属性字段

王 垚

2021-02-04 3bdc41a3c5a0857b04c1ee4d6f7cc515d1983820

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

@@ -396,7 +396,7 @@
                {
                    // 需要捕获错误，以确保处理结束时，关闭进度滚动界面
                    // 引入功能实际处理函数
                    this.CompleteSetAnalysisBySQL();
                    this.CompleteSetAnalysisBatch();
                }
                finally
                {
@@ -470,18 +470,20 @@
                ListInventoryByStockOrgId = ds.ToModelList<PRD_Inventory>();
                //需要齐套分析的数据
                string sql = @"
/*dialect*/
 /*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
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);
@@ -545,12 +547,13 @@
                                    FHStockQty = sjkcList.FBASEQTY,//即时库存
                                    FHLeftQty = Inventory.FBASEQTY,//可用数量
                                    FOccupyCount = occupyMaterial,//占用数量
                                    FErpClsID = _item.FErpClsID,
                                    /* 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;
                                //剩余日计划需求数量
@@ -584,7 +587,9 @@
                                FHLeftQty = 0,//可用数量
                                FOccupyCount = occupyMaterial,//占用数量
                                FComPlete = "未齐套",
                                FPlanDate = item.FHMASTERDATE // 日计划日期
                                FPlanDate = item.FHMASTERDATE, // 日计划日期
                                FErpClsID = _item.FErpClsID

                            });
                        }
                    }
@@ -627,6 +632,7 @@
                    jsonModel.Add("FLackCount", item.FLackCount);
                    jsonModel.Add("FOccupyCount", item.FOccupyCount);
                    jsonModel.Add("FPlanDate", item.FPlanDate);
                    jsonModel.Add("FErpClsID", item.FErpClsID);
                    if (item.FOwnerId != null)
                        jsonModel.Add("FOwnerId", new JObject() { ["FNUMBER"] = item.FOwnerId });
                    jsonRoot.Add("Model", jsonModel);
@@ -710,18 +716,20 @@
                ListInventoryByStockOrgId = ds.ToModelList<PRD_Inventory>();
                //需要齐套分析的数据
                string sql = @"
/*dialect*/
 /*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
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);
@@ -735,7 +743,6 @@
                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)
@@ -785,6 +792,7 @@
                                    FHStockQty = sjkcList.FBASEQTY,//即时库存
                                    FHLeftQty = Inventory.FBASEQTY,//可用数量
                                    FOccupyCount = occupyMaterial,//占用数量
                                    FErpClsID = _item.FErpClsID,
                                    /* 2021 1.26 更改为need
                                    FComPlete = sjkcList.FBASEQTY >= _item.PlanCount ? "齐套" : "未齐套",
                                    */
@@ -817,6 +825,7 @@
                                FOwnerTypeId = "BD_OwnerOrg",
                                FUnitDosage = _item.dwyl.ToString(),//单位用量
                                FSumPlanCount = _item.PlanCount.ToString(),//计划总数量
                                FErpClsID = _item.FErpClsID,
                                FCompleteCount = 0, //齐套数量
                                FCompleteCount1 = 0, //齐套数量
                                FLackCount = _item.PlanCount,//缺料数量
@@ -861,6 +870,7 @@
                    FBatchModel.Add("FLackCount", item.FLackCount);
                    FBatchModel.Add("FOccupyCount", item.FOccupyCount);
                    FBatchModel.Add("FPlanDate", item.FPlanDate);
                    FBatchModel.Add("FErpClsID", item.FErpClsID);
                    if (item.FOwnerId != null)
                        FBatchModel.Add("FOwnerId", new JObject() { ["FNUMBER"] = item.FOwnerId });
                    Fentity.Add(FBatchModel);
@@ -961,15 +971,16 @@
                ListInventoryByStockOrgId = ds.ToModelList<PRD_Inventory>();
                //需要齐套分析的数据
                sql = @"
/*dialect*/
 /*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
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
@@ -987,7 +998,7 @@
                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)
@@ -1026,6 +1037,7 @@
                                    FHMaterID = _item.FMATERIALID2,
                                    FHStockOrgID = _item.FStockOrgId,
                                    FHPRDORGID = _item.FStockOrgId,
                                    FErpClsID = _item.FErpClsID,
                                    FPRDBillNo = _item.FMOBillNO,
                                    FOwnerTypeId = "BD_OwnerOrg",
                                    FOwnerId = Inventory.FOwnerId,
@@ -1064,6 +1076,7 @@
                                FHICMOInterID = _item.FID.ToString(),
                                FHICMOEntryID = _item.FEntryID.ToString(),
                                FHMaterID = _item.FMATERIALID2,
                                FErpClsID = _item.FErpClsID,
                                FHStockOrgID = _item.FStockOrgId,
                                FHPRDORGID = _item.FStockOrgId,
                                FPRDBillNo = _item.FMOBillNO,
@@ -1096,10 +1109,10 @@
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
(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_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);
", 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++;