public ActionResult ExportCustomerTotalOutput()
{
string strErrText;
#region 提取参数
var request = HttpContext.Request;
string strStartTime = request.QueryString["startTime"] ?? string.Empty;
string strEndTime = request.QueryString["endTime"] ?? string.Empty;
string strCustomerName = request.QueryString["customerName"] ?? string.Empty;
#endregion
#region 读取明细数据
StatisticsSystem stat = new StatisticsSystem();
List<CustomerTotalOutputDetail> listDetail = stat.LoadCustomerTotalOutputDetailsByConditions(strStartTime, strEndTime, strCustomerName, LoginAccountId, LoginStaffName, out strErrText);
if (listDetail == null)
{
throw new Exception(strErrText);
}
#endregion
#region 根据结算公式计算运费
{
foreach (CustomerTotalOutputDetail 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<CustomerTotalOutputDetail> listShipmentNoDetail = grpShipmentNo.ToList<CustomerTotalOutputDetail>();
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<StatCustomerTotalOutput> listStat = new List<StatCustomerTotalOutput>();
{
//按客户名称分组
var grpCustomerNames = listDetail.GroupBy(d => d.CustomerName).OrderBy(d => d.Key);
foreach (var grpCustomerName in grpCustomerNames)
{
List<CustomerTotalOutputDetail> listCustomerNameDetail = grpCustomerName.ToList<CustomerTotalOutputDetail>();
//再按省份分组
var grpReceiverProvinces = listCustomerNameDetail.GroupBy(d => d.ReceiverProvince).OrderBy(d => d.Key);
foreach (var grpReceiverProvince in grpReceiverProvinces)
{
List<CustomerTotalOutputDetail> listReceiverProvinceDetail = grpReceiverProvince.ToList<CustomerTotalOutputDetail>();
//生成表格数据行
StatCustomerTotalOutput s = new StatCustomerTotalOutput();
s.Id = listStat.Count + 1;
s.CustomerName = grpCustomerName.Key;
s.ProvinceName = grpReceiverProvince.Key;
s.JoinInTunnages = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalTunnages);
s.SelfSupportTunnages = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalTunnages);
s.PrestowageTunnages = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail 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(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalPiles);
s.SelfSupportPiles = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalPiles);
s.PrestowagePiles = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail 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(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SelfSupportTransportCharges = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.PrestowageTransportCharges = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail 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(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CarrierTransportCharges);
s.SelfSupportTransportChargesDifference = s.SelfSupportTransportCharges - listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CarrierTransportCharges);
s.PrestowageTransportChargesDifference = s.PrestowageTransportCharges - listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail 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);
}
//小计
{
StatCustomerTotalOutput s = new StatCustomerTotalOutput();
s.Id = listStat.Count + 1;
s.CustomerName = InnoSoft.LS.Resources.Labels.Subtotal;
s.ProvinceName = string.Empty;
s.JoinInTunnages = listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalTunnages);
s.SelfSupportTunnages = listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalTunnages);
s.PrestowageTunnages = listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.TotalTunnages);
s.SubtotalTunnages = listCustomerNameDetail.Sum(d => d.TotalTunnages);
s.JoinInPiles = listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalPiles);
s.SelfSupportPiles = listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalPiles);
s.PrestowagePiles = listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.TotalPiles);
s.SubtotalPiles = listCustomerNameDetail.Sum(d => d.TotalPiles);
s.JoinInTransportCharges = listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SelfSupportTransportCharges = listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.PrestowageTransportCharges = listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SubtotalTransportCharges = listCustomerNameDetail.Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.JoinInTransportChargesDifference = s.JoinInTransportCharges - listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CarrierTransportCharges);
s.SelfSupportTransportChargesDifference = s.SelfSupportTransportCharges - listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CarrierTransportCharges);
s.PrestowageTransportChargesDifference = s.PrestowageTransportCharges - listCustomerNameDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.Prestowage; }).Sum(d => d.CarrierTransportCharges);
s.SubtotalTransportChargesDifference = s.SubtotalTransportCharges - listCustomerNameDetail.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);
}
}
//总计
{
StatCustomerTotalOutput s = new StatCustomerTotalOutput();
s.Id = listStat.Count + 1;
s.CustomerName = InnoSoft.LS.Resources.Labels.Total;
s.ProvinceName = string.Empty;
s.JoinInTunnages = listDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalTunnages);
s.SelfSupportTunnages = listDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalTunnages);
s.PrestowageTunnages = listDetail.FindAll(delegate(CustomerTotalOutputDetail 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(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalPiles);
s.SelfSupportPiles = listDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalPiles);
s.PrestowagePiles = listDetail.FindAll(delegate(CustomerTotalOutputDetail 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(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SelfSupportTransportCharges = listDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.PrestowageTransportCharges = listDetail.FindAll(delegate(CustomerTotalOutputDetail 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(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CarrierTransportCharges);
s.SelfSupportTransportChargesDifference = s.SelfSupportTransportCharges - listDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CarrierTransportCharges);
s.PrestowageTransportChargesDifference = s.PrestowageTransportCharges - listDetail.FindAll(delegate(CustomerTotalOutputDetail 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 colCustomerName = new BoundField();
colCustomerName.HeaderText = InnoSoft.LS.Resources.Labels.CustomerName;
colCustomerName.DataField = "CustomerName";
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(colCustomerName);
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);
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);
grid.Columns.Add(colJoinInGrossProfitRate);
grid.Columns.Add(colSelfSupportGrossProfitRate);
grid.Columns.Add(colPrestowageGrossProfitRate);
grid.Columns.Add(colSubtotalGrossProfitRate);
grid.AutoGenerateColumns = false;
grid.RowDataBound += new GridViewRowEventHandler(CustomerTotalOutputGrid_RowDataBound);
grid.DataSource = from s in listStat
select new
{
CustomerName = s.CustomerName,
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=CustomerTotalOutput.xls");
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
#endregion
return View("StatCustomerTotalOutput");
}
public JsonResult LoadStatCustomerTotalOutputGrid(string sidx, string sord, int page, int rows, string startTime, string endTime, string customerName)
{
#region 读取明细数据
string strErrText;
StatisticsSystem stat = new StatisticsSystem();
List<CustomerTotalOutputDetail> listDetail = stat.LoadCustomerTotalOutputDetailsByConditions(startTime, endTime, customerName, LoginAccountId, LoginStaffName, out strErrText);
if (listDetail == null)
{
throw new Exception(strErrText);
}
#endregion
#region 根据结算公式计算运费
{
foreach (CustomerTotalOutputDetail 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<CustomerTotalOutputDetail> listShipmentNoDetail = grpShipmentNo.ToList<CustomerTotalOutputDetail>();
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<StatCustomerTotalOutput> listStat = new List<StatCustomerTotalOutput>();
{
//按客户分组
var grpCustomerNames = listDetail.GroupBy(d => d.CustomerName).OrderBy(d => d.Key);
foreach (var grpCustomerName in grpCustomerNames)
{
List<CustomerTotalOutputDetail> listCustomerNameDetail = grpCustomerName.ToList<CustomerTotalOutputDetail>();
//再按省份分组
var grpReceiverProvinces = listCustomerNameDetail.GroupBy(d => d.ReceiverProvince).OrderBy(d => d.Key);
foreach (var grpReceiverProvince in grpReceiverProvinces)
{
List<CustomerTotalOutputDetail> listReceiverProvinceDetail = grpReceiverProvince.ToList<CustomerTotalOutputDetail>();
//生成表格数据行
StatCustomerTotalOutput s = new StatCustomerTotalOutput();
s.Id = listStat.Count + 1;
s.CustomerName = grpCustomerName.Key;
s.ProvinceName = grpReceiverProvince.Key;
s.JoinInTunnages = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalTunnages);
s.SelfSupportTunnages = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalTunnages);
s.PrestowageTunnages = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail 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(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.TotalPiles);
s.SelfSupportPiles = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.TotalPiles);
s.PrestowagePiles = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail 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(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.SelfSupportTransportCharges = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.PrestowageTransportCharges = listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail 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(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.JoinIn; }).Sum(d => d.CarrierTransportCharges);
s.SelfSupportTransportChargesDifference = s.SelfSupportTransportCharges - listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail d) { return d.CarrierBusinessType == InnoSoft.LS.Resources.Options.SelfSupport; }).Sum(d => d.CarrierTransportCharges);
s.PrestowageTransportChargesDifference = s.PrestowageTransportCharges - listReceiverProvinceDetail.FindAll(delegate(CustomerTotalOutputDetail 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.CustomerName,
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
{
CustomerName = 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);
}