From c613143105d9f7c898dd3fb236d683b76cf8a42c Mon Sep 17 00:00:00 2001 From: Abyss777 Date: Mon, 17 Oct 2016 14:02:23 +0500 Subject: - Implement export reports to excel - Migrate csv export to /api/positions --- templates/export/events.xlsx | Bin 0 -> 14120 bytes 1 file changed, 0 insertions(+), 0 deletions(-) create mode 100644 templates/export/events.xlsx (limited to 'templates/export/events.xlsx') diff --git a/templates/export/events.xlsx b/templates/export/events.xlsx new file mode 100644 index 000000000..007265496 Binary files /dev/null and b/templates/export/events.xlsx differ -- cgit v1.2.3 From 17f5a899a08b992507b574eeb4047b1f6bfa8629 Mon Sep 17 00:00:00 2001 From: Abyss777 Date: Wed, 19 Oct 2016 18:35:28 +0500 Subject: - Filter events on server side - Use common path for templates - Filter events about geofences that user do not have access (minor security fix) - Removed unused function - Visual templates improvements --- debug.xml | 2 +- src/org/traccar/api/resource/EventResource.java | 13 ++---- src/org/traccar/database/DataManager.java | 11 +---- src/org/traccar/model/Event.java | 2 + src/org/traccar/reports/Events.java | 52 ++++++++++++------------ src/org/traccar/reports/Route.java | 6 +-- src/org/traccar/reports/Summary.java | 6 +-- src/org/traccar/reports/Trips.java | 6 +-- swagger.json | 42 ------------------- templates/export/events.xlsx | Bin 14120 -> 11830 bytes templates/export/route.xlsx | Bin 14505 -> 12398 bytes templates/export/summary.xlsx | Bin 14413 -> 11896 bytes templates/export/trips.xlsx | Bin 14666 -> 12811 bytes 13 files changed, 43 insertions(+), 97 deletions(-) (limited to 'templates/export/events.xlsx') diff --git a/debug.xml b/debug.xml index e3ee64221..3afe59253 100644 --- a/debug.xml +++ b/debug.xml @@ -242,7 +242,7 @@ - SELECT * FROM events WHERE deviceId = :deviceId AND type LIKE :type AND serverTime BETWEEN :from AND :to ORDER BY serverTime DESC; + SELECT * FROM events WHERE deviceId = :deviceId AND serverTime BETWEEN :from AND :to ORDER BY serverTime diff --git a/src/org/traccar/api/resource/EventResource.java b/src/org/traccar/api/resource/EventResource.java index 74a748ea5..c0a8f968d 100644 --- a/src/org/traccar/api/resource/EventResource.java +++ b/src/org/traccar/api/resource/EventResource.java @@ -1,14 +1,12 @@ package org.traccar.api.resource; import java.sql.SQLException; -import java.util.Collection; import javax.ws.rs.Consumes; import javax.ws.rs.GET; import javax.ws.rs.Path; import javax.ws.rs.PathParam; import javax.ws.rs.Produces; -import javax.ws.rs.QueryParam; import javax.ws.rs.core.MediaType; import org.traccar.Context; @@ -26,14 +24,9 @@ public class EventResource extends BaseResource { public Event get(@PathParam("id") long id) throws SQLException { Event event = Context.getDataManager().getEvent(id); Context.getPermissionsManager().checkDevice(getUserId(), event.getDeviceId()); + if (event.getGeofenceId() != 0) { + Context.getPermissionsManager().checkGeofence(getUserId(), event.getGeofenceId()); + } return event; } - - @GET - public Collection get( - @QueryParam("deviceId") long deviceId, @QueryParam("type") String type, - @QueryParam("interval") int interval) throws SQLException { - Context.getPermissionsManager().checkDevice(getUserId(), deviceId); - return Context.getDataManager().getLastEvents(deviceId, type, interval); - } } diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java index 58d16d7f5..e98e1429c 100644 --- a/src/org/traccar/database/DataManager.java +++ b/src/org/traccar/database/DataManager.java @@ -20,7 +20,6 @@ import java.lang.reflect.Method; import java.net.URL; import java.net.URLClassLoader; import java.sql.SQLException; -import java.util.Calendar; import java.util.Collection; import java.util.Date; @@ -332,22 +331,14 @@ public class DataManager { .executeUpdate()); } - public Collection getEvents(long deviceId, String type, Date from, Date to) throws SQLException { + public Collection getEvents(long deviceId, Date from, Date to) throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectEvents")) .setLong("deviceId", deviceId) - .setString("type", type) .setDate("from", from) .setDate("to", to) .executeQuery(Event.class); } - public Collection getLastEvents(long deviceId, String type, int interval) throws SQLException { - Calendar calendar = Calendar.getInstance(); - calendar.add(Calendar.SECOND, -interval); - Date from = calendar.getTime(); - return getEvents(deviceId, type, from, new Date()); - } - public Collection getGeofences() throws SQLException { return QueryBuilder.create(dataSource, getQuery("database.selectGeofencesAll")) .executeQuery(Geofence.class); diff --git a/src/org/traccar/model/Event.java b/src/org/traccar/model/Event.java index c3c8b5320..5b4b0e089 100644 --- a/src/org/traccar/model/Event.java +++ b/src/org/traccar/model/Event.java @@ -35,6 +35,8 @@ public class Event extends Message { public Event() { } + public static final String ALL_EVENTS = "allEvents"; + public static final String TYPE_COMMAND_RESULT = "commandResult"; public static final String TYPE_DEVICE_ONLINE = "deviceOnline"; diff --git a/src/org/traccar/reports/Events.java b/src/org/traccar/reports/Events.java index 9f58e1bf4..37bf7b227 100644 --- a/src/org/traccar/reports/Events.java +++ b/src/org/traccar/reports/Events.java @@ -23,12 +23,10 @@ import java.io.OutputStream; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; -import java.util.Comparator; import java.util.Date; import java.util.HashMap; +import java.util.Iterator; import java.util.List; -import java.util.SortedSet; -import java.util.TreeSet; import javax.json.Json; import javax.json.JsonArrayBuilder; @@ -58,9 +56,14 @@ public final class Events { JsonArrayBuilder json = Json.createArrayBuilder(); for (long deviceId: ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); - for (String type : types) { - for (Event event : Context.getDataManager().getEvents(deviceId, type, from, to)) { - json.add(JsonConverter.objectToJson(event)); + Collection events = Context.getDataManager().getEvents(deviceId, from, to); + boolean all = types.isEmpty() || types.contains(Event.ALL_EVENTS); + for (Event event : events) { + if (all || types.contains(event.getType())) { + long geofenceId = event.getGeofenceId(); + if (geofenceId == 0 || Context.getGeofenceManager().checkGeofence(userId, geofenceId)) { + json.add(JsonConverter.objectToJson(event)); + } } } } @@ -75,28 +78,27 @@ public final class Events { HashMap geofenceNames = new HashMap<>(); for (long deviceId: ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); - SortedSet eventReports = new TreeSet(new Comparator() { - @Override - public int compare(Event e1, Event e2) { - return e1.getServerTime().compareTo(e2.getServerTime()); - } - }); - for (String type : types) { - Collection events = Context.getDataManager().getEvents(deviceId, type, from, to); - for (Event event : events) { + Collection events = Context.getDataManager().getEvents(deviceId, from, to); + boolean all = types.isEmpty() || types.contains(Event.ALL_EVENTS); + for (Iterator iterator = events.iterator(); iterator.hasNext();) { + Event event = iterator.next(); + if (all || types.contains(event.getType())) { long geofenceId = event.getGeofenceId(); if (geofenceId != 0) { - Geofence geofence = Context.getGeofenceManager().getGeofence(geofenceId); - if (geofence != null) { - geofenceNames.put(geofenceId, geofence.getName()); + if (Context.getGeofenceManager().checkGeofence(userId, geofenceId)) { + Geofence geofence = Context.getGeofenceManager().getGeofence(geofenceId); + if (geofence != null) { + geofenceNames.put(geofenceId, geofence.getName()); + } } else { - geofenceNames.put(geofenceId, Long.toString(geofenceId)); + iterator.remove(); } } - eventReports.add(event); + } else { + iterator.remove(); } } - if (!eventReports.isEmpty()) { + if (!events.isEmpty()) { DeviceReport deviceEvents = new DeviceReport(); Device device = Context.getIdentityManager().getDeviceById(deviceId); deviceEvents.setDeviceName(device.getName()); @@ -107,13 +109,13 @@ public final class Events { deviceEvents.setGroupName(group.getName()); } } - deviceEvents.setObjects(eventReports); + deviceEvents.setObjects(events); devicesEvents.add(deviceEvents); } } - String templatePath = Context.getConfig().getString("report.events.template.excel", - "templates/export/events.xlsx"); - try (InputStream inputStream = new FileInputStream(templatePath)) { + String templatePath = Context.getConfig().getString("report.templatesPath", + "templates/export/"); + try (InputStream inputStream = new FileInputStream(templatePath + "/events.xlsx")) { org.jxls.common.Context jxlsContext = PoiTransformer.createInitialContext(); jxlsContext.putVar("devices", devicesEvents); jxlsContext.putVar("sheetNames", sheetNames); diff --git a/src/org/traccar/reports/Route.java b/src/org/traccar/reports/Route.java index 1bb7b564a..7cefe04a3 100644 --- a/src/org/traccar/reports/Route.java +++ b/src/org/traccar/reports/Route.java @@ -83,9 +83,9 @@ public final class Route { devicesRoutes.add(deviceRoutes); } } - String templatePath = Context.getConfig().getString("report.route.template.excel", - "templates/export/route.xlsx"); - try (InputStream inputStream = new FileInputStream(templatePath)) { + String templatePath = Context.getConfig().getString("report.templatesPath", + "templates/export/"); + try (InputStream inputStream = new FileInputStream(templatePath + "/route.xlsx")) { org.jxls.common.Context jxlsContext = PoiTransformer.createInitialContext(); jxlsContext.putVar("devices", devicesRoutes); jxlsContext.putVar("sheetNames", sheetNames); diff --git a/src/org/traccar/reports/Summary.java b/src/org/traccar/reports/Summary.java index e7f41bcd6..14f8b7839 100644 --- a/src/org/traccar/reports/Summary.java +++ b/src/org/traccar/reports/Summary.java @@ -92,9 +92,9 @@ public final class Summary { Context.getPermissionsManager().checkDevice(userId, deviceId); summaries.add(calculateSummaryResult(deviceId, from, to)); } - String templatePath = Context.getConfig().getString("report.summary.template.excel", - "templates/export/summary.xlsx"); - try (InputStream inputStream = new FileInputStream(templatePath)) { + String templatePath = Context.getConfig().getString("report.templatesPath", + "templates/export/"); + try (InputStream inputStream = new FileInputStream(templatePath + "/summary.xlsx")) { org.jxls.common.Context jxlsContext = PoiTransformer.createInitialContext(); jxlsContext.putVar("summaries", summaries); jxlsContext.putVar("from", from); diff --git a/src/org/traccar/reports/Trips.java b/src/org/traccar/reports/Trips.java index c912dc651..27863d877 100644 --- a/src/org/traccar/reports/Trips.java +++ b/src/org/traccar/reports/Trips.java @@ -202,9 +202,9 @@ public final class Trips { devicesTrips.add(deviceTrips); } } - String templatePath = Context.getConfig().getString("report.trips.template.excel", - "templates/export/trips.xlsx"); - try (InputStream inputStream = new FileInputStream(templatePath)) { + String templatePath = Context.getConfig().getString("report.templatesPath", + "templates/export/"); + try (InputStream inputStream = new FileInputStream(templatePath + "/trips.xlsx")) { org.jxls.common.Context jxlsContext = PoiTransformer.createInitialContext(); jxlsContext.putVar("devices", devicesTrips); jxlsContext.putVar("sheetNames", sheetNames); diff --git a/swagger.json b/swagger.json index cd44c953a..0e19bf5a8 100644 --- a/swagger.json +++ b/swagger.json @@ -1040,48 +1040,6 @@ } } }, - "/events": { - "get": { - "consumes": [ - "application/json" - ], - "produces": [ - "application/json" - ], - "parameters": [ - { - "name": "deviceId", - "in": "query", - "required": true, - "type": "integer" - }, - { - "name": "type", - "in": "query", - "required": true, - "type": "string" - }, - { - "name": "interval", - "in": "query", - "required": true, - "type": "integer" - } - ], - "responses": { - "200": { - "description": "OK", - "headers": {}, - "schema": { - "type": "array", - "items": { - "$ref": "#/definitions/Event" - } - } - } - } - } - }, "/events/{id}": { "get": { "consumes": [ diff --git a/templates/export/events.xlsx b/templates/export/events.xlsx index 007265496..99a3f7437 100644 Binary files a/templates/export/events.xlsx and b/templates/export/events.xlsx differ diff --git a/templates/export/route.xlsx b/templates/export/route.xlsx index c22e37268..6f74cfb45 100644 Binary files a/templates/export/route.xlsx and b/templates/export/route.xlsx differ diff --git a/templates/export/summary.xlsx b/templates/export/summary.xlsx index 6e22343ba..a975c54fe 100644 Binary files a/templates/export/summary.xlsx and b/templates/export/summary.xlsx differ diff --git a/templates/export/trips.xlsx b/templates/export/trips.xlsx index 136291181..8e13d5ff8 100644 Binary files a/templates/export/trips.xlsx and b/templates/export/trips.xlsx differ -- cgit v1.2.3 From 40d7ff2f9505150c85e87332fac9f67725beba8e Mon Sep 17 00:00:00 2001 From: Abyss777 Date: Thu, 20 Oct 2016 09:55:49 +0500 Subject: - Fix export empty reports - Cleanup templates metadata --- src/org/traccar/reports/Events.java | 22 ++++++++++------------ src/org/traccar/reports/Route.java | 22 ++++++++++------------ src/org/traccar/reports/Trips.java | 22 ++++++++++------------ templates/export/events.xlsx | Bin 11830 -> 11211 bytes templates/export/route.xlsx | Bin 12398 -> 11707 bytes templates/export/summary.xlsx | Bin 11896 -> 11209 bytes templates/export/trips.xlsx | Bin 12811 -> 12124 bytes 7 files changed, 30 insertions(+), 36 deletions(-) (limited to 'templates/export/events.xlsx') diff --git a/src/org/traccar/reports/Events.java b/src/org/traccar/reports/Events.java index 37bf7b227..77d995f56 100644 --- a/src/org/traccar/reports/Events.java +++ b/src/org/traccar/reports/Events.java @@ -98,20 +98,18 @@ public final class Events { iterator.remove(); } } - if (!events.isEmpty()) { - DeviceReport deviceEvents = new DeviceReport(); - Device device = Context.getIdentityManager().getDeviceById(deviceId); - deviceEvents.setDeviceName(device.getName()); - sheetNames.add(deviceEvents.getDeviceName()); - if (device.getGroupId() != 0) { - Group group = Context.getDeviceManager().getGroupById(device.getGroupId()); - if (group != null) { - deviceEvents.setGroupName(group.getName()); - } + DeviceReport deviceEvents = new DeviceReport(); + Device device = Context.getIdentityManager().getDeviceById(deviceId); + deviceEvents.setDeviceName(device.getName()); + sheetNames.add(deviceEvents.getDeviceName()); + if (device.getGroupId() != 0) { + Group group = Context.getDeviceManager().getGroupById(device.getGroupId()); + if (group != null) { + deviceEvents.setGroupName(group.getName()); } - deviceEvents.setObjects(events); - devicesEvents.add(deviceEvents); } + deviceEvents.setObjects(events); + devicesEvents.add(deviceEvents); } String templatePath = Context.getConfig().getString("report.templatesPath", "templates/export/"); diff --git a/src/org/traccar/reports/Route.java b/src/org/traccar/reports/Route.java index 7cefe04a3..6992c89cc 100644 --- a/src/org/traccar/reports/Route.java +++ b/src/org/traccar/reports/Route.java @@ -68,20 +68,18 @@ public final class Route { for (long deviceId: ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); Collection positions = Context.getDataManager().getPositions(deviceId, from, to); - if (positions != null && !positions.isEmpty()) { - DeviceReport deviceRoutes = new DeviceReport(); - Device device = Context.getIdentityManager().getDeviceById(deviceId); - deviceRoutes.setDeviceName(device.getName()); - sheetNames.add(deviceRoutes.getDeviceName()); - if (device.getGroupId() != 0) { - Group group = Context.getDeviceManager().getGroupById(device.getGroupId()); - if (group != null) { - deviceRoutes.setGroupName(group.getName()); - } + DeviceReport deviceRoutes = new DeviceReport(); + Device device = Context.getIdentityManager().getDeviceById(deviceId); + deviceRoutes.setDeviceName(device.getName()); + sheetNames.add(deviceRoutes.getDeviceName()); + if (device.getGroupId() != 0) { + Group group = Context.getDeviceManager().getGroupById(device.getGroupId()); + if (group != null) { + deviceRoutes.setGroupName(group.getName()); } - deviceRoutes.setObjects(positions); - devicesRoutes.add(deviceRoutes); } + deviceRoutes.setObjects(positions); + devicesRoutes.add(deviceRoutes); } String templatePath = Context.getConfig().getString("report.templatesPath", "templates/export/"); diff --git a/src/org/traccar/reports/Trips.java b/src/org/traccar/reports/Trips.java index 27863d877..705644849 100644 --- a/src/org/traccar/reports/Trips.java +++ b/src/org/traccar/reports/Trips.java @@ -187,20 +187,18 @@ public final class Trips { for (long deviceId: ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); Collection trips = detectTrips(deviceId, from, to); - if (!trips.isEmpty()) { - DeviceReport deviceTrips = new DeviceReport(); - Device device = Context.getIdentityManager().getDeviceById(deviceId); - deviceTrips.setDeviceName(device.getName()); - sheetNames.add(deviceTrips.getDeviceName()); - if (device.getGroupId() != 0) { - Group group = Context.getDeviceManager().getGroupById(device.getGroupId()); - if (group != null) { - deviceTrips.setGroupName(group.getName()); - } + DeviceReport deviceTrips = new DeviceReport(); + Device device = Context.getIdentityManager().getDeviceById(deviceId); + deviceTrips.setDeviceName(device.getName()); + sheetNames.add(deviceTrips.getDeviceName()); + if (device.getGroupId() != 0) { + Group group = Context.getDeviceManager().getGroupById(device.getGroupId()); + if (group != null) { + deviceTrips.setGroupName(group.getName()); } - deviceTrips.setObjects(detectTrips(deviceId, from, to)); - devicesTrips.add(deviceTrips); } + deviceTrips.setObjects(trips); + devicesTrips.add(deviceTrips); } String templatePath = Context.getConfig().getString("report.templatesPath", "templates/export/"); diff --git a/templates/export/events.xlsx b/templates/export/events.xlsx index 99a3f7437..4f9569324 100644 Binary files a/templates/export/events.xlsx and b/templates/export/events.xlsx differ diff --git a/templates/export/route.xlsx b/templates/export/route.xlsx index 6f74cfb45..efd659e64 100644 Binary files a/templates/export/route.xlsx and b/templates/export/route.xlsx differ diff --git a/templates/export/summary.xlsx b/templates/export/summary.xlsx index a975c54fe..a989e0d19 100644 Binary files a/templates/export/summary.xlsx and b/templates/export/summary.xlsx differ diff --git a/templates/export/trips.xlsx b/templates/export/trips.xlsx index 8e13d5ff8..8e214a8a3 100644 Binary files a/templates/export/trips.xlsx and b/templates/export/trips.xlsx differ -- cgit v1.2.3 From 313a51dbf23ca3ee0d5ac1d1dc37d0ff05cb2abd Mon Sep 17 00:00:00 2001 From: Anton Tananaev Date: Sun, 6 Nov 2016 10:36:16 +1300 Subject: Use distance units in Excel reports --- src/org/traccar/reports/Events.java | 2 ++ src/org/traccar/reports/ReportUtils.java | 24 ++++++++++++++++++++---- src/org/traccar/reports/Route.java | 2 ++ src/org/traccar/reports/Summary.java | 2 ++ src/org/traccar/reports/Trips.java | 2 ++ templates/export/events.xlsx | Bin 11211 -> 8020 bytes templates/export/route.xlsx | Bin 11707 -> 8508 bytes templates/export/summary.xlsx | Bin 11209 -> 8081 bytes templates/export/trips.xlsx | Bin 12124 -> 8911 bytes 9 files changed, 28 insertions(+), 4 deletions(-) (limited to 'templates/export/events.xlsx') diff --git a/src/org/traccar/reports/Events.java b/src/org/traccar/reports/Events.java index 0ad6ab309..9d82b97a5 100644 --- a/src/org/traccar/reports/Events.java +++ b/src/org/traccar/reports/Events.java @@ -116,6 +116,8 @@ public final class Events { jxlsContext.putVar("geofenceNames", geofenceNames); jxlsContext.putVar("from", from); jxlsContext.putVar("to", to); + jxlsContext.putVar("distanceUnit", ReportUtils.getDistanceUnit(userId)); + jxlsContext.putVar("speedUnit", ReportUtils.getSpeedUnit(userId)); jxlsContext.putVar("bracketsRegex", "[\\{\\}\"]"); Transformer transformer = TransformerFactory.createTransformer(inputStream, outputStream); List xlsAreas = new XlsCommentAreaBuilder(transformer).build(); diff --git a/src/org/traccar/reports/ReportUtils.java b/src/org/traccar/reports/ReportUtils.java index 6cbda0ede..7fcc767e9 100644 --- a/src/org/traccar/reports/ReportUtils.java +++ b/src/org/traccar/reports/ReportUtils.java @@ -16,20 +16,36 @@ */ package org.traccar.reports; +import org.traccar.Context; +import org.traccar.helper.Log; +import org.traccar.model.Position; + import java.math.BigDecimal; import java.math.RoundingMode; import java.util.ArrayList; import java.util.Collection; -import org.traccar.Context; -import org.traccar.helper.Log; -import org.traccar.model.Position; - public final class ReportUtils { private ReportUtils() { } + public static String getDistanceUnit(long userId) { + String unit = Context.getPermissionsManager().getUser(userId).getDistanceUnit(); + if (unit == null) { + unit = Context.getPermissionsManager().getServer().getDistanceUnit(); + } + return unit != null ? unit : "km"; + } + + public static String getSpeedUnit(long userId) { + String unit = Context.getPermissionsManager().getUser(userId).getSpeedUnit(); + if (unit == null) { + unit = Context.getPermissionsManager().getServer().getSpeedUnit(); + } + return unit != null ? unit : "kn"; + } + public static Collection getDeviceList(Collection deviceIds, Collection groupIds) { Collection result = new ArrayList<>(); result.addAll(deviceIds); diff --git a/src/org/traccar/reports/Route.java b/src/org/traccar/reports/Route.java index 9ac183aea..b29e04b2e 100644 --- a/src/org/traccar/reports/Route.java +++ b/src/org/traccar/reports/Route.java @@ -83,6 +83,8 @@ public final class Route { jxlsContext.putVar("sheetNames", sheetNames); jxlsContext.putVar("from", from); jxlsContext.putVar("to", to); + jxlsContext.putVar("distanceUnit", ReportUtils.getDistanceUnit(userId)); + jxlsContext.putVar("speedUnit", ReportUtils.getSpeedUnit(userId)); jxlsContext.putVar("bracketsRegex", "[\\{\\}\"]"); Transformer transformer = TransformerFactory.createTransformer(inputStream, outputStream); List xlsAreas = new XlsCommentAreaBuilder(transformer).build(); diff --git a/src/org/traccar/reports/Summary.java b/src/org/traccar/reports/Summary.java index d008f5d88..95a3737a1 100644 --- a/src/org/traccar/reports/Summary.java +++ b/src/org/traccar/reports/Summary.java @@ -91,6 +91,8 @@ public final class Summary { jxlsContext.putVar("summaries", summaries); jxlsContext.putVar("from", from); jxlsContext.putVar("to", to); + jxlsContext.putVar("distanceUnit", ReportUtils.getDistanceUnit(userId)); + jxlsContext.putVar("speedUnit", ReportUtils.getSpeedUnit(userId)); JxlsHelper.getInstance().setUseFastFormulaProcessor(false) .processTemplate(inputStream, outputStream, jxlsContext); } diff --git a/src/org/traccar/reports/Trips.java b/src/org/traccar/reports/Trips.java index 5a3a9a355..91a080d45 100644 --- a/src/org/traccar/reports/Trips.java +++ b/src/org/traccar/reports/Trips.java @@ -202,6 +202,8 @@ public final class Trips { jxlsContext.putVar("sheetNames", sheetNames); jxlsContext.putVar("from", from); jxlsContext.putVar("to", to); + jxlsContext.putVar("distanceUnit", ReportUtils.getDistanceUnit(userId)); + jxlsContext.putVar("speedUnit", ReportUtils.getSpeedUnit(userId)); Transformer transformer = TransformerFactory.createTransformer(inputStream, outputStream); List xlsAreas = new XlsCommentAreaBuilder(transformer).build(); for (Area xlsArea : xlsAreas) { diff --git a/templates/export/events.xlsx b/templates/export/events.xlsx index 4f9569324..63c24d0ce 100644 Binary files a/templates/export/events.xlsx and b/templates/export/events.xlsx differ diff --git a/templates/export/route.xlsx b/templates/export/route.xlsx index efd659e64..fbddfd06d 100644 Binary files a/templates/export/route.xlsx and b/templates/export/route.xlsx differ diff --git a/templates/export/summary.xlsx b/templates/export/summary.xlsx index a989e0d19..c61258844 100644 Binary files a/templates/export/summary.xlsx and b/templates/export/summary.xlsx differ diff --git a/templates/export/trips.xlsx b/templates/export/trips.xlsx index 8e214a8a3..795189d16 100644 Binary files a/templates/export/trips.xlsx and b/templates/export/trips.xlsx differ -- cgit v1.2.3 From 94464b86bcd2c1ebf8505c0adab1cb80a15194d5 Mon Sep 17 00:00:00 2001 From: Abyss777 Date: Mon, 7 Nov 2016 16:59:02 +0500 Subject: Fix export templates --- templates/export/events.xlsx | Bin 8020 -> 12941 bytes templates/export/route.xlsx | Bin 8508 -> 13435 bytes templates/export/summary.xlsx | Bin 8081 -> 12962 bytes templates/export/trips.xlsx | Bin 8911 -> 13790 bytes 4 files changed, 0 insertions(+), 0 deletions(-) (limited to 'templates/export/events.xlsx') diff --git a/templates/export/events.xlsx b/templates/export/events.xlsx index 63c24d0ce..8486cfcdb 100644 Binary files a/templates/export/events.xlsx and b/templates/export/events.xlsx differ diff --git a/templates/export/route.xlsx b/templates/export/route.xlsx index fbddfd06d..3ca8fb7c9 100644 Binary files a/templates/export/route.xlsx and b/templates/export/route.xlsx differ diff --git a/templates/export/summary.xlsx b/templates/export/summary.xlsx index c61258844..ec6cedd0f 100644 Binary files a/templates/export/summary.xlsx and b/templates/export/summary.xlsx differ diff --git a/templates/export/trips.xlsx b/templates/export/trips.xlsx index 795189d16..1f6157e76 100644 Binary files a/templates/export/trips.xlsx and b/templates/export/trips.xlsx differ -- cgit v1.2.3 From f3ea84458b126c2ebd2dd256bb3a821f0f34273c Mon Sep 17 00:00:00 2001 From: Abyss777 Date: Sat, 12 Nov 2016 11:50:11 +0500 Subject: Fix warning about print scale value --- templates/export/events.xlsx | Bin 12941 -> 12307 bytes templates/export/route.xlsx | Bin 13435 -> 12806 bytes templates/export/summary.xlsx | Bin 12962 -> 12330 bytes templates/export/trips.xlsx | Bin 13790 -> 13161 bytes 4 files changed, 0 insertions(+), 0 deletions(-) (limited to 'templates/export/events.xlsx') diff --git a/templates/export/events.xlsx b/templates/export/events.xlsx index 8486cfcdb..f0ff05a23 100644 Binary files a/templates/export/events.xlsx and b/templates/export/events.xlsx differ diff --git a/templates/export/route.xlsx b/templates/export/route.xlsx index 3ca8fb7c9..24027523d 100644 Binary files a/templates/export/route.xlsx and b/templates/export/route.xlsx differ diff --git a/templates/export/summary.xlsx b/templates/export/summary.xlsx index ec6cedd0f..88788f82a 100644 Binary files a/templates/export/summary.xlsx and b/templates/export/summary.xlsx differ diff --git a/templates/export/trips.xlsx b/templates/export/trips.xlsx index 1f6157e76..83d22678e 100644 Binary files a/templates/export/trips.xlsx and b/templates/export/trips.xlsx differ -- cgit v1.2.3 From b14169a2b7bfcaecaa0f5ea9baafad4e7e4f1ae2 Mon Sep 17 00:00:00 2001 From: Abyss777 Date: Wed, 23 Nov 2016 11:41:11 +0500 Subject: - Pass client timezone to excel template - Templates: adjust timezone, use "https" in links --- src/org/traccar/api/resource/ReportResource.java | 12 ++++-------- src/org/traccar/helper/DateUtil.java | 6 ++++++ src/org/traccar/reports/Events.java | 9 +++++++-- src/org/traccar/reports/Route.java | 10 ++++++++-- src/org/traccar/reports/Summary.java | 9 +++++++-- src/org/traccar/reports/Trips.java | 9 +++++++-- templates/export/events.xlsx | Bin 12307 -> 12346 bytes templates/export/route.xlsx | Bin 12806 -> 13042 bytes templates/export/summary.xlsx | Bin 12330 -> 12315 bytes templates/export/trips.xlsx | Bin 13161 -> 13197 bytes 10 files changed, 39 insertions(+), 16 deletions(-) (limited to 'templates/export/events.xlsx') diff --git a/src/org/traccar/api/resource/ReportResource.java b/src/org/traccar/api/resource/ReportResource.java index 2717fe01e..e8e80fa2f 100644 --- a/src/org/traccar/api/resource/ReportResource.java +++ b/src/org/traccar/api/resource/ReportResource.java @@ -50,8 +50,7 @@ public class ReportResource extends BaseResource { @QueryParam("deviceId") final List deviceIds, @QueryParam("groupId") final List groupIds, @QueryParam("from") String from, @QueryParam("to") String to) throws SQLException, IOException { ByteArrayOutputStream stream = new ByteArrayOutputStream(); - Route.getExcel(stream, getUserId(), deviceIds, groupIds, - DateUtil.parseDate(from), DateUtil.parseDate(to)); + Route.getExcel(stream, getUserId(), deviceIds, groupIds, from, to); return Response.ok(stream.toByteArray()) .header(HttpHeaders.CONTENT_DISPOSITION, CONTENT_DISPOSITION_VALUE_XLSX).build(); @@ -76,8 +75,7 @@ public class ReportResource extends BaseResource { @QueryParam("type") final List types, @QueryParam("from") String from, @QueryParam("to") String to) throws SQLException, IOException { ByteArrayOutputStream stream = new ByteArrayOutputStream(); - Events.getExcel(stream, getUserId(), deviceIds, groupIds, types, - DateUtil.parseDate(from), DateUtil.parseDate(to)); + Events.getExcel(stream, getUserId(), deviceIds, groupIds, types, from, to); return Response.ok(stream.toByteArray()) .header(HttpHeaders.CONTENT_DISPOSITION, CONTENT_DISPOSITION_VALUE_XLSX).build(); @@ -100,8 +98,7 @@ public class ReportResource extends BaseResource { @QueryParam("deviceId") final List deviceIds, @QueryParam("groupId") final List groupIds, @QueryParam("from") String from, @QueryParam("to") String to) throws SQLException, IOException { ByteArrayOutputStream stream = new ByteArrayOutputStream(); - Summary.getExcel(stream, getUserId(), deviceIds, groupIds, - DateUtil.parseDate(from), DateUtil.parseDate(to)); + Summary.getExcel(stream, getUserId(), deviceIds, groupIds, from, to); return Response.ok(stream.toByteArray()) .header(HttpHeaders.CONTENT_DISPOSITION, CONTENT_DISPOSITION_VALUE_XLSX).build(); @@ -124,8 +121,7 @@ public class ReportResource extends BaseResource { @QueryParam("deviceId") final List deviceIds, @QueryParam("groupId") final List groupIds, @QueryParam("from") String from, @QueryParam("to") String to) throws SQLException, IOException { ByteArrayOutputStream stream = new ByteArrayOutputStream(); - Trips.getExcel(stream, getUserId(), deviceIds, groupIds, - DateUtil.parseDate(from), DateUtil.parseDate(to)); + Trips.getExcel(stream, getUserId(), deviceIds, groupIds, from, to); return Response.ok(stream.toByteArray()) .header(HttpHeaders.CONTENT_DISPOSITION, CONTENT_DISPOSITION_VALUE_XLSX).build(); diff --git a/src/org/traccar/helper/DateUtil.java b/src/org/traccar/helper/DateUtil.java index ad8534eb8..840f37a7a 100644 --- a/src/org/traccar/helper/DateUtil.java +++ b/src/org/traccar/helper/DateUtil.java @@ -18,12 +18,14 @@ package org.traccar.helper; import java.util.Calendar; import java.util.Date; +import org.joda.time.DateTime; import org.joda.time.format.DateTimeFormatter; import org.joda.time.format.ISODateTimeFormat; public final class DateUtil { private static final DateTimeFormatter DATE_FORMAT = ISODateTimeFormat.dateTime(); + private static final DateTimeFormatter DATE_FORMAT_NOMILLIS = ISODateTimeFormat.dateTimeNoMillis(); private DateUtil() { } @@ -64,4 +66,8 @@ public final class DateUtil { return DATE_FORMAT.parseDateTime(value).toDate(); } + public static DateTime parseDateTime(String value) { + return DATE_FORMAT_NOMILLIS.withOffsetParsed().parseDateTime(value); + } + } diff --git a/src/org/traccar/reports/Events.java b/src/org/traccar/reports/Events.java index 9d82b97a5..ada1580d9 100644 --- a/src/org/traccar/reports/Events.java +++ b/src/org/traccar/reports/Events.java @@ -28,6 +28,7 @@ import java.util.HashMap; import java.util.Iterator; import java.util.List; +import org.joda.time.DateTime; import org.jxls.area.Area; import org.jxls.builder.xls.XlsCommentAreaBuilder; import org.jxls.common.CellRef; @@ -36,6 +37,7 @@ import org.jxls.transform.Transformer; import org.jxls.transform.poi.PoiTransformer; import org.jxls.util.TransformerFactory; import org.traccar.Context; +import org.traccar.helper.DateUtil; import org.traccar.model.Device; import org.traccar.model.Event; import org.traccar.model.Geofence; @@ -68,13 +70,15 @@ public final class Events { public static void getExcel(OutputStream outputStream, long userId, Collection deviceIds, Collection groupIds, - Collection types, Date from, Date to) throws SQLException, IOException { + Collection types, String fromString, String toString) throws SQLException, IOException { ArrayList devicesEvents = new ArrayList<>(); ArrayList sheetNames = new ArrayList<>(); + DateTime from = DateUtil.parseDateTime(fromString); + DateTime to = DateUtil.parseDateTime(toString); HashMap geofenceNames = new HashMap<>(); for (long deviceId: ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); - Collection events = Context.getDataManager().getEvents(deviceId, from, to); + Collection events = Context.getDataManager().getEvents(deviceId, from.toDate(), to.toDate()); boolean all = types.isEmpty() || types.contains(Event.ALL_EVENTS); for (Iterator iterator = events.iterator(); iterator.hasNext();) { Event event = iterator.next(); @@ -118,6 +122,7 @@ public final class Events { jxlsContext.putVar("to", to); jxlsContext.putVar("distanceUnit", ReportUtils.getDistanceUnit(userId)); jxlsContext.putVar("speedUnit", ReportUtils.getSpeedUnit(userId)); + jxlsContext.putVar("timezone", from.getZone()); jxlsContext.putVar("bracketsRegex", "[\\{\\}\"]"); Transformer transformer = TransformerFactory.createTransformer(inputStream, outputStream); List xlsAreas = new XlsCommentAreaBuilder(transformer).build(); diff --git a/src/org/traccar/reports/Route.java b/src/org/traccar/reports/Route.java index b29e04b2e..f438c8f4d 100644 --- a/src/org/traccar/reports/Route.java +++ b/src/org/traccar/reports/Route.java @@ -26,6 +26,7 @@ import java.util.Collection; import java.util.Date; import java.util.List; +import org.joda.time.DateTime; import org.jxls.area.Area; import org.jxls.builder.xls.XlsCommentAreaBuilder; import org.jxls.common.CellRef; @@ -34,6 +35,7 @@ import org.jxls.transform.Transformer; import org.jxls.transform.poi.PoiTransformer; import org.jxls.util.TransformerFactory; import org.traccar.Context; +import org.traccar.helper.DateUtil; import org.traccar.model.Device; import org.traccar.model.Group; import org.traccar.model.Position; @@ -56,12 +58,15 @@ public final class Route { public static void getExcel(OutputStream outputStream, long userId, Collection deviceIds, Collection groupIds, - Date from, Date to) throws SQLException, IOException { + String fromString, String toString) throws SQLException, IOException { ArrayList devicesRoutes = new ArrayList<>(); ArrayList sheetNames = new ArrayList<>(); + DateTime from = DateUtil.parseDateTime(fromString); + DateTime to = DateUtil.parseDateTime(toString); for (long deviceId: ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); - Collection positions = Context.getDataManager().getPositions(deviceId, from, to); + Collection positions = Context.getDataManager() + .getPositions(deviceId, from.toDate(), to.toDate()); DeviceReport deviceRoutes = new DeviceReport(); Device device = Context.getIdentityManager().getDeviceById(deviceId); deviceRoutes.setDeviceName(device.getName()); @@ -85,6 +90,7 @@ public final class Route { jxlsContext.putVar("to", to); jxlsContext.putVar("distanceUnit", ReportUtils.getDistanceUnit(userId)); jxlsContext.putVar("speedUnit", ReportUtils.getSpeedUnit(userId)); + jxlsContext.putVar("timezone", from.getZone()); jxlsContext.putVar("bracketsRegex", "[\\{\\}\"]"); Transformer transformer = TransformerFactory.createTransformer(inputStream, outputStream); List xlsAreas = new XlsCommentAreaBuilder(transformer).build(); diff --git a/src/org/traccar/reports/Summary.java b/src/org/traccar/reports/Summary.java index 95a3737a1..d7d8ab417 100644 --- a/src/org/traccar/reports/Summary.java +++ b/src/org/traccar/reports/Summary.java @@ -25,9 +25,11 @@ import java.util.ArrayList; import java.util.Collection; import java.util.Date; +import org.joda.time.DateTime; import org.jxls.transform.poi.PoiTransformer; import org.jxls.util.JxlsHelper; import org.traccar.Context; +import org.traccar.helper.DateUtil; import org.traccar.model.Position; import org.traccar.reports.model.SummaryReport; @@ -82,8 +84,10 @@ public final class Summary { public static void getExcel(OutputStream outputStream, long userId, Collection deviceIds, Collection groupIds, - Date from, Date to) throws SQLException, IOException { - Collection summaries = getObjects(userId, deviceIds, groupIds, from, to); + String fromString, String toString) throws SQLException, IOException { + DateTime from = DateUtil.parseDateTime(fromString); + DateTime to = DateUtil.parseDateTime(toString); + Collection summaries = getObjects(userId, deviceIds, groupIds, from.toDate(), to.toDate()); String templatePath = Context.getConfig().getString("report.templatesPath", "templates/export/"); try (InputStream inputStream = new FileInputStream(templatePath + "/summary.xlsx")) { @@ -93,6 +97,7 @@ public final class Summary { jxlsContext.putVar("to", to); jxlsContext.putVar("distanceUnit", ReportUtils.getDistanceUnit(userId)); jxlsContext.putVar("speedUnit", ReportUtils.getSpeedUnit(userId)); + jxlsContext.putVar("timezone", from.getZone()); JxlsHelper.getInstance().setUseFastFormulaProcessor(false) .processTemplate(inputStream, outputStream, jxlsContext); } diff --git a/src/org/traccar/reports/Trips.java b/src/org/traccar/reports/Trips.java index 91a080d45..c31eaac83 100644 --- a/src/org/traccar/reports/Trips.java +++ b/src/org/traccar/reports/Trips.java @@ -26,6 +26,7 @@ import java.util.Collection; import java.util.Date; import java.util.List; +import org.joda.time.DateTime; import org.jxls.area.Area; import org.jxls.builder.xls.XlsCommentAreaBuilder; import org.jxls.common.CellRef; @@ -34,6 +35,7 @@ import org.jxls.transform.Transformer; import org.jxls.transform.poi.PoiTransformer; import org.jxls.util.TransformerFactory; import org.traccar.Context; +import org.traccar.helper.DateUtil; import org.traccar.model.Device; import org.traccar.model.Group; import org.traccar.model.Position; @@ -175,12 +177,14 @@ public final class Trips { public static void getExcel(OutputStream outputStream, long userId, Collection deviceIds, Collection groupIds, - Date from, Date to) throws SQLException, IOException { + String fromString, String toString) throws SQLException, IOException { ArrayList devicesTrips = new ArrayList<>(); ArrayList sheetNames = new ArrayList<>(); + DateTime from = DateUtil.parseDateTime(fromString); + DateTime to = DateUtil.parseDateTime(toString); for (long deviceId: ReportUtils.getDeviceList(deviceIds, groupIds)) { Context.getPermissionsManager().checkDevice(userId, deviceId); - Collection trips = detectTrips(deviceId, from, to); + Collection trips = detectTrips(deviceId, from.toDate(), to.toDate()); DeviceReport deviceTrips = new DeviceReport(); Device device = Context.getIdentityManager().getDeviceById(deviceId); deviceTrips.setDeviceName(device.getName()); @@ -204,6 +208,7 @@ public final class Trips { jxlsContext.putVar("to", to); jxlsContext.putVar("distanceUnit", ReportUtils.getDistanceUnit(userId)); jxlsContext.putVar("speedUnit", ReportUtils.getSpeedUnit(userId)); + jxlsContext.putVar("timezone", from.getZone()); Transformer transformer = TransformerFactory.createTransformer(inputStream, outputStream); List xlsAreas = new XlsCommentAreaBuilder(transformer).build(); for (Area xlsArea : xlsAreas) { diff --git a/templates/export/events.xlsx b/templates/export/events.xlsx index f0ff05a23..80a54fa57 100644 Binary files a/templates/export/events.xlsx and b/templates/export/events.xlsx differ diff --git a/templates/export/route.xlsx b/templates/export/route.xlsx index 24027523d..79a2649b7 100644 Binary files a/templates/export/route.xlsx and b/templates/export/route.xlsx differ diff --git a/templates/export/summary.xlsx b/templates/export/summary.xlsx index 88788f82a..53539ed8b 100644 Binary files a/templates/export/summary.xlsx and b/templates/export/summary.xlsx differ diff --git a/templates/export/trips.xlsx b/templates/export/trips.xlsx index 83d22678e..20130f44a 100644 Binary files a/templates/export/trips.xlsx and b/templates/export/trips.xlsx differ -- cgit v1.2.3