This is a quick guide to writing Portal Reports. You should be comfortable with the PortalFramework and have checked out a copy of the conduits repository before beginning.

Any vim users working with these reports are advised to make use of the gurgle VIM plugin which includes syntax highlighting.

Anatomy of a Portal Report

Portal reports have a simple structure and always include one of the includes/theon_portal_* files (sometimes via conduit inclusion).

Each report follows the following structure:

#!/usr/bin/pggurgle
%% Optional Comment line
%%include "includes/theon_portal.grg"

%%equate TP_SSI_CAT "path/ref"
%%equate TP_ARTICLE_NAME "Report Name"
%%equate TP_ARTICLE_INFO "Short description"

%%masterdb "data.sql"
SELECT [...]

%%HEADER
<!-- Here's an example HTML page generated by EXAMPLE_CONDUIT -->
<table><tr><th>Last Name</th><th>First Name</th></tr>
%%RECORD
<tr><td>%LASTNAME</td><td>%FIRSTNAME</td></tr>
%%FOOTER
</table>
%%END

The definitive reference of the TP_* commands is in the include file includes/theon_portal.grg.

The file itself is written in GURGLE; for a full language reference please see the GURGLE documentation pages.

If you are struggling with certain aspects of GURGLE it might also be worth scanning the list of GurgleQuirks which are likely to trip the unwary.

Header

#!/usr/bin/pggurgle
%% Here is my lovely conduit.

Portal Include Line

This is typically one of the following:

%%include "includes/theon_portal_plaintext.grg"
%%include "includes/theon_portal.grg"

more are available but this guide presently only covers the above.

Portal metadata

Only a few of these are mandatory. The full list is available in the portal source but some important ones are described below:

%%equate TP_SSI_CAT "System"

TP_SSI_CAT determines the location in the portal tree where this report will be visible. It can contain forward-slashes to place it down the tree. Make sure to pick a location with appropriately restrictive access control, or that any new access-control permissions have been committed and function as expected.

Note that you must pick one of the paths already defined in outgoing/portal_paths.txt or else define a new one as specified in PortalFramework .

This conduit, for example, will generate a file at the path /reports/system/TPXXX_Conduit_File_Name.shtml

or if it has been defined as multi-page (see below), it each file will be generated within the path: /reports/system/TPXXX_Conduit_File_Name/

The filenames above are controlled by the id column of your report (again, see "Multi-page Reports" below).

%%equate TP_ARTICLE_NAME "Report Name"

TP_ARTICLE_NAME is the "friendly name" of the conduit, as it will appear to users on the index page containing the report.

%%equate TP_ARTICLE_INFO "Short description of the report as shown to the user."

TP_ARTICLE_INFO is a short text description of the report, as shown on the index page containing the report.

%%equate TP_SSI_CON "file-name"

TP_SSI_CON determines the output filename in a non-multipage environment. It is not typically required as the report will be automatically be named after the conduit file name.

Database Query

Simple conduits will need only one database query, namely %%masterdb. The name is unimportant unless you plan to refer to it later but it should end in .sql.

%%masterdb "data.sql"
SELECT a.fname || ' ' || a.lname as "name",
      b.number as telephone,
      c.age, c.pets, c.colour
  FROM mytable a
  LEFT JOIN
  [...]

pay attention to column names, manipulating if necessary with the "AS" keyword, as you will need to refer to them later on.

Report Output

A typical, simple gurgle output section for a CSV file might look like this:

%%HEADER
# This file was generated by #TP_ARTICLE_NAME - do not edit
"name","telephone","age","pets","favouritecolour"

