public static Book Find(int bookId)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlDataReader rdr = null;
SqlCommand cmd = new SqlCommand("SELECT * FROM books WHERE id = @BookId;", conn);
SqlParameter bookIdParameter = new SqlParameter();
bookIdParameter.ParameterName = "@BookId";
bookIdParameter.Value = bookId;
cmd.Parameters.Add(bookIdParameter);
int foundBookId = 0;
string foundBookTitle = null;
DateTime? foundBookPublishedDate = null;
int foundBookGenreId = 0;
rdr = cmd.ExecuteReader();
while(rdr.Read())
{
foundBookId = rdr.GetInt32(0);
foundBookTitle = rdr.GetString(1);
foundBookPublishedDate = rdr.GetDateTime(2);
foundBookGenreId = rdr.GetInt32(3);
}
Book foundBook = new Book(foundBookTitle, foundBookPublishedDate, foundBookGenreId, foundBookId);
if(rdr!=null) rdr.Close();
if(conn!=null) conn.Close();
return foundBook;
}
public void Book_Delete_DeletesBookById()
{
Book firstBook = new Book("Cats", testDate, 2);
firstBook.Save();
Book secondBook = new Book("Crime & Punishment", testDate, 3);
secondBook.Save();
firstBook.Delete();
List<Book> expectedResult = new List<Book>{secondBook};
List<Book> actualResult = Book.GetAll();
Assert.Equal(expectedResult, actualResult);
}
public void Book_AddAuthors()
{
//Arrange
Book newBook = new Book("Cats", testDate, 2);
newBook.Save();
Author newAuthor = new Author("Chad");
newAuthor.Save();
//Act
newBook.AddAuthor(newAuthor.GetId());
List<Author> result = newBook.GetAuthors();
List<Author> expectedResult = new List<Author>{newAuthor};
//Assert
Assert.Equal(expectedResult, result);
}
public void Genre_GetBooks_GetsAllBookInGenre()
{
Genre firstGenre = new Genre("Sci-Fi");
firstGenre.Save();
Book firstBook = new Book("Cats", testDate, 2);
firstBook.Save();
Book secondBook = new Book("Crime & Punishment", testDate, firstGenre.GetId());
secondBook.Save();
List<Book> expectedResult = new List<Book> {secondBook};
List<Book> actualResult = firstGenre.GetBooks();
Assert.Equal(expectedResult, actualResult);
}
public void Patron_Checkout_ChecksoutABook()
{
Patron firstPatron = new Patron("Mayor McCheese");
firstPatron.Save();
Book newBook = new Book("Cats", testDate, 2);
newBook.Save();
newBook.StockBook();
firstPatron.CheckoutBook(newBook.GetCopies()[0].GetId(), testDate2);
List<Copy> result = firstPatron.GetCheckOutRecord(false);
List<Copy> expectedResult = newBook.GetCopies();
Assert.Equal(expectedResult, result);
}
public void Author_GetAllBooksByAuthor()
{
//Arrange
Author firstAuthor = new Author("Chad");
firstAuthor.Save();
Book firstBook = new Book("Cats", testDate, 2);
firstBook.Save();
Book secondBook = new Book("Crime & Punishment", testDate, 3);
secondBook.Save();
//Act
firstBook.AddAuthor(firstAuthor.GetId());
List<Book> result = firstAuthor.GetBooks();
List<Book> expectedResult = new List<Book>{firstBook};
//Assert
Assert.Equal(expectedResult, result);
}
public void Book_DeleteAuthor()
{
//Arrange
Book newBook = new Book("Cats", testDate, 2);
newBook.Save();
Author firstAuthor = new Author("Chad");
firstAuthor.Save();
Author secondAuthor = new Author("Todd");
secondAuthor.Save();
newBook.AddAuthor(firstAuthor.GetId());
newBook.AddAuthor(secondAuthor.GetId());
//Act
newBook.DeleteAuthor(firstAuthor.GetId());
List<Author> result = newBook.GetAuthors();
List<Author> expectedResult = new List<Author>{secondAuthor};
//Assert
Assert.Equal(expectedResult, result);
}
public void Copy_GetAllCheckouts_ReturnAllCheckoutInfo()
{
Patron firstPatron = new Patron("Mayor McCheese");
firstPatron.Save();
Book firstBook = new Book("Cats", testDate, 2);
firstBook.Save();
firstBook.StockBook();
Book secondBook = new Book("Dogs", testDate, 2);
secondBook.Save();
secondBook.StockBook();
firstPatron.CheckoutBook(firstBook.GetCopies()[0].GetId(), testDate2);
firstPatron.CheckoutBook(secondBook.GetCopies()[0].GetId(), testDate);
Dictionary<string, object> result = Copy.GetAllCheckouts();
List<Patron> expectedPatrons = new List<Patron>{firstPatron, firstPatron};
List<Copy> expectedCopies = new List<Copy>{firstBook.GetCopies()[0], secondBook.GetCopies()[0]};
List<DateTime?> expectedDueDates = new List<DateTime?>{testDate2, testDate};
Assert.Equal(result["patrons"], expectedPatrons);
Assert.Equal(result["copies"], expectedCopies);
Assert.Equal(result["dueDates"], expectedDueDates);
}
public static List<Book> SearchForBookByTitle(string searchTerm, bool partialMatches)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlDataReader rdr = null;
SqlCommand cmd = new SqlCommand("SELECT * FROM books WHERE title LIKE @SearchTerm;", conn);
SqlParameter searchTermParameter = new SqlParameter();
searchTermParameter.ParameterName = "@SearchTerm";
searchTermParameter.Value = searchTerm;
if(partialMatches)
{
searchTermParameter.Value = "%"+searchTerm+"%";
}
cmd.Parameters.Add(searchTermParameter);
int foundBookId = 0;
string foundBookTitle = null;
DateTime? foundBookPublishedDate = null;
int foundBookGenreId = 0;
List<Book> foundBooks = new List<Book>{};
rdr = cmd.ExecuteReader();
while(rdr.Read())
{
foundBookId = rdr.GetInt32(0);
foundBookTitle = rdr.GetString(1);
foundBookPublishedDate = rdr.GetDateTime(2);
foundBookGenreId = rdr.GetInt32(3);
Book foundBook = new Book(foundBookTitle, foundBookPublishedDate, foundBookGenreId, foundBookId);
foundBooks.Add(foundBook);
}
if(rdr!=null) rdr.Close();
if(conn!=null) conn.Close();
return foundBooks;
}
public void Book_Update_UpdatesBook()
{
Book firstBook = new Book("Cats", testDate, 2);
firstBook.Save();
firstBook.Update("Crime & Punishment", testDate2, 3);
Book resultBook = Book.Find(firstBook.GetId());
Assert.Equal("Crime & Punishment", resultBook.GetTitle());
Assert.Equal(testDate2, resultBook.GetDatePublished());
Assert.Equal(3, resultBook.GetGenreId());
}
public void Book_StockBook_AddsCopiesWhenWeStockBook()
{
Book newBook = new Book("Cathedrals", testDate, 2);
newBook.Save();
newBook.StockBook();
newBook.StockBook();
newBook.StockBook();
int result = newBook.GetCopies().Count;
Assert.Equal(3, result);
}
public void Book_Save_SavesBookToDatabase()
{
Book newBook = new Book("Cats", testDate, 2);
newBook.Save();
List<Book> expectedResult = new List<Book>{newBook};
List<Book> actualResult = Book.GetAll();
Assert.Equal(expectedResult, actualResult);
}
public void Book_GetCheckedOutCopies()
{
Book newBook = new Book("Cathedrals", testDate, 2);
newBook.Save();
Patron firstPatron = new Patron("Mayor McCheese");
firstPatron.Save();
newBook.StockBook();
newBook.StockBook();
newBook.StockBook();
firstPatron.CheckoutBook(newBook.GetCopies()[0].GetId(), testDate2);
firstPatron.CheckoutBook(newBook.GetCopies()[2].GetId(), testDate2);
List<Copy> expectedResult = new List<Copy>{newBook.GetCopies()[0], newBook.GetCopies()[2]};
List<Copy> result = newBook.GetCheckedOutCopies();
Assert.Equal(expectedResult, result);
}
public void Book_FindsBookInDatabase()
{
//Arrange
Book expectedResult = new Book("Crime & Punishment", testDate, 3);
expectedResult.Save();
//Act
Book result = Book.Find(expectedResult.GetId());
//Assert
Assert.Equal(expectedResult, result);
}
public void Book_FindBookByTitlePartialMatch()
{
Book newBook = new Book("Cathedrals", testDate, 2);
newBook.Save();
Book secondBook = new Book("Other Book", testDate, 1);
secondBook.Save();
List<Book> result = Book.SearchForBookByTitle("Cat", true);
List<Book> expectedResult = new List<Book>{newBook};
Assert.Equal(expectedResult, result);
}
public void Book_FindBookByTitle()
{
Book newBook = new Book("Cats", testDate, 2);
newBook.Save();
Book secondBook = new Book("Cathderals", testDate, 1);
secondBook.Save();
List<Book> result = Book.SearchForBookByTitle("Cats", false);
List<Book> expectedResult = new List<Book>{newBook};
Assert.Equal(expectedResult, result);
}
public static List<Book> GetAll()
{
List<Book> allBooks = new List<Book>{};
SqlConnection conn = DB.Connection();
SqlDataReader rdr = null;
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM books;", conn);
rdr = cmd.ExecuteReader();
while(rdr.Read())
{
int bookId = rdr.GetInt32(0);
string bookTitle = rdr.GetString(1);
DateTime? bookPublishDate = rdr.GetDateTime(2);
int bookGenre = rdr.GetInt32(3);
Book newBook = new Book(bookTitle, bookPublishDate, bookGenre, bookId);
allBooks.Add(newBook);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return allBooks;
}
public void Book_FindBookByAuthorFullMatch()
{
Book newBook = new Book("Cathedrals", testDate, 2);
newBook.Save();
Book secondBook = new Book("Other Book", testDate, 1);
secondBook.Save();
Author newAuthor1 = new Author("Chad");
newAuthor1.Save();
Author newAuthor2 = new Author("Chadwick");
newAuthor2.Save();
newBook.AddAuthor(newAuthor1.GetId());
secondBook.AddAuthor(newAuthor2.GetId());
List<Book> result = Book.SearchForBookByAuthor("Chad", false);
List<Book> expectedResult = new List<Book>{newBook};
Assert.Equal(expectedResult, result);
}
public void Patron_GetCheckoutHistory_ReturnsCheckOutHistory()
{
Patron firstPatron = new Patron("Mayor McCheese");
firstPatron.Save();
Book firstBook = new Book("Cats", testDate, 2);
firstBook.Save();
firstBook.StockBook();
Book secondBook = new Book("Dogs", testDate, 2);
secondBook.Save();
secondBook.StockBook();
firstPatron.CheckoutBook(firstBook.GetCopies()[0].GetId(), testDate2);
firstPatron.CheckoutBook(secondBook.GetCopies()[0].GetId(), testDate2);
firstPatron.ReturnBook(firstBook.GetCopies()[0].GetId());
Copy result = firstPatron.GetCheckOutRecord(true)[0];
Copy expectedResult = firstBook.GetCopies()[0];
Assert.Equal(expectedResult, result);
}
public void Patron_ReturnBook_ReturnsCheckedoutBook()
{
Patron firstPatron = new Patron("Mayor McCheese");
firstPatron.Save();
Book newBook = new Book("Cats", testDate, 2);
newBook.Save();
newBook.StockBook();
firstPatron.CheckoutBook(newBook.GetCopies()[0].GetId(), testDate2);
firstPatron.ReturnBook(newBook.GetCopies()[0].GetId());
int result = firstPatron.GetCheckOutRecord(false).Count;
Assert.Equal(0, result);
}
public void Patron_GetDueDate_GetsDueDateOfCheckedOutBook()
{
Patron firstPatron = new Patron("Mayor McCheese");
firstPatron.Save();
Book newBook = new Book("Cats", testDate, 2);
newBook.Save();
newBook.StockBook();
firstPatron.CheckoutBook(newBook.GetCopies()[0].GetId(), testDate2);
DateTime? result = firstPatron.GetReturnDate(newBook.GetCopies()[0].GetId());
DateTime? expectedResult = testDate2;
Assert.Equal(expectedResult, result);
}
public HomeModule()
{
Get["/"] = _ => View["index.cshtml"];
Get["/books"] = _ =>
{
List<Book> allBooks = Book.GetAll();
return View["books.cshtml", allBooks];
};
Post["/results"] = _ =>
{
string searchTerm = Request.Form["search-term"];
bool searchType = Request.Form["search-type"];
bool searchByTitle = Request.Form["title-or-author"];
List<Book> searchResult = new List<Book>{};
if(searchByTitle)
{
searchResult = Book.SearchForBookByTitle(searchTerm, searchType);
}
else
{
searchResult = Book.SearchForBookByAuthor(searchTerm, searchType);
}
Dictionary<string, object> model = new Dictionary<string, object>{};
model.Add("results", searchResult);
model.Add("priorSearchTerm", searchTerm);
model.Add("priorSearchType", searchType);
model.Add("priorSearchBy", searchByTitle);
return View["results.cshtml", model];
};
Get["/books/{id}"] = parameters =>
{
Dictionary<string, object> model = new Dictionary<string, object>{};
List<Author> allAuthors = Author.GetAll();
Book selectedBook = Book.Find(parameters.id);
List<Genre> allGenres = Genre.GetAll();
List<Patron> allPatrons = Patron.GetAll();
model.Add("patrons", allPatrons);
model.Add("genres", allGenres);
model.Add("book", selectedBook);
model.Add("authors", allAuthors);
return View["book.cshtml", model];
};
Get["/books/{id}/stock"]= parameters =>
{
Dictionary<string, object> model = new Dictionary<string, object>{};
List<Author> allAuthors = Author.GetAll();
Book selectedBook = Book.Find(parameters.id);
selectedBook.StockBook();
List<Genre> allGenres = Genre.GetAll();
List<Patron> allPatrons = Patron.GetAll();
model.Add("patrons", allPatrons);
model.Add("genres", allGenres);
model.Add("book", selectedBook);
model.Add("authors", allAuthors);
return View["book.cshtml", model];
};
Post["/books/{id}"] = parameters =>
{
Book selectedBook = Book.Find(parameters.id);
int selectedAuthor = Request.Form["author-name"];
Dictionary<string, object> model = new Dictionary<string, object>{};
selectedBook.AddAuthor(selectedAuthor);
List<Author> allAuthors = Author.GetAll();
List<Genre> allGenres = Genre.GetAll();
List<Patron> allPatrons = Patron.GetAll();
model.Add("patrons", allPatrons);
model.Add("genres", allGenres);
model.Add("book", selectedBook);
model.Add("authors", allAuthors);
return View["book.cshtml", model];
};
Delete["/books/{id}"] = parameters =>
{
Book selectedBook = Book.Find(parameters.id);
int authorToDelete = Request.Form["author-name"];
selectedBook.DeleteAuthor(authorToDelete);
Dictionary<string, object> model = new Dictionary<string, object>{};
List<Author> allAuthors = Author.GetAll();
List<Genre> allGenres = Genre.GetAll();
List<Patron> allPatrons = Patron.GetAll();
model.Add("patrons", allPatrons);
model.Add("genres", allGenres);
model.Add("book", selectedBook);
model.Add("authors", allAuthors);
return View["book.cshtml", model];
};
Patch["/books/{id}"] = parameters =>
{
Book selectedBook = Book.Find(parameters.id);
selectedBook.Update(Request.Form["book-title"], Request.Form["publication-date"], Request.Form["new-genre"]);
Dictionary<string, object> model = new Dictionary<string, object>{};
List<Author> allAuthors = Author.GetAll();
List<Genre> allGenres = Genre.GetAll();
List<Patron> allPatrons = Patron.GetAll();
model.Add("patrons", allPatrons);
model.Add("genres", allGenres);
model.Add("book", selectedBook);
model.Add("authors", allAuthors);
return View["book.cshtml", model];
};
Get["/books/add"] = _ =>
{
Dictionary<string, object> model = new Dictionary<string, object>{};
List<Genre> allGenres = Genre.GetAll();
List<Author> allAuthors = Author.GetAll();
model.Add("genres", allGenres);
model.Add("authors", allAuthors);
return View["book_new.cshtml", model];
};
Post["/books/add"] = _ =>
{
Book newBook = new Book(Request.Form["book-title"], Request.Form["publication-date"], Request.Form["genre"]);
newBook.Save();
int authorId = Request.Form["author"];
newBook.AddAuthor(authorId);
List<Book> allBooks = Book.GetAll();
return View["books.cshtml", allBooks];
};
Get["/authors"] = _ =>
{
List<Author> allAuthors = Author.GetAll();
return View["authors.cshtml", allAuthors];
};
Get["/authors/{id}"] = parameters =>
{
Author selectedAuthor = Author.Find(parameters.id);
return View["author.cshtml", selectedAuthor];
};
Get["/authors/add"] = _ =>View["author_new.cshtml"];
Post["/authors/add"] = _ =>
{
Author newAuthor = new Author(Request.Form["author-name"]);
newAuthor.Save();
List<Author> allAuthors = Author.GetAll();
return View["authors.cshtml", allAuthors];
};
Get["/genres"] = _ =>
{
List<Genre> allGenres = Genre.GetAll();
return View["genres.cshtml", allGenres];
};
Get["/genres/{id}"] = parameters =>
{
Genre selectedGenre = Genre.Find(parameters.id);
return View["genre.cshtml", selectedGenre];
};
Get["/genres/add"] = _ =>View["genre_new.cshtml"];
Post["/genres/add"] = _ =>
{
Genre newGenre = new Genre(Request.Form["genre-name"]);
newGenre.Save();
List<Genre> allGenres = Genre.GetAll();
return View["genres.cshtml", allGenres];
};
Get["/patrons"] = _ =>
{
List<Patron> allPatrons = Patron.GetAll();
return View["patrons.cshtml", allPatrons];
};
Get["/patrons/{id}"] = parameters =>
{
Patron selectedPatron = Patron.Find(parameters.id);
return View["patron.cshtml", selectedPatron];
};
Get["/patrons/new"] = _ => View["patron_new.cshtml"];
Post["/patrons"] = _ =>
{
Patron newPatron = new Patron(Request.Form["name"]);
newPatron.Save();
List<Patron> allPatrons = Patron.GetAll();
return View["patrons.cshtml", allPatrons];
};
Post["/patrons/checkout"] = _ =>
{
Patron selectedPatron = Patron.Find(Request.Form["patron"]);
DateTime today = DateTime.Today;
selectedPatron.CheckoutBook(Request.Form["copy"], today.AddDays(Request.Form["length-of-borrow"]));
return View["patron.cshtml", selectedPatron];
};
Post["/patrons/{id}/return"] = parameters =>
{
Patron selectedPatron = Patron.Find(parameters.id);
int bookId = Request.Form["book"];
selectedPatron.ReturnBook(bookId);
return View["patron.cshtml", selectedPatron];
};
Get["/librarian"] = _ =>
{
//Key: "patrons, copies, dueDates"
Dictionary<string, object> allCheckouts = Copy.GetAllCheckouts();
return View["librarian.cshtml", allCheckouts];
};
}
public void Book_FindBookByAuthorNoDuplicates()
{
Book newBook = new Book("Cathedrals", testDate, 2);
newBook.Save();
Book secondBook = new Book("Other Book", testDate, 1);
secondBook.Save();
Author newAuthor1 = new Author("Meredith Hartley");
newAuthor1.Save();
Author newAuthor2 = new Author("Chadwick Hartley");
newAuthor2.Save();
newBook.AddAuthor(newAuthor1.GetId());
newBook.AddAuthor(newAuthor2.GetId());
List<Book> result = Book.SearchForBookByAuthor("Hartley", true);
List<Book> expectedResult = new List<Book>{newBook};
Assert.Equal(expectedResult, result);
}
public List<Book> GetBooks()
{
SqlConnection conn = DB.Connection();
SqlDataReader rdr = null;
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT books.* FROM authors JOIN books_authors ON (books_authors.author_id = authors.id) JOIN books ON (books_authors.book_id = books.id) WHERE author_id = @AuthorId;", conn);
SqlParameter authorIdParameter = new SqlParameter();
authorIdParameter.ParameterName = "@AuthorId";
authorIdParameter.Value = this.GetId();
cmd.Parameters.Add(authorIdParameter);
rdr=cmd.ExecuteReader();
List<Book> foundBooks = new List<Book>{};
while(rdr.Read())
{
int foundBookId = rdr.GetInt32(0);
string foundBookTitle = rdr.GetString(1);
DateTime? foundBookPublishedDate = rdr.GetDateTime(2);
int foundBookGenreId = rdr.GetInt32(3);
Book foundBook = new Book(foundBookTitle, foundBookPublishedDate, foundBookGenreId, foundBookId);
foundBooks.Add(foundBook);
}
if(rdr!=null) rdr.Close();
if(conn!=null) conn.Close();
return foundBooks;
}
public List<Book> GetBooks()
{
List<Book> allBooks = new List<Book>{};
SqlConnection conn = DB.Connection();
SqlDataReader rdr = null;
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM books WHERE genre_id = @GenreId;", conn);
SqlParameter idParameter = new SqlParameter();
idParameter.ParameterName = "@GenreId";
idParameter.Value = this.GetId();
cmd.Parameters.Add(idParameter);
rdr = cmd.ExecuteReader();
while(rdr.Read())
{
int bookId = rdr.GetInt32(0);
string bookTitle = rdr.GetString(1);
DateTime? bookPublicationDate = rdr.GetDateTime(2);
int bookGenreId = rdr.GetInt32(3);
Book newBook = new Book(bookTitle, bookPublicationDate, bookGenreId, bookId);
allBooks.Add(newBook);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return allBooks;
}
