public string GetWhere(CarGlobalQueryModel query)
{
var sqlWhere = new StringBuilder(""); //在售和已售车辆
//省份
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.minprice.HasValue)
{
sqlWhere.Append($" and a.price>={query.minprice}");
}
//销售价小于..
if (query.maxprice.HasValue)
{
sqlWhere.Append($" and a.price<={query.maxprice}");
}
//上牌时间 <
if (query.minyear.HasValue)
{
var date = DateTime.Now.AddYears(-query.minyear.Value).ToShortDateString();
sqlWhere.Append($" and a.register_date<='{date}'");
}
//上牌时间 >
if (query.maxyear.HasValue)
{
var date = DateTime.Now.AddYears(-query.maxyear.Value).ToShortDateString();
sqlWhere.Append($" and a.register_date>='{date}'");
}
//行驶里程 >
if (query.minmileage.HasValue)
{
sqlWhere.Append($" and a.mileage>='{query.minmileage}'");
}
//行驶里程 <
if (query.maxmileage.HasValue)
{
sqlWhere.Append($" and a.mileage<='{query.maxmileage}'");
}
//颜色
if (query.colorid.HasValue)
{
sqlWhere.Append($" and a.colorid={query.colorid}");
}
//排量
if (!string.IsNullOrWhiteSpace(query.liter))
{
sqlWhere.Append($" and c3.liter='{query.liter.Trim()}'");
}
//变速箱类型
if (!string.IsNullOrWhiteSpace(query.gear))
{
sqlWhere.Append($" and c3.geartype='{query.gear.Trim()}'");
}
//车源类型
if (query.type.HasValue)
{
sqlWhere.Append($" and a.seller_type={query.type}");
}
//关键字搜索
if (!string.IsNullOrWhiteSpace(query.keyword))
{
sqlWhere.Append($" and (c1.brandname like '%{query.keyword}%' or c2.seriesname like '%{query.keyword}%' or a.carno='{query.keyword}')");
}
return sqlWhere.ToString();
}