Charts

[DJF3]

Team,

I'm looking for a solution that allows me to create (custom) charts based on .MDB file data. (RFXCOM, own DB, etc). This would be a (more flexible) alternative to RFXCharts.

- Online could be nice
- easy interface for filtering (table, year, month, week, custom filters etc)
- Scheduled saving of chart images

Any suggestions?

[Sooty]

The main reason for me not continuing development of RFXCharts is the fact that not everyone wants the same thing when it comes to the actual charts and the data they contain.

In order to create a "suits all" solution I would need to create a user friendly interface for one of the charting API's which is a task I just don't have the time to undertake.

Another consideration is the frequency that data is recorded. Some people are happy to have the outside temperature for example recorded at 1 hour intervals and others would like it at 5 minute intervals. Wind speed and direction for example really need to be recorded when they change value or the data is meaningless. The recording of data is not a problem and it is easy enough to have any device recorded on change or at a given interval etc. The hard part is presenting the recorded data in charts that a user can configure to suit their requirements without having to learn a charting API.

I actually record nearly all my HS devices to a database, some at various intervals and others only when the value changes. I also have dampers for things like wind direction so that direction changes are not recorded if the wind speed is less than xx etc. I'm actually using the free Microsoft chart controls to create the charts and running the solution on an IIS server because the Homeseer web server won't play with the MS controls. This is not a solution that I can easily make available to other users as the charts are hard coded and bespoke to my system.

The Google charts tools are quite easy to use. You can get an idea of how it works here https://developers.google.com/chart/ima ... playground
Of course you will still need to extract the required data from you underlying DB and present it to Google charts in the correct format which will inevitably require some code writing.

There are also a number of free Java based charting solutions out there that look pretty good. Again you will need to consider how your data is recorded and some code writing will be required.

I already have a comprehensive data recording HS plug-in that I am using now, however I am waiting to see what HS3 brings to the table before I put a significant amount of time into any new charting front end. I'm not ruling out a full blown charting plug-in at some stage in the future but normal work seems to take priority these days

Paul..

[ludom]

In my application I use HighCharts;
http://www.highcharts.com/

Fully Javascript based, so you have to create some pages (with PHP for example) to read-out your MDB files.

[vanisher]

I actually record nearly all my HS devices to a database, some at various intervals and others only when the value changes. I also have dampers for things like wind direction so that direction changes are not recorded if the wind speed is less than xx etc.

How are you getting the device values in the database? Which script are you using for that?

[Irritanterik]

Hi Vanisher,

Just figured it all out, after lots of reading and learning on this forum. Probably my script might help you getting started with database logging.

I use this line in Script\Startup.txt within the Main sub:

Code: Select all

  hs.RegisterStatusChangeCB "TON_StatusChange.vb", "StatusChange"

With this command Homeseer will call the 'StatusChange' function in TON_StatusChange.vb each time a DeviceStatus changes.
TON_StatusChange.vb contains the following:

Code: Select all

'TONs Eerste StatusChange script

Const LogLevel As Integer = 1        '0 = no logging, 1 = normal logging, 2 = debug logging
Const DbUpdate As Boolean = TRUE
Const DbFile As String = "c:\Program Files\HomeSeer HS2\data\TON\TON_DB.mdb"
Const ScriptName As String = "TON_StatusChange"

Sub StatusChange(params)
	Dim hc As String
	Dim dc As String
	Dim status As Integer
	Dim deviceId As Integer

    Dim strConnectionString As String
	Dim sql As String
    Dim objConn As Object
	
	hc = params(0)
	dc = params(1)
	status = params(2)
	deviceId = params(3)
	
	Log("Logging status change for device " & hc & dc & " (" & status & ")")
	Log("hc: " & hc & " dc: " & dc & " status: " & status & " deviceid: " & deviceId, 2)

	' === SQL STATEMENT
	sql = "INSERT INTO tblStateUpdate ( HouseCode, DeviceCode, State, DeviceId ) values ("
	sql = sql & "'" & hc & "','" & dc & "'," & status & "," & DeviceId & ");"
	Log("SQL query: " & sql, 2)

	' === DB WRITE1
	If DbUpdate Then	
		objConn = CreateObject("ADODB.Connection")
		strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DbFile
		objConn.Open(strConnectionString)
		objConn.execute(sql)
		objConn.close()
		objConn = Nothing
		Log("Write To DB executed", 2)
	Else
		Log("Write To DB disabled", 2)
	End if	 
	
End Sub

Private Sub Log(Message As String, Optional MessageLevel As Integer = 1)
	If LogLevel >= MessageLevel Then
		hs.WriteLog(ScriptName, Message)
	End if
End Sub

For now i log all devicechanges in a MS Access database. To use this script the database has to contain a table named tblStatuUpdate with fields:
- ID (autoindexnumber)
- DateTime (datefield with default = now())
- HouseCode (text)
- DeviceCode (text)
- State (integer)
- DeviceId (integer)

My next step is to figure out how to combine ASP.NET, JqWidgets and Highcharts with Homeseer...