157 lines
8.0 KiB
PHP
157 lines
8.0 KiB
PHP
<?php
|
||
/**
|
||
* Visual Management Live API
|
||
* Doctrine 65: VISUAL_MANAGEMENT - Lean Six Sigma KPI aggregation
|
||
* 0 hardcode · live fetch · UX premium
|
||
*/
|
||
header('Content-Type: application/json');
|
||
header('Access-Control-Allow-Origin: *');
|
||
header('Cache-Control: no-cache, max-age=30');
|
||
|
||
function q($sql) {
|
||
$cmd = 'PGPASSWORD=admin123 psql -h 10.1.0.3 -U admin -d adx_system -t -A -F "|" -c ' . escapeshellarg($sql) . ' 2>&1';
|
||
$raw = @shell_exec($cmd);
|
||
$rows = [];
|
||
foreach (array_filter(array_map('trim', explode("\n", $raw ?? ''))) as $line) {
|
||
// Support single-column AND multi-column rows
|
||
if (strpos($line, '|') !== false) {
|
||
$rows[] = array_map('trim', explode('|', $line));
|
||
} else {
|
||
$rows[] = [$line];
|
||
}
|
||
}
|
||
return $rows;
|
||
}
|
||
|
||
function qone($sql) {
|
||
$r = q($sql);
|
||
return $r && isset($r[0][0]) ? $r[0][0] : null;
|
||
}
|
||
|
||
$out = ['ts' => date('c'), 'generator' => 'visual-management-live.php v1 doctrine 65'];
|
||
|
||
// 1. BUSINESS KPIs (CRM pipeline state)
|
||
$out['business'] = [
|
||
'crm_deals' => (int)qone("SELECT count(*) FROM admin.pipeline_deals"),
|
||
'crm_deals_amount_eur' => (int)qone("SELECT COALESCE(SUM(amount),0) FROM admin.pipeline_deals WHERE currency='EUR'"),
|
||
'crm_companies' => (int)qone("SELECT count(*) FROM admin.pipeline_companies"),
|
||
'crm_contacts' => (int)qone("SELECT count(*) FROM admin.pipeline_contacts"),
|
||
'crm_contacts_b2b' => (int)qone("SELECT count(*) FROM admin.pipeline_contacts WHERE segment_type='B2B'"),
|
||
'crm_contacts_linked' => (int)qone("SELECT count(*) FROM admin.pipeline_contacts WHERE company_id IS NOT NULL"),
|
||
'crm_activities' => (int)qone("SELECT count(*) FROM admin.pipeline_activities"),
|
||
'crm_activities_last_7d' => (int)qone("SELECT count(*) FROM admin.pipeline_activities WHERE created_at > NOW() - interval '7 days'"),
|
||
'ethica_hcps' => (int)qone("SELECT count(*) FROM ethica.medecins_real"),
|
||
'ethica_hcps_dz' => (int)qone("SELECT count(*) FROM ethica.medecins_real WHERE pays='DZ'"),
|
||
'ethica_hcps_ma' => (int)qone("SELECT count(*) FROM ethica.medecins_real WHERE pays='MA'"),
|
||
'ethica_hcps_tn' => (int)qone("SELECT count(*) FROM ethica.medecins_real WHERE pays='TN'"),
|
||
'office_accounts' => (int)qone("SELECT count(*) FROM admin.office_accounts"),
|
||
'office_active' => (int)qone("SELECT count(*) FROM admin.office_accounts WHERE status='active'"),
|
||
'office_warming' => (int)qone("SELECT count(*) FROM admin.office_accounts WHERE status='warming'"),
|
||
];
|
||
|
||
// 2. FLUX KPIs (flow detection - doctrine 55 anti-staleness)
|
||
$out['flux'] = [
|
||
'send_contacts_last_7d' => (int)qone("SELECT count(*) FROM admin.send_contacts WHERE created_at > NOW() - interval '7 days'"),
|
||
'send_contacts_last_30d' => (int)qone("SELECT count(*) FROM admin.send_contacts WHERE created_at > NOW() - interval '30 days'"),
|
||
'send_contacts_last_created' => qone("SELECT MAX(created_at)::text FROM admin.send_contacts"),
|
||
'graph_send_last_7d' => (int)qone("SELECT count(*) FROM admin.graph_send_log WHERE created_at > NOW() - interval '7 days'"),
|
||
'graph_send_last_created' => qone("SELECT MAX(created_at)::text FROM admin.graph_send_log"),
|
||
'leads_last_7d' => (int)qone("SELECT count(*) FROM admin.leads WHERE created_at > NOW() - interval '7 days'"),
|
||
'weval_leads_last_7d' => (int)qone("SELECT count(*) FROM admin.weval_leads WHERE created_at > NOW() - interval '7 days'"),
|
||
'pipeline_deals_last_30d' => (int)qone("SELECT count(*) FROM admin.pipeline_deals WHERE created_at > NOW() - interval '30 days'"),
|
||
'pipeline_contacts_last_24h' => (int)qone("SELECT count(*) FROM admin.pipeline_contacts WHERE created_at > NOW() - interval '24 hours'"),
|
||
];
|
||
|
||
// 3. QUALITY KPIs (Lean 6σ)
|
||
// NonReg + L99
|
||
$nr = json_decode(@file_get_contents('http://127.0.0.1/api/nonreg-api.php?cat=all'), true);
|
||
$l99 = json_decode(@file_get_contents('http://127.0.0.1/api/l99-api.php?action=stats'), true);
|
||
$out['quality'] = [
|
||
'nonreg_pass' => (int)($nr['pass'] ?? 0),
|
||
'nonreg_total' => (int)($nr['total'] ?? 0),
|
||
'nonreg_score' => (float)($nr['score'] ?? 0),
|
||
'l99_pass' => (int)($l99['pass'] ?? 0),
|
||
'l99_total' => (int)($l99['total'] ?? 0),
|
||
'l99_score' => (float)($l99['score'] ?? 0),
|
||
];
|
||
|
||
// 4. ANDON ALERTS (flux stagnation detection - doctrine 55 KPI gap fix)
|
||
$andons = [];
|
||
$last_send = strtotime($out['flux']['send_contacts_last_created'] ?? 'now');
|
||
$days_since_send = ($last_send && $last_send > 0) ? floor((time() - $last_send) / 86400) : 999;
|
||
if ($days_since_send > 7) {
|
||
$andons[] = ['severity' => 'RED', 'kpi' => 'send_contacts flux', 'message' => "send_contacts aucun ajout depuis $days_since_send jours"];
|
||
}
|
||
|
||
if ($out['flux']['graph_send_last_7d'] < 100) {
|
||
$andons[] = ['severity' => 'ORANGE', 'kpi' => 'graph_send_log flux', 'message' => "Emails envoyés 7j: " . $out['flux']['graph_send_last_7d'] . " (objectif >1000)"];
|
||
}
|
||
|
||
if ($out['flux']['pipeline_deals_last_30d'] == 0) {
|
||
$andons[] = ['severity' => 'RED', 'kpi' => 'crm_deals flux', 'message' => "Aucun nouveau deal en 30 jours (pipeline commercial stale)"];
|
||
}
|
||
|
||
if ($out['business']['crm_contacts_b2b'] > 0 && $out['business']['crm_activities'] < 100) {
|
||
$andons[] = ['severity' => 'ORANGE', 'kpi' => 'crm_activities', 'message' => "Activities = " . $out['business']['crm_activities'] . " vs " . $out['business']['crm_contacts_b2b'] . " contacts B2B — sous-exploité"];
|
||
}
|
||
|
||
if ($out['quality']['nonreg_score'] < 100) {
|
||
$andons[] = ['severity' => 'ORANGE', 'kpi' => 'nonreg', 'message' => "NonReg score " . $out['quality']['nonreg_score'] . "% — régressions détectées"];
|
||
}
|
||
|
||
$out['andons'] = $andons;
|
||
$out['andons_count'] = count($andons);
|
||
|
||
// 5. INFRA KPIs
|
||
$load = trim(@shell_exec('cat /proc/loadavg 2>&1') ?? '');
|
||
$load_parts = explode(' ', $load);
|
||
$mem = trim(@shell_exec("free -m | awk 'NR==2{printf \"%.1f\", \$3*100/\$2}'") ?? '0');
|
||
$disk = trim(@shell_exec("df / | awk 'NR==2{print \$5}' | tr -d '%'") ?? '0');
|
||
$out['infra'] = [
|
||
'load_1m' => (float)($load_parts[0] ?? 0),
|
||
'load_5m' => (float)($load_parts[1] ?? 0),
|
||
'mem_pct' => (float)$mem,
|
||
'disk_pct' => (int)$disk,
|
||
'uptime_hours' => (int)(trim(@shell_exec("awk '{print int(\$1/3600)}' /proc/uptime") ?? 0)),
|
||
'docker_containers' => (int)trim(@shell_exec("docker ps -q 2>/dev/null | wc -l") ?? 0),
|
||
'fpm_workers' => (int)trim(@shell_exec("pgrep -c 'php-fpm: pool www' 2>/dev/null") ?? 0),
|
||
];
|
||
|
||
// 6. CLASSIFICATION KPIs (doctrine 63)
|
||
$out['classification'] = [
|
||
'leads_classified_pct' => 100.0 * (int)qone("SELECT count(*) FROM admin.leads WHERE segment_type IS NOT NULL") / max(1, (int)qone("SELECT count(*) FROM admin.leads")),
|
||
'send_contacts_classified_pct' => 100.0 * (int)qone("SELECT count(*) FROM admin.send_contacts WHERE segment_type IS NOT NULL") / max(1, (int)qone("SELECT count(*) FROM admin.send_contacts")),
|
||
'leads_b2b' => (int)qone("SELECT count(*) FROM admin.leads WHERE segment_type='B2B'"),
|
||
'send_contacts_b2b' => (int)qone("SELECT count(*) FROM admin.send_contacts WHERE segment_type='B2B'"),
|
||
];
|
||
|
||
// Global health score (weighted)
|
||
$health = 0;
|
||
$health += min(25, $out['quality']['nonreg_score'] / 4);
|
||
$health += min(25, $out['quality']['l99_score'] / 4);
|
||
$health += $andons ? max(0, 25 - count($andons) * 5) : 25;
|
||
$health += ($out['business']['crm_contacts_b2b'] > 1000) ? 25 : min(25, $out['business']['crm_contacts_b2b'] / 40);
|
||
$out['health_score'] = round($health, 1);
|
||
$out['health_status'] = $health >= 85 ? 'GREEN' : ($health >= 60 ? 'AMBER' : 'RED');
|
||
|
||
// 7. KPI HISTORY (doctrine 65 - 30 derniers jours)
|
||
$hist_rows = q("SELECT snap_date::text, crm_deals, crm_companies, crm_contacts, crm_contacts_b2b, crm_activities, ethica_hcps, health_score, andons_count FROM admin.kpi_history_daily WHERE snap_date > CURRENT_DATE - interval '30 days' ORDER BY snap_date ASC LIMIT 31");
|
||
$out['history'] = [];
|
||
foreach ($hist_rows as $h) {
|
||
if (count($h) >= 9) {
|
||
$out['history'][] = [
|
||
'date' => $h[0],
|
||
'deals' => (int)$h[1],
|
||
'companies' => (int)$h[2],
|
||
'contacts' => (int)$h[3],
|
||
'contacts_b2b' => (int)$h[4],
|
||
'activities' => (int)$h[5],
|
||
'hcps' => (int)$h[6],
|
||
'health' => (float)$h[7],
|
||
'andons' => (int)$h[8],
|
||
];
|
||
}
|
||
}
|
||
|
||
echo json_encode($out, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
|