This sample software package requests the Fleettracker SOAP interface and stores the information in the Microsoft SQL Server.

Please note that this software comes without any warrenty and is only provided for demonstration purposes!

The software project

The software package including the source code can be downloaded here: Attach:fleettrackerApiWsdlClient.zip

You can use the Microsoft development tools to open the project fleettrackerApiWsdlClient.sln and build the executable fleettrackerApiWsdlClient.exe. The program is a console application that can be started from the cmd line and put into a batch mode.

Configuration

The configuration file fleettrackerApiWsdlClient.exe.config contains the information about the database connection and the connection to the SOAP interface. You can find a template file (fleettrackerApiWsdlClient.exe.template.config) in the ZIP archive. Rename the template to the configuration file name and ensure that the config file is in the same directly as your executable.

The configuration file looks like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
	<configSections>
        <section name="userSettings" type="System.Configuration.AppSettingsSection, System.Configuration, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    </configSections>
    <userSettings>
        <add key="ClientGuid" value="..." />
    	 <add key="CompanyGuid" value="..." />
    	 <add key="Url" value="http://www.fleettracker.de/api/1.0/WSShipSchedule.php" />
    	 <add key="DBConnectString" value="..." />
    </userSettings>
</configuration>

Parameters	Description
ClientGuid	This is provided by the Fleettracker support team
CompanyGuid	This is provided by the Fleettracker support team
Url	The URL to the PHP file that provides the service
DBConnectString	The Microsoft database connection string according to your database settings

Running the program

The program fleettrackerApiWsdlClient.exe requires the IMO number as the first parameter:

fleettrackerApiWsdlClient.exe <imoNo> [--verbose]

Example:

fleettrackerApiWsdlClient.exe 91234567

You can also run in verbose mode if you add the --verbose parameter.

The software requests the current ship schedule for the vessel with the IMO number as specified. Each schedule entry is written into the database. If the entry exists, then it is updated.

The database and the tables are created if they don't exist. The database name is Fleettracker.

Database table structure

The software creates the following tables:

Each row in scheduleEntry belongs to a row in schedule. The table scheduleEntry contains the 4 database indexes for the entries in scheduleAgent.

Table schedule

CREATE TABLE schedule (
id int IDENTITY(1,1) PRIMARY KEY NOT NULL, 
imoNo int NOT NULL, 
ident int NOT NULL, 
changed datetime NOT NULL, 
author varchar(64) DEFAULT NULL,
chiefname varchar(64) DEFAULT NULL, 
chiefOfficerName varchar(64) DEFAULT NULL );

CREATE NONCLUSTERED  INDEX imoNoIndex ON schedule(imoNo);
CREATE NONCLUSTERED  INDEX identIndex ON schedule(ident);

The following table explains the columns:

Column	Description
id	The database id
imoNo	The IMO number of the ship
changed	The timestamp when the schedule was changed
author	The captains name
chiefname	Chief Engineer
chiefOfficerName	Chief Officer

Table scheduleEntry

The destination entries are stored in this table:

CREATE TABLE scheduleEntry
( id  int IDENTITY(1,1) PRIMARY KEY NOT NULL, 
scheduleDbId int NOT NULL,
name varchar(64) DEFAULT NULL,
unLocationCode char(3) DEFAULT NULL,
countryCode char(2) DEFAULT NULL,
stateCode char(2) DEFAULT NULL,
country varchar(64) DEFAULT NULL,
destType varchar(64) DEFAULT NULL,
ETA datetime DEFAULT NULL,
ETB datetime DEFAULT NULL,
ETD datetime DEFAULT NULL,
EOSP datetime DEFAULT NULL,
FWE datetime DEFAULT NULL,
SBE datetime DEFAULT NULL,
BOSP datetime DEFAULT NULL,
ident varchar(64) DEFAULT NULL,
arrived int DEFAULT '0',
departed int DEFAULT '0',
canceled int DEFAULT '0',
comment varchar(255) DEFAULT '',
scheduleAgentId int DEFAULT NULL,
sCrewAgentId int DEFAULT NULL,
sPurchaseAgentId int DEFAULT NULL,
sOwnersAgentId int DEFAULT NULL,
portactivities varchar(255) NULL,
voyageNumber varchar(64) DEFAULT NULL,
portCallNotFixedYet int NOT NULL DEFAULT '0',
created datetimeDEFAULT NULL ,
lastmodified datetime DEFAULT NULL);

