最近在寫樞紐分析表查詢，先筆記一下，比較常用的是固定的寫法如下：
public class HomeController : Controller
{
public List<Report> reports = new List<Report>()
{
new Report("001","案件1","E1","張三"),
new Report("001","案件1","E1","張三"),
new Report("001","案件1","E2","李四"),
new Report("001","案件1","E3","李五"),
new Report("002","案件2","E1","張三"),
new Report("002","案件2","E1","張三"),
new Report("003","案件3","E3","李五"),
new Report("003","案件3","E3","李五"),
};
public ActionResult LinqToPivotDemo()
{
#region 固定樞紐分析表
var staticPivot = reports.GroupBy(r => r.CaseId)
.Select(r => new
{
CaseId = r.Key,
R001 = r.Where(e => e.EmpId == "E1").Count(),
R002 = r.Where(e => e.EmpId == "E2").Count(),
R003 = r.Where(e => e.EmpId == "E3").Count(),
});
#endregion
return View();
}
}
但是以上有一個狀況是如果遇到動態的話，就沒辦法滿足這個需求了，因為每一次都要改一次程式，最近筆者臨時接到這個需求，且急案非常重要，需求單位時程壓很緊，筆者在思考這種動態樞紐分析表，怎麼設計。
先前看一下https://blog.darkthread.net/blog/linq-pivot/，動態做法還是看不懂，後來自己用拆開外加陣列處理掉。
來看看這次需求
| 案件編號 | 案件名稱 | 張三 | 李四 | 趙五 | 合計 |
|---|---|---|---|---|---|
| 1 | 案件1 | 2 | 1 | 1 | 4 |
| 2 | 案件2 | 2 | 0 | 0 | 2 |
| 3 | 案件3 | 0 | 0 | 2 | 2 |
| 合計 | 4 | 1 | 3 | 8 |
筆者會考慮用土炮的方式，設計思路如下：
- 先把上方的部分設計一個ColViewModel，採用GroupBy方式變成動態設計。
- 左方也是用GroupBy方式。
- 再用Where方式逐筆逐筆去統計，並右側加總。
- 最後再開始用陣列方式把底下加總起來。
第一步：資料夾新增ViewModel並新增ColViewModel、DataViewModel、EmpViewModel
namespace WebApplication6.ViewModel
{
public class ColViewModel
{
public string ColId { get; set; }
public string ColName { get; set; }
public ColViewModel(string colId, string colName)
{
ColId = colId;
ColName = colName;
}
}
}
namespace WebApplication6.ViewModel
{
public class DataViewModel
{
public string CaseId { get; set; }
public string CaseName { get; set; }
public int HorizontalTotalCnt { get; set; }
public List<EmpViewModel> Emps { get; set; }
public List<int> DetailTotals { get; set; }
}
}
namespace WebApplication6.ViewModel
{
public class EmpViewModel
{
public string EmpId { get; set; }
public string EmpName { get; set; }
}
}
先進行動態標頭處理，這樣就可以無懼人員組織變動狀況下，可隨意的動態處理
//先刻動態標頭
var cols = new List<ColViewModel>();
cols.Add(new ColViewModel("CaseId", "案件編號"));
cols.Add(new ColViewModel("CaseName", "案件名稱"));
var empCols = reports.GroupBy(emp => emp.EmpId)
.Select(col => new ColViewModel(col.Key,
col.FirstOrDefault().EmpName))
.OrderBy(emp => emp.ColId);
cols.AddRange(empCols);
var footer = new ColViewModel("Total", "合計");
cols.Add(footer);
第二步驟：進行案件GroupBy
var caseDisc = reports.GroupBy(caseInfo => caseInfo.CaseId)
.Select(data => new DataViewModel
{
CaseId = data.Key,
CaseName = data.FirstOrDefault().CaseName,
}).OrderBy(data => data.CaseId);
第三步驟：接者開始進行資料統計處理與右側加總合計
foreach (var caseItem in caseDisc)
{
var data = new DataViewModel()
{
CaseId = caseItem.CaseId,
CaseName = caseItem.CaseName,
Emps = new List<EmpViewModel>(),
DetailTotals = new List<int>(),
HorizontalTotalCnt = 0,
};
var horizontalTotal = 0;
foreach (var empCol in empCols)
{
var empDetails = reports.Where(report =>
report.CaseId == caseItem.CaseId
&& report.EmpId == empCol.ColId)
.Select(emp => new EmpViewModel
{
EmpId = emp.EmpId,
EmpName = emp.EmpName
}).ToList();
var cnt = empDetails.Count;
data.Emps.AddRange(empDetails);
if (cnt > 0)
{
horizontalTotal += cnt;
data.DetailTotals.Add(cnt);
}
else
{
data.DetailTotals.Add(0);
}
}
//右側加總
data.HorizontalTotalCnt = horizontalTotal;
datas.Add(data);
}
第四步驟：最後再開始進行做垂直加總
if (datas.Any())
{
var rowCnt = datas.Count();
var colCnt = datas.FirstOrDefault().DetailTotals.Count();
//垂直加總
int[,] array = new int[rowCnt, colCnt];
for (int row = 0; row < rowCnt; row++)
{
for (int col = 0; col < colCnt; col++)
array[row, col] = datas[row].DetailTotals[col];
}
for (int col = 0; col < colCnt; col++)
{
int total = 0;
for (int row = 0; row < rowCnt; row++)
total += array[row, col];
colsTotal.Add(total);
}
}
namespace WebApplication6.ViewModel
{
public class ResultViewModel
{
public List<ColViewModel> Cols { get; set; }
public List<DataViewModel> Datas { get; set; }
public List<int> FooterTotals { get; set; }
}
}
整體程式碼
public class HomeController : Controller
{
public List<Report> reports = new List<Report>()
{
new Report("001","案件1","E1","張三"),
new Report("001","案件1","E1","張三"),
new Report("001","案件1","E2","李四"),
new Report("001","案件1","E3","李五"),
new Report("002","案件2","E1","張三"),
new Report("002","案件2","E1","張三"),
new Report("003","案件3","E3","李五"),
new Report("003","案件3","E3","李五"),
};
public ActionResult LinqToPivotDemo()
{
#region 固定樞紐分析表
//var staticPivot = reports.GroupBy(r => r.CaseId)
// .Select(r => new
// {
// CaseId = r.Key,
// R001 = r.Where(e => e.EmpId == "E1").Count(),
// R002 = r.Where(e => e.EmpId == "E2").Count(),
// R003 = r.Where(e => e.EmpId == "E3").Count(),
// }).ToArray();
#endregion
#region 動態樞紐分析表
var result = new ResultViewModel();
var datas = new List<DataViewModel>();
var cols = new List<ColViewModel>();
cols.Add(new ColViewModel("CaseId", "案件編號"));
cols.Add(new ColViewModel("CaseName", "案件名稱"));
var empCols = reports.GroupBy(emp => emp.EmpId)
.Select(col => new ColViewModel(col.Key,
col.FirstOrDefault().EmpName))
.OrderBy(emp => emp.ColId).ToList();
cols.AddRange(empCols);
var footer = new ColViewModel("Total", "合計");
cols.Add(footer);
var colsTotal = new List<int>();
var caseDisc = reports.GroupBy(caseInfo => caseInfo.CaseId)
.Select(data => new DataViewModel
{
CaseId = data.Key,
CaseName = data.FirstOrDefault().CaseName,
}).OrderBy(data => data.CaseId);
foreach (var caseItem in caseDisc)
{
var data = new DataViewModel()
{
CaseId = caseItem.CaseId,
CaseName = caseItem.CaseName,
Emps = new List<EmpViewModel>(),
DetailTotals = new List<int>(),
HorizontalTotalCnt = 0,
};
var horizontalTotal = 0;
foreach (var empCol in empCols)
{
var empDetails = reports.Where(report =>
report.CaseId == caseItem.CaseId
&& report.EmpId == empCol.ColId)
.Select(emp => new EmpViewModel
{
EmpId = emp.EmpId,
EmpName = emp.EmpName
}).ToList();
var cnt = empDetails.Count;
data.Emps.AddRange(empDetails);
if (cnt > 0)
{
horizontalTotal += cnt;
data.DetailTotals.Add(cnt);
}
else
{
data.DetailTotals.Add(0);
}
}
//右側加總
data.HorizontalTotalCnt = horizontalTotal;
datas.Add(data);
}
if (datas.Any())
{
var rowCnt = datas.Count();
var colCnt = datas.FirstOrDefault().DetailTotals.Count();
//垂直加總
int[,] array = new int[rowCnt, colCnt];
for (int row = 0; row < rowCnt; row++)
{
for (int col = 0; col < colCnt; col++)
array[row, col] = datas[row].DetailTotals[col];
}
for (int col = 0; col < colCnt; col++)
{
int total = 0;
for (int row = 0; row < rowCnt; row++)
total += array[row, col];
colsTotal.Add(total);
}
}
result.Cols = cols;
result.Datas = datas;
result.FooterTotals = colsTotal;
return View(result);
}
#endregion
}
@model WebApplication6.ViewModel.ResultViewModel
@{
ViewBag.Title = "LinqToPivotDemo";
}
<h2>動態樞紐分析</h2>
<table class="table">
<thead>
@{
<thead class="thead-dark">
<tr>
@foreach (var col in Model.Cols)
{
<th scope="col">@col.ColName</th>
}
</tr>
</thead>
}
<tbody>
@foreach (var item in Model.Datas)
{
<tr>
<td>@item.CaseId</td>
<td>@item.CaseName</td>
@foreach (var detailTotal in item.DetailTotals)
{
<td>@detailTotal</td>
}
<td>@item.HorizontalTotalCnt</td>
</tr>
}
<tr>
<td>
合計
</td>
<td>
</td>
@{int total = 0; }
@foreach (var item in Model.FooterTotals)
{
total+= item;
<td>
@item
</td>
}
<td>
@total
</td>
</tr>
</tbody>
</table>
