Examples with Filter org.olat.repository.model.SearchMyRepositoryEntryViewParams.Filter used on opensource projects

Example 6 with Filter

use of org.olat.repository.model.SearchMyRepositoryEntryViewParams.Filter in project openolat by klemens.

the class RepositoryEntryMyCourseQueries method creatMyViewQuery.

protected <T> TypedQuery<T> creatMyViewQuery(SearchMyRepositoryEntryViewParams params, Class<T> type) {
    Roles roles = params.getRoles();
    Identity identity = params.getIdentity();
    List<String> resourceTypes = params.getResourceTypes();
    boolean needIdentityKey = false;
    boolean count = Number.class.equals(type);
    boolean oracle = "oracle".equals(dbInstance.getDbVendor());
    StringBuilder sb = new StringBuilder();
    if (count) {
        sb.append("select count(v.key) ").append(" from repositoryentry as v").append(" inner join v.olatResource as res").append(" left join v.lifecycle as lifecycle ");
    } else {
        sb.append("select v, ");
        if (params.getMarked() != null && params.getMarked().booleanValue()) {
            sb.append(" 1 as marks,");
        } else {
            needIdentityKey = true;
            sb.append(" (select count(mark.key) from ").append(MarkImpl.class.getName()).append(" as mark ").append("   where mark.creator.key=:identityKey and mark.resId=v.key and mark.resName='RepositoryEntry'").append(" ) as marks,");
        }
        sb.append(" (select count(offer.key) from acoffer as offer ").append("   where offer.resource=res and offer.valid=true").append(" ) as offers, ");
        if (repositoryModule.isRatingEnabled()) {
            needIdentityKey = true;
            sb.append(" (select rating.rating from userrating as rating").append("   where rating.resId=v.key and rating.creator.key=:identityKey and rating.resName='RepositoryEntry'").append(" ) as myrating");
        } else {
            sb.append(" 0 as myrating");
        }
        needIdentityKey = true;
        sb.append(" ,(select eff.key from ").append(UserEfficiencyStatementImpl.class.getName()).append(" as eff").append("    where eff.resource=res and eff.identity.key=:identityKey").append(" ) as effKey");
        needIdentityKey |= appendOrderByInSelect(params, sb);
        sb.append(" from repositoryentry as v").append(" inner join ").append(oracle ? "" : "fetch").append(" v.olatResource as res");
        if (repositoryModule.isRatingEnabled() || repositoryModule.isCommentEnabled()) {
            sb.append(" inner join fetch v.statistics as stats");
        }
        sb.append(" left join fetch v.lifecycle as lifecycle ");
    }
    // join seems to be quicker
    if (params.getMarked() != null && params.getMarked().booleanValue()) {
        sb.append(" inner join ").append(MarkImpl.class.getName()).append(" as mark2 on (mark2.creator.key=:identityKey and mark2.resId=v.key and mark2.resName='RepositoryEntry')");
    }
    sb.append(" where ");
    needIdentityKey |= appendMyViewAccessSubSelect(sb, roles, params.getFilters(), params.isMembershipMandatory());
    if (params.getRepoEntryKeys() != null && params.getRepoEntryKeys().size() > 0) {
        sb.append(" and v.key in (:repoEntryKeys) ");
    }
    if (params.getClosed() != null) {
        if (params.getClosed().booleanValue()) {
            sb.append(" and v.statusCode>0");
        } else {
            sb.append(" and v.statusCode=0");
        }
    }
    if (params.getFilters() != null) {
        for (Filter filter : params.getFilters()) {
            needIdentityKey |= appendFiltersInWhereClause(filter, sb);
        }
    }
    if (params.getParentEntry() != null) {
        sb.append(" and exists (select cei.parent.key from ").append(CatalogEntryImpl.class.getName()).append(" as cei ").append("   where cei.parent.key=:parentCeiKey and cei.repositoryEntry.key=v.key").append(" )");
    }
    if (params.isResourceTypesDefined()) {
        sb.append(" and res.resName in (:resourcetypes)");
    }
    String author = params.getAuthor();
    if (StringHelper.containsNonWhitespace(author)) {
        // fuzzy author search
        author = PersistenceHelper.makeFuzzyQueryString(author);
        sb.append(" and v.key in (select rel.entry.key from repoentrytogroup as rel, bgroupmember as membership, ").append(IdentityImpl.class.getName()).append(" as identity, ").append(UserImpl.class.getName()).append(" as user").append("    where rel.group.key=membership.group.key and membership.identity.key=identity.key and user.identity.key=identity.key").append("      and membership.role='").append(GroupRoles.owner.name()).append("'").append("      and (");
        PersistenceHelper.appendFuzzyLike(sb, "user.firstName", "author", dbInstance.getDbVendor());
        sb.append(" or ");
        PersistenceHelper.appendFuzzyLike(sb, "user.lastName", "author", dbInstance.getDbVendor());
        sb.append(" or ");
        PersistenceHelper.appendFuzzyLike(sb, "identity.name", "author", dbInstance.getDbVendor());
        sb.append(" ))");
    }
    String text = params.getText();
    if (StringHelper.containsNonWhitespace(text)) {
        // displayName = '%' + displayName.replace('*', '%') + '%';
        // query.append(" and v.displayname like :displayname");
        text = PersistenceHelper.makeFuzzyQueryString(text);
        sb.append(" and (");
        PersistenceHelper.appendFuzzyLike(sb, "v.displayname", "displaytext", dbInstance.getDbVendor());
        sb.append(" or ");
        PersistenceHelper.appendFuzzyLike(sb, "v.description", "displaytext", dbInstance.getDbVendor());
        sb.append(" or ");
        PersistenceHelper.appendFuzzyLike(sb, "v.objectives", "displaytext", dbInstance.getDbVendor());
        sb.append(" or ");
        PersistenceHelper.appendFuzzyLike(sb, "v.authors", "displaytext", dbInstance.getDbVendor());
        sb.append(")");
    }
    Long id = null;
    String refs = null;
    String fuzzyRefs = null;
    if (StringHelper.containsNonWhitespace(params.getIdAndRefs())) {
        refs = params.getIdAndRefs();
        fuzzyRefs = PersistenceHelper.makeFuzzyQueryString(refs);
        sb.append(" and (v.externalId=:ref or ");
        PersistenceHelper.appendFuzzyLike(sb, "v.externalRef", "fuzzyRefs", dbInstance.getDbVendor());
        sb.append(" or v.softkey=:ref");
        if (StringHelper.isLong(refs)) {
            try {
                id = Long.parseLong(refs);
                sb.append(" or v.key=:vKey or res.resId=:vKey");
            } catch (NumberFormatException e) {
            // 
            }
        }
        sb.append(")");
    }
    // alt id, refs and title
    Long quickId = null;
    String quickRefs = null;
    String quickText = null;
    if (StringHelper.containsNonWhitespace(params.getIdRefsAndTitle())) {
        quickRefs = params.getIdRefsAndTitle();
        quickText = PersistenceHelper.makeFuzzyQueryString(quickRefs);
        sb.append(" and (v.externalId=:quickRef or ");
        PersistenceHelper.appendFuzzyLike(sb, "v.externalRef", "quickText", dbInstance.getDbVendor());
        sb.append(" or v.softkey=:quickRef or ");
        PersistenceHelper.appendFuzzyLike(sb, "v.displayname", "quickText", dbInstance.getDbVendor());
        if (StringHelper.isLong(quickRefs)) {
            try {
                quickId = Long.parseLong(quickRefs);
                sb.append(" or v.key=:quickVKey or res.resId=:quickVKey");
            } catch (NumberFormatException e) {
            // 
            }
        }
        sb.append(")");
    }
    if (!count) {
        appendOrderBy(params.getOrderBy(), params.isOrderByAsc(), sb);
    }
    TypedQuery<T> dbQuery = dbInstance.getCurrentEntityManager().createQuery(sb.toString(), type);
    if (params.getParentEntry() != null) {
        dbQuery.setParameter("parentCeiKey", params.getParentEntry().getKey());
    }
    if (params.getRepoEntryKeys() != null && params.getRepoEntryKeys().size() > 0) {
        dbQuery.setParameter("repoEntryKeys", params.getRepoEntryKeys());
    }
    if (params.isResourceTypesDefined()) {
        dbQuery.setParameter("resourcetypes", resourceTypes);
    }
    if (params.isLifecycleFilterDefined()) {
        dbQuery.setParameter("now", new Date());
    }
    if (id != null) {
        dbQuery.setParameter("vKey", id);
    }
    if (refs != null) {
        dbQuery.setParameter("ref", refs);
    }
    if (fuzzyRefs != null) {
        dbQuery.setParameter("fuzzyRefs", fuzzyRefs);
    }
    if (quickId != null) {
        dbQuery.setParameter("quickVKey", quickId);
    }
    if (quickRefs != null) {
        dbQuery.setParameter("quickRef", quickRefs);
    }
    if (quickText != null) {
        dbQuery.setParameter("quickText", quickText);
    }
    if (StringHelper.containsNonWhitespace(text)) {
        dbQuery.setParameter("displaytext", text);
    }
    if (StringHelper.containsNonWhitespace(author)) {
        // fuzzy author search
        dbQuery.setParameter("author", author);
    }
    if (needIdentityKey) {
        dbQuery.setParameter("identityKey", identity.getKey());
    }
    return dbQuery;
}