public static NpgsqlDataAdapter LayDanhSachKhachhang()
{
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
//NpgsqlConnection conn = new NpgsqlConnection("Server = 127.0.0.1; Port = 5432; User Id = admin; Password = 123456; Database = db_qlks;");
try
{
conn.Open();
}
catch (Exception)
{
throw;
}
// quite complex sql statement
string sql = "SELECT * FROM khachhang";
// data adapter making request from our connection
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
conn.Close();
return(da);
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
throw msg;
}
}
public static NpgsqlDataAdapter TimKiemKhachHang(KhachHangDTO kh)
{
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
conn.Open();
// quite complex sql statement
string sql = "SELECT * FROM khachhang WHERE makhachhang LIKE '%" + kh.MaKhachHang + "%' " +
"AND hoten LIKE '%" + kh.HoTen + "%' AND " +
"cmnd LIKE '%" + kh.CMND + "%' AND " +
"diachi LIKE '%" + kh.DiaChi + "%' AND " +
"sodienthoai LIKE '%" + kh.Sdt + "%'";
// data adapter making request from our connection
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
conn.Close();
return(da);
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
//MessageBox.Show(msg.ToString());
throw msg;
}
}
public static Boolean CapNhatTinhTrangPhongTheoMaPhong(String maphong)
{
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
conn.Open();
// quite complex sql statement
string sqlPara = "UPDATE phong SET tinhtrang=true WHERE maphong='" + maphong + "';";
NpgsqlCommand cmd = new NpgsqlCommand(sqlPara, conn);
cmd.ExecuteNonQuery();
conn.Close();
//MessageBox.Show("okie");
return(true);
}
catch (Exception)
{
// something went wrong, and you wanna know why
//MessageBox.Show(msg.ToString());
return(false);
throw;
}
}
public static NpgsqlDataAdapter LayDanhSachPhieuThue()
{
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
try
{
conn.Open();
}
catch (Exception)
{
throw;
}
// quite complex sql statement
string sql = "SELECT phieuthue.maphieuthue, phieuthue.makhachhang, hoten, manhanvien, ngaylap, soluongphong FROM phieuthue, khachhang WHERE phieuthue.makhachhang = khachhang.makhachhang";
// data adapter making request from our connection
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
conn.Close();
return(da);
}
catch (Exception msg)
{
throw msg;
}
}
public static Boolean ThemPhieuThue(PhieuThueDTO pt)
{
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
conn.Open();
// quite complex sql statement
string sqlPara = "INSERT INTO phieuthue(maphieuthue, makhachhang, manhanvien, ngaylap, soluongphong)" +
" VALUES('" + pt.MaPhieuThue + "', '" + pt.MaKhachHang + "', '" + pt.MaNhanVien + "', '" + pt.NgayLap + "', '" + pt.SoLuongPhong + "'); ";
NpgsqlCommand cmd = new NpgsqlCommand(sqlPara, conn);
cmd.ExecuteNonQuery();
conn.Close();
//MessageBox.Show("okie");
return(true);
}
catch (Exception)
{
// something went wrong, and you wanna know why
//MessageBox.Show(msg.ToString());
return(false);
throw;
}
}
public static Boolean ThemChiTietPhieuThue(ChiTietPhieuThueDTO ctpt)
{
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
conn.Open();
// quite complex sql statement
string sqlPara = "INSERT INTO chitietphieuthue( maphieuthue, maphong, ngaynhanphong, ngaytraphong, thanhtienphong, ghichu) " +
"VALUES('" + ctpt.MaPhieuThue + "', '" + ctpt.MaPhong + "', '" + ctpt.NgayThue + "', '" + ctpt.NgayTra + "', '" + ctpt.TongTien + "', '" + ctpt.GhiChu + "'); ";
//string sql = "Insert into hocsinh values('MH004', 'Hoang Trung Ba', 'Lao'," +
// "array[('Cương độ', '099987723')," +
// "('Lam Phiệt', '099989999')]::phuHuynh[]); ";
NpgsqlCommand cmd = new NpgsqlCommand(sqlPara, conn);
cmd.ExecuteNonQuery();
// data adapter making request from our connection
// NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
conn.Close();
//MessageBox.Show("okie");
return(true);
}
catch (Exception)
{
// something went wrong, and you wanna know why
//MessageBox.Show(msg.ToString());
return(false);
throw;
}
}
public static PhongDTO LayPhongTheoTenPhong(String tenphong)
{
PhongDTO phong = null;
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
try
{
conn.Open();
}
catch (Exception)
{
throw;
}
// quite complex sql statement
string sql = "SELECT maphong, tenphong, loaiphong.maloaiphong, tenloaiphong, dongia, tinhtrang FROM phong, loaiphong WHERE phong.maloaiphong = loaiphong.maloaiphong AND tenphong = '" + tenphong + "'";
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
phong = new PhongDTO();
phong.MaPhong = reader[0].ToString();
phong.TenPhong = reader[1].ToString();
phong.MaLoaiPhong = reader[2].ToString();
phong.TenLoaiPhong = reader[3].ToString();
phong.DonGia = Double.Parse(reader[4].ToString());
if (reader[5].ToString() == "true")
{
phong.LoaiTinhTrang = true;
}
else
{
phong.LoaiTinhTrang = false;
}
}
conn.Close();
return(phong);
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
throw msg;
}
}
public static PhieuThueDTO LayPhieuThueMoiNhat()
{
PhieuThueDTO phieuthue = new PhieuThueDTO();
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
try
{
conn.Open();
}
catch (Exception)
{
throw;
}
// quite complex sql statement
string sql = "SELECT * FROM phieuthue ORDER BY maphieuthue DESC LIMIT 1";
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
phieuthue.MaPhieuThue = reader[0].ToString();
phieuthue.MaKhachHang = reader[1].ToString();
phieuthue.MaNhanVien = reader[2].ToString();
phieuthue.NgayLap = DateTime.Parse(reader[3].ToString());
phieuthue.SoLuongPhong = Int32.Parse(reader[4].ToString());
if (reader[2].ToString() == "true")
{
phieuthue.TinhTrang = true;
}
else
{
phieuthue.TinhTrang = false;
}
}
conn.Close();
return(phieuthue);
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
throw msg;
}
}
public static NpgsqlDataAdapter LayDanhSachBaoCaoDoanhThuPhong(DateTime ngayDau, DateTime ngayCuoi)
{
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
try
{
conn.Open();
}
catch (Exception)
{
throw;
}
// quite complex sql statement
string sql = "select phong.maphong, tenphong, sum(thanhtienphong) as thanhtienphong " +
"from phieuthue, phong, chitietphieuthue " +
"where chitietphieuthue.maphong = phong.maphong " +
"and chitietphieuthue.maphieuthue = phieuthue.maphieuthue " +
"and ((TO_DATE(to_char(ngaylap, 'YYYY-MM-DD'), 'YYYY-MM-DD') >= to_timestamp('" + ngayDau + "', 'MM-DD-YYYY')) " +
"and (TO_DATE(to_char(ngaylap, 'YYYY-MM-DD'), 'YYYY-MM-DD') <= to_timestamp('" + ngayCuoi + "', 'MM-DD-YYYY')))" +
"group by phong.maphong";
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
//NpgsqlCommand command = new NpgsqlCommand(sql, conn);
//NpgsqlDataReader reader = command.ExecuteReader();
//while (reader.Read())
//{
// bcdt = new BaoCaoDoanhThuPhongDTO();
// bcdt.MaPhong = reader[0].ToString();
// bcdt.TenPhong = reader[1].ToString();
// bcdt.TongTien = Double.Parse(reader[2].ToString());
//}
conn.Close();
return(da);
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
throw msg;
}
}
public static KhachHangDTO LayKhachHangTheoMaKhachHang(String mkh)
{
KhachHangDTO khachhang = new KhachHangDTO();
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
try
{
conn.Open();
}
catch (Exception)
{
throw;
}
// quite complex sql statement
string sql = "SELECT * FROM khachhang Where makhachhang = '" + mkh + "'";
NpgsqlCommand command = new NpgsqlCommand(sql, conn);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
khachhang.MaKhachHang = reader[0].ToString();
khachhang.HoTen = reader[1].ToString();
khachhang.CMND = reader[2].ToString();
khachhang.NgaySinh = DateTime.Parse(reader[3].ToString());
khachhang.GioiTinh = reader[4].ToString();
khachhang.DiaChi = reader[5].ToString();
khachhang.Sdt = reader[6].ToString();
khachhang.MaLoaiKhachHang = reader[7].ToString();
}
conn.Close();
return(khachhang);
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
throw msg;
}
}
public static NpgsqlDataAdapter TimKiemPhieuThue(PhieuThueDTO pt)
{
try
{
// Making connection with Npgsql provider
NpgsqlConnection conn = new NpgsqlConnection(SqlDataAccessHelper.ConnectionString());
conn.Open();
// quite complex sql statement
string sql = "select phieuthue.maphieuthue, phieuthue.makhachhang, hoten, manhanvien, ngaylap, soluongphong from phieuthue, khachhang WHERE phieuthue.makhachhang = khachhang.makhachhang AND maphieuthue LIKE '%" + pt.MaPhieuThue + "%' AND phieuthue.makhachhang LIKE '%" + pt.MaKhachHang + "%' AND hoten LIKE '%" + pt.HoTenKhachHang + "%'";
// data adapter making request from our connection
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
conn.Close();
return(da);
}
catch (Exception msg)
{
// something went wrong, and you wanna know why
//MessageBox.Show(msg.ToString());
throw msg;
}
}
