C# LINQ行转列输出年度报表、月度汇总报表、应收明细汇总表|C/S开发框架
报表界面效果
实现逻辑
按月度汇总订单金额、收款金额、未收款金额（余额）
模型：
/// <summary>
/// 应收明细汇总表-数据项
/// </summary>
public class res_ARSummaryItem
{
public string CustomerCode { get; set; }
public string CustomerName { get; set; }
/// <summary>
/// 年
/// </summary>
public int Year { get; set; }
/// <summary>
/// 月
/// </summary>
public int Month { get; set; }
/// <summary>
/// 月份, 如：2023/06
/// </summary>
public string YearMonth { get; set; }
/// <summary>
/// 货币
/// </summary>
public string CurrencyCode { get; set; }
/// <summary>
/// 订单金额
/// </summary>
public decimal AmountOrder { get; set; }
/// <summary>
/// 已收金额
/// </summary>
public decimal AmountPaid { get; set; }
/// <summary>
/// 未收金额
/// </summary>
public decimal AmountBalance { get; set; }
}
DAL 层查询接口：
C#+ LINQ 行转列
/// <summary>
/// 应收明细汇总表(行转列)
/// </summary>
/// <param name="year">年度，报表必选条件</param>
/// <param name="listSource">数据源，行数据</param>
/// <returns></returns>
public List<res_ARSummary> GetARSummaryRowToCol(int year, List<res_ARSummaryItem> listSource)
{
//过来年度数据
listSource = listSource.Where(w => w.Year == year).ToList();
var q = (from a in listSource
group a by new { a.CustomerCode, a.CustomerName, a.CurrencyCode })
.Select(s => new res_ARSummary
{
ID = 0,
CustomerCode = s.Key.CustomerCode,
CustomerName = s.Key.CustomerName,
CurrencyCode = s.Key.CurrencyCode,
M01Amount = s.Where(w => w.Month == 1).Sum(s => s.AmountOrder),
M01Paied = s.Where(w => w.Month == 1).Sum(s => s.AmountPaid),
M01Balance = s.Where(w => w.Month == 1).Sum(s => s.AmountBalance),
M02Amount = s.Where(w => w.Month == 2).Sum(s => s.AmountOrder),
M02Paied = s.Where(w => w.Month == 2).Sum(s => s.AmountPaid),
M02Balance = s.Where(w => w.Month == 2).Sum(s => s.AmountBalance),
M03Amount = s.Where(w => w.Month == 3).Sum(s => s.AmountOrder),
M03Paied = s.Where(w => w.Month == 3).Sum(s => s.AmountPaid),
M03Balance = s.Where(w => w.Month == 3).Sum(s => s.AmountBalance),
M04Amount = s.Where(w => w.Month == 4).Sum(s => s.AmountOrder),
M04Paied = s.Where(w => w.Month == 4).Sum(s => s.AmountPaid),
M04Balance = s.Where(w => w.Month == 4).Sum(s => s.AmountBalance),
M05Amount = s.Where(w => w.Month == 5).Sum(s => s.AmountOrder),
M05Paied = s.Where(w => w.Month == 5).Sum(s => s.AmountPaid),
M05Balance = s.Where(w => w.Month == 5).Sum(s => s.AmountBalance),
M06Amount = s.Where(w => w.Month == 6).Sum(s => s.AmountOrder),
M06Paied = s.Where(w => w.Month == 6).Sum(s => s.AmountPaid),
M06Balance = s.Where(w => w.Month == 6).Sum(s => s.AmountBalance),
M07Amount = s.Where(w => w.Month == 7).Sum(s => s.AmountOrder),
M07Paied = s.Where(w => w.Month == 7).Sum(s => s.AmountPaid),
M07Balance = s.Where(w => w.Month == 7).Sum(s => s.AmountBalance),
M08Amount = s.Where(w => w.Month == 8).Sum(s => s.AmountOrder),
M08Paied = s.Where(w => w.Month == 8).Sum(s => s.AmountPaid),
M08Balance = s.Where(w => w.Month == 8).Sum(s => s.AmountBalance),
M09Amount = s.Where(w => w.Month == 9).Sum(s => s.AmountOrder),
M09Paied = s.Where(w => w.Month == 9).Sum(s => s.AmountPaid),
M09Balance = s.Where(w => w.Month == 9).Sum(s => s.AmountBalance),
M10Amount = s.Where(w => w.Month == 10).Sum(s => s.AmountOrder),
M10Paied = s.Where(w => w.Month == 10).Sum(s => s.AmountPaid),
M10Balance = s.Where(w => w.Month == 10).Sum(s => s.AmountBalance),
M11Amount = s.Where(w => w.Month == 11).Sum(s => s.AmountOrder),
M11Paied = s.Where(w => w.Month == 11).Sum(s => s.AmountPaid),
M11Balance = s.Where(w => w.Month == 11).Sum(s => s.AmountBalance),
M12Amount = s.Where(w => w.Month == 12).Sum(s => s.AmountOrder),
M12Paied = s.Where(w => w.Month == 12).Sum(s => s.AmountPaid),
M12Balance = s.Where(w => w.Month == 12).Sum(s => s.AmountBalance),
});
var result = q.ToList();
result.ForEach(i =>
{
i.TotalBalance = i.M01Balance + i.M02Balance + i.M03Balance + i.M04Balance + i.M05Balance + i.M06Balance
+ i.M07Balance + i.M08Balance + i.M09Balance + i.M10Balance + i.M11Balance + i.M12Balance;
});
return result;
}
前端页面设计
点击 【Run Designer】 设计表格，添加 12 个 Bands，添加数十个字段，分别对应 12个月的订单、收款、余额。
扫一扫加作者微信
在线客服
