@Override
  public Optional<String> getPopulateTempTableStatement() {
    String sql =
        "insert into "
            + getTempTableName()
            + " "
            + "select coc.categoryoptioncomboid as cocid, coc.name as cocname, ";

    for (DataElementCategory category : objects) {
      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 "
              + columnQuote
              + category.getName()
              + columnQuote
              + ", ";

      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 "
              + columnQuote
              + category.getUid()
              + columnQuote
              + ", ";
    }

    sql = TextUtils.removeLastComma(sql) + " ";
    sql += "from categoryoptioncombo coc ";

    return Optional.of(sql);
  }
Exemplo n.º 2
0
  @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 String getCreateTempTableStatement() {
    String statement =
        "create table "
            + getTempTableName()
            + " ("
            + "categoryoptioncomboid integer not null, "
            + "categoryoptioncomboname varchar(230), ";

    for (DataElementCategory category : objects) {
      statement += columnQuote + category.getName() + columnQuote + " varchar(230), ";
      statement += columnQuote + category.getUid() + columnQuote + " character(11), ";
    }

    statement += "primary key (categoryoptioncomboid))";

    return statement;
  }