mvc5+ef+ adminlte教程16-2：两张表进行汇总、对比分析（以欠费清单管理为例
接着上一章。
因为一效直在弄left join的加group by，没整好，所以先把index效果图弄出来。
其中红色为表1中的sum数据，蓝天 要取表2中的数据。
同时点最前面的部门和后面的“详单”都能弹到以部门名或责任人为搜索的清单页面。ArrearsList页面。
1.视图文件
注意视图中还有分页功能。
@model IEnumerable<jsdhh2.Models.NewArreartwo>
<div class="dhhheight1 col-md-12 col-xs-12"></div>
<!--start 右 -->
<div class="col-md-12 col-xs-12 pull-right">
<!-- Content Header (Page header) -->
<section class="content-header">
<h1>
欠费明细中心
<small>当月欠费分析~</small>
</h1>
<ol class="breadcrumb">
<li><a href="#"><i class="fa fa-dashboard"></i> Level</a></li>
<li class="active">Here</li>
</ol>
</section>
<!--end Content Header (Page header) -->
<!--start button -->
<h2></h2>
<div>
</div>
<h2>.</h2>
<table class="table table-hover">
<tr>
<th>
序号
</th>
<th>
用户名
</th>
<th>
号码
</th>
<th>
账户ID
</th>
<th>
联系电话
</th>
<th>
产品
</th>
<th>
套餐
</th>
<th>
当月金额
</th>
<th>
金额
</th>
<th>
账期
</th>
<th>
地址
</th>
<th>
支付类型
</th>
<th>
责任部门
</th>
<th>
责任人
</th>
<th>
备注
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.SerialNumber)
</td>
<td>
@Html.DisplayFor(modelItem => item.UserName)
</td>
<td>
@Html.DisplayFor(modelItem => item.Number)
</td>
<td>
@Html.DisplayFor(modelItem => item.AccountId)
</td>
<td>
@Html.DisplayFor(modelItem => item.ContactNumber)
</td>
<td>
@Html.DisplayFor(modelItem => item.Product)
</td>
<td>
@Html.DisplayFor(modelItem => item.Package)
</td>
<td>
@Html.DisplayFor(modelItem => item.AmountMoney)
</td>
<td>
@Html.DisplayFor(modelItem => item.TotalMoney)
</td>
<td>
@Html.DisplayFor(modelItem => item.Aging)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.DisplayFor(modelItem => item.PayType)
</td>
<td>
@Html.DisplayFor(modelItem => item.ResponsibleDePart)
</td>
<td>
@Html.DisplayFor(modelItem => item.ResponsiblePeople)
</td>
<td>
@Html.DisplayFor(modelItem => item.Bei)
</td>
<td>
@Html.ActionLink("Del", "Delete", new { id = item.Id })
</td>
</tr>
}
</table>
</div>
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
@section Scripts {
}
2.后台的代码。
一是读取的显示代码v
// GET: 欠费用户清单
public ActionResult ArreasList(string seachDepart,string seachstring,int? page)
{
///只要几个数，主要是分类汇总。
ViewBag.seachstring = seachstring;
ViewBag.seachDepart = seachDepart;
if (!string.IsNullOrEmpty(seachstring)&& !string.IsNullOrEmpty(seachDepart))
{
page = 1;
}
int pageNumber = page ?? 1;
int pageSize = 4;
if (!string.IsNullOrEmpty(seachstring))
{
var seachindexlist1 = (from w in db.NewArrearones
join yy in db.NewArreartwos
on w.Number equals yy.Number
select w).Where(x => x.ResponsiblePeople == seachstring).OrderByDescending(x => x.TotalMoney);
return View(seachindexlist1.ToPagedList(pageNumber, pageSize));
}
var seachindexlist = (from w in db.NewArrearones
join yy in db.NewArreartwos
on w.Number equals yy.Number
select w).Where(x => x.ResponsibleDePart == seachDepart).OrderByDescending(x => x.TotalMoney);
return View(seachindexlist.ToPagedList(pageNumber, pageSize));
}
二是导出EXCLE代码
//导出的方法Npoi:有搜索字符串的
public FileResult ToExlceseachArrears(string seachstring, string seachDepart)
{
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
ISheet sheet1 = book.CreateSheet("Sheet1");
//获取list数据，因是最新欠费表，可能需要关联责任人与责任部门，主要原因是只有月初才分配清单。以后只是对比,所以字段全是表1。
List<NewArrearone> listRainInfo = new List<NewArrearone>();
if (!string.IsNullOrEmpty(seachstring))
{
//同时要还取一个WHERE,当责任人的搜索关键字不为空的时候，为空则是部门
var bijiao = ((from m in db.NewArrearones
join mr in db.NewArreartwos
on m.Number equals mr.Number
select m).Where(x => x.ResponsiblePeople == seachstring)).ToList();
listRainInfo = bijiao;
}
else
{
var bijiao1 = ((from m in db.NewArrearones
join mr in db.NewArreartwos
on m.Number equals mr.Number
select m).Where(x => x.ResponsibleDePart == seachDepart)).ToList();
listRainInfo = bijiao1;
}
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//从第2列开始写，第一列是guid.ID
row1.CreateCell(1).SetCellValue("序号");
row1.CreateCell(2).SetCellValue("用户名");
row1.CreateCell(3).SetCellValue("号码");
row1.CreateCell(4).SetCellValue("账号ID");
row1.CreateCell(5).SetCellValue("联系电话");
row1.CreateCell(6).SetCellValue("产品");
row1.CreateCell(7).SetCellValue("套餐");
row1.CreateCell(8).SetCellValue("当月金额");
row1.CreateCell(9).SetCellValue("总金额");
row1.CreateCell(10).SetCellValue("账龄");
row1.CreateCell(11).SetCellValue("地址");
row1.CreateCell(12).SetCellValue("付费类型");
row1.CreateCell(13).SetCellValue("责任部门");
row1.CreateCell(14).SetCellValue("责任人");
row1.CreateCell(15).SetCellValue("备注哟");
//将数据逐步写入sheet1各个行
for (int i = 0; i < listRainInfo.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
//也从第二列开始写
rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].SerialNumber.ToString());
rowtemp.CreateCell(2).SetCellValue(listRainInfo[i].UserName.ToString());
rowtemp.CreateCell(3).SetCellValue(listRainInfo[i].Number.ToString());
rowtemp.CreateCell(4).SetCellValue(listRainInfo[i].AccountId.ToString());
rowtemp.CreateCell(5).SetCellValue(listRainInfo[i].ContactNumber.ToString());
rowtemp.CreateCell(6).SetCellValue(listRainInfo[i].Product.ToString());
rowtemp.CreateCell(7).SetCellValue(listRainInfo[i].Package.ToString());
rowtemp.CreateCell(8).SetCellValue(listRainInfo[i].AmountMoney);
rowtemp.CreateCell(9).SetCellValue(listRainInfo[i].TotalMoney);
rowtemp.CreateCell(10).SetCellValue(listRainInfo[i].Aging);
rowtemp.CreateCell(11).SetCellValue(listRainInfo[i].Address.ToString());
rowtemp.CreateCell(12).SetCellValue(listRainInfo[i].PayType.ToString());
rowtemp.CreateCell(13).SetCellValue(listRainInfo[i].ResponsibleDePart.ToString());
rowtemp.CreateCell(14).SetCellValue(listRainInfo[i].ResponsiblePeople.ToString());
rowtemp.CreateCell(15).SetCellValue(listRainInfo[i].Bei.ToString());
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", seachstring+ seachDepart+"最新欠费表.xls");
}
测试导出功能正常，还有删除，我默认的没有管他
8,现在来实现index汇总页面的功能。
注意代码中的.tolist()必须要呀，要不然要出错，在select new前必须是tolist()
代码。有点复杂，就不一一说明了，有了注解，我笨，所以用四步来实现。
// GET: NewArrearones
public ActionResult Index()
{
///下面这个先用左join接两个表，因为表2可能是空表，所以对空间中几个int,double字段进行赋值。同时对关键的二个主体字段赋值，用于再次group by
var y = from m in db.NewArrearones.ToList()
join mr in db.NewArreartwos.ToList() on m.Number equals mr.Number into newTable
from item in newTable.DefaultIfEmpty(new NewArreartwo { AmountMoney = 0, TotalMoney = 0, Aging = 0, ResponsiblePeople = m.ResponsiblePeople, ResponsibleDePart = m.ResponsibleDePart })
select item;
//以Y表进行group by
var yy =from mm in y group mm by mm.ResponsiblePeople into mg
select new ArrearsView
{
ResponsibleDePart = mg.FirstOrDefault().ResponsibleDePart,
ResponsiblePeople = mg.Key,
StartAmount = 0,
NewAmount = mg.Sum(m => m.Aging),
StartMoney = 0,
NewMoney = mg.Sum(m => m.AmountMoney)
};
////对表1进行GROU 到NEW 的ArrearsVIew型型。
var xx = from x in db.NewArrearones.ToList()
group x by x.ResponsiblePeople into g
select new ArrearsView
{
ResponsibleDePart = g.FirstOrDefault().ResponsibleDePart,
ResponsiblePeople = g.Key,
StartAmount = g.Sum(m => m.Aging),
NewAmount = 0,
StartMoney = g.Sum(m => m.AmountMoney),
NewMoney = 0
};
//两张表连接
var arrrarviewlist = (from xxyy in yy
join xxxy in xx on xxyy.ResponsiblePeople equals xxxy.ResponsiblePeople
select new ArrearsView
{
ResponsibleDePart = xxxy.ResponsibleDePart,
ResponsiblePeople = xxxy.ResponsiblePeople,
StartAmount = xxxy.StartAmount,
NewAmount =xxyy.NewAmount,
StartMoney = xxxy.StartMoney,
NewMoney = xxyy.NewMoney
}).OrderBy(my => my.ResponsibleDePart); ;
return View(arrrarviewlist);
//return View(await db.NewArrearones.ToListAsync());
}
前台页面
@model IEnumerable<jsdhh2.ViewModels.ArrearsView>
<div class="dhhheight1 col-md-12 col-xs-12"></div>
<!--start 左 -->
<div class="col-md-3 hidden-xs">
<div class="box box-solid">
<div class="box-header with-border">
<h3 class="box-title">分析版块</h3>
<div class="box-tools">
<button type="button" class="btn btn-box-tool" data-widget="collapse">
<i class="fa fa-minus"></i>
</button>
</div>
</div>
<div class="box-body no-padding" style="display: block;">
<ul class="nav nav-pills nav-stacked">
<li class="active">
<a href="~/NewArrearones/Index">
<i class="fa fa-inbox"></i> 欠费管理
<span class="label label-primary pull-right"></span>
</a>
</li>
<li class="dhhleft"><a href="~/NewArrearones/arreaslist"><i class="fa fa-circle-o"></i> 搜索值</a></li>
<li class="active">
<a href="~/Emails/Index">
<i class="fa fa-inbox"></i> 版块2
<span class="label label-primary pull-right"></span>
</a>
</li>
<li><a href="~/Emails/SeedIndex"><i class="fa fa-envelope-o"></i> 已发邮箱</a></li>
<li><a href="~/Emails/Create"><i class="fa fa-file-text-o"></i> 发邮件</a></li>
</ul>
</div>
<!-- /.box-body -->
</div>
</div>
<!--start 右 -->
<div class="col-md-9 col-xs-12 pull-right">
<!-- Content Header (Page header) -->
<section class="content-header">
<h1>
欠费管理中心
<small>当月欠费分析~</small>
</h1>
<ol class="breadcrumb">
<li><a href="#"><i class="fa fa-dashboard"></i> Level</a></li>
<li class="active">Here</li>
</ol>
</section>
<!--end Content Header (Page header) -->
<!--start button -->
<h2></h2>
<div>
@Html.ActionLink("导出月初数Excle", "ToExlceArrearone", "", new { @class = "btn btn-primary" }) @Html.ActionLink("导出最新数Excle", "ToExlceArreartwo", "", new { @class = "btn btn-warning" })
</div>
<h2>.</h2>
<table class="table table-hover">
<tr>
<th>
@Html.DisplayNameFor(model => model.ResponsibleDePart)
</th>
<th>
@Html.DisplayNameFor(model => model.ResponsiblePeople)
</th>
<th>
@Html.DisplayNameFor(model => model.StartAmount)
</th>
<th>
@Html.DisplayNameFor(model => model.NewAmount)
</th>
<th>
@Html.DisplayNameFor(model => model.StartMoney)
</th>
<th>
@Html.DisplayNameFor(model => model.NewMoney)
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
<a class="btn btn-primary btn-sm" href="~/NewArrearones/ArreasList?seachstring=@item.ResponsibleDePart" role="button">@Html.DisplayFor(modelItem => item.ResponsibleDePart)</a>
</td>
<td>
@Html.DisplayFor(modelItem => item.ResponsiblePeople)
</td>
<td>
@Html.DisplayFor(modelItem => item.StartAmount)
</td>
<td>
@Html.DisplayFor(modelItem => item.NewAmount)
</td>
<td>
@Html.DisplayFor(modelItem => item.StartMoney)
</td>
<td>
@Html.DisplayFor(modelItem => item.NewMoney)
</td>
<td>
<a class="btn btn-primary btn-sm" href="~/NewArrearones/ArreasList?seachstring=@item.ResponsiblePeople" role="button">详单</a>
</td>
</tr>
}
</table>
<hr />
<!-- Main content -->
@*Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @Model.PageCount
@Html.PagedListPager(Model, page => Url.Action("Index",
new { page, emailState = ViewBag.emailState, SearchString = ViewBag.SearchString }))*@
</div>
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
@section Scripts {
}
效果
