public static List<INFATITrip> CalculateTripsByCarId(Int16 carId)
{
DBController dbc = new DBController();
//Fetch all temporalinformation for a cardId
List<TemporalInformation> datapoints = dbc.GetTimestampsByCarId(carId);
dbc.Close();
//Instantiate the containers for trips and timestamps fetched by a single date at a time
List<INFATITrip> allTrips = new List<INFATITrip>();
INFATITrip trip = new INFATITrip(carId);
allTrips.Add(trip);
//First case
allTrips.Last().Timestamps.Add(new TemporalInformation(datapoints.ElementAt(0).EntryId, datapoints.ElementAt(0).Timestamp));
//Starting to iterate over all timestamps
for (int i = 1; i < datapoints.Count(); i++) {
//Compare the last seen timestamp to the current, if more than 300 seconds has past, create a new trip and store the current timestamp in it
if (Math.Abs(ToUnixTime(allTrips.Last().Timestamps.Last().Timestamp) - ToUnixTime(datapoints.ElementAt(i).Timestamp)) <= 180) {
allTrips.Last().Timestamps.Add(new TemporalInformation(datapoints.ElementAt(i).EntryId, datapoints.ElementAt(i).Timestamp));
} else {
allTrips.Add(new INFATITrip(carId));
allTrips.Last().Timestamps.Add(new TemporalInformation(datapoints.ElementAt(i).EntryId, datapoints.ElementAt(i).Timestamp));
}
}
return allTrips;
}
public int UpdateFactTable(Int64 tripId, INFATITrip trip)
{
string sql = String.Format("UPDATE gpsfact SET tripid = '{0}' WHERE entryid = '{1}'", tripId, trip.Timestamps[0].EntryId);
StringBuilder sb = new StringBuilder(sql);
for (int i = 1; i < trip.Timestamps.Count; i++) {
sb.Append(String.Format(" OR entryid = '{0}'", trip.Timestamps[i].EntryId));
}
NpgsqlCommand command = new NpgsqlCommand(sb.ToString(), Connection);
try {
return NonQuery(command, "gpsfact");
} catch (Exception e) {
Console.WriteLine(e.ToString());
}
return 0;
}
public int InsertTripAndUpdateFactTable(INFATITrip trip)
{
Int64 tripId = AddTripInformation(trip.CarId);
if (trip.Timestamps.Count > 2000) {
List<INFATITrip> subTrips = new List<INFATITrip>();
INFATITrip subTrip;
int index = 0;
while (true) {
if (index + 2000 > trip.Timestamps.Count) {
subTrip = new INFATITrip(trip.CarId);
subTrip.Timestamps = trip.Timestamps.GetRange(index, trip.Timestamps.Count - index);
subTrips.Add(subTrip);
break;
} else {
subTrip = new INFATITrip(trip.CarId);
subTrip.Timestamps = trip.Timestamps.GetRange(index, 2000);
subTrips.Add(subTrip);
}
index = index + 2000;
}
foreach (INFATITrip sub in subTrips) {
UpdateFactTable(tripId, sub);
}
} else {
UpdateFactTable(tripId, trip);
}
return 0;
}
