//View detail book by book ID
public JsonResult Detail(int id)
{
using (var db = new BookManagementEntities())
{
var model = db.Books.Where(book => book.BookID == id).Join(db.Categories, b => b.CategoryID, c => c.CategoryID, (b, c) => new { b, c }).
Join(db.Authors, bo => bo.b.AuthorID, au => au.AuthorID, (bo, au) => new { bo, au }).
Join(db.Publishers, bop => bop.bo.b.PublisherID, p => p.PublisherID, (bop, p) => new BookViewModel
{
BookID = bop.bo.b.BookID,
Title = bop.bo.b.Title,
CategoryName = bop.bo.c.CategoryName,
Description = bop.bo.b.Description,
CreatedDate = bop.bo.b.CreatedDate,
ModifiedDate = bop.bo.b.ModifiedDate,
ISBN = bop.bo.b.ISBN,
PublisherName = p.PublisherName,
AuthorName = bop.au.AuthorName,
Quantity = bop.bo.b.Quantity,
Price = bop.bo.b.Price,
Picture = bop.bo.b.Picture,
}).FirstOrDefault();
model.CreatedDate = model.CreatedDate.Date;
model.ModifiedDate = model.ModifiedDate.Date;
var serializer = new JavaScriptSerializer();
serializer.MaxJsonLength = Int32.MaxValue;
return(Json(model, JsonRequestBehavior.AllowGet));
}
}
public int CheckAccount(string accountName, string password)
{
using (var db = new BookManagementEntities())
{
var account = db.Accounts.Where(m => m.AccountName == accountName && m.Password == password).FirstOrDefault();
if (null != account)
{
if (account.IsActive)
{
return(account.AccountID); //account actived - get account id
}
else
{
return(-1); // account locked
}
}
else if (null != db.Accounts.Where(m => m.AccountName == accountName || m.Password == password).SingleOrDefault())
{
return(-2); //username or password not true
}
else
{
return(0); //account not exist
}
}
}
public void UpdateOrderStatus(int orderID, int idStatus)
{
using (var db = new BookManagementEntities())
{
using (var dbContextTransaction = db.Database.BeginTransaction())
{
try
{
var order = db.Orders.Where(s => s.OrderID == orderID).FirstOrDefault();
if (idStatus == 1)
{
order.OrderStatusID = 2;
order.ModifiredDate = DateTime.Now;
}
else
{
order.OrderStatusID = 3;
order.ModifiredDate = DateTime.Now;
}
db.SaveChanges();
dbContextTransaction.Commit();
}
catch (Exception)
{
dbContextTransaction.Rollback();
}
}
}
}
public void UpdateStatusOrder_Customer(int orderID)
{
using (var db = new BookManagementEntities())
{
using (var dbContextTransaction = db.Database.BeginTransaction())
{
try
{
var order = db.Orders.Where(s => s.OrderID == orderID).FirstOrDefault();
order.OrderStatusID = 4;
db.Entry(order).State = EntityState.Modified;
db.SaveChanges();
var orderDetail = db.OrderDetails.Where(s => s.OrderID == orderID).ToList();
foreach (var item in orderDetail)
{
var book = db.Books.Where(s => s.BookID == item.BookID).FirstOrDefault();
book.Quantity += item.Quantity;
}
db.SaveChanges();
dbContextTransaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
dbContextTransaction.Rollback();
}
}
}
}
public List <Comment> LoadComment(int id)
{
using (var db = new BookManagementEntities())
{
var list = db.Comments.Where(s => s.IsActive == true && s.BookID == id).OrderByDescending(s => s.CreatedDate).ToList();
return(list);
}
}
public int GetQuantityConfirm()
{
using (var db = new BookManagementEntities())
{
var res = db.Orders.Where(s => s.IsActive == true && (s.OrderStatusID == 1 || s.OrderStatusID == 2)).Count();
return(res);
}
}
//Delete comment
public void DeleteComment(int idComment)
{
using (var db = new BookManagementEntities())
{
var res = db.Comments.Where(s => s.CommentID == idComment).FirstOrDefault();
res.IsActive = false;
db.SaveChanges();
}
}
// Add Reply
public void AddReply(string replyContent, int accountID, int idComment)
{
using (var db = new BookManagementEntities())
{
Reply res = new Reply();
res.CommentID = idComment;
res.AccountID = accountID;
res.ReplyContent = replyContent;
res.ReplyDate = DateTime.Now;
db.Replies.Add(res);
db.SaveChanges();
}
}
public void SendComment(int bookID, string commentContent)
{
using (var db = new BookManagementEntities())
{
Comment cmt = new Comment();
cmt.CommentContent = commentContent;
cmt.CreatedDate = DateTime.Now;
cmt.BookID = bookID;
cmt.IsActive = true;
db.Comments.Add(cmt);
db.SaveChanges();
}
}
//get order by account id
public static List <Order> GetOrder(int id)
{
using (var db = new BookManagementEntities())
{
try
{
return(db.Orders.Where(m => m.AccountCustomerID == id).Include(m => m.OrderDetails.Select(c => c.Book)).Include(m => m.OrderStatu).OrderByDescending(m => m.ModifiredDate).ToList());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(new List <Order>());
}
}
}
//get list publishers which are active
public static List <Publisher> GetListPublisher()
{
using (var db = new BookManagementEntities())
{
try
{
return(db.Publishers.Where(m => m.IsActive).ToList());
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(new List <Publisher>());
}
}
}
// Send mail nge dai ca
public JsonResult CancelOrder(int orderID) //
{
dao.CancelOrder(orderID);
string emailCustomer;
using (BookManagementEntities db = new BookManagementEntities())
{
var accountId = db.Orders.Where(m => m.OrderID == orderID).Select(m => m.Account.AccountID).SingleOrDefault();
emailCustomer = db.Customers.Where(m => m.AccountID == accountId).Select(m => m.CustomerEmail).FirstOrDefault();
}
return(Json(new
{
status = true,
email = emailCustomer
}));
}
public static List <Category> GetAllCategory()
{
using (var db = new BookManagementEntities())
{
try
{
var list = db.Categories.ToList();
return(list);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(new List <Category>());
}
}
}
public void CancelOrder(int orderID)
{
using (var db = new BookManagementEntities())
{
var order = db.Orders.Where(s => s.OrderID == orderID).FirstOrDefault();
order.OrderStatusID = 4;
order.ModifiredDate = DateTime.Now;
var orderDetail = db.OrderDetails.Where(s => s.OrderID == orderID).ToList();
foreach (var item in orderDetail)
{
var book = db.Books.Where(s => s.BookID == item.BookID).FirstOrDefault();
book.Quantity += item.Quantity;
}
db.SaveChanges();
}
}
public static Author GetAuthor(int id)
{
using (var db = new BookManagementEntities())
{
try
{
var a = db.Authors.Where(m => m.AuthorID == id).Include(b => b.Books).FirstOrDefault();
return(a);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(null);
}
}
}
//find customer by customer id
public static Customer FindCustomer(int id)
{
using (var db = new BookManagementEntities())
{
try
{
var customer = db.Customers.Include(m => m.Account).FirstOrDefault(m => m.CustomerID == id);
return(customer);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
}
//Get book by book id
public static Book GetBook(int id)
{
using (var db = new BookManagementEntities())
{
Book book = null;
try
{
book = db.Books.Include(m => m.Category).Include(m => m.Publisher).Include(m => m.Author).FirstOrDefault(b => b.BookID == id);
return(book);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
}
public IEnumerable <OrderDetailBookModel> LoadOrderDetail(int orderID)
{
using (var db = new BookManagementEntities())
{
var list = (from a in db.OrderDetails
join b in db.Books
on a.BookID equals b.BookID
where a.IsActive == true && a.OrderID == orderID
select new OrderDetailBookModel
{
BookName = b.Title,
Quantity = a.Quantity,
Money = a.Money
}).ToList();
return(list);
}
}
//create or edit book
public static int CreateOrEditBook(Book book)
{
using (var db = new BookManagementEntities())
{
try
{
db.Entry(book).State = book.BookID > 0? EntityState.Modified:EntityState.Added;
int ret = db.SaveChanges();
return(ret);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(0);
}
}
}
//get list books which are active
public static List <Book> GetListBooks()
{
using (var db = new BookManagementEntities())
{
List <Book> list = new List <Book>();
try
{
list = db.Books.Include(m => m.Author).Include(m => m.Category).Include(m => m.Publisher).Where(m => m.IsActive).ToList();
return(list);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(new List <Book>());
}
}
}
public OrderDetailCustomer GetInfoCustomer(int orderID)
{
using (var db = new BookManagementEntities())
{
var customer = (from a in db.Orders
join b in db.Customers
on a.AccountCustomerID equals b.AccountID
where a.OrderID == orderID
select new OrderDetailCustomer
{
CustomerName = b.CustomerName,
Email = b.CustomerEmail,
Phone = a.PhoneNumber,
Address = a.OrderAddress
}).SingleOrDefault();
return(customer);
}
}
// Load data Reply
public IEnumerable <ReplyAccount> LoadReply(int idcomment)
{
using (var db = new BookManagementEntities())
{
var list = (from a in db.Replies
join b in db.Accounts
on a.AccountID equals b.AccountID
where a.CommentID == idcomment
orderby a.ReplyDate descending
select new ReplyAccount
{
AccountName = b.AccountName,
ReplyContent = a.ReplyContent,
ReplyDate = a.ReplyDate
}).ToList();
return(list);
}
}
public List <ReplyAccount> LoadReply()
{
using (var db = new BookManagementEntities())
{
var list = (from a in db.Replies
join b in db.Accounts
on a.AccountID equals b.AccountID
orderby a.ReplyDate descending
select new ReplyAccount
{
AccountName = b.AccountName,
CommentID = a.CommentID,
ReplyDate = a.ReplyDate,
ReplyContent = a.ReplyContent
}).ToList();
return(list);
}
}
public static int Delete(int id)
{
using (var db = new BookManagementEntities())
{
try
{
var author = db.Authors.FirstOrDefault(m => m.AuthorID == id);
author.IsActive = false;
db.Entry(author).State = EntityState.Modified;
int ret = db.SaveChanges();
return(ret);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(0);
}
}
}
// Load data comment
public IEnumerable <CommentBookModels> Load()
{
using (var db = new BookManagementEntities())
{
var list = (from a in db.Comments
join b in db.Books
on a.BookID equals b.BookID
where a.IsActive == true
orderby a.CreatedDate descending
select new CommentBookModels
{
Title = b.Title,
Content = a.CommentContent,
CreatedDate = a.CreatedDate,
CommentID = a.CommentID,
BookID = b.BookID
}).ToList();
return(list);
}
}
//delete book by book ID
public static bool DeleteBook(int id)
{
using (var db = new BookManagementEntities())
{
try
{
//check this book exist in order
if (db.OrderDetails.FirstOrDefault(d => d.BookID == id) == null)
{
return(false);
}
Book book = db.Books.FirstOrDefault(m => m.BookID == id);
book.IsActive = false;
db.Entry(book).State = EntityState.Modified;
db.SaveChanges();
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
}
}
//public void CreateOrder()
//{
// //var currentCustomer = (CurrentCustomer)Session[CommonBox.SessionBox.CUSTOMER_SESSION];
// //customerID = currentCustomer.GetCustomerID();
// using (var db = new BookManagementEntities())
// {
// using (var dbContextTransaction = db.Database.BeginTransaction())
// {
// try
// {
// int customerID = 0;
// Order order = new Order();
// order.AccountCustomerID = customerID;
// order.OrderDate = DateTime.Now;
// order.TotalMoney = 0;
// order.ModifiredDate = DateTime.Now;
// order.OrderStatusID = 1;
// order.IsActive = true;
// db.Orders.Add(order);
// db.SaveChanges();
// }
// catch (Exception)
// {
// dbContextTransaction.Rollback();
// }
// }
// }
//}
//public void CreateOrder_Detail(int bookID, int quantity)
//{
// using (var db = new BookManagementEntities())
// using (var dbContextTransaction = db.Database.BeginTransaction())
// {
// try
// {
// OrderDetail orderDetail = new OrderDetail();
// var order = db.Orders.Where(s => s.IsActive == true).OrderByDescending(s => s.OrderID).FirstOrDefault(); // info of order
// var detailBook = db.Books.Where(s => s.BookID == bookID && s.IsActive == true).FirstOrDefault(); // info of book
// var money = detailBook.Price * quantity; // money
// orderDetail.OrderID = Convert.ToInt32(order.OrderID);
// orderDetail.BookID = bookID;
// orderDetail.Quantity = quantity;
// orderDetail.Money = money;
// orderDetail.IsActive = true;
// db.OrderDetails.Add(orderDetail);
// db.SaveChanges();
// detailBook.Quantity -= quantity; // update quantity of book after add orderDetail
// order.TotalMoney += money; //upadte total money of Order after add orderDetail
// db.SaveChanges();
// }
// catch (Exception)
// {
// dbContextTransaction.Rollback();
// }
// }
//}
public IEnumerable <OrderAccountModel> LoadOrder()
{
using (var db = new BookManagementEntities())
{
var list = (from a in db.Orders
join b in db.Accounts
on a.AccountCustomerID equals b.AccountID
join c in db.OrderStatus on a.OrderStatusID equals c.OrderStatusID
where a.IsActive == true
orderby a.OrderDate descending
select new OrderAccountModel
{
OrderID = a.OrderID,
OrderStatusID = c.OrderStatusID,
AccountCustomerName = b.AccountName,
OrderStatusName = c.OrderStatusName,
OrderDate = a.OrderDate,
ModifiedDate = a.ModifiredDate,
TotalMoney = a.TotalMoney
});
return(list.ToList());
}
}
public GenresDAO()
{
_db = new BookManagementEntities();
}
public UserDAO()
{
_db = new BookManagementEntities();
}
public BookStatusDAO()
{
_db = new BookManagementEntities();
}