public TramDTO Read(int key)
{
try
{
TramDTO returnTram = new TramDTO();
string query = "SELECT * FROM Tram WHERE Id = @key";
using (SqlConnection con = new SqlConnection(DBConnection._connectionString))
{
using (SqlCommand command = new SqlCommand(query, con))
{
con.Open();
command.Parameters.AddWithValue("@key", key);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
returnTram.Id = reader.GetInt32(0);
returnTram.Type = (TramType)reader.GetInt32(1);
returnTram.TramNumber = reader.GetString(2);
}
}
con.Close();
}
}
returnTram = CombineStatusWithTram(GetStatusesFromTram(returnTram.Id), returnTram);
return(returnTram);
}
catch
{
return(null);
}
}
private TramDTO CombineStatusWithTram(List <StatusDTO> stats, TramDTO tram)
{
if (stats != null)
{
tram.Status = stats;
}
return(tram);
}
public void AddTram(SectorDTO sector, TramDTO tram)
{
if (sector.Tram == null)
{
sector.Tram = tram;
_sectoraccess.Update(sector);
}
}
public void AddStatus(StatusDTO status, TramDTO tram)
{
if (!tram.Status.Any(s => s.Status == status.Status))
{
tram.Status.Add(status);
Update(tram);
}
}
public async Task Update(TramDTO obj)
{
using (_context)
{
_context.Update(obj);
await _context.SaveChangesAsync();
}
}
public TramDTO Read(int key)
{
using (_context)
{
TramDTO tram = new TramDTO();
var readTram = _context.Tram.FirstOrDefault(i => i.Id == key);
return(tram = _mapper.Map <TramDTO>(readTram));
}
}
public void StoreTram(TramDTO tram, TrackDTO track)
{
foreach (SectorDTO sector in track.Sectors)
{
if (_sectorLogic.CheckIfSectorIsEmpty(sector))
{
_sectorLogic.AddTram(sector, tram);
break;
}
}
}
private bool CheckTramType(TramDTO tram, TrackDTO track)
{
return(true);
if (tram.Type == track.TramType || track.TramType == null)
{
return(true);
}
else
{
return(false);
}
}
//public static void AllocateTramToService(TramDTO tram, RepairService _repairServiceLogic, CleaningService _cleaningServiceLogic)
//{
// if (tram.Status.Any(s => s.Status == TramStatus.Defect))
// {
// _repairServiceLogic.AddTram(tram);
// }
// if (tram.Status.Any(s => s.Status == TramStatus.Cleaning))
// {
// _cleaningServiceLogic.AddTram(tram);
// }
//}
public static void AllocateTramToTrack(TramDTO tram, List <TrackDTO> tracks, Track _Tracklogic)
{
//if tram is in the repairservice -> send to a repair track
foreach (TrackDTO track in tracks)
{
if (_Tracklogic.CheckTramCanBeStored(tram, track))
{
_Tracklogic.StoreTram(tram, track);
break;
}
}
}
public static void AllocateToRandomTrack(TramDTO tram, List <TrackDTO> tracks, Track _Tracklogic)
{
bool tramIsStored = false;
do
{
TrackDTO randomTrack = _Tracklogic.GetRandomTrack(tracks);
if (_Tracklogic.CheckTramCanBeStored(tram, randomTrack))
{
_Tracklogic.StoreTram(tram, randomTrack);
tramIsStored = true;
}
} while (tramIsStored == false);
}
public void ReceiveTram(string tramNumber, bool repairstatus, bool cleanstatus, string statusDescription, DepotDTO depot)
{
if (CheckIfTramIsAllowed(tramNumber, _tramlogic))
{
TramDTO tram = _tramlogic.GetTram(tramNumber);
changeTramStatus(tram, repairstatus, cleanstatus, _tramlogic, statusDescription);
//AllocationManager.AllocateTramToService(tram, _repairServiceLogic, _cleaningServiceLogic);
AllocationManager.AllocateToRandomTrack(tram, depot.DepotTracks, _tracklogic);
}
else
{
//not needed yet
}
}
public bool CheckTramCanBeStored(TramDTO tram, TrackDTO track)
{
if (!CheckTramType(tram, track))
{
return(false);
}
foreach (SectorDTO sector in track.Sectors)
{
if (_sectorLogic.CheckIfSectorIsEmpty(sector))
{
return(true);
}
}
return(false);
}
private void UpdateStatuses(TramDTO tram, List <StatusDTO> oldStatuses)
{
foreach (StatusDTO tramStat in tram.Status)
{
if (!oldStatuses.Any(s => s.StatusId == tramStat.StatusId))
{
AddStatus(tram.Id, tramStat);
}
}
foreach (StatusDTO stat in oldStatuses)
{
if (!tram.Status.Any(s => s.StatusId == stat.StatusId))
{
DeleteStatus(stat.StatusId);
}
}
}
private void changeTramStatus(TramDTO tram, bool repairstatus, bool cleanstatus, Tram _tramlogic, string statusDescription)
{
StatusDTO statusInDepot = new StatusDTO();
statusInDepot.Status = TramStatus.Depot;
_tramlogic.AddStatus(statusInDepot, tram);
if (repairstatus)
{
StatusDTO status = new StatusDTO();
status.Status = TramStatus.Defect;
status.Description = statusDescription;
_tramlogic.AddStatus(status, tram);
}
if (cleanstatus)
{
StatusDTO status = new StatusDTO();
status.Status = TramStatus.Cleaning;
_tramlogic.AddStatus(status, tram);
}
}
public void Create(TramDTO obj)
{
string query = "INSERT INTO Tram (Type, TramNumber) VALUES (@type, @tramNumber)";
using (SqlConnection con = new SqlConnection(DBConnection._connectionString))
{
using (SqlCommand command = new SqlCommand(query, con))
{
con.Open();
command.Parameters.AddWithValue("@type", (int)obj.Type);
command.Parameters.AddWithValue("@tramNumber", obj.TramNumber);
command.ExecuteNonQuery();
con.Close();
}
}
if (obj.Status.Count > 0)
{
var tram = ReadFromTramNumber(obj.TramNumber);
foreach (StatusDTO stat in obj.Status)
{
AddStatus(tram.Id, stat);
}
}
}
public DepotDTO Read(int key)
{
DepotDTO depot = new DepotDTO();
depot.DepotTracks = new List <TrackDTO>();
using (SqlConnection conn = new SqlConnection(DBConnection._connectionString))
{
//SELECT dbo.Depot.Id, dbo.Depot.Location, dbo.Track.TrackNumber, dbo.Sector.TramId, dbo.Tram.TramNumber FROM dbo.Depot RIGHT JOIN dbo.Track ON dbo.Depot.Id = dbo.Track.depotId RIGHT JOIN dbo.Sector ON dbo.Track.Id = dbo.Sector.TrackId RIGHT JOIN dbo.Tram ON dbo.Tram.Id = dbo.Sector.TramId WHERE [Id] = @key;
using (SqlCommand cmd = new SqlCommand($"SELECT dbo.Depot.Id, dbo.Depot.Location, dbo.Track.Id, dbo.Track.TrackNumber, dbo.Track.PreferedLine, dbo.Track.PreferedTramType, dbo.Track.RepairServiceId, dbo.Sector.Id, dbo.Sector.Location, dbo.Sector.TramId, dbo.Tram.TramNumber, dbo.Tram.Type, dbo.Status_Tram.StatusId, dbo.Status_Tram.Description, dbo.Status_Tram.Id FROM dbo.Depot RIGHT JOIN dbo.Track ON dbo.Depot.Id = dbo.Track.depotId RIGHT JOIN dbo.Sector ON dbo.Track.Id = dbo.Sector.TrackId LEFT JOIN dbo.Tram ON dbo.Sector.TramId = dbo.Tram.Id LEFT JOIN dbo.Status_Tram ON dbo.Tram.Id = dbo.Status_Tram.TramId WHERE [DepotId] = @key", conn))
{
cmd.Parameters.AddWithValue("@key", key);
conn.Open();
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
TrackDTO track = new TrackDTO();
depot.Id = dataReader.GetInt32(0);
depot.Location = dataReader.GetString(1);
int trackId = dataReader.GetInt32(2);
if (!depot.DepotTracks.Any(i => i.Id == trackId))
{
track.Id = trackId;
track.TrackNumber = dataReader.GetInt32(3);
if (!dataReader.IsDBNull(5))
{
track.TramType = (TramType)dataReader.GetInt32(5);
}
if (!dataReader.IsDBNull(4))
{
track.PreferredTrackLine = dataReader.GetInt32(4);
}
depot.DepotTracks.Add(track);
}
int sectorId = dataReader.GetInt32(7);
if (!depot.DepotTracks.Any(track => track.Sectors.Any(sector => sector.Id == sectorId)))
{
SectorDTO sector = new SectorDTO
{
Id = sectorId,
SectorPosition = dataReader.GetInt32(8),
TrackNumber = dataReader.GetInt32(3),
};
depot.DepotTracks.FirstOrDefault(i => i.Id == dataReader.GetInt32(2)).Sectors.Add(sector);
}
if (!dataReader.IsDBNull(9))
{
int tramId = dataReader.GetInt32(9);
var foundTram = depot.DepotTracks.FirstOrDefault(i => i.Id == dataReader.GetInt32(2)).Sectors.FirstOrDefault(d => d.Id == dataReader.GetInt32(7)).Tram;
if (foundTram == null)
{
TramDTO tram = new TramDTO
{
Id = tramId,
TramNumber = dataReader.GetString(10),
Type = (TramType)dataReader.GetInt32(11),
};
TrackDTO foundTrack = depot.DepotTracks.FirstOrDefault(i => i.Id == dataReader.GetInt32(2));
foundTrack.Sectors.FirstOrDefault(i => i.Id == dataReader.GetInt32(7)).Tram = tram;
}
StatusDTO status = new StatusDTO();
if (!dataReader.IsDBNull(12))
{
status.Status = (TramStatus)dataReader.GetInt32(12);
}
if (!dataReader.IsDBNull(13))
{
status.Description = dataReader.GetString(13);
}
if (!dataReader.IsDBNull(14))
{
status.StatusId = dataReader.GetInt32(14);
TrackDTO foundTrack = depot.DepotTracks.FirstOrDefault(i => i.Id == dataReader.GetInt32(2));
foundTrack.Sectors.FirstOrDefault(i => i.Id == dataReader.GetInt32(7)).Tram.Status.Add(status);
}
}
}
}
}
conn.Close();
}
return(depot);
}
public void Update(TramDTO obj) // now only updates statuses from the tram.
{
UpdateStatuses(obj, GetStatusesFromTram(obj.Id));
}
public void AddTram(TramDTO tram)
{
throw new NotImplementedException();
}
public void Update(TramDTO tram) // private maken?
{
_tramAccess.Update(tram);
}
public void Delete(TramDTO tram)
{
_tramAccess.Delete(tram.Id);
}
public void Create(TramDTO tram)
{
_tramAccess.Create(tram);
}
public void AddStatus(TramDTO tram, TramStatus newStat)
{
}