Kevin's Week 6 Report

This week I was able to get some data from the serial port to go to the database and show up on the graph.  I met with Andrew and Ashley and we were able to to do some initial testing through the serial port.  We determined that Ashely was reading a consistent "6" from the ADE7763 chip through SecureCRT through her computer.  This number is not the final number we should expect, but it was fine for testing whether or not the PC application was reading in numbers correctly.  So I modified my program to accept serial data from our test circuit.  As can be seen in the screenshots below, we were able to successfully read in a "6" from the serial port and display it on the graph.  The SnesorAvgVal's that contain a "7" are from when Ashley hardcoded a "7" to the serial port, so that we could see the change on the graph.  Below the screenshots is my updated code for getting data to the database.  Now that we have confirmed that the database accepts and correctly shoes data from the serial port, I have to set up the program to read from Ethernet.  I will have to make sure Ashley and I are on the same page on how we are sending and requesting information.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading;

using MySql.Data.MySqlClient;

using System.Net;

using System.IO.Ports;

namespace SensorTool

{

    class SensorThread

    {

        private string name;

        private string address;

        private string connStr;

        private Form1 form;

        public SerialPort serialPort;

        public SensorThread(string sensorName, string sensorAddress, string connection, 

            Form1 userForm)

        {

            name = sensorName;

            address = sensorAddress;

            form = userForm;

            connStr = connection;

        }

        public void startThread()

        {

            

            MySqlConnection dbCon = new MySqlConnection(connStr);

            DateTime currTime=DateTime.Now;

            int secondTag = currTime.Second;

            int minuteTag = currTime.Minute;

            int dayTag = currTime.Day;

            serialPort = new SerialPort("COM4");

            serialPort.BaudRate = 9600;

            serialPort.Parity = Parity.None;

            serialPort.DataBits = 8;

            serialPort.StopBits = StopBits.Two;

            while (true)

            {

                currTime = DateTime.Now;

                if (secondTag != currTime.Second)       //changed to == from !=

                { 

                    //Store second

                    //Read Sensor

                    String sensorValue = "";

                    try

                    {

                        //WebClient client = new WebClient();

                        //sensorValue = client.DownloadString(address);

                        //possible serial testing

                         if(!(serialPort.IsOpen==true)) serialPort.Open();

                          serialPort.Write("p");

                          sensorValue=Convert.ToString(serialPort.ReadChar());

                          //sensorValue2 = sensorValue2 + 10;

                         // sensorValue = Convert.ToString(sensorValue2);

                          serialPort.Close();

                         

                    }

                    catch (Exception)

                    {

                        string error = "Unable to read " + name;

                        form.debug.BeginInvoke(

                            new Form1.DebugCallback(form.UpdateDebugOutput), error);

                    }

                    //Write to Database

                    try

                    {

                        DBnonQuery("INSERT INTO DataTemp VALUES (NOW(), '"

                            + name + "', " + sensorValue + ")");

                    }

                    catch (Exception)

                    {

                        string error = "Unable to write " + name + "'s value to database";

                        form.debug.BeginInvoke(

                            new Form1.DebugCallback(form.UpdateDebugOutput), error);

                    }

                    secondTag = currTime.Second;

                }

                if (minuteTag != currTime.Minute)       //changed to == from !=

                {

                    // Avg prev minute

                    averageSeconds(currTime.AddMinutes(-1));

                    clearSeconds();

                    minuteTag = currTime.Minute;

                }

                if (dayTag != currTime.Day)             //changed to == from !=

                {

                    // Avg prev day

                    averageMinutes(currTime.AddDays(-1));

                    dayTag = currTime.Day;

                }

                Thread.Sleep(500);

            }

        }

        private void averageSeconds(DateTime prevTime)

