use of org.olat.core.commons.persistence.NativeQueryBuilder in project OpenOLAT by OpenOLAT.
the class CoachingDAO method getStudentsStastisticInfosForCoach.
private boolean getStudentsStastisticInfosForCoach(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_olatresource sg_res on (sg_res.resource_id = sg_re.fk_olatresource and sg_res.resname = 'CourseModule') ").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_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(" 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.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 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();
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();
String[] userProperties = new String[numOfProperties];
for (int i = 0; i < numOfProperties; i++) {
userProperties[i] = (String) rawStat[pos++];
}
StudentStatEntry entry = new StudentStatEntry(identityKey, identityName, userProperties);
appendArrayToSet(rawStat[pos++], entry.getRepoIds());
appendArrayToSet(rawStat[pos++], entry.getLaunchIds());
map.put(entry.getIdentityKey(), entry);
}
return rawList.size() > 0;
}
use of org.olat.core.commons.persistence.NativeQueryBuilder in project OpenOLAT by OpenOLAT.
the class CoachingDAO method getStudentsStatisticStatement.
private boolean getStudentsStatisticStatement(IdentityRef coach, boolean hasCoached, boolean hasOwned, Map<Long, StudentStatEntry> stats) {
NativeQueryBuilder sb = new NativeQueryBuilder(1024, dbInstance);
sb.append("select ").append(" fin_statement.fk_identity, ").append(" count(fin_statement.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(" from o_as_eff_statement fin_statement ").append(" where ");
if (hasCoached) {
sb.append(" 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_coach on (sg_coach.fk_group_id=togroup.fk_group_id").append(" and sg_coach.fk_identity_id=:coachKey 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(" inner join o_as_eff_statement sg_statement ").append(" on (sg_statement.fk_identity = sg_participant.fk_identity_id and sg_statement.fk_resource_id = sg_re.fk_olatresource) ").append(" where ( ").append(" (sg_re.accesscode>2) ").append(" or ").append(" (sg_re.accesscode=1 and sg_re.membersonly=").appendTrue().append(")) ").append(" )");
}
if (hasOwned) {
if (hasCoached) {
sb.append(" or ");
}
sb.append(" 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 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 sg_owner.g_role='owner' and sg_owner.fk_identity_id=:coachKey and owngroup.r_defgroup=").appendTrue().append(")").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 ").append(" on (sg_statement.fk_identity = sg_participant.fk_identity_id and sg_statement.fk_resource_id = sg_re.fk_olatresource) ").append(" where sg_re.accesscode>=").append(RepositoryEntry.ACC_OWNERS).append(") ");
}
sb.append(" group by fin_statement.fk_identity");
List<?> rawList = dbInstance.getCurrentEntityManager().createNativeQuery(sb.toString()).setParameter("coachKey", coach.getKey()).getResultList();
for (Object rawObject : rawList) {
Object[] rawStat = (Object[]) rawObject;
Long identityKey = ((Number) rawStat[0]).longValue();
StudentStatEntry entry = stats.get(identityKey);
if (entry != null) {
int passed = ((Number) rawStat[2]).intValue();
int failed = ((Number) rawStat[3]).intValue();
entry.setCountPassed(passed);
entry.setCountFailed(failed);
}
}
return rawList.size() > 0;
}
use of org.olat.core.commons.persistence.NativeQueryBuilder in project OpenOLAT by OpenOLAT.
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;
}
use of org.olat.core.commons.persistence.NativeQueryBuilder in project OpenOLAT by OpenOLAT.
the class CoachingDAO method getGroups.
private boolean getGroups(Identity coach, Map<Long, GroupStatEntry> map) {
NativeQueryBuilder sb = new NativeQueryBuilder(1024, dbInstance);
sb.append("select ").append(" infos.group_id as grp_id, ").append(" infos.fk_group_id as bgrp_id, ").append(" infos.groupname as grp_name, ").append(" (select count(sg_participant.fk_identity_id) from o_bs_group_member sg_participant ").append(" where infos.fk_group_id = sg_participant.fk_group_id and sg_participant.g_role='participant' ").append(" ) as num_of_participant ").append(" from o_gp_business infos where infos.fk_group_id in ( select ").append(" distinct togroup.fk_group_id ").append(" from o_re_to_group togroup ").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_repositoryentry sg_re on (togroup.fk_entry_id = sg_re.repositoryentry_id) ").append(" inner join o_olatresource sg_res on (sg_res.resource_id = sg_re.fk_olatresource and sg_res.resname = 'CourseModule') ").append(" where sg_coach.fk_identity_id=:coachKey and ( ").append(" sg_re.accesscode>=").append(RepositoryEntry.ACC_USERS).append(" or ").append(" (sg_re.accesscode=").append(RepositoryEntry.ACC_OWNERS).append(" and sg_re.membersonly=").appendTrue().append(")) ").append(" ) or infos.fk_group_id in ( select ").append(" distinct togroup.fk_group_id ").append(" from o_re_to_group togroup ").append(" inner join o_repositoryentry sg_re on (togroup.fk_entry_id = sg_re.repositoryentry_id) ").append(" inner join o_olatresource sg_res on (sg_res.resource_id = sg_re.fk_olatresource and sg_res.resname = 'CourseModule') ").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(" where togroup.r_defgroup=").appendFalse().append(" and sg_owner.fk_identity_id=:coachKey and sg_re.accesscode>=").append(RepositoryEntry.ACC_OWNERS).append(" ) ");
List<?> rawList = dbInstance.getCurrentEntityManager().createNativeQuery(sb.toString()).setParameter("coachKey", coach.getKey()).getResultList();
for (Object rawObject : rawList) {
Object[] rawStat = (Object[]) rawObject;
Long groupKey = ((Number) rawStat[0]).longValue();
Long baseGroupKey = ((Number) rawStat[1]).longValue();
String title = (String) rawStat[2];
GroupStatEntry entry = new GroupStatEntry(groupKey, title);
entry.setCountDistinctStudents(((Number) rawStat[3]).intValue());
map.put(baseGroupKey, entry);
}
return rawList.size() > 0;
}
use of org.olat.core.commons.persistence.NativeQueryBuilder in project OpenOLAT by OpenOLAT.
the class CoachingDAO method getCoursesStatisticsStatements.
private boolean getCoursesStatisticsStatements(Identity coach, Map<Long, CourseStatEntry> map) {
NativeQueryBuilder sb = new NativeQueryBuilder(1024, dbInstance);
sb.append("select ").append(" fin_statement.course_repo_key, ").append(" count(fin_statement.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(" avg(fin_statement.score) ").append("from o_as_eff_statement fin_statement ").append("where fin_statement.id in ( select ").append(" distinct sg_statement.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_coach on (sg_coach.fk_group_id=togroup.fk_group_id and sg_coach.g_role in ('owner','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(" 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_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(// B
" and sg_coach.g_role = 'owner') ").append(" or ").append(" (sg_re.accesscode = ").append(RepositoryEntry.ACC_OWNERS).append(" and sg_re.membersonly=").appendTrue().append(")) ").append(") or fin_statement.id in ( select ").append(" distinct sg_statement.id ").append(" from o_repositoryentry sg_re ").append(" inner join o_re_to_group owngroup on (owngroup.fk_entry_id = sg_re.repositoryentry_id and owngroup.r_defgroup=").appendTrue().append(") ").append(" inner join o_bs_group_member sg_coach on (sg_coach.fk_group_id=owngroup.fk_group_id and sg_coach.g_role = 'owner') ").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(" where sg_coach.fk_identity_id=:coachKey and sg_re.accesscode >= ").append(RepositoryEntry.ACC_OWNERS).append(") ").append("group by fin_statement.course_repo_key ");
List<?> rawList = dbInstance.getCurrentEntityManager().createNativeQuery(sb.toString()).setParameter("coachKey", coach.getKey()).getResultList();
for (Object rawObject : rawList) {
Object[] rawStats = (Object[]) rawObject;
Long repoKey = ((Number) rawStats[0]).longValue();
CourseStatEntry entry = map.get(repoKey);
if (entry != null) {
int passed = ((Number) rawStats[2]).intValue();
int failed = ((Number) rawStats[3]).intValue();
entry.setCountFailed(failed);
entry.setCountPassed(passed);
if (rawStats[4] != null) {
entry.setAverageScore(((Number) rawStats[4]).floatValue());
}
}
}
return rawList.size() > 0;
}
Aggregations