**Group Tembo**\\
Arturo Apú Chinchilla. B20386\\
Erick Eduarte Rojas. B22305\\
Luis Felipe Rincón Riveros. B25530\\
======= Description =======
The main goal of the project is to create a device that controls an electric latchkey with a circuit (with a relay and a transistor), a RaspberryPi and a barcode reader.\\
The RaspberryPi will receive a serial number from the barcode reader from the user's card. This number will travel from the RaspberrryPi to a server with a database. The database will have the registers of the authorized users (authorized by an administrator) to enter the room. Only if the user is authorized, the server will return a positive answer to the RaspberryPi for it to activate the circuit that sends the signal to the latchkey and opens it.
======= Justification =======
The project's goal is to control the entrance to the ArcosLab or any other room at the Escuela de Ingeniería Eléctrica. The device to be created is a low cost one; the RaspberryPi and the electric components are cheap artecfacts. It will include a permisions' database which only allows athorized people into the room. The device will also be usefull to keep a register of the people that enters the room.
======= Objectives =======
===== General ====
- Build an electric latchkey-controlling device with a Bar Code reader, controlled by a RaspberryPi that comunicates with a database with user permissions.
===== Specific =====
- Create a database, using SQlite, with the authorized users in specific periods and a program, using Python, that modifies it.
- Create a program that allows comunication between the server with the database and the RaspberryPi.
- Control, with the GPIO pins of the RasperryPi, a electric circuit (with a transitor, a magnetic relay and a optoacoupler) that controls a electromecatic lock KP321 Assa Abloy Phillips.
====== Contents ======
===== Database in Sqlite3 =====
{{:ie0117_proyectos_2013:200px-sqlite370.svg.png?200|}}
The database to create is a //relational database//.SQlite will be the database management system. SQLite is in a relatively small library (275 kiB) but allows databases as big as 2TB ((Further information about SQLite at [[http://en.wikipedia.org/wiki/SQLite]] and [[http://www.sqlite.org/download.html]])). The final database have 5 tables:\\
- A table 'Usuario' with the user's information. A specific number ('Num_Usuario' the primary key) and the person's characteristics ('Name', 'Email', and 'Cedula').\\
- Another table 'Tarjeta' with the information related to de card used by the user.'Num_Serie' is the number in the barcode (primary key); 'Esta_Activa' a boolean that tells if the card is active or not; and 'Num_Usuario' a foreign key that tells which user is related to the specific card.\\
- A third table 'Horario' with the period in which the users are authorized to enter the room. 'ID' a number related to a user (primary key); a time 'desde' and a time 'hasta'. The user can enter the room from time 'desde' until time 'hasta'.\\
- A table 'Tarjeta-Horario' that references 'Tarjeta' and 'Horario'.\\
- The last table called 'Bitacora' that makes a record of all the users that open the latchkey, saving the date and hour in wich they enter the room. This table has a variables called 'Dia' (corresponding day), 'Hora' (corresponding hour), 'Num_Usuario' and 'Num_serie', that references this table to 'Usuario' and 'Tarjeta' respectively. This table does not have a primary key.
Due that Sqlite only support 5 datatypes ((Further information about sqlite and the datatypes it supports at [[http://www.sqlite.org/docs.html]] and [[http://www.sqlite.org/datatype3.html]])) (null, integer, real, text and blob) there were different limitations for choosing the right ones. Finally the variables were chosen in this way:
* Text: The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE). 'Nombre' and 'Email' are text datatype.
* Integer: The value is a signed integer, stored in 1 to 8 bytes depending on the magnitude of the value. Variables 'Num_Usuario' ,'Num_Serie' and 'ID' are all integers.
* Numeric: With Numeric affinity a column may contain values using all five storage classes. For 'Cedula' ,'Esta_Activa' ,'Desde' ,'Hasta' y 'Es_Valida'. Since Sqlite does not have a 'time' datatype 'deade' and 'hasta' were assigned as numerics. Sqlite does not have a 'boolean' datatype either, so 'Esta_Activa' and 'Es_Valida' were assigned as numerics also.
The database was created as follows:
CREATE TABLE Usuario (Num_Usuario INTEGER PRIMARY KEY, Nombre TEXT, Email TEXT, Cedula INTEGER);
CREATE TABLE Tarjeta (Num_Serie INTEGER PRIMARY KEY, Esta_Activa NUMERIC, Num_Usuario INTEGER, FOREIGN KEY(Num_Usuario) REFERENCES Usuario(Num_Usuario));
CREATE TABLE Horario (ID INTEGER PRIMARY KEY, Desde NUMERIC, Hasta NUMERIC);
CREATE TABLE Tarjeta_Horario(Num_Serie INTEGER, Es_Valida NUMERIC, ID INTEGER,
FOREIGN KEY(Num_Serie) REFERENCES Tarjeta(Num_Serie), FOREIGN KEY(ID) REFERENCES Horario(ID));
CREATE TABLE Bitacora (Dia TEXT, Hora TEXT, Num_Usuario INTEGER, Num_Serie INTEGER,
FOREIGN KEY(Num_Usuario) REFERENCES Usuario(Num_Usuario),
FOREIGN KEY(Num_Serie) REFERENCES Tarjeta(Num_Serie));
{{:ie0117_proyectos:final_2013:base.png|}}
===== Comunication Process =====
An internet socket is an abstrac concept in wich two different programs (located in different computers) may exchange any type of data.
The sockets allows a connection server-client.This comunication must be initialized by the client and the server only waits for clients to start connection.
This concept is applied on this project due that Rasperry Pi needs to comunicate with the server in wich the database is located and also by the clients (including the administrator) who wants to check their status information as an user or change it for some reason.
{{:ie0117_proyectos:final_2013:protocolo.png?nolink|}}
==== RaspberryPi-Server-Database ====
A program called client.py is constantly running in the RaspberryPi. First, it establishes a connection with the server and creates socket. Then, it calls read.py, which opens Zbar. When Zbar reads a barcode, client.py sends a message to client-server.py that consists in 'decoded<>' and the barcode concatenated. Client-server.py splits this message. If the message is 'decoded' it uses a function called 'buscar_datos()'. This function goes to the database, first checks if the scaned barcode is somewhere in the database, then checks if the associated user to the barcode is active or not (this is, if 'Esta_Activa' is 1 or 0), and finally checks if the current time is in the user's schedule (between 'desde' and 'hasta'). This returns 'True' or 'False' to client-server.py; if the answer is 'True' 'buscar_datos()' also saves the entry in 'Bitacora'. Then, client-server.py sends that back to the RaspberryPi. If the answer is 'True', client.py calls a function call open(), which gives 3V volts in the RaspberryPi's GPIO pins that immediately opens the latchkey.
**Read.py**
#!/usr/bin/python
from sys import argv
import zbar
import time as t
def read():
# create a Processor
proc = zbar.Processor()
# configure the Processor
proc.parse_config('enable')
# initialize the Processor
device = '/dev/video0'
if len(argv) > 1:
device = argv[1]
proc.init(device)
# enable the preview window
proc.visible = True
# read at least one barcode (or until window closed)
proc.process_one()
# hide the preview window
proc.visible = False
# extract results
for symbol in proc.results:
# do something useful with results
print 'decoded','%s' % symbol.data
return symbol.data
**Client.py (RaspberryPi)**
#! /usr/bin/env python
import socket
import read as procs
import time as t
import newblink
#Crates the socket to make the connection between the RPi and server
client = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
server_address = ('192.168.0.104', 10101)
print "socket created"
client.connect(server_address)
print "connection established, write a command"
o_info=''
while True:
info1=procs.read() #In the Rpi executes Zbar each time a barcode is scanned
if info1 != '':
message = 'decoded<>'+info1
client.sendall(message) # Sends the barcode and specify to server what to do with it.
permiso=client.recv(10)# Recieves the permission send by the database (True o False)
print (permiso)
if str(permiso)=='True':
newblink.open() # Activates the GPIO pins
o_info = info1
t.sleep(2)
**Server.py**
#! /usr/bin/env python
import socket, os
import client_server
from client_server import Client_server
#Defines the socket
def socket_setup():
server = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
server_address = ('192.168.0.104', 10101)
server.bind(server_address)
server.listen(1)
print "server created and configured"
return server
#Wait for connections and send a message if the cennection is lost
def wait_connection(server):
wait_connection = True
while wait_connection:
print "Online and waiting for new requests"
client_socket, client_address = server.accept()
newproc = os.fork()
if newproc == 0:
wait_connection = False
client_sver = Client_server(client_socket)
client_sver.decide()
client_sver = None
print 'lost connection'
os._exit(1)
def main():
server = socket_setup()
wait_connection(server)
if __name__== "__main__":
main()
**Client_Server**
# /usr/bin/env python
import socket
import Methods as met
class Client_server:
def __init__(self, client_socket):
self.client_socket = client_socket
def __del__(self):
del self.client_socket
def decide(self):
self.client_socket.settimeout(900)
while True:
try:
msg = self.client_socket.recv(4096) #Recieves the user's message
except socket.timeout:
self.client_socket.close()
break
instruction = []
instruction = msg.split('<>') #Split the instruction given to execute an action
command = instruction[0]
if command == 'printf':
self.printf(instruction)
#It is executed if recieves a code from the RPi and proceeds to look for permissions
elif command == 'decoded':
permiso=met.buscar_datos(instruction[1])
self.answ_rasp(permiso)
#Answer to users the succesfuly or fail action
def answer(self, status):
if int(status):
answer = 'Succesful'
else:
answer = 'An error has ocurred'
self.client_socket.sendall(answer)
#Prints the message from the user
def printf(self, msg_list):
print msg_list[1]
self.answer(1)
#Answer specificly to RPi if it has to open the latchkey or not
def answ_rasp(self,dato):
if dato:
self.client_socket.sendall('True')
else:
self.client_socket.sendall('False')
==== Server functions ====
A second client was created for comunication with the database. This client displays a menu with different functions for an user or an administrator.
* //Administrator//. A password is required and the following functions are provided
- **Search a users' information**. Prints the name, email, cedula and Num_Usuario of a specific user
- **Add user**. Adds as many user as wanted. Name, emial, cedula, time 'desde' and time 'hasta' are the only requiriments. All the users are inicially active.
- **Change Num_Serie**. Changes the barcode associated to a users. This if the users loses his card or similar.
- **Change activation**. Activates or desactivates a user's 'Tarjeta', for people that are not anymore required in the room.
- **Change schedule**. Changes times 'desde' and 'hasta' so that a user can enter at different moments.
- **Print binnacle**. The administrator can chose whether to print the binnacle with all the entraces or only the ones associated to an specific user.
- **Quit**.
* //User//
- **Print registers**. Prints any user's information.
#! /usr/bin/env python
import socket
import Methods as met
client = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
server_address = ('192.168.0.104', 10101)
print "socket created"
client.connect(server_address)
print "connection established, write a command"
def ask_info():
info = raw_input()
return info
print '/n'
exit = False
while not exit:
command = ask_info()
if command == "exit":
exit = True
elif command == 'print':
info = ask_info()
message = 'printf<>'+info
client.sendall(message)
print client.recv(10)
elif command == 'menu':
print('Funciones de adminitrador (1) o del usuario (2)? \n')
user=raw_input()
exit1=1
option=0
if int(user)==1:
while exit1==1:
print('Password :')
psw=raw_input()
if str(psw)==met.password:
while option!=7:
print('Funciones : \n 1-Buscar informacion de usuario \n 2- Agregar usuarios \n 3- Cambiar codigo de usuario \n 4-Cambiar estado de tarjeta \n 5-Cambiar horario de permiso \n 6- Imprimir bitacora \n 7-salir \n Cual numero de opcion desea realizar? : ')
opt= ask_info()
option=int(opt)
if int(option)==1:
met.print_registers()
if int(option)==2:
met.add_users()
if int(option)==3:
met.change_barcode()
if int(option)==4:
met.change_status()
if int(option)==5:
met.change_schedule()
if int(option)==6:
met.print_binnacle()
if int(option)==7:
print('Ha salido del menu \n')
met.cursor.close()
exit1=0
else:
print("Clave incorrecta, desea digitarla de nuevo?, 1- Si, 2-No \n")
opt=raw_input()
if opt==2: exit1=True
if int(user)==2:
met.print_registers()
Methods used.
#!/usr/bin/python
import sqlite3
from datetime import datetime
connection = sqlite3.connect('prueba.db')
cursor = connection.cursor()
password='raspberry'
def add_users(): #method that adds users
cantidad = raw_input('Cantidad de usuarios por agregar: ') #this is the number of users to add
for i in range(0, int(cantidad)):
print('Usuario '+str(i+1)) #the program asks for the information needed in the database
nombre = raw_input('Nombre: ')
email = raw_input('Email: ')
NumCedula = raw_input('Numero de identificacion: ')
numeroDeSerie = raw_input('Numero de codigo de barra: ') #the barcode
desde = raw_input('Autorizado desde: (HH:MM) ') #this recieves a 24 hour clock time, so if the user wants to enter "3 p.m.", he/she should enter "15:00".
hasta = raw_input('Autorizado hasta: (HH:MM)')
#fills table Usuario
datosU = [(nombre, email, int(NumCedula))]
for t in datosU: #sqlite autoincrements the INTEGER FOREING KEYS if inicializated as NULL
cursor.execute('INSERT INTO Usuario (Num_Usuario,Nombre,Email,Cedula) VALUES (NULL,?,?,?)', t)
j=cursor.execute('SELECT last_insert_rowid()')
ultimo_Num_Usuario=j.fetchone()[0] #the last Num_Usuario es saved in here to fill other tables
#fills table Tarjeta
datosT = [(numeroDeSerie, '1', ultimo_Num_Usuario)]
for k in datosT: #every new user is authorized by default, which means Esta_Activa is always 1 when a user is added
cursor.execute('INSERT INTO Tarjeta (Num_Serie,Esta_Activa,Num_Usuario) VALUES (?,?,?)', k)
#fills table Horario **time is stored as a integer with onlythe minutes, so the hour is multiplied by 60 and added to the minutes
desd = int(desde.split(':')[0])*60+int(desde.split(':')[1]) #
hast = int(hasta.split(':')[0])*60+int(desde.split(':')[1])
datosH = [(desd, hast)]
for m in datosH:
cursor.execute('INSERT INTO Horario (ID,Desde,Hasta) VALUES (NULL, ?,?)', m)
q=cursor.execute('SELECT last_insert_rowid()')
ultimo_ID=q.fetchone()[0]
#fills table Tarjeta-Horario
datosTH = [(numeroDeSerie, '1', ultimo_ID)]
for v in datosTH:
cursor.execute('INSERT INTO Tarjeta_Horario (Num_Serie,Es_Valida,ID) VALUES (?,?,?)', v)
connection.commit()
print 'Usuario agregado exitosamente'
cursor.close()
def change_barcode(): #method that changes the barcode of a specific user
e_user = raw_input('Usuario al cual desea cambiarle el codigo: ')
e_code = raw_input('Nuevo codigo: ')
user = (str(e_user),)
code = int(e_code)
#the previous barcode (or Num_Serie) is found
num_user = cursor.execute('SELECT Num_Usuario FROM Usuario WHERE Nombre=?', user)
v_num_user = num_user.fetchone()[0]
w_num_user = (v_num_user,)
values = (code, v_num_user,)
#the barcode is changed by updating the tables
cursor.execute('UPDATE Tarjeta SET Num_Serie=? WHERE Num_Usuario=?', values)
cursor.execute('UPDATE Tarjeta_Horario SET Num_Serie=? WHERE ID=?', values)
connection.commit()
print('Codigo de barras cambiado exitosamente')
cursor.close()
def print_registers(): #method that print the information of an user
usuario1 =raw_input('Usuario por encontrar: ')
user=(str(usuario1),)
x=cursor.execute('SELECT * FROM Usuario WHERE Nombre=?', user)
resp=''
for row in x:
resp= 'Numero de usuario: %i \n' %row[0]
resp+= 'Nombre: %s \n' %row[1]
resp+= 'Direccion de email: %s \n' %row[2]
for row1 in cursor.execute('SELECT * FROM Horario WHERE ID=?', (int(row[0]),)):
resp+= 'Desde las %s horas hasta las %s horas' %(row1[1],row1[2])
print resp
def buscar_datos(barcode): #method that checks if the user is active and authorized in the current moment and, if it is, saves the entry in Bitacora
v_bar_code = (int(barcode),)
now = datetime.now()
today = str(now).split()[0]
th_time = str(now).split()[1].split('.')[0]
time_in_minutes=int(th_time.split(':')[0])*60+int(th_time.split(':')[1])
try:
#finds Num_Serie
x = cursor.execute('SELECT Num_Serie FROM Tarjeta WHERE Num_Serie=?', v_bar_code)
x1 = int(x.fetchone()[0])
#finds ID
idns = cursor.execute('SELECT ID FROM Tarjeta_Horario WHERE Num_Serie=?', v_bar_code)
ids = int(idns.fetchone()[0])
#finds desde and hasta
ds = cursor.execute('SELECT Desde FROM Horario WHERE ID=?', (ids,))
desde = ds.fetchone()[0]
hs = cursor.execute('SELECT Hasta FROM Horario WHERE ID=?', (ids,))
hasta = hs.fetchone()[0]
#checks if Esta_Activa is 1 or 0
validez = cursor.execute('SELECT Esta_Activa FROM Tarjeta WHERE Num_Serie=?', v_bar_code)
v = validez.fetchone()[0]
if x1==int(barcode):
if v==1:
if desde
===== Opening latchkey with Raspberry pi's gpio pins =====
Once installed RPi.GPIO ((Further information about GPIO at [[http://en.wikipedia.org/wiki/General_Purpose_Input/Output]])) is needed to setup (export) the pins. A scheme of the pins can be found down here in the image. In this project we use the BCM GPIO numbering, especificaly the pin 18 (12th pin counting from the bottom of the pins) and the ground pin(6th pin from the bottom).
Those pins are connected directly to the circuit make previously [[teaching:ie0117:proyectos:2013:i:berrylocky|Designing and creating the circuit]] to open the latchkey. Those pins are managed with code shown in newblink.py. This python script executes a few of simple commands in console to export and setup the GPIO pins. The reason this script was made in this way is because when running a python script, a sudo command is needed and it makes zbarcam fail. So running directly into console avoids the need to run the script as root.
{{ :ie0117_proyectos:final_2013:pinout.jpg?nolink&300 |}}
The program used to manipulated GPIO pins is the following.
#! /usr/bin/env python
import os #heather that allows to use line shell comands in python
import time as t
def open()
os.system('gpio export 18 out')
os.system('gpio -g mode 18 out')
os.system('gpio -g write 18 1') #gives 3 volts
t.spleep(1) #for 1 second
os.system('gpio -g write 18 0') #and stops the voltage
======= Results & Conclusions =======
* A circuit capable of opening the latchkey was created. GPIO pins were succesfully acopled to this circuit and the management and verification of permissions into the database were acomplished.
* It was possible to implement a remote computer to control the RaspberryPi that also handles the webcam and it iterates the process all the time, opening the latchkey when it has to.
* It was achieved reading barcodes implementing programs in python as zbar and managing this information to open the latchkey.
* A relational database was created with all the information needed to verify a correct regulation of the entrace. And also save a record of all the people who enter to the room specifying the exact date and hour.