grocery-manager/grocery_transactions.sh

#!/bin/bash
#
# Copyright (c) Daniel Sheffield 2021
#
# All rights reserved
#
# THIS SOFTWARE IS PROVIDED AS IS WITHOUT WARRANTY
set -euo pipefail
ENDKW=ROLLBACK
DATE="$(date +%Y-%m-%d)"
STORE=" IS NOT NULL"
usage(){
    cat <<EOF
Usage: ./${0##*/} [ -d DATE ] [ -s STORE ] [ -r | -c ] < transactions.txn
 
Companion script for grocery_transactions.py

Reads transactions from stdin.
ROLLBACK (-r) is enabled by default
Date is optional and by default is set to current day.
Date and store only affect the display of the transactions.
If store is not set, then all stores are shown for that day.

Example:
    # load generated transactions with ROLLBACK
    ./${0##*/} -d DATE -s STORE < transactions.txn
    ./${0##*/} -d DATE -s STORE -r < transactions.txn
    
    # load generated transactions with COMMIT
    ./${0##*/} -d DATE -s STORE -c < transactions.txn
EOF
}
while getopts ":crd:s:" options; do
  case "${options}" in         
    c)                          
      ENDKW=COMMIT               
      ;;
    r)
      ENDKW=ROLLBACK
      ;;
    d)
      DATE="${OPTARG}"
      ;;
    s)
      STORE=" = \$store\$${OPTARG}\$store\$"
      ;;
    :)             
      ;;
    *) 
      usage 1>&2
      ;;
  esac
done

if [[ -t 0 ]]
then
    usage 1>&2
    exit 1
fi
if [ -f db_credentials.py ]; then 
    . db_credentials.py
    HOST="-h ${HOST}"
    PASSWORD=
else
    HOST=
    PASSWORD=
fi

psql ${HOST} -d grocery -U ${USER} <<EOF
BEGIN;
$(cat - )
SELECT
  date_part('day',ts)||'/'||date_part('month',ts)||' '||date_part('hour',ts)::int%12 AS ts,
  code AS shop,
  substr(description,1,32) AS description,
  quantity AS volume,
  substr(unit,1,4) AS unit,
  price,
  TRUNC(price/quantity,4) AS "\$/unit",
  sum(transaction_view.price) OVER (PARTITION BY transaction_view.ts::date ORDER BY transaction_view.ts, transaction_view.description ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total,
  substr(product,1,10) AS product, substr(category,1,8) AS category, substr("group",1,9) AS "group", organic AS og
FROM transaction_view
WHERE ts BETWEEN '${DATE}'::date AND '${DATE}'::date + '23 hours 59 minutes 59 seconds'::interval
  AND store ${STORE};
${ENDKW};
EOF