@Override
  public void createAndPopulateDataApprovalMinLevel(Set<OrganisationUnitLevel> levels) {
    try {
      jdbcTemplate.execute("drop table if exists " + TABLE_NAME_DATA_APPROVAL_MIN_LEVEL);
    } catch (BadSqlGrammarException ex) {
      // Do nothing, table does not exist
    }

    final String create =
        "create table "
            + TABLE_NAME_DATA_APPROVAL_MIN_LEVEL
            + "("
            + "datasetid integer not null, "
            + "periodid integer not null, "
            + "organisationunitid integer not null, "
            + "attributeoptioncomboid integer not null, "
            + "minlevel integer not null);";

    log.info("Create data approval min level SQL: " + create);

    jdbcTemplate.execute(create);

    String sql =
        "insert into "
            + TABLE_NAME_DATA_APPROVAL_MIN_LEVEL
            + " (datasetid,periodid,organisationunitid,attributeoptioncomboid,minlevel) "
            + "select da.datasetid, da.periodid, da.organisationunitid, da.attributeoptioncomboid, dal.level as minlevel "
            + "from dataapproval da "
            + "inner join dataapprovallevel dal on da.dataapprovallevelid=dal.dataapprovallevelid "
            + "where not exists ( "
            + "select 1 from dataapproval da2 "
            + "inner join dataapprovallevel dal2 on da2.dataapprovallevelid=dal2.dataapprovallevelid "
            + "inner join _orgunitstructure ous2 on da2.organisationunitid=ous2.organisationunitid "
            + "where da.datasetid=da2.datasetid and da.periodid=da2.periodid and da.attributeoptioncomboid=da2.attributeoptioncomboid "
            + "and dal2.level < dal.level "
            + "and ( ";

    for (OrganisationUnitLevel level : levels) {
      sql += "da.organisationunitid = ous2.idlevel" + level.getLevel() + " or ";
    }

    sql = TextUtils.removeLastOr(sql) + ") )";

    log.info("Insert data approval min level SQL: " + sql);

    jdbcTemplate.execute(sql);

    final String index =
        "create index in_dataapprovalminlevel_datasetid on _dataapprovalminlevel(datasetid);"
            + "create index in_dataapprovalminlevel_periodid on _dataapprovalminlevel(periodid);"
            + "create index in_dataapprovalminlevel_organisationunitid on _dataapprovalminlevel(organisationunitid);"
            + "create index in_dataapprovalminlevel_attributeoptioncomboid on _dataapprovalminlevel(attributeoptioncomboid);";

    log.info("Create data approval min level index SQL: " + index);

    jdbcTemplate.execute(index);
  }
