public static ObservableCollection <Genre> GetGenresForBook(int bookid)
{
ObservableCollection <Genre> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select tl.MaTheLoai, tl.TenTheLoai from TheLoai tl, ChiTietTheLoaiSach ct where tl.MaTheLoai = ct.MaTheLoai and ct.MaSach = " + bookid);
if (reader != null)
{
result = new ObservableCollection <Genre>();
while (reader.Read())
{
var item = new Genre(reader.GetInt32(0));
item.BeginInit();
item.Name = (string)reader.GetValueDefault(1, null);
item.EndInit();
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <Bill> GetOldBillsOfDebtors()
{
ObservableCollection <Bill> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select MaHoaDon, NgayLap, MaKhachHang, TienTra from HoaDon where TienTra < TongTien");
if (reader != null)
{
result = new ObservableCollection <Bill>();
while (reader.Read())
{
var id = reader.GetInt32(0);
var item = new Bill(id, reader.GetDateTime(1));
item.BeginInit();
item.Customer = CustomerAdapter.GetCustomer(reader.GetInt32(2));
item.PayMoney = reader.GetInt32(3);
foreach (var i in BillItemAdapter.GetBillItems(id))
{
item.BillItems.Add(i);
}
item.EndInit();
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static Customer GetCustomer(int _id)
{
Customer result = null;
try
{
var reader = DataConnector.ExecuteQuery("select MaKhachHang,HoTen,SoTienNo,DiaChi,DienThoai,Email,BiXoa from KhachHang where MaKhachHang = " + _id);
if (reader != null)
{
while (reader.Read())
{
var item = new Customer(reader.GetInt32(0));
item.BeginInit();
item.Name = (string)reader.GetValueDefault(1, null);
item.Debt = (int)reader.GetValueDefault(2, 0);
item.Adress = (string)reader.GetValueDefault(3, null);
item.Phone = (string)reader.GetValueDefault(4, null);
item.Email = (string)reader.GetValueDefault(5, null);
item.IsDeleted = (bool)reader.GetValueDefault(6, false);
item.IsDeletedItem = item.IsDeleted;
item.EndInit();
result = item;
break;
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <Customer> GetDeletedCustomers()
{
ObservableCollection <Customer> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select MaKhachHang,HoTen,SoTienNo,DiaChi,DienThoai,Email from KhachHang where BiXoa = 'true'");
if (reader != null)
{
result = new ObservableCollection <Customer>();
while (reader.Read())
{
var item = new Customer(reader.GetInt32(0));
item.BeginInit();
item.Name = (string)reader.GetValueDefault(1, null);
item.Debt = (int)reader.GetValueDefault(2, 0);
item.Adress = (string)reader.GetValueDefault(3, null);
item.Phone = (string)reader.GetValueDefault(4, null);
item.Email = (string)reader.GetValueDefault(5, null);
item.IsDeleted = true;
item.IsDeletedItem = item.IsDeleted;
item.EndInit();
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <AddedBook> GetAllListAddedBook(int id)
{
ObservableCollection <AddedBook> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select MaPhieuNhap,MaSach,SoLuong from ChiTietPhieuNhap where MaPhieuNhap = " + id);
if (reader != null)
{
result = new ObservableCollection <AddedBook>();
while (reader.Read())
{
var ID = reader.GetInt32(0);
var item = new AddedBook(ID);
item.BeginInit();
item.Book = BookAdapter.GetBook(reader.GetInt32(1));
item.Number = reader.GetInt32(2);
item.EndInit();
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static Account GetAccount(int id, bool findDeletedToo = false)
{
Account result = null;
try
{
var reader = DataConnector.ExecuteQuery(
string.Format(@"select tk.mataikhoan, tk.email, tk.matkhau, tk.hoten, tk.maphanquyen, tk.bixoa " +
"from taikhoan tk " +
"where tk.mataikhoan = {0} {1} and tk.maphanquyen = pq.maphanquyen", id, findDeletedToo ? "" : "and tk.bixoa = 'false'"));
if (reader != null)
{
while (reader.Read())
{
var item = new Account(reader.GetInt32(0));
item.BeginInit();
item.Email = (string)reader.GetValueDefault(1, null);
item.Password = (string)reader.GetValueDefault(2, null);
item.Name = (string)reader.GetValueDefault(3, null);
item.AccessLevel = AccessLevelAdapter.GetAcessLevelById(reader.GetInt32(4));
item.IsDeleted = (bool)reader.GetValueDefault(5, false);
item.IsDeletedItem = item.IsDeleted;
item.EndInit();
result = item;
break;
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <PayDebtMoney> GetAllPays()
{
ObservableCollection <PayDebtMoney> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select MaPhieuThu, MaKhachHang, NgayThu, SoTienThu, MaTaiKhoan from PhieuThuTien");
if (reader != null)
{
result = new ObservableCollection <PayDebtMoney>();
while (reader.Read())
{
result.Add(new PayDebtMoney()
{
IDPayDebt = reader.GetInt32(0),
Customer = Adapters.CustomerAdapter.GetCustomer(reader.GetInt32(1)),
DateCreated = reader.GetDateTime(2),
PayMoney = reader.GetInt32(3),
IDManager = reader.GetInt32(4)
});
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeInsert.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <Author> GetAuthorsForBook(int bookid)
{
ObservableCollection <Author> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select tg.MaTacGia, tg.TenTacGia from TacGia tg, ChiTietTacGiaSach ct where tg.MaTacGia = ct.MaTacGia and ct.MaSach = " + bookid);
if (reader != null)
{
result = new ObservableCollection <Author>();
while (reader.Read())
{
var item = new Author(reader.GetInt32(0));
item.BeginInit();
item.Name = (string)reader.GetValueDefault(1, null);
item.EndInit();
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <Account> GetDeletedAccounts()
{
ObservableCollection <Account> result = null;
try
{
var reader = DataConnector.ExecuteQuery(
@"select tk.mataikhoan, tk.email, tk.matkhau, tk.hoten, tk.maphanquyen " +
"from taikhoan tk " +
"where tk.bixoa = 'true'");
if (reader != null)
{
result = new ObservableCollection <Account>();
while (reader.Read())
{
var item = new Account(reader.GetInt32(0));
item.BeginInit();
item.Email = (string)reader.GetValueDefault(1, null);
item.Password = (string)reader.GetValueDefault(2, null);
item.Name = (string)reader.GetValueDefault(3, null);
item.AccessLevel = AccessLevelAdapter.GetAcessLevelById(reader.GetInt32(4));
item.IsDeleted = true;
item.IsDeletedItem = item.IsDeleted;
item.EndInit();
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <Author> GetAll()
{
ObservableCollection <Author> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select MaTacGia,TenTacGia from TacGia");
if (reader != null)
{
result = new ObservableCollection <Author>();
while (reader.Read())
{
var item = new Author(reader.GetInt32(0));
item.BeginInit();
item.Name = (string)reader.GetValueDefault(1, null);
item.EndInit();
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static bool IsExists(string username, string password, out Account result, bool findDeletedToo = false)
{
result = null;
try
{
password = crypto.encodeMD5(crypto.encodeSHA1(password));
var reader = DataConnector.ExecuteQuery(
string.Format(@"select tk.mataikhoan, tk.email, tk.matkhau, tk.hoten, tk.maphanquyen, tk.bixoa " +
"from taikhoan tk " +
"where tk.email = '{0}' and tk.matkhau = '{1}' {2}", username, password, findDeletedToo ? "" : "and tk.bixoa = 'false'"));
if (reader != null)
{
while (reader.Read())
{
var item = new Account(reader.GetInt32(0));
item.BeginInit();
item.Email = (string)reader.GetValueDefault(1, null);
item.Password = (string)reader.GetValueDefault(2, null);
item.Name = (string)reader.GetValueDefault(3, null);
item.AccessLevel = AccessLevelAdapter.GetAcessLevelById(reader.GetInt32(4));
item.IsDeleted = (bool)reader.GetValueDefault(5, false);
item.IsDeletedItem = item.IsDeleted;
item.EndInit();
result = item;
break;
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result != null);
}
public static ObservableCollection <ManagerListAddedBook> GetImported()
{
ObservableCollection <ManagerListAddedBook> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select MaPhieuNhap,NgayNhapKho,NgayTaoPhieu,MaTaiKhoan from PhieuNhap where DaNhap = 'true'");
if (reader != null)
{
result = new ObservableCollection <ManagerListAddedBook>();
while (reader.Read())
{
var id = reader.GetInt32(0);
var item = new ManagerListAddedBook(id);
item.BeginInit();
item.DateCreated = reader.GetDateTime(1);
item.DateAddIntoStorage = reader.GetDateTime(2);
item.IDManager = (int)reader.GetValueDefault(3, 0);
item.EndInit();
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static void DeleteAddedBook(AddedBook ab)
{
try
{
DataConnector.ExecuteNonQuery(string.Format("delete from ChiTietPhieuNhap where MaPhieuNhap={0} and MaSach={1}", ab.ID, ab.Book.ID));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeInsert.Call(ex.Message);
}
}
public static void UpdateAddedBook(AddedBook ab)
{
try
{
DataConnector.ExecuteNonQuery(string.Format("update ChiTietPhieuNhap set SoLuong={0} where MaPhieuNhap={1} and MaSach={2} ", ab.Number, ab.ID, ab.Book.ID));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeInsert.Call(ex.Message);
}
}
public static void UpdateIsImported(ManagerListAddedBook mlab)
{
try
{
DataConnector.ExecuteNonQuery(string.Format("update PhieuNhap set DaNhap='true' where MaPhieuNhap={0}", mlab.ID));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
}
public static void Update(ManagerListAddedBook mlab)
{
try
{
DataConnector.ExecuteNonQuery(string.Format("update PhieuNhap set NgayNhapKho='{0}' where MaPhieuNhap={1}", mlab.DateAddIntoStorage, mlab.ID));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
}
public static void Delete(ManagerListAddedBook mlab)
{
try
{
DataConnector.ExecuteNonQuery("delete from PhieuNhap where MaPhieuNhap=" + mlab.ID);
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
}
public static int Insert(string name)
{
try
{
return(DataConnector.ExecuteNonQuery(string.Format("insert into TacGia (TenTacGia) values (N'{0}')", name)));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeInsert.Call(ex.Message);
}
return(-1);
}
public static int Insert(ManagerListAddedBook mlab)
{
try
{
DataConnector.ExecuteNonQuery(string.Format("insert into PhieuNhap(NgayNhapKho,NgayTaoPhieu,MaTaiKhoan) values('{0}','{1}',{2})", mlab.DateAddIntoStorage, DateTime.Now, mlab.IDManager));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeInsert.Call(ex.Message);
}
return(-1);
}
public static int FixedBillsOverTime()
{
try
{
return(DataConnector.ExecuteNonQuery(string.Format("update HoaDon set DaLuu=1 where DaLuu=0 and DATEDIFF(day, '{0}', NgayLap) < 0", DateTime.Now)));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeUpdate.Call(ex.Message);
}
return(-1);
}
public static int UpdateBillItem(int billid, BillItem item)
{
try
{
return(DataConnector.ExecuteNonQuery(string.Format("update ChiTietHoaDon set SoLuong={0} where MaHoaDon={1} and MaSach = {2}", item.Number, billid, item.Book.ID)));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeUpdate.Call(ex.Message);
}
return(-1);
}
public static int Delete(int id)
{
try
{
return(DataConnector.ExecuteNonQuery(string.Format("delete from TacGia where MaTacGia = {0}", id)));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeDelete.Call(ex.Message);
}
return(-1);
}
public static int Update(Author which)
{
try
{
return(DataConnector.ExecuteNonQuery(string.Format("update TacGia set TenTacGia = N'{0}' where MaTacGia = {1}", which.Name, which.ID)));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeUpdate.Call(ex.Message);
}
return(-1);
}
public static int DeleteBill(int id)
{
try
{
return(DataConnector.ExecuteNonQuery("delete from HoaDon where MaHoaDon = " + id));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeUpdate.Call(ex.Message);
}
return(-1);
}
public static int ClearBillItems(int billid)
{
try
{
return(DataConnector.ExecuteNonQuery(string.Format("delete from ChiTietHoaDon where MaHoaDon = {0}", billid)));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeDelete.Call(ex.Message);
}
return(-1);
}
public static int AddGenre(int bookid, int genreid)
{
try
{
return(DataConnector.ExecuteNonQuery(string.Format("insert into ChiTietTheLoaiSach (MaSach, MaTheLoai) values ({0}, {1})", bookid, genreid)));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeInsert.Call(ex.Message);
}
return(-1);
}
public static int UpdateOldBill(Bill bill)
{
try
{
return(DataConnector.ExecuteNonQuery(string.Format("update HoaDon set TienTra={0},TongTien={1} where MaHoaDon={2} and DaLuu=0", bill.PayMoney, bill.TotalMoney, bill.ID)));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeUpdate.Call(ex.Message);
}
return(-1);
}
public static int RemoveAuthorFromBook(int bookid, int authorid)
{
try
{
return(DataConnector.ExecuteNonQuery(string.Format("delete from ChiTietTacGiaSach where MaSach = {0} and MaTacGia = {1}", bookid, authorid)));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeDelete.Call(ex.Message);
}
return(-1);
}
public static int InsertAddedBookItem(int ID, int IDBook, AddedBook ab)
{
try
{
DataConnector.ExecuteNonQuery(string.Format("insert into ChiTietPhieuNhap(MaPhieuNhap,MaSach,SoLuong) values({0},{1},{2})", ID, IDBook, ab.Number));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeInsert.Call(ex.Message);
}
return(-1);
}
public static int ClearGenres(int bookid)
{
try
{
return(DataConnector.ExecuteNonQuery("delete from ChiTietTheLoaiSach where MaSach = " + bookid));
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeDelete.Call(ex.Message);
}
return(-1);
}
