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 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 void ThemCongThuc(DTO_ChiTietMonAn ct)
{
SqlConnection cnn = dp.getConnect();
dp.Connect();
string themChiTiet = "INSERT INTO ChiTietMonAn(MaMonAn,MaNguyenLieu,GhiChu) VALUES(N'" + ct.MaMonAn + "','" + ct.MaNguyenLieu + "',N'" + ct.GhiChu + "')";
SqlCommand cmd = new SqlCommand(themChiTiet, cnn);
cmd.ExecuteNonQuery();//Thuc thi cau truy van
}
public void ThemMonAn(DTO_MonAn ma)
{
SqlConnection cnn = dp.getConnect();
dp.Connect();
string themMon = "INSERT INTO MonAn(TenMonAn,GiaTien,MaDanhMuc,DonViTinh) VALUES(N'" + ma.TenMonAn + "','" + ma.GiaTien + "','" + ma.MaDanhMuc + "',N'" + ma.DonViTinh + "')";
SqlCommand cmd = new SqlCommand(themMon, cnn);
cmd.ExecuteNonQuery();//Thuc thi cau truy van
}
public void ThemDanhMuc(DTO_DanhMuc dm)
{
SqlConnection cnn = dp.getConnect();
dp.Connect();
string themDanhMuc = "INSERT INTO DanhMuc(TenDanhMuc,MoTa) VALUES(N'" + dm.TenDanhMuc + "',N'" + dm.MoTa + "')";
SqlCommand cmd = new SqlCommand(themDanhMuc, cnn);
cmd.ExecuteNonQuery();//thuc thi sqlCommand
}
public void ThemMonAn(DTO_MonAn ma)
{
//Phải lấy chuỗi kết nối trước mới dp.connect
SqlConnection cnn = dp.getConnect();
dp.Connect();// phải có hàm kết nối mới thêm được
string themMon = "INSERT INTO MonAn(TenMonAn,GiaTien,MaDanhMuc,DonViTinh) VALUES(N'" + ma.TenMonAn + "','" + ma.GiaTien + "','" + ma.MaDanhMuc + "',N'" + ma.DonViTinh + "')";
SqlCommand cmd = new SqlCommand(themMon, cnn);
cmd.ExecuteNonQuery();//Thuc thi cau truy van
}
public static List <KhachHang_DTO> takeCustomers()
{
string sQuery = @"select *from KhachHang";
conn = DataProvider.Connect();
DataTable dt = DataProvider.dataSQuery(sQuery, conn);
if (dt.Rows.Count == 0)
{
return(null);
}
List <KhachHang_DTO> Cus = new List <KhachHang_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
KhachHang_DTO K = new KhachHang_DTO();
K.MaKH = dt.Rows[i]["MaKH"].ToString();
K.TenKH = dt.Rows[i]["TenKH"].ToString();
K.Cmnd = int.Parse(dt.Rows[i]["CMND"].ToString());
K.QuocTich = dt.Rows[i]["QuocTich"].ToString();
K.GioiTinh = dt.Rows[i]["GioiTinh"].ToString();
K.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
K.Sdt = int.Parse(dt.Rows[i]["SDT"].ToString());
K.MaPhong = dt.Rows[i]["MaPhong"].ToString();
Cus.Add(K);
}
DataProvider.DongKetNoi(conn);
return(Cus);
}
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 List <HoaDon_DTO> takeInvoice()
{
string sQuery = @"select *from HoaDon";
conn = DataProvider.Connect();
DataTable dt = DataProvider.dataSQuery(sQuery, conn);
if (dt.Rows.Count == 0)
{
return(null);
}
List <HoaDon_DTO> lst = new List <HoaDon_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
HoaDon_DTO HD = new HoaDon_DTO();
HD.MaHD = dt.Rows[i]["MaHD"].ToString();
HD.MaNV = dt.Rows[i]["MaNV"].ToString();
HD.MaPhong = dt.Rows[i]["MaPhong"].ToString();
HD.TenKH = dt.Rows[i]["TenKH"].ToString();
HD.NgayDatPhong = DateTime.Parse(dt.Rows[i]["NgayDatPhong"].ToString());
HD.NgayTraPhong = DateTime.Parse(dt.Rows[i]["NgayTraPhong"].ToString());
HD.GiaHD = int.Parse(dt.Rows[i]["GiaHD"].ToString());
lst.Add(HD);
}
return(lst);
}
public static List <Phong_DTO> takeRooms()
{
string sQuery = @"select *from Phong";
conn = DataProvider.Connect();
DataTable dt = DataProvider.dataSQuery(sQuery, conn);
if (dt.Rows.Count == 0)
{
return(null);
}
List <Phong_DTO> lst = new List <Phong_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Phong_DTO P = new Phong_DTO();
P.MaPhong = dt.Rows[i]["MaPhong"].ToString();
P.TenPhong = dt.Rows[i]["TenPhong"].ToString();
P.LoaiPhong = dt.Rows[i]["LoaiPhong"].ToString();
P.GiaPhong = int.Parse(dt.Rows[i]["GiaPhong"].ToString());
P.TinhTrang = dt.Rows[i]["TinhTrang"].ToString();
P.NgayDatPhong = DateTime.Parse(dt.Rows[i]["NgayDatPhong"].ToString());
P.MaNV = dt.Rows[i]["MaNV"].ToString();
P.MaDV = dt.Rows[i]["MaDV"].ToString();
lst.Add(P);
}
return(lst);
}
public static List <KhachHang_DTO> takeCustomerFIdPhong(string id)
{
string sQuery = @"select k.* from Phong p , KhachHang K where p.MaPhong = k.MaPhong and p.MaPhong = '" + id + "' ";
//where p.MaPhong = k.MaPhong AND p.MaPhong = 'P003'
conn = DataProvider.Connect();
DataTable dt = DataProvider.dataSQuery(sQuery, conn);
if (dt.Rows.Count == 0)
{
return(null);
}
List <KhachHang_DTO> Cus = new List <KhachHang_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
KhachHang_DTO K = new KhachHang_DTO();
K.MaKH = dt.Rows[i]["MaKH"].ToString();
K.TenKH = dt.Rows[i]["TenKH"].ToString();
K.Cmnd = int.Parse(dt.Rows[i]["CMND"].ToString());
K.QuocTich = dt.Rows[i]["QuocTich"].ToString();
K.GioiTinh = dt.Rows[i]["GioiTinh"].ToString();
K.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
K.Sdt = int.Parse(dt.Rows[i]["SDT"].ToString());
// K.MaPhong = dt.Rows[i]["MaPhong"].ToString();
Cus.Add(K);
}
return(Cus);
// DataProvider.DongKetNoi(conn);
}
public static List <DichVuDaDat_DTO> takeServiceFIDSer(string IDDV)
{
string sQuery = @" select * from DichVuDaDat where MaDV ='" + IDDV + "' ";
conn = DataProvider.Connect();
DataTable dt = DataProvider.dataSQuery(sQuery, conn);
if (dt.Rows.Count == 0)
{
return(null);
}
List <DichVuDaDat_DTO> Ser = new List <DichVuDaDat_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DichVuDaDat_DTO K = new DichVuDaDat_DTO();
K.Id = int.Parse(dt.Rows[i]["id"].ToString());
K.MaDV = dt.Rows[i]["MaDV"].ToString();
K.TenDV = dt.Rows[i]["TenDV"].ToString();
K.SoLuong = int.Parse(dt.Rows[i]["SoLuong"].ToString());
K.GiaDV = int.Parse(dt.Rows[i]["GiaDV"].ToString());
Ser.Add(K);
}
return(Ser);
}
///////////////////////////
// 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 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 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);
}
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);
}
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 List <DichVu_DTO> takeServicesTest()
{
string sQuery = @"select D.* from DichVu D";
// SqlDataAdapter da = new SqlDataAdapter(sQuery, con);
// DataTable dt = new DataTable();
//da.Fill(dt);
conn = DataProvider.Connect();
DataTable dt = DataProvider.dataSQuery(sQuery, conn);
if (dt.Rows.Count == 0)
{
return(null);
}
List <DichVu_DTO> Ser = new List <DichVu_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DichVu_DTO K = new DichVu_DTO();
K.MaDV = dt.Rows[i]["MaDV"].ToString();
K.TenDV = dt.Rows[i]["TenDV"].ToString();
K.SoLuong = int.Parse(dt.Rows[i]["SoLuong"].ToString());
K.GiaDV = int.Parse(dt.Rows[i]["GiaDV"].ToString());
// K.MaPhong = dt.Rows[i]["MaPhong"].ToString();
Ser.Add(K);
}
return(Ser);
}
// lấy chức vụ nhân viên
public static List <NhanVien_DTO> loadChucVu()
{
string sQuery = @" select *from NhanVien where ChucVu like N'%n%'";
conn = DataProvider.Connect();
DataTable dt = DataProvider.dataSQuery(sQuery, conn);
if (dt.Rows.Count == 0)
{
return(null);
}
List <NhanVien_DTO> Emp = new List <NhanVien_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
NhanVien_DTO N = new NhanVien_DTO();
N.MaNV = dt.Rows[i]["MaNV"].ToString();
N.TenNV = dt.Rows[i]["TenNV"].ToString();
N.ChucVu = dt.Rows[i]["ChucVu"].ToString();
N.NamSinh = DateTime.Parse(dt.Rows[i]["NamSinh"].ToString());
N.GioiTinh = dt.Rows[i]["GioiTinh"].ToString();
Emp.Add(N);
}
return(Emp);
}
///////////////////////////
// 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 List <NhanVien> LoadData()
{
List <NhanVien> list = new List <NhanVien>();
try
{
dp.Connect();
string sql = "Select * from NhanVien";
SqlDataReader dr = dp.ExcuteReader(sql);
while (dr.Read())
{
NhanVien nv = new NhanVien();
nv.maNV = dr.GetInt32(0);
nv.hoNV = dr.GetString(1);
nv.ten = dr.GetString(2);
nv.diaChi = dr.GetString(3);
nv.sdt = dr.GetString(4);
list.Add(nv);
}
}
catch (SqlException ex)
{
throw ex;
}
catch (InvalidOperationException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
return(list);
}
public static List <TaiKhoan_DTO> takeAccount(string TenNV)
{
string sQuery = @" select T.* from TaiKhoan T where FullName =N'" + TenNV + "' ";
conn = DataProvider.Connect();
DataTable dt = DataProvider.dataSQuery(sQuery, conn);
if (dt.Rows.Count == 0)
{
return(null);
}
List <TaiKhoan_DTO> Acc = new List <TaiKhoan_DTO>();
for (int i = 0; i < dt.Rows.Count; i++)
{
TaiKhoan_DTO K = new TaiKhoan_DTO();
K.MaTk = dt.Rows[i]["MaTK"].ToString();
K.TaiKhoan = dt.Rows[i]["TaiKhoan"].ToString();
K.MatKhau = dt.Rows[i]["MatKhau"].ToString();
K.TrangThai = int.Parse(dt.Rows[i]["TrangThai"].ToString());
K.ChuTich = dt.Rows[i]["ChuTich"].ToString();
K.FullName = dt.Rows[i]["FullName"].ToString();
K.MaNV = dt.Rows[i]["MaNV"].ToString();
Acc.Add(K);
}
return(Acc);
}
public List <HocSinh> getStudents()
{
string sql = "SELECT hs.MaHS,hs.HoTen,hs.GioiTinh,hs.NgaySinh,hs.DiaChi,hs.SDT,hs.Email,hs.TinhTrang,hs.MaLop, l.TenLop from HocSinh hs, Lop l where hs.MaLop = l.MaLop";
int maHS, maLop;
DateTime ngaySinh;
string hoTen, gioiTinh, diaChi, sdt, email, tenLop;
bool tinhTrang;
List <HocSinh> list = new List <HocSinh>();
dp.Connect();
try
{
SqlDataReader dr = dp.myExecuteReader(sql);
while (dr.Read())
{
maHS = dr.GetInt32(0);
hoTen = dr.GetString(1);
gioiTinh = dr.GetString(2);
ngaySinh = dr.GetDateTime(3);
diaChi = dr.GetString(4);
sdt = dr.GetString(5);
email = dr.GetString(6);
tinhTrang = (bool)dr[7];
maLop = dr.GetInt32(8);
tenLop = dr.GetString(9);
HocSinh hs = new HocSinh(maHS, hoTen, gioiTinh, ngaySinh, diaChi, sdt, email, tinhTrang, maLop, tenLop);
list.Add(hs);
list.ForEach(x =>
{
x.TinhTrangText = x.TinhTrang ? "Đang học" : "Thôi học";
});
}
dr.Close();
return(list);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
dp.DisConnect();
}
}
//public static List<DangNhap_DTO> getNameUser()
//{
// string sQuery = "select *from TaiKhoan";
// conn = DataProvider.Connect();
// DataTable dt = DataProvider.dataSQuery(sQuery, conn);
// if (dt.Rows.Count == 0)
// {
// return null;
// }
// List<DangNhap_DTO> Acc = new List<DangNhap_DTO>();
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// DangNhap_DTO N = new DangNhap_DTO();
// N.MaTK = dt.Rows[i]["MaTK"].ToString();
// N.Account = dt.Rows[i]["TaiKhoan"].ToString();
// N.PassWord = dt.Rows[i]["MatKhau"].ToString();
// N.TrangThai = int.Parse(dt.Rows[i]["TrangThai"].ToString());
// N.Note = dt.Rows[i]["Note"].ToString();
// Acc.Add(N);
// }
// return Acc;
//}
public static bool Login(string userName, string passWord)
{
string sQuery = "SELECT * FROM TaiKhoan WHERE TaiKhoan= '" + userName + "' AND MatKhau = '" + passWord + "' ";
conn = DataProvider.Connect();
DataTable dt = DataProvider.dataSQuery(sQuery, conn);
return(dt.Rows.Count > 0);
}
// thêm update số lượng dịch vụ
public static bool UpdateSoLuong(DichVu_DTO DT)
{
string sQuery = string.Format(@"update DichVu set SoLuong='{0}' where MaDV='{1}'", DT.SoLuong, DT.MaDV);
conn = DataProvider.Connect();
bool kq = DataProvider.queryWithoutData(sQuery, conn);
DataProvider.DongKetNoi(conn);
return(kq);
}
public void XoaMon(DTO_Order dm, int maban)
{
SqlConnection cnn = dp.getConnect();
dp.Connect();//ham nay de ket noi thay thi cnn.open();
string XoaMon = "DELETE FROM HoaDon WHERE MaMonNuoc = '" + dm.MaMonNuoc + "'" + "and MaBan ='" + maban + "'";
SqlCommand cmd = new SqlCommand(XoaMon, cnn);
cmd.ExecuteNonQuery();
}
// thêm dịch vụ
public static bool AddService(DichVu_DTO dv)
{
string sQuery = string.Format(@"insert into DichVu values('{0}',N'{1}','{2}','{3}')", dv.MaDV, dv.TenDV, dv.SoLuong, dv.GiaDV);
conn = DataProvider.Connect();
bool kq = DataProvider.queryWithoutData(sQuery, conn);
DataProvider.DongKetNoi(conn);
return(kq);
}
// thêm nhân viên
public static bool AddEmployees(NhanVien_DTO nv)
{
string sQuery = string.Format(@"insert into NhanVien values('{0}',N'{1}',N'{2}','{3}',N'{4}')", nv.MaNV, nv.TenNV, nv.ChucVu, nv.NamSinh, nv.GioiTinh);
conn = DataProvider.Connect();
bool kq = DataProvider.queryWithoutData(sQuery, conn);
DataProvider.DongKetNoi(conn);
return(kq);
}
public static bool deleteEmployees(NhanVien_DTO nv)
{
string sQuery = string.Format(@"delete from nhanvien where manv=N'{0}'", nv.MaNV);
conn = DataProvider.Connect();
bool kq = DataProvider.queryWithoutData(sQuery, conn);
DataProvider.DongKetNoi(conn);
return(kq);
}