public BookHistory CheckBookIssueToStudent(string studentId, string bookId)
{
BookHistory obj = null;
try
{
connect();
String s = "select TOP 1 * from BookHistory WHERE StudentId = @StudentId AND HBookId = @bookId ORDER BY BookHistoryId DESC";
cmd.CommandText = s;
cmd.Parameters.AddWithValue("@StudentId", studentId);
cmd.Parameters.AddWithValue("@bookId", bookId);
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
obj = new BookHistory(Convert.ToInt32(rd["BookHistoryId"].ToString()),
rd["StudentId"].ToString(),
Convert.ToDateTime(rd["IssueDate"].ToString()),
Convert.ToDateTime(rd["DueDate"].ToString()),
Convert.ToDouble(rd["Balance"].ToString()),
rd["Status"].ToString(),
Convert.ToInt32(rd["HBookId"].ToString())
);
}
con.Close();
}
catch (Exception ex)
{
Console.WriteLine("Get BookHistory by StudentId and Book Id: " + ex.Message.ToString());
}
return(obj);
}
private void Button2_Click(object sender, EventArgs e)
{
try
{
string bookid = txt1.Text.Trim();
string studentid = txt3.Text.Trim();
string issuedate = txtIssue.Value.ToString("yyyy-MM-dd");
string duedate = txtDue.Value.ToString("yyyy-MM-dd");
string amount = txtAmount.Text.Trim();
if (String.IsNullOrEmpty(bookid) || String.IsNullOrEmpty(studentid) || String.IsNullOrEmpty(amount))
{
MessageBox.Show("Book id and Student Id and Amount fields are required");
}
else
{
BookHistory bkh = new BookHistory(studentid, Convert.ToDateTime(issuedate), Convert.ToDateTime(duedate), Convert.ToDouble(amount), "Issued", Convert.ToInt32(bookid));
BookHistory checkIssue = db.CheckBookIssueToStudent(studentid, bookid);
if (checkIssue != null)
{
if (checkIssue.Status.Equals("Returned"))
{
if (db.IssueBook(bkh))
{
MessageBox.Show("Book issued successfully!");
txt1.Text = ""; txt2.Text = "";
txt3.Text = ""; txt5.Text = "";
txt4.Text = ""; txtAmount.Text = "";
}
}
else
{
MessageBox.Show("This book was issued previously and not yet returned to library. Please return previous book before issueing");
}
}
else
{
if (db.IssueBook(bkh))
{
MessageBox.Show("Book issued successfully!");
txt1.Text = ""; txt2.Text = "";
txt3.Text = ""; txt5.Text = "";
txt4.Text = ""; txtAmount.Text = "";
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
private void BtnReturn_Click(object sender, EventArgs e)
{
try
{
if (gvBookHistory.SelectedRows.Count != 0)
{
string historyId = Convert.ToString(gvBookHistory.CurrentRow.Cells["BookHistoryId"].Value);
string bookid = Convert.ToString(gvBookHistory.CurrentRow.Cells["BookId"].Value);
string studentid = loggedUser.StudentId;
string returndate = txtDate.Value.ToString("yyyy-MM-dd");
if (String.IsNullOrEmpty(bookid) || String.IsNullOrEmpty(studentid))
{
MessageBox.Show("Book id and Student Id fields are required");
}
else
{
BookHistory checkIssue = db.CheckBookIssueToStudent(studentid, bookid);
if (checkIssue != null)
{
if (checkIssue.Status.Equals("Issued"))
{
BookHistory bkh = new BookHistory(checkIssue.HistoryId, Convert.ToDateTime(returndate), checkIssue.Balance, "Returned");
if (db.ReturnBook(bkh))
{
MessageBox.Show("Book returned successfully!");
populate();
}
}
else
{
MessageBox.Show("This book wsa already returned previously. Thanks for your patience");
}
}
else
{
MessageBox.Show("Issued Book doesnot exists");
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
private void BtnReturn_Click(object sender, EventArgs e)
{
try
{
string bookid = txtBook.Text.Trim();
string studentid = txtStudent.Text.Trim();
string returndate = txtDate.Value.ToString("yyyy-MM-dd");
if (String.IsNullOrEmpty(bookid) || String.IsNullOrEmpty(studentid))
{
MessageBox.Show("Book id and Student Id fields are required");
}
else
{
BookHistory checkIssue = db.CheckBookIssueToStudent(studentid, bookid);
if (checkIssue != null)
{
txtStatus.Text = checkIssue.Status;
if (checkIssue.Status.Equals("Issued"))
{
BookHistory bkh = new BookHistory(checkIssue.HistoryId, Convert.ToDateTime(returndate), checkIssue.Balance, "Returned");
if (db.ReturnBook(bkh))
{
MessageBox.Show("Book returned successfully!");
txtBook.Text = ""; txtStudent.Text = "";
txtStatus.Text = "";
}
}
else
{
MessageBox.Show("This book wsa already returned previously. Thanks for your patience");
}
}
else
{
txtStatus.Text = "Issued Book doesnot exists";
MessageBox.Show("Issued Book doesnot exists");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
private void Button1_Click(object sender, EventArgs e)
{
if (gvBooks.SelectedRows.Count != 0)
{
string bookid = Convert.ToString(gvBooks.CurrentRow.Cells["BookId"].Value);
string amount = txtAmount.Text;
string issuedate = txtIssue.Value.ToString("yyyy-MM-dd");
string duedate = txtDue.Value.ToString("yyyy-MM-dd");
BookHistory checkIssue = db.CheckBookIssueToStudent(loggedUser.StudentId, bookid);
BookHistory bkh = new BookHistory(loggedUser.StudentId, Convert.ToDateTime(issuedate), Convert.ToDateTime(duedate), Convert.ToDouble(amount), "Issued", Convert.ToInt32(bookid));
if (String.IsNullOrEmpty(bookid) || String.IsNullOrEmpty(amount))
{
MessageBox.Show("Book id and Amount fields are required");
}
else
{
if (checkIssue != null)
{
if (checkIssue.Status.Equals("Returned"))
{
if (db.IssueBook(bkh))
{
MessageBox.Show("Book issued successfully!");
}
}
else
{
MessageBox.Show("This book was issued previously and not yet returned to library. Please return previous book before issueing");
}
}
else
{
if (db.IssueBook(bkh))
{
MessageBox.Show("Book issued successfully!");
}
}
}
}
}
public List <BookHistory> GetAllBookHistoryByStudent(string studentid)
{
List <BookHistory> list = new List <BookHistory>();
try
{
connect();
String s = "select A.*, B.Title,B.Author,B.Publisher from BookHistory A INNER JOIN Book B ON B.BookId = A.HBookId WHERE StudentId = @studentid";
cmd.CommandText = s;
cmd.Parameters.AddWithValue("@studentid", Convert.ToInt32(studentid));
SqlDataReader rd = cmd.ExecuteReader();
while (rd.Read())
{
BookHistory obj = new BookHistory();
obj.HistoryId = Convert.ToInt32(rd["BookHistoryId"].ToString());
obj.BookId = Convert.ToInt32(rd["HBookId"].ToString());
obj.StudentId = rd["StudentId"].ToString();
obj.Title = rd["Title"].ToString();
obj.Author = rd["Author"].ToString();
obj.Publisher = rd["Publisher"].ToString();
obj.IssueDate = Convert.ToDateTime(rd["IssueDate"].ToString());
obj.DueDate = Convert.ToDateTime(rd["DueDate"].ToString());
DateTime returnDate;
DateTime.TryParse(rd["ReturnDate"].ToString(), out returnDate);
obj.ReturnDate = returnDate;
obj.Balance = Convert.ToDouble(rd["Balance"].ToString());
obj.Status = rd["Status"].ToString();
list.Add(obj);
}
con.Close();
}
catch (Exception ex)
{
Console.WriteLine("Get All Books: " + ex.Message.ToString());
}
return(list);
}
public bool ReturnBook(BookHistory book)
{
try
{
connect();
String s = "Update BookHistory SET ReturnDate = @ReturnDate, Balance = @Balance, Status = @Status WHERE BookHistoryId = @Id";
cmd.CommandText = s;
cmd.Parameters.AddWithValue("@ReturnDate", book.ReturnDate);
cmd.Parameters.AddWithValue("@Balance", book.Balance);
cmd.Parameters.AddWithValue("@Status", book.Status);
cmd.Parameters.AddWithValue("@Id", book.HistoryId);
int result = cmd.ExecuteNonQuery();
con.Close();
return(result > 0);
}
catch (Exception ex)
{
Console.WriteLine("Return Book: " + ex.Message.ToString());
}
return(false);
}
public bool IssueBook(BookHistory book)
{
try
{
connect();
String s = "INSERT INTO BookHistory(StudentId, HBookId,IssueDate,DueDate,Balance,Status) VALUES(@StudentId, @HBookId,@IssueDate,@DueDate,@Balance,@Status)";
cmd.CommandText = s;
cmd.Parameters.AddWithValue("@StudentId", book.StudentId);
cmd.Parameters.AddWithValue("@HBookId", book.BookId);
cmd.Parameters.AddWithValue("@IssueDate", book.IssueDate);
cmd.Parameters.AddWithValue("@DueDate", book.DueDate);
cmd.Parameters.AddWithValue("@Balance", book.Balance);
cmd.Parameters.AddWithValue("@Status", book.Status);
int result = cmd.ExecuteNonQuery();
con.Close();
return(result > 0);
}
catch (Exception ex)
{
Console.WriteLine("Issue Book: " + ex.Message.ToString());
}
return(false);
}
