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