HomeGenie Forum

Automation Program Plugins and Wizard Scripting => Help => Topic started by: raja_tamilarasan on March 09, 2016, 10:20:27 PM

Title: First Post - Statistics / graph from MySQL Database
Post by: raja_tamilarasan on March 09, 2016, 10:20:27 PM

Dear HG Forum,
This is my first post and hence please bear with me while I ramp up on HG.

I have successfully integrated HG with a few Z-Wave based sensors.
I also have some existing sensors that are dumping data to a MySQL database. I would like to add a widget on my home screen to visualize the data from my MySQL database. Could you please advise me on how to do that ?

Database schema :

# Structure for table "sensordata"
#

DROP TABLE IF EXISTS `sensordata`;
CREATE TABLE `sensordata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gatewayid` int(11) DEFAULT NULL,
  `temperature` int(11) DEFAULT NULL,
  `light` int(11) DEFAULT NULL,
  `date_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

# Structure for table "weather"
#

DROP TABLE IF EXISTS `weather`;
CREATE TABLE `weather` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NULL,
  `longitude` int(11) DEFAULT NULL,
  `latitude` int(11) DEFAULT NULL,
  `temperature` int(11) DEFAULT NULL,
  `temperature_min` int(11) DEFAULT NULL,
  `temperature_max` int(11) DEFAULT NULL,
  `zipcode` int(11) DEFAULT NULL,
  `humidity` int(11) DEFAULT NULL,
  `pressure` int(11) DEFAULT NULL,
  `wind_speed` int(11) DEFAULT NULL,
  `wind_deg` int(11) DEFAULT NULL,
  `clouds` char(16) DEFAULT NULL,
  `name` char(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=801 DEFAULT CHARSET=utf8;

Title: Re: First Post - Statistics / graph from MySQL Database
Post by: bkenobi on March 10, 2016, 12:14:42 AM

If you use the built-in statistics database, you might be able to use the widget that is provided with the HG installation.  I'm not sure if that suits your needs.  Alternately, you could use the existing widget as a template for your new custom widget.

Title: Re: First Post - Statistics / graph from MySQL Database
Post by: raja_tamilarasan on March 10, 2016, 01:42:15 AM

I am using the built-in database for my Z-Wave sensors, but I would also like to showcase the data coming from my MySQL database.
So that I have two charts one for Z-Wave senors ( for example switch on / off times)

Second would be the data coming from my MySQL Database (for example, current value of temperature sensor)

Title: Re: First Post - Statistics / graph from MySQL Database
Post by: bkenobi on March 10, 2016, 04:24:50 AM

This won't be what you want, but the only thing I can suggest is to write the values from your second database into the HG database.  If you create a virtual module for the data you want stored, you can then use the built-in charting.

Title: Re: First Post - Statistics / graph from MySQL Database
Post by: raja_tamilarasan on March 10, 2016, 07:25:51 AM

Well that might actually work, I can change my existing scripts to write to the home geneie database. Given the above database definiton, could you please give me steps to create a new widget to show statistics for one of my tables ?

Title: Re: First Post - Statistics / graph from MySQL Database
Post by: kevin1 on March 10, 2016, 12:44:54 PM

The existing chart widget is created specifically for displaying energy consumption from what I can remember.  There is no way to configure what data it shows, so the corresponding widget would have to be significantly modified or copied as starting point for a new widget.  Add it to dashboard like this:

configure-groups-dashboard, action-add module, energy monitor

You can use the Statistics feature to view charts of the HG data as well.

Title: Re: First Post - Statistics / graph from MySQL Database
Post by: bkenobi on March 10, 2016, 04:35:05 PM

Correct.  Out of the box, HG will display any data you write to a sensor type widget (and a couple others, but I don't remember which).  If you create a virtual module of type sensor and write your temperature, power usage, etc to them, you can see the data in the statistics page.  However, the built-in widget will only display energy in the way it looks now.  If you want it to look different or display different data, I would think you could do that by using the stock widget as a template.  I don't know how complicated modifying it would be as I don't know widgets.  They are html and javascript I believe so if you know those languages, you should be able to work your way through the modification.

Title: Re: First Post - Statistics / graph from MySQL Database
Post by: raja_tamilarasan on March 10, 2016, 09:25:29 PM

Ok, I have now changed my code to dump data to the homegenie_stats.db database. While they add values in the form Sensor.PhidgetTemp and Sensor.PhidgetLight, I am unable to see these values in the statistics drop down.

What could  I be missing.

Title: Re: First Post - Statistics / graph from MySQL Database
Post by: [email protected] on March 11, 2016, 01:10:29 AM

Dunno but I'd love to know,  never Sussed out the stats

Title: Re: First Post - Statistics / graph from MySQL Database
Post by: bkenobi on March 12, 2016, 06:29:26 PM

I'm not sure why the data isn't available.  In my code, I write data to locations like: Sensor.Temperature, Sensor.Windspeed, Sensor.Humidity, etc.  All of these show up in the statistics option menu.

Title: Re: First Post - Statistics / graph from MySQL Database
Post by: dani on March 12, 2016, 07:13:04 PM

To see statistics in the dropdown list each data must be linked to a valid module Reel or Virtual.