commit 8ef1438366b9dd0fa73ca3c46ae0b2673cc299c5
Author: Alexander Oppermann <a.oppermann@bgbm.org>
Date:   Tue Apr 7 16:18:13 2015 +0000

    Added SQL-Container to DB-Status App

diff --git a/src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionSelectionPresenter.java b/src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionSelectionPresenter.java
index 414a0151..73ec885e 100644
--- a/src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionSelectionPresenter.java
+++ b/src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionSelectionPresenter.java
@@ -1,5 +1,6 @@
 package eu.etaxonomy.cdm.vaadin.presenter.dbstatus;
 
+import java.sql.SQLException;
 import java.util.Arrays;
 import java.util.List;
 
@@ -28,7 +29,7 @@ public class DistributionSelectionPresenter implements IDistributionSelectionCom
 	}
 
 	@Override
-	public void buttonClick(Classification classification, TermVocabulary<DefinedTermBase> term) {
+	public void buttonClick(Classification classification, TermVocabulary<DefinedTermBase> term) throws SQLException {
 	    VaadinSession.getCurrent().setAttribute("classificationUUID", classification.getUuid());
 	    VaadinSession.getCurrent().setAttribute("selectedTerm", term.getUuid());
 
diff --git a/src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionTablePresenter.java b/src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionTablePresenter.java
index f175547b..0b1f866d 100644
--- a/src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionTablePresenter.java
+++ b/src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionTablePresenter.java
@@ -1,5 +1,7 @@
 package eu.etaxonomy.cdm.vaadin.presenter.dbstatus;
 
+import java.sql.SQLException;
+import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.HashMap;
 import java.util.HashSet;
@@ -27,10 +29,12 @@ import eu.etaxonomy.cdm.model.description.PresenceAbsenceTerm;
 import eu.etaxonomy.cdm.model.taxon.Classification;
 import eu.etaxonomy.cdm.model.taxon.Taxon;
 import eu.etaxonomy.cdm.model.taxon.TaxonNode;
+import eu.etaxonomy.cdm.vaadin.container.CdmSQLContainer;
 import eu.etaxonomy.cdm.vaadin.model.CdmTaxonTableCollection;
 import eu.etaxonomy.cdm.vaadin.model.DbTableDTO;
 import eu.etaxonomy.cdm.vaadin.model.DbTableDTOS;
 import eu.etaxonomy.cdm.vaadin.model.LazyLoadedContainer;
+import eu.etaxonomy.cdm.vaadin.util.CdmQueryFactory;
 import eu.etaxonomy.cdm.vaadin.util.CdmSpringContextHelper;
 import eu.etaxonomy.cdm.vaadin.view.dbstatus.DistributionTableView;
 import eu.etaxonomy.cdm.vaadin.view.dbstatus.IDistributionTableComponent;
@@ -47,7 +51,7 @@ public class DistributionTablePresenter implements IDistributionTableComponent.D
 	private final DistributionTableView view;
 	private ITaxonService taxonService;
 
-	public DistributionTablePresenter(DistributionTableView dtv){
+	public DistributionTablePresenter(DistributionTableView dtv) throws SQLException{
 	    this.view = dtv;
 	    view.addListener(this);
 	    taxonService = (ITaxonService)CdmSpringContextHelper.newInstance().getBean("taxonServiceImpl");
@@ -60,26 +64,6 @@ public class DistributionTablePresenter implements IDistributionTableComponent.D
 	}
 
 
-	
-	//for sql container
-    public static final String NAME_ID = "Name";
-    public static final String PB_ID = "Pb";
-    public static final String FN_ID = "Fn";
-    public static final String UNP_ID = "Unp";
-    public static final String UNR_ID = "Unr";
-    
-    public static String TERM = null;
-
-//    private static final String SELECT_QUERY="SELECT tb.id as taxon_id, tnb.titleCache as " + NAME_ID + " , tb.publish as " + PB_ID + " , tb.unplaced as " +  UNP_ID + FROM_QUERY;
-    private static final String SELECT_QUERY="Select tb.DTYPE, tb.id, tb.titleCache AS Taxon, deb.DTYPE, deb.id, deb.area_id, dtb.vocabulary_id, dtb1.vocabulary_id, ";
-    
-    private static final String PIVOT_QUERY = "MAX( IF(dtb1.titleCache = '"+ TERM +"', dtb.titleCache, NULL) ) as '"+ TERM +"',";
-    
-    private static final String FROM_QUERY = " From TaxonBase tb JOIN DescriptionBase db ON db.taxon_id=tb.id JOIN DescriptionElementBase deb ON deb.indescription_id=db.id Join DefinedTermBase dtb on deb.status_id=dtb.id Join DefinedTermBase dtb1 on deb.area_id=dtb1.id WHERE deb.DTYPE LIKE 'Distribution' GROUP BY tb.id";
-		
-    private static final String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
-
-	
 	public ComboBox updateDistributionField(DescriptionElementBase deb,
 			Distribution db,
 			BeanItemContainer<PresenceAbsenceTerm> termContainer, ComboBox box,
@@ -97,6 +81,20 @@ public class DistributionTablePresenter implements IDistributionTableComponent.D
 		return term.getTerms();
 	}
 
+	
+	public List<String> getTermList() {
+		VaadinSession session = VaadinSession.getCurrent();
+		UUID termUUID = (UUID) session.getAttribute("selectedTerm");
+		TermVocabulary<DefinedTermBase> term = vocabularyService.load(termUUID);
+		term = CdmBase.deproxy(term, TermVocabulary.class);
+		Set<DefinedTermBase> terms = term.getTerms();
+		List<String> list = new ArrayList<String>();
+		for(DefinedTermBase dtb: terms){
+			list.add(dtb.getTitleCache());
+		}
+		return list;
+	}
+	
 	@Override
 	public HashMap<DescriptionElementBase, Distribution> getDistribution(DefinedTermBase dt, Taxon taxon) {
 		Set<Feature> setFeature = new HashSet<Feature>(Arrays.asList(Feature.DISTRIBUTION()));
@@ -173,7 +171,14 @@ public class DistributionTablePresenter implements IDistributionTableComponent.D
 		return items;
 	}
 	
-	
+	@Override
+	public CdmSQLContainer getSQLContainer() throws SQLException{
+		Classification classification = getChosenClassification();
+		int classificationId = classification.getId();
+		List<String> termList = getTermList();
+		CdmSQLContainer container = new CdmSQLContainer(new CdmQueryFactory().generateTaxonDistributionQuery("id", termList, classificationId));
+		return container;
+	}
 	
 	@Override
 	public LazyLoadedContainer getLazyLoadedContainer(){
diff --git a/src/main/java/eu/etaxonomy/cdm/vaadin/util/CdmQueryFactory.java b/src/main/java/eu/etaxonomy/cdm/vaadin/util/CdmQueryFactory.java
index 8029b5a4..44a29be1 100644
--- a/src/main/java/eu/etaxonomy/cdm/vaadin/util/CdmQueryFactory.java
+++ b/src/main/java/eu/etaxonomy/cdm/vaadin/util/CdmQueryFactory.java
@@ -10,6 +10,7 @@
 package eu.etaxonomy.cdm.vaadin.util;
 
 import java.sql.SQLException;
+import java.util.List;
 
 import com.vaadin.data.util.sqlcontainer.query.FreeformQuery;
 import com.vaadin.data.util.sqlcontainer.query.QueryDelegate;
@@ -48,20 +49,65 @@ public class CdmQueryFactory {
         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY, id);
     }
 
-    public static QueryDelegate generateSynonymofTaxonQuery(String id,
-            String name_id) throws SQLException {
-        String FROM_QUERY = " FROM TaxonBase tb " +
-                "INNER JOIN TaxonNameBase tnb on tb.name_id=tnb.id " +
-                "INNER JOIN SynonymRelationship sr on tb.id=sr.relatedfrom_id ";
-        String SELECT_QUERY="SELECT tb.id as " + id +
-                ", tnb.titleCache as " + name_id +
-                FROM_QUERY;
-        String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
-        String CONTAINS_QUERY = "SELECT * FROM SynonymRelationship sr WHERE sr.relatedfrom_id = ?";
+    public static QueryDelegate generateTaxonDistributionQuery(String id,
+            List<String> termList, int classificationID) throws SQLException {
+        String FROM_QUERY = 
+        		" FROM TaxonNode tn " +
+        		"INNER JOIN TaxonBase tb on tn.taxon_id = tb.id " +
+        		"INNER JOIN Classification cl ON tn.classification_id = cl.id " +
+        		"LEFT OUTER JOIN TaxonNameBase tnb ON tnb.id=tb.id " + 
+        		"LEFT OUTER JOIN DescriptionBase db ON db.taxon_id=tb.id " +
+        		"LEFT OUTER JOIN (SELECT indescription_id, area_id, status_id, DTYPE, id FROM DescriptionElementBase deb WHERE deb.DTYPE LIKE 'Distribution') AS deb ON deb.indescription_id=db.id " +
+        		"LEFT OUTER JOIN DefinedTermBase dtb on deb.status_id=dtb.id " +
+        		"LEFT OUTER JOIN DefinedTermBase dtb1 on deb.area_id=dtb1.id " +
+        		"LEFT OUTER JOIN DefinedTermBase dtb2 on tnb.rank_id = dtb2.id " +
+        		"WHERE tn.classification_id = "+ classificationID +" AND tb.DTYPE = 'Taxon'" ;
+
+        String GROUP_BY = " GROUP BY tb.id ";
+       
+        String SELECT_QUERY=
+        		"SELECT tb.DTYPE," +
+        		"tb.id, " +
+        		"tn.classification_id, " +
+        		"tb.titleCache AS Taxon, " +
+        		"dtb2.titleCache AS Rank, " +
+        		"deb.DTYPE," +
+        		"deb.id, " +
+        		"deb.area_id, "+
+        		"dtb.vocabulary_id, " +
+        		"dtb1.vocabulary_id, "; 
+        int count = termList.size();
+        for(String term : termList){
+        	if(count == 1){
+        		SELECT_QUERY= SELECT_QUERY + 
+            			"MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"' " ;
+        	}else{
+        		SELECT_QUERY= SELECT_QUERY + 
+        				"MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"'," ;
+        	}
+        	count--;
+        }
+        SELECT_QUERY= SELECT_QUERY + FROM_QUERY + GROUP_BY; 
+        String COUNT_QUERY = "SELECT count(DISTINCT tb.id)" + FROM_QUERY;
+        String CONTAINS_QUERY = "SELECT * FROM TaxonNode tn WHERE tn.id = ?";
 
         return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY, id);
     }
 
+    public static QueryDelegate generateSynonymofTaxonQuery(String id,
+    		String name_id) throws SQLException {
+    	String FROM_QUERY = " FROM TaxonBase tb " +
+    			"INNER JOIN TaxonNameBase tnb on tb.name_id=tnb.id " +
+    			"INNER JOIN SynonymRelationship sr on tb.id=sr.relatedfrom_id ";
+    	String SELECT_QUERY="SELECT tb.id as " + id +
+    			", tnb.titleCache as " + name_id +
+    			FROM_QUERY;
+    	String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
+    	String CONTAINS_QUERY = "SELECT * FROM SynonymRelationship sr WHERE sr.relatedfrom_id = ?";
+    	
+    	return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY, id);
+    }
+
     public static QueryDelegate generateQueryDelegate(String SELECT_QUERY, String COUNT_QUERY, String CONTAINS_QUERY, String id) throws SQLException {
         FreeformQuery query = new FreeformQuery("This query is not used", CdmSpringContextHelper.getConnectionPool(), id);
         CdmStatementDelegate delegate = new CdmStatementDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
diff --git a/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionSelectionView.java b/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionSelectionView.java
index c4c9a455..1950d945 100644
--- a/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionSelectionView.java
+++ b/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionSelectionView.java
@@ -1,5 +1,6 @@
 package eu.etaxonomy.cdm.vaadin.view.dbstatus;
 
+import java.sql.SQLException;
 import java.util.List;
 
 import com.vaadin.annotations.AutoGenerated;
@@ -76,7 +77,12 @@ public class DistributionSelectionView extends CustomComponent implements IDistr
 		if(classificationSelection != null && distributionSelection != null){
 			Classification classification = (Classification) classificationSelection.getValue();
 			TermVocabulary<DefinedTermBase> term = (TermVocabulary<DefinedTermBase>)distributionSelection.getValue();
-			distListener.buttonClick(classification, term);
+			try {
+				distListener.buttonClick(classification, term);
+			} catch (SQLException e) {
+				// TODO Auto-generated catch block
+				e.printStackTrace();
+			}
 		}
 	}
 
diff --git a/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionTableView.java b/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionTableView.java
index 55c31414..59f6e05d 100644
--- a/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionTableView.java
+++ b/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionTableView.java
@@ -1,5 +1,6 @@
 package eu.etaxonomy.cdm.vaadin.view.dbstatus;
 
+import java.sql.SQLException;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.HashMap;
@@ -12,10 +13,7 @@ import com.vaadin.annotations.AutoGenerated;
 import com.vaadin.data.Container;
 import com.vaadin.data.Property;
 import com.vaadin.data.Property.ValueChangeListener;
-import com.vaadin.data.util.BeanItem;
 import com.vaadin.data.util.BeanItemContainer;
-import com.vaadin.event.ItemClickEvent;
-import com.vaadin.event.ItemClickEvent.ItemClickListener;
 import com.vaadin.event.ShortcutAction.KeyCode;
 import com.vaadin.event.ShortcutAction.ModifierKey;
 import com.vaadin.navigator.View;
@@ -46,8 +44,7 @@ import eu.etaxonomy.cdm.model.location.NamedArea;
 import eu.etaxonomy.cdm.model.taxon.Taxon;
 import eu.etaxonomy.cdm.model.taxon.TaxonNode;
 import eu.etaxonomy.cdm.vaadin.component.HorizontalToolbar;
-import eu.etaxonomy.cdm.vaadin.model.CdmTaxonTableCollection;
-import eu.etaxonomy.cdm.vaadin.model.LazyLoadedContainer;
+import eu.etaxonomy.cdm.vaadin.container.CdmSQLContainer;
 
 public class DistributionTableView extends CustomComponent implements IDistributionTableComponent, View{
 
@@ -125,27 +122,30 @@ public class DistributionTableView extends CustomComponent implements IDistribut
 
 
     
-	public void dataBinding(){
+	public void dataBinding() throws SQLException{
 
 //		container.addNestedContainerProperty("dDTO.status");
-		final LazyLoadedContainer container = listener.getLazyLoadedContainer();
-		Set<DefinedTermBase> chosenTerms = listener.getChosenTerms();
-		generateDsitributionColumn(chosenTerms);
+//		final LazyLoadedContainer container = listener.getLazyLoadedContainer();
+//		Set<DefinedTermBase> chosenTerms = listener.getChosenTerms();
+//		generateDsitributionColumn(chosenTerms);
+		
+		CdmSQLContainer container = listener.getSQLContainer();
 		
 		table_1.setContainerDataSource(container);
 		table_1.setColumnReorderingAllowed(true);
-		table_1.setSortEnabled(false);
+		table_1.setSortEnabled(true);
 //		setVisibleColumns(columns);
-		Object[] visibleColumns = columnList.toArray();
-		table_1.setVisibleColumns(visibleColumns);
-		table_1.setColumnHeaders(headerList.toArray(new String[headerList.size()]));//new String[]{"Taxon", "Rang"});// ,"Deutschland"
+//		Object[] visibleColumns = columnList.toArray();
+//		table_1.setVisibleColumns(visibleColumns);
+//		table_1.setColumnHeaders(headerList.toArray(new String[headerList.size()]));//new String[]{"Taxon", "Rang"});// ,"Deutschland"
+		
 		table_1.setColumnCollapsingAllowed(true);
 		table_1.setSelectable(true);
 //		table_1.setSizeUndefined();
 //		setSizeFull();
 		table_1.setPageLength(20);
 		table_1.setFooterVisible(true);
-		table_1.setColumnFooter("fullTitleCache", "Total amount of Taxa displayed: " + container.size());
+		table_1.setColumnFooter("Taxon", "Total amount of Taxa displayed: " + container.size());
 
 		table_1.setCacheRate(10);
 	}
@@ -389,17 +389,18 @@ public class DistributionTableView extends CustomComponent implements IDistribut
 			}
 		});
 
-		table_1.addItemClickListener(new ItemClickListener() {
-
-			@Override
-			public void itemClick(ItemClickEvent event) {
-				Object taxonbean = ((BeanItem<?>)event.getItem()).getBean();
-				if(taxonbean instanceof CdmTaxonTableCollection){
-					CdmTaxonTableCollection red = (CdmTaxonTableCollection) taxonbean;
-					currentTaxon = red.getTaxon();
-				}
-			}
-		});
+		//FIXME with new SQL CONTAINER
+//		table_1.addItemClickListener(new ItemClickListener() {
+//
+//			@Override
+//			public void itemClick(ItemClickEvent event) {
+//				Object taxonbean = ((BeanItem<?>)event.getItem()).getBean();
+//				if(taxonbean instanceof CdmTaxonTableCollection){
+//					CdmTaxonTableCollection red = (CdmTaxonTableCollection) taxonbean;
+//					currentTaxon = red.getTaxon();
+//				}
+//			}
+//		});
 	}
 
 }
