Shuttle bookings may be provided in XLS and we will need to map the fields in the XLS to import code.

The import code needs to then process the fields in order to create bookings.

The assumption is that each row in the XLS is another booking

Story

Want the ability to map import sheets with all booking information

Spreadsheets from customers have different columns > need to build import mapping / save / use

Changes

Shuttle Bookings > Booking Import Mappings > NEW SCREEN (copy from Admin > Sales > Marketplace Order Upload Mapping)

Customer gets selected in top of screen (similar to order upload mapping)
Saved mapping is listed on RHS (similar to order upload mapping)
1. “Data Type” Columns listed in table below
2. Some data type columns also require lookups saved per customer
  1. ie data expected and it’s meaning - eg the word “Arrival” indicates direction of travel

Spreadsheet column	Data Type	Where it needs to end up on Shuttle Booking Screen	Other things that May happen from this
Date of Travel (date)	Date of Travel dd/mm/yy or dd/mm/yyyy or dd/mm	Date of Travel
Flight number (text)	Flight Number (Text)	Flight number	Implies Direction of Travel Implies Destination and Terminal Implies Flight time
Flight Time (time)	Flight Time (hh:mm)	Flight time
Direction of Travel (lookup list)	Direction of travel lookup list.	Direction of travel	Need a list of possible data passed by the customer and the resulting meaning. Eg “outbound” = From Destination. etc
na	Direction from flight number	Direction of travel	Infer from flight number history + lookup table
na	Destination and Terminal from flight number	Destination and Terminal	Need to get this by looking into system history (last 3 bookings with this flight number)
Destination and Terminal (lookup list)		Destination and Terminal	Need a list of possible data passed by the customer and the resulting meaning. eg: sydney domestic = Domestic Terminal
PAX (number)		Travellers
na	PAX default (number)	Travellers	Default value
Extra luggage (number)		extra luggage
Passenger name (text)	Passenger Full Name	Passenger name
Fname (text)	Passenger First Name	Passenger name First part	combine first and last name to single field
Lname (text)	Passenger Last Name	Passenger name end part	combine first and last name to single field
Passenger email (text)	Passenger Email	booking email
passenger phone (text)	Passenger Phone	booking phone
Address in single cell (text)	Full Address	Multiple columns (address, post code, suburb)	Decompose address into line 1,2, suburb, postcode
Address line 1 (text)	Address1	Build Address
Address line 2 (text)	Address2	Build Address
address post code (text)	Postcode	Build Address	find in suburb / postcode list
address suburb (text)	Suburb	Build Address	find in suburb / postcode list
Their reference (text)	External Reference	Their Reference
Hotel Name (text)	Hotel Name	Address	Find Hotel name and compare with customer and with hotels in the system
na	Use Customer Address	Address	Choose from default customer addresses

Logical Flow

Travel Date provided (is mandatory)
1. Date is today or in the future
Flight number provided
1. Yes
  1. Infer other fields > by looking at shuttle booking history (at least 5 bookings prior to today)
    1. Destination
    2. Direction of Travel
    3. Flight Time
2. No
  1. Destination must be provided
    1. compare with lookup table for this customer
  2. Terminal must be provided
    1. yes - use lookup for terminal
    2. no - default terminal setting
  3. Flight time must be provided
  4. Direction of Travel must be provided
    1. Will need a lookup table that converts text provided to direction
PAX
1. if not provided then use default value (1)
Excess Bags
1. Only if provided
Passenger name options
1. Single field
2. combine Fname / Lname fields
Passenger phone
1. may not be provided
passenger email
1. may not be provided
Address
1. May be provided line by line
2. May be provided in a single field > decompose
3. Hotel name may be provided
  1. Look up in customer name fields (all customers) > use address found
4. May not be provided = use customer address on the booking
External Reference
1. May not be provided
Inbound Delegate company > always the customer used

Shuttle Booking > Shuttle Booking > New fields

Has Problem Flag - editable
Problem Text - all data originally imported

Shuttle booking > create booking > enter flight number > update booking details:

Look for flight number on previous bookings
1. If no values already > infer destination, direction, arrival time from found booking (check last 3 found )
  1. warn user if last 3 were different in some way (indicate differences)
User can over-write any of the fields involved
1. Warn user if found data different to booking data

Using the upload

Shuttle Bookings > Booking > Clear > Choose “Booking Entity” > upload xls by drag and drop on screen

Edit NEW MENU “Import bookings XXXXX . XLS ” for “Booking Entity Name”
1. Check all columns identified in the mapping exist
  1. if not - yellow note indicates missing mappings (not found) AND that import cannot proceed until headings/mapping fixed
2. If all mappings exist > create bookings
  1. Flag any problems found
  2. Flag all bookings with no problem with the problem “Booking to be reviewed” if import has all bookings to be flagged as a problem.

Note that

Importing Shuttle Bookings Mapping