        {

            MySqlConnection dbCon = new MySqlConnection(connStr);

            dbCon.Open();

            string query = "SELECT SensorVal FROM DataTemp WHERE SensorID='" + name

                + "' AND YEAR(SensorTS)='" + prevTime.Year

                + "' AND MONTH(SensorTS)='" + prevTime.Month

                + "' AND DAY(SensorTS)= '" + prevTime.Day

                + "' AND HOUR(SensorTS)= '" + prevTime.Hour

                + "' AND MINUTE(SensorTS)= '" + prevTime.Minute + "'";

            MySqlCommand dbCmd = new MySqlCommand(query, dbCon);

            MySqlDataReader dbDr = dbCmd.ExecuteReader();

            int total = 0;

            int count = 0;

            while (dbDr.Read())

            {

                total += Int32.Parse(dbDr.GetString(0));

                count++;

            }

            dbCon.Close();

            

            string prevTimeStr = prevTime.ToString("yyyy-MM-dd HH:mm:00");

            DBnonQuery("INSERT INTO DataMinute VALUES( '" + prevTimeStr

                + "', '" + name + "', " + (total / count) + ")");

            string msg = " MinuteAverage Count: " + count.ToString() + "  Total: "

                + total.ToString() + "  Average: " + (total / count).ToString();

            form.debug.BeginInvoke(

                new Form1.DebugCallback(form.UpdateDebugOutput), msg);

        }

        private void averageMinutes( DateTime prevTime )

        {

            MySqlConnection dbCon = new MySqlConnection(connStr);

            dbCon.Open();

            string query = "SELECT SensorAvgVal FROM DataMinute WHERE SensorID='" + name

                + "' AND YEAR(MinuteTS)='" + prevTime.Year

                + "' AND MONTH(MinuteTS)='" + prevTime.Month

                + "' AND DAY(MinuteTS)= '" + prevTime.Day + "'";

            MySqlCommand dbCmd = new MySqlCommand(query, dbCon);

            MySqlDataReader dbDr = dbCmd.ExecuteReader();

            int total = 0;

            int count = 0;

            while (dbDr.Read())

            {

                total += Int32.Parse(dbDr.GetString(0));

                count++;

            }

            dbCon.Close();

            string prevTimeStr = prevTime.ToString("yyyy-MM-dd 00:00:00");

            DBnonQuery("INSERT INTO DataDay VALUES( '" + prevTimeStr

                + "', '" + name + "', " + (total / count) + ")");

            string msg = " DayAverage Count: " + count.ToString() + "  Total: "

                + total.ToString() + "  Average: " + (total / count).ToString();

            form.debug.BeginInvoke(

                new Form1.DebugCallback(form.UpdateDebugOutput), msg);

        }

        private void clearSeconds()

        {

            DBnonQuery("DELETE FROM DataTemp WHERE SensorID='" + name + "'");

            string msg = "Cleaned DATATEMP of " + name;

            form.debug.BeginInvoke(

                new Form1.DebugCallback(form.UpdateDebugOutput), msg);

        }

        private void DBnonQuery(string query)

        {

            MySqlConnection dbCon = new MySqlConnection(connStr);

            dbCon.Open();

            MySqlCommand dbCmd = new MySqlCommand(query, dbCon);

            dbCmd.ExecuteReader();

            dbCon.Close();

        }

    }

}

I also was able to work more on the radio buttons with navigating different time windows.  I have right now been able to successfully get some of the buttons to take me to the current time period selected.  For example, if I click on the "Hour" radio button and hit the newly added "Submit" button, it will take me to the current hour window.  Starting from the time frame in the screenshot above, and it took me to this window:

The current time is 1:24, so I can see that this button is working.  The data is from the 30-50 range again because I set it back up to collect random data since I am not connected to the circuit now.  I am still working on how to get the drop down buttons to display information from the database.  Below is my up to date code for the graphing website.

For next week, I want to have some test Ethernet code for getting data from the circuit so we can see if that part takes data in correctly.  I also want to have the drop down buttons getting data from the database and displaying them to the user.

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Web.UI.DataVisualization.Charting;

using System.Xml.Linq;

using MySql.Data.MySqlClient;

namespace SensorDataSite

{

    public partial class Data : System.Web.UI.Page

    {

        DateTime dt = DateTime.Now;

        protected void Page_Load(object sender, EventArgs e)

