Sending multiple Reports via e-mail

[jonas]

Hi,

(for clarification: when I write data set, i refer to a single row inside a table, i hope this is correct)
I want to create and send multiple Reports to different e-mail addresses. This function shall be executed either regularly (i.E. once a week) or by pressing a single button, if the former would not be possible.
The Reports are dependent on a main set of data with an theoretical infinite amount of sub-datasets referring to the primary key of the main dataset. An external system writes data entries inside a seperate table, which contain the Primary Key of the main dataset and a specific timestamp. If such a new data entry exists, a Report containing this timestamp and the informations of the datasets und sub-datasets shall be created and auomaticly sent to a list of E-Mail addresses. Those E-Mail addresses ar listet in another table, containing the address and the primary key of the main dataset. After sending the E-Mails a value inside the table conataining the data entries will be set to 1 for this specific entry to mark the it as sent.

I already managed to send a single Report via e-mail using a button inside a form, with the specific main dataset selected by modifying the nurunpdf.php as explained on another topic inside this forum.
I already built the query for finding and selecting the not-yet sent data entrys and the e-mail addresses.

What I haven't been able to, is sending or creating a Report conatining the information of the dataset and sub-datasets, without being inside a form with those specifically selected.

Is there any way to create multiple reports dependent of different main-datasets and sub-datasets with a single action by just using the specific primary key of the main dataset given in the table containing the data entries?

Thank you for your help.

Jonas

[Janusz]

Hi,

Is there any way to create multiple reports dependent of different main-datasets and sub-datasets with a single action by just using the specific primary key of the main dataset given in the table containing the data entries?

to start discussion at least
If you want to send email with multiple attachments first you have to generate PDFs and store them on the server.

In the raport SQL use hash cookies to be able to set what you want as the report output.
e.g.:

Code: Select all

SELECT con_part,par_number,par_name,par_zlecenie,rap_number,rap_type,rap_location from v_connect_sub left join parts on parts_id=con_part
WHERE con_part='#parts_id2#'

Having that - you can generate by setting hash cookies - from any form the PDF file(s) with JS function which you can add to the button onclick(). Belowe just basic example but you will need to automatize with loop for your final solution. Here you can refer to any number of report templates.

Code: Select all

function report2(){
nuSetProperty('parts_id2','5f6ddc2fb4a469f');
nuRunReport("FR_PART_RAP");
nuSetProperty('parts_id2','c16012068020141002');
nuRunReport("FR_PART_RAP");
}

To be abble to save the file on the server you need to modify in the nurunpdf.php (from I to F and define the file name) e.g.

Code: Select all

...
//$PDF->Output('nureport.pdf', 'I');

// output to file
   $filename1 ='proto'.nuID().'.pdf';
   $filename =getcwd().'/temp/'.$filename1;              // for linux
// $filename =getcwd().'\temp\\'.$filename;         // for MS Windows

$PDF->Output($filename,'F');
...

There are some other problems that new windows are opening and not closing so you can add following function in JS: (together with report2() function)

Code: Select all

function nuRunReport2(f, iframe){
        var current                     = nuFORM.getCurrent();
        var last                        = $.extend(true, {}, current);
        last.session_id         = window.nuSESSION;
        last.call_type          = 'runreport';
        last.form_id            = f;
        last.hash                       = nuHashFromEditForm();
        var successCallback = function(data,textStatus,jqXHR){
                var fm                  = data;
                if(!nuDisplayError(fm)){
                        var pdfUrl      = 'nurunpdf.php?i=' + fm.id;
                        if(iframe === undefined){
                var pdf_window = window.open(pdfUrl);
				console.log(pdf_window);
setTimeout(function(){pdf_window.close();}, 2000);
                        }else{
                                parent.$('#'+ iframe).attr('src', pdfUrl);
                        }
                }
        }
        nuAjax(last,successCallback);
}

The above was working as quick try - but its far from something ready to use. You need to manage file names to know them exactly for mail attm, deleting old files, etc
Once you will have files on the server and list of emails then you can use:
http://192.168.6.105/wiki.nubuilder.net ... uSendEmail
If on your server you have mail server running - then you can use as well standard php mail function.

[jonas]

Hello, thanks for the reply.

What I'm still struggling with is, to generate a report where the Hash Cookies reference a specific selected Dataset inside a Form, without running the report inside that form.
For example: I have a location for each dataset. My Report gets this Location via #CUSTOMER_LOCATION#. How can I input that value into my report without runiing it inside the form with this specific customer selected?

[Janusz]

My Report gets this Location via #CUSTOMER_LOCATION#. How can I input that value into my report without runiing it inside the form with this specific customer selected?

