User Defined Queries

Catch-e uses a relational database management systems (RDBMS) to store your data.

SQL (Structured Query Language) is the database computer language used for managing data in the database.

Its scope includes data query and update, table creation and modification, and data access control. In this context, you are only interested in the data query and update functions.

• Queries used in the Scheduler are stored in the scheduler table

• Queries used in Screen Reports are stored in the screen reports table

The details below are to assist you in creating reports for use in the Scheduler and in Screen Reports.

Query Storage and Formats

Office (xls) Format

If you have nominated 'Office(xls)_Format' as the output option for your scheduler job or screen report, you can customise the resulting spreadsheet in several ways, such as:

• Adding a report header including a logo, title, and run time details

• Customising the cell formatting, for example fonts, colours, column widths

• Embedding Excel formulae in your data

See Office(xls) Format for further details.

Office (xlsx) Format

Formatting Office(xlsx) reports is not available at this time.

Data Field Handling

Encrypted Fields

Encrypted fields can be accessed using special syntax.

Example

To decrypt and report on the field you can use this:

SELECT AES_DECRYPT(income_gross, 'AES_CRYPT_CIPHER_KEY') AS income_gross FROM fm_drivers WHERE driver_id = '100000'; 

This will only work when the query is run by the Scheduler or via a Web Service call. If you try and run this in Query Browser / Workbench it will return a 'null' value.

UUID_SHORT Fields

UUID_SHORT fields are identified as those that have a field type of 'bigint(20) unsigned'.

To add a row in a table using this unique identifier, the UUID_SHORT function must be included in an 'INSERT INTO' script, instead of the numbering sequence using gb_unique_ids.

Example

INSERT INTO fm_finance_asset_types (finance_asset_type_id, name, ownership_required_flag) VALUES (uuid_short, 'sample', 'no'); 

The value 'uuid_short' is entered without the characters ' ', as it needs to be identified as an incremental value not a string value.

Billing and Transactions

Billing Locks

When creating executeScript scheduler jobs, you can invoke Stored Procedures to create and release billing locks:

CALL applyBillingLock; CALL releaseBillingLock; 

Stored Procedures

Stored procedures contain a set of rules to update the database when they are run. The run times of stored procedures are set at a system level.

Transaction Safe Queries

To ensure data integrity, it is important to put queries into a transaction block when you are making updates or inserting records.

When a transaction block is run in executeScript scheduler jobs, or in executeScript web services, and any of the sql statements fail to execute, then all prior changes made in the transaction block are rolled back. Queries within the transaction block are only committed if all the queries have successfully executed.

Each query MUST begin on a new line.

Setup Format

1. Transactions that do not use LOCK TABLES:

START TRANSACTION; … updates, inserts here … COMMIT; 

For this use case to be valid the following statements must occur once each in the following order:

• START TRANSACTION

• COMMIT

2. Transactions that use LOCK TABLES:

SET autocommit=0; LOCK TABLES t1 WRITE, t2 READ, …; … do something with tables t1 and t2 here … COMMIT; UNLOCK TABLES; 

For this use case to be valid the following statements must occur once each in the following order:

• SET autocommit=0

• LOCK TABLES

• COMMIT

• UNLOCK TABLES

Refer Using transaction with lock tables for additional guidance.

Report Queue

Report Queue Field

Check Box — Check this field to send the report to the Report Queue. If configured, you will receive an email when the report can be downloaded. Otherwise, the report will be generated for you on this screen for downloading when complete. If the report takes a long time to run and exceeds the time set in gb_control reportqueuetimeout, then the report will be sent to the Report Queue. You will be alerted if this is going to happen.

Download a Report from the Report Queue

Qualifiers

• You are running a system report and checked Report Queue, or

• You are running a system report and it has timed out, or

• You have received an email advising that a report is ready to download (email will note the Queue ID and the Report Title)

Process

1. Go to System / Report Queue

2. A list of your active reports will be shown

3. Source: select the source to filter for one source

4. Report Title: select the report title to filter for one report

5. Queue ID: if you received an email, you can enter the queue id to filter to the exact report

6. User: change to '--All--' or to the user who normally receives the report you want to see (you can only do this if you have access to this field)

7. Click Download

8. Click on the paperclip to download the report

Derived Fields

Overview

Using special mark-up in your query, you can access derived fields. These are fields generated internally by the system that would be too hard to define in standard SQL. By using derived fields you are guaranteed to get the same values output on many of Catch-e's standard reports, e.g. Estimated Odometer on the Inventory report.

Derived Field Libraries

The following list gives many of the 'standard' libraries. To get a listing of the 'bespoke' libraries please consult your internal Wiki. To get a list of derived fields click on the Library name.

Required Mark-up

The special markup has the following general form: '{type}:{path}/{library}->{field}' AS 'field alias'

Where:

• {type}: Either 'bespoke' to get derived fields from your client specific libraries, or 'standard' for derived fields from the standard system

• {path}: Directory where the {library} is located

• {library}: Name of the library containing the required derived field. For some libraries, an additional parameter must be included for a country code. The general form then looks like this {countrycode|library} e.g. NZ|fbtemployee_quarterly

• {field}: Name of the derived field

