List of usage examples for org.apache.commons.lang3 StringUtils replace
public static String replace(final String text, final String searchString, final String replacement)
Replaces all occurrences of a String within another String.
A null reference passed to this method is a no-op.
StringUtils.replace(null, *, *) = null StringUtils.replace("", *, *) = "" StringUtils.replace("any", null, *) = "any" StringUtils.replace("any", *, null) = "any" StringUtils.replace("any", "", *) = "any" StringUtils.replace("aba", "a", null) = "aba" StringUtils.replace("aba", "a", "") = "b" StringUtils.replace("aba", "a", "z") = "zbz" From source file:org.apache.drill.common.util.DrillStringUtils.java
/** * Copied form commons-lang 2.x code as common-lang 3.x has this API removed. * (http://commons.apache.org/proper/commons-lang/article3_0.html#StringEscapeUtils.escapeSql) * @param str/*from www .j av a 2s . c o m*/ * @return */ public static String escapeSql(String str) { return (str == null) ? null : StringUtils.replace(str, "'", "''"); }
From source file:org.apache.fineract.infrastructure.dataexport.service.DataExportWritePlatformServiceImpl.java
/** * Adds a join statements, select statements or where clauses * This method prevents the addition of duplicate join statements * //from w w w .java 2 s. co m * @param dataExportEntityData * @param aliasPostfixNumber * @param sqlBuilder * @param sqlJoinMap * @param isSelectStatement * @param filterValue */ private void addCoreColumnSqlToSqlBuilder(final DataExportEntityData dataExportEntityData, final EntityColumnMetaData columnMetaData, final SQL sqlBuilder, final HashMap<String, DataExportSqlJoin> sqlJoinMap, final boolean isSelectStatement, final String filterValue, final MutableInt aliasPostfixNumber) { final String columnName = columnMetaData.getName(); final String columnLabel = columnMetaData.getLabel(); final DataExportCoreColumn coreColumn = DataExportCoreColumn.newInstanceFromName(columnName); final String baseEntityName = dataExportEntityData.getEntityName(); final DataExportBaseEntity baseEntity = DataExportBaseEntity.fromEntityName(baseEntityName); final DataExportCoreTable baseEntityCoreTable = DataExportCoreTable.newInstance(baseEntity.getTableName()); // variables initialized with null values String sqlStatement, mClientTableAlias, mGroupTableAlias, mGroupClientTableAlias, mOfficeTableAlias, mStaffTableAlias, mLoanTableAlias, mProductLoanTableAlias, mPaymentDetailTableAlias, mSavingsProductTableAlias, sqlJoinKey, parentTableAlias, mSavingsAccountTableAlias, mAppUserTableAlias; DataExportSqlJoin dataExportSqlJoin; if (coreColumn != null) { String referencedTableName = coreColumn.getReferencedTableName(); DataExportCoreTable referencedTable = DataExportCoreTable.newInstance(referencedTableName); String referencedColumnName = coreColumn.getReferencedColumnName(); String foreignKeyIndexColumnName = coreColumn.getForeignKeyIndexColumnName(); switch (baseEntity) { case CLIENT: switch (coreColumn) { case GROUP_NAME: case GROUP_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupClientTableAlias = DataExportCoreTable.M_GROUP_CLIENT .getAlias(aliasPostfixNumber.intValue()); // m_client and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_GROUP_CLIENT.getName() + "` `" + mGroupClientTableAlias + "` on `" + mGroupClientTableAlias + "`.`client_id` = `" + baseEntityName + "`.`id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, baseEntityName, mGroupClientTableAlias); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_group_client and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + dataExportSqlJoin.getChildTableAlias() + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, mGroupTableAlias, dataExportSqlJoin.getChildTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case BRANCH_NAME: case STAFF_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_CLIENT); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); // m_office/m_staff and m_client table join sqlStatement = "`" + referencedTable.getName() + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`id` = `" + baseEntityName + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_CLIENT, sqlStatement, parentTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case CLIENT_ID: case CLIENT_NAME: // ============================================================================= if (isSelectStatement) { sqlStatement = "`" + baseEntityName + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + baseEntityName + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case PHONE_NUMBER: case DATE_OF_BIRTH: case GENDER: // ============================================================================= if (isSelectStatement) { sqlStatement = "`" + baseEntityName + "`.`" + coreColumn.getName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + baseEntityName + "`.`" + coreColumn.getName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; default: // ============================================================================= if (isSelectStatement) { // add the select statement sqlStatement = "NULL as `" + coreColumn.getLabel() + "`"; sqlBuilder.SELECT(sqlStatement); } // ============================================================================= break; } break; case GROUP: switch (coreColumn) { case GROUP_NAME: case GROUP_ID: // ============================================================================= if (isSelectStatement) { sqlStatement = "`" + baseEntityName + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + baseEntityName + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case BRANCH_NAME: case STAFF_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_GROUP); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); // m_office/m_staff and m_group table join sqlStatement = "`" + referencedTableName + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`id` = `" + baseEntityName + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_GROUP, sqlStatement, parentTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add the WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; default: // ============================================================================= if (isSelectStatement) { sqlStatement = "NULL as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } // ============================================================================= break; } break; case LOAN: switch (coreColumn) { case LOAN_OFFICER_NAME: case CLIENT_NAME: case CLIENT_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); // m_client/m_staff and m_loan sqlStatement = "`" + referencedTable.getName() + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`id` = `" + baseEntityName + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_LOAN, sqlStatement, parentTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case GROUP_NAME: case GROUP_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupClientTableAlias = DataExportCoreTable.M_GROUP_CLIENT .getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_GROUP_CLIENT.getName() + "` `" + mGroupClientTableAlias + "` on `" + mGroupClientTableAlias + "`.`client_id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, baseEntityName, mGroupClientTableAlias); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_group_client and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = case when " + "isnull(`" + baseEntityName + "`.`group_id`) then `" + dataExportSqlJoin.getChildTableAlias() + "`.`group_id` else `" + baseEntityName + "`.`group_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, mGroupTableAlias, dataExportSqlJoin.getChildTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case BRANCH_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN, sqlStatement, mClientTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + baseEntityName + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN, sqlStatement, mGroupTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); DataExportSqlJoin clientLoanSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); DataExportSqlJoin groupLoanSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mOfficeTableAlias = DataExportCoreTable.M_OFFICE.getAlias(aliasPostfixNumber.intValue()); // m_office and m_client/m_group table join sqlStatement = "`" + DataExportCoreTable.M_OFFICE.getName() + "` `" + mOfficeTableAlias + "` on `" + mOfficeTableAlias + "`.`id` = case when " + "isnull(`" + baseEntityName + "`.`group_id`) then `" + clientLoanSqlJoin.getParentTableAlias() + "`.`office_id` else `" + groupLoanSqlJoin.getParentTableAlias() + "`.`office_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_LOAN, sqlStatement, mOfficeTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_LOAN); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case STAFF_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN, sqlStatement, mClientTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + baseEntityName + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN, sqlStatement, mGroupTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); DataExportSqlJoin clientLoanSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); DataExportSqlJoin groupLoanSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mStaffTableAlias = DataExportCoreTable.M_STAFF.getAlias(aliasPostfixNumber.intValue()); // m_staff and m_client/m_group table join sqlStatement = "`" + DataExportCoreTable.M_STAFF.getName() + "` `" + mStaffTableAlias + "` on `" + mStaffTableAlias + "`.`id` = case when " + "isnull(`" + baseEntityName + "`.`group_id`) then `" + clientLoanSqlJoin.getParentTableAlias() + "`.`staff_id` else `" + groupLoanSqlJoin.getParentTableAlias() + "`.`staff_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN, sqlStatement, mStaffTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case DATE_OF_BIRTH: case PHONE_NUMBER: case GENDER: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN, sqlStatement, mClientTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; case LOAN_ARREARS_AMOUNT: case LOAN_ARREARS_DATE: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); // m_client/m_staff and m_loan sqlStatement = "`" + referencedTable.getName() + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`loan_id` = `" + baseEntityName + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_LOAN, sqlStatement, parentTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_ARREARS_DAYS: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); // m_client/m_staff and m_loan sqlStatement = "`" + referencedTable.getName() + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`loan_id` = `" + baseEntityName + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_LOAN, sqlStatement, parentTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "DATEDIFF(CURDATE(), `" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "`) as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "DATEDIFF(CURDATE(), `" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "`) " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; default: // ============================================================================= if (isSelectStatement) { // add the select statement sqlStatement = "NULL as `" + coreColumn.getLabel() + "`"; sqlBuilder.SELECT(sqlStatement); } // ============================================================================= break; } break; case SAVINGS_ACCOUNT: switch (coreColumn) { case CLIENT_NAME: case CLIENT_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); // m_client/m_staff and m_savings_account sqlStatement = "`" + referencedTable.getName() + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`id` = `" + baseEntityName + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, parentTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case GROUP_NAME: case GROUP_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupClientTableAlias = DataExportCoreTable.M_GROUP_CLIENT .getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_GROUP_CLIENT.getName() + "` `" + mGroupClientTableAlias + "` on `" + mGroupClientTableAlias + "`.`client_id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, baseEntityName, mGroupClientTableAlias); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_group_client and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = case when " + "isnull(`" + baseEntityName + "`.`group_id`) then `" + dataExportSqlJoin.getChildTableAlias() + "`.`group_id` else `" + baseEntityName + "`.`group_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, mGroupTableAlias, dataExportSqlJoin.getChildTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case BRANCH_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mClientTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + baseEntityName + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mGroupTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT); DataExportSqlJoin clientSavingsAccount = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_SAVINGS_ACCOUNT); DataExportSqlJoin groupSavingsAccount = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mOfficeTableAlias = DataExportCoreTable.M_OFFICE.getAlias(aliasPostfixNumber.intValue()); // m_office and m_client/m_group table join sqlStatement = "`" + DataExportCoreTable.M_OFFICE.getName() + "` `" + mOfficeTableAlias + "` on `" + mOfficeTableAlias + "`.`id` = case when " + "isnull(`" + baseEntityName + "`.`group_id`) then `" + clientSavingsAccount.getParentTableAlias() + "`.`office_id` else `" + groupSavingsAccount.getParentTableAlias() + "`.`office_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mOfficeTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_SAVINGS_ACCOUNT); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case STAFF_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mClientTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + baseEntityName + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mGroupTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT); DataExportSqlJoin clientSavingsAccount = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_SAVINGS_ACCOUNT); DataExportSqlJoin groupSavingsAccount = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mStaffTableAlias = DataExportCoreTable.M_STAFF.getAlias(aliasPostfixNumber.intValue()); // m_staff and m_client/m_group table join sqlStatement = "`" + DataExportCoreTable.M_STAFF.getName() + "` `" + mStaffTableAlias + "` on `" + mStaffTableAlias + "`.`id` = case when " + "isnull(`" + baseEntityName + "`.`group_id`) then `" + clientSavingsAccount.getParentTableAlias() + "`.`staff_id` else `" + groupSavingsAccount.getParentTableAlias() + "`.`staff_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mStaffTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_SAVINGS_ACCOUNT); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case DATE_OF_BIRTH: case PHONE_NUMBER: case GENDER: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mClientTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; default: // ============================================================================= if (isSelectStatement) { // add the select statement sqlStatement = "NULL as `" + coreColumn.getLabel() + "`"; sqlBuilder.SELECT(sqlStatement); } // ============================================================================= break; } break; case LOAN_TRANSACTION: switch (coreColumn) { case LOAN_TRANSACTION_TOTAL_REPAID: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`amount` " + "is not null and `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.REPAYMENT.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`amount` " + "is not null and `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.REPAYMENT.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_TRANSACTION_PRINCIPAL_REPAID: case LOAN_TRANSACTION_INTEREST_REPAID: case LOAN_TRANSACTION_FEES_REPAID: case LOAN_TRANSACTION_PENALTIES_REPAID: case LOAN_TRANSACTION_OVERPAYMENT_REPAID: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`" + coreColumn.getName() + "` " + "is not null and `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.REPAYMENT.getValue() + " then `" + baseEntityName + "`.`" + coreColumn.getName() + "` else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`" + coreColumn.getName() + "` " + "is not null and `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.REPAYMENT.getValue() + " then `" + baseEntityName + "`.`" + coreColumn.getName() + "` else NULL end " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_TRANSACTION_INTEREST_ACCRUED: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`interest_portion_derived` " + "is not null and `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.ACCRUAL.getValue() + " then `" + baseEntityName + "`.`interest_portion_derived` else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`interest_portion_derived` " + "is not null and `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.ACCRUAL.getValue() + " then `" + baseEntityName + "`.`interest_portion_derived` else NULL end " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_TRANSACTION_INTEREST_WAIVED: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`interest_portion_derived` " + "is not null and `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.WAIVE_INTEREST.getValue() + " then `" + baseEntityName + "`.`interest_portion_derived` " + "else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`interest_portion_derived` " + "is not null and `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.WAIVE_INTEREST.getValue() + " then `" + baseEntityName + "`.`interest_portion_derived` " + "else NULL end " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; case LOAN_TRANSACTION_TRANSFER_AMOUNT: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.APPROVE_TRANSFER.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.APPROVE_TRANSFER.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end `" + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_TRANSACTION_TOTAL_RECOVERED: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.RECOVERY_REPAYMENT.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + LoanTransactionType.RECOVERY_REPAYMENT.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end `" + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_TRANSACTION_PRODUCT_SHORT_NAME: case LOAN_TRANSACTION_PRODUCT_NAME: case LOAN_TRANSACTION_PRODUCT_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mProductLoanTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + referencedTable.getName() + "` `" + mProductLoanTableAlias + "` on `" + mProductLoanTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_LOAN, sqlStatement, mProductLoanTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_TRANSACTION_LOAN_ACCOUNT_NUMBER: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add the WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_TRANSACTION_PAYMENT_CHANNEL: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_PAYMENT_DETAIL, DataExportCoreTable.M_LOAN_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mPaymentDetailTableAlias = DataExportCoreTable.M_PAYMENT_DETAIL .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_PAYMENT_DETAIL.getName() + "` `" + mPaymentDetailTableAlias + "` on `" + mPaymentDetailTableAlias + "`.`id` = `" + baseEntityName + "`.`payment_detail_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_PAYMENT_DETAIL, DataExportCoreTable.M_LOAN_TRANSACTION, sqlStatement, mPaymentDetailTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_PAYMENT_DETAIL); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + referencedTable.getName() + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_PAYMENT_DETAIL, sqlStatement, parentTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add the WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_TRANSACTION_REFERENCE: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_PAYMENT_DETAIL, DataExportCoreTable.M_LOAN_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mPaymentDetailTableAlias = DataExportCoreTable.M_PAYMENT_DETAIL .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_PAYMENT_DETAIL.getName() + "` `" + mPaymentDetailTableAlias + "` on `" + mPaymentDetailTableAlias + "`.`id` = `" + baseEntityName + "`.`payment_detail_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_PAYMENT_DETAIL, DataExportCoreTable.M_LOAN_TRANSACTION, sqlStatement, mPaymentDetailTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add the WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_OFFICER_NAME: case CLIENT_NAME: case CLIENT_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + referencedTable.getName() + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_LOAN, sqlStatement, parentTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case GROUP_NAME: case GROUP_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupClientTableAlias = DataExportCoreTable.M_GROUP_CLIENT .getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_GROUP_CLIENT.getName() + "` `" + mGroupClientTableAlias + "` on `" + mGroupClientTableAlias + "`.`client_id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, dataExportSqlJoin.getParentTableAlias(), mGroupClientTableAlias); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION); DataExportSqlJoin loanTransactionSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_CLIENT); DataExportSqlJoin loanGroupClientSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_group_client and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = case when " + "isnull(`" + loanTransactionSqlJoin.getParentTableAlias() + "`.`group_id`) then `" + loanGroupClientSqlJoin.getChildTableAlias() + "`.`group_id` else `" + loanTransactionSqlJoin.getParentTableAlias() + "`.`group_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, mGroupTableAlias, loanGroupClientSqlJoin.getChildTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case BRANCH_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_LOAN_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mOfficeTableAlias = DataExportCoreTable.M_OFFICE.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_OFFICE.getName() + "` `" + mOfficeTableAlias + "` on `" + mOfficeTableAlias + "`.`id` = `" + baseEntityName + "`.`office_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_LOAN_TRANSACTION, sqlStatement, mOfficeTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case STAFF_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN, sqlStatement, mClientTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION); DataExportSqlJoin loanTransactionSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + loanTransactionSqlJoin.getParentTableAlias() + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN, sqlStatement, mGroupTableAlias, loanTransactionSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION); DataExportSqlJoin loanTransactionSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); DataExportSqlJoin clientLoanSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); DataExportSqlJoin groupLoanSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mStaffTableAlias = DataExportCoreTable.M_STAFF.getAlias(aliasPostfixNumber.intValue()); // m_group_client and m_group table join sqlStatement = "`" + DataExportCoreTable.M_STAFF.getName() + "` `" + mStaffTableAlias + "` on `" + mStaffTableAlias + "`.`id` = case when " + "isnull(`" + loanTransactionSqlJoin.getParentTableAlias() + "`.`group_id`) then `" + clientLoanSqlJoin.getParentTableAlias() + "`.`staff_id` else `" + groupLoanSqlJoin.getParentTableAlias() + "`.`staff_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN, sqlStatement, mStaffTableAlias, loanTransactionSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case DATE_OF_BIRTH: case PHONE_NUMBER: case GENDER: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_TRANSACTION, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN, sqlStatement, mClientTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_TRANSACTION_CREATED_BY: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_APP_USER, DataExportCoreTable.M_LOAN_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mAppUserTableAlias = DataExportCoreTable.M_APP_USER.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_APP_USER.getName() + "` `" + mAppUserTableAlias + "` on `" + mAppUserTableAlias + "`.`id` = `" + baseEntityName + "`.`" + coreColumn.getForeignKeyIndexColumnName() + "` "; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_APP_USER, DataExportCoreTable.M_LOAN_TRANSACTION, sqlStatement, mAppUserTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; default: // ============================================================================= if (isSelectStatement) { // add the select statement sqlStatement = "NULL as `" + coreColumn.getLabel() + "`"; sqlBuilder.SELECT(sqlStatement); } // ============================================================================= break; } break; case SAVINGS_ACCOUNT_TRANSACTION: switch (coreColumn) { case SAVINGS_TRANSACTION_DEPOSIT: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.DEPOSIT.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.DEPOSIT.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case SAVINGS_TRANSACTION_INTEREST_POSTING: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.INTEREST_POSTING.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.INTEREST_POSTING.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case SAVINGS_TRANSACTION_CHARGE_APPLIED: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.WITHDRAWAL_FEE.getValue() + " or `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.ANNUAL_FEE.getValue() + " then `" + baseEntityName + "`.`amount` " + "else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.WITHDRAWAL_FEE.getValue() + " or `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.ANNUAL_FEE.getValue() + " then `" + baseEntityName + "`.`amount` " + "else NULL end " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case SAVINGS_TRANSACTION_CHARGE_WAIVED: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.WAIVE_CHARGES.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.WAIVE_CHARGES.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case SAVINGS_TRANSACTION_TRANSFER_AMOUNT: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.APPROVE_TRANSFER.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.APPROVE_TRANSFER.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case SAVINGS_TRANSACTION_PRODUCT_SHORT_NAME: case SAVINGS_TRANSACTION_PRODUCT_NAME: case SAVINGS_TRANSACTION_PRODUCT_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mSavingsAccountTableAlias = DataExportCoreTable.M_SAVINGS_ACCOUNT .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_SAVINGS_ACCOUNT.getName() + "` `" + mSavingsAccountTableAlias + "` on `" + mSavingsAccountTableAlias + "`.`id` = `" + baseEntityName + "`.`savings_account_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION, sqlStatement, mSavingsAccountTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mSavingsProductTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + referencedTable.getName() + "` `" + mSavingsProductTableAlias + "` on `" + mSavingsProductTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mSavingsProductTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case SAVINGS_TRANSACTION_ACCOUNT_NUMBER: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mSavingsAccountTableAlias = DataExportCoreTable.M_SAVINGS_ACCOUNT .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_SAVINGS_ACCOUNT.getName() + "` `" + mSavingsAccountTableAlias + "` on `" + mSavingsAccountTableAlias + "`.`id` = `" + baseEntityName + "`.`savings_account_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION, sqlStatement, mSavingsAccountTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add the WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case SAVINGS_TRANSACTION_PAYMENT_CHANNEL: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_PAYMENT_DETAIL, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mPaymentDetailTableAlias = DataExportCoreTable.M_PAYMENT_DETAIL .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_PAYMENT_DETAIL.getName() + "` `" + mPaymentDetailTableAlias + "` on `" + mPaymentDetailTableAlias + "`.`id` = `" + baseEntityName + "`.`payment_detail_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_PAYMENT_DETAIL, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION, sqlStatement, mPaymentDetailTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_PAYMENT_DETAIL); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + referencedTable.getName() + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_PAYMENT_DETAIL, sqlStatement, parentTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add the WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case SAVINGS_TRANSACTION_REFERENCE: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_PAYMENT_DETAIL, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mPaymentDetailTableAlias = DataExportCoreTable.M_PAYMENT_DETAIL .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_PAYMENT_DETAIL.getName() + "` `" + mPaymentDetailTableAlias + "` on `" + mPaymentDetailTableAlias + "`.`id` = `" + baseEntityName + "`.`payment_detail_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_PAYMENT_DETAIL, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION, sqlStatement, mPaymentDetailTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add the WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case SAVINGS_TRANSACTION_WITHDRAWAL: // ============================================================================= if (isSelectStatement) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.WITHDRAWAL.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "case when `" + baseEntityName + "`.`transaction_type_enum` = " + SavingsAccountTransactionType.WITHDRAWAL.getValue() + " then `" + baseEntityName + "`.`amount` else NULL end " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case CLIENT_NAME: case CLIENT_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mSavingsAccountTableAlias = DataExportCoreTable.M_SAVINGS_ACCOUNT .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_SAVINGS_ACCOUNT.getName() + "` `" + mSavingsAccountTableAlias + "` on `" + mSavingsAccountTableAlias + "`.`id` = `" + baseEntityName + "`.`savings_account_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION, sqlStatement, mSavingsAccountTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + referencedTable.getName() + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, parentTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_SAVINGS_ACCOUNT); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case GROUP_NAME: case GROUP_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mSavingsAccountTableAlias = DataExportCoreTable.M_SAVINGS_ACCOUNT .getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_SAVINGS_ACCOUNT.getName() + "` `" + mSavingsAccountTableAlias + "` on `" + mSavingsAccountTableAlias + "`.`id` = `" + baseEntityName + "`.`savings_account_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION, sqlStatement, mSavingsAccountTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupClientTableAlias = DataExportCoreTable.M_GROUP_CLIENT .getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_GROUP_CLIENT.getName() + "` `" + mGroupClientTableAlias + "` on `" + mGroupClientTableAlias + "`.`client_id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, dataExportSqlJoin.getParentTableAlias(), mGroupClientTableAlias); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_GROUP_CLIENT); DataExportSqlJoin savingsAccountGroupClientSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_group_client and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = case when " + "isnull(`" + savingsAccountGroupClientSqlJoin.getParentTableAlias() + "`.`group_id`) then `" + savingsAccountGroupClientSqlJoin.getChildTableAlias() + "`.`group_id` else `" + savingsAccountGroupClientSqlJoin.getParentTableAlias() + "`.`group_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, mGroupTableAlias, savingsAccountGroupClientSqlJoin.getChildTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case BRANCH_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mOfficeTableAlias = DataExportCoreTable.M_OFFICE.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_OFFICE.getName() + "` `" + mOfficeTableAlias + "` on `" + mOfficeTableAlias + "`.`id` = `" + baseEntityName + "`.`office_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION, sqlStatement, mOfficeTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case STAFF_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mSavingsAccountTableAlias = DataExportCoreTable.M_SAVINGS_ACCOUNT .getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_SAVINGS_ACCOUNT.getName() + "` `" + mSavingsAccountTableAlias + "` on `" + mSavingsAccountTableAlias + "`.`id` = `" + baseEntityName + "`.`savings_account_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION, sqlStatement, mSavingsAccountTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + dataExportSqlJoin.getChildTableAlias() + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mClientTableAlias, dataExportSqlJoin.getChildTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT); DataExportSqlJoin clientSavingsAccountSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + clientSavingsAccountSqlJoin.getChildTableAlias() + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mGroupTableAlias, clientSavingsAccountSqlJoin.getChildTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT); DataExportSqlJoin clientSavingsAccountSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_SAVINGS_ACCOUNT); DataExportSqlJoin groupSavingsAccountSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mStaffTableAlias = DataExportCoreTable.M_STAFF.getAlias(aliasPostfixNumber.intValue()); // m_group_client and m_group table join sqlStatement = "`" + DataExportCoreTable.M_STAFF.getName() + "` `" + mStaffTableAlias + "` on `" + mStaffTableAlias + "`.`id` = case when " + "isnull(`" + clientSavingsAccountSqlJoin.getChildTableAlias() + "`.`group_id`) then `" + clientSavingsAccountSqlJoin.getParentTableAlias() + "`.`staff_id` else `" + groupSavingsAccountSqlJoin.getParentTableAlias() + "`.`staff_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mStaffTableAlias, clientSavingsAccountSqlJoin.getChildTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_SAVINGS_ACCOUNT); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case DATE_OF_BIRTH: case PHONE_NUMBER: case GENDER: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mSavingsAccountTableAlias = DataExportCoreTable.M_SAVINGS_ACCOUNT .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_SAVINGS_ACCOUNT.getName() + "` `" + mSavingsAccountTableAlias + "` on `" + mSavingsAccountTableAlias + "`.`id` = `" + baseEntityName + "`.`savings_account_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_SAVINGS_ACCOUNT, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION, sqlStatement, mSavingsAccountTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_SAVINGS_ACCOUNT, sqlStatement, mClientTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case SAVINGS_TRANSACTION_CREATED_BY: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_APP_USER, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mAppUserTableAlias = DataExportCoreTable.M_APP_USER.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_APP_USER.getName() + "` `" + mAppUserTableAlias + "` on `" + mAppUserTableAlias + "`.`id` = `" + baseEntityName + "`.`" + coreColumn.getForeignKeyIndexColumnName() + "` "; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_APP_USER, DataExportCoreTable.M_SAVINGS_ACCOUNT_TRANSACTION, sqlStatement, mAppUserTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; default: // ============================================================================= if (isSelectStatement) { // add the select statement sqlStatement = "NULL as `" + coreColumn.getLabel() + "`"; sqlBuilder.SELECT(sqlStatement); } // ============================================================================= break; } break; case LOAN_REPAYMENT_SCHEDULE: String outstandingInterest = "(ifnull(`" + baseEntityName + "`.`interest_amount`, 0)" + " - ifnull(`" + baseEntityName + "`.`interest_writtenoff_derived`, 0)" + " - ifnull(`" + baseEntityName + "`.`interest_waived_derived`, 0)" + " - ifnull(`" + baseEntityName + "`.`interest_completed_derived`, 0))"; String outstandingPrincipal = "(ifnull(`" + baseEntityName + "`.`principal_amount`, 0)" + " - ifnull(`" + baseEntityName + "`.`principal_completed_derived`, 0)" + " - ifnull(`" + baseEntityName + "`.`principal_writtenoff_derived`, 0))"; String outstandingFees = "(ifnull(`" + baseEntityName + "`.`fee_charges_amount`, 0)" + " - ifnull(`" + baseEntityName + "`.`fee_charges_completed_derived`, 0)" + " - ifnull(`" + baseEntityName + "`.`fee_charges_writtenoff_derived`, 0)" + " - ifnull(`" + baseEntityName + "`.`fee_charges_waived_derived`, 0))"; String outstandingPenalties = "(ifnull(`" + baseEntityName + "`.`penalty_charges_amount`, 0)" + " - ifnull(`" + baseEntityName + "`.`penalty_charges_completed_derived`, 0)" + " - ifnull(`" + baseEntityName + "`.`penalty_charges_writtenoff_derived`, 0)" + " - ifnull(`" + baseEntityName + "`.`penalty_charges_waived_derived`, 0))"; switch (coreColumn) { case REPAYMENT_SCHEDULE_TOTAL_OUTSTANDING: // ============================================================================= if (isSelectStatement) { sqlStatement = "(" + outstandingInterest + " + " + outstandingPrincipal + " + " + outstandingFees + " + " + outstandingPenalties + ") as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "(" + outstandingInterest + " + " + outstandingPrincipal + " + " + outstandingFees + " + " + outstandingPenalties + ")" + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case REPAYMENT_SCHEDULE_PRINCIPAL_OUTSTANDING: // ============================================================================= if (isSelectStatement) { sqlStatement = outstandingPrincipal + " as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = outstandingPrincipal + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case REPAYMENT_SCHEDULE_INTEREST_OUTSTANDING: // ============================================================================= if (isSelectStatement) { sqlStatement = outstandingInterest + " as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = outstandingInterest + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case REPAYMENT_SCHEDULE_FEES_OUTSTANDING: // ============================================================================= if (isSelectStatement) { sqlStatement = outstandingFees + " as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = outstandingFees + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case REPAYMENT_SCHEDULE_PENALTIES_OUTSTANDING: // ============================================================================= if (isSelectStatement) { sqlStatement = outstandingPenalties + " as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = outstandingPenalties + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case REPAYMENT_SCHEDULE_TOTAL_EXPECTED: // ============================================================================= if (isSelectStatement) { sqlStatement = "(ifnull(`" + baseEntityName + "`.`principal_amount`, 0) + " + "ifnull(`" + baseEntityName + "`.`interest_amount`, 0) + " + "ifnull(`" + baseEntityName + "`.`fee_charges_amount`, 0) + " + "ifnull(`" + baseEntityName + "`.`penalty_charges_amount`, 0)) " + "as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "(ifnull(`" + baseEntityName + "`.`principal_amount`, 0) + " + "ifnull(`" + baseEntityName + "`.`interest_amount`, 0) + " + "ifnull(`" + baseEntityName + "`.`fee_charges_amount`, 0) + " + "ifnull(`" + baseEntityName + "`.`penalty_charges_amount`, 0)) " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case REPAYMENT_SCHEDULE_INTEREST_EXPECTED: case REPAYMENT_SCHEDULE_FEES_EXPECTED: case REPAYMENT_SCHEDULE_PENALTIES_EXPECTED: case REPAYMENT_SCHEDULE_PRINCIPAL_EXPECTED: // ============================================================================= final String actualColumnName = StringUtils.replace(coreColumn.getName(), "repayment_schedule_", ""); if (isSelectStatement) { sqlStatement = "`" + baseEntityName + "`.`" + actualColumnName + "` " + "as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + baseEntityName + "`.`" + actualColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; case STAFF_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); mLoanTableAlias = dataExportSqlJoin.getParentTableAlias(); // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN, sqlStatement, mClientTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + mLoanTableAlias + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN, sqlStatement, mGroupTableAlias, mLoanTableAlias); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); DataExportSqlJoin clientLoanSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); DataExportSqlJoin groupLoanSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mStaffTableAlias = DataExportCoreTable.M_STAFF.getAlias(aliasPostfixNumber.intValue()); // m_staff and m_client/m_group table join sqlStatement = "`" + DataExportCoreTable.M_STAFF.getName() + "` `" + mStaffTableAlias + "` on `" + mStaffTableAlias + "`.`id` = case when " + "isnull(`" + mLoanTableAlias + "`.`group_id`) then `" + clientLoanSqlJoin.getParentTableAlias() + "`.`staff_id` else `" + groupLoanSqlJoin.getParentTableAlias() + "`.`staff_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN, sqlStatement, mStaffTableAlias, mLoanTableAlias); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case BRANCH_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN, sqlStatement, mClientTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN, sqlStatement, mGroupTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE); DataExportSqlJoin loanScheduleSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); DataExportSqlJoin groupLoanSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); DataExportSqlJoin clientLoanSqlJoin = sqlJoinMap.get(sqlJoinKey); // increment the alias postfix number aliasPostfixNumber.increment(); mOfficeTableAlias = DataExportCoreTable.M_OFFICE.getAlias(aliasPostfixNumber.intValue()); // m_office and m_client/m_group table join sqlStatement = "`" + DataExportCoreTable.M_OFFICE.getName() + "` `" + mOfficeTableAlias + "` on `" + mOfficeTableAlias + "`.`id` = case when " + "isnull(`" + loanScheduleSqlJoin.getParentTableAlias() + "`.`group_id`) then `" + clientLoanSqlJoin.getParentTableAlias() + "`.`office_id` else `" + groupLoanSqlJoin.getParentTableAlias() + "`.`office_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_LOAN, sqlStatement, mOfficeTableAlias, loanScheduleSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_LOAN); dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case GROUP_NAME: case GROUP_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupClientTableAlias = DataExportCoreTable.M_GROUP_CLIENT .getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group_client table join sqlStatement = "`" + DataExportCoreTable.M_GROUP_CLIENT.getName() + "` `" + mGroupClientTableAlias + "` on `" + mGroupClientTableAlias + "`.`client_id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, dataExportSqlJoin.getParentTableAlias(), mGroupClientTableAlias); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_group_client and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = case when " + "isnull(`" + dataExportSqlJoin.getParentTableAlias() + "`.`group_id`) then `" + dataExportSqlJoin.getChildTableAlias() + "`.`group_id` else `" + dataExportSqlJoin.getParentTableAlias() + "`.`group_id` end"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_GROUP_CLIENT, sqlStatement, mGroupTableAlias, dataExportSqlJoin.getChildTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case LOAN_OFFICER_NAME: case CLIENT_NAME: case CLIENT_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); parentTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + referencedTable.getName() + "` `" + parentTableAlias + "` on `" + parentTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_LOAN, sqlStatement, parentTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case DATE_OF_BIRTH: case PHONE_NUMBER: case GENDER: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_LOAN, sqlStatement, mClientTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case REPAYMENT_SCHEDULE_PRODUCT_SHORT_NAME: case REPAYMENT_SCHEDULE_PRODUCT_NAME: case REPAYMENT_SCHEDULE_PRODUCT_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(referencedTable, DataExportCoreTable.M_LOAN); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mProductLoanTableAlias = referencedTable.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + referencedTable.getName() + "` `" + mProductLoanTableAlias + "` on `" + mProductLoanTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`" + foreignKeyIndexColumnName + "`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(referencedTable, DataExportCoreTable.M_LOAN, sqlStatement, mProductLoanTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; case REPAYMENT_SCHEDULE_LOAN_ACCOUNT_NUMBER: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE); // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_LOAN_REPAYMENT_SCHEDULE, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + referencedColumnName + "` " + filterValue; // add the WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= break; default: // ============================================================================= if (isSelectStatement) { // add the select statement sqlStatement = "NULL as `" + coreColumn.getLabel() + "`"; sqlBuilder.SELECT(sqlStatement); } // ============================================================================= break; } break; case GROUP_LOAN_MEMBER_ALLOCATION: switch (coreColumn) { case CLIENT_ID: case CLIENT_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_group_loan_member_allocation and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION, sqlStatement, mClientTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; case DATE_OF_BIRTH: case PHONE_NUMBER: case GENDER: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_group_loan_member_allocation and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION, sqlStatement, mClientTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; case LOAN_OFFICER_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); // m_group_loan_member_allocation and m_loan table join sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mStaffTableAlias = DataExportCoreTable.M_STAFF.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group table join sqlStatement = "`" + DataExportCoreTable.M_STAFF.getName() + "` `" + mStaffTableAlias + "` on `" + mStaffTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`loan_officer_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_LOAN, sqlStatement, mStaffTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; case GROUP_NAME: case GROUP_ID: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); // m_group_loan_member_allocation and m_loan table join sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN, sqlStatement, mGroupTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; case GROUP_LOAN_MEMBER_ALLOCATION_LOAN_ACCOUNT_NUMBER: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); // m_group_loan_member_allocation and m_loan table join sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; case STAFF_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mClientTableAlias = DataExportCoreTable.M_CLIENT.getAlias(aliasPostfixNumber.intValue()); // m_group_loan_member_allocation and m_client table join sqlStatement = "`" + DataExportCoreTable.M_CLIENT.getName() + "` `" + mClientTableAlias + "` on `" + mClientTableAlias + "`.`id` = `" + baseEntityName + "`.`client_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_CLIENT, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION, sqlStatement, mClientTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_CLIENT); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mStaffTableAlias = DataExportCoreTable.M_STAFF.getAlias(aliasPostfixNumber.intValue()); // m_client and m_staff table join sqlStatement = "`" + DataExportCoreTable.M_STAFF.getName() + "` `" + mStaffTableAlias + "` on `" + mStaffTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`staff_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_STAFF, DataExportCoreTable.M_CLIENT, sqlStatement, mStaffTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; case BRANCH_NAME: // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mLoanTableAlias = DataExportCoreTable.M_LOAN.getAlias(aliasPostfixNumber.intValue()); // m_group_loan_member_allocation and m_loan table join sqlStatement = "`" + DataExportCoreTable.M_LOAN.getName() + "` `" + mLoanTableAlias + "` on `" + mLoanTableAlias + "`.`id` = `" + baseEntityName + "`.`loan_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_LOAN, DataExportCoreTable.M_GROUP_LOAN_MEMBER_ALLOCATION, sqlStatement, mLoanTableAlias, baseEntityName); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mGroupTableAlias = DataExportCoreTable.M_GROUP.getAlias(aliasPostfixNumber.intValue()); // m_loan and m_group table join sqlStatement = "`" + DataExportCoreTable.M_GROUP.getName() + "` `" + mGroupTableAlias + "` on `" + mGroupTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`group_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_GROUP, DataExportCoreTable.M_LOAN, sqlStatement, mGroupTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_GROUP); if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); mOfficeTableAlias = DataExportCoreTable.M_OFFICE.getAlias(aliasPostfixNumber.intValue()); // m_group and m_office table join sqlStatement = "`" + DataExportCoreTable.M_OFFICE.getName() + "` `" + mOfficeTableAlias + "` on `" + mOfficeTableAlias + "`.`id` = `" + dataExportSqlJoin.getParentTableAlias() + "`.`office_id`"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.M_OFFICE, DataExportCoreTable.M_GROUP, sqlStatement, mOfficeTableAlias, dataExportSqlJoin.getParentTableAlias()); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` as `" + coreColumn.getLabel() + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`" + coreColumn.getReferencedColumnName() + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } break; default: // ============================================================================= if (isSelectStatement) { // add the select statement sqlStatement = "NULL as `" + coreColumn.getLabel() + "`"; sqlBuilder.SELECT(sqlStatement); } // ============================================================================= break; } break; default: break; } } else if (columnName.equals("loan_status_id")) { // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.R_ENUM_VALUE, baseEntityCoreTable) + "_loan_status_id"; // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); String rEnumValuetableAlias = DataExportCoreTable.R_ENUM_VALUE .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.R_ENUM_VALUE.getName() + "` `" + rEnumValuetableAlias + "` on `" + rEnumValuetableAlias + "`.`enum_id` = `" + baseEntityName + "`.`" + columnName + "` and `" + rEnumValuetableAlias + "`.`enum_name` = '" + columnName + "'"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.R_ENUM_VALUE, baseEntityCoreTable, sqlStatement, rEnumValuetableAlias, baseEntityName); // update the DataExportSqlJoin id dataExportSqlJoin.updateId(sqlJoinKey); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`enum_value` as `" + columnLabel + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`enum_value`" + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= } else if (columnName.equals("loan_type_enum")) { // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.R_ENUM_VALUE, baseEntityCoreTable) + "_loan_type_enum"; // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); String rEnumValuetableAlias = DataExportCoreTable.R_ENUM_VALUE .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.R_ENUM_VALUE.getName() + "` `" + rEnumValuetableAlias + "` on `" + rEnumValuetableAlias + "`.`enum_id` = `" + baseEntityName + "`.`" + columnName + "` and `" + rEnumValuetableAlias + "`.`enum_name` = '" + columnName + "'"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.R_ENUM_VALUE, baseEntityCoreTable, sqlStatement, rEnumValuetableAlias, baseEntityName); // update the DataExportSqlJoin id dataExportSqlJoin.updateId(sqlJoinKey); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`enum_value` as `" + columnLabel + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`enum_value`" + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= } else if (columnName.equals("status_enum")) { // ============================================================================= sqlJoinKey = DataExportSqlJoin.createId(DataExportCoreTable.R_ENUM_VALUE, baseEntityCoreTable) + "_status_enum"; // only add the join statement if it hasn't been previously added if (!sqlJoinMap.containsKey(sqlJoinKey)) { // increment the alias postfix number aliasPostfixNumber.increment(); String rEnumValuetableAlias = DataExportCoreTable.R_ENUM_VALUE .getAlias(aliasPostfixNumber.intValue()); sqlStatement = "`" + DataExportCoreTable.R_ENUM_VALUE.getName() + "` `" + rEnumValuetableAlias + "` on `" + rEnumValuetableAlias + "`.`enum_id` = `" + baseEntityName + "`.`" + columnName + "` and `" + rEnumValuetableAlias + "`.`enum_name` = '" + columnName + "'"; dataExportSqlJoin = DataExportSqlJoin.newInstance(DataExportCoreTable.R_ENUM_VALUE, baseEntityCoreTable, sqlStatement, rEnumValuetableAlias, baseEntityName); // update the DataExportSqlJoin id dataExportSqlJoin.updateId(sqlJoinKey); sqlBuilder.LEFT_OUTER_JOIN(sqlStatement); // add the join to the map sqlJoinMap.put(dataExportSqlJoin.getId(), dataExportSqlJoin); } // ============================================================================= // ============================================================================= dataExportSqlJoin = sqlJoinMap.get(sqlJoinKey); if (isSelectStatement) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`enum_value` as `" + columnLabel + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + dataExportSqlJoin.getParentTableAlias() + "`.`enum_value`" + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= } else { // ============================================================================= if (isSelectStatement) { sqlStatement = "`" + baseEntityName + "`.`" + columnName + "` as `" + columnLabel + "`"; // add the select statement sqlBuilder.SELECT(sqlStatement); } else if (filterValue != null) { sqlStatement = "`" + baseEntityName + "`.`" + columnName + "` " + filterValue; // add a WHERE clause sqlBuilder.WHERE(sqlStatement); } // ============================================================================= } }
From source file:org.apache.gobblin.hive.policy.HiveRegistrationPolicyBase.java
/*** * Obtain Hive table names filtered by <code>dbPrefix</code> (if present). * * The returned {@link List} contains://from w w w. j av a 2s . c o m * A. If <code>dbPrefix</code> is absent: * 1. Table name returned by {@link #getTableName(Path)} * 2. Table names specified by <code>additional.hive.table.names</code> * B. If dbPrefix is present: * 1. Table names specified by <code>dbPrefix.hive.table.names</code> * * In table names above, the {@value PRIMARY_TABLE_TOKEN} if present is also replaced by the * table name obtained via {@link #getTableName(Path)}. * * @param dbPrefix Prefix to the property <code>additional.table.names</code>, to obtain table names only * for the specified db. Eg. If <code>dbPrefix</code> is db, then * <code>db.hive.table.names</code> is the resolved property name. * @param path Path for the table on filesystem. * @return Table names to register. */ protected List<String> getTableNames(Optional<String> dbPrefix, Path path) { List<String> tableNames = Lists.newArrayList(); Optional<String> primaryTableName; if ((primaryTableName = getTableName(path)).isPresent() && !dbPrefix.isPresent()) { tableNames.add(primaryTableName.get()); } Optional<Config> configForTopic = Optional.<Config>absent(); if (primaryTableName.isPresent()) { Timer.Context context = this.metricContext.timer(CONFIG_FOR_TOPIC_TIMER).time(); configForTopic = ConfigStoreUtils.getConfigForTopic(this.props.getProperties(), KafkaSource.TOPIC_NAME, this.configClient); context.close(); } String additionalNamesProp; if (dbPrefix.isPresent()) { additionalNamesProp = String.format("%s.%s", dbPrefix.get(), HIVE_TABLE_NAME); } else { additionalNamesProp = ADDITIONAL_HIVE_TABLE_NAMES; } if (configForTopic.isPresent() && configForTopic.get().hasPath(additionalNamesProp)) { for (String additionalTableName : Splitter.on(",").trimResults() .splitToList(configForTopic.get().getString(additionalNamesProp))) { String resolvedTableName = StringUtils.replace(additionalTableName, PRIMARY_TABLE_TOKEN, primaryTableName.get()); tableNames.add(this.tableNamePrefix + resolvedTableName + this.tableNameSuffix); } } else if (!Strings.isNullOrEmpty(this.props.getProp(additionalNamesProp))) { for (String additionalTableName : this.props.getPropAsList(additionalNamesProp)) { String resolvedTableName = primaryTableName.isPresent() ? StringUtils.replace(additionalTableName, PRIMARY_TABLE_TOKEN, primaryTableName.get()) : additionalTableName; tableNames.add(this.tableNamePrefix + resolvedTableName + this.tableNameSuffix); } } return tableNames; }
From source file:org.apache.gobblin.util.test.RetentionTestHelper.java
/** * * Does gobblin retention for test data. {@link DatasetCleaner} which does retention in production can not be directly called as we need to resolve some * runtime properties like ${testNameTempPath}. This directory contains all the setup data created for a test by {@link RetentionTestDataGenerator#setup()}. * It is unique for each test./*from www . j a va 2 s.c o m*/ * The default {@link ConfigClient} used by {@link DatasetCleaner} connects to config store configs. We need to provide a * mock {@link ConfigClient} since the configs are in classpath and not on config store. * * @param retentionConfigClasspathResource this is the same jobProps/config files used while running a real retention job * @param testNameTempPath temp path for this test where test data is generated */ public static void clean(FileSystem fs, Path retentionConfigClasspathResource, Optional<Path> additionalJobPropsClasspathResource, Path testNameTempPath) throws Exception { Properties additionalJobProps = new Properties(); if (additionalJobPropsClasspathResource.isPresent()) { try (final InputStream stream = RetentionTestHelper.class.getClassLoader() .getResourceAsStream(additionalJobPropsClasspathResource.get().toString())) { additionalJobProps.load(stream); } } if (retentionConfigClasspathResource.getName().endsWith(".job")) { Properties jobProps = new Properties(); try (final InputStream stream = RetentionTestHelper.class.getClassLoader() .getResourceAsStream(retentionConfigClasspathResource.toString())) { jobProps.load(stream); for (Entry<Object, Object> entry : jobProps.entrySet()) { jobProps.put(entry.getKey(), StringUtils.replace((String) entry.getValue(), "${testNameTempPath}", testNameTempPath.toString())); } } MultiCleanableDatasetFinder finder = new MultiCleanableDatasetFinder(fs, jobProps); for (Dataset dataset : finder.findDatasets()) { ((CleanableDataset) dataset).clean(); } } else { Config testConfig = ConfigFactory.parseResources(retentionConfigClasspathResource.toString()) .withFallback(ConfigFactory.parseMap(ImmutableMap.of("testNameTempPath", PathUtils.getPathWithoutSchemeAndAuthority(testNameTempPath).toString()))) .resolve(); ConfigClient client = mock(ConfigClient.class); when(client.getConfig(any(String.class))).thenReturn(testConfig); Properties jobProps = new Properties(); jobProps.setProperty(CleanableDatasetBase.SKIP_TRASH_KEY, Boolean.toString(true)); jobProps.setProperty(ConfigurationKeys.CONFIG_MANAGEMENT_STORE_URI, "dummy"); jobProps.setProperty(ConfigurationKeys.CONFIG_MANAGEMENT_STORE_ENABLED, "true"); jobProps.putAll(additionalJobProps); @SuppressWarnings("unchecked") DatasetsFinder<CleanableDataset> finder = (DatasetsFinder<CleanableDataset>) GobblinConstructorUtils .invokeFirstConstructor( Class.forName( testConfig.getString(MultiCleanableDatasetFinder.DATASET_FINDER_CLASS_KEY)), ImmutableList.of(fs, jobProps, testConfig, client), ImmutableList.of(fs, jobProps, client)); for (CleanableDataset dataset : finder.findDatasets()) { dataset.clean(); } } }
From source file:org.apache.metron.parsing.parsers.MetronGrok.java
/** * Compile the {@code Grok} pattern to named regex pattern. * * @param pattern : Grok pattern (ex: %{IP}) * @throws Exception/*from w w w. ja v a 2 s . co m*/ */ public void compile(String pattern) throws Exception { if (StringUtils.isBlank(pattern)) { throw new Exception("{pattern} should not be empty or null"); } namedRegex = pattern; originalGrokPattern = pattern; int index = 0; /** flag for infinite recurtion */ int iterationLeft = 1000; Boolean continueIteration = true; // Replace %{foo} with the regex (mostly groupname regex) // and then compile the regex while (continueIteration) { continueIteration = false; if (iterationLeft <= 0) { throw new Exception("Deep recursion pattern compilation of " + originalGrokPattern); } iterationLeft--; Matcher m = GrokUtils.GROK_PATTERN.matcher(namedRegex); // Match %{Foo:bar} -> pattern name and subname // Match %{Foo=regex} -> add new regex definition if (m.find()) { continueIteration = true; Map<String, String> group = m.namedGroups(); if (group.get("definition") != null) { try { addPattern(group.get("pattern"), group.get("definition")); group.put("name", group.get("name") + "=" + group.get("definition")); } catch (Exception e) { // Log the exeception } } namedRegexCollection.put("name" + index, (group.get("subname") != null ? group.get("subname") : group.get("name"))); namedRegex = StringUtils.replace(namedRegex, "%{" + group.get("name") + "}", "(?<name" + index + ">" + grokPatternDefinition.get(group.get("pattern")) + ")"); // System.out.println(_expanded_pattern); index++; } } if (namedRegex.isEmpty()) { throw new Exception("Pattern not fount"); } // Compile the regex compiledNamedRegex = Pattern.compile(namedRegex); }
From source file:org.apache.nifi.processors.poi.ConvertExcelToCSVProcessor.java
/** * Takes the original input filename and updates it by removing the file extension and replacing it with * the .csv extension.//w w w . ja v a 2s . c om * * @param origFileName * Original filename from the input file. * * @return * The new filename with the .csv extension that should be place in the output flowfile's attributes */ private String updateFilenameToCSVExtension(String nifiUUID, String origFileName, String sheetName) { StringBuilder stringBuilder = new StringBuilder(); if (StringUtils.isNotEmpty(origFileName)) { String ext = FilenameUtils.getExtension(origFileName); if (StringUtils.isNotEmpty(ext)) { stringBuilder.append(StringUtils.replace(origFileName, ("." + ext), "")); } else { stringBuilder.append(origFileName); } } else { stringBuilder.append(nifiUUID); } stringBuilder.append("_"); stringBuilder.append(sheetName); stringBuilder.append("."); stringBuilder.append("csv"); return stringBuilder.toString(); }
From source file:org.apache.nifi.scripting.JavaScriptScriptFactory.java
public String getScript(File scriptFile) throws IOException { StringBuilder sb = new StringBuilder(); final String parent = StringUtils.replace(scriptFile.getParent(), "\\", "/"); sb.append(PRELOADS).append("var $PATH = \"").append(parent).append("\"\n") .append(FileUtils.readFileToString(scriptFile, "UTF-8")); return sb.toString(); }
From source file:org.apache.nifi.scripting.ScriptEngineFactory.java
ScriptEngine getNewEngine(File scriptFile, String extension) throws ScriptException { ScriptEngine engine = scriptEngMgr.getEngineByExtension(extension); if (null == engine) { throw new IllegalArgumentException("No ScriptEngine exists for extension " + extension); }/*from ww w. j av a 2 s. c o m*/ // Initialize some paths StringBuilder sb = new StringBuilder(); switch (extension) { case "rb": String parent = scriptFile.getParent(); parent = StringUtils.replace(parent, "\\", "/"); sb.append("$:.unshift '").append(parent).append("'\n").append("$:.unshift File.join '").append(parent) .append("', 'lib'\n"); engine.eval(sb.toString()); break; case "py": parent = scriptFile.getParent(); parent = StringUtils.replace(parent, "\\", "/"); String lib = parent + "/lib"; sb.append("import sys\n").append("sys.path.append('").append(parent).append("')\n") .append("sys.path.append('").append(lib).append("')\n").append("__file__ = '") .append(scriptFile.getAbsolutePath()).append("'\n"); engine.eval(sb.toString()); break; default: break; } Object threading = engine.getFactory().getParameter(THREADING); // the MULTITHREADED status means that the scripts need to be careful about sharing state if (THREAD_ISOLATED.equals(threading) || STATELESS.equals(threading) || MULTITHREADED.equals(threading)) { // replace prior instance if any threadSafeEngines.put(extension, engine); } return engine; }
From source file:org.apache.rocketmq.filtersrv.filter.DynaCode.java
private String[] uploadSrcFile() throws Exception { List<String> srcFileAbsolutePaths = new ArrayList<String>(codeStrs.size()); for (String code : codeStrs) { if (StringUtils.isNotBlank(code)) { String packageName = getPackageName(code); String className = getClassName(code); if (StringUtils.isNotBlank(className)) { File srcFile = null; BufferedWriter bufferWriter = null; try { if (StringUtils.isBlank(packageName)) { File pathFile = new File(sourcePath); if (!pathFile.exists()) { if (!pathFile.mkdirs()) { throw new RuntimeException("create PathFile Error!"); }//from ww w . ja va 2 s . c om } srcFile = new File(sourcePath + FILE_SP + className + ".java"); } else { String srcPath = StringUtils.replace(packageName, ".", FILE_SP); File pathFile = new File(sourcePath + FILE_SP + srcPath); if (!pathFile.exists()) { if (!pathFile.mkdirs()) { throw new RuntimeException("create PathFile Error!"); } } srcFile = new File(pathFile.getAbsolutePath() + FILE_SP + className + ".java"); } synchronized (loadClass) { loadClass.put(getFullClassName(code), null); } if (null != srcFile) { LOGGER.warn("Dyna Create Java Source File:---->" + srcFile.getAbsolutePath()); srcFileAbsolutePaths.add(srcFile.getAbsolutePath()); srcFile.deleteOnExit(); } OutputStreamWriter outputStreamWriter = new OutputStreamWriter( new FileOutputStream(srcFile), encoding); bufferWriter = new BufferedWriter(outputStreamWriter); for (String lineCode : code.split(LINE_SP)) { bufferWriter.write(lineCode); bufferWriter.newLine(); } bufferWriter.flush(); } finally { if (null != bufferWriter) { bufferWriter.close(); } } } } } return srcFileAbsolutePaths.toArray(new String[srcFileAbsolutePaths.size()]); }
From source file:org.apache.struts2.osgi.FelixOsgiHost.java
protected void addExportedPackages(Properties strutsConfigProps, Properties configProps) { String[] rootPackages = StringUtils.split((String) strutsConfigProps.get("scanning.package.includes"), ","); ResourceFinder finder = new ResourceFinder(StringUtils.EMPTY); List<String> exportedPackages = new ArrayList<String>(); //build a list of subpackages for (String rootPackage : rootPackages) { try {//from w ww .j a v a 2 s . c om String version = null; if (rootPackage.indexOf(";") > 0) { String[] splitted = rootPackage.split(";"); rootPackage = splitted[0]; version = splitted[1]; } Map<URL, Set<String>> subpackagesMap = finder .findPackagesMap(StringUtils.replace(rootPackage.trim(), ".", "/")); for (Map.Entry<URL, Set<String>> entry : subpackagesMap.entrySet()) { URL url = entry.getKey(); Set<String> packages = entry.getValue(); //get version if not set if (StringUtils.isBlank(version)) version = getVersion(url); if (packages != null) { for (String subpackage : packages) { exportedPackages.add(subpackage + "; version=" + version); } } } } catch (IOException e) { if (LOG.isErrorEnabled()) LOG.error("Unable to find subpackages of [#0]", e, rootPackage); } } //make a string with the exported packages and add it to the system properties if (!exportedPackages.isEmpty()) { String systemPackages = (String) configProps.get(Constants.FRAMEWORK_SYSTEMPACKAGES); systemPackages = StringUtils.chomp(systemPackages, ",") + "," + StringUtils.join(exportedPackages, ","); configProps.put(Constants.FRAMEWORK_SYSTEMPACKAGES, systemPackages); } }