分享一个总仓核算模式下循环获取物料期末单价的C#方法（向前寻找12个月）
有些财务想通过期末结存单价计算物料清单的成本（系统只支持采购订单/采购入库/采购发票/采购价目表/物料参考成本）或者是在即时库存看到物料的期末结存单价，此方法递归查询物料最新一期的期末结存单价。分仓核算模式下，需要关联核算范围表 获取仓库信息 方面里面添加仓库参数即可。
/// <summary>
/// 总仓模式:获取物料的期末结存单价，期末数量为0则获取发出单价/收入单价，最大向前追溯12个月 ctx当前上下文,fma物料编码,year年份,qj期间,i默认0 返回一个List<string> List[0] 单价 List[1] 取价期间
/// </summary>
/// <param name="ctx"></param>
/// <param name="fma"></param>
/// <param name="year"></param>
/// <param name="qj"></param>
/// <param name="i"></param>
/// <returns></returns>
public List<string> GetCost(Context ctx, string fma, int year, int qj,int i, int zz)
{
List<string> msgs = new List<string>();
if (i > 12)
{
return null;
}
else
{
if (i == 0)
{
string sql = string.Format("select a.FBEGINADJAMOUNT,FENDINITKEY,FQTY,a.FOUTSTOCKQTY,a.FCURRENTINQTY,a.FCURRENTINAMOUNT,a.FCURRENTOUTAMOUNT,isnull(e.FADJUSTMENTAMOUNT,0)FADJUSTMENTAMOUNT from T_HS_Balance a inner join T_HS_StockDimension b on a.FDIMEENTRYID=b.FENTRYID inner join T_BD_MATERIAL c on b.FMASTERID=c.FMASTERID inner join T_HS_OUTACCTG d on a.FID=d.FID left join (select isnull(sum (FADJUSTMENTAMOUNT),0) FADJUSTMENTAMOUNT,FAcctgDimeEntryId from T_HS_ADJUSTMENTBILL a inner join T_HS_ADJUSTMENTBILLENTRY b on a.FID=b.FID inner join T_HS_OUTACCTG c on a.FACCTGID=c.FID where FYEAR='{1}' and FPERIOD='{2}' group by FAcctgDimeEntryId) e on a.FDIMEENTRYID=e.FACCTGDIMEENTRYID inner join T_HS_CALDIMENSIONS f on a.FDIMENSIONID=f.FDIMENSIONID and f.FACCTSYSTEMID='1' and f.FFINORGID='{3}' where c.FUSEORGID='{3}' and FENDINITKEY=1 and c.FNUMBER='{0}' ", fma, year, qj, zz);
DataTable table = this.Select(sql, ctx);
if (table != null && (Convert.ToDecimal(table.Rows[0]["FQTY"]) != 0 || Convert.ToDecimal(table.Rows[0]["FOUTSTOCKQTY"]) != 0 || Convert.ToDecimal(table.Rows[0]["FCURRENTINQTY"]) != 0))
{
if (Convert.ToDecimal(table.Rows[0]["FQTY"]) == 0)//结存数量为0
{
if (Convert.ToDecimal(table.Rows[0]["FOUTSTOCKQTY"]) == 0)//发出数量为0
{
msgs.Add(Math.Round(Convert.ToDecimal(table.Rows[0]["FCURRENTINAMOUNT"]) / Convert.ToDecimal(table.Rows[0]["FCURRENTINQTY"]), 6).ToString());
msgs.Add(string.Format("'{0}'年{1}期收入单价", year, qj));
return msgs;
}
else
{
msgs.Add(Math.Round(Convert.ToDecimal(table.Rows[0]["FCURRENTOUTAMOUNT"]) / Convert.ToDecimal(table.Rows[0]["FOUTSTOCKQTY"]), 6).ToString());
msgs.Add(string.Format("{0}年{1}期发出单价", year, qj));
return msgs;
}
}
else
{
msgs.Add(Math.Round((Convert.ToDecimal(table.Rows[0]["FBEGINADJAMOUNT"]) - Convert.ToDecimal(table.Rows[0]["FADJUSTMENTAMOUNT"]) - Convert.ToDecimal(table.Rows[0]["FCURRENTOUTAMOUNT"]) + Convert.ToDecimal(table.Rows[0]["FCURRENTINAMOUNT"])) / Convert.ToDecimal(table.Rows[0]["FQTY"]), 6).ToString());
msgs.Add(string.Format("{0}年{1}期期末余额", year, qj));
return msgs;
}
}
else
{
i++;
if (i < qj)
{
return GetCost(ctx, fma, year, qj - i,i, zz);
}
else
{
return GetCost(ctx, fma, year - 1, qj - i + 12,i, zz);
}
}
}
else
{
string sql = string.Format("select a.FBEGINADJAMOUNT,a.FQTY,a.FOUTSTOCKQTY,a.FCURRENTINQTY,a.FCURRENTINAMOUNT,a.FCURRENTOUTAMOUNT,isnull(e.FADJUSTMENTAMOUNT,0)FADJUSTMENTAMOUNT from T_HS_BALANCE_H a inner join T_HS_StockDimension b on a.FDIMEENTRYID=b.FENTRYID inner join T_BD_MATERIAL c on b.FMASTERID=c.FMASTERID inner join T_HS_OUTACCTG d on a.FID=d.FID left join (select isnull(sum (FADJUSTMENTAMOUNT),0) FADJUSTMENTAMOUNT,FAcctgDimeEntryId from T_HS_ADJUSTMENTBILL a inner join T_HS_ADJUSTMENTBILLENTRY b on a.FID=b.FID inner join T_HS_OUTACCTG c on a.FACCTGID=c.FID where FYEAR='{1}' and FPERIOD='{2}' group by FAcctgDimeEntryId) e on a.FDIMEENTRYID=e.FACCTGDIMEENTRYID inner join T_HS_CALDIMENSIONS f on a.FDIMENSIONID=f.FDIMENSIONID and f.FACCTSYSTEMID='1' and f.FFINORGID='{3}' where c.FUSEORGID='{3}' and FENDINITKEY=1 and c.FNUMBER='{0}' and FYEAR='{1}' and FPERIOD='{2}' ", fma, year, qj, zz);
DataTable table = this.Select(sql, ctx);
if (table != null && (Convert.ToDecimal(table.Rows[0]["FQTY"]) != 0 || Convert.ToDecimal(table.Rows[0]["FOUTSTOCKQTY"]) != 0 || Convert.ToDecimal(table.Rows[0]["FCURRENTINQTY"]) != 0))
{
if (Convert.ToDecimal(table.Rows[0]["FQTY"]) == 0)//结存数量为0
{
if (Convert.ToDecimal(table.Rows[0]["FOUTSTOCKQTY"]) == 0)//发出数量为0
{
msgs.Add(Math.Round(Convert.ToDecimal(table.Rows[0]["FCURRENTINAMOUNT"]) / Convert.ToDecimal(table.Rows[0]["FCURRENTINQTY"]), 6).ToString());
msgs.Add(string.Format("{0}年{1}期收入单价", year, qj));
return msgs;
}
else
{
msgs.Add(Math.Round(Convert.ToDecimal(table.Rows[0]["FCURRENTOUTAMOUNT"]) / Convert.ToDecimal(table.Rows[0]["FOUTSTOCKQTY"]), 6).ToString());
msgs.Add(string.Format("{0}年{1}期发出单价", year, qj));
return msgs;
}
}
else
{
msgs.Add(Math.Round((Convert.ToDecimal(table.Rows[0]["FBEGINADJAMOUNT"]) - Convert.ToDecimal(table.Rows[0]["FADJUSTMENTAMOUNT"]) - Convert.ToDecimal(table.Rows[0]["FCURRENTOUTAMOUNT"]) + Convert.ToDecimal(table.Rows[0]["FCURRENTINAMOUNT"])) / Convert.ToDecimal(table.Rows[0]["FQTY"]), 6).ToString());
msgs.Add(string.Format("{0}年{1}期期末余额", year, qj));
return msgs;
}
}
else
{
i++;
if (qj - 1 >= 1)
{
return GetCost(ctx, fma, year, qj - 1,i, zz);
}
else
{
return GetCost(ctx, fma, year - 1, qj - 1 + 12,i, zz);
}
}
}
}
}
/// <summary>
/// 数据库查询
/// </summary>
/// <param name="sql">查询sql</param>
/// <param name="ctx">上下文</param>
/// <returns></returns>
public DataTable Select(string sql, Context ctx)
{
DataSet table = DBServiceHelper.ExecuteDataSet(ctx, sql);
if (!(table != null && table.Tables.Count > 0 && table.Tables[0].Rows.Count > 0))
{
return null;
}
else
{
return table.Tables[0];
}
}
楼主是值得我深深关注的大神。
牛逼
分享一个总仓核算模式下循环获取物料期末单价的C#方法（向前寻找12个月）
本文2024-09-16 17:22:16发表“云星空知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3cloud-15277.html
