《物料清单汇总查询》二开增加自定义字段
业务需求
增加文本，显示物料清单的替代编码。
说明
BomQueryIntegration继承了BomQueryForward。
具体步骤
1、新建cs类BomQueryIntegrationExtend，继承BomQueryIntegration，重写获取子项信息GetBomChildData。
protected override List<DynamicObject> GetBomChildData(List<DynamicObject> lstExpandSource, MemBomExpandOption_ForPSV memBomExpandOption)
{
var bomQueryChildItems1=base.GetBomChildData(lstExpandSource, memBomExpandOption);
if (bomQueryChildItems1 != null && bomQueryChildItems1.Count > 0)
{
long bomId = 0;
long orgId = 0;
//获取顶层bom
var bom = this.View.Model.GetValue("FBillBomId") as DynamicObject;//BOM版本
if (bom != null)
{
bomId = Convert.ToInt64(bom["Id"]);
}
//var mater = this.View.Model.GetValue("FBillMaterialId") as DynamicObject;//物料编码
var org = this.View.Model.GetValue("FBomUseOrgId") as DynamicObject;//使用组织
if (org != null)
{
orgId = Convert.ToInt64(org["Id"]);
}
#region
string _getSql = string.Format(@"{0}with cte as
(
--1、定点（Anchor）子查询，用来查询最顶级的产品的BOM的
select
0 as BOM层次,t1.fid as 最顶级BOM内码
,t1.FNUMBER as BOM版本,fxwl.FNUMBER as 父项物料代码,fxwl_L.FNAME as 父项物料名称,t3.FSEQ as 分录行号
,t3.FREPLACEGROUP as 项次,CAST(10000+t3.FREPLACEGROUP AS nvarchar) as 项次组合
,cast(CAST(t1.fid AS nvarchar)+'-'+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar(max)) as BOM内码和项次组合
,t3.FMATERIALID as 子项物料内码,zxwl.FNUMBER as 子项物料代码,zxwl_L.FNAME as 子项物料名称
,t3.FMATERIALTYPE
,t3.FBOMID,t1.FUSEORGID
,0 as 是否有子项BOM版本,t3.FREPNUMBER
from dbo.T_ENG_BOM t1
join T_BD_MATERIAL fxwl --用父项关联物料表
on fxwl.FMATERIALID = t1.FMATERIALID
and t1.FFORBIDSTATUS = 'A' --只取未禁用状态的BOM
join T_BD_MATERIAL_L fxwl_L --用父项关联物料多语言表
on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052
join T_BD_MATERIALPRODUCE fxwl_P
on fxwl_P.FMATERIALID = fxwl.FMATERIALID
join T_ENG_BOMCHILD t3
on t1.fid = t3.FID
join T_BD_MATERIAL zxwl --用子项关联物料表
on zxwl.FMATERIALID = t3.FMATERIALID
join T_BD_MATERIAL_L zxwl_L --用子项关联物料多语言表
on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052
where 1=1
and fxwl_P.FISMAINPRD = 1 --物料-生产页签的'可为主产品'属性FISMAINPRD,等于1就意味着可以建立BOM
AND t1.FID={1} --750171--799267
--and t1.FNUMBER in ('1.01.003_V1.0') --这里可以输入一个产品BOM版本,则只会查询一个产品的BOM多级展开;如果这一句注释掉了,就可以查询全部产品物料的多级展开;下面还有一个控制的条件要同步改,一共两个.
union all
--2、递归子查询，根据定点子查询的查询结果来关联展开它的所有下级的BOM
select
p.BOM层次+1 as BOM层次,P.最顶级BOM内码 as 最顶级BOM内码
,t1.FNUMBER as BOM版本,fxwl.FNUMBER as 父项物料代码,fxwl_L.FNAME as 父项物料名称,t3.FSEQ as 分录行号
,t3.FREPLACEGROUP as 项次,cast(p.项次组合+'.'+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar) as 项次组合
,cast(p.BOM内码和项次组合 +'.'+ ( CAST(t1.FID AS nvarchar) + '-' +CAST(10000+t3.FREPLACEGROUP AS nvarchar) ) as nvarchar(max)) as BOM内码组合
,t3.FMATERIALID as 子项物料内码,zxwl.FNUMBER as 子项物料代码,zxwl_L.FNAME as 子项物料名称
,t3.FMATERIALTYPE
,t3.FBOMID,t1.FUSEORGID
,case when p.FBOMID = t1.FID then 1 else 0 end as 是否有子项BOM版本,t3.FREPNUMBER
from cte P --调用递归CTE本身
join dbo.T_ENG_BOM t1
on t1.FMATERIALID = p.子项物料内码
join T_BD_MATERIAL fxwl --父项关联物料表
on fxwl.FMATERIALID = t1.FMATERIALID
and t1.FFORBIDSTATUS = 'A'
join T_BD_MATERIAL_L fxwl_L --父项关联物料多语言表
on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052
join T_ENG_BOMCHILD t3
on t1.fid = t3.FID
join T_BD_MATERIAL zxwl --子项关联物料表
on zxwl.FMATERIALID = t3.FMATERIALID
join T_BD_MATERIAL_L zxwl_L --子项关联物料多语言表
on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052
)
--select * from cte ----调试第一段CTE
,cte2_ZuiXinZiXiangBom as --这个cte2是用来取非0层的子项BOM的最新BOM版本的,然后和0层的父项信息union在一起
(
select
t1.BOM层次 as BOM层级,t1.最顶级BOM内码,t1.BOM版本
,t1.父项物料代码 as 物料代码,t1.父项物料名称 as 物料名称
,0 as 分录行号,0 as 项次,t1.项次组合 as 项次组合,BOM内码和项次组合
,0 as 子项物料内码,'' as 子项物料代码,'' as 子项物料名称,'0' as FMATERIALTYPE,0 as BOM内码,t1.FUSEORGID,t1.是否有子项BOM版本
,t1.FREPNUMBER
,dense_rank() over(partition by t1.最顶级BOM内码,t1.父项物料代码 order by t1.BOM版本 desc) as BOM版本号分区
from cte t1
where 1=1
and t1.BOM层次 = 0 and t1.项次组合 = '10001' --这里是只显示0层的产品
--and t1.BOM版本 in ('1.01.003_V1.0') --这里可以输入一个产品BOM版本,则只会查询一个产品的BOM多级展开;如果这一句注释掉了,就可以查询全部产品物料的多级展开;上面还有一个控制的条件要同步改,一共两个.
union
select
t1.BOM层次+1 as BOM层级,t1.最顶级BOM内码,t1.BOM版本
,t1.子项物料代码 as 物料代码,t1.子项物料名称 as 物料名称
,t1.分录行号 as 分录行号,t1.项次 as 项次,t1.项次组合 as 项次组合,BOM内码和项次组合
,0 as 子项物料内码,t1.子项物料代码 as 子项物料代码,'' as 子项物料名称,t1.FMATERIALTYPE,t1.FBOMID as BOM内码,t1.FUSEORGID,t1.是否有子项BOM版本
,t1.FREPNUMBER
,dense_rank() over(partition by t1.最顶级BOM内码,t1.父项物料代码 order by t1.BOM层次+1,t1.是否有子项BOM版本 desc,t1.BOM版本 desc) as BOM版本号分区 --通过这个字段标识最新版本的BOM，按照父项物料分区之后，把BOM版本降序排列，BOM版本高的排序序号就是1
from cte t1
where 1=1
--and t1.BOM层次+1 <=2 --可以通过BOM层次字段来控制递归循环的次数,如果这里不加控制，那系统默认最多是循环100次
)
--select * from cte2_ZuiXinZiXiangBom t2 ----调试第二段CTE
select t2.BOM层级 as FBOMLevel
,t2.物料代码 as FCHILDMATERIALID,t2.物料名称 as FCHILDMATERIALNAME,t2.分录行号 as FROWNUMBER,t2.项次 as FGROUPID,t2.FMATERIALTYPE
,t2.FUSEORGID,t2.项次组合 as FGROUPIDCOM,t2.BOM内码和项次组合 FBOMGROUPIDCOM
,t2.BOM内码 as FCHILDBOMID,t2.BOM版本 as FBOM,t2.最顶级BOM内码 FTOPLEVEL,t2.FREPNUMBER,t4.FNUMBER FTOPMATERIALNUMBER --这一行的可以注释掉,只是为了排查SQL问题用的.
from cte2_ZuiXinZiXiangBom t2
LEFT JOIN T_ENG_BOM t3 ON t2.最顶级BOM内码=t3.FID
LEFT JOIN dbo.T_BD_MATERIAL t4 ON t4.FMATERIALID=t3.FMATERIALID
where t2.BOM版本号分区 = 1 --通过“BOM版本号分区”标识最新版本的BOM，按照父项物料分区之后，把BOM版本降序排列，BOM版本高的值就是1
and ( (t2.BOM层级 = 0 and t2.项次组合 = '10001' ) or (t2.BOM层级 > 0) ) --这个是为了查询出最终的结果.
and t2.FUSEORGID ={2} --AND t2.FMATERIALTYPE='1'
AND t2.FREPNUMBER!=''
order by t2.BOM内码和项次组合", OtherConst.DIALECT, bomId, orgId);
#endregion
var getBOM = CommonServiceHelper.SelectMethod(this.Context, _getSql);
List<DynamicObject> getRepNumber = new List<DynamicObject>();
if (getBOM != null && getBOM.Count > 0)
{
getRepNumber = getBOM.ToList();
}
bool isTDJ = Convert.ToBoolean(this.View.Model.GetValue("FIsIntShowSubMtrl"));
if (!isTDJ)
{
getRepNumber = getRepNumber.Where(s => (s["FMATERIALTYPE"] + "").Equals("1")).ToList();
}
foreach (var item in bomQueryChildItems1)
{
string mn = (item["MaterialId"] as DynamicObject)["Number"] + "";
var getThisRep = getRepNumber.Where(s => (s["FCHILDMATERIALID"] + "").Equals(mn)).ToList();
if (getThisRep != null && getThisRep.Count > 0)
{
string repNum = getThisRep.FirstOrDefault()["FREPNUMBER"] + "";
item.SetDynamicObjectItemValue("FRepNumber", repNum);
}
}
}
return bomQueryChildItems1;
}
2、扩展《物料清单汇总查询》表单，取消表单插件，挂载新插件。
3、表单实体增加属性FRepNumber，替代编码
4、测试
