//using System.ComponentModel; //using Kingdee.BOS.Contracts; //using Kingdee.BOS; //using Kingdee.BOS.Core; //using Kingdee.BOS.ServiceHelper; //using System.Data; //using ZD.Cloud.Logger; //using Newtonsoft.Json.Linq; //using System; //using Kingdee.BOS.Util; //using System.Linq; //using ZD.Cloud.WebApi; //using Demo.Model.Model; //using Demo.Model.Model.PODemandPlan; //using System.Diagnostics; //using System.Collections.Generic; //using ZD.Share.Common; //namespace Demo.TimedTaskPlugIn.PRD //{ // [Description("日计划发生变动时执行")] // [HotUpdate] // public class DayPlanChangeDynamicAlignment : Kingdee.BOS.Contracts.IScheduleService // { // CloudClient cloudClient = new CloudClient("http://localhost//k3cloud/"); // public Context Context; // List ListInventory;//库存明细 // List ListInventoryByStockOrgId;//物料总库存 // public void Run(Context ctx, Schedule schedule) // { // try // { // Context = ctx; // int Count = DBServiceHelper.ExecuteScalar(Context, "/*dialect*/ select count(*) from SC_WORKBILLSORTBILLSub where ISNULL(FCOMPLETE,'')='' ", 0); // if (Count > 0) // { // CompleteSetAnalysis(); // DayPlanPPBomBill(); // Extraction(); // } // } // catch (System.Exception ex) // { // LogHelper.Error(ex.Message.ToString()); // } // } // /// // /// 齐套分析 // /// // public void CompleteSetAnalysis() // { // try // { // Stopwatch sw = new Stopwatch(); // sw.Start();//开始计时 // //清空齐套临时表 // 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 //--where T1.FMATERIALID in (105773) //"); // ListInventory = ds.ToModelList(); // //总库存 物料+库存组织+库存数量 // 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(); // //需要齐套分析的数据 // string sql = @" ///*dialect*/ //select a.FPRDMOMAINID,a.FPRDMOENTYID,a.FID,b.FEntryID,a.FHPRDORGID FStockOrgId,t5.FNUMBER as OrgFnumber,a.FHOrderLev,b.FHQTY,b.FHMASTERDATE,T1.FBillNo,T1.FMOBillNO,T1.FMOEntrySeq,T1.FMoId,T1.FMaterialID,T1.FQty, //T2.FMATERIALID FMATERIALID2, T4.FNumber,T2.FNeedQty,FNeedQty/FQty dwyl,(FHQTY-ISNULL(FProductNum,0))*(FNeedQty/FQty) PlanCount from SC_WORKBILLSORTBILLMAIN a //join SC_WORKBILLSORTBILLSub b on a.FID = b.FID //join T_PRD_PPBOM T1 on a.FPRDMOMAINID = t1.FMoId and a.FPRDMOENTYID = t1.FMOENTRYID //LEFT JOIN(select FID, MAX(FENTRYID) FENTRYID, SUM(FNeedQty) FNeedQty, FMATERIALID from T_PRD_PPBOMENTRY //group by FMATERIALID, FID ) T2 on T1.FID = t2.FID // --LEFT JOIN T_STK_Inventory T3 on t2.FMATERIALID = t3.FMATERIALID //JOIN T_BD_MATERIAL T4 on T2.FMATERIALID = T4.FMATERIALID //JOIN T_ORG_Organizations t5 on a.FHPRDORGID = t5.FORGID //--条件为今天往后的日计划 + 今天之前未关闭的日计划 //where (FHEntryCloseDate is null and DATEDIFF(D,GETDATE(),FHMASTERDATE)<0)or DATEDIFF(D,GETDATE(),FHMASTERDATE)>=0 //ORDER BY b.FHMASTERDATE,a.FHOrderLev //"; // ds = DBServiceHelper.ExecuteDataSet(Context, sql); // List BomList = ds.ToModelList(); // //日计划+生产订单子表编码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 = new List(); // int i = 1; // //创建字典 用于储存物料 + 占用数量 // Dictionary occupyDic = new Dictionary(); // 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 _ListInventory = ListInventory.Where(c => c.FMATERIALID == _item.FMATERIALID2 && c.FStockOrgId == _item.FStockOrgId && c.FBASEQTY > 0).ToList(); // if (_ListInventory.Count > 0) // { // decimal _fLackCount = _item.PlanCount > sjkcList.FBASEQTY ? (_item.PlanCount - sjkcList.FBASEQTY) : 0;//缺料数量 // foreach (var Inventory in _ListInventory) // { // if (need == 0) // break; // decimal fCompleteCount = need >= Inventory.FBASEQTY ? Inventory.FBASEQTY : need;//齐套数量 // decimal occupyMaterial = 0; // if (!occupyDic.ContainsKey(_item.FMATERIALID2)) // { // occupyDic.Add(_item.FMATERIALID2, fCompleteCount); // } // else // { // occupyMaterial = occupyDic[_item.FMATERIALID2]; // occupyDic[_item.FMATERIALID2] = occupyMaterial + fCompleteCount; // } // completeAnalysisTempModel.Add(new Demo.Model.Model.CompleteAnalysisTempModel // { // FHMainICMOInterIDr = _item.FPRDMOMAINID, // FHMainICMOEntryID = _item.FPRDMOENTYID, // FHICMOInterID = _item.FID.ToString(), // FHICMOEntryID = _item.FEntryID.ToString(), // FHMaterID = _item.FNumber, // FHStockOrgID = _item.OrgFnumber, // FHPRDORGID = _item.OrgFnumber, // FPRDBillNo = _item.FMOBillNO, // FOwnerTypeId = "BD_OwnerOrg", // FOwnerId = Inventory.Fnumber, // FUnitDosage = _item.dwyl.ToString(),//单位用量 // FSumPlanCount = _item.PlanCount.ToString(),//计划总数量 // FCompleteCount = fCompleteCount, //齐套数量 // FLackCount = _fLackCount,//缺料数量 // FHStockQty = sjkcList.FBASEQTY,//即时库存 // FHLeftQty = Inventory.FBASEQTY,//可用数量 // FOccupyCount = occupyMaterial,//占用数量 // 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 // { // decimal occupyMaterial = 0; // if (occupyDic.ContainsKey(_item.FMATERIALID2)) // occupyMaterial = occupyDic[_item.FMATERIALID2]; // completeAnalysisTempModel.Add(new Demo.Model.Model.CompleteAnalysisTempModel // { // FHMainICMOInterIDr = _item.FPRDMOMAINID, // FHMainICMOEntryID = _item.FPRDMOENTYID, // FHICMOInterID = _item.FID.ToString(), // FHICMOEntryID = _item.FEntryID.ToString(), // FHMaterID = _item.FNumber, // FHStockOrgID = _item.OrgFnumber, // FHPRDORGID = _item.OrgFnumber, // FPRDBillNo = _item.FMOBillNO, // FOwnerTypeId = "BD_OwnerOrg", // FUnitDosage = _item.dwyl.ToString(),//单位用量 // FSumPlanCount = _item.PlanCount.ToString(),//计划总数量 // FCompleteCount = 0, //齐套数量 // FLackCount = _item.PlanCount,//缺料数量 // FHStockQty = 0,//即时库存 // FHLeftQty = 0,//可用数量 // FOccupyCount = occupyMaterial,//占用数量 // FComPlete = "未齐套", // FPlanDate = item.FHMASTERDATE // 日计划日期 // }); // } // } // //Thread.Sleep(100); // i++; // } // i = 1; // foreach (var item in completeAnalysisTempModel) // { // // this.View.Session["ProcessRateValue"] = Convert.ToInt32(this.View.Session["ProcessRateValue"]) + Convert.ToInt32(1 / completeAnalysisTempModel.Count * 90); // 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("FOccupyCount", item.FOccupyCount); // jsonModel.Add("FPlanDate", item.FPlanDate); // if (item.FOwnerId != null) // jsonModel.Add("FOwnerId", new JObject() { ["FNUMBER"] = item.FOwnerId }); // jsonRoot.Add("Model", jsonModel); // 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()); // } // i++; // } // //执行完成后 更新日计划工单状态 // DBServiceHelper.Execute(Context, @" ///*dialect*/update Sc_WorkBillSortBillSub set FComplete = '未齐套' where FEntryID IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBill where isnull(FCOMPLETE,'未齐套') ='未齐套') //"); // DBServiceHelper.Execute(Context, @" ///*dialect*/update Sc_WorkBillSortBillSub set FComplete = '齐套' where FEntryID NOT IN (select distinct FHICMOEntryID from JIT_MOMaterReadysBill where isnull(FCOMPLETE,'未齐套') ='未齐套') //"); // sw.Stop();//结束计时 // LogHelper.Info("运行总时长：" + sw.Elapsed); // } // catch (Exception ex) // { // LogHelper.Error(ex.Message.ToString()); // } // } // /// // /// 日计划用料清单生成 // /// // public void DayPlanPPBomBill() // { // //锁定的日计划不生成 // try // { // string sql = string.Format(@" ///*dialect*/ //select t1.FID,t1.FEntryID,t3.FMATERIALID,T5.FNUMBER,t1.FHMASTERDATE,t1.FHQTY,t4.FMATERIALID FMATERIALID2,T6.FNUMBER FNUMBER2,T7.FNUMBER FUNITNUMBER,FNumerator,FDenominator,convert(decimal(18,2),(FNumerator/FDenominator) *t1.FHQTY) FHQtyMust,T4.FUnitID,t8.FNUMBER FUNITNUMBER2,T3.FID FPPBOM,T4.FENTRYID,isnull(T9.FBillNo,'')FBillNo,t10.FNUMBER FORGNUMBER,t11.FNUMBER FHSOURCENUMBER,FSCRAPRATE,FFIXSCRAPQTY,T9.FID AS DayPlanFID //FROM Sc_WorkBillSortBillSub t1 //JOIN Sc_WorkBillSortBillMAIN t2 on t1.FID = t2.FID //JOIN T_PRD_PPBOM T3 on T2.FPRDMOMAINID=t3.FMOID AND T2.FPRDMOENTYID=T3.FMOENTRYID //JOIN T_PRD_PPBOMENTRY T4 ON T3.FID=T4.FID //JOIN T_BD_MATERIAL T5 ON T3.FMATERIALID=T5.FMATERIALID //JOIN T_BD_MATERIAL T6 ON T4.FMATERIALID=T6.FMATERIALID //LEFT JOIN T_BD_UNIT T7 ON T3.FUNITID = T7.FUNITID //LEFT JOIN T_BD_UNIT T8 ON T4.FUNITID = T8.FUNITID // LEFT JOIN Sc_DayPlanPPBomBillMain T9 ON T1.FEntryID=T9.FDayPlanFID // LEFT join T_ORG_Organizations t10 on t2.FHPRDORGID = t10.FORGID // LEFT join T_ENG_WORKCENTER t11 on t2.FHSOURCEID = t11.FID //" //); // DataSet ds = DBServiceHelper.ExecuteDataSet(Context, sql); // DataTable dt = ds.Tables[0]; // List DayPlanMaterial = new List(); // if (dt.Rows.Count > 0) // { // foreach (DataRow dr in dt.Rows) // { // DayPlanMaterial.Add(new Demo.Model.Model.DayPlanMaterial // { // FID = dr["DayPlanFID"].ToString(), // FEntryID = dr["FEntryID"].ToString(), // FHDate = DateTime.Now.ToString(), // FOrgId = dr["FORGNUMBER"].ToString(), // FHMaterID = dr["FNUMBER"].ToString(), // FHWorkDate = dr["FHMASTERDATE"].ToString(), // FHQty = dr["FHQTY"].ToString(), // FHUnitID = dr["FUNITNUMBER"].ToString(), // FDayPlanFID = dr["FEntryID"].ToString(), // FHMaterSubID = dr["FNUMBER2"].ToString(), // FHUnitSubID = dr["FUNITNUMBER2"].ToString(), // FHQtyMust = dr["FHQtyMust"].ToString(), // FHQtySub = dr["FHQtyMust"].ToString(), // FHPPBomInterID = dr["FPPBOM"].ToString(), // FHPPBomEntryID = dr["FENTRYID"].ToString(), // FHSourceID = dr["FHSOURCENUMBER"].ToString(), // FNumerator = dr["FNumerator"].ToString(), // FDenominator = dr["FDenominator"].ToString(), // FFIXSCRAPQTY = dr["FFIXSCRAPQTY"].ToString(), // FHQtyScrap = dr["FSCRAPRATE"].ToString(), // }); // } // var fidList = DayPlanMaterial.Select(x => x.FEntryID).Distinct().ToList(); // foreach (var item in fidList) // { // var _DayPlanMaterial = DayPlanMaterial.Where(x => x.FEntryID == item).ToList(); // JObject model = new JObject(); // model.Add("FHDate", DateTime.Now); // model.Add("FOrgId", new JObject() { ["Fnumber"] = _DayPlanMaterial[0].FOrgId });//发料组织 // model.Add("FHMaterID", new JObject() { ["Fnumber"] = _DayPlanMaterial[0].FHMaterID });//物料 // model.Add("FHWorkDate", _DayPlanMaterial[0].FHWorkDate);//日工单日期 FHWorkDate // model.Add("FHQty", _DayPlanMaterial[0].FHQty);//主产品数量：FHQty // model.Add("FHUnitID", new JObject() { ["Fnumber"] = _DayPlanMaterial[0].FHUnitID });//单位：FHUnitID // model.Add("FDayPlanFID", _DayPlanMaterial[0].FDayPlanFID);//日计划工单内码 // JArray Entry = new JArray(); // foreach (var _item in _DayPlanMaterial) // { // JObject ModelEnty = new JObject(); // ModelEnty.Add("FHMaterSubID", new JObject() { ["Fnumber"] = _item.FHMaterSubID });//子物料FHMaterSubID // ModelEnty.Add("FHUnitSubID", new JObject() { ["Fnumber"] = _item.FHUnitSubID });//单位FHUnitSubID // ModelEnty.Add("FHQtyMust", _item.FHQtyMust);//应发数量FHQtyMust // ModelEnty.Add("FHQtySub", _item.FHQtySub);//数量FHQtySub // ModelEnty.Add("FHPPBomInterID", _item.FHPPBomInterID);//用料清单主内码FHPPBomInterID // ModelEnty.Add("FHPPBomEntryID", _item.FHPPBomEntryID);//用料清单子内码FHPPBomEntryID // ModelEnty.Add("FHSourceID", new JObject() { ["Fnumber"] = _item.FHSourceID });//生产资源FHSourceID // ModelEnty.Add("FNumerator", _item.FNumerator);//分母 // ModelEnty.Add("FDenominator", _item.FDenominator);//分子 // ModelEnty.Add("FDwyl", Convert.ToDecimal(_item.FNumerator) / Convert.ToDecimal(_item.FDenominator));//单位用料 // ModelEnty.Add("FFIXSCRAPQTY", _item.FFIXSCRAPQTY);//固定损耗 // ModelEnty.Add("FHQtyScrap", _item.FHQtyScrap);//变动损耗率% // Entry.Add(ModelEnty); // } // model.Add("FEntity", Entry); // if (!string.IsNullOrEmpty(_DayPlanMaterial[0].FID)) // model.Add("FID", _DayPlanMaterial[0].FID); // JObject jsonRoot = new JObject() // { // ["Creator"] = "", // ["NeedUpDateFields"] = new JArray(), // ["NeedReturnFields"] = new JArray(), // ["IsDeleteEntry"] = "true", // ["SubSystemId"] = "", // ["IsVerifyBaseDataField"] = "false", // ["Model"] = model // }; // var result = cloudClient.Save("Paez_Sc_DayPlanPPBomBill", jsonRoot.ToString()); // JObject saveObj = JObject.Parse(result); // string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper(); // if (saveIsSuc != "TRUE") // LogHelper.Error(saveIsSuc); // } // } // } // catch (Exception ex) // { // LogHelper.Error(ex.Message.ToString()); // } // } // /// // /// 提料计划 // /// // public void Extraction() // { // string sql = string.Format(@"/*dialect*/ select t1.FID from Sc_WorkBillSortBillMain t1"); // DataTable fidDt = DBServiceHelper.ExecuteDataSet(Context, sql).Tables[0]; // string FDayPlanWorkID = ""; // foreach (DataRow dr in fidDt.Rows) // { // FDayPlanWorkID += Convert.ToString(dr["FID"]) + ","; // } // FDayPlanWorkID = FDayPlanWorkID.Substring(0, FDayPlanWorkID.Length - 1); // //提料计划预处理 清理提料计划单数据 更新采购订单提料计划数量 // sql = string.Format(@" // /*dialect*/ // exec [提料计划预处理] '{0}' // ", FDayPlanWorkID.Replace(",", "-")); // DBServiceHelper.Execute(Context, sql); // //采购订单数据 // sql = @" // /*dialect*/ //select t1.FID,t1.FBillNo,t2.FENTRYID,t1.FSUPPLIERID,t3.fnumber,t2.FMATERIALID,(t2.FQTY-t2.FPODemandPlanCount)FQTY,FPurchaseOrgId FStockOrgId,t5.FNUMBER FORGNumber,FSTOCKINQTY,FReceiveQty,FCloseStatus,FMRPCLOSESTATUS from t_PUR_POOrder t1 //join t_PUR_POOrderEntry t2 on t1.FID = t2.FID //join t_BD_Supplier t3 on t1.FSUPPLIERID = t3.FSUPPLIERID //join T_PUR_POORDERENTRY_R t4 on t2.FENTRYID = t4.FENTRYID //join T_ORG_Organizations t5 on t1.FPurchaseOrgId = t5.FORGID //where t2.FQTY-t2.FPODemandPlanCount>0 // and FCloseStatus in('A') and FMRPCLOSESTATUS in('A') //"; // List PurchaseInventory = DBServiceHelper.ExecuteDataSet(Context, sql).ToModelList(); // LogHelper.Info("采购订单数据" + sql); // sql = string.Format(@" // /*dialect*/ // SELECT T1.FID,FHMATERID,FHMASTERDATE,FHQTY,FCOMPLETECOUNT,FPRDMOMAINID,T2.FENTRYID,T1.FBILLNO, FNeedQty,(FLeadtime+FLeadTtime2)FFIXLEADTIME,t5.FMAXPOQTY,FJITBatch FMINPOQTY,FJITMATERIALGROUP,FJITMaterielDemand,FJITSafeStock,t3.FMATERIALID,t6.FNumber,FHQTY*FNeedQty NeedQty,FHPRDORGID FStockOrgId FROM SC_WORKBILLSORTBILLMAIN T1 //JOIN SC_WORKBILLSORTBILLSUB T2 ON T1.FID=T2.FID //LEFT JOIN(SELECT T1.FID,max(t2.FMATERIALID)FMATERIALIDMAIN, max(t2.FMoId)FMoId,MAX(FENTRYID) FENTRYID, SUM(convert(decimal(18, 2), FBASENUMERATOR / FBASEDENOMINATOR))FNeedQty, T1.FMATERIALID from T_PRD_PPBOMENTRY T1 //JOIN T_PRD_PPBOM T2 on t1.FID = t2.FID //group by T1.FMATERIALID, T1.FID) T3 on T1.FPRDMOMAINID = t3.FMoId and T1.FHMATERID = t3.FMATERIALIDMAIN //JOIN T_BD_MATERIAL T6 ON T3.FMATERIALID = T6.FMATERIALID //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 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 = ds.ToModelList(); // 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 = new List(); // //sql集合 更新采购订单占用的提料计划数量 // List sqlList = new List(); // //MaterialIDList = new List { "105773" }; // foreach (var item in MaterialIDList) // { // LogHelper.Info(item.FMATERIALID.ToString()); // List _DayPlanPpbom = DayPlanPpbom.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId).ToList(); // decimal FMINPOQTY = _DayPlanPpbom.FirstOrDefault().FMINPOQTY;//最小起订量 // double FFIXLEADTIME = _DayPlanPpbom.FirstOrDefault().FFIXLEADTIME;//提前期 // string FJITmaterialGroup = _DayPlanPpbom.FirstOrDefault().FJITmaterialGroup;//jit物料分类 // string FJITMaterielDemand = _DayPlanPpbom.FirstOrDefault().FJITMaterielDemand; //JIT物料需求供货周期 // string FJITSafeStock = _DayPlanPpbom.FirstOrDefault().FJITSafeStock; //JIT安全库存 // decimal NeedQty = 0; // DateTime DATE = _DayPlanPpbom.FirstOrDefault().FHMASTERDATE; // int i = 1; // //记录在最小采购量需求下的日计划明细FentyrID // List FEntryIdList = new List(); // foreach (var _item in _DayPlanPpbom) // { // if (NeedQty == 0) // DATE = Convert.ToDateTime(_item.FHMASTERDATE).AddDays(-FFIXLEADTIME); // FEntryIdList.Add(_item.FENTRYID); // NeedQty += _item.NeedQty; // if ((FJITmaterialGroup == "总量控制规格类" || FJITmaterialGroup == "订单专用个性类") && FEntryIdList.Count < Convert.ToInt32(FJITMaterielDemand)) //订单专用个性类 // continue; // if (NeedQty >= FMINPOQTY) // { // List _PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).ToList(); // if (_PurchaseInventory.Count == 0) // { // NeedQty = 0; // break; // } // foreach (var Purchase in _PurchaseInventory) // { // LogHelper.Info("Purchase"); // LogHelper.Info("物料：" + Purchase.FMATERIALID + ",数量：" + Purchase.FQTY); // if (Purchase.FQTY >= NeedQty) // { // List mmm = new List(); // foreach (int id in FEntryIdList) // { // mmm.Add(id); // var DayPlanPpbomls = DayPlanPpbom.Where(c => c.FENTRYID == id && c.FMATERIALID == _item.FMATERIALID).FirstOrDefault(); // PODemandPlanTemp.Add(new Demo.Model.Model.PODemandPlan.PODemandPlanTemp // { // FID = DayPlanPpbomls.FID, // FHMASTERDATE = DATE, // FQty = DayPlanPpbomls.NeedQty, // FBILLNO = DayPlanPpbomls.FBILLNO, // FENTRYID = DayPlanPpbomls.FENTRYID, // FMATERIALID = DayPlanPpbomls.FMATERIALID, // FNumber = DayPlanPpbomls.FNumber, // OLDDATE = DayPlanPpbomls.FHMASTERDATE, // FFIXLEADTIME = FFIXLEADTIME, // PurchseFID = Purchase.FID, // PurchseFentryID = Purchase.FENTRYID, // FSUPPLIERID = Purchase.FSUPPLIERID, // PurchseFNUMBER = Purchase.FNUMBER, // PurchseFBillNo = Purchase.FBillNo, // PurchseFqty = Purchase.FQTY, // FStockOrgId = Purchase.FStockOrgId, //采购组织 // FORGNumber = Purchase.FORGNumber // }); // //扣除日计划明细已被分配的数量 // DayPlanPpbomls.NeedQty = 0; // } // FEntryIdList.Clear(); // //更新采购订单 // sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount =isnull(FPODemandPlanCount,0)+ '{NeedQty}' where FENTRYID = {Purchase.FENTRYID}"); // //扣除当前行对应采购订单数量 // var ls = PurchaseInventory.Where(c => c.FENTRYID == Purchase.FENTRYID).FirstOrDefault(); // ls.FQTY -= NeedQty; // break; // } // else // { // NeedQty = NeedQty - Purchase.FQTY; // decimal _NeedQty = Purchase.FQTY;//采购订单数量 // List mmm = new List(); // foreach (int id in FEntryIdList) // { // var DayPlanPpbomls = DayPlanPpbom.Where(c => c.FENTRYID == id && c.FMATERIALID == _item.FMATERIALID).FirstOrDefault(); // decimal Qty = DayPlanPpbomls.NeedQty; // if (Qty > _NeedQty) // Qty = _NeedQty; // _NeedQty = _NeedQty - DayPlanPpbomls.NeedQty; // PODemandPlanTemp.Add(new Demo.Model.Model.PODemandPlan.PODemandPlanTemp // { // FID = DayPlanPpbomls.FID, // FHMASTERDATE = DATE, // FQty = Qty, // FBILLNO = DayPlanPpbomls.FBILLNO, // FENTRYID = DayPlanPpbomls.FENTRYID, // FMATERIALID = DayPlanPpbomls.FMATERIALID, // FNumber = DayPlanPpbomls.FNumber, // OLDDATE = DayPlanPpbomls.FHMASTERDATE, // FFIXLEADTIME = FFIXLEADTIME, // PurchseFID = Purchase.FID, // PurchseFentryID = Purchase.FENTRYID, // FSUPPLIERID = Purchase.FSUPPLIERID, // PurchseFNUMBER = Purchase.FNUMBER, // PurchseFBillNo = Purchase.FBillNo, // PurchseFqty = Purchase.FQTY, // FStockOrgId = Purchase.FStockOrgId, //采购组织 // FORGNumber = Purchase.FORGNumber // }); // DayPlanPpbomls.NeedQty -= Qty; // //更新采购订单 // sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount = isnull(FPODemandPlanCount,0)+ '{Qty}' where FENTRYID = {Purchase.FENTRYID}"); // //更新Model // var ls = PurchaseInventory.Where(c => c.FENTRYID == Purchase.FENTRYID).FirstOrDefault(); // ls.FQTY -= Qty; // if (_NeedQty < 0) // { // foreach (var mm in mmm) // { // FEntryIdList.Remove(mm); // } // break; // } // mmm.Add(id); // } // foreach (var mm in mmm) // { // FEntryIdList.Remove(mm); // } // } // } // NeedQty = 0; // } // #region 剩下不足时按最小采购批次生成 // //剩下不足时按最小采购批次生成 // //else if (i == _DayPlanPpbom.Count && NeedQty > 0) // //{ // // NeedQty = FMINPOQTY; // // List _PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).ToList(); // // if (_PurchaseInventory.Count == 0) // // { // // NeedQty = 0; // // break; // // } // // foreach (var Purchase in _PurchaseInventory) // // { // // if (Purchase.FQTY >= NeedQty) // // { // // List mmm = new List(); // // foreach (int id in FEntryIdList) // // { // // mmm.Add(id); // // var DayPlanPpbomls = DayPlanPpbom.Where(c => c.FENTRYID == id && c.FMATERIALID == _item.FMATERIALID).FirstOrDefault(); // // PODemandPlanTemp.Add(new Demo.Model.Model.PODemandPlan.PODemandPlanTemp // // { // // FID = DayPlanPpbomls.FID, // // FHMASTERDATE = DATE, // // FQty = DayPlanPpbomls.NeedQty, // // FBILLNO = DayPlanPpbomls.FBILLNO, // // FENTRYID = DayPlanPpbomls.FENTRYID, // // FMATERIALID = DayPlanPpbomls.FMATERIALID, // // FNumber = DayPlanPpbomls.FNumber, // // OLDDATE = DayPlanPpbomls.FHMASTERDATE, // // FFIXLEADTIME = FFIXLEADTIME, // // PurchseFID = Purchase.FID, // // PurchseFentryID = Purchase.FENTRYID, // // FSUPPLIERID = Purchase.FSUPPLIERID, // // PurchseFNUMBER = Purchase.FNUMBER, // // PurchseFBillNo = Purchase.FBillNo, // // PurchseFqty = Purchase.FQTY // // }); // // //扣除日计划明细已被分配的数量 // // DayPlanPpbomls.NeedQty = 0; // // } // // FEntryIdList.Clear(); // // //更新采购订单 // // sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount =isnull(FPODemandPlanCount,0)+ '{NeedQty}' where FENTRYID = {Purchase.FENTRYID}"); // // //扣除当前行对应采购订单数量 // // var ls = PurchaseInventory.Where(c => c.FENTRYID == Purchase.FENTRYID).FirstOrDefault(); // // ls.FQTY -= NeedQty; // // break; // // } // // else // // { // // NeedQty = NeedQty - Purchase.FQTY; // // decimal _NeedQty = Purchase.FQTY;//采购订单数量 // // List mmm = new List(); // // foreach (int id in FEntryIdList) // // { // // var DayPlanPpbomls = DayPlanPpbom.Where(c => c.FENTRYID == id && c.FMATERIALID == _item.FMATERIALID).FirstOrDefault(); // // decimal Qty = DayPlanPpbomls.NeedQty; // // if (Qty > _NeedQty) // // Qty = _NeedQty; // // _NeedQty = _NeedQty - DayPlanPpbomls.NeedQty; // // PODemandPlanTemp.Add(new Demo.Model.Model.PODemandPlan.PODemandPlanTemp // // { // // FID = DayPlanPpbomls.FID, // // FHMASTERDATE = DATE, // // FQty = Qty, // // FBILLNO = DayPlanPpbomls.FBILLNO, // // FENTRYID = DayPlanPpbomls.FENTRYID, // // FMATERIALID = DayPlanPpbomls.FMATERIALID, // // FNumber = DayPlanPpbomls.FNumber, // // OLDDATE = DayPlanPpbomls.FHMASTERDATE, // // FFIXLEADTIME = FFIXLEADTIME, // // PurchseFID = Purchase.FID, // // PurchseFentryID = Purchase.FENTRYID, // // FSUPPLIERID = Purchase.FSUPPLIERID, // // PurchseFNUMBER = Purchase.FNUMBER, // // PurchseFBillNo = Purchase.FBillNo, // // PurchseFqty = Purchase.FQTY // // }); // // DayPlanPpbomls.NeedQty -= Qty; // // //更新采购订单 // // sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount = isnull(FPODemandPlanCount,0)+ '{Qty}' where FENTRYID = {Purchase.FENTRYID}"); // // //更新Model // // var ls = PurchaseInventory.Where(c => c.FENTRYID == Purchase.FENTRYID).FirstOrDefault(); // // ls.FQTY -= Qty; // // if (_NeedQty < 0) // // { // // foreach (var mm in mmm) // // { // // FEntryIdList.Remove(mm); // // } // // break; // // } // // mmm.Add(id); // // } // // foreach (var mm in mmm) // // { // // FEntryIdList.Remove(mm); // // } // // } // // } // // NeedQty = 0; // //} // #endregion // i++; // } // } // var PODemandPlanList222 = PODemandPlanTemp.GroupBy(p => new PODemandPlanTemp { PurchseFNUMBER = p.PurchseFNUMBER, FHMASTERDATE = p.FHMASTERDATE }).Select(x => new PODemandPlanTemp { PurchseFNUMBER = x.Key.PurchseFNUMBER, FHMASTERDATE = x.Key.FHMASTERDATE }).ToList(); // var PODemandPlanList = PODemandPlanTemp.GroupBy(p => new { p.PurchseFNUMBER, p.FHMASTERDATE }).Select(x => new PODemandPlanTemp { PurchseFNUMBER = x.Key.PurchseFNUMBER, FHMASTERDATE = x.Key.FHMASTERDATE }).ToList(); // foreach (var item in PODemandPlanList) // { // DateTime date = item.FHMASTERDATE; // string PurchseFNUMBER = item.PurchseFNUMBER; // JObject model = new JObject(); // model.Add("FHDate", date); // model.Add("FHRemark", "生产订单号：" + "测试呢呢"); // model.Add("FSupplierID", new JObject() { ["Fnumber"] = PurchseFNUMBER }); // JArray Fentity = new JArray(); // List _PODemandPlanList = PODemandPlanTemp.Where(x => x.FHMASTERDATE == date && x.PurchseFNUMBER == PurchseFNUMBER).ToList(); // foreach (var _item in _PODemandPlanList) // { // JObject FentityModel = new JObject(); // FentityModel.Add("FHPURCHASEORGID", new JObject() { ["Fnumber"] = _item.FORGNumber });//采购组织 // FentityModel.Add("FHMaterID", new JObject() { ["Fnumber"] = _item.FNumber });//物料 // FentityModel.Add("FHQty", _item.FQty);//数量 // FentityModel.Add("FHSourceInterID", _item.FID);//日计划工单FID // FentityModel.Add("FHSourceEntryID", _item.FENTRYID);//日计划工单FENTRYID // FentityModel.Add("FHSourceBillNo", _item.FBILLNO);//日计划工单单号 // FentityModel.Add("FHSourceBillType", "");//日计划工单类型 // FentityModel.Add("FDayPlanDate", _item.OLDDATE);//日计划工单类型 // FentityModel.Add("FHRelationQty", _item.PurchseFqty);//关联数量 // FentityModel.Add("FFIXLEADTIME", _item.FFIXLEADTIME);//提前期 // FentityModel.Add("FPURCHASEORGID", new JObject() { ["Fnumber"] = _item.FORGNumber });//采购组织 // FentityModel.Add("FHPOOrderInterID", _item.PurchseFID);// 采购订单内码：FHPOOrderInterID // FentityModel.Add("FHPOOrderEntryID", _item.PurchseFentryID); //采购订单子内码：FHPOOrderEntryID // FentityModel.Add("FHPOOrderBillNo", _item.PurchseFBillNo); //采购订单号：FHPOOrderBillNo // 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 // }; // var result = cloudClient.Save("paez_PODemandPlan", jsonRoot.ToString()); // JObject saveObj = JObject.Parse(result); // string saveIsSuc = saveObj["Result"]["ResponseStatus"]["IsSuccess"].ToString().ToUpper(); // LogHelper.Info("最后"); // LogHelper.Info(jsonRoot.ToString()); // if (saveIsSuc != "TRUE") // LogHelper.Error(saveIsSuc); // } // DBServiceHelper.ExecuteBatch(Context, sqlList); // } // } //}