/*
 *                                                                            
 *  Copyright (c) 2013, 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.xtext.datamartdsl.jvmmodel

import java.sql.Connection
import java.util.ArrayList
import java.util.HashSet
import java.util.LinkedHashMap
import java.util.List
import java.util.Map
import java.util.Set
import java.util.stream.Collectors
import javax.inject.Inject
import mondrian.rolap.RolapConnection
import org.eclipse.osbp.bpm.api.IBPMEngine
import org.eclipse.osbp.bpm.api.IBPMTaskClient
import org.eclipse.osbp.core.api.persistence.IPersistenceService
import org.eclipse.osbp.dsl.common.datatypes.IDto
import org.eclipse.osbp.dsl.common.xtext.extensions.AnnotationExtension
import org.eclipse.osbp.dsl.entity.xtext.extensions.ModelExtensions
import org.eclipse.osbp.dsl.entity.xtext.extensions.NamingExtensions
import org.eclipse.osbp.dsl.semantic.common.types.LAttribute
import org.eclipse.osbp.dsl.semantic.common.types.LDataType
import org.eclipse.osbp.dsl.semantic.common.types.LScalarType
import org.eclipse.osbp.dsl.semantic.entity.LBean
import org.eclipse.osbp.dsl.semantic.entity.LEntity
import org.eclipse.osbp.dsl.semantic.entity.LEntityAttribute
import org.eclipse.osbp.dsl.semantic.entity.LEntityFeature
import org.eclipse.osbp.preferences.databases.vendors.DatabaseVendor
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.datamart.IDataMart.AttributeVisibility
import org.eclipse.osbp.ui.api.datamart.IDataMart.EType
import org.eclipse.osbp.ui.api.metadata.IDSLMetadataService
import org.eclipse.osbp.ui.api.user.IUser
import org.eclipse.osbp.ui.api.useraccess.IUserAccessService
import org.eclipse.osbp.utils.common.EntityUtils
import org.eclipse.osbp.utils.entityhelper.DataType
import org.eclipse.osbp.xtext.cubedsl.CubeDimension
import org.eclipse.osbp.xtext.cubedsl.CubePackage
import org.eclipse.osbp.xtext.datamart.common.ACubeDatamart
import org.eclipse.osbp.xtext.datamart.common.AEntityDatamart
import org.eclipse.osbp.xtext.datamart.common.ATaskDatamart
import org.eclipse.osbp.xtext.datamart.common.DatamartDefinitionUtil
import org.eclipse.osbp.xtext.datamart.common.DatamartDtoMapper
import org.eclipse.osbp.xtext.datamart.common.olap.DerivedCellSet
import org.eclipse.osbp.xtext.datamartdsl.Addition
import org.eclipse.osbp.xtext.datamartdsl.AxisEnum
import org.eclipse.osbp.xtext.datamartdsl.ConditionalExpression
import org.eclipse.osbp.xtext.datamartdsl.Conjunction
import org.eclipse.osbp.xtext.datamartdsl.DatamartAggregation
import org.eclipse.osbp.xtext.datamartdsl.DatamartAggregationFunction
import org.eclipse.osbp.xtext.datamartdsl.DatamartAttribute
import org.eclipse.osbp.xtext.datamartdsl.DatamartAttributeBase
import org.eclipse.osbp.xtext.datamartdsl.DatamartCondition
import org.eclipse.osbp.xtext.datamartdsl.DatamartCube
import org.eclipse.osbp.xtext.datamartdsl.DatamartCubeAxis
import org.eclipse.osbp.xtext.datamartdsl.DatamartCubeElement
import org.eclipse.osbp.xtext.datamartdsl.DatamartDSLFactory
import org.eclipse.osbp.xtext.datamartdsl.DatamartDefineDerivedMeasure
import org.eclipse.osbp.xtext.datamartdsl.DatamartDefinition
import org.eclipse.osbp.xtext.datamartdsl.DatamartDerivedMeasure
import org.eclipse.osbp.xtext.datamartdsl.DatamartEntity
import org.eclipse.osbp.xtext.datamartdsl.DatamartFunction
import org.eclipse.osbp.xtext.datamartdsl.DatamartFunctionIntParameter
import org.eclipse.osbp.xtext.datamartdsl.DatamartHierarchy
import org.eclipse.osbp.xtext.datamartdsl.DatamartHierarchyLevelMultiple
import org.eclipse.osbp.xtext.datamartdsl.DatamartHierarchyLevelSingle
import org.eclipse.osbp.xtext.datamartdsl.DatamartMeasure
import org.eclipse.osbp.xtext.datamartdsl.DatamartMember
import org.eclipse.osbp.xtext.datamartdsl.DatamartMemberTuple
import org.eclipse.osbp.xtext.datamartdsl.DatamartNavigation
import org.eclipse.osbp.xtext.datamartdsl.DatamartOrder
import org.eclipse.osbp.xtext.datamartdsl.DatamartOwner
import org.eclipse.osbp.xtext.datamartdsl.DatamartPackage
import org.eclipse.osbp.xtext.datamartdsl.DatamartParameterFunction
import org.eclipse.osbp.xtext.datamartdsl.DatamartReference
import org.eclipse.osbp.xtext.datamartdsl.DatamartReferenceBase
import org.eclipse.osbp.xtext.datamartdsl.DatamartSetAggregation
import org.eclipse.osbp.xtext.datamartdsl.DatamartSetAggregationFunction
import org.eclipse.osbp.xtext.datamartdsl.DatamartSetFunction
import org.eclipse.osbp.xtext.datamartdsl.DatamartSetParameterFunction
import org.eclipse.osbp.xtext.datamartdsl.DatamartSetTuple
import org.eclipse.osbp.xtext.datamartdsl.DatamartSlicer
import org.eclipse.osbp.xtext.datamartdsl.DatamartSource
import org.eclipse.osbp.xtext.datamartdsl.DatamartTask
import org.eclipse.osbp.xtext.datamartdsl.DatamartTaskFilter
import org.eclipse.osbp.xtext.datamartdsl.Disjunction
import org.eclipse.osbp.xtext.datamartdsl.Division
import org.eclipse.osbp.xtext.datamartdsl.Expression
import org.eclipse.osbp.xtext.datamartdsl.Multiplication
import org.eclipse.osbp.xtext.datamartdsl.SetFunctionEnum
import org.eclipse.osbp.xtext.datamartdsl.Subtraction
import org.eclipse.osbp.xtext.datamartdsl.util.DatamartAttributeUtil
import org.eclipse.osbp.xtext.datamartdsl.util.DatamartHierarchyUtil
import org.eclipse.xtext.common.types.JvmAnnotationReference
import org.eclipse.xtext.common.types.JvmDeclaredType
import org.eclipse.xtext.common.types.JvmField
import org.eclipse.xtext.common.types.JvmGenericType
import org.eclipse.xtext.common.types.JvmOperation
import org.eclipse.xtext.common.types.JvmVisibility
import org.eclipse.xtext.naming.IQualifiedNameProvider
import org.eclipse.xtext.xbase.jvmmodel.AbstractModelInferrer
import org.eclipse.xtext.xbase.jvmmodel.IJvmDeclaredTypeAcceptor
import org.eclipse.xtext.xbase.jvmmodel.JvmTypesBuilder
import org.junit.Test
import org.osgi.service.component.annotations.Component
import org.osgi.service.component.annotations.Reference
import org.osgi.service.component.annotations.ReferenceCardinality
import org.osgi.service.component.annotations.ReferencePolicy
import org.slf4j.Logger

public class Triple<S1, S2, S3> {
	var S1 firstElement
	var S2 secondElement
	var S3 thirdElement
	
	new(S1 f, S2 s, S3 t) {
		this.firstElement = f;
		this.secondElement = s;
		this.thirdElement = t;
	}
	
	def S1 getFirst() {
		return firstElement
	}

	def S2 getSecond() {
		return secondElement
	}
	
	def S3 getThird() {
		return thirdElement
	}
	
	override boolean equals(Object other) {
		if (other === null)
			return false
		if (other === this)
			return true
		if (other instanceof Triple<?,?,?>) {
			var r = other as Triple<?,?,?>
			if(firstElement===null) return r.first===null else firstElement.equals(r.first)
			if(secondElement===null) return r.second===null else secondElement.equals(r.second)
			if(thirdElement===null) return r.third===null else thirdElement.equals(r.third)
		}
		return false
	}
	
	override int hashCode() {
		var fhc = 0
		var shc = 0
		var thc = 0
		if(firstElement !== null) fhc = firstElement.hashCode
		if(secondElement !== null) shc = secondElement.hashCode		
		if(thirdElement !== null) thc = thirdElement.hashCode
		return fhc + 17*shc + 31*thc		
	}
	
	override String toString() {
		return "Triple("+first+","+second+","+third+")"
	}
}	
/**
 * <p>
 * This inferrer infers models of extension .datamart and generates code to be used by other dsl. It encapsulates difficult syntax construction
 * and provides a reflective call interface as well as a data transfer object olap cellSet. 
 * There are two different approaches: the traditional SQL thru entities and their attributes and MDX thru cubes and their measures and stuff.
 * For SQL the olap cellSet is emulated by appropriate classes and implements NOT ALL features of a MDX filled olap cellSet.
 * </p>
 * 
 * @author Joerg Riegel
 */
/**
 * <p>Infers a JVM model from the source model.</p> 
 * 
 * <p>The JVM model should contain all elements that would appear in the Java code 
 * which is generated from the source model. Other models link against the JVM model rather than the source model.</p>     
 */
class DatamartDSLJvmModelInferrer extends AbstractModelInferrer {

	/**
	 * convenience API to build and initialize JVM types and their members.
	 */
	@Inject extension JvmTypesBuilder
	@Inject extension IQualifiedNameProvider
	@Inject extension NamingExtensions
	@Inject extension AnnotationExtension
	@Inject extension ModelExtensions
	@Inject extension DatamartHierarchyUtil

	@Inject
	@Extension
	private DataType dtType;

	var sqlFilterMap = <String, DatamartAttributeBase>newLinkedHashMap
	var idMap = <String, LEntity>newLinkedHashMap
	var idEntityAliasMap = <String, String>newLinkedHashMap
	var datamartDtoMapper = null as String
	var ignoreEntityGrouping = false
	var sqlHasAggregate = false
	var sqlHasOrder = false
	var public static String pckgName = null
	var String binderClassName = ""
	var defaultColumnWeight = 500
	var defaultFetchSize = 500

	def void generatePckgName(DatamartPackage pckg, IJvmDeclaredTypeAcceptor acceptor) {
		pckgName = pckg.name
	}

	/**
	 * <p>infer method dispatches the neccessary routines to build fields, setter, getter, constructors and methods of the generated code.</p> 
	 * 
	 */
	def dispatch void infer(DatamartPackage datamart, IJvmDeclaredTypeAcceptor acceptor, boolean isPreIndexingPhase) {
		datamart.generatePckgName(acceptor)

		// the test main class
		var cls = datamart.toClass(datamart.name.toString.concat("TestClass"))
		cls.simpleName = cls.simpleName.toFirstUpper
		acceptor.accept(cls, [
			annotations += _annotationTypesBuilder.annotationRef(SuppressWarnings, "serial")
			packageName = datamart.fullyQualifiedName.toString
			it.toTestCalls(datamart)
			it.fileHeader = datamart.documentation
		])

		// the service binder class
		binderClassName = datamart.fullyQualifiedName.toString + "." +
			datamart.fullyQualifiedName.lastSegment.toFirstUpper + "ServiceBinder"
		cls = datamart.toClass(datamart.name.toString.concat("ServiceBinder"))
		cls.simpleName = cls.simpleName.toFirstUpper
		acceptor.accept(cls, [
			annotations += _annotationTypesBuilder.annotationRef(SuppressWarnings, "serial")
			annotations += _annotationTypesBuilder.annotationRef(typeof(Component))

			packageName = datamart.fullyQualifiedName.toString
			it.fileHeader = datamart.documentation
			it.toBinderFields(datamart)
			it.toBinderOperations(datamart)
		])

		for (definition : datamart.definitions) {
			acceptor.accept(definition.toClass(definition.fullyQualifiedName + IDataMart.DATAMART_CLASS_POSTFIX), [
				annotations += _annotationTypesBuilder.annotationRef(SuppressWarnings, "serial")
				packageName = datamart.fullyQualifiedName.toString
				if (definition.source instanceof DatamartEntity) {
					superTypes += _typeReferenceBuilder.typeRef(AEntityDatamart)
				}
				if (definition.source instanceof DatamartCube) {
					superTypes += _typeReferenceBuilder.typeRef(ACubeDatamart)
				}
				if (definition.source instanceof DatamartTask) {
					superTypes += _typeReferenceBuilder.typeRef(ATaskDatamart)
				}
				it.toFields(definition)
				it.toGetterSetter(definition)
				it.toOperations(definition, datamart)
			])
		}
	}

	def toTestCalls(JvmGenericType type, DatamartPackage datamart) {
		var JvmOperation operation
		for (definition : datamart.definitions) {
			operation = definition.toMethod("test".concat(definition.name), _typeReferenceBuilder.typeRef(Void::TYPE), [
				annotations += _annotationTypesBuilder.annotationRef(Test)
				body = [append('''«definition.getAllTestcalls»''')]
			])
			type.members += operation
		}
	}

	def getAllTestcalls(DatamartDefinition definition) {
		var body = ""
		body = '''
			«body»
			«definition.name»Datamart «definition.name.toFirstLower»Datamart = new «definition.name»Datamart();
			«definition.name.toFirstLower»Datamart.setUser(new User("«IUserAccessService.NAME_ADMINISTRATOR»"));
			«body»«definition.name.toFirstLower»Datamart.Test«definition.name»();
		'''
		return body
	}

	def void toBinderFields(JvmDeclaredType type, DatamartPackage datamart) {
		var JvmField field = null
		// create logger
		field = datamart.toField("log", _typeReferenceBuilder.typeRef(Logger)) [
			setInitializer([append('''org.slf4j.LoggerFactory.getLogger("servicebinder")''')])
		]
		field.static = true
		field.visibility = JvmVisibility::PRIVATE
		type.members += field
		// create persistence service field
		field = datamart.toField("persistenceService", _typeReferenceBuilder.typeRef(IPersistenceService))
		field.static = true
		field.visibility = JvmVisibility::PRIVATE
		type.members += field
		field = datamart.toField("bpmEngine", _typeReferenceBuilder.typeRef(IBPMEngine))
		field.static = true
		field.visibility = JvmVisibility::PRIVATE
		type.members += field
		field = datamart.toField("userAccessService", _typeReferenceBuilder.typeRef(IUserAccessService))
		field.static = true
		field.visibility = JvmVisibility::PRIVATE
		type.members += field
		field = datamart.toField("dslMetadataService", _typeReferenceBuilder.typeRef(IDSLMetadataService))
		field.static = true
		field.visibility = JvmVisibility::PRIVATE
		type.members += field
		field = datamart.toField("taskClient", _typeReferenceBuilder.typeRef(IBPMTaskClient))
		field.static = true
		field.visibility = JvmVisibility::PRIVATE
		type.members += field
	}

