public void Delete(params int[] idsToDelete)
{
if (idsToDelete.Count() == 0)
{
return;
}
StringBuilder sqlCommandBuilder = new StringBuilder("DELETE FROM user_details WHERE id IN (");
for (int i = 0; i < idsToDelete.Count(); i++)
{
sqlCommandBuilder.Append(idsToDelete[i]);
if (i != idsToDelete.Count() - 1)
{
sqlCommandBuilder.Append(", ");
}
else
{
sqlCommandBuilder.Append(")");
}
}
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
}
}
public List <BlackList> GetBlackLists(int?userId = null, bool?banned = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM public.black_list");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (userId != null && banned != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE user_id='{0}'", userId));
}
else if (userId != null)
{
sqlCommandBuilder.Append(" WHERE user_id=" + userId);
}
else if (banned != null)
{
sqlCommandBuilder.Append(" WHERE banned=" + (banned.Value ? "1" : "0"));
}
List <BlackList> userDetailsList = new List <BlackList>();
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
BlackList blackListToAdd = null;
try
{
while (reader.Read())
{
blackListToAdd = new BlackList()
{
Id = Convert.ToInt32(reader["id"].ToString()),
Warnings = Convert.ToInt32(reader["warnings"].ToString()),
Banned = reader["banned"].ToString().Equals("1"),
UserId = Convert.ToInt32(reader["user_id"].ToString()),
};
userDetailsList.Add(blackListToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(userDetailsList);
}
public void Update(BlackList blackList)
{
string sqlCommand = String.Format("UPDATE public.black_list SET warnings='{0}', banned=b'{1}', user_id='{2}' WHERE id='{3}'",
blackList.Warnings, blackList.Banned ? "1" : "0", blackList.UserId, blackList.Id);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public void Update(User user)
{
string sqlCommand = String.Format("UPDATE public.user SET username='******', password='******', name='{2}', surname='{3}', user_details_id='{4}'," +
"function='{5}' WHERE id='{6}'", user.Username, user.Password, user.Name, user.Surname,
user.UserDetailsId, user.UserFunction, user.Id);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public List <UserDetails> GetUserDetails(int?userId = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM user_details");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (userId != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE user_id='{0}'", userId));
}
List <UserDetails> damageList = new List <UserDetails>();
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
UserDetails userdetailsToAdd = null;
try
{
while (reader.Read())
{
userdetailsToAdd = new UserDetails()
{
Id = Convert.ToInt32(reader["id"].ToString()),
Email = reader["email"].ToString(),
Street = reader["street"].ToString(),
City = reader["city"].ToString(),
ZipCode = reader["zipcode"].ToString(),
Country = reader["country"].ToString(),
Premium = reader["premium"].ToString().Equals("1"),
UserId = Convert.ToInt32(reader["user_id"].ToString()),
BirthDate = Convert.ToDateTime(reader["birth_date"].ToString())
};
damageList.Add(userdetailsToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(damageList);
}
public void Update(UserDetails userDetails)
{
string sqlCommand = String.Format("UPDATE user_details SET email='{0}', street='{1}', city='{2}', zipcode='{3}', country='{4}'," +
"premium=b'{5}', user_id='{6}', birth_date='{7}' WHERE id='{8}'",
userDetails.Email, userDetails.Street, userDetails.City, userDetails.ZipCode, userDetails.Country,
userDetails.Premium ? "1" : "0", userDetails.UserId, userDetails.BirthDate.ToShortDateString(), userDetails.Id);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public void Update(BankAccount bankAccount)
{
string sqlCommand = String.Format("UPDATE public.bank_account SET security_number='{0}', card_type='{1}', bank_name='{2}'," +
" user_id='{3}', iban='{4}', expiry_date='{5}' WHERE id='{6}'",
bankAccount.SecurityNumber, bankAccount.CardType, bankAccount.BankName, bankAccount.UserId,
bankAccount.Iban, bankAccount.ExpiryDate.ToShortDateString(), bankAccount.Id);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DBUtils.ExecuteCommand(sqlCommand, connection);
}
}
public List <BankAccount> GetBankAccounts(int?userId = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM public.bank_account");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (userId != null)
{
sqlCommandBuilder.Append(" WHERE user_id=" + userId);
}
List <BankAccount> bankAccountList = new List <BankAccount>();
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
BankAccount bankAccountToAdd = null;
try
{
while (reader.Read())
{
bankAccountToAdd = new BankAccount()
{
Id = Convert.ToInt32(reader["id"].ToString()),
SecurityNumber = Convert.ToInt32(reader["security_number"].ToString()),
CardType = (BankAccount.CardTypes)Enum.Parse(typeof(BankAccount.CardTypes), reader["card_type"].ToString()),
BankName = reader["bank_name"].ToString(),
Iban = reader["iban"].ToString(),
ExpiryDate = Convert.ToDateTime(reader["expiry_date"].ToString()),
UserId = Convert.ToInt32(reader["user_id"].ToString()),
};
bankAccountList.Add(bankAccountToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(bankAccountList);
}
public void Insert(User user)
{
string sqlCommand = String.Format("INSERT INTO public.user (username, password, name, surname, user_details_id, function)" +
"VALUES ('{0}','{1}','{2}','{3}','{4}','{5}') RETURNING id",
user.Username, user.Password, user.Name, user.Surname, user.UserDetailsId, user.UserFunction);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
user.Id = id;
}
}
}
public void Insert(BlackList blackList)
{
string sqlCommand = String.Format("INSERT INTO public.black_list (warnings, banned, user_id)" +
"VALUES ('{0}',b'{1}','{2}') RETURNING id",
blackList.Warnings, blackList.Banned ? "1" : "0", blackList.UserId);
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
blackList.Id = id;
}
}
}
public void Insert(UserDetails userDetails)
{
string sqlCommand = String.Format("INSERT INTO user_details (email, street, city, zipcode, country, premium, user_id, birth_date)" +
"VALUES ('{0}','{1}','{2}','{3}','{4}',b'{5}','{6}','{7}') RETURNING id",
userDetails.Email, userDetails.Street, userDetails.City, userDetails.ZipCode, userDetails.Country,
userDetails.Premium ? "1" : "0", userDetails.UserId, userDetails.BirthDate.ToShortDateString());
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
userDetails.Id = id;
}
}
}
public void Insert(BankAccount bankAccount)
{
string sqlCommand = String.Format("INSERT INTO public.bank_account (security_number, card_type, bank_name, user_id, iban, expiry_date)" +
"VALUES ('{0}','{1}','{2}','{3}','{4}','{5}') RETURNING id",
bankAccount.SecurityNumber, bankAccount.CardType, bankAccount.BankName, bankAccount.UserId,
bankAccount.Iban, bankAccount.ExpiryDate.ToShortDateString());
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommand, connection);
if (reader.Read())
{
int id = -1;
int.TryParse(reader.GetValue(0).ToString(), out id);
bankAccount.Id = id;
}
}
}
/// <summary>
///
/// </summary>
/// <param name="username"></param>
/// <param name="function"></param>
/// <param name="id"></param>
/// <returns>empty list if nothing in database</returns>
public List <User> GetUsers(string username = null, string function = null, int?id = null)
{
StringBuilder sqlCommandBuilder = new StringBuilder("SELECT * FROM public.user");
if (id != null)
{
sqlCommandBuilder.Append(" WHERE id=" + id);
}
else if (function != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE function = '{0}'", function));
}
else if (username != null)
{
sqlCommandBuilder.Append(String.Format(" WHERE username = '******'", username));
}
List <User> userList = new List <User>();
using (DbConnection connection = DBUtils.GetPostgreSQLDBConnection())
{
DbDataReader reader = DBUtils.ExecuteCommand(sqlCommandBuilder.ToString(), connection);
User userToAdd = null;
try
{
while (reader.Read())
{
userToAdd = new User()
{
Id = Convert.ToInt32(reader["id"].ToString()),
Username = reader["username"].ToString(),
Password = reader["password"].ToString(),
Name = reader["name"].ToString(),
Surname = reader["surname"].ToString(),
UserFunction = (User.Function)Enum.Parse(typeof(User.Function), reader["function"].ToString()),
UserDetailsId = Convert.ToInt32(reader["user_details_id"].ToString()),
};
try
{
userToAdd.UserDetails = new UserDetailsDAO().GetUserDetails(userId: userToAdd.Id).First();
}
catch (Exception)
{
DebugLog.WriteLine("Exception didnt find userdetails for user with id " + userToAdd.Id);
}
userList.Add(userToAdd);
}
}
catch (FormatException ex)
{
DebugLog.WriteLine(ex);
}
finally
{
reader.Close();
}
}
return(userList);
}