        {

            chkSensors.Items.Add("Sensor1");

            if (Request.QueryString["Type"] == null)

            {

                //No info for a particular date, use default

                //DateTime dt = DateTime.Now;

                Response.Redirect("/Data.aspx?Type=hour&Year=" + dt.Year.ToString()

                    + "&Month=" + dt.Month.ToString()

                    + "&Day=" + dt.Day.ToString()

                    + "&Hour=" + dt.Hour.ToString() );

                

            }

            else

            {

                string query = "";

                int year = (Request.QueryString["Year"] != null ? Int32.Parse(Request.QueryString["Year"]) : 0);

                int month = (Request.QueryString["Month"] != null ? Int32.Parse(Request.QueryString["Month"]) : 0);

                int day = (Request.QueryString["Day"] != null ? Int32.Parse(Request.QueryString["Day"]) : 0);

                int hour = (Request.QueryString["Hour"] != null ? Int32.Parse(Request.QueryString["Hour"]) : 0);

                DateTime viewDate = new DateTime(year, month, day, hour, 0, 0);

                switch (Request.QueryString["Type"].ToLower())

                {

                    case "hour":

                        query = createHourQuery("Sensor1", year, month, day, hour);

                        setHourView(year, month, day, hour);

                        lblTitle.Text = "Hour View - " + viewDate.ToString("MM/dd/yyyy h tt");

                        break;

                    

                    case "day":

                        query = createDayQuery("Sensor1", year, month, day);

                        setDayView(year, month, day);

                        lblTitle.Text = "Day View - " + viewDate.ToString("MM/dd/yyyy");

                        break;

                    case "month":

                        query = createMonthQuery("Sensor1", year, month);

                        setMonthView(year, month);

                        lblTitle.Text = "Month View - " + viewDate.ToString("MMMM yyyy");

                        break;

                    case "year":

                        query = createYearQuery("Sensor1", year);

                        setYearView(year);

                        lblTitle.Text = "Year View - " + viewDate.ToString("yyyy");

                        break;

                }

                populateChart(query, Chart1.Series[0]);

            }

        }

        private void populateChart(string query, Series chartSeries)

        {

            MySqlConnection dbCon = new MySqlConnection(ConfigurationManager.ConnectionStrings["DataDB"].ToString());

            dbCon.Open();

            

            MySqlCommand dbCmd = new MySqlCommand(query, dbCon);

            MySqlDataReader dbDr = dbCmd.ExecuteReader();

            while (dbDr.Read())

            {

                chartSeries.Points.AddXY(dbDr.GetDateTime(1).ToOADate(), dbDr.GetString(0));

            }

            

            dbCon.Close();

        }

        //Create queries

        private string createHourQuery(string name, int year, int month, int day, int hour)

        {

            string query = "SELECT SensorAvgVal, MinuteTS FROM dataminute WHERE SensorID='" 

                + name + "' AND YEAR(MinuteTS)="

                + year.ToString() + " AND MONTH(MinuteTS)="

                + month.ToString() + " AND DAY(MinuteTS)="

                + day.ToString() + " AND HOUR(MinuteTS)="

                + hour.ToString() + " ORDER BY MinuteTS";

            return query;

        }

        private string createDayQuery(string name, int year, int month, int day)

        {

            string query = "SELECT SensorAvgVal, MinuteTS FROM dataminute WHERE SensorID='"

                + name + "' AND YEAR(MinuteTS)="

                + year.ToString() + " AND MONTH(MinuteTS)="

                + month.ToString() + " AND DAY(MinuteTS)="

                + day.ToString() + " ORDER BY MinuteTS";

            return query;

        }

        private string createMonthQuery(string name, int year, int month)

        {

            string query = "SELECT SensorAvgVal, DayTS FROM dataday WHERE SensorID='"

                + name + "' AND YEAR(DayTS)="

                + year.ToString() + " AND MONTH(DayTS)="

                + month.ToString() + " ORDER BY DayTS";

            return query;

        }

        private string createYearQuery(string name, int year)

        {

            string query = "SELECT SensorAvgVal, DayTS FROM dataday WHERE SensorID='"

                + name + "' AND YEAR(DayTS)="

                + year.ToString() + " ORDER BY DayTS";

            return query;

        }

        // Set up all four views

        private void setHourView(int year, int month, int day, int hour)

        {

            DateTime dtS = new DateTime(year, month, day, hour, 0, 0);

            DateTime dtE = dtS.AddHours(1);

            setUpView( dtS.Year, dtS.Month, dtS.Day, dtS.Hour,

                dtE.Year, dtE.Month, dtE.Day, dtE.Hour, 

                ChartValueType.Time);

        }

        private void setDayView(int year, int month, int day)

