FS 315624 PROD Multi-Timezones

From Calidus HUB





Aptean Logo.png







PROD

Multi-Timezones

CALIDUS ePOD

28th March 2014 - 0.3
Reference: FS 315624











































Functional Overview

Client Requirement

The system will soon be operated in Australia and New Zealand, which contain numerous time-zones, some that operate without daylight-saving. In order to achieve that, the system must be made so that can operate without time-zone issues on a basic level.


Solution Overview

In order to allow for multiple time-zones the system will be modified as follows:

  • All dates and times from the PDA to the server will be in local device time-zone.
  • All dates and times in message data from the server to the PDA is assumed to be in local device time-zone.
  • Last Changed date and times sent from the PDA to the server will be in server time-zone.
  • Last Update date and time (from a Login Request message) from the PDA to the server will be in local time-zone and will be converted to server time-zone by the server.
  • IANA time-zone will be added to every message from the PDA to the server, set from the device's local time-zone.
  • When present, the server will store the IANA time-zone against User Audit records.
  • All CALIDUS TTM messages will be tagged with new time-zone fields, storing the same data.
  • Time-zone will be added to Load and Job web-service imports.


Scope

  • This functionality will be developed in the latest version of CALIDUS ePOD only.
  • This functionality will be developed in the Android application only and will not be available in the Windows Mobile application.
  • CALIDUS TMS functionality is mentioned only by reference in this solution - see elsewhere for a specification of the changes.
  • The customer requiring the modification in the first instance (LFS) confirms that a device does not stray from a time-zone during the operation of a trip, therefore there is no need to display the time-zone on the device.
  • No changes will be made to the CALIDUS ePOD Admin system to identify the time-zone.


Note Note: Additional phase 2 changes should be entered into to allow:

  • Change of time-zone on device
  • Default CALIDUS TTM UTC time-zone
  • Properly stamp CALIDUS TTM "CAN" records with the timezone of the position where they were cancelled.
  • Admin changes to:
    • Display time-zones where-ever dates and times are displayed.
    • Enter time-zones against Customers.
    • Enter time-zones against new Jobs.
    • Enter time-zones against Job Addresses.


Set-up

Pre-requisites

  • A working CALIDUS ePOD system.


Menu Structure

None


Data

None


Functional Description

Database/DAL

The existing EPOD_LOAD table will be modified to add new fields as follows:

  • EPL_TIMEZONE - nvarchar(40), NOT NULL DEFAULT "".

Existing packages will be modified to allow the creating, editing and selecting of the new field, including but not limited to:

  • EPOD_LOAD_INSERT
  • EPOD_LOAD_SEARCH
  • EPOD_LOAD_SELECT
  • EPOD_LOAD_SELECT_DATE
  • EPOD_LOAD_SELECT_PDA
  • EPOD_LOAD_UPDATE

