# -*- coding: utf-8 -*-
import serial
import mysql.connector

SERIALPORT = "/dev/tty.wchusbserial410" #serial port on mac
SERIALRATE = 9600

ser = serial.Serial(SERIALPORT, SERIALRATE)
conn = mysql.connector.connect(host='localhost', database='drinkDispenser', user='root', password='root')
if conn.is_connected():
    print('Connected to MySQL database')

cursor = conn.cursor()
print ("connection OK, cursor OK")

while 1:
    serial_line = ser.readline()
    if(serial_line != "GO_BLINK"):
        IDCardReadLine = ser.readline()
        print(IDCardReadLine) #del this line - only show the tag ID

        sqlSearchCardID = "SELECT idUser, nameUser, userCardID, isActive FROM Users WHERE userCardID LIKE = %s" %(IDCardReadLine)
        sqlSearchName = "SELECT nameUser FROM Users WHERE userCardID LIKE = %s" %(IDCardReadLine)

        if(sqlSearchCardID != '' and sqlSearchName != ''):
            sqlQueryInsertData = "INSERT INTO Records (idRecord, recordData, recordDateTime, userCardID) VALUES (NULL, 'Registo ok por: %s', CURRENT_TIMESTAMP, '%s')" %(IDCardReadLine, IDCardReadLine)
            cursor.execute(sqlQueryInsertData)
            conn.commit()
        else:
            print "Error"

    ser.flush()


cursor.close()
ser.close()
conn.close();