2.1. Queries

Queries are a powerful tool the Officers can use to filter occurrences, validated reports and original reports through various stages in the E2. Also, safety recommendations should be made if the officer belongs to a Safety Investigation Authority.

2.1.11. Queries overview and Libraries tree view

In the Queries section, the Administrator can create queries, share them with other users or manage queries shared by other users with the Administrator.

The Administrator has to open the 'Data Management’ menu at the top of the page, then click on the 'Queries' option (Data Management > Queries). The system will display the Queries menu:

On the left panel, there is a view of the Libraries tree. It includes all the libraries available to the Administrator. Those are the folders that can have categories and subcategories. Find more information about these libraries in the Libraries section. The libraries available to the Administrator are: My Personal Libraries, the Local Common Library, the Common BO Library, the National Shared Library, and the Shared with Me Library.

Administrators can click on the corresponding libraries, and the content will be displayed in the queries overview in the right panel.

On the Queries menu, Administrators will have all the folders available, but only the queries will be accessible to them, although the Libraries tree view is the same.

On the right, there will be the list of default Queries. It shows the first Library with the content of the Tree view, as seen in the image above. The list shows the corresponding folder or category of the library and below its subcategories if existing, and the queries.

The columns that also work as filters are the following:

·        Name: It shows the category, its subcategories, and the queries if they exist. Administrators can unfold the folders through the arrows.

·        The ‘BO’ column shows a green tick if the query is used in a batch operation. N/A for SRIS queries for the moment.

·        Author: it is the username of the author of the query

·        Date: Creation date.

·        ‘To Lobby’ column shows a green tick if the query can be exported to another environment and a red cross if it cannot be exported to another environment because it contains Custom Attributes.

2.1.22. Query builder - creation of a query

The Administrator can create queries in My Personal Libraries and the Local Common Library. The Administrator must click the 'New Query' button to create a new one in the selected library or folder. (This New Query button is unavailable when navigating through ‘Shared with me Libraries’ and ‘Lobby Libraries, whose queries can be executed and copied by Administrators but neither created nor edited nor deleted.) A new window will be displayed with the Query Builder:

The Query Builder allows the Administrator to build queries to search original reports, validated reports, and occurrences or safety recommendations, depending on whether the Administrator’s Authority is a National Aviation Authority, a Safety Investigation Authority or both, with Attributes or metadata fields that meet the given conditions.

The queries are executed against the reports indexed in Elastic Search.

The Administrator has to fill in the unique Query Name for each Administrator query and select a Library, category or subcategory where to store the query. See the Libraries section for more information.

Before executing, exporting or saving a query from the query builder, it is required to select over which reports the query will be executed on the ‘Count Number of Items’ section:

ECCAIRS queries (based on ECCAIRS Taxonomy)

They will be activated as soon as the mandatory fields of the query are filled.

·        Original Reports

·        Validated Reports

·        Occurrences

·        Occurrence shared with me: Occurrences shared by other Authorities with my Authority.

·        Occurrence + Shared: My major occurrences and the ones shared by other Authorities with my Authority

·        ECR: All occurrences shared with the ECR by my own and other Authorities.

 

SRIS queries (based on SRIS Taxonomy)

They will be activated as soon as the mandatory fields of the query are filled.

·        Safety Recommendations

·        ECR: All safety recommendations shared with the ECR by mine and other Authorities.

 

2.1.33. Query conditions – Attributes and metadata

Once in the Query Builder, the Administrator has to select the query parameters to set each rule by clicking on ‘Select Attribute’ under the ‘Conditions’ section.

The system will display a modal window where the Administrator can choose between:

For ECCAIRS queries (based on ECCAIRS Taxonomy)

·        Attributes: Any of the Attributes available for the Administrator’s Authority Taxonomy:

·        Metadata: additional information found at the database level that can be relevant for the Administrator to query by:

ü  e2id – E2 identificator

ü  Audit fields – they refer to the information of the specific version of the report

o         au_creation_date

o         au_creation_user

o         au_modification_date

o         au_modification_user

