public static List <SemanticLink> GetTestSemanticLinks()
{
var ret = new List <SemanticLink>();
DataTable semanticLinkTable = new DataTable();
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT");
query.AppendLine(" DISTINCT SemanticLinkID,");
query.AppendLine(" DriverID,");
query.AppendLine(" semantics,");
query.AppendLine(" Distance");
query.AppendLine("FROM default_Outward_SemanticLinks");
query.AppendLine("WHERE SemanticLinkID = 187 OR SemanticLinkID = 188");
query.AppendLine("ORDER BY SemanticLinkID");
semanticLinkTable = DatabaseAccesserEcolog.GetResult(query.ToString());
for (int i = 0; i < semanticLinkTable.Rows.Count; i++)
{
ret.Add(new SemanticLink()
{
SemanticLinkId = (int)semanticLinkTable.Rows[i]["SemanticLinkID"],
DriverId = (int)semanticLinkTable.Rows[i]["DriverID"],
Semantics = (string)semanticLinkTable.Rows[i]["Semantics"],
Distance = (double)semanticLinkTable.Rows[i]["Distance"]
});
}
return(ret);
}
public static int GetEnergyMinTripID(SemanticLink semanticLink, TripDirection direction)
{
return(DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedEnergyMinOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field <int>("TripID"))
.ElementAt(0));
}
public void SetLinks()
{
var ret = new List <Link>();
DataTable semanticLinkTable = new DataTable();
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT links.*");
query.AppendLine("FROM semantic_links");
query.AppendLine(" INNER JOIN links ON semantic_links.link_id = links.link_id");
query.AppendLine("WHERE semantic_link_id = " + this.SemanticLinkId);
query.AppendLine("ORDER BY link_id, num");
semanticLinkTable = DatabaseAccesserEcolog.GetResult(query.ToString());
foreach (DataRow row in semanticLinkTable.Rows)
{
Links.Add(new Link()
{
Num = row.Field <int>("num"),
LinkId = row.Field <string>("link_id"),
Latitude = row.Field <double>("latitude"),
Longitude = row.Field <double>("longitude"),
NodeId = row.Field <string>("node_id"),
Direction = row.Field <int?>("direction")
});
}
}
public static List <Driver> CreateDriversAddAll()
{
var ret = new List <Driver>();
DataTable driverTable = new DataTable();
string query = "SELECT * FROM drivers";
driverTable = DatabaseAccesserEcolog.GetResult(query);
ret.Add(new Driver {
DriverId = 0, Name = "All"
});
for (int i = 0; i < driverTable.Rows.Count; i++)
{
ret.Add(
new Driver()
{
DriverId = (int)driverTable.Rows[i]["driver_id"],
Name = (string)driverTable.Rows[i]["name"]
}
);
}
return(ret);
}
public static List <SemanticLink> GetAllSemanticLinks()
{
var ret = new List <SemanticLink>();
DataTable semanticLinkTable = new DataTable();
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT");
query.AppendLine(" DISTINCT semantic_link_id,");
query.AppendLine(" driver_id,");
query.AppendLine(" semantics");
query.AppendLine("FROM semantic_links");
query.AppendLine("ORDER BY semantic_link_id");
semanticLinkTable = DatabaseAccesserEcolog.GetResult(query.ToString());
for (int i = 0; i < semanticLinkTable.Rows.Count; i++)
{
ret.Add(new SemanticLink()
{
SemanticLinkId = (int)semanticLinkTable.Rows[i]["semantic_link_id"],
DriverId = (int)semanticLinkTable.Rows[i]["driver_id"],
Semantics = (string)semanticLinkTable.Rows[i]["semantics"],
});
}
return(ret);
}
public static List <Sensor> CreateSensorsAddAll()
{
var ret = new List <Sensor>();
DataTable sensorTable = new DataTable();
string query = "SELECT * FROM sensors";
sensorTable = DatabaseAccesserEcolog.GetResult(query);
ret.Add(new Sensor {
SensorId = 0, SensorModel = "All"
});
for (int i = 0; i < sensorTable.Rows.Count; i++)
{
ret.Add(new Sensor
{
SensorId = (int)sensorTable.Rows[i]["sensor_id"],
SensorModel = (string)sensorTable.Rows[i]["sensor_model"],
Brand = (sensorTable.Rows[i]["brand"] == DBNull.Value ? null : (string)sensorTable.Rows[i]["brand"]),
OsVersion = (sensorTable.Rows[i]["os_version"] == DBNull.Value ? 0 : (float)sensorTable.Rows[i]["os_version"]),
Ordinal = (sensorTable.Rows[i]["ordinal"] == DBNull.Value ? 0 : (int)sensorTable.Rows[i]["ordinal"])
});
}
return(ret);
}
public static ObservableCollection <Trip> SelectTrips()
{
DataTable tripsTable = new DataTable();
string query = "SELECT trip_id, driver_id, car_id, sensor_id, start_time, end_time, consumed_energy, trip_direction FROM trips";
tripsTable = DatabaseAccesserEcolog.GetResult(query);
var trips = new ObservableCollection <Trip>();
for (int i = 0; i < tripsTable.Rows.Count; i++)
{
trips.Add(new Trip
{
TripId = (int)tripsTable.Rows[i]["trip_id"],
DriverId = (int)tripsTable.Rows[i]["driver_id"],
CarId = (int)tripsTable.Rows[i]["car_id"],
SensorId = (int)tripsTable.Rows[i]["sensor_id"],
StartTime = (DateTime)tripsTable.Rows[i]["start_time"],
EndTime = (DateTime)tripsTable.Rows[i]["end_time"],
ConsumedEnergy = (tripsTable.Rows[i]["consumed_energy"] == DBNull.Value ? 0 : (float)tripsTable.Rows[i]["consumed_energy"]),
TripDirection = (tripsTable.Rows[i]["trip_direction"] == DBNull.Value ? null : (string)tripsTable.Rows[i]["trip_direction"])
});
}
return(trips);
}
public static List <GraphEcolog> ExtractGraphEcolog(int tripId)
{
var ret = new List <GraphEcolog>();
DataTable ecologTable = new DataTable();
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT");
query.AppendLine(" jst,");
query.AppendLine(" latitude,");
query.AppendLine(" longitude,");
query.AppendLine(" speed,");
query.AppendLine(" terrain_altitude,");
query.AppendLine(" longitudinal_acc,");
query.AppendLine(" CASE WHEN consumed_electric_energy > 0 THEN energy_by_air_resistance * 3600 ELSE 0 END AS energy_by_air_resistance_plus,");
query.AppendLine(" CASE WHEN consumed_electric_energy <= 0 THEN energy_by_air_resistance * 3600 ELSE 0 END AS energy_by_air_resistance_minus,");
query.AppendLine(" CASE WHEN consumed_electric_energy > 0 THEN energy_by_rolling_resistance * 3600 ELSE 0 END AS energy_by_rolling_resistance_plus,");
query.AppendLine(" CASE WHEN consumed_electric_energy <= 0 THEN energy_by_rolling_resistance * 3600 ELSE 0 END AS energy_by_rolling_resistance_minus,");
query.AppendLine(" CASE WHEN energy_by_climbing_resistance > 0 THEN energy_by_climbing_resistance * 3600 ELSE 0 END AS energy_by_climbing_resistance,");
query.AppendLine(" CASE WHEN consumed_electric_energy > 0 THEN energy_by_acc_resistance * 3600 ELSE 0 END AS energy_by_acc_resistance,");
query.AppendLine(" convert_loss * 3600 AS convert_loss,");
query.AppendLine(" CASE WHEN consumed_electric_energy <= 0 THEN regene_loss * 3600 ELSE 0 END AS regene_loss,");
query.AppendLine(" CASE WHEN consumed_electric_energy <= 0 THEN regene_energy * 3600 ELSE 0 END AS regene_energy,");
query.AppendLine(" lost_energy * 3600 AS lost_energy,");
query.AppendLine(" consumed_electric_energy * 3600 AS consumed_electric_energy,");
query.AppendLine(" link_id");
query.AppendLine("FROM ecolog");
query.AppendLine("WHERE trip_id = " + tripId);
query.AppendLine("ORDER BY jst ASC");
ecologTable = DatabaseAccesserEcolog.GetResult(query.ToString());
for (int i = 0; i < ecologTable.Rows.Count; i++)
{
ret.Add(new GraphEcolog()
{
Jst = (DateTime)ecologTable.Rows[i]["jst"],
Latitude = (double)ecologTable.Rows[i]["latitude"],
Longitude = (double)ecologTable.Rows[i]["longitude"],
Speed = (float)ecologTable.Rows[i]["speed"],
TerrainAltitude = (float)ecologTable.Rows[i]["terrain_altitude"],
LongitudinalAcc = (ecologTable.Rows[i]["longitudinal_acc"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["longitudinal_acc"]),
EnergyByAirResistancePlus = (float)ecologTable.Rows[i]["energy_by_air_resistance_plus"],
EnergyByAirResistanceMinus = (float)ecologTable.Rows[i]["energy_by_air_resistance_minus"],
EnergyByRollingResistancePlus = (float)ecologTable.Rows[i]["energy_by_rolling_resistance_plus"],
EnergyByRollingResistanceMinus = (float)ecologTable.Rows[i]["energy_by_rolling_resistance_minus"],
EnergyByClimbingResistance = (float)ecologTable.Rows[i]["energy_by_climbing_resistance"],
EnergyByAccResistance = (ecologTable.Rows[i]["energy_by_acc_resistance"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["energy_by_acc_resistance"]),
ConvertLoss = (float)ecologTable.Rows[i]["convert_loss"],
RegeneLoss = (float)ecologTable.Rows[i]["regene_loss"],
RegeneEnergy = (float)ecologTable.Rows[i]["regene_energy"],
LostEnergy = (float)ecologTable.Rows[i]["lost_energy"],
ConsumedElectricEnergy = (float)ecologTable.Rows[i]["consumed_electric_energy"],
LinkId = (ecologTable.Rows[i]["link_id"] == DBNull.Value ? null : (string)ecologTable.Rows[i]["link_id"]),
});
}
return(ret);
}
public static List <Car> CreateCars()
{
var ret = new List <Car>();
DataTable carTable = new DataTable();
string query = "SELECT * FROM cars";
carTable = DatabaseAccesserEcolog.GetResult(query);
for (int i = 0; i < carTable.Rows.Count; i++)
{
ret.Add(new Car()
{
CarId = (int)carTable.Rows[i]["car_id"],
Model = (string)carTable.Rows[i]["model"],
Battery = (float)carTable.Rows[i]["battery"],
Weight = (float)carTable.Rows[i]["weight"],
TireRadius = (float)carTable.Rows[i]["tire_radius"],
ReductionRatio = (float)carTable.Rows[i]["reduction_ratio"],
CdValue = (float)carTable.Rows[i]["cd_value"],
FrontalProjectedArea = (float)carTable.Rows[i]["frontal_projected_area"]
});
}
return(ret);
}
public static List <Ecolog> ExtractEcolog(int tripId)
{
var ret = new List <Ecolog>();
DataTable ecologTable = new DataTable();
string query = "SELECT * FROM ecolog WHERE trip_id = " + tripId + " ORDER BY jst ASC";
ecologTable = DatabaseAccesserEcolog.GetResult(query);
for (int i = 0; i < ecologTable.Rows.Count; i++)
{
ret.Add(new Ecolog()
{
TripId = (int)ecologTable.Rows[i]["trip_id"],
DriverId = (int)ecologTable.Rows[i]["driver_id"],
CarId = (int)ecologTable.Rows[i]["car_id"],
SensorId = (int)ecologTable.Rows[i]["sensor_id"],
Jst = (DateTime)ecologTable.Rows[i]["jst"],
Latitude = (double)ecologTable.Rows[i]["latitude"],
Longitude = (double)ecologTable.Rows[i]["longitude"],
Speed = (float)ecologTable.Rows[i]["speed"],
Heading = (float)ecologTable.Rows[i]["heading"],
DistanceDifference = (float)ecologTable.Rows[i]["distance_difference"],
TerrainAltitude = (float)ecologTable.Rows[i]["terrain_altitude"],
TerrainAltitudeDifference = (float)ecologTable.Rows[i]["terrain_altitude_difference"],
LongitudinalAcc = (ecologTable.Rows[i]["longitudinal_acc"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["longitudinal_acc"]),
LateralAcc = (ecologTable.Rows[i]["lateral_acc"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["lateral_acc"]),
VerticalAcc = (ecologTable.Rows[i]["vertical_acc"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["vertical_acc"]),
EnergyByAirResistance = (float)ecologTable.Rows[i]["energy_by_air_resistance"],
EnergyByRollingResistance = (float)ecologTable.Rows[i]["energy_by_rolling_resistance"],
EnergyByClimbingResistance = (float)ecologTable.Rows[i]["energy_by_climbing_resistance"],
EnergyByAccResistance = (ecologTable.Rows[i]["energy_by_acc_resistance"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["energy_by_acc_resistance"]),
ConvertLoss = (float)ecologTable.Rows[i]["convert_loss"],
RegeneLoss = (float)ecologTable.Rows[i]["regene_loss"],
RegeneEnergy = (float)ecologTable.Rows[i]["regene_energy"],
LostEnergy = (float)ecologTable.Rows[i]["lost_energy"],
Efficiency = (float)ecologTable.Rows[i]["efficiency"],
ConsumedElectricEnergy = (float)ecologTable.Rows[i]["consumed_electric_energy"],
LostEnergyByWellToWheel = (ecologTable.Rows[i]["lost_energy_by_well_to_wheel"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["lost_energy_by_well_to_wheel"]),
ConsumedFuel = (ecologTable.Rows[i]["consumed_fuel"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["consumed_fuel"]),
ConsumedFuelByWellToWheel = (ecologTable.Rows[i]["consumed_fuel_by_well_to_wheel"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["consumed_fuel_by_well_to_wheel"]),
EnergyByEquipment = (ecologTable.Rows[i]["energy_by_equipment"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["energy_by_equipment"]),
EnergyByCooling = (ecologTable.Rows[i]["energy_by_cooling"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["energy_by_cooling"]),
EnergyByHeating = (ecologTable.Rows[i]["energy_by_heating"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["energy_by_heating"]),
TripDirection = (ecologTable.Rows[i]["trip_direction"] == DBNull.Value ? null : (string)ecologTable.Rows[i]["trip_direction"]),
MeshId = (ecologTable.Rows[i]["mesh_id"] == DBNull.Value ? -1 : (int)ecologTable.Rows[i]["mesh_id"]),
LinkId = (ecologTable.Rows[i]["link_id"] == DBNull.Value ? null : (string)ecologTable.Rows[i]["link_id"]),
RoadTheta = (ecologTable.Rows[i]["road_theta"] == DBNull.Value ? -1 : (float)ecologTable.Rows[i]["road_theta"])
});
}
return(ret);
}
public static ObservableCollection <Trip> SelectTrips(int driverId, int carId, int sensorId, string direction)
{
DataTable tripsTable = new DataTable();
string query = "SELECT trip_id, driver_id, car_id, sensor_id, start_time, end_time, consumed_energy, trip_direction FROM trips WHERE trip_id >= 0";
if (driverId != 0)
{
query = query + " AND driver_id = " + driverId;
}
if (carId != 0)
{
query = query + " AND car_id = " + carId;
}
if (sensorId != 0)
{
query = query + " AND sensor_Id = " + sensorId;
}
if (direction != "All")
{
query = query + " AND trip_direction = '" + direction + "'";
}
tripsTable = DatabaseAccesserEcolog.GetResult(query);
var trips = new ObservableCollection <Trip>();
for (int i = 0; i < tripsTable.Rows.Count; i++)
{
trips.Add(new Trip
{
TripId = (int)tripsTable.Rows[i]["trip_id"],
DriverId = (int)tripsTable.Rows[i]["driver_id"],
CarId = (int)tripsTable.Rows[i]["car_id"],
SensorId = (int)tripsTable.Rows[i]["sensor_id"],
StartTime = (DateTime)tripsTable.Rows[i]["start_time"],
EndTime = (DateTime)tripsTable.Rows[i]["end_time"],
ConsumedEnergy = (tripsTable.Rows[i]["consumed_energy"] == DBNull.Value ? 0 : (float)tripsTable.Rows[i]["consumed_energy"]),
TripDirection = (tripsTable.Rows[i]["trip_direction"] == DBNull.Value ? null : (string)tripsTable.Rows[i]["trip_direction"])
});
}
return(trips);
}
public static SemanticHistogramDatum GetNormalizedStackedTimeInstance(SemanticLink semanticLink, TripDirection direction)
{
SemanticHistogramDatum datum = new SemanticHistogramDatum();
datum.SemanticLink = semanticLink;
datum.Direction = direction;
datum.Sum = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeSumOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field <int>("Sum"))
.ElementAt(0);
datum.Number = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeNumberOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field <int>("Number"))
.ElementAt(0);
DataTable table = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeStackOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')");
datum.HistogramData = new List <LevelAndValue>();
foreach (DataRow row in table.Rows)
{
datum.HistogramData.Add(new LevelAndValue()
{
Level = row.Field <int>("Level"), Value = row.Field <int>("Stack") / datum.Number
});
}
datum.MaxLevel = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeMaxOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field <int>("Max"))
.ElementAt(0);
datum.MinLevel = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeMinOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field <int>("Min"))
.ElementAt(0);
datum.ClassWidth = (int)((datum.MaxLevel - datum.MinLevel) / 10);
return(datum);
}
public static PhotographicImage CreatePhotographicImage(int tripId, DateTime jst)
{
StringBuilder query = new StringBuilder();
query.AppendLine("SELECT");
query.AppendLine(" picture.driver_id,");
query.AppendLine(" picture.car_id,");
query.AppendLine(" picture.sensor_id,");
query.AppendLine(" picture.jst,");
query.AppendLine(" case when picture is null then LAG(picture) over (order by selected_ecolog.jst) else picture end as picture");
query.AppendLine("FROM (");
query.AppendLine(" SELECT ecolog.*");
query.AppendLine(" FROM ecolog as ecolog");
query.AppendLine(" WHERE ecolog.trip_id = " + tripId);
query.AppendLine(" AND jst = '" + jst + "'");
query.AppendLine(" ) AS selected_ecolog");
query.AppendLine("LEFT JOIN corrected_picture AS picture");
query.AppendLine("ON selected_ecolog.driver_id = picture.driver_id");
query.AppendLine(" AND selected_ecolog.jst = picture.jst");
query.AppendLine(" AND picture.sensor_id = 19");
query.AppendLine("ORDER BY selected_ecolog.jst");
DataTable pictureTable;
pictureTable = DatabaseAccesserEcolog.GetResult(query.ToString());
return(new PhotographicImage()
{
DriverId = (pictureTable.Rows[0]["driver_id"] == DBNull.Value ? -1 : (int)pictureTable.Rows[0]["driver_id"]),
CarId = (pictureTable.Rows[0]["car_id"] == DBNull.Value ? -1 : (int)pictureTable.Rows[0]["car_id"]),
SensorId = (pictureTable.Rows[0]["sensor_id"] == DBNull.Value ? -1 : (int)pictureTable.Rows[0]["sensor_id"]),
Jst = (pictureTable.Rows[0]["jst"] == DBNull.Value ? new DateTime() : (DateTime)pictureTable.Rows[0]["jst"]),
ImageSource = (pictureTable.Rows[0]["picture"] == DBNull.Value ? new Byte[0] : (Byte[])pictureTable.Rows[0]["picture"])
});
}
private async void createPlotModel()
{
await Task.Run(() =>
{
this.dataTable = DatabaseAccesserEcolog.GetResult(createQuery());
});
calculateEnergyParameter();
calculateTimeParameter();
var plotModel = new PlotModel();
plotModel.Subtitle = "SemananticLink: " + semantciLink.Semantics + ", Direction: " + direction.Direction;
plotModel.Title = "Semantic Matrix";
var linearColorAxis = new LinearColorAxis();
linearColorAxis.HighColor = OxyColors.Gray;
linearColorAxis.LowColor = OxyColors.Black;
linearColorAxis.Position = AxisPosition.Right;
plotModel.Axes.Add(linearColorAxis);
var linearAxis1 = new LinearAxis();
linearAxis1.Title = "Time";
linearAxis1.Unit = "s";
linearAxis1.Position = AxisPosition.Bottom;
plotModel.Axes.Add(linearAxis1);
var linearAxis2 = new LinearAxis();
linearAxis2.Title = "Lost Energy";
linearAxis2.Unit = "kWh";
plotModel.Axes.Add(linearAxis2);
var heatMapSeries1 = new HeatMapSeries();
heatMapSeries1.LabelFormatString = "0";
heatMapSeries1.X0 = minExcludedTime;
heatMapSeries1.X1 = maxExcludedTime;
heatMapSeries1.Y0 = minExcludedEnergy;
heatMapSeries1.Y1 = maxExcludedEnergy;
heatMapSeries1.LabelFontSize = 0.2;
heatMapSeries1.Data = new Double[classNumber + 1, classNumber + 1];
int count = 0; // for debug
double preTimeLevel = 0;
double currentTimeLevel = minExcludedTime;
for (int i = 0; i < classNumber + 1; i++)
{
double preEnergyLevel = 0;
double currentEnergyLevel = minExcludedEnergy;
for (int j = 0; j < classNumber + 1; j++)
{
heatMapSeries1.Data[i, j] = dataTable.AsEnumerable()
.Where(x => x.Field <double>("SumLostEnergy") > preEnergyLevel)
.Where(x => x.Field <double>("SumLostEnergy") <= currentEnergyLevel)
.Where(x => x.Field <int>("TIME") > preTimeLevel)
.Where(x => x.Field <int>("TIME") <= currentTimeLevel).Count();
count += (int)heatMapSeries1.Data[i, j];
if (j == 0)
{
preEnergyLevel = minExcludedEnergy;
}
else
{
preEnergyLevel += classWidthEnergy;
}
currentEnergyLevel += classWidthEnergy;
}
if (i == 0)
{
preTimeLevel = minExcludedTime;
}
else
{
preTimeLevel += classWidthTime;
}
currentTimeLevel += classWidthTime;
}
plotModel.Series.Add(heatMapSeries1);
this.ProgressBarVisibility = System.Windows.Visibility.Collapsed;
this.PlotModel = plotModel;
}
public static SemanticHistogramDatum GetEnergyInstance(SemanticLink semanticLink, TripDirection direction, int sensorID)
{
SemanticHistogramDatum datum = new SemanticHistogramDatum();
datum.SemanticLink = semanticLink;
datum.Direction = direction;
string query = "WITH SelectedSemanticLink ";
query += "AS ( ";
query += " SELECT * ";
query += " FROM SEMANTIC_LINKS ";
query += " WHERE SEMANTIC_LINK_ID = "+ semanticLink.SemanticLinkId;
query += " ) ";
query += " ,ExtractedTrips ";
query += "AS ( ";
query += " SELECT Sensor"+ sensorID + ".TRIP_ID ";
query += " FROM TRIPS Sensor12 ";
query += " INNER JOIN TRIPS Sensor24 ON DATEPART(YEAR, Sensor12.START_TIME) = DATEPART(YEAR, Sensor24.START_TIME) ";
query += " AND DATEPART(MONTH, Sensor12.START_TIME) = DATEPART(MONTH, Sensor24.START_TIME) ";
query += " AND DATEPART(DAYOFYEAR, Sensor12.START_TIME) = DATEPART(DAYOFYEAR, Sensor24.START_TIME) ";
query += " AND DATEPART(HOUR, Sensor12.START_TIME) = DATEPART(HOUR, Sensor24.START_TIME) ";
query += " AND DATEPART(MINUTE, Sensor12.START_TIME) = DATEPART(MINUTE, Sensor24.START_TIME) ";
query += " WHERE Sensor12.SENSOR_ID = 12 ";
query += " AND Sensor24.SENSOR_ID = 24 ";
query += " ) ";
query += " ,ExtractedEcolog ";
query += "AS ( ";
query += " SELECT ECOLOG.TRIP_ID ";
query += " ,SUM(LOST_ENERGY) AS SumLostEnergy ";
query += " FROM ECOLOG ";
query += " INNER JOIN ExtractedTrips ON ECOLOG.TRIP_ID = ExtractedTrips.TRIP_ID ";
query += " INNER JOIN SelectedSemanticLink ON ECOLOG.LINK_ID = SelectedSemanticLink.LINK_ID ";
query += " WHERE ECOLOG.DRIVER_ID = 1 ";
query += " AND TRIP_DIRECTION = '"+ direction.Direction + "' ";
query += " AND (SENSOR_ID = "+ sensorID + ") ";
query += " GROUP BY ECOLOG.TRIP_ID ";
query += " ) ";
/*
* query += " ,NormalizedEcolog ";
* query += "AS ( ";
* query += " SELECT SumLostEnergy ";
* query += " FROM ExtractedEcolog ";
* query += " WHERE SumLostEnergy <= Q3 + 1.5 * (Q3 - Q1) ";
* query += " AND SumLostEnergy >= Q1 - 1.5 * (Q3 - Q1) ";
* query += " ) ";
* query += " ,NormalizedEcologClassWidth ";
* query += "AS ( ";
* query += " SELECT (MAX(SumLostEnergy) - MIN(SumLostEnergy)) / 10 AS ClassWidth ";
* query += " FROM NormalizedEcolog ";
* query += " ) ";
*/
query += "SELECT TOP 100 CEILING(SumLostEnergy / 0.003564219668623985) * 0.003564219668623985 AS LEVEL ";
query += " ,COUNT(*) AS Number ";
query += "FROM ExtractedEcolog ";
// query += " ,NormalizedEcologClassWidth ";
query += "GROUP BY CEILING(SumLostEnergy / 0.003564219668623985) * 0.003564219668623985 ";
query += "ORDER BY CEILING(SumLostEnergy / 0.003564219668623985) * 0.003564219668623985 ";
DataTable table = DatabaseAccesserEcolog.GetResult(query);
datum.HistogramData = new List <LevelAndValue>();
foreach (DataRow row in table.Rows)
{
datum.HistogramData.Add(new LevelAndValue()
{
Level = row.Field <double>("Level"), Value = row.Field <int>("Number")
});
}
/*
* query = null;
*
* query = "WITH SelectedSemanticLink ";
* query += "AS ( ";
* query += " SELECT * ";
* query += " FROM SEMANTIC_LINKS ";
* query += " WHERE SEMANTIC_LINK_ID = " + semanticLink.SemanticLinkId;
* query += " ) ";
* query += " ,ExtractedTrips ";
* query += "AS ( ";
* query += " SELECT Sensor" + sensorID + ".TRIP_ID ";
* query += " FROM TRIPS Sensor12 ";
* query += " INNER JOIN TRIPS Sensor24 ON DATEPART(YEAR, Sensor12.START_TIME) = DATEPART(YEAR, Sensor24.START_TIME) ";
* query += " AND DATEPART(MONTH, Sensor12.START_TIME) = DATEPART(MONTH, Sensor24.START_TIME) ";
* query += " AND DATEPART(DAYOFYEAR, Sensor12.START_TIME) = DATEPART(DAYOFYEAR, Sensor24.START_TIME) ";
* query += " AND DATEPART(HOUR, Sensor12.START_TIME) = DATEPART(HOUR, Sensor24.START_TIME) ";
* query += " AND DATEPART(MINUTE, Sensor12.START_TIME) = DATEPART(MINUTE, Sensor24.START_TIME) ";
* query += " WHERE Sensor12.SENSOR_ID = 12 ";
* query += " AND Sensor24.SENSOR_ID = 24 ";
* query += " ) ";
* query += " ,ExtractedEcolog ";
* query += "AS ( ";
* query += " SELECT ECOLOG.TRIP_ID ";
* query += " ,SUM(LOST_ENERGY) AS SumLostEnergy ";
* query += " ,PERCENTILE_DISC(0.25) WITHIN ";
* query += " GROUP ( ";
* query += " ORDER BY SUM(LOST_ENERGY) ";
* query += " ) OVER (PARTITION BY NULL) AS Q1 ";
* query += " ,PERCENTILE_DISC(0.75) WITHIN ";
* query += " GROUP ( ";
* query += " ORDER BY SUM(LOST_ENERGY) ";
* query += " ) OVER (PARTITION BY NULL) AS Q3 ";
* query += " FROM ECOLOG ";
* query += " INNER JOIN ExtractedTrips ON ECOLOG.TRIP_ID = ExtractedTrips.TRIP_ID ";
* query += " INNER JOIN SelectedSemanticLink ON ECOLOG.LINK_ID = SelectedSemanticLink.LINK_ID ";
* query += " WHERE ECOLOG.DRIVER_ID = 1 ";
* query += " AND TRIP_DIRECTION = '" + direction.Direction + "' ";
* query += " AND SENSOR_ID = " + sensorID;
* query += " GROUP BY ECOLOG.TRIP_ID ";
* query += " ) ";
* query += "SELECT TRIP_ID AS TripID ";
* query += " ,SumLostEnergy AS Min ";
* query += "FROM ExtractedEcolog ";
* query += "WHERE SumLostEnergy = ( ";
* query += " SELECT MIN(SumLostEnergy) AS Min ";
* query += " FROM ExtractedEcolog ";
* query += " WHERE SumLostEnergy >= Q1 - 1.5 * (Q3 - Q1) ";
* query += " ) ";
*
* datum.MinLevel = DatabaseAccesserEcolog.GetResult(query)
* .AsEnumerable()
* .Select(x => x.Field<double>("Min"))
* .ElementAt(0);
*
* query = null;
*
* query = "WITH SelectedSemanticLink ";
* query += "AS ( ";
* query += " SELECT * ";
* query += " FROM SEMANTIC_LINKS ";
* query += " WHERE SEMANTIC_LINK_ID = " + semanticLink.SemanticLinkId;
* query += " ) ";
* query += " ,ExtractedTrips ";
* query += "AS ( ";
* query += " SELECT Sensor" + sensorID + ".TRIP_ID ";
* query += " FROM TRIPS Sensor12 ";
* query += " INNER JOIN TRIPS Sensor24 ON DATEPART(YEAR, Sensor12.START_TIME) = DATEPART(YEAR, Sensor24.START_TIME) ";
* query += " AND DATEPART(MONTH, Sensor12.START_TIME) = DATEPART(MONTH, Sensor24.START_TIME) ";
* query += " AND DATEPART(DAYOFYEAR, Sensor12.START_TIME) = DATEPART(DAYOFYEAR, Sensor24.START_TIME) ";
* query += " AND DATEPART(HOUR, Sensor12.START_TIME) = DATEPART(HOUR, Sensor24.START_TIME) ";
* query += " AND DATEPART(MINUTE, Sensor12.START_TIME) = DATEPART(MINUTE, Sensor24.START_TIME) ";
* query += " WHERE Sensor12.SENSOR_ID = 12 ";
* query += " AND Sensor24.SENSOR_ID = 24 ";
* query += " ) ";
* query += " ,ExtractedEcolog ";
* query += "AS ( ";
* query += " SELECT ECOLOG.TRIP_ID ";
* query += " ,SUM(LOST_ENERGY) AS SumLostEnergy ";
* query += " ,PERCENTILE_DISC(0.25) WITHIN ";
* query += " GROUP ( ";
* query += " ORDER BY SUM(LOST_ENERGY) ";
* query += " ) OVER (PARTITION BY NULL) AS Q1 ";
* query += " ,PERCENTILE_DISC(0.75) WITHIN ";
* query += " GROUP ( ";
* query += " ORDER BY SUM(LOST_ENERGY) ";
* query += " ) OVER (PARTITION BY NULL) AS Q3 ";
* query += " FROM ECOLOG ";
* query += " INNER JOIN ExtractedTrips ON ECOLOG.TRIP_ID = ExtractedTrips.TRIP_ID ";
* query += " INNER JOIN SelectedSemanticLink ON ECOLOG.LINK_ID = SelectedSemanticLink.LINK_ID ";
* query += " WHERE ECOLOG.DRIVER_ID = 1 ";
* query += " AND TRIP_DIRECTION = '" + direction.Direction + "' ";
* query += " AND SENSOR_ID = " + sensorID;
* query += " GROUP BY ECOLOG.TRIP_ID ";
* query += " ) ";
* query += "SELECT TRIP_ID AS TripID ";
* query += " ,SumLostEnergy AS Max ";
* query += "FROM ExtractedEcolog ";
* query += "WHERE SumLostEnergy = ( ";
* query += " SELECT MAX(SumLostEnergy) AS Max ";
* query += " FROM ExtractedEcolog ";
* query += " WHERE SumLostEnergy <= Q3 + 1.5 * (Q3 - Q1) ";
* query += " ) ";
*
* datum.MaxLevel = DatabaseAccesserEcolog.GetResult(query)
* .AsEnumerable()
* .Select(x => x.Field<double>("Max"))
* .ElementAt(0);
*/
/*
* datum.MedianLevel = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedEnergyMedianOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
* .AsEnumerable()
* .Select(x => x.Field<double>("Median"))
* .ElementAt(0);
*
* datum.AvgLevel = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedEnergyAvgOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
* .AsEnumerable()
* .Select(x => x.Field<double>("Avg"))
* .ElementAt(0);
*/
// datum.ModeLevel = datum.HistogramData.First(v => v.Value.Equals(datum.HistogramData.Select(m => m.Value).Max())).Level;
// datum.HistogramData.Max(x => x.Level);
// datum.ClassWidth = (datum.MaxLevel - datum.MinLevel) / 10;
datum.ClassWidth = 0.003564219668623985;
/*
* datum.UnderModeData = datum.HistogramData
* .Where(v => v.Value <= datum.HistogramData.Max(x => x.Value) * 0.75)
* .Where(v => v.Level < datum.ModeLevel)
* .ToList();
*
* datum.ModeData = datum.HistogramData
* .Where(v => v.Value >= datum.HistogramData.Max(x => x.Value) * 0.75)
* .ToList();
*
* datum.UpperModeData = datum.HistogramData
* .Where(v => v.Value <= datum.HistogramData.Max(x => x.Value) * 0.75)
* .Where(v => v.Level > datum.ModeLevel)
* .ToList();
*
* datum.DistUnderMode = datum.UnderModeData.Sum(x => x.Value) * 100 / datum.HistogramData.Sum(x => x.Value);
* datum.DistMode = datum.ModeData.Sum(x => x.Value) * 100 / datum.HistogramData.Sum(x => x.Value);
* datum.DistUpperMode = datum.UpperModeData.Sum(x => x.Value) * 100 / datum.HistogramData.Sum(x => x.Value);
*
* datum.CompMinMax = datum.MaxLevel * 100 / datum.MinLevel;
* datum.CompMinMode = datum.ModeLevel * 100 / datum.MinLevel;
* datum.CompModeMax = datum.MaxLevel * 100 / datum.ModeLevel;
*/
return(datum);
}
public static SemanticHistogramDatum GetDistanceNormalizedTimeInstance(SemanticLink semanticLink, TripDirection direction)
{
SemanticHistogramDatum datum = new SemanticHistogramDatum();
datum.SemanticLink = semanticLink;
datum.Direction = direction;
DataTable table = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeHistogramOfSemanticLinkWithDistance(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')");
datum.HistogramData = new List <LevelAndValue>();
foreach (DataRow row in table.Rows)
{
datum.HistogramData.Add(new LevelAndValue()
{
Level = row.Field <int>("Level"), Value = row.Field <int>("Number")
});
}
datum.MaxLevel = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeMaxOfSemanticLinkWithDistance(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field <double>("Max"))
.ElementAt(0);
datum.MinLevel = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeMinOfSemanticLinkWithDistance(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field <double>("Min"))
.ElementAt(0);
datum.MedianLevel = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeMedianOfSemanticLinkWithDistance(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field <double>("Median"))
.ElementAt(0);
datum.AvgLevel = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeAvgOfSemanticLinkWithDistance(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field <double>("Avg"))
.ElementAt(0);
datum.ModeLevel = datum.HistogramData.First(v => v.Value.Equals(datum.HistogramData.Select(m => m.Value).Max())).Level;
datum.HistogramData.Max(x => x.Level);
datum.ClassWidth = (int)((datum.MaxLevel - datum.MinLevel) / 10);
datum.UnderModeData = datum.HistogramData
.Where(v => v.Value <= datum.HistogramData.Max(x => x.Value) * 0.75)
.Where(v => v.Level < datum.ModeLevel)
.ToList();
datum.ModeData = datum.HistogramData
.Where(v => v.Value >= datum.HistogramData.Max(x => x.Value) * 0.75)
.ToList();
datum.UpperModeData = datum.HistogramData
.Where(v => v.Value <= datum.HistogramData.Max(x => x.Value) * 0.75)
.Where(v => v.Level > datum.ModeLevel)
.ToList();
datum.DistUnderMode = datum.UnderModeData.Sum(x => x.Value) * 100 / datum.HistogramData.Sum(x => x.Value);
datum.DistMode = datum.ModeData.Sum(x => x.Value) * 100 / datum.HistogramData.Sum(x => x.Value);
datum.DistUpperMode = datum.UpperModeData.Sum(x => x.Value) * 100 / datum.HistogramData.Sum(x => x.Value);
datum.CompMinMax = datum.MaxLevel * 100 / datum.MinLevel;
datum.CompMinMode = datum.ModeLevel * 100 / datum.MinLevel;
datum.CompModeMax = datum.MaxLevel * 100 / datum.ModeLevel;
return(datum);
}
