org.eclipse.osbp.utils.ui/src/org/eclipse/osbp/utils/vaadin/MDXDialog.java - osbp/org.eclipse.osbp.utils.ui - Git at Google

 /**
  *
  *  Copyright (c) 2011, 2016 - Loetz GmbH&Co.KG (69115 Heidelberg, Germany)
  *
  *  All rights reserved. This program and the accompanying materials
  *  are made available under the terms of the Eclipse Public License 2.0
  *  which accompanies this distribution, and is available at
  *  https://www.eclipse.org/legal/epl-2.0/
  *
  *  SPDX-License-Identifier: EPL-2.0
  *
  *  Contributors:
  * 	   Christophe Loetz (Loetz GmbH&Co.KG) - initial implementation
  *
  */
 package org.eclipse.osbp.utils.vaadin;

 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.ArrayList;
 import java.util.List;
 import java.util.Locale;

 import org.eclipse.e4.core.contexts.IEclipseContext;
 import org.eclipse.osbp.core.api.persistence.IPersistenceService;
 import org.eclipse.osbp.mondrian.api.IMondrianService;
 import org.eclipse.osbp.ui.api.metadata.IDSLMetadataService;
 import org.eclipse.osbp.ui.api.user.IUser;
 import org.eclipse.osbp.xtext.datamart.common.olap.DerivedAxis;
 import org.eclipse.osbp.xtext.datamart.common.olap.DerivedCell;
 import org.eclipse.osbp.xtext.datamart.common.olap.DerivedCellSet;
 import org.eclipse.osbp.xtext.datamart.common.olap.DerivedMember;
 import org.eclipse.osbp.xtext.datamart.common.olap.DerivedPosition;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;

 import com.vaadin.data.Item;
 import com.vaadin.server.ClientConnector.DetachListener;
 import com.vaadin.ui.Button;
 import com.vaadin.ui.Component;
 import com.vaadin.ui.TabSheet;
 import com.vaadin.ui.Table;
 import com.vaadin.ui.Table.RowHeaderMode;
 import com.vaadin.ui.TextArea;
 import com.vaadin.ui.VerticalLayout;
 import com.vaadin.ui.Window;

 import mondrian.olap4j.MondrianOlap4jDriver;
 import mondrian.rolap.RolapConnection;

 public class MDXDialog extends Window implements DetachListener { // NOSONAR
 	private static final String MEMBER_TITLE = "member";
 	/**
 	 *
 	 */
 	private static final long serialVersionUID = 3461161019240158996L;
 	private static final Logger LOGGER = LoggerFactory.getLogger(MDXDialog.class);
 	private TextArea input;
 	private Button button;
 	private Component tableLayout;
 	private String messageText = "MDX Query";
 	private String buttonText = "execute";
 	private String areaText = "Statement";
 	private String sampleText = "select Non Empty [Measures].[Salary] on columns,Non Empty [Employees].[Employee].Members on rows from Payroll";
 	private transient IDSLMetadataService dslMetadataService;
 	private transient RolapConnection connection;
 	private transient IPersistenceService persistenceService;
 	private transient IMondrianService mondrianService;
 	private transient IUser user;
 	private VerticalLayout subLayout;
 	private ArrayList<Integer> coordinateSystem = new ArrayList<>();

 	public MDXDialog(IEclipseContext eclipseContext) {
 		super();
 		addDetachListener(this);
 		dslMetadataService = eclipseContext.get(IDSLMetadataService.class);
 		persistenceService = eclipseContext.get(IPersistenceService.class);
 		user = eclipseContext.get(IUser.class);
 		mondrianService = eclipseContext.get(IMondrianService.class);

 		setClosable(true);
 		setModal(false);
 		VerticalLayout subContent = new VerticalLayout();
 		subContent.setMargin(true);
 		setContent(subContent);
 		setCaption(messageText);
 		subContent.setSpacing(true);
 		subLayout = new VerticalLayout();
 		subContent.addComponent(subLayout);

 		input = new TextArea(areaText);
 		input.setRows(6);
 		input.setWordwrap(true);
 		input.setSizeFull();
 		input.setValue(sampleText);
 		input.setWidth("600px");
 		input.setHeight("150px");

 		subLayout.addComponent(input);
 		button = new Button(buttonText);
 		button.addClickListener(e -> executeQuery());
 		subLayout.addComponent(button);
 		center();
 		connect();
 	}

 	@Override
 	public void setLocale(Locale locale) {
 		super.setLocale(locale);
 		setCaption(dslMetadataService.translate(locale.toLanguageTag(), messageText));
 	}

 	private void connect() {
 		if (connection == null) {
 			LOGGER.debug("{}", "connect MDX");
 			try {
 				// for simplicity we only take the first entity used in a cube definition to establish connection
 				connection = persistenceService.getMondrianConnection(mondrianService.getPersistenceUnit(),
 						mondrianService.getPackageName());
 			} catch (SQLException e) {
 				LOGGER.error("{}", e);
 			}
 		}
 	}

 	private void disconnect() {
 		LOGGER.debug("{}", "disconnect MDX");
 		if (connection != null) {
 			connection.close();
 			connection = null;
 		}
 	}

 	private void executeQuery() {
 		LOGGER.debug("{}", "execute MDX");
 		if (tableLayout != null) {
 			subLayout.removeComponent(tableLayout);
 		}
 		RolapConnection rolapConnection = connection;
 		Connection connection;
 		// (JCD) : temporarily disabled to avoid errors with mondrian 4
 //		try {
 //			connection = new MondrianOlap4jDriver().connect(rolapConnection);
 //			Statement statement = connection.createStatement();
 //			ResultSet result = statement.executeQuery(input.getValue());
 //		} catch (SQLException e) {
 //			// TODO Auto-generated catch block
 //			e.printStackTrace();
 //		}
 		DerivedCellSet cellSet = new DerivedCellSet(persistenceService.sendQuery(this.connection, input.getValue()),
 				dslMetadataService, user);
 		coordinateSystem.clear();
 		for (int axis = 0; axis < cellSet.getAxes().size(); axis++) {
 			coordinateSystem.add(0);
 		}
 		tableLayout = createTabSheet(cellSet, cellSet.getAxes().size());
 		subLayout.addComponent(tableLayout);
 	}

 	private Component createTabSheet(DerivedCellSet cellSet, final Integer axisNo) {
 		Component component = null;
 		if (axisNo == 2) {
 			component = createTable(cellSet);
 		} else {
 			Integer axis = axisNo - 1;
 			TabSheet tabsheet = new TabSheet();
 			tabsheet.setSizeFull();
 			DerivedAxis tabAxis = cellSet.getAxes().get(axis);
 			int tabNo = 0;
 			for (DerivedPosition column : tabAxis.getPositions()) {
 				String title = null;
 				for (DerivedMember member : column.getMembers()) {
 					if (title == null) {
 						title = member.getCaption();
 					} else {
 						title += " / " + member.getCaption();
 					}
 				}
 				coordinateSystem.set(axis, tabNo);
 				component = createTabSheet(cellSet, axis);
 				if (component != null) {
 					component.setCaption(title);
 					tabsheet.addComponent(component);
 				}
 				tabNo++;
 			}
 			component = tabsheet;
 		}
 		return component;
 	}

 	private Component createTable(DerivedCellSet cellSet) {
 	    List<Integer> coordinate = new ArrayList<>(coordinateSystem);
 		VerticalLayout container = new VerticalLayout();
 		Table table = new Table();
 		table.setImmediate(true);
 		table.setMultiSelect(false);
 		table.setSelectable(true);
 		table.setRowHeaderMode(RowHeaderMode.HIDDEN);
 		table.setSizeFull();
 		container.addComponent(table);

 		if(cellSet.getAxes().size() >= 2 && !cellSet.getAxes().get(1).getPositions().isEmpty()) {
 			renderTable(cellSet, coordinate, table);
 		}
 		return container;
 	}

 	private void renderTable(DerivedCellSet cellSet, List<Integer> coordinate, Table table) {
 		for (int memCnt = 0; memCnt < cellSet.getAxes().get(1).getPositions().iterator().next().getMembers()
 				.size(); memCnt++) {
 			String title = MEMBER_TITLE + memCnt;
 			table.addContainerProperty(title, String.class, null);
 		}

 		for (DerivedPosition pos : cellSet.getAxes().get(0).getPositions()) {
 			String title = null;
 			for (DerivedMember member : pos.getMembers()) {
 				if (title == null) {
 					title = member.getCaption();
 				} else {
 					title += " " + member.getCaption();
 				}
 			}
 			if (title != null) {
 				table.addContainerProperty(title, Double.class, null);
 			}
 		}

 		int rowsNo = 0;
 		for (DerivedPosition rowsPos : cellSet.getAxes().get(1).getPositions()) {
 			Object newItem = table.addItem();
 			int memCnt = 0;
 			for (DerivedMember member : rowsPos.getMembers()) {
 				String coltitle = MEMBER_TITLE + memCnt;
 				Item r = table.getItem(newItem);
 				r.getItemProperty(coltitle).setValue(member.getUniqueName());
 				memCnt++;
 			}
 			coordinate.set(1, rowsNo);
 			int columnsNo = 0;
 			for (DerivedPosition columnsPos : cellSet.getAxes().get(0).getPositions()) {
 				coordinate.set(0, columnsNo);
 				Object value = null;
 				DerivedCell cell = cellSet.getCell(coordinate);
 				if (cell != null) {
 					if (cell.getValue() == null) {
 						value = 0.0;
 					} else {
 						value = cell.getValue();
 					}
 				}
 				String title = null;
 				for (DerivedMember member : columnsPos.getMembers()) {
 					if (title == null) {
 						title = member.getCaption();
 					} else {
 						title += " " + member.getCaption();
 					}
 				}
 				Item r = table.getItem(newItem);
 				r.getItemProperty(title).setValue(value);
 				columnsNo++;
 			}
 			rowsNo++;
 		}
 	}

 	@Override
 	public void detach(DetachEvent event) {
 		disconnect();
 	}
 }
	/**
	*
	* Copyright (c) 2011, 2016 - Loetz GmbH&Co.KG (69115 Heidelberg, Germany)
	*
	* All rights reserved. This program and the accompanying materials
	* are made available under the terms of the Eclipse Public License 2.0
	* which accompanies this distribution, and is available at
	* https://www.eclipse.org/legal/epl-2.0/
	*
	* SPDX-License-Identifier: EPL-2.0
	*
	* Contributors:
	* Christophe Loetz (Loetz GmbH&Co.KG) - initial implementation
	*
	*/
	package org.eclipse.osbp.utils.vaadin;

	import java.sql.Connection;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.ArrayList;
	import java.util.List;
	import java.util.Locale;

	import org.eclipse.e4.core.contexts.IEclipseContext;
	import org.eclipse.osbp.core.api.persistence.IPersistenceService;
	import org.eclipse.osbp.mondrian.api.IMondrianService;
	import org.eclipse.osbp.ui.api.metadata.IDSLMetadataService;
	import org.eclipse.osbp.ui.api.user.IUser;
	import org.eclipse.osbp.xtext.datamart.common.olap.DerivedAxis;
	import org.eclipse.osbp.xtext.datamart.common.olap.DerivedCell;
	import org.eclipse.osbp.xtext.datamart.common.olap.DerivedCellSet;
	import org.eclipse.osbp.xtext.datamart.common.olap.DerivedMember;
	import org.eclipse.osbp.xtext.datamart.common.olap.DerivedPosition;
	import org.slf4j.Logger;
	import org.slf4j.LoggerFactory;

	import com.vaadin.data.Item;
	import com.vaadin.server.ClientConnector.DetachListener;
	import com.vaadin.ui.Button;
	import com.vaadin.ui.Component;
	import com.vaadin.ui.TabSheet;
	import com.vaadin.ui.Table;
	import com.vaadin.ui.Table.RowHeaderMode;
	import com.vaadin.ui.TextArea;
	import com.vaadin.ui.VerticalLayout;
	import com.vaadin.ui.Window;

	import mondrian.olap4j.MondrianOlap4jDriver;
	import mondrian.rolap.RolapConnection;

	public class MDXDialog extends Window implements DetachListener { // NOSONAR
	private static final String MEMBER_TITLE = "member";
	/**
	*
	*/
	private static final long serialVersionUID = 3461161019240158996L;
	private static final Logger LOGGER = LoggerFactory.getLogger(MDXDialog.class);
	private TextArea input;
	private Button button;
	private Component tableLayout;
	private String messageText = "MDX Query";
	private String buttonText = "execute";
	private String areaText = "Statement";
	private String sampleText = "select Non Empty [Measures].[Salary] on columns,Non Empty [Employees].[Employee].Members on rows from Payroll";
	private transient IDSLMetadataService dslMetadataService;
	private transient RolapConnection connection;
	private transient IPersistenceService persistenceService;
	private transient IMondrianService mondrianService;
	private transient IUser user;
	private VerticalLayout subLayout;
	private ArrayList<Integer> coordinateSystem = new ArrayList<>();

	public MDXDialog(IEclipseContext eclipseContext) {
	super();
	addDetachListener(this);
	dslMetadataService = eclipseContext.get(IDSLMetadataService.class);
	persistenceService = eclipseContext.get(IPersistenceService.class);
	user = eclipseContext.get(IUser.class);
	mondrianService = eclipseContext.get(IMondrianService.class);

	setClosable(true);
	setModal(false);
	VerticalLayout subContent = new VerticalLayout();
	subContent.setMargin(true);
	setContent(subContent);
	setCaption(messageText);
	subContent.setSpacing(true);
	subLayout = new VerticalLayout();
	subContent.addComponent(subLayout);

	input = new TextArea(areaText);
	input.setRows(6);
	input.setWordwrap(true);
	input.setSizeFull();
	input.setValue(sampleText);
	input.setWidth("600px");
	input.setHeight("150px");

	subLayout.addComponent(input);
	button = new Button(buttonText);
	button.addClickListener(e -> executeQuery());
	subLayout.addComponent(button);
	center();
	connect();
	}

	@Override
	public void setLocale(Locale locale) {
	super.setLocale(locale);
	setCaption(dslMetadataService.translate(locale.toLanguageTag(), messageText));
	}

	private void connect() {
	if (connection == null) {
	LOGGER.debug("{}", "connect MDX");
	try {
	// for simplicity we only take the first entity used in a cube definition to establish connection
	connection = persistenceService.getMondrianConnection(mondrianService.getPersistenceUnit(),
	mondrianService.getPackageName());
	} catch (SQLException e) {
	LOGGER.error("{}", e);
	}
	}
	}

	private void disconnect() {
	LOGGER.debug("{}", "disconnect MDX");
	if (connection != null) {
	connection.close();
	connection = null;
	}
	}

	private void executeQuery() {
	LOGGER.debug("{}", "execute MDX");
	if (tableLayout != null) {
	subLayout.removeComponent(tableLayout);
	}
	RolapConnection rolapConnection = connection;
	Connection connection;
	// (JCD) : temporarily disabled to avoid errors with mondrian 4
	// try {
	// connection = new MondrianOlap4jDriver().connect(rolapConnection);
	// Statement statement = connection.createStatement();
	// ResultSet result = statement.executeQuery(input.getValue());
	// } catch (SQLException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// }
	DerivedCellSet cellSet = new DerivedCellSet(persistenceService.sendQuery(this.connection, input.getValue()),
	dslMetadataService, user);
	coordinateSystem.clear();
	for (int axis = 0; axis < cellSet.getAxes().size(); axis++) {
	coordinateSystem.add(0);
	}
	tableLayout = createTabSheet(cellSet, cellSet.getAxes().size());
	subLayout.addComponent(tableLayout);
	}

	private Component createTabSheet(DerivedCellSet cellSet, final Integer axisNo) {
	Component component = null;
	if (axisNo == 2) {
	component = createTable(cellSet);
	} else {
	Integer axis = axisNo - 1;
	TabSheet tabsheet = new TabSheet();
	tabsheet.setSizeFull();
	DerivedAxis tabAxis = cellSet.getAxes().get(axis);
	int tabNo = 0;
	for (DerivedPosition column : tabAxis.getPositions()) {
	String title = null;
	for (DerivedMember member : column.getMembers()) {
	if (title == null) {
	title = member.getCaption();
	} else {
	title += " / " + member.getCaption();
	}
	}
	coordinateSystem.set(axis, tabNo);
	component = createTabSheet(cellSet, axis);
	if (component != null) {
	component.setCaption(title);
	tabsheet.addComponent(component);
	}
	tabNo++;
	}
	component = tabsheet;
	}
	return component;
	}

	private Component createTable(DerivedCellSet cellSet) {
	List<Integer> coordinate = new ArrayList<>(coordinateSystem);
	VerticalLayout container = new VerticalLayout();
	Table table = new Table();
	table.setImmediate(true);
	table.setMultiSelect(false);
	table.setSelectable(true);
	table.setRowHeaderMode(RowHeaderMode.HIDDEN);
	table.setSizeFull();
	container.addComponent(table);

	if(cellSet.getAxes().size() >= 2 && !cellSet.getAxes().get(1).getPositions().isEmpty()) {
	renderTable(cellSet, coordinate, table);
	}
	return container;
	}

	private void renderTable(DerivedCellSet cellSet, List<Integer> coordinate, Table table) {
	for (int memCnt = 0; memCnt < cellSet.getAxes().get(1).getPositions().iterator().next().getMembers()
	.size(); memCnt++) {
	String title = MEMBER_TITLE + memCnt;
	table.addContainerProperty(title, String.class, null);
	}

	for (DerivedPosition pos : cellSet.getAxes().get(0).getPositions()) {
	String title = null;
	for (DerivedMember member : pos.getMembers()) {
	if (title == null) {
	title = member.getCaption();
	} else {
	title += " " + member.getCaption();
	}
	}
	if (title != null) {
	table.addContainerProperty(title, Double.class, null);
	}
	}

	int rowsNo = 0;
	for (DerivedPosition rowsPos : cellSet.getAxes().get(1).getPositions()) {
	Object newItem = table.addItem();
	int memCnt = 0;
	for (DerivedMember member : rowsPos.getMembers()) {
	String coltitle = MEMBER_TITLE + memCnt;
	Item r = table.getItem(newItem);
	r.getItemProperty(coltitle).setValue(member.getUniqueName());
	memCnt++;
	}
	coordinate.set(1, rowsNo);
	int columnsNo = 0;
	for (DerivedPosition columnsPos : cellSet.getAxes().get(0).getPositions()) {
	coordinate.set(0, columnsNo);
	Object value = null;
	DerivedCell cell = cellSet.getCell(coordinate);
	if (cell != null) {
	if (cell.getValue() == null) {
	value = 0.0;
	} else {
	value = cell.getValue();
	}
	}
	String title = null;
	for (DerivedMember member : columnsPos.getMembers()) {
	if (title == null) {
	title = member.getCaption();
	} else {
	title += " " + member.getCaption();
	}
	}
	Item r = table.getItem(newItem);
	r.getItemProperty(title).setValue(value);
	columnsNo++;
	}
	rowsNo++;
	}
	}

	@Override
	public void detach(DetachEvent event) {
	disconnect();
	}
	}