//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)); }