ü  blocked – it is set to TRUE when a batch operation blocks the report to prevent the users from editing it

ü  has_automatic_changes – it is set to TRUE when the report has had any automatic changes, coming from the OR, that no Administrator has acknowledged yet

ü  has_conflicts – it is set to NOT NULL if the report (VR or OC) has any conflicts coming from an OR update

ü  last_shared_ecr – it determines the last time the OC was shared with the ECR

ü  organisation name – the name of the Organisation of the reporter, if applicable

ü  personal_details – it is set to NOT NULL if there is any personal information from the reporter.

ü  receiving__date – it determines when the OR was sent to the Authority.

ü  reporting_entity_id – id of the reporting Entity

ü  reporting_method – what reporting method was chosen to report the OR

ü  responsible_entities_id –  Authority to which the report belongs.

ü  Status – the report status

ü  Version – version number

SRIS queries (based on SRIS Taxonomy)

·        Attributes: Any of the Attributes available for the Officer’s Authority Taxonomy:

·        Metadata: additional information found at the database level that can be relevant for the Officer to query by:

ü  e2id – E2 identifier of SRs

ü  Audit fields – they refer to the information of the specific version of the report:

o         au_creation_date: the date when the report has been created

o         au_creation_user: user who has created the report

o         au_modification_date: the last date the report was updated

o         au_modification_user: the previous user to update the report

ü  blocked – it is set to TRUE when the report is blocked to prevent the users from editing it

ü  last_shared_ecr – it determines the last time the SR was shared with the ECR

ü  responsible_entities_id – id of the responsible Entity (Authority in E2)

ü  status – the report status

ü  Version – version number

2.1.44.  Query structure

The queries are made at least by one rule in a group. A rule is a condition the search made by the query has to meet.

Queries can also include various rules and or groups of rules. A query can include one or more existing queries (subqueries).

A rule contains a condition to be met concerning an Attribute or metadata field. Layouts and operators vary depending on the Attribute and metadata field type, i.e.:

If the Administrator selects Air temperature as an Attribute, the system will display ‘Value’ as the only possible selectable layout and the Select operator filter:

This will also generate a ‘Decimals’ field to be filled out by the Administrator.

Attributes

·        Alphanumeric and Text Attributes or metadata fields can be searched in documents by a complete word using the layout ‘Value (Word)’ or by a string of characters using the layout ‘Text’. i.e.:

ü  Value (Word) as in ‘Aircraft’ (complete word)

ü  Text begins with ‘Airc’ (a string of characters)

Data Type	Layout	Operators
Alphanumeric, Text	Value (Word)	equal does not equal in not in is null is not null
	Text	in not in begins with does not begin with ends with does not end with is null is not null

·        Code Attributes can be searched in documents using layouts:

ü  Value: selecting one or more values of the Value List

ü  Level: when the Value List has more than one level, selecting all the values of a branch (level) in the Value List, starting from the selected value(s)

ü  Alias: when the Value List has alias(es), selecting one of them

Data Type	Layout	Operators
Code	Value	equal does not equal in not in is null is not null
	Level * if the Value List has more than one level	equal does not equal in not in is null is not null
	Alias * if the Value List has alias(es)	in not in begins with does not begin with ends with does not end with

·        Code and Additional Text, Code or Alternative Text Attributes can be searched in documents using layouts:

ü  Value: selecting one or more values of the Value List

ü  Level: when the Value List has more than one level, selecting all the values of a branch in the Value List

ü  Alias: when the Value List has alias(es), select one of them.

ü  Text: typing a string of characters

Data Type	Layout	Operators
Code and Additional Text, Code or Alternative Text	Value	equal does not equal in not in is null is not null
	Level * if the Value List has more than one level	equal does not equal in not in is null is not null
	Alias * if the Value List has alias(es)	in not in begins with does not begin with ends with does not end with
	Text Used to search in the Text part of the Attribute	in not in begins with does not begin with ends with does not end with is null is not null

·        Date and DateTime Attributes can be searched in documents using layouts:

ü  Value: when searching by date or range of dates 

