/// <summary> /// 根据年份查询 /// </summary> /// <param name="conn"></param> /// <param name="model"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <VehicleArticleViewModel> SelectVehicleArticleModelForNiann (SqlConnection conn, VehicleSearchModel model) { var sql = @"SELECT c.PKID , c.VehicleId , c.PaiLiang , c.Nian , c.ArticleUrl , c.CreateDateTime , c.LastUpdateDateTime FROM Configuration..VehicleArticle AS c WITH ( NOLOCK ) WHERE c.VehicleId = @VehicleId AND c.PaiLiang = @PaiLiang AND ( @Nian IS NULL OR @Nian = N'' OR c.Nian = @Nian ) ORDER BY c.PKID DESC;"; var parameters = new[] { new SqlParameter("@VehicleId", model.VehicleId), new SqlParameter("@PaiLiang", model.PaiLiang), new SqlParameter("@Nian", model.Nian) }; var result = SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql, parameters).ConvertTo <VehicleArticleViewModel>().ToList(); return(result); }
/// <summary> /// 根据等级获取选车攻略配置 /// </summary> /// <param name="model"></param> /// <param name="vehicleLevel"></param> /// <returns></returns> public Tuple <List <VehicleArticleViewModel>, int> SelectVehicleArticleModelByLevel (VehicleSearchModel model, int vehicleLevel) { var result = null as List <VehicleArticleViewModel>; var totalCount = 0; try { switch (vehicleLevel) { case 2: result = dbScopeManagerConfigurationRead.Execute(conn => DalVehicleArticle.SelectVehicleArticleModelForVehicleId(conn, model, out totalCount)); break; case 3: result = dbScopeManagerConfigurationRead.Execute(conn => DalVehicleArticle.SelectVehicleArticleModelForPaiLiang(conn, model, out totalCount)); break; case 4: var vehicleConfigs = dbScopeManagerConfigurationRead.Execute(conn => DalVehicleArticle.SelectVehiclesForNian(conn, model)); var nianConfigs = dbScopeManagerConfigurationRead.Execute(conn => DalVehicleArticle.SelectVehicleArticleModelForNiann(conn, model)); result = new List <VehicleArticleViewModel>(); if (!string.IsNullOrEmpty(model.Nian)) { vehicleConfigs = vehicleConfigs.Where(s => string.Equals(s.Nian, model.Nian)).ToList(); } foreach (var vehicleConfig in vehicleConfigs) { var nianConfig = nianConfigs.FirstOrDefault(s => string.Equals(s.Nian, vehicleConfig.Nian)); if (nianConfig != null || !model.IsOnlyConfiged) { result.Add(new VehicleArticleViewModel() { PKID = nianConfig?.PKID ?? 0, Brand = vehicleConfig.Brand, VehicleId = vehicleConfig.VehicleId, VehicleSeries = vehicleConfig.VehicleSeries, PaiLiang = vehicleConfig.PaiLiang, Nian = vehicleConfig.Nian, ArticleUrl = nianConfig?.ArticleUrl, CreateDateTime = nianConfig?.CreateDateTime, LastUpdateDateTime = nianConfig?.LastUpdateDateTime }); } } break; default: break; } } catch (Exception ex) { result = null; Logger.Error("SelectVehicleArticleModelForVehicleId", ex); } return(Tuple.Create(result, totalCount)); }
/// <summary> /// 根据年款查询(不分页) /// </summary> /// <param name="conn"></param> /// <param name="model"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <VehicleArticleViewModel> SelectVehiclesForNian (SqlConnection conn, VehicleSearchModel model) { var sql = @"SELECT DISTINCT g.VehicleID , t.Brand , g.VehicleSeries , g.PaiLiang , g.ListedYear , ISNULL(g.StopProductionYear, YEAR(GETDATE())) AS StopProductionYear FROM Gungnir..tbl_Vehicle_Type_Timing AS g WITH ( NOLOCK ) INNER JOIN Gungnir..tbl_Vehicle_Type AS t WITH (NOLOCK) ON t.ProductID=g.VehicleID WHERE g.VehicleID = @VehicleId AND g.PaiLiang = @PaiLiang AND ( @Nian IS NULL OR @Nian = N'' OR ( @Nian BETWEEN g.ListedYear AND ISNULL(g.StopProductionYear, YEAR(GETDATE())) ) ) ORDER BY t.Brand;"; var parameters = new[] { new SqlParameter("@VehicleId", model.VehicleId), new SqlParameter("@PaiLiang", model.PaiLiang), new SqlParameter("@Nian", model.Nian) }; var nianConfigs = SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql, parameters).ConvertTo <VehicleTypeInfoDb>().ToList(); var result = new List <VehicleArticleViewModel>(); foreach (var nianConfig in nianConfigs) { int start; int end; if (int.TryParse(nianConfig.ListedYear, out start) && int.TryParse(nianConfig.StopProductionYear, out end)) { for (var i = start; i <= end; i++) { if (!result.Any(s => s.Nian == i.ToString())) { result.Add(new VehicleArticleViewModel() { Brand = nianConfig.Brand, VehicleSeries = nianConfig.VehicleSeries, VehicleId = nianConfig.VehicleID, PaiLiang = nianConfig.PaiLiang, Nian = i.ToString() }); } } } } return(result); }
public async Task <IActionResult> Search(string name, DateTime?beforeDate, DateTime?fromDate, int?modelId, int?brandId) { userDTO = new UserDTO { Name = User.Identity.Name }; VehicleSearchModel vehicleSearchModel = new VehicleSearchModel { Name = name, BeforeDate = beforeDate, BrandId = brandId, FromDate = fromDate, ModelId = modelId }; searchVehicles = await service.GetVehicleDTOs(vehicleSearchModel, userDTO); return(RedirectToAction("Index")); }
/// <summary> /// 根据等级查询配置信息 /// </summary> /// <param name="request"></param> /// <param name="vehicleLevel"></param> /// <returns></returns> public ActionResult SelectVehicleArticleModel(VehicleSearchModel request, int vehicleLevel) { var manager = new VehicleArticleManager(User.Identity.Name); if (request == null) { return(Json(new { Status = false, Msg = "未知的查询对象" }, JsonRequestBehavior.AllowGet)); } request.PageIndex = request.PageIndex < 1 ? 1 : request.PageIndex; request.PageSize = request.PageSize < 1 ? 20 : request.PageSize; var result = manager.SelectVehicleArticleModelByLevel(request, vehicleLevel); return(Json(new { Status = result.Item1 != null, Msg = "", Data = result.Item1, TotalCount = result.Item2 } , JsonRequestBehavior.AllowGet)); }
public async Task <ActionResult> SearchIndex(VehicleSearchModel searchModel) { var result = db.Vehicles.AsQueryable(); if (searchModel != null) { if (!string.IsNullOrEmpty(searchModel.Make)) { result = result.Where(t => t.VehicleType.Make.Equals(searchModel.Make)); } if (!string.IsNullOrEmpty(searchModel.Model)) { result = result.Where(t => t.VehicleType.Model.Equals(searchModel.Model)); } if (searchModel.Price.HasValue) { result = result.Where(t => t.Price <= searchModel.Price); } if (searchModel.MinManufacturerYear.HasValue) { result = result.Where(t => t.ManufacturerYear >= searchModel.MinManufacturerYear); } if (searchModel.MaxManufacturerYear.HasValue) { result = result.Where(t => t.ManufacturerYear <= searchModel.MaxManufacturerYear); } if (searchModel.CountySoldFrom.HasValue) { result = result.Where(t => t.CountySoldFrom == searchModel.CountySoldFrom); } } var vehicles = await result.ToListAsync(); var model = await vehicles.Convert(db); //return View(model); return(View(model.OrderBy(t => t.Price))); }
public async Task <IEnumerable <VehicleDTO> > GetVehicleDTOs(VehicleSearchModel searchModel, UserDTO user) { var result = await database.VehicleRepository.GetAllWithBrandAndModel(); if (user.Name == null) { return(null); } result = result.AsQueryable(); result = result.Where(x => x.CreatorId == database.EmployeeRepository.GetByName(user.Name)); if (searchModel != null) { if (!string.IsNullOrEmpty(searchModel.Name)) { //if(int.TryParse(searchModel.Name)) result = result.Where(x => x.Name.StartsWith(searchModel.Name)); } if (searchModel.ModelId.HasValue) { result = result.Where(x => x.ModelId == searchModel.ModelId); } if (searchModel.BrandId.HasValue) { result = result.Where(x => x.BrandId == searchModel.BrandId); } if (searchModel.BeforeDate != null) { result = result.Where(x => x.CreateDate < searchModel.BeforeDate); } if (searchModel.FromDate != null) { result = result.Where(x => x.CreateDate > searchModel.FromDate); } } return(mapper.Map <IEnumerable <VehicleDTO> >(result)); }
public async Task Get([FromQuery] VehicleSearchModel model) { Response.ContentType = "application/json"; await Service.SearchJsonAsync(Response.Body, model.Domain, model); }
/// <summary> /// 根据排量查询 /// </summary> /// <param name="conn"></param> /// <param name="model"></param> /// <param name="totalCount"></param> /// <returns></returns> public static List <VehicleArticleViewModel> SelectVehicleArticleModelForPaiLiang (SqlConnection conn, VehicleSearchModel model, out int totalCount) { var sql = @"SELECT @Total = COUNT(1) FROM ( SELECT DISTINCT c.PKID , g.VehicleID , t.Brand , g.VehicleSeries , g.PaiLiang , c.ArticleUrl , c.CreateDateTime , c.LastUpdateDateTime FROM ( SELECT * FROM Configuration..VehicleArticle AS s WITH ( NOLOCK ) WHERE s.PaiLiang IS NOT NULL AND s.PaiLiang <> N'' AND ( s.Nian IS NULL OR s.Nian = N'' ) ) AS c RIGHT JOIN Gungnir..tbl_Vehicle_Type_Timing AS g WITH ( NOLOCK ) ON c.VehicleId = g.VehicleID COLLATE Chinese_PRC_CI_AS AND c.PaiLiang = g.PaiLiang COLLATE Chinese_PRC_CI_AS INNER JOIN Gungnir..tbl_Vehicle_Type AS t WITH ( NOLOCK ) ON t.ProductID = g.VehicleID WHERE ( @Brand IS NULL OR @Brand = N'' OR t.Brand = @Brand ) AND ( @VehicleId IS NULL OR @VehicleId = N'' OR g.VehicleID = @VehicleId ) AND ( @PaiLiang IS NULL OR @PaiLiang = N'' OR g.PaiLiang = @PaiLiang ) AND ( @IsOnlyConfiged = 0 OR c.PKID > 0 ) ) AS result; SELECT DISTINCT c.PKID , g.VehicleID , t.Brand , g.VehicleSeries , g.PaiLiang , c.ArticleUrl , c.CreateDateTime , c.LastUpdateDateTime FROM ( SELECT s.* FROM Configuration..VehicleArticle AS s WITH ( NOLOCK ) WHERE s.PaiLiang IS NOT NULL AND s.PaiLiang <> N'' AND ( s.Nian IS NULL OR s.Nian = N'' ) ) AS c RIGHT JOIN Gungnir..tbl_Vehicle_Type_Timing AS g WITH ( NOLOCK ) ON c.VehicleId = g.VehicleID COLLATE Chinese_PRC_CI_AS AND c.PaiLiang = g.PaiLiang COLLATE Chinese_PRC_CI_AS INNER JOIN Gungnir..tbl_Vehicle_Type AS t WITH ( NOLOCK ) ON t.ProductID = g.VehicleID WHERE ( @Brand IS NULL OR @Brand = N'' OR t.Brand = @Brand ) AND ( @VehicleId IS NULL OR @VehicleId = N'' OR g.VehicleID = @VehicleId ) AND ( @PaiLiang IS NULL OR @PaiLiang = N'' OR g.PaiLiang = @PaiLiang ) AND ( @IsOnlyConfiged = 0 OR c.PKID > 0 ) ORDER BY c.PKID DESC , t.Brand OFFSET ( @PageIndex - 1 ) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;"; var parameters = new[] { new SqlParameter("@Brand", model.Brand), new SqlParameter("@VehicleId", model.VehicleId), new SqlParameter("@PaiLiang", model.PaiLiang), new SqlParameter("@IsOnlyConfiged", model.IsOnlyConfiged), new SqlParameter("@PageIndex", model.PageIndex), new SqlParameter("@PageSize", model.PageSize), new SqlParameter("@Total", SqlDbType.Int) { Direction = ParameterDirection.Output } }; var result = SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql, parameters).ConvertTo <VehicleArticleViewModel>().ToList(); totalCount = Convert.ToInt32(parameters.LastOrDefault().Value?.ToString()); return(result); }