/// <summary>
/// Adds the given account to the database.
/// The ID of the given account is ignored, as the database will use it's next valid value (using PLSQL sequences).
/// Returns the account with the new (valid) database ID.
/// </summary>
/// <param name="account">The account to add to the database. The account will have rights on the tram depot in the depot property of the account.</param>
/// <param name="password">The password of the account.</param>
/// <param name="closeConnection">If true, closes the database connection at the end of this method.</param>
/// <returns>The given account, with the ID updated.</returns>
public static Account AddAccount(Account account, string password, bool closeConnection = true)
{
try
{
// Create account
OracleCommand commandCreate = CreateOracleCommand("INSERT INTO ACCOUNT(ID, EMAIL, WACHTWOORD, VOORNAAM, TUSSENVOEGSEL, ACHTERNAAM, ROL, STATUS) VALUES (SEQ_ACCOUNTID.NEXTVAL, :Email, :Password, :FirstName, :Prefix, :LastName, :Role, 1)");
commandCreate.Parameters.Add(":Email", account.Email);
commandCreate.Parameters.Add(":Password", password);
commandCreate.Parameters.Add(":FirstName", account.FirstName);
commandCreate.Parameters.Add(":Prefix", account.Prefix);
commandCreate.Parameters.Add(":LastName", account.LastName);
commandCreate.Parameters.Add(":Role", account.Role.ToString());
bool isAdded = ExecuteNonQuery(commandCreate);
if (!isAdded)
{
throw new Exception("The account could not be added to the database.");
}
// Fetch database ID of account
OracleCommand commandID = CreateOracleCommand("SELECT SEQ_ACCOUNTID.CURRVAL AS ID FROM DUAL");
OracleDataReader reader = ExecuteQuery(commandID);
reader.Read();
int id = Convert.ToInt32(reader["ID"].ToString());
account.ID = id;
// Add account permissions for the given tram depot
OracleCommand commandDepot = CreateOracleCommand("INSERT INTO REMISE_ACCOUNT(REMISE_ID, ACCOUNT_ID) VALUES(:TramDepotID, :AccountID)");
commandDepot.Parameters.Add(":TramDepotID", account.Depot.ID);
commandDepot.Parameters.Add(":AccountID", account.ID);
isAdded = ExecuteNonQuery(commandDepot);
if (!isAdded)
{
throw new Exception("The account could not be assigned to the tram depot. Please confirm the tram depot is valid and that the account doesn't already have rights on this tram depot.");
}
return account;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// This method is used to create a new account in the database and application
/// </summary>
/// <param name="account">Contains an account, instant which contains all the account information</param>
/// <param name="password">This contains the account's chosen password</param>
/// <returns>Returns a true of false value depending on the success of the method</returns>
public bool CreateAccount(Account account, string password)
{
try
{
DatabaseManager.AddAccount(account, password);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// This method is used to change the password of the account
/// </summary>
/// <param name="account">Contains an account, instant which contains all the account information</param>
/// <param name="password">This string contains the new password</param>
/// <returns>It returns a true of false value depending on the success</returns>
public bool ChangePassword(Account account, string password)
{
try
{
DatabaseManager.UpdateAccountPassword(account, password);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// Initializes a new instance of the <see cref="RepairService" /> class.
/// </summary>
/// <param name="id">The Service's ID</param>
/// <param name="startingTime">The service's starting time</param>
/// <param name="endingTime">The service's ending time</param>
/// <param name="isLarge">Whether or not the service is big</param>
/// <param name="tram">The tram which received a service</param>
/// <param name="account">The account which was used to complete the service</param>
public RepairService(int id, DateTime? startingTime, DateTime? endingTime, bool isLarge, Tram tram, Account account)
: base(id, startingTime, endingTime, isLarge, tram, account)
{
}
/// <summary>
/// This handles the create account button click event.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void BtnCreateAccount_Click(object sender, EventArgs e)
{
if (this.cbxCreateRole.SelectedIndex != -1 & this.tbxCreateEmail.Text != string.Empty & this.tbxCreateFirstName.Text != string.Empty & this.tbxCreateLastName.Text != string.Empty & this.tbxCreatePassword.Text != string.Empty)
{
string email = this.tbxCreateEmail.Text;
if (email.IndexOf('@') == -1 || email.IndexOf('.') == -1)
{
AccountType tempType = new AccountType();
if (tbxCreatePassword.Text == tbxCreateRePassword.Text)
{
switch (cbxCreateRole.SelectedItem.ToString())
{
case "TramDepotManager":
{
tempType = AccountType.TramDepotManager;
break;
}
case "Administrator":
{
tempType = AccountType.Administrator;
break;
}
case "Cleaner":
{
tempType = AccountType.Cleaner;
break;
}
case "Driver":
{
tempType = AccountType.Driver;
break;
}
case "Technician":
{
tempType = AccountType.Technician;
break;
}
}
Account tempAccount = new Account(-1, this.tbxCreateEmail.Text, this.tbxCreateFirstName.Text, this.tbxCreatePrefix.Text, this.tbxCreateLastName.Text, tempType, LoggedInAccount.AccountLoggedIn.Depot);
if (this.accountManagementSystem.CreateAccount(tempAccount, this.tbxCreatePassword.Text))
{
MessageBox.Show("Account created");
this.UpdateList();
}
else
{
MessageBox.Show("Creation of account failed \n Check to see if the email isn't already taken and all the fields have an input.");
}
}
else
{
MessageBox.Show("Passwords don't match");
}
}
else
{
MessageBox.Show("Input a valid email");
}
}
else
{
MessageBox.Show("Make sure you filled in all the neccessary fields, and you've selected a Role");
}
}
/// <summary>
/// Modifies the password of the account in the database.
/// </summary>
/// <param name="account">The account that needs it's password updated.</param>
/// <param name="newPassword">The new password for the account.</param>
/// <param name="closeConnection">If true, closes the database connection at the end of this method.</param>
/// <returns>Value indicating whether the password was successfully updated.</returns>
public static bool UpdateAccountPassword(Account account, string newPassword, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("UPDATE ACCOUNT SET wachtwoord = :Password WHERE ID = :AccountID");
command.Parameters.Add(":Password", newPassword);
command.Parameters.Add(":AccountID", account.ID);
return ExecuteNonQuery(command);
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// Modifies the account in the database, by using the given account as the updated version.
/// The account using the same database ID will be updated in the database.
/// </summary>
/// <param name="account">The modified account to update in the database.</param>
/// <param name="closeConnection">If true, closes the database connection at the end of this method.</param>
/// <returns>True when the account row was successfully updated in the database.</returns>
public static bool UpdateAccount(Account account, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("UPDATE ACCOUNT SET email = :Email, voornaam = :FirstName, tussenvoegsel = :Prefix, achternaam = :LastName, rol = :Role WHERE ID = :AccountID");
command.Parameters.Add(":Email", account.Email);
command.Parameters.Add(":FirstName", account.FirstName);
command.Parameters.Add(":Prefix", account.Prefix);
command.Parameters.Add(":LastName", account.LastName);
command.Parameters.Add(":Role", account.Role.ToString());
command.Parameters.Add(":AccountID", account.ID);
return ExecuteNonQuery(command);
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// The account to set to inactive in the database. This serves as 'deleting' the user without corrupting the database, as inactive accounts are ignored by other queries.
/// </summary>
/// <param name="account">The account to set to inactive in the database.</param>
/// <param name="closeConnection">Whether the database connection should be closed at the end of this method.</param>
/// <returns>Value indicating if the account was successfully set inactive.</returns>
public static bool RemoveAccount(Account account, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("UPDATE ACCOUNT SET status = '0' WHERE ID = :AccountID");
command.Parameters.Add(":AccountID", account.ID);
return ExecuteNonQuery(command);
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// This method is used to modify account details. By using the provided account object.
/// </summary>
/// <param name="account">Contains the account object, containing the account details</param>
/// <returns>Returns a true of false value depending on the success of the method</returns>
public bool ModifyAccount(Account account)
{
try
{
DatabaseManager.UpdateAccount(account);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// This method is used to the remove the account.
/// </summary>
/// <param name="account">Contains the account object, containing the account details</param>
/// <returns>A true of false, depending if the account could be removed or not.</returns>
public bool RemoveAccount(Account account)
{
try
{
DatabaseManager.RemoveAccount(account);
return true;
}
catch
{
return false;
}
}