public Account ByOpenId(string openid, ValidityState state)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
return(dbs.Single <Account>(a => a.OpenId == openid && a.State == state));
}
}
public Corporation GetCorporationInfo(string id)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
return(dbs.Single <Corporation>(x => x.Id == id));
}
}
public Account ByWeChat(string wechat, ValidityState state)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
return(dbs.Single <Account>(a => a.Wechat == wechat && a.State == state));
}
}
public Account ById(string id)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
return(dbs.SingleById <Account>(id));
}
}
public Account GetAccount(string openid)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
return(dbs.Single <Account>(a => a.OpenId == openid));
}
}
/// <summary>
/// 查询公司信息
/// </summary>
/// <param name="id"></param>
/// <param name="state"></param>
/// <returns></returns>
public Corporation GetCorporationInfo(string id, ValidityState state)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
return(dbs.Single <Corporation>(x => x.Id == id && x.State == state));
}
}
public Corporation GetCorporationInfoByName(string name)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
return(dbs.Single <Corporation>(x => x.Name == name));
}
}
/// <summary>
/// 修改品牌车系
/// </summary>
/// <param name="state"></param>
/// <returns></returns>
public long UpdateBrandSeries(CarsBrandSeries cbs)
{
using (var dba = WxDbFactory.GetDbContext("hch_dba").MyDB)
{
var ret = dba.Update <CarsBrandSeries>(cbs);
return(ret);
}
}
/// <summary>
/// 获取品牌、车系信息
/// </summary>
/// <param name="BrandSeriesId"></param>
/// <returns></returns>
public CarsBrandSeries GetBrandSeriesDetail(string BrandSeriesId)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var ret = dbs.Single <CarsBrandSeries>(s => s.Id == BrandSeriesId);
return(ret);
}
}
/// <summary>
/// 获得品牌下的车系
/// </summary>
/// <param name="BrandSeriesId"></param>
/// <param name="state"></param>
/// <returns></returns>
public List <CarsBrandSeries> GetBrandSeries(string BrandSeriesId, ValidityState state)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var ret = dbs.Select <CarsBrandSeries>(s => s.ParentId == BrandSeriesId && s.State == state).OrderBy(x => x.Name).ToList();
return(ret);
}
}
/// <summary>
/// 获取所有品牌
/// </summary>
/// <param name="state"></param>
/// <returns></returns>
public List <CarsBrandSeries> GetAllBrand(ValidityState state)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var ret = dbs.Select <CarsBrandSeries>(s => s.ParentId == null && s.State == state && s.SeriesGrade == CarSeriesGrade.None).OrderBy(x => x.Capital).ToList();
return(ret);
}
}
/// <summary>
/// 修改车辆详情
/// </summary>
/// <param name="state"></param>
/// <returns></returns>
public long UpdateCarDetail(CarsDetails cd)
{
using (var dba = WxDbFactory.GetDbContext("hch_dba").MyDB)
{
var ret = dba.Update <CarsDetails>(cd);
return(ret);
}
}
/// <summary>
/// 获取车辆的详情
/// </summary>
/// <param name="cardetailid"></param>
/// <param name="state"></param>
/// <returns></returns>
public CarsDetails GetCarDetail(string cardetailid)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var ret = dbs.Single <CarsDetails>(s => s.Id == cardetailid);
return(ret);
}
}
/// <summary>
/// 修改车辆简洁
/// </summary>
/// <param name="state"></param>
/// <returns></returns>
public long UpdateCarConcise(CarsConcise cc)
{
using (var dba = WxDbFactory.GetDbContext("hch_dba").MyDB)
{
var ret = dba.Update <CarsConcise>(cc);
return(ret);
}
}
/// <summary>
/// 获取车辆的简介信息
/// </summary>
/// <param name="carinfoid"></param>
/// <param name="state"></param>
/// <returns></returns>
public CarsConcise GetCarInfo(string carinfoid, ValidityState state)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var ret = dbs.Single <CarsConcise>(s => s.Id == carinfoid && s.State == state);
return(ret);
}
}
/// <summary>
/// 修改公司
/// </summary>
/// <param name="state"></param>
/// <returns></returns>
public long UpdateCorporation(Corporation co)
{
using (var dba = WxDbFactory.GetDbContext("hch_dba").MyDB)
{
var ret = dba.Update <Corporation>(co);
return(ret);
}
}
/// <summary>
/// 修改用户
/// </summary>
/// <param name="state"></param>
/// <returns></returns>
public long UpdateAccount(Account ac)
{
using (var dba = WxDbFactory.GetDbContext("hch_dba").MyDB)
{
var ret = dba.Update <Account>(ac);
return(ret);
}
}
/// <summary>
/// 获取热门品牌
/// </summary>
/// <param name="state"></param>
/// <returns></returns>
public List <CarsBrandSeries> GetBrandHot(ValidityState state, PageParam pageParam)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var ret = dbs.Select <CarsBrandSeries>(s => s.BrandType == CarBrandType.Hot && s.State == state).OrderBy(x => x.Capital).Take(pageParam.PageSize).ToList();
return(ret);
}
}
/// <summary>
/// 获取某台车辆的详细信息，包括低价
/// </summary>
/// <param name="carId"></param>
/// <returns></returns>
public ResCarDetail GetCarDetailToRes(string carId)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var q = dbs.From <CarsConcise>().Join <CarsDetails>((cc, cd) => cc.CarsDetailsId == cd.Id).Where(x => x.State == ValidityState.Enabled && x.Id == carId).Select <CarsConcise, CarsDetails>((cc, cd) => new { CarInfoId = cc.Id, cc.CarsDetailsId, cc.Name, cc.GuidePrice, cc.SellingPrice, cc.BasePrice, cc.SurfaceImage, cc.CarActivity, cc.UpdateTime, cd.CarLicenseTime, cd.Mileage, cc.AccountId, cc.CorporationId, cd.Appearance, cd.Interior, cd.CarConfig, cd.Emission, cd.Images, cd.CarDrive, cd.CarEnergy, cd.CarGearbox, cd.CarSeat, cd.CarEmissionStandard });
var ret = dbs.Single <ResCarDetail>(q);
return(ret);
}
}
/// <summary>
/// 查询今天发布的某台太车辆之前的车辆
/// </summary>
/// <param name="pageParam"></param>
/// <param name="indexCarId"></param>
/// <returns></returns>
public List <ResCarConciseInfo> GetBeforeTodayCars(PageParam pageParam, string indexCarId)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var q = dbs.From <CarsConcise>().Join <CarsConcise, CarsDetails>((cc, cd) => cc.CarsDetailsId == cd.Id).Where(x => x.State == ValidityState.Enabled && x.CarSell == CarSell.Sale && x.UpdateTime >= pageParam.BeginTime && x.UpdateTime <= pageParam.Endtime).Select <CarsConcise, CarsDetails>((cc, cd) => new { cc.Id, CarInfoId = cc.Id, cc.CarsDetailsId, cc.Name, cc.GuidePrice, cc.SellingPrice, cc.SurfaceImage, cc.CarActivity, cc.UpdateTime, cd.CarLicenseTime, cd.Mileage });
var ret = dbs.Select <ResCarConciseInfo>(q).Where(x => x.Id.CompareTo(indexCarId) < 0).OrderByDescending(x => x.UpdateTime).Take(pageParam.PageSize).ToList();
return(ret);
}
}
/// <summary>
/// 查询某个人发布的(在售、下架)某台太车辆之后的车辆(索引车辆为空，获取最新的几台车辆)
/// </summary>
/// <param name="openId"></param>
/// <param name="carSell"></param>
/// <param name="pageParam"></param>
/// <param name="indexCarId"></param>
/// <returns></returns>
public List <ResCarConciseInfo> GetLaterUserCars(string openId, CarSell carSell, PageParam pageParam, string indexCarId = null)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var q = dbs.From <CarsConcise>().Join <CarsConcise, CarsDetails>((cc, cd) => cc.CarsDetailsId == cd.Id).Where(x => x.State == ValidityState.Enabled && x.CarSell == carSell && x.AccountId == openId).Select <CarsConcise, CarsDetails>((cc, cd) => new { cc.Id, CarInfoId = cc.Id, cc.CarsDetailsId, cc.Name, cc.GuidePrice, cc.SellingPrice, cc.SurfaceImage, cc.CarActivity, cc.UpdateTime, cd.CarLicenseTime, cd.Mileage });
var ret = dbs.Select <ResCarConciseInfo>(q).Where(x => (string.IsNullOrWhiteSpace(indexCarId) || x.Id.CompareTo(indexCarId) > 0)).OrderByDescending(x => x.UpdateTime).Take(pageParam.PageSize).ToList();
return(ret);
}
}
/// <summary>
/// 根据用户openid获取公司信息（含个人信息）
/// </summary>
/// <param name="opendid"></param>
/// <param name="state"></param>
/// <returns></returns>
public ResCorporation GetCorporation(string openid, ValidityState state)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
ResCorporation ret = dbs.Single <ResCorporation>("SELECT ac.`Name`AS AccountName,ac.OpenId,ac.Phone,ac.Wechat,ac.WechatName,co.`Name` as CorporationName,co.Address,co.FixPhone,co.Id,co.Presentation,co.Image AS CorporationImage FROM"
+ " (SELECT Wechat, WechatName, OpenId, Phone,`Name`, Corporation FROM Account WHERE OpenId = @openid AND State=@state) AS ac"
+ " INNER JOIN Corporation AS co ON co.Id = ac.Corporation AND co.State=@state", new { openid = openid, state = state });
return(ret);
}
}
/// <summary>
/// 获取品牌分组
/// </summary>
/// <param name="state"></param>
/// <returns></returns>
public List <ResGroupBrand> GetGroupBrand(ValidityState state)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var sql = $" SELECT Capital,count(Capital) AS Total FROM CarsBrandSeries " +
$" WHERE ParentId IS NULL AND State={(int)state} AND SeriesGrade =0 group BY Capital ASC ";
var ret = dbs.Select <ResGroupBrand>(sql);
return(ret);
}
}
/// <summary>
/// 获取某个分组下的品牌
/// </summary>
/// <param name="state"></param>
/// <returns></returns>
public List <ResBrand> GetGroupBrandList(ValidityState state, string capital)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
var sql = $"SELECT Id, Capital,`Name`,BrandLogo,BrandType,BrandCountry,State,`TimeStamp` FROM CarsBrandSeries " +
$" WHERE ParentId IS NULL AND State={(int)state} AND SeriesGrade =0 AND Capital='{capital}' ORDER BY `Name`";
var ret = dbs.Select <ResBrand>(sql);
return(ret);
}
}
public int Build(IWxDbContext context, params string[] args)
{
using (var db = WxDbFactory.GetDbContext().MyDB)
{
if (db.Count <CarsBrandSeries>() == 0)
{
var Audi = new CarsBrandSeries("奥迪", "A", "ad.jpg", ValidityState.Enabled, CarBrandType.Hot, CarBrandCountry.Germany);
var BMW = new CarsBrandSeries("宝马", "B", "bm.jpg", ValidityState.Enabled, CarBrandType.Hot, CarBrandCountry.Germany);
var Benz = new CarsBrandSeries("奔驰", "B", "bc.jpg", ValidityState.Enabled, CarBrandType.Hot, CarBrandCountry.Germany);
var Porsche = new CarsBrandSeries("保时捷", "B", "bsj.jpg", ValidityState.Enabled, CarBrandType.Hot, CarBrandCountry.Germany);
var Bentley = new CarsBrandSeries("宾利", "B", "bl.jpg", ValidityState.Enabled, CarBrandType.Hot, CarBrandCountry.England);
var Toyota = new CarsBrandSeries("丰田", "F", "ft.jpg", ValidityState.Enabled, CarBrandType.Hot, CarBrandCountry.Japanese);
var Volkswagen = new CarsBrandSeries("大众", "D", "dz.jpg", ValidityState.Enabled, CarBrandType.Hot, CarBrandCountry.Germany);
var Infiniti = new CarsBrandSeries("英菲尼迪", "Y", "yfnd.jpg", ValidityState.Enabled, CarBrandType.Hot, CarBrandCountry.Japanese);
var Lexus = new CarsBrandSeries("雷克萨斯", "L", "lkss.jpg", ValidityState.Enabled, CarBrandType.Hot, CarBrandCountry.Japanese);
var cbs = new List <CarsBrandSeries>()
{
Audi, BMW, Benz, Porsche, Bentley, Toyota, Volkswagen, Infiniti, Lexus
};
var BrandSeries = new List <CarsBrandSeries>()
{
new CarsBrandSeries(Audi.Id, "A1(进口)", ValidityState.Enabled, CarSeriesGrade.Small),
new CarsBrandSeries(Audi.Id, "A3", ValidityState.Enabled, CarSeriesGrade.Compact),
new CarsBrandSeries(Audi.Id, "A4L", ValidityState.Enabled, CarSeriesGrade.Middle),
new CarsBrandSeries(Audi.Id, "A6L", ValidityState.Enabled, CarSeriesGrade.MiddleLarge),
new CarsBrandSeries(Audi.Id, "A8(进口)", ValidityState.Enabled, CarSeriesGrade.Large),
new CarsBrandSeries(Audi.Id, "Q5", ValidityState.Enabled, CarSeriesGrade.SUV),
new CarsBrandSeries(BMW.Id, "i3(进口)", ValidityState.Enabled, CarSeriesGrade.Small),
new CarsBrandSeries(BMW.Id, "1系", ValidityState.Enabled, CarSeriesGrade.Compact),
new CarsBrandSeries(BMW.Id, "3系", ValidityState.Enabled, CarSeriesGrade.Middle),
new CarsBrandSeries(BMW.Id, "5系", ValidityState.Enabled, CarSeriesGrade.MiddleLarge),
new CarsBrandSeries(BMW.Id, "5系(进口)", ValidityState.Enabled, CarSeriesGrade.MiddleLarge),
new CarsBrandSeries(BMW.Id, "7系(进口)", ValidityState.Enabled, CarSeriesGrade.Large),
new CarsBrandSeries(BMW.Id, "2系(进口)", ValidityState.Enabled, CarSeriesGrade.MPV),
new CarsBrandSeries(Benz.Id, "SLC级(进口)", ValidityState.Enabled, CarSeriesGrade.SportsCar),
new CarsBrandSeries(Benz.Id, "V级", ValidityState.Enabled, CarSeriesGrade.MPV),
new CarsBrandSeries(Benz.Id, "GLA", ValidityState.Enabled, CarSeriesGrade.SUV),
new CarsBrandSeries(Benz.Id, "GLC", ValidityState.Enabled, CarSeriesGrade.SUV),
new CarsBrandSeries(Benz.Id, "E级", ValidityState.Enabled, CarSeriesGrade.MiddleLarge),
new CarsBrandSeries(Benz.Id, "E级(进口)", ValidityState.Enabled, CarSeriesGrade.MiddleLarge),
new CarsBrandSeries(Benz.Id, "S级(进口)", ValidityState.Enabled, CarSeriesGrade.Large),
new CarsBrandSeries(Benz.Id, "R级(进口)", ValidityState.Enabled, CarSeriesGrade.MPV),
};
db.InsertAll <CarsBrandSeries>(cbs);
db.InsertAll <CarsBrandSeries>(BrandSeries);
return(cbs.Count + BrandSeries.Count);
}
}
return(0);
}
/// <summary>
/// 获取用户信息包含公司信息
/// </summary>
/// <param name="openid"></param>
/// <returns></returns>
public ResAccount GetAccountInfo(string openid, ValidityState state)
{
using (var dbs = WxDbFactory.GetDbContext("hch_dbs").MyDB)
{
ResAccount ret = dbs.Single <ResAccount>("SELECT acc.*,c.`Name`as CorporationName FROM ( SELECT * FROM Account WHERE OpenId=@openid and State=@state) AS acc INNER JOIN Corporation as c ON c.Id= acc.corporation where c.State=@state ", new { openid = openid, state = state });
//var q = dbs.From<Account>().LeftJoin<Corporation>((a, c) => a.Corporation == c.Id).Where<Account>(a => a.Id == openid).Select<Account, Corporation>((x, c) => new { x,CorporationName=c.Name });
//var ret = dbs.Single<dynamic>(q);
//foreach (dynamic item in accret)
//{
// string id = item.Id;
//}
return(ret);
}
}
/// <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);
}
}
/// <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);
}
}