====== Introduction ======
This project consists in creating a helpful tool to manage the equipment at the storage in the electrical engineering building of the University of Costa Rica.

We use the open source relational database management system (RDBMS) MySql and the programming language Python, the first one to create a database that contains all the equipment and users, also we use the open source software suite ZBar  for reading bar codes from various sources and Python for manage the ZBar functions and control the database.
====== Objectives =========
**__General Objective__**

  *Implement a basic program that helps with the manage of the equipment of certain place

**__Specific Objectives__**
  * Learn to use MySQL.
  * Create a program using Python to edit and manipulate the database.
  * Get to know with the open source software like ZBar.
 
====== Documentation ======
[[teaching:ie0117:proyectos:2013:i:waconsys:barcode_with_webcam|Scanning and decoding barcode with webcam (zbarcam)]]\\
[[teaching:ie0117:proyectos:2013:i:waconsys:using_mysql|Creating and Managing Databases in MySQL]]\\
[[teaching:ie0117:proyectos:2013:i:waconsys:python-mysql|Relating Python with MySQL]]

====== Implementation ======

==== Database Implementation ====

Table Equipment\\
^  Field    ^   Type       ^   NULL   ^  Key  ^  Default  ^  Extra  ^
|placa   |BIGINT(20)UNSIGNED  |    NO    |  PRI  |    NULL   |         |
|tipo   |VARCHAR(50)  |    YES    |       |    NULL   |         |
|marca      |VARCHAR(50)  |    YES    |       |    NULL   |         |
|modelo       |VARCHAR(50)  |    YES   |       |    NULL   |         |
|usuario   |BIGINT(20) UNSIGNED  |    YES   |       |          |         |
\\

Table User
^  Field    ^   Type       ^   NULL   ^  Key  ^  Default  ^  Extra  ^
|usuario   |BIGINT(20)UNSIGNED  |    NO    |  PRI  |    NULL   |         |
|canet   |VARCHAR(50)  |    YES    |       |    NULL   |         |
|nombre      |VARCHAR(50)  |    YES    |       |    NULL   |         |
|telefono       |VARCHAR(50)  |    YES   |       |    NULL   |         |
|email   |VARCHAR(50)  |    YES   |       |          |         |



==== Base Program Implementation ====

=== scann module ===

we use the following code to define a function that returns the scan code as a integer value and use this value for the application. We use the module scan to get the value and access to our database.

<file python>

  #!/usr/bin/python

  import zbar

  def scan():

	proc = zbar.Processor()

	proc.parse_config('enable')

	device = '/dev/video0'
	proc.init(device)

	proc.visible = True
	proc.process_one()
	proc.visible = False

	for symbol in proc.results:
		code = [symbol.data]
		code=code[0]
		code=int(code)
		return code
</file>
\\
In the last lines of this script you can see how we extract from the list symbol.data the integer value of the scan code.
\\

=== update module ===


We use the next code to build the **update** module. which receives two parameters through the tuple //data//, which contains the variables //user// and //equipment//, scanned with the **scan** module 

<file python>
#!/usr/bin/python

import MySQLdb

def updatedb(data):
	# Open database connection
	db = MySQLdb.connect("localhost","user","bodega","bodegaEIE" )

	# prepare a cursor object using cursor() method
	cursor = db.cursor()

	# Prepare SQL query to UPDATE required records
	sql = "UPDATE equipo SET usuario = '%s' WHERE placa = '%s'" %(data)
	try:
	   # Execute the SQL command
	   cursor.execute(sql)
	   # Commit your changes in the database
	   db.commit()
	except:
	   # Rollback in case there is any error
	   db.rollback()

	# disconnect from server
	db.close()
</file>

=== check module ===


This module was created to verify a certain equipment is registered in the database. Note how the //fetchall// method returns a list of tuples or empty list when no rows available. This module return a False boolean value to indicate that some equipment is not registered in the database 

<file python>
#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

def checkdb(code):

	# Open database connection	
	db= mdb.connect('localhost', 'user', 'bodega', 'bodegaEIE')

	# prepare a cursor object using cursor() method
	cursor = db.cursor()

	# Prepare SQL query to INSERT a record into the database.
	sql = "SELECT * FROM equipo WHERE placa = '%d'" % (code)
	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Fetch all the rows in a list of lists.
		results = cursor.fetchall()
		if len(results)  == 0:
			return False
		else:
			return True
	except:
		print "Error: unable to fecth data"
		
	# disconnect from server
	db.close()
