System Overview

Overview

The system consists of several key areas:

Database

This currently is configured for all installation on a SQL Server DB. Within the database we have several tables and a series of stored procedures to access and manipulate data. The stored procedures are called from the data access layer, no sql is manually executed from here.

Data Access Layer

This is sometimes refered to as the server. In reality this is a series of C# classes that represent the database tables as objects and allow interaction with the stored procedures. Some business logic is excuted here in, moving forward more business logic should be included in here.

Web Services

The webservices are .Net asmx services. These act as both SOAP and Restful webservices and are accessible through a standardised URL. There are two sets of service:

• Calidus_ePOD.asmx

This is used exclusively by the PDA Clients for communications with the server.

• DataServices

These are a set of services that allow clients to import data and request the export of data from the system

Web Application

The web application provides a graphical user interface allows users to access and modify data within the database. This utilises ASP.Net, C# (for the code behind), CSS and Javascript (jQuery and jQuery UI).

ePOD Manager

This windows application is still in the beta phases and has not been released. The purpose of this application is to allow administrative tasks such as archiving and clear downs to be run seperately from the Web Application.

ePOD AutoExport

This is a windows application that uses the data access layer to retrieve all records marked for export or POD emailing, and will process these records sending either xml data of jobs or loads via soap, post, or email, or POD html or PDF via email.

PDA Client

The PDA client is used by drivers. This allows drivers to download their current workload, and complete/cancel any jobs assigned to it. The PDA has it's own local SQL Server Compact database which mirrors a subset of the data held in the main server database. All communications are performed via SOAP calls to the Calidus_ePOD.asmx update. There are two flavours of the ePOD PDA: Android and Windows Mobile 6.5.

Database

The database is currently exclusive deployed on MS SQL Server and is support from 2005 upwards. Due to the nature of the Data Access Layers implementation, the system is to some extent database agnostic, and that simply rewritting the database table and packages to support another database should be all that is required to port over to MySQL or Oracle.

The database conists of a series of tables that are required to hold the needed data for the ePOD product. This data is manipulated through stored procedures which can be call from the data access layer.

Each table generally has four stored procedures:

• TABLE_NAME_INSERT
• TABLE_NAME_UPDATE
• TABLE_NAME_SELECT
• TABLE_NAME_DELETE

There may also be the following stored procedures:

• TABLE_NAME_GET_MAXID
• TABLE_NAME_CANCEL
• TABLE_NAME_SEARCH
• TABLE_NAME_SELECT_UPDATED_DATA - Not currenly used due to improvements needed in the PDA Client and the Webservices

Tables

• EPOD_DEVICE_TYPE

Holds a list of models of devices that have logged on to the calidus_epod.asmx webservice and configuration flags such as barcode scanning and camera capture capabilities. The intention is to use this data to restrict functionality on devices where there may be a issue using the functionality, for example no camera on the device.

• EPOD_CONTAINER

Holds container level information this is linked through primary key to the job record. Container 0000000000 is always the loose product container.

• EPOD_CUSTOMER

Holds customer level information.

• EPOD_JOB_ADDRESS

Holds address and contact information similar to a customer record but is used in the scenario of a different delivery location being provided for the customer.

• EPOD_JOB

Holds Job information such as type, customer, and times.

• EPOD_LOAD

Holds load level informatiom, representing a drivers workload (a set of jobs).

• EPOD_DEVICE

Holds device specific identification details such as Unique ID, Type, OS and the last User. This is updated with every relavant communcations message from the PDA.

• EPOD_JOB_GROUPS

Holds the configuration details for a job group

• EPOD_LOTS_STATUS

This table is not currently used, but was part of the initial design for LOTS intergration.

• EPOD_PRODUCT

Holds product level information associated to the Container and Job.

• EPOD_PHOTO

Holds photo information against job, container and products, this is held as a base64 encoded jpg file.

• EPOD_REASON_CODE

Holds the static reason code information.

• EPOD_USER_AUDIT

Holds a audit of all communcations between the PDA and the server, identifying the type of communication and the GPS location.

• EPOD_SERVICE_ACTIVITY_MASTER

Holds the static activity data.

• EPOD_SERVICE

Holds the service job information, this will be linked to a job record.

• EPOD_SERVICE_PRODUCT

This table holds all products used as part of a service.

• EPOD_SERVICE_PRODUCT_MASTER

This table holds static product information.

• EPOD_SERVICE_VEHICLE_PRODUCT

This table holds a list of service products that are held in each vehicle.

• EPOD_SITE

This table is at the top of the heirarchy, allowing multiple sites to be hosted on the same database. This holds high level configuration.

• EPOD_USER_ACCESS_GROUPS

This table holds the association between users and jobs groups allow users to view jobs of a particular group within the system.

• EPOD_USER

This table contains the users details.

• EPOD_VEHICLE_CHECK

This table holds the raw xml returned from vehicle checks performed on the device.

• EPOD_VEHICLE

The vehicles within the system are held here.

• EPOD_XF_AUDIT_HEADER

This table contains all the export audit information.

• EPOD_XF_AUDIT_DETAIL

This table has not been implemented within the system, and will be used if the Audits need expanding.

• EPOD_XF_CONFIG

This holds the configuration for the system exports.

Data Access Layer

The data access layer (EPOD Server project) provides a series of classes for interacting with the database. These classes are mapped out as the following #regions:

Class
 Private Members
 Public Members
 Public Constructor
 Private Constructor
 Public Methods
 Private Methods
 Public Static Methods

Private and Public Members

The private members represent columns within the data rows. These are all C# data types. This should not be manipulated manually and should be accessed via the public members which provide Get and Set methods to access them, implying various rules, for example maximum length.

