BDGraphs: Device value recording and graphing (charts)

[BrunDog]

Hold up... I think I know what it is... try this script and let me know. If it doesn't work, restart HS and try again, then report back.

BDGraphs_script.zip

-BD

[sparkman]

Hi BD,

Thanks for the quick response. The script file you posted did not resolve the issue. Here are the answers to your questions:

1. What version of HS do you have?

2.5.0.60

2. Did you install .NET 4.0? You don't have to for the "Add" part to work.

Yes, installed first and rebooted HS Server afterwards even though the installer did not request that.

3. Try restarting HS. As you can see above, pbim also got a script compile error and restarting HS fixed his issue. I am not sure why his error stated where the problem occured.

I've restarted a few times and no luck.

4. (dumb question but I gotta ask)... did you copy the BDGraphs.mdb file to the specified folder, which is your homeseer folder\Data\BDGraphs\? I do not believe this to be the issue because the script checks for the .mdb file and gives you an error (in english).

Yes, all files were copied from the zip file to their respective folders and I've double-checked them.

I also reconfirmed the username/pwd for the account.

Thanks
Al

[BrunDog]

Can you tell me what your ScriptingReferences line in your \config\settings.ini file reads?

-BD

[sparkman]

Can you tell me what your ScriptingReferences line in your \config\settings.ini file reads?

-BD

It read:

ScriptingReferences=HSPI_SKYPE;C:\Program Files\HomeSeer HSPRO\hspi_skype.dll

However, I disabled the Skype plugin a few months ago, so I removed this reference and restarted HS and still get the same error.

Thanks
Al

[BrunDog]

OK, add the following to this line: System.Data;System.Data.Dll

If you are leaving the skype reference in place, use a comma to separate these. Therefore, it would look like:

ScriptingReferences=HSPI_SKYPE;C:\Program Files\HomeSeer HSPRO\hspi_skype.dll,System.Data;System.Data.Dll

-BD

[sparkman]

Hi BD,

Shut down HS, changed the setting and restarted HS. Ran the event and still get the same error.

Thanks
Al

[BrunDog]

Sorry man! OK, let's keep trying... Here is the script with another update. I believe the problem lies with the reference library getting included. Let me know how this one goes.

BDGraphs_script2.zip

-BD

[sparkman]

No problem, thanks for the quick responses. Same issue with this one. Would it make sense to test an unencrypted script? Running it that way should give an indication which line item causes the problem.

Thanks
Al

[BrunDog]

Yes, we'll have to give that a try. I am away from my computer right now, but will send it tomorrow am. In the meantime, can you post up a screenshot of the action tab of the event that triggers the script, and the resulting log screenshot?

-BD

[BrunDog]

One last try on the .vben. Give the attached a shot.

BDGraphs_script3.zip

-BD

[BrunDog]

Multiple values/series on graph

How-D,

Working on aldo's request... will provide the ability to record and graph up to 10 devices simultaneously. I should have this updated in the package tonight or tomorrow.

Here is a early look at a 2-up. Obviously need to add a legend.



-BD

[alphatech]

It looks very sharp, looking forward to install it when you post the new updates. Thank you very much,
Aldo

[rmies1]

Trying Out BDGraphs

Bruno,
Just as an FYI tried your program/plugin and it did not work odbc connection issue line 25 with original script, Line 21 with your second script and line 21 with your 3rd script.
I appreciate any help you can give in getting this to work.
I am trying to graph four thermostats, and outside temps to control HVAC heating accurately

