/// <summary> /// 获取利润--时间统计 /// </summary> /// <param name="model">利润统计查询实体</param> /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic> GetEarningsTimeStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds) { IList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic> list = new List <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic>(); string strOrder = string.Empty; string strWhere = this.GetSqlWhere(model, HaveUserIds, ref strOrder); StringBuilder strSql = new StringBuilder(" select "); strSql.Append(" year(LeaveDate) as CurrYear,month(LeaveDate) as CurrMonth, "); 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 year(tmpVes.LeaveDate) = year(ves.LeaveDate) and month(tmpVes.LeaveDate) = month(ves.LeaveDate) 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 year(LeaveDate),month(LeaveDate) "); 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.EarningsTimeStatistic tmpModel = null; decimal FinanceSum = 0; int PeopleNumber = 0; while (dr.Read()) { tmpModel = new EyouSoft.Model.StatisticStructure.EarningsTimeStatistic(); if (!dr.IsDBNull(0)) { tmpModel.CurrYear = dr.GetInt32(0); } if (!dr.IsDBNull(1)) { tmpModel.CurrMonth = dr.GetInt32(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); } FinanceSum = 0; PeopleNumber = 0; IList <Model.StatisticStructure.StatisticOperator> tmp = null; if (!dr.IsDBNull(6)) { this.GetOrderInfoByXML(dr.GetString(6), model, ref FinanceSum, ref PeopleNumber, ref tmp, false); } tmpModel.GrossIncome = FinanceSum; //此处只计算团款收入 tmpModel.TourPeopleNum = PeopleNumber; if (!dr.IsDBNull(7)) { tmpModel.GrossIncome += dr.GetDecimal(7); //此处加上杂费收入 } //团队毛利=总收入-总支出 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.EarningsTimeStatistic> GetEarningsTimeStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds) { IList <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic> items = new List <EyouSoft.Model.StatisticStructure.EarningsTimeStatistic>(); StringBuilder cmdText = new StringBuilder(); #region SQL cmdText.Append(" SELECT YEAR(A.LeaveDate) AS Year "); cmdText.Append(" ,MONTH(A.LeaveDate) AS Month "); 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 YEAR(A.LeaveDate),MONTH(A.LeaveDate) "); if (model.OrderIndex == 6) { cmdText.Append(" ORDER BY Year ASC,Month ASC "); } else { cmdText.Append(" ORDER BY Year DESC,Month DESC "); } #endregion DbCommand cmd = _db.GetSqlStringCommand(cmdText.ToString()); using (IDataReader rdr = DbHelper.ExecuteReader(cmd, _db)) { while (rdr.Read()) { var item = new EyouSoft.Model.StatisticStructure.EarningsTimeStatistic(); item.CurrMonth = rdr.GetInt32(rdr.GetOrdinal("Month")); item.CurrYear = rdr.GetInt32(rdr.GetOrdinal("Year")); 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); }