public void TestGetAllBooks()
{
///Arrange
BookDao bookDao = new BookDao();
BookSearchArg arg = new BookSearchArg();
///Act
List <Book> books = bookDao.GetBookByCondtioin(arg);
///Assert
Assert.AreEqual(2305, books.Count);
}
public void TestGetClassBKBooks()
{
///Arrange
BookDao bookDao = new BookDao();
BookSearchArg arg = new BookSearchArg()
{
BookClassId = "BK"
};
///Act
List <Book> books = bookDao.GetBookByCondtioin(arg);
///Assert
Assert.AreEqual(145, books.Count);
}
public void Initializa()
{
test = new BookDao();
iBook = new Books()
{
//BOOK_ID = 0,
BookName = "ibook",
BookAuthor = "ia",
BookPublisher = "ip",
BookNote = "insert_test",
BookBoughtDate = "2018-08-01",
BookClassId = "BK"
};
argI = new BookSearchArg()
{
BookName = "ibook",
BookClassId = "BK",
BookKeeper = "",
BookCodeId = "A"
};
argEmpty = new BookSearchArg()
{
BookName = "",
BookClassId = "",
BookKeeper = "",
BookCodeId = ""
};
bookUpdateData = new BookUpdateData()
{
BookName = "ubook",
BookAuthor = "ua",
BookPublisher = "up",
BookNote = "update_test",
BookBoughtDate = "2018-08-01",
BookCodeId = "A",
BookKeeper = "0003"
};
}
/// <summary>
/// 依照條件取得客戶資料
/// </summary>
/// <returns></returns>
public List <eLibrary.Model.Books> GetBookByCondition(BookSearchArg arg)
{
//LEFT JOIN dbo.BOOK_CODE as BCD
// ON(BD.BOOK_STATUS = BCD.CODE_ID)
// and BCD.CODE_TYPE = 'STATUS'
DataTable dt = new DataTable();
string sql = @"SELECT BD.BOOK_ID As BookId , BD.BOOK_CLASS_ID As BookClassId, BD.BOOK_NAME As BookName,
CONVERT( varchar(12), BD.BOOK_BOUGHT_DATE, 111) AS BookBoughtDate,
BCD.CODE_NAME AS BookStatusName, M.USER_ENAME AS BookKeeperEName,
M.USER_CNAME As BookKeeperCName, BCL.BOOK_CLASS_NAME As BookClassName
FROM dbo.BOOK_DATA as BD
JOIN dbo.BOOK_CODE as BCD
ON (BD.BOOK_STATUS = BCD.CODE_ID) And BCD.CODE_TYPE = 'BOOK_STATUS'
JOIN dbo.BOOK_CLASS as BCL
ON (BD.BOOK_CLASS_ID = BCL.BOOK_CLASS_ID)
LEFT JOIN dbo.MEMBER_M as M
ON (BD.BOOK_KEEPER = M.USER_ID)
Where (BD.BOOK_CLASS_ID = @BookClassId Or @BookClassId = '') AND
(BD.BOOK_NAME = @BookName Or @BookName = '')AND
(BD.BOOK_STATUS = @BookStatus Or @BookStatus = '')AND
(BD.BOOK_KEEPER = @BookKeeper Or @BookKeeper = '')
ORDER BY BookBoughtDate DESC";
using (SqlConnection conn = new SqlConnection(this.GetDBConnectionString()))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@BookClassId", arg.BookClassId == null ? string.Empty : arg.BookClassId));
cmd.Parameters.Add(new SqlParameter("@BookName", arg.BookName == null ? string.Empty : arg.BookName));
cmd.Parameters.Add(new SqlParameter("@BookKeeper", arg.BookKeeper == null ? string.Empty : arg.BookKeeper));
cmd.Parameters.Add(new SqlParameter("@BookStatus", arg.BookCodeId == null ? string.Empty : arg.BookCodeId));
SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
sqlAdapter.Fill(dt);
conn.Close();
}
//the above are search conditions
return(this.MapBookDataToList(dt));
}
/// <summary>
/// 依照條件取得客戶資料
/// </summary>
/// <returns></returns>
public List <Books> GetBookByCondition(BookSearchArg arg)
{
return(bookDao.GetBookByCondition(arg));
}
public List <Books> GetBookByCondition(BookSearchArg arg)
{
throw new NotImplementedException();
}
