Re: Nice lookin’ reports
Well there is not much of an app to do but I compiled a short description the way I did it.
Download and install iReport.
Download and extract phpjasperxml.
You might want to get the latest tcpdf as well ( but that’s optional).
Create a subfolder (i.e. “reports”) in documentroot/scriptcase/app/yourproject/.
Copy class-folder and sample1.php from phpjasperxml to the folder created in the previous step.
I renamed sample1.php to index.php and edited it as nessecary.
Mine looks like this
<?php
session_start();
include_once(‘class/tcpdf/tcpdf.php’);
include_once(“class/PHPJasperXML.inc.php”);
$xml = simplexml_load_file($_SESSION[xml_file]); //variable for jrxml file
$PHPJasperXML = new PHPJasperXML();
$PHPJasperXML->arrayParameter=array(“pdf_query”=>$_SESSION[pdf_query]); //variable for sql statement
$PHPJasperXML->xml_dismantle($xml);
$PHPJasperXML->transferDBtoArray($host,$user,$pass,$db);//you have to handle db-access yourself
$PHPJasperXML->outpage(“I”); //page output method I:standard output D:Download file
?>
Design your report and the sql-query using iReport and make sure it works as expected. As mentioned in the wiki for phpjasperxml
don’t use any fancy number or date formating in iReport, use the sql tools instead.
The last thing you want to do in the Designer is copy the sql statement you’ve created and save it somewhere and replace it
with the parameter corresponding to the index.php. (in my case it’s “$P{pdf_query}”).
Save your report and copy the file to your “reports” folder of your project.
Now we’re heading over to SC.
Chose the application you want to print from and create a new php-button and label it “Print” or “PDF” or whatever you like.
In the code area just paste your sql statement saved earlier, set the xml filename like the sample below and fire it up.
$pdf_query="SELECT mit_firstname, mit_name, mit_address, mit_postcode, mit_city, invoice.rch_id, rch_date,
REPLACE(CAST(rch_total AS CHAR), '.', ','), rpos_text, REPLACE(CAST(rpos_price AS CHAR), '.', ','),
REPLACE(CAST(rpos_total AS CHAR), '.', ','), REPLACE(CAST(rpos_vat_amount AS CHAR), '.', ',')
FROM customer, invoice, rposition
WHERE customer.mit_id=invoice.mit_id and rposition.rch_id=invoice.rch_id and invoice.rch_id={rch_id}";//sql statement from iReport
$xml_file="invoice.jrxml"; //filename of report created with iReport
sc_set_global($pdf_query);
sc_set_global($xml_file);
sc_redir(../reports/index.php);
That’s it.
One thing to remember, when you deploy your project don’t forget to upload your “reports” folder separately because it is not included in SC.
If you/somebody can figure out how to include it in SC would be great. I just didn’t have the time so i took this way.
Hope this helps.
Edit:
If you use a library with all your functions for your application you can ease up the database access a bit by creating a function.
May be something like this:
function report($pdf_query,$xml_file)
{
if(!isset($_SESSION[‘report’]))
{
$_SESSION[‘report’]=array(‘host’=>[sc_glo_servidor],‘user’=>[sc_glo_usuario],‘pass’=>sc_decode([sc_glo_senha]),
‘db’=>[sc_glo_banco],‘pdf_query’=>’’,‘xml_file’=>’’);
}
$_SESSION['report']['xml_file']="../reports/".$xml_file;
$_SESSION['report']['pdf_query']=$pdf_query;
sc_redir(../reports/index.php,"","_blank");
}
And don’t forget to alter your index.php in the reports folder.
<?php
session_start();
include_once(‘class/tcpdf/tcpdf.php’);
include_once(“class/PHPJasperXML.inc.php”);
$xml = simplexml_load_file($_SESSION[‘report’][‘xml_file’]); //variable for jrxml file
$PHPJasperXML = new PHPJasperXML();
$PHPJasperXML->arrayParameter=array(‘pdf_query’=>$_SESSION[‘report’][‘pdf_query’]); //variable for sql statement
$PHPJasperXML->xml_dismantle($xml);
$PHPJasperXML->transferDBtoArray($_SESSION[‘report’][‘host’],
$_SESSION[‘report’][‘user’],
$_SESSION[‘report’][‘pass’],
$_SESSION[‘report’][‘db’]);
$PHPJasperXML->outpage(“I”); //page output method I:standard output Download file
?>
Have fun
jsb
