public List <Checkouts> GetAllCheckouts()
{
ConnectionString myConnection = new ConnectionString();
string cs = myConnection.cs;
var con = new MySqlConnection(cs);
con.Open();
string stm = "SELECT * FROM checkouts";
var cmd = new MySqlCommand(stm, con);
MySqlDataReader reader = cmd.ExecuteReader();
List <Checkouts> checkouts = new List <Checkouts>();
while (reader.Read())
{
Checkouts c = new Checkouts()
{
checkoutid = reader.GetInt32(0),
itemid = reader.GetInt32(1),
userid = reader.GetInt32(2),
checkouttime = reader.GetDateTime(3),
duedate = reader.GetDateTime(4),
isreturned = reader.GetBoolean(5)
};
checkouts.Add(c);
}
return(checkouts);
}
public void AddReturn(Checkouts cvalue, int userid, int userstatus, int ivalue)
{
ConnectionString myConnection = new ConnectionString();
string cs = myConnection.cs;
var con = new MySqlConnection(cs);
con.Open();
System.DateTime tempreturntime = DateTime.Now;
string stm = @"INSERT INTO itemreturns(checkoutid, returndate)
VALUES(@checkoutid, @returndate)";
var cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@checkoutid", cvalue.checkoutid);
cmd.Parameters.AddWithValue("@returndate", tempreturntime);
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"UPDATE checkouts SET isreturned = 1 WHERE checkoutid = @checkout_id";
cmd.Parameters.AddWithValue("@checkout_id", cvalue);
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"UPDATE items SET ischeckedout = 0 where itemid = @itemid";
cmd.Parameters.AddWithValue("@itemid", ivalue);
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = @"UPDATE users SET userstatus = @userstatus WHERE userid = @userid";
cmd.Parameters.AddWithValue("@userid", userid);
if (tempreturntime.CompareTo(cvalue.duedate) > 0)
{
cmd.Parameters.AddWithValue("@userstatus", userstatus + 1);
}
else
{
cmd.Parameters.AddWithValue("@userstatus", userstatus);
}
cmd.Prepare();
cmd.ExecuteNonQuery();
}
public void UpdateCheckOut(int checkoutid, Checkouts cvalue)
{
ConnectionString myConnection = new ConnectionString();
string cs = myConnection.cs;
var con = new MySqlConnection(cs);
con.Open();
string stm = "UPDATE checkouts SET itemid = @itemid, userid = @userid, checkouttime = @checkouttime, duedate = @duedate, isreturned = @isreturned WHERE checkoutid = @checkoutid";
var cmd = new MySqlCommand(stm, con);
cmd.Parameters.AddWithValue("@itemid", cvalue.itemid);
cmd.Parameters.AddWithValue("@userid", cvalue.userid);
cmd.Parameters.AddWithValue("@checkouttime", cvalue.checkouttime);
cmd.Parameters.AddWithValue("@duedate", cvalue.duedate);
cmd.Parameters.AddWithValue("@isreturned", cvalue.isreturned);
cmd.Parameters.AddWithValue("@checkoutid", checkoutid);
cmd.Prepare();
cmd.ExecuteNonQuery();
}
