public DataTable GetFavorites(Member member) { //Member member = HiContext.Current.User as Member; StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("SELECT a.*, b.ProductName, b.ThumbnailUrl60, b.MarketPrice,b.ShortDescription,b.TaxRate,"); if (member != null) { int discount = new MemberGradeDao().GetMemberGrade(member.GradeId).Discount; stringBuilder.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Ecshop_SKUMemberPrice WHERE SkuId = b.SkuId AND GradeId = {0}) = 1", member.GradeId); stringBuilder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Ecshop_SKUMemberPrice WHERE SkuId = b.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) AS SalePrice", member.GradeId, discount); } else { stringBuilder.Append("SalePrice"); } stringBuilder.AppendFormat(" FROM Ecshop_Favorite a left join vw_Ecshop_CDisableBrowseProductList b on a.ProductId = b.ProductId WHERE a.UserId={0} ORDER BY a.FavoriteId DESC", member.UserId); DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString()); DataTable result; using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand)) { result = DataHelper.ConverDataReaderToDataTable(dataReader); } return(result); }
public DbQueryResult GetFavorites(ProductFavoriteQuery query) { //Member member = HiContext.Current.User as Member; StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("("); stringBuilder.Append("SELECT a.*, b.ProductName, b.ThumbnailUrl60,b.ThumbnailUrl220,b.MarketPrice,b.ShortDescription,b.TaxRate,"); int discount = new MemberGradeDao().GetMemberGrade(query.GradeId).Discount; stringBuilder.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Ecshop_SKUMemberPrice WHERE SkuId = b.SkuId AND GradeId = {0}) = 1", query.GradeId); stringBuilder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Ecshop_SKUMemberPrice WHERE SkuId = b.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) AS SalePrice", query.GradeId, discount); stringBuilder.AppendFormat(" FROM Ecshop_Favorite a left join vw_Ecshop_CDisableBrowseProductList b on a.ProductId = b.ProductId WHERE a.UserId={0}", query.UserId); stringBuilder.Append(") as FavoriteTable "); return(DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, stringBuilder.ToString(), "FavoriteId", "", "*")); }