From f39e1a18e4ceed8ad6edb7fc4b7e4d1879703dd5 Mon Sep 17 00:00:00 2001
From: INOPIAE <m.maengel@inopiae.de>
Date: Sat, 9 Jul 2016 17:45:36 +0200
Subject: [PATCH] add: check if the last valid test is within the last 12 month

Change-Id: I9da1a71df81ec68fc7dc6363737b666e65840721
---
 src/org/cacert/gigi/dbObjects/CATS.java       | 16 +++++++
 tests/org/cacert/gigi/dbObjects/TestCATS.java | 44 +++++++++++++++++++
 2 files changed, 60 insertions(+)
 create mode 100644 tests/org/cacert/gigi/dbObjects/TestCATS.java

diff --git a/src/org/cacert/gigi/dbObjects/CATS.java b/src/org/cacert/gigi/dbObjects/CATS.java
index cac16694..a353e168 100644
--- a/src/org/cacert/gigi/dbObjects/CATS.java
+++ b/src/org/cacert/gigi/dbObjects/CATS.java
@@ -42,6 +42,11 @@ public class CATS {
         }
     }
 
+    /**
+     * The maximal number of months a passed test is considered "recent".
+     */
+    public static final int TEST_MONTHS = 12;
+
     private static HashMap<String, Integer> names = new HashMap<>();
 
     private CATS() {
@@ -89,4 +94,15 @@ public class CATS {
             ps.execute();
         }
     }
+
+    public static boolean isInCatsLimit(int uID, int testID) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `cats_passed` WHERE `user_id` = ? AND `variant_id` = ? AND`pass_date` > (now() - interval '1 months' * ?)")) {
+            ps.setInt(1, uID);
+            ps.setInt(2, testID);
+            ps.setInt(3, TEST_MONTHS);
+
+            GigiResultSet rs = ps.executeQuery();
+            return rs.next();
+        }
+    }
 }
diff --git a/tests/org/cacert/gigi/dbObjects/TestCATS.java b/tests/org/cacert/gigi/dbObjects/TestCATS.java
new file mode 100644
index 00000000..721b7a65
--- /dev/null
+++ b/tests/org/cacert/gigi/dbObjects/TestCATS.java
@@ -0,0 +1,44 @@
+package org.cacert.gigi.dbObjects;
+
+import static org.junit.Assert.*;
+
+import java.io.IOException;
+import java.security.GeneralSecurityException;
+import java.sql.Date;
+
+import org.cacert.gigi.dbObjects.CATS.CATSType;
+import org.cacert.gigi.testUtils.ClientTest;
+import org.junit.Test;
+
+public class TestCATS extends ClientTest {
+
+    /**
+     * at least 11 months ago (but less than 12), so is inside the window of
+     * {@link CATS#TEST_MONTHS}
+     */
+    private static final Date min11month = new Date(System.currentTimeMillis() - 24L * 60 * 60 * 11 * 31 * 1000L);
+
+    /**
+     * at least 12 months ago, so is outside the window of
+     * {@link CATS#TEST_MONTHS}
+     */
+    private static final Date min12month = new Date(System.currentTimeMillis() - 24L * 60 * 60 * 12 * 31 * 1000L);
+
+    public TestCATS() throws GeneralSecurityException, IOException {}
+
+    @Test
+    public void testRAChallenge() throws IOException, GeneralSecurityException {
+        CATS.enterResult(User.getById(id), CATSType.ASSURER_CHALLENGE, min12month, "en_US", "1");
+        assertFalse(CATS.isInCatsLimit(id, CATSType.ASSURER_CHALLENGE.getId()));
+        CATS.enterResult(User.getById(id), CATSType.ASSURER_CHALLENGE, min11month, "en_US", "1");
+        assertTrue(CATS.isInCatsLimit(id, CATSType.ASSURER_CHALLENGE.getId()));
+    }
+
+    @Test
+    public void testCodeSigningChallenge() throws IOException, GeneralSecurityException {
+        CATS.enterResult(User.getById(id), CATSType.CODE_SIGNING_CHALLENGE_NAME, min12month, "en_US", "1");
+        assertFalse(CATS.isInCatsLimit(id, CATSType.CODE_SIGNING_CHALLENGE_NAME.getId()));
+        CATS.enterResult(User.getById(id), CATSType.CODE_SIGNING_CHALLENGE_NAME, min11month, "en_US", "1");
+        assertTrue(CATS.isInCatsLimit(id, CATSType.CODE_SIGNING_CHALLENGE_NAME.getId()));
+    }
+}
-- 
2.39.2