Example #1
0
        public ActionResult ExportOrganGrossProfitRate()
        {
            string strErrText;

            #region 提取参数

            var request = HttpContext.Request;
            string strStartTime = request.QueryString["startTime"] ?? string.Empty;
            string strEndTime = request.QueryString["endTime"] ?? string.Empty;
            string strOrganId = request.QueryString["organId"] ?? string.Empty;

            #endregion

            #region 读取明细数据

            StatisticsSystem stat = new StatisticsSystem();
            List<OrganGrossProfitRateDetail> listDetail = stat.LoadOrganGrossProfitRateDetailsByConditions(strStartTime, strEndTime, strOrganId, LoginAccountId, LoginStaffName, out strErrText);
            if (listDetail == null)
            {
                throw new Exception(strErrText);
            }

            #endregion

            #region 根据结算公式计算运费
            {
                foreach (OrganGrossProfitRateDetail detail in listDetail)
                {
                    if (detail.CustomerTransportCharges == 0 && detail.CustomerSettlementExpression != null && detail.CustomerSettlementExpression != string.Empty)
                    {
                        try
                        {
                            EvaluatorHelper evaluator = new EvaluatorHelper();

                            evaluator.SetExpression(detail.CustomerSettlementExpression);

                            evaluator.AddVariable(InnoSoft.LS.Resources.Labels.KM, detail.KM == null || detail.KM == string.Empty ? "0" : detail.KM);
                            evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Tunnages, detail.TotalTunnages.ToString());
                            evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Piles, detail.TotalPiles.ToString());
                            evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportPrice, detail.CustomerTransportPrice.ToString());
                            evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportCharges, "0");

                            string strTransportCharges = evaluator.EvaluateExpression();
                            detail.CustomerTransportCharges = decimal.Parse(strTransportCharges);
                        }
                        catch (Exception e)
                        {
                            throw e;
                        }
                    }
                }
            }
            #endregion

            #region 计算拼车费
            {
                var grpShipmentNos = listDetail.GroupBy(d => d.ShipmentNo).OrderBy(d => d.Key);
                foreach (var grpShipmentNo in grpShipmentNos)
                {
                    if (grpShipmentNo.Key != null && grpShipmentNo.Key != string.Empty)
                    {
                        List<OrganGrossProfitRateDetail> listShipmentNoDetail = grpShipmentNo.ToList<OrganGrossProfitRateDetail>();

                        int i = 0;
                        while (i < listShipmentNoDetail.Count)
                        {
                            if (i > 0)
                            {
                                int j = 0;
                                while (j < i)
                                {
                                    if (listShipmentNoDetail[i].ReceiverAddress == listShipmentNoDetail[j].ReceiverAddress)
                                    {
                                        break;
                                    }
                                    j++;
                                }
                                if (j >= i)
                                {
                                    listShipmentNoDetail[i].CustomerCarpoolFee = 100;
                                }
                            }
                            i++;
                        }
                    }
                }
            }
            #endregion

            #region 生成表格数据源

            List<StatOrganGrossProfitRate> listStat = new List<StatOrganGrossProfitRate>();
            {
                //按办事处分组
                var grpOrganNames = listDetail.GroupBy(d => d.OwnOrganName).OrderBy(d => d.Key);
                foreach (var grpOrganName in grpOrganNames)
                {
                    List<OrganGrossProfitRateDetail> listOrganNameDetail = grpOrganName.ToList<OrganGrossProfitRateDetail>();

                    //再按起点分组
                    var grpStartPlaces = listOrganNameDetail.GroupBy(d => d.StartCity).OrderBy(d => d.Key);
                    foreach (var grpStartPlace in grpStartPlaces)
                    {
                        List<OrganGrossProfitRateDetail> listStartPlaceDetail = grpStartPlace.ToList<OrganGrossProfitRateDetail>();

                        //最后按讫点分组
                        var grpDestPlaces = listStartPlaceDetail.GroupBy(d => d.ReceiverCity).OrderBy(d => d.Key);
                        foreach (var grpDestPlace in grpDestPlaces)
                        {
                            List<OrganGrossProfitRateDetail> listDestPlaceDetail = grpDestPlace.ToList<OrganGrossProfitRateDetail>();

                            //生成表格数据行
                            StatOrganGrossProfitRate s = new StatOrganGrossProfitRate();
                            s.Id = listStat.Count + 1;
                            s.OrganName = grpOrganName.Key;
                            s.StartTime = strStartTime;
                            s.EndTime = strEndTime;
                            s.StartPlace = grpStartPlace.Key;
                            s.DestPlace = grpDestPlace.Key;
                            s.TotalCarrierTransportCharges = listDestPlaceDetail.Sum(d => d.CarrierTransportCharges);
                            s.TotalCustomerTransportCharges = listDestPlaceDetail.Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
                            s.TotalTransportChargesDifference = s.TotalCustomerTransportCharges - s.TotalCarrierTransportCharges;
                            s.TotalGrossProfitRate = s.TotalCustomerTransportCharges != 0 ? s.TotalTransportChargesDifference / s.TotalCustomerTransportCharges : 0;

                            listStat.Add(s);
                        }
                    }
                }
            }

            #endregion

            #region 输出Excel

            //生成GridView
            BoundField colOrganName = new BoundField();
            colOrganName.HeaderText = InnoSoft.LS.Resources.Labels.OrganName;
            colOrganName.DataField = "OrganName";

            BoundField colStartTime = new BoundField();
            colStartTime.HeaderText = InnoSoft.LS.Resources.Labels.StartTime;
            colStartTime.DataField = "StartTime";

            BoundField colEndTime = new BoundField();
            colEndTime.HeaderText = InnoSoft.LS.Resources.Labels.EndTime;
            colEndTime.DataField = "EndTime";

            BoundField colStartPlace = new BoundField();
            colStartPlace.HeaderText = InnoSoft.LS.Resources.Labels.StartPlace;
            colStartPlace.DataField = "StartPlace";

            BoundField colDestPlace = new BoundField();
            colDestPlace.HeaderText = InnoSoft.LS.Resources.Labels.DestPlace;
            colDestPlace.DataField = "DestPlace";

            BoundField colTotalCarrierTransportCharges = new BoundField();
            colTotalCarrierTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.TotalCarrierTransportCharges;
            colTotalCarrierTransportCharges.DataField = "TotalCarrierTransportCharges";

            BoundField colTotalCustomerTransportCharges = new BoundField();
            colTotalCustomerTransportCharges.HeaderText = InnoSoft.LS.Resources.Labels.TotalCustomerTransportCharges;
            colTotalCustomerTransportCharges.DataField = "TotalCustomerTransportCharges";

            BoundField colTotalTransportChargesDifference = new BoundField();
            colTotalTransportChargesDifference.HeaderText = InnoSoft.LS.Resources.Labels.TotalTransportChargesDifference;
            colTotalTransportChargesDifference.DataField = "TotalTransportChargesDifference";

            BoundField colTotalGrossProfitRate = new BoundField();
            colTotalGrossProfitRate.HeaderText = InnoSoft.LS.Resources.Labels.TotalGrossProfitRate;
            colTotalGrossProfitRate.DataField = "TotalGrossProfitRate";

            var grid = new GridView();
            grid.Columns.Add(colOrganName);
            grid.Columns.Add(colStartTime);
            grid.Columns.Add(colEndTime);
            grid.Columns.Add(colStartPlace);
            grid.Columns.Add(colDestPlace);
            grid.Columns.Add(colTotalCarrierTransportCharges);
            grid.Columns.Add(colTotalCustomerTransportCharges);
            grid.Columns.Add(colTotalTransportChargesDifference);
            grid.Columns.Add(colTotalGrossProfitRate);

            grid.AutoGenerateColumns = false;

            grid.RowDataBound += new GridViewRowEventHandler(OrganGrossProfitRateGrid_RowDataBound);
            grid.DataSource = from s in listStat
                              select new
                              {
                                  OrganName = s.OrganName,
                                  StartTime = s.StartTime,
                                  EndTime = s.EndTime,
                                  StartPlace = s.StartPlace,
                                  DestPlace = s.DestPlace,
                                  TotalCarrierTransportCharges = s.TotalCarrierTransportCharges != 0 ? s.TotalCarrierTransportCharges.ToString("N") : string.Empty,
                                  TotalCustomerTransportCharges = s.TotalCustomerTransportCharges != 0 ? s.TotalCustomerTransportCharges.ToString("N") : string.Empty,
                                  TotalTransportChargesDifference = s.TotalTransportChargesDifference != 0 ? s.TotalTransportChargesDifference.ToString("N") : string.Empty,
                                  TotalGrossProfitRate = s.TotalGrossProfitRate != 0 ? s.TotalGrossProfitRate.ToString("N") : string.Empty
                              };
            grid.DataBind();

            //导出GridView
            Response.ClearContent();
            Response.Charset = InnoSoft.LS.Resources.Encoding.ExcelCharset;
            Response.ContentEncoding = System.Text.Encoding.GetEncoding(InnoSoft.LS.Resources.Encoding.ExcelContent);
            Response.ContentType = "application/ms-excel";
            Response.Write("<meta http-equiv=Content-Type content=text/html charset=" + InnoSoft.LS.Resources.Encoding.ExcelCharset + ">");
            Response.AddHeader("content-disposition", "attachment; filename=OrganGrossProfitRate.xls");
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            grid.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();

            #endregion

            return View("StatOrganGrossProfitRate");
        }
