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

Example 16 with NativeQueryBuilder

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

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;
}

Example 17 with NativeQueryBuilder

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

the class ACOrderDAO method findNativeOrderItems.

/**
 * The method is optimized for our settings: 1 order -> 1 order part -> 1 order line
 *
 * @param resource
 * @param delivery
 * @return
 */
public List<RawOrderItem> findNativeOrderItems(OLATResource resource, IdentityRef delivery, Long orderNr, Date from, Date to, OrderStatus[] status, int firstResult, int maxResults, List<UserPropertyHandler> userPropertyHandlers, SortKey... orderBy) {
    NativeQueryBuilder sb = new NativeQueryBuilder(1024, dbInstance);
    sb.append("select").append("  o.order_id as order_id,").append("  o.total_currency_code as total_currency_code,").append("  o.total_amount as total_amount,").append("  o.creationdate as creationdate,").append("  o.order_status as o_status,").append("  o.fk_delivery_id as delivery_id,").append("  ").appendToArray("offer.resourcedisplayname").append(" as resDisplaynames,").append("  ").appendToArray("trx.trx_status").append(" as trxStatus,").append("  ").appendToArray("trx.fk_method_id").append(" as trxMethodIds,").append("  ").appendToArray("pspTrx.trx_status").append(" as pspTrxStatus");
    if (delivery == null) {
        sb.append("  ,delivery.id as delivery_ident_id").append("  ,delivery.name as delivery_ident_name").append("  ,delivery_user.user_id as delivery_user_id");
        if (userPropertyHandlers != null) {
            for (UserPropertyHandler handler : userPropertyHandlers) {
                sb.append(" ,delivery_user.").append(handler.getDatabaseColumnName()).append(" as ").append(handler.getName());
            }
        }
    }
    sb.append(" from o_ac_order o").append(" inner join o_ac_order_part order_part on (o.order_id=order_part.fk_order_id and order_part.pos=0)").append(" inner join o_ac_order_line order_line on (order_part.order_part_id=order_line.fk_order_part_id and order_line.pos=0)").append(" inner join o_ac_offer offer on (order_line.fk_offer_id=offer.offer_id)");
    if (delivery == null) {
        sb.append(" inner join o_bs_identity delivery on (delivery.id=o.fk_delivery_id)").append(" inner join o_user delivery_user on (delivery_user.fk_identity=delivery.id)");
    }
    sb.append(" left join o_ac_paypal_transaction pspTrx on (o.order_id = pspTrx.order_id)").append(" left join o_ac_transaction trx on (o.order_id = trx.fk_order_id)");
    boolean where = false;
    if (resource != null) {
        where = PersistenceHelper.appendAnd(sb, where);
        sb.append(" offer.fk_resource_id=:resourceKey ");
    }
    if (delivery != null) {
        where = PersistenceHelper.appendAnd(sb, where);
        sb.append(" o.fk_delivery_id=:deliveryKey ");
    }
    if (status != null && status.length > 0 && status[0] != null) {
        where = PersistenceHelper.appendAnd(sb, where);
        sb.append("o.order_status in (:status)");
    }
    if (from != null) {
        where = PersistenceHelper.appendAnd(sb, where);
        sb.append("o.creationdate >=:from");
    }
    if (to != null) {
        where = PersistenceHelper.appendAnd(sb, where);
        sb.append("o.creationdate <=:to");
    }
    if (orderNr != null) {
        where = PersistenceHelper.appendAnd(sb, where);
        sb.append("o.order_id=:orderNr");
    }
    sb.append(" group by o.order_id");
    if (dbInstance.isOracle()) {
        sb.append(", o.total_currency_code, o.total_amount, o.creationdate, o.order_status, o.fk_delivery_id");
    }
    if (delivery == null) {
        sb.append(", delivery.id, delivery_user.user_id");
        if (dbInstance.isOracle()) {
            sb.append(", delivery.name");
            if (userPropertyHandlers != null) {
                for (UserPropertyHandler handler : userPropertyHandlers) {
                    sb.append(", delivery_user.").append(handler.getDatabaseColumnName());
                }
            }
        }
    }
    if (orderBy != null && orderBy.length > 0 && orderBy[0] != null) {
        sb.appendOrderBy(orderBy[0]);
    }
    Query query = dbInstance.getCurrentEntityManager().createNativeQuery(sb.toString());
    if (resource != null) {
        query.setParameter("resourceKey", resource.getKey());
    }
    if (delivery != null) {
        query.setParameter("deliveryKey", delivery.getKey());
    }
    if (status != null && status.length > 0 && status[0] != null) {
        List<String> statusStr = new ArrayList<String>();
        for (OrderStatus s : status) {
            statusStr.add(s.name());
        }
        query.setParameter("status", statusStr);
    }
    if (orderNr != null) {
        query.setParameter("orderNr", orderNr);
    }
    if (from != null) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(from);
        cal.set(Calendar.HOUR, 0);
        cal.set(Calendar.MINUTE, 0);
        cal.set(Calendar.SECOND, 0);
        cal.set(Calendar.MILLISECOND, 0);
        query.setParameter("from", cal.getTime(), TemporalType.TIMESTAMP);
    }
    if (to != null) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(to);
        cal.set(Calendar.HOUR, 23);
        cal.set(Calendar.MINUTE, 59);
        cal.set(Calendar.SECOND, 59);
        cal.set(Calendar.MILLISECOND, 0);
        query.setParameter("to", cal.getTime(), TemporalType.TIMESTAMP);
    }
    if (maxResults > 0) {
        query.setFirstResult(firstResult).setMaxResults(maxResults);
    }
    int numOfProperties = userPropertyHandlers == null ? 0 : userPropertyHandlers.size();
    List<?> rawOrders = query.getResultList();
    List<RawOrderItem> items = new ArrayList<>(rawOrders.size());
    for (Object rawOrder : rawOrders) {
        Object[] order = (Object[]) rawOrder;
        int pos = 0;
        Long orderKey = ((Number) order[pos++]).longValue();
        String totalCurrencyCode = (String) order[pos++];
        BigDecimal totalAmount = (BigDecimal) order[pos++];
        Date creationDate = (Date) order[pos++];
        String orderStatus = (String) order[pos++];
        Long deliveryKey = ((Number) order[pos++]).longValue();
        String resourceName = (String) order[pos++];
        String trxStatus = (String) order[pos++];
        String trxMethodIds = (String) order[pos++];
        String pspTrxStatus = (String) order[pos++];
        String username = null;
        String[] userProperties = null;
        if (numOfProperties > 0) {
            // identityKey
            pos++;
            username = (String) order[pos++];
            // userKey
            pos++;
            userProperties = new String[numOfProperties];
            for (int i = 0; i < numOfProperties; i++) {
                userProperties[i] = (String) order[pos++];
            }
        }
        RawOrderItem item = new RawOrderItem(orderKey, orderKey.toString(), totalCurrencyCode, totalAmount, creationDate, orderStatus, deliveryKey, resourceName, trxStatus, trxMethodIds, pspTrxStatus, username, userProperties);
        items.add(item);
    }
    return items;
}

