HS3 MQTT Plugin

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

Moderator: Willem4ever

tominthevan
Starting Member
Starting Member
Posts: 9
Joined: Wed Jan 25, 2012 8:05 pm

Re: HS3 MQTT Plugin

Post 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
tominthevan
Starting Member
Starting Member
Posts: 9
Joined: Wed Jan 25, 2012 8:05 pm

Re: HS3 MQTT Plugin

Post 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
geert-jan
Member
Member
Posts: 126
Joined: Sat Nov 27, 2010 7:23 pm

Re: HS3 MQTT Plugin

Post 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
Attachments
HSPI_MQTT_v0.6.2.zip
(37.42 KiB) Downloaded 488 times
raymonvdm
Senior Member
Senior Member
Posts: 1153
Joined: Sun Dec 18, 2011 1:23 am

Re: HS3 MQTT Plugin

Post 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.

Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
Sparkman
Starting Member
Starting Member
Posts: 29
Joined: Wed Sep 09, 2015 5:57 am

Re: HS3 MQTT Plugin

Post by Sparkman »

Great, thanks very much. Trying it right now.

Cheers
Al
Last edited by Bwired on Sat Nov 28, 2015 11:37 am, edited 1 time in total.
Reason: please stop quoting while not needed...thanks
geert-jan
Member
Member
Posts: 126
Joined: Sat Nov 27, 2010 7:23 pm

Re: HS3 MQTT Plugin

Post 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
raymonvdm
Senior Member
Senior Member
Posts: 1153
Joined: Sun Dec 18, 2011 1:23 am

Re: HS3 MQTT Plugin

Post 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
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
geert-jan
Member
Member
Posts: 126
Joined: Sat Nov 27, 2010 7:23 pm

Re: HS3 MQTT Plugin

Post 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
raymonvdm
Senior Member
Senior Member
Posts: 1153
Joined: Sun Dec 18, 2011 1:23 am

Re: HS3 MQTT Plugin

Post 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
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
geert-jan
Member
Member
Posts: 126
Joined: Sat Nov 27, 2010 7:23 pm

Re: HS3 MQTT Plugin

Post by geert-jan »

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

Regards,
Geert-Jan
raymonvdm
Senior Member
Senior Member
Posts: 1153
Joined: Sun Dec 18, 2011 1:23 am

Re: HS3 MQTT Plugin

Post 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
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
geert-jan
Member
Member
Posts: 126
Joined: Sat Nov 27, 2010 7:23 pm

Re: HS3 MQTT Plugin

Post 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
Attachments
HSPI_MQTT_v0.6.4.zip
HS3 MQTT v0.6.4
(37.5 KiB) Downloaded 432 times
raymonvdm
Senior Member
Senior Member
Posts: 1153
Joined: Sun Dec 18, 2011 1:23 am

Re: HS3 MQTT Plugin

Post 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
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
geert-jan
Member
Member
Posts: 126
Joined: Sat Nov 27, 2010 7:23 pm

Re: HS3 MQTT Plugin

Post 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
raymonvdm
Senior Member
Senior Member
Posts: 1153
Joined: Sun Dec 18, 2011 1:23 am

Re: HS3 MQTT Plugin

Post 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
Running HS3PRO on PC with Z-Wave / OpenTherm / Plugwise / RFXcom / MQTT / XAP400 / Logitech Media Server and Squeezelite on PI`s
Post Reply

Return to “Homeseer MQTT Plugin Forum”