Project

General

Profile

Revision 8ef14383

ID8ef1438366b9dd0fa73ca3c46ae0b2673cc299c5
Parent aceb7b1d
Child 73baaed9

Added by Alexander Oppermann over 5 years ago

Added SQL-Container to DB-Status App

View differences:

src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionSelectionPresenter.java
1 1
package eu.etaxonomy.cdm.vaadin.presenter.dbstatus;
2 2

  
3
import java.sql.SQLException;
3 4
import java.util.Arrays;
4 5
import java.util.List;
5 6

  
......
28 29
	}
29 30

  
30 31
	@Override
31
	public void buttonClick(Classification classification, TermVocabulary<DefinedTermBase> term) {
32
	public void buttonClick(Classification classification, TermVocabulary<DefinedTermBase> term) throws SQLException {
32 33
	    VaadinSession.getCurrent().setAttribute("classificationUUID", classification.getUuid());
33 34
	    VaadinSession.getCurrent().setAttribute("selectedTerm", term.getUuid());
34 35

  
src/main/java/eu/etaxonomy/cdm/vaadin/presenter/dbstatus/DistributionTablePresenter.java
1 1
package eu.etaxonomy.cdm.vaadin.presenter.dbstatus;
2 2

  
3
import java.sql.SQLException;
4
import java.util.ArrayList;
3 5
import java.util.Arrays;
4 6
import java.util.HashMap;
5 7
import java.util.HashSet;
......
27 29
import eu.etaxonomy.cdm.model.taxon.Classification;
28 30
import eu.etaxonomy.cdm.model.taxon.Taxon;
29 31
import eu.etaxonomy.cdm.model.taxon.TaxonNode;
32
import eu.etaxonomy.cdm.vaadin.container.CdmSQLContainer;
30 33
import eu.etaxonomy.cdm.vaadin.model.CdmTaxonTableCollection;
31 34
import eu.etaxonomy.cdm.vaadin.model.DbTableDTO;
32 35
import eu.etaxonomy.cdm.vaadin.model.DbTableDTOS;
33 36
import eu.etaxonomy.cdm.vaadin.model.LazyLoadedContainer;
37
import eu.etaxonomy.cdm.vaadin.util.CdmQueryFactory;
34 38
import eu.etaxonomy.cdm.vaadin.util.CdmSpringContextHelper;
35 39
import eu.etaxonomy.cdm.vaadin.view.dbstatus.DistributionTableView;
36 40
import eu.etaxonomy.cdm.vaadin.view.dbstatus.IDistributionTableComponent;
......
47 51
	private final DistributionTableView view;
48 52
	private ITaxonService taxonService;
49 53

  
50
	public DistributionTablePresenter(DistributionTableView dtv){
54
	public DistributionTablePresenter(DistributionTableView dtv) throws SQLException{
51 55
	    this.view = dtv;
52 56
	    view.addListener(this);
53 57
	    taxonService = (ITaxonService)CdmSpringContextHelper.newInstance().getBean("taxonServiceImpl");
......
60 64
	}
61 65

  
62 66

  
63
	
64
	//for sql container
65
    public static final String NAME_ID = "Name";
66
    public static final String PB_ID = "Pb";
67
    public static final String FN_ID = "Fn";
68
    public static final String UNP_ID = "Unp";
69
    public static final String UNR_ID = "Unr";
70
    
71
    public static String TERM = null;
72

  
73
//    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;
74
    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, ";
75
    
76
    private static final String PIVOT_QUERY = "MAX( IF(dtb1.titleCache = '"+ TERM +"', dtb.titleCache, NULL) ) as '"+ TERM +"',";
77
    
78
    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";
79
		
80
    private static final String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
81

  
82
	
83 67
	public ComboBox updateDistributionField(DescriptionElementBase deb,
84 68
			Distribution db,
85 69
			BeanItemContainer<PresenceAbsenceTerm> termContainer, ComboBox box,
......
97 81
		return term.getTerms();
98 82
	}
99 83

  
84
	
85
	public List<String> getTermList() {
86
		VaadinSession session = VaadinSession.getCurrent();
87
		UUID termUUID = (UUID) session.getAttribute("selectedTerm");
88
		TermVocabulary<DefinedTermBase> term = vocabularyService.load(termUUID);
89
		term = CdmBase.deproxy(term, TermVocabulary.class);
90
		Set<DefinedTermBase> terms = term.getTerms();
91
		List<String> list = new ArrayList<String>();
92
		for(DefinedTermBase dtb: terms){
93
			list.add(dtb.getTitleCache());
94
		}
95
		return list;
96
	}
97
	
100 98
	@Override
101 99
	public HashMap<DescriptionElementBase, Distribution> getDistribution(DefinedTermBase dt, Taxon taxon) {
102 100
		Set<Feature> setFeature = new HashSet<Feature>(Arrays.asList(Feature.DISTRIBUTION()));
......
173 171
		return items;
174 172
	}
175 173
	
176
	
174
	@Override
175
	public CdmSQLContainer getSQLContainer() throws SQLException{
176
		Classification classification = getChosenClassification();
177
		int classificationId = classification.getId();
178
		List<String> termList = getTermList();
179
		CdmSQLContainer container = new CdmSQLContainer(new CdmQueryFactory().generateTaxonDistributionQuery("id", termList, classificationId));
180
		return container;
181
	}
177 182
	
178 183
	@Override
179 184
	public LazyLoadedContainer getLazyLoadedContainer(){
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.List;
13 14

  
14 15
import com.vaadin.data.util.sqlcontainer.query.FreeformQuery;
15 16
import com.vaadin.data.util.sqlcontainer.query.QueryDelegate;
......
48 49
        return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY, id);
49 50
    }
50 51

  
51
    public static QueryDelegate generateSynonymofTaxonQuery(String id,
52
            String name_id) throws SQLException {
53
        String FROM_QUERY = " FROM TaxonBase tb " +
54
                "INNER JOIN TaxonNameBase tnb on tb.name_id=tnb.id " +
55
                "INNER JOIN SynonymRelationship sr on tb.id=sr.relatedfrom_id ";
56
        String SELECT_QUERY="SELECT tb.id as " + id +
57
                ", tnb.titleCache as " + name_id +
58
                FROM_QUERY;
59
        String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
60
        String CONTAINS_QUERY = "SELECT * FROM SynonymRelationship sr WHERE sr.relatedfrom_id = ?";
52
    public static QueryDelegate generateTaxonDistributionQuery(String id,
53
            List<String> termList, int classificationID) throws SQLException {
54
        String FROM_QUERY = 
55
        		" FROM TaxonNode tn " +
56
        		"INNER JOIN TaxonBase tb on tn.taxon_id = tb.id " +
57
        		"INNER JOIN Classification cl ON tn.classification_id = cl.id " +
58
        		"LEFT OUTER JOIN TaxonNameBase tnb ON tnb.id=tb.id " + 
59
        		"LEFT OUTER JOIN DescriptionBase db ON db.taxon_id=tb.id " +
60
        		"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 " +
61
        		"LEFT OUTER JOIN DefinedTermBase dtb on deb.status_id=dtb.id " +
62
        		"LEFT OUTER JOIN DefinedTermBase dtb1 on deb.area_id=dtb1.id " +
63
        		"LEFT OUTER JOIN DefinedTermBase dtb2 on tnb.rank_id = dtb2.id " +
64
        		"WHERE tn.classification_id = "+ classificationID +" AND tb.DTYPE = 'Taxon'" ;
65

  
66
        String GROUP_BY = " GROUP BY tb.id ";
67
       
68
        String SELECT_QUERY=
69
        		"SELECT tb.DTYPE," +
70
        		"tb.id, " +
71
        		"tn.classification_id, " +
72
        		"tb.titleCache AS Taxon, " +
73
        		"dtb2.titleCache AS Rank, " +
74
        		"deb.DTYPE," +
75
        		"deb.id, " +
76
        		"deb.area_id, "+
77
        		"dtb.vocabulary_id, " +
78
        		"dtb1.vocabulary_id, "; 
79
        int count = termList.size();
80
        for(String term : termList){
81
        	if(count == 1){
82
        		SELECT_QUERY= SELECT_QUERY + 
83
            			"MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"' " ;
84
        	}else{
85
        		SELECT_QUERY= SELECT_QUERY + 
86
        				"MAX( IF(dtb1.titleCache = '"+ term +"', dtb.titleCache, NULL) ) as '"+ term +"'," ;
87
        	}
88
        	count--;
89
        }
90
        SELECT_QUERY= SELECT_QUERY + FROM_QUERY + GROUP_BY; 
91
        String COUNT_QUERY = "SELECT count(DISTINCT tb.id)" + FROM_QUERY;
92
        String CONTAINS_QUERY = "SELECT * FROM TaxonNode tn WHERE tn.id = ?";
61 93

  
62 94
        return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY, id);
63 95
    }
64 96

  
97
    public static QueryDelegate generateSynonymofTaxonQuery(String id,
98
    		String name_id) throws SQLException {
99
    	String FROM_QUERY = " FROM TaxonBase tb " +
100
    			"INNER JOIN TaxonNameBase tnb on tb.name_id=tnb.id " +
101
    			"INNER JOIN SynonymRelationship sr on tb.id=sr.relatedfrom_id ";
102
    	String SELECT_QUERY="SELECT tb.id as " + id +
103
    			", tnb.titleCache as " + name_id +
104
    			FROM_QUERY;
105
    	String COUNT_QUERY = "SELECT count(*) " + FROM_QUERY;
106
    	String CONTAINS_QUERY = "SELECT * FROM SynonymRelationship sr WHERE sr.relatedfrom_id = ?";
107
    	
108
    	return generateQueryDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY, id);
109
    }
110

  
65 111
    public static QueryDelegate generateQueryDelegate(String SELECT_QUERY, String COUNT_QUERY, String CONTAINS_QUERY, String id) throws SQLException {
66 112
        FreeformQuery query = new FreeformQuery("This query is not used", CdmSpringContextHelper.getConnectionPool(), id);
67 113
        CdmStatementDelegate delegate = new CdmStatementDelegate(SELECT_QUERY, COUNT_QUERY, CONTAINS_QUERY);
src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionSelectionView.java
1 1
package eu.etaxonomy.cdm.vaadin.view.dbstatus;
2 2

  
3
import java.sql.SQLException;
3 4
import java.util.List;
4 5

  
5 6
import com.vaadin.annotations.AutoGenerated;
......
76 77
		if(classificationSelection != null && distributionSelection != null){
77 78
			Classification classification = (Classification) classificationSelection.getValue();
78 79
			TermVocabulary<DefinedTermBase> term = (TermVocabulary<DefinedTermBase>)distributionSelection.getValue();
79
			distListener.buttonClick(classification, term);
80
			try {
81
				distListener.buttonClick(classification, term);
82
			} catch (SQLException e) {
83
				// TODO Auto-generated catch block
84
				e.printStackTrace();
85
			}
80 86
		}
81 87
	}
82 88

  
src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/DistributionTableView.java
1 1
package eu.etaxonomy.cdm.vaadin.view.dbstatus;
2 2

  
3
import java.sql.SQLException;
3 4
import java.util.ArrayList;
4 5
import java.util.Arrays;
5 6
import java.util.HashMap;
......
12 13
import com.vaadin.data.Container;
13 14
import com.vaadin.data.Property;
14 15
import com.vaadin.data.Property.ValueChangeListener;
15
import com.vaadin.data.util.BeanItem;
16 16
import com.vaadin.data.util.BeanItemContainer;
17
import com.vaadin.event.ItemClickEvent;
18
import com.vaadin.event.ItemClickEvent.ItemClickListener;
19 17
import com.vaadin.event.ShortcutAction.KeyCode;
20 18
import com.vaadin.event.ShortcutAction.ModifierKey;
21 19
import com.vaadin.navigator.View;
......
46 44
import eu.etaxonomy.cdm.model.taxon.Taxon;
47 45
import eu.etaxonomy.cdm.model.taxon.TaxonNode;
48 46
import eu.etaxonomy.cdm.vaadin.component.HorizontalToolbar;
49
import eu.etaxonomy.cdm.vaadin.model.CdmTaxonTableCollection;
50
import eu.etaxonomy.cdm.vaadin.model.LazyLoadedContainer;
47
import eu.etaxonomy.cdm.vaadin.container.CdmSQLContainer;
51 48

  
52 49
public class DistributionTableView extends CustomComponent implements IDistributionTableComponent, View{
53 50

  
......
125 122

  
126 123

  
127 124
    
128
	public void dataBinding(){
125
	public void dataBinding() throws SQLException{
129 126

  
130 127
//		container.addNestedContainerProperty("dDTO.status");
131
		final LazyLoadedContainer container = listener.getLazyLoadedContainer();
132
		Set<DefinedTermBase> chosenTerms = listener.getChosenTerms();
133
		generateDsitributionColumn(chosenTerms);
128
//		final LazyLoadedContainer container = listener.getLazyLoadedContainer();
129
//		Set<DefinedTermBase> chosenTerms = listener.getChosenTerms();
130
//		generateDsitributionColumn(chosenTerms);
131
		
132
		CdmSQLContainer container = listener.getSQLContainer();
134 133
		
135 134
		table_1.setContainerDataSource(container);
136 135
		table_1.setColumnReorderingAllowed(true);
137
		table_1.setSortEnabled(false);
136
		table_1.setSortEnabled(true);
138 137
//		setVisibleColumns(columns);
139
		Object[] visibleColumns = columnList.toArray();
140
		table_1.setVisibleColumns(visibleColumns);
141
		table_1.setColumnHeaders(headerList.toArray(new String[headerList.size()]));//new String[]{"Taxon", "Rang"});// ,"Deutschland"
138
//		Object[] visibleColumns = columnList.toArray();
139
//		table_1.setVisibleColumns(visibleColumns);
140
//		table_1.setColumnHeaders(headerList.toArray(new String[headerList.size()]));//new String[]{"Taxon", "Rang"});// ,"Deutschland"
141
		
142 142
		table_1.setColumnCollapsingAllowed(true);
143 143
		table_1.setSelectable(true);
144 144
//		table_1.setSizeUndefined();
145 145
//		setSizeFull();
146 146
		table_1.setPageLength(20);
147 147
		table_1.setFooterVisible(true);
148
		table_1.setColumnFooter("fullTitleCache", "Total amount of Taxa displayed: " + container.size());
148
		table_1.setColumnFooter("Taxon", "Total amount of Taxa displayed: " + container.size());
149 149

  
150 150
		table_1.setCacheRate(10);
151 151
	}
......
389 389
			}
390 390
		});
