public static Checkout Find(int checkoutId)
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM checkouts WHERE id = @id;";
cmd.Parameters.AddWithValue("@id", checkoutId);
var rdr = cmd.ExecuteReader() as MySqlDataReader;
int id = 0;
int bookId = 0;
int patronId = 0;
DateTime checkout = DateTime.MinValue;
DateTime due = DateTime.MinValue;
bool returned = false;
while (rdr.Read())
{
id = rdr.GetInt32(0);
bookId = rdr.GetInt32(1);
patronId = rdr.GetInt32(2);
checkout = rdr.GetDateTime(3);
due = rdr.GetDateTime(4);
returned = rdr.GetBoolean(5);
}
Checkout foundCheckout = new Checkout(bookId, patronId, checkout, due, returned, id);
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(foundCheckout);
}
public List <Checkout> GetReturnedCheckouts()
{
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT checkouts.* FROM patrons
JOIN checkouts ON (checkouts.patrons_id = patrons.id)
WHERE checkouts.patrons_id = @patronId
AND returned = true
ORDER BY due_date ASC;";
cmd.Parameters.AddWithValue("@patronId", this.Id);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Checkout> allCheckouts = new List <Checkout> {
};
while (rdr.Read())
{
int id = rdr.GetInt32(0);
int book_id = rdr.GetInt32(1);
int patrons_id = rdr.GetInt32(2);
DateTime checkout = rdr.GetDateTime(3);
DateTime due = rdr.GetDateTime(4);
bool returned = rdr.GetBoolean(5);
Checkout newCheckout = new Checkout(book_id, patrons_id, checkout, due, returned, id);
allCheckouts.Add(newCheckout);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allCheckouts);
}
public List <Checkout> GetAllPastCheckouts()
{
List <Checkout> allCheckouts = new List <Checkout> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM checkouts WHERE (patron_id = @searchId) AND (is_returned = 1);";
cmd.Parameters.AddWithValue("@searchId", this.Id);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int id = rdr.GetInt32(0);
int patronId = rdr.GetInt32(1);
int copyId = rdr.GetInt32(2);
DateTime checkoutDate = rdr.GetDateTime(3);
DateTime dueDate = rdr.GetDateTime(4);
bool isReturned = rdr.GetBoolean(5);
Checkout newCheckout = new Checkout(patronId, copyId, checkoutDate, dueDate, isReturned, id);
allCheckouts.Add(newCheckout);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allCheckouts);
}