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
| Table of Contents |
|---|
Overview
When customers send bookings in xls they can be imported to the system
Using the importing process to create bookings
Open the shuttle booking screen
Select the Booking Entity
Note - The cash debtor entity allows bookings to be imported across Booking Entitities
Must have a Booking Entity column mapped
If the Hotel column mapping does NOT find a hotel
Then it will look for a suburb / postcode combination in the Hotel column
setting the booking as a private address booking if found.
Drag and drop the XLS on the screen
Edit > Import “name” > Import from Mapped XLS file
If any required columns do not exist
NO bookings will be created
The user will be notified of the columns missing
All lines that are successful will create bookings
All lines that are not successful will not create bookings > a yellow note with a list of all bookings that failed will be provided
user will need to either
Fix these bookings in the sheet > remove successful bookings > reupload
or enter manually
Mapping the columns in the XLS sheet
Shuttle bookings may be provided in XLS so map the xls columns in the mapping screen.
Booking Entities must be Inbound Delegates (flagged on the Debtor)
Booking entities typically have collected money for the booking and must later pay for it.
Hotels will typically use their address for all bookings > so will not provide addresses
Tick use inbound delegate address
Booking Websites have agreements with different booking entities - this requires a partnership link if addresses are not set
Addresses may be inbound delegate partner address
Hotel column
Looks for an exact match on the hotel name (fuzzy not possible)
if none found - will look for a suburb / postcode combination > if found will set the booking as to or from a private address
Cash debtor is a special mapping that allows imports across multiple booking entities.
Booking entity is a required column
Partnerships
When a booking entity takes bookings on behalf of another entity - they are a partner. Example a travel agency that handles all the bookings for a hotel.
The Travel Agency is the inbound delegate that will pay for the booking - however the address comes from the linked Partner Address
...
Logical Flow
This is how the system uses the data provided
Travel Date provided (is mandatory)
Date is today or in the future
Flight time
Flight number provided
Yes
Infer other fields > by looking at shuttle booking history (
...
most common in last 180 days)
Destination
Direction of Travel
Flight Time (only if flight time was NOT provided)
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 (1)
Children will be added to Adults if separate columns
Excess Bags
Only if provided
Passenger name options
Single field
combine Fname / Lname fields
Passenger phone
...
passenger email
...
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
...
from Booking Entity
Pickup time may be provided
...
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
...