| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386 |
- <?php
- /**
- * API Reporting - tableau de bord et export (FR10, FR11).
- * GET ?format=json (défaut) | ?format=csv pour export.
- */
- require_once __DIR__ . '/config.php';
- header('Access-Control-Allow-Origin: *');
- header('Access-Control-Allow-Methods: GET');
- $method = $_SERVER['REQUEST_METHOD'] ?? '';
- if ($method !== 'GET') {
- sendError('Méthode non autorisée', 405);
- exit;
- }
- $format = isset($_GET['format']) ? strtolower(trim($_GET['format'])) : 'json';
- if (isMockMode()) {
- $clients = loadMock('clients');
- $factures = loadMock('factures');
- $paiements = loadMock('paiements');
- $encoursParClient = [];
- foreach ($clients as $c) {
- $cid = (int) $c['id'];
- $totalF = 0;
- $totalP = 0;
- foreach ($factures as $f) {
- if ((int) $f['client_id'] === $cid) {
- $totalF += (float) $f['montant_ht'];
- foreach ($paiements as $p) {
- if ((int) $p['facture_id'] === (int) $f['id']) $totalP += (float) $p['montant'];
- }
- }
- }
- $enc = $totalF - $totalP;
- if ($enc > 0) {
- $encoursParClient[] = ['client_id' => $cid, 'nom' => $c['nom'], 'encours' => $enc];
- }
- }
- usort($encoursParClient, fn($a, $b) => $a['encours'] <=> $b['encours']);
- $encoursTotal = array_sum(array_column($encoursParClient, 'encours'));
- $delaiMoyenJ = null;
- $nbPaiements = 0;
- $sommeJours = 0;
- foreach ($paiements as $p) {
- foreach ($factures as $f) {
- if ((int) $f['id'] === (int) $p['facture_id']) {
- $d = (new DateTime($p['date_paiement']))->diff(new DateTime($f['date_facture']))->days;
- $sommeJours += $d;
- $nbPaiements++;
- break;
- }
- }
- }
- if ($nbPaiements > 0) {
- $delaiMoyenJ = (int) round($sommeJours / $nbPaiements);
- }
- // Charger les écheanciers pour calculer les dates d'échéances réelles
- $echeanciers = loadMock('echeanciers');
- $echeancierByFacture = [];
- foreach ($echeanciers as $ech) {
- $echeancierByFacture[(int) $ech['facture_id']] = $ech;
- }
-
- $echeances = [];
- foreach ($factures as $f) {
- $tp = 0;
- foreach ($paiements as $p) {
- if ((int) $p['facture_id'] === (int) $f['id']) $tp += (float) $p['montant'];
- }
- $enc = (float) $f['montant_ht'] - $tp;
- if ($enc > 0) {
- $nom = '';
- foreach ($clients as $c) {
- if ((int) $c['id'] === (int) $f['client_id']) { $nom = $c['nom']; break; }
- }
- $factureId = (int) $f['id'];
-
- // Si la facture a un écheancier, calculer les dates d'échéances réelles
- if (isset($echeancierByFacture[$factureId])) {
- $ech = $echeancierByFacture[$factureId];
- $dateDebut = new DateTime($ech['date_debut_echeancier']);
- $nbEcheances = (int) $ech['nb_echeances'];
- $montantParEch = isset($ech['montant_par_echeance']) ? (float) $ech['montant_par_echeance'] : ($nbEcheances > 0 ? $enc / $nbEcheances : 0);
-
- // Créer une échéance pour chaque date prévue
- for ($i = 0; $i < $nbEcheances; $i++) {
- $dateEcheance = clone $dateDebut;
- $dateEcheance->modify("+{$i} months");
- $echeances[] = [
- 'facture_id' => $factureId,
- 'client_id' => (int) $f['client_id'],
- 'nom' => $nom,
- 'n_facture' => $f['n_facture'],
- 'date_echeance' => $dateEcheance->format('Y-m-d'),
- 'montant_echeance' => $montantParEch,
- 'encours_facture' => $enc,
- 'echeance_num' => $i + 1,
- 'echeance_total' => $nbEcheances,
- ];
- }
- } else {
- // Pas d'écheancier : utiliser la date de facture
- $echeances[] = [
- 'facture_id' => $factureId,
- 'client_id' => (int) $f['client_id'],
- 'nom' => $nom,
- 'n_facture' => $f['n_facture'],
- 'date_echeance' => $f['date_facture'],
- 'montant_echeance' => $enc,
- 'encours_facture' => $enc,
- 'echeance_num' => null,
- 'echeance_total' => null,
- ];
- }
- }
- }
- usort($echeances, fn($a, $b) => strcmp($a['date_echeance'], $b['date_echeance']));
-
- // Filtrer pour ne garder que les échéances à venir (date >= aujourd'hui)
- $aujourdhui = date('Y-m-d');
- $echeances = array_filter($echeances, fn($e) => $e['date_echeance'] >= $aujourdhui);
- $echeances = array_values($echeances);
-
- if ($format === 'csv') {
- header('Content-Type: text/csv; charset=utf-8');
- header('Content-Disposition: attachment; filename=reporting-encours-' . date('Y-m-d') . '.csv');
- $out = fopen('php://output', 'w');
- fprintf($out, "\xEF\xBB\xBF");
- fputcsv($out, ['client_id', 'nom', 'encours'], ';');
- foreach ($encoursParClient as $r) {
- fputcsv($out, [$r['client_id'], $r['nom'], $r['encours']], ';');
- }
- fclose($out);
- exit;
- }
- if ($format === 'factures_csv') {
- header('Content-Type: text/csv; charset=utf-8');
- header('Content-Disposition: attachment; filename=suivi-factures-' . date('Y-m-d') . '.csv');
- $out = fopen('php://output', 'w');
- fprintf($out, "\xEF\xBB\xBF");
- // En-têtes alignés sur l’onglet Factures Excel (Écloserie marine - Suivi des Ventes) + Total payé, Encours
- $headers = ['EXO', 'Mois', 'CLIENT', 'PAYS', 'DEPART DEM', 'NEG.', 'TYPE', 'CYCLE', 'produc', 'PAI', 'DATE FACTUR', 'N° DE VENTE', 'N° FACTURE', 'QTE FACT', 'QTE LIVREE', '% GT', 'CA FACTURE HT', 'PU', 'Total payé', 'Encours'];
- fputcsv($out, $headers, ';');
- foreach ($factures as $f) {
- $nom = '';
- foreach ($clients as $c) {
- if ((int) $c['id'] === (int) $f['client_id']) { $nom = $c['nom']; break; }
- }
- $tp = 0;
- foreach ($paiements as $p) {
- if ((int) $p['facture_id'] === (int) $f['id']) $tp += (float) $p['montant'];
- }
- $enc = (float) $f['montant_ht'] - $tp;
- $cycle = $f['cycle'] ?? '';
- $exo = $cycle ? str_replace('-', '/', $cycle) : ''; // 25-26 -> 25/26
- $typeLabel = isset($f['type_produit']) ? (stripos($f['type_produit'], 'alevin') !== false ? 'Alevin bar' : (stripos($f['type_produit'], 'larve') !== false ? 'Larve bar' : $f['type_produit'])) : '';
- $dateFactur = !empty($f['date_facture']) ? (new DateTime($f['date_facture']))->format('d/m/Y') : '';
- $qteFact = $f['quantites'] ?? '';
- $qteLivree = $f['qte_livree'] ?? '';
- $caHt = $f['montant_ht'] ?? '';
- $pu = '';
- if (!empty($qteFact) && (float) $qteFact > 0 && $caHt !== '') {
- $pu = number_format((float) $caHt / (float) $qteFact, 2, ',', ' ');
- }
- if (is_numeric($caHt)) $caHt = number_format((float) $caHt, 2, ',', ' ');
- if (is_numeric($qteFact)) $qteFact = number_format((float) $qteFact, 0, ',', ' ');
- if (is_numeric($qteLivree)) $qteLivree = number_format((float) $qteLivree, 0, ',', ' ');
- if (is_numeric($tp)) $tp = number_format((float) $tp, 2, ',', ' ');
- if (is_numeric($enc)) $enc = number_format((float) $enc, 2, ',', ' ');
- fputcsv($out, [
- $exo,
- '', // Mois (non géré dans l’appli)
- $nom,
- '', // PAYS
- 'Écloserie marine', // DEPART DEM
- $typeLabel,
- $typeLabel,
- $cycle,
- '', // produc
- '', // PAI
- $dateFactur,
- $f['n_vente'] ?? '',
- $f['n_facture'] ?? '',
- $qteFact,
- $qteLivree,
- '', // % GT
- $caHt,
- $pu,
- $tp,
- $enc,
- ], ';');
- }
- fclose($out);
- exit;
- }
- sendJson([
- 'date_rapport' => date('Y-m-d'),
- 'encours_total' => $encoursTotal,
- 'encours_par_client' => $encoursParClient,
- 'delai_moyen_paiement_j' => $delaiMoyenJ,
- 'echeances_a_venir' => $echeances,
- ]);
- exit;
- }
- $pdo = getPdo();
- $stmt = $pdo->query("
- SELECT c.id AS client_id, c.nom,
- COALESCE(SUM(f.montant_ht), 0) - COALESCE(SUM(p.montant), 0) AS encours
- FROM clients c
- LEFT JOIN factures f ON f.client_id = c.id
- LEFT JOIN paiements p ON p.facture_id = f.id
- GROUP BY c.id, c.nom
- HAVING encours > 0
- ORDER BY encours ASC
- ");
- $encoursParClient = $stmt->fetchAll();
- // Récupérer les factures avec encours et leurs écheanciers
- $stmt = $pdo->query("
- SELECT f.id AS facture_id, f.client_id, c.nom, f.n_facture, f.date_facture, f.montant_ht,
- COALESCE(SUM(p.montant), 0) AS total_paye,
- GREATEST(0, f.montant_ht - COALESCE(SUM(p.montant), 0)) AS encours_facture,
- e.id AS echeancier_id, e.nb_echeances, e.date_debut_echeancier, e.montant_par_echeance
- FROM factures f
- JOIN clients c ON c.id = f.client_id
- LEFT JOIN paiements p ON p.facture_id = f.id
- LEFT JOIN echeanciers e ON e.facture_id = f.id
- GROUP BY f.id, f.client_id, c.nom, f.n_facture, f.date_facture, f.montant_ht, e.id, e.nb_echeances, e.date_debut_echeancier, e.montant_par_echeance
- HAVING encours_facture > 0
- ORDER BY f.date_facture ASC
- ");
- $facturesAvecEncours = $stmt->fetchAll();
- $encoursTotal = 0;
- foreach ($encoursParClient as &$r) {
- $r['encours'] = (float) $r['encours'];
- $encoursTotal += $r['encours'];
- }
- $delaiMoyenJ = null;
- $stmtDelai = $pdo->query("
- SELECT AVG(DATEDIFF(p.date_paiement, f.date_facture)) AS delai_moyen
- FROM paiements p
- JOIN factures f ON f.id = p.facture_id
- ");
- $rowDelai = $stmtDelai->fetch();
- if ($rowDelai && $rowDelai['delai_moyen'] !== null) {
- $delaiMoyenJ = (int) round((float) $rowDelai['delai_moyen']);
- }
- // Calculer les échéances à partir des écheanciers
- $echeances = [];
- foreach ($facturesAvecEncours as $f) {
- $encoursF = (float) $f['encours_facture'];
- $factureId = (int) $f['facture_id'];
-
- // Si la facture a un écheancier, calculer les dates d'échéances réelles
- if ($f['echeancier_id'] !== null && $f['date_debut_echeancier'] !== null) {
- $dateDebut = new DateTime($f['date_debut_echeancier']);
- $nbEcheances = (int) $f['nb_echeances'];
- $montantParEch = $f['montant_par_echeance'] !== null ? (float) $f['montant_par_echeance'] : ($nbEcheances > 0 ? $encoursF / $nbEcheances : 0);
-
- // Créer une échéance pour chaque date prévue
- for ($i = 0; $i < $nbEcheances; $i++) {
- $dateEcheance = clone $dateDebut;
- $dateEcheance->modify("+{$i} months");
- $echeances[] = [
- 'facture_id' => $factureId,
- 'client_id' => (int) $f['client_id'],
- 'nom' => $f['nom'],
- 'n_facture' => $f['n_facture'],
- 'date_echeance' => $dateEcheance->format('Y-m-d'),
- 'montant_echeance' => $montantParEch,
- 'encours_facture' => $encoursF,
- 'echeance_num' => $i + 1,
- 'echeance_total' => $nbEcheances,
- ];
- }
- } else {
- // Pas d'écheancier : utiliser la date de facture
- $echeances[] = [
- 'facture_id' => $factureId,
- 'client_id' => (int) $f['client_id'],
- 'nom' => $f['nom'],
- 'n_facture' => $f['n_facture'],
- 'date_echeance' => $f['date_facture'],
- 'montant_echeance' => $encoursF,
- 'encours_facture' => $encoursF,
- 'echeance_num' => null,
- 'echeance_total' => null,
- ];
- }
- }
- usort($echeances, fn($a, $b) => strcmp($a['date_echeance'], $b['date_echeance']));
- // Filtrer pour ne garder que les échéances à venir (date >= aujourd'hui)
- $aujourdhui = date('Y-m-d');
- $echeances = array_filter($echeances, fn($e) => $e['date_echeance'] >= $aujourdhui);
- $echeances = array_values($echeances);
- if ($format === 'csv') {
- header('Content-Type: text/csv; charset=utf-8');
- header('Content-Disposition: attachment; filename=reporting-encours-' . date('Y-m-d') . '.csv');
- $out = fopen('php://output', 'w');
- fprintf($out, "\xEF\xBB\xBF"); // BOM UTF-8
- fputcsv($out, ['client_id', 'nom', 'encours'], ';');
- foreach ($encoursParClient as $r) {
- fputcsv($out, [$r['client_id'], $r['nom'], $r['encours']], ';');
- }
- fclose($out);
- exit;
- }
- if ($format === 'factures_csv') {
- $stmtF = $pdo->query("
- SELECT f.id, f.client_id, c.nom, f.n_facture, f.n_vente, f.date_facture, f.type_produit, f.cycle, f.quantites, f.qte_livree, f.montant_ht,
- COALESCE(SUM(p.montant), 0) AS total_paye
- FROM factures f
- JOIN clients c ON c.id = f.client_id
- LEFT JOIN paiements p ON p.facture_id = f.id
- GROUP BY f.id, f.client_id, c.nom, f.n_facture, f.n_vente, f.date_facture, f.type_produit, f.cycle, f.quantites, f.qte_livree, f.montant_ht
- ORDER BY f.date_facture DESC, c.nom
- ");
- $allFactures = $stmtF->fetchAll();
- header('Content-Type: text/csv; charset=utf-8');
- header('Content-Disposition: attachment; filename=suivi-factures-' . date('Y-m-d') . '.csv');
- $out = fopen('php://output', 'w');
- fprintf($out, "\xEF\xBB\xBF");
- // En-têtes alignés sur l’onglet Factures Excel (Écloserie marine - Suivi des Ventes) + Total payé, Encours
- $headers = ['EXO', 'Mois', 'CLIENT', 'PAYS', 'DEPART DEM', 'NEG.', 'TYPE', 'CYCLE', 'produc', 'PAI', 'DATE FACTUR', 'N° DE VENTE', 'N° FACTURE', 'QTE FACT', 'QTE LIVREE', '% GT', 'CA FACTURE HT', 'PU', 'Total payé', 'Encours'];
- fputcsv($out, $headers, ';');
- foreach ($allFactures as $f) {
- $totalPaye = (float) $f['total_paye'];
- $enc = (float) $f['montant_ht'] - $totalPaye;
- $cycle = $f['cycle'] ?? '';
- $exo = $cycle ? str_replace('-', '/', $cycle) : '';
- $typeLabel = isset($f['type_produit']) ? (stripos($f['type_produit'], 'alevin') !== false ? 'Alevin bar' : (stripos($f['type_produit'], 'larve') !== false ? 'Larve bar' : $f['type_produit'])) : '';
- $dateFactur = !empty($f['date_facture']) ? (new DateTime($f['date_facture']))->format('d/m/Y') : '';
- $qteFact = $f['quantites'] ?? '';
- $qteLivree = $f['qte_livree'] ?? '';
- $caHt = $f['montant_ht'];
- $pu = '';
- if ($qteFact !== '' && $qteFact !== null && (float) $qteFact > 0) {
- $pu = number_format((float) $caHt / (float) $qteFact, 2, ',', ' ');
- }
- $caHt = number_format((float) $caHt, 2, ',', ' ');
- if ($qteFact !== '' && $qteFact !== null) $qteFact = number_format((float) $qteFact, 0, ',', ' ');
- if ($qteLivree !== '' && $qteLivree !== null) $qteLivree = number_format((float) $qteLivree, 0, ',', ' ');
- $totalPayeStr = number_format($totalPaye, 2, ',', ' ');
- $encStr = number_format($enc, 2, ',', ' ');
- fputcsv($out, [
- $exo,
- '',
- $f['nom'],
- '',
- 'Écloserie marine',
- $typeLabel,
- $typeLabel,
- $cycle ?? '',
- '',
- '',
- $dateFactur,
- $f['n_vente'] ?? '',
- $f['n_facture'],
- $qteFact,
- $qteLivree,
- '',
- $caHt,
- $pu,
- $totalPayeStr,
- $encStr,
- ], ';');
- }
- fclose($out);
- exit;
- }
- sendJson([
- 'date_rapport' => date('Y-m-d'),
- 'encours_total' => $encoursTotal,
- 'encours_par_client' => $encoursParClient,
- 'delai_moyen_paiement_j' => $delaiMoyenJ,
- 'echeances_a_venir' => $echeances,
- ]);
|