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)