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(); }
@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 ); }
sql = TextUtils.removeLastOr( sql ) + ") ";
/** * Returns sub-query for approval level. First looks for approval level in * data element resource table which will indicate level 0 (highest) if approval * is not required. Then looks for highest level in dataapproval table. * * @param year the data year. */ private String getApprovalJoinClause( Integer year ) { if ( isApprovalEnabled( year ) ) { String sql = "left join _dataapprovalminlevel da " + "on des.workflowid=da.workflowid and da.periodid=dv.periodid and da.attributeoptioncomboid=dv.attributeoptioncomboid " + "and ("; Set<OrganisationUnitLevel> levels = dataApprovalLevelService.getOrganisationUnitApprovalLevels(); for ( OrganisationUnitLevel level : levels ) { sql += "ous.idlevel" + level.getLevel() + " = da.organisationunitid or "; } return TextUtils.removeLastOr( sql ) + ") "; } return StringUtils.EMPTY; }
private static String createOrgUnitClause( ExportParams params, ImmutableMap.Builder<String, String> namedParamsBuilder ) { if ( params.isIncludeChildren() ) { String clause = " AND ( "; clause += params.getOrganisationUnits().stream() .map( o -> " ou.path LIKE '" + o.getPath() + "%' OR " ) .collect( Collectors.joining() ); return TextUtils.removeLastOr( clause ) + " ) "; } else { String clause = " AND ( "; if ( params.hasOrganisationUnits() ) { namedParamsBuilder.put( "orgUnits", commaDelimitedIds( params.getOrganisationUnits() ) ); clause += " cdsr.sourceid IN ( ${orgUnits} ) "; if ( params.hasOrganisationUnitGroups() ) { clause += " OR "; } } if ( params.hasOrganisationUnitGroups() ) { namedParamsBuilder.put( "orgUnitGroups", commaDelimitedIds( params.getOrganisationUnitGroups() ) ); clause += " ougm.orgunitgroupid in ( ${orgUnitGroups} )"; } return clause + " ) "; } }
sql = TextUtils.removeLastOr( sql ) + ") "; sql += "and dv.followup = true and dv.deleted is false ";
sql = TextUtils.removeLastOr( sql ) + ") "; sql += "and dv.deleted is false ";
sql = TextUtils.removeLastOr( sql ) + " ) "; sql += "and dv.deleted is false ";
sql = TextUtils.removeLastOr( sql ) + ") and ("; sql = TextUtils.removeLastOr( sql ) + ") "; sql += "and dv.deleted is false " +
matchPaths = TextUtils.removeLastOr( matchPaths ) + ") ";
sql = TextUtils.removeLastOr( sql ) + ") ";
hql = removeLastOr( hql );
hql = TextUtils.removeLastOr( hql ) + ")";
hql = TextUtils.removeLastOr( hql ) + ") ";
sql = removeLastOr( sql ) + ") "; sql = removeLastOr( sql ) + ") ";
sql = TextUtils.removeLastOr( sql ) + ") ";
hql = TextUtils.removeLastOr( hql ) + ") ";
sql = removeLastOr( sql ) + ") ";
sql = removeLastOr( sql ) + ") ";
sql = removeLastOr( sql ) + ") and ";