Example 18 with NativeQueryBuilder

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

the class CoachingDAO method getCoursesStatisticsUserInfosForOwner.

private boolean getCoursesStatisticsUserInfosForOwner(Identity coach, Map<Long, CourseStatEntry> map) {
    NativeQueryBuilder sb = new NativeQueryBuilder(1024, dbInstance);
    if (dbInstance.isMySQL()) {
        sb.append("select").append("  sg_re.repositoryentry_id as re_id,").append("  count(distinct sg_participant.fk_identity_id) as student_id,").append("  count(distinct pg_initial_launch.id) as pg_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(" 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 sg_re.accesscode >= ").append(RepositoryEntry.ACC_OWNERS).append(" group by sg_re.repositoryentry_id");
    } else {
        sb.append("select").append("  sg_re.repositoryentry_id as re_id,").append("  count(distinct sg_participant.fk_identity_id) as student_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.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(" 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_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(" ) and exists (").append("  select owngroup.id from o_re_to_group owngroup inner join o_bs_group_member sg_owner on (sg_owner.fk_group_id=owngroup.fk_group_id)").append("  where owngroup.fk_entry_id = sg_re.repositoryentry_id and owngroup.r_defgroup=").appendTrue().append(" and sg_owner.g_role='owner' and sg_owner.fk_identity_id=:coachKey").append(" )").append(" group by sg_re.repositoryentry_id");
    }
    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) {
            entry.setCountStudents(((Number) rawStats[1]).intValue());
            entry.setInitialLaunch(((Number) rawStats[2]).intValue());
        }
    }
    return rawList.size() > 0;
}

Example 19 with NativeQueryBuilder

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

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 20 with NativeQueryBuilder

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

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;
}