/// <summary> /// 拼接查询条件 /// </summary> /// <param name="model"></param> /// <returns></returns> string GetSQL(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model) { StringBuilder cmdText = new StringBuilder(); if (model.TourType.HasValue) { cmdText.AppendFormat(" AND A.TourType={0} ", (int)model.TourType.Value); } if (model.LeaveDateEnd.HasValue) { cmdText.AppendFormat(" AND A.LeaveDate<'{0}' ", model.LeaveDateEnd.Value.AddDays(1)); } if (model.LeaveDateStart.HasValue) { cmdText.AppendFormat(" AND A.LeaveDate>'{0}' ", model.LeaveDateStart.Value.AddDays(-1)); } if (model.CheckDateEnd.HasValue) { cmdText.AppendFormat(" AND A.EndDateTime<'{0}' ", model.CheckDateEnd.Value.AddDays(1)); } if (model.CheckDateStart.HasValue) { cmdText.AppendFormat(" AND A.EndDateTime>'{0}' ", model.CheckDateStart.Value.AddDays(-1)); } if (model.SaleIds != null && model.SaleIds.Length > 0) { cmdText.AppendFormat(" AND A.OperatorId IN({0}) ", EyouSoft.Toolkit.Utils.GetSqlIdStrByArray(model.SaleIds)); } if (model.DepartIds != null && model.DepartIds.Length > 0) { cmdText.AppendFormat(" AND A.DepartId IN({0}) ", EyouSoft.Toolkit.Utils.GetSqlIdStrByArray(model.DepartIds)); } if (model.AreaId > 0) { cmdText.AppendFormat(" AND A.AreaId={0} ", model.AreaId); } return(cmdText.ToString()); }
private void IntiTourPerList() { EyouSoft.BLL.TourStructure.Tour TourBll = new EyouSoft.BLL.TourStructure.Tour(SiteUserInfo); //查询实体 //默认获取当年的数据 DateTime?CurrStartTime = new DateTime(DateTime.Now.Year, 1, 1); DateTime?CurrEndTime = new DateTime(DateTime.Now.Year, 12, 31); EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model = new EyouSoft.Model.StatisticStructure.QueryEarningsStatistic(); PageIndex = Utils.GetInt(Utils.GetQueryStringValue("Page"), 1); TourType = Utils.GetQueryStringValue("TourType"); if (Utils.GetQueryStringValue("AreaId") == "0") { TourType = "2"; } if (Request.QueryString["IsByTime"] != null && Request.QueryString["IsByTime"] == "1") { LeaveTourStartDate = Utils.GetDateTimeNullable(Request.QueryString["LeaveTourStartDate"], CurrStartTime); LeaveTourEndDate = Utils.GetDateTimeNullable(Request.QueryString["LeaveTourEndDate"], CurrEndTime); } else { LeaveTourStartDate = Utils.GetDateTimeNullable(Request.QueryString["LeaveStartDate"]); LeaveTourEndDate = Utils.GetDateTimeNullable(Request.QueryString["LeaveEndDate"]); } CheckStartDate = Utils.GetDateTimeNullable(Request.QueryString["CheckStartTime"]); CheckEndDate = Utils.GetDateTimeNullable(Request.QueryString["CheckEndTime"]); AreaId = Utils.GetInt(Utils.GetQueryStringValue("AreaId"), 0); DepartId = Utils.GetQueryStringValue("DepartId"); Year = Utils.GetInt(Request.QueryString["CuYear"], 0); //年份 Month = Utils.GetInt(Request.QueryString["CuMoth"], 0); //月份 if (TourType != "" && int.Parse(TourType) > 0) { model.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType) int.Parse(TourType); } model.OrderIndex = 0; model.LeaveDateStart = LeaveTourStartDate; model.LeaveDateEnd = LeaveTourEndDate; model.CheckDateStart = CheckStartDate; model.CheckDateEnd = CheckEndDate; model.AreaId = AreaId; model.CurrYear = Year; model.CurrMonth = Month; model.DepartIds = Utils.GetIntArray(DepartId, ","); list = TourBll.GetToursLYTJ(this.SiteUserInfo.CompanyID, PageSize, PageIndex, ref RecordCount, model); if (list != null && list.Count > 0) { this.crp_GetTourDetailList.DataSource = list; this.crp_GetTourDetailList.DataBind(); BindPage(); this.tbl_ExportPage.Visible = true; } else { this.crp_GetTourDetailList.EmptyText = "<tr><td colspan='8' bgcolor='#e3f1fc' height='50px' align='center'>暂时没有数据!</td></tr>"; this.tbl_ExportPage.Visible = false; } //释放资源 list = null; model = null; TourBll = null; }
/// <summary> /// 根据订单XML获取订单部分信息 /// </summary> /// <param name="strOrderXML">订单XML</param> /// <param name="model">查询实体</param> /// <param name="TotalAllIncome">团队总收入</param> /// <param name="TourPeopleNum">团队人数</param> /// <param name="tmpList">销售员集合</param> /// <param name="IsIList">是否为集合赋值</param> private void GetOrderInfoByXML(string strOrderXML, EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model , ref decimal TotalAllIncome, ref int TourPeopleNum , ref IList <Model.StatisticStructure.StatisticOperator> tmpList, bool IsIList) { if (string.IsNullOrEmpty(strOrderXML)) { return; } XElement xRoot = XElement.Parse(strOrderXML); var xRows = Utils.GetXElements(xRoot, "tmpVes"); if (xRows == null || xRows.Count() <= 0) { return; } TotalAllIncome = 0; TourPeopleNum = 0; if (tmpList == null && IsIList) { tmpList = new List <Model.StatisticStructure.StatisticOperator>(); } Model.StatisticStructure.StatisticOperator tmpModel = null; foreach (var t in xRows) { string strTmpXML = Utils.GetXAttributeValue(t, "Orders"); if (string.IsNullOrEmpty(strTmpXML)) { continue; } var TmpXRows = Utils.GetXElements(XElement.Parse(strTmpXML), "row"); if (TmpXRows == null || TmpXRows.Count() <= 0) { continue; } foreach (var tmp in TmpXRows) { int SaleId = Utils.GetInt(Utils.GetXAttributeValue(tmp, "SalerId")); int OrderState = Utils.GetInt(Utils.GetXAttributeValue(tmp, "OrderState")); if (model != null) { //销售员查询 if (SaleId > 0 && model.SaleIds != null && model.SaleIds.Length > 0 && !model.SaleIds.Contains(SaleId)) { continue; } //订单统计方式 if (model.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计有效订单) { if (OrderState == (int)Model.EnumType.TourStructure.OrderState.受理 || OrderState == (int)Model.EnumType.TourStructure.OrderState.留位过期) { continue; } } else if (model.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计确认成交订单 && OrderState != (int)Model.EnumType.TourStructure.OrderState.已成交) { continue; } } TourPeopleNum += (Utils.GetInt(Utils.GetXAttributeValue(tmp, "PeopleNumber")) - Utils.GetInt(Utils.GetXAttributeValue(tmp, "LeaguePepoleNum"))); TotalAllIncome += Utils.GetDecimal(Utils.GetXAttributeValue(tmp, "FinanceSum")); if (!IsIList) { continue; } tmpModel = new EyouSoft.Model.StatisticStructure.StatisticOperator(); tmpModel.OperatorId = Utils.GetInt(Utils.GetXAttributeValue(tmp, "SalerId")); tmpModel.OperatorName = Utils.GetXAttributeValue(tmp, "SalerName"); tmpList.Add(tmpModel); } } if (tmpList != null) { tmpList = tmpList.Distinct(new DistinctByStatisticOperator()).ToList(); } }
/// <summary> /// 根据查询实体生成Where子句 /// </summary> /// <param name="model">查询实体</param> /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param> /// <param name="strOrder">排序语句</param> /// <returns>Where子句</returns> private string GetSqlWhere(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds , ref string strOrder) { if (model == null) { return(string.Empty); } StringBuilder strSqlWhere = new StringBuilder(); string strIds = string.Empty; if (model.CompanyId > 0) { strSqlWhere.AppendFormat(" and ves.CompanyId = {0} ", model.CompanyId); } if (model.TourType.HasValue) { strSqlWhere.AppendFormat(" and ves.TourType = {0} ", (int)model.TourType.Value); } if (model.DepartIds != null && model.DepartIds.Length > 0) { strIds = string.Empty; foreach (int i in model.DepartIds) { if (i <= 0) { continue; } strIds += i.ToString() + ","; } strIds = strIds.Trim(','); if (!string.IsNullOrEmpty(strIds)) { strSqlWhere.AppendFormat(" and ves.DepartId in ({0}) ", strIds); } } if (model.SaleIds != null && model.SaleIds.Length > 0) { strIds = string.Empty; foreach (int i in model.SaleIds) { if (i <= 0) { continue; } strIds += i.ToString() + ","; } strIds = strIds.Trim(','); if (!string.IsNullOrEmpty(strIds)) { strSqlWhere.AppendFormat(" and ves.TourId in (select distinct tbl_TourOrder.TourId from tbl_TourOrder where SalerId in ({0})) ", strIds); } } if (!string.IsNullOrEmpty(HaveUserIds)) { strSqlWhere.AppendFormat(" and ves.OperatorId in ({0}) ", HaveUserIds); } if (model.LeaveDateStart.HasValue) { strSqlWhere.AppendFormat(" and datediff(dd,'{0}',ves.LeaveDate) >= 0 ", model.LeaveDateStart.Value.ToShortDateString()); } if (model.LeaveDateEnd.HasValue) { strSqlWhere.AppendFormat(" and datediff(dd,ves.LeaveDate,'{0}') >= 0 ", model.LeaveDateEnd.Value.ToShortDateString()); } if (model.CheckDateStart.HasValue) { strSqlWhere.AppendFormat(" and datediff(dd,'{0}',ves.EndDateTime) >= 0 ", model.CheckDateStart.Value.ToShortDateString()); } if (model.CheckDateEnd.HasValue) { strSqlWhere.AppendFormat(" and datediff(dd,ves.EndDateTime,'{0}') >= 0 ", model.CheckDateEnd.Value.ToShortDateString()); } if (model.AreaId > 0) { strSqlWhere.AppendFormat(" and ves.AreaId = {0} ", model.AreaId); } switch (model.OrderIndex) { case 0: strOrder = " AreaId asc "; break; case 1: strOrder = " AreaId desc "; break; case 2: strOrder = " DepartId asc "; break; case 3: strOrder = " DepartId desc "; break; case 4: strOrder = " TourType asc "; break; case 5: strOrder = " TourType desc "; break; case 6: strOrder = " CurrYear asc,CurrMonth asc "; break; case 7: strOrder = " CurrYear desc,CurrMonth desc "; break; } return(strSqlWhere.ToString()); }
/// <summary> /// 获取利润--区域统计 /// </summary> /// <param name="model">利润统计查询实体</param> /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic> GetEarningsAreaStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds) { IList <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic> list = new List <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic>(); string strOrder = string.Empty; string strWhere = this.GetSqlWhere(model, HaveUserIds, ref strOrder); StringBuilder strSql = new StringBuilder(" select "); strSql.Append(" AreaId,AreaName, "); strSql.Append(" count(TourId) as TourCount, "); strSql.Append(" sum(TotalAllExpenses) as TotalAllExpenses, "); strSql.Append(" 0 as GrossProfit, "); strSql.Append(" sum(DistributionAmount) as DistributionAmount, "); strSql.AppendFormat(" (select Logistics from View_EarningsStatistic as tmpVes where tmpVes.AreaId = ves.AreaId {0} for xml auto,root('root')) as Logistics, ", strWhere.Replace("ves.", "tmpVes.")); strSql.AppendFormat(" (select Orders from View_EarningsStatistic as tmpVes where tmpVes.AreaId = ves.AreaId and len(Orders) > 1 {0} for xml auto,root('root')) as Orders, ", strWhere.Replace("ves.", "tmpVes.")); strSql.Append(" Sum(TotalOtherIncome) as TotalOtherIncome"); strSql.Append(" from View_EarningsStatistic as ves "); strSql.AppendFormat(" where IsDelete = '0' "); strSql.AppendFormat(" {0} ", strWhere); strSql.Append(" group by AreaId,AreaName "); if (!string.IsNullOrEmpty(strOrder)) { strSql.AppendFormat(" order by {0} ", strOrder); } DbCommand dc = _db.GetSqlStringCommand(strSql.ToString()); using (IDataReader dr = DbHelper.ExecuteReader(dc, _db)) { EyouSoft.Model.StatisticStructure.EarningsAreaStatistic tmpModel = null; decimal FinanceSum = 0; int PeopleNumber = 0; while (dr.Read()) { tmpModel = new EyouSoft.Model.StatisticStructure.EarningsAreaStatistic(); if (!dr.IsDBNull(0)) { tmpModel.AreaId = dr.GetInt32(0); } if (!dr.IsDBNull(1)) { tmpModel.AreaName = dr.GetString(1); } if (!dr.IsDBNull(2)) { tmpModel.TourNum = dr.GetInt32(2); } if (!dr.IsDBNull(3)) { tmpModel.GrossOut = dr.GetDecimal(3); } if (!dr.IsDBNull(4)) { tmpModel.TourGross = 0;//dr.GetDecimal(4); } if (!dr.IsDBNull(5)) { tmpModel.TourShare = dr.GetDecimal(5); } if (!dr.IsDBNull(6)) { tmpModel.Logistics = this.GetStatisticOperatorByXML(dr.GetString(6)); } FinanceSum = 0; PeopleNumber = 0; IList <Model.StatisticStructure.StatisticOperator> tmp = null; if (!dr.IsDBNull(7)) { this.GetOrderInfoByXML(dr.GetString(7), model, ref FinanceSum, ref PeopleNumber, ref tmp, false); } tmpModel.GrossIncome = FinanceSum; //此处只计算团款收入 tmpModel.TourPeopleNum = PeopleNumber; if (!dr.IsDBNull(8)) { tmpModel.GrossIncome += dr.GetDecimal(8); //此处加上杂费收入 } //团队毛利=总收入-总支出 tmpModel.TourGross = tmpModel.GrossIncome - tmpModel.GrossOut; list.Add(tmpModel); } } return(list); }
private void InitProAreaStaticlist() { #region 页面参数初始化 PageIndex = Utils.GetInt(Request.QueryString["Page"], 1); DeapartName = Utils.GetQueryStringValue("departName"); OperatorName = Utils.GetQueryStringValue("OperatorName"); OperatorId = Utils.GetQueryStringValue("OperatorId"); StartDate = Utils.GetDateTimeNullable(Request.QueryString["StartDate"], new DateTime(DateTime.Now.Year, 1, 1)); EndDate = Utils.GetDateTimeNullable(Request.QueryString["EndDate"], new DateTime(DateTime.Now.Year, 12, 31)); DepartId = Utils.GetQueryStringValue("DepartmentId"); CheckSDate = Utils.GetDateTimeNullable(Request.QueryString["CheckSDate"]); CheckEDate = Utils.GetDateTimeNullable(Request.QueryString["CheckEDate"]); #endregion #region 表单控件初始化 UCSelectDepartment1.GetDepartmentName = DeapartName; UCSelectDepartment1.GetDepartId = DepartId; SelectOperator.OperName = OperatorName; SelectOperator.OperId = OperatorId; txtStartDate.Value = StartDate.HasValue ? StartDate.Value.ToString("yyyy-MM-dd") : ""; txtEndDate.Value = EndDate.HasValue ? EndDate.Value.ToString("yyyy-MM-dd") : ""; txtCheckStartTime.Value = CheckSDate.HasValue ? CheckSDate.Value.ToString("yyyy-MM-dd") : ""; txtCheckEndTime.Value = CheckEDate.HasValue ? CheckEDate.Value.ToString("yyyy-MM-dd") : ""; #endregion #region 调用底层方法 查询Model赋值 EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model = new EyouSoft.Model.StatisticStructure.QueryEarningsStatistic(); EyouSoft.BLL.StatisticStructure.EarningsStatistic EarningBll = new EyouSoft.BLL.StatisticStructure.EarningsStatistic(this.SiteUserInfo); model.CompanyId = base.SiteUserInfo.CompanyID; model.OrderIndex = 4; model.LeaveDateStart = StartDate; model.LeaveDateEnd = EndDate; if (!string.IsNullOrEmpty(OperatorId)) { model.SaleIds = Utils.GetIntArray(OperatorId, ","); } if (!string.IsNullOrEmpty(DepartId)) { model.DepartIds = Utils.GetIntArray(DepartId, ","); } //设置查询model的ComputeOrderType值 EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType?computerOrderType = new EyouSoft.BLL.CompanyStructure.CompanySetting().GetComputeOrderType(SiteUserInfo.CompanyID); if (computerOrderType.HasValue) { model.ComputeOrderType = computerOrderType.Value; } model.CheckDateStart = CheckSDate; model.CheckDateEnd = CheckEDate; IList <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic> statisticList = new List <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic>(); statisticList = EarningBll.GetEarningsTypeStatistic(model); #region 统计 by 田想兵 3.9 int teamNum = 0; decimal inMoney = 0; decimal outMoney = 0; decimal teamGross = 0; decimal pepoleGross = 0; decimal profitallot = 0; decimal comProfit = 0; int peopleNum = 0; int propleSum = 0; foreach (var v in statisticList) { peopleNum += v.TourPeopleNum; teamNum += v.TourNum; inMoney += v.GrossIncome; outMoney += v.GrossOut; teamGross += v.TourGross; //pepoleGross += v.PeopleGross; profitallot += v.TourShare; comProfit += v.CompanyShare; propleSum += v.TourPeopleNum; } if (peopleNum == 0) { pepoleGross = 0; } else { pepoleGross = teamGross / peopleNum; } lt_teamNum.Text = teamNum.ToString(); lt_InMoney.Text = inMoney.ToString("###,##0.00"); lt_outMoney.Text = outMoney.ToString("###,##0.00"); lt_teamgross_profit.Text = teamGross.ToString("###,##0.00"); lt_pepolegross_profit.Text = pepoleGross.ToString("###,##0.00"); lt_profitallot.Text = profitallot.ToString("###,##0.00"); lt_comProfit.Text = comProfit.ToString("###,##0.00"); lt_peopleSum.Text = propleSum.ToString(); if (inMoney != 0) { lt_lirunlv.Text = (Convert.ToDouble(comProfit) / Convert.ToDouble(inMoney)).ToString("0.00%"); } else { lt_lirunlv.Text = "0.00%"; } #endregion ProTypeStaList = EyouSoft.Common.Function.SelfExportPage.GetList <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic>(PageIndex, PageSize, out RecordCount, EarningBll.GetEarningsTypeStatistic(model)); #endregion #region 数据源绑定 if (ProTypeStaList != null && ProTypeStaList.Count != 0) { this.crp_GetProTypeList.DataSource = ProTypeStaList; this.crp_GetProTypeList.DataBind(); BindPage();//绑定分页 } else { this.tbl_ExportPage.Visible = false; this.crp_GetProTypeList.EmptyText = "<tr><td colspan='9' id=\"EmptyData\" bgcolor='#e3f1fc' height='50px' align='center'>暂时没有数据!</td></tr>"; } #endregion RegisterScript(string.Format("var recordCount={0};", RecordCount)); }
private void InitProTimeStaticlist() { #region 获取参数 PageIndex = Utils.GetInt(Request.QueryString["Page"], 1); RouteAreaName = Utils.GetQueryStringValue("RouteAreaName"); leaveTourStartDate = Utils.GetDateTimeNullable(Request.QueryString["leaveTourStarDate"], new DateTime(DateTime.Now.Year, 1, 1)); leaveTourEndDate = Utils.GetDateTimeNullable(Request.QueryString["leaveTourEndDate"], new DateTime(DateTime.Now.Year, 12, 31)); checkStartDate = Utils.GetDateTimeNullable(Request.QueryString["checkStartDate"]); checkEndDate = Utils.GetDateTimeNullable(Request.QueryString["checkEndDate"]); TourType = Utils.GetQueryStringValue("TourType"); OperatorName = Utils.GetQueryStringValue("OperatorName"); OperatorId = Utils.GetQueryStringValue("OperatorId"); DepartName = Utils.GetQueryStringValue("deptnames"); DepartId = Utils.GetQueryStringValue("deptids"); #endregion #region 初始化表单值 this.txtcheckStartDate.Value = checkStartDate.HasValue ? checkStartDate.Value.ToString("yyyy-MM-dd") : ""; this.txtcheckEndDate.Value = checkEndDate.HasValue ? checkEndDate.Value.ToString("yyyy-MM-dd") : ""; this.txtLeaveStarDate.Value = leaveTourStartDate.HasValue ? leaveTourStartDate.Value.ToString("yyyy-MM-dd") : ""; this.txtLeaveEndDate.Value = leaveTourEndDate.HasValue ? leaveTourEndDate.Value.ToString("yyyy-MM-dd") : ""; if (TourType != "")//团队类型 { this.TourTypeList1.TourType = int.Parse(TourType); } if (RouteAreaName != "")//线路区域名称 { this.RouteAreaList1.RouteAreaId = int.Parse(RouteAreaName); } this.SelectOperator.OperName = OperatorName; //销售员 this.SelectOperator.OperId = OperatorId; this.UCSelectDepartment1.GetDepartmentName = DepartName; //部门 this.UCSelectDepartment1.GetDepartId = DepartId; #endregion #region 调用底层方法 Model赋值 EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model = new EyouSoft.Model.StatisticStructure.QueryEarningsStatistic(); EyouSoft.BLL.StatisticStructure.EarningsStatistic EarningBll = new EyouSoft.BLL.StatisticStructure.EarningsStatistic(this.SiteUserInfo); if (RouteAreaName != "" && RouteAreaName != "0") { model.AreaId = int.Parse(RouteAreaName); } if (TourType != "" && TourType != "-1") { model.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType) int.Parse(TourType); } model.OrderIndex = 6; model.LeaveDateStart = leaveTourStartDate; model.LeaveDateEnd = leaveTourEndDate; //设置查询model的ComputeOrderType值 EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType?computerOrderType = new EyouSoft.BLL.CompanyStructure.CompanySetting().GetComputeOrderType(SiteUserInfo.CompanyID); if (computerOrderType.HasValue) { model.ComputeOrderType = computerOrderType.Value; } if (OperatorId != "") { model.SaleIds = Utils.GetIntArray(OperatorId, ","); } if (DepartId != "") { model.DepartIds = Utils.GetIntArray(DepartId, ","); } model.CheckDateStart = checkStartDate; model.CheckDateEnd = checkEndDate; IList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic> statisticList = new List <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic>(); model.CompanyId = CurrentUserCompanyID; statisticList = EarningBll.GetEarningsTimeStatistic(model); #region 统计 by 田想兵 3.9 int teamNum = 0; decimal inMoney = 0; decimal outMoney = 0; decimal teamGross = 0; decimal pepoleGross = 0; decimal profitallot = 0; decimal comProfit = 0; int peopleNum = 0; int propleSum = 0; foreach (var v in statisticList) { peopleNum += v.TourPeopleNum; teamNum += v.TourNum; inMoney += v.GrossIncome; outMoney += v.GrossOut; teamGross += v.TourGross; //pepoleGross += v.PeopleGross; profitallot += v.TourShare; comProfit += v.CompanyShare; propleSum += v.TourPeopleNum; } if (peopleNum == 0) { pepoleGross = 0; } else { pepoleGross = teamGross / peopleNum; } lt_teamNum.Text = teamNum.ToString(); lt_InMoney.Text = inMoney.ToString("###,##0.00"); lt_outMoney.Text = outMoney.ToString("###,##0.00"); lt_teamgross_profit.Text = teamGross.ToString("###,##0.00"); lt_pepolegross_profit.Text = pepoleGross.ToString("###,##0.00"); lt_profitallot.Text = profitallot.ToString("###,##0.00"); lt_comProfit.Text = comProfit.ToString("###,##0.00"); lt_peopleSum.Text = propleSum.ToString(); if (inMoney != 0) { lt_lirunlv.Text = (Convert.ToDouble(comProfit) / Convert.ToDouble(inMoney)).ToString("0.00%"); } else { lt_lirunlv.Text = "0.00%"; } #endregion list = EyouSoft.Common.Function.SelfExportPage.GetList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic>(PageIndex, PageSize, out RecordCount, statisticList); #endregion if (list != null && list.Count != 0) { this.tbl_ExportPage.Visible = true; this.crp_GetProTimeList.DataSource = list; this.crp_GetProTimeList.DataBind(); BindPage();//绑定分页 } else { this.tbl_ExportPage.Visible = false; this.crp_GetProTimeList.EmptyText = "<tr><td colspan='8' id=\"EmptyData\" bgcolor='#e3f1fc' height='50px' align='center'>暂时没有数据!</td></tr>"; } //释放资源 model = null; EarningBll = null; RegisterScript(string.Format("var recordCount={0};", RecordCount)); }
/// <summary> /// 获取利润--区域统计 /// </summary> /// <param name="model">利润统计查询实体</param> /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic> GetEarningsAreaStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds) { IList <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic> items = new List <EyouSoft.Model.StatisticStructure.EarningsAreaStatistic>(); StringBuilder cmdText = new StringBuilder(); #region SQL cmdText.Append(" SELECT A.AreaId "); cmdText.Append(" ,A.AreaName "); cmdText.Append(" ,COUNT(A.TourId) AS TuanDuiShu "); cmdText.Append(" ,SUM(A.TotalAllExpenses) AS ZhiChuJinE "); cmdText.Append(" ,SUM(A.DistributionAmount) AS LiRunFenPeiJinE "); if (model.ComputeOrderType == EyouSoft.Model.EnumType.CompanyStructure.ComputeOrderType.统计确认成交订单) { cmdText.Append(" ,SUM(A.TotalOtherIncome+A.ChengJiaoJinE) AS ShouRuJinE "); cmdText.Append(" ,SUM(A.ChengJiaoRenShu) AS RenShu "); } else { cmdText.Append(" ,SUM(A.TotalOtherIncome+A.YouXiaoJinE) AS ShouRuJinE "); cmdText.Append(" ,SUM(A.YouXiaoRenShu) AS RenShu "); } cmdText.Append(" FROM [View_EarningsStatistic] AS A "); cmdText.AppendFormat(" WHERE A.CompanyId={0} ", model.CompanyId); if (!string.IsNullOrEmpty(HaveUserIds)) { cmdText.AppendFormat(" AND A.OperatorId IN({0}) ", HaveUserIds); } cmdText.Append(GetSQL(model)); cmdText.Append(" GROUP BY A.AreaId,A.AreaName "); cmdText.Append(" ORDER BY A.AreaId "); if (model.OrderIndex == 1) { cmdText.Append(" DESC "); } #endregion DbCommand cmd = _db.GetSqlStringCommand(cmdText.ToString()); using (IDataReader rdr = DbHelper.ExecuteReader(cmd, _db)) { //int index0 = rdr.GetOrdinal("AreaId"); while (rdr.Read()) { var item = new EyouSoft.Model.StatisticStructure.EarningsAreaStatistic(); item.AreaId = rdr.GetInt32(rdr.GetOrdinal("AreaId")); item.AreaName = rdr["AreaName"].ToString(); if (!rdr.IsDBNull(rdr.GetOrdinal("ShouRuJinE"))) { item.GrossIncome = rdr.GetDecimal(rdr.GetOrdinal("ShouRuJinE")); } if (!rdr.IsDBNull(rdr.GetOrdinal("ZhiChuJinE"))) { item.GrossOut = rdr.GetDecimal(rdr.GetOrdinal("ZhiChuJinE")); } if (!rdr.IsDBNull(rdr.GetOrdinal("TuanDuiShu"))) { item.TourNum = rdr.GetInt32(rdr.GetOrdinal("TuanDuiShu")); } if (!rdr.IsDBNull(rdr.GetOrdinal("LiRunFenPeiJinE"))) { item.TourShare = rdr.GetDecimal(rdr.GetOrdinal("LiRunFenPeiJinE")); } if (!rdr.IsDBNull(rdr.GetOrdinal("RenShu"))) { item.TourPeopleNum = rdr.GetInt32(rdr.GetOrdinal("RenShu")); } item.TourGross = item.GrossIncome - item.GrossOut; items.Add(item); } } return(items); }