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); }