The dashboard displays a list of locations stored in mysql, and when you click the location number, the panels of devices in the corresponding location are displayed.
The panel displays several data extracted from the power_meter_data table.
[id | deviceID | location_number | timestamp | active_power | reactive_power | voltage_a | voltage_b | voltage_c | current_a | current_b | current_c | angle_a | angle_b | angle_c | pf_a | pf_b | pf_c | total_active_power | total_reactive_power | active_power_a | active_power_b | active_power_c | reactive_power_a | reactive_power_b | reactive_power_c]
[
{
"id": "76adf00993a13957",
"type": "function",
"z": "53c7c787661b774e",
"name": "Device검색",
"func": "var query = \"SELECT DISTINCT location from devicelist;\";\nmsg.topic = query;\nreturn msg;\n",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 270,
"y": 240,
"wires": [
[
"f84f3c234e8141ba"
]
]
},
{
"id": "f84f3c234e8141ba",
"type": "mysql",
"z": "53c7c787661b774e",
"mydb": "20fc62db2276ec96",
"name": "",
"x": 375,
"y": 240,
"wires": [
[
"3d6c586ce0c591e3",
"829c3bed65179a0d"
]
],
"l": false
},
{
"id": "3d6c586ce0c591e3",
"type": "function",
"z": "53c7c787661b774e",
"name": "List 컬럼명 수정",
"func": "let data = msg.payload;\n\n// 데이터 배열을 순회하면서 \"DeviceName (DeviceEUI)\" 형식의 문자열을 생성\nlet modifiedData = data.map(item => {\n return {\n \"DCU\": `${item[\"location\"]}`\n };\n});\n\n// 수정된 데이터를 다음 노드로 전달\nmsg.payload = modifiedData;\nreturn msg;\n",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 520,
"y": 240,
"wires": [
[
"211f717daa2911fc"
]
]
},
{
"id": "211f717daa2911fc",
"type": "ui-table",
"z": "53c7c787661b774e",
"group": "e94821fd12d57c6e",
"name": "",
"label": "text",
"order": 0,
"width": "1",
"height": "1",
"maxrows": 0,
"passthru": false,
"autocols": true,
"selectionType": "click",
"columns": [
{
"title": "",
"key": ""
}
],
"x": 670,
"y": 240,
"wires": [
[
"f7430b1ac8b75e51",
"c3a2fdd9de513061",
"6cc635e06629e764",
"35d8c270645e39f0"
]
]
},
{
"id": "35d8c270645e39f0",
"type": "link out",
"z": "53c7c787661b774e",
"name": "link out 1",
"mode": "link",
"links": [
"764917d34c19c1d3"
],
"x": 505,
"y": 320,
"wires": []
},
{
"id": "f7430b1ac8b75e51",
"type": "function",
"z": "53c7c787661b774e",
"name": "Go to Device Monitor",
"func": "let locationID = msg.payload[\"DCU\"];\nlet query;\n\nmsg.payload = {\n groups: {\n show: ['Dashboard:Monitoring','Dashboard:List'],\n }\n};\nflow.set(\"locationID\", locationID);\nquery = `\nSELECT p1.*\nFROM power_meter_data p1\nINNER JOIN (\n SELECT deviceID, MAX(timestamp) AS max_timestamp\n FROM power_meter_data\n WHERE location_number = ${locationID}\n GROUP BY deviceID\n) p2 ON p1.deviceID = p2.deviceID AND p1.timestamp = p2.max_timestamp\nORDER BY p1.timestamp DESC;\n`;\nmsg.topic = query;\nreturn msg;\n",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 720,
"y": 380,
"wires": [
[
"18acc1c4cacf416a",
"09a057f344141ae0"
]
]
},
{
"id": "18acc1c4cacf416a",
"type": "mysql",
"z": "53c7c787661b774e",
"mydb": "20fc62db2276ec96",
"name": "",
"x": 875,
"y": 380,
"wires": [
[
"ddda503bd1a480de"
]
],
"l": false
},
{
"id": "09a057f344141ae0",
"type": "ui-control",
"z": "53c7c787661b774e",
"name": "",
"ui": "e20d369367cae77f",
"events": "all",
"x": 935,
"y": 380,
"wires": [
[]
],
"l": false
},
{
"id": "ddda503bd1a480de",
"type": "function",
"z": "53c7c787661b774e",
"name": "deviceID sort",
"func": "// 데이터 정렬 함수\nconst sortByDeviceID = (data) => {\n return data.sort((a, b) => a.deviceID - b.deviceID);\n}\n\n// function 노드 코드\nmsg.payload = sortByDeviceID(msg.payload);\n\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1070,
"y": 380,
"wires": [
[
"19b3aeb828f8edb6"
]
]
},
{
"id": "20fc62db2276ec96",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "nj_modbus",
"tz": "",
"charset": "UTF8"
},
{
"id": "e94821fd12d57c6e",
"type": "ui-group",
"name": "List",
"page": "062c796f176df548",
"width": "1",
"height": "1",
"order": 1,
"showTitle": false,
"className": "",
"visible": "true",
"disabled": "false"
},
{
"id": "e20d369367cae77f",
"type": "ui-base",
"name": "My Dashboard",
"path": "/dashboard",
"includeClientData": true,
"acceptsClientConfig": [
"ui-notification",
"ui-control"
],
"showPathInSidebar": false,
"navigationStyle": "default"
},
{
"id": "062c796f176df548",
"type": "ui-page",
"name": "Dashboard",
"ui": "e20d369367cae77f",
"path": "/pageN",
"icon": "home",
"layout": "grid",
"theme": "42bb5152f64cd1e0",
"order": -1,
"className": "",
"visible": "true",
"disabled": "false"
},
{
"id": "42bb5152f64cd1e0",
"type": "ui-theme",
"name": "NJ",
"colors": {
"surface": "#ffffff",
"primary": "#393e46",
"bgPage": "#eeeeee",
"groupBg": "#ffffff",
"groupOutline": "#cccccc"
},
"sizes": {
"pagePadding": "8px",
"groupGap": "8px",
"groupBorderRadius": "4px",
"widgetGap": "4px"
}
}
]
result example
Here we are outputting the sum of active_power for the previous month, current month, yesterday, and today of deviceIDs with the selected location_nnumber.
[
{
"id": "d73551f5a7805fa1",
"type": "function",
"z": "dd9d343c38dbc39a",
"name": "function 11",
"func": "\n\n// 특정 위치의 ID를 설정합니다. msg.payload[\"DCU\"]를 locationID로 대체하세요.\nvar locationID = msg.payload[\"DCU\"];\n\n// Create the SQL query to calculate the sum of active_power for each device in the specified location\nvar query = `\nSELECT deviceID, \n SUM(active_power) AS current_month_active_power_sum\nFROM power_meter_data\nWHERE location_number=${locationID} AND timestamp >= DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) DAY) + INTERVAL 1 DAY\n AND timestamp < DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY)\nGROUP BY deviceID;\n`;\n\n// Set the query as the topic of the msg object\nmsg.topic = query;\n\n// Return the msg object\nreturn msg;\n",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 350,
"y": 100,
"wires": [
[
"df7c2902be5e9312"
]
]
},
{
"id": "2b0a15b6c14e74da",
"type": "function",
"z": "dd9d343c38dbc39a",
"name": "function 12",
"func": "var now = new Date();\nvar prevMonthFirstDay = new Date(now.getFullYear(), now.getMonth()-1, 1);\nvar prevMonthLastDay = new Date(now.getFullYear(), now.getMonth(), 0);\nvar startTime = '00:00:00';\nvar endTime = '23:59:59';\n\n// 특정 위치의 ID를 설정합니다. msg.payload[\"DCU\"]를 locationID로 대체하세요.\nvar locationID = msg.payload[\"DCU\"];\n\n// Format the start and end date/time values for the previous month\nvar prevMonthStartDateTime = new Date(now.getFullYear(), now.getMonth()-1, 1).toISOString().split('T')[0] + 'T' + startTime + ':00Z';\nvar prevMonthEndDateTime = new Date(now.getFullYear(), now.getMonth(), 1).toISOString().split('T')[0] + 'T' + endTime + ':00Z'; // 4월 1일 직전까지 포함\n\n// Create the SQL queries to calculate the sum of active_power for each device in the specified location for the previous and current months\nvar prevMonthQuery = `\nSELECT deviceID, \n SUM(active_power) AS yesterday_active_power_sum\nFROM power_meter_data\nWHERE location_number=${locationID} AND timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)\n AND timestamp < CURDATE()\nGROUP BY deviceID;\n\n`;\n\nmsg.topic = prevMonthQuery;\n\nmsg.test = prevMonthStartDateTime;\n\n// Return the msg object\nreturn msg;\n",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 350,
"y": 140,
"wires": [
[
"47051944a20b0b6c"
]
]
},
{
"id": "5427d4c8c5887918",
"type": "function",
"z": "dd9d343c38dbc39a",
"name": "function 13",
"func": "var now = new Date();\n\nvar thisMonthFirstDay = new Date(now.getFullYear(), now.getMonth(), 1);\nvar startTime = '00:00:00';\nvar endTime = '23:59:59';\n\n// 특정 위치의 ID를 설정합니다. msg.payload[\"DCU\"]를 locationID로 대체하세요.\nvar locationID = msg.payload[\"DCU\"];\n\n// Format the start and end date/time values for the current month\nvar thisMonthStartDateTime = thisMonthFirstDay.toISOString().split('T')[0] + 'T' + startTime + ':00Z';\nvar thisMonthEndDateTime = new Date(now.getFullYear(), now.getMonth() + 1, 0).toISOString().split('T')[0] + 'T' + endTime + ':00Z'; // 다음 달의 첫날 직전까지 포함\n\n// Create the SQL queries to calculate the sum of active_power for each device in the specified location for the previous and current months\n\nvar thisMonthQuery = `\nSELECT deviceID, \n SUM(active_power) AS today_active_power_sum\nFROM power_meter_data\nWHERE location_number=${locationID} AND timestamp >= CURDATE()\n AND timestamp < DATE_ADD(CURDATE(), INTERVAL 1 DAY)\nGROUP BY deviceID;\n\n`;\n\n// Set the queries as the topic of the msg object\nmsg.topic = thisMonthQuery; // 배열에 두 개의 쿼리를 포함시킴\n\n// Return the msg object\nreturn msg;\n",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 350,
"y": 180,
"wires": [
[
"f04edd13b0d4a61a"
]
]
},
{
"id": "df7c2902be5e9312",
"type": "mysql",
"z": "dd9d343c38dbc39a",
"mydb": "20fc62db2276ec96",
"name": "",
"x": 475,
"y": 100,
"wires": [
[
"056806f862d0b2d7",
"90580da4be642d9d"
]
],
"l": false
},
{
"id": "47051944a20b0b6c",
"type": "mysql",
"z": "dd9d343c38dbc39a",
"mydb": "20fc62db2276ec96",
"name": "",
"x": 475,
"y": 140,
"wires": [
[
"056806f862d0b2d7",
"90580da4be642d9d"
]
],
"l": false
},
{
"id": "f04edd13b0d4a61a",
"type": "mysql",
"z": "dd9d343c38dbc39a",
"mydb": "20fc62db2276ec96",
"name": "",
"x": 475,
"y": 180,
"wires": [
[
"056806f862d0b2d7",
"90580da4be642d9d",
"b1e897178d4cde8b"
]
],
"l": false
},
{
"id": "f8fb5794436bb03f",
"type": "function",
"z": "dd9d343c38dbc39a",
"name": "function 19",
"func": "var now = new Date();\nvar yesterday = new Date(Date.UTC(now.getFullYear(), now.getMonth(), now.getDate() - 1, 23, 59, 59, 0));\nvar startTime = '00:00:00';\nvar endTime = '23:59:59';\n\nvar locationID = msg.payload[\"DCU\"];\nflow.set(\"locationID\",locationID);\n// Format the start and end date/time values for the query\nvar startDateTime = yesterday.toISOString().split('T')[0] + 'T' + startTime + ':00Z';\nvar endDateTime = yesterday.toISOString().split('T')[0] + 'T' + endTime + ':00Z';\n\n// Create the SQL query\nvar query = `\nSELECT\n deviceID, \n SUM(active_power) AS total_active_power_last_month\nFROM\n power_meter_data\nWHERE location_number=${locationID} AND timestamp >= DATE_SUB(CURDATE(), INTERVAL 2 MONTH) AND timestamp < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)\nGROUP BY\n deviceID;\n`;\nmsg.topic = query;\nreturn msg;\n",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 350,
"y": 60,
"wires": [
[
"fb7e0c40b55c10d8"
]
]
},
{
"id": "fb7e0c40b55c10d8",
"type": "mysql",
"z": "dd9d343c38dbc39a",
"mydb": "20fc62db2276ec96",
"name": "",
"x": 475,
"y": 60,
"wires": [
[
"056806f862d0b2d7",
"90580da4be642d9d"
]
],
"l": false
},
{
"id": "764917d34c19c1d3",
"type": "link in",
"z": "dd9d343c38dbc39a",
"name": "link in 1",
"links": [
"35d8c270645e39f0"
],
"x": 155,
"y": 120,
"wires": [
[
"d73551f5a7805fa1",
"2b0a15b6c14e74da",
"5427d4c8c5887918",
"f8fb5794436bb03f"
]
]
},
{
"id": "20fc62db2276ec96",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "nj_modbus",
"tz": "",
"charset": "UTF8"
}
]
result example "today_active_power_sum" (The sum of the active_power of the previous month, the current month, and yesterday is also output like this.):
[{"deviceID":1,"today_active_power_sum":2022},{"deviceID":2,"today_active_power_sum":93},{"deviceID":3,"today_active_power_sum":1167},{"deviceID":4,"today_active_power_sum":5061},{"deviceID":5,"today_active_power_sum":954},{"deviceID":6,"today_active_power_sum":36},{"deviceID":7,"today_active_power_sum":5052},{"deviceID":8,"today_active_power_sum":1167},{"deviceID":9,"today_active_power_sum":168},{"deviceID":10,"today_active_power_sum":165}]
I wonder how to combine the five result values derived from the five function nodes into one result.
How to combine these array?
like
id: 200
deviceID: 1
location_number: "1"
timestamp: "2024-05-07T01:48:21.000Z"
active_power: 56
reactive_power: 16
voltage_a: 219.13
voltage_b: 0
voltage_c: 0
current_a: 0
current_b: 0
current_c: 0
angle_a: 0
angle_b: 0
angle_c: 0
pf_a: 0
pf_b: 0
pf_c: 0
total_active_power: 0
total_reactive_power: 0
active_power_a: 0
active_power_b: 0
active_power_c: 0
reactive_power_a: 0
reactive_power_b: 0
reactive_power_c: 0
total_active_power_last_month: 9142
current_month_active_power_sum : 8758
yesterday_active_power_sum: 1276
today_active_power_sum: 2022
