EDIT

import java.time.LocalDate;

import java.util.UUID;

import eu.etaxonomy.cdm.io.berlinModel.BerlinModelTransformer;

import eu.etaxonomy.cdm.model.description.PresenceAbsenceTerm;

//    private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_EM2PESI();

    private static final Source defaultDestination = PesiDestinations.pesi_test_local_CDM_EM2PESI_2();

//            success &= testReferences();

              success &= testTaxa();

//              success &= testDistributions();

        if (!success){

              success &= testSingleDistributions(source.getUniqueInteger(distributionCountSQL));

        if (success){

    private String distributionCountWhere = " WHERE deb.DTYPE = 'Distribution' AND tb.publish = 1 AND a.uuid NOT IN ("

            + "'111bdf38-7a32-440a-9808-8af1c9e54b51',"   //E+M

            //Former UUSR

            + "'c4a898ce-0f32-44fe-a8a3-278e11a4ba53','a575d608-dd53-4c01-b2af-5067d0711f64','da4e9cc3-b1cc-403a-81ff-bcc5d9fadbd1',"

            + "'7e0f8fa3-5db9-48f0-9fa8-87fcab3eaa53','2188e3a5-0446-47c8-b11b-b4b2b9a71c75','44f262e3-5091-4d28-8081-440d3978fb0b',"

            + "'efabc8fd-0b3c-475b-b532-e1ca0ba0bdbb') ";

    private String distributionCountSQL = "SELECT count(*) as n "

            + " FROM DescriptionElementBase deb INNER JOIN DescriptionBase db ON deb.inDescription_id = db.id "

            + "    LEFT JOIN TaxonBase tb ON db.taxon_id = tb.id "

            + "    LEFT JOIN DefinedTermBase a ON a.id = deb.area_id "

            + distributionCountWhere;

        int countSrc = source.getUniqueInteger(distributionCountSQL);

    private final String countTaxon = "SELECT count(*) FROM TaxonBase tb WHERE tb.publish = 1 ";

         int countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon t WHERE t.SourceFk IS NOT NULL OR t.AuthorString = 'auct.' ");

//         //NomStatus

//         countSrc = source.getUniqueInteger("SELECT count(*) FROM tu WHERE ("

//               + " tu_unacceptreason like '%inval%' OR  tu_unacceptreason like '%not val%' "

//               + " OR tu_unacceptreason like '%illeg%' OR tu_unacceptreason like '%nud%' "

//               + " OR tu_unacceptreason like '%rej.%' OR tu_unacceptreason like '%superfl%' "

//               + " OR tu_unacceptreason like '%Comb. nov%' OR tu_unacceptreason like '%New name%' "

//               + " OR tu_unacceptreason = 'new combination'  "

//               + " OR tu_status IN (3,5,6,7,8) )");

//         countDest = destination.getUniqueInteger("SELECT count(*) FROM Taxon WHERE NameStatusFk IS NOT NULL ");

//         result = equals("Taxon name status count ", countSrc, countDest, String.valueOf(-1));

        ResultSet srcRS = source.getResultSet("SELECT CAST(tn.id as char(20)) tid, tb.uuid as GUID, tn.rank_id, rank.titleCache rank_name, "

                + "      sec.titleCache secTitle,"

                + "      tn.genusOrUninomial, tn.infraGenericEpithet, tn.specificEpithet, tn.infraSpecificEpithet, "

                + "      tn.nameCache, tn.authorshipCache, tn.titleCache nameTitleCache, "

                + "      tb.DTYPE taxStatus, nsType.id nsId, nsType.idInVocabulary nsTitle, "

                + "      CASE WHEN tb.updated IS NOT NULL THEN tb.updated ELSE tb.created END as lastActionDate, "

                + "      CASE WHEN tb.updated IS NOT NULL THEN 'changed' ELSE 'created' END as lastAction "

                + " FROM TaxonBase tb "

                + "     LEFT JOIN TaxonName tn on tb.name_id = tn.id "

                + "     LEFT JOIN DefinedTermBase rank ON rank.id = tn.rank_id "

                + "     LEFT JOIN Reference sec ON sec.id = tb.sec_id "

                + "     LEFT JOIN TaxonName_NomenclaturalStatus nsMN ON tn.id = nsMN.TaxonName_id "

                + "     LEFT JOIN NomenclaturalStatus ns ON ns.id = nsMN.status_id "

                + "     LEFT JOIN DefinedTermBase nsType ON nsType.id = ns.type_id "

                + " WHERE tb.publish = 1 "

                + " GROUP BY tid, GUID, tn.rank_id, rank.titleCache, secTitle,"

                + "      tn.genusOrUninomial, tn.infraGenericEpithet, tn.specificEpithet, tn.infraSpecificEpithet, "

                + "      tn.nameCache, tn.authorshipCache, tn.titleCache, "

                + "      tb.DTYPE, tb.updated, tb.created "    //for duplicates caused by >1 name status

                + " ORDER BY tid, GUID, lastActionDate ");

                + " WHERE t."+ origEuroMed + " AND (t.SourceFk IS NOT NULL  OR t.AuthorString = 'auct.') "   //FIXME remove SourceFk filter is only preliminary for first check

                + " ORDER BY t.IdInSource, t.GUID, t.LastActionDate, AuthorString ");

        logger.error("remove SourceFk filter is only preliminary for first check");

        String id = String.valueOf(srcRS.getInt("tid"));

        //TODO decide, according to SQL it also contains the taxon UUID, but in PESI2014 backup I can't find this

        boolean success = equals("Taxon ID", "NameId: " + srcRS.getInt("tid"), destRS.getString("IdInSource"), id);

        success &= equals("Taxon source", srcRS.getString("secTitle"), destRS.getString("sourceName"), id);

        success &= equals("Taxon kingdomFk", "3", destRS.getString("KingdomFk"), id);

//difficult to test        success &= equals("Taxon rank fk", srcRS.getString("rank_id"), destRS.getString("RankFk"), id);

        success &= equals("Taxon genusOrUninomial", srcRS.getString("genusOrUninomial"), destRS.getString("GenusOrUninomial"), id) ;

        success &= equals("Taxon infraGenericEpithet", srcRS.getString("infraGenericEpithet"), destRS.getString("InfraGenericEpithet"), id) ;

        success &= equals("Taxon specificEpithet", srcRS.getString("specificEpithet"), destRS.getString("SpecificEpithet"), id) ;

        success &= equals("Taxon infraSpecificEpithet", srcRS.getString("infraSpecificEpithet"), destRS.getString("InfraSpecificEpithet"), id) ;

        success &= equals("Taxon websearchname", srcRS.getString("nameCache"), destRS.getString("WebSearchName"), id);

//TODO        success &= equals("Taxon WebShowName", srcRS.getString("tu_displayname"), destRS.getString("WebShowName"), id);

//FIXME sensu+auct. autoren       success &= equals("Taxon authority", srcRS.getString("authorshipCache"), destRS.getString("AuthorString"), id);

//FIXME sensu+auct. autoren        success &= equals("Taxon FullName", srcRS.getString("nameTitleCache"), destRS.getString("FullName"), id);

//TODO        success &= isNull("NomRefString", destRS);

//TODO        success &= equals("Taxon DisplayName", srcDisplayName(srcRS), destRS.getString("DisplayName"), id);  //in ERMS according to SQL script same as FullName, no nom.ref. information attached

//TODO        success &= equals("Taxon NameStatusFk", nullSafeInt(srcRS, "nsId"),nullSafeInt( destRS,"NameStatusFk"), id);

        success &= equals("Taxon NameStatusCache", srcRS.getString("nsTitle"), destRS.getString("NameStatusCache"), id);

//        success &= equals("Taxon TaxonStatusFk", mapTaxStatusFk(srcRS.getString("taxStatus")), nullSafeInt( destRS,"TaxonStatusFk"), id);

//        success &= equals("Taxon TaxonStatusCache", mapTaxStatus(srcRS.getString("taxStatus")), destRS.getString("TaxonStatusCache"), id);

//        //TODO ParentTaxonFk

//        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);

          //quality status, according to SQL always constant, could be changed in future

        success &= equals("Taxon QualityStatusFK", 2, nullSafeInt( destRS,"QualityStatusFk"), String.valueOf(id));

        success &= equals("Taxon QualityStatusCache", "Added by Database Management Team", destRS.getString("QualityStatusCache"), id);

//        //TODO TreeIndex

          success &= isNull("FossilStatusFk", destRS);

          success &= isNull("FossilStatusCache", destRS);

        success &= isNull("ExpertGUID", destRS);  //according to SQL + PESI2014

//        success &= isNull("ExpertName", destRS);

//        success &= isNull("SpeciesExpertGUID", destRS);

//      success &= isNull("SpeciesExpertName", destRS);  //only relevant after merge

//FIXME !!        success &= equals("Taxon cache citation", srcRS.getString("secTitle"), destRS.getString("CacheCitation"), id);

        success &= equals("Taxon Last Action", srcRS.getString("lastAction"),  destRS.getString("LastAction"), id);

        success &= equals("Taxon Last Action Date", srcRS.getTimestamp("lastActionDate"),  destRS.getTimestamp("LastActionDate"), id);

    private String mapTaxStatus(String string) {

        if (string == null){

            return null;

        }else if ("Synonym".equals(string)){

            return "synonym";

        }else if ("Taxon".equals(string)){

            return "accepted";

        return null;

    private Integer mapTaxStatusFk(String string) {

        if (string == null){

            return null;

        }else if ("Synonym".equals(string)){

            return PesiTransformer.T_STATUS_SYNONYM;

        }else if ("Taxon".equals(string)){

            return PesiTransformer.T_STATUS_ACCEPTED;

        }

        return null;

    private String normalizeRank(String rankStr, ResultSet srcRS, String id) throws SQLException {

        if (rankStr == null){return null;

        }else if (rankStr.equals("Convar")){return "Convariety";

        }else if (rankStr.equals("Unranked (infrageneric)")){return "Tax. infragen.";

        }else if (rankStr.equals("Unranked (infraspecific)")){return "Tax. infraspec.";

        }else if (rankStr.equals("Coll. species")){return "Coll. Species";

        }else if (rankStr.equals("Species Aggregate")){return "Aggregate";

        }else if (rankStr.equals("Subsection bot.")){return "Subsection";

        }return rankStr;

                + " ORDER BY CAST(t.id as char(20)) ");

        ResultSet srcRs = source.getResultSet("SELECT CAST(tu.id as char(20)) tuId, MN.*, s.*, su.sourceuse_name "

                + " ORDER BY CAST(tu.id as char(20)), MN.sourceuse_id, s.id ");  //, no.note (not possible because ntext

        ResultSet srcRs = source.getResultSet("SELECT CAST(tu.id as char(20)) tuId, no.*, l.LanName "

                + " ORDER BY CAST(tu.id as char(20)), no.type, no.noteSortable ");  //, no.note (not possible because ntext

                + " ORDER BY CAST(tu.id as char(20)), no.type, no.noteSortable, s.sessiondate DESC, a.id DESC ");

        ResultSet srcRs = source.getResultSet(

                  " SELECT CAST(tb.name_id as char(20)) AS tid, a.idInVocabulary, a.titleCache area, st.uuid statusUuid, "

                + "        CASE WHEN deb.updated IS NOT NULL THEN deb.updated ELSE deb.created END as lastActionDate, "

                + "        CASE WHEN deb.updated IS NOT NULL THEN 'changed' ELSE 'created' END as lastAction "

                + " FROM DescriptionElementBase deb INNER JOIN DescriptionBase db ON deb.inDescription_id = db.id "

                + "    LEFT JOIN TaxonBase tb ON db.taxon_id = tb.id "

                + "    LEFT JOIN DefinedTermBase a ON a.id = deb.area_id "

                + "    LEFT JOIN DefinedTermBase st ON st.id = deb.status_id "

                + distributionCountWhere

                + " ORDER BY CAST(tb.name_id as char(20)), a.idInVocabulary, a.titleCache ");

        ResultSet destRs = destination.getResultSet("SELECT t.IdInSource, a.AreaEmCode, oc.*, a.AreaName "

                + " ORDER BY t.IdInSource, a.AreaEmCode, a.AreaName, oc.Notes ");

        String id = String.valueOf(srcRs.getInt("tid") + "-" + srcRs.getString("area"));

        boolean success = equals("Distribution taxonID ", "NameId: " + String.valueOf(srcRs.getInt("tid")), destRs.getString("IdInSource"), id);

        success &= equals("Distribution AreaEmCode ", srcRs.getString("idInVocabulary"), destRs.getString("AreaEmCode"), id);

//        success &= equals("Distribution area name ", normalizeDistrArea(srcRs.getString("area")), destRs.getString("AreaName"), id);

        success &= equals("Distribution area name cache", normalizeDistrArea(srcRs.getString("area")), destRs.getString("AreaNameCache"), id);

        success &= equals("Distribution OccurrenceStatusFk", mapStatus(srcRs.getString("statusUuid")), destRs.getInt("OccurrenceStatusFk"), id);

//TODO        success &= equals("Distribution OccurrenceStatusCache", "Present", destRs.getString("OccurrenceStatusCache"), id);

        success &= isNull("SourceFk", destRs);  //sources should be moved to extra table only, according to script there were values, but in PESI 2014 values existed only in OccurrenceSource table (for all only E+M records)

        success &= isNull("SourceCache", destRs);  //sources should be moved to extra table, see above

//TODO        success &= equals("Distribution notes ", srcRs.getString("note"), destRs.getString("Notes"), id);

        success &= isNull("SpeciesExpertGUID", destRs);  //SpeciesExpertGUID does not exist in EM and according to script

        success &= isNull("SpeciesExpertName", destRs);  //SpeciesExpertName does not exist in EM and according to script

        success &= equals("Distribution Last Action", srcRs.getString("lastAction"),  destRs.getString("LastAction"), id);

        success &= equals("Distribution Last Action Date", srcRs.getTimestamp("lastActionDate"),  destRs.getTimestamp("LastActionDate"), id);

    /**

     * @param string

     * @return

     */

    private Integer mapStatus(String uuidStr) {

        UUID uuid = UUID.fromString(uuidStr);

        if (uuid.equals(PresenceAbsenceTerm.uuidNativeError) ){  //native, reported in error

            return PesiTransformer.STATUS_ABSENT;

        }else if (uuid.equals(PresenceAbsenceTerm.uuidIntroducesAdventitious)  //casual, introduced adventitious

                || uuid.equals(PresenceAbsenceTerm.uuidIntroducedUncertainDegreeNaturalisation)//introduced: uncertain degree of naturalisation

                || uuid.equals(PresenceAbsenceTerm.uuidIntroduced)){

            return PesiTransformer.STATUS_INTRODUCED;

        }else if (uuid.equals(PresenceAbsenceTerm.uuidNative) ){  //native

            return PesiTransformer.STATUS_NATIVE;

        }else if (uuid.equals(PresenceAbsenceTerm.uuidNaturalised) ){  //naturalised

            return PesiTransformer.STATUS_NATURALISED;

        }else if (uuid.equals(PresenceAbsenceTerm.uuidNativePresenceQuestionable) ){  //native, presence questionable

            return PesiTransformer.STATUS_DOUBTFUL;

        }else if (uuid.equals(PresenceAbsenceTerm.uuidCultivated) ){  //cultivated

            return PesiTransformer.STATUS_MANAGED;

        }else if (uuid.equals(BerlinModelTransformer.uuidStatusUndefined) ){  //native, reported in error

            return -1;

        }

        return null;

    }

    private String normalizeDistrArea(String area) {

        if (area == null){

            return null;

        }else if ("France".equals(area)){return "French mainland";

        }else if ("France, with Channel Islands and Monaco".equals(area)){return "France";

        }else if ("Greece".equals(area)){return "Greece with Cyclades and more islands";

        }else if ("Spain, with Gibraltar and Andorra (without Bl and Ca)".equals(area)){return "Spain";

        }else if ("Italy, with San Marino and Vatican City (without Sa and Si(S))".equals(area)){return "Italy";

        }else if ("Morocco, with Spanish territories".equals(area)){return "Morocco";

        }else if ("Serbia including Kosovo and Vojvodina".equals(area)){return "Serbia including Vojvodina and with Kosovo";

        }else if ("Caucasia (Ab + Ar + Gg + Rf(CS))".equals(area)){return "Caucasus region";

        }else if ("Georgia, with Abchasia and Adzharia".equals(area)){return "Georgia";

        }else if ("Canary Is.".equals(area)){return "Canary Islands";

        }else if ("Kriti with Karpathos, Kasos & Gavdhos".equals(area)){return "Crete with Karpathos, Kasos & Gavdhos";

        }else if ("Ireland, with N Ireland".equals(area)){return "Ireland";

        }else if ("mainland Spain".equals(area)){return "Kingdom of Spain";

        }else if ("Portugal".equals(area)){return "Portuguese mainland";

        }else if ("Svalbard".equals(area)){return "Svalbard with Björnöya and Jan Mayen";

        }else if ("Norway".equals(area)){return "Norwegian mainland";

        }else if ("Ukraine".equals(area)){return "Ukraine including Crimea";

        }else if ("Turkey-in-Europe".equals(area)){return "European Turkey";

        }else if ("Azerbaijan".equals(area)){return "Azerbaijan including Nakhichevan";

        }else if ("Ireland".equals(area)){return "Republic of Ireland";

        }else if ("France".equals(area)){return "French mainland";

        }

        return area;

    }

                + " ORDER BY CAST(tu.id as char(20)), ISNULL([639_3],[639_2]), v.vername, v.id ");

                + " ORDER BY CAST(tu.id as char(20)), ISNULL([639_3],[639_2]), v.vername, v.id, s.sessiondate DESC, a.id DESC ");

            LocalDate date1 = srcDate.toLocalDateTime().toLocalDate();

            LocalDate date2 = destDate.toLocalDateTime().toLocalDate();

            if (date1.equals(date2) || date1.plusDays(1).equals(date2)){

                logger.info(messageStart + " were (almost) equal: " + srcDate);

                return true;

            }else{

                String message = id + ": " + messageStart + " must be equal, but was not.\n Source: "+  srcDate + "; Destination: " + destDate;

                logger.warn(message);

                return false;

            }

	public static int T_STATUS_ACCEPTED = 1;

	public static int T_STATUS_SYNONYM = 2;

	public static int STATUS_ABSENT = 2;

	public static int STATUS_NATIVE = 3;

	public static int STATUS_INTRODUCED = 4;

	public static int STATUS_NATURALISED = 5;

	public static int STATUS_MANAGED = 7;

	public static int STATUS_DOUBTFUL = 8;