Examples with SqlHelper org.hisp.dhis.commons.util.SqlHelper used on opensource projects

Example 11 with SqlHelper

use of org.hisp.dhis.commons.util.SqlHelper in project dhis2-core by dhis2.

the class HibernateProgramMessageStore method getHqlQuery.

// -------------------------------------------------------------------------
// Supportive Methods
// -------------------------------------------------------------------------
private Query getHqlQuery(ProgramMessageQueryParams params) {
    SqlHelper helper = new SqlHelper(true);
    String hql = " select distinct pm from " + TABLE_NAME + " pm ";
    if (params.hasProgramInstance()) {
        hql += helper.whereAnd() + "pm.programInstance = :programInstance";
    }
    if (params.hasProgramStageInstance()) {
        hql += helper.whereAnd() + "pm.programStageInstance = :programStageInstance";
    }
    hql += params.getMessageStatus() != null ? helper.whereAnd() + "pm.messageStatus = :messageStatus" : "";
    hql += params.getAfterDate() != null ? helper.whereAnd() + "pm.processeddate > :processeddate" : "";
    hql += params.getBeforeDate() != null ? helper.whereAnd() + "pm.processeddate < :processeddate" : "";
    Query query = sessionFactory.getCurrentSession().createQuery(hql);
    if (params.hasProgramInstance()) {
        query.setInteger("programInstance", params.getProgramInstance().getId());
    }
    if (params.hasProgramStageInstance()) {
        query.setInteger("programStageInstance", params.getProgramStageInstance().getId());
    }
    if (params.getMessageStatus() != null) {
        query.setParameter("messageStatus", params.getMessageStatus());
    }
    if (params.getAfterDate() != null) {
        query.setTime("processeddate", params.getAfterDate());
    }
    if (params.getBeforeDate() != null) {
        query.setTime("processeddate", params.getBeforeDate());
    }
    return query;
}

Example 12 with SqlHelper

use of org.hisp.dhis.commons.util.SqlHelper in project dhis2-core by dhis2.

the class HibernateTrackedEntityInstanceStore method getTrackedEntityInstancesGrid.

@Override
public List<Map<String, String>> getTrackedEntityInstancesGrid(TrackedEntityInstanceQueryParams params) {
    SqlHelper hlp = new SqlHelper();
    // ---------------------------------------------------------------------
    // Select clause
    // ---------------------------------------------------------------------
    String sql = "select tei.uid as " + TRACKED_ENTITY_INSTANCE_ID + ", " + "tei.created as " + CREATED_ID + ", " + "tei.lastupdated as " + LAST_UPDATED_ID + ", " + "ou.uid as " + ORG_UNIT_ID + ", " + "ou.name as " + ORG_UNIT_NAME + ", " + "te.uid as " + TRACKED_ENTITY_ID + ", " + (params.isIncludeDeleted() ? "tei.deleted as " + DELETED + ", " : "") + "tei.inactive as " + INACTIVE_ID + ", ";
    for (QueryItem item : params.getAttributes()) {
        String col = statementBuilder.columnQuote(item.getItemId());
        sql += item.isNumeric() ? "CAST( " + col + ".value AS NUMERIC ) as " : col + ".value as ";
        sql += col + ", ";
    }
    sql = removeLastComma(sql) + " ";
    // ---------------------------------------------------------------------
    // From and where clause
    // ---------------------------------------------------------------------
    sql += getFromWhereClause(params, hlp);
    // ---------------------------------------------------------------------
    // Order clause
    // ---------------------------------------------------------------------
    sql += getOrderClause(params);
    if (params.isPaging()) {
        sql += " limit " + params.getPageSizeWithDefault() + " offset " + params.getOffset();
    }
    // ---------------------------------------------------------------------
    // Query
    // ---------------------------------------------------------------------
    log.info("Query: " + sql);
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql);
    log.debug("Tracked entity instance query SQL: " + sql);
    List<Map<String, String>> list = new ArrayList<>();
    while (rowSet.next()) {
        final Map<String, String> map = new HashMap<>();
        map.put(TRACKED_ENTITY_INSTANCE_ID, rowSet.getString(TRACKED_ENTITY_INSTANCE_ID));
        map.put(CREATED_ID, rowSet.getString(CREATED_ID));
        map.put(LAST_UPDATED_ID, rowSet.getString(LAST_UPDATED_ID));
        map.put(ORG_UNIT_ID, rowSet.getString(ORG_UNIT_ID));
        map.put(ORG_UNIT_NAME, rowSet.getString(ORG_UNIT_NAME));
        map.put(TRACKED_ENTITY_ID, rowSet.getString(TRACKED_ENTITY_ID));
        map.put(INACTIVE_ID, rowSet.getString(INACTIVE_ID));
        if (params.isIncludeDeleted()) {
            map.put(DELETED, rowSet.getString(DELETED));
        }
        for (QueryItem item : params.getAttributes()) {
            map.put(item.getItemId(), rowSet.getString(item.getItemId()));
        }
        list.add(map);
    }
    return list;
}

