String dataElementIds = getCommaDelimitedString( getIdentifiers( dataElements ) ); String periodIds = getCommaDelimitedString( getIdentifiers( periods ) ); String categoryOptionComboIds = getCommaDelimitedString( getIdentifiers( categoryOptionCombos ) ); sql = TextUtils.removeLastOr( sql ) + ") "; sql += "and dv.followup = true and dv.deleted is false ";
/** * Drops and creates the table partitions for the given analytics table. * * @param table the {@link AnalyticsTable}. */ protected void createTempTablePartitions( AnalyticsTable table ) { for ( AnalyticsTablePartition partition : table.getPartitionTables() ) { final String tableName = partition.getTempTableName(); final List<String> checks = getPartitionChecks( partition ); String sqlCreate = "create table " + tableName + " "; if ( !checks.isEmpty() ) { StringBuilder sqlCheck = new StringBuilder( "(" ); checks.stream().forEach( check -> sqlCheck.append( "check (" + check + "), " ) ); sqlCreate += TextUtils.removeLastComma( sqlCheck.toString() ) + ") "; } sqlCreate += "inherits (" + table.getTempTableName() + ") " + getTableOptions(); log.info( String.format( "Creating partition table: %s", tableName ) ); log.debug( "Create SQL: " + sqlCreate ); jdbcTemplate.execute( sqlCreate ); } }
@Override @Async public Future<?> applyAggregationLevels( ConcurrentLinkedQueue<AnalyticsTablePartition> partitions, Collection<String> dataElements, int aggregationLevel ) { taskLoop: while ( true ) { AnalyticsTablePartition partition = partitions.poll(); if ( partition == null ) { break taskLoop; } StringBuilder sql = new StringBuilder( "update " + partition.getTempTableName() + " set " ); for ( int i = 0; i < aggregationLevel; i++ ) { int level = i + 1; String column = quote( DataQueryParams.LEVEL_PREFIX + level ); sql.append( column + " = null," ); } sql.deleteCharAt( sql.length() - ",".length() ); sql.append( " where level > " + aggregationLevel ); sql.append( " and dx in (" + getQuotedCommaDelimitedString( dataElements ) + ")" ); log.debug( "Aggregation level SQL: " + sql.toString() ); jdbcTemplate.execute( sql.toString() ); } return ConcurrentUtils.getImmediateFuture(); }
sql += sqlHelper.whereAnd() + " " + col + " in (" + getQuotedCommaDelimitedString( getUids( dim.getItems() ) ) + ") "; sql += col + " in (" + getQuotedCommaDelimitedString( getUids( filter.getItems() ) ) + ") or "; sql = removeLastOr( sql ) + ") "; sql = removeLastOr( sql ) + ") "; TextUtils.getCommaDelimitedString( params.getPartitions().getPartitions() ) + ") ";
private static String commaDelimitedIds( Collection<? extends IdentifiableObject> idObjects ) { return TextUtils.getCommaDelimitedString( IdentifiableObjectUtils.getIdentifiers( idObjects ) ); }
sql = TextUtils.removeLastOr( sql ) + ") "; sql += sqlHelper.whereAnd() + " " + col + " in (" + getQuotedCommaDelimitedString( getUids( dim.getItems() ) ) + ") ";
+ getCommaDelimitedString( getIdentifiers( params.getOrganisationUnits() ) ) + ") "; List<String> queryTokens = getTokens( params.getQuery().getFilter() ); sql = removeLastOr( sql ) + ") and "; sql = removeLastAnd( sql ) + ") ";
public String getAnyValueExistsClauseAnalyticsSql( String expression, AnalyticsType analyticsType ) { Set<String> uids = ProgramIndicator.getDataElementAndAttributeIdentifiers( expression, analyticsType ); if ( uids.isEmpty() ) { return null; } String sql = StringUtils.EMPTY; for ( String uid : uids ) { sql += statementBuilder.columnQuote( uid ) + " is not null or "; } return TextUtils.removeLastOr( sql ).trim(); }
sql += hlp.whereAnd() + " psi.organisationunitid in (" + getCommaDelimitedString( orgUnitIds ) + ") "; sql += hlp.whereAnd() + " (psi.uid in (" + getQuotedCommaDelimitedString( params.getEvents() ) + ")) "; sql += hlp.whereAnd() + " (p.uid in (" + getQuotedCommaDelimitedString( params.getAccessiblePrograms() ) + ")) "; sql += hlp.whereAnd() + " (ps.uid in (" + getQuotedCommaDelimitedString( params.getAccessibleProgramStages() ) + ")) ";
final String valTypes = TextUtils.getQuotedCommaDelimitedString( ObjectUtils.asStringList( valueTypes ) ); final boolean respectStartEndDates = (Boolean) systemSettingManager.getSystemSetting( SettingKey.RESPECT_META_DATA_START_END_DATES_IN_ANALYTICS_TABLE_EXPORT ); sql = TextUtils.removeLastComma( sql ) + ") select ";
/** * Replaces the first n matches of the given regular expression starting * from the beginning of the given string. * * @param string the string to replace matches. * @param regex the regular expression to match the string against. * @param replacement the replacement string. * @param occurrences the number of matches to replace. * @return the replaced string. */ public static String replaceFirst( String string, final String regex, final String replacement, final int occurrences ) { StringBuffer sb = new StringBuffer(); Matcher matcher = Pattern.compile( regex ).matcher( string ); int c = 0; while ( matcher.find() && c < occurrences ) { matcher.appendReplacement( sb, replacement ); c++; } return appendTail( matcher, sb ); }
@Override public String evaluate( String... args ) { if ( args == null || args.length == 0 ) { throw new IllegalArgumentException( "Illegal arguments, expected at least one argument" ); } String sql = "nullif(cast(("; for ( String value : args ) { sql += "case when " + value + " >= 0 then 1 else 0 end + "; } return TextUtils.removeLast( sql, "+" ).trim() + ") as double precision),0)"; }
@Override public boolean isInUserHierarchyCached( OrganisationUnit organisationUnit ) { String cacheKey = joinHyphen( currentUserService.getCurrentUsername(), organisationUnit.getUid() ); return IN_USER_ORG_UNIT_HIERARCHY_CACHE.get( cacheKey, ou -> isInUserHierarchy( organisationUnit ) ); }
/** * Gets part of an object identifier which may be composite. * * @param id The identifier to parse. * @param index Index of the part to return. * @return The identifier part. */ private String getIdPart( String id, int index ) { return splitSafe( id, COMPOSITE_DIM_OBJECT_ESCAPED_SEP, index ); }
String className = TextUtils.getPrettyClassName( object.getClass() );
/** * Transforms a collection of Integers into a comma delimited String. If the * given collection of elements are null or is empty, an empty String is * returned. * * @param delimitPrefix whether to prefix the string with a delimiter. * @param delimitSuffix whether to suffix the string with a delimiter. * @param elements the collection of Integers * @return a comma delimited String. */ public static String getCommaDelimitedString( Collection<?> elements, boolean delimitPrefix, boolean delimitSuffix ) { final StringBuilder builder = new StringBuilder(); if ( elements != null && !elements.isEmpty() ) { if ( delimitPrefix ) { builder.append( DELIMITER ); } builder.append( getCommaDelimitedString( elements ) ); if ( delimitSuffix ) { builder.append( DELIMITER ); } } return builder.toString(); }
hql += hlp.whereAnd() + " a.workflow.uid in (" + getQuotedCommaDelimitedString( getUids( params.getWorkflows() ) ) + ") "; hql += hlp.whereAnd() + " a.level.uid in (" + getQuotedCommaDelimitedString( getUids( params.getLevels() ) ) + ") "; hql += hlp.whereAnd() + " a.organisationUnit.uid in (" + getQuotedCommaDelimitedString( getUids( params.getOrganisationUnits() ) ) + ") "; hql += hlp.whereAnd() + " a.attributeOptionCombo.uid in (" + getQuotedCommaDelimitedString( getUids( params.getAttributeOptionCombos() ) ) + ") "; hql = TextUtils.removeLastOr( hql ) + ") ";
@Override public Optional<String> getPopulateTempTableStatement() { String sql = "insert into " + getTempTableName() + " (workflowid,periodid,organisationunitid,attributeoptioncomboid,minlevel) " + "select da.workflowid, da.periodid, da.organisationunitid, da.attributeoptioncomboid, dal.level as minlevel " + "from dataapproval da " + "inner join _dataapprovalremaplevel dal on dal.workflowid=da.workflowid and dal.dataapprovallevelid=da.dataapprovallevelid " + "inner join _orgunitstructure ous on da.organisationunitid=ous.organisationunitid " + "where not exists ( " + "select 1 from dataapproval da2 " + "inner join _dataapprovalremaplevel dal2 on da2.workflowid = dal2.workflowid and da2.dataapprovallevelid=dal2.dataapprovallevelid " + "where da.workflowid=da2.workflowid " + "and da.periodid=da2.periodid " + "and da.attributeoptioncomboid=da2.attributeoptioncomboid " + "and dal.level > dal2.level " + "and ( "; for ( OrganisationUnitLevel level : objects ) { sql += "ous.idlevel" + level.getLevel() + " = da2.organisationunitid or "; } sql = TextUtils.removeLastOr( sql ) + ") )"; return Optional.of( sql ); }
+ getCommaDelimitedString( getIdentifiers( organisationUnits ) ) + ") "; sql += hlp.whereAnd() + " (psi.uid in (" + getQuotedCommaDelimitedString( params.getEvents() ) + ")) ";
private String getSubstitutedVariablesForAnalyticsSql( String expression, ProgramIndicator programIndicator, Date startDate, Date endDate ) { if ( expression == null ) { return null; } StringBuffer buffer = new StringBuffer(); Matcher matcher = ProgramIndicator.VARIABLE_PATTERN.matcher( expression ); while ( matcher.find() ) { String var = matcher.group( 1 ); String sql = getVariableAsSql( var, expression, programIndicator.getAnalyticsType(), startDate, endDate ); if ( sql != null ) { matcher.appendReplacement( buffer, sql ); } } return TextUtils.appendTail( matcher, buffer ); }