public DatabasePerSecondStat GetLatestPerSecondStatForCamera(int cameraId)
{
DatabasePerSecondStat perSecondStat = null;
using (MySqlConnection conn = GetConnection())
{
string query = $"SELECT * FROM {DatabasePerSecondStat.TABLE_NAME} "
+ $"WHERE {DatabasePerSecondStat.CAMERA_ID_LABEL} = {cameraId} "
+ $"ORDER BY {DatabasePerSecondStat.DATE_TIME_LABEL} DESC LIMIT 1";
conn.Open();
MySqlCommand cmd = new MySqlCommand(query, conn);
using (var reader = cmd.ExecuteReader())
{
// Expecting only one row
if (reader.Read())
{
perSecondStat = new DatabasePerSecondStat()
{
PerSecondStatId = Convert.ToInt32(reader[DatabasePerSecondStat.PER_SECOND_STAT_ID_LABEL]),
DateTime = Convert.ToDateTime(reader[DatabasePerSecondStat.DATE_TIME_LABEL]),
CameraId = Convert.ToInt32(reader[DatabasePerSecondStat.CAMERA_ID_LABEL]),
NumDetectedObjects = Convert.ToInt32(reader[DatabasePerSecondStat.NUM_DETECTED_OBJECTS_LABEL]),
HasSavedImage = Convert.ToBoolean(Convert.ToInt16(reader[DatabasePerSecondStat.HAS_SAVED_IMAGE_LABEL]))
};
}
}
}
return(perSecondStat);
}
public void getNotificationWhenAlertConditionIsMetTest()
{
DatabasePerSecondStat perSecondStat = new DatabasePerSecondStat()
{
CameraId = 1,
DateTime = DateTime.Now,
DateTimeReceived = DateTime.Now,
HasSavedImage = false,
NumDetectedObjects = 1,
PerSecondStatId = 1,
PerHourStatId = 1
};
Mock <IDatabaseQueryService> mockDBService = new Mock <IDatabaseQueryService>(MockBehavior.Loose);
List <DatabaseAlert> singleDBAlerts = new List <DatabaseAlert>();
singleDBAlerts.Add(dbAlerts[0]);
mockDBService.Setup(x => x.GetAllAlerts(0)).Returns(singleDBAlerts);
mockDBService.Setup(x => x.GetEarliestPerSecondStatTriggeringAlert(singleDBAlerts[0], It.IsAny <DateTime>(), It.IsAny <DateTime>())).Returns(perSecondStat);
mockDBService.Setup(x => x.PersistNewNotification(It.Is <DatabaseNotification>(p => p.AlertId == 1))).Returns(true);
Thread alertMonitoringThread = new Thread(delegate()
{
int snoozeDurationMinutes = 5;
AlertMonitoringService alertMonitoringService = new AlertMonitoringService(mockDBService.Object, new EmailService("test", "test"), snoozeDurationMinutes);
alertMonitoringService.StartMonitoring();
});
alertMonitoringThread.Start();
Thread.Sleep(1000);
mockDBService.Verify(m => m.PersistNewNotification(It.Is <DatabaseNotification>(p => p.AlertId == 1)), Times.Once);
}
public void ShouldRetrieveItem()
{
DatabaseCamera testCamera = new DatabaseCamera()
{
CameraId = 1,
CameraName = "cameraName",
LocationId = 1,
UserId = 1,
MonitoredArea = "Room"
};
DatabasePerSecondStat testStat = new DatabasePerSecondStat()
{
CameraId = 1,
HasSavedImage = false,
DateTime = new DateTime(2018, 4, 3, 10, 0, 0),
NumDetectedObjects = 23,
PerSecondStatId = 2
};
List <DatabasePerSecondStat> listOfStats = new List <DatabasePerSecondStat>();
listOfStats.Add(testStat);
Mock <IDatabaseQueryService> mockDBService = new Mock <IDatabaseQueryService>(MockBehavior.Strict);
mockDBService.Setup(x => x.GetCameraById(1)).Returns(testCamera);
mockDBService.Setup(x => x.GetLatestPerSecondStatForCamera(It.IsAny <int>())).Returns(testStat);
mockDBService.Setup(x => x.GetPerSecondStatsForCamera(1)).Returns(listOfStats);
CameraService cameraService = new CameraService(mockDBService.Object, new GraphStatisticService(mockDBService.Object), new LocationService(mockDBService.Object));
Assert.That(cameraService.getCameraStatisticsForNowById(1).MostRecentPeopleCount, Is.EqualTo(23));
}
public CameraStatistics getCameraStatisticsForNowById(int cameraId)
{
DatabaseCamera camera = _dbQueryService.GetCameraById(cameraId);
DatabasePerSecondStat mostRecentStat = _dbQueryService.GetLatestPerSecondStatForCamera(cameraId);
GraphStatistics graphStatistics = _graphStatisticsService.GetLast30MinutesStatistics(cameraId);
if (camera != null)
{
CameraStatistics cameraStatistics = new CameraStatistics()
{
CameraInformation = new CameraInformation(camera),
DayTimeOfTheWeekAverageCount = 0,
DayTimeOfTheWeekAverageCountAvailable = false,
DayTimeOfTheWeekAverageCountDisplayString = null,
LastUpdatedTime = null,
MostRecentPeopleCount = 0,
PeriodOfTheDayAverageCount = 0,
PeriodOfTheDayAverageCountAvailable = false,
PeriodOfTheDayAverageCountDisplayString = null,
GraphStatistics = graphStatistics
};
if (mostRecentStat != null)
{
cameraStatistics.LastUpdatedTime = mostRecentStat.DateTime;
cameraStatistics.MostRecentPeopleCount = mostRecentStat.NumDetectedObjects;
}
return(cameraStatistics);
}
else
{
return(null);
}
}
private string GetEmailBody(DatabaseAlert alert, DatabasePerSecondStat earliestStatThatTriggersAlert, DatabaseUser user)
{
AlertTriggeredEmailInformation alertEmailInfo =
GetAlertTriggeredInformation(alert, earliestStatThatTriggersAlert, user);
string emailBody =
RazorEngineWrapper.RunCompile("Views/Alert", "AlertTriggeredEmailBodyTemplate.cshtml", alertEmailInfo);
return(emailBody);
}
public CameraStatistics getCameraStatisticsForNowById(int cameraId)
{
DatabaseCamera camera = _dbQueryService.GetCameraById(cameraId);
DatabaseRoom room = null;
if (camera != null && camera.RoomId != null)
{
room = _dbQueryService.GetRoomById(camera.RoomId.Value);
}
DatabasePerSecondStat mostRecentStat = _dbQueryService.GetLatestPerSecondStatForCamera(cameraId);
GraphStatistics graphStatistics = _graphStatisticsService.GetLast30MinutesStatistics(cameraId);
if (camera != null)
{
CameraStatistics cameraStatistics = new CameraStatistics
{
CameraInformation = new CameraInformation(camera),
CameraDetails = new CameraDetails(camera),
DayTimeOfTheWeekAverageCount = 0,
DayTimeOfTheWeekAverageCountAvailable = false,
DayTimeOfTheWeekAverageCountDisplayString = null,
LastUpdatedTime = null,
MostRecentPeopleCount = null,
PeriodOfTheDayAverageCount = 0,
PeriodOfTheDayAverageCountAvailable = false,
PeriodOfTheDayAverageCountDisplayString = null,
GraphStatistics = graphStatistics,
TempImagePath = null
};
if (room != null)
{
cameraStatistics.CameraDetails.MonitoredArea = room.RoomName;
cameraStatistics.CameraInformation.CameraRoomName = room.RoomName;
cameraStatistics.CameraInformation.RoomId = room.RoomId;
}
if (mostRecentStat != null)
{
cameraStatistics.LastUpdatedTime = mostRecentStat.DateTime;
cameraStatistics.MostRecentPeopleCount = mostRecentStat.NumDetectedObjects;
}
if (camera.LocationId != null)
{
cameraStatistics.CameraDetails.Location = new LocationDetails(_dbQueryService.GetLocationById(camera.LocationId.Value));
}
cameraStatistics.CameraInformation.TempImagePath = GetTempPathFromFullPath(camera.ImagePath);
return(cameraStatistics);
}
return(null);
}
private void HandleTriggeringOfAlert(DatabaseAlert alert, DatabasePerSecondStat earliestStatThatTriggersAlert)
{
if ((ContactMethod)Enum.Parse(typeof(ContactMethod), alert.ContactMethod) == ContactMethod.Notification)
{
CreateNotificationForTriggeredAlert(alert, earliestStatThatTriggersAlert);
}
else if ((ContactMethod)Enum.Parse(typeof(ContactMethod), alert.ContactMethod) == ContactMethod.Email)
{
bool emailSuccess = SendAlertTriggeredEmail(alert, earliestStatThatTriggersAlert);
CreateNotificationForTriggeredAlert(alert, earliestStatThatTriggersAlert, !emailSuccess);
}
}
private bool SendAlertTriggeredEmail(DatabaseAlert alert, DatabasePerSecondStat earliestStatThatTriggersAlert)
{
DatabaseUser databaseUser = _databaseQueryService.GetUserById(alert.UserId);
if (!databaseUser.EmailAddress.IsNullOrEmpty())
{
string emailSubject = GetEmailSubject(alert, earliestStatThatTriggersAlert);
string emailBody = GetEmailBody(alert, earliestStatThatTriggersAlert, databaseUser);
return(_emailService.SendEmail(databaseUser.EmailAddress, emailSubject, emailBody));
}
return(false);
}
private void CreateNotificationForTriggeredAlert(DatabaseAlert alert,
DatabasePerSecondStat earliestStatThatTriggersAlert, bool failedEmail = false)
{
DatabaseNotification dbNotification = new DatabaseNotification
{
AlertId = alert.AlertId,
Acknowledged = false,
TriggerDateTime = earliestStatThatTriggersAlert.DateTime,
FailedEmail = failedEmail
};
_databaseQueryService.PersistNewNotification(dbNotification);
}
private void HandleAlertMonitoring(DatabaseAlert alert, DateTime lastCheckup, DateTime checkupDateTime)
{
//check if any persecondstat has a count higher than threshold since last checkup
DatabasePerSecondStat earliestStatThatTriggersAlert
= _databaseQueryService.GetEarliestPerSecondStatTriggeringAlert(alert, lastCheckup, checkupDateTime);
if (earliestStatThatTriggersAlert != null)
{
LogManager.GetLogger("AlertMonitoringService").Info($"Following stat triggered {alert.AlertName}: " +
$"{earliestStatThatTriggersAlert.NumDetectedObjects} at {earliestStatThatTriggersAlert.DateTime}");
HandleTriggeringOfAlert(alert, earliestStatThatTriggersAlert);
//Snooze alert so that it doesn't get retriggered constantly if it monitors a constantly busy area
SnoozeAlert(alert);
}
}
private bool DeletePerSecondStatJpgFrm(DatabasePerSecondStat dbStat)
{
string imagePath = dbStat.FrameJpgPath;
if (File.Exists(imagePath))
{
try
{
File.Delete(imagePath);
return(true);
}
catch (Exception e)
{
logger.Error(e);
}
}
return(false);
}
// Once a DataMessage is verified, this method allows persisting all contents (PerSecondStat objects) into the database.
public bool StoreStatsFromDataMessage(DataMessage verifiedMessage)
{
List <DatabasePerSecondStat> dbSecondsToPersist = new List <DatabasePerSecondStat>();
// Remove any possible duplicates.
List <PerSecondStat> distinctPerSecondStats = verifiedMessage.RealTimeStats.Distinct().ToList();
for (int y = 0; y < distinctPerSecondStats.Count; y++)
{
PerSecondStat stat = distinctPerSecondStats[y];
int cameraId = _dbQueryService.GetCameraIdFromKey(stat.CameraKey);
// Only persist PerSecondStats from valid Cameras (with valid CameraKeys).
if (cameraId > 0)
{
DatabasePerSecondStat dbPerSecondStat = new DatabasePerSecondStat();
dbPerSecondStat.CameraId = cameraId;
dbPerSecondStat.DateTime = MySqlDateTimeConverter.ToDateTime(stat.DateTime);
dbPerSecondStat.HasSavedImage = stat.HasSavedImage;
dbPerSecondStat.PerHourStatId = null;
dbPerSecondStat.NumDetectedObjects = stat.NumTrackedPeople;
dbPerSecondStat.DateTimeReceived = DateTime.Now;
// If PerSecondStat has a key frame.
if (stat.HasSavedImage && String.IsNullOrWhiteSpace(stat.FrameAsJpg) == false)
{
// Save it to the server.
dbPerSecondStat.FrameJpgPath = SaveKeyImage(stat);
}
else
{
dbPerSecondStat.HasSavedImage = false;
dbPerSecondStat.FrameJpgPath = null;
}
dbSecondsToPersist.Add(dbPerSecondStat);
}
}
return(_dbQueryService.PersistNewPerSecondStats(dbSecondsToPersist));
}
private AlertTriggeredEmailInformation GetAlertTriggeredInformation(DatabaseAlert alert,
DatabasePerSecondStat earliestStatThatTriggersAlert, DatabaseUser user)
{
DatabaseCamera camera = _databaseQueryService.GetCameraById(alert.CameraId);
AlertTriggeredEmailInformation alertEmailInformation = new AlertTriggeredEmailInformation
{
CameraName = camera.CameraName,
AlertName = alert.AlertName,
DateTriggered = earliestStatThatTriggersAlert.DateTime
};
alertEmailInformation.Name = GetUserName(user);
if (camera.LocationId != null)
{
alertEmailInformation.LocationName = _databaseQueryService.GetLocationById(camera.LocationId.Value).LocationName;
}
return(alertEmailInformation);
}
private void AddDummyStatsForDeadPeriods(List <DatabasePerSecondStat> perSecondStats)
{
List <DatabasePerSecondStat> dummyStats = new List <DatabasePerSecondStat>();
for (int i = 0; i < perSecondStats.Count - 1; i++)
{
DatabasePerSecondStat previousStat = perSecondStats[i];
DatabasePerSecondStat nextStat = perSecondStats[i + 1];
if (nextStat.DateTime.Subtract(previousStat.DateTime).TotalMinutes > 2)
{
dummyStats.Add(new DatabasePerSecondStat
{
DateTime = previousStat.DateTime.AddSeconds(5),
NumDetectedObjects = 0
});
dummyStats.Add(new DatabasePerSecondStat
{
DateTime = nextStat.DateTime.AddSeconds(-5),
NumDetectedObjects = 0
});
}
}
perSecondStats.AddRange(dummyStats);
}
private static string GetEmailSubject(DatabaseAlert alert, DatabasePerSecondStat earliestStatThatTriggersAlert)
{
return($"Your alert '{alert.AlertName}' was triggered on {earliestStatThatTriggersAlert.DateTime.ToShortTimeString()}");
}
public FrameInformation(DatabasePerSecondStat dbPerSecondStat)
{
FrmJpgRelPath = CameraService.GetTempPathFromFullPath(dbPerSecondStat.FrameJpgPath);
PeopleCount = dbPerSecondStat.NumDetectedObjects;
Timestamp = dbPerSecondStat.DateTime;
}