import streamlit as st
from streamlit_cookies_controller import CookieController
import streamlit.components.v1 as components
import pandas as pd
import json, time
from datetime import datetime
from gs_client import read_ws
st.set_page_config(layout="wide", initial_sidebar_state="collapsed", page_title="PMJA - Dashboard")
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/atual.py")
# ── LOADING ───────────────────────────────────────────────────────────
loading_placeholder = st.empty()
loading_placeholder.markdown("""
PMJA — Dashboard Gestão de Materiais
Visão Completa · Recebimento & Expedição
Conectando ao Google Sheets...
""", unsafe_allow_html=True)
# ── LOAD DATA ─────────────────────────────────────────────────────────
df_recebimento = read_ws("recebimento")
df_rec_dados = read_ws("rec_dados")
df_exp_raw = read_ws("exp_dados")
loading_placeholder.empty()
# ── HELPERS ───────────────────────────────────────────────────────────
def fmt(n):
return "0" if pd.isna(n) else "{:,}".format(int(n)).replace(',','.')
def proc_exp(df_raw):
if df_raw is None or df_raw.empty: return None, 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.loc[:,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
cols = ['mes_num','ano','qtd_requisicoes','qtd_unidades_emitidas','qtd_itens_total']
return (df[cols].sort_values(['ano','mes_num']).reset_index(drop=True),
df[['mes_num','ano']+req+unid+itens].sort_values(['ano','mes_num']).reset_index(drop=True))
def get_sistemas(df):
s = set()
skip = {'Volumes','Unid. Itens','Itens por unidade','mes_ano','data','mes','ano'}
for col in df.columns:
for m in ['Volumes','Unid. Itens','Itens por unidade']:
if m in col:
cat = col.replace(m,'').strip()
if cat and cat not in skip: s.add(cat)
break
return sorted(list(s))
def ext_cat(col):
for s in [' Requisições',' Requisiçoes',' Unid. Itens',' Unid Itens',
' Itens por unidade',' Items por unidade',' Volumes']:
col = col.replace(s,'')
return col.strip()
MESES = {1:'Jan',2:'Fev',3:'Mar',4:'Abr',5:'Mai',6:'Jun',7:'Jul',8:'Ago',9:'Set',10:'Out',11:'Nov',12:'Dez'}
COR_REC = ['#003a70','#007dcc','#004d80','#4da6e6','#003d66','#99ccff']
COR_EXP = ['#064e3b','#10b981','#047857','#34d399','#065f46','#6ee7b7']
COR_SIS = ['#001a33','#002d4d','#003d66','#004d80','#005d99','#006db3',
'#007dcc','#1a8dd4','#3399dd','#4da6e6','#66b3ee','#80c0f5','#99ccff','#b3d9ff']
COR_SIS_EXP = ['#064e3b','#065f46','#047857','#059669','#10b981','#34d399',
'#4ade80','#6ee7b7','#86efac','#a7f3d0','#bbf7d0','#d1fae5','#ecfdf5','#f0fdf4']
def build_all_frames(df_rec, df_exp, df_rec_sis, df_exp_det, sistemas_rec,
anos_rec, anos_exp, mes_max, mes_max_rec, mes_max_exp, mes_max_sis):
frames = []
for mes in range(1, mes_max+1):
frame = {"mes": mes, "metrics": {}, "charts": {}}
for col, key, src in [
('qtd_descarregamentos','desc','rec'),('peso_recebido','peso','rec'),
('qtd_volumes_recebidos','vol','rec'),('qtd_unidades_recebidos','unid','rec'),
('qtd_itens_por_unidade','itens','rec'),('qtd_requisicoes','req','exp'),
('qtd_unidades_emitidas','uem','exp'),('qtd_itens_total','itot','exp'),
]:
if src=='rec':
v = df_rec[df_rec['mes']<=mes][col].sum() if mes<=mes_max_rec else df_rec[col].sum()
else:
v = df_exp[df_exp['mes']<=mes][col].sum() if mes<=mes_max_exp else df_exp[col].sum()
frame["metrics"][key] = fmt(v)
def line_traces(df, col, anos, cores, lim):
traces = []
m = mes if mes<=lim else lim
for i,ano in enumerate(anos):
d = df[(df['ano']==ano)&(df['mes']<=m)].sort_values('mes')
if d.empty: continue
cor = cores[i%len(cores)]
traces.append({
"x":[int(x) for x in d['mes']],
"y":[float(y) for y in d[col]],
"text":[fmt(v) if v>0 else "" for v in d[col]],
"line_color":cor, "ano":str(ano), "total":fmt(d[col].sum())
})
return traces
if mes<=mes_max_rec:
frame["charts"]["r1_0"] = line_traces(df_rec,'qtd_descarregamentos',anos_rec,COR_REC,mes_max_rec)
frame["charts"]["r1_1"] = line_traces(df_rec,'peso_recebido',anos_rec,COR_REC,mes_max_rec)
frame["charts"]["r1_2"] = line_traces(df_rec,'qtd_volumes_recebidos',anos_rec,COR_REC,mes_max_rec)
if df_rec_sis is not None and mes<=mes_max_sis and sistemas_rec:
cols_map = {}
for col in df_rec_sis.columns:
for s in sistemas_rec:
if 'Volumes' in col and not any(e in col for e in ['Unid.','unidade']) and s in col:
cols_map[col] = s
slices = []
for i,s in enumerate(sistemas_rec):
col = next((c for c,v in cols_map.items() if v==s), None)
if col and col in df_rec_sis.columns:
t = float(pd.to_numeric(df_rec_sis.loc[df_rec_sis['mes']<=mes,col],errors='coerce').fillna(0).sum())
if t>0: slices.append({"label":s,"value":t,"color":COR_SIS[i%len(COR_SIS)]})
if slices: frame["charts"]["r1_3"] = slices
if mes<=mes_max_rec:
frame["charts"]["r2_0"] = line_traces(df_rec,'qtd_unidades_recebidos',anos_rec,COR_REC,mes_max_rec)
frame["charts"]["r2_2"] = line_traces(df_rec,'qtd_itens_por_unidade',anos_rec,COR_REC,mes_max_rec)
if df_rec_sis is not None and mes<=mes_max_sis and sistemas_rec:
for slot, palavra, excluir in [('r2_1','Unid. Itens',['por unidade']),('r2_3','Itens por unidade',['Unid. Itens'])]:
cols_map2 = {}
for col in df_rec_sis.columns:
for s in sistemas_rec:
if palavra in col and not any(e in col for e in excluir) and s in col:
cols_map2[col] = s
bars = []
for i,s in enumerate(sistemas_rec):
col = next((c for c,v in cols_map2.items() if v==s), None)
if col and col in df_rec_sis.columns:
t = float(pd.to_numeric(df_rec_sis.loc[df_rec_sis['mes']<=mes,col],errors='coerce').fillna(0).sum())
if t>0: bars.append({"label":s,"value":t,"color":COR_SIS[i%len(COR_SIS)]})
if bars: frame["charts"][slot] = bars
if mes<=mes_max_exp:
frame["charts"]["e1_0"] = line_traces(df_exp,'qtd_requisicoes',anos_exp,COR_EXP,mes_max_exp)
frame["charts"]["e1_1"] = line_traces(df_exp,'qtd_unidades_emitidas',anos_exp,COR_EXP,mes_max_exp)
frame["charts"]["e1_2"] = line_traces(df_exp,'qtd_itens_total',anos_exp,COR_EXP,mes_max_exp)
if df_exp_det is not None and mes<=mes_max_exp:
det = df_exp_det[df_exp_det['mes_num']<=mes].copy()
req_c = [c for c in df_exp_det.columns if 'requisi' in c.lower() and 'unid' not in c.lower() and 'por' not in c.lower()]
uni_c = [c for c in df_exp_det.columns if 'unid' in c.lower() and 'iten' in c.lower().replace('.','') and 'por' not in c.lower()]
it_c = [c for c in df_exp_det.columns if 'iten' in c.lower().replace('.','') and 'por' in c.lower() and 'unid' in c.lower()]
for slot, cols in [('es_0',req_c),('es_1',uni_c),('es_2',it_c)]:
if not cols: continue
tots = {}
for col in cols:
cat = ext_cat(col)
if cat not in tots: tots[cat]={'v':0.0,'c':COR_SIS_EXP[len(tots)%len(COR_SIS_EXP)]}
det.loc[:,col] = pd.to_numeric(det[col],errors='coerce').fillna(0)
tots[cat]['v'] += float(det[col].sum())
dados = sorted([(k,v) for k,v in tots.items() if v['v']>0],key=lambda x:x[1]['v'],reverse=True)
if dados: frame["charts"][slot] = [{"label":k,"value":v['v'],"color":v['c']} for k,v in dados]
frames.append(frame)
return frames
# ── PROCESS DATA ──────────────────────────────────────────────────────
df_exp_raw = df_exp_raw if df_exp_raw is not None and not df_exp_raw.empty else None
df_recebimento = df_recebimento if df_recebimento is not None and not df_recebimento.empty else None
df_rec_dados = df_rec_dados if df_rec_dados is not None and not df_rec_dados.empty else None
df_expedicao, df_exp_det = proc_exp(df_exp_raw) if df_exp_raw is not None else (None, None)
df_rec, mes_max_rec = None, 0
if df_recebimento is not None:
df_rec = df_recebimento.copy()
if len(df_rec.columns)==6:
df_rec.columns = ['mes_ano','qtd_descarregamentos','qtd_volumes_recebidos',
'peso_recebido','qtd_unidades_recebidos','qtd_itens_por_unidade']
df_rec['data'] = pd.to_datetime(df_rec['mes_ano'], format='%m/%Y', errors='coerce')
df_rec = df_rec.dropna(subset=['data']).sort_values('data')
df_rec['ano'] = df_rec['data'].dt.year; df_rec['mes'] = df_rec['data'].dt.month
for col in ['qtd_descarregamentos','qtd_volumes_recebidos','peso_recebido',
'qtd_unidades_recebidos','qtd_itens_por_unidade']:
if col in df_rec.columns:
df_rec.loc[:,col] = pd.to_numeric(df_rec[col], errors='coerce').fillna(0)
mes_max_rec = int(df_rec['mes'].max())
df_exp, mes_max_exp = None, 0
if df_expedicao is not None and not df_expedicao.empty:
df_exp = df_expedicao.copy()
df_exp.rename(columns={'mes_num':'mes'}, inplace=True)
for col in ['qtd_requisicoes','qtd_unidades_emitidas','qtd_itens_total']:
df_exp.loc[:,col] = pd.to_numeric(df_exp[col], errors='coerce').fillna(0)
mes_max_exp = int(df_exp['mes'].max())
df_rec_sis, mes_max_sis, sistemas_rec = None, 0, []
if df_rec_dados is not None:
df_rec_sis = df_rec_dados.copy()
df_rec_sis['data'] = pd.to_datetime(df_rec_sis.iloc[:,0], format='%m/%Y', errors='coerce')
df_rec_sis = df_rec_sis.dropna(subset=['data']).sort_values('data').copy()
df_rec_sis['mes'] = df_rec_sis['data'].dt.month
df_rec_sis['ano'] = df_rec_sis['data'].dt.year
sistemas_rec = get_sistemas(df_rec_sis)
mes_max_sis = int(df_rec_sis['mes'].max())
if df_rec is not None and df_exp is not None and mes_max_rec>0 and mes_max_exp>0:
anos_rec = [int(a) for a in sorted(df_rec['ano'].unique())]
anos_exp = [int(a) for a in sorted(df_exp['ano'].unique())]
mes_max = max(mes_max_rec, mes_max_exp, mes_max_sis if mes_max_sis>0 else 0)
frames = build_all_frames(df_rec, df_exp, df_rec_sis, df_exp_det, sistemas_rec,
anos_rec, anos_exp, mes_max, mes_max_rec, mes_max_exp, mes_max_sis)
frames_json = json.dumps(frames, ensure_ascii=False)
meses_json = json.dumps(MESES)
html = """
