jsreport forum

Anandanr

"The studio doesn't"
I was confused by this.

By schedule it works! Thanks..

Anandanr

I am having this script already.
The issue is, report is not stored to s3 while running through studio or schedules.

It is storing or going s3 while I am running through API with following:
POST http://xx.xx.xx.xxx:8443/api/report
{
"template": { "shortid" : "2F44QoE" },
"options": {
"reports": { "save": true }
}
}

Just I need to bring this to the script.

Anandanr

I can store my xlsx file by API call as follows:
POST http://xx.xx.xx.xxx:8443/api/report

{
      "template": { "shortid" : "2F44QoE" },      
      "options": {
          "reports": { "save": true, "blobName": "dailyrpta/myfilename3" }
      }
   }

But, I need to do this in the script with the template because I have to scedule to run daily, I have tried the following, even this is working with the API call only.

async function beforeRender(req, res) {   
    if (res.meta.reportsOptions) {
        res.meta.reportsOptions.blobName = "dailyrpta/daily_report" + new Date().getTime()   
    }
}

I think I am missing only this part "reports": { "save": true, ) in the script.
Please let me know if you have any idea.

Anandanr

Working ! after

function beforeRender(req, res) { 
    req.options.preview = false  
}

Thanks..

Anandanr

This is what there in the xlsx file

<html><head><title>daily_total_template</title><body><iframe style="height:100%;width:100%" src="https://view.officeapps.live.com/op/view.aspx?src=http%3A%2F%2Fjsreport.net%2Ftemp%2F6057feaac4dfce7935c2c6c585303497.xlsx" /></body></html>

I can download the file and open that manually but I need to have that file on mail.

I tied making preview = false, it was only stops to view the stuido

Anandanr

Thankyou ! it works.

Anandanr

I am getting this error whiel opening the excel file which has been sent

Debug log:

Render request 43 queued for execution and waiting for availible worker
Starting rendering request 43 (user: admin)
Rendering template { name: daily_total_template, recipe: html-to-xlsx, engine: handlebars, preview: true }
Data item not defined for this template.
Executing script daily_total_script (beforeRender)
Executing script st_file_store_script (beforeRender)
Executing script dt_mail_out (beforeRender)
Base url not specified, skipping its injection.
Rendering engine handlebars
Executing recipe html-to-xlsx
html-to-xlsx generation is starting
html-to-xlsx generation was finished
Executing script st_file_store_script (afterRender)
Executing script dt_mail_out (afterRender)
Skipping storing report.
Rendering request 43 finished in 2330 ms

I tried all of these:

attachments: [
      {  
        // content: res.content.toString('base64'),
        // content: Buffer.from(res.content).toString('base64'),
        filename: 'report.xlsx',
        // content: Buffer.from(res.content),
        content: res.content.toString('base64'),
        // content: Buffer.from(res.content).toString('base64'),
        disposition: "attachment" ,
        // contentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        contentType: 'application/octet-stream',
      }],
    })       
}

Anandanr

Getting following error while starting the jsreport with
npm install jsreport-aws-s3-storage

# jsreport start

 error: Error occurred during reporter init: Error: Error when loading extension aws-s3-storage@1.1.0
Error: Extension "aws-s3-storage" is not compatible with "core" extension. Missing ".requires" information in "aws-s3-storage" definition

jsreport -v
jsreport version: 3.6.2
cli version: 3.2.1

Anandanr

With html to xlsx I cant send the report as *.xlsx but when I use the chrome pdf I can send the genrated report.pdf file with mail attachement.

I need to send it to mail and to s3 aswell.

const nodemailer = require('nodemailer')
async function afterRender(req, res) {
    const smtpTransport = nodemailer.createTransport({
      host: 'smtp.ethereal.email',
      port: 587,
      auth: {
          user: 'yyy',
          pass: 'xxx'
      }
const mail = {
        from: "Anand <anandanr@gmail.com>",
       to: "anandanr@gmail.com",
       subject: "Sending email from node.js",
      text: "See the attached report",
        attachments: [
        {  
            // filename: 'rout.xlsx',
            content: res.content.toString('base64'),
            type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            filename: "data.xlsx",
            disposition: "attachment" 
        }],
    }

    try {
        await smtpTransport.sendMail(mail)
    } finally {
        smtpTransport.close()
    }
}
    })

Anandanr

I have template to working with a node js script to get data from snowflake but it is getting data but it is not showing up in the template result.

<html>

<head>
    <style>
        {
                {
                asset "sd-styles.css""utf8"
            }
        }
    </style>
