Author: obruce Date: 2014-08-04 14:39:19 +0200 (Mon, 04 Aug 2014) New Revision: 3024 Url: http://forge.chorem.org/projects/jtimer/repository/revisions/3024 Log: changement connection storage, ajout dans les config Modified: branches/ng-jtimer/src/main/go/ui.go branches/ng-jtimer/src/main/java/org/chorem/jtimer/config/JtimerConfig.java branches/ng-jtimer/src/main/java/org/chorem/jtimer/config/JtimerConfigOption.java branches/ng-jtimer/src/main/java/org/chorem/jtimer/entities/TimerTask.java branches/ng-jtimer/src/main/java/org/chorem/jtimer/storage/Storage.java branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/ReportResource.java branches/ng-jtimer/src/main/resources/jtimer-default.properties Modified: branches/ng-jtimer/src/main/go/ui.go =================================================================== --- branches/ng-jtimer/src/main/go/ui.go 2014-08-01 14:59:12 UTC (rev 3023) +++ branches/ng-jtimer/src/main/go/ui.go 2014-08-04 12:39:19 UTC (rev 3024) @@ -22,7 +22,7 @@ } binary, _ := exec.LookPath("chromium") - return exec.Command(binary, "--app=http://localhost:8080", + return exec.Command(binary, "--app=http://localhost:8081", "--user-data-dir=" + usr.HomeDir + "/.jtimer/chromium") } Modified: branches/ng-jtimer/src/main/java/org/chorem/jtimer/config/JtimerConfig.java =================================================================== --- branches/ng-jtimer/src/main/java/org/chorem/jtimer/config/JtimerConfig.java 2014-08-01 14:59:12 UTC (rev 3023) +++ branches/ng-jtimer/src/main/java/org/chorem/jtimer/config/JtimerConfig.java 2014-08-04 12:39:19 UTC (rev 3024) @@ -127,6 +127,10 @@ return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_PARENT_TASK.getKey()); } + public String getStorageQueryCreateAliasContainsOne (){ + return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_CREATE_ALIAS_CONTAINSONE.getKey()); + } + public String getStorageQuerySelectTimedTask(){ return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_TIMED_TASK.getKey()); } @@ -163,21 +167,26 @@ return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_BYDAY.getKey()); } - public String getStorageQuerySelectReportTasks(){ - return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_TIMEDTASKS.getKey()); + public String getStorageQuerySelectReportByProjectAndTags(){ + return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_BYPROJECTANDTAG.getKey()); } - public String getStorageQuerySelectReportRootTask(){ - return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_ROOTTASK.getKey()); + + public String getStorageQuerySelectReportByYearAndTags(){ + return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_BYYEARANDTAG.getKey()); } - public String getStorageQuerySelectReportWeekTimedTasks(){ - return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_WEEK_TIMEDTASKS.getKey()); + + public String getStorageQuerySelectReportByMonthAndTags(){ + return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_BYMONTHANDTAG.getKey()); } - public String getStorageQuerySelectReportYearTimedTasks(){ - return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_YEAR_TIMEDTASKS.getKey()); + + public String getStorageQuerySelectReportByWeekAndTags(){ + return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_BYWEEKANDTAG.getKey()); } - public String getStorageQuerySelectReportMonthTimedTasks(){ - return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_MONTH_TIMEDTASKS.getKey()); + + public String getStorageQuerySelectReportByDayAndTags(){ + return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_BYDAYANDTAG.getKey()); } + public String getStorageQuerySelectAlarmOnDate(){ return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_ALARM_ONDATE.getKey()); } Modified: branches/ng-jtimer/src/main/java/org/chorem/jtimer/config/JtimerConfigOption.java =================================================================== --- branches/ng-jtimer/src/main/java/org/chorem/jtimer/config/JtimerConfigOption.java 2014-08-01 14:59:12 UTC (rev 3023) +++ branches/ng-jtimer/src/main/java/org/chorem/jtimer/config/JtimerConfigOption.java 2014-08-04 12:39:19 UTC (rev 3024) @@ -52,6 +52,12 @@ "", String.class ), + JTIMER_STORAGE_CREATE_ALIAS_CONTAINSONE( + "jtimer.storage.alias.containsone" , + "requête qui va créer l'alias pour la fonction vérifiant les elements", + "", + String.class + ), JTIMER_STORAGE_CREATE_TABLE_TASK( "jtimer.storage.create.table.task", "requête de création la table des tâches", @@ -178,33 +184,33 @@ "", String.class ), - JTIMER_STORAGE_SELECT_REPORT_TIMEDTASKS( - "jtimer.storage.select.report.timedtasks", - "requête de selection des taches minutées entre deux bornes", + JTIMER_STORAGE_SELECT_REPORT_BYPROJECTANDTAG( + "jtimer.storage.select.report.byProjectAndTag", + "requête de selection des taches minutées entre deux bornes filtrer par projet", "", String.class ), - JTIMER_STORAGE_SELECT_REPORT_ROOTTASK( - "jtimer.storage.select.report.roottasks", - "requête de selection des taches root", + JTIMER_STORAGE_SELECT_REPORT_BYYEARANDTAG( + "jtimer.storage.select.report.byYearAndTag", + "requête de selection des taches minutées entre deux bornes filtrer par year", "", String.class ), - JTIMER_STORAGE_SELECT_REPORT_WEEK_TIMEDTASKS( - "jtimer.storage.select.report.week.timedtasks", - "requête de selection des taches minutees sur une semaine", + JTIMER_STORAGE_SELECT_REPORT_BYMONTHANDTAG( + "jtimer.storage.select.report.byMonthAndTag", + "requête de selection des taches minutées entre deux bornes filtrer par month", "", String.class ), - JTIMER_STORAGE_SELECT_REPORT_YEAR_TIMEDTASKS( - "jtimer.storage.select.report.year.timedtasks", - "requête de selection des taches minutees sur une annee", + JTIMER_STORAGE_SELECT_REPORT_BYWEEKANDTAG( + "jtimer.storage.select.report.byWeekAndTag", + "requête de selection des taches minutées entre deux bornes filtrer par semaine", "", String.class ), - JTIMER_STORAGE_SELECT_REPORT_MONTH_TIMEDTASKS( - "jtimer.storage.select.report.month.timedtasks", - "requête de selection des taches minutees sur un mois", + JTIMER_STORAGE_SELECT_REPORT_BYDAYANDTAG( + "jtimer.storage.select.report.byDayAndTag", + "requête de selection des taches minutées entre deux bornes filtrer par jour", "", String.class ), Modified: branches/ng-jtimer/src/main/java/org/chorem/jtimer/entities/TimerTask.java =================================================================== --- branches/ng-jtimer/src/main/java/org/chorem/jtimer/entities/TimerTask.java 2014-08-01 14:59:12 UTC (rev 3023) +++ branches/ng-jtimer/src/main/java/org/chorem/jtimer/entities/TimerTask.java 2014-08-04 12:39:19 UTC (rev 3024) @@ -58,6 +58,9 @@ /**ModificationDate*/ protected Date modificationDate; + /** Description */ + protected String description; + /** Closed task. */ protected boolean closed; @@ -70,8 +73,12 @@ /** date if task is removed */ protected long removed; + /** Array of ordered parent */ protected String[] path; + /** Array of ordered tags */ + protected String[] tags; + /** * Constructor. */ @@ -90,20 +97,15 @@ this.name = name; this.taskId = UUID.randomUUID().toString(); this.parent = ""; + this.description=""; this.creationDate = new Date(); this.modificationDate = new Date(); this.removed = 0; this.path = null; + this.tags = null; } - public Date getCreationDate() { - return creationDate; - } - public void setCreationDate(Date creationDate) { - this.creationDate = creationDate; - } - /** * Set task uuid * @@ -211,6 +213,35 @@ public void setRemoved(long removed) {this.removed = removed;} + + public Date getCreationDate() { + return creationDate; + } + + public void setCreationDate(Date creationDate) { + this.creationDate = creationDate; + } + + public String getDescription() { + return description; + } + + public void setDescription(String description) { + this.description = description; + } + + public void setPath(String[] path) { + this.path = path; + } + + public void setTags(String[] tags) { + this.tags = tags; + } + + public String[] getTags() { + return this.tags; + } + @Override public String toString() { String res ="TimerTask {" + @@ -221,16 +252,25 @@ ", closed=" + closed + ", todayTime=" + todayTime + ", totalTime=" + totalTime + - ", removed=" + removed +'}'; + ", removed=" + removed + + ", path=[" ; - if(path != null) - res += ",path= " + path.toString(); + if(path != null){ + for (String o : path) { + res += o + ", "; + } - return res; + } + res += "], tags=["; + if( tags != null) { + for (String o : tags) { + res += o + ", "; + } + } + return res+"]}"; + } - public void setPath(String[] path) { - this.path = path; - } + } \ No newline at end of file Modified: branches/ng-jtimer/src/main/java/org/chorem/jtimer/storage/Storage.java =================================================================== --- branches/ng-jtimer/src/main/java/org/chorem/jtimer/storage/Storage.java 2014-08-01 14:59:12 UTC (rev 3023) +++ branches/ng-jtimer/src/main/java/org/chorem/jtimer/storage/Storage.java 2014-08-04 12:39:19 UTC (rev 3024) @@ -1,5 +1,6 @@ package org.chorem.jtimer.storage; +import org.apache.commons.lang3.ArrayUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.chorem.jtimer.config.JtimerConfig; @@ -82,33 +83,42 @@ } protected Connection getConnection() throws SQLException { + if (connection == null) { + synchronized (Storage.class) { + if (connection == null) { + //TODO obruce 07/2014 mettre un pool de connection et pas d'autocommit + if (log.isInfoEnabled()) { + log.info("Opening connection to database : " + STORAGE_PATH); + } + Connection conn = DriverManager.getConnection("jdbc:h2:" + STORAGE_PATH, "sa", + ""); + conn.setAutoCommit(true); - if (log.isInfoEnabled()) { - log.info("Opening connection to database : " + STORAGE_PATH); - } - Connection conn = DriverManager.getConnection("jdbc:h2:" + STORAGE_PATH, "sa", - ""); - conn.setAutoCommit(true); + //TODO modifier pour que ce soit fait une seule fois au demarrage de l'application + // test to create schema if it not already exists + boolean schemaExists = false; //schemaExists(conn); + if (!schemaExists) { + if (log.isInfoEnabled()) { + log.info("Creating new database schema"); + } - // test to create schema if it not already exists - boolean schemaExists = schemaExists(conn); - if (!schemaExists) { - if (log.isInfoEnabled()) { - log.info("Creating new database schema"); + createSchema(conn); + + // set new version in schema + Statement statement = conn.createStatement(); + statement.executeUpdate(config.getStorageQueryInsertVersionNumber()); + } + connection = conn; + } } - createSchema(conn); - - // set new version in schema - Statement statement = conn.createStatement(); - statement.executeUpdate(config.getStorageQueryInsertVersionNumber()); } - - return conn; + return connection; } protected void closeConnection(Connection conn) throws SQLException { if (conn != null) { conn.close(); + conn = null; } } @@ -156,13 +166,16 @@ protected void createSchema(Connection conn) { Statement statement = null; try { + statement = conn.createStatement(); + statement.executeUpdate(config.getStorageQueryCreateAliasContainsOne()); statement.executeUpdate(config.getStorageQueryCreateTableVersion()); statement.executeUpdate(config.getStorageQueryCreateTableTask()); statement.executeUpdate(config.getStorageQueryCreateTableTime()); statement.executeUpdate(config.getStorageQueryCreateTableAlarm()); + } catch (SQLException ex) { throw new StorageException("Can't create schema", ex); } finally { @@ -171,6 +184,48 @@ } + public static boolean containsOneTheGood(Object[] array1, Object[] array2){ + boolean result = ArrayUtils.isEmpty(array1) && ArrayUtils.isEmpty(array2); + if (array1 != null && array2 != null) { + //TODO 31/07/2014 obruce optimiser la methode regarder la taille du plus petit tableau + for (Object o : array1) { + result = ArrayUtils.contains(array2, o); + if (result) { + break; + } + } + } + return result; + } + public static boolean containsOne(Object[] array1, Object[] array2){ + boolean result = ArrayUtils.isEmpty(array1) && ArrayUtils.isEmpty(array2); + if (array1 != null && array2 != null) { + //TODO 31/07/2014 obruce optimiser la methode regarder la taille du plus petit tableau + for (Object o : array1) { + result = ArrayUtils.contains(array2, o); + if (result) { + break; + } + } + } + return result; + } + + public static boolean containsOneHack2(Object[] array1, Object[] array2){ + boolean result = ArrayUtils.isEmpty(array1) && ArrayUtils.isEmpty(array2); + if (array1 != null && array2 != null) { + //TODO 31/07/2014 obruce optimiser la methode regarder la taille du plus petit tableau + for (Object o : array1) { + result = ArrayUtils.contains(array2, o); + if (result) { + break; + } + } + } + System.out.println("###"+ArrayUtils.toString(array1)+"#######"+ArrayUtils.toString(array2)+"######## true ?" + result); + return result; + } + /** Query **/ /** @@ -182,7 +237,7 @@ int result = 0; PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQuerySelectCountTask()); + statement = getConnection().prepareStatement(config.getStorageQuerySelectCountTask()); ResultSet rs = statement.executeQuery(); if (rs.next()) { @@ -209,23 +264,25 @@ PreparedStatement statement2; try { - statement = connection.prepareStatement(config.getStorageQueryInsertTask()); + statement = getConnection().prepareStatement(config.getStorageQueryInsertTask()); statement.setString(1, task.getName()); statement.setString(2, task.getParent()); statement.setString(3, task.getTaskId()); statement.setBoolean(4, task.isClosed()); - statement.setString(5, null /*project.getNote()*/); + statement.setString(5, task.getDescription()); statement.setObject(6, task.getCreationDate()); statement.setObject(7, task.getModificationDate()); statement.setLong(8,task.getRemoved()); + //statement.setObject(9,task.getTags()); + statement.executeUpdate(); - String[] array; + String[] path; //On recupere le path du parent if(!task.getParent().equals("")) { - statement2 = connection.prepareStatement(config.getStorageQuerySelectPath()); + statement2 = getConnection().prepareStatement(config.getStorageQuerySelectPath()); //On insere l'identifiant du parent statement2.setString(1, task.getParent()); @@ -234,17 +291,18 @@ try { rs.first(); String[] parentArray = (String[]) rs.getObject("path"); - array = Arrays.copyOf(parentArray, parentArray.length + 1); - array[parentArray.length] = task.getTaskId(); + path = Arrays.copyOf(parentArray, parentArray.length + 1); + path[parentArray.length] = task.getTaskId(); + }catch(JdbcSQLException eee){ log.error(eee); - array = new String[]{task.getTaskId()}; + path = new String[]{task.getTaskId()}; } } else { - array = new String[]{task.getTaskId()}; + path = new String[]{task.getTaskId()}; } - updateTaskPath(task.getTaskId(), array); + updateTaskPath(task.getTaskId(), path); } catch (SQLException ex) { @@ -266,7 +324,7 @@ public void addTaskTime(TimerTask task, Date date, String uuid, long duration) { PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryInsertTime()); + statement = getConnection().prepareStatement(config.getStorageQueryInsertTime()); statement.setString(1, task.getTaskId()); statement.setObject(2, new Date()); @@ -289,7 +347,7 @@ public void addTaskTime(TimerTime time) { PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryInsertTime()); + statement = getConnection().prepareStatement(config.getStorageQueryInsertTime()); statement.setString(1, time.getTaskId()); statement.setObject(2, time.getCreationDate()); @@ -315,7 +373,7 @@ public void addTaskAlarm(TimerAlarm alarm) { PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryInsertAlarm()); + statement = getConnection().prepareStatement(config.getStorageQueryInsertAlarm()); statement.setString(1, alarm.getTaskId()); statement.setString(2, alarm.getAlarmId()); @@ -346,7 +404,7 @@ PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQuerySelectTaskTimeWithDate()); + statement = getConnection().prepareStatement(config.getStorageQuerySelectTaskTimeWithDate()); statement.setObject(1, new Date(date)); ResultSet rs = statement.executeQuery(); @@ -382,7 +440,7 @@ PreparedStatement statement2 = null; try { - statement = connection.prepareStatement(config.getStorageQuerySelectTimedTask()); + statement = getConnection().prepareStatement(config.getStorageQuerySelectTimedTask()); statement.setObject(1, new Date(date)); log.debug(statement.toString()); @@ -394,9 +452,7 @@ task.setName(rs.getString("name")); task.setParent(rs.getString("parent")); task.setRemoved(rs.getLong("removed")); - - - + task.setDescription(rs.getString("description")); task.setTodayTime(0); task.setCreationDate(new java.util.Date((rs.getTimestamp("creationDate").getTime()))); task.setModificationDate(new java.util.Date(rs.getTimestamp("modificationDate").getTime())); @@ -405,7 +461,7 @@ } // not timed tasks - statement2 = connection.prepareStatement(config.getStorageQuerySelectNotTimedTask()); + statement2 = getConnection().prepareStatement(config.getStorageQuerySelectNotTimedTask()); statement2.setObject(1, new Date(date)); rs = statement2.executeQuery(); @@ -414,11 +470,13 @@ task.setName(rs.getString("name")); task.setTaskId(rs.getString("taskId")); task.setParent(rs.getString("parent")); + task.setDescription(rs.getString("description")); task.setCreationDate(new java.util.Date((rs.getTimestamp("creationDate").getTime()))); task.setModificationDate(new java.util.Date(rs.getTimestamp("modificationDate").getTime())); task.setRemoved(rs.getLong("removed")); task.setTodayTime(0); task.setTotalTime(0); + tasks.add(task); } } catch (SQLException ex) { @@ -440,7 +498,7 @@ List<TimerTime> times = new ArrayList<>(); PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQuerySelectNotRemovedTaskTime()); + statement = getConnection().prepareStatement(config.getStorageQuerySelectNotRemovedTaskTime()); statement.setString(1, taskid); statement.setObject(2, new Date(date)); @@ -475,7 +533,7 @@ PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQuerySelectAlarmOnDate()); + statement = getConnection().prepareStatement(config.getStorageQuerySelectAlarmOnDate()); statement.setObject(1, new Date(date)); ResultSet rs = statement.executeQuery(); @@ -509,7 +567,7 @@ PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQuerySelectAlarmWithTaskID()); + statement = getConnection().prepareStatement(config.getStorageQuerySelectAlarmWithTaskID()); statement.setObject(1, new Date(date)); statement.setString(2, taskId); @@ -544,19 +602,20 @@ * @param endDate la borne superieur * @return un hashmap {key: Id, value: task} */ - public HashMap<String, ReportTask> getReportByProject(Long startDate, Long endDate, String[] toReport){ + public HashMap<String, ReportTask> getReportByProject(Long startDate, Long endDate, String[] toReport ){ HashMap<String, ReportTask> map = new HashMap<>(); PreparedStatement statement = null; try{ - statement = connection.prepareStatement(config.getStorageQuerySelectReportByProject()); + statement = getConnection().prepareStatement(config.getStorageQuerySelectReportByProject()); statement.setObject(1, new Date(startDate)); statement.setObject(2, new Date(endDate)); statement.setObject(3, toReport); + ResultSet rs = statement.executeQuery(); while(rs.next()) { @@ -580,40 +639,35 @@ } /** - * Methode qui retourne les taches minutees entre deux bornes par jour - * @param startDate borne inferieur + * Methode qui retourne les taches minutees entre deux bornes par projet + * @param startDate la borne inferieur * @param endDate la borne superieur - * @return un hashmap {key: parentId, value: array of task} + * @return un hashmap {key: Id, value: task} */ - public HashMap<String,Set<ReportTask>> getReportByDay(Long startDate, Long endDate, String[] toReport){ - HashMap<String,Set<ReportTask>> res = new HashMap<>(); + public HashMap<String, ReportTask> getReportByProjectAndTags(Long startDate, Long endDate, String[] toReport, String[] wantedTags){ + + HashMap<String, ReportTask> map = new HashMap<>(); + PreparedStatement statement = null; try{ - statement = connection.prepareStatement(config.getStorageQuerySelectReportByDay()); - // les bornes + statement = getConnection().prepareStatement(config.getStorageQuerySelectReportByProjectAndTags()); + statement.setObject(1, new Date(startDate)); statement.setObject(2, new Date(endDate)); statement.setObject(3, toReport); + statement.setObject(4, wantedTags); ResultSet rs = statement.executeQuery(); while(rs.next()) { - String period = rs.getString("day")+" - " +rs.getString("month") +" - "+ rs.getString("year"); - - //Si le Year n'est pas présent - if(res.get(period)==null) { - res.put(period, new HashSet<ReportTask>()); - } - String name = rs.getString("name"); String taskId = rs.getString("taskId"); long totalDuration = rs.getLong("totalduration"); ReportTask task = new ReportTask(name,taskId,totalDuration); - //On ajoute le resultat: year, set de reportTask - res.get(period).add(task); + map.put(taskId, task); } }catch(SQLException ex) { @@ -622,62 +676,33 @@ closeStatement(statement); } - return res; + + return map; } /** - * Methode qui retourne les taches minutees entre deux bornes par semaine - * @param startDate borne inferieur - * @param endDate la borne superieur - * @return un hashmap {key: parentId, value: array of task} + * Method that returns the collection of timed tasks for a time interval grouped by period + * @param startDate the start date + * @param endDate the end date + * @param toReport task to be reported + * @return the collection of timed tasks */ - public HashMap<String,Set<ReportTask>> getReportByWeek(Long startDate, Long endDate, String[] toReport){ + public HashMap<String,Set<ReportTask>> getReportByPeriod(Long startDate, Long endDate, String[] toReport, String type){ + HashMap<String,Set<ReportTask>> res = new HashMap<>(); PreparedStatement statement = null; try{ - statement = connection.prepareStatement(config.getStorageQuerySelectReportByWeek()); - // les bornes - statement.setObject(1, new Date(startDate)); - statement.setObject(2, new Date(endDate)); - statement.setObject(3, toReport); - - ResultSet rs = statement.executeQuery(); - while(rs.next()) { - - String period = rs.getString("week") +" - "+ rs.getString("year"); - - //Si le Year n'est pas présent - if(res.get(period)==null) { - res.put(period, new HashSet<ReportTask>()); - } - - String name = rs.getString("name"); - String taskId = rs.getString("taskId"); - long totalDuration = rs.getLong("totalduration"); - - ReportTask task = new ReportTask(name,taskId,totalDuration); - - //On ajoute le resultat: year, set de reportTask - res.get(period).add(task); + if("Year".equals(type)){ + statement = getConnection().prepareStatement(config.getStorageQuerySelectReportByYear()); + }else if("Month".equals(type)){ + statement = getConnection().prepareStatement(config.getStorageQuerySelectReportByMonth()); + }else if("Week".equals(type)){ + statement = getConnection().prepareStatement(config.getStorageQuerySelectReportByWeek()); + }else{ + statement = getConnection().prepareStatement(config.getStorageQuerySelectReportByDay()); } - }catch(SQLException ex) { - throw new StorageException("Can't get report", ex); - } finally { - closeStatement(statement); - } - - return res; - } - - public HashMap<String,Set<ReportTask>> getReportByMonth(Long startDate, Long endDate, String[] toReport){ - - HashMap<String,Set<ReportTask>> res = new HashMap<>(); - PreparedStatement statement = null; - - try{ - statement = connection.prepareStatement(config.getStorageQuerySelectReportByMonth()); // les bornes statement.setObject(1, new Date(startDate)); statement.setObject(2, new Date(endDate)); @@ -685,9 +710,17 @@ ResultSet rs = statement.executeQuery(); while(rs.next()) { + String period; + if("Year".equals(type)){ + period = rs.getString("year"); + }else if("Month".equals(type)){ + period = rs.getString("month") +" - "+ rs.getString("year"); + }else if("Week".equals(type)){ + period = rs.getString("week") +" - "+ rs.getString("year"); + }else { + period = rs.getString("day") + " - " + rs.getString("month") + " - " + rs.getString("year"); + } - String period = rs.getString("month") +" - "+ rs.getString("year"); - //Si le Year n'est pas présent if(res.get(period)==null) { res.put(period, new HashSet<ReportTask>()); @@ -713,32 +746,51 @@ } /** - * Method that returns the collection of timed tasks + * Method that returns the collection of timed tasks for a time interval grouped by period * @param startDate the start date * @param endDate the end date * @param toReport task to be reported * @return the collection of timed tasks */ - public HashMap<String,Set<ReportTask>> getReportByYear(Long startDate, Long endDate, String[] toReport){ + public HashMap<String,Set<ReportTask>> getReportByPeriodAndTags(Long startDate, Long endDate, String[] toReport,String[] tags, String type){ HashMap<String,Set<ReportTask>> res = new HashMap<>(); PreparedStatement statement = null; try{ - statement = connection.prepareStatement(config.getStorageQuerySelectReportByYear()); + if("Year".equals(type)){ + statement = getConnection().prepareStatement(config.getStorageQuerySelectReportByYearAndTags()); + }else if("Month".equals(type)){ + statement = getConnection().prepareStatement(config.getStorageQuerySelectReportByMonthAndTags()); + }else if("Week".equals(type)){ + String sql = config.getStorageQuerySelectReportByWeekAndTags(); + statement = getConnection().prepareStatement(sql); + }else{ + statement = getConnection().prepareStatement(config.getStorageQuerySelectReportByDayAndTags()); + } + // les bornes statement.setObject(1, new Date(startDate)); statement.setObject(2, new Date(endDate)); statement.setObject(3, toReport); + statement.setObject(4, tags); ResultSet rs = statement.executeQuery(); while(rs.next()) { + String period; + if("Year".equals(type)){ + period = rs.getString("year"); + }else if("Month".equals(type)){ + period = rs.getString("month") +" - "+ rs.getString("year"); + }else if("Week".equals(type)){ + period = rs.getString("week") +" - "+ rs.getString("year"); + }else { + period = rs.getString("day") + " - " + rs.getString("month") + " - " + rs.getString("year"); + } - String year = rs.getString("year"); - //Si le Year n'est pas présent - if(res.get(year)==null) { - res.put(year, new HashSet<ReportTask>()); + if(res.get(period)==null) { + res.put(period, new HashSet<ReportTask>()); } String name = rs.getString("name"); @@ -748,7 +800,7 @@ ReportTask task = new ReportTask(name,taskId,totalDuration); //On ajoute le resultat: year, set de reportTask - res.get(year).add(task); + res.get(period).add(task); } }catch(SQLException ex) { @@ -770,16 +822,17 @@ public void modifyTask(TimerTask task) { PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryUpdateTask()); + statement = getConnection().prepareStatement(config.getStorageQueryUpdateTask()); statement.setString(1, task.getName()); //name statement.setString(2, task.getParent()); //parent statement.setBoolean(3, task.isClosed()); //hidden - statement.setString(4, null /*project.getNote()*/);//note + statement.setString(4, task.getDescription());//note statement.setObject(5, task.getModificationDate()); //modificationDate + statement.setObject(6,task.getTags()); - statement.setLong(6,task.getRemoved()); - statement.setString(7, task.getTaskId()); //taskId + statement.setLong(7,task.getRemoved()); + statement.setString(8, task.getTaskId()); //taskId statement.executeUpdate(); } catch (SQLException ex) { @@ -796,7 +849,7 @@ public void modifyTime(TimerTime t) { PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryUpdateTime()); + statement = getConnection().prepareStatement(config.getStorageQueryUpdateTime()); statement.setObject(1, t.getCreationDate()); statement.setLong(2, t.getTime()); @@ -820,7 +873,7 @@ PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryUpdateAlarm()); + statement = getConnection().prepareStatement(config.getStorageQueryUpdateAlarm()); statement.setLong(1, t.getRemainingHour()); statement.setLong(2, t.getRemainingMin()); @@ -838,15 +891,15 @@ /** * Methoque qui met à jour le path d'une tache * @param taskId l'identifiant de la tache - * @param array l'array representant la tache et ses parents dans l'ordre parent -> enfant + * @param path l'array representant la tache et ses parents dans l'ordre parent -> enfant * */ - public void updateTaskPath(String taskId, String[] array){ + public void updateTaskPath(String taskId, String[] path){ PreparedStatement statement = null; try { //On update celui du fils - statement = connection.prepareStatement(config.getStorageQueryUpdateTaskPath()); - statement.setObject(1, array); + statement = getConnection().prepareStatement(config.getStorageQueryUpdateTaskPath()); + statement.setObject(1, path); statement.setString(2, taskId); statement.executeUpdate(); }catch (SQLException ex) { @@ -868,7 +921,7 @@ PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryDeleteTime()); + statement = getConnection().prepareStatement(config.getStorageQueryDeleteTime()); statement.setString(1, timeId); statement.executeUpdate(); } catch (SQLException ex) { @@ -886,7 +939,7 @@ public void deleteTaskWithId(String taskId) { PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryDeleteTask()); + statement = getConnection().prepareStatement(config.getStorageQueryDeleteTask()); statement.setString(1, taskId); statement.executeUpdate(); } catch (SQLException ex) { @@ -907,7 +960,7 @@ PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryUpdateTimeToRemove()); + statement = getConnection().prepareStatement(config.getStorageQueryUpdateTimeToRemove()); statement.setLong(1, time); statement.setObject(2, new Date(time)); @@ -929,7 +982,7 @@ public void removeTaskWithId(String taskId, long time) { PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryUpdateTaskToRemove()); + statement = getConnection().prepareStatement(config.getStorageQueryUpdateTaskToRemove()); statement.setLong(1, time); statement.setObject(2, new Date(time)); @@ -948,7 +1001,7 @@ public void removeAlarmWithId(String alarmId, long time) { PreparedStatement statement = null; try { - statement = connection.prepareStatement(config.getStorageQueryUpdateAlarmToRemove()); + statement = getConnection().prepareStatement(config.getStorageQueryUpdateAlarmToRemove()); statement.setLong(1, time); statement.setObject(2, new Date(time)); Modified: branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/ReportResource.java =================================================================== --- branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/ReportResource.java 2014-08-01 14:59:12 UTC (rev 3023) +++ branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/ReportResource.java 2014-08-04 12:39:19 UTC (rev 3024) @@ -15,7 +15,6 @@ import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; -import java.util.Set; /** * Created by olivia on 11/06/14. @@ -44,12 +43,18 @@ @Post("json") public Representation getReport(Representation representation) throws IOException { // Les elements voulus par l'interface + String[][] tmp; String[] toReport; + String[] wantedTags; + //On recupere les elements String repr1 = representation.getText(); Gson gson = new Gson(); - toReport = gson.fromJson(repr1, String[].class); + tmp = gson.fromJson(repr1, String[][].class); + //On extrait les elements voulus + toReport = tmp[0]; + wantedTags = tmp[1]; String json="[No data]"; String type =""; @@ -81,102 +86,38 @@ //On ne recupere que les elements interessants gson = new GsonBuilder().excludeFieldsWithoutExposeAnnotation().create(); - if(type.equals("Project")) { + if(wantedTags.length == 0) { + if (type.equals("Project")) { - HashMap<String, ReportTask> storedTask = storage.getReportByProject(startDate, endDate,toReport); - json = gson.toJson(storedTask, HashMap.class); + HashMap<String, ReportTask> storedTask = storage.getReportByProject(startDate, endDate, toReport); + json = gson.toJson(storedTask, HashMap.class); - }else if(type.equals("Day")){ + } else { - HashMap storedTask = storage.getReportByDay(startDate, endDate, toReport); - json = gson.toJson(storedTask, HashMap.class); + HashMap storedTask = storage.getReportByPeriod(startDate, endDate, toReport, type); + json = gson.toJson(storedTask, HashMap.class); - }else if(type.equals("Month")){ + } + }else{ - HashMap storedTask = storage.getReportByMonth(startDate, endDate, toReport); - json = gson.toJson(storedTask, HashMap.class); + if (type.equals("Project")) { - }else if(type.equals("Week")){ + HashMap<String, ReportTask> storedTask = storage.getReportByProjectAndTags(startDate, endDate, toReport, wantedTags); + json = gson.toJson(storedTask, HashMap.class); - HashMap storedTask = storage.getReportByWeek(startDate, endDate, toReport); - json = gson.toJson(storedTask, HashMap.class); + } else { - }else if(type.equals("Year")){ + HashMap storedTask = storage.getReportByPeriodAndTags(startDate, endDate, toReport, wantedTags, type); + json = gson.toJson(storedTask, HashMap.class); - HashMap storedTask = storage.getReportByYear(startDate, endDate,toReport); - json = gson.toJson(storedTask, HashMap.class); - + } } return new StringRepresentation(json); } - /** - * Method that sorts in a array a mapped tree - * @param map the map to sort - * @param rt the current task - * @param res the tree array - */ - private void recursivSortTask(HashMap<String,Set<ReportTask>> map, ReportTask rt, ArrayList<ReportTask> res ){ - if(map.containsKey(rt.getTaskId())) { - for (ReportTask child : map.get(rt.getTaskId())) { - //On attache le parent - child.setRtParent(rt); - - //Appel recursif sur les enfants - recursivSortTask(map, child, res); - - //Si le noeud enfant est a du temps alors on l'ajoute - if(child.getSelftime()!= (long) 0 || child.getChildtime()!= (long) 0){ - rt.addChild(child); - rt.addChildTime(child.getSelftime()); - } - } - } - - if(rt.getParentId().equals("") && rt.getChildtime() != 0){ - res.add(rt); - } - } - /** - * Methode reccursive qui enleve les elements indesirables - * @param toCheck la liste a verifiee - * @param wanted la liste d'identifiant voulu - */ - private void recursiveRemoveUnreportedTask(ArrayList<ReportTask> toCheck, ArrayList<String> wanted){ - //iterator sur l'arraylist a checker - Iterator<ReportTask> iter = toCheck.iterator(); - - while(iter.hasNext()) { - //Le noeud courant - ReportTask repT= iter.next(); - - //Le noeud a des enfants - if(!repT.getSubtasks().isEmpty()) { - //On va supprimer les noeuds non souhaites dans les enfants - recursiveRemoveUnreportedTask(repT.getSubtasks(), wanted); - } - //On vérifie si dans la liste des elements voulus - if(!idIsInArray(repT.getTaskId(),wanted) ){ - if(repT.hasParent()) { - repT.getRtParent().removeChildTime(repT.getSelftime()); - //repT.setSelfTime((long)0); - - } - if(repT.getChildtime() == 0){ - iter.remove(); - } - - - } - - } - - } - - /** * Methode qui détermine si l'id est dans la collection * @param id l'identifiant a trouver * @param list la list a parcourir Modified: branches/ng-jtimer/src/main/resources/jtimer-default.properties =================================================================== --- branches/ng-jtimer/src/main/resources/jtimer-default.properties 2014-08-01 14:59:12 UTC (rev 3023) +++ branches/ng-jtimer/src/main/resources/jtimer-default.properties 2014-08-04 12:39:19 UTC (rev 3024) @@ -2,7 +2,7 @@ # jTimer default properties ### # jTimer storage path -jtimer.storage.path=/home/olivia/Bureau/jtimer/jtimer/BDDNNe +jtimer.storage.path=/home/olivia/Bureau/jtimer/jtimer/BDDDevReu ### # SQL properties ### @@ -13,8 +13,8 @@ jtimer.storage.table.alarm=taskalarm ### #CREATE SCHEMA -jtimer.storage.create.table.version=CREATE TABLE version (version VARCHAR(10)) -jtimer.storage.create.table.task=CREATE TABLE task \ +jtimer.storage.create.table.version=CREATE TABLE IF NOT EXISTS version (version VARCHAR(10)) +jtimer.storage.create.table.task=CREATE TABLE IF NOT EXISTS task \ (taskId VARCHAR(255) NOT NULL, \ name VARCHAR(255) NOT NULL, \ parent VARCHAR(255), \ @@ -22,10 +22,11 @@ modificationDate Timestamp, \ hidden BOOLEAN, \ path ARRAY, \ - note TEXT, \ + description TEXT, \ + tags ARRAY, \ removed LONG, \ PRIMARY KEY (taskId)) -jtimer.storage.create.table.time=CREATE TABLE tasktime \ +jtimer.storage.create.table.time=CREATE TABLE IF NOT EXISTS tasktime \ (taskid VARCHAR(255) NOT NULL, \ creationDate Timestamp, \ uuid varchar(255) unique, \ @@ -36,7 +37,7 @@ FOREIGN KEY (taskid) \ REFERENCES task (taskId) \ ON DELETE CASCADE) -jtimer.storage.create.alarm=CREATE TABLE taskalarm \ +jtimer.storage.create.alarm=CREATE TABLE IF NOT EXISTS taskalarm \ (taskId VARCHAR(255) NOT NULL, \ alarmId VARCHAR(255) NOT NULL, \ name VARCHAR(255) NOT NULL, \ @@ -52,9 +53,12 @@ REFERENCES task (taskId) \ ON DELETE CASCADE) #### +#ALIAS +jtimer.storage.alias.containsone=CREATE ALIAS IF NOT EXISTS ARRAY_CONTAINS_ONE FOR "org.chorem.jtimer.storage.Storage.containsOne"; +#### #INSERT jtimer.storage.insert.version.number=INSERT INTO VERSION VALUES('2.0') -jtimer.storage.insert.task=INSERT INTO task (name, parent, taskId, hidden, note,creationDate, modificationDate, removed) \ +jtimer.storage.insert.task=INSERT INTO task (name, parent, taskId, hidden, description,creationDate, modificationDate, removed) \ VALUES (?, ?, ?, ?, ?, ?, ?, ?) jtimer.storage.insert.time=INSERT INTO tasktime (taskid, creationDate, uuid, duration, modificationDate, removed) \ VALUES (?, ?, ?, ?, ?, ?) @@ -142,30 +146,50 @@ AND ARRAY_CONTAINS(?, TA.taskid) \ AND TA.removed = 0 \ GROUP BY TA.taskId , day, month, year -jtimer.storage.select.report.roottasks=SELECT * \ - FROM task \ - WHERE (taskId not in (SELECT taskid FROM tasktime) AND removed = 0 ) -jtimer.storage.select.report.week.timedtasks=SELECT WEEK(TI.creationDate) as week, YEAR(TI.creationDate) as year, TA.* , sum(TI.duration) AS totalduration \ - FROM task TA, tasktime TI \ - WHERE TA.taskId = TI.taskid \ +jtimer.storage.select.report.byProjectAndTag=SELECT TA.*, sum(TI.duration) AS totalduration \ + FROM task TA, tasktime TI \ + WHERE TA.taskId = TI.taskid \ AND TI.creationDate BETWEEN ? AND ? \ + AND ARRAY_CONTAINS(?, TA.taskid) \ + AND ARRAY_CONTAINS_ONE(TA.tags, ?) \ AND TA.removed = 0 \ - GROUP BY TA.taskId, week, year \ - ORDER BY TA.creationDate DESC -jtimer.storage.select.report.year.timedtasks=SELECT YEAR(TI.creationDate) as year, TA.* , sum(TI.duration) AS totalduration \ - FROM task TA, tasktime TI \ - WHERE TA.taskId = TI.taskid \ + GROUP BY TA.taskId +jtimer.storage.select.report.byYearAndTag=SELECT YEAR(TI.creationDate) as year, TA.*, sum(TI.duration) AS totalduration \ + FROM task TA, tasktime TI \ + WHERE TA.taskId = TI.taskid \ AND TI.creationDate BETWEEN ? AND ? \ + AND ARRAY_CONTAINS(TA.path, TA.taskid) \ + AND ARRAY_CONTAINS(?, TA.taskid) \ + AND ARRAY_CONTAINS_ONE(TA.tags, ?) \ AND TA.removed = 0 \ - GROUP BY TA.taskId, year \ - ORDER BY TA.creationDate DESC -jtimer.storage.select.report.month.timedtasks=SELECT MONTH(TI.creationDate) as month, YEAR(TI.creationDate) as year, TA.* , sum(TI.duration) AS totalduration \ - FROM task TA, tasktime TI \ - WHERE TA.taskId = TI.taskid \ + GROUP BY TA.taskId , year +jtimer.storage.select.report.byMonthAndTag=SELECT MONTH(TI.creationDate) as month, YEAR(TI.creationDate) as year, TA.*, sum(TI.duration) AS totalduration \ + FROM task TA, tasktime TI \ + WHERE TA.taskId = TI.taskid \ AND TI.creationDate BETWEEN ? AND ? \ + AND ARRAY_CONTAINS(TA.path, TA.taskid) \ + AND ARRAY_CONTAINS(?, TA.taskid) \ + AND ARRAY_CONTAINS_ONE(TA.tags, ?) \ AND TA.removed = 0 \ - GROUP BY TA.taskId, year, month \ - ORDER BY TA.creationDate DESC + GROUP BY TA.taskId , month, year +jtimer.storage.select.report.byWeekAndTag=SELECT WEEK(TI.creationDate) as week,YEAR(TI.creationDate) as year, TA.*, sum(TI.duration) AS totalduration \ + FROM task TA, tasktime TI \ + WHERE TA.taskId = TI.taskid \ + AND TI.creationDate BETWEEN ? AND ? \ + AND ARRAY_CONTAINS(TA.path, TA.taskid) \ + AND ARRAY_CONTAINS(?, TA.taskid) \ + AND ARRAY_CONTAINS_ONE(?, TA.tags) \ + AND TA.removed = 0 \ + GROUP BY TA.taskId , week, year +jtimer.storage.select.report.byDayAndTag=SELECT EXTRACT(DAY FROM TI.creationDate) as day,MONTH(TI.creationDate) as month,YEAR(TI.creationDate) as year, TA.*, sum(TI.duration) AS totalduration \ + FROM task TA, tasktime TI \ + WHERE TA.taskId = TI.taskid \ + AND TI.creationDate BETWEEN ? AND ? \ + AND ARRAY_CONTAINS(TA.path, TA.taskid) \ + AND ARRAY_CONTAINS(?, TA.taskid) \ + AND ARRAY_CONTAINS_ONE(TA.tags, ?) \ + AND TA.removed = 0 \ + GROUP BY TA.taskId , day, month, year jtimer.storage.select.alarm.ondate= SELECT * \ FROM taskalarm \ WHERE modificationDate > ? @@ -182,7 +206,7 @@ jtimer.storage.update.task.path=UPDATE task \ SET path=? \ WHERE taskId = ? -jtimer.storage.update.task.withid=UPDATE task SET name=?, parent=?, hidden=?, note=?, modificationDate=?, removed=? \ +jtimer.storage.update.task.withid=UPDATE task SET name=?, parent=?, hidden=?, description=?, modificationDate=?, tags=?, removed=? \ WHERE taskId = ? jtimer.storage.update.time.withid=UPDATE tasktime SET creationDate=?, duration=?, modificationDate=?, removed = ? \ WHERE uuid = ?