From 1bcc2e29be77cc7347141997de16b90a672d5287 Mon Sep 17 00:00:00 2001 From: INOPIAE Date: Wed, 13 Jul 2016 10:32:06 +0200 Subject: [PATCH] add: handling of multiple verifications by the same RA Agent fixes issue #13 Change-Id: I90956db3d55294626f5507c7f1cbb49b8ee4d2c5 --- src/org/cacert/gigi/dbObjects/User.java | 6 +- src/org/cacert/gigi/util/Notary.java | 8 +- .../cacert/gigi/dbObjects/TestAssurance.java | 122 ++++++++++++++++-- .../cacert/gigi/pages/wot/TestAssurance.java | 35 +++++ 4 files changed, 159 insertions(+), 12 deletions(-) diff --git a/src/org/cacert/gigi/dbObjects/User.java b/src/org/cacert/gigi/dbObjects/User.java index 8e9bc762..fe9ef754 100644 --- a/src/org/cacert/gigi/dbObjects/User.java +++ b/src/org/cacert/gigi/dbObjects/User.java @@ -11,6 +11,7 @@ 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; @@ -179,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(); @@ -194,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; diff --git a/src/org/cacert/gigi/util/Notary.java b/src/org/cacert/gigi/util/Notary.java index 901e5ef6..974c128a 100644 --- a/src/org/cacert/gigi/util/Notary.java +++ b/src/org/cacert/gigi/util/Notary.java @@ -17,6 +17,8 @@ import org.cacert.gigi.output.template.SprintfCommand; public class Notary { + public final static int LIMIT_DAYS_VERIFICATION = 90; // conf.getProperty("limit_days_verification"); + public static void writeUserAgreement(User member, String document, String method, String comment, boolean active, int secmemid) { try (GigiPreparedStatement q = new GigiPreparedStatement("INSERT INTO `user_agreements` SET `memid`=?, `secmemid`=?," + " `document`=?,`date`=NOW(), `active`=?,`method`=?,`comment`=?")) { q.setInt(1, member.getId()); @@ -33,13 +35,15 @@ public class Notary { if (assurer.getId() == target.getId()) { throw new GigiApiException("You cannot assure yourself."); } - try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `notary` where `to`=? and `from`=? AND `deleted` IS NULL")) { + try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `notary` where `to`=? and `from`=? and `method` = ? ::`notaryType` AND `deleted` IS NULL AND `when` > (now() - interval '1 days' * ?)")) { ps.setInt(1, target.getId()); ps.setInt(2, assurer.getId()); + ps.setString(3, AssuranceType.FACE_TO_FACE.getDescription()); + ps.setInt(4, LIMIT_DAYS_VERIFICATION); GigiResultSet rs = ps.executeQuery(); if (rs.next()) { rs.close(); - throw new GigiApiException("You have already assured this member."); + throw new GigiApiException(SprintfCommand.createSimple("You have already verified this applicant within the last {0} days.", LIMIT_DAYS_VERIFICATION)); } } if ( !assurer.canAssure()) { diff --git a/tests/org/cacert/gigi/dbObjects/TestAssurance.java b/tests/org/cacert/gigi/dbObjects/TestAssurance.java index d55f0797..44809c02 100644 --- a/tests/org/cacert/gigi/dbObjects/TestAssurance.java +++ b/tests/org/cacert/gigi/dbObjects/TestAssurance.java @@ -8,33 +8,39 @@ import java.sql.Timestamp; import org.cacert.gigi.GigiApiException; import org.cacert.gigi.database.GigiPreparedStatement; import org.cacert.gigi.testUtils.BusinessTest; +import org.cacert.gigi.util.DayDate; +import org.cacert.gigi.util.Notary; +import org.junit.Before; import org.junit.Test; public class TestAssurance extends BusinessTest { - private final Timestamp yesterday = new Timestamp(System.currentTimeMillis() - 24L * 60 * 60 * 1000L); + private final Timestamp yesterday = new Timestamp(System.currentTimeMillis() - DayDate.MILLI_DAY); - private final Timestamp tomorrow = new Timestamp(System.currentTimeMillis() + 24L * 60 * 60 * 1000L); + private final Timestamp tomorrow = new Timestamp(System.currentTimeMillis() + DayDate.MILLI_DAY); /** * at least 39 months ago, so is outside the window of * {@link User#VERIFICATION_MONTHS} */ - private final Timestamp min39month = new Timestamp(System.currentTimeMillis() - 24L * 60 * 60 * 39 * 31 * 1000L); + private final Timestamp min39month = new Timestamp(System.currentTimeMillis() - DayDate.MILLI_DAY * 39 * 31); /** * at least 24 months ago (but less than 39), so is inside the window of * {@link User#VERIFICATION_MONTHS} */ - private final Timestamp min24month = new Timestamp(System.currentTimeMillis() - 24L * 60 * 60 * 24 * 31 * 1000L); + private final Timestamp min24month = new Timestamp(System.currentTimeMillis() - DayDate.MILLI_DAY * 24 * 31); - private final int agentID; + private int agentID; - private final int applicantID; + private int agent2ID; + + private int applicantID; + + private int applicantMultID; public TestAssurance() throws GigiApiException { - agentID = createAssuranceUser("a", "b", createUniqueName() + "@example.com", TEST_PASSWORD); - applicantID = createVerifiedUser("a", "c", createUniqueName() + "@example.com", TEST_PASSWORD); + } // test for verification in 39 month period @@ -74,6 +80,18 @@ public class TestAssurance extends BusinessTest { } } + private void enterAssuranceWhen(int agentID, int applicantID, Timestamp when, int points) { + try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?, `when`=? ")) { + ps.setInt(1, agentID); + ps.setInt(2, applicantID); + ps.setInt(3, points); + ps.setString(4, "test-location"); + ps.setString(5, "2010-01-01"); + ps.setTimestamp(6, when); + ps.execute(); + } + } + private void enterAssuranceDeleted(int agentID, int applicantID, Timestamp deleted) { try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?, `deleted`=? ")) { ps.setInt(1, agentID); @@ -86,6 +104,14 @@ public class TestAssurance extends BusinessTest { } } + @Before + public void initTest() throws GigiApiException { + agentID = createAssuranceUser("a", "b", createUniqueName() + "@example.com", TEST_PASSWORD); + agent2ID = createAssuranceUser("a", "d", createUniqueName() + "@example.com", TEST_PASSWORD); + applicantID = createVerifiedUser("a", "c", createUniqueName() + "@example.com", TEST_PASSWORD); + applicantMultID = createVerifiedUser("a", "e", createUniqueName() + "@example.com", TEST_PASSWORD); + } + @Test public void testVerificationYesterday() throws IOException { enterAssuranceWhen(agentID, applicantID, yesterday); @@ -127,4 +153,84 @@ public class TestAssurance extends BusinessTest { enterAssuranceDeleted(agentID, applicantID, yesterday); assertFalse(User.isInVerificationLimit(applicantID)); } + + @Test + public void testMultipleAssurancePossible() throws IOException { + + User agent = User.getById(agentID); + User applicantMult = User.getById(applicantMultID); + + enterAssuranceWhen(agentID, applicantMultID, min39month); + + // test that new entry would be possible + try { + Notary.checkAssuranceIsPossible(agent, applicantMult); + } catch (GigiApiException e) { + assertTrue(false); + } + + // enter new entry + enterAssuranceWhen(agentID, applicantMultID, yesterday); + + // test that new entry is not possible + try { + Notary.checkAssuranceIsPossible(agent, applicantMult); + } catch (GigiApiException e) { + assertTrue(true); + } + + } + + @Test + public void testMultipleAssurancePointsCalculation() throws IOException { + + User agent = User.getById(agentID); + User applicantMult = User.getById(applicantMultID); + + enterAssuranceWhen(agentID, applicantMultID, min39month); + + int xPoints = agent.getExperiencePoints(); + + // test that VP after first entry + + assertEquals(applicantMult.getAssurancePoints(), 10); + + // enter second entry to check correct calculation with larger points + enterAssuranceWhen(agentID, applicantMultID, min24month, 20); + assertEquals(applicantMult.getAssurancePoints(), 20); + + // test correct XP calculation + assertEquals(agent.getExperiencePoints(), xPoints); + + // enter third entry to check correct calculation with less points + enterAssuranceWhen(agentID, applicantMultID, yesterday, 15); + assertEquals(applicantMult.getAssurancePoints(), 15); + + // test correct XP calculation + assertEquals(agent.getExperiencePoints(), xPoints); + + // enter expired entry + enterAssuranceExpired(agentID, applicantMultID, yesterday); + assertEquals(applicantMult.getAssurancePoints(), 15); + + // enter deleted entry same agent + enterAssuranceDeleted(agentID, applicantMultID, yesterday); + assertEquals(applicantMult.getAssurancePoints(), 15); + + // enter expired entry future + enterAssuranceExpired(agentID, applicantMultID, tomorrow); + assertEquals(applicantMult.getAssurancePoints(), 10); + + // test correct XP calculation + assertEquals(agent.getExperiencePoints(), xPoints); + + // enter entry from different agent + enterAssuranceWhen(agent2ID, applicantMultID, yesterday); + assertEquals(applicantMult.getAssurancePoints(), 20); + + // enter entry for second applicant + enterAssuranceWhen(agentID, applicantID, yesterday); + assertEquals(agent.getExperiencePoints(), xPoints + 2); + + } } diff --git a/tests/org/cacert/gigi/pages/wot/TestAssurance.java b/tests/org/cacert/gigi/pages/wot/TestAssurance.java index f104c67e..4108cf8f 100644 --- a/tests/org/cacert/gigi/pages/wot/TestAssurance.java +++ b/tests/org/cacert/gigi/pages/wot/TestAssurance.java @@ -10,14 +10,18 @@ import java.net.MalformedURLException; import java.net.URLConnection; import java.net.URLEncoder; import java.sql.SQLException; +import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.Date; import java.util.regex.Pattern; +import org.cacert.gigi.database.GigiPreparedStatement; +import org.cacert.gigi.dbObjects.User; import org.cacert.gigi.pages.account.MyDetails; import org.cacert.gigi.testUtils.IOUtils; import org.cacert.gigi.testUtils.ManagedTest; +import org.cacert.gigi.util.DayDate; import org.hamcrest.Matcher; import org.junit.Before; import org.junit.Test; @@ -250,4 +254,35 @@ public class TestAssurance extends ManagedTest { return uc; } + @Test + public void testMultipleAssurance() throws IOException { + + User users[] = User.findByEmail(assurerM); + int agentID = users[0].getId(); + + users = User.findByEmail(assureeM); + int applicantID = users[0].getId(); + + // enter first entry 200 days in the past + try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?, `when`=? ")) { + ps.setInt(1, agentID); + ps.setInt(2, applicantID); + ps.setInt(3, 10); + ps.setString(4, "test-location"); + ps.setString(5, "2010-01-01"); + ps.setTimestamp(6, new Timestamp(System.currentTimeMillis() - DayDate.MILLI_DAY * 200)); + ps.execute(); + } + + // enter second entry + String uniqueLoc = createUniqueName(); + executeSuccess("date=2000-01-01&location=" + uniqueLoc + "&certify=1&rules=1&assertion=1&points=10"); + + // enter third entry on the same day + URLConnection uc = get(cookie, AssurePage.PATH); + uc.setDoOutput(true); + uc.getOutputStream().write(("email=" + URLEncoder.encode(assureeM, "UTF-8") + "&day=1&month=1&year=1910&search").getBytes("UTF-8")); + assertThat(IOUtils.readURL(uc), hasError()); + + } } -- 2.39.2