' Gambas module file

' Description:

' Devices.module

' Contains code which is device related.

' Development Status:

' Working, maybe needs more error checking.

' DomotiGa - an open source home automation program.

' Copyright(C) 2008-2010 Ron Klinkien

' Read file called COPYING for license details.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' control device via linked interface

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB SetDevice(sDeviceName AS String, sValue AS String)

  DIM iInterface, iDeviceId AS Integer

  DIM sText, sAddress AS String

  iInterface = FindInterfaceForDevice(sDeviceName)

  sAddress = FindAddressForDevice(sDeviceName)

  SELECT CASE iInterface

    CASE 0

      Main.WriteDebugLog(("[Devices] Something is wrong, cannot query interface for this device!"))

    CASE 1 ' RFXCom Receiver

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] RFXCom Receiver can't control anything!"))

    CASE 2 ' Xanura CTX35

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] Xanura CTX35: " & sAddress & " " & sValue)

      IF Main.hCTX35 THEN

        IF Upper(sValue) <> "STOP" THEN

          Main.hCTX35.sCommandToSend = sAddress & " " & sValue

          Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

          sText = ("Switched ") & sDeviceName & " " & sValue

        END IF

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because CTX35 is disabled!"))

      END IF

    CASE 3, 4, 5, 6, 8

      Main.WriteDebugLog(("[Devices] Error, read-only interface used for switching!"))

    CASE 7 ' Heyu X10

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] Heyu X10")

      IF Main.bHeyuEnabled THEN

        sText = ("Switched ") & sDeviceName & " " & sValue

        Heyu.SendCommand(sAddress, sValue)

        Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because Heyu is disabled!"))

      END IF

    CASE 9 ' Weeder WTSSR

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] Weeder WTSSR: " & sAddress & " " & sValue)

      IF Main.hWeeder THEN

        iDeviceId = Find(sAddress, iInterface)

        IF iDeviceId THEN

          IF InStr(FindTypeNameForDevice(iDeviceId), "ITHO ECO Fan") THEN

            IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] ITHO ECO Fan")

            Main.hWeeder.ControlITHO(sAddress, sValue)

            Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

          END IF

        ELSE

          Main.WriteDebugLog(("[Devices] ITHO ECO Fan not enabled!"))

        END IF

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because Weeder is disabled!"))

      END IF

    CASE 10 ' Plugwise

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] Plugwise Stick: " & sAddress & " " & sValue)

      IF Main.hPlugwise THEN

        Main.hPlugwise.SendCommand(sAddress, sValue)

        Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

        sText = ("Switched ") & sDeviceName & " " & sValue

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because Plugwise is disabled!"))

      END IF

    CASE 12 ' RFXCom Transmitter

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] RFXCom Transmitter: " & sAddress & " " & sValue)

      IF Main.hRFXComTX THEN

        IF sValue <> "STOP" THEN

          Main.hRFXComTX.SendCommand(sAddress, sValue)

          Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

          sText = ("Switched ") & sDeviceName & " " & sValue

        END IF

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because RFXCom Transmitter is disabled!"))

      END IF

    CASE 13 ' KNX/EIB Interface

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] KNX/EIB Interface: " & sAddress & " " & sValue)

      IF Main.hEIB THEN

        Main.hEIB.SendCommand(sAddress, sValue)

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because KNX/EIB is disabled!"))

      END IF

    CASE 15 ' Z-Wave Controller

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] Z-Wave Interface: " & sAddress & " " & sValue)

      IF Main.hZWave THEN

        IF sValue <> "STOP" THEN

          Main.hZWave.SendCommand(sAddress, sValue)

          Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

          sText = ("Switched ") & sDeviceName & " " & sValue

        END IF

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because Z-Wave interface is disabled!"))

      END IF

    CASE 16 ' PLCBUS Interface

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] PLCBUS Interface: " & sAddress & " " & sValue)

      IF Main.hPLCBUS THEN

        IF sValue <> "STOP" THEN

          Main.hPLCBUS.SendCommand(sAddress, sValue)

          Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

          sText = ("Switched ") & sDeviceName & " " & sValue

        END IF

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because PLCBUS interface is disabled!"))

      END IF

    CASE 20 ' EZcontrol Interface

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] EZcontrol Interface: " & sAddress & " " & sValue)

      IF Main.hEZcontrol THEN

        IF sValue <> "STOP" THEN

          iDeviceId = Find(sAddress, iInterface)

          IF iDeviceId THEN

            Main.hEZcontrol.SendCommand(sAddress, sValue, iDeviceId)

            Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

            sText = ("Switched ") & sDeviceName & " " & sValue

          END IF

        END IF

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because EZcontrol interface is disabled!"))

      END IF

    CASE 22 ' PwrCtrl Interface

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] PwrCtrl Interface: " & sAddress & " " & sValue)

      IF Main.hPwrCtrl THEN

        IF sValue <> "STOP" THEN

            Main.hPwrCtrl.SendCommand(sAddress, sValue)

            Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

            sText = ("Switched ") & sDeviceName & " " & sValue

        END IF

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because PwrCtrl interface is disabled!"))

      END IF

    CASE 23 ' Denon Interface

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] Denon Interface: " & sAddress & " " & sValue)

      IF Main.hDenon THEN

        IF sValue <> "STOP" THEN

            Main.hDenon.SendCommand(sAddress, sValue)

            Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

            sText = ("Switched ") & sDeviceName & " " & sValue

        END IF

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because Denon interface is disabled!"))

      END IF

    CASE 24 ' Onkyo Interface

      IF Main.bDevicesDebug THEN Main.WriteDebugLog("[Devices] Onkyo Interface: " & sAddress & " " & sValue)

      IF Main.hOnkyo THEN

        IF sValue <> "STOP" THEN

            Main.hOnkyo.SendCommand(sAddress, sValue)

            Devices.ValueUpdate(Find(sAddress, iInterface), sValue, "", "", "")

            sText = ("Switched ") & sDeviceName & " " & sValue

        END IF

      ELSE

        Main.WriteDebugLog(("[Devices] Can't control device '") & sDeviceName & ("' because Onkyo interface is disabled!"))

      END IF

    CASE -1

      Main.WriteDebugLog(("[Devices] Interface for device '") & sDeviceName & ("' does not support Write!"))

  CASE ELSE

    Main.WriteDebugLog(("[Devices] Unknown Interface type ") & Str(iInterface) & (" for device '") & sDeviceName & "'!")

  END SELECT

  IF sText AND IF iInterface THEN

    iDeviceId = Find(sAddress, iInterface)

    IF iDeviceId AND IF FindLogSpeakForDevice(iDeviceId) THEN VoiceText.Speak(sText)

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find interface with name sInterfaceName, return it's id.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindInterface(sInterfaceName AS String) AS Integer

  DIM rInterface AS Result

  TRY rInterface = Main.hDB.Exec("SELECT * FROM interfaces WHERE name = &1", sInterfaceName)

  IF rInterface.Available THEN

    IF rInterface.Count = 1 THEN

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Interface with name '") & sInterfaceName & ("' has id '") & rInterface!id & "'")

      RETURN rInterface!id

    ELSE

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Interface with name '") & sInterfaceName & ("' not found!"))

      RETURN 0

    END IF

  ELSE

    IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Cannot get Interface records!"))

    RETURN 0

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find devicetype with type, return it's id.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindDeviceType(sDeviceType AS String) AS Integer

  DIM rDeviceType AS Result

  TRY rDeviceType = Main.hDB.Exec("SELECT * FROM devicetypes WHERE type LIKE &1", sDeviceType)

  IF rDeviceType.Available THEN

    IF rDeviceType.Count = 1 THEN

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] DeviceType with type '") & sDeviceType & ("' has id '") & rDeviceType!id & "'")

      RETURN rDeviceType!id

    ELSE

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] DeviceType with type '") & sDeviceType & ("' not found!"))

      RETURN 0

    END IF

  ELSE

    IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Cannot get DeviceType records!"))

    RETURN 0

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return module/devicetype for device with id

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindModuleForDevice(iDeviceId AS Integer) AS Integer

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT module FROM devices WHERE id = &1", iDeviceId)

  IF rDevice.Available THEN

    RETURN rDevice!module

  ELSE

    RETURN 0

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return type for device with id

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindTypeForDevice(iDeviceId AS Integer) AS String

  DIM rDevice AS Result

  DIM iDeviceType AS Integer

  iDeviceType = FindModuleForDevice(iDeviceId)

  TRY rDevice = Main.hDB.Exec("SELECT type FROM devicetypes WHERE id = &1", iDeviceType)

  RETURN rDevice!type

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return typename for device with id

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindTypeNameForDevice(iDeviceId AS Integer) AS String

  DIM rDevice AS Result

  DIM iDeviceType AS Integer

  iDeviceType = FindModuleForDevice(iDeviceId)

  TRY rDevice = Main.hDB.Exec("SELECT name FROM devicetypes WHERE id = &1", iDeviceType)

  RETURN rDevice!name

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return description for devicetype with iId

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindDescrForDeviceType(iId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT description FROM devicetypes WHERE id = &1", iId)

  IF rDevice.Available THEN

    RETURN rDevice!description

  ELSE

    RETURN 0

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return name for device with id

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindNameForDevice(iDeviceId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT name FROM devices WHERE id = &1", iDeviceId)

  IF NOT ERROR AND IF rDevice.Available THEN

    RETURN rDevice!name

  ELSE

    Main.WriteDebugLog(("[Devices] Cannot find name for device with id ") & iDeviceId)

    RETURN ""

  ENDIF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return device id for virtual device with address

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindVirtualDeviceID(sAddress AS String) AS Integer

  DIM iId AS Integer

  iId = Find(sAddress, FindInterface("Virtual Interface"), "Virtual Device")

  RETURN iId

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return name for interface with id

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindNameForInterface(iInterfaceId AS Integer) AS String

  DIM rInterface AS Result

  TRY rInterface = Main.hDB.Exec("SELECT name FROM interfaces WHERE id = &1", iInterfaceId)

  RETURN rInterface!name

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return log boolean for device with id

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindLogForDevice(iDeviceId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT log FROM devices WHERE id = &1", iDeviceId)

  IF NOT ERROR AND IF rDevice.Available THEN RETURN rDevice!log

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return print boolean for device with id

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindLogDisplayForDevice(iDeviceId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT logdisplay FROM devices WHERE id = &1", iDeviceId)

  IF NOT ERROR AND IF rDevice.Available THEN RETURN rDevice!logdisplay

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return speak boolean for device with id

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindLogSpeakForDevice(iDeviceId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT logspeak FROM devices WHERE id = &1", iDeviceId)

  IF NOT ERROR AND IF rDevice.Available THEN RETURN rDevice!logspeak

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' return hide boolean for device with id

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindHideForDevice(iDeviceId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT hide FROM devices WHERE id = &1", iDeviceId)

  IF NOT ERROR AND IF rDevice.Available THEN RETURN rDevice!hide

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find interface for device with sdeviceName.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindInterfaceForDevice(sDeviceName AS String) AS Integer

  DIM rDevice, rResult AS Result

  TRY rDevice = Main.hDB.Exec("SELECT address, name, interface FROM devices WHERE name = &1", sDeviceName)

  ' found only one unique entry?

  IF rDevice.Count > 1 THEN

    Main.WriteDebugLog(("[Devices] The following devices have same name:"))

    FOR EACH rDevice

      Main.WriteDebugLog(("[Devices] Device with address '") & rDevice!address & "' is '" & rDevice!name & "'")

    NEXT

    RETURN 0

  ' ok this one seems useful

  ELSE IF rDevice.Count = 1 THEN

    TRY rResult = Main.hDB.Exec("SELECT mode FROM interfaces WHERE id = &1", rDevice!interface)

    IF InStr(rResult!mode, "Write") THEN

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Interface for device with name '") & sDeviceName & "' is '" & rDevice!interface & "'")

      RETURN rDevice!interface

    ELSE

      ' read-only

      RETURN -1

    END IF

  ' no interface found, something is wrong

  ELSE

    Main.WriteDebugLog(("[Devices] Device with name '") & sDeviceName & ("' not found!"))

    RETURN 0

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find address for device with sDeviceName.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB FindAddressForDevice(sDeviceName AS String) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT address FROM devices WHERE name = &1", sDeviceName)

  IF rDevice.Count > 1 THEN

    Main.WriteDebugLog(("[Devices] The following devices have same name:"))

    FOR EACH rDevice

      Main.WriteDebugLog(("[Devices] Device with address '") & rDevice!address & ("' is '") & rDevice!name & "'")

    NEXT

    RETURN 0

  ELSE IF rDevice.Count = 1 THEN

    IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Address for device with name '") & sDeviceName & ("' is '") & rDevice!address & "'")

    RETURN rDevice!address

  ELSE

    Main.WriteDebugLog(("[Devices] Device with name '") & sDeviceName & ("' not found!"))

    RETURN 0

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find device with sName, return it's current value

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB GetValueForDevice(sDeviceName AS String, OPTIONAL iValue AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT value, value2, value3, value4 address FROM devices WHERE name = &1", sDeviceName)

  IF rDevice.Count > 1 THEN

    Main.WriteDebugLog(("[Devices] The following devices have same name:"))

    FOR EACH rDevice

      Main.WriteDebugLog(("[Devices] Device with address '") & rDevice!address & ("' is '") & rDevice!name & "'")

    NEXT

    RETURN 0

  ELSE IF rDevice.Count = 1 THEN

    IF ivalue THEN

      SELECT CASE iValue

        CASE 1

          RETURN rDevice!value

        CASE 2

          RETURN rDevice!value2

        CASE 3

          RETURN rDevice!value3

        CASE 4

          RETURN rDevice!value4

        CASE ELSE

          RETURN rDevice!value

      END SELECT

    ELSE

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Value of device with name '") & sDeviceName & ("' is ") & rDevice!value)

      RETURN rDevice!value

    ENDIF

  ELSE

    Main.WriteDebugLog(("[Devices] Device with name '") & sDeviceName & ("' not found!"))

    RETURN 0

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find device with iId, return it's lastchanged value

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB GetLastChangedForDevice(iId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT lastchanged FROM devices WHERE id = &1", iId)

  IF rDevice.Count THEN

    RETURN rDevice!lastchanged

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find device with iId, return it's current value

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB GetCurrentValueForDevice(iId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT value FROM devices WHERE id = &1", iId)

  IF rDevice.Count THEN

    RETURN rDevice!value

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find device with iId, return it's current value2

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB GetCurrentValue2ForDevice(iId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT value2 FROM devices WHERE id = &1", iId)

  IF rDevice.Count THEN

    RETURN rDevice!value2

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find device with iId, return it's current value3

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB GetCurrentValue3ForDevice(iId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT value3 FROM devices WHERE id = &1", iId)

  IF rDevice.Count THEN

    RETURN rDevice!value3

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find device with iId, return it's current value4

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB GetCurrentValue4ForDevice(iId AS Integer) AS String

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT value4 FROM devices WHERE id = &1", iId)

  IF rDevice.Count THEN

    RETURN rDevice!value4

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find device with sAddress and iInterface, return id if found.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB Find(sAddress AS String, iInterface AS Integer, OPTIONAL sDevice AS String) AS Integer

  DIM rDevice, rResult AS Result

  IF iInterface THEN

    TRY rDevice = Main.hDB.Exec("SELECT * FROM devices WHERE address LIKE &1 AND interface = &2 AND enabled is TRUE", sAddress, iInterface)

    IF rDevice.Count > 1 THEN

      Main.WriteDebugLog(("[Devices] The following devices have same address:"))

      FOR EACH rDevice

        Main.WriteDebugLog(("[Devices] Device with address '") & sAddress & ("' is '") & rDevice!name & "'")

      NEXT

      RETURN 0

    ELSE IF rDevice.Count = 1 THEN

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Device with address '") & sAddress & ("' is '") & rDevice!name & "'")

      IF rDevice!firstseen = "00:00:00" OR IF rDevice!firstseen = "" THEN

        IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Device with address '") & sAddress & ("' and '") & rDevice!name & ("' is a new device"))

        rResult = Main.hDB.Exec("UPDATE devices SET firstseen = &1 WHERE id = &2", Format(Now(), "yyyy-mm-dd hh:nn:ss"), rDevice!id)

      END IF

      ' if it's hidden we return 0, else we return it's id

      IF rDevice!hide THEN

        IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Device with address '") & sAddress & ("' and name '") & rDevice!name & ("' is set to hide"))

        RETURN 0

      ELSE

        RETURN rDevice!id

      END IF

    ELSE

      IF NOT IsBlacklisted(sAddress, iInterface) THEN

        IF sDevice THEN

          Main.WriteDebugLog(("[Devices] Device '") & sDevice & ("' with address '") & sAddress & ("' and interface '") & FindNameForInterface(iInterface) & ("' not found!"))

        ELSE

          Main.WriteDebugLog(("[Devices] Device with address '") & sAddress & ("' and interface '") & FindNameForInterface(iInterface) & ("' not found!"))

        END IF

        RETURN 0

      ELSE

        IF Main.bDevicesDebug THEN

          IF sDevice THEN

            Main.WriteDebugLog(("[Devices] Device '") & sDevice & ("' with address '") & sAddress & ("' and interface '") & FindNameForInterface(iInterface) & ("' is blacklisted!"))

          ELSE

            Main.WriteDebugLog(("[Devices] Device with address '") & sAddress & ("' and interface '") & FindNameForInterface(iInterface) & ("' is blacklisted!"))

          END IF

        END IF

      END IF

    END IF

  ELSE

    Main.WriteDebugLog(("[Devices] Interface for device '") & sDevice & ("' with address '") & sAddress & ("' not found!"))

    RETURN 0

  ENDIF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find out if an device has firstseen filled

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB CheckFirstSeen(iId AS Integer)

  DIM rResult, rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT * FROM devices WHERE id = &1", iId)

  IF rDevice.Count THEN

    IF rDevice!firstseen = "00:00:00" OR IF rDevice!firstseen = "" THEN

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Device with address '") & rDevice!address & ("' and '") & rDevice!name & ("' is a new device!"))

      rResult = Main.hDB.Exec("UPDATE devices SET firstseen = &1 WHERE id = &2", Format(Now(), "yyyy-mm-dd hh:nn:ss"), rDevice!id)

    END IF

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' find out if address is blacklisted and return true

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB IsBlacklisted(sAddress AS String, iInterface AS Integer) AS Boolean

  DIM rDevice AS Result

  TRY rDevice = Main.hDB.Exec("SELECT * FROM deviceblacklist WHERE address = &1", sAddress)

  IF rDevice.Count THEN

    IF rDevice!id THEN

      IF iInterface = rDevice!id THEN

        IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Address '") & sAddress & ("' with Interface '") & FindNameForInterface(iInterface) & ("' is blacklisted, so I ignore it."))

        RETURN TRUE

      ELSE

        RETURN FALSE

      END IF

    END IF

    IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Address '") & sAddress & ("' is blacklisted, so I ignore it."))

    RETURN TRUE

  END IF

  RETURN FALSE

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' update device with iId with vValue, vValue2 etc

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB ValueUpdate(iId AS Integer, vValue AS Variant, vValue2 AS Variant, vValue3 AS Variant, vValue4 AS Variant) AS Boolean

  DIM rResult, rDevice AS Result

  DIM bUpdated AS Boolean

  DIM vValUpdated AS NEW Collection

  DIM rDeviceLog AS Result

  DIM vVal AS Variant

  DIM sLabel, sLabel2, sLabel3, sLabel4, sValue, sValue2, sValue3, sValue4, sSql AS String

  IF vValue = "Dim 100" THEN vValue = "On"

  IF vValue = "Dim 0" THEN vValue = "Off"

  rDevice = Main.hDB.Exec("SELECT name, label, label2, label3, label4, value, value2, value3, value4, log FROM devices WHERE id = &1", iId)

  IF rDevice.Count = 1 THEN

    sLabel = rDevice!label

    sLabel2 = rDevice!label2

    sLabel3 = rDevice!label3

    sLabel4 = rDevice!label4

    sValue = rDevice!value

    sValue2 = rDevice!value2

    sValue3 = rDevice!value3

    sValue4 = rDevice!value4

  ENDIF

  IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] ValueUpdate() called for device with id '") & iId & ("' and name '") & rDevice!name & "'")

  IF vValue THEN

    IF Comp(vValue, sValue, gb.Text) <> 0 THEN

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Device change triggered for device with id '") & iId & ("' and value1 '") & vValue & "'")

      EventLoop.DeviceChanged(iId, "Value", vValue, sLabel)

      bUpdated = TRUE

      IF rDevice!log THEN

        vValUpdated["value"] = vValue

      ENDIF

      rResult = Main.hDB.Exec("UPDATE devices SET lastchanged = &1, lastseen = &2, value = &3 WHERE id = &4", Format(Now(), "yyyy-mm-dd hh:nn:ss"), Format(Now(), "yyyy-mm-dd hh:nn:ss"), vValue, iId)

    ENDIF

  ENDIF

  IF vValue2 THEN

    IF Comp(vValue2, sValue2, gb.Text) <> 0 THEN

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Device change triggered for device with id '") & iId & ("' and value2 '") & vValue2 & "'")

      EventLoop.DeviceChanged(iId, "Value2", vValue2, sLabel2)

      bUpdated = TRUE

      IF rDevice!log THEN

        vValUpdated["value2"] = vValue2

      ENDIF

      rResult = Main.hDB.Exec("UPDATE devices SET lastchanged = &1, lastseen = &2, value2 = &3 WHERE id = &4", Format(Now(), "yyyy-mm-dd hh:nn:ss"), Format(Now(), "yyyy-mm-dd hh:nn:ss"), vValue2, iId)

    ENDIF

  ENDIF

  IF vValue3 THEN

    IF Comp(vValue3, sValue3, gb.Text) <> 0 THEN

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Device change triggered for device with id '") & iId & ("' and value3 '") & vValue3 & "'")

      EventLoop.DeviceChanged(iId, "Value3", vValue3, sLabel3)

      bUpdated = TRUE

      IF rDevice!log THEN

        vValUpdated["value3"] = vValue3

      ENDIF

      rResult = Main.hDB.Exec("UPDATE devices SET lastchanged = &1, lastseen = &2, value3 = &3 WHERE id = &4", Format(Now(), "yyyy-mm-dd hh:nn:ss"), Format(Now(), "yyyy-mm-dd hh:nn:ss"), vValue3, iId)

    ENDIF

  ENDIF

  IF vValue4 THEN

    IF Comp(vValue4, sValue4, gb.Text) <> 0 THEN

      IF Main.bDevicesDebug THEN Main.WriteDebugLog(("[Devices] Device change triggered for device with id '") & iId & ("' and value4 '") & vValue4 & "'")

      EventLoop.DeviceChanged(iId, "Value4", vValue4, sLabel4)

      bUpdated = TRUE

      IF rDevice!log THEN

        vValUpdated["value4"] = vValue4

      ENDIF

      rResult = Main.hDB.Exec("UPDATE devices SET lastchanged = &1, lastseen = &2, value4 = &3 WHERE id = &4", Format(Now(), "yyyy-mm-dd hh:nn:ss"), Format(Now(), "yyyy-mm-dd hh:nn:ss"), vValue4, iId)

    ENDIF

  ENDIF

  IF NOT bUpdated THEN

    rResult = Main.hDB.Exec("UPDATE devices SET lastseen = &1 WHERE id = &2", Format(Now(), "yyyy-mm-dd hh:nn:ss"), iId)

  ELSE

    IF rDevice!log THEN

      Main.hDB.Begin()

      rDeviceLog = Main.hDB.Create("devices_log")

      rDeviceLog!deviceid = iId

      rDeviceLog!lastchanged = Now()

      FOR EACH vVal IN vValUpdated

        rDeviceLog[vValUpdated.Key] = vVal

      NEXT

      rDeviceLog.Update()

      Main.hDB.Commit()

    ENDIF

  ENDIF

  Main.RefreshDeviceList()

  IF rResult THEN

    RETURN TRUE

  ELSE

    RETURN FALSE

  ENDIF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' update battery status for device with iId

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC SUB Battery(iId AS Integer, vValue AS Variant) AS Boolean

  DIM rResult AS Result

  rResult = Main.hDB.Exec("UPDATE devices SET batterystatus = &1 WHERE id = &2", vValue, iId)

  IF rResult.Count THEN

    RETURN TRUE

  ELSE

    RETURN FALSE

  END IF

END

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

' mail battery status for device with battery status set

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PUBLIC FUNCTION MailBatteryStatus()

  DIM rDevice AS Result

  DIM sMessageBody AS String

  DIM bFound AS Boolean

  TRY rDevice = Main.hDB.Exec("SELECT name, address, batterystatus, lastseen FROM devices WHERE enabled is TRUE")

  IF rDevice.Available THEN

    IF rDevice.Count THEN

      FOR EACH rDevice

        IF rDevice!batterystatus THEN

          sMessageBody = sMessageBody & "\r" & rDevice!address & " - " & rDevice!name & "  " & rDevice!batterystatus & "  " & Replace$(Str$(Format(rDevice!lastseen, "yyyy-mm-dd hh:nn:ss")), Format(Date(), "yyyy-mm-dd") & " ", "")

          bFound = TRUE

        END IF

      NEXT

      ' sent e-mail with lowbatt status

      IF bFound THEN Mail.SendMail(Application.Name & (": Battery Status"), ("These Devices have their Battery status set: \n") & sMessageBody, Main.sEmailToAddress)

    END IF

  END IF

END