public List <DatabasePerHourStat> CalculateHourlyAverages(List <DateTime> hoursToCalulate)
{
List <DatabasePerHourStat> perHourStats = new List <DatabasePerHourStat>();
DatabasePerHourStat hourStat = null;
List <DatabasePerSecondStat> tempAllSecondsInHour = null;
int minPeopleInHour = -1;
int maxPeopleInHour = -1;
int tempSum = 0;
foreach (DateTime hour in hoursToCalulate)
{
tempAllSecondsInHour = _dbQueryService.GetAllSecondsForHour(hour);
if (tempAllSecondsInHour.Count != 3600)
{
// Write to log that this hour is missing PerSecondStats.
return(null);
}
minPeopleInHour = tempAllSecondsInHour.First().NumDetectedObjects;
maxPeopleInHour = tempAllSecondsInHour.First().NumDetectedObjects;
tempSum = 0;
// Calculate average, min and max. Create DatabasePerHourStat.
foreach (DatabasePerSecondStat second in tempAllSecondsInHour)
{
tempSum += second.NumDetectedObjects;
if (second.NumDetectedObjects < minPeopleInHour)
{
minPeopleInHour = second.NumDetectedObjects;
}
if (second.NumDetectedObjects > maxPeopleInHour)
{
maxPeopleInHour = second.NumDetectedObjects;
}
}
hourStat = new DatabasePerHourStat
{
Day = DateTimeTools.GetHourBeginning(hour),
Hour = hour.Hour,
AverageDetectedObjects = (tempSum / tempAllSecondsInHour.Count),
MaximumDetectedObjects = maxPeopleInHour,
MinimumDetectedObjects = minPeopleInHour
};
perHourStats.Add(hourStat);
}
return(perHourStats);
}
public bool PersistNewPerHourStats(List <DatabasePerHourStat> perHourStats)
{
// Define the bulk insert query without any values to insert.
string bulkInsertCommand = $"INSERT INTO {DatabasePerHourStat.TABLE_NAME} "
+ $"({DatabasePerHourStat.DATE_DAY_LABEL},{DatabasePerHourStat.DATE_HOUR_LABEL},{DatabasePerHourStat.MAX_DETECTED_OBJECT_LABEL},"
+ $"{DatabasePerHourStat.MIN_DETECTED_OBJECT_LABEL},{DatabasePerHourStat.AVG_DETECTED_OBJECT_LABEL}) VALUES ";
// Append the values one by one to the bulk insert query.
DatabasePerHourStat lastHourStat = perHourStats.Last();
foreach (DatabasePerHourStat hourStat in perHourStats)
{
string dateDay = MySqlDateTimeConverter.ToMySqlDateString(hourStat.Day);
string dateHour = hourStat.Hour.ToString();
string hourMax = hourStat.MaximumDetectedObjects.ToString();
string hourMin = hourStat.MinimumDetectedObjects.ToString();
string hourAverage = hourStat.AverageDetectedObjects.ToString();
bulkInsertCommand += $"('{dateDay}',{dateHour},{hourMax},{hourMin},{hourAverage})";
if (hourStat != lastHourStat)
{
bulkInsertCommand += ",";
}
}
// Open connection and execute bulk insert command.
using (MySqlConnection conn = GetConnection())
{
conn.Open();
try
{
MySqlCommand cmd = new MySqlCommand(bulkInsertCommand, conn);
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e);
// Write to Log
return(false); // This is probably not going to be executed...
}
}
return(true);
}
