PrintReportQuery

Run query saved in gb_report_queries table using identified report_query_id.

To get report setup or modified, fill in Report Query Request and send to Account Manager.

Requires ReportQueries:Print permission.

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

Note: Queries resulting in 0 rows return HTTP 204 (No Content). Queries exceeding maximum_execution_time return HTTP 422.

HTTP Method

POST

URL Examples

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

Input - Headers

Output format controlled by HTTP Accept header. Four content types supported:

• text/csv

• text/xml

• application/vnd.ms-excel

• application/json (default)

Input - Query Parameters

Input - Body (JSON)

Query parameters optional. If specific parameter required by report query but not included, 422 response.

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

Example with Single Parameter

Report query: 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;

Body with two placeholders:

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

Example with Array Parameter

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

Body with array placeholder:

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

Creating Queries

For large datasets use LIMIT placeholders.

Refer to User Defined Queries documentation for general features.

Encrypted Fields and Derived Fields supported (libraries: emissions, fbt_statutory, odometer, vehicle_photos only).

Wildcard (*)

Can be used but not recommended for procedural use. Processes affected if tables change.

Example query:

SELECT * FROM fm_contracts WHERE contract_id = #contract_id# 

Request body:

{ "contract_id": "103456" } 

LIMIT and OFFSET

Example query:

SELECT contract_id, reg_no FROM fm_contracts LIMIT #offset#, #limit# 

Request body:

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

Returns 1,000 records starting from record 5,000.

Built-in Placeholder: #authenticated_user_id#

System placeholder holds user_id of authenticated user calling query. Restricts extracted data. Unlike other parameters, doesn't need passed in body.

Example:

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 role restriction to limit access to required reports. Works well with #authenticated_user_id# for external roles like 'Fleet Manager'.

SELECT r.name, rar.* FROM gb_role_api_restrictions AS rar INNER JOIN gb_roles AS r ON r.role_id = rar.role_id WHERE table_name = 'gb_report_queries'; 

Successful Output

CSV Format

driver_id,supplier_id,bank_bsb,bank_account_number 100003,100471,546-321,654789 

XML Format

<root> <row> <driver_id>100003</driver_id> <supplier_id>100471</supplier_id> <bank_bsb>546-321</bank_bsb> <bank_account_number>654789</bank_account_number> </row> </root> 

JSON Format

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

Response - 204 No Content

Request successful. No response required (empty result set).

Response - 401 Unauthorized

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

You have not authenticated or token_timeout has passed. Authenticate again.

Response - 403 Forbidden

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

You do not have permissions. Go to System Roles, enter 'web_services', navigate to Roles / APIs and check permissions.

Response - 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 restriction on which reports can run. report_query_id not in allowed list. Contact Account Manager to update.

SELECT * FROM gb_role_api_restrictions WHERE table_name = 'gb_report_queries'; 

Response - 422 Unprocessable Entity

Non-SELECT statements:

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

Only SELECT allowed. Other statements throw error.

Wildcard (*) in select:

{ "report_query_id": { "InvalidQuery": "SQLSTATE[42000]: Syntax error... check the manual..." } } 

Named columns required, not wildcards.

Missing required parameter:

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

Required parameter missing from request body.

Multiple statements:

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

Multiple SELECT statements not allowed. Check for duplicate fields/joins.

Inactive query:

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

Update query status_flag to active.

Execution timeout:

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

Query exceeded maximum_execution_time.

Invalid date format (Odometer Library):

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

Use dd/mm/yyyy or yyyy-mm-dd. Applies to odometer_cutoff_date.

Encrypted fields (JSON response):

{ "status": 500, "title": "Unexpected error", "detail": "JSON encoding error occurred: Malformed UTF-8 characters, possibly incorrectly encoded" } 

Encrypted fields not decrypted using aes_decrypt. Decrypt in query before adding.

Commented lines in query:

{ "title": "Internal Server Error", "status": 500, "detail": "Statement could not be executed..." } 

API sensitive to commented lines. Query may run in Workbench but fail here. Remove commented lines, update table, re-test.

Response - 429 Too Many Requests

Rate limit exceeded. Response contains seconds to wait. Includes all legacy web services and APIs. Whitelisted IPs excluded (includes localhost). Sliding window rate limiting.

Reference: https://blog.cloudflare.com/counting-things-a-lot-of-different-things/

ReadSingleRecord

Read details for single record from table. Key fields/values similar to SQL WHERE clause. Ensure key combination results in single match.

Requires Record:Get permission.

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

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

Note: Not configured for external use. Contact Account Manager to discuss access.

Authentication

Authenticate with the API before running this API.

HTTP Method

GET

URL Examples

https://api.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 - 200 OK

{ "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.

Response - 403 Forbidden

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

You do not have permissions. Go to System Roles, enter 'web_services', navigate to Roles / APIs and check permissions.

Response - 422 Unprocessable Entity

Record not found:

{ "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 matches key fields/values.

Bad key field name:

{ "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'." } 

Key field name doesn't exist.

Bad input fields:

{ "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" } 

Field name doesn't exist.

Multiple matches found:

{ "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" } 

Key combination matches multiple records. Redefine to ensure single match.

Response - 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 request rate limit exceeded.