HSTouch and Google Charts

[Simonk]

I've been playing with Google Charts today and incorporating them into HSTouch screens, these are very easy to incorporate into your own screens and its free to use !
You just create a link to the Google Charts website with your data, labels, etc embedded in it and it returns a chart.
In the text object of a element on a screen I've put in the following code to read the link details from a device string, this will cause it to read the string from device D20 and go and get that web page/chart.

 [$SCRIPT=&hs.DeviceString("D20")]

The contents of device D20 are updated every hour by a script which creates the Google Charts link with the relevant data embedded in it that I wish to be displayed, in this example I'm showing a graph of the last 24 hours worth of electricity usage.

 http://chart.apis.google.com/chart?chtt=Electricity Usage per hour (KWh)&cht=bvg&chs=400x100chd=t:65.3999999999996,66.4999999999999,65.3000000000006,141.3,85.0999999999999,67.7,68.3,82.6000000000001,80.9000000000005,81.2999999999995,154.9,181.8&chxt=x,y&chxl=0:|08:00|09:00|10:00|11:00|12:00|13:00|14:00|15:00|16:00|17:00|18:00|19:00|1:|0|1|2

Image below is a partial screen shot of a screen that I've just dumped this chart on for now, haven't managed to figure out the transparency option yet !

Attached is a copy of the script that I run every hour.

I hope that someone finds this interesting and it's free !

[waszaa]

Hi
Just trying your script for extracting power data but cant get it to run -i have changed the ref to the RFXCOM.mdb to point to mine but get the Log error below! any ideas?


Option Strict OffImports Microsoft.VisualBasic.FileIOImports System.Data.OleDbImports System.Dataimports Schedulerimports SystemPublic Module scriptcode16#Region "Automatically generated code, do not modify"'Automatically generated code, do not modify'Event Sources Begin Public WithEvents hs As Scheduler.hsapplication Public WithEvents hsp As scheduler.hsp Public WithEvents hssystem As scheduler.phone0'Event Sources End'End of automatically generated code#End RegionDim AccessConnection As New OleDbConnectionDim oDA As OleDbDataAdapterDim oDS As New DataSetDim DataBase As String = "D:\Program Files\HomeSeer 2\Data\RFXCOM\RFXCOM.mdb"Dim MaxRows As Integer = 0Dim HighUsage As Integer = 0Dim Debug = FalseSub Main(ByVal Data As String) RunQuery("SELECT Top 13 rfxdate,rfxtime,rfxvalue FROM Power ORDER BY Id DESC") Dim WebPageString As String = "http://chart.apis.google.com/chart?chtt=Electricity Usage per hour (KWh)&cht=bvg&chs=400x100&chd=t:" Dim WebPageTime As String = "" Dim myloop For myloop = 11 To 0 Step -1 Dim rfxdate = ods.Tables("Results").Rows(myloop).Item(0) Dim rfxtime = ods.Tables("Results").Rows(myloop).Item(1) Dim rfxvalue = ods.Tables("Results").Rows(myloop).Item(2) Dim rfxUsage = rfxvalue - ods.Tables("Results").Rows(myloop + 1).Item(2) If Debug Then hs.writelog("Power Data Test", "Date : " & rfxdate & " Time : " & rfxtime & " Value : " & rfxvalue & " Usage : " & rfxUsage) WebPageString &= rfxUsage * 100 'change the values to KWh If myloop <> 0 Then WebPageString &= "," WebPageTime &= left(rfxtime, 5) & "|" If Debug Then hs.writelog("Extract Power Data", "") If Debug Then hs.writelog("Extract Power Data", "My Loop : " & myloop) If Debug Then hs.writelog("Extract Power Data", "WebPageString In Loop : " & WebPageString) If rfxUsage > HighUsage Then HighUsage = rfxUsage Next If Debug Then hs.writelog("Extract Power Data", "WebPageTime : " & WebPageTime) WebPageString &= "&chxt=x,y&chxl=0:|" & WebPageTime & "1:|" & "0|" & HighUsage / 2 & "|" & HighUsage If Debug Then hs.writelog("Extract Power Data", "WebPageString : " & WebPageString) hs.SetDeviceString("D20", WebPageString)End SubPrivate Sub RunQuery(ByVal Query As String) ' Execute all SQL commands If Debug Then hs.writelog("Extract Power Data", "Query : " & Query) Dim sConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DataBase AccessConnection.ConnectionString = sConnectString Try oDA = New OleDb.OleDbDataAdapter(Query, AccessConnection) oDS = New DataSet oDA.Fill(oDS, "Results") Catch e As Exception If Debug Then hs.writelog("Extract Power Data", e.Message) End Try MaxRows = oDS.Tables("Results").Rows.Count If Debug Then hs.writelog("Extract Power Data", "MaxRows in RunQuery: " & MaxRows)End SubEnd Module
11/15/2009 1:15:00 PM Error Script compile error: Type 'OleDbConnection' is not defined.on line 18
11/15/2009 1:14:59 PM Event Running script in background: extractpowerdata.vb

