| package org.eclipse.osbp.authentication.account.datamarts; |
| |
| import com.vaadin.ui.Notification; |
| import java.math.BigDecimal; |
| import java.sql.Connection; |
| import java.sql.Date; |
| import java.sql.DriverManager; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.sql.Timestamp; |
| import java.util.ArrayList; |
| import java.util.HashMap; |
| import java.util.LinkedHashMap; |
| import java.util.List; |
| import java.util.Locale; |
| import java.util.Map; |
| import java.util.ResourceBundle; |
| import org.eclipse.osbp.bpm.api.BPMTaskSummary; |
| import org.eclipse.osbp.core.api.persistence.IPersistenceService; |
| import org.eclipse.osbp.dsl.common.datatypes.IDto; |
| import org.eclipse.osbp.preferences.ProductConfiguration; |
| import org.eclipse.osbp.runtime.common.event.IDualData; |
| import org.eclipse.osbp.runtime.common.historized.UUIDHist; |
| import org.eclipse.osbp.runtime.common.i18n.ITranslator; |
| import org.eclipse.osbp.ui.api.datamart.DatamartData; |
| import org.eclipse.osbp.ui.api.datamart.DatamartFilter; |
| import org.eclipse.osbp.ui.api.datamart.DatamartPrimary; |
| import org.eclipse.osbp.ui.api.datamart.IDataMart; |
| import org.eclipse.osbp.ui.api.date.SimpleDateFormatter; |
| import org.eclipse.osbp.ui.api.user.IUser; |
| import org.eclipse.osbp.user.User; |
| import org.eclipse.osbp.xtext.datamart.common.AEntityDatamart; |
| import org.eclipse.osbp.xtext.datamart.common.DatamartDtoMapper; |
| import org.eclipse.osbp.xtext.datamart.common.olap.DerivedAxis; |
| 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.eclipse.osbp.xtext.datamart.common.sql.SqlCellSet; |
| import org.junit.Test; |
| import org.olap4j.Axis; |
| import org.osgi.service.component.annotations.ReferenceCardinality; |
| import org.osgi.service.component.annotations.ReferencePolicy; |
| import org.slf4j.Logger; |
| |
| @SuppressWarnings("serial") |
| public class UserAccountDatamart extends AEntityDatamart { |
| private static Logger log = org.slf4j.LoggerFactory.getLogger("datamarts"); |
| |
| private IUser user; |
| |
| private Map<String, Map<String, String>> dateFilterAttributeProperties = new LinkedHashMap<String, Map<String, String>>(); |
| |
| private String statement = "select UserAccount.USER_NAME as \"userName\",UserAccount.EMAIL as \"email\",UserGroup_userGroup.USER_GROUP_NAME as \"userGroupName\",UserAccount.POSITION as \"position\",UserAccount.PROFILEIMAGE as \"profileimage\",UserAccount.NOT_REGISTERED as \"notRegistered\",UserGroup_userGroup.id as \"__UserGroup__ID__\",UserAccount.id as \"__UserAccount__ID__\" from USER_ACCOUNT UserAccount left join USER_GROUP UserGroup_userGroup on(UserGroup_userGroup.id=UserAccount.USER_GROUP_ID)"; |
| |
| private DatamartDtoMapper datamartDtoMapper = new DatamartDtoMapper() |
| .add("org.eclipse.osbp.authentication.account.dtos.UserGroupDto", "id", EType.NONE, "__UserGroup__ID__") |
| .add("org.eclipse.osbp.authentication.account.dtos.UserAccountDto", "id", EType.NONE, "__UserAccount__ID__") |
| ; |
| |
| private Connection connection = null; |
| |
| private Map<Integer, ArrayList<String>> axisMap = new LinkedHashMap<Integer,ArrayList<String>>() {{ |
| put(0,new ArrayList<String>() {{ |
| add("userName"); |
| add("email"); |
| add("userGroupName"); |
| add("position"); |
| add("profileimage"); |
| add("notRegistered"); |
| add("__UserGroup__ID__"); |
| add("__UserAccount__ID__"); |
| }}); |
| }}; |
| |
| private Map<String, String> aliasMap = new LinkedHashMap<String,String>() {{ |
| put("userName","org.eclipse.osbp.authentication.account.entities.UserAccount.userName"); |
| put("email","org.eclipse.osbp.authentication.account.entities.UserAccount.email"); |
| put("position","org.eclipse.osbp.authentication.account.entities.UserAccount.position"); |
| put("profileimage","org.eclipse.osbp.authentication.account.entities.UserAccount.profileimage"); |
| put("notRegistered","org.eclipse.osbp.authentication.account.entities.UserAccount.notRegistered"); |
| put("userGroupName","org.eclipse.osbp.authentication.account.entities.UserGroup.userGroupName"); |
| }}; |
| |
| private Map<String, IDataMart.AttributeVisibility> hiddenMap = new LinkedHashMap<String,AttributeVisibility>() {{ |
| }}; |
| |
| private Map<String, IDataMart.EType> idMap = new LinkedHashMap<String,EType>() {{ |
| put("__UserGroup__ID__", EType.STRING); |
| put("__UserAccount__ID__", EType.STRING); |
| }}; |
| |
| private Map<String, DatamartPrimary> primaryList = new LinkedHashMap<String, DatamartPrimary>() {{ |
| put("__UserGroup__ID__", new DatamartPrimary("__UserGroup__ID__", "id", "org.eclipse.osbp.authentication.account.entities.UserGroup", false)); |
| put("__UserAccount__ID__", new DatamartPrimary("__UserAccount__ID__", "id", "org.eclipse.osbp.authentication.account.entities.UserAccount", false)); |
| }}; |
| |
| private Map<String, IDataMart.EType> typesMap = new LinkedHashMap<String,EType>() {{ |
| put("UserGroup.userGroupName", EType.STRING); |
| put("UserAccount.userName", EType.STRING); |
| put("UserAccount.email", EType.STRING); |
| put("UserAccount.position", EType.STRING); |
| put("UserAccount.profileimage", EType.BLOPMAPPING); |
| put("UserAccount.notRegistered", EType.BOOLEAN); |
| }}; |
| |
| private Map<String, String> typesProp = new LinkedHashMap<String, String>() {{ |
| put("position", ""); |
| put("profileimage", "2"); |
| }}; |
| |
| private Map<String, String> resultAttributes = new LinkedHashMap<String,String>(); |
| |
| private boolean moreToLoad = false; |
| |
| public Map<String, IDataMart.EType> getIdMap() { |
| return this.idMap; |
| } |
| |
| public Map<String, DatamartPrimary> getPrimaryList() { |
| return this.primaryList; |
| } |
| |
| public Map<Integer, ArrayList<String>> getAxisMap() { |
| return this.axisMap; |
| } |
| |
| public Map<String, String> getAliasMap() { |
| return this.aliasMap; |
| } |
| |
| public Map<String, IDataMart.AttributeVisibility> getHiddenMap() { |
| return this.hiddenMap; |
| } |
| |
| public Map<String, String> getTypesProp() { |
| return this.typesProp; |
| } |
| |
| public IUser getUser() { |
| return this.user; |
| } |
| |
| public void setUser(final IUser user) { |
| this.user = user; |
| } |
| |
| public List<Object> getPrimaryListKeys(final String columnId) { |
| return primaryList.get(columnId).getKeys(); |
| |
| } |
| |
| public boolean contains(final Object key) { |
| for(String primaryKeyName:idMap.keySet()) { |
| if(primaryList.get(primaryKeyName).getKeys().contains(key)) { |
| return true; |
| } |
| } |
| return false; |
| |
| } |
| |
| protected ArrayList<DatamartFilter> initializeFilterMap() { |
| return new ArrayList<DatamartFilter>() {{ |
| add(new DatamartFilter(DatamartFilter.MultipleType.SINGLE, DatamartFilter.FilterType.BY_ID, "UserGroup_userGroup.id", "UserGroup.id","", false)); |
| add(new DatamartFilter(DatamartFilter.MultipleType.SINGLE, DatamartFilter.FilterType.BY_ID, "UserAccount.id", "UserAccount.id","", false)); |
| }}; |
| |
| } |
| |
| public String getPrimaryFilterId() { |
| return "UserAccount.id"; |
| |
| } |
| |
| @Override |
| public void clearCache() { |
| |
| } |
| |
| @Override |
| public String getResultAttribute(final String attributeName) { |
| return resultAttributes.get(attributeName); |
| } |
| |
| public Map<String, IDataMart.EType> getTypesMap() { |
| return typesMap; |
| |
| } |
| |
| public String getPersistenceUnit() { |
| return "authentication"; |
| |
| } |
| |
| public Connection connect() { |
| if (connection == null) { |
| try { |
| connection = org.eclipse.osbp.authentication.account.datamarts.DatamartsServiceBinder.getPersistenceService().getPersistenceUnitConnection(getPersistenceUnit()); |
| } catch (SQLException e) { |
| log.error("{}", e); |
| return (connection); |
| } |
| } |
| return (connection); |
| } |
| |
| public void disconnect() { |
| if (connection != null) { |
| try { |
| connection.close(); |
| connection = null; |
| } catch (SQLException e) { |
| log.error("{}", e); |
| } |
| } |
| } |
| |
| public boolean isMoreToLoad() { |
| return moreToLoad; |
| } |
| |
| public String insertCondition(final String filteredStatement, final String condition) { |
| int index = filteredStatement.indexOf("order by"); |
| if(index > -1) { |
| return filteredStatement.substring(0, index-1) + condition + filteredStatement.substring(index-1); |
| } else { |
| return filteredStatement + condition; |
| } |
| } |
| |
| public String addUserFilter(final String sql, final String entity) { |
| return addUserFilter( sql, entity, entity ); |
| |
| } |
| |
| public String addUserFilter(final String sql, final String entity, final String entityAlias) { |
| String condition = ""; |
| String retSql=sql; |
| if ( user.getUserFilter() == null ) |
| return retSql; |
| if ( user.getUserFilter().getInFilterMap() != null ) { |
| String idList=""; |
| for ( java.util.Map.Entry<String, List<String>> uf :user.getUserFilter().getInFilterMap().entrySet() ) { |
| if (uf.getKey().contains(entity)) { |
| for ( String entry:uf.getValue() ) { |
| if ( !idList.equals("") ) { |
| idList = idList + ","; |
| } |
| idList = idList + "\'" + entry + "\'" ; |
| } |
| idList="(" + idList + ")"; |
| } |
| } |
| if ( !idList.equals("") ) { |
| condition = " ("+entityAlias+".id in "+idList+") "; |
| } |
| } else if ( user.getUserFilter().getNotFilterMap() != null ) { |
| String idList=""; |
| for ( java.util.Map.Entry<String, List<String>> uf :user.getUserFilter().getNotFilterMap().entrySet() ) { |
| if (uf.getKey().contains(entity)) { |
| for ( String entry:uf.getValue() ) { |
| if ( !idList.equals("") ) { |
| idList = idList + ","; |
| } |
| idList = idList + "\'" + entry + "\'" ; |
| } |
| idList="(" + idList + ")"; |
| } |
| } |
| if ( !idList.equals("") ) { |
| condition = " ("+entityAlias+".id not in "+idList+") "; |
| } |
| } |
| if( condition != "" ) { |
| if ( retSql.toLowerCase().contains( " where " ) ) { |
| retSql = insertCondition(retSql, " and " + condition); |
| } else { |
| retSql = insertCondition(retSql, " where " + condition); |
| } |
| } |
| return retSql; |
| |
| } |
| |
| public void renderFilters() { |
| connect(); |
| if (connection != null) { |
| for (DatamartFilter filter : getFilters()) { |
| if((DatamartFilter.FilterType.BY_ID != filter.getType()) && (DatamartFilter.FilterType.BY_HISTUUID != filter.getType()) && (DatamartFilter.FilterType.BETWEEN != filter.getType()) && (DatamartFilter.FilterType.BETWEEN_DATE != filter.getType())) { |
| filter.setSql(addUserFilter(filter.getSql(), filter.getEntityName() )); |
| try (ResultSet rs = connection.createStatement().executeQuery(getVendorSql(filter))){ |
| if (rs != null) { |
| while (rs.next()) { |
| int type = rs.getMetaData().getColumnType(1); |
| switch (type) { |
| case java.sql.Types.DATE: |
| Date date = rs.getDate(1); |
| if (date != null) { |
| if (user == null) { |
| filter.addItem(date.toString(), vendorConvertToDateSQL( date ), false); |
| } else { |
| filter.addItem(SimpleDateFormatter.getFormat("LONGDATE", user.getLocale()).format(date), vendorConvertToDateSQL( date ), false); |
| } |
| } |
| break; |
| case java.sql.Types.TIMESTAMP: |
| Timestamp timestamp = rs.getTimestamp(1); |
| if (timestamp != null) { |
| if (user == null) { |
| filter.addItem(timestamp.toString(), vendorConvertToTimestampSQL( timestamp ), false); |
| } else { |
| filter.addItem(SimpleDateFormatter.getFormat("LONGDATE", user.getLocale()).format(timestamp), vendorConvertToTimestampSQL( timestamp ), false); |
| } |
| } |
| break; |
| case java.sql.Types.DECIMAL: |
| BigDecimal bigDecimal = rs.getBigDecimal(1); |
| if (bigDecimal != null) { |
| filter.addItem(bigDecimal.toPlainString(), bigDecimal.toPlainString(), false); |
| } |
| break; |
| case java.sql.Types.NUMERIC: |
| case java.sql.Types.DOUBLE: |
| filter.addItem(Double.toString(rs.getDouble(1)), Double.toString(rs.getDouble(1)), false); |
| break; |
| case java.sql.Types.INTEGER: |
| case java.sql.Types.BIGINT: |
| filter.addItem(Integer.toString(rs.getInt(1)), Integer.toString(rs.getInt(1)), false); |
| break; |
| case java.sql.Types.FLOAT: |
| filter.addItem(Float.toString(rs.getFloat(1)), Float.toString(rs.getFloat(1)), false); |
| break; |
| case java.sql.Types.CHAR: |
| case java.sql.Types.NCHAR: |
| case java.sql.Types.NVARCHAR: |
| case java.sql.Types.VARCHAR: |
| case java.sql.Types.LONGVARCHAR: |
| filter.addItem(rs.getString(1), rs.getString(1), true); |
| break; |
| } |
| } |
| } |
| } catch (SQLException e) { |
| log.error("{}", e); |
| } |
| } |
| } |
| } |
| disconnect(); |
| } |
| |
| public String applyFilters() { |
| String filteredStatement = statement; |
| computeConditions(); |
| for (DatamartFilter filter : getFilters()) { |
| if (DatamartFilter.FilterType.BY_ID == filter.getType() || DatamartFilter.FilterType.BY_HISTUUID == filter.getType()) { |
| if(filter.getCondition() != null) { |
| if (!filteredStatement.toLowerCase().contains(" where ")) { |
| if ((" "+filter.getCondition()).toLowerCase().contains("where ")) { |
| filteredStatement = insertCondition(filteredStatement, " "+filter.getCondition()); |
| } |
| else { |
| filteredStatement = insertCondition(filteredStatement, " where "+filter.getCondition()); |
| } |
| } |
| else { |
| if (filter.getCondition().trim().toLowerCase().startsWith("and ") || |
| filter.getCondition().trim().toLowerCase().startsWith("or ") ) { |
| filteredStatement = insertCondition(filteredStatement, filter.getCondition()); |
| } |
| else { |
| filteredStatement = insertCondition(filteredStatement, " and "+filter.getCondition()); |
| } |
| } |
| } |
| } |
| else { |
| if(filter.getCondition() != null) { |
| filteredStatement = filteredStatement.replace(filter.getDecoratedName(), filter.getCondition()); |
| filteredStatement =addUserFilter( filteredStatement, filter.getEntityName(), filter.getAliasName() ); |
| } else { |
| log.debug("condition was null in UserAccount:{}", filteredStatement); |
| return ""; |
| } |
| } |
| } |
| log.debug("UserAccount:"+filteredStatement); |
| String entity=getPrimaryFilterId().split("\\.")[0]; |
| if( entity.contains("_")){ |
| entity=entity.split("\\_")[0]; |
| } |
| filteredStatement = addUserFilter( filteredStatement, entity); |
| return filteredStatement; |
| } |
| |
| public DerivedCellSet getResults() { |
| return getResults(false, null, null); |
| } |
| |
| public DerivedCellSet getResults(final boolean limited, final Class operativeDtoClass, final List<IDto> operativeDtos) { |
| SqlCellSet cellSet = null; |
| java.sql.ResultSetMetaData metaData = null; |
| connect(); |
| if (connection != null) { |
| String query = applyFilters(); |
| if (query.length()>0) { |
| try { |
| log.debug("statement:{} limited:{}", query, limited); |
| Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); |
| if(limited) { |
| stmt.setMaxRows(500); |
| stmt.setFetchSize(500); |
| } |
| ResultSet rs = stmt.executeQuery(query); |
| if(!rs.isClosed()) { |
| metaData = rs.getMetaData(); |
| cellSet = new SqlCellSet(rs, metaData, user, resultAttributes, getAxisMap(), getIdMap(), getAliasMap(), getHiddenMap(), datamartDtoMapper, operativeDtoClass, operativeDtos, getPrimaryList(), org.eclipse.osbp.authentication.account.datamarts.DatamartsServiceBinder.getUserAccessService(), false); |
| moreToLoad = false; |
| if(limited && cellSet.getFetchedRows() == 500) { |
| moreToLoad = true; |
| } |
| disconnect(); |
| return (new DerivedCellSet(cellSet, |
| metaData, |
| org.eclipse.osbp.authentication.account.datamarts.DatamartsServiceBinder.getDSLMetadataService(), user)); |
| } |
| } |
| catch (java.sql.SQLException e) { |
| log.error("Statement: {} error {}", query, e); |
| } |
| } |
| } |
| disconnect(); |
| return null; |
| } |
| |
| @Test |
| public void TestUserAccount() { |
| renderFilters(); |
| org.junit.Assert.assertNotNull(getResults(true, null, null)); |
| } |
| } |