業務需求
增加文字，顯示物料清單的替代編碼。
說明
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、測試
