#! /usr/bin/env python

#  PROBLEMAS APARENTE CON EL LOAD_CONTACT

###########################################################################
# authors = {"pynash- Walter Danilo Vargas": "pynash AT gmail DOT com"
#		"Joel Davila: joe74@fedoraproject.org"}   #
#                                                                         #
# date = "January 30th, 2011"                                             #
#                                                                         #
# version = ":~$ revision 1.0$"                                           #
###########################################################################


import os.path
import sqlite3
import os.path, subprocess as sp
from random import randint

	
class Conexion(object):
	"""Class bd's conection, basic operation"""

	def __init__(self, id = "", f_name = " ", l_name = " ", address = " ", phone = " ", ocupation=" ", email=" ", category=" "):
		"""Initialiting var and conecting to bd"""
		self.id = id
		self.f_name = f_name
		self.l_name = l_name
		self.address = address
		self.phone = phone
		self.ocupation = ocupation
		self.email = email
		self.categoria = category
		self.imagen = ""

		if not (os.path.exists('sqlite3_localdb.db')):
			self.conn  = sqlite3.connect("sqlite3_localdb.db")
			self.c = self.conn.cursor()
			self.c.execute("""create table User (id INTEGER, \
							f_name text,\
							l_name text,\
							address text,\
							phone text,\
							ocupation text,\
							email text,\
							categoria text,\
							imagen BLOB)""")
			#cargamos la imagen
			imgdata = open('./varios/3.bmp', 'r').read()
			self.imagen = sqlite3.Binary(imgdata)
			
			simbol1 = (1, 'carlos', 'vargas', '', '12345', 'Estudiante', '', 'amigos', self.imagen, )
			simbol2 = (2, 'walter danilo', 'vargas', 'Masaya', '12345', 'Estudiante', 'pynash@gmail.com', 'otros', self.imagen, )
			self.c.execute("""insert into User values(?, ?, ?, ?, ?, ?, ?, ?, ?)""", simbol1)
			self.c.execute("""insert into User values(?, ?, ?, ?, ?, ?, ?, ?, ?)""", simbol2)
			self.conn.commit()
		else:
			self.conn  = sqlite3.connect("sqlite3_localdb.db")
			self.c = self.conn.cursor()

	def _exists_user(self, name):
		self.c.execute("select f_name, l_name from User ")
		for i in self.c:
			if (i[0].lower() + " " + i[1].lower()) == name:
				return 1

	def _operation_image(self, image):
		if os.path.exists('./images/show.jpg'):
			sp.call('rm ./images/show.jpg', shell=True)

		with  open('./images/show.jpg', 'w') as f:
			f.write(image)

	def _show_default(self, metodo=1):
		self.conn.row_factory = sqlite3.Row
		t = (self.f_name, self.l_name, )
		if metodo:
			self._randint()
			self.c.execute('select * from User where id=?', (self.id, ))
		else:
			self.c.execute('select * from User where f_name=? and l_name=?', t)
		r = self.c.fetchone()

		return [r[1], r[2], r[3], r[4], r[5], r[6], r[7]]

	def _add_user(self):
		"""register a new user to bd"""
		self.id = self._id_calc()
		imgdata = open('./noavailable.png', 'r').read()
		self.imagen = sqlite3.Binary(imgdata)
			
		simbol = (self.id, self.f_name, self.l_name, self.address, self.phone, self.ocupation, self.email, self.categoria, self.imagen, )
		self.c.execute('insert into User \
				 values (?, ?, ?, ?, ?, ?, ?, ?, ?)', simbol)
		self.conn.commit()

	def _return_id(self, fname, lname):
		t = (fname, lname, )
		self.conn.row_factory = sqlite3.Row
		self.c.execute('select id from User where f_name=? and l_name = ?', t)
		id = self.c.fetchone()
		return id[0]

	def _update_user(self):
		"""udpate a user in the bd"""
		t = (self.f_name, self.l_name, self.address, self.phone, self.ocupation, self.email, self.categoria, self.id)

		self.c.execute('update User \
				set f_name=?, l_name=?, address=?, phone=?, ocupation=?, email=?, categoria=?\
				 where id=?', t)
		self.conn.commit()

	def _del_user(self):
		"""delete a user"""
		t = (self.id, )
		self.c.execute('delete from User where id=?', t)
		self.conn.commit()

	def _id_calc(self):
		self.conn.row_factory = sqlite3.Row
		self.c.execute('select max(id) from user')
		id = self.c.fetchone()
		return id[0]+1

	def _randint(self):
		self.conn.row_factory = sqlite3.Row
		self.c.execute('select id from User')
		list1 = []
		for i in self.c:
			list1.append(i)
		list2 = []
		for i in list1:
			list2.append(i[0])
		self.id = list2[randint(1, len(list2)-1)]
		
	def _display_on_tree(self, cat):
		self.c.execute('select f_name, l_name from User where categoria=?', (cat, ))
		lista = []
		for i in self.c:
			lista.append([i[0], i[1]])
		return lista

		#lista = []
		#for i in self.c:
		#	lista.append(i[0] + " " + i[1])
		#return lista