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>
/// 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>
/// Gets all the id numbers for all welfare checks in database
/// </summary>
/// <returns>Returns an array of all welfare check Ids</returns>
public List<int[]> GetAllWelfareIds()
{
List<int[]> list = new List<int[]>();
Database db = new Database(this.newDatabase);
string query = @"SELECT welfarecheck.id,
welfarecheck.site_id,
region.branch_id,
branch.zone_id
FROM welfarecheck
LEFT JOIN site ON welfarecheck.site_id = site.id
LEFT JOIN region ON site.region_id = region.id
LEFT JOIN branch ON region.branch_id = branch.id
WHERE site.sitWelfareActive = 1";
using (var reader = db.Query(query))
while (reader.Read())
{
int[] array = new int[4] {
Convert.ToInt32(reader.GetValue(0)),
Convert.ToInt32(reader.GetValue(1)),
Convert.ToInt32(reader.GetValue(2)),
Convert.ToInt32(reader.GetValue(3))
};
list.Add(array);
}
return list;
}
/// <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>
/// Gets the report name by the content Id
/// </summary>
/// <param name="contentId"></param>
/// <returns></returns>
public string GetReportNameByContentId(int contentId)
{
string result = null;
Database db = new Database("ug12_maindb");
string query = String.Format(@"SELECT conTitle FROM content WHERE id = {0}", contentId.ToString());
using (var reader = db.Query(query))
while (reader.Read())
{
result = reader.GetString(0);
}
return result;
}
/// <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>
/// 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>
/// 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>
/// Gets the branch id for a particular recorder
/// </summary>
/// <param name="recorderSerial">Gets the recorder serial</param>
/// <returns>Branch Id for a recorder</returns>
private int GetBranchIdForRecorderSerial(string recorderSerial)
{
int output = 1;
Database db = new Database(this.newDatabase);
string query = String.Format("SELECT branch_id FROM recorder WHERE recRSN = {0}", recorderSerial);
// Initialize reader
using (var reader = db.Query(query))
while (reader.Read())
{
output = Convert.ToInt32(reader.GetValue(0));
}
return output;
}
/// <summary>
/// Returns a list of all databases
/// </summary>
/// <returns></returns>
public static string[] GetAllDatabases()
{
List<string> list = new List<string>();
Database db = new Database();
try
{
using (var reader = db.Query("SELECT schCode FROM schemalocation"))
while (reader.Read())
{
list.Add(reader.GetString(0));
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
return list.Count > 0 ? list.ToArray() : null;
}
/// <summary>
/// Gets the zone id for current database
/// </summary>
/// <returns>Returns zone.id</returns>
public int GetZoneId(int branchId = 0)
{
int output = 0;
string query = branchId != 0 ?
String.Format("SELECT zone_id FROM branch WHERE id = {0}", branchId) :
String.Format("SELECT zone_id FROM branch ORDER BY id ASC LIMIT 1;");
Database db = new Database(this.newDatabase);
using (var reader = db.Query(query))
while (reader.Read())
{
output = Convert.ToInt32(reader.GetValue(0));
}
return output;
}
public double GetZoneOffset(int zoneId = 38)
{
double offset = 0;
string query;
query = String.Format(
@"SELECT timezone.gmt_offset
FROM timezone
JOIN zone ON timezone.zone_id = zone.id
WHERE timezone.time_start < UNIX_TIMESTAMP(UTC_TIMESTAMP())
AND zone.id = {0}
ORDER BY timezone.time_start DESC LIMIT 1", zoneId);
Database db = new Database();
using (var reader = db.Query(query))
{
while (reader.Read())
{
offset = Convert.ToInt32(reader.GetValue(0));
}
}
return offset;
}
public string GetScheduledTaskName(int taskId)
{
string output = null;
Database db = new Database(this.newDatabase);
string query = String.Format("SELECT sctName FROM scheduledtask WHERE id = {0}", taskId);
using (var reader = db.Query(query))
while (reader.Read())
{
output = reader.GetValue(0).ToString();
}
return output;
}
/// <summary>
/// Returns list of emails for the selected task
/// </summary>
/// <param name="taskId">Integer task Id</param>
/// <returns></returns>
public string[] GetScheduledTaskEmailList(int taskId)
{
List<string> list = new List<string>();
Database db = new Database(this.newDatabase);
string query = String.Format(@"SELECT sceEmail FROM scheduledtaskemail WHERE scheduledtask_id = {0}", taskId);
using (var reader = db.Query(query))
while (reader.Read())
{
list.Add(reader.GetString(0));
}
return list.ToArray();
}
/// <summary>
/// Returns a string array with all the values from the scheduled task
/// </summary>
/// <param name="taskId"></param>
/// <returns></returns>
public string[] GetScheduledTask(int taskId)
{
List<string> list = new List<string>();
Database db = new Database(this.newDatabase);
string query = String.Format(@"SELECT timecycle_id, content_id, sctNextRun, sctDescription, sctEmailSubject
FROM scheduledtask WHERE scheduledtask.id = {0}", taskId);
using (var reader = db.Query(query))
while (reader.Read())
{
list.Add(reader.GetValue(0).ToString()); // timecycle_id
list.Add(reader.GetValue(1).ToString()); // content_id
list.Add(reader.GetValue(2).ToString()); // sctNextRun
list.Add(reader.GetString(3)); // sctDescription
list.Add(reader.GetString(4)); // sctEmailSubject
}
return list.ToArray();
}
/// <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>
/// 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;
}
/// <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>
/// 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>
/// 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>
/// 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>
/// Returns the Id number of the scheduled task to be run
/// </summary>
/// <returns>Returns an array of integer with the respective task ids</returns>
public int[] GetPendingScheduledTasks()
{
List<int> list = new List<int>();
Database db = new Database(this.newDatabase);
string datetime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string query = String.Format("SELECT id FROM scheduledtask WHERE sctNextRun < '{0}' AND sctActive = '1'", datetime);
using (var reader = db.Query(query))
while (reader.Read())
{
list.Add(Convert.ToInt32(reader.GetValue(0)));
}
return list.ToArray();
}
/// <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>
/// 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 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 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>
/// 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;
}
/// <summary>
/// Gets all administrators emails
/// </summary>
/// <returns></returns>
public string[] GetAdministratorEmails()
{
List<string> emailList = new List<string>();
Database db = new Database(this.newDatabase);
string query = "SELECT usrEmail FROM user WHERE userrole_id = 1";
using (var reader = db.Query(query))
while (reader.Read())
{
emailList.Add(reader.GetValue(0).ToString());
}
return emailList.ToArray();
}
