mirror of
https://github.com/hoshikawa2/select_ai.git
synced 2026-03-03 16:09:38 +00:00
899 lines
33 KiB
Python
899 lines
33 KiB
Python
# app_select_ai.py
|
||
# ---------------------------------------------------------
|
||
# Chat + Dashboard (histórico) para SELECT AI no Autonomous Database
|
||
# ---------------------------------------------------------
|
||
|
||
from flask import Flask, request, render_template_string, session
|
||
import oracledb, os, json
|
||
from flask import send_file, jsonify
|
||
from io import BytesIO
|
||
import base64
|
||
from openpyxl import Workbook
|
||
from openpyxl.utils import get_column_letter
|
||
from openpyxl.drawing.image import Image as XLImage
|
||
from PIL import Image as PILImage
|
||
|
||
# ======================
|
||
# CONFIGURAÇÕES DO BANCO
|
||
# ======================
|
||
with open("./config", "r") as f:
|
||
config_data = json.load(f)
|
||
|
||
WALLET_PATH = config_data["WALLET_PATH"]
|
||
DB_ALIAS = config_data["DB_ALIAS"]
|
||
USERNAME = config_data["USERNAME"]
|
||
PASSWORD = config_data["PASSWORD"]
|
||
os.environ["TNS_ADMIN"] = WALLET_PATH
|
||
|
||
PROFILE_NAME = "OCI_GENERATIVE_AI_PROFILE"
|
||
|
||
def set_select_ai_profile(conn, requested_tag):
|
||
# Ativa o profile em cada sessão do pool
|
||
with conn.cursor() as cur:
|
||
cur.execute("BEGIN DBMS_CLOUD_AI.SET_PROFILE(:p); END;", p=PROFILE_NAME)
|
||
|
||
pool = oracledb.create_pool(
|
||
user=USERNAME,
|
||
password=PASSWORD,
|
||
dsn=DB_ALIAS,
|
||
config_dir=WALLET_PATH,
|
||
wallet_location=WALLET_PATH,
|
||
wallet_password=PASSWORD,
|
||
min=1, max=5, increment=1,
|
||
session_callback=set_select_ai_profile
|
||
)
|
||
|
||
# ======================
|
||
# APP FLASK
|
||
# ======================
|
||
app = Flask(__name__)
|
||
app.secret_key = "troque-esta-chave" # necessário p/ sessão (histórico)
|
||
|
||
PAGE = """
|
||
<!doctype html>
|
||
<html lang="pt-br">
|
||
<head>
|
||
<meta charset="utf-8">
|
||
<title>ER Analytics · Select AI</title>
|
||
<meta name="viewport" content="width=device-width, initial-scale=1">
|
||
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
|
||
<script>
|
||
Chart.defaults.font.size = 11;
|
||
Chart.defaults.plugins.legend.labels.boxWidth = 10;
|
||
</script>
|
||
<!-- Export PDF -->
|
||
<script src="https://cdn.jsdelivr.net/npm/jspdf@2.5.1/dist/jspdf.umd.min.js"></script>
|
||
<script src="https://cdn.jsdelivr.net/npm/html2canvas@1.4.1/dist/html2canvas.min.js"></script>
|
||
<!-- Export Excel (browser) -->
|
||
<script src="https://cdn.jsdelivr.net/npm/xlsx-populate/browser/xlsx-populate.min.js"></script>
|
||
<style>
|
||
:root{
|
||
--bg:#ffffff; /* fundo página */
|
||
--card:#f9fafb; /* cartões/itens */
|
||
--border:#e5e7eb; /* linhas/bordas */
|
||
--muted:#6b7280; /* textos fracos */
|
||
--text:#111827; /* texto principal */
|
||
--brand:#0d6efd; /* botões/realces */
|
||
}
|
||
|
||
*{box-sizing:border-box}
|
||
body{
|
||
margin:0;
|
||
font-family:system-ui,-apple-system,Segoe UI,Roboto,Arial;
|
||
background:var(--bg);
|
||
color:var(--text);
|
||
}
|
||
|
||
/* Layout */
|
||
.page{ min-height:100vh; display:flex; flex-direction:column; }
|
||
.wrap{ max-width:1200px; width:100%; margin:0 auto; padding:16px; flex:1; display:flex; flex-direction:column; gap:12px; }
|
||
.header{ display:flex; align-items:center; gap:10px; }
|
||
.logo{ width:34px; height:34px; border-radius:8px; background:linear-gradient(135deg,#60a5fa,#22d3ee); display:flex; align-items:center; justify-content:center; font-weight:800; color:#fff; }
|
||
.title{ font-size:18px; font-weight:700 }
|
||
.muted{ color:var(--muted); font-size:12px; }
|
||
|
||
.card{ background:var(--card); border:1px solid var(--border); border-radius:12px; padding:14px; }
|
||
.kpi{ display:flex; gap:8px; flex-wrap:wrap; }
|
||
.pill{ padding:4px 8px; border:1px solid var(--border); border-radius:999px; font-size:12px; background:#fff; color:var(--text); }
|
||
|
||
/* Histórico */
|
||
#history{ flex:1; overflow:auto; display:flex; flex-direction:column; gap:10px; padding-right:4px; max-height:68vh; }
|
||
.item{ background:var(--card); border:1px solid var(--border); border-radius:10px; padding:12px; }
|
||
.sql{
|
||
font-family: ui-monospace, SFMono-Regular, Menlo, Consolas, "Liberation Mono", monospace;
|
||
font-size:12px; color:#1f2937; background:#ffffff; border:1px solid var(--border);
|
||
border-radius:8px; padding:8px; overflow:auto;
|
||
}
|
||
.block{ margin-top:8px; }
|
||
|
||
table{ width:100%; border-collapse:collapse; font-size:14px; background:#fff; }
|
||
th,td{ padding:8px; border-bottom:1px solid var(--border); text-align:left; }
|
||
th{ background:#f3f4f6; position:sticky; top:0; }
|
||
|
||
/* Rodapé/input */
|
||
.footer{ position:sticky; bottom:0; background:var(--bg); padding:12px 16px; border-top:1px solid var(--border); }
|
||
.ask{ display:flex; gap:8px; flex-wrap:wrap; }
|
||
input[type="text"]{
|
||
flex:1; min-width:280px; padding:10px 12px; border-radius:10px;
|
||
border:1px solid var(--border); background:#ffffff; color:var(--text);
|
||
}
|
||
button{
|
||
background:var(--brand); color:#fff; border:none; border-radius:10px;
|
||
padding:10px 14px; font-weight:600; cursor:pointer;
|
||
}
|
||
button:hover{ filter:brightness(1.05) }
|
||
|
||
/* Gráficos */
|
||
.toolbar{ display:flex; gap:6px; flex-wrap:wrap; margin:6px 0 6px; }
|
||
.pill{ background:#ffffff; border:1px solid var(--border); color:#374151; }
|
||
.pill:hover{ border-color:#cbd5e1; }
|
||
|
||
.chart-box{
|
||
height:220px; width:100%;
|
||
border:1px solid var(--border); border-radius:10px;
|
||
background:#ffffff; padding:8px;
|
||
}
|
||
@media (max-width:640px){ .chart-box{ height:180px; } }
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<div class="page">
|
||
<div class="wrap">
|
||
<div class="header">
|
||
<div class="logo">ER</div>
|
||
<div>
|
||
<div class="title">ER Analytics · Select AI</div>
|
||
<div class="muted">Respostas e gráficos no topo; pergunta no rodapé. A última resposta fica visível automaticamente.</div>
|
||
</div>
|
||
</div>
|
||
|
||
{% if timeline %}
|
||
<div class="card">
|
||
<div class="kpi">
|
||
<span class="pill">Conexão: <b>{{ db_alias }}</b></span>
|
||
<span class="pill">Usuário: <b>{{ session_user }}</b></span>
|
||
<span class="pill">Profile AI: <b>{{ profile or '—' }}</b></span>
|
||
|
||
<!-- Botões de export -->
|
||
<span class="pill" style="cursor:auto; border:none;">|</span>
|
||
<button type="button" class="pill" onclick="exportPDF()" title="Exporta todas as respostas (com gráficos e tabelas) para PDF">Exportar PDF</button>
|
||
<button type="button" class="pill" onclick="exportExcelServer()">Exportar Excel</button>
|
||
</div>
|
||
</div>
|
||
|
||
<div id="history">
|
||
{% for item in timeline %}
|
||
<div class="item">
|
||
<div><b>Pergunta:</b> {{ item.prompt }}</div>
|
||
{% if item.sql %}<div class="block"><div class="sql">{{ item.sql }}</div></div>{% endif %}
|
||
|
||
{% if item.chart %}
|
||
<div class="toolbar">
|
||
<span class="pill" onclick="render_{{ loop.index0 }}('bar')">Barras</span>
|
||
<span class="pill" onclick="render_{{ loop.index0 }}('line')">Linhas</span>
|
||
<span class="pill" onclick="render_{{ loop.index0 }}('pie')">Pizza</span>
|
||
{% if item.table %}
|
||
<span class="pill" onclick="toggleTable_{{ loop.index0 }}()">Mostrar/ocultar Tabela</span>
|
||
{% endif %}
|
||
</div>
|
||
|
||
<div class="block chart-box">
|
||
<canvas id="chart_{{ loop.index0 }}"></canvas>
|
||
</div>
|
||
|
||
<script>
|
||
(function(){
|
||
const DATA_{{ loop.index0 }} = {
|
||
type: '{{ item.chart["type"] }}',
|
||
labels: {{ item.chart["labels"]|tojson }},
|
||
values: {{ item.chart["values"]|tojson }},
|
||
seriesLabel: '{{ item.chart["seriesLabel"] }}',
|
||
title: '{{ item.chart["title"] }}',
|
||
xLabel: '{{ item.chart["xLabel"] }}',
|
||
yLabel: '{{ item.chart["yLabel"] }}'
|
||
};
|
||
|
||
let ch_{{ loop.index0 }} = null;
|
||
|
||
function makeChart_{{ loop.index0 }}(kind){
|
||
const ctx = document.getElementById('chart_{{ loop.index0 }}').getContext('2d');
|
||
if (ch_{{ loop.index0 }}) ch_{{ loop.index0 }}.destroy();
|
||
ch_{{ loop.index0 }} = new Chart(ctx, {
|
||
type: kind,
|
||
data: {
|
||
labels: DATA_{{ loop.index0 }}.labels,
|
||
datasets: [{
|
||
label: DATA_{{ loop.index0 }}.seriesLabel,
|
||
data: DATA_{{ loop.index0 }}.values,
|
||
borderWidth: 1,
|
||
pointRadius: 2
|
||
}]
|
||
},
|
||
options: {
|
||
responsive: true,
|
||
maintainAspectRatio: false,
|
||
layout: { padding: 4 },
|
||
plugins: {
|
||
legend: { display: true, labels: { boxWidth: 10, font: { size: 10 } } },
|
||
title: { display: true, text: DATA_{{ loop.index0 }}.title, font: { size: 12 } },
|
||
tooltip:{ bodyFont: { size: 11 }, titleFont: { size: 11 } }
|
||
},
|
||
scales: {
|
||
x: { title: { display: true, text: DATA_{{ loop.index0 }}.xLabel, font: { size: 11 } },
|
||
ticks: { font: { size: 10 }, maxRotation: 0, autoSkip: true } },
|
||
y: { title: { display: true, text: DATA_{{ loop.index0 }}.yLabel, font: { size: 11 } },
|
||
ticks: { font: { size: 10 } }, beginAtZero: true, grace: "5%" }
|
||
},
|
||
elements: { line: { tension: 0.2 } }
|
||
}
|
||
});
|
||
// garante que a última resposta fica visível após render
|
||
setTimeout(scrollHistoryBottom, 60);
|
||
}
|
||
|
||
// expõe funções por-card
|
||
window.render_{{ loop.index0 }} = makeChart_{{ loop.index0 }};
|
||
{% if item.table %}
|
||
window.toggleTable_{{ loop.index0 }} = function(){
|
||
const el = document.getElementById('table_{{ loop.index0 }}');
|
||
if (!el) return;
|
||
el.style.display = (el.style.display === 'none') ? '' : 'none';
|
||
setTimeout(scrollHistoryBottom, 40);
|
||
};
|
||
{% endif %}
|
||
|
||
// inicial: usa o tipo sugerido pelo backend
|
||
makeChart_{{ loop.index0 }}(DATA_{{ loop.index0 }}.type);
|
||
})();
|
||
</script>
|
||
{% endif %}
|
||
|
||
{% if item.table %}
|
||
<div id="table_{{ loop.index0 }}" class="block" style="border:1px solid var(--border); border-radius:10px; overflow:auto; max-height:40vh;">
|
||
<table>
|
||
<thead><tr>{% for h in item.table.headers %}<th>{{ h }}</th>{% endfor %}</tr></thead>
|
||
<tbody>
|
||
{% for r in item.table.rows %}
|
||
<tr>{% for c in r %}<td>{{ c }}</td>{% endfor %}</tr>
|
||
{% endfor %}
|
||
</tbody>
|
||
</table>
|
||
</div>
|
||
{% endif %}
|
||
</div>
|
||
{% endfor %}
|
||
<!-- ⬇️ marcador fixo de fim -->
|
||
<div id="history-bottom"></div>
|
||
</div>
|
||
{% endif %}
|
||
</div>
|
||
|
||
<!-- Rodapé com input SEMPRE embaixo -->
|
||
<div class="footer">
|
||
<form class="ask" method="post" onsubmit="markScrollToBottom()">
|
||
<input type="text" name="frase" placeholder="Faça sua pergunta… (Enter para enviar)" required>
|
||
<input type="text" name="tabela" value="{{ default_table }}" style="display:none;">
|
||
<button type="submit">Enviar</button>
|
||
</form>
|
||
<div class="muted" style="margin-top:6px">Dica: cite colunas (ex.: <code>admitted</code>, <code>month_num</code>, <code>year_num</code>) para respostas mais precisas.</div>
|
||
</div>
|
||
<script>
|
||
document.addEventListener("DOMContentLoaded", () => {
|
||
const frase = document.querySelector('input[name="frase"]');
|
||
frase.addEventListener("keydown", (e) => {
|
||
if(e.key === "Enter"){
|
||
e.preventDefault();
|
||
frase.form.requestSubmit(); // envia o form
|
||
}
|
||
});
|
||
});
|
||
</script>
|
||
</div>
|
||
|
||
<script>
|
||
function scrollHistoryBottom(smooth=true){
|
||
const end = document.getElementById('history-bottom');
|
||
if (end) end.scrollIntoView({behavior: smooth ? 'smooth' : 'auto', block: 'end'});
|
||
}
|
||
|
||
// Marca a intenção de rolar após o POST (reload da página)
|
||
function markScrollToBottom(){
|
||
try { localStorage.setItem('__scrollToBottom', '1'); } catch(e){}
|
||
}
|
||
|
||
// Rola quando a página carrega (e re-rola depois que gráficos expandirem)
|
||
function settleAndScroll(){
|
||
scrollHistoryBottom(false); // imediato
|
||
setTimeout(scrollHistoryBottom, 80); // após layout inicial
|
||
setTimeout(scrollHistoryBottom, 250);// após Chart.js desenhar
|
||
setTimeout(scrollHistoryBottom, 600);// salvaguarda final
|
||
}
|
||
|
||
// Executa no load
|
||
window.addEventListener('load', () => {
|
||
// se veio de um submit, respeite a marca
|
||
let must = false;
|
||
try { must = localStorage.getItem('__scrollToBottom') === '1'; } catch(e){}
|
||
if (must) {
|
||
try { localStorage.removeItem('__scrollToBottom'); } catch(e){}
|
||
settleAndScroll();
|
||
} else {
|
||
// mesmo sem submit, mantenha a última resposta à vista
|
||
settleAndScroll();
|
||
}
|
||
});
|
||
</script>
|
||
<script>
|
||
function _grabTableData(itemEl){
|
||
const table = itemEl.querySelector('table');
|
||
if(!table) return null;
|
||
const headers = Array.from(table.querySelectorAll('thead th')).map(th => th.textContent.trim());
|
||
const rows = Array.from(table.querySelectorAll('tbody tr')).map(tr =>
|
||
Array.from(tr.querySelectorAll('td')).map(td => td.textContent)
|
||
);
|
||
return { headers, rows };
|
||
}
|
||
|
||
function _grabQuestion(itemEl){
|
||
const b = itemEl.querySelector('div>b');
|
||
if(b && b.parentElement){
|
||
return b.parentElement.textContent.replace(/^Pergunta:\s*/,'').trim();
|
||
}
|
||
return "Pergunta";
|
||
}
|
||
|
||
function _grabSQL(itemEl){
|
||
const sqlEl = itemEl.querySelector('.sql');
|
||
return sqlEl ? sqlEl.textContent : "";
|
||
}
|
||
|
||
async function exportExcelServer(){
|
||
const items = Array.from(document.querySelectorAll('#history .item'));
|
||
if(items.length === 0){
|
||
alert('Não há respostas para exportar.');
|
||
return;
|
||
}
|
||
|
||
// monta payload
|
||
const payload = { items: [] };
|
||
for(const item of items){
|
||
const question = _grabQuestion(item);
|
||
const sql = _grabSQL(item);
|
||
const table = _grabTableData(item);
|
||
const canvas = item.querySelector('canvas');
|
||
|
||
let chartPng = null;
|
||
if(canvas){
|
||
try{
|
||
chartPng = canvas.toDataURL('image/png'); // inclui prefixo data:
|
||
}catch(e){
|
||
chartPng = null;
|
||
}
|
||
}
|
||
|
||
payload.items.push({
|
||
question, sql, table, chartPng
|
||
});
|
||
}
|
||
|
||
// envia para o servidor
|
||
const res = await fetch("/export/xlsx", {
|
||
method: "POST",
|
||
headers: { "Content-Type": "application/json" },
|
||
body: JSON.stringify(payload)
|
||
});
|
||
|
||
if(!res.ok){
|
||
const t = await res.text();
|
||
console.error('Falha no export:', t);
|
||
alert('Falha ao gerar Excel no servidor.');
|
||
return;
|
||
}
|
||
|
||
// baixa o arquivo
|
||
const blob = await res.blob();
|
||
const url = URL.createObjectURL(blob);
|
||
const a = document.createElement("a");
|
||
a.href = url;
|
||
a.download = "respostas-select-ai.xlsx";
|
||
document.body.appendChild(a);
|
||
a.click();
|
||
a.remove();
|
||
setTimeout(()=>URL.revokeObjectURL(url), 1000);
|
||
}
|
||
// Utilitário para baixar Blob
|
||
function downloadBlob(blob, filename){
|
||
const url = URL.createObjectURL(blob);
|
||
const a = document.createElement('a');
|
||
a.href = url; a.download = filename; a.click();
|
||
setTimeout(()=>URL.revokeObjectURL(url), 1000);
|
||
}
|
||
|
||
// ============ Exportar PDF (com charts + tabelas como na tela) ============
|
||
async function exportPDF(){
|
||
const { jsPDF } = window.jspdf;
|
||
const pdf = new jsPDF('p','mm','a4');
|
||
const pageWidth = pdf.internal.pageSize.getWidth();
|
||
const pageHeight = pdf.internal.pageSize.getHeight();
|
||
const margin = 10;
|
||
const maxW = pageWidth - margin*2;
|
||
|
||
const items = Array.from(document.querySelectorAll('#history .item'));
|
||
if(items.length === 0){ alert('Não há respostas para exportar.'); return; }
|
||
|
||
for(let i=0;i<items.length;i++){
|
||
const el = items[i];
|
||
// usa html2canvas para “fotografar” o card, incluindo canvas e tabelas
|
||
const canvas = await html2canvas(el, {backgroundColor: '#0f1115', scale: 2, useCORS: true});
|
||
const imgData = canvas.toDataURL('image/png');
|
||
// dimensiona para caber na página
|
||
const imgW = maxW;
|
||
const imgH = canvas.height * (imgW / canvas.width);
|
||
|
||
// se altura maior que a página, escala para caber verticalmente
|
||
const effW = imgW;
|
||
const effH = Math.min(imgH, pageHeight - margin*2);
|
||
const scale = Math.min(imgW / canvas.width, (pageHeight - margin*2) / canvas.height);
|
||
const drawW = canvas.width * scale;
|
||
const drawH = canvas.height * scale;
|
||
|
||
if(i>0) pdf.addPage();
|
||
pdf.addImage(imgData, 'PNG', margin, margin, drawW, drawH);
|
||
}
|
||
|
||
pdf.save('respostas-select-ai.pdf');
|
||
}
|
||
|
||
// ============ Exportar Excel (uma planilha por resposta; gráfico como imagem) ============
|
||
// Helper para baixar Blob
|
||
function downloadBlob(blob, filename){
|
||
const url = URL.createObjectURL(blob);
|
||
const a = document.createElement('a');
|
||
a.href = url; a.download = filename; a.click();
|
||
setTimeout(()=>URL.revokeObjectURL(url), 1000);
|
||
}
|
||
|
||
// Helper: insere imagem tentando múltiplas assinaturas do XlsxPopulate
|
||
async function insertImage(wb, sheet, base64Png, topLeftCell, width, height){
|
||
// 1) Tentativa com 'base64' + 'anchor'
|
||
try{
|
||
await sheet.addImage({
|
||
base64: base64Png, // sem prefixo data:
|
||
name: 'chart',
|
||
anchor: topLeftCell, // ex.: 'A12'
|
||
width, height
|
||
});
|
||
return true;
|
||
}catch(e1){
|
||
// 2) Tentativa com 'image' (dataURL completo) + 'topLeftCell'
|
||
try{
|
||
const dataUrl = 'data:image/png;base64,' + base64Png;
|
||
await sheet.addImage({
|
||
image: dataUrl,
|
||
topLeftCell: topLeftCell,
|
||
width, height
|
||
});
|
||
return true;
|
||
}catch(e2){
|
||
// 3) Via workbook.addImage() + sheet.addImage({image})
|
||
try{
|
||
const img = wb.addImage({ base64: base64Png, extension: 'png' });
|
||
await sheet.addImage({
|
||
image: img,
|
||
topLeftCell: topLeftCell,
|
||
width, height
|
||
});
|
||
return true;
|
||
}catch(e3){
|
||
console.warn('Falha ao inserir imagem no Excel:', {e1, e2, e3});
|
||
return false;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
// Exporta TODAS as respostas para Excel: dados + gráfico (PNG)
|
||
async function exportExcel(){
|
||
if (!window.XlsxPopulate) {
|
||
alert("Biblioteca XlsxPopulate não carregada.");
|
||
return;
|
||
}
|
||
|
||
const items = Array.from(document.querySelectorAll('#history .item'));
|
||
if(items.length === 0){
|
||
alert('Não há respostas para exportar.');
|
||
return;
|
||
}
|
||
|
||
try {
|
||
const wb = await XlsxPopulate.fromBlankAsync();
|
||
|
||
for (let i = 0; i < items.length; i++) {
|
||
const item = items[i];
|
||
const name = ("Resp " + (i+1)).slice(0, 31);
|
||
const sheet = wb.addSheet(name);
|
||
|
||
// --- Pergunta ---
|
||
let question = 'Resposta ' + (i+1);
|
||
const qTitle = item.querySelector('div>b');
|
||
if (qTitle && qTitle.parentElement) {
|
||
question = qTitle.parentElement.textContent.replace(/^Pergunta:\s*/,'').trim();
|
||
}
|
||
sheet.cell('A1').value('Pergunta:').style({ bold:true });
|
||
sheet.cell('B1').value(question);
|
||
sheet.row(1).height(22);
|
||
|
||
// --- SQL (se houver) ---
|
||
const sqlEl = item.querySelector('.sql');
|
||
if (sqlEl){
|
||
sheet.cell('A2').value('SQL:').style({ bold:true });
|
||
sheet.cell('B2').value(sqlEl.textContent);
|
||
sheet.column('B').width(100);
|
||
}
|
||
|
||
// --- Tabela (se houver) ---
|
||
let nextRow = 4;
|
||
const tableEl = item.querySelector('table');
|
||
if (tableEl){
|
||
const headers = Array.from(tableEl.querySelectorAll('thead th')).map(th => th.textContent.trim());
|
||
const rows = Array.from(tableEl.querySelectorAll('tbody tr')).map(tr =>
|
||
Array.from(tr.querySelectorAll('td')).map(td => td.textContent)
|
||
);
|
||
|
||
if (headers.length){
|
||
const lastColLetter = String.fromCharCode(64 + headers.length); // A..Z (ok p/ até 26 colunas)
|
||
sheet.range(`A${nextRow}:${lastColLetter}${nextRow}`).value([headers]).style({ bold:true, fill: 'EFEFEF' });
|
||
if (rows.length){
|
||
sheet.range(`A${nextRow+1}:${lastColLetter}${nextRow+rows.length}`).value(rows);
|
||
}
|
||
// larguras aproximadas
|
||
headers.forEach((h, idx) => sheet.column(idx+1).width(Math.max(12, Math.min(40, h.length + 4))));
|
||
nextRow = nextRow + rows.length + 2;
|
||
}
|
||
}
|
||
|
||
// --- Gráfico (se houver) ---
|
||
const canvasEl = item.querySelector('canvas');
|
||
let embedded = false;
|
||
|
||
if (canvasEl){
|
||
try{
|
||
const dataUrl = canvasEl.toDataURL('image/png');
|
||
const base64 = dataUrl.split(',')[1]; // remove prefixo
|
||
embedded = await insertImage(wb, sheet, base64, `A${nextRow}`, 640, 300);
|
||
if (embedded) nextRow += 18;
|
||
}catch(err){
|
||
console.warn('Falha ao capturar canvas:', err);
|
||
}
|
||
}
|
||
|
||
// Fallback: se não tinha canvas ou inserção falhou, captura o card inteiro (html2canvas)
|
||
if (!embedded){
|
||
try{
|
||
const snap = await html2canvas(item, { backgroundColor: '#ffffff', scale: 2, useCORS: true });
|
||
const base64 = snap.toDataURL('image/png').split(',')[1];
|
||
await insertImage(wb, sheet, base64, `A${nextRow}`, 640, 360);
|
||
nextRow += 20;
|
||
}catch(err2){
|
||
console.warn(`Sem gráfico na aba ${name} (seguindo só com dados).`, err2);
|
||
}
|
||
}
|
||
}
|
||
|
||
// (Opcional) renomeia a primeira planilha vazia
|
||
try { wb.sheet(0).name('Resumo'); } catch(e){}
|
||
|
||
const blob = await wb.outputAsync();
|
||
downloadBlob(blob, 'respostas-select-ai.xlsx');
|
||
} catch (e) {
|
||
console.error('Falha ao gerar Excel:', e);
|
||
alert('Falha ao gerar Excel. Veja o console para detalhes.');
|
||
}
|
||
}
|
||
</script>
|
||
</body>
|
||
</html>
|
||
"""
|
||
|
||
def _auto_width(ws, start_col, end_col, extra=2):
|
||
for col_idx in range(start_col, end_col+1):
|
||
col_letter = get_column_letter(col_idx)
|
||
max_len = 0
|
||
for cell in ws[col_letter]:
|
||
try:
|
||
max_len = max(max_len, len(str(cell.value)) if cell.value is not None else 0)
|
||
except:
|
||
pass
|
||
ws.column_dimensions[col_letter].width = min(50, max(12, max_len + extra))
|
||
|
||
@app.route("/export/xlsx", methods=["POST"])
|
||
def export_xlsx():
|
||
"""
|
||
Espera JSON com uma lista 'items'. Cada item:
|
||
{
|
||
"question": str,
|
||
"sql": str|None,
|
||
"table": { "headers":[...], "rows":[[...], ...] } | None,
|
||
"chartPng": "data:image/png;base64,..." | None
|
||
}
|
||
Retorna um arquivo XLSX com 1 aba por resposta.
|
||
"""
|
||
try:
|
||
payload = request.get_json(force=True)
|
||
items = payload.get("items", [])
|
||
if not items:
|
||
return jsonify({"error":"sem itens"}), 400
|
||
|
||
wb = Workbook()
|
||
# use a folha ativa como “Resumo”
|
||
ws0 = wb.active
|
||
ws0.title = "Resumo"
|
||
ws0["A1"] = "Export gerado pelo ER Analytics · Select AI"
|
||
ws0["A2"] = f"Total de respostas: {len(items)}"
|
||
|
||
for i, it in enumerate(items, start=1):
|
||
title = f"Resp {i}"
|
||
ws = wb.create_sheet(title[:31])
|
||
|
||
# Pergunta
|
||
ws["A1"] = "Pergunta:"
|
||
ws["A1"].font = ws["A1"].font.copy(bold=True)
|
||
ws["B1"] = it.get("question") or f"Resposta {i}"
|
||
ws.row_dimensions[1].height = 22
|
||
|
||
# SQL
|
||
sql = (it.get("sql") or "").strip()
|
||
if sql:
|
||
ws["A2"] = "SQL:"
|
||
ws["A2"].font = ws["A2"].font.copy(bold=True)
|
||
ws["B2"] = sql
|
||
|
||
row = 4
|
||
|
||
# Tabela
|
||
table = it.get("table")
|
||
if table and table.get("headers"):
|
||
headers = table["headers"]
|
||
rows = table.get("rows", [])
|
||
for j, h in enumerate(headers, start=1):
|
||
cell = ws.cell(row=row, column=j, value=h)
|
||
cell.font = cell.font.copy(bold=True)
|
||
cell.fill = cell.fill.copy()
|
||
row += 1
|
||
for r in rows:
|
||
for j, val in enumerate(r, start=1):
|
||
ws.cell(row=row, column=j, value=val)
|
||
row += 1
|
||
# largura de colunas
|
||
_auto_width(ws, 1, len(headers))
|
||
row += 1
|
||
|
||
# Gráfico como PNG (se vier)
|
||
chart_png = it.get("chartPng")
|
||
if chart_png:
|
||
try:
|
||
# aceita dataURL com prefixo
|
||
if "," in chart_png:
|
||
chart_png = chart_png.split(",", 1)[1]
|
||
img_bytes = base64.b64decode(chart_png)
|
||
# normaliza via PIL (corrige metadados)
|
||
pil = PILImage.open(BytesIO(img_bytes)).convert("RGBA")
|
||
buf = BytesIO()
|
||
pil.save(buf, format="PNG")
|
||
buf.seek(0)
|
||
xl_img = XLImage(buf)
|
||
anchor_cell = f"A{row}"
|
||
ws.add_image(xl_img, anchor_cell)
|
||
# empurra linhas para não sobrepor texto
|
||
row += 20
|
||
except Exception as e:
|
||
# segue sem o gráfico
|
||
pass
|
||
|
||
# remove folha “Sheet” se sobrar (caso libs criem extra)
|
||
for sh in list(wb.sheetnames):
|
||
if sh.lower().startswith("sheet") and sh != ws0.title:
|
||
try:
|
||
del wb[sh]
|
||
except:
|
||
pass
|
||
|
||
out = BytesIO()
|
||
wb.save(out)
|
||
out.seek(0)
|
||
return send_file(
|
||
out,
|
||
mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
|
||
as_attachment=True,
|
||
download_name="respostas-select-ai.xlsx",
|
||
)
|
||
except Exception as e:
|
||
return jsonify({"error": str(e)}), 500
|
||
|
||
def _to_float(x):
|
||
if x is None: return None
|
||
s = str(x).strip().replace(',', '.')
|
||
if s.endswith('%'): s = s[:-1]
|
||
try:
|
||
return float(s)
|
||
except Exception:
|
||
return None
|
||
|
||
def build_chart(headers, rows):
|
||
"""
|
||
Gera metadados de gráfico + eixos/legenda:
|
||
- 1x1 percentual -> pizza (Internados vs Não)
|
||
- 2 colunas (cat, num) -> barras
|
||
- 3 colunas com (categoria, count, percentage) -> usa percentage; senão count
|
||
- 3 colunas com temporal (col contém MONTH/YEAR/DATE) -> linha
|
||
Retorna dict {type, labels, values, seriesLabel, title, xLabel, yLabel} ou None
|
||
"""
|
||
if not headers or not rows: return None
|
||
H = [h.upper() for h in headers]
|
||
|
||
# 1) 1x1 percentual
|
||
if len(headers) == 1 and len(rows) == 1:
|
||
v = _to_float(rows[0][0])
|
||
if v is None: return None
|
||
pct = v*100 if 0 <= v <= 1 else v
|
||
if 0 <= pct <= 100:
|
||
return {
|
||
"type": "pie",
|
||
"labels": ["Internados", "Não internados"],
|
||
"values": [round(pct,2), round(100-pct,2)],
|
||
"seriesLabel": "Percentual",
|
||
"title": f"{headers[0]}",
|
||
"xLabel": "Status",
|
||
"yLabel": "%"
|
||
}
|
||
|
||
# 2) 2 colunas: categoria × valor
|
||
if len(headers) == 2:
|
||
labels, values = [], []
|
||
for r in rows:
|
||
labels.append(str(r[0]))
|
||
vf = _to_float(r[1])
|
||
if vf is None: return None
|
||
values.append(vf)
|
||
return {
|
||
"type": "bar",
|
||
"labels": labels,
|
||
"values": values,
|
||
"seriesLabel": headers[1],
|
||
"title": f"{headers[1]} por {headers[0]}",
|
||
"xLabel": headers[0],
|
||
"yLabel": headers[1]
|
||
}
|
||
|
||
# 3) 3 colunas: temporal ou categoria + (count, percentage)
|
||
if len(headers) == 3:
|
||
# 3a) temporal
|
||
idx_time = next((i for i,h in enumerate(H) if any(k in h for k in ["DATE","DATA","MONTH","YEAR","TIME"])), None)
|
||
if idx_time is not None:
|
||
idx_val = 2
|
||
labels, values = [], []
|
||
for r in rows:
|
||
labels.append(str(r[idx_time]))
|
||
vf = _to_float(r[idx_val])
|
||
if vf is None: return None
|
||
values.append(vf)
|
||
return {
|
||
"type": "line",
|
||
"labels": labels,
|
||
"values": values,
|
||
"seriesLabel": headers[idx_val],
|
||
"title": f"{headers[idx_val]} por {headers[idx_time]}",
|
||
"xLabel": headers[idx_time],
|
||
"yLabel": headers[idx_val]
|
||
}
|
||
# 3b) categoria + count + percentage
|
||
idx_pct = next((i for i,h in enumerate(H) if "PERCENT" in h), None)
|
||
idx_cnt = next((i for i,h in enumerate(H) if any(k in h for k in ["COUNT","NUMBER"])), None)
|
||
idx_cat = 0
|
||
idx_val = idx_pct if idx_pct is not None else idx_cnt
|
||
if idx_val is not None:
|
||
labels, values = [], []
|
||
for r in rows:
|
||
labels.append(str(r[idx_cat]))
|
||
vf = _to_float(r[idx_val])
|
||
if vf is None: return None
|
||
values.append(vf)
|
||
ylab = headers[idx_val] + (" (%)" if idx_val == idx_pct else "")
|
||
return {
|
||
"type": "bar" if idx_val == idx_cnt else "pie",
|
||
"labels": labels,
|
||
"values": values,
|
||
"seriesLabel": headers[idx_val],
|
||
"title": f"{headers[idx_val]} por {headers[idx_cat]}",
|
||
"xLabel": headers[idx_cat],
|
||
"yLabel": ylab
|
||
}
|
||
|
||
return None
|
||
|
||
def format_table(headers, rows, limit=500):
|
||
if not headers: return None
|
||
rows = rows if len(rows) <= limit else rows[:limit]
|
||
return {"headers": headers, "rows": rows}
|
||
|
||
def run_select_ai(nl_prompt, table_name):
|
||
frase_segura = nl_prompt.replace("'", "''")
|
||
sql = f"SELECT AI '{frase_segura}' FROM {table_name}"
|
||
with pool.acquire() as conn:
|
||
with conn.cursor() as cur:
|
||
cur.execute("select user, dbms_cloud_ai.get_profile() from dual")
|
||
user_, profile_ = cur.fetchone()
|
||
cur.execute(sql)
|
||
rows = cur.fetchall()
|
||
headers = [d[0] for d in cur.description] if cur.description else []
|
||
return sql, headers, rows, user_, profile_
|
||
|
||
def ensure_timeline():
|
||
if "timeline" not in session:
|
||
session["timeline"] = []
|
||
return session["timeline"]
|
||
|
||
@app.route("/", methods=["GET","POST"])
|
||
def index():
|
||
default_table = "MEU_USUARIO.NLU_ED_ADMISSION"
|
||
timeline = ensure_timeline()
|
||
|
||
if request.method == "POST":
|
||
frase = request.form["frase"].strip()
|
||
tabela = request.form.get("tabela", default_table).strip()
|
||
|
||
try:
|
||
sql, headers, rows, user_, profile_ = run_select_ai(frase, tabela)
|
||
table = format_table(headers, rows)
|
||
chart = build_chart(headers, rows)
|
||
|
||
# empilha no histórico (mantém últimos 10)
|
||
timeline.append({
|
||
"prompt": frase,
|
||
"sql": sql,
|
||
"table": table,
|
||
"chart": chart
|
||
})
|
||
if len(timeline) > 10:
|
||
timeline[:] = timeline[-10:]
|
||
session["timeline"] = timeline
|
||
|
||
return render_template_string(
|
||
PAGE,
|
||
timeline=timeline,
|
||
default_table=default_table,
|
||
db_alias=DB_ALIAS,
|
||
session_user=user_,
|
||
profile=profile_
|
||
)
|
||
except Exception as e:
|
||
timeline.append({
|
||
"prompt": frase,
|
||
"sql": None,
|
||
"table": {"headers": ["Erro"], "rows": [[str(e)]]},
|
||
"chart": None
|
||
})
|
||
if len(timeline) > 10:
|
||
timeline[:] = timeline[-10:]
|
||
session["timeline"] = timeline
|
||
return render_template_string(
|
||
PAGE,
|
||
timeline=timeline,
|
||
default_table=default_table,
|
||
db_alias=DB_ALIAS,
|
||
session_user=USERNAME,
|
||
profile=None
|
||
)
|
||
|
||
# GET
|
||
return render_template_string(
|
||
PAGE,
|
||
timeline=timeline,
|
||
default_table=default_table,
|
||
db_alias=DB_ALIAS,
|
||
session_user=USERNAME,
|
||
profile=None
|
||
)
|
||
|
||
if __name__ == "__main__":
|
||
app.run(debug=True, port=5001) |