public String getFilterWhereClause(VDimension dimension) {
String tableName = dimension.getTableName();
// StringBuffer from = new StringBuffer("FROM " + tableName + " AS " + tableName);
StringBuffer where = new StringBuffer();
VDimension oldDimension = dimension;
boolean filtered = true;
while (dimension.isDependent() && filtered) {
dimension = (VDimension) dimension.getParent();
boolean appended = false;
for (Filterable filter : dimension.getSelections()) {
Long selection = (Long) filter.getId();
appended = true;
filtered = false;
if (where.length() > 0) {
where.append(" OR ");
} else {
where.append("(");
}
if (oldDimension.isVisible()) {
where.append(tableName).append(".parent = ").append(selection);
} else {
where
.append(tableName)
.append(".")
.append(dimension.getForeignKey())
.append(" = ")
.append(selection);
}
}
if (appended) {
where.append(")");
}
}
if (where.length() > 0) {
where.insert(0, " AND ");
}
// return from.toString() + where.toString();
return where.toString();
}
public String createItemCountSQL(VDimension dimension) {
String sql = "SELECT DISTINCT " + lastTableName + ".name FROM " + lastTableName;
if (dimension.getSelections() != null && dimension.getSelectionSize() > 0) {
sql += " WHERE (";
int size = dimension.getSelections().size();
for (Filterable filter : dimension.getSelections()) {
sql += lastTableName + ".id = " + filter.getId();
if (size > 1) {
sql += " OR ";
size--;
}
}
sql += ")";
}
if (LOG.isDebugEnabled()) {
LOG.debug(sql);
}
return sql;
}
/**
* @param dimension
* @param blueprint
* @return
*/
public String createChartQuery(VDimension dimension, VDimension blueprint) {
String cubeName = cube.getTableName();
String bluepName = blueprint.getTableName();
String tableName = dimension.getTableName();
if (history.size() <= 1) {
if (!dimension.equals(blueprint)) {
from.append(cubeName).append(", ").append(tableName).append(", ").append(bluepName);
where
.append(tableName)
.append(".id = ")
.append(bluepName)
.append(".")
.append(dimension.getForeignKey())
.append(" AND ")
.append(bluepName)
.append(".id = ")
.append(cubeName)
.append(".")
.append(blueprint.getForeignKey());
String filter = getFilterWhereClause(blueprint);
if (filter.length() > 0) {
where.append(" ").append(filter);
}
} else {
from.append(cubeName).append(", ").append(tableName);
where
.append(tableName)
.append(".id = ")
.append(cubeName)
.append(".")
.append(dimension.getForeignKey());
if (blueprint.getSelections() != null && blueprint.getSelections().size() > 0) {
where.append(" AND (");
int size = blueprint.getSelections().size();
for (Filterable filter : blueprint.getSelections()) {
where
.append(cubeName)
.append(".")
.append(blueprint.getForeignKey())
.append(" = ")
.append(filter.getId());
if (size > 1) {
where.append(" OR ");
size--;
}
}
where.append(")");
}
}
} else {
if (!dimension.equals(blueprint)) {
StringTokenizer tokenizer = new StringTokenizer(from.toString(), " ");
boolean containsBluep = false;
boolean containsTable = false;
while (tokenizer.hasMoreTokens()) {
String table = tokenizer.nextToken().replace(",", "").trim();
if (table.equals(bluepName)) {
containsBluep = true;
}
if (table.equals(tableName)) {
containsTable = true;
}
}
if (!containsBluep) {
from.append(", ").append(bluepName);
}
if (!containsTable) {
from.append(", ").append(tableName);
}
where
.append(" AND ")
.append(tableName)
.append(".id = ")
.append(bluepName)
.append(".")
.append(dimension.getForeignKey())
.append(" AND ")
.append(bluepName)
.append(".id = ")
.append(cubeName)
.append(".")
.append(blueprint.getForeignKey());
String filter = getFilterWhereClause(blueprint);
if (filter.length() > 0) {
where.append(" ").append(filter);
}
} else {
if (from.indexOf(tableName) == -1) {
from.append(", ").append(tableName);
}
where
.append(" AND ")
.append(tableName)
.append(".id = ")
.append(cubeName)
.append(".")
.append(dimension.getForeignKey());
if (blueprint.getSelections() != null && blueprint.getSelections().size() > 0) {
where.append(" AND (");
int size = blueprint.getSelections().size();
for (Filterable filter : blueprint.getSelections()) {
where
.append(cubeName)
.append(".")
.append(blueprint.getForeignKey())
.append(" = ")
.append(filter.getId());
if (size > 1) {
where.append(" OR ");
size--;
}
}
where.append(")");
}
}
group.append(", ");
order.append(", ");
}
group.append(tableName).append(".id").append(", ").append(tableName).append(".name");
order.append(tableName).append(".id").append(", ").append(tableName).append(".name");
if (concatenation.length() > 0) {
concatenation.append(" || '_' || ");
}
if (lastTableName != null && !"".equals(lastTableName)) {
concatenation.append(lastTableName).append(".name");
}
lastTableName = tableName;
select.append(", ").append(tableName).append(".name");
sql.append(select);
if (concatenation.length() > 0) {
sql.append(", ").append(concatenation);
}
sql.append(from).append(where).append(group).append(order);
if (LOG.isDebugEnabled()) {
LOG.debug(sql.toString());
}
itemCountSQL = createItemCountSQL(dimension);
return sql.toString();
}