The XML Export of EPOD_LOAD (through the DAL) will be modified to add the new field to the end of the EPOD_LOAD section. The XML export format in this case will be as follows: 

 <EPOD_LOAD>
   <EPL_SITE_ID></EPL_SITE_ID>
   <EPL_LOAD_ID></EPL_LOAD_ID>
   <EPL_LOAD_START_PLANNED_DATE></EPL_LOAD_START_PLANNED_DATE>
   <EPL_LOAD_START_PLANNED_TIME></EPL_LOAD_START_PLANNED_TIME>
   <EPL_LOAD_END_PLANNED_DATE></EPL_LOAD_END_PLANNED_DATE>
   <EPL_LOAD_END_PLANNED_TIME></EPL_LOAD_END_PLANNED_TIME>
   <EPL_LOAD_START_ACTUAL_DATE></EPL_LOAD_START_ACTUAL_DATE>
   <EPL_LOAD_START_ACTUAL_TIME></EPL_LOAD_START_ACTUAL_TIME>
   <EPL_LOAD_END_ACTUAL_DATE></EPL_LOAD_END_ACTUAL_DATE>
   <EPL_LOAD_END_ACTUAL_TIME></EPL_LOAD_END_ACTUAL_TIME>
   <EPL_LOAD_DISTANCE_PLANNED></EPL_LOAD_DISTANCE_PLANNED>
   <EPL_LOAD_DISTANCE_ACTUAL></EPL_LOAD_DISTANCE_ACTUAL>
   <EPL_VEHICLE_ID></EPL_VEHICLE_ID>
   <EPL_USER_ID></EPL_USER_ID>
   <EPL_STATUS></EPL_STATUS>
   <EPL_MILEAGE_START></EPL_MILEAGE_START>
   <EPL_MILEAGE_END></EPL_MILEAGE_END>
   <EPL_LAST_CHANGED_DATE></EPL_LAST_CHANGED_DATE>
   <EPL_LAST_CHANGED_TIME></EPL_LAST_CHANGED_TIME>
   <EPL_UDF_LOAD_START></EPL_UDF_LOAD_START>
   <EPL_UDF_LOAD_END></EPL_UDF_LOAD_END>
   <EPL_LOAD_INFORMATION></EPL_LOAD_INFORMATION>
   <EPL_TIMEZONE></EPL_TIMEZONE>
   <EPOD_JOBS>...</EPOD_JOBS>
 <EPOD_LOAD>


The existing EPOD_JOB table will be modified to add new fields as follows:

  • EPL_TIMEZONE - nvarchar(40), NOT NULL DEFAULT "".

Existing packages will be modified to allow the creating, editing and selecting of the new field, including but not limited to:

  • EPOD_JOB_INSERT
  • EPOD_JOB_SEARCH
  • EPOD_JOB_SELECT
  • EPOD_JOB_SELECT_DATE_RANGE
  • EPOD_JOB_UPDATE

The existing EPOD_JOB DAL object will be changed to:

  • Export the new field in XML requests
  • Read the new field

Note Note: It is not necessary to add this field as a search-able item. However, if allowing this keeps the packages and DAL objects standard in design, then this can also be done, within the DAL and the packages.

