예제 #1
0
    @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;
    }
예제 #2
0
    @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);
      }
    }
예제 #3
0
  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));
  }
예제 #4
0
    @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);
      }
    }