Monthly Reports

Monthly Update of Cumulative Deposits
Create new graphs. See Curator Report Technology page, section Graphing Dryad submissions over time.

In the dryad-data GitHub repository:
 * Add the updated dryadSubmitDates.txt
 * Manually edit the dataPackagesInDryad.csv file to include the new month.

Monthly Shopping Cart Report
This report is being automated. The current script is in https://github.com/datadryad/dryad-data/tree/master/monthlyReporting/monthly-report.sh, but it is incomplete.

On production, export CSVs using commands below: \copy (select * from shoppingcart, item, metadatavalue where shoppingcart.item=metadatavalue.item_id and shoppingcart.item=item.item_id and item.in_archive=TRUE and metadatavalue.metadata_field_id=11) to 'shoppingArchived.csv' with CSV;

\copy (select * from shoppingcart, item where shoppingcart.item=item.item_id and item.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 'shoppingBlackout.csv' with CSV;

\copy (select * from shoppingcart, item where shoppingcart.item=item.item_id and item.item_id in (select item_id from workflowitem where workflow_id in (select workflow_item_id from taskowner where owner_id = 949))) to 'shoppingFakeBlackout.csv' with CSV;

Then:
 * 1) Combine the files shoppingArchived and shoppingBlackout into shoppingCharged.
 * 2) Remove rows for any items that were included in previous reports
 * 3) filter with the script and data in https://github.com/datadryad/dryad-data/tree/master/monthlyReporting
 * 4) Import filtered file (shoppingChargedFiltered.csv) into google spreadsheet.
 * 5) Copy/paste headings from previous report.
 * 6) Remove three columns before the journal name -- journal names should line up with the headings.
 * 7) Remove three columns before the final colum -- the final colum should be the date.
 * 8) Filter spreadsheet to remove items that have a payment date beyond the correct month. (In the future, this should be accomplished by restricting the Postgres queries, as long as all carts now have dates.)
 * 9) After filtering is complete, copy the first column (cart ID) into https://github.com/datadryad/dryad-data/tree/master/monthlyReporting/shoppingCartIdsSeenInReports.txt. Sort this file numerically (sort -n). Push the changes to github.
 * 10) Select the entire spreadsheet. sort by journal, subsort by archive_date.