/// <summary>
/// 获取机油车型推荐排序
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public JsonResult GetVehicleOilProductPriority(OilVehicleProductPriorityRequst request)
{
if (request == null)
{
return(Json(new { Status = false, Msg = "参数验证失败" }, JsonRequestBehavior.AllowGet));
}
request.PageIndex = request.PageIndex > 0 ? request.PageIndex : 1;
request.PageSize = request.PageSize > 0 ? request.PageSize : 100;
var manager = new BaoYangRecommendManager();
var data = manager.GetVehicleOilProductPriorityView(request);
return(Json(new { Status = true, Msg = string.Empty, Data = data.Item2, Total = data.Item1 }, JsonRequestBehavior.AllowGet));
}
public static List <ProductPriorityOilDetail> GetOilProductPriorityAreaDetail(SqlConnection conn, OilVehicleProductPriorityRequst request)
{
#region SQL
const string sql = @"SELECT o.NewViscosity ,
o.AreaId ,
o.VehicleId ,
o.Viscosity ,
o.Grade ,
o.NewViscosity ,
o.IsEnabled ,
o.PKID AS AreaOilId ,
od.Brand ,
od.Series ,
od.Seq ,
od.Grade AS ProductPriorityGrade
FROM ProductPriority_Area_Oil AS o WITH (NOLOCK)
LEFT JOIN dbo.ProductPriority_Area_OilDetail AS od WITH (NOLOCK)
ON o.PKID = od.AreaOilId
WHERE (
(
@Seq = 0
AND (
@ProductPriorityGrade = N''
OR @ProductPriorityGrade IS NULL
)
AND (
@Brand = N''
OR @Brand IS NULL
)
AND (
@Series = N''
OR @Series IS NULL
)
)
OR EXISTS (
SELECT 1
FROM dbo.ProductPriority_Area_OilDetail AS odd WITH (NOLOCK)
WHERE odd.AreaOilId = o.PKID
AND odd.Seq >= @Seq
AND (
@ProductPriorityGrade = N''
OR @ProductPriorityGrade IS NULL
OR (
odd.Grade = @ProductPriorityGrade
AND (
@Seq = 0
OR odd.Seq = @Seq
)
)
)
AND (
@Brand = N''
OR @Brand IS NULL
OR (
odd.Brand = @Brand
AND (
@Seq = 0
OR odd.Seq = @Seq
)
)
)
AND (
@Series = N''
OR @Series IS NULL
OR (
odd.Series = @Series
AND (
@Seq = 0
OR odd.Seq = @Seq
)
)
)
)
)
AND (
@NewViscosity = N''
OR @NewViscosity IS NULL
OR o.NewViscosity = @NewViscosity
)
AND (
@Viscosity = N''
OR @Viscosity IS NULL
OR o.Viscosity = @Viscosity
)
AND (
@Grade = N''
OR @Grade IS NULL
OR o.Grade = @Grade
)
AND o.IsDeleted = 0
AND o.AreaId = @AreaId
AND (
@VehicleId = N''
OR @VehicleId IS NULL
OR o.VehicleId = @VehicleId
)
;";
#endregion
SqlParameter[] parameter = new SqlParameter[] {
new SqlParameter("@AreaId", request.AreaId),
new SqlParameter("@Grade", request.Grade),
new SqlParameter("@NewViscosity", request.NewViscosity),
new SqlParameter("@Brand", request.ProductBrand),
new SqlParameter("@ProductPriorityGrade", request.ProductPriorityGrade),
new SqlParameter("@Series", request.ProductSeries),
new SqlParameter("@Seq", request.Seq),
new SqlParameter("@Viscosity", request.Viscosity),
new SqlParameter("@VehicleId", request.VehicleId)
};
return(SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql, parameter).ConvertTo <ProductPriorityOilDetail>().ToList());
}
/// <summary>
/// 获取车型机油保养推荐排序
/// </summary>
/// <param name="conn"></param>
/// <param name="request"></param>
/// <returns></returns>
public static List <VehicleOilProductPriorityView> GetVehicleByProductPriorityRequest(SqlConnection conn, OilVehicleProductPriorityRequst request)
{
#region sql
var sql = @"SELECT DISTINCT
v.ProductID AS VehicleId ,
v.Brand ,
v.Vehicle ,
p.Viscosity ,
p.Grade
FROM Gungnir..tbl_Vehicle_Type AS v WITH (NOLOCK)
LEFT JOIN Gungnir..tbl_Vehicle_Type_Timing AS t WITH (NOLOCK)
ON v.ProductID = t.VehicleID
LEFT JOIN BaoYang..tbl_PartAccessory AS p WITH (NOLOCK)
ON p.TID = t.TID COLLATE Chinese_PRC_CI_AS
WHERE p.AccessoryName = N'发动机油'
AND p.IsDeleted = 0
AND (
@Brand = N''
OR @Brand IS NULL
OR v.Brand = @Brand
)
AND (
@VehicleId IS NULL
OR v.ProductID = @VehicleId
OR @VehicleId = N''
)
AND (
@MaxPrice = 0
OR v.AvgPrice <= @MaxPrice
)
AND (
@MinPrice = 0
OR v.AvgPrice > @MinPrice
)
AND (
@VehicleBodyType = N''
OR @VehicleBodyType IS NULL
OR v.VehicleBodyType = @VehicleBodyType
)
AND (
@Viscosity = N''
OR @Viscosity IS NULL
OR p.Viscosity = @Viscosity
)
AND (
@Grade = N''
OR @Grade IS NULL
OR p.Grade = @Grade
);";
#endregion
var parameters = new[] {
new SqlParameter("@Brand", request.Brand),
new SqlParameter("@MaxPrice", request.MaxPrice),
new SqlParameter("@MinPrice", request.MinPrice),
new SqlParameter("@VehicleBodyType", request.VehicleBodyType),
new SqlParameter("@Viscosity", request.Viscosity),
new SqlParameter("@Grade", request.Grade),
new SqlParameter("@VehicleId", request.VehicleId)
};
return(SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql, parameters).ConvertTo <VehicleOilProductPriorityView>().ToList());
}