Domoticz

Code: Select all

-- create table that contains the HardwareID's for ZWAVE
DROP VIEW IF EXISTS ZwaveHardwareID;
CREATE VIEW ZwaveHardwareID
AS
SELECT DISTINCT [HardwareID]
FROM   [ZwaveNodes];


-- create table that contains all ZWAVE devices and the hex version of the NodeID to use for matching
DROP VIEW IF EXISTS ZwaveNodes_Temp;
CREATE VIEW ZwaveNodes_Temp
AS
SELECT [NodeId],
       PRINTF ('%02X', [NodeId]) AS "U2",
       [Name],
       [HardwareID]
FROM   [ZwaveNodes];

-- create table that contains all Domoticz devices that below to the HardwareID's for ZWAVE
DROP VIEW IF EXISTS ZwaveDevicesAll;
CREATE VIEW ZwaveDevicesAll
AS
SELECT DISTINCT [DeviceStatus].[DeviceID],
                [DeviceStatus].[Id],
                [DeviceStatus].[Used],
                [DeviceStatus].[HardwareID],
                [DeviceStatus].[Name],
                [DeviceStatus].[nValue],
                [DeviceStatus].[sValue],
                [DeviceStatus].[LastUpdate]
FROM   [DeviceStatus]
       INNER JOIN [ZwaveHardwareID] ON [ZwaveHardwareID].[HardwareID] = [DeviceStatus].[HardwareID];


-- create table with Domoticz devices ordered by ZWAVE device
DROP VIEW IF EXISTS ZwaveDevicesOK;
CREATE VIEW ZwaveDevicesOK
AS
SELECT SUBSTR ([DeviceID], - 4, 2) AS "ZW-NodeID",
       [ZwaveNodes_Temp].[Name] AS "ZW-Name",
       SUBSTR ([DeviceID], 1, 4) AS "Dev",
       SUBSTR ([DeviceID], - 2, 2) AS "Dev1",
       [ZwaveDevicesAll].[Name] AS "Dev-Name",
       [ZwaveDevicesAll].[Id] AS IDX,
       [ZwaveDevicesAll].[Used],
       [Hardware].[Id],
       [Hardware].[Name] AS "H-Name",
       [ZwaveDevicesAll].[DeviceID],
       [ZwaveDevicesAll].[nValue],
       [ZwaveDevicesAll].[sValue],
       [ZwaveDevicesAll].[LastUpdate]
FROM   [ZwaveDevicesAll]
       INNER JOIN [Hardware] ON [Hardware].[Id] = [ZwaveDevicesAll].[HardwareID]
       INNER JOIN [ZwaveNodes_Temp] ON [ZwaveNodes_Temp].[HardwareID] = [ZwaveDevicesAll].[HardwareID] AND [ZwaveNodes_Temp].[U2] = SUBSTR ([DeviceID], - 4, 2)
WHERE  SUBSTR ([DeviceID], - 4, 2) <> "00"
       AND length([DeviceID]) > 5
UNION ALL
SELECT SUBSTR ([DeviceID], - 2, 2) AS "ZW-NodeID",
       [ZwaveNodes_Temp].[Name] AS "ZW-Name",
       SUBSTR ([DeviceID], 1, 4) AS "Dev",
       "xx" AS "Dev1",
       [ZwaveDevicesAll].[Name] AS "Dev-Name",
       [ZwaveDevicesAll].[Id] AS IDX,
       [ZwaveDevicesAll].[Used],
       [Hardware].[Id],
       [Hardware].[Name] AS "H-Name",
       [ZwaveDevicesAll].[DeviceID],
       [ZwaveDevicesAll].[nValue],
       [ZwaveDevicesAll].[sValue],
       [ZwaveDevicesAll].[LastUpdate]
