public void AddDatPhong(string madp, string makh, string sophong, string ngaydat, string songl, string sotre, string tiencoc)
{
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string map = null;
// lấy mã phòng từ số phòng
string sql_map = "SELECT `MaPhong` FROM `phong` WHERE SoPhong='" + sophong + "'";
MySqlCommand command_map = new MySqlCommand(sql_map, mySql);
using (var reader = command_map.ExecuteReader())
{
while (reader.Read())
{
map = (string)reader["MaPhong"];
}
}
//------------------
string sql = "INSERT INTO `datphong`(`MaDP`, `MaKH`, `MaPhong`, `NgayDat`, `SoNguoiLon`, `SoTreCon`, `TienDatCoc`) " +
"VALUES ('" + madp + "','" + makh + "','" + map + "','" + ngaydat + "','" + songl + "','" + sotre + "','" + tiencoc + "')";
MySqlCommand command = new MySqlCommand(sql, mySql);
command.ExecuteReader();
}
public List <ThongTinPhong> GetAllThongTinPhong()
{
List <ThongTinPhong> ThongTin = new List <ThongTinPhong>();
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string sql = "select * FROM phong, loaiphong WHERE phong.MaLoaiPhong=loaiphong.MaLoaiPhong";
MySqlCommand command = new MySqlCommand(sql, mySql);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
ThongTinPhong TTP = new ThongTinPhong((string)reader["MaPhong"], (string)reader["TenLoaiPhong"], (int)reader["SoPhong"], (string)reader["TrangThai"], null, null, null, (double)reader["GiaTheoNgay"]);
ThongTin.Add(TTP);
}
}
for (int i = 0; i < ThongTin.Count(); i++)
{
if (ThongTin[i].TrangThai != "Trong")
{
string sqltt = "select * from phong ,datphong, ct_datphong, khachhang WHERE phong.MaPhong= datphong.MaPhong AND datphong.MaKH=khachhang.MaKH AND datphong.MaDP= ct_datphong.MaDP and phong.SoPhong=" + ThongTin[i].SoPhong + "";
MySqlCommand command_ct = new MySqlCommand(sqltt, mySql);
using (var reader1 = command_ct.ExecuteReader()) {
while (reader1.Read())
{
ThongTin[i].TenKhachHang = (string)reader1["TenKH"];
ThongTin[i].ThoiGianDen = ((DateTime)(reader1["NgayDen"])).ToString();
ThongTin[i].ThoiGianDi = reader1["NgayDi"].ToString();
}
}
}
}
return(ThongTin);
}
public void AddLoaiPhong(string maloaiphong, string tenloaiphong, string songuoilon, string sotrecon, string giatheongay)
{
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string sql = "INSERT INTO `loaiphong`(`MaLoaiPhong`, `TenLoaiPhong`, `SoNguoiLon`, `SoTreCon`, `GiaTheoNgay`, `GiaQuaDem`, `GiaTuan`, `GiaThang`) VALUES ('" + maloaiphong + "','" + tenloaiphong + "','" + songuoilon + "','" + sotrecon + "','" + giatheongay + "','','','')";
MySqlCommand command = new MySqlCommand(sql, mySql);
command.ExecuteReader();
}
//update trạng thái phòng theo số phòng
public void UpdateStatus(string status, string sophong)
{
//UPDATE `phong` SET `TrangThai`='Da Dat' WHERE phong.MaPhong='p001'
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string sql = "UPDATE `phong` SET `TrangThai`='" + status + "' WHERE phong.SoPhong='" + sophong + "' ";
MySqlCommand command = new MySqlCommand(sql, mySql);
command.ExecuteReader();
}
public void AddCT_DatPhong(string madp, string thoigiannhan, string ngayden, string ngaydi)
{
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string sql = "INSERT INTO `ct_datphong`( `MaDP`, `ThoiGianNhan`, `NgayDen`, `NgayDi`)" +
" VALUES ('" + madp + "','" + thoigiannhan + "','" + ngayden + "','" + ngaydi + "')";
MySqlCommand command = new MySqlCommand(sql, mySql);
command.ExecuteReader();
}
public void AddKhachHang(string makh, string tenkh, string cmnd, string gioitinh, string diachi, string dt, string quoctich)
{
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string sql = "INSERT INTO `khachhang`(`MaKH`, `TenKH`, `CMND`, `GioiTinh`, `DiaChi`, `DienThoai`, `QuocTich`)" +
" VALUES ('" + makh + "','" + tenkh + "','" + cmnd + "','" + gioitinh + "','" + diachi + "','" + dt + "','" + quoctich + "')";
MySqlCommand command = new MySqlCommand(sql, mySql);
command.ExecuteReader();
}
public void AddNhanVien(string tennv, string vitri, string user, string pass, string sdt, string cmnd)
{
string manv = "NV" + cmnd;
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string sql = "INSERT INTO `nhanvien`(`MaNV`, `TenNV`, `ViTri`, `UserName`, `Password`, `CMND`, `GioiTinh`, `DiaChi`, `DienThoai`, `Email`) VALUES " +
"('" + manv + "','" + tennv + "','" + vitri + "','" + user + "','" + pass + "','" + cmnd + "','','','" + sdt + "','')";
MySqlCommand command = new MySqlCommand(sql, mySql);
command.ExecuteReader();
}
// lấy thông tin của tất cả các phong
public List <Phong> GetAllPhong()
{
List <Phong> ListPhong = new List <Phong>();
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string sql = "select * from phong, loaiphong WHERE phong.MaLoaiPhong=loaiphong.MaLoaiPhong";
MySqlCommand command = new MySqlCommand(sql, mySql);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Phong P = new Phong((string)reader["MaPhong"], (string)reader["MaLoaiPhong"], (int)reader["SoPhong"], (string)reader["TrangThai"], (string)reader["TenLoaiPhong"]);
ListPhong.Add(P);
}
}
return(ListPhong);
}
public List <LoaiPhong> GetAllLoaiPhong()
{
List <LoaiPhong> loaiphong = new List <LoaiPhong>();
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string sql = "SELECT * FROM `loaiphong`";
MySqlCommand command = new MySqlCommand(sql, mySql);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
LoaiPhong LP = new LoaiPhong((string)reader["MaLoaiPhong"], (string)reader["TenLoaiPhong"], (int)reader["SoNguoiLon"], (int)reader["SoTreCon"], (double)reader["GiaTheoNgay"], (double)reader["GiaQuaDem"], (double)reader["GiaTuan"], (double)reader["GiaThang"]);
loaiphong.Add(LP);
}
}
//mySql.Close();
return(loaiphong);
}
// thống kê doah thu các tháng trong năm
public List <double> get_doanhthuthang(string nam)
{
List <double> array_doanhthu = new List <double>();
List <HoaDon> hoadon = new List <HoaDon>();
Connect_Database a = new Connect_Database();
MySqlConnection mySqlConnection = a.Connection();
string sql = "SELECT *, SUM(Gia) FROM hoadon where year(NgayThanhToan)=" + nam + " GROUP BY MONTH(NgayThanhToan)";
MySqlCommand cmd = new MySqlCommand(sql, mySqlConnection);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
array_doanhthu.Add((double)reader["SUM(Gia)"]);
}
}
return(array_doanhthu);
}
public List <NhanVien> GetAllNhanVien()
{
List <NhanVien> nhanViens = new List <NhanVien>();
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string sql = "select * from nhanvien";
MySqlCommand command = new MySqlCommand(sql, mySql);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
NhanVien NV = new NhanVien((string)reader["MaNV"], (string)reader["TenNV"], (string)reader["ViTri"], (string)reader["UserName"], (string)reader["Password"],
(string)reader["CMND"], (string)reader["GioiTinh"], (string)reader["DiaChi"], (string)reader["DienThoai"], (string)reader["Email"]);
nhanViens.Add(NV);
}
}
return(nhanViens);
}
//thêm mới 1 phòng
public void AddPhong(string maphong, string sophong, string tenlp)
{
string maloaiphong = null;
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
string sql = "SELECT MaLoaiPhong FROM `loaiphong` WHERE loaiphong.TenLoaiPhong='" + tenlp + "'";
MySqlCommand command = new MySqlCommand(sql, mySql);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
maloaiphong = (string)reader["MaLoaiPhong"];
}
}
//string sql_addphong = "INSERT INTO `phong`(`MaPhong`, `MaLoaiPhong`, `SoPhong`, `TrangThai`) VALUES ('"+maphong+"','"+maloaiphong+"','"+sophong+"','Trống')";
string sql_addphong = "INSERT INTO `phong`(`MaPhong`, `MaLoaiPhong`, `SoPhong`, `TrangThai`) VALUES ('" + maphong + "','" + maloaiphong + "','" + sophong + "','Trong')";
MySqlCommand cmd = new MySqlCommand(sql_addphong, mySql);
cmd.ExecuteReader();
}
//lấy tất cả thông tin để xuất báo cáo
public List <BaoCao> GetAllBaoCao(string nam, string thangden, string thangdi, string ngayden, string ngaydi)
{
List <BaoCao> ListBaoCao = new List <BaoCao>();
Connect_Database connect = new Connect_Database();
MySqlConnection mySql = connect.Connection();
//string sql = "SELECT vdatphong.MaDP, vkhachhang.TenKH, vnhanvien.TenNV, vphong.SoPhong, vct_datphong.NgayDen, vct_datphong.NgayDi,vhoadon.Gia" +
// " from vkhachhang, vdatphong, vct_datphong , vhoadon, vnhanvien, vphong WHERE vkhachhang.MaKH=vdatphong.MaKH AND vdatphong.MaDP=vct_datphong.MaDP AND vkhachhang.MaKH=vhoadon.MaKHThanhToan AND vnhanvien.MaNV=vhoadon.MaNV AND vphong.MaPhong=vdatphong.MaPhong and year(vhoadon.NgayThanhToan)='"+nam+"' AND month(vhoadon.NgayThanhToan)>='"+thangden+"' and month(vhoadon.NgayThanhToan)<='"+thangdi+"' AND day(vhoadon.NgayThanhToan)>='"+ngayden+"' and day(vhoadon.NgayThanhToan)<='"+ngaydi+"'";
string sql = "SELECT * " +
" from vkhachhang, vdatphong, vct_datphong , vhoadon, vnhanvien, vphong WHERE vkhachhang.MaKH=vdatphong.MaKH AND vdatphong.MaDP=vct_datphong.MaDP AND vkhachhang.MaKH=vhoadon.MaKHThanhToan AND vnhanvien.MaNV=vhoadon.MaNV AND vphong.MaPhong=vdatphong.MaPhong and year(vhoadon.NgayThanhToan)='" + nam + "' AND month(vhoadon.NgayThanhToan)>='" + thangden + "' and month(vhoadon.NgayThanhToan)<='" + thangdi + "' AND day(vhoadon.NgayThanhToan)>='" + ngayden + "' and day(vhoadon.NgayThanhToan)<='" + ngaydi + "'";
MySqlCommand command = new MySqlCommand(sql, mySql);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
BaoCao BC = new BaoCao((string)reader["MaDP"], (string)reader["TenKH"], (string)reader["TenNV"], (int)reader["SoPhong"], ((DateTime)reader["NgayDen"]).ToString(), ((DateTime)reader["NgayDi"]).ToString(), (double)reader["Gia"]);
ListBaoCao.Add(BC);
}
}
return(ListBaoCao);
}
// lấy thông tin hóa đơn
public List <HoaDon> getallhoadon()
{
//List<HoaDon> hoadon =new List<HoaDon>();
//Connect_Database a = new Connect_Database();
//MySqlConnection mySqlConnection = a.Connection();
//string sql = "select * from hoadon";
//MySqlCommand cmd = new MySqlCommand(sql, mySqlConnection);
//using (var reader = cmd.ExecuteReader())
//{
// while (reader.Read())
// {
// /*hoadon.Add(new HoaDon(reader["MaHoaDon"].ToString(), reader["MaDP"].ToString(), reader["MaKHThanhToan"].ToString(),
// reader["MaNV"].ToString(), reader["NgayThanhToan"].ToString(), (float)reader["Gia"]));
// */
// // hoadon.Add(new HoaDon(reader["FullName"].ToString(), reader["Gender"].ToString(), reader["IDCardNumber"].ToString(), reader["Address"].ToString(), reader["Point"].ToString(), (float)reader["RoomID"]));
// hoadon.Add(new HoaDon()
// {
// MaHoaDon = reader["MaHoaDon"].ToString(),
// MaDP = reader["MaDP"].ToString(),
// MaKHThanhToan = reader["MaKHThanhToan"].ToString(),
// MaNV = reader["MaNV"].ToString(),
// NgayThanhToan = reader["NgayThanhToan"].ToString(),
// Gia = (float)reader["Gia"]
// });
// }
//}
List <HoaDon> hoadon = new List <HoaDon>();
Connect_Database a = new Connect_Database();
MySqlConnection mySqlConnection = a.Connection();
DataTable dataTable = null;
try
{
switch (mySqlConnection.State)
{
case ConnectionState.Open:
string sql = "select * from hoadon";
MySqlCommand command = new MySqlCommand(sql, mySqlConnection);
command.Connection = mySqlConnection;
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = command;
DataSet dataSet = new DataSet();
da.Fill(dataSet);
dataTable = new DataTable();
da.Fill(dataTable);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
HoaDon customer = new HoaDon(dataTable.Rows[i][0].ToString(), dataTable.Rows[i][1].ToString(), dataTable.Rows[i][2].ToString(), dataTable.Rows[i][3].ToString(), dataTable.Rows[i][4].ToString(), (double)(dataTable.Rows[i][5]));
hoadon.Add(customer);
}
return(hoadon);
case ConnectionState.Closed:
throw new Exception("The database connection state is Closed");
default:
break;
}
}
catch (MySqlException)
{
}
finally
{
if (mySqlConnection.State != System.Data.ConnectionState.Closed)
{
mySqlConnection.Close();
}
}
return(hoadon);
}