FS 315624 PROD Multi-Timezones
PROD
Multi-Timezones
CALIDUS ePOD
25th February 2014 - 0.1
Reference: FS 315624
Contents
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: Additional phase 2 changes should be entered into to allow:
- Change of time-zone on device
- Default TTM UTC time-zone
- Admin changes to:
- Display time-zones where-ever dates and times are displayed.
- Display time-zones against Customers.
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: 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 field:
- EPL_TIMEZONE - nvarchar(40), NOT NULL DEFAULT "".
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 the new field
Note: It is not necessary to add this flag as a search-able item.
Import/Export
All TTM Export messages exported through the Auto-Export application will be modified to add time-zone information, as two extra tags:
- EVENT_HEADER
- New tag EVENT_TIMEZONE added after existing tag EVENT_ACTION
- STOP_DETAIL
- New tab LOC_TIMEZONE added after existing tag STOP_COUNTRY_NAME
Affected messages:
- GPS message - Take from device field sent back and stored on EPOD_USER_AUDIT
- TRP message - EVENT_TIMEZONE take from Load record, LOC_TIMEZONE from EPOD_JOB record.
- DEL message - Take both from EPOD_JOB record.
- ARR message - Take both from EPOD_JOB record.
- OIT message - Take both from EPOD_JOB record.
- CAN message - Take both from EPOD_JOB record.
- ORD message - Take both from 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.
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, but left in local time.
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.
- Anywhere database dates and times are set by GetOBSDate and GetOBSTime, for example:
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.25 | 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 No | Document Title & ID | Version | Date |
| 1 |
A.2 Glossary
A.3 Authorised By
Matt Tipping | Project Manager | _____________________________ |
____________________(PRINT) | Client Representative | _____________________________ |
