Jon00 Database Charting Utility for Homeseer 3 & Homeseer 4

[cbryan]

First and foremost, amazing plugin! Thank you for your work and expertise.

Still experimentally fine tuning a multitude of graphs.

Is there a method to allow the Y2 axis to be labeled with text (rather than numeric) values such that a numeric value plotted on the y2 axis within the graph (0, 1, 2 or 3) would be associated with a text value label on the y2 label axis (Off, Heat, Cool or Fan).

This is sort of the inverse of the $dtwr function on page 16 of the docs:

Data1=$dtwr:xxxx||Fan,3;Cool,2;Heat,1;Off,2

The goal would be to assign y2 axis labels (Fan, Cool, Heat, Off) that would correspond to numeric data (3,2,1,0).

Thanks in advance,

Craig

[jon00]

First and foremost, amazing plugin! Thank you for your work and expertise.

Still experimentally fine tuning a multitude of graphs.

Is there a method to allow the Y2 axis to be labeled with text (rather than numeric) values such that a numeric value plotted on the y2 axis within the graph (0, 1, 2 or 3) would be associated with a text value label on the y2 label axis (Off, Heat, Cool or Fan).

This is sort of the inverse of the $dtwr function on page 16 of the docs:

Data1=$dtwr:xxxx||Fan,3;Cool,2;Heat,1;Off,2

The goal would be to assign y2 axis labels (Fan, Cool, Heat, Off) that would correspond to numeric data (3,2,1,0).

Thanks in advance,

Craig

Unfortunately the chart control does not support text for the Y-axis.

[BobSpen]

@ BobSpen Good point. I don't understand the PI well enough to comment with authority, however I do understand that my on time is less than my database update mode. Right now it's set for timer 6, 120 seconds. I did this because the PI would overload if I tried to display a few days of records.

It ended up I only need to drill down to a few hours at a time so I may make this a lower interval. I always like to back out and look at the bigger picture, but that may not be necessary here.

My goal is to simply end up with an on time / off interval that meets our hot water needs without running 24/7.

racerfern,
Another approach that would assure you don't miss an "On/Off" would be to create a virtual device that is set with the "On" and reset at the end of the Event that runs the DB update. Then it wouldn't matter what the update interval was.

[racerfern]

Another approach that would assure you don't miss an "On/Off" would be to create a virtual device that is set with the "On" and reset at the end of the Event that runs the DB update. Then it wouldn't matter what the update interval was.

Thanks, I'll look at that as a possibility. I'm getting real close to having the cycles where I want so thanks for a great plug in that made my goals easier to achieve.

At this point I don't have an event that runs the DB update. Should I? I simply call the asp, then click on dynamic charting > chart. Once my cycles are set, I may tweak it a bit for warmer water in the pipes due to a higher ambient temperature in the crawl space.

[cbryan]

Possible bug in Chart Style Setup

