Report-generator

From VPOIDS Documentation

Report generator

The report generator in AFIDS combines a dataset with a presentation template to create a report that can be displayed on the screen, printed (in PDF format), or sent by email.

Reports are defined in an XML file.

The dataset can be specified as a SQL command, a call to a view, or a call to a MySql routine. The report definition allows you to define the parameters which are presented to the user for input, the values of which are passed to the database object.

The presentation is defined in an xsl stylesheet.

To generate the report, the system calls the reports.php file and passes the name of the element in the report definition xml which defines the report, as well as any other necessary parameters.

Call url

reports.php?

  • startPage = not currently used.
  • reportDef = the name of the report definition xml file
  • reportName = reportName in the report definition xml
  • action = If action is not specified, then the report generator queries the user for the input parameters specified in the report defintion. This is the normal behavior. In some cases, you may wish to use the report generator to create a one-click report to be generated for display, print or email without the user input, for example when the input parameters are passed from a script. In this case, set action=display, and the report generator will bypass the user input. Note that you must be sure to pass all required parameters as well, otherwise the report generator will return an error.
  • outtype = Default = html. Can also be print or email.
  • responseformat = the format of the output. The report generator supports the paging features of the jQuery datatables plugin, which enables large datasets to be loaded progressively rather than all at once.

Example:

http://afids/reports/reports.php?reportDef=report_specs.xml&reportName=medReleaseByPassenger&passenger_id=1000&action=display&outtype=print
http://afids.angelflightwest.org/reports/reports.php?reportDef=report_specs.xml&reportName=mission_report_feedback_trend&action=display&outtype=email&recipient_email=stephan@meliorist.com&send_by=email&subject=Mission%20Report%20Feedback%20Trends&attachment_in_body=1

Report definition XML elements

Report definition block

These values relate to the report overall.

  • reportName. This is the ID of the report, passed in the url to reports.php which determines which report is displayed
  • title. The title of the report as displayed to the user
  • category. The main category under which this report appears in the report menu.
  • category_2. The sub category under which this report appears in the report menu.
  • category_3. The sub-sub category under which this report appears in the report menu.
  • active. True or False. Indicates whether the report should be displayed in the report menu. Does not disable or disallow the report. Used when you want to be able to call the report from the url only.
  • description. The description of the report as displayed to the user. Can be used to explain the report, or indicate its purpose.
  • security. Permissions
  • dataset. Specifies the database object used to generate the dataset.
    • commandText. The text of the command. This can either be a complete SQL statement, a statement referencing a view, or a call to a stored procedure.
    • commandType adCmdText for SQL statements or views, adStoredProc for procedures.
    • commandTimeout (deprecated)

Input parameters

This block defines the inputs which are used to filter the report.

Each input parameter is defined in a inputParameter block:

  • paramName='startDate'
  • fieldName='mission_date'
  • fieldDesc='Application Date Start'
  • operator='ge'
  • required='no'
  • useroption='yes'
  • bottomoption='yes'
  • default='monthstart'
  • datatype='date'
  • fieldType='text'
  • fieldSize='10'


Output options

Defined in the outputformats element

type

Options are:

  • html for display to the screen
  • print for the creation of a PDF file
  • email for inclusion in the body of an email
  • download for the creation of a csv file.

description is the text of the link which appears in the report as the user option.

primary set to 'yes' if this is the default presentation, which is then generated when the user first submits the input parameters. Typically the html will be the default.

stylesheet

The style sheet is an xsl document which handles the presentation of the report data. If the report demands a customized presentation, you can create a custom stylesheet and define it here. (More on how to reference information from the dataset below).

If the report is a standard columnar display, you can use the standard report stylesheets:

  • afwHTMLReport.xsl
  • afwPrintReport.xsl
  • afwEmailReport.xsl
  • afwDownloadReport.xsl
  • jTableReport.xsl (for loading data into a jQuery datatable)

These stylesheets place each field in the dataset into a column, so create the dataset accordingly.

If your dataset does not match the fields you want displayed, or if you want another level of control over the format of teh columns, you can enumerate the columns in a "column" element in the outputformat element:

