[Jtimer-commits] r3005 - in branches/ng-jtimer/src/main: java/org/chorem/jtimer/config java/org/chorem/jtimer/storage java/org/chorem/jtimer/web resources webapp/js

23 Jun 2014

Author: obruce
Date: 2014-06-23 17:38:30 +0200 (Mon, 23 Jun 2014)
New Revision: 3005

Url: http://forge.chorem.org/projects/jtimer/repository/revisions/3005

Log:
requ?\195?\170te sql mise dans les propri?\195?\169t?\195?\169s avec nuiton config

Removed:
   branches/ng-jtimer/src/main/resources/jtimer-query.properties
Modified:
   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/storage/Storage.java
   branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/RestApplication.java
   branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/TimesResource.java
   branches/ng-jtimer/src/main/resources/jtimer-default.properties
   branches/ng-jtimer/src/main/webapp/js/controllers.js

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-06-20 16:07:12 UTC (rev 3004)
+++ branches/ng-jtimer/src/main/java/org/chorem/jtimer/config/JtimerConfig.java	2014-06-23 15:38:30 UTC (rev 3005)
@@ -42,7 +42,6 @@
     }
 
 
-
     public JtimerConfig(String configFileName) throws Exception {
 
         try {
@@ -61,6 +60,7 @@
                 }
                 appConfig = defaultConfig;
             }
+
         } catch (Exception e) {
             throw new Exception("Can't read property file");
         }
@@ -74,4 +74,80 @@
     public String getStoragePath(){
         return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_PATH.getKey());
     }
