private static List<Integer> GetIds(
Context context, int dateId, Type type, String param, String beginY, String endY)
throws SQLException {
String subQuery = null;
int typeId = Item.returnId(context, "type", "");
String begin = "";
String end = "";
begin += beginY + "-01-01";
end += endY + "-12-31";
// Different subqueries for different types
if (type == Type.COLLECTION) {
subQuery = "(SELECT item_id FROM collection2item WHERE collection_id = ?) a";
} else if (type == Type.AUTHOR) {
subQuery =
"(SELECT item_id FROM metadatavalue WHERE metadata_field_id = ? AND text_value = ?) a";
}
// Main query
String query =
"SELECT item.item_id FROM item,handle, "
+ "(SELECT metadata_value_id ,metadatavalue.item_id,metadatavalue.text_value FROM metadatavalue WHERE metadatavalue.text_value >= ? AND metadatavalue.text_value <= ? AND metadata_field_id = ? "
+ "ORDER BY metadatavalue.text_value DESC) e, (SELECT item_id, metadatavalue.text_value FROM metadatavalue WHERE metadata_field_id = ?) g, "
+ subQuery
+ " WHERE g.item_id = item.item_id AND a.item_id = item.item_id AND item.item_id = e.item_id AND item.item_id = handle.resource_id AND "
+ "handle.resource_type_id = 2 AND in_archive AND NOT withdrawn ORDER BY e.text_value DESC, g.text_value";
List<Integer> ids = new ArrayList<Integer>();
PreparedStatement statement = context.getDBConnection().prepareStatement(query);
statement.setString(1, begin); // fill in boundries
statement.setString(2, end);
statement.setInt(3, dateId); // fill in dateId
statement.setInt(4, typeId); // fill in type id
// Fill in the given parameters depending on type, add custom types here incase needed!
if (type == Type.COLLECTION) {
int col = Integer.parseInt(param);
statement.setInt(5, col);
} else if (type == Type.AUTHOR) {
int id = Item.returnId(context, "contributor", "author");
statement.setInt(5, id);
statement.setString(6, param);
}
ResultSet rs = statement.executeQuery();
int i = 0;
while (rs.next()) {
ids.add(rs.getInt("item_id"));
}
return ids;
}
// Generate the html for a given Type and a given parameter (for example: collection id)
public static String GenerateHTML(
Context context, Type type, String param, String contextPath, String beginY, String endY)
throws SQLException {
// Pickup the metadata_field_id for a given element and a qualifier (from the
// metadatafieldregistry)
int autId = Item.returnId(context, "contributor", "author");
int citId = Item.returnId(context, "identifier", "citation");
int titleId = Item.returnId(context, "title", "");
int dateId = Item.returnId(context, "date", "issued");
int typeId = Item.returnId(context, "type", "");
boolean stop = false;
String typ = "";
// Get a vector with the IDs of the requested items
List<Integer> ids = GetIds(context, dateId, type, param, beginY, endY);
// actual HTML string
String result = "";
int currentYear = 0;
// Construct the html string
for (int i = 0; i < ids.size(); ++i) {
String auth = "";
// pickup the metadata from the DB
List<String> authors = Item.latestAdditionsText(context, ids.get(i), autId, 0);
List<String> citations = Item.latestAdditionsText(context, ids.get(i), citId);
List<String> titles = Item.latestAdditionsText(context, ids.get(i), titleId);
List<String> dates = Item.latestAdditionsText(context, ids.get(i), dateId);
List<String> types = Item.latestAdditionsText(context, ids.get(i), typeId);
int year = getYear(dates.get(0));
// incase this items year differs from the last one, display a new year header
if (year != currentYear) {
currentYear = year;
if (year != 0) {
result += "</ul>";
}
result +=
"<h2 class=\"dateItem\" style=\"cursor: pointer;\" onclick=\"Effect.toggle('"
+ year
+ "', 'blind')\">"
+ year
+ "</h2><ul id=\""
+ year
+ "\">";
}
// incase this items type differs from the last one, display a new type header
if (!typ.equals(types.get(0))) {
typ = types.get(0);
result += "<li><h3>" + typ + "</h3></li>";
}
// display all authors
for (int j = 0; j < authors.size() && !stop; ++j) {
if (j < (authors.size() - 1)) {
auth +=
"<a href=\""
+ contextPath
+ "/browse?type=author&value="
+ Item.latestAdditionsText(context, ids.get(i), "contributor", "author").get(0)
+ "\">"
+ Item.latestAdditionsText(context, ids.get(i), "contributor", "author").get(0)
+ "</a>; ";
} else if (authors.get(j) == null) {
stop = false;
} else {
auth +=
"<a href=\""
+ contextPath
+ "/browse?type=author&value="
+ Item.latestAdditionsText(context, ids.get(i), "contributor", "author").get(0)
+ "\">"
+ Item.latestAdditionsText(context, ids.get(i), "contributor", "author").get(0)
+ "</a> ";
}
}
// display the other important metadata
result +=
"<ul class=\"collectionListItem\"><li class=\"metadataFieldValue\" style=\"list-style-type: none;\">"
+ auth
+ " ("
+ year
+ ") <em>"
+ "<a href=\""
+ contextPath
+ "/handle/"
+ Item.getHandleMod(context, ids.get(i))
+ "\"/>"
+ titles.get(0)
+ "</a><br/>"
+ "</em>"
+ citations.get(0)
+ "</li></ul>";
}
// close the year tag!
if (currentYear != 0) {
result += "</ul>";
}
return result;
}