// inicjalizuj wartości domyślne
public Search(Search oldSearch)
{
_searchTerm = oldSearch.SearchTerm;
_includeTitle = oldSearch.IncludeTitle;
_includeAuthor = oldSearch.IncludeAuthor;
_includeCategory = oldSearch.IncludeCategory;
_includeDescription = oldSearch.IncludeDescription;
_sortOrder = oldSearch.SortOrder;
_sortBy = oldSearch.SortBy;
_howMuchSkip = oldSearch.HowMuchSkip;
_howMuchTake = oldSearch.HowMuchTake;
}
public string GetAutocompleteResults(Search search)
{
if (!ModelState.IsValid)
throw new Exception("Model dla 'search' jest nieprawidłowy");
bool error;
string resultsCounter;
var books = GetBooks(search, out resultsCounter, out error);
var dateFormatSettings = new JsonSerializerSettings
{
DateFormatHandling = DateFormatHandling.IsoDateFormat,
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
};
return JsonConvert.SerializeObject(books, dateFormatSettings);
}
public ActionResult Index()
{
var search = new Search();
return View(search);
}
public JsonResult GetDefaultSearchProperties()
{
var filteredSearchOptions = new Search().ToDictionary().Where(kvp => kvp.Key.GetType() != typeof(List<SelectListItem>)).ToDictionary(kvp => kvp.Key, kvp => kvp.Value);
return Json(filteredSearchOptions, JsonRequestBehavior.AllowGet);
}
// NIEUŻYWANE
protected List<Book> GetBooksDynamicLinqExpressions(Search search)
{
var db = new ProjectDbContext();
var books = db.Books;//.Include(b => b.Author);
var listTerms = search.SearchTerm.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries)
.Where(s => s.Length >= 3).ToList().ConvertAll(t => t.ToLower().Replace("|", ""));
var searchedBooks = books;
//.AsQueryable().Where(delegate (Book book)
//{
// if (book.IsPublic != true)
// return false;
// if (listTerms.Count <= 0)
// return true;
// var sbWhereToSearch = new StringBuilder();
// var titleValue = book.Title;
// var authorValue = db.Users.Single(u => u.Id == book.AuthorId).UserName;
// var categoryValue = book.Category;
// var descriptionValue = book.Description;
// if (search.IncludeTitle)
// sbWhereToSearch.Append(titleValue + " ");
// if (search.IncludeAuthor)
// sbWhereToSearch.Append(authorValue + " ");
// if (search.IncludeCategory)
// sbWhereToSearch.Append(categoryValue + " ");
// if (search.IncludeDescription)
// sbWhereToSearch.Append(descriptionValue + " ");
// if (sbWhereToSearch.Length == 0) // jeśli nic nie zostało wybrane
// sbWhereToSearch.Append(titleValue + " ");
// return listTerms.All(s => sbWhereToSearch.ToString().ToLower().Contains(s));
//});
//// Drzewo wyrażenia reprezentujące parametr predykatu
//ParameterExpression pe = Expression.Parameter(typeof(Book), "book");
//LabelTarget returnTarget = Expression.Label(typeof(bool));
//// if (book.IsPublic != true)
//// return false;
//Expression ifBookNotPublic = Expression.IfThen(
// Expression.NotEqual(
// Expression.Property(pe, typeof(Book).GetProperty("IsPublic")),
// Expression.Constant(true)),
// Expression.Return(returnTarget, Expression.Constant(false)));
//// if (listTerms.Count <= 0)
//// return true;
//Expression paramListTerms = Expression.Constant(listTerms);
//Expression ifListTermsCountLessOrEqualThanZero = Expression.IfThen(
// Expression.LessThanOrEqual(
// Expression.Property(paramListTerms, typeof(List<string>).GetProperty("Count")),
// Expression.Constant(0, typeof(int))),
// Expression.Return(returnTarget, Expression.Constant(true)));
//// listTerms.All(s => sbWhereToSearch.ToString().ToLower().Contains(s));
//ParameterExpression pTerm = Expression.Parameter(typeof(string), "s");
//Expression paramSearch = Expression.Constant(search);
//// if (search.IncludeTitle)
//// sbWhereToSearch.Append(titleValue + " ");
//Expression ifSearchIncludeTitleThenConcat = Expression.IfThen(
// Expression.Equal(
// Expression.Property(paramSearch, typeof(Search).GetProperty("IncludeTitle")),
// Expression.Constant(true)),
// Expression. WHAT NOW? );
//// ===================================
//var exprBlock = Expression.Block(); // Expression Calls here
//var searchedBooks = books.AsQueryable().Where(Expression.Lambda<Func<Book, bool>>(exprBlock, pe)); // książki, takie dla których cały blok zwraca true
var sortedBooks = searchedBooks.OrderBy(search.SortBy + " " + search.SortOrder.ToLower()); // dynamic LINQ query helper
var pagedBooks = search.HowMuchSkip >= 0 ?
sortedBooks.Skip(search.HowMuchSkip).Take(search.HowMuchTake) :
Enumerable.Empty<Book>().AsQueryable();
//var sql = ((ObjectQuery)pagedBooks).ToTraceString();
//var linq = pagedBooks.ToString();
// całe procedurą z LIMIT search.HowMuchSkip OFFSET search.HowMuchTake
// E:\Program Files\XAMPP\mysql\data\Szymon.log
return pagedBooks.ToList(); // Error: LINQ to Entities does not recognize the method 'Boolean CheckWhatToSearch(MVCDemo.Models.Book, MVCDemo.Models.Search, System.Collections.Generic.List`1[System.String])' method, and this method cannot be translated into a store expression.
}
// NIEUŻYWANE
protected List<Book> GetBooksAlternate(Search search)
{
var db = new ProjectDbContext();
var books = db.Books;
var listTerms = search.SearchTerm.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries)
.Where(s => s.Length >= 3).ToList().ConvertAll(t => t.ToLower().Replace("|", ""));
var searchedBooks = books
.Where(delegate (Book book)
{
if (book.IsPublic != true)
return false;
if (listTerms.Count <= 0)
return true;
var sbWhereToSearch = new StringBuilder();
var titleValue = book.Title;
var authorValue = db.Users.Single(u => u.Id == book.AuthorId).UserName;
var categoryValue = book.Category;
var descriptionValue = book.Description;
if (search.IncludeTitle)
sbWhereToSearch.Append(titleValue + " ");
if (search.IncludeAuthor)
sbWhereToSearch.Append(authorValue + " ");
if (search.IncludeCategory)
sbWhereToSearch.Append(categoryValue + " ");
if (search.IncludeDescription)
sbWhereToSearch.Append(descriptionValue + " ");
if (sbWhereToSearch.Length == 0) // jeśli nic nie zostało wybrane
sbWhereToSearch.Append(titleValue + " ");
return listTerms.All(sbWhereToSearch.ToString().ToLower().Contains); // true jeśli zawiera wszystkie elementy z wpisanych przez usera
});
var sortedBooks = searchedBooks.OrderBy(search.SortBy + " " + search.SortOrder.ToLower()); // dynamic LINQ query helper
var pagedBooks = search.HowMuchSkip >= 0 ?
sortedBooks.Skip(search.HowMuchSkip).Take(search.HowMuchTake) :
Enumerable.Empty<Book>().AsQueryable();
return pagedBooks.ToList();
}
protected List<Book> GetBooks(Search search, out string resultsCounter, out bool error)
{
error = false;
resultsCounter = "n/a";
var books = Enumerable.Empty<Book>().ToList();
using (var db = new ProjectDbContext())
{
db.Database.Initialize(force: false); // MODEL MUSI BYĆ ZBUDOWANY ZANIM OTWORZYMY POŁĄCZENIE, INACZEJ BĘDZIE BŁĄD, CANNOT USE CONTEXT DURING MODEL CREATING
var paramSearchTerms = new MySqlParameter { ParameterName = "p_SearchTerms", Value = search.SearchTerm };
var paramIncludeTitle = new MySqlParameter { ParameterName = "p_IncludeTitle", Value = search.IncludeTitle };
var paramIncludeAuthor = new MySqlParameter { ParameterName = "p_IncludeAuthor", Value = search.IncludeAuthor };
var paramIncludeCategory = new MySqlParameter { ParameterName = "p_IncludeCategory", Value = search.IncludeCategory };
var paramIncludeDescription = new MySqlParameter { ParameterName = "p_IncludeDescription", Value = search.IncludeDescription };
var paramHowMuchSkip = new MySqlParameter { ParameterName = "p_HowMuchSkip", Value = search.HowMuchSkip };
var paramHowMuchTake = new MySqlParameter { ParameterName = "p_HowMuchTake", Value = search.HowMuchTake };
var paramSortBy = new MySqlParameter { ParameterName = "p_SortBy", Value = search.SortBy };
var paramSortOrder = new MySqlParameter { ParameterName = "p_SortOrder", Value = search.SortOrder };
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "sp_SearchBooks";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(paramSearchTerms);
cmd.Parameters.Add(paramIncludeTitle);
cmd.Parameters.Add(paramIncludeAuthor);
cmd.Parameters.Add(paramIncludeCategory);
cmd.Parameters.Add(paramIncludeDescription);
cmd.Parameters.Add(paramHowMuchSkip);
cmd.Parameters.Add(paramHowMuchTake);
cmd.Parameters.Add(paramSortBy);
cmd.Parameters.Add(paramSortOrder);
try
{
if (search.HowMuchSkip >= 0)
{
db.Database.Connection.Open();
var reader = cmd.ExecuteReader();
resultsCounter = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<string>(reader).SingleOrDefault();
reader.NextResult();
books = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<Book>(reader).ToList(); //.AsQueryable().Include(b => b.Author)
reader.Close();
var loadedUsers = new List<User>();
foreach (var b in books)
{
var loadedCurrAuthor = loadedUsers.SingleOrDefault(u => b.AuthorId == u.Id);
if (loadedCurrAuthor == null)
loadedUsers.Add(db.Users.Single(u => b.AuthorId == u.Id));
b.Author = loadedUsers.Single(u => b.AuthorId == u.Id);
}
}
}
catch (Exception ex)
{
error = true;
return books; // fallback, zwróć pusty zestaw
}
finally
{
if (db.Database.Connection.State == ConnectionState.Open)
db.Database.Connection.Close();
}
return books;
}
}
public PartialViewResult GetSearchWidget(string controller, string action)
{
var search = new Search();
var dictSearchParams = GetSearchParamsSession();
if (dictSearchParams != null && dictSearchParams.Count > 0)
{
search.SearchTerm = dictSearchParams["SearchTerm".ToLower()] != null ? dictSearchParams["SearchTerm".ToLower()].ToString() : string.Empty;
search.IncludeAuthor = Convert.ToBoolean(dictSearchParams["IncludeAuthor".ToLower()]);
}
ViewBag.Controller = controller;
ViewBag.Action = action;
return PartialView("_SearchWidget", search);
}
public JsonResult GetScrollSearchResults(Search search, string scrollDirection)
{
if (!ModelState.IsValid || !new[] {"scrollup", "scrolldown"}.Contains(scrollDirection.ToLower()))
throw new Exception(
"Model dla 'search' lub Kierunek sortowania przekazany przez Ajax jest nieprawidłowy");
var sessSearch = new Search((Dictionary<string, object>) Session["SearchParams"]);
var expectedSearch = new Search(search) {HowMuchSkip = sessSearch.HowMuchSkip};
if (Session["SearchParams"] == null || !sessSearch.Equals(expectedSearch))
{
//throw new Exception("Sesja jest pusta lub nieprawidłowa"); // fallback
return Json(new
{
ResultsCount = -2, // Sesja jest pusta lub nieprawidłowa
PartialView = string.Empty
}, JsonRequestBehavior.AllowGet);
}
var totalSkip = search.HowMuchSkip + sessSearch.HowMuchSkip;
search.HowMuchSkip = totalSkip;
var searchWithoutInvertedValues = new Search(search);
switch (scrollDirection.ToLower())
{
case "scrolldown":
totalSkip = search.HowMuchSkip + search.HowMuchTake - 2;
search.HowMuchSkip = totalSkip;
break;
case "scrollup":
totalSkip = search.HowMuchSkip; // dummy, do usunięcia
search.HowMuchSkip = totalSkip;
break;
default:
throw new Exception("Niepoprawny kierunek przewijania. ");
}
search.HowMuchTake = 2;
bool error;
string resultsCounter;
var books = GetBooks(search, out resultsCounter, out error);
if (books.Count <= 0)
return Json(new
{
ResultsCount = error ? -1 : books.Count,
ResultsCounter = resultsCounter,
PartialView = string.Empty
}, JsonRequestBehavior.AllowGet);
SaveSearchParamsSession(searchWithoutInvertedValues.ToDictionary());
// parsować do liczb if scrolldown dodac z dolu scrollup z gory
resultsCounter = resultsCounter.Trim().Replace(" ", string.Empty);
// obsługuję resultsCounter tylko jeżeli są wyniki
var parsedFrom = Convert.ToInt32(resultsCounter.Substring(0, resultsCounter.IndexOf('-')));
if (scrollDirection.ToLower() == "scrolldown")
parsedFrom -= 10;
var parsedTo =
Convert.ToInt32(resultsCounter.Substring(resultsCounter.LastIndexOf('-') + 1,
resultsCounter.IndexOf('z') - resultsCounter.LastIndexOf('-') - 1));
if (scrollDirection.ToLower() == "scrollup")
parsedTo += 10;
var parsedTotal =
Convert.ToInt32(resultsCounter.Substring(resultsCounter.LastIndexOf('z') + 1,
resultsCounter.LastIndexOf('(') - resultsCounter.LastIndexOf('z') - 1));
var parsedCount =
Convert.ToInt32(resultsCounter.Substring(resultsCounter.LastIndexOf('(') + 1,
resultsCounter.LastIndexOf(')') - resultsCounter.LastIndexOf('(') - 1)) + 10;
//if (parsedCount < parsedTo - parsedFrom) // obsłużone w bazie danych
// parsedTo = parsedFrom + parsedCount;
resultsCounter = parsedFrom + " - " + parsedTo + " z " + parsedTotal + " (" + parsedCount + ")";
return Json(new
{
ResultsCount = books.Count,
ResultsCounter = resultsCounter,
PartialView = RenderPartialView("_SearchResults", books)
}, JsonRequestBehavior.AllowGet);
}
public ActionResult Index(Search search)
{
if (!ModelState.IsValid)
throw new Exception("Model dla 'search' jest nieprawidłowy");
var requestType = System.Web.HttpContext.Current.Request.HttpMethod;
var searchOptions = new Search {HowMuchTake = 12};
var dictDefaultSearchOptions = searchOptions.ToDictionary();
var dictSessionSearchOptions = GetSearchParamsSession();
var dictPostedSearchOptions = new Dictionary<string, object>();
if (requestType == "POST")
{
dictPostedSearchOptions.Add("searchTerm", search.SearchTerm);
dictPostedSearchOptions.Add("includeAuthor", search.IncludeAuthor);
}
var dictMergedSearchOptions = dictDefaultSearchOptions;
if (dictSessionSearchOptions != null && requestType == "GET")
dictMergedSearchOptions = MergeDictonaries(dictMergedSearchOptions, dictSessionSearchOptions);
if (requestType == "POST")
dictMergedSearchOptions = MergeDictonaries(dictMergedSearchOptions, dictPostedSearchOptions);
SaveSearchParamsSession(dictMergedSearchOptions);
return View();
}
public JsonResult GetSearchResults(Search search)
{
if (!ModelState.IsValid)
throw new Exception("Model dla 'search' jest nieprawidłowy");
bool error;
string resultsCounter;
var books = GetBooks(search, out resultsCounter, out error);
SaveSearchParamsSession(search.ToDictionary());
if (books.Count <= 0)
{
return Json(new
{
ResultsCount = error ? -1 : books.Count,
ResultsCounter = resultsCounter,
PartialView = string.Empty
}, JsonRequestBehavior.AllowGet);
}
return Json(new
{
ResultsCount = books.Count,
ResultsCounter = resultsCounter,
PartialView = RenderPartialView("_SearchResults", books)
}, JsonRequestBehavior.AllowGet);
}
public PartialViewResult GetSearchOptions(string controller, string action)
{
var search = new Search {HowMuchTake = 12};
var dictSearchParams = GetSearchParamsSession();
if (dictSearchParams != null && dictSearchParams.Count > 0)
{
search = new Search(dictSearchParams);
}
ViewBag.Controller = controller;
ViewBag.Action = action;
return PartialView("_SearchOptions", search);
}