Initialization of flight data in the AIRCOM Server system

Some theory

ACARS (Aircraft Communications Addressing and Reporting System) is a digital communications system used in aviation to transmit short, relatively simple communications between an aircraft and ground stations, either through direct radio communication or via satellite systems.
AIRCOM - Server (gateway) messaging between different networks. Manufacturer - SITA company. Connects ACARS on-board equipment through ARINC networks with LIDO OC flight planning system (Jeppesen Jet Planner or others), email, SITAtex, telephony, file sharing and other necessary information systems used by the airline.

How AIRCOM Server Works

The SITA AIRCOM Server information system is implemented in MS SQL and is used to provide the airline’s aircraft with information about the route, wind at flight levels, weather, and for the message “crew - MCC - crew”. AIRCOM Server is configured on ACARC airborne equipment and functions in conjunction with it. AIRCOM Server is a critical information system for flight operations.

For AIRCOM to work correctly, it is necessary that the correct data about the upcoming (or current) flight be in the AIRCOM database and in the memory of the FMS of the aircraft:

- flight number;
- the side number of the aircraft (VS);
- departure and destination aerodromes;
- time of departure, etc.
')
If the information in the FMS memory, in the AIRCOM database and in the flight planning system will not match, some pilots requests will not be processed, and the crew will not receive, for example, updated wind data at flight levels.

Data on the upcoming (or current) flight to AIRCOM and to the memory of the FMS aircraft should be obtained from the airline's information system (let's call it conditionally “the Schedule”), in which the flight schedule is formed and adjusted.

This can be done in two ways:

1. Manual initialization by crew

The pilot manually fills all the data for the upcoming flight using the ACARS remote control and work flight plan (OFP), and then performs initialization by pressing the “INIT” button. In this case, flight data is sent to AIRCOM and recorded in its database.

The disadvantages of this method are:

- The pilot may make a mistake when entering data.
- it is necessary to wait some time (~ 15 minutes) after turning on the power of the onboard systems of the aircraft and only then enter flight data and initialization.

2. Automated initialization

The pilot sends an initialization message to AIRCOM without paying attention to what data is stored in the FMS memory (this could be a previous flight or not at all), only the type of message is important - INIT.
AIRCOM receives this INIT and knows that this request came from a specific aircraft (by the hull number), as well as the date and time of the request.

AIRCOM, having received an INIT-request from the aircraft, uses the downlink template and model (Model) for this type of message, receives from the third-party system the current valid flight schedule data for this aircraft (flight number, departure and destination aerodrome, date and departure time), writes this data to its database and sends this information via ACARS to the plane. This data is recorded in the onboard system’s memory and used for subsequent requests from the aircraft.

A task was formed - to implement information exchange between the information system with the flight schedule of the aircraft and AIRCOM.

The AIRCOM IS has a full-time function — the use of an additional third-party database and to exchange information with it using two stored procedures: one for writing and one for reading. The connection parameters to this database are specified in the AIRCOM configuration file - AircomSrv.ini. Additionally, on the AIRCOM server, an additional service, AS Database Connector, must be installed and started (when everything is configured).

AS Database Connector supports connection only to the MS SQL Server type database (others, including Oracle, will be supported in some future versions). Database Connector can be connected to only one database and use only one pair of stored procedures (read and write) to receive and send data.

Implementation

1. In the database of the Flight Schedule IS (Oracle), a View (view) and a user with the right to launch this view were created. This view returned a set of data collected from several DB tables “Schedule”, which contained:

• id - id of the flight from the database "Schedule" (unique)
• acft - aircraft side number
• flight - flight number
• dep_icao - ICAO code of a / d departure
• dest_icao - ICAO code of a / d destination
• STD - date and time of departure scheduled
• ETD - departure date and time scheduled (corrected)
• ETA - date and time of arrival scheduled

2. An additional database was created on the AIRCOM (MS SQL) IS server, containing 2 tables and 2 stored procedures, as well as an individual user having the right to write and read these tables and run the stored procedures — the standard implementation provided by the AIRCOM IS.

2.1 “schedule” table - for storing the current flight schedule of all AC aircraft (flight data obtained from the “Schedule”):