the header block is processed once per file in a multi-page report, or just once otherwise. Column references, where it makes sense to do so (using %COL), or other %%equate variables (using #VAR) can be included here.

%%RECORD
"%LASTNAME","%PHONE","%AGE","%PETS","%COLOUR"

the Record block is processed once per record, in each file. Note that column references are capitalised, though they refer to database columns which are lowercase.

%%FOOTER
%%END

The FOOTER can be undefined and/or empty. It is recommended that you define END to end your block(s), particularly as it will cause problems in more complex structures if you do not.

Specialist Report Types

Multi-page Reports

The portal provides a number of variables which control multipage output. Set these to '1' to enable.

%% Enable multi-page output:
%%equate TP_SSI_MULTIPAGE 1

%% Generate an index page listing each file:
%%equate TP_SSI_MPNDX 1

to make use of these, your query must include (for simple examples) a column id, which is used to construct the filename for each record or group of records. All rows with matching id columns are processed into a single file of that name.

Message "Mailout" Reports

These fall into two categories: single message reports and multi-message mailouts. Both use the portal header

%%include "includes/theon_portal_message.grg"

and permit (or require) a number of variables to be set:

%%equate TP_ARTICLE_REFRESH

Choose a frequency to send this message from the file outgoing/updates.txt. Remember that if you require a specific message to be sent once only, a flag will need to be set, or frequency unset so that the report is run manually.

%%equate TP_SSI_ART_TITLE

Subject line.

%%equate TP_SSI_ART_TO
%%equate TP_SSI_ART_CC
%%equate TP_SSI_ART_BCC
%%equate TP_SSI_ART_FROM

Quoted, comma-separated To, CC, BCC and From fields for your email.

%%equate TP_SSI_MULTIPAGE 1

As for multi-page reports, governs how many emails are to be sent. The outgoing email address can be set on a per-message basis with %%equate TP_SSI_ART_TO %EMAIL.

Sending Mail

The portal will not send email as a result of a RUN (or frequency-based) operation unless the --deliver argument is set: portal --deliver RUN TPXXX_Portal_Report. This is set on the live portal server but test servers will typically not be set.

Debugging

Typically just overriding the TO,CC,BCC addresses is sufficient to test output but if you require to see the sent mails within the tree the values of TP_ARTICLE_HIDDEN, TP_SSI_NULL, TP_SSI_DIR, TP_SSI_CAT can be overridden to have the messages appear in the portal.

Mailing lists

Mailing lists can be defined with a simple query returning a single row of pipe (|) separated person ids.

This is processed automatically into a mailing list with inclusions/exclusions with the following rough template:

#!/usr/bin/pggurgle

%%database "list.sql"
  SELECT DISTINCT
    ARRAY_TO_STRING(ARRAY_AGG(p.person), '|') AS people
    FROM person_3g p
    WHERE [...]

%%equate TP_LIST_MEMBERS %list->PEOPLE[1]
%%include "includes/theon_portal_mailing_list.grg"

and a few variables:

%%equate TP_LIST_NAME "list-address"
%%equate TP_ARTICLE_NAME "Short List Name"
%%equate TP_ARTICLE_INFO "Description of the Mailing List"

Then, for most lists, you should include

%%equate TP_SSI_MULTIPAGE 0
%%equate TP_SSI_CON TP_LIST_NAME

This framework also ensures that the list is uploaded to the mailing list server.

Index and Access Control Conduits

These are written in the same way as regular conduit reports and have the same structure. An important proviso is that they should be prefixed TP000_ so that they are executed before any data is placed into the relevant subdirectory.

CSV Conduits

You can easily produce a CSV version of an existing conduit by creating a new report which includes your base conduit and also the includes/theon_portal_csv.grg file; an example CSV stub might look like the following:

%% include existing conduit and set CSV variables
%%include "conduits/TPXXX_Existing_Conduit"
%%include "includes/theon_portal_csv.grg"

%% IF you wish to include the CSV version as a downloadable alternative, hide it:
%%equate TP_ARTICLE_HIDDEN 1
%% OTHERWISE, name it differently to the base conduit:
%%equate TP_SSI_ART "Existing Conduit (CSV)"

%% override header, footer and record here:
%%header
%%footer
%%record
"%FIELD","%ANOTHER","%SOMETHINGELSE"
%%end

If wish to hide the conduit as shown above, you can include it as a "download" link from the base conduit with these equates (to be placed in the base conduit):

%%equate TP_ARTICLE_DOWNLOAD_LINK 1
%%equate TP_ARTICLE_DOWNLOAD_FORMAT "CSV"

Advanced: multiple outputs and banners

%%banner lines are usually undesirable in a CSV conduit so if one has been defined it will probably need to be removed. However they can't be overridden in the same way as %%header, etc., so you will probably want to follow the instuctions in GurgleQuirks: "banner definition" to suppress it.

Advanced Report Manipulation

Post-Processing

Portal reports support a free-form post-processing pipeline command after the conduit has been generated, in the form of a one line script:

%%equate TP_ARTICLE_POSTPROC "<statement>"
%%equate TP_ARTICLE_POSTPROC_EXTN "ext"

This might be used to output data in an unusual format or send a trigger command to a remote host or process. See includes/theon_portal.grg for details on use of this command as its behaviour is fairly complex and changes depending on the conduit type.

*NOTE*: while TheonTrac#805 is open, post-processing is *always* applied to portal conduits, even on test servers. It will not shed your permissions if run manually. Please be aware of the implications of running these commands and use only when necessary.

-- TimColles - 27 Nov 2018

Topic revision: r1 - 27 Nov 2018 - 15:35:01 - TimColles
 
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
This Wiki uses Cookies