Part 3
In Part 2, we made fun of end users. End users are perhaps the funniest little clowns of the entire plant kingdom, and that is why they are as revered and respected as they are. We also created a basic reports list table, added a grid to list the reports, and added a form to add/modify/delete reports.
Now we’ll create a ScriptCase Control Application that allows its user to select a report from a dropdown, then displays the details of that report, and prompts the user for the required parameters.
But first, back to our Outline.
The User Interface should allow the user to:
- Select only the User-Viewable reports.
- See the Report Name (output only)
- See the Report Description (output only)
- See the Report Preview screenshot (output only)
- Enter report parameters (these can be anything, dates, names, etc)
That isn’t pretty, but it’s easy to use, intuitive, and it’s better than what you have now.
So, begin by creating an empty Control Application, and set up the layout however you like. Expand the empty Fields list, and add a “Select” field named Report_ID, use “Automatic” lookup, use a blank Title, and select refresh when selection changes. The following SQL is probably adequate if a Parameters_Mask of 0x100 means Admins-Only:
SELECT _id, Report_Name
FROM T_Reports_List
WHERE (Parameters_Mask & 0x100) = '0'
ORDER BY Report_Name
Add a Text type field named Description.
Add an Image (file name) field named Preview, and wonder why there isn’t just an “Image Display” field type.
Add a Date field named Start_Date and provide a popup calendar.
Add a Date field named End_Date and provide a popup calendar.
Add a Text field named Employee.
Add a Select field named Output, with Manual Lookup, and provide the following report output types:
[INDENT]Adobe PDF with value “pdf”
Microsoft Excel with value “xls”[/INDENT]
Now click Toolbar settings and change the OK button label to “Run”.
We want to hide report parameters unless a report uses them. So let’s expand the Events. Click the onScriptInit event, and enter:
sc_field_display({Start_Date}, off);
sc_field_display({End_Date}, off);
sc_field_display({Employee}, off);
When a user selects a report, we have opted to refresh the screen and we can use that to enable or disable the display of report parameters, so click on the onRefresh event and plug in the code:
sc_lookup(rs, "SELECT Parameters_Mask,Report_XML_URL,Report_Script_Path,Report_SQL,Report_Preview FROM T_Reports_List WHERE _id = '".{Report_ID}."'");
if( count({rs}) != 0 ) {
$mask = {rs[0][0]};
if( ($mask & 0x01) == 0x01 ) {
sc_field_display({Start_Date}, on);
} else {
sc_field_display({Start_Date}, off);
}
if( ($mask & 0x02) == 0x02 ) {
sc_field_display({End_Date}, on);
} else {
sc_field_display({End_Date}, off);
}
if( ($mask & 0x08) == 0x08 ) {
sc_field_display({Employee}, on);
} else {
sc_field_display({Employee}, off);
}
$xml_file = "/var/www/html/file/doc/reports/".{rs[0][1]};
{Preview} = {rs[0][4]};
sc_set_global($xml_file);
$report_php = {rs[0][2]};
sc_set_global($report_php);
}
Finally, we want to run the report and we’ll do that from the onValidate event which is fired by the user clicking the app’s “OK” button which we labelled “Run”. Click the onValidate event and enter the following code:
$OUTPUT = {Output};
$cmd = "/opt/jasperstarter/bin/jreport ";
$db_name = "[B]MyDatabaseName[/B]";
$cmd .= " -t ".$OUTPUT." -j \"".[xml_file]."\" -d ".$db_name;
sc_lookup(rs, "SELECT Parameters_Mask FROM T_Reports_List WHERE _id = '".{Report_ID}."'");
if( count({rs}) != 0 ) {
$mask = {rs[0][0]};
if( ($mask & 0x01) == 0x01 ) {
$cmd .= " -S ".$START_DATE;
}
if( ($mask & 0x02) == 0x02 ) {
$cmd .= " -E ".$END_DATE;
}
if( ($mask & 0x08) == 0x08 ) {
$cmd .= " -T \"".$EMPLOYEE."\"";
}
}
sc_set_global($cmd);
sc_redir([report_php], "_blank");
You probably guessed that the onValidate event is building a command line based on the defined report’s Parameters_Mask field values. Obviously, you would want to add some sanity checks on the user input. This is just a simple example.
We’ll be calling a shell script, “jreport”, that you don’t have yet. That script will be provided in Part 4, because some explanation about why it is even needed is helpful, and because it adds the ability to email reports.
Now, click Application->Global Variables. Set the displayed global variables to “Out”.
For the moment, we should pause and test what we have so far. But we need that script that drives our report.
Edit your file pointed-to by Report_Script_Path, and enter the following PHP code:
<?php
session_start();
$cmd = $_SESSION['cmd'];
//$output = shell_exec($cmd);
echo $cmd;
?>
There may be some debugging needed, but this is really simple. If I left something out, let me know and I’ll correct it. Otherwise, it’s probably just a typo, so search for typing errors and you’ll learn more.
We now have an application to upload and define a Jasper report, and an application to run a report which prompts us for only the parameters that the selected report accepts (and requires!). When we run the user application, it displays a command line with the correct Jasper report file name, the correct output format, and all of the parameter values. String values are properly quoted.
Part 4 will include and cover the jreport shell script, we’ll discuss the above onValidate event some more, and explain how the above control application behaves within the scope of a ScriptCase Menu Application.
Ciao!
