\copy (select distinct text_value from metadatavalue where metadata_field_id =77 and item_id IN (select item_id from item where in_archive=TRUE and item_id IN (select item_id from collection2item where collection_id=2))) to '/home/dryad/currentJournals.txt' with CSV;

List of all journals associated with items in publication blackout:

\copy (select text_value from metadatavalue where metadata_field_id =77 and item_id in (select item_id from metadatavalue where text_value like '%Entered publication blackout%' and item_id not in (select item_id from metadatavalue where text_value like '%Approved for entry into archive%'))) to '/home/dryad/allJournalsBlackout.txt' with csv;

Graphing Dryad submissions over time

postgres-client.sh -c "select text_value from metadatavalue where metadata_field_id=12 and item_id in (select item_id from item where owning_collection=2 and in_archive='t');" > dryadSubmitDates.txt

(collection 2 is "Data Packages" and metadata field 12 is "Date Available")

Edit the file to remove the timestamps (leave only the date portion).

Sort the dates.

Import the dryadSubmitDates.txt into Excel.

Create a column beside the dates that starts at 1 and counts up (this represents the total number of submissions present on each day).

Create a graph of these two columns.

Items in publication blackout

Items in blackout are owned by "Dryad Queue". The queue's ePerson ID is 949.

select item_id from workflowitem where workflow_id IN (select workflow_item_id from taskowner where owner_id=949);

To get metadata about items in the blackout, you must select the metadata directly from the database, one field at a time. Below is a query to select metadata field 17 (DOI). When performing these types of queries, always order by item_id to keep the ordering of the metadata values consistent across queries.
select text_value from metadatavalue where metadata_field_id=17 and item_id IN (select item_id from workflowitem where workflow_id IN (select workflow_item_id from taskowner where owner_id=949)) ORDER BY item_id;

Submissions from a particular instituiton

This query will identify archived data packages that are associated with a particular email suffix. Note that this undercounts the number of deposits from an institution, since around 25% of Dryad user create accounts with a generic email address (e.g., GMail).

select item_id from item where submitter_id IN (select eperson_id from eperson where email like '%TARGET_INSTITUTION_EMAIL_SUFFIX') and in_archive='t' and item_id IN (select item_id from collection2item where collection_id=2) order by last_modified;

Item Metadata

Items That Have Been Paid, but are Incorrectly in Reauthorization

select item_id from workflowitem, taskowner, shoppingcart where workflowitem.workflow_id = taskowner.workflow_item_id and (taskowner.step_id like 'reAuthorizationPaymentStep' or taskowner.step_id like 'pendingPublicationReAuthorizationPaymentStep') and workflowitem.item_id=shoppingcart.item and shoppingcart.status like 'completed';