/// <summary>
/// Calculate reconciliation final result based on statement summation result and reconciliation summation result
/// </summary>
/// <param name="grid"></param>
public static void BuildReconciliationFinalResult(YearlyReconciliationGridDto grid)
{
var statementResultRow = grid.Statements.FirstOrDefault(x => x.Title == StatementConst.Result);
var reconciliationResultRow = grid.Results.FirstOrDefault(x => x.Title == ReconciliationConst.Result);
if (statementResultRow == null || reconciliationResultRow == null)
{
return;
}
var row = new YearlyReconciliationGridResultDto(ReconciliationConst.FinalResult, 1);
foreach (var title in grid.Titles)
{
var column = new YearlyReconciliationGridResultValueDto(title.Month);
var income = statementResultRow.Values.FirstOrDefault(x => x.Month == title.Month);
var cost = reconciliationResultRow.Values.FirstOrDefault(x => x.Month == title.Month);
column.Amount = income?.Amount + cost?.Amount;
row.Values.Add(column);
}
grid.Results.Add(row);
}
/// <summary>
/// Calculate monthly statement result
/// </summary>
/// <param name="grid"></param>
public static void BuildStatementResult(YearlyReconciliationGridDto grid)
{
var incomeRow = grid.Statements.FirstOrDefault(x => x.Title == StatementConst.Income);
var costRow = grid.Statements.FirstOrDefault(x => x.Title == StatementConst.Cost);
if (incomeRow == null || costRow == null)
{
return;
}
var row = new YearlyReconciliationGridResultDto(StatementConst.Result, 4);
foreach (var title in grid.Titles)
{
var column = new YearlyReconciliationGridResultValueDto(title.Month);
var income = incomeRow.Values.FirstOrDefault(x => x.Month == title.Month);
var cost = costRow.Values.FirstOrDefault(x => x.Month == title.Month);
column.Amount = income?.Amount - cost?.Amount;
row.Values.Add(column);
}
grid.Statements.Add(row);
}
/// <summary>
/// Generate monthly cost statement
/// </summary>
/// <param name="grid"></param>
/// <param name="incomeOrExpenses"></param>
public static void BuildCostStatement(YearlyReconciliationGridDto grid, ref List <IncomeOrExpense> incomeOrExpenses)
{
var row = new YearlyReconciliationGridResultDto(StatementConst.Cost, 2);
foreach (var title in grid.Titles)
{
var column = new YearlyReconciliationGridResultValueDto(title.Month);
column.Amount = incomeOrExpenses
.Where(x => x.IncomeOrExpenseType.Flag == IncomeOrExpenseFlag.Expense &&
x.Date.Month == (int)title.Month)
.Sum(x => x.Amount);
row.Values.Add(column);
}
grid.Statements.Add(row);
}
/// <summary>
/// Calculate reconciliation summation result
/// </summary>
/// <param name="grid"></param>
public static void BuildReconciliationResult(YearlyReconciliationGridDto grid)
{
var row = new YearlyReconciliationGridResultDto(ReconciliationConst.Result, 0);
foreach (var title in grid.Titles)
{
var column = new YearlyReconciliationGridResultValueDto(title.Month);
var columnValues = grid.Rows
.SelectMany(x => x.Columns)
.Where(x => x.Month == title.Month).ToList();
column.Amount = columnValues.Where(x => x.Flag == IncomeOrExpenseFlag.Income).Sum(x => x.Amount) -
columnValues.Where(x => x.Flag == IncomeOrExpenseFlag.Expense).Sum(x => x.Amount);
row.Values.Add(column);
}
grid.Results.Add(row);
}
/// <summary>
/// Calculate cumulative final result
/// </summary>
/// <param name="grid"></param>
public static void BuildCumulativeFinalResult(YearlyReconciliationGridDto grid)
{
var finalResultRow = grid.Results.FirstOrDefault(x => x.Title == ReconciliationConst.FinalResult);
if (finalResultRow == null)
{
return;
}
var row = new YearlyReconciliationGridResultDto(ReconciliationConst.CumulativeFinalResult, 2);
for (var index = 0; index < grid.Titles.Count; index++)
{
var title = grid.Titles[index];
var column = new YearlyReconciliationGridResultValueDto(title.Month);
var currentMonth = finalResultRow.Values.FirstOrDefault(x => x.Month == title.Month);
if (index == 0)
{
if (currentMonth != null)
{
column.Amount = currentMonth.Amount;
}
}
else
{
var previousMonthCumulative = row.Values.FirstOrDefault(x => x.Month == (Month)((int)title.Month - 1));
if (previousMonthCumulative != null && currentMonth != null)
{
column.Amount = previousMonthCumulative.Amount + currentMonth.Amount;
}
}
row.Values.Add(column);
}
grid.Results.Add(row);
}
public async Task <YearlyReconciliationGridDto> GetReconciliationsAsync(int year)
{
var grid = new YearlyReconciliationGridDto()
{
Year = year,
Titles = Month.January.ToList().ConvertAll(x => new YearlyReconciliationGridTitleDto(x)),
};
//this can be improve through projection. i am ignoring projection for simplicity
var incomeOrExpenses = await Context.IncomeOrExpenses.AsNoTracking()
.Where(x => x.Date.Year == year)
.Include(x => x.IncomeOrExpenseType).ToListAsync();
StatementBuilder.BuildIncomeStatement(grid, ref incomeOrExpenses);
StatementBuilder.BuildCumulativeIncomeStatement(grid);
StatementBuilder.BuildCostStatement(grid, ref incomeOrExpenses);
StatementBuilder.BuildCumulativeCostStatement(grid);
StatementBuilder.BuildStatementResult(grid);
#region Build Reconcilation Editable Grid
//this can be improve through projection. i am ignoring projection for simplicity
var incomeOrExpenseTypes = await Context.IncomeOrExpenseTypes.AsNoTracking().ToListAsync();
incomeOrExpenseTypes = incomeOrExpenseTypes.OrderBy(x => x.Flag).ThenBy(x => x.DisplayName).ToList();
//this can be improve through projection. i am ignoring projection for simplicity
var reconciliations = await Context.Reconciliations.AsNoTracking()
.Where(x => x.Year == year)
.Include(x => x.IncomeOrExpenseType).ToListAsync();
foreach (var type in incomeOrExpenseTypes)
{
var row = new YearlyReconciliationGridRowDto(type.DisplayName, type.Flag);
foreach (var title in grid.Titles)
{
var reconciliation = reconciliations.FirstOrDefault(x =>
x.Year == year && x.Month == title.Month && x.IncomeOrExpenseTypeId == type.Id);
var column = new YearlyReconciliationGridColumnDto()
{
Year = year,
Month = title.Month,
IncomeOrExpenseTypeId = type.Id
};
if (reconciliation != null)
{
column.Id = reconciliation.Id;
column.Amount = reconciliation.Amount;
column.Flag = reconciliation.IncomeOrExpenseType.Flag;
}
row.Columns.Add(column);
}
grid.Rows.Add(row);
}
#endregion
ReconciliationBuilder.BuildReconciliationResult(grid);
ReconciliationBuilder.BuildReconciliationFinalResult(grid);
ReconciliationBuilder.BuildCumulativeFinalResult(grid);
return(grid);
}
