public JsonResult BookNameAutoComplete(BookSystem.Model.BookSearchArg arg)
{
List <BookSystem.Model.BookSearchArg> searchResult = bookService.GetBookByCondtioin(arg);
List <string> bookNameData = new List <string>();
foreach (var value in searchResult)
{
bookNameData.Add(value.BOOK_NAME);
}
return(Json(bookNameData));
}
/// <summary>
/// 依照條件取得書籍資料(搜尋)
/// </summary>
public List <BookSystem.Model.BookSearchArg> GetBookByCondtioin(BookSystem.Model.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"select
BD.BOOK_ID as BookID,
BC.BOOK_CLASS_NAME as BookClassName,
BC.BOOK_CLASS_ID as BookClassID,
BD.BOOK_NAME as BookName,
CONVERT(varchar(12),BD.BOOK_BOUGHT_DATE, 23) as BoughtDate,
BD.BOOK_STATUS as BookStatus,
BCD.CODE_NAME as StatusName,
M.USER_ENAME as UserName,
M.USER_ID as UserID,
M.USER_CNAME as UserCName
FROM BOOK_DATA AS BD
LEFT JOIN MEMBER_M AS M ON BD.BOOK_KEEPER = M.USER_ID
INNER JOIN BOOK_CLASS AS BC ON BD.BOOK_CLASS_ID = BC.BOOK_CLASS_ID
INNER JOIN BOOK_CODE AS BCD ON BD.BOOK_STATUS=BCD.CODE_ID
where BCD.CODE_TYPE='BOOK_STATUS' AND
(UPPER(BD.BOOK_NAME) LIKE UPPER('%' + @BOOK_NAME + '%')or @BOOK_NAME='') AND
(BC.BOOK_CLASS_ID = @BOOK_CLASS_ID OR @BOOK_CLASS_ID='')AND
(M.USER_ID = @USER_ID OR @USER_ID='')AND
(BD.BOOK_STATUS = @BOOK_STATUS OR @BOOK_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("@BOOK_NAME", arg.BOOK_NAME == null ? string.Empty : arg.BOOK_NAME));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", arg.BOOK_CLASS_ID == null ? string.Empty : arg.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@USER_ID", arg.USER_ID == null ? string.Empty : arg.USER_ID));
cmd.Parameters.Add(new SqlParameter("@BOOK_STATUS", arg.BOOK_STATUS == null ? string.Empty : arg.BOOK_STATUS));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
}
return(this.MapBookDataToList(dt));
}
public JsonResult SearchBook(BookSystem.Model.BookSearchArg arg)
{
List <BookSystem.Model.BookSearchArg> searchResult = bookService.GetBookByCondtioin(arg);
return(Json(searchResult));
}
public List <BookSystem.Model.BookSearchArg> GetBookByCondtioin(BookSystem.Model.BookSearchArg arg)
{
return(bookDao.GetBookByCondtioin(arg));
}