Thank you in advance
Error is copied here <table border="0" cellpadding="0" cellspacing="2" width="100%"><tbody><tr><td colspan="1" class="LOGDateTime0" align="left" nowrap="nowrap">10/13/2012 9:13:55 AM </td><td colspan="3" class="LOGType0" align="left"> Error </td><td colspan="8" class="LOGEntry0" align="left">Script compile error: Type 'OleDb.OleDbConnection' is not defined.on line 21</td></tr></tbody></table>
Some Info on current system
HS Professional Edition 2.5.0.52
<table border="0" cellpadding="0" cellspacing="0" width="100%"><tbody><tr><td colspan="1" class="tableroweven" align="right">Operating System:</td><td colspan="1" class="tableroweven" align="left">Microsoft Windows Server 2003 R2 Enterprise Edition - Server</td></tr> <tr><td colspan="1" class="tablerowodd" align="right">OS Version:</td><td colspan="1" class="tablerowodd" align="left">5.2.3790 Service Pack: 2.0
</td></tr></tbody></table><table border="0" cellpadding="0" cellspacing="0" width="100%"><tbody><tr><td colspan="1" class="tablerowodd" align="right">Unique, Used X-10 Devices:</td><td colspan="1" class="tablerowodd" align="left">32</td></tr> <tr><td colspan="1" class="tableroweven" align="right">Number of Events:</td><td colspan="1" class="tableroweven" align="left">23</td></tr> <tr><td colspan="1" class="tablerowodd" align="right">Event Last Ran:</td><td colspan="1" class="tablerowodd" align="left">HVAC Control : Outside Environment Temp Change, Ran at 10/13/2012 9:05:07 AM</td></tr> <tr><td colspan="1" class="tableroweven" align="right">Number of unique event scripts used:</td><td colspan="1" class="tableroweven" align="left">4</td></tr> <tr><td colspan="1" class="tablerowodd" align="right">Connected Speaker Clients:</td><td colspan="1" class="tablerowodd" align="left">WAYNEWEBSVR8 (127.0.0.1)EFAULT</td></tr> <tr><td colspan="1" class="tableroweven" align="right">Plug-Ins Enabled:</td><td colspan="1" class="tableroweven" align="left">X10 CM11A/CM12U, , RCS X10 Thermostat, UltraWeatherBug,
HSTouch Server, HSTouch Server Unlimited, HSTouch iPhone, HSTouch Android,
Napco Gemini, UltraSMTP, HAI RC Thermostat</td></tr> <tr><td colspan="1" class="tablerowodd" align="right">Processor Type and Speed:</td><td colspan="1" class="tablerowodd" align="left">x86 Family 6 Model 42 Stepping 7 at 2.14 GHz</td></tr> <tr><td colspan="1" class="tableroweven" align="right">Modules/Threads:</td><td colspan="1" class="tableroweven" align="left">162 Modules, 56 Threads</td></tr> <tr><td colspan="1" class="tablerowodd" align="right">System Processes/Load:</td><td colspan="1" class="tablerowodd" align="left">48 Processes, 1% Load</td></tr> <tr><td colspan="1" class="tableroweven" align="right">Free / Total Physical Memory:</td><td colspan="1" class="tableroweven" align="left">3.46 GBytes / 4.00 GBytes (86% free)</td></tr> <tr><td colspan="1" class="tablerowodd" align="right">Free / Total Virtual Memory:</td><td colspan="1" class="tablerowodd" align="left">7.34 GBytes / 7.82 GBytes (94% free)</td></tr> <tr><td colspan="1" class="tableroweven" align="right">HomeSeer Memory Used:</td><td colspan="1" class="tableroweven" align="left">79 Mbytes</td></tr> <tr><td colspan="1" class="tablerowodd" align="right">COM Plug-In Memory Used:</td><td colspan="1" class="tablerowodd" align="left">2 COM EXE Plug-Ins using 13 Mbytes
7 Mbytes for HS_Compatibility.</td></tr></tbody></table>

[BrunDog]

@rmies1: Please add the entries, "System.Data;System.Data.Dll" to the ScriptingReferences key in your HomeSeer settings.ini file per post 20 above. Separate this entry with a comma.

Then, restart HS and test it again. Please report back. Thanks!

-BD

[rmies1]

Same Error note I did not have a scripting reference key in my settings.ini
This I created one at the bottom which looked like this
ScriptingReferences=c:\Program Files\HomeSeer HSPRO\System.Data;System.Data.Dll

