/// <summary>
/// For the specified book, populate the Details section and ensure it is visible.
/// </summary>
/// <param name="item"></param>
private void PopulateDetailsSection(BookSearchResult item)
{
int checkedOutCount = GetCheckoutCountForItem(item);
UpdateDetailsText(item, checkedOutCount);
groupSelectionDetails.Show();
}
/// <summary>
/// For the specified book, populate the Details section and ensure it is visible.
/// </summary>
/// <param name="item"></param>
private void DisplayEditView(BookSearchResult item)
{
using (var form = new EditInventoryView())
{
form.DbConnection = DbConnection;
form.Item = item;
form.ShowDialog();
}
}
/// <summary>
/// Populate the text fields within the Details section.
/// </summary>
/// <param name="item"></param>
/// <param name="checkedOutCount"></param>
private void UpdateDetailsText(BookSearchResult item, int checkedOutCount)
{
txtDetailsTitle.Text = item.Title;
txtDetailsAuthor.Text = item.Authors.GetNames();
txtDetailsIsbn.Text = item.ISBN;
txtDetailsPublishDate.Text = item.PublishDate;
txtDetailsAvailable.Text = (item.Quantity - checkedOutCount).ToString();
txtDetailsQuantity.Text = item.Quantity.ToString();
}
/// <summary>
/// Query the DB to determine how many of the specified book are currently checked out.
/// </summary>
/// <param name="item"></param>
/// <returns></returns>
private int GetCheckoutCountForItem(BookSearchResult item)
{
var cmd = DbConnection.CreateCommand();
cmd.CommandText = "select count(*) from checkout_item where InventoryID = @barcode and ReturnedDate is null";
cmd.Parameters.AddWithValue("barcode", item.Barcode);
return((int)cmd.ExecuteScalar());
}
/// <summary>
/// Query the DB for books matching the specified terms. Caller is expected to pre-validate that at least one term is populated.
/// </summary>
/// <param name="req"></param>
private void SearchForBooks(SearchRequest req)
{
var cmd = DbConnection.CreateCommand();
cmd.CommandText = "select b.ISBN, b.title, b.PublishDate, i.Barcode, i.Quantity, a.Fname, a.Minit, a.Lname " +
"from book b " +
"join inventory i on i.BookID = b.ISBN " +
"join book_author ba on ba.BookID = i.BookID " +
"join author a on a.id = ba.AuthorID " +
"where b.ISBN in ( " +
" select distinct(b.ISBN) " +
" from book b " +
" join book_author ba on ba.BookID = i.BookID " +
" join author a on a.id = ba.AuthorID " +
" where 1 = 1 ";
if (req.Title.Length > 0)
{
cmd.CommandText += " and upper(b.Title) like @title ";
cmd.Parameters.AddWithValue("title", "%" + req.Title.ToUpper() + "%");
}
if (req.Author.Length > 0)
{
cmd.CommandText += " and(upper(a.Fname) like @author or upper(a.Lname) like @author)";
cmd.Parameters.AddWithValue("author", "%" + req.Author.ToUpper() + "%");
}
//close the IN clause, set ordering
cmd.CommandText += ") order by b.Title";
Hashtable resultsHash = new Hashtable();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//this PK will be our hash key
var ISBN = reader.GetString(0);
var item = (BookSearchResult)resultsHash[ISBN];
if (item == null)
{
item = new BookSearchResult()
{
ISBN = ISBN,
Title = reader.GetString(1),
PublishDate = reader.GetDateTime(2).ToString("MMM dd, yyyy"),
Barcode = reader.GetString(3),
Quantity = reader.GetInt32(4),
};
resultsHash[ISBN] = item;
}
item.Authors.Add(new Author()
{
Firstname = reader.GetString(5),
MiddleInitial = (reader.IsDBNull(6) ? null : reader.GetString(6)),
Lastname = reader.GetString(7)
});
}
}
foreach (var item in resultsHash.Values)
{
//this will fire the collection changed event
searchResults.Add((BookSearchResult)item);
}
}