Update query in sqlite3 with fob id coming from gateway code

General
61

Let me restate my question. please write down the steps you think need to happen to acheive your goal. For example

  1. a person flashed his fob an a scanner
  2. the scanner checks the database to see if the person is authorized
  • if the person is authorized go to (3)
  • if the person is not authorized sound the alarm
  1. activate the relay to open the door and update the database showing the door is open.
  2. wait 4 seconds and update the database showing the door is closed.

One other question - are you trying to create a log of when the door is opened/closed and who entered?

62 
Ok Sir.

Steps for my aim are:
1. A person will scan his person id/fob id/tag id.
2. if person data exist in DB Door automatically open and immediately DB Status column also gets updated with Door ON.
3.if not in DB then go to step 1.
4.After scanning successfully with the exist Person ID/Fob ID/tag id/ DR open and after 4 sec Door automatically off and immediately DB Status changed/uploaded to DR OFF.
5.go to step 1 if this time person id/fob id/tag id is again some person scans then again Door should open and DB gets updated and again after 4 sec Door close and DB gets updated with DR OFF.
6. this process should repeat either with same person id/fob id/tag id or with different one.
63

Do you mean "is again same person" (not some person)"
If so then what happens if it is a different person?

64

Sir,If same person is again scanning again DR should open but if different person is scanning DR2 gets Open and DB also gets updated with DR2ON with respective person id row. again goto step2

65

So if the same person scans again you want to open the door but not update the database?

66

I want to update DR ON in DB again because it has been closed by 4 sec so some one wants to go again he scans and door again open and database also gets updated.

67

Sorry that doesn't make sense. What is the difference between the same person scanning again and a new person scanning?

68

Suppose lets take a scenerio.
one person with id 00422 enters the admin office right.he scanned and door get opened and simultaneously DB also get updated.
As the door closing time is there (door closes automatically after 4or 5 sec depending upon the maker of door config it will vary).
when person gets into admin room he want to come out so he has to scan again with person id right?(if not then suggest me).So that DR ON updation should be there in DB also. & again door closes after 4 /5sec and DB again gets updated.

69

I have to repeat the same question because you have not answered it. What is the difference you want between the same person scanning and a different person scanning?

70

Or to put it another way, what is wrong with this?

  1. A person will scan his person id/fob id/tag id.
  2. if person not in DB then goto step 1
  3. If in DB, Door opens
  4. Update database for that person with Door On
  5. 4 seconds later Update database for that person with Door Off.
  6. Goto step 1.
71

by this logic you will have a row in the database for each person allowed into the room showing only the last time the door closed, except when the same person is the next one to open the door in which case you ignore that .

Is that right?

NOTE: it looks like you have a bug in your flow. At one point you send the msg to two function nodes . One of the function nodes checks for a id and the other does not. In that case, one person is going to pass both tests sending two msgs to the attached sqlite node

also - what is the purpose of this? To know the last time the door closed behind someone?

72

Sir then how can i made a flow so that i can fulfill my aim?

73

It is still not clear what your aim is. Is it as I suggested in my last reply?

Also, as @zenofmud says it seems odd that you just want to update a single record in the database rather than add a new record for each event so that you have a full record of who went in and out.

74

Did you find what I think is a bug and if it is a bug did you fix it?

75

Hello Sir, i want to update time as well in my DB as soon as someone enter with valid card

76

Did you find and fix the bug?

77

Sir I fount that DR OFF should not be in the flow because we never trace the person when the person has opened the door,only closes it shows so i though that was the error.

78

Please provide the updated flow so I can see if you fixed what I think is a bug

