I am reading data from mysql database. and want to publish this data to a pdf report along with other design elements. I could make a complete flow, thanks to all the help from various forum topics, however, I am struggling to put large data (>500 rows) into the pdf sheet. Problem is : I don't know how to define the iterations of data rows so that it can take all the rows data automatically. My example flow is as below:
[
{
"id": "8ba699c1.0f9858",
"type": "function",
"z": "bf49b341.ae1dc",
"name": "Table format",
"func": "//datetime\nvar time = new Date().toLocaleString(\"de\");\n\n\nmsg.payload = {\n pageOrientation: 'portrait',\n\tcontent: [\n\t \t\t\t{\n\t\t\t columnGap: 20,\n columns: [\n {\n\t\t\timage: msg.pellogo,\n\t\t fit: [75, 75],\n\t\t\talignment: 'left'\n\t\t},\n\t\t\t\n\t\t\t{text: 'Daily Report _ Production', \n\t\t\t\tstyle: 'header',\n\t\talignment: 'center',\n\t\twidth: 300,\n\t\tmargin: [0,0,20,0]\n\t\t},\n\t\t\n\t\t{\n\t\t\timage: msg.stlogo,\n\t\t\tfit: [50,50],\n\t\t\talignment: 'right'\n\t\t},\n\t\t\n\t\t\t]\n\t\t\t},\n'\\n\\n',\n'Key Parameters for all Lines:',\n'\\n\\n',\n\t\t{\n\t\t\tstyle: 'DataTable',\n\t\t\talignment: 'center',\n\t\t\ttable: {\n\t\t\t headerRows: 1,\n\t\t\t fontSize: 7,\n\t\t\t\tbody: [\n\t\t\t\t\t[{text:'Line No', style: 'tableHeader'}, {text:'Plan Qty', style: 'tableHeader'}, {text:'Input Qty', style: 'tableHeader'}, {text:'Finshed Goods', style: 'tableHeader'} , {text:'Fall Off Rate', style: 'tableHeader'} , {text:'Productivity', style: 'tableHeader'}, {text:'Throughput Time', style: 'tableHeader'}, {text:'Total Tests', style: 'tableHeader'}, {text:'Pass Count', style: 'tableHeader'}, {text:'Fail Count', style: 'tableHeader'}, {text:'First Pass', style: 'tableHeader'}, {text:'R/W Pass', style: 'tableHeader'}, {text:'R/W Pending', style: 'tableHeader'}],\n[msg.payload],\n//[\"Line 1\", \"\"+a+\"\", \"\"+b+\"\", \"\"+c+\"\",\"\"+d+\"\", \"\"+e+\"\", \"\"+f+\"\", \"\"+g+\"\", \"\"+h+\"\", \"\"+i+\"\", \"\"+j+\"\", \"\"+k+\"\", \"\"+l+\"\"],\n//[\"Line 2\", \"\"+aa+\"\", \"\"+bb+\"\", \"\"+cc+\"\",\"\"+dd+\"\", \"\"+ee+\"\", \"\"+ff+\"\", \"\"+gg+\"\", \"\"+hh+\"\", \"\"+ii+\"\", \"\"+jj+\"\", \"\"+kk+\"\", \"\"+ll+\"\"],\n\n\t\t\t\t]\n\t\t\t}\n\t\t},\n'\\n\\n',\n'\\n\\n',\n'Line 5 - Daily Production Trend - Last 7 Days',\n\t\t\t{\n\t\t\timage: msg.image2,\n\t\t\tfit: [400,225],\n\t\t\talignment: 'center'\n\t\t},\n'\\n\\n',\n'Line 5 - FOR Analysis',\n\t\t\t{\n\t\t\timage: msg.image3,\n\t\t\tfit: [200,200],\n\t\t\talignment: 'center'\n\t\t},\n\t\t\t\n ],\n \tstyles: {\n\t\theader: {\n\t\t\tfontSize: 18,\n\t\t\tbold: true,\n\t\t\tmargin: [0, 0, 0, 10]\n\t\t\t\n\t\t},\n\t\tsubheader: {\n\t\t\tfontSize: 16,\n\t\t\tbold: true,\n\t\t\tmargin: [0, 10, 0, 5]\n\t\t},\n\t\ttableExample: {\n\t\t\tmargin: [0, 5, 0, 15],\n\t\t\talignment: 'right'\n\t\t},\n\t\ttableHeader: {\n\t\t\tbold: true,\n\t\t\tfontSize: 10,\n\t\t\tcolor: 'black'\n\t\t},\n\t\t\tDataTable: {\n\t\t\tbold: false,\n\t\t\tfontSize: 10,\n\t\t\tcolor: 'black'\n\t\t},\n\t\t\tquote: {\n\t\t\titalics: true\n\t\t}\n \t}\n};\nmsg.filename = '/home/pi/Pictures/pdf/Daily_report10072022.pdf'\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 570,
"y": 680,
"wires": [
[
"e934fe0a.723cc"
]
]
},
{
"id": "51da5e06.c7fd",
"type": "file",
"z": "bf49b341.ae1dc",
"name": "",
"filename": "",
"appendNewline": false,
"createDir": true,
"overwriteFile": "true",
"encoding": "none",
"x": 890,
"y": 680,
"wires": [
[
"bdc36ecf.c1c4d"
]
]
},
{
"id": "bdc36ecf.c1c4d",
"type": "debug",
"z": "bf49b341.ae1dc",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 1050,
"y": 680,
"wires": []
},
{
"id": "e934fe0a.723cc",
"type": "pdfmake",
"z": "bf49b341.ae1dc",
"name": "",
"outputType": "Buffer",
"inputProperty": "payload",
"options": "{}",
"outputProperty": "payload",
"x": 740,
"y": 680,
"wires": [
[
"51da5e06.c7fd"
]
]
},
{
"id": "100f8ac.ed83575",
"type": "function",
"z": "bf49b341.ae1dc",
"name": "Select",
"func": "msg.topic = 'select Plan_Qty, Input_Qty, Finshed_Goods, Fall_off_rate, Productivity, TPT, Total_Tests, pass_count, fail_count, First_Pass, RW_Pass, RW_Pending from Line1 order by id desc limit 2'\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 190,
"y": 680,
"wires": [
[
"21247992.fef1d6"
]
]
},
{
"id": "21247992.fef1d6",
"type": "mysql",
"z": "bf49b341.ae1dc",
"mydb": "e3e96a2f.4a4ed8",
"name": "",
"x": 360,
"y": 680,
"wires": [
[
"8ba699c1.0f9858"
]
]
},
{
"id": "e8cd8b56.0e0908",
"type": "inject",
"z": "bf49b341.ae1dc",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 60,
"y": 680,
"wires": [
[
"100f8ac.ed83575"
]
]
},
{
"id": "e3e96a2f.4a4ed8",
"type": "MySQLdatabase",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "trial_03072021",
"tz": "",
"charset": "UTF8"
}
]
When I manually define the variables and call them at Table body (as shown in commented rows), this works well. But I cannot do this for all the rows. Reason: 1. High number of rows. 2. Rows count can vary .
I need help to write some loop query or so, so that I can get all the data from mysql to pdf report.
Please suggest.
