Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
./monthly-sales.sh `date --date "-1 month" +%Y-%m-01` `date +%Y-%m-01` > monthly-sales-`date --date "-1 month" +%Y-%m`.csv
# m h dom mon dow command
1 0 1 * * /home/openfoodnetwork/create-montly-csv.sh
#!/bin/sh
#
# I can execute this:
#
# ssh ofn-prod './monthly-sales.sh `date --date "-1 month" +%Y-%m-01` `date +%Y-%m-01`' > "OFN-monthly-sales-`date +%Y-%m`.csv"
#
if [ $# -lt 2 ]; then
echo >&2 "Usage: $0 2018-09-01 2018-10-01"
exit 1
fi
from="$1"
to="$2"
psql -h localhost -U ofn_user openfoodnetwork -A -F"," -c "select distributor_id, enterprises.name, enterprises.contact_name, enterprises.email_address, enterprises.phone, replace(spree_addresses.address1, ',', ' ') address1, replace(spree_addresses.address2, ',', ' ') address2, spree_addresses.city, spree_addresses.zipcode, spree_addresses.state_name, count(spree_orders.id) orders, sum(spree_orders.total) distributor_total from spree_orders join enterprises on distributor_id=enterprises.id join spree_addresses on enterprises.address_id=spree_addresses.id where (spree_orders.completed_at >= timestamp '$from' at time zone 'Australia/Melbourne' at time zone 'UTC' and spree_orders.completed_at < timestamp '$to' at time zone 'Australia/Melbourne' at time zone 'UTC') and state = 'complete' group by distributor_id, enterprises.name, enterprises.contact_name, enterprises.email_address, enterprises.phone, address1, address2, city, zipcode, state_name having sum(spree_orders.total) > 0 order by enterprises.name"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.