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

Example 21 with SqlHelper

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

the class HibernateValidationResultStore method getQueryRestrictions.

private String getQueryRestrictions(ValidationResultQuery query) {
    StringBuilder restrictions = new StringBuilder();
    SqlHelper sqlHelper = new SqlHelper();
    restrictions.append(getUserRestrictions(sqlHelper));
    if (!isEmpty(query.getOu())) {
        restrictions.append(" " + sqlHelper.whereAnd() + " vr.organisationUnit.uid in :orgUnitsUids ");
    }
    if (!isEmpty(query.getVr())) {
        restrictions.append(" " + sqlHelper.whereAnd() + " vr.validationRule.uid in :validationRulesUids ");
    }
    if (!isEmpty(query.getPe())) {
        restrictions.append(" " + sqlHelper.whereAnd() + "(");
        for (int i = 1; i <= query.getPe().size(); i++) {
            if (i > 1) {
                restrictions.append(" or ");
            }
            String parameterName = ":periodId" + i;
            restrictions.append(" ((vr.period.startDate <= " + parameterName + "End ) and (vr.period.endDate >= " + parameterName + "Start ))");
        }
        restrictions.append(")");
    }
    if (query.getCreatedDate() != null) {
        restrictions.append(" " + sqlHelper.whereAnd() + " vr.created >= :createdDate ");
    }
    return restrictions.toString();
}

Example 22 with SqlHelper

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

the class HibernateValidationResultStore method getById.

@Override
public ValidationResult getById(long id) {
    SqlHelper sqlHelper = new SqlHelper();
    // as we use where already below
    sqlHelper.whereAnd();
    return getSingleResult(getQuery("from ValidationResult vr where vr.id = :id" + getUserRestrictions(sqlHelper)).setParameter("id", id));
}

Example 23 with SqlHelper

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

the class HibernateValidationResultStore method delete.

@Override
@SuppressWarnings("unchecked")
public void delete(ValidationResultsDeletionRequest request) {
    SqlHelper helper = new SqlHelper();
    StringBuilder hql = new StringBuilder();
    hql.append("delete from ValidationResult vr ");
    if (!isEmpty(request.getOu())) {
        // OBS! sub-select is needed to avoid issue with wrongly created
        // cross join
        hql.append(helper.whereAnd()).append(" vr.organisationUnit in (select ou.id from OrganisationUnit ou where ou.uid in :unitsUids) ");
    }
    if (!isEmpty(request.getVr())) {
        // OBS! sub-select is needed to avoid issue with wrongly created
        // cross join
        hql.append(helper.whereAnd()).append(" vr.validationRule in (select r.id from ValidationRule r where r.uid in :rulesUids) ");
    }
    if (request.getPe() != null) {
        // OBS! sub-select is needed to avoid issue with wrongly created
        // cross join
        hql.append(helper.whereAnd()).append(" vr.period in (select p.id from Period p where p.startDate <= :endDate and p.endDate >= :startDate) ");
    }
    if (request.getCreated() != null) {
        hql.append(helper.whereAnd()).append(" ((vr.created >= :createdStartDate and vr.created <= :createdEndDate)) ");
    }
    if (request.getNotificationSent() != null) {
        hql.append(helper.whereAnd()).append(" vr.notificationSent = :notificationSent ");
    }
    Query<ValidationResult> query = getSession().createQuery(hql.toString());
    if (!isEmpty(request.getOu())) {
        query.setParameter("unitsUids", request.getOu());
    }
    if (!isEmpty(request.getVr())) {
        query.setParameter("rulesUids", request.getVr());
    }
    if (request.getPe() != null) {
        Period p = PeriodType.getPeriodFromIsoString(request.getPe());
        query.setParameter("startDate", p.getStartDate());
        query.setParameter("endDate", p.getEndDate());
    }
    if (request.getCreated() != null) {
        Period p = PeriodType.getPeriodFromIsoString(request.getCreated());
        query.setParameter("createdStartDate", p.getStartDate());
        query.setParameter("createdEndDate", p.getEndDate());
    }
    if (request.getNotificationSent() != null) {
        query.setParameter("notificationSent", request.getNotificationSent());
    }
    query.executeUpdate();
}

Example 24 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 25 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;
}