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