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