Sir please help.
my query is I want that whenever user presses case 3 on gateway side FOB and TRADE1 gets Published and then only it should get updated ,otherwise not
What happening is
when user pressing case 3 both DR on by FOB (which is for case 1 only) FOB and TAG (which i want).DR on by FOB i dont want to print in this case.Please help
Let me rephrase your question to see if I understand it.
You have two different MQTT messages that could arrive - FOB and TAG.
You are trying to handle the case where a user triggers both the FOB and TAG messages, and you want both of those messages to be written in the same row in the database.
Is that a correct description of the problem?
Almost correct but i have another MQTT node name TRADE1
for case 3 i am taking FOB and TRADE1
You say "almost correct" but you don't explain what TRADE1 has to do with it.
Let me try to reread your previous posts to see if I can figure out what you are asking.
Sir, I will elaborate more
I want that whenever user gives FOB and TAG is should get updated with both the successful id in their respective columns(that thing is achieved.)
But issue is that FOB MQTT has 2 flows one printing for single FOB column (which is cant remove because this flow will print for my case 1)another flow is going to my another function in which I am printing both FOB and TAG.(this only i want not the single fob should get printed again for case 3).
I've just imported the flow you shared earlier. It contains 50 nodes, it has lots of Function nodes, none have been named to describe what they do and lots of wires crossing each other making it hard to follow any path. I have no idea what you want us to look at in the flow and I don't have the time to spend trying to figure it out from what you have shared.
I strongly recommend you spend a bit of time tidying it up. Trying to arrange the nodes to avoid lots of crossing wires. Give your Function nodes names to help identify what they actually do. Aside from making it easier for others to help you, it will make it easier for you to maintain.
So is the issue the you have 3 different cases to handle:
- you only get the FOB message from the user
- you only get the TAG message from the user
- you get both FOB and TAG for the same user
And because you have logic handling case 1 for every FOB message, you don't know how to handle case 3 and recognise you have had both a FOB and TAG message from the same user?
(Still no idea what TRADE1 is in any of this)
[
{
"id": "f9a462b5.c005b",
"type": "tab",
"label": "Flow 3",
"disabled": false,
"info": ""
},
{
"id": "745f3dc0.62f50c",
"type": "sqlite",
"z": "f9a462b5.c005b",
"mydb": "1a9ea8ca.835eef",
"sqlquery": "msg.topic",
"sql": "SELECT * FROM NCS WHERE Person_ID='{{payload}}';",
"name": "SQLITE3",
"x": 720,
"y": 420,
"wires": [
[
"15082637.f17282"
]
]
},
{
"id": "15082637.f17282",
"type": "switch",
"z": "f9a462b5.c005b",
"name": "",
"property": "payload",
"propertyType": "msg",
"rules": [
{
"t": "empty"
},
{
"t": "nempty"
}
],
"checkall": "true",
"repair": false,
"outputs": 2,
"x": 810,
"y": 500,
"wires": [
[
"7dda7b2c.a92e5c"
],
[
"50967bd4.cf3c24"
]
]
},
{
"id": "7dda7b2c.a92e5c",
"type": "change",
"z": "f9a462b5.c005b",
"name": "",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "CR_FAIL",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1000,
"y": 420,
"wires": [
[
"14910207.c303e6"
]
]
},
{
"id": "50967bd4.cf3c24",
"type": "change",
"z": "f9a462b5.c005b",
"name": "",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "CR_SUCCESS",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1000,
"y": 560,
"wires": [
[
"14910207.c303e6",
"6a667929.43126"
]
]
},
{
"id": "60631b91.6502ac",
"type": "function",
"z": "f9a462b5.c005b",
"name": "check FOB ",
"func": "let newMsg = {};\nnewMsg.topic = `SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='${msg.payload}'`;\nreturn newMsg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 490,
"y": 420,
"wires": [
[
"745f3dc0.62f50c"
]
]
},
{
"id": "e8af5769.005968",
"type": "function",
"z": "f9a462b5.c005b",
"name": "id no space ",
"func": "msg.payload = msg.payload.replace(/\\s/g, '')\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 250,
"y": 420,
"wires": [
[
"f538c6aa.4fbdc"
]
]
},
{
"id": "b101ba47.a14638",
"type": "function",
"z": "f9a462b5.c005b",
"name": "check TRADE/TAG",
"func": "\nlet newMsg1 = {};\nnewMsg1.topic = `SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='${msg.payload}'`;\nreturn newMsg1;\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 510,
"y": 480,
"wires": [
[
"745f3dc0.62f50c"
]
]
},
{
"id": "35c0d47e.0acfa4",
"type": "function",
"z": "f9a462b5.c005b",
"name": "tagid no space ",
"func": "msg.payload = msg.payload.replace(/\\s/g, '')\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 260,
"y": 480,
"wires": [
[
"115451dd.6f8636"
]
]
},
{
"id": "6a667929.43126",
"type": "change",
"z": "f9a462b5.c005b",
"name": "",
"rules": [
{
"t": "set",
"p": "id",
"pt": "msg",
"to": "id",
"tot": "flow"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1390,
"y": 580,
"wires": [
[
"d360d82.e9204a8",
"a4cb6fc0.fed9"
]
]
},
{
"id": "f538c6aa.4fbdc",
"type": "change",
"z": "f9a462b5.c005b",
"name": "",
"rules": [
{
"t": "set",
"p": "id",
"pt": "flow",
"to": "payload",
"tot": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 380,
"y": 360,
"wires": [
[
"60631b91.6502ac"
]
]
},
{
"id": "115451dd.6f8636",
"type": "change",
"z": "f9a462b5.c005b",
"name": "",
"rules": [
{
"t": "set",
"p": "id",
"pt": "flow",
"to": "payload",
"tot": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 340,
"y": 560,
"wires": [
[
"b101ba47.a14638"
]
]
},
{
"id": "14910207.c303e6",
"type": "mqtt out",
"z": "f9a462b5.c005b",
"name": "",
"topic": "MATCH",
"qos": "1",
"retain": "true",
"broker": "2b0fd3c9.2369b4",
"x": 1220,
"y": 480,
"wires": []
},
{
"id": "fe81b0fe.8d4718",
"type": "mqtt in",
"z": "f9a462b5.c005b",
"name": "",
"topic": "FOB",
"qos": "1",
"datatype": "auto",
"broker": "2b0fd3c9.2369b4",
"x": 50,
"y": 420,
"wires": [
[
"e8af5769.005968"
]
]
},
{
"id": "67fb9f4b.0101e8",
"type": "sqlite",
"z": "f9a462b5.c005b",
"mydb": "1a9ea8ca.835eef",
"sqlquery": "msg.topic",
"sql": "",
"name": "SQLITE3",
"x": 1820,
"y": 480,
"wires": [
[]
]
},
{
"id": "ace67310.165ba",
"type": "mqtt in",
"z": "f9a462b5.c005b",
"name": "",
"topic": "TRADE",
"qos": "1",
"datatype": "auto",
"broker": "2b0fd3c9.2369b4",
"x": 50,
"y": 480,
"wires": [
[
"35c0d47e.0acfa4"
]
]
},
{
"id": "a4cb6fc0.fed9",
"type": "function",
"z": "f9a462b5.c005b",
"name": "case 1 and 2 updating seperately as per user is entering",
"func": "\nvar words=msg.id;\nvar i= words.length;\nif(i==4)\n{\nlet newMsg1 = {};\n\nnewMsg1.topic = `INSERT INTO NCS_DATABASE2(Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'TAG','DR OPEN BY TAG',' ','Shipra Nigam',' ','${msg.id}',' ',' ','DR1 ON')`;\nreturn newMsg1;\n\n}\nif(i==8)\n{\n let newMsg2 = {};\n\nnewMsg2.topic = `INSERT INTO NCS_DATABASE2 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB','DR OPEN BY FOB ',' ','Shipra Nigam','${msg.id}',' ',' ',' ','DR1 ON')`;\nreturn newMsg2;\n}",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 1710,
"y": 580,
"wires": [
[
"67fb9f4b.0101e8"
]
]
},
{
"id": "a47b126e.5647d",
"type": "mqtt out",
"z": "f9a462b5.c005b",
"name": "",
"topic": "MATCH1",
"qos": "1",
"retain": "true",
"broker": "2b0fd3c9.2369b4",
"x": 1180,
"y": 760,
"wires": []
},
{
"id": "b7254879.37a048",
"type": "function",
"z": "f9a462b5.c005b",
"name": "",
"func": "var w2=w1=msg.payload\nif(w1.length==8)\n{\n var payload1=w1;\n}\nif(w2.length==4)\n{\n var payload2=w2;\n}\n\nlet newMsg1 = {};\nnewMsg1.topic =`SELECT * FROM NCS_DATABASE3 WHERE FOB_TAG_ID ='${payload1}' AND TRADE_ID='${payload2}'`;\nreturn newMsg1;\n\n\n\n ",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 640,
"y": 100,
"wires": [
[]
]
},
{
"id": "b60381fa.5e23a",
"type": "sqlite",
"z": "f9a462b5.c005b",
"mydb": "1a9ea8ca.835eef",
"sqlquery": "msg.topic",
"sql": "SELECT * FROM NCS WHERE Person_ID='{{payload}}';",
"name": "SQLITE3",
"x": 680,
"y": 740,
"wires": [
[
"52046306.85bdd4"
]
]
},
{
"id": "52046306.85bdd4",
"type": "switch",
"z": "f9a462b5.c005b",
"name": "",
"property": "payload",
"propertyType": "msg",
"rules": [
{
"t": "empty"
},
{
"t": "nempty"
}
],
"checkall": "true",
"repair": false,
"outputs": 2,
"x": 850,
"y": 740,
"wires": [
[
"9c6edd56.22dfa"
],
[
"d1460df6.b40a38"
]
]
},
{
"id": "9c6edd56.22dfa",
"type": "change",
"z": "f9a462b5.c005b",
"name": "",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "CR_FAIL",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1020,
"y": 700,
"wires": [
[
"a47b126e.5647d"
]
]
},
{
"id": "d1460df6.b40a38",
"type": "change",
"z": "f9a462b5.c005b",
"name": "",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "CR_SUCCESS",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1020,
"y": 840,
"wires": [
[
"a47b126e.5647d",
"3493a35f.17437c"
]
]
},
{
"id": "464910ad.ac7d58",
"type": "mqtt in",
"z": "f9a462b5.c005b",
"name": "",
"topic": "TRADE1",
"qos": "1",
"datatype": "auto",
"broker": "2b0fd3c9.2369b4",
"x": 80,
"y": 780,
"wires": [
[
"adf4af98.a343a"
]
]
},
{
"id": "adf4af98.a343a",
"type": "function",
"z": "f9a462b5.c005b",
"name": "tagid no space ",
"func": "msg.payload = msg.payload.replace(/\\s/g, '')\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 290,
"y": 741.6666259765625,
"wires": [
[
"622f5991.8274d"
]
]
},
{
"id": "622f5991.8274d",
"type": "change",
"z": "f9a462b5.c005b",
"name": "",
"rules": [
{
"t": "set",
"p": "id",
"pt": "flow",
"to": "payload",
"tot": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 440,
"y": 800,
"wires": [
[
"5d69680e.c8ef78"
]
]
},
{
"id": "5d69680e.c8ef78",
"type": "function",
"z": "f9a462b5.c005b",
"name": "check TRADE (only used for case 3)",
"func": "\nlet newMsg1 = {};\nnewMsg1.topic = `SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='${msg.payload}'`;\nreturn newMsg1;\n",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 550,
"y": 680,
"wires": [
[
"b60381fa.5e23a"
]
]
},
{
"id": "3493a35f.17437c",
"type": "change",
"z": "f9a462b5.c005b",
"name": "",
"rules": [
{
"t": "set",
"p": "id1",
"pt": "msg",
"to": "id",
"tot": "flow"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 1310,
"y": 800,
"wires": [
[
"d360d82.e9204a8"
]
]
},
{
"id": "df455d83.5cdd8",
"type": "function",
"z": "f9a462b5.c005b",
"name": "case 1 and 3 updating together",
"func": "\nvar words=msg.id;\nvar words1=msg.id1;\nvar i= words.length;\nvar j= words1.length;\n/*if(i==8)\n\n{\n let newMsg1={};\n newMsg1.topic = `INSERT INTO NCS_DATABASE4 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB','DR OPEN BY FOB ',' ','Shipra Nigam','${msg.id}',' ',' ',' ','DR1 ON')`;\nreturn newMsg1;\n \n}*/\nif(i==8&&j==4)\n {\n \nlet newMsg2 = {};\n\nnewMsg2.topic = `INSERT INTO NCS_DATABASE2 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB & TAG','DR OPEN BY FOB & TAG',' ','Shipra Nigam',' ','${msg.id}','${msg.id1} ',' ','High Risk DR ON')`;\n\nreturn newMsg2;\n}\n\n\n\n\n\n/*if(i==8)\n{\n let newMsg2 = {};\n\nnewMsg2.topic = `INSERT INTO NCS_DATABASE4 (Date_and_Time_India,Date_and_Time_UK ,Event_Type ,Event_Description ,Person_ID,Person_Name_Description ,Fob_Tag_ID ,Trade_ID,Door_ID ,Door_Description ,Status)\nVALUES ((strftime('%Y-%m-%d %H:%M:%S','now','localtime')),(strftime('%Y-%m-%d %H:%M:%S','now','localtime','-4 hour','-30 minutes')),'FOB','DR OPEN BY FOB ',' ','Shipra Nigam','${msg.id}',' ',' ',' ','DR1 ON')`;\nreturn newMsg2;\n}*/",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"x": 1590,
"y": 640,
"wires": [
[
"ced9a8ce.b102b8"
]
]
},
{
"id": "d360d82.e9204a8",
"type": "join",
"z": "f9a462b5.c005b",
"name": "",
"mode": "custom",
"build": "string",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": false,
"timeout": "",
"count": "2",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 1370,
"y": 640,
"wires": [
[
"df455d83.5cdd8"
]
]
},
{
"id": "ced9a8ce.b102b8",
"type": "sqlite",
"z": "f9a462b5.c005b",
"mydb": "1a9ea8ca.835eef",
"sqlquery": "msg.topic",
"sql": "SELECT * FROM NCS WHERE Person_ID='{{payload}}';",
"name": "SQLITE3",
"x": 1760,
"y": 720,
"wires": [
[]
]
},
{
"id": "1178ec9e.b2bd1b",
"type": "comment",
"z": "f9a462b5.c005b",
"name": "this flow check case 1 and 2",
"info": "",
"x": 1480,
"y": 540,
"wires": []
},
{
"id": "eea4837a.cacfe8",
"type": "comment",
"z": "f9a462b5.c005b",
"name": "this flow check case 1 and 3",
"info": "",
"x": 1460,
"y": 680,
"wires": []
},
{
"id": "1a9ea8ca.835eef",
"type": "sqlitedb",
"z": "",
"db": "/home/shipra/Documents/NCS_Projects/IOT/NCS_API/New DB Testing/NCS_new.db",
"mode": "RWC"
},
{
"id": "2b0fd3c9.2369b4",
"type": "mqtt-broker",
"z": "",
"name": "",
"broker": "tcp://mqtt.eclipse.org",
"port": "1883",
"clientid": "NCS_Client_034",
"usetls": false,
"compatmode": true,
"keepalive": "60",
"cleansession": true,
"birthTopic": "",
"birthQos": "0",
"birthPayload": "",
"closeTopic": "",
"closeQos": "0",
"closePayload": "",
"willTopic": "",
"willQos": "0",
"willPayload": ""
}
]
this is more simpler look please check and suggest
TRADE 1 i have taken for my c code because for case 3 when i was doing in code and inspite of TRADE1 if i was taking TRADE pub node it is not showing my required output thats y i have taken TRADE1 node
I'm sorry but that is not very clear. What is TRADE1? How does it relate to FOB/TAG? I don't care what you call it, but we need to know what it means in terms of the logic you want.
Can you answer my previous question?
ok So,TRADE 1 is another MQTT PUB node which is doing same thing as TAG PUB node but due to some coding issue i have taken TRADE1 (i.e in code(for case 3 i am saying we are checking first the o/p of fob if is returns me a CR_SUCCESS then i am catching this string and publishing my Tag from user on TRADE 1 ).if gets successful my DB should get updated only by one row (i.e only with TAG+FOB) .(but it is printing the FOB flow too again)
here TAG is nothing but TRADE or TRADE1
yes you said correct .this is the issue
if somehow in node red we are able to catch that for case 3 only node red should update Both FOB and TAG in my DB .It has to avoid that FOB Flow for case 3
then maybe issue get solved.So i dont know how can i do this
I hope i am able to convey my message .please suggest some method ho wcan i do this.Thank you
So in your flow, a message will arrive on the FOB MQTT node.
You do not know at that point in time if it is a FOB message by itself (case 1), or if a TAG message will arrive 'soon' which will make it case 3.
That is the problem you need to solve - right?
In case 3, do you know how long (in seconds) the two messages might arrive? Do they always get published within 1 second of each other? or could there be a longer delay?
The only way for you to tell the difference between case 1,2 and 3 is that when you receive a message, wait for a period of time for the "other" message to arrive. If it arrives in that timeframe, it is case 3. If it doesn't arrive then it is case 1 (or 2, depending on what arrive originally).
For the moment, forget about the database and the output end of the flow. Just focus on the start of the flow where the messages arrive.
You can use the Join node to help you do this. Wire both the FOB MQTT node and the TAG MQTT nodes to a Join node. Configure the Join node in manual mode, to create a key/value object like this:
This will create a single message with a payload containing both the FOB and TAG payloads, as long as both arrive within 1 second of each other. If just one message arrives and then nothing for over 1 second, it will send on a message containing just the one message.
The payload of the message sent by the Join node will be different to what the rest of your flows expect - wire the Join node to a Debug node to help you see what it contains (and to test that I have got the logic right here...)
The goal is get a single message that contains either just the FOB message (case1), just the TAG message (case2) or both the TAG and FOB messages (case3).
You can then use a Switch node to branch the flow by checking the contents of msg.payload to see which case it is.
And then you can rebuild the database logic you want for each of those cases.
That's about all the time I can afford to spend on this. I've tried to explain in enough detail to help you get started in the right direction.
If you aren't sure about any part of it, do ask, but please try to be specific about what part you don't understand. If something isn't working, be clear about what exactly you have tried and what about it is not working.
Remember the Debug node is your friend - it will help you understand what you have in the messages at any point in the flow.
yes 1 or 2 sec most probably it is showing
i will try this and update you in a proper manner .Thanks a lot Sir.
did you mean TRADE1?
I still don't understand what TRADE1 is and how it relates to use case 1, 2 or 3.
But hopefully my explanation is sufficient to get you started with the logic you need.
ohk Sir I am trying this
Acc to this I have done
9/16/2020, 8:54:34 PMnode: 1111
SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814' : msg : Object
object
topic: "SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814'"
_msgid: "4000318d.f9cb"
payload: object
SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436': "CR_SUCCESS"
SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814': "CR_SUCCESS"
9/16/2020, 9:48:34 PMnode: 0000
SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814' : msg : Object
object
topic: "SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814'"
_msgid: "d26d5caa.5933c"
payload: object
SELECT Fob_Tag_ID FROM NCS_DATABASE1 WHERE Fob_Tag_ID='04222436': array[1]
0: object
Fob_Tag_ID: "04222436"
SELECT Trade_ID FROM NCS_DATABASE1 WHERE Trade_ID ='2814': array[1]
0: object
Trade_ID: "2814"
I am getting both 2 id from FOB as well as TRADE1 in an array object.Now how to use switch node?
