Queries & Reports API: Print a report query

Print a report query

Allows you to run a query that is saved in the table using the identified reportqueryid.

To get a report set-up or modified, fill in a Report Query Request and send it to your Account Manager.

Creating Queries

For large datasets use LIMIT placeholders. See the LIMIT and OFFSET section for an example.

Visit the pages below for details of general query features that apply here
User Defined Queries
Encrypted Fields
Derived Fields - only the libraries listed below will run in this API.
emissions
fbtstatutory odometer vehiclephotos

Permissions

To run this API, the nominated 'web-services' role needs to be given permission.

If you are not actively using the API, leave the permission off for better security.

Go to Roles / Apis and check on ReportQueries:Print.

HTTP Method

Use the HTTP Method 'POST' for consuming this web service.

Input Fields (Header)

The representational output format of the report query is controlled by HTTP Accept header. Three content types are presently supported:

• text/csv
• text/xml
• application/vnd.ms-excel
• application/json (default)

Queries that run exceed the will return an HTTP 422 (Unprocessable Content) response from the API.

Query

• stringifyresultfields_flag (string) — This parameter is set to 'yes' by default. When yes, number fields are returned as strings in the response. Set to 'No' to return number fields in a numeric format. No

Path

• reportqueryid (string) — Report Query ID of report to run Yes

Body (JSON)

Query parameters are optional.
If a specific parameter is required by the selected report query but not included in the request,
a 422 Unprocessable Entity response will result.
The required parameter will be documented within the HTTP 422 response payload.

{ "key": "value", "anotherkey": 123456}

For example:

Let say you are running Report query below:

select driver_id, supplier_id from fm_driver_bank_accounts t1 where driver_id = '#t1.driver_id#' and t1.supplier_id = '#t1.supplier_id#' limit 10;

This query has two placeholders required to run the query : #t1.driverid# and #t1.supplierid#

Body will have below JSON structure:

{ "t1.driver_id": "100003", "t1.supplier_Id": "100124"}

Here's another example:

Let say you are running Report query below:

SELECT q.quote_id AS 'quotes' FROM qt_quotes q WHERE q.client_id IN ('#client_id#') ORDER BY q.quote_id DESC;

This query has one placeholder required to run the query. However, the placeholder is an array of client_id(s) like this: 100001, 100002, 100003

In this case the Body will have below JSON structure:

{ "client_id": [ "100001", "100002", "100003", ]}

Wildcard (*)

Wildcard queries can be used here. We don't recommend them for procedural use.
Your processes may be affected if table changes are made. Catch-e updates tables from time to time.

Query stored in :

SELECT *FROM fm_contractsWHERE contract_id = #contract_id#

printReportQuery request Body:

{ "contract_id": "103456"}

LIMIT and OFFSET

Query stored in :

SELECT contract_id, reg_noFROM fm_contractsLIMIT #offset#, #limit#

printReportQuery request Body:

{ "offset": "5000", "limit": "1000"}

The SQL query above says "return only 1000 (#limit#) records, start on record 5000 (#offset#)"

SELECT contract_id, reg_noFROM fm_contractsLIMIT 5000, 1000

Build in placeholder - #authenticateduserid

A system placeholder, called #authenticateduserid#, holds the user_id of the authenticated user calling the query. This can be used to restrict the extracted data. Unlike other query parameters, system placeholders don't need to be passed in the body as input fields.

SELECT cl.*FROM gb_users AS u    INNER JOIN gb_roles AS r ON r.role_id = u.role_id    LEFT JOIN gb_user_roles AS ur ON ur.user_id = u.user_id    INNER JOIN fm_clients AS cl ON        If(r.restrict_table = 'fm_clients'            , cl.client_id = ur.restrict_key_value            , cl.client_group_id = ur.restrict_key_value)WHERE u.user_id = '#authenticated_user_id#';

Role Restrict

Add a role restriction to limit access to only the required reports.

This works well with the #authenticateduserid# placeholder to provide reports to external roles such as 'Fleet Manager'.

select r.name, rar.*from gb_role_api_restrictions as rarinner join gb_roles as r on r.role_id = rar.role_idWHERE table_name = 'gb_report_queries';

URL Examples

https://api.catch-e.com/gb/report/queries/print/{report_query_id}

(Successful) Output Example

Queries that run successfully but result in 0 rows will return an HTTP 204 (No content) response from the API.

HTTP Accept header = text/csv

driver_id,supplier_id,bank_bsb,bank_account_number100003,100471,546-321,654789

HTTP Accept header = text/xml

100003 100471 546-321 654789

HTTP Accept header = application/json

[ { "driver_id": "100003", "supplier_id": "100471", "bank_bsb": "546-321", "bank_account_number": "654789" }]

Response Details

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unauthorized", "status": 401, "detail": "Unauthorized"}

| You have not authenticated before running this API or The token_timeout of the current session has passed. You need to authenticate again. |
| 403 - Forbidden | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Forbidden", "status": 403, "detail": "Forbidden"}

| You do not have permissions for this request. Go to System Roles and enter 'web_services' Navigate to the Roles / APIs tab to make sure the permission you need to run this API is checked. |
| 404 - Not Found | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Forbidden", "status": 404, "detail": "Report query not found"}

| Your role has a restriction on which reports can be run. The reportqueryid passed is not in the list of allowed reports. Contact your Account Manager to update the list of allowed reports.

