>我需要帮助来构造一个linq查询来显示按人员和TransactId分组的付款。成员 ID 和年份将通过查询字符串传递给控制器。
我有这些模型
Member(MemberId,MemberName)
Payment(PayId, MemberId[foreignkey],TransactId, PayDate, Amount,CategoryId[foreignkey])
Category(CategoryId, CategoryName)
查看如下所示的输出:John 可能进行了两次或多次不同的付款(无限制,但报告会获取 12 个月),每笔付款都会获得具有不同付款类别的唯一 TransactId。
因此,在数据库付款中如下所示:对于 30/12/2016,Cat1、Cat2、Cat3 将是单独的行,但日期、trasactId 和 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 金额显示在未付款的类别中。例如
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
我需要什么:我只想显示付款的类别
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>
}
根据要显示的视图,您的模型不正确,需要
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
,因为您希望显示由成员,交易和日期定义的组的每个类别。
控制器方法中用于生成模型的代码需要
// 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>
}