ok, take a chair
my tables
table devices
fields:
esn (an unique alphanumeric serial number)
devicetype (it says what kind of message can send and is a group)
1: FLEET (send only position)
2: GENERIC (send only position)
3: TRACKER (send ASSISTANCE or EMERGENCY or POSITION messages...)
IDSHIP (is the ship associated with device that have data in other table and can be parsed through SQL and in some case should populate marker popup description)
table ship
IDSHIP
shipname
captain
telephone
email
...etc
table name log (contain GPS messages sent from devices)
fields:
esn (IS A GROUP, it could show position history of a device with lines or markers...or both!)
lat
long
msgTimestamp (datetime of message)
MSGtype (POSITION, ALARM, EMERGENCY, FLEET, OTHER...IS A GROUP)
MANAGED (0/1)
function
FLEET: filter log table and show on map only FLEET message type in this way
- All FLEET device LAST POSITIONS: click on menu that call a module that show a map with all the markers of last position of FLEET type DEVICES (only markers)
- LAST n POSITIONS of a specific FLEET device (n is variable but 10 could be enough or choosen through SQL) a module call that show last n position on map with lines, like already implemented but with sercheable esn of FLEET type.
- LAST n POSITIONS of ALL device (group of goups) is not indispensable but here we are and i ask
EMERGENCY, ASSISTANCE:
Map of ASSISTANCE/EMERGENCY type of message (parsed through SQL) and show markers with complex popup text (button, link etc composed through SQL i think is enough)
SQL
---
should group device by esn or a GROUPFIELD field choosen in configuration...for general purpos use of the plugin i think is bettere if name of field in SQL is choose from user.
should group coordinates by type of alarm or a defined field GROUPFIELD
and should permit to choose the rappresentation
- 1 row for all record (line, circle polygon and obviously not use the field GROUPFIELD)
- 1 row for each record (line, circle polygon) a series of paths (grouped by GROUPFIELD configured)
- only markers (SQL say wich are)
should have a TOPn parameter that say how MAX record should be taken...a parameter from outside is the best(!) so users can choose number of LAST positions from a field on a form and pass to visualization SQL.
Marker popup
text inside popup should be taken from a record field (or a field generated through SQL "CONCAT")
name should be configurable through inteface (same as latitude, longitude etc. are doing now)
should allow html or code
example:
FLEET
esn lat long MSGtimestamp MSGtype MANAGED
esn1, 41.0000, 12.0000, 4364657987622, FLEET, 1
esn2, 41.0000, 12.0000, 4454656587611, FLEET, 1
esn3, 41.0000, 12.0000, 4764644447655, FLEET, 1
esn1, 40.0000, 11.0000, 4564657987634, FLEET, 1
esn2, 40.0000, 11.5000, 4564657987634, FLEET, 1
(fleet are ever managed, only alarm need managing flag)
on map i want see:
markers with last positions
last n positions devices (in this example only esn1 and esn2 have more than 1 position)
ALARM
esn lat long MSGtimestamp MSGtype MANAGED
esn1, 41.0000, 12.0000, 4364657987622, POSITION, 1
esn2, 41.0000, 12.0000, 4454656587611, ASSISTANCe, 1
esn3, 41.0000, 12.0000, 4764644447655, EMERGENCY, 1
esn1, 40.0000, 11.0000, 4564657987634, POSITION, 1
esn2, 40.0000, 11.5000, 4564657987634, ASSISTANCE, 0
i want see
alarm that are in MANAGED state=0 (esn2 in this case) and show it on map
the same SQL that extract UNMANAGED could compose HTML with a button to write MANAGED in database and call other maps for assistance point for example, but i think the limit are only imposed from fantasy implementing the right HTML in SQL.
For ALARMS: a timer (settable and resettable by user) every n second read database and show UNMANAGED ALARM marker on map..with sound...but i think this part is not the purpose of this plugin.