public static string GetSchemaName(string schemaAlias)
{
string output = null;
Database db = new Database();
string query = String.Format("SELECT schCode FROM schemalocation WHERE schCode LIKE '%{0}%'", schemaAlias);
// Initialize reader
using (var reader = db.Query(query))
while (reader.Read())
{
output = reader.GetValue(0).ToString();
}
return output;
}
public bool CheckPatrol(string tsn, string date, string time, string rsn)
{
Database db = new Database(this.newDatabase);
string query = String.Format(
"SELECT id FROM patrol WHERE patTSN = '{0}' AND patDate = '{1}' AND patTime = '{2}' AND patRSN = '{3}'",
tsn,
date,
time,
rsn
);
using (var reader = db.Query(query))
{
while (reader.Read())
{
return false;
}
}
return true;
}
/// <summary>
/// Creates a loop exception and returns its ID
/// </summary>
private int CreateException()
{
// Connect to the database
Database db = new Database(this.database);
// Create insert query
InsertQuery insert = new InsertQuery();
insert.SetTable("loopexception");
insert.SetFields(new string[3] { "loop_id", "lexDate", "lexTime" });
insert.AddRowValues(new string[3] {
this.loopId.ToString(),
Utility.Now(this.gmtOffset).ToString("yyyy-MM-dd"),
Utility.Now(this.gmtOffset).ToString("HH:mm:ss")
});
// Insert
db.Insert(insert);
// Return last insert ID
return db.GetLastColumn("loopexception", "id");
}
/// <summary>
/// Checks if the welfare check falls within the parameters required to check it or not
/// </summary>
/// <returns></returns>
private bool IsActive()
{
// We will store all the start and end times in a dates variable as a list
List<DateTime[]> dateList = new List<DateTime[]>();
// Connect to database
Database db = new Database(this.database);
// Build query
string query = String.Format(@"
SELECT @diff:=( CAST(welfarecheckday.day_id AS SIGNED) - (WEEKDAY(CURRENT_DATE) + 1) ) as 'diff',
@date:=DATE_ADD(CURRENT_DATE, INTERVAL @diff DAY) as 'start_date',
CONVERT(TIMESTAMP(@date, welfarecheckday.wedStart) USING latin1) AS 'start',
CONVERT(TIMESTAMP(IF (welfarecheckday.wedFinish <= welfarecheckday.wedStart,
DATE_ADD(@date, INTERVAL 1 DAY),
@date),
welfarecheckday.wedFinish) USING latin1) AS 'end',
welfarecheck.welAlwaysCheck,
welfarecheckday.day_id
FROM welfarecheckday
LEFT JOIN welfarecheck ON welfarecheckday.welfarecheck_id = welfarecheck.id
LEFT JOIN site ON welfarecheck.site_id = site.id
WHERE welfarecheck.id = {0}
AND welfarecheckday.wedActive = 1
AND welfarecheckday.day_id < 8
AND site.sitWelfareActive = 1
", this.welfareId);
using (var reader = db.Query(query))
{
while (reader.Read())
{
// Set to true if 'always check' is selected and matches the current day of the week
if (Convert.ToInt32(reader.GetValue(4)) == 1 && Convert.ToInt32(reader.GetValue(5)) == this.dayOfWeek)
return true;
// Assign TIMESTAMP data to DateTime array List
dateList.Add(new DateTime[2] { Convert.ToDateTime(reader.GetString(2)), Convert.ToDateTime(reader.GetString(3)) });
}
}
// Establish current time
DateTime target = Utility.Now(this.gmtOffset);
// Iterate over dates stored
foreach (var dates in dateList)
{
// If right now lands in between the start and end time of any of the following, return true
if (target > dates[0] && target < dates[1])
{
// Assign the start date/time
this.start = dates[0];
// Return true
return true;
}
}
return false;
}
private List<string[]> GetVisitHistory()
{
List<string[]> list = new List<string[]>();
Database db = new Database(this.database);
// Get DateTime and substract 24 hours
string startTime = Utility.Now(this.gmtOffset).AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss");
string query = String.Format(@"
SELECT
patrol.patDate,
patrol.patTime,
patrol.patRSN,
checkpoint.chpDescription,
site.sitName,
region.regName,
IF (recorder.recName IS NULL, patrol.patRSN, recorder.recName) AS 'recName'
FROM patrol
LEFT JOIN tag ON patrol.patTSN = tag.tagTSN
LEFT JOIN recorder ON patrol.patRSN = recorder.recRSN
LEFT JOIN checkpoint ON checkpoint.tag_id = tag.id
LEFT JOIN site ON checkpoint.site_id = site.id
LEFT JOIN region ON site.region_id = region.id
WHERE patrol.patRSN = {0}
AND TIMESTAMP(patrol.patDate, patrol.patTime) > '{1}'
ORDER BY patrol.patDate ASC, patrol.patTime ASC LIMIT 10
", this.recorderSerial, startTime);
using (var reader = db.Query(query))
{
while (reader.Read())
{
list.Add(new string[7] {
reader.GetDateTime(0).ToString("dd/MM/yyyy"),
reader.GetValue(1).ToString(),
reader.GetString(2),
reader.GetString(3) == String.Empty ? "Unkonwn Checkpoint" : reader.GetString(3),
reader.GetString(4),
reader.GetString(5),
reader.GetString(6)
});
}
}
return list;
}
/// <summary>
/// Gets total seconds since last welfare check
/// </summary>
/// <returns>Returns seconds since last welfare check</returns>
private double GetSecondsSinceLastCheck()
{
Database db = new Database(this.database);
double result = 0;
string query = String.Format(
"SELECT welLastCheck FROM welfarecheck WHERE id = {0}", this.welfareId);
using (var reader = db.Query(query))
{
while (reader.Read())
{
DateTime last = reader.GetDateTime(0);
DateTime now = Utility.Now();
TimeSpan ts = now - last;
result = Math.Floor(ts.TotalSeconds);
}
}
return result;
}
/// <summary>
/// Gets the email list to contact in case of an exception
/// </summary>
private void GetEmailList()
{
Database db = new Database(this.database);
string query = String.Format(
"SELECT weeEmail FROM welfarecheckemail WHERE welfarecheck_id = {0}", this.welfareId);
using (var reader = db.Query(query))
{
while (reader.Read())
{
this.emailList.Add(reader.GetString(0));
}
}
}
/// <summary>
/// Updates the last completion row in the table
/// </summary>
/// <param name="dt"></param>
private void UpdateLastCompletion(DateTime dt)
{
Database db = new Database(this.database);
UpdateQuery update = new UpdateQuery();
update.SetTable("loop");
update.SetFields(new string[] { "looLastCompletion" });
update.AddRowValue(dt.ToString("yyyy-MM-dd HH:mm:ss"));
update.SetId(this.loopId.ToString());
db.Update(update);
this.lastCompletion = dt;
}
private void UpdateLastCheck(int HighRiskRecord_Id)
{
Database db = new Database(this.database);
UpdateQuery update = new UpdateQuery();
update.SetTable("checkpointhighrisk");
update.SetFields(new string[] { "chrLastCheck" });
update.AddRowValue(DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
update.SetId(HighRiskRecord_Id.ToString());
db.Update(update);
}
/// <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>
/// Get Email Addresses from checkpointhighriskemail table
/// </summary>
private void GetEmailList(int HighRiskRecord_Id, List<string> emailList)
{
Database db = new Database(this.database);
string query = String.Format(
//"SELECT cheEmail FROM checkpointhighriskemail WHERE checkpointhighrisk_id = {0}",
//modification to allow for only active checkpoints to be emailed
@"SELECT cheEmail
FROM checkpointhighriskemail
INNER JOIN checkpointhighrisk on checkpointhighrisk.id=checkpointhighriskemail.checkpointhighrisk_id
WHERE checkpointhighrisk_id = {0} and checkpointhighrisk.chrActive=1",
HighRiskRecord_Id);
using (var reader = db.Query(query))
{
while (reader.Read())
{
emailList.Add(reader.GetString(0));
}
}
}
private void CreateException(int HighRiskRecord_Id)
{
// Create insert query
InsertQuery insert = new InsertQuery();
insert.SetTable("checkpointhighriskexception");
insert.SetFields(new string[] { "checkpointhighrisk_id", "hreDate", "hreTime" });
insert.AddRowValues(new string[] {
HighRiskRecord_Id.ToString(),
Utility.Now(this.gmtOffset).ToString("yyyy-MM-dd"),
Utility.Now(this.gmtOffset).ToString("HH:mm:ss")
});
// Database
Database db = new Database(this.database);
db.Insert(insert);
}
/// <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);
}
/*********************************************************
* DELETE STATEMENTS
*********************************************************/
public void DeleteImport(int id)
{
Database db = new Database(this.newDatabase);
db.Delete("import", id);
}
/// <summary>
/// Gets number of checkpoints in site
/// </summary>
/// <returns></returns>
private double NumberOfCheckpoints()
{
// Connect to the database
Database db = new Database(this.database);
double numCheckpoints = 0;
// Get number of checkpoints in site
string q1 = String.Format(@"
SELECT COUNT(*) as 'num'
FROM checkpoint
WHERE site_id = {0}
AND checkpoint.id NOT IN (SELECT checkpoint_id FROM loopexclusion WHERE loop_id = {1})
", this.siteId, this.loopId);
// Get response
using (var reader = db.Query(q1))
while (reader.Read())
numCheckpoints = Convert.ToDouble(reader.GetValue(0));
return numCheckpoints;
}
/// <summary>
/// Retrieves Data from HighRiskCheckpoint Table
/// </summary>
public void RetrieveData()
{
Database db = new Database(this.database);
HighRiskDataList = new List<CheckPointHighRisk>();
string query = @"SELECT checkpointhighrisk.id,
checkpointhighrisk.checkpoint_id,
checkpointhighrisk.chrTimeAllowance,
checkpointhighrisk.chrCheckOffset,
checkpointhighrisk.chrStartTime,
checkpointhighrisk.chrLastCheck,
checkpointhighrisk.chrActive,
branch.zone_id
FROM checkpointhighrisk
JOIN checkpoint ON checkpointhighrisk.checkpoint_id = checkpoint.id
JOIN site ON checkpoint.site_id = site.id
JOIN region ON site.region_id = region.id
JOIN branch ON region.branch_id = branch.id
WHERE checkpointhighrisk.chrActive = 1";
using (var reader = db.Query(query))
{
while (reader.Read())
{
try
{
bool isNull = (reader.GetValue(4) is System.DBNull && reader.GetValue(5) is System.DBNull);
this.gmtOffset = data.GetZoneOffset(Convert.ToInt32(reader.GetValue(7)));
if (isNull)
HighRiskDataList.Add(new CheckPointHighRisk(
reader.GetValue(0), // Id
reader.GetValue(1), // Checkpoint Id
reader.GetValue(2), // Time Allowance
reader.GetValue(3), // Check Offset
true, // Last Check
true, // Start time
reader.GetValue(6), // Active
this.gmtOffset // GMT Offset
));
else
HighRiskDataList.Add(new CheckPointHighRisk(
reader.GetValue(0), // Id
reader.GetValue(1), // Checkpoint Id
reader.GetValue(2), // Time Allowance
reader.GetValue(3), // Check Offset
reader.GetValue(4), // Last Check
reader.GetValue(5), // Start time
reader.GetValue(6), // Active
this.gmtOffset // GMT Offset
));
}
catch (Exception ex)
{
Log.Error("Error: " + ex.Message);
}
}
}
//set Count for checking in HighRisk Class
HighRiskDataListCount = HighRiskDataList.Count;
}
/// <summary>
/// Updates the last check row in the table
/// </summary>
private void UpdateLastCheck()
{
Database db = new Database(this.database);
DateTime dt = DateTime.Now;
UpdateQuery update = new UpdateQuery();
update.SetTable("loop");
update.SetFields(new string[] { "looLastCheck", "looPreWarned" });
update.AddRowValue(dt.ToString("yyyy-MM-dd HH:mm:ss"));
update.AddRowValue(0.ToString());
update.SetId(this.loopId.ToString());
db.Update(update);
this.lastCheck = dt;
}
/// <summary>
/// Gets the email list to contact in case of an exception
/// </summary>
private void GetEmailList(bool isPreWarning)
{
// Connect to database
Database db = new Database(this.database);
string preWarning = isPreWarning ? "1" : "0";
// Query
string q1 = String.Format("SELECT loeEmail FROM loopemail WHERE loop_id = {0} AND loePreWarn = {1}", this.loopId, preWarning);
try
{
// Insert results into list
using (var reader = db.Query(q1))
while (reader.Read())
this.emailList.Add(reader.GetString(0));
}
catch (Exception ex)
{
Log.Error(ex.ToString());
}
}
/// <summary>
/// Set the loop pre-warned status to true
/// </summary>
private void UpdatePreWarning()
{
Database db = new Database(this.database);
UpdateQuery update = new UpdateQuery();
update.SetTable("loop");
update.SetFields(new string[] { "looPreWarned" });
update.AddRowValue(1.ToString());
update.SetId(this.loopId.ToString());
db.Update(update);
this.preWarned = true;
}
/// <summary>
/// Gets the missing checkpoints from the breaching loop
/// </summary>
private void GetMissingCheckpoints()
{
// Connect to the database
Database db = new Database(this.database);
// Create the query string for the existing checkpoints
string chpString = this.checkpoints.Count > 0 ?
String.Format(" AND tag.tagTSN NOT IN ({0})", string.Join(", ", this.checkpoints.ToArray())) :
null;
// Create query to get missing checkpoints
string query = String.Format(@"
SELECT checkpoint.id,
checkpoint.chpDescription,
site.sitName,
tag.tagTSN
FROM tag
JOIN checkpoint ON checkpoint.tag_id = tag.id
JOIN site ON checkpoint.site_id = site.id
WHERE site.id = {0}
AND checkpoint.id NOT IN (SELECT checkpoint_id FROM loopexclusion WHERE loop_id = {1})
{2}
", this.siteId, this.loopId, chpString);
// Get response
using (var reader = db.Query(query))
{
while (reader.Read())
{
// Create new missing checkpoint
MissingCheckpoint mc = new MissingCheckpoint();
mc.CheckpointID = reader.GetInt32(0);
mc.Description = reader.GetString(1);
mc.SiteName = reader.GetString(2);
mc.TagSerial = reader.GetString(3);
// Add it to the list
this.missingCheckpoints.Add(mc);
}
}
}
/// <summary>
/// Creates an exception and inserts it to the database.
/// </summary>
private void CreateException()
{
// Create insert query
InsertQuery insert = new InsertQuery();
insert.SetTable("welfarecheckexception");
insert.SetFields(new string[4] { "welfarecheck_id", "excDate", "excTime", "excRSN" });
insert.AddRowValues(new string[4] {
this.welfareId.ToString(),
Utility.Now(this.gmtOffset).ToString("yyyy-MM-dd"),
Utility.Now(this.gmtOffset).ToString("HH:mm:ss"),
this.recorderSerial.ToString()
});
// Database
Database db = new Database(this.database);
db.Insert(insert);
}
/// <summary>
/// Gets the site loop details and assign them to scope variables.
/// Will also adjust the last check property if it is null.
/// </summary>
private void GetSiteLoop()
{
// Connect to database
Database db = new Database(this.database);
string query = String.Format(
"SELECT looPreWarnInterval, looInterval, looMinimumCompliance, looLastCheck, looLastCompletion, looPreWarned FROM `loop` WHERE id = {0}", this.loopId
);
// Perform query
using (var reader = db.Query(query))
{
while (reader.Read())
{
// Assign variables
this.preWarnInterval = Convert.ToDouble(reader.GetValue(0));
this.interval = Convert.ToDouble(reader.GetValue(1));
this.minimumCompliance = Convert.ToDouble(reader.GetValue(2));
// If the last check is null, mark it as now
if (DBNull.Value.Equals(reader.GetValue(3)))
this.UpdateLastCheck();
// Otherwise assign the last check to the scope
else
this.lastCheck = reader.GetDateTime(3);
// Get the number of seconds since last check
this.secondsSinceLastCheck = (DateTime.Now - this.lastCheck).TotalSeconds;
// Check if the last completion is not null, and less than the start time
if ((!DBNull.Value.Equals(reader.GetValue(4)) && reader.GetDateTime(4) < this.start) || (DBNull.Value.Equals(reader.GetValue(4))))
{
this.UpdateLastCompletion(this.start.AddMinutes(-10));
}
// Get pre-warned status
this.preWarned = Convert.ToInt32(reader.GetValue(5)) == 0 ? false : true;
}
}
}
/// <summary>
/// Gets details for last welfare visit
/// </summary>
/// <returns>Object array, single row</returns>
private object[] GetLastWelfareVisit()
{
object[] result = new object[7];
Database db = new Database(this.database);
// Build query
string query = String.Format(@"
SELECT
patrol.patDate,
patrol.patTime,
patrol.patRSN,
checkpoint.chpDescription,
site.sitName,
region.regName,
IF (recorder.recName IS NULL, patrol.patRSN, recorder.recName) AS 'recName'
FROM patrol
LEFT JOIN tag ON patrol.patTSN = tag.tagTSN
LEFT JOIN recorder ON patrol.patRSN = recorder.recRSN
LEFT JOIN checkpoint ON checkpoint.tag_id = tag.id
LEFT JOIN site ON checkpoint.site_id = site.id
LEFT JOIN region ON site.region_id = region.id
WHERE site.id = {0}
ORDER BY patrol.patDate DESC, patrol.patTime DESC LIMIT 1
", this.siteId);
// Get data
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] = Convert.ToInt32(reader.GetValue(2));
result[3] = reader.GetString(3) == String.Empty ? "Unkonwn Checkpoint" : reader.GetString(3);
result[4] = reader.GetString(4);
result[5] = reader.GetString(5);
result[6] = reader.GetString(6);
// Let's add the recorder serial here for convenience too
this.recorderSerial = Convert.ToInt32(reader.GetValue(2));
}
}
return result;
}
/// <summary>
/// Insert data into exception
/// </summary>
/// <param name="loopExceptionId"></param>
/// <param name="checkpointId"></param>
private void InsertIntoException(int loopExceptionId, int checkpointId)
{
// Connect to the database
Database db = new Database(this.database);
// Create insert query
InsertQuery insert = new InsertQuery();
insert.SetTable("loopexceptioncheckpoint");
insert.SetFields(new string[2] { "loopexception_id", "checkpoint_id" });
insert.AddRowValues(new string[2] {
loopExceptionId.ToString(),
checkpointId.ToString()
});
// Insert
db.Insert(insert);
}
/// <summary>
/// Returns the time (in minutes) since the last registered hit in the site
/// </summary>
/// <returns>Number of minutes since last hit on site</returns>
private double GetSecondsSinceLastHit()
{
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
LEFT JOIN site ON checkpoint.site_id = site.id
WHERE site.id = {0}
ORDER BY patrol.patDate DESC, patrol.patTime DESC LIMIT 1
", this.siteId);
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>
/// Check that the loop is currently active and continue the check
/// </summary>
/// <returns></returns>
private bool IsActive()
{
// We will store all the start and end times in a dates variable as a list
List<DateTime[]> dateList = new List<DateTime[]>();
// Connect to database
Database db = new Database(this.database);
// Get an array of all the dates for the current week in loop
string query = String.Format(@"
SELECT @diff:=( CAST(loopshift.day_id AS SIGNED) - (WEEKDAY(CURRENT_DATE) + 1) ) as 'diff',
@date:=DATE_ADD(CURRENT_DATE, INTERVAL @diff DAY) as 'start_date',
CONVERT(TIMESTAMP(@date, loopshift.losStart) USING latin1) AS 'start',
CONVERT(TIMESTAMP(IF (loopshift.losFinish <= loopshift.losStart,
DATE_ADD(@date, INTERVAL 1 DAY),
@date),
loopshift.losFinish) USING latin1) AS 'end'
FROM loopshift
LEFT JOIN `loop` ON loopshift.loop_id = `loop`.id
WHERE `loop`.id = {0}
AND loopshift.losActive = 1
AND `loop`.looActive = 1
AND loopshift.day_id < 8
", this.loopId);
using (var reader = db.Query(query))
{
while (reader.Read())
{
// Assign TIMESTAMP data to DateTime array List
dateList.Add(new DateTime[2] { Convert.ToDateTime(reader.GetString(2)), Convert.ToDateTime(reader.GetString(3)) });
}
}
// Establish current time
DateTime target = Utility.Now(this.gmtOffset);
// Iterate over dates stored
foreach (var dates in dateList)
{
// If right now lands in between the start and end time of any of the following, return true
if (target > dates[0] && target < dates[1])
{
// First assign the start and end times
this.start = dates[0];
this.end = dates[1];
return true;
}
}
return false;
}
/// <summary>
/// Assigns all details about welfare check to the variables
/// </summary>
private void GetWelfareCheck()
{
Database db = new Database(this.database);
string query = String.Format(
"SELECT welTimeAllowance, welCheckOffset, welLastCheck FROM welfarecheck WHERE id = {0}", this.welfareId);
using (var reader = db.Query(query))
{
while (reader.Read())
{
this.timeAllowance = Convert.ToDouble(reader.GetValue(0));
this.timeOffset = Convert.ToDouble(reader.GetValue(1));
this.lastCheck = reader.GetValue(2).ToString();
}
}
}
/// <summary>
/// Determines wether or not the loop raises an exception
/// </summary>
/// <returns></returns>
private bool LoopException()
{
// Connect to the database
Database db = new Database(this.database);
double numCheckpoints = this.NumberOfCheckpoints();
// Ensure the number of checkpoints is more than 0
if (numCheckpoints > 0)
{
// Determine the parameters for the query:
// Query: select checkpoint from site -- start time: this.lastCompletion, end time: now,
// then make sure compliance percentage is met.
// also, exclude checkpoints which are in exclusion list
string query = String.Format(@"
SELECT DISTINCT patrol.patTSN
FROM patrol
LEFT JOIN tag ON tag.tagTSN = patrol.patTSN
LEFT JOIN checkpoint ON checkpoint.tag_id = tag.id
WHERE checkpoint.site_id = {0}
AND checkpoint.id NOT IN (SELECT checkpoint_id FROM loopexclusion WHERE loop_id = {1})
AND TIMESTAMP(patrol.patDate, patrol.patTime) BETWEEN '{2}' AND '{3}'
", this.siteId, this.loopId, this.start.ToString("yyyy-MM-dd HH:mm:ss"), this.end.ToString("yyyy-MM-dd HH:mm:ss"));
// Add response to checkpoints list
using (var reader = db.Query(query))
while (reader.Read())
this.checkpoints.Add(reader.GetValue(0).ToString());
// If the count of returned checkpoints is lower than the count of checkpoints, look closer
if (checkpoints.Count < numCheckpoints)
{
// Check percentage
double percentage = Math.Abs((checkpoints.Count / numCheckpoints) * 100);
// If the percentage visited is lower than the minimum compliance percentage
if (percentage < this.minimumCompliance)
{
// Return exception alert
return true;
}
}
}
return false;
}
/// <summary>
/// Updates the last check row in the table
/// </summary>
private void UpdateLastCheck()
{
Database db = new Database(this.database);
UpdateQuery update = new UpdateQuery();
update.SetTable("welfarecheck");
update.SetFields(new string[] { "welLastCheck" });
update.AddRowValue(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
update.SetId(this.welfareId.ToString());
db.Update(update);
}
/// <summary>
/// Returns the company name by database name
/// </summary>
/// <param name="databaseName">Database name</param>
/// <returns></returns>
public static string GetCompanyNameByDatabase(string databaseName)
{
string result = null;
int databaseId = 0;
Database db = new Database();
string query = String.Format("SELECT id FROM schemalocation WHERE schCode = '{0}'", databaseName);
using (var reader = db.Query(query))
{
while (reader.Read())
{
databaseId = Convert.ToInt32(reader.GetValue(0));
}
}
// Reassign query
query = String.Format("SELECT accCompanyName FROM account WHERE id = {0}", databaseId);
using (var reader = db.Query(query))
{
while (reader.Read())
{
result = reader.GetString(0);
}
}
return result;
}
