【emoji】以下代码会获取全部物料的
如果要获取指定物料的可以参考：https://vip.kingdee.com/article/76961607695187456
自己增加过滤条件
Ps:图片中按期间取数有BUG 已修复。取最新年份金额后 再获取最大月份那一条分录数据。
代码已更正，直接copy需要根据实际情况测试
/// <summary>
/// 获取所有物料的最新核算单价
/// </summary>
/// <returns></returns>
public Dictionary<Int64, Decimal> QueryHSPrice(Context this_Con)
{
string sql = @"select B.FMATERIALID , SUM(FAmount ) FCurrentOutAmount ,SUM(FQty) FOutStockQty,B.FYEAR,MAX(B.FPERIOD) FPERIOD
from (select FMATERIALID,MAX(FYEAR)FYEAR
from (select DIM.FMATERIALID,SUM(FQty) FOutStockQty, FYEAR
from T_HS_OUTACCTG AG
inner join T_HS_INIVBALANCE_H BAL on AG.FID = BAL.FID
inner join T_HS_CALDIMENSIONS CAL on AG.FDIMENSIONID = CAL.FDIMENSIONID
inner join T_HS_INIVSTOCKDIMENSION DIM on BAL.FDIMEENTRYID = DIM.FENTRYID
inner join T_ORG_ACCOUNTSYSTEM sy on CAL.Facctsystemid =sy. FACCTSYSTEMID
inner join T_FA_ACCTPOLICY fa on fa.FACCTPOLICYID=cal.FACCTPOLICYID
where sy.fisdefault='1' and fa.fisdefault='1'and bal.FEndInitKey='1' and FQty<>0
GROUP BY FMATERIALID , FYEAR
) a GROUP BY FMATERIALID )A
left join (select DIM.FMATERIALID , SUM(bal.FAmount ) FAmount ,SUM(bal.FQty) FQty ,FYEAR,FPERIOD
from T_HS_OUTACCTG AG
inner join T_HS_INIVBALANCE_H BAL on AG.FID = BAL.FID
inner join T_HS_CALDIMENSIONS CAL on AG.FDIMENSIONID = CAL.FDIMENSIONID
inner join T_HS_INIVSTOCKDIMENSION DIM on BAL.FDIMEENTRYID = DIM.FENTRYID
inner join T_ORG_ACCOUNTSYSTEM sy on CAL.Facctsystemid =sy. FACCTSYSTEMID
inner join T_FA_ACCTPOLICY fa on fa.FACCTPOLICYID=cal.FACCTPOLICYID
where sy.fisdefault='1' and fa.fisdefault='1'and bal.FEndInitKey='1' and FQty<>0
group by DIM.FMATERIALID,FYEAR,FPERIOD) b
on a.FMATERIALID=b.FMATERIALID and a.FYEAR=b.FYEAR
GROUP BY B.FMATERIALID ,B.FYEAR";
//SqlParam param = new SqlParam("@MaterialIds", KDDbType.udt_inttable, MasterIds.Keys.ToArray());
//var SoPriceList = DBUtils.ExecuteDynamicObject(this_Con, sql, null, null, System.Data.CommandType.Text, param);
var SoPriceList = DBUtils.ExecuteDynamicObject(this_Con, sql);
// 对已经取得的物料库存数据，按物料进行分组，以便后面在循环中，快速取到本物料的信息
Dictionary<Int64, Decimal> SoPrice = new Dictionary<Int64, Decimal>();
foreach (var Price in SoPriceList)
{
long materialMasterId = Convert.ToInt64(Price["FMATERIALID"]);
Decimal FCurrentOutAmount = Convert.ToDecimal(Price["FCurrentOutAmount"]);
Decimal FOutStockQty = Convert.ToDecimal(Price["FOutStockQty"]);
if (SoPrice.ContainsKey(materialMasterId) == false)
{
if (FOutStockQty == 0)
{
SoPrice.Add(materialMasterId, 0);
}
else
{
Decimal PriceXX = FCurrentOutAmount / FOutStockQty;
SoPrice.Add(materialMasterId, PriceXX);
}
}
}
return SoPrice;
}
赞 12
打赏推荐阅读