391 391

  
392
		table_1.addItemClickListener(new ItemClickListener() {
393

  
394
			@Override
395
			public void itemClick(ItemClickEvent event) {
396
				Object taxonbean = ((BeanItem<?>)event.getItem()).getBean();
397
				if(taxonbean instanceof CdmTaxonTableCollection){
398
					CdmTaxonTableCollection red = (CdmTaxonTableCollection) taxonbean;
399
					currentTaxon = red.getTaxon();
400
				}
401
			}
402
		});
392
		//FIXME with new SQL CONTAINER
393
//		table_1.addItemClickListener(new ItemClickListener() {
394
//
395
//			@Override
396
//			public void itemClick(ItemClickEvent event) {
397
//				Object taxonbean = ((BeanItem<?>)event.getItem()).getBean();
398
//				if(taxonbean instanceof CdmTaxonTableCollection){
399
//					CdmTaxonTableCollection red = (CdmTaxonTableCollection) taxonbean;
400
//					currentTaxon = red.getTaxon();
401
//				}
402
//			}
403
//		});
403 404
	}
404 405

  
405 406
}
src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionSelectionComponent.java
1 1
package eu.etaxonomy.cdm.vaadin.view.dbstatus;
2 2

  
3
import java.sql.SQLException;
3 4
import java.util.List;
4 5

  
5 6
import eu.etaxonomy.cdm.model.common.DefinedTermBase;
......
8 9

  
9 10
public interface IDistributionSelectionComponent {
10 11
	public interface DistributionSelectionComponentListener{
11
		void buttonClick(Classification classification, TermVocabulary<DefinedTermBase> term);
12
		void buttonClick(Classification classification, TermVocabulary<DefinedTermBase> term) throws SQLException;
12 13
		
13 14
		List<Classification> getClassificationList();
14 15
		
src/main/java/eu/etaxonomy/cdm/vaadin/view/dbstatus/IDistributionTableComponent.java
1 1
package eu.etaxonomy.cdm.vaadin.view.dbstatus;
2 2

  
3
import java.sql.SQLException;
3 4
import java.util.HashMap;
4 5
import java.util.List;
5 6
import java.util.Set;
......
21 22
import eu.etaxonomy.cdm.model.taxon.Classification;
22 23
import eu.etaxonomy.cdm.model.taxon.Taxon;
23 24
import eu.etaxonomy.cdm.model.taxon.TaxonNode;
25
import eu.etaxonomy.cdm.vaadin.container.CdmSQLContainer;
24 26
import eu.etaxonomy.cdm.vaadin.model.DbTableDTOS;
25 27
import eu.etaxonomy.cdm.vaadin.model.LazyLoadedContainer;
26 28

  
......
64 66

  
65 67
		List<TaxonNode> getAllNodes(int start, int end);
66 68

  
69
		CdmSQLContainer getSQLContainer() throws SQLException;
70

  
67 71
	}
68 72
	public void addListener(DistributionTableComponentListener listener);
69 73
}

Also available in: Unified diff

Add picture from clipboard (Maximum size: 40 MB)