public bool AddPayment(DateTime date, float money, string customerID)
{
string SQL = "call USP_AddPayment(\"" + date.ToString("yyyy/MM/dd") + "\",\"" + money + "\",\"" + Int32.Parse(customerID) + "\")";
try
{
DatabaseAccess.getInstance().getConnect();
DatabaseAccess.getInstance().ExecuteQuerry(SQL);
DatabaseAccess.getInstance().getClose();
return(true);
}
catch (Exception ex) { return(false); }
}
public bool AddCategory(string Category)
{
string SQL = "call USP_AddCategory('" + Category + "')";
try
{
DatabaseAccess.getInstance().getConnect();
MySqlCommand cmd = DatabaseAccess.getInstance().conn.CreateCommand();
cmd.CommandText = SQL;
MySqlDataReader reader = cmd.ExecuteReader();
DatabaseAccess.getInstance().getClose();
return(true);
}
catch (Exception e) { return(false); }
}
public bool UpdateCustomer(string id, string name, string phone, string email, string address)
{
string SQL = "call USP_UpdateCusTomer('" + id + "','" + name + "','" + phone + "','" + email + "','" + address + "')";
try
{
DatabaseAccess.getInstance().getConnect();
DatabaseAccess.getInstance().ExecuteQuerry(SQL);
DatabaseAccess.getInstance().getClose();
return(true);
}
catch (Exception e)
{
return(false);
}
}
public bool UpdateRules(int import, int MaximumBookLeft, int MinimumBookLeft, int MaximumOwe)
{
//MyRule rs = new MyRule(import, MaximumBookLeft, MinimumBookLeft,MaximumOwe);
string SQL = "call USP_UpdateRules(" + import + "," + MaximumBookLeft + "," + MinimumBookLeft + "," + MaximumOwe + ")";
try
{
DatabaseAccess.getInstance().getConnect();
DatabaseAccess.getInstance().ExecuteQuerry(SQL);
DatabaseAccess.getInstance().getClose();
return(true);
}
catch (Exception e)
{
return(false);
}
}
public string GetAuthorIDByName(string name)
{
string rs = "";
string SQL = "Select MaTacGia from TacGia where TenTacGia ='" + name + "'";
DatabaseAccess.getInstance().getConnect();
MySqlCommand cmd = DatabaseAccess.getInstance().conn.CreateCommand();
cmd.CommandText = SQL;
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
rs = reader.GetString("MaTacGia");
}
return(rs);
}
public string GetCustomerIDByName(string name)
{
string SQL = "Select * from KHACHHANG where TenKhachHang = '" + name + "'";
string rs = "";
try
{
DatabaseAccess.getInstance().getConnect();
DatabaseAccess.getInstance().ExecuteQuerry(SQL);
while (DatabaseAccess.getInstance().reader.Read())
{
rs = DatabaseAccess.getInstance().reader.GetString("MaKhachHang");
}
DatabaseAccess.getInstance().getClose();
}
catch (Exception e) { }
return(rs);
}
public string GetCategoryIDByName(string name)
{
string rs = "";
string SQL = "Select MaTheLoai from THELOAISACH where TENTHELOAI ='" + name + "'";
DatabaseAccess.getInstance().getConnect();
MySqlCommand cmd = DatabaseAccess.getInstance().conn.CreateCommand();
cmd.CommandText = SQL;
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
rs = reader.GetString("MaTheLoai");
}
return(rs);
}
public bool AddImportBookCard(DateTime date, float valueTotal)
{
//date.ToString()
string SQL = "call USP_AddImportBook('" + date.ToString("yyyy/MM/dd") + "','" + valueTotal + "')";
try
{
DatabaseAccess.getInstance().getConnect();
MySqlCommand cmd = DatabaseAccess.getInstance().conn.CreateCommand();
cmd.CommandText = SQL;
MySqlDataReader reader = cmd.ExecuteReader();
DatabaseAccess.getInstance().getClose();
return(true);
}
catch
{
return(false);
}
}
public List <Book> getBooks()
{
string SQL = "call USP_GetBook()";
List <Book> list = new List <Book>();
try
{
DatabaseAccess.getInstance().getConnect();
MySqlCommand cmd = DatabaseAccess.getInstance().conn.CreateCommand();
cmd.CommandText = SQL;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string id = reader.GetString("MaSach");
string name = reader.GetString("TenSach");
List <Author> authors = (new AuthorDAL()).getAuthorByBook(reader.GetString("MaSach"));
Category category = (new CategoryDAL()).getCategoryByBook(reader.GetString("MaSach"));
string publishCompany = reader.GetString("NhaXuatBan");
int publishYear = Int32.Parse(reader.GetString("NamXuatBan"));
int count = Int32.Parse(reader.GetString("SoLuongTon"));
float price = (float)Math.Round(float.Parse(reader.GetString("DonGiaNhap")) * 10) / 10;
Book book = new Book(id, name, authors, category, publishCompany, publishYear, count, price);
list.Add(book);
}
DatabaseAccess.getInstance().getClose();
}
catch
{
}
return(list);
}
public List <ReportInventory> getReportInventory(int month, int year)
{
string SQL;
List <ReportInventory> list = new List <ReportInventory>();
try
{
DatabaseAccess.getInstance().getConnect();
SQL = "call USP_FreshReportInventory('" + month + "','" + year + "')";
DatabaseAccess.getInstance().ExecuteQuerry(SQL);
DatabaseAccess.getInstance().getClose();
List <Book> listBook = new List <Book>();
listBook = (new BookDAL().getBooks());
foreach (Book book in listBook)
{
DatabaseAccess.getInstance().getConnect();
SQL = "call USP_ImportReportInventory('" + month + "','" + year + "','" + book.Id() + "')";
DatabaseAccess.getInstance().ExecuteQuerry(SQL);
DatabaseAccess.getInstance().getClose();
}
DatabaseAccess.getInstance().getConnect();
SQL = "call USP_GetReportInventory(\"" + month + "\",\"" + year + "\")";
DatabaseAccess.getInstance().ExecuteQuerry(SQL);
while (DatabaseAccess.getInstance().reader.Read())
{
list.Add(new ReportInventory(
Int32.Parse(DatabaseAccess.getInstance().reader.GetString("Thang")),
Int32.Parse(DatabaseAccess.getInstance().reader.GetString("Nam")),
DatabaseAccess.getInstance().reader.GetString("MaSach"),
Int32.Parse(DatabaseAccess.getInstance().reader.GetString("TonDau")),
Int32.Parse(DatabaseAccess.getInstance().reader.GetString("PhatSinh")),
Int32.Parse(DatabaseAccess.getInstance().reader.GetString("TonCuoi"))
));
}
DatabaseAccess.getInstance().getClose();
}
catch (Exception e) { }
return(list);
}
public Category getCategoryByBook(string bookID)
{
string SQL = "call USP_GetCategoryByBook('" + bookID + "')";
Category category = null;
try
{
DatabaseAccess.getInstance().getConnect();
MySqlCommand cmd = DatabaseAccess.getInstance().conn.CreateCommand();
cmd.CommandText = SQL;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
category = new Category(reader.GetString("MaTheLoai"), reader.GetString("TenTheLoai"));
}
DatabaseAccess.getInstance().getClose();
}
catch (Exception e) { }
return(category);
}
public bool AddBillInfo(string BookId, int count, float price, float total)
{
string SQL = "call USP_AddBillInfo('" + BookId + "','" + count + "','" + price + "','" + total + "')";
try
{
DatabaseAccess.getInstance().getConnect();
MySqlCommand cmd = DatabaseAccess.getInstance().conn.CreateCommand();
cmd.CommandText = SQL;
MySqlDataReader reader = cmd.ExecuteReader();
DatabaseAccess.getInstance().getClose();
return(true);
}
catch
{
return(false);
}
}
public MyRule GetRules()
{
string SQL = "CALL USP_GetRules()";
MyRule rs = null;
try
{
DatabaseAccess.getInstance().getConnect();
DatabaseAccess.getInstance().ExecuteQuerry(SQL);
if (DatabaseAccess.getInstance().reader.Read())
{
int NhaptoiThieu = DatabaseAccess.getInstance().reader.GetInt32("LuongNhapToiThieu");
int TonTruocKhiNhap = DatabaseAccess.getInstance().reader.GetInt32("LuongTonTruocKhiNhap");
int TonSauKhiBan = DatabaseAccess.getInstance().reader.GetInt32("LuongTonSauKhiBan");
int TienNoToiDa = DatabaseAccess.getInstance().reader.GetInt32("TienNoToiDa");
rs = new MyRule(NhaptoiThieu, TonTruocKhiNhap, TonSauKhiBan, TienNoToiDa);
}
DatabaseAccess.getInstance().getClose();
}
catch (Exception e) { }
return(rs);
}
public List <Author> getAuthorByBook(string bookID)
{
string SQL = "call USP_GetAuthorByBook('" + bookID + "')";
List <Author> list = new List <Author>();
try
{
DatabaseAccess.getInstance().getConnect();
MySqlCommand cmd = DatabaseAccess.getInstance().conn.CreateCommand();
cmd.CommandText = SQL;
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
list.Add(new Author(reader.GetString("MaTacGia"), reader.GetString("TenTacGia")));
}
DatabaseAccess.getInstance().getClose();
}
catch (Exception e) { }
return(list);
}
public bool AddBill(DateTime date, float BillValue, float moneyReceive, float moneyChange, string customerID)
{
string SQL = "call USP_AddBill('" + date.ToString("yyyy/MM/dd")
+ "','" + BillValue
+ "','" + moneyReceive
+ "','" + moneyChange
+ "','" + customerID
+ "')";
try
{
DatabaseAccess.getInstance().getConnect();
MySqlCommand cmd = DatabaseAccess.getInstance().conn.CreateCommand();
cmd.CommandText = SQL;
MySqlDataReader reader = cmd.ExecuteReader();
DatabaseAccess.getInstance().getClose();
return(true);
}
catch
{
return(false);
}
}
public bool Login(string username, string password)
{
try
{
bool rs = false;
DatabaseAccess.getInstance().getConnect();
MySqlCommand cmd = DatabaseAccess.getInstance().conn.CreateCommand();
cmd.CommandText = "Call USP_Login('" + username + "','" + password + "')";
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
rs = true;
}
DatabaseAccess.getInstance().getClose();
reader.Close();
return(rs);
}
catch
{
return(false);
}
}
public Customer getCustomerByID(string customerID)
{
string SQL = "CALL USP_GetCustomerByID(\"" + customerID + "\")";
Customer customer = null;
try
{
DatabaseAccess.getInstance().getConnect();
DatabaseAccess.getInstance().ExecuteQuerry(SQL);
while (DatabaseAccess.getInstance().reader.Read())
{
string id = DatabaseAccess.getInstance().reader.GetString("MaKhachHang");
string name = DatabaseAccess.getInstance().reader.GetString("TenKhachHang");
string phone = DatabaseAccess.getInstance().reader.GetString("SoDienThoai");
string email = DatabaseAccess.getInstance().reader.GetString("Email");
string address = DatabaseAccess.getInstance().reader.GetString("DiaChi");
float owe = (float)Math.Round(float.Parse(DatabaseAccess.getInstance().reader.GetString("SoTienNo")));
customer = new Customer(id, name, phone, email, address, owe);
}
DatabaseAccess.getInstance().getClose();
}
catch (Exception e) { }
return(customer);
}
public List <Customer> getCustomers()
{
string SQL = "CALL USP_GetCustomer()";
List <Customer> list = new List <Customer>();
try
{
DatabaseAccess.getInstance().getConnect();
DatabaseAccess.getInstance().ExecuteQuerry(SQL);
while (DatabaseAccess.getInstance().reader.Read())
{
string id = DatabaseAccess.getInstance().reader.GetString("MaKhachHang");
string name = DatabaseAccess.getInstance().reader.GetString("TenKhachHang");
string phone = DatabaseAccess.getInstance().reader.GetString("SoDienThoai");
string email = DatabaseAccess.getInstance().reader.GetString("Email");
string address = DatabaseAccess.getInstance().reader.GetString("DiaChi");
float owe = (float)Math.Round(float.Parse(DatabaseAccess.getInstance().reader.GetString("SoTienNo")));
list.Add(new Customer(id, name, phone, email, address, owe));
}
DatabaseAccess.getInstance().getClose();
}
catch (Exception e) { }
return(list);
}
