====== Building Your Website ======
The best part, and the whole reason, for using our XML service is that it allows you to be flexible with what your client needs; such as data integration in to a proprietary backend, booking system or a custom CMS solution. We suggest you use the example schema as a guide only, and try integrating the provided data to meet your needs instead of building your solutions around it.
\\
\\
Below we list some of the less obvious relationships that are required for your integration to have accurate data output.


===== Cruises & Specials =====
Cruises and specials are two separate //products// available within Cruise Factory, although cruises will share details with specials that have been created from them.

  * Cruises are the base product within Cruise Factory. They contain the basic details of the cruise; description, itinerary, ship/cruise line/sailing dates, indicative pricing (''priceguide'') etc.
  * Specials extend cruises by adding more layers of information; pre/post itinerary, detailed pricing, inclusions, specific sailing dates, etc.


===== Cruise pricing =====
The tables required to fetch the indicative pricing for a cruise are as follows:
  * [[structure#sailingdates|sailingdates]]
  * [[structure#priceguide|priceguide]]

These tables are related via the ''sailingdates.id'' & ''priceguide.sailing_id'' fields. A query to fetch pricing for a particular cruise would look like this:
<code sql>
SELECT
	priceguide.`sailing_id`,
	priceguide.`inside_cabin`,
	priceguide.`outside_cabin`,
	priceguide.`balcony`,
	priceguide.`suite`
FROM
	priceguide,
	sailingdates
WHERE
	sailingdates.`id`=230484
	AND sailingdates.`id`=priceguide.`sailing_id`
</code>

This may seem confusing as the cruise ID is no where to be seen. Indicative pricing is based on sailing dates instead of only a cruise, so we must find the particular sailing date ID (''sailingdates.id'') of a cruise before we can fetch it's pricing.
\\
\\
To get a particular ''sailingdate.id'' of a cruise you will need the ''cruise.id'' and ''sailingdate.sailingdate'' parameters. You can then fetch the ''sailingdate.id'' via a query like this:
<code sql>
SELECT
	sailingdate.`id`
FROM
	sailingdates
WHERE
	sailingdates.`cruise_id`=19169
	AND sailingdates.`sailingdate`="2012-04-14"
</code>
This may seem convoluted but the example queries above are only to show you a basic way of fetching a single cruise's indicative pricing. More complex queries will make more sense in actual usage.



===== Special pricing =====
Specials have a field in the ''specials'' table called ''specials.start_price'' which will quickly give you the cheapest available price for that special. The only exception to that is when using multi-sailing date pricing (''specialsmultipricing'') as different dates may have different starting (lowest) prices.
\\
\\
The tables required to fetch pricing for specials can change depending on what type of pricing has been entered for that particular special. All tables that can have pricing for specials are as follows:
  * [[structure#leadpricing|leadpricing]]
  * [[structure#companionpricing|companionpricing]]
  * [[structure#specialspricing|specialspricing]]
    * [[structure#cabins|cabins]]
  * [[structure#specialsmultipricing|specialsmultipricing]]

You may notice that ''cabins'' is situated under the ''specialspricing'' table, this is because ''specialspricing'' has specific cabin pricing and uses the ''cabins'' table to provide the associated cabin's details.


==== leadpricing ====
Lead pricing is fetched for a specific special via the ''specials.id'' & ''leadpricing.special_id'' fields. For example:

<code sql>
SELECT
	leadpricing.`price_inside`,
	leadpricing.`price_outside`,
	leadpricing.`price_balcony`
	leadpricing.`price_suites`
FROM
	leadpricing
WHERE
	leadpricing.`special_id`=1234567
</code>

All sailing dates for this special (ID: 1234567) will have the same pricing when using the ''leadpricing'' table.


==== companionpricing ====
Companion pricing can also be included on a special with lead pricing available. So you will have to check the ''companionpricing'' table for extra pricing when looking at a special.

Similarly to the leadpricing query, we need to match the ''specials.id'' & ''companionpricing.special_id'' fields, as shown below:

<code sql>
SELECT
	companionpricing.`price_inside`,
	companionpricing.`price_outside`,
	companionpricing.`price_balcony`,
	companionpricing.`price_suites`
FROM
	companionpricing
WHERE
	companionpricing.`special_id`=1234567
</code>


==== specialspricing ====
Specials pricing is specific cabin pricing. It can also be included on a special with lead/companion pricing available, so you will have to check the ''specialspricing'' table for extra pricing when looking at a special in case it is available.
\\
\\
Specific cabin pricing is related via the ''specials.id'' parameter and the ''cabins.id'' field to the ''specialspricing.special_id'' & ''specialspricing.cabin_id'' fields. For example:

<code sql>
SELECT
	cabins.`name`,
	specialspricing.`price`
FROM
	specialspricing,
	cabins
WHERE
	specialspricing.`special_id`=648311
	AND specialspricing.`cabin_id`=cabins.`id`
</code>

As you can see we are no specifying //inside//, //outside//, //balcony// or //suite// in our select query. Instead we only fetch the name of the cabin that has pricing associated with it. This can return many results as multiple cabins can have pricing per special.


==== specialsmultipricing ====
//Multi-sailing date specials are the only specials that you will not need to check the other tables for. If a special has multi-sailing date pricing then you **not** have to check the other tables for extra pricing.//
\\
\\
Multi-sailing date pricing is related via the ''specials.id'' and ''sailingdates.sailingdate'' parameters to the ''specialmultipricing'' table. In the query below we are fetching a single sailing date's pricing for a particular special.

<code sql>
SELECT
	specialmultipricing.`inside`,
	specialmultipricing.`outside`,
	specialmultipricing.`balcony`,
	specialmultipricing.`suite`
FROM
	specialmultipricing
WHERE
	specialmultipricing.`special_id`=580913
	AND specialmultipricing.`sailingdate`="2014-04-12"
</code>
===== Special Itineraries =====
//Pre/post itineraries only relate to specials. Cruises do not need to look for these//
\\
\\
Itineraries, or more specifically for this section, pre/post itineraries are rows of extra information that are used to extend the description of a cruise itinerary. Pre/post itineraries are located in the ''specialitineraries'' table and are related via the ''specials.id'' parameter to the ''specialitineraries.special_id'' field of the ''specialitineraries'' table. For example:

<code sql>
SELECT
	specialitineraries.`day`,
	specialitineraries.`activity`,
	specialitineraries.`starttime`,
	specialitineraries.`endtime`,
	specialitineraries.`type`,
	specialitineraries.`order`
FROM
	specialitineraries
WHERE
	specialitineraries.`special_id`=1234567
ORDER BY
	specialitineraries.`type` ASC,
	specialitineraries.`order` ASC
</code>

We include an ''ORDER BY'' clause in this query because it is makes it easier to format these for display. The ''specialitineraries.type'' field has two options; ''pre'' & ''post''. This determines if they are to go before (pre) the cruise itinerary portion, or after (post). The ''specialitineraries.order'' field then determines the order they display in their given section (not the ''specialitineraries.day'' field).
\\
\\
{{ :itinerary.png?nolink|}}
The ''specialitineraries.day'' field will only accumulate for the //post// type of rows. The //pre// rows will always start at day 1 (one). If there is a //pre// itinerary, it will force the cruise itinerary's start day to be higher than 1 (however many days there are for the //pre// itinerary). The picture to the right best shows how the //pre// & //post// itineraries interface with the standard cruise itineraries. Notice that the //post// itinerary start date is the **same** day as the end date for the cruise itinerary (same goes for the cruise itinerary after the //pre// itinerary).



===== Special Sailing Dates =====
Specials are for specific sailing dates, which are linked via the ''specialsailingdates'' table to the ''sailingdates'' table. Not //all// sailing dates that the related cruise has available may be available to the special; although a special may have multiple sailing dates selected, so ''specialsailingdates'' can return multiple rows. For example:

<code sql>
SELECT
	sailingdates.`sailingdate`
FROM
	sailingdates,
	specialsailingdates
WHERE
	specialsailingdates.`special_id`=1234567
	AND specialsailingdates.`sailingdate_id`=sailingdates.`id`
ORDER BY
	sailingdates.`sailingdate` ASC
</code>

The above would return all sailing dates available for the special ID 1234567.

===== Uploaded PDF Retrieval =====
To download a PDF attached to your special, you can use the following URL:
  http://images.cruisefactory.net/pdf/[SPECIAL ID]/[PDF NAME].pdf

The [SPECIAL ID] being the number corresponding to the special you’re fetching the PDF for. For example:
  http://images.cruisefactory.net/pdf/1111111/Cruise_Details.pdf
would fetch the attached PDF for the special ID 1111111.



===== eFlyer Retrieval =====
To download the eFlyer you can use the following URL:
  http://images.cruisefactory.net/eflyer/[SPECIAL ID].pdf

The [SPECIAL ID] being the number corresponding to the special you’re fetching the eflyer PDF for. For example:
  http://images.cruisefactory.net/eflyer/1111111.pdf
would fetch the eflyer PDF for the special ID 1111111.




===== Autoflyer Retrieval =====
To download a special auto-flyer use the following URL:
  http://images.cruisefactory.net/autoflyer.html?special_id=[SPECIAL_ID]&sailingdate=[YYYY-MM-DD]




===== Image Archive =====
Images are archive into gzipped tar files ready for download. They can be rather large in download size and are updated once every week. They are available as follows:
  http://images.cruisefactory.net/images/archive/destinations.tar.gz
  http://images.cruisefactory.net/images/archive/cruises.tar.gz
  http://images.cruisefactory.net/images/archive/cruiselines.tar.gz
  http://images.cruisefactory.net/images/archive/ships.tar.gz
  http://images.cruisefactory.net/images/archive/ports.tar.gz

===== Image References =====
You may link directly to the images on the Cruise Factory server or download them to your own server using a batch script.

Use the URLs below to link the image reference in the XML data to the image on the Cruise Factory server.

An example using the an image reference found in the cruise XML data:
  http://images.cruisefactory.net/images/cruises/cruise__11968102406861

==== cruises ====
  http://images.cruisefactory.net/images/cruises/

==== ports ====
  http://images.cruisefactory.net/images/ports/

==== deck plans ====
  http://images.cruisefactory.net/images/ships/deckplans/

==== ships (photos) ====
  http://images.cruisefactory.net/images/ships/photos/

==== ships (thumbnails) ====
  http://images.cruisefactory.net/images/ships/thumbnails/

==== ships (large) ====
  http://images.cruisefactory.net/images/ships/largeimages/

==== ship cabins ====
  http://images.cruisefactory.net/images/ships/cabins/

==== ships cabin photos ====
  http://images.cruisefactory.net/images/ships/cabinphotos/

==== dining ====
  http://images.cruisefactory.net/images/cruiselines/dining/

==== kids programs ====
  http://images.cruisefactory.net/images/cruiselines/kidsprograms/

==== destinations ====
  http://images.cruisefactory.net/images/destinations/image/

===== Incremental feed & images =====
The incremental XML feed for cruises now updates the row for each cruise that has had its image updated.

We recommend that you include functionality into your import scripts to automatically compare and update these images so that your site is always displaying the most up to date and correct images.

===== Port latitude & longitude =====
The latitude and longitude coordinates are now included for each port within the Cruise Factory database. You can [[structure#latlong|view the structure of the table here]].

To access a coordinate of a port you will need to have both the `ports` and `latlong` tables imported successfully. They will then link together via the `ports.id` and `latlong.port_id` columns.

For example, to retrieve all ports with their corresponding lat/long coordinates you could run a query as such:

<code sql>
SELECT
	ports.*,
	latlong.lat,
	latlong.long
FROM
	ports,
	latlong
WHERE
	ports.id=latlong.port_id
</code>
==== Google maps ====
As a suggested usage for the new port coordinates we recommend using [[https://developers.google.com/maps/documentation/javascript/tutorial|Google Maps Javascript API]] for creating an interactive map of the selected port for your site visitors.

If you follow the tutorial at the link above you will have an API key to access the Google API for you own site. You will then be able to generate maps that display your selected ports using the Cruise Factory provided latitude/longitude coordinates by editing this in your Google map javascript:

<code javascript>
var mapOptions = {
	center: new google.maps.LatLng(-34.397, 150.644),
	zoom: 8
};
</code>

By entering in (dynamically) the lat & long of the port into the above you will find that the map automatically centers on the location of the port in your Google map widget.

Read the [[https://developers.google.com/maps/documentation/javascript/tutorial|Google Maps JavaScript API]] for more details on how to customize your maps.

==== Amended Conditions ====
Cruise lines may require specific conditions and disclaimers for the bookings and pricing. We have a table named ''amended_conditions'' which will give you the details required by the cruise line for display on your site. Currently on a per sailing date basis, we provide the pricing disclaimer and terms & conditions text that must be displayed.

As above the required table name is ''amended_conditions'' which has 3 fields of interest:
  * **''sailingdate_id''**
    * This will link to the current sailing:
    * ''sailingdates.id = amended_conditions.sailingdate_id''.
  * **''disclaimer''**
    * This text must be displayed. Suggested under the pricing.
  * **''conditions''**
    * This text must be displayed.
    * Will usually be much longer text and should be displayed under a "Terms & Conditions" heading.