Project

General

Profile

« Previous | Next » 

Revision ff732f0a

Added by Patrick Plitzner almost 8 years ago

  • ID ff732f0a45cd8539f08196159f54d69496300ffe
  • Parent 99f6841e

Load taxonNodes in SQL query (unstable) #5458

View differences:

src/main/java/eu/etaxonomy/cdm/vaadin/container/TaxonNodeContainer.java
1
// $Id$
2
/**
3
* Copyright (C) 2016 EDIT
4
* European Distributed Institute of Taxonomy
5
* http://www.e-taxonomy.eu
6
*
7
* The contents of this file are subject to the Mozilla Public License Version 1.1
8
* See LICENSE.TXT at the top of this package for the full license terms.
9
*/
10
package eu.etaxonomy.cdm.vaadin.container;
11

  
12
import java.util.List;
13

  
14
import com.vaadin.data.util.HierarchicalContainer;
15

  
16
import eu.etaxonomy.cdm.model.taxon.TaxonNode;
17

  
18
/**
19
 * @author pplitzner
20
 * @date 03.06.2016
21
 *
22
 */
23
public class TaxonNodeContainer extends HierarchicalContainer {
24

  
25
    public TaxonNodeContainer(List<TaxonNode> rootNodes) {
26
        for(TaxonNode rootNode:rootNodes){
27
            addItem(rootNode);
28
            //        setItemCaption(rootNode, rootNode.getClassification().getName().toString());
29
            setParent(rootNode, null);
30
            addChildNodes(rootNode);
31
        }
32
    }
33

  
34
    private void addChildNodes(TaxonNode parentNode) {
35
        List<TaxonNode> childNodes = parentNode.getChildNodes();
36
        if(childNodes.size()==0){
37
            //set node to be a leaf
38
            setChildrenAllowed(parentNode, false);
39
        }
40
        for (TaxonNode taxonNode : childNodes) {
41
            if(taxonNode!=null){//TODO when does this happen?? orphaned taxa??
42
                addItem(taxonNode);
43
                //            setItemCaption(taxonNode, taxonNode.getTaxon().getName().getTitleCache());
44
                setParent(taxonNode, parentNode);
45
                addChildNodes(taxonNode);
46
            }
47
        }
48
    }
49

  
50
}
src/main/java/eu/etaxonomy/cdm/vaadin/model/LazyLoadedContainer.java
18 18

  
19 19
	DistributionTableComponentListener listener;
20 20

  
21
	
21

  
22 22
	public LazyLoadedContainer(Class type) throws IllegalArgumentException {
23 23
		super(type);
24 24
	}
25
//	
25
//
26 26
//	public LazyLoadedContainer(Class type, IClassificationService classificationService, ITaxonNodeService taxonNodeService, IDescriptionService descriptionService) throws IllegalArgumentException {
27 27
//		super(type);
28 28
//		this.classificationService = classificationService;
29 29
//		this.taxonNodeService = taxonNodeService;
30 30
//		this.descriptionService = descriptionService;
31
//		
31
//
32 32
//	}
33
	
33

  
34 34
	@Override
35 35
	public int size(){
36
		return listener.getSizeOfClassification();
36
		return listener.getSizeOfTaxonNode();
37 37
	}
38
	
38

  
39 39
	@Override
40 40
	public BeanItem getItem(Object itemId){
41 41
		TaxonNode taxonNode = ((TaxonNode) itemId);
42 42
		CdmBase.deproxy(taxonNode, TaxonNode.class);
43
		Taxon taxon = (Taxon)taxonNode.getTaxon();
43
		Taxon taxon = taxonNode.getTaxon();
44 44
		CdmBase.deproxy(taxon, Taxon.class);
45 45
		CdmTaxonTableCollection cttc = new CdmTaxonTableCollection(taxon);
46 46
		return new BeanItem(cttc);
47 47
	}
48
	
