def add_event(event_name, location): """ :param event_name: :param location: :return: """ query("""INSERT INTO EVENT (EVENT_NAME, LOCATION) VALUES (?, ?)""", [event_name, location])
def add_to_event(self, event_idx): """ :return: True - when add record to database table user_events False - when record exist """ self.event_idx = event_idx if not query("""SELECT * FROM USER_EVENTS WHERE USER_ID=? and EVENT_ID=?)""", [self.idx, event_idx]): query("""INSERT INTO USER_EVENTS (USER_ID, EVENT_ID) VALUES (?,?)""", [self.idx, event_idx]) return True return False
def add_user(mail, password): """ :param mail: :param password: :return: """ query( """INSERT INTO USERS (MAIL, PASSWORD, ACCOUNT_TYPE) VALUES (?, ?, ?)""", [mail, password, "user"])
def add_user(mail, password, account_type): """ :param mail: :param password: :param account_type: :return: True - when insert user to data base False - when that user exist in users database """ if not query("""SELECT * FROM USERS WHERE MAIL=?""", [mail]): query("""INSERT INTO USERS (MAIL, PASSWORD, ACCOUNT_TYPE) VALUES (?, ?, ?)""", [mail, password, account_type]) return True return False
def add_question(question, answer1, answer2, answer3, answer4, correct_ans, event_idx): """ :param question: :param answer1: :param answer2: :param answer3: :param answer4: :param correct_ans: :param event_idx: :return: """ query( """INSERT INTO QUESTION (QUESTION, ANSWER1, ANSWER2, ANSWER3, ANSWER4, CORRECT, ID_EVENT) VALUES (?, ?, ?, ?, ?, ?, ?)""", [ question, answer1, answer2, answer3, answer4, correct_ans, event_idx ])
def check_user_in_db_by_mail_model(mail, password): """ Check if user exists in database. :param mail: :param password: :return: list[ID, MAIL, ACCOUNT_TYPE] """ sql_obj = query("SELECT ID, MAIL, ACCOUNT_TYPE FROM USERS WHERE MAIL=? AND PASSWORD=?;", [mail, password]) if sql_obj: mail_password_list = [sql_obj[0][0], sql_obj[0][1], sql_obj[0][2]] return mail_password_list return None
def add_beacon(beacon_idx, location1, location2, location3, location4, correct, hint, event_idx): """ :param beacon_idx: :param location1: :param location2: :param location3: :param location4: :param correct: :param hint: :param event_idx: :return: """ query( """INSERT INTO BEACON_LOCATION (ID_BEACON, LOCATION1, LOCATION2, LOCATION3, LOCATION4, CORRECT, HINT) VALUES (?, ?, ?, ?, ?, ?, ?, ?)""", [ beacon_idx, location1, location2, location3, location4, correct, hint, event_idx ]) query("""INSERT INTO BEACON_EVENT (EVENT_ID, BEACON_ID) """, [event_idx, beacon_idx])
def create_beacon_object_by_id(cls, idx): """ return object of class beacon created by id :param idx: int idx of beacon :return: object of class BEACON or None if beacon not found """ beacon_query = """SELECT * FROM BEACON_LOCATION WHERE ID_BEACON = ?""" beacon = sql.query(beacon_query, [idx]) if beacon: beacon = cls(beacon[0][1], beacon[0][2], beacon[0][3], beacon[0][4], beacon[0][5], beacon[0][6], beacon[0][7]) return beacon return None
def random_beacon_id(): """ RANDOM IDX OF NON USED BEACON :return: RANDOM IDX OF NON USED BEACON or NONE IF NOT FOUND """ random_beacon_query = """SELECT BEACON_ID FROM BEACON_EVENT WHERE BEACON_EVENT.BEACON_ID NOT IN(SELECT BEACON_ID FROM USER_BEACONS WHERE USER_BEACONS.EVENT_ID = ? AND USER_BEACONS.USER_ID = ? ) AND BEACON_EVENT._ROWID_ >= (abs(random()) % (SELECT max(BEACON_EVENT._ROWID_) FROM BEACON_EVENT) + 1 ) LIMIT 1;""" random_beacon = sql.query(random_beacon_query, [1, 1]) return random_beacon[0][0]
def create_question_object_by_id(cls, idx): """ return object of class question created by id :param idx: int idx of question :return: object of class BEACON or None if question not found """ question_query = """SELECT * FROM QUESTION WHERE ID = ?""" question = sql.query(question_query, [idx]) if question: question = cls(question[0][0], question[0][1], question[0][2], question[0][3], question[0][4], question[0][5], question[0][6]) return question return None
def random_question_id(user_idx, event_idx): """ RANDOM IDX OF NON USED BEACON :return: RANDOM IDX OF NON USED BEACON or NONE IF NOT FOUND """ random_question_query = """SELECT ID FROM QUESTION WHERE ID NOT IN(SELECT QUESTION_ID FROM USER_QUESTIONS WHERE USER_ID = ? AND ID_EVENT = ? ) AND QUESTION._ROWID_ >= (abs(random()) % (SELECT max(QUESTION._ROWID_) FROM QUESTION) + 1 ) LIMIT 1;""" random_question = sql.query(random_question_query, [event_idx, user_idx]) return random_question[0][0]
def get_beacon(): beacon = sql.query("""SELECT * FROM BEACON WHERE USED=0""") beacon = beacon[0] sql.query("""UPDATE BEACON SET USED=1 WHERE ID=?""", [beacon[0]]) return "||".join([str(i) for i in beacon])
def get_question(): question = sql.query("""SELECT * FROM QUESTION WHERE USED=0""") question = question[0] sql.query("""UPDATE QUESTION SET USED=1 WHERE ID=?""", [question[0]]) return question[1:-1]
def get_question_mobile(): question = sql.query("""SELECT * FROM QUESTION WHERE USED=0""") question = question[0] sql.query("""UPDATE QUESTION SET USED=1 WHERE ID=?""", [question[0]]) return "||".join([str(i) for i in question])
def used_question(user_idx, question_idx): sql.query( """INSERT INTO USER_QUESTIONS (USER_ID, QUESTION_ID) VALUES (?, ?)""", [user_idx, question_idx])
def add_point_mobile(user_mail): if query("""SELECT POINTS_AMOUNT FROM USERS WHERE MAIL=?""", [user_mail]): query("""UPDATE USERS SET POINTS_AMOUNT= POINTS_AMOUNT + 1 WHERE MAIL=?""", [user_mail]) else: query("""UPDATE USERS SET POINTS_AMOUNT= 1 WHERE MAIL=?""", [user_mail])