ASP.Net MVC - Razor Displaying account totals for customers by Month/Year Columns

Question

I have a statement summary view that i need to show total balance on a customers account by month and year.

I have created the linq query and view models which successfully pull this data from the database.

I want to display this data in a tabular form e.g.

View Desired

My LINQ code

var monthlyTotals = from t in db.InvoiceItems.AsEnumerable()
                                 // where t.Invoice.LegalFile.IsClosed == false
                                  group t by t.Invoice.LegalFile.Client into g
                                  select new StatementSummaryVM
                                  {
                                      Client = g.Key,
                                      GrandTotal = g.Sum(x => x.AmountInclVAT),
                                      MonthlyTotals = from i in g
                                              group i by new
                                                    {
                                                        month = i.ItemDate.Month,
                                                        year = i.ItemDate.Year
                                                    }
                                              into d
                                              select new MonthlyTotalsVM
                                              {
                                                  Date = new DateTime(d.Key.year, d.Key.month,1),
                                                  Amount = d.Sum(s => s.AmountInclVAT)
                                              }
                                  };

            return monthlyTotals;

        }

My View Model

public class StatementSummaryVM
{
    [Display(Name = "File No.")]
    public int Id { get; set; }

    public Client Client { get; set; }
    public IEnumerable<MonthlyTotalsVM> MonthlyTotals { get; set; }
    [Display(Name = "Grand Total")]
    [DisplayFormat(DataFormatString = "{0:C}")]
    public decimal GrandTotal { get; set; }
}

public class MonthlyTotalsVM
{
    [DisplayFormat(DataFormatString = "{0:MMM-yyyy}")]
    public DateTime Date { get; set; }
    [DisplayFormat(DataFormatString = "{0:C}")]
    public decimal Amount { get; set; }
}

My Current View Code

<table class="table">
    <tr>
        <th>File No.</th>
        <th>Client</th>
        <th>Grand Total</th>

        @foreach (var item in Model)
        {
            <th></th>
            foreach (var monthlyTotal in item.MonthlyTotals)
            {
                <th>@Html.DisplayFor(model => monthlyTotal.Date)</th>
            }
        }
    </tr>

    @foreach (var item in Model)
            {
        <tr>
            <td>@item.Client.Id</td>
             <td>@item.Client.Name </td>
             <td>@item.GrandTotal</td>

        @foreach (var monthlyTotal in item.MonthlyTotals)
        {
             <td>@Html.DisplayFor(model => monthlyTotal.Date)</td>
            <td>@monthlyTotal.Amount</td>
        }
        </tr>
    }

</table>

Which looks like this when rendered

Current Rendered View1:

Im struggling to get this to display correctly.

Any help would really be appreciated.Thanks

Answer 1

Your existing code is only returning a collection of MonthlyTotalsVM where a value actually exists and the key to making this work is to initialize the collection with a MonthlyTotalsVM for each month in the range of dates you want to display and then update the corresponding item in the collection based on the index of the month.

Assuming your method accepts parameters for the startDate and the number of months to display in the table, you code would be

public ActionResult StatementSummary(DateTime startDate, int months)
{
    // Get the start and end dates
    startDate = new DateTime(startDate.Year, startDate.Month, 1); // ensure first day of month
    DateTime endDate = startDate.AddMonths(months + 1);
    // Initialize the model
    List<StatementSummaryVM> model = new List<StatementSummaryVM>();
    // Filter the data and group by client
    var data = db.InvoiceItems
        .Where(i => i.ItemDate >= startDate && i.ItemDate < endDate)
        .GroupBy(i => i.Invoice.LegalFile.Client);
    // Create a StatementSummaryVM for each client group
    foreach(var clientGroup in data)
    {
        StatementSummaryVM summary = new StatementSummaryVM(startDate, months)
        {
            Client = clientGroup.Key,
            GrandTotal = clientGroup.Sum(x => x.AmountInclVAT)
        };
        // Group by month/year
        foreach(var monthGroup in clientGroup.GroupBy(x => new { Month = x.Date.Month, Year = x.Date.Year }))
        {
            // Get the index of the month
            int index = ((monthGroup.Key.Year - startDate.Year) * 12) + monthGroup.Key.Month - startDate.Month;
            summary.MonthlyTotals[index].Amount = monthGroup.First().AmountInclVAT; // or .Sum(m => m.AmountInclVAT) if there are multiple invoives per month
        }
        model.Add(summary);
    }
    return View(model);
}

And then change your StatementSummaryVM model to add a constructor that initializes the collection

public class StatementSummaryVM
{
    public StatementSummaryVM(DateTime startDate, int months)
    {
        MonthlyTotals = new List<MonthlyTotalsVM>();
        for (int i = 0; i < months; i++)
        {
            MonthlyTotals.Add(new MonthlyTotalsVM() { Date = startDate.AddMonths(i) });
        }
    }
    .....
}