Keystone GPS System 2007 Era

NOTE This is the from the 2007 era Delphi based GPS system. The core of this remains in Keystone dispatch and integrates with the current cloud/.net versions of Keystone.

Background

Keystone GPS is completely written on the Keystone platform using Delphi and Interbase.

Pathway for a GPS Message

• GPS Modem Transmits Signal via Cellular Network Over Internet to Programmed IP Address
• Message Received by Device Server on Communications PC
• Message Passed to Device Aggregator on Communications PC
• Device Aggregator writes message to GPTSTATUS table

Major Software Components:

• Device Server
• Device Aggregator

Database Change:

• GPTSTATUS: Stores all GPS incoming data.

Hardware: Keystone GPS is compatible with both DataRemote and Bluetree systems.

Device Configuration

GPSCOMPANIES (string)

Comma-separated list of companies containing trucks to be processed

ONJOBSTOPTIME (integer, default=6)

Number of minutes truck must be stopped to be considered on-job (0=disabled)

USEGEOFENCING (boolean, default=False)

True to automatically determine on-job and returned statuses based on GPS coordinates

USEODOMETER (boolean, default=True)

True to obtain vehicle odometer reading from GPS device

False to calculate straight-line distance from last valid GPS coordinate

USESERVERTIME (boolean, default=False)

True to use the server time for incoming status messages

False to use the time from the incoming status messages

VALIDLAT (float)

The validation point's latitude(*)

VALIDLONG (float)

The validation point's longitude(*)

VALIDRADIUS (float)

The radius from the validation point(*)

(*)All incoming GPS coordinates within the validation radius are marked as valid coordinates, those outside the radius are marked as invalid but are still recorded.

GPTSTATUS

select * from gptstatus
where truck_no='74' order by seq_no desc
rows 1 to 100

---

STATUS_COL values:

1 Begin Load
2 End Load
3 Leave Plant
4 Arrive Job
5 Begin Pour
6 End Pour
7 Leave Job
8 Arrive Plant
9 In Service
10 Out of Service
11 Clock In
12 Clock Out
13 GPS Data
14 Pre-Ticket

---

STATUS_REASON values:

0 GPS Data
1 Manual Status
2 Auto Status
3 On-job based on stop time
4 On-job based on button
5 Loaded based on batch results received
6 Pre-ticketed load released
7 Geofence entered/exited

Queries

Driver Status Analysis:

SELECT
S.driver_code,
s.truck_no,
status_datetime,
STATUS_COL,
cast(case status_col
when 0 then 'Unknown'
when 1 then 'Beg Load'
when 2 then 'End Load'
when 3 then  'To Job'
when 4 then  'On Job'
when 5 then  'Beg Pour'
when 6 then  'End Pour'
when 7 then  'Returning'
when 8 then  'At Plant' 
when 9 then  'In Serv'
when 10 then  'Out of Serv'
when 11 then  'Trk Clock In'
when 12 then  'Trk Clock Out'
when 13 then  'Travel'
when 14 then  'Pre-Ticket'
when 15 then  'Driver Clockin'
when 16 then  'Driver Clockout'
end  as char(20)) "Status",
Status_reason "Stat Rsn",
cast(case status_reason
  WHEN 0 THEN' gps'
  WHEN 1 THEN' manual'
 WHEN 2 THEN 'auto'
 WHEN 3 THEN 'onjob stop'
 WHEN 4 THEN 'onjob button'when 0 then 'Unknown'
 WHEN 5 THEN 'ld batch'
 WHEN 6 THEN 'pre tkt rls'
 WHEN 7 THEN 'geofence'
 WHEN 8 THEN 'custom'
 WHEN 9 THEN 'set order loc'
  ELSE 'Unknown'
end  as char(20)) "Reason",
s.custom_status "Mode",cs.description "Disp. Mode"
from gptstatus s
left outer join ditcuststat cs on cs.custom_status=s.custom_status
 where status_datetime 
 between :Begin and :End
and ((s.status_col IN (0,1,8,9,10,11,12,15,16))
     OR (s.custom_status<>0) )
Order by Driver_code,status_datetime