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(); }