Example #2
0
        public JsonResult LoadStatOrganGrossProfitRateGrid(string sidx, string sord, int page, int rows, string startTime, string endTime, string organId)
        {
            #region 读取明细数据

            string strErrText;
            StatisticsSystem stat = new StatisticsSystem();
            List<OrganGrossProfitRateDetail> listDetail = stat.LoadOrganGrossProfitRateDetailsByConditions(startTime, endTime, organId, LoginAccountId, LoginStaffName, out strErrText);
            if (listDetail == null)
            {
                throw new Exception(strErrText);
            }

            #endregion

            #region 根据结算公式计算运费
            {
                foreach (OrganGrossProfitRateDetail detail in listDetail)
                {
                    if (detail.CustomerTransportCharges == 0 && detail.CustomerSettlementExpression != null && detail.CustomerSettlementExpression != string.Empty)
                    {
                        try
                        {
                            EvaluatorHelper evaluator = new EvaluatorHelper();

                            evaluator.SetExpression(detail.CustomerSettlementExpression);

                            evaluator.AddVariable(InnoSoft.LS.Resources.Labels.KM, detail.KM == null || detail.KM == string.Empty ? "0" : detail.KM);
                            evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Tunnages, detail.TotalTunnages.ToString());
                            evaluator.AddVariable(InnoSoft.LS.Resources.Labels.Piles, detail.TotalPiles.ToString());
                            evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportPrice, detail.CustomerTransportPrice.ToString());
                            evaluator.AddVariable(InnoSoft.LS.Resources.Labels.TransportCharges, "0");

                            string strTransportCharges = evaluator.EvaluateExpression();
                            detail.CustomerTransportCharges = decimal.Parse(strTransportCharges);
                        }
                        catch (Exception e)
                        {
                            throw e;
                        }
                    }
                }
            }
            #endregion

            #region 计算拼车费
            {
                var grpShipmentNos = listDetail.GroupBy(d => d.ShipmentNo).OrderBy(d => d.Key);
                foreach (var grpShipmentNo in grpShipmentNos)
                {
                    if (grpShipmentNo.Key != null && grpShipmentNo.Key != string.Empty)
                    {
                        List<OrganGrossProfitRateDetail> listShipmentNoDetail = grpShipmentNo.ToList<OrganGrossProfitRateDetail>();

                        int i = 0;
                        while (i < listShipmentNoDetail.Count)
                        {
                            if (i > 0)
                            {
                                int j = 0;
                                while (j < i)
                                {
                                    if (listShipmentNoDetail[i].ReceiverAddress == listShipmentNoDetail[j].ReceiverAddress)
                                    {
                                        break;
                                    }
                                    j++;
                                }
                                if (j >= i)
                                {
                                    listShipmentNoDetail[i].CustomerCarpoolFee = 100;
                                }
                            }
                            i++;
                        }
                    }
                }
            }
            #endregion

            #region 生成表格数据源

            List<StatOrganGrossProfitRate> listStat = new List<StatOrganGrossProfitRate>();
            {
                //按办事处分组
                var grpOrganNames = listDetail.GroupBy(d => d.OwnOrganName).OrderBy(d => d.Key);
                foreach (var grpOrganName in grpOrganNames)
                {
                    List<OrganGrossProfitRateDetail> listOrganNameDetail = grpOrganName.ToList<OrganGrossProfitRateDetail>();

                    //再按起点分组
                    var grpStartPlaces = listOrganNameDetail.GroupBy(d => d.StartCity).OrderBy(d => d.Key);
                    foreach (var grpStartPlace in grpStartPlaces)
                    {
                        List<OrganGrossProfitRateDetail> listStartPlaceDetail = grpStartPlace.ToList<OrganGrossProfitRateDetail>();

                        //最后按讫点分组
                        var grpDestPlaces = listStartPlaceDetail.GroupBy(d => d.ReceiverCity).OrderBy(d => d.Key);
                        foreach (var grpDestPlace in grpDestPlaces)
                        {
                            List<OrganGrossProfitRateDetail> listDestPlaceDetail = grpDestPlace.ToList<OrganGrossProfitRateDetail>();

                            //生成表格数据行
                            StatOrganGrossProfitRate s = new StatOrganGrossProfitRate();
                            s.Id = listStat.Count + 1;
                            s.OrganName = grpOrganName.Key;
                            s.StartTime = startTime;
                            s.EndTime = endTime;
                            s.StartPlace = grpStartPlace.Key;
                            s.DestPlace = grpDestPlace.Key;
                            s.TotalCarrierTransportCharges = listDestPlaceDetail.Sum(d => d.CarrierTransportCharges);
                            s.TotalCustomerTransportCharges = listDestPlaceDetail.Sum(d => d.CustomerTransportCharges + d.CustomerCarpoolFee + d.CustomerRiverCrossingCharges);
                            s.TotalTransportChargesDifference = s.TotalCustomerTransportCharges - s.TotalCarrierTransportCharges;
                            s.TotalGrossProfitRate = s.TotalCustomerTransportCharges != 0 ? s.TotalTransportChargesDifference / s.TotalCustomerTransportCharges : 0;

                            listStat.Add(s);
                        }
                    }
                }
            }

            #endregion

            //提取当前页面数据
            int nTotalRows = listStat.Count;
            int nPageIndex = page;
            int nPageSize = rows;
            int nTotalPages = nTotalRows / nPageSize;
            if (nTotalRows % nPageSize > 0)
                nTotalPages++;

            string sortExpression = (sidx ?? "OrganName") + " " + (sord ?? "ASC");
            var data = listStat.OrderBy(sortExpression).Skip((nPageIndex - 1) * nPageSize).Take(nPageSize).ToList();

            //生成表格数据
            var ret = new
            {
                total = nTotalPages,
                page = nPageIndex,
                records = nTotalRows,
                rows = (
                      from s in data
                      select new
                      {
                          id = s.Id,
                          cell = new string[] {
                              s.Id.ToString(),
                              s.OrganName,
                              s.StartTime,
                              s.EndTime,
                              s.StartPlace,
                              s.DestPlace,
                              s.TotalCarrierTransportCharges.ToString(),
                              s.TotalCustomerTransportCharges.ToString(),
                              s.TotalTransportChargesDifference.ToString(),
                              s.TotalGrossProfitRate.ToString()
                          }
                      }).ToArray(),
                userdata = new
                {
                    OrganName = InnoSoft.LS.Resources.Labels.Total,
                    TotalCarrierTransportCharges = data.Sum(s => s.TotalCarrierTransportCharges),
                    TotalCustomerTransportCharges = data.Sum(s => s.TotalCustomerTransportCharges),
                    TotalTransportChargesDifference = data.Sum(s => s.TotalTransportChargesDifference),
                    TotalGrossProfitRate = data.Sum(s => s.TotalCustomerTransportCharges) != 0 ? data.Sum(s => s.TotalTransportChargesDifference) / data.Sum(s => s.TotalCustomerTransportCharges) : 0
                }
            };
            return Json(ret, JsonRequestBehavior.AllowGet);
        }