#!/bin/bash

#This script is to delete any purchase orders that are pending, named delete, and have no line items or direct charges.

#set up the environment
PSQL="/usr/bin/psql"
DB_USER="evergreen"
DB_HOST="db01"
read -r -d '' SQL << 'EOF'
DELETE FROM acq.purchase_order po
        LEFT JOIN acq.lineitem li ON (li.purchase_order = po.id)
        LEFT JOIN acq.po_item poi on (poi.purchase_order = po.id)
WHERE   acq.lineitem.id IS NULL
        AND po.state = 'pending'
        AND poi.id IS NULL
        AND po.name ILIKE '%delete%'
EOF

#do the thing

#$PSQL -U $DB_USER -h $DB_HOST -1 -c "$SQL"
echo $SQL