79 
[
    {
        "id": "a0a22bbe.4dd42",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": ""
    },
    {
        "id": "4189be61.b54dd",
        "type": "sqlite",
        "z": "a0a22bbe.4dd42",
        "mydb": "ebf455b5.0e09e",
        "sqlquery": "msg.topic",
        "sql": "SELECT * FROM NCS WHERE Person_ID='{{payload}}';",
        "name": "SQLITE3",
        "x": 760,
        "y": 220,
        "wires": [
            [
                "81f4171d.a3c57"
            ]
        ]
    },
    {
        "id": "81f4171d.a3c57",
        "type": "switch",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "property": "payload",
        "propertyType": "msg",
        "rules": [
            {
                "t": "empty"
            },
            {
                "t": "nempty"
            }
        ],
        "checkall": "true",
        "repair": false,
        "outputs": 2,
        "x": 850,
        "y": 300,
        "wires": [
            [
                "b6e09667.cfd1f8"
            ],
            [
                "47c73500.14fd6c"
            ]
        ]
    },
    {
        "id": "b6e09667.cfd1f8",
        "type": "change",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "CR_FAIL",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1040,
        "y": 220,
        "wires": [
            [
                "b4f536b1.835ee",
                "c857818d.fa3bd"
            ]
        ]
    },
    {
        "id": "47c73500.14fd6c",
        "type": "change",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "CR_SUCCESS",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1040,
        "y": 360,
        "wires": [
            [
                "b4c18fb9.b990e8",
                "c857818d.fa3bd",
                "743e1ca5.336ef4"
            ]
        ]
    },
    {
        "id": "4629413e.f8d6f",
        "type": "function",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "func": "let newMsg = {};\nnewMsg.topic = `SELECT Person_ID FROM NCS_DATABASE WHERE Person_ID='${msg.payload}'`;\nreturn newMsg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 520,
        "y": 220,
        "wires": [
            [
                "4189be61.b54dd"
            ]
        ]
    },
    {
        "id": "950a518e.f68788",
        "type": "function",
        "z": "a0a22bbe.4dd42",
        "name": "id no space ",
        "func": "msg.payload = msg.payload.replace(/\\s/g, '')\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 290,
        "y": 220,
        "wires": [
            [
                "7cc57b61.7cddbc"
            ]
        ]
    },
    {
        "id": "e1c813c6.492a18",
        "type": "function",
        "z": "a0a22bbe.4dd42",
        "name": "function2",
        "func": "let newMsg1 = {};\nnewMsg1.topic = `SELECT Fob_Tag_ID  FROM NCS_DATABASE WHERE Fob_Tag_ID ='${msg.payload}'`;\nreturn newMsg1;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 520,
        "y": 280,
        "wires": [
            [
                "4189be61.b54dd"
            ]
        ]
    },
    {
        "id": "42ce4ee7.c335d8",
        "type": "function",
        "z": "a0a22bbe.4dd42",
        "name": "tagid no space ",
        "func": "msg.payload = msg.payload.replace(/\\s/g, '')\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 300,
        "y": 280,
        "wires": [
            [
                "ce2e3f7f.05cd6"
            ]
        ]
    },
    {
        "id": "8b7358f2.2eb348",
        "type": "mqtt in",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "topic": "FOB_and_TAG",
        "qos": "1",
        "datatype": "buffer",
        "broker": "2b0fd3c9.2369b4",
        "x": 120,
        "y": 360,
        "wires": [
            []
        ]
    },
    {
        "id": "b4c18fb9.b990e8",
        "type": "change",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "id",
                "pt": "msg",
                "to": "id",
                "tot": "flow"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1330,
        "y": 360,
        "wires": [
            [
                "cf6f5371.cdfa7"
            ]
        ]
    },
    {
        "id": "2cd6eb05.9d3e94",
        "type": "sqlite",
        "z": "a0a22bbe.4dd42",
        "mydb": "ebf455b5.0e09e",
        "sqlquery": "msg.topic",
        "sql": "UPDATE NCS_DATABASE\nSET Status='{{payload}}'\nWHERE Person_ID='{{id}}';",
        "name": "SQLITE3",
        "x": 1920,
        "y": 360,
        "wires": [
            [
                "b6956c2d.14843"
            ]
        ]
    },
    {
        "id": "7cc57b61.7cddbc",
        "type": "change",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "id",
                "pt": "flow",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 420,
        "y": 160,
        "wires": [
            [
                "4629413e.f8d6f"
            ]
        ]
    },
    {
        "id": "ce2e3f7f.05cd6",
        "type": "change",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "id",
                "pt": "flow",
                "to": "payload",
                "tot": "msg"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 420,
        "y": 340,
        "wires": [
            [
                "e1c813c6.492a18"
            ]
        ]
    },
    {
        "id": "56217bc.1faf604",
        "type": "debug",
        "z": "a0a22bbe.4dd42",
        "name": "NCS_debug1",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1780,
        "y": 280,
        "wires": []
    },
    {
        "id": "c0ab02b9.8d1e1",
        "type": "debug",
        "z": "a0a22bbe.4dd42",
        "name": "NCS_debug11",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 2300,
        "y": 620,
        "wires": []
    },
    {
        "id": "b6956c2d.14843",
        "type": "debug",
        "z": "a0a22bbe.4dd42",
        "name": "DR1OFF debug",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 2000,
        "y": 460,
        "wires": []
    },
    {
        "id": "b4f536b1.835ee",
        "type": "debug",
        "z": "a0a22bbe.4dd42",
        "name": " debug",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1470,
        "y": 220,
        "wires": []
    },
    {
        "id": "743e1ca5.336ef4",
        "type": "debug",
        "z": "a0a22bbe.4dd42",
        "name": " debug2",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1500,
        "y": 260,
        "wires": []
    },
    {
        "id": "cf6f5371.cdfa7",
        "type": "function",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "func": "\nlet newMsg1 = {};\nnewMsg1.topic = `UPDATE NCS_DATABASE SET Status='DR1 ON' WHERE Person_ID='${msg.id}'`;\nreturn newMsg1;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "x": 1580,
        "y": 360,
        "wires": [
            [
                "2cd6eb05.9d3e94",
                "56217bc.1faf604"
            ]
        ]
    },
    {
        "id": "c857818d.fa3bd",
        "type": "mqtt out",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "topic": "MATCH",
        "qos": "1",
        "retain": "true",
        "broker": "2b0fd3c9.2369b4",
        "x": 1260,
        "y": 280,
        "wires": []
    },
    {
        "id": "af79011e.e6c558",
        "type": "mqtt in",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "topic": "FOB",
        "qos": "1",
        "datatype": "auto",
        "broker": "2b0fd3c9.2369b4",
        "x": 90,
        "y": 220,
        "wires": [
            [
                "950a518e.f68788"
            ]
        ]
    },
    {
        "id": "1df40781.f708b8",
        "type": "mqtt in",
        "z": "a0a22bbe.4dd42",
        "name": "",
        "topic": "TRADE",
        "qos": "1",
        "datatype": "auto",
        "broker": "2b0fd3c9.2369b4",
        "x": 90,
        "y": 280,
        "wires": [
            [
                "42ce4ee7.c335d8"
            ]
        ]
    },
    {
        "id": "ebf455b5.0e09e",
        "type": "sqlitedb",
        "z": "",
        "db": "/home/shipra/Documents/NCS_Projects/IOT/NCS_API/NCS.db",
        "mode": "RWC"
    },
    {
        "id": "2b0fd3c9.2369b4",
        "type": "mqtt-broker",
        "z": "",
        "name": "",
        "broker": "tcp://mqtt.eclipse.org",
        "port": "1883",
        "clientid": "NCS_Client_03",
        "usetls": false,
        "compatmode": true,
        "keepalive": "60",
        "cleansession": false,
        "birthTopic": "",
        "birthQos": "0",
        "birthPayload": "",
        "closeTopic": "",
        "closeQos": "0",
        "closePayload": "",
        "willTopic": "",
        "willQos": "0",
        "willPayload": ""
    }
]
80

Does the database have column(s) defined to hold the data/time? If so, do you know how to insert them in the database?