class Users:
def cadastrarUser(self, id):
try:
self.bd = Connection()
self.bd.executarSQL(
"INSERT INTO users (usuario) VALUES ('{0}')".format(id))
except:
print('Erro ao cadastrar usuario')
finally:
self.bd.cursor.close()
self.bd.connection.close()
def deletarUser(self, id_usuario):
try:
self.bd = Connection()
self.bd.executarSQL(
"DELETE FROM users WHERE id_usuario = {0}".format(id_usuario))
except:
print('Erro ao cadastrar usuario')
finally:
self.bd.cursor.close()
self.bd.connection.close()
def get_usuario_id(self, nome):
try:
self.bd = Connection()
return self.bd.findOne(
"SELECT id_usuario FROM users WHERE usuario = '{0}'".format(
nome))
except:
print('Erro no GET usuario by ID')
finally:
self.bd.cursor.close()
self.bd.connection.close()
class Links:
def __init__(self):
self.dominio = "http://127.0.0.1:5000/urls/"
def cadastra_urls(self, URL, userid):
self.shorturl = Links.gerar_codigo(self)
self.garanta = Validacoes()
URL = self.garanta.http_existe_na_url(URL)
try:
self.bd = Connection()
self.query = (
"INSERT INTO links (hits, url, shorturl, usuario_id) VALUES ({0}, '{1}', '{2}', {3})"
.format(0, URL, self.shorturl, userid))
self.bd.executarSQL(self.query)
return self.bd.findOne(
"SELECT * FROM links WHERE shorturl = '{0}'".format(
self.shorturl))
except:
print("Erro ao inserir as URLs no banco!")
finally:
self.bd.cursor.close()
self.bd.connection.close()
def gerar_codigo(self):
try:
self.bd = Connection()
self.caracteres = string.digits + string.ascii_letters
self.shorturl = self.dominio + ''.join(
random.choices(self.caracteres, k=4))
self.verifica_url_existe = self.bd.findOne(
"SELECT * FROM links WHERE shorturl = '{0}'".format(
self.shorturl))
if self.verifica_url_existe:
Links.gerar_codigo(self)
return self.shorturl
except:
print("Erro ao verificar as codigos no banco!")
finally:
self.bd.cursor.close()
self.bd.connection.close()
def find_url_original(self, codigo):
try:
self.bd = Connection()
self.urlOriginal = self.bd.findOne(
"SELECT url FROM links WHERE shorturl = '{0}'".format(
self.dominio + codigo))
return self.urlOriginal
except:
print("Erro ao pesquisar a URL original!")
finally:
self.bd.cursor.close()
self.bd.connection.close()
def registra_hits(self, codigo):
try:
self.bd = Connection()
self.bd.executarSQL(
"UPDATE links SET HITS = ((SELECT HITS FROM LINKS WHERE shorturl = '{0}') + 1) WHERE shorturl = '{1}'"
.format(self.dominio + codigo, self.dominio + codigo))
except:
print("Erro ao incrementar hit!")
finally:
self.bd.cursor.close()
self.bd.connection.close()
def get_stats_by_id(self, id):
try:
self.bd = Connection()
self.query = ("SELECT * FROM links WHERE id = {0}".format(id))
self.results = self.bd.findOne(self.query)
if not self.results:
return False
return self.results
except:
print("Erro no get stats")
finally:
self.bd.cursor.close()
self.bd.connection.close()
def get_stats_all(self):
try:
self.bd = Connection()
self.query = ("SELECT * FROM links".format(id))
self.results = self.bd.findOne(self.query)
return self.results
except:
print("Erro no get stats")
finally:
self.bd.cursor.close()
self.bd.connection.close()
def delete_url(self, id=None, id_usuario=None):
try:
if id:
self.query = ("DELETE FROM links WHERE id = {0}".format(id))
if id_usuario:
self.query = ("DELETE FROM links WHERE usuario_id = {0}".
format(id_usuario))
self.bd = Connection()
self.bd.executarSQL(self.query)
except:
print("Erro no deletar registro")
finally:
self.bd.cursor.close()
self.bd.connection.close()
def get_analitcs(self, id=None):
try:
if id:
self.bd = Connection()
self.query = ("select sum(hits), count(url) "
"from links L "
"join users U on L.usuario_id = U.id_usuario "
"where U.id_usuario = '{0}'".format(id))
self.hitsUrls = self.bd.findOne(self.query)
utils = Utils()
self.query = ("select * "
"from links L "
"join users U on L.usuario_id = U.id_usuario "
"where U.id_usuario = '{0}' "
"order by hits desc "
"fetch first 10 rows only".format(id))
self.top_hits = self.bd.findOne(self.query)
self.top_hits = utils.gera_dict(self.top_hits)
self.results = {
"urlCount": self.hitsUrls[0][1],
"hits": self.hitsUrls[0][0],
"topurls": self.top_hits
}
else:
self.bd = Connection()
self.query = "select sum(hits), count(url) from links"
self.hits_count = self.bd.findOne(
"select sum(hits), count(url) from links")
self.query = "select * from links order by hits desc fetch first 10 rows only"
self.top_hits = self.bd.findOne(self.query)
utils = Utils()
self.top_hits = utils.gera_dict(self.top_hits)
self.results = {
"urlCount": self.hits_count[0][1],
"hits": self.hits_count[0][0],
"topurls": self.top_hits
}
return self.results
except:
print("Erro ao gerar estatisticas do sistema")
finally:
self.bd.cursor.close()
self.bd.connection.close()
class Validacoes:
def __init__(self):
self.dominio = "http://127.0.0.1:5000/urls/"
def short_url_existe(self, codigo):
self.short_url = self.dominio + codigo
self.bd = Connection()
self.query = ("SELECT * FROM links WHERE shorturl = '{0}'".format(
self.short_url))
self.result = self.bd.findOne(self.query)
if not self.result:
return False
return True
def id_existe(self, id):
self.bd = Connection()
self.query = ("SELECT * FROM links WHERE id = {0}".format(id))
self.result = self.bd.findOne(self.query)
if not self.result:
return False
return True
def http_existe_na_url(self, URL):
if URL[:7] == 'http://' or URL[:8] == 'https://':
return URL
return 'http://' + URL
def existem_dados(self, id=None):
self.bd = Connection()
if id:
self.query = f"SELECT count(id) FROM links WHERE usuario_id = {id}"
else:
self.query = "SELECT * FROM links"
self.results = self.bd.findOne(self.query)
if self.results and self.results[0][0] != 0:
return True
return False
def user_existe(self, user=None, id=None):
self.bd = Connection()
if user:
self.query = (
"SELECT count(id_usuario) FROM users where usuario = '{0}'".
format(user))
if id:
self.query = (
"SELECT count(id_usuario) FROM users where id_usuario = {0}".
format(id))
self.result = self.bd.findOne(self.query)
if self.result[0][0] == 1:
return True
return False
def existe_url_do_usuario(self, id):
self.bd = Connection()
self.results = self.bd.findOne(
"SELECT count(id) FROM links WHERE id_usuario = {0}".format(id))
if self.results:
return True
return False