# 使用MemBomExpandOption获取物料清单正查BOM数据写入底表，下面直接上代码
```
public class BomShow : IScheduleService
{
public void Run(Context ctx, Schedule schedule)
{
string sql = string.Format(@"/*dialect*/ select distinct * from(select
'bom版本' bom版本,a.FID,
'使用组织' 使用组织,a.FUSEORGID,
'物料' 物料,a.FMATERIALID,
'物料单位' 物料单位,a.FUNITID
from T_ENG_BOM a
join T_ORG_ORGANIZATIONS b on a.FUSEORGID=b.forgid
join T_BD_MATERIAL c on a.FMATERIALID=c.FMATERIALID
join T_BD_UNIT d on a.FUNITID=d.FUNITID
where a.FFORBIDSTATUS='A' and b.FNumber='105.01'
and Convert(date,a.FMODIFYDATE,120)='{0}'
union all
select
'bom版本' bom版本,BomId,
'使用组织' 使用组织,b.FUSEORGID,
'物料' 物料,b.FMATERIALID,
'物料单位' 物料单位,b.FUNITID
from Run_BomTable a
join T_ENG_BOM b on a.BomId=b.FID
where BomLevel=0 and a.BomId in (select BomId from Run_BomTable where BomLevel=0 and Id in
(select Id from Run_BomTable where MaterialId in (select a.FMATERIALID from T_ENG_BOM a
join T_ORG_ORGANIZATIONS b on a.FUSEORGID=b.forgid
where a.FFORBIDSTATUS='A' and b.FNumber='105.01' and Convert(date,a.FMODIFYDATE,120)='{0}'))))a", DateTime.Now.ToString("yyyy-MM-dd"));
DataTable dt = DBServiceHelper.ExecuteDataSet(ctx, sql).Tables[0];
if (dt.Rows.Count > 0)
{
MemBomExpandOption memBomExpandOption = new MemBomExpandOption();
memBomExpandOption.DeleteVirtualMaterial = false;
memBomExpandOption.ExpandVirtualMaterial = true;
memBomExpandOption.DeleteSkipRow = false;
memBomExpandOption.ExpandSkipRow = true;
memBomExpandOption.CsdSubstitution = true;
memBomExpandOption.IsExpandForbidden = true; //疑似展开外购件
memBomExpandOption.ParentCsdYieldRate = true;
memBomExpandOption.ChildCsdYieldRate = true;
memBomExpandOption.ExpandLevelTo = 30;
memBomExpandOption.BomExpandCalType = Kingdee.K3.Core.MFG.EnumConst.Enums.Enu_BomExpandCalType.kdBySystemProfile;
memBomExpandOption.IsStandReplaceGroup = false;
memBomExpandOption.ChildCsdYieldRate = true;
memBomExpandOption.CsdMtrlPlanStrategy = true;
memBomExpandOption.IsConvertUnitQty = true;
memBomExpandOption.IsExpandSubMtrl = true;
memBomExpandOption.IsExpandSupplyManager = true;
memBomExpandOption.IsHideOutSourceBOM = true;
memBomExpandOption.IsKeepNumeratorUnchange = true;
memBomExpandOption.IsUseCache = true;
memBomExpandOption.ParentCsdYieldRate = true;
int Id = 1;
for (int i = 0; i < dt.Rows.Count; i++)
{
List<DynamicObject> bomCollection = new List<DynamicObject>();
BomForwardSourceDynamicRow row = BomForwardSourceDynamicRow.CreateInstance();
row.MaterialId_Id = Convert.ToInt32(dt.Rows[i]["FMATERIALID"].ToString()); //物料内码
row.BomId_Id = Convert.ToInt32(dt.Rows[i]["FID"].ToString());//BOM内码
row.UnitId_Id = Convert.ToInt32(dt.Rows[i]["FUNITID"].ToString()); //单位内码
row.BaseUnitId_Id = Convert.ToInt32(dt.Rows[i]["FUNITID"].ToString()); //基本单价内码
row.SupplyOrgId_Id = Convert.ToInt32(dt.Rows[i]["FUSEORGID"].ToString());//使用组织内码
row.TimeUnit = 1.ToString(); //默认BOM展开过程使用的时间单位为天。
row.NeedQty = 1;//需求数量
bomCollection.Add(row);
string textsql = string.Format(@"select Id from Run_BomTable where BomId='{0}' and BomLevel='0'",dt.Rows[i]["FID"].ToString());
DataTable ds = DBServiceHelper.ExecuteDataSet(ctx, textsql).Tables[0];
if (ds.Rows.Count>0)
{
Id = Convert.ToInt32(ds.Rows[0]["Id"].ToString());
string delsql = string.Format(@"delete from Run_BomTable where Id='{0}'", Id);
int n = DBServiceHelper.Execute(ctx, delsql);
if (n==0)
{
continue;
}
else
{
DynamicObjectCollection bomData = BomExpandServiceHelper.ExpandBomForwardMen(ctx, bomCollection, memBomExpandOption)["BomExpandResult"] as DynamicObjectCollection;
foreach (DynamicObject item in bomData)
{
string BomId_Id = item["BomId_Id"].ToString();//bom版本内码
DynamicObject BomId = item["BomId"] as DynamicObject;
string BomNumber = BomId == null ? "" : BomId[6].ToString();
string BomLevel = item["BomLevel"].ToString();//当前展开的级次
string MaterialId_Id = item["MaterialId_Id"].ToString();//物料内码
DynamicObject MaterialId = item["MaterialId"] as DynamicObject;
string MaterialNumber = MaterialId == null ? "" : MaterialId[6].ToString();
string MaterialName = MaterialId == null ? "" : MaterialId[5].ToString();
DynamicObject UnitId = item["UnitId"] as DynamicObject;//单位
string UnitName = UnitId == null ? "" : UnitId[3].ToString();
decimal NUMERATOR = Convert.ToDecimal(item["NUMERATOR"].ToString());//--用量:分子
decimal DENOMINATOR = Convert.ToDecimal(item["DENOMINATOR"].ToString());//用量:分母
decimal ActualQty = Convert.ToDecimal(item["ActualQty"].ToString());//实际用量
string POSITIONNO = item["POSITIONNO"] == null ? "" : item["POSITIONNO"].ToString();//位置号
if (POSITIONNO.IndexOf("'") != -1)//判断字符串中是否存在单引号
{
POSITIONNO = POSITIONNO.Replace("'", "''");
}
string ReplaceType = item["ReplaceType"].ToString();//替代方式（1、替代;2、取代）
if (item["ReplaceType"].ToString() == "1")
{
ReplaceType = "替代";
}
if (item["ReplaceType"].ToString() == "2")
{
ReplaceType = "取代";
}
string MATERIALTYPE = item["MATERIALTYPE"].ToString();//子项类型（1、标准件;2、返还件；3、替代件）
if (item["MATERIALTYPE"].ToString() == "1")
{
MATERIALTYPE = "标准件";
}
if (item["MATERIALTYPE"].ToString() == "2")
{
MATERIALTYPE = "返还件";
}
if (item["MATERIALTYPE"].ToString() == "3")
{
MATERIALTYPE = "替代件";
}
int ReplacePriority = Convert.ToInt32(item["ReplacePriority"].ToString());//替代优先级
string allocation = "100%";//采购配额
if (ReplaceType == "取代" && ReplacePriority != 1)
{
allocation = "0%";
}
if (ReplaceType == "替代" && MATERIALTYPE != "标准件")
{
allocation = "0%";
}
string PATHENTRYID = item["PATHENTRYID"] == null ? "" : item["PATHENTRYID"].ToString();//上级组合码
int ReplaceGroup = Convert.ToInt32(item["ReplaceGroup"].ToString());//行号
string TotalGroup = BomLevel == "0" ? "0" : item["TotalGroup"].ToString();//顺序号
string sqlInsert = string.Format(@"insert into Run_BomTable(Id,BomId,BomNumber,BomLevel,MaterialId,MaterialNumber,MaterialName,
UnitName,NUMERATOR,DENOMINATOR,ActualQty,POSITIONNO,ReplaceType,MATERIALTYPE,allocation,PATHENTRYID,ReplaceGroup,TotalGroup)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')",
Id, BomId_Id, BomNumber, BomLevel, MaterialId_Id, MaterialNumber, MaterialName, UnitName,
NUMERATOR, DENOMINATOR, ActualQty, POSITIONNO, ReplaceType, MATERIALTYPE, allocation, PATHENTRYID, ReplaceGroup, TotalGroup);
DBServiceHelper.Execute(ctx, sqlInsert);
}
}
}
else
{
string sqlstr = string.Format(@"select isnull(max(Id),0)+1 Id from Run_BomTable");
DataTable dr = DBServiceHelper.ExecuteDataSet(ctx, sqlstr).Tables[0];
Id = Convert.ToInt32(dr.Rows[0]["Id"].ToString());
DynamicObjectCollection bomData = BomExpandServiceHelper.ExpandBomForwardMen(ctx, bomCollection, memBomExpandOption)["BomExpandResult"] as DynamicObjectCollection;
foreach (DynamicObject item in bomData)
{
string BomId_Id = item["BomId_Id"].ToString();//bom版本内码
DynamicObject BomId = item["BomId"] as DynamicObject;
string BomNumber = BomId == null ? "" : BomId[6].ToString();
string BomLevel = item["BomLevel"].ToString();//当前展开的级次
string MaterialId_Id = item["MaterialId_Id"].ToString();//物料内码
DynamicObject MaterialId = item["MaterialId"] as DynamicObject;
string MaterialNumber = MaterialId == null ? "" : MaterialId[6].ToString();
string MaterialName = MaterialId == null ? "" : MaterialId[5].ToString();
DynamicObject UnitId = item["UnitId"] as DynamicObject;//单位
string UnitName = UnitId == null ? "" : UnitId[3].ToString();
decimal NUMERATOR = Convert.ToDecimal(item["NUMERATOR"].ToString());//--用量:分子
decimal DENOMINATOR = Convert.ToDecimal(item["DENOMINATOR"].ToString());//用量:分母
decimal ActualQty = Convert.ToDecimal(item["ActualQty"].ToString());//实际用量
string POSITIONNO = item["POSITIONNO"] == null ? "" : item["POSITIONNO"].ToString();//位置号
if (POSITIONNO.IndexOf("'") != -1)//判断字符串中是否存在单引号
{
POSITIONNO = POSITIONNO.Replace("'", "''");
}
string ReplaceType = item["ReplaceType"].ToString();//替代方式（1、替代;2、取代）
if (item["ReplaceType"].ToString() == "1")
{
ReplaceType = "替代";
}
if (item["ReplaceType"].ToString() == "2")
{
ReplaceType = "取代";
}
string MATERIALTYPE = item["MATERIALTYPE"].ToString();//子项类型（1、标准件;2、返还件；3、替代件）
if (item["MATERIALTYPE"].ToString() == "1")
{
MATERIALTYPE = "标准件";
}
if (item["MATERIALTYPE"].ToString() == "2")
{
MATERIALTYPE = "返还件";
}
if (item["MATERIALTYPE"].ToString() == "3")
{
MATERIALTYPE = "替代件";
}
int ReplacePriority = Convert.ToInt32(item["ReplacePriority"].ToString());//替代优先级
string allocation = "100%";//采购配额
if (ReplaceType == "取代" && ReplacePriority != 1)
{
allocation = "0%";
}
if (ReplaceType == "替代" && MATERIALTYPE != "标准件")
{
allocation = "0%";
}
string PATHENTRYID = item["PATHENTRYID"] == null ? "" : item["PATHENTRYID"].ToString();//上级组合码
int ReplaceGroup = Convert.ToInt32(item["ReplaceGroup"].ToString());//行号
string TotalGroup = BomLevel == "0" ? "0" : item["TotalGroup"].ToString();//顺序号
string sqlInsert = string.Format(@"insert into Run_BomTable(Id,BomId,BomNumber,BomLevel,MaterialId,MaterialNumber,MaterialName,
UnitName,NUMERATOR,DENOMINATOR,ActualQty,POSITIONNO,ReplaceType,MATERIALTYPE,allocation,PATHENTRYID,ReplaceGroup,TotalGroup)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')",
Id, BomId_Id, BomNumber, BomLevel, MaterialId_Id, MaterialNumber, MaterialName, UnitName,
NUMERATOR, DENOMINATOR,ActualQty, POSITIONNO, ReplaceType, MATERIALTYPE, allocation, PATHENTRYID, ReplaceGroup, TotalGroup);
DBServiceHelper.Execute(ctx, sqlInsert);
}
}
}
}
else
{
return;
}
}
}
```