When using the menu driven Chart Style Setup page (/Jon00dbsetup2.asp#u2) it appears that when a change is made to Plot X Pen Width the actual change is made to Plot X+1 Pen Width.

I haven't verified at the ini file level but this is what happens in practice when the chart is updated.

Edit: Might just affect a specific Plot. In my case changes to Plot 2 Pen Width appeared on the Plot 3 Pen Width trace but a change to the Plot 4 Pen Width appeared on the Plot 4 Pen Width trace.

[BobSpen]

racerfern,
I may have mislead you with my suggestion of a new virtual device. It would only work if you could trigger an Event when/after jon00 updates the DB(I think in your case every two minutes). I'm not sure that trigger is available? jon00?

You could provide a "reset Event" to reset the virtual device with an event trigger= "if the virtual device has been on for at least 2 minutes".

[jon00]

When using the menu driven Chart Style Setup page (/Jon00dbsetup2.asp#u2) it appears that when a change is made to Plot X Pen Width the actual change is made to Plot X+1 Pen Width.

I haven't verified at the ini file level but this is what happens in practice when the chart is updated.

Edit: Might just affect a specific Plot. In my case changes to Plot 2 Pen Width appeared on the Plot 3 Pen Width trace but a change to the Plot 4 Pen Width appeared on the Plot 4 Pen Width trace.

I've had a quick look at both the main application and the setup page. I see a tool-tip text error for the 'Plot 2 Pen Width', however the actual code appears OK. It's the same routine which is just incremented by 1 for each plot.

[cbryan]

I've had a quick look at both the main application and the setup page. I see a tool-tip text error for the 'Plot 2 Pen Width', however the actual code appears OK. It's the same routine which is just incremented by 1 for each plot.

Must have something to do with the default pallet. When I set the color and pen width for each of the four plot datasets the issue goes away.

Thanks for taking a look at it.

[TallTM]

Daily

I've been attempting to run the script at the bottom of this post to adjust Daily Values appearance on charts. Running the script as a VB file gives me the same errors shown below as found in post #1103.
Was an answer to the script issues ever found?

Thanks.

I'm not an expert on scripting, so please be mild. Tried to run that script, because I face the same issue. Unfortunately it didn't work on my end and I get the following error message in the log.

Compiling script C:\Program Files\HomeSeer HS3\scripts\testscript5.vb: Type 'SQLite.SQLiteCommand' is not defined.
Nov-05 8:28:22 PM Error Compiling script C:\Program Files\HomeSeer HS3\scripts\testscript5.vb: Type 'SQLiteConnection' is not defined.
Nov-05 8:28:22 PM Error Compiling script C:\Program Files\HomeSeer HS3\scripts\testscript5.vb: Namespace or type specified in the Imports 'System.Data.SQLite' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.
Nov-05 8:28:22 PM Error Compiling script C:\Program Files\HomeSeer HS3\scripts\testscript5.vb: Namespace or type specified in the Imports 'System.Core' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.

Any hints on what I should do? Have put the lines in an VB file (should that be an txt file)?

This is the script I am trying to use, per post #1070:

I ran into the same issue a couple years back when I started doing the same thing, tracking daily power used for things like HVAC, pool, and total consumption, and also daily counters for various things (AC cycles, etc) My solution was to run a SQL script just before midnight 23:57 (after the day's data was inserted at 23:55), which updates the timestamp of the data from 23:55 to 00:00. Basically it trims off the time component from the datetimestamp, and it defaults back to 00:00 (first thing in the morning for that day). Been working flawlessly ever since.

The script is very broad and updates ALL data in the table (DBtable3 in my case). You could add criteria to scale down the scope of the update, but in my case that table is only used for "daily totals data"

Basically I execute this inside a script from an event:

Imports System.Data.SQLite

Public Sub Main(ByVal Parms As Object)

Try
Dim SQLconnect As New SQLiteConnection()
Dim SQLcommand As SQLite.SQLiteCommand

SQLconnect.ConnectionString = "Data Source=" & hs.GetAppPath & "\bin\Jon00DBChart\jon00dbcharting.db3;"
SQLconnect.Open()
SQLcommand = SQLconnect.CreateCommand
SQLcommand.CommandText = "UPDATE DBtable3 SET DateAdded = date(DateAdded)"
SQLcommand.ExecuteNonQuery()

Catch Ex As Exception
hs.writelog("Error",Ex.ToString)
End Try

End Sub

[jon00]

You may have to add a reference to your ScriptingReferences entry in settings.ini

ScriptingReferences=System.Data.SQLite;C:\Program Files\HomeSeer HS3\bin\Jon00DBChart\System.Data.SQLite.dll

or if you have other entries already:

ScriptingReferences=<existing entries>,System.Data.SQLite;C:\Program Files\HomeSeer HS3\bin\Jon00DBChart\System.Data.SQLite.dll

It may work without the full file path (which you should change to suit your setup) i.e.

ScriptingReferences= System.Data.SQLite;\bin\Jon00DBChart\System.Data.SQLite.dll

Edit: I forgot to add, this must be done when Homeseer is shutdown.

[Xian]

I have just downloaded the jon00 Graph utility. But I cant populate the graph. If I go to the Dymanic Charts page, I see that I have no datapoints. I have set the Chart to update every 15seconds, just for test, but still no datapoints..

I have copied all file and settings into the right files and directories. What could be wrong ?

[jon00]

It could be a number of things however I suggest you look in the database file to see if metrics are being recorded. Download DB Broiwser for SQLite from http://sqlitebrowser.org/

Install the program and then open up Jon00dbcharting.db3 which can be found in your <Homeseer root>\Bin\Jon00DBChart folder.

You should be able to see the data under the 'Browse Data' tab.

[Xian]

All data are in the table under Browse data tab

[jon00]

On the dynamic chart, have you selected date/times which are confirmed to be in the database?

[Xian]

On the dynamic chart, have you selected date/times which are confirmed to be in the database?

Yes, now I have from 0800 to 0900 this morning... and they are populated in the ddb