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)
“Data Type” Columns listed in table below
Some data type columns also require lookups saved per customer
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
Date provided (is mandatory)
Date is today or in the future
Flight number provided
Yes
Infer other fields > by looking at shuttle booking history (at least 5 bookings prior to today)
Destination
Direction of Travel
Flight Time
No
Destination must be provided
compare with lookup table for this customer
Terminal must be provided
yes - use lookup for terminal
no - default terminal setting
Flight time must be provided
Direction of Travel must be provided
Will need a lookup table that converts text provided to direction
PAX
if not provided then use default value
Excess Bags
Only if provided
Passenger name options
Single field
combine Fname / Lname fields
Passenger phone
may not be provided
passenger email
may not be provided
Address
May be provided line by line
May be provided in a single field > decompose
Hotel name may be provided
Look up in customer name fields (all customers) > use address found
May not be provided = use customer address on the booking
External Reference
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
If no values already > infer destination, direction, arrival time from found booking (check last 3 found )
warn user if last 3 were different in some way (indicate differences)
User can over-write any of the fields involved
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”
Check all columns identified in the mapping exist
if not - yellow note indicates missing mappings (not found) AND that import cannot proceed until headings/mapping fixed
If all mappings exist > create bookings
Flag any problems found
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
| Mockup | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...