FROM   [ZwaveDevicesAll]
       INNER JOIN [Hardware] ON [Hardware].[Id] = [ZwaveDevicesAll].[HardwareID]
       INNER JOIN [ZwaveNodes_Temp] ON [ZwaveNodes_Temp].[HardwareID] = [ZwaveDevicesAll].[HardwareID] AND [ZwaveNodes_Temp].[U2] = SUBSTR ([DeviceID], - 2, 2)
WHERE  [Hardware].[Name] = "ZWAVE"
       AND SUBSTR ([DeviceID], - 4, 2) = "00"
       AND length([DeviceID]) > 5
UNION ALL
SELECT SUBSTR (PRINTF ('%08X', [DeviceID]), - 4, 2) AS "ZW-NodeID",
       [ZwaveDevicesAll].[Name] AS "Dev-Name",
       SUBSTR (PRINTF ('%08X', [DeviceID]), 1, 4) AS "Dev",
       "yy" AS "Dev1",
       [ZwaveNodes_Temp].[Name] AS "ZW-Name",
       [ZwaveDevicesAll].[Id] AS IDX,
       [ZwaveDevicesAll].[Used],
       [Hardware].[Id],
       [Hardware].[Name] AS "H-Name",
       [ZwaveDevicesAll].[DeviceID],
       [ZwaveDevicesAll].[nValue],
       [ZwaveDevicesAll].[sValue],
       [ZwaveDevicesAll].[LastUpdate]
FROM   [ZwaveDevicesAll]
       INNER JOIN [Hardware] ON [Hardware].[Id] = [ZwaveDevicesAll].[HardwareID]
       INNER JOIN [ZwaveNodes_Temp] ON [ZwaveNodes_Temp].[HardwareID] = [ZwaveDevicesAll].[HardwareID] AND [ZwaveNodes_Temp].[U2] = SUBSTR (PRINTF ('%08X', [DeviceID]), - 4, 2)
WHERE  length([DeviceID]) < 6
ORDER  BY [ZW-NodeID],
          [Dev];

-- create table with devices that don't below to any ZWAVE device anymore
DROP VIEW IF EXISTS ZwaveDevicesWrong;
CREATE VIEW ZwaveDevicesWrong
AS
SELECT [ZwaveDevicesAll].[DeviceID],
       [ZwaveDevicesAll].[Name] AS "Dev-Name",
       [ZwaveDevicesAll].[Id] AS IDX,
       [ZwaveDevicesOK].[Idx] AS OKIDX,
       [ZwaveDevicesAll].[Used],
       [ZwaveDevicesAll].[DeviceID],
       [ZwaveDevicesAll].[nValue],
       [ZwaveDevicesAll].[sValue],
       [ZwaveDevicesAll].[LastUpdate]
FROM   [ZwaveDevicesAll]
       LEFT JOIN [ZwaveDevicesOK] ON [ZwaveDevicesOK].[Idx] = [ZwaveDevicesAll].[ID]
WHERE [ZwaveDevicesOK].[Idx] is null
ORDER  BY [IDX]

Code: Select all

--------------------------------------------------------------------------------------------------------------------------
-- create temp table that contains all Domoticz devices that belong to the HardwareID's for ZWAVE
--------------------------------------------------------------------------------------------------------------------------
DROP TABLE IF EXISTS _ZWTempAll;
CREATE TABLE _ZWTempAll
AS
SELECT DISTINCT
        "??"                AS "ZW-ID",
        "??"                AS "ZW-Name",
        [DeviceStatus].[Id] AS "D-IDX",
        [DeviceStatus].[Name] AS "D-Name",
        [DeviceStatus].[Used] AS "D-Used",
        [DeviceStatus].[nValue],
        [DeviceStatus].[sValue],
        [DeviceStatus].[LastUpdate],
        "?" AS "Sel",
        [DeviceStatus].[DeviceID],
        "?" AS "HexDevId",
        [DeviceStatus].[HardwareID]
FROM   [DeviceStatus]
       INNER JOIN [ZwaveNodes] ON [ZwaveNodes].[HardwareID] = [DeviceStatus].[HardwareID]