Example 13 with SqlHelper

use of org.hisp.dhis.commons.util.SqlHelper in project dhis2-core by dhis2.

the class HibernateTrackedEntityInstanceStore method getFromWhereClause.

/**
     * From, join and where clause. For attribute params, restriction is set in
     * inner join. For query params, restriction is set in where clause.
     */
private String getFromWhereClause(TrackedEntityInstanceQueryParams params, SqlHelper hlp) {
    final String regexp = statementBuilder.getRegexpMatch();
    final String wordStart = statementBuilder.getRegexpWordStart();
    final String wordEnd = statementBuilder.getRegexpWordEnd();
    final String anyChar = "\\.*?";
    String sql = "from trackedentityinstance tei " + "inner join trackedentity te on tei.trackedentityid = te.trackedentityid " + "inner join organisationunit ou on tei.organisationunitid = ou.organisationunitid ";
    for (QueryItem item : params.getAttributesAndFilters()) {
        final String col = statementBuilder.columnQuote(item.getItemId());
        final String joinClause = item.hasFilter() ? "inner join" : "left join";
        sql += joinClause + " " + "trackedentityattributevalue as " + col + " " + "on " + col + ".trackedentityinstanceid = tei.trackedentityinstanceid " + "and " + col + ".trackedentityattributeid = " + item.getItem().getId() + " ";
        if (!params.isOrQuery() && item.hasFilter()) {
            for (QueryFilter filter : item.getFilters()) {
                final String encodedFilter = statementBuilder.encode(filter.getFilter(), false);
                final String queryCol = item.isNumeric() ? (col + ".value") : "lower(" + col + ".value)";
                sql += "and " + queryCol + " " + filter.getSqlOperator() + " " + StringUtils.lowerCase(filter.getSqlFilter(encodedFilter)) + " ";
            }
        }
    }
    if (params.hasTrackedEntity()) {
        sql += hlp.whereAnd() + " tei.trackedentityid = " + params.getTrackedEntity().getId() + " ";
    }
    params.handleOrganisationUnits();
    if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.ALL)) {
    // No restriction
    } else if (params.isOrganisationUnitMode(OrganisationUnitSelectionMode.DESCENDANTS)) {
        String ouClause = " (";
        SqlHelper orHlp = new SqlHelper(true);
        for (OrganisationUnit organisationUnit : params.getOrganisationUnits()) {
            ouClause += orHlp.or() + "ou.path like '" + organisationUnit.getPath() + "%'";
        }
        ouClause += ")";
        sql += hlp.whereAnd() + ouClause;
    } else // SELECTED (default)        
    {
        sql += hlp.whereAnd() + " tei.organisationunitid in (" + getCommaDelimitedString(getIdentifiers(params.getOrganisationUnits())) + ") ";
    }
    if (params.hasProgram()) {
        sql += hlp.whereAnd() + " exists (" + "select pi.trackedentityinstanceid " + "from programinstance pi ";
        if (params.hasEventStatus()) {
            sql += "left join programstageinstance psi " + "on pi.programinstanceid = psi.programinstanceid and psi.deleted is false ";
        }
        sql += "where pi.trackedentityinstanceid = tei.trackedentityinstanceid " + "and pi.programid = " + params.getProgram().getId() + " ";
        if (params.hasProgramStatus()) {
            sql += "and pi.status = '" + params.getProgramStatus() + "' ";
        }
        if (params.hasFollowUp()) {
            sql += "and pi.followup = " + params.getFollowUp() + " ";
        }
        if (params.hasProgramEnrollmentStartDate()) {
            sql += "and pi.enrollmentdate >= '" + getMediumDateString(params.getProgramEnrollmentStartDate()) + "' ";
        }
        if (params.hasProgramEnrollmentEndDate()) {
            sql += "and pi.enrollmentdate <= '" + getMediumDateString(params.getProgramEnrollmentEndDate()) + "' ";
        }
        if (params.hasProgramIncidentStartDate()) {
            sql += "and pi.incidentdate >= '" + getMediumDateString(params.getProgramIncidentStartDate()) + "' ";
        }
        if (params.hasProgramIncidentEndDate()) {
            sql += "and pi.incidentdate <= '" + getMediumDateString(params.getProgramIncidentEndDate()) + "' ";
        }
        if (params.hasEventStatus()) {
            sql += getEventStatusWhereClause(params);
        }
        if (!params.isIncludeDeleted()) {
            sql += " and pi.deleted is false ";
        }
        sql += ") ";
    }
    if (params.isOrQuery() && params.hasAttributesOrFilters()) {
        final String start = params.getQuery().isOperator(QueryOperator.LIKE) ? anyChar : wordStart;
        final String end = params.getQuery().isOperator(QueryOperator.LIKE) ? anyChar : wordEnd;
        sql += hlp.whereAnd() + " (";
        List<String> queryTokens = getTokens(params.getQuery().getFilter());
        for (String queryToken : queryTokens) {
            final String query = statementBuilder.encode(queryToken, false);
            sql += "(";
            for (QueryItem item : params.getAttributesAndFilters()) {
                final String col = statementBuilder.columnQuote(item.getItemId());
                sql += col + ".value " + regexp + " '" + start + StringUtils.lowerCase(query) + end + "' or ";
            }
            sql = removeLastOr(sql) + ") and ";
        }
        sql = removeLastAnd(sql) + ") ";
    }
    if (!params.isIncludeDeleted()) {
        sql += hlp.whereAnd() + " tei.deleted is false ";
    }
    return sql;
}