	def void toBinderOperations(JvmDeclaredType type, DatamartPackage datamart) {
		type.members += datamart.toMethod("getPersistenceService", _typeReferenceBuilder.typeRef(IPersistenceService), [
			visibility = JvmVisibility.PUBLIC
			static = true
			body = [
				append(
			'''return persistenceService;''')
			]
		])
		type.members += datamart.toMethod("getBpmEngine", _typeReferenceBuilder.typeRef(IBPMEngine), [
			visibility = JvmVisibility.PUBLIC
			static = true
			body = [
				append(
			'''return bpmEngine;''')
			]
		])
		type.members += datamart.toMethod("getUserAccessService", _typeReferenceBuilder.typeRef(IUserAccessService), [
			visibility = JvmVisibility.PUBLIC
			static = true
			body = [
				append(
			'''return userAccessService;''')
			]
		])
		type.members += datamart.toMethod("getDSLMetadataService", _typeReferenceBuilder.typeRef(IDSLMetadataService), [
			visibility = JvmVisibility.PUBLIC
			static = true
			body = [
				append(
			'''return dslMetadataService;''')
			]
		])
		type.members += datamart.toMethod("getTaskClient", _typeReferenceBuilder.typeRef(IBPMTaskClient), [
			visibility = JvmVisibility.PUBLIC
			static = true
			body = [
				append(
			'''return taskClient;''')
			]
		])
		type.members += datamart.toMethod("bindPersistenceMethod", _typeReferenceBuilder.typeRef(Void::TYPE), [
			var annotationRef = _annotationTypesBuilder.annotationRef(typeof(Reference))
			annotationRef.addAnnAttr(datamart, "cardinality", ReferenceCardinality.MANDATORY)
			annotationRef.addAnnAttr(datamart, "policy", ReferencePolicy.STATIC)
			annotations += annotationRef
			visibility = JvmVisibility.PUBLIC
			synchronized = true
			parameters += datamart.toParameter("persistenceService", _typeReferenceBuilder.typeRef(IPersistenceService))
			body = [
				append(
			'''
				«datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».persistenceService = persistenceService;
				log.debug("Datamart PersistenceService bound");''')
			]
		])
		// unbind persistence service
		type.members += datamart.toMethod("unbindPersistenceMethod", _typeReferenceBuilder.typeRef(Void::TYPE), [
			visibility = JvmVisibility.PUBLIC
			synchronized = true
			parameters += datamart.toParameter("persistenceService", _typeReferenceBuilder.typeRef(IPersistenceService))
			body = [
				append(
			'''
				«datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».persistenceService = null;
				log.debug("Datamart PersistenceService unbound");''')
			]
		])
		// bind bpm service
		type.members += datamart.toMethod("bindBPMMethod", _typeReferenceBuilder.typeRef(Void::TYPE), [
			var annotationRef = _annotationTypesBuilder.annotationRef(typeof(Reference))
			annotationRef.addAnnAttr(datamart, "cardinality", ReferenceCardinality.OPTIONAL)
			annotationRef.addAnnAttr(datamart, "policy", ReferencePolicy.DYNAMIC)
			annotations += annotationRef
			visibility = JvmVisibility.PUBLIC
			synchronized = true
			parameters += datamart.toParameter("bpmEngine", _typeReferenceBuilder.typeRef(IBPMEngine))
			body = [
				append(
			'''
				«datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».bpmEngine = bpmEngine;
				log.debug("Datamart BPMEngine bound");''')
			]
		])
		// unbind bpm service
		type.members += datamart.toMethod("unbindBPMMethod", _typeReferenceBuilder.typeRef(Void::TYPE), [
			visibility = JvmVisibility.PUBLIC
			synchronized = true
			parameters += datamart.toParameter("bpmEngine", _typeReferenceBuilder.typeRef(IBPMEngine))
			body = [
				append(
			'''
				«datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».bpmEngine = null;
				log.debug("Datamart BPMEngine unbound");''')
			]
		])
		// bind userAccessService
		type.members += datamart.toMethod("bindUserAccessMethod", _typeReferenceBuilder.typeRef(Void::TYPE), [
			var annotationRef = _annotationTypesBuilder.annotationRef(typeof(Reference))
			annotationRef.addAnnAttr(datamart, "cardinality", ReferenceCardinality.MANDATORY)
			annotationRef.addAnnAttr(datamart, "policy", ReferencePolicy.STATIC)
			annotations += annotationRef
			visibility = JvmVisibility.PUBLIC
			synchronized = true
			parameters += datamart.toParameter("userAccessService", _typeReferenceBuilder.typeRef(IUserAccessService))
			body = [
				append(
			'''
				«datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».userAccessService = userAccessService;
				log.debug("Datamart UserAccessService bound");''')
			]
		])
		// unbind userAccessService
		type.members += datamart.toMethod("unbindUserAccessMethod", _typeReferenceBuilder.typeRef(Void::TYPE), [
			visibility = JvmVisibility.PUBLIC
			synchronized = true
			parameters += datamart.toParameter("userAccessService", _typeReferenceBuilder.typeRef(IUserAccessService))
			body = [
				append(
			'''
				«datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».userAccessService = null;
				log.debug("Datamart UserAccessService unbound");''')
			]
		])
		// bind dslMetadataService
		type.members += datamart.toMethod("bindDSLMetadataService", _typeReferenceBuilder.typeRef(Void::TYPE), [
			var annotationRef = _annotationTypesBuilder.annotationRef(typeof(Reference))
			annotationRef.addAnnAttr(datamart, "cardinality", ReferenceCardinality.MANDATORY)
			annotationRef.addAnnAttr(datamart, "policy", ReferencePolicy.STATIC)
			annotations += annotationRef
			visibility = JvmVisibility.PUBLIC
			synchronized = true
			parameters += datamart.toParameter("dslMetadataService", _typeReferenceBuilder.typeRef(IDSLMetadataService))
			body = [
				append(
			'''
				«datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».dslMetadataService = dslMetadataService;
				log.debug("Datamart DSLMetadataService bound");''')
			]
		])
		// unbind dslMetadataService
		type.members += datamart.toMethod("unbindDSLMetadataService", _typeReferenceBuilder.typeRef(Void::TYPE), [
			visibility = JvmVisibility.PUBLIC
			synchronized = true
			parameters += datamart.toParameter("dslMetadataService", _typeReferenceBuilder.typeRef(IDSLMetadataService))
			body = [
				append(
			'''
				«datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».dslMetadataService = null;
				log.debug("Datamart DSLMetadataService unbound");''')
			]
		])
		// bind taskClient
		type.members += datamart.toMethod("bindTaskClient", _typeReferenceBuilder.typeRef(Void::TYPE), [
			var annotationRef = _annotationTypesBuilder.annotationRef(typeof(Reference))
			annotationRef.addAnnAttr(datamart, "cardinality", ReferenceCardinality.OPTIONAL)
			annotationRef.addAnnAttr(datamart, "policy", ReferencePolicy.DYNAMIC)
			annotations += annotationRef
			visibility = JvmVisibility.PUBLIC
			synchronized = true
			parameters += datamart.toParameter("taskClient", _typeReferenceBuilder.typeRef(IBPMTaskClient))
			body = [
				append(
			'''
				«datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».taskClient = taskClient;
				log.debug("Datamart BPMTaskClient bound");''')
			]
		])
		// unbind taskClient
		type.members += datamart.toMethod("unbindTaskClient", _typeReferenceBuilder.typeRef(Void::TYPE), [
			visibility = JvmVisibility.PUBLIC
			synchronized = true
			parameters += datamart.toParameter("taskClient", _typeReferenceBuilder.typeRef(IBPMTaskClient))
			body = [
				append(
			'''
				«datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».taskClient = null;
				log.debug("Datamart BPMTaskClient unbound");''')
			]
		])
	}

	/**
	 * <p>build the class variables for each datamart definition.</p> 
	 * 
	 */
	def void toFields(JvmDeclaredType type, DatamartDefinition definition) {
		val typesMap = <String, LAttribute>newLinkedHashMap
		val entityMap = <String, DatamartEntity>newLinkedHashMap
		var JvmField field = null
		// create logger
		field = definition.toField("log", _typeReferenceBuilder.typeRef(Logger)) [
			setInitializer([append('''org.slf4j.LoggerFactory.getLogger("datamarts")''')])
		]
		field.static = true
		field.visibility = JvmVisibility::PRIVATE
		type.members += field
		field = definition.toField("user", _typeReferenceBuilder.typeRef(IUser))
		field.visibility = JvmVisibility::PRIVATE
		type.members += field
		if (definition.source instanceof DatamartCube) {
			field = definition.toField("statement", _typeReferenceBuilder.typeRef(String)) [
				setInitializer([append('''"«definition.createMDXStatement»"''')])
			]
			type.members += field
			field = definition.toField("connection", _typeReferenceBuilder.typeRef(RolapConnection)) [
				setInitializer([append('''null''')])
			]
			type.members += field
			field = definition.toField("datamartDtoMapper", _typeReferenceBuilder.typeRef(DatamartDtoMapper)) [
				setInitializer([append('''new DatamartDtoMapper()''')])
			]
			type.members += field
		}
		if (definition.source instanceof DatamartEntity) {
			field = definition.toField("dateFilterAttributeProperties",
				_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
					_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
						_typeReferenceBuilder.typeRef(String)))) [
				setInitializer = [
					append('''new LinkedHashMap<String, Map<String, String>>()«(definition.source as DatamartEntity).createFilterAttributeProperties»''')
				]
			]
			field.visibility = JvmVisibility::PRIVATE
			type.members += field

