Flight schedule importer/exporter

[nabeelio]

Import flights via CSV format, or export them. This CSV format is a bit more complicated than past formats.

Library

• https://csv.thephpleague.com/9.0/

Tasks

• Create new app/Services/ImportService.php service, All importers should go here

function importFlights($csv_contents, bool $delete_previous=true) 

• Build a Console importer first (php artisan phpvms:import schedules /path/to/file.csv)
  • Option in navbar of "flights" section of admin to import/export to come after
  • Look for/upload file to storage/importer folder
  • Export can also be placed in that folder, or downloaded
• Before writing to database, scan all the rows to make sure there are no obvious errors (wrong number of columns, invalid column types)
• Option to remove all previous flights and replace entirely with CSV contents
• Export current flights
• Tests
  • Create ImporterTest.php in /tests directory
  • Have tests for parsing of the days, subfleets, fares, and fields columns
    • Check for invalid columns, values, etc
  • Create sample CSV files that can be run through the importer, with a mock for the FlightRepository injected for the insert/updates

Format for file:

Column	Type	Example	Notes
airline	string	VMS	Airline code
flight_number	string	123
route_code	string	A	optional
route_leg	integer	1	optional
dpt_airport	string	KAUS	icao
arr_airport	string	KJFK	icao
alt_airport	string	KLGA	optional
* days	string	0123	optional, convert to bitmapped int
dpt_time	string	0500 CST	any string
arr_time	string	0900 EST	any string
level	integer	350	should be in standard altitude format
distance	float	1681	must be in miles
flight_time	integer	195	must be in minutes
flight_type	integer	0	0 - passenger, 1 - cargo, 2 - charter
route	string	ILEXY2 ZENZI ELD MEM JHW STENT MAGIO LVZ LENDY6	optional
notes	string		optional
active	boolean	1	0 for false, 1 for true
subfleets	string	See Below	Subfleet TYPEs that are assigned to this flight. Either semicolon separated or space separated
fares	string	See below	Any fare overrides
fields	string	See below	Any custom fields and values

* not implemented yet

• optional fields are left blank, but columns must be there
• string fields are safest to be surrounded by quotes
• support Windows and Unix line endings [fgetcsv()](http://php.net/manual/en/function.fgetcsv.php] should handle it
• laravel-excel may be worth looking into for the batch import functionality and
  • need to check the test directory size and make sure it's deleted in the build scripts

Days

The integers are for the days, starting from 0, a Sunday. This is converted to a bitmask for storage, but that might be changed.

• mysql bitmask operations
• sqlite bitwise operations

Where this becomes tricky is supporting the different database types. It might not be worth the effort to do this with bitmasks, though, bitmasks are likely going to be faster than any wildcarded string match operations when the table is scanned.

See app/Models/Enums/Days.php for bit values

Subfleets

The format for the subfleet column is either semicolon separated or space separated

A32X;A319X

or

A32X A319X

Fares

Since the fare can be overridden on a per-flight basis, taking precedence over the fare on the subfleet or the fare itself, the format can be complicated. It needs to be quoted, and comma separated, with the fare class, and in parenthesis, the fields and their values. The fields are optional.

Example:

"Y?price=200&cost=100; F?price=1200"

# should be parsed into:

[
  'Y' => [
    'price' => 200,
    'cost' => 100,
  ],
  'F' => [
    'price' => 1200
  ]
]

So the economy class, Y, for this flight, will have a price of $200, and cost $100. The first class fare, F, only will override the price, with the other fields being derived from the subfleet. This takes the format of a query string, so we can more easily parse, either using some URL parsing tools, using explode() on the ; character to determine all the different fare types, which can either be run through a URL parser, or just more explode() calls to determine the column values.

Can optionally be quoted.

See app/Services/ImporterService::parseMultiColumnValues()

Fields

Set custom fields and values, similar to the above format:

"gate=B32;cost index=100";

# should be parsed into:

[
  'gate' => 'B32',
  'cost index' => 100,
]

Can optionally be quoted. This parser should be the same as the parser above.

See app/Services/ImporterService::parseMultiColumnValues()