/// <summary> /// 加在强制推荐数据 /// </summary> /// <param name="model">条件</param> /// <param name="pager">分页</param> /// <returns></returns> public static IEnumerable <QZTJModel> SelectQZTJTires(QZTJSelectModel model, PagerModel pager) { var dt = DALRecommendTire.SelectQZTJTires(model, pager); if (dt == null || dt.Rows.Count == 0) { return(new QZTJModel[0]); } var list = dt.Rows.Cast <DataRow>().GroupBy(row => row["PID"].ToString()).Select(T => new QZTJModel() { PID = T.Key, DisplayName = T.FirstOrDefault()["DisplayName"].ToString(), TireSize = T.FirstOrDefault()["TireSize"].ToString(), Products = T.Select(C => new RecommendProductModel() { PID = C["RecommendPID"].ToString(), ProductName = C["ProductName"].ToString(), Image = C["Image"].ToString(), Reason = C["Reason"].ToString(), Postion = Convert.ToInt32(C["Postion"] == DBNull.Value ? 0 : C["Postion"]) }) }); return(list); }
public static DataTable SelectQZTJTires(QZTJSelectModel model, PagerModel pager) { using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir"))) { pager.TotalItem = FetchQZTJCount(model); return(dbHelper.ExecuteDataTable(@"SELECT T.PID, T.DisplayName, T.TireSize FROM ( SELECT VP.PID, VP.DisplayName, VP.TireSize FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) WHERE VP.PID LIKE 'TR-%' AND ( VP.TireSize = @TireSize OR @TireSize IS NULL ) AND ( VP.PID = @PID OR @PID IS NULL ) AND ( VP.PID IN ( SELECT DISTINCT TRR2.PID FROM Tuhu_productcatalog.dbo.tbl_TireEnforceRecommend AS TRR2 WITH ( NOLOCK ) WHERE TRR2.RecommendPID = @RecommendPID ) OR @RecommendPID IS NULL ) AND ( @Brands IS NULL OR VP.CP_Brand COLLATE Chinese_PRC_CI_AS IN ( SELECT * FROM Gungnir.dbo.Split(@Brands, ',') AS S ) ) AND ( @ShowType = 1 OR @ShowType = 2 AND VP.PID IN ( SELECT DISTINCT TRR2.PID FROM Tuhu_productcatalog.dbo.tbl_TireEnforceRecommend AS TRR2 WITH ( NOLOCK ) ) OR @ShowType = 3 AND VP.PID NOT IN ( SELECT DISTINCT TRR2.PID FROM Tuhu_productcatalog.dbo.tbl_TireEnforceRecommend AS TRR2 WITH ( NOLOCK ) ) OR @ShowType = 4 AND VP.PID IN ( SELECT TT.PID FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY TRR.PID, TRR.RecommendPID ORDER BY TRR.PKID ) AS RowNumber, * FROM Tuhu_productcatalog..tbl_TireEnforceRecommend AS TRR ) AS TT WHERE TT.RowNumber > 1 ) ) ORDER BY VP.oid OFFSET ( @PageIndex - 1 ) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY ) AS T" , CommandType.Text, new SqlParameter[] { new SqlParameter("@PageIndex", pager.CurrentPage), new SqlParameter("@PageSize", pager.PageSize), new SqlParameter("@TireSize", string.IsNullOrWhiteSpace(model.TireSize)?null:model.TireSize), new SqlParameter("@PID", string.IsNullOrWhiteSpace(model.PID)?null:model.PID), new SqlParameter("@RecommendPID", string.IsNullOrWhiteSpace(model.RecommendPID)?null:model.RecommendPID), new SqlParameter("@Brands", string.IsNullOrWhiteSpace(model.Brands) || model.Brands.Contains("不限")?null:model.Brands), new SqlParameter("@ShowType", model.ShowType) })); } }
public static int FetchQZTJCount(QZTJSelectModel model) { using (var dbHelper = new SqlDbHelper(ConnectionHelper.GetDecryptConn("Gungnir"))) { var obj = dbHelper.ExecuteScalar(@"SELECT COUNT(1) FROM ( SELECT VP.PID FROM Tuhu_productcatalog.dbo.vw_Products AS VP WITH ( NOLOCK ) WHERE VP.PID LIKE 'TR-%' AND ( VP.TireSize = @TireSize OR @TireSize IS NULL ) AND ( VP.PID = @PID OR @PID IS NULL ) AND ( VP.PID IN ( SELECT DISTINCT TRR2.PID FROM Tuhu_productcatalog.dbo.tbl_TireEnforceRecommend AS TRR2 WITH ( NOLOCK ) WHERE TRR2.RecommendPID = @RecommendPID ) OR @RecommendPID IS NULL ) AND ( @Brands IS NULL OR VP.CP_Brand COLLATE Chinese_PRC_CI_AS IN ( SELECT * FROM Gungnir.dbo.Split(@Brands, ',') AS S ) ) AND ( @ShowType = 1 OR @ShowType = 2 AND VP.PID IN ( SELECT DISTINCT TRR2.PID FROM Tuhu_productcatalog.dbo.tbl_TireEnforceRecommend AS TRR2 WITH ( NOLOCK ) ) OR @ShowType = 3 AND VP.PID NOT IN ( SELECT DISTINCT TRR2.PID FROM Tuhu_productcatalog.dbo.tbl_TireEnforceRecommend AS TRR2 WITH ( NOLOCK ) ) OR @ShowType = 4 AND VP.PID IN ( SELECT TT.PID FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY TRR.PID, TRR.RecommendPID ORDER BY TRR.PKID ) AS RowNumber, * FROM Tuhu_productcatalog..tbl_TireEnforceRecommend AS TRR ) AS TT WHERE TT.RowNumber > 1 ) ) ) AS T" , CommandType.Text, new SqlParameter[] { new SqlParameter("@TireSize", string.IsNullOrWhiteSpace(model.TireSize)?null:model.TireSize), new SqlParameter("@PID", string.IsNullOrWhiteSpace(model.PID)?null:model.PID), new SqlParameter("@RecommendPID", string.IsNullOrWhiteSpace(model.RecommendPID)?null:model.RecommendPID), new SqlParameter("@Brands", string.IsNullOrWhiteSpace(model.Brands) || model.Brands.Contains("不限")?null:model.Brands), new SqlParameter("@ShowType", model.ShowType) }); return(Convert.ToInt32(obj)); } }
/// <summary> /// 加在强制推荐数据 /// </summary> /// <param name="model">条件</param> /// <param name="pager">分页</param> /// <returns></returns> public static IEnumerable <QZTJModel> SelectQZTJTires(QZTJSelectModel model, PagerModel pager) { var dt = DALRecommendTire.SelectQZTJTires(model, pager); if (dt == null || dt.Rows.Count == 0) { return(new QZTJModel[0]); } List <string> pids = dt.Rows.Cast <DataRow>().Select(row => row["PID"].ToString()).Distinct().ToList(); var recommendResult = GetTireEnforceRecommendByPids(pids); var list = dt.Rows.Cast <DataRow>().GroupBy(row => row["PID"].ToString()).Select(T => new QZTJModel() { PID = T.Key, DisplayName = T.FirstOrDefault()["DisplayName"].ToString(), TireSize = T.FirstOrDefault()["TireSize"].ToString(), Products = recommendResult.Where(item => item.PID == T.Key) }); return(list); }