添加 SQLite Database 到 IOL City RFID (RFID Homie Scanner)
var st1=msg.topic;
var st2=msg.payload.uid;
var st3=msg.payload.set;
msg.topic = "INSERT INTO CITYRFID (time, Mqtt_Topic_UID, UID_NO ,SET_status) VALUES (?,?,?,?)";
//msg.topic = "INSERT INTO CITYRFID (time, Mqtt_Topic_UID, UID_NO, Mqtt_Topic_SET,SET_status) VALUES (?,?,?,?,?)";
var Today = new Date();
var yyyy = Today.getFullYear(); //年
var MM = Today.getMonth()+1; //月
var dd = Today.getDate(); //日
var h = Today.getHours(); //時
var m = Today.getMinutes(); //分
var s = Today.getSeconds(); //秒
if(MM<10)
{
MM = '0'+MM;
}
if(dd<10)
{
dd = '0'+dd;
}
if(h<10)
{
h = '0'+h;
}
if(m<10)
{
m = '0' + m;
}
if(s<10)
{
s = '0' + s;
}
var hms= yyyy + '/'+ MM + '/'+ dd + ' ' + h + ':' + m + ':' + s ;
//var id= Date.now() ;
//msg.payload = [id ,hms, msg.payload];
//msg.payload = [hms, st1,st2,st3,st4];
msg.payload = [hms, st1,st2,st3];
return msg;
Node-RED 程式
http://www.mediafire.com/file/zuunv7gv1seouke/IOL_City_RFID_SQLite_flows_%25281%2529.json/file
Node-RED 程式
[{"id":"24e85507.8e202a","type":"mqtt in","z":"ec596068.55c12","name":"","topic":"alex9ufo/sensors/e1b47ae0/RFID-1/uid","qos":"1","datatype":"auto","broker":"e4d9b72d.d14398","x":190,"y":100,"wires":[["b9674635.6ec8e8","5b4d6603.a323d8"]]},{"id":"5d424b72.1ced34","type":"switch","z":"ec596068.55c12","name":"RFID Tags","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"d1f21b2b","vt":"str"},{"t":"eq","v":"94587b25","vt":"str"},{"t":"eq","v":"f58a89","vt":"str"},{"t":"eq","v":"b5482a50","vt":"str"},{"t":"else"}],"checkall":"true","outputs":5,"x":450,"y":160,"wires":[["c19abe02.09f24"],["c19abe02.09f24"],["c19abe02.09f24"],["c19abe02.09f24","cb4e06f9.7d0a68"],["7670cf2f.3e947"]]},{"id":"e68f553e.645fb8","type":"function","z":"ec596068.55c12","name":"{\"command\":\"disco\"}","func":"msg.payload = {\"command\":\"disco\"};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":700,"y":300,"wires":[["e1cf9b1b.183f58"]]},{"id":"237ad74.f3a6128","type":"mqtt out","z":"ec596068.55c12","name":"","topic":"alex9ufo/iol/command","qos":"","retain":"","broker":"e4d9b72d.d14398","x":1140,"y":300,"wires":[]},{"id":"a1c4a28b.568c9","type":"inject","z":"ec596068.55c12","name":"","repeat":"","crontab":"","once":false,"topic":"","payload":"disco","payloadType":"str","x":430,"y":300,"wires":[["e68f553e.645fb8"]]},{"id":"b9674635.6ec8e8","type":"debug","z":"ec596068.55c12","name":"sensors/e1b47ae0/RFID-1/uid","active":false,"console":"false","complete":"payload","x":510,"y":60,"wires":[]},{"id":"574f80a0.81813","type":"inject","z":"ec596068.55c12","name":"Forward","props":[{"p":"payload","v":"","vt":"str"},{"p":"topic","v":"motor","vt":"string"}],"repeat":"","crontab":"","once":false,"topic":"motor","payload":"","payloadType":"str","x":440,"y":440,"wires":[["41b856eb.964688"]]},{"id":"7c3d1b24.af17f4","type":"inject","z":"ec596068.55c12","name":"Stop","repeat":"","crontab":"","once":false,"topic":"motor","payload":"0","payloadType":"num","x":430,"y":480,"wires":[["274c4c5d.0e2144"]]},{"id":"48eea8c7.ef5f08","type":"mqtt out","z":"ec596068.55c12","name":"","topic":"alex9ufo/trains/horizonexpress/motor","qos":"2","retain":"true","broker":"e4d9b72d.d14398","x":930,"y":480,"wires":[]},{"id":"41b856eb.964688","type":"function","z":"ec596068.55c12","name":"1024","func":"msg.payload = 1024;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":650,"y":440,"wires":[["48eea8c7.ef5f08","c2dcc992.809548"]]},{"id":"274c4c5d.0e2144","type":"function","z":"ec596068.55c12","name":"0","func":"msg.payload = 0;\nreturn msg;","outputs":1,"noerr":0,"x":650,"y":480,"wires":[["48eea8c7.ef5f08","c2dcc992.809548"]]},{"id":"397898bf.dfea58","type":"mqtt in","z":"ec596068.55c12","name":"","topic":"alex9ufo/sensors/e1b47ae0/#","qos":"0","datatype":"auto","broker":"e4d9b72d.d14398","x":220,"y":580,"wires":[["55ca3298.a8088c","d724d215.494a6","ed45a676.4ecfd8"]]},{"id":"55ca3298.a8088c","type":"debug","z":"ec596068.55c12","name":"sensors/e1b47ae0/#","active":true,"console":"false","complete":"payload","x":480,"y":580,"wires":[]},{"id":"e1cf9b1b.183f58","type":"delay","z":"ec596068.55c12","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":930,"y":300,"wires":[["237ad74.f3a6128","ce6ed262.c6fad"]]},{"id":"20b0832a.4a30ac","type":"mqtt out","z":"ec596068.55c12","name":"","topic":"alex9ufo/sensors/e1b47ae0/RFID-1/validate/set","qos":"","retain":"","broker":"e4d9b72d.d14398","x":1060,"y":180,"wires":[]},{"id":"c19abe02.09f24","type":"function","z":"ec596068.55c12","name":"msg.payload = \"granted\";","func":"msg.topic = msg.payload;\nmsg.payload = \"granted\";\nreturn msg;","outputs":1,"noerr":0,"x":705,"y":126,"wires":[["20b0832a.4a30ac","23ccca08.3dd636","272d9f7b.17b27"]]},{"id":"7670cf2f.3e947","type":"function","z":"ec596068.55c12","name":"msg.payload = \"denied\";","func":"msg.topic = msg.payload;\nmsg.payload = \"denied\";\nreturn msg;","outputs":1,"noerr":0,"x":705,"y":186,"wires":[["20b0832a.4a30ac","272d9f7b.17b27"]]},{"id":"cb4e06f9.7d0a68","type":"trigger","z":"ec596068.55c12","name":"","op1":"1024","op2":"0","op1type":"str","op2type":"str","duration":"5","extend":false,"units":"s","reset":"","bytopic":"all","topic":"topic","outputs":1,"x":660,"y":400,"wires":[["48eea8c7.ef5f08","c2dcc992.809548"]]},{"id":"6dc167a2.922048","type":"comment","z":"ec596068.55c12","name":"RFID Scanner","info":"This flow receives the UID from the RFID scanner\nvia MQTT. The RFID tags are compared against \nseveral workflow options, such as disco, train,etc.\n\nA grant/deny message is sent back to the RFID\nscanner, using the Homie convention. \nhttps://github.com/marvinroger/homie\n\nComplete write-up on the IoL blog:\nwww.InterentOfLEGO.com\n\nwritten by \nCory Guynn\n2016","x":270,"y":40,"wires":[]},{"id":"b0e33b4c.83f398","type":"comment","z":"ec596068.55c12","name":"README","info":"#RFID SCANNER\n\n* This flow listens to an RFID scanner via MQTT.\n\n* A switch node then assigns a workflow based on\nthe 'uid' received.\n\n* An authorization message is sent back to the device \nby setting the 'validate' property.\n\n##Device Info \n* WeMos d1-mini (ESP8266 dev board)\nhttp://www.wemos.cc/Products/d1_mini.html\n\n* RFID RC522\nhttp://playground.arduino.cc/Learning/MFRC522\n\n* Homie MQTT framework\nhttps://github.com/marvinroger/homie/tree/master\n\n\n\n\n## Written by Cory Guynn, 2016\nwww.InternetOfLEGO.com\n\n\n\n","x":100,"y":40,"wires":[]},{"id":"31b1dd74.58ae22","type":"comment","z":"ec596068.55c12","name":"Workflows","info":"","x":260,"y":200,"wires":[]},{"id":"fc6060fa.692c6","type":"comment","z":"ec596068.55c12","name":"Disco Cinema","info":"","x":370,"y":260,"wires":[]},{"id":"9d6336a1.c878b8","type":"comment","z":"ec596068.55c12","name":"Train Routine","info":"","x":410,"y":400,"wires":[]},{"id":"75194cd.9e92cb4","type":"comment","z":"ec596068.55c12","name":"MQTT Monitor","info":"","x":170,"y":540,"wires":[]},{"id":"272d9f7b.17b27","type":"ui_toast","z":"ec596068.55c12","position":"dialog","displayTime":"","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":false,"topic":"確認","name":"RFID","x":950,"y":120,"wires":[[]]},{"id":"23ccca08.3dd636","type":"link out","z":"ec596068.55c12","name":"UID Granted","links":["425a6e8e.3418a","d9391786.7a6b58","7a52bfa0.58d78","409fd8ad.03b418"],"x":855,"y":80,"wires":[]},{"id":"425a6e8e.3418a","type":"link in","z":"ec596068.55c12","name":"","links":["23ccca08.3dd636"],"x":315,"y":300,"wires":[["e68f553e.645fb8"]]},{"id":"9feee5fd.50f468","type":"ui_template","z":"ec596068.55c12","group":"66a6f9e9.2ef518","name":"RFID-1 Log","order":2,"width":"6","height":"7","format":"<h1>Event Log</h1>\n<div>\n<ul>\n <li ng-repeat=\"x in msg.payload\">\n <font color=\"red\">{{x.topic}}</font>\n <ul>\n <li>{{x.payload}}</li>\n </ul>\n </li>\n</ul>\n</div>","storeOutMessages":true,"fwdInMessages":true,"x":710,"y":620,"wires":[[]]},{"id":"d724d215.494a6","type":"function","z":"ec596068.55c12","name":"Store and shift msg","func":"\n\n\n// initialise the counter to 0 if it doesn't exist already\nvar text = context.get('text')|| [];\n\ntext.push(msg);\nif (text.length > 5){\n text.shift();\n text.length = 5;\n} \n\n// store the value back\ncontext.set('text',text);\n// make it part of the outgoing msg object\nmsg = {};\nmsg.payload = text;\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":470,"y":620,"wires":[["9feee5fd.50f468","8d16a18c.2c4dc"]]},{"id":"8d16a18c.2c4dc","type":"debug","z":"ec596068.55c12","name":"RFID-1 log","active":true,"console":"false","complete":"payload","x":710,"y":660,"wires":[]},{"id":"5b4d6603.a323d8","type":"delay","z":"ec596068.55c12","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":true,"x":270,"y":160,"wires":[["5d424b72.1ced34"]]},{"id":"ed45a676.4ecfd8","type":"function","z":"ec596068.55c12","name":"store Homie state","func":"\n\n\n// initialise the state to empty object if it doesn't exist already\nvar state = context.get('state')|| {};\n\n// regular expression to look for Homie updates\nvar t;\nvar re = /([^/]+$)/i;\nt = re.exec(msg.topic);\nvar prop = t[0];\n\n// remove preceding $\nif (prop.substring(0, 1) == '$') { \n prop = prop.substring(1);\n}\n\n// Store state\nstate[prop] = msg.payload;\ncontext.set('state',state);\n\n// send state object\nmsg.payload = state;\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":470,"y":720,"wires":[["3e68cf11.823fe","eebc787b.343488","d738f539.ac34d8","23f8854f.1063fa"]]},{"id":"3e68cf11.823fe","type":"debug","z":"ec596068.55c12","name":"RFID-1 State","active":true,"console":"false","complete":"payload","x":690,"y":720,"wires":[]},{"id":"eebc787b.343488","type":"ui_template","z":"ec596068.55c12","group":"66a6f9e9.2ef518","name":"RFID-1 State","order":1,"width":"6","height":"6","format":"<h1>{{msg.payload.name}}</h1>\n<p ng-style=\"{color: msg.payload.online === 'true' ? 'green' : 'red'}\">\n {{msg.payload.online === 'true' ? 'ONLINE' : 'OFFLINE'}}\n</p>\n<hr>\n<table>\n<tr ng-repeat=\"(key, value) in msg.payload\">\n <td> {{key}} </td> <td> {{ value }} </td>\n</tr>\n</table>\n\n","storeOutMessages":false,"fwdInMessages":true,"templateScope":"local","x":710,"y":760,"wires":[[]]},{"id":"ce6ed262.c6fad","type":"debug","z":"ec596068.55c12","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1120,"y":260,"wires":[]},{"id":"c2dcc992.809548","type":"debug","z":"ec596068.55c12","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":850,"y":400,"wires":[]},{"id":"3419d7b3.cc25f8","type":"comment","z":"ec596068.55c12","name":"被拒絕","info":"","x":750,"y":220,"wires":[]},{"id":"e2d99729.c13148","type":"comment","z":"ec596068.55c12","name":"被授予","info":"","x":750,"y":91,"wires":[]},{"id":"22da80f4.40b8c","type":"mqtt out","z":"ec596068.55c12","name":"","topic":"alex9ufo/sensors/e1b47ae0/RFID-1/uid","qos":"","retain":"","broker":"e4d9b72d.d14398","x":600,"y":940,"wires":[]},{"id":"c04fa163.636ec","type":"inject","z":"ec596068.55c12","name":"卡號1","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"d1f21b2b","payloadType":"str","x":290,"y":860,"wires":[["22da80f4.40b8c"]]},{"id":"73d01dce.2a8b24","type":"inject","z":"ec596068.55c12","name":"卡號2","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"94587b25","payloadType":"str","x":290,"y":900,"wires":[["22da80f4.40b8c"]]},{"id":"8b88697e.9c7e98","type":"inject","z":"ec596068.55c12","name":"卡號3","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"f58a89","payloadType":"str","x":290,"y":940,"wires":[["22da80f4.40b8c"]]},{"id":"867702b7.b61b2","type":"inject","z":"ec596068.55c12","name":"卡號4","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"b5482a50","payloadType":"str","x":290,"y":980,"wires":[["22da80f4.40b8c"]]},{"id":"8c43287d.a26b18","type":"inject","z":"ec596068.55c12","name":"無效卡號","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"b5482a5012","payloadType":"str","x":300,"y":1020,"wires":[["22da80f4.40b8c"]]},{"id":"d738f539.ac34d8","type":"function","z":"ec596068.55c12","name":"INSERT","func":"var st1=msg.topic;\nvar st2=msg.payload.uid;\nvar st3=msg.payload.set;\n\n\n\nmsg.topic = \"INSERT INTO CITYRFID (time, Mqtt_Topic_UID, UID_NO ,SET_status) VALUES (?,?,?,?)\";\n//msg.topic = \"INSERT INTO CITYRFID (time, Mqtt_Topic_UID, UID_NO, Mqtt_Topic_SET,SET_status) VALUES (?,?,?,?,?)\";\n\nvar Today = new Date();\nvar yyyy = Today.getFullYear(); //年\nvar MM = Today.getMonth()+1; //月\nvar dd = Today.getDate(); //日\nvar h = Today.getHours(); //時\nvar m = Today.getMinutes(); //分\nvar s = Today.getSeconds(); //秒\n\nif(MM<10)\n{\n MM = '0'+MM;\n}\n\nif(dd<10)\n{\n dd = '0'+dd;\n}\n\nif(h<10)\n{\n h = '0'+h;\n}\n\nif(m<10)\n{\n m = '0' + m;\n}\n\nif(s<10)\n{\n s = '0' + s;\n}\n\nvar hms= yyyy + '/'+ MM + '/'+ dd + ' ' + h + ':' + m + ':' + s ;\n//var id= Date.now() ;\n//msg.payload = [id ,hms, msg.payload];\n//msg.payload = [hms, st1,st2,st3,st4];\nmsg.payload = [hms, st1,st2,st3];\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":700,"y":820,"wires":[["21f22a12.1b7ab6"]]},{"id":"21f22a12.1b7ab6","type":"sqlite","z":"ec596068.55c12","mydb":"19f59ce9.3edc23","sqlquery":"msg.topic","sql":"","name":"LED Status","x":870,"y":820,"wires":[["899370ff.2a16"]]},{"id":"899370ff.2a16","type":"debug","z":"ec596068.55c12","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1030,"y":820,"wires":[]},{"id":"23f8854f.1063fa","type":"debug","z":"ec596068.55c12","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":480,"y":820,"wires":[]},{"id":"e4d9b72d.d14398","type":"mqtt-broker","z":"","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"66a6f9e9.2ef518","type":"ui_group","z":"ec596068.55c12","name":"RFID","tab":"c1177b42.6553c8","order":6,"disp":true,"width":"6"},{"id":"19f59ce9.3edc23","type":"sqlitedb","z":"","db":"IOLCITY_RFID.db","mode":"RWC"},{"id":"c1177b42.6553c8","type":"ui_tab","z":"","name":"IoL City - Master Dashboard","icon":"dashboard","order":3}]
沒有留言:
張貼留言