def search(self):
from run import db_query
query = """SELECT id FROM public.member
WHERE status = 1 and city!='{}' LIMIT 1;"""
search_result = db_query.execute_query(query.format(self.city))
if len(search_result.value) < 1:
return False
else:
conn = db_query.create_db_connection()
cur = conn.cursor()
query = """INSERT INTO public.team(
count, points)
VALUES (2, 0)
RETURNING id;"""
team_insert_result = db_query.execute_query_wo_commit(cur, query)
conn.commit()
db_query.close_db_connection(cur, conn)
query = """ UPDATE public.member
SET team_id= {} , status = 2 WHERE id in ({},{});"""
update_result = db_query.execute_query(query.format(team_insert_result.value[0][0], self.id,search_result.value[0][0]), is_dml=True)
query = """ UPDATE public.member
SET task_id= 1 WHERE id = {};"""
set_task_result = db_query.execute_query(query.format(self.id), is_dml=True)
query = """ UPDATE public.member
SET task_id= 2 WHERE id = {};"""
set_task_result = db_query.execute_query(query.format(search_result.value[0][0]), is_dml=True)
return True
def get_task(self):
from run import db_query
query = """SELECT task_id FROM public.member
WHERE team_id = {} and id != {};"""
task_query_result = db_query.execute_query(query.format(self.team_id,self.id))
query = """SELECT value FROM public.task
WHERE id = {};"""
query_result = db_query.execute_query(query.format(task_query_result.value[0][0]))
return query_result.value[0][0]
def get_dictionary_by_name(item, value=None):
from run import db_query
if not value:
query_result = db_query.execute_query(""" SELECT *
FROM public.{}
ORDER BY id ASC;""".format(item))
return (query_result.value)
else:
query_result = db_query.execute_query(""" SELECT id
FROM public.{}
WHERE name='{}';""".format(item, value))
return (query_result.value[0][0])
def add_to_team(self):
from run import db_query
query = """SELECT id, count, points
FROM public.team
WHERE count = 1 LIMIT 1;"""
query_result = db_query.execute_query(query)
if len(query_result.value)>1:
team_id = query_result.value[0][0]
query = """ UPDATE public.member
SET team_id= {},status=2 WHERE id={};"""
query_result = db_query.execute_query(query.format(team_id,self.id), is_dml=True)
query = """ UPDATE public.team
SET count= 2 WHERE id={};"""
query_result = db_query.execute_query(query.format(team_id), is_dml=True)
else:
query = """INSERT INTO public.team(count, points)
VALUES ( 1, 0);"""
query_result = db_query.execute_query(query, is_dml=True)
self.add_to_team()
def get(self):
from run import db_query
query = """SELECT id, value
FROM public.task
WHERE id = {};"""
query_result = db_query.execute_query(query.format(self.id))
if len(query_result.value) < 1:
raise Exception
self.id = query_result.value[0][0]
self.value = query_result.value[0][1]
def get(self):
from run import db_query
query = """SELECT id, count, points
FROM public.team
WHERE id = {};"""
query_result = db_query.execute_query(query.format(self.id))
if len(query_result.value) < 1:
raise Exception
self.id = query_result.value[0][0]
self.count = query_result.value[0][1]
self.points = query_result.value[0][2]
def get(self):
from run import db_query
query = """SELECT id, password, status, team_id, city, task_id, answer
FROM public.member
WHERE {} = {};"""
query_result = db_query.execute_query(query.format('id', self.id))
if len(query_result.value) < 1:
raise Exception
self.id = query_result.value[0][0]
self.password = query_result.value[0][1]
self.status = query_result.value[0][2]
self.team_id = query_result.value[0][3]
self.city = query_result.value[0][4]
self.task_id = query_result.value[0][5]
self.answer = query_result.value[0][6]
def update_answer(self, new_answer):
from run import db_query
query = """ UPDATE public.member
SET answer= '{}' WHERE id={};"""
query_result = db_query.execute_query(query.format(new_answer, self.id), is_dml=True)
def update_task(self, new_task):
from run import db_query
query = """ UPDATE public.member
SET task_id= {} WHERE id={};"""
query_result = db_query.execute_query(query.format(new_task,self.id), is_dml=True)
def update_status(self, new_status):
from run import db_query
query = """ UPDATE public.member
SET status= {} WHERE id={};"""
query_result = db_query.execute_query(query.format(new_status,self.id), is_dml=True)
def team_status(self):
from run import db_query
query = """SELECT status FROM public.member
WHERE team_id = {};"""
query_result = db_query.execute_query(query.format(self.team_id))
return (query_result.value[0][0])
def get_answer(self):
from run import db_query
query = """SELECT answer FROM public.member
WHERE team_id = {} and id != {};"""
query_result = db_query.execute_query(query.format(self.team_id,self.id))
return (query_result.value[0][0])