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)