Examples with NativeQueryBuilder org.olat.core.commons.persistence.NativeQueryBuilder used on opensource projects

Example 11 with NativeQueryBuilder

use of org.olat.core.commons.persistence.NativeQueryBuilder in project openolat by klemens.

the class CoachingDAO method getUsersStatisticsStatements.

private boolean getUsersStatisticsStatements(SearchCoachedIdentityParams params, Map<Long, StudentStatEntry> map) {
    NativeQueryBuilder sb = new NativeQueryBuilder(1024, dbInstance);
    Map<String, Object> queryParams = new HashMap<>();
    sb.append("select ").append(" fin_statement.fk_identity, ").append(" sum(case when fin_statement.passed=").appendTrue().append(" then 1 else 0 end) as num_of_passed, ").append(" sum(case when fin_statement.passed=").appendFalse().append(" then 1 else 0 end) as num_of_failed ").append("from o_as_eff_statement fin_statement ").append("where fin_statement.id in ( select ").append("  distinct sg_statement.id as st_id ").append(" from o_repositoryentry sg_re ").append(" inner join o_re_to_group togroup on (togroup.fk_entry_id = sg_re.repositoryentry_id) ").append(" inner join o_bs_group_member sg_participant on (sg_participant.fk_group_id=togroup.fk_group_id and sg_participant.g_role='participant') ").append(" inner join o_as_eff_statement sg_statement on (sg_statement.fk_identity = sg_participant.fk_identity_id and sg_statement.fk_resource_id = sg_re.fk_olatresource) ").append(" inner join o_bs_identity id_participant on (sg_participant.fk_identity_id = id_participant.id) ");
    appendUsersStatisticsJoins(params, sb).append(" where  sg_re.accesscode>0 ");
    appendUsersStatisticsSearchParams(params, queryParams, sb).append(") ").append("group by fin_statement.fk_identity ");
    Query query = dbInstance.getCurrentEntityManager().createNativeQuery(sb.toString());
    for (Map.Entry<String, Object> entry : queryParams.entrySet()) {
        query.setParameter(entry.getKey(), entry.getValue());
    }
    List<?> rawList = query.getResultList();
    for (Object rawObject : rawList) {
        Object[] rawStat = (Object[]) rawObject;
        Long userKey = ((Number) rawStat[0]).longValue();
        StudentStatEntry entry = map.get(userKey);
        if (entry != null) {
            int passed = ((Number) rawStat[1]).intValue();
            int failed = ((Number) rawStat[2]).intValue();
            entry.setCountPassed(passed);
            entry.setCountFailed(failed);
            int notAttempted = entry.getCountRepo() - passed - failed;
            entry.setCountNotAttempted(notAttempted);
        }
    }
    return rawList.size() > 0;
}

Example 12 with NativeQueryBuilder

use of org.olat.core.commons.persistence.NativeQueryBuilder in project openolat by klemens.

the class CoachingDAO method getGroupsStatisticsStatementForOwner.

private boolean getGroupsStatisticsStatementForOwner(Identity coach, Map<Long, GroupStatEntry> map) {
    NativeQueryBuilder sb = new NativeQueryBuilder(1024, dbInstance);
    sb.append("select").append(" fin_statement.bgp_id,").append(" fin_statement.re_id,").append(" sum(case when fin_statement.passed=").appendTrue().append(" then 1 else 0 end) as num_of_passed,").append(" sum(case when fin_statement.passed=").appendFalse().append(" then 1 else 0 end) as num_of_failed,").append(" sum(fin_statement.score) as avg_score ").append("from ( select ").append("  distinct sg_statement.id as id,").append("  togroup.fk_group_id as bgp_id,").append("  togroup.fk_entry_id as re_id,").append("  sg_statement.passed as passed,").append("  sg_statement.score as score ").append(" from o_repositoryentry sg_re ").append(" inner join o_re_to_group owngroup on (owngroup.r_defgroup=").appendTrue().append(" and owngroup.fk_entry_id = sg_re.repositoryentry_id) ").append(" inner join o_bs_group_member sg_owner on (sg_owner.fk_group_id=owngroup.fk_group_id and sg_owner.g_role = 'owner') ").append(" inner join o_re_to_group togroup on (togroup.r_defgroup=").appendFalse().append(" and togroup.fk_entry_id = sg_re.repositoryentry_id) ").append(" inner join o_bs_group_member sg_participant on (sg_participant.fk_group_id=togroup.fk_group_id and sg_participant.g_role='participant') ").append(" inner join o_as_eff_statement sg_statement on (sg_statement.fk_identity = sg_participant.fk_identity_id and sg_statement.fk_resource_id = sg_re.fk_olatresource) ").append(" where sg_owner.fk_identity_id=:coachKey and sg_re.accesscode >= ").append(RepositoryEntry.ACC_OWNERS).append(") ").appendAs().append(" fin_statement ").append("group by fin_statement.bgp_id, fin_statement.re_id ");
    List<?> rawList = dbInstance.getCurrentEntityManager().createNativeQuery(sb.toString()).setParameter("coachKey", coach.getKey()).getResultList();
    for (Object rawObject : rawList) {
        Object[] rawStats = (Object[]) rawObject;
        Long baseGroupKey = ((Number) rawStats[0]).longValue();
        Long repoKey = ((Number) rawStats[1]).longValue();
        GroupStatEntry entry = map.get(baseGroupKey);
        if (entry != null && !entry.getRepoIds().contains(repoKey)) {
            int passed = ((Number) rawStats[2]).intValue();
            int failed = ((Number) rawStats[3]).intValue();
            entry.setCountFailed(failed + entry.getCountFailed());
            entry.setCountPassed(passed + entry.getCountPassed());
            if (rawStats[4] != null) {
                entry.setSumScore(entry.getSumScore() + ((Number) rawStats[4]).floatValue());
            }
            entry.getRepoIds().add(repoKey);
        }
    }
    return rawList.size() > 0;
}

