public static List <Book> SearchBooks(string keyword)
{
using (var db = new BookShopEntities())
{
var books = db.Books.Where(x => x.Name.Contains(keyword))
.OrderBy(x => x.Name)
.ToList();
return(books);
}
}
public static int DeleteOrderDetail(int ID)
{
using (var db = new BookShopEntities())
{
var orderDetail = db.OrderDetails.Find(ID);
db.OrderDetails.Remove(orderDetail);
var result = db.SaveChanges();
return(result);
}
}
public static List <Book> GetBooksBelongToBookPublisher(int bookPublisherID)
{
using (var db = new BookShopEntities())
{
var books = db.Books
.Where(x => x.PublisherID == bookPublisherID)
.ToList();
return(books);
}
}
public static List <Book> GetBooksCategoryInOrder(int bookCategoryID)
{
using (var db = new BookShopEntities())
{
var books = db.Books
.Where(x => x.BookCategoryID == bookCategoryID)
.OrderBy(x => x.Price)
.ToList();
return(books);
}
}
public static List <Book> GetNewBooks()
{
using (var db = new BookShopEntities())
{
var newBooks = db.Books
.Where(x => x.New == true)
.OrderBy(x => x.Price)
.ToList();
return(newBooks);
}
}
private void IniMyStuff() {
// luodaan konteksti = datasisältö
ctx = new BookShopEntities();
// ladataan kirjojen tiedot paikalliseksi
ctx.Books.Load();
localBooks = ctx.Books.Local;
// täytetään combobox kirjailijoitten maiden nimillä
// huom: lambda-tyylin LINQ-kysely
cbCountries.DataContext = localBooks.Select(n => n.country).Distinct();
// luodaan view
view = CollectionViewSource.GetDefaultView(localBooks);
}
public ActionResult Delete(int id)
{
Book bookModel = new Book();
BookShopEntities entities = new BookShopEntities();
Book book = (from c in entities.Books where c.BookID == id select c).FirstOrDefault();
entities.Books.Remove(book);
entities.SaveChanges();
// ViewBag.Message = "Record is deleted.";
return(RedirectToAction("EditBookInfo"));
}
public static int UpdateOrderDetail(OrderDetail orderDetailEntity)
{
using (var db = new BookShopEntities())
{
var orderDetails = db.OrderDetails.Find(orderDetailEntity.ID);
orderDetails.OrderID = orderDetailEntity.OrderID;
orderDetails.BookID = orderDetailEntity.BookID;
orderDetails.Quantity = orderDetailEntity.Quantity;
orderDetails.Price = orderDetailEntity.Price;
orderDetails.TotalPrice = orderDetailEntity.TotalPrice;
var result = db.SaveChanges();
return(result);
}
}
//добав книг
private void bttnAddBook_Click(object sender, EventArgs e)
{
if (!String.IsNullOrWhiteSpace(txtBookAgeOfPublic.Text) && !String.IsNullOrWhiteSpace(txtBookPage.Text) && !String.IsNullOrWhiteSpace(txtBookPrice.Text) && !String.IsNullOrWhiteSpace(txtBookPublish.Text) &&
!String.IsNullOrWhiteSpace(txtBookTheme.Text) && !String.IsNullOrWhiteSpace(txtBookTitle.Text) && !String.IsNullOrWhiteSpace(txtLNAuthor.Text))
{
using (BookShopEntities db = new BookShopEntities())
{
int price = 0, age = 0, page = 0;
var au = db.Authors.Where(a => a.LastName == txtLNAuthor.Text).FirstOrDefault();
if (int.TryParse(txtBookPrice.Text, out price))
{
if (int.TryParse(txtBookAgeOfPublic.Text, out age))
{
if (int.TryParse(txtBookPage.Text, out page))
{
Books book = new Books
{
Title = txtBookTitle.Text,
Theme = txtBookTheme.Text,
Publisher = txtBookPublish.Text,
Price = price,
AgeOfReceipt = age,
Pages = page,
AuthorsID = au.ID
};
db.Books.Add(book);
db.SaveChanges();
}
else
{
MessageBox.Show("только числовые значения");
}
}
else
{
MessageBox.Show("только числовые значения");
}
}
else
{
MessageBox.Show("только числовые значения");
}
}
}
else
{
MessageBox.Show("Заполните все поля!");
}
}
public static int UpdateUser(User userEntity)
{
using (var db = new BookShopEntities())
{
var user = db.Users.Find(userEntity.ID);
user.Name = userEntity.Name;
user.UserName = userEntity.UserName;
user.Password = userEntity.Password;
user.Address = userEntity.Address;
user.Email = userEntity.Email;
user.Phone = userEntity.Phone;
var result = db.SaveChanges();
return(result);
}
}
private void bttnRt_Click(object sender, EventArgs e)
{
if (i != 0 && i != 4)
{
panel2.Visible = false;
panel3.Visible = false;
panel4.Visible = false;
panel1.Visible = false;
y = 3;
i -= 4;
int count = i - 4;
using (BookShopEntities db = new BookShopEntities())
{
var books1 = (from b in db.Books
from a in db.Authors
from sh in db.Shop
where a.ID == b.AuthorsID && sh.BooksID == b.ID
select sh).ToList();
if (books1 != null)
{
for (; i > count; i--)
{
if (books1.Count >= i)
{
if (y == 0)
{
panel1.Visible = true; Init(lblTitle1, lblAge1, lblTheme1, lblAuthor1, lblPrice1, pchrBxBook1, books1[i]); indexbttn1 = i;
}
if (y == 1)
{
panel2.Visible = true; Init(lblTitle2, lblAge2, lblTheme2, lblAuthor2, lblPrice2, pchrBxBook2, books1[i]); indexbttn2 = i;
}
if (y == 2)
{
panel3.Visible = true; Init(lblTitle3, lblAge3, lblTheme3, lblAuthor3, lblPrice3, pchrBxBook3, books1[i]); indexbttn3 = i;
}
if (y == 3)
{
panel4.Visible = true; Init(lblTitle4, lblAge4, lblTheme4, lblAuthor4, lblPrice4, pchrBxBook4, books1[i]); indexbttn4 = i;
}
y--;
}
}
}
}
}
}
public HttpResponseMessage Get(int id)
{
using (var db = new BookShopEntities())
{
var book = db.Books.Where(b => b.Id == id).FirstOrDefault();
if (book == null)
{
return(Request.CreateResponse(HttpStatusCode.NotFound));
}
else
{
return(Request.CreateResponse(HttpStatusCode.OK, book));
}
}
}
//изменение
private void bttnSale_Click(object sender, EventArgs e)
{
using (BookShopEntities db = new BookShopEntities())
{
using (DbContextTransaction dbTran = db.Database.BeginTransaction())
{
try
{
//МАГАЗИН
Shop s = db.Shop.Where(sp => sp.BooksID == temp).FirstOrDefault();
int price = 0, age = 0;
if (int.TryParse(txtBxPrice.Text, out price))
{
s.PriceSales = price;
}
else
{
MessageBox.Show("только числовые значения");
}
//КНИГИ
Books b = db.Books.Where(book => book.ID == temp).FirstOrDefault();
b.Title = txtBxTitle.Text;
b.Theme = txtBxTheme.Text;
if (int.TryParse(txtBxAge.Text, out age))
{
b.AgeOfReceipt = age;
}
else
{
MessageBox.Show("только числовые значения");
}
//Автор
Authors a = (from bk1 in db.Books
from author in db.Authors
where author.ID == bk1.AuthorsID && bk1.ID == temp
select author).FirstOrDefault();
a.LastName = txtBxAuthor.Text;
a.FirstName = txtBxAuthorName.Text;
db.SaveChanges();
dbTran.Commit();
}
catch (Exception ex) { dbTran.Rollback(); }
}
}
}
public void CleanDatabase()
{
var db = new BookShopEntities();
foreach (var lineItem in db.OrderLines)
{
db.DeleteObject(lineItem);
}
foreach (var order in db.Orders)
{
db.DeleteObject(order);
}
foreach (var book in db.Books)
{
db.DeleteObject(book);
}
db.SaveChanges();
}
public void GivenTheFollowingBooks(Table table)
{
var db = new BookShopEntities();
foreach (var row in table.Rows)
{
Book book = new Book {
Author = row["Author"], Title = row["Title"], Price = Convert.ToDecimal(row["Price"])
};
if (table.Header.Contains("Id"))
{
_catalogContext.ReferenceBooks.Add(row["Id"], book);
}
db.AddToBooks(book);
}
db.SaveChanges();
}
public static int UpdateBook(Book bookEntity)
{
using (var db = new BookShopEntities())
{
var book = db.Books.Find(bookEntity.ID);
book.Name = bookEntity.Name;
book.MetaTitle = bookEntity.MetaTitle;
book.Image = bookEntity.Image;
book.Price = bookEntity.Price;
book.Description = bookEntity.Description;
book.Quantity = bookEntity.Quantity;
book.PublisherID = bookEntity.PublisherID;
book.BookCategoryID = bookEntity.BookCategoryID;
var result = db.SaveChanges();
return(result);
}
}
public HttpResponseMessage Delete(int id)
{
using (var db = new BookShopEntities())
{
var rbook = db.Books.Where(b => b.Id == id).FirstOrDefault();
if (rbook == null)
{
return(Request.CreateResponse(HttpStatusCode.NotFound, "invalid id"));
}
else
{
db.Books.Remove(rbook);
db.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.OK, "book with id = " + id + " has been deleted"));
}
}
}
//добавление автора
private void dttnAddAuthor_Click(object sender, EventArgs e)
{
if (!String.IsNullOrWhiteSpace(txtBxAuthorFN.Text) && !String.IsNullOrWhiteSpace(txtBxAuthorLN.Text))
{
using (BookShopEntities db = new BookShopEntities())
{
Authors au = new Authors {
FirstName = txtBxAuthorFN.Text, LastName = txtBxAuthorLN.Text
};
db.Authors.Add(au);
db.SaveChanges();
}
}
else
{
MessageBox.Show("Заполните все поля!");
}
}
public ActionResult Maintain(int id = 0)
{
Book bookModel = new Book();
BookShopEntities db = new BookShopEntities();
var getCategoryList = db.Categories.ToList();
SelectList list = new SelectList(getCategoryList, "CategoryID", "Name");
ViewBag.CategoryListName = list;
if (id != 0)
{
bookModel = db.Books.Where(x => x.BookID == id).FirstOrDefault();
}
bookModel.CategoryCollection = db.Categories.ToList <Category>();
ViewBag.CategoryListName = list;
return(View(bookModel));
}
public ActionResult Search(string searchTerm)
{
BookShopEntities db = new BookShopEntities();
//List<Book> books = db.Books.Where(b => b.Title.Contains(searchTerm)).ToList();
var terms = searchTerm.Split(' ');
var predicate = PredicateBuilder.False <Book>();
foreach (string term in terms)
{
string temp = term;
predicate = predicate.Or(p => p.Title.Contains(temp));
}
List <Book> books = db.Books.AsExpandable().Where(predicate).ToList();
ViewData.Model = books;
return(View("List"));
}
public static List <string> GetCredentials(string userName)
{
var db = new BookShopEntities();
var user = db.Users.SingleOrDefault(x => x.UserName.Equals(userName));
var data = (from a in db.Credentials
join b in db.UserGroups on a.UserGroupID equals b.ID
join c in db.Roles on a.RoleID equals c.ID
where b.ID == user.UserGroupID
select new
{
RoleID = a.RoleID,
UserGroupID = a.UserGroupID
}).AsEnumerable().Select(x => new Credential
{
RoleID = x.RoleID,
UserGroupID = x.UserGroupID
});
return(data.Select(x => x.RoleID).ToList());
}
public ActionResult Search(string searchTerm)
{
using (var db = new BookShopEntities())
{
var terms = searchTerm.Split(' ');
var predicate = PredicateBuilder.New <Book>(false);
foreach (string term in terms)
{
string temp = term;
predicate = predicate.Or(p => p.Title.Contains(temp));
predicate = predicate.Or(p => p.Author.Contains(temp));
}
List <Book> books = db.Books.AsExpandable().Where(predicate).OrderBy(b => b.Title).ToList();
this.ViewData.Model = books;
return(this.View("List"));
}
}
private void MainControl_Load(object sender, EventArgs e)
{
using (BookShopEntities db = new BookShopEntities())
{
books = (from b in db.Books
from a in db.Authors
from sh in db.Shop
where a.ID == b.AuthorsID && sh.BooksID == b.ID
select sh).ToList();
Random rand = new Random();
index = rand.Next(0, books.Count - 1);
lblPrice.Text = "₴ " + (Convert.ToInt32(books[index].PriceSales - Convert.ToInt32(Convert.ToDouble(books[index].PriceSales) * 0.05)));
lblTitle.Text = books[index].Books.Title;
lblAuthor.Text = $"Автор: {books[index].Books.Authors.LastName} {books[index].Books.Authors.FirstName}";
lblTheme.Text = $"Жанр: {books[index].Books.Theme}";
lblAge.Text = $"Год: {books[index].Books.AgeOfReceipt.ToString()}";
pchrBxBook.Load(@".../ImajeBook/" + books[index].Picture);
}
}
public HttpResponseMessage Put(int id, [FromBody] Books book)
{
using (var db = new BookShopEntities())
{
var mbook = db.Books.Where(b => b.Id == id).FirstOrDefault();
if (mbook == null)
{
return(Request.CreateResponse(HttpStatusCode.NotFound, "invalid id"));
}
else
{
mbook.Title = book.Title;
mbook.Price = book.Price;
db.SaveChanges();
return(Request.CreateResponse(HttpStatusCode.OK, mbook));
}
}
}
//добав книги в магазин
private void button1_Click(object sender, EventArgs e)
{
if (!String.IsNullOrWhiteSpace(txtShopIdBook.Text) && !String.IsNullOrWhiteSpace(txtShopPicture.Text) &&
!String.IsNullOrWhiteSpace(txtShopPriceSale.Text) && !String.IsNullOrWhiteSpace(txtShopQuantity.Text))
{
int qunt = 0, priceSale = 0;
using (BookShopEntities db = new BookShopEntities())
{
var book = db.Books.Where(b => b.Title == txtShopIdBook.Text).FirstOrDefault();
if (int.TryParse(txtShopQuantity.Text, out qunt))
{
if (int.TryParse(txtShopPriceSale.Text, out priceSale))
{
Shop shop = new Shop {
Picture = txtShopPicture.Text,
QuantityBooks = qunt,
DateSales = DateTime.Today,
PriceSales = priceSale,
BooksID = book.ID
};
db.Shop.Add(shop);
db.SaveChanges();
}
else
{
MessageBox.Show("только числовые значения");
}
}
else
{
MessageBox.Show("только числовые значения");
}
}
}
else
{
MessageBox.Show("Заполните все поля!");
}
}
//bnntSearch Click
private void bttnSearch_Click(object sender, EventArgs e)
{
//проверка на не пустое поле поиска
if (!String.IsNullOrWhiteSpace(txtIdSearch.Text))
{
if (!int.TryParse(txtIdSearch.Text, out temp))
{
MessageBox.Show("Вы ввели не числовое значение!");
}
else
{
//подключение к БД и считывание таблиц
try
{
BookShopEntities db = new BookShopEntities();
books = (from b in db.Books
from a in db.Authors
from shp in db.Shop
where a.ID == b.AuthorsID && shp.BooksID == b.ID
select shp).ToList();
}
catch (Exception ex) { MessageBox.Show(ex.Message); }
//загрукзка информвции в форму
if (books.Count >= temp && books.Count > 0 && temp > 0)
{
sh = (from bk in books where bk.BooksID == temp select bk).First();
txtBxPrice.Text = Convert.ToInt32(sh.PriceSales).ToString();
txtBxTitle.Text = sh.Books.Title;
txtBxAuthor.Text = sh.Books.Authors.LastName;
txtBxAuthorName.Text = sh.Books.Authors.FirstName;
txtBxTheme.Text = sh.Books.Theme;
txtBxAge.Text = sh.Books.AgeOfReceipt.ToString();
pctrBx.Load(@".../ImajeBook/" + sh.Picture);
bttnSale.Visible = true;
}
}
}
}
public ActionResult Promotion()
{
BookShopEntities _db = new BookShopEntities();
var q = _db.Promotions.AsEnumerable().Where(s => s.Start_Time.Date <= DateTime.Now.Date && DateTime.Now.Date <= s.End_Time.Date).ToList();
List <CartItemsViewModel> aa = (from b in _db.Books
join c in _db.Categories on b.CategoryID equals c.CategoryID
select new CartItemsViewModel
{
CategoryName = c.Name,
CategoryID = b.CategoryID,
Cover = b.Cover,
Author = b.Author,
Title = b.Title,
ISBN = b.ISBN,
Price = b.Price,
BookID = b.BookID
}).Take(3).ToList();
ViewBag.promoBooks = aa;
return(View(q));
}
public ActionResult Maintain(Book model, HttpPostedFileBase image1)
{
var db = new BookShopEntities();
//model.CategoryID = CategoryList1;
if (ModelState.IsValid)
{
if (image1 != null)
{
model.Cover = new byte[image1.ContentLength];
image1.InputStream.Read(model.Cover, 0, image1.ContentLength);
}
model.CategoryCollection = db.Categories.ToList <Category>();
db.Books.Add(model);
db.SaveChanges();
ModelState.Clear();
ViewBag.SuccessMessage = "Successfully Save.";
}
// return RedirectToAction("EditBookInfo");
return(RedirectToAction("EditBookInfo"));
}
public ActionResult Add(int bookId)
{
BookShopEntities db = new BookShopEntities();
var shoppingCart = GetShoppingCart();
var existingLine = shoppingCart.Lines.SingleOrDefault(l => l.Book.Id == bookId);
if (existingLine != null)
{
existingLine.Quantity++;
}
else
{
var book = db.Books.First(b => b.Id == bookId);
OrderLine newOrderLine = new OrderLine();
newOrderLine.Book = book;
newOrderLine.Quantity = 1;
shoppingCart.AddLineItem(newOrderLine);
}
ViewData.Model = shoppingCart;
return RedirectToAction("Index");
}
public void GivenTheFollowingBooks(Table givenBooks)
{
using (var db = new BookShopEntities())
{
foreach (var row in givenBooks.Rows)
{
Book book = new Book {
Author = row["Author"], Title = row["Title"]
};
book.Price = givenBooks.Header.Contains("Price")
? Convert.ToDecimal(row["Price"])
: _bookDefaultPrice;
this._catalogContext.ReferenceBooks.Add(
givenBooks.Header.Contains("Id") ? row["Id"] : book.Title,
book);
db.AddToBooks(book);
}
db.SaveChanges();
}
}
//Покупка передача данных в БД через транзакцию в EF
private void bttnSale_Click(object sender, EventArgs e)
{
using (BookShopEntities db = new BookShopEntities())
{
using (DbContextTransaction dbTran = db.Database.BeginTransaction())
{
try
{
Sales sl = new Sales {
BooksID = sh.BooksID, DateOfSales = DateTime.Today, Quantity = 1, Price = (int)sh.PriceSales
};
db.Sales.Add(sl);
Shop s = db.Shop.Where(sp => sp.BooksID == sh.BooksID).FirstOrDefault();
s.QuantityBooks = s.QuantityBooks - 1;
db.SaveChanges();
dbTran.Commit();
}
catch (Exception ex) { dbTran.Rollback(); }
}
}
//освобождение ресурсов закрытие контроллера
this.Dispose();
}
public async Task <ActionResult> Register(RegisterViewModel model)
{
if (ModelState.IsValid)
{
var user = new ApplicationUser {
UserName = model.Email, Email = model.Email
};
var result = await UserManager.CreateAsync(user, model.Password);
if (result.Succeeded)
{
await SignInManager.SignInAsync(user, isPersistent : false, rememberBrowser : false);
// For more information on how to enable account confirmation and password reset please visit https://go.microsoft.com/fwlink/?LinkID=320771
// Send an email with this link
// string code = await UserManager.GenerateEmailConfirmationTokenAsync(user.Id);
// var callbackUrl = Url.Action("ConfirmEmail", "Account", new { userId = user.Id, code = code }, protocol: Request.Url.Scheme);
// await UserManager.SendEmailAsync(user.Id, "Confirm your account", "Please confirm your account by clicking <a href=\"" + callbackUrl + "\">here</a>");
await this.UserManager.AddToRoleAsync(user.Id, "User");
BookShopEntities db = new BookShopEntities();
db.Memberships.Add(new Membership
{
MemberID = user.Id,
CustomerName = model.Email,
Email = model.Email
});
db.SaveChanges();
return(RedirectToAction("Index", "Home"));
}
AddErrors(result);
}
// If we got this far, something failed, redisplay form
return(View(model));
}
public CategoriesController()
{
this.context = new BookShopEntities();
}
public BooksController()
{
this.context = new BookShopEntities();
}