/// <summary>
/// 获得数据列表
/// </summary>
public IList <Wuyiju.Model.YejiModel> GetYejiTongji(Wuyiju.Model.YejiModel.Query filter)
{
using (var db = new DataContext())
{
return(this.GetDao(db).GetYejiTongji(filter));
}
}
public IList <Wuyiju.Model.YejiModel> GetYejiTongji(Wuyiju.Model.YejiModel.Query filter)
{
StringBuilder newsql = new StringBuilder();
StringBuilder sql = new StringBuilder(@"select hj.*,ye.storenum,left(zu.position,4) as zubie,zd.tuijiannum from (select t.parent_id, t.`name` as yewuyuan, COUNT(case when z.id is not null and z.guanlian_id=t.id then 1 end) as guanliannum,
COUNT(case when z.id is not null and z.admin_id=t.id then 1 end) as xiaoshounum,
COUNT(case when z.id is not null and z.guanlian_id=t.id or z.admin_id=t.id then 1 end) as total_num,
SUM(case when z.id is not null and z.guanlian_id=t.id then z.price*2/3 end) as totalguanlian,
SUM(case when z.id is not null and z.admin_id=t.id then z.price*1/3 end) as totalxiaoshou,
SUM(case when z.id is not null and z.guanlian_id=t.id then z.price*2/3 end)+
SUM(case when z.id is not null and z.admin_id=t.id then z.price*1/3 end) as totalyeji
from ec_admin t LEFT JOIN
(select n.price,n.id,n.guanlian_id,n.admin_id from ec_order m LEFT JOIN ec_product n on n.id=m.product_id where
m.add_time>=@startdate and m.add_time<=@enddate and m.status!=2 and m.status!=3 and (m.pay_statu=1 or m.pay_statu=2 ) and m.del=0 ");
if (filter.status != null)
{
sql.Append(" and m.status=@status ");
}
if (filter.parent_id != null)
{
newsql = new StringBuilder(@") as z on t.id=z.admin_id or t.id=z.guanlian_id where t.role_id=8 and t.parent_id=@parent_id group by yewuyuan) as hj left join (select h.`name` as yeyuyuan,
COUNT(case when y.id is not null then 1 end) as storenum from ec_admin h left join ec_product y on y.guanlian_id=h.id where h.role_id=8
and y.add_time>=@startdate and y.add_time<=@enddate GROUP BY yeyuyuan)
as ye on hj.yewuyuan=ye.yeyuyuan LEFT JOIN (select sd.`name` as yeyuyuan,
COUNT(case when sz.id is not null then 1 end) as tuijiannum from ec_admin sd left join
ec_user sz on sd.username=sz.introducer where sd.role_id=8 and sz.add_time>=@startdate and sz.add_time<=@enddate GROUP BY yeyuyuan) as zd on ye.yeyuyuan=zd.yeyuyuan left join
ec_admin as zu on zu.id=hj.parent_id");
}
else
{
newsql = new StringBuilder(@") as z on t.id=z.admin_id or t.id=z.guanlian_id where t.role_id=8 group by yewuyuan) as hj left join (select h.`name` as yeyuyuan,
COUNT(case when y.id is not null then 1 end) as storenum from ec_admin h left join ec_product y on y.guanlian_id=h.id where h.role_id=8
and y.add_time>=@startdate and y.add_time<=@enddate GROUP BY yeyuyuan)
as ye on hj.yewuyuan=ye.yeyuyuan LEFT JOIN (select sd.`name` as yeyuyuan,
COUNT(case when sz.id is not null then 1 end) as tuijiannum from ec_admin sd left join
ec_user sz on sd.username=sz.introducer where sd.role_id=8 and sz.add_time>=@startdate and sz.add_time<=@enddate GROUP BY yeyuyuan) as zd on ye.yeyuyuan=zd.yeyuyuan left join
ec_admin as zu on zu.id=hj.parent_id");
}
newsql.Append(" order by " + filter.yejistyle + " desc;");
sql.Append(newsql.ToString());
DynamicParameters param = new DynamicParameters();
//sql.AndEquals("p.admin_id", "admin_id");
//sql.AndEquals("z.guanlian_id", "guanlian_id");
//sql.AndBetween("o.add_time", "startdate", "enddate");
//sql.AndEquals("o.status", "status");
//sql.AndEquals("z.parent_id", "parent_id");
if (filter != null)
{
param.AddDynamicParams(filter);
}
return(db.GetList <Wuyiju.Model.YejiModel>(sql, param));
}
