//Lấy thông tin nhân viên
public static int GetIdNhanVien(string username)
{
string sql = "select id from NHANVIEN nv, TAIKHOAN tk where nv.TenTaiKhoan = tk.TenNguoiDung and tk.TenNguoiDung = N'" + username + "'";
DataTable data = KetNoiCSDL.Query(sql);
return((int)data.Rows[0]["id"]);
}
// Hàm lấy thông tin chi tiết nhân viên
public static Employee GetEmployee(string id)
{
string sql = "select * from NHANVIEN where id = '" + id + "'";
DataTable data = KetNoiCSDL.Query(sql);
DataRow row = data.Rows[0];
Employee currentEmployee = new Employee(row);
return(currentEmployee);
}
//public static bool Login(string username, string password) // chức năng đăng nhập
//{
// string sql = "Select * from TAIKHOAN where TenNguoiDung= '"+username+"' and MatKhau = '"+password+"'";
// return KetNoiCSDL.Query(sql).Rows.Count == 1? true : false ;
//}
public static bool CheckExistsTenNguoiDung(string username)
{
string sql = "select * from TAIKHOAN where TenNguoiDung = N'" + username + "'";
DataTable data = KetNoiCSDL.Query(sql);
if (data.Rows.Count < 1)
{
return(false);
}
return(true);
}
// Lấy id hóa đơn hiện tại của bàn ăn (@param idTable)
// Thành công : bill ID
// Thất bại : -1
public static int GetUnCheckBillIDByTableID(int idTable)
{
string sql = "select * from HOADON where idTable = " + idTable + " and TrangThaiHoaDon=0";
DataTable data = KetNoiCSDL.Query(sql);
if (data.Rows.Count > 0)
{
return(Convert.ToInt32(data.Rows[0]["id"].ToString()));
}
return(-1);
}
public static List <DTO.Menu> GetMenuListByTable(int id)
{
List <DTO.Menu> menus = new List <DTO.Menu>();
string sql = "select mon.TenMon, cthd.SoLuong, mon.GiaTien, cthd.SoLuong*GiaTien as TongTien from CHITIETHOADON cthd, HOADON hd, MONAN mon where cthd.idHoaDon = hd.id and cthd.idMonAn = mon.id and hd.TrangThaiHoaDon=0 and hd.idTable= " + id;
DataTable data = KetNoiCSDL.Query(sql);
foreach (DataRow row in data.Rows)
{
DTO.Menu menu = new DTO.Menu(row);
menus.Add(menu);
}
return(menus);
}
//nếu trả về true thì người dùng có quyền sử dụng
public static bool GetAuthority(string username)
{
bool result = false;
string sql = "Select loai.TenLoaiTK from TAIKHOAN tk, LOAITK loai where tk.LoaiTK=loai.idLoaiTK and TenNguoiDung = N'" + username + "'";
try
{
DataTable data = KetNoiCSDL.Query(sql);
foreach (DataRow row in data.Rows)
{
if ("Admin" == row["TenLoaiTK"].ToString())
{
result = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show("Lỗi xảy ra khi truy vấn dữ liệu hoặc kết nối với server thất bại ! " + ex);
}
return(result);
}
public static string GetTenNguoiDung(string username, string password)
{
string id = "";
string sql = "Select * from TAIKHOAN where TenNguoiDung= N'" + username + "' and MatKhau = HASHBYTES('SHA2_256', N'" + password + "')";
try
{
DataTable data = KetNoiCSDL.Query(sql);
if (data != null)
{
foreach (DataRow row in data.Rows)
{
id = row["TenNguoiDung"].ToString();
}
}
}
catch (Exception ex)
{
MessageBox.Show("Lỗi xảy ra khi truy vấn dữ liệu hoặc kết nối với server thất bại !" + ex);
}
return(id);
}
// test
public static DataTable GetDataReport(string idHoaDon)
{
string sql = "select mon.TenMon, cthd.SoLuong, mon.GiaTien, cthd.SoLuong*GiaTien as TongTien, ban.id as TenBan, hd.ThoiGianVao, hd.ThoiGianRa from CHITIETHOADON cthd, HOADON hd, MONAN mon, BANAN ban where hd.idTable = ban.id and cthd.idHoaDon = hd.id and cthd.idMonAn = mon.id and hd.TrangThaiHoaDon=0 and hd.id = " + idHoaDon;
return(KetNoiCSDL.Query(sql));
}
public static DataTable GetBillItem(DTO.CTHD item)
{
string sql = "select * from CHITIETHOADON where idHoaDon = '" + item.IdHoaDon + "' and idMonAn = '" + item.IdMonAn + "'";
return(KetNoiCSDL.Query(sql));
}
public static DataTable CheckTableStatus(string id)
{
string sql = "select * from BANAN where id = '" + id + "'";
return(KetNoiCSDL.Query(sql));
}
public static DataTable GetSanPham(string idLoai)
{
string sql = "select * from MONAN sp where sp.idLoaiMon = " + idLoai + "";
return(KetNoiCSDL.Query(sql));
}
public static DataTable GetLoaiSP()
{
string sql = "select * from LOAIMON";
return(KetNoiCSDL.Query(sql));
}
public static DataTable FindAllSalaryFromDateToDate(string start, string end)
{
string sql = "select nv.id, nv.TenNhanVien, cv.TenChucVu, ccnv.GioBatDau, ccnv.GioKetThuc, cv.Luong, cc.Ngay, (DATEDIFF(HOUR, ccnv.GioBatDau , ccnv.GioKetThuc) * cv.Luong) as TongLuongTrongNgay from NHANVIEN nv, CHAMCONG cc, CHAMCONGNHANVIEN ccnv, CHUCVU cv where nv.id = ccnv.idNhanVien and cc.id = ccnv.idChamCong and nv.idChucVu = cv.id and cc.Ngay >= '" + start + "' and cc.Ngay <= '" + end + "'";
return(KetNoiCSDL.Query(sql));
}
public static DataTable GetAllHoaDon(string tgbatdau, string tgketthuc)
{
string sql = "select hd.id as HoaDon, sum(cthd.SoLuong* mon.GiaTien) as TongTien, hd.ThoiGianVao, hd.ThoiGianRa from CHITIETHOADON cthd, HOADON hd, MONAN mon where cthd.idHoaDon = hd.id and cthd.idMonAn = mon.id and hd.TrangThaiHoaDon = 1 and hd.ThoiGianRa >= '" + tgbatdau + "' and hd.ThoiGianRa <= '" + tgketthuc + "' Group by hd.id, hd.ThoiGianVao, hd.ThoiGianRa";
return(KetNoiCSDL.Query(sql));
}
public static DataTable GetChucVU()
{
string sql = "select * from CHUCVU ";
return(KetNoiCSDL.Query(sql));
}
public static DataTable GetTTAccount()
{
string sql = "select tk.TenNguoiDung, tk.TenHienThi, loai.TenLoaiTK from TAIKHOAN tk, LOAITK loai where tk.LoaiTK=loai.idLoaiTK";
return(KetNoiCSDL.Query(sql));
}
public static DataTable GetTTSanPham(string idMon)
{
string sql = "select TenLoai, TenMon, GiaTien from LoaiMon l, MonAn m where m.idLoaiMon = l.id and m.id = '" + idMon + "' ";
return(KetNoiCSDL.Query(sql));
}
// Chỉ lấy thông tin trong bảng CHAMCONG
public static DataTable FindTimeKeeping(string date)
{
string sql = "select * from CHAMCONG where Ngay='" + date + "'";
return(KetNoiCSDL.Query(sql));
}
// Lấy thông tin từ nhiều bảng
public static DataTable FindTimeKeepingByDate(string date)
{
string sql = "select nv.TenNhanVien, ccnv.GioBatDau, ccnv.GioKetThuc from CHAMCONG cc, CHAMCONGNHANVIEN ccnv, NHANVIEN nv where cc.id = ccnv.idChamCong and ccnv.idNhanVien = nv.id and cc.Ngay = '" + date + "'";
return(KetNoiCSDL.Query(sql));
}
public static DataTable GetDataTable()
{
string sql = "select * from BANAN";
return(KetNoiCSDL.Query(sql));
}
public static DataTable FindNhanVien(string idNhanVien, string idChamCong)
{
string sql = "select * from CHAMCONGNHANVIEN where idNhanVien = '" + idNhanVien + "' and idChamCong ='" + idChamCong + "'";
return(KetNoiCSDL.Query(sql));
}
//Thêm-Luu-Xoa
public static DataTable GetMaxIDTable()
{
string sql = "select top 1 id from BANAN order by id desc";
return(KetNoiCSDL.Query(sql));
}
// Hàm lấy thông tin danh sách nhân viên
public static DataTable GetEmployeeList()
{
string sql = "select nv.id, TenNhanVien, TenChucVu from NHANVIEN nv, CHUCVU cv where cv.id= nv.idChucVu";
return(KetNoiCSDL.Query(sql));
}