//search for a book based on inputted title
public static List <Checkout> SearchDueDate(DateTime dueDate)
{
List <Checkout> allDue = new List <Checkout> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM checkouts WHERE due_date = @DueDate;", conn);
cmd.Parameters.Add(new SqlParameter("@DueDate", dueDate));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int foundId = rdr.GetInt32(0);
DateTime foundDueDate = rdr.GetDateTime(1);
// DateTime foundReturnDate = rdr.GetDateTime(2);
int foundPatronsId = rdr.GetInt32(3);
int foundCopiesId = rdr.GetInt32(4);
Checkout foundCheckout = new Checkout(foundDueDate, foundPatronsId, foundCopiesId, foundId);
allDue.Add(foundCheckout);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return(allDue);
}
public List <Checkout> GetCheckouts()
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM checkouts WHERE patron_id = @PatronId", conn);
SqlParameter patronIdParameter = new SqlParameter("@PatronId", this.GetId());
cmd.Parameters.Add(patronIdParameter);
SqlDataReader rdr = cmd.ExecuteReader();
List <Checkout> checkoutList = new List <Checkout> {
};
while (rdr.Read())
{
int id = rdr.GetInt32(0);
int patronId = rdr.GetInt32(1);
int copyId = rdr.GetInt32(2);
string dueDate = rdr.GetDateTime(3).ToString();
Checkout foundCheckout = new Checkout(patronId, copyId, dueDate, id);
checkoutList.Add(foundCheckout);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
return(checkoutList);
}
//Returns a book to the library and sets the return date in Checkout object
public void ReturnBook(int patronId)
{
SqlConnection conn = DB.Connection();
conn.Open();
DateTime today = DateTime.Today;
SqlCommand cmd = new SqlCommand("UPDATE checkouts SET date_returned = @ReturnDate WHERE patrons_id = @PatronId AND copies_id IN (SELECT id FROM copies WHERE books_id = @BookId);", conn);
cmd.Parameters.Add(new SqlParameter("@PatronId", patronId));
cmd.Parameters.Add(new SqlParameter("@BookId", this.GetId()));
cmd.Parameters.Add(new SqlParameter("@ReturnDate", today));
SqlDataReader rdr = cmd.ExecuteReader();
int myCheckoutId = 0;
while (rdr.Read())
{
myCheckoutId = rdr.GetInt32(0);
Checkout myCheckout = Checkout.Find(myCheckoutId);
Console.WriteLine("MYCHECKOUT: " + myCheckout.GetReturnDate());
myCheckout.UpdateReturnDate(today);
}
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}