The XML export format in this case will be as follows: 

 <EPOD_JOBS>
   <EPOD_JOB>
     <EPL_SITE_ID></EPL_SITE_ID>
     <EPL_JOB_ID></EPL_JOB_ID>
     <EPL_LOAD_ID></EPL_LOAD_ID>
     <EPL_JOB_TYPE></EPL_JOB_TYPE>
     <EPL_JOB_GROUP></EPL_JOB_GROUP>
     <EPL_JOB_INSTRUCTION></EPL_JOB_INSTRUCTION>
     <EPL_JOB_SIGNATURE></EPL_JOB_SIGNATURE>
     <EPL_REASON_CODE></EPL_REASON_CODE>
     <EPL_STATUS></EPL_STATUS>
     <EPL_CUSTOMER_CODE></EPL_CUSTOMER_CODE>
     <EPL_PHOTO_ID></EPL_PHOTO_ID>
     <EPL_ENG_SIGNATURE></EPL_ENG_SIGNATURE>
     <EPL_SEQUENCE></EPL_SEQUENCE>
     <EPL_START_PLANNED_DATE></EPL_START_PLANNED_DATE>
     <EPL_START_PLANNED_TIME></EPL_START_PLANNED_TIME>
     <EPL_END_PLANNED_DATE></EPL_END_PLANNED_DATE>
     <EPL_END_PLANNED_TIME></EPL_END_PLANNED_TIME>
     <EPL_START_ACTUAL_DATE></EPL_START_ACTUAL_DATE>
     <EPL_START_ACTUAL_TIME></EPL_START_ACTUAL_TIME>
     <EPL_END_ACTUAL_DATE></EPL_END_ACTUAL_DATE>
     <EPL_END_ACTUAL_TIME></EPL_END_ACTUAL_TIME>
     <EPL_DISTANCE_PLANNED></EPL_DISTANCE_PLANNED>
     <EPL_DISTANCE_ACTUAL></EPL_DISTANCE_ACTUAL>
     <EPL_DRIVING_TIME></EPL_DRIVING_TIME>
     <EPL_CUSTOMER_NAME></EPL_CUSTOMER_NAME>
     <EPL_JOB_ADDRESS></EPL_JOB_ADDRESS>
     <EPL_ADDRESS_1></EPL_ADDRESS_1>
     <EPL_ADDRESS_2></EPL_ADDRESS_2>
     <EPL_ADDRESS_3></EPL_ADDRESS_3>
     <EPL_ADDRESS_4></EPL_ADDRESS_4>
     <EPL_ADDRESS_5></EPL_ADDRESS_5>
     <EPL_POSTCODE></EPL_POSTCODE>
     <EPL_CONTACT></EPL_CONTACT>
     <EPL_TELEPHONE></EPL_TELEPHONE>
     <EPL_EMAIL></EPL_EMAIL>
     <EPL_INVOICED></EPL_INVOICED>
     <EPL_CUST_SIGNATORY></EPL_CUST_SIGNATORY>
     <EPL_JOB_CODE></EPL_JOB_CODE>
     <EPL_CUST_REF></EPL_CUST_REF>
     <EPL_OFFICE_INSTRUCTION></EPL_OFFICE_INSTRUCTION>
     <EPL_SIGNED_UNCHECKED></EPL_SIGNED_UNCHECKED>
     <EPL_SO_NUMBER></EPL_SO_NUMBER>
     <EPL_ORDER_DATE></EPL_ORDER_DATE>
     <EPL_SALES_CONTACT></EPL_SALES_CONTACT>
     <EPL_USER_NOTES></EPL_USER_NOTES>
     <EPL_OWNER_NAME></EPL_OWNER_NAME>
     <EPL_SERVICE_LEVEL></EPL_SERVICE_LEVEL>
     <EPL_LAST_CHANGED_DATE></EPL_LAST_CHANGED_DATE>
     <EPL_LAST_CHANGED_TIME></EPL_LAST_CHANGED_TIME>
     <EPL_ARRIVAL_DATE></EPL_ARRIVAL_DATE>
     <EPL_ARRIVAL_TIME></EPL_ARRIVAL_TIME>
     <EPL_XF_VALUES></EPL_XF_VALUES>
     <EPL_EXT_REF></EPL_EXT_REF>
     <EPL_TRAILER_ID></EPL_TRAILER_ID>
     <EPL_PF_DEPOT></EPL_PF_DEPOT>
     <EPL_PF_TRACKING_NO></EPL_PF_TRACKING_NO>
     <EPL_JOB_STATUS></EPL_JOB_STATUS>
     <EPL_UDF_JOBDETS></EPL_UDF_JOBDETS>
     <EPL_LINKED_ID></EPL_LINKED_ID>
     <EPOD_JOB_ADDRESS>
       <EPL_ADDRESS_TYPE></EPL_ADDRESS_TYPE>
       <EPL_ADDRESS_1></EPL_ADDRESS_1>
       <EPL_ADDRESS_2></EPL_ADDRESS_2>
       <EPL_ADDRESS_3></EPL_ADDRESS_3>
       <EPL_ADDRESS_4></EPL_ADDRESS_4>
       <EPL_ADDRESS_5></EPL_ADDRESS_5>
       <EPL_POSTCODE></EPL_POSTCODE>
       <EPL_CONTACT></EPL_CONTACT>
       <EPL_TELEPHONE></EPL_TELEPHONE>
       <EPL_EMAIL></EPL_EMAIL>
     <EPOD_JOB_ADDRESS>
     <EPL_TIMEZONE></EPL_TIMEZONE>
     <EPOD_CONTAINERS />
     <EPOD_SERVICES />
   </EPOD_JOB>
 </EPOD_JOBS>


The existing table EPOD_USER_AUDIT will be modified to add the following fields:

  • EPL_DEVICE_TIMEZONE - nvarchar(40), NOT NULL DEFAULT "".
  • EPL_DEVICE_DATE - int, NOT NULL DEFAULT 0.
  • EPL_DEVICE_TIME - int, NOT NULL DEFAULT 0.

