@Override
protected ProfileSummary retrieveConcrete() throws SQLException {
PreparedStatement select =
conn.prepareStatement(
"SELECT p.id, p.organization, e.version, p.surname, p.givenName, p.user, "
+ "o.name, IF(acl.acl_object_identity IS NOT NULL, TRUE, FALSE) AS isAdmin "
+ "FROM profile p "
+ "JOIN organization o ON p.organization=o.id "
+ "JOIN systemEntity e ON p.id=e.id "
+ "LEFT OUTER JOIN acl_entry acl ON p.id=acl.sid AND p.organization=acl.acl_object_identity"
+ " AND acl.mask=16 "
+ "WHERE p.user=? AND p.organization=?");
select.setInt(1, userId);
select.setInt(2, input);
ResultSet results = select.executeQuery();
if (!results.next()) {
return null;
}
Profile profile = new Profile(results.getInt(1), results.getInt(2), results.getInt(3));
profile.setSurname(results.getString(4));
profile.setGivenName(results.getString(5));
profile.setUserId(results.getInt(6));
ProfileSummary profileSummary =
new ProfileSummary(profile, results.getString(7), results.getBoolean(8));
results.close();
select.close();
return profileSummary;
}
@Override
public Profile process() {
try {
if (input.isEmpty()) {
return null;
}
List<Object> queryElements =
FluentIterable.from(input)
.transform(
new Function<String, Object>() {
@Override
public String apply(String input) {
return "LOWER(TRIM(value))=LOWER(TRIM(?))";
}
})
.toList();
String query = " AND " + Joiner.on(" OR ").join(queryElements);
PreparedStatement retrieve =
conn.prepareStatement(
"SELECT p.id, p.surname, p.givenName, se.version, p.user, p.organization "
+ "FROM profile p JOIN systemEntity se ON p.id=se.id JOIN contact c ON p.id = c"
+ ".entityId "
+ "WHERE c.medium='EMAIL' AND p.organization=?"
+ query
+ " ORDER BY user DESC");
retrieve.setInt(1, orgId);
int i = 2;
for (String email : input) {
retrieve.setString(i++, email);
}
ResultSet results = retrieve.executeQuery();
if (!results.next()) {
return null;
}
Profile profile = new Profile(results.getInt(1), results.getInt(6), results.getInt(4));
profile.setSurname(results.getString(2));
profile.setGivenName(results.getString(3));
profile.setUserId(results.getInt(5));
return profile;
} catch (SQLException e) {
throw new GeneralException(e);
}
}
private static ProfileSummary getProfileSummary(Connection conn, int profileId)
throws SQLException {
PreparedStatement select =
conn.prepareStatement(
"SELECT p.id, p.organization, e.version, p.surname, p.givenName, p.user, o.name, "
+ "IF(acl.acl_object_identity IS NOT NULL, TRUE, FALSE) AS isAdmin FROM profile p "
+ "JOIN organization o ON p.organization=o.id "
+ "JOIN systemEntity e ON p.id=e.id "
+ "LEFT OUTER JOIN acl_entry acl ON p.id=acl.sid "
+ "AND p.organization=acl.acl_object_identity AND acl.mask>="
+ ROLE_ADMIN
+ " WHERE p.id=?");
select.setInt(1, profileId);
ResultSet results = select.executeQuery();
if (!results.next()) {
return null;
}
Profile profile = new Profile(results.getInt(1), results.getInt(2), results.getInt(3));
profile.setSurname(results.getString(4));
profile.setGivenName(results.getString(5));
int userId = results.getInt(6);
if (!results.wasNull()) {
profile.setUserId(userId);
}
PreparedStatement convioSelect =
conn.prepareStatement(
"SELECT orgId, cons_id, syncStatus FROM personConvioSyncStatus WHERE personId=?");
convioSelect.setInt(1, profileId);
ResultSet convioResults = convioSelect.executeQuery();
while (convioResults.next()) {
profile.setConvioSyncStatus(
convioResults.getInt(1), convioResults.getInt(2), convioResults.getString(3));
}
return new ProfileSummary(profile, results.getString(7), results.getBoolean(8));
}
@Override
public List<ProfileSummary> process() {
try {
List<ProfileSummary> profileSummaries = new ArrayList<ProfileSummary>();
PreparedStatement update =
conn.prepareStatement(
"SELECT p.id, p.organization, e.version, p.surname, p.givenName, p.user, "
+ "o.name, IF(acl.acl_object_identity IS NOT NULL OR p.user="******", TRUE, "
+ "FALSE) AS isAdmin "
+ "FROM profile p "
+ "JOIN organization o ON p.organization=o.id "
+ "JOIN systemEntity e ON p.id=e.id "
+ "LEFT OUTER JOIN acl_entry acl ON p.id=acl.sid "
+ "AND p.organization=acl.acl_object_identity AND acl.mask>="
+ ROLE_ADMIN
+ " WHERE p.user=?");
update.setInt(1, input);
ResultSet results = update.executeQuery();
while (results.next()) {
Profile profile = new Profile(results.getInt(1), results.getInt(2), results.getInt(3));
profile.setSurname(results.getString(4));
profile.setGivenName(results.getString(5));
profile.setUserId(results.getInt(6)); // User cannot be null in this case
profileSummaries.add(
new ProfileSummary(profile, results.getString(7), results.getBoolean(8)));
}
return profileSummaries;
} catch (SQLException e) {
throw new GeneralException(e);
}
}