/// <summary> /// 通过keyword查询所有产品 /// </summary> /// <param name="keyword"></param> /// <returns></returns> public ProductListBykeyword SearchProductByKeyword(string keyword) { ProductListBykeyword plist = new ProductListBykeyword(); string catesql = "select a.ID,a.PartName,a.CategoryID,a.PartBrand,a.Price,b.imgpath from Parts a left join (select * from ImgStock where id in (select min(id) from ImgStock group by PartID)) b on a.ID=b.PartID where a.State=1 and a.CategoryID in (select id from PartsCategory where CategoryName like '%{0}%' ) and a.ProType=0 order by a.ShowDate, a.orderby desc"; List <Product> list1 = db.Database.SqlQuery <Product>(string.Format(catesql, keyword)).ToList(); string prosql = "select a.ID,a.PartName,a.CategoryID,a.PartBrand,a.Price,b.imgpath from Parts a left join (select * from ImgStock where id in (select min(id) from ImgStock group by PartID)) b on a.ID=b.PartID where a.State=1 and a.PartName like '%{0}%' and a.ProType=0 order by a.ShowDate, a.orderby desc"; List <Product> list2 = db.Database.SqlQuery <Product>(string.Format(prosql, keyword)).ToList(); if (list1.Count == 0) { list1 = list2; } else { if (list2.Count > 0) { list2.AddRange(list1); } } list1 = list1.Where((x, i) => list1.FindIndex(z => z.ID == x.ID) == i).ToList(); string carsql = "select id from CarM where brand like '%{0}%' or model like '%{0}%'"; List <int> carList = db.Database.SqlQuery <int>(string.Format(carsql, keyword)).ToList(); string carProsql = "select a.ID,a.PartName,a.CategoryID,a.PartBrand,a.Price,b.imgpath from Parts a left join (select * from ImgStock where id in (select min(id) from ImgStock group by PartID)) b on a.ID=b.PartID where a.State=1 and a.tocars like '%,{0},%' and a.ProType=0 order by a.ShowDate, a.orderby desc"; foreach (var item in carList) { List <Product> list3 = db.Database.SqlQuery <Product>(string.Format(carProsql, item)).ToList(); if (list1.Count == 0) { list1 = list3; } else { if (list3.Count > 0) { list3.AddRange(list1); } } } list1 = list1.Where((x, i) => list1.FindIndex(z => z.ID == x.ID) == i).ToList(); //List<Product> list3 = db.Database.SqlQuery<Product>(string.Format(prosql, keyword)).ToList(); plist.proList = list1; //string CSql = " select * from PartsCategory where ID={0} and IsShow=1 "; //DAO.PartsCategory pc = db.Database.SqlQuery<DAO.PartsCategory>(string.Format(CSql, string.IsNullOrEmpty(id) ? "1" : id)).FirstOrDefault(); //plist.partCate = pc; return(plist); }
public ActionResult SearchProduct(string keyword, int pageIndex = 1) { int pageSize = 32; //数量 string kd = HttpUtility.HtmlEncode(keyword.Trim()); ProductBll bll = new ProductBll(); ProductListBykeyword proList = bll.SearchProductByKeyword(kd); List <Product> plist = proList.proList; var page = (from c in plist select c).Skip((pageIndex - 1) * pageSize).Take(pageSize);//跳过前50条数据,取10条 proList.proList = page.ToList(); proList.totleCount = plist.Count; proList.pageSize = pageSize; proList.currentPage = pageIndex; return(View(proList)); }