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])