Example 13 with NativeQueryBuilder

use of org.olat.core.commons.persistence.NativeQueryBuilder in project openolat by klemens.

the class CoachingDAO method getCourses.

private boolean getCourses(IdentityRef coach, Map<Long, CourseStatEntry> map) {
    NativeQueryBuilder sb = new NativeQueryBuilder(1024, dbInstance);
    sb.append("select v.key, v.displayname").append(" from repositoryentry v").append(" inner join v.olatResource as res").append(" inner join v.groups as relGroup").append(" inner join relGroup.group as baseGroup").append(" inner join baseGroup.members as coach on coach.role in ('").append(GroupRoles.coach.name()).append("','").append(GroupRoles.owner.name()).append("')").append(" where coach.identity.key=:coachKey and res.resName='CourseModule'").append(" and ((v.access=1 and v.membersOnly=true) ").append(" or (v.access >= ").append(RepositoryEntry.ACC_USERS).append(" and coach.role='").append(GroupRoles.coach.name()).append("')").append(" or (v.access >= ").append(RepositoryEntry.ACC_OWNERS).append(" and coach.role='").append(GroupRoles.owner.name()).append("'))");
    List<Object[]> rawList = dbInstance.getCurrentEntityManager().createQuery(sb.toString(), Object[].class).setParameter("coachKey", coach.getKey()).getResultList();
    for (Object[] rawStat : rawList) {
        CourseStatEntry entry = new CourseStatEntry();
        entry.setRepoKey(((Number) rawStat[0]).longValue());
        entry.setRepoDisplayName((String) rawStat[1]);
        map.put(entry.getRepoKey(), entry);
    }
    return rawList.size() > 0;
}

Example 14 with NativeQueryBuilder

use of org.olat.core.commons.persistence.NativeQueryBuilder in project openolat by klemens.

the class CoachingDAO method getStudentsStastisticInfosForOwner.

