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