public String buildGetObjectValuePairsByJoinStatement(
List<Criteria> alCriterias,
int nAxisId,
JoinStatement joinStatementAllPositions,
String todayFormatted,
boolean visibilitySensitive) {
StringBuilder sSQLStatement = new StringBuilder(1000);
sSQLStatement
.append("SELECT CMC.internalContentId")
.append(", CEC.")
.append(AXIS_COLUMN)
.append(nAxisId)
.append(", CMI.componentId");
sSQLStatement
.append(" FROM ")
.append(CLASSIFICATION_TABLE)
.append(" CEC, ")
.append(joinStatementAllPositions.getTable(0))
.append(" CML, SB_ContentManager_Content CMC, SB_ContentManager_Instance CMI ");
sSQLStatement
.append(" WHERE CEC.")
.append(POSITION_ID_COLUMN)
.append(" = CML.")
.append(joinStatementAllPositions.getJoinKey(0));
sSQLStatement
.append(" AND CEC.")
.append(SILVEROBJECT_ID_COLUMN)
.append(" = CMC.silverContentId ");
sSQLStatement.append(" AND CMC.contentInstanceId").append(" = CMI.instanceId ");
String whereClause = joinStatementAllPositions.getWhere();
if (isDefined(whereClause)) {
sSQLStatement.append(" AND ").append(whereClause);
}
for (Criteria criteria : alCriterias) {
if (criteria.getValue() != null) {
sSQLStatement
.append(" AND (CEC.")
.append(AXIS_COLUMN)
.append(criteria.getAxisId())
.append(" LIKE '")
.append(criteria.getValue())
.append("%')");
}
}
// Set the pertinent axiom
sSQLStatement.append(" AND (CEC.").append(AXIS_COLUMN).append(nAxisId).append(" IS NOT NULL)");
// Set the visibility constraints --> en faire une fonction
sSQLStatement
.append(" AND ('")
.append(todayFormatted)
.append("' between CMC.beginDate AND CMC.endDate)");
if (visibilitySensitive) {
sSQLStatement.append(" AND (CMC.isVisible = 1 )");
}
return sSQLStatement.toString();
}
public String buildGetPertinentAxisStatement(
List<Criteria> alCriterias, int nAxisId, String todayFormatted, boolean visibilitySensitive) {
StringBuilder sSQLStatement = new StringBuilder(1000);
sSQLStatement
.append("SELECT COUNT(*) FROM ")
.append(CLASSIFICATION_TABLE)
.append(" CEC, SB_ContentManager_Content CMC WHERE CEC.")
.append(SILVEROBJECT_ID_COLUMN)
.append(" = CMC.silverContentId AND (CEC.")
.append(POSITION_ID_COLUMN)
.append(" <> -1) ");
if (alCriterias != null) {
for (Criteria criteria : alCriterias) {
if (criteria.getValue() != null) {
sSQLStatement
.append(" AND (CEC.")
.append(AXIS_COLUMN)
.append(criteria.getAxisId())
.append(" LIKE '")
.append(criteria.getValue())
.append("%')");
}
}
}
// Set the visibility constraints --> en faire une fonction
sSQLStatement
.append(" AND ('")
.append(todayFormatted)
.append("' between CMC.beginDate AND CMC.endDate)");
if (visibilitySensitive) {
sSQLStatement.append(" AND (CMC.isVisible = 1 )");
}
// Set the pertinent axiom
sSQLStatement.append(" AND (CEC.").append(AXIS_COLUMN).append(nAxisId).append(" IS NOT NULL)");
return sSQLStatement.toString();
}
public String buildGetPertinentAxisStatementByJoin(
List<? extends Criteria> alCriterias,
int nAxisId,
String sRootValue,
JoinStatement joinStatementAllPositions,
String todayFormatted,
boolean visibilitySensitive) {
StringBuilder sSQLStatement = new StringBuilder(1000);
String containerMgrLinks = joinStatementAllPositions.getTable(0);
String containerMgrLinksKey = joinStatementAllPositions.getJoinKey(0);
sSQLStatement.append("SELECT COUNT(DISTINCT CEC.").append(SILVEROBJECT_ID_COLUMN).append(")");
sSQLStatement
.append(" FROM ")
.append(CLASSIFICATION_TABLE)
.append(" CEC, ")
.append(containerMgrLinks)
.append(" CML, SB_ContentManager_Content CMC ");
sSQLStatement
.append(" WHERE CEC.")
.append(POSITION_ID_COLUMN)
.append(" = CML.")
.append(containerMgrLinksKey);
sSQLStatement
.append(" AND CEC.")
.append(SILVEROBJECT_ID_COLUMN)
.append(" = CMC.silverContentId ");
String whereClause = joinStatementAllPositions.getWhere();
if (isDefined(whereClause)) {
sSQLStatement.append(" AND ").append(whereClause);
}
if (alCriterias != null) {
for (Criteria criteria : alCriterias) {
if (criteria.getValue() != null) {
sSQLStatement
.append(" AND (CEC.")
.append(AXIS_COLUMN)
.append(criteria.getAxisId())
.append(" LIKE '")
.append(criteria.getValue())
.append("%')");
}
}
}
// Set the visibility constraints --> en faire une fonction
sSQLStatement
.append(" AND ('")
.append(todayFormatted)
.append("' between CMC.beginDate AND CMC.endDate)");
if (visibilitySensitive) {
sSQLStatement.append(" AND (CMC.isVisible = 1 )");
}
// Set the pertinent axiom
if (sRootValue.length() == 0) {
sSQLStatement
.append(" AND (CEC.")
.append(AXIS_COLUMN)
.append(nAxisId)
.append(" IS NOT NULL)");
} else {
sSQLStatement
.append(" AND (CEC.")
.append(AXIS_COLUMN)
.append(nAxisId)
.append(" LIKE '")
.append(sRootValue)
.append("%')");
}
return sSQLStatement.toString();
}
public String buildFindByCriteriasStatementByJoin(
List<Criteria> alCriterias,
JoinStatement joinStatementContainer,
JoinStatement joinStatementContent,
String todayFormatted,
boolean recursiveSearch,
boolean visibilitySensitive) {
StringBuilder sSQLStatement = new StringBuilder(1000);
String containerMgrLinks = joinStatementContainer.getTable(0);
String containerMgrLinksKey = joinStatementContainer.getJoinKey(0);
String contentMgr = joinStatementContent.getTable(0);
String contentMgrKey = joinStatementContent.getJoinKey(0);
String whereClause = "";
sSQLStatement
.append(" SELECT CEC.")
.append(SILVEROBJECT_ID_COLUMN)
.append(" FROM ")
.append(CLASSIFICATION_TABLE)
.append(" CEC,")
.append(containerMgrLinks)
.append(" CML,")
.append(contentMgr)
.append(" CMC");
sSQLStatement.append(" WHERE ");
sSQLStatement
.append(" CEC.")
.append(POSITION_ID_COLUMN)
.append(" = CML.")
.append(containerMgrLinksKey);
sSQLStatement
.append(" AND CEC.")
.append(SILVEROBJECT_ID_COLUMN)
.append(" = CMC.")
.append(contentMgrKey);
// works on the container statement
whereClause = joinStatementContainer.getWhere();
if (!whereClause.equals("")) {
sSQLStatement.append(" AND ").append(whereClause);
}
// works on the content statement
whereClause = joinStatementContent.getWhere();
if (!whereClause.equals("")) {
sSQLStatement.append(" AND ").append(whereClause);
}
// criteres
for (Criteria alCriteria : alCriterias) {
if (alCriteria.getValue() != null) {
sSQLStatement.append(" AND (").append(AXIS_COLUMN).append(alCriteria.getAxisId());
if (recursiveSearch) {
sSQLStatement.append(" LIKE '");
} else {
sSQLStatement.append(" = '");
}
sSQLStatement.append(alCriteria.getValue());
if (recursiveSearch) {
sSQLStatement.append("%')");
} else {
sSQLStatement.append("')");
}
}
}
// Set the visibility constraints --> en faire une fonction
sSQLStatement
.append(" AND ('")
.append(todayFormatted)
.append("' between CMC.beginDate AND CMC.endDate)");
if (visibilitySensitive) {
sSQLStatement.append(" AND (CMC.isVisible = 1 )");
}
sSQLStatement.append(" GROUP BY CEC.").append(SILVEROBJECT_ID_COLUMN);
whereClause = null;
return sSQLStatement.toString();
}