private double GetSecondsSinceLastHit(CheckPointHighRisk HighRiskRecord)
{
Database db = new Database(this.database);
double result = 0;
string query = String.Format(@"
SELECT TIMESTAMP(patrol.patDate, patrol.patTime) as 'timestamp'
FROM patrol
LEFT JOIN tag ON patrol.patTSN = tag.tagTSN
LEFT JOIN checkpoint ON checkpoint.tag_id = tag.id
WHERE checkpoint.id = {0}
ORDER BY patrol.patDate DESC, patrol.patTime DESC LIMIT 1
", HighRiskRecord.checkpoint_id);
using (var reader = db.Query(query))
{
while (reader.Read())
{
DateTime last = reader.GetDateTime(0);
DateTime now = Utility.Now(this.gmtOffset);
TimeSpan ts = now - last;
result = Math.Floor(ts.TotalSeconds);
}
}
return(result);
}
/// <summary>
/// Process emails to send
/// </summary>
/// <param name="HighRiskRecord_Id"></param>
private void ProcessEmailAlert(CheckPointHighRisk HighRiskRecord)
{
List <string> emailList = new List <string>();
GetEmailList(HighRiskRecord.id, emailList);
if (emailList.Count > 0)
{
try
{
Mailer mail = new Mailer();
mail.Recipients = emailList;
mail.Visit = GetMissedVisit(HighRiskRecord);
//there are circumstances where doing a search for GetMissedVisit, there is no data returned
//this should only mail out if there is data returned, else empty alert is useless.
if (HighRiskRecord.anyMissedVisitData != true)
{
mail.sendMail();
}
}
catch (Exception ex)
{
Log.Error("Mailer setup error:\r\n" + ex.Message);
}
}
}
/// <summary>
/// Gets the missed visit details
/// </summary>
/// <param name="HighRiskRecord_Id">Checkpoint ID</param>
/// <returns>
/// object[] { checkpoint, tagNo, site, region, exception date, exception time }
/// </returns>
private object[] GetMissedVisit(CheckPointHighRisk HighRiskRecord)
{
object[] result = new object[7];
string query;
Database db = new Database(this.database);
// Get exception details
query = String.Format(@"
SELECT checkpointhighriskexception.hreDate,
checkpointhighriskexception.hreTime,
checkpoint.chpDescription,
tag.tagTSN,
site.sitName,
region.regName
FROM checkpointhighriskexception
JOIN checkpointhighrisk ON checkpointhighriskexception.checkpointhighrisk_id = checkpointhighrisk.id
JOIN checkpoint ON checkpointhighrisk.checkpoint_id = checkpoint.id
JOIN tag on checkpoint.tag_id = tag.id
JOIN site on checkpoint.site_id = site.id
JOIN region on site.region_id = region.id
WHERE checkpointhighrisk_id = {0}
ORDER BY checkpointhighriskexception.hreDate DESC,
checkpointhighriskexception.hreTime DESC
LIMIT 1
", HighRiskRecord.id);
using (var reader = db.Query(query))
{
while (reader.Read())
{
result[0] = reader.GetDateTime(0).ToString("dd/MM/yyyy");
result[1] = reader.GetValue(1).ToString();
result[2] = reader.GetString(2);
result[3] = reader.GetString(3);
result[4] = reader.GetString(4);
result[5] = reader.GetString(5);
if (result == null)
{
HighRiskRecord.anyMissedVisitData = true;
}
else
{
HighRiskRecord.anyMissedVisitData = false;
}
}
}
return(result);
}
/// <summary>
/// class to check for valid or invalid time interval since Lastcheck
/// </summary>
/// <param name="HighRiskRecord"></param>
/// <returns></returns>
private bool IsCheckLastCheckedTime(CheckPointHighRisk HighRiskRecord)
{
int timeAllowance = HighRiskRecord.chrTimeAllowance;
int timeOffset = HighRiskRecord.chrCheckOffset;
DateTime start = HighRiskRecord.chrStartTime;
double secondsSinceLastHit = this.GetSecondsSinceLastHit(HighRiskRecord);
double secondsSinceLastCheck = this.GetSecondsSinceLastCheck(HighRiskRecord.id);
double secondsSinceStart = this.GetSecondsSinceStart(HighRiskRecord.chrStartTime);
// Check that the seconds since the start of the shift are greater than the time allowance
// And the number of seconds since the last hit is also greater than the time allowance
// And the number of seconds since the last check are greater than the time offset
return(!((secondsSinceStart > timeAllowance) && (secondsSinceLastHit > timeAllowance) && (secondsSinceLastCheck > timeOffset)));
}
/// <summary>
/// Writes data to the database for the records and sets the chrStartDate and chrLastCheck
/// </summary>
/// <param name="HighRiskData"></param>
private void CheckandInitialiseNullRecords(CheckPointHighRisk HighRiskRecord)
{
Database db = new Database(this.database);
UpdateQuery update = new UpdateQuery();
update.SetTable("checkpointhighrisk");
update.SetFields(new string[] { "chrLastCheck", "chrStartTime" });
string _nullDateTime = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss");
List <string> _nullDates = new List <string>();
_nullDates.Add(_nullDateTime);
_nullDates.Add(_nullDateTime);
update.SetRowValues(_nullDates);
update.SetId(HighRiskRecord.id.ToString());
db.Update(update);
}
