def edit_rubic(title_, description_, datajson_, id_challenge, id_rubic):
response = -1
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "UPDATE rubic SET title=%s , description=%s, last_modified_datetime=%s, datajson=%s, FK_CHALLENGE_id_number=%s WHERE id_number=%s "
data = (
title_,
description_,
str(now),
datajson_,
id_challenge,
id_rubic,
)
cursor.execute(query, data)
cnx.commit()
response = int(cursor.lastrowid)
cnx.close()
return response
except Exception as e:
print(e)
return response
def new_template_rubic(title_, description_, datajson_, fk_user_email):
response = -1
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "INSERT INTO rubic_template(title, description, create_datetime, datajson, status, FK_USERS_email) VALUES (%s, %s, %s, %s, %s, %s);"
data = (
title_,
description_,
str(now),
datajson_,
"show",
fk_user_email,
)
cursor.execute(query, data)
cnx.commit()
response = int(cursor.lastrowid)
cnx.close()
return response
except Exception as e:
print(e)
return response
def edit_template_challenge(id_challenge_, title_, description_, photo_, aim_,
FK_CATEGORY_category_name_, FK_USER_email):
response = -1
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "UPDATE challenge_template SET title=%s , description=%s, photo=%s, aim=%s, last_modified_datetime=%s, FK_CATEGORY_category_name=%s WHERE FK_USERS_email=%s AND id_challenge = %s"
data = (
title_,
description_,
photo_,
aim_,
now,
FK_CATEGORY_category_name_,
FK_USER_email,
id_challenge_,
)
cursor.execute(query, data)
cnx.commit()
response = int(cursor.lastrowid)
cnx.close()
return response
except Exception as e:
print(e)
return response
def get_all_my_challenges(user_email):
r = []
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT ch.id_number,ch.title,ch.description,ch.token,ch.photo,ch.aim,ch.create_datetime,ch.last_modified_datetime,ch.FK_DEADLINE_type,ch.deadline_value,ch.FK_CLASSROOM_id_number,ch.FK_CATEGORY_category_name FROM challenge as ch INNER JOIN classroom as cl WHERE cl.FK_USERS_email = %s AND cl.id_number = ch.FK_CLASSROOM_id_number"
data = (user_email, )
cursor.execute(query, data)
for (id_number, title, description, token, photo, aim, create_datetime,
last_modified_datetime, FK_DEADLINE_type, deadline_value,
FK_CLASSROOM_id_number, FK_CATEGORY_category_name) in cursor:
r.append([
id_number, title, description, token, photo, aim,
create_datetime, last_modified_datetime, FK_DEADLINE_type,
deadline_value, FK_CLASSROOM_id_number,
FK_CATEGORY_category_name
])
cursor.close()
cnx.close()
return r
except Exception as e:
print('Error #1 en la base de datos')
print(e)
return r
def iam_challenge_owner_by_id(id_challenge_, FK_USER_email):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT ch.id_number as iddd FROM classroom as cl INNER JOIN challenge as ch WHERE cl.id_number = ch.FK_CLASSROOM_id_number AND ch.id_number=%s AND cl.FK_USERS_email=%s "
data_ = (
id_challenge_,
FK_USER_email,
)
cursor.execute(
query,
data_,
)
for (iddd) in cursor:
return True
cursor.close()
cnx.close()
return False
except Exception as e:
print('Error #1 en la base de datos')
print(e)
return False
#### ESTUDIANTE
def new_template_challenge(title_, description_, photo_, aim_,
FK_CATEGORY_category_name_, FK_USER_email):
response = -1
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "INSERT INTO challenge_template(title, description, token, photo, aim ,added_datetime, status ,FK_CATEGORY_category_name) VALUES (%s, %s, %s, %s, %s, %s, %s, %s);"
data = (
title_,
description_,
photo_,
aim_,
now,
"show",
FK_CATEGORY_category_name_,
FK_USER_email,
)
cursor.execute(query, data)
cnx.commit()
response = int(cursor.lastrowid)
cnx.close()
return response
except Exception as e:
print(e)
return response
def get_all_templates_challenge():
r = []
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT id_number, title, description, photo, aim, added_datetime, last_modified_datetime, FK_CATEGORY_category_name, FK_USERS_email FROM challenge_template WHERE status =%s"
cursor.execute(
query,
"show",
)
for (id_number, title, description, photo, aim, added_datetime,
last_modified_datetime, FK_CATEGORY_category_name,
FK_USERS_email) in cursor:
r.append([
id_number, title, description, photo, aim, added_datetime,
last_modified_datetime, FK_CATEGORY_category_name,
FK_USERS_email
])
cursor.close()
cnx.close()
return r
except Exception as e:
print('Error #1 en la base de datos')
print(e)
return r
def get_all_my_rubics(fk_user_email):
r = []
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT ru.id_number, ru.title, ru.description, ru.create_datetime, ru.last_modified_datetime, ru.datajson, ru.FK_CHALLENGE_id_number FROM rubic as ru INNER JOIN challenge as ch INNER JOIN classroom as cl WHERE cl.id_number = ch.FK_CLASSROOM_id_number AND ru.FK_CHALLENGE_id_number=ch.id_number AND cl.FK_USERS_email=%s"
data_ = (fk_user_email, )
cursor.execute(
query,
data_,
)
for (id_number, title, description, create_datetime,
last_modified_datetime, datajson,
FK_CHALLENGE_id_number) in cursor:
r.append([
id_number, title, description, create_datetime,
last_modified_datetime, datajson, FK_CHALLENGE_id_number
])
cursor.close()
cnx.close()
return r
except Exception as e:
print('Error #1 en la base de datos')
print(e)
return r
def edit_challenge(id_challenge_, title_, description_, photo_, aim_,
FK_DEADLINE_type_, deadline_value_, FK_CLASSROOM_id_number_,
FK_CATEGORY_category_name_):
response = -1
try:
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 = "UPDATE challenge SET title=%s , description=%s, token=%s, photo=%s, aim=%s, last_modified_datetime=%s, FK_DEADLINE_type=%s, deadline_value=%s, FK_CLASSROOM_id_number=%s, FK_CATEGORY_category_name=%s WHERE id_challenge = %s"
data = (
title_,
description_,
token_,
photo_,
aim_,
now,
FK_DEADLINE_type_,
deadline_value_,
FK_CLASSROOM_id_number_,
FK_CATEGORY_category_name_,
id_challenge_,
)
cursor.execute(query, data)
cnx.commit()
response = int(cursor.lastrowid)
cnx.close()
return response
except Exception as e:
print(e)
return response
def new_challenge(title_, description_, photo_, aim_, fk_deadline_type_,
deadline_value_, FK_CLASSROOM_id_number_,
FK_CATEGORY_category_name_):
response = -1
try:
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 challenge(title,description,token,photo,aim,create_datetime,FK_DEADLINE_type,deadline_value,FK_CLASSROOM_id_number,FK_CATEGORY_category_name) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
data = (
title_,
description_,
token_,
photo_,
aim_,
now,
fk_deadline_type_,
deadline_value_,
FK_CLASSROOM_id_number_,
FK_CATEGORY_category_name_,
)
cursor.execute(query, data)
cnx.commit()
response = int(cursor.lastrowid)
cnx.close()
return response
except Exception as e:
print(e)
return response
def insert_request_log(endpoint_,
request_type_,
data_,
ip_,
fk_user_email=None):
response = -1
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "INSERT INTO request_log(endpoint,request_type, data, reqdatetime, ip, FK_USERS_email) VALUES (%s, %s, %s, %s, %s, %s);"
data = (
endpoint_,
request_type_,
data_,
str(now),
ip_,
fk_user_email,
)
cursor.execute(query, data)
cnx.commit()
response = int(cursor.lastrowid)
cnx.close()
return response
except Exception as e:
print(e)
return response
def restar_reco(id_query):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "UPDATE query_result SET popularidad = popularidad - 1 WHERE id_result =%s;"
data = (id_query,)
cursor.execute(query, data)
cnx.commit()
cnx.close()
return True
except Exception as e:
print(e)
return False
def fin_de_desafio_reg(usuario_,n_desafio):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "INSERT INTO r_usuario_challenge(FK_USUARIO_nickname,FK_CHALLENGE_id_number, estado) VALUES (%s, %s, %s);"
data = (usuario_,n_desafio,'finalizado')
cursor.execute(query, data)
cnx.commit()
cnx.close()
return True
except Exception as e:
print(e)
return False
def insertar_nuevo_registro(tipo_, data_, usuario_):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
now = datetime.now()
query = "INSERT INTO logs(log_type, data, server_datetime,FK_USUARIO_nickname) VALUES (%s, %s, %s,%s);"
data = (tipo_, data_, now, usuario_)
cursor.execute(query, data)
cnx.commit()
cnx.close()
return True
except Exception as e:
print(e)
return False
def this_user_exist(user_, email_):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT email FROM users WHERE email=%s OR users =%s"
data = (email_,user_,)
cursor.execute(query, data)
for (email) in cursor:
return True
cursor.close()
cnx.close()
return False
except Exception as e:
print(str(e))
return False
def get_all_categories():
r = []
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT category_name, description FROM category"
cursor.execute(query, )
for (category_name, description) in cursor:
r.append([category_name, description])
cursor.close()
cnx.close()
return r
except Exception as e:
print('Error #1 en la base de datos')
print(e)
return r
def get_all_contries():
r = []
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT country FROM country"
cursor.execute(query)
for (country) in cursor:
r.append(str(country[0]))
cursor.close()
cnx.close()
return r
except Exception as e:
print('Error #1 en la base de datos')
print(e)
return r
def Get_posttest():
r = []
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT id_test,test_texto,tipo_pregunta FROM pre_pos_test WHERE test_tipopregunta = 'posttest';"
cursor.execute(query,)
for (id_test,test_texto,tipo_pregunta) in cursor:
r.append([{"id":id_test,"text":test_texto,"tipopregunta":tipo_pregunta}])
cursor.close()
cnx.close()
return r
except Exception as e:
print('Error #2 en la base de datos')
print(e)
return r
def login_user(user_, passw_):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT FK_USUARIO_TIPO_tipo_usuario FROM usuario WHERE nickname = %s AND password = %s"
data = (user_,passw_,)
cursor.execute(query, data)
for (FK_USUARIO_TIPO_tipo_usuario) in cursor:
return FK_USUARIO_TIPO_tipo_usuario[0]
cursor.close()
cnx.close()
return ""
except Exception as e:
print('Error #2 en la base de datos')
print(e)
return ""
def login_user(user_, passw_):
try:
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 nickname, email,FK_ROL_rol_name FROM users WHERE (nickname = %s or email = %s) AND passw = %s"
data = (user_,user_, passw_salted,)
cursor.execute(query, data)
for (nickname, email,FK_ROL_rol_name) in cursor:
return [nickname, email, FK_ROL_rol_name]
cursor.close()
cnx.close()
return []
except Exception as e:
print(str(e))
return 'Error #2 en la base de datos'
def crear_usuario(email_, nickname_, firstname_, lastname_, passw_, birthdat_datetime_, contry_, type_):
response = -1
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
salt = random_salt()
passw_salted = encrypt_pass(passw_, salt)
query = "INSERT INTO `users` (`email`, `nickname`, `firstname`, `lastname`, `passw`, `salt`, `birthday_datetime`, `FK_COUNTRY_country`, `FK_ROL_rol_name`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s);"
data = (email_, nickname_, firstname_, lastname_, passw_salted, salt, birthdat_datetime_, contry_, type_,)
cursor.execute(query, data)
cnx.commit()
response = int(cursor.lastrowid)
cnx.close()
return response
except Exception as e:
print(e)
return response
def get_all_my_class_student(email_user):
r = []
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT room.id_number, room.classroom_name, users.firstname, users.lastname FROM classmate as mate INNER JOIN classroom as room INNER JOIN users WHERE room.FK_USERS_email = users.email AND mate.FK_CLASSROOM_id_number = room.id_number and mate.FK_USERS_email = %s"
data = (email_user, )
cursor.execute(query, data)
for (id_number, classroom_name, firstname, lastname) in cursor:
r.append([id_number, classroom_name, firstname, lastname])
cursor.close()
cnx.close()
return r
except Exception as e:
print('Error #1 en la base de datos | get_all_my_class_student')
print(e)
return r
def Get_challenges_by_id(user_,id_desafio):
r = {}
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT id_number, titulo, objetivos, descripcion FROM challenge WHERE id_number = %s;"
data=(id_desafio,)
cursor.execute(query,data)
for (id_number, titulo, objetivos, descripcion) in cursor:
estado = Get_challenge_state(user_, id_desafio)
return {"titulo":titulo, "objetivo":objetivos,"descripcion":descripcion, "estado":estado}
return r
except Exception as e:
print('Error #2 en la base de datos')
print(e)
return r
def get_all_my_challenges_profesor(email_user):
r = []
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT id_number, classroom_name FROM `classroom` WHERE FK_USERS_email =%s"
data = (email_user, )
cursor.execute(query, data)
for (id_number, classroom_name) in cursor:
r.append([id_number, classroom_name])
cursor.close()
cnx.close()
return r
except Exception as e:
print('Error #1 en la base de datos | get_all_my_challenges_profesor')
print(e)
return r
def get_all_students_in_challenge(id_challenge):
r = []
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT FK_USERS_email FROM challenge_user WHERE FK_CHALLENGE_id_number = %s"
data = (id_challenge, )
cursor.execute(query, data)
for (FK_USERS_email) in cursor:
r.append(str(FK_USERS_email[0]))
cursor.close()
cnx.close()
return r
except Exception as e:
print('Error #1 en la base de datos')
print(e)
return r
def Get_all_challenges(user_):
r = []
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT id_number, titulo, objetivos, descripcion FROM challenge WHERE 1;"
cursor.execute(query,)
for (id_number, titulo, objetivos, descripcion) in cursor:
estado = Get_challenge_state(user_,id_number)
r.append([id_number, titulo, objetivos, descripcion, estado])
cursor.close()
cnx.close()
return r
except Exception as e:
print('Error #2 en la base de datos')
print(e)
return r
def edit_class_profesor_(id_class, new_classname):
response = -1
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "UPDATE challenge SET classroom_name=%s WHERE id_number=%s"
data = (
new_classname,
id_class,
)
cursor.execute(query, data)
cnx.commit()
response = int(cursor.lastrowid)
cnx.close()
return response
except Exception as e:
print(e)
return response
def Get_challenge_state(user_,id_number):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT estado FROM r_usuario_challenge WHERE FK_CHALLENGE_id_number = %s AND FK_USUARIO_nickname = %s"
data = (id_number,user_,)
print(id_number)
print(user_)
cursor.execute(query, data)
for (estado) in cursor:
print(estado)
return estado[0]
cursor.close()
cnx.close()
return ""
except Exception as e:
print('Error #2 en la base de datos')
print(e)
return ""
def this_user_below_to_this_class(FK_USER_EMAIL, id_class):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT * FROM classmate WHERE FK_CLASSROOM_id_number=%s and FK_USERS_email=%s"
data = (
id_class,
FK_USER_EMAIL,
)
cursor.execute(query, data)
for (id_number) in cursor:
return True
cursor.close()
cnx.close()
return False
except Exception as e:
print('Error #1 en la base de datos | this_user_below_to_this_class')
print(e)
return False
def this_challenge_have_rubic(id_challenge_):
try:
database_ = Database()
config = database_.config
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = "SELECT id_number FROM `rubic` WHERE FK_CHALLENGE_id_number=%s "
data_ = (id_challenge_, )
cursor.execute(
query,
data_,
)
for (id_number) in cursor:
return True
cursor.close()
cnx.close()
return False
except Exception as e:
print('Error #1 en la base de datos')
print(e)
return False