;
--------------------------------------------------------------------------------------------------------------------------
-- Update table with ZWAVE ID for DeviceIDs that contain the ZWAVE ID in a dec DeviceID which will be converted to Hex
--------------------------------------------------------------------------------------------------------------------------
UPDATE _ZWTempAll
SET [HexDevId] = PRINTF ('%08X', [DeviceID]),
    [Sel] = "a"
WHERE  length([DeviceID]) < 7
;
UPDATE _ZWTempAll
SET [HexDevId] = [DeviceID],
    [Sel] = "b"
WHERE  length([DeviceID]) > 6
;
--------------------------------------------------------------------------------------------------------------------------
-- Update table with ZWAVE ID for DeviceIDs that contain the ZWAVE ID in the first 2 of the last 4 characters
--------------------------------------------------------------------------------------------------------------------------
UPDATE _ZWTempAll
SET [ZW-ID] = SUBSTR ([HexDevId], - 4, 2),
    [Sel] = "1"
WHERE  SUBSTR ([HexDevId], - 4, 2) <> "00"
;
--------------------------------------------------------------------------------------------------------------------------
-- Update table with ZWAVE ID for DeviceIDs that contain the ZWAVE ID in the last 2 characters
--------------------------------------------------------------------------------------------------------------------------
UPDATE _ZWTempAll
SET [ZW-ID] = SUBSTR ([HexDevId], - 2, 2),
    [Sel] = "2"
WHERE  SUBSTR ([HexDevId], -4, 2) = "00"
;
--------------------------------------------------------------------------------------------------------------------------
-- Update table with ZWAVE NAME for the ZWAVE device
--------------------------------------------------------------------------------------------------------------------------
With a AS
(
SELECT [ZwaveNodes].[Name]  AS "ZW-Name",
       [_ZWTempAll].[D-IDX]
FROM   [_ZWTempAll]
       INNER JOIN [ZwaveNodes] ON [ZwaveNodes].[HardwareID] = [_ZWTempAll].[HardwareID] AND PRINTF ('%02X', [ZwaveNodes].[NodeId]) = [ZW-ID]
)
UPDATE _ZWTempAll
SET [ZW-Name] = (SELECT "ZW-NAME" FROM a WHERE [a].[D-IDX] =[_ZWTempAll].[D-IDX] )
WHERE
EXISTS (
     SELECT *
     FROM a
     WHERE [a].[D-IDX] = [_ZWTempAll].[D-IDX]
)
;
--------------------------------------------------------------------------------------------------------------------------
-- Update table for OLD ZWAVE devices that aren't linked to an existing ZWAVE device anymore.
--------------------------------------------------------------------------------------------------------------------------
UPDATE _ZWTempAll
SET   [ZW-Name] = "*** Not Found ***",
      [Sel] = "*Err*"
WHERE [_ZWTempAll].[ZW-Name] = "??"
;
-- create sorted table which contains all ZWAVE devices and their ZWAVE device info sorted on ZW-ID and IDX
DROP TABLE IF EXISTS _ZWDevAll;
CREATE TABLE _ZWDevAll
AS
SELECT *
FROM   [_ZWTempAll]
Order by [ZW-ID],[D-IDX]
;
-- remove temp table
DROP TABLE IF EXISTS _ZWTempAll;

Code: Select all

+++ b/www/app/DevicesController.js
@@ -384,6 +384,13 @@ define(['app'], function (app) {
                 itemSubIcons += '&nbsp;<img src="images/empty16.png">';
              }
              var ID = item.ID;
+             if (item.HardwareTypeVal != undefined && item.HardwareTypeVal == 21) {
+                var ZWID = item.ID.substr(-4, 2);
+                if (ZWID == '00') {
+                  ZWID = item.ID.substr(-2, 2);
+                }
+                item.HardwareName = item.HardwareName + ' ' + ZWID;
+             }
              if (item.Type == "Lighting 1") {
                ID = String.fromCharCode(item.ID);
              }