using System.ComponentModel;
|
using Kingdee.BOS;
|
using Kingdee.BOS.Core;
|
using Kingdee.BOS.ServiceHelper;
|
using System.Data;
|
|
using Newtonsoft.Json.Linq;
|
using System;
|
using Kingdee.BOS.Util;
|
using System.Linq;
|
using Demo.Model.Model;
|
using Demo.Model.Model.PODemandPlan;
|
using System.Collections.Generic;
|
using ZD.Share.Common;
|
using ZD.Cloud.WebApi;
|
|
namespace Demo.TimedTaskPlugIn.PRD
|
{
|
[Description("领料退料导致库存发生变化时发生变动时执行")]
|
[HotUpdate]
|
public class StockChangeDynamicAlignment : Kingdee.BOS.Contracts.IScheduleService
|
{
|
CloudClient cloudClient = new CloudClient("http://localhost//k3cloud/");
|
public Context Context;
|
List<PRD_Inventory> ListInventory;//库存明细
|
List<PRD_Inventory> ListInventoryByStockOrgId;//物料总库存
|
|
public void Run(Context ctx, Schedule schedule)
|
{
|
try
|
{
|
Context = ctx;
|
string sql = @"/*dialect*/ select count(*) from
|
(
|
select a.FSTOCKORGID,sum(b.FACTUALQTY)FACTUALQTY,b.FMATERIALID from T_PRD_FEEDMTRL A
|
join T_PRD_PICKMTRLDATA b on a.FID =b.FID
|
where a.FAPPROVEDATE is not null and DATEDIFF(D,GETDATE(),FDATE)=0
|
group by FSTOCKORGID,FMATERIALID
|
) a
|
left join
|
(
|
select a.FSTOCKORGID,sum(b.FQTY)FQTY,b.FMATERIALID from T_PRD_RETURNMTRL A
|
join T_PRD_RETURNMTRLENTRY b on a.FID =b.FID
|
where a.FAPPROVEDATE is not null and DATEDIFF(D,GETDATE(),FDATE)=0
|
group by FSTOCKORGID,FMATERIALID
|
)B ON A.FSTOCKORGID=B.FSTOCKORGID AND A.FMATERIALID=B.FMATERIALID
|
WHERE a.FACTUALQTY - ISNULL(B.FQTY,0)>0";
|
int Count = DBServiceHelper.ExecuteScalar<int>(Context, sql, 0);
|
if (Count > 0)
|
{
|
CompleteSetAnalysis();
|
DayPlanPPBomBill();
|
Extraction();
|
}
|
}
|
catch (System.Exception ex)
|
{
|
//LogHelper.Error(ex.Message.ToString());
|
}
|
}
|
|
/// <summary>
|
/// 齐套分析
|
/// </summary>
|
public void CompleteSetAnalysis()
|
{
|
try
|
{
|
//清空齐套临时表
|
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<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-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<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.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,'未齐套') ='未齐套')
|
//");
|
|
//执行完成后 更新日计划工单状态
|
DBServiceHelper.Execute(Context, @"
|
/*dialect*/update Sc_WorkBillSortBillSub set FComplete = '未齐套' where
|
exists (select distinct FHICMOEntryID from JIT_MOMaterReadysBill v where isnull(FCOMPLETE,'未齐套') ='未齐套' and v.FHICMOENTRYID = Sc_WorkBillSortBillSub.FEntryID)
|
");
|
DBServiceHelper.Execute(Context, @"
|
/*dialect*/update Sc_WorkBillSortBillSub set FComplete = '齐套' where
|
not exists
|
(select distinct FHICMOEntryID from JIT_MOMaterReadysBill v where isnull(FCOMPLETE,'未齐套') ='未齐套' and v.FHICMOENTRYID = Sc_WorkBillSortBillSub.FEntryID )
|
");
|
|
}
|
catch (Exception ex)
|
{
|
//LogHelper.Error(ex.Message.ToString());
|
}
|
}
|
|
/// <summary>
|
/// 日计划用料清单生成
|
/// </summary>
|
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> DayPlanMaterial = new List<DayPlanMaterial>();
|
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());
|
}
|
}
|
/// <summary>
|
/// 提料计划
|
/// </summary>
|
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> PurchaseInventory = DBServiceHelper.ExecuteDataSet(Context, sql).ToModelList<PurchaseInventory>();
|
//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> DayPlanPpbom = ds.ToModelList<DayPlanPpbom>();
|
|
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集合 更新采购订单占用的提料计划数量
|
List<string> sqlList = new List<string>();
|
//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;//最小起订量
|
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<int> FEntryIdList = new List<int>();
|
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 = 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<int> mmm = new List<int>();
|
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<int> mmm = new List<int>();
|
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 = 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<int> mmm = new List<int>();
|
// 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<int> mmm = new List<int>();
|
// 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<PODemandPlanTemp> _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);
|
}
|
|
|
}
|
}
|
