public TransportCenter FetchOne(int id)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sqlQuery = "SELECT * from dbo.Centers WHERE Id = @id";
SqlCommand command = new SqlCommand(sqlQuery, connection);
command.Parameters.Add("@Id", System.Data.SqlDbType.Int).Value = id;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
TransportCenter transportCenter = new TransportCenter();
if (reader.HasRows)
{
while (reader.Read())
{
transportCenter.Id = reader.GetInt32(reader.GetOrdinal("Id"));
transportCenter.Name = reader.GetString(reader.GetOrdinal("Name"));
}
}
return(transportCenter);
}
}
public List <TransportCenter> FetchAll()
{
List <TransportCenter> returnList = new List <TransportCenter>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sqlQuery = "SELECT * FROM dbo.Centers";
SqlCommand command = new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
TransportCenter center = new TransportCenter();
center.Id = reader.GetInt32(0);
center.Name = reader.GetString(1);
returnList.Add(center);
}
}
}
return(returnList);
}
public List <Routes> FetchAll()
{
List <Routes> returnList = new List <Routes>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sqlQuery = "SELECT * FROM dbo.Routes";
SqlCommand command = new SqlCommand(sqlQuery, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Routes route = new Routes();
TransportCenterDAO tcDAO = new TransportCenterDAO();
int centerAId = reader.GetInt32(reader.GetOrdinal("Center_A_Id"));
TransportCenter centerA = tcDAO.FetchOne(centerAId);
int centerBId = reader.GetInt32(reader.GetOrdinal("Center_B_Id"));
TransportCenter centerB = tcDAO.FetchOne(centerBId);
TransportTypeDAO ttDAO = new TransportTypeDAO();
int typeId = reader.GetOrdinal("Transport_Id");
TransportType transportType = ttDAO.FetchOne(typeId);
route.Id = reader.GetInt32(reader.GetOrdinal("Id"));
route.CenterA = centerA;
route.CenterB = centerB;
route.Type = transportType;
if (reader.IsDBNull(reader.GetOrdinal("Travel_Time_Hours")))
{
route.TravelTime = 1000;
}
else
{
route.TravelTime = reader.GetInt32(reader.GetOrdinal("Travel_Time_Hours"));
}
route.Price = (int)transportType.PricePerHour * route.TravelTime;
returnList.Add(route);
}
}
}
return(returnList);
}
public Routes FetchOne(int A_ID, int B_ID)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sqlQuery = "SELECT * from dbo.Routes WHERE(CENTER_A_ID = @A_ID AND CENTER_B_ID = @B_ID AND TRANSPORT_ID = 1) OR (CENTER_B_ID = @A_ID AND CENTER_A_ID = @B_ID AND TRANSPORT_ID = 1)";
SqlCommand command = new SqlCommand(sqlQuery, connection);
command.Parameters.Add("@A_Id", System.Data.SqlDbType.Int).Value = A_ID;
command.Parameters.Add("@B_Id", System.Data.SqlDbType.Int).Value = B_ID;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
Routes route = new Routes();
if (reader.HasRows)
{
while (reader.Read())
{
TransportCenterDAO tcDAO = new TransportCenterDAO();
int centerAId = reader.GetInt32(reader.GetOrdinal("Center_A_Id"));
TransportCenter centerA = tcDAO.FetchOne(centerAId);
int centerBId = reader.GetInt32(reader.GetOrdinal("Center_B_Id"));
TransportCenter centerB = tcDAO.FetchOne(centerBId);
TransportTypeDAO ttDAO = new TransportTypeDAO();
int typeId = reader.GetOrdinal("Transport_Id");
TransportType transportType = ttDAO.FetchOne(typeId);
route.Id = reader.GetInt32(reader.GetOrdinal("Id"));
route.CenterA = centerA;
route.CenterB = centerB;
route.Type = transportType;
if (reader.IsDBNull(reader.GetOrdinal("Travel_Time_Hours")))
{
route.TravelTime = 1000;
}
else
{
route.TravelTime = reader.GetInt32(reader.GetOrdinal("Travel_Time_Hours"));
}
route.Price = (int)transportType.PricePerHour * route.TravelTime;
}
}
return(route);
}
}
public void ComputeRouteTest()
{
var t1 = new TransportCenter();
t1.Name = "DARFUR";
t1.Id = 19;
var t2 = new TransportCenter();
t2.Name = "ADDIS_ABEBA";
t2.Id = 17;
var test = new RouteEngine();
var res = test.ComputeRoute(null, t1, t2);
Assert.IsNotNull(res);
}
public Tuple <List <int>, int> ComputeRoute(Package packageInfo, TransportCenter from, TransportCenter to)
{
var keyFrom = Dictio.centerIdToKey[from.Id];
var keyTo = Dictio.centerIdToKey[to.Id];
ShortestPathResult result = _map.Dijkstra(keyFrom, keyTo);
List <int> shortestRoute = new List <int>();
foreach (var node in result.GetPath())
{
var centerId = Dictio.keyToCenterId[node];
shortestRoute.Add(centerId);
}
Tuple <List <int>, int> output = new Tuple <List <int>, int>(shortestRoute, result.Distance);
return(output);
}
