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)); }