public static DataTable GetCategories() { DataTable categories = HiCache.Get("DataCache-Categories") as DataTable; if (categories == null) { categories = new CategoryDao().GetCategories(); HiCache.Insert("DataCache-Categories", categories, 360, CacheItemPriority.Normal); } return categories; }
public DataTable GetProducts(MemberInfo member, int? topicId, int? categoryId, int distributorId, string keyWord, int pageNumber, int maxNum, out int toal, string sort, bool isAsc = false) { int discount = 100; StringBuilder builder = new StringBuilder(); builder.AppendFormat("ProductId,ProductName,ProductCode,ShowSaleCounts AS SaleCounts,ShortDescription,", maxNum); builder.Append(" ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,MarketPrice,VistiCounts,"); if (member != null) { discount = new MemberGradeDao().GetMemberGrade(member.GradeId).Discount; builder.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = vw_Hishop_BrowseProductList.SkuId AND GradeId = {0}) = 1", member.GradeId); builder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = vw_Hishop_BrowseProductList.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) AS SalePrice", member.GradeId, discount); } else { builder.Append("SalePrice"); } StringBuilder builder2 = new StringBuilder(); builder2.Append(" SaleStatus=1"); if (topicId.HasValue) { builder2.AppendFormat(" AND ProductId IN (SELECT RelatedProductId FROM Vshop_RelatedTopicProducts WHERE TopicId = {0})", topicId.Value); } if (categoryId.HasValue) { CategoryInfo category = new CategoryDao().GetCategory(categoryId.Value); if (category != null) { builder2.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%') ", category.Path); } } if (!string.IsNullOrEmpty(keyWord)) { builder2.AppendFormat(" AND (ProductName LIKE '%{0}%' OR ProductCode LIKE '%{0}%')", keyWord); } if (distributorId > 0) { builder2.AppendFormat(" AND ProductId IN (SELECT ProductId FROM Hishop_DistributorProducts WHERE UserId={0})", distributorId); } if (string.IsNullOrWhiteSpace(sort)) { sort = "ProductId"; } DbQueryResult result = DataHelper.PagingByRownumber(pageNumber, maxNum, sort, isAsc ? SortAction.Asc : SortAction.Desc, true, "vw_Hishop_BrowseProductList", "ProductId", builder2.ToString(), builder.ToString()); DataTable data = (DataTable) result.Data; toal = result.TotalRecords; return data; }
public static bool DeleteCategory(int categoryId) { ManagerHelper.CheckPrivilege(Privilege.DeleteProductCategory); bool flag = new CategoryDao().DeleteCategory(categoryId); if (flag) { EventLogs.WriteOperationLog(Privilege.DeleteProductCategory, string.Format(CultureInfo.InvariantCulture, "删除了编号为 “{0}” 的店铺分类", new object[] { categoryId })); HiCache.Remove("DataCache-Categories"); } return flag; }
public static CategoryActionStatus UpdateCategory(CategoryInfo category) { if (null == category) { return CategoryActionStatus.UnknowError; } Globals.EntityCoding(category, true); CategoryActionStatus status = new CategoryDao().UpdateCategory(category); if (status == CategoryActionStatus.Success) { EventLogs.WriteOperationLog(Privilege.EditProductCategory, string.Format(CultureInfo.InvariantCulture, "修改了编号为 “{0}” 的店铺分类", new object[] { category.CategoryId })); HiCache.Remove("DataCache-Categories"); } return status; }
/// <summary> /// add by:JHB 150916 店铺上架商品,根据配置来限定范围 /// type:0正常显示店铺已上架的商品,1正常显示店铺未上架的商品,2显示所有出售状态的商品,3根据上架范围显示已上架的商品,4根据上架范围显示未上架的商品 /// </summary> public DataTable GetProductsEx(MemberInfo member, int?topicId, int?categoryId, int distributorId, string keyWord, int pageNumber, int maxNum, out int toal, string sort, bool isAsc = false , ProductInfo.ProductRanage productRanage = ProductInfo.ProductRanage.NormalSelect) { int discount = 100; StringBuilder builder = new StringBuilder(); builder.AppendFormat("ProductId,ProductName,ProductCode,ShowSaleCounts AS SaleCounts,ShortDescription,", maxNum); builder.Append(" ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,MarketPrice,VistiCounts,"); if (member != null) { discount = new MemberGradeDao().GetMemberGrade(member.GradeId).Discount; builder.AppendFormat(" (CASE WHEN (SELECT COUNT(*) FROM Hishop_SKUMemberPrice WHERE SkuId = vw_Hishop_BrowseProductList.SkuId AND GradeId = {0}) = 1", member.GradeId); builder.AppendFormat(" THEN (SELECT MemberSalePrice FROM Hishop_SKUMemberPrice WHERE SkuId = vw_Hishop_BrowseProductList.SkuId AND GradeId = {0}) ELSE SalePrice*{1}/100 END) AS SalePrice", member.GradeId, discount); } else { builder.Append("SalePrice"); } StringBuilder builder2 = new StringBuilder(); builder2.Append(" SaleStatus=1"); if (topicId.HasValue) { builder2.AppendFormat(" AND ProductId IN (SELECT RelatedProductId FROM Vshop_RelatedTopicProducts WHERE TopicId = {0})", topicId.Value); } if (categoryId.HasValue) { CategoryInfo category = new CategoryDao().GetCategory(categoryId.Value); if (category != null) { builder2.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%') ", category.Path); } } if (!string.IsNullOrEmpty(keyWord)) { builder2.AppendFormat(" AND (ProductName LIKE '%{0}%' OR ProductCode LIKE '%{0}%')", keyWord); } switch (productRanage) { case ProductInfo.ProductRanage.NormalSelect: //正常显示店铺已上架的商品 builder2.AppendFormat(" AND ProductId IN (SELECT ProductId FROM Hishop_DistributorProducts WHERE UserId={0})", distributorId); break; case ProductInfo.ProductRanage.NormalUnSelect: //正常显示店铺未上架的商品 builder2.AppendFormat(" AND ProductId NOT IN (SELECT ProductId FROM Hishop_DistributorProducts WHERE UserId={0})", distributorId); break; case ProductInfo.ProductRanage.All: //显示所有出售状态的商品 break; case ProductInfo.ProductRanage.RangeSelect: //根据上架范围显示已上架的商品 //本身上架的记录 builder2.AppendFormat(" AND ProductId IN (SELECT ProductId FROM Hishop_DistributorProducts WHERE UserId={0})", distributorId); //代理取系统设置的上架范围,分销商取所属代理商的上架范围 int ofAgentID = distributorId; DataTable dtDistributors = this.database.ExecuteDataSet(CommandType.Text, string.Format("Select UserId,IsAgent,AgentPath From aspnet_Distributors Where UserId={0}", ofAgentID)).Tables[0]; if (dtDistributors.Rows[0]["IsAgent"].ToString() != "1" && dtDistributors.Rows[0]["AgentPath"] != DBNull.Value) { ofAgentID = int.Parse(dtDistributors.Rows[0]["AgentPath"].ToString().Split('|')[dtDistributors.Rows[0]["AgentPath"].ToString().Split('|').Length - 1]); } DataTable dtProductRange = this.database.ExecuteDataSet(CommandType.Text, string.Format("Select * From Hishop_DistributorProductRange Where UserId={0}", ofAgentID)).Tables[0]; if (dtProductRange.Rows.Count == 0) { builder2.AppendFormat(" And 1=2 "); //未设置上架范围 } else { DataRow drProductRange = dtProductRange.Rows[0]; builder2.AppendFormat(" AND ("); if (drProductRange["ProductRange1"] != DBNull.Value) { builder2.AppendFormat(string.Format("CategoryId in({0})", drProductRange["ProductRange1"].ToString())); } else { builder2.AppendFormat("CategoryId in(null)"); } if (drProductRange["ProductRange2"] != DBNull.Value) { builder2.AppendFormat(string.Format(" or BrandId in({0})", drProductRange["ProductRange2"].ToString())); } else { builder2.AppendFormat(" or BrandId in(null)"); } if (drProductRange["ProductRange3"] != DBNull.Value) { builder2.AppendFormat(string.Format(" or TypeId in({0})", drProductRange["ProductRange3"].ToString())); } else { builder2.AppendFormat(" or TypeId in(null)"); } builder2.AppendFormat(")"); } break; case ProductInfo.ProductRanage.RangeUnSelect: //根据上架范围显示未上架的商品 //本身上架的记录 builder2.AppendFormat(" AND ProductId Not IN (SELECT ProductId FROM Hishop_DistributorProducts WHERE UserId={0})", distributorId); //代理取系统设置的上架范围,分销商取所属代理商的上架范围 int ofAgentIDUn = distributorId; DataTable dtDistributorsUn = this.database.ExecuteDataSet(CommandType.Text, string.Format("Select UserId,IsAgent,AgentPath From aspnet_Distributors Where UserId={0}", ofAgentIDUn)).Tables[0]; if (dtDistributorsUn.Rows[0]["IsAgent"].ToString() != "1" && dtDistributorsUn.Rows[0]["AgentPath"] != DBNull.Value) { ofAgentIDUn = int.Parse(dtDistributorsUn.Rows[0]["AgentPath"].ToString().Split('|')[dtDistributorsUn.Rows[0]["AgentPath"].ToString().Split('|').Length - 1]); } DataTable dtProductRangeUn = this.database.ExecuteDataSet(CommandType.Text, string.Format("Select * From Hishop_DistributorProductRange Where UserId={0}", ofAgentIDUn)).Tables[0]; if (dtProductRangeUn.Rows.Count == 0) { builder2.AppendFormat(" And 1=2 "); //未设置上架范围 } else { DataRow drProductRange = dtProductRangeUn.Rows[0]; builder2.AppendFormat(" AND ("); if (drProductRange["ProductRange1"] != DBNull.Value) { builder2.AppendFormat(string.Format("CategoryId in({0})", drProductRange["ProductRange1"].ToString())); } else { builder2.AppendFormat("CategoryId in(null)"); } if (drProductRange["ProductRange2"] != DBNull.Value) { builder2.AppendFormat(string.Format(" or BrandId in({0})", drProductRange["ProductRange2"].ToString())); } else { builder2.AppendFormat(" or BrandId in(null)"); } if (drProductRange["ProductRange3"] != DBNull.Value) { builder2.AppendFormat(string.Format(" or TypeId in({0})", drProductRange["ProductRange3"].ToString())); } else { builder2.AppendFormat(" or TypeId in(null)"); } builder2.AppendFormat(")"); } break; } if (string.IsNullOrWhiteSpace(sort)) { sort = "ProductId"; } DbQueryResult result = DataHelper.PagingByRownumber(pageNumber, maxNum, sort, isAsc ? SortAction.Asc : SortAction.Desc, true, "vw_Hishop_BrowseProductList", "ProductId", builder2.ToString(), builder.ToString()); DataTable data = (DataTable)result.Data; toal = result.TotalRecords; return(data); }
public DataTable GetGroupBuyProducts(int? categoryId, string keyWord, int page, int size, out int total, bool onlyUnFinished = true) { StringBuilder builder = new StringBuilder(); builder.Append("a.GroupBuyId,a.ProductId,ProductName,ProductCode,ShortDescription,SoldCount,"); builder.Append(" ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160,ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,a.Price,b.SalePrice"); StringBuilder builder2 = new StringBuilder(); builder2.Append(" Hishop_GroupBuy a left join vw_Hishop_BrowseProductList b on a.ProductId = b.ProductId "); StringBuilder builder3 = new StringBuilder(" SaleStatus=1"); if (onlyUnFinished) { builder3.AppendFormat(" AND a.Status = {0}", 1); } if (categoryId.HasValue) { CategoryInfo category = new CategoryDao().GetCategory(categoryId.Value); if (category != null) { builder3.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%') ", category.Path); } } if (!string.IsNullOrEmpty(keyWord)) { builder3.AppendFormat(" AND (ProductName LIKE '%{0}%' OR ProductCode LIKE '%{0}%')", keyWord); } string sortBy = "a.DisplaySequence"; DbQueryResult result = DataHelper.PagingByRownumber(page, size, sortBy, SortAction.Desc, true, builder2.ToString(), "GroupBuyId", builder3.ToString(), builder.ToString()); DataTable data = (DataTable) result.Data; total = result.TotalRecords; return data; }