ASP.NET MVC搭建项目后台UI框架—7、统计报表

1. ASP.NET MVC搭建项目后台UI框架—1、后台主框架
2. ASP.NET MVC搭建项目后台UI框架—2、菜单特效
3. ASP.NET MVC搭建项目后台UI框架—3、面板折叠和展开
4. ASP.NET MVC搭建项目后台UI框架—4、tab多页签支持
5. ASP.NET MVC搭建项目后台UI框架—5、Demo演示Controller和View的交互
6. ASP.NET MVC搭建项目后台UI框架—6、客户管理（添加、修改、查询、分页）
7. ASP.NET MVC搭建项目后台UI框架—7、统计报表
8. ASP.NET MVC搭建项目后台UI框架—8、将View中选择的数据行中的部分数据传入到Controller中
9. ASP.NET MVC搭建项目后台UI框架—9、服务器端排序

本节，我将通过一个Demo，演示Datatables 和ASP.NET MVC的完美结合，可以这么说，如果这样的界面都能做出来，后台系统90%的界面功能都可以开发出来了。

用jquery Datatables 来开发确实是件比较蛋疼的事情（和Jquery EasyUI、MiniUI、ExtJs相比），用其它的第三方UI框架来实现相同的功能真是非常非常的简单，可是使用Datatables却是那么的吃力，至少我这么觉得，可能是因为我对这个控件使用得还不够纯熟。在官网，datatables默认使用的是bootstraps的样式，这里我已经把样式重写了一部分。

看见公司原有的系统，同样是使用ASP.NET MVC做的，在页面随便点击个东东，整个界面就刷新了，刷得我自己都受不了，更别指望固定表头啊什么什么的了，完全不存在用户体验啊！于是我就自己写了UI框架（也可以说是组装，但是我重写了许多东西）。

技术点：1、服务器端分页。2、查询（模糊查询）3、界面操作刷新后依旧保留当前分页 4、固定表头、表尾 5、动态控制列的隐藏和显示 6、全选、反选（数据行中复选框全部选中时，全选按钮自动选中，我发现很多程序员这个功能一直没做，可是说是bug么？）  7、服务器排序（功能我已经开发出来了，但是这里我没有写上去，这个网上我还没有看到实现的Demo） 8、特殊字段标红显示 9、滑动变色 10、单击行选中变色 ....

先看下效果：

点击图片，折叠或展开列

新建Reconciliation控制器

using Core.CostFlow;
using Core.Filters;
using Core.Reconciliation;
using Data.Reconciliation;
using ProjectBase.Utils;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ProjectBase.Utils.Entities;

namespace Site.Controllers
{
    public class ReconciliationController : Controller
    {
        //运单对账
        public ActionResult WayBill()
        {
            return View();
        }

