public String Test(String[] arrayIn, String[] arrayNotIn, String nameComic, String status, String author,
String nation, String page, String sort)
{
List <String> listIn = new List <string>();
List <String> listNotIn = new List <string>();
if (arrayIn != null)
{
listIn = arrayIn.ToList();
}
if (arrayNotIn != null)
{
listNotIn = arrayNotIn.ToList();
}
SuperSearch superSearch = new SuperSearch(listIn, listNotIn, Convert.ToInt32(nation),
Convert.ToInt32(status),
nameComic, author);
ComicDAO comicDao = new ComicDAO();
Pagination pagination = new Pagination(16, Convert.ToInt32(page));
PaginationComic paginationComic = comicDao.SearchAdvanced(superSearch, pagination, sort);
String json = JsonConvert.SerializeObject(paginationComic, Formatting.Indented);
return(json);
}
public ActionResult SearchAdvanced(String[] arrayIn, String[] arrayNotIn, String nameComic, String status,
String author, String nation, String sort)
{
List <String> listIn = new List <string>();
List <String> listNotIn = new List <string>();
if (arrayIn != null)
{
listIn = arrayIn.ToList();
}
if (arrayNotIn != null)
{
listNotIn = arrayNotIn.ToList();
}
SuperSearch superSearch = new SuperSearch(listIn, listNotIn, Convert.ToInt32(nation),
Convert.ToInt32(status),
nameComic, author);
ComicDAO comicDao = new ComicDAO();
CategoryDAO categoryDao = new CategoryDAO();
StatusComicDAO statusComicDao = new StatusComicDAO();
NationDAO nationDao = new NationDAO();
var comics = comicDao.SearchAdvanced(superSearch, new Pagination(16, Convert.ToInt32(1)), sort);
ViewBag.Comics = comics.Comics;
ViewBag.Numpage = comics.PageSize;
ViewBag.Page = comics.Page;
ViewBag.Search = nameComic;
ViewBag.Categorys = categoryDao.List().OrderBy(category => category.CategoryId);
ViewBag.Status = statusComicDao.ListUser();
ViewBag.Nations = nationDao.List();
ViewBag.ListInAc = listIn;
ViewBag.ListNotInAc = listNotIn;
ViewBag.AuthorAc = author;
ViewBag.SortAc = sort;
ViewBag.StatusAc = status;
ViewBag.NationAc = nation;
return(View());
}
public PaginationComic SearchAdvanced(SuperSearch search, Pagination pagination, String sort)
{
char c = ',';
String strIn = "''";
String strNotIn = "''";
String sqlJoin;
int countListIn = search.ListIn.Count;
int countListNotIn = search.ListNotIn.Count;
if (countListIn != 0)
{
strIn = search.ListIn.Aggregate((s, s1) => s + c + s1);
}
if (countListNotIn != 0)
{
strNotIn = search.ListNotIn.Aggregate((s, s1) => s + c + s1);
}
if (countListIn > 0)
{
sqlJoin = String.Format(
"join (select ComicId from comic_category where CategoryId in ({0}) group by ComicId having COUNT(CategoryId) = {1} EXCEPT select ComicId from comic_category where CategoryId in ({2}) group by ComicId having COUNT(CategoryId) = {3}) t1 on comic.ComicId = t1.ComicId",
strIn, countListIn,
strNotIn, countListNotIn
);
}
else
{
sqlJoin = String.Format(
" left join (select ComicId from comic_category where CategoryId in ({0}) group by ComicId having COUNT(CategoryId) = {1}) t1 on comic.ComicId = t1.ComicId",
strNotIn, countListNotIn
);
}
if (countListIn == 0 && countListNotIn == 0)
{
sqlJoin = "";
}
String sql = String.Format(
" select * from comic {0} where StatusComicId < 4 ",
sqlJoin
);
if (search.StatusId > 0)
{
sql = sql + String.Format(" AND StatusComicId = {0} ", search.StatusId);
}
if (search.NationId > 0)
{
sql = sql + String.Format(" AND NationId = {0} ", search.NationId);
}
if (search.NameComic != null && !search.NameComic.Trim().Equals(""))
{
sql = sql + String.Format(" AND NameComic LIKE N'%{0}%'", search.NameComic);
}
if (search.AuthorComic != null && !search.AuthorComic.Trim().Equals(""))
{
sql = sql + String.Format(" AND NameComic LIKE N'%{0}%'", search.AuthorComic);
}
var comics = WcDbContext.comics.SqlQuery(sql).OrderBy(comic => comic.NameComic);
switch (Convert.ToInt32(sort))
{
case 1:
{
comics = comics.OrderBy(comic => comic.NameComic);
break;
}
case 2:
{
comics = comics.OrderByDescending(comic => comic.NameComic);
break;
}
case 3:
{
comics = comics.OrderBy(comic => comic.UpdateAt);
break;
}
case 4:
{
comics = comics.OrderBy(comic => comic.ReleaseDate);
break;
}
}
var list = ListPage(pagination, comics);
return(list);
}