ü  Date of the Month (1-31): when searching by the day of the month

ü  Date of Week (1-7): when searching by the day of the week

ü  Month (1-12): when searching by the month

ü  Year: when searching by year

Data Type	Layout	Operators
Date, DateTime	Value	equal does not equal less than less or equal greater than greater or equal between not between
	Day of the Month (1-31)	equal does not equal less than less or equal greater than greater or equal between not between
	Day of Week (1-7)	equal does not equal less than less or equal greater than greater or equal between not between
	Month	equal does not equal less than less or equal greater than greater or equal between not between
	Year	equal does not equal less than less or equal greater than greater or equal between not between

·        Time Attributes can be searched in documents using layouts:

ü  Value: when searching by hours and minutes

Data Type	Layout	Operators
Time	Value	equal does not equal less than less or equal greater than greater or equal between not between is null is not null

·        Latitude and Longitude Attributes can be searched in documents using layouts:

ü  Value: when searching by degrees, minutes and seconds or range of them

Data Type	Layout	Operators
Latitude, Longitude	Value	equal does not equal less than less or equal greater than greater or equal between not between is null is not null

·        Decimal Attributes can be searched in documents using layouts:

ü  Value: when searching by a decimal number

Data Type	Layout	Operators
Decimals	Value	equal does not equal less than less or equal greater than greater or equal between not between is null is not null

·        Number Attributes can be searched in documents using layouts:

ü  Value: when searching by an integer number

Data Type	Layout	Operators
Number	Value	equal does not equal less than less or equal greater than greater or equal between not between is null is not null

·        ECCAIRS Resource Locator Attributes can be searched in documents using layouts:

ü  File Name: When searching by the Name of an attached field

ü  Description: When searching by the description given in the attached field 

Data Type	Layout	Operators
ECCAIRS Resource Locator	File Name	equal does not equal in not in is null is not null
	Description	equal does not equal in not in is null is not null

·        ECCAIRS Embedded data Attributes can be searched in documents using layouts:

ü  ECCAIRS number: when searching the former document's ECCAIRS number

Data Type	Layout	Operators
ECCAIRS Embedded Data	ECCAIRS number	in not in begins with does not begin with ends with does not end with is null is not null

·        ECCAIRS Data link Attributes can be searched in documents using layouts:

ü  ECCAIRS number: when searching the former document's ECCAIRS number

Data Type	Layout	Operators
ECCAIRS Data Link	ECCAIRS number	in not in begins with does not begin with ends with does not end with is null is not null

Groups can include one or more rules and one or more groups combined with AND/OR clauses. The Administrator must click the ‘Add Rule’ button to add more rules. For groups, on the ‘Group’ tab. If the Administrator wants different sets of rules to be evaluated together, the Administrator must click the 'Add group' button. The Administrator can also add groups to other groups.

Groups and rules can be rearranged, dragging and dropping them where needed.

At the top centre of each group are the OCCURRENCE/ENTITY alternative tabs, which show whether conditions of the given Attributes shall be met in any instance of the Entity or if the conditions shall be met in the same instance of the Entity. Thus, when specifying restrictions in a query, the restrictions can be evaluated across all the entities of a record at once OR can be assessed to each Entity of a record one by one. By default, the restrictions of a query will be evaluated across all the entities of a record at once, with the 'Occurrence' option selected by default.

With the selector 'Occurrence/Entity', the Administrator can change that, and the restrictions will be evaluated Entity by Entity. There is a limitation in creating groups based on the restriction selected here. If a group is restricted to Entity, no more groups can be added inside that "Entity" group.

Example of search by occurrence and by Entity:

1.       OCCURRENCE: If we want to obtain the Occurrences where the Manufacturer model (21) is equal to ‘BOEING > 787’ and the Aircraft altitude (22) is greater than 1000 ft, independently if the conditions are met in different aircraft of an occurrence:

 

2.       ENTITY: If we want to obtain the Occurrences where the Manufacturer model (21) is equal to ‘BOEING > 787’ and the Aircraft altitude (22) is greater than 1000 ft when the conditions are met in the same aircraft.

