| Author |
|
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 14 2014 at 14:22 | IP Logged
|
|
|
----- REVISION HISTORY ------
Click on Links posted in each release notice to get downloadable ZIP files.
- 12/14/14 - Orig post
- 12/16/14 - v2 uploaded to include better code commenting
- 01/03/15 - v3 uploaded. Now supports multiple charts on a web page and extensive comments in the code for easier understanding and use. 2015-01-07_133557_Charting-v3.zip (Bad. Don't use.)
- 01/07/15 - v4 Corrects period roll-over failure with v3 code resulting in no distinction between new/old data points. Also added extensive comments to "SQL UPDATE_DAY_TBL" macro to explain more, especially the concept of "binning." NOTE: The CSV files included herein are not representative of "full" operation. They have been capture early in a period cycle, and are thus quite incomplete, but they demonstrate the principles. You will have to look thru the entire files to find the actual data partial captures. 2015-01-07_133724_Charting-v4.zip
- 08/09/16 - The js executable has an initial set of supporting software links, one of which is a javascript CVS parser that has disappeared from its web based file archive. To fix this error, go to ...
"https://github.com/evanplaice/jquery-csv/blob/master/src/jq uery.csv.js"
and download that file to your computer and save it in with the other files supporting the PH web server. Then change the chartTemp.html file contents to point to your file, rather than the old web link.
The new js sw preload section should now look like this, where the last entry is the file you just saved to your computer...
<script src="https://www.google.com/jsapi"> </script>
<script src="http://code.jquery.com/jquery-1.10.1.min.js"> </script>
<script src="jquery.csv.js"></script>
------------------------------
Over the years, there have been numerous requests by users to have some kind of charting available from PH.
Like many of you, I have bugged Dave several times to look at this issue, but there is only one of him and an almost insurmountable work load to keep up with all the new PH development needs, and keep us all happy. 
For several years I have been using "LogTemp" (a free Windows 1-wire charting Utility by MRSoft) ---> http://www.mrsoft.fi/ohj01en.htm) but while I found it could do the charting I wanted, it could only do so with connected 1-wire devices. I wanted more charting breadth than just to monitor 1-wire devices, so I could plot weather trends, my thermostat temps on different house floors, etc.
So, long story short, I bit the bullet a month ago and started an effort to find and/or develop a solution.
With some occasional generous SQL assistance from Dave, lots of research, and pushing my limits with javaScript, I finally achieved what I think is a reasonably useful and extraordinary capability, with only moderate complexity.
The breakthrough came with the discovery of the remarkable Google Chart APIs.
Introductory documentation is available here...
https://google-developers.appspot.com/chart/interactive/docs /index
and a wonderful Help Forum with quick responses and terrific mentors here...
https://groups.google.com/forum/#!forum/google-visualization -api
Using the Google API, coupled with a very moderate amount of javaScrip I have been able to now display graphs of any numeric values that PH can access.
As an aside, what is documented below is for Line Charting, but the Google Chart API is astoundingly capable, allowing for the creation of line, pie, donut, column, area, scatter, and more charts. You can even create these cool Gauges...
References below to specific files refer to files of the same name located in the zip file link included below.
Here is what is displayed, when I click on the "Temperatures" button on my PH Web Center Home Page.
There are three charts, one each for a Day, a Week, and Month. Since development work is still going on in an attempt to include the new PH thermostat and the 1-wire support in the full solution, these charts are a bit messy at the moment, as PH gets re=booted after major changes and Timed Event data samples are missed, or code goes amuck. I WILL update once some nice stable looking charts are produced. 
The ZIP files referenced in the Release Information above contain the following information, to serve as examples for your efforts...
- SQL CREATE_DAY_TABLE.sql: A PH Macro to initialize the DAY table area. On the very first use the Delete All initial SQL formula can be enabled (normally disabled) to produce an empty database Table, This table covers 24 hours and has 6-bins (10 min slots) within each hour. The DAY data is all keyed as Type-1.
- SQL CREATE_WEEK_TABLE.sql: A PH Macro to Initialize the time bin slots for the WEEK data. The WEEK data is all keyed as Type-2 and the time scale runs from day 0 (Sunday) to day 6.83 (Sat last bin before midnight) with data samples taken every hour (24-bins/day)
- SQL CREATE_MONTH_TABLE.sql: A PH Macro to Initialize the time bin slots for the MONTH data. The MONTH data is all keyed as Type-3 and the time scale runs from day 1 to just before midnight of the last day of the Month (whatever it is) with data samples taken every 4 hours (6-bins/day)
- SQL UPDATE_DAY_TBL: The PH Macro that gathers the daily data and writes it to the PH userData2 database table. Called by a Timed Event that fires every 10 minutes.
- SQL UPDATE_WEEK_TBL: The PH Macro that gathers the weekly data and writes it to the PH userData2 database table. Called by a Timed Event that fires every hour.
- SQL UPDATE_MONTH_TBL: The PH Macro that gathers the monthly data and writes it to the PH userData2 database table. Called by a Timed Event that fires every 4 hours.
- chartTemps.html: The web page that presents the charts using the Google Charts API. This page is almost entirely javaScript, but it is heavily commented to help it be as understandable as possible. This web page is constructed to create the three charts using a single function, drawChart(), and passing parameters to it in the call, that define the axis ranges, various labels, input csv file, etc.
- tempsDay.csv: The CSV file output that drives the Google Day Chart creation. This file is written by SQL UPDATE_DAY_TBL every time it is run by its Timed Event.
- tempsWeek.csv: The CSV file output that drives the Google Week Chart creation. This file is written by SQL UPDATE_WEEK_TBL every time it is run by its Timed Event.
- tempsMonth.csv: The CSV file output that drives the Google Month Chart creation. This file is written by SQL UPDATE_MONTH_TBL every time it is run by its Timed Event.
All of the database management is done using PowerHome standard SQL functions, against one of the predefined database tables named "userData2". See this Forum link for information on that table (search for "userdata2" to find the info in this large Thread). It is on page 1..
http://www.power-home.com/forum/forum_posts.asp?TID=2723&KW= userdata2
My charting uses the following columns in the PH userData2 db Table ...
NOTE: The Charts show a sensor's data line as solid (for current) and dashed (for historic) data. That way you can see what was happening, at the current time in the period before, to identify any deviations from expected norms. This is handled in the Google Chart API by using SQL to move the paired (new/old) data from the left pair-column to the right column at the beginning of a new charting period (ie, midnight for the DAY chart). The Google Chart visualization (on the chartTemps.html page) charts left column data as "solid" and right column data as "dashed".
The contents of these columns is as follows...
- Uid - Sequentially numbered as a unique index key
- Type - I'm using "1" to mark this as a "Day" table, since I will have "Week" and "Month" and maybe even "Year" tables ( Type=2,3,4) as I replace the LogTemp charts.
- idstring: Not used
- idnum: Not used
- iddate - not used
- valstring1: Time expressed as a number. Required to make Google Charts use a continuous,, rather than discreet X-Axis so that data is displayed in the correct time position even if there are missing data points. The UPDATE Macro converts true time into discrete numeric bins so that everything is standardized. See above comment about my 10 minute (1/6th hour) time increments.
- valstring2: Holds the current (new) data, since 00:00 hours up to the current viewing time.
- valstring3: This column holds the historic data. This Day view is a 24 hour cyclic buffer. So if the current snapshot was at 2PM all of the day from 00:00 - 14:00 hours would be in the Valstring1 column (and displayed as a solid line) while the data from 2PM to midnight would all appear in the Valstring2 column, since that is historic (yesterday's data).}
- No other columns are used for Charting data but valnum1 and valnum2 are used as a ring buffer to compute long term average temps for the Frig and Frzr. This need will disappear with the release of PH 2.1.5c which will have averaging built into the Analog I/O Devices support.
============================
OK. All the background stuff is defined, how do you use this?
Here are the steps to set it all up.
1. Create Timed Events to grab data at your desired time intervals and call the SQL Table updating Macros.
2. Create a web link from your Web Center page to chartTemps.html
That should not only get you started, but well on your way! I will try to help any interested users as much as I can.
Edited by GadgetGuy - August 09 2016 at 18:00
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
TonyNo
Moderator Group
Joined: December 05 2001
Location: United States
Online Status: Offline
Posts: 2889
|
| Posted: December 14 2014 at 17:33 | IP Logged
|
|
|
Very cool! Need to try this out.
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: December 14 2014 at 18:28 | IP Logged
|
|
|
I kinda thought you might get excited TonyNo.
Now just waiting for the BeachBum to comment (if he is watching the Forum).
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
BeachBum
Super User
Joined: April 11 2007
Location: United States
Online Status: Offline
Posts: 1880
|
| Posted: April 16 2015 at 10:49 | IP Logged
|
|
|
I may be a day late and a dollar short but you have exceeded all my expectations. Very very cool...
__________________
Pete - X10 Oldie
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: April 16 2015 at 11:05 | IP Logged
|
|
|
Thanks Pete.
Hey, after spending the winter here, we finally decided that your home
state is pretty nifty and so are selling our Michigan digs and moving to
FL as soon as possible.
Looks like I'll have to start automating all over again!
:-)
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
BeachBum
Super User
Joined: April 11 2007
Location: United States
Online Status: Offline
Posts: 1880
|
| Posted: May 06 2015 at 21:22 | IP Logged
|
|
|
Where and when do tell ???
__________________
Pete - X10 Oldie
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: August 07 2016 at 07:39 | IP Logged
|
|
|
BeachBum wrote:
| Where and when do tell ??? |
|
|
Talk about being "Late and Short", I just saw your
comment. [:-)}
We moved to FL in Sep 2016 and live in The Villages
(America's Friendliest Home Town). Lovin' it here and
even got thru our first summer with no problem. No
where near as hot/humid as everyone said it would be.
I'm now thinking this "Terrible Florida Summers" thing
is just a ploy to keep everyone from coming down here
from up North. [:-)} Now live only 20 minutes away
from Dave.
Edited by GadgetGuy - August 26 2016 at 14:27
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
BeachBum
Super User
Joined: April 11 2007
Location: United States
Online Status: Offline
Posts: 1880
|
| Posted: August 07 2016 at 09:00 | IP Logged
|
|
|
Poor Dave.... I was beginning to worry about you as the forum has been too quiet.
__________________
Pete - X10 Oldie
|
| Back to Top |
|
| |
GadgetGuy
Super User
Joined: June 01 2008
Location: United States
Online Status: Offline
Posts: 942
|
| Posted: August 09 2016 at 17:42 | IP Logged
|
|
|
Ha Peter, LOL -
When I saw the Forum Posting last night, I was worried as I haven't looked at the Charting JavaScript in over a year and wasn't sure I could answer any questions about it.
But then I decided this morning to chart the temps in my Wine Room and had to "dust off" my rusty knowledge.
While I am pretty much back up to speed, I was still relieved to discover that your post didn't demand a lot of technical expertise! :-)
I have been slow to automate our new home here in FL. The Elk security system I purchased to replace our ADT unit has been sitting on the floor for 8 months while I keep renting the old system. Go figure.
Slowly getting things going though. This home is smaller than our MI hacienda and is a Insteon dream. Nothing is more than 40' from the breaker panel. I have never seen such reliable Insteon transmission reports. Does it get any better than this?
I'm having fun and have learned enough over the years now, to self extricate from my new issues, thus the calmness in the Forum.
If it gets too cold up north there for you in the months ahead, you'll have to come down and visit.
We have a guest room and lots of good things to do and eat within just 2 blocks of our home.
__________________
Ken B - Live every day like it's your last. Eventually, you'll get it right!
|
| Back to Top |
|
| |
|
|
Active Topics 
Memberlist 
Search 
Help
Register 
Login
PowerHome Messageboard : PowerHome Macros, Scripts, and Formulas Repository
Topic: Full CHarting Capability with PH