Example 14 with SqlHelper

use of org.hisp.dhis.commons.util.SqlHelper in project dhis2-core by dhis2.

the class HibernateUserStore method getUserQuery.

private Query getUserQuery(UserQueryParams params, boolean count) {
    SqlHelper hlp = new SqlHelper();
    String hql = count ? "select count(distinct u) " : "select distinct u ";
    hql += "from User u " + "inner join u.userCredentials uc " + "left join u.groups g ";
    if (params.getOrganisationUnit() != null) {
        hql += "left join u.organisationUnits ou ";
        if (params.getIncludeOrgUnitChildren()) {
            hql += hlp.whereAnd() + " ou.path like :organisationUnitUid ";
        } else {
            hql += hlp.whereAnd() + " ou = :organisationUnit ";
        }
    }
    if (params.getQuery() != null) {
        hql += hlp.whereAnd() + " (" + "lower(u.firstName) like :key " + "or lower(u.email) like :key " + "or lower(u.surname) like :key " + "or lower(uc.username) like :key) ";
    }
    if (params.getPhoneNumber() != null) {
        hql += hlp.whereAnd() + " u.phoneNumber = :phoneNumber ";
    }
    if (params.isCanManage() && params.getUser() != null) {
        hql += hlp.whereAnd() + " g.id in (:ids) ";
    }
    if (params.isAuthSubset() && params.getUser() != null) {
        hql += hlp.whereAnd() + " not exists (" + "select uc2 from UserCredentials uc2 " + "inner join uc2.userAuthorityGroups ag2 " + "inner join ag2.authorities a " + "where uc2.id = uc.id " + "and a not in (:auths) ) ";
    }
    if (params.isDisjointRoles() && params.getUser() != null) {
        hql += hlp.whereAnd() + " not exists (" + "select uc3 from UserCredentials uc3 " + "inner join uc3.userAuthorityGroups ag3 " + "where uc3.id = uc.id " + "and ag3.id in (:roles) ) ";
    }
    if (params.getLastLogin() != null) {
        hql += hlp.whereAnd() + " uc.lastLogin >= :lastLogin ";
    }
    if (params.getInactiveSince() != null) {
        hql += hlp.whereAnd() + " uc.lastLogin < :inactiveSince ";
    }
    if (params.getDaysPassedSincePasswordChange() != null) {
        hql += hlp.whereAnd() + " uc.passwordLastUpdated < :daysPassedSincePasswordChange ";
    }
    if (params.isSelfRegistered()) {
        hql += hlp.whereAnd() + " uc.selfRegistered = true ";
    }
    if (UserInvitationStatus.ALL.equals(params.getInvitationStatus())) {
        hql += hlp.whereAnd() + " uc.invitation = true ";
    }
    if (UserInvitationStatus.EXPIRED.equals(params.getInvitationStatus())) {
        hql += hlp.whereAnd() + " uc.invitation = true " + "and uc.restoreToken is not null " + "and uc.restoreCode is not null " + "and uc.restoreExpiry is not null " + "and uc.restoreExpiry < current_timestamp() ";
    }
    if (!count) {
        hql += "order by u.surname, u.firstName";
    }
    Query query = sessionFactory.getCurrentSession().createQuery(hql);
    if (params.getQuery() != null) {
        query.setString("key", "%" + params.getQuery().toLowerCase() + "%");
    }
    if (params.getPhoneNumber() != null) {
        query.setString("phoneNumber", params.getPhoneNumber());
    }
    if (params.isCanManage() && params.getUser() != null) {
        Collection<Integer> managedGroups = IdentifiableObjectUtils.getIdentifiers(params.getUser().getManagedGroups());
        query.setParameterList("ids", managedGroups);
    }
    if (params.isAuthSubset() && params.getUser() != null) {
        Set<String> auths = params.getUser().getUserCredentials().getAllAuthorities();
        query.setParameterList("auths", auths);
    }
    if (params.isDisjointRoles() && params.getUser() != null) {
        Collection<Integer> roles = IdentifiableObjectUtils.getIdentifiers(params.getUser().getUserCredentials().getUserAuthorityGroups());
        query.setParameterList("roles", roles);
    }
    if (params.getLastLogin() != null) {
        query.setTimestamp("lastLogin", params.getLastLogin());
    }
    if (params.getDaysPassedSincePasswordChange() != null) {
        query.setTimestamp("daysPassedSincePasswordChange", params.getDaysPassedSincePasswordChange());
    }
    if (params.getInactiveSince() != null) {
        query.setTimestamp("inactiveSince", params.getInactiveSince());
    }
    if (params.getOrganisationUnit() != null) {
        if (params.getIncludeOrgUnitChildren()) {
            // Match self and all children of selv in the path column.
            query.setString("organisationUnitUid", "%/" + params.getOrganisationUnit().getUid() + "%");
        } else {
            query.setEntity("organisationUnit", params.getOrganisationUnit());
        }
    }
    if (params.getFirst() != null) {
        query.setFirstResult(params.getFirst());
    }
    if (params.getMax() != null) {
        query.setMaxResults(params.getMax()).list();
    }
    return query;
}

