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();
}
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);
}
}