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