Examples with IntegerRange de.symeda.sormas.api.IntegerRange used on opensource projects

Example 1 with IntegerRange

use of de.symeda.sormas.api.IntegerRange in project SORMAS-Project by hzi-braunschweig.

the class CaseStatisticsFacadeEjb method buildCaseCountQuery.

/**
 * private void replaceIdsWithGroupingKeys(List<StatisticsCaseCountDto> results, StatisticsCaseAttribute groupingA,
 * for (StatisticsCaseCountDto result : results) {
 * Builds SQL query string and list of parameters (for filters)
 */
public Pair<String, List<Object>> buildCaseCountQuery(StatisticsCaseCriteria caseCriteria, StatisticsCaseAttribute groupingA, StatisticsCaseSubAttribute subGroupingA, StatisticsCaseAttribute groupingB, StatisticsCaseSubAttribute subGroupingB) {
    // Steps to build the query:
    // 1. Join the required tables
    // 2. Build the filter query
    // 3. Add selected groupings
    // 4. Retrieve and prepare the results
    // ///////////
    // 1. Join tables that cases are grouped by or that are used in the caseCriteria
    // ///////////
    StringBuilder caseJoinBuilder = new StringBuilder();
    if (subGroupingA == StatisticsCaseSubAttribute.FACILITY || subGroupingB == StatisticsCaseSubAttribute.FACILITY) {
        caseJoinBuilder.append(" LEFT JOIN ").append(Facility.TABLE_NAME).append(" ON ").append(Case.TABLE_NAME).append(".").append(Case.HEALTH_FACILITY).append("_id = ").append(Facility.TABLE_NAME).append(".").append(Facility.ID);
    }
    if (subGroupingA == StatisticsCaseSubAttribute.COMMUNITY || subGroupingB == StatisticsCaseSubAttribute.COMMUNITY) {
        caseJoinBuilder.append(" LEFT JOIN ").append(Community.TABLE_NAME).append(" ON ").append(Case.TABLE_NAME).append(".").append(Case.RESPONSIBLE_COMMUNITY).append("_id = ").append(Community.TABLE_NAME).append(".").append(Community.ID);
    }
    if (subGroupingA == StatisticsCaseSubAttribute.DISTRICT || subGroupingB == StatisticsCaseSubAttribute.DISTRICT) {
        caseJoinBuilder.append(" LEFT JOIN ").append(District.TABLE_NAME).append(" ON ").append(Case.TABLE_NAME).append(".").append(Case.RESPONSIBLE_DISTRICT).append("_id = ").append(District.TABLE_NAME).append(".").append(District.ID);
    }
    if (subGroupingA == StatisticsCaseSubAttribute.REGION || subGroupingB == StatisticsCaseSubAttribute.REGION) {
        caseJoinBuilder.append(" LEFT JOIN ").append(Region.TABLE_NAME).append(" ON ").append(Case.TABLE_NAME).append(".").append(Case.RESPONSIBLE_REGION).append("_id = ").append(Region.TABLE_NAME).append(".").append(Region.ID);
    }
    if (groupingA == StatisticsCaseAttribute.ONSET_TIME || groupingB == StatisticsCaseAttribute.ONSET_TIME || caseCriteria.hasOnsetDate()) {
        caseJoinBuilder.append(" LEFT JOIN ").append(Symptoms.TABLE_NAME).append(" ON ").append(Case.TABLE_NAME).append(".").append(Case.SYMPTOMS).append("_id").append(" = ").append(Symptoms.TABLE_NAME).append(".").append(Symptoms.ID);
    }
    if (groupingA == StatisticsCaseAttribute.SEX || groupingB == StatisticsCaseAttribute.SEX || groupingA == StatisticsCaseAttribute.AGE_INTERVAL_1_YEAR || groupingB == StatisticsCaseAttribute.AGE_INTERVAL_1_YEAR || groupingA == StatisticsCaseAttribute.AGE_INTERVAL_5_YEARS || groupingB == StatisticsCaseAttribute.AGE_INTERVAL_5_YEARS || groupingA == StatisticsCaseAttribute.AGE_INTERVAL_CHILDREN_COARSE || groupingB == StatisticsCaseAttribute.AGE_INTERVAL_CHILDREN_COARSE || groupingA == StatisticsCaseAttribute.AGE_INTERVAL_CHILDREN_FINE || groupingB == StatisticsCaseAttribute.AGE_INTERVAL_CHILDREN_FINE || groupingA == StatisticsCaseAttribute.AGE_INTERVAL_CHILDREN_MEDIUM || groupingB == StatisticsCaseAttribute.AGE_INTERVAL_CHILDREN_MEDIUM || groupingA == StatisticsCaseAttribute.AGE_INTERVAL_BASIC || groupingB == StatisticsCaseAttribute.AGE_INTERVAL_BASIC || caseCriteria.getSexes() != null || caseCriteria.getAgeIntervals() != null || caseCriteria.getPersonRegions() != null || caseCriteria.getPersonDistricts() != null || caseCriteria.getPersonCommunities() != null || caseCriteria.getPersonCity() != null || caseCriteria.getPersonPostcode() != null) {
        caseJoinBuilder.append(" LEFT JOIN ").append(Person.TABLE_NAME).append(" ON ").append(Case.TABLE_NAME).append(".").append(Case.PERSON).append("_id").append(" = ").append(Person.TABLE_NAME).append(".").append(Person.ID);
    }
    if (caseCriteria.getPersonRegions() != null || caseCriteria.getPersonDistricts() != null || caseCriteria.getPersonCommunities() != null || caseCriteria.getPersonCity() != null || caseCriteria.getPersonPostcode() != null) {
        caseJoinBuilder.append(" LEFT JOIN ").append(Location.TABLE_NAME).append(" ON ").append(Person.TABLE_NAME).append(".").append(Person.ADDRESS).append("_id").append(" = ").append(Location.TABLE_NAME).append(".").append(Location.ID);
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getReportingUserRoles()) || groupingA == StatisticsCaseAttribute.REPORTING_USER_ROLE || groupingB == StatisticsCaseAttribute.REPORTING_USER_ROLE) {
        caseJoinBuilder.append(" LEFT JOIN ").append(User.TABLE_NAME_USERROLES).append(" ON ").append(Case.TABLE_NAME).append(".").append(Case.REPORTING_USER).append("_id").append(" = ").append(User.TABLE_NAME_USERROLES).append(".").append(UserDto.COLUMN_NAME_USER_ID);
    }
    // ///////////
    // 2. Build filter based on caseCriteria
    // ///////////
    StringBuilder caseFilterBuilder = new StringBuilder(" WHERE ");
    caseFilterBuilder.append("(").append(Case.TABLE_NAME).append(".").append(Case.DELETED).append(" = false");
    // needed for the full join on population
    caseFilterBuilder.append(" OR ").append(Case.TABLE_NAME).append(".").append(Case.DELETED).append(" IS NULL ");
    caseFilterBuilder.append(")");
    List<Object> filterBuilderParameters = new ArrayList<Object>();
    if (CollectionUtils.isNotEmpty(caseCriteria.getOnsetYears())) {
        extendFilterBuilderWithDateElement(caseFilterBuilder, filterBuilderParameters, "YEAR", Symptoms.TABLE_NAME, Symptoms.ONSET_DATE, caseCriteria.getOnsetYears(), dateValue -> (dateValue.getValue()));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOnsetQuarters())) {
        extendFilterBuilderWithDateElement(caseFilterBuilder, filterBuilderParameters, "QUARTER", Symptoms.TABLE_NAME, Symptoms.ONSET_DATE, caseCriteria.getOnsetQuarters(), dateValue -> (dateValue.getValue()));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOnsetMonths())) {
        extendFilterBuilderWithDateElement(caseFilterBuilder, filterBuilderParameters, "MONTH", Symptoms.TABLE_NAME, Symptoms.ONSET_DATE, caseCriteria.getOnsetMonths(), dateValue -> (dateValue.ordinal() + 1));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOnsetEpiWeeks())) {
        extendFilterBuilderWithEpiWeek(caseFilterBuilder, filterBuilderParameters, Symptoms.TABLE_NAME, Symptoms.ONSET_DATE, caseCriteria.getOnsetEpiWeeks(), value -> value.getWeek());
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOnsetQuartersOfYear())) {
        extendFilterBuilderWithQuarterOfYear(caseFilterBuilder, filterBuilderParameters, Symptoms.TABLE_NAME, Symptoms.ONSET_DATE, caseCriteria.getOnsetQuartersOfYear(), value -> value.getYear().getValue() * 10 + value.getQuarter().getValue());
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOnsetMonthsOfYear())) {
        extendFilterBuilderWithMonthOfYear(caseFilterBuilder, filterBuilderParameters, Symptoms.TABLE_NAME, Symptoms.ONSET_DATE, caseCriteria.getOnsetMonthsOfYear(), value -> value.getYear().getValue() * 100 + (value.getMonth().ordinal() + 1));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOnsetEpiWeeksOfYear())) {
        extendFilterBuilderWithEpiWeekOfYear(caseFilterBuilder, filterBuilderParameters, Symptoms.TABLE_NAME, Symptoms.ONSET_DATE, caseCriteria.getOnsetEpiWeeksOfYear(), value -> value.getYear() * 100 + value.getWeek());
    }
    if (caseCriteria.getOnsetDateFrom() != null || caseCriteria.getOnsetDateTo() != null) {
        extendFilterBuilderWithDate(caseFilterBuilder, filterBuilderParameters, caseCriteria.getOnsetDateFrom(), caseCriteria.getOnsetDateTo(), Symptoms.TABLE_NAME, Symptoms.ONSET_DATE);
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getReportYears())) {
        extendFilterBuilderWithDateElement(caseFilterBuilder, filterBuilderParameters, "YEAR", Case.TABLE_NAME, Case.REPORT_DATE, caseCriteria.getReportYears(), dateValue -> (dateValue.getValue()));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getReportQuarters())) {
        extendFilterBuilderWithDateElement(caseFilterBuilder, filterBuilderParameters, "QUARTER", Case.TABLE_NAME, Case.REPORT_DATE, caseCriteria.getReportQuarters(), dateValue -> (dateValue.getValue()));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getReportMonths())) {
        extendFilterBuilderWithDateElement(caseFilterBuilder, filterBuilderParameters, "MONTH", Case.TABLE_NAME, Case.REPORT_DATE, caseCriteria.getReportMonths(), dateValue -> (dateValue.ordinal() + 1));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getReportEpiWeeks())) {
        extendFilterBuilderWithEpiWeek(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.REPORT_DATE, caseCriteria.getReportEpiWeeks(), value -> value.getWeek());
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getReportQuartersOfYear())) {
        extendFilterBuilderWithQuarterOfYear(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.REPORT_DATE, caseCriteria.getReportQuartersOfYear(), value -> value.getYear().getValue() * 10 + value.getQuarter().getValue());
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getReportMonthsOfYear())) {
        extendFilterBuilderWithMonthOfYear(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.REPORT_DATE, caseCriteria.getReportMonthsOfYear(), value -> value.getYear().getValue() * 100 + (value.getMonth().ordinal() + 1));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getReportEpiWeeksOfYear())) {
        extendFilterBuilderWithEpiWeekOfYear(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.REPORT_DATE, caseCriteria.getReportEpiWeeksOfYear(), value -> value.getYear() * 100 + value.getWeek());
    }
    if (caseCriteria.getReportDateFrom() != null || caseCriteria.getReportDateTo() != null) {
        extendFilterBuilderWithDate(caseFilterBuilder, filterBuilderParameters, caseCriteria.getReportDateFrom(), caseCriteria.getReportDateTo(), Case.TABLE_NAME, Case.REPORT_DATE);
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOutcomeYears())) {
        extendFilterBuilderWithDateElement(caseFilterBuilder, filterBuilderParameters, "YEAR", Case.TABLE_NAME, Case.OUTCOME_DATE, caseCriteria.getOutcomeYears(), dateValue -> (dateValue.getValue()));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOutcomeQuarters())) {
        extendFilterBuilderWithDateElement(caseFilterBuilder, filterBuilderParameters, "QUARTER", Case.TABLE_NAME, Case.OUTCOME_DATE, caseCriteria.getOutcomeQuarters(), dateValue -> (dateValue.getValue()));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOutcomeMonths())) {
        extendFilterBuilderWithDateElement(caseFilterBuilder, filterBuilderParameters, "MONTH", Case.TABLE_NAME, Case.OUTCOME_DATE, caseCriteria.getOutcomeMonths(), dateValue -> (dateValue.ordinal() + 1));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOutcomeEpiWeeks())) {
        extendFilterBuilderWithEpiWeek(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.OUTCOME_DATE, caseCriteria.getOutcomeEpiWeeks(), value -> value.getWeek());
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOutcomeQuartersOfYear())) {
        extendFilterBuilderWithQuarterOfYear(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.OUTCOME_DATE, caseCriteria.getOutcomeQuartersOfYear(), value -> value.getYear().getValue() * 10 + value.getQuarter().getValue());
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOutcomeMonthsOfYear())) {
        extendFilterBuilderWithMonthOfYear(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.OUTCOME_DATE, caseCriteria.getOutcomeMonthsOfYear(), value -> value.getYear().getValue() * 100 + (value.getMonth().ordinal() + 1));
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOutcomeEpiWeeksOfYear())) {
        extendFilterBuilderWithEpiWeekOfYear(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.OUTCOME_DATE, caseCriteria.getOutcomeEpiWeeksOfYear(), value -> value.getYear() * 100 + value.getWeek());
    }
    if (caseCriteria.getOutcomeDateFrom() != null || caseCriteria.getOutcomeDateTo() != null) {
        extendFilterBuilderWithDate(caseFilterBuilder, filterBuilderParameters, caseCriteria.getOutcomeDateFrom(), caseCriteria.getOutcomeDateTo(), Case.TABLE_NAME, Case.OUTCOME_DATE);
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getPersonRegions())) {
        List<Long> regionIds = regionService.getIdsByReferenceDtos(caseCriteria.getPersonRegions());
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, Location.TABLE_NAME, Location.REGION + "_id", regionIds, entry -> entry);
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getPersonDistricts())) {
        List<Long> districtIds = districtService.getIdsByReferenceDtos(caseCriteria.getPersonDistricts());
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, Location.TABLE_NAME, Location.DISTRICT + "_id", districtIds, entry -> entry);
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getPersonCommunities())) {
        List<Long> communityIds = communityService.getIdsByReferenceDtos(caseCriteria.getPersonCommunities());
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, Location.TABLE_NAME, Location.COMMUNITY + "_id", communityIds, entry -> entry);
    }
    if (StringUtils.isNotEmpty(caseCriteria.getPersonCity())) {
        extendFilterBuilderWithLike(caseFilterBuilder, Location.TABLE_NAME, Location.CITY, caseCriteria.getPersonCity());
    }
    if (StringUtils.isNotEmpty(caseCriteria.getPersonPostcode())) {
        extendFilterBuilderWithLike(caseFilterBuilder, Location.TABLE_NAME, Location.POSTAL_CODE, caseCriteria.getPersonPostcode());
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getSexes()) || caseCriteria.isSexUnknown() != null) {
        if (caseFilterBuilder.length() > 0) {
            caseFilterBuilder.append(" AND ");
        }
        caseFilterBuilder.append("(");
        StringBuilder subFilterBuilder = new StringBuilder();
        if (CollectionUtils.isNotEmpty(caseCriteria.getSexes())) {
            extendFilterBuilderWithSimpleValue(subFilterBuilder, filterBuilderParameters, Person.TABLE_NAME, Person.SEX, caseCriteria.getSexes(), entry -> entry.name());
        }
        if (caseCriteria.isSexUnknown() != null) {
            if (subFilterBuilder.length() > 0) {
                subFilterBuilder.append(" OR ");
            }
            subFilterBuilder.append(Person.TABLE_NAME).append(".").append(Person.SEX).append(" IS ").append(caseCriteria.isSexUnknown() == true ? "NULL" : "NOT NULL");
        }
        caseFilterBuilder.append(subFilterBuilder);
        caseFilterBuilder.append(")");
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getAgeIntervals())) {
        if (caseFilterBuilder.length() > 0) {
            caseFilterBuilder.append(" AND ");
        }
        caseFilterBuilder.append("(");
        StringBuilder subFilterBuilder = new StringBuilder();
        Integer upperRangeBoundary = null;
        boolean appendUnknown = false;
        List<Integer> agesList = new ArrayList<Integer>();
        for (IntegerRange range : caseCriteria.getAgeIntervals()) {
            if (range.getTo() == null) {
                if (range.getFrom() == null) {
                    appendUnknown = true;
                } else {
                    upperRangeBoundary = range.getFrom();
                }
            } else {
                agesList.addAll(IntStream.rangeClosed(range.getFrom(), range.getTo()).boxed().collect(Collectors.toList()));
            }
        }
        if (agesList.size() > 0) {
            extendFilterBuilderWithSimpleValue(subFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.CASE_AGE, agesList, value -> value);
        }
        if (upperRangeBoundary != null) {
            if (subFilterBuilder.length() > 0) {
                subFilterBuilder.append(" OR ");
            }
            subFilterBuilder.append(Case.TABLE_NAME).append(".").append(Case.CASE_AGE).append(" >= ?").append(filterBuilderParameters.size() + 1);
            filterBuilderParameters.add(upperRangeBoundary);
        }
        if (appendUnknown) {
            if (subFilterBuilder.length() > 0) {
                subFilterBuilder.append(" OR ");
            }
            subFilterBuilder.append(Case.TABLE_NAME).append(".").append(Case.CASE_AGE).append(" IS NULL");
        }
        caseFilterBuilder.append(subFilterBuilder);
        caseFilterBuilder.append(")");
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getDiseases())) {
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.DISEASE, caseCriteria.getDiseases(), entry -> entry.name());
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getClassifications())) {
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.CASE_CLASSIFICATION, caseCriteria.getClassifications(), entry -> entry.name());
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getOutcomes())) {
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.OUTCOME, caseCriteria.getOutcomes(), entry -> entry.name());
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getRegions())) {
        List<Long> regionIds = regionService.getIdsByReferenceDtos(caseCriteria.getRegions());
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.RESPONSIBLE_REGION + "_id", regionIds, entry -> entry);
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getDistricts())) {
        List<Long> districtIds = districtService.getIdsByReferenceDtos(caseCriteria.getDistricts());
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.RESPONSIBLE_DISTRICT + "_id", districtIds, entry -> entry);
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getCommunities())) {
        List<Long> communityIds = communityService.getIdsByReferenceDtos(caseCriteria.getCommunities());
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.RESPONSIBLE_COMMUNITY + "_id", communityIds, entry -> entry);
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getHealthFacilities())) {
        List<Long> facilityIds = facilityService.getIdsByReferenceDtos(caseCriteria.getHealthFacilities());
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, Case.TABLE_NAME, Case.HEALTH_FACILITY + "_id", facilityIds, entry -> entry);
    }
    if (CollectionUtils.isNotEmpty(caseCriteria.getReportingUserRoles())) {
        extendFilterBuilderWithSimpleValue(caseFilterBuilder, filterBuilderParameters, User.TABLE_NAME_USERROLES, UserDto.COLUMN_NAME_USERROLE, caseCriteria.getReportingUserRoles(), entry -> entry.name());
    }
    // ////////////
    // 3. Add selected groupings
    // ///////////
    String groupingSelectQueryA = null, groupingSelectQueryB = null;
    StringBuilder caseGroupByBuilder = new StringBuilder();
    StringBuilder orderByBuilder = new StringBuilder();
    String groupAAlias = "groupA";
    String groupBAlias = "groupB";
    if (groupingA != null || groupingB != null) {
        caseGroupByBuilder.append(" GROUP BY ");
        if (groupingA != null) {
            groupingSelectQueryA = buildCaseGroupingSelectQuery(groupingA, subGroupingA, groupAAlias);
            caseGroupByBuilder.append(groupAAlias);
        }
        if (groupingB != null) {
            groupingSelectQueryB = buildCaseGroupingSelectQuery(groupingB, subGroupingB, groupBAlias);
            if (groupingA != null) {
                caseGroupByBuilder.append(",");
            }
            caseGroupByBuilder.append(groupBAlias);
        }
    }
    // ////////////
    // 4. Order results
    // ///////////
    orderByBuilder.append(" ORDER BY ");
    if (groupingA != null) {
        orderByBuilder.append(groupAAlias).append(" NULLS LAST");
    }
    if (groupingB != null) {
        if (groupingA != null) {
            orderByBuilder.append(",");
        }
        orderByBuilder.append(groupBAlias).append(" NULLS LAST");
    }
    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append("SELECT COUNT(*) AS casecount ");
    if (groupingSelectQueryA != null) {
        queryBuilder.append(", ").append(groupingSelectQueryA);
    } else {
        queryBuilder.append(", null\\:\\:text AS ").append(groupAAlias);
    }
    if (groupingSelectQueryB != null) {
        queryBuilder.append(", ").append(groupingSelectQueryB);
    } else {
        queryBuilder.append(", null\\:\\:text AS ").append(groupBAlias);
    }
    queryBuilder.append(" FROM ").append(Case.TABLE_NAME).append(caseJoinBuilder).append(caseFilterBuilder).append(caseGroupByBuilder);
    if (groupingA != null || groupingB != null) {
        queryBuilder.append(orderByBuilder);
    }
    return new ImmutablePair<String, List<Object>>(queryBuilder.toString(), filterBuilderParameters);
}

