HS3 MQTT Plugin

Alles m.b.t. de Homeseer MQTT Broker Plugin van Willem Eradus (Dutch Forum)

Moderator: Willem4ever

Re: HS3 MQTT Plugin

Postby tominthevan » Sun Nov 15, 2015 4:57 pm

Geert-Jan

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

Thanks

Tom
tominthevan
Starting Member
Starting Member
 
Posts: 9
Joined: January 2012

Re: HS3 MQTT Plugin

Postby tominthevan » Sun Nov 15, 2015 6:05 pm

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
tominthevan
Starting Member
Starting Member
 
Posts: 9
Joined: January 2012

Re: HS3 MQTT Plugin

Postby geert-jan » Wed Nov 25, 2015 8:16 pm

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
Attachments
HSPI_MQTT_v0.6.2.zip
(37.42 KiB) Downloaded 186 times
geert-jan
Member
Member
 
Posts: 126
Joined: November 2010

Re: HS3 MQTT Plugin

Postby raymonvdm » Fri Nov 27, 2015 4:56 pm

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.

Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1153
Joined: December 2011

Re: HS3 MQTT Plugin

Postby Sparkman » Sat Nov 28, 2015 4:02 am

Great, thanks very much. Trying it right now.

Cheers
Al
Last edited by Bwired on Sat Nov 28, 2015 10:37 am, edited 1 time in total.
Reason: please stop quoting while not needed...thanks
Sparkman
Starting Member
Starting Member
 
Posts: 29
Joined: September 2015

Re: HS3 MQTT Plugin

Postby geert-jan » Mon Nov 30, 2015 7:34 am

@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
geert-jan
Member
Member
 
Posts: 126
Joined: November 2010

Re: HS3 MQTT Plugin

Postby raymonvdm » Mon Nov 30, 2015 9:59 am

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
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1153
Joined: December 2011

Re: HS3 MQTT Plugin

Postby geert-jan » Mon Nov 30, 2015 7:06 pm

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
geert-jan
Member
Member
 
Posts: 126
Joined: November 2010

Re: HS3 MQTT Plugin

Postby raymonvdm » Tue Dec 01, 2015 9:25 pm

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
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1153
Joined: December 2011

Re: HS3 MQTT Plugin

Postby geert-jan » Wed Dec 02, 2015 7:20 am

Adding the reference ID is no issue. I will add it to the overview.

Regards,
Geert-Jan
geert-jan
Member
Member
 
Posts: 126
Joined: November 2010

Re: HS3 MQTT Plugin

Postby raymonvdm » Sat Dec 05, 2015 12:11 am

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
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1153
Joined: December 2011

Re: HS3 MQTT Plugin

Postby geert-jan » Sun Dec 06, 2015 2:32 pm

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
Attachments
HSPI_MQTT_v0.6.4.zip
HS3 MQTT v0.6.4
(37.5 KiB) Downloaded 137 times
geert-jan
Member
Member
 
Posts: 126
Joined: November 2010

Re: HS3 MQTT Plugin

Postby raymonvdm » Mon Dec 07, 2015 9:15 am

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
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1153
Joined: December 2011

Re: HS3 MQTT Plugin

Postby geert-jan » Tue Dec 08, 2015 7:47 am

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
geert-jan
Member
Member
 
Posts: 126
Joined: November 2010

Re: HS3 MQTT Plugin

Postby raymonvdm » Tue Dec 08, 2015 9:18 am

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
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
raymonvdm
Senior Member
Senior Member
 
Posts: 1153
Joined: December 2011

PreviousNext

Return to Homeseer MQTT Plugin Forum

Who is online

Users browsing this forum: No registered users and 1 guest