diff --git a/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionSelectionComponent.java b/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionSelectionComponent.java
index 8e28221c..e1f02ea2 100644
--- a/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionSelectionComponent.java
+++ b/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionSelectionComponent.java
@@ -1,5 +1,6 @@
 package eu.etaxonomy.cdm.vaadin.view.dbstatus;
 
+import java.sql.SQLException;
 import java.util.List;
 
 import eu.etaxonomy.cdm.model.common.DefinedTermBase;
@@ -8,7 +9,7 @@ import eu.etaxonomy.cdm.model.taxon.Classification;
 
 public interface IDistributionSelectionComponent {
 	public interface DistributionSelectionComponentListener{
-		void buttonClick(Classification classification, TermVocabulary<DefinedTermBase> term);
+		void buttonClick(Classification classification, TermVocabulary<DefinedTermBase> term) throws SQLException;
 		
 		List<Classification> getClassificationList();
 		
diff --git a/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionTableComponent.java b/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionTableComponent.java
index a8ab5a4e..b30a87ea 100644
--- a/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionTableComponent.java
+++ b/src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionTableComponent.java
@@ -1,5 +1,6 @@
 package eu.etaxonomy.cdm.vaadin.view.dbstatus;
 
+import java.sql.SQLException;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Set;
@@ -21,6 +22,7 @@ import eu.etaxonomy.cdm.model.description.PresenceAbsenceTerm;
 import eu.etaxonomy.cdm.model.taxon.Classification;
 import eu.etaxonomy.cdm.model.taxon.Taxon;
 import eu.etaxonomy.cdm.model.taxon.TaxonNode;
+import eu.etaxonomy.cdm.vaadin.container.CdmSQLContainer;
 import eu.etaxonomy.cdm.vaadin.model.DbTableDTOS;
 import eu.etaxonomy.cdm.vaadin.model.LazyLoadedContainer;
 
@@ -64,6 +66,8 @@ public interface IDistributionTableComponent {
 
 		List<TaxonNode> getAllNodes(int start, int end);
 
+		CdmSQLContainer getSQLContainer() throws SQLException;
+
 	}
 	public void addListener(DistributionTableComponentListener listener);
 }