Restarted HSPRO then enabled the event I created and did a Run
The result in the log was
10/13/2012 8:03:26 PM Error Script compile error: Type 'OleDb.OleDbConnection' is not defined.on line 21
10/13/2012 8:03:26 PM SCR Option Strict OffImports System.DataImports System.Data.OleDbImports System.Text.RegularExpressionsImports System.IOimports Schedulerimports SystemPublic Module scriptcode1#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 Region Public Sub Add(ByVal Parms As Object) Dim DatabasePath As String Dim tableName As String Dim con As New OleDb.OleDbConnection '("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & DatabasePath) Dim cmd As New OleDb.OleDbCommand Dim ds As New DataSet Dim sql As String Dim RowsChanged As Integer Dim DeviceCode As String Dim Multiplier As Single Dim PassedParms() As String Dim Device Dim DeviceValue As Single Try 'Set the database file location DatabasePath = hs.GetAppPath & "\DATA\BDGraphs\BDGraphs.mdb" 'DatabasePath = "\\nas1\public\BDGraphs.mdb" 'Check that the database file is in place If Not File.Exists(DatabasePath) Then hs.WriteLog("BDGraphs", "WARNING: The database file (" & DatabasePath & ") could not be found.") Exit Sub End If 'Create the connection string con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & DatabasePath 'Parse out the incoming parameters, create the table name from the device name of max 64 chars PassedParms = Split(Parms.ToString, ";") If Not UBound(PassedParms) = 1 Then hs.WriteLog("BDGraphs", "WARNING: The Add function requires 2 parameters: device code;Multiplier.") Exit Sub End If DeviceCode = PassedParms(0) Multiplier = CSng(PassedParms(1)) 'Check that the device code is legit If hs.GetDeviceRef(DeviceCode) = -1 Then hs.WriteLog("BDGraphs", "WARNING: The device code passed, '" & DeviceCode & "' is invalid.") Exit Sub End If Device = hs.GetDeviceByRef(hs.GetDeviceRef(DeviceCode)) 'tableName = Left(Replace(Device.name, " ", ""), 64) tableName = Left(Regex.Replace(Device.name, "[^A-Za-z0-9]", ""), 64) 'con.Open() 'sql = "SELECT * FROM " & tableName 'da = New OleDb.OleDbDataAdapter(sql, con) 'da.Fill(ds, "Data") 'con.Close() 'RowsCount = ds.Tables("Data").Rows.Count 'hs.WriteLog("BDGraphs", "The # of rows is: " & RowsCount) 'Determine if the table exists and if it does not, create it 'Open the database, get the schema, close it con.Open() Dim dbSchema As DataTable = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, tableName, "TABLE"}) con.Close() If dbSchema.Rows.Count = 0 Then sql = "CREATE TABLE [" + tableName + "] ([Date] DATE, [Data] SINGLE)" cmd = New OleDb.OleDbCommand(sql, con) con.Open() cmd.ExecuteNonQuery() hs.WriteLog("BDGraphs", "The new table '" & tableName & "' was created.") con.Close() End If 'Add the device value into the specified table DeviceValue = hs.DeviceValue(DeviceCode) * Multiplier sql = "INSERT INTO " & tableName & " VALUES ('" & Now() & "','" & DeviceValue & "')" con.Open() cmd = New OleDb.OleDbCommand(sql, con) RowsChanged = cmd.ExecuteNonQuery() If RowsChanged = 1 Then hs.WriteLog("BDGraphs", "The value '" & DeviceValue & "' in table '" & tableName & "' was added.") Else hs.WriteLog("BDGraphs", "WARNING: The value '" & DeviceValue & "' in table '" & tableName & "' was not added.") End If con.Close() Catch ex As Exception hs.WriteLog("BDGraphs", "ERROR: The message was: " & ex.Message & ". The source was: " & ex.Source) End Try End Sub Public Sub Delete(ByVal Parms As Object) Dim DatabasePath As String Dim tableName As String Dim con As New OleDb.OleDbConnection '("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & DatabasePath) Dim cmd As New OleDb.OleDbCommand Dim sql As String Dim RowsChanged As Integer Dim DeviceCode As String Dim PassedParms() As String Dim HoursStart As Integer Dim Today As Date Dim DateStart As Date Dim Device Try 'Set the database file location DatabasePath = hs.GetAppPath & "\DATA\BDGraphs\BDGraphs.mdb" 'DatabasePath = "\\nas1\public\BDGraphs.mdb" 'Check that the database file is in place If Not File.Exists(DatabasePath) Then hs.WriteLog("BDGraphs", "WARNING: The database file (" & DatabasePath & ") could not be found.") Exit Sub End If 'Create the connection string con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & DatabasePath Today = Now 'Parse out the incoming parameters: devicecode and date cutoff PassedParms = Split(Parms.ToString, ";") If Not UBound(PassedParms) = 1 Then hs.WriteLog("BDGraphs", "WARNING: The Delete function requires 2 parameters: device code;# hours to delete values before.") Exit Sub End If DeviceCode = PassedParms(0) HoursStart = CInt(PassedParms(1)) 'DateThreshold = PassedParms(1) 'Check that the device code is legit If hs.GetDeviceRef(DeviceCode) = -1 Then hs.WriteLog("BDGraphs", "WARNING: The device code passed, '" & DeviceCode & "' is invalid.") Exit Sub End If Device = hs.GetDeviceByRef(hs.GetDeviceRef(DeviceCode)) 'tableName = Left(Replace(Device.name, " ", ""), 64) tableName = Left(Regex.Replace(Device.name, "[^A-Za-z0-9]", ""), 64) 'Calculate the start and end dates DateStart = Today.AddHours(HoursStart * -1) 'Execute the deletion sql = "DELETE FROM " & tableName & " WHERE (DATE 1 Then hs.WriteLog("BDGraphs", RowsChanged & " value(s) before " & DateStart & " in table '" & tableName & "' were deleted.") Else hs.WriteLog("BDGraphs", "No values in table '" & tableName & "' were deleted.") End If con.Close() Catch ex As Exception hs.WriteLog("BDGraphs", "ERROR: The message was: " & ex.Message & ". The source was: " & ex.Source) End Try End Sub Public Sub Make(ByVal Parms As Object) Dim PassedParms() As String Dim AppString As String Try 'Parse out the incoming parameters: devicecode and date cutoff PassedParms = Split(Parms.ToString, ";") 'Check that all parameters were passed If Not UBound(PassedParms) = 3 Then hs.WriteLog("BDGraphs", "WARNING: The Make function requires 4 parameters: device code;# hours ago to start;# hours ago to end;graph style # to use.") Exit Sub End If AppString = hs.GetAppPath & "\BDGraphs.exe" 'Check that the application is in the HS folder. If Not File.Exists(AppString) Then hs.WriteLog("BDGraphs", "WARNING: The BDGraphs.exe file is not found in the HomeSeer directory.") Exit Sub End If 'Check that the device code is legit If hs.GetDeviceRef(PassedParms(0)) = -1 Then hs.WriteLog("BDGraphs", "WARNING: The device code passed, '" & PassedParms(0) & "' is invalid.") Exit Sub End If 'May want to add checks for hours ago and graph style - for now just passing entire string 'Launch that **** hs.Launch(AppString, Parms) Catch ex As Exception hs.WriteLog("BDGraphs", "The error message was: " & ex.Message & ". The error source was: " & ex.Source) End Try End Sub Public Sub Place(ByVal Parms As Object) Dim DeviceCode As String Dim PassedParms() As String Dim GraphCode As String Dim GraphCodeNumber As String Dim htmlstring As String Dim tableName As String Dim dv Dim Device Try 'Parse out the incoming parameters: devicecode and date cutoff PassedParms = Split(Parms.ToString, ";") 'Check that all parameters were passed If Not UBound(PassedParms) = 1 Then hs.WriteLog("BDGraphs", "WARNING: The Place function requires 2 parameters: device code of source data;device code of graph location.") Exit Sub End If 'Assign the parameters DeviceCode = PassedParms(0) GraphCode = PassedParms(1) 'Check that the device code is legit If hs.GetDeviceRef(DeviceCode) = -1 Then hs.WriteLog("BDGraphs", "WARNING: The device code passed, '" & DeviceCode & "' is invalid.") Exit Sub End If 'Check that the device code is possible (weak attempt, as it only checks the right one or two letters to be a number) GraphCodeNumber = Right(GraphCode, Len(GraphCode) - 1) Device = hs.GetDeviceByRef(hs.GetDeviceRef(DeviceCode)) 'tableName = Left(Replace(Device.name, " ", ""), 64) tableName = Left(Regex.Replace(Device.name, "[^A-Za-z0-9]", ""), 64) If Not Regex.IsMatch(GraphCodeNumber, "^[0-9 ]+$") Then hs.WriteLog("BDGraphs", "WARNING: The graph location device code passed, '" & GraphCode & "' is invalid.") Exit Sub End If 'Check to see if the graph code exists, and if not, make it If hs.GetDeviceRef(GraphCode) = -1 Then dv = hs.NewDeviceEx(Device.name & " Graph") With dv .misc = &H10 .dev_type_string = "Status Only" .hc = Left(GraphCode, 1) .dc = GraphCodeNumber .location = Device.location hs.WriteLog("BDGraphs", "The new device '" & Device.name & " Graph' with device code '" & GraphCode & "' was created.") End With End If 'Create the device string and load it into the graph device code htmlstring = "" hs.SetDeviceString(GraphCode, htmlstring, True) hs.WriteLog("BDGraphs", "The graph for '" & Device.name & "' was loaded into device code '" & GraphCode & "'.") Catch ex As Exception hs.WriteLog("BDGraphs", "The error message was: " & ex.Message & ". The error source was: " & ex.Source) End Try End SubEnd Module