public JsonResult GetGridData(Models.SearchBookStatusArg arg)
{
Models.BookMangementService bookMangement = new Models.BookMangementService();
List <Models.SearchBookStatusResult> GridData = bookMangement.GetSearchResultByCondition(arg);
return(Json(GridData));
}
/// <summary>
/// 依照條件從資料庫取得書籍狀態資料
/// </summary>
/// <param name="arg"></param>
/// <returns></returns>
public List <Models.SearchBookStatusResult> GetSearchResultByCondition(Models.SearchBookStatusArg arg)
{ //**主體資料表BOOK_DATA, BOOK_CLASS, BOOK_CODE，用INNER JOIN 而 MEMBER_M 是依附的資料表，所以用LEFT JOIN，MEMBER_M是依附的資料表的原因是，可以看到如果書籍狀態是'可以借出'，借閱人的欄位是空的，That's，就算沒有借閱人資料還是要顯示出來
DataTable dt = new DataTable();
string sql = @"SELECT bd.BOOK_ID AS BookId,bc.BOOK_CLASS_NAME AS 圖書類別,bd.BOOK_NAME AS 書名,CONVERT(VARCHAR(10),bd.BOOK_BOUGHT_DATE,111) AS 購書日期,
bc1.CODE_NAME AS 借閱狀態,ISNULL(mm.USER_ENAME,'')AS 借閱人
FROM BOOK_DATA bd
INNER JOIN BOOK_CLASS bc ON bd.BOOK_CLASS_ID = bc.BOOK_CLASS_ID
LEFT JOIN MEMBER_M mm ON mm.USER_ID = bd.BOOK_KEEPER
INNER JOIN BOOK_CODE bc1 ON bc1.CODE_ID=bd.BOOK_STATUS AND bc1.CODE_TYPE='BOOK_STATUS'
WHERE (bd.BOOK_NAME LIKE '%' + @BOOK_NAME + '%' OR @BOOK_NAME='') AND
(bc.BOOK_CLASS_ID = @BOOK_CLASS_ID OR @BOOK_CLASS_ID ='') AND
(mm.USER_ID = @USER_ID OR @USER_ID = '') AND
(bc1.CODE_ID = @CODE_ID OR @CODE_ID = '')
ORDER BY 購書日期";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BOOK_NAME", arg.BookName == null ? string.Empty : arg.BookName));
cmd.Parameters.Add(new SqlParameter("@BOOK_CLASS_ID", arg.BookClass == null ? string.Empty : arg.BookClass));
cmd.Parameters.Add(new SqlParameter("@USER_ID", arg.BookKeeperID == null ? string.Empty : arg.BookKeeperID));
cmd.Parameters.Add(new SqlParameter("@CODE_ID", arg.BookStatus == null ? string.Empty : arg.BookStatus));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
return(this.MapSearchBookStatusToList(dt));
}