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); }