Report Objects give the PHP programmer the ability to embed QLR Manager reports into the body of their web pages. These report objects can be tabular report data, a chart, or both depending upon the layout that was applied. ReportObject.php is a PHP Class file that is used to generate a report object. It provides a number of PHP functions that can be used to build and manipulate a report. Ultimately, a report body and set of Cascading Style Sheet (CSS) entries are returned.
The default is for a single report of all records found to be returned in the Report Object. "Report Blocks" can be specified by using the setStartRow() and setBlockSize() functions.
Additional functions in this class such as runQuery(), getDataRow(), and others, also provide the Programmer with basic database interface capabilities.
Input Controls can be used in conjunction with report objects to collect user input for
query variables. These input controls
can be created by logging onto the "User ID Admin and Tools" function
from the QLR Manager Connect panel and selecting
The functions getControl() and setControl() are provided to implement the controls in a PHP generated web page. Some controls are returned in a table to preserve formatting. This is the most expedient way of providing an input control to collect user input. However, this may not provide a control that fits well with the web page design. An alternative is for the Author to create their own control and pass the selected value(s) to the report object using the setQueryVar() function. This method provides unlimited flexibility in the design of the input control.
Whether using input controls that have been created in QLR Manager or a control designed by the Author of the web page, the method of implementation is the same.
| • | Create a web page that will display the input controls to collect the desired values. This is accomplished be creating an HTML form, which uses <form> </form> tagging. The key element of this form is the ACTION to process when the "Submit" button is clicked. It is the name of the PHP file that will be called to create the report object. A simple example:
<form name="varform" action="./custinv.php" method="post"> |
| This will create a simple input form: | |
Example #5 contains the code for creating an input control web page. |
|
| • | When the "Submit" button is clicked, it will execute a file called custinv.php. custinv.php should contain the PHP code (see examples below) to create a report object. custinv.php can use the customer number that is being passed to it via the HTML <form>. It's value can be accessed via PHP using the $_POST array entry of $_POST['customer']. The customer number can be supplied to a stored QLR Manager query using the setQueryVar() function. See Example #2 for an example of how to set query variables. |
Report Objects are only available in the Enterprise Edition of QLR Manager.
| 1 | A PHP session is started. |
| 2 | A session variable called 'inc_path' is defined. This variable identifies where the QLR Manager files are located in relation to the file that is requesting to create a report object. |
| 3 | Include the file ReportObject.php in the code. |
| 4 | A report object is created using database connection values that have authority to run the query to produce the report. |
| 5 | The query and layout used to produce the report are defined. |
| 6 | Optionally, values for query variables and changes to the layout can be made. |
| 7 | The report is created. |
| 8 | The report and CSS values are requested. |
Steps 4 through 7 are repeated for each report to be embedded into a page.
An example of a PHP generated page with embedded report objects is provided as a Sample web page. The Sample page source is also provided to illustrate how the report objects are defined. The following examples provide more information about how report objects can be customized to meet different needs.
Depending on how calls to report objects are nested inside functions, it may be necessary
to include a reference to a global variable called $mconn (for the QLR master connection) in
that function. If an error is encountered stating that the function
function myreportcreator() {
global $mconn;
(User code starts here)
}Example #1: This first example uses the basic steps to create a report from a stored query, owned by 'frank', named 'customer invoice'. The necessary statements are highlighted in blue.
<?php
// First create a PHP session
session_start();
// Define the relative path to the QLR Manager file directory
$_SESSION['inc_path'] ='../qlr/';
// Include the Report Object file
include ('../qlr/ReportObject.php');
// Create a new report object
// Provide the host, database associated to the query to be run, userid and password
// The user id needs authority to execute the query
$rptObj = new ReportObject('localhost', 'qlrmanager', 'jake', 'myd0g22');
// Define the query owner and query name, followed by the layout owner and name
$okay = $rptObj->defineReport('frank','customer invoice','frank','invoice details');
// Create the report table
$rptObj->createReport();
// HTML for the web page
echo '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">'."\n";
echo '<html><head>'."\n";
echo '<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">'."\n";
echo '<meta http-equiv="Content-Script-Type" content="text/javascript">'."\n";
echo '<meta http-equiv="Content-Style-Type" content="text/css">'."\n";
echo '</head><body>'."\n";
// Get both the CSS entries and the report table
echo $rptObj->getOutput();
echo '</body></html>'."\n";
?>
Example #2: This second example uses a few additional functions. It shows how to set the values for query variables that may be present in the query, and retrieves the CSS entries separately from the report table data. Also, the row count is requested to determine what to display when the query does not produce any records.
<?php
// First create a PHP session
session_start();
// Define the relative path to the QLR Manager file directory
$_SESSION['inc_path'] ='../qlr/';
// Include the Report Object file
include ('../qlr/ReportObject.php');
// Create a new report object
// Provide the host, database associated to the query to be run, userid and password
// The user id needs authority to execute the query
$rptObj = new ReportObject('localhost', 'qlrmanager', 'jake', 'myd0g22');
// Define the query owner and query name, followed by the layout owner and name
$okay = $rptObj->defineReport('frank','customer invoice','frank','invoice details');
// Set query variable values
// Notice how this is done AFTER defineReport() and BEFORE createReport()
$rptObj->setQueryVar('customer_id', $custID);
$rptObj->setQueryVar('invoice_year', $year);
// Create the report table
$rptObj->createReport();
// HTML for the web page
echo '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">'."\n";
echo '<html><head>'."\n";
echo '<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">'."\n";
echo '<meta http-equiv="Content-Script-Type" content="text/javascript">'."\n";
echo '<meta http-equiv="Content-Style-Type" content="text/css">'."\n";
echo '<style type="text/css"><!--'."\n";
// Get the CSS values and place them in the HTML <head>
echo $rptObj->getCSSvalues();
echo '//--></style>'."\n";
echo '</head><body>'."\n";
if ($rptObj->getRecordCount() > 0) {
echo $rptObj->getReport();
}
else {
echo 'No invoice records found for this customer number';
}
echo '</body></html>'."\n";
?>
Example #3: This is an example of a page containing more than one report object. For each report, a separate report object is created. Notice how the Cascading Style Sheet (CSS) entries are retrieved separately from the report and included in the HTML Head section. When creating multiple reports that require multiple sets of CSS entries, this method is safer for supporting older browsers, such as Netscape 4.x.
<?php
// First create a PHP session
session_start();
// Define the relative path to the QLR Manager file directory
$_SESSION['inc_path'] ='../qlr/';
// Include the Report Object file
include ('../qlr/ReportObject.php');
// Create a new report object
// Provide the host, database associated to the query to be run, userid and password
// The user id needs authority to execute the query
$rptObj1 = new ReportObject('localhost', 'inventory', 'jake', 'myd0g22');
$rptObj2 = new ReportObject('localhost', 'customer', 'jake', 'myd0g22');
// Define the query owner and query name, followed by the layout owner and name
$okay = $rptObj1->defineReport('jake','low stock','jake','low stock details');
$okay = $rptObj2->defineReport('frank','customer invoice','frank','invoice details');
// Create the report tables
$rptObj1->createReport();
$rptObj2->createReport();
// HTML for the web page
echo '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">'."\n";
echo '<html><head>'."\n";
echo '<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">'."\n";
echo '<meta http-equiv="Content-Script-Type" content="text/javascript">'."\n";
echo '<meta http-equiv="Content-Style-Type" content="text/css">'."\n";
// Get the CSS values to be placed in the <head> section
echo '<style type="text/css"><!--'."\n";
echo $rptObj1->getCSSvalues();
echo $rptObj2->getCSSvalues();
echo '//--></style>'."\n";
echo '</head><body>'."\n";
// Get the report table data
echo 'Here are the low stock items:';
echo $rptObj1->getReport();
echo 'And here is the customer invoice information:';
echo $rptObj2->getReport();
echo '</body></html>'."\n";
?>
Example #4: This is an example of creating a Microsoft Excel file for download from a report. When creating a download file, no characters can be sent to the browser prior to executing getOutputFile().
<?php
// First create a PHP session
session_start();
// Define the relative path to the QLR Manager file directory
$_SESSION['inc_path'] ='../qlr/';
// Include the Report Object file
include ('../qlr/ReportObject.php');
// Create a new report object
// Provide the host, database associated to the query to be run, userid and password
// The user id needs authority to execute the query
$rptObj = new ReportObject('localhost', 'qlrmanager', 'jake', 'myd0g22');
// Define the query owner and query name, followed by the layout owner and name
$okay = $rptObj->defineReport('frank','customer invoice','frank','invoice details');
// Create the report table
$rptObj->createReport();
// Create an xls output file called invoice that contains the report column headings
// See getOutputFile() for valid output types
$rptObj->getOutputFile('xls', 'invoice');
?>
Example #5: This is an example of creating a page to collect input variables to be passed to a page that will create a report object.
<?php
// First create a PHP session
session_start();
// Define the relative path to the QLR Manager file directory
$_SESSION['inc_path'] ='../qlr/';
// Include the Report Object file
include ('../qlr/ReportObject.php');
// Create a new report object
// Provide the host, database associated to the query to be run, userid and password
// The user id needs authority to execute the query
$rptObj = new ReportObject('localhost', 'qlrmanager', 'jake', 'myd0g22');
// Define the input controls that will be called within the HTML page down below
$controls = array('customer','end_date','start_date');
$rptObj->setControl($controls);
// Set the default value of the customer input control
$rptObj->setControlValue('customer','default_val','12337');
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html><head>
<title>Customer Invoice Selection</title>
<style type="text/css"><!--
body,td,input,select { font-size:13px; font-family:Arial,Helvetica,sans-serif;
color:#000000; font-weight:normal }
.hdr { font-size:16px; font-family:Arial,Helvetica,sans-serif;
color:#000000; font-weight:bold }
.sub { font-size:11px; font-family:Arial,Helvetica,sans-serif;
color:#333333 }
.btn { font-size:12px; font-family:Verdana,Tahoma,sans-serif; color:#F0F0F0;
font-weight:bold; background-color:#4B7396; border-color:#8CBED7 }
//--></style>
// This next section is in support of popup calendar controls
<script language="javascript" src="./calendar/calendar.js" type="text/javascript"></script>
<style type="text/css"><!--
#calWin {
position:absolute;
visibility:hidden;
left:-500px;
top:-500px;
z-index:10;
}
.calBtn {
width:26px;
height:20px;
background:#4B7396 url(./calendar/calbtn.gif) no-repeat center; border-color:#8CBED7;
}
.datein {
width:100px;
}
//--></style>
</head><body bgcolor="#f6f6f6">
<form name="varsForm" action="./invoice.php" method="post">
<center>
<br />
<br />
<table border="0" cellpadding="4" cellspacing="0"><tr>
<td colspan="3" align="center" class="hdr">Please select customer and date range for invoices</td>
</tr><tr>
<td> </td>
</tr><tr>
<td>Select a customer:</td>
<td><?php echo $rptObj->getControl('customer'); ?></td>
</tr><tr>
<td>Select starting date:</td>
<td><?php echo $rptObj->getControl('start_date'); ?></td>
<td class="sub">(a date early in 2002 should be used)</td>
</tr><tr>
<td>Select ending date:</td>
<td><?php echo $rptObj->getControl('end_date'); ?></td>
<td class="sub">(a date late in 2004 should be used)</td>
</tr></table>
<br />
<input type="submit" value=" Submit " class="btn">
</center>
</form>
// This next line is to support popup calendar controls (keep on one line)
<iframe id="calWin" name="calWin" src="./calendar/calendar.html" width="192" height="187"
marginwidth="0" marginheight="0" hspace="0" vspace="0" frameborder="0" scrolling="no"></iframe>
</body></html>
Example #6: This is an example of using a Report Object to create the contents for a report file. A report is generated and then, using php, a file is written.
<?php
// First create a PHP session
session_start();
// Define the relative path to the QLR Manager file directory
$_SESSION['inc_path'] ='../qlr/';
// Include the Report Object file
include ('../qlr/ReportObject.php');
// Create a new report object
// Provide the host, database associated to the query to be run, userid and password
// The user id needs authority to execute the query
$rptObj = new ReportObject('localhost', 'qlrmanager', 'serge', 'i8mylunch');
// Define the query owner and query name, followed by the layout owner and name
$okay = $rptObj->defineReport('frank','sales history','frank','history layout');
// Create the report table
$rptObj->createReport();
$content = $rptObj->getOutput();
// add windows line breaks for making source prettier if on a windows server
$content = str_replace("\n","\r\n",$content);
// note: do not use the QLR /reports directory, as files get purged automatically
// after 12 hours once another user logs in
$filename = '../userReports/myreport.html';
if (!$handle = fopen($filename, 'w')) {
echo "Cannot open file ($filename)";
exit;
}
// Write content to the opened file.
if (fwrite($handle, $content) === FALSE) {
echo "Cannot write to file ($filename)";
exit;
}
echo "Success, wrote content to file ($filename)";
fclose($handle);
?>
addAttachment:
null addAttachment(file_content, file_name)
This function allows for content to be added as a file attachment to an e-mail. The e-mail is sent using the sendMail() function. Multiple files can be attached by calling this function more than once.
Example:
// Create the report table
$rptObj->createReport();
// get ready to send an e-mail
$to = 'jake@gmail.com';
$cc = '';
$bcc = '';
$from = 'greg@iris.com';
$subject = 'Inventory report';
$body = 'Please find attached the latest inventory report.';
// This command gets the report data formatted for Excel
$content = $rptObj->getFileData('xlsf');
// Now add the attachment
$rptObj->addAttachment($content,'inventory.xls');
// Note: Once sendMail is executed, it resets the current attachments to empty.
$rptObj->sendMail($to, $cc, $bcc, $from, $subject, $body);
See sendMail() for information about sending an e-mail. In addition, getFileData() is used to retrieve the current report in a particular file format.
void cleanUpFiles()
This calls the QLR Manager clean up routine. It will delete QLR Manager pages that are
over 12 hours old. This is intended for installations that primarily run Report Objects and rarely
use the traditional QLR Manager interface. The traditional interface invokes this routine once a
day with the first log on through the
$rptObj->cleanUpFiles();
string createReport()
Creates the report based on the query and layout parameters provided in defineReport(). Returns either "report" when a report table is successfully created or "info" when an informational message is generated instead of a report.
Example:$outputType = $rptObj->createReport();
boolean defineReport(string query_owner, string query_name,
string layout_owner, string layout_name)
Defines the query and layout to be used as the basis for the report. Alternately, if the
query_owner is defined as '*query_text*', a query string can be entered as the query_name as
illustrated in
Note: The query and layout specified in defineReport() must either be owned by the ID that was provided when the new ReportObject() was created, or the query and layout must have been saved to be SHARED with other users.
Example #1:
$okay = $rptObj->defineReport('frank', 'invoice report',
'frank', 'invoice summary');
$query = 'select custID, first_name, last_name from customer';
$okay = $rptObj->defineReport('*query_text*', $query, '', '');
getControl:
string getControl(string control_name)
Returns an HTML formatted string for the specified Input Control. If the Input Control is not found, a "not found" message is returned. The HTML "name" is the Input Control name. Some controls are returned in a table to preserve formatting. If the Input Control is a date input with the popup calendar, there are some CSS Classes, a JS file import and an <iframe> element that must be included in the web page. Please see Popup calendar for more information.
Note: This function should be called after setControl() has initialized the input control(s) for use.
Example:
echo $rptObj->getControl('custlist');Would return:
<select name="custlist" size="1"> <option value="17">Asher</option> <option value="15">Bagadonuts</option> <option value="11">Benton</option> <option value="8">Billings</option> </select>
| • | [Select all] [Deselect all] [Reverse] for checkbox groups and multi-select lists. |
| • | Character limit for text areas. |
| • | Selection limit for checkbox groups and multi-select lists. |
| • | Regular Expression validations and string replacement for text inputs and text areas. |
For features such as Character/Selection limit and Regular Expression validations, it is also wise to validate the submitted data on the server. These functions on the client side rely on JavaScript and it is possible to disable JavaScript in the browser which would disable these limits and validations.
getCSSvalues:
string getCSSvalues()
Returns the Cascading Style Sheet (CSS) values needed to format the report table.
Note: It does not return the opening and closing <style> tags.
Example:$css = $rptObj->getCSSvalues();
array getDataRow()
Returns an associative array of the values in the current result set and then points to the next record in the result set.
Example:
$okay = $rptObj->runQuery('select partnum,boh from inventory where boh>10000');
if ($okay) {
echo 'Record count: '.$rptObj->getRecordCount()."<br />\n";
for ($i=0; $i < $rptObj->getRecordCount(); $i++) {
$row = $rptObj->getDataRow();
while (list($key,$value) = each ($row)) {
$key = strtolower($key);
echo " $key = $value";
}
echo "<br />";
}
}
else echo 'Error: '.$rptObj->getErrorNum().' '.$rptObj->getErrorMsg();
getErrorMsg:
string getErrorMsg()
Returns the database engine error message when runQuery() returns false.
Example:
$okay = $rptObj->runQuery('select * from inventory where boh>10000');
if (!$okay) {
echo 'Error: '.$rptObj->getErrorNum().' '.$rptObj->getErrorMsg();
}
getErrorNum:
integer getErrorNum()
Returns the database engine error number when runQuery() returns false.
Example:
$okay = $rptObj->runQuery('select * from inventory where boh>10000');
if (!$okay) {
echo 'Error: '.$rptObj->getErrorNum().' '.$rptObj->getErrorMsg();
}
getFieldName:
string getFieldName(integer field_number)
Returns the field name from a result set associated to a column number. The first column in a result set is equal to zero.
Example:
$okay = $rptObj->runQuery('select * from inventory where boh>10000');
if ($okay) {
echo 'The second column is called: '.$rptObj->getFieldName(1)."<br />\n";
}
else echo 'Error: '.$rptObj->getErrorNum().' '.$rptObj->getErrorMsg();
getFileData:
string getFileData(output_type)
This returns the current report data formatted in a particular file format.
Valid output types are as follows:
| Output Type | Description | |
| csv | Comma separated value file. | |
| csv+ | Comma separated value file with report column headers. | |
| csvr | Comma separated value file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| csvr+ | Comma separated value file with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| doc | Microsoft Word Doc file. | |
| html | HTML formatted file. | |
| html+ | HTML formatted file that can be imported into MS Excel or Word. | |
| txt | Semi-colon separated value file. | |
| txt+ | Semi-colon separated value file with report column headers. | |
| txtr | Semi-colon separated value file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| txtr+ | Semi-colon separated value file with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| xls | Microsoft Excel file (data only). | |
| xls+ | Microsoft Excel file (data only) with report column headers. | |
| xlsr | Microsoft Excel file (data only) produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| xlsr+ | Microsoft Excel file (data only) with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| xlsf | Microsoft Excel file formatted with coloring, etc. | |
| xml | XML formatted file. | |
| xmlr | XML formatted file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. |
Example:
// Create the report table
$rptObj->createReport();
// This command gets the report data formatted for Excel
$content = $rptObj->getFileData('xlsf');
// Now add the attachment
$rptObj->addAttachment($content,'inventory.xls');
See addAttachment() for information about adding an attached file to an e-mail.
getInfo:
string getInfo()
Returns the informational message text generated when createReport() returns "info".
Example:$msg = $rptObj->getInfo();
getOutput:
string getOutput()
Returns both the Cascading Style Sheet values (enclosed in <style> tags) and the report table when createReport() returns "report".
Example:$output = $rptObj->getOutput();
(browser download interface) getOutputFile(string output_type, string file_name)
Creates an output file for user download as defined by the output type. The file extension is automatically appended to the file name based on the output type. This function is called after createReport().
Valid output types are as follows:
| Output Type | Description | |
| csv | Comma separated value file. | |
| csv+ | Comma separated value file with report column headers. | |
| csvr | Comma separated value file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| csvr+ | Comma separated value file with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| doc | Microsoft Word Doc file. | |
| html | HTML formatted file. | |
| html+ | HTML formatted file that can be imported into MS Excel or Word. | |
| txt | Semi-colon separated value file. | |
| txt+ | Semi-colon separated value file with report column headers. | |
| txtr | Semi-colon separated value file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| txtr+ | Semi-colon separated value file with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| xls | Microsoft Excel file (data only). | |
| xls+ | Microsoft Excel file (data only) with report column headers. | |
| xlsr | Microsoft Excel file (data only) produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| xlsr+ | Microsoft Excel file (data only) with report column headers produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. | |
| xlsf | Microsoft Excel file formatted with coloring, etc. | |
| xml | XML formatted file. | |
| xmlr | XML formatted file produced from the raw query results. This is much faster, but bypasses applying any Layout data modifications. |
Example:
$rptObj->getOutputFile('csv', 'invoices');
Note: It is very important that no information is sent to the browser, not even blank characters, prior to calling this function.
getPDFfile:
PDF data stream getPDFfile(integer left_margin,
integer right_margin,
integer top_margin,
string page_orientation, ('p' for portrait, 'l' for landscape)
string paper size, ('A3','A4','A5','Letter' or 'Legal')
integer page_width, (600, 800 or 1024)
string page_content) (an empty string '', or custom content)
This function supports the creation of a PDF file stream sent to the browser. Since it does send data back to the browser, no output should be sent from the server prior to this function returning data. The "page_orientation" is determined by examining the first letter of the value provided and is case insensitive. The "paper_size" arguments are also case insensitive. As the "page_width" value is increased, more information can be fit onto a page, similar to how the screen resolution setting works on a computer display.
There are two basic ways to provide content to this function. The first is by executing the createReport() function prior to calling getPDFfile and entering '' (empty quotes) as the "page_content" value. The second way is to provide customized content for the entire PDF output stream via the "page_content" value. Following are examples of the two methods.
$rptObj = new ReportObject('localhost', 'baitshop', 'frank', 'my02dog');
// Define the query owner and query name, followed by the layout owner and name
$okay = $rptObj->defineReport('frank','sales','frank','sales summary');
// Create the report
$rptObj->createReport();
// The following will create a PDF stream based on the report created in
// createReport(). Notice how the last argument for page_content is ''.
$rptObj->getPDFfile(10,10,5,5,'p','letter',800,'');
$rptObj = new ReportObject('localhost', 'baitshop', 'frank', 'my02dog');
// Define the query owner and query name, followed by the layout owner and name
$okay = $rptObj->defineReport('frank','invoice','frank','invoice_layout');
// Create a report body
$rptObj->createReport();
// Start some custom content
$content = 'Dear Mary,<br /><br />Here is your invoice<br />';
// Optionally embed a report into the custom content
$content .= '<center>';
$content .= $rptObj->getStyledReport();
$content .= '</center>';
// Finish the custom content
$content .= '<br /><Sincerely,<br /><<br />Frank';
// The following will create a PDF stream based on the custom content.
$rptObj->getPDFfile(15,15,20,20,'portrait','a4',1024,$content);
getQuery:
string getQuery()
Returns the query string that was executed by createReport(). This is helpful for debugging, especially when a query includes query variable replacement.
Example:
$okay = $rptObj->runQuery('select * from inventory where boh>10000');
if (!$okay) {
echo 'Query text: '.$rptObj->getQuery();
}
getRecordCount:
integer getRecordCount()
Returns the number of rows in the report that is generated after createReport() is executed.
Example:$count = $rptObj->getRecordCount();
getReport:
string getReport()
Returns the report HTML table generated when createReport() returns "report".
Example:$reportBody = $rptObj->getReport();
getSQLdate:
string getSQLdate(string raw_date_value, string input_control_name)
Returns a date string in the database yyyy-mm-dd format by converting dates that are in a different format for a given Input Control.
Example:
$sql_date = $rptObj->getSQLdate('12-31-2005', 'first_date');
getStyledReport:
string getStyledReport()
Returns the report HTML table generated when createReport() returns "report". The CSS entries are created as in-line style="(values)" entries as part of each report table cell. This provides formatted output without having to accommodate CSS entries. The drawback is that it returns more data.
Example:$reportBody = $rptObj->getStyledReport();
object ReportObject( string host, string database_name,
string userid, string password)
Creates an object of type ReportObject and establishes database connection parameters. When using QLR Manager with Oracle, the database name is be entered as '' (empty single quotes).
Example:
$rptObj = new ReportObject('localhost', 'custdata', 'jake', 'myd0g22');
replaceNameChars:
string replaceNameChars(string input_control_name)
Returns a string that converts special characters back to their original form in an input control name.
When input controls are created as report objects, the name of the input control is used as the HTML form element NAME. The name applied to the input control when saved in QLR Manager may contain invalid characters that cannot be used in the HTML form element NAME attribute. Certain characters must be converted to produce valid HTML. For example, an input control with the name of "first.date(yyyy-mm-dd)" is converted by QLR Manager to "first_pe_date_lp_yyyy_ds_mm_ds_dd_rp". replaceNameChars will convert the QLR use of the form element NAME attribute back to the original input control name.
Example:
$name = $rptObj->replaceNameChars('first_pe_date_lp_yyyy_ds_mm_ds_dd_rp');
boolean runQuery(string query_text)
Executes an SQL query string using the connect values as provided when the ReportObject() was created.
Examples:
$rptObj->runQuery('select * from customer');
$rptObj->runQuery('update inventory set boh=495 where partnum=65099');
boolean saveOutputFile(string output_type, string path/file_name)
Saves a file to disk as defined by the output type. The path can be either relative or absolute. The file extension is automatically appended to the file name based on the output type. This function is called after createReport(). Returns TRUE if the file is successfully written.
Valid output types are as follows:
| Output Type | Description | |
| csv | Comma separated value file. | |
| csv+ | Comma separated value file with report column headers. | |
| doc | Microsoft Word Doc file. | |
| xls | Microsoft Excel file (data only). | |
| xls+ | Microsoft Excel file (data only) with report column headers. | |
| xlsf | Microsoft Excel file formatted with coloring, etc. | |
| html | HTML formatted file. | |
| html+ | HTML formatted file that can be imported into MS Excel or Word. |
Example:
$okay = $rptObj->saveOutputFile('doc', '../myfiles/invoices');
sendMail:
boolean sendMail(string to_addresses, (multiple address separated by commas)
string carbon_copy_addresses, (multiple address separated by commas)
string blind_copy_addresses, (multiple address separated by commas)
string from_address,
string email_subject,
string email_content)
This function supports HTML formatted email distribution. It requires that you have a Simple Mail Transfer Protocol (SMTP) server running. This returns true if the email content is successfully sent to the SMTP server, but it does not know if the STMP server has successfully sent the message.
Example:// Create the email body $Body = '<p>Fred, here is your account activity:</p>'; $Body .= $rptObj->getStyledReport(); $Body .= '<p>Sincerely,<br />Frank</p>'; $To = 'fred@yahoo.com'; $Cc = ''; $Bcc = 'jake@comcast.net, tom@hotmail.com'; $From = 'john@tekoutsight.com'; $Subject = 'Account activity'; // Returns true or false based on getting mail to SMTP server. $sent = $rptObj->sendMail($To,$Cc,$Bcc,$From,$Subject,$Body);
See addAttachment() for information about adding an attached file to an e-mail.
setBlockSize:
void setBlockSize(integer number_of_rows_to_display)
Allows the User to specify the number of rows of data to return in a Report Object. It is used in conjunction with setStartRow() to create Report Objects that contain ranges of report row output.
This function should be used after defineReport() is called and before createReport() is called.
Examples:$rptObj->setStartRow(1001); $rptObj->setBlockSize(500);
setColumnAttribute:
void setColumnAttribute(integer column_number, string field_name, string value)
Allows the User to set the attributes associated with given column within a report. The first column is equal to 1. These are the same fields as found in the Report Columns section of the Layout panel. Generally this function does not need to be used. It is much simpler to create and save a Layout and then reference the Layout in defineReport().
The allowable field_names are column_title, column_align, column_action, format, decimal_places, display width and evaluate. Please view the allowable choices for these fields in the Report Columns section of the Layout panel.
This function should be used after defineReport() is called and before createReport() is called.
Examples:$rptObj->setColumnAttribute(1, 'column_title','customer<br />number'); $rptObj->setColumnAttribute(1, 'column_align','Left');
setControl:
void setControl(string or array of control_names)
This function initializes the Input Controls to be used in a page. A single control can be specified by providing a string argument. If more than one control is to be used in a page, an array of string arguments should be provided.
Examples:
$rptObj->setControl('custlist');
$controls = array('custlist','startDate','endDate','region');
$rptObj->setControl($controls);
setControlValue:
void setControlValue(string control_name, string field_name, mixed value)
This function allows for certain input control characteristics to be overriden and set at the time of use.
The control_name references a control that was defined in SetControl().
The field_name can be one of the following. Different fields can be set by issuing this function more than once.
| Field | Description | |
| default_val: | This allows for the default value of a field to be set. If the control is a checkbox set or multiple select list, an array of values can be used to set multiple values. | |
| error_msg: | The error message to display when a regular expression check fails. | |
| max_size: | the character width of a text input field or text area, or the number of columns of checkboxes or radio buttons when a grid arrangement is specified. | |
| reg_exp: | A regular expression string to perform data validation. | |
| select_limit: | The number of characters that can be entered into a text field or textarea, or the number of selections that can be chosen from a checkbox set or multiple select list. | |
| text_rows: | The number of rows to display in a textarea or the height of a select list. |
Examples:
$rptObj->setControlValue('customer number','default_val', 230099);
$rptObj->setControlValue('customer number','select_limit', 5);
$names = array('Fred','Nick','Kadir','Anitha');
$rptObj->setControlValue('first names','default_val',$names);
setPageAttribute:
void setPageAttribute(string field_name, string value)
Allows the User to set the attributes associated to the overall page appearance of a report. These are the same fields as found in all the sections of the Layout panel, except for the setColumnAttribute() which corresponds to the Report Columns section of the Layout. Generally this function does not need to be used. It is much simpler to create and save a Layout and then reference the Layout in defineReport().
The allowable field_names for this function can be found by looking at the Table info (from the Query panel) for the table qlr_layout_master.
This function should be used after defineReport() is called and before createReport() is called.
Examples:
$rptObj->setPageAttribute('title_text','Customer Invoice report');
$rptObj->setPageAttribute('title_font_face','Arial,Helvetica,sans-serif');
setPDFdestination:
void setPDFdestination(integer 1 (default browser) 2 file)
Allows the User to specify that PDF output should be sent to a file.
This function should be used before getPDFfile() is called. The file will be created in the QLR Manager /reports directory. Please note that when saving to a file, the process will cause the user's browser window to go blank, so some sort of HTML should be sent after the creation of the file.
Examples:
$rptObj->setPDFdestination(2);
$rptObj->setPDFfilename('juneSales');
setPDFfiledir:
void setPDFfiledir(String file_directory)
Allows the User to specify the directory of where the PDF file will be created. This can be used when creating a file for downloading or when saving the PDF output to a file. The directory is relative to where the executing report object is located. If not specified, the QLR Manager reports directory is used.
Examples:
$rptObj->setPDFfiledir('../../june_reports/');
$rptObj->setPDFfilename('sales');
setPDFfilename:
void setPDFfilename(String file_name)
Allows the User to specify the name of the PDF file that is being created. This can be used when creating a file for downloading or when saving the PDF output to a file. QLR Manager will automatically add the .pdf file extension.
Examples:
$rptObj->setPDFfilename('juneSales');
$rptObj->setPDFfilename('invoice224');
setQueryVar:
void setQueryVar(string variable_name, string value)
Allows the User to set the value of a query variable when present. It is not necessary to use the ** prefix, or the [brackets] used in QLR Manager version 5, with the variable_name.
This function should be used after defineReport() is called and before createReport() is called.
Examples:
$rptObj->setQueryVar('customer_id','73490');
$rptObj->setQueryVar('year','2004');
setStartRow:
void setStartRow(integer first_row_to_display)
Allows the User to specify the first row number of data to return in a Report Object. It is used in conjunction with setBlockSize() to create Report Objects that contain ranges of report row output. The first report row is 1.
This function should be used after defineReport() is called and before createReport() is called.
Examples:$rptObj->setStartRow(1001); $rptObj->setBlockSize(500);
setWatermark:
void setWatermark(text)
Allows the User to specify text that will be displayed as transparent text rendered diagonally across the first page of the generated PDF file.
Example:
$rptObj->setWatermark('Draft');
setXMLRecordTag:
void setXMLRecordTag(text)
Allows the User to set the record level tag for XML output. The default value is "record".
Example:
$rptObj->setXMLRecordTag('customer');
setXMLRootTag:
void setXMLRootTag(text)
Allows the User to set the root level tag for XML output. The default value is "data".
Example:
$rptObj->setXMLRootTag('allcustomers');
suppressSelectAll:
void suppressSelectAll(void)
This function will suppress the addition of the [Select all] [Deselect all] [Reverse] controls that appear on the top of checkbox sets and multiple select list input controls.
$rptObj->suppressSelectAll();
A popup calendar is provided for use with input controls that require date selections.
The calendar is automatically included within QLR Manager if an input control is defined using
one of the "Calendar popup" selections available under Control type. These
input controls can be created by clicking the Edit Input Controls link from either
the
Note: All the path references below must be adjusted to reference the calendar files relative to web page PHP directory. All the files required to support the calendar input control are located in the /qlr_manager/calendar directory. It may be more convenient to copy this directory and its contents to a location under the PHP directory to more easily reference the path. The instructions below assume this path configuration. Be sure to read the comments in the top of calendar.html for available configuration options.
CSS classes:
There are a few CSS classes that should included in the <head> of the web page:| #calWin: | This class is required to define the <iframe> that will hold the calendar. |
| .calBtn: | This defines the size, color and background image for the button used to launch the calendar. |
| .datein: | Since the rendering width of text inputs varies greatly among different browsers, this class is included within the <input type="text" class="datein" size="12" produced with the input control. Using the width value along with the size attribute produces a consistent width across most browsers. |
<style type="text/css"><!--
#calWin {
position:absolute;
visibility:hidden;
left:-500px;
top:-500px;
z-index:10;
}
.calBtn {
width:26px;
height:20px;
background:ButtonFace url(./calendar/calbtn.gif) no-repeat center;
}
.datein {
width:100px;
}
//--></style>
JS file import:
A JS file must be imported that will launch the calendar and position it immediately below the text input it is associated with. The following should be placed in the <head> of the web page following the <style> declarations:
<script language="javascript" src="./calendar/calendar.js" type="text/javascript"></script>
Calendar html file:
The main component of the calendar is the HTML file. For most modern browsers, this HTML is loaded into an <iframe> that must be defined in the web page. For older browsers that do not support the <iframe>, such as Netscape 4.x, the calendar is launched in a popup window. Include the following <iframe> element in the bottom of the web page before the closing <body> tag:
<iframe id="calWin" name="calWin" src="./calendar/calendar.html" width="192" height="187"
marginwidth="0" marginheight="0" hspace="0" vspace="0" frameborder="0" scrolling="no"></iframe>
Keep the <iframe> tagging on one line.
Button to launch calendar:
When using a "Calendar popup" input control defined within QLR Manager, the text input and button using the "calbtn.gif" is returned in a table:
<table border="0" cellspacing="0" cellpadding="0"><tr>
<td><input type="text" size="12" maxlength="10" name="begin_date" value="" class="datein"></td>
<td><input type="button" value="" alt="Calendar" class="calBtn"
onMouseUp="openCalendar(event,0,'varsForm','Begin_date','mmddyyyy','/','Begin date',1);return false"></td>
</tr></table>
Since some older browsers do not support the CSS to include an image on a form button (i.e. Netscape 4.x), a slightly different approach is used and automatically generated by QLR Manager when a calendar input control is used with report objects. Instead of a button adjacent to the text input, the "calBtn.gif" is enclosed in a <href> with the following HTML returned:
<table border="0" cellspacing="0" cellpadding="0"><tr>
<TD><input type="text" size="12" maxlength="10" name="begin_date" value="" class="datein"></td>
<td><a href="./calendar/calendar.html"
onClick="openCalendar(event,0,'varsForm','r000Begin_date','mmddyyyy','/','',1);return false" title="Calendar">
<img src="calendar/calbtn.gif" alt="Calendar" align="top" border="0" /></a></td>
</tr></table>
This is the most expedient way of creating a date selection input control. However, this may not provide a control that fits well with the web page design. An alternative is for the Author to create their own control and pass the date value to the report object using the setQueryVar() function. This method provides more flexibility in the design of the control, but it does require that the arguments passed to the openCalendar() function be carefully constructed with the selected value passed to the setQueryVar() function as described in the Overview.
Color schemes:
When used as a date input control associated with report objects, all the colors that make up the popup calendar can be easily changed to allow the Author to coordinate the look of the calendar with their web site. There are commented instructions in the top of calendar.html that describe how to change the colors. Following are examples of 3 predefined color sets that can be selected:
| GRN | BRN | GRY | ||
 |
 |
 |