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