Esempio n. 1
1
  public static String prepareFilters(final SearchRequest req) {
    final StringBuilder builder = new StringBuilder(Query.FETCH_IMAGES.toString());

    // Add date filters
    builder.append(" where startedAt >= ? and endedAt <= ? ");

    if (req.getUserList() != null && req.getUserList().size() > 0) {
      // Add user name filters
      builder.append(" and userid in (");
      int count = req.getUserList().size();
      while (count-- > 0) builder.append("?,");
      builder.setLength(builder.length() - 1);
      builder.append(") ");
    }

    // Add location filters
    if (req.getLocationList() != null && req.getLocationList().size() > 0) {
      builder.append(" and location in (");
      int count = req.getLocationList().size();
      while (count-- > 0) builder.append("?,");
      builder.setLength(builder.length() - 1);
      builder.append(") ");
    }

    builder.append(" order by userid,startedAt;");

    return builder.toString();
  }
Esempio n. 2
0
  public static SearchResponse fetchImagesBasedOnUserQuery(final SearchRequest req)
      throws SQLException, IOException, PropertyVetoException, ClassNotFoundException,
          ParseException {
    final String query = prepareFilters(req);
    final Connection conn = DataSource.getInstance().getConnection();
    final PreparedStatement stmt = conn.prepareStatement(query);
    int index = 1;

    final Timestamp startDate = DbUtil.convertStringToSql(req.getDateStart());
    final Timestamp endDate = DbUtil.convertStringToSql(req.getDateEnd());

    // Fill date filters
    stmt.setTimestamp(index++, startDate);
    stmt.setTimestamp(index++, endDate);
    // stmt.setTimestamp(index++, startDate);
    // stmt.setTimestamp(index++, endDate);

    // Fill user name filters
    if (req.getUserList() != null && req.getUserList().size() > 0) {
      for (final String user : req.getUserList()) stmt.setString(index++, user);
    }

    // Fill location filters
    if (req.getLocationList() != null && req.getLocationList().size() > 0) {
      for (final String location : req.getLocationList()) stmt.setString(index++, location);
    }

    System.out.println(stmt);
    ResultSet rs = stmt.executeQuery();
    if (!rs.next()) {
      conn.close();
      throw new SQLException("Returned empty set.");
    } else {
      final LinkedList<User> userList = new LinkedList<User>();
      List<Image> imgList = new ArrayList<Image>();
      List<ImageSet> imgSet = new ArrayList<ImageSet>();
      String lastSeenUser = null;
      Timestamp lastSeenDay = null;
      do {
        final String sessionId = rs.getString("id");
        final String userId = rs.getString("userId");
        final String location = rs.getString("location");
        final List<String> s3Imgs = S3.getImages(userId + "/" + sessionId + "/");

        for (final String s3Image : s3Imgs) {
          final String[] keys = s3Image.split("_");
          final String imageId = keys[0];
          final Timestamp snapedAt =
              DbUtil.parseTimeStamp(keys[2], keys[3], keys[4], keys[5], keys[6], keys[7]);

          if (startDate.before(snapedAt) && snapedAt.before(endDate)) {

            if (lastSeenUser == null || !userId.equals(lastSeenUser)) {
              if (lastSeenUser != null) {
                imgSet.add(
                    new ImageSet(
                        DbUtil.convertTimestampToStringWithoutTime(lastSeenDay),
                        DbUtil.cloneImage(imgList),
                        imgList.size()));
                userList.add(new User(lastSeenUser, DbUtil.cloneImageSet(imgSet), imgSet.size()));
                lastSeenDay = null;
              }
              lastSeenUser = userId;
              imgSet.clear();
            }

            if (lastSeenDay == null || DbUtil.isDifferentDay(lastSeenDay, snapedAt)) {
              if (lastSeenDay != null)
                imgSet.add(
                    new ImageSet(
                        DbUtil.convertTimestampToStringWithoutTime(lastSeenDay),
                        DbUtil.cloneImage(imgList),
                        imgList.size()));
              lastSeenDay = snapedAt;
              imgList.clear();
            }

            // Get image properties.
            final Image image = new Image();
            image.setImageId(imageId);
            image.setSnapedAt(DbUtil.convertTimestampToString(snapedAt));
            image.setLocation(location);
            image.setResourcePath(
                AppGlobals.CLOUD_FRONT_PREFIX + "/" + userId + "/" + sessionId + "/" + s3Image);
            imgList.add(image);
          }
        }
      } while (rs.next());
      imgSet.add(
          new ImageSet(
              DbUtil.convertTimestampToStringWithoutTime(lastSeenDay),
              DbUtil.cloneImage(imgList),
              imgList.size()));
      userList.add(new User(lastSeenUser, imgSet, imgSet.size()));
      conn.close();
      return new SearchResponse("200", "Successfully fetched records.", userList);
    }
  }