{'Guid': 'VARCHAR(64) NOT NULL PRIMARY KEY', 'Package': "VARCHAR(255) NOT NULL DEFAULT ''", 'Person': 'VARCHAR(64)', 'Username': 'VARCHAR(128)', 'DeviceId': 'VARCHAR(128)', 'Tid': 'VARCHAR(8)', 'Lat': 'DECIMAL(10,7) DEFAULT 0', 'Lng': 'DECIMAL(10,7) DEFAULT 0', 'Accuracy': 'DECIMAL(8,1) DEFAULT 0', 'Speed': 'DECIMAL(6,1) DEFAULT 0', 'Battery': 'INT DEFAULT 0', 'Trigger': 'VARCHAR(4)', 'LocationTs': 'BIGINT DEFAULT 0', 'CreateTime': 'DATETIME DEFAULT CURRENT_TIMESTAMP', 'Module': 'VARCHAR(255)'}
INSERT INTO data_owntracks_location
(Guid, Package, Person, Username, DeviceId, Tid,
Lat, Lng, Accuracy, Speed, Battery,
Trigger, LocationTs, Module)
VALUES
('{guid}', '{package}', '{person}',
'{username}', '{device_id}', '{tid}',
{lat}, {lng}, {accuracy}, {speed}, {battery},
'{trigger}', {location_ts},
'ObjServiceOwnTracks')
INSERT INTO data_household
(Package, Person, Lat, Lng, Accuracy,
Speed, Battery, LocationSrc,
LastSeen, UpdateTime)
VALUES
('{package}', '{person}', {lat}, {lng},
{accuracy}, {speed}, {battery},
'owntracks', NOW(), NOW())
ON DUPLICATE KEY UPDATE
Lat = {lat},
Lng = {lng},
Accuracy = {accuracy},
Speed = {speed},
Battery = {battery},
LocationSrc = 'owntracks',
LastSeen = NOW(),
UpdateTime = CURRENT_TIMESTAMP
SELECT o1.Person, o1.Lat, o1.Lng,
o1.Accuracy, o1.Speed, o1.Battery,
o1.CreateTime
FROM data_owntracks_location o1 INNER JOIN (
SELECT Person, MAX(CreateTime) AS MaxTime
FROM data_owntracks_location
WHERE Package = '{package}'
GROUP BY Person
) o2 ON o1.Person = o2.Person
AND o1.CreateTime = o2.MaxTime
ORDER BY o1.Person
SELECT Person, Lat, Lng, Accuracy,
Speed, Battery, Trigger, CreateTime
FROM data_owntracks_location WHERE Package = '{package}'
AND Person = '{person}'
ORDER BY CreateTime DESC LIMIT 1