public bool themNhatKy(NhatKy objNhatKy)
{
bool isSuccess = false;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "INSERT INTO nhatky VALUES('"
+ objNhatKy.MaHopDong + "','"
+ objNhatKy.MaPhong + "','"
+ objNhatKy.Cmnd + "',N'"
+ objNhatKy.TenKhachHang + "','"
+ objNhatKy.SoNguoi + "','"
+ objNhatKy.NgayDatPhong + "','"
+ objNhatKy.NgayTraPhong + "','"
+ objNhatKy.SoTien + "','"
+ objNhatKy.MaNhanVien + "')";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
isSuccess = command.ExecuteNonQuery() > 0;
conn.Close();
return(isSuccess);
}
public float tinhTienThuePhong(NhatKy objNhatKy)
{
float tienPhaiTra = 0;
int soPhutThuePhong = 0;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "SELECT DATEDIFF(minute,'"
+ objNhatKy.NgayDatPhong + "','" + objNhatKy.NgayTraPhong + "')";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
soPhutThuePhong = (int)command.ExecuteScalar();
conn.Close();
List <Phong> lstPhong = phongRepository.timPhongTheoMaPhong(objNhatKy.MaPhong);
float giaTienPhong = lstPhong[0].GiaTien;
if (soPhutThuePhong < 1)
{
tienPhaiTra = (float)10000;
}
else
{
tienPhaiTra = (giaTienPhong / 60) * soPhutThuePhong;
}
return(tienPhaiTra);
}
public List <NhatKy> timNhatKyTheoMaHopDong(string maHopDong)
{
List <NhatKy> lstNhatKy = new List <NhatKy>();
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "SELECT mahopdong,maphong,cmnd,tenkhachhang,songuoi,ngaydatphong,ngaytraphong,sotien,manhanvien FROM nhatky WHERE mahopdong LIKE '%" + maHopDong + "%'";
SqlDataReader reader = null;
SqlCommand command = new SqlCommand(strQuery, conn);
reader = command.ExecuteReader();
while (reader.Read())
{
NhatKy objNhatKy = new NhatKy();
objNhatKy.MaHopDong = reader["mahopdong"].ToString();
objNhatKy.MaPhong = reader["maphong"].ToString();
objNhatKy.Cmnd = reader["cmnd"].ToString();
objNhatKy.TenKhachHang = reader["tenkhachhang"].ToString();
objNhatKy.SoNguoi = (int)reader["songuoi"];
objNhatKy.NgayDatPhong = (DateTime)reader["ngaydatphong"];
objNhatKy.NgayTraPhong = (DateTime)reader["ngaytraphong"];
//objNhatKy.SoTien = (float)reader.GetDouble(8);
objNhatKy.SoTien = float.Parse(reader[7].ToString());
objNhatKy.MaNhanVien = reader["manhanvien"].ToString();
lstNhatKy.Add(objNhatKy);
}
conn.Close();
return(lstNhatKy);
}
public List <NhatKyDangNhap> timKiemTheoMaNhanVien(string maNhanVien)
{
List <NhatKyDangNhap> lstNhatKyDangNhap = new List <NhatKyDangNhap>();
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "SELECT manhanvien,quyenhan,hoten,thoigian FROM nhatkydangnhap WHERE manhanvien LIKE '%" + maNhanVien + "%'";
SqlDataReader reader = null;
SqlCommand command = new SqlCommand(strQuery, conn);
reader = command.ExecuteReader();
while (reader.Read())
{
NhatKyDangNhap objNhatKy = new NhatKyDangNhap();
objNhatKy.MaNhanVien = reader["manhanvien"].ToString();
objNhatKy.QuyenHan = reader["quyenhan"].ToString();
objNhatKy.HoTen = reader["hoten"].ToString();
objNhatKy.ThoiGianDangNhap = (DateTime)reader["thoigian"];
lstNhatKyDangNhap.Add(objNhatKy);
}
conn.Close();
return(lstNhatKyDangNhap);
}
public bool dangNhap(NhanVien objNhanVien)
{
List <NhanVien> lstNhanVien = new List <NhanVien>();
bool isSuccess = false;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "SELECT manhanvien,matkhau,quyenhan,hoten FROM nhanvien WHERE manhanvien='"
+ objNhanVien.MaNhanVien + "'AND matkhau='"
+ objNhanVien.MatKhau + "'AND quyenhan=N'"
+ objNhanVien.QuyenHan + "'";
SqlDataReader reader = null;
SqlCommand command = new SqlCommand(strQuery, conn);
reader = command.ExecuteReader();
while (reader.Read())
{
NhanVien objNhanVien1 = new NhanVien();
objNhanVien1.MaNhanVien = reader["manhanvien"].ToString();
objNhanVien1.MatKhau = reader["matkhau"].ToString();
objNhanVien1.QuyenHan = reader["quyenhan"].ToString();
objNhanVien1.HoTen = reader["hoten"].ToString();
lstNhanVien.Add(objNhanVien1);
}
isSuccess = lstNhanVien.Count() > 0;
conn.Close();
return(isSuccess);
}
public List <Phong> layDanhSachPhongTrong()
{
List <Phong> lstPhongTrong = new List <Phong>();
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "SELECT maphong,tang,loaiphong,mota,giatien,tinhtrang FROM phong WHERE tinhtrang=N'Trống'";
SqlDataReader reader = null;
SqlCommand command = new SqlCommand(strQuery, conn);
reader = command.ExecuteReader();
while (reader.Read())
{
Phong objPhong = new Phong();
objPhong.MaPhong = reader["maphong"].ToString();
objPhong.Tang = (int)reader["tang"];
objPhong.LoaiPhong = reader["loaiphong"].ToString();
objPhong.MoTa = reader["mota"].ToString();
objPhong.GiaTien = (float)reader.GetDouble(4);
objPhong.TinhTrang = reader["tinhtrang"].ToString();
lstPhongTrong.Add(objPhong);
}
conn.Close();
return(lstPhongTrong);
}
public List <PhongDat> timKiemPhongDatTheoMaPhong(string maPhong)
{
List <PhongDat> lstPhongDat = new List <PhongDat>();
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "SELECT mahopdong,maphong,cmnd,tenkhachhang,songuoi,ngaydatphong,manhanvien FROM phongdat WHERE maphong='" + maPhong + "'";
SqlDataReader reader = null;
SqlCommand command = new SqlCommand(strQuery, conn);
reader = command.ExecuteReader();
while (reader.Read())
{
PhongDat objPhongDat = new PhongDat();
objPhongDat.MaHopDong = reader["mahopdong"].ToString();
objPhongDat.MaPhong = reader["maphong"].ToString();
objPhongDat.Cmnd = reader["cmnd"].ToString();
objPhongDat.TenKhachHang = reader["tenkhachhang"].ToString();
objPhongDat.SoNguoi = (int)reader["songuoi"];
objPhongDat.NgayDatPhong = (DateTime)reader["ngaydatphong"];
objPhongDat.MaNhanVien = reader["manhanvien"].ToString();
lstPhongDat.Add(objPhongDat);
}
conn.Close();
return(lstPhongDat);
}
public bool xoaPhongDat(string maHopDong)
{
bool isSuccess = false;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "DELETE FROM phongdat WHERE mahopdong='" + maHopDong + "'";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
isSuccess = command.ExecuteNonQuery() > 0;
conn.Close();
return(isSuccess);
}
public bool xoaNhanVien(string maNhanVien)
{
bool isSuccess = false;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "DELETE FROM nhanvien WHERE manhanvien='" + maNhanVien + "'";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
isSuccess = command.ExecuteNonQuery() > 0;
conn.Close();
return(isSuccess);
}
public int demSoPhongDuocThue()
{
int ketQua = 0;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "SELECT COUNT (tinhtrang) FROM phong WHERE tinhtrang=N'Đang được thuê'";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
ketQua = (int)command.ExecuteScalar();
conn.Close();
return(ketQua);
}
public bool capNhatTinhTrangPhong(Phong objPhong)
{
bool isSuccess = false;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "UPDATE phong SET tinhtrang=N'"
+ objPhong.TinhTrang + "'WHERE maphong='" + objPhong.MaPhong + "'";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
isSuccess = command.ExecuteNonQuery() > 0;
conn.Close();
return(isSuccess);
}
public bool themNhanVien(NhanVien objNhanVien)
{
bool isSuccess = false;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "INSERT INTO nhanvien VALUES(N'"
+ objNhanVien.MaNhanVien + "',N'" + objNhanVien.MatKhau
+ "',N'" + objNhanVien.QuyenHan + "',N'"
+ objNhanVien.HoTen + "')";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
isSuccess = command.ExecuteNonQuery() > 0;
conn.Close();
return(isSuccess);
}
public bool capNhatNhanVien(NhanVien objNhanVien)
{
bool isSuccess = false;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "UPDATE nhanvien SET matkhau=N'"
+ objNhanVien.MatKhau + "',quyenhan=N'"
+ objNhanVien.QuyenHan + "',hoten=N'"
+ objNhanVien.HoTen + "'WHERE manhanvien='" + objNhanVien.MaNhanVien + "'";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
isSuccess = command.ExecuteNonQuery() > 0;
conn.Close();
return(isSuccess);
}
public bool themNhatKy(NhatKyDangNhap obj)
{
bool isSuccess = false;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "INSERT INTO nhatkydangnhap VALUES('"
+ obj.MaNhanVien + "',N'"
+ obj.QuyenHan + "',N'"
+ obj.HoTen + "','"
+ obj.ThoiGianDangNhap + "')";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
isSuccess = command.ExecuteNonQuery() > 0;
conn.Close();
return(isSuccess);
}
public bool capNhatThongTinPhong(Phong objPhong)
{
bool isSuccess = false;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "UPDATE phong SET tang='"
+ objPhong.Tang + "',loaiphong=N'"
+ objPhong.LoaiPhong + "',mota=N'"
+ objPhong.MoTa + "',giatien=N'"
+ objPhong.GiaTien + "'WHERE maphong='" + objPhong.MaPhong + "'";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
isSuccess = command.ExecuteNonQuery() > 0;
conn.Close();
return(isSuccess);
}
public bool datPhong(PhongDat objPhongDat)
{
bool isSuccess = false;
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "INSERT INTO phongdat VALUES('"
+ objPhongDat.MaHopDong + "','"
+ objPhongDat.MaPhong + "','"
+ objPhongDat.Cmnd + "',N'"
+ objPhongDat.TenKhachHang + "','"
+ objPhongDat.SoNguoi + "','"
+ objPhongDat.NgayDatPhong + "','"
+ objPhongDat.MaNhanVien + "')";
SqlCommand command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = strQuery;
isSuccess = command.ExecuteNonQuery() > 0;
conn.Close();
return(isSuccess);
}
public List <NhanVien> layDanhSachQuyenHanNhanVien()
{
List <NhanVien> lstNhanVien = new List <NhanVien>();
SqlConnection conn = null;
conn = DataAccessConnection.getConnection();
conn.Open();
string strQuery = "SELECT manhanvien,matkhau,quyenhan,hoten FROM nhanvien WHERE quyenhan=N'Nhân viên'";
SqlDataReader reader = null;
SqlCommand command = new SqlCommand(strQuery, conn);
reader = command.ExecuteReader();
while (reader.Read())
{
NhanVien objNhanVien = new NhanVien();
objNhanVien.MaNhanVien = reader["manhanvien"].ToString();
objNhanVien.MatKhau = reader["matkhau"].ToString();
objNhanVien.QuyenHan = reader["quyenhan"].ToString();
objNhanVien.HoTen = reader["hoten"].ToString();
lstNhanVien.Add(objNhanVien);
}
conn.Close();
return(lstNhanVien);
}
