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(); }