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 <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 <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 <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> 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 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 <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 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 <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 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 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 int ExistNumberReport(int month, int year)
{
try
{
var reader = DataConnector.ExecuteQuery(string.Format("select MaBaoCao from BaoCaoTon where Thang = {0} and Nam = {1}", month, year));
if (reader != null)
{
while (reader.Read())
{
return(reader.GetInt32(0));
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(-1);
}
public static int Exist(string name)
{
try
{
var reader = DataConnector.ExecuteQuery(string.Format("select MaTacGia from TacGia where TenTacGia = N'{0}'", name));
if (reader != null)
{
if (reader.Read())
{
return(reader.GetInt32(0));
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(-1);
}
public static int GetNumber(int bookid)
{
try
{
var reader = DataConnector.ExecuteQuery("select SoLuongTon from Sach where MaSach = " + bookid);
if (reader != null)
{
if (reader.Read())
{
return(reader.GetInt32(0));
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeUpdate.Call(ex.Message);
}
return(-1);
}
public static int GetLatestId()
{
try
{
var reader = DataConnector.ExecuteQuery("select MAX(MaHoaDon) from HoaDon");
if (reader != null)
{
if (reader.Read())
{
return(reader.GetInt32(0));
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(-1);
}
public static ObservableCollection <Book> GetBooksForGenre(int genreid)
{
ObservableCollection <Book> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select s.MaSach,s.TenSach,s.AnhBia,s.SoLuongTon,s.DonGia,s.BiXoa " +
"from Sach s, ChiTietTheLoaiSach ct " +
"where s.MaSach = ct.MaSach and ct.MaTheLoai = " + genreid);
if (reader != null)
{
result = new ObservableCollection <Book>();
while (reader.Read())
{
var id = reader.GetInt32(0);
var item = new Book(id);
item.BeginInit();
item.Name = (string)reader.GetValueDefault(1, null);
item.Image = (string)reader.GetValueDefault(2, null);
item.Number = (int)reader.GetValueDefault(3, 0);
item.Price = (int)reader.GetValueDefault(4, 0);
item.IsDeleted = (bool)reader.GetValueDefault(5, false);
foreach (var i in AuthorAdapter.GetAuthorsForBook(id))
{
item.Authors.Add(i);
}
foreach (var i in GenreAdapter.GetGenresForBook(id))
{
item.Genres.Add(i);
}
item.IsDeletedItem = item.IsDeleted;
item.EndInit();
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <Book> GetDeletedBooks()
{
ObservableCollection <Book> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select MaSach,TenSach,AnhBia,SoLuongTon,DonGia " +
"from Sach " +
"where BiXoa = 'true'");
if (reader != null)
{
result = new ObservableCollection <Book>();
while (reader.Read())
{
var id = reader.GetInt32(0);
var item = new Book(id);
item.BeginInit();
item.Name = (string)reader.GetValueDefault(1, null);
item.Image = (string)reader.GetValueDefault(2, null);
item.Number = (int)reader.GetValueDefault(3, 0);
item.Price = (int)reader.GetValueDefault(4, 0);
item.IsDeleted = true;
foreach (var i in AuthorAdapter.GetAuthorsForBook(id))
{
item.Authors.Add(i);
}
foreach (var i in GenreAdapter.GetGenresForBook(id))
{
item.Genres.Add(i);
}
item.IsDeletedItem = item.IsDeleted;
item.EndInit();
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static Book GetBook(int id)
{
Book result = null;
try
{
var reader = DataConnector.ExecuteQuery("select s.TenSach,s.AnhBia,s.SoLuongTon,s.DonGia,s.BiXoa " +
"from Sach s where s.MaSach = " + id);
if (reader != null)
{
while (reader.Read())
{
var item = new Book(id);
item.BeginInit();
item.Name = (string)reader.GetValueDefault(0, null);
item.Image = (string)reader.GetValueDefault(1, null);
item.Number = (int)reader.GetValueDefault(2, 0);
item.Price = (int)reader.GetValueDefault(3, 0);
item.IsDeleted = (bool)reader.GetValueDefault(4, false);
foreach (var i in AuthorAdapter.GetAuthorsForBook(id))
{
item.Authors.Add(i);
}
foreach (var i in GenreAdapter.GetGenresForBook(id))
{
item.Genres.Add(i);
}
item.IsDeletedItem = item.IsDeleted;
item.EndInit();
result = item;
break;
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <BillItem> GetBillItems(int billid)
{
ObservableCollection <BillItem> result = null;
try
{
var reader = DataConnector.ExecuteQuery("select MaSach, SoLuong, DonGia from ChiTietHoaDon where MaHoaDon = " + billid);
if (reader != null)
{
result = new ObservableCollection <BillItem>();
while (reader.Read())
{
var item = new BillItem(BookAdapter.GetBook(reader.GetInt32(0)), reader.GetInt32(1), reader.GetInt32(2));
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <BillItem> GetAllBeforeDate(DateTime date)
{
ObservableCollection <BillItem> result = null;
try
{
var reader = DataConnector.ExecuteQuery(string.Format("select ct.MaSach, ct.SoLuong, ct.DonGia from ChiTietHoaDon ct, HoaDon hd where ct.MaHoaDon = hd.MaHoaDon and DATEDIFF(day, '{0}', hd.NgayLap) <= 0", date));
if (reader != null)
{
result = new ObservableCollection <BillItem>();
while (reader.Read())
{
var item = new BillItem(BookAdapter.GetBook(reader.GetInt32(0)), reader.GetInt32(1), reader.GetInt32(2));
result.Add(item);
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static ObservableCollection <AccessLevel> GetAll()
{
if (_accessLevels == null)
{
try
{
var reader = DataConnector.ExecuteQuery(@"SELECT MaPhanQuyen, TenPhanQuyen FROM PhanQuyen");
if (reader != null)
{
_accessLevels = new ObservableCollection <AccessLevel>();
while (reader.Read())
{
_accessLevels.Add(new AccessLevel(reader.GetInt32(0), (string)reader.GetValueDefault(1, null)));
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
}
return(_accessLevels);
}
public static ObservableCollection <Book> GetNumberReportData(int month, int year)
{
ObservableCollection <Book> result = null;
try
{
var reader = DataConnector.ExecuteQuery(string.Format("select MaBaoCao from BaoCaoTon where Thang = {0} and Nam = {1}", month, year));
if (reader != null)
{
while (reader.Read())
{
var id = reader.GetInt32(0);
result = new ObservableCollection <Book>();
var r = DataConnector.ExecuteQuery(string.Format("select MaSach, SoLuongTonDau, SoLuongTonCuoi from ChiTietBaoCaoTon where MaBaoCao = {0}", id));
if (r != null)
{
while (r.Read())
{
var c = Adapters.BookAdapter.GetBook(r.GetInt32(0));
c.BeginInit();
c.Number = (int)r.GetValueDefault(2, 0);
c.Tag = (int)r.GetValueDefault(1, 0);
c.EndInit();
result.Add(c);
}
}
break;
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
public static Rule GetLastRules()
{
Rule result = null;
try
{
var reader = DataConnector.ExecuteQuery("select MaQuyDinh, NgayCapNhat, " +
"SoLuongSachNhapToiThieu, " +
"SoLuongSachTonToiThieuDeNhap, " +
"SoLuongSachTonToiThieuSauKhiBan, " +
"TienNoToiDa, " +
"DuocThuVuotSoTienKhachHangDangNo " +
"from QuyDinh order by NgayCapNhat desc");
if (reader != null)
{
while (reader.Read())
{
var item = new Rule(reader.GetInt32(0), reader.GetDateTime(1));
item.BeginInit();
item.MinNumberWhenImport = reader.GetInt32(2);
item.MinNumberToImport = reader.GetInt32(3);
item.MinNumberInStore = reader.GetInt32(4);
item.MaxDebt = reader.GetInt32(5);
item.AllowGetMoneyGreaterDebt = reader.GetBoolean(6);
item.EndInit();
result = item;
break;
}
}
}
catch (Exception ex)
{
ErrorManager.Current.DataCantBeRead.Call(ex.Message);
}
return(result);
}