The Administrator can set rules with AND/OR clauses and combine them as needed with groups by clicking on the ‘Group tab’ and selecting AND/OR clauses, i.e.:

Subqueries: The Administrator can add a subquery to the query through the ‘Query’ tab. The aim is to add an existing query available for the Administrator to the current query. When the Administrator clicks on the ‘Query’ tab, the system will display the subquery section:

The Administrator has to click on ‘Select Query’, and the system will display a window to select the subquery:

The Administrator must select a query from the available libraries and click ‘Add’. The system will add the subquery to the conditions of the current query.

2.1.55. Query action buttons

Once the query is completed, the following options are available:

·        Cancel button: the system will return to the Queries page.

·        Execute button: N/A to the Administrator unless they have the Officer’s role.

·        Save button: The system will save the query in the list of queries of the selected library and display the table of queries with the new query on the queries page.

·        Export button: N/A to the Administrators unless they have the Officer’s role.

2.1.66. Query Management

The Queries menu displays the table of queries of the corresponding Library, category or subcategory selected in the Libraries tree:

1.2.1.6.11.  Editing a query

Only Queries of ‘My Personal Libraries’ and ‘the Local Common library’ are editable for the Administrator. If a query is associated with a BO or query-based notification, it is not editable. In those cases, the query is visible through the ‘View button’ of the 3-dot menu or by double-clicking on the query on the table of queries.

The Administrator has to double-click on the specific editable query of the queries list or click on the ‘Edit’ option of the query’s 3-dot menu, and the system will display the editing page of the query:

The Administrator must introduce the needed update and click ‘Save’. See Query action buttons for further details.

This page also provides:

·        Elastic Query viewer button: it accesses the Elastic Query Viewer window as follows,

·        Query viewer button: it opens the query viewer window as follows,

1.2.1.6.22. Duplicating a query

All queries available to the Administrator, no matter the library or category, can be duplicated by the Administrator.

The Administrator has to click on the copy icon of the 3-dot menu of the specific query of the list of queries, and the system will display the ‘Copy Query’ window:

It is like the editing page above but with the mandatory Library field empty. The Administrator has to fill in the Library field with a different Library than the original one, and then the Execute and Export button will be activated. The Save button does not work unless a Library is selected. The Administrator must introduce the needed update and click ‘Save’. See the Query action buttons section for further details.

1.2.1.6.33. Sharing a query

The Administrator can only share queries of ‘My Personal Libraries’. Only the queries not involved in a batch operation or a query-based notification can be shared.

The Administrator has to click on the ‘Share’ option of the 3-dot menu of the specific query of the list of queries, and the system will display the Share query window:

The Administrator has to select ‘Share to User’, and then the system will display the ‘Users’ tab below.

 The Administrator has to click on ‘Users’, and the system will display the Available Users section:

As long as there are available users, the Administrator can check the specific Users, click on the ‘ADD’ button, and then click the ‘Save’ button.

The system will save the updates of the query in the database and return to the list of queries that will have added a Shared icon to the specific query:

The Administrator then can click on the ‘Edit share’ option that will be added to the 3-dot menu of that query and add more users or remove some of them.

1.2.1.6.44.  ‘To Lobby’ function

This functionality allows the Administrator to export the query to other E2 environments.

The Administrator can click on the ‘To Lobby’ option of the 3-dot menu of the specific query of the list of queries, and the system will display the ‘To Lobby’ window:

 

The Administrator has to fill in the mandatory Name and Description fields, select the Authorities of the environments displayed below and click on the ‘To Lobby’ button. The system returns to the queries menu and displays a message on the success of transferring the query to the specific Authority/ies Lobby library.

1.2.1.6.55. Deleting a query

Only My Personal Libraries and Local Common Libraries queries can be deleted by the Administrator.

They can only be deleted if not involved in a BO or query-based notification.

The Administrator has to click on the ‘Delete’ option of the specific query 3-dot menu of the list of queries. The system will delete it and update the table of queries accordingly.