public JsonResult LoadStatCarrierStatementGrid(string sidx, string sord, int page, int rows, string startTime, string endTime, string startCountry, string startProvince, string startCity, string destCountry, string destProvince, string destCity, string carrierName, string carNo, string deliverBillReceiptReceived)
{
//读取数据
string strErrText;
SettlementSystem settlement = new SettlementSystem();
List<CarrierStatement> listStatement = settlement.LoadCarrierStatementByConditions(startTime, endTime, startCountry, startProvince, startCity, destCountry, destProvince, destCity, carrierName, carNo, deliverBillReceiptReceived, LoginAccountId, LoginStaffName, out strErrText);
if (listStatement == null)
{
throw new Exception(strErrText);
}
//提取当前页面数据
int nTotalRows = listStatement.Count;
int nPageIndex = page;
int nPageSize = rows;
int nTotalPages = nTotalRows / nPageSize;
if (nTotalRows % nPageSize > 0)
nTotalPages++;
string sortExpression = (sidx ?? "CreateTime") + " " + (sord ?? "ASC");
var data = listStatement.OrderBy(sortExpression).Skip((nPageIndex - 1) * nPageSize).Take(nPageSize).ToList();
//生成表格数据
var ret = new
{
total = nTotalPages,
page = nPageIndex,
records = nTotalRows,
rows = (
from s in data
select new
{
id = s.Id,
cell = new string[] {
s.Id.ToString(),
s.CarrierName,
s.DeliverBillNo,
s.DeliveryNo,
s.CreateTime,
s.CarNo,
s.StartCity,
s.ReceiverCity,
s.TotalPackages.ToString(),
s.TotalTunnages.ToString("#0.######"),
s.TotalPiles.ToString("#0.######"),
s.TotalTenThousands.ToString("#0.######"),
s.TransportCharges.ToString(),
s.Remark,
s.Remark2,
s.DeliverBillReceiptReceived.ToString(),
s.DamageInfo
}
}).ToArray(),
userdata = new
{
CarrierName = InnoSoft.LS.Resources.Labels.Total,
Packages = data.Sum(s => s.TotalPackages),
Tunnages = data.Sum(s => s.TotalTunnages),
Piles = data.Sum(s => s.TotalPiles),
TenThousands = data.Sum(s => s.TotalTenThousands),
TransportCharges = data.Sum(s => s.TransportCharges)
}
};
return Json(ret, JsonRequestBehavior.AllowGet);
}
public ActionResult ExportCarrierStatement()
{
string strErrText;
var request = HttpContext.Request;
string strStartTime = request.QueryString["startTime"] ?? string.Empty;
string strEndTime = request.QueryString["endTime"] ?? string.Empty;
string strStartCountry = request.QueryString["startCountry"] ?? string.Empty;
string strStartProvince = request.QueryString["startProvince"] ?? string.Empty;
string strStartCity = request.QueryString["startCity"] ?? string.Empty;
string strDestCountry = request.QueryString["destCountry"] ?? string.Empty;
string strDestProvince = request.QueryString["destProvince"] ?? string.Empty;
string strDestCity = request.QueryString["destCity"] ?? string.Empty;
string strCarrierName = request.QueryString["carrierName"] ?? string.Empty;
string strCarNo = request.QueryString["carNo"] ?? string.Empty;
string strDeliverBillReceiptReceived = request.QueryString["deliverBillReceiptReceived"] ?? "false";
//读取数据
SettlementSystem settlement = new SettlementSystem();
List<CarrierStatement> listStatement = settlement.LoadCarrierStatementByConditions(strStartTime, strEndTime, strStartCountry, strStartProvince, strStartCity, strDestCountry, strDestProvince, strDestCity, strCarrierName, strCarNo, strDeliverBillReceiptReceived, LoginAccountId, LoginStaffName, out strErrText);
if (listStatement == null)
{
throw new Exception(strErrText);
}
//汇总
List<CarrierStatement> listStat = new List<CarrierStatement>();
{
//按承运单位分组
var grpCarrierNames = listStatement.GroupBy(s => s.CarrierName).OrderBy(s => s.Key);
foreach (var grpCarrierName in grpCarrierNames)
{
List<CarrierStatement> listCarrierNameDetail = grpCarrierName.OrderBy(s => s.CreateTime).ToList<CarrierStatement>();
listStat.AddRange(listCarrierNameDetail);
//小计
CarrierStatement subTotal = new CarrierStatement();
subTotal.CarrierName = InnoSoft.LS.Resources.Labels.Subtotal;
subTotal.TotalPackages = listCarrierNameDetail.Sum(s => s.TotalPackages);
subTotal.TotalTunnages = listCarrierNameDetail.Sum(s => s.TotalTunnages);
subTotal.TotalPiles = listCarrierNameDetail.Sum(s => s.TotalPiles);
subTotal.TotalTenThousands = listCarrierNameDetail.Sum(s => s.TotalTenThousands);
subTotal.TransportCharges = listCarrierNameDetail.Sum(s => s.TransportCharges);
listStat.Add(subTotal);
}
//总计
CarrierStatement total = new CarrierStatement();
total.CarrierName = InnoSoft.LS.Resources.Labels.Total;
total.TotalPackages = listStatement.Sum(s => s.TotalPackages);
total.TotalTunnages = listStatement.Sum(s => s.TotalTunnages);
total.TotalPiles = listStatement.Sum(s => s.TotalPiles);
total.TotalTenThousands = listStatement.Sum(s => s.TotalTenThousands);
total.TransportCharges = listStatement.Sum(s => s.TransportCharges);
listStat.Add(total);
}
//生成GridView
BoundField colCarrierName = new BoundField();
colCarrierName.HeaderText = InnoSoft.LS.Resources.Labels.CustomerName;
colCarrierName.DataField = "CarrierName";
BoundField colDeliverBillNo = new BoundField();
colDeliverBillNo.HeaderText = InnoSoft.LS.Resources.Labels.DeliverBillNo;
colDeliverBillNo.DataField = "DeliverBillNo";
BoundField colDeliveryNo = new BoundField();
colDeliveryNo.HeaderText = InnoSoft.LS.Resources.Labels.DeliveryNo;
colDeliveryNo.DataField = "DeliveryNo";
BoundField colCreateTime = new BoundField();
colCreateTime.HeaderText = InnoSoft.LS.Resources.Labels.DeliverDate;
colCreateTime.DataField = "CreateTime";
BoundField colCarNo = new BoundField();
colCarNo.HeaderText = InnoSoft.LS.Resources.Labels.CarNo;
colCarNo.DataField = "CarNo";
BoundField colStartCity = new BoundField();
colStartCity.HeaderText = InnoSoft.LS.Resources.Labels.StartPlace;
colStartCity.DataField = "StartCity";
BoundField colReceiverCity = new BoundField();
colReceiverCity.HeaderText = InnoSoft.LS.Resources.Labels.DestPlace;
colReceiverCity.DataField = "ReceiverCity";
BoundField colPackages = new BoundField();
colPackages.HeaderText = InnoSoft.LS.Resources.Labels.Pieces;
colPackages.DataField = "Packages";
BoundField colTunnages = new BoundField();
colTunnages.HeaderText = InnoSoft.LS.Resources.Labels.Tunnages;
colTunnages.DataField = "Tunnages";
BoundField colPiles = new BoundField();
colPiles.HeaderText = InnoSoft.LS.Resources.Labels.Piles;
colPiles.DataField = "Piles";
BoundField colTenThousands = new BoundField();
colTenThousands.HeaderText = InnoSoft.LS.Resources.Labels.TenThousands;
colTenThousands.DataField = "TenThousands";
BoundField colTransportCharges = new BoundField();
colTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.TransportCharges;
colTransportCharges.DataField = "TransportCharges";
BoundField colRemark = new BoundField();
colRemark.HeaderText = InnoSoft.LS.Resources.Labels.Remark;
colRemark.DataField = "Remark";
BoundField colRemark2 = new BoundField();
colRemark2.HeaderText = InnoSoft.LS.Resources.Labels.Remark + "2";
colRemark2.DataField = "Remark2";
BoundField colDeliverBillReceiptReceived = new BoundField();
colDeliverBillReceiptReceived.HeaderText = InnoSoft.LS.Resources.Labels.ReceiptReceived;
colDeliverBillReceiptReceived.DataField = "DeliverBillReceiptReceived";
BoundField colDamageInfo = new BoundField();
colDamageInfo.HeaderText = InnoSoft.LS.Resources.Labels.DamageInfo;
colDamageInfo.DataField = "DamageInfo";
var grid = new GridView();
grid.Columns.Add(colCarrierName);
grid.Columns.Add(colDeliverBillNo);
grid.Columns.Add(colDeliveryNo);
grid.Columns.Add(colCreateTime);
grid.Columns.Add(colCarNo);
grid.Columns.Add(colStartCity);
grid.Columns.Add(colReceiverCity);
grid.Columns.Add(colPackages);
grid.Columns.Add(colTunnages);
grid.Columns.Add(colPiles);
grid.Columns.Add(colTenThousands);
grid.Columns.Add(colTransportCharges);
grid.Columns.Add(colRemark);
grid.Columns.Add(colRemark2);
grid.Columns.Add(colDeliverBillReceiptReceived);
grid.Columns.Add(colDamageInfo);
grid.AutoGenerateColumns = false;
grid.RowDataBound += new GridViewRowEventHandler(StatCarrierStatementGrid_RowDataBound);
grid.DataSource = from s in listStat
select new
{
CarrierName = s.CarrierName,
DeliverBillNo = s.DeliverBillNo,
DeliveryNo = s.DeliveryNo,
CreateTime = s.CreateTime,
CarNo = s.CarNo,
StartCity = s.StartCity,
ReceiverCity = s.ReceiverCity,
Packages = s.TotalPackages != 0 ? s.TotalPackages.ToString() : string.Empty,
Tunnages = s.TotalTunnages != 0 ? s.TotalTunnages.ToString("#0.######") : string.Empty,
Piles = s.TotalPiles > 0 ? s.TotalPiles.ToString("#0.######") : string.Empty,
TenThousands = s.TotalTenThousands > 0 ? s.TotalTenThousands.ToString("#0.######") : string.Empty,
TransportCharges = s.TransportCharges != 0 ? s.TransportCharges.ToString("N2") : string.Empty,
Remark = s.Remark,
Remark2 = s.Remark2,
DeliverBillReceiptReceived = s.DeliverBillReceiptReceived ? InnoSoft.LS.Resources.Options.Yes : InnoSoft.LS.Resources.Options.No,
DamageInfo = s.DamageInfo
};
grid.DataBind();
//导出GridView
Response.ClearContent();
Response.Charset = InnoSoft.LS.Resources.Encoding.ExcelCharset;
Response.ContentEncoding = System.Text.Encoding.GetEncoding(InnoSoft.LS.Resources.Encoding.ExcelContent);
Response.ContentType = "application/ms-excel";
Response.Write("<meta http-equiv=Content-Type content=text/html charset=" + InnoSoft.LS.Resources.Encoding.ExcelCharset + ">");
Response.AddHeader("content-disposition", "attachment; filename=CarrierStatement.xls");
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
return View("StatCarrierStatement");
}
