// Get Average Rating of Dorm
public static int GetAvgRating(string emailOwner, int idDorm)
{
Mydatabase.sql = $"select avg({Helpers.colRatingDormComment}) from {Helpers.tbBookComment} where {Helpers.tbBookComment}.{Helpers.colEmailOwnerDormComment} = @Email and {Helpers.tbBookComment}.{Helpers.colIdDormDormComment} = @Id ;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", emailOwner);
Mydatabase.cmd.Parameters.AddWithValue("@Id", idDorm);
int a = Convert.ToInt32(Mydatabase.cmd.ExecuteScalar().ToString());
return(a);
}
catch (Exception e)
{
MessageBox.Show("Error getAVGRatig " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(-1);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Update Profile when click Save In Screen My Profile
public static Boolean UpdateProfileSave(UserProfile user)
{
Mydatabase.sql = $"update {Helpers.tbUserProfile} SET {Helpers.colNameProfile}=@Name , {Helpers.colPhoneProfile}=@Phone, {Helpers.colImageProfile}=@Image where {Helpers.colEmailProfile}=@Email;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Name", user.Name);
Mydatabase.cmd.Parameters.AddWithValue("@Email", user.Email);
Mydatabase.cmd.Parameters.AddWithValue("@Phone", user.Phone);
Mydatabase.cmd.Parameters.AddWithValue("@Image", user.Avatar);
Mydatabase.cmd.ExecuteScalar();
return(true);
}
catch (Exception e)
{
MessageBox.Show("Error Update User Profile " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Insert
public static bool Insert(string emailOwner, int idDorm, string emailUser, string comment, int rating)
{
Mydatabase.sql = $"insert into {Helpers.tbBookComment} values(@EmailOwner,@Id,@EmailUser,@Comment,@Rating) ;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@EmailOwner", emailOwner);
Mydatabase.cmd.Parameters.AddWithValue("@Id", idDorm);
Mydatabase.cmd.Parameters.AddWithValue("@EmailUser", emailUser);
Mydatabase.cmd.Parameters.AddWithValue("@Comment", comment);
Mydatabase.cmd.Parameters.AddWithValue("@Rating", rating);
Mydatabase.cmd.ExecuteScalar();
return(true);
}
catch (Exception e)
{
//MessageBox.Show("Error insertDormComment " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Get item
public static BookDorm GetItem(BookDorm bookDorm)
{
BookDorm dorm = new BookDorm();
Mydatabase.sql = $"select * from {Helpers.tbBookDorm} where {Helpers.colEmailOwnerBookDorm} = @EmailOwner and {Helpers.colEmailUserBookDorm}=@EmailUser and {Helpers.colIdDormBookDorm} = @Id";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@EmailOwner", bookDorm.EmailOwner);
Mydatabase.cmd.Parameters.AddWithValue("@EmailUser", bookDorm.EmailUser);
Mydatabase.cmd.Parameters.AddWithValue("@Id", bookDorm.IdDorm);
Mydatabase.rd = Mydatabase.cmd.ExecuteReader();
if (Mydatabase.rd.Read())
{
dorm.EmailOwner = Mydatabase.rd.GetString(0);
dorm.EmailUser = Mydatabase.rd.GetString(1);
dorm.IdDorm = Mydatabase.rd.GetInt32(2);
dorm.StateDorm = Mydatabase.rd.GetInt16(3);
}
}
catch (Exception e)
{
MessageBox.Show("Error get infor Owner Like Dorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.CloseConnection();
}
return(dorm);
}
//Check Account Remember In App
public static User CheckAccountAreadyInApp()
{
Mydatabase.sql = $"select * from {Helpers.tbUser} where {Helpers.colRemember}=@value;";
User user = null;
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@value", 1);
Mydatabase.rd = Mydatabase.cmd.ExecuteReader();
while (Mydatabase.rd.Read())
{
string email = Mydatabase.rd.GetValue(0).ToString();
string password = Mydatabase.rd.GetValue(1).ToString();
user = new User(email, password, 1);
}
}
catch (Exception e)
{
MessageBox.Show("Error loading remember" + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.rd.Close();
Mydatabase.CloseConnection();
}
return(user);
}
//Update Profile when click Update In Screen My Profile
public static Boolean UpdateProfileSubmit(UserProfile user)
{
Mydatabase.sql = $"update {Helpers.tbUserProfile} SET {Helpers.colAdressProfile}=@Address , {Helpers.colHintProfile}=@Hint, {Helpers.colDateProfile} = @Date" +
$", {Helpers.colGenderProfile} = @Gender where {Helpers.colEmailProfile}=@Email;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Address", user.Address);
Mydatabase.cmd.Parameters.AddWithValue("@Hint", user.Hint);
Mydatabase.cmd.Parameters.AddWithValue("@Date", user.Date);
Mydatabase.cmd.Parameters.AddWithValue("@Gender", user.Gender);
Mydatabase.cmd.Parameters.AddWithValue("@Email", user.Email);
Mydatabase.cmd.ExecuteScalar();
return(true);
}
catch (Exception e)
{
MessageBox.Show("Error Update User Profile " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Get Like Or DisLike
public static Boolean GetStateLike(string email, int id)
{
Mydatabase.sql = $"select {Helpers.colLikeOwnerLikeDorm} from {Helpers.tbOwnerLikeDorm} where {Helpers.colEmailOwnerLikeDorm}=@Email and {Helpers.colIdDormOwnerLikeDorm} = @Id;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@Id", id);
Mydatabase.rd = Mydatabase.cmd.ExecuteReader();
if (Mydatabase.rd.Read())
{
return(Mydatabase.rd.GetBoolean(0));
}
}
catch (Exception e)
{
MessageBox.Show("Error get State Owner Like Dorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.CloseConnection();
}
return(false);
}
// Check Account exists in Database
public static Boolean CheckAccount(string email, string password)
{
Mydatabase.sql = $"select count(*) from {Helpers.tbUser} where {Helpers.colEmail}=@Email and {Helpers.colPassword}=@password;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@Password", password);
int count = Convert.ToInt32(Mydatabase.cmd.ExecuteScalar().ToString());
if (count != 0)
{
return(true);
}
else
{
MessageBox.Show("Email or Password not correct", "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
}
catch (Exception e)
{
MessageBox.Show("Error connecting...", "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
finally
{
Mydatabase.CloseConnection();
}
}
// Update user
public static Boolean Update(User user, string email)
{
Mydatabase.sql = $"update {Helpers.tbUser} SET {Helpers.colRemember}=@IsRemember , {Helpers.colPassword}=@Password where {Helpers.colEmail}=@Email;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@IsRemember", user.isRemember);
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@Password", user.Password);
Mydatabase.cmd.ExecuteScalar();
return(true);
}
catch (Exception e)
{
MessageBox.Show("Error Change Remember " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Update
public static void Update(string email, int id, byte like)
{
Mydatabase.sql = $"update {Helpers.tbOwnerLikeDorm} SET {Helpers.colLikeOwnerLikeDorm}=@Like where {Helpers.colEmailOwnerLikeDorm}=@Email and {Helpers.colIdDormOwnerLikeDorm} = @Id;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Like", like);
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@Id", id);
Mydatabase.cmd.ExecuteScalar();
}
catch (Exception e)
{
MessageBox.Show("Error Update Owner Like Dorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Delete item by id user
public static bool DeleteDormWhenBookUser(string emailUser, int id)
{
Mydatabase.sql = $"delete from {Helpers.tbBookDorm} where {Helpers.colEmailUserBookDorm} = @EmailUser and {Helpers.colIdDormBookDorm} = @Id";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@State", 0);
Mydatabase.cmd.Parameters.AddWithValue("@EmailUser", emailUser);
Mydatabase.cmd.Parameters.AddWithValue("@Id", id);
Mydatabase.cmd.ExecuteScalar();
return(true);
}
catch (Exception e)
{
MessageBox.Show("Error DeleteDormWhenBookUser " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Update item
public static void Update(BookDorm bookDorm)
{
Mydatabase.sql = $"update {Helpers.tbBookDorm} SET {Helpers.colEmailOwnerBookDorm}=@EmailOwner" +
$", {Helpers.colEmailUserBookDorm} = @EmailUser" +
$", {Helpers.colIdDormBookDorm}=@Id" +
$", {Helpers.colStateBookDorm}=@State" +
$" where {Helpers.colEmailOwnerBookDorm}=@EmailOwner and {Helpers.colEmailUserBookDorm} = @EmailUser and {Helpers.colIdDormBookDorm}=@Id;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@EmailOwner", bookDorm.EmailOwner);
Mydatabase.cmd.Parameters.AddWithValue("@EmailUser", bookDorm.EmailUser);
Mydatabase.cmd.Parameters.AddWithValue("@Id", bookDorm.IdDorm);
Mydatabase.cmd.Parameters.AddWithValue("@State", bookDorm.StateDorm);
Mydatabase.cmd.ExecuteScalar();
}
catch (Exception e)
{
MessageBox.Show("Error Update Book Dorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Get User Book Dorm By Id dorm
public static User GetUserDorm(string email, int id)
{
User user = new User("", "", 0);
Mydatabase.sql = $"select {Helpers.colEmailUserBookDorm} from {Helpers.tbBookDorm} where {Helpers.colEmailOwnerBookDorm} = @Email and {Helpers.colIdDormBookDorm} = @Id";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@Id", id);
Mydatabase.rd = Mydatabase.cmd.ExecuteReader();
if (Mydatabase.rd.Read())
{
user.Email = Mydatabase.rd.GetString(0);
return(user);
}
}
catch (Exception e)
{
MessageBox.Show("Error GetUserByIdDorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(null);
}
finally
{
Mydatabase.rd.Close();
Mydatabase.CloseConnection();
}
return(null);
}
//Insert item
public static Boolean Insert(BookDorm bookDorm)
{
Mydatabase.sql = $"insert into {Helpers.tbBookDorm}({Helpers.colEmailOwnerBookDorm},{Helpers.colEmailUserBookDorm},{Helpers.colIdDormBookDorm},{Helpers.colStateBookDorm}) values(@EmailOwner,@EmailUser,@Id,@State);";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@EmailOwner", bookDorm.EmailOwner);
Mydatabase.cmd.Parameters.AddWithValue("@EmailUser", bookDorm.EmailUser);
Mydatabase.cmd.Parameters.AddWithValue("@Id", bookDorm.IdDorm);
Mydatabase.cmd.Parameters.AddWithValue("@State", bookDorm.StateDorm);
Mydatabase.cmd.ExecuteScalar();
return(true);
}
catch (Exception e)
{
//MessageBox.Show("Error Insert Book Dorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Update State Dorm
public static bool Update(string email, int id, int state)
{
Mydatabase.sql = $"update {Helpers.tbBookDorm} set {Helpers.colStateBookDorm} = @State where {Helpers.colEmailOwnerBookDorm} = @Email and {Helpers.colIdDormBookDorm} = @Id";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@State", state);
Mydatabase.cmd.Parameters.AddWithValue("@Id", id);
Mydatabase.cmd.ExecuteScalar();
return(true);
}
catch (Exception e)
{
MessageBox.Show("Error Reset Dorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
return(false);
}
finally
{
Mydatabase.CloseConnection();
}
}
// Insert User to Database
public static Boolean Insert(string email, string password, byte isActive)
{
Mydatabase.sql = $"insert into {Helpers.tbUser} values(@Email,@Password,@IsRemember);";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@Password", password);
Mydatabase.cmd.Parameters.AddWithValue("@IsRemember", isActive);
Mydatabase.cmd.ExecuteScalar();
return(true);
}
catch (Exception e)
{
return(false);
}
finally
{
Mydatabase.CloseConnection();
}
}
// Get Information User
public static UserProfile GetProfile(User user)
{
Mydatabase.sql = $"select * from {Helpers.tbUserProfile} where {Helpers.colEmailProfile}=@Email";
UserProfile userProfile = null;
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", user.Email.Trim());
Mydatabase.rd = Mydatabase.cmd.ExecuteReader();
if (Mydatabase.rd.Read())
{
string email = Mydatabase.rd.GetValue(0).ToString();
string name = Mydatabase.rd.GetValue(1).ToString();
string date = Mydatabase.rd.GetValue(2).ToString();
string phone = Mydatabase.rd.GetValue(3).ToString();
string address = Mydatabase.rd.GetValue(4).ToString();
string hint = Mydatabase.rd.GetValue(5).ToString();
byte gender = byte.Parse(Mydatabase.rd.GetValue(6).ToString());
byte[] image;
if (Mydatabase.rd.GetValue(7).ToString().Equals(""))
{
userProfile = new UserProfile(email, name, date, phone, address, hint, gender, null);
}
else
{
image = (byte[])Mydatabase.rd.GetValue(7);
userProfile = new UserProfile(email, name, date, phone, address, hint, gender, image);
}
}
}
catch (Exception e)
{
MessageBox.Show("Error loading Profile" + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.rd.Close();
Mydatabase.CloseConnection();
}
return(userProfile);
}
//Get Owner of Dorm
public static UserProfile GetOwnerProfileOfDorm(int idDorm)
{
UserProfile owner = new UserProfile();
Mydatabase.sql = $"select {Helpers.colImageProfile},{Helpers.colNameProfile},{Helpers.colPhoneProfile},{Helpers.colEmailProfile}," +
$"{Helpers.colAdressProfile}, {Helpers.colGenderProfile} " +
$"from {Helpers.tbUserProfile},{Helpers.tbDormOwner} " +
$"where {Helpers.colIdOwnerDorm} = @Id and {Helpers.colEmailOwnerDorm} = {Helpers.colEmailProfile};";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Id", idDorm);
Mydatabase.rd = Mydatabase.cmd.ExecuteReader();
while (Mydatabase.rd.Read())
{
string email = Mydatabase.rd.GetValue(3).ToString();
string name = Mydatabase.rd.GetValue(1).ToString();
string phone = Mydatabase.rd.GetValue(2).ToString();
string address = Mydatabase.rd.GetValue(4).ToString();
byte gender = byte.Parse(Mydatabase.rd.GetValue(5).ToString());
byte[] image;
try
{
image = (byte[])Mydatabase.rd.GetValue(0);
}
catch (Exception e)
{
image = Helpers.ConvertImageToBinary(new BitmapImage(new Uri("../../images/blank_account.png", UriKind.RelativeOrAbsolute)));
}
owner = new UserProfile(email, name, "", phone, address, "", gender, image);
}
}
catch (Exception e)
{
MessageBox.Show("Error GetOwnerProfileWithDorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.rd.Close();
Mydatabase.CloseConnection();
}
return(owner);
}
//Get All Commnet of Dorm
public static List <BookComment> GetAllCommentBookDorm(string email, int id)
{
List <BookComment> list = new List <BookComment>();
Mydatabase.sql = $"select {Helpers.tbUserProfile}.{Helpers.colEmailProfile},{Helpers.tbUserProfile}.{Helpers.colImageProfile},{Helpers.tbBookComment}.{Helpers.colCommentDormComment},{Helpers.tbBookComment}.{Helpers.colRatingDormComment} " +
$"from {Helpers.tbUserProfile},{Helpers.tbBookComment} where {Helpers.tbUserProfile}.{Helpers.colEmailProfile} = {Helpers.tbBookComment}.{Helpers.colEmailUserDormComment} " +
$"and {Helpers.tbBookComment}.{Helpers.colEmailOwnerDormComment} = @Email and {Helpers.tbBookComment}.{Helpers.colIdDormDormComment} = @Id;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@Id", id);
Mydatabase.rd = Mydatabase.cmd.ExecuteReader();
while (Mydatabase.rd.Read())
{
BookComment bookDorm = new BookComment();
bookDorm.Owner = email;
bookDorm.Id = id;
bookDorm.User = Mydatabase.rd.GetString(0);
try
{
bookDorm.Image = Helpers.ConvertByteToImageBitmap((byte[])Mydatabase.rd.GetValue(1));
}
catch (Exception exception)
{
bookDorm.Image = new System.Windows.Media.Imaging.BitmapImage(new Uri("../../images/blank_account.png", UriKind.RelativeOrAbsolute));
}
bookDorm.Comment = Mydatabase.rd.GetString(2);
bookDorm.Rating = Mydatabase.rd.GetInt32(3);
list.Add(bookDorm);
}
}
catch (Exception e)
{
MessageBox.Show("Error GetAllCommentBookDorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.CloseConnection();
}
return(list);
}
//Get All User booking waiting
public static List <BookDorm> GetAllWattingBook(string email, bool IsOwner)
{
List <BookDorm> list = new List <BookDorm>();
if (IsOwner)
{
Mydatabase.sql = $"select * from {Helpers.tbBookDorm} where {Helpers.colEmailOwnerBookDorm} = @Email and {Helpers.colStateBookDorm} = @State";
}
else
{
Mydatabase.sql = $"select * from {Helpers.tbBookDorm} where {Helpers.colEmailUserBookDorm} = @Email and {Helpers.colStateBookDorm} = @State";
}
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@State", 1);
Mydatabase.rd = Mydatabase.cmd.ExecuteReader();
while (Mydatabase.rd.Read())
{
BookDorm dorm = new BookDorm();
dorm.EmailOwner = Mydatabase.rd.GetString(0);
dorm.EmailUser = Mydatabase.rd.GetString(1);
dorm.IdDorm = Mydatabase.rd.GetInt32(2);
dorm.StateDorm = Mydatabase.rd.GetInt16(3);
list.Add(dorm);
}
}
catch (Exception e)
{
MessageBox.Show("Error get All List Owner Book Dorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.CloseConnection();
}
return(list);
}
// Insert email to Table Profile (Not use FaceBook)
public static void Insert(string email)
{
Mydatabase.sql = $"insert into {Helpers.tbUserProfile}({Helpers.colEmailProfile}) values(@Email);";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.ExecuteScalar();
}
catch (Exception e)
{
MessageBox.Show("Information Error", "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Insert Dorm By Email and Id Dorm
public static void Insert(string email, int idDorm)
{
Mydatabase.sql = $"insert into {Helpers.tbDormOwner}({Helpers.colEmailOwnerDorm},{Helpers.colIdOwnerDorm}) values(@Email,@Id);";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@Id", idDorm);
Mydatabase.cmd.ExecuteScalar();
}
catch (Exception e)
{
MessageBox.Show("Error Insert to table owner dorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Delete By Id
public static void DeleteByIdDorm(int idDorm)
{
Mydatabase.sql = $"delete from {Helpers.tbBookComment} where {Helpers.tbBookComment} = @Id ;";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Id", idDorm);
Mydatabase.cmd.ExecuteScalar();
}
catch (Exception e)
{
//MessageBox.Show("Error insertDormComment " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Delete by Id Dorm
public static void DeleteByDormId(int id)
{
Mydatabase.sql = $"delete from {Helpers.tbDormOwner} where {Helpers.colIdOwnerDorm} = @Id";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Id", id);
Mydatabase.cmd.ExecuteScalar();
}
catch (Exception e)
{
MessageBox.Show("Delete tbOwnerDorm Error " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Insert User To Table Like Dorm
public static Boolean Insert(string email, int idDorm, byte like)
{
Mydatabase.sql = $"insert into {Helpers.tbOwnerLikeDorm}({Helpers.colEmailOwnerLikeDorm},{Helpers.colIdDormOwnerLikeDorm},{Helpers.colLikeOwnerLikeDorm}) values(@Email,@Id,@Like);";
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.cmd.Parameters.AddWithValue("@Id", idDorm);
Mydatabase.cmd.Parameters.AddWithValue("@Like", like);
Mydatabase.cmd.ExecuteScalar();
return(true);
}
catch (Exception e)
{
return(false);
}
finally
{
Mydatabase.CloseConnection();
}
}
//Get All Dorm By Email Owner
public static List <Dorm> GetAllListDormOwner(string email)
{
Mydatabase.sql = $"select * from {Helpers.tbDorm},{Helpers.tbDormOwner} where {Helpers.colIdDorm} = {Helpers.colIdOwnerDorm} and {Helpers.colEmailOwnerDorm} = @Email";
List <Dorm> listDorm = new List <Dorm>();
try
{
Mydatabase.OpenConnection();
Mydatabase.cmd.CommandType = CommandType.Text;
Mydatabase.cmd.CommandText = Mydatabase.sql;
Mydatabase.cmd.Parameters.Clear();
Mydatabase.cmd.Parameters.AddWithValue("@Email", email);
Mydatabase.rd = Mydatabase.cmd.ExecuteReader();
while (Mydatabase.rd.Read())
{
Dorm dorm = new Dorm();
int id = Mydatabase.rd.GetInt32(0);
string owner = Mydatabase.rd.GetString(1);
string address = Mydatabase.rd.GetString(2);
string description = Mydatabase.rd.GetString(3);
double price = Mydatabase.rd.GetDouble(4);
double sale = Mydatabase.rd.GetDouble(5);
byte[] image;
if (Mydatabase.rd.GetValue(6).ToString().Equals(""))
{
dorm.Image = new System.Windows.Media.Imaging.BitmapImage(new Uri($"../../images/icon_app.ico", UriKind.RelativeOrAbsolute));
}
else
{
image = (byte[])Mydatabase.rd.GetValue(6);
dorm.Image = Helpers.ConvertByteToImageBitmap(image);
}
int count = Mydatabase.rd.GetInt32(7);
int countLike = Mydatabase.rd.GetInt32(8);
byte wifi = Helpers.ConverBoolToByte(Mydatabase.rd.GetBoolean(9));
byte parking = Helpers.ConverBoolToByte(Mydatabase.rd.GetBoolean(10));
byte television = Helpers.ConverBoolToByte(Mydatabase.rd.GetBoolean(11));
byte bathroom = Helpers.ConverBoolToByte(Mydatabase.rd.GetBoolean(12));
byte aircon = Helpers.ConverBoolToByte(Mydatabase.rd.GetBoolean(13));
byte waterheater = Helpers.ConverBoolToByte(Mydatabase.rd.GetBoolean(14));
int quality = Mydatabase.rd.GetInt16(15);
double size = Mydatabase.rd.GetDouble(16);
dorm.Id = id;
dorm.Owner = owner;
dorm.Address = address;
dorm.Description = description;
dorm.Price = price;
dorm.Sale = sale;
dorm.Count = count;
dorm.CountLike = countLike;
dorm.IsWifi = Helpers.ConvertByteToVisibility(wifi);
dorm.IsParking = Helpers.ConvertByteToVisibility(parking);
dorm.IsTelevision = Helpers.ConvertByteToVisibility(television);
dorm.IsBathroom = Helpers.ConvertByteToVisibility(bathroom);
dorm.IsAirCondiditioner = Helpers.ConvertByteToVisibility(aircon);
dorm.IsWaterHeater = Helpers.ConvertByteToVisibility(waterheater);
dorm.Quality = quality;
dorm.Size = size;
listDorm.Add(dorm);
}
}
catch (Exception e)
{
MessageBox.Show("Error loading List Dorm " + e.Message, "ERROR", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
Mydatabase.rd.Close();
Mydatabase.CloseConnection();
}
return(listDorm);
}