前文：以下SQL是用于获取最新的存货核算表各出库核算及入库核算单价，不包含成本调整单的金额【不知道什么原因 反正没取到。。。所以请视业务场景使用】。
PS：取数SQL日期部分存在一个小BUG 代码部分已经修复
获取最新存货核算出库单价
获取最新存货核算采购单价
/// <summary>
/// 获取出库单价
/// </summary>
/// <returns></returns>
public Dictionary<Int64, Decimal> QuerySoPrice(Dictionary<Int64, List<DynamicObject>> MasterIds,Context this_Con)
{
string sql = @"select B.FMATERIALID ,FNUMBER, SUM(FCurrentOutAmount ) FCurrentOutAmount ,SUM(FOutStockQty) FOutStockQty,B.FYEAR,MAX(B.FPERIOD) FPERIOD
from (select FMATERIALID,MAX(FYEAR)FYEAR
from (select DIM.FMATERIALID,SUM(FOutStockQty) 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 bal.FOutStockQty<>0
GROUP BY FMATERIALID , FYEAR
) a where FOutStockQty<>0 GROUP BY FMATERIALID )A
left join (select DIM.FMATERIALID ,ma.FNUMBER, bal.FCurrentOutAmount,bal.FOutStockQty,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
left join T_BD_MATERIAL MA ON MA.FMATERIALID=DIM.FMATERIALID
where sy.fisdefault='1' and fa.fisdefault='1'and bal.FEndInitKey='1'
and bal.FOutStockQty<>0 ) b
on a.FMATERIALID=b.FMATERIALID and a.FYEAR=b.FYEAR
WHERE b.FMATERIALID IN (SELECT FID FROM table(fn_StrSplit(@MaterialIds,',',1)))
GROUP BY B.FMATERIALID ,FNUMBER,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;
}
/// <summary>
/// 获取入库单价
/// </summary>
/// <returns></returns>
public Dictionary<Int64, Decimal> QueryPoPrice(Dictionary<Int64, List<DynamicObject>> MasterIds, Context this_Con)
{
string sql = @"select B.FMATERIALID ,FNUMBER, SUM(FCurrentInAmount ) FCurrentInAmount ,SUM(FCurrentInQty) FCurrentInQty,B.FYEAR,MAX(B.FPERIOD)FPERIOD
from (select FMATERIALID,MAX(FYEAR)FYEAR
from (select DIM.FMATERIALID,SUM(FCurrentInQty) FCurrentInQty, 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 bal.FCurrentInQty<>0
GROUP BY FMATERIALID , FYEAR
) a where FCurrentInQty<>0 GROUP BY FMATERIALID )A
left join (select DIM.FMATERIALID ,ma.FNUMBER, bal.FCurrentInAmount,bal.FCurrentInQty,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
left join T_BD_MATERIAL MA ON MA.FMATERIALID=DIM.FMATERIALID
where sy.fisdefault='1' and fa.fisdefault='1'and bal.FEndInitKey='1'
and bal.FCurrentInQty<>0 ) b
on a.FMATERIALID=b.FMATERIALID and a.FYEAR=b.FYEAR
WHERE b.FMATERIALID IN (SELECT FID FROM table(fn_StrSplit(@MaterialIds,',',1)))
GROUP BY B.FMATERIALID ,FNUMBER,B.FYEAR";
SqlParam param = new SqlParam("@MaterialIds", KDDbType.udt_inttable, MasterIds.Keys.ToArray());
var PoPriceList = DBUtils.ExecuteDynamicObject(this_Con, sql, null, null, System.Data.CommandType.Text, param);
//var PoPriceList = DBUtils.ExecuteDynamicObject(this_Con, sql);
// 优化后算法：
// 对已经取得的物料库存数据，按物料进行分组，以便后面在循环中，快速取到本物料的库存
Dictionary<Int64, Decimal> PoPrice = new Dictionary<Int64, Decimal>();
foreach (var Price in PoPriceList)
{
long materialMasterId = Convert.ToInt64(Price["FMATERIALID"]);
Decimal FCurrentInAmount = Convert.ToDecimal(Price["FCurrentInAmount"]);
Decimal FCurrentInQty = Convert.ToDecimal(Price["FCurrentInQty"]);
if (PoPrice.ContainsKey(materialMasterId) == false)
{
Decimal PriceXX = FCurrentInAmount / FCurrentInQty;
PoPrice.Add(materialMasterId, PriceXX);
}
}
return PoPrice;
}
推荐阅读