public List <RouteData> PullRouteDataByTicket(int inTicketID)
{
string query = "select RouteDataID, CityA, CityB, PickUpTime, DropOffTime, LtlTime, DrivenTime, KM from RouteData as RD" +
"left join TripTicket as TT on TT.TicketID = RD.TicketID" +
"where TT.TicketID = " + inTicketID + ";";
//Create a list to store the result
List <string>[] list = new List <string> [8];
for (int i = 0; i < list.Length; i++)
{
list[i] = new List <string>();
}
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
list[0].Add(dataReader["RouteDataID"] + "");
list[1].Add(dataReader["CityA"] + "");
list[2].Add(dataReader["CityB"] + "");
list[3].Add(dataReader["PickUpTime"] + "");
list[4].Add(dataReader["DropOffTime"] + "");
list[5].Add(dataReader["LtlTime"] + "");
list[6].Add(dataReader["DrivenTime"] + "");
list[7].Add(dataReader["KM"] + "");
}
//close Data Reader
dataReader.Close();
List <RouteData> output = new List <RouteData>();
for (int i = 0; i < list[0].Count; i++)
{
RouteData current = new RouteData();
current.RouteDataID = int.Parse(list[0][i]);
current.CityA = LoadCSV.ToCityID(list[1][i]);
current.CityB = LoadCSV.ToCityID(list[2][i]);
current.PickupTime = double.Parse(list[3][i]);
current.DropoffTime = double.Parse(list[4][i]);
current.LtlTime = double.Parse(list[5][i]);
current.DrivenTime = double.Parse(list[6][i]);
current.KM = int.Parse(list[7][i]);
output.Add(current);
}
return(output);
}
public bool UpdateTruckLocation(int truckID, int newLocation)
{
string query = "update Truck set Current_Location = '" + LoadCSV.ToCityName(newLocation) + "' where TruckID = '" + truckID + "';";
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, connection);
//Execute command
cmd.ExecuteNonQuery();
return(true);
}
public bool LoadTheCSV()
{
LoadCSV.Load(connection);
return(true);
}
