public BasePageList<CarInfoListViewModel> GetAllCarPageList(CarQueryModel query) { return _carervice.GetAllCarPageList(query); }
/// <summary> /// 车辆悬赏推荐 /// </summary> /// <param name="query"></param> /// <returns></returns> public BasePageList<CarInfoListViewModel> GetCarRewardPageList(CarQueryModel query) { const string spName = "sp_common_pager"; const string tableName = @"car_info as a 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.custid,a.pic_url,a.price,a.buyprice,a.dealprice,a.buytime,a.status,a.mileage,a.register_date,a.istop,c1.brandname as brand_name,c2.seriesname as series_name,c3.modelname as model_name,ct.cityname"; var orderField = string.IsNullOrWhiteSpace(query.Order) ? "a.createdtime desc" : query.Order; #region 查询条件 var sqlWhere = new StringBuilder("a.status=1"); //品牌 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.cityid != null) { sqlWhere.Append($" or a.cityid={query.cityid}"); } //销售价大于.. if (query.minprice.HasValue) { sqlWhere.Append($" or ( a.price>={query.minprice}"); } //销售价小于.. if (query.maxprice.HasValue) { sqlWhere.Append($" and a.price<={query.maxprice} )"); } //车龄大于 if (query.mincoty.HasValue) { var date = DateTime.Now.AddYears(-query.mincoty.Value).ToShortDateString(); sqlWhere.Append($" or ( a.register_date<='{date}'"); } //车龄小于 if (query.maxcoty.HasValue) { var date = DateTime.Now.AddYears(-query.maxcoty.Value).ToShortDateString(); sqlWhere.Append($" and a.register_date>='{date}' )"); } //里程大于 if (query.mincoty.HasValue) { sqlWhere.Append($" or ( a.mileage<='{query.mincoty}'"); } //里程小于 if (query.maxcoty.HasValue) { sqlWhere.Append($" and a.mileage<='{query.maxcoty}' )"); } #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; }
/// <summary> /// 获取车辆列表 /// </summary> /// <param name="query">查询条件</param> /// <returns></returns> public BasePageList<CarInfoListViewModel> GetCarPageList(CarQueryModel query) { const string spName = "sp_common_pager"; const string tableName = @"car_info as a 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.pic_url,a.price,a.buyprice,a.dealprice,a.buytime,a.status,a.mileage,a.register_date,c1.brandname as brand_name,c2.seriesname as series_name,c3.modelname as model_name,ct.cityname"; var orderField = string.IsNullOrWhiteSpace(query.Order) ? "a.createdtime desc" : query.Order; #region 查询条件 var sqlWhere = new StringBuilder("1=1"); sqlWhere.Append(query.status != null ? $" and a.status={query.status}" : " and a.status<>0"); if (!string.IsNullOrWhiteSpace(query.custid)) { sqlWhere.Append($" and a.custid='{query.custid}'"); } if (!string.IsNullOrWhiteSpace(query.title)) { sqlWhere.Append($" and a.title like '%{query.title}%'"); } //省份 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) { sqlWhere.Append($" and a.brand_id={query.brand_id}"); } //车系 if (query.series_id != null) { sqlWhere.Append($" and a.series_id={query.series_id}"); } //车型 if (query.model_id != null) { 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.SearchField) && query.model_id == null) { //sqlWhere.Append($" and (c1.brandname like '%{query.SearchField}%' or c2.seriesname like '%{query.SearchField}%')"); //车辆添加时会将【品牌/车系】放到该字段 sqlWhere.Append($" and title like '%{query.SearchField}%'"); } #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; }
/// <summary> /// 获取车辆列表 /// </summary> /// <param name="query">查询条件</param> /// <returns></returns> public BasePageList<CarInfoListViewModel> GetCarPageList(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 为神秘车源 sqlWhere.Append(query.status != null ? $" and a.status={query.status}" : " and a.status<>0"); if (!string.IsNullOrWhiteSpace(query.custid)) { sqlWhere.Append($" and a.custid='{query.custid}'"); } //省份 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; }