String mapTable;
int tableCounter = 0;
int columnCounter = 0;
for (int idx : queryFields) {
DcField field = module.getField(idx);
if (columnCounter > 0)
sql.append(", ");
if (field.getValueType() == DcRepository.ValueTypes._DCOBJECTCOLLECTION) {
mapping = DcModules.get(DcModules.getMappingModIdx(module.getIndex(), field.getReferenceIdx(), field.getIndex()));
reference = DcModules.get(field.getReferenceIdx());
mapTable = " MAPTABLE" + tableCounter;
joins.append(" LEFT OUTER JOIN ");
joins.append(mapping.getTableName());
joins.append(mapTable);
joins.append(" ON ");
joins.append(mapTable);
joins.append(".");
joins.append(mapping.getField(DcMapping._A_PARENT_ID).getDatabaseFieldName());
joins.append(" = MAINTABLE.ID");
subTable = " SUBTABLE" + tableCounter;
joins.append(" INNER JOIN ");
joins.append(reference.getTableName());
joins.append(subTable);
joins.append(" ON ");
joins.append(subTable);
joins.append(".ID");
joins.append(" = ");
joins.append(mapTable);
joins.append(".");
joins.append(mapping.getField(DcMapping._B_REFERENCED_ID).getDatabaseFieldName());
sql.append(subTable);
sql.append(".");
sql.append(reference.getField(reference.getDisplayFieldIdx()).getDatabaseFieldName());
tableCounter++;
} else if (field.getValueType() == DcRepository.ValueTypes._DCOBJECTREFERENCE) {
reference = DcModules.get(field.getReferenceIdx());
subTable = " SUBTABLE" + tableCounter;
joins.append(" LEFT OUTER JOIN ");
joins.append(reference.getTableName());
joins.append(subTable);
joins.append(" ON ");
joins.append(subTable);
joins.append(".ID = MAINTABLE.");
joins.append(field.getDatabaseFieldName());
sql.append(subTable);
sql.append(".");
sql.append(reference.getField(reference.getDisplayFieldIdx()).getDatabaseFieldName());
tableCounter++;
} else if (field.getValueType() == DcRepository.ValueTypes._PICTURE) {
reference = DcModules.get(DcModules._PICTURE);
subTable = " SUBTABLE" + tableCounter;
sql.append("(case when ");
sql.append(subTable);
sql.append(".OBJECTID IS NULL then '' else ");
sql.append("'/mediaimages/'+MAINTABLE.ID+'_");
sql.append(field.getDatabaseFieldName());
sql.append("_small.jpg' ");
sql.append("END) AS ");
sql.append(field.getDatabaseFieldName());
joins.append(" LEFT OUTER JOIN ");
joins.append(reference.getTableName());
joins.append(subTable);
joins.append(" ON ");
joins.append(subTable);
joins.append(".OBJECTID = MAINTABLE.ID");
joins.append(" AND ");
joins.append(subTable);
joins.append(".");
joins.append(reference.getField(Picture._B_FIELD));
joins.append("='");
joins.append(field.getDatabaseFieldName());
joins.append("'");
} else if (field.getIndex() == DcObject._SYS_AVAILABLE ||
field.getIndex() == DcObject._SYS_LOANDURATION ||
field.getIndex() == DcObject._SYS_LOANDUEDATE ||
field.getIndex() == DcObject._SYS_LOANDAYSTILLOVERDUE) {
// TODO: implement for web site
// reference = DcModules.get(DcModules._LOAN);
// subTable = " SUBTABLE" + tableCounter;
// joins.append(" LEFT OUTER JOIN ");
} else if (!field.isUiOnly()) {
sql.append("MAINTABLE.");
sql.append(field.getDatabaseFieldName());
} else {
sql.append("'N/A' AS ");
sql.append("NA");
sql.append(columnCounter);
}