private void DisplayIssued()
{
string selectQuery;
selectQuery = "SELECT BookIssue.bookID,Book.title,BookIssue.StudentID,BookIssue.returnDate From BookIssue JOIN Book ON BookIssue.bookID = Book.bookID";
SqlConnection conn = ConnectionManager.DBConnection();
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(selectQuery, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
// Define the list items
Book booksDetails = new Book();
booksDetails.BookTitle = rdr["title"].ToString();
IssueBook books = new IssueBook();
books.BookID = int.Parse(rdr["bookID"].ToString());
books.StudentID = int.Parse(rdr["StudentID"].ToString());
books.DoR = DateTime.Parse(rdr["returnDate"].ToString());
ListViewItem lvi = new ListViewItem(books.BookID.ToString());
lvi.SubItems.Add(booksDetails.BookTitle);
lvi.SubItems.Add(books.StudentID.ToString());
lvi.SubItems.Add(books.DoR.ToString());
listView1.Items.Add(lvi);
}
if (rdr != null)
{
rdr.Close();
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Unsuccessful" + ex);
}
}
private void btnSubmit_Click(object sender, EventArgs e)
{
int bookID;
if (!int.TryParse(txtBookID.Text, out bookID))
{
MessageBox.Show("Book ID must be an integer", "ERROR", MessageBoxButtons.OK);
return;
}
else if (bookID < 1)
{
MessageBox.Show("Book ID must be a positive number");
txtBookID.Focus();
return;
}
int studentID;
if (!int.TryParse(txtStudentID.Text, out studentID))
{
MessageBox.Show("Student ID must be an integer", "ERROR", MessageBoxButtons.OK);
return;
}
else if (studentID < 1)
{
MessageBox.Show("Student ID must be a positive number");
txtStudentID.Focus();
return;
}
DataTable books = ConnectionManager.GetTable("select * from Book where bookID = "
+ bookID);
if (books.Rows.Count == 0)
{
MessageBox.Show("No book was found matching book ID " + bookID);
return;
}
DataTable students = ConnectionManager.GetTable("select * from Student where studentID = "
+ studentID);
if (students.Rows.Count == 0)
{
MessageBox.Show("No student was found matching student ID " + studentID);
return;
}
if (int.Parse(books.Rows[0]["noOfAvailableBooks"].ToString()) < 1)
{
MessageBox.Show("No copies of " + books.Rows[0]["title"].ToString() + "are available");
}
else
{
IssueBook book = new IssueBook();
book.BookID = bookID;
book.StudentID = studentID;
book.LibrarianID = int.Parse(Environment.GetEnvironmentVariable("librarianID"));
book.IssueDate = DateTime.Now;
book.ReturnDate = book.IssueDate.Add(new TimeSpan(14, 0, 0, 0));
if (book.Issue() != 0)
{
book.updateBookQtyOnIssue();
MessageBox.Show("Book issued successfully");
txtBookID.Text = "";
txtStudentID.Text = "";
}
else
{
MessageBox.Show("Failed to issue book. Please check details and try again");
txtBookID.Focus();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
if (ValidateInputs() == false)
{
return;
}
Book ReturnedBook = new Book();
ReturnedBook.BookID = int.Parse(txtBookID.Text);
ReturnBook newReturn = new ReturnBook();
newReturn.BookID = int.Parse(txtBookID.Text);
newReturn.StudentID = int.Parse(txtStudentID.Text);
newReturn.RDoR = DateTime.Parse(txtRDoR.Text);
newReturn.LibrarianID = int.Parse(txtLibrarianID.Text);
DataTable IstheBookIssued = ConnectionManager.GetTable("select * from BookIssue where studentID=('" + newReturn.StudentID + "') AND librarianID=('" + newReturn.LibrarianID + "') AND bookID=('" + newReturn.BookID + "') AND returnDate=('" + newReturn.RDoR + "')");
// suppose to be comparing return date
string ConditionQuery;
if (IstheBookIssued.Rows.Count == 1)
{
//Just in Time
newReturn.AddNewReturn();
ReturnedBook.updateBookQtyOnReturn();
newReturn.RemoveIssue();
MessageBox.Show("GoodJob, Returned just in Time.", "Book Return Complete.");
}
else if (IstheBookIssued.Rows.Count == 0)
{
DataTable IstheBookIssued2 = ConnectionManager.GetTable("select * from BookIssue where studentID=('" + newReturn.StudentID + "') AND librarianID=('" + newReturn.LibrarianID + "') AND bookID=('" + newReturn.BookID + "') ");
if (IstheBookIssued2.Rows.Count == 1)
{
ConditionQuery = "select * from BookIssue where studentID=('" + newReturn.StudentID + "') AND librarianID=('" + newReturn.LibrarianID + "') AND bookID=('" + newReturn.BookID + "') ";
SqlConnection conn = ConnectionManager.DBConnection();
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(ConditionQuery, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
IssueBook dor = new IssueBook();
dor.StudentID = int.Parse(rdr["studentID"].ToString());
dor.BookID = int.Parse(rdr["bookID"].ToString());
dor.LibrarianID = int.Parse(rdr["librarianID"].ToString());
dor.DoR = DateTime.Parse(rdr["returnDate"].ToString());
if (newReturn.RDoR > dor.DoR)
{
MessageBox.Show(" Return Date of this book is OverDue." + Environment.NewLine + "Fine Requested to Pay : 50$.", "Sorry YOU ARE LATE");
newReturn.AddNewReturn();
ReturnedBook.updateBookQtyOnReturn();
newReturn.RemoveIssue();
MessageBox.Show(" Fine is been paid." + Environment.NewLine + "Have a good Day ", " Book Return Complete.");
txtBookID.Text = "";
txtStudentID.Text = "";
txtRDoR.Text = "";
txtLibrarianID.Text = "";
txtBookID.Focus();
}
else
{
newReturn.AddNewReturn();
ReturnedBook.updateBookQtyOnReturn();
newReturn.RemoveIssue();
MessageBox.Show("GoodJob, Returned before the expected date of return.", "Book Return Complete.");
txtBookID.Text = "";
txtStudentID.Text = "";
txtRDoR.Text = "";
txtLibrarianID.Text = "";
txtBookID.Focus();
}
}
}
catch
{
MessageBox.Show("Please make sure the follow details are inserted in the right format as in this example:" + Environment.NewLine + "BookiD : 1" + Environment.NewLine + "Date of Return: MM-DD-YYYY" + Environment.NewLine + "LibrarianID : 2");
}
}
else
{
MessageBox.Show("None of the books borrowed corrispond to the details inserted." + Environment.NewLine + "Please Check Again The Details.", "Sorry NOT FOUND");
}
}
}
catch
{
MessageBox.Show("Please make sure the follow details are inserted in the right format as in this example:" + Environment.NewLine + "BookiD : 1" + Environment.NewLine + "Student ID : 1001" + Environment.NewLine + "Date of Return: MM-DD-YYYY" + Environment.NewLine + "LibrarianID : 2");
}
}
private void btnIssue_Click(object sender, EventArgs e)
{
try
{
if (ValidateInputs() == false)
{
return;
}
Book issuedBook = new Book();
issuedBook.BookID = int.Parse(txtBookID.Text);
DataTable BookRequired = ConnectionManager.GetTable("select noOfAvailableBooks from Book where bookID=(" + int.Parse(txtBookID.Text) + ")");
IssueBook newIssue = new IssueBook();
newIssue.BookID = int.Parse(txtBookID.Text);
newIssue.StudentID = int.Parse(txtStudentID.Text);
newIssue.DoI = DateTime.Parse(txtDoI.Text);
newIssue.DoR = DateTime.Parse(txtDoR.Text);
newIssue.LibrarianID = int.Parse(txtLibrarianID.Text);
DataTable BookAlreadyIssued = ConnectionManager.GetTable("select * from BookIssue where bookID=(" + int.Parse(txtBookID.Text) + ") and studentID=(" + int.Parse(txtStudentID.Text) + ")");
if (BookAlreadyIssued.Rows.Count >= 1)
{
MessageBox.Show("This student is currently in possess of one of this book already." + Environment.NewLine + "Please check again that all the Details inserted are correct.", "Sorry NOT ALLOWED");
txtBookID.Focus();
}
else if (newIssue.DoR <= newIssue.DoI)
{
MessageBox.Show(" Date inserted must be wrong, as return date is previous or same to the date of issue.", "Sorry NOT POSSIBLE");
}
else if (BookAlreadyIssued.Rows.Count == 0)
{
string ConditionQuery;
ConditionQuery = "select noOfAvailableBooks from Book where bookID=(" + int.Parse(txtBookID.Text) + ")";
SqlConnection conn = ConnectionManager.DBConnection();
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(ConditionQuery, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Book booksDetails = new Book();
booksDetails.AvailableBooks = int.Parse(rdr["noOfAvailableBooks"].ToString());
if (booksDetails.AvailableBooks <= 0)
{
DialogResult dialogResult = MessageBox.Show("Book Requested is not available at moment." + Environment.NewLine + "Do you want Reserve this book?", "Sorry,Not Available.", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
this.Hide();
ReserveForm Procede = new ReserveForm();
Procede.Show();
}
else if (dialogResult == DialogResult.No)
{
txtBookID.Text = "";
txtStudentID.Text = "";
txtDoI.Text = "";
txtDoR.Text = "";
txtLibrarianID.Text = "";
txtBookID.Focus();
MessageBox.Show("Issue Resetted.");
}
}
else
{
newIssue.AddNewIssue();
issuedBook.updateBookQtyOnIssue();
MessageBox.Show("Book Issue Complete.");
}
}
}
catch
{
MessageBox.Show("Please make sure the follow details are inserted in the right format as in this example:" + Environment.NewLine + "BookiD : 1" + Environment.NewLine + "Student ID : 1001" + Environment.NewLine + "Date of Issue: MM-DD-YYYY" + Environment.NewLine + "Date of Return: 12-31-2018" + Environment.NewLine + "LibrarianID : 2");
}
}
}catch
{
MessageBox.Show("Please make sure the follow details are inserted in the right format as in this example:" + Environment.NewLine + "BookiD : 1" + Environment.NewLine + "Student ID : 1001" + Environment.NewLine + "Date of Issue: DD-MM-YYYY" + Environment.NewLine + "Date of Return: 31-12-2018" + Environment.NewLine + "LibrarianID : 2");
}
}