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