public JsonResult Index(eBook.Model.BookSearchArg arg)
{
///ViewBag.SearchResult = bookService.GetBookByCondtioin(arg);回傳查詢結果
///回傳JSON格式的查詢結果
var json = new JavaScriptSerializer();
var jsonArray = json.Serialize(bookService.GetBookByCondtioin(arg));
return(Json(jsonArray));
}
/// <summary>
/// 依照條件取得書籍資料
/// </summary>
/// <returns></returns>
public List <eBook.Model.Book> GetBookByCondtioin(eBook.Model.BookSearchArg arg)
{
DataTable dt = new DataTable();
string sql = @"SELECT a.BOOK_ID , a.BOOK_NAME
,a.BOOK_CLASS_ID , b.BOOK_CLASS_NAME as CLASS_NAME
,a.BOOK_AUTHOR , CONVERT( varchar(12), a.BOOK_BOUGHT_DATE, 111) as BOOK_BOUGHT_DATE
,a.BOOK_PUBLISHER , a.BOOK_NOTE
,a.BOOK_STATUS , c.CODE_TYPE , c.CODE_NAME as STATUS_NAME
,a.BOOK_KEEPER , case(a.BOOK_STATUS)
WHEN 'A' THEN ' '
WHEN 'U' THEN ' '
ELSE d.USER_ENAME + '(' + d.USER_CNAME + ')'
END as USER_NAME
,a.CREATE_DATE , a.CREATE_USER
,a.MODIFY_DATE , a.MODIFY_USER
FROM BOOK_DATA as a
Left JOIN BOOK_CLASS as b
ON a.BOOK_CLASS_ID = b.BOOK_CLASS_ID
Left JOIN BOOK_CODE as c
ON a.BOOK_STATUS = c.CODE_ID
Left JOIN MEMBER_M as d
ON a.BOOK_KEEPER = d.USER_ID
WHERE (a.BOOK_NAME LIKE '%' + @BookName + '%' ) AND
(a.BOOK_CLASS_ID = @BookClassId OR @BookClassId = '' ) AND
(a.BOOK_KEEPER = @BookKeeper OR @BookKeeper = '' ) AND
(a.BOOK_STATUS = @BookStatus OR @BookStatus = '' ) AND
(c.CODE_TYPE = 'BOOK_STATUS')
ORDER BY BOOK_BOUGHT_DATE DESC
";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BookName == null ? string.Empty : arg.BookName));
cmd.Parameters.Add(new SqlParameter("@BookClassId", arg.BookClassId == null ? string.Empty : arg.BookClassId));
cmd.Parameters.Add(new SqlParameter("@BookKeeper", arg.BookKeeper == null ? string.Empty : arg.BookKeeper));
cmd.Parameters.Add(new SqlParameter("@BookStatus", arg.BookStatus == null ? string.Empty : arg.BookStatus));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapBookDataToList(dt));
}
public List <eBook.Model.Book> GetBookByCondtioin(eBook.Model.BookSearchArg arg)
{
return(bookDao.GetBookByCondtioin(arg));
}
