Пример #1
0
        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;
        }
Пример #2
0
        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;
        }
Пример #3
0
        /// <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");
        }
Пример #4
0
        /// <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;
        }
Пример #5
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;
        }
Пример #6
0
        /// <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;
        }
Пример #7
0
        /// <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));
                }
            }
        }
Пример #8
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;
        }
Пример #9
0
        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);
        }
Пример #10
0
        /// <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;
        }
Пример #11
0
        /// <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));
                }
            }
        }
Пример #12
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);
        }
Пример #13
0
        /// <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);
        }
Пример #14
0
 /*********************************************************
  * DELETE STATEMENTS
  *********************************************************/
 public void DeleteImport(int id)
 {
     Database db = new Database(this.newDatabase);
     db.Delete("import", id);
 }
Пример #15
0
        /// <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;
        }
Пример #16
0
        /// <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;
        }
Пример #17
0
        /// <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;
        }
Пример #18
0
        /// <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());
            }
        }
Пример #19
0
        /// <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;
        }
Пример #20
0
        /// <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);
                }
            }
        }
Пример #21
0
        /// <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);
        }
Пример #22
0
        /// <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;

                }
            }
        }
Пример #23
0
        /// <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;
        }
Пример #24
0
        /// <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);
        }
Пример #25
0
        /// <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;
        }
Пример #26
0
        /// <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;
        }
Пример #27
0
        /// <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();
                }
            }
        }
Пример #28
0
        /// <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;
        }
Пример #29
0
        /// <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);
        }
Пример #30
0
        /// <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;
        }