public DataTable HienThiKhach()
{
string sql = "SELECT * FROM tblKhachThuePhong";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
SqlDataAdapter da = new SqlDataAdapter(sql, con);
da.Fill(dt);
return(dt);
}
public DataTable ShowMaLPhong()
{
string str = @"select MaPhong as [Mã Phòng] from tblPhong";
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(str, con);
ad.Fill(dt);
con.Close();
return(dt);
}
public DataTable ShowTrangbi()
{
string str = @"select MaPhong as [Mã Phòng],MaDoDung as [Mã Đồ Dùng],Soluong as [Số Lượng]
from tblDoDung";
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(str, con);
ad.Fill(dt);
con.Close();
return(dt);
}
public void XoaKhach(string MaKH)
{
string sql = "Xoa_KhachThuePhong";
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MaKH", MaKH);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
// Tìm kiếm phòng theo tên
public DataTable TKPhong_TenPhong(string TenPhong)
{
string sql = "SELECT * FROM tblPhong WHERE TenPhong LIKE N'%' + @TenPhong + '%'";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter();
cmd.Parameters.AddWithValue("@TenPhong", TenPhong);
da.SelectCommand = cmd;
da.Fill(dt);
return(dt);
}
// tìm kiếm khách theo CMND
public DataTable TKKhach_CMND(string CMND)
{
string sql = "SELECT * FROM tblKhachThuePhong WHERE CMND LIKE N'%' + @CMND + '%'";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter();
cmd.Parameters.AddWithValue("@CMND", CMND);
da.SelectCommand = cmd;
da.Fill(dt);
return(dt);
}
//Show đồ dùng trong phòng theo mã
public DataTable ShowDoDung_Ma(string MaPhong)
{
string sql = "SELECT * FROM tblPhong WHERE MaPhong=@MaPhong";
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter();
cmd.Parameters.AddWithValue("@MaPhong", MaPhong);
da.SelectCommand = cmd;
da.Fill(dt);
return(dt);
}
public void ThemPhong(string TenPhong, string LoaiPhong)
{
string sql = "ADDPhong";
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TenPhong", TenPhong);
cmd.Parameters.AddWithValue("@LoaiPhong", LoaiPhong);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
public void ThemKhach(string TenKH, string GT, string NgaySinh, string CMND)
{
string sql = "ADDKhachThuePhong";
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@TenKH", TenKH);
cmd.Parameters.AddWithValue("@GT", GT);
cmd.Parameters.AddWithValue("@NgaySinh", DateTime.Parse(NgaySinh));
cmd.Parameters.AddWithValue("@CMND", CMND);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
public DataTable DangNhap(string Username, string Pass)
{
DataTable dt = new DataTable();
string sql = "LogIn";
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", Username);
cmd.Parameters.AddWithValue("@Pass", Pass);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return(dt);
}
public void GhiHD(string MaHD, string MaCTHD, string MaKH, string MaNV, string NgayLapHD)
{
string sql = "Ins_HD";
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MaHD", MaHD);
cmd.Parameters.AddWithValue("@MaCTHD", MaCTHD);
cmd.Parameters.AddWithValue("@MaKH", MaKH);
cmd.Parameters.AddWithValue("@MaNV", MaNV);
cmd.Parameters.AddWithValue("@NgayLapHD", NgayLapHD);
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}
public DataTable TimKiemTBTB(string MaPhong, string MaDoDung, int chose)
{
string str = "TimKiemTBTB";
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
con.Open();
SqlCommand cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MaPhong", MaPhong);
cmd.Parameters.AddWithValue("@MaDoDung", MaDoDung);
cmd.Parameters.AddWithValue("@chose", chose);
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(cmd);
ad.Fill(dt);
cmd.Dispose();
con.Close();
return(dt);
}
public string XoaTrangBi(string MaPhong, string MaDoDung)
{
string str = "XoaTBTB";
SqlConnection con = new SqlConnection(KetNoiDB.KetNoi());
con.Open();
SqlCommand cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MaPhong", MaPhong);
cmd.Parameters.AddWithValue("@MaDoDung", MaDoDung);
SqlParameter para = new SqlParameter("@kq", SqlDbType.Int);
para.Direction = ParameterDirection.Output;
cmd.Parameters.Add(para);
cmd.ExecuteNonQuery();
string kq = para.Value.ToString();
cmd.Dispose();
con.Close();
return(kq);
}