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