Page 3 of 16

Re: HS3 MQTT Plugin

Posted: Sun Nov 15, 2015 5:57 pm
by tominthevan
Geert-Jan

That was quick! I had the wrong plug-in installed. I'll fix that and see how it goes.

Thanks

Tom

Re: HS3 MQTT Plugin

Posted: Sun Nov 15, 2015 7:05 pm
by tominthevan
Hi

Thanks that worked. I should have read the forum more closely. That suggestion moved me forward a lot. Now I need to install a broker somewhere on my network.

Would it be asking too much of the little Pi to have the broker also running on it beside HS3?

Tom

Re: HS3 MQTT Plugin

Posted: Wed Nov 25, 2015 9:16 pm
by geert-jan
Hereby an updated version, but not thoroughly tested yet.

Changelog:
- Added filter on the 'Publish' page, which will make selecting a device more easy and reduces page loading time.

Note that per default ALL check marks are off, meaning that NO devices will be displayed until you select the Room and Type (aka location and location2).

Regards,
Geert-Jan

Re: HS3 MQTT Plugin

Posted: Fri Nov 27, 2015 5:56 pm
by raymonvdm
geert-jan wrote:Hi,

the MQTT plugin writes the MQTT data in a Homeseer device, the device values are available in events and scripts.
I use 'location2' as device type; Electra, Water, Gas.

The script below is called from a Homeseer event every hour, and writes the actual device values for each device where the type is 'Electra', 'Gas' or 'Water' to a MySQL database. The types can easily be extended, with Temperature, Humidity, etc.

Code: Select all

Imports MySql.Data.MySqlClient

Sub main(Optional ByVal pParms As String = "")
  Dim en
  Dim dv

  en = hs.GetDeviceEnumerator

  if IsReference(en) Then
    do while not en.Finished
      dv = en.GetNext
      if not dv is nothing then
          select case dv.location2(hs)
          case "Electra" 
            writeDB("electra", dv, 1000)
          case "Gas"
            writeDB("gas", dv, 1000)
          case "Water"
            writeDB("water", dv, 1)
          end select
      end if
    loop
  end if


End Sub


Sub writeDB(ByVal dbTable as String, ByVal dvRef as Object, devFactor as Integer)
  Dim conn As MySqlConnection
  Dim myCommand As New MySqlCommand
  Dim timestamp As String
  Dim devValue As Integer

  timestamp = Datepart("yyyy",Now()) & "-" & Right("0" & DatePart("m",Now()),2) & "-" & Right("0" & DatePart("d",Now()),2) & " " & Right("0" & DatePart("h",Now()),2) & ":" & "00:00"

  if NOT dbTable = Nothing
    conn = New MySqlConnection()
    conn.ConnectionString = "server=<ip address>; user id=<user id>; password=<password>; database=<database>"

    devValue = Convert.Toint64(dvRef.devValue(hs) * devFactor)

    Try
          conn.Open()

          myCommand.CommandText = "INSERT INTO " _
		& dbTable _
		&  " (deviceName, timestamp, deviceValue, deviceType, deviceId)" _
		& " VALUES ('" _
                  & dvRef.Name(hs) & "','" _
                  & timestamp & "','" _
                  & devValue  & "','" _
                  & dvRef.location2(hs) & "','" _
                  & dvRef.Code(hs) & "')"

      myCommand.Connection = conn

      myCommand.ExecuteNonQuery()

      conn.Close()
   Catch myerror As MySqlException
      hs.writelog ("datalog", "Error Connecting to Database: " & myerror.Message)
   End Try
 end If

End Sub

Sub updateCounter(Optional ByVal pParms As String = "")
    dim electraTotal as double
    dim dev1 as integer
    dim dev2 as integer
    dim dev3 as integer
    Dim arrParm() As String

    ' Extract parameters
    arrParm = pParms .ToString.Split(";")
    dev1= arrParm(0)
    dev2= arrParm(1)
    dev3= arrParm(2)

    electraTotal = hs.devicevalueex(dev1) + hs.devicevalueex(dev2)
    hs.setdevicevaluebyref(dev3, electraTotal, true)
    hs.setdevicestring(dev3, electraTotal & " KWh", true)

