import RPi.GPIO as GPIO
import time
import fdb
import re

con = fdb.connect(dsn='10.100.2.185/3050:/home/trainee2/Desktop/ice', user='sysdba', password='trainee')
cur = con.cursor()

namen = []
voornamen = []
fotos = []
pinnen = []
status = [0] * 12
indexSpatie = 0
inOrOut =0
xcoors = []
ycoors = []
ids = []
controleIds = []
controlepin = [1] * 12
GPIO.setmode(GPIO.BCM)
statement = ""
dagVanVandaag = time.strftime("%d")

def getPinnen():
    cur.execute("SELECT PINNUMBER, NAME, PRENAME, FOTOLINK,XAS, YAS, PERSONID FROM ICEUSERS WHERE PERSONID > 29 ")
    for (PINNUMBER, NAME, PRENAME, FOTOLINK, XAS, YAS, PERSONID) in cur:
        PINNUMBER = str(PINNUMBER).replace("(","").replace(",)", "")
        pinnen.append(int(PINNUMBER))
        NAME = str(NAME).replace("(","").replace(",)", "")
        namen.append(NAME)
        PRENAME = str(PRENAME).replace("(","").replace(",)", "")
        voornamen.append(PRENAME)
        FOTOLINK = str(FOTOLINK).replace("(","").replace(")", "")
        fotos.append(FOTOLINK)
        XAS = str(XAS).replace("(","").replace(")", "")
        xcoors.append(XAS)
        YAS = str(YAS).replace("(","").replace(")", "")
        ycoors.append(YAS)
        PERSONID = str(PERSONID).replace("(","").replace(")", "")
        ids.append(PERSONID)

def controleVars():
    cur.execute("SELECT DISTINCT PERSONID FROM ICEDATABASE WHERE PERSONID > 29 and ISPRESENT = 1 and cast(DATUMIN as date) = (select DISTINCT cast ('Now' as date) from icedatabase);")
    for (PERSONID) in cur:
        PERSONID = str(PERSONID).replace("(","").replace(")", "").replace(",","")
        controleIds.append(PERSONID)
    while (len(controleIds) < 12):
        controleIds.append(" ")


getPinnen()
controleVars()
for p in range(0,12):
    GPIO.setup(pinnen[p],GPIO.IN)

while True:
    for e in range(0,12):
        status[e] = GPIO.input(pinnen[e])
        if (status[e] != controlepin[e]):
            datum = time.strftime("%Y-%m-%d %H:%M")
            uur = time.strftime("%H")
            voornaam = voornamen[e]
            achternaam = namen[e]
            stat = str(status[e])
            foto = fotos[e]
            ycoor = ycoors[e]
            xcoor = xcoors[e]
            id = ids[e]
            if (status[e] == 0):
                print id
                if id in controleIds:
                    print "erin"
                    cur.execute("update icedatabase set DATUMIN = ?, ISPRESENT = 1 where PERSONID = ? and cast(DATUMIN as date) = (select DISTINCT cast ('Now' as date) from icedatabase);",(datum,id))
                else:
                    cur.execute("insert into ICEDATABASE (PRENAME, NAME, DATUMIN, PICTURELINKS, ISPRESENT, XCOR, YCOR, PERSONID) values(?,?,?,?,1,?,?,?)",(voornaam,achternaam,datum,foto,xcoor,ycoor, id))
                    controleIds[e] == id
            else:
                cur.execute("update icedatabase set DATUMOUT = ?, ISPRESENT = 0 where PERSONID = ? and cast(DATUMIN as date) = (select DISTINCT cast ('Now' as date) from icedatabase);",(datum,id))
                controleIds[e] = " "


            if(dagVanVandaag != time.strftime("%d")):
                    controleIds = []
                    controleVars()

            con.commit()
            controlepin[e] = status[e]

    time.sleep(1)