#!/usr/bin/python

from pysqlite2 import dbapi2 as sqlite
from sys import exit
import datetime

def connectdb (dbfile):
  """
  Create connection to database
  return DB Object
  user gets information if failure
  """
  try:
    db = sqlite.connect (dbfile) 
    return db
  except:
    print ("Couldnt connct to DB")
    exit (1)


class DB:
  def __init__ (self, dbfile):
    self.db = connectdb (dbfile)


  def executesql (self, dbstring, arguments = ()):
    """
    exectutes sql in dbstring
    and returns cursor
    """
    try:
      cursor = self.db.cursor ()
      cursor.execute (dbstring, arguments)
    except sqlite.OperationalError:
      print ("SQL exectution failed. Caused by: " + dbstring)
      raise Warning
    except sqlite.IntegrityError:
      print ("Double entry. Caused by: " + dbstring)
      raise Warning
    except sqlite.ProgrammingError:
      print ("Invalid SQL: " + dbstring)
      raise Warning
    except sqlite.Warning:
      pass

    self.db.commit ()
    return cursor


  def geterrorlog (self):
    """
    returns all errorlogs 
    """

    try:
      cursor = self.executesql ("SELECT timestamp, errornr, count, action FROM errorlog")
    except:
      print ("couldn't get Errors")
      raise Warning
   
    return cursor.fetchall ()



  def getallmsglogs (self):
    """
    returns all msglog entrys
    """

    try:
      cursor = self.executesql ("SELECT m.id, c.client, l.location, i.iopoint, ms.msg, m.timestamp FROM msglog m \
	                         LEFT JOIN clients c  ON (c.ID = m.client) \
				 LEFT JOIN locations l ON (l.id = m.location) \
				 LEFT JOIN iopoints i ON (i.id = m.iopoint) \
				 LEFT JOIN messages ms ON (ms.id = m.msg)")
    except:
      print ("couldn't get msglog")
      raise Warning
   
    return cursor.fetchall ()

 

  def logmsg (self, timestamp, errornr, count, action):
    """
    save errormsg
    """
    actions = { 1 : 'Entstanden',
		2 : 'Verschwunden nach SW Quittierung',
		3 : 'Verschwunden ohne Quittierung',
		4 : 'HW Quittierung',
		5 : 'SW Quittierung',
		6 : 'Auf Wartung gesetzt',
		7 : 'Auf Wartung gesetzt',
		8 : 'aus Wartung rueckgesetzt',
		9 : 'Behoben'
		}
    atext = 'undefiniert'
    if action in actions:
      atext = actions[action] 
    try:
      cursor = self.executesql ("INSERT INTO errorlog VALUES ('%s', %d, %d, '%s')" %(timestamp, errornr, count, atext))
    except:
      print ("couldn't insert logmsg")
      raise Warning
      

  def logvalue (self, client, location, iopoint, value, dimension, timestamp):
    """
    saves a valuelog
    """

    try:
      cursor = self.executesql ("INSERT INTO valuelog VALUES (null, %d, %d, %d, %d, %d, '%s')" %(client, location, iopoint, value, dimension, timestamp))
    except:
      print ("couldn't insert valuelog")
      raise Warning
 

if __name__ == "__main__":
  conn = DB ("test.db3")

  print ("Error Logs")
  print ("-------------")
  for i in  conn.geterrorlog ():
    print (i)
  
  #conn.logvalue ( client = 1, location = 2, iopoint = 3, value = 4623, dimension = 1, timestamp = datetime.datetime(2007, 7, 6, 2, 2))