48

  
49 49
	@Override
50 50
	public List getItemIds(int startIndex, int numberOfIds){
51 51
		int endIndex = startIndex + numberOfIds;
......
60 60
		this.listener = listener;
61 61
	}
62 62

  
63
	
63

  
64 64

  
65 65
}
src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionSelectionPresenter.java
31 31

  
32 32
	@Override
33 33
	public void buttonClick(TermVocabulary<DefinedTermBase> term, TaxonNode taxonNode) throws SQLException {
34
//	    VaadinSession.getCurrent().setAttribute("classificationUUID", classification.getUuid());
35 34
	    VaadinSession.getCurrent().setAttribute("taxonNodeUuid", taxonNode.getUuid());
36 35
	    VaadinSession.getCurrent().setAttribute("selectedTerm", term.getUuid());
37 36

  
src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionTablePresenter.java
14 14

  
15 15
import com.vaadin.data.Container;
16 16
import com.vaadin.data.util.BeanItemContainer;
17
import com.vaadin.data.util.IndexedContainer;
17 18
import com.vaadin.server.VaadinSession;
18 19

  
19 20
import eu.etaxonomy.cdm.api.service.IClassificationService;
......
33 34
import eu.etaxonomy.cdm.model.description.PresenceAbsenceTerm;
34 35
import eu.etaxonomy.cdm.model.description.TaxonDescription;
35 36
import eu.etaxonomy.cdm.model.location.NamedArea;
36
import eu.etaxonomy.cdm.model.taxon.Classification;
37 37
import eu.etaxonomy.cdm.model.taxon.Taxon;
38 38
import eu.etaxonomy.cdm.model.taxon.TaxonNode;
39 39
import eu.etaxonomy.cdm.vaadin.container.CdmSQLContainer;
......
180 180
	}
181 181

  
182 182

  
183
	@Override
184
	public Classification getChosenClassification() {
185
		VaadinSession session = VaadinSession.getCurrent();
186
		UUID classificationUUID = (UUID) session.getAttribute("classificationUUID");
187
		Classification classification = classificationService.load(classificationUUID);
188
		return classification;
189
	}
190

  
191 183
	@Override
192 184
	public TaxonNode getChosenTaxonNode() {
193 185
	    VaadinSession session = VaadinSession.getCurrent();
......
196 188
	    return taxonNode;
197 189
	}
198 190

  
199
	@Override
200
	public int getSizeOfClassification(){
201
		TaxonNode taxonNode = getChosenTaxonNode();
202
		return taxonNodeService.loadChildNodesOfTaxonNode(taxonNode, null, true, null).size();
203
	}
204

  
205 191
	@Override
