public void insertPhieuThue(string maphieu, string ngaybd, int soluongkhach, string tenphong)
{
SqlProvider.ExecuteQuery("insert into phieuthuephong values ('" + maphieu + "','" + ngaybd + "'," + soluongkhach + ",'" + tenphong + "')");
}
public void updateDonGiaTatCaCacPhongTheoLoai(string loaiphong, double dongia)
{
SqlProvider.ExecuteQuery("update phong set dongia = " + dongia + "where loaiphong = '" + loaiphong + "'");
}
public void updateTTPhongAsChuaThue()
{
SqlProvider.ExecuteQuery("exec UpdateTTPhong");
}
public void insertKH(double cmt, string ten, string loaikhach, string diachi, string maphieuthue)
{
SqlProvider.ExecuteQuery("insert into khachhang values('" + cmt + "',N'" + ten + "','" + loaikhach + "',N'" + diachi + "','" + maphieuthue + "')");
}
public DataTable selectAllHoaDon()
{
return(SqlProvider.ExecuteQuery("select * from hoadonthanhtoan"));
}
public DataTable SelectThamSo()
{
return(SqlProvider.ExecuteQuery("select*from thamso"));
}
public void updateHeSoPhuThuKhachThu3(double hesophuthu)
{
SqlProvider.ExecuteQuery("update thamso set phuthukhachthu3 = " + hesophuthu);
}
public DataTable SortByPhongDaThue()
{
return(SqlProvider.ExecuteQuery("select * from phong order by tinhtrang DESC"));
}
public DataTable SearchTenVaLoaiPhong(string textsearch)
{
return(SqlProvider.ExecuteQuery("select * from phong where tenphong like '%" + textsearch + "%' or loaiphong like'%" + textsearch + "%'"));
}
public DataTable selectMatDoSuDungPhong(int thang, int nam)
{
return(SqlProvider.ExecuteQuery("select p.tenphong,sum(hd.songaythue) TongSoNgayThue from HOADONTHANHTOAN hd join PHONG p on hd.phong = p.tenphong where year(hd.ngaythanhtoan) = " + nam + " and MONTH(hd.ngaythanhtoan) = " + thang + " group by p.tenphong, hd.songaythue"));
}
public DataTable SelectAllPhong()
{
return(SqlProvider.ExecuteQuery("select * from phong"));
}
public DataTable selectDoanhThuTheoLoaiPhong(int thang, int nam)
{
return(SqlProvider.ExecuteQuery("select p.loaiphong, SUM(hd.thanhtien) TongTien from HOADONTHANHTOAN hd join PHONG p on hd.phong = p.tenphong where year(hd.ngaythanhtoan) = " + nam + " and MONTH(hd.ngaythanhtoan) = " + thang + " group by p.loaiphong, hd.thanhtien"));
}
public DataTable SelectNam()
{
return(SqlProvider.ExecuteQuery("select year(ngaythanhtoan) from hoadonthanhtoan"));
}
public DataTable Kiemtrathanhtoan()
{
return(SqlProvider.ExecuteQuery("select pt.phong from HOADONTHANHTOAN hd join PHIEUTHUEPHONG pt on pt.maphieu = hd.phieuthue join PHONG p on p.tenphong = pt.phong where p.tinhtrang = 'da thue'"));
}
public DataTable selectAllPhieuThuePhong()
{
return(SqlProvider.ExecuteQuery("select * from phieuthuephong"));
}
public void updatePhong(string tenphong)
{
SqlProvider.ExecuteQuery("update phong set tinhtrang = 'da thue' where tenphong = '" + tenphong + "'");
}
public DataTable selectPhieuThueGanNhat(string tenphong)
{
return(SqlProvider.ExecuteQuery("select pt.* from phong p join PHIEUTHUEPHONG pt on p.tenphong = pt.phong where p.tenphong = '" + tenphong + "' and DATEDIFF(DAY, pt.ngaybd, getdate()) <= all(select DATEDIFF(DAY, pt.ngaybd, getdate()) from phong p join PHIEUTHUEPHONG pt on p.tenphong = pt.phong where p.tenphong = '" + tenphong + "')"));
}
public DataTable SearchTheoTenPhongchuathue(string tenphong)
{
return(SqlProvider.ExecuteQuery("select * from phong where tenphong like '%" + tenphong + "%' and tinhtrang = 'chua thue'"));
}
public void updateSoLuongKhachToiDa(int soluongkhach)
{
SqlProvider.ExecuteQuery("update thamso set soluongkhachtoidamoiphong = " + soluongkhach);
}
public void UpdateTTPhong(string tenphong, string loai, double dongia, string ghichu)
{
SqlProvider.ExecuteQuery("update phong set loaiphong = '" + loai + "',dongia =" + dongia + ",ghichu =N'" + ghichu + "'where tenphong = '" + tenphong + "' ");
}
public void updateHeSoPhuThuKhachNuocNgoai(double hesophuthu)
{
SqlProvider.ExecuteQuery("update thamso set phuthukhachnuocngoai = " + hesophuthu);
}
public DataTable selectGiaPhongTheoLoaiPhong(string loaiphong)
{
return(SqlProvider.ExecuteQuery("select dongia from phong where loaiphong ='" + loaiphong + "'"));
}
public DataTable selectKhachHang(string maphieuthue)
{
return(SqlProvider.ExecuteQuery("select * from khachhang where maphieuthue = '" + maphieuthue + "'"));
}
public void insertHoaDon(string mahd, string tenphong, string ngaythanhtoan, int songaythue, double dongia, double phuthu, double thanhtien, string phieuthue)
{
SqlProvider.ExecuteQuery("insert into hoadonthanhtoan values('" + mahd + "','" + tenphong + "','" + ngaythanhtoan + "'," + songaythue + "," + dongia + "," + phuthu + "," + thanhtien + ",'" + phieuthue + "')");
}