select * from gb_role_api_restrictions WHERE table_name = 'gb_report_queries';

|
| 422 - Unprocessable Entity | | |

 "report_query_id": { "InvalidQueryType": "Only SELECT statements supported"

| Only SELECT statements are allowed to be run using this API. If a query has any statements except SELECT, this error is thrown. | |

 "report_query_id": { "InvalidQuery": "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `undefined1`\nFROM\n ...... "

| Using a wildcard (*) in your select statement will return this error. Select statements must contain named columns. | |

 "report_query_id": { "missingParameter": { "message": "Report query is missing required parameter", "parameter": "t1.driver_id"

| If a parameter is missing which is required in the report query, this error is thrown. | |

 "report_query_id": { "multipleStatementsNotSupported": "Report query contains multiple statements"

| If a report query includes multiple SELECT statements, this error is thrown. Check your query for duplicate fields or joins. | |

 "report_query_id": { "reportQueryNotActive": "Report query status_flag is inactive"

| Stored queries cannot be run if is ínactive'. Update the query status. | |

 "query_id": { "invalidQuery": "Query execution was interrupted, maximum statement execution time exceeded." }

| Stored queries exceeded the '. |
| 422 - Unprocessable Entity - Odometer Library | | |

 "date_filter": { "invalidFormat": "Invalid date format specified"

| The date being passed is malformed, use either dd/mm/yyyy or yyyy-mm-dd. This validation also applies to the field odometercutoffdate. |
| 429 - Too Many Requests | | |

 "title": "To Many Requests"

| The rate limit of your system has been exceeded and the response contains the number of seconds you should wait until trying again. Your rates limit includes all legacy web services and current token-based APIs. Whitelisted IP Addresses are excluded, the whitelist includes the localhost IP so that internal APIs are excluded from rate limiting counts. Catch-e's rate limiting uses the sliding window concept. See the following discussion piece about this: https://blog.cloudflare.com/counting-things-a-lot-of-different-things/ |
| 500 - Unexpected error | | |

{ "status": 500, "title": "Unexpected error", "describedBy": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "detail": "JSON encoding error occurred: Malformed UTF-8 characters, possibly incorrectly encoded",}

| If a query has encrypted fields which are not decrypted using aes_decrypt in JSON response, below error is thrown. Please decrypt the fields in query before adding the query in table. | |

{ "title": "Internal Server Error", "status": 500, "detail": "Statement could not be executed(42000 - 1064 - You have an error in your SQL syntax; check the manual that correspondsto your MySQL server version for the right syntax to use near ')AS `o` ON o.quote_id = c.quote_id LEFT JOIN( SELECT contract_i' at line 252)"}

| This API is sensitive to lines that are commented out in the target query. Such a query may run in other environments such as Workbench, but will fail here. Review and remove commented lines from the query. Update the table and re-test the query using the API. |

Read single record

This API returns details for a single record.
The key fields/values here are similar to specifying WHERE clause in SQL.
Make sure your key fields/Values combination result in a single record match.
A multiple records match will return a 422 response status.

Supplementary SWAGGER documentation is available here: https://api.catch-e.com/docs/#/Import%20%2F%20Export/readRecord

Permissions

To run this API, the nominated 'web-services' role needs to be given permission.

If you are not actively using the API, leave the permission off for better security.

Go to Roles / Apis and check on Record:Get.

Note: This API is not configured for external use.
Contact your Account Manager to discuss access to this API.

Authentication

Authenticate with the API before running this API.

HTTP Method

Use the HTTP Method 'GET' for consuming this web service.

URL Examples

https://api.test.catch-e.com/gb/record/read-record?table_name={table_name}&table_key_fields={table_key_fields}&table_key_values={table_key_values}

Path Variables

Response Details

{ "client_code": "ABC", "name": "ABC Test Company", "client_type": "lead", "contact1_email": "email@abc.com.au", "send_approval_email_flag": "yes", "dirty_flag": "2020-05-29 05:30:11"}

| Success. |
| 403 - Forbidden | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Forbidden", "status": 403, "detail": "Forbidden"}

| You do not have permissions for this request. Go to System Roles and enter 'web_services' Navigate to the Roles / APIs tab to make sure the permission you need to run this API is checked. |
| 422 - Unprocessable Content | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unprocessable Entity", "status": 422, "detail": "CATCH_E_ERROR_RECORD_NOT_FOUND : No record found in table 'fm_clients' with the provided key fields and values."}

| No record found. | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unprocessable Entity", "status": 422, "detail": "CATCH_E_ERROR_BAD_TABLE_KEY : Column 'posting_class_id1' does not exist in table 'fm_contract_budgets'."}

| Bad key field name. | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unprocessable Entity", "status": 422, "detail": "CATCH_E_ERROR_BAD_INPUT : Unknown 'fields' specified: client_codeq"}

| Bad input. | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Unprocessable Entity", "status": 422, "detail": CATCH_E_ERROR_MULTIPLE_RECORDS_FOUND : Multiple records found in table 'fm_clients' with the provided key fields and values.Please redefine your key fields and values to ensure only a single record is matched"}

| Multiple macthes found. |
| 429 - Too Many Requests | | |

{ "type": "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html", "title": "Too Many Requests", "status": 429, "detail": "Too Many Requests"}

| Rest call requests exceeded the limit set in |