@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);
}
@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;
}