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)