import cx_Oracle
import os
import datetime
from time import localtime, strftime, strptime
import ConfigParser


# check-oracle-tbs.py
# 
# to cx_Oracle work, you need:
# 1. set LIBRARY_PATH correctly
# 2. configure yaml file
# 3. echo /u01/app/oracle/product/11.2.0.3/db/lib > /etc/ld.so.conf.d/oracle.conf 
# ldconfig


def __run__(params):
  conn = None

  cf = ConfigParser.ConfigParser()
  config_file = params.get('config_file')
  cf.readfp(open(config_file))

  user = cf.get('oracle', 'user')
  password = cf.get('oracle', 'passwd')
  oracle_home = cf.get('oracle', 'oracle_home')
  pct_critical = cf.getint('oracle', 'pct_critical')
  pct_warning = cf.getint('oracle', 'pct_warning')
  instances = cf.get('oracle','instances')

  os.environ['ORACLE_HOME'] = oracle_home
  try:
    instances = instances.split();
    check_message = ''
    criticals = 0
    warnings = 0
    for instance in instances:
      ctl = '/tmp/tbs_' + instance +'.log'
      buf = '/tmp/tbs_' + instance +'.buf'

      crit = '/tmp/tbs_' + instance +'.crit'
      warn = '/tmp/tbs_' + instance +'.warn'

      if os.path.isfile(ctl):
        file = open(ctl,'r')
        ultimo = file.read()
        file.close()

        now = datetime.datetime.now()
        data = now.strftime("%Y-%m-%d %H:%M:%S")
        dt_last_check = datetime.datetime.strptime(ultimo,"%Y-%m-%d %H:%M:%S")
        diferenca = now - dt_last_check
        if (diferenca >  datetime.timedelta(minutes=30) ):

          file = open(ctl,"w")
          file.write(data)
          file.close()

          os.environ['ORACLE_SID'] = instance
          conn = cx_Oracle.connect(user, password, mode=cx_Oracle.SYSDBA)
          cursor = conn.cursor()
          sql = """SELECT df.tbspace, (100*fs.livre/df.total) pct_free, fs.livre/1024/1024 livre
                 FROM (SELECT tablespace_name tbspace, SUM(BYTES) total
                    FROM sys.dba_data_files
                    where tablespace_name not like '%UNDO%'
                    GROUP BY tablespace_name) df,
                    (SELECT tablespace_name tbspace, SUM(BYTES) livre
                    FROM sys.dba_free_space
                    where tablespace_name not like '%UNDO%'
                    GROUP BY tablespace_name) fs
                 WHERE df.tbspace = fs.tbspace(+) order by 1, 2"""
          cursor.execute(sql)
          result = cursor.fetchall()
          rowcount = cursor.rowcount
          cursor.close()
          if rowcount == 0:
            check_message = check_message + '(!!) Error to fetch tablespaces'
            criticals = criticals + 1
          else:
            for row in result:
              grp_name = row[0]
              pct_free = row[1]
              size_free = row[2]
              if pct_free <= pct_critical :
                criticals = criticals + 1
                check_message = check_message + '(C)' + grp_name + ' Livre(' + str("%.2f" % pct_free) + '%/' + str("%u" % size_free) + 'MB)\\n'
              elif pct_free <= pct_warning:
                warnings = warnings + 1
                check_message = check_message +  '(W)' + grp_name + ' Livre(' + str("%.2f" % pct_free) + '%/' + str("%u" % size_free) + 'MB)\\n'
              else:
                check_message = check_message + grp_name + ' Livre(' + str("%.2f" % pct_free) + '%/' + str("%u" % size_free) + 'MB)\\n'

          file = open(buf,"w")
          file = open(buf,"w")
          file.write(check_message)
          file.close()



          if os.path.isfile(crit):
            os.remove(crit)

          if os.path.isfile(warn):
            os.remove(warn)

          if criticals > 0:
            file = open(crit,"w")
            file.write(criticals)
            file.close()
          elif warnings > 0:
            file = open(warn,"w")
            file.write(warnings)
            file.close()


      else:
        now = datetime.datetime.now()
        #data = now.strftime("%Y-%m-%d %H:%M:%S")
        file = open(ctl,"w")
        data_exp = now - datetime.timedelta(minutes = 30)
        data_exp = data_exp.strftime("%Y-%m-%d %H:%M:%S")
        file.write(data_exp)
        file.close()

    final_message = ''
    has_critical = 0
    has_warning = 0

    for instance in instances:
      ctl = '/tmp/tbs_' + instance +'.log'
      buf = '/tmp/tbs_' + instance +'.buf'

      crit = '/tmp/tbs_' + instance +'.crit'
      warn = '/tmp/tbs_' + instance +'.warn'

      if os.path.isfile(crit):
        if os.path.isfile(buf):
          file = open(buf,'r')
          mensagem= file.read()
          file.close()
          final_message = final_message + '(!!) Existem tablespaces na instancia ' + str(instance) + ' em estado critico!\\n' + mensagem + '\\n'
          has_critical = 1

      elif os.path.isfile(warn):
        if os.path.isfile(buf):
          file = open(buf,'r')
          mensagem= file.read()
          file.close()
          final_message = final_message + '(!) Existem tablespaces na instancia ' + str(instance) + ' em estado warning!\\n' + mensagem + '\\n'
          has_warning = 1
      else:
        if os.path.isfile(buf):
          file = open(buf,'r')
          mensagem= file.read()
          file.close()
          final_message = final_message + 'Tablespaces da instancia ' +  str(instance) + ' em estado normal.\\n' + mensagem + '\\n'
    
    if has_critical <> 0:
      final_message = ' Existem tablespaces em estado critico. \\n' + final_message
      return [2, str(final_message) ]
    elif has_warning <> 0:
      final_message = ' Existem tablespaces em estado warning. \\n' + final_message
      return [1, str(final_message) ]
    else:
      final_message = 'Todas tablespaces em todas instancias em estado normal. \\n' + final_message
      return [0, str(final_message) ]
 
  except Exception, e:
    return [2, '{0}'.format(str(e))]