public static LibTran getLastTransationByBookID(int _book_id)
{
LibraryDBEntities entity = new LibraryDBEntities();
var q = from t in entity.LibTrans
where t.BookID == _book_id
group t by t.BookID into g
select new
{
Max = g.Max(b => b.TransactionID),
};
int i = 0;
foreach (var data in q)
{
i = data.Max;
break;
}
LibTran tranObj = new LibTran();
if (i > 1)
{
tranObj = entity.LibTrans.Where(x => x.TransactionID == i).First();
}
return(tranObj);
}
//return the number of avaiable books to rent for a specific book
public static int getNumberOfAvaiableBooksToRentInLibraryByBookModelID(int _book_model_id)
{
LibraryDBEntities entity = new LibraryDBEntities();
int i = entity.Books.Where(x => x.BookModelID == _book_model_id).Where(x => x.BookStatus == 1).Count();
return(i);
}
public static IQueryable getBookModelByTitle(string _book_title)
{
LibraryDBEntities entity = new LibraryDBEntities();
var v = entity.BooksModels.Where(x => x.BookTitle.Contains(_book_title));
return(v);
}
public static Book getBookByBookID(int _book_id)
{
LibraryDBEntities entity = new LibraryDBEntities();
Book b = entity.Books.Where(x => x.BookID == _book_id).SingleOrDefault();
return(b);
}
public static BooksModel getBookModelByID(int _book_model_id)
{
LibraryDBEntities entity = new LibraryDBEntities();
BooksModel v = entity.BooksModels.Where(x => x.BookModelId == _book_model_id).First();
return(v);
}
public static Member getMemberByMemberID(int _member_id)
{
LibraryDBEntities entity = new LibraryDBEntities();
Member m = entity.Members.Where(x => x.MemberID == _member_id).SingleOrDefault();
return(m);
}
//return the whole book model object
public static IQueryable getBooksModels()
{
LibraryDBEntities entity = new LibraryDBEntities();
var v = from bm in entity.BooksModels select bm;
return(v);
}
public ucDetialsMember(int _member_id)
{
InitializeComponent();
member_id = _member_id;
entity = new LibraryDBEntities();
member = EntityBroker.getMemberByMemberID(_member_id);
}
public static IQueryable getBooksByBookModelID(int _book_model_id)
{
LibraryDBEntities entity = new LibraryDBEntities();
//var books = from b in entity.Books where b.BookModelID == _book_model_id select new { b.BookID , b.BookStatus,Status = (b.BookStatus == 0) ? "Rented" : (b.BookStatus == 1) ? "Avaiable" : "Not Avaiable" };
var books = from b in entity.Books where b.BookModelID == _book_model_id select b;
return(books);
}
public static int updateBookModelEntity(BooksModel _book_model)
{
LibraryDBEntities entity = new LibraryDBEntities();
var book_model_row = (from bm in entity.BooksModels
where bm.BookModelId == _book_model.BookModelId
select bm).FirstOrDefault();
book_model_row = _book_model;
return(entity.SaveChanges());
}
private void ucListMember_Load(object sender, EventArgs e)
{
LibraryDBEntities le = new LibraryDBEntities();
LibraryDBEntities context = new LibraryDBEntities();
var query = from m in context.Members select new { m.MemberID, m.MemberName, m.PhoneNumber, m.Address, m.Email };
var bookmodels = query.ToList();
dgvMembersList.DataSource = bookmodels;
}
public static int createNewBook(Book _book)
{
LibraryDBEntities entity = new LibraryDBEntities();
Book book = new Book();
book.BookModelID = _book.BookModelID;
book.BookStatus = _book.BookStatus;
entity.AddToBooks(book);
int i = entity.SaveChanges();
return(i);
}
public static int updateBook(Book book)
{
LibraryDBEntities entity = new LibraryDBEntities();
var book_row = entity.Books.Where(x => x.BookID == book.BookID).SingleOrDefault();
book_row.BookStatus = book.BookStatus;
int i = entity.SaveChanges();
return(i);
}
public static int updateTransaction(LibTran tran)
{
LibraryDBEntities entity = new LibraryDBEntities();
var tran_row = entity.LibTrans.Where(x => x.TransactionID == tran.TransactionID).SingleOrDefault();
tran_row.ReturnDate = tran.ReturnDate;
tran_row.ChargeAmount = tran.ChargeAmount;
int i = entity.SaveChanges();
return(i);
}
public static int updateMember(Member _member)
{
LibraryDBEntities entity = new LibraryDBEntities();
var member_row = entity.Members.Where(x => x.MemberID == _member.MemberID).SingleOrDefault();
member_row.MemberName = _member.MemberName;
member_row.Address = _member.Address;
member_row.PhoneNumber = _member.PhoneNumber;
member_row.NIRC = _member.NIRC;
member_row.Email = _member.Email;
int i = entity.SaveChanges();
return(i);
}
public ucDetailsBook(int _book_model_id)
{
InitializeComponent();
book_model_id = _book_model_id;
entity = new LibraryDBEntities();
dt = new DataTable();
dt.Columns.Add("Book ID", typeof(int));
dt.Columns.Add("Status", typeof(string));
dt.Columns.Add("Lend Date", typeof(string));
dt.Columns.Add("Due Date", typeof(string));
bookmodel = EntityBroker.getBookModelByID(_book_model_id);
}
public ucListBooks()
{
InitializeComponent();
entity = new LibraryDBEntities();
dt = new DataTable();
dt.Columns.Add("Book Title", typeof(string));
dt.Columns.Add("Number of Books", typeof(string));
dt.Columns.Add("Avaiable to Rent", typeof(string));
dt.Columns.Add("Author", typeof(string));
dt.Columns.Add("Book Category", typeof(string));
dt.Columns.Add("Max Available Days To Rent", typeof(string));
dt.Columns.Add("Book Model ID", typeof(int));
}
public static int createMember(Member _member)
{
LibraryDBEntities entity = new LibraryDBEntities();
Member mb = new Member();
mb.MemberName = _member.MemberName;
mb.Address = _member.Address;
mb.PhoneNumber = _member.PhoneNumber;
mb.NIRC = _member.NIRC;
mb.Email = _member.Email;
entity.AddToMembers(mb);
int i = entity.SaveChanges();
return(i);
}
public static int updateBookModel(BooksModel bm)
{
LibraryDBEntities entity = new LibraryDBEntities();
var book_model_row = entity.BooksModels.Where(x => x.BookModelId == bm.BookModelId).SingleOrDefault();
book_model_row.BookTitle = bm.BookTitle;
book_model_row.BookDescription = bm.BookDescription;
book_model_row.Author = bm.Author;
book_model_row.PublisherName = bm.PublisherName;
book_model_row.PublishDate = bm.PublishDate;
book_model_row.BookCategory = bm.BookCategory;
book_model_row.MaxAvailableDayToRent = bm.MaxAvailableDayToRent;
book_model_row.RentalPricePerDay = bm.RentalPricePerDay;
int i = entity.SaveChanges();
return(i);
}
public static int createNewBookModel(BooksModel _book_model)
{
LibraryDBEntities entity = new LibraryDBEntities();
BooksModel book_model = new BooksModel();
book_model.BookTitle = _book_model.BookTitle;
book_model.BookDescription = _book_model.BookDescription;
book_model.Author = _book_model.Author;
book_model.PublisherName = _book_model.PublisherName;
book_model.BookCategory = _book_model.BookCategory;
book_model.PublishDate = _book_model.PublishDate;
book_model.RentalPricePerDay = _book_model.RentalPricePerDay;
book_model.MaxAvailableDayToRent = _book_model.MaxAvailableDayToRent;
entity.AddToBooksModels(book_model);
int i = entity.SaveChanges();
return(i);
}
private void btnRent_Click(object sender, EventArgs e)
{
//#TODO::ExceptionHandle
LibraryDBEntities entity = new LibraryDBEntities();
LibTran t = new LibTran();
int member_id = 0;
int book_id = 0;
try
{
book_id = Convert.ToInt32(txtBookID.Text);
}
catch
{
MessageBox.Show("BookID you entered is not valid type.");
return;
}
try
{
member_id = Convert.ToInt32(txtMemberID.Text);
}
catch
{
MessageBox.Show("MemberID you entered is not valid type.");
return;
}
Book book = EntityBroker.getBookByBookID(book_id);
if (book == null)
{
MessageBox.Show("BookID doesn't exit.");
return;
}
if (book.BookStatus != 1)
{
MessageBox.Show("Book is not avaiable to rent.");
return;
}
Member member;
member = EntityBroker.getMemberByMemberID(member_id);
if (member == null)
{
MessageBox.Show("MemberID doesn't exit.");
return;
}
t.MemberID = member_id;
t.BookID = book_id;
t.LendDate = DateTime.Now;
entity.AddToLibTrans(t);
try
{
entity.SaveChanges();
}catch {
MessageBox.Show("User not exit");
}
Book b = entity.Books.Where(x => x.BookID == t.BookID).SingleOrDefault();
b.BookStatus = 0;
int i = entity.SaveChanges();
if (i == 1)
{
try
{
ucListBooks booklist = new ucListBooks();
booklist.setMainWindowRefrence(MainWindowObject);
MainWindowObject.RequestContentChange(booklist);
}
catch {
MessageBox.Show("Exception. Please close the window manually. Sorry for inconvenience");
}
}
this.Close();
}