Existing packages will be modified to allow the creating, editing and selecting of the new fields, including but not limited to:

  • EPOD_USER_AUDIT_INSERT
  • EPOD_USER_AUDIT_SEARCH
  • EPOD_USER_AUDIT_SELECT
  • EPOD_USER_AUDIT_UPDATE

The existing EPOD_USER_AUDIT DAL object will be changed to:

  • Read, search and update the new fields


Import/Export

The generic XML Import webservices (epod_dataservice

The dataservice webservices (ePOD_DataService.asmx and ePOD_DataService2.asmx) and the AutoImport application will need to change to import the new EPOD_LOAD and EPOD_JOB fields. The dataservice webservices and the AutoExport application will also need to change to export the new fields.

The messages affected are:

  • AutoImport and ePOD_DataService:
    • EPOD_XML_IMPORT
  • AutoExport:
    • EPOD_EXPORT_LOAD
    • EPOD_EXPORT_JOB
  • ePOD_DataService
    • EPOD_EXPORT_LOAD_RESPONSE
    • EPOD_EXPORT_JOB_RESPONSE

Note Note: All XSDs should be modified to include the new field, along with the standard documentation. This includes but is not limited to:

  • EPOD_EXPORT_LOAD_RESPONSE.xsd
  • XMLUpload.xsd
  • EPOD_EXPORT_JOB.xsd
  • EPOD_EXPORT_JOB_RESPONSE.xsd
  • ePOD_ImportResponce.xsd

The field definitions should be added to the XSDs in the appropriate position as follows: 

   <xsd:element name="EPL_TIMEZONE" nillable="true" minOccurs="0" maxOccurs="1">
     <xsd:annotation>
       <xsd:documentation>
         The time-zone for the times on this record
       </xsd:documentation>
     </xsd:annotation>
     <xsd:simpleType>
       <xsd:restriction base="xsd:string">
         <xsd:maxLength value="30" />
       </xsd:restriction>
     </xsd:simpleType>
   </xsd:element>

The elements will be added at the following positions:

  • In EPOD_JOB as the last element before EPOD_CONTAINERS.
  • In EPOD_LOAD as the last element before EPOD_JOBS.


All TTM Export messages exported through the Auto-Export application will be modified to add time-zone information, as extra tags:

  • EVENT_HEADER
    • New tag EVENT_TIMEZONE added after existing tag EVENT_ACTION.
  • STOP_DETAIL
    • New tag LOC_TIMEZONE added after existing tag STOP_COUNTRY_NAME
  • GPS_POINT
    • New tag LOC_TIMEZONE added after existing tag TRIP_ID
  • ORDER_HEADER)ADDRESS
    • New tag LOC_TIMEZONE added after existing tag ADDRESS_VAT_NO


EVENT_TIMEZONE should always be set to the timezone of the EPOD Server. The existing tag EVENT_TRANSACTION_DATE should always be in the server timezone.


Values for the messages affected:

  • GPS message
    • GPS_POINT/LOC_TIMEZONE - take from the device field sent back and stored on EPOD_USER_AUDIT. The existing tag GPS_POINT_DATE should be set from the time stamped against the message (which is device local time)
  • TRP/ORD messages:
    • Each STOP_DETAIL/LOC_TIMEZONE taken from the associated EPOD_JOB records.
    • Each ORDER_HEADER_ADDRESS/LOC_TIMEZONE taken from the associated EPOD_JOB records.
  • DEL/ARR/OIT/CAN messages:
    • STOP_DETAIL/LOC_TIMEZONE taken from the associated EPOD_JOB record.


The message processor (MessageProcess.cs) called from Calidus_ePOD.asmx will be modified at the point of processing each request to store the time-zone from the new attribute TZ onto the table, through the call to the EPOD_USER_AUDIT DAL in every function.

This call will also be changed to ensure the fields are created as follows:

  • EPL_AUDIT_DATE/TIME - set from the time the message is created.
  • EPL_DEVICE_DATE/TIME - set from the REQUEST_DATE/TIME on the message from the device.

