Hi
I am fairly new to JS and node red. I have data from sensors that is stored in a MYSQL database.
I display a user query from the database on my dashboard and it all works when called
I now want to display the different fields (batt,temp,light etc) on a line chart with a time base as x.
The graph shows up on my dashboard with the y axis formatted to scale, but no lines or data. here is the flow.
[
{
"id": "4447f1fa.c7a27",
"type": "tab",
"label": "show stud data",
"disabled": false,
"info": ""
},
{
"id": "80a060da.8c1db",
"type": "ui_template",
"z": "4447f1fa.c7a27",
"group": "fe5b5e1b0beaa2ad",
"name": "From Database",
"order": 7,
"width": "17",
"height": "7",
"format": "<style>\ntable\n{\n background:black;\n}\n.main\n{\n\n\n height:1500px;\n}\n</style>\n<div class=\"main\">\n<table style=\"width:100%\">\n <tr>\n <th>Date</th> \n <th>Time</th>\n <th>Stud</th>\n <th>Batt</th>\n <th>Charge</th>\n <th>Signal</th>\n <th>Temperature</th>\n <th>Light</th>\n\n \n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:10000\">\n <td>{{msg.payload[$index].datum}}</td>\n <td>{{msg.payload[$index].timing}}</td>\n <td>{{msg.payload[$index].position}}</td>\n <td>{{msg.payload[$index].battery}}</td>\n <td>{{msg.payload[$index].charge}}</td>\n <td>{{msg.payload[$index].signalstrength}}</td>\n <td>{{msg.payload[$index].temperature}}</td>\n <td>{{msg.payload[$index].light}}</td>\n \n\n </tr>\n</table>\n</div>",
"storeOutMessages": true,
"fwdInMessages": true,
"resendOnRefresh": false,
"templateScope": "local",
"className": "",
"x": 960,
"y": 240,
"wires": [
[]
]
},
{
"id": "2134256f.8304f2",
"type": "function",
"z": "4447f1fa.c7a27",
"name": "",
"func": "var data = context.get('data') || { value: \"\", condition: \">=\" };\nvar topic = msg.topic;\nvar payload = msg.payload;\nif (topic == \"position\")\n data.site = msg.payload;\nif (topic == \"value\")\n data.value = msg.payload;\nif (topic == \"condition\")\n data.condition = msg.payload;\nif (topic == \"sdatum\")\n data.sdate = msg.payload;\nif (topic == \"edatum\")\n data.edate = msg.payload;\n\nvar d = new Date();\nvar now = d.getDate();\nif (data.edate == undefined || data.edate == \"\")\n edate = now;\nelse\n var edate = data.edate;\n\nif (data.sdate == undefined || data.sdate == \"\")\n var sdate = now;\nelse\n sdate = data.sdate;\ncontext.set('data', data)\nif (sdate == 0) {\n var part_msg = \"\";\n\n}\nelse {\n var part_msg = \" AND TIMESTAMP >\" + sdate + \" AND TIMESTAMP<\" + edate + \" \";\n}\nif (topic == \"submit\") {\n var query = \"\";\n if (data.value == undefined || data.value == \"\") {\n if (data.site != undefined)\n query = \"SELECT * FROM studs.stud_status WHERE position=\" + \"\\\"\" + data.site + \"\\\"\" + \"AND timestamp_ >= \" + sdate + \"\";//\" + \"\\\"\" + data.site + \"\\\" ORDER BY datum DESC\";\n }\n else if (data.value != undefined && data.value != \"\") {\n if (data.site != undefined && data.condition != undefined) {\n query = \"SELECT * FROM studs.stud_status WHERE position=\" + \"\\\"\" + data.site + \"\\\"\" + \" AND light \";\n query = query + data.condition + data.value;\n query = query + \" ORDER BY datum DESC LIMIT 100\";\n }\n }\n node.log(\"query \" + query);\n msg.topic = query;\n\n context.set('data', data)\n if (query = \"1\")\n return msg;\n}\nreturn;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 420,
"y": 240,
"wires": [
[
"2ef4c453f4827be8",
"9ade5738986f044c"
]
]
},
{
"id": "b82db343.9fb4d",
"type": "ui_dropdown",
"z": "4447f1fa.c7a27",
"d": true,
"name": "Query Type",
"label": "Query Type",
"tooltip": "",
"place": "Select option",
"group": "fe5b5e1b0beaa2ad",
"order": 1,
"width": "4",
"height": "2",
"passthru": false,
"multiple": false,
"options": [
{
"label": ">=",
"value": ">=",
"type": "str"
},
{
"label": "<=",
"value": "<=",
"type": "str"
},
{
"label": "=",
"value": "=",
"type": "str"
}
],
"payload": "",
"topic": "condition",
"topicType": "str",
"className": "",
"x": 130,
"y": 220,
"wires": [
[
"2134256f.8304f2"
]
]
},
{
"id": "280eff25.001f5",
"type": "ui_text_input",
"z": "4447f1fa.c7a27",
"name": "value",
"label": "value",
"tooltip": "",
"group": "f7a06e3b.8c35b",
"order": 2,
"width": "4",
"height": "1",
"passthru": false,
"mode": "text",
"delay": "100",
"topic": "value",
"sendOnBlur": true,
"className": "",
"topicType": "str",
"x": 130,
"y": 280,
"wires": [
[
"2134256f.8304f2"
]
]
},
{
"id": "a372c41d.5e6968",
"type": "ui_button",
"z": "4447f1fa.c7a27",
"name": "submit",
"group": "fe5b5e1b0beaa2ad",
"order": 8,
"width": "4",
"height": "1",
"passthru": false,
"label": "Submit",
"tooltip": "",
"color": "",
"bgcolor": "",
"className": "",
"icon": "",
"payload": "1",
"payloadType": "str",
"topic": "submit",
"topicType": "str",
"x": 130,
"y": 360,
"wires": [
[
"2134256f.8304f2"
]
]
},
{
"id": "6240d8.feb99f28",
"type": "ui_date_picker",
"z": "4447f1fa.c7a27",
"name": "start date",
"label": "Start date",
"group": "fe5b5e1b0beaa2ad",
"order": 3,
"width": "5",
"height": "2",
"passthru": true,
"topic": "sdatum",
"topicType": "str",
"className": "",
"x": 140,
"y": 420,
"wires": [
[
"2134256f.8304f2"
]
]
},
{
"id": "8ab8621.bcbbca",
"type": "ui_date_picker",
"z": "4447f1fa.c7a27",
"d": true,
"name": "End Date",
"label": "End Date",
"group": "fe5b5e1b0beaa2ad",
"order": 4,
"width": "4",
"height": "4",
"passthru": true,
"topic": "edatum",
"topicType": "str",
"className": "",
"x": 140,
"y": 480,
"wires": [
[
"2134256f.8304f2"
]
]
},
{
"id": "159524314a5fae1a",
"type": "ui_dropdown",
"z": "4447f1fa.c7a27",
"name": "Stud Number",
"label": "Stud Number",
"tooltip": "Select the stud number to lookup",
"place": "Select option",
"group": "fe5b5e1b0beaa2ad",
"order": 2,
"width": "5",
"height": "2",
"passthru": false,
"multiple": false,
"options": [
{
"label": "1",
"value": "1",
"type": "str"
},
{
"label": "2",
"value": "2",
"type": "str"
},
{
"label": "3",
"value": "3",
"type": "str"
},
{
"label": "4",
"value": "4",
"type": "str"
},
{
"label": "5",
"value": "5",
"type": "str"
},
{
"label": "6",
"value": "6",
"type": "str"
},
{
"label": "7",
"value": "7",
"type": "str"
},
{
"label": "8",
"value": "8",
"type": "str"
},
{
"label": "9",
"value": "9",
"type": "str"
},
{
"label": "10",
"value": "10",
"type": "str"
},
{
"label": "11",
"value": "11",
"type": "str"
},
{
"label": "12",
"value": "12",
"type": "str"
},
{
"label": "13",
"value": "13",
"type": "str"
},
{
"label": "14",
"value": "14",
"type": "str"
},
{
"label": "15",
"value": "15",
"type": "str"
},
{
"label": "16",
"value": "16",
"type": "str"
},
{
"label": "17",
"value": "17",
"type": "str"
},
{
"label": "18",
"value": "18",
"type": "str"
},
{
"label": "19",
"value": "19",
"type": "str"
},
{
"label": "20",
"value": "20",
"type": "str"
}
],
"payload": "",
"topic": "position",
"topicType": "str",
"className": "",
"x": 130,
"y": 160,
"wires": [
[
"2134256f.8304f2"
]
]
},
{
"id": "2ef4c453f4827be8",
"type": "mysql",
"z": "4447f1fa.c7a27",
"mydb": "6e9d84d500e8a508",
"name": "stud status mysql",
"x": 650,
"y": 240,
"wires": [
[
"80a060da.8c1db",
"4c98e94c06312a2e"
]
]
},
{
"id": "9ade5738986f044c",
"type": "ui_text",
"z": "4447f1fa.c7a27",
"group": "fe5b5e1b0beaa2ad",
"order": 9,
"width": "5",
"height": "4",
"name": "",
"label": "Query sent",
"format": "{{msg.topic}}",
"layout": "col-center",
"className": "",
"x": 610,
"y": 180,
"wires": []
},
{
"id": "4c98e94c06312a2e",
"type": "function",
"z": "4447f1fa.c7a27",
"name": "charge graph",
"func": "//var series = [\"Charge\"];\n//var labels = [\"Charge Values\"];\n//var data = msg.payload;\n//var thetime = Date.now()\n\nvar d = [\"[\"]\nfor (var i = 0; i < msg.payload.length; i++) {\n d += '{ \"x\":' + msg.payload[i].timestamp_ + ', \"y\":' + msg.payload[i].battery + '}';\n if (i < (msg.payload.length - 1)) {\n d += \",\"\n } else {\n d += \"]\"\n }\n}\n//var a = JSON.parse(d)\n\n\nvar f = [\"[\"]\nfor (var j = 0; j < msg.payload.length; j++) {\n f += '{ \"x\":' + msg.payload[j].timestamp_ + ', \"y\":' + msg.payload[j].charge + '}';\n if (j < (msg.payload.length - 1)) {\n f += \",\"\n } else {\n f += \"]\"\n }\n}\n//var b = JSON.parse(f)\n\nvar x = [\"[\"]\nfor (var m = 0; m < msg.payload.length; m++) {\n x += '{ \"x\":' + msg.payload[m].timestamp_ + ', \"y\":' + msg.payload[m].temperature + '}';\n if (m < (msg.payload.length - 1)) {\n x += \",\"\n } else {\n x += \"]\"\n }\n}\n//var c = JSON.parse(x)\n\nvar e = [\"[\"]\nfor (var m = 0; m < msg.payload.length; m++) {\n x += '{ \"x\":' + msg.payload[m].timestamp_ + ', \"y\":' + msg.payload[m].light + '}';\n if (m < (msg.payload.length - 1)) {\n x += \",\"\n } else {\n x += \"]\"\n }\n}\n//var c = JSON.parse(e)\n\nmsg.payload = [{\n \"series\": [\"Battery\", \"Charge\", \"Temperature\", \"Light\"],\n \"data\": [d, f, x, e],\n \"labels\": [\"Battery\", \"Charge\", \"Temperature\", \"Light\"]\n}]\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 900,
"y": 340,
"wires": [
[
"06b5672e66242ca5",
"893a2823bb2e8272"
]
]
},
{
"id": "893a2823bb2e8272",
"type": "debug",
"z": "4447f1fa.c7a27",
"name": "debug 6",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 1120,
"y": 420,
"wires": []
},
{
"id": "06b5672e66242ca5",
"type": "ui_chart",
"z": "4447f1fa.c7a27",
"name": "sql response",
"group": "fe5b5e1b0beaa2ad",
"order": 10,
"width": "9",
"height": "4",
"label": "Line Chart",
"chartType": "line",
"legend": "true",
"xformat": "auto",
"interpolate": "linear",
"nodata": "",
"dot": false,
"ymin": "",
"ymax": "",
"removeOlder": 1,
"removeOlderPoints": "",
"removeOlderUnit": "60",
"cutout": 0,
"useOneColor": false,
"useUTC": false,
"colors": [
"#1f77b4",
"#aec7e8",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"outputs": 1,
"useDifferentColor": false,
"className": "",
"x": 1140,
"y": 340,
"wires": [
[]
]
},
{
"id": "fe5b5e1b0beaa2ad",
"type": "ui_group",
"name": "Stud Reporting",
"tab": "e9bbb610152bc753",
"order": 1,
"disp": true,
"width": "35",
"collapse": true,
"className": ""
},
{
"id": "f7a06e3b.8c35b",
"type": "ui_group",
"name": "Default",
"tab": "ff84105b.764c3",
"order": 1,
"disp": true,
"width": "12",
"collapse": false
},
{
"id": "6e9d84d500e8a508",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "studs",
"tz": "",
"charset": "UTF8"
},
{
"id": "e9bbb610152bc753",
"type": "ui_tab",
"name": "Test Stud Site Monitor",
"icon": "dashboard",
"order": 1,
"disabled": false,
"hidden": false
},
{
"id": "ff84105b.764c3",
"type": "ui_tab",
"name": "SITE-RESULTS",
"icon": "dashboard"
}
]
My debug shows the output of the function, but I am still missing something.
[{"series":["Battery","Charge","Temperature","Light"],"data":["[{ \"x\":1666224007685, \"y\":0.01},{ \"x\":1666224025963, \"y\":0.01},{ \"x\":1666224034888, \"y\":0.01},{ \"x\":1666224052898, \"y\":0.00},{ \"x\":1666224069144, \"y\":0.00},{ \"x\":1666224078075, \"y\":0.00},{ \"x\":1666224095352, \"y\":0.00},{ \"x\":1666224113105, \"y\":0.01},{ \"x\":1666224122325, \"y\":0.01},{ \"x\":1666224148086, \"y\":0.01},{ \"x\":1666224157504, \"y\":0.01},{ \"x\":1666224175330, \"y\":0.01},{ \"x\":1666224192390, \"y\":0.01},{ \"x\":1666224210452, \"y\":0.01},{ \"x\":1666224219662, \"y\":0.00},{ \"x\":1666224236774, \"y\":0.00},{ \"x\":1666224254526, \"y\":0.00},{ \"x\":1666224272308, \"y\":0.00},{ \"x\":1666224281331, \"y\":0.00},{ \"x\":1666224299058, \"y\":0.00},{ \"x\":1666224316692, \"y\":0.00},{ \"x\":1666224325516, \"y\":0.00},{ \"x\":1666224343485, \"y\":0.00},{ \"x\":1666224361261, \"y\":0.00},{ \"x\":1666224370341, \"y\":0.00},{ \"x\":1666224388056, \"y\":0.00},{ \"x\":1666224405968, \"y\":0.00},{ \"x\":1666224414740, \"y\":0.00},{ \"x\":1666224432537, \"y\":0.00},{ \"x\":1666224450218, \"y\":0.00},{ \"x\":1666224468251, \"y\":0.01},{ \"x\":1666224477291, \"y\":0.01},{ \"x\":1...","[{ \"x\":1666224007685, \"y\":412},{ \"x\":1666224025963, \"y\":412},{ \"x\":1666224034888, \"y\":412},{ \"x\":1666224052898, \"y\":412},{ \"x\":1666224069144, \"y\":412},{ \"x\":1666224078075, \"y\":412},{ \"x\":1666224095352, \"y\":412},{ \"x\":1666224113105, \"y\":415},{ \"x\":1666224122325, \"y\":415},{ \"x\":1666224148086, \"y\":415},{ \"x\":1666224157504, \"y\":415},{ \"x\":1666224175330, \"y\":415},{ \"x\":1666224192390, \"y\":415},{ \"x\":1666224210452, \"y\":415},{ \"x\":1666224219662, \"y\":412},{ \"x\":1666224236774, \"y\":412},{ \"x\":1666224254526, \"y\":412},{ \"x\":1666224272308, \"y\":412},{ \"x\":1666224281331, \"y\":415},{ \"x\":1666224299058, \"y\":415},{ \"x\":1666224316692, \"y\":415},{ \"x\":1666224325516, \"y\":415},{ \"x\":1666224343485, \"y\":412},{ \"x\":1666224361261, \"y\":412},{ \"x\":1666224370341, \"y\":412},{ \"x\":1666224388056, \"y\":412},{ \"x\":1666224405968, \"y\":412},{ \"x\":1666224414740, \"y\":412},{ \"x\":1666224432537, \"y\":412},{ \"x\":1666224450218, \"y\":412},{ \"x\":1666224468251, \"y\":415},{ \"x\":1666224477291, \"y\":415},{ \"x\":1666224495556, \"y\":415},{ \"x\":166...","[{ \"x\":1666224007685, \"y\":40.0},{ \"x\":1666224025963, \"y\":40.0},{ \"x\":1666224034888, \"y\":40.0},{ \"x\":1666224052898, \"y\":40.0},{ \"x\":1666224069144, \"y\":40.0},{ \"x\":1666224078075, \"y\":40.0},{ \"x\":1666224095352, \"y\":40.0},{ \"x\":1666224113105, \"y\":40.0},{ \"x\":1666224122325, \"y\":40.0},{ \"x\":1666224148086, \"y\":40.0},{ \"x\":1666224157504, \"y\":40.0},{ \"x\":1666224175330, \"y\":40.0},{ \"x\":1666224192390, \"y\":40.0},{ \"x\":1666224210452, \"y\":40.0},{ \"x\":1666224219662, \"y\":40.0},{ \"x\":1666224236774, \"y\":40.0},{ \"x\":1666224254526, \"y\":40.0},{ \"x\":1666224272308, \"y\":40.0},{ \"x\":1666224281331, \"y\":40.0},{ \"x\":1666224299058, \"y\":40.0},{ \"x\":1666224316692, \"y\":40.0},{ \"x\":1666224325516, \"y\":40.0},{ \"x\":1666224343485, \"y\":40.0},{ \"x\":1666224361261, \"y\":40.0},{ \"x\":1666224370341, \"y\":40.0},{ \"x\":1666224388056, \"y\":40.0},{ \"x\":1666224405968, \"y\":40.0},{ \"x\":1666224414740, \"y\":40.0},{ \"x\":1666224432537, \"y\":40.0},{ \"x\":1666224450218, \"y\":40.0},{ \"x\":1666224468251, \"y\":40.0},{ \"x\":1666224477291, \"y\":40.0},{ \"x\":1...",["["]],"labels":["Battery","Charge","Temperature","Light"]}]
Any help would be appreciated.
