public override DataTable GetSubjectList(SubjectListQuery query)
{
StringBuilder builder = new StringBuilder();
int num = HiContext.Current.SiteSettings.UserId.Value;
if (HiContext.Current.User.UserRole == UserRole.Underling)
{
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", query.MaxNum);
builder.AppendFormat(",(SELECT SalePrice FROM vw_distro_SkuPrices WHERE SkuId = p.SkuId AND DistributoruserId = {0}) AS SalePrice", num);
builder.AppendFormat(",CASE WHEN (SELECT COUNT(*) FROM distro_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0} AND DistributoruserId = {1}) = 1 ", user.GradeId, num);
builder.AppendFormat(" THEN (SELECT MemberSalePrice FROM distro_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0} AND DistributoruserId = {1})", user.GradeId, num);
builder.AppendFormat(" ELSE (SELECT SalePrice FROM vw_distro_SkuPrices WHERE SkuId = p.SkuId AND DistributoruserId = {0})*{1}/100 END AS RankPrice", num, memberDiscount);
}
else
{
builder.AppendFormat("SELECT TOP {0} ProductId,ProductName,ProductCode,SaleCounts,ShortDescription,ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160, ThumbnailUrl180,ThumbnailUrl220, MarketPrice", query.MaxNum);
builder.AppendFormat(" ,(SELECT SalePrice FROM vw_distro_SkuPrices WHERE SkuId = p.SkuId AND DistributoruserId = {0}) AS SalePrice", num);
builder.Append(" , 0 AS RankPrice");
}
builder.Append(" FROM vw_distro_BrowseProductList p WHERE ");
builder.Append(ProductSubsiteProvider.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 GetUnSaleProductList(ProductBrowseQuery query)
{
int num = HiContext.Current.SiteSettings.UserId.Value;
string filter = ProductSubsiteProvider.BuildUnSaleProductBrowseQuerySearch(query) + string.Format(" AND DistributorUserId = {0} ", HiContext.Current.SiteSettings.UserId.Value);
string selectFields = "ProductId,ProductName,ProductCode,ShowSaleCounts AS SaleCounts, ShortDescription,MarketPrice, ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160, ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,Stock";
if (HiContext.Current.User.UserRole == UserRole.Underling)
{
Member user = HiContext.Current.User as Member;
int memberDiscount = MemberProvider.Instance().GetMemberDiscount(user.GradeId);
selectFields = (selectFields + string.Format(",(SELECT SalePrice FROM vw_distro_SkuPrices WHERE SkuId = p.SkuId AND DistributoruserId = {0}) AS SalePrice", num) + string.Format(",CASE WHEN (SELECT COUNT(*) FROM distro_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0} AND DistributoruserId = {1}) = 1 ", user.GradeId, num)) + string.Format(" THEN (SELECT MemberSalePrice FROM distro_SKUMemberPrice WHERE SkuId = p.SkuId AND GradeId = {0} AND DistributoruserId = {1})", user.GradeId, num) + string.Format(" ELSE (SELECT SalePrice FROM vw_distro_SkuPrices WHERE SkuId = p.SkuId AND DistributoruserId = {0})*{1}/100 END AS RankPrice", num, memberDiscount);
}
else
{
selectFields = selectFields + string.Format(",(SELECT SalePrice FROM vw_distro_SkuPrices WHERE SkuId = p.SkuId AND DistributoruserId = {0}) AS SalePrice", num) + string.Format(",(SELECT SalePrice FROM vw_distro_SkuPrices WHERE SkuId = p.SkuId AND DistributoruserId = {0}) AS RankPrice", num);
}
return(DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "vw_distro_BrowseProductList p", "ProductId", filter, selectFields));
}
