publicvoidGetcost(Contextcontext,stringstr){Listlist=newList();//算法二更新委外bom成本误差问题if(true){stringsql=$@"/*dialect*/withcteas(select0asBOM层次,t1.fidas最顶级BOM内码,t1.FNUMBERasBOM版本,fxwl.FNUMBERas父项物料代码,fxwl_L.FNAMEas父项物料名称,t3.FSEQas分录行号,t3.FREPLACEGROUPas项次,CAST(10000+t3.FREPLACEGROUPASnvarchar)as项次组合,cast(CAST(t1.fidASnvarchar)+'-'+CAST(10000+t3.FREPLACEGROUPASnvarchar)asnvarchar(max))asBOM内码和项次组合,t3.FMATERIALIDas子项物料内码,zxwl.FNUMBERas子项物料代码,zxwl_L.FNAMEas子项物料名称,casewhenFMATERIALTYPE=1then'标准件'whenFMATERIALTYPE=2then'返还件'whenFMATERIALTYPE=3then'替代件'else'未知类型'endas子项类型,t3.FNUMERATORas分子,t3.FDENOMINATORas分母,t3.FFIXSCRAPQTYas固定损耗,t3.FSCRAPRATEas变动损耗,t3.FBOMID,t1.FUSEORGIDfromdbo.T_ENG_BOMt1joinT_BD_MATERIALfxwl--用父项关联物料表onfxwl.FMATERIALID=t1.FMATERIALID--andt1.FFORBIDSTATUS='A'--只取未禁用状态的BOMjoinT_BD_MATERIAL_Lfxwl_L--用父项关联物料多语言表onfxwl.FMATERIALID=fxwl_l.FMATERIALIDandfxwl_L.FLOCALEID=2052joinT_BD_MATERIALPRODUCEfxwl_Ponfxwl_P.FMATERIALID=fxwl.FMATERIALIDjoinT_ENG_BOMCHILDt3ont1.fid=t3.FIDjoinT_BD_MATERIALzxwl--用子项关联物料表onzxwl.FMATERIALID=t3.FMATERIALIDjoinT_BD_MATERIAL_Lzxwl_L--用子项关联物料多语言表onzxwl.FMATERIALID=zxwl_L.FMATERIALIDandzxwl_L.FLOCALEID=2052where1=1andt1.FDOCUMENTSTATUS='C'andt1.FNUMBERin('{str}')andfxwl_P.FISMAINPRD=1--物料-生产页签的'可为主产品'属性FISMAINPRD,等于1就意味着可以建立BOMunionallselectp.BOM层次+1asBOM层次,P.最顶级BOM内码as最顶级BOM内码,t1.FNUMBERasBOM版本,fxwl.FNUMBERas父项物料代码,fxwl_L.FNAMEas父项物料名称,t3.FSEQas分录行号,t3.FREPLACEGROUPas项次,cast(p.项次组合+'.'+CAST(10000+t3.FREPLACEGROUPASnvarchar)asnvarchar)as项次组合,cast(p.BOM内码和项次组合+'.'+(CAST(t1.FIDASnvarchar)+'-'+CAST(10000+t3.FREPLACEGROUPASnvarchar))asnvarchar(max))asBOM内码组合,t3.FMATERIALIDas子项物料内码,zxwl.FNUMBERas子项物料代码,zxwl_L.FNAMEas子项物料名称,casewhenFMATERIALTYPE=1then'标准件'whenFMATERIALTYPE=2then'返还件'whenFMATERIALTYPE=3then'替代件'else'未知类型'endas子项类型,t3.FNUMERATORas分子,t3.FDENOMINATORas分母,t3.FFIXSCRAPQTYas固定损耗,t3.FSCRAPRATEas变动损耗,t3.FBOMID,t1.FUSEORGIDfromcteP--调用递归CTE本身joindbo.T_ENG_BOMt1ont1.FMATERIALID=p.子项物料内码joinT_BD_MATERIALfxwl--父项关联物料表onfxwl.FMATERIALID=t1.FMATERIALID--andt1.FFORBIDSTATUS='A'joinT_BD_MATERIAL_Lfxwl_L--父项关联物料多语言表onfxwl.FMATERIALID=fxwl_l.FMATERIALIDandfxwl_L.FLOCALEID=2052joinT_ENG_BOMCHILDt3ont1.fid=t3.FIDjoinT_BD_MATERIALzxwl--子项关联物料表onzxwl.FMATERIALID=t3.FMATERIALIDjoinT_BD_MATERIAL_Lzxwl_L--子项关联物料多语言表onzxwl.FMATERIALID=zxwl_L.FMATERIALIDandzxwl_L.FLOCALEID=2052),cte2_ZuiXinZiXiangBomas--这个cte2是用来取非0层的子项BOM的最新BOM版本的,然后和0层的父项信息union在一起(selectt1.BOM层次asBOM层级,t1.最顶级BOM内码,t1.BOM版本,t1.父项物料代码as物料代码,t1.父项物料名称as物料名称,0as分录行号,0as项次,t1.项次组合as项次组合,BOM内码和项次组合,0as子项物料内码,''as子项物料代码,''as子项物料名称,'最顶层父项'as子项类型,0as分子,0as分母,0as固定损耗,0as变动损耗,0asBOM内码,t1.FUSEORGID,dense_rank()over(partitionbyt1.最顶级BOM内码,t1.父项物料代码orderbyt1.BOM版本desc)asBOM版本号分区fromctet1where1=1andt1.BOM层次=0andt1.项次组合='10001'--这里是只显示0层的产品unionselectt1.BOM层次+1asBOM层级,t1.最顶级BOM内码,t1.BOM版本,t1.子项物料代码as物料代码,t1.子项物料名称as物料名称,t1.分录行号as分录行号,t1.项次as项次,t1.项次组合as项次组合,BOM内码和项次组合,0as子项物料内码,t1.子项物料代码as子项物料代码,''as子项物料名称,t1.子项类型as子项类型,t1.分子as分子,t1.分母as分母,t1.固定损耗as固定损耗,t1.变动损耗as变动损耗,t1.FBOMIDasBOM内码,t1.FUSEORGID,dense_rank()over(partitionbyt1.最顶级BOM内码,t1.父项物料代码orderbyt1.BOM层次+1,t1.BOM版本desc)asBOM版本号分区--通过这个字段标识最新版本的BOM，按照父项物料分区之后，把BOM版本降序排列，BOM版本高的排序序号就是1fromctet1where1=1)selectLEFT(t2.BOM版本,CHARINDEX('_',t2.BOM版本)-1)父级BOM,T3.FMATERIALID,*fromcte2_ZuiXinZiXiangBomt2leftjoinT_BD_MATERIALt3ont2.物料代码=t3.FNUMBERandT3.FUSEORGID=1andT3.F_ABCD_COMBO='否'JOIN(selectA.FMATERIALID,casewhenB.FMATERIALIDisnotnullthenB.FTAXPRICEelseA.FTAXPRICEENDFTAXPRICEfrom(selectrow_number()over(partitionbyFMATERIALIDorderbyFTAXPRICEdesc)row,FMATERIALID,FTAXPRICEfromt_PUR_PriceListEntryALEFTJOINt_PUR_PriceListBONA.FID=B.FID)aleftjoin(select*from(selectrow_number()over(partitionbyFMATERIALIDorderbyFTAXPRICEdesc)row,FMATERIALID,FTAXPRICEfromt_PUR_PriceListEntryALEFTJOINt_PUR_PriceListBONA.FID=B.FIDwhereA.FEFFECTIVEDATE<=GETDATE()andA.FEXPIRYDATE>=GETDATE()andFForbidStatus='A'andFDisableStatus='B'andFPRICETYPE=3--FPRICETYPE=3变动时间)AWHERErow=1)bona.FMATERIALID=B.FMATERIALIDwhereA.row=1)BONB.FMATERIALID=T3.FMATERIALIDwhere1=1andt2.BOM版本号分区=1and((t2.BOM层级=0andt2.项次组合='10001')or(t2.BOM层级>0))andt2.FUSEORGID=1--orderbyt2.BOM内码和项次组合andt2.分母>0orderbyBOM层级desc";vardata2=DBUtils.ExecuteDynamicObject(context,sql);//vardata2=DBUtils.ExecuteDynamicObject(context,$@"execProc_Getcost3{str}");Listmodels=newList();foreach(varitemindata2){modelmo=newmodel();mo.最顶级BOM内码=Convert.ToInt32(item["最顶级BOM内码"]);mo.父级BOM=item["父级BOM"].ToString();mo.BOM层级=item["BOM层级"].ToString();mo.项次组合=item["项次组合"].ToString();mo.物料代码=item["物料代码"].ToString();mo.分子=Convert.ToDecimal(item["分子"]);mo.分母=Convert.ToDecimal(item["分母"]);mo.FTAXPRICE=Convert.ToDecimal(item["FTAXPRICE"]);models.Add(mo);}varMASTERLIST=data2.Select(t=>t["最顶级BOM内码"]).Distinct().ToList();ListkeyValuePairs=newList();foreach(intkeyValueinMASTERLIST){foreach(varitemindata2){if(keyValue==Convert.ToInt32(item["最顶级BOM内码"])){vara=models.Where(t=>t.最顶级BOM内码==keyValue).ToList();varmolecule=bom(models.Where(t=>t.最顶级BOM内码==keyValue).ToList(),item["物料代码"].ToString(),1);//分子默认1Convert.ToDecimal(item["分子"])modelmodel=newmodel();model.最顶级BOM内码=Convert.ToInt32(item["最顶级BOM内码"]);model.父级BOM=item["父级BOM"].ToString();model.BOM层级=item["BOM层级"].ToString();model.项次组合=item["项次组合"].ToString();model.物料代码=item["物料代码"].ToString();model.分子=molecule;model.分母=Convert.ToDecimal(item["分母"]);model.FTAXPRICE=Convert.ToDecimal(item["FTAXPRICE"]);keyValuePairs.Add(model);}}}varresult=keyValuePairs.GroupBy(t=>t.最顶级BOM内码).Where(g=>g.Min(t=>t.FTAXPRICE)!=0).Select(g=>new{A=g.Key,SumB=g.Sum(t=>t.FTAXPRICE*t.分子/t.分母)}).ToList();foreach(variteminresult){list.Add($@"/*dialect*/updateT_ENG_BOMsetF_COSTPRICE={item.SumB},F_COSTDATE=GETDATE()wherefid={item.A}");list.Add($@"/*dialect*/UPDATEBSETB.FlistPrice='{item.SumB}'fromT_ENG_BOMALEFTJOINT_BD_MATERIALBONA.FMATERIALID=B.FMATERIALIDWHEREA.FID={item.A}");}DBUtils.ExecuteBatch(context,list,500);}}