206 192
	public int getSizeOfTaxonNode(){
207 193
	    TaxonNode taxonNode = getChosenTaxonNode();
......
212 198
	@Override
213 199
	public CdmSQLContainer getSQLContainer() throws SQLException{
214 200
		TaxonNode taxonNode = getChosenTaxonNode();
215
		int classificationId = taxonNode.getId();
201
		List<Integer>taxonNodeIds = new ArrayList<Integer>();
202
		taxonNodeIds.add(taxonNode.getId());
203
		for (TaxonNode taxonNode2 : taxonNodeService.loadChildNodesOfTaxonNode(taxonNode, null, true, null)) {
204
		    taxonNodeIds.add(taxonNode2.getId());
205
        }
216 206
		List<String> termList = getTermList();
217
		CdmSQLContainer container = new CdmSQLContainer(CdmQueryFactory.generateTaxonDistributionQuery(termList, classificationId));
207
		CdmSQLContainer container = new CdmSQLContainer(CdmQueryFactory.generateTaxonDistributionQuery(termList, taxonNodeIds));
218 208
		return container;
219 209
	}
220 210

  
211
	@Override
212
	public Container getTaxonNodeContainer() {
213
	    TaxonNode taxonNode = getChosenTaxonNode();
214
	    List<TaxonNode> nodes = taxonNodeService.loadChildNodesOfTaxonNode(taxonNode, null, true, null);
215
	    IndexedContainer container = new IndexedContainer();
216
	    for (TaxonNode taxonNode2 : nodes) {
217
            container.addItem(new Object[]{taxonNode2.getTaxon(),taxonNode2.getTaxon().getName().getRank()});
218
        }
219
	    return container;
220
	}
221

  
221 222
	@Override
222 223
	public void createDistributionField(final Taxon taxon, Object comboboxValue, String area) {
223 224
		Set<DefinedTermBase> chosenTerms = getChosenTerms();
src/main/java/eu/etaxonomy/cdm/vaadin/util/CdmQueryFactory.java
10 10
package eu.etaxonomy.cdm.vaadin.util;
11 11

  
12 12
import java.sql.SQLException;
13
import java.util.Iterator;
13 14
import java.util.List;
14 15

  
15 16
import com.vaadin.data.util.sqlcontainer.query.FreeformQuery;
......
50 51
        return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
51 52
    }
52 53

  
53
    public static QueryDelegate generateTaxonDistributionQuery(List<String> termList, int classificationID) throws SQLException {
54
//    public static QueryDelegate generateTaxonDistributionQuery(List<String> termList, int classificationID) throws SQLException {
55
//        String FROM_QUERY =
56
//                " FROM TaxonNode tn " +
57
//                        "INNER JOIN TaxonBase tb on tn.taxon_id = tb.id " +
58
//                        "INNER JOIN Classification cl ON tn.classification_id = cl.id " +
59
//                        "LEFT OUTER JOIN TaxonNameBase tnb ON tnb.id=tb.id " +
60
//                        "LEFT OUTER JOIN DescriptionBase db ON db.taxon_id=tb.id " +
61
//                        "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 " +
62
//                        "LEFT OUTER JOIN DefinedTermBase dtb on deb.status_id=dtb.id " +
63
//                        "LEFT OUTER JOIN DefinedTermBase dtb1 on deb.area_id=dtb1.id " +
64
//                        "LEFT OUTER JOIN DefinedTermBase dtb2 on tnb.rank_id = dtb2.id " +
65
//                        "WHERE tn.classification_id = "+ classificationID +" AND tb.DTYPE = 'Taxon'" ;
66
//
67
//        String GROUP_BY = " GROUP BY tb.id ";
68
//
69
//        String SELECT_QUERY=
70
//                "SELECT tb.DTYPE," +
71
//                        "tb.id, " +
72
//                        "tb.uuid, " +
73
//                        "tn.classification_id, " +
74
//                        "tb.titleCache AS Taxon, " +
75
//                        "dtb2.titleCache AS Rank, ";
76
//
77
//        int count = termList.size();
78
//        for(String term : termList){
79
//            if(count == 1){
80
//                SELECT_QUERY= SELECT_QUERY +
81
//                        "MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"' " ;
82
//            }else{
83
//                SELECT_QUERY= SELECT_QUERY +
84
//                        "MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"'," ;
85
//            }
86
//            count--;
87
//        }
88
//        SELECT_QUERY= SELECT_QUERY + FROM_QUERY + GROUP_BY;
89
//        String COUNT_QUERY = "SELECT count(DISTINCT tb.id)" + FROM_QUERY;
90
////        String CONTAINS_QUERY = "SELECT * FROM TaxonNode tn WHERE tn.id = ?";
91
//        String CONTAINS_QUERY = "SELECT * FROM TaxonBase tb WHERE tb.uuid = ?";
92
//
93
//        return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
94
//    }
95

  
96
    public static QueryDelegate generateTaxonDistributionQuery(List<String> termList, List<Integer> taxonNodeIds) throws SQLException {
97
        String idArray = "";
98
        Iterator<Integer> nodeIterator = taxonNodeIds.iterator();
99
        while (nodeIterator.hasNext()) {
100
            idArray += nodeIterator.next();
101
            if(nodeIterator.hasNext()){
102
                idArray += ", ";
103
            }
104
        }
54 105
        String FROM_QUERY =
55 106
        		" FROM TaxonNode tn " +
56 107
        		"INNER JOIN TaxonBase tb on tn.taxon_id = tb.id " +
......
61 112
        		"LEFT OUTER JOIN DefinedTermBase dtb on deb.status_id=dtb.id " +
62 113
        		"LEFT OUTER JOIN DefinedTermBase dtb1 on deb.area_id=dtb1.id " +
63 114
        		"LEFT OUTER JOIN DefinedTermBase dtb2 on tnb.rank_id = dtb2.id " +
64
        		"WHERE tn.classification_id = "+ classificationID +" AND tb.DTYPE = 'Taxon'" ;
115
        		"WHERE tn.id IN ("+ idArray +") AND tb.DTYPE = 'Taxon'" ;
65 116

  
66 117
        String GROUP_BY = " GROUP BY tb.id ";
67 118

  
src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionTableView.java
66 66
	protected List<Field> fields = new ArrayList<Field>();
67 67

  
68 68
	List<String> columnList;
69
	ArrayList<String> headerList;
70 69

  
71 70
	/**
72 71
	 * The constructor should first build the main layout, set the
......
124 123

  
125 124

  
126 125
	public void dataBinding() throws SQLException{
127
		CdmSQLContainer container = listener.getSQLContainer();
126
		Container container = listener.getSQLContainer();
128 127

  
129 128
		table.setContainerDataSource(container);
130 129
		table.setColumnReorderingAllowed(true);
......
135 134
		Object[] visibleColumns = columnList.toArray();
136 135
		table.setVisibleColumns(visibleColumns);
137 136

  
138
		headerList = new ArrayList<String>(Arrays.asList(new String[]{"Taxon","Rang"}));
139
		headerList.addAll(listener.getAbbreviatedTermList());
140
//		table.setColumnHeaders(headerList.toArray(new String[headerList.size()]));//new String[]{"Taxon", "Rang"});// ,"Deutschland"
141

  
142 137
		table.setColumnCollapsingAllowed(true);
143 138
		table.setSelectable(true);
144 139
		table.setPageLength(20);
src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionTableComponent.java
18 18
import eu.etaxonomy.cdm.model.description.Distribution;
19 19
import eu.etaxonomy.cdm.model.description.Feature;
20 20
import eu.etaxonomy.cdm.model.description.PresenceAbsenceTerm;
21
import eu.etaxonomy.cdm.model.taxon.Classification;
22 21
import eu.etaxonomy.cdm.model.taxon.Taxon;
23 22
import eu.etaxonomy.cdm.model.taxon.TaxonNode;
24 23
import eu.etaxonomy.cdm.vaadin.container.CdmSQLContainer;
......
35 34

  
36 35
		LazyLoadedContainer getTableContainer();
37 36

  
38
		int getSizeOfClassification();
39

  
40 37
		int getSizeOfTaxonNode();
41 38

  
42 39
		List<Distribution> getDistribution(Taxon taxon);
......
56 53
		List<DescriptionElementBase> listDescriptionElementsForTaxon(
57 54
				Taxon taxon, Set<Feature> setFeature);
58 55

  
59
		Classification getChosenClassification();
60

  
61 56
		TaxonNode getChosenTaxonNode();
62 57

  
63 58
		List<TaxonNode> getAllNodes(int start, int end);
64 59

  
65 60
		CdmSQLContainer getSQLContainer() throws SQLException;
66 61

  
62
		Container getTaxonNodeContainer();
63

  
67 64
		List<String> getTermList();
68 65

  
69 66
		Container getPresenceAbsenceContainer();

Also available in: Unified diff