• 'field alias': Name you want to give the field in the final output. Note: this must be unique

Example Mark-up

The following SQL shows the derived field markup required to output the Estimated Odometer value that appears on the Inventory report:

SELECT 'standard:/lib/PDF/odometer->odo_est_current' AS 'Estimated Odometer', t1.contract_id AS contract_id FROM fm_contracts AS t1 WHERE contract_start > '2009-01-01' 

If you ran this in the Query Browser, the output would look something like this:

'standard:/lib/PDF/odometer->odo_est_current', 100101 'standard:/lib/PDF/odometer->odo_est_current', 100121 'standard:/lib/PDF/odometer->odo_est_current', 103810 ... 

You can add as many derived fields as required, and they can come from multiple libraries. The markup is formatted in such a way that the SQL can be run normally, i.e. from within an SQL client such as the Query Browser. That way you can develop the SQL with the derived field markup in place, and it will run properly albeit with the derived field markup output literally.

Important Limitation: Derived fields cannot be used in formulae or calculations themselves. They can only be output as is. If there is a Derived Field you think is missing from a library, please contact Catch-e, and we will likely add it in.

Warning: Important: Derived field libraries often require values from other fields before they will work. These fields must appear somewhere in the SELECT statement of your query. E.g. the 'standard:/lib/PDF/odometer' library requires the contract_id field. You MUST NOT rename the field using an alias, e.g. t1.contract_id as 'Contract ID' will fail. In this example you must use t1.contract_id AS contract_id

Alternative Syntax

There is an alternative syntax that gives you a more concise way of defining derived field markup. You start by including some special comments in your query as follows:

## library_type bespoke ## library_path /fleet/reports ## library_name odometer 

These comments are best placed at the top, before the SQL begins, but can be placed anywhere. Now you can use the following syntax for a derived field: ->{field}' AS 'field alias'

Examples

Example 1: Using alternative syntax

## library_type standard ## library_path /lib/PDF ## library_name odometer SELECT '->odo_est_current' AS 'Estimated Odometer', t1.contract_id AS contract_id FROM fm_contracts AS t1 WHERE contract_start > '2009-01-01' 

Example 2: Excluding the special comment '# library_name'

## library_type standard ## library_path /lib/PDF SELECT 'odometer->odo_est_current' AS 'Estimated Odometer', t1.contract_id AS contract_id FROM fm_contracts AS t1 WHERE contract_start > '2009-01-01' 

Note: The library name now needs to be defined within the derived field markup itself i.e. the {library} part.

You can think of these special comments as a way of setting global values for {type}, {path} and {library}, so they don't have to be repeated for each derived field.

Limitation: You can only define one set of these global values. If you require derived fields from more than one library, you will need to use the more verbose syntax for some of the derived fields.

Multi-Library Example

## library_type standard ## library_path /lib/PDF ## library_name odometer SELECT '->odo_est_current' AS 'Estimated Odometer', t1.contract_id AS contract_id, 'bespoke:/fleet/reports/costing->rounding_adjustment_flag' AS 'Rounding Adjustment Flag' FROM fm_contracts AS t1 WHERE contract_start > '2009-01-01' 

Including Derived Fields in WHERE or HAVING Clauses

Derived fields cannot be included in the WHERE or HAVING statements of the SQL. To overcome this, you can include a special comment 'having_clause' followed by the required logic.

Examples

## having_clause $row['service_overdue'] == 'yes' 

Or:

## having_clause $row['service_overdue'] == 'yes' && $row['service_overdue_days'] > 10 

Note: PHP code is used in the above, so 'is equal to' operator is == not =

Derived Fields FBT Statutory

This library is used to calculate FBT statutory placeholders and emission values based on existing data.

These readings are shown on screens and used in various reports, such as the FBT Statutory Report.

This library can also be used with the following scheduler job methods:

• executeQuery

• mailQuery

• eventQuery

This library can also be used with the following APIs:

• printReportQuery

Input Fields

The following Input Fields need to be added to your query:

Output Fields

The following Derived Fields can be added to your query or report query using the Derived Fields syntax:

• variance_odo_at_other_date — Variance in odometer at a date other than contract end

• contract_periods_ltd_at_other_date — MonthDiff (Date1, Date2) where Date1 = filterdate (Report Date) and Date2 = fm_contracts.thirdpartystart if present otherwise fm_contracts.contract_start. Note: MonthDiff = 1 if Date1 and Date2 are in same month. E.g. MonthDiff(19/06/2011, 19/06/2011) = 1 and MonthDiff(19/05/2011, 19/06/2011) = 2

• contract_periods_sytd_at_other_date — MonthDiff (Date1, Date2) where Date1 = filterdate (Report Date) and Date2 = Maximum Date between FBT Year Start Date of filterdate and fm_contracts.thirdpartystart if present otherwise fm_contracts.contract_start. E.g. MonthDiff(19/06/2011, 19/06/2011) = 1 and MonthDiff(19/05/2011, 19/06/2011) = 2

• reportable_fbt_benefit — Reportable FBT benefit value

• budget_fringe_benefit — Contracts / FBT Budgeted "Fringe Benefit"

• actual_fringe_benefit — Contracts / FBT Actual "Fringe Benefit"