EDIT

/**

* Copyright (C) 2007 EDIT

* European Distributed Institute of Taxonomy

* http://www.e-taxonomy.eu

*

* The contents of this file are subject to the Mozilla Public License Version 1.1

* See LICENSE.TXT at the top of this package for the full license terms.

*/

package eu.etaxonomy.cdm.io.berlinModel.in.validation;

import java.sql.ResultSet;

import java.sql.SQLException;

import org.apache.commons.lang.StringUtils;

import org.apache.log4j.Logger;

import eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportConfigurator;

import eu.etaxonomy.cdm.io.berlinModel.in.BerlinModelImportState;

import eu.etaxonomy.cdm.io.common.IOValidator;

import eu.etaxonomy.cdm.io.common.Source;

/**

 * @author a.mueller

 * @since 17.02.2010

 */

public class BerlinModelOccurrenceImportValidator implements IOValidator<BerlinModelImportState> {

	@SuppressWarnings("unused")

    private static final Logger logger = Logger.getLogger(BerlinModelOccurrenceImportValidator.class);

	@Override

	public boolean validate(BerlinModelImportState state) {

		boolean result = true;

		BerlinModelImportConfigurator config = state.getConfig();

		result &= checkTaxonIsAccepted(config);

		result &= checkSourcesWithWhitespace(config);

		return result;

	}

	//******************************** CHECK *************************************************

	private static boolean checkTaxonIsAccepted(BerlinModelImportConfigurator config){

		try {

			boolean result = true;

			Source source = config.getSource();

			String strQuery = "SELECT emOccurrence.OccurrenceId, PTaxon.StatusFk, Name.FullNameCache, "

			                + " Status.Status, PTaxon.PTRefFk, Reference.RefCache, emArea.EMCode, emOccurrence.* " +

						" FROM emOccurrence INNER JOIN " +

							" PTaxon ON emOccurrence.PTNameFk = PTaxon.PTNameFk AND emOccurrence.PTRefFk = PTaxon.PTRefFk INNER JOIN " +

			                " Name ON PTaxon.PTNameFk = Name.NameId INNER JOIN " +

			                " emArea ON emOccurrence.AreaFk = emArea.AreaId INNER JOIN " +

			                " Status ON PTaxon.StatusFk = Status.StatusId LEFT OUTER JOIN " +

			                " Reference ON PTaxon.PTRefFk = Reference.RefId " +

						" WHERE (PTaxon.StatusFk NOT IN ( 1, 5))  ";

			if (StringUtils.isNotBlank(config.getOccurrenceFilter())){

				strQuery += String.format(" AND (%s) ", config.getOccurrenceFilter()) ;

			}

			ResultSet resultSet = source.getResultSet(strQuery);

			boolean firstRow = true;

			while (resultSet.next()){

				if (firstRow){

					System.out.println("========================================================");

					System.out.println("There are Occurrences for a taxon that is not accepted!");

					System.out.println("--------------------------------------------------------");

					System.out.println(strQuery);

					System.out.println("========================================================");

				}

				int occurrenceId = resultSet.getInt("OccurrenceId");

//			    int statusFk = resulSet.getInt("StatusFk");

				String status = resultSet.getString("Status");

				String fullNameCache = resultSet.getString("FullNameCache");

				String ptRefFk = resultSet.getString("PTRefFk");

				String ptRef = resultSet.getString("RefCache");

				String area = resultSet.getString("EMCode");

				System.out.println("OccurrenceId:" + occurrenceId + "\n  Status: " + status +

						"\n  FullNameCache: " + fullNameCache +  "\n  ptRefFk: " + ptRefFk +

						"\n  sec: " + ptRef + "\n  area: " + area) ;

				result = firstRow = false;

			}

			return result;

		} catch (SQLException e) {

			e.printStackTrace();

			return false;

		}

	}

    private static boolean checkSourcesWithWhitespace(BerlinModelImportConfigurator config){

        try {

            boolean result = true;

            Source source = config.getSource();

            String strSelect = "SELECT OccurrenceId, PTNameFk, PTRefFk, AreaFk, Sources, Created_When, Created_Who, Updated_When, Updated_Who, Notes, Occurrence ";

            String strCount = " SELECT count(*) ";

            String strQueryBase =

                    " FROM emOccurrence " +

                    " WHERE (Sources LIKE '%  %') OR (Sources LIKE '% ') OR (Sources LIKE ' %') ";

            ResultSet rs = source.getResultSet(strCount + strQueryBase);

            rs.next();

            int n = rs.getInt("n");

            if (n > 0){

                System.out.println("=======================================================================");

                System.out.println("There are "+n+" occurrences with source attribute has unexpected whitespace!");

                System.out.println("---------------------------------------------------------------");

                System.out.println(strSelect + strQueryBase);

                System.out.println("=======================================================================");

            }

            rs = source.getResultSet(strSelect + strQueryBase);

            while (rs.next()){

                int occurrenceId = rs.getInt("OccurrenceId");

                String sources = rs.getString("Sources");

                System.out.println("OccurrenceSourceId:" + occurrenceId +

                        "\n  Sources: " + sources)

                        ;

            }

            return result;

        } catch (SQLException e) {

            e.printStackTrace();

            return false;

        }

    }

}