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