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