/* * This file is part of JGrasstools (http://www.jgrasstools.org) * (C) HydroloGIS - www.hydrologis.com * * JGrasstools is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package org.jgrasstools.gears.io.geopaparazzi; import static org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.TABLE_GPSLOGS; import static org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.TABLE_IMAGES; import static org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.TABLE_METADATA; import static org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.TABLE_NOTES; import java.io.File; import java.io.FilenameFilter; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.Date; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Map.Entry; import java.util.Set; import java.util.TreeMap; import org.geotools.feature.simple.SimpleFeatureTypeBuilder; import org.geotools.referencing.crs.DefaultGeographicCRS; import org.jgrasstools.dbs.compat.IJGTConnection; import org.jgrasstools.dbs.compat.IJGTResultSet; import org.jgrasstools.dbs.compat.IJGTStatement; import org.jgrasstools.dbs.spatialite.jgt.SqliteDb; import org.jgrasstools.gears.io.geopaparazzi.forms.Utilities; import org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.ImageTableFields; import org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.MetadataTableFields; import org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.NotesTableFields; import org.jgrasstools.gears.io.geopaparazzi.geopap4.ETimeUtilities; import org.json.JSONArray; import org.json.JSONObject; import org.opengis.feature.simple.SimpleFeatureType; import com.vividsolutions.jts.geom.MultiLineString; import com.vividsolutions.jts.geom.Point; /** * Geopaparazzi utils. * * @author Andrea Antonello (www.hydrologis.com) */ public class GeopaparazziUtilities { public static final String PROJECT_NAME = "name"; public static final String PROJECT_CREATION_USER = "creationuser"; public static final String PROJECT_CREATION_TS = "creationts"; public static final String PROJECT_DESCRIPTION = "description"; public static final String GPAP_EXTENSION = "gpap"; public static final String GPS_LOGS = "GPS logs"; public static final String MEDIA_NOTES = "Media Notes"; public static final String SIMPLE_NOTES = "Simple Notes"; public static final String TAG_KEY = "key"; public static final String TAG_VALUE = "value"; public static final String TAG_TYPE = "type"; public static final String NOTES_tsFN = NotesTableFields.COLUMN_TS.getFieldName(); public static final String NOTES_altimFN = NotesTableFields.COLUMN_ALTIM.getFieldName(); public static final String NOTES_dirtyFN = NotesTableFields.COLUMN_ISDIRTY.getFieldName(); public static final String NOTES_textFN = NotesTableFields.COLUMN_TEXT.getFieldName(); public static final String NOTES_descFN = NotesTableFields.COLUMN_DESCRIPTION.getFieldName(); public static final String NOTES_formFN = NotesTableFields.COLUMN_FORM.getFieldName(); public static final String GPSLOG_descrFN = "DESCR"; public static final String GPSLOG_enddateFN = "ENDDATE"; public static final String GPSLOG_startdateFN = "STARTDATE"; public static final String IMAGES_altimFN = ImageTableFields.COLUMN_ALTIM.getFieldName(); public static final String IMAGES_tsFN = ImageTableFields.COLUMN_TS.getFieldName(); public static final String IMAGES_azimFN = ImageTableFields.COLUMN_AZIM.getFieldName(); public static final String IMAGES_imageidFN = "imageid"; public static List<HashMap<String, String>> readProjectMetadata( File[] projectFiles ) throws Exception { List<HashMap<String, String>> infoList = new ArrayList<HashMap<String, String>>(); for( File geopapDatabaseFile : projectFiles ) { try (SqliteDb db = new SqliteDb()) { db.open(geopapDatabaseFile.getAbsolutePath()); HashMap<String, String> projectInfo = getProjectMetadata(db.getConnection()); infoList.add(projectInfo); } } return infoList; } public static File[] getGeopaparazziFiles( final File geopaparazziFolder ) { File[] projectFiles = geopaparazziFolder.listFiles(new FilenameFilter(){ @Override public boolean accept( File dir, String name ) { return name.endsWith(GPAP_EXTENSION); } }); Arrays.sort(projectFiles, Collections.reverseOrder()); return projectFiles; } /** * Get the map of metadata of the project. * * @param connection the db connection. * @return the map of metadata. * @throws SQLException */ public static LinkedHashMap<String, String> getProjectMetadata( IJGTConnection connection ) throws Exception { LinkedHashMap<String, String> metadataMap = new LinkedHashMap<>(); try (IJGTStatement statement = connection.createStatement()) { statement.setQueryTimeout(30); // set timeout to 30 sec. String sql = "select " + MetadataTableFields.COLUMN_KEY.getFieldName() + ", " + // MetadataTableFields.COLUMN_VALUE.getFieldName() + " from " + TABLE_METADATA; IJGTResultSet rs = statement.executeQuery(sql); while( rs.next() ) { String key = rs.getString(MetadataTableFields.COLUMN_KEY.getFieldName()); String value = rs.getString(MetadataTableFields.COLUMN_VALUE.getFieldName()); if (!key.endsWith("ts")) { metadataMap.put(key, value); } else { try { long ts = Long.parseLong(value); String dateTimeString = ETimeUtilities.INSTANCE.TIME_FORMATTER_LOCAL.format(new Date(ts)); metadataMap.put(key, dateTimeString); } catch (Exception e) { metadataMap.put(key, value); } } } } return metadataMap; } /** * @return the list of potential layers. * @throws SQLException */ public static List<String> getLayerNamesList( IJGTConnection connection ) throws Exception { String formFN = NotesTableFields.COLUMN_FORM.getFieldName(); String textFN = NotesTableFields.COLUMN_TEXT.getFieldName(); List<String> layerNames = new ArrayList<>(); String sql = "select count(*) from " + TABLE_NOTES + " where " + formFN + " is null or " + formFN + " = ''"; int count = countRows(connection, sql); if (count > 0) layerNames.add(SIMPLE_NOTES); sql = "select count(*) from " + TABLE_IMAGES; count = countRows(connection, sql); if (count > 0) layerNames.add(MEDIA_NOTES); sql = "select count(*) from " + TABLE_GPSLOGS; count = countRows(connection, sql); if (count > 0) layerNames.add(GPS_LOGS); sql = "select distinct " + textFN + " from " + TABLE_NOTES + " where " + formFN + " is not null and " + formFN + "<>''"; try (IJGTStatement statement = connection.createStatement(); IJGTResultSet rs = statement.executeQuery(sql);) { statement.setQueryTimeout(30); // set timeout to 30 sec. while( rs.next() ) { String formName = rs.getString(1); layerNames.add(formName); } } return layerNames; } private static int countRows( IJGTConnection connection, String sql ) throws Exception { try (IJGTStatement statement = connection.createStatement(); IJGTResultSet rs = statement.executeQuery(sql);) { if (rs.next()) { int notesCount = rs.getInt(1); return notesCount; } } return 0; } public static String getProjectInfo( IJGTConnection connection, boolean doHtml ) throws Exception { StringBuilder sb = new StringBuilder(); String sql = "select " + MetadataTableFields.COLUMN_KEY.getFieldName() + ", " + // MetadataTableFields.COLUMN_VALUE.getFieldName() + " from " + TABLE_METADATA; try (IJGTStatement statement = connection.createStatement(); IJGTResultSet rs = statement.executeQuery(sql);) { statement.setQueryTimeout(30); // set timeout to 30 sec. while( rs.next() ) { String key = rs.getString(MetadataTableFields.COLUMN_KEY.getFieldName()); String value = rs.getString(MetadataTableFields.COLUMN_VALUE.getFieldName()); String openBold = "<b>"; String closeBold = "</b>"; String nl = "<br/>"; if (!doHtml) { openBold = ""; closeBold = ""; nl = "\n"; } if (!key.endsWith("ts")) { sb.append(openBold).append(key).append(":" + closeBold + " ").append(escapeHTML(value)).append(nl); } else { try { long ts = Long.parseLong(value); String dateTimeString = ETimeUtilities.INSTANCE.TIME_FORMATTER_LOCAL.format(new Date(ts)); sb.append(openBold).append(key).append(":" + closeBold + " ").append(dateTimeString).append(nl); } catch (Exception e) { sb.append(openBold).append(key).append(":" + closeBold + " ").append(escapeHTML(value)).append(nl); } } } } return sb.toString(); } /** * @param connection * @return the list of [lon, lat, altim, dateTimeString, text, descr] * @throws Exception */ public static List<String[]> getNotesText( Connection connection ) throws Exception { String textFN = NotesTableFields.COLUMN_TEXT.getFieldName(); String descFN = NotesTableFields.COLUMN_DESCRIPTION.getFieldName(); String tsFN = NotesTableFields.COLUMN_TS.getFieldName(); String altimFN = NotesTableFields.COLUMN_ALTIM.getFieldName(); String latFN = NotesTableFields.COLUMN_LAT.getFieldName(); String lonFN = NotesTableFields.COLUMN_LON.getFieldName(); String sql = "select " + // latFN + "," + // lonFN + "," + // altimFN + "," + // tsFN + "," + // textFN + "," + // descFN + " from " + // TABLE_NOTES; List<String[]> notesDescriptionList = new ArrayList<>(); try (Statement statement = connection.createStatement()) { statement.setQueryTimeout(30); // set timeout to 30 sec. ResultSet rs = statement.executeQuery(sql); while( rs.next() ) { double lat = rs.getDouble(latFN); double lon = rs.getDouble(lonFN); double altim = rs.getDouble(altimFN); long ts = rs.getLong(tsFN); String dateTimeString = ETimeUtilities.INSTANCE.TIME_FORMATTER_LOCAL.format(new Date(ts)); String text = rs.getString(textFN); String descr = rs.getString(descFN); if (descr == null) descr = ""; if (lat == 0 || lon == 0) { continue; } notesDescriptionList.add(new String[]{// String.valueOf(lon), // String.valueOf(lat), // String.valueOf(altim), // dateTimeString, // text, // descr// }); } } return notesDescriptionList; } public static String escapeHTML( String s ) { if (s == null) { return ""; } StringBuilder out = new StringBuilder(Math.max(16, s.length())); for( int i = 0; i < s.length(); i++ ) { char c = s.charAt(i); if (c > 127 || c == '"' || c == '<' || c == '>' || c == '&') { out.append("&#"); out.append((int) c); out.append(';'); } else { out.append(c); } } return out.toString(); } public static String loadProjectsList( File gpapProjectsFolder ) { try { File[] geopaparazziProjectFiles = GeopaparazziUtilities.getGeopaparazziFiles(gpapProjectsFolder); List<HashMap<String, String>> projectMetadataList = GeopaparazziUtilities .readProjectMetadata(geopaparazziProjectFiles); StringBuilder sb = new StringBuilder(); sb.append("{"); sb.append("\"projects\": ["); for( int i = 0; i < projectMetadataList.size(); i++ ) { HashMap<String, String> metadataMap = projectMetadataList.get(i); long fileSize = geopaparazziProjectFiles[i].length(); if (i > 0) sb.append(","); sb.append("{"); sb.append(" \"id\": \"" + geopaparazziProjectFiles[i].getName() + "\","); sb.append(" \"title\": \"" + metadataMap.get(PROJECT_DESCRIPTION) + "\","); sb.append(" \"date\": \"" + metadataMap.get(PROJECT_CREATION_TS) + "\","); sb.append(" \"author\": \"" + metadataMap.get(PROJECT_CREATION_USER) + "\","); sb.append(" \"name\": \"" + metadataMap.get(PROJECT_NAME) + "\","); sb.append(" \"size\": \"" + fileSize + "\""); sb.append("}"); } sb.append("]"); sb.append("}"); return sb.toString(); } catch (Exception e) { return "An error occurred: " + e.getMessage(); } } public static SimpleFeatureType getSimpleNotesfeatureType() { SimpleFeatureTypeBuilder b = new SimpleFeatureTypeBuilder(); b.setName("gpsimplenotes"); //$NON-NLS-1$ b.setCRS(DefaultGeographicCRS.WGS84); b.add("the_geom", Point.class); //$NON-NLS-1$ b.add(NOTES_textFN, String.class); b.add(NOTES_descFN, String.class); b.add(NOTES_tsFN, String.class); b.add(NOTES_altimFN, Double.class); b.add(NOTES_dirtyFN, Integer.class); SimpleFeatureType featureType = b.buildFeatureType(); return featureType; } public static SimpleFeatureType getGpsLogLinesFeatureType() { SimpleFeatureTypeBuilder b = new SimpleFeatureTypeBuilder(); b.setName("geopaparazzilogs"); b.setCRS(DefaultGeographicCRS.WGS84); b.add("the_geom", MultiLineString.class); b.add(GPSLOG_startdateFN, String.class); b.add(GPSLOG_enddateFN, String.class); b.add(GPSLOG_descrFN, String.class); SimpleFeatureType featureType = b.buildFeatureType(); return featureType; } public static SimpleFeatureType getMediaFeaturetype() { SimpleFeatureTypeBuilder b = new SimpleFeatureTypeBuilder(); b.setName("geopaparazzimediapoints"); b.setCRS(DefaultGeographicCRS.WGS84); b.add("the_geom", Point.class); b.add(IMAGES_altimFN, String.class); b.add(IMAGES_tsFN, String.class); b.add(IMAGES_azimFN, Double.class); b.add(IMAGES_imageidFN, Long.class); SimpleFeatureType featureType = b.buildFeatureType(); return featureType; } public static SimpleFeatureType getComplexNotefeatureType( String noteName, IJGTConnection connection ) throws Exception { String sql = "select " + // NOTES_formFN + " from " + // TABLE_NOTES + " where " + NOTES_textFN + "='" + noteName + "'"; try (IJGTStatement statement = connection.createStatement(); IJGTResultSet rs = statement.executeQuery(sql);) { while( rs.next() ) { String formString = rs.getString(NOTES_formFN); if (formString == null || formString.trim().length() == 0) { continue; } JSONObject sectionObject = new JSONObject(formString); String sectionName = sectionObject.getString("sectionname"); if (!sectionName.equals(noteName)) { continue; } sectionName = sectionName.replaceAll("\\s+", "_"); LinkedHashMap<String, String> valuesMap = new LinkedHashMap<>(); LinkedHashMap<String, String> typesMap = new LinkedHashMap<>(); List<String> formNames4Section = Utilities.getFormNames4Section(sectionObject); extractValues(sectionObject, formNames4Section, valuesMap, typesMap); Set<Entry<String, String>> entrySet = valuesMap.entrySet(); TreeMap<String, Integer> namesMap = new TreeMap<String, Integer>(); SimpleFeatureTypeBuilder b = new SimpleFeatureTypeBuilder(); b.setName(sectionName); // $NON-NLS-1$ b.setCRS(DefaultGeographicCRS.WGS84); b.add("the_geom", Point.class); //$NON-NLS-1$ b.add(NOTES_tsFN, String.class); // $NON-NLS-1$ b.add(NOTES_altimFN, Double.class); // $NON-NLS-1$ b.add(NOTES_dirtyFN, Integer.class); // $NON-NLS-1$ for( Entry<String, String> entry : entrySet ) { String key = entry.getKey(); key = key.replaceAll("\\s+", "_"); if (key.length() > 10) { key = key.substring(0, 10); } Integer nCount = namesMap.get(key); if (nCount == null) { nCount = 1; namesMap.put(key, 1); } else { nCount++; namesMap.put(key, nCount); if (nCount < 10) { key = key.substring(0, key.length() - 1) + nCount; } else { key = key.substring(0, key.length() - 2) + nCount; } } b.add(key, String.class); } SimpleFeatureType featureType = b.buildFeatureType(); return featureType; } return null; } } public static void extractValues( JSONObject sectionObject, List<String> formNames4Section, LinkedHashMap<String, String> valuesMap, LinkedHashMap<String, String> typesMap ) { for( String formName : formNames4Section ) { JSONObject form4Name = Utilities.getForm4Name(formName, sectionObject); JSONArray formItems = Utilities.getFormItems(form4Name); int length = formItems.length(); for( int i = 0; i < length; i++ ) { JSONObject jsonObject = formItems.getJSONObject(i); if (!jsonObject.has(TAG_KEY)) { continue; } String key = jsonObject.getString(TAG_KEY).trim(); String value = null; if (jsonObject.has(TAG_VALUE)) { value = jsonObject.get(TAG_VALUE).toString().trim(); } String type = null; if (jsonObject.has(TAG_TYPE)) { type = jsonObject.getString(TAG_TYPE).trim(); } if (value != null) { valuesMap.put(key, value); typesMap.put(key, type); } } } } }