/// <summary> /// 获取利润--类型统计 /// </summary> /// <param name="model">利润统计查询实体</param> /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic> GetEarningsTypeStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds) { IList <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic> list = new List <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic>(); string strOrder = string.Empty; string strWhere = this.GetSqlWhere(model, HaveUserIds, ref strOrder); StringBuilder strSql = new StringBuilder(" select "); strSql.Append(" TourType, "); 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 Orders from View_EarningsStatistic as tmpVes where tmpVes.TourType = ves.TourType 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 TourType "); 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.EarningsTypeStatistic tmpModel = null; decimal FinanceSum = 0; int PeopleNumber = 0; while (dr.Read()) { tmpModel = new EyouSoft.Model.StatisticStructure.EarningsTypeStatistic(); if (!dr.IsDBNull(0)) { tmpModel.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType)dr.GetByte(0); } if (!dr.IsDBNull(1)) { tmpModel.TourNum = dr.GetInt32(1); } if (!dr.IsDBNull(2)) { tmpModel.GrossOut = dr.GetDecimal(2); } if (!dr.IsDBNull(3)) { tmpModel.TourGross = 0;//dr.GetDecimal(3); } if (!dr.IsDBNull(4)) { tmpModel.TourShare = dr.GetDecimal(4); } FinanceSum = 0; PeopleNumber = 0; IList <Model.StatisticStructure.StatisticOperator> tmp = null; if (!dr.IsDBNull(5)) { this.GetOrderInfoByXML(dr.GetString(5), model, ref FinanceSum, ref PeopleNumber, ref tmp, true); } tmpModel.SalesClerk = tmp; tmpModel.GrossIncome = FinanceSum; //此处只计算团款收入 tmpModel.TourPeopleNum = PeopleNumber; if (!dr.IsDBNull(6)) { tmpModel.GrossIncome += dr.GetDecimal(6); //此处加上杂费收入 } //团队毛利=总收入-总支出 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.EarningsTypeStatistic> GetEarningsTypeStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds) { IList <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic> items = new List <EyouSoft.Model.StatisticStructure.EarningsTypeStatistic>(); StringBuilder cmdText = new StringBuilder(); #region SQL cmdText.Append(" SELECT A.TourType "); 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.TourType "); cmdText.Append(" ORDER BY A.TourType "); if (model.OrderIndex == 5) { cmdText.Append(" DESC "); } #endregion DbCommand cmd = _db.GetSqlStringCommand(cmdText.ToString()); using (IDataReader rdr = DbHelper.ExecuteReader(cmd, _db)) { while (rdr.Read()) { var item = new EyouSoft.Model.StatisticStructure.EarningsTypeStatistic(); item.TourType = (EyouSoft.Model.EnumType.TourStructure.TourType)rdr.GetByte(rdr.GetOrdinal("TourType")); 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); }