public DataTable Table_No()
{
string strSQL = "SELECT MaBan,TenBan FROM Ban WHERE MaBan NOT IN (SELECT MaBan FROM HoaDon WHERE TrangThai = '0')";
strSQL = strSQL + "UNION ALL ";
strSQL = strSQL + "SELECT a.MaBan,TenBan FROM HoaDon a INNER JOIN Ban b ON a.MaBan = b.MaBan ";
strSQL = strSQL + "WHERE TrangThai = '1' AND a.MaBan NOT IN (SELECT MaBan FROM Ban) ";
return(KetNoiSQL.GetData(strSQL));
}
public DataTable XemLaiHoaDon(DateTime ngay1, DateTime ngay2, string MaBan)
{
string strSQL = "SELECT NgayHoaDon,TenBan,TenKH,TenDo_Uong,a.DonGia,a.SoLuong,ThanhTien ";
strSQL += "FROM HoaDon a LEFT JOIN Ban b ON a.MaBan = b.MaBan ";
strSQL += "LEFT JOIN KhachHang c ON a.MaKH = c.MaKH ";
strSQL += "LEFT JOIN DoUong d ON a.MaDo_Uong = d.MaDo_Uong ";
strSQL += " WHERE CONVERT(DATETIME, NgayHoaDon,103) BETWEEN CONVERT(DATETIME, '" + ngay1 + "',103) AND CONVERT(DATETIME, '" + ngay2 + "',103) AND a.MaBan ='" + MaBan + "' AND TrangThai = '1'";
return(KetNoiSQL.GetData(strSQL));
}
public int KiemTraTrung(string MaTK_NV)
{
int i = 0;
string strSQL = "SELECT * FROM TaiKhoan_NhanVien WHERE MaTK_NV = '" + MaTK_NV + "'";
DataTable dt = KetNoiSQL.GetData(strSQL);
if (dt == null || dt.Rows.Count > 0)
{
i = 0;
}
else
{
i = 1;
}
return(i);
}
public int KiemTraTrung(string MaDo_Uong)
{
int i = 0;
string strSQL = "SELECT * FROM DoUong WHERE MaDo_Uong = '" + MaDo_Uong + "'";
DataTable dt = KetNoiSQL.GetData(strSQL);
if (dt == null || dt.Rows.Count > 0)
{
i = 0;
}
else
{
i = 1;
}
return(i);
}
public DataTable DanhSachHoaDon(string MaBan)
{
string strSQL = "SELECT MaHD,TenDo_Uong,a.DonGia,SoLuong,ThanhTien FROM HoaDon a LEFT JOIN DoUong b ON a.MaDo_Uong = b.MaDo_Uong WHERE a.MaBan = '" + MaBan + "' AND TrangThai = '0'";
return(KetNoiSQL.GetData(strSQL));
}
public DataTable HoaDonTheoMa(string MaHD)
{
string strSQL = "SELECT * FROM HoaDon WHERE MaHD = '" + MaHD + "'";
return(KetNoiSQL.GetData(strSQL));
}
public DataTable Table_Yes()
{
string strSQL = "SELECT DISTINCT a.MaBan,TenBan FROM HoaDon a INNER JOIN Ban b ON a.MaBan = b.MaBan WHERE TrangThai = '0'";
return(KetNoiSQL.GetData(strSQL));
}
public DataTable DanhSachTimKiem(string MaBan, string TenBan, decimal SoLuongNguoi, string GhiChu)
{
string strSQL = string.Format("select * FROM Ban WHERE MaBan LIKE N'%" + MaBan + "%' AND TenBan LIKE N'%" + TenBan + "%' AND SoLuongNguoi LIKE '%" + SoLuongNguoi + "%' AND GhiChu LIKE N'%" + GhiChu + "%'");
return(KetNoiSQL.GetData(strSQL));
}
public DataTable DanhSachTimKiem(string MaDo_Uong, string TenDo_Uong, decimal DonGia, decimal SoLuongTon, string GhiChu)
{
string strSQL = string.Format("select * FROM DoUong WHERE MaDo_Uong LIKE N'%" + MaDo_Uong + "%' AND TenDo_Uong LIKE N'%" + TenDo_Uong + "%' AND DonGia LIKE '%" + DonGia + "%' AND SoLuongTon LIKE '%" + SoLuongTon + "%' AND GhiChu LIKE N'%" + GhiChu + "%'");
return(KetNoiSQL.GetData(strSQL));
}
public DataTable ThongTinTaiKhoanCaNhan(String MaTK_NV)
{
return(KetNoiSQL.GetData(string.Format("select MaTK_NV,TenTK_NV,SoDienThoai,DiaChi,Quyen FROM TaiKhoan_NhanVien WHERE MaTK_NV = '" + MaTK_NV + "'")));
}
public DataTable DanhSachTaiKhoan_NhanVien()
{
return(KetNoiSQL.GetData(string.Format("select MaTK_NV,TenTK_NV,SoDienThoai,DiaChi,Quyen FROM TaiKhoan_NhanVien")));
}
public DataTable DanhSachQuyen()
{
return(KetNoiSQL.GetData(string.Format("select * FROM Quyen")));
}
public DataTable DangNhapChuongTrinh(string MaTK_NV, string MatKhau, string Quyen)
{
string strSQL = "Select * From TaiKhoan_NhanVien Where MaTK_NV = '" + MaTK_NV + "' And MatKhau = '" + MatKhau + "' And Quyen = '" + Quyen + "'";
return(KetNoiSQL.GetData(strSQL));
}
public DataTable ThongKeDoanhThu(DateTime ngay1, DateTime ngay2)
{
string strSQL = "SELECT SUM(ThanhTien) AS ThanhTien,NgayHoaDon,TenBan FROM HoaDon a LEFT JOIN Ban b ON a.MaBan = b.MaBan WHERE CONVERT(DATETIME, NgayHoaDon,103) BETWEEN CONVERT(DATETIME, '" + ngay1 + "',103) AND CONVERT(DATETIME, '" + ngay2 + "',103) AND TrangThai = '1' GROUP BY NgayHoaDon,TenBan ";
return(KetNoiSQL.GetData(strSQL));
}
public DataTable DanhSachDoUong()
{
return(KetNoiSQL.GetData(string.Format("select * FROM DoUong")));
}
public DataTable DanhSachDoUongTheoGia(string MaDo_Uong)
{
return(KetNoiSQL.GetData(string.Format("select * FROM DoUong WHERE MaDo_Uong = '" + MaDo_Uong + "'")));
}
public DataTable LayMatKhau(string MaTK_NV)
{
return(KetNoiSQL.GetData(string.Format("select * FROM TaiKhoan_NhanVien WHERE MaTK_NV = '" + MaTK_NV + "'")));
}
public DataTable DanhSachTimKiem(string MaTK_NV, string TenTK_NV, string SoDienThoai, string DiaChi)
{
return(KetNoiSQL.GetData(string.Format("select * FROM TaiKhoan_NhanVien WHERE MaTK_NV LIKE N'%" + MaTK_NV + "%' AND TenTK_NV LIKE N'%" + TenTK_NV + "%' AND SoDienThoai LIKE N'%" + SoDienThoai + "%' AND DiaChi LIKE N'%" + DiaChi + "%'")));
}
public DataTable DanhSachTimKiem(string MaKH, string TenKH, string SoDienThoai, string DiaChi)
{
string strSQL = string.Format("select * FROM KhachHang WHERE MaKH LIKE N'%" + MaKH + "%' AND TenKH LIKE N'%" + TenKH + "%' AND SoDienThoai LIKE '%" + SoDienThoai + "%' AND DiaChi LIKE N'%" + DiaChi + "%'");
return(KetNoiSQL.GetData(strSQL));
}