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