private Map<String, String> getAffectedColumns( final List<ResultsetColumnHeaderData> columnHeaders, final Map<String, String> queryParams, final String keyFieldName) { String dateFormat = queryParams.get("dateFormat"); Locale clientApplicationLocale = null; String localeQueryParam = queryParams.get("locale"); if (!(StringUtils.isBlank(localeQueryParam))) clientApplicationLocale = new Locale(queryParams.get("locale")); String underscore = "_"; String space = " "; String pValue = null; String queryParamColumnUnderscored; String columnHeaderUnderscored; boolean notFound; Map<String, String> affectedColumns = new HashMap<String, String>(); Set<String> keys = queryParams.keySet(); for (String key : keys) { // ignores id and foreign key fields // also ignores locale and dateformat fields that are used for // validating numeric and date data if (!((key.equalsIgnoreCase("id")) || (key.equalsIgnoreCase(keyFieldName)) || (key.equals("locale")) || (key.equals("dateFormat")))) { notFound = true; // matches incoming fields with and without underscores (spaces // and underscores considered the same) queryParamColumnUnderscored = genericDataService.replace(key, space, underscore); for (ResultsetColumnHeaderData columnHeader : columnHeaders) { if (notFound) { columnHeaderUnderscored = genericDataService.replace(columnHeader.getColumnName(), space, underscore); if (queryParamColumnUnderscored.equalsIgnoreCase(columnHeaderUnderscored)) { pValue = queryParams.get(key); pValue = validateColumn(columnHeader, pValue, dateFormat, clientApplicationLocale); affectedColumns.put(columnHeader.getColumnName(), pValue); notFound = false; } } } if (notFound) { throw new PlatformDataIntegrityException( "error.msg.column.not.found", "Column: " + key + " Not Found"); } } } return affectedColumns; }
private String getAddSql( final List<ResultsetColumnHeaderData> columnHeaders, final String datatable, final String fkName, final Long appTableId, final Map<String, String> queryParams) { final Map<String, String> affectedColumns = getAffectedColumns(columnHeaders, queryParams, fkName); String pValueWrite = ""; String addSql = ""; String singleQuote = "'"; String insertColumns = ""; String selectColumns = ""; String columnName = ""; String pValue = null; for (String key : affectedColumns.keySet()) { pValue = affectedColumns.get(key); if (StringUtils.isEmpty(pValue)) { pValueWrite = "null"; } else { pValueWrite = singleQuote + genericDataService.replace(pValue, singleQuote, singleQuote + singleQuote) + singleQuote; } columnName = "`" + key + "`"; insertColumns += ", " + columnName; selectColumns += "," + pValueWrite + " as " + columnName; } addSql = "insert into `" + datatable + "` (`" + fkName + "` " + insertColumns + ")" + " select " + appTableId + " as id" + selectColumns; return addSql; }
private String getUpdateSql( final String datatable, final String keyFieldName, final Long keyFieldValue, final Map<String, Object> changedColumns) { // just updating fields that have changed since pre-update read - though // its possible these values are different from the page the user was // looking at and even different from the current db values (if some // other update got in quick) - would need a version field for // completeness but its okay to take this risk with additional fields // data if (changedColumns.size() == 0) return null; String pValue = null; String pValueWrite = ""; String singleQuote = "'"; boolean firstColumn = true; String sql = "update `" + datatable + "` "; for (String key : changedColumns.keySet()) { if (firstColumn) { sql += " set "; firstColumn = false; } else { sql += ", "; } pValue = (String) changedColumns.get(key); if (StringUtils.isEmpty(pValue)) { pValueWrite = "null"; } else { pValueWrite = singleQuote + genericDataService.replace(pValue, singleQuote, singleQuote + singleQuote) + singleQuote; } sql += "`" + key + "` = " + pValueWrite; } sql += " where " + keyFieldName + " = " + keyFieldValue; return sql; }
private String dataScopedSQL(final String unscopedSQL, final String appTable) { String dataScopeCriteria = null; /* * unfortunately have to, one way or another, be able to restrict data * to the users office hierarchy. Here it's hardcoded for client and * loan. They are the main application tables. But if additional fields * are needed on other tables like group, loan_transaction or others the * same applies (hardcoding of some sort) */ AppUser currentUser = context.authenticatedUser(); if (appTable.equalsIgnoreCase("m_client")) { dataScopeCriteria = " join m_office o on o.id = t.office_id and o.hierarchy like '" + currentUser.getOffice().getHierarchy() + "%'"; } if (appTable.equalsIgnoreCase("m_loan")) { dataScopeCriteria = " join m_client c on c.id = t.client_id " + " join m_office o on o.id = c.office_id and o.hierarchy like '" + currentUser.getOffice().getHierarchy() + "%'"; } if (appTable.equalsIgnoreCase("m_group")) { dataScopeCriteria = " join m_office o on o.id = t.office_id and o.hierarchy like '" + currentUser.getOffice().getHierarchy() + "%'"; } if (appTable.equalsIgnoreCase("m_office")) { dataScopeCriteria = " join m_office o on o.id = t.id and o.hierarchy like '" + currentUser.getOffice().getHierarchy() + "%'"; } if (dataScopeCriteria == null) { throw new PlatformDataIntegrityException( "error.msg.invalid.dataScopeCriteria", "Application Table: " + appTable + " not catered for in data Scoping"); } return genericDataService.replace(unscopedSQL, "${dataScopeCriteria}", dataScopeCriteria); }