class User:
def __init__(self):
self.db = Database()
self.utils = Utils()
def _select_all(self):
return self.db.query('SELECT * FROM User')
def _select_count_by_email(self, email):
return int(
self.db.query(
'SELECT COUNT(*) AS COUNT FROM User WHERE email = "' + email +
'"')[0]['COUNT'])
def _select_count_by_email_password(self, email, password):
return int(
self.db.query(
'SELECT COUNT(*) AS COUNT FROM User WHERE email = "' + email +
'" AND password = "******"')[0]['COUNT'])
def _insert(self, fullname, email, password):
return self.db.sql(
'INSERT INTO User(fullname, email, password) VALUES ("' +
fullname + '", "' + email + '", "' + self.utils.md5(password) +
'")')
def _delete_user(self, email, password):
return self.db.sql('DELETE FROM User WHERE email = "' + email +
'" AND password = "******"')
def _select_id_by_email(self, email):
return self.db.query('SELECT iduser FROM User WHERE email = "' +
email + '"')[0]['iduser']
def _select_all_by_userid(self, userid):
return self.db.query('SELECT * FROM User WHERE iduser = "******"')
def _update_but_password(self, fullname, email, description, userid):
return self.db.sql('UPDATE User SET fullname="' + fullname +
'", email="' + email + '", description="' +
description + '" WHERE iduser = "******"')
def _update_password(self, password, userid):
return self.db.sql('UPDATE User SET password="******" WHERE iduser = "******"')
def _delete(self, user_id):
self.db.sql('DELETE FROM VoteAnswer WHERE iduser = "******"')
self.db.sql('DELETE FROM VoteQuestion WHERE iduser = "******"')
self.db.sql('DELETE FROM Answer WHERE iduser = "******"')
self.db.sql(
'DELETE a FROM Answer a INNER JOIN Question q ON a.idquestion = q.idquestion WHERE q.iduser = "******"')
self.db.sql('DELETE FROM Question WHERE iduser = "******"')
self.db.sql('DELETE FROM User WHERE iduser = "******"')
def get_by_id(self, id):
data = self._select_all_by_userid(id)
if len(data) == 1:
return data[0]
else:
return False
def validate_register(self, fullname, email, password):
if not self.utils.validate_not_empty([fullname, email, password]):
return False
if self._select_count_by_email(email) == 0:
if self._insert(fullname, email, password) == 1:
return True
return False
def validate_login(self, email, password):
if not self.utils.validate_not_empty([email, password]):
return False
if self._select_count_by_email_password(email, password) == 1:
return True
return False
def validate_update(self, fullname, email, password, description, userid):
if not self.utils.validate_not_empty([fullname, email, userid]):
return False
r = False
if password != '':
self._update_password(password, userid)
self._update_but_password(fullname, email, description, userid)
return True
class Answer:
def __init__(self):
self.db = Database()
self.utils = Utils()
def _select_all(self):
return self.db.query('SELECT * FROM Answer')
def _select_all_by_questionid(self, questionid):
return self.db.query(
'SELECT a.idanswer, a.idquestion, a.iduser, a.description, (CASE WHEN b.rating IS NULL THEN 0 ELSE b.rating END) AS rating, DATE_FORMAT(a.data, "%d/%m/%Y %H:%i:%s") AS data, u.fullname AS user_fullname FROM Answer a LEFT JOIN (SELECT idanswer, SUM(vote) AS rating FROM VoteAnswer GROUP BY idanswer) b ON b.idanswer = a.idanswer INNER JOIN User u on a.iduser = u.iduser WHERE a.idquestion = "'
+ questionid + '"')
def _select_count_by_author(self, author):
return int(
self.db.query(
'SELECT COUNT(*) AS COUNT FROM Answer WHERE author = "' +
author + '"')[0]['COUNT'])
def _insert_vote(self, idanswer, iduser, vote):
return self.db.sql(
'INSERT INTO VoteAnswer(idanswer, iduser, vote) VALUES (' +
idanswer + ',' + iduser + ',' + vote + ')')
def _insert(self, idquestion, iduser, description):
return self.db.sql(
'INSERT INTO Answer(idquestion, iduser, description) VALUES ("' +
idquestion + '", "' + str(iduser) + '", "' + description + '")')
def _delete(self, answer_id, user_id):
return self.db.sql('DELETE FROM Answer WHERE idanswer = ' +
str(answer_id) + ' AND iduser = '******'UPDATE Answer SET description="' +
answer_description + '" WHERE idanswer = "' +
str(answer_id) + '"')
def get_by_user(self, user_id):
return self.db.query(
'SELECT a.idquestion, a.idanswer, a.description, DATE_FORMAT(a.data, "%d/%m/%Y %H:%i:%s") AS data, q.title, u.fullname FROM Answer a INNER JOIN Question q ON a.idquestion = q.idquestion INNER JOIN User u ON a.iduser = u.iduser WHERE a.iduser = "******"')
def remove_by_question_id(self, question_id):
return self.db.sql('DELETE FROM Answer WHERE idquestion = ' +
str(question_id))
def get_iduser_by_idanswer(self, answer_id):
return self.db.sql('SELECT iduser FROM Answer WHERE idanswer = "' +
str(answer_id) + '"')
def get_by_id(self, answer_id):
return self.db.query(
'SELECT q.title, a.iduser, a.description FROM Answer a INNER JOIN Question q ON a.idquestion = q.idquestion WHERE a.idanswer = "'
+ str(answer_id) + '"')[0]
def remove(self, answer_id, user_id):
self._delete(answer_id, user_id)
return True
def validate_answer_edit(self, description, user_id, answer_id):
if not self.utils.validate_not_empty([description, user_id, answer_id
]):
return False
if user_id:
return self._edit(description, answer_id)
else:
return False
def validate_answer_post(self, idquestion, iduser, description):
if not self.utils.validate_not_empty([idquestion, iduser, description
]):
return False
if iduser:
return self._insert(idquestion, iduser, description)
else:
return False
def vote(self, idanswer, iduser, vote):
self._insert_vote(idanswer, iduser, vote)
