#!/usr/bin/python
'''docasap_monitor.py
'''

import sys
import platform
import cx_Oracle
import time
import smtplib
import os
from pprint import pprint

# Set some defaults - maybe need config file or more arguments?
_DB_CON = None
JYTHON = 0
db_username = '<password>'
db_password = '<password>'
tnsname = sys.argv[1]

GRAB_SQL_INFO = """select name, "MAX_DATAFILES", "CURRENT_#_DATAFILES", round(("CURRENT_#_DATAFILES"/"MAX_DATAFILES")*100) "Percent_Used" from (select v.name, v.value "MAX_DATAFILES", max(df.FILE_ID) "CURRENT_#_DATAFILES" from v$parameter v, dba_data_files df where v.name='db_files' group by v.name, v.value)"""
GRAB_DB_INFO = """select name from v$database"""


# Establish connection
def est_db_con (username, password, tnsname):
	global _DB_CON
#    if not _DB_CON:
	dbinfo = username
	try:
		dbinfo = 'db: %s@%s' % (username, tnsname)
		print('--'+dbinfo)
		_DB_CON = cx_Oracle.connect(username, password, tnsname)
		print ('--cx_Oracle version: ' + _DB_CON.version),
	except:
		print '\n###### Database Connection Info ######'
		print ('Python version: ' + platform.python_version())
		ex = sys.exc_info()
		s = 'Exception: %s: %s\n%s' % (ex[0], ex[1], dbinfo)
		print(s)
		return None
	return _DB_CON

# Alias the database connection
def db_con():
    return _DB_CON

def query_to_dict(sql):
    est_db_con (db_username, db_password, tnsname)
    cur = db_con().cursor()
    print "EXECUTE_SQL:"+sql
    cur.execute(sql)
    columns = [i[0] for i in cur.description]
    results = [dict(zip(columns, row)) for row in cur]
    return results
    cur.close()

def add_db_info(sql):
    cur = db_con().cursor()
    print "PROCESS_SQL:"+sql
    try:
        cur.execute(sql)
        sql_output = cur.fetchone()
        result = next(iter(sql_output))
        cur.close()
        return result
    except cx_Oracle.DatabaseError, exc:
        error, = exc.args
        print >> sys.stderr, "Oracle-Error-Code:", error.code
        print >> sys.stderr, "Oracle-Error-Message:", error.message
        cur.close()


def alert():
	for item in db_files_results:
		if db_files_results[0].get("Percent_Used") > 50:
			f = open('test_file', 'w')
			f.write(' '.join('{0}: {1} \n'.format(key, val) for key, val in db_files_results[0].items()))
			f.close()
			os.system("mailx -s '{0} DB_FILES WARNING' xxxxxx.xxxxxx@xxxxxx.com < /home/oracle/test_file".format(db_files_results[0]["db_name"]))
			print("email sent")
		else:
			print("script finished")

db_files_results = query_to_dict(GRAB_SQL_INFO)

db_files_results[0]["db_name"] = add_db_info(GRAB_DB_INFO)

alert()