private void BtnBorrow_Click(object sender, EventArgs e)
{
string matric = lblMatric.Text;
try
{
var studentId = Guid.Parse(lblID.Text);//enter the student id here
if (cmbBooks.SelectedValue == null)
{
MessageBox.Show("Please select a book", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (ctx.StudentBooks.Any(c => c.BookId == (int)cmbBooks.SelectedValue && c.StudentId == studentId && c.IsReturned != true))
{
MessageBox.Show($"This student has already borrowed {cmbBooks.Text} and has not returned it", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
var qty = ctx.Books.SingleOrDefault(m => m.BookId == (int)cmbBooks.SelectedValue).QtyAvailable;
if (qty > 0)
{
//initialized the Db Context
using (var ctx = new LibraryManagerEntities())
{
ctx.StudentBooks.Add(new Models.StudentBook()
{
StudentId = studentId,
BookId = (int)cmbBooks.SelectedValue,
DateToReturn = cmbDateToReturn.Value,
DateBorrowed = DateTime.Now.Date
});
var book = ctx.Books.Find((int)cmbBooks.SelectedValue);
var qty1 = book.QtyAvailable;
book.QtyAvailable = --qty1;
book.TimesBorowed = ++book.TimesBorowed;
ctx.SaveChanges();
ctx.SaveChanges();
MessageBox.Show("Book Borrowed to Student Successfully", "Good Job", MessageBoxButtons.OK, MessageBoxIcon.Information);
GetStudentBookHistory(matric);
}
}
else
{
MessageBox.Show($"{cmbBooks.Text} is out of stock", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show("An error occured while saving record", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void DataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
try
{
if (MessageBox.Show("Are you sure you want to delete this book record?", "Delete Confirmation", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
if (e.ColumnIndex == dataGridView1.Columns["actionButton"].Index)
{
int index = e.RowIndex;
var selectedRow = dataGridView1.Rows[index];
//var id = selectedRow.Cells[3].Value.ToString();
//i used an extension method here instead of above line cos the isbn cell value was not consistent, sths it returns the qty value
var isbn = selectedRow.Cells.GetCellValueFromColumnHeader("ISBN").ToString();
var book = ctx.Books.Where(m => m.ISBN == isbn).FirstOrDefault();
var rec = ctx.StudentBooks.Where(m => m.BookId == book.BookId).ToList();
if (rec.Any())
{
MessageBox.Show("Sorry! This record cannot be deleted because it has a borrow history", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
ctx.Books.Remove(book);
ctx.SaveChanges();
MessageBox.Show("Book deleted successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
LoadGrid();
}
}
}
catch (Exception ex)
{
MessageBox.Show("An error occured while deleting", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void GdvHistory_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
try
{
if (e.ColumnIndex == gdvHistory.Columns["actionButton"].Index)
{
//Do something with your button.
int index = e.RowIndex;
var selectedRow = gdvHistory.Rows[index];
var id = selectedRow.Cells[0].Value;
var data = ctx.StudentBooks.Where(m => m.Id == (int)id).FirstOrDefault();
data.IsReturned = true;
data.DateReturned = DateTime.Now.Date;
var book = ctx.Books.Where(m => m.BookId == data.BookId).FirstOrDefault();
book.QtyAvailable = ++book.QtyAvailable;
ctx.SaveChanges();
MessageBox.Show("Book returned successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
GetStudentBookHistory(lblMatric.Text);
}
}
catch (Exception ex)
{
MessageBox.Show("An error occured during the return operation", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void MetroButton1_Click(object sender, EventArgs e)
{
try
{
//Check if feilds are nulls
if (string.IsNullOrWhiteSpace(txtname.Text) && string.IsNullOrWhiteSpace(txtphone.Text) &&
string.IsNullOrWhiteSpace(txtpassword.Text) && string.IsNullOrWhiteSpace(txtusername.Text))
{
MessageBox.Show("All fields are required", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (ctx.Users.Any(c => c.PhoneNumber == txtphone.Text))
{
MessageBox.Show($"A record with {txtphone.Text} already exists", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (ctx.Users.Any(c => c.username == txtusername.Text))
{
MessageBox.Show($"A record with the username {txtusername.Text} already exists", "Sorry", MessageBoxButtons.OK);
return;
}
//initialized the Db Context
using (var ctx = new LibraryManagerEntities())
{
ctx.Users.Add(new User()
{
PhoneNumber = txtphone.Text,
username = txtusername.Text,
Password = txtpassword.Text,
CreatedBy = 2,
FullName = txtname.Text,
DateCreated = DateTime.Now.Date
});
ctx.SaveChanges();
LoadGrid();
MessageBox.Show("Record Saved Successfully", "Good Job", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.RetryCancel);
}
}
private void MetroButton1_Click(object sender, EventArgs e)
{
try
{
//Check if feilds are nulls
if (string.IsNullOrWhiteSpace(txtTitle.Text) || string.IsNullOrWhiteSpace(txtAuthor.Text) ||
string.IsNullOrWhiteSpace(txtISBN.Text) || string.IsNullOrWhiteSpace(txtQty.Text) || (int)ddlCategory.SelectedValue == 0)
{
MessageBox.Show("All fields are required", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (ctx.Books.Any(c => c.Title == txtTitle.Text))
{
MessageBox.Show($"A record with the Title {txtTitle.Text} already exists", "Sorry", MessageBoxButtons.OK);
return;
}
//initialized the Db Context
using (var ctx = new LibraryManagerEntities())
{
ctx.Books.Add(new Book()
{
Author = txtAuthor.Text,
ISBN = txtISBN.Text,
QtyEntered = Int32.Parse(txtQty.Text),
QtyAvailable = Int32.Parse(txtQty.Text),
Title = txtTitle.Text,
DateCreated = DateTime.Now.Date,
CategoryId = (int)ddlCategory.SelectedValue
});
ctx.SaveChanges();
LoadGrid();
MessageBox.Show("Record Saved Successfully", "Good Job", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
}
}
private void BtnCategory_Click(object sender, EventArgs e)
{
try
{
//Check if feilds are nulls
if (string.IsNullOrWhiteSpace(txtCategory.Text))
{
MessageBox.Show("Please enter a Category name", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (ctx.Books.Any(c => c.Title == txtTitle.Text))
{
MessageBox.Show($"{txtCategory.Text} already exists", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
//initialized the Db Context
using (var ctx = new LibraryManagerEntities())
{
ctx.Categories.Add(new Category()
{
CategoryName = txtCategory.Text,
DateCreated = DateTime.Now.Date,
CreatedBy = 1
});
ctx.SaveChanges();
LoadGrid();
MessageBox.Show("Category Saved Successfully", "Good Job", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.RetryCancel, MessageBoxIcon.Error);
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
//Check if feilds are nulls
if (string.IsNullOrWhiteSpace(txtfirstName.Text) && string.IsNullOrWhiteSpace(txtsurname.Text) &&
string.IsNullOrWhiteSpace(txtemail.Text) && string.IsNullOrWhiteSpace(txtmatricNo.Text) &&
string.IsNullOrWhiteSpace(txtlastName.Text) && string.IsNullOrWhiteSpace(txtNumber.Text))
{
MessageBox.Show("All fields are required", "Warning", MessageBoxButtons.OK);
}
else
{
if (ctx.Students.Any(c => c.Email == txtemail.Text))
{
MessageBox.Show($"A record with that Email{txtemail.Text} already exists", "Sorry", MessageBoxButtons.OK);
return;
}
//initialized the Db Context
using (var ctx = new LibraryManagerEntities())
{
//Using Student Info to generate QR
var stdId = Guid.NewGuid();
string makeStudentQR = $"{txtmatricNo.Text}";
//Saving student Info to DB
ctx.Students.Add(new Student()
{
FirstName = txtfirstName.Text,
LastName = txtsurname.Text,
CreatedBy = 1,
DateCreated = DateTime.Now.Date,
Email = txtemail.Text,
MatricNo = txtmatricNo.Text,
MiddleName = txtlastName.Text,
PhoneNumber = txtNumber.Text,
StudentId = stdId,
qrcode = makeStudentQR
});
ctx.SaveChanges();
var response = ctx.Students.Select(c => new
{
c.LastName,
c.FirstName,
c.Email,
c.MatricNo,
c.DateCreated
}).ToList();
if (response != null)
{
dataGridView1.DataSource = response;
}
else
{
dataGridView1.Visible = false;
groupBox2.Visible = false;
}
MessageBox.Show("Record Saved Successfully", "Good Job", MessageBoxButtons.OK);
}
}
}
catch (Exception ex)
{
throw;
}
}
private void MetroButton2_Click(object sender, EventArgs e)
{
var filee = openFileDialog1.FileName;
try
{
var filePath = Path.GetTempFileName();
if (Path.GetExtension(filee) != ".xlsx" && Path.GetExtension(filee) != ".xls")
{
MessageBox.Show("This is not a valid excel file", "Invalid File", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
bool firstRow = true; bool firstRow2 = true;
DataTable dt = new DataTable();
int count = 0;
using (XLWorkbook workBook = new XLWorkbook(filee))
{
var cats = ctx.Categories.ToList();
var categories = cats.Select(x => x.CategoryName);
//Read the first Sheet from Excel file.
IXLWorksheet workSheet = workBook.Worksheet(1);
foreach (IXLRow row in workSheet.Rows())
{
if (firstRow)
{
firstRow = false;
}
else
{
var i = row.Cell(6).Value.ToString();
if (!categories.Contains(row.Cell(6).Value.ToString().Trim()))
{
MessageBox.Show("Ensure that all category names listed in the excel sheet are already registered on the system. click on the Category drop down to see registered categories, or add a new category in the Create Category section above.", "Unable to Upload!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
}
foreach (IXLRow row in workSheet.Rows())
{
if (firstRow2)
{
firstRow2 = false;
}
else
{
string title = row.Cell(2).Value.ToString();
if (ctx.Books.Any(c => c.Title == title))
{
MessageBox.Show($"A book with the Title {title} already exists", "Sorry", MessageBoxButtons.OK);
}
else
{
//initialized the Db Context
using (var ctx = new LibraryManagerEntities())
{
string qty = row.Cell(5).Value.ToString();
ctx.Books.Add(new Book()
{
Author = row.Cell(4).Value.ToString(),
ISBN = row.Cell(3).Value.ToString(),
QtyEntered = Int32.Parse(qty),
QtyAvailable = Int32.Parse(qty),
Title = row.Cell(2).Value.ToString(),
CategoryId = cats.Where(x => x.CategoryName == row.Cell(6).Value.ToString().Trim()).FirstOrDefault().Id
});
ctx.SaveChanges();
count++;
}
}
}
}
LoadGrid();
MessageBox.Show(count + " Records Saved Successfully", "Good Job", MessageBoxButtons.OK);
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("The process cannot access the file"))
{
MessageBox.Show("Close the excel file and try again", "Unable to Upload", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
MessageBox.Show("An error occured while uploading the file, Ensure the excel sheet is properly formatted or contact the vendor", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}