@Override
public List<Item> getItems(MetadataValue value, Date startDate, Date endDate) {
// FIXME: Of course, this should actually go somewhere else
boolean oracle = false;
if ("oracle".equals(ConfigurationManager.getProperty("db.name"))) {
oracle = true;
}
// FIXME: this method is clearly not optimised
String valueQuery = null;
if (value != null) {
valueQuery =
"SELECT item_id FROM metadatavalue "
+ "WHERE metadata_field_id = ? "
+ "AND text_value LIKE ?";
// "AND metadata_field_id = (" +
// "SELECT metadata_field_id FROM metadatafieldregistry " +
// "WHERE element = ? AND qualifier = ?" +
// "AND metadata_schema_id = ?" +
// ")";
}
// start the date constraint query buffer
StringBuffer dateQuery = new StringBuffer();
// FIXME: This is a little DC-specific, but I suppose that's OK.
dateQuery.append(
"SELECT item_id FROM metadatavalue "
+ "WHERE metadata_field_id = ("
+ "SELECT metadata_field_id "
+ "FROM metadatafieldregistry "
+ "WHERE element = 'date' "
+ "AND qualifier = 'accessioned' "
+ ")");
if (startDate != null) {
if (oracle) {
dateQuery.append(
" AND TO_TIMESTAMP( TO_CHAR(text_value), "
+ "'yyyy-mm-dd\"T\"hh24:mi:ss\"Z\"' ) > TO_DATE('"
+ unParseDate(startDate)
+ "', 'yyyy-MM-dd') ");
} else {
dateQuery.append(
" AND text_value::timestamp > '" + unParseDate(startDate) + "'::timestamp ");
}
}
if (endDate != null) {
if (oracle) {
dateQuery.append(
" AND TO_TIMESTAMP( TO_CHAR(text_value), "
+ "'yyyy-mm-dd\"T\"hh24:mi:ss\"Z\"' ) < TO_DATE('"
+ unParseDate(endDate)
+ "', 'yyyy-MM-dd') ");
} else {
dateQuery.append(" AND text_value::timestamp < '" + unParseDate(endDate) + "'::timestamp ");
}
}
// build the final query
StringBuffer query = new StringBuffer();
query.append(
"SELECT item_id FROM item "
+ "WHERE in_archive = "
+ (oracle ? "1 " : "true ")
+ "AND withdrawn = "
+ (oracle ? "0 " : "false "));
if (startDate != null || endDate != null) {
query.append(" AND item_id IN ( " + dateQuery.toString() + ") ");
}
if (value != null) {
query.append(" AND item_id IN ( " + valueQuery + ") ");
}
try {
TableRowIterator tri = null;
if (value == null) {
tri = DatabaseManager.query(context, query.toString());
} else {
tri =
DatabaseManager.query(context, query.toString(), value.getFieldID(), value.getValue());
}
return returnAsList(tri);
} catch (SQLException sqle) {
throw new RuntimeException(sqle);
}
}
