/// <summary>
/// 筛选某台太车辆之后的车辆(索引车辆为空，获取最新的几台车辆)
/// </summary>
/// <param name="pageParam"></param>
/// <param name="indexCarId"></param>
/// <param name="brandId"></param>
/// <param name="seriesId"></param>
/// <param name="seriesGrade"></param>
/// <param name="carActivity"></param>
/// <param name="brandCountry"></param>
/// <param name="carAgeBegin"></param>
/// <param name="CarAgeEnd"></param>
/// <param name="mileageBegin"></param>
/// <param name="mileageEnd"></param>
/// <param name="emissionBegin"></param>
/// <param name="emissionEnd"></param>
/// <param name="carDrive"></param>
/// <param name="carEnergy"></param>
/// <param name="carGearbox"></param>
/// <param name="carSeat"></param>
/// <returns></returns>
public List <ResCarConciseInfo> GetLaterFilterCars(PageParam pageParam, string indexCarId = null, string brandId = null, string seriesId = null, CarActivity carActivity = CarActivity.None,
CarSeriesGrade seriesGrade = CarSeriesGrade.None, CarBrandCountry brandCountry = CarBrandCountry.None, CarDrive carDrive = CarDrive.None,
CarEnergy carEnergy = CarEnergy.None, CarGearbox carGearbox = CarGearbox.None, CarSeat carSeat = CarSeat.None)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var sql = new StringBuilder(" SELECT cc.Id,cc.Id as CarInfoId, cc.`Name`,cc.CarsDetailsId,cc.GuidePrice,cc.SellingPrice,cc.SurfaceImage,cc.CarActivity,cc.UpdateTime,cd.CarLicenseTime,cd.Mileage FROM " +
" ( SELECT carc.Id,carc.`Name`,carc.SeriesId, carc.CarsDetailsId, carc.GuidePrice, carc.SellingPrice, carc.SurfaceImage, carc.CarActivity, carc.UpdateTime FROM CarsConcise as carc ");
if (seriesGrade != CarSeriesGrade.None || brandCountry != CarBrandCountry.None)
{
sql.Append(" INNER JOIN ( SELECT Id FROM CarsBrandSeries WHERE 1=1 ");
if (seriesGrade != CarSeriesGrade.None)
{
sql.Append(" and SeriesGrade= " + (int)seriesGrade);
}
if (brandCountry != CarBrandCountry.None)
{
sql.Append(" and BrandCountry= " + (int)brandCountry);
}
sql.Append(" ) AS cbs ON cbs.Id = carc.SeriesId ");
}
sql.Append("WHERE carc.CarSell = 0 and carc.State=1 ");
if (!string.IsNullOrWhiteSpace(indexCarId))
{
sql.Append(" and carc.Id > '" + indexCarId + "'");
}
if (!string.IsNullOrWhiteSpace(brandId))
{
sql.Append(" and carc.BrandId ='" + brandId + "'");
}
if (!string.IsNullOrWhiteSpace(seriesId))
{
sql.Append(" and carc.SeriesId = '" + seriesId + "'");
}
if (carActivity != CarActivity.None)
{
sql.Append(" and carc.CarActivity =" + (int)carActivity);
}
sql.Append(" ) AS cc INNER JOIN(SELECT Id, CarLicenseTime, Mileage FROM CarsDetails where 1=1 ");
if (carDrive != CarDrive.None)
{
sql.Append(" and CarDrive= " + (int)carDrive);
}
if (carEnergy != CarEnergy.None)
{
sql.Append(" and CarEnergy= " + (int)carEnergy);
}
if (carGearbox != CarGearbox.None)
{
sql.Append(" and CarGearbox= " + (int)carGearbox);
}
if (carSeat != CarSeat.None)
{
sql.Append(" and CarSeat= " + (int)carSeat);
}
sql.Append(" )AS cd ON cd.Id = cc.CarsDetailsId ");
if (pageParam.SortType == SortType.Time)
{
if (pageParam.Ascending == true)
{
sql.Append(" ORDER BY cc.UpdateTime ");
}
else
{
sql.Append(" ORDER BY cc.UpdateTime desc");
}
}
else
{
if (pageParam.Ascending == true)
{
sql.Append(" ORDER BY cc.SellingPrice ");
}
else
{
sql.Append(" ORDER BY cc.SellingPrice desc");
}
}
//var ss = sql.ToString();
var query = dbs.Select <ResCarConciseInfo>(sql.ToString()).Take(pageParam.PageSize).ToList();
return(query);
}
}
public CarsConcise(string AccountId = null, string CarsDetailsId = null, string CorporationId = null, string Name = null, string BrandId = null, string SeriesId = null,
Decimal GuidePrice = 0, Decimal SellingPrice = 0, Decimal BasePrice = 0,
ValidityState State = ValidityState.Disabled, string SurfaceImage = null, CarSell CarSell = CarSell.Sale, CarActivity CarActivity = CarActivity.None)
{
this.AccountId = AccountId;
this.CarsDetailsId = CarsDetailsId;
this.CorporationId = CorporationId;
this.CarSell = CarSell;
this.BrandId = BrandId;
this.SeriesId = SeriesId;
this.CarActivity = CarActivity;
this.Name = Name;
this.GuidePrice = GuidePrice;
this.SellingPrice = SellingPrice;
this.BasePrice = BasePrice;
this.State = State;
this.SurfaceImage = SurfaceImage;
this.UpdateTime = DateTime.Now;
}
/// <summary>
/// 筛选某台车辆之前的车辆( CarSeriesGrade seriesGrade = CarSeriesGrade.None, CarBrandCountry brandCountry = CarBrandCountry.None, CarDrive carDrive = CarDrive.None, CarEnergy carEnergy = CarEnergy.None, CarGearbox carGearbox = CarGearbox.None, CarSeat carSeat = CarSeat.None,DateTime ? carAgeBegin = null, DateTime? CarAgeEnd = null, float mileageBegin = 0, float mileageEnd = 0, float emissionBegin = 0, float emissionEnd = 0)
/// </summary>
/// <param name="pageParam"></param>
/// <param name="indexCarId"></param>
/// <param name="brandId"></param>
/// <param name="seriesId"></param>
/// <param name="seriesGrade"></param>
/// <param name="carActivity"></param>
/// <param name="brandCountry"></param>
/// <param name="carAgeBegin"></param>
/// <param name="CarAgeEnd"></param>
/// <param name="mileageBegin"></param>
/// <param name="mileageEnd"></param>
/// <param name="emissionBegin"></param>
/// <param name="emissionEnd"></param>
/// <param name="carDrive"></param>
/// <param name="carEnergy"></param>
/// <param name="carGearbox"></param>
/// <param name="carSeat"></param>
/// <returns></returns>
public List <ResCarConciseInfo> GetBeforeFilterCars(PageParam pageParam, string indexCarId, string brandId = null, string seriesId = null, CarActivity carActivity = CarActivity.None,
CarSeriesGrade seriesGrade = CarSeriesGrade.None, CarBrandCountry brandCountry = CarBrandCountry.None, CarDrive carDrive = CarDrive.None,
CarEnergy carEnergy = CarEnergy.None, CarGearbox carGearbox = CarGearbox.None, CarSeat carSeat = CarSeat.None)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
//var ret = dbs.From<CarsConcise>().Join<CarsConcise, CarsDetails>((cc, cd) => cc.CarsDetailsId == cd.Id).Where<CarsConcise>(x => x.State == ValidityState.Enabled && x.CarSell == CarSell.Sale && x.Id.CompareTo(indexCarId) < 0
// && (string.IsNullOrWhiteSpace(brandId) || x.BrandId == brandId) && (string.IsNullOrWhiteSpace(seriesId) || x.SeriesId == seriesId) && (carActivity == CarActivity.None || x.CarActivity == carActivity))
//.Select<CarsConcise, CarsDetails>((cc, cd) => new { CarInfoId = cc.Id, cc.Name, cc.BrandId, cc.CarsDetailsId, cc.AccountId, cc.CorporationId, cc.GuidePrice, cc.SellingPrice, cc.SurfaceImage, cc.CarActivity, cc.UpdateTime, cd.CarLicenseTime, cd.Mileage });
//if (pageParam.SortType == SortType.Price)
//{
// if (pageParam.Ascending == true)
// {
// var list = dbs.Select<ResCarConciseInfo>(ret).OrderBy(x => x.SellingPrice).Take(pageParam.PageSize).ToList();
// return list;
// }
// else
// {
// var list = dbs.Select<ResCarConciseInfo>(ret).OrderByDescending(x => x.SellingPrice).Take(pageParam.PageSize).ToList();
// return list;
// }
//}
//else
//{
// if (pageParam.Ascending == true)
// {
// var list = dbs.Select<ResCarConciseInfo>(ret).OrderBy(x => x.UpdateTime).Take(pageParam.PageSize).ToList();
// return list;
// }
// else
// {
// var list = dbs.Select<ResCarConciseInfo>(ret).OrderByDescending(x => x.UpdateTime).Take(pageParam.PageSize).ToList();
// return list;
// }
//}
var sql = new StringBuilder(" SELECT cc.Id,cc.Id as CarInfoId, cc.`Name`,cc.CarsDetailsId,cc.GuidePrice,cc.SellingPrice,cc.SurfaceImage,cc.CarActivity,cc.UpdateTime,cd.CarLicenseTime,cd.Mileage FROM " +
" ( SELECT carc.Id,carc.`Name`,carc.SeriesId, carc.CarsDetailsId, carc.GuidePrice, carc.SellingPrice, carc.SurfaceImage, carc.CarActivity, carc.UpdateTime FROM CarsConcise as carc ");
if (seriesGrade != CarSeriesGrade.None || brandCountry != CarBrandCountry.None)
{
sql.Append(" INNER JOIN ( SELECT Id FROM CarsBrandSeries WHERE 1=1 ");
if (seriesGrade != CarSeriesGrade.None)
{
sql.Append(" and SeriesGrade= " + (int)seriesGrade);
}
if (brandCountry != CarBrandCountry.None)
{
sql.Append(" and BrandCountry= " + (int)brandCountry);
}
sql.Append(" ) AS cbs ON cbs.Id = carc.SeriesId ");
}
sql.Append("WHERE carc.CarSell = 0 and carc.State=1 ");
if (!string.IsNullOrWhiteSpace(indexCarId))
{
sql.Append(" and carc.Id < '" + indexCarId + "'");
}
if (!string.IsNullOrWhiteSpace(brandId))
{
sql.Append(" and carc.BrandId ='" + brandId + "'");
}
if (!string.IsNullOrWhiteSpace(seriesId))
{
sql.Append(" and carc.SeriesId = '" + seriesId + "'");
}
if (carActivity != CarActivity.None)
{
sql.Append(" and carc.CarActivity =" + (int)carActivity);
}
sql.Append(" ) AS cc INNER JOIN(SELECT Id, CarLicenseTime, Mileage FROM CarsDetails where 1=1 ");
if (carDrive != CarDrive.None)
{
sql.Append(" and CarDrive= " + (int)carDrive);
}
if (carEnergy != CarEnergy.None)
{
sql.Append(" and CarEnergy= " + (int)carEnergy);
}
if (carGearbox != CarGearbox.None)
{
sql.Append(" and CarGearbox= " + (int)carGearbox);
}
if (carSeat != CarSeat.None)
{
sql.Append(" and CarSeat= " + (int)carSeat);
}
sql.Append(" )AS cd ON cd.Id = cc.CarsDetailsId ");
if (pageParam.SortType == SortType.Time)
{
if (pageParam.Ascending == true)
{
sql.Append(" ORDER BY cc.UpdateTime ");
}
else
{
sql.Append(" ORDER BY cc.UpdateTime desc");
}
}
else
{
if (pageParam.Ascending == true)
{
sql.Append(" ORDER BY cc.SellingPrice ");
}
else
{
sql.Append(" ORDER BY cc.SellingPrice desc");
}
}
//var ss = sql.ToString();
var query = dbs.Select <ResCarConciseInfo>(sql.ToString()).Take(pageParam.PageSize).ToList();
return(query);
}
}
public ReqCar(string OpenId = null, string CorporationId = null, string CarId = null, CarSell CarSell = CarSell.Sale, CarBrandType BrandType = CarBrandType.None, string IndexCarId = null, string BrandId = null, string SeriesId = null,
CarSeriesGrade SeriesGrade = CarSeriesGrade.None, CarBrandCountry BrandCountry = CarBrandCountry.None, CarActivity CarActivity = CarActivity.None, int CarAgeBegin = 0, int CarAgeEnd = 0,
int MileageBegin = 0, int MileageEnd = 15, CarDrive CarDrive = CarDrive.None, CarEnergy CarEnergy = CarEnergy.None, CarGearbox CarGearbox = CarGearbox.None, CarSeat CarSeat = CarSeat.None)
{
this.OpenId = OpenId;
this.CorporationId = CorporationId;
this.CarId = CarId;
this.CarSell = CarSell;
this.BrandType = BrandType;
this.IndexCarId = IndexCarId;
this.BrandId = BrandId;
this.SeriesId = SeriesId;
this.SeriesGrade = SeriesGrade;
this.BrandCountry = BrandCountry;
this.CarActivity = CarActivity;
this.CarAgeBegin = CarAgeBegin;
this.CarAgeEnd = CarAgeEnd;
this.MileageBegin = MileageBegin;
this.MileageEnd = MileageEnd;
this.CarDrive = CarDrive;
this.CarEnergy = CarEnergy;
this.CarGearbox = CarGearbox;
this.CarSeat = CarSeat;
}