Example 15 with SqlHelper

use of org.hisp.dhis.commons.util.SqlHelper in project dhis2-core by dhis2.

the class JdbcEventStore method getEventSelectQuery.

private String getEventSelectQuery(EventSearchParams params, List<OrganisationUnit> organisationUnits) {
    List<Integer> orgUnitIds = getIdentifiers(organisationUnits);
    SqlHelper hlp = new SqlHelper();
    String sql = "select psi.programstageinstanceid as psi_id, psi.uid as psi_uid, psi.code as psi_code, psi.status as psi_status, psi.executiondate as psi_executiondate, " + "psi.duedate as psi_duedate, psi.completedby as psi_completedby, psi.storedby as psi_storedby, psi.longitude as psi_longitude, " + "psi.latitude as psi_latitude, psi.created as psi_created, psi.lastupdated as psi_lastupdated, psi.completeddate as psi_completeddate, psi.deleted as psi_deleted, " + "coc.code AS coc_categoryoptioncombocode, coc.uid AS coc_categoryoptioncombouid, cocco.categoryoptionid AS cocco_categoryoptionid, " + "deco.uid AS deco_uid, pi.uid as pi_uid, pi.status as pi_status, pi.followup as pi_followup, p.uid as p_uid, p.code as p_code, " + "p.type as p_type, ps.uid as ps_uid, ps.code as ps_code, ps.capturecoordinates as ps_capturecoordinates, " + "ou.uid as ou_uid, ou.code as ou_code, ou.name as ou_name, " + "tei.trackedentityinstanceid as tei_id, tei.uid as tei_uid, teiou.uid as tei_ou, teiou.name as tei_ou_name, tei.created as tei_created, tei.inactive as tei_inactive " + "from programstageinstance psi " + "inner join programinstance pi on pi.programinstanceid=psi.programinstanceid " + "inner join program p on p.programid=pi.programid " + "inner join programstage ps on ps.programstageid=psi.programstageid " + "INNER JOIN categoryoptioncombo coc ON coc.categoryoptioncomboid=psi.attributeoptioncomboid " + "INNER JOIN categoryoptioncombos_categoryoptions cocco ON psi.attributeoptioncomboid=cocco.categoryoptioncomboid " + "INNER JOIN dataelementcategoryoption deco ON cocco.categoryoptionid=deco.categoryoptionid " + "left join trackedentityinstance tei on tei.trackedentityinstanceid=pi.trackedentityinstanceid " + "left join organisationunit ou on (psi.organisationunitid=ou.organisationunitid) " + "left join organisationunit teiou on (tei.organisationunitid=teiou.organisationunitid) ";
    if (params.getTrackedEntityInstance() != null) {
        sql += hlp.whereAnd() + " tei.trackedentityinstanceid=" + params.getTrackedEntityInstance().getId() + " ";
    }
    if (params.getProgram() != null) {
        sql += hlp.whereAnd() + " p.programid = " + params.getProgram().getId() + " ";
    }
    if (params.getProgramStage() != null) {
        sql += hlp.whereAnd() + " ps.programstageid = " + params.getProgramStage().getId() + " ";
    }
    if (params.getProgramStatus() != null) {
        sql += hlp.whereAnd() + " pi.status = '" + params.getProgramStatus() + "' ";
    }
    if (params.getFollowUp() != null) {
        sql += hlp.whereAnd() + " pi.followup is " + (params.getFollowUp() ? "true" : "false") + " ";
    }
    if (params.getLastUpdatedStartDate() != null) {
        sql += hlp.whereAnd() + " psi.lastupdated >= '" + DateUtils.getLongDateString(params.getLastUpdatedStartDate()) + "' ";
    }
    if (params.getLastUpdatedEndDate() != null) {
        Date dateAfterEndDate = getDateAfterAddition(params.getLastUpdatedEndDate(), 1);
        sql += hlp.whereAnd() + " psi.lastupdated < '" + DateUtils.getLongDateString(dateAfterEndDate) + "' ";
    }
    if (params.getCategoryOptionCombo() != null) {
        sql += hlp.whereAnd() + " psi.attributeoptioncomboid = " + params.getCategoryOptionCombo().getId() + " ";
    }
    if (orgUnitIds != null && !orgUnitIds.isEmpty()) {
        sql += hlp.whereAnd() + " psi.organisationunitid in (" + getCommaDelimitedString(orgUnitIds) + ") ";
    }
    if (params.getStartDate() != null) {
        sql += hlp.whereAnd() + " (psi.executiondate >= '" + getMediumDateString(params.getStartDate()) + "' " + "or (psi.executiondate is null and psi.duedate >= '" + getMediumDateString(params.getStartDate()) + "')) ";
    }
    if (params.getEndDate() != null) {
        Date dateAfterEndDate = getDateAfterAddition(params.getEndDate(), 1);
        sql += hlp.whereAnd() + " (psi.executiondate < '" + getMediumDateString(dateAfterEndDate) + "' " + "or (psi.executiondate is null and psi.duedate < '" + getMediumDateString(dateAfterEndDate) + "')) ";
    }
    if (params.getProgramType() != null) {
        sql += hlp.whereAnd() + " p.type = '" + params.getProgramType() + "' ";
    }
    if (params.getEventStatus() != null) {
        if (params.getEventStatus() == EventStatus.VISITED) {
            sql += hlp.whereAnd() + " psi.status = '" + EventStatus.ACTIVE.name() + "' and psi.executiondate is not null ";
        } else if (params.getEventStatus() == EventStatus.OVERDUE) {
            sql += hlp.whereAnd() + " date(now()) > date(psi.duedate) and psi.status = '" + EventStatus.SCHEDULE.name() + "' ";
        } else {
            sql += hlp.whereAnd() + " psi.status = '" + params.getEventStatus().name() + "' ";
        }
    }
    if (params.getEvents() != null && !params.getEvents().isEmpty() && !params.hasFilters()) {
        sql += hlp.whereAnd() + " (psi.uid in (" + getQuotedCommaDelimitedString(params.getEvents()) + ")) ";
    }
    if (!params.isIncludeDeleted()) {
        sql += hlp.whereAnd() + " psi.deleted is false ";
    }
    return sql;
}