import streamlit as st
import pandas as pd
import json, time
from datetime import datetime
from streamlit_cookies_controller import CookieController
import streamlit.components.v1 as components
from gs_client import read_ws
st.set_page_config(layout="wide", initial_sidebar_state="collapsed", page_title="PMJA - Dashboard Expedição")
st.markdown("""""", unsafe_allow_html=True)
# ── SESSION ───────────────────────────────────────────────────────────
_cc = CookieController()
COOKIE_NAME = "pmja_session"
def load_session():
if st.session_state.get("logged_in") and st.session_state.get("session_exp"):
if datetime.now() < st.session_state.session_exp:
return True
st.session_state.logged_in = False
return False
try:
c = _cc.get(COOKIE_NAME)
if not c: return False
if datetime.now() >= datetime.fromisoformat(c["expiry"]):
_cc.remove(COOKIE_NAME); return False
st.session_state.update(logged_in=True, session_uid=c["user_id"],
session_usr=c["username"], session_exp=datetime.fromisoformat(c["expiry"]))
return True
except Exception:
return False
if not load_session():
st.switch_page("app.py")
if 'inicio_exibicao' not in st.session_state:
st.session_state.inicio_exibicao = time.time()
if time.time() - st.session_state.inicio_exibicao >= 120:
st.session_state.inicio_exibicao = time.time()
st.switch_page("pages/full.py")
# ── LOADING ───────────────────────────────────────────────────────────
loading_placeholder = st.empty()
loading_placeholder.markdown("""
PMJA — Dashboard Expedição
Gestão de Materiais
Conectando ao Google Sheets...
""", unsafe_allow_html=True)
# ── LOAD DATA ─────────────────────────────────────────────────────────
df_exp_raw = read_ws("exp_dados")
loading_placeholder.empty()
# ── HELPERS ───────────────────────────────────────────────────────────
def fmt(n):
if pd.isna(n): return "0"
return "{:,}".format(int(n)).replace(',', '.')
def extrair_categoria(col):
for s in [' Requisições',' Requisiçoes',' Unid. Itens',' Unid Itens',
' Itens por unidade',' Items por unidade']:
col = col.replace(s, '')
return col.strip()
def proc_exp(df_raw):
if df_raw is None or df_raw.empty: return None
df = df_raw.copy()
df.rename(columns={df.columns[0]: 'mes'}, inplace=True)
df = df[df['mes'].notna()].copy()
df['data'] = pd.to_datetime(df['mes'], format='%m/%Y', errors='coerce')
df = df.dropna(subset=['data']).copy()
df['ano'] = df['data'].dt.year
df['mes_num'] = df['data'].dt.month
req, unid, itens = [], [], []
for col in df.columns:
c = col.lower().strip().replace('.','').replace(' ',' ')
if 'requisi' in c and 'unid' not in c and 'por' not in c: req.append(col)
if 'unid' in c and 'iten' in c and 'por' not in c: unid.append(col)
if 'iten' in c and 'por' in c and 'unid' in c: itens.append(col)
for col in req+unid+itens:
if col in df.columns: df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)
df['qtd_requisicoes'] = df[req].sum(axis=1)
df['qtd_unidades_emitidas'] = df[unid].sum(axis=1)
df['qtd_itens_total'] = df[itens].sum(axis=1) if itens else 0
df_final = df[['mes_num','ano','qtd_requisicoes','qtd_unidades_emitidas','qtd_itens_total']]\
.sort_values(['ano','mes_num']).reset_index(drop=True)
df_det = df[['mes_num','ano']+req+unid+itens]\
.sort_values(['ano','mes_num']).reset_index(drop=True)
return df_final, df_det, req, unid, itens
# ── BUILD FRAMES ──────────────────────────────────────────────────────
def build_frames(df_plot, df_det, req_cols, unid_cols, itens_cols, anos_unicos, mes_max, CORES_EXP):
MESES_PT = {1:'Jan',2:'Fev',3:'Mar',4:'Abr',5:'Mai',6:'Jun',
7:'Jul',8:'Ago',9:'Set',10:'Out',11:'Nov',12:'Dez'}
metricas_linha = [
{'col':'qtd_requisicoes', 'cores':['#001f3f','#0056A3','#00a8e8','#7ecfef']},
{'col':'qtd_unidades_emitidas','cores':['#001f3f','#0056A3','#00a8e8','#7ecfef']},
{'col':'qtd_itens_total', 'cores':['#001f3f','#0056A3','#00a8e8','#7ecfef']},
]
tipos_l1 = ['barra_h','barra_v','linha']
frames = []
for mes_atual in range(1, mes_max+1):
frame = {"mes": mes_atual, "cards": {}, "linha1": [], "linha2": []}
df_ate = df_plot[df_plot['mes_num'] <= mes_atual]
df_det_ate = df_det[df_det['mes_num'] <= mes_atual]
for col, tit, cor, suf in [
('qtd_requisicoes', 'Requisições', '#003a70',''),
('qtd_unidades_emitidas','Unidades Emitidas','#005a9c',''),
('qtd_itens_total', 'Total de Itens', '#0075be',''),
]:
frame["cards"][col] = {"valor": fmt(df_ate[col].sum()), "cor": cor, "titulo": tit, "sufixo": suf}
meses_ex = sorted(df_plot[df_plot['mes_num'] <= mes_atual]['mes_num'].unique())
for idx_m, cfg in enumerate(metricas_linha):
col = cfg['col']
traces = []
for idx_ano, ano in enumerate(anos_unicos):
d = df_plot[(df_plot['ano']==ano)&(df_plot['mes_num']<=mes_atual)].sort_values('mes_num')
if d.empty: continue
cor = cfg['cores'][idx_ano % len(cfg['cores'])]
vals, texts = [], []
for m in meses_ex:
row = d[d['mes_num']==m]
v = float(row[col].iloc[0]) if not row.empty else 0.0
vals.append(v); texts.append(fmt(v) if v > 0 else "")
traces.append({
"ano": str(ano), "cor": cor,
"meses": meses_ex, "y": vals, "text": texts,
"fill_rgba": "rgba({},{},{},0.1)".format(int(cor[1:3],16),int(cor[3:5],16),int(cor[5:7],16))
})
frame["linha1"].append({"tipo": tipos_l1[idx_m], "traces": traces, "meses_ex": meses_ex})
for grp in [
{'cols': req_cols, 'tipo': 'donut'},
{'cols': unid_cols, 'tipo': 'linha_sis'},
{'cols': itens_cols, 'tipo': 'barv_sis'},
]:
totais = {}
for c in grp['cols']:
cat = extrair_categoria(c)
if cat not in totais:
totais[cat] = {'v': 0.0, 'cor': CORES_EXP[len(totais) % len(CORES_EXP)]}
totais[cat]['v'] += float(df_det_ate[c].sum()) if c in df_det_ate.columns else 0.0
dados = sorted([(k,v) for k,v in totais.items() if v['v']>0],
key=lambda x: x[1]['v'], reverse=True)
frame["linha2"].append({
"tipo": grp['tipo'],
"labels": [d[0] for d in dados],
"valores":[d[1]['v'] for d in dados],
"cores": [d[1]['cor'] for d in dados],
})
frames.append(frame)
return frames
# ── MAIN ──────────────────────────────────────────────────────────────
result = proc_exp(df_exp_raw) if df_exp_raw is not None and not df_exp_raw.empty else None
if result is not None:
df_expedicao, df_det, req_cols, unid_cols, itens_cols = result
for col in ['qtd_requisicoes','qtd_unidades_emitidas','qtd_itens_total']:
df_expedicao[col] = pd.to_numeric(df_expedicao[col], errors='coerce').fillna(0)
mes_max = int(df_expedicao['mes_num'].max())
anos_unicos = sorted(df_expedicao['ano'].unique())
CORES_EXP = ['#001a33','#002d4d','#003d66','#004d80','#005d99','#006db3',
'#007dcc','#1a8dd4','#3399dd','#4da6e6','#66b3ee','#80c0f5','#99ccff','#b3d9ff']
MESES_PT = {1:'Jan',2:'Fev',3:'Mar',4:'Abr',5:'Mai',6:'Jun',
7:'Jul',8:'Ago',9:'Set',10:'Out',11:'Nov',12:'Dez'}
metricas_cfg = [
{'col':'qtd_requisicoes', 'cores':['#001f3f','#0056A3','#00a8e8','#7ecfef'],'suf':''},
{'col':'qtd_unidades_emitidas','cores':['#001f3f','#0056A3','#00a8e8','#7ecfef'],'suf':''},
{'col':'qtd_itens_total', 'cores':['#001f3f','#0056A3','#00a8e8','#7ecfef'],'suf':''},
]
def sub_anos(cfg):
parts = []
for i, ano in enumerate(anos_unicos):
cor = cfg['cores'][i % len(cfg['cores'])]
total = fmt(df_expedicao[df_expedicao['ano']==ano][cfg['col']].sum())
parts.append("{}: {}{}".format(cor, ano, total, cfg['suf']))
return " | ".join(parts)
def sub_sistemas(cols):
totais = {}
for c in cols:
cat = extrair_categoria(c)
if cat not in totais: totais[cat] = {'v':0.0,'cor':CORES_EXP[len(totais)%len(CORES_EXP)]}
totais[cat]['v'] += float(df_det[c].sum()) if c in df_det.columns else 0.0
dados = sorted([(k,v) for k,v in totais.items() if v['v']>0],
key=lambda x: x[1]['v'], reverse=True)
return " | ".join(
["{}: {}".format(d[1]['cor'], d[0], fmt(d[1]['v']))
for d in dados])
frames = build_frames(df_expedicao, df_det, req_cols, unid_cols, itens_cols, anos_unicos, mes_max, CORES_EXP)
import numpy as np
class NpEncoder(json.JSONEncoder):
def default(self, obj):
if isinstance(obj, (np.integer,)): return int(obj)
if isinstance(obj, (np.floating,)): return float(obj)
if isinstance(obj, np.ndarray): return obj.tolist()
return super().default(obj)
frames_json = json.dumps(frames, cls=NpEncoder, ensure_ascii=False)
meses_json = json.dumps(MESES_PT)
TITULOS_L1 = ['Requisições','Unidades Emitidas','Total de Itens']
TITULOS_L2 = ['Requisições por Sistema','Unidades Emitidas por Sistema','Total de Itens por Sistema']
subs_l1 = [sub_anos(c) for c in metricas_cfg]
subs_l2 = [sub_sistemas(req_cols), sub_sistemas(unid_cols), sub_sistemas(itens_cols)]
html = """
