@Override public void processRow( ResultSet rs ) throws SQLException { String organisationUnitId = rs.getString( "ou_uid" ); Set<String> dataSetIds = SqlUtils.getArrayAsSet( rs, "ds_uid" ); map.put( organisationUnitId, dataSetIds ); } } );
@Override public String getCreateTempTableStatement() { StringBuilder sql = new StringBuilder(); sql.append( "create table " ).append( getTempTableName() ). append( " (organisationunitid integer not null primary key, organisationunituid character(11), level integer" ); for ( int k = 1 ; k <= organisationUnitLevels; k++ ) { sql.append( ", " ).append( quote( "idlevel" + k ) ).append (" integer, " ) .append( quote( "uidlevel" + k ) ).append( " character(11), " ) .append( quote( "namelevel" + k ) ).append( " text" ); } return sql.append( ");" ).toString(); }
@Override public String getCreateTempTableStatement() { String statement = "create table " + getTempTableName() + " (" + "dataelementid integer not null, " + "dataelementname varchar(230), "; for ( DataElementGroupSet groupSet : objects ) { statement += quote( groupSet.getName() ) + " varchar(230), "; statement += quote( groupSet.getUid() ) + " character(11), "; } statement += "primary key (dataelementid))"; return statement; }
@Override public String getCreateTempTableStatement() { String sql = "create table " + getTempTableName() + " (dateperiod date not null primary key, year integer not null"; for ( PeriodType periodType : PeriodType.PERIOD_TYPES ) { sql += ", " + quote( periodType.getName().toLowerCase() ) + " varchar(15)"; } sql += ")"; return sql; }
@Override public String getCreateTempTableStatement() { String statement = "create table " + getTempTableName() + " (" + "indicatorid integer not null, " + "indicatorname varchar(230), "; for ( IndicatorGroupSet groupSet : objects ) { statement += quote( groupSet.getName() ) + " varchar(230), "; statement += quote( groupSet.getUid() ) + " character(11), "; } statement += "primary key (indicatorid))"; return statement; }
@Override public String getCreateTempTableStatement() { String statement = "create table " + getTempTableName() + " (" + "categoryoptioncomboid integer not null, " + "categoryoptioncomboname varchar(255), "; for ( Category category : objects ) { quote( category.getName() ); statement += quote( category.getName() ) + " varchar(230), "; statement += quote( category.getUid() ) + " character(11), "; } for ( CategoryOptionGroupSet groupSet : groupSets ) { statement += quote( groupSet.getName() ) + " varchar(230), "; statement += quote( groupSet.getUid() ) + " character(11), "; } statement += "primary key (categoryoptioncomboid))"; return statement; }
@Override public String getCreateTempTableStatement() { String sql = "create table " + getTempTableName() + " (periodid integer not null primary key, iso varchar(15) not null, daysno integer not null, startdate date not null, enddate date not null, year integer not null"; for ( PeriodType periodType : PeriodType.PERIOD_TYPES ) { sql += ", " + quote( periodType.getName().toLowerCase() ) + " varchar(15)"; } sql += ")"; return sql; }
@Override public String getCreateTempTableStatement() { String statement = "create table " + getTempTableName() + " (" + "organisationunitid integer not null, " + "organisationunitname varchar(230), " + "startdate date, "; for ( OrganisationUnitGroupSet groupSet : objects ) { statement += quote( groupSet.getName() ) + " varchar(230), "; statement += quote( groupSet.getUid() ) + " character(11), "; } return removeLastComma( statement ) + ")"; }
@Override public List<String> getCreateIndexStatements() { List<String> indexes = new ArrayList<>(); for ( PeriodType periodType : PeriodType.PERIOD_TYPES ) { String colName = periodType.getName().toLowerCase(); String indexName = "in" + getTableName() + "_" + colName + "_" + getRandomSuffix(); String sql = "create index " + indexName + " on " + getTempTableName() + "(" + quote( colName ) + ")"; indexes.add( sql ); } return indexes; } }
@Override public Optional<String> getPopulateTempTableStatement() { String sql = "insert into " + getTempTableName() + " " + "select d.dataelementid as dataelementid, d.name as dataelementname, "; for ( DataElementGroupSet groupSet : objects ) { sql += "(" + "select deg.name from dataelementgroup deg " + "inner join dataelementgroupmembers degm on degm.dataelementgroupid = deg.dataelementgroupid " + "inner join dataelementgroupsetmembers degsm on degsm.dataelementgroupid = degm.dataelementgroupid and degsm.dataelementgroupsetid = " + groupSet.getId() + " " + "where degm.dataelementid = d.dataelementid " + "limit 1) as " + quote( groupSet.getName() ) + ", "; sql += "(" + "select deg.uid from dataelementgroup deg " + "inner join dataelementgroupmembers degm on degm.dataelementgroupid = deg.dataelementgroupid " + "inner join dataelementgroupsetmembers degsm on degsm.dataelementgroupid = degm.dataelementgroupid and degsm.dataelementgroupsetid = " + groupSet.getId() + " " + "where degm.dataelementid = d.dataelementid " + "limit 1) as " + quote( groupSet.getUid() ) + ", "; } sql = TextUtils.removeLastComma( sql ) + " "; sql += "from dataelement d"; return Optional.of( sql ); }
@Override public Optional<String> getPopulateTempTableStatement() { String sql = "insert into " + getTempTableName() + " " + "select i.indicatorid as indicatorid, i.name as indicatorname, "; for ( IndicatorGroupSet groupSet : objects ) { sql += "(" + "select ig.name from indicatorgroup ig " + "inner join indicatorgroupmembers igm on igm.indicatorgroupid = ig.indicatorgroupid " + "inner join indicatorgroupsetmembers igsm on igsm.indicatorgroupid = igm.indicatorgroupid and igsm.indicatorgroupsetid = " + groupSet.getId() + " " + "where igm.indicatorid = i.indicatorid " + "limit 1) as " + quote( groupSet.getName() ) + ", "; sql += "(" + "select ig.uid from indicatorgroup ig " + "inner join indicatorgroupmembers igm on igm.indicatorgroupid = ig.indicatorgroupid " + "inner join indicatorgroupsetmembers igsm on igsm.indicatorgroupid = igm.indicatorgroupid and igsm.indicatorgroupsetid = " + groupSet.getId() + " " + "where igm.indicatorid = i.indicatorid " + "limit 1) as " + quote( groupSet.getUid() ) + ", "; } sql = TextUtils.removeLastComma( sql ) + " "; sql += "from indicator i"; return Optional.of( sql ); }
"where coc.categoryoptioncomboid = cocco.categoryoptioncomboid " + "and cco.categoryid = " + category.getId() + " " + "limit 1) as " + quote( category.getName() ) + ", "; "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid " + "and cco.categoryid = " + category.getId() + " " + "limit 1) as " + quote( category.getUid() ) + ", "; "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid " + "and cogsm.categoryoptiongroupsetid = " + groupSet.getId() + " " + "limit 1) as " + quote( groupSet.getName() ) + ", "; "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid " + "and cogsm.categoryoptiongroupsetid = " + groupSet.getId() + " " + "limit 1) as " + quote( groupSet.getUid() ) + ", ";
"inner join orgunitgroupsetmembers ougsm on ougsm.orgunitgroupid = ougm.orgunitgroupid and ougsm.orgunitgroupsetid = " + groupSet.getId() + " " + "where ougm.organisationunitid = ou.organisationunitid " + "limit 1) as " + quote( groupSet.getName() ) + ", "; "inner join orgunitgroupsetmembers ougsm on ougsm.orgunitgroupid = ougm.orgunitgroupid and ougsm.orgunitgroupsetid = " + groupSet.getId() + " " + "where ougm.organisationunitid = ou.organisationunitid " + "limit 1) as " + quote( groupSet.getUid() ) + ", "; ") as " + quote( groupSet.getName() ) + ", "; ") as " + quote( groupSet.getUid() ) + ", ";