Public Constructor

This is used to create a instance of the object. Calling this with the relevant parameters (those making up the primary key) will cause the object to call the TABLE_NAME_SELECT method and load the first record returned into the public members. If the object has been found then the DBExists property will be set to true.

Private Contructor

This is used to construct the object internally within the object without calling the database. This will simply take all of the properties as parameters and will setup the object as with the public contructor. This is used when returning a <TABLE_NAME>List of objects, and will be called mutliple times rather than repeating calls to the database. This can only be and should only evey called in the scenario where your query will return mulitple records.

Public Methods

These methods typically consist of the following:

• Update() - Commits the current private memebers to the database using TABLE_NAME_INSERT or TABLE_NAME_UPDATE dependant on the DBExists property.
• Cancel() - Sets the object to cancelled status, either through setting the properties manually and calling update or calling a TABLE_NAME_CANCEL stored procedure.
• Delete() - Calls the TABLE_NAME_DELETE stored procedure deleting the object from the database.
• Complete() - Sets the object to complete
• ToXElement() - Returns a XElement object containing all private members in XML form.
• ToXElementMin() - Returns a reduced set of the above, not including any hierarchy.

Private Methods

This region contains methods internal to the objects. This will typically contain:

• GetParmeters() - Adds all the private members to the parmeters in the DbCommand object to be passed to the store procedure.
• GetParametersForSelect() - Adds all the database fields as null parmeters to the DbCommand object.
• SetNewId() - Used in the constructor methods to set a new id for the object if one is not provided.

Public Static Methods

These methods provide access to the search functionality of the system. Typically you will provide a series of parmeters, which will be passed to the TABLE_NAME_SELECT or TABLE_NAME_SEARCH. The method will then call the private constructor of the object for each row found and store the objects in a <TABLE_NAME>List which is returned. This are structured in the same way as the constructor but will use a loop to populate a list rather than simply using the first row found.

WebServices

All communcations with ePOD are currently done via WebServices supporting both SOAP and Restful. ePOD uses XML as it's communications medium.

Message Process

The message process C# class within the ePOD_Server project handles all communications from the PDA. This is called from the Calidus_epod.asmx code behind when a request is recieved. All messages are passed in as a XElement object. As all messages from the PDA are passed to a single end-point, the MessageProcess must evaluate how to process this based on the root tags of the XElment object. All responses from the Message Process are given a RESULT attribute, container ACK for success and NAK for failure. All messages processed have a record written to the EPOD_USER_AUDIT table.

ProcessEPODMessage

The ProcessEPODMessage method is the method that is called by the Calidus_ePOD.asmx code behind. The contents of the Soap request from the PDA are passed to this method as a XElement object. This method initially validates the EPOD_SITE and EPOD_USER exist and the password sent with the message are correct, if not the error is returned as a NAK xml to the Calidus_ePOD.asmx. If validation is successful this will be passed to the relevant method for processing and the results returned to Calidus_epod.asmx.

If the device type is android the EPOD_DEVICE record is inserted or updated, if inserted and a EPOD_DEVICE_TYPE record does not exists this is inserted as well.

ProcessLogonRequest

This method will process all logon requests. The devices last data update date and time are send with the login request, this is used to retrieve standing data from, EPOD_SITE,EPOD_JOB_GROUPS, EPOD_REASON_CODE, EPOD_SERVICE_ACTIVITY_MASTER, EPOD_SERVICE_PRODUCT_MASTER and EPOD_USER, where the data has been updated since this time. Version numbers are also sent across with login request from the PDA, these are passed to ProcessUpdateRequest().

ProcessUpdateRequest

These version numbers are compared to the version numbers stored within the PDA_Updates folder. If the version on the server are greater then the updated version numbers are returned with the url to access the updates as part of the response message.

ProcessLoadRequest

It will retrieve the first available load for the user of the user id passed to it and will return this as XML. It will filter out any jobs at completed or cancelled status. During the retrieval process the load will be set to inprogress status.

ProcessJobLockRequest

This message is processed by validating the desired job is still available to the current user, if not a delete message is returned. If the job is available the job is checked for updates and any are then sent to the device. The job is set to inprogress on the server at this point, and all other inprogress jobs on the drivers load are set back to pending.

ProcessAutoUpdateRequest

This message is responible for checking that a users current load is up to date. The message sent from the device will contain the current load the user is working on and a last changed date for this load. When recieved there are the following scenarios:

• The load has been removed or all jobs completed and cancelled

The method will return a message to the device advising that the load has been removed.

• The load is in the same state as when found.

The unique keys of each record at load, job, container and product are returned as XML.

• The load has been updated

The full XML for the load is returned.

• A Job, Container and Product has been updated

The updated record is return in full as XML.

ProcessVehicleCheck

This function processes completed vehicle check data from the PDA. This method will write the vehicle check record to the database and return a ACK if successful.

EPOD_DATA_SERVICE

The EPOD_DATA_SERVICE C# class handles importing of data.

Development Tools List

• Visual Studio 2010
• Visual Studio 2008 - WM 6.5 PDA only
• Titanium Studio
• NotePad++
• SQL Server Managment Studio
• SQL Server Express 2008
• SQL Server Compact
• Windows Mobile 6 SDK
• Windows Mobile 6.5 SDK
• Android SDK
• SOAPUi
• ActiveSync/Windows Mobile Device Center
• Android NDK
• Cygwin – NDK dependancy
• GMake – NDK dependancy
• Gperf – NDK dependancy
• ActivePython
• Scons
• ANT
• Visual Source Safe
• RapidSVN
• IIS