Home Automation Domotica Forum Europe, Bwired Forum

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

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.

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