@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 ProfileSummary createProfile(Profile profile) {
User loggedInUser = userService.getLoggedInUser();
if (profile.getUserId() != null && profile.getUserId() == loggedInUser.getId()
|| authService.isUserAuthorized(ROLE_ADMIN, profile.getOrganizationId())) {
return profilePersister.create(profile);
} else {
throw new NotAuthorizedException();
}
}
@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);
}
}
public ProfileSummary create(Profile profile) {
Profile savedProfile = persistenceService.process(new CreateProfileFunc(profile, this));
return getProfileSummary(savedProfile.getId());
}
@Override
public StoryExport<StoryTellerCsv> exportStoryTellers(
int profileId, Integer collectionId, Integer questionnaireId, int window) {
try {
int windowSize = 75;
User user = userService.getUserForProfile(profileId);
Profile userProfile = profilePersister.get(profileId);
int organizationContext = userProfile.getOrganizationId();
int accessMode = ACCESS_MODE_PRIVILEGED;
if (authService.isSuperUser(user)) {
accessMode = ACCESS_MODE_ROOT;
}
List<StoryTellerCsv> storyTellers = new ArrayList<StoryTellerCsv>();
try {
StoryTellersParams countParams =
new StoryTellersParams(
0,
1,
StorySortField.CREATED_OLD,
false,
collectionId,
questionnaireId,
accessMode,
userService.getEffectiveSubject(user),
null);
int countStories = storyService.getStorytellerCount(countParams);
// window starts at 0; bail out if we're asked for a window beyond what's available
if (window * windowSize >= countStories) {
return new StoryExport<StoryTellerCsv>(storyTellers, countStories);
}
try {
SolrQuery sQuery = new SolrQuery("*:*");
if (collectionId != null) {
sQuery.addFilterQuery("collections:" + collectionId);
sQuery.setSort("lastStoryDateByCollection_" + collectionId, SolrQuery.ORDER.desc);
}
if (questionnaireId != null) {
sQuery.addFilterQuery("questionnaires:" + questionnaireId);
sQuery.setSort("lastStoryDateByCollection_" + questionnaireId, SolrQuery.ORDER.desc);
}
if (!authService.isSuperUser(user)) {
sQuery.addFilterQuery("readAuths:" + organizationContext);
}
sQuery.setRows(windowSize);
sQuery.setStart(window * windowSize);
QueryResponse result = solrPersonServer.query(sQuery);
if (result.getResults().getNumFound() > 0) {
for (SolrDocument entries : result.getResults()) {
ProfileDocument doc = new ProfileDocument(entries);
Profile profile = profilePersister.get(doc.getId());
StoryTellerCsv storyTellerCsv = new StoryTellerCsv(doc, profile);
storyTellers.add(storyTellerCsv);
}
}
return new StoryExport<StoryTellerCsv>(storyTellers, countStories);
} catch (Exception e) {
throw new GeneralException(e);
}
} catch (Exception e) {
e.printStackTrace();
}
return new StoryExport<StoryTellerCsv>(storyTellers, 0);
} catch (NotLoggedInException e) {
throw new GeneralException(e);
}
}