private void appendResourceKeyCondition(StringBuilder sb) {
if (StringUtils.isNotBlank(filter.getResourceKey())) {
sb.append(" AND UPPER(p.kee) LIKE '%");
sb.append(
escapePercentAndUnderscrore(
StringEscapeUtils.escapeSql(StringUtils.upperCase(filter.getResourceKey()))));
sb.append("%'");
appendEscapeForSomeDb(sb);
}
}
private void appendDateConditions(StringBuilder sb) {
Date fromDate = filter.getFromDate();
if (fromDate != null) {
sb.append(" AND s.created_at >= ? ");
dateParameters.add(fromDate.getTime());
}
Date toDate = filter.getToDate();
if (toDate != null) {
sb.append(" AND s.created_at <= ? ");
dateParameters.add(toDate.getTime());
}
}
private void appendResourceNameCondition(StringBuilder sb) {
if (StringUtils.isNotBlank(filter.getResourceName())) {
sb.append(
" AND s.project_id IN (SELECT rindex.resource_id FROM resource_index rindex WHERE rindex.kee LIKE '");
sb.append(
escapePercentAndUnderscrore(
StringEscapeUtils.escapeSql(StringUtils.lowerCase(filter.getResourceName()))));
sb.append("%'");
appendEscapeForSomeDb(sb);
if (!filter.getResourceQualifiers().isEmpty()) {
sb.append(" AND rindex.qualifier IN ");
appendInStatement(filter.getResourceQualifiers(), sb);
}
sb.append(") ");
}
}
private void appendFavouritesCondition(StringBuilder sb) {
if (filter.isOnFavourites()) {
sb.append(
" AND props.prop_key='favourite' AND props.resource_id IS NOT NULL AND props.user_id=");
sb.append(context.getUserId());
sb.append(" ");
}
}
private void appendResourceConditions(StringBuilder sb) {
sb.append(" s.status='P' AND s.islast=").append(database.getDialect().getTrueSqlValue());
if (context.getBaseSnapshot() == null) {
sb.append(" AND p.copy_resource_id IS NULL ");
}
if (!filter.getResourceQualifiers().isEmpty()) {
sb.append(" AND s.qualifier IN ");
appendInStatement(filter.getResourceQualifiers(), sb);
}
if (!filter.getResourceScopes().isEmpty()) {
sb.append(" AND s.scope IN ");
appendInStatement(filter.getResourceScopes(), sb);
}
appendDateConditions(sb);
appendFavouritesCondition(sb);
appendResourceNameCondition(sb);
appendResourceKeyCondition(sb);
appendResourceBaseCondition(sb);
}
List<MeasureFilterRow> process(ResultSet rs) throws SQLException {
List<MeasureFilterRow> rows = Lists.newArrayList();
RowProcessor rowProcessor;
if (filter.sort().isOnNumericMeasure()) {
rowProcessor = new NumericSortRowProcessor();
} else if (filter.sort().isOnDate()) {
rowProcessor = new DateSortRowProcessor();
} else if (filter.sort().isOnTime()) {
rowProcessor = new LongSortRowProcessor();
} else if (filter.sort().isOnAlert()) {
rowProcessor = new AlertSortRowProcessor();
} else {
rowProcessor = new TextSortRowProcessor();
}
while (rs.next()) {
rows.add(rowProcessor.fetch(rs));
}
return rowProcessor.sort(rows, filter.sort().isAsc());
}
private void appendResourceBaseCondition(StringBuilder sb) {
SnapshotDto baseSnapshot = context.getBaseSnapshot();
if (baseSnapshot != null) {
if (filter.isOnBaseResourceChildren()) {
sb.append(" AND s.parent_snapshot_id=").append(baseSnapshot.getId());
} else {
Long rootSnapshotId =
baseSnapshot.getRootId() != null ? baseSnapshot.getRootId() : baseSnapshot.getId();
sb.append(" AND s.root_snapshot_id=").append(rootSnapshotId);
sb.append(" AND s.path LIKE '")
.append(StringUtils.defaultString(baseSnapshot.getPath()))
.append(baseSnapshot.getId())
.append(".%'");
}
}
}
private String generateSql() {
StringBuilder sb = new StringBuilder(1000);
sb.append("SELECT s.id, s.project_id, s.root_project_id, ");
sb.append(filter.sort().column());
sb.append(" FROM snapshots s INNER JOIN projects p ON s.project_id=p.id ");
for (int index = 0; index < filter.getMeasureConditions().size(); index++) {
MeasureFilterCondition condition = filter.getMeasureConditions().get(index);
sb.append(" INNER JOIN project_measures pmcond").append(index);
sb.append(" ON s.id=pmcond").append(index).append(".snapshot_id AND ");
condition.appendSqlCondition(sb, index);
}
if (filter.isOnFavourites()) {
sb.append(" INNER JOIN properties props ON props.resource_id=s.project_id ");
}
if (filter.sort().isOnMeasure()) {
sb.append(
" LEFT OUTER JOIN project_measures pmsort ON s.id=pmsort.snapshot_id AND pmsort.metric_id=");
sb.append(filter.sort().metric().getId());
sb.append(
" AND pmsort.rule_id IS NULL AND pmsort.rule_priority IS NULL AND pmsort.characteristic_id IS NULL AND pmsort.person_id IS NULL ");
}
sb.append(" WHERE ");
appendResourceConditions(sb);
for (int index = 0; index < filter.getMeasureConditions().size(); index++) {
MeasureFilterCondition condition = filter.getMeasureConditions().get(index);
sb.append(" AND ");
condition.appendSqlCondition(sb, index);
}
return sb.toString();
}
