public IEnumerable <QuotesShowModel> GetQuotes([FromUri] QuotesQueryModel model) { var list = DAL.GetQuotesList(model); List <QuotesShowModel> qsmlist = new List <QuotesShowModel>(); foreach (var item in list) { QuotesShowModel qsm = new QuotesShowModel(); qsm.ID = item.ID; qsm.RegionName = item.RegionName; qsm.ServiceName = item.ServiceName; qsm.ServiceNickName = item.ServiceNickName; qsm.SaleTypeName = item.SaleTypeName; qsm.ProductName = item.ProductName; qsm.ProducPrice = item.ProducPrice; qsm.DealTime = item.DealTime.Value.Year.ToString().Substring(2, 2) + '/' + item.DealTime.Value.ToString("MM/dd"); if (item.IsAnonymous) { qsm.Bidder = "***"; } else { qsm.Bidder = BLL.J3Price_Helper.RemoveSpecialCharacter(item.Bidder);//处理表情符号 } qsm.QuotationTime = item.QuotationTime.Value.ToShortDateString(); qsmlist.Add(qsm); } return(qsmlist); }
/// <summary> /// 获取报价信息 /// </summary> /// <param name="region"></param> /// <param name="server"></param> /// <param name="productname"></param> /// <param name="dealtime"></param> /// <returns></returns> public IEnumerable <QuotesModel> GetQuotesList(QuotesQueryModel model) { string str = model.Server; str = str.Substring(1, str.Length - 2); int RegionID = int.Parse(str.Split(',')[0]); int ServiceID = int.Parse(str.Split(',')[1]); int Count = 5;//前N条 var _query = from q in db.Quotes join p in db.Products on q.ProductID equals p.ProductID join e in db.Exteriors on p.ExteriorID equals e.ExteriorID join r in db.RegionMst on q.RegionID equals r.RegionID join service in db.ServiceMst on q.ServiceID equals service.ServiceID join sale in db.SaleTypeMst on q.SaleTypeCode equals sale.SaleTypeCode where q.RegionID == RegionID orderby p.ProductName, q.DealTime descending, q.QuotationTime descending select new { q.ID, r.RegionName, service.ServiceID, service.ServiceName, service.ServiceNickName, sale.SaleTypeName, e.ExteriorID, p.ProductName, p.ProductNickName1, p.ProductNickName2, p.ProductNickName3, p.ProductNickName4, p.ProductNickName5, q.ProducPrice, q.DealTime, q.Bidder, q.IsAnonymous, q.QuotationTime }; var _query2 = from s in db.Products join e in db.Exteriors on s.ExteriorID equals e.ExteriorID orderby e.ExteriorName select s.ProductName; string[] order = _query2.ToArray();//获取关键词列表 switch (ServiceID) { case 0: //全部服务器 if (model.ProductName == null) { //按外观类型查询 string sqlQuery = "select * from(select SN = ROW_NUMBER()over(PARTITION by productid order by QuotationTime desc),* from quotes where RegionID=@RegionID)tmp where tmp.SN <= @Count"; var list = db.Database.SqlQuery <Quotes>(sqlQuery, new SqlParameter("@RegionID", RegionID), new SqlParameter("@Count", Count)); var query = from q in list join p in db.Products on q.ProductID equals p.ProductID join e in db.Exteriors on p.ExteriorID equals e.ExteriorID join r in db.RegionMst on q.RegionID equals r.RegionID join service in db.ServiceMst on q.ServiceID equals service.ServiceID join sale in db.SaleTypeMst on q.SaleTypeCode equals sale.SaleTypeCode where e.ExteriorID == model.ExteriorID select new QuotesModel { ID = q.ID, RegionName = r.RegionName, ServiceName = service.ServiceName, ServiceNickName = service.ServiceNickName, SaleTypeName = sale.SaleTypeName, ProductName = p.ProductName, ProducPrice = q.ProducPrice, DealTime = q.DealTime, Bidder = q.Bidder, IsAnonymous = q.IsAnonymous, QuotationTime = q.QuotationTime }; return(query.AsEnumerable().OrderBy(a => Array.IndexOf(order, a.ProductName)).ThenByDescending(a => a.DealTime).ThenByDescending(a => a.QuotationTime));//按照关键词列表排序、成交时间降序排列、报价时间降序排列 } else { //按物品名称查询(30条) return(_query.Where(x => x.ProductName == model.ProductName || x.ProductNickName1 == model.ProductName || x.ProductNickName2 == model.ProductName || x.ProductNickName3 == model.ProductName || x.ProductNickName4 == model.ProductName || x.ProductNickName5 == model.ProductName ).Select(x => new QuotesModel { ID = x.ID, RegionName = x.RegionName, ServiceName = x.ServiceName, ServiceNickName = x.ServiceNickName, SaleTypeName = x.SaleTypeName, ProductName = x.ProductName, ProducPrice = x.ProducPrice, DealTime = x.DealTime, Bidder = x.Bidder, IsAnonymous = x.IsAnonymous, QuotationTime = x.QuotationTime }).Take(30).ToList()); } default: //指定服务器 if (model.ProductName == null) { //按外观类型查询 string sqlQuery = "select * from(select SN = ROW_NUMBER()over(PARTITION by productid order by DealTime desc),* from quotes where RegionID=@RegionID and ServiceID=@ServiceID)tmp where tmp.SN <= @Count"; var list = db.Database.SqlQuery <Quotes>(sqlQuery, new SqlParameter("@RegionID", RegionID), new SqlParameter("@ServiceID", ServiceID), new SqlParameter("@Count", Count)); var query = from q in list join p in db.Products on q.ProductID equals p.ProductID join e in db.Exteriors on p.ExteriorID equals e.ExteriorID join r in db.RegionMst on q.RegionID equals r.RegionID join service in db.ServiceMst on q.ServiceID equals service.ServiceID join sale in db.SaleTypeMst on q.SaleTypeCode equals sale.SaleTypeCode where e.ExteriorID == model.ExteriorID select new QuotesModel { ID = q.ID, RegionName = r.RegionName, ServiceName = service.ServiceName, ServiceNickName = service.ServiceNickName, SaleTypeName = sale.SaleTypeName, ProductName = p.ProductName, ProducPrice = q.ProducPrice, DealTime = q.DealTime, Bidder = q.Bidder, IsAnonymous = q.IsAnonymous, QuotationTime = q.QuotationTime }; return(query.AsEnumerable().OrderBy(a => Array.IndexOf(order, a.ProductName)).ThenByDescending(a => a.DealTime).ThenByDescending(a => a.QuotationTime)); //按照关键词列表排序、成交时间降序排列、报价时间降序排列 } else { //按物品名称查询 return(_query.Where(x => x.ServiceID == ServiceID && (x.ProductName == model.ProductName || x.ProductNickName1 == model.ProductName || x.ProductNickName2 == model.ProductName || x.ProductNickName3 == model.ProductName || x.ProductNickName4 == model.ProductName || x.ProductNickName5 == model.ProductName ) ).Select(x => new QuotesModel { ID = x.ID, RegionName = x.RegionName, ServiceName = x.ServiceName, ServiceNickName = x.ServiceNickName, SaleTypeName = x.SaleTypeName, ProductName = x.ProductName, ProducPrice = x.ProducPrice, DealTime = x.DealTime, Bidder = x.Bidder, IsAnonymous = x.IsAnonymous, QuotationTime = x.QuotationTime }).Take(30).ToList()); } } }