+
+    public String getStorageTableTask(){return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_TABLE_TASK.getKey());}
+    public String getStorageTableTime(){return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_TABLE_TIME.getKey());}
+    public String getStorageTableVersion(){return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_TABLE_VERSION.getKey());}
+
+    public String getStorageQueryCreateTableVersion(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_CREATE_TABLE_VERSION.getKey());
+    }
+
+    public String getStorageQueryCreateTableTask(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_CREATE_TABLE_TASK.getKey());
+    }
+
+    public String getStorageQueryCreateTableTime(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_CREATE_TABLE_TIME.getKey());
+    }
+
+    public String getStorageQueryInsertVersionNumber(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_INSERT_VERSION_NUMBER.getKey());
+    }
+
+    public String getStorageQueryInsertTask(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_INSERT_TASK.getKey());
+    }
+
+    public String getStorageQueryInsertTime(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_INSERT_TIME.getKey());
+    }
+
+    public String getStorageQuerySelectTableName(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_TABLE_NAME.getKey());
+    }
+
+    public String getStorageQuerySelectCountTask(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_COUNT_TASK.getKey());
+    }
+
+    public String getStorageQuerySelectTimedTask(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_TIMED_TASK.getKey());
+    }
+    public String getStorageQuerySelectNotTimedTask(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_NOTTIMED_TASK.getKey());
+    }
+    public String getStorageQuerySelectTaskTimeWithDate(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_TIME_WITHDATE.getKey());
+    }
+    public String getStorageQuerySelectNotRemovedTaskTime(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_NOTREMOVED_TASKTIME.getKey());
+    }
+    public String getStorageQuerySelectRemovedTaskTime(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REMOVED_TASKTIME.getKey());
+    }
+    public String getStorageQuerySelectReportTasks(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_TIMEDTASKS.getKey());
+    }
+    public String getStorageQuerySelectReportRootTask(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_SELECT_REPORT_ROOTTASK.getKey());
+    }
+    public String getStorageQueryDeleteTime(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_DELETE_TIME_WITHID.getKey());
+    }
+    public String getStorageQueryDeleteTask(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_DELETE_TASK_WITHID.getKey());
+    }
+    public String getStorageQueryUpdateTask(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_UPDATE_TASK_WITHID.getKey());
+    }
+    public String getStorageQueryUpdateTime(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_UPDATE_TIME_WITHID.getKey());
+    }
+    public String getStorageQueryUpdateTaskToRemove(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_UPDATE_TASK_FORREMOVE.getKey());
+    }
+    public String getStorageQueryUpdateTimeToRemove(){
+        return appConfig.getOption(JtimerConfigOption.JTIMER_STORAGE_UPDATE_TIME_FORREMOVE.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-06-20 16:07:12 UTC (rev 3004)
+++ branches/ng-jtimer/src/main/java/org/chorem/jtimer/config/JtimerConfigOption.java	2014-06-23 15:38:30 UTC (rev 3005)
@@ -10,6 +10,7 @@
  * Package name : org.chorem.jtimer.config
  */
 public enum JtimerConfigOption implements ConfigOptionDef {
+    /**Storage**/
     JTIMER_STORAGE_PATH(
             "jtimer.storage.path",
             "chemin qui indique où se trouve le stockage",
@@ -19,8 +20,155 @@
             "jtimer.storage.credentials",
             "indique les éléments dont on a besoin pour s'authentifier à la base",
             "",
-            String.class);
+            String.class
+    ),
+    JTIMER_STORAGE_TABLE_TASK(
+            "jtimer.storage.table.task",
+            "le nom de la table des tâches",
+            "task",
+            String.class
+    ),
+    JTIMER_STORAGE_TABLE_TIME(
+            "jtimer.storage.table.time",
+            "le nom de la table des temps",
+            "tasktime",
+            String.class
+    ),
+    JTIMER_STORAGE_TABLE_VERSION(
+            "jtimer.storage.table.version",
+            "le nom de la table des version",
+            "tasktime",
+            String.class
+    ),
+    JTIMER_STORAGE_CREATE_TABLE_VERSION(
+            "jtimer.storage.create.table.version",
+            "requête de création la table des version",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_CREATE_TABLE_TASK(
+            "jtimer.storage.create.table.task",
+            "requête de création la table des tâches",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_CREATE_TABLE_TIME(
+            "jtimer.storage.create.table.time",
+            "requête de création la table des temps",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_INSERT_VERSION_NUMBER(
+            "jtimer.storage.insert.version.number",
+            "requête d'insertion dans la table des versions",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_INSERT_TASK(
+            "jtimer.storage.insert.task",
+            "requête d'insertion dans la table des tâches",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_INSERT_TIME(
+            "jtimer.storage.insert.time",
+            "requête d'insertion dans la table des temps",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_SELECT_TABLE_NAME(
+            "jtimer.storage.select.table.name",
+            "requête de selection des noms dans le table name",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_SELECT_COUNT_TASK(
+            "jtimer.storage.select.count.task",
+            "requête comptant le nombre de tâches dans le stockaqe",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_SELECT_TIMED_TASK(
+            "jtimer.storage.select.timed.task",
+            "requête de selection des tâches minutées",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_SELECT_NOTTIMED_TASK(
+            "jtimer.storage.select.nottimed.task",
+            "requête de selection des tâches non minutées",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_SELECT_TIME_WITHDATE(
+            "jtimer.storage.select.time.withdate",
+            "requête de récupération de tous les temps",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_SELECT_NOTREMOVED_TASKTIME(
+            "jtimer.storage.select.notremoved.tasktime",
+            "requête de selection des temps de tâches non supprimés",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_SELECT_REMOVED_TASKTIME(
+            "jtimer.storage.select.removed.tasktime",
+            "requête de selection des temps de tâches non supprimés",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_SELECT_REPORT_TIMEDTASKS(
+            "jtimer.storage.select.report.timedtasks",
+            "requête de selection des taches minutées entre deux bornes",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_SELECT_REPORT_ROOTTASK(
+            "jtimer.storage.select.report.roottasks",
+            "requête de selection des taches root",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_DELETE_TIME_WITHID(
+            "jtimer.storage.delete.time.withid",
+            "requête de suppression d'un temps avec un identifiant",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_DELETE_TASK_WITHID(
+            "jtimer.storage.delete.task.withid",
+            "requête de suppression d'une tâches avec un identifiant",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_UPDATE_TASK_WITHID(
+            "jtimer.storage.update.task.withid",
+            "requête de mise à jour d'une taches avec un identifiant",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_UPDATE_TIME_WITHID(
+            "jtimer.storage.update.time.withid",
+            "requête de mise à jour d'un temps avec un identifiant",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_UPDATE_TASK_FORREMOVE(
+            "jtimer.storage.update.task.forremove",
+            "requête de de changement de statut (remove) d'une tâches avec un identifiant",
+            "",
+            String.class
+    ),
+    JTIMER_STORAGE_UPDATE_TIME_FORREMOVE(
+            "jtimer.storage.update.time.forremove",
+            "requête de de changement de statut (remove) d'un temps avec un identifiant",
+            "",
+            String.class
+    );
 
+
+
     public final String key;
     public final String description;
     public String defaultValue;

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-06-20 16:07:12 UTC (rev 3004)
+++ branches/ng-jtimer/src/main/java/org/chorem/jtimer/storage/Storage.java	2014-06-23 15:38:30 UTC (rev 3005)
@@ -2,6 +2,7 @@
 
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
+import org.chorem.jtimer.config.JtimerConfig;
 import org.chorem.jtimer.entities.TimerTask;
 import org.chorem.jtimer.entities.TimerTime;
 import org.chorem.jtimer.utils.ReportTask;
@@ -14,9 +15,7 @@
 import java.sql.Statement;
 import java.util.ArrayList;
 import java.util.Date;
-import java.util.HashMap;
 import java.util.List;
-import java.util.Map;
 
 /**
  * Implementation du stockage des taches en base de données basée sur h2.
@@ -39,12 +38,14 @@
     protected static final String TABLE_VERSION = "version";
 
     protected String STORAGE_PATH;
+    protected JtimerConfig config;
 
     protected Connection connection;
 
-    public Storage(String path) {
+    public Storage(JtimerConfig conf) {
 
-        this.STORAGE_PATH = path;
+        this.config = conf;
+        this.STORAGE_PATH = config.getStoragePath();
 
         try {
             if (log.isDebugEnabled()) {
@@ -91,7 +92,7 @@
 
             // set new version in schema
             Statement statement = conn.createStatement();
-            statement.executeUpdate("INSERT INTO VERSION VALUES('2.0')");
+            statement.executeUpdate(config.getStorageQueryInsertVersionNumber());
         }
 
         return conn;
@@ -124,7 +125,7 @@
         Statement statement = null;
         try {
             statement = conn.createStatement();
-            ResultSet rs = statement.executeQuery("SELECT table_name FROM information_schema.tables;");
+            ResultSet rs = statement.executeQuery(config.getStorageQuerySelectTableName());
             while (rs.next()) {
                 String name = rs.getString(1);
                 if (TABLE_VERSION.equalsIgnoreCase(name)) {
@@ -148,30 +149,11 @@
         Statement statement = null;
         try {
             statement = conn.createStatement();
-            statement.executeUpdate("CREATE TABLE " + TABLE_VERSION +
-                    "(version VARCHAR(10))");
-            statement.executeUpdate("CREATE TABLE " + TABLE_TASK +
-                    "(taskId VARCHAR(255) NOT NULL," +
-                    " name VARCHAR(255) NOT NULL," +
-                    " parent VARCHAR(255)," +
-                    " creationDate LONG, " +
-                    " modificationDate LONG, " +
-                    " hidden BOOLEAN," +
-                    " note TEXT," +
-                    " removed LONG," +
-                    " PRIMARY KEY (taskId))");
-            statement.executeUpdate("CREATE TABLE " + TABLE_TIME +
-                    "(taskid VARCHAR(255) NOT NULL," +
-                    " date LONG," +
-                    " uuid varchar(255) unique," +
-                    " duration LONG," +
-                    " modificationDate LONG," +
-                    " removed LONG," +
-                    " PRIMARY KEY (taskid, date, uuid)," +
-                    " FOREIGN KEY (taskid)" +
-                    " REFERENCES " + TABLE_TASK +"(taskId)" +
-                    " ON DELETE CASCADE" +
-                    ")");
+
+            statement.executeUpdate(config.getStorageQueryCreateTableVersion());
+            statement.executeUpdate(config.getStorageQueryCreateTableTask());
+            statement.executeUpdate(config.getStorageQueryDeleteTime());
+
         } catch (SQLException ex) {
             throw new StorageException("Can't create schema", ex);
         } finally {
@@ -191,8 +173,8 @@
         int result = 0;
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("SELECT count(*) FROM " + TABLE_TASK
-                                                    + " WHERE removed = 0");
+            statement = connection.prepareStatement(config.getStorageQuerySelectCountTask());
+
             ResultSet rs = statement.executeQuery();
             if (rs.next()) {
                 result = rs.getInt(1);
@@ -214,65 +196,9 @@
         PreparedStatement statement = null;
         PreparedStatement statement2 = null;
         try {
-            statement = connection.prepareStatement("SELECT TA.*, sum(TI.duration) AS totalduration FROM " +
-                    TABLE_TASK + " TA, " + TABLE_TIME + " TI" +
-                    " WHERE TA.taskId = TI.taskid" +
-                    " AND TA.modificationDate >" +date +
-                    " GROUP BY TA.taskId");
-            ResultSet rs = statement.executeQuery();
-            while (rs.next()) {
-                TimerTask task = new TimerTask();
-                task.setTaskId(rs.getString("taskId"));
-                task.setName(rs.getString("name"));
-                task.setParent(rs.getString("parent"));
-                task.setRemoved(rs.getLong("removed"));
-                task.setTodayTime(0);
-                task.setCreationDate(new java.util.Date(rs.getLong("creationDate")));
-                task.setModificationDate(new java.util.Date(rs.getLong("modificationDate")));
-                task.setTotalTime(rs.getLong("totalduration"));
-                tasks.add(task);
-            }
+            statement = connection.prepareStatement(config.getStorageQuerySelectTimedTask());
+            statement.setLong(1, date);
 
-            // not timed tasks
-            statement2 = connection.prepareStatement("SELECT * FROM " + TABLE_TASK +
-                    " WHERE (taskId not in (SELECT taskid FROM " + TABLE_TIME + "))" +
-                    " AND "+ TABLE_TASK +".modificationDate >" +date);
-            rs = statement2.executeQuery();
-            while (rs.next()) {
-                TimerTask task = new TimerTask();
-                task.setName(rs.getString("name"));
-                task.setTaskId(rs.getString("taskId"));
-                task.setParent(rs.getString("parent"));
-                task.setCreationDate(new java.util.Date(rs.getLong("creationDate")));
-                task.setModificationDate(new java.util.Date(rs.getLong("modificationDate")));
-                task.setRemoved(rs.getLong("removed"));
-                task.setTodayTime(0);
-                task.setTotalTime(0);
-                tasks.add(task);
-            }
-        } catch (SQLException ex) {
-            throw new StorageException("Can't get task", ex);
-        } finally {
-            closeStatement(statement);
-            closeStatement(statement2);
-        }
-        return tasks;
-    }
-
-    /**
-     * Query that returns not removed tasks
-     * @return tasks arraylist of tasks
-     */
-    public ArrayList<TimerTask> getRemovedTasks(Long date) {
-        ArrayList<TimerTask> tasks = new ArrayList<>();
-        PreparedStatement statement = null;
-        PreparedStatement statement2 = null;
-        try {
-            statement = connection.prepareStatement("SELECT TA.*, sum(TI.duration) AS totalduration FROM " +
-                    TABLE_TASK + " TA, " + TABLE_TIME + " TI" +
-                    " WHERE TA.taskId = TI.taskid" +
-                    " AND TA.removed > 0"+
-                    " GROUP BY TA.taskId");
             ResultSet rs = statement.executeQuery();
             while (rs.next()) {
                 TimerTask task = new TimerTask();
@@ -288,9 +214,9 @@
             }
 
             // not timed tasks
-            statement2 = connection.prepareStatement("SELECT * FROM " + TABLE_TASK +
-                    " WHERE (taskId not in (SELECT taskid FROM " + TABLE_TIME + "))" +
-                    " AND "+ TABLE_TASK +".modificationDate >" +date);
+            statement2 = connection.prepareStatement(config.getStorageQuerySelectNotTimedTask());
+            statement2.setLong(1, date);
+
             rs = statement2.executeQuery();
             while (rs.next()) {
                 TimerTask task = new TimerTask();
@@ -313,15 +239,12 @@
         return tasks;
     }
 
-
     /* Insert, Update, Delete */
 
     public void addTask(TimerTask task) {
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("INSERT INTO " +
-                    TABLE_TASK + "(name, parent, taskId, hidden, note,creationDate, modificationDate, removed)" +
-                    " VALUES (?, ?, ?, ?, ?,?, ?, ?)");
+            statement = connection.prepareStatement(config.getStorageQueryInsertTask());
             statement.setString(1, task.getName());
             statement.setString(2, task.getParent());
             statement.setString(3, task.getTaskId());
@@ -342,9 +265,8 @@
     public void modifyTask(TimerTask task) {
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("UPDATE " +
-                    TABLE_TASK + " SET name=?, parent=?, hidden=?, note=?, modificationDate=?, removed=?" +
-                    " WHERE taskId = ?");
+            statement = connection.prepareStatement(config.getStorageQueryUpdateTask());
+
             statement.setString(1, task.getName()); //name
             statement.setString(2, task.getParent()); //parent
             statement.setBoolean(3, task.isClosed()); //hidden
@@ -365,9 +287,8 @@
     public void modifyTime(TimerTime t) {
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("UPDATE " +
-                    TABLE_TIME + " SET date=?, duration=?, modificationDate=?, removed = ?" +
-                    " WHERE uuid = ?");
+            statement = connection.prepareStatement(config.getStorageQueryUpdateTime());
+
             statement.setLong(1, t.getCreationDate().getTime());
             statement.setLong(2, t.getTime());
             statement.setLong(3, t.getModificationDate().getTime()); //modificationDate
@@ -391,9 +312,7 @@
     public void addTaskTime(TimerTask task, Date date, String uuid, long duration) {
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("INSERT INTO " + TABLE_TIME +
-                    "(taskid, date, uuid, duration, modificationDate, removed)" +
-                    " VALUES(?, ?, ?, ?, ?, ?)");
+            statement = connection.prepareStatement(config.getStorageQueryInsertTime());
 
             statement.setString(1, task.getTaskId());
             statement.setLong(2, date.getTime());
@@ -416,9 +335,7 @@
     public void addTaskTime(TimerTime time) {
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("INSERT INTO " + TABLE_TIME +
-                    "(taskid, date, uuid, duration, modificationDate, removed)" +
-                    " VALUES(?, ?, ?, ?, ?, ?)");
+            statement = connection.prepareStatement(config.getStorageQueryInsertTime());
 
             statement.setString(1, time.getTaskId());
             statement.setLong(2, time.getCreationDate().getTime());
@@ -452,13 +369,11 @@
 
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("SELECT TA.taskId AS mytask, TI.* FROM " +
-                    TABLE_TASK + " TA, "+ TABLE_TIME + " TI" +
-                    " WHERE TA.taskId = TI.taskid" +
-                    " AND TI.modificationDate >" +date);
+            statement = connection.prepareStatement(config.getStorageQuerySelectTaskTimeWithDate());
 
-            ResultSet rs = statement.executeQuery();
+            statement.setLong(1, date);
 
+            ResultSet rs = statement.executeQuery();
             while (rs.next()) {
 
                 TimerTime time = new TimerTime();
@@ -491,11 +406,10 @@
         List<TimerTime> times = new ArrayList<>();
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("SELECT TI.*" +
-                    " FROM " + TABLE_TASK + " TA, "+ TABLE_TIME + " TI" +
-                    " WHERE TA.taskId = TI.taskid AND TA.taskId = '" +taskid +"' " +
-                    " AND TA.removed = 0" +
-                    " AND TI.modificationDate >" +date);
+            statement = connection.prepareStatement(config.getStorageQuerySelectNotRemovedTaskTime());
+            statement.setString(1, taskid);
+            statement.setLong(2, date);
+
             ResultSet rs = statement.executeQuery();
             while (rs.next()) {
                 TimerTime time = new TimerTime();
@@ -522,16 +436,11 @@
         PreparedStatement statement = null;
         PreparedStatement statement2 = null;
         try{
-            statement = connection.prepareStatement(
-                    "SELECT TA.taskId AS taskid, TA.name AS task, TA.parent AS parent," +
-                            " TA.creationDate AS creationDate,  sum(TI.duration) AS totalduration" +
-                    " FROM " + TABLE_TASK+" TA,"  + TABLE_TIME +" TI" +
-                    " WHERE TA.taskId = TI.taskid" +
-                    " AND TA.creationDate >" + startDate +
-                    " AND TA.creationDate <" + endDate +
-                    " GROUP BY TA.taskId" +
-                    " ORDER BY TA.creationDate DESC");
+            statement = connection.prepareStatement(config.getStorageQuerySelectReportTasks());
 
+            statement.setLong(1, startDate);
+            statement.setLong(2, endDate);
+
             ResultSet rs = statement.executeQuery();
             while(rs.next()){
                 list.add(new ReportTask(rs.getString("task"),rs.getString("taskid"),
@@ -539,10 +448,7 @@
             }
 
             // not timed tasks
-            statement2 = connection.prepareStatement("SELECT * FROM " + TABLE_TASK +
-                    " WHERE (taskId not in (SELECT taskid FROM " + TABLE_TIME + ")" +
-                            " AND parent = '' )"
-                    );
+            statement2 = connection.prepareStatement(config.getStorageQuerySelectReportRootTask());
 
             rs = statement2.executeQuery();
             while(rs.next()){
@@ -560,75 +466,7 @@
         return list;
     }
 
-    public Map getReportByWeek(Long startDate, Long endDate){
-        //treemap car date implemente comparable
-        HashMap<Integer, ArrayList> byWeekTasks = new HashMap<Integer, ArrayList>();
 
-        PreparedStatement statement = null;
-        PreparedStatement statement2 = null;
-
-        try{
-            statement = connection.prepareStatement(
-                    "SELECT WEEK(FROM_UNIXTIME(creationDate)) AS numWeek, TA.taskId AS taskid, TA.name AS task, TA.parent AS parent," +
-                            " TA.creationDate AS creationDate,  sum(TI.duration) AS totalduration" +
-                            " FROM " + TABLE_TASK+" TA,"  + TABLE_TIME +" TI" +
-                            " WHERE TA.taskId = TI.taskid" +
-                            " AND TA.creationDate >" + startDate +
-                            " AND TA.creationDate <" + endDate +
-                            " GROUP BY TA.taskId, numWeek " +
-                            " ORDER BY creationDate DESC");
-
-
-            ResultSet rs = statement.executeQuery();
-            while(rs.next()){
-                Integer numWeek = rs.getInt("numWeek");
-                ArrayList tasks;
-                if(byWeekTasks.containsKey(numWeek)){
-                    tasks = byWeekTasks.get(numWeek);
-                }else{
-                    tasks = new ArrayList();
-                }
-                tasks.add(new ReportTask(rs.getString("task"),rs.getString("taskid"),
-                        rs.getString("parent") , rs.getLong("totalduration"), rs.getLong("creationDate")));
-
-                byWeekTasks.put(numWeek, tasks);
-            }
-
-            // not timed tasks
-            statement2 = connection.prepareStatement("SELECT * FROM " + TABLE_TASK +
-                            " WHERE (taskId not in (SELECT taskid FROM " + TABLE_TIME + ")" +
-                            " AND parent = '' )"
-            );
-
-            rs = statement2.executeQuery();
-            while(rs.next()){
-                ArrayList tasks;
-
-                if(byWeekTasks.containsKey(0)){
-                    tasks = byWeekTasks.get(0);
-                }else{
-                    tasks = new ArrayList();
-                }
-                tasks.add(new ReportTask(rs.getString("name"), rs.getString("taskId"),
-                                rs.getString("parent"), (long) 0, rs.getLong("creationDate")));
-
-                byWeekTasks.put(0, tasks);
-            }
-
-
-        }catch(SQLException ex) {
-            throw new StorageException("Can't get report", ex);
-        } finally {
-            closeStatement(statement);
-            closeStatement(statement2);
-        }
-
-        log.info("le map " + byWeekTasks.toString());
-
-        return byWeekTasks;
-    }
-
-
     /** Suppression de tuple **/
 
     /**
@@ -639,8 +477,7 @@
 
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("DELETE FROM " +
-                    TABLE_TIME + " WHERE uuid = ?");
+            statement = connection.prepareStatement(config.getStorageQueryDeleteTime());
             statement.setString(1, timeId);
             statement.executeUpdate();
         } catch (SQLException ex) {
@@ -658,8 +495,7 @@
     public void deleteTaskWithId(String taskId) {
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("DELETE FROM " +
-                    TABLE_TASK + " WHERE taskId = ?");
+            statement = connection.prepareStatement(config.getStorageQueryDeleteTask());
             statement.setString(1, taskId);
             statement.executeUpdate();
         } catch (SQLException ex) {
@@ -680,11 +516,11 @@
 
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("UPDATE " +
-                    TABLE_TIME + " SET removed = "+time+", modificationDate =" +time +
-                    " WHERE uuid = ?");
+            statement = connection.prepareStatement(config.getStorageQueryUpdateTimeToRemove());
 
-            statement.setString(1, timeId);
+            statement.setLong(1, time);
+            statement.setLong(2, time);
+            statement.setString(3, timeId);
             statement.executeUpdate();
         } catch (SQLException ex) {
             throw new StorageException("Can't delete time", ex);
@@ -702,10 +538,11 @@
     public void removeTaskWithId(String taskId, long time) {
         PreparedStatement statement = null;
         try {
-            statement = connection.prepareStatement("UPDATE " +
-                    TABLE_TASK + " SET removed = "+time+", modificationDate =" +time +
-                    " WHERE taskId = '"+taskId +"'");
+            statement = connection.prepareStatement(config.getStorageQueryUpdateTaskToRemove());
 
+            statement.setLong(1, time);
+            statement.setLong(2, time);
+            statement.setString(3, taskId);
 
             statement.executeUpdate();
         } catch (SQLException ex) {

Modified: branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/RestApplication.java
===================================================================
--- branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/RestApplication.java	2014-06-20 16:07:12 UTC (rev 3004)
+++ branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/RestApplication.java	2014-06-23 15:38:30 UTC (rev 3005)
@@ -74,7 +74,7 @@
         jtimerConf = new JtimerConfig();
 
         //Initialisation du storage
-        Storage storage = new Storage(jtimerConf.getStoragePath());
+        Storage storage = new Storage(jtimerConf);
         
         context.getAttributes().put(Storage.class.getName(), storage);
 

Modified: branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/TimesResource.java
===================================================================
--- branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/TimesResource.java	2014-06-20 16:07:12 UTC (rev 3004)
+++ branches/ng-jtimer/src/main/java/org/chorem/jtimer/web/TimesResource.java	2014-06-23 15:38:30 UTC (rev 3005)
@@ -65,7 +65,7 @@
     }
 
     /**
-     * Methode qui retourne la representation de la liste de toutes les temps
+     * Methode qui retourne la representation de la liste de tous les temps
      * Suite a une requete de type GET
      *
      * @return

Modified: branches/ng-jtimer/src/main/resources/jtimer-default.properties
===================================================================
--- branches/ng-jtimer/src/main/resources/jtimer-default.properties	2014-06-20 16:07:12 UTC (rev 3004)
+++ branches/ng-jtimer/src/main/resources/jtimer-default.properties	2014-06-23 15:38:30 UTC (rev 3005)
@@ -3,4 +3,90 @@
 ###
 # jTimer storage path
 jtimer.storage.path=/home/olivia/Bureau/jtimer/jtimer8081
-
+###
+# SQL properties
+###
+#TABLE
+jtimer.storage.table.task=task
+jtimer.storage.table.time=tasktime
+jtimer.storage.table.version=version
+###
+#CREATE SCHEMA
+jtimer.storage.create.table.version=CREATE TABLE version (version VARCHAR(10))
+jtimer.storage.create.table.task=CREATE TABLE task \
+    (taskId VARCHAR(255) NOT NULL, \
+    name VARCHAR(255) NOT NULL, \
+    parent VARCHAR(255), \
+    creationDate LONG, \
+    modificationDate LONG, \
+    hidden BOOLEAN, \
+    note TEXT, \
+    removed LONG, \
+    PRIMARY KEY (taskId))
+jtimer.storage.create.table.time=CREATE TABLE tasktime \
+    (taskid VARCHAR(255) NOT NULL, \
+    date LONG, \
+    uuid varchar(255) unique, \
+    duration LONG, \
+    modificationDate LONG, \
+    removed LONG, \
+    PRIMARY KEY (taskid, date, uuid), \
+    FOREIGN KEY (taskid) \
+        REFERENCES task (taskId) \
+        ON DELETE CASCADE)
+####
+#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) VALUES (?, ?, ?, ?, ?,?, ?, ?)
+jtimer.storage.insert.time=INSERT INTO tasktime (taskid, date, uuid, duration, modificationDate, removed) VALUES(?, ?, ?, ?, ?, ?)
+###
+#SELECT
+jtimer.storage.select.table.name=SELECT table_name FROM information_schema.tables;
+jtimer.storage.select.count.task=SELECT count(*) FROM task WHERE removed = 0
+jtimer.storage.select.timed.task=SELECT TA.*, sum(TI.duration) AS totalduration \
+  FROM task TA, tasktime TI \
+   WHERE TA.taskId = TI.taskid \
+   AND TA.modificationDate > ? \
+   GROUP BY TA.taskId
+jtimer.storage.select.nottimed.task=SELECT * \
+  FROM task \
+  WHERE (taskId not in (SELECT taskid FROM task)) \
+  AND task.modificationDate > ?
+jtimer.storage.select.time.withdate=SELECT TA.taskId AS mytask, TI.* \
+   FROM task  TA, tasktime TI \
+   WHERE TA.taskId = TI.taskid AND TI.modificationDate > ?
+jtimer.storage.select.notremoved.tasktime=SELECT TI.* \
+  FROM task TA, tasktime TI \
+  WHERE TA.taskId = TI.taskid AND TA.taskId = '?' \
+  AND TA.removed = 0 \
+  AND TI.modificationDate > ?
+jtimer.storage.select.removed.tasktime=SELECT TA.*, sum(TI.duration) AS totalduration \
+  FROM task TA, tasktime TI \
+  WHERE TA.taskId = TI.taskid \
+  AND TA.removed > 0 \
+  GROUP BY TA.taskId
+jtimer.storage.select.report.timedtasks=SELECT TA.taskId AS taskid, TA.name AS task, TA.parent AS parent, \
+      TA.creationDate AS creationDate,  sum(TI.duration) AS totalduration \
+  FROM task TA, tasktime TI \
+  WHERE TA.taskId = TI.taskid\
+  AND TA.creationDate > ? \
+  AND TA.creationDate < ? \
+  GROUP BY TA.taskId \
+  ORDER BY TA.creationDate DESC
+jtimer.storage.select.report.roottasks=SELECT * \
+  FROM task \
+  WHERE (taskId not in (SELECT taskid FROM task) AND parent = '' )
+###
+#DELETE
+jtimer.storage.delete.time.withid=DELETE FROM tasktime WHERE uuid = ?
+jtimer.storage.delete.task.withid=DELETE FROM task WHERE taskId = ?
+###
+#UPDATE
+jtimer.storage.update.task.withid=UPDATE task SET name=?, parent=?, hidden=?, note=?, modificationDate=?, removed=? \
+  WHERE taskId = ?
+jtimer.storage.update.time.withid=UPDATE tasktime SET date=?, duration=?, modificationDate=?, removed = ? \
+  WHERE uuid = ?
+jtimer.storage.update.task.forremove=UPDATE task SET removed = ?, modificationDate = ? \
+  WHERE taskId = ?
+jtimer.storage.update.time.forremove=UPDATE tasktime SET removed = ?, modificationDate = ? \
+  WHERE uuid = ?

Deleted: branches/ng-jtimer/src/main/resources/jtimer-query.properties
===================================================================
--- branches/ng-jtimer/src/main/resources/jtimer-query.properties	2014-06-20 16:07:12 UTC (rev 3004)
+++ branches/ng-jtimer/src/main/resources/jtimer-query.properties	2014-06-23 15:38:30 UTC (rev 3005)
@@ -1,49 +0,0 @@
-###
-# This file contains all sql queries
-###
-#TABLE
-TABLE_TASK=task
-TABLE_TIME=tasktime
-TABLE_VERSION=version
-###
-#CREATE SCHEMA
-CREATE_TABLE_VERSION=CREATE TABLE version (version VARCHAR(10))
-CREATE_TABLE_TASK=CREATE TABLE task \
-    (taskId VARCHAR(255) NOT NULL, \
-    name VARCHAR(255) NOT NULL, \
-    parent VARCHAR(255), \
-    creationDate LONG, \
-    modificationDate LONG, \
-    hidden BOOLEAN, \
-    note TEXT, \
-    removed LONG, \
-    PRIMARY KEY (taskId))
-CREATE_TABLE_TIME=CREATE TABLE time \
-    (taskid VARCHAR(255) NOT NULL, \
-    date LONG, \
-    uuid varchar(255) unique, \
-    duration LONG, \
-    modificationDate LONG, \
-    removed LONG, \
-    PRIMARY KEY (taskid, date, uuid), \
-    FOREIGN KEY (taskid) \
-        REFERENCES task (taskId) \
-        ON DELETE CASCADE)
-####
-#INSERT
-INSERT_VERSION_NUMBER=INSERT INTO VERSION VALUES('2.0')
-INSERT_TASK=INSERT INTO ? (name, parent, taskId, hidden, note,creationDate, modificationDate, removed)" VALUES (?, ?, ?, ?, ?,?, ?, ?)
-INSERT_TIME=INSERT INTO ? (taskid, date, uuid, duration, modificationDate, removed) VALUES(?, ?, ?, ?, ?, ?)
-###
-#SELECT
-SELECT_TIMED_TASK="SELECT TA.*, sum(TI.duration) AS totalduration \
-  FROM ? TA, ? TI \
-   WHERE TA.taskId = TI.taskid \
-   AND TA.modificationDate > ? \
-   GROUP BY TA.taskId
-SELECT_NOTTIMED_TASK=SELECT * \
-  FROM ? \
-  WHERE (taskId not in (SELECT taskid FROM ?)) \
-  AND ?.modificationDate > ?
-###
-#DELETE
\ No newline at end of file

Modified: branches/ng-jtimer/src/main/webapp/js/controllers.js
===================================================================
--- branches/ng-jtimer/src/main/webapp/js/controllers.js	2014-06-20 16:07:12 UTC (rev 3004)
+++ branches/ng-jtimer/src/main/webapp/js/controllers.js	2014-06-23 15:38:30 UTC (rev 3005)
@@ -909,15 +909,8 @@
             var modalInstance = $modal.open({
                 templateUrl: 'partials/reportModal.html',
                 controller: ReportModalInstanceCtrl,
-                resolve: {
-                    times :  function () {
-                        return angular.copy($scope.data.times);
-                    },
-                    tasks : function () {
-                        return angular.copy($scope.data.tasks);
-                    }
-                }
 
+
             });
 
             modalInstance.result.then(function (item) {

    

[Jtimer-commits] r3005 - in branches/ng-jtimer/src/main: java/org/chorem/jtimer/config java/org/chorem/jtimer/storage java/org/chorem/jtimer/web resources webapp/js

obruce＠users.chorem.org