public override DataTable GetSubjectList(SubjectListQuery query)
{
StringBuilder builder = new StringBuilder();
if (HiContext.Current.User.UserRole == UserRole.Member)
{
Member user = HiContext.Current.User as Member;
int memberDiscount = MemberProvider.Instance().GetMemberDiscount(user.GradeId);
builder.AppendFormat("SELECT TOP {0} ProductId,ProductName,ProductCode,SaleCounts,ShortDescription,ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,MarketPrice,SalePrice,", query.MaxNum);
builder.AppendFormat(" CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) = 1 ", user.GradeId);
builder.AppendFormat("THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END AS RankPrice", user.GradeId, memberDiscount);
}
else
{
builder.AppendFormat("SELECT TOP {0} ProductId,ProductName,ProductCode,SaleCounts,ShortDescription,ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,MarketPrice,SalePrice,SalePrice AS RankPrice", query.MaxNum);
}
builder.Append(" FROM vw_Hishop_BrowseProductList p WHERE ");
builder.Append(ProductMasterProvider.BuildProductSubjectQuerySearch(query));
if (!string.IsNullOrEmpty(query.SortBy))
{
builder.AppendFormat(" ORDER BY {0} {1}", DataHelper.CleanSearchString(query.SortBy), DataHelper.CleanSearchString(query.SortOrder.ToString()));
}
DbCommand sqlStringCommand = this.database.GetSqlStringCommand(builder.ToString());
using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
{
return(DataHelper.ConverDataReaderToDataTable(reader));
}
}
public override DbQueryResult GetBrowseProductList(ProductBrowseQuery query)
{
string filter = ProductMasterProvider.BuildProductBrowseQuerySearch(query);
string selectFields = "ProductId,ProductName,ProductCode, SaleCounts, ShortDescription, ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160, ThumbnailUrl180,ThumbnailUrl220,MarketPrice, SalePrice,Stock";
if (HiContext.Current.User.UserRole == UserRole.Member)
{
Member user = HiContext.Current.User as Member;
int memberDiscount = MemberProvider.Instance().GetMemberDiscount(user.GradeId);
selectFields = selectFields + string.Format(",CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) = 1 ", user.GradeId) + string.Format("THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END AS RankPrice", user.GradeId, memberDiscount);
}
else
{
selectFields = selectFields + ",SalePrice as RankPrice";
}
return(DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "vw_Hishop_BrowseProductList p", "ProductId", filter, selectFields));
}