			field = definition.toField("statement",
				_typeReferenceBuilder.typeRef(String)) [
				setInitializer([
					append('''"«definition.createSQLStatements((definition.source as DatamartEntity), typesMap, entityMap)»"''')
				])
			]
			type.members += field
			field = definition.toField("datamartDtoMapper", _typeReferenceBuilder.typeRef(DatamartDtoMapper)) [
				setInitializer([append('''«datamartDtoMapper»''')])
			]
			type.members += field
			field = definition.toField("connection", _typeReferenceBuilder.typeRef(Connection)) [
				setInitializer([append('''null''')])
			]
			type.members += field
			field = definition.toField("axisMap",
				_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(Integer),
					_typeReferenceBuilder.typeRef(ArrayList, _typeReferenceBuilder.typeRef(String)))) [
				setInitializer([append('''new LinkedHashMap<Integer,ArrayList<String>>() «definition.createAxisMap(entityMap)»''')])
			]
			type.members += field
			field = definition.toField("aliasMap",
				_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
					_typeReferenceBuilder.typeRef(String))) [
				setInitializer([append('''new LinkedHashMap<String,String>() «definition.createAliasMap»''')])
			]
			type.members += field
			field = definition.toField("hiddenMap",
				_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
					_typeReferenceBuilder.typeRef(AttributeVisibility))) [
				setInitializer([append('''new LinkedHashMap<String,AttributeVisibility>() «definition.createHiddenMap»''')])
			]
			type.members += field
		}

		if (definition.source instanceof DatamartTask) {
			field = definition.toField("axisMap",
				_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(Integer),
					_typeReferenceBuilder.typeRef(ArrayList, _typeReferenceBuilder.typeRef(String)))) [
				setInitializer([append('''new LinkedHashMap<Integer,ArrayList<String>>() «definition.createAxisMap(entityMap)»''')])
			]
			type.members += field
			field = definition.toField("datamartDtoMapper", _typeReferenceBuilder.typeRef(DatamartDtoMapper)) [
				setInitializer([append('''new DatamartDtoMapper()''')])
			]
			type.members += field
		}
		// must be made after createSQLStatement
		field = definition.toField("idMap",
			_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
				_typeReferenceBuilder.typeRef(EType))) [
			setInitializer([append('''new LinkedHashMap<String,EType>() «definition.createIdMap»''')])
		]
		type.members += field

		field = definition.toField("primaryList",
			_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String), _typeReferenceBuilder.typeRef(DatamartPrimary))) [
			setInitializer([append('''new LinkedHashMap<String, DatamartPrimary>() «definition.createPrimaryList»''')])
		]
		type.members += field

		field = definition.toField("typesMap",
			_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
				_typeReferenceBuilder.typeRef(EType))) [
			setInitializer([
				append('''new LinkedHashMap<String,EType>() «definition.createTypesMaps(typesMap)»''')
			])
		]
		type.members += field

		field = definition.toField("typesProp",
			_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
				_typeReferenceBuilder.typeRef(String))) [
			setInitializer([
				append('''new LinkedHashMap<String, String>() «definition.createTypesProps(typesMap)»''')
			])
		]
		type.members += field

		field = definition.toField("resultAttributes",
			_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
				_typeReferenceBuilder.typeRef(String)))[setInitializer([append('''new LinkedHashMap<String,String>()''')])]
		type.members += field

		field = definition.toField("moreToLoad", _typeReferenceBuilder.typeRef(boolean))[setInitializer([append('''false''')])]
		type.members += field
	}

	/**
	 * <p>build the getters and setters from class variables of datamart definition.</p> 
	 * 
	 */
	def void toGetterSetter(JvmDeclaredType type, DatamartDefinition definition) {
		var JvmOperation operation = null
		operation = definition.toGetter("idMap",
			_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
				_typeReferenceBuilder.typeRef(EType)))
		operation.visibility = JvmVisibility::PUBLIC
		type.members += operation
		operation = definition.toGetter("primaryList",
			_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String), _typeReferenceBuilder.typeRef(DatamartPrimary)))
		operation.visibility = JvmVisibility::PUBLIC
		type.members += operation
		if (definition.source instanceof DatamartEntity || definition.source instanceof DatamartTask) {
			operation = definition.toGetter("axisMap",
				_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(Integer),
					_typeReferenceBuilder.typeRef(ArrayList, _typeReferenceBuilder.typeRef(String))))
			operation.visibility = JvmVisibility::PUBLIC
			type.members += operation
		}
		if (definition.source instanceof DatamartEntity) {
			operation = definition.toGetter("aliasMap",
				_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
					_typeReferenceBuilder.typeRef(String)))
			operation.visibility = JvmVisibility::PUBLIC
			type.members += operation
			operation = definition.toGetter("hiddenMap",
				_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
					_typeReferenceBuilder.typeRef(AttributeVisibility)))
			operation.visibility = JvmVisibility::PUBLIC
			type.members += operation
		}
		operation = definition.toGetter("typesProp",
			_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
				_typeReferenceBuilder.typeRef(String)))
		operation.visibility = JvmVisibility::PUBLIC
		type.members += operation
		operation = definition.toGetter("user", _typeReferenceBuilder.typeRef(IUser))
		operation.visibility = JvmVisibility::PUBLIC
		type.members += operation
		operation = definition.toSetter("user", _typeReferenceBuilder.typeRef(IUser))
		operation.visibility = JvmVisibility::PUBLIC
		type.members += operation
	}

	/**
	 * <p>build the methods separately for cube, entity and task or SQL and MDX respective.</p> 
	 * 
	 */
	def void toOperations(JvmDeclaredType type, DatamartDefinition definition, DatamartPackage datamart) {
		var JvmOperation operation

		operation = definition.toMethod("getPrimaryListKeys",
			_typeReferenceBuilder.typeRef(List, _typeReferenceBuilder.typeRef(Object))) [
				parameters += definition.toParameter("columnId", _typeReferenceBuilder.typeRef(String))
				body = [
					append('''
						return primaryList.get(columnId).getKeys();
					''')
				]
			]
		operation.visibility = JvmVisibility::PUBLIC
		type.members += operation

		operation = definition.toMethod("contains",
			_typeReferenceBuilder.typeRef(boolean)) [
				parameters += definition.toParameter("key", _typeReferenceBuilder.typeRef(Object))
				body = [
					append('''
						for(String primaryKeyName:idMap.keySet()) {
							if(primaryList.get(primaryKeyName).getKeys().contains(key)) {
								return true;
							}
						}
						return false;
					''')
				]
			]
		operation.visibility = JvmVisibility::PUBLIC
		type.members += operation

		operation = definition.toMethod("initializeFilterMap",
			_typeReferenceBuilder.typeRef(ArrayList, _typeReferenceBuilder.typeRef(DatamartFilter))) [
			body = [
				append('''
					return new ArrayList<DatamartFilter>() «definition.createFilterMap»;
				''')
			]
		]
		operation.visibility = JvmVisibility::PROTECTED
		type.members += operation

		// Method getPrimaryFilterId
		operation = definition.toMethod("getPrimaryFilterId", _typeReferenceBuilder.typeRef(String), [
			body = [
				append('''
					return "«definition.createPrimaryFilterId»";
				''')
			]
		])
		operation.visibility = JvmVisibility::PUBLIC
		type.members += operation

		operation = definition.toMethod("clearCache", _typeReferenceBuilder.typeRef(Void::TYPE), [
			annotations += _annotationTypesBuilder.annotationRef(Override)
			body = [
				append('''
				«IF definition.source instanceof DatamartCube»
					try {
						«binderClassName».getPersistenceService().clearCubeCache(«binderClassName».getPersistenceService().getMondrianConnection(getPersistenceUnit(), "«((definition.source as DatamartCube).cubeRef.eContainer as CubePackage).fullyQualifiedName»"), "«(definition.source as DatamartCube).cubeRef.name»");
					} catch (SQLException e) {
						log.error("{}", e);
					};
				«ENDIF»''')
			]
		])
		type.members += operation

		operation = definition.toMethod("getResultAttribute", _typeReferenceBuilder.typeRef(String), [
			annotations += _annotationTypesBuilder.annotationRef(Override)
			parameters += definition.toParameter("attributeName", _typeReferenceBuilder.typeRef(String))
			body = [
				append('''
				return resultAttributes.get(attributeName);''')
			]
		])
		type.members += operation

		operation = definition.toMethod("getTypesMap",
			_typeReferenceBuilder.typeRef(Map, _typeReferenceBuilder.typeRef(String),
				_typeReferenceBuilder.typeRef(EType)), [
				body = [
					append('''
						return typesMap;
					''')
				]
			])
		operation.visibility = JvmVisibility::PUBLIC
		type.members += operation

		/* the task stuff */
		if (definition.source instanceof DatamartTask) {
			// get persistence unit
			type.members += definition.toMethod("getPersistenceUnit", _typeReferenceBuilder.typeRef(String), [
				body = [
					append(
						'''
							return null;
						'''
					)
				]
			])
			// client connect method
			type.members += definition.toMethod("connect", _typeReferenceBuilder.typeRef(Connection), [
				body = [
					append(
							'''
					return null;''')
				]
			])

			// disconnect method
			type.members += definition.toMethod("disconnect", _typeReferenceBuilder.typeRef(Void::TYPE), [
				body = [
					append(
							'''''')
				]
			])

			// renderFilters method
			operation = definition.toMethod("renderFilters", _typeReferenceBuilder.typeRef(Void::TYPE), [
				body = [
					append(
							'''
					for (DatamartFilter filter : getFilters()) {
						filter.clearData();
						if (filter.getName().equals("Groups")) {
							for(String grp : «binderClassName».getBpmEngine().getGroups()) {
								filter.addItem(grp, true);
							}
						}
						if (filter.getName().equals("Users")) {
							for(String usr : «binderClassName».getBpmEngine().getUsers()) {
								filter.addItem(usr, true);
							}
						}
					}''')
				]
			])
			type.members += operation

			// getResults method
			type.members += definition.toMethod("getResults", _typeReferenceBuilder.typeRef(DerivedCellSet), [
				body = [
					append(
							'''
						if(user != null) {
							List<BPMTaskSummary> tasks = «datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».getTaskClient().getUserTaskList(user, false);
							if (tasks == null || tasks.size() == 0) {
								tasks = «datamart.fullyQualifiedName.lastSegment.toFirstUpper+"ServiceBinder"».getTaskClient().getUserTaskList(user, true);
							}
							return (new DerivedCellSet(new SqlCellSet(tasks, getAxisMap()), null,
								«binderClassName».getDSLMetadataService(),
								user));
						}
						return null;
					''')
				]
			])

			// getResults by taskid method
			type.members += definition.toMethod("getResultsByTaskId", _typeReferenceBuilder.typeRef(DerivedCellSet), [
				parameters += definition.toParameter("client", _typeReferenceBuilder.typeRef(IBPMTaskClient))
				parameters += definition.toParameter("taskId", _typeReferenceBuilder.typeRef(Long))
				body = [
					append(
							'''
					List<BPMTaskSummary> myTasks = new ArrayList<>(); 
					myTasks.add(client.getUserTask(taskId, user));
					return (new DerivedCellSet(new SqlCellSet(myTasks, getAxisMap()), null,
						«binderClassName».getDSLMetadataService(), user));''')
				]
			])

			// test method
			operation = definition.toMethod('''Test«definition.name»''', _typeReferenceBuilder.typeRef(String), [
				body = [
					append(
							'''
					renderFilters();
					org.junit.Assert.assertNotNull(getResults());''')
				]
			])
			operation.returnType = _typeReferenceBuilder.typeRef(Void::TYPE)
			var JvmAnnotationReference annRef = _annotationTypesBuilder.annotationRef(Test)
			operation.annotations.add(annRef)
			type.members += operation
		}

		/* the cube stuff */
		if (definition.source instanceof DatamartCube) {
			if ((definition.source as DatamartCube).cubeRef.cubeTypeEntity !== null) {
				val entity = (definition.source as DatamartCube).cubeRef.cubeTypeEntity.entityRef.entityValue
				// get persistence unit
				type.members += definition.toMethod("getPersistenceUnit", _typeReferenceBuilder.typeRef(String), [
					body = [
						append(
							'''
								return "«(entity as LEntity).persistenceUnit»";
							'''
						)
					]
				])
				// connect method
				type.members += definition.toMethod("connect", _typeReferenceBuilder.typeRef(RolapConnection), [
					body = [
						append(
								'''
						if (connection == null) {   			
							try {
								connection = «binderClassName».getPersistenceService().getMondrianConnection(getPersistenceUnit(), "«((definition.source as DatamartCube).cubeRef.eContainer as CubePackage).fullyQualifiedName»");
							} catch (SQLException e) {
								log.error("{}", e);
								return (connection);
							}
						}
						return (connection);''')
					]
				])
				// disconnect method
				type.members += definition.toMethod("disconnect", _typeReferenceBuilder.typeRef(Void::TYPE), [
					body = [
						append(
								'''
						if (connection != null) {
							connection.close();
							connection = null;
						}''')
					]
				])

				// renderFilters method
				operation = definition.toMethod("renderFilters", _typeReferenceBuilder.typeRef(Void::TYPE), [
					body = [
						append(
								'''
						connect();
						if (connection != null) {
							for (DatamartFilter filter : getFilters()) {
								filter.clearData();
								DerivedCellSet cellSet = new DerivedCellSet(«binderClassName».getPersistenceService().sendQuery(connection,getVendorSql(filter)),
									«binderClassName».getDSLMetadataService(),
									user);
								List<DerivedAxis> axes = cellSet.getAxes();
								DerivedAxis columnsAxis = axes.get(Axis.COLUMNS.ordinal());
								for (DerivedPosition position : columnsAxis.getPositions()) {
									DerivedMember measure = position.getMembers().get(0);
									filter.addOlapItem(measure.getHierarchy().getName(), measure.getUniqueName());
								}
							}
						}
						disconnect();''')
					]
				])
				type.members += operation

				// applyFilters method
				operation = definition.toMethod("applyFilters", _typeReferenceBuilder.typeRef(String), [
					body = [
						append(
								'''
						String filteredStatement = statement;
						computeConditions();
						for (DatamartFilter filter : getFilters()) {
							if(filter.getCondition() != null) {
								filteredStatement = filteredStatement.replace(filter.getDecoratedName(), filter.getCondition().replace("'",""));
							} else {
								filteredStatement = filteredStatement.replace(filter.getDecoratedName(), "");
							}
						}
						filteredStatement = filteredStatement.replace("<empty>,","").replace(",<empty>","").replace("<empty>","");
						log.debug("«definition.name»:"+filteredStatement);
						return filteredStatement;''')
					]
				])
				type.members += operation

				// execute method
				type.members += definition.toMethod("getResults", _typeReferenceBuilder.typeRef(DerivedCellSet), [
					body = [
						append(
								'''
						DerivedCellSet derivedCellSet = null;
						connect();
						if (connection != null) {
							derivedCellSet = new DerivedCellSet(«binderClassName».getPersistenceService().sendQuery(connection, applyFilters()),
									«binderClassName».getDSLMetadataService(),
									user);
						}
						disconnect();
						return derivedCellSet;''')
					]
				])

				// test method
				operation = definition.toMethod('''Test«definition.name»''', _typeReferenceBuilder.typeRef(String), [
					body = [
						append(
							'''
							renderFilters();
							org.junit.Assert.assertNotNull(getResults());'''
						)
					]
				])
				operation.returnType = _typeReferenceBuilder.typeRef(Void::TYPE)
				var JvmAnnotationReference annRef = _annotationTypesBuilder.annotationRef(Test)
				operation.annotations.add(annRef)
				type.members += operation
			}
		}

		/* the entity stuff */
		if (definition.source instanceof DatamartEntity) {
			// get persistence unit
			type.members += definition.toMethod("getPersistenceUnit", _typeReferenceBuilder.typeRef(String), [
				body = [
					append(
						'''
							return "«(definition.source as DatamartEntity)?.entityRef?.persistenceUnit»";
						'''
					)
				]
			])
			// connect method
			type.members += definition.toMethod("connect", _typeReferenceBuilder.typeRef(Connection), [
				body = [
					append(
						'''
						if (connection == null) {   			
							try {
								connection = «binderClassName».getPersistenceService().getPersistenceUnitConnection(getPersistenceUnit());
							} catch (SQLException e) {
								log.error("{}", e);
								return (connection);
							}
						}
						return (connection);'''
					)
				]
			])

			// disconnect method
			type.members += definition.toMethod("disconnect", _typeReferenceBuilder.typeRef(Void::TYPE), [
				body = [
					append(
						'''
						if (connection != null) {   			
							try {
								connection.close();
								connection = null;
							} catch (SQLException e) {
								log.error("{}", e);
							}
						}'''
					)
				]
			])

			// more to load?
			type.members += definition.toMethod("isMoreToLoad", _typeReferenceBuilder.typeRef(boolean), [
				body = [
					append('''return moreToLoad;''')
				]
			])
			// renderFilters method
			type.members += definition.toMethod("insertCondition", _typeReferenceBuilder.typeRef(String), [
				parameters += definition.toParameter("filteredStatement", _typeReferenceBuilder.typeRef(String))
				parameters += definition.toParameter("condition", _typeReferenceBuilder.typeRef(String))
				body = [
					append(
						'''
						int index = filteredStatement.indexOf("order by");
						if(index > -1) {
							return filteredStatement.substring(0, index-1) + condition + filteredStatement.substring(index-1); 
						} else {
							return filteredStatement + condition;
						}'''
					)
				]
			])
			
			// addUserFilter method
			operation = definition.toMethod("addUserFilter", _typeReferenceBuilder.typeRef(String), [
				parameters += definition.toParameter("sql", _typeReferenceBuilder.typeRef(String))
				parameters += definition.toParameter("entity", _typeReferenceBuilder.typeRef(String))
				body = [
					append(
						'''
						return addUserFilter( sql, entity, entity );
						'''
					)
				]
			])
			type.members += operation
			
			// addUserFilter method
			operation = definition.toMethod("addUserFilter", _typeReferenceBuilder.typeRef(String), [
				parameters += definition.toParameter("sql", _typeReferenceBuilder.typeRef(String))
				parameters += definition.toParameter("entity", _typeReferenceBuilder.typeRef(String))
				parameters += definition.toParameter("entityAlias", _typeReferenceBuilder.typeRef(String))
				body = [
					append(
						'''
					    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;
						'''
					)
				]
			])
			type.members += operation
			

			// renderFilters method
			operation = definition.toMethod("renderFilters", _typeReferenceBuilder.typeRef(Void::TYPE), [
				body = [
					append(
						'''
						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();'''
					)
				]
			])
			type.members += operation

			// applyFilters method
			operation = definition.toMethod("applyFilters", _typeReferenceBuilder.typeRef(String), [
				body = [
					append(
							'''
					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 «definition.name»:{}", filteredStatement);
								return "";
							}
						}
						«IF (definition.source as DatamartEntity).entityRef.currentAttribute !== null»
						if (DatamartFilter.FilterType.BY_HISTUUID == filter.getType()) {
							if	(!filteredStatement.toLowerCase().contains(" where ")) {
								if	((" "+filter.getCondition()).toLowerCase().contains("where ")) {
									filteredStatement = insertCondition(filteredStatement, " "+filter.getHistCondition("«(definition.source as DatamartEntity).getEntityAlias».«(definition.source as DatamartEntity).entityRef.currentAttribute.toColumnName»"));
								}
								else {
									filteredStatement = insertCondition(filteredStatement, " where "+filter.getHistCondition("«(definition.source as DatamartEntity).getEntityAlias».«(definition.source as DatamartEntity).entityRef.currentAttribute.toColumnName»"));
								}
							}
							else {
								if	(filter.getCondition().trim().toLowerCase().startsWith("and ") ||
									 filter.getCondition().trim().toLowerCase().startsWith("or ") ) {
									filteredStatement = insertCondition(filteredStatement, filter.getHistCondition("«(definition.source as DatamartEntity).getEntityAlias».«(definition.source as DatamartEntity).entityRef.currentAttribute.toColumnName»"));
								}
								else {
									filteredStatement = insertCondition(filteredStatement, " and "+filter.getHistCondition("«(definition.source as DatamartEntity).getEntityAlias».«(definition.source as DatamartEntity).entityRef.currentAttribute.toColumnName»"));
								}
							}
						}
						«ENDIF»
					}
					log.debug("«definition.name»:"+filteredStatement);
					String entity=getPrimaryFilterId().split("\\.")[0];
					if( entity.contains("_")){
						entity=entity.split("\\_")[0];
					}
					filteredStatement = addUserFilter( filteredStatement, entity);
					return filteredStatement;''')
				]
			])
			type.members += operation

			// execute methods
			type.members += definition.toMethod("getResults", _typeReferenceBuilder.typeRef(DerivedCellSet), [
				body = [append('''return getResults(false, null, null);''')]
			])
				
			type.members += definition.toMethod("getResults", _typeReferenceBuilder.typeRef(DerivedCellSet), [
				parameters += definition.toParameter("limited", _typeReferenceBuilder.typeRef(boolean))
				parameters += definition.toParameter("operativeDtoClass", _typeReferenceBuilder.typeRef(Class))
				parameters += definition.toParameter("operativeDtos",
					_typeReferenceBuilder.typeRef(List, _typeReferenceBuilder.typeRef(IDto)))
				body = [
					append(
							'''
					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(«IF definition.rowLimitSet»«definition.fetchSize»«ELSE»«defaultFetchSize»«ENDIF»);
									stmt.setFetchSize(«IF definition.rowLimitSet»«definition.fetchSize»«ELSE»«defaultFetchSize»«ENDIF»);
								}
								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(), «binderClassName».getUserAccessService(), «(definition.source as DatamartEntity).entityRef.isHistorizedOrTimedependentWithParent.booleanValue»);
									moreToLoad = false;
									if(limited && cellSet.getFetchedRows() == «IF definition.rowLimitSet»«definition.fetchSize»«ELSE»«defaultFetchSize»«ENDIF») {
										moreToLoad = true;
									}
									disconnect();
									return (new DerivedCellSet(cellSet,
										metaData,
										«binderClassName».getDSLMetadataService(), user));
								}
							}
							catch (java.sql.SQLException e) {
								log.error("Statement: {}  error {}", query, e);
							}
						}
					}
					disconnect();
					return null;''')
				]
			])
			// test method
			operation = definition.toMethod('''Test«definition.name»''', _typeReferenceBuilder.typeRef(String), [
				body = [
					append(
							'''
					renderFilters();
					org.junit.Assert.assertNotNull(getResults(true, null, null));''')
				]
			])
			operation.returnType = _typeReferenceBuilder.typeRef(Void::TYPE)
			var JvmAnnotationReference annRef = _annotationTypesBuilder.annotationRef(Test)
			operation.annotations.add(annRef)
			type.members += operation
		}
	}

	def String createFilterAttributeProperties(DatamartEntity entity) {
		var text = ""
		for (condition : entity.conditions) {
			if (condition.condition !== null) {
				if (condition.condition instanceof ConditionalExpression) {
					var element = (condition.condition as ConditionalExpression).right
					var leftConditionalExpr = (condition.condition as ConditionalExpression).left 
					if (leftConditionalExpr instanceof DatamartAttributeBase) {
						var attribute = (condition.condition as ConditionalExpression).left as DatamartAttributeBase
						if (element.ranged) {
							if (attribute.attributeRef.datatype.isDate) {
								if (attribute.attributeRef.properties !== null &&
									!attribute.attributeRef.properties.empty) {
									text = '''
									{{
										put("«attribute.getAttributeName(entity)»", new LinkedHashMap<String, String>() {{'''
									for (property : attribute.attributeRef.properties) {
										text = '''
										«text»
												put("«property.key»", "«property.value»");'''
									}
									text = '''
									«text»
										}});
									}}'''
								}
							}
						}
					}
				}
			}
		}
		for (navigation : entity.navigations) {
			if (navigation instanceof DatamartMember) {
				text = '''
				«text»
				«(navigation as DatamartMember).datamartEntity.createFilterAttributeProperties»'''
			}
			if (navigation instanceof DatamartOwner) {
				text = '''
				«text»
				«(navigation as DatamartOwner).datamartEntity.createFilterAttributeProperties»'''
			}
		}
		return text
	}

	/**
	 * <p>build the id map as lookup for primary key oriented applications.</p> 
	 * 
	 */
	def String createIdMap(DatamartDefinition definition) {
		var listTxt = ""
		for (idColumn : idMap.keySet) {
			listTxt = '''
			«listTxt»
					put("«idColumn»", EType.«dtType.getBasicType(idMap.get(idColumn).primaryKeyAttribute as LEntityAttribute)»);'''
		}
		listTxt = '''
		{{
			«listTxt»
		}}'''
		return listTxt
	}

	def String createPrimaryList(DatamartDefinition definition) {
		var listTxt = ""
		for (idColumn : idMap.keySet) {
			if (idMap.get(idColumn).idAttributeType instanceof LDataType) {
				listTxt = '''
				«listTxt»
						put("«idColumn»", new DatamartPrimary("«idColumn»", "«idMap.get(idColumn).primaryKeyAttribute.name»", "«idMap.get(idColumn).fullyQualifiedName»", «idMap.get(idColumn).historizedOrTimedependentWithParent.toString»));'''
			}
		}
		listTxt = '''
		{{
			«listTxt»
		}}'''
		return listTxt
	}

	def String addPrimaryFilters(DatamartDefinition definition, DatamartEntity entity) {
		var listTxt = ""
		for (idColumn : idMap.keySet) {
			listTxt = '''
			«listTxt»
			add(new DatamartFilter(DatamartFilter.MultipleType.SINGLE, DatamartFilter.FilterType.«IF entity.entityRef.historizedOrTimedependentWithParent»BY_HISTUUID«ELSE»BY_ID«ENDIF», "«idEntityAliasMap.get(idColumn)».«idMap.get(idColumn).primaryKeyAttribute.name»", "«idMap.get(idColumn).name».«idMap.get(idColumn).primaryKeyAttribute.name»","", false));'''
		}
		return listTxt
	}

	/**
	 * <p>build the types map as lookup for datatypes.</p> 
	 * 
	 */
	def String createTypesMaps(
		DatamartDefinition definition,
		LinkedHashMap<String, LAttribute> typesMap
	) {

		var listTxt = '''
		{{
			«definition.createTypesMap(typesMap)»
		}}'''
		return listTxt
	}

	/**
	 * <p>build the types map as lookup for datatypes.</p> 
	 * 
	 */
	def String createTypesProps(
		DatamartDefinition definition,
		LinkedHashMap<String, LAttribute> typesMap
	) {

		var listTxt = ""
		for (attribute : typesMap.keySet) {
			var attributeName = attribute.split("\\.").get(1)
			if (!typesMap.get(attribute).properties.empty) {
				listTxt = '''
				«listTxt»
				put("«attributeName»", "«typesMap.get(attribute).properties.get(0).value»");'''
			} else if(typesMap.get(attribute).type instanceof LDataType) {
				if(!(typesMap.get(attribute).type as LDataType).properties.empty) {
					listTxt = '''
					«listTxt»
					put("«attributeName»", "«(typesMap.get(attribute).type as LDataType).properties.get(0).value»");'''
				}
			}
		}
		listTxt = '''
		{{
			«listTxt»
		}}'''
		return listTxt
	}

	/**
	 * <p>build the types map as lookup for datatypes according to authorization roles.</p> 
	 * 
	 */
	def String createTypesMap(DatamartDefinition definition, LinkedHashMap<String, LAttribute> typesMap) {
		var listTxt = ""
		for (attribute : typesMap.keySet) {
			val type = dtType.getBasicType(typesMap.get(attribute))
			listTxt = '''
			«listTxt»
			put("«attribute»", EType.«type.toString»);'''
		}
		return listTxt
	}

	/**
	 * <p>build the axis map.</p> 
	 * 
	 */
	def String createAxisMap(DatamartDefinition definition, Map<String, DatamartEntity> entityMap) {
		var text = ""
		var map = <Integer, Map<String, Integer>>newLinkedHashMap
		if (definition.source !== null) {
			if (definition.source instanceof DatamartEntity) {
				(definition.source as DatamartEntity).createAxisMap(map)
			}
			if (definition.source instanceof DatamartTask) {
				(definition.source as DatamartTask).createAxisMap(map)
			}
		}
		for (axisNumber : map.keySet()) {
			if (definition.source instanceof DatamartEntity && axisNumber == AxisEnum.COLUMNS_VALUE) {
				if (!ignoreEntityGrouping) {
					for (entity : entityMap.values) {
						var idColumn = DatamartDefinitionUtil.getEntityIdAliasName(entity.entityRef)
						if (idColumn !== null) {
							map.get(axisNumber).put(idColumn, 0)
						}
						var validColumn = DatamartDefinitionUtil.getEntityValidAliasName(entity.entityRef)
						if(validColumn !== null) {
							map.get(axisNumber).put(validColumn, 0)
						}
						var currentColumn = DatamartDefinitionUtil.getEntityCurrentAliasName(entity.entityRef)
						if(currentColumn !== null) {
							map.get(axisNumber).put(currentColumn, 0)
						}
					}
				}
			}
			// sort according weight reverse and format map 
			var listTxt = map.get(axisNumber).entrySet.stream.sorted(Map.Entry.comparingByValue.reversed).map [
				"add(\"" + it.key + "\");"
			].collect(Collectors.joining("\n", "{{\n", "\n}}"))
			text = '''
			«text»
				put(«axisNumber»,new ArrayList<String>() «listTxt»);'''
		}
		text = '''
		{{
			«text»
		}}'''
		return text
	}

	/**
	 * <p>creates an axis map for tasks.</p> 
	 * 
	 */
	def void createAxisMap(DatamartTask task, Map<Integer, Map<String, Integer>> map) {
		this.idMap.clear()
		for (column : task.columns) {
			var axisNumber = 0
			var name = column.columnRef.literal
			var found = false
			for (axNum : map.keySet()) {
				if (axNum == axisNumber) {
					map.get(axNum).put(name, defaultColumnWeight)
					found = true
				}
			}
			if (!found) {
				var columns = <String, Integer>newLinkedHashMap
				columns.put(name, defaultColumnWeight)
				map.put(axisNumber, columns)
			}
		}
	}

	def void recursAxisMap(LScalarType type, String beanPrefix, DatamartAttribute attribute, Map<Integer, Map<String, Integer>> map,
		int axisNumber) {
		if (type instanceof LBean) {
			var bean = type as LBean
			for (attr : bean.allAttributes) {
				var newBeanPrefix = '''«IF beanPrefix !== null»«beanPrefix»_«ENDIF»«attr.name»'''
				if (attr.type instanceof LBean) {
					attr.type.recursAxisMap(newBeanPrefix, attribute, map, axisNumber)
				} else {
					addAxisAttribute(newBeanPrefix, defaultColumnWeight, map, axisNumber)
				}
			}
		} else {
			var weight = defaultColumnWeight
			if (attribute.hasColumnWeight) {
				weight = attribute.columnWeight
			}
			addAxisAttribute(DatamartAttributeUtil.getAliasedAttributeName(attribute), weight, map, axisNumber)
		}
	}

	def addAxisAttribute(String name, Integer weight, Map<Integer, Map<String, Integer>> map, int axisNumber) {
		var found = false
		for (axNum : map.keySet()) {
			if (axNum == axisNumber) {
				map.get(axNum).put(name, weight)
				found = true
			}
		}
		if (!found) {
			var columns = <String, Integer>newLinkedHashMap
			columns.put(name, weight)
			map.put(axisNumber, columns)
		}
	}

	/**
	 * <p>recursively create an axis map for entities.</p> 
	 * 
	 */
	def void createAxisMap(DatamartEntity entity, Map<Integer, Map<String, Integer>> map) {
		var entityAttributes = entity.entityAttributes
		for (attribute : entityAttributes) {
			var axisNumber = -1
			if (attribute.axis.name.value == -1) {
				axisNumber = 0
			} else {
				axisNumber = attribute.axis.name.value
			}
			var type = attribute.attributeRef.type as LScalarType
			var String beanPrefix = null
			if (type instanceof LBean) {
				beanPrefix = attribute.attributeRef.name
			}
			type.recursAxisMap(beanPrefix, attribute, map, axisNumber)
		}
		for (navigation : entity.navigations) {
			navigation.datamartEntity.createAxisMap(map)
		}
	}
	
	def String createHiddenMap(DatamartDefinition definition) {
		val hiddenMap = <String,AttributeVisibility>newLinkedHashMap
		if (definition.source !== null && definition.source instanceof DatamartEntity) {
			(definition.source as DatamartEntity).createHiddenMap(hiddenMap)
		}
		var text = '''
		{{
			«hiddenMap.entrySet.map['''put("«it.key»",AttributeVisibility.«it.value»);'''].join("\n")»
		}}'''
		return text
	}

	def void createHiddenMap(DatamartEntity entity, Map<String,AttributeVisibility> hiddenMap) {
		var entityAttributes = entity.entityAttributes
		for (attribute : entityAttributes) {
			var type = attribute.attributeRef.type as LScalarType
			recursHiddenMap(type, attribute, hiddenMap)
		}
		for (navigation : entity.navigations) {
			navigation.datamartEntity.createHiddenMap(hiddenMap)
		}
	}
	
	def void recursHiddenMap(LScalarType type, DatamartAttribute attribute, Map<String,AttributeVisibility> hiddenMap) {
		if (type instanceof LBean) {
			var bean = type as LBean
			for (attr : bean.allAttributes) {
				if (attr.type instanceof LBean) {
					recursHiddenMap(attr.type, attribute, hiddenMap)
				} else if(attr.attributeHidden) {
					hiddenMap.put(attr.name, AttributeVisibility.HIDDEN)
				}
			}
		} else if(attribute.attributeRef.attributeHidden) {
			hiddenMap.put(DatamartAttributeUtil.getAliasedAttributeName(attribute), AttributeVisibility.HIDDEN)
		}
	}
	
	/**
	 * <p>build the alias map.</p> 
	 * 
	 */
	def String createAliasMap(DatamartDefinition definition) {
		var aliases = <String, String>newLinkedHashMap
		if (definition.source !== null && definition.source instanceof DatamartEntity) {
			(definition.source as DatamartEntity).createAliasMap(aliases)
		}
		var text = '''
		{{
			«aliases.entrySet.map['''put("«it.key»","«it.value»");'''].join("\n")»
		}}'''
		return text
	}

	def void recursAliasMap(LScalarType type, String beanPrefix, DatamartAttribute attribute, Map<String, String> map) {
		if (type instanceof LBean) {
			var bean = type as LBean
			for (attr : bean.allAttributes) {
				var newBeanPrefix = '''«IF beanPrefix !== null»«beanPrefix»_«ENDIF»«attr.name»'''
				if (attr.type instanceof LBean) {
					attr.type.recursAliasMap(newBeanPrefix, attribute, map)
				} else {
					map.put(newBeanPrefix, attr.fullyQualifiedName.toString)
				}
			}
		} else {
			map.put(DatamartAttributeUtil.getAliasedAttributeName(attribute), attribute.attributeRef.entity.fullyQualifiedName.toString+"."+attribute.attributeRef.name)
		}
	}

	/**
	 * <p>recursively create an alias map for entities.</p> 
	 * 
	 */
	def void createAliasMap(DatamartEntity entity, Map<String, String> map) {
		var entityAttributes = entity.entityAttributes
		for (attribute : entityAttributes) {
			var type = attribute.attributeRef.type as LScalarType
			var String beanPrefix = null
			if (type instanceof LBean) {
				beanPrefix = attribute.attributeRef.name
			}
			type.recursAliasMap(beanPrefix, attribute, map)
		}
		for (navigation : entity.navigations) {
			navigation.datamartEntity.createAliasMap(map)
		}
	}

	def getAttributeType(DatamartAttribute prop) {
		return prop.attributeRef.type
	}

	/**
	 * <p>build a map for the filters contained in a datamart depending on a cube.</p> 
	 * 
	 */
	def createFilterMap(DatamartDefinition definition) {
		var text = ""
		if (definition.source !== null) {
			if (definition.source instanceof DatamartCube) {
				var cube = (definition.source as DatamartCube)
				for (axisslicer : cube.axisslicer) {
					if (axisslicer instanceof DatamartCubeAxis && (axisslicer as DatamartCubeAxis).elements !== null) {
						for (element : (axisslicer as DatamartCubeAxis).elements) {
							if(element instanceof DatamartSetAggregation) {
								var setAggregation = (element as DatamartSetAggregation)
								if(setAggregation.set instanceof DatamartHierarchy) {
									var hierarchy = setAggregation.set as DatamartHierarchy
									text = text+hierarchy.createHierarchyFilter(definition, true)
								} else if(setAggregation.left.set instanceof DatamartHierarchy) {
									var hierarchy = setAggregation.left.set as DatamartHierarchy
									text = text+hierarchy.createHierarchyFilter(definition, true)
								} else if(setAggregation.set instanceof DatamartSetTuple) {
									var hierarchy = (setAggregation.set as DatamartSetTuple).right
									text = text+hierarchy.createHierarchyFilter(definition, true)
								}
							} else if (element instanceof DatamartHierarchy) {
								var hierarchy = (element as DatamartHierarchy)
								text = text+hierarchy.createHierarchyFilter(definition, true)
							}
						}
					}
					if (axisslicer instanceof DatamartSlicer && (axisslicer as DatamartSlicer).element !== null) {
						var element = (axisslicer as DatamartSlicer).element
						if (element instanceof DatamartHierarchy) {
							var hierarchy = (element as DatamartHierarchy)
							text = text+hierarchy.createHierarchyFilter(definition, false)
						}
					}
				}
			}
			if (definition.source instanceof DatamartEntity) {
				var entity = (definition.source as DatamartEntity)
				text = entity.createFiltermapCondition(definition)
				text = text + definition.addPrimaryFilters(entity)
			}
			if (definition.source instanceof DatamartTask) {
				var task = (definition.source as DatamartTask)
				text = text+task.createFiltermapCondition(definition)
			}
			if (text.length > 0) {
				text = '''
				{{
				«text»
				}}'''
			}
		}
		return text
	}
	
	def String createHierarchyFilter(DatamartHierarchy hierarchy, DatamartDefinition definition, boolean fromHierarchy) {
		var text = ""
		if (hierarchy.level instanceof DatamartHierarchyLevelSingle){
			text = (hierarchy.level as DatamartHierarchyLevelSingle).createHierarchyFilter(hierarchy, definition, fromHierarchy) 
		} else if (hierarchy.level instanceof DatamartHierarchyLevelMultiple) {
			for (hierarchyLevelSingle : (hierarchy.level as DatamartHierarchyLevelMultiple).levels) {
				text = '''
				«text»
				«hierarchyLevelSingle.createHierarchyFilter(hierarchy, definition, fromHierarchy)»
				'''
			}
		}
		return text
	}
	
	def private String createHierarchyFilter(DatamartHierarchyLevelSingle hierarchyLevel, DatamartHierarchy hierarchy, DatamartDefinition definition, boolean fromHierarchy) {
		var text = ""
		var String ordering = null
		if(hierarchy.hierarchyRef.eContainer instanceof CubeDimension) {
			if((hierarchy.hierarchyRef.eContainer as CubeDimension).typeTime) {
				ordering = "DESC"
			}
		}
		if(fromHierarchy) {
			if((hierarchyLevel.filtered || hierarchyLevel.selected) && hierarchy.hierarchyRef.eContainer !== null) { //} && !((hierarchy.hierarchyRef.eContainer as CubeDimension).typeTime)) {
				text = '''
				«text»
				add(new DatamartFilter(«IF hierarchyLevel.filtered»DatamartFilter.MultipleType.SINGLE«ELSE»DatamartFilter.MultipleType.MULTIPLE«ENDIF», DatamartFilter.FilterType.HIERARCHY, "«hierarchyLevel.getLevelName(hierarchy, true, false, false, null, true)»","«hierarchyLevel.createMDXFilter(hierarchy, definition.source, ordering, false)»", false));
				'''
			}
		} else {	// slicer
			text = '''
			«text»
			add(new DatamartFilter(«IF hierarchyLevel.filtered»DatamartFilter.MultipleType.SINGLE«ELSE»DatamartFilter.MultipleType.MULTIPLE«ENDIF», DatamartFilter.FilterType.SLICER, "«hierarchyLevel.getLevelName(hierarchy, true, false, false, null, true)»","«hierarchyLevel.createMDXFilter(hierarchy, definition.source, ordering, false)»", false));
			'''
		}
		if (hierarchy.except) {
			text = '''
			«text»
			add(new DatamartFilter(«IF hierarchyLevel.filtered»DatamartFilter.MultipleType.SINGLE«ELSE»DatamartFilter.MultipleType.MULTIPLE«ENDIF», DatamartFilter.FilterType.EXCEPT, "«hierarchy.getDimensionName(true)+".["+hierarchy.exceptRef.name+"]"»","«hierarchyLevel.createMDXFilter(hierarchy, definition.source, ordering, true)»", false));
			'''
		}
		return text
	}
	
	/**
	 * <p>build a map for the filters contained in a datamart depending on entity.</p> 
	 * 
	 */
	def createPrimaryFilterId(DatamartDefinition definition) {
		var text = ""
		if (definition.source !== null && definition.source instanceof DatamartEntity) {
			var entity = (definition.source as DatamartEntity)
			text = '''«entity.entityRef.name».«entity.entityRef.primaryKeyAttribute.name»'''
		}
		return text
	}

	/**
	 * <p>build a map for the filters contained in a datamart depending on a task.</p> 
	 * 
	 */
	def String createFiltermapCondition(DatamartTask task, DatamartDefinition definition) {
		var text = ""
		for (condition : task.conditions) {
			text = '''
			«text»
			«condition.createSQLFilter(task, definition)»'''
		}
		return text
	}

	/**
	 * <p>build the sql filter condition depending on an entity.</p> 
	 * 
	 */
	def createSQLFilter(DatamartCondition condition, DatamartTask task, DatamartDefinition definition) {
		var text = ""
		if (condition.condition !== null) {
			if (condition.condition instanceof ConditionalExpression) {
				var element = (condition.condition as ConditionalExpression).right
				if ((condition.condition as ConditionalExpression).left instanceof DatamartTaskFilter) {
					var taskFilter = (condition.condition as ConditionalExpression).left as DatamartTaskFilter
					if (element.filtered || element.selected) {
						var filter = '''«taskFilter.filterRef.literal»'''
						text = '''
						«text»
						add(new DatamartFilter(«IF element.filtered»DatamartFilter.MultipleType.SINGLE«ELSEIF element.selected»DatamartFilter.MultipleType.MULTIPLE«ENDIF», DatamartFilter.FilterType.SQL, "«taskFilter.filterRef.literal»","«filter»", false));'''
					}
				}
			}
		}
		return text
	}

	/**
	 * <p>build a map for the filters contained in a datamart depending on an entity.</p> 
	 * 
	 */
	def String createFiltermapCondition(DatamartEntity entity, DatamartDefinition definition) {
		var text = ""
		for (condition : entity.conditions) {
			text = '''«text»«condition.createSQLFilter(entity, definition)»'''
		}
		for (navigation : entity.navigations) {
			if (navigation instanceof DatamartMember) {
				text = '''
				«text»
				«(navigation as DatamartMember).datamartEntity.createFiltermapCondition(definition)»'''
			}
			if (navigation instanceof DatamartOwner) {
				text = '''
				«text»
				«(navigation as DatamartOwner).datamartEntity.createFiltermapCondition(definition)»'''
			}
		}
		return text
	}

	/**
	 * <p>build the sql filter condition depending on an entity.</p> 
	 * 
	 */
	def createSQLFilter(DatamartCondition condition, DatamartEntity entity, DatamartDefinition definition) {
		var text = ""
		if (condition !== null && condition.condition !== null) {
			text = entity.evaluateDatamartFilterFromCondition(condition.condition)
		}
		return text
	}
	
	def String evaluateDatamartFilterFromCondition(DatamartEntity entity, Expression expression) {
		var text = ""
		if (expression instanceof ConditionalExpression) {
			text = text+entity.createDatamartFilterFromCondition(expression as ConditionalExpression)
		} else if(expression instanceof Conjunction) {
			text = text+entity.evaluateDatamartFilterFromCondition(expression.left)
			text = text+entity.evaluateDatamartFilterFromCondition(expression.right)
		} else if(expression instanceof Disjunction) {
			text = text+entity.evaluateDatamartFilterFromCondition(expression.left)
			text = text+entity.evaluateDatamartFilterFromCondition(expression.right)
		}
		return text
	}
	
	def createDatamartFilterFromCondition(DatamartEntity entity, ConditionalExpression expression) {
		var text = ""
		var element = expression.right
		var leftCondExpr = expression.left
		if (leftCondExpr instanceof DatamartAttributeBase){
			var attribute = expression.left as DatamartAttributeBase
			var filter = element.createSQLFilter(entity, attribute)
			if(element.filtered || element.selected) {
				text = '''«text»add(new DatamartFilter(«IF element.filtered»DatamartFilter.MultipleType.SINGLE«ELSE»DatamartFilter.MultipleType.MULTIPLE«ENDIF», DatamartFilter.FilterType.SQL, "«attribute.getAttributeName(entity)»","«filter»",«element.optional.booleanValue»));
				'''
			}
			if (element.ranged) {
				if (attribute.attributeRef.datatype.isDate) {
					if (attribute.attributeRef.properties !== null) {
						text = '''«text»add(new DatamartFilter(DatamartFilter.MultipleType.NONE, DatamartFilter.FilterType.BETWEEN_DATE, dateFilterAttributeProperties.get("«attribute.getAttributeName(entity)»"), "«attribute.getAttributeName(entity)»","",«element.optional.booleanValue»));
						'''
					} else {
						text = '''«text»add(new DatamartFilter(DatamartFilter.MultipleType.NONE, DatamartFilter.FilterType.BETWEEN_DATE, "«attribute.getAttributeName(entity)»","",«element.optional.booleanValue»));
						'''
					}
				} else {
					text = '''«text»add(new DatamartFilter(DatamartFilter.MultipleType.NONE, DatamartFilter.FilterType.BETWEEN, "«attribute.getAttributeName(entity)»","",«element.optional.booleanValue»));
					'''
				}
			}
		}		
		return text
	}

	def createSQLFilter(Expression element, DatamartEntity entity, DatamartAttributeBase attribute) {
		var tableName = entity.entityRef.toTableName
		if (entity.hasSuperType) {
			tableName = entity.entityRef.superType.toTableName
		}
		var type = attribute.attributeRef.type
		var filter = ""
		if (type instanceof LDataType) {
			if (element.optional && !type.date) {
				filter = '''select «IDataMart.SQLFILTERNOTHING» from «DatabaseVendor.ONE_ROW_ONE_COLUMN_TABLE» union '''
			}
		}
		var currentColumn = DatamartDefinitionUtil.getEntityCurrentAliasName(entity.entityRef)
		filter = '''«filter»select distinct «attribute.attributeRef.toColumnName» as \"«DatamartAttributeUtil.getAliasedAttributeName(attribute)»\" from «entity.entityRef.persistenceInfo.schemaName.provideSchemaName»«tableName»«IF entity.entityRef.isHistorizedOrTimedependentWithParent»where «currentColumn»=1«ENDIF» order by 1'''
		return filter
	}

	/**
	 * <p>build the mdx filter slice depending on a cube.</p> 
	 * 
	 */
	def createMDXFilter(DatamartHierarchyLevelSingle hierarchyLevel, DatamartHierarchy hierarchy, DatamartSource source, String ordering, boolean isExcept) {
		var filter = "select {} on columns,"
		if (hierarchyLevel.levelRef !== null && !isExcept) {
			if(ordering !== null) {
				filter = '''«filter»Order(«hierarchyLevel.getLevelName(hierarchy, true, false, false, null, true)».members,[«hierarchy.hierarchyName»].CurrentMember.Properties(\"MEMBER_KEY\"),«ordering») on rows'''
			} else {
				filter = '''«filter»«hierarchyLevel.getLevelName(hierarchy, true, false, false, null, true)».members on rows'''
			}
		}
		if (hierarchy.exceptRef !== null && isExcept) {
			if(ordering !== null) {
				filter = '''«filter»Order(«hierarchy.getDimensionName(true)+".["+hierarchy.exceptRef.name+"]"».members,[«hierarchy.getExceptHierarchyName»].CurrentMember.Properties(\"MEMBER_KEY\"),«ordering») on rows'''
			} else {
				filter = '''«filter»«hierarchy.getDimensionName(true)+".["+hierarchy.exceptRef.name+"]"».members on rows'''
			}
		}
		if (source instanceof DatamartCube) {
			filter = '''«filter» from «(source as DatamartCube).cubeRef.name»'''
		}
		return filter
	}

	/**
	 * <p>helper to get a level name.</p> 
	 * 
	 */
	def getFilterAlias(DatamartHierarchy hierarchy) {
		var text = ""
		if ((hierarchy instanceof DatamartHierarchyLevelSingle) && (hierarchy as DatamartHierarchyLevelSingle).levelRef !== null) {
			text = '''«hierarchy.getDimensionName(false)»«(hierarchy as DatamartHierarchyLevelSingle).levelRef.name.toFirstUpper»'''
		}
		return text
	}

	/**
	 * <p>mdx syntax part for aggregations on sets.</p> 
	 * 
	 */
	def createSetAggregation(List<DatamartSetAggregation> usedSetAggregation, Boolean nonEmpty) {
		var set = ""
		var firstTuple = true
		for (element : usedSetAggregation) {
			if (firstTuple) {
				firstTuple = false
			} else {
				set = '''«set»,'''
			}
			set = '''«set»«element.evaluateExpression(null)»'''
		}
		if (usedSetAggregation.size > 1) {
			set = '''«IF nonEmpty»Non Empty«ENDIF»{«set»}'''
		} else if (usedSetAggregation.size > 0) {
			set = '''«IF nonEmpty»Non Empty «ENDIF»«set»'''
		}
		return set
	}

	/**
	 * <p>mdx syntax part for hierarchies.</p> 
	 * 
	 */
	def createHierarchies(List<DatamartHierarchy> usedHierarchies, Boolean nonEmpty) {
		var set = ""
		var firstTuple = true
		for (element : usedHierarchies) {
			if (firstTuple) {
				firstTuple = false
			} else {
				set = '''«set»,'''
			}
			if (element.level instanceof DatamartHierarchyLevelMultiple) {
				set = '''«set»ORDER(Hierarchize({«element.getLevelName(true, true, true, null, false)» }«IF (element.level as DatamartHierarchyLevelMultiple).post», POST«ENDIF»)«element.getOrderArgument(element.level as DatamartHierarchyLevelMultiple)»'''
			} else {
				// Otherwise in all cases, also in case of non level hierarchy.
				set = '''«set»«element.getLevelName(true, true, true, null, false)»'''
			}
		}
		if (usedHierarchies.size > 1) {
			set = '''«IF nonEmpty»Non Empty«ENDIF»{«set»}'''
		} else if (usedHierarchies.size > 0) {
			set = '''«IF nonEmpty»Non Empty «ENDIF»«set»'''
		}
		return set
	}
	
	
	def String getOrderArgument(DatamartHierarchy hierarchy, DatamartHierarchyLevelMultiple multiple) {
		for (level : multiple.levels){
			if (level.sorted) {
				return ''', «hierarchy.getDimensionName(true)».[«level.levelRef.name»].Ordinal, BASC)'''
			}
		}
		return ''''''
	}

	/**
	 * <p>mdx syntax part for measures.</p> 
	 * 
	 */
	def createMeasures(List<DatamartMeasure> usedMeasures, List<DatamartDerivedMeasure> usedDerived, Boolean nonEmpty) {
		var set = ""
		var firstTuple = true
		for (element : usedMeasures) {
			if (firstTuple) {
				firstTuple = false
			} else {
				set = '''«set»,'''
			}
			set = '''«set»«element.getMeasureName»'''
		}
		for (element : usedDerived) {
			if (firstTuple) {
				firstTuple = false
			} else {
				set = '''«set»,'''
			}
			set = '''«set»«element.getDerivedMeasureName»'''
		}
		if (usedMeasures.size + usedDerived.size > 1) {
			set = '''«IF nonEmpty»Non Empty«ENDIF»{«set»}'''
		} else if (usedMeasures.size + usedDerived.size > 0) {
			set = '''«IF nonEmpty»Non Empty «ENDIF»«set»'''
		}
		return set
	}

	/**
	 * <p>the main function to build a mdx statement.</p> 
	 * 
	 */
	def createMDXStatement(DatamartDefinition definition) {
		this.sqlFilterMap.clear()
		this.ignoreEntityGrouping = false
		this.sqlHasAggregate = false
		this.sqlHasOrder = false
		this.idMap.clear()
		var cube = (definition.source as DatamartCube)
		var body = ""
		var slice = ""
		var derived = ""
		var firstAxis = true
		var firstSlice = true
		var firstderived = true
		/* The following lines is to make a sorted list of the axis slicer having the selected slicer at the end of the list. 
		 * So a required where clause with selected slicer is much easier to create syntactically.*/ 
		var sortedAxisSlicer = cube.sortedAxisSlicerList
		for (axisslicer : sortedAxisSlicer) {
			if (axisslicer instanceof DatamartCubeAxis) {
				var crossJoinMeasure = false
				var crossJoinHierarchy = false
				var crossJoinSetAggregation = false
				var axis = (axisslicer as DatamartCubeAxis)
				if (firstAxis) {
					firstAxis = false
				} else {
					body = '''«body»,'''
				}
				if (axis.elements !== null) {
					var set = ""
					val usedHierarchies = <DatamartHierarchy>newArrayList
					val usedMeasures = <DatamartMeasure>newArrayList
					val usedDerivedMeasure = <DatamartDerivedMeasure>newArrayList
					val usedSetAggregation = <DatamartSetAggregation>newArrayList
					for (element : axis.elements) {
						if (element instanceof DatamartSetAggregation) {

							// if a set aggregation comes along, all is cross joined
							if (usedMeasures.size > 0 || usedDerivedMeasure.size > 0) {
								if (crossJoinMeasure) {
									set = '''«set»,'''
								}
								set = '''«IF cube.nonEmpty»NonEmptyCrossjoin«ELSE»CrossJoin«ENDIF»(«set»«createMeasures(
									usedMeasures, usedDerivedMeasure, false)»'''
								if (crossJoinMeasure) {
									set = '''«set»)'''
								}
								usedMeasures.clear
								usedDerivedMeasure.clear
								crossJoinSetAggregation = true
							}
							if (usedHierarchies.size > 0) {
								if (crossJoinHierarchy) {
									set = '''«set»,'''
								}
								set = '''«IF cube.nonEmpty»NonEmptyCrossjoin«ELSE»CrossJoin«ENDIF»(«set»«createHierarchies(
									usedHierarchies, false)»'''
								if (crossJoinHierarchy) {
									set = '''«set»)'''
								}
								usedHierarchies.clear
								crossJoinSetAggregation = true
							}
							var setAggregation = (element as DatamartSetAggregation)
							usedSetAggregation.add(setAggregation)
						}
						if (element instanceof DatamartHierarchy) {

							// if hierarchy changes to measure, a crossjoin is generated
							if (usedMeasures.size > 0 || usedDerivedMeasure.size > 0) {
								if (crossJoinMeasure) {
									set = '''«set»,'''
								}
								set = '''«IF cube.nonEmpty»NonEmptyCrossjoin«ELSE»CrossJoin«ENDIF»(«set»«createMeasures(
									usedMeasures, usedDerivedMeasure, false)»'''
								if (crossJoinMeasure) {
									set = '''«set»)'''
								}
								usedMeasures.clear
								usedDerivedMeasure.clear
								crossJoinMeasure = true
							}
							if (usedSetAggregation.size > 0) {
								if (crossJoinSetAggregation) {
									set = '''«set»,'''
								}
								set = '''«IF cube.nonEmpty»NonEmptyCrossjoin«ELSE»CrossJoin«ENDIF»(«set»«createSetAggregation(
									usedSetAggregation, false)»'''
								if (crossJoinSetAggregation) {
									set = '''«set»)'''
								}
								usedSetAggregation.clear
								crossJoinSetAggregation = true
							}
							var hierarchyLevel = (element as DatamartHierarchy)

							// if hierarchies are mixed, a crossjoin is generated
							var lastHierarchy = usedHierarchies.last
							if (!crossJoinHierarchy && (lastHierarchy === null ||
								hierarchyLevel.getHierarchyName.equals(lastHierarchy.getHierarchyName))) {
								usedHierarchies.add(hierarchyLevel)
							} else {
								if (crossJoinHierarchy) {
									set = '''«set»,'''
								}
								set = '''«IF cube.nonEmpty»NonEmptyCrossjoin«ELSE»CrossJoin«ENDIF»(«set»«createHierarchies(
									usedHierarchies, false)»'''
								if (crossJoinHierarchy) {
									set = '''«set»)'''
								}
								usedHierarchies.clear
								usedHierarchies.add(hierarchyLevel)
								crossJoinHierarchy = true
							}
						}
						if (element instanceof DatamartMeasure) {

							// if measure changes to hierarchy, a crossjoin is generated
							if (usedHierarchies.size > 0) {
								if (crossJoinMeasure) {
									set = '''«set»,'''
								}
								set = '''«IF cube.nonEmpty»NonEmptyCrossjoin«ELSE»CrossJoin«ENDIF»(«set»«createHierarchies(
									usedHierarchies, false)»'''
								if (crossJoinMeasure) {
									set = '''«set»)'''
								}
								usedHierarchies.clear
								crossJoinMeasure = true
							}
							if (usedSetAggregation.size > 0) {
								if (crossJoinSetAggregation) {
									set = '''«set»,'''
								}
								set = '''«IF cube.nonEmpty»NonEmptyCrossjoin«ELSE»CrossJoin«ENDIF»(«set»«createSetAggregation(
									usedSetAggregation, false)»'''
								if (crossJoinSetAggregation) {
									set = '''«set»)'''
								}
								usedSetAggregation.clear
								crossJoinSetAggregation = true
							}
							var measure = (element as DatamartMeasure)
							usedMeasures.add(measure)
						}
						if (element instanceof DatamartDerivedMeasure) {

							// if measure changes to hierarchy, a crossjoin is generated
							if (usedHierarchies.size > 0) {
								if (crossJoinMeasure) {
									set = '''«set»,'''
								}
								set = '''«IF cube.nonEmpty»NonEmptyCrossjoin«ELSE»CrossJoin«ENDIF»(«set»«createHierarchies(
									usedHierarchies, false)»'''
								if (crossJoinMeasure) {
									set = '''«set»)'''
								}
								usedHierarchies.clear
								crossJoinMeasure = true
							}
							if (usedSetAggregation.size > 0) {
								if (crossJoinSetAggregation) {
									set = '''«set»,'''
								}
								set = '''«IF cube.nonEmpty»NonEmptyCrossjoin«ELSE»CrossJoin«ENDIF»(«set»«createSetAggregation(
									usedSetAggregation, false)»'''
								if (crossJoinSetAggregation) {
									set = '''«set»)'''
								}
								usedSetAggregation.clear
								crossJoinSetAggregation = true
							}
							var measure = (element as DatamartDerivedMeasure)
							usedDerivedMeasure.add(measure)
						}
					}
					if (crossJoinMeasure || crossJoinHierarchy || crossJoinSetAggregation) {
						set = '''«set»,'''
					}
					set = '''«set»«createHierarchies(usedHierarchies,
						cube.nonEmpty && !(crossJoinMeasure || crossJoinHierarchy || crossJoinSetAggregation))»'''
					usedHierarchies.clear
					set = '''«set»«createMeasures(usedMeasures, usedDerivedMeasure,
						cube.nonEmpty && !(crossJoinMeasure || crossJoinHierarchy || crossJoinSetAggregation))»'''
					usedMeasures.clear
					usedDerivedMeasure.clear
					set = '''«set»«createSetAggregation(usedSetAggregation,
						cube.nonEmpty && !(crossJoinMeasure || crossJoinHierarchy || crossJoinSetAggregation))»'''
					usedMeasures.clear
					if (crossJoinMeasure || crossJoinHierarchy || crossJoinSetAggregation) {
						set = '''«set»)'''
					}
					body = '''«body»«set» on «axis.axis.name»'''
				}
			}
			if (axisslicer instanceof DatamartSlicer) {
				var element = (axisslicer as DatamartSlicer).element
				val selected = (element instanceof Expression) && (element as Expression).selected
				if (firstSlice) {
					firstSlice = false
				} else if (selected) {
					slice = '''«slice»)*('''
				} else {
					slice = '''«slice»,'''
				}
				var newSlice = ""
				if (element instanceof DatamartHierarchy) {
					var hierarchy = (element as DatamartHierarchy)
					newSlice = hierarchy.getLevelName(true, true, true, null, true)
				}
				if (element instanceof DatamartMeasure) {
					newSlice = (element as DatamartMeasure).getMeasureName
				}
				if (selected){
					slice = '''«slice»{«newSlice»}'''
				} else {
					slice = '''«slice»«newSlice»'''
				}
			}
			if (axisslicer instanceof DatamartDefineDerivedMeasure) {
				if (firstderived) {
					firstderived = false
				}
				var derivedMeasure = (axisslicer as DatamartDefineDerivedMeasure)
				derived = '''«derived» member [Measures].[«derivedMeasure.name»] as «derivedMeasure.buildDerivedFormula»'''
			}
		}
		if (!firstSlice) {
			slice = ''' where («slice»)'''
		}
		if (!firstderived) {
			derived = '''with«derived» '''
		}

		return '''«derived»select «body» from «cube.cubeRef.name»«slice»'''
	}
	
	/**
	 * This method creates a sorted list of the axis slicer having the selected slicer at the end of the list. 
	 * So a required where clause with selected slicer is much easier to create syntactically.
	 */
	protected def ArrayList<DatamartCubeElement> getSortedAxisSlicerList(DatamartCube cube) {
		var selectedAxisSlicer = newArrayList()
		var nonSelectedAxisSlicer = newArrayList()
		var sortedAxisSlicer = newArrayList()
		for (axisslicer : cube.axisslicer) {
			if (axisslicer instanceof DatamartSlicer) {
				var element = (axisslicer as DatamartSlicer).element
				val selected = (element instanceof Expression) && (element as Expression).selected
				if (selected) {
					selectedAxisSlicer.add(axisslicer)
				} else {
					nonSelectedAxisSlicer.add(axisslicer)
				}
			} else {
				nonSelectedAxisSlicer.add(axisslicer)
			}
		}
		sortedAxisSlicer.addAll(nonSelectedAxisSlicer)
		sortedAxisSlicer.addAll(selectedAxisSlicer)
		sortedAxisSlicer
	}

	def isCrossJoined(DatamartDefinition definition) {
		if (!(definition.source instanceof DatamartCube)) {
			return false;
		}
		var cube = (definition.source as DatamartCube)
		var firstAxis = true
		for (axisslicer : cube.axisslicer) {
			if (axisslicer instanceof DatamartCubeAxis) {
				var crossJoinMeasure = false
				var crossJoinHierarchy = false
				var crossJoinSetAggregation = false
				var axis = (axisslicer as DatamartCubeAxis)
				if (firstAxis) {
					firstAxis = false
				}
				if (axis.elements !== null) {
					val usedHierarchies = <DatamartHierarchy>newArrayList
					val usedMeasures = <DatamartMeasure>newArrayList
					val usedDerivedMeasure = <DatamartDerivedMeasure>newArrayList
					val usedSetAggregation = <DatamartSetAggregation>newArrayList
					for (element : axis.elements) {
						if (element instanceof DatamartSetAggregation) {
							// if a set aggregation comes along, all is cross joined
							if (usedMeasures.size > 0 || usedDerivedMeasure.size > 0) {
								usedMeasures.clear
								usedDerivedMeasure.clear
								crossJoinSetAggregation = true
							}
							if (usedHierarchies.size > 0) {
								usedHierarchies.clear
								crossJoinSetAggregation = true
							}
							var setAggregation = (element as DatamartSetAggregation)
							usedSetAggregation.add(setAggregation)
						}
						if (element instanceof DatamartHierarchy) {
							// if hierarchy changes to measure, a crossjoin is generated
							if (usedMeasures.size > 0 || usedDerivedMeasure.size > 0) {
								usedMeasures.clear
								usedDerivedMeasure.clear
								crossJoinMeasure = true
							}
							if (usedSetAggregation.size > 0) {
								usedSetAggregation.clear
								crossJoinSetAggregation = true
							}
							var hierarchy = (element as DatamartHierarchy)

							// if hierarchies are mixed, a crossjoin is generated
							var lastHierarchy = usedHierarchies.last
							if (!crossJoinHierarchy && (lastHierarchy === null || ((lastHierarchy !== null) &&
								hierarchy.getHierarchyName.equals(lastHierarchy.getHierarchyName)))) {
								usedHierarchies.add(hierarchy)
							} else {
								usedHierarchies.clear
								usedHierarchies.add(hierarchy)
								crossJoinHierarchy = true
							}
						}
						if (element instanceof DatamartMeasure) {
							// if measure changes to hierarchy, a crossjoin is generated
							if (usedHierarchies.size > 0) {
								usedHierarchies.clear
								crossJoinMeasure = true
							}
							if (usedSetAggregation.size > 0) {
								usedSetAggregation.clear
								crossJoinSetAggregation = true
							}
							var measure = (element as DatamartMeasure)
							usedMeasures.add(measure)
						}
						if (element instanceof DatamartDerivedMeasure) {
							// if measure changes to hierarchy, a crossjoin is generated
							if (usedHierarchies.size > 0) {
								usedHierarchies.clear
								crossJoinMeasure = true
							}
							if (usedSetAggregation.size > 0) {
								usedSetAggregation.clear
								crossJoinSetAggregation = true
							}
							var measure = (element as DatamartDerivedMeasure)
							usedDerivedMeasure.add(measure)
						}
					}
					usedMeasures.clear
					usedDerivedMeasure.clear
					usedMeasures.clear
				}
				return (crossJoinMeasure || crossJoinHierarchy || crossJoinSetAggregation);
			}
		}
		return false;
	}

	/**
	 * <p>mdx syntax part for hierarchy names. If a hierarchy itself has no name in the cube definition, the dimension name is used instead</p> 
	 * 
	 */
	def String getHierarchyName(DatamartHierarchy hierarchy) {
		if (hierarchy.hierarchyRef.name === null) {
			if ((hierarchy.hierarchyRef !== null) && (hierarchy.hierarchyRef.eContainer as CubeDimension)!==null){
				return (hierarchy.hierarchyRef.eContainer as CubeDimension).name
			}
		} else {
			return '''«(hierarchy.hierarchyRef.eContainer as CubeDimension).name».«hierarchy.hierarchyRef.name»'''
		}
		return ""
	}

	/**
	 * <p>mdx syntax part for hierarchy names of except keyword. If a hierarchy itself has no name in the cube definition, the dimension name is used instead</p> 
	 * 
	 */
	def String getExceptHierarchyName(DatamartHierarchy hierarchy) {
		if (hierarchy.exceptRef.name === null) {
			return (hierarchy.exceptRef.eContainer as CubeDimension).name
		} else {
			return '''«(hierarchy.exceptRef.eContainer as CubeDimension).name».«hierarchy.exceptRef.name»'''
		}
	}

	/**
	 * <p>mdx syntax part for derived measure expressions.</p> 
	 * 
	 */
	def buildDerivedFormula(DatamartDefineDerivedMeasure derivedMeasure) {
		var formula = ""
		for (element : derivedMeasure.derivedElement) {
			formula = '''«formula»«element.evaluateExpression(null)»'''
		}
		return "'".concat(formula).concat("'")
	}

	/**
	 * <p>as the grammar makes use of expressions along an abstract syntax tree, we need this recursive dispatcher to synthesize resulting code.</p> 
	 * 
	 */
	// due to a possible bug in xtext 2.4.2 we must supply the extra entity as the model will find a wrong one from the attribute
	def String evaluateExpression(Expression element, DatamartEntity entity) {
		var evaluation = ""
		if (element.value !== null) {
			evaluation = element.value
		}
		if (element.numberValue !== null) {
			evaluation = element.numberValue
		}
		if (element.stringValue !== null) {
			evaluation = "'".concat(element.stringValue).concat("'")
		}
		if (element.filtered || element.selected || element.ranged) {
			evaluation = '''«DatamartFilter.decorate(((element.eContainer() as ConditionalExpression).left as DatamartAttributeBase).getAttributeName(entity))»'''
		}
		if (element.unreferenced) {
			var leftConditionExpression = (element.eContainer() as ConditionalExpression).left
			var LEntityFeature entityF = null
			if (leftConditionExpression instanceof DatamartReferenceBase){
				entityF = ((element.eContainer() as ConditionalExpression).left as DatamartReferenceBase).referenceRef
			} else if (leftConditionExpression instanceof DatamartAttributeBase){
				entityF = ((element.eContainer() as ConditionalExpression).left as DatamartAttributeBase).attributeRef
			}
			if (entityF !== null) {
				evaluation = '''«entityF.entity.name».«entityF.toColumnName» is null'''
			}
				
		}
		if (element.function !== null) {
			if (element.function instanceof DatamartFunction) {
				evaluation = '''«evaluation»«element.hierarchy.getLevelName(true, true, true, null, false)».«(element.function as DatamartFunction).
					function.name()»'''
			}
			if (element.function instanceof DatamartParameterFunction) {
				evaluation = '''«evaluation»«element.hierarchy.getLevelName(true, true, true, null, false)».«(element.function as DatamartParameterFunction).
					function.name()»(«(element.function as DatamartParameterFunction).parameter.value»)'''
			}
		}

		// derived functionless hierarchized measure
		if (element.hierarchy !== null && element.function === null) {
			evaluation = '''«evaluation»«element.hierarchy.getLevelName(true, true, true, null, false)»'''
		}
		if (element instanceof DatamartSetTuple) {
			if ((element as DatamartSetTuple).left.setFunction !== null) {
				if ((element as DatamartSetTuple).left.setFunction instanceof DatamartSetFunction) {
					evaluation = '''«evaluation»«((element as DatamartSetTuple).left.setFunction as DatamartSetFunction).
						setFunction.name()»(«(element as DatamartSetTuple).right.getLevelName(true, true, true, ((element as DatamartSetTuple).left.setFunction as DatamartSetFunction).setFunction.name(), false)»)'''
				}
				if ((element as DatamartSetTuple).left.setFunction instanceof DatamartSetParameterFunction) {
					evaluation = '''«evaluation»«((element as DatamartSetTuple).left.setFunction as DatamartSetParameterFunction).
						setFunction.name()»(«(element as DatamartSetTuple).right.getLevelName(true, true, true, ((element as DatamartSetTuple).left.setFunction as DatamartSetParameterFunction).
						setFunction.name(), false)»,«(((element as DatamartSetTuple).
						left.setFunction as DatamartSetParameterFunction).parameter as DatamartFunctionIntParameter).
						value»)'''
				}
			}
		}
		if (element instanceof DatamartMeasure) {
			evaluation = '''«(element as DatamartMeasure).getMeasureName»'''
		}
		if (element instanceof DatamartDerivedMeasure) {
			evaluation = '''«(element as DatamartDerivedMeasure).getDerivedMeasureName»'''
		}
		if (element instanceof DatamartHierarchy) {
			evaluation = '''«(element as DatamartHierarchy).getLevelName(true, true, true, null, false)»'''
		}
		if (element instanceof DatamartAttributeBase) {
			evaluation = '''«(element as DatamartAttributeBase).getAttributeName(entity)»'''
		}
		if (element instanceof Multiplication) {
			evaluation = '''«(element as Multiplication).left.evaluateExpression(entity)»'''
			evaluation = '''«evaluation»*'''
			evaluation = '''«evaluation»«(element as Multiplication).right.evaluateExpression(entity)»'''
		}
		if (element instanceof Division) {
			evaluation = '''«(element as Division).left.evaluateExpression(entity)»'''
			evaluation = '''«evaluation»/'''
			evaluation = '''«evaluation»«(element as Division).right.evaluateExpression(entity)»'''
		}
		if (element instanceof Addition) {
			evaluation = '''(«(element as Addition).left.evaluateExpression(entity)»'''
			evaluation = '''«evaluation»+'''
			evaluation = '''«evaluation»«(element as Addition).right.evaluateExpression(entity)»)'''
		}
		if (element instanceof Subtraction) {
			evaluation = '''(«(element as Subtraction).left.evaluateExpression(entity)»'''
			evaluation = '''«evaluation»-'''
			evaluation = '''«evaluation»«(element as Subtraction).right.evaluateExpression(entity)»)'''
		}
		if (element instanceof Conjunction) {
			evaluation = '''«(element as Conjunction).left.evaluateExpression(entity)»'''
			evaluation = '''«evaluation» and '''
			evaluation = '''«evaluation»«(element as Conjunction).right.evaluateExpression(entity)»'''
		}
		if (element instanceof Disjunction) {
			evaluation = '''(«(element as Disjunction).left.evaluateExpression(entity)»'''
			evaluation = '''«evaluation» or '''
			evaluation = '''«evaluation»«(element as Disjunction).right.evaluateExpression(entity)»)'''
		}
		if (element instanceof ConditionalExpression) {
			evaluation = '''«(element as ConditionalExpression).left.evaluateExpression(entity)»'''
			var optionalAllowed = true
			if ((element as ConditionalExpression).left instanceof DatamartAttributeBase) {
				var type = ((element as ConditionalExpression).left as DatamartAttributeBase).attributeRef.type
				// we cannot allow optional with date as we cannot mix * with date types
				if (type instanceof LDataType) {
					optionalAllowed = !type.date
				}
			}

			if ((element as ConditionalExpression).right.selected) {
				evaluation = '''«evaluation» in '''
				evaluation = '''«evaluation»(«(element as ConditionalExpression).right.evaluateExpression(entity)»)'''
			} else if ((element as ConditionalExpression).right.ranged) {
				evaluation = '''«(element as ConditionalExpression).right.evaluateExpression(entity)»'''
			} else if ((element as ConditionalExpression).right.unreferenced) {
				evaluation = '''«(element as ConditionalExpression).right.evaluateExpression(entity)»'''
			} else {
				evaluation = '''«evaluation» «(element as ConditionalExpression).operator.literal» '''
				evaluation = '''«evaluation»«(element as ConditionalExpression).right.evaluateExpression(entity)»'''
			}
			if ((element as ConditionalExpression).right.optional && optionalAllowed) {
				evaluation = '''(«(element as ConditionalExpression).right.evaluateExpression(entity)»=«IDataMart.SQLFILTERNOTHING» or «evaluation»)'''
			}
		}
		if (element instanceof DatamartMemberTuple) {
			evaluation = '''(«(element as DatamartMemberTuple).right.evaluateExpression(entity)»'''
			evaluation = '''«evaluation»,'''
			evaluation = '''«evaluation»«(element as DatamartMemberTuple).left.evaluateExpression(entity)»)'''
		}
		if (element instanceof DatamartAggregation) {
			if ((element as DatamartAggregation).left.aggregation !== null) {
				if ((element as DatamartAggregation).left.aggregation instanceof DatamartAggregationFunction) {
					evaluation = '''«((element as DatamartAggregation).left.aggregation as DatamartAggregationFunction).
						aggregation.name()»('''
					if ((element as DatamartAggregation).left.set instanceof DatamartHierarchy) {
						evaluation = '''«evaluation»«((element as DatamartAggregation).left.set as DatamartHierarchy).
							getLevelName(true, true, true, null, false)»'''
					}
					if ((element as DatamartAggregation).left.set instanceof DatamartSetTuple) {
						evaluation = '''«evaluation»«((element as DatamartAggregation).left.set as DatamartSetTuple).
							evaluateExpression(entity)»'''
					}
				}
			}
			evaluation = '''«evaluation»,'''
			evaluation = '''«evaluation»«(element as DatamartAggregation).right.evaluateExpression(entity)»)'''
		}
		if (element instanceof DatamartSetAggregation) {
			if ((element as DatamartSetAggregation).left.aggregation !== null) {
				if ((element as DatamartSetAggregation).left.aggregation instanceof DatamartSetAggregationFunction) {
					evaluation = '''«((element as DatamartSetAggregation).left.aggregation as DatamartSetAggregationFunction).
						aggregation.name()»('''
					if ((element as DatamartSetAggregation).left.set instanceof DatamartHierarchy) {
						evaluation = '''«evaluation»«((element as DatamartSetAggregation).left.set as DatamartHierarchy).
							getLevelName(true, true, true, null, false)»'''
					}
					if ((element as DatamartSetAggregation).left.set instanceof DatamartSetTuple) {
						evaluation = '''«evaluation»«((element as DatamartSetAggregation).left.set as DatamartSetTuple).
							evaluateExpression(entity)»'''
					}
				}
			}
			evaluation = '''«evaluation»,«(((element as DatamartSetAggregation).left.aggregation as DatamartSetAggregationFunction).
				parameter as DatamartFunctionIntParameter).value»,'''
			evaluation = '''«evaluation»«(element as DatamartSetAggregation).right.evaluateExpression(entity)»)'''
		}
		return evaluation
	}

	/**
	 * <p>mdx syntax part for attribute names, full qualified and aliased> 
	 * 
	 */
	def getAttributeName(DatamartAttributeBase attribute, DatamartEntity entity) {
		if (entity !== null) {
			return '''«entity.getEntityAlias».«attribute.attributeRef.toColumnName»'''
		} else {
			return '''«(attribute.attributeRef.eContainer as LEntity).name».«attribute.attributeRef.toColumnName»'''
		}
	}

	/**
	 * <p>mdx syntax part for measure names.</p> 
	 * 
	 */
	def getMeasureName(DatamartMeasure measure) {
		return '''[Measures].[«measure.measureRef.name»]'''
	}

	/**
	 * <p>mdx syntax part for derived measure names.</p> 
	 * 
	 */
	def getDerivedMeasureName(DatamartDerivedMeasure measure) {
		return '''[Measures].[«measure.derivedRef.name»]'''
	}

	/**
	 * <p>mdx syntax part for dimension names.</p> 
	 * 
	 */
	def getDimensionName(DatamartHierarchy hierarchy, Boolean withBrackets) {
		var dim = ""
		if (hierarchy.hierarchyRef.eContainer !== null) {
			if (hierarchy.hierarchyRef.name === null) {
				dim = '''«IF withBrackets»[«ENDIF»«(hierarchy.hierarchyRef.eContainer as CubeDimension).name»«IF withBrackets»]«ENDIF»'''
			} else {
				dim = '''«IF withBrackets»[«ENDIF»«(hierarchy.hierarchyRef.eContainer as CubeDimension).name».«hierarchy.hierarchyRef.name»«IF withBrackets»]«ENDIF»'''
			}
		}
		return dim
	}
	
	/**
	 * <p>mdx syntax part for level names.</p> 
	 * 
	 */
	def getLevelName(DatamartHierarchy hierarchy, Boolean withBrackets, Boolean withFiltermask, Boolean withFunctions,
		String functionEnumName, Boolean isFilter) {
		var text = '''«hierarchy.getDimensionName(withBrackets)»'''
		if (hierarchy.level instanceof DatamartHierarchyLevelSingle){
			if ((hierarchy.level as DatamartHierarchyLevelSingle).levelRef !== null) {
					text = '''«(hierarchy.level as DatamartHierarchyLevelSingle).getLevelName(hierarchy, withFiltermask, withBrackets, withFunctions, functionEnumName, isFilter)»'''
				}
		} else if (hierarchy.level instanceof DatamartHierarchyLevelMultiple) {
			text = ""
			var firstTupel = true
			if ((hierarchy.level as DatamartHierarchyLevelMultiple).levels.size > 0) {
				for (hierarchyLevelSingle : (hierarchy.level as DatamartHierarchyLevelMultiple).levels){
					if (hierarchyLevelSingle.levelRef !== null) {
						if (firstTupel){
							firstTupel = false
						} else {
							text = '''«text»,'''
						}
						var hierarchyLevelText = '''«hierarchyLevelSingle.getLevelName(hierarchy, withFiltermask, withBrackets, withFunctions, functionEnumName, isFilter)»'''
						text = '''«text» «hierarchyLevelText»'''
					}
				}
			}
		}
		if (!hierarchy.existLevelDefinition) {
			if (hierarchy.hierarchyRef.eContainer !== null &&
				!(hierarchy.hierarchyRef.eContainer as CubeDimension).typeTime) {
				if (hierarchy.all) {
					if(hierarchy.except && !isFilter) {
						text = '''«text».Children'''
					} else {
						text = '''«text».Members'''
					}
				} else if (hierarchy.allMember && hierarchy.hierarchyRef.allMemberName) {
					text = '''«text».[«hierarchy.hierarchyRef.allMemberNameValue.replaceAll("\"","")»]'''
				} else if (hierarchy.allLevels) {
					text = '''«text».AllMembers'''
				} else if (hierarchy.defaultMember && hierarchy.hierarchyRef.defaultMember) {
					text = '''«text»text'''
				} else {
					if (hierarchy.hierarchyRef.defaultMember) {
						text = '''«text».Defaultmember'''
					} else {
						text = '''«text».Members'''
					}
				}
			}
		}
		if (hierarchy.except && !isFilter) {
			text = '''Except(«text»,{«DatamartFilter.FILTER_PLACEHOLDER»«hierarchy.getDimensionName(true)».«IF withBrackets»[«ENDIF»«hierarchy.exceptRef.name»«IF withBrackets»]«ENDIF»«DatamartFilter.FILTER_PLACEHOLDER»})'''
		}
		if (hierarchy.ordered) {
			if (hierarchy.orderRef instanceof DatamartMeasure) {
				text = '''Order(«text»,«(hierarchy.orderRef as DatamartMeasure).getMeasureName»,«IF hierarchy.
					descending»Desc«ELSE»Asc«ENDIF»)'''
			} else {
				text = '''Order(«text»,«(hierarchy.orderRef as DatamartDerivedMeasure).getDerivedMeasureName»,«IF hierarchy.
					descending»Desc«ELSE»Asc«ENDIF»)'''
			}
		}
		return '''«text»'''
	}
	
	def String getLevelName(DatamartHierarchyLevelSingle hierarchyLevelSingle, DatamartHierarchy hierarchy, Boolean withBrackets, Boolean withFiltermask, Boolean withFunctions, String functionEnumName, Boolean isFilter) {
		var text = '''«hierarchy.getDimensionName(withBrackets)».'''
		if (hierarchyLevelSingle.levelRef !== null) {
			if (hierarchyLevelSingle.filtered && withFiltermask) {
				text = '''«DatamartFilter.FILTER_PLACEHOLDER»«text»'''
			}
			if (hierarchyLevelSingle.selected && withFiltermask) {
				text = '''«DatamartFilter.FILTER_PLACEHOLDER»«text»'''
			}
			if (withBrackets) {
				text = '''«text»['''
			}
			text = '''«text»«hierarchyLevelSingle.levelRef.name»'''
			if (withBrackets) {
				text = '''«text»]'''
			}
			if (hierarchyLevelSingle.filtered && withFiltermask) {
				text = '''«text»«DatamartFilter.FILTER_PLACEHOLDER»'''
			}
			if (hierarchyLevelSingle.selected && withFiltermask) {
				text = '''«text»«DatamartFilter.FILTER_PLACEHOLDER»'''
			}
//			if (hierarchyLevelSingle.filtered || hierarchyLevelSingle.selected) {
//				if (!isFilter && withFunctions) {
//					text = '''«text».Children'''
//				}
//			} else 
			if (!hierarchyLevelSingle.filtered && !hierarchyLevelSingle.selected && withFunctions) {
				if (!(hierarchyLevelSingle.eContainer instanceof DatamartMemberTuple) && !(SetFunctionEnum.PERIODSTODATE.getName.equals(functionEnumName))) {
					text = '''«text».Members'''
				}
			}
		}
		text
	}

	/**
	 * <p>helper for entity names.</p> 
	 * 
	 */
	def getEntityAlias(DatamartEntity entity) {
		return entity.entityRef.getEntityAlias(entity)
	}

	/**
	 * <p>helper for entity names including aliases when used in recursive join relations.</p> 
	 * 
	 */
	def String getEntityAlias(LEntity entityRef, DatamartEntity entity) {
		var name = ""
		if (entityRef.hasSuperType) {
			name = entityRef.superType.getName()
		} else {
			name = entityRef.getName()
		}
		var alias = name
		if (entity.eContainer instanceof DatamartNavigation) {
			alias = '''«alias»_«(entity.eContainer as DatamartNavigation).joinRef.ref.name»'''
		}
		return alias.shortenTo30Char
	}
	
	def private String shortenTo30Char(String text) {
		var result = text
		val vocals = #["a", "e", "i", "o", "u", "A", "E", "I", "O", "U", "y", "Y"]
		for (i : 0 ..< vocals.length) {
			if (result.length()  > 30) {
				result = result.replaceAll(vocals.get(i), "")
			} else {
				return result
			}
		}
		return result
	}

	/**
	 * <p>helper for entity names including aliases when used in recursive join relations.</p> 
	 * 
	 */
	def entityAlias2(DatamartEntity entity) {
		var name = ""
		var entityRef = entity.entityRef
		if (entityRef.hasSuperType) {
			name = entityRef.superType.getName()
		} else {
			name = entityRef.getName()
		}
		var alias = name
				if (entity.eContainer instanceof DatamartNavigation) {
			alias = '''«alias»_«(entity.eContainer as DatamartNavigation).joinRef.ref.name»'''
		}
		return alias.shortenTo30Char
	}

	/**
	 * <p>main method to build sql statements bases on entities and roles.</p> 
	 * 
	 */
	def createSQLStatements(DatamartDefinition definition, DatamartEntity entity,
		Map<String, LAttribute> typesMap, Map<String, DatamartEntity> entityMap) {
		return definition.createSQLStatement(typesMap, entityMap)
	}

	/**
	 * <p>main method to build sql statements bases on entities and a role.</p> 
	 * 
	 */
	def createSQLStatement(DatamartDefinition definition, Map<String, LAttribute> typesMap, Map<String, DatamartEntity> entityMap) {
		this.sqlFilterMap.clear()
		this.sqlHasAggregate = false
		this.sqlHasOrder = false
		var groupingList = <String>newArrayList
		this.idMap.clear()
		this.ignoreEntityGrouping = false
		var attributes = <String, Triple<String, Integer, Boolean>>newLinkedHashMap
		var conditions = <String>newArrayList
		var orderings = <String, Integer>newLinkedHashMap
		var entity = (definition.source as DatamartEntity)
		var used = entity.createEntity(null, null, entityMap, attributes, conditions, orderings, typesMap, groupingList)
		var select = attributes.createSelect(definition, entityMap)
		var condition = conditions.createCondition
		var ordering = orderings.createOrdering
		var grouping = groupingList.createGrouping
		if (select.empty) {
			return ""
		}
		return '''select «select» from «used»«IF conditions.size > 0» where «condition»«ENDIF»«IF this.
			sqlHasAggregate» group by «grouping»«ENDIF»«IF this.sqlHasOrder» order by «ordering»«ENDIF»'''
	}

	/**
	 * <p>sql syntax part for group by.</p> 
	 * 
	 */
	def createGrouping(List<String> grouping) {
		var groupingString = ""
		for (String groupBy : grouping) {
			groupingString = '''«IF !groupingString.empty»«groupingString»,«ENDIF»«groupBy»'''
		}
		return groupingString
	}

	/**
	 * <p>sql syntax part for where.</p> 
	 * 
	 */
	def createCondition(ArrayList<String> conditions) {
		var conditionString = ""
		for (String condition : conditions) {
			conditionString = '''«IF !conditionString.empty»«conditionString» and «ENDIF»(«condition»)'''
		}
		return conditionString
	}

	/**
	 * <p>sql syntax part for order.</p> 
	 * 
	 */
	def createOrdering(Map<String, Integer> orderings) {
		sqlHasOrder = orderings.size > 0
		return orderings.entrySet.stream.sorted(Map.Entry.comparingByValue.reversed).map[it.key].collect(
			Collectors.joining(","))
	}

	/**
	 * <p>sql syntax part for select.</p> 
	 * 
	 */
	def createSelect(Map<String, Triple<String, Integer, Boolean>> attributes, DatamartDefinition definition, Map<String, DatamartEntity> entityMap) {
		var selectString = ""
		if (definition.source instanceof DatamartEntity){
				if (!(definition.source as DatamartEntity).suppressAttributes){
				selectString = attributes.entrySet.sortWith[e1, e2|return (e2.value.second - e1.value.second)].map [
					if(it.value.third) {
						"distinct " + it.key + " as \\\"" + it.value.first + "\\\""
					} else {
						it.key + " as \\\"" + it.value.first + "\\\""
					}
				].join(",")
				selectString = '''«selectString»'''
			}
		}
		datamartDtoMapper = "new DatamartDtoMapper()"
		if (definition !== null) {
			if (!ignoreEntityGrouping) {
				for (entity : entityMap.values) {
					var entityName = getEntityAlias(entity)
					var entityRef = entity.entityRef
					var dtoFqn = EntityUtils.getDtoFQNForLEntity(entityRef)
					var idColumn = DatamartDefinitionUtil.getEntityIdAliasName(entityRef)
					var validColumn = DatamartDefinitionUtil.getEntityValidAliasName(entityRef)
					var currentColumn = DatamartDefinitionUtil.getEntityCurrentAliasName(entityRef)
					if (entityRef.primaryKeyAttribute !== null) {
						selectString = '''«selectString»«IF !selectString.empty»,«ENDIF»«entityName».«entityRef.primaryKeyAttribute.name» as \"«idColumn»\"«IF validColumn !== null»,«entityName».validfrom as \"«validColumn»\",«entityName».«entityRef.currentAttribute.toColumnName» as \"«currentColumn»\"«ENDIF»'''
						if (entityRef.hasSuperType) {
							idMap.put(idColumn, entityRef.superType)
							idEntityAliasMap.put(idColumn, entityRef.superType.name)
						} else {
							idMap.put(idColumn, entityRef)
							idEntityAliasMap.put(idColumn, entityName)
						}
						datamartDtoMapper = '''
						«datamartDtoMapper»
						.add("«dtoFqn»", "«entityRef.primaryKeyAttribute.name»", EType.«dtType.getBasicType(entityRef.idAttributeType.name)», "«idColumn»")
						«IF validColumn !== null».add("«dtoFqn»", "validfrom", EType.«dtType.getBasicType("long")», "«validColumn»")
						.add("«dtoFqn»", "«entityRef.currentAttribute.name»", EType.«dtType.getBasicType("boolean")», "«currentColumn»")
						«ENDIF»
						'''
					}
				}
			}
		}
		return selectString
	}

	def hasSuperType(DatamartEntity entity) {
		return entity.entityRef.hasSuperType
	}

	def hasSuperType(LEntity entityRef) {
		return (entityRef.superType !== null) && !entityRef.superType.mappedSuperclass && !entityRef.superType.equals(entityRef)
	}

	/**
	 * <p>recursive entity relation builder.</p> 
	 * 
	 */
	def String createEntity(DatamartEntity entity, DatamartEntity parent, DatamartNavigation parentNavigation, Map<String, DatamartEntity> entityMap,
		Map<String, Triple<String, Integer, Boolean>> attributes, List<String> conditions,
		LinkedHashMap<String, Integer> orderings, Map<String, LAttribute> typesMap, List<String> groupingList) {
		var body = ""
		var tableName = entity.entityRef.toTableName
		if (entity.hasSuperType) {
			conditions.add('''«entity.getEntityAlias».disc='«entity.entityRef.toTableName»' ''')
			tableName = entity.entityRef.superType.toTableName
		}
		body = '''«body»«entity.entityRef.persistenceInfo.schemaName.provideSchemaName»«tableName» «entity.entityAlias2»'''
		if (parent !== null && parentNavigation !== null) {
			body = ''' left join «body»«entity.createJoin(parent, parentNavigation)»'''
		}
		if (entity.hasSuperType) {
			body = '''«body» left join «entity.createSupertypeJoin»'''
		}
		for (DatamartNavigation navigation : entity.navigations) {
			if (navigation instanceof DatamartMember) {
				body = '''«body»«(navigation as DatamartMember).datamartEntity.createEntity(entity, navigation, entityMap,
					attributes, conditions, orderings, typesMap, groupingList)»'''
			}
			if (navigation instanceof DatamartOwner) {
				body = '''«body»«(navigation as DatamartOwner).datamartEntity.createEntity(entity, navigation, entityMap,
					attributes, conditions, orderings, typesMap, groupingList)»'''
			}
		}
		// Required for the creation of the sql statement with at least the id attribute. 
		entityMap.put(entity.getEntityAlias, entity)
		// -----------------------------------------------------------------------------
		if (entity.attributes.empty && !entity.suppressAttributes) {
			for (attr : entity.entityRef.allAttributes) {
				if (entity.entityRef.isNormalAttribute(attr)) {
					var datamartAttr = DatamartDSLFactory.eINSTANCE.createDatamartAttribute
					datamartAttr.attributeRef = attr
					entity.recurAttribute(null, datamartAttr, false, datamartAttr.distinct, "", defaultColumnWeight, attributes, typesMap, groupingList)
				}
			}
		} else {
			for (DatamartAttribute attribute : entity.attributes) {
				var columnWeight = defaultColumnWeight
				if (attribute.hasColumnWeight) {
					columnWeight = attribute.columnWeight
				}
				entity.recurAttribute(null, attribute, attribute.aggregated, attribute.distinct, attribute.aggregate.getName(), columnWeight,
					attributes, typesMap, groupingList)
			}
		}
		for (DatamartCondition condition : entity.conditions) {
			conditions.add(condition.condition.evaluateExpression(entity))
		}
		for (DatamartOrder order : entity.ordering) {
			var Integer columnWeight
			if (order.hasColumnWeight) {
				columnWeight = order.columnWeight
			} else {
				columnWeight = defaultColumnWeight
			}
			orderings.put(entity.getEntityAlias + "." + order.orderBy.toColumnName + " " + order.orderHow.getName(),
				columnWeight)
		}
		return body
	}

	def void recurAttribute(DatamartEntity entity, String beanPrefix, DatamartAttribute datamartAttr, boolean aggregated,boolean distinct,
		String aggregateName, Integer columnWeight, Map<String, Triple<String, Integer, Boolean>> attributes,
		Map<String, LAttribute> typesMap, List<String> groupingList) {
		var attr = datamartAttr.attributeRef
		var LScalarType type = attr.type
		if (type instanceof LBean) {
			var bean = type as LBean
			for (battr : bean.allAttributes) {
				if (battr.type instanceof LBean) {
					entity.recurAttribute(attr.name, battr, aggregated, distinct, aggregateName, columnWeight, attributes,
						typesMap, groupingList, attr)
				} else {
					entity.addAttribute(attr.name, battr, aggregated, distinct, aggregateName, columnWeight, attributes,
						typesMap, groupingList, attr)
				}
			}
		} else {
			entity.addAttribute(null, datamartAttr, aggregated, distinct, aggregateName, columnWeight, attributes, typesMap, groupingList)
		}
	}

	private def void recurAttribute(DatamartEntity entity, String beanPrefix, LAttribute attr, boolean aggregated, boolean distinct,
		String aggregateName, Integer columnWeight, Map<String, Triple<String, Integer, Boolean>> attributes,
		Map<String, LAttribute> typesMap, List<String> groupingList, LAttribute parentBean) {
		var LScalarType type = attr.type
		if (type instanceof LBean) {
			var newBeanPrefix = '''«IF beanPrefix !== null»«beanPrefix»_«ENDIF»«attr.name»_'''
			var bean = type as LBean
			for (battr : bean.allAttributes) {
				if (battr.type instanceof LBean) {
					entity.recurAttribute(newBeanPrefix, battr, aggregated, distinct, aggregateName, columnWeight, attributes,
						typesMap, groupingList, attr)
				} else {
					entity.addAttribute(newBeanPrefix, battr, aggregated, distinct, aggregateName, columnWeight, attributes,
						typesMap, groupingList, attr)
				}
			}
		} else {
			entity.addAttribute(null, attr, aggregated, distinct, aggregateName, columnWeight, attributes, typesMap, groupingList, parentBean)
		}
	}

	def aliasedColumnName(DatamartEntity entity, LAttribute attr, String beanPrefix, LAttribute parentBean) {
		var name = ""
		var entityRef = entity.entityRef
		if (entityRef.hasSuperType) {
			if (entityRef.superType.allAttributes.contains(attr) || parentBean !== null && entityRef.superType.allAttributes.contains(parentBean)) {
				name = entityRef.superType.getName()
			} else {
				name = entityRef.getName()
			}
		} else {
			name = entityRef.getName()
		}
		var alias = name
		if (entity.eContainer instanceof DatamartNavigation) {
			alias = '''«alias»_«(entity.eContainer as DatamartNavigation).joinRef.ref.name»'''
		}
		return '''«alias.shortenTo30Char».«attr.toColumnName.getBeanName(beanPrefix)»'''
	}

	def void addAttribute(DatamartEntity datamartEntity, String beanPrefix, DatamartAttribute datamartAttr, boolean aggregated, boolean distinct,
		String aggregateName, Integer columnWeight, Map<String, Triple<String, Integer, Boolean>> attributes,
		Map<String, LAttribute> typesMap, List<String> groupingList) {
		var attr = datamartAttr.attributeRef
		datamartEntity.addAttribute(beanPrefix, attr, DatamartAttributeUtil.getAliasedAttributeName(datamartAttr), aggregated, distinct, aggregateName, columnWeight, attributes, typesMap, groupingList, null)
	}

	def void addAttribute(DatamartEntity datamartEntity, String beanPrefix, LAttribute attr, boolean aggregated, boolean distinct,
		String aggregateName, Integer columnWeight, Map<String, Triple<String, Integer, Boolean>> attributes,
		Map<String, LAttribute> typesMap, List<String> groupingList, LAttribute parentBean) {
		var attributeName = '''«attr?.name?.replace("^", "")»'''
		datamartEntity.addAttribute(beanPrefix, attr, attributeName, aggregated, distinct, aggregateName, columnWeight, attributes, typesMap, groupingList, parentBean)
	}

	private def void addAttribute(DatamartEntity datamartEntity, String beanPrefix, LAttribute attr, String attributeName, boolean aggregated, boolean distinct,
		String aggregateName, Integer columnWeight, Map<String, Triple<String, Integer, Boolean>> attributes,
		Map<String, LAttribute> typesMap, List<String> groupingList, LAttribute parentBean) {
		var entity = datamartEntity.entityRef
		var key = datamartEntity.aliasedColumnName(attr, beanPrefix, parentBean)
		var newAttributeName = attributeName.getBeanName(beanPrefix)
		var typesAttr = ""
		if (entity.hasSuperType) {
			if (entity.superType.allAttributes.contains(attr) || parentBean !== null && entity.superType.allAttributes.contains(parentBean)) {
				typesAttr = '''«entity.superType.name».«newAttributeName»'''
			} else {
				typesAttr = '''«entity.name».«newAttributeName»'''
			}
		} else {
			typesAttr = '''«entity.name».«newAttributeName»'''
		}
		typesMap.put(typesAttr, attr);
		if (aggregated) {
			key = '''«aggregateName»(«key»)'''
			this.sqlHasAggregate = true;
			attributes.put(key.toString,
				new Triple(newAttributeName, columnWeight, new Boolean(distinct)))
			this.ignoreEntityGrouping = true
		} else {
			groupingList.add(key.toString)
			attributes.put(key.toString,
				new Triple(newAttributeName, columnWeight, distinct))
		}
	}

	private def getBeanName(String attributeName, String beanPrefix) {
		return '''«IF beanPrefix !== null»«beanPrefix»_«ENDIF»«attributeName»'''
	}
	
	def getProvideSchemaName(
		String schemaName) '''«IF schemaName !== null && schemaName.length > 0»«schemaName».«ENDIF»'''

	/**
	 * <p>sql syntax part for joined relations.</p> 
	 * 
	 */
	def createJoin(DatamartEntity entity, DatamartEntity parent, DatamartNavigation navigation) {
		var join = ""
		var joinRef = navigation.joinRef
		if (joinRef instanceof DatamartReference) {
			var name = joinRef.ref.toColumnName
			if(joinRef.ref.type instanceof LEntity && (joinRef.ref.type as LEntity).isHistorizedOrTimedependentWithParent) {
				name = name.concat("_ID")
			}
			if (navigation instanceof DatamartMember) {
				// determination of the right column name of the foreign key id required for the join 
				// using the name of the reference instead of the name joinref and checking the names 
				// of the navigation ref with the ref opposite.
				var refs = navigation.datamartEntity.entityRef.allReferences
				for (ref : refs) {
					if (ref.type !== null && ref.type.equals(parent.entityRef) && (ref.opposite.name.equals(joinRef.ref.name))) {
						name = ref.toColumnName
						if(ref.type instanceof LEntity && (ref.type as LEntity).isHistorizedOrTimedependentWithParent) {
							name = name.concat("_ID")
						}
					}
				}
				join = '''«entity.getEntityAlias».«name»=«parent.getEntityAlias».«parent.entityRef.idAttributeName»'''
			} else if (navigation instanceof DatamartOwner) {
				join = '''«entity.getEntityAlias».«entity.entityRef.idAttributeName»=«parent.getEntityAlias».«name»'''
			}
			join = ''' on(«join»)'''
		}
	}

	def createSupertypeJoin(DatamartEntity entity) {
		var subTypeAlias = entity.entityRef.name
		var join = '''«subTypeAlias».«entity.entityRef.idAttributeName»=«entity.entityRef.superType.getEntityAlias(entity)».«entity.entityRef.superType.idAttributeName»'''
		join = '''«entity.entityRef.persistenceInfo.schemaName.provideSchemaName»«entity.entityRef.toTableName» «subTypeAlias» on(«join»)'''
	}

	/**
	 * get a set of entities used by the datamart
	 */
	def Set<LEntity> findAllEntities(DatamartDefinition datamart) {
		var entities = new HashSet<LEntity>()
		switch (datamart.source) {
			DatamartEntity: findAllEntites(datamart.source as DatamartEntity, entities)
			DatamartCube: findAllEntites(datamart.source as DatamartCube, entities)
		}
		return entities
	}

	/**
	 * add all entities used by the datamart entity into the given set of entities
	 */
	def void findAllEntites(DatamartEntity datamartEntity, Set<LEntity> entities) {
		entities.add(datamartEntity.entityRef)
		for (navigation : datamartEntity.navigations) {
			findAllEntites(navigation.datamartEntity, entities)
		}
	}

	/**
	 * add all entities used by the datamart cube into the given set of entities
	 */
	def void findAllEntites(DatamartCube datamartCube, Set<LEntity> entities) {
		entities.add(datamartCube.cubeRef.cubeTypeEntity.entityRef.entityValue)
		for (dimensionUsage : datamartCube.cubeRef.cubeTypeEntity.dimensionUsages) {
			for (hierarchy : dimensionUsage.sourceValue.hierarchies) {
				entities.add(hierarchy.cubeDimEntity.entityRef.entityValue)
			}
		}
	}

	def List<DatamartAttribute> allEntityAttributes(DatamartDefinition datamart) {
		var attributes = <DatamartAttribute>newArrayList
		if (datamart.source instanceof DatamartEntity) {
			allEntityAttributes(datamart.source as DatamartEntity, "", attributes)
		}
		return attributes
	}

	/**
	 * add all entities used by the datamart entity into the given set of entities
	 */
	def void allEntityAttributes(DatamartEntity datamartEntity, String refPrefix, ArrayList<DatamartAttribute> attributes) {
		var entityAttributes = datamartEntity.entityAttributes
		for (navigation : datamartEntity.navigations) {
			allEntityAttributes(navigation.datamartEntity, navigation.joinRef.ref.name, attributes)
		}
		var primary = datamartEntity.entityRef.primaryKeyAttribute
		var attribute = DatamartDSLFactory.eINSTANCE.createDatamartAttribute
		attribute.attributeRef = primary
		attribute.aliased = true
		attribute.aliasName = '''«refPrefix»«IF primary!==null»«IF refPrefix.empty»«primary.name.toFirstLower»«ELSE»«primary.name.toFirstUpper»«ENDIF»«ENDIF»'''
		entityAttributes.add(attribute)
		attributes.addAll(entityAttributes)
	}
	
	private def entityAttributes(DatamartEntity entity) {
		var entityAttributes = newArrayList
		for (attribute : entity.attributes) {
			if(entity.entityRef.isNormalAttribute(attribute.attributeRef)) {
				entityAttributes.add(attribute)
			}
		} 
		if (entityAttributes.empty && !entity.suppressAttributes) {
			for (attr : entity.entityRef.allAttributes) {
				if(entity.entityRef.isNormalAttribute(attr)) {
					entityAttributes.add(attr.createDatamartAttribute)
				}
			}
		}
		entityAttributes
	}
	
	private def createDatamartAttribute(LEntityAttribute attr){
		var datamartAttr = DatamartDSLFactory.eINSTANCE.createDatamartAttribute
		datamartAttr.attributeRef = attr
		var datamartAxis = DatamartDSLFactory.eINSTANCE.createDatamartAxis
		datamartAxis.name = AxisEnum.COLUMNS
		datamartAttr.axis = datamartAxis
		datamartAttr
	}
}