__author__ = 'robdr'

import mysql.connector


class Data(object) :
    # set connector=
    def __init__(self) :
        super(Data, self).__init__()
        try :
            self.conn = mysql.connector.connect(host='localhost', database='db', user='root', password='')
        except :
            print('Failed to connect<br>')
            return
        self.cursor = self.conn.cursor()
        pass

    def __del__(self) :
        self.cursor.close()
        self.conn.close()
        pass

    # where class=
    def get_by_classname(self, cn) :
        if cn != '*' :
            cn = "students.class = '" + cn + "'"
        else :
            cn = "1"
        self.cursor.execute("SELECT students.name, students.state, e1.time, e1.date, e2.time, e2.date FROM students, events AS e1, events AS e2 WHERE e1.id = students.linkIn AND e2.id = students.linkOut AND " + cn)

        return self.cursor.fetchall()

    # where time=
    def get_by_datetime(self, dt) :
        # parse dt
        if (dt != '*') :
            dt = "events.date = '" + dt + "'"
        else :
            dt = "1"
        self.cursor.execute(
            "SELECT students.name, students.class, events.time, events.type, events.comment FROM students, events WHERE students.id = events.idStudent and " + dt)
        return self.cursor.fetchall()

    # where name=
    def get_by_name(self, name) :
        self.cursor.execute(
            "SELECT events.date, events.time, events.type, events.comment FROM students, events WHERE students.name = '" + name + "' and students.id = events.idStudent")
        return self.cursor.fetchall()

    # get id=
    def get_id_by_hash(self, hash) :
        # SELECT id from students WHERE hash=1234
        self.cursor.execute("SELECT id from students WHERE hash = " + str(hash))
        return self.cursor.fetchone()[0]

    # insert new event
    def insert_event(self, idStudent, type, comment) :
        import datetime
        now = datetime.datetime.now()
        query = "INSERT INTO `events`(`idStudent`, `date`, `time`, `type`, `comment`) VALUES ( %s, %s, %s, %s, %s)"
        data = (str(idStudent), now.strftime("%Y-%m-%d"), now.strftime("%H:%M"), str(type), str(comment))
        self.cursor.execute(query, data)
        self.conn.commit()
        query = "UPDATE `students` SET `state`=%s, "  + ("`linkIn`=%s" if (type == 0) else "`linkOut`=%s") + " WHERE id=" + str(idStudent)
        data = (str(type), str(self.cursor.lastrowid))
        self.cursor.execute(query, data)
        self.conn.commit()

    #insert student
    def insert_student(self, name, hash, cl) :
        query = "INSERT INTO `students` (`name`, `hash`, `class`, `state`) VALUES ( %s, %s, %s, %s)"
        data = (str(name), str(hash), str(cl), '0')
        self.cursor.execute(query, data)
        self.conn.commit()