On any edit form first you have to define the object keeping all the CUSTOMER_LOCATION references. For that can be used Display object where you can define for example:

Code: Select all

SELECT GROUP_CONCAT(CUSTOMER_LOCATION SEPARATOR ',')
FROM your_table
WHERE cust_invoice = 'not paid' 

(adjust to your case) to make it simple assign FF_disp_mail_rap as ID of it,you will get long string displayed in it separated by comma.
After you can read that field from the function which will generate raport for each separate CUSTOMER_LOCATION.

to read that field you can use function like following placed in the form JS.
FF_disp_mail_rap - id of the display object
FR_PART_RAP2 - raport ID (change to your raport ref)

Code: Select all

function report2(){
var files=$('#FF_disp_mail_rap').val();
    files = files.split(",");
files.forEach(element => 
{
nuSetProperty('CUSTOMER_LOCATION',element);
nuRunReport2("FR_PART_RAP2");
    }
);
setTimeout(function(){nuGetBreadcrumb();}, 1500);
}

the above function refers to the following function:

Code: Select all

function nuRunReport2(f, iframe){
        var current                     = nuFORM.getCurrent();
        var last                        = $.extend(true, {}, current);
        last.session_id         = window.nuSESSION;
        last.call_type          = 'runreport';
        last.form_id            = f;
        last.hash                       = nuHashFromEditForm();
        var successCallback = function(data,textStatus,jqXHR){
                var fm                  = data;
                if(!nuDisplayError(fm)){
        var pdfUrl  = 'nurunpdf2.php?i=' + fm.id;
                        if(iframe === undefined){
        var fd = new FormData();
        fd.append('ID',fm.id);
        var xhr = new XMLHttpRequest();
        xhr.open('POST', 'nurunpdf2.php', true);
        xhr.send(fd);
                        }else{
                                parent.$('#'+ iframe).attr('src', pdfUrl);
                        }
                }
        };
        nuAjax(last,successCallback);
}

Additionally to write the PDF's directly to the disk (and not opening in the browser) you need to copy nurunpdf.php to nurunpdf2.php and modify it (in the attachement already modified version so you can compare with original - there are some comments included)
files will be strored in the temp folder so you need to creat it. Depending on the system Linux or Windows you need to adjust the code with path naming standard in the nurunpdf2.php (currently it's for linux)
nurunpdf2.php - will created as well a table on the server named pdf_temp_attm where all created pdf files will be stored.
here you can download the nurunpdf2.php - so just copy it to the main nuBuilder folder on the server
https://drive.google.com/file/d/1W1tSE6 ... sp=sharing
and short movie how it works:
https://drive.google.com/file/d/1wA9B0R ... sp=sharing
(and in button onclick place report2();)

[admin]

Janusz,

I have used some of your code to create a Javascript function called nuRunReportSave().

I have not documented it yet because I want it tested please.

(It saves a pdf to the temp directory.)

It's on Github now.


Steven

[Janusz]

Thanks Steven,
Just tested and it works fine
It's OK for both nuRunReport() and nuRunReportSave()

and for those who work on linux before test you need to change the temp folder for writing ( chmod 777 temp ).

[admin]

Janusz,

I'm running Windows and I found I had to use the line referred to as "// for linux"...

Code: Select all

// output to file
   $filename1 ='proto'.nuID().'.pdf';
   $filename =getcwd().'/temp/'.$filename1;              // for linux
// $filename =getcwd().'\temp\\'.$filename;         // for MS Windows

Steven

[Janusz]

Steven,
The code marked for Linux is for sure OK on linux
but I do not have much experience with nuBuilder running on latest MS WIndows (and maybe there were some changes recently)
I have applications running if I well remember on Windows Server 2008 or 2012 - and the linux notation is not OK for that - that's why I noticed such difference.

https://superuser.com/questions/176388/ ... rd-slashes
https://stackoverflow.com/questions/533 ... slash-in-c

[admin]

Janusz,

What do you get on your OS's when you run...

Code: Select all

var_dump(PHP_OS);

Steven

[Janusz]

Steven,
I get: string(5) "Linux"

and my server configuration is:
---------------------------------------------------------------------
Linux 4.19.0-11-amd64 x86_64
---------------------------------------------------------------------
No LSB modules are available.
Distributor ID: Debian
Description: Debian GNU/Linux 10 (buster)
Release: 10
Codename: buster
Debian ver: 10.6
---------------------------------------------------------------------
mysql Ver 15.1 Distrib 10.5.6-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
---------------------------------------------------------------------
PHP 7.4.11 (cli) (built: Oct 8 2020 17:32:43) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
with Zend OPcache v7.4.11, Copyright (c), by Zend Technologies
---------------------------------------------------------------------

Regarding Windows applications I have very limited access to the server.