private StatisticRecord GetRecordForPiece(DataPiece piece)
{
using (var db = DatabaseUtility.OpenConnection()) {
var m = db.GetMapping <StatisticRecord>();
var existingRecord = (StatisticRecord)db.FindWithQuery(
m,
$"SELECT * FROM {m.TableName} WHERE {nameof(StatisticRecord.TrackId)} = ?",
piece.TrackId
);
if (existingRecord != null)
{
Log.Debug("Found existing track in DB, resuming data collection");
return(existingRecord);
}
else
{
Log.Debug("Creating new track record in DB");
var r = new StatisticRecord {
TrackId = piece.TrackId,
Start = piece.StartTimestamp,
End = piece.EndTimestamp,
LocationStartLatitude = piece.Latitude,
LocationStartLongitude = piece.Longitude,
LocationEndLatitude = piece.Latitude,
LocationEndLongitude = piece.Longitude,
Vehicle = piece.Vehicle,
Anchorage = piece.Anchorage,
NumberOfPeople = piece.NumberOfPeople
};
db.Insert(r);
return(r);
}
}
}
public void Reset()
{
_previous = null;
_record = null;
_ppeAccumulator = 0.0;
_ppeCount = 0;
if (_dumpWriter != null)
{
_dumpWriter.Dispose();
_dumpWriter = null;
}
}
/// <summary>
/// Complete session and flush out data to database.
/// </summary>
public void CompleteSession()
{
if (_dumpWriter != null)
{
_dumpWriter.Flush();
}
if (_ppeCount == 0)
{
Log.Debug("Completing session of 0 data pieces, ignoring");
return;
}
Log.Debug("Completing statistic collection session");
try {
// TODO: perform in background
// TODO: accumulate on DB record if record already stored (updates instead of insert)
using (var db = DatabaseUtility.OpenConnection()) {
var record = new StatisticRecord {
TrackId = _previous.TrackId,
Start = _tsStart,
End = _tsEnd,
MaxPpe = _ppeMax,
AvgPpe = (_ppeAccumulator / _ppeCount),
Bins = _ppeBins,
DistanceTraveled = _distance,
DataPieceCount = _ppeCount,
ElapsedTime = _elapsed
};
db.Insert(record);
}
}
catch (Exception ex) {
Log.Error(ex, "Failed to store statistics");
}
Log.Event("Recording.newStats", new Dictionary <string, string>()
{
{ "distance", _distance.ToString(CultureInfo.InvariantCulture) },
{ "dataPieces", _ppeCount.ToString(CultureInfo.InvariantCulture) },
{ "elapsedMinutes", _elapsed.TotalMinutes.ToString(CultureInfo.InvariantCulture) }
});
UserLog.Add(LogStrings.StatsRecorded, _distance, _ppeCount);
Reset();
}
public async Task <List <StatisticRecord> > GetStatistics(DateTime from, DateTime to, string mccList)
{
var retVal = new List <StatisticRecord>();
DateTime startDate = new DateTime(from.Year, from.Month, from.Day);
DateTime endDate = new DateTime(to.Year, to.Month, to.Day);
List <Sms> queryResult;
string[] mccListArray;
if (!string.IsNullOrEmpty(mccList))
{
mccListArray = mccList.Split(',');
queryResult = await _uow.Sms.GetAllAsync(e => e.DateTime >= from && e.DateTime < endDate.AddDays(1) && mccListArray.Contains(e.Mcc));
}
else
{
mccListArray = (await _uow.Countries.GetAllAsync()).Select(e => e.MobileCountryCode).ToArray();
queryResult = await _uow.Sms.GetAllAsync();
}
for (var date = startDate; date <= endDate; date = date.AddDays(1))
{
foreach (var mcc in mccListArray)
{
var recordResults = queryResult.Where(e => e.DateTime >= date && e.DateTime < date.AddDays(1) && e.Mcc == mcc);
var totalPrice = recordResults.Sum(e => e.Price);
var count = recordResults.Count();
StatisticRecord record = new StatisticRecord
{
Day = date,
Mcc = mcc,
TotalPrice = totalPrice,
Count = count,
PricePerSms = count == 0 ? 0 : totalPrice / count
};
retVal.Add(record);
}
}
return(retVal);
}
private async void QueryStatistics(bool onlyInExtent, bool onlyLargePop)
{
// Create definitions for each statistic to calculate.
StatisticDefinition statDefinitionAvgPop = new StatisticDefinition("POP", StatisticType.Average, "");
StatisticDefinition statDefinitionMinPop = new StatisticDefinition("POP", StatisticType.Minimum, "");
StatisticDefinition statDefinitionMaxPop = new StatisticDefinition("POP", StatisticType.Maximum, "");
StatisticDefinition statDefinitionSumPop = new StatisticDefinition("POP", StatisticType.Sum, "");
StatisticDefinition statDefinitionStdDevPop = new StatisticDefinition("POP", StatisticType.StandardDeviation, "");
StatisticDefinition statDefinitionVarPop = new StatisticDefinition("POP", StatisticType.Variance, "");
// Create a definition for count that includes an alias for the output.
StatisticDefinition statDefinitionCount = new StatisticDefinition("POP", StatisticType.Count, "CityCount");
// Add the statistics definitions to a list.
List <StatisticDefinition> statDefinitions = new List <StatisticDefinition>
{
statDefinitionAvgPop,
statDefinitionCount,
statDefinitionMinPop,
statDefinitionMaxPop,
statDefinitionSumPop,
statDefinitionStdDevPop,
statDefinitionVarPop
};
// Create the statistics query parameters, pass in the list of definitions.
StatisticsQueryParameters statQueryParams = new StatisticsQueryParameters(statDefinitions);
// If only using features in the current extent, set up the spatial filter for the statistics query parameters.
if (onlyInExtent)
{
// Get the current extent (envelope) from the map view.
Envelope currentExtent = _myMapView.GetCurrentViewpoint(ViewpointType.BoundingGeometry).TargetGeometry as Envelope;
// Set the statistics query parameters geometry with the current extent.
statQueryParams.Geometry = currentExtent;
// Set the spatial relationship to Intersects (which is the default).
statQueryParams.SpatialRelationship = SpatialRelationship.Intersects;
}
// If only evaluating the largest cities (over 5 million in population), set up an attribute filter.
if (onlyLargePop)
{
// Set a where clause to get the largest cities (could also use "POP_CLASS = '5,000,000 and greater'").
statQueryParams.WhereClause = "POP_RANK = 1";
}
try
{
// Execute the statistical query with these parameters and await the results.
StatisticsQueryResult statQueryResult = await _worldCitiesTable.QueryStatisticsAsync(statQueryParams);
// Get the first (only) StatisticRecord in the results.
StatisticRecord record = statQueryResult.FirstOrDefault();
// Make sure a record was returned.
if (record == null || record.Statistics.Count == 0)
{
ShowMessage("No result", "No results were returned.");
return;
}
// Display results.
ShowStatsList(record.Statistics);
}
catch (ArcGISWebException exception)
{
ShowMessage("There was a problem running the query.", exception.ToString());
}
}
/// <summary>
/// Collects a new piece of data.
/// </summary>
public void Collect(DataPiece piece)
{
if (_record != null && (_record.TrackId != piece.TrackId))
{
Log.Warning(new ArgumentException(nameof(piece.TrackId)), "Different track ID seen while collecting statistics");
CompleteSession();
}
if (_record == null)
{
_record = GetRecordForPiece(piece);
}
// Record set, update
_ppeAccumulator += piece.Ppe;
_ppeCount += 1;
_record.Bins[PpeMapper.GetBinIndex(piece.Ppe)] += 1;
if (piece.Ppe > _record.MaxPpe)
{
_record.MaxPpe = piece.Ppe;
}
_record.AvgPpe = _ppeAccumulator / _ppeCount;
_record.DataPieceCount = (int)_ppeCount;
_record.End = piece.EndTimestamp;
_record.ElapsedTime += (piece.EndTimestamp - piece.StartTimestamp);
_record.LocationEndLatitude = piece.Latitude;
_record.LocationEndLongitude = piece.Longitude;
if (_previous != null)
{
var traveledDistance = GeoHelper.DistanceBetweenPoints(
_previous.Latitude, _previous.Longitude,
piece.Latitude, piece.Longitude
) * DistanceFactor;
_record.DistanceTraveled += traveledDistance;
Log.Debug("Traveled {0:F3}km, count {1} {2:t}-{3:t}",
traveledDistance, _ppeCount, _record.Start, _record.End);
}
_previous = piece;
// Flush every few minutes
if (_ppeCount > 0 && (_ppeCount % FlushIntervalSize == 0))
{
Flush();
}
// Dump data
// Generates approximately 89 bytes per measurement (~313 KB/hour)
if (_dumpWriter == null)
{
var outputFilepath = FileNaming.GetDataTrackFilepath(piece.TrackId);
Log.Debug("Appending to file {0}", outputFilepath);
var dumpStream = FileOperations.AppendFile(outputFilepath);
_dumpWriter = new StreamWriter(dumpStream);
_dumpWriter.WriteLine("# SRS Track {0:N} {1:u}", piece.TrackId, DateTime.Now);
_dumpWriter.WriteLine("# Start,End,Lat,Lng,PPE,PPEx,PPEy,PPEz,Speed,Bearing,Accuracy");
}
_dumpWriter.WriteLine(
string.Format(CultureInfo.InvariantCulture, "{0},{1},{2:F5},{3:F5},{4:F3},{5:F2},{6:F2},{7:F2},{8:F1},{9:D},{10:D}",
piece.StartTimestamp.Ticks,
piece.EndTimestamp.Ticks,
piece.Latitude,
piece.Longitude,
piece.Ppe,
piece.PpeX,
piece.PpeY,
piece.PpeZ,
piece.Speed,
(int)piece.Bearing,
piece.Accuracy
)
);
Log.Debug("Data piece collected");
}
private async void OnExecuteStatisticsQueryClicked(object sender, EventArgs e)
{
// Create definitions for each statistic to calculate
StatisticDefinition statDefinitionAvgPop = new StatisticDefinition("POP", StatisticType.Average, "");
StatisticDefinition statDefinitionMinPop = new StatisticDefinition("POP", StatisticType.Minimum, "");
StatisticDefinition statDefinitionMaxPop = new StatisticDefinition("POP", StatisticType.Maximum, "");
StatisticDefinition statDefinitionSumPop = new StatisticDefinition("POP", StatisticType.Sum, "");
StatisticDefinition statDefinitionStdDevPop = new StatisticDefinition("POP", StatisticType.StandardDeviation, "");
StatisticDefinition statDefinitionVarPop = new StatisticDefinition("POP", StatisticType.Variance, "");
// Create a definition for count that includes an alias for the output
StatisticDefinition statDefinitionCount = new StatisticDefinition("POP", StatisticType.Count, "CityCount");
// Add the statistics definitions to a list
List <StatisticDefinition> statDefinitions = new List <StatisticDefinition>
{
statDefinitionAvgPop,
statDefinitionCount,
statDefinitionMinPop,
statDefinitionMaxPop,
statDefinitionSumPop,
statDefinitionStdDevPop,
statDefinitionVarPop
};
// Create the statistics query parameters, pass in the list of definitions
StatisticsQueryParameters statQueryParams = new StatisticsQueryParameters(statDefinitions);
// If only using features in the current extent, set up the spatial filter for the statistics query parameters
if (_onlyInExtentSwitch.On)
{
// Get the current extent (envelope) from the map view
Envelope currentExtent = _myMapView.GetCurrentViewpoint(ViewpointType.BoundingGeometry).TargetGeometry as Envelope;
// Set the statistics query parameters geometry with the envelope
statQueryParams.Geometry = currentExtent;
// Set the spatial relationship to Intersects (which is the default)
statQueryParams.SpatialRelationship = SpatialRelationship.Intersects;
}
// If only evaluating the largest cities (over 5 million in population), set up an attribute filter
if (_onlyBigCitiesSwitch.On)
{
// Set a where clause to get the largest cities (could also use "POP_CLASS = '5,000,000 and greater'")
statQueryParams.WhereClause = "POP_RANK = 1";
}
// Execute the statistical query with these parameters and await the results
StatisticsQueryResult statQueryResult = await _worldCitiesTable.QueryStatisticsAsync(statQueryParams);
// Get the first (only) StatisticRecord in the results
StatisticRecord record = statQueryResult.FirstOrDefault();
// Make sure a record was returned
if (record == null || record.Statistics.Count == 0)
{
// Notify the user that no results were returned
UIAlertView alert = new UIAlertView();
alert.Message = "No results were returned";
alert.Title = "Statistical Query";
alert.Show();
return;
}
// Display results
IReadOnlyDictionary <string, object> statistics = record.Statistics;
ShowStatsList(statistics);
}
