commit 9ec5ede262ae523a0ba4ce44900b1a2f15d4b220
Author: Andreas Müller <a.mueller@bgbm.org>
Date:   Wed Sep 11 13:38:06 2019 +0200

    ref #8508 latest changes to ERMS pipeline validator

diff --git a/cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/erms/validation/PesiErmsValidator.java b/cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/erms/validation/PesiErmsValidator.java
index 1ccaba56..73c56971 100644
--- a/cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/erms/validation/PesiErmsValidator.java
+++ b/cdm-pesi/src/main/java/eu/etaxonomy/cdm/io/pesi/erms/validation/PesiErmsValidator.java
@@ -31,7 +31,7 @@ public class PesiErmsValidator {
     private static final Logger logger = Logger.getLogger(PesiErmsValidator.class);
 
     private static final Source defaultSource = PesiSources.PESI2019_ERMS();
-    private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_ERMS2PESI_2();
+    private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_ERMS2PESI();
 
     private Source source = defaultSource;
     private Source destination = defaultDestination;
@@ -44,6 +44,7 @@ public class PesiErmsValidator {
             success &= testReferences();
             success &= testTaxa();
             success &= testTaxonRelations();
+            success &= testCommonNames();
         } catch (Exception e) {
             e.printStackTrace();
             success = false;
@@ -52,6 +53,14 @@ public class PesiErmsValidator {
         System.out.println("end validation " + (success? "":"NOT ") + "successful.");
     }
 
+    private boolean testCommonNames() throws SQLException {
+        boolean success = testCommonNameCount();
+        if (success){
+            success &= testSingleCommonNames(source.getUniqueInteger("SELECT count(*) FROM vernaculars "));
+        }
+        return success;
+    }
+
     private boolean testTaxonRelations() {
         boolean success = true;
         return success;
@@ -73,10 +82,16 @@ public class PesiErmsValidator {
         return success;
     }
 
+    private boolean testCommonNameCount() {
+        int countSrc = source.getUniqueInteger("SELECT count(*) FROM vernaculars ");
+        int countDest = destination.getUniqueInteger("SELECT count(*) FROM CommonName ");
+        return equals("CommonName count ", countSrc, countDest, String.valueOf(-1));
+    }
+
     private boolean testTaxaCount() {
          int countSrc = source.getUniqueInteger("SELECT count(*) FROM tu ");
          int countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon ");
-         return equals("Taxon count ", countSrc, countDest, -1);
+         return equals("Taxon count ", countSrc, countDest, String.valueOf(-1));
      }
 
     private boolean testSingleTaxa(int n) throws SQLException {
@@ -95,7 +110,8 @@ public class PesiErmsValidator {
         ResultSet destRS = destination.getResultSet("SELECT t.*, type.IdInSource typeSourceId "
                 + " FROM Taxon t "
                 + "    LEFT JOIN Taxon type ON type.TaxonId = t.TypeNameFk"
-                + " WHERE t.OriginalDB = 'erms' ORDER BY t.IdInSource");
+                + " WHERE t.OriginalDB = 'erms' "
+                + " ORDER BY t.IdInSource");
         ResultSet srcRsLastAction = source.getResultSet(""
                 + " SELECT t.id, s.sessiondate, a.action_name "
                 + " FROM tu t "
@@ -106,16 +122,16 @@ public class PesiErmsValidator {
         int i = 0;
         while (srcRS.next() && destRS.next()){
             success &= testSingleTaxon(srcRS, destRS);
-            success &= testTaxonLastAction(srcRsLastAction, destRS, srcRS.getInt("id"));
+            success &= testTaxonLastAction(srcRsLastAction, destRS, String.valueOf(srcRS.getInt("id")));
             i++;
         }
-        success &= equals("Taxon count for single compare", n, i, -1);
+        success &= equals("Taxon count for single compare", n, i, String.valueOf(-1));
         return success;
     }
 
 
     private boolean testSingleTaxon(ResultSet srcRS, ResultSet destRS) throws SQLException {
-        int id = srcRS.getInt("id");
+        String id = String.valueOf(srcRS.getInt("id"));
         boolean success = equals("Taxon ID", "tu_id: " + srcRS.getInt("id"), destRS.getString("IdInSource"), id);
         //TODO SourceFk
 //      success &= compareKingdom("Taxon kingdom", srcRS, destRS, id);
@@ -137,40 +153,32 @@ public class PesiErmsValidator {
         Integer orgigTypeNameFk = nullSafeInt(srcRS, "tu_typetaxon");
         success &= equals("Taxon TypeNameFk", orgigTypeNameFk == null? null : "tu_id: " + orgigTypeNameFk, destRS.getString("typeSourceId"), id);
 //TODO  success &= equals("Taxon TypeFullNameCache", CdmUtils.concat(" ", srcRS.getString("typename"), srcRS.getString("typeauthor")), destRS.getString("TypeFullNameCache"), id);
-        success &= equals("Taxon QualityStatusFK", nullSafeInt(srcRS, "tu_qualitystatus"),nullSafeInt( destRS,"QualityStatusFk"), id);
+        success &= equals("Taxon QualityStatusFK", nullSafeInt(srcRS, "tu_qualitystatus"),nullSafeInt( destRS,"QualityStatusFk"), String.valueOf(id));
         success &= equals("Taxon QualityStatusCache", srcRS.getString("qualitystatus_name"), destRS.getString("QualityStatusCache"), id);
         //TODO TreeIndex
-        success &= equals("Taxon FossilStatusFk", nullSafeInt(srcRS, "tu_fossil"),nullSafeInt( destRS,"FossilStatusFk"), id);
+        success &= equals("Taxon FossilStatusFk", nullSafeInt(srcRS, "tu_fossil"),nullSafeInt( destRS,"FossilStatusFk"), String.valueOf(id));
         success &= equals("Taxon FossilStatusCache", srcRS.getString("fossil_name"), destRS.getString("FossilStatusCache"), id);
         success &= equals("Taxon GUID", srcRS.getString("GUID"), destRS.getString("GUID"), id);
         //in 2014 GUID and DerivedFromGuid was always same for ERMS
         success &= equals("Taxon DerivedFromGuid", srcRS.getString("GUID"), destRS.getString("DerivedFromGuid"), id);
-        //TODO DerivedFromGuid
         //TODO ExpertGUID
         //TODO ExpertName
         //TODO SpeciesExpertGUID
         //TODO SpeciesExpertName
         //TODO CacheCitation
         //LastAction(Date) handled in separate method
-        //TODO GUID2
-        //TODO DerivedFromGuid2
+        success &= isNull("GUID2", destRS);  //only relevant after merge
+        success &= isNull("DerivedFromGuid2", destRS);  //only relevant after merge
         return success;
     }
 
-
-
-    /**
-     * @param srcRS
-     * @return
-     * @throws SQLException
-     */
     private String srcFullName(ResultSet srcRs) throws SQLException {
         String result = null;
         String epi = srcRs.getString("tu_name");
         epi = " a" + epi;
         String display = srcRs.getString("tu_displayname");
         String sp = srcRs.getString("tu_sp");
-        if (display.indexOf(epi) != display.lastIndexOf(epi) && !sp.startsWith("#1#")){ //homonym, animal
+        if (display.indexOf(epi) != display.lastIndexOf(epi) && !sp.startsWith("#2#")){ //homonym, animal
             result = srcRs.getString("tu_displayname").replaceFirst(epi+" ", CdmUtils.concat(" ", " "+epi, srcRs.getString("tu_authority")))+" ";
         }else{
             result = CdmUtils.concat(" ", srcRs.getString("tu_displayname"), srcRs.getString("tu_authority"));
@@ -178,11 +186,11 @@ public class PesiErmsValidator {
         return result;
     }
 
-    private boolean testTaxonLastAction(ResultSet srcRs, ResultSet destRs, int id) throws SQLException {
+    private boolean testTaxonLastAction(ResultSet srcRs, ResultSet destRs, String id) throws SQLException {
         boolean success = true;
         while (srcRs.next()){
             int srcId = srcRs.getInt("id");
-            if (id == srcId){
+            if (id.equals(String.valueOf(srcId))){
                 break;
             }
         }
@@ -192,7 +200,7 @@ public class PesiErmsValidator {
         return success;
     }
 
-    private boolean compareKingdom(String messageStart, ResultSet srcRS, ResultSet destRS, int id) throws SQLException {
+    private boolean compareKingdom(String messageStart, ResultSet srcRS, ResultSet destRS, String id) throws SQLException {
         String strSrc = srcRS.getString("acc_sp");
         if (strSrc == null){
             strSrc = srcRS.getString("tu_sp");
@@ -212,6 +220,37 @@ public class PesiErmsValidator {
         }
     }
 
+    private boolean testSingleCommonNames(int n) throws SQLException {
+        boolean success = true;
+        ResultSet srcRs = source.getResultSet("SELECT v.*, l.LanName, tu.id tuId "
+                + " FROM vernaculars v LEFT JOIN tu ON v.tu_id = tu.id LEFT JOIN languages l ON l.LanID = v.lan_id "
+                + " ORDER BY CAST(tu.id as nvarchar(20)), tu.id ");
+        ResultSet destRs = destination.getResultSet("SELECT cn.*, t.IdInSource, l.ISO639_2 "
+                + " FROM CommonName cn INNER JOIN Taxon t ON t.TaxonId = cn.TaxonFk INNER JOIN Language l ON l.LanguageId = cn.LanguageFk "
+                + " WHERE t.OriginalDB = 'erms' "
+                + " ORDER BY t.IdInSource, l.ISO639_2");
+        int count = 0;
+        while (srcRs.next() && destRs.next()){
+            success &= testSingleCommonName(srcRs, destRs);
+            count++;
+        }
+        success &= equals("Common name count differs", n, count, "-1");
+        return success;
+    }
+
+    private boolean testSingleCommonName(ResultSet srcRs, ResultSet destRs) throws SQLException {
+        String id = String.valueOf(srcRs.getInt("tuId") + "-" + srcRs.getString("lan_id"));
+        boolean success = equals("Common name taxonID ", "tu_id: " + String.valueOf(srcRs.getInt("tuId")), destRs.getString("IdInSource"), id);
+        success &= equals("Common name languageID ", srcRs.getString("lan_id"), destRs.getString("ISO639_2"), id);
+        success &= equals("CommonName name ", srcRs.getString("vername"), destRs.getString("CommonName"), id);
+        //TODO success = equals("CommonName language code ", srcRs.getString("lan_id"), destRs.getString("LanguageFk"), id);
+        success = equals("CommonName LanguageCache ", srcRs.getString("LanName"), destRs.getString("LanguageCache"), id);
+        success &= isNull("Region", destRs);  //region does not seem to exist in ERMS
+
+        //TODO
+        return success;
+    }
+
     private boolean testSingleReferences() throws SQLException {
         boolean success = true;
         ResultSet srcRS = source.getResultSet("SELECT s.* FROM sources s ORDER BY s.id ");
@@ -224,7 +263,7 @@ public class PesiErmsValidator {
     }
 
     private boolean testSingleReference(ResultSet srcRS, ResultSet destRS) throws SQLException {
-        int id = srcRS.getInt("id");
+        String id = String.valueOf(srcRS.getInt("id"));
         boolean success = equals("Reference ID ", srcRS.getInt("id"), destRS.getInt("RefIdInSource"), id);
         success &= equals("Reference IMIS_id ", srcRS.getString("imis_id"), destRS.getString("IMIS_Id"), id);
 //TODO        success &= equals("Reference SourceCategoryFk ", srcRS.getString("source_type"), destRS.getInt("SourceCategoryFk"), id);
@@ -232,7 +271,7 @@ public class PesiErmsValidator {
         success &= equals("Reference name ", srcRS.getString("source_name"), destRS.getString("Name"), id);
         success &= equals("Reference abstract ", srcRS.getString("source_abstract"), destRS.getString("Abstract"), id);
         success &= equals("Reference title ", srcRS.getString("source_title"), destRS.getString("Title"), id);
-//TODO     success &= equals("Reference author string ", srcRS.getString("source_author"), destRS.getString("AuthorString"), id);
+        success &= equals("Reference author string ", srcRS.getString("source_author"), destRS.getString("AuthorString"), id);
         success &= equals("Reference year ", normalizeYear(srcRS.getString("source_year")), destRS.getString("RefYear"), id);
         success &= isNull("NomRefCache", destRS);  //for ERMS no other value was found in 2014 value
         success &= equals("Reference link ", srcRS.getString("source_link"), destRS.getString("Link"), id);
@@ -245,7 +284,7 @@ public class PesiErmsValidator {
     private boolean testReferenceCount() {
         int countSrc = source.getUniqueInteger("SELECT count(*) FROM sources ");
         int countDest = destination.getUniqueInteger("SELECT count(*) FROM Source s WHERE s.OriginalDB = 'erms'");  // +1 for the source reference "erms" but this has no OriginalDB
-        boolean success = equals("Reference count ", countSrc, countDest, -1);
+        boolean success = equals("Reference count ", countSrc, countDest, "-1");
         return success;
     }
 
@@ -272,7 +311,7 @@ public class PesiErmsValidator {
         }
     }
 
-    private boolean equals(String messageStart, Timestamp srcDate, Timestamp destDate, int id) {
+    private boolean equals(String messageStart, Timestamp srcDate, Timestamp destDate, String id) {
         if (!CdmUtils.nullSafeEqual(srcDate, destDate)){
             String message = id + ": " + messageStart + " must be equal, but was not.\n Source: "+  srcDate + "; Destination: " + destDate;
             logger.warn(message);
@@ -283,8 +322,8 @@ public class PesiErmsValidator {
         }
     }
 
-    private boolean equals(String messageStart, Integer nSrc, Integer nDest, int id) {
-        String strId = id == -1? "": (id+ ": ");
+    private boolean equals(String messageStart, Integer nSrc, Integer nDest, String id) {
+        String strId = id.equals("-1")? "": (id+ ": ");
         if (!CdmUtils.nullSafeEqual(nSrc,nDest)){
             String message = strId+ messageStart + " must be equal, but was not.\n Source: "+  nSrc + "; Destination: " + nDest;
             logger.warn(message);
@@ -295,7 +334,7 @@ public class PesiErmsValidator {
         }
     }
 
-    private boolean equals(String messageStart, String strSrc, String strDest, int id) {
+    private boolean equals(String messageStart, String strSrc, String strDest, String id) {
         if (StringUtils.isBlank(strSrc)){
             strSrc = null;
         }else{
