Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Charting data with CanvasJS
#1
CreateChart Macro
Version 1.1

[Image: TempGraph.png]

Description
This macro will create interactive/animated HTML5 line charts from a MySQL database for data logged with the datalog DB driver and display them in the CQC web server. The default config allows for tooltips, zooming, and turning on/off series dynamically. The macro literals can be edited for many more options. To see what all is available see the canvasjs.com website for more info.

Installation
  • Install Macro Pack
  • Download CanvasJS and install the canvasjs-min.js file under /htmlroot/script
  • Optionally, open the macro in the Macroworkshop and edit the default DSN, Username, and Password. This is so you don't have to pass those parameters on the URL.

Usage
Code:
http://cqcserver/cmlbin/user/CreateChart?Type=parm&dsn=CQCDB&series1=descriptor1&series2=descriptor2...

Parameters
  • dsn - DSN of your MySQL DB
  • user - Username of your MySQL DB
  • pass - Password of your MySQL DB
  • type - Graph Type (Min5,Min10,Min15,Min30,Hour,Day,Week,Month,Year)
  • seriesX - Descriptor you would like to chart.
  • width - Chart width in pixels or percent (i.e. 300px or 100%)
  • height - Chart height in pixels or percent
  • maxpoints - Maximum data points to display/scale graph to (default 50)
  • title - Graph title

Examples
Code:
[b]Chart 2 Series, Period = 1 Day[/b]
http://cqcserver/cmlbin/user/CreateChart?dsn=CQCDB&type=Day&series1=IndoorTemp&series2=AtticTemp

[b]Chart 2 Series, 1 Day, Size 300x600, Title Temperatures, Datapoints 100[/b]
http://cqcserver/cmlbin/user/CreateChart?dsn=CQCDB&type=Day&series1=IndoorTemp&series2=AtticTemp&title=Temperatures&maxpoints=100&width=600px&height=300px

Versions
  • Version 1.0 - Original Version (Did not function in IE or CQC Web Widget)
  • Version 1.1 - Works in IE - Fixed JS Date function to be IE Compatible


Attached Files
.cqcmacropack   CreateChart_1_1.CQCMacroPack (Size: 4.15 KB / Downloads: 3)
Wuench
My Home Theater/Automation Website

[THREAD=5957]BlueGlass CQC Config[/THREAD]
[THREAD=10624]Wuench's CQC Drivers[/THREAD]
Reply
#2
We should probably move this to the 'how to' section?
Dean Roddey
Explorans limites defectum
Reply
#3
Awesome.

Does this work with the MS SQL offering that was originally proposed for the DB Datalogger driver or just MySQL only
Mykel Koblenz
Illawarra Smart Home
Reply
#4
i don't know if it will work for other DBs or not, I am not much of a SQL guy. You might need to adjust the SQL statement. There is only one, it is a literal in the top of the macro. This doesn't actually use the driver, it accesses the DB directly, but it does assumes that the data is in the structure used by the driver of Timestamp,Value.
Wuench
My Home Theater/Automation Website

[THREAD=5957]BlueGlass CQC Config[/THREAD]
[THREAD=10624]Wuench's CQC Drivers[/THREAD]
Reply
#5
Well, maybe jumped the gun. It isn't working correctly in IE and therefor doesn't work in a web widget either. Guess i'll have to do some tweaking....

EDIT: Ok, fixed with version 1.1. Looks like IE isn't capable of converting the raw date string in the format stored in the DB so I had to convert it.
Wuench
My Home Theater/Automation Website

[THREAD=5957]BlueGlass CQC Config[/THREAD]
[THREAD=10624]Wuench's CQC Drivers[/THREAD]
Reply
#6
Dean Roddey Wrote:We should probably move this to the 'how to' section?

Yeah, sorry. Feel free to move it...
Wuench
My Home Theater/Automation Website

[THREAD=5957]BlueGlass CQC Config[/THREAD]
[THREAD=10624]Wuench's CQC Drivers[/THREAD]
Reply
#7
I tried this with MSSQL and got an error

Code:
Server: CQC Web Server
Connection: Close
Date: Mon, Aug 31 21:53:13 2015 +1000
Content-Type: text/html
Content-Length: 363

<h1>An error has occurred creating the graph...</h1>
01/01 00:00:00-Zeus01, CQCWebSrv, CQCWebSrvWorkerThread1
{
    CIDDBase, CIDDBase_DirectStatement.cpp.88, Failed/Cannot Do, Error: 6502/65535/195
    Direct statement execution failed for statement
    [Microsoft][ODBC SQL Server Driver][SQL Server]'TO_SECONDS' is not a recognized built-in function name.
}


Also, are spaces in the DSN and/or descriptor supported. I couldn't get the browser to return anything when I used a DSN with a space. I created a second DSN [no spaces] and it worked. The descriptor seems to work, but I get the above error.
Mykel Koblenz
Illawarra Smart Home
Reply
#8
I don't know, I don't have any in mine. You could try putting tick marks around the DSN in your SQL statement and see if that fixes it.

Oh sorry looks like the DSN is a part of the connect statement not in the SQL. So I guess that is a Dean question. I am not sure. Looking at your error, it looks like it doesn't like the TO_SECONDS. Basically what that statement is doing is converting everything to seconds then doing a little math, so it will go back last day, last month, etc in seconds. Maybe MSSQL needs a different function or even a different approach.
Wuench
My Home Theater/Automation Website

[THREAD=5957]BlueGlass CQC Config[/THREAD]
[THREAD=10624]Wuench's CQC Drivers[/THREAD]
Reply
#9
I'm pretty familiar with MS SQL, and I definitely want to give this a try. I'll report back on what I find, although I may not have time to play with it until the weekend.
Reply
#10
Totally forgot about this thread.

Was any progress made in getting it to work with MS SQL?

Mick
Mykel Koblenz
Illawarra Smart Home
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  GPS Data from Traccar to MQTT simplextech 0 130 02-01-2020, 07:27 PM
Last Post: simplextech
  Data Log DB and Graphamatic Again The Camper 2 2,205 07-14-2011, 07:07 PM
Last Post: Dean Roddey
  Data from Personal Weather Stations George M 6 2,510 04-25-2010, 01:30 PM
Last Post: Sendero
  Newbie Question: How do I get event data for triggered events? Sendero 7 2,314 02-10-2009, 11:26 PM
Last Post: Dean Roddey
  Russound CAV 2-way/meta-data integration netarc 7 2,223 12-02-2006, 02:59 PM
Last Post: Dean Roddey

Forum Jump:


Users browsing this thread: 1 Guest(s)