//search
public List <Book_Maintain_Ajax.Model.BookSearch> SearchBook(Book_Maintain_Ajax.Model.BookSearch searchdata)
{
var sql = @"SELECT bd.BOOK_ID AS BookID,bc.BOOK_CLASS_NAME AS BookClassId,bd.BOOK_NAME AS BookName, CONVERT(varchar, bd.BOOK_BOUGHT_DATE, 111) AS BoughtDate, bcd.CODE_NAME AS BookStatusId, mm.USER_ENAME AS BookKeeperId
FROM BOOK_DATA bd
INNER JOIN BOOK_CLASS bc
ON bd.BOOK_CLASS_ID = bc.BOOK_CLASS_ID
INNER JOIN BOOK_CODE bcd
ON bd.BOOK_STATUS = bcd.CODE_ID AND bcd.CODE_TYPE = 'BOOK_STATUS'
LEFT OUTER JOIN MEMBER_M mm
ON bd.BOOK_KEEPER = mm.[USER_ID]
WHERE(bd.BOOK_CLASS_ID = @BookClassId OR @BookClassId = '') AND
(LOWER(bd.BOOK_NAME) LIKE ('%'+LOWER(@BookName)+'%') OR @BookName = '') AND
(bcd.CODE_ID = @BookStatusId OR @BookStatusId = '') AND
(mm.[USER_ID] = @BookKeeperId OR @BookKeeperId = '')
ORDER BY bd.CREATE_DATE DESC" ;
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
var SearchList = conn.Query <Book_Maintain_Ajax.Model.BookSearch>(sql, new
{
BookClassId = searchdata.BookClassId == null ? string.Empty : searchdata.BookClassId,
BookName = searchdata.BookName == null ? string.Empty : searchdata.BookName,
BookStatusId = searchdata.BookStatusId == null ? string.Empty : searchdata.BookStatusId,
BookKeeperId = searchdata.BookKeeperId == null ? string.Empty : searchdata.BookKeeperId
}).ToList();
return(SearchList);
}
}
public List <Book_Maintain_Ajax.Model.BookSearch> SearchBook(Book_Maintain_Ajax.Model.BookSearch searchdata)
{
return(bookDao.SearchBook(searchdata));
}
public JsonResult GetGrid(Book_Maintain_Ajax.Model.BookSearch booksearch)
{
List <Book_Maintain_Ajax.Model.BookSearch> Grid = bookService.SearchBook(booksearch);
return(Json(Grid, JsonRequestBehavior.AllowGet)); //回傳json需要JsonRequestBehavior.AllowGet以免危險
}
