public JsonResult HandleRecieveOrder(ManagerForm form)
{
var success = false;
var reason = "unknown";
Order order = JsonSerializer.Deserialize <Order>(form.Data);
try
{
var handler = new ConnectionHandler();
using (MySqlConnection connection = handler.Connection)
{
string sql = "UPDATE Orders SET dateRecieved=@DATERECIEVED WHERE orderId=@ORDERID";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@DATERECIEVED", order.DateRecieved);
cmd.Parameters.AddWithValue("@ORDERID", order.OrderId);
cmd.ExecuteNonQuery();
success = true;
}
}
catch (Exception ex)
{
success = false;
reason = "unknown";
Console.WriteLine(ex);
}
if (success)
{
reason = "none";
}
return(new JsonResult($"{{\"success\":\"{success}\"," +
$"\"reason\":\"{reason}\"}}", new System.Text.Json.JsonSerializerOptions()));
}
public JsonResult HandleAddBook(EmployeeForm eform)
{
var success = false;
var reason = "unknown";
Console.WriteLine(eform.Data);
BookForm form = JsonSerializer.Deserialize <BookForm>(eform.Data);
try
{
var absoluteImagePath = Path.GetFullPath(".") + "\\wwwroot\\images\\" + form.ImageName;
var imageBase64 = form.Image.Replace("data:image/jpeg;base64,", "");
var handler = new ConnectionHandler();
using (MySqlConnection connection = handler.Connection)
{
string sql = "SELECT * FROM Books WHERE ISBN=@ISBN";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@ISBN", form.ISBN);
var hasBookAlready = false;
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
hasBookAlready = true;
}
}
if (!hasBookAlready)
{
cmd.CommandText = "INSERT INTO Books(ISBN,title,genre,author,summary,imagePath) VALUES (@ISBN,@TITLE,@GENRE,@AUTHOR,@SUMMARY,@IMAGEPATH)";
cmd.Parameters.AddWithValue("@TITLE", form.Title);
cmd.Parameters.AddWithValue("@GENRE", form.Genre);
cmd.Parameters.AddWithValue("@AUTHOR", form.Author);
cmd.Parameters.AddWithValue("@SUMMARY", form.Summary);
cmd.Parameters.AddWithValue("@IMAGEPATH", form.ImageName);
cmd.ExecuteNonQuery();
System.IO.File.WriteAllBytes(absoluteImagePath, Convert.FromBase64String(imageBase64));
success = true;
}
else
{
reason = "bookExists";
}
}
}
catch (Exception ex)
{
success = false;
Console.WriteLine(ex);
}
if (success)
{
reason = "none";
}
return(new JsonResult($"{{\"success\":\"{success}\"," +
$"\"reason\":\"{reason}\"}}", new System.Text.Json.JsonSerializerOptions()));
}
public JsonResult HandleCheckout([FromBody] EmployeeForm eform)
{
var success = false;
var reason = "unknown";
Console.WriteLine(eform.Data);
CheckoutForm form = JsonSerializer.Deserialize <CheckoutForm>(eform.Data);
Console.WriteLine(form.BookId);
Console.WriteLine(form.Username);
try
{
var handler = new ConnectionHandler();
using (MySqlConnection connection = handler.Connection)
{
MySqlTransaction trans = connection.BeginTransaction();
string sql = "SELECT userId FROM Users WHERE username=@USERNAME";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Transaction = trans;
cmd.Parameters.AddWithValue("@USERNAME", form.Username);
MySqlDataReader rdr = cmd.ExecuteReader();
// if the username exists
if (rdr.Read())
{
int userId = (int)rdr[0];
rdr.Close();
cmd.CommandText = "SELECT * FROM BookHistory WHERE userId=@USERID AND dateReturned IS NULL";
cmd.Parameters.AddWithValue("@USERID", userId);
rdr = cmd.ExecuteReader();
// User does not have a book already checkedout
if (!rdr.Read())
{
rdr.Close();
cmd.CommandText = "SELECT availability FROM BookDetails WHERE bookId=@BOOKID";
cmd.Parameters.AddWithValue("@BOOKID", form.BookId);
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr[0].ToString() == "available")
{
rdr.Close();
cmd.CommandText = "INSERT INTO BookHistory(userId, bookId, dateCheckout) VALUES (@USERID, @BOOKID, CURDATE())";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE BookDetails SET availability='checkedout' WHERE bookId=@BOOKID";
cmd.ExecuteNonQuery();
trans.Commit();
success = true;
}
else if (rdr[0].ToString() == "reserved")
{
rdr.Close();
cmd.CommandText = "SELECT userId FROM Reservations WHERE bookId=@BOOKID";
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if ((int)rdr[0] == userId)
{
rdr.Close();
cmd.CommandText = "INSERT INTO BookHistory(userId, bookId, dateCheckout) VALUES (@USERID, @BOOKID, CURDATE())";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE BookDetails SET availability='checkedout' WHERE bookId=@BOOKID";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM Reservations WHERE bookId=@BOOKID";
cmd.ExecuteNonQuery();
trans.Commit();
success = true;
}
else
{
rdr.Close();
reason = "reserved";
}
}
else
{
// INTERNAL PROBLEM - Book is reserved but there is no row in Reservations
reason = "unknown";
}
}
else
{
rdr.Close();
reason = "unavailable";
}
}
else
{
rdr.Close();
reason = "badBookId";
}
}
else
{
rdr.Close();
reason = "multipleCheckout";
}
}
else
{
rdr.Close();
reason = "badUsername";
}
}
}
catch (Exception ex)
{
success = false;
Console.WriteLine(ex);
}
if (success)
{
reason = "none";
}
return(new JsonResult($"{{\"success\":\"{success}\"," +
$"\"reason\":\"{reason}\"}}", new System.Text.Json.JsonSerializerOptions()));
}
public void OnGet(string isbn)
{
if (isbn == null)
{
ISBN = "0000000000000";
}
else
{
ISBN = isbn;
}
Console.WriteLine("ITEM Page ISBN=" + isbn);
try
{
var handler = new ConnectionHandler();
MySqlConnection connection = handler.Connection;
string sql = "SELECT * FROM Books WHERE ISBN=@ISBN";
MySqlCommand cmd = new MySqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@ISBN", ISBN);
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
//this.ISBN = (string) rdr[0];
Title = (string)rdr[1];
Genre = (string)rdr[2];
Author = (string)rdr[3];
Summary = (string)rdr[4];
//DatePublished = (DateTime)rdr[5];
ImagePath = (string)rdr[6];
}
}
Console.WriteLine("ITEM Page ImagePath=" + ImagePath);
cmd.CommandText = "SELECT reviewText, username, rating " +
"FROM UserReviews " +
"INNER JOIN Users " +
"ON UserReviews.userId=Users.userId AND ISBN=@ISBN";
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Review review = new Review((string)rdr[0], (string)rdr[1], (int)rdr[2]);
Reviews.Add(review);
}
}
cmd.CommandText = "SELECT * FROM BookDetails WHERE ISBN=@ISBN AND availability='available'";
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
HasAvailableCopies = true;
}
else
{
HasAvailableCopies = false;
}
}
cmd.CommandText = "SELECT * FROM Reservations WHERE userId=@USERID";
cmd.Parameters.AddWithValue("@USERID", HttpContext.Session.GetString("userId"));
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
HasBookReserved = true;
}
else
{
HasBookReserved = false;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
public JsonResult OnPost([FromBody] RegistrationForm form)
{
var hasBadField = false;
var hasDifPass = false;
var isUnameTaken = false;
var errorMessage = "";
var success = false;
try
{
if (form.FirstName == null || form.LastName == null || form.Username == null ||
form.Password1 == null || form.Password2 == null || form.Address == null ||
form.City == null || form.State == null || form.Zip == null || form.Phone == null)
{
hasBadField = true;
}
// these should be validated client side as well
if (form.Username.Length < 6 || form.Zip.Length != 5 || form.Phone.Length != 10 ||
form.Address.Length < 2 || form.State.Length != 2 || form.City.Length < 2 ||
form.FirstName.Length < 1 || form.LastName.Length < 1)
{
hasBadField = true;
}
if (form.Password1 != form.Password2 || form.Password1.Length < 6)
{
hasDifPass = true;
}
var handler = new ConnectionHandler();
string sql = "SELECT * FROM Users WHERE username=@USERNAME";
MySqlCommand select_username = new MySqlCommand(sql, handler.Connection);
select_username.Parameters.AddWithValue("@USERNAME", form.Username);
using (MySqlDataReader rdr = select_username.ExecuteReader())
{
if (rdr.Read())
{
isUnameTaken = true;
}
}
if (!isUnameTaken && !hasBadField && !hasDifPass)
{
using (MySqlTransaction trans = handler.Connection.BeginTransaction())
{
Console.WriteLine("Register: No Error in fields");
// create a random salt to hash the password with to
// guard against rainbow tables and other site breaches
var rand = new Random((int)Stopwatch.GetTimestamp());
SHA256 hash = SHA256.Create();
var saltBytes = hash.ComputeHash(Encoding.ASCII.GetBytes(form.Username + rand.Next().ToString()));
var salt = Encoding.ASCII.GetString(saltBytes);
var hashedPasswordBytes = hash.ComputeHash(Encoding.ASCII.GetBytes(form.Password1 + salt));
var hashedPassword = Encoding.ASCII.GetString(hashedPasswordBytes);
sql = "INSERT INTO Users(username,password,salt,accountType,"
+ "firstName,lastName,address,city,zip,state,phone) " +
"VALUES (@USERNAME, @PASSWORD, @SALT, 'user', "
+ "@FIRSTNAME, @LASTNAME, @ADDRESS, @CITY, @ZIP, @STATE, @PHONE)";
MySqlCommand insert_user = new MySqlCommand(sql, trans.Connection);
insert_user.Parameters.AddWithValue("@USERNAME", form.Username);
insert_user.Parameters.AddWithValue("@PASSWORD", hashedPassword);
insert_user.Parameters.AddWithValue("@SALT", salt);
insert_user.Parameters.AddWithValue("@FIRSTNAME", form.FirstName);
insert_user.Parameters.AddWithValue("@LASTNAME", form.LastName);
insert_user.Parameters.AddWithValue("@ADDRESS", form.Address);
insert_user.Parameters.AddWithValue("@CITY", form.City);
insert_user.Parameters.AddWithValue("@ZIP", form.Zip);
insert_user.Parameters.AddWithValue("@STATE", form.State);
insert_user.Parameters.AddWithValue("@PHONE", form.Phone);
insert_user.ExecuteNonQuery();
trans.Commit();
success = true;
}
}
}
catch (Exception ex)
{
errorMessage = ex.ToString();
success = false;
Console.WriteLine(ex);
}
return(new JsonResult($"{{\"hasBadField\":\"{hasBadField}\","
+ $"\"hasDifferentPasswords\":\"{hasDifPass}\","
+ $"\"isUsernameTaken\":\"{isUnameTaken}\","
+ $"\"success\":\"{success}\"}}", new System.Text.Json.JsonSerializerOptions()));
}
