Good evening gents, I have a few issues that are just getting the better of me and don't seem to figure this out, probably this something small, but I don't seem to get it right.
Hardware: Raspberry Pi 4 / Node Red version : 1.0.6
Issue:1 - Add data to the SQLite dB from 2 different incoming MQTT nodes. I have used the previous code that is confirmed working in the other flow but for some reason or syntax issue, I just don't seem to get it to pass the data to the reliant DB.
// the code that works now modified below for a new table ..
var myymd= msg.payload.myymd;
var mytimes= msg.payload.mytimes;
var _msgid= msg.payload._msgid;
var door_id= msg.payload["Door ID"];
var door_otp= msg.payload.OTP;
var hashkey= msg.payload.hashkey;
//create SQL command
msg.topic = `INSERT INTO ACCREQ (
DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY
) values (
'${myymd}', '${mytimes}', '${_msgid}', ${door_id}, ${door_otp} ,${hashkey}
);`
return msg;
Issue:2, Now I need to collect the data for the user's table one by one, encrypt them and check if the key matches = if true, then pass 1 or 0
Can some please be of some assistance, it will be greatly appreciated. Thank you in advance
Below is my complete flow of what I would like to achieve ....tonight ( : wink:)
herewith my flow
[{"id":"c7ac4dd1.1d648","type":"tab","label":"New Acc Request","disabled":false,"info":""},{"id":"3fba7a44.43c286","type":"debug","z":"c7ac4dd1.1d648","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":650,"y":160,"wires":[]},{"id":"168fe769.a484b9","type":"mqtt in","z":"c7ac4dd1.1d648","name":"PSA001 - QR Value","topic":"accreq","qos":"0","datatype":"auto","broker":"57c985f0.a39e8c","x":170,"y":160,"wires":[["3fba7a44.43c286","64b4ac9b.9d95a4","38b16044.77229","190917f3.d79a18","a9dd3d9f.c5e95"]]},{"id":"64b4ac9b.9d95a4","type":"ui_text","z":"c7ac4dd1.1d648","group":"6771f7fc.19f008","order":2,"width":26,"height":1,"name":"PSA001 - QR Value Dissplay","label":"PSA001 - DoorID:001 - QR HASH Value : ","format":"{{msg.payload}}","layout":"row-left","x":720,"y":200,"wires":[]},{"id":"9bfc35d3.f29208","type":"rpi-gpio out","z":"c7ac4dd1.1d648","name":"PSA001 INStatus LED","pin":"33","set":true,"level":"0","freq":"","out":"out","x":1100,"y":120,"wires":[]},{"id":"829ab0fc.2211b","type":"ui_led","z":"c7ac4dd1.1d648","group":"6771f7fc.19f008","order":1,"width":4,"height":1,"label":"Recived ","labelPlacement":"left","labelAlignment":"left","colorForValue":[{"color":"red","value":"0","valueType":"num"},{"color":"green","value":"1","valueType":"num"}],"allowColorForValueInMessage":false,"name":"","x":1050,"y":80,"wires":[]},{"id":"38b16044.77229","type":"trigger","z":"c7ac4dd1.1d648","op1":"1","op2":"0","op1type":"num","op2type":"str","duration":"5","extend":false,"units":"s","reset":"5","bytopic":"all","name":"","x":660,"y":120,"wires":[["829ab0fc.2211b","9bfc35d3.f29208"]]},{"id":"f73eb69a.3337b8","type":"sqlite","z":"c7ac4dd1.1d648","mydb":"9db34194.66aca","sqlquery":"msg.topic","sql":"","name":"PSA-DB ","x":1540,"y":320,"wires":[["25617dd6.b83862"]]},{"id":"25617dd6.b83862","type":"debug","z":"c7ac4dd1.1d648","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1730,"y":320,"wires":[]},{"id":"10aa3ead.9de511","type":"mqtt in","z":"c7ac4dd1.1d648","name":"PSA001 - OTP","topic":"psa001/d1/user_input ","qos":"0","datatype":"json","broker":"57c985f0.a39e8c","x":120,"y":580,"wires":[["7ed0ff44.ba906"]]},{"id":"190917f3.d79a18","type":"function","z":"c7ac4dd1.1d648","name":"Get Hashkey from mqtt payload","func":"h = msg.payload;\nreturn {topic:\"psa001/d1/qrvalue\", hashkey:h};","outputs":1,"noerr":0,"x":670,"y":340,"wires":[["b41435aa.e49b58"]]},{"id":"7ed0ff44.ba906","type":"simpletime","z":"c7ac4dd1.1d648","name":"Time Stamp ","mydate":false,"myymd":true,"myyear":false,"mymonth":false,"mymonthn":false,"mydom":false,"mydoy":false,"myday":false,"myhourpm":false,"myhour":false,"mytime":false,"mytimes":true,"myminute":false,"myminutes":false,"mysecond":false,"mymillis":false,"myepoch":false,"myrawdate":false,"mypm":false,"x":570,"y":580,"wires":[["b41435aa.e49b58"]]},{"id":"b41435aa.e49b58","type":"join","z":"c7ac4dd1.1d648","name":"Wait and Join ","mode":"custom","build":"merged","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":960,"y":420,"wires":[["d709170b.774078"]]},{"id":"a9dd3d9f.c5e95","type":"delay","z":"c7ac4dd1.1d648","name":"Delay 2sec","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":130,"y":420,"wires":[["c63e22af.c892c"]]},{"id":"cb55e176.c7aa7","type":"change","z":"c7ac4dd1.1d648","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"complete","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":420,"wires":[["b41435aa.e49b58"]]},{"id":"40ebe0ca.2bbef","type":"debug","z":"c7ac4dd1.1d648","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1530,"y":420,"wires":[]},{"id":"c63e22af.c892c","type":"trigger","z":"c7ac4dd1.1d648","op1":"true","op2":"0","op1type":"bool","op2type":"str","duration":"0","extend":false,"units":"ms","reset":"5","bytopic":"all","name":"Trigger TRUE Value ","x":380,"y":420,"wires":[["cb55e176.c7aa7","1f8956cf.724ea9"]]},{"id":"1f8956cf.724ea9","type":"trigger","z":"c7ac4dd1.1d648","op1":"5","op2":"","op1type":"str","op2type":"str","duration":"0","extend":false,"units":"s","reset":"true ","bytopic":"all","name":"","x":380,"y":500,"wires":[["c63e22af.c892c"]]},{"id":"d709170b.774078","type":"function","z":"c7ac4dd1.1d648","name":"PSA DB - Insert - ACCREQ","func":"var myymd= msg.payload.myymd;\nvar mytimes= msg.payload.mytimes;\nvar _msgid= msg.payload._msgid;\nvar door_id= msg.payload[\"Door ID\"];\nvar door_otp= msg.payload.OTP;\nvar hashkey= msg.payload.hashkey;\n\n\n//create SQL command\nmsg.topic = `INSERT INTO ACCREQ (\n DATE, TIME, MSGID_ID, DOOR_ID, DOOR_OTP, HASHKEY\n) values (\n '${myymd}', '${mytimes}', '${_msgid}', ${door_id}, ${door_otp} ,${hashkey}\n);`\nreturn msg;","outputs":1,"noerr":0,"x":1280,"y":420,"wires":[["40ebe0ca.2bbef","ba7bf1bd.b6b0a","999245d.337c3b8","f73eb69a.3337b8"]]},{"id":"ba7bf1bd.b6b0a","type":"function","z":"c7ac4dd1.1d648","name":"Read DB for user 1 ","func":"SELECT * FROM USERS \n \nreturn msg;\n\nthen\n\n// use the result from the db quiry to encrypt one by one with the \"Door_id\"+\"Door_otp\" \n\nvar user_name= msg.payload.user_name;\nvar user_surname= msg.payload.User_surname;\nvar user_pin= msg.payload.user_pin;\nvar user_acc_level= msg.payload.user_acc_level;\nvar user_acc_code= msg.payload.user_acc_code;\n\n\n","outputs":1,"noerr":7,"x":810,"y":680,"wires":[["a728b689.0aca28","6365a6f2.77f0f8"]]},{"id":"a728b689.0aca28","type":"digest","z":"c7ac4dd1.1d648","name":"Encrypt User Data to SHA256","algorithm":"SHA256","x":1130,"y":680,"wires":[["4f8f6e92.4651c"]]},{"id":"999245d.337c3b8","type":"function","z":"c7ac4dd1.1d648","name":"Read DB for ACCREQ Data ","func":"SELECT * FROM USERS \n \nreturn msg;\n\nthen\n\n// use the result from the db quiry to encrypt one by one with the \"Door_id\"+\"Door_otp\" \n\nvar user_name= msg.payload.user_name;\nvar user_surname= msg.payload.User_surname;\nvar user_pin= msg.payload.user_pin;\nvar user_acc_level= msg.payload.user_acc_level;\nvar user_acc_code= msg.payload.user_acc_code;\n\n\n","outputs":1,"noerr":7,"x":780,"y":760,"wires":[["6365a6f2.77f0f8","a728b689.0aca28"]]},{"id":"4f8f6e92.4651c","type":"match","z":"c7ac4dd1.1d648","name":"","rules":[{"property":"payload","propertyType":"msg","type":"cont","value":"payload.hashkey ","valueType":"msg"}],"x":1370,"y":720,"wires":[["2f4c4ef0.861cd2","fb57ee09.6fa6f","848a2e81.568cc","7816a6e6.61ff28"],["2f4c4ef0.861cd2","fb57ee09.6fa6f"]]},{"id":"848a2e81.568cc","type":"mqtt out","z":"c7ac4dd1.1d648","name":"Notify Base Station node ","topic":"server/accreq/result ","qos":"","retain":"","broker":"d8ebb20f.0a884","x":1730,"y":580,"wires":[]},{"id":"2f4c4ef0.861cd2","type":"ui_led","z":"c7ac4dd1.1d648","group":"162eae12.1020b2","order":1,"width":0,"height":0,"label":"Access Status ","labelPlacement":"left","labelAlignment":"left","colorForValue":[{"color":"red","value":"false","valueType":"bool"},{"color":"green","value":"true","valueType":"bool"}],"allowColorForValueInMessage":false,"name":"Access Status ","x":1700,"y":720,"wires":[]},{"id":"fb57ee09.6fa6f","type":"ui_text","z":"c7ac4dd1.1d648","group":"162eae12.1020b2","order":0,"width":0,"height":0,"name":"Granted / Denied ","label":"Access ","format":"{{msg.payload}}","layout":"row-spread","x":1710,"y":800,"wires":[]},{"id":"7816a6e6.61ff28","type":"mqtt out","z":"c7ac4dd1.1d648","name":"Notify Door node ","topic":"server/accreq/result ","qos":"","retain":"","broker":"d8ebb20f.0a884","x":1710,"y":640,"wires":[]},{"id":"6365a6f2.77f0f8","type":"digest","z":"c7ac4dd1.1d648","name":"Encrypt User Data to SHA256","algorithm":"SHA256","x":1130,"y":760,"wires":[["4f8f6e92.4651c"]]},{"id":"57c985f0.a39e8c","type":"mqtt-broker","z":"","name":"PSA Server 1","broker":"localhost ","port":"1883","clientid":"psa003","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"6771f7fc.19f008","type":"ui_group","z":"","name":"Traffic From Psa001","tab":"8d4ff28.725211","order":1,"disp":true,"width":30,"collapse":false},{"id":"9db34194.66aca","type":"sqlitedb","z":"","db":"psa.db","mode":"RWC"},{"id":"d8ebb20f.0a884","type":"mqtt-broker","z":"","name":"PSA Server 1","broker":"10.11.12.101","port":"1880","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"162eae12.1020b2","type":"ui_group","z":"","name":"PSA-VPN / Pass: Psa@123! - Please Scan QR to get access to PSA-QR GEN-SYST ","tab":"24e16ee9.7041c2","order":2,"disp":true,"width":15,"collapse":false},{"id":"8d4ff28.725211","type":"ui_tab","z":"","name":"Traffic","icon":"dashboard","order":3,"disabled":false,"hidden":false},{"id":"24e16ee9.7041c2","type":"ui_tab","z":"","name":"IP ACCESS ","icon":"dashboard","disabled":false,"hidden":false}]
