Automate/Schedule a report

Automate/Schedule a report

Postby csnet » Fri Jan 03, 2014 8:41 pm

Hi,
I have a freight Management System I build for my customer using nuBuilder.
All have designed all the necessary reports. But I had a request from my customer to have some of the reports emailed automatically to some of the users.
Is there a way I can schedule a cron Job to email nuBuilder's reports?
Regards,
csnet
 
Posts: 11
Joined: Thu Sep 30, 2010 3:43 am

Re: Automate/Schedule a report

Postby massiws » Tue Jan 07, 2014 11:14 pm

csnet,
actually nuBuilder can't do this.
I've never tried it, but you could create a PHP script (to call on server's cronjob) that bootstrap nuBuilder framework and execute emailIt() javascript function.

Let us know if you manage to get something done.

Max
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: Automate/Schedule a report

Postby Fike » Thu Feb 13, 2014 11:50 pm

I have done it this way:

First I created a form that allows access without login.

This form contains the parameters of the report in text fields that are updated using javascript functions that are executed when the form is loaded (using the nuLoadThis() function).

At the end of the nuLoadThis function I have a 'runIt(param1,param2,param3,param4,...)' function that opens the Activity which contains a php script that reads the parameters, builds the report and emails it.

The php script in that activity is this (some parts of the script are take from the nuBuilder source code) :


Code: Select all
//---Begining of script

$nombre_servidor = $_SERVER['HTTP_HOST']; 


require_once("emaillib.php");
require_once(
"common.php");


//this are parameters passed from the opened form
$rep_turno_id = "#runIt1#";
$turno = "#runIt2#";
$reporte = "#runIt3#";
$corte = "#runIt4#";
$fecha = "#runIt5#";
$hora = "#runIt6#";

$x              = $_GET['x'];
$dir            = $_GET['dir'];
$ses            = $_GET['ses'];
$form_ses       = $_GET['form_ses'];
$r              = $reporte;

$toRun = "runpdf.php";
$ext   = ".PDF";
$report_url = 'http://'.$nombre_servidor.'/productionnu2/'.$toRun;



$fqurl          = $report_url."?x=".$x."&dir=".$dir."&ses=".$ses."&form_ses=".$form_ses."&r=".$r."&emailer=1"."&rep_turno_id=".$rep_turno_id."&turno=".$turno."&fecha=".$fecha."&hora=".$hora;


//this are just descriptions for the email subject based on whic reporte will be sent 

if($reporte == '1010-RPT-0022') {$desc = 'diario de horas normales ';}
if(
$reporte == '1010-RPT-0023') {$desc = 'diario de horas extra ';}
if(
$reporte == '1010-RPT-0023-03') {$desc = 'diario de horas extra ';}
if(
$reporte == '1010-RPT-0024') {$desc = 'semanal de horas extra ';}
if(
$reporte == '1010-RPT-0026') {$desc = 'consolidado de horas extra ';}

function getReportFile($fqurl, $ext, $rep_turno_id, $nombre) 
{
    // BEGIN - 2009/06/10 - Michael
    // Changed the code that gets the report's content from fopen to CURL.

    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $fqurl);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    $content = curl_exec($ch);
    curl_close($ch);

     // END - 2009/06/10 - Michael
    
    $pfile 
= dirname(__FILE__)."/temppdf/".$nombre.$ext;
  
    $fp 
= @fopen($pfile,"w");
    // BEGIN - 2009/05/29 - Michael
    // Make sure we could open our temp file.
    if (!$fp)
        return 'no se puede abrir el archivo';
    // Make sure we could write to our temp file.
    if (!@fwrite($fp,$content))
    {
            // We need to close the file because we did open it.
            fclose($fp);
        return NULL;
    } // if
     // END - 2009/05/29
    fclose($fp);
 
    return $pfile
;
  
    
//unlink($pfile);
}



$rep_name = uniqid('1');
$nombre = "turno-".$turno."-fecha-".$fecha."-id-".$rep_name;
$origen_pdf  = getReportFile($fqurl, $ext, $rep_turno_id, $nombre);


$subject = utf8_decode("Reporte ".$desc."trabajadas (".$corte.") para el turno ".$turno);


$table_mail = nuRunQuery("SELECT rep_tur_mail FROM rep_turno WHERE rep_turno_id = '$rep_turno_id' ");
 
while($array_mail = db_fetch_array($table_mail))
{
 
   $mail_list 
= $array_mail['rep_tur_mail'];
}

$receptores = $mail_list;

nuSendEmail($receptores, "xxx@gmail.com", $mensaje, true, $subject, 120, array($nombre.'.pdf' => $origen_pdf), false, "XXX", "XXX");



echo '<script type=\'text/javascript\'>'."\n";
echo 'setTimeout(function(){window.open("", "_self", "");window.close();},\'20000\');';
echo '</script>'."\n";



//---End of php script    




Finally I just setup a scheduled task on a windows machine (because it is easier for me) that opens a batch file which contains the following script:



Code: Select all
start /min iexplore "http://yourservername.com/productionnu2/form.php?x=1&f=heregoesyourformid&r=heregoestherecordidthatcontainstheparameters&dir=db/yourdatabasename"




This way you don't need to create the cron job on the server. It can be created on any other machie. It just needs to open a browser window with the url of the created form.

However, If you want to schedule a cron job in the server that opens the created form, this might work:

Code: Select all
wget -qO /dev/null http://yourservername.com/productionnu2/form.php?x=1&f=heregoesyourformid&r=heregoestherecordidthatcontainstheparameters&dir=db/yourdatabasename



I hope it helps.

Kind regards,

Fike
Fike
 
Posts: 79
Joined: Fri Oct 21, 2011 4:43 am

Re: Automate/Schedule a report

Postby massiws » Fri Feb 14, 2014 2:22 am

Fike, I correct your post only for a better readability.

Thank you for sharing it!

Max
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy


Return to Report Writer