Linq查询以将某人在一段时间内的付款收据分组 [英] Linq query to group payment receipts for a person over a period
问题描述
我需要帮助来构造一个linq查询，以显示按人员和TransactId分组的付款. MemberId和Year将通过查询字符串传递给控制器.
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.
我有这些型号
Member(MemberId,MemberName)
Payment(PayId, MemberId[foreignkey],TransactId, PayDate, Amount,CategoryId[foreignkey])
Category(CategoryId, CategoryName)
希望获得如下输出:John可以进行两次或更多次不同的付款(无限制，但提取报告的时间为12个月)，每笔付款都具有唯一的带有不同付款类别的transactId.
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.
因此在数据库付款中如下所示:对于30/12/2016，Cat1，Cat2，Cat3将是单独的行，但是具有相同的日期，trasactId和memberId.
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
我的尝试显示了所有15种以上的付款类别，0种金额显示了未付款的类别. 例如
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);
}
查看
@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>
}
推荐答案
基于您要显示的视图，您的模型不正确，需要将其设为
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; }
}
然后，您需要从分组中删除Category，因为您要显示成员"，交易"和日期"定义的分组的每个类别.
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);
然后您认为
@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>
}
这篇关于Linq查询以将某人在一段时间内的付款收据分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
