public List <AdminInfo> loginAdmin(string username, string password)
{
// kode get data from sql server..
Koneksi kon = new Koneksi();
SqlConnection sqlcon = kon.getConnection();
List <AdminInfo> objList = new List <AdminInfo>();
using (sqlcon)
{
sqlcon.Open();
string sql = "select * from tb_admin where username = @username and password = @password ";
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
using (sqlcom)
{
sqlcom.Parameters.AddWithValue("@email", username);
sqlcom.Parameters.AddWithValue("@password", password);
SqlDataReader dr = sqlcom.ExecuteReader();
if (dr.Read())
{
AdminInfo obj = new AdminInfo();
obj.IDAdmin = dr.GetString(1);
obj.NameAdmin = dr.GetString(2);
obj.UsernameAdmin = dr.GetString(3);
obj.AddressAdmin = dr.GetString(5);
obj.PhoneNumberAdmin = dr.GetString(6);
obj.TypeAdmin = dr.GetString(7);
objList.Add(obj);
}
}
sqlcon.Close();
}
return(objList);
}
public string updateUser(UserInfo data)
{
Koneksi kon = new Koneksi();
SqlConnection sqlcon = kon.getConnection();
string msg = string.Empty;
using (sqlcon)
{
sqlcon.Open();
string sql = "update tb_customer set name = @nama, alamat = @alamat, card_type = @cardtype, card_number = @cardnumber where id_customer = @id";
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
using (sqlcom)
{
sqlcom.Parameters.AddWithValue("@id", data.IDUser);
sqlcom.Parameters.AddWithValue("@nama", data.NameUser);
sqlcom.Parameters.AddWithValue("@alamat", data.AddressUser);
sqlcom.Parameters.AddWithValue("@cardtype", data.Card_typeUser);
sqlcom.Parameters.AddWithValue("@cardnumber", data.Card_numberUser);
int res = sqlcom.ExecuteNonQuery();
msg = (res != 0 ? "Data has been updated." : "Oops, something went wrong.");
}
sqlcon.Close();
}
return(msg);
}
public List <ReservationInfo> getReservationFromID(string id)
{
// kode get data from sql server..
Koneksi kon = new Koneksi();
SqlConnection sqlcon = kon.getConnection();
List <ReservationInfo> objList = new List <ReservationInfo>();
using (sqlcon)
{
sqlcon.Open();
string sql = "select a.id_reservation,b.name,c.name as customer,a.date_time,a.status,a.info from tb_reservation a inner join tb_room_type b on b.id_room_type = a.id_room inner join tb_customer c on c.id_customer = a.id_customer where a.id_customer = @id";
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
using (sqlcom)
{
sqlcom.Parameters.AddWithValue("@id", id);
SqlDataReader dr = sqlcom.ExecuteReader();
while (dr.Read())
{
ReservationInfo obj = new ReservationInfo();
obj.IDReservation = dr.GetString(0);
obj.TipeRoomTypeReservation = dr.GetString(1);
obj.NameCustomerReservation = dr.GetString(2);
obj.DateTimeReservation = (string)dr.GetDateTime(3).ToString();
obj.StatusReservation = dr.GetString(4);
obj.InfoReservation = dr.GetString(5);
objList.Add(obj);
}
}
sqlcon.Close();
}
return(objList);
}
public string insertUser(UserInfo data)
{
Koneksi kon = new Koneksi();
SqlConnection sqlcon = kon.getConnection();
string msg = string.Empty;
using (sqlcon)
{
sqlcon.Open();
string sql = "insert tb_customer (name,birthdate,address,phonenumber,card_type,card_number,email,password) values(@nama, @birthdate, @alamat, @nohp, @cardtype, @cardnumber, @email, @password)";
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
using (sqlcom)
{
sqlcom.Parameters.AddWithValue("@nama", data.NameUser);
sqlcom.Parameters.AddWithValue("@birthdate", data.BirthdateUser);
sqlcom.Parameters.AddWithValue("@alamat", data.AddressUser);
sqlcom.Parameters.AddWithValue("@nohp", data.PhoneNumberUser);
sqlcom.Parameters.AddWithValue("@cardtype", data.Card_typeUser);
sqlcom.Parameters.AddWithValue("@cardnumber", data.Card_numberUser);
sqlcom.Parameters.AddWithValue("@email", data.EmailUser);
sqlcom.Parameters.AddWithValue("@password", data.PasswordUser);
int res = sqlcom.ExecuteNonQuery();
msg = (res != 0 ? "Data has been saved." : "Oops, something went wrong.");
}
sqlcon.Close();
}
return(msg);
}
public List <MonthPaidInfo> getMonthPaidByIDUser(string id)
{
// kode get data from sql server..
Koneksi kon = new Koneksi();
SqlConnection sqlcon = kon.getConnection();
List <MonthPaidInfo> objList = new List <MonthPaidInfo>();
using (sqlcon)
{
sqlcon.Open();
string sql = "select c.id_transaction,b.name as kamar,a.date_time,a.total,a.info from tb_monthpaid a inner join tb_transaction c on c.id_transaction = a.id_transaction inner join tb_customer d on d.id_customer = c.id_customer inner join tb_room b on c.id_room = b.id_room where d.id_customer = @id order by date_time DESC";
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
using (sqlcom)
{
sqlcom.Parameters.AddWithValue("@id", id);
SqlDataReader dr = sqlcom.ExecuteReader();
while (dr.Read())
{
MonthPaidInfo obj = new MonthPaidInfo();
obj.IDTransaction = dr.GetString(0);
obj.KamarMonthPaid = dr.GetString(1);
//Convert Date Time to String.
DateTime dt = Convert.ToDateTime(dr.GetDateTime(2));
obj.TglMonthPaid = dt.ToString("dd-MM-yyyy");
obj.TotalMonthPaid = (int)dr.GetInt32(3);
obj.InfoMonthPaid = dr.GetString(4);
objList.Add(obj);
}
}
sqlcon.Close();
}
return(objList);
}
public List <RoomTypeInfo> getReservation(string id)
{
// kode get data from sql server..
Koneksi kon = new Koneksi();
SqlConnection sqlcon = kon.getConnection();
List <RoomTypeInfo> objList = new List <RoomTypeInfo>();
using (sqlcon)
{
sqlcon.Open();
string sql = "select * from tb_room_type where id_room_type = @id";
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
using (sqlcom)
{
sqlcom.Parameters.AddWithValue("@id", id);
SqlDataReader dr = sqlcom.ExecuteReader();
while (dr.Read())
{
RoomTypeInfo obj = new RoomTypeInfo();
obj.IDRoomType = dr.GetString(1);
obj.TipeRoomType = dr.GetString(2);
obj.PriceRoomType = (int)dr.GetInt64(3);
obj.InfoRoomType = dr.GetString(4);
objList.Add(obj);
}
}
sqlcon.Close();
}
return(objList);
}
public List <RoomTypeInfo> getTotalRoomType()
{
// kode get data from sql server..
Koneksi kon = new Koneksi();
SqlConnection sqlcon = kon.getConnection();
List <RoomTypeInfo> objList = new List <RoomTypeInfo>();
using (sqlcon)
{
sqlcon.Open();
string sql = "select b.name,COUNT(b.name) as jumlah from tb_room a inner join tb_room_type b on a.id_room_type = b.id_room_type where a.status = 'Available' group by b.name";
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
using (sqlcom)
{
SqlDataReader dr = sqlcom.ExecuteReader();
while (dr.Read())
{
RoomTypeInfo obj = new RoomTypeInfo();
obj.TipeRoomType = dr.GetString(0);
obj.TotalRoomType = (int)dr.GetInt32(1);
objList.Add(obj);
}
}
sqlcon.Close();
}
return(objList);
}
public List <UserInfo> loginUser(string email, string password)
{
// kode get data from sql server..
Koneksi kon = new Koneksi();
SqlConnection sqlcon = kon.getConnection();
List <UserInfo> objList = new List <UserInfo>();
using (sqlcon)
{
sqlcon.Open();
string sql = "select * from tb_customer where email = @email and password = @password ";
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
using (sqlcom)
{
sqlcom.Parameters.AddWithValue("@email", email);
sqlcom.Parameters.AddWithValue("@password", password);
SqlDataReader dr = sqlcom.ExecuteReader();
if (dr.Read())
{
UserInfo obj = new UserInfo();
obj.IDUser = dr.GetString(1);
obj.NameUser = dr.GetString(2);
//Convert Date Time to String.
DateTime dt = Convert.ToDateTime(dr.GetDateTime(3));
obj.BirthdateUser = dt.ToString("dd-MM-yyyy");
obj.AddressUser = dr.GetString(4);
obj.PhoneNumberUser = dr.GetString(5);
obj.Card_typeUser = dr.GetString(6);
obj.Card_numberUser = dr.GetString(7);
obj.EmailUser = dr.GetString(8);
obj.PasswordUser = dr.GetString(9);
objList.Add(obj);
}
}
sqlcon.Close();
}
return(objList);
}
public string insertData(ReservationInfo data)
{
Koneksi kon = new Koneksi();
SqlConnection sqlcon = kon.getConnection();
string msg = string.Empty;
using (sqlcon)
{
sqlcon.Open();
string sql = "insert into tb_reservation (id_room,id_customer,info) values (@idroom,@idcustomer,@info);";
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
using (sqlcom)
{
sqlcom.Parameters.AddWithValue("@idroom", data.IDRoomReservation);
sqlcom.Parameters.AddWithValue("@idcustomer", data.IDCustomerReservation);
sqlcom.Parameters.AddWithValue("@info", data.InfoReservation);
int res = sqlcom.ExecuteNonQuery();
msg = (res != 0 ? "Data has been saved." : "Oops, something went wrong.");
}
sqlcon.Close();
}
return(msg);
}
public List <UserInfo> getUserFromName(string nama)
{
// kode get data from sql server..
Koneksi kon = new Koneksi();
SqlConnection sqlcon = kon.getConnection();
List <UserInfo> objList = new List <UserInfo>();
using (sqlcon)
{
sqlcon.Open();
string sql = "select * from tb_customer where name like @nama ";
SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
using (sqlcom)
{
sqlcom.Parameters.AddWithValue("@nama", '%' + nama + '%');
SqlDataReader dr = sqlcom.ExecuteReader();
while (dr.Read())
{
UserInfo obj = new UserInfo();
obj.IDUser = dr.GetString(1);
obj.NameUser = dr.GetString(2);
//Convert Date Time to String.
DateTime dt = Convert.ToDateTime(dr.GetDateTime(3));
obj.BirthdateUser = dt.ToString("dd-MM-yyyy");
obj.AddressUser = dr.GetString(4);
obj.PhoneNumberUser = dr.GetString(5);
obj.Card_typeUser = dr.GetString(6);
obj.Card_numberUser = dr.GetString(7);
objList.Add(obj);
}
}
sqlcon.Close();
}
return(objList);
}