#!/usr/bin/env python

import sys
import gtk
import MySQLdb as mdb
	
class ConfDBBrowser:

	#----------------------------
	# quit_program
	#----------------------------
	def quit_program(self, widget=None, data=None):
		gtk.main_quit()

	#----------------------------
	# __init__
	#----------------------------
	def __init__(self):

		builder = gtk.Builder()
		builder.add_from_file("confdb_gui.xml") 

		self.mainwindow = builder.get_object("mainwindow")
		self.db_connect_parms_dialog = builder.get_object("db_connect_parms_dialog")
		self.dbmodify = builder.get_object("dbmodify")

		self.dbhost_label = builder.get_object("dbhost")
		self.dbuser_label = builder.get_object("dbuser")
		self.dbname_label = builder.get_object("dbname")

		self.dbhost_entry = builder.get_object("dbhost_entry")
		self.dbuser_entry = builder.get_object("dbuser_entry")
		self.dbname_entry = builder.get_object("dbname_entry")
		self.dbpassword_entry = builder.get_object("dbpassword_entry")
		self.dbpassword = '' # this should probably come from the XML file
		
		self.SelectAuthorDialog = builder.get_object("SelectAuthorDialog")
		self.author = None
		self.author_id = ''
		self.author_cb = builder.get_object("author_cb")
		self.author_ls = builder.get_object("author_ls")
		self.new_author_name = builder.get_object("new_author_name")
		self.new_author_email = builder.get_object("new_author_email")

		self.boardview = builder.get_object("boardview")
		self.boardsdb = builder.get_object("boardsdb")
		
		self.board_info_id = builder.get_object("board_info_id")
		self.board_info_type = builder.get_object("board_info_type")
		self.board_info_serial = builder.get_object("board_info_serial")
		self.board_info_property = builder.get_object("board_info_property")
		self.board_info_creator = builder.get_object("board_info_creator")
		self.board_info_status = builder.get_object("board_info_status")
		self.board_info_status_time = builder.get_object("board_info_status_time")
		self.board_info_location = builder.get_object("board_info_location")
		
		self.remove_board_comment = builder.get_object("remove_board_comment")
		
		self.boardtype_cb = builder.get_object("boardtype_cb")
		self.location_cb = builder.get_object("location_cb")
		self.boardtype_ls = builder.get_object("boardtype_ls")
		self.location_ls = builder.get_object("location_ls")
		self.serial_number_entry = builder.get_object("serial_number_entry")
		self.jlab_prop_control_entry = builder.get_object("jlab_prop_control_entry")
		self.show_deleted_boards = builder.get_object("show_deleted_boards")

		builder.connect_signals(self)
		
		self.dbconnected = self.connect_to_db()
		self.update_boards_list_from_db()

	#----------------------------
	# connect_to_db
	#----------------------------
	def connect_to_db(self):
		try:
			dbhost = self.dbhost_label.get_text()
			dbuser = self.dbuser_label.get_text()
			dbname = self.dbname_label.get_text()
			dbpassword = self.dbpassword
			self.con = mdb.connect(dbhost, dbuser, dbpassword , dbname)
			print "Connected to %s on %s " % (dbname, dbhost)
			return True

		except mdb.Error, e:
			print "Error %d: %s " % (e.args[0], e.args[1])
			return False

	#----------------------------
	# edit_db_connection_parameters
	#----------------------------
	def edit_db_connection_parameters(button, dialog):
		if dialog.flags() & gtk.MAPPED:
			dialog.present()
		else:
			browser.dbhost_entry.set_text(browser.dbhost_label.get_text())
			browser.dbuser_entry.set_text(browser.dbuser_label.get_text())
			browser.dbname_entry.set_text(browser.dbname_label.get_text())
			browser.dbpassword_entry.set_text(browser.dbpassword)
			dialog.show()
	
	#----------------------------
	# edit_db_connection_parameters_apply
	#----------------------------
	def edit_db_connection_parameters_apply(button, dialog):
		browser.dbhost_label.set_text(browser.dbhost_entry.get_text())
		browser.dbuser_label.set_text(browser.dbuser_entry.get_text())
		browser.dbname_label.set_text(browser.dbname_entry.get_text())
		browser.dbpassword = browser.dbpassword_entry.get_text()
		dialog.hide()
		if not browser.connect_to_db(): browser.dbmodify.clicked()
	
	#----------------------------
	# edit_db_connection_parameters_close
	#----------------------------
	def edit_db_connection_parameters_close(button, dialog):
		dialog.hide()

	#----------------------------
	# get_board_types
	#----------------------------
	def get_board_types(self):
		cur = self.con.cursor()
		cur.execute('SELECT name FROM board_type ORDER BY name')
		data = cur.fetchall()
		types = []
		for s in data: types.append(s[0])
		return types 

	#----------------------------
	# get_locations
	#----------------------------
	def get_locations(self):
		cur = self.con.cursor()
		cur.execute('SELECT location FROM locations ORDER BY created')
		data = cur.fetchall()
		locations = []
		for s in data: locations.append(s[0])
		return locations 

	#----------------------------
	# get_board_statuses
	#----------------------------
	def get_board_statuses(self):
		cur = self.con.cursor()
		cur.execute('SELECT status FROM board_status GROUP BY status ORDER BY status')
		data = cur.fetchall()
		statuses = []
		for s in data: statuses.append(s[0])
		return statuses 

	#----------------------------
	# get_location_id
	#----------------------------
	def get_location_id(self, board_id):
		cur = self.con.cursor()
		sql = "SELECT location_id FROM boards,board_status"
		sql += " WHERE id=board_id AND board_id=%s" % board_id
		sql += " ORDER BY board_status.modified DESC LIMIT 1"
		cur.execute(sql)
		row = cur.fetchone()
		if row==None: return "-1"
		return "%d" % row[0]

	#----------------------------
	# get_location_id_from_name
	#----------------------------
	def get_location_id_from_name(self, location):
		cur = self.con.cursor()
		sql = "SELECT id FROM locations"
		sql += " WHERE location='%s'" % location
		cur.execute(sql)
		row = cur.fetchone()
		if row==None: return "-1"
		return "%d" % row[0]

	#----------------------------
	# get_location_name_from_id
	#----------------------------
	def get_location_name_from_id(self, location_id):
		cur = self.con.cursor()
		sql = "SELECT location FROM locations"
		sql += " WHERE id='%s'" % location_id
		cur.execute(sql)
		row = cur.fetchone()
		if row==None: return "-1"
		return row[0]

	#----------------------------
	# get_type_id_from_name
	#----------------------------
	def get_type_id_from_name(self, board_type):
		cur = self.con.cursor()
		sql = "SELECT id FROM board_type"
		sql += " WHERE name='%s'" % board_type
		cur.execute(sql)
		row = cur.fetchone()
		if row==None: return "-1"
		return "%d" % row[0]

	#----------------------------
	# get_type_name_from_board_id
	#----------------------------
	def get_type_name_from_board_id(self, board_id):
		cur = self.con.cursor()
		sql = "SELECT name FROM board_type JOIN boards ON boards.boardtype_id=board_type.id"
		sql += " WHERE boards.id=%s" % board_id
		cur.execute(sql)
		row = cur.fetchone()
		if row==None: return "-1"
		return row[0]

	#----------------------------
	# get_status_from_board_id
	#----------------------------
	def get_status_from_board_id(self, board_id):
		cur = self.con.cursor()
		sql = "SELECT status,board_status.created FROM board_status JOIN boards ON boards.id=board_status.board_id"
		sql += " WHERE boards.id=%s" % board_id
		sql += " ORDER BY board_status.modified DESC LIMIT 1"
		cur.execute(sql)
		row = cur.fetchone()
		if row==None: return "-1"
		return (row[0],row[1])

	#----------------------------
	# get_boards
	#----------------------------
	def get_boards(self):
		cur = self.con.cursor()
		if self.show_deleted_boards.get_active():
			# Show all boards (even "deleted" ones)
			sql = "SELECT boards.id AS id,name,serial_number"
			sql += " FROM boards,board_type WHERE boardtype_id=board_type.id"
			sql += " GROUP BY boards.id ORDER BY boardtype_id,serial_number"
		else:
			# Show only boards not marked as deleted.
			# NOTE: boards without an entry in the board_status
			# table will also be filtered!
			sql = "SELECT boards.id AS id,name,serial_number"
			sql += " FROM boards,board_type,board_status"
			sql += " ,(SELECT max(modified) AS max FROM board_status GROUP BY board_id) AS tbl "
			sql += " WHERE boardtype_id=board_type.id"
			sql += "      AND board_status.board_id=boards.id"
			sql += "      AND status!='deleted'"
			sql += "      AND tbl.max=board_status.modified"
			sql += " GROUP BY boards.id ORDER BY boardtype_id,serial_number,board_status.modified DESC"
		cur.execute(sql)
		data = cur.fetchall()
		boards = []
		for s in data: boards.append(s)
		return boards 

	#----------------------------
	# show_dialog
	#----------------------------
	def show_dialog(button, dialog):
		if dialog.flags() & gtk.MAPPED:
			dialog.present()
		else:
			dialog.show()

	#----------------------------
	# hide_dialog
	#----------------------------
	def hide_dialog(button, dialog):
		dialog.hide()

	#----------------------------
	# update_boards_list_from_db
	#----------------------------
	def update_boards_list_from_db(self, data=None):
		boards = self.get_boards()
		self.boardsdb.clear()
		print "Found %d boards in DB" % len(boards)
		for s in boards: self.boardsdb.append(s)

	#----------------------------
	# board_selected
	#----------------------------
	def board_selected(self, boardview, data=None):
		id = self.get_selected_board(boardview)
		self.show_board_info(id)

	#----------------------------
	# get_selected_board
	#----------------------------
	def get_selected_board(self, boardview):
		(path, column) = boardview.get_cursor()
		model = boardview.get_model()
		if path==None: return ''
		id = model.get_value(model.get_iter(path), 0)
		return id
	
	#----------------------------
	# show_board_info
	#----------------------------
	def show_board_info(self, board_id):
		print 'show board info for id=%s ' % board_id
		board_type = self.get_type_name_from_board_id(board_id)
		location_id = self.get_location_id(board_id)
		location = self.get_location_name_from_id(location_id)
		(status,status_time) = self.get_status_from_board_id(board_id)
		self.board_info_id.set_text(board_id)
		self.board_info_type.set_text(board_type)
		self.board_info_status.set_text(status)
		self.board_info_status_time.set_text(status_time.ctime())
		self.board_info_location.set_text(location)
		
		cur = self.con.cursor()
		sql = "SELECT serial_number,property_control,authors.name"
		sql += " FROM boards,authors"
		sql += " WHERE boards.creator_id=authors.id AND boards.id=%s" % board_id
		cur.execute(sql)
		row = cur.fetchone()
		if row==None:
			self.board_info_serial.set_text("")
			self.board_info_property.set_text("")
			self.board_info_creator.set_text("")
		else:
			self.board_info_serial.set_text(row[0])
			self.board_info_property.set_text(row[1])
			self.board_info_creator.set_text(row[2])

	#----------------------------
	# add_board
	#----------------------------
	def add_board(button, dialog):
		if dialog.flags() & gtk.MAPPED:
			dialog.present()
		else:
			browser.boardtype_ls.clear()
			browser.location_ls.clear()
			types = browser.get_board_types()
			locations = browser.get_locations()
			for s in types:     browser.boardtype_ls.append([s])
			for s in locations: browser.location_ls.append([s])
			dialog.show()

	#----------------------------
	# add_board_add
	#----------------------------
	def add_board_add(button, dialog):
		# Check that all required info is present. If not, notify
		# user and return *without* hiding the dialog
		type_iter = browser.boardtype_cb.get_active_iter()
		location_iter = browser.location_cb.get_active_iter()
		serial = browser.serial_number_entry.get_text()
		jlab_prop = browser.jlab_prop_control_entry.get_text()
		if type_iter==None:
			print "Give type warning dialog"
			return
		if location_iter==None:
			print "Give location warning dialog"
			return
		if len(serial)<1:
			print "Give serial number dialog"
			return
		type_selected =browser.boardtype_ls.get_value(type_iter, 0)
		location_selected =browser.location_ls.get_value(location_iter, 0)
		type_id = browser.get_type_id_from_name(type_selected)
		location_id = browser.get_location_id_from_name(location_selected)
		author_id = browser.get_author_id()
		comment = ""
		
		# Check first that this board is not already in the DB
		cur = browser.con.cursor()
		sql = "SELECT id FROM boards WHERE boardtype_id=%s AND serial_number='%s'" % (type_id,serial)
		cur.execute(sql)
		if cur.rowcount > 0:
			print "Board already exists!"
			return
		
		# Insert into boards DB
		sql = "INSERT INTO boards (boardtype_id,serial_number,property_control,modifier_id,creator_id,created)"
		sql += " VALUES(%s,'%s','%s',%s,%s, NOW())" %(type_id,serial,jlab_prop,author_id,author_id)
		cur.execute(sql)

		# Get board id from DB
		sql = "SELECT id FROM boards WHERE boardtype_id=%s AND serial_number='%s'" % (type_id,serial)
		cur.execute(sql)
		row = cur.fetchone()
		board_id = "%d" % row[0]
		
		# Insert into board_status
		sql = "INSERT INTO board_status (board_id,status,location_id,comment,modifier_id,creator_id,created)"
		sql += " VALUES(%s,'Added',%s,'%s',%s,%s, NOW())" %(board_id,location_id,comment,author_id,author_id)
		cur = browser.con.cursor()
		cur.execute(sql)
		
		dialog.hide()
		browser.update_boards_list_from_db()
				

	#----------------------------
	# add_board_cancel
	#----------------------------
	def add_board_cancel(button, dialog):
		dialog.hide()

	#----------------------------
	# get_author_id
	#----------------------------
	def get_author_id(button=None):
		if browser.author==None:
			# No author is currently set. Run the dialog
			# to have the user select an existing author
			# or create a new one.
			browser.author_ls.clear()
			cur = browser.con.cursor()
			cur.execute("SELECT name from authors ORDER BY name")
			rows = cur.fetchall()
			for row in rows:browser.author_ls.append(row)

			res = browser.SelectAuthorDialog.run()
			browser.SelectAuthorDialog.hide()
			if res==gtk.RESPONSE_OK:
				# Dialog finished with OK button. Get info from
				# the various fields.
				name_selected = None
				iter = browser.author_cb.get_active_iter()
				if iter!=None: name_selected =browser.author_ls.get_value(iter, 0)
				name = browser.new_author_name.get_text()
				email = browser.new_author_email.get_text()

				# If both a name and an email were given, assume they
				# want to create a new author.
				if len(name)>0 and len(email)>0:
					sql = "INSERT INTO authors (name,email,created) VALUES('%s','%s',NOW())" % (name,email)
					cur.execute(sql)
					sql = "SELECT id,name FROM authors WHERE email='%s'" % email
					cur.execute(sql)
					row = cur.fetchone()
					browser.author_id = "%d" % row[0]
					browser.author = row[1]
				elif name_selected != None:
					# Try getting author id from DB based on selected name
					sql = "SELECT id,name FROM authors WHERE name='%s'" % name_selected
					cur.execute(sql)
					row = cur.fetchone()
					browser.author_id = "%d" % row[0]
					browser.author = row[1]

		return browser.author_id

	#----------------------------
	# remove_board
	#----------------------------
	def remove_board(button, boardview):
		board_id = browser.get_selected_board(boardview)
		if len(board_id)<1: return		
		author_id = browser.get_author_id()
		if len(browser.author_id)<1: return
		location_id = -1
		buff = browser.remove_board_comment.get_buffer()
		comment = buff.get_text(buff.get_start_iter(), buff.get_end_iter())
	
		print "removing board %s" % board_id
		sql = "INSERT INTO board_status "
		sql += "(board_id, status, location_id, comment, modifier_id, created, creator_id)"
		sql += "VALUES(%s,'deleted',%s,'%s',%s,NOW(),%s);" % (board_id,location_id,comment,author_id, author_id)
		cur = browser.con.cursor()
		cur.execute(sql)
		browser.update_boards_list_from_db()



if __name__ == "__main__":
    browser = ConfDBBrowser()
    browser.mainwindow.show()
    if not browser.dbconnected: browser.dbmodify.clicked()
    gtk.main()