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