Exemple #2
0
  /**
   * Returns a mapping based on the given grid where the key is a joined string of the string value
   * of each value for meta columns. The value is the object at the given value index. The map
   * contains at maximum one entry per row in the given grid, less if the joined key string are
   * duplicates. The object at the value index must be numeric.
   *
   * @param grid the grid.
   * @param valueIndex the index of the column holding the value, must be numeric.
   * @return a meta string to value object mapping.
   */
  public static Map<String, Object> getMetaValueMapping(Grid grid, int valueIndex) {
    Map<String, Object> map = new HashMap<>();

    List<Integer> metaIndexes = grid.getMetaColumnIndexes();

    for (List<Object> row : grid.getRows()) {
      List<Object> metaDataRowItems = ListUtils.getAtIndexes(row, metaIndexes);

      String key =
          TextUtils.join(metaDataRowItems, DIMENSION_SEP, NameableObjectUtils.NULL_REPLACEMENT);

      map.put(key, row.get(valueIndex));
    }

    return map;
  }
  @Override
  public void populateCategoryStructure(List<DataElementCategory> categories) {
    String sql =
        "insert into "
            + CreateCategoryTableStatement.TABLE_NAME
            + " "
            + "select coc.categoryoptioncomboid as cocid, con.categoryoptioncomboname as cocname, ";

    for (DataElementCategory category : categories) {
      sql +=
          "("
              + "select co.name from categoryoptioncombos_categoryoptions cocco "
              + "inner join dataelementcategoryoption co on cocco.categoryoptionid = co.categoryoptionid "
              + "inner join categories_categoryoptions cco on co.categoryoptionid = cco.categoryoptionid "
              + "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid "
              + "and cco.categoryid = "
              + category.getId()
              + " "
              + "limit 1) as "
              + statementBuilder.columnQuote(category.getName())
              + ", ";

      sql +=
          "("
              + "select co.uid from categoryoptioncombos_categoryoptions cocco "
              + "inner join dataelementcategoryoption co on cocco.categoryoptionid = co.categoryoptionid "
              + "inner join categories_categoryoptions cco on co.categoryoptionid = cco.categoryoptionid "
              + "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid "
              + "and cco.categoryid = "
              + category.getId()
              + " "
              + "limit 1) as "
              + statementBuilder.columnQuote(category.getUid())
              + ", ";
    }

    sql = TextUtils.removeLastComma(sql) + " ";
    sql +=
        "from categoryoptioncombo coc "
            + "inner join _categoryoptioncomboname con on coc.categoryoptioncomboid = con.categoryoptioncomboid";

    log.info("Populate category structure SQL: " + sql);

    jdbcTemplate.execute(sql);
  }
  @Override
  public void populateOrganisationUnitGroupSetStructure(List<OrganisationUnitGroupSet> groupSets) {
    String sql =
        "insert into "
            + CreateOrganisationUnitGroupSetTableStatement.TABLE_NAME
            + " "
            + "select ou.organisationunitid as organisationunitid, ou.name as organisationunitname, ";

    for (OrganisationUnitGroupSet groupSet : groupSets) {
      sql +=
          "("
              + "select oug.name from orgunitgroup oug "
              + "inner join orgunitgroupmembers ougm on ougm.orgunitgroupid = oug.orgunitgroupid "
              + "inner join orgunitgroupsetmembers ougsm on ougsm.orgunitgroupid = ougm.orgunitgroupid and ougsm.orgunitgroupsetid = "
              + groupSet.getId()
              + " "
              + "where ougm.organisationunitid = ou.organisationunitid "
              + "limit 1) as "
              + statementBuilder.columnQuote(groupSet.getName())
              + ", ";

      sql +=
          "("
              + "select oug.uid from orgunitgroup oug "
              + "inner join orgunitgroupmembers ougm on ougm.orgunitgroupid = oug.orgunitgroupid "
              + "inner join orgunitgroupsetmembers ougsm on ougsm.orgunitgroupid = ougm.orgunitgroupid and ougsm.orgunitgroupsetid = "
              + groupSet.getId()
              + " "
              + "where ougm.organisationunitid = ou.organisationunitid "
              + "limit 1) as "
              + statementBuilder.columnQuote(groupSet.getUid())
              + ", ";
    }

    sql = TextUtils.removeLastComma(sql) + " ";
    sql += "from organisationunit ou";

    log.info("Populate organisation unit group set structure SQL: " + sql);

    jdbcTemplate.execute(sql);
  }
  @Override
  public void populateIndicatorGroupSetStructure(List<IndicatorGroupSet> groupSets) {
    String sql =
        "insert into "
            + CreateIndicatorGroupSetTableStatement.TABLE_NAME
            + " "
            + "select i.indicatorid as indicatorid, i.name as indicatorname, ";

    for (IndicatorGroupSet groupSet : groupSets) {
      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 "
              + statementBuilder.columnQuote(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 "
              + statementBuilder.columnQuote(groupSet.getUid())
              + ", ";
    }

    sql = TextUtils.removeLastComma(sql) + " ";
    sql += "from indicator i";

    log.info("Populate indicator group set structure SQL: " + sql);

    jdbcTemplate.execute(sql);
  }
  @Override
  public void populateDataElementGroupSetStructure(List<DataElementGroupSet> groupSets) {
    String sql =
        "insert into "
            + CreateDataElementGroupSetTableStatement.TABLE_NAME
            + " "
            + "select d.dataelementid as dataelementid, d.name as dataelementname, ";

    for (DataElementGroupSet groupSet : groupSets) {
      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 "
              + statementBuilder.columnQuote(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 "
              + statementBuilder.columnQuote(groupSet.getUid())
              + ", ";
    }

    sql = TextUtils.removeLastComma(sql) + " ";
    sql += "from dataelement d";

    log.info("Populate data element group set structure SQL: " + sql);

    jdbcTemplate.execute(sql);
  }