From f99a615ef7ca05d8cca71a666620cbfc20735386 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Fri, 12 Jul 2019 11:36:17 -0400 Subject: [PATCH] Adding Tiffany's PO deletion script. --- ...te_empty_purchase_orders_marked_for_deletion.sh | 24 ++++++++++++++++++++++ 1 file changed, 24 insertions(+) create mode 100755 sql/delete_empty_purchase_orders_marked_for_deletion.sh diff --git a/sql/delete_empty_purchase_orders_marked_for_deletion.sh b/sql/delete_empty_purchase_orders_marked_for_deletion.sh new file mode 100755 index 0000000..6392a60 --- /dev/null +++ b/sql/delete_empty_purchase_orders_marked_for_deletion.sh @@ -0,0 +1,24 @@ +#!/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="mydbuser" +DB_HOST="mydbhost" +read -r -d '' SQL << 'EOF' +DELETE FROM acq.purchase_order +WHERE id IN (SELECT po.id FROM acq.purchase_order po + LEFT JOIN acq.lineitem li ON (li.purchase_order = po.id) + LEFT JOIN acq.po_note note ON (note.purchase_order = po.id) + LEFT JOIN acq.po_item poi on (poi.purchase_order = po.id) +WHERE li.id IS NULL + AND poi.id IS NULL + AND note.id IS NULL + AND po.state = 'pending' + AND po.name ILIKE '%delete%') +EOF + +#do the thing + +$PSQL -U $DB_USER -h $DB_HOST -1 -c "$SQL" -- 2.11.0