FS 315624 PROD Multi-Timezones - Revision history

Anw: v0.3 - extended Import/Export to identify that Timezone should be imported

2014-03-28T08:55:47Z

v0.3 - extended Import/Export to identify that Timezone should be imported

@@ Line 4: / Line 4: @@
 {{#vardefine:System|''CALIDUS'' ePOD}}
 {{#vardefine:Doc_Title|Multi-Timezones}}
-{{#vardefine:Version|0.2}}
+{{#vardefine:Version|0.3}}
-{{#vardefine:Date|5th March 2014}}
+{{#vardefine:Date|28th March 2014}}
 {{#vardefine:Reference|315624}}
 {{#vardefine:Year|2014}}
@@ Line 223: / Line 223: @@
 == Import/Export ==
 All TTM Export messages exported through the Auto-Export application will be modified to add time-zone information, as extra tags:
 * EVENT_HEADER

Anw: v0.2 - Added handling of new fields for TTM and new Audit fields.

2014-03-05T12:39:48Z

v0.2 - Added handling of new fields for TTM and new Audit fields.

← Older revision		Revision as of 14:39, 5 March 2014
Line 4:		Line 4:
	{{#vardefine:System\|''CALIDUS'' ePOD}}		{{#vardefine:System\|''CALIDUS'' ePOD}}
	{{#vardefine:Doc_Title\|Multi-Timezones}}		{{#vardefine:Doc_Title\|Multi-Timezones}}
	{{#vardefine:Version\|0.1}}		{{#vardefine:Version\|0.2}}
	{{#vardefine:Date\|~~25th February~~ 2014}}		{{#vardefine:Date\|5th March 2014}}
	{{#vardefine:Reference\|315624}}		{{#vardefine:Reference\|315624}}
	{{#vardefine:Year\|2014}}		{{#vardefine:Year\|2014}}
Line 48:		Line 48:
	{{Note}} Additional phase 2 changes should be entered into to allow:		{{Note}} Additional phase 2 changes should be entered into to allow:
	* Change of time-zone on device		* Change of time-zone on device
	* Default TTM UTC time-zone		* 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:		* Admin changes to:
	** Display time-zones where-ever dates and times are displayed.		** Display time-zones where-ever dates and times are displayed.
	** ~~Display~~ time-zones against Customers.		** Enter time-zones against Customers.
			** Enter time-zones against new Jobs.
			** Enter time-zones against Job Addresses.


Line 206:		Line 209:


	The existing table EPOD_USER_AUDIT will be modified to add the following ~~field~~:		The existing table EPOD_USER_AUDIT will be modified to add the following fields:
	* ~~EPL_TIMEZONE~~ - nvarchar(40), NOT NULL DEFAULT "".		* 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:		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_INSERT
Line 214:		Line 219:
	*EPOD_USER_AUDIT_UPDATE		*EPOD_USER_AUDIT_UPDATE
	The existing EPOD_USER_AUDIT DAL object will be changed to:		The existing EPOD_USER_AUDIT DAL object will be changed to:
	* Read the new ~~field~~		* Read, search and update the new fields
	~~{{Note}} It is not necessary to add this flag as a search-able item.~~


	== Import/Export ==		== Import/Export ==
	All TTM Export messages exported through the Auto-Export application will be modified to add time-zone information, as ~~two~~ extra tags:		All TTM Export messages exported through the Auto-Export application will be modified to add time-zone information, as extra tags:
	* EVENT_HEADER		* EVENT_HEADER
	** New tag EVENT_TIMEZONE added after existing tag EVENT_ACTION		** New tag EVENT_TIMEZONE added after existing tag EVENT_ACTION.
	* STOP_DETAIL		* STOP_DETAIL
	** New ~~tab~~ LOC_TIMEZONE added after existing tag STOP_COUNTRY_NAME		** 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

	~~Affected~~ messages:
	* GPS message - ~~Take~~ from device field sent back and stored on EPOD_USER_AUDIT		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.
	* 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.~~		Values for the messages affected:
	* OIT ~~message - Take both from EPOD_JOB record.~~		* GPS message
	* ~~CAN message - Take both from EPOD_JOB record.~~		** 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)
	* ~~ORD message - Take both~~ from EPOD_JOB record.		* 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.		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~~.		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.


Line 312:		Line 328:
	\|FS=1.5		\|FS=1.5
	\|TS=0		\|TS=0
	\|DEV=6.25		\|DEV=6.75
	\|ST=1.5		\|ST=1.5
	\|IMP=0.25		\|IMP=0.25

Anw: v0.1 - Initial Creation

2014-02-25T14:43:21Z

v0.1 - Initial Creation

New page

<div class="noprint">
{{#vardefine:Client|PROD}}
{{#vardefine:ClientName|PROD}}
{{#vardefine:System|''CALIDUS'' ePOD}}
{{#vardefine:Doc_Title|Multi-Timezones}}
{{#vardefine:Version|0.1}}
{{#vardefine:Date|25th February 2014}}
{{#vardefine:Reference|315624}}
{{#vardefine:Year|2014}}
</div>
{{Doc_Title
|Client={{#var:ClientName}}
|System={{#var:System}}
|Title={{#var:Doc_Title}}
|Reference=FS {{#var:Reference}}
|Version={{#var:Version}}
|Date={{#var:Date}}
|Year={{#var:Year}}
}}


<div class="noprint">
= 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 {{#var:System}} 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 {{#var:System}} 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 {{#var:System}} 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.

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.



{{Doc_Appendix
|Appendix=A
|Estimate=Y
|Glossary={{#var:System}}
|REQ=0
|EST=0
|FS=1.5
|TS=0
|DEV=6.25
|ST=1.5
|IMP=0.25
|EREQ=0
|EEST=0
|EFS=0
|ETS=0
|EDEV=0
|ESTT=0
|EIMP=0
|Client={{#var:Client}}
|Year={{#var:Year}}
|FSEST=Y
|FOC=Y
|Rev1=Matt Tipping
|Rev1Title=Project Manager
|Rev2Title=Client Representative
}}</div>
[[Category:{{#var:Client}} FS]]