public bool adminCreateUser(string email, string password, UserType type)
{
if (!getCurrentUserIsAdmin())
{
return false;
}
if (WebSecurity.UserExists(email))
{
return false;
}
var user = new User();
user.Email = email;
user.UserType = type;
db.Users.Add(user);
db.SaveChanges();
WebSecurity.CreateAccount(email, password);
return true;
}
public bool changeUserType(User user, UserType type)
{
user.UserType = type;
db.Entry(user).State = EntityState.Modified;
db.SaveChanges();
return true;
}
public ActionResult UpdateUser(User user)
{
try
{
user.Prepare();
//if (TryUpdateModel(user, null, null, new[] { "Password" })) --> werkt niet
using (DatabaseQuery query = new DatabaseQuery())
{
if (ModelState.IsValid) //is niet goed door password (wordt ofc niet opgehaald + password moet los aangepast kunnen worden)
{
query.UpdateUser(user);
((Session)this.Session["__MySessionObject"]).User = query.GetUser(user.Id);
return RedirectToAction("UserDetails");
}
}
}
catch (Exception e)
{
ViewBag.Error = "Er is iets fout gegaan met het updaten van de gebruiker: " + e;
return RedirectToAction("ChangeUser", user);
}
return RedirectToAction("UserDetails");
}
public bool changeUserType(User user, string type)
{
return changeUserType(user, (UserType)(Enum.Parse(typeof(UserType), type)));
}
public ActionResult Register(User user)
{
user.Prepare();
if (ModelState.IsValid)
{
using (DatabaseQuery query = new DatabaseQuery())
{
query.CreateUser(user);
}
return RedirectToAction("Login");
}
return View(user);
}
public ActionResult Register()
{
CheckSession();
User model = new User();
return View(model);
}
internal bool UpdateAddress(User user)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = _connection;
_transaction = cmd.Connection.BeginTransaction();
cmd.CommandText = "UPDATE address set street=@street, city=@city, postalcode=@postalcode, \"number\"=@number, suffix=@suffix where postalcode=@postalcode and \"number\"=@number and suffix=@suffix";
cmd.Parameters.AddWithValue("street", user.Address.Street);
cmd.Parameters.AddWithValue("city", user.Address.City);
cmd.Parameters.AddWithValue("postalcode", user.Address.PostalCode);
cmd.Parameters.AddWithValue("number", user.Address.HouseNumber);
cmd.Parameters.AddWithValue("suffix", user.Address.Suffix);
//Parameters
bool success = parseNonqueryResult(cmd.ExecuteNonQuery());
if (success)
{
_transaction.Commit();
_transaction.Dispose();
return success; //Commit als het sucessvol is
}
_transaction.Rollback();
_transaction.Dispose();
return success; //Rollback en dispose als het niet lukt
}
}
internal bool UpdateUser(User user)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
_transaction = cmd.Connection.BeginTransaction();
cmd.CommandText = "UPDATE \"user\" SET first_name=@first_name, last_name=@last_name, email_address=@email_address, date_of_birth=@date_of_birth where id = @id;";
cmd.Parameters.AddWithValue("first_name", user.FirstName);
cmd.Parameters.AddWithValue("last_name", user.LastName);
cmd.Parameters.AddWithValue("email_address", user.Email);
cmd.Parameters.AddWithValue("date_of_birth", user.DateOfBirth);
cmd.Parameters.AddWithValue("id", (long)user.Id);
//Parameters
bool success = parseNonqueryResult(cmd.ExecuteNonQuery());
if (success)
{
_transaction.Commit();
_transaction.Dispose();
UpdateAddress(user);
}
return success;
}
}
internal bool setAddress(User user)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = _connection;
_transaction = cmd.Connection.BeginTransaction();
cmd.CommandText = "INSERT INTO address (street, city, postalcode, \"number\", suffix) "
+ "VALUES(@street, @city, @postalcode, @number, @suffix); ";
cmd.Parameters.AddWithValue("street", user.Address.Street);
cmd.Parameters.AddWithValue("city", user.Address.City);
cmd.Parameters.AddWithValue("postalcode", user.Address.PostalCode);
cmd.Parameters.AddWithValue("number", user.Address.HouseNumber);
cmd.Parameters.AddWithValue("suffix", user.Address.Suffix);
//Parameters
bool success = parseNonqueryResult(cmd.ExecuteNonQuery());
if (success)
{
_transaction.Commit();
_transaction.Dispose();
setUserAddress(user);
return success; //Commit als het sucessvol is
}
_transaction.Rollback();
_transaction.Dispose();
return success; //Rollback en dispose als het niet lukt
}
}
internal bool setUserAddress(User user)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = _connection;
_transaction = cmd.Connection.BeginTransaction();
cmd.CommandText = "INSERT INTO user_address (user_id, postalcode, number, suffix, type) "
+ "VALUES(@user_id, @postalcode, @number, @suffix, @type);";
LoginDataModel ldm = new LoginDataModel() { Username = user.Username, Password = user.Password };
cmd.Parameters.AddWithValue("user_id", (long)GetUser(ldm).Id);
cmd.Parameters.AddWithValue("postalcode", user.Address.PostalCode);
cmd.Parameters.AddWithValue("number", user.Address.HouseNumber);
cmd.Parameters.AddWithValue("suffix", user.Address.Suffix);
cmd.Parameters.AddWithValue("type", (int)user.Address.Type);
//Parameters
bool success = parseNonqueryResult(cmd.ExecuteNonQuery());
if (success)
{
_transaction.Commit();
_transaction.Dispose();
return success; //Commit als het sucessvol is
}
_transaction.Rollback();
_transaction.Dispose();
return success; //Rollback en dispose als het niet lukt
}
}
internal List<User> GetUsers()
{
//Krijg een lijst met categorien
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
cmd.CommandText = "select distinct * from \"user\" u join user_address ua on ua.user_id = u.id join address a on ua.postalcode = a.postalcode and ua.\"number\" = a.\"number\" and ua.suffix = a.suffix";
NpgsqlDataReader reader = cmd.ExecuteReader(); //intialiseren
List<User> users = new List<User>();
while (reader.Read())
{ //Kan de ID code niet vinden System.IndexOutOfRangeException: Field not found
User user = new User();
user.Id = (ulong)reader.GetInt32(reader.GetOrdinal("id"));
user.FirstName = reader.GetString(reader.GetOrdinal("first_name"));
user.LastName = reader.GetString(reader.GetOrdinal("last_name"));
user.Username = reader.GetString(reader.GetOrdinal("username"));
user.Email = reader.GetString(reader.GetOrdinal("email_address"));
user.DateOfBirth = reader.GetDateTime(reader.GetOrdinal("date_of_birth"));
user.Role = (UserRole)Enum.ToObject(typeof(UserRole), reader.GetInt32(reader.GetOrdinal("role")));
/*
user.Address.PostalCode = (string)reader.GetString(reader.GetOrdinal("postalcode"));
user.Address.HouseNumber = (int)reader.GetInt64(reader.GetOrdinal("number"));
user.Address.Suffix = (string)reader.GetString(reader.GetOrdinal("suffix"));
user.Address.Street = (string)reader.GetString(reader.GetOrdinal("street"));
user.Address.City = (string)reader.GetString(reader.GetOrdinal("city"));
user.Address.Type = (AddressType)Enum.ToObject(typeof(AddressType), (int)reader.GetInt32(reader.GetOrdinal("type")));
*/
users.Add(user); //Blijf users toeveogen
}
reader.Close();
foreach(User u in users)
{
u.Address = GetAddress(u.Id);
}
return users; //return alle users
}
}
internal User GetUser(ulong userId)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction;
}
cmd.CommandText = "SELECT * FROM \"user\" where id= @userId";
cmd.Parameters.AddWithValue("userId", (long)userId);
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
User user = new User
{
Id = (ulong)reader.GetInt32(reader.GetOrdinal("id")),
FirstName = reader.GetString(reader.GetOrdinal("first_name")),
LastName = reader.GetString(reader.GetOrdinal("last_name")),
Username = reader.GetString(reader.GetOrdinal("username")),
Email = reader.GetString(reader.GetOrdinal("email_address")),
DateOfBirth = reader.GetDateTime(reader.GetOrdinal("date_of_birth")),
Role = (UserRole)Enum.ToObject(typeof(UserRole), (int)reader.GetInt32(reader.GetOrdinal("role")))
};
reader.Close();
user.Address = GetAddress((ulong)reader.GetInt32(reader.GetOrdinal("id")));
return user;
}
reader.Close();
return null; //Anders return een Null waarde
}
}
internal User GetUser(LoginDataModel GetUser)
{
//Haal een user op uit de database
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = _connection;
if (_transaction != null)
{
cmd.Transaction = _transaction; //maak een transactie als hij er niet is
}
cmd.CommandText = "SELECT * FROM \"user\" WHERE \"username\" = @username";
cmd.Parameters.AddWithValue("username", GetUser.Username);
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.Read()) //lees een user
{
User user = new User();
user.Id = (ulong)reader.GetInt32(reader.GetOrdinal("id"));
user.FirstName = reader.GetString(reader.GetOrdinal("first_name"));
user.LastName = reader.GetString(reader.GetOrdinal("last_name"));
user.Username = reader.GetString(reader.GetOrdinal("username"));
user.Email = reader.GetString(reader.GetOrdinal("email_address"));
user.DateOfBirth = reader.GetDateTime(reader.GetOrdinal("date_of_birth"));
user.Role = (UserRole)Enum.ToObject(typeof(UserRole), reader.GetInt32(reader.GetOrdinal("role")));
reader.Close();
user.Address = GetAddress((ulong)reader.GetInt32(reader.GetOrdinal("id")));
return user;
}
reader.Close();
return null;
}
}
internal List<Order> GetOrdersByUser(User user)
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = this._connection;
//kan ook join gebruiken o join orderline ol on ol.order_id = o.id
cmd.CommandText = "SELECT * FROM \"order\" where user_id=@user_id";
cmd.Parameters.AddWithValue("user_id", (long)user.Id);
NpgsqlDataReader reader = cmd.ExecuteReader();
List<Order> orders = new List<Order>();
while (reader.Read())
{
Order order = new Order();
order.Id = (ulong)reader.GetInt32(reader.GetOrdinal("id"));
order.DTime = (DateTime)reader.GetDateTime(reader.GetOrdinal("date_time"));
order.User = this.GetUser((ulong)reader.GetInt64(reader.GetOrdinal("user_id")));
order.OrderLines = this.getOrderlines(order.Id);
order.Status = (OrderStatus)Enum.ToObject(typeof(OrderStatus), reader.GetInt64(reader.GetOrdinal("order_status")));
orders.Add(order);
}
reader.Close();
return orders;
}
}
internal bool CreateUser(User user)
{
//create Query voor de users
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
PBKDF2Password password = new PBKDF2Password(user.Password);
//Maak een speciaal geEncrypt Password aan.
cmd.Connection = _connection;
_transaction = cmd.Connection.BeginTransaction();
cmd.CommandText = "INSERT INTO \"user\" (id, first_name, last_name, username, password_hash, password_salt, password_iterations, email_address, date_of_birth, role)"
+ "VALUES(((SELECT COUNT(id) FROM \"user\")+1), @first_name, @last_name, @username, @password_hash, @password_salt, @password_iterations, @email_address, @date_of_birth, @role)";
cmd.Parameters.AddWithValue("first_name", user.FirstName);
cmd.Parameters.AddWithValue("last_name", user.LastName);
cmd.Parameters.AddWithValue("username", user.Username);
cmd.Parameters.AddWithValue("password_hash", password.Hash);
cmd.Parameters.AddWithValue("password_salt", password.Salt);
cmd.Parameters.AddWithValue("password_iterations", password.Iterations);
cmd.Parameters.AddWithValue("email_address", user.Email);
cmd.Parameters.AddWithValue("date_of_birth", user.DateOfBirth);
cmd.Parameters.AddWithValue("role", (int)user.Role);
//Parameters
bool success = parseNonqueryResult(cmd.ExecuteNonQuery());
if (success)
{
_transaction.Commit();
_transaction.Dispose();
setAddress(user);
return success; //Commit als het sucessvol is
}
_transaction.Rollback();
_transaction.Dispose();
return success; //Rollback en dispose als het niet lukt
}
}
