public void OnGet(int Id)
{
// Connect to Database
DBConnection dbstring = new DBConnection();
string DbConnection = dbstring.DbString();
SqlConnection conn = new SqlConnection(DbConnection);
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
SqlDataReader reader;
LoanedBooks = new List <LoanedBook>();
PreLoanedBooks = new List <PreLoanedBook>();
if (Quantity != null)
{
// Book is available for Loan at any form
// SQL Query
command.CommandText = @"SELECT Username, FirstName, LastName, Title, DateLoaned, DateReturned FROM LoanedBook INNER JOIN Member ON UsernameId = Member.Id INNER JOIN Book ON Book.Id = BookId WHERE BookId = @BId";
command.Parameters.AddWithValue("@BId", Id);
reader = command.ExecuteReader(); // read records
while (reader.Read())
{
LoanedBook CustomerBook = new LoanedBook();
CustomerBook.CustomerUsername = reader.GetString(0);
CustomerBook.CustomerFirstName = reader.GetString(1);
CustomerBook.CustomerLastName = reader.GetString(2);
CustomerBook.BookTitle = reader.GetString(3);
CustomerBook.LoanedDate = reader.GetDateTime(4); // get the date when the customer got the book
CustomerBook.ReturnDate = reader.GetDateTime(5); // get the date when the book should be returned
LoanedBooks.Add(CustomerBook);
}
reader.Close();
}
// Get all the Books that the user could have preloaned
command.CommandText = @"SELECT Username, FirstName, LastName, Title, DatePreLoaned FROM PreLoanedBook INNER JOIN Member ON UsernameId = Member.Id INNER JOIN Book ON Book.Id = BookId WHERE BookId = @BookId";
command.Parameters.AddWithValue("@BookId", Id);
reader = command.ExecuteReader(); // read records
while (reader.Read())
{
PreLoanedBook CustomerBook = new PreLoanedBook();
CustomerBook.CustomerUsername = reader.GetString(0);
CustomerBook.CustomerFirstName = reader.GetString(1);
CustomerBook.CustomerLastName = reader.GetString(2);
CustomerBook.BookTitle = reader.GetString(3);
CustomerBook.PreLoanedDate = reader.GetDateTime(4); // get the date when the customer got the book
PreLoanedBooks.Add(CustomerBook);
}
reader.Close();
}
}
public void OnPost(int id, int currentBooks, int addedBooks)
{
// Connect to Database
DBConnection dbstring = new DBConnection();
string DbConnection = dbstring.DbString();
SqlConnection conn = new SqlConnection(DbConnection);
conn.Open();
// SQL Query
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
if (currentBooks == 0)
{
// First read PreloanBook Table as we need to know if any customers have preloaned the book as they will instantly loan the book
command.CommandText = @"SELECT TOP(@BFirst) UsernameId FROM PreLoanedBook WHERE BookId = @BId ORDER BY DatePreLoaned";
command.Parameters.AddWithValue("@BFirst", addedBooks);
command.Parameters.AddWithValue("@BId", id);
SqlDataReader reader = command.ExecuteReader(); // read records
PreLoanedBooks = new List <PreLoanedBook>();
// Get each username who pre-loan the book
while (reader.Read())
{
PreLoanedBook PreLoanedBook = new PreLoanedBook();
PreLoanedBook.CustomerId = reader.GetInt32(0);
PreLoanedBooks.Add(PreLoanedBook);
}
reader.Close();
// If the Book List is empty then no one has pre-loaned that book
if (PreLoanedBooks.Count != 0)
{
// Delete the records from the table for that book as the users who have the earliest date take priority
command.CommandText = @"DELETE FROM PreLoanedBook WHERE Id IN(SELECT TOP(@BFirst) Id FROM PreLoanedBook WHERE BookId = @BId ORDER BY DatePreLoaned)";
command.ExecuteNonQuery();
for (int i = 0; i < PreLoanedBooks.Count; i++)
{
// Add those records to LoanedBook Table & add the Dates
command.CommandText = @"INSERT INTO LoanedBook (UsernameId, BookId, DateLoaned, DateReturned) VALUES (@CId" + i + ", @BId, @DLoaned" + i + ", @DReturned" + i + ")";
command.Parameters.AddWithValue("@CId" + i, PreLoanedBooks[i].CustomerId);
// already defined BookId earlier on, so automatically added
DateTime currentDate = DateTime.Now; // get the current time
command.Parameters.AddWithValue("@DLoaned" + i, currentDate); // they now loan the book as of now
command.Parameters.AddWithValue("@DReturned" + i, currentDate.AddDays(7)); // this is the returned due date
command.ExecuteNonQuery();
}
// change the value - people who preloan the book take priorty
currentBooks = addedBooks - PreLoanedBooks.Count;
}
else
{
// If no one has preloaned the book we can just simply add it on
currentBooks += addedBooks;
}
}
else
{
// If no one has preloaned the book we can just simply add it on
currentBooks += addedBooks;
}
// Update the quantity
command.CommandText = @"UPDATE Book SET Quantity = @BQuantity WHERE Id = @BookId";
command.Parameters.AddWithValue("@BookId", id);
command.Parameters.AddWithValue("@BQuantity", currentBooks);
command.ExecuteNonQuery();
}
// Update the page
OnGet();
}
public IActionResult OnPostReturn()
{
// Connect to Database
DBConnection dbstring = new DBConnection();
string DbConnection = dbstring.DbString();
SqlConnection conn = new SqlConnection(DbConnection);
conn.Open();
using (SqlCommand command = new SqlCommand())
{
command.Connection = conn;
// SQL Query to delete book details
command.CommandText = @"DELETE FROM LoanedBook WHERE BookId = @BookId AND UsernameId = @UserId";
command.Parameters.AddWithValue("@UserId", Id = (int)HttpContext.Session.GetInt32(SessionKeyName1));
command.Parameters.AddWithValue("@BookId", BookRecord.Id);
command.ExecuteNonQuery();
// SQL Query to let the user who currently preloans the book to get it the book
if (BookRecord.Quantity == 0)
{
// First read PreloanBook Table as we need to know if any customers have preloaned the book as they will instantly loan the book
command.CommandText = @"SELECT TOP(1) UsernameId FROM PreLoanedBook WHERE BookId = @BookId ORDER BY DatePreLoaned";
SqlDataReader reader = command.ExecuteReader(); // read records
PreLoanedRecord = new PreLoanedBook();
// Get each username who pre-loan the book
while (reader.Read())
{
PreLoanedRecord.CustomerId = reader.GetInt32(0);
}
reader.Close();
// If the Book is empty then no one has pre-loaned that book
if (PreLoanedRecord.CustomerId != 0)
{
// Delete the record from the table for that book as the users who have the earliest date take priority
command.CommandText = @"DELETE FROM PreLoanedBook WHERE Id IN(SELECT TOP(1) Id FROM PreLoanedBook WHERE BookId = @BId ORDER BY DatePreLoaned)";
command.ExecuteNonQuery();
// Add those records to LoanedBook Table & add the Dates
command.CommandText = @"INSERT INTO LoanedBook (UsernameId, BookId, DateLoaned, DateReturned) VALUES (@CId, @BId, @DLoaned, @DReturned)";
command.Parameters.AddWithValue("@CId", PreLoanedRecord.CustomerId);
DateTime currentDate = DateTime.Now; // get the current time
command.Parameters.AddWithValue("@DLoaned", currentDate); // they now loan the book as of now
command.Parameters.AddWithValue("@DReturned", currentDate.AddDays(7)); // this is the returned due date
command.ExecuteNonQuery();
// Returned book + 1, Preloaned the book -1 == cancel out
}
else
{
// No users pre-loan the book add quantity
AddQuantity(command, Id);
}
}
else
{
// No users preloan the book add quantity
AddQuantity(command, Id);
}
// User has just returned the book
LoanBook = false;
PreLoanBook = false;
return(Page());
}
}
