/// <summary> /// 获取拍卖车辆列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public BasePageList<AuctionCarInfoViewModel> GetAuctionList(AuctionCarInfoQueryModel query) { const string spName = "sp_common_pager"; const string tableName = @"auction_carinfo as a left join car_info as b on b.innerid=a.carid left join base_carbrand as c1 on b.brand_id=c1.innerid left join base_carseries as c2 on b.series_id=c2.innerid left join base_carmodel as c3 on b.model_id=c3.innerid left join base_city as ct on b.cityid=ct.innerid left join base_province as pr on b.provid=pr.innerid left join sys_user as su on su.innerid=a.operatedid"; const string fields = "a.innerid,a.no,a.carid,a.mobile,a.lowestprice,a.status as auditstatus,b.pic_url,a.status,b.price,b.mileage,b.register_date,a.publishedtime,a.validtime,b.createdtime,c1.brandname as brand_name,c2.seriesname as series_name,c3.modelname as model_name,c3.modelprice,ct.cityname,pr.provname,su.username"; var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime asc " : query.Order; var sqlWhere = new StringBuilder("1=1"); //车商手机号 if (!string.IsNullOrWhiteSpace(query.mobile)) { sqlWhere.Append($" and a.mobile like '%{query.mobile}%'"); } //省份 if (query.provid != null) { sqlWhere.Append($" and b.provid={query.provid}"); } //城市(车源地) if (query.cityid != null) { sqlWhere.Append($" and b.cityid={query.cityid}"); } //品牌 if (query.brand_id != null && query.brand_id != 0) { sqlWhere.Append($" and b.brand_id={query.brand_id}"); } //车系 if (query.series_id != null && query.series_id != 0) { sqlWhere.Append($" and b.series_id={query.series_id}"); } //车型 if (query.model_id != null && query.model_id != 0) { sqlWhere.Append($" and b.model_id={query.model_id}"); } //里程数 if (query.minmileage.HasValue) { sqlWhere.Append($" and b.mileage>={query.minmileage}"); } //里程数 if (query.maxmileage.HasValue) { sqlWhere.Append($" and b.mileage<{query.maxmileage}"); } //上牌时间 if (query.register_date.HasValue) { sqlWhere.Append($" and b.register_date='{query.register_date}'"); } //状态 if (query.status.HasValue) { sqlWhere.Append($" and a.status='{query.status}'"); } //员工编号 if (query.userno != null) { sqlWhere.Append($" and su.no='{query.userno}'"); } //拍品编号 if (query.auctionno != null) { sqlWhere.Append($" and a.no='{query.auctionno}'"); } //业务员Id operatedid if (query.operatedid != null) { sqlWhere.Append($" and a.operatedid='{query.operatedid}'"); } var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex); var list = Helper.ExecutePaging<AuctionCarInfoViewModel>(model, query.Echo); return list; }
/// <summary> /// 获取正在拍卖的车辆列表 /// </summary> /// <param name="query"></param> /// <returns></returns> public BasePageList<AuctionCarInfoViewModel> GetAuctioningList(AuctionCarInfoQueryModel query) { const string spName = "sp_common_pager"; string tableName = @"auction_carinfo as a left join car_info as b on b.innerid=a.carid left join base_carbrand as c1 on b.brand_id=c1.innerid left join base_carseries as c2 on b.series_id=c2.innerid left join base_carmodel as c3 on b.model_id=c3.innerid left join base_city as ct on b.cityid=ct.innerid left join base_province as pr on b.provid=pr.innerid left join (select count(1) as count,auctionid from auction_participant group by auctionid) d on d.auctionid=a.innerid "; string fields = @"a.innerid,a.mobile,a.lowestprice,a.status as auditstatus,b.pic_url,b.status,b.price,b.mileage,b.register_date,a.validtime,b.createdtime ,c1.brandname as brand_name,a.no,c2.seriesname as series_name,c3.modelname as model_name,c3.modelprice,ct.cityname,pr.provname,d.count"; if (!string.IsNullOrWhiteSpace(query.userid)) { tableName += " left join (select count(1) as follow, auctionid from auction_follow where isdelete=0 and userid = '" + query.userid + "' group by auctionid ) e on e.auctionid = a.innerid"; fields += " ,ifnull(e.follow,0) as follow "; } var oldField = string.IsNullOrWhiteSpace(query.Order) ? " a.createdtime asc " : query.Order; var sqlWhere = new StringBuilder(" a.status=6 "); //省份 if (query.provid != null) { sqlWhere.Append($" and b.provid={query.provid}"); } //城市 if (query.cityid != null) { sqlWhere.Append($" and b.cityid={query.cityid}"); } //品牌 if (query.brand_id != null && query.brand_id != 0) { sqlWhere.Append($" and b.brand_id={query.brand_id}"); } //车系 if (query.series_id != null && query.series_id != 0) { sqlWhere.Append($" and b.series_id={query.series_id}"); } //车型 if (query.model_id != null && query.model_id != 0) { sqlWhere.Append($" and b.model_id={query.model_id}"); } //开始时间 if (query.publishedtime.HasValue) { sqlWhere.Append($" and a.publishedtime='{query.publishedtime}'"); } //结束时间 if (query.validtime.HasValue) { sqlWhere.Append($" and a.validtime='{query.validtime}'"); } else { sqlWhere.Append($" and a.validtime>='{DateTime.Now}'"); } //里程数 if (query.minmileage.HasValue) { sqlWhere.Append($" and b.mileage>={query.minmileage}"); } //里程数 if (query.maxmileage.HasValue) { sqlWhere.Append($" and b.mileage<{query.maxmileage}"); } //上牌时间 if (query.register_date.HasValue) { sqlWhere.Append($" and YEAR(b.register_date)=YEAR('{query.register_date}')"); } var model = new PagingModel(spName, tableName, fields, oldField, sqlWhere.ToString(), query.PageSize, query.PageIndex); var list = Helper.ExecutePaging<AuctionCarInfoViewModel>(model, query.Echo); return list; }