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()); }