/// <summary>
/// 取得書籍資訊
/// </summary>
/// <returns></returns>
public List <Models.Books> GetBooks(BooksSearchArg arg)
{
DataTable booksTable = new DataTable();
string cmdText =
@"SELECT b.BOOK_ID, b.BOOK_NAME, b.BOOK_CLASS_ID,
bClass.BOOK_CLASS_NAME, b.BOOK_AUTHOR,
CONVERT( varchar(12), b.BOOK_BOUGHT_DATE, 23) BOOK_BOUGHT_DATE,
b.BOOK_PUBLISHER, b.BOOK_NOTE, b.BOOK_STATUS,
bCode.CODE_NAME, b.BOOK_KEEPER,
m.USER_CNAME,
m.USER_ENAME
FROM BOOK_DATA b
LEFT JOIN BOOK_CLASS bClass
ON b.BOOK_CLASS_ID=bClass.BOOK_CLASS_ID
LEFT JOIN BOOK_CODE bCode
ON b.BOOK_STATUS=bCode.CODE_ID
AND bCode.CODE_TYPE='BOOK_STATUS'
LEFT JOIN MEMBER_M m
ON b.BOOK_KEEPER=m.[USER_ID]
WHERE (b.BOOK_ID=@BOOK_ID or @BOOK_ID=0) AND
(UPPER(b.BOOK_NAME) LIKE '%'+UPPER(@BOOK_NAME)+'%' or @BOOK_NAME='') AND
(b.BOOK_CLASS_ID=@BOOK_CLASS_ID or @BOOK_CLASS_ID='') AND
(b.BOOK_KEEPER=@BOOK_KEEPER or @BOOK_KEEPER='') AND
(b.BOOK_STATUS=@BOOK_STATUS or @BOOK_STATUS='')
Order By b.BOOK_BOUGHT_DATE DESC, BOOK_ID";
using (SqlConnection conn = new SqlConnection(GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.Parameters.AddWithValue("@BOOK_ID", arg.BookId);
cmd.Parameters.AddWithValue("@BOOK_NAME", arg.BookName ?? string.Empty);
cmd.Parameters.AddWithValue("@BOOK_CLASS_ID", arg.BookClassId ?? string.Empty);
cmd.Parameters.AddWithValue("@BOOK_KEEPER", arg.KeeperId ?? string.Empty);
cmd.Parameters.AddWithValue("@BOOK_STATUS", arg.BookStatusCode ?? string.Empty);
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(booksTable);
conn.Close();
}
return(MapBooksDataToList(booksTable));
}
/// <summary>
/// 取得書籍資訊
/// </summary>
/// <returns></returns>
public List <BOOK_DATA> GetBooks(BooksSearchArg arg)
{
try
{
return(db.BOOK_DATA
.Include(b => b.MEMBER_M)
.Include(b => b.BOOK_CLASS)
.Include(b => b.BOOK_CODE)
.Where(b =>
(b.BOOK_ID == arg.BookId || arg.BookId == 0) &&
(b.BOOK_NAME.ToUpper().Contains(arg.BookName.ToUpper() ?? string.Empty) || (arg.BookName ?? string.Empty) == "") &&
(b.BOOK_CLASS_ID == (arg.BookClassId ?? string.Empty) || (arg.BookClassId ?? string.Empty) == "") &&
(b.BOOK_KEEPER == (arg.KeeperId ?? string.Empty) || (arg.KeeperId ?? string.Empty) == "") &&
(b.BOOK_STATUS == (arg.BookStatusCode ?? string.Empty) || (arg.BookStatusCode ?? string.Empty) == "")
)
.OrderByDescending(b => b.BOOK_BOUGHT_DATE)
.ThenBy(b => b.BOOK_ID)
.ToList());
}
catch (Exception ex)
{
return(new List <BOOK_DATA>());
}
}