</head>
        <tbody class="posts">
            <tr>
                <th class="t-center">State</th>
                <th class="t-center">Credits</th>
                <th class="t-center">Actual Cash</th>
                <th class="t-center"> Unrevealed </th>
                <th class="t-center"> Customer Funds </th>
            </tr>
            {{#each posts}}
            <tr style="height: 20px, class="post">
            <td dclass="t-right" data-cell-type="text">{{STATE}}</td>
                <td class="t-right" data-cell-type="number" data-cell-format-str="0.00">{{STATE_CREDITS}}</td>
                <td class="t-right" data-cell-type="number" data-cell-format-str="0.00">{{STATE_ACTUAL_CASH}}</td>
                <td class="t-right" data-cell-type="number" data-cell-format-str="0.00">{{STATE_UNREVEALED}}</td>
                <td class="t-right" data-cell-type="number" data-cell-format-str="0.00">{{STATE_CUSTOMER_FUNDS}}</td>
            </tr>
            {{/each}}
        </tbody>
    </table>
</body>

</html>

Script nodejs:

async function beforeRender(req, res) {
  var snowflake = require('snowflake-sdk');
    const connection = snowflake.createConnection({
    account: '******.eu-west-2.aws',
    username: '*****',
    password: '*********' 
    //clientSessionKeepAlive: true     
  });
         connection.connect( 
          function(err, conn) {
            if (err) {
                console.error('Unable to connect: ' + err.message);
                } 
            else {
                console.log('Successfully connected to Snowflake.');
                // Optional: store the connection ID.
                //connection_ID = conn.getId();
                }
        }
    )
        connection.execute({
        sqlText: 'select * FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO',
        complete: function(err, stmt, rows) {
          if (err) {
            console.error('Failed to execute statement due to the following error: ' + err.message);
          } else {
            console.log('Number of rows produced: ' + rows.length);
            console.log(JSON.stringify(rows));
            req.data.posts = rows
          }
        }
      });
        connection.execute({
              sqlText: 'select * FROM SUPERDRAFT_PROD.SD_REPORTS.VW_DAILY_STATE_BALANCE_SDPRO;',
              complete: function (err, stmt, rows)
              {
                  var stream = stmt.streamRows();
                  stream.on('data', function (row)
                  {
                      console.log(row);
                      console.log(JSON.stringify(row));
                  });
                  stream.on('end', function (row)
                  {
                      console.log('All rows consumed');
                  });

                  req.data.posts = stream
              }
            })
        connection.destroy(function(err, conn) {
        if (err) {
          console.error('Unable to disconnect: ' + err.message);
        } else {
          console.log('Disconnected connection with id: ' + connection.getId());
        }
  })
}

Logs debug details:


info
+0
Render request 18 queued for execution and waiting for availible worker
info
+15
Starting rendering request 18 (user: admin)
info
+0
Rendering template { name: daily_state_balance_template, recipe: chrome-pdf, engine: handlebars, preview: true }
debug
+1
Data item not defined for this template.
debug
+4
Executing script daily_state_balance (beforeRender)
debug
+532
Base url not specified, skipping its injection.
debug
+1
Rendering engine handlebars
debug
+32
Executing recipe chrome-pdf
debug
+6
Converting with chrome HeadlessChrome/98.0.4758.0 using chrome-pool strategy
debug
+64
Page request: GET (document) file:///tmp/jsreport/autocleanup/fb23f58e-09f7-43db-ad7d-0859e4b305fb-chrome-pdf.html
debug
+20
Page request finished: GET (document) file:///tmp/jsreport/autocleanup/fb23f58e-09f7-43db-ad7d-0859e4b305fb-chrome-pdf.html
debug
+10
Running chrome with params {"printBackground":true,"timeout":59409,"margin":{}}
debug
+25
Skipping storing report.
info
+1
Rendering request 18 finished in 712 ms

We can see the request is reaching to snowflake and it success

0_1664389634100_upload-a89620f1-5998-469f-8e50-28147094a695

Log debug details, see you can see the output from the snowflake here:


info
+0
Render request 692 queued for execution and waiting for availible worker
info
+19
Starting rendering request 692 (user: admin)
info
+1
Rendering template { name: daily_state_balance_template, recipe: chrome-pdf, engine: handlebars, preview: true }
debug
+3
Data item not defined for this template.
debug
+18
Executing script daily_state_balance (beforeRender)
debug
+657
Base url not specified, skipping its injection.
debug
+0
Rendering engine handlebars
debug
+29
Executing recipe chrome-pdf
debug
+437
Successfully connected to Snowflake.
error
+157
Failed to execute statement due to the following error: Unable to perform operation using terminated connection.
debug
+2
Disconnected connection with id: 5728cff4-8369-4326-8b37-febf90f0840c
debug
+157
{
  STATE: 'NH',
  STATE_CREDITS: 0,
  STATE_ACTUAL_CASH: 684510.37,
  STATE_UNREVEALED: 0,
  STATE_CUSTOMER_FUNDS: 684510.37
}
debug
+0
{
  STATE: 'ON',
  STATE_CREDITS: 0,
  STATE_ACTUAL_CASH: 1698745,
  STATE_UNREVEALED: 0,
  STATE_CUSTOMER_FUNDS: 1698745
}
debug
+0
{
  STATE: 'Ohio',
  STATE_CREDITS: 0,
  STATE_ACTUAL_CASH: 1437135,
  STATE_UNREVEALED: 0,
  STATE_CUSTOMER_FUNDS: 1437135
}
debug
+0
{
  STATE: 'Wisconsin',
  STATE_CREDITS: 0,
  STATE_ACTUAL_CASH: 1283727,
  STATE_UNREVEALED: 0,
  STATE_CUSTOMER_FUNDS: 1283727
}
debug
+1
All rows consumed
debug
+254
Converting with chrome HeadlessChrome/98.0.4758.0 using chrome-pool strategy
debug
+300
Page request: GET (document) file:///tmp/jsreport/autocleanup/0af73c52-35b3-42c4-a534-3f42b4f1e068-chrome-pdf.html
debug
+22
Page request finished: GET (document) file:///tmp/jsreport/autocleanup/0af73c52-35b3-42c4-a534-3f42b4f1e068-chrome-pdf.html
debug
+29
Running chrome with params {"printBackground":true,"timeout":59121,"margin":{}}
debug
+49
Skipping storing report.
info
+0
Rendering request 692 finished in 2135 ms

Anandanr

@Anandanr

Posts made by Anandanr