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");
        }