Example 2 with IntegerRange

use of de.symeda.sormas.api.IntegerRange in project SORMAS-Project by hzi-braunschweig.

the class StatisticsHelper method getAgeIntervalGroupingKeys.

public static List<StatisticsGroupingKey> getAgeIntervalGroupingKeys(StatisticsCaseAttribute attribute) {
    List<StatisticsGroupingKey> ageIntervalList = new ArrayList<>();
    switch(attribute) {
        case AGE_INTERVAL_1_YEAR:
            for (int i = 0; i < 80; i++) {
                ageIntervalList.add(new IntegerRange(i, i));
            }
            break;
        case AGE_INTERVAL_5_YEARS:
            for (int i = 0; i < 80; i += 5) {
                ageIntervalList.add(new IntegerRange(i, i + 4));
            }
            break;
        case AGE_INTERVAL_CHILDREN_COARSE:
            ageIntervalList.add(new IntegerRange(0, 14));
            for (int i = 15; i < 30; i += 5) {
                ageIntervalList.add(new IntegerRange(i, i + 4));
            }
            for (int i = 30; i < 80; i += 10) {
                ageIntervalList.add(new IntegerRange(i, i + 9));
            }
            break;
        case AGE_INTERVAL_CHILDREN_FINE:
            for (int i = 0; i < 5; i++) {
                ageIntervalList.add(new IntegerRange(i, i));
            }
            for (int i = 5; i < 30; i += 5) {
                ageIntervalList.add(new IntegerRange(i, i + 4));
            }
            for (int i = 30; i < 80; i += 10) {
                ageIntervalList.add(new IntegerRange(i, i + 9));
            }
            break;
        case AGE_INTERVAL_CHILDREN_MEDIUM:
            for (int i = 0; i < 30; i += 5) {
                ageIntervalList.add(new IntegerRange(i, i + 4));
            }
            for (int i = 30; i < 80; i += 10) {
                ageIntervalList.add(new IntegerRange(i, i + 9));
            }
            break;
        case AGE_INTERVAL_BASIC:
            ageIntervalList.add(new IntegerRange(0, 0));
            ageIntervalList.add(new IntegerRange(1, 4));
            ageIntervalList.add(new IntegerRange(5, 14));
            ageIntervalList.add(new IntegerRange(15, null));
            break;
        default:
            throw new IllegalArgumentException(attribute.toString());
    }
    if (attribute != StatisticsCaseAttribute.AGE_INTERVAL_BASIC) {
        ageIntervalList.add(new IntegerRange(80, null));
    }
    ageIntervalList.add(new IntegerRange(null, null));
    return ageIntervalList;
}