        [HttpPost]
        public JsonResult WayBillList(WayBillReconciliationFilter filter)
        {
            DataTablesRequest parm = new DataTablesRequest(this.Request);    //处理对象
            int pageIndex = parm.iDisplayStart / parm.iDisplayLength;
            filter.PageIndex = pageIndex;    //页索引
            filter.PageSize = parm.iDisplayLength;    //页行数
            var DataSource = WayBillReconciliation.GetByFilter(filter) as WRPageOfList<WayBillReconciliation>;

            int i = parm.iDisplayLength * pageIndex;

            List<WayBillReconciliation> queryData = DataSource.ToList();
            var data = queryData.Select(u => new
           {
               Index = ++i, //行号
               ID = u.ID,
               CusName = u.CusName, //客户简称
               PostingTime =u.PostingTime==null?string.Empty: u.PostingTime.Value.ToStringDate(),//收寄日期
               ExpressNo = u.ExpressNo, //邮件号
               BatchNO = u.LoadBillNum, //提单号
               Weight = u.Weight==null ? 0m : u.Weight / 100, //重量
               WayBillFee = u.WayBillFee, //邮资
               ProcessingFee = u.ProcessingFee, //邮政邮件处理费
               InComeWayBillFee = u.ExpressFee, //客户运费
               InComeOprateFee = u.OperateFee, //客户操作费
               WayBillMargins = u.WayBillProfit, //运费毛利
               TotalMargins = u.ExpressFee + u.OperateFee + u.InComeOtherFee-(u.WayBillFee + u.ProcessingFee + u.CostOtherFee), //总毛利
               Margin = (u.ExpressFee + u.OperateFee + u.InComeOtherFee == 0 ? 0m : (u.ExpressFee + u.OperateFee + u.InComeOtherFee - (u.WayBillFee + u.ProcessingFee + u.CostOtherFee)) / (u.ExpressFee + u.OperateFee + u.InComeOtherFee) * 100) + "%", //毛利率 毛利率=(总收入-总的支出的成本)/总收入*100% 
                           
               ReconcileDate=DateTime.Now.ToString("yyyy-MM"), //对账日期
               CostOtherFee = u.CostOtherFee, //成本 其他费用
               CostTotalFee = u.WayBillFee + u.ProcessingFee+u.CostOtherFee, //成本 总费用
               CostStatus = u.CostStatus.ToChinese(),  //成本 状态
               InComeOtherFee = u.InComeOtherFee, //收入 其他费用
               InComeTotalFee = u.ExpressFee + u.OperateFee+u.InComeOtherFee, //收入 总费用
               InComeStatus = u.InComeStatus.ToChinese(),  //收入 状态
               TotalStatus=""
           });
            decimal totalProfit = 0m;      //总毛利求和
            //构造成Json的格式传递
            var result = new
            {
                iTotalRecords = DataSource.Count,
                iTotalDisplayRecords = DataSource.RecordTotal,
                data = data,
                TotalWeight = DataSource.StatModelBy.TotalWeight/100,
                TotalWayBillFee = DataSource.StatModelBy.TotalWayBillFee,
                               TotalProcessingFee = DataSource.StatModelBy.TotalProcessingFee,
                               TotalExpressFee = DataSource.StatModelBy.TotalExpressFee,
                               TotalOperateFee = DataSource.StatModelBy.TotalOperateFee,
                               SumWayBillProfit = DataSource.StatModelBy.TotalWayBillProfit,
                               SumTotalProfit = totalProfit
            };
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 提单对账
        /// </summary>
        /// <returns></returns>
        public ActionResult LoadBill()
        {
            return View();
        }

        public JsonResult LoadBillList()
        {
            return Json(null, JsonRequestBehavior.AllowGet);
        }
    }
}

新建WayBill视图

@{
    ViewBag.Title = "运费对账";
}
<style type="text/css">
    .numberColor {
        color:red;
    }
</style>
<link href="~/libs/DataTables-1.10.6/media/css/jquery.dataTablesNew.css" rel="stylesheet" />
<script src="~/libs/DataTables-1.10.6/media/js/jquery.dataTables.min.js"></script>
<script src="~/Scripts/DataTablesExt.js"></script>
<script src="~/libs/My97DatePicker/WdatePicker.js"></script>
<script type="text/javascript">
    $(function () {
        var h = $(document).height() - 312;
        var table = $("#table_local").dataTable({
            bProcessing: true,
            "scrollY": h,
            "scrollCollapse": "true",
            "dom": 'tr<"bottom"lip><"clear">',
            "bServerSide": true,                    //指定从服务器端获取数据  
            sServerMethod: "POST",
            showRowNumber:true,
            sAjaxSource: "@Url.Action("WayBillList", "Reconciliation")",
            "initComplete": function (data, args) {
                //getTotal(args);
                var arr = new Array(7,8,9,12,13,14);
                controlColumnShow(table, arr,false);
            },
            "fnServerParams": function (aoData) {  //查询条件
                aoData.push(
                    { "name": "CusShortName", "value": $("#CusShortName").val() },
                    { "name": "LoadBillNum", "value": $("#LoadBillNum").val() },
                    { "name": "ExpressNo", "value": $("#ExpressNo").val() },
                    { "name": "PostingTime", "value": $("#PostingTime").val() },
                    { "name": "PostingTimeTo", "value": $("#PostingTimeTo").val() }
                    // ,{ "name": "PostingTimeTo", "value": $("#sltMargin").val() }
                     );
            },
            //跟数组下标一样，第一列从0开始，这里表格初始化时，第四列默认降序
            "order": [[ 2, "asc" ]],
            columns: [
               {
                   "data": "ID", orderable: false,
                   "render": function (data, type, row, meta) {
                       return " <input id='cbx" + data + "' type='checkbox' onclick='controlSelectAll(" + data + ")' class='cbx' value='" + data + "'/>  " + row.Index;
                   }
               },                                                      
                { "data": "ReconcileDate",visible:false},//对账日期
                { "data": "CusName" }, //客户名称
                { "data": "PostingTime"},//收寄日期
                { "data": "ExpressNo", orderable: false }, //邮件号
                { "data": "BatchNO"},//提单号
                { "data": "Weight"},//重量
                { "data": "WayBillFee"},//邮政邮资
                { "data": "ProcessingFee" },//邮政邮件处理费
                { "data": "CostOtherFee"},//其它费用
                { "data": "CostTotalFee" },//总成本
                { "data": "CostStatus", orderable: false },//状态
                { "data": "InComeWayBillFee" },//客户运费
                { "data": "InComeOprateFee"},//客户操作费
                { "data": "InComeOtherFee"},//其它费用
                { "data": "InComeTotalFee" },//总收入
                { "data": "InComeStatus", orderable: false },//状态
                 {
                     "data": "WayBillMargins", orderable: false, "render": function (data, type, row, meta) { //运费毛利
                         var css = "";
                         if (data < 0) {
                             css=" class='numberColor'";
                         }
                         var re = "<div"+css+">"+data+"</div>";
                         return re;
                     }
                 },
                  {
                      "data": "TotalMargins", orderable: false, "render": function (data, type, row, meta) { //总毛利
                          var css = "";
                          if (data < 0) {
                              css = " class='numberColor'";
                          }
                          var re = "<div" + css + ">" + data + "</div>";
                          return re;
                      }
                  },
                { "data": "Margin", orderable: false },//毛利率
                { "data": "TotalStatus", orderable: false },
                 {
                     "data": "ID", orderable: false, width: "80", "render": function (data, type, row, meta) { //操作
                         var re = "<div style='text-align:center'><a style='visibility:visible' onclick='openDetail(" + data + ")'>详情</a>&nbsp;&nbsp;";
                         return re;
                     }
                 }
            ],
            paging: true,//分页
            ordering: true,//是否启用排序
            searching: true,//搜索
            language: {
                "sProcessing": "处理中...",
                lengthMenu: '每页显示：<select class="form-control input-xsmall">' + '<option value="10">10</option>' + '<option value="20">20</option>' + '<option value="30">30</option>'
                    + '<option value="50">50</option>' + '<option value="100">100</option>' + '<option value="150">150</option>' + '<option value="200">200</option>' + '<option value="250">250</option>',//左上角的分页大小显示。
                search: '<span class="label label-success">搜索：</span>',//右上角的搜索文本，可以写html标签

                paginate: {//分页的样式内容。
                    previous: "上一页",
                    next: "下一页",
                    first: "",
                    last: ""
                },

                zeroRecords: "暂无记录",//table tbody内容为空时，tbody的内容。
                //下面三者构成了总体的左下角的内容。
                info: "总共 <span class='pagesStyle'>(_PAGES_) </span>页，显示 _START_ -- _END_ ，共<span class='recordsStyle'> (_TOTAL_)</span> 条",//左下角的信息显示，大写的词为关键字。初始_MAX_ 条 
                infoEmpty: "0条记录",//筛选为空时左下角的显示。
                infoFiltered: ""//筛选之后的左下角筛选提示，
            },
            pagingType: "full_numbers"//分页样式的类型
      });
        //设置选中行样式
        $('#table_local tbody').on('click', 'tr', function () {
            if ($(this).hasClass('selected')) {
                $(this).removeClass('selected');
            }
            else {
                table.$('tr.selected').removeClass('selected');
                $(this).addClass('selected');
            }
        });
        //展开折叠列
        $("#imgIncome").click(function () {
            var url = $("#imgIncome").attr("src");
            var arr = new Array(7, 8, 9);
            if (url == "/images/icon_9.png") {
                controlColumnShow(table, arr, true);
                $("#imgIncome").attr("src", "/images/icon_10.png");
            }
            else {
                controlColumnShow(table, arr, false);
                $("#imgIncome").attr("src", "/images/icon_9.png");
            }
            
        });
        //收入展开折叠
        $("#imgCost").click(function () {
            var url = $("#imgCost").attr("src");
            var arr = new Array(12, 13, 14);
            if (url == "/images/icon_9.png") {
                controlColumnShow(table, arr, true);
                $("#imgCost").attr("src", "/images/icon_10.png");
            }
            else {
                controlColumnShow(table, arr, false);
                $("#imgCost").attr("src", "/images/icon_9.png");
            }
        });
    });
    function reloadList() {
        var tables = $('#table_local').dataTable().api();//获取DataTables的Api，详见 http://www.datatables.net/reference/api/
        tables.ajax.reload(function () {
            var json = tables.context[0].json;
            getTotal(json);
        }, false);
    }
    //统计
    function getTotal(json) {
        if (json) {
            if (json.TotalWeight) {
                $("#spnTotalWeight").html(json.TotalWeight);
                $("#spnTotalWayBillFee").html(json.TotalWayBillFee);
                $("#spnTotalProcessingFee").html(json.TotalProcessingFee);
                $("#spnTotalExpressFee").html(json.TotalExpressFee);
                $("#spnTotalOperateFee").html(json.TotalOperateFee);
                $("#spnSumWayBillProfit").html(json.SumWayBillProfit);
                $("#spnSumTotalProfit").html(json.SumTotalProfit);
            }
        }
    }
    //控制指定定列的隐藏和显示（table，列索引数组，隐藏or显示:true,false)
    function controlColumnShow(table, arr,tag) {
        for (var i = 0; i < arr.length; i++) {
            table.fnSetColumnVis(arr[i],tag);
        }
    }
</script>
<div class="areabx clear">
    @using (Html.BeginForm("List", null, FormMethod.Get, new { @clase = "form-inline", @role = "form" }))
    {
        <div class="areabx_header">@ViewBag.Title</div>
        <ul class="formod mgt10">
            <li><span>客户简称：</span>@Html.TextBox("CusShortName","",new { @class = "trade-time wid153" })</li>
            <li><span>提单号：</span>@Html.TextBox("LoadBillNum","", new { @class = "trade-time" })</li>
        </ul>
        <ul class="formod mgt10">
            <li><span>运单号：</span>@Html.TextBox("ExpressNo","", new { @class = "trade-time wid153" })</li>
            <li><span>收寄日期：</span>@Html.TextBox("PostingTime", "", new { @class = "trade-time wid153", @onClick = "WdatePicker({maxDate:'#F{$dp.$D(\\'PostingTimeTo\\')}'})" })</li>
            <li><span style="text-align:left;width:25px;margin-left:-20px;">—</span>  @Html.TextBox("PostingTimeTo", "", new { @class = "trade-time wid153", @onClick =  "WdatePicker({minDate:'#F{$dp.$D(\\'PostingTime\\')}'})"  })</li>
            <li><span>毛利：</span><select class="trade-time" id="sltMargin"><option value="" selected="selected">全部</option><option value="+">+</option><option value="-">-</option></select></li>
        </ul>
        <div class="botbtbx pdb0">
            <input type="button" value="查询" id="btnSearch" onclick="reloadList();" class="btn btn-primary" />
        </div>
    }
    <div class="tob_box mgt15">
        <table id="table_local" class="display" cellspacing="0" cellpadding="0" border="0" style="width: 100%">
            <thead>
                <tr>
                    <th rowspan="2">
                     <input type='checkbox' id='chkAllColl' onclick='selectAll()' />序号</th>
                    <th rowspan="2">对账日期</th>
                    <th rowspan="2">客户简称</th>
                    <th rowspan="2">收寄日期</th>
                    <th rowspan="2">邮件号</th>
                    <th rowspan="2">提单号</th>
                    <th rowspan="2">重量(kg)</th>
                    <th colspan="5"><span>成本</span><span class="divIncome1"><img id="imgIncome" src="/images/icon_9.png"  alt="收起/展开"/></span></th>
                    <th colspan="5"><span>收入</span><span class="divIncome1"><img id="imgCost" src="/images/icon_9.png" alt="收起/展开"/></span></th>
                    <th colspan="3">毛利</th>
                    <th rowspan="2">状态</th>
                    <th rowspan="2">操作</th>
                </tr>
                <tr>
                    <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>毛利率</th>
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <td>总计</td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td><span id="spnTotalWeight"></span></td>
                    <td><span id="spnTotalWayBillFee"></span></td>
                    <td><span id="spnTotalProcessingFee"></span></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td><span id="spnTotalExpressFee"></span></td>
                    <td><span id="spnTotalOperateFee"></span></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td><span id="spnSumWayBillProfit"></span></td>
                    <td><span id="spnSumTotalProfit"></span></td>
                    <td></td>
                    <td></td>
                    <td></td>
                </tr>
            </tfoot>
        </table>
    </div>
</div>

这里面  table.fnSetColumnVis(arr[i], tag);这行代码控制列动态隐藏和展示的时候，会重新加载数据，可以在后面加一个false参数，取消刷新。 如： table.fnSetColumnVis(arr[i], tag,false);

请求参数封装类DataTablesRequest，这个类是从冠军的博客下载的，它主要用于解析datatables的请求参数，由于datatables支持多列排序，所以比较复杂。下载的这个类有点问题，那就是获取的排序方式一直是asc，于是我进行了修改，修改后的代码如下：

/* ==============================================================================
   * 功能描述：DataTablesRequest  
   * 创 建 者：Zouqj
   * 创建日期：2015/4/21 17:47:35
   ==============================================================================*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace ProjectBase.Utils
{
    // 排序的方向
    public enum SortDirection
    {
        Asc,    // 升序
        Desc    // 降序
    }

    // 排序列的定义
    public class SortColumn
    {
        public int Index { get; set; }                  // 列序号
        public SortDirection Direction { get; set; }    // 列的排序方向
    }

    // 列定义
    public class Column
    {
        public string Name { get; set; }        // 列名
        public bool Sortable { get; set; }      // 是否可排序
        public bool Searchable { get; set; }    // 是否可搜索
        public string Search { get; set; }      // 搜索串
        public bool EscapeRegex { get; set; }   // 是否正则
    }

    public class DataTablesRequest
    {
        private HttpRequestBase request;        // 内部使用的 Request 对象

        public DataTablesRequest(System.Web.HttpRequestBase request)    // 用于 MVC 模式下的构造函数
        {
            this.request = request;

            this.echo = this.ParseStringParameter(sEchoParameter);
            this.displayStart = this.ParseIntParameter(iDisplayStartParameter);
            this.displayLength = this.ParseIntParameter(iDisplayLengthParameter);
            this.sortingCols = this.ParseIntParameter(iSortingColsParameter);

            this.search = this.ParseStringParameter(sSearchParameter);
            this.regex = this.ParseStringParameter(bRegexParameter) == "true";

            // 排序的列
            int count = this.iSortingCols;
            this.sortColumns = new SortColumn[count];
            for (int i = 0; i < count; i++)
            {
                SortColumn col = new SortColumn();
                col.Index = this.ParseIntParameter(string.Format("iSortCol_{0}", i));

                if (this.ParseStringParameter(string.Format("sSortDir_{0}", i)) == "desc")
                {
                    col.Direction = SortDirection.Desc;
                }
                else
                {
                    col.Direction = SortDirection.Asc;
                }
                this.sortColumns[i] = col;
            }

            this.ColumnCount = this.ParseIntParameter(iColumnsParameter);

            count = this.ColumnCount;
            this.columns = new Column[count];

            if(this.ParseStringParameter(sColumnsParameter)==null||!this.ParseStringParameter(sColumnsParameter).Contains(','))
            {
                   return;
            }
            string[] names = this.ParseStringParameter(sColumnsParameter).Split(',');

            for (int i = 0; i < count; i++)
            {
                Column col = new Column();
                col.Name = names[i];
                col.Sortable = this.ParseStringParameter(string.Format("bSortable_{0}", i)) == "true";
                col.Searchable = this.ParseStringParameter(string.Format("bSearchable_{0}", i)) == "true";
                col.Search = this.ParseStringParameter(string.Format("sSearch_{0}", i));
                col.EscapeRegex = this.ParseStringParameter(string.Format("bRegex_{0}", i)) == "true";
                columns[i] = col;
            }
        }
        public DataTablesRequest(HttpRequest httpRequest)       // 标准的 WinForm 方式下的构造函数
            : this(new HttpRequestWrapper(httpRequest))
        { }

        #region
        private const string sEchoParameter = "sEcho";

        // 起始索引和长度
        private const string iDisplayStartParameter = "iDisplayStart";
        private const string iDisplayLengthParameter = "iDisplayLength";

        // 列数
        private const string iColumnsParameter = "iColumns";
        private const string sColumnsParameter = "sColumns";

        // 参与排序列数
        private const string iSortingColsParameter = "iSortingCols";
        private const string iSortColPrefixParameter = "iSortCol_";         // 排序列的索引
        private const string sSortDirPrefixParameter = "sSortDir_";         // 排序的方向 asc, desc

        // 每一列的可排序性
        private const string bSortablePrefixParameter = "bSortable_";

        // 全局搜索
        private const string sSearchParameter = "sSearch";
        private const string bRegexParameter = "bRegex";

        // 每一列的搜索
        private const string bSearchablePrefixParameter = "bSearchable_";
        private const string sSearchPrefixParameter = "sSearch_";
        private const string bEscapeRegexPrefixParameter = "bRegex_";
        #endregion

        private readonly string echo;
        public string sEcho
        {
            get { return echo; }
        }

        private readonly int displayStart;
        public int iDisplayStart
        {
            get { return this.displayStart; }
        }

        private readonly int displayLength;
        public int iDisplayLength
        {
            get { return this.displayLength; }
        }

        // 参与排序的列
        private readonly int sortingCols;
        public int iSortingCols
        {
            get { return this.sortingCols; }
        }

        // 排序列
        private readonly SortColumn[] sortColumns;
        public SortColumn[] SortColumns
        {
            get { return sortColumns; }
        }

        private readonly int ColumnCount;
        public int iColumns
        {
            get { return this.ColumnCount; }
        }

        private readonly Column[] columns;
        public Column[] Columns
        {
            get { return this.columns; }
        }

        private readonly string search;
        public string Search
        {
            get { return this.search; }
        }

        private readonly bool regex;
        public bool Regex
        {
            get { return this.regex; }
        }

        #region 常用的几个解析方法
        private int ParseIntParameter(string name)          // 解析为整数
        {
            int result = 0;
            string parameter = this.request[name];
            if (!string.IsNullOrEmpty(parameter))
            {
                int.TryParse(parameter, out result);
            }
            return result;
        }

        private string ParseStringParameter(string name)    // 解析为字符串
        {
            return this.request[name];
        }

        private bool ParseBooleanParameter(string name)     // 解析为布尔类型
        {
            bool result = false;
            string parameter = this.request[name];
            if (!string.IsNullOrEmpty(parameter))
            {
                bool.TryParse(parameter, out result);
            }
            return result;
        }
        #endregion
    }
}

本篇我不想做过多的说明，我写了非常详实的注释，而且代码非常通俗易懂，界面的功能还是非常强大的，我相信，从这些犀利的代码中，你一定会获益良多。