/// <summary>
/// Find list of book
/// </summary>
/// <param name="inputquery">Search query</param>
/// <returns>List of book with field similar with inputQuery</returns>
public List <Book> Find(string inputquery)
{
if (string.IsNullOrEmpty(inputquery.Trim()))
{
throw new NotImplementedException();
}
Queue <object> parameters = new Queue <object>();
parameters.Enqueue(new OleDbParameter("@inputQuery", inputquery.Trim()));
string query = "SELECT tblBook.ID, tblBook.Title, tblBook.Author, tblBook.Publisher, tblBook.PublishedDate FROM tblBook WHERE tblBook.ID like '%'+@inputQuery+'%' or tblBook.Title like '%'+@inputQuery+'%' or tblBook.Author like '%'+@inputQuery+'%' or tblBook.Publisher like '%'+@inputQuery+'%' or tblBook.PublishedDate like '%'+@inputQuery+'%';";
DataSet result = DBHelper.ExecuteReturnedQuery(query, parameters);
List <Book> resultBook = new List <Book>();
foreach (DataRow dataRow in result.Tables[0].Rows)
{
resultBook.Add(new Book(
Convert.ToInt32(dataRow[0].ToString()),
dataRow[1].ToString(),
dataRow[2].ToString(),
dataRow[3].ToString(),
DateTime.Parse(dataRow[4].ToString())
));
}
return(resultBook);
}
public int CountUsername(string username)
{
if (string.IsNullOrEmpty(username.Trim()))
{
throw new NotImplementedException();
}
Queue <object> parameters = new Queue <object>();
parameters.Enqueue(new OleDbParameter("@Username", username));
string query = "SELECT Count(*) from tblAccount WHERE tblAccount.Username = @Username";
DataRow result = DBHelper.ExecuteReturnedQuery(query, parameters).Tables[0].Rows[0];
return(Convert.ToInt32(result[0].ToString()));
}
public List <Account> BookHistory(int bookId)
{
if (bookId <= 0)
{
throw new NotImplementedException();
}
string query = "select tblAccount.ID, tblAccount.Fullname, tblAccount.Address, tblAccount.PhoneNumber, tblAccount.Opened, tblAccount.Level from ( tblBorrow inner join tblBook on tblBorrow.BookId = tblBook.ID) inner join tblAccount on tblBorrow.AccountId = tblAccount.ID where tblBook.ID = @ID; ";
Queue <object> parameters = new Queue <object>();
parameters.Enqueue(new SqlParameter("@ID", bookId));
DataSet result = DBHelper.ExecuteReturnedQuery(query, parameters);
List <Account> resultAccount = new List <Account>();
foreach (DataRow dataRow in result.Tables[0].Rows)
{
resultAccount.Add(new Account(
Convert.ToInt32(dataRow[0].ToString()),
dataRow[1].ToString(),
dataRow[2].ToString(),
dataRow[3].ToString(),
DateTime.Parse(dataRow[4].ToString()),
(AccountLevelEnums)Convert.ToInt32(dataRow[5].ToString())
));
}
return(resultAccount);
}
public List <BorrowReport> GetReport()
{
string query = "SELECT tblBorrow.Borrowed , tblBorrow.Fine FROM (tblBorrow INNER JOIN tblBook ON tblBorrow.BookId = tblBook.ID) INNER JOIN tblAccount ON tblBorrow.AccountId = tblAccount.ID";
Queue <object> parameters = new Queue <object>();
DataSet result = DBHelper.ExecuteReturnedQuery(query, parameters);
List <BorrowReport> resultReport = new List <BorrowReport>();
foreach (DataRow dataRow in result.Tables[0].Rows)
{
DateTime month = Convert.ToDateTime(dataRow[0].ToString());
decimal fine = string.IsNullOrEmpty(dataRow[1].ToString()) ? 0 : Convert.ToDecimal(dataRow[1].ToString());
resultReport.Add(new BorrowReport(month, fine));
}
return(resultReport);
}