</file>

==== read module===

This one was created to access the database and take the information for a determined equipment.

<file python>

#!/usr/bin/python

import MySQLdb

def readdb(code):

	# Open database connection
	db = MySQLdb.connect("localhost","root","cocolocoEC85","bodegaEIE" )

	# prepare a cursor object using cursor() method
	cursor = db.cursor()

	# Prepare SQL query to INSERT a record into the database.
	sql = "SELECT * FROM equipo WHERE usuario = '%d'" % (code)
	try:
		# Execute the SQL command
	        cursor.execute(sql)
		# Fetch all the rows in a list of lists.
		results = cursor.fetchall()
		if len(results)  == 0:
			print "ningun equipo registrado al usuario '%d'" %(code)
		else:
			for row in results:
				placa = row[0]
				tipo = row[1]
				marca = row[2]
				modelo = row[3]
				usuario = row[4]
				# Now print fetched result
				print " "
				print "placa=%d,  tipo=%s,  marca=%s,  modelo=%s,  usuario=%s" % (placa, tipo, marca, modelo, usuario)
				print " "
	except:
		print "Error: unable to fecth data"
		
	# disconnect from server
	db.close()

</file>

===== clear module =====

When some returns the equipment, we have to erase his name from the database so we use this module:

<file python>
#!/usr/bin/python

import MySQLdb

def cleardb(data):
	# Open database connection
	db = MySQLdb.connect("localhost","user","bodega","bodegaEIE" )

	# prepare a cursor object using cursor() method
	cursor = db.cursor()

	# Prepare SQL query to UPDATE required records
	sql = "UPDATE equipo SET usuario = NULL WHERE placa = '%s'" %(data)
	try:
	   # Execute the SQL command
	   cursor.execute(sql)
	   # Commit your changes in the database
	   db.commit()
	except:
	   # Rollback in case there is any error
	   db.rollback()

	# disconnect from server
	db.close()

</file>

==== linking the modules =====

We link all the modules in this little script.
<file python>
#!/usr/bin/python

import sys
from time import sleep
from scanner import scan
from update import updatedb
from check import checkdb
from read import readdb
from clear import cleardb



print "digite 'prestar', 'devolver', 'consultar' o 'exit' para lo que desee"

exit=False
while not exit:
	a=raw_input()
	if a=="salir":
		exit=True
		print "Bye"
	elif a=="prestar":
		equipo=scan()
		print "el equipo escaneado es '%d'" %(equipo)
		print "dijite ok si es correcto"
		b=raw_input()
		check=checkdb(equipo)
		if ((check == True) and (b == "ok")):
			usuario=scan()
			print "el usuario escaneado es '%d'" %(usuario)		
			dato = (usuario,equipo)
			print "desea prestar al usuario '%d' el equipo '%d'? [si/no] " % dato
			c=raw_input()
			if c=="si":
				updatedb(dato)
				print "equipo prestado"
			elif c=="no":
				print "equipo no prestado, dijite 'scann' para nuevo intento"
		else:
			print "equipo no registrado en la base de datos"
	elif a=="devolver":
		equipo=scan()
		print "el equipo escaneado es '%d'" %(equipo)
		check=checkdb(equipo)
		if check == True:
			print "desea devolver '%d'? [si/no] " % (equipo)
			b=raw_input()
			if b=="si":
				cleardb(equipo)
				print "equipo devuelto"
			elif b=="no":
				print "no se ha devuelto el equipo"
		else:
			print "equipo no registrado en la base de datos"
	elif a=="consultar":
		code=scan()
		readdb(code)
	else:
		print "codigo incorrecto"
		print "digite 'prestar', 'devolver', 'consultar' o 'exit' para lo que desee"
	sleep (1.0)
</file>

\\
[[teaching:ie0117:proyectos:2013:i:waconsys|
go back]]

====== References ======
http://dev.mysql.com/doc/

http://www.mikusa.com/python-mysql-docs/query.html

http://zbar.sourceforge.net/