X-Git-Url: https://code.wpia.club/?a=blobdiff_plain;ds=sidebyside;f=src%2Forg%2Fcacert%2Fgigi%2FdbObjects%2FUser.java;h=c0b0128bdd37e28040605d141a80dd3fcad24b49;hb=4b9842acd7dd0e79de8fc3f88cb8241f9b2f134e;hp=5132b52fa04c72b698581307c1238429e541c641;hpb=7f4284fefac71623029edbb2a3bc8279f1d881e3;p=gigi.git

diff --git a/src/org/cacert/gigi/dbObjects/User.java b/src/org/cacert/gigi/dbObjects/User.java
index 5132b52f..c0b0128b 100644
--- a/src/org/cacert/gigi/dbObjects/User.java
+++ b/src/org/cacert/gigi/dbObjects/User.java
@@ -11,6 +11,8 @@ import java.util.Set;
 import org.cacert.gigi.GigiApiException;
 import org.cacert.gigi.database.GigiPreparedStatement;
 import org.cacert.gigi.database.GigiResultSet;
+import org.cacert.gigi.dbObjects.Assurance.AssuranceType;
+import org.cacert.gigi.dbObjects.CATS.CATSType;
 import org.cacert.gigi.localisation.Language;
 import org.cacert.gigi.output.DateSelector;
 import org.cacert.gigi.pages.PasswordResetPage;
@@ -48,6 +50,11 @@ public class User extends CertificateOwner {
 
     public static final boolean POJAM_ENABLED = false;
 
+    /**
+     * Time in months a verification is considered "recent".
+     */
+    public static final int VERIFICATION_MONTHS = 39;
+
     protected User(GigiResultSet rs) {
         super(rs.getInt("id"));
         updateName(rs);
@@ -161,7 +168,7 @@ public class User extends CertificateOwner {
     public boolean hasPassedCATS() {
         try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT 1 FROM `cats_passed` where `user_id`=? AND `variant_id`=?")) {
             query.setInt(1, getId());
-            query.setInt(2, CATS.ASSURER_CHALLANGE_ID);
+            query.setInt(2, CATSType.ASSURER_CHALLENGE.getId());
             try (GigiResultSet rs = query.executeQuery()) {
                 if (rs.next()) {
                     return true;
@@ -173,7 +180,7 @@ public class User extends CertificateOwner {
     }
 
     public int getAssurancePoints() {
-        try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT sum(points) FROM `notary` where `to`=? AND `deleted` is NULL AND (`expire` IS NULL OR `expire` > CURRENT_TIMESTAMP)")) {
+        try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT SUM(lastpoints) FROM ( SELECT DISTINCT ON (`from`) `from`, `to`, `points` as lastpoints, `method` FROM `notary` WHERE `deleted` is NULL AND (`expire` IS NULL OR `expire` > CURRENT_TIMESTAMP) AND `to` = ? ORDER BY  `from`, `when` DESC) as p")) {
             query.setInt(1, getId());
 
             GigiResultSet rs = query.executeQuery();
@@ -188,8 +195,9 @@ public class User extends CertificateOwner {
     }
 
     public int getExperiencePoints() {
-        try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT count(*) FROM `notary` where `from`=? AND `deleted` is NULL")) {
+        try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT count(*) FROM ( SELECT `to` FROM `notary` WHERE `from`=? AND `deleted` IS NULL AND `method` = ? ::`notaryType` GROUP BY `to`) as p")) {
             query.setInt(1, getId());
+            query.setString(2, AssuranceType.FACE_TO_FACE.getDescription());
 
             GigiResultSet rs = query.executeQuery();
             int points = 0;
@@ -354,40 +362,6 @@ public class User extends CertificateOwner {
 
     }
 
-    public boolean wantsDirectoryListing() {
-        try (GigiPreparedStatement get = new GigiPreparedStatement("SELECT listme FROM users WHERE id=?")) {
-            get.setInt(1, getId());
-            GigiResultSet exec = get.executeQuery();
-            return exec.next() && exec.getBoolean("listme");
-        }
-    }
-
-    public String getContactInformation() {
-        try (GigiPreparedStatement get = new GigiPreparedStatement("SELECT contactinfo FROM users WHERE id=?")) {
-            get.setInt(1, getId());
-
-            GigiResultSet exec = get.executeQuery();
-            exec.next();
-            return exec.getString("contactinfo");
-        }
-    }
-
-    public void setDirectoryListing(boolean on) {
-        try (GigiPreparedStatement update = new GigiPreparedStatement("UPDATE users SET listme = ? WHERE id = ?")) {
-            update.setBoolean(1, on);
-            update.setInt(2, getId());
-            update.executeUpdate();
-        }
-    }
-
-    public void setContactInformation(String contactInfo) {
-        try (GigiPreparedStatement update = new GigiPreparedStatement("UPDATE users SET contactinfo = ? WHERE id = ?")) {
-            update.setString(1, contactInfo);
-            update.setInt(2, getId());
-            update.executeUpdate();
-        }
-    }
-
     public boolean isInGroup(Group g) {
         return groups.contains(g);
     }
@@ -494,14 +468,26 @@ public class User extends CertificateOwner {
     }
 
     public String[] getTrainings() {
-        try (GigiPreparedStatement prep = new GigiPreparedStatement("SELECT `pass_date`, `type_text` FROM `cats_passed` LEFT JOIN `cats_type` ON `cats_type`.`id`=`cats_passed`.`variant_id`  WHERE `user_id`=? ORDER BY `pass_date` ASC")) {
+        try (GigiPreparedStatement prep = new GigiPreparedStatement("SELECT `pass_date`, `type_text`, `language`, `version` FROM `cats_passed` LEFT JOIN `cats_type` ON `cats_type`.`id`=`cats_passed`.`variant_id`  WHERE `user_id`=? ORDER BY `pass_date` ASC")) {
             prep.setInt(1, getId());
             GigiResultSet res = prep.executeQuery();
             List<String> entries = new LinkedList<String>();
 
             while (res.next()) {
-
-                entries.add(DateSelector.getDateFormat().format(res.getTimestamp(1)) + " (" + res.getString(2) + ")");
+                StringBuilder training = new StringBuilder();
+                training.append(DateSelector.getDateFormat().format(res.getTimestamp(1)));
+                training.append(" (");
+                training.append(res.getString(2));
+                if (res.getString(3).length() > 0) {
+                    training.append(" ");
+                    training.append(res.getString(3));
+                }
+                if (res.getString(4).length() > 0) {
+                    training.append(", ");
+                    training.append(res.getString(4));
+                }
+                training.append(")");
+                entries.add(training.toString());
             }
 
             return entries.toArray(new String[0]);
@@ -555,4 +541,14 @@ public class User extends CertificateOwner {
     private Assurance assuranceByRes(GigiResultSet res) {
         return new Assurance(res.getInt("id"), User.getById(res.getInt("from")), User.getById(res.getInt("to")), res.getString("location"), res.getString("method"), res.getInt("points"), res.getString("date"));
     }
+
+    public static boolean isInVerificationLimit(int id) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `notary` WHERE `to` = ? AND `when` > (now() - (interval '1 month' * ?)) AND (`expire` IS NULL OR `expire` > now()) AND `deleted` IS NULL;")) {
+            ps.setInt(1, id);
+            ps.setInt(2, VERIFICATION_MONTHS);
+
+            GigiResultSet rs = ps.executeQuery();
+            return rs.next();
+        }
+    }
 }