<column field='missionDate' label='Mission Date' width='10%' align='left' />
  • field is the name of the field in the dataset
  • label is the label given to the column header
  • width specifies the width of the column, either in percent or pixels.
  • align specifies the alignment, as in the alignment of an html table cell or row (left, right, middle)
  • dataType governs the formatting of the value: number, text, integer, currency
  • totalColumn specify yes if you want the values in this column to be totaled at the end of the report. In the dataset, be sure * that none of the values in this field are null, otherwise the result will be displayed as NaN.

The order of the column elements determines the order of the fields in the report.

newwindow set to 'yes' if you want this format to be displayed in a new browser window.

printTemplate

The print template refers to a block of defintions in the /lib/printTemplates.xml file. The template defines the margins of the page, default fonts, and other parameters about the how the PDF file is formatted. In addition, the printTemplate determines the header that's used, which is defined in the /lib/pdf_functions.php file. [Note: The header information should be extracted from this pdf_functions file into the print template, but there were some things I couldn't figure out how to define (or was too lazy to)].

templateOption determines the frame which is placed around the html display of the report. This used to be used for a print friendly version of the html display (no navigation), but this has since been replaced by the use of PDF for printing.

Example:

<outputformats>
   <outputformat type='print' description='Print' primary='yes' stylesheet='medRelease_print.xsl' newwindow='yes' printTemplate='letterhead'/>
   <outputformat type='email' description='Email' primary='no' stylesheet='medRelease_print.xsl' newwindow='yes' printTemplate='letterhead'/>
</outputformats>

<outputformats>
   <outputformat type='print' description='Print' primary='yes' stylesheet='medRelease.xsl' newwindow='yes' templateOption='printFrame'/>
</outputformats>

Sending by email accepts the following additional parameters:

  • recipient_email
  • subject
  • message
  • send_by. Sending by fax assumes the use of an efax account. If send_by = fax then the recipient email is appended to @efaxsend.com and the sender is changed to the faxSender value in the config file. Efax requires that the sender address be registered with them to be processed. The recipient email is assumed to be the fax number, in the format 12223332222 (for US phones) where 1 is the country code, 222 is the area code, and 3332222 is the number.
  • attachment_as_pdf. Must be included and equal to 1 for faxes.

http://afids.angelflightwest.org/reports/reports.php?reportDef=report_specs.xml&reportName=medReleaseByPassenger&passenger_id=1001&action=display&outtype=email&recipient_email=18663966980&subject=Medical%20Release&send_by=fax&attachment_as_pdf=1

Example of a report block

			<reportName>cancelled_mission_no_pilot</reportName>
			<title>Missions Canceled for No Pilot</title>
			<category>Missions</category>
			<category_2></category_2>
			<category_3></category_3>
			<active>true</active>
			<descriptionText>Missions canceled for no pilot, or no pilot other leg</descriptionText>
			<security>
				<userRights>
					<userRight>REPNV</userRight>
				</userRights>
			</security>
			<dataset commandText="SELECT * FROM rp_cancel_no_pilot" commandType='adCmdText' commandTimeout='60'>
				<inputParameters>
					<inputParameter paramName='startDate' fieldName='mission_date' fieldDesc='Application Date Start' operator='ge' required='no' useroption='yes' bottomoption='yes' default='monthstart' datatype='date' fieldType='text'  fieldSize='10' />
					<inputParameter paramName='endDate' fieldName='mission_date' fieldDesc='Application Date End' operator='le' required='no' useroption='yes' bottomoption='yes' default='monthend' datatype='date' fieldType='text'  fieldSize='10' />
					<inputParameter paramName='origin_wing' fieldName='from_wing_id' fieldDesc='Origin wing' operator='eq' required='no' useroption='yes' bottomoption='yes' default='' datatype='int' fieldType='select' fieldSize='1' refSourceTable='wing' refSourceIDField='id' refSourceDescField='name' />
					<inputParameter paramName='dest_wing' fieldName='to_wing_id' fieldDesc='Destination wing' operator='eq' required='no' useroption='yes' bottomoption='yes' default='' datatype='int' fieldType='select' fieldSize='1' refSourceTable='wing' refSourceIDField='id' refSourceDescField='name' />
				</inputParameters>
			</dataset>
			<pageSize>0</pageSize>


Formatting the stylesheets

Preparing the dataset