public List <MatHang> LoadData()
{
try
{
dp.Connect();
string sqlstr = "SELECT * FROM MatHang";
List <MatHang> list = new List <MatHang>();
SqlDataReader dr = dp.ExecuteReader(sqlstr);
//List<MatHang> list = new List<MatHang>();
while (dr.Read())
{
string maMatHang = dr.GetString(0);
string tenMatHang = dr.GetString(1);
string maNhaCungCap = dr.GetString(2);
string tenNhaCungCap = dr.GetString(3);
int donGia = dr.GetInt32(4);
int giaBan = dr.GetInt32(5);
string maLoaiTien = dr.GetString(6);
int namSanXuat = dr.GetInt32(7);
MatHang N = new MatHang(maMatHang, tenMatHang, maNhaCungCap, tenNhaCungCap, donGia, giaBan, maLoaiTien, namSanXuat);
list.Add(N);
}
dr.Close();
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.Disconnect();
}
}
public List <Nhanvien> LoadData()
{
try
{
dp.Connect();
string sqlstr = "SELECT * FROM NhanVien";
List <Nhanvien> list = new List <Nhanvien>();
SqlDataReader dr = dp.ExecuteReader(sqlstr);
//List<Nhanvien> list = new List<Nhanvien>();
while (dr.Read())
{
string maNV = dr.GetString(0);
string hoTen = dr.GetString(1);
string tenQuayGiaoDich = dr.GetString(2);
string diaChi = dr.GetString(3);
string soDienThoai = dr.GetString(4);
Nhanvien N = new Nhanvien(maNV, hoTen, tenQuayGiaoDich, diaChi, soDienThoai);
list.Add(N);
}
dr.Close();
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.Disconnect();
}
}
public static List <Ban> LoadAll()
{
provider.Connect();
string cmdStr = "Execute proc_LoadAllTables";
List <Ban> list = new List <Ban>();
SqlDataReader reader = provider.ExecuteReader(cmdStr);
while (reader.Read())
{
list.Add(new Ban(reader.GetInt32(0), reader.GetString(1)));
}
provider.Disconnect();
return(list);
}
public static HoaDon GetBill(int maHoaDon)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_GetBill " + maHoaDon;
SqlDataReader reader = provider.ExecuteReader(cmdStr);
HoaDon bill = null;
if (reader.Read())
{
DateTime?thoiGianLap;
if (!reader.IsDBNull(3))
{
thoiGianLap = reader.GetDateTime(3);
}
else
{
thoiGianLap = null;
}
bill = new HoaDon
{
MaHoaDon = reader.GetInt32(0),
MaBan = reader.GetInt32(1),
MaNhanVien = reader.GetInt32(2),
ThoiGianLap = thoiGianLap,
TongTien = reader.GetInt32(4)
};
}
provider.Disconnect();
return(bill);
}
///////////////////////////
// Lấy danh sách KHÁCH HÀNG
///////////////////////////
public List <Customer> GetCustomer(string sql)
{
List <Customer> list = new List <Customer>();
string MaKH, HoTen, NgaySinh, GioiTinh, DiaChi;
int SDT;
dp.Connect();
try
{
SqlDataReader dr = dp.ExecuteReader(sql);
while (dr.Read())
{
MaKH = dr.GetString(0);
HoTen = dr.GetString(1);
NgaySinh = dr.GetString(2);
GioiTinh = dr.GetString(3);
SDT = dr.GetInt32(4);
DiaChi = dr.GetString(5);
Customer cus = new Customer(MaKH, HoTen, NgaySinh, GioiTinh, SDT, DiaChi);
list.Add(cus);
}
return(list);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dp.Disconnect();
}
}
public static List <HoaDon> LoadAll(int pageNumber, int billsPerPage)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_LoadAllBills " + pageNumber + ", " + billsPerPage;
SqlDataReader reader = provider.ExecuteReader(cmdStr);
List <HoaDon> billList = new List <HoaDon>();
while (reader.Read())
{
int maHoaDon = reader.GetInt32(0);
int maBan = reader.GetInt32(1);
int maNhanVen = reader.GetInt32(2);
DateTime?thoiGianLap;
if (!reader.IsDBNull(3))
{
thoiGianLap = reader.GetDateTime(3);
}
else
{
thoiGianLap = null;
}
int tongTien = reader.GetInt32(4);
billList.Add(new HoaDon(maHoaDon, maBan, maNhanVen, thoiGianLap, tongTien));
}
provider.Disconnect();
return(billList);
}
public static NhanVien GetEmployee(int maNhanVien)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_GetEmployee " + maNhanVien;
SqlDataReader reader = provider.ExecuteReader(cmdStr);
NhanVien employee = new NhanVien();
if (reader.Read())
{
employee.MaNhanVien = reader.GetInt32(0);
employee.HoTen = reader.GetString(1);
employee.NgaySinh = reader.GetDateTime(2);
employee.GioiTinh = reader.GetString(3);
employee.DiaChi = reader.GetString(4);
employee.SoDienThoai = reader.GetString(5);
employee.NgayVaoLam = reader.GetDateTime(6);
employee.ChucVu = reader.GetString(7);
employee.Luong = reader.GetInt32(8);
}
provider.Disconnect();
return(employee);
}
///////////////////////////
// Lấy danh sách Ghế
///////////////////////////
public List <Seat> GetSeat(string sql)
{
List <Seat> list = new List <Seat>();
string MaVe, Loai, HangGhe;
bool TinhTrang;
int Gia;
dp.Connect();
try
{
SqlDataReader dr = dp.ExecuteReader(sql);
while (dr.Read())
{
MaVe = dr.GetString(0);
Loai = dr.GetString(1);
HangGhe = dr.GetString(2);
Gia = dr.GetInt32(3);
TinhTrang = dr.GetBoolean(4);
Seat seat = new Seat(MaVe, Loai, HangGhe, Gia, TinhTrang);
list.Add(seat);
}
return(list);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dp.Disconnect();
}
}
public static List <ThucDon> LoadAll(string tenMon = "")
{
provider = new DataProvider();
provider.Connect();
string cmdStr;
if (tenMon == string.Empty)
{
cmdStr = "Execute proc_LoadAllDishes";
}
else
{
cmdStr = "Execute proc_LoadAllDishesWithName N'" + tenMon + "'";
}
SqlDataReader reader = provider.ExecuteReader(cmdStr);
List <ThucDon> list = new List <ThucDon>();
while (reader.Read())
{
list.Add(new ThucDon(reader.GetString(0), reader.GetInt32(1), reader.GetString(2)));
}
provider.Disconnect();
return(list);
}
public static HoaDon GetLastBill(int maBan)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_GetLastBill " + maBan;
SqlDataReader reader = provider.ExecuteReader(cmdStr);
HoaDon bill = new HoaDon();
if (reader.Read())
{
bill.MaHoaDon = reader.GetInt32(0);
bill.MaBan = reader.GetInt32(1);
bill.MaNhanVien = reader.GetInt32(2);
if (!reader.IsDBNull(3))
{
bill.ThoiGianLap = reader.GetDateTime(3);
}
bill.TongTien = reader.GetInt32(4);
}
provider.Disconnect();
return(bill);
}
public static int GetNumberOfBills()
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_GetNumberOfBills";
int numOfBills = (int)provider.ExecuteScalar(cmdStr);
provider.Disconnect();
return(numOfBills);
}
public static int Delete(int numOfTables)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_RemoveTable " + numOfTables;
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int Insert(HoaDon hoadon)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_AddBill " + hoadon.MaBan + ", " + hoadon.MaNhanVien + ", " + hoadon.TongTien;
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int Delete(int maNhanVien)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_RemoveEmployee " + maNhanVien;
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int Insert(ThucDon dish)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_AddDish N'" + dish.TenMon + "', " + dish.DonGia + ", N'" + dish.DonVi + "'";
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int UpdatePrice(string tenMon, int donGia)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_UpdateDishPrice N'" + tenMon + "', " + donGia;
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int Delete(string tenMon)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_RemoveDish N'" + tenMon + "'";
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int Insert(ChiTietHoaDon detail)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_AddBillDetail " + detail.MaHoaDon + ", N'" + detail.TenMon + "', " + detail.SoLuong + ", " + detail.ThanhTien;
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int GetNumberOfBillsByTime(DateTime from, DateTime to)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_GetNumberOfBillsByTime '" + from.ToString("yyyy-MM-dd") + "', '" + to.ToString("yyyy-MM-dd") + "'";
int numOfBills = (int)provider.ExecuteScalar(cmdStr);
provider.Disconnect();
return(numOfBills);
}
public static DataTable LoadAll()
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_LoadAllAccounts";
DataTable table = provider.ExcuteQuery(cmdStr);
provider.Disconnect();
return(table);
}
public static DataTable LoadAllExtendedBillDetails(int maHoaDon)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_LoadAllExtendedBillDetails " + maHoaDon;
DataTable table = provider.ExcuteQuery(cmdStr);
provider.Disconnect();
return(table);
}
public static int DeleteAccount(string taiKhoan)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_DeleteAccont '" + taiKhoan + "'";
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int CreateAccount(DangNhap dangNhap)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_CreateAccount '" + dangNhap.TaiKhoan + "', '" + dangNhap.MatKhau + "', " + dangNhap.MaNhanVien;
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int UpdateTotalPrice(HoaDon bill)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_UpdateTotalPrice " + bill.MaHoaDon + ", " + bill.TongTien;
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int UpdatePassword(string taiKhoan, string matKhau)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_UpdatePassword '" + taiKhoan + "', '" + matKhau + "'";
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static int UpdateInvoicingTime(HoaDon hoaDon)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_UpdateInvoicingTime " + hoaDon.MaHoaDon;
int result = provider.ExecuteNonQuery(cmdStr);
provider.Disconnect();
return(result);
}
public static bool Has(string taiKhoan, string matKhau)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_CheckAccount '" + taiKhoan + "', '" + matKhau + "'";
SqlDataReader reader = provider.ExecuteReader(cmdStr);
bool result = reader.HasRows;
provider.Disconnect();
return(result);
}
///////////////////////////
// Lấy đúng dữ liệu đăng nhập
///////////////////////////
public int LoginSuccess(string sql, string MaNV, string MatKhau)
{
List <SqlParameter> paras = new List <SqlParameter>();
paras.Add(new SqlParameter("@MaNV", MaNV));
paras.Add(new SqlParameter("@MatKhau", MatKhau));
dp.Connect();
try
{
int x = (int)dp.ExecuteScalar(sql, System.Data.CommandType.Text, paras);
return(x);
}
catch (Exception ex)
{
throw ex;
}
finally
{
dp.Disconnect();
}
}
public static int GetMaxYear()
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_GetMaxYear";
object result = provider.ExecuteScalar(cmdStr);
int maxYear = result == DBNull.Value ? DateTime.Today.Year : (int)result;
provider.Disconnect();
return(maxYear);
}
public static int GetTotalPriceByYear(int year)
{
provider = new DataProvider();
provider.Connect();
string cmdStr = "Execute proc_GetTotalPriceByYear " + year;
int total;
object totalObject = provider.ExecuteScalar(cmdStr);
total = totalObject == DBNull.Value ? 0 : (int)totalObject;
provider.Disconnect();
return(total);
}