/// <summary> /// 获取利润--部门统计 /// </summary> /// <param name="model">利润统计查询实体</param> /// <param name="HaveUserIds">用户Id集合,半角逗号分割</param> /// <returns></returns> public IList <EyouSoft.Model.StatisticStructure.EarningsDepartStatistic> GetEarningsDepartStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds) { IList <EyouSoft.Model.StatisticStructure.EarningsDepartStatistic> list = new List <EyouSoft.Model.StatisticStructure.EarningsDepartStatistic>(); string strOrder = string.Empty; string strWhere = this.GetSqlWhere(model, HaveUserIds, ref strOrder); StringBuilder strSql = new StringBuilder(" select "); strSql.Append(" DepartId,"); strSql.Append(" (select tbl_CompanyDepartment.DepartName from tbl_CompanyDepartment where tbl_CompanyDepartment.Id = DepartId) as DepartName, "); 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.DepartId = ves.DepartId 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 DepartId "); 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.EarningsDepartStatistic tmpModel = null; decimal FinanceSum = 0; int PeopleNumber = 0; while (dr.Read()) { tmpModel = new EyouSoft.Model.StatisticStructure.EarningsDepartStatistic(); if (!dr.IsDBNull(0)) { tmpModel.DepartId = dr.GetInt32(0); } if (!dr.IsDBNull(1)) { tmpModel.DepartName = 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); } 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, true); } tmpModel.SalesClerk = tmp; 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.EarningsDepartStatistic> GetEarningsDepartStatistic(EyouSoft.Model.StatisticStructure.QueryEarningsStatistic model, string HaveUserIds) { IList <EyouSoft.Model.StatisticStructure.EarningsDepartStatistic> items = new List <EyouSoft.Model.StatisticStructure.EarningsDepartStatistic>(); StringBuilder cmdText = new StringBuilder(); #region SQL cmdText.Append(" SELECT A.DepartId "); cmdText.Append(" ,(SELECT A1.DepartName FROM tbl_CompanyDepartment AS A1 WHERE A1.Id=A.DepartId) AS DeptName "); 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.DepartId "); cmdText.Append(" ORDER BY A.DepartId "); if (model.OrderIndex == 3) { 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.EarningsDepartStatistic(); if (!rdr.IsDBNull(rdr.GetOrdinal("DepartId"))) { item.DepartId = rdr.GetInt32(rdr.GetOrdinal("DepartId")); } item.DepartName = rdr["DeptName"].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); }