after argument list sandbox.js:16

djbrody

Postgres report

Render request 16 queued for execution and waiting for availible worker
info+7Starting rendering request 16 (user: admin)
info+3Rendering template { name: Daily_State_Balance(clone), recipe: html-to-xlsx, engine: handlebars, preview: true }
debug+4Data item not defined for this template.
debug+3Executing script dev11db-test (beforeRender)
error+10Error when processing render request 16 Error when evaluating custom script /Dev11/dev11db-test
missing ) after argument list sandbox.js:16
    const res = await client.query('with balances as ( select acco_id, ROUND(SUM(CASE WHEN wallet_type_id=1 and fund_type_id=1 THEN balance ELSE 0 END), 2) ACTUAL_CASH, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=11 THEN balance ELSE 0 END), 2) CREDITS, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=16 THEN balance ELSE 0 END), 2) UNREVEALED, current_date -1  as trans_date from betcatch.account_balances where brand_id = 3 group by 1 ) select contact_details::json->'ContactDetail'->>'address4' as State, SUM(CREDITS) STATE_CREDITS, SUM(ACTUAL_CASH) STATE_ACTUAL_CASH, SUM(UNREVEALED) STATE_UNREVEALED, SUM((ACTUAL_CASH + UNREVEALED)) "state_customer_funds" from accounts a inner join balances b on a.id = b.acco_id where brand_id = 3 group by 1 by state ORDER BY 1;') 
                                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

SyntaxError: missing ) after argument list
    at new Script (node:vm:100:7)
    at doCompileScript (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/createSandbox.js:224:14)
    at run (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/createSandbox.js:169:72)
    at WorkerReporter._runInSandbox (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/sandbox/runInSandbox.js:163:12)
    at WorkerReporter.runInSandbox (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/reporter.js:152:17)
    at async executeScript (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-scripts/lib/executeScript.js:122:12)
    at async Scripts._runScript (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-scripts/lib/worker.js:140:30)
    at async Scripts.handleBeforeRender (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-scripts/lib/worker.js:64:7)
    at async ListenerCollection.fire (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/shared/listenerCollection.js:157:21)
    at async beforeRender (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/render/render.js:25:5)
    at async WorkerReporter._render (/home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/render/render.js:146:7)
    at async /home/ubuntu/jsreportapp/node_modules/@jsreport/jsreport-core/lib/worker/reporter.js:179:19
    at async Domain.<anonymous> (/home/ubuntu/jsreportapp/node_modules/@jsreport/advanced-workers/lib/workerHandler.js:141:19)
error+0Rendering request 16 finished with error in 27 ms

DATA script

async function afterRender (req, res) {
  const { Client } = require('pg')

  const credentials = {
    user: 'sddev',
    host: 'dev11-ro-database-01.************.us-east-2.rds.amazonaws.com',
    database: 'ats_whitelabel',
    password: '*************',
    port: 5432
  }
  // Connect with a client.

  async function clientDemo () {
    const client = new Client(credentials)
    await client.connect()
    const res = await client.query('with balances as ( select acco_id, ROUND(SUM(CASE WHEN wallet_type_id=1 and fund_type_id=1 THEN balance ELSE 0 END), 2) ACTUAL_CASH, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=11 THEN balance ELSE 0 END), 2) CREDITS, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=16 THEN balance ELSE 0 END), 2) UNREVEALED, current_date -1  as trans_date from betcatch.account_balances where brand_id = 3 group by 1 ) select contact_details::json->'ContactDetail'->>'address4' as State, SUM(CREDITS) STATE_CREDITS, SUM(ACTUAL_CASH) STATE_ACTUAL_CASH, SUM(UNREVEALED) STATE_UNREVEALED, SUM((ACTUAL_CASH + UNREVEALED)) "state_customer_funds" from accounts a inner join balances b on a.id = b.acco_id where brand_id = 3 group by 1 by state ORDER BY 1;') 
    await client.end()
  }

  const clientResult = await clientDemo()
  console.log('Time with client: ' + clientResult.rows[0].now)

};

Report

<style>
    td, th {
        padding: 5px;
        text-align: right;
    }
