/// <summary>
/// 取分类商品
/// </summary>
/// <param name="categoryId"></param>
/// <param name="keyword"></param>
/// <param name="sortType"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <Goods> GetGoodsByCategory(Guid?categoryId, string keyword, GoodsSortType sortType, int pageIndex, int pageSize, out int totalCount)
{
using (var dbContext = new MallDbContext())
{
var query = dbContext.Goods.Where(me => me.Status > 0);
if (categoryId != null && categoryId != Guid.Empty)
{
query = query.Where(me => me.CategoryId == categoryId);
}
if (!string.IsNullOrWhiteSpace(keyword))
{
query = query.Where(me => me.Name.Contains(keyword));
}
switch (sortType)
{
case GoodsSortType.Degault:
query = query.OrderByDescending(me => me.Sort).ThenByDescending(me => me.CreateTime);
break;
case GoodsSortType.PriceLow:
query = query.OrderBy(me => me.ShopPrice).ThenByDescending(me => me.Sort).ThenByDescending(me => me.CreateTime);
break;
case GoodsSortType.PriceHigh:
query = query.OrderByDescending(me => me.ShopPrice).ThenByDescending(me => me.Sort).ThenByDescending(me => me.CreateTime);
break;
case GoodsSortType.SalesVolumeHigh:
query = query.OrderByDescending(me => me.SalesVolume).ThenByDescending(me => me.Sort).ThenByDescending(me => me.CreateTime);
break;
default:
query = query.OrderByDescending(me => me.Sort).ThenByDescending(me => me.CreateTime);
break;
}
totalCount = query.Count();
return(query.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList());
}
}
/// <summary>
/// 【视图查询】商品信息属性查询
/// </summary>
/// <param name="categoryId"></param>
/// <param name="keyword"></param>
/// <param name="brands"></param>
/// <param name="others"></param>
/// <param name="minPrice"></param>
/// <param name="maxPrice"></param>
/// <param name="sortType"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <GoodsView> GetGoodsByCategory_View(Guid?categoryId, string keyword, List <Guid> brands, List <AttributeModel> others, decimal minPrice, decimal maxPrice, GoodsSortType sortType, int pageIndex, int pageSize, out int totalCount)
{
using (var dbContext = new MallDbContext())
{
var query = from v in dbContext.SingleGoodsCategoryView
where v.CategoryId == categoryId
select new ExtendGoodsViewModelnew
{
GoodsView =
{
Id = v.Id,
Name = v.Name,
GoodsNo = v.GoodsNo,
BrandId = v.BrandId,
GoodsType = v.GoodsType,
CategoryId = v.CategoryId,
OriginalPrice = v.OriginalPrice,
ShopPrice = v.ShopPrice,
IsNew = v.IsNew,
IsBest = v.IsBest,
IsHot = v.IsHot,
CreateTime = v.CreateTime,
LastUpdateTime = v.LastUpdateTime,
Stock = v.Stock,
PaymentAmount = v.PaymentAmount,
SalesVolume = v.SalesVolume,
EvaluateCount = v.EvaluateCount,
ViewCount = v.ViewCount,
Status = v.Status,
Sort = v.Sort,
FreeShipping = v.FreeShipping,
SingleGoodsId = v.SingleGoodsId,
AttributeId = v.AttributeId,
AttributeValue = v.AttributeValue
},
ExtendCategoryId = v.ExtentCategoryId
}
;
//var query = dbContext.GoodsViews.Where(me => me.Status != GoodsStatus.Delete);
//分类
if (categoryId != null && categoryId != Guid.Empty)
{
query = query.Where(me => me.GoodsView.CategoryId == categoryId || me.ExtendCategoryId == categoryId);
}
//价格
if (minPrice > 0)
{
query = query.Where(me => me.GoodsView.ShopPrice >= minPrice);
}
if (maxPrice > 0)
{
query = query.Where(me => me.GoodsView.ShopPrice <= maxPrice);
}
//关键词
if (!string.IsNullOrWhiteSpace(keyword))
{
query = query.Where(me => me.GoodsView.Name.Contains(keyword));
}
//品牌
if (brands != null && brands.Count > 0)
{
query = query.Where(me => brands.Contains((Guid)me.GoodsView.BrandId));
}
//其他属性
if (others != null && others.Count > 0)
{
foreach (var attribute in others)
{
//寻找符合条件的单品
var values = attribute.AttributeValues.Split(',');
query = query.Where(me => !(me.GoodsView.AttributeId == attribute.AttributeId && !values.Contains(me.GoodsView.AttributeValue)));
}
}
//排序
switch (sortType)
{
case GoodsSortType.Degault:
query = query.OrderByDescending(me => me.GoodsView.Sort).ThenByDescending(me => me.GoodsView.CreateTime);
break;
case GoodsSortType.PriceLow:
query = query.OrderBy(me => me.GoodsView.ShopPrice).ThenByDescending(me => me.GoodsView.Sort).ThenByDescending(me => me.GoodsView.CreateTime);
break;
case GoodsSortType.PriceHigh:
query = query.OrderByDescending(me => me.GoodsView.ShopPrice).ThenByDescending(me => me.GoodsView.Sort).ThenByDescending(me => me.GoodsView.CreateTime);
break;
case GoodsSortType.SalesVolumeHigh:
query = query.OrderByDescending(me => me.GoodsView.SalesVolume).ThenByDescending(me => me.GoodsView.Sort).ThenByDescending(me => me.GoodsView.CreateTime);
break;
default:
query = query.OrderByDescending(me => me.GoodsView.Sort).ThenByDescending(me => me.GoodsView.CreateTime);
break;
}
totalCount = query.Select(x => x.GoodsView.Id).Distinct().Count();
return(query.Select(x => x.GoodsView).DistinctBy(x => x.Id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList());
}
}
/// <summary>
/// 商品信息属性查询
/// </summary>
/// <param name="categoryId"></param>
/// <param name="keyword"></param>
/// <param name="brands"></param>
/// <param name="others"></param>
/// <param name="minPrice"></param>
/// <param name="maxPrice"></param>
/// <param name="sortType"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <GoodsView> GetGoodsByCategory(Guid?categoryId, string keyword, List <Guid> brands, List <AttributeModel> others, decimal minPrice, decimal maxPrice, GoodsSortType sortType, int pageIndex, int pageSize, out int totalCount)
{
using (var dbContext = new MallDbContext())
{
var query = from g in dbContext.GoodsViews
join gr in dbContext.GoodsCategoryRelations on g.Id equals gr.GoodsId into temp
from gr in temp.DefaultIfEmpty()
join gc in dbContext.GoodsCategories on g.CategoryId equals gc.Id
where g.Status == GoodsStatus.InSale
select new ExtendGoodsViewModelnew {
GoodsView = g, MergerCategoryId = gc.MergerId, ExtendCategoryId = gr.CategoryId
}
;
//var query = dbContext.GoodsViews.Where(me => me.Status != GoodsStatus.Delete);
//分类
if (categoryId != null && categoryId != Guid.Empty)
{
var strCategoryId = categoryId.ToString();
query = query.Where(me => me.MergerCategoryId.Contains(strCategoryId) || me.ExtendCategoryId == categoryId);
}
//价格
if (minPrice > 0)
{
query = query.Where(me => me.GoodsView.ShopPrice >= minPrice);
}
if (maxPrice > 0)
{
query = query.Where(me => me.GoodsView.ShopPrice <= maxPrice);
}
//关键词
if (!string.IsNullOrWhiteSpace(keyword))
{
query = query.Where(me => me.GoodsView.Name.Contains(keyword));
}
//品牌
if (brands != null && brands.Count > 0)
{
query = query.Where(me => brands.Contains((Guid)me.GoodsView.BrandId));
}
//其他属性
if (others != null && others.Count > 0)
{
foreach (var attribute in others)
{
//寻找符合条件的单品
var values = attribute.AttributeValues.Split(',');
query = query.Where(me => !(me.GoodsView.AttributeId == attribute.AttributeId && !values.Contains(me.GoodsView.AttributeValue)));
}
}
//排序
switch (sortType)
{
case GoodsSortType.Degault:
query = query.OrderByDescending(me => me.GoodsView.Sort).ThenByDescending(me => me.GoodsView.CreateTime);
break;
case GoodsSortType.PriceLow:
query = query.OrderBy(me => me.GoodsView.ShopPrice).ThenByDescending(me => me.GoodsView.Sort).ThenByDescending(me => me.GoodsView.CreateTime);
break;
case GoodsSortType.PriceHigh:
query = query.OrderByDescending(me => me.GoodsView.ShopPrice).ThenByDescending(me => me.GoodsView.Sort).ThenByDescending(me => me.GoodsView.CreateTime);
break;
case GoodsSortType.SalesVolumeHigh:
query = query.OrderByDescending(me => me.GoodsView.SalesVolume).ThenByDescending(me => me.GoodsView.Sort).ThenByDescending(me => me.GoodsView.CreateTime);
break;
default:
query = query.OrderByDescending(me => me.GoodsView.Sort).ThenByDescending(me => me.GoodsView.CreateTime);
break;
}
totalCount = query.Select(x => x.GoodsView.Id).Distinct().Count();
return(query.Select(x => x.GoodsView).DistinctBy(x => x.Id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList());
}
}
/// <summary>
/// 【视图查询】商品信息查询
/// </summary>
/// <param name="categoryId"></param>
/// <param name="keyword"></param>
/// <param name="brands"></param>
/// <param name="minPrice"></param>
/// <param name="maxPrice"></param>
/// <param name="sortType"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <Goods> GetGoodsByCategory_View(Guid?categoryId, string keyword, List <Guid> brands, decimal minPrice, decimal maxPrice, GoodsSortType sortType, int pageIndex, int pageSize, out int totalCount)
{
using (var dbContext = new MallDbContext())
{
var query = from v in dbContext.GoodsCategoryView select v;
;
//var query = dbContext.Goods.Where(me => me.Status > 0);
//分类
if (categoryId != null && categoryId != Guid.Empty)
{
query = query.Where(me => me.CategoryId == categoryId || me.ExtentCategoryId == categoryId || me.MergerId.Contains(categoryId.ToString()));
}
//价格
if (minPrice > 0)
{
query = query.Where(me => me.ShopPrice >= minPrice);
}
if (maxPrice > 0)
{
query = query.Where(me => me.ShopPrice <= maxPrice);
}
//关键词
if (!string.IsNullOrWhiteSpace(keyword))
{
query = query.Where(me => me.Name.Contains(keyword));
}
//品牌
if (brands != null && brands.Count > 0)
{
query = query.Where(me => brands.Contains((Guid)me.BrandId));
}
//排序
switch (sortType)
{
case GoodsSortType.Degault:
query = query.OrderByDescending(me => me.Sort).ThenByDescending(me => me.CreateTime);
break;
case GoodsSortType.PriceLow:
query = query.OrderBy(me => me.ShopPrice).ThenByDescending(me => me.Sort).ThenByDescending(me => me.CreateTime);
break;
case GoodsSortType.PriceHigh:
query = query.OrderByDescending(me => me.ShopPrice).ThenByDescending(me => me.Sort).ThenByDescending(me => me.CreateTime);
break;
case GoodsSortType.SalesVolumeHigh:
query = query.OrderByDescending(me => me.SalesVolume).ThenByDescending(me => me.Sort).ThenByDescending(me => me.CreateTime);
break;
default:
query = query.OrderByDescending(me => me.Sort).ThenByDescending(me => me.CreateTime);
break;
}
totalCount = query.Count();
return(query.Select(x => new Goods
{
Id = x.Id,
Name = x.Name,
GoodsNo = x.GoodsNo,
BrandId = x.BrandId,
GoodsType = x.GoodsType,
CategoryId = x.CategoryId,
OriginalPrice = x.OriginalPrice,
ShopPrice = x.ShopPrice,
IsNew = x.IsNew,
IsBest = x.IsBest,
IsHot = x.IsHot,
CreateTime = x.CreateTime,
LastUpdateTime = x.LastUpdateTime,
Stock = x.Stock,
PaymentAmount = x.PaymentAmount,
SalesVolume = x.SalesVolume,
EvaluateCount = x.EvaluateCount,
ViewCount = x.ViewCount,
Status = x.Status,
Sort = x.Sort,
FreeShipping = x.FreeShipping
}).DistinctBy(x => x.Id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList());
}
}
/// <summary>
/// 商品信息查询
/// </summary>
/// <param name="categoryId"></param>
/// <param name="keyword"></param>
/// <param name="brands"></param>
/// <param name="minPrice"></param>
/// <param name="maxPrice"></param>
/// <param name="sortType"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public List <Goods> GetGoodsByCategory(Guid?categoryId, string keyword, List <Guid> brands, decimal minPrice, decimal maxPrice, GoodsSortType sortType, int pageIndex, int pageSize, out int totalCount)
{
using (var dbContext = new MallDbContext())
{
var query = from g in dbContext.Goods
join gr in dbContext.GoodsCategoryRelations on g.Id equals gr.GoodsId into temp
from gr in temp.DefaultIfEmpty()
join gc in dbContext.GoodsCategories on g.CategoryId equals gc.Id
where g.Status == GoodsStatus.InSale
select new { g, gr.CategoryId, gc.MergerId }
;
//var query = dbContext.Goods.Where(me => me.Status > 0);
//分类
if (categoryId != null && categoryId != Guid.Empty)
{
query = query.Where(me => me.g.CategoryId == categoryId || me.CategoryId == categoryId || me.MergerId.Contains(categoryId.ToString()));
}
//价格
if (minPrice > 0)
{
query = query.Where(me => me.g.ShopPrice >= minPrice);
}
if (maxPrice > 0)
{
query = query.Where(me => me.g.ShopPrice <= maxPrice);
}
//关键词
if (!string.IsNullOrWhiteSpace(keyword))
{
query = query.Where(me => me.g.Name.Contains(keyword));
}
//品牌
if (brands != null && brands.Count > 0)
{
query = query.Where(me => brands.Contains((Guid)me.g.BrandId));
}
//排序
switch (sortType)
{
case GoodsSortType.Degault:
query = query.OrderByDescending(me => me.g.Sort).ThenByDescending(me => me.g.CreateTime);
break;
case GoodsSortType.PriceLow:
query = query.OrderBy(me => me.g.ShopPrice).ThenByDescending(me => me.g.Sort).ThenByDescending(me => me.g.CreateTime);
break;
case GoodsSortType.PriceHigh:
query = query.OrderByDescending(me => me.g.ShopPrice).ThenByDescending(me => me.g.Sort).ThenByDescending(me => me.g.CreateTime);
break;
case GoodsSortType.SalesVolumeHigh:
query = query.OrderByDescending(me => me.g.SalesVolume).ThenByDescending(me => me.g.Sort).ThenByDescending(me => me.g.CreateTime);
break;
default:
query = query.OrderByDescending(me => me.g.Sort).ThenByDescending(me => me.g.CreateTime);
break;
}
totalCount = query.Count();
return(query.Select(x => x.g).DistinctBy(x => x.Id).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList());
}
}