end sub
Adapt the fields between'<>' in conn.ConnectionString = "server=<ip address>; user id=<user id>; password=<password>; database=<database>" to your configuration.


Regards,
Geert-Jan
I have installed http://dev.mysql.com/downloads/connector/net/ but the script is not yet working

Code: Select all


Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlException' is not defined.
Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlConnection' is not defined.


Re: HS3 MQTT Plugin

Posted: Sat Nov 28, 2015 5:02 am
by Sparkman
Great, thanks very much. Trying it right now.

Cheers
Al

Re: HS3 MQTT Plugin

Posted: Mon Nov 30, 2015 8:34 am
by geert-jan
@raymonvdm:

I got VB and MySql working by downloading the MySql connector from Microsoft:
- http://dev.mysql.com/downloads/connector/net/1.0.html
- Select platform '.Net and Mono'
- Download the zip file
- Extract the .dll files to your Homeseer folder

Regards,
Geert-Jan

Re: HS3 MQTT Plugin

Posted: Mon Nov 30, 2015 10:59 am
by raymonvdm
Which version did you select? I tried 1.0.10 and 6.8.7 but stil no go

Code: Select all

Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlException' is not defined.
Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlConnection' is not defined.
Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlCommand' is not defined.
Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Type 'MySqlConnection' is not defined.
Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.vb: Namespace or type specified in the Imports 'MySql.Data.MySqlClient' 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.
Error	Compiling script C:\Program Files\HomeSeer HS3\scripts\LogDataToMysql.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.
Note: My HS3 is running on Windows 7

Re: HS3 MQTT Plugin

Posted: Mon Nov 30, 2015 8:06 pm
by geert-jan
I am using MySql connector version 6.7.4, date 19-06-2013, on a Windows XP system (running in a VirtualBox).

Regards,
Geert-Jan

Re: HS3 MQTT Plugin

Posted: Tue Dec 01, 2015 10:25 pm
by raymonvdm
Is it also possible to add the Reference ID to the list of devices visable under MQTT -> Publish ?

I have found a way to export the MQTT data to file to create my cacti graphs but i need to manually lookup the reference id for each device which is quite time consuming

Re: HS3 MQTT Plugin

Posted: Wed Dec 02, 2015 8:20 am
by geert-jan
Adding the reference ID is no issue. I will add it to the overview.

Regards,
Geert-Jan

Re: HS3 MQTT Plugin

Posted: Sat Dec 05, 2015 1:11 am
by raymonvdm
I did some extra testing and it looks like some value updates are not MQTTed out. I have an OpenThermGateway script and the HomeSeer device status seems to be updated but the values are never send out by MQTT

Re: HS3 MQTT Plugin

Posted: Sun Dec 06, 2015 3:32 pm
by geert-jan
Hereby an updated version.

Changelog v0.6.4:
- Added device Id to 'Publish' page
- Publish value on value change (/homeseer/ + <dvRef> + /value)
- Publish string on string change (/homeseer/ + <dvRef> + /string)

This may lead to 2 publish messages per value update, since mostly also the string value is updated.

Regards,
Geert-Jan

Re: HS3 MQTT Plugin

Posted: Mon Dec 07, 2015 10:15 am
by raymonvdm
Is it possible to select the value or sting to publish to prevent dual MQTT messages. Usually the value is sufficient but sometimes you indeed need a string instead of a value :D

Re: HS3 MQTT Plugin

Posted: Tue Dec 08, 2015 8:47 am
by geert-jan
Hi,

I will add a feature to select if you want to publish the value on value change, or publish the string on string change.

Regards,
Geert-Jan

Re: HS3 MQTT Plugin

Posted: Tue Dec 08, 2015 10:18 am
by raymonvdm
geert-jan wrote:Hi,

I will add a feature to select if you want to publish the value on value change, or publish the string on string change.

Regards,
Geert-Jan
Or both perhaps :D