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>
}
See Question&Answers more detail:
os