Example 3 with IntegerRange

use of de.symeda.sormas.api.IntegerRange in project SORMAS-Project by hzi-braunschweig.

the class CaseStatisticsFacadeEjb method addAgeGroupToStringBuilder.

private void addAgeGroupToStringBuilder(StringBuilder groupingBuilder, AgeGroup ageGroup) {
    IntegerRange ageRange = ageGroup.toIntegerRange();
    groupingBuilder.append("WHEN ").append(Case.TABLE_NAME).append(".").append(Case.CASE_AGE);
    if (ageRange.getTo() == null) {
        groupingBuilder.append(" >= ").append(ageRange.getFrom());
    } else {
        groupingBuilder.append(" BETWEEN ").append(ageRange.getFrom()).append(" AND ").append(ageRange.getTo());
    }
    groupingBuilder.append(" THEN '").append(ageGroup.name()).append("' ");
}

Example 4 with IntegerRange

use of de.symeda.sormas.api.IntegerRange in project SORMAS-Project by hzi-braunschweig.

the class StatisticsView method fillCaseCriteria.

private void fillCaseCriteria(boolean showCaseIncidence) {
    caseCriteria = new StatisticsCaseCriteria();
    for (StatisticsFilterComponent filterComponent : filterComponents) {
        StatisticsFilterElement filterElement = filterComponent.getFilterElement();
        switch(filterComponent.getSelectedAttribute()) {
            case SEX:
                if (filterElement.getSelectedValues() != null) {
                    List<Sex> sexes = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                        if (tokenizableValue.getValue().equals(I18nProperties.getString(Strings.notSpecified))) {
                            caseCriteria.sexUnknown(true);
                        } else {
                            sexes.add((Sex) tokenizableValue.getValue());
                        }
                    }
                    caseCriteria.sexes(sexes);
                }
                break;
            case DISEASE:
                if (filterElement.getSelectedValues() != null) {
                    List<Disease> diseases = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                        diseases.add((Disease) tokenizableValue.getValue());
                    }
                    caseCriteria.diseases(diseases);
                }
                break;
            case CLASSIFICATION:
                if (filterElement.getSelectedValues() != null) {
                    List<CaseClassification> classifications = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                        classifications.add((CaseClassification) tokenizableValue.getValue());
                    }
                    caseCriteria.classifications(classifications);
                }
                break;
            case OUTCOME:
                if (filterElement.getSelectedValues() != null) {
                    List<CaseOutcome> outcomes = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                        outcomes.add((CaseOutcome) tokenizableValue.getValue());
                    }
                    caseCriteria.outcomes(outcomes);
                }
                break;
            case AGE_INTERVAL_1_YEAR:
            case AGE_INTERVAL_5_YEARS:
            case AGE_INTERVAL_CHILDREN_COARSE:
            case AGE_INTERVAL_CHILDREN_FINE:
            case AGE_INTERVAL_CHILDREN_MEDIUM:
            case AGE_INTERVAL_BASIC:
                if (filterElement.getSelectedValues() != null) {
                    List<IntegerRange> ageIntervals = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                        ageIntervals.add((IntegerRange) tokenizableValue.getValue());
                    }
                    caseCriteria.addAgeIntervals(ageIntervals);
                    // Fill age groups if 5 years interval has been selected and case incidence is shown
                    if (showCaseIncidence && filterComponent.getSelectedAttribute() == StatisticsCaseAttribute.AGE_INTERVAL_5_YEARS) {
                        List<AgeGroup> ageGroups = new ArrayList<>();
                        for (IntegerRange ageInterval : ageIntervals) {
                            if (ageInterval.getFrom() != null || ageInterval.getTo() != null) {
                                ageGroups.add(AgeGroup.getAgeGroupFromIntegerRange(ageInterval));
                            }
                        }
                        caseCriteria.addAgeGroups(ageGroups);
                    }
                }
                break;
            case JURISDICTION:
                StatisticsFilterJurisdictionElement jurisdictionElement = (StatisticsFilterJurisdictionElement) filterElement;
                if (jurisdictionElement.getSelectedRegions() != null) {
                    List<RegionReferenceDto> regions = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : jurisdictionElement.getSelectedRegions()) {
                        regions.add((RegionReferenceDto) tokenizableValue.getValue());
                    }
                    caseCriteria.regions(regions);
                }
                if (jurisdictionElement.getSelectedDistricts() != null) {
                    List<DistrictReferenceDto> districts = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : jurisdictionElement.getSelectedDistricts()) {
                        districts.add((DistrictReferenceDto) tokenizableValue.getValue());
                    }
                    caseCriteria.districts(districts);
                }
                if (jurisdictionElement.getSelectedCommunities() != null) {
                    List<CommunityReferenceDto> communities = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : jurisdictionElement.getSelectedCommunities()) {
                        communities.add((CommunityReferenceDto) tokenizableValue.getValue());
                    }
                    caseCriteria.communities(communities);
                }
                if (jurisdictionElement.getSelectedHealthFacilities() != null) {
                    List<FacilityReferenceDto> facilities = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : jurisdictionElement.getSelectedHealthFacilities()) {
                        facilities.add((FacilityReferenceDto) tokenizableValue.getValue());
                    }
                    caseCriteria.healthFacilities(facilities);
                }
                break;
            case PLACE_OF_RESIDENCE:
                StatisticsFilterResidenceElement residenceElement = (StatisticsFilterResidenceElement) filterElement;
                if (residenceElement.getSelectedRegions() != null) {
                    List<RegionReferenceDto> regions = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : residenceElement.getSelectedRegions()) {
                        regions.add((RegionReferenceDto) tokenizableValue.getValue());
                    }
                    caseCriteria.personRegions(regions);
                }
                if (residenceElement.getSelectedDistricts() != null) {
                    List<DistrictReferenceDto> districts = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : residenceElement.getSelectedDistricts()) {
                        districts.add((DistrictReferenceDto) tokenizableValue.getValue());
                    }
                    caseCriteria.personDistricts(districts);
                }
                if (residenceElement.getSelectedCommunities() != null) {
                    List<CommunityReferenceDto> communities = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : residenceElement.getSelectedCommunities()) {
                        communities.add((CommunityReferenceDto) tokenizableValue.getValue());
                    }
                    caseCriteria.personCommunities(communities);
                }
                if (residenceElement.getCity() != null) {
                    caseCriteria.setPersonCity(residenceElement.getCity());
                }
                if (residenceElement.getPostcode() != null) {
                    caseCriteria.setPersonPostcode(residenceElement.getPostcode());
                }
                break;
            case REPORTING_USER_ROLE:
                if (filterElement.getSelectedValues() != null) {
                    List<UserRole> reportingUserRoles = new ArrayList<>();
                    for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                        reportingUserRoles.add((UserRole) tokenizableValue.getValue());
                    }
                    caseCriteria.reportingUserRoles(reportingUserRoles);
                }
                break;
            default:
                switch(filterComponent.getSelectedSubAttribute()) {
                    case YEAR:
                        if (filterElement.getSelectedValues() != null) {
                            List<Year> years = new ArrayList<>();
                            for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                                years.add((Year) tokenizableValue.getValue());
                            }
                            caseCriteria.years(years, filterComponent.getSelectedAttribute());
                        }
                        break;
                    case QUARTER:
                        if (filterElement.getSelectedValues() != null) {
                            List<Quarter> quarters = new ArrayList<>();
                            for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                                quarters.add((Quarter) tokenizableValue.getValue());
                            }
                            caseCriteria.quarters(quarters, filterComponent.getSelectedAttribute());
                        }
                        break;
                    case MONTH:
                        if (filterElement.getSelectedValues() != null) {
                            List<Month> months = new ArrayList<>();
                            for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                                months.add((Month) tokenizableValue.getValue());
                            }
                            caseCriteria.months(months, filterComponent.getSelectedAttribute());
                        }
                        break;
                    case EPI_WEEK:
                        if (filterElement.getSelectedValues() != null) {
                            List<EpiWeek> epiWeeks = new ArrayList<>();
                            for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                                epiWeeks.add((EpiWeek) tokenizableValue.getValue());
                            }
                            caseCriteria.epiWeeks(epiWeeks, filterComponent.getSelectedAttribute());
                        }
                        break;
                    case QUARTER_OF_YEAR:
                        if (filterElement.getSelectedValues() != null) {
                            List<QuarterOfYear> quartersOfYear = new ArrayList<>();
                            for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                                quartersOfYear.add((QuarterOfYear) tokenizableValue.getValue());
                            }
                            caseCriteria.quartersOfYear(quartersOfYear, filterComponent.getSelectedAttribute());
                        }
                        break;
                    case MONTH_OF_YEAR:
                        if (filterElement.getSelectedValues() != null) {
                            List<MonthOfYear> monthsOfYear = new ArrayList<>();
                            for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                                monthsOfYear.add((MonthOfYear) tokenizableValue.getValue());
                            }
                            caseCriteria.monthsOfYear(monthsOfYear, filterComponent.getSelectedAttribute());
                        }
                        break;
                    case EPI_WEEK_OF_YEAR:
                        if (filterElement.getSelectedValues() != null) {
                            List<EpiWeek> epiWeeksOfYear = new ArrayList<>();
                            for (TokenizableValue tokenizableValue : filterElement.getSelectedValues()) {
                                epiWeeksOfYear.add((EpiWeek) tokenizableValue.getValue());
                            }
                            caseCriteria.epiWeeksOfYear(epiWeeksOfYear, filterComponent.getSelectedAttribute());
                        }
                        break;
                    case DATE_RANGE:
                        caseCriteria.dateRange((Date) filterElement.getSelectedValues().get(0).getValue(), (Date) filterElement.getSelectedValues().get(1).getValue(), filterComponent.getSelectedAttribute());
                        break;
                    default:
                        throw new IllegalArgumentException(filterComponent.getSelectedSubAttribute().toString());
                }
        }
    }
}