</style>
<table name="Data">
    <tr>
        <td class="title" colspan="10">DAILY STATE BALANCE</td>
    </tr>
    <tr class="cell-titles">
        <th>STATE</th>
        <th>CREDITS</th>
        <th>ACTUAL_CASH</th>
        <th>UNREVEALED</th>
        <th>CUSTOMER_FUNDS</th>
    </tr>
    {{#each rows}}
        <tr>
            <td>{{{STATE}}}</td>
            <td>{{{STATE_CREDITS}}}</td>
            <td>{{{STATE_ACTUAL_CASH}}}</td>
            <td>{{{STATE_UNREVEALED}}}</td>
            <td>{{{STATE_CUSTOMER_FUNDS}}}</td>
        </tr>
    {{/each}}
</table>

jan_blaha

You have a syntax error in the script as the error explains. Check that line carefully.

djbrody

this SQL correct line

contact_details::json->'ContactDetail'->>'address4' as State,

But I guess the script does not like this what would be the option for jsreport?

bjrmatos

hi! the fix is not anything in particular related to jsreport or the jsreport script, it is more a problem with javascript strings.

as you know you can delimit a javascript string with either ' or ", however since your string is expected to contain SQL queries, then you are using ' inside the string when this happens javascript gets confused, because it thinks you are trying to close the string, in this case, you have two options you either use template literals `string` or escape the ' characters inside your string:

option 1:

`with balances as ( select acco_id, ROUND(SUM(CASE WHEN wallet_type_id=1 and fund_type_id=1 THEN balance ELSE 0 END), 2) ACTUAL_CASH, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=11 THEN balance ELSE 0 END), 2) CREDITS, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=16 THEN balance ELSE 0 END), 2) UNREVEALED, current_date -1 as trans_date from betcatch.account_balances where brand_id = 3 group by 1 ) select contact_details::json->'ContactDetail'->>'address4' as State, SUM(CREDITS) STATE_CREDITS, SUM(ACTUAL_CASH) STATE_ACTUAL_CASH, SUM(UNREVEALED) STATE_UNREVEALED, SUM((ACTUAL_CASH + UNREVEALED)) "state_customer_funds" from accounts a inner join balances b on a.id = b.acco_id where brand_id = 3 group by 1 by state ORDER BY 1;`

option 2:

'with balances as ( select acco_id, ROUND(SUM(CASE WHEN wallet_type_id=1 and fund_type_id=1 THEN balance ELSE 0 END), 2) ACTUAL_CASH, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=11 THEN balance ELSE 0 END), 2) CREDITS, ROUND(SUM(CASE WHEN wallet_type_id=4 and fund_type_id=16 THEN balance ELSE 0 END), 2) UNREVEALED, current_date -1 as trans_date from betcatch.account_balances where brand_id = 3 group by 1 ) select contact_details::json->\'ContactDetail\'->>\'address4\' as State, SUM(CREDITS) STATE_CREDITS, SUM(ACTUAL_CASH) STATE_ACTUAL_CASH, SUM(UNREVEALED) STATE_UNREVEALED, SUM((ACTUAL_CASH + UNREVEALED)) "state_customer_funds" from accounts a inner join balances b on a.id = b.acco_id where brand_id = 3 group by 1 by state ORDER BY 1;'

(note that now this part \'ContactDetail\'->>\'address4\' is escaped)

djbrody

Thanks for the help with sql

hit this but I know I run npm install -g pg

Error when evaluating custom script /Dev11/dev11db-test
Unable to find module pg
The require calls:
require('pg')
require('/home/ubuntu/jsreportapp/pg')


(sandbox.js line 2:22)

  1 | async function afterRender (req, res) {
> 2 |   const { Client } = require('pg')
    |                      ^
  3 |
  4 |   const credentials = {
  5 |     user: 'sddev',

bjrmatos

hit this but I know I run npm install -g pg

you installed the pg module globally, however in order for the jsreport script to recognize such module it should be installed locally in your jsreport server project, so basically it needs to be part of the package.json of the jsreport server, you can go to the folder where your package.json is and execute npm i pg --save

djbrody

Ok so update pg locally and restarted now have this error. This is the config for my production

Report "dev11_Daily_State_Balance" render failed.

Connection terminated
Error: Connection terminated
    at Connection.<anonymous> (/home/ubuntu/jsreportapp/node_modules/pg/lib/client.js:132:36)
    at Object.onceWrapper (node:events:641:28)
    at Connection.emit (node:events:539:35)
    at Connection.emit (node:domain:537:15)
    at Socket.<anonymous> (/home/ubuntu/jsreportapp/node_modules/pg/lib/connection.js:57:12)
    at Socket.emit (node:events:527:28)
    at Socket.emit (node:domain:537:15)
    at TCP.<anonymous> (node:net:709:12)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17)

bjrmatos

this looks like an issue with the connection to postgres itself, have you tried running the code alone? i mean outside of jsreport, you can take all the code inside your afterRender function and put it in a file like test-connection.js inside your jsreport project, then start testing with node test-connection.js, if you hit the same error you should likely check the https://github.com/brianc/node-postgres docs, perhaps there is something else that you are missing to configure in the connection options.