CREATE NONCLUSTERED  INDEX scheduleDbIdIndex ON scheduleEntry(scheduleDbId);

The following table explains the columns:

Column	Description
id	The database id
scheduleDbId	The foreign key to the entry in table schedule
name	Port name
unLocationCode	UN location code of the destination
countryCode	Country code
stateCode	The state code (for US ports)
country	Country name
destType	Destination type, can be 'HARBOUR','ANCHORAGE','DRIFTING','CANAL' or 'RANGE'
ETA	ETA
ETB	ETB
ETD	ETD
EOSP	End of Sea Passage
FWE	Finished with Engine
SBE	Standby engine
BOSP	Begin of Sea Passage
ident	External identifier for this schedule entry
arrived	If true then the destination is arrived
departed	If true then the destination is departed
canceled	If true then the destination is canceled
comment	The remarks the captain entered for this destination
scheduleAgentId	The database id of the default (charterer) agent in scheduleAgent (maybe NULL if not set)
sCrewAgentId	The database id of the crewing agent in scheduleAgent (maybe NULL if not set)
sPurchaseAgentId	The database id of the purchasing agent in scheduleAgent (maybe NULL if not set)
sOwnersAgentId	The database id of the owners agent in scheduleAgent (maybe NULL if not set)
portactivities	a list of 'LOADING','DISCHARGING','BUNKERING','AWAITING_SERVICE','FOR_REPAIRS','SHIPYARD','FOR_ORDERS','PURGING','CLEANING'
voyageNumber	The voyage number
portCallNotFixedYet	If true then the port call is not fixed yet
created	When this entry was created
lastmodified	When this entry was updated

Table scheduleAgent

CREATE TABLE scheduleAgent (
 id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
agent_ident varchar(64)  NOT NULL DEFAULT '',
name varchar(64) DEFAULT '',
phone varchar(64) DEFAULT '',
phone2 varchar(64) DEFAULT NULL,
phone3 varchar(64) DEFAULT NULL,
fax varchar(64) DEFAULT '',
mobile varchar(64) DEFAULT '',
mobile2 varchar(64) DEFAULT NULL,
mobile3 varchar(64) DEFAULT NULL,
email varchar(64) DEFAULT '',
email2 varchar(64) DEFAULT NULL ,
email3 varchar(64) DEFAULT NULL ,
address1 varchar(128) DEFAULT NULL,
address2 varchar(128) DEFAULT NULL,
address3 varchar(128) DEFAULT NULL,
zip varchar(16) DEFAULT NULL,
city varchar(64) DEFAULT NULL,
country varchar(128) NOT NULL DEFAULT '',
contact varchar(128) NOT NULL DEFAULT '',
contact2 varchar(128) DEFAULT NULL,
contact3 varchar(128) DEFAULT NULL,);

The following table explains the columns:

Column	Description
id	The database id
agent_ident	The external identifier of this agent
name	Agent company name
phone	Telephone number (1st contact)
phone2	Telephone number (2nd contact)
phone3	Telephone number (3rd contact)
fax	Fax number
mobile	Mobile number (1st contact)
mobile2	Mobile number (2nd contact)
mobile3	Mobile number (3rd contact)
email	Email (1st contact)
email2	Email (2nd contact)
email3	Email (3rd contact)
address1	Address (1st row)
address2	Address (2nd row)
address3	Address (3rd row)
zip	ZIP
city	City
Country	Country name
contact	Contact name (1st contact)
contact2	Contact name (2nd contact)
contact3	Contact name (3rd contact)