        {

            DateTime dtS = new DateTime(year, month, day, 0, 0, 0);

            DateTime dtE = dtS.AddDays(1);

            setUpView(dtS.Year, dtS.Month, dtS.Day, dtS.Hour,

                dtE.Year, dtE.Month, dtE.Day, dtE.Hour,

                ChartValueType.Time);

        }

        private void setMonthView(int year, int month)

        {

            DateTime dtS = new DateTime(year, month, 1, 0, 0, 0);

            DateTime dtE = dtS.AddMonths(1);

            setUpView(dtS.Year, dtS.Month, dtS.Day, dtS.Hour,

                dtE.Year, dtE.Month, dtE.Day, dtE.Hour,

                ChartValueType.Date);

        }

        private void setYearView(int year)

        {

            DateTime dtS = new DateTime(year, 1, 1, 0, 0, 0);

            DateTime dtE = dtS.AddYears(1);

            setUpView(dtS.Year, dtS.Month, dtS.Day, dtS.Hour,

                dtE.Year, dtE.Month, dtE.Day, dtE.Hour,

                ChartValueType.Date);

        }

        private void setUpView(int start_year, int start_month, int start_day, int start_hour,

            int end_year, int end_month, int end_day, int end_hour, ChartValueType type)

        {

            Chart1.Series[0].XValueType = type;

            Chart1.ChartAreas[0].AxisX.Minimum = new DateTime(start_year, start_month,

                start_day, start_hour, 0, 0).ToOADate();

            Chart1.ChartAreas[0].AxisX.Maximum = new DateTime(end_year, end_month,

                end_day, end_hour, 0, 0).ToOADate();

        }

        private void shiftTime(int shift)

        {

            int year = (Request.QueryString["Year"] != null ? Int32.Parse(Request.QueryString["Year"]) : 0);

            int month = (Request.QueryString["Month"] != null ? Int32.Parse(Request.QueryString["Month"]) : 0);

            int day = (Request.QueryString["Day"] != null ? Int32.Parse(Request.QueryString["Day"]) : 0);

            int hour = (Request.QueryString["Hour"] != null ? Int32.Parse(Request.QueryString["Hour"]) : 0);

            DateTime dt = new DateTime(year, month, day, hour, 0, 0);

            switch (Request.QueryString["Type"])

            {

                case "year":

                    dt.AddYears(shift);

                    break;

                case "month":

                    dt = dt.AddMonths(shift);

                    break;

                case "day":

                    dt = dt.AddDays(shift);

                    break;

                case "hour":

                    dt = dt.AddHours(shift);

                    break;

            }

            string redirectPath = "/Data.aspx?Type=" + Request.QueryString["Type"];

            redirectPath += "&Year=" + dt.Year.ToString(); 

            redirectPath += "&Month=" + dt.Month.ToString(); 

            redirectPath += "&Day=" + dt.Day.ToString(); 

            redirectPath += "&Hour=" + dt.Hour.ToString(); 

            Response.Redirect(redirectPath);

        }

        protected void butNext_Click(object sender, EventArgs e)

        {

            shiftTime(1);

        }

        protected void butPrevious_Click(object sender, EventArgs e)

        {

            shiftTime(-1);

        }

        protected void submit_button_Click(object sender, EventArgs e)

        {

            if (rblDisplayType.SelectedItem.Text == "Year")

            {

                Response.Redirect("/Data.aspx?Type=hour&Year=" + dt.Year.ToString());

            }

            if (rblDisplayType.SelectedItem.Text == "Month") 

            {

                Response.Redirect("/Data.aspx?Type=hour&Year=" + dt.Year.ToString()

                    + "&Month=" + dt.Month.ToString());

            }

            if (rblDisplayType.SelectedItem.Text == "Day")

            {

                Response.Redirect("/Data.aspx?Type=hour&Year=" + dt.Year.ToString()

                    + "&Month=" + dt.Month.ToString()

                    + "&Day=" + dt.Day.ToString());

            }

            if (rblDisplayType.SelectedItem.Text == "Hour")

            {

                Response.Redirect("/Data.aspx?Type=hour&Year=" + dt.Year.ToString()

                    + "&Month=" + dt.Month.ToString()

                    + "&Day=" + dt.Day.ToString()

                    + "&Hour=" + dt.Hour.ToString());

            }

        }

    }

}