Join based on identifier

General
1

Hi!

I'm getting several payloads from a sql query in an array.
Example:

[{"COL_PRIMARY_KEY":7,"COL_CALL":"ABC123","COL_QSL_VIA":"","COL_TIME_ON":"2022-08-23T08:47:15.000Z","COL_MODE":"FT8","COL_BAND":"20m","COL_RST_SENT":"-16","COL_QSL_SENT":"N"},{"COL_PRIMARY_KEY":29,"COL_CALL":"XYZ321","COL_QSL_VIA":"","COL_TIME_ON":"2022-09-05T17:06:45.000Z","COL_MODE":"FT8","COL_BAND":"20m","COL_RST_SENT":"+08","COL_QSL_SENT":"N"},{"COL_PRIMARY_KEY":41,"COL_CALL":"XYZ321","COL_QSL_VIA":"","COL_TIME_ON":"2022-09-09T18:49:00.000Z","COL_MODE":"FT8","COL_BAND":"20m","COL_RST_SENT":"+02","COL_QSL_SENT":"N"}]

After that, I'm splitting them into separate payloads.

I'd now like to join those payloads where COL_CALL is the same.

Since the join node expects a specific number of messages to join, I can not set this as the number of messages with the same identifier in COL_CALL varies per identifier.

How would I accomplish something like this?
In the provided example, I would like to have one payload for COL_CALL ABC123 and an array of two payloads for XYZ321.

After that, I need to merge all payloads in each column, separated by a linebreak, while I don't know how many payloads are in each array. Would this be accomplishable using a change node?

Thanks!

2

You can join automaticaly and use "recreate message sequence" in the switch node, or you can use JSONata in a change node to group by.
here are two example in one flow

[{"id":"1ff80151.13ad57","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"COL_PRIMARY_KEY\":7,\"COL_CALL\":\"ABC123\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-08-23T08:47:15.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"20m\",\"COL_RST_SENT\":\"-16\",\"COL_QSL_SENT\":\"N\"},{\"COL_PRIMARY_KEY\":29,\"COL_CALL\":\"XYZ321\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-09-05T17:06:45.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"20m\",\"COL_RST_SENT\":\"+08\",\"COL_QSL_SENT\":\"N\"},{\"COL_PRIMARY_KEY\":41,\"COL_CALL\":\"XYZ321\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-09-09T18:49:00.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"20m\",\"COL_RST_SENT\":\"+02\",\"COL_QSL_SENT\":\"N\"}]","payloadType":"json","x":100,"y":2880,"wires":[["cd8c306b.a0226","9d165754.b3876"]]},{"id":"cd8c306b.a0226","type":"split","z":"bf9e1e33.030598","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":230,"y":2880,"wires":[["373d674e.714448"]]},{"id":"9d165754.b3876","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.${COL_CALL: [$]}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":2960,"wires":[["4d8071a2.6cc4a"]]},{"id":"373d674e.714448","type":"switch","z":"bf9e1e33.030598","name":"","property":"payload.COL_CALL","propertyType":"msg","rules":[{"t":"eq","v":"ABC123","vt":"str"},{"t":"eq","v":"XYZ321","vt":"str"}],"checkall":"true","repair":true,"outputs":2,"x":400,"y":2880,"wires":[["efd5051.d3f2078"],["ad93447.c4914b8"]]},{"id":"4d8071a2.6cc4a","type":"debug","z":"bf9e1e33.030598","name":"group by jsonata","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":630,"y":2960,"wires":[]},{"id":"efd5051.d3f2078","type":"join","z":"bf9e1e33.030598","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":530,"y":2840,"wires":[["affc9039.630868"]]},{"id":"ad93447.c4914b8","type":"join","z":"bf9e1e33.030598","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":530,"y":2900,"wires":[["affc9039.630868"]]},{"id":"affc9039.630868","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":2860,"wires":[]}]
3

Hi,

Could you write the SQL query to include the additional data and to segregate it on the COL_CALL field?

Cheers,

Paul

1 Like
4

Hi,
the second part using jsonata works for merging based on COL_CALL.
The first one is not suitable in my case as I don't know which and how many different values are in COL_CALL each time this flow runs.

How would I then merge the values in each object of an array together, all with a line break in between?
Values using jsonata is for example payload.ABC123[0].COL_PRIMARY_KEY.
Can I use some variable in a change node for that?
Result should be something like
payload.COL_PRIMARY_KEY = 29\n41
\n or

Thanks

5

Might be an option but I guess my SQL skills are not sufficient for something like this.
But I'll have a look into it.

Thanks

6

The expression would be

{
"COL_PRIMARY_KEY" :$join($$.payload.COL_PRIMARY_KEY.$string($), "\n")
}

or if you want them grouped

$$.payload.${
   COL_CALL: $join([$.COL_PRIMARY_KEY.$string($)],"\n")
}
7

Thanks but where am I supposed to put this? Is this jsonata format?

I tried it in the initial change node as well as in one behind the first one.
All variations give me empty results.

Thanks

8

Yes they are JSONata expressions
If you paste them in the change node of my original example.
e.g.

[{"id":"1ff80151.13ad57","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"COL_PRIMARY_KEY\":7,\"COL_CALL\":\"ABC123\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-08-23T08:47:15.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"20m\",\"COL_RST_SENT\":\"-16\",\"COL_QSL_SENT\":\"N\"},{\"COL_PRIMARY_KEY\":29,\"COL_CALL\":\"XYZ321\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-09-05T17:06:45.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"20m\",\"COL_RST_SENT\":\"+08\",\"COL_QSL_SENT\":\"N\"},{\"COL_PRIMARY_KEY\":41,\"COL_CALL\":\"XYZ321\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-09-09T18:49:00.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"20m\",\"COL_RST_SENT\":\"+02\",\"COL_QSL_SENT\":\"N\"}]","payloadType":"json","x":150,"y":2960,"wires":[["9d165754.b3876"]]},{"id":"9d165754.b3876","type":"change","z":"bf9e1e33.030598","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.${\t   COL_CALL: $join([$.COL_PRIMARY_KEY.$string($)],\"\\n\")\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":360,"y":3000,"wires":[["4d8071a2.6cc4a"]]},{"id":"4d8071a2.6cc4a","type":"debug","z":"bf9e1e33.030598","name":"group by jsonata","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":600,"y":3000,"wires":[]}]

Output

{
   "ABC123":"7",
   "XYZ321":"29\n41"
}
9

