public ActionResult SelectBaoYangActivityVehicle(BaoYangActivityVehicleSearchModel model, int pageIndex = 1, int pageSize = 20)
{
if (model == null)
{
return(Json(new { Status = false, Msg = "未知的查询对象" }, JsonRequestBehavior.AllowGet));
}
if (model.BrandCategories == null || !model.BrandCategories.Any() || model.Brands == null || !model.Brands.Any())
{
return(Json(new { Status = false, Msg = "请选择车型信息" }, JsonRequestBehavior.AllowGet));
}
if (string.IsNullOrWhiteSpace(model.OilViscosity))
{
return(Json(new { Status = false, Msg = "请选择机油等级" }, JsonRequestBehavior.AllowGet));
}
if (model.MaxPrice < model.MinPrice)
{
return(Json(new { Status = false, Msg = "最高价不得小于最低价" }, JsonRequestBehavior.AllowGet));
}
var manager = new BaoYangActivitySettingManager();
if (model.OilLevels != null && model.OilLevels.Any())
{
var oilLevels = manager.GetAllOilLevel();
if (oilLevels == null || !oilLevels.Any())
{
return(Json(new { Status = false, Msg = "无法获取机油等级信息" }, JsonRequestBehavior.AllowGet));
}
model.OilLevels = oilLevels.Where(s => model.OilLevels.Contains(s.OilType)).Select(v => v.OilLevel).Distinct().ToList();
}
var result = manager.SelectBaoYangActivityVehicle(model, pageIndex, pageSize);
var totalPage = (result.Item2 % pageSize == 0) ? ((int)result.Item2 / pageSize) : ((int)result.Item2 / pageSize + 1);
return(Json(new { Status = result.Item1 != null, Data = result.Item1, TotalCount = result.Item2, TotalPage = totalPage },
JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 查询保养活动车型配置
/// </summary>
/// <param name="model"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public Tuple <List <BaoYangActivityVehicleViewModel>, int> SelectBaoYangActivityVehicle(BaoYangActivityVehicleSearchModel model, int pageIndex, int pageSize)
{
var result = null as Tuple <List <BaoYangActivityVehicleViewModel>, int>;
try
{
result = dbScopeManagerGungnirRead.Execute(conn => DalBaoYangActivitySetting.SelectBaoYangActivityVehicle(conn, model, pageIndex, pageSize));
var oilLevels = GetAllOilLevel();
if (oilLevels != null && oilLevels.Any() && result != null && result.Item1 != null && result.Item1.Any())
{
result.Item1.ForEach(s => s.OilLevel = (string.Join(",", oilLevels.Where(o => s.OilLevel.Split(',').ToList().Contains(o.OilLevel)).Select(r => r.OilType).Distinct().ToList())));
}
}
catch (Exception ex)
{
Logger.Error("SelectBaoYangActivityVehicle", ex);
}
return(result ?? Tuple.Create(null as List <BaoYangActivityVehicleViewModel>, 0));
}
/// <summary>
/// 查询保养活动车型配置
/// </summary>
/// <param name="conn"></param>
/// <param name="model"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public static Tuple <List <BaoYangActivityVehicleViewModel>, int> SelectBaoYangActivityVehicle(SqlConnection conn,
BaoYangActivityVehicleSearchModel model, int pageIndex, int pageSize)
{
var result = null as List <BaoYangActivityVehicleViewModel>;
int totalCount = 0;
#region sql
var sql = @"SELECT @Total = COUNT(DISTINCT gt.VehicleID)
FROM Gungnir..tbl_Vehicle_Type_Timing AS gt WITH ( NOLOCK )
INNER JOIN BaoYang..tbl_PartAccessory AS bt ON gt.TID = bt.TID
WHERE gt.TID IN (
SELECT pa.TID
FROM BaoYang..tbl_PartAccessory AS pa WITH ( NOLOCK )
WHERE pa.TID IN (
SELECT tt.TID
FROM Gungnir..tbl_Vehicle_Type_Timing AS tt WITH ( NOLOCK )
WHERE tt.VehicleID IN (
SELECT vt.ProductID
FROM Gungnir..tbl_Vehicle_Type AS vt WITH ( NOLOCK )
WHERE ( @BrandCategory = N'All'
OR vt.BrandCategory IN (
SELECT *
FROM Gungnir..SplitString(@BrandCategory,
',', 1) )
)
AND ( vt.AvgPrice BETWEEN @MinPrice AND @MaxPrice )
AND ( @Brands = N'All'
OR vt.Brand IN (
SELECT *
FROM Gungnir..SplitString(@Brands,
',', 1) )
) ) )
AND AccessoryName = N'发动机油'
AND ( @OilLevel = N''
OR pa.Level COLLATE Chinese_PRC_CI_AS IN (
SELECT *
FROM Gungnir..SplitString(@OilLevel, ',', 1) )
)
AND ( @OilViscosity = N'All'
OR pa.Viscosity COLLATE Chinese_PRC_CI_AS = @OilViscosity
) );
SELECT DISTINCT
gt.VehicleID AS VehicleId ,
( SELECT TOP ( 1 )
Brand
FROM Gungnir..tbl_Vehicle_Type WITH ( NOLOCK )
WHERE ProductID = gt.VehicleID
) AS Brand ,
( SELECT TOP ( 1 )
Vehicle
FROM Gungnir..tbl_Vehicle_Type WITH ( NOLOCK )
WHERE ProductID = gt.VehicleID
) AS VehicleSeries ,
( SELECT gp.AvgPrice
FROM Gungnir..tbl_Vehicle_Type AS gp ( NOLOCK )
WHERE gp.ProductID = gt.VehicleID
) AS AvgPrice ,
STUFF(( SELECT DISTINCT
',' + Viscosity
FROM BaoYang..tbl_PartAccessory WITH ( NOLOCK )
WHERE TID IN (
SELECT DISTINCT
TID
FROM Gungnir..tbl_Vehicle_Type_Timing WITH ( NOLOCK )
WHERE VehicleID = gt.VehicleID )
AND Viscosity IS NOT NULL
AND Viscosity <> N''
FOR
XML PATH('')
), 1, 1, '') AS OilViscosity ,
STUFF(( SELECT DISTINCT
',' + Level
FROM BaoYang..tbl_PartAccessory WITH ( NOLOCK )
WHERE TID IN (
SELECT DISTINCT
TID
FROM Gungnir..tbl_Vehicle_Type_Timing WITH ( NOLOCK )
WHERE VehicleID = gt.VehicleID )
AND Level IS NOT NULL
AND Level <> N''
AND AccessoryName = N'发动机油'
FOR
XML PATH('')
), 1, 1, '') AS OilLevel ,
t3.ActivityId ,
t3.ActivityName
FROM Gungnir..tbl_Vehicle_Type_Timing AS gt WITH ( NOLOCK )
INNER JOIN BaoYang..tbl_PartAccessory AS bt ON gt.TID = bt.TID
LEFT JOIN ( SELECT t1.VehicleId ,
t1.ActivityId ,
t2.ActivityName
FROM BaoYang..BaoYangActivityVehicleAndRegion AS t1
WITH ( NOLOCK )
INNER JOIN BaoYang..BaoYangActivitySetting AS t2 ON t1.ActivityId = t2.ActivityNum
WHERE t1.Type = N'Vehicle'
) AS t3 ON gt.VehicleID = t3.VehicleID COLLATE Chinese_PRC_CI_AS
WHERE gt.TID IN (
SELECT pa.TID
FROM BaoYang..tbl_PartAccessory AS pa WITH ( NOLOCK )
WHERE pa.TID IN (
SELECT tt.TID
FROM Gungnir..tbl_Vehicle_Type_Timing AS tt WITH ( NOLOCK )
WHERE tt.VehicleID IN (
SELECT vt.ProductID
FROM Gungnir..tbl_Vehicle_Type AS vt WITH ( NOLOCK )
WHERE ( @BrandCategory = N'All'
OR vt.BrandCategory IN (
SELECT *
FROM Gungnir..SplitString(@BrandCategory,
',', 1) )
)
AND ( vt.AvgPrice BETWEEN @MinPrice AND @MaxPrice )
AND ( @Brands = N'All'
OR vt.Brand IN (
SELECT *
FROM Gungnir..SplitString(@Brands,
',', 1) )
) ) )
AND AccessoryName = N'发动机油'
AND ( @OilLevel = N''
OR pa.Level COLLATE Chinese_PRC_CI_AS IN (
SELECT *
FROM Gungnir..SplitString(@OilLevel, ',', 1) )
)
AND ( @OilViscosity = N'All'
OR pa.Viscosity COLLATE Chinese_PRC_CI_AS = @OilViscosity
) )
ORDER BY Brand ,
VehicleSeries
OFFSET ( @PageIndex - 1 ) * @PageSize ROWS FETCH NEXT @PageSize ROWS
ONLY;";
#endregion
var parameters = new SqlParameter[] {
new SqlParameter("@PageIndex", pageIndex),
new SqlParameter("@PageSize", pageSize),
new SqlParameter("@BrandCategory", string.Join(",", model.BrandCategories)),
new SqlParameter("@Brands", string.Join(",", model.Brands)),
new SqlParameter("@OilLevel", string.Join(",", (model.OilLevels ?? new List <string>()))),
new SqlParameter("@OilViscosity", model.OilViscosity),
new SqlParameter("@MinPrice", model.MinPrice),
new SqlParameter("@MaxPrice", model.MaxPrice),
new SqlParameter("@Total", SqlDbType.Int)
{
Direction = ParameterDirection.Output
}
};
var dt = SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql, parameters);
result = dt.ConvertTo <BaoYangActivityVehicleViewModel>().ToList();
totalCount = Convert.ToInt32(parameters.Last().Value);
return(Tuple.Create(result, totalCount));
}
