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, ]);