c# - Linq query to group payment receipts for a person over a period

Question

I need help to construct a linq query to display payments grouped by person and TransactId. MemberId and Year will be passed to controller by query string.

I have these models

Member(MemberId,MemberName)
Payment(PayId, MemberId[foreignkey],TransactId, PayDate, Amount,CategoryId[foreignkey])
Category(CategoryId, CategoryName)

Looking at getting an output like below: John could have made two or more different payments(unlimited but reports are fetched for 12 months) each payment is given a unique transactId with different payment categories.

So in database payment as shown below: for 30/12/2016, Cat1,Cat2,Cat3 will be separate rows but with the same date, trasactId and memberId.

Name: John Brown  TrasactId: 3089ced9-b95e-4625  PayDate: 30/12/2016
Category     Amount
Cat1          12.30   
Cat2          10.30   
Cat3          20.40
Total         43.00

Name: John Brown  TrasactId: 1088dfe8-b84e-3625  PayDate:  10/11/2016
Category     Amount
Cat1          22.30   
Cat2          20.30   
Cat3          20.40
Total         63.00

My attempt shows all the 15+ payment categories and 0 amount showing at categories where no payments were made. eg

Name: John Brown  TrasactId: 1088dfe8-b84e-3625  PayDate:  10/11/2016
    Category     Amount
    Cat1          22.30   
    Cat2          20.30   
    Cat3          20.40
    Cat4           0
    Cat5           0
    Cat6           0
    Cat7           0
    ....           ..
    Cat15          0
    Total         63.00

What I need: I want to show only categories where payments were made to

public class MemberPaymentVM // for the table rows
{
    public MemberPaymentVM(int categoryCount)
    {
        Amounts = new List<decimal>(new decimal[categoryCount]);
    }
    public string Name { get; set; }
    public List<decimal> Amounts { get; set; }
    public decimal Total { get; set; }
    public string TransactId { get; set; }
    public DateTime PayDate  { get; set; }
}
public class MonthPaymentsVM
{
    [DisplayFormat(DataFormatString = "{0:MMMM yyyy}"]
    public DateTime Date { get; set; }
    public IEnumerable<string> Categories { get; set; }
    public List<MemberPaymentVM> Payments { get; set; }
}

public ActionResult YearReceipt(int year, int MemberId)
        {
            DateTime startdate = DateTime.Now;
            DateTime enddate = DateTime.Now;    
            DateTime date = DateTime.Now;
            if (year > 0 && MemberId>0)
            {
               startdate = (new DateTime(year - 1, 4, 1));
               enddate = (new DateTime(year, 3, 31));

               // date = (new DateTime(year, month, 1));
            }

            var filtered = db.Payments.ToList().Where(x => x.PayDate >= startdate && x.PayDate <= enddate && x.POK && x.MemberId==MemberId);

            var grouped = filtered.GroupBy(x => new { member = x.MemberId, category= x.Category.ID, txn = x.TransactId, pd = x.PayDate }).Select(x => new
            {
                Member = x.First().Member,
                Category = x.First().Category,
                Txn = x.First().TransactId,
                Pd = x.First().PayDate,
                Amount = x.Sum(y => y.Amount)
            });


            var data = grouped.GroupBy(x => x.Transact);
            var categories = db.Categories;
            var categoryCount = categories.Count();
            var categoryIDs = categories.Select(x => x.CategoryId).ToList();

            var model = new MonthPaymentsVM()
            {
                Date = date,
                Categories = categories.Select(x => x.CategoryName),
                Payments = new List<MemberPaymentVM>()
            };

            foreach (var group in data)
            {
                MemberPaymentVM payment = new MemberPaymentVM(categoryCount);
                var member = group.First().Member;
                var txind = group.First().Txn;
                var pd = group.First().Pd;
                payment.Name = member.Name;
                payment.TransactId = txind;
                payment.PayDate = pd;


                foreach (var item in group)
                {
                    int index = categoryIDs.IndexOf(item.Category.CategoryId);

                    payment.Amounts[index] = item.Amount;
                    payment.Total += item.Amount;
                }
                model.Payments.Add(payment);
            }



            return View(model);
        }

View

@model Paymaster.ViewModel.MonthPaymentsVM
@foreach (var item in Model.Payments)
{

<span> Name:@item.Name 
 Pay Date: @item.PDate.ToString("dd/MM/yyyy")
 TransactId: @item.TxnId </span>


<span class="left2col">Donation Category</span> <span class="right2col">Amount</span>
<span class="left2col">                                                              
@foreach (var category in Model.Categories)
{
@category       
}                                                         
</span>                                                                 
<span class="right2col">@foreach (var amount in item.Amounts)
{
@amount
}
</span>                                                            
<span class="left2col">Total:</span> <span class="right2col"> @item.Total.ToString("c")</span>


}

Answer 1

Based on the view you want to display, your models are incorrect and they need to be

public class MemberPaymentVM
{
    public string MemberName{ get; set; }
    public string TransactId { get; set; }
    [DisplayFormat(DataFormatString = "{0:d}")]
    public DateTime PayDate { get; set; }
    public IEnumerable<CategoryAmountsVM> Payments { get; set; }
    [DisplayFormat(DataFormatString = "{0:C}")]
    public decimal TotalAmount { get; set; }
}
public class CategoryAmountsVM
{
    public string Category { get; set; }
    [DisplayFormat(DataFormatString = "{0:C}")]
    public decimal Amount { get; set; }
}

Then you need to remove the Category from the grouping since you want to display each category for the group defined by Member, Transaction and Date.

The code in your controller method to generate the model needs to be

// Use .ToList() at the end so you do not load all records into memory
var filtered = db.Payments.Where(x => x.PayDate >= startdate && x.PayDate <= enddate && x.POK && x.MemberId==MemberId).ToList();

var model = filtered.GroupBy(x => new { member = x.MemberId, txn = x.TransactId, pd = x.PayDate })
    .Select(x => new MemberPaymentVM()
    {
        MemberName = x.First().Member.MemberName,
        TransactId = x.Key.txn,
        PayDate = x.Key.pd,
        TotalAmount = x.Sum(y => y.Amount),
        Payments = x.Select(y => new CategoryAmountsVM()
        {
            CategoryId = y.Category.CategoryName,
            Amount = y.Amount
        })
    });
return View(model);

Then in your view

@foreach(var item in Model)
{
    <div>
        <span>@item.MemberName</span>
        <span>@item.TransactId</span>
        <span>@Html.DisplayFor(m => item.PayDate)</span>
    </div>
    foreach(var payment in item.Payments)
    {
        <div>
            <span>@payment.Category</span>
            <span>@Html.DisplayFor(m => payment.Amount)</span>
        </div>
    }
    <div>
        <span>Total</span>
        <span>@Html.DisplayFor(m => item.TotalAmount)</span>
    <div>
}