We had a bit of an incident today. It seems that some of the staff get a bit flustered when they make a mistake in entering transactions. There seems to be consistent misunderstanding about the difference between a credit and a refund etc. As a result, we got such a mess in the transaction history that I decided to attempt to delete the transactions.

To do that, I wrote a python script. Looking to get some review on this, and also share it w/ the world out there in case you need such a facility. Bear in mind that this was very quick and dirty... so looking for critique on the SQL more than anything else.

NOTE: This script is too aggresive right now.... deletes too much data, so I don't recommend using it as is. :)

#!/usr/bin/python
import MySQLdb
import sys

#-------------------------------------------------------------------------------
# Database access

def execute(c,sql):
    sys.stderr.write(sql+'\n')
    c.execute(sql)

def delete_act(conn,act_id):
    print "-- deleteing act: %d"%act_id
    c = conn.cursor()
    # Find the act_id and get associated acts
    # This is effectively recursive if the act has many associated acts
    sql = 'select act_relationship_id,target_id from act_relationships where source_id=%d'%act_id
    sys.stderr.write(sql+'\n')
    c.execute(sql)
    results=c.fetchall()
    for row in results:
        # Kill the downstream act
        delete_act(conn,int(row[1]))
        # Kill act_relationship details
        sql = 'delete from act_relationship_details where act_relationship_id=%d'%row[0]
        execute(c,sql)
    # Kill the act_relationships
    sql = 'delete from act_relationships where source_id=%d'%act_id
    execute(c,sql)
    # Go get the next round where the target_id is referenced
    sql = 'select act_relationship_id from act_relationships where target_id=%d'%act_id
    sys.stderr.write(sql+'\n')
    c.execute(sql)
    results=c.fetchall()
    for row in results:
        # Kill the details
        sql = 'delete from act_relationship_details where act_relationship_id=%d'%row[0]
        execute(c,sql)
    # Kill the act_relationships
    sql = 'delete from act_relationships where target_id=%d'%act_id
    execute(c,sql)
    # Kill the act_details
    sql = 'delete from act_details where act_id=%d'%act_id
    execute(c,sql)
    # Kill participations
    sql = 'delete from participations where act_id=%d'%act_id
    execute(c,sql)
    # Kill the act
    sql = 'delete from acts where act_id=%d'%act_id
    sys.stderr.write(sql+'\n')
    execute(c,sql)
    # Kill the financial transaction
    sql = 'delete from financial_acts where financial_act_id=%d'%act_id
    execute(c,sql)
    c.close()    
    
def delete_txn(txn_id):
    try:
        connection = MySQLdb.connect('localhost','root','','openvpms')
        print "deleting..."
        delete_act(connection,txn_id)
        connection.commit()
    except Exception,e:
        print "!!! exception %s"%e
        connection.rollback()
    connection.close()
    
if __name__=='__main__':
    if len(sys.argv) < 2: print("Please specify transaction id")
    else:
        txn_id=int(sys.argv[1])
        print(txn_id)
        delete_txn(txn_id)
        print("deleted")