public static Manager getManagerByMaID(string maID)
{
SqlConnection conn = DAO.getConnections();
string cmd1 = "select * from Manager where maID = '" + maID + "'";
SqlCommand cmd = new SqlCommand(cmd1);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string maID1 = da.GetValue(0).ToString();
string username1 = da.GetValue(1).ToString();
DateTime dob = DateTime.Parse(da.GetValue(2).ToString());
int gender = int.Parse(da.GetValue(3).ToString());
string mail = da.GetValue(4).ToString();
string phoneNumber = da.GetValue(5).ToString();
string name = da.GetValue(6).ToString();
Manager hp = new Manager(maID1, username1, dob, gender, mail, phoneNumber, name);
return(hp);
}
conn.Close();
return(null);
}
public static List <HistoryPayment> getHistoryPaymentsByStuID(string stuId)
{
List <HistoryPayment> list = new List <HistoryPayment>();
SqlConnection conn = DAO.getConnections();
string cmd1 = "select * from HistoryPayment where stuID = '" + stuId + "'";
SqlCommand cmd = new SqlCommand(cmd1);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
int id = int.Parse(da.GetValue(0).ToString());
string stuId1 = da.GetValue(1).ToString();
DateTime datePay = DateTime.Parse(da.GetValue(2).ToString());
string type = da.GetValue(3).ToString();
int moneyPay = int.Parse(da.GetValue(4).ToString());
string roomName = da.GetValue(5).ToString();
HistoryPayment hp = new HistoryPayment(id, stuId1, datePay, type, moneyPay, roomName);
list.Add(hp);
}
conn.Close();
return(list);
}
public static List <Manager> getManagers()
{
List <Manager> list = new List <Manager>();
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("select * from Manager");
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string maID = da.GetValue(0).ToString();
string username = da.GetValue(1).ToString();
DateTime dob = DateTime.Parse(da.GetValue(2).ToString());
int gender = int.Parse(da.GetValue(3).ToString());
string mail = da.GetValue(4).ToString();
string phoneNumber = da.GetValue(5).ToString();
string name = da.GetValue(6).ToString();
Manager hp = new Manager(maID, username, dob, gender, mail, phoneNumber, name);
list.Add(hp);
}
conn.Close();
return(list);
}
public static Student getStudentByStuID(string stuID)
{
SqlConnection conn = DAO.getConnections();
string cmd1 = "select * from Student where stuID = '" + stuID + "'";
SqlCommand cmd = new SqlCommand(cmd1);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string stuID1 = da.GetValue(0).ToString();
string username1 = da.GetValue(1).ToString();
string name = da.GetValue(2).ToString();
DateTime dob = DateTime.Parse(da.GetValue(3).ToString());
int gender = int.Parse(da.GetValue(4).ToString());
string roomName = da.GetValue(5).ToString();
int bedNo = int.Parse(da.GetValue(6).ToString());
DateTime bookingDate = DateTime.Parse(da.GetValue(7).ToString());
int moneyAccount = int.Parse(da.GetValue(8).ToString());
int debt = int.Parse(da.GetValue(9).ToString());
Student hp = new Student(stuID1, username1, name, dob, gender, roomName, bedNo, bookingDate, moneyAccount, debt);
return(hp);
}
conn.Close();
return(null);
}
public static List <ElectricityWaterBills> GetElectricityWaterBillsByRoom(string roomName)
{
List <ElectricityWaterBills> electricityWaterBills = new List <ElectricityWaterBills>();
SqlConnection conn = DAO.getConnections();
string sql = "select * from ElectricityWaterBills where roomName = '" + roomName + "'";
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
int id = int.Parse(da.GetValue(0).ToString());
string roomName1 = da.GetValue(1).ToString();
DateTime monthYear = DateTime.Parse(da.GetValue(2).ToString());
int soDien = int.Parse(da.GetValue(3).ToString());
int soNuoc = int.Parse(da.GetValue(4).ToString());
int paid = int.Parse(da.GetValue(5).ToString());
ElectricityWaterBills account = new ElectricityWaterBills(id, roomName1, monthYear, soDien, soNuoc, paid);
electricityWaterBills.Add(account);
}
conn.Close();
return(electricityWaterBills);
}
public static List <Request> getRequests()
{
List <Request> list = new List <Request>();
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("select * from Request");
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string stuID = da.GetValue(0).ToString();
string dateRequest = da.GetValue(1).ToString();
string requestContent = da.GetValue(2).ToString();
string maID = da.GetValue(3).ToString();
string dateReply = da.GetValue(4).ToString();
string reply = da.GetValue(5).ToString();
int requestID = int.Parse(da.GetValue(6).ToString());
Request hp = new Request(stuID, dateRequest, requestContent, maID, dateReply, reply, requestID);
list.Add(hp);
}
conn.Close();
return(list);
}
//them
public static HistoryBook GetHistoryBooksByBookID(int bookID)
{
List <HistoryBook> historyBooks = new List <HistoryBook>();
SqlConnection conn = DAO.getConnections();
string sql = "select * from HistoryBook where bookID = " + bookID;
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string stuID1 = da.GetValue(0).ToString();
DateTime dateBook = DateTime.Parse(da.GetValue(1).ToString());
string roomName = da.GetValue(2).ToString();
int bedNo = int.Parse(da.GetValue(3).ToString());
int bookID1 = int.Parse(da.GetValue(4).ToString());
int status = int.Parse(da.GetValue(5).ToString());
DateTime dateExpiry = DateTime.Parse(da.GetValue(6).ToString());
HistoryBook historyBook = new HistoryBook(stuID1, dateBook, roomName, bedNo, bookID1, status, dateExpiry);
return(historyBook);
}
conn.Close();
return(null);
}
public static Request getRequestByRequestID(int requestID)
{
List <Request> list = new List <Request>();
SqlConnection conn = DAO.getConnections();
string cmd1 = "select * from Request where requestID = " + requestID;
SqlCommand cmd = new SqlCommand(cmd1);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string stuID1 = da.GetValue(0).ToString();
string dateRequest = da.GetValue(1).ToString();
string requestContent = da.GetValue(2).ToString();
string maID = da.GetValue(3).ToString();
string dateReply = da.GetValue(4).ToString();
string reply = da.GetValue(5).ToString();
int requestID1 = int.Parse(da.GetValue(6).ToString());
Request hp = new Request(stuID1, dateRequest, requestContent, maID, dateReply, reply, requestID1);
return(hp);
}
conn.Close();
return(null);
}
public static List <Student> getStudents()
{
List <Student> list = new List <Student>();
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("select * from Student");
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string stuID = da.GetValue(0).ToString();
string username = da.GetValue(1).ToString();
string name = da.GetValue(2).ToString();
DateTime dob = DateTime.Parse(da.GetValue(3).ToString());
int gender = int.Parse(da.GetValue(4).ToString());
string roomName = da.GetValue(5).ToString();
int bedNo = int.Parse(da.GetValue(6).ToString());
DateTime bookingDate = DateTime.Parse(da.GetValue(7).ToString());
int moneyAccount = int.Parse(da.GetValue(8).ToString());
int debt = int.Parse(da.GetValue(9).ToString());
Student hp = new Student(stuID, username, name, dob, gender, roomName, bedNo, bookingDate, moneyAccount, debt);
list.Add(hp);
}
conn.Close();
return(list);
}
//them
public static List <HistoryBook> GetHistoryBooksByRoomNameStatus(string roomName, int bedNo)
{
List <HistoryBook> historyBooks = new List <HistoryBook>();
SqlConnection conn = DAO.getConnections();
string sql = "select * from HistoryBook where status=1 AND roomName = '" + roomName + "' AND bedNo = " + bedNo;
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string stuID1 = da.GetValue(0).ToString();
DateTime dateBook = DateTime.Parse(da.GetValue(1).ToString());
string roomName1 = da.GetValue(2).ToString();
int bedNo1 = int.Parse(da.GetValue(3).ToString());
int bookID = int.Parse(da.GetValue(4).ToString());
int status = int.Parse(da.GetValue(5).ToString());
DateTime dateExpiry = DateTime.Parse(da.GetValue(6).ToString());
HistoryBook historyBook = new HistoryBook(stuID1, dateBook, roomName1, bedNo1, bookID, status, dateExpiry);
historyBooks.Add(historyBook);
}
conn.Close();
return(historyBooks);
}
public static bool InsertBed(Room b) // Minh them
{
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("insert into Bed(roomName,bedNo,status) values(@roomName,@bedNo,@status)");
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@roomName", b.RoomName);
cmd.Parameters.AddWithValue("@bedNo", b.BedNo);
cmd.Parameters.AddWithValue("@status", b.BedStatus);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
public static bool Insert(Request b)
{
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("insert into Request(stuID,dateRequest, requestContent) values(@stuID,@dateRequest, @requestContent)");
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@stuID", b.StuID);
cmd.Parameters.AddWithValue("@dateRequest", b.DateRequest);
cmd.Parameters.AddWithValue("@requestContent", b.RequestContent);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
public static bool Insert(HistoryPayment b)
{
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("insert into HistoryPayment(stuId,datePay, type,moneyPay,roomName) values(@stuId, @datePay, @type, @moneyPay,@roomName)");
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@stuId", b.StuID);
cmd.Parameters.AddWithValue("@datePay", b.DatePay);
cmd.Parameters.AddWithValue("@type", b.Type);
cmd.Parameters.AddWithValue("@moneyPay", b.MoneyPay);
cmd.Parameters.AddWithValue("@roomName", b.RoomName);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
public static bool Insert(Room b)
{
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("insert into Room(roomName,gender,price) values(@roomName,@gender,@price);insert into Bed(roomName,bedNo,status) values(@roomName1,@bedNo,@status)");
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@roomName", b.RoomName);
cmd.Parameters.AddWithValue("@gender", b.Gender);
cmd.Parameters.AddWithValue("@price", b.Price);
cmd.Parameters.AddWithValue("@roomName1", b.RoomName);
cmd.Parameters.AddWithValue("@bedNo", b.BedNo);
cmd.Parameters.AddWithValue("@status", b.BedStatus);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
public static bool Insert(Manager b)
{
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("insert into Manager(maID,username, dob,gender,mail,phoneNumber,name) values(@maID,@username, @dob,@gender,@mail,@phoneNumber,@name)");
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@maID", b.MaID);
cmd.Parameters.AddWithValue("@username", b.Username);
cmd.Parameters.AddWithValue("@dob", b.Dob);
cmd.Parameters.AddWithValue("@gender", b.Gender);
cmd.Parameters.AddWithValue("@mail", b.Mail);
cmd.Parameters.AddWithValue("@phoneNumber", b.PhoneNumber);
cmd.Parameters.AddWithValue("@name", b.Name);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
public static int getRoomPrice(string room)
{
List <Room> list = new List <Room>();
SqlConnection conn = DAO.getConnections();
string cmd1 = "SELECT price FROM Room where roomName = '" + room + "'";
SqlCommand cmd = new SqlCommand(cmd1);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
int price = int.Parse(da.GetValue(0).ToString());
return(price);
}
conn.Close();
return(0);
}
public static bool Insert(Student b)
{
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("insert into Student(stuID,username,name, dob,gender,roomName,bedNo,bookingDate,moneyAccount,debt) values(@stuID,@username,@name, @dob,@gender,@roomName,@bedNo,@bookingDate,@moneyAccount,@debt)");
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@stuID", b.StuID);
cmd.Parameters.AddWithValue("@username", b.Username);
cmd.Parameters.AddWithValue("@name", b.Name);
cmd.Parameters.AddWithValue("@dob", b.Dob);
cmd.Parameters.AddWithValue("@gender", b.Gender);
cmd.Parameters.AddWithValue("@roomName", b.RoomName);
cmd.Parameters.AddWithValue("@bedNo", b.BedNo);
cmd.Parameters.AddWithValue("@bookingDate", b.BookingDate);
cmd.Parameters.AddWithValue("@moneyAccount", b.MoneyAccount);
cmd.Parameters.AddWithValue("@debt", b.Debt);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return(true);
}
public static Admin GetAccountByUsername(string username)
{
Admin admin;
SqlConnection conn = DAO.getConnections();
string sql = "select * from Admin where username = '******'";
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
int id = int.Parse(da.GetValue(0).ToString());
string username1 = da.GetValue(1).ToString();
admin = new Admin(id, username1);
return(admin);
}
conn.Close();
return(null);
}
public static List <Admin> GetAccounts()
{
List <Admin> admins = new List <Admin>();
SqlConnection conn = DAO.getConnections();
string sql = "select * from Admin";
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
int id = int.Parse(da.GetValue(0).ToString());
string username = da.GetValue(1).ToString();
Admin admin = new Admin(id, username);
admins.Add(admin);
}
conn.Close();
return(admins);
}
public static AvailibleBookingTime GetLatestDate()
{
AvailibleBookingTime abt = new AvailibleBookingTime();
SqlConnection conn = DAO.getConnections();
string sql = "select top 1 * from AvailibleBookingTime";
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
int adID = int.Parse(da.GetValue(0).ToString());
DateTime from = DateTime.Parse(da.GetValue(1).ToString());
DateTime to = DateTime.Parse(da.GetValue(2).ToString());
abt = new AvailibleBookingTime(adID, from, to);
}
conn.Close();
return(abt);
}
public static List <Room> getRoomBeds()
{
List <Room> list = new List <Room>();
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("select Room.roomName,Room.gender,Bed.bedNo,Bed.status from Room,Bed where Room.roomName=Bed.roomName");
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string roomName = da.GetValue(0).ToString();
int gender = int.Parse(da.GetValue(1).ToString());
int bedNo = int.Parse(da.GetValue(2).ToString());
int status = int.Parse(da.GetValue(3).ToString());
Room hp = new Room(roomName, gender, bedNo, status);
list.Add(hp);
}
conn.Close();
return(list);
}
//them
public static Room getRoomBedByRoomNameBedNo(string room, int bed)
{
List <Room> list = new List <Room>();
SqlConnection conn = DAO.getConnections();
string cmd1 = "select Room.roomName,Room.gender,Bed.bedNo,Bed.status from Room,Bed where Room.roomName=Bed.roomName AND Room.roomName = '" + room + "' AND Bed.bedNo = " + bed;
SqlCommand cmd = new SqlCommand(cmd1);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string roomName = da.GetValue(0).ToString();
int gender = int.Parse(da.GetValue(1).ToString());
int bedNo = int.Parse(da.GetValue(2).ToString());
int status = int.Parse(da.GetValue(3).ToString());
Room hp = new Room(roomName, gender, bedNo, status);
return(hp);
}
conn.Close();
return(null);
}
public static List <Room> GetRoomBedPricesByRoomName(string roomName) // Minh them
{
List <Room> list = new List <Room>();
SqlConnection conn = DAO.getConnections();
SqlCommand cmd = new SqlCommand("select Room.roomName,Room.gender,Bed.bedNo,Bed.status, Room.price from Room,Bed where Room.roomName=Bed.roomName and Room.roomName = '" + roomName + "'");
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string roomName1 = da.GetValue(0).ToString();
int gender = int.Parse(da.GetValue(1).ToString());
int bedNo = int.Parse(da.GetValue(2).ToString());
int status = int.Parse(da.GetValue(3).ToString());
int price = int.Parse(da.GetValue(4).ToString());
Room hp = new Room(roomName1, gender, bedNo, status, price);
list.Add(hp);
}
conn.Close();
return(list);
}
public static Account GetAccountByUsername(string username)
{
Account account = new Account();
SqlConnection conn = DAO.getConnections();
string sql = "select * from Account where username = '******'";
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string username1 = da.GetValue(0).ToString();
string password = da.GetValue(1).ToString();
int type = int.Parse(da.GetValue(2).ToString());
account = new Account(username1, password, type);
return(account);
}
conn.Close();
return(null);
}
public static List <Account> GetAccounts()
{
List <Account> accounts = new List <Account>();
SqlConnection conn = DAO.getConnections();
string sql = "select * from Account";
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
conn.Open();
SqlDataReader da = cmd.ExecuteReader();
while (da.Read())
{
string username = da.GetValue(0).ToString();
string password = da.GetValue(1).ToString();
int type = int.Parse(da.GetValue(2).ToString());
Account account = new Account(username, password, type);
accounts.Add(account);
}
conn.Close();
return(accounts);
}