Initializing Flight Data in AIRCOM Server
Bit of 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 communications or satellite systems.
AIRCOM - Server (gateway) messaging between different networks. The manufacturer is SITA. It connects ACARS on-board equipment via ARINC networks with the LIDO OC flight planning system (Jeppesen Jet Planner or others), e-mail, SITAtex, telephony, file exchange and other necessary information systems used by the airline.
How AIRCOM Server Works
The SITA AIRCOM Server information system is implemented on MS SQL and is used to provide airline airlines with data on the route, wind at echelons, weather and for the exchange of messages “crew - MCC - crew". AIRCOM Server is configured for ACARC on-board equipment and operates in conjunction with it. AIRCOM Server is a critical flight information system.
For the correct operation of AIRCOM, it is necessary that the correct data on the upcoming (or current) flight should be in the AIRCOM database and in the FMS memory of the aircraft:
- flight number;
- aircraft side number (Aircraft);
- departure and destination aerodromes;
- Departure time, etc.
If the information in the FMS memory, in the AIRCOM database and in the flight planning system does not match, some requests of the pilots will not be processed, and the crew will not receive, for example, updated data on winds at echelons along the flight route.
Data on the upcoming (or current) flight to AIRCOM and to the FMS aircraft memory must be received from the airline’s information system (let's call it “Schedule”), in which the flight schedule is formed and adjusted.
This can be done in two ways:
1. Manual initialization by the crew.
The pilot manually fills in all the data for the upcoming flight using the ACARS remote control and flight work plan (OFP), after which it initializes by pressing the “INIT” button. At the same time, the flight data is sent to AIRCOM and recorded in its database.
Cons of this method:
- the pilot may make a mistake when entering data.
- it is necessary to wait a while (~ 15 minutes) after turning on the power of the aircraft's onboard systems and only then enter the flight data and perform 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 may be the previous flight completed or nonexistent at all), only the message type type is important - INIT.
AIRCOM receives this INIT and knows that this request came from a specific aircraft (by board 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) intended for this type of messages, receives from the third-party system the current valid flight schedule data for the aircraft (flight number, departure and destination aerodrome, date and time of departure), writes this data to its database and sends this information via ACARS to the plane. These data are recorded in the memory of the on-board system and are used for subsequent requests from the aircraft.
The task was formed - to implement the information exchange between the information system with the flight schedule of the aircraft and AIRCOM.
IS AIRCOM has a standard function - use an additional third-party database and perform information exchange with it using two stored procedures: one for writing, the second for reading. The parameters for connecting to this database are specified in the AIRCOM IP settings file - AircomSrv.ini. Additionally, an additional service, AS Database Connector, must be installed and started (when everything is configured) on the AIRCOM server.
AS Database Connector only supports connecting to a database of the "MS SQL Server" type (others, including Oracle, will allegedly support in some future versions). Database Connector can be connected to only one database and use only one pair of stored procedures (for reading and writing) to receive and send data.
1. In the IS Flight Schedule database (Oracle), a View and a user with the right to run this view were created. This view returned a set of data collected from several tables of the Schedule database, which contained:
- id - id of the flight from the Schedule database (unique)
- acft - aircraft aboard
- flight - flight number
- dep_icao - ICAO code of a / d departure
- dest_icao - ICAO destination code
- STD - scheduled departure date and time
- ETD - scheduled departure date and time (adjusted)
- ETA - scheduled date and time of arrival
2. An additional database was created on the AIRCOM IS server (MS SQL), containing 2 tables and 2 stored procedures, as well as a separate user who has the right to write and read these tables and start stored procedures — a standard implementation provided by AIRCOM IS.
2.1. The “schedule” table - to store the current flight schedule of all AK aircraft (flight data obtained from the “Schedule”):
CREATETABLE [dbo].[schedule]( [leg_number] [int] NOTNULL, [tail] [varchar](10) NOTNULL, [flight] [varchar](7) NOTNULL, [dep_icao] [varchar](4) NOTNULL, [dest_icao] [varchar](4) NOTNULL, [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 Table “messages” - for recording data on INIT-requests received from AIRCOM:
CREATETABLE [dbo].[messages]( [id] [int] IDENTITY(1,1) NOTNULL, [AircraftReg] [varchar](7) NOTNULL, [Treated] [bit] NOTNULL, [dt] [datetime] NULL ) ON [PRIMARY] GO
Requests come from the IS AIRCOM and contain only the side number of the aircraft.
2.3 msgReceive stored procedure - triggered when an INIT request is received from the sun.
CREATEPROCEDURE [dbo].[msgReceive]( @inMsg varchar(8000), @inTarget varchar(256)) ASBEGINDECLARE @cnt INT; SELECT @cnt = count(*) FROM dbo.messages WHERE (AircraftReg = @inMsg)and(Treated = 0); IF (@cnt = 0) BEGININSERTINTO dbo.messages (AircraftReg, Treated, dt) VALUES(@inMsg, 0, GETDATE()); END; RETURN 0 ENDGO
The msgReceive stored procedure is a write procedure. Upon receipt from AIRCOM a message containing the aircraft flight number, it writes data to the “messages” table in a new row. In this case, zero is written 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) = ''OUTASBEGINDECLARE @cnt INT; DECLARE @msID VARCHAR(7); SELECT @cnt = count(*) FROM dbo.messages WHERE Treated = 0; IF (@cnt > 0) BEGINselect top(1) @msID = ms.id, @outMsg = 'SUPER_INIT <ACFT>'+sc.tail+'</ACFT> <FLIGHT>ZZ'+sc.flight+'</FLIGHT> <FltNum>'+case ISNUMERIC (sc.flight) when1thenREPLICATE('0', 5 - DATALENGTH(sc.flight)) + sc.flight elseREPLICATE('0', 6 - DATALENGTH(sc.flight)) + LEFT(sc.flight, LEN(sc.flight)-1) end +'</FltNum> <OS>'+case ISNUMERIC(sc.flight)when1then' 'elseRIGHT(sc.flight, 1) end+'</OS> <DEP>'+sc.dep_icao+'</DEP> <DEST>'+sc.dest_icao+'</DEST> <STD>'+RIGHT('00'+CAST(DAY(sc.STD) asVARCHAR), 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 INNERJOIN dbo.messages ms ON sc.tail = ms.AircraftReg where (ms.Treated = 0) and ((getutcdate() between ETD and ETA) or (getutcdate() < ETD)) ORDERBY sc.ETD; UPDATE dbo.messages SET Treated = 1WHEREid = @msID; ENDENDGO
The GetSchedule procedure is a reading procedure and returns data from the schedule table to AIRCOM in the following form:
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 returned data can be absolutely anything, the main thing is to configure the template accordingly.
3. A separate Windows application has been created that runs on the AIRCOM server (Windows Sever) through the Task Scheduler. This program, using the account from paragraph 1, launches the Presentation and receives data on the current flight schedule of all airline airlines for the period from - 12 hours to + 24 hours, after which it writes this array to the shedule table, after deleting all available from it strings.
For the program to work, it is necessary to install the standard micro-client Oracle into the system and make an entry in the tnsnames.ora file with settings for connecting to the DB of the Schedule system.
The principle of operation of the implemented initialization mechanism
- The pilot sends an INIT request to AIRCOM;
- AIRCOM, having received an INIT request from the Sun using a downlink template, understands that this is an initialization request.
Based on the “INIT Model” model that is active and selected by default for this template, AIRCOM takes only the aircraft's side number from the received message and sends it to the user “INIT DB” (Special Accounts). This user is of type “Database”.
- The user “INIT DB” accesses the database “INIT” and starts the stored procedure to write “msgReceive”, while in the database “INIT” a new row is added to the table “messages” with zero (0) in the column “Treated” .
- The stored reading procedure 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 with the value “0” in the “Treated” column, it returns it to AIRCOM message of the form:
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.