# coding: utf-8
import socket
import RPi.GPIO as GPIO
import time
import MySQLdb
import sys
import serial
serial = serial.Serial('/dev/ttyUSB0', 115200)

paramMysql = {
'host'   : '172.17.18.42',
'user'   : 'raspberryGachesAfficheur',
'passwd' : 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',
'db'     : 'velo'
}


gache = [17,27,22,0,0,0,0,0,0,0,0,0]

def ouvrirGache(numGache):
    print "called OuvrirGache"
    if (numGache > 0): ouvrirGache(0)
    GPIO.output(gache[numGache],True)
    time.sleep(2)
    GPIO.output(gache[numGache],False)


host = ''        # Toutes les interfaces réseau
port = 15555     # Port
s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s.bind((host, port))


GPIO.setmode(GPIO.BCM)

for i in range(0,12):
  GPIO.setup(gache[i],GPIO.OUT) # Gaches

GPIO.setup(12,GPIO.OUT)         # Eclairage

GPIO.setup(16,GPIO.IN)          # Intrusion


print host , port

while True:
    s.listen(1)
    conn, addr = s.accept()
    print('Connected by', addr)

    while True:

        try:

            received_idcasier = conn.recv(1024)

            if not received_idcasier: break

            if (int(received_idcasier) < 42):

                received_idcasier = int(received_idcasier)
                
                ouvrirGache(received_idcasier)

                try:

                    connexion = MySQLdb.connect(**paramMysql)
                    curseur = conn.cursor(MySQLdb.cursors.DictCursor)
                    requete_sql = """SELECT prenom, soldepoints FROM Cyclistes WHERE idcasier = %s"""
                    curseur.execute(requete_sql, (received_idcasier, ))
                    rows = curseur.fetchall()
                    for row in rows:
                        prenom = row['prenom']
                        soldepoints = row['soldepoints']
                        print(prenom)
                        print(soldepoints)
                        string = "Bienvenue "+prenom+"  Bravo tu as "+str(soldepoints)+" points."
                        string_encode = string.encode()
                        serial.write(string_encode)