import gradio as gr import sqlite3 import bcrypt import pandas as pd from pathlib import Path from datetime import datetime import plotly.figure_factory as ff import plotly.graph_objects as go # ====================================================== # Configuração # ====================================================== BASE_DIR = Path(__file__).parent DB_PATH = BASE_DIR / "db" / "app.db" DB_PATH.parent.mkdir(parents=True, exist_ok=True) # Inicializar base de dados automaticamente try: from init_db import init_database init_database() except Exception as e: print(f"Aviso ao inicializar BD: {e}") # ====================================================== # Funções de Base de Dados # ====================================================== def get_conn(): """Retorna conexão com BD""" conn = sqlite3.connect(DB_PATH, timeout=30) conn.execute("PRAGMA foreign_keys = ON") return conn # ====================================================== # Utilitários # ====================================================== def slots_30_min(): """Gera slots de 30 em 30 minutos""" return [f"{h:02d}:{m:02d}" for h in range(24) for m in (0, 30)] def calcular_horas(start_date, start_time, end_date, end_time): """Calcula horas entre duas datas/horas""" if not all([start_date, start_time, end_date, end_time]): raise ValueError("Preencha todos os campos") try: # Aceitar formato de gr.DateTime (pode vir como "2026-02-03" ou "2026-02-03 00:00:00") if isinstance(start_date, str) and " " in start_date: start_date = start_date.split()[0] if isinstance(end_date, str) and " " in end_date: end_date = end_date.split()[0] inicio = datetime.strptime(f"{start_date} {start_time}", "%Y-%m-%d %H:%M") fim = datetime.strptime(f"{end_date} {end_time}", "%Y-%m-%d %H:%M") except ValueError as e: raise ValueError(f"Formato inválido: {e}") if fim <= inicio: raise ValueError("Data/hora fim deve ser posterior ao início") return round((fim - inicio).total_seconds() / 3600, 2) def calcular_horas_preview(start_date, start_time, end_date, end_time): """Preview de horas calculadas""" try: if not all([start_date, start_time, end_date, end_time]): return "" horas = calcular_horas(start_date, start_time, end_date, end_time) return f"{horas} h" except: return "" # ====================================================== # Autenticação # ====================================================== def login_user(username, password): """Faz login do utilizador - retorna username""" if not username or not password: return None, "❌ Preencha todos os campos", "⚠️ **Não autenticado**" conn = get_conn() cur = conn.cursor() cur.execute("SELECT username, password_hash FROM users WHERE username = ?", (username,)) row = cur.fetchone() if not row: conn.close() return None, "❌ Utilizador não encontrado", "⚠️ **Não autenticado**" db_username, pw_hash = row if bcrypt.checkpw(password.encode(), pw_hash.encode()): conn.close() indicator = f"## ✅ Logado: **{db_username}**" return db_username, f"✅ Bem-vindo, {db_username}!", indicator conn.close() return None, "❌ Password incorreta", "⚠️ **Não autenticado**" def create_user(username, password): """Cria novo utilizador""" if not username or not password: return "❌ Preencha todos os campos" if len(password) < 6: return "❌ Password deve ter pelo menos 6 caracteres" conn = get_conn() cur = conn.cursor() pw_hash = bcrypt.hashpw(password.encode(), bcrypt.gensalt()).decode() try: cur.execute("INSERT INTO users (username, password_hash) VALUES (?, ?)", (username, pw_hash)) conn.commit() return f"✅ Utilizador '{username}' criado! Faça login agora." except sqlite3.IntegrityError: return f"⚠️ Utilizador '{username}' já existe" finally: conn.close() # ====================================================== # CRUD Tarefas # ====================================================== def get_task_codes(username): """Lista códigos de tarefas do utilizador""" if username is None: return [] conn = get_conn() cur = conn.cursor() cur.execute("SELECT task_code FROM tasks WHERE username = ? ORDER BY id DESC", (username,)) codes = [row[0] for row in cur.fetchall()] conn.close() return codes def add_task(username, task_name, start_date, start_time, end_date, end_time): """Adiciona nova tarefa""" if username is None: return "", "❌ Faça login primeiro", gr.Dropdown(choices=[]) if not task_name or not task_name.strip(): return "", "❌ Preencha a descrição da tarefa", gr.Dropdown(choices=[]) try: planned_hours = calcular_horas(start_date, start_time, end_date, end_time) except ValueError as e: return "", f"❌ {str(e)}", gr.Dropdown(choices=[]) conn = get_conn() cur = conn.cursor() try: # Gerar código com username cur.execute("SELECT COUNT(*) FROM tasks WHERE username = ?", (username,)) seq = cur.fetchone()[0] + 1 task_code = f"TAR_{username}_{seq:04d}" # Limpar formato de data if isinstance(start_date, str) and " " in start_date: start_date = start_date.split()[0] if isinstance(end_date, str) and " " in end_date: end_date = end_date.split()[0] cur.execute(""" INSERT INTO tasks (username, task_code, task_name, task_date, start_time, end_time, planned_hours) VALUES (?, ?, ?, ?, ?, ?, ?) """, (username, task_code, task_name, start_date, f"{start_date} {start_time}:00", f"{end_date} {end_time}:00", planned_hours)) conn.commit() # Atualizar lista task_codes = get_task_codes(username) return f"{planned_hours} h", f"✅ Tarefa {task_code} criada!", gr.Dropdown(choices=task_codes, value=task_code) except Exception as e: conn.rollback() return "", f"❌ Erro: {e}", gr.Dropdown(choices=[]) finally: conn.close() def get_task_by_code(username, task_code): """Busca tarefa por código""" if not task_code or username is None: return "", "", "09:00", "", "17:00", "" conn = get_conn() cur = conn.cursor() cur.execute(""" SELECT task_name, start_time, end_time, planned_hours FROM tasks WHERE username = ? AND task_code = ? """, (username, task_code)) row = cur.fetchone() conn.close() if not row: return "", "", "09:00", "", "17:00", "" task_name, start_time, end_time, planned_hours = row # Parse timestamps start_dt = datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S") end_dt = datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S") return ( task_name, start_dt.strftime("%Y-%m-%d"), start_dt.strftime("%H:%M"), end_dt.strftime("%Y-%m-%d"), end_dt.strftime("%H:%M"), f"{planned_hours} h" ) def update_task(username, task_code, task_name, start_date, start_time, end_date, end_time): """Atualiza tarefa existente""" if username is None: return "❌ Faça login primeiro" if not task_code: return "❌ Selecione uma tarefa" if not task_name or not task_name.strip(): return "❌ Preencha a descrição" try: planned_hours = calcular_horas(start_date, start_time, end_date, end_time) except ValueError as e: return f"❌ {str(e)}" conn = get_conn() cur = conn.cursor() try: # Limpar formato de data if isinstance(start_date, str) and " " in start_date: start_date = start_date.split()[0] if isinstance(end_date, str) and " " in end_date: end_date = end_date.split()[0] cur.execute(""" UPDATE tasks SET task_name = ?, task_date = ?, start_time = ?, end_time = ?, planned_hours = ? WHERE username = ? AND task_code = ? """, (task_name, start_date, f"{start_date} {start_time}:00", f"{end_date} {end_time}:00", planned_hours, username, task_code)) if cur.rowcount == 0: return "❌ Tarefa não encontrada" conn.commit() return f"✅ Tarefa {task_code} atualizada!" except Exception as e: conn.rollback() return f"❌ Erro: {e}" finally: conn.close() def delete_task(username, task_code): """Elimina tarefa""" if username is None: return "❌ Faça login primeiro" if not task_code: return "❌ Selecione uma tarefa" conn = get_conn() cur = conn.cursor() try: cur.execute("DELETE FROM tasks WHERE username = ? AND task_code = ?", (username, task_code)) if cur.rowcount == 0: return "❌ Tarefa não encontrada" conn.commit() return f"✅ Tarefa {task_code} eliminada!" except Exception as e: conn.rollback() return f"❌ Erro: {e}" finally: conn.close() def list_tasks(username): """Lista tarefas do utilizador""" if username is None: return pd.DataFrame() conn = get_conn() df = pd.read_sql_query(""" SELECT t.task_code AS 'Código', t.task_name AS 'Tarefa', t.start_time AS 'Início', t.end_time AS 'Fim', t.planned_hours AS 'Horas Previstas', COALESCE(SUM(s.planned_hours), 0) AS 'Horas Subtarefas', COUNT(s.id) AS 'Nº Subtarefas' FROM tasks t LEFT JOIN subtasks s ON t.id = s.task_id WHERE t.username = ? GROUP BY t.id ORDER BY t.id DESC """, conn, params=(username,)) conn.close() if not df.empty: df['Status'] = df.apply( lambda row: '🔴 EXCEDIDO' if row['Horas Subtarefas'] > row['Horas Previstas'] else '🟢 OK', axis=1 ) return df # ====================================================== # CRUD Subtarefas # ====================================================== def get_subtask_codes(username, task_code): """Lista códigos de subtarefas""" if username is None or not task_code: return [] conn = get_conn() cur = conn.cursor() cur.execute(""" SELECT s.subtask_code FROM subtasks s JOIN tasks t ON s.task_id = t.id WHERE t.username = ? AND t.task_code = ? ORDER BY s.id DESC """, (username, task_code)) codes = [row[0] for row in cur.fetchall()] conn.close() return codes def add_subtask(username, task_code, sub_name, start_date, start_time, end_date, end_time): """Adiciona subtarefa""" if username is None: return "", "❌ Faça login primeiro" if not task_code: return "", "❌ Selecione uma tarefa" if not sub_name or not sub_name.strip(): return "", "❌ Preencha a descrição" try: planned_hours = calcular_horas(start_date, start_time, end_date, end_time) except ValueError as e: return "", f"❌ {str(e)}" conn = get_conn() cur = conn.cursor() try: # Buscar task_id e horas cur.execute("SELECT id, planned_hours FROM tasks WHERE username = ? AND task_code = ?", (username, task_code)) row = cur.fetchone() if not row: return "", "❌ Tarefa não encontrada" task_id, task_hours = row # Verificar limite de 10 cur.execute("SELECT COUNT(*) FROM subtasks WHERE task_id = ?", (task_id,)) count = cur.fetchone()[0] if count >= 10: return "", "❌ Limite de 10 subtarefas atingido" # Gerar código subtask_code = f"SUB_{task_id}_{count+1:02d}" # Limpar formato de data if isinstance(start_date, str) and " " in start_date: start_date = start_date.split()[0] if isinstance(end_date, str) and " " in end_date: end_date = end_date.split()[0] cur.execute(""" INSERT INTO subtasks (task_id, subtask_code, subtask_name, start_time, end_time, planned_hours) VALUES (?, ?, ?, ?, ?, ?) """, (task_id, subtask_code, sub_name, f"{start_date} {start_time}:00", f"{end_date} {end_time}:00", planned_hours)) conn.commit() # Verificar total cur.execute("SELECT COALESCE(SUM(planned_hours), 0) FROM subtasks WHERE task_id = ?", (task_id,)) total_sub = cur.fetchone()[0] if total_sub > task_hours: msg = f"⚠️ Subtarefa {subtask_code} criada, mas TOTAL EXCEDE ({total_sub}h / {task_hours}h)" else: msg = f"✅ Subtarefa {subtask_code} criada! ({total_sub}h / {task_hours}h)" return f"{planned_hours} h", msg except Exception as e: conn.rollback() return "", f"❌ Erro: {e}" finally: conn.close() def delete_subtask(username, task_code, subtask_code): """Elimina subtarefa""" if username is None: return "❌ Faça login primeiro" if not task_code or not subtask_code: return "❌ Selecione tarefa e subtarefa" conn = get_conn() cur = conn.cursor() try: # Buscar task_id cur.execute("SELECT id FROM tasks WHERE username = ? AND task_code = ?", (username, task_code)) row = cur.fetchone() if not row: return "❌ Tarefa não encontrada" task_id = row[0] cur.execute("DELETE FROM subtasks WHERE task_id = ? AND subtask_code = ?", (task_id, subtask_code)) if cur.rowcount == 0: return "❌ Subtarefa não encontrada" conn.commit() return f"✅ Subtarefa {subtask_code} eliminada!" except Exception as e: conn.rollback() return f"❌ Erro: {e}" finally: conn.close() def list_subtasks(username, task_code): """Lista subtarefas de uma tarefa""" if username is None or not task_code: return pd.DataFrame() conn = get_conn() cur = conn.cursor() # Buscar task_id e horas cur.execute("SELECT id, planned_hours FROM tasks WHERE username = ? AND task_code = ?", (username, task_code)) row = cur.fetchone() if not row: conn.close() return pd.DataFrame() task_id, task_hours = row df = pd.read_sql_query(""" SELECT subtask_code AS 'Código', subtask_name AS 'Subtarefa', start_time AS 'Início', end_time AS 'Fim', planned_hours AS 'Horas' FROM subtasks WHERE task_id = ? ORDER BY id DESC """, conn, params=(task_id,)) conn.close() if not df.empty: total_sub = df['Horas'].sum() exceeded = total_sub > task_hours df['Status'] = '🔴 EXCEDIDO' if exceeded else '🟢 OK' return df # ====================================================== # Gráfico Gantt # ====================================================== def generate_gantt(username, selected_task_code=None): """Gera gráfico Gantt - mostra tarefas ou subtarefas""" if username is None: return go.Figure().add_annotation( text="⚠️ Faça login primeiro", xref="paper", yref="paper", x=0.5, y=0.5, showarrow=False, font=dict(size=20) ) conn = get_conn() # Se nenhuma tarefa selecionada, mostrar todas as tarefas if not selected_task_code: df = pd.read_sql_query(""" SELECT task_code, task_name, start_time, end_time FROM tasks WHERE username = ? ORDER BY start_time """, conn, params=(username,)) conn.close() if df.empty: return go.Figure().add_annotation( text="Nenhuma tarefa encontrada", xref="paper", yref="paper", x=0.5, y=0.5, showarrow=False, font=dict(size=16) ) # Preparar dados para Gantt gantt_data = [] for _, row in df.iterrows(): start = datetime.strptime(row['start_time'], "%Y-%m-%d %H:%M:%S") end = datetime.strptime(row['end_time'], "%Y-%m-%d %H:%M:%S") gantt_data.append(dict( Task=row['task_name'][:30], # Limitar tamanho Start=start.strftime("%Y-%m-%d"), Finish=end.strftime("%Y-%m-%d"), Resource=row['task_code'] )) fig = ff.create_gantt( gantt_data, index_col='Resource', show_colorbar=True, group_tasks=True, title=f"📊 Gantt - Tarefas de {username}" ) fig.update_layout( xaxis_title="Data", height=max(500, 200 + len(gantt_data) * 50), margin=dict(l=400, r=50, t=80, b=50), hovermode='closest', yaxis=dict(automargin=True) ) return fig # Se tarefa selecionada, mostrar tarefa + subtarefas else: # Buscar tarefa cur = conn.cursor() cur.execute(""" SELECT id, task_name, start_time, end_time FROM tasks WHERE username = ? AND task_code = ? """, (username, selected_task_code)) task_row = cur.fetchone() if not task_row: conn.close() return go.Figure().add_annotation( text="Tarefa não encontrada", xref="paper", yref="paper", x=0.5, y=0.5, showarrow=False ) task_id, task_name, task_start, task_end = task_row # Buscar subtarefas df_sub = pd.read_sql_query(""" SELECT subtask_code, subtask_name, start_time, end_time FROM subtasks WHERE task_id = ? ORDER BY start_time """, conn, params=(task_id,)) conn.close() # Preparar dados gantt_data = [] # Adicionar tarefa principal task_start_dt = datetime.strptime(task_start, "%Y-%m-%d %H:%M:%S") task_end_dt = datetime.strptime(task_end, "%Y-%m-%d %H:%M:%S") gantt_data.append(dict( Task=f"📌 {task_name[:30]}", Start=task_start_dt.strftime("%Y-%m-%d"), Finish=task_end_dt.strftime("%Y-%m-%d"), Resource=selected_task_code )) # Adicionar subtarefas for _, row in df_sub.iterrows(): start = datetime.strptime(row['start_time'], "%Y-%m-%d %H:%M:%S") end = datetime.strptime(row['end_time'], "%Y-%m-%d %H:%M:%S") gantt_data.append(dict( Task=f" └─ {row['subtask_name'][:25]}", Start=start.strftime("%Y-%m-%d"), Finish=end.strftime("%Y-%m-%d"), Resource=row['subtask_code'] )) if len(gantt_data) == 1: # Só tarefa, sem subtarefas fig = ff.create_gantt( gantt_data, index_col='Resource', show_colorbar=False, title=f"📊 Gantt - {selected_task_code} (sem subtarefas)" ) else: fig = ff.create_gantt( gantt_data, index_col='Resource', show_colorbar=True, group_tasks=True, title=f"📊 Gantt - {selected_task_code} com {len(gantt_data)-1} subtarefas" ) fig.update_layout( xaxis_title="Data", height=max(500, 200 + len(gantt_data) * 50), margin=dict(l=600, r=50, t=80, b=50), hovermode='closest', yaxis=dict( automargin=False, tickfont=dict(size=10), tickmode='linear', side='left' ), xaxis=dict(domain=[0, 1]) ) return fig # ====================================================== # Interface Gradio # ====================================================== with gr.Blocks(title="Time Tracking") as demo: gr.Markdown("# ⛏️ Time Tracking") user_state = gr.State(None) # Armazena username login_indicator = gr.Markdown("## ⚠️ **Não autenticado**") # ===== TAB: Login ===== with gr.Tab("🔐 Login"): with gr.Row(): with gr.Column(): gr.Markdown("### Entrar") login_user_input = gr.Textbox(label="Utilizador") login_pass_input = gr.Textbox(label="Password", type="password") login_msg = gr.Markdown() login_btn = gr.Button("Entrar", variant="primary") with gr.Column(): gr.Markdown("### Criar Conta") new_user_input = gr.Textbox(label="Novo utilizador") new_pass_input = gr.Textbox(label="Password", type="password") create_msg = gr.Markdown() create_btn = gr.Button("Criar", variant="secondary") login_btn.click( login_user, [login_user_input, login_pass_input], [user_state, login_msg, login_indicator] ) create_btn.click( create_user, [new_user_input, new_pass_input], create_msg ) # ===== TAB: Gerir Tarefas ===== with gr.Tab("📝 Gerir Tarefas"): with gr.Row(): edit_task_code = gr.Dropdown(label="Selecionar tarefa (vazio = nova)", choices=[]) refresh_tasks_btn = gr.Button("🔄", size="sm") task_name = gr.Textbox(label="Descrição", lines=2) with gr.Row(): with gr.Column(): task_start_date = gr.DateTime(label="📅 Data início", value=datetime.now().strftime("%Y-%m-%d"), include_time=False, type="string") task_start_time = gr.Dropdown(label="Hora início", choices=slots_30_min(), value="09:00") with gr.Column(): task_end_date = gr.DateTime(label="📅 Data fim", value=datetime.now().strftime("%Y-%m-%d"), include_time=False, type="string") task_end_time = gr.Dropdown(label="Hora fim", choices=slots_30_min(), value="17:00") task_hours_box = gr.Textbox(label="Horas previstas", interactive=False) task_msg = gr.Markdown() with gr.Row(): save_task_btn = gr.Button("💾 Guardar Nova", variant="primary") update_task_btn = gr.Button("✏️ Atualizar", variant="secondary") delete_task_btn = gr.Button("🗑️ Eliminar", variant="stop") # Eventos for comp in [task_start_date, task_start_time, task_end_date, task_end_time]: comp.change(calcular_horas_preview, [task_start_date, task_start_time, task_end_date, task_end_time], task_hours_box) edit_task_code.change( get_task_by_code, [user_state, edit_task_code], [task_name, task_start_date, task_start_time, task_end_date, task_end_time, task_hours_box] ) refresh_tasks_btn.click(lambda u: gr.Dropdown(choices=get_task_codes(u)), user_state, edit_task_code) save_task_btn.click( add_task, [user_state, task_name, task_start_date, task_start_time, task_end_date, task_end_time], [task_hours_box, task_msg, edit_task_code] ) update_task_btn.click( update_task, [user_state, edit_task_code, task_name, task_start_date, task_start_time, task_end_date, task_end_time], task_msg ) delete_task_btn.click(delete_task, [user_state, edit_task_code], task_msg) # ===== TAB: Gerir Subtarefas ===== with gr.Tab("📋 Gerir Subtarefas"): with gr.Row(): sub_task_code = gr.Dropdown(label="Tarefa", choices=[]) refresh_sub_btn = gr.Button("🔄", size="sm") sub_name = gr.Textbox(label="Descrição", lines=2) with gr.Row(): with gr.Column(): sub_start_date = gr.DateTime(label="📅 Data início", value=datetime.now().strftime("%Y-%m-%d"), include_time=False, type="string") sub_start_time = gr.Dropdown(label="Hora início", choices=slots_30_min(), value="09:00") with gr.Column(): sub_end_date = gr.DateTime(label="📅 Data fim", value=datetime.now().strftime("%Y-%m-%d"), include_time=False, type="string") sub_end_time = gr.Dropdown(label="Hora fim", choices=slots_30_min(), value="10:00") sub_hours_box = gr.Textbox(label="Horas", interactive=False) sub_msg = gr.Markdown() save_sub_btn = gr.Button("💾 Guardar Subtarefa", variant="primary") gr.Markdown("---\n### Eliminar Subtarefa") with gr.Row(): del_task_code = gr.Dropdown(label="Tarefa", choices=[]) del_sub_code = gr.Dropdown(label="Subtarefa", choices=[]) refresh_del_btn = gr.Button("🔄", size="sm") del_msg = gr.Markdown() del_sub_btn = gr.Button("🗑️ Eliminar", variant="stop") # Eventos for comp in [sub_start_date, sub_start_time, sub_end_date, sub_end_time]: comp.change(calcular_horas_preview, [sub_start_date, sub_start_time, sub_end_date, sub_end_time], sub_hours_box) refresh_sub_btn.click(lambda u: gr.Dropdown(choices=get_task_codes(u)), user_state, sub_task_code) save_sub_btn.click( add_subtask, [user_state, sub_task_code, sub_name, sub_start_date, sub_start_time, sub_end_date, sub_end_time], [sub_hours_box, sub_msg] ) refresh_del_btn.click(lambda u: gr.Dropdown(choices=get_task_codes(u)), user_state, del_task_code) del_task_code.change(lambda u, tc: gr.Dropdown(choices=get_subtask_codes(u, tc)), [user_state, del_task_code], del_sub_code) del_sub_btn.click(delete_subtask, [user_state, del_task_code, del_sub_code], del_msg) # ===== TAB: Visualizar Tarefas ===== with gr.Tab("📊 Visualizar Tarefas"): tasks_table = gr.Dataframe() refresh_view_btn = gr.Button("🔄 Atualizar") refresh_view_btn.click(list_tasks, user_state, tasks_table) # ===== TAB: Visualizar Subtarefas ===== with gr.Tab("📋 Visualizar Subtarefas"): view_task_code = gr.Dropdown(label="Tarefa", choices=[]) refresh_view_sub_btn = gr.Button("🔄 Atualizar lista") subtasks_table = gr.Dataframe() refresh_view_sub_btn.click(lambda u: gr.Dropdown(choices=get_task_codes(u)), user_state, view_task_code) view_task_code.change(list_subtasks, [user_state, view_task_code], subtasks_table) # ===== TAB: Gráfico Gantt ===== with gr.Tab("📊 Gráfico Gantt"): gr.Markdown(""" ### 📊 Gráfico de Gantt **Modo 1**: Deixe vazio para ver **todas as tarefas** **Modo 2**: Selecione uma tarefa para ver **tarefa + subtarefas** """) gantt_task_select = gr.Dropdown(label="🔍 Selecionar tarefa (opcional - deixe vazio para ver todas)", choices=[], allow_custom_value=False) with gr.Row(): gantt_refresh_btn = gr.Button("🔄 Atualizar lista") gantt_clear_btn = gr.Button("❌ Limpar seleção") gantt_generate_btn = gr.Button("📊 Gerar Gantt", variant="primary") gantt_plot = gr.Plot(label="Gráfico") # Atualizar dropdown de tarefas gantt_refresh_btn.click( lambda u: gr.Dropdown(choices=get_task_codes(u)), user_state, gantt_task_select ) # Limpar seleção (volta para modo "todas as tarefas") gantt_clear_btn.click( lambda: gr.Dropdown(value=None), None, gantt_task_select ) # Gerar Gantt (todas as tarefas ou tarefa específica) gantt_generate_btn.click( generate_gantt, [user_state, gantt_task_select], gantt_plot ) if __name__ == "__main__": demo.launch(server_name="0.0.0.0", server_port=7860, share=False)