def getfile(id, name):
"""
Retorna um arquivo em anexo.
"""
mime = mimetypes.guess_type(name)[0]
if mime is None:
mime = "application/octet-stream"
c = get_cursor()
c.execute(
"""
select files.ticket_id as ticket_id,
files.size as size,
files.contents as contents,
tickets.admin_only as admin_only
from files
join tickets
on tickets.id = files.ticket_id
where files.id = :id
""",
{"id": id},
)
row = c.fetchone()
blob = zlib.decompress(row["contents"])
if not user_admin(current_user()) and row["admin_only"] == 1:
return "você não tem permissão para acessar este recurso!"
else:
response.content_type = mime
return blob
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 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 user_admin(username: str) -> bool:
"""
Checa se usuário tem poderes administrativos.
"""
c = get_cursor()
c.execute(
"""
select is_admin
from users
where username = :username
""",
{"username": username},
)
return c.fetchone()["is_admin"]
def ticket_title(ticket_id) -> str:
"""
Retorna o título de um ticket.
"""
c = get_cursor()
c.execute(
"""
select title
from tickets
where id = :ticket_id
""",
{"ticket_id": ticket_id},
)
return c.fetchone()["title"]
def ticket_tags(ticket_id) -> List[str]:
"""
Retorna tags de um ticket.
"""
c = get_cursor()
c.execute(
"""
select tag
from tags
where ticket_id = :ticket_id
""",
{"ticket_id": ticket_id},
)
return [r["tag"] for r in c]
def user_ident(username: str) -> Dict[str, str]:
"""
Retorna o nome e e-mail de usuário.
"""
c = get_cursor()
c.execute(
"""
select name,
email
from users
where username=:username
""",
{"username": username},
)
return dict(c.fetchone())
def validate_session(session_id: str) -> bool:
"""
Valida sessão ativa no banco de dados.
"""
c = get_cursor()
c.execute(
"""
select session_id
from sessions
where session_id = :session_id
""",
{"session_id": session_id},
)
r = c.fetchone()
return bool(r)
def current_user() -> str:
"""
Retorna o usuário corrente.
"""
session_id = request.get_cookie(cookie_session_name())
c = get_cursor()
c.execute(
"""
select username
from sessions
where session_id = :session_id
""",
{"session_id": session_id},
)
return c.fetchone()["username"]
def validate_user_db(user: str, passwd: str) -> bool:
"""
Valida usuário e senha no banco de dados.
"""
c = get_cursor()
c.execute(
"""
select username
from users
where username = :user
and password = :passwdsha1
""",
{
"user": user,
"passwdsha1": hash_password(passwd)
},
)
r = c.fetchone()
return bool(r)
def tags_desc() -> Dict[str, Dict[str, str]]:
"""
Retorna as descrições de tags.
"""
tagdesc = {}
c = get_cursor()
c.execute("""
select tag,
description,
bgcolor,
fgcolor
from tagsdesc
""")
for r in c:
tagdesc[r["tag"]] = {
"description": r["description"] or "",
"bgcolor": r["bgcolor"] or "#00D6D6",
"fgcolor": r["fgcolor"] or "#4D4D4D",
}
return tagdesc
def ticket_depends(ticket_id) -> Dict[str, Dict[str, str]]:
"""
Retorna quais ticket dependem de um ticket.
"""
deps = {}
c = get_cursor()
c.execute(
"""
select d.ticket_id,
t.title,
t.status,
t.admin_only
from dependencies as d
inner join tickets as t
on t.id = d.ticket_id
where d.blocks = :ticket_id
""",
{"ticket_id": ticket_id},
)
for r in c:
deps[r[0]] = {"title": r[1], "status": r[2], "admin_only": r[3]}
return deps
def index():
"""
Lista tickets utilizando critérios de um filtro.
"""
# A página padrão exibe os tickets ordenados por prioridade.
if "filter" not in request.query.keys():
return redirect("/?filter=o:p")
filter = request.query.filter
if filter.strip() == "":
filter = "o:p"
# Redireciona ao ticket caso pesquisa seja #NNNNN
m = re.match(r"^#(\d+)$", filter)
if m:
return redirect("/ticket/%s" % m.group(1))
# Dividindo filtro em tokens separados por espaços
tokens = filter.strip().split()
limit = ""
search = []
status = "and status = 0"
order = "order by datemodified desc"
orderdate = "datemodified"
# Abrangência dos filtros (status)
# T: todos
# F: fechados
# A: abertos
if re.match(r"^[TFA] ", filter):
tr = {"T": "", "A": "and status = 0", "F": "and status = 1"}
status = tr[tokens[0]]
tokens.pop(0) # Removendo primeiro item
sql = "select * from tickets where ( 1 = 1 ) "
sqlparams = []
for t in tokens:
# Limite de resultados (l:NNN)
m = re.match(r"^l:(\d+)$", t)
if m:
limit = "limit %s " % m.group(1)
continue
# Palavra-chave (t:TAG)
m = re.match(r"^t:(.+)$", t)
if m:
sql += "and id in ( select ticket_id from tags where tag = ? ) "
sqlparams.append(m.group(1))
continue
# Ordenação (o:m)
m = re.match(r"^o:([mcfpv])$", t)
if m:
o = m.group(1)
if o == "c":
order = "order by datecreated desc "
orderdate = "datecreated"
elif o == "m":
order = "order by datemodified desc "
orderdate = "datemodified"
elif o == "f":
order = "order by dateclosed desc "
orderdate = "dateclosed"
elif o == "v":
order = "order by datedue asc "
orderdate = "datedue"
elif o == "p":
order = "order by priority asc, datecreated asc "
orderdate = ""
continue
# Usuário de criação, fechamento, modificação (u:USER)
m = re.match(r"^u:(.+)$", t)
if m:
u = m.group(1)
sql += """
and ( ( user = ? )
or ( id in
( select ticket_id from comments where user = ? )
)
or ( id in
( select ticket_id from timetrack where user = ? )
)
or ( id in
( select ticket_id from statustrack where user = ? ) )
)
"""
sqlparams += [u, u, u, u]
continue
# Faixa de data de criação, fechamento, modificação e previsão
m = re.match(
r"^d([fmcv]):(\d{4})(\d{2})(\d{2})-(\d{4})(\d{2})(\d{2})$", t)
if m:
dt = ""
y1, m1, d1, y2, m2, d2 = m.groups()[1:]
dt = {
"c": "datecreated",
"m": "datemodified",
"f": "dateclosed",
"v": "datedue",
}[m.group(1)]
sql += ("and %s between '%s-%s-%s 00:00:00' "
"and '%s-%s-%s 23:59:59' ") % (
dt,
y1,
m1,
d1,
y2,
m2,
d2,
)
continue
# Data de criação, fechamento, modificação e previsão
m = re.match(r"^d([fmc]):(\d{4})(\d{2})(\d{2})$", t)
if m:
dt = ""
y1, m1, d1 = m.groups()[1:]
dt = {
"c": "datecreated",
"m": "datemodified",
"f": "dateclosed",
"v": "datedue",
}[m.group(1)]
sql += ("and %s between '%s-%s-%s 00:00:00' "
"and '%s-%s-%s 23:59:59' ") % (
dt,
y1,
m1,
d1,
y1,
m1,
d1,
)
continue
# Faixa de prioridade (p:1-2)
m = re.match(r"^p:([1-5])-([1-5])$", t)
if m:
p1, p2 = m.groups()
sql += "and priority between %s and %s " % (p1, p2)
continue
# Prioridade (p:1)
m = re.match(r"^p:([1-5])$", t)
if m:
p1 = m.group(1)
sql += "and priority = %s " % (p1, )
continue
# Restrição de tickets (administrador, normal e todos)
m = re.match(r"^r:([ant])$", t)
if m:
a = {"a": "1", "n": "0", "t": "admin_only"}[m.group(1)]
sql += "and admin_only = %s " % a
continue
# Texto para busca
search.append(t)
ctx = TemplateContext()
# Caso usuário não seja administrador, vamos filtrar os tickets que ele não tem
# acesso.
if not ctx.user_is_admin:
sql += "and admin_only = 0 "
if len(search) > 0:
s = " ".join(search)
sql += "and id in ( select docid from search where search match ? ) "
sqlparams.append(s)
# Caso ordenação seja por data de previsão, mostrando somente tickets com date de
# previsão preenchida.
if orderdate == "datedue":
sql += "and datedue is not null "
# Caso ordenação seja por data de fechamento, mostrando somente os tickets fechados.
if orderdate == "dateclosed":
sql += "and status = 1 "
if status:
sql += "%s " % status
if order:
sql += "%s " % order
if limit:
sql += "%s " % limit
c = get_cursor()
c.execute(sql, sqlparams)
tickets = []
for t in c:
ticketdict = dict(t)
ticketdict["tags"] = ticket_tags(t["id"])
tickets.append(ticketdict)
ctx.tickets = tickets
ctx.filter = filter
ctx.orderdate = orderdate
ctx.tags_desc = tags_desc()
return dict(ctx=ctx)
def showticket(ticket_id):
"""
Exibe detalhes de um ticket.
"""
c = get_cursor()
# Obtém dados do ticket
ctx = TemplateContext()
sql_is_admin = ""
if not ctx.user_is_admin:
sql_is_admin = "and admin_only = 0"
c.execute(
"""
select *
from tickets
where id = :ticket_id
""" + sql_is_admin,
{"ticket_id": ticket_id},
)
ctx.ticket = c.fetchone()
if not ctx.ticket:
return "ticket inexistente!"
# Obtém notas, mudanças de status e registro de tempo
ctx.comments = []
# Mudanças de status
c.execute(
"""
select datecreated,
user,
status
from statustrack
where ticket_id = :ticket_id
""",
{"ticket_id": ticket_id},
)
for r in c:
reg = dict(r)
reg["type"] = "statustrack"
ctx.comments.append(reg)
# Comentários
c.execute(
"""
select datecreated,
user,
comment
from comments
where ticket_id = :ticket_id
""",
{"ticket_id": ticket_id},
)
for r in c:
reg = dict(r)
reg["comment"] = sanitize_comment(reg["comment"])
reg["type"] = "comments"
ctx.comments.append(reg)
# Registro de tempo
c.execute(
"""
select datecreated,
user,
minutes
from timetrack
where ticket_id = :ticket_id
""",
{"ticket_id": ticket_id},
)
for r in c:
reg = dict(r)
reg["type"] = "timetrack"
ctx.comments.append(reg)
# Arquivos anexos
c.execute(
"""
select datecreated,
user,
name,
id
from files
where ticket_id = :ticket_id
""",
{"ticket_id": ticket_id},
)
for r in c:
reg = dict(r)
reg["type"] = "files"
ctx.comments.append(reg)
# Ordenando comentários por data
ctx.comments = sorted(ctx.comments,
key=lambda comments: comments["datecreated"])
# Obtém resumo de tempo trabalhado
ctx.timetrack = []
c.execute(
"""
select user,
sum(minutes) as minutes
from timetrack
where ticket_id = :ticket_id
group by user
order by user
""",
{"ticket_id": ticket_id},
)
for r in c:
ctx.timetrack.append(dict(r))
# Obtém palavras-chave
ctx.tags = ticket_tags(ticket_id)
# Obtém dependências
ctx.blocks = ticket_blocks(ticket_id)
ctx.depends = ticket_depends(ticket_id)
ctx.user = user_ident(ctx.username)
get_db().commit()
# Renderiza template
return dict(ctx=ctx)