Unfortunately this gives me separate payloads per and only each "attribute" holds the information combined for each COL_CALL (don't know how I should explain it better).

Maybe I describe the usecase:
I am printing so called QSL cards. These are signal reports for amateur radio.
Each radio operator has a call sign which is called "COL_CALL" in the db.
If I had several calls with one radio operator, I will have multiple rows for him in the the db/logbook.

I now want to combine each row for one COL_CALL in one QSL card.
Therefore I want to have one payload per COL_CALL, combining each payload received from the SQL query, where each is separated with a line break (to have one line per contact in the printed table).

The current flow is as follows, but it only is capable of creating one pdf per row, not merged together using COL_CALL as an identifier:

[
    {
        "id": "b80b61d89ced5bf4",
        "type": "inject",
        "z": "9a9055680f904d80",
        "name": "inject demo",
        "props": [
            {
                "p": "partner",
                "v": "ABC123",
                "vt": "str"
            },
            {
                "p": "filename",
                "v": "/media/qsl.pdf",
                "vt": "str"
            },
            {
                "p": "manager",
                "v": "CBA321",
                "vt": "str"
            },
            {
                "p": "COL_TIME_OFF",
                "v": "2022-09-05 20:33:00",
                "vt": "str"
            },
            {
                "p": "COL_MODE",
                "v": "FT8",
                "vt": "str"
            },
            {
                "p": "COL_BAND",
                "v": "20m",
                "vt": "str"
            },
            {
                "p": "COL_RST_SENT",
                "v": "-16",
                "vt": "str"
            },
            {
                "p": "COL_QSL_SENT",
                "v": "R",
                "vt": "str"
            },
            {
                "p": "COL_QSL_SENT",
                "v": "R",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "x": 390,
        "y": 480,
        "wires": [
            [
                "d3a42c6fb6abe1dc"
            ]
        ]
    },
    {
        "id": "1f5a7fe3013edd67",
        "type": "mysql",
        "z": "9a9055680f904d80",
        "mydb": "67a5a532e5304528",
        "name": "",
        "x": 730,
        "y": 160,
        "wires": [
            [
                "9f0f9ab18c1b3975",
                "0c360a6d178ffda4"
            ]
        ]
    },
    {
        "id": "0340d4c330c4e0f6",
        "type": "function",
        "z": "9a9055680f904d80",
        "name": "Get QSOs",
        "func": "msg.payload = \"\"\nmsg.topic = 'SELECT COL_PRIMARY_KEY,COL_CALL,COL_QSL_VIA,COL_TIME_ON,COL_MODE,COL_BAND,COL_RST_SENT,COL_QSL_SENT,COL_QSL_SENT FROM TABLE_HRD_CONTACTS_V01 WHERE COL_QSL_SENT=\"R\" ';\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 530,
        "y": 160,
        "wires": [
            [
                "1f5a7fe3013edd67"
            ]
        ]
    },
    {
        "id": "8b3132695fdda741",
        "type": "inject",
        "z": "9a9055680f904d80",
        "name": "Trigger QSL card generation",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "filename",
                "v": "/media/qsl.pdf",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 300,
        "y": 160,
        "wires": [
            [
                "e60da31cd797efdf"
            ]
        ]
    },
    {
        "id": "ea741ffad455ff94",
        "type": "change",
        "z": "9a9055680f904d80",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "COL_CALL",
                "pt": "msg",
                "to": "payload.COL_CALL",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "COL_QSL_VIA",
                "pt": "msg",
                "to": "payload.COL_QSL_VIA",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "COL_MODE",
                "pt": "msg",
                "to": "payload.COL_MODE",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "COL_BAND",
                "pt": "msg",
                "to": "payload.COL_BAND",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "COL_RST_SENT",
                "pt": "msg",
                "to": "payload.COL_RST_SENT",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "COL_QSL_SENT",
                "pt": "msg",
                "to": "payload.COL_QSL_SENT",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "filename",
                "pt": "msg",
                "to": "\"/media/qsl/\" & $.payload.COL_PRIMARY_KEY & \".pdf\"",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 400,
        "y": 300,
        "wires": [
            [
                "a7bc6ca94c8649cb"
            ]
        ]
    },
    {
        "id": "a7bc6ca94c8649cb",
        "type": "moment",
        "z": "9a9055680f904d80",
        "name": "get year",
        "topic": "",
        "input": "COL_TIME_ON_formatted",
        "inputType": "msg",
        "inTz": "ETC/UTC",
        "adjAmount": 0,
        "adjType": "days",
        "adjDir": "add",
        "format": "YYYY",
        "locale": "C",
        "output": "year",
        "outputType": "msg",
        "outTz": "ETC/UTC",
        "x": 640,
        "y": 300,
        "wires": [
            [
                "6eb1f5ba2fddb727"
            ]
        ]
    },
    {
        "id": "6eb1f5ba2fddb727",
        "type": "moment",
        "z": "9a9055680f904d80",
        "name": "get month",
        "topic": "",
        "input": "COL_TIME_ON_formatted",
        "inputType": "msg",
        "inTz": "ETC/UTC",
        "adjAmount": 0,
        "adjType": "days",
        "adjDir": "add",
        "format": "MM",
        "locale": "C",
        "output": "month",
        "outputType": "msg",
        "outTz": "ETC/UTC",
        "x": 640,
        "y": 340,
        "wires": [
            [
                "7adeee6b4f9eebf3"
            ]
        ]
    },
    {
        "id": "7adeee6b4f9eebf3",
        "type": "moment",
        "z": "9a9055680f904d80",
        "name": "get day",
        "topic": "",
        "input": "COL_TIME_ON_formatted",
        "inputType": "msg",
        "inTz": "ETC/UTC",
        "adjAmount": 0,
        "adjType": "days",
        "adjDir": "add",
        "format": "DD",
        "locale": "C",
        "output": "day",
        "outputType": "msg",
        "outTz": "ETC/UTC",
        "x": 640,
        "y": 380,
        "wires": [
            [
                "58994e7e0483c7ac"
            ]
        ]
    },
    {
        "id": "58994e7e0483c7ac",
        "type": "moment",
        "z": "9a9055680f904d80",
        "name": "get time",
        "topic": "",
        "input": "COL_TIME_ON_formatted",
        "inputType": "msg",
        "inTz": "ETC/UTC",
        "adjAmount": 0,
        "adjType": "days",
        "adjDir": "add",
        "format": "HH:mm",
        "locale": "C",
        "output": "time",
        "outputType": "msg",
        "outTz": "ETC/UTC",
        "x": 640,
        "y": 420,
        "wires": [
            [
                "d3a42c6fb6abe1dc"
            ]
        ]
    },
    {
        "id": "6c1cd0a9d0a25243",
        "type": "string",
        "z": "9a9055680f904d80",
        "name": "",
        "methods": [
            {
                "name": "toString",
                "params": []
            },
            {
                "name": "replaceAll",
                "params": [
                    {
                        "type": "str",
                        "value": " GMT+0200 (Central European Summer Time)"
                    },
                    {
                        "type": "str",
                        "value": " UTC"
                    }
                ]
            }
        ],
        "prop": "payload.COL_TIME_ON",
        "propout": "COL_TIME_ON_formatted",
        "object": "msg",
        "objectout": "msg",
        "x": 370,
        "y": 240,
        "wires": [
            [
                "ea741ffad455ff94"
            ]
        ]
    },
    {
        "id": "9f0f9ab18c1b3975",
        "type": "split",
        "z": "9a9055680f904d80",
        "name": "",
        "splt": "\\n",
        "spltType": "str",
        "arraySplt": 1,
        "arraySpltType": "len",
        "stream": false,
        "addname": "",
        "x": 890,
        "y": 180,
        "wires": [
            [
                "6c1cd0a9d0a25243"
            ]
        ]
    },
    {
        "id": "834be93ce354c306",
        "type": "change",
        "z": "9a9055680f904d80",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "pageOrientation",
                "pt": "msg",
                "to": "landscape",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1090,
        "y": 480,
        "wires": [
            [
                "11a2106c4a5c8d06"
            ]
        ]
    },
    {
        "id": "0c360a6d178ffda4",
        "type": "debug",
        "z": "9a9055680f904d80",
        "name": "debug 47",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 980,
        "y": 120,
        "wires": []
    },
    {
        "id": "e60da31cd797efdf",
        "type": "function",
        "z": "9a9055680f904d80",
        "name": "Get QSOs",
        "func": "msg.payload = \"\"\nmsg.topic = 'SELECT COL_PRIMARY_KEY,COL_CALL,COL_QSL_VIA,COL_TIME_ON,COL_MODE,COL_BAND,COL_RST_SENT,COL_QSL_SENT,COL_QSL_SENT FROM TABLE_HRD_CONTACTS_V01 WHERE COL_CALL=\"DK1PU\" ';\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 530,
        "y": 120,
        "wires": [
            [
                "1f5a7fe3013edd67"
            ]
        ]
    },
    {
        "id": "d3a42c6fb6abe1dc",
        "type": "template",
        "z": "9a9055680f904d80",
        "name": "",
        "field": "payload",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "<!doctype html>\n<html>\n<head>\n</head>\n<body>\n    <div class=\"sql\">SELECT *<br>FROM logbook<br>WHERE callsign='OE3BIO' AND partner='{{COL_CALL}}'<br>ORDER BY date DESC </div>\n    <div class=\"address\">To radio: {{COL_CALL}}\n        <br>\n        {{#COL_QSL_VIA}}\n        <b>via {{COL_QSL_VIA}}</b>\n        {{/COL_QSL_VIA}}\n    </div>\n    <div class=\"qso\" style=\"font-size:13px; text-align:center;\">\n    <table style=\"border-collapse:collapse; border:1px solid;\">\n        <tr style=\"font-weight:bold;\">\n            <th width=\"50px\">YEAR</th>\n            <th width=\"50px\">MONTH</th>\n            <th width=\"50px\">DAY</th>\n            <th width=\"80px\">TIME (UTC)</th>\n            <th width=\"50px\">BAND</th>\n            <th width=\"50px\">MODE</th>\n            <th width=\"50px\">RST</th>\n        </tr>\n        <tr style=\"padding:5px\">\n            <td>{{year}}</td>\n            <td>{{month}}</td>\n            <td>{{day}}</td>\n            <td>{{time}}</td>\n            <td>{{COL_BAND}}</td>\n            <td>{{COL_MODE}}</td>\n            <td>{{COL_RST_SENT}}</td>\n        </tr>\n    </table>\n    </div>\n</body>\n</html>",
        "output": "str",
        "x": 880,
        "y": 480,
        "wires": [
            [
                "834be93ce354c306"
            ]
        ]
    },
    {
        "id": "11a2106c4a5c8d06",
        "type": "function",
        "z": "9a9055680f904d80",
        "name": "HTML to docDef",
        "func": "const { JSDOM } = jsdom;\nconst { window } = new JSDOM(\"\");\n\nconst html = htmlToPdfmake(msg.payload, { window: window, tableAutoSize:true });\n\nconst docDefinition = {\n    // a string or { width: number, height: number }\npageSize: {\n    width: 396.85,\n    height: 255.118\n  },\n\n//pageSize: msg.pageSize || 'A4',\n\n    // by default we use portrait, you can change it to landscape if you wish\n    pageOrientation: msg.pageOrientation || 'landscape',\n    pageMargins: [ 20, 0, 0, 0 ],\n\n    // [left, top, right, bottom] or [horizontal, vertical] or just a number for equal margins\n    //pageMargins: [40, 60, 40, 60],\n\n    content: [\n        html\n    ],\n    styles:{\n        sql:{\n            //marginLeft: 14.1732,\n            marginTop: 40,\n            fontSize: 9,\n            marginBottom: 25\n        },\n        address:{\n            marginLeft: 270,\n            marginTop: -80,\n            fontSize: 10.5,\n            bold: true,\n            marginBottom: 40\n        },\n        //qso:{\n            //alignment: 'center'\n            //width: 100,\n            //width: 200,\n            //marginTop: 56.6929,\n            //paddingLeft: 14.1732,\n            //fontSize: 13\n        //}\n}};\n\n\nmsg.payload = docDefinition\n\nreturn msg",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [
            {
                "var": "htmlToPdfmake",
                "module": "html-to-pdfmake"
            },
            {
                "var": "jsdom",
                "module": "jsdom"
            }
        ],
        "x": 1340,
        "y": 600,
        "wires": [
            [
                "075ae101fed922d1"
            ]
        ]
    },
    {
        "id": "075ae101fed922d1",
        "type": "pdfmake",
        "z": "9a9055680f904d80",
        "name": "",
        "outputType": "Buffer",
        "inputProperty": "payload",
        "options": "{}",
        "outputProperty": "payload",
        "x": 1340,
        "y": 660,
        "wires": [
            [
                "4e692a74d7a1230c"
            ]
        ]
    },
    {
        "id": "4e692a74d7a1230c",
        "type": "file",
        "z": "9a9055680f904d80",
        "name": "",
        "filename": "filename",
        "filenameType": "msg",
        "appendNewline": false,
        "createDir": true,
        "overwriteFile": "true",
        "encoding": "none",
        "x": 1360,
        "y": 720,
        "wires": [
            [
                "21f501c403a9a4e2"
            ]
        ]
    },
    {
        "id": "67a5a532e5304528",
        "type": "MySQLdatabase",
        "name": "cloudlog db",
        "host": "1.1.1.1",
        "port": "3306",
        "db": "cloudlog",
        "tz": "",
        "charset": "UTF8"
    }
]
10

Please supply a sample sql return, and a json representation of how you wish it to look at the end. With no ambiguity.

11

Hi,

pls excuse my late reply.

Here is a return I get from SQL

[{"COL_PRIMARY_KEY":3,"COL_CALL":"DQ5R","COL_QSL_VIA":"","COL_TIME_ON":"2022-08-23T07:58:00.000Z","COL_MODE":"FT8","COL_BAND":"20m","COL_RST_SENT":"-14","COL_QSL_SENT":"N"},{"COL_PRIMARY_KEY":118,"COL_CALL":"DQ5R","COL_QSL_VIA":"","COL_TIME_ON":"2022-09-19T17:55:22.000Z","COL_MODE":"MFSK","COL_BAND":"40m","COL_RST_SENT":"+09","COL_QSL_SENT":"N"},{"COL_PRIMARY_KEY":98,"COL_CALL":"EA1IOK","COL_QSL_VIA":"","COL_TIME_ON":"2022-09-16T07:18:45.000Z","COL_MODE":"FT8","COL_BAND":"20m","COL_RST_SENT":"-15","COL_QSL_SENT":"N"},{"COL_PRIMARY_KEY":143,"COL_CALL":"EA1IOK","COL_QSL_VIA":"","COL_TIME_ON":"2022-09-21T16:30:30.000Z","COL_MODE":"FT8","COL_BAND":"40m","COL_RST_SENT":"-20","COL_QSL_SENT":"N"}]

This is the current flow:

[
    {
        "id": "b80b61d89ced5bf4",
        "type": "inject",
        "z": "9a9055680f904d80",
        "name": "inject demo",
        "props": [
            {
                "p": "partner",
                "v": "ABC123",
                "vt": "str"
            },
            {
                "p": "filename",
                "v": "/media/qsl.pdf",
                "vt": "str"
            },
            {
                "p": "manager",
                "v": "CBA321",
                "vt": "str"
            },
            {
                "p": "COL_TIME_OFF",
                "v": "2022-09-05 20:33:00",
                "vt": "str"
            },
            {
                "p": "COL_MODE",
                "v": "FT8",
                "vt": "str"
            },
            {
                "p": "COL_BAND",
                "v": "20m",
                "vt": "str"
            },
            {
                "p": "COL_RST_SENT",
                "v": "-16",
                "vt": "str"
            },
            {
                "p": "COL_QSL_SENT",
                "v": "R",
                "vt": "str"
            },
            {
                "p": "COL_QSL_SENT",
                "v": "R",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "x": 390,
        "y": 480,
        "wires": [
            [
                "d3a42c6fb6abe1dc"
            ]
        ]
    },
    {
        "id": "1f5a7fe3013edd67",
        "type": "mysql",
        "z": "9a9055680f904d80",
        "mydb": "67a5a532e5304528",
        "name": "",
        "x": 730,
        "y": 160,
        "wires": [
            [
                "9f0f9ab18c1b3975",
                "0c360a6d178ffda4"
            ]
        ]
    },
    {
        "id": "0340d4c330c4e0f6",
        "type": "function",
        "z": "9a9055680f904d80",
        "name": "Get QSOs",
        "func": "msg.payload = \"\"\nmsg.topic = 'SELECT COL_PRIMARY_KEY,COL_CALL,COL_QSL_VIA,COL_TIME_ON,COL_MODE,COL_BAND,COL_RST_SENT,COL_QSL_SENT,COL_QSL_SENT FROM TABLE_HRD_CONTACTS_V01 WHERE COL_QSL_SENT=\"R\" ';\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 530,
        "y": 160,
        "wires": [
            [
                "1f5a7fe3013edd67"
            ]
        ]
    },
    {
        "id": "8b3132695fdda741",
        "type": "inject",
        "z": "9a9055680f904d80",
        "name": "Trigger QSL card generation",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "filename",
                "v": "/media/qsl.pdf",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 300,
        "y": 160,
        "wires": [
            [
                "c078d03cf87ad413"
            ]
        ]
    },
    {
        "id": "ea741ffad455ff94",
        "type": "change",
        "z": "9a9055680f904d80",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "COL_CALL",
                "pt": "msg",
                "to": "payload.COL_CALL",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "COL_QSL_VIA",
                "pt": "msg",
                "to": "payload.COL_QSL_VIA",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "COL_MODE",
                "pt": "msg",
                "to": "payload.COL_MODE",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "COL_BAND",
                "pt": "msg",
                "to": "payload.COL_BAND",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "COL_RST_SENT",
                "pt": "msg",
                "to": "payload.COL_RST_SENT",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "COL_QSL_SENT",
                "pt": "msg",
                "to": "payload.COL_QSL_SENT",
                "tot": "msg"
            },
            {
                "t": "set",
                "p": "filename",
                "pt": "msg",
                "to": "\"/media/qsl/\" & $.payload.COL_PRIMARY_KEY & \".pdf\"",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 400,
        "y": 300,
        "wires": [
            [
                "a7bc6ca94c8649cb"
            ]
        ]
    },
    {
        "id": "a7bc6ca94c8649cb",
        "type": "moment",
        "z": "9a9055680f904d80",
        "name": "get year",
        "topic": "",
        "input": "COL_TIME_ON_formatted",
        "inputType": "msg",
        "inTz": "ETC/UTC",
        "adjAmount": 0,
        "adjType": "days",
        "adjDir": "add",
        "format": "YYYY",
        "locale": "C",
        "output": "year",
        "outputType": "msg",
        "outTz": "ETC/UTC",
        "x": 640,
        "y": 300,
        "wires": [
            [
                "6eb1f5ba2fddb727"
            ]
        ]
    },
    {
        "id": "6eb1f5ba2fddb727",
        "type": "moment",
        "z": "9a9055680f904d80",
        "name": "get month",
        "topic": "",
        "input": "COL_TIME_ON_formatted",
        "inputType": "msg",
        "inTz": "ETC/UTC",
        "adjAmount": 0,
        "adjType": "days",
        "adjDir": "add",
        "format": "MM",
        "locale": "C",
        "output": "month",
        "outputType": "msg",
        "outTz": "ETC/UTC",
        "x": 640,
        "y": 340,
        "wires": [
            [
                "7adeee6b4f9eebf3"
            ]
        ]
    },
    {
        "id": "7adeee6b4f9eebf3",
        "type": "moment",
        "z": "9a9055680f904d80",
        "name": "get day",
        "topic": "",
        "input": "COL_TIME_ON_formatted",
        "inputType": "msg",
        "inTz": "ETC/UTC",
        "adjAmount": 0,
        "adjType": "days",
        "adjDir": "add",
        "format": "DD",
        "locale": "C",
        "output": "day",
        "outputType": "msg",
        "outTz": "ETC/UTC",
        "x": 640,
        "y": 380,
        "wires": [
            [
                "58994e7e0483c7ac"
            ]
        ]
    },
    {
        "id": "58994e7e0483c7ac",
        "type": "moment",
        "z": "9a9055680f904d80",
        "name": "get time",
        "topic": "",
        "input": "COL_TIME_ON_formatted",
        "inputType": "msg",
        "inTz": "ETC/UTC",
        "adjAmount": 0,
        "adjType": "days",
        "adjDir": "add",
        "format": "HH:mm",
        "locale": "C",
        "output": "time",
        "outputType": "msg",
        "outTz": "ETC/UTC",
        "x": 640,
        "y": 420,
        "wires": [
            [
                "d3a42c6fb6abe1dc"
            ]
        ]
    },
    {
        "id": "6c1cd0a9d0a25243",
        "type": "string",
        "z": "9a9055680f904d80",
        "name": "",
        "methods": [
            {
                "name": "toString",
                "params": []
            },
            {
                "name": "replaceAll",
                "params": [
                    {
                        "type": "str",
                        "value": " GMT+0200 (Central European Summer Time)"
                    },
                    {
                        "type": "str",
                        "value": " UTC"
                    }
                ]
            }
        ],
        "prop": "payload.COL_TIME_ON",
        "propout": "COL_TIME_ON_formatted",
        "object": "msg",
        "objectout": "msg",
        "x": 370,
        "y": 240,
        "wires": [
            [
                "ea741ffad455ff94"
            ]
        ]
    },
    {
        "id": "9f0f9ab18c1b3975",
        "type": "split",
        "z": "9a9055680f904d80",
        "name": "",
        "splt": "\\n",
        "spltType": "str",
        "arraySplt": 1,
        "arraySpltType": "len",
        "stream": false,
        "addname": "",
        "x": 890,
        "y": 180,
        "wires": [
            [
                "6c1cd0a9d0a25243"
            ]
        ]
    },
    {
        "id": "21f501c403a9a4e2",
        "type": "function",
        "z": "9a9055680f904d80",
        "name": "Set as printed",
        "func": "msg.payload = \"\"\nmsg.topic = 'UPDATE TABLE_HRD_CONTACTS_V01 SET COL_QSL_SENT=\"Y\" WHERE COL_QSL_SENT=\"R\" AND COL_CALL=\"ABC123\" ';\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 540,
        "y": 560,
        "wires": [
            [
                "eec75b1ab0d3768d"
            ]
        ]
    },
    {
        "id": "6ba62ed1dafe991f",
        "type": "inject",
        "z": "9a9055680f904d80",
        "name": "Set as printed",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 330,
        "y": 560,
        "wires": [
            [
                "21f501c403a9a4e2"
            ]
        ]
    },
    {
        "id": "eec75b1ab0d3768d",
        "type": "mysql",
        "z": "9a9055680f904d80",
        "mydb": "67a5a532e5304528",
        "name": "",
        "x": 750,
        "y": 560,
        "wires": [
            [
                "fc26b8cd5f353240"
            ]
        ]
    },
    {
        "id": "d26d781bc63bce9e",
        "type": "function",
        "z": "9a9055680f904d80",
        "name": "merge",
        "func": "const merger = new pdfMergerJs()\n\n//the array of files from fs-ops\nconst files = msg.files\n\n//check there are 2 or more\nif (!files || !files.length || files.length < 2) {\n    node.warn(\"Not enough files to merge. there should be 2 or more PDFs\");\n    return\n}\n\n// loop the file names from fs-ops-directory\nfor (let index = 0; index < files.length; index++) {\n    const file = files[index] // this file name\n    const fullPath = path.join(msg.path, file) // make full path\n    await merger.add(fullPath) // add the full path to pdf\n}\n\n// join and return a buffer\nmsg.payload = await merger.saveAsBuffer() \n\n// set msg.filename for write node\nmsg.filename = msg.output // msg.result is configured in the inject node\n\n// pass the msg to next node\nnode.send(msg);\n\n\n\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [
            {
                "var": "pdfMergerJs",
                "module": "pdf-merger-js"
            },
            {
                "var": "pdfLib",
                "module": "pdf-lib"
            },
            {
                "var": "path",
                "module": "path"
            }
        ],
        "x": 670,
        "y": 680,
        "wires": [
            [
                "673562b571393984"
            ]
        ]
    },
    {
        "id": "d9dfbfbc08f1a1b4",
        "type": "inject",
        "z": "9a9055680f904d80",
        "name": "Trigger Merge",
        "props": [
            {
                "p": "path",
                "v": "/media/qsl/",
                "vt": "str"
            },
            {
                "p": "output",
                "v": "/media/qsl/merged.pdf",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "x": 310,
        "y": 680,
        "wires": [
            [
                "096a8fd47d68be6c"
            ]
        ]
    },
    {
        "id": "096a8fd47d68be6c",
        "type": "fs-ops-dir",
        "z": "9a9055680f904d80",
        "name": "",
        "path": "path",
        "pathType": "msg",
        "filter": "*.pdf",
        "filterType": "str",
        "dir": "files",
        "dirType": "msg",
        "x": 500,
        "y": 680,
        "wires": [
            [
                "d26d781bc63bce9e"
            ]
        ]
    },
    {
        "id": "673562b571393984",
        "type": "file",
        "z": "9a9055680f904d80",
        "name": "",
        "filename": "filename",
        "filenameType": "msg",
        "appendNewline": true,
        "createDir": false,
        "overwriteFile": "true",
        "encoding": "none",
        "x": 820,
        "y": 680,
        "wires": [
            [
                "72f29966c9f46e2b"
            ]
        ]
    },
    {
        "id": "fc26b8cd5f353240",
        "type": "delay",
        "z": "9a9055680f904d80",
        "name": "",
        "pauseType": "delay",
        "timeout": "10",
        "timeoutUnits": "seconds",
        "rate": "1",
        "nbRateUnits": "1",
        "rateUnits": "second",
        "randomFirst": "1",
        "randomLast": "5",
        "randomUnits": "seconds",
        "drop": false,
        "allowrate": false,
        "outputs": 1,
        "x": 920,
        "y": 560,
        "wires": [
            [
                "7d1b6eb1264b4473"
            ]
        ]
    },
    {
        "id": "8ce5fc1e1e2f9c56",
        "type": "fs-ops-move",
        "z": "9a9055680f904d80",
        "name": "Move merged file",
        "sourcePath": "/media/qsl",
        "sourcePathType": "str",
        "sourceFilename": "merged.pdf",
        "sourceFilenameType": "str",
        "destPath": "/media/qsl/merged",
        "destPathType": "str",
        "destFilename": "merged_rename",
        "destFilenameType": "msg",
        "link": false,
        "x": 310,
        "y": 800,
        "wires": [
            [
                "43e520e078ca8603"
            ]
        ]
    },
    {
        "id": "43e520e078ca8603",
        "type": "fs-file-lister",
        "z": "9a9055680f904d80",
        "name": "",
        "start": "/media/qsl",
        "pattern": "*.pdf",
        "folders": "*",
        "hidden": true,
        "lstype": "files",
        "path": false,
        "single": false,
        "depth": 0,
        "stat": false,
        "showWarnings": true,
        "x": 500,
        "y": 800,
        "wires": [
            [
                "5b5da73b1e2fa85d"
            ]
        ]
    },
    {
        "id": "5b5da73b1e2fa85d",
        "type": "change",
        "z": "9a9055680f904d80",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "delete",
                "pt": "msg",
                "to": "payload",
                "tot": "msg",
                "dc": true
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 680,
        "y": 800,
        "wires": [
            [
                "cebb8dc9aa2bf6c7"
            ]
        ]
    },
    {
        "id": "cebb8dc9aa2bf6c7",
        "type": "fs-ops-delete",
        "z": "9a9055680f904d80",
        "name": "Delete source files",
        "path": "/media/qsl",
        "pathType": "str",
        "filename": "delete",
        "filenameType": "msg",
        "x": 890,
        "y": 800,
        "wires": [
            []
        ]
    },
    {
        "id": "7d1b6eb1264b4473",
        "type": "change",
        "z": "9a9055680f904d80",
        "name": "Set paths",
        "rules": [
            {
                "t": "set",
                "p": "path",
                "pt": "msg",
                "to": "/media/qsl/",
                "tot": "str"
            },
            {
                "t": "set",
                "p": "output",
                "pt": "msg",
                "to": "/media/qsl/merged.pdf",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 300,
        "y": 620,
        "wires": [
            [
                "096a8fd47d68be6c"
            ]
        ]
    },
    {
        "id": "72f29966c9f46e2b",
        "type": "change",
        "z": "9a9055680f904d80",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "merged_rename",
                "pt": "msg",
                "to": "\"merged_\" & $now() & \".pdf\"",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 330,
        "y": 740,
        "wires": [
            [
                "8ce5fc1e1e2f9c56"
            ]
        ]
    },
    {
        "id": "834be93ce354c306",
        "type": "change",
        "z": "9a9055680f904d80",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "pageOrientation",
                "pt": "msg",
                "to": "landscape",
                "tot": "str"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1090,
        "y": 480,
        "wires": [
            [
                "11a2106c4a5c8d06"
            ]
        ]
    },
    {
        "id": "075ae101fed922d1",
        "type": "pdfmake",
        "z": "9a9055680f904d80",
        "name": "",
        "outputType": "Buffer",
        "inputProperty": "payload",
        "options": "{}",
        "outputProperty": "payload",
        "x": 1340,
        "y": 660,
        "wires": [
            [
                "4e692a74d7a1230c"
            ]
        ]
    },
    {
        "id": "11a2106c4a5c8d06",
        "type": "function",
        "z": "9a9055680f904d80",
        "name": "HTML to docDef",
        "func": "const { JSDOM } = jsdom;\nconst { window } = new JSDOM(\"\");\n\nconst html = htmlToPdfmake(msg.payload, { window: window, tableAutoSize:true });\n\nconst docDefinition = {\n    // a string or { width: number, height: number }\npageSize: {\n    width: 396.85,\n    height: 255.118\n  },\n\n//pageSize: msg.pageSize || 'A4',\n\n    // by default we use portrait, you can change it to landscape if you wish\n    pageOrientation: msg.pageOrientation || 'landscape',\n    pageMargins: [ 20, 0, 0, 0 ],\n\n    // [left, top, right, bottom] or [horizontal, vertical] or just a number for equal margins\n    //pageMargins: [40, 60, 40, 60],\n\n    content: [\n        html\n    ],\n    styles:{\n        sql:{\n            //marginLeft: 14.1732,\n            marginTop: 40,\n            fontSize: 9,\n            marginBottom: 25\n        },\n        address:{\n            marginLeft: 270,\n            marginTop: -80,\n            fontSize: 10.5,\n            bold: true,\n            marginBottom: 40\n        },\n        //qso:{\n            //alignment: 'center'\n            //width: 100,\n            //width: 200,\n            //marginTop: 56.6929,\n            //paddingLeft: 14.1732,\n            //fontSize: 13\n        //}\n}};\n\n\nmsg.payload = docDefinition\n\nreturn msg",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [
            {
                "var": "htmlToPdfmake",
                "module": "html-to-pdfmake"
            },
            {
                "var": "jsdom",
                "module": "jsdom"
            }
        ],
        "x": 1340,
        "y": 600,
        "wires": [
            [
                "075ae101fed922d1"
            ]
        ]
    },
    {
        "id": "4e692a74d7a1230c",
        "type": "file",
        "z": "9a9055680f904d80",
        "name": "",
        "filename": "filename",
        "filenameType": "msg",
        "appendNewline": false,
        "createDir": true,
        "overwriteFile": "true",
        "encoding": "none",
        "x": 1360,
        "y": 720,
        "wires": [
            [
                "21f501c403a9a4e2"
            ]
        ]
    },
    {
        "id": "0c360a6d178ffda4",
        "type": "debug",
        "z": "9a9055680f904d80",
        "name": "debug 47",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 980,
        "y": 120,
        "wires": []
    },
    {
        "id": "d3a42c6fb6abe1dc",
        "type": "template",
        "z": "9a9055680f904d80",
        "name": "",
        "field": "payload",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "<!doctype html>\n<html>\n<head>\n</head>\n<body>\n    <div class=\"sql\">SELECT *<br>FROM logbook<br>WHERE callsign='OE3BIO' AND partner='{{COL_CALL}}'<br>ORDER BY date DESC </div>\n    <div class=\"address\">To radio: {{COL_CALL}}\n        <br>\n        {{#COL_QSL_VIA}}\n        <b>via {{COL_QSL_VIA}}</b>\n        {{/COL_QSL_VIA}}\n    </div>\n    <div class=\"qso\" style=\"font-size:13px; text-align:center;\">\n    <table style=\"border-collapse:collapse; border:1px solid;\">\n        <tr style=\"font-weight:bold;\">\n            <th width=\"50px\">YEAR</th>\n            <th width=\"50px\">MONTH</th>\n            <th width=\"50px\">DAY</th>\n            <th width=\"80px\">TIME (UTC)</th>\n            <th width=\"50px\">BAND</th>\n            <th width=\"50px\">MODE</th>\n            <th width=\"50px\">RST</th>\n        </tr>\n        <tr style=\"padding:5px\">\n            <td>{{year}}</td>\n            <td>{{month}}</td>\n            <td>{{day}}</td>\n            <td>{{time}}</td>\n            <td>{{COL_BAND}}</td>\n            <td>{{COL_MODE}}</td>\n            <td>{{COL_RST_SENT}}</td>\n        </tr>\n    </table>\n    </div>\n</body>\n</html>",
        "output": "str",
        "x": 880,
        "y": 480,
        "wires": [
            [
                "834be93ce354c306"
            ]
        ]
    },
    {
        "id": "e5a4e769b3d3f057",
        "type": "template",
        "z": "9a9055680f904d80",
        "name": "template some css",
        "field": "payload",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "<!doctype html>\n<html>\n<head>\n<style>\n    body {\n        font-family: Arial, Helvetica, sans-serif;\n    }\n    #sql {\n        /*margin-left: 5mm;\n        margin-top: 15mm;*/\n        margin: 15mm 0mm 0mm 5mm;\n        font-size: 12px;\n    }\n    #address {\n        /*margin-left: 95mm;\n        margin-top: -10mm;*/\n        margin: -10mm 0mm 0mm 95mm;\n        font-size: 14px;\n        font-weight: bold;\n    }\n    table {\n        width: 120mm;\n        /*margin-top: 20mm;\n        margin-left: 5mm;*/\n        margin: 20mm 0mm 0mm 5mm;\n    }\n    table, th, td {\n        font-size: 13px;\n        border: 1px solid;\n        text-align: center;\n        /*border-collapse: collapse;*/\n    }\n    th, td {\n        /*padding: 5px;*/\n    }\n*/</style>\n</head>\n<body>\n    <div id=\"sql\">SELECT *<br>FROM logbook<br>WHERE callsign='OE3BIO' AND partner='{{COL_CALL}}'<br>ORDER BY date DESC </div>\n    <div id=\"address\">To radio: {{COL_CALL}}\n        <br>\n        {{#COL_QSL_VIA}}\n        <b>via {{COL_QSL_VIA}}</b>\n        {{/COL_QSL_VIA}}\n    </div>\n    <table>\n        <tr>\n            <th>YEAR</th>\n            <th>MONTH</th>\n            <th>DAY</th>\n            <th>TIME (UTC)</th>\n            <th>BAND</th>\n            <th>MODE</th>\n            <th>RST</th>\n        </tr>\n        <tr>\n            <td>{{year}}</td>\n            <td>{{month}}</td>\n            <td>{{day}}</td>\n            <td>{{time}}</td>\n            <td>{{COL_BAND}}</td>\n            <td>{{COL_MODE}}</td>\n            <td>{{COL_RST_SENT}}</td>\n        </tr>\n    </table>\n</body>\n</html>",
        "output": "str",
        "x": 1070,
        "y": 380,
        "wires": [
            []
        ]
    },
    {
        "id": "92df3a8281aab41e",
        "type": "template",
        "z": "9a9055680f904d80",
        "name": "template some table css",
        "field": "payload",
        "fieldType": "msg",
        "format": "handlebars",
        "syntax": "mustache",
        "template": "<!doctype html>\n<html>\n<head>\n</head>\n<body>\n    <div class=\"sql\">SELECT *<br>FROM logbook<br>WHERE callsign='OE3BIO' AND partner='{{COL_CALL}}'<br>ORDER BY date DESC </div>\n    <div class=\"address\">To radio: {{COL_CALL}}\n        <br>\n        {{#COL_QSL_VIA}}\n        <b>via {{COL_QSL_VIA}}</b>\n        {{/COL_QSL_VIA}}\n    </div>\n    <table style=\"width:340.157; margin-top:56.6929; margin-left:14.1732; font-size:13px; border:1px; border-style:solid; text-align:center; border-collpase:collapse;\">\n        <tr>\n            <th>YEAR</th>\n            <th>MONTH</th>\n            <th>DAY</th>\n            <th>TIME (UTC)</th>\n            <th>BAND</th>\n            <th>MODE</th>\n            <th>RST</th>\n        </tr>\n        <tr>\n            <td>{{year}}</td>\n            <td>{{month}}</td>\n            <td>{{day}}</td>\n            <td>{{time}}</td>\n            <td>{{COL_BAND}}</td>\n            <td>{{COL_MODE}}</td>\n            <td>{{COL_RST_SENT}}</td>\n        </tr>\n    </table>\n</body>\n</html>",
        "output": "str",
        "x": 1090,
        "y": 420,
        "wires": [
            []
        ]
    },
    {
        "id": "c078d03cf87ad413",
        "type": "function",
        "z": "9a9055680f904d80",
        "name": "Get QSOs",
        "func": "msg.payload = \"\"\nmsg.topic = 'SELECT COL_PRIMARY_KEY,COL_CALL,COL_QSL_VIA,COL_TIME_ON,COL_MODE,COL_BAND,COL_RST_SENT,COL_QSL_SENT,COL_QSL_SENT FROM TABLE_HRD_CONTACTS_V01 WHERE COL_CALL=\"EA1IOK\" OR COL_CALL=\"DQ5R\" ';\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 530,
        "y": 120,
        "wires": [
            [
                "1f5a7fe3013edd67"
            ]
        ]
    },
    {
        "id": "67a5a532e5304528",
        "type": "MySQLdatabase",
        "name": "cloudlog db",
        "host": "0.0.0.0",
        "port": "3306",
        "db": "cloudlog",
        "tz": "",
        "charset": "UTF8"
    }
]

And here is a sample output:
merged_2022-09-23T08_37_59.895Z.pdf (61.8 KB)

Instead of having one pdf (or after they got merged one page) per QSO (=one individual payload from the SQL), I want to have one pdf per callsign (=COL_CALL in SQL).
This pdf/page should then hold all contacts from this specific callsign below each other.
Example:

image
image1220Ă—765 32.2 KB

becomes something like this (but the second row also being inside the table):
image
image1186Ă—643 36.6 KB

Hope this explains it a bit better.

Thanks!

12

I do not have the majority of your nodes install.
I can help you transform your sql object, I will require the output object, the pdf does not help.
You need to supply a sample object that is required to create your pdf. Without that I can not help.

13

Ok I hope this is understandable:

Each separate msg:

{"payload":{"COL_PRIMARY_KEY":3,"COL_CALL":"DQ5R","COL_QSL_VIA":"","COL_TIME_ON":"2022-08-23T07:58:00.000Z","COL_MODE":"FT8","COL_BAND":"20m","COL_RST_SENT":"-14","COL_QSL_SENT":"N"},"filename":"/media/qsl/3.pdf","topic":"SELECT COL_PRIMARY_KEY,COL_CALL,COL_QSL_VIA,COL_TIME_ON,COL_MODE,COL_BAND,COL_RST_SENT,COL_QSL_SENT,COL_QSL_SENT FROM TABLE_HRD_CONTACTS_V01 WHERE COL_CALL=\"EA1IOK\" OR COL_CALL=\"DQ5R\" ","parts":{"id":"dcaaf2d699202539","type":"array","count":4,"len":1,"index":0},"_msgid":"17bfe76475f62632","COL_TIME_ON_formatted":"Tue Aug 23 2022 09:58:00 UTC","COL_CALL":"DQ5R","COL_QSL_VIA":"","COL_MODE":"FT8","COL_BAND":"20m","COL_RST_SENT":"-14","COL_QSL_SENT":"N","year":"2022","settings":{"input":"Tue Aug 23 2022 09:58:00 UTC","input_format":"ddd MMM D YYYY HH:mm:ss UTC","input_tz":"ETC/UTC","output_format":"HH:mm","output_locale":"C","output_tz":"ETC/UTC"},"month":"08","day":"23","time":"09:58"}

{"payload":{"COL_PRIMARY_KEY":118,"COL_CALL":"DQ5R","COL_QSL_VIA":"","COL_TIME_ON":"2022-09-19T17:55:22.000Z","COL_MODE":"MFSK","COL_BAND":"40m","COL_RST_SENT":"+09","COL_QSL_SENT":"N"},"filename":"/media/qsl/118.pdf","topic":"SELECT COL_PRIMARY_KEY,COL_CALL,COL_QSL_VIA,COL_TIME_ON,COL_MODE,COL_BAND,COL_RST_SENT,COL_QSL_SENT,COL_QSL_SENT FROM TABLE_HRD_CONTACTS_V01 WHERE COL_CALL=\"EA1IOK\" OR COL_CALL=\"DQ5R\" ","parts":{"id":"dcaaf2d699202539","type":"array","count":4,"len":1,"index":1},"_msgid":"9db6cb2151fae5d9","COL_TIME_ON_formatted":"Mon Sep 19 2022 19:55:22 UTC","COL_CALL":"DQ5R","COL_QSL_VIA":"","COL_MODE":"MFSK","COL_BAND":"40m","COL_RST_SENT":"+09","COL_QSL_SENT":"N","year":"2022","settings":{"input":"Mon Sep 19 2022 19:55:22 UTC","input_format":"ddd MMM D YYYY H:mm:ss UTC","input_tz":"ETC/UTC","output_format":"HH:mm","output_locale":"C","output_tz":"ETC/UTC"},"month":"09","day":"19","time":"19:55"}

{"payload":{"COL_PRIMARY_KEY":98,"COL_CALL":"EA1IOK","COL_QSL_VIA":"","COL_TIME_ON":"2022-09-16T07:18:45.000Z","COL_MODE":"FT8","COL_BAND":"20m","COL_RST_SENT":"-15","COL_QSL_SENT":"N"},"filename":"/media/qsl/98.pdf","topic":"SELECT COL_PRIMARY_KEY,COL_CALL,COL_QSL_VIA,COL_TIME_ON,COL_MODE,COL_BAND,COL_RST_SENT,COL_QSL_SENT,COL_QSL_SENT FROM TABLE_HRD_CONTACTS_V01 WHERE COL_CALL=\"EA1IOK\" OR COL_CALL=\"DQ5R\" ","parts":{"id":"dcaaf2d699202539","type":"array","count":4,"len":1,"index":2},"_msgid":"c49f45335cca406a","COL_TIME_ON_formatted":"Fri Sep 16 2022 09:18:45 UTC","COL_CALL":"EA1IOK","COL_QSL_VIA":"","COL_MODE":"FT8","COL_BAND":"20m","COL_RST_SENT":"-15","COL_QSL_SENT":"N","year":"2022","settings":{"input":"Fri Sep 16 2022 09:18:45 UTC","input_format":"ddd MMM D YYYY HH:mm:ss UTC","input_tz":"ETC/UTC","output_format":"HH:mm","output_locale":"C","output_tz":"ETC/UTC"},"month":"09","day":"16","time":"09:18"}

Here, the msg with COL_Call EA1IOK would stay untouched as there is only one msg with this COL_Call.

Both messages with COL_CALL DQ5R would have to be merged as they have the same COL_CALL and not differing COL_QSL_VIA, while the following msg parts would be changed:

  • COL_MODE
  • COL_BAND
  • COL_RST_SENT
  • COL_QSL_SENT
  • year
  • month
  • day
  • time
  • payload: deleted
  • filename (can also the be set after merging, might be easier)
  • topic: deleted

The values from the first msg are merged with the second (or third, ...) one, separated with a br tag.

{"filename":"/media/qsl/3_118.pdf","parts":{"id":"dcaaf2d699202539","type":"array","count":4,"len":1,"index":0},"_msgid":"17bfe76475f62632","COL_TIME_ON_formatted":"Tue Aug 23 2022 09:58:00 UTC","COL_CALL":"DQ5R","COL_QSL_VIA":"","COL_MODE":"FT8<br>FT8","COL_BAND":"20m<br>40m","COL_RST_SENT":"-14<br>+09","COL_QSL_SENT":"N","year":"2022<br>2022","settings":{"input":"Tue Aug 23 2022 09:58:00 UTC","input_format":"ddd MMM D YYYY HH:mm:ss UTC","input_tz":"ETC/UTC","output_format":"HH:mm","output_locale":"C","output_tz":"ETC/UTC"},"month":"08<br>09","day":"23<br>19","time":"09:58<br>19:55"}

The question is if it would be easier to address the template instead of manipulating the data.
Maybe it's possible to do some for-each in the template?
EDIT: So joining those with the same COL_CALL and COL_QSL_VIA and then doing a for each in the mustache template.
EDIT2: Min. number of messages to join would need to be set, even though it differs per COL_CALL.

Thanks!

14

Ok try this, if it is not right, i really will insist on a output json example before any more attempts.

[{"id":"b80b61d89ced5bf4","type":"inject","z":"fe76f7928e585317","name":"inject demo","props":[{"p":"payload"},{"p":"test","v":"true","vt":"bool"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"COL_PRIMARY_KEY\":3,\"COL_CALL\":\"DQ5R\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-08-23T07:58:00.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"20m\",\"COL_RST_SENT\":\"-14\",\"COL_QSL_SENT\":\"N\"},{\"COL_PRIMARY_KEY\":118,\"COL_CALL\":\"DQ5R\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-09-19T17:55:22.000Z\",\"COL_MODE\":\"MFSK\",\"COL_BAND\":\"40m\",\"COL_RST_SENT\":\"+09\",\"COL_QSL_SENT\":\"N\"},{\"COL_PRIMARY_KEY\":98,\"COL_CALL\":\"EA1IOK\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-09-16T07:18:45.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"20m\",\"COL_RST_SENT\":\"-15\",\"COL_QSL_SENT\":\"N\"},{\"COL_PRIMARY_KEY\":143,\"COL_CALL\":\"EA1IOK\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-09-21T16:30:30.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"40m\",\"COL_RST_SENT\":\"-20\",\"COL_QSL_SENT\":\"N\"}]","payloadType":"json","x":440,"y":720,"wires":[["edad0d8c.84b43"]]},{"id":"edad0d8c.84b43","type":"change","z":"fe76f7928e585317","name":"group results","rules":[{"t":"set","p":"payload","pt":"msg","to":"$each($$.payload{COL_CALL: $}, function($v) {\t[$v.(\t$date_parts := $split($.COL_TIME_ON, /-|T|\\./);\t$ ~> |$|{\"year\": $date_parts[0],\t\"month\": $date_parts[1],\t\"day\": $date_parts[2],\t\"time\": $date_parts[3],\t\"COL_QSL_VIA\": ($.COL_QSL_VIA = \"\" ? \"direct\" : $.COL_QSL_VIA)}|\t)]\t})","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":780,"wires":[["b3f6435b.f70eb","c6f221fa.85a6e8"]]},{"id":"85be2136.ac3fb","type":"change","z":"fe76f7928e585317","name":"sql return","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"COL_PRIMARY_KEY\":3,\"COL_CALL\":\"DQ5R\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-08-23T07:58:00.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"20m\",\"COL_RST_SENT\":\"-14\",\"COL_QSL_SENT\":\"N\"},{\"COL_PRIMARY_KEY\":118,\"COL_CALL\":\"DQ5R\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-09-19T17:55:22.000Z\",\"COL_MODE\":\"MFSK\",\"COL_BAND\":\"40m\",\"COL_RST_SENT\":\"+09\",\"COL_QSL_SENT\":\"N\"},{\"COL_PRIMARY_KEY\":98,\"COL_CALL\":\"EA1IOK\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-09-16T07:18:45.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"20m\",\"COL_RST_SENT\":\"-15\",\"COL_QSL_SENT\":\"N\"},{\"COL_PRIMARY_KEY\":143,\"COL_CALL\":\"EA1IOK\",\"COL_QSL_VIA\":\"\",\"COL_TIME_ON\":\"2022-09-21T16:30:30.000Z\",\"COL_MODE\":\"FT8\",\"COL_BAND\":\"40m\",\"COL_RST_SENT\":\"-20\",\"COL_QSL_SENT\":\"N\"}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":840,"wires":[["edad0d8c.84b43"]]},{"id":"b3f6435b.f70eb","type":"debug","z":"fe76f7928e585317","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1230,"y":780,"wires":[]},{"id":"c6f221fa.85a6e8","type":"switch","z":"fe76f7928e585317","name":"filter out test injects","property":"test","propertyType":"msg","rules":[{"t":"true"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":870,"y":800,"wires":[[],["d3a42c6fb6abe1dc"]]},{"id":"14e10af0.e34965","type":"http in","z":"fe76f7928e585317","name":"","url":"/col","method":"get","upload":false,"swaggerDoc":"","x":330,"y":840,"wires":[["85be2136.ac3fb"]]},{"id":"d3a42c6fb6abe1dc","type":"template","z":"fe76f7928e585317","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!doctype html>\n<html>\n<head>\n</head>\n<body>\n    {{#payload}}\n    <div class=\"sql\">SELECT *<br>FROM logbook<br>WHERE callsign='OE3BIO' AND partner='{{0.COL_CALL}}'<br>ORDER BY date DESC </div>\n    <div class=\"address\">To radio: {{0.COL_CALL}}\n    </div>\n    <div class=\"qso\" style=\"font-size:13px; text-align:center;\">\n    <table style=\"border-collapse:collapse; border:1px solid;\">\n        <tr style=\"font-weight:bold;\">\n            <th width=\"50px\">YEAR</th>\n            <th width=\"50px\">MONTH</th>\n            <th width=\"50px\">DAY</th>\n            <th width=\"80px\">TIME (UTC)</th>\n            <th width=\"50px\">BAND</th>\n            <th width=\"50px\">MODE</th>\n            <th width=\"50px\">RST</th>\n            <th width=\"50px\">VIA</th>\n        </tr>\n        {{#.}}\n        <tr style=\"padding:5px\">\n            <td>{{year}}</td>\n            <td>{{month}}</td>\n            <td>{{day}}</td>\n            <td>{{time}}</td>\n            <td>{{COL_BAND}}</td>\n            <td>{{COL_MODE}}</td>\n            <td>{{COL_RST_SENT}}</td>\n            <td>{{COL_QSL_VIA}}</td>\n        {{/.}}\n        </tr>\n    </table>\n    </div>\n    {{/payload}}\n</body>\n</html>","output":"str","x":790,"y":840,"wires":[["b3f6435b.f70eb","84f5caf0.3dd6"]]},{"id":"84f5caf0.3dd6","type":"http response","z":"fe76f7928e585317","name":"","statusCode":"","headers":{},"x":990,"y":840,"wires":[]}]

you can see result at http://your_node-red-ip:1880/col

chrome_screenshot_1664270838040
chrome_screenshot_16642708380401920Ă—1629 125 KB

[edit] edited to handle single groups,, and VIA added

15

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.