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