public ServiceResult<AccountBalancesModel> GetAccountBalances(DateTime? from = null, DateTime? to = null)
{
var result = new ServiceResult<AccountBalancesModel>();
var sql = @"
WITH MonthlyAccountDiffs AS
(
SELECT a.Id AS AccountId, DATEADD(MONTH, MONTH(t.Timestamp), DATEADD(YEAR, YEAR(t.Timestamp)-1900, 0)) AS [Month], sum(t.Amount) AS MonthTransactionTotal
FROM Account a
LEFT JOIN [Transaction] t ON t.AccountId = a.Id
GROUP BY a.Id, DATEADD(MONTH, MONTH(t.Timestamp), DATEADD(YEAR, YEAR(t.Timestamp)-1900, 0))
)
SELECT
ag.*,
'' as split,
a.Id, a.Name, a.InitialBalance, a.Balance, a.BalanceTimestamp,
(SELECT COUNT(*) FROM [Transaction] t JOIN Subtransaction st ON st.TransactionId = t.Id WHERE t.AccountId = md.AccountId and st.CategoryId is null) AS UncategorizedTransactionCount,
'' as split,
a.Id as AccountId,
md.Month, md.MonthTransactionTotal,
(SELECT SUM(md2.MonthTransactionTotal) FROM MonthlyAccountDiffs md2 WHERE md2.AccountId = a.Id AND md2.Month <= md.Month) AS MonthEndBalance
FROM MonthlyAccountDiffs md
JOIN Account a ON a.Id = md.AccountId
LEFT JOIN AccountGroup ag ON ag.Id = a.AccountGroupId";
var balances = _db.Connection.Query<AccountBalancesModel.AccountGroupModel, AccountBalancesModel.AccountModel, AccountBalancesModel.AccountBalanceModel, Tuple<AccountBalancesModel.AccountGroupModel, AccountBalancesModel.AccountModel, AccountBalancesModel.AccountBalanceModel>>(
sql, (agm, am, abm) => new Tuple<AccountBalancesModel.AccountGroupModel, AccountBalancesModel.AccountModel, AccountBalancesModel.AccountBalanceModel>(agm, am, abm),
splitOn: "split");
var accountGroups = balances
.GroupBy(x => x.Item1.Id)
.Select(x => x.First().Item1)
.OrderBy(x => x.DisplayOrder);
foreach (var accountGroup in accountGroups)
{
accountGroup.Accounts = balances
.Where(x => x.Item1.Id == accountGroup.Id)
.GroupBy(x => x.Item2.Id)
.Select(x => x.First().Item2)
.OrderBy(x => x.Name);
foreach (var account in accountGroup.Accounts)
{
account.AccountBalances = balances
.Where(x => x.Item2.Id == account.Id)
.Select(x => x.Item3)
.OrderBy(x => x.Month);
}
}
var model = new AccountBalancesModel();
model.DateRange = new DateRangeModel(from, to);
model.AccountBalances = accountGroups;
result.Result = model;
return result;
}
public string AccountGroupClass(AccountBalancesModel.AccountGroupModel accountGroupModel)
{
return "account-group" + (accountGroupModel.IsActive ? "" : " inactive");
}
