public DataTable ShowKhachHang()
{
string str = @"select MaKH as [Mã khách hàng], TenKH as [Tên khách hàng],NgaySinh as [Ngày Sinh],
GioiTinh as [Giới tính],QueQuan as [Quê quán],CMND as[Số CMND],
SDT as[Số điện thoại],QuocTich as [Quốc tịch] from tblKhachHang";
SqlConnection con = new SqlConnection(kn.GetConnect());
SqlDataAdapter ad = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
ad.Fill(dt);
return(dt);
}
public string ThuePhong(string makh, int sophong, string tgthue, string tgtra, int tiendc)
{
KetNoi kn = new KetNoi();
SqlConnection con = new SqlConnection(kn.GetConnect());
con.Open();
string str = "ThemThuePhong";
SqlCommand cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@idkhach", makh);
cmd.Parameters.AddWithValue("@idphong", Convert.ToInt32(sophong));
cmd.Parameters.AddWithValue("@tgbd", Convert.ToDateTime(tgthue));
cmd.Parameters.AddWithValue("@tgtp", Convert.ToDateTime(tgtra));
cmd.Parameters.AddWithValue("@tiendc", tiendc);
SqlParameter para = new SqlParameter("@kq", SqlDbType.Int);
para.Direction = ParameterDirection.Output;
cmd.Parameters.Add(para);
cmd.ExecuteNonQuery();
string kq = para.Value.ToString();
con.Close();
cmd.Dispose();
return(kq);
}
public DataTable ShowTrangbi()
{
string str = @"select MaLPhong as [Mã Loại Phòng],MaTB as [Mã Thiết Bị],Soluong as [Số Lượng]
from tblTrangbiTB";
SqlConnection con = new SqlConnection(kn.GetConnect());
DataTable dt = new DataTable();
SqlDataAdapter ad = new SqlDataAdapter(str, con);
ad.Fill(dt);
con.Close();
return(dt);
}
public string LayMaKH(string cmnd)
{
KetNoi kn=new KetNoi();
string query = string.Format("Select distinct dbo.[LayIDKH]({0}) from tblPhong",cmnd);
SqlConnection con = new SqlConnection(kn.GetConnect());
con.Open();
SqlCommand cmd = new SqlCommand(query,con);
string makh = cmd.ExecuteScalar().ToString();
return makh;
}
public DataTable LayPhongTrong()
{
KetNoi kn=new KetNoi();
string query = string.Format("Select * from LayPhongTrong()");
SqlConnection con = new SqlConnection(kn.GetConnect());
SqlCommand cmd = new SqlCommand(query, con);
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
ad.Fill(dt);
return dt;
}
public DataTable LayPhongTrong()
{
KetNoi kn = new KetNoi();
string query = string.Format("Select * from LayPhongTrong()");
SqlConnection con = new SqlConnection(kn.GetConnect());
SqlCommand cmd = new SqlCommand(query, con);
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
ad.Fill(dt);
return(dt);
}
public string LayMaKH(string cmnd)
{
KetNoi kn = new KetNoi();
string query = string.Format("Select distinct dbo.[LayIDKH]({0}) from tblPhong", cmnd);
SqlConnection con = new SqlConnection(kn.GetConnect());
con.Open();
SqlCommand cmd = new SqlCommand(query, con);
string makh = cmd.ExecuteScalar().ToString();
return(makh);
}
public DataTable ShowPhong()
{
string str = @"select SoPhong as [Số phòng],MaPLoai as [Mã loại phòng],TinhTrang as [Tình trạng],
HienTrang as [Hiện trạng],SoDT as [Số điện thoại] from tblPhong";
SqlConnection con = new SqlConnection(kn.GetConnect());
SqlDataAdapter ad = new SqlDataAdapter(str, con);
DataTable dt = new DataTable();
ad.Fill(dt);
return(dt);
}
public DataTable ShowSDDV()
{
SqlConnection con = new SqlConnection(kn.GetConnect());
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"select sophong as [Mã Phòng],MaDV as [Mã Dịch vụ],
soluong as [Số Lượng],ngaysd as [Ngày Sử dụng] from tblSuDungDV";
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
SqlDataAdapter ad = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
ad.Fill(dt);
return(dt);
}
private void ThucHienThue()
{
if (dtgThuePhong.RowCount == 0)
{
return;
}
string makh = tp.LayMaKH(txtCMND.Text);
if (makh == null)
{
MessageBox.Show("Không có khách hàng.yêu cầu kiểm tra lại");
return;
}
try
{
KetNoi kn = new KetNoi();
KhachHang kh = new KhachHang();
SqlConnection con = new SqlConnection(kn.GetConnect());
for (int i = 0; i < dtgThuePhong.RowCount; i++)
{
if (dtgThuePhong[0, i].Selected == true)
{
string sophong = dtgThuePhong[1, i].Value.ToString();
string kq = tp.ThuePhong(makh, Convert.ToInt32(sophong), dTPTGBD.Text, dTPTGKT.Text, Convert.ToInt32(txtTienThue.Text));
if (kq == "1")
{
MessageBox.Show("Phòng không đảm bảo chất lượng");
}
else
{
MessageBox.Show("Đăng kí thuê thành công");
}
}
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
}
public string ThuePhong(string makh, int sophong, string tgthue, string tgtra, int tiendc)
{
KetNoi kn = new KetNoi();
SqlConnection con = new SqlConnection(kn.GetConnect());
con.Open();
string str = "ThemThuePhong";
SqlCommand cmd = new SqlCommand(str, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@idkhach", makh);
cmd.Parameters.AddWithValue("@idphong", Convert.ToInt32(sophong));
cmd.Parameters.AddWithValue("@tgbd",Convert.ToDateTime(tgthue));
cmd.Parameters.AddWithValue("@tgtp",Convert.ToDateTime(tgtra));
cmd.Parameters.AddWithValue("@tiendc", tiendc);
SqlParameter para = new SqlParameter("@kq", SqlDbType.Int);
para.Direction = ParameterDirection.Output;
cmd.Parameters.Add(para);
cmd.ExecuteNonQuery();
string kq = para.Value.ToString();
con.Close();
cmd.Dispose();
return kq;
}
private void ThucHienThue()
{
if (dtgThuePhong.RowCount == 0) return;
string makh = tp.LayMaKH(txtCMND.Text);
if (makh == null)
{
MessageBox.Show("Không có khách hàng.yêu cầu kiểm tra lại");
return;
}
try
{
KetNoi kn = new KetNoi();
KhachHang kh = new KhachHang();
SqlConnection con = new SqlConnection(kn.GetConnect());
for (int i = 0; i < dtgThuePhong.RowCount; i++)
{
if (dtgThuePhong[0, i].Selected == true)
{
string sophong = dtgThuePhong[1, i].Value.ToString();
string kq = tp.ThuePhong(makh, Convert.ToInt32(sophong), dTPTGBD.Text, dTPTGKT.Text, Convert.ToInt32(txtTienThue.Text));
if (kq == "1")
MessageBox.Show("Phòng không đảm bảo chất lượng");
else
MessageBox.Show("Đăng kí thuê thành công");
}
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
}
