185 lines
6.8 KiB
PHP
185 lines
6.8 KiB
PHP
<?php
|
|
/**
|
|
* V174 · ambre-xlsx-gen.php
|
|
* Real XLSX generation using Python openpyxl via shell_exec
|
|
* Yacine: Excel KO · genere docx au lieu xlsx
|
|
* Cause: ambre-xlsx-gen.php missing, fallback to docx
|
|
* Fix: this file generates real xlsx via openpyxl (already installed)
|
|
*/
|
|
header("Content-Type: application/json; charset=utf-8");
|
|
set_time_limit(60);
|
|
$t0 = microtime(true);
|
|
|
|
$topic = trim($_GET["topic"] ?? $_POST["topic"] ?? "");
|
|
if (!$topic) {
|
|
echo json_encode(["error"=>"topic required"]);
|
|
exit;
|
|
}
|
|
|
|
// 1. Use LLM to generate JSON structure for the spreadsheet
|
|
$sys = "Tu es generateur de donnees spreadsheet. Reponds UNIQUEMENT avec JSON valide, pas de markdown. Schema: {\"sheet_name\":\"...\",\"headers\":[\"col1\",\"col2\",...],\"rows\":[[\"val1\",\"val2\",...],...],\"kpis\":{\"metric\":\"value\",...}}. MAX 8 columns, 15 rows. Donnees realistes en francais avec chiffres et pourcentages.";
|
|
$user = "Genere donnees spreadsheet pour: $topic";
|
|
$llm_raw = @file_get_contents("http://127.0.0.1:4000/v1/chat/completions", false, stream_context_create([
|
|
"http" => [
|
|
"method" => "POST",
|
|
"header" => "Content-Type: application/json\r\n",
|
|
"content" => json_encode([
|
|
"model" => "fast",
|
|
"messages" => [
|
|
["role"=>"system","content"=>$sys],
|
|
["role"=>"user","content"=>$user],
|
|
],
|
|
"max_tokens" => 1200,
|
|
"temperature" => 0.3,
|
|
]),
|
|
"timeout" => 30,
|
|
],
|
|
]));
|
|
$llm_d = @json_decode($llm_raw, true);
|
|
$content = $llm_d["choices"][0]["message"]["content"] ?? "";
|
|
$content = trim(preg_replace("/```(?:json)?\n?|```/", "", $content));
|
|
|
|
// Parse JSON
|
|
$data = @json_decode($content, true);
|
|
if (!$data || !isset($data["headers"]) || !isset($data["rows"])) {
|
|
// Fallback minimal
|
|
$data = [
|
|
"sheet_name" => mb_substr($topic, 0, 30),
|
|
"headers" => ["Categorie", "Valeur", "Pourcentage"],
|
|
"rows" => [
|
|
["Donnee 1", 100, "25%"],
|
|
["Donnee 2", 200, "50%"],
|
|
["Donnee 3", 300, "75%"],
|
|
],
|
|
"kpis" => ["Total"=>"600","Moyenne"=>"200"],
|
|
];
|
|
}
|
|
|
|
// 2. Call Python script to generate xlsx
|
|
$slug = preg_replace("/[^a-z0-9-]/", "-", strtolower($topic));
|
|
$slug = trim(preg_replace("/-+/", "-", $slug), "-");
|
|
$slug = mb_substr($slug, 0, 40);
|
|
$filename = "wevia-xlsx-" . $slug . "-" . date("Ymd-His") . "-" . substr(md5($topic.rand()), 0, 6) . ".xlsx";
|
|
$filepath = "/var/www/html/generated/" . $filename;
|
|
|
|
// JSON data file for Python to read (avoid shell escaping issues)
|
|
$json_tmp = "/tmp/wevia-xlsx-" . uniqid() . ".json";
|
|
file_put_contents($json_tmp, json_encode($data, JSON_UNESCAPED_UNICODE));
|
|
|
|
$py_script = <<<'PYEND'
|
|
import json, sys, openpyxl
|
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
|
|
from openpyxl.chart import BarChart, Reference
|
|
json_file = sys.argv[1]
|
|
xlsx_out = sys.argv[2]
|
|
topic = sys.argv[3]
|
|
with open(json_file) as f:
|
|
data = json.load(f)
|
|
wb = openpyxl.Workbook()
|
|
ws = wb.active
|
|
ws.title = (data.get("sheet_name") or topic[:30])[:30]
|
|
# Header styling
|
|
header_font = Font(bold=True, color="FFFFFF", size=12)
|
|
header_fill = PatternFill(start_color="6366F1", end_color="6366F1", fill_type="solid")
|
|
header_align = Alignment(horizontal="center", vertical="center")
|
|
# Title row
|
|
ws["A1"] = topic
|
|
ws["A1"].font = Font(bold=True, size=16, color="4338CA")
|
|
ws["A1"].alignment = Alignment(horizontal="left", vertical="center")
|
|
ws.row_dimensions[1].height = 30
|
|
# Headers row 3
|
|
headers = data.get("headers", [])
|
|
for i, h in enumerate(headers):
|
|
cell = ws.cell(row=3, column=i+1, value=h)
|
|
cell.font = header_font
|
|
cell.fill = header_fill
|
|
cell.alignment = header_align
|
|
# Data rows
|
|
rows = data.get("rows", [])
|
|
for ri, row in enumerate(rows):
|
|
for ci, val in enumerate(row):
|
|
c = ws.cell(row=4+ri, column=ci+1, value=val)
|
|
c.alignment = Alignment(vertical="center")
|
|
if ri % 2 == 0:
|
|
c.fill = PatternFill(start_color="F1F5F9", end_color="F1F5F9", fill_type="solid")
|
|
# Column widths
|
|
for i, h in enumerate(headers):
|
|
ws.column_dimensions[chr(65+i)].width = max(15, len(str(h))+5)
|
|
# KPIs section
|
|
kpi_row = 4 + len(rows) + 2
|
|
kpis = data.get("kpis", {})
|
|
if kpis:
|
|
kpi_cell = ws.cell(row=kpi_row, column=1, value="KPIs")
|
|
kpi_cell.font = Font(bold=True, size=14, color="4338CA")
|
|
for i, (k, v) in enumerate(kpis.items()):
|
|
ws.cell(row=kpi_row+1+i, column=1, value=k).font = Font(bold=True)
|
|
ws.cell(row=kpi_row+1+i, column=2, value=v)
|
|
# Add chart if numeric data
|
|
try:
|
|
numeric_col = None
|
|
for ci, h in enumerate(headers):
|
|
if any(isinstance(r[ci], (int, float)) if ci < len(r) else False for r in rows):
|
|
numeric_col = ci
|
|
break
|
|
if numeric_col is not None and len(rows) > 0:
|
|
chart = BarChart()
|
|
chart.title = "Visualisation"
|
|
chart.style = 13
|
|
data_ref = Reference(ws, min_col=numeric_col+1, min_row=3, max_row=3+len(rows), max_col=numeric_col+1)
|
|
cats_ref = Reference(ws, min_col=1, min_row=4, max_row=3+len(rows))
|
|
chart.add_data(data_ref, titles_from_data=True)
|
|
chart.set_categories(cats_ref)
|
|
chart.width = 15
|
|
chart.height = 8
|
|
ws.add_chart(chart, f"E{kpi_row}")
|
|
except Exception as e:
|
|
print(f"Chart error: {e}", file=sys.stderr)
|
|
wb.save(xlsx_out)
|
|
print("OK")
|
|
PYEND;
|
|
|
|
$py_tmp = "/tmp/wevia-xlsx-gen-" . uniqid() . ".py";
|
|
file_put_contents($py_tmp, $py_script);
|
|
|
|
$cmd = "python3 " . escapeshellarg($py_tmp) . " " . escapeshellarg($json_tmp) . " " . escapeshellarg($filepath) . " " . escapeshellarg($topic) . " 2>&1";
|
|
$out = shell_exec($cmd);
|
|
@unlink($py_tmp);
|
|
@unlink($json_tmp);
|
|
|
|
if (!file_exists($filepath) || filesize($filepath) < 100) {
|
|
echo json_encode(["error"=>"xlsx generation failed","python_out"=>$out]);
|
|
exit;
|
|
}
|
|
|
|
$size = filesize($filepath);
|
|
$elapsed = round((microtime(true) - $t0) * 1000);
|
|
$full_url = "https://weval-consulting.com/generated/" . $filename;
|
|
|
|
// Build preview text
|
|
$preview = "📊 **" . $topic . "** (Excel reel avec graphique)\n\n";
|
|
$preview .= "Feuille: " . ($data["sheet_name"] ?? $topic) . "\n";
|
|
$preview .= "Colonnes: " . count($data["headers"] ?? []) . " · Lignes: " . count($data["rows"] ?? []) . "\n";
|
|
if (!empty($data["kpis"])) {
|
|
$preview .= "\nKPIs:\n";
|
|
foreach ($data["kpis"] as $k => $v) {
|
|
$preview .= "- **$k**: $v\n";
|
|
}
|
|
}
|
|
|
|
echo json_encode([
|
|
"ok" => true,
|
|
"url" => "/generated/" . $filename,
|
|
"full_url" => $full_url,
|
|
"size_bytes" => $size,
|
|
"size_human" => round($size/1024, 1) . "KB",
|
|
"elapsed_ms" => $elapsed,
|
|
"engine" => "openpyxl",
|
|
"provider" => "WEVIA XLSX Engine V174",
|
|
"topic" => $topic,
|
|
"preview" => $preview,
|
|
"sheet_name" => $data["sheet_name"] ?? $topic,
|
|
"rows_count" => count($data["rows"] ?? []),
|
|
"columns_count" => count($data["headers"] ?? []),
|
|
"has_chart" => true,
|
|
], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
|