public ActionResult ExportCustomerStatement()
{
#region 生成数据源
string strErrText;
var request = HttpContext.Request;
string strStartTime = request.QueryString["startTime"] ?? string.Empty;
string strEndTime = request.QueryString["endTime"] ?? string.Empty;
string strPayerName = request.QueryString["payerName"] ?? string.Empty;
string strReceiverName = request.QueryString["receiverName"] ?? 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 strGoodsName = request.QueryString["goodsName"] ?? string.Empty;
string strAllowStatementWhenConsignedDeliverPlanNotCompleted = request.QueryString["allowStatementWhenConsignedDeliverPlanNotCompleted"] ?? "false";
//读取数据
SettlementSystem settlement = new SettlementSystem();
List<CustomerStatement> listStatement = settlement.LoadCustomerStatementByConditions(strStartTime, strEndTime, strPayerName, strReceiverName, strStartCountry, strStartProvince, strStartCity, strDestCountry, strDestProvince, strDestCity, strCarrierName, strCarNo, strGoodsName, strAllowStatementWhenConsignedDeliverPlanNotCompleted, LoginAccountId, LoginStaffName, out strErrText);
if (listStatement == null)
{
throw new Exception(strErrText);
}
//根据结算公式计算运费
foreach (CustomerStatement statement in listStatement)
{
if (statement.TransportCharges == 0 && statement.SettlementExpression != null && statement.SettlementExpression != string.Empty)
{
try
{
EvaluatorHelper evaluator = new EvaluatorHelper();
evaluator.SetExpression(statement.SettlementExpression);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.KM, statement.KM == null || statement.KM == string.Empty ? "0" : statement.KM);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Tunnages, statement.TotalTunnages.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Piles, statement.TotalPiles.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportPrice, statement.TransportPrice.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportCharges, "0");
string strTransportCharges = evaluator.EvaluateExpression();
statement.TransportCharges = decimal.Parse(strTransportCharges);
}
catch (Exception e)
{
throw e;
}
}
}
//计算拼车费
var grpShipmentNos = listStatement.GroupBy(s => s.ShipmentNo).OrderBy(s => s.Key);
foreach (var grpShipmentNo in grpShipmentNos)
{
if (grpShipmentNo.Key != null && grpShipmentNo.Key != string.Empty)
{
List<CustomerStatement> listShipmentNoDetail = grpShipmentNo.ToList<CustomerStatement>();
int i = 0;
while (i < listShipmentNoDetail.Count)
{
if (i > 0)
{
int j = 0;
while (j < i)
{
if (listShipmentNoDetail[i].ReceiverAddress == listShipmentNoDetail[j].ReceiverAddress)
{
break;
}
j++;
}
if (j >= i)
{
listShipmentNoDetail[i].CarpoolFee = 100;
}
}
i++;
}
}
}
//生成结果数据
List<CustomerStatement> listRet = new List<CustomerStatement>();
{
//按客户分组
var grpCustomerNames = listStatement.GroupBy(f => f.CustomerName).OrderBy(f => f.Key);
foreach (var grpCustomerName in grpCustomerNames)
{
List<CustomerStatement> data = grpCustomerName.ToList<CustomerStatement>();
listRet.AddRange(data);
//生成小计行
CustomerStatement subtotal = new CustomerStatement();
subtotal.CustomerName = grpCustomerName.Key;
subtotal.CreateTime = InnoSoft.LS.Resources.Labels.Subtotal;
subtotal.TotalPackages = data.Sum(s => s.TotalPackages);
subtotal.TotalTunnages = data.Sum(s => s.TotalTunnages);
subtotal.TotalPiles = data.Sum(s => s.TotalPiles);
subtotal.TotalTenThousands = data.Sum(s => s.TotalTenThousands);
subtotal.TransportCharges = data.Sum(s => s.TransportCharges);
subtotal.CarpoolFee = data.Sum(s => s.CarpoolFee);
subtotal.RiverCrossingCharges = data.Sum(s => s.RiverCrossingCharges);
listRet.Add(subtotal);
}
//生成合计行
CustomerStatement total = new CustomerStatement();
total.CustomerName = 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);
total.CarpoolFee = listStatement.Sum(s => s.CarpoolFee);
total.RiverCrossingCharges = listStatement.Sum(s => s.RiverCrossingCharges);
listRet.Add(total);
}
#endregion
#region 导出Excel
#region 创建Excel工作簿
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
#endregion
#region 设置列宽度
int iCol = 0;
sheet.SetColumnWidth(iCol++, 15 * 256);//客户名称
sheet.SetColumnWidth(iCol++, 10 * 256);//发货日期
sheet.SetColumnWidth(iCol++, 13 * 256);//计划单号
sheet.SetColumnWidth(iCol++, 11 * 256);//交货单号
if (listRet.FindAll(delegate(CustomerStatement cs) { return cs.ShipmentNo != null && cs.ShipmentNo != string.Empty; }).Count() > 0)
{
sheet.SetColumnWidth(iCol++, 11 * 256);//装运单号
}
sheet.SetColumnWidth(iCol++, 15 * 256);//收货单位名称
sheet.SetColumnWidth(iCol++, 20 * 256);//收货单位地址
sheet.SetColumnWidth(iCol++, 6 * 256);//起点
sheet.SetColumnWidth(iCol++, 6 * 256);//讫点
sheet.SetColumnWidth(iCol++, 10 * 256);//车号
sheet.SetColumnWidth(iCol++, 10 * 256);//货物名称
if (listRet.Sum(cs => cs.TotalPackages) != 0)
{
sheet.SetColumnWidth(iCol++, 4 * 256);//件数
}
if (listRet.Sum(cs => cs.TotalTunnages) != 0)
{
sheet.SetColumnWidth(iCol++, 9 * 256);//吨数
}
if (listRet.Sum(cs => cs.TotalPiles) > 0)
{
sheet.SetColumnWidth(iCol++, 8 * 256);//垛数
}
if (listRet.Sum(cs => cs.TotalTenThousands) > 0)
{
sheet.SetColumnWidth(iCol++, 8 * 256);//万只
}
sheet.SetColumnWidth(iCol++, 6 * 256);//运费单价
sheet.SetColumnWidth(iCol++, 10 * 256);//运费金额
if (listRet.Sum(cs => cs.CarpoolFee) > 0)
{
sheet.SetColumnWidth(iCol++, 8 * 256);//拼车费
}
if (listRet.Sum(cs => cs.RiverCrossingCharges) > 0)
{
sheet.SetColumnWidth(iCol++, 8 * 256);//过江费
}
if (listRet.FindAll(delegate(CustomerStatement cs) { return cs.Remark != null && cs.Remark != string.Empty; }).Count() > 0)
{
sheet.SetColumnWidth(iCol++, 15 * 256);//备注
}
if (listRet.FindAll(delegate(CustomerStatement cs) { return cs.Remark2 != null && cs.Remark2 != string.Empty; }).Count() > 0)
{
sheet.SetColumnWidth(iCol++, 15 * 256);//备注2
}
sheet.SetColumnWidth(iCol++, 6 * 256);//回单标志
#endregion
#region 创建表头
//标题
var rowTitle = sheet.CreateRow(0);
rowTitle.HeightInPoints = 30;
rowTitle.CreateCell(0).SetCellValue(strPayerName + InnoSoft.LS.Resources.Titles.CustomerTransportChargesStatement);
ICellStyle styleTitle = workbook.CreateCellStyle();
styleTitle.Alignment = HorizontalAlignment.CENTER;
IFont fontTitle = workbook.CreateFont();
fontTitle.FontHeightInPoints = 20;
styleTitle.SetFont(fontTitle);
rowTitle.GetCell(0).CellStyle = styleTitle;
var rowTitle2 = sheet.CreateRow(1);
rowTitle2.HeightInPoints = 22;
rowTitle2.CreateCell(0).SetCellValue(strStartTime + InnoSoft.LS.Resources.Labels.Separator1 + strEndTime);
ICellStyle styleTitle2 = workbook.CreateCellStyle();
styleTitle2.Alignment = HorizontalAlignment.CENTER;
IFont fontTitle2 = workbook.CreateFont();
fontTitle2.FontHeightInPoints = 12;
styleTitle2.SetFont(fontTitle2);
rowTitle2.GetCell(0).CellStyle = styleTitle2;
//表头
ICellStyle styleLeft = workbook.CreateCellStyle();
styleLeft.Alignment = HorizontalAlignment.LEFT;
styleLeft.BorderBottom = CellBorderType.THIN;
styleLeft.BorderLeft = CellBorderType.THIN;
styleLeft.BorderRight = CellBorderType.THIN;
styleLeft.BorderTop = CellBorderType.THIN;
ICellStyle styleCenter = workbook.CreateCellStyle();
styleCenter.Alignment = HorizontalAlignment.CENTER;
styleCenter.BorderBottom = CellBorderType.THIN;
styleCenter.BorderLeft = CellBorderType.THIN;
styleCenter.BorderRight = CellBorderType.THIN;
styleCenter.BorderTop = CellBorderType.THIN;
ICellStyle styleRight = workbook.CreateCellStyle();
styleRight.Alignment = HorizontalAlignment.RIGHT;
styleRight.BorderBottom = CellBorderType.THIN;
styleRight.BorderLeft = CellBorderType.THIN;
styleRight.BorderRight = CellBorderType.THIN;
styleRight.BorderTop = CellBorderType.THIN;
var rowHeader = sheet.CreateRow(2);
iCol = 0;
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.CustomerName);//客户名称
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.DeliverDate);//发货日期
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.PlanNo);//计划单号
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.DeliveryNo);//交货单号
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
if (listRet.FindAll(delegate(CustomerStatement cs) { return cs.ShipmentNo != null && cs.ShipmentNo != string.Empty; }).Count() > 0)
{
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.ShipmentNo);//装运单号
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
}
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.ReceiverName);//收货单位名称
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.ReceiverAddress);//收货单位地址
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.StartPlace);//起点
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.DestPlace);//讫点
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.CarNo);//车号
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.GoodsName);//货物名称
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
if (listRet.Sum(cs => cs.TotalPackages) != 0)
{
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.Pieces);//件数
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
}
if (listRet.Sum(cs => cs.TotalTunnages) != 0)
{
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.Tunnages);//吨数
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
}
if (listRet.Sum(cs => cs.TotalPiles) > 0)
{
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.Piles);//垛数
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
}
if (listRet.Sum(cs => cs.TotalTenThousands) > 0)
{
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.TenThousands);//万只
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
}
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.TransportPrice);//运费单价
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.TransportCharges);//运费金额
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
if (listRet.Sum(cs => cs.CarpoolFee) > 0)
{
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.CarpoolFee);//拼车费
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
}
if (listRet.Sum(cs => cs.RiverCrossingCharges) > 0)
{
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.RiverCrossingCharges);//过江费
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
}
if (listRet.FindAll(delegate(CustomerStatement cs) { return cs.Remark != null && cs.Remark != string.Empty; }).Count() > 0)
{
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.Remark);//备注
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
}
if (listRet.FindAll(delegate(CustomerStatement cs) { return cs.Remark2 != null && cs.Remark2 != string.Empty; }).Count() > 0)
{
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.Remark + "2");//备注
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
}
rowHeader.CreateCell(iCol++).SetCellValue(InnoSoft.LS.Resources.Labels.ReceiptReceived);//回单标志
rowHeader.GetCell(iCol - 1).CellStyle = styleCenter;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, iCol - 1));
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, iCol - 1));
#endregion
#region 创建表体
int iRow = 3;
foreach (var ret in listRet)
{
var rowBody = sheet.CreateRow(iRow++);
iCol = 0;
rowBody.CreateCell(iCol++).SetCellValue(ret.CustomerName);//客户名称
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
rowBody.CreateCell(iCol++).SetCellValue(ret.CreateTime);//发货日期
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
rowBody.CreateCell(iCol++).SetCellValue(ret.PlanNo);//计划单号
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
rowBody.CreateCell(iCol++).SetCellValue(ret.DeliveryNo);//交货单号
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
if (listRet.FindAll(delegate(CustomerStatement cs) { return cs.ShipmentNo != null && cs.ShipmentNo != string.Empty; }).Count() > 0)
{
rowBody.CreateCell(iCol++).SetCellValue(ret.ShipmentNo);//装运单号
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
}
rowBody.CreateCell(iCol++).SetCellValue(ret.ReceiverName);//收货单位名称
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
rowBody.CreateCell(iCol++).SetCellValue(ret.ReceiverAddress);//收货单位地址
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
rowBody.CreateCell(iCol++).SetCellValue(ret.StartCity);//起点
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
rowBody.CreateCell(iCol++).SetCellValue(ret.ReceiverCity);//讫点
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
rowBody.CreateCell(iCol++).SetCellValue(ret.CarNo);//车号
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
rowBody.CreateCell(iCol++).SetCellValue(ret.GoodsName);//货物名称
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
if (listRet.Sum(cs => cs.TotalPackages) != 0)
{
rowBody.CreateCell(iCol++).SetCellValue(ret.TotalPackages != 0 ? ret.TotalPackages.ToString() : string.Empty);//件数
rowBody.GetCell(iCol - 1).CellStyle = styleCenter;
}
if (listRet.Sum(cs => cs.TotalTunnages) != 0)
{
rowBody.CreateCell(iCol++).SetCellValue(ret.TotalTunnages != 0 ? ret.TotalTunnages.ToString("#0.######") : string.Empty);//吨数
rowBody.GetCell(iCol - 1).CellStyle = styleCenter;
}
if (listRet.Sum(cs => cs.TotalPiles) > 0)
{
rowBody.CreateCell(iCol++).SetCellValue(ret.TotalPiles > 0 ? ret.TotalPiles.ToString("#0.######") : string.Empty);//垛数
rowBody.GetCell(iCol - 1).CellStyle = styleCenter;
}
if (listRet.Sum(cs => cs.TotalTenThousands) > 0)
{
rowBody.CreateCell(iCol++).SetCellValue(ret.TotalTenThousands > 0 ? ret.TotalTenThousands.ToString("#0.######") : string.Empty);//万只
rowBody.GetCell(iCol - 1).CellStyle = styleCenter;
}
rowBody.CreateCell(iCol++).SetCellValue(ret.TransportPrice > 0 ? ret.TransportPrice.ToString("#0.00####") : string.Empty);//运费单价
rowBody.GetCell(iCol - 1).CellStyle = styleRight;
rowBody.CreateCell(iCol++).SetCellValue(ret.TransportCharges != 0 ? ret.TransportCharges.ToString("N2") : string.Empty);//运费金额
rowBody.GetCell(iCol - 1).CellStyle = styleRight;
if (listRet.Sum(cs => cs.CarpoolFee) > 0)
{
rowBody.CreateCell(iCol++).SetCellValue(ret.CarpoolFee > 0 ? ret.CarpoolFee.ToString("N2") : string.Empty);//拼车费
rowBody.GetCell(iCol - 1).CellStyle = styleRight;
}
if (listRet.Sum(cs => cs.RiverCrossingCharges) > 0)
{
rowBody.CreateCell(iCol++).SetCellValue(ret.RiverCrossingCharges > 0 ? ret.RiverCrossingCharges.ToString("N2") : string.Empty);//过江费
rowBody.GetCell(iCol - 1).CellStyle = styleRight;
}
if (listRet.FindAll(delegate(CustomerStatement cs) { return cs.Remark != null && cs.Remark != string.Empty; }).Count() > 0)
{
rowBody.CreateCell(iCol++).SetCellValue(ret.Remark);//备注
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
}
if (listRet.FindAll(delegate(CustomerStatement cs) { return cs.Remark2 != null && cs.Remark2 != string.Empty; }).Count() > 0)
{
rowBody.CreateCell(iCol++).SetCellValue(ret.Remark2);//备注
rowBody.GetCell(iCol - 1).CellStyle = styleLeft;
}
rowBody.CreateCell(iCol++).SetCellValue(ret.DeliverBillReceiptReceived ? InnoSoft.LS.Resources.Options.Yes : InnoSoft.LS.Resources.Options.No);//回单标志
rowBody.GetCell(iCol - 1).CellStyle = styleCenter;
}
#endregion
#region 输出Excel文件
MemoryStream output = new MemoryStream();
workbook.Write(output);
return File(output.ToArray(), "application/vnd.ms-excel", "CustomerStatement.xls");
#endregion
#endregion
}
public JsonResult LoadStatCustomerStatementGrid(string sidx, string sord, int page, int rows, string startTime, string endTime, string payerName, string receiverName, string startCountry, string startProvince, string startCity, string destCountry, string destProvince, string destCity, string carrierName, string carNo, string goodsName, string allowStatementWhenConsignedDeliverPlanNotCompleted)
{
//读取数据
string strErrText;
SettlementSystem settlement = new SettlementSystem();
List<CustomerStatement> listStatement = settlement.LoadCustomerStatementByConditions(startTime, endTime, payerName, receiverName, startCountry, startProvince, startCity, destCountry, destProvince, destCity, carrierName, carNo, goodsName, allowStatementWhenConsignedDeliverPlanNotCompleted, LoginAccountId, LoginStaffName, out strErrText);
if (listStatement == null)
{
throw new Exception(strErrText);
}
//根据结算公式计算运费
foreach (CustomerStatement statement in listStatement)
{
if (statement.TransportCharges == 0 && statement.SettlementExpression != null && statement.SettlementExpression != string.Empty)
{
try
{
EvaluatorHelper evaluator = new EvaluatorHelper();
evaluator.SetExpression(statement.SettlementExpression);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.KM, statement.KM == null || statement.KM == string.Empty ? "0" : statement.KM);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Tunnages, statement.TotalTunnages.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Piles, statement.TotalPiles.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportPrice, statement.TransportPrice.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportCharges, "0");
string strTransportCharges = evaluator.EvaluateExpression();
statement.TransportCharges = decimal.Parse(strTransportCharges);
}
catch (Exception e)
{
throw e;
}
}
}
//计算拼车费
var grpShipmentNos = listStatement.GroupBy(s => s.ShipmentNo).OrderBy(s => s.Key);
foreach (var grpShipmentNo in grpShipmentNos)
{
if (grpShipmentNo.Key != null && grpShipmentNo.Key != string.Empty)
{
List<CustomerStatement> listShipmentNoDetail = grpShipmentNo.ToList<CustomerStatement>();
int i = 0;
while (i < listShipmentNoDetail.Count)
{
if (i > 0)
{
int j = 0;
while (j < i)
{
if (listShipmentNoDetail[i].ReceiverAddress == listShipmentNoDetail[j].ReceiverAddress)
{
break;
}
j++;
}
if (j >= i)
{
listShipmentNoDetail[i].CarpoolFee = 100;
}
}
i++;
}
}
}
//提取当前页面数据
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.CustomerName,
s.CreateTime,
s.PlanNo,
s.DeliverBillNo,
s.ShipmentNo,
s.DeliveryNo,
s.ReceiverName,
s.ReceiverAddress,
s.StartCity,
s.ReceiverCity,
s.KM,
s.CarNo,
s.GoodsName,
s.TotalPackages.ToString(),
s.TotalTunnages.ToString("#0.######"),
s.TotalPiles.ToString("#0.######"),
s.TotalTenThousands.ToString("#0.######"),
s.TransportPrice.ToString("#0.######"),
s.SettlementExpression,
s.TransportCharges.ToString(),
s.CarpoolFee.ToString(),
s.RiverCrossingCharges.ToString(),
s.Remark,
s.Remark2,
s.DeliverBillReceiptReceived.ToString(),
s.InvoiceNo
}
}).ToArray(),
userdata = new
{
CustomerName = 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),
CarpoolFee = data.Sum(s => s.CarpoolFee),
RiverCrossingCharges = data.Sum(s => s.RiverCrossingCharges)
}
};
return Json(ret, JsonRequestBehavior.AllowGet);
}
public ActionResult ExportOrganTotalOutput()
{
string strErrText;
#region 提取参数
var request = HttpContext.Request;
string strStartTime = request.QueryString["startTime"] ?? string.Empty;
string strEndTime = request.QueryString["endTime"] ?? string.Empty;
string strOrganId = request.QueryString["organId"] ?? string.Empty;
#endregion
#region 读取明细数据
StatisticsSystem stat = new StatisticsSystem();
List<OrganTotalOutputDetail> listDetail = stat.LoadOrganTotalOutputDetailsByConditions(strStartTime, strEndTime, strOrganId, LoginAccountId, LoginStaffName, out strErrText);
if (listDetail == null)
{
throw new Exception(strErrText);
}
#endregion
#region 根据结算公式计算运费
{
foreach (OrganTotalOutputDetail detail in listDetail)
{
if (detail.CustomerTransportCharges == 0 && detail.CustomerSettlementExpression != null && detail.CustomerSettlementExpression != string.Empty)
{
try
{
EvaluatorHelper evaluator = new EvaluatorHelper();
evaluator.SetExpression(detail.CustomerSettlementExpression);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.KM, detail.KM == null || detail.KM == string.Empty ? "0" : detail.KM);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Tunnages, detail.TotalTunnages.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Piles, detail.TotalPiles.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportPrice, detail.CustomerTransportPrice.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportCharges, "0");
string strTransportCharges = evaluator.EvaluateExpression();
detail.CustomerTransportCharges = decimal.Parse(strTransportCharges);
}
catch (Exception e)
{
throw e;
}
}
}
}
#endregion
#region 计算拼车费
{
var grpShipmentNos = listDetail.GroupBy(d => d.ShipmentNo).OrderBy(d => d.Key);
foreach (var grpShipmentNo in grpShipmentNos)
{
if (grpShipmentNo.Key != null && grpShipmentNo.Key != string.Empty)
{
List<OrganTotalOutputDetail> listShipmentNoDetail = grpShipmentNo.ToList<OrganTotalOutputDetail>();
int i = 0;
while (i < listShipmentNoDetail.Count)
{
if (i > 0)
{
int j = 0;
while (j < i)
{
if (listShipmentNoDetail[i].ReceiverAddress == listShipmentNoDetail[j].ReceiverAddress)
{
break;
}
j++;
}
if (j >= i)
{
listShipmentNoDetail[i].CustomerCarpoolFee = 100;
}
}
i++;
}
}
}
}
#endregion
#region 生成表格数据源
List<StatOrganTotalOutput> listStat = new List<StatOrganTotalOutput>();
{
//按办事处分组
var grpOrganNames = listDetail.GroupBy(d => d.OwnOrganName).OrderBy(d => d.Key);
foreach (var grpOrganName in grpOrganNames)
{
List<OrganTotalOutputDetail> listOrganNameDetail = grpOrganName.ToList<OrganTotalOutputDetail>();
//再按省份分组
var grpReceiverProvinces = listOrganNameDetail.GroupBy(d => d.ReceiverProvince).OrderBy(d => d.Key);
foreach (var grpReceiverProvince in grpReceiverProvinces)
{
List<OrganTotalOutputDetail> listReceiverProvinceDetail = grpReceiverProvince.ToList<OrganTotalOutputDetail>();
//生成表格数据行
StatOrganTotalOutput s = new StatOrganTotalOutput();
s.Id = listStat.Count + 1;
s.OrganName = grpOrganName.Key;
s.ProvinceName = grpReceiverProvince.Key;
s.JoinInTunnages = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalTunnages);
s.SelfSupportTunnages = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalTunnages);
s.PrestowageTunnages = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.TotalTunnages);
s.SubtotalTunnages = listReceiverProvinceDetail.Sum(d => d.TotalTunnages);
s.JoinInPiles = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalPiles);
s.SelfSupportPiles = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalPiles);
s.PrestowagePiles = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.TotalPiles);
s.SubtotalPiles = listReceiverProvinceDetail.Sum(d => d.TotalPiles);
s.JoinInTransportCharges = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SelfSupportTransportCharges = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.PrestowageTransportCharges = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SubtotalTransportCharges = listReceiverProvinceDetail.Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.JoinInTransportChargesDifference = s.JoinInTransportCharges - listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CarrierTransportCharges);
s.SelfSupportTransportChargesDifference = s.SelfSupportTransportCharges - listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CarrierTransportCharges);
s.PrestowageTransportChargesDifference = s.PrestowageTransportCharges - listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.CarrierTransportCharges);
s.SubtotalTransportChargesDifference = s.SubtotalTransportCharges - listReceiverProvinceDetail.Sum(d => d.CarrierTransportCharges);
s.JoinInGrossProfitRate = s.JoinInTransportCharges != 0 ? s.JoinInTransportChargesDifference / s.JoinInTransportCharges : 0;
s.SelfSupportGrossProfitRate = s.SelfSupportTransportCharges != 0 ? s.SelfSupportTransportChargesDifference / s.SelfSupportTransportCharges : 0;
s.PrestowageGrossProfitRate = s.PrestowageTransportCharges != 0 ? s.PrestowageTransportChargesDifference / s.PrestowageTransportCharges : 0;
s.SubtotalGrossProfitRate = s.SubtotalTransportCharges != 0 ? s.SubtotalTransportChargesDifference / s.SubtotalTransportCharges : 0;
listStat.Add(s);
}
//小计
{
StatOrganTotalOutput s = new StatOrganTotalOutput();
s.Id = listStat.Count + 1;
s.OrganName = InnoSoft.LS.Resources.Labels.Subtotal;
s.ProvinceName = string.Empty;
s.JoinInTunnages = listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalTunnages);
s.SelfSupportTunnages = listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalTunnages);
s.PrestowageTunnages = listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.TotalTunnages);
s.SubtotalTunnages = listOrganNameDetail.Sum(d => d.TotalTunnages);
s.JoinInPiles = listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalPiles);
s.SelfSupportPiles = listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalPiles);
s.PrestowagePiles = listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.TotalPiles);
s.SubtotalPiles = listOrganNameDetail.Sum(d => d.TotalPiles);
s.JoinInTransportCharges = listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SelfSupportTransportCharges = listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.PrestowageTransportCharges = listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SubtotalTransportCharges = listOrganNameDetail.Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.JoinInTransportChargesDifference = s.JoinInTransportCharges - listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CarrierTransportCharges);
s.SelfSupportTransportChargesDifference = s.SelfSupportTransportCharges - listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CarrierTransportCharges);
s.PrestowageTransportChargesDifference = s.PrestowageTransportCharges - listOrganNameDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.CarrierTransportCharges);
s.SubtotalTransportChargesDifference = s.SubtotalTransportCharges - listOrganNameDetail.Sum(d => d.CarrierTransportCharges);
s.JoinInGrossProfitRate = s.JoinInTransportCharges != 0 ? s.JoinInTransportChargesDifference / s.JoinInTransportCharges : 0;
s.SelfSupportGrossProfitRate = s.SelfSupportTransportCharges != 0 ? s.SelfSupportTransportChargesDifference / s.SelfSupportTransportCharges : 0;
s.PrestowageGrossProfitRate = s.PrestowageTransportCharges != 0 ? s.PrestowageTransportChargesDifference / s.PrestowageTransportCharges : 0;
s.SubtotalGrossProfitRate = s.SubtotalTransportCharges != 0 ? s.SubtotalTransportChargesDifference / s.SubtotalTransportCharges : 0;
listStat.Add(s);
}
}
//总计
{
StatOrganTotalOutput s = new StatOrganTotalOutput();
s.Id = listStat.Count + 1;
s.OrganName = InnoSoft.LS.Resources.Labels.Total;
s.ProvinceName = string.Empty;
s.JoinInTunnages = listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalTunnages);
s.SelfSupportTunnages = listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalTunnages);
s.PrestowageTunnages = listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.TotalTunnages);
s.SubtotalTunnages = listDetail.Sum(d => d.TotalTunnages);
s.JoinInPiles = listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalPiles);
s.SelfSupportPiles = listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalPiles);
s.PrestowagePiles = listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.TotalPiles);
s.SubtotalPiles = listDetail.Sum(d => d.TotalPiles);
s.JoinInTransportCharges = listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SelfSupportTransportCharges = listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.PrestowageTransportCharges = listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SubtotalTransportCharges = listDetail.Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.JoinInTransportChargesDifference = s.JoinInTransportCharges - listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CarrierTransportCharges);
s.SelfSupportTransportChargesDifference = s.SelfSupportTransportCharges - listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CarrierTransportCharges);
s.PrestowageTransportChargesDifference = s.PrestowageTransportCharges - listDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.CarrierTransportCharges);
s.SubtotalTransportChargesDifference = s.SubtotalTransportCharges - listDetail.Sum(d => d.CarrierTransportCharges);
s.JoinInGrossProfitRate = s.JoinInTransportCharges != 0 ? s.JoinInTransportChargesDifference / s.JoinInTransportCharges : 0;
s.SelfSupportGrossProfitRate = s.SelfSupportTransportCharges != 0 ? s.SelfSupportTransportChargesDifference / s.SelfSupportTransportCharges : 0;
s.PrestowageGrossProfitRate = s.PrestowageTransportCharges != 0 ? s.PrestowageTransportChargesDifference / s.PrestowageTransportCharges : 0;
s.SubtotalGrossProfitRate = s.SubtotalTransportCharges != 0 ? s.SubtotalTransportChargesDifference / s.SubtotalTransportCharges : 0;
listStat.Add(s);
}
}
#endregion
#region 输出Excel
//生成GridView
BoundField colOrganName = new BoundField();
colOrganName.HeaderText = InnoSoft.LS.Resources.Labels.OrganName;
colOrganName.DataField = "OrganName";
BoundField colProvinceName = new BoundField();
colProvinceName.HeaderText = InnoSoft.LS.Resources.Labels.StateName;
colProvinceName.DataField = "ProvinceName";
BoundField colJoinInTunnages = new BoundField();
colJoinInTunnages.HeaderText = InnoSoft.LS.Resources.Labels.JoinIn + "-" + InnoSoft.LS.Resources.Labels.Tunnages;
colJoinInTunnages.DataField = "JoinInTunnages";
BoundField colSelfSupportTunnages = new BoundField();
colSelfSupportTunnages.HeaderText = InnoSoft.LS.Resources.Labels.SelfSupport + "-" + InnoSoft.LS.Resources.Labels.Tunnages;
colSelfSupportTunnages.DataField = "SelfSupportTunnages";
BoundField colPrestowageTunnages = new BoundField();
colPrestowageTunnages.HeaderText = InnoSoft.LS.Resources.Labels.Prestowage + "-" + InnoSoft.LS.Resources.Labels.Tunnages;
colPrestowageTunnages.DataField = "PrestowageTunnages";
BoundField colSubtotalTunnages = new BoundField();
colSubtotalTunnages.HeaderText = InnoSoft.LS.Resources.Labels.Subtotal + "-" + InnoSoft.LS.Resources.Labels.Tunnages;
colSubtotalTunnages.DataField = "SubtotalTunnages";
BoundField colJoinInPiles = new BoundField();
colJoinInPiles.HeaderText = InnoSoft.LS.Resources.Labels.JoinIn + "-" + InnoSoft.LS.Resources.Labels.Piles;
colJoinInPiles.DataField = "JoinInPiles";
BoundField colSelfSupportPiles = new BoundField();
colSelfSupportPiles.HeaderText = InnoSoft.LS.Resources.Labels.SelfSupport + "-" + InnoSoft.LS.Resources.Labels.Piles;
colSelfSupportPiles.DataField = "SelfSupportPiles";
BoundField colPrestowagePiles = new BoundField();
colPrestowagePiles.HeaderText = InnoSoft.LS.Resources.Labels.Prestowage + "-" + InnoSoft.LS.Resources.Labels.Piles;
colPrestowagePiles.DataField = "PrestowagePiles";
BoundField colSubtotalPiles = new BoundField();
colSubtotalPiles.HeaderText = InnoSoft.LS.Resources.Labels.Subtotal + "-" + InnoSoft.LS.Resources.Labels.Piles;
colSubtotalPiles.DataField = "SubtotalPiles";
BoundField colJoinInTransportCharges = new BoundField();
colJoinInTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.JoinIn + "-" + InnoSoft.LS.Resources.Labels.TransportCharges;
colJoinInTransportCharges.DataField = "JoinInTransportCharges";
BoundField colSelfSupportTransportCharges = new BoundField();
colSelfSupportTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.SelfSupport + "-" + InnoSoft.LS.Resources.Labels.TransportCharges;
colSelfSupportTransportCharges.DataField = "SelfSupportTransportCharges";
BoundField colPrestowageTransportCharges = new BoundField();
colPrestowageTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.Prestowage + "-" + InnoSoft.LS.Resources.Labels.TransportCharges;
colPrestowageTransportCharges.DataField = "PrestowageTransportCharges";
BoundField colSubtotalTransportCharges = new BoundField();
colSubtotalTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.Subtotal + "-" + InnoSoft.LS.Resources.Labels.TransportCharges;
colSubtotalTransportCharges.DataField = "SubtotalTransportCharges";
BoundField colJoinInTransportChargesDifference = new BoundField();
colJoinInTransportChargesDifference.HeaderText = InnoSoft.LS.Resources.Labels.JoinIn + "-" + InnoSoft.LS.Resources.Labels.TransportChargesDifference;
colJoinInTransportChargesDifference.DataField = "JoinInTransportChargesDifference";
BoundField colSelfSupportTransportChargesDifference = new BoundField();
colSelfSupportTransportChargesDifference.HeaderText = InnoSoft.LS.Resources.Labels.SelfSupport + "-" + InnoSoft.LS.Resources.Labels.TransportChargesDifference;
colSelfSupportTransportChargesDifference.DataField = "SelfSupportTransportChargesDifference";
BoundField colPrestowageTransportChargesDifference = new BoundField();
colPrestowageTransportChargesDifference.HeaderText = InnoSoft.LS.Resources.Labels.Prestowage + "-" + InnoSoft.LS.Resources.Labels.TransportChargesDifference;
colPrestowageTransportChargesDifference.DataField = "PrestowageTransportChargesDifference";
BoundField colSubtotalTransportChargesDifference = new BoundField();
colSubtotalTransportChargesDifference.HeaderText = InnoSoft.LS.Resources.Labels.Subtotal + "-" + InnoSoft.LS.Resources.Labels.TransportChargesDifference;
colSubtotalTransportChargesDifference.DataField = "SubtotalTransportChargesDifference";
BoundField colJoinInGrossProfitRate = new BoundField();
colJoinInGrossProfitRate.HeaderText = InnoSoft.LS.Resources.Labels.JoinIn + "-" + InnoSoft.LS.Resources.Labels.GrossProfitRate;
colJoinInGrossProfitRate.DataField = "JoinInGrossProfitRate";
BoundField colSelfSupportGrossProfitRate = new BoundField();
colSelfSupportGrossProfitRate.HeaderText = InnoSoft.LS.Resources.Labels.SelfSupport + "-" + InnoSoft.LS.Resources.Labels.GrossProfitRate;
colSelfSupportGrossProfitRate.DataField = "SelfSupportGrossProfitRate";
BoundField colPrestowageGrossProfitRate = new BoundField();
colPrestowageGrossProfitRate.HeaderText = InnoSoft.LS.Resources.Labels.Prestowage + "-" + InnoSoft.LS.Resources.Labels.GrossProfitRate;
colPrestowageGrossProfitRate.DataField = "PrestowageGrossProfitRate";
BoundField colSubtotalGrossProfitRate = new BoundField();
colSubtotalGrossProfitRate.HeaderText = InnoSoft.LS.Resources.Labels.Subtotal + "-" + InnoSoft.LS.Resources.Labels.GrossProfitRate;
colSubtotalGrossProfitRate.DataField = "SubtotalGrossProfitRate";
var grid = new GridView();
grid.Columns.Add(colOrganName);
grid.Columns.Add(colProvinceName);
grid.Columns.Add(colJoinInTunnages);
grid.Columns.Add(colSelfSupportTunnages);
grid.Columns.Add(colPrestowageTunnages);
grid.Columns.Add(colSubtotalTunnages);
grid.Columns.Add(colJoinInPiles);
grid.Columns.Add(colSelfSupportPiles);
grid.Columns.Add(colPrestowagePiles);
grid.Columns.Add(colSubtotalPiles);
grid.Columns.Add(colJoinInTransportCharges);/*10*/
grid.Columns.Add(colSelfSupportTransportCharges);
grid.Columns.Add(colPrestowageTransportCharges);
grid.Columns.Add(colSubtotalTransportCharges);
grid.Columns.Add(colJoinInTransportChargesDifference);
grid.Columns.Add(colSelfSupportTransportChargesDifference);
grid.Columns.Add(colPrestowageTransportChargesDifference);
grid.Columns.Add(colSubtotalTransportChargesDifference);/*17*/
grid.Columns.Add(colJoinInGrossProfitRate);
grid.Columns.Add(colSelfSupportGrossProfitRate);
grid.Columns.Add(colPrestowageGrossProfitRate);
grid.Columns.Add(colSubtotalGrossProfitRate);
grid.AutoGenerateColumns = false;
grid.RowDataBound += new GridViewRowEventHandler(OrganTotalOutputGrid_RowDataBound);
grid.DataSource = from s in listStat
select new
{
OrganName = s.OrganName,
ProvinceName = s.ProvinceName,
JoinInTunnages = s.JoinInTunnages != 0 ? s.JoinInTunnages.ToString("#0.######") : string.Empty,
SelfSupportTunnages = s.SelfSupportTunnages != 0 ? s.SelfSupportTunnages.ToString("#0.######") : string.Empty,
PrestowageTunnages = s.PrestowageTunnages != 0 ? s.PrestowageTunnages.ToString("#0.######") : string.Empty,
SubtotalTunnages = s.SubtotalTunnages != 0 ? s.SubtotalTunnages.ToString("#0.######") : string.Empty,
JoinInPiles = s.JoinInPiles != 0 ? s.JoinInPiles.ToString("#0.######") : string.Empty,
SelfSupportPiles = s.SelfSupportPiles != 0 ? s.SelfSupportPiles.ToString("#0.######") : string.Empty,
PrestowagePiles = s.PrestowagePiles != 0 ? s.PrestowagePiles.ToString("#0.######") : string.Empty,
SubtotalPiles = s.SubtotalPiles != 0 ? s.SubtotalPiles.ToString("#0.######") : string.Empty,
JoinInTransportCharges = s.JoinInTransportCharges != 0 ? s.JoinInTransportCharges.ToString("N") : string.Empty,
SelfSupportTransportCharges = s.SelfSupportTransportCharges != 0 ? s.SelfSupportTransportCharges.ToString("N") : string.Empty,
PrestowageTransportCharges = s.PrestowageTransportCharges != 0 ? s.PrestowageTransportCharges.ToString("N") : string.Empty,
SubtotalTransportCharges = s.SubtotalTransportCharges != 0 ? s.SubtotalTransportCharges.ToString("N") : string.Empty,
JoinInTransportChargesDifference = s.JoinInTransportChargesDifference != 0 ? s.JoinInTransportChargesDifference.ToString("N") : string.Empty,
SelfSupportTransportChargesDifference = s.SelfSupportTransportChargesDifference != 0 ? s.SelfSupportTransportChargesDifference.ToString("N") : string.Empty,
PrestowageTransportChargesDifference = s.PrestowageTransportChargesDifference != 0 ? s.PrestowageTransportChargesDifference.ToString("N") : string.Empty,
SubtotalTransportChargesDifference = s.SubtotalTransportChargesDifference != 0 ? s.SubtotalTransportChargesDifference.ToString("N") : string.Empty,
JoinInGrossProfitRate = s.JoinInGrossProfitRate != 0 ? s.JoinInGrossProfitRate.ToString("N") : string.Empty,
SelfSupportGrossProfitRate = s.SelfSupportGrossProfitRate != 0 ? s.SelfSupportGrossProfitRate.ToString("N") : string.Empty,
PrestowageGrossProfitRate = s.PrestowageGrossProfitRate != 0 ? s.PrestowageGrossProfitRate.ToString("N") : string.Empty,
SubtotalGrossProfitRate = s.SubtotalGrossProfitRate != 0 ? s.SubtotalGrossProfitRate.ToString("N") : string.Empty
};
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=OrganTotalOutput.xls");
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
#endregion
return View("StatOrganTotalOutput");
}
public JsonResult LoadSyntheticalSearchGrid(string sidx, string sord, int page, int rows, string startTime, string endTime, string payerName, string startCountry, string startProvince, string startCity, string destCountry, string destProvince, string destCity, string carNo, string organId)
{
//读取数据
string strErrText;
DeliverSystem deliver = new DeliverSystem();
List<DeliverBill> listBill = deliver.LoadDeliverBillsByConditions(startTime, endTime, string.Empty, string.Empty, payerName, startCountry, startProvince, startCity, destCountry, destProvince, destCity, carNo, string.Empty, organId, string.Empty, LoginAccountId, LoginStaffName, out strErrText);
if (listBill == null)
{
throw new Exception(strErrText);
}
//根据结算公式计算运费
foreach (DeliverBill bill in listBill)
{
if (bill.CustomerTransportCharges == 0 && bill.SettlementExpression != null && bill.SettlementExpression != string.Empty)
{
try
{
EvaluatorHelper evaluator = new EvaluatorHelper();
evaluator.SetExpression(bill.SettlementExpression);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.KM, bill.KM == null || bill.KM == string.Empty ? "0" : bill.KM);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Tunnages, bill.TotalTunnages.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Piles, bill.TotalPiles.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportPrice, bill.CustomerTransportPrice.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportCharges, "0");
string strTransportCharges = evaluator.EvaluateExpression();
bill.CustomerTransportCharges = decimal.Parse(strTransportCharges);
}
catch (Exception e)
{
throw e;
}
}
}
//计算拼车费
var grpShipmentNos = listBill.GroupBy(s => s.ShipmentNo).OrderBy(s => s.Key);
foreach (var grpShipmentNo in grpShipmentNos)
{
if (grpShipmentNo.Key != null && grpShipmentNo.Key != string.Empty)
{
List<DeliverBill> listShipmentNoDetail = grpShipmentNo.ToList<DeliverBill>();
int i = 0;
while (i < listShipmentNoDetail.Count)
{
if (i > 0)
{
int j = 0;
while (j < i)
{
if (listShipmentNoDetail[i].ReceiverAddress == listShipmentNoDetail[j].ReceiverAddress)
{
break;
}
j++;
}
if (j >= i)
{
listShipmentNoDetail[i].CarpoolFee = 100;
}
}
i++;
}
}
}
//提取当前页面数据
int nTotalRows = listBill.Count;
int nPageIndex = page;
int nPageSize = rows;
int nTotalPages = nTotalRows / nPageSize;
if (nTotalRows % nPageSize > 0)
nTotalPages++;
string sortExpression = (sidx ?? "CreateTime") + " " + (sord ?? "ASC");
var data = listBill.OrderBy(sortExpression).Skip((nPageIndex - 1) * nPageSize).Take(nPageSize).ToList();
//生成表格数据
var ret = new
{
total = nTotalPages,
page = nPageIndex,
records = nTotalRows,
rows = (
from b in data
select new
{
id = b.Id,
cell = new string[] {
b.Id.ToString(),
b.ShipmentBillId.ToString(),
b.PlanId.ToString(),
b.CreateTime.ToString("yyyy-MM-dd"),
b.PlanNo,
b.CustomerName,
b.PayerId.ToString(),
b.PayerName,
b.ShipmentNo,
b.DeliveryNo,
b.ReceiverName,
b.ReceiverCountry + b.ReceiverProvince + b.ReceiverCity + b.ReceiverAddress,
b.StartCity,
b.ReceiverCity,
b.ReceiveType,
b.GoodsName,
b.TotalPackages.ToString(),
b.TotalTunnages.ToString("#0.######"),
b.TotalPiles.ToString("#0.######"),
b.TotalTenThousands.ToString("#0.######"),
b.CarNo,
b.TrailerNo,
b.CarType,
b.BillNo,
b.ContractId.ToString(),
b.ContractNo,
b.OriginalContractNo,
b.TransportCharges.ToString(),
(b.CustomerTransportCharges + b.CarpoolFee + b.RiverCrossingCharges).ToString(),
(b.CustomerTransportCharges + b.CarpoolFee + b.RiverCrossingCharges - b.TransportCharges).ToString(),
b.IsCustomerTransportChargesSettled.ToString(),
b.IsCarrierTransportChargesSettled.ToString(),
b.IsDeliverBillReceiptReceived.ToString(),
b.IsDeliverBillReceiptReceived ? b.ReturnTime.ToString("yyyy-MM-dd") : string.Empty,
b.ReverseAmount.ToString(),
b.TransportChargesBalance.ToString(),
b.Remark
}
}).ToArray(),
userdata = new
{
CreateTime = InnoSoft.LS.Resources.Labels.Total,
TotalPackages = data.Sum(s => s.TotalPackages),
TotalTunnages = data.Sum(s => s.TotalTunnages),
TotalPiles = data.Sum(s => s.TotalPiles),
TotalTenThousands = data.Sum(s => s.TotalTenThousands),
TransportCharges = data.Sum(s => s.TransportCharges),
CustomerTransportCharges = data.Sum(s => s.CustomerTransportCharges + s.CarpoolFee + s.RiverCrossingCharges),
TransportChargesDifference = data.Sum(s => s.CustomerTransportCharges + s.CarpoolFee + s.RiverCrossingCharges - s.TransportCharges),
ReverseAmount = data.Sum(s => s.ReverseAmount),
TransportChargesBalance = data.Sum(s => s.TransportChargesBalance)
}
};
return Json(ret, JsonRequestBehavior.AllowGet);
}
public JsonResult LoadStatOrganTotalOutputGrid(string sidx, string sord, int page, int rows, string startTime, string endTime, string organId)
{
#region 读取明细数据
string strErrText;
StatisticsSystem stat = new StatisticsSystem();
List<OrganTotalOutputDetail> listDetail = stat.LoadOrganTotalOutputDetailsByConditions(startTime, endTime, organId, LoginAccountId, LoginStaffName, out strErrText);
if (listDetail == null)
{
throw new Exception(strErrText);
}
#endregion
#region 根据结算公式计算运费
{
foreach (OrganTotalOutputDetail detail in listDetail)
{
if (detail.CustomerTransportCharges == 0 && detail.CustomerSettlementExpression != null && detail.CustomerSettlementExpression != string.Empty)
{
try
{
EvaluatorHelper evaluator = new EvaluatorHelper();
evaluator.SetExpression(detail.CustomerSettlementExpression);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.KM, detail.KM == null || detail.KM == string.Empty ? "0" : detail.KM);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Tunnages, detail.TotalTunnages.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Piles, detail.TotalPiles.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportPrice, detail.CustomerTransportPrice.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportCharges, "0");
string strTransportCharges = evaluator.EvaluateExpression();
detail.CustomerTransportCharges = decimal.Parse(strTransportCharges);
}
catch (Exception e)
{
throw e;
}
}
}
}
#endregion
#region 计算拼车费
{
var grpShipmentNos = listDetail.GroupBy(d => d.ShipmentNo).OrderBy(d => d.Key);
foreach (var grpShipmentNo in grpShipmentNos)
{
if (grpShipmentNo.Key != null && grpShipmentNo.Key != string.Empty)
{
List<OrganTotalOutputDetail> listShipmentNoDetail = grpShipmentNo.ToList<OrganTotalOutputDetail>();
int i = 0;
while (i < listShipmentNoDetail.Count)
{
if (i > 0)
{
int j = 0;
while (j < i)
{
if (listShipmentNoDetail[i].ReceiverAddress == listShipmentNoDetail[j].ReceiverAddress)
{
break;
}
j++;
}
if (j >= i)
{
listShipmentNoDetail[i].CustomerCarpoolFee = 100;
}
}
i++;
}
}
}
}
#endregion
#region 生成表格数据源
List<StatOrganTotalOutput> listStat = new List<StatOrganTotalOutput>();
{
//按办事处分组
var grpOrganNames = listDetail.GroupBy(d => d.OwnOrganName).OrderBy(d => d.Key);
foreach (var grpOrganName in grpOrganNames)
{
List<OrganTotalOutputDetail> listOrganNameDetail = grpOrganName.ToList<OrganTotalOutputDetail>();
//再按省份分组
var grpReceiverProvinces = listOrganNameDetail.GroupBy(d => d.ReceiverProvince).OrderBy(d => d.Key);
foreach (var grpReceiverProvince in grpReceiverProvinces)
{
List<OrganTotalOutputDetail> listReceiverProvinceDetail = grpReceiverProvince.ToList<OrganTotalOutputDetail>();
//生成表格数据行
StatOrganTotalOutput s = new StatOrganTotalOutput();
s.Id = listStat.Count + 1;
s.OrganName = grpOrganName.Key;
s.ProvinceName = grpReceiverProvince.Key;
s.JoinInTunnages = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalTunnages);
s.SelfSupportTunnages = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalTunnages);
s.PrestowageTunnages = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.TotalTunnages);
s.SubtotalTunnages = listReceiverProvinceDetail.Sum(d => d.TotalTunnages);
s.JoinInPiles = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalPiles);
s.SelfSupportPiles = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalPiles);
s.PrestowagePiles = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.TotalPiles);
s.SubtotalPiles = listReceiverProvinceDetail.Sum(d => d.TotalPiles);
s.JoinInTransportCharges = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SelfSupportTransportCharges = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.PrestowageTransportCharges = listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SubtotalTransportCharges = listReceiverProvinceDetail.Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.JoinInTransportChargesDifference = s.JoinInTransportCharges - listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CarrierTransportCharges);
s.SelfSupportTransportChargesDifference = s.SelfSupportTransportCharges - listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CarrierTransportCharges);
s.PrestowageTransportChargesDifference = s.PrestowageTransportCharges - listReceiverProvinceDetail.FindAll(delegate(OrganTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.CarrierTransportCharges);
s.SubtotalTransportChargesDifference = s.SubtotalTransportCharges - listReceiverProvinceDetail.Sum(d => d.CarrierTransportCharges);
s.JoinInGrossProfitRate = s.JoinInTransportCharges != 0 ? s.JoinInTransportChargesDifference / s.JoinInTransportCharges : 0;
s.SelfSupportGrossProfitRate = s.SelfSupportTransportCharges != 0 ? s.SelfSupportTransportChargesDifference / s.SelfSupportTransportCharges : 0;
s.PrestowageGrossProfitRate = s.PrestowageTransportCharges != 0 ? s.PrestowageTransportChargesDifference / s.PrestowageTransportCharges : 0;
s.SubtotalGrossProfitRate = s.SubtotalTransportCharges != 0 ? s.SubtotalTransportChargesDifference / s.SubtotalTransportCharges : 0;
listStat.Add(s);
}
}
}
#endregion
//提取当前页面数据
int nTotalRows = listStat.Count;
int nPageIndex = page;
int nPageSize = rows;
int nTotalPages = nTotalRows / nPageSize;
if (nTotalRows % nPageSize > 0)
nTotalPages++;
string sortExpression = (sidx ?? "ProvinceName") + " " + (sord ?? "ASC");
var data = listStat.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.OrganName,
s.ProvinceName,
s.JoinInTunnages.ToString("#0.######"),
s.SelfSupportTunnages.ToString("#0.######"),
s.PrestowageTunnages.ToString("#0.######"),
s.SubtotalTunnages.ToString("#0.######"),
s.JoinInPiles.ToString("#0.######"),
s.SelfSupportPiles.ToString("#0.######"),
s.PrestowagePiles.ToString("#0.######"),
s.SubtotalPiles.ToString("#0.######"),
s.JoinInTransportCharges.ToString(),
s.SelfSupportTransportCharges.ToString(),
s.PrestowageTransportCharges.ToString(),
s.SubtotalTransportCharges.ToString(),
s.JoinInTransportChargesDifference.ToString(),
s.SelfSupportTransportChargesDifference.ToString(),
s.PrestowageTransportChargesDifference.ToString(),
s.SubtotalTransportChargesDifference.ToString(),
s.JoinInGrossProfitRate.ToString(),
s.SelfSupportGrossProfitRate.ToString(),
s.PrestowageGrossProfitRate.ToString(),
s.SubtotalGrossProfitRate.ToString()
}
}).ToArray(),
userdata = new
{
OrganName = InnoSoft.LS.Resources.Labels.Total,
JoinInTunnages = data.Sum(s => s.JoinInTunnages),
SelfSupportTunnages = data.Sum(s => s.SelfSupportTunnages),
PrestowageTunnages = data.Sum(s => s.PrestowageTunnages),
SubtotalTunnages = data.Sum(s => s.SubtotalTunnages),
JoinInPiles = data.Sum(s => s.JoinInPiles),
SelfSupportPiles = data.Sum(s => s.SelfSupportPiles),
PrestowagePiles = data.Sum(s => s.PrestowagePiles),
SubtotalPiles = data.Sum(s => s.SubtotalPiles),
JoinInTransportCharges = data.Sum(s => s.JoinInTransportCharges),
SelfSupportTransportCharges = data.Sum(s => s.SelfSupportTransportCharges),
PrestowageTransportCharges = data.Sum(s => s.PrestowageTransportCharges),
SubtotalTransportCharges = data.Sum(s => s.SubtotalTransportCharges),
JoinInTransportChargesDifference = data.Sum(s => s.JoinInTransportChargesDifference),
SelfSupportTransportChargesDifference = data.Sum(s => s.SelfSupportTransportChargesDifference),
PrestowageTransportChargesDifference = data.Sum(s => s.PrestowageTransportChargesDifference),
SubtotalTransportChargesDifference = data.Sum(s => s.SubtotalTransportChargesDifference),
JoinInGrossProfitRate = data.Sum(s => s.JoinInTransportCharges) != 0 ? data.Sum(s => s.JoinInTransportChargesDifference) / data.Sum(s => s.JoinInTransportCharges) : 0,
SelfSupportGrossProfitRate = data.Sum(s => s.SelfSupportTransportCharges) != 0 ? data.Sum(s => s.SelfSupportTransportChargesDifference) / data.Sum(s => s.SelfSupportTransportCharges) : 0,
PrestowageGrossProfitRate = data.Sum(s => s.PrestowageTransportCharges) != 0 ? data.Sum(s => s.PrestowageTransportChargesDifference) / data.Sum(s => s.PrestowageTransportCharges) : 0,
SubtotalGrossProfitRate = data.Sum(s => s.SubtotalTransportCharges) != 0 ? data.Sum(s => s.SubtotalTransportChargesDifference) / data.Sum(s => s.SubtotalTransportCharges) : 0
}
};
return Json(ret, JsonRequestBehavior.AllowGet);
}
public ActionResult ExportSyntheticalSearch()
{
string strErrText;
#region 提取参数
var request = HttpContext.Request;
string strStartTime = request.QueryString["startTime"] ?? string.Empty;
string strEndTime = request.QueryString["endTime"] ?? string.Empty;
string strPayerName = request.QueryString["payerName"] ?? 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 strCarNo = request.QueryString["carNo"] ?? string.Empty;
string strOrganId = request.QueryString["organId"] ?? string.Empty;
#endregion
#region 读取明细数据
DeliverSystem deliver = new DeliverSystem();
List<DeliverBill> listBill = deliver.LoadDeliverBillsByConditions(strStartTime, strEndTime, string.Empty, string.Empty, strPayerName, strStartCountry, strStartProvince, strStartCity, strDestCountry, strDestProvince, strDestCity, strCarNo, string.Empty, strOrganId, string.Empty, LoginAccountId, LoginStaffName, out strErrText);
if (listBill == null)
{
throw new Exception(strErrText);
}
#endregion
#region 根据结算公式计算运费
{
foreach (DeliverBill bill in listBill)
{
if (bill.CustomerTransportCharges == 0 && bill.SettlementExpression != null && bill.SettlementExpression != string.Empty)
{
try
{
EvaluatorHelper evaluator = new EvaluatorHelper();
evaluator.SetExpression(bill.SettlementExpression);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.KM, bill.KM == null || bill.KM == string.Empty ? "0" : bill.KM);
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Tunnages, bill.TotalTunnages.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Piles, bill.TotalPiles.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportPrice, bill.CustomerTransportPrice.ToString());
evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportCharges, "0");
string strTransportCharges = evaluator.EvaluateExpression();
bill.CustomerTransportCharges = decimal.Parse(strTransportCharges);
}
catch (Exception e)
{
throw e;
}
}
}
}
#endregion
#region 计算拼车费
{
var grpShipmentNos = listBill.GroupBy(s => s.ShipmentNo).OrderBy(s => s.Key);
foreach (var grpShipmentNo in grpShipmentNos)
{
if (grpShipmentNo.Key != null && grpShipmentNo.Key != string.Empty)
{
List<DeliverBill> listShipmentNoDetail = grpShipmentNo.ToList<DeliverBill>();
int i = 0;
while (i < listShipmentNoDetail.Count)
{
if (i > 0)
{
int j = 0;
while (j < i)
{
if (listShipmentNoDetail[i].ReceiverAddress == listShipmentNoDetail[j].ReceiverAddress)
{
break;
}
j++;
}
if (j >= i)
{
listShipmentNoDetail[i].CarpoolFee = 100;
}
}
i++;
}
}
}
}
#endregion
#region 输出Excel
//生成GridView
BoundField colCreateTime = new BoundField();
colCreateTime.HeaderText = InnoSoft.LS.Resources.Labels.DeliverDate;
colCreateTime.DataField = "CreateTime";
BoundField colPlanNo = new BoundField();
colPlanNo.HeaderText = InnoSoft.LS.Resources.Labels.PlanNo;
colPlanNo.DataField = "PlanNo";
BoundField colCustomerName = new BoundField();
colCustomerName.HeaderText = InnoSoft.LS.Resources.Labels.CustomerName;
colCustomerName.DataField = "CustomerName";
BoundField colPayerName = new BoundField();
colPayerName.HeaderText = InnoSoft.LS.Resources.Labels.PayerName;
colPayerName.DataField = "PayerName";
BoundField colShipmentNo = new BoundField();
colShipmentNo.HeaderText = InnoSoft.LS.Resources.Labels.ShipmentNo;
colShipmentNo.DataField = "ShipmentNo";
BoundField colDeliveryNo = new BoundField();
colDeliveryNo.HeaderText = InnoSoft.LS.Resources.Labels.DeliveryNo;
colDeliveryNo.DataField = "DeliveryNo";
BoundField colReceiverName = new BoundField();
colReceiverName.HeaderText = InnoSoft.LS.Resources.Labels.ReceiverName;
colReceiverName.DataField = "ReceiverName";
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 colReceiveType = new BoundField();
colReceiveType.HeaderText = InnoSoft.LS.Resources.Labels.ReceiveType;
colReceiveType.DataField = "ReceiveType";
BoundField colGoodsName = new BoundField();
colGoodsName.HeaderText = InnoSoft.LS.Resources.Labels.GoodsName;
colGoodsName.DataField = "GoodsName";
BoundField colTotalPackages = new BoundField();
colTotalPackages.HeaderText = InnoSoft.LS.Resources.Labels.Pieces;
colTotalPackages.DataField = "TotalPackages";
BoundField colTotalTunnages = new BoundField();
colTotalTunnages.HeaderText = InnoSoft.LS.Resources.Labels.Tunnages;
colTotalTunnages.DataField = "TotalTunnages";
BoundField colTotalPiles = new BoundField();
colTotalPiles.HeaderText = InnoSoft.LS.Resources.Labels.Piles;
colTotalPiles.DataField = "TotalPiles";
BoundField colTotalTenThousands = new BoundField();
colTotalTenThousands.HeaderText = InnoSoft.LS.Resources.Labels.TenThousands;
colTotalTenThousands.DataField = "TotalTenThousands";
BoundField colCarNo = new BoundField();
colCarNo.HeaderText = InnoSoft.LS.Resources.Labels.CarNo;
colCarNo.DataField = "CarNo";
BoundField colTrailerNo = new BoundField();
colTrailerNo.HeaderText = InnoSoft.LS.Resources.Labels.TrailerNo;
colTrailerNo.DataField = "TrailerNo";
BoundField colCarType = new BoundField();
colCarType.HeaderText = InnoSoft.LS.Resources.Labels.CarType;
colCarType.DataField = "CarType";
BoundField colDeliverBillNo = new BoundField();
colDeliverBillNo.HeaderText = InnoSoft.LS.Resources.Labels.DeliverBillNo;
colDeliverBillNo.DataField = "BillNo";
BoundField colContractNo = new BoundField();
colContractNo.HeaderText = InnoSoft.LS.Resources.Labels.ContractNo;
colContractNo.DataField = "ContractNo";
BoundField colOriginalContractNo = new BoundField();
colOriginalContractNo.HeaderText = InnoSoft.LS.Resources.Labels.OriginalContractNo;
colOriginalContractNo.DataField = "OriginalContractNo";
BoundField colCarrierTransportCharges = new BoundField();
colCarrierTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.CarrierTransportCharges;
colCarrierTransportCharges.DataField = "TransportCharges";
BoundField colCustomerTransportCharges = new BoundField();
colCustomerTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.CustomerTransportCharges;
colCustomerTransportCharges.DataField = "CustomerTransportCharges";
BoundField colTransportChargesDifference = new BoundField();
colTransportChargesDifference.HeaderText = InnoSoft.LS.Resources.Labels.TransportChargesDifference;
colTransportChargesDifference.DataField = "TransportChargesDifference";
BoundField colIsCustomerTransportChargesSettled = new BoundField();
colIsCustomerTransportChargesSettled.HeaderText = InnoSoft.LS.Resources.Labels.IsCustomerTransportChargesSettled;
colIsCustomerTransportChargesSettled.DataField = "IsCustomerTransportChargesSettled";
BoundField colIsCarrierTransportChargesSettled = new BoundField();
colIsCarrierTransportChargesSettled.HeaderText = InnoSoft.LS.Resources.Labels.IsCarrierTransportChargesSettled;
colIsCarrierTransportChargesSettled.DataField = "IsCarrierTransportChargesSettled";
BoundField colIsDeliverBillReceiptReceived = new BoundField();
colIsDeliverBillReceiptReceived.HeaderText = InnoSoft.LS.Resources.Labels.IsReceiptReceived;
colIsDeliverBillReceiptReceived.DataField = "IsDeliverBillReceiptReceived";
BoundField colReturnTime = new BoundField();
colReturnTime.HeaderText = InnoSoft.LS.Resources.Labels.ReturnTime;
colReturnTime.DataField = "ReturnTime";
BoundField colReverseAmount = new BoundField();
colReverseAmount.HeaderText = InnoSoft.LS.Resources.Labels.ReverseAmount;
colReverseAmount.DataField = "ReverseAmount";
BoundField colTransportChargesBalance = new BoundField();
colTransportChargesBalance.HeaderText = InnoSoft.LS.Resources.Labels.TransportChargesBalance;
colTransportChargesBalance.DataField = "TransportChargesBalance";
BoundField colRemark = new BoundField();
colRemark.HeaderText = InnoSoft.LS.Resources.Labels.Remark;
colRemark.DataField = "Remark";
var grid = new GridView();
grid.Columns.Add(colCreateTime);
grid.Columns.Add(colPlanNo);
grid.Columns.Add(colCustomerName);
grid.Columns.Add(colPayerName);
grid.Columns.Add(colShipmentNo);
grid.Columns.Add(colDeliveryNo);
grid.Columns.Add(colReceiverName);
grid.Columns.Add(colStartCity);
grid.Columns.Add(colReceiverCity);
grid.Columns.Add(colReceiveType);
grid.Columns.Add(colGoodsName);
grid.Columns.Add(colTotalPackages);
grid.Columns.Add(colTotalTunnages);
grid.Columns.Add(colTotalPiles);
grid.Columns.Add(colTotalTenThousands);
grid.Columns.Add(colCarNo);
grid.Columns.Add(colTrailerNo);
grid.Columns.Add(colCarType);
grid.Columns.Add(colDeliverBillNo);
grid.Columns.Add(colContractNo);
grid.Columns.Add(colOriginalContractNo);
grid.Columns.Add(colCarrierTransportCharges);
grid.Columns.Add(colCustomerTransportCharges);
grid.Columns.Add(colTransportChargesDifference);
grid.Columns.Add(colIsCustomerTransportChargesSettled);
grid.Columns.Add(colIsCarrierTransportChargesSettled);
grid.Columns.Add(colIsDeliverBillReceiptReceived);
grid.Columns.Add(colReturnTime);
grid.Columns.Add(colReverseAmount);
grid.Columns.Add(colTransportChargesBalance);
grid.Columns.Add(colRemark);
grid.AutoGenerateColumns = false;
grid.RowDataBound += new GridViewRowEventHandler(SyntheticalSearchGrid_RowDataBound);
grid.DataSource = from b in listBill
select new
{
CreateTime = b.CreateTime.ToString("yyyy-MM-dd"),
PlanNo = b.PlanNo,
CustomerName = b.CustomerName,
PayerName = b.PayerName,
ShipmentNo = b.ShipmentNo,
DeliveryNo = b.DeliveryNo,
ReceiverName = b.ReceiverName,
StartCity = b.StartCity,
ReceiverCity = b.ReceiverCity,
ReceiveType = b.ReceiveType,
GoodsName = b.GoodsName,
TotalPackages = b.TotalPackages != 0 ? b.TotalPackages.ToString() : string.Empty,
TotalTunnages = b.TotalTunnages != 0 ? b.TotalTunnages.ToString("#0.######") : string.Empty,
TotalPiles = b.TotalPiles != 0 ? b.TotalPiles.ToString("#0.######") : string.Empty,
TotalTenThousands = b.TotalTenThousands != 0 ? b.TotalTenThousands.ToString("#0.######") : string.Empty,
CarNo = b.CarNo,
TrailerNo = b.TrailerNo,
CarType = b.CarType,
BillNo = b.BillNo,
ContractNo = b.ContractNo,
OriginalContractNo = b.OriginalContractNo,
TransportCharges = b.TransportCharges != 0 ? b.TransportCharges.ToString("N") : string.Empty,
CustomerTransportCharges = (b.CustomerTransportCharges + b.CarpoolFee + b.RiverCrossingCharges) != 0 ? (b.CustomerTransportCharges + b.CarpoolFee + b.RiverCrossingCharges).ToString("N") : string.Empty,
TransportChargesDifference = (b.CustomerTransportCharges + b.CarpoolFee + b.RiverCrossingCharges - b.TransportCharges) != 0 ? (b.CustomerTransportCharges + b.CarpoolFee + b.RiverCrossingCharges - b.TransportCharges).ToString("N") : string.Empty,
IsCustomerTransportChargesSettled = b.IsCustomerTransportChargesSettled ? InnoSoft.LS.Resources.Options.Yes : InnoSoft.LS.Resources.Options.No,
IsCarrierTransportChargesSettled = b.IsCarrierTransportChargesSettled ? InnoSoft.LS.Resources.Options.Yes : InnoSoft.LS.Resources.Options.No,
IsDeliverBillReceiptReceived = b.IsDeliverBillReceiptReceived ? InnoSoft.LS.Resources.Options.Yes : InnoSoft.LS.Resources.Options.No,
ReturnTime = b.IsDeliverBillReceiptReceived ? b.ReturnTime.ToString("yyyy-MM-dd") : string.Empty,
ReverseAmount = b.ReverseAmount != 0 ? b.ReverseAmount.ToString("N") : string.Empty,
TransportChargesBalance = b.TransportChargesBalance != 0 ? b.TransportChargesBalance.ToString("N") : string.Empty,
Remark = b.Remark
};
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=SyntheticalSearch.xls");
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
#endregion
return View("SyntheticalSearch");
}
