public MaterialPageBySearchList FindMaterialPageBySearchListByQDT(int PageIndex, int PageSize, string Keyword, string MatBrand, Guid CatID, string IsStatUs)
{
var query = (from M in db.Material
where true
select M).AsQueryable();
//品牌判断
if (!string.IsNullOrEmpty(MatBrand))
{
query = query.Where(m => m.MatBrand.Contains(MatBrand)).AsQueryable();
}
ICategory ICat = new CategoryBase();
CatTree CT = ICat.GetCatTreeByQDT();
//分类判断
if (CatID != Guid.Empty)
{
if (CT.SecondCatTree.Where(c => c.CatID == CatID).Count() > 0)
{
query = query.Where(c => c.LinkQDTCatID == CatID).AsQueryable();
}
}
//发布 新品 置顶判断
if (IsStatUs == MatISNo.IsPublic.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsNotPublic.ToString())
{
query = query.Where(x => x.IsPublic == 0 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsTop.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsTop == 1 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsNotTop.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsTop == 0 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsNew.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsNew == 1 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsNotNew.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsNew == 0 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsPromotion.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsPromotion == 1 && x.IsGroupMat <= 0).AsQueryable();
}
else if (IsStatUs == MatISNo.IsNotPromotion.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsPromotion == 0 && x.IsGroupMat <= 0).AsQueryable();
}
if (!string.IsNullOrEmpty(Keyword))
{
Keyword = Regex.Replace(Keyword, @"( )\1+", "$1", RegexOptions.None);
string[] KeywordStr = Keyword.Split(' ');
foreach (var K in KeywordStr)
{
if (!string.IsNullOrEmpty(K))
{
query = query.Where(
m => m.MatSn.Contains(K) ||
m.MatBrand.Contains(K) ||
m.MatManufacturerSn.Contains(K) ||
m.MatSpecifications.Contains(K) ||
m.MatName.Contains(K) ||
m.KeywordIndex.Contains(K)
).AsQueryable();
}
}
}
//获取以过滤的品牌项
var queryBrand = query.Where(x => x.MatBrandID != Guid.Empty).GroupBy(x => x.MatBrandID).Select(x => x.Key).AsQueryable();
List<P_Brand> PBL = new List<P_Brand>();
P_Brand PB = new P_Brand();
Brand B = new Brand();
foreach (var x in queryBrand)
{
B = db.Brand.Find(x);
if (B != null)
{
PB = new P_Brand();
PB.BID = B.BID;
PB.BrandName = B.BrandName;
PB.BrandEnName = B.BrandNameEn;
PB.BrandLogo = B.BrandLogo;
PBL.Add(PB);
}else{
PB = new P_Brand();
PB.BID = Guid.Empty;
PB.BrandName = string.Empty;
PB.BrandEnName = string.Empty;
PB.BrandLogo = string.Empty;
PBL.Add(PB);
}
}
List<P_Brand> PBLNew = new List<P_Brand>();
var QueryBrandNew = from x in PBL
where true
&&
x.BID != Guid.Empty
&&
x.BrandLogo != string.Empty
&&
x.BrandName != string.Empty
group x by x.BrandName into g
select new {
BID = g.FirstOrDefault().BID,
BrandLogo = g.FirstOrDefault().BrandLogo,
BrandName = g.FirstOrDefault().BrandName,
BrandEnName = g.FirstOrDefault().BrandEnName,
};
P_Brand NewB = new P_Brand();
foreach(var x in QueryBrandNew)
{
NewB = new P_Brand();
NewB.BID = x.BID;
NewB.BrandLogo = x.BrandLogo;
NewB.BrandEnName = x.BrandEnName;
NewB.BrandName = x.BrandName;
PBLNew.Add(NewB);
}
//获取以过滤分类项
var queryCat = from x in query
where true
&&
x.CategoryID != Guid.Empty
&&
x.LinkQDTCatID != Guid.Empty
group x by x.LinkQDTCatID into g
select new
{
CID = g.Key,
Count = g.Count()
};
List<P_Cat> PCL = new List<P_Cat>();
P_Cat PC = new P_Cat();
Category Cat = new Category();
foreach (var x in queryCat)
{
PC = new P_Cat();
if (CT.SecondCatTree.Where(c => c.CatID == x.CID).Count() > 0)
{
PC.CatID = x.CID;
PC.CatCount = x.Count;
PC.CatName = CT.SecondCatTree.Where(c => c.CatID == x.CID).FirstOrDefault().CategoryName;
PCL.Add(PC);
}
}
List<Material> ML = query.OrderByDescending(s => s.LastUpdateTime).Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList();
List<Material> SubGroupML = new List<Material>();
IBrand IB = new BrandBase();
string MaxPrice = string.Empty;
string MinPrice = string.Empty;
foreach (var x in ML)
{
x.MoreDetail = string.Empty;
if (x.IsGroupMat > 0)
{
SubGroupML = this.GetMaterialByGroupMatID(Guid.Empty, x.MatID);
x.LinkGroupMatCount = SubGroupML.Count();
try
{
MaxPrice = SubGroupML.Where(c => c.PriceInfo.SalesPrice > 0).Max(c => c.PriceInfo.SalesPrice).ToString("N");
MinPrice = SubGroupML.Where(c => c.PriceInfo.SalesPrice > 0).Min(c => c.PriceInfo.SalesPrice).ToString("N");
if (MaxPrice != MinPrice)
{
x.LinkGroupPrice = MinPrice + " ~ " + MaxPrice;
}
else
{
x.LinkGroupPrice = MinPrice;
}
}
catch
{
x.LinkGroupPrice = string.Empty;
}
}
}
ML = IB.GetMatSalesPriceInfoList(ML, Guid.Empty);
MaterialPageBySearchList MP = new MaterialPageBySearchList();
MP.PageIndex = PageIndex;
MP.PageSize = PageSize;
MP.TotalRecord = query.Count();
MP.Rows = ML;
MP.Brand = PBLNew;
MP.Cat = PCL;
return MP;
}
public List<Material> FindMaterialByPage(int PageIndex, int PageSize, out int TotalRecord, string Keyword, string MatBrand, string CatName, string IsImg, string IsStore, Guid MatBrandID, string IsStatUs, Guid LinkMainCID)
{
var query = (from M in db.Material
where true
&&
M.LinkMainCID.Equals(LinkMainCID)
select M).AsQueryable();
//品牌判断
if (!string.IsNullOrEmpty(MatBrand))
{
query = query.Where(m => m.MatBrand.Contains(MatBrand)).AsQueryable();
}
//品牌ID判断
if (MatBrandID != Guid.Empty)
{
query = query.Where(m => m.MatBrandID.Equals(MatBrandID)).AsQueryable();
}
//分类判断
if (!string.IsNullOrEmpty(CatName))
{
if(CatName == "IsNotConfigCatID")
{
query = query.Where(m => m.CategoryID == Guid.Empty).AsQueryable();
}
else
{
CatName = CatName + ',';
query = query.Where(m => m.CategoryName.Contains(CatName)).AsQueryable();
}
}
//图片判断
if (IsImg == "IsImg")
{
query = query.Where(m => m.MatImgPath != string.Empty && m.MatImgPath != null).AsQueryable();
}
if (IsImg == "IsNotImg")
{
query = query.Where(m => m.MatImgPath == string.Empty || m.MatImgPath == null).AsQueryable();
}
//安全库存判断
if (IsStore == "IsStore")
{
query = query.Where(m => m.SafetyStock > 0).AsQueryable();
}
//发布 新品 置顶判断
if(IsStatUs == MatISNo.IsPublic.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsNotPublic.ToString())
{
query = query.Where(x => x.IsPublic == 0 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsTop.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsTop == 1 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsNotTop.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsTop == 0 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsNew.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsNew == 1 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsNotNew.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsNew == 0 && x.GroupToMatID == Guid.Empty).AsQueryable();
}
else if (IsStatUs == MatISNo.IsPromotion.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsPromotion == 1 && x.IsGroupMat <= 0).AsQueryable();
}
else if (IsStatUs == MatISNo.IsNotPromotion.ToString())
{
query = query.Where(x => x.IsPublic == 1 && x.IsPromotion == 0 && x.IsGroupMat <= 0 ).AsQueryable();
}
if (!string.IsNullOrEmpty(Keyword))
{
Keyword = Regex.Replace(Keyword, @"( )\1+", "$1", RegexOptions.None);
string[] KeywordStr = Keyword.Split(' ');
foreach (var K in KeywordStr)
{
if (!string.IsNullOrEmpty(K))
{
query = query.Where(
m => m.MatSn.Contains(K) ||
m.MatBrand.Contains(K) ||
m.MatManufacturerSn.Contains(K) ||
m.MatSpecifications.Contains(K) ||
m.MatName.Contains(K) ||
m.KeywordIndex.Contains(K)
).AsQueryable();
}
}
}
TotalRecord = query.Count();
List<Material> ML = query.OrderByDescending(s => s.LastUpdateTime).Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList();
foreach(var x in ML)
{
try
{
x.SingleOrderNumber = Convert.ToInt32(x.WeeklySales * (x.LeadTime / 7) + x.SafetyStock);
}
catch
{
x.SingleOrderNumber = 0;
}
}
string SupName = string.Empty;
foreach(var x in ML.GroupBy(x=>x.LinkSupID).Select(x=>x.Key))
{
if(x == Guid.Empty)
{
SupName = string.Empty;
}
else
{
SupName = db.Supplier.Find(x).SupName;
}
foreach(var xx in ML.Where(c=>c.LinkSupID == x))
{
xx.SupplierName = SupName;
}
}
IBrand IB = new BrandBase();
ML = IB.GetMatSalesPriceInfoList(ML, Guid.Empty);
return ML;
}
public List<Material> FindMaterialForCustomerByPage(int PageIndex, int PageSize, out int TotalRecord, string Keyword, string MatBrand, string CatName, Guid MatBrandID, string IsStatUs, Guid CustomerID, Guid LinkMainCID)
{
var query = (from M in db.Material
where true
&&
M.LinkMainCID.Equals(LinkMainCID)
&&
M.IsGroupMat <= 0
select M).AsQueryable();
if (IsStatUs == MatISNo.IsPublic.ToString())
{
query = query.Where(x => x.IsPublic == 1).AsQueryable();
}
//品牌判断
if (!string.IsNullOrEmpty(MatBrand))
{
query = query.Where(m => m.MatBrand.Contains(MatBrand)).AsQueryable();
}
//品牌ID判断
if (MatBrandID != Guid.Empty)
{
query = query.Where(m => m.MatBrandID.Equals(MatBrandID)).AsQueryable();
}
//分类判断
if (!string.IsNullOrEmpty(CatName))
{
if (CatName == "IsNotConfigCatID")
{
query = query.Where(m => m.CategoryID == Guid.Empty).AsQueryable();
}
else
{
CatName = CatName + ',';
query = query.Where(m => m.CategoryName.Contains(CatName)).AsQueryable();
}
}
if (!string.IsNullOrEmpty(Keyword))
{
Keyword = Regex.Replace(Keyword, @"( )\1+", "$1", RegexOptions.None);
string[] KeywordStr = Keyword.Split(' ');
foreach (var K in KeywordStr)
{
if (!string.IsNullOrEmpty(K))
{
query = query.Where(
m => m.MatSn.Contains(K) ||
m.MatBrand.Contains(K) ||
m.MatManufacturerSn.Contains(K) ||
m.MatSpecifications.Contains(K) ||
m.MatName.Contains(K) ||
m.KeywordIndex.Contains(K)
).AsQueryable();
}
}
}
TotalRecord = query.Count();
List<Material> ML = query.OrderByDescending(s => s.LastUpdateTime).Skip((PageIndex - 1) * PageSize).Take(PageSize).ToList();
foreach (var x in ML)
{
x.MoreDetail = string.Empty;
}
IBrand IB = new BrandBase();
ML = IB.GetMatSalesPriceInfoList(ML, CustomerID);
return ML;
}
public List<Material> GetMaterialByGroupMatID(Guid CID, Guid MatID)
{
List<Material> ML = new List<Material>();
ML = db.Material.Where(x => x.GroupToMatID == MatID).OrderBy(x => x.MatName).ThenBy(x => x.MatSpecifications).ThenBy(x => x.MatManufacturerSn).ToList();
IBrand IB = new BrandBase();
ML = IB.GetMatSalesPriceInfoList(ML, CID);
foreach (var x in ML)
{
x.MoreDetail = string.Empty;
}
return ML;
}