/* GET BILL BY PAGE NUMBER */
public static List <Tuple <Order, OrderDetail> > GetDetail(int ID, int page_size, int page_number)
{
List <Tuple <Order, OrderDetail> > lst = new List <Tuple <Order, OrderDetail> >();
Tuple <Order, OrderDetail> tup;
using (var db = new DBShop())
{
/* get orders by page number */
var order = db.DbOrder.Where(item => item.id_customer == ID).OrderByDescending(item => item.date_create).Skip((page_number - 1) * page_size).Take(page_size).ToList();
for (int i = 0; i < order.Count; i++)
{
int id = order[i].id;
/* get bills by page number */
var bill = db.DbBill.Where(item => item.id_order == id).OrderByDescending(item => item.id).Skip((page_number - 1) * page_size).Take(page_size).ToList();
for (int j = 0; j < bill.Count; j++)
{
tup = new Tuple <Order, OrderDetail>(order[i], bill[j]);
lst.Add(tup);
}
}
db.Dispose();
}
return(lst);
}
/* GET VIEWS COUNTING ALL MONTHS */
public static List <TotalVisit> Get_Visit()
{
using (var db = new DBShop())
{
var tmp = db.DbVisit.OrderBy(item => item.month).ToList();
db.Dispose();
return(tmp);
}
}
/* Create Record */
public static void Create_Record(string Username, string Action, DateTime Date_Action)
{
using (var db = new DBShop())
{
db.DbRecord.Add(new Record { username = Username, action = Action, date_action = Date_Action});
db.SaveChanges();
db.Dispose();
}
}
/* FIND VIEWS COUNTING BY MONTH */
public static TotalVisit Find_Month(int Month)
{
using (var db = new DBShop())
{
var result = db.DbVisit.Where(item => item.month == Month).FirstOrDefault();
db.Dispose();
return(result);
}
}
/* SET ROLE FOR USER */
public static void Create_Staff(int Id, string Role)
{
using (var db = new DBShop())
{
var staff = db.DbCustomer.Find(Id);
staff.role = Role;
db.Entry(staff).State = EntityState.Modified;
db.SaveChanges();
db.Dispose();
}
}
/* FIND CUSTOMER BY EMAIL */
public static Customer CheckCustomer(string Email)
{
Customer customer = null;
using (var db = new DBShop())
{
customer = db.DbCustomer.Where(item => item.email == Email).FirstOrDefault();
db.Dispose();
}
return(customer);
}
/* DELETE BOOK */
public static void Delete_Book(int Id)
{
using (var db = new DBShop())
{
var book = db.DbBook.Find(Id);
book.flag = true;
db.Entry(book).State = EntityState.Modified;
db.SaveChanges();
db.Dispose();
}
}
/* FIND BOOK BY GENRE */
public static List <Book> Get_Book_By_Genre(string Search_book)
{
List <Book> list_book = null;
using (var db = new DBShop())
{
list_book = db.DbBook.Where(item => item.the_loai.Contains(Search_book) && item.flag == false).ToList();
db.Dispose();
}
return(list_book);
}
/* FIND LIST BOOK BY NAME, AUTHOR, GENRE */
public static List <Book> Find_Book(string Search_book)
{
List <Book> list_book = null;
using (var db = new DBShop())
{
list_book = db.DbBook.Where(item => (item.ten_sach.Contains(Search_book) || item.the_loai.Contains(Search_book) || item.tac_gia.Contains(Search_book)) && item.flag == false).ToList();
db.Dispose();
}
return(list_book);
}
/* FIND BOOK BY NAME */
public static Book find(string Name)
{
Book book;
using (var db = new DBShop())
{
book = db.DbBook.Where(item => item.ten_sach == Name).FirstOrDefault();
db.Dispose();
}
return(book);
}
/* FIND BOOK BY ID */
public static Book Find_Book(int id)
{
Book book = null;
using (var db = new DBShop())
{
book = db.DbBook.Find(id);
db.Dispose();
}
return(book);
}
/* RESTORE BOOK */
public static void Restore_Book(int Id)
{
using (var db = new DBShop())
{
var tmp = db.DbBook.Find(Id);
tmp.flag = false;
db.Entry(tmp).State = EntityState.Modified;
db.SaveChanges();
db.Dispose();
}
}
/* LIST CUSTOMERS WERE DELETED */
public static List <Customer> Get_Customer_Delete(int page_number, int page_size)
{
List <Customer> list_customer = null;
using (var db = new DBShop())
{
list_customer = db.DbCustomer.Where(item => item.flag == true).OrderBy(item => item.first_name).Skip((page_number - 1) * page_size).Take(page_size).ToList();
db.Dispose();
}
return(list_customer);
}
/* GET CURRENT ORDER */
public static int Find_Order()
{
int id_order;
using (var db = new DBShop())
{
id_order = db.DbOrder.Max(item => item.id);
db.Dispose();
}
return(id_order);
}
/* INCREATE VIEWS COUNTING */
public static void Change_Visit(int Visit_Count, int Month)
{
using (var db = new DBShop())
{
var visit = db.DbVisit.Where(item => item.month == Month).FirstOrDefault();
visit.total_view = visit.total_view + Visit_Count;
db.Entry(visit).State = EntityState.Modified;
db.SaveChanges();
db.Dispose();
}
}
/* CREATE VIEWS COUNTING */
public static void Create_Visit(int Visit_Count, int Month)
{
using (var db = new DBShop())
{
db.DbVisit.Add(new TotalVisit {
total_view = Visit_Count, month = Month
});
db.SaveChanges();
db.Dispose();
}
}
/* CREATE ORDER */
public static void Create_Order(int Id_Customer, string Date_Create)
{
using (var db = new DBShop())
{
db.DbOrder.Add(new Order {
id_customer = Id_Customer, date_create = Date_Create
});
db.SaveChanges();
db.Dispose();
}
}
/* CHECK LOGIN CUSTOMER */
public static Customer Find_Customer(string Email, string Password)
{
Customer customer = null;
using (var db = new DBShop())
{
customer = db.DbCustomer.Where(item => item.email == Email && item.password == Password && item.flag == false).FirstOrDefault();
db.Dispose();
}
return(customer);
}
/* UPDATE IMAGE BOOK */
public static void Update_Img(int Id, string Hinh)
{
using (var db = new DBShop())
{
var book = db.DbBook.Find(Id);
book.hinh = Hinh;
db.Entry(book).State = EntityState.Modified;
db.SaveChanges();
db.Dispose();
}
}
/* LIST BOOKS WERE DELETED */
public static List <Book> Get_Book_Delete(int page_number, int page_size)
{
List <Book> list_book = null;
using (var db = new DBShop())
{
list_book = db.DbBook.Where(item => item.flag == true).OrderBy(a => a.the_loai).
Skip((page_number - 1) * page_size).Take(page_size).ToList();
db.Dispose();
}
return(list_book);
}
/* FIND CUSTOMER BY EMAIL, FIRSTNAME, LASTNAME */
public static List <Customer> Find_Customer(string Email)
{
List <Customer> list_customer = null;
using (var db = new DBShop())
{
list_customer = db.DbCustomer.
Where(item => item.email.Contains(Email) || item.first_name.Contains(Email) || item.last_name.Contains(Email)).
OrderBy(item => item.first_name).ToList();
db.Dispose();
}
return(list_customer);
}
/* DELETE CUSTOMER */
public static void Delete_Customer(int Id)
{
if (Id != 0)
{
using (var db = new DBShop())
{
var customer = db.DbCustomer.Find(Id);
customer.flag = true;
db.Entry(customer).State = EntityState.Modified;
db.SaveChanges();
db.Dispose();
}
}
}
/* RESTORE CUSTOMER ACCOUNT */
public static void UnLock(int Id)
{
using (var db = new DBShop())
{
var kh = db.DbCustomer.Find(Id);
if (kh.flag)
{
kh.flag = false;
db.Entry(kh).State = EntityState.Modified;
db.SaveChanges();
db.Dispose();
}
}
}
/* TOP CUSTOMER BUY BOOKS */
public static List <Customer> TopCustomer()
{
List <Customer> lst = new List <Customer>();
using (var db = new DBShop())
{
var tmp = db.DbOrder.GroupBy(item => item.id_customer).Select(item => new { Id = item.Key, Count = item.Count() }).OrderBy(item => item.Count).Take(10).ToList();
foreach (var item in tmp)
{
lst.Add(CustomerAction.Find_Customer(item.Id));
}
db.Dispose();
}
return(lst);
}
/* EDIT BOOK */
public static void Edit_Book(int Id, string Ten_Sach, string Tac_Gia, string The_Loai, string Nha_Xuat_Ban, string Mo_Ta, double Gia_Ban)
{
using (var db = new DBShop())
{
var book = db.DbBook.Find(Id);
book.ten_sach = Ten_Sach;
book.tac_gia = Tac_Gia;
book.the_loai = The_Loai;
book.nha_xuat_ban = Nha_Xuat_Ban;
book.mo_ta = Mo_Ta;
book.gia_ban = Gia_Ban;
db.Entry(book).State = EntityState.Modified;
db.SaveChanges();
db.Dispose();
}
}
/* PROFIT */
public static List <Tuple <string, double> > Profits()
{
List <Tuple <string, double> > lst = new List <Tuple <string, double> >();
Tuple <string, double> tup;
using (var db = new DBShop())
{
var tmp = db.DbBill.GroupBy(item => item.date_buy.Contains(DateTime.Now.Month.ToString())).Select(item => new { ID = DateTime.Now.Month.ToString(), Total = item.Sum(total => total.thanh_tien) });
foreach (var item in tmp)
{
tup = new Tuple <string, double>(item.ID, item.Total);
lst.Add(tup);
}
db.Dispose();
}
return(lst);
}
/* Auto Delete Record After N Days */
public static void Auto_Delete_Record(int Day_Delete)
{
using (var db = new DBShop())
{
var record = db.DbRecord.ToList();
for(int i = 0; i < record.Count(); i++)
{
int day = DateTime.Now.Date.Subtract(record[i].date_action).Days;
if ( day > Day_Delete)
{
db.Entry(record[i]).State = EntityState.Deleted;
db.SaveChanges();
}
}
db.Dispose();
}
}
/* FIND CUSTOMER BY ID */
public static Customer Find_Customer(int Id)
{
Customer customer = null;
if (Id != 0)
{
using (var db = new DBShop())
{
customer = db.DbCustomer.Where(item => item.id_customer == Id).FirstOrDefault();
db.Dispose();
}
return(customer);
}
else
{
return(customer);
}
}
/* CREATE BOOK */
public static void Create_Book(string Ten_Sach, string Tac_Gia, string The_Loai, string Nha_Xuat_Ban, string Mo_Ta, double Gia_Ban, string Hinh)
{
using (var db = new DBShop())
{
db.DbBook.Add(new Book
{
ten_sach = Ten_Sach,
tac_gia = Tac_Gia,
the_loai = The_Loai,
nha_xuat_ban = Nha_Xuat_Ban,
mo_ta = Mo_Ta,
gia_ban = Gia_Ban,
hinh = Hinh,
flag = false
});
db.SaveChanges();
db.Dispose();
}
}
/* TOP BOOKS WERE BOUGHT */
public static List <Tuple <Book, int> > BestSeller()
{
List <Tuple <Book, int> > lst = new List <Tuple <Book, int> >();
Tuple <Book, int> tup;
using (var db = new DBShop())
{
var tmp = db.DbBill.GroupBy(item => item.id_sach).Select(item => new { ID = item.Key, Count = item.Count() }).OrderByDescending(item => item.Count).Take(10);
foreach (var item in tmp)
{
tup = new Tuple <Book, int>(BookAction.Find_Book(item.ID), item.Count);
lst.Add(tup);
break;
}
db.Dispose();
}
return(lst);
}
