Query Tag Mqtt SQL lite

gistfile1.txt

[{"id":"fd04a8e8.b4e6d8","type":"tab","label":"Aedes Mqtt 17 Dicembre","disabled":false,"info":""},{"id":"b0ea04d4.e7e008","type":"mqtt in","z":"fd04a8e8.b4e6d8","name":"","topic":"/ITS","qos":"2","datatype":"auto","broker":"abf1da65.1b0658","x":330,"y":160,"wires":[["9ca6ade7.6be3a"]]},{"id":"9ca6ade7.6be3a","type":"debug","z":"fd04a8e8.b4e6d8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":510,"y":160,"wires":[]},{"id":"89293660.f058e8","type":"mqtt out","z":"fd04a8e8.b4e6d8","name":"","topic":"/ITS","qos":"","retain":"","broker":"abf1da65.1b0658","x":490,"y":100,"wires":[]},{"id":"38ec37e4.694968","type":"inject","z":"fd04a8e8.b4e6d8","name":"","topic":"","payload":"{\"message\":\"Ciao Ragazz*\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":190,"y":40,"wires":[["89293660.f058e8"]]},{"id":"5e1e33f5.97aa7c","type":"inject","z":"fd04a8e8.b4e6d8","name":"complete json string (open)","topic":"/hello","payload":"{\"name\":\"Pino Daniele\",\"status\":\"open\",\"pin\":2,\"color\":\"red\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":230,"y":400,"wires":[["c609339b.817b"]]},{"id":"6e7a6617.465ca8","type":"inject","z":"fd04a8e8.b4e6d8","name":"complete json string (closed)","topic":"/hello","payload":"{\"name\":\"LED\",\"status\":\"closed\",\"pin\":0,\"color\":\"red\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":220,"y":440,"wires":[["c609339b.817b"]]},{"id":"23ac7c28.6cb754","type":"inject","z":"fd04a8e8.b4e6d8","name":"json for status open","topic":"/hello","payload":"{    \"status\": \"open\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":250,"y":480,"wires":[["c609339b.817b"]]},{"id":"8ceb1d82.ed578","type":"inject","z":"fd04a8e8.b4e6d8","name":"json for pin 0","topic":"/hello","payload":"{    \"pin\": 0}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":270,"y":520,"wires":[["c609339b.817b"]]},{"id":"c609339b.817b","type":"mqtt out","z":"fd04a8e8.b4e6d8","name":"","topic":"/ITS","qos":"","retain":"","broker":"abf1da65.1b0658","x":530,"y":480,"wires":[]},{"id":"259e926e.03668e","type":"comment","z":"fd04a8e8.b4e6d8","name":"Usiamo HiveMQ online!","info":"","x":420,"y":40,"wires":[]},{"id":"eac1ceb4.9a228","type":"inject","z":"fd04a8e8.b4e6d8","name":"","topic":"","payload":"{\"id\":\"Gomba\",\"message\":\"riprendiamo ragazzi?\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":190,"y":100,"wires":[["89293660.f058e8"]]},{"id":"df625ea4.e2dcb","type":"mqtt in","z":"fd04a8e8.b4e6d8","name":"","topic":"/ITS-1","qos":"2","datatype":"json","broker":"abf1da65.1b0658","x":330,"y":220,"wires":[["cb09640d.042f88","22f68946.55f056"]]},{"id":"cb09640d.042f88","type":"debug","z":"fd04a8e8.b4e6d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":510,"y":220,"wires":[]},{"id":"e1d86f1a.393f5","type":"comment","z":"fd04a8e8.b4e6d8","name":"Interroghiamo le nostre API","info":"","x":430,"y":340,"wires":[]},{"id":"7d5347d5.3b49c8","type":"comment","z":"fd04a8e8.b4e6d8","name":"capiamo se l'utente può accedere","info":"","x":450,"y":640,"wires":[]},{"id":"62478e9c.fbd76","type":"sqlite","z":"fd04a8e8.b4e6d8","mydb":"4e4abe36.17055","sqlquery":"msg.topic","sql":"","name":"user database","x":580,"y":720,"wires":[["870b3e5a.e70e6"]]},{"id":"f1a2fafc.6b8c38","type":"inject","z":"fd04a8e8.b4e6d8","name":"CREATE TABLE1","topic":"CREATE TABLE table1(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, tag TEXT, ACCESSO1 TEXT, ACCESSO2 TEXT, ACCESSO3 TEXT)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":"0.1","x":280,"y":720,"wires":[["62478e9c.fbd76"]]},{"id":"c880155d.9512b8","type":"debug","z":"fd04a8e8.b4e6d8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":850,"y":780,"wires":[]},{"id":"b788459b.65e6a8","type":"inject","z":"fd04a8e8.b4e6d8","name":"insert utente Pino Daniele","topic":"INSERT INTO table1 VALUES(1, \"Pino Daniele\",\"421418319424897128\",\"Y\", \"N\", \"N\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":250,"y":760,"wires":[["62478e9c.fbd76"]]},{"id":"9d6b907e.58ef1","type":"function","z":"fd04a8e8.b4e6d8","name":"QUERY TAG","func":"msg.topic  = \"SELECT DISTINCT name FROM table1 WHERE tag = \"+ msg.payload\nreturn msg;\n","outputs":1,"noerr":0,"x":290,"y":920,"wires":[["62478e9c.fbd76"]]},{"id":"312e9a48.dd6fa6","type":"inject","z":"fd04a8e8.b4e6d8","name":"Query for NFC TAG","topic":"SELECT DISTINCT name FROM table1 WHERE tag =  \"555556319424807026\"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":270,"y":880,"wires":[["62478e9c.fbd76"]]},{"id":"27b3e990.056cf6","type":"inject","z":"fd04a8e8.b4e6d8","name":"insert utente Franco Battiato","topic":"INSERT INTO table1 VALUES(2, \"Franco Battiato\",\"521416319424807026\",\"N\", \"Y\", \"N\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":240,"y":800,"wires":[["62478e9c.fbd76"]]},{"id":"6e5a1c50.316254","type":"inject","z":"fd04a8e8.b4e6d8","name":"insert utente Francesco De Gregori","topic":"INSERT INTO table1 VALUES(3, \"Francesco De Gregori\",\"555556319424807026\",\"N\", \"Y\", \"N\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":220,"y":840,"wires":[["62478e9c.fbd76"]]},{"id":"22f68946.55f056","type":"change","z":"fd04a8e8.b4e6d8","name":"Estraiamo il numero della tessera","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.tessera","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":580,"y":280,"wires":[["e511756a.2c0748","5c541c9b.39ddb4"]]},{"id":"e511756a.2c0748","type":"debug","z":"fd04a8e8.b4e6d8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":970,"y":240,"wires":[]},{"id":"870b3e5a.e70e6","type":"change","z":"fd04a8e8.b4e6d8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].name","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":780,"wires":[["c880155d.9512b8","fa9dc10c.bd5f5"]]},{"id":"fa9dc10c.bd5f5","type":"template","z":"fd04a8e8.b4e6d8","name":"","field":"payload","fieldType":"msg","format":"json","syntax":"mustache","template":"{\n    \"name\": \"{{payload}}\",\n    \"status\": \"open\",\n    \"pin\": 2,\n    \"color\": \"red\"\n}  ","output":"str","x":840,"y":720,"wires":[["dabc05ef.797d78","6dbb6ce4.03d3b4"]]},{"id":"6dbb6ce4.03d3b4","type":"mqtt out","z":"fd04a8e8.b4e6d8","name":"","topic":"/ITS","qos":"","retain":"","broker":"abf1da65.1b0658","x":1050,"y":720,"wires":[]},{"id":"dabc05ef.797d78","type":"debug","z":"fd04a8e8.b4e6d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1070,"y":780,"wires":[]},{"id":"5c541c9b.39ddb4","type":"link out","z":"fd04a8e8.b4e6d8","name":"","links":[],"x":795,"y":320,"wires":[]},{"id":"dd7666fe.ae0018","type":"link in","z":"fd04a8e8.b4e6d8","name":"","links":[],"x":175,"y":920,"wires":[["9d6b907e.58ef1"]]},{"id":"abf1da65.1b0658","type":"mqtt-broker","z":"","name":"HiveMQ","broker":"broker.hivemq.com","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"4e4abe36.17055","type":"sqlitedb","z":"","db":"C:\\Users\\Davide Gomba\\Documents\\A-ITS\\online course\\db\\user-db.db","mode":"RWC"}]