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

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);        }    }}

@{    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>

/* ==============================================================================   * 功能描述：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    }}