public ListProductModel Query(FilterModel filter)
{
int offset = (filter.page - 1) * filter.view;
int count = filter.view;
IQueryable<SanPham> query = null;
if (filter.query != null && filter.query.Trim() != "")
{
string name = filter.query;
query = SearchByName(name, offset, count);
}
else
{
string sql = CreatSqlQuery(filter);
query = (((IObjectContextAdapter)db).ObjectContext.CreateQuery<SanPham>(sql));
}
ListProductModel result = new ListProductModel();
string[] param = filter.sortby.Split('_');
bool order_price = param[0] == "price" ? true : false;
bool asc = param[1] == "asc" ? true : false;
if (order_price)
{
if (asc)
{
result.data = query.OrderBy(x => x.GiaBanHienHanh).Skip(offset).Take(count).ToList();
}
else
{
result.data = query.OrderByDescending(x => x.GiaBanHienHanh).Skip(offset).Take(count).ToList();
}
}
else
{
if (asc)
{
result.data = query.OrderBy(x => x.TenSP).Skip(offset).Take(count).ToList();
}
else
{
result.data = query.OrderByDescending(x => x.TenSP).Skip(offset).Take(count).ToList();
}
}
foreach (SanPham sp in result.data)
{
sp.ChiTietSanPham = GetDetailProductWithoutImage(sp.MaCTSP);
}
result.count = query.Count();
return result;
}
private string CreatSqlQuery(FilterModel filter)
{
string where = "";
if (filter.price != null)
{
string wherePrice = "";
foreach (string price in filter.price)
{
if (wherePrice != "")
{
wherePrice += " or";
}
string[] param = price.Split('_');
wherePrice += string.Format(" (sp.GiaBanHienHanh >= {0} and sp.GiaBanHienHanh <= {1})", param[0], param[1]);
}
if (wherePrice != "")
{
where += string.Format("({0})", wherePrice);
}
}
if (filter.brand != null)
{
string whereBrand = "";
foreach (string brand in filter.brand)
{
if (whereBrand != "")
{
whereBrand += " or";
}
whereBrand += string.Format(" (sp.MaNSX = {0})", brand);
}
if (whereBrand != "")
{
if (where != "")
{
where += " and";
}
where += string.Format("({0})", whereBrand);
}
}
if (filter.star != null)
{
string whereStar = "";
foreach (string star in filter.star)
{
if (whereStar != "")
{
whereStar += " or";
}
whereStar += string.Format(" (sp.DiemDanhGia >= {0})", star);
}
if (whereStar != "")
{
if (where != "")
{
where += " and";
}
where += string.Format("({0})", whereStar);
}
}
if (filter.os != null)
{
string whereos = "";
foreach (string os in filter.os)
{
if (whereos != "")
{
whereos += " or";
}
whereos += string.Format(" (sp.ChiTietSanPham.HeDieuHanh.MaHDH = '{0}')", os);
}
if (whereos != "")
{
if (where != "")
{
where += " and";
}
where += string.Format("({0})", whereos);
}
}
string sql = "SELECT VALUE sp FROM CellPhoneDbEntities.SanPhams as sp";
if (where != "")
{
sql += " WHERE" + where;
}
return sql;
}
