ich führe unsere HomeMatic-Inventarliste in einer Datenbank welche ich in regelmässigen Abständen automatisch durch einen Node-RED Flow aktuell halte - funktioniert soweit perfekt.
Nachdem ich - wie vermutlich so viele andere auch - sämtliche QR-Aufkleber in einer großen Schuhschachtel sammle, sie aber noch nie digital erfasst habe, kam nach einem interessanten Gedankenaustausch zum Thema Dokumentation am Wochenende folgendes zustande:
Eine einfache Page im Node-RED Dashboard namens "Inventur", bestehend aus einem Texteingabefeld und zwei Buttons:
Der Flow selber ist extrem einfach gehalten:
Code: Alles auswählen
[{"id":"ea75d76a.51baf8","type":"ui_text","z":"1bced7e.eb07028","group":"ca7c67cf.740b78","order":4,"width":0,"height":0,"name":"","label":"ADDRESS","format":"{{msg.payload[0].ADDRESS}}","layout":"row-spread","x":490,"y":340,"wires":[]},{"id":"7f8432a3.06c16c","type":"MSSQL","z":"1bced7e.eb07028","mssqlCN":"429e639.206fe9c","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"","modeOpt":"","modeOptType":"query","queryOpt":"payload","queryOptType":"msg","paramsOpt":"","paramsOptType":"none","params":[],"x":500,"y":120,"wires":[["ea75d76a.51baf8","c906275b.315cb8","b4f0576c.5950a8","d0274e7c.91f18","283c666d.2e03aa","8a5bb6a6.215598"]]},{"id":"89cf2088.ce1cf","type":"template","z":"1bced7e.eb07028","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"SELECT \n\tDevice.ADDRESS, \n\tDevice.TYPE,\n\tDevice.QR,\n\tDevice.QR_KEY,\n\tDevice.QR_SGTIN,\n\tDevice.deviceName\nFROM HomeMatic.Device\nWHERE Device.ADDRESS = SUBSTRING('{{payload}}', 17, 14);","output":"str","x":320,"y":120,"wires":[["7f8432a3.06c16c"]]},{"id":"c906275b.315cb8","type":"ui_text","z":"1bced7e.eb07028","group":"ca7c67cf.740b78","order":5,"width":0,"height":0,"name":"","label":"TYPE","format":"{{msg.payload[0].TYPE}}","layout":"row-spread","x":470,"y":380,"wires":[]},{"id":"b4f0576c.5950a8","type":"ui_text","z":"1bced7e.eb07028","group":"ca7c67cf.740b78","order":6,"width":0,"height":0,"name":"","label":"QR_KEY","format":"{{msg.payload[0].QR_KEY}}","layout":"row-spread","x":480,"y":460,"wires":[]},{"id":"d0274e7c.91f18","type":"ui_text","z":"1bced7e.eb07028","group":"ca7c67cf.740b78","order":8,"width":0,"height":0,"name":"","label":"QR_SGTIN","format":"{{msg.payload[0].QR_SGTIN}}","layout":"row-spread","x":490,"y":500,"wires":[]},{"id":"283c666d.2e03aa","type":"ui_text","z":"1bced7e.eb07028","group":"ca7c67cf.740b78","order":3,"width":0,"height":0,"name":"","label":"deviceName","format":"{{msg.payload[0].deviceName}}","layout":"row-spread","x":490,"y":300,"wires":[]},{"id":"d56cf097.38b4e","type":"switch","z":"1bced7e.eb07028","name":"","property":"payload","propertyType":"msg","rules":[{"t":"nempty"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":150,"y":160,"wires":[["89cf2088.ce1cf","56fa116c.f58fb"],["ea75d76a.51baf8","c906275b.315cb8","b4f0576c.5950a8","d0274e7c.91f18","283c666d.2e03aa","8a5bb6a6.215598"]]},{"id":"24987a3d.4e0516","type":"ui_button","z":"1bced7e.eb07028","name":"","group":"ca7c67cf.740b78","order":10,"width":0,"height":0,"passthru":false,"label":"import QR to DB","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":120,"y":620,"wires":[["a6b516dc.c360c8"]]},{"id":"88c0d9a1.624a98","type":"ui_text_input","z":"1bced7e.eb07028","name":"","label":"QR","tooltip":"Scan your QR Code here","group":"ca7c67cf.740b78","order":1,"width":0,"height":0,"passthru":true,"mode":"text","delay":"300","topic":"","x":450,"y":40,"wires":[["d56cf097.38b4e"]]},{"id":"e06cdd87.e6b05","type":"change","z":"1bced7e.eb07028","name":"clear","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":40,"wires":[["88c0d9a1.624a98"]]},{"id":"9d90be37.b327d","type":"ui_button","z":"1bced7e.eb07028","name":"clean Scan Field","group":"ca7c67cf.740b78","order":11,"width":0,"height":0,"passthru":false,"label":"clean scan field","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"","x":130,"y":40,"wires":[["e06cdd87.e6b05"]]},{"id":"56fa116c.f58fb","type":"change","z":"1bced7e.eb07028","name":"QR -> flow.QR","rules":[{"t":"move","p":"payload","pt":"msg","to":"QR","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":160,"wires":[[]]},{"id":"a6b516dc.c360c8","type":"template","z":"1bced7e.eb07028","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"UPDATE HomeMatic.Device\nSET Device.QR = '{{flow.QR}}',\nDevice.QR_SGTIN = SUBSTRING('{{flow.QR}}', 7, 24),\nDevice.QR_KEY = SUBSTRING('{{flow.QR}}', 34, 17)\nWHERE Device.ADDRESS = SUBSTRING('{{flow.QR}}', 17, 14);","output":"str","x":300,"y":620,"wires":[["d2b9ad21.e956"]]},{"id":"d2b9ad21.e956","type":"MSSQL","z":"1bced7e.eb07028","mssqlCN":"429e639.206fe9c","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"","modeOpt":"","modeOptType":"query","queryOpt":"payload","queryOptType":"msg","paramsOpt":"","paramsOptType":"none","params":[],"x":480,"y":620,"wires":[["a4736e9d.5ecb5"]]},{"id":"a4736e9d.5ecb5","type":"change","z":"1bced7e.eb07028","name":"get flow.QR","rules":[{"t":"set","p":"payload","pt":"msg","to":"QR","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":670,"y":620,"wires":[["d56cf097.38b4e"]]},{"id":"8a5bb6a6.215598","type":"ui_text","z":"1bced7e.eb07028","group":"ca7c67cf.740b78","order":7,"width":0,"height":0,"name":"","label":"QR","format":"{{msg.payload[0].QR}}","layout":"row-spread","x":470,"y":420,"wires":[]},{"id":"ca7c67cf.740b78","type":"ui_group","z":"","name":"HomeMatic QR Scanner","tab":"c781a868.43f4f8","order":2,"disp":true,"width":12,"collapse":false},{"id":"429e639.206fe9c","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"SQL mix.milnet.at","server":"10.20.30.40","port":"1433","encyption":false,"database":"milnet","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true},{"id":"c781a868.43f4f8","type":"ui_tab","z":"","name":"Inventur","icon":"dashboard","order":16,"disabled":false,"hidden":false}]Zum Testen reichte ein iPhone, den erkannten QR-Code kann man mittels copy/paste ins Node-RED Dashboard übertragen, ist allerdings nicht allzu komfortabel. Ein günstiger QR-Scanner ist bei den üblichen Verdächtigen allerdings um unter EUR 40,- zu bekommen und erhöht die Lebensqualität ungemein
Liebe Grüße aus Baden bei Wien,
Alexandra
PS: die doppelten KEY/SGTIN-Felder in der Datenbank rühren von meinen vorherigen manuellen Eingabeversuchen, die ich mangels Motivation relativ schnell abgebrochen habe.