StringBuffer sql = new StringBuffer("");
List<String> joinOn = new ArrayList<String>();
DcField field;
DcModule reference;
DcModule main;
Collection<DcModule> modules = new ArrayList<DcModule>();
if (m.isAbstract()) {
modules = DcModules.getPersistentModules(m);
} else {
modules.add(m);
}
int moduleCounter = 0;
int fieldCounter = 0;
StringBuffer columns;
StringBuffer joins;
for (DcModule module : modules) {
fieldCounter = 0;
columns = new StringBuffer("select ");
joins = new StringBuffer("from ");
joins.append(module.getTableName());
if (moduleCounter > 0)
sql.append(" UNION ");
columns.append(module.getTableName());
columns.append(".ID, ");
columns.append(module.getIndex());
columns.append(" AS MODULEIDX");
for (int idx : fields) {
field = module.getField(idx);
if (field.isUiOnly() &&
field.getValueType() != DcRepository.ValueTypes._DCOBJECTCOLLECTION &&
field.getValueType() != DcRepository.ValueTypes._DCOBJECTREFERENCE) continue;
columns.append(",");
joinOn.add(module.getTableName() + fieldCounter + ".ID");
if (field.getValueType() == DcRepository.ValueTypes._DCOBJECTCOLLECTION ||
field.getValueType() == DcRepository.ValueTypes._DCOBJECTREFERENCE) {
reference = DcModules.get(field.getReferenceIdx());
main = field.getValueType() == DcRepository.ValueTypes._DCOBJECTREFERENCE ? module :
DcModules.get(DcModules.getMappingModIdx(module.getIndex(), reference.getIndex(), field.getIndex()));
if ( field.getValueType() == DcRepository.ValueTypes._DCOBJECTCOLLECTION ||
field.getFieldType() == ComponentFactory._REFERENCEFIELD) {
columns.append("subselect");
columns.append(fieldCounter);
columns.append(".ID AS ");
columns.append("ID");
columns.append(fieldCounter);
columns.append(", ");
columns.append("subselect");
columns.append(fieldCounter);
columns.append(".name AS ");
columns.append("NAME");
columns.append(fieldCounter);
columns.append(", ");
columns.append("subselect");
columns.append(fieldCounter);
columns.append(".icon AS ");
columns.append("ICON");
columns.append(fieldCounter);
if (field.getValueType() == DcRepository.ValueTypes._DCOBJECTCOLLECTION) {
joins.append(" left outer join ");
joins.append("(select ");
joins.append(reference.getTableName());
joins.append(".ID as ID,");
joins.append(main.getTableName());
joins.append(".");
joins.append(main.getField(DcMapping._A_PARENT_ID).getDatabaseFieldName());
joins.append(" as parentID, ");
joins.append(reference.getTableName());
joins.append(".");
joins.append(reference.getField(reference.getSystemDisplayFieldIdx()).getDatabaseFieldName());
joins.append(" as name,");
if (reference.getType() == DcModule._TYPE_PROPERTY_MODULE) {
joins.append(reference.getTableName());
joins.append(".");
joins.append(reference.getField(DcProperty._B_ICON).getDatabaseFieldName());
} else {
joins.append("NULL");
}
joins.append(" as icon");
joins.append(" from ");
joins.append(reference.getTableName());
joins.append(" inner join ");
joins.append(main.getTableName());
joins.append(" on ");
joins.append(reference.getTableName());
joins.append(".ID = ");
joins.append(main.getTableName());
joins.append(".");
joins.append(main.getField(DcMapping._B_REFERENCED_ID).getDatabaseFieldName());
joins.append(") subselect");
joins.append(fieldCounter);
joins.append(" on ");
joins.append(" subselect");
joins.append(fieldCounter);
joins.append(".parentID = ");
joins.append(module.getTableName());
joins.append(".ID ");
} else {
joins.append(" left outer join ");
joins.append(reference.getTableName());
joins.append(" ");
joins.append(" subselect");
joins.append(fieldCounter);
joins.append(" on ");
joins.append(module.getTableName());
joins.append(".");
joins.append(field.getDatabaseFieldName());
joins.append("=");
joins.append(" subselect");
joins.append(fieldCounter);
joins.append(".ID");
}
}
} else {
columns.append(module.getTableName());
columns.append(".");
columns.append(field.getDatabaseFieldName());
columns.append(",");
columns.append(module.getTableName());
columns.append(".");
columns.append(field.getDatabaseFieldName() + " as df");
columns.append(fieldCounter);
columns.append(",NULL");
joinOn.add(module.getTableName() + ".ID");
}
fieldCounter++;
}
sql.append(columns.toString() + " " + joins.toString());
sql.append(" ");
moduleCounter++;
}
if (m.isAbstract()) {
sql.insert(0, "select * from (");
sql.append(") ");
}
// index based order by
int level = 0;
boolean ordered = false;
for (int idx : fields) {
field = DcModules.get(getModule()).getField(idx);
if (field.isUiOnly() &&
field.getIndex() != DcObject._SYS_MODULE &&
field.getValueType() != DcRepository.ValueTypes._DCOBJECTCOLLECTION &&
field.getValueType() != DcRepository.ValueTypes._DCOBJECTREFERENCE) continue;
if (!ordered) {
sql.append(" order by ");
ordered = true;
} else {
sql.append(",");
}
if (field.getIndex() == DcObject._SYS_MODULE) {
sql.append("2");
} else {
sql.append(String.valueOf((level * 3) + 4));
level++;
}