public BasePageList<CarInfoListViewModel> GetAllCarPageList(CarQueryModel query)
{
const string spName = "sp_common_pager";
const string tableName = @"car_info as a
left join auction_carinfo as b on b.carid=a.innerid
left join base_carbrand as c1 on a.brand_id=c1.innerid
left join base_carseries as c2 on a.series_id=c2.innerid
left join base_carmodel as c3 on a.model_id=c3.innerid
left join base_city as ct on a.cityid=ct.innerid ";
const string fields = "a.innerid,a.carno,a.custid,a.pic_url,a.price,a.buyprice,a.dealprice,a.buytime,a.status,a.mileage,a.register_date,a.seller_type as type,c1.brandname as brand_name,c2.seriesname as series_name,c3.modelname as model_name,ct.cityname,a.istop,b.status as auditstatus,(select count(1) from car_tipoff where carid=a.innerid and status=1) as toNum";
var orderField = string.IsNullOrWhiteSpace(query.Order) ? "a.refreshtime desc" : query.Order;
#region 查询条件
var sqlWhere = new StringBuilder("1=1 and a.seller_type<>3"); //seller_type=3 为神秘车源
if (query.status != null)
{
sqlWhere.Append($" and a.status={query.status}");
}
if (!string.IsNullOrWhiteSpace(query.custid))
{
sqlWhere.Append($" and a.custid='{query.custid}'");
}
if (!string.IsNullOrWhiteSpace(query.carno))
{
sqlWhere.Append($" and a.carno='{query.carno}'");
}
//省份
if (query.provid != null)
{
sqlWhere.Append($" and a.provid={query.provid}");
}
//城市
if (query.cityid != null)
{
sqlWhere.Append($" and a.cityid={query.cityid}");
}
//品牌
if (query.brand_id != null && query.brand_id != 0)
{
sqlWhere.Append($" and a.brand_id={query.brand_id}");
}
//车系
if (query.series_id != null && query.series_id != 0)
{
sqlWhere.Append($" and a.series_id={query.series_id}");
}
//车型
if (query.model_id != null && query.model_id != 0)
{
sqlWhere.Append($" and a.model_id={query.model_id}");
}
//收购价大于..
if (query.minbuyprice.HasValue)
{
sqlWhere.Append($" and a.buyprice>={query.minbuyprice}");
}
//收购价小于..
if (query.maxbuyprice.HasValue)
{
sqlWhere.Append($" and a.buyprice<={query.maxbuyprice}");
}
if (!string.IsNullOrWhiteSpace(query.keyword) && query.model_id == null)
{
sqlWhere.Append($" and (c1.brandname like '%{query.keyword}%' or c2.seriesname like '%{query.keyword}%')");
}
//用户ID
if (!string.IsNullOrWhiteSpace(query.userid))
{
sqlWhere.Append($" and cityid in (select cityid from sys_user_city where userid='{query.userid}')");
}
//有举报的
if (query.tipoffonum != null)
{
sqlWhere.Append(" and (select count(1) from car_tipoff where carid=a.innerid and status=1)>0");
}
#endregion
var model = new PagingModel(spName, tableName, fields, orderField, sqlWhere.ToString(), query.PageSize, query.PageIndex);
var list = Helper.ExecutePaging<CarInfoListViewModel>(model, query.Echo);
return list;
}