def changedependencies(ticket_id):
"""
Altera dependências de um ticket.
"""
assert "text" in request.forms
deps = request.forms.get("text")
deps = deps.strip().split()
# Validando dependências
for dep in deps:
# Valida sintaxe
if not re.match(r"^\d+$", dep):
return "sintaxe inválida para dependência: %s" % dep
# Valida se não é o mesmo ticket
dep = int(dep)
if dep == ticket_id:
return "ticket não pode bloquear ele mesmo"
# Valida se ticket existe
with db_trans() as c:
c.execute("SELECT count(*) FROM tickets WHERE id=:dep", locals())
if c.fetchone()[0] == 0:
return "ticket %s não existe" % dep
# Valida dependência circular
if ticket_id in ticket_blocks(dep):
return "dependência circular: %s" % dep
with db_trans() as c:
c.execute(
"""
delete from dependencies
where ticket_id = :ticket_id
""",
locals(),
)
for dep in deps:
c.execute(
"""
insert into dependencies (
ticket_id,
blocks
)
values (
:ticket_id,
:dep
)
""",
{
"ticket_id": ticket_id,
"dep": dep
},
)
return redirect("/ticket/%s" % ticket_id)
def newticketpost():
"""
Salva um novo ticket.
"""
assert "title" in request.forms
title = request.forms.get("title").strip()
if title == "":
return "erro: título inválido"
username = current_user()
with db_trans() as c:
c.execute(
"""
insert into tickets (
title,
user
)
values (
:title,
:username
)
""",
{
"title": title,
"username": username
},
)
ticket_id = c.lastrowid
populate_search(ticket_id)
return redirect("/ticket/%s" % ticket_id)
def changetags(ticket_id):
"""
Altera tags de um ticket.
"""
assert "text" in request.forms
tags = list(set(request.forms.get("text").strip().split()))
with db_trans() as c:
c.execute(
"""
delete from tags
where ticket_id = :ticket_id
""",
locals(),
)
for tag in tags:
c.execute(
"""
insert into tags (
ticket_id,
tag
)
values (
:ticket_id,
:tag
)""",
{
"ticket_id": ticket_id,
"tag": tag
},
)
return redirect("/ticket/%s" % ticket_id)
def changedatedue(ticket_id):
"""
Altera data de previsão de solução de um ticket.
"""
assert "datedue" in request.forms
datedue = request.forms.get("datedue").strip()
if datedue != "":
# Testando máscara
if not re.match(r"^2\d{3}-\d{2}-\d{2}$", datedue):
return "erro: data de previsão inválida"
# Testando validade da data
try:
time.strptime(datedue, "%Y-%m-%d")
except ValueError:
return "erro: data de previsão inválida"
datedue += " 23:59:59"
else:
datedue = None
with db_trans() as c:
c.execute(
"""
update tickets
set datedue = :datedue
where id = :ticket_id
""",
{
"datedue": datedue,
"ticket_id": ticket_id
},
)
return redirect("/ticket/%s" % ticket_id)
def user(username: str) -> User:
"""
Retorna os dados de um usuário específico.
"""
with db_trans() as c:
c.execute(
"""
select username,
is_admin,
name,
email
from users
where username = :username
""",
{"username": username},
)
row = c.fetchone()
if not row:
raise ValueError(f"usuário {username} não encontrado!")
return User(
username=row["username"],
is_admin=row["is_admin"],
name=row["name"],
email=row["email"],
)
def user_new(user: User, password: str):
"""
Salva um novo usuário.
"""
password = hash_password(password)
with db_trans() as c:
c.execute(
"""
insert into users (
username,
password,
is_admin
)
values (
:username,
:password,
0
)
""",
{
"username": user.username,
"password": password,
"is_admin": user.is_admin,
},
)
def uploadfile(ticket_id):
"""
Anexa um arquivo ao ticket.
"""
if "file" not in request.files:
return "arquivo inválido"
filename = request.files.get("file").filename
maxfilesize = int(cfg("attachments", "max-size"))
blob = b""
filesize = 0
while True:
chunk = request.files.get("file").file.read(4096)
if not chunk:
break
chunksize = len(chunk)
if filesize + chunksize > maxfilesize:
return "erro: arquivo maior do que máximo permitido"
filesize += chunksize
blob += chunk
log.debug(type(blob))
blob = zlib.compress(blob)
username = current_user()
with db_trans() as c:
c.execute(
"""
insert into files (
ticket_id,
name,
user,
size,
contents
)
values (
:ticket_id,
:filename,
:username,
:filesize,
:blob
)
""",
{
"ticket_id": ticket_id,
"filename": filename,
"username": username,
"filesize": filesize,
"blob": blob,
},
)
c.execute(
"""
update tickets
set datemodified = datetime('now', 'localtime')
where id = :ticket_id
""",
{"ticket_id": ticket_id},
)
return redirect("/ticket/%s" % ticket_id)
def expire_old_sessions():
"""
Expira sessões mais antigas que 7 dias.
"""
with db_trans() as c:
c.execute("""
delete from sessions
where julianday('now') - julianday(date_login) > 7
""")
def closeticket(ticket_id):
"""
Fecha um ticket.
"""
# Verifica se existem tickets que bloqueiam este ticket que ainda estão abertos.
c = get_cursor()
c.execute(
"""
select d.ticket_id as ticket_id
from dependencies as d
inner join tickets as t
on t.id = d.ticket_id
where d.blocks = :ticket_id
and t.status = 0
""",
{"ticket_id": ticket_id},
)
blocks = [r["ticket_id"] for r in c]
if blocks:
return ("os seguintes tickets bloqueiam este ticket e " +
"estão em aberto: %s" % " ".join([str(x) for x in blocks]))
username = current_user()
with db_trans() as c:
c.execute(
"""
update tickets
set status = 1,
dateclosed = datetime('now', 'localtime'),
datemodified = datetime('now', 'localtime')
where id = :ticket_id
""",
{"ticket_id": ticket_id},
)
c.execute(
"""
insert into statustrack (
ticket_id,
user,
status
)
values (
:ticket_id,
:username,
'close'
)
""",
{
"ticket_id": ticket_id,
"username": username
},
)
return redirect("/ticket/%s" % ticket_id)
def remove_session(session_id: str) -> None:
"""
Remove uma sessão do banco de dados.
"""
with db_trans() as c:
c.execute(
"""
delete from sessions
where session_id = :session_id
""",
{"session_id": session_id},
)
def user_remove(username: str):
"""
Remove um usuário.
"""
with db_trans() as c:
c.execute(
"""
delete from users
where username = :username
""",
{"username": username},
)
def reopenticket(ticket_id):
"""
Reabre um ticket.
"""
# Verifica se existem tickets bloqueados por este ticket que estão fechados.
c = get_cursor()
c.execute(
"""
select d.blocks as blocks
from dependencies as d
inner join tickets as t
on t.id = d.blocks
where d.ticket_id = :ticket_id
and t.status = 1
""",
{"ticket_id": ticket_id},
)
blocks = [r["blocks"] for r in c]
if blocks:
return ("os seguintes tickets são bloqueados por este ticket " +
"e estão fechados: %s" % " ".join([str(x) for x in blocks]))
username = current_user()
with db_trans() as c:
c.execute(
"""
update tickets
set status = 0,
dateclosed = null,
datemodified = datetime('now', 'localtime')
where id = :ticket_id
""",
{"ticket_id": ticket_id},
)
c.execute(
"""
insert into statustrack (
ticket_id,
user,
status
)
values (
:ticket_id,
:username,
'reopen'
)
""",
{
"ticket_id": ticket_id,
"username": username
},
)
return redirect("/ticket/%s" % ticket_id)
def recreate_fts():
"""
Recria os índices full-text-search do SQLite. Bom quando-se altera algum texto
diretamente no banco de dados.
"""
with db_trans() as c:
c.execute("""
delete from search
""")
c.execute("""
select id
from tickets
order by id
""")
for r in c:
populate_search(r["id"])
def change_password(user: str, password: str) -> None:
"""
Altera a senha de um usuário.
"""
with db_trans() as c:
c.execute(
"""
update users
set password = :passwd_sha1
where username = :user
""",
{
"passwd_sha1": hash_password(password),
"user": user
},
)
def user_password_save(username: str, password: str):
"""
Salva uma nova senha para um usuário.
"""
password = hash_password(password)
with db_trans() as c:
c.execute(
"""
update users
set password = :password
where username = :username
""",
{
"password": password,
"username": username
},
)
def changeadminonly(ticket_id, toggle):
"""
Tornar ticket somente visível para administradores.
"""
assert toggle in ("0", "1")
with db_trans() as c:
c.execute(
"""
update tickets
set admin_only = :toggle
where id = :ticket_id
""",
{
"toggle": toggle,
"ticket_id": ticket_id
},
)
return redirect("/ticket/%s" % ticket_id)
def user_save(user: User):
"""
Salva os dados de um usuário.
"""
with db_trans() as c:
c.execute(
"""
update users
set name = :name
, email = :email
, is_admin = :is_admin
where username = :username
""",
{
"name": user.name,
"email": user.email,
"is_admin": user.is_admin,
"username": user.username,
},
)
def changepriority(ticket_id):
"""
Altera a prioridade de um ticket.
"""
assert "prio" in request.forms
assert re.match(r"^[1-5]$", request.forms.get("prio"))
priority = int(request.forms.get("prio"))
with db_trans() as c:
c.execute(
"""
update tickets
set priority = :priority
where id = :ticket_id
""",
{
"priority": priority,
"ticket_id": ticket_id
},
)
return redirect("/ticket/%s" % ticket_id)
def changetitle(ticket_id):
"""
Altera título de um ticket.
"""
assert "text" in request.forms
title = request.forms.get("text").strip()
if title == "":
return "erro: título inválido"
with db_trans() as c:
c.execute(
"""
update tickets
set title = :title
where id = :ticket_id
""",
{
"title": title,
"ticket_id": ticket_id
},
)
populate_search(ticket_id)
return redirect("/ticket/%s" % ticket_id)
def registerminutes(ticket_id):
"""
Registra tempo trabalhado em um ticket.
"""
assert "minutes" in request.forms
if not re.match(r"^[\-0-9\.]+$", request.forms.get("minutes")):
return "tempo inválido"
minutes = float(request.forms.get("minutes"))
if minutes <= 0.0:
return "tempo inválido"
username = current_user()
with db_trans() as c:
c.execute(
"""
insert into timetrack (
ticket_id,
user,
minutes
)
values (
:ticket_id,
:username,
:minutes
)""",
{
"ticket_id": ticket_id,
"username": username,
"minutes": minutes
},
)
c.execute(
"""
update tickets
set datemodified = datetime('now', 'localtime')
where id = :ticket_id
""",
{"ticket_id": ticket_id},
)
return redirect("/ticket/%s" % ticket_id)
def all_users() -> list[User]:
"""
Retorna a lista de todos os usuários com seus dados, para tela de administração.
"""
users: list[User] = []
with db_trans() as c:
c.execute("""
select username,
is_admin,
name,
email
from users
order by username
""")
for row in c:
users.append(
User(
username=row["username"],
is_admin=row["is_admin"],
name=row["name"],
email=row["email"],
))
return users
def make_session(user: str) -> str:
"""
Cria uma nova sessão no banco de dados.
"""
with db_trans() as c:
session_id = str(uuid4())
c.execute(
"""
insert into sessions (
session_id,
username
)
values (
:session_id,
:user
)
""",
{
"session_id": session_id,
"user": user
},
)
return session_id
def newnote(ticket_id):
"""
Cria um novo comentário para um ticket.
"""
assert "text" in request.forms
contacts = []
if "contacts" in request.forms:
contacts = request.forms.get("contacts").strip().split()
note = request.forms.get("text")
if note.strip() == "":
return "nota inválida"
if len(contacts) > 0:
note += " [Notificação enviada para: %s]" % (", ".join(contacts))
username = current_user()
with db_trans() as c:
c.execute(
"""
insert into comments (
ticket_id,
user,
comment
)
values (
:ticket_id,
:username,
:note
)
""",
{
"ticket_id": ticket_id,
"username": username,
"note": note
},
)
c.execute(
"""
update tickets
set datemodified = datetime('now', 'localtime')
where id = :ticket_id
""",
{"ticket_id": ticket_id},
)
populate_search(ticket_id)
user = user_ident(username)
if len(contacts) > 0 and user["name"] and user["email"]:
title = ticket_title(ticket_id)
subject = "#%s - %s" % (ticket_id, title)
body = """
[%s] (%s):
%s
-- Este é um e-mail automático enviado pelo sistema ticket.
""" % (
time.strftime("%Y-%m-%d %H:%M"),
user["name"],
note,
)
send_mail(user["email"], contacts, cfg("smtp", "host"), subject, body)
return redirect("/ticket/%s" % ticket_id)