[Simonk]

I think you need to add a scripting reference to the settings.ini file in th HomeSeer HS2/Config folder. Mine is shown below, I think its's the System.Data;System.Data.dll entry that you need to add.

You also need to have device D20 set up or change it in the script to whatever you want to use.

ScriptingReferences=HSPI_SKYPE;C:\Program Files\HomeSeer HS2\hspi_skype.dll,System.ServiceProcess;System.ServiceProcess.dll,System.Drawing;System.Drawing.dll,HSPI_SIP;C:\Program Files\HomeSeer HS2\hspi_sip.dll,System.Data;System.Data.dll,System.Xml;System.Xml.dll,bc.JRMediaCenter.Entity;bc.JRMediaCenter.Entity.dll,bc.JRMediaCenter.Services;bc.JRMediaCenter.Services.dll
SADProc=True

[waszaa]

Hi

Thanks for your help - i pasted the System.Data;System.Data.dll entry but needed the ,System.Xml;System.Xml.dll as well.

Its working now so time to play with it in HStouch!

[Simonk]

I think there is a problem with the scaling of the bars on the screen with regards to the labels.

I will try and take a look tomorrow.

[Simonk]

I think there is a problem with the scaling of the bars on the screen with regards to the labels.

I will try and take a look tomorrow.

I've uploaded a new version of the script to my initial post as the bars on the chart were not showing correctly compared to the labels on the up axis. I've also added a horizontal grid.

If anyone manages to get the transparency working then let me know !

[greentech]

Where are you pulling your data from ... google power meter via TED5000 or maybe TED1000?

[Simonk]

Where are you pulling your data from ... google power meter via TED5000 or maybe TED1000?

I'm using a Owl CM119 power meter with RFXCom to store the data in a access database.

[Jakob.Sand]

I can't get an object in HST to show the acutal picture but only the url to the picture.

What object type do you use? Image? Text? and in what property do you add the [$SCRIPT=&hs.DeviceString("P16")] to make the URL of the variable (in my case P16) show up as picture?

[Simonk]

I can't get an object in HST to show the acutal picture but only the url to the picture.

What object type do you use? Image? Text? and in what property do you add the [$SCRIPT=&hs.DeviceString("P16")] to make the URL of the variable (in my case P16) show up as picture?

I think I used a text box and the string just goes into the text box.

Image attached.

[vossenh]

I would really like to implement this for my various charts that I have through RFXcom Charts. However I am a little stuck, as I don't seem to have a settings.cfg file anywhere in any of the Homeseer2 sub-folders. I do have a settings.ini file in the config folder, should I add the recommended line to this file??

Harry

[Simonk]

I would really like to implement this for my various charts that I have through RFXcom Charts. However I am a little stuck, as I don't seem to have a settings.cfg file anywhere in any of the Homeseer2 sub-folders. I do have a settings.ini file in the config folder, should I add the recommended line to this file??

Harry

I've corrected my post, it should be the settings.ini file.

[vossenh]

Simon, I have updated the settings.ini file as suggested and it all works fine. I am now also able to use your amended code to get temperature data etc. so really happy about this working now. Thanks,

Harry

[Simonk]

Simon, I have updated the settings.ini file as suggested and it all works fine. I am now also able to use your amended code to get temperature data etc. so really happy about this working now. Thanks,

Harry

I'm glad it's working for you.

It feels nice to be able to give something back to the community that I've gained so much from !

Simon

[vossenh]

Simon,

Since you have obviously worked out the code to tap into the RFX database to retrieve various temperature, rain, power etc. values, would you know how to go about storing values in a new table? I am currently measuring how long the central heating boiler is on for on a daily basis, but so far I am storing this in a flat file. What I would really want to be able to do is set up a new table in an access database and then use a script at the end of the day to store some values into a new record. Say I call this the 'Boiler' table with a couple of fields like the date, time and boiler_up_time (in minutes). Do you have any suggestions of what the code needs to look like ? I have tried to modify your current script to use an insert statement rather than the select statement, but I am currently stabbing in the dark to get this working. Any ideas would be most welcome. I have extensively implemented your first suggestion and now have about a dozen or so very cool looking graphs appearing in HSTouch at the press of a button.

Many Thanks,

Harry