Additionally, these messages must be checked so that, in all cases where dates and times are handled, dates and times are never converted to the server's time-zone.


The message processor (MessageProcess.cs) called from Calidus_ePOD.asmx for LOGIN_REQUEST messages will be changed to check the timezone on the request. This will be used to modify the EPL_LAST_UPDATE_DATE_TIME by the time-zone modifier (if present on the message in attribute TZ) to get the time in the server's time-zone. If not present, as now, assume that the device is in the same time-zone as the server, and that no conversion is necessary.


In all of the cases above, if the time-zone is not available from the data, this should be set from the server's own time-zone. To achieve this, the application should retrieve the IANA/Olsen code of the server and use that instead - facilities exist within .NET to translate a Windows locale ID to an IANA code.


PDA

The PDA will tag all messages sent back to the server with the IANA/Olsen code. Use jstz.js to achieve this, including this at the appropriate level. This will be added as a new attribute TZ added to the top-level REQUEST tag on each web-service request message, in WebServices.js. This will be calculated at the point the message is built to be sent to the server, through a call to jstz.determine().name

An example message follows: 

 <VEHICLE_CHECK_UPDATE ID="1" SITE="ROB" USER="rmpda" PASSWORD="76" 
  DEVICE_ID="e01404b5d63cbe14" GPS_STAMP="53.291736,-2.9120364" 
  DEVICE_OS="android 4.0.3" DEVICE_TYPE="GT-P3100" TZ="Europe/London">
  ...
</VEHICLE_CHECK_UPDATE>


The population of the dates and times in these messages will always be strictly controlled, as follows:

  • All dates and times content set on the device will always be local, for example:
    • Anywhere database dates and times are set by GetOBSDate and GetOBSTime, for example:
      • EPOD_JOB: EPL_START_DATE, EPL_ARRIVAL_TIME
      • EPOD_LOAD: EPL_START_DATE
    • Anywhere XML dates are set, for example:
    • EPOD_VEHICLE_CHECK: DATE_TIME
    • Message tags will be set from UTC time, for example:
      • Every message uses REQUEST_TIME_DATE, which calls GetOBSDate and GetOBSTime.
    • Login LAST_UPDATE_DATE/TIME.

To achieve this, it must be insured that the existing GetOBSDate and GetOBSTime procedures always return the local time with no modifiers. This should be checked in these two procedures.


As Last Update times are set by the server and then stored by the EPOD device as a number, the device should have not converted these to local times. When these are returned on messages, no further changes should be made to them, to ensure that the times remain in server time. This will result in message processing on the server, where updates are sent to the device based on the Last Update date/time fields, being unaffected by time-zone differences.


Appendix A: Quote & Document References

Cost Details
Activity Estimate
No. of Days		 No. of Days Rate per Day (£) Cost (£ Exc. VAT)
Requirements 0.00 0.00 0 £0.00
Change Request Evaluation 0.00 0.00 0 £0.00
Functional Specification 0.00 1.50 0 £0.00
Technical Specification 0.00 0.00 0 £0.00
Development 0.00 6.75 0 £0.00
Testing and Release 0.00 1.50 0 £0.00
Implementation 0.00 0.25 0 £0.00
Project Management First argument to "number_format" must be a number. First argument to "number_format" must be a number. 0 £First argument to "number_format" must be a number.
 
TOTAL First argument to "number_format" must be a number. First argument to "number_format" must be a number.   £First argument to "number_format" must be a number.
Estimate excludes training, release to live and go live support.

A.1 References

Ref NoDocument Title & IDVersionDate
1   


A.2 Glossary



A.3 Authorised By


Matt Tipping
Project Manager		_____________________________

____________________(PRINT)
Client Representative		_____________________________
Retrieved from ‘https://calidusassist.adcservices.apteancloud.com/calidus-assist/OBS/index.php?title=FS_315624_PROD_Multi-Timezones&oldid=1486’