/// 搜尋輸入的條件取得Book Data
public List <Model.BookData> GetBookDataWithConditions(Model.SearchArg arg)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
string sql = @"SELECT bd.BOOK_ID AS BOOK_ID, bd.BOOK_NAME AS BOOK_NAME, bcl.BOOK_CLASS_NAME AS BOOK_CLASS_ID,
bd.BOOK_AUTHOR AS BOOK_AUTHOR, bd.BOOK_BOUGHT_DATE AS BOOK_BOUGHT_DATE, bd.BOOK_PUBLISHER AS BOOK_PUBLISHER,
bd.BOOK_NOTE AS BOOK_NOTE, bco.CODE_NAME AS BOOK_STATUS, m.USER_ENAME+'('+m.USER_CNAME+')' AS BOOK_KEEPER,
bd.CREATE_DATE AS CREATE_DATE, bd.CREATE_USER AS CREATE_USER, bd.MODIFY_DATE AS MODIFY_DATE,
bd.MODIFY_USER AS MODIFY_USER
FROM BOOK_DATA AS bd
INNER JOIN BOOK_CLASS AS bcl ON bd.BOOK_CLASS_ID = bcl.BOOK_CLASS_ID
INNER JOIN BOOK_CODE AS bco ON bd.BOOK_STATUS = bco.CODE_ID
LEFT JOIN MEMBER_M AS m ON bd.BOOK_KEEPER = m.USER_ID
WHERE ( UPPER(bd.BOOK_NAME) LIKE UPPER(@bookName) or @bookName='') AND
(bd.BOOK_CLASS_ID = @bookClass or @bookClass='') AND
(bd.BOOK_KEEPER = @bookKeeper or @bookKeeper='') AND
(bd.BOOK_STATUS = @bookStatus or @bookStatus='')
ORDER BY CREATE_DATE DESC
";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@bookName", arg.BOOK_NAME == null ? string.Empty : "%" + arg.BOOK_NAME + "%"));
cmd.Parameters.Add(new SqlParameter("@bookClass", arg.BOOK_CLASS_ID == null ? string.Empty : arg.BOOK_CLASS_ID));
cmd.Parameters.Add(new SqlParameter("@bookKeeper", arg.BOOK_KEEPER == null ? string.Empty : arg.BOOK_KEEPER));
cmd.Parameters.Add(new SqlParameter("@bookStatus", arg.BOOK_STATUS == null ? string.Empty : arg.BOOK_STATUS));
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dt);
conn.Close();
return(MapBookDataToList(dt));
}
}
public JsonResult SearchBook(Model.SearchArg arg)
{
Service.BookDataService bookDataService = new Service.BookDataService();
return(Json(bookDataService.GetBookDataWithConditions(arg)));
}
/// 搜尋輸入的條件取得Book Data
public List <Model.BookData> GetBookDataWithConditions(Model.SearchArg arg)
{
Dao.IBookDataDao bookDataDao = new Dao.BookDataDao();
return(bookDataDao.GetBookDataWithConditions(arg));
}
