public SemanticDetailWindowViewModel(SemanticLink link, TripDirection direction)
{
this.SemanticLink = link;
this.TripDirection = direction;
Initialize();
}
public DetailTripDetailPageViewModel(SemanticLink link, TripDirection direction, InvokeScript script)
{
this.SemanticLink = link;
this.TripDirection = direction;
this.invokeScript = script;
Initialize();
}
public DetailHeatMapPageViewModel(SemanticLink semanticLink, TripDirection direction, List<Driver> drivers, List<Car> cars, List<Sensor> sensors)
{
this.semantciLink = semanticLink;
this.direction = direction;
this.drivers = drivers;
this.cars = cars;
this.sensors = sensors;
this.ProgressBarVisibility = System.Windows.Visibility.Visible;
createPlotModel();
}
private void showCompareDialog(SemanticLink semanticLink)
{
var dialog = new MainPageCompareDialog
{
Message = { Text = semanticLink.Semantics },
TripDirection = this.TripDirection,
SelectedSemanticLinks = this.SelectedSemanticLinks.ToList(),
SemanticLink = semanticLink,
ViewModel = this
};
DialogHost.Show(dialog, "RootDialog");
}
private void showDetailDialog(SemanticLink semanticLink)
{
var dialog = new MainPageShowDetailDialog
{
Message = { Text = semanticLink.Semantics },
TripDirection = this.TripDirection,
SemanticLink = semanticLink,
ViewModel = this
};
DialogHost.Show(dialog, "RootDialog");
}
public static SemanticHistogramDatum GetTimeInstance(SemanticLink semanticLink, TripDirection direction)
{
SemanticHistogramDatum datum = new SemanticHistogramDatum();
datum.SemanticLink = semanticLink;
datum.Direction = direction;
DataTable table = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeHistogramOfSemanticLink(" + 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 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.MedianLevel = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeMedianOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field<int>("Median"))
.ElementAt(0);
datum.AvgLevel = DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeAvgOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field<int>("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;
}
public static int GetTimeMinTripID(SemanticLink semanticLink, TripDirection direction)
{
return DatabaseAccesserEcolog.GetResult("SELECT * FROM funcNormalizedTimeMinOfSemanticLink(" + semanticLink.SemanticLinkId + ", '" + direction.Direction + "')")
.AsEnumerable()
.Select(x => x.Field<int>("TripID"))
.ElementAt(0);
}
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 GetStackedTimeInstance(SemanticLink semanticLink, TripDirection direction)
{
SemanticHistogramDatum datum = new SemanticHistogramDatum();
datum.SemanticLink = semanticLink;
datum.Direction = direction;
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.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);
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 List<Ecolog> ExtractEcolog(int tripId, SemanticLink semanticLink)
{
var ret = new List<Ecolog>();
DataTable ecologTable = new DataTable();
StringBuilder query = new StringBuilder();
query.AppendLine("WITH SelectedSemanticLink");
query.AppendLine("AS (");
query.AppendLine(" SELECT *");
query.AppendLine(" FROM SEMANTIC_LINKS");
query.AppendLine(" WHERE SEMANTIC_LINK_ID = " + semanticLink.SemanticLinkId);
query.AppendLine(" )");
query.AppendLine("SELECT *");
query.AppendLine("FROM ecolog");
query.AppendLine("INNER JOIN SelectedSemanticLink ON ecolog.link_id = SelectedSemanticLink.link_id");
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 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 DetailComparePageViewModel(SemanticLink link, TripDirection direction)
{
this.SelectedSemanticLink = link;
this.TripDirection = direction;
Initialize();
}
public static List <Ecolog> ExtractEcolog(int tripId, SemanticLink semanticLink)
{
var ret = new List <Ecolog>();
DataTable ecologTable = new DataTable();
StringBuilder query = new StringBuilder();
query.AppendLine("WITH SelectedSemanticLink");
query.AppendLine("AS (");
query.AppendLine(" SELECT *");
query.AppendLine(" FROM SEMANTIC_LINKS");
query.AppendLine(" WHERE SEMANTIC_LINK_ID = "+ semanticLink.SemanticLinkId);
query.AppendLine(" )");
query.AppendLine("SELECT *");
query.AppendLine("FROM ecolog");
query.AppendLine("INNER JOIN SelectedSemanticLink ON ecolog.link_id = SelectedSemanticLink.link_id");
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 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 DetailEnergyPageViewModel(SemanticLink link, TripDirection direction)
{
this.SemanticLink = link;
this.TripDirection = direction;
Initialize();
}
public static List<GraphEcolog> ExtractGraphEcolog(int tripId, SemanticLink link)
{
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(" ecolog.link_id");
query.AppendLine("FROM ecolog");
query.AppendLine("INNER JOIN semantic_links");
query.AppendLine(" ON ecolog.link_id = semantic_links.link_id");
query.AppendLine("WHERE trip_id = " + tripId);
query.AppendLine(" AND semantic_link_id = " + link.SemanticLinkId);
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;
}
