/// <summary>
/// Initializes a new instance of the <see cref="Account"/> class.
/// </summary>
/// <param name="id">The account's ID</param>
/// <param name="email">The account's email</param>
/// <param name="firstName">The account's first name</param>
/// <param name="prefix">The account's prefix</param>
/// <param name="lastName">The account's last name</param>
/// <param name="role">The account's role</param>
/// <param name="tramDepot">The TramDepot is used to see which tram depots the account has access to</param>
public Account(int id, string email, string firstName, string prefix, string lastName, AccountType role, TramDepot tramDepot)
{
this.ID = id;
this.Email = email;
this.FirstName = firstName;
this.Prefix = prefix;
this.LastName = lastName;
this.Role = role;
this.Depot = tramDepot;
}
/// <summary>
/// Generate a general service list of all services
/// </summary>
/// <param name="serviceType">This decides what the type of the service is</param>
/// <param name="tramDepot">This is the current tram depot</param>
/// <returns>A generated general list</returns>
public List<Service> GenerateList(string serviceType, TramDepot tramDepot)
{
List<Service> services = new List<Service>();
try
{
if (serviceType == "Cleaning")
{
services.AddRange(DatabaseManager.GetCleaningList(tramDepot));
}
else if (serviceType == "Repair")
{
services.AddRange(DatabaseManager.GetRepairList(tramDepot));
}
}
catch
{
throw;
}
return services;
}
/// <summary>
/// Returns a List with RepairServices of services that have not been completed yet for the given tram depot. Services will only be returned for trams that are currently parked on the (sectors of the) tram depot.
/// </summary>
/// <param name="tramDepot">The tram depot to fetch all uncompleted repair services for.</param>
/// <param name="closeConnection">If true, closes the database connection at the end of this method.</param>
/// <returns>A List with all repair services for parked trams on the given tram depot that have not been completed yet.</returns>
public static List<RepairService> GetRepairList(TramDepot tramDepot, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("SELECT b.ID AS BID, b.startTijdstip AS BSTARTTIJDSTIP, b.WAGEN_ID AS BWAGEN_ID, b.beschrijving AS BBESCHRIJVING, b.isGroot AS BISGROOT, w.nummer AS WNUMMER, w.status as WSTATUS, w.les AS WLES FROM BEURT b LEFT JOIN WAGEN W ON b.WAGEN_ID = w.ID WHERE eindTijdStip IS NULL AND b.ID IN ( SELECT ID FROM SERVICEBEURT ) AND b.WAGEN_ID IN ( SELECT WAGEN_ID FROM SECTOR WHERE SPOOR_ID IN ( SELECT ID FROM SPOOR WHERE REMISE_ID = :TramDepotID )) ORDER BY b.startTijdStip");
command.Parameters.Add(":TramDepotID", tramDepot.ID);
OracleDataReader reader = ExecuteQuery(command);
List<RepairService> repairServices = new List<RepairService>();
while (reader.Read())
{
try
{
int serviceId = Convert.ToInt32(reader["BID"].ToString());
DateTime startingTime = Convert.ToDateTime(reader["BSTARTTIJDSTIP"]);
int tramID = Convert.ToInt32(reader["BWAGEN_ID"].ToString());
string description = reader["BBESCHRIJVING"].ToString();
int isLargeInt = Convert.ToInt32(reader["BISGROOT"].ToString());
bool isLarge = isLargeInt == 1;
string number = reader["WNUMMER"].ToString();
string stateString = reader["WSTATUS"].ToString();
State state;
if (!Enum.TryParse(stateString, out state))
{
throw new ArgumentException("Tram state could not be parsed as a State");
}
int educationalInt = Convert.ToInt32(reader["WLES"].ToString());
bool educational = educationalInt == 1;
Tram tram = new Tram(tramID, number, state, null, educational, null);
repairServices.Add(new RepairService(serviceId, startingTime, null, isLarge, tram, null));
}
catch (Exception exc)
{
Debug.WriteLine(exc.Message);
continue;
}
}
return repairServices;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// Returns a list with all reservations for trams on lines, for the given tram depot.
/// Reservations are used to keep at least on the sector on the rail of the reservation free for the given tram the reservation is made for.
/// </summary>
/// <param name="tramDepot">The tram depot to fetch all reservations for.</param>
/// <param name="closeConnection">If true, closes the database connection at the end of this method.</param>
/// <returns>A list with all reservations for trams on lines, for the given tram depot.</returns>
public static List<Reservation> GetReservations(TramDepot tramDepot, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("SELECT SPOOR_ID, WAGEN_ID FROM RESERVERING WHERE SPOOR_ID IN ( SELECT ID FROM SPOOR WHERE REMISE_ID = :TramDepotID )");
command.Parameters.Add(":TramDepotID", tramDepot.ID);
OracleDataReader reader = ExecuteQuery(command);
List<Reservation> reservations = new List<Reservation>();
while (reader.Read())
{
try
{
Tram t = new Tram(Convert.ToInt32(reader["WAGEN_ID"].ToString()));
Rail r = new Rail(Convert.ToInt32(reader["SPOOR_ID"].ToString()));
reservations.Add(new Reservation(t, r));
}
catch (Exception exc)
{
Debug.WriteLine(exc.Message);
continue;
}
}
return reservations;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// Returns all active accounts that have rights on the given TramDepot.
/// Used by the Account Management System
/// </summary>
/// <param name="tramDepot">The TramDepot to fetch all accounts for</param>
/// <returns>A list of accounts that have rights on the given TramDepot</returns>
public static List<Account> GetAccounts(TramDepot tramDepot)
{
try
{
OracleCommand command = CreateOracleCommand("SELECT ID, email, voornaam, tussenvoegsel, achternaam, rol FROM ACCOUNT WHERE ID IN ( SELECT ACCOUNT_ID FROM REMISE_ACCOUNT WHERE REMISE_ID = :TramDepotID ) AND status = '1'");
command.Parameters.Add(":TramDepotID", tramDepot.ID);
OracleDataReader reader = ExecuteQuery(command);
List<Account> accounts = new List<Account>();
while (reader.Read())
{
try
{
accounts.Add(ParseAccount(reader, tramDepot));
}
catch (Exception exc)
{
Debug.WriteLine(exc.Message);
continue;
}
}
return accounts;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Returns a list with all rails of the given tram depot, with the sectors in each rail.
/// </summary>
/// <param name="tramDepot">The tram depot to fetch all rails for.</param>
/// <param name="closeConnection">Whether the database connection should be closed at the end of this method.</param>
/// <returns>A list with all rails of the given tram depot, with the sectors in each rail.</returns>
public static List<Rail> GetRails(TramDepot tramDepot, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("SELECT ID, nummer, type FROM SPOOR WHERE REMISE_ID = :TramDepotID");
command.Parameters.Add(":TramDepotID", tramDepot.ID);
OracleDataReader reader = ExecuteQuery(command);
List<Rail> rails = new List<Rail>();
while (reader.Read())
{
try
{
Rail r = ParseRail(reader);
r.Sectors = GetSectors(r, false);
rails.Add(r);
}
catch (Exception exc)
{
Debug.WriteLine(exc.Message);
continue;
}
}
return rails;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// Adds the given tram to the database, belonging to the given tram depot.
/// The ID of the given tram is ignored, as the database will use it's next valid value (using PLSQL sequences).
/// </summary>
/// <param name="tram">The tram to add to the database.</param>
/// <param name="tramDepot">The tram depot the tram belongs to.</param>
/// <returns>The tram with the new database ID set.</returns>
public static Tram AddTram(Tram tram, TramDepot tramDepot)
{
try
{
// Add tram
OracleCommand command = CreateOracleCommand("INSERT INTO WAGEN(ID, STATUS, NUMMER, WAGENTYPE_ID, REMISE_ID, LES) VALUES (SEQ_WAGENID.NEXTVAL, :TramState, :TramNumber, :TramTypeID, :TramDepotID, :Educational)");
command.Parameters.Add(":TramState", tram.State.ToString());
command.Parameters.Add(":TramNumber", tram.Number);
command.Parameters.Add(":TramTypeID", tram.TramType.ID);
command.Parameters.Add(":TramDepotID", tramDepot.ID);
int educationalInt = tram.UsedForEducationalPurposes ? 1 : 0;
command.Parameters.Add(":Educational", educationalInt);
bool isAdded = ExecuteNonQuery(command);
if (!isAdded)
{
throw new Exception("The tram could not be added to the database. Please confirm all values are correct.");
}
// Get tram ID from database
OracleCommand command2 = CreateOracleCommand("SELECT SEQ_WAGENID.CURRVAL AS ID FROM DUAL");
OracleDataReader reader = ExecuteQuery(command2);
reader.Read();
int tramID = Convert.ToInt32(reader["ID"].ToString());
tram.ID = tramID;
return tram;
}
finally
{
connection.Close();
}
}
/// <summary>
/// Parses the current data of an OracleDataReader into a new Account. Given reader must contain column data for $'ID', 'email', 'voornaam', 'tussenvoegsel', 'achternaam', 'rol'$.
/// reader.Read() has to be called prior to parsing the data.
/// </summary>
/// <param name="reader">OracleDataReader to read data from</param>
/// <param name="tramDepot">TramDepot that the Account has rights for</param>
/// <returns>The Account parsed from the data in the reader, or null</returns>
private static Account ParseAccount(OracleDataReader reader, TramDepot tramDepot)
{
int accountID = Convert.ToInt32(reader["ID"].ToString());
string email = reader["email"].ToString();
string firstName = reader["voornaam"].ToString();
string prefix = reader["tussenvoegsel"].ToString();
string lastName = reader["achternaam"].ToString();
string role = reader["rol"].ToString();
AccountType accountType;
if (!Enum.TryParse(role, out accountType))
{
throw new ArgumentException("Account role could not be parsed as an AccountType");
}
return new Account(accountID, email, firstName, prefix, lastName, accountType, tramDepot);
}
/// <summary>
/// Verifies if the given email and password match an account that exists and has rights on the given TramDepot. Inactive (deleted) accounts do not have any rights.
/// Return the Account with details, if the parameters match an Account. Otherwise, returns null.
/// </summary>
/// <param name="email">The email address of the account</param>
/// <param name="password">The password of the account</param>
/// <param name="tramDepot">The TramDepot to check permissions for.</param>
/// <returns>Account if parameters match an account with permissions on the TramDepot, or null.</returns>
public static Account VerifyLogin(string email, string password, TramDepot tramDepot)
{
try
{
OracleCommand command = CreateOracleCommand("SELECT ID, Email, voornaam, tussenvoegsel, achternaam, rol FROM ACCOUNT WHERE email = :Email AND status = '1' AND wachtwoord = :Password AND ID IN ( SELECT ACCOUNT_ID FROM REMISE_ACCOUNT WHERE REMISE_ID = :TramDepotID )");
command.Parameters.Add(":Email", email);
command.Parameters.Add(":Password", password);
command.Parameters.Add(":TramDepotID", tramDepot.ID);
OracleDataReader reader = ExecuteQuery(command);
if (!reader.HasRows)
{
return null;
}
reader.Read();
return ParseAccount(reader, tramDepot);
}
finally
{
connection.Close();
}
}
/// <summary>
/// Returns a DataTable filled with all trams that belong to the given tram depot. Only the database ID of the tram type is included.
/// Columns:
/// $Tram.ID, Tram.Number, Tram.State(string), Tram.IsUsedForEducationalPurposes(int), Tram.TramType.ID$
/// $WID, WNUMMER, WSTATUS, WLES, WTYPEID$
/// </summary>
/// <param name="tramDepot">The tram depot to fetch all trams for.</param>
/// <param name="closeConnection">Whether the database connection should be closed at the end of this method.</param>
/// <returns>A DataTable filled with all trams, and the database ID of their tram type.</returns>
public static DataTable GetTramsOfDepot(TramDepot tramDepot, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("SELECT ID AS WID, nummer AS WNUMMER, status AS WSTATUS, les AS WLES, WAGENTYPE_ID AS WTYPEID FROM WAGEN WHERE REMISE_ID = :TramDepotID AND status != 'Removed'");
command.Parameters.Add(":TramDepotID", tramDepot.ID);
OracleDataAdapter oda = new OracleDataAdapter(command);
DataTable table = new DataTable();
oda.Fill(table);
return table;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// Returns a DataTable filled with the data used by the TramDepotManagementSystem.
/// Rail.ID, Rail.Number, Rail.Type, Sector.ID, Sector.Number, Sector.IsBlocked, Tram.ID, Tram.State, Tram.Number, Tram.IsUsedForEducationalPurposes, TramType.ID, TramType.Name, TramType.Length, TramType.IsDoubleSided, Line.ID, Line.Number, Line.Color.
/// SPID, SPNUMMER, SPTYPE, SEID, SENUMMER, SEGEBLOKKEERD, WID, WSTATUS WNUMMER, WLES, WTID, WTLENGTE, WTISDUBBELZIJDIG, LID, LNUMMER, LKLEUR
/// Tram and TramType columns are null if no tram is found on the sector.
/// Line columns are null if no line is bound to the rail.
/// </summary>
/// <param name="tramDepot">The tram depot to fetch all rail data for, including the lines the rails are bound to, the sectors of the rails, the trams located on the sectors and the types of the trams.</param>
/// <param name="closeConnection">If true, closes the database connection at the end of this method.</param>
/// <returns>DataTable filled with the column data in the order shown in the summary of this method.</returns>
public static DataTable GetTramDepotData(TramDepot tramDepot, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("SELECT sp.ID AS SPID, sp.NUMMER AS SPNUMMER, sp.TYPE AS SPTYPE, se.ID AS SEID, se.NUMMER as SENUMMER, se.GEBLOKKEERD AS SEGEBLOKKEERD, w.ID AS WID, sp.LID AS LID, sp.LNUMMER AS LNUMMER, sp.LKLEUR AS LKLEUR FROM (SELECT sp.ID, sp.NUMMER, sp.TYPE, sp.REMISE_ID, l.ID AS LID, l.NUMMER AS LNUMMER, l.KLEUR AS LKLEUR FROM SPOOR sp LEFT JOIN LIJN_SPOOR ls ON ls.SPOOR_ID = sp.ID LEFT JOIN LIJN l ON l.ID = ls.LIJN_ID) sp, SECTOR se LEFT JOIN WAGEN w ON se.WAGEN_ID = w.ID WHERE sp.ID = se.SPOOR_ID AND sp.REMISE_ID = :TramDepotID");
command.Parameters.Add(":TramDepotID", tramDepot.ID);
OracleDataAdapter oda = new OracleDataAdapter(command);
DataTable table = new DataTable();
oda.Fill(table);
return table;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
