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