private boolean getStudentsStastisticInfosForOwner(IdentityRef coach, Map<Long, StudentStatEntry> map, List<UserPropertyHandler> userPropertyHandlers) {
    NativeQueryBuilder sb = new NativeQueryBuilder(1024, dbInstance);
    sb.append("select").append("  sg_participant_id.id as part_id,").append("  sg_participant_id.name as part_name,").append("  sg_participant_user.user_id as part_user_id,");
    writeUserProperties("sg_participant_user", sb, userPropertyHandlers);
    sb.append("  ").appendToArray("sg_re.repositoryentry_id").append(" as re_ids,").append("  ").appendToArray("pg_initial_launch.id").append(" as pg_ids").append(" from o_repositoryentry sg_re").append(" inner join o_re_to_group owngroup on (owngroup.fk_entry_id = sg_re.repositoryentry_id)").append(" inner join o_bs_group_member sg_owner on (sg_owner.fk_group_id=owngroup.fk_group_id ").append("  and owngroup.r_defgroup=").appendTrue().append(" and sg_owner.g_role='owner' and sg_owner.fk_identity_id=:coachKey)").append(" inner join o_re_to_group togroup on (togroup.fk_entry_id = sg_re.repositoryentry_id)").append(" inner join o_bs_group_member sg_participant on (sg_participant.fk_group_id=togroup.fk_group_id and sg_participant.g_role='participant')").append(" inner join o_bs_identity sg_participant_id on (sg_participant_id.id=sg_participant.fk_identity_id)").append(" inner join o_user sg_participant_user on (sg_participant_user.fk_identity=sg_participant_id.id)").append(" left join o_as_user_course_infos pg_initial_launch").append("   on (pg_initial_launch.fk_resource_id = sg_re.fk_olatresource and pg_initial_launch.fk_identity = sg_participant_id.id)").append(" where sg_re.accesscode >= ").append(RepositoryEntry.ACC_OWNERS).append(" and sg_re.fk_olatresource in (").append("  select sg_res.resource_id from o_olatresource sg_res where sg_res.resname = 'CourseModule'").append(" )").append(" group by sg_participant_id.id, sg_participant_user.user_id");
    if (dbInstance.isOracle()) {
        sb.append(", sg_participant_id.name");
        writeUserPropertiesGroupBy("sg_participant_user", sb, userPropertyHandlers);
    }
    List<?> rawList = dbInstance.getCurrentEntityManager().createNativeQuery(sb.toString()).setParameter("coachKey", coach.getKey()).getResultList();
    int numOfProperties = userPropertyHandlers.size();
    Map<Long, StudentStatEntry> stats = new HashMap<>();
    for (Object rawObject : rawList) {
        Object[] rawStat = (Object[]) rawObject;
        int pos = 0;
        Long identityKey = ((Number) rawStat[pos++]).longValue();
        String identityName = (String) rawStat[pos++];
        // user key
        ((Number) rawStat[pos++]).longValue();
        StudentStatEntry entry;
        if (map.containsKey(identityKey)) {
            entry = map.get(identityKey);
            pos += numOfProperties;
        } else {
            String[] userProperties = new String[numOfProperties];
            for (int i = 0; i < numOfProperties; i++) {
                userProperties[i] = (String) rawStat[pos++];
            }
            entry = new StudentStatEntry(identityKey, identityName, userProperties);
            map.put(identityKey, entry);
        }
        appendArrayToSet(rawStat[pos++], entry.getRepoIds());
        appendArrayToSet(rawStat[pos++], entry.getLaunchIds());
        stats.put(entry.getIdentityKey(), entry);
    }
    return rawList.size() > 0;
}

Example 15 with NativeQueryBuilder

use of org.olat.core.commons.persistence.NativeQueryBuilder in project openolat by klemens.

the class CoachingDAO method getGroupsStatisticsInfosForCoach.

private boolean getGroupsStatisticsInfosForCoach(Identity coach, Map<Long, GroupStatEntry> map) {
    NativeQueryBuilder sb = new NativeQueryBuilder(1024, dbInstance);
    sb.append("select ").append("  togroup.fk_group_id as basegr_id, ").append("  togroup.fk_entry_id as re_id, ").append("  count(distinct pg_initial_launch.id) as pg_id ").append(" from o_repositoryentry sg_re  ").append(" inner join o_re_to_group togroup on (togroup.r_defgroup=").appendFalse().append(" and togroup.fk_entry_id = sg_re.repositoryentry_id) ").append(" inner join o_bs_group_member sg_coach on (sg_coach.fk_group_id=togroup.fk_group_id and sg_coach.g_role = 'coach') ").append(" inner join o_bs_group_member sg_participant on (sg_participant.fk_group_id=sg_coach.fk_group_id and sg_participant.g_role='participant') ").append(" left join o_as_user_course_infos pg_initial_launch ").append("   on (pg_initial_launch.fk_resource_id = sg_re.fk_olatresource and pg_initial_launch.fk_identity = sg_participant.fk_identity_id) ").append(" where sg_coach.fk_identity_id=:coachKey and ( ").append("   (sg_re.accesscode >= ").append(RepositoryEntry.ACC_USERS).append(// BAR
    " and sg_coach.g_role = 'coach') ").append("   or ").append("   (sg_re.accesscode = ").append(RepositoryEntry.ACC_OWNERS).append(" and sg_re.membersonly=").appendTrue().append(")) ").append(" group by togroup.fk_group_id, togroup.fk_entry_id ");
    List<?> rawList = dbInstance.getCurrentEntityManager().createNativeQuery(sb.toString()).setParameter("coachKey", coach.getKey()).getResultList();
    for (Object rawObject : rawList) {
        Object[] rawStats = (Object[]) rawObject;
        Long baseGroupKey = ((Number) rawStats[0]).longValue();
        GroupStatEntry entry = map.get(baseGroupKey);
        if (entry != null) {
            Long repoKey = ((Number) rawStats[1]).longValue();
            if (!entry.getRepoIds().contains(repoKey)) {
                int initalLaunch = ((Number) rawStats[2]).intValue();
                entry.setInitialLaunch(initalLaunch + entry.getInitialLaunch());
                entry.setCountCourses(entry.getCountCourses() + 1);
                entry.getRepoIds().add(repoKey);
            }
        }
    }
    return rawList.size() > 0;
}