EDIT

import java.util.Collection;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import java.util.regex.Matcher;

import org.apache.commons.lang.StringUtils;

import com.vaadin.data.util.sqlcontainer.query.FreeformQuery;

import eu.etaxonomy.cdm.common.CdmUtils;

import eu.etaxonomy.cdm.model.common.Language;

import eu.etaxonomy.cdm.model.common.Representation;

import eu.etaxonomy.cdm.model.location.NamedArea;

import eu.etaxonomy.cdm.vaadin.statement.CdmStatementDelegate;

	public static final String DTYPE_COLUMN = "DTYPE";

	public static final String ID_COLUMN = "ID";

    public static QueryDelegate generateTaxonTreeQuery(String name_id, String classificationId)  {

        String FROM_QUERY = " FROM TaxonBase tb " +

                "INNER JOIN TaxonName n on tb.name_id=n.id " +

                "INNER JOIN Classification cl on cl.id=tn.classification_id and cl.id='"+classificationId+"'";

                ", n.titleCache as " + name_id +

                ", tn.parent_id as parent" +

    public static QueryDelegate generateTaxonBaseQuery(String name_id,

            String pb_id,

            String has_syn_id)  {

        String FROM_QUERY = " FROM TaxonBase tb " +

                "INNER JOIN TaxonName n on tb.name_id=n.id " +

        String SELECT_QUERY="SELECT tb.id as " + ID +

                ", n.titleCache as " + name_id +

                ", tb.publish as " + pb_id +

                ", tn.unplaced as " + unp_id +

                ", dtb.titleCache as " + rank_id +

                ", (SELECT COUNT(*) FROM TaxonBase syn WHERE tb.id = syn.acceptedTaxon_id) as " + has_syn_id +

                FROM_QUERY;

        return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);

    }

    public static QueryDelegate generateTaxonDistributionQuery(List<Integer> taxonNodeIds, Collection<NamedArea> namedAreas) {

    	String idString = "";

			Integer integer = nodeIterator.next();

			idString += String.valueOf(integer);

        String FROM_QUERY =

                " FROM TaxonName n "

                + "INNER JOIN TaxonBase tb on n.id = tb.name_id and tb.DTYPE='Taxon' " + // # name<->taxon

        "INNER JOIN TaxonNode tn on tn.taxon_id = tb.id "+

        "INNER JOIN DefinedTermBase rank on n.rank_id = rank.id "+// # rank <-> name

        "LEFT OUTER JOIN DescriptionBase descr on descr.taxon_id = tb.id "+// # taxon <-> taxon description (not every taxon has a description)

        "LEFT OUTER JOIN DefinedTermBase area on area.id = descrEl.area_id ";

        	FROM_QUERY += " WHERE tn.id IN ("+ idString +") ";

        }

        String ORDER_BY = " ORDER BY tb.titleCache ";

        String SELECT_QUERY= "SELECT "

                + "tb.DTYPE AS "+DTYPE_COLUMN+", "

        		"tb.titleCache AS "+TAXON_COLUMN+", " +

        		"rank.titleCache AS "+RANK_COLUMN+", ";

        Map<String, Integer> labels = new HashMap<>();

        for(NamedArea namedArea : namedAreas){

            String label = null;

            String fullLabel = null;

            Representation representation = namedArea.getRepresentation(Language.DEFAULT());

            	fullLabel = representation.getLabel();

				if(DistributionEditorUtil.isAbbreviatedLabels()){

            		label = abbreviatedLabel;

            	}

            		label = fullLabel;

            	}

            }

            //fallback

            if(label==null){

            }

            SELECT_QUERY += "MAX( IF(area.uuid = '"+ namedArea.getUuid() +"', statusTerm.uuid, NULL) ) as '"+ label +"'," ;

        }

        SELECT_QUERY = StringUtils.stripEnd(SELECT_QUERY, ",")+" ";

        SELECT_QUERY= SELECT_QUERY + FROM_QUERY + GROUP_BY + ORDER_BY;

        String COUNT_QUERY = "SELECT count(DISTINCT tb.id)" + FROM_QUERY;

        String CONTAINS_QUERY = "SELECT * FROM TaxonBase tb WHERE tb.uuid = ?";

        //Escape SQL control character '

        return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);

    }

    public static QueryDelegate generateSynonymOfTaxonQuery(String name_id)  {

    	String FROM_QUERY = " FROM TaxonBase tb " +

    			"INNER JOIN TaxonName n on tb.name_id=n.id " +

    			"INNER JOIN TaxonBase acc on tb.acceptedTaxon_id = acc.id "; //or s.id = ?

    	String SELECT_QUERY="SELECT tb.id as " + ID +

    			", n.titleCache as " + name_id +

    			FROM_QUERY;

    	String CONTAINS_QUERY = "SELECT * FROM TaxonBase syn WHERE syn.id = ?"; //or s.id = ?

    public static QueryDelegate generateTaxonRelatedToQuery(String reluuid_id,

            String toname_id)  {

        String FROM_QUERY = "     FROM TaxonRelationship tr " +

        String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;

    /**

    public static QueryDelegate generateTableQuery(String tableName) {

        String FROM_QUERY = " FROM " + tableName;

    }

    public static QueryDelegate generateQueryDelegate(String SELECT_QUERY, String COUNT_QUERY, String CONTAINS_QUERY) {

        FreeformQuery query = new FreeformQuery("This query is not used", CdmSpringContextHelper.getCurrent().getConnectionPool(), ID);

        CdmStatementDelegate delegate = new CdmStatementDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);