Hello. I am facing a challenge in passing a dynamic php variable to an SQL statement i wrote in Jaspersoft iReport. I downloaded the PHP-Jasperserver-integration library and i am managing to open a jrxml file i created in Jaspersoft and then using the libraries to open it in PDF format, here is the php code:
report.php
<?php
//Import the PhpJasperLibrary
include_once('PhpJasperLibrary/tcpdf/tcpdf.php');
include_once("PhpJasperLibrary/PHPJasperXML.inc.php");
include "includes/functions.php";
//database connection details
$server="localhost";
$db="underwriting";
$user="root";
$pass="";
$pchartfolder="./class/pchart2";
if ( $_REQUEST_METHOD = 'GET' ) {
if ( (isset( $_GET['url'] ) && !empty( $_GET['url'] ))) {
$file = escape_value( $_GET['url'] );
}
}
//display errors should be off in the php.ini file
ini_set('display_errors', 0);
//setting the path to the created jrxml file
$xml = simplexml_load_file('reports/billing_schedule.jrxml');
$PHPJasperXML = new PHPJasperXML();
//$PHPJasperXML->debugsql=true;
//$PHPJasperXML->arrayParameter=array("parameter1"=>1);
$PHPJasperXML->xml_dismantle($xml);
$PHPJasperXML->transferDBtoArray($server,$user,$pass,$db);
$PHPJasperXML->outpage("I"); //page output method I:standard output D:Download file
?>
And the SQL query i wrote in Jaspersoft:
SELECT
policydetails.policyStatus AS policyStatus,
policydetails.payingAuthorityName AS payingAuthority,
policydetails.policyNumber AS policyNumber,
policydetails.firstName AS firstName,
policydetails.lastName AS lastName,
policydetails.premium AS premium,
policydetails.coverDate AS coverDate,
policydetails.ECNumber AS ECNumber,
policydetails.IDNumber AS IDNumber
FROM
policydetails
WHERE
(policydetails.payingAuthorityName = 'Unifreight') AND
(policydetails.policyStatus = 'Active' OR
policydetails.policyStatus = 'Accepted' OR
policydetails.policyStatus = 'Lapsed' OR
policydetails.policyStatus = 'Underwriting')
ORDER BY
policyStatus ASC,
policyNumber ASC
;
Now the problem i am having is how to pass a php variable from report.php taken from a dropdown combo box in another script(which a value is then passed to report.php using url parameter) and then replace 'Unifreight' in the above SQL WHERE clause with the dynamic php variable so that the records listed will be dependant on the value picked from the combo box.
I need to know how to re-write report.php to accomodate the php variable to be passed to the SQL query in Jasper. Any help would be greatly appreciated!!