private void Button5_Click(object sender, EventArgs e)
{
string searchQuery = txtSearchBox.Text;
//search by book title
if (cmbSearchFilter.SelectedIndex == 0)
{
bookSearchGridView.DataSource = SelectQueries.SearchBookByTitle(searchQuery);
}
//search by author
if (cmbSearchFilter.SelectedIndex == 1)
{
bookSearchGridView.DataSource = SelectQueries.SearchBookByAuthor(searchQuery);
}
//search by publisher
if (cmbSearchFilter.SelectedIndex == 2)
{
bookSearchGridView.DataSource = SelectQueries.SearchBookByPublisher(searchQuery);
}
//search by category
if (cmbSearchFilter.SelectedIndex == 3)
{
bookSearchGridView.DataSource = SelectQueries.SearchBookByCategory(searchQuery);
}
if (bookSearchGridView.Rows.Count == 1)
{
MessageBox.Show("Book not found.\n Please try searching again", "NOT FOUND"
, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show("Book Found", "FOUND", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void Button1_Click(object sender, EventArgs e)
{
//Approve
if (cmbStatusID.SelectedIndex == 0)
{
int rowIndex = issueRequestGridView.CurrentCell.RowIndex;
DataGridViewRow row = issueRequestGridView.Rows[rowIndex];
int memberID = Convert.ToInt32(row.Cells[1].Value);
int bookID = Convert.ToInt32(row.Cells[3].Value);
int totalCurrentIssues = SelectQueries.CountCurrentIssuedBooks(memberID);
int allowedIssues = SelectQueries.CountMemberIssueLimit(memberID);
//MEMBER IS IN HIS ISSUE LIMIT
if (totalCurrentIssues < allowedIssues)
{
int bookIssueCount = SelectQueries.BookIssuedCount(memberID, bookID, "YES");
int bookIssueLimit = SelectQueries.BookReissueLimitCount(bookID, memberID);
//member is in limit of reissuing current book
if (bookIssueCount < bookIssueLimit)
{
string issueDate = DateTime.Now.Date.ToShortDateString();
string returnDate = issueReturnDate.Value.ToShortDateString();
if (SelectQueries.GetCopiesAvailable(bookID) > 0)
{
InsertQueries.IssueBook(bookID, memberID, issueDate, returnDate, "NO");
UpdateQueries.DecreaseBookQuantitiy(bookID);
UpdateQueries.FinishIssueRequest("APPROVED", Convert.ToInt32(cmbIssueRequestID.SelectedValue.ToString()));
MessageBox.Show("BOOK ISSUED SUCCESSFULLY", "RESULT", MessageBoxButtons.OK, MessageBoxIcon.Information);
ViewIssueRequests();
}
else
{
MessageBox.Show("NO COPIES AVAILABLE IN THE LIBRARY \n " +
"PLEASE WAIT TILL COPIES BECOME AVAILABLE", "REJECTED", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
}
else
{
MessageBox.Show("REISSUE LIMIT OF THIS PARTICULAR BOOK REACHED \n " +
"CAN NO MORE ISSUE THIS SAME BOOK", "REJECTED", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
}
else
{
MessageBox.Show("ISSUE LIMIT OF MEMBER REACHED \n " +
"CAN NO MORE ISSUE ANY BOOK", "REJECTED", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
}
//DECLINED
if (cmbStatusID.SelectedIndex == 1)
{
UpdateQueries.FinishIssueRequest("DECLINED", Convert.ToInt32(cmbIssueRequestID.SelectedValue.ToString()));
ViewIssueRequests();
MessageBox.Show("ISSUE REQUEST DECLINED\n ", "DECLINED", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void Button2_Click(object sender, EventArgs e)
{
double dueAmount = 0;
int rowIndex = returnRequestGridView.CurrentCell.RowIndex;
DataGridViewRow row = returnRequestGridView.Rows[rowIndex];
DateTime returnDate = Convert.ToDateTime(row.Cells[3].Value);
int issueID = Convert.ToInt32(row.Cells[0].Value);
try
{
if (DateTime.Compare(DateTime.Today, returnDate) > 0)
{
if (txtOverdueAmount.Text.Trim() == "")
{
MessageBox.Show("AMOUNT CANNOT BE EMPTY");
}
else
{
dueAmount = Convert.ToDouble(txtOverdueAmount.Text.Trim());
try
{
InsertQueries.InsertIntoOverdue(issueID, dueAmount, cmbPaidStatus.SelectedValue.ToString());
UpdateQueries.FinishReturnRequest(Convert.ToInt32(cmbReturnRequestID.SelectedValue.ToString()));
UpdateQueries.UpdateIssueReturnRequest(issueID);
UpdateQueries.IncreaseBookQuantity(SelectQueries.GetBookidFromIssueId(issueID));
MessageBox.Show("Return request successfully served");
ViewReturnRequests();
}
catch (Exception exc)
{
Console.WriteLine(exc);
}
}
}
else
{
try
{
InsertQueries.InsertIntoReturn(issueID, DateTime.Now.Date.ToShortDateString());
UpdateQueries.FinishReturnRequest(Convert.ToInt32(cmbReturnRequestID.SelectedValue.ToString()));
UpdateQueries.UpdateIssueReturnRequest(issueID);
UpdateQueries.IncreaseBookQuantity(SelectQueries.GetBookidFromIssueId(issueID));
MessageBox.Show("Return request successfully served");
ViewReturnRequests();
}
catch (Exception exc)
{
Console.WriteLine(exc);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Due amount can only be in number");
Console.WriteLine(ex);
}
}
private void ViewIssueRequests()
{
DataTable dt = SelectQueries.GetIssueRequests();
issueRequestGridView.DataSource = dt;
cmbIssueRequestID.DisplayMember = "issue_request_id";
cmbIssueRequestID.ValueMember = "issue_request_id";
cmbIssueRequestID.DataSource = dt;
}
private void ViewToUpdateReturns()
{
DataTable dt = SelectQueries.GetAllIssuedBooks();
updateReturnsGridView.DataSource = dt;
cmbIssueID_return.ValueMember = "issue_id";
cmbIssueID_return.DisplayMember = "issue_id";
cmbIssueID_return.DataSource = dt;
}
public SelectMember()
{
InitializeComponent();
cmbSelectMember.SelectedIndex = -1;
DataTable dt = SelectQueries.GetAllMembers();
cmbSelectMember.ValueMember = "member_id";
cmbSelectMember.DisplayMember = "member_id";
cmbSelectMember.DataSource = dt;
}
private void BookSearchGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
int rowIndex = e.RowIndex;
DataGridViewRow row = bookSearchGridView.Rows[rowIndex];
int bookID = Convert.ToInt32(row.Cells[0].Value);
try
{
bookIssueGridView.DataSource = SelectQueries.GetBookIssues(bookID);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
private void ViewReturnRequests()
{
try
{
DataTable dt = SelectQueries.GetReturnRequests();
returnRequestGridView.DataSource = dt;
cmbReturnRequestID.DisplayMember = "return_request_id";
cmbReturnRequestID.ValueMember = "return_request_id";
cmbReturnRequestID.DataSource = dt;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
public JsonResult GetSpecies()
{
ResultResponseModel response = new ResultResponseModel();
try
{
SelectQueries query = new SelectQueries();
var result = query.SelectGenuses();
response.data = result;
response.error = new Error(200, "OK");
}
catch (Exception ex)
{
response.error = new Error(500, ex.Message);
}
return(Json(response));
}
private void Button2_Click(object sender, EventArgs e)
{
string searchQuery = txtBoxSearch.Text;
DataTable dt = new DataTable();
//search by book title
if (cmbFilter.SelectedIndex == 0)
{
dt = SelectQueries.SearchBookByTitle(searchQuery);
bookSearchGridView.DataSource = dt;
}
//search by author
if (cmbFilter.SelectedIndex == 1)
{
dt = SelectQueries.SearchBookByAuthor(searchQuery);
bookSearchGridView.DataSource = dt;
}
//search by publisher
if (cmbFilter.SelectedIndex == 2)
{
dt = SelectQueries.SearchBookByPublisher(searchQuery);
bookSearchGridView.DataSource = dt;
}
//search by category
if (cmbFilter.SelectedIndex == 3)
{
dt = SelectQueries.SearchBookByCategory(searchQuery);
bookSearchGridView.DataSource = dt;
}
if (bookSearchGridView.Rows.Count == 1)
{
lblMsg.Text = "Book not found!";
MessageBox.Show("Book not found.\n Please try searching again", "NOT FOUND"
, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
lblMsg.Text = "Book Found";
MessageBox.Show("Book Found", "FOUND", MessageBoxButtons.OK, MessageBoxIcon.Information);
cmbBookID.ValueMember = "book_id";
cmbBookID.DisplayMember = "book_id";
cmbBookID.DataSource = dt;
}
}
public JsonResult FilterBirdByLoc([FromBody] Bird bird)
{
ResultResponseModel response = new ResultResponseModel();
try
{
SelectQueries query = new SelectQueries();
var result = query.FilterBirdLoc(bird);
response.data = result;
response.error = new Error(200, "OK");
}
catch (Exception ex)
{
response.error = new Error(500, ex.Message);
}
return(Json(response));
}
public JsonResult GetHabitatRegion()
{
ResultResponseModel response = new ResultResponseModel();
try
{
SelectQueries query = new SelectQueries();
var region = query.SelectRegions();
var habitat = query.SelectHabitats();
response.data = new { region, habitat };
response.error = new Error(200, "OK");
}
catch (Exception ex)
{
response.error = new Error(500, ex.Message);
}
return(Json(response));
}
private void Button6_Click(object sender, EventArgs e)
{
int issueID = Convert.ToInt32(cmbIssueID_return.SelectedValue.ToString());
string newDate = newReturnDate.Value.ToShortDateString();
int bookID = SelectQueries.GetBookidFromIssueId(issueID);
int crntIssueReq = SelectQueries.GetBookCountInIssueRequests(bookID);
//Book issue can be extended as no issue requests are there for that book
if (crntIssueReq == 0)
{
UpdateQueries.UpdateReturnDate(issueID, newDate);
string message = "Return Date successfully changed";
MessageBox.Show(message, "DONE", MessageBoxButtons.OK, MessageBoxIcon.Information);
ViewToUpdateReturns();
}
else
{
string message = "Cannot change return date as there is a issue request for same book";
MessageBox.Show(message, "PROBIHATED", MessageBoxButtons.OK, MessageBoxIcon.Hand);
}
}
public Member()
{
InitializeComponent();
memberID = SelectMember.memberID;
lblMemberID.Text = memberID.ToString();
MemberClass member = new MemberClass();
member.MemberID = memberID;
member = SelectQueries.GetMemberInfo(member);
lblDateJoined.Text = member.JoinDate.ToShortDateString();
txtFirstName_Mem.Text = member.FName;
txtLastName_Mem.Text = member.LName;
txtPhone_Mem.Text = member.Number;
txtEmail_Mem.Text = member.Email;
txtAddress_Mem.Text = member.Address;
lblBooklimit.Text = member.IssueLimit.ToString();
pictureBoxImage.ImageLocation = member.ProfileImage;
DataTable dt = new DataTable();
dt = SelectQueries.BooksLeftToReturn(memberID);
booksLeftGriedView.DataSource = dt;
cmbIssueID.ValueMember = "Issue_ID";
cmbIssueID.DisplayMember = "Issue_ID";
cmbIssueID.DataSource = dt;
dt = SelectQueries.IssueHistory(memberID);
issueHistoryGridView.DataSource = dt;
dt = SelectQueries.ReturnHistory(memberID);
returnHistoryGridView.DataSource = dt;
dt = SelectQueries.UnpaidOverdues(memberID);
UnpaidGridView.DataSource = dt;
dt = SelectQueries.PaidOverdues(memberID);
PaidGridView.DataSource = dt;
}
private void Button4_Click(object sender, EventArgs e)
{
issuedBooksGridView.DataSource = SelectQueries.GetAllIssuedBooks();
overDueBooksGridView.DataSource = SelectQueries.GetAllOverdueBooks();
}
// Task 2 :
public void GetVillains()
{
SelectQueries.GetVillains(Constants.ConnectionString);
}
private void ViewIssuedBooks()
{
issuedBooksGridView.DataSource = SelectQueries.GetAllIssuedBooks();
overDueBooksGridView.DataSource = SelectQueries.GetAllOverdueBooks();
}
private void GenerateBAPReport()
{
booksReportGridView.DataSource = SelectQueries.GetAllBooks();
authorReportGridView.DataSource = SelectQueries.GetAllAuthors();
publisherReportGridView.DataSource = SelectQueries.GetAllPublisher();
}
// Task 3 :
public void GetMinionNames()
{
SelectQueries.GetMinionNames(Constants.ConnectionString);
}
// Task 8 :
public void IncreaseMinionAge()
{
SelectQueries.IncreaseMinionAge(Constants.ConnectionString);
}
// Task 7 :
public void PrintAllMinionNames()
{
SelectQueries.PrintAllMinionNames(Constants.ConnectionString);
}
