public ActionResult ExportOrganGrossProfitRate()
{
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<OrganGrossProfitRateDetail> listDetail = stat.LoadOrganGrossProfitRateDetailsByConditions(strStartTime, strEndTime, strOrganId, LoginAccountId, LoginStaffName, out strErrText);
if (listDetail == null)
{
throw new Exception(strErrText);
}
#endregion
#region 根据结算公式计算运费
{
foreach (OrganGrossProfitRateDetail 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<OrganGrossProfitRateDetail> listShipmentNoDetail = grpShipmentNo.ToList<OrganGrossProfitRateDetail>();
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<StatOrganGrossProfitRate> listStat = new List<StatOrganGrossProfitRate>();
{
//按办事处分组
var grpOrganNames = listDetail.GroupBy(d => d.OwnOrganName).OrderBy(d => d.Key);
foreach (var grpOrganName in grpOrganNames)
{
List<OrganGrossProfitRateDetail> listOrganNameDetail = grpOrganName.ToList<OrganGrossProfitRateDetail>();
//再按起点分组
var grpStartPlaces = listOrganNameDetail.GroupBy(d => d.StartCity).OrderBy(d => d.Key);
foreach (var grpStartPlace in grpStartPlaces)
{
List<OrganGrossProfitRateDetail> listStartPlaceDetail = grpStartPlace.ToList<OrganGrossProfitRateDetail>();
//最后按讫点分组
var grpDestPlaces = listStartPlaceDetail.GroupBy(d => d.ReceiverCity).OrderBy(d => d.Key);
foreach (var grpDestPlace in grpDestPlaces)
{
List<OrganGrossProfitRateDetail> listDestPlaceDetail = grpDestPlace.ToList<OrganGrossProfitRateDetail>();
//生成表格数据行
StatOrganGrossProfitRate s = new StatOrganGrossProfitRate();
s.Id = listStat.Count + 1;
s.OrganName = grpOrganName.Key;
s.StartTime = strStartTime;
s.EndTime = strEndTime;
s.StartPlace = grpStartPlace.Key;
s.DestPlace = grpDestPlace.Key;
s.TotalCarrierTransportCharges = listDestPlaceDetail.Sum(d => d.CarrierTransportCharges);
s.TotalCustomerTransportCharges = listDestPlaceDetail.Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.TotalTransportChargesDifference = s.TotalCustomerTransportCharges - s.TotalCarrierTransportCharges;
s.TotalGrossProfitRate = s.TotalCustomerTransportCharges != 0 ? s.TotalTransportChargesDifference / s.TotalCustomerTransportCharges : 0;
listStat.Add(s);
}
}
}
}
#endregion
#region 输出Excel
//生成GridView
BoundField colOrganName = new BoundField();
colOrganName.HeaderText = InnoSoft.LS.Resources.Labels.OrganName;
colOrganName.DataField = "OrganName";
BoundField colStartTime = new BoundField();
colStartTime.HeaderText = InnoSoft.LS.Resources.Labels.StartTime;
colStartTime.DataField = "StartTime";
BoundField colEndTime = new BoundField();
colEndTime.HeaderText = InnoSoft.LS.Resources.Labels.EndTime;
colEndTime.DataField = "EndTime";
BoundField colStartPlace = new BoundField();
colStartPlace.HeaderText = InnoSoft.LS.Resources.Labels.StartPlace;
colStartPlace.DataField = "StartPlace";
BoundField colDestPlace = new BoundField();
colDestPlace.HeaderText = InnoSoft.LS.Resources.Labels.DestPlace;
colDestPlace.DataField = "DestPlace";
BoundField colTotalCarrierTransportCharges = new BoundField();
colTotalCarrierTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.TotalCarrierTransportCharges;
colTotalCarrierTransportCharges.DataField = "TotalCarrierTransportCharges";
BoundField colTotalCustomerTransportCharges = new BoundField();
colTotalCustomerTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.TotalCustomerTransportCharges;
colTotalCustomerTransportCharges.DataField = "TotalCustomerTransportCharges";
BoundField colTotalTransportChargesDifference = new BoundField();
colTotalTransportChargesDifference.HeaderText = InnoSoft.LS.Resources.Labels.TotalTransportChargesDifference;
colTotalTransportChargesDifference.DataField = "TotalTransportChargesDifference";
BoundField colTotalGrossProfitRate = new BoundField();
colTotalGrossProfitRate.HeaderText = InnoSoft.LS.Resources.Labels.TotalGrossProfitRate;
colTotalGrossProfitRate.DataField = "TotalGrossProfitRate";
var grid = new GridView();
grid.Columns.Add(colOrganName);
grid.Columns.Add(colStartTime);
grid.Columns.Add(colEndTime);
grid.Columns.Add(colStartPlace);
grid.Columns.Add(colDestPlace);
grid.Columns.Add(colTotalCarrierTransportCharges);
grid.Columns.Add(colTotalCustomerTransportCharges);
grid.Columns.Add(colTotalTransportChargesDifference);
grid.Columns.Add(colTotalGrossProfitRate);
grid.AutoGenerateColumns = false;
grid.RowDataBound += new GridViewRowEventHandler(OrganGrossProfitRateGrid_RowDataBound);
grid.DataSource = from s in listStat
select new
{
OrganName = s.OrganName,
StartTime = s.StartTime,
EndTime = s.EndTime,
StartPlace = s.StartPlace,
DestPlace = s.DestPlace,
TotalCarrierTransportCharges = s.TotalCarrierTransportCharges != 0 ? s.TotalCarrierTransportCharges.ToString("N") : string.Empty,
TotalCustomerTransportCharges = s.TotalCustomerTransportCharges != 0 ? s.TotalCustomerTransportCharges.ToString("N") : string.Empty,
TotalTransportChargesDifference = s.TotalTransportChargesDifference != 0 ? s.TotalTransportChargesDifference.ToString("N") : string.Empty,
TotalGrossProfitRate = s.TotalGrossProfitRate != 0 ? s.TotalGrossProfitRate.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=OrganGrossProfitRate.xls");
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
#endregion
return View("StatOrganGrossProfitRate");
}
public JsonResult LoadStatOrganGrossProfitRateGrid(string sidx, string sord, int page, int rows, string startTime, string endTime, string organId)
{
#region 读取明细数据
string strErrText;
StatisticsSystem stat = new StatisticsSystem();
List<OrganGrossProfitRateDetail> listDetail = stat.LoadOrganGrossProfitRateDetailsByConditions(startTime, endTime, organId, LoginAccountId, LoginStaffName, out strErrText);
if (listDetail == null)
{
throw new Exception(strErrText);
}
#endregion
#region 根据结算公式计算运费
{
foreach (OrganGrossProfitRateDetail 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<OrganGrossProfitRateDetail> listShipmentNoDetail = grpShipmentNo.ToList<OrganGrossProfitRateDetail>();
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<StatOrganGrossProfitRate> listStat = new List<StatOrganGrossProfitRate>();
{
//按办事处分组
var grpOrganNames = listDetail.GroupBy(d => d.OwnOrganName).OrderBy(d => d.Key);
foreach (var grpOrganName in grpOrganNames)
{
List<OrganGrossProfitRateDetail> listOrganNameDetail = grpOrganName.ToList<OrganGrossProfitRateDetail>();
//再按起点分组
var grpStartPlaces = listOrganNameDetail.GroupBy(d => d.StartCity).OrderBy(d => d.Key);
foreach (var grpStartPlace in grpStartPlaces)
{
List<OrganGrossProfitRateDetail> listStartPlaceDetail = grpStartPlace.ToList<OrganGrossProfitRateDetail>();
//最后按讫点分组
var grpDestPlaces = listStartPlaceDetail.GroupBy(d => d.ReceiverCity).OrderBy(d => d.Key);
foreach (var grpDestPlace in grpDestPlaces)
{
List<OrganGrossProfitRateDetail> listDestPlaceDetail = grpDestPlace.ToList<OrganGrossProfitRateDetail>();
//生成表格数据行
StatOrganGrossProfitRate s = new StatOrganGrossProfitRate();
s.Id = listStat.Count + 1;
s.OrganName = grpOrganName.Key;
s.StartTime = startTime;
s.EndTime = endTime;
s.StartPlace = grpStartPlace.Key;
s.DestPlace = grpDestPlace.Key;
s.TotalCarrierTransportCharges = listDestPlaceDetail.Sum(d => d.CarrierTransportCharges);
s.TotalCustomerTransportCharges = listDestPlaceDetail.Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
s.TotalTransportChargesDifference = s.TotalCustomerTransportCharges - s.TotalCarrierTransportCharges;
s.TotalGrossProfitRate = s.TotalCustomerTransportCharges != 0 ? s.TotalTransportChargesDifference / s.TotalCustomerTransportCharges : 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 ?? "OrganName") + " " + (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.StartTime,
s.EndTime,
s.StartPlace,
s.DestPlace,
s.TotalCarrierTransportCharges.ToString(),
s.TotalCustomerTransportCharges.ToString(),
s.TotalTransportChargesDifference.ToString(),
s.TotalGrossProfitRate.ToString()
}
}).ToArray(),
userdata = new
{
OrganName = InnoSoft.LS.Resources.Labels.Total,
TotalCarrierTransportCharges = data.Sum(s => s.TotalCarrierTransportCharges),
TotalCustomerTransportCharges = data.Sum(s => s.TotalCustomerTransportCharges),
TotalTransportChargesDifference = data.Sum(s => s.TotalTransportChargesDifference),
TotalGrossProfitRate = data.Sum(s => s.TotalCustomerTransportCharges) != 0 ? data.Sum(s => s.TotalTransportChargesDifference) / data.Sum(s => s.TotalCustomerTransportCharges) : 0
}
};
return Json(ret, JsonRequestBehavior.AllowGet);
}