/**
* @see
* org.programmerplanet.crm.data.dao.ObjectDataDao#clearObjectValue(org.programmerplanet.crm.metadata.ObjectDefinition,
* org.programmerplanet.crm.metadata.FieldDefinition, java.lang.Object)
*/
public void clearObjectValue(
ObjectDefinition objectDefinition, FieldDefinition fieldDefinition, Object value) {
String sql = "UPDATE " + objectDefinition.getTableName();
sql += " SET " + fieldDefinition.getColumnName() + " = NULL";
sql += " WHERE " + fieldDefinition.getColumnName() + " = ?";
Object[] params = new Object[] {value};
this.getJdbcTemplate().update(sql, params);
}
/**
* @see
* org.programmerplanet.crm.data.dao.ObjectDataDao#getObjectsAvailableForLinking(org.programmerplanet.crm.metadata.ObjectDefinition,
* java.util.List, org.programmerplanet.crm.metadata.Relationship,
* org.programmerplanet.crm.metadata.ObjectDefinition, java.util.UUID)
*/
public List<ObjectData> getObjectsAvailableForLinking(
ObjectDefinition objectDefinition,
List<FieldDefinition> fieldDefinitions,
Relationship relationship,
ObjectDefinition parentObjectDefinition,
UUID id) {
StringBuffer sql = new StringBuffer();
sql.append("SELECT ot.id, ");
for (FieldDefinition fieldDefinition : fieldDefinitions) {
String columnName = fieldDefinition.getColumnName();
sql.append("ot.");
sql.append(columnName);
sql.append(", ");
}
sql.delete(sql.length() - 2, sql.length());
sql.append(" FROM ");
sql.append(objectDefinition.getTableName());
sql.append(" AS ot ");
sql.append("WHERE NOT EXISTS (");
sql.append("SELECT * FROM ");
sql.append(relationship.getTableName());
sql.append(" AS rt ");
sql.append("WHERE rt.");
sql.append(objectDefinition.getTableName());
sql.append("_id = ot.id ");
sql.append("AND rt.");
sql.append(parentObjectDefinition.getTableName());
sql.append("_id = ?::uuid)");
List<Map> data =
this.getJdbcTemplate().queryForList(sql.toString(), new Object[] {id.toString()});
convertUUIDValues(data, fieldDefinitions);
List<ObjectData> objects = createObjectDataList(objectDefinition, fieldDefinitions, data);
return objects;
}
/**
* @see
* org.programmerplanet.crm.data.dao.ObjectDataDao#updateObject(org.programmerplanet.crm.data.ObjectData)
*/
public void updateObject(ObjectData objectData) {
ObjectDefinition objectDefinition = objectData.getObjectDefinition();
List<FieldDefinition> fieldDefinitions = objectData.getFieldDefinitions();
Map<String, Object> data = objectData.getData();
UUID id = objectData.getId();
List parameters = new ArrayList();
StringBuffer sql = new StringBuffer();
sql.append("UPDATE ");
sql.append(objectDefinition.getTableName());
sql.append(" SET ");
for (FieldDefinition fieldDefinition : fieldDefinitions) {
// special case - skip update of autonumber field
if (fieldDefinition.getDataType().equals(DataType.AUTO_NUMBER)) {
continue;
}
String columnName = fieldDefinition.getColumnName();
Object value = data.get(columnName);
sql.append(columnName);
if (value instanceof UUID) {
sql.append(" = ?::uuid, ");
value = value.toString();
} else {
sql.append(" = ?, ");
}
parameters.add(value);
}
sql.delete(sql.length() - 2, sql.length());
sql.append(" WHERE id = ?::uuid");
parameters.add(id.toString());
this.getJdbcTemplate().update(sql.toString(), parameters.toArray());
}
private void convertUUIDValues(Map object, List<FieldDefinition> fieldDefinitions) {
convertUUIDValue(object, "id");
for (FieldDefinition fieldDefinition : fieldDefinitions) {
if (fieldDefinition.getDataType().equals(DataType.OBJECT)) {
String columnName = fieldDefinition.getColumnName();
convertUUIDValue(object, columnName);
}
}
}
private Integer getNextAutoNumberValue(
ObjectDefinition objectDefinition, FieldDefinition fieldDefinition) {
// TODO: deal with concurrency issue here - it is possible for 2 people to get the same number
// TODO: it would probably also be better to maintain the value externally to prevent reuse
// after deletion
StringBuffer sql = new StringBuffer();
sql.append("SELECT MAX(");
sql.append(fieldDefinition.getColumnName());
sql.append(") FROM ");
sql.append(objectDefinition.getTableName());
int value = this.getJdbcTemplate().queryForInt(sql.toString());
return new Integer(value + 1);
}
private String getBasicSelectSql(
ObjectDefinition objectDefinition, List<FieldDefinition> fieldDefinitions) {
StringBuffer sql = new StringBuffer();
sql.append("SELECT id, ");
for (FieldDefinition fieldDefinition : fieldDefinitions) {
String columnName = fieldDefinition.getColumnName();
sql.append(columnName);
sql.append(", ");
}
sql.delete(sql.length() - 2, sql.length());
sql.append(" FROM ");
sql.append(objectDefinition.getTableName());
return sql.toString();
}
/**
* @see
* org.programmerplanet.crm.data.dao.ObjectDataDao#insertObject(org.programmerplanet.crm.data.ObjectData)
*/
public UUID insertObject(ObjectData objectData) {
ObjectDefinition objectDefinition = objectData.getObjectDefinition();
List<FieldDefinition> fieldDefinitions = objectData.getFieldDefinitions();
Map<String, Object> data = objectData.getData();
UUID id = UUID.randomUUID();
List parameters = new ArrayList();
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO ");
sql.append(objectDefinition.getTableName());
sql.append(" (id, ");
parameters.add(id);
for (FieldDefinition fieldDefinition : fieldDefinitions) {
String columnName = fieldDefinition.getColumnName();
sql.append(columnName);
sql.append(", ");
Object value = data.get(columnName);
// special case - set autonumber field manually
if (fieldDefinition.getDataType().equals(DataType.AUTO_NUMBER)) {
value = getNextAutoNumberValue(objectDefinition, fieldDefinition);
}
parameters.add(value);
}
sql.delete(sql.length() - 2, sql.length());
sql.append(") VALUES (");
for (int i = 0; i < parameters.size(); i++) {
Object parameter = parameters.get(i);
if (parameter instanceof UUID) {
sql.append("?::uuid, ");
parameters.set(i, parameter.toString());
} else {
sql.append("?, ");
}
}
sql.delete(sql.length() - 2, sql.length());
sql.append(")");
this.getJdbcTemplate().update(sql.toString(), parameters.toArray());
objectData.setId(id);
return id;
}
/**
* @see org.programmerplanet.crm.web.app.renderer.FieldRenderer#renderEditField(java.io.Writer,
* java.lang.Object, org.programmerplanet.crm.metadata.FieldDefinition)
*/
public void renderEditField(Writer writer, Object value, FieldDefinition fieldDefinition)
throws IOException {
writer.write("<select");
writer.write(" name=\"" + fieldDefinition.getColumnName() + "\">");
writer.write("<option value=\"\"></option>");
String str = getAsString(value, fieldDefinition);
List users = userManager.getUsers();
for (Iterator i = users.iterator(); i.hasNext(); ) {
User user = (User) i.next();
writer.write("<option value=\"");
writer.write(user.getUsername());
writer.write("\"");
if (user.getUsername().equals(str)) {
writer.write("selected=\"selected\"");
}
writer.write(">");
writer.write(user.getUsername());
writer.write("</option>");
}
writer.write("</select>");
}