Example 5 with IntegerRange

use of de.symeda.sormas.api.IntegerRange in project SORMAS-Project by hzi-braunschweig.

the class CaseStatisticsFacadeEjbTest method testQueryCaseCountZeroValues.

@Test
public void testQueryCaseCountZeroValues() {
    RDCF rdcf = creator.createRDCF("Region", "District", "Community", "Facility");
    UserDto user = creator.createUser(rdcf.region.getUuid(), rdcf.district.getUuid(), rdcf.facility.getUuid(), "Surv", "Sup", UserRole.SURVEILLANCE_SUPERVISOR);
    PersonDto cazePerson = creator.createPerson("Case", "Person");
    cazePerson.setApproximateAge(30);
    cazePerson.setApproximateAgeReferenceDate(new Date());
    cazePerson.setApproximateAgeType(ApproximateAgeType.YEARS);
    cazePerson = getPersonFacade().savePerson(cazePerson);
    CaseDataDto caze = creator.createCase(user.toReference(), cazePerson.toReference(), Disease.EVD, CaseClassification.PROBABLE, InvestigationStatus.PENDING, new Date(), rdcf);
    caze = getCaseFacade().getCaseDataByUuid(caze.getUuid());
    StatisticsCaseCriteria criteria = new StatisticsCaseCriteria();
    int year = DateHelper8.toLocalDate(caze.getSymptoms().getOnsetDate()).getYear();
    criteria.years(Arrays.asList(new Year(year), new Year(year + 1)), StatisticsCaseAttribute.ONSET_TIME);
    criteria.regions(Arrays.asList(new RegionReferenceDto(rdcf.region.getUuid(), null, null)));
    criteria.addAgeIntervals(Arrays.asList(new IntegerRange(10, 40)));
    List<StatisticsCaseCountDto> results = getCaseStatisticsFacade().queryCaseCount(criteria, StatisticsCaseAttribute.SEX, null, null, null, false, true, null);
    // List should have one entry per sex
    assertEquals(Sex.values().length, results.size());
}