/// <summary>
/// Adds a tram.
/// </summary>
/// <param name="sender">contains a reference to the clicked button.</param>
/// <param name="e">contains the data from the click event.</param>
private void BtnAddTram_Click(object sender, EventArgs e)
{
if (this.IsAddTramInputValid())
{
string number = this.tbTramNumber.Text.Trim();
bool usedForEducationalPurposes = this.cboxUsedForEducationalPurposes.Checked;
TramType tramType = (TramType)this.cbxTramTypes.SelectedValue;
Tram tram = new Tram(-1, number, State.Depot, tramType, usedForEducationalPurposes, null);
try
{
this.tramDepotManagementSystem.AddTram(tram);
this.UpdateTramDataGridView();
}
catch (Exception ex)
{
this.ShowErrorMessage("The tram could not be added:" + System.Environment.NewLine + ex.Message);
}
this.tbTramNumber.Text = string.Empty;
this.cboxUsedForEducationalPurposes.Checked = false;
}
else
{
this.ShowErrorMessage("Fill in a tram number");
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Notification"/> class.
/// </summary>
/// <param name="tram">The tram for which the notification is made.</param>
/// <param name="preferredState">The state the tram would like to have.</param>
public Notification(Tram tram, State preferredState)
{
this.ID = -1;
this.Tram = tram;
this.PreferredState = preferredState;
this.Date = DateTime.Now;
}
/// <summary>
/// Initializes a new instance of the <see cref="AddCleaningForm" /> class.
/// </summary>
/// <param name="tramDepotManagementSystem">This is the current tram depot management system</param>
/// <param name="tram">This is the given tram</param>
/// <param name="serviceSystem">This is the current service system</param>
public AddCleaningForm(TramDepotManagementSystem tramDepotManagementSystem, Tram tram, ServiceSystem serviceSystem)
: this(serviceSystem)
{
rtbxTramID.Text = tram.Number.ToString();
this.InitializeComponent();
this.tramDepotManagementSystem = tramDepotManagementSystem;
this.tram = tram;
this.serviceSystem = serviceSystem;
}
/// <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>
/// Initializes a new instance of the <see cref="Ride"/> class.
/// </summary>
/// <param name="tram">The tram used for this ride.</param>
/// <param name="time">The time the ride is scheduled for.</param>
/// <param name="line">The line the tram is bound to for this ride</param>
public Ride(Tram tram, DateTime time, Line line)
{
this.Tram = tram;
this.Time = time;
this.Line = line;
}
/// <summary>
/// Moves the given tram from it's old sector (if any) to the given sector.
/// </summary>
/// <param name="tram">The tram to move.</param>
/// <param name="newSector">The sector to move the tram to.</param>
/// <param name="closeConnection">If true, closes the database connection at the end of this method.</param>
/// <returns>True when the tram could be (removed from it's old sector and) added to the new sector.</returns>
public static bool MoveTram(Tram tram, Sector newSector, bool closeConnection = true)
{
try
{
bool removeSuccess = true;
// Remove tram from old sector
if (tram.Sector != null)
{
OracleCommand command = CreateOracleCommand("UPDATE SECTOR SET WAGEN_ID = NULL WHERE ID = :SectorID");
command.Parameters.Add(":SectorID", tram.Sector.ID);
removeSuccess = ExecuteNonQuery(command);
}
// Add tram to new sector
OracleCommand command2 = CreateOracleCommand("UPDATE SECTOR SET WAGEN_ID = :TramID WHERE ID = :NewSectorID");
command2.Parameters.Add(":TramID", tram.ID);
command2.Parameters.Add(":NewSectorID", newSector.ID);
bool addSuccess = ExecuteNonQuery(command2);
if (!removeSuccess && !addSuccess)
{
throw new Exception("The tram could not be removed from the current sector, nor could it be added to the new sector. The database seems to have handled the query correctly, but no rows were updated.");
}
else if (!removeSuccess)
{
throw new Exception("The tram could not be removed from the current sector, but was added to the new sector. The database seems to have handled the query correctly, but no rows were updated for the removal from the old sector.");
}
else if (!addSuccess)
{
throw new Exception("The tram was removed from the current sector, but couldn't be added to the new sector. The database seems to have handled the query correctly, but no rows were updated for the new position of the tram.");
}
return addSuccess && removeSuccess;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// Adds a new cleaning service.
/// </summary>
/// <param name="cleaningService">The cleaning service which is to be added.</param>
/// <param name="tram">The tram for which the cleaning service needs to be added.</param>
public void AddCleaningService(CleaningService cleaningService, Tram tram)
{
DatabaseManager.AddCleaningService(cleaningService, tram);
}
/// <summary>
/// Removes a tram from consecutively the database and the application.
/// If the attempt to remove the tram from the database failed, a string containing the error message is returned.
/// </summary>
/// <param name="tram">The tram which is to be removed.</param>
/// <returns>A string containing an error message, provided that an error occurs. If no error occurs, the string will be 'null'.</returns>
public string RemoveTram(Tram tram)
{
try
{
DatabaseManager.RemoveTram(tram);
}
catch (Exception exc)
{
return "The tram could not be removed:" + System.Environment.NewLine + exc.Message;
}
this.Trams.Remove(tram);
return null;
}
/// <summary>
/// Adds a cleaning service to the schedule
/// </summary>
/// <param name="cleaningService">This is the given cleaning service</param>
/// <param name="tram">This is the given tram</param>
/// <returns>Returns a true of false depending on the success of adding the service to the schedule</returns>
public bool AddCleaningService(CleaningService cleaningService, Tram tram)
{
DatabaseManager.AddCleaningService(cleaningService, tram);
return true;
}
/// <summary>
/// Returns the sector the tram is located on, or null.
/// </summary>
/// <param name="tram">The tram to get the sector of.</param>
/// <param name="closeConnection">Whether the database connection should be closed at the end of this method.</param>
/// <returns>The sector the given tram is located on, or null if the tram isn't located on a sector.</returns>
public static Sector GetSector(Tram tram, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("SELECT ID, nummer, SPOOR_ID, geblokkeerd FROM SECTOR WHERE WAGEN_ID = :TramID");
command.Parameters.Add(":TramID", tram.ID);
OracleDataReader reader = ExecuteQuery(command);
int sectorID = Convert.ToInt32(reader["ID"].ToString());
int number = Convert.ToInt32(reader["nummer"].ToString());
int railID = Convert.ToInt32(reader["SPOOR_ID"].ToString());
int blockedInt = Convert.ToInt32(reader["geblokkeerd"].ToString());
bool blocked = blockedInt == 1;
return new Sector(sectorID, number, blocked, tram);
}
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 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>
/// 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>
/// Adds the given repair service to the database.
/// </summary>
/// <param name="repairService">The repair service to add.</param>
/// <param name="tram">The tram the service is for.</param>
/// <param name="closeConnection">Whether the database connection should be closed at the end of this method.</param>
/// <returns>The repair service with it's database ID set.</returns>
public static RepairService AddRepairService(RepairService repairService, Tram tram, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("INSERT INTO BEURT(ID, startTijdstip, WAGEN_ID, beschrijving, isGroot) VALUES(SEQ_BEURTID.NEXTVAL, :StartTime, :TramID, :Description, :IsLarge)");
int isLarge = repairService.IsLarge ? 1 : 0;
command.Parameters.Add(":StartTime", repairService.StartingTime);
command.Parameters.Add(":TramID", tram.ID);
command.Parameters.Add(":Description", repairService.Description);
command.Parameters.Add(":IsLarge", isLarge);
bool isAdded = ExecuteNonQuery(command);
if (!isAdded)
{
throw new Exception("The repair service could not be added to the database.");
}
OracleCommand command2 = CreateOracleCommand("INSERT INTO SERVICEBEURT(ID) VALUES (SEQ_BEURTID.CURRVAL)");
ExecuteNonQuery(command2);
OracleCommand command3 = CreateOracleCommand("SELECT MAX(ID) AS ID FROM SERVICEBEURT");
OracleDataReader reader = ExecuteQuery(command3);
reader.Read();
int id = Convert.ToInt32(reader["ID"].ToString());
repairService.ID = id;
return repairService;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// Removes the given tram from the database, by setting it's status to removed.
/// </summary>
/// <param name="tram">The tram to remove.</param>
/// <param name="closeConnection">Whether the database connection should be closed at the end of this method.</param>
/// <returns>Value indicating whether the removing succeeded or not.</returns>
public static bool RemoveTram(Tram tram, bool closeConnection = true)
{
try
{
OracleCommand command = CreateOracleCommand("UPDATE WAGEN SET status = 'Removed' WHERE ID = :TramID");
command.Parameters.Add(":TramID", tram.ID);
bool isRemoved = ExecuteNonQuery(command);
if (!isRemoved)
{
throw new Exception("The tram could not be removed from the database!");
}
OracleCommand command2 = CreateOracleCommand("BEGIN " +
"UPDATE SECTOR SET WAGEN_ID NULL WHERE WAGEN_ID = :TramID" +
"DELETE FROM RESERVERING WHERE WAGEN_ID = :TramID;" +
"DELETE FROM SERVICEBEURT WHERE WAGEN_ID = :TramID;" +
"DELETE FROM SCHOONMAAKBEURT WHERE WAGEN_ID = :TramID;" +
"DELETE FROM BEURT WHERE WAGEN_ID = :TramID;" +
"DELETE FROM MELDING WHERE WAGEN_ID = :TramID;" +
"END;");
command2.Parameters.Add(":TramID", tram.ID);
return ExecuteNonQuery(command);
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
/// <summary>
/// Adds a tram to consecutively the database and the application.
/// If the attempt to add the tram to the database failed, a string containing the error message is returned.
/// </summary>
/// <param name="tram">The tram which is to be added.</param>
public void AddTram(Tram tram)
{
this.Trams.Add(DatabaseManager.AddTram(tram, LoggedInAccount.AccountLoggedIn.Depot));
}
/// <summary>
/// Moves the given tram to the given sector.
/// </summary>
/// <param name="tram">The tram which is to be moved.</param>
/// <param name="newSector">The sector that the tram is to be moved to.</param>
public void MoveTram(Tram tram, Sector newSector)
{
DatabaseManager.MoveTram(tram, newSector);
}
/// <summary>
/// Adds a repair service to the schedule
/// </summary>
/// <param name="repairService">This is the given repair service</param>
/// <param name="tram">This is the given tram</param>
/// <returns>Returns a true of false depending on the success of adding the service to the schedule</returns>
public bool AddRepairService(RepairService repairService, Tram tram)
{
DatabaseManager.AddRepairService(repairService, tram);
return false;
}
/// <summary>
/// Loads the tram data in the form.
/// </summary>
/// <param name="closeConnection">closes the connection after the method is done.</param>
private void LoadTramData(bool closeConnection = true)
{
this.Trams.Clear();
DataTable data = DatabaseManager.GetTramsOfDepot(LoggedInAccount.AccountLoggedIn.Depot, closeConnection);
foreach (DataRow row in data.Rows)
{
int tramID = Convert.ToInt32(row["WID"].ToString());
string tramNumber = row["WNUMMER"].ToString();
int tramTypeID = Convert.ToInt32(row["WTYPEID"].ToString());
TramType tramType = this.TramTypes.Find(t => t.ID == tramTypeID);
State tramState;
if (!Enum.TryParse(row["WSTATUS"].ToString(), out tramState))
{
throw new ArgumentException("Tram state could not be parsed as a state");
}
int usedForEducationalPurposesNumber = Convert.ToInt32(row["WLES"].ToString());
bool usedForEducationalPurposes = usedForEducationalPurposesNumber == 1;
Tram tram = new Tram(tramID, tramNumber, tramState, tramType, usedForEducationalPurposes, null);
this.Trams.Add(tram);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Reservation"/> class.
/// Used to reserve at least one sector on the given rail for the given tram.
/// </summary>
/// <param name="tram">Tram the reservation is for.</param>
/// <param name="rail">The rail reserved for the tram.</param>
public Reservation(Tram tram, Rail rail)
{
this.Tram = tram;
this.Rail = rail;
}
/// <summary>
/// Adds a new repair service.
/// </summary>
/// <param name="repairService">The repair service which is to be added.</param>
/// <param name="tram">The tram for which the repair service needs to be added.</param>
public void AddRepairService(RepairService repairService, Tram tram)
{
DatabaseManager.AddRepairService(repairService, tram);
}
/// <summary>
/// Adds the given cleaning service to the database.
/// The ID of the given service is ignored, as the database will use it's next valid value (using PLSQL sequences).
/// </summary>
/// <param name="cleaningService">The cleaning service to add to the database.</param>
/// <param name="tram">The tram the cleaning service is for.</param>
/// <param name="closeConnection">Whether the database connection should be closed at the end of this method.</param>
/// <returns>The cleaning service with it's database ID set.</returns>
public static CleaningService AddCleaningService(CleaningService cleaningService, Tram tram, bool closeConnection = true)
{
try
{
// Inesrt Cleaning Service into service table
OracleCommand command = CreateOracleCommand("INSERT INTO BEURT(ID, startTijdstip, WAGEN_ID, beschrijving, isGroot) VALUES(SEQ_BEURTID.NEXTVAL, :StartTime, :TramID, :Description, :IsLarge)");
int isLarge = cleaningService.IsLarge ? 1 : 0;
command.Parameters.Add(":StartTime", cleaningService.StartingTime);
command.Parameters.Add(":TramID", tram.ID);
command.Parameters.Add(":Description", cleaningService.Description);
command.Parameters.Add(":IsLarge", isLarge);
bool isAdded = ExecuteNonQuery(command);
if (!isAdded)
{
throw new Exception("The cleaning service could not be added to the database.");
}
// Fetch the database ID of the service
OracleCommand command2 = CreateOracleCommand("SELECT MAX(ID) AS ID FROM BEURT");
OracleDataReader reader = ExecuteQuery(command2);
reader.Read();
int id = Convert.ToInt32(reader["ID"].ToString());
cleaningService.ID = id;
// Insert the service into the cleaning service table
OracleCommand command3 = CreateOracleCommand("INSERT INTO SCHOONMAAKBEURT(ID) VALUES (:ServiceID)");
command3.Parameters.Add(":ServiceID", id);
ExecuteNonQuery(command3);
return cleaningService;
}
finally
{
if (closeConnection)
{
connection.Close();
}
}
}