CREATE TABLE [dbo].[schedule]( [leg_number] [int] NOT NULL, [tail] [varchar](10) NOT NULL, [flight] [varchar](7) NOT NULL, [dep_icao] [varchar](4) NOT NULL, [dest_icao] [varchar](4) NOT NULL, [STD] [datetime] NULL, [ETD] [datetime] NULL, [ETA] [datetime] NULL, PRIMARY KEY CLUSTERED ( [leg_number] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO 

2.2 “messages” table - for recording data on INIT queries received from AIRCOM:

 CREATE TABLE [dbo].[messages]( [id] [int] IDENTITY(1,1) NOT NULL, [AircraftReg] [varchar](7) NOT NULL, [Treated] [bit] NOT NULL, [dt] [datetime] NULL ) ON [PRIMARY] GO 

Requests come from the AIRCOM information system and contain only the aircraft board number.

2.3 Stored procedure msgReceive - triggered when an INIT request is received from the aircraft.

 CREATE PROCEDURE [dbo].[msgReceive]( @inMsg varchar(8000), @inTarget varchar(256)) AS BEGIN DECLARE @cnt INT; SELECT @cnt = count(*) FROM dbo.messages WHERE (AircraftReg = @inMsg)and(Treated = 0); IF (@cnt = 0) BEGIN INSERT INTO dbo.messages (AircraftReg, Treated, dt) VALUES(@inMsg, 0, GETDATE()); END; RETURN 0 END GO 

The “msgReceive” stored procedure is a write procedure. When receiving a message from AIRCOM that contains the aircraft's board number, it writes data to the “messages” table with a new row. In this case, a zero is recorded in the “Treated” column. The “dt” column displays the time the request was received.

2.4 “GetSchedule” stored procedure

 CREATE PROC [dbo].[GetSchedule] @outMsg VARCHAR(8000) = '' OUT, @outSource VARCHAR(256) = '' OUT AS BEGIN DECLARE @cnt INT; DECLARE @msID VARCHAR(7); SELECT @cnt = count(*) FROM dbo.messages WHERE Treated = 0; IF (@cnt > 0) BEGIN select top(1) @msID = ms.id, @outMsg = 'SUPER_INIT <ACFT>'+sc.tail+'</ACFT> <FLIGHT>ZZ'+sc.flight+'</FLIGHT> <FltNum>'+case ISNUMERIC (sc.flight) when 1 then REPLICATE('0', 5 - DATALENGTH(sc.flight)) + sc.flight else REPLICATE('0', 6 - DATALENGTH(sc.flight)) + LEFT(sc.flight, LEN(sc.flight)-1) end +'</FltNum> <OS>'+case ISNUMERIC(sc.flight)when 1 then ' 'else RIGHT(sc.flight, 1) end+'</OS> <DEP>'+sc.dep_icao+'</DEP> <DEST>'+sc.dest_icao+'</DEST> <STD>'+RIGHT('00'+CAST(DAY(sc.STD) as VARCHAR), 2)+'</STD> <ETD>'+REPLACE(left(convert(VARCHAR, sc.ETD, 108),5), ':', '')+'</ETD> <ETE>'+REPLACE(left(convert(VARCHAR, sc.ETA-sc.ETD, 108),5), ':', '')+'</ETE>' from dbo.schedule sc INNER JOIN dbo.messages ms ON sc.tail = ms.AircraftReg where (ms.Treated = 0) and ((getutcdate() between ETD and ETA) or (getutcdate() < ETD)) ORDER BY sc.ETD; UPDATE dbo.messages SET Treated = 1 WHERE id = @msID; END END GO 

The “GetSchedule” procedure is a reading procedure and returns to AIRCOM the data from the schedule table as follows:

 SUPER_INIT –   (Header),   ; <ACFT>AB-CDE</ACFT> –   ; <FLIGHT>ZZ123</FLIGHT> –  ; <FltNum>00123</FltNum> –   (   5- ) <OS> </OS> –  (  ) <DEP>KDFW</DEP> – - / ; <DEST>EHAM</DEST> – - / ; <STD>21</STD> –      (dd); <ETD>0620</ETD> –    (UTC, hhnn); <ETE>0930</ETE> –    (UTC, flightime, hhnn); 

The type of the returned data can be absolutely anything, the main thing is to adjust the template accordingly.

3. A separate Windows application was created that runs on the AIRCOM server (Windows Sever) through the Task Scheduler. This program, using the account from item 1, starts the Presentation and receives data on the current flight schedule of all airlines of the airline for the period from - 12 hours to + 24 hours, after which it writes this array to the shedule table, after removing from it all lines.

For the program to work, it is necessary to install a standard Oracle micro-client into the system and to make entries in the tnsnames.ora file with settings for connecting to the “Schedule” database.

The principle of operation of the implemented initialization mechanism

- The pilot sends an INIT request to AIRCOM;

- AIRCOM, having received the INIT request from the VS, using the template (downlink template), understands that this is an initialization request.
Based on the active and default model for this template - “INIT Model”, AIRCOM takes only the aircraft board number from the received message and sends it to the user “INIT DB” (Special Accounts). This user has a type - "Database".

- The user "INIT DB" accesses the database "INIT" and runs the stored procedure on the entry "msgReceive", while in the database "INIT" a new row is added to the table "messages", which has a value of 0 in the column "Treated" .

- The stored procedure for reading starts automatically - every 600 seconds (the launch period is specified in the AircomSrv.ini file and can be changed) and, if there are lines in the “messages” table that have the value “0” in the “Treated” column, returns it sends AIRCOM message of the form:

 SUPER_INIT <ACFT>AB-CDE</ACFT> <FLIGHT>ZZ122</FLIGHT> <FltNum>00223</FltNum> <OS> </OS> <DEP>UUDD</DEP> <DEST>EHAM</DEST> <STD>03</STD> <ETD>1240</ETD> <ETE>0340</ETE> 

And in the “INIT” database in the “messages” table in the “Treated” column, it changes the value from zero (0) to one (1).

- AIRCOM, having received the AUTO-INIT message, writes all the data to its database and, using a certain template (Uplink Template from the “Special Accounts” section) and its certain model, by default, sends flight data to the aircraft.