DS7/DSWeb/Areas/RptMng/Controllers/MsRptWlTransController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using DSWeb.Areas.RptMng.Models;
using DSWeb.TruckMng.Helper;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.SoftMng.Filter;

namespace DSWeb.Areas.RptMng.Controllers
{
    /// <summary>
    /// 单车运输明细
    /// </summary>
    public class MsRptWlTransController : Controller
    {
        //
        // GET: /RptMng/MsRptWlTrans/

        public ActionResult Index()
        {
            return View();
        }
        [SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
        public ContentResult GetDataList(int start, int limit, string sort, string condition)
        {
            var dataList = GetDataList(condition);

            var list = dataList.Skip(start).Take(limit);
            
            var json = JsonConvert.Serialize(new { Success = true, Message = "查询成功", totalCount = dataList.Count, data = list.ToList() });
            return new ContentResult() { Content = json };
        }
        [SqlKeyWordsFilter(Type = "Action")]//sql 防注入过滤器
        private static List<MsRptWlTrans> GetDataList(string strCondition)
        {
            var strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("BillNo,RefBillNo,GId,TruckNo,DrvName,CustomerName,dbo.trimdate(ExpDate) ExpDate,YardName,RtnYardName,DstArea,");
            strSql.Append("(OverLoadMil+NoLoadMil) as RealMil,OverLoadMil,NoLoadMil,RatedFuel,FuelQty,ContainerType,FixFsTotal,Freight,ExtrasFee,Charge");
            strSql.Append(" from vMsWlTrans  ");

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            return SetData(strSql);
        }

        private static List<MsRptWlTrans> SetData(StringBuilder strSql)
        {
            var headList = new List<MsRptWlTrans>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new MsRptWlTrans();
                    #region Set DB data to Object

                    data.BillNo = Convert.ToString(reader["BillNo"]);
                    data.RefBillNo = Convert.ToString(reader["RefBillNo"]);
                    data.GId = Convert.ToString(reader["GId"]);
                    data.TruckNo = Convert.ToString(reader["TruckNo"]);
                    data.DrvName = Convert.ToString(reader["DrvName"]);
                    data.CustomerName = Convert.ToString(reader["CustomerName"]);
                    data.ExpDate = Convert.ToString(reader["ExpDate"]);
                    data.YardName = Convert.ToString(reader["YardName"]);
                    data.RtnYardName = Convert.ToString(reader["RtnYardName"]);
                    data.DstArea = Convert.ToString(reader["DstArea"]);

                    data.RealMil = Convert.ToDecimal(reader["RealMil"]);
                    data.OverLoadMil = Convert.ToDecimal(reader["OverLoadMil"]);
                    data.NoLoadMil = Convert.ToDecimal(reader["NoLoadMil"]);
                    data.RatedFuel = Convert.ToDecimal(reader["RatedFuel"]);
                    data.FuelQty = Convert.ToDecimal(reader["FuelQty"]);

                    data.ContainerType = Convert.ToString(reader["ContainerType"]);
                   
                    data.FixFsTotal = Convert.ToDecimal(reader["FixFsTotal"]);
                    data.Freight = Convert.ToDecimal(reader["Freight"]);
                    data.ExtrasFee = Convert.ToDecimal(reader["ExtrasFee"]);
                    
                    data.Charge = Convert.ToDecimal(reader["Charge"]);
                    
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

    }
}