def create_user(nick_, email_, sex_, school, class_, birth_year_, birth_month_,
birth_day_, country_):
try:
token = get_random(30)
c = Config()
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
pass_ = get_random(8)
salt = random_salt()
password = encrypt_pass(pass_, salt)
query = "INSERT INTO users(nick, email, fk_roluser, password, salt, sex, school, class, birth_year, birth_month, birth_day, fk_country) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
data = (
nick_,
email_,
'Student',
password,
salt,
sex_,
school,
class_,
birth_year_,
birth_month_,
birth_day_,
country_,
)
cursor.execute(query, data)
cnx.commit()
cnx.close()
return True, pass_
except Exception as e:
print e
return False, ''
def remove_challenge_to_class_professor(FK_class_id_number_,
FK_challenge_id_number_):
try:
c = Config()
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "DELETE FROM class_challenges WHERE FK_class_id_number=%s AND FK_challenge_id_number = %s;"
data = (
FK_class_id_number_,
FK_challenge_id_number_,
)
cursor.execute(query, data)
row = cursor.rowcount
cnx.commit()
cnx.close()
# pendiente - remover estudiantes al desafio
if row >= 1:
return True, 'The challenge has been delete successfully of this class'
else:
return True, 'No challenge was eliminated'
except Exception as e:
return False, 'Something went wrong'
def student_init_it(student_nick, id_challenge):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT id_number, FK_student_nick, FK_challenge_id_number, last_response, number_of_interaction, init_date, end_date, finalized FROM Challenge_last_activity WHERE FK_student_nick = %s AND FK_challenge_id_number = %s "
data = (
student_nick,
id_challenge,
)
cursor.execute(query, data)
for (id_number, FK_student_nick, FK_challenge_id_number, last_response,
number_of_interaction, init_date, end_date, finalized) in cursor:
return True, [
id_number, FK_student_nick, FK_challenge_id_number,
last_response, number_of_interaction, init_date, end_date,
finalized
]
cursor.close()
cnx.close()
return False, []
except Exception as e:
print e
return False, []
def finish_challenge(id_challenge_, nick_student_, last_response, date_):
try:
c = Config()
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "UPDATE Challenge_last_activity SET last_response =%s, finalized = %s, end_date=%s WHERE FK_challenge_id_number =%s AND FK_student_nick =%s;"
data = (
last_response,
'1',
date_,
id_challenge_,
nick_student_,
)
cursor.execute(query, data)
cnx.commit()
row = cursor.rowcount
cnx.close()
if row >= 1:
add_interaction(id_challenge_, nick_student_)
return True, 'The challenge has been finished'
else:
return True, 'No changes detected in the challenge'
except Exception as e:
print e
return False, 'Something went wrong'
def edit_challenge_professor(id_number_challenge, title, summary, description,
aim, category, FK_owner_nick):
try:
c = Config()
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "UPDATE all_challenges SET title =%s, summary =%s, description =%s, aim =%s, last_edit=%s, fk_category =%s WHERE id_number =%s AND owner_fk_nick =%s;"
data = (
title,
summary,
description,
aim,
now,
category,
id_number_challenge,
FK_owner_nick,
)
cursor.execute(query, data)
cnx.commit()
row = cursor.rowcount
cnx.close()
# pendiente - remover estudiantes al desafio
if row >= 1:
return True, 'The challenge has been edited successfully'
else:
return True, 'No changes detected in the challenge'
except Exception as e:
print e
return False, 'Something went wrong'
def edit_class_info_professor(id_class_, school_, identificator_, class_,
year_, FK_owner_nick):
try:
c = Config()
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "UPDATE class_list SET school =%s, identificator =%s, class =%s, year =%s, d_last_modification =%s WHERE id_number =%s AND FK_owner_nick =%s;"
data = (
school_,
identificator_,
class_,
year_,
now,
id_class_,
FK_owner_nick,
)
cursor.execute(query, data)
cnx.commit()
row = cursor.rowcount
cnx.close()
if row >= 1:
return True, 'The class information, has been edited successfully'
else:
return True, 'No changes detected in the class'
except Exception as e:
print e
return False, 'Something went wrong'
def new_challenge_p(title_, summary_, description_, aims_, photo_, owner_,
category_):
try:
c = Config()
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
token_ = str(get_random_num(4)) + '-' + str(get_random_str(4))
query = "INSERT INTO all_challenges(title,token_challenge, photourl, summary, description, aim, created, owner_fk_nick, fk_category) VALUES (%s, %s, %s, %s, %s,%s, %s, %s, %s);"
data = (
str(title_),
str(token_),
str(photo_),
str(summary_),
str(description_),
str(aims_),
now,
str(owner_),
str(category_),
)
cursor.execute(query, data)
cnx.commit()
cnx.close()
return True
except Exception as e:
print e
return False
def get_all_categories_student(nick_student):
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT all_c.fk_category, categories.description FROM all_challenges as all_c INNER JOIN class_challenges INNER JOIN class_list INNER JOIN categories INNER JOIN classes WHERE class_challenges.FK_challenge_id_number = all_c.id_number AND class_list.id_number = class_challenges.FK_class_id_number AND categories.category= all_c.fk_category AND classes.FK_student_nick = %s;"
data = (nick_student, )
cursor.execute(query, data)
r = []
for (category, description) in cursor:
noesta = True
if len(r) != 0:
for x in r:
if x[0] == category:
noesta = False
if noesta:
r.append([category, description, 1])
else:
for x in r:
if x[0] == category:
x[2] = x[2] + 1
else:
r.append([category, description, 1])
cursor.close()
cnx.close()
return r
def get_all_challenges_student_no_finalized(student_nick):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT all_c.id_number, all_c.title, all_c.photourl, all_c.summary, all_c.description, all_c.aim, all_c.created, all_c.last_edit, all_c.fk_category ,all_c.owner_fk_nick, cla.init_date, cla.number_of_interaction, cla.last_response FROM all_challenges as all_c INNER JOIN class_challenges INNER JOIN Challenge_last_activity as cla INNER JOIN class_list INNER JOIN classes WHERE class_challenges.FK_challenge_id_number = all_c.id_number AND class_list.id_number = class_challenges.FK_class_id_number AND classes.FK_class_id_number = class_list.id_number AND classes.FK_student_nick = %s AND cla.FK_student_nick = classes.FK_student_nick and cla.FK_challenge_id_number = all_c.id_number and cla.finalized = '0' ORDER BY cla.number_of_interaction DESC;"
data = (student_nick, )
cursor.execute(query, data)
r = []
for (id_number, title, photourl, summary, description, aim, created,
last_edit, fk_category, owner_fk_nick, init_date,
number_of_interaction, last_response) in cursor:
temp = [
id_number, title, photourl, summary, description, aim, created,
last_edit, fk_category, owner_fk_nick, init_date,
number_of_interaction, last_response
]
r.append(temp)
cursor.close()
cnx.close()
return r
except Exception as e:
print e
print ' no final'
def get_all_challenges_student_by_cat(student_nick, category_):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT all_c.id_number, all_c.title, all_c.photourl, all_c.summary, all_c.description, all_c.aim, all_c.created, all_c.last_edit, all_c.owner_fk_nick FROM all_challenges as all_c INNER JOIN class_challenges INNER JOIN class_list INNER JOIN classes WHERE class_challenges.FK_challenge_id_number = all_c.id_number AND class_list.id_number = class_challenges.FK_class_id_number AND classes.FK_student_nick = %s AND all_c.fk_category = %s;"
data = (
student_nick,
category_,
)
cursor.execute(query, data)
r = []
for (id_number, title, photourl, summary, description, aim, created,
last_edit, owner_fk_nick) in cursor:
temp = [
id_number, title, photourl, summary, description, aim, created,
last_edit, owner_fk_nick
]
r.append(temp)
cursor.close()
cnx.close()
return r
except Exception as e:
print e
def new_class(school_, identificator_, class_, year_, _FK_owner_nick):
try:
c = Config()
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "INSERT INTO class_list(school, identificator, class, year, status, d_creation,FK_owner_nick) VALUES (%s, %s, %s,%s, %s, %s, %s);"
data = (
school_,
identificator_,
class_,
year_,
'active',
now,
str(_FK_owner_nick),
)
cursor.execute(query, data)
cnx.commit()
cnx.close()
return True, 'The class has been successfully entered'
except Exception as e:
print e
return False, 'Something went wrong'
def get_rol_of_user(user_):
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT fk_roluser FROM users WHERE nick = %s;"
data = (user_, )
cursor.execute(query, data)
for (rol_id) in cursor:
return rol_id[0]
cursor.close()
cnx.close()
return ''
def itwasintializedbyanystudent(idchallenge):
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT FK_challenge_id_number FROM Challenge_last_activity WHERE FK_challenge_id_number = %s"
data = (idchallenge, )
cursor.execute(query, data)
for (FK_challenge_id_number) in cursor:
return True
cursor.close()
cnx.close()
return False
def get_all_categories_profesor():
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT category, description FROM categories WHERE 1 = 1"
cursor.execute(query)
r = []
for (category, description) in cursor:
r.append([category, description])
cursor.close()
cnx.close()
return r
def get_all_countries():
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT country, descr FROM countries WHERE 1= 1;"
cursor.execute(query)
r = []
for (country, descr) in cursor:
r.append([str(country), str(descr)])
cursor.close()
cnx.close()
return r
def a_get_all_users():
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT nick, email, sex, school, class as classs, fk_roluser, birth_year, birth_month, birth_day, fk_country FROM users WHERE 1 = 1;"
cursor.execute(query,)
r = []
for (nick, email, sex, school, classs, fk_roluser, birth_year, birth_month, birth_day, fk_country) in cursor:
temp = [nick, email, sex, school, classs, fk_roluser, birth_year, birth_month, birth_day, fk_country]
r.append(temp)
cursor.close()
cnx.close()
return r
def get_email(user_):
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT email FROM users WHERE nick = %s"
data = (user_, )
cursor.execute(query, data)
for (email) in cursor:
r = str(email[0])
return r
cursor.close()
cnx.close()
return ''
def get_salt_of_user(user_):
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT salt FROM users WHERE nick = %s"
data = (user_, )
cursor.execute(query, data)
for (salt) in cursor:
r = str(salt[0])
return r
cursor.close()
cnx.close()
return r
def get_recovery_token(user_):
token = ''
c = Config()
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT token FROM recovery_pass_token WHERE used = 0 AND expire >= NOW() AND fk_nick = %s;"
data = (user_, )
cursor.execute(query, data)
for (token) in cursor:
return True, token
cursor.close()
cnx.close()
return False, token
def Get_last_response_challenge(id_challenge_, nick_student_):
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT last_response FROM Challenge_last_activity WHERE FK_challenge_id_number = %s AND FK_student_nick = %s"
data = (
id_challenge_,
nick_student_,
)
cursor.execute(query, data)
for (last_response) in cursor:
return last_response
cursor.close()
cnx.close()
return (u'-', )
def get_nick_by_email(email):
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
salt = get_salt_of_user(user_)
passw_salted = encrypt_pass(passw_, salt)
query = "SELECT nick, email FROM users WHERE email = %s "
data = (user_, passw_salted)
cursor.execute(query, data)
for (nick, email) in cursor:
return nick
cursor.close()
cnx.close()
r = False
return r
def country_exist_tocreate_user(country_):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT country FROM countries WHERE country = %s "
data = (country_, )
cursor.execute(query, data)
for (country) in cursor:
return True
cursor.close()
cnx.close()
return False
except Exception as e:
print e
return False
def email_exist_tocreate_user(email_):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT email FROM users WHERE email = %s "
data = (email_, )
cursor.execute(query, data)
for (email) in cursor:
return True
cursor.close()
cnx.close()
return False
except Exception as e:
print e
return True
def challenge_init(id_challenge_, nick_student_):
try:
c = Config()
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "INSERT INTO Challenge_last_activity(FK_student_nick ,FK_challenge_id_number ,last_response, number_of_interaction, init_date, finalized) VALUES (%s, %s, %s, %s, %s,%s);"
data = (nick_student_, id_challenge_, '', 1, now, 0)
cursor.execute(query, data)
cnx.commit()
cnx.close()
return True
except Exception as e:
print e
return False
def updatestatus_recovery_token(user_, token_):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
semi_query = ("UPDATE recovery_pass_token "
"SET used=%i "
"WHERE fk_nick=%s AND token=%s;")
data = (1, user_, token_)
cursor.execute(semi_query, data)
cnx.commit()
cnx.close()
return True
except Exception as e:
print e
return False
def login_user(user_, passw_):
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
salt = get_salt_of_user(user_)
passw_salted = encrypt_pass(passw_, salt)
query = "SELECT nick FROM users WHERE nick = %s AND password = %s "
data = (user_, passw_salted)
cursor.execute(query, data)
for (nick) in cursor:
r = True
return r
cursor.close()
cnx.close()
r = False
return r
def get_id_challenge_by_token(token_):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT id_number, token_challenge FROM all_challenges WHERE token_challenge = %s;"
data = (token_, )
cursor.execute(query, data)
for (id_number, token_challenge) in cursor:
return int(id_number)
cursor.close()
cnx.close()
return 0
except Exception as e:
print e
return 0
def new_permission(id_challenge_, nick_user):
try:
c = Config()
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "INSERT INTO evaluation_permissions(FK_all_challenges_id,FK_nick_evaluator) VALUES (%s, %s);"
data = (
id_challenge_,
nick_user,
)
cursor.execute(query, data)
cnx.commit()
cnx.close()
except Exception as e:
print e
def get_all_students_by_id_challenge(id_challenge):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT classes.FK_student_nick FROM class_challenges as cc INNER JOIN classes WHERE classes.FK_class_id_number = cc.FK_class_id_number AND cc.FK_challenge_id_number = %s "
data = (id_challenge, )
cursor.execute(query, data)
r = []
for (FK_student_nick) in cursor:
r.append(FK_student_nick)
cursor.close()
cnx.close()
return r
except Exception as e:
print e
return []
def get_all_nicks_tudents():
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT nick, email FROM users WHERE fk_roluser = %s "
data = ('Student', )
cursor.execute(query, data)
r = []
for (nick, email) in cursor:
r.append([nick])
cursor.close()
cnx.close()
return r
except Exception as e:
print e
return []