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