public ActionResult SearchBook(Models.SearchBook book)
{
Models.BookService library = new Models.BookService();
ViewBag.bookClassData = this.codeService.GetBookClassDropdownlist();
ViewBag.borrowerData = this.codeService.GetBorrowerDropdownlist();
ViewBag.statusData = this.codeService.GetBookStatusDropdownlist();
ViewBag.searchResult = library.GetBookByData(book);
return(View());
}
/// <summary>
/// 根據資料查找書籍
/// </summary>
public List <Models.Book> GetBookByData(Models.SearchBook searchBook)
{
DataTable dt = new DataTable();
string sql = @"SELECT
CLASS.BOOK_CLASS_NAME as TypeName,
DATA.BOOK_NAME as Name,
CONVERT(VARCHAR(10),DATA.BOOK_BOUGHT_DATE,111)AS BoughtDate,
DATA.BOOK_STATUS as Status,
CODE.CODE_NAME as StatusName,
(MEM.USER_CNAME+'-'+MEM.USER_ENAME)AS Borrower ,
data.BOOK_ID AS Id
FROM BOOK_DATA AS data
INNER JOIN BOOK_CLASS AS CLASS
ON DATA.BOOK_CLASS_ID=CLASS.BOOK_CLASS_ID
LEFT JOIN MEMBER_M AS MEM
ON DATA.BOOK_KEEPER=MEM.USER_ID
INNER JOIN BOOK_CODE AS CODE
ON DATA.BOOK_STATUS=CODE.CODE_ID AND CODE.CODE_TYPE='BOOK_STATUS'
WHERE
(DATA.BOOK_CLASS_ID=@classID OR @classID='') AND
(UPPER (DATA.BOOK_NAME) LIKE UPPER('%'+@name+'%') or @name='') AND
(DATA.BOOK_KEEPER=@borrower OR @borrower='') AND
(DATA.BOOK_STATUS = @status OR @status='')
ORDER BY BoughtDate DESC";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@classID", searchBook.book_TypeName == null ? string.Empty : searchBook.book_TypeName));
cmd.Parameters.Add(new SqlParameter("@name", searchBook.book_Name == null ? string.Empty : searchBook.book_Name));
cmd.Parameters.Add(new SqlParameter("@borrower", searchBook.book_Borrower == null ? string.Empty : searchBook.book_Borrower));
cmd.Parameters.Add(new SqlParameter("@status", searchBook.book_Status == null ? string.Empty : searchBook.book_Status));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookByData(dt));
}
