private List <dynamic> GetFinancialPositionByType(AccountChartTypeEnum accountType)
{
var query = (from tl in db.TransactionLines
join a in db.AccountCharts on tl.AccountId equals a.Id
join f in db.AccountCharts on tl.FundId equals f.Id
where a.Type == accountType
group new { tl, a, f } by new { Fund = f.Id, Account = a.Id } into grp
select new
{
Fund = grp.FirstOrDefault().f.Name,
Account = grp.FirstOrDefault().a.Name,
Amount = grp.Sum(s => s.tl.Amount) > 0 ? grp.Sum(s => s.tl.Amount) : grp.Sum(s => s.tl.Amount) * -1
}).ToList();
var pivotTable = query.ToPivotTable(
item => item.Fund,
item => item.Account,
items => items.Any() ? items.Sum(x => x.Amount) : 0);
return(pivotTable.ToDynamicList());
}
private List <dynamic> GetReportByType(AccountChartTypeEnum accountType, DateTime dateFrom, DateTime dateTo)
{
var transview = (from t in db.Transactions
join tl in db.TransactionLines on t.Id equals tl.TransactionId
join a in db.AccountCharts on tl.AccountId equals a.Id
join f in db.AccountCharts on tl.FundId equals f.Id
where a.Type == accountType && (t.TransactionDate >= dateFrom && t.TransactionDate <= dateTo)
group new { tl, a, f } by new { Fund = f.Id, Account = a.Id } into grp
select new
{
Fund = grp.FirstOrDefault().f.Name,
//Type = grp.FirstOrDefault().a.Type,
Account = grp.FirstOrDefault().a.Name,
Amount = grp.Sum(s => s.tl.Amount > 0 ? s.tl.Amount : s.tl.Amount * -1)
}).ToList();
var pivotTable = transview.ToPivotTable(
item => item.Fund,
item => item.Account,
items => items.Any() ? items.Sum(x => x.Amount) : 0);
return(pivotTable.ToDynamicList());
}
