reporting.php 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386
  1. <?php
  2. /**
  3. * API Reporting - tableau de bord et export (FR10, FR11).
  4. * GET ?format=json (défaut) | ?format=csv pour export.
  5. */
  6. require_once __DIR__ . '/config.php';
  7. header('Access-Control-Allow-Origin: *');
  8. header('Access-Control-Allow-Methods: GET');
  9. $method = $_SERVER['REQUEST_METHOD'] ?? '';
  10. if ($method !== 'GET') {
  11. sendError('Méthode non autorisée', 405);
  12. exit;
  13. }
  14. $format = isset($_GET['format']) ? strtolower(trim($_GET['format'])) : 'json';
  15. if (isMockMode()) {
  16. $clients = loadMock('clients');
  17. $factures = loadMock('factures');
  18. $paiements = loadMock('paiements');
  19. $encoursParClient = [];
  20. foreach ($clients as $c) {
  21. $cid = (int) $c['id'];
  22. $totalF = 0;
  23. $totalP = 0;
  24. foreach ($factures as $f) {
  25. if ((int) $f['client_id'] === $cid) {
  26. $totalF += (float) $f['montant_ht'];
  27. foreach ($paiements as $p) {
  28. if ((int) $p['facture_id'] === (int) $f['id']) $totalP += (float) $p['montant'];
  29. }
  30. }
  31. }
  32. $enc = $totalF - $totalP;
  33. if ($enc > 0) {
  34. $encoursParClient[] = ['client_id' => $cid, 'nom' => $c['nom'], 'encours' => $enc];
  35. }
  36. }
  37. usort($encoursParClient, fn($a, $b) => $a['encours'] <=> $b['encours']);
  38. $encoursTotal = array_sum(array_column($encoursParClient, 'encours'));
  39. $delaiMoyenJ = null;
  40. $nbPaiements = 0;
  41. $sommeJours = 0;
  42. foreach ($paiements as $p) {
  43. foreach ($factures as $f) {
  44. if ((int) $f['id'] === (int) $p['facture_id']) {
  45. $d = (new DateTime($p['date_paiement']))->diff(new DateTime($f['date_facture']))->days;
  46. $sommeJours += $d;
  47. $nbPaiements++;
  48. break;
  49. }
  50. }
  51. }
  52. if ($nbPaiements > 0) {
  53. $delaiMoyenJ = (int) round($sommeJours / $nbPaiements);
  54. }
  55. // Charger les écheanciers pour calculer les dates d'échéances réelles
  56. $echeanciers = loadMock('echeanciers');
  57. $echeancierByFacture = [];
  58. foreach ($echeanciers as $ech) {
  59. $echeancierByFacture[(int) $ech['facture_id']] = $ech;
  60. }
  61. $echeances = [];
  62. foreach ($factures as $f) {
  63. $tp = 0;
  64. foreach ($paiements as $p) {
  65. if ((int) $p['facture_id'] === (int) $f['id']) $tp += (float) $p['montant'];
  66. }
  67. $enc = (float) $f['montant_ht'] - $tp;
  68. if ($enc > 0) {
  69. $nom = '';
  70. foreach ($clients as $c) {
  71. if ((int) $c['id'] === (int) $f['client_id']) { $nom = $c['nom']; break; }
  72. }
  73. $factureId = (int) $f['id'];
  74. // Si la facture a un écheancier, calculer les dates d'échéances réelles
  75. if (isset($echeancierByFacture[$factureId])) {
  76. $ech = $echeancierByFacture[$factureId];
  77. $dateDebut = new DateTime($ech['date_debut_echeancier']);
  78. $nbEcheances = (int) $ech['nb_echeances'];
  79. $montantParEch = isset($ech['montant_par_echeance']) ? (float) $ech['montant_par_echeance'] : ($nbEcheances > 0 ? $enc / $nbEcheances : 0);
  80. // Créer une échéance pour chaque date prévue
  81. for ($i = 0; $i < $nbEcheances; $i++) {
  82. $dateEcheance = clone $dateDebut;
  83. $dateEcheance->modify("+{$i} months");
  84. $echeances[] = [
  85. 'facture_id' => $factureId,
  86. 'client_id' => (int) $f['client_id'],
  87. 'nom' => $nom,
  88. 'n_facture' => $f['n_facture'],
  89. 'date_echeance' => $dateEcheance->format('Y-m-d'),
  90. 'montant_echeance' => $montantParEch,
  91. 'encours_facture' => $enc,
  92. 'echeance_num' => $i + 1,
  93. 'echeance_total' => $nbEcheances,
  94. ];
  95. }
  96. } else {
  97. // Pas d'écheancier : utiliser la date de facture
  98. $echeances[] = [
  99. 'facture_id' => $factureId,
  100. 'client_id' => (int) $f['client_id'],
  101. 'nom' => $nom,
  102. 'n_facture' => $f['n_facture'],
  103. 'date_echeance' => $f['date_facture'],
  104. 'montant_echeance' => $enc,
  105. 'encours_facture' => $enc,
  106. 'echeance_num' => null,
  107. 'echeance_total' => null,
  108. ];
  109. }
  110. }
  111. }
  112. usort($echeances, fn($a, $b) => strcmp($a['date_echeance'], $b['date_echeance']));
  113. // Filtrer pour ne garder que les échéances à venir (date >= aujourd'hui)
  114. $aujourdhui = date('Y-m-d');
  115. $echeances = array_filter($echeances, fn($e) => $e['date_echeance'] >= $aujourdhui);
  116. $echeances = array_values($echeances);
  117. if ($format === 'csv') {
  118. header('Content-Type: text/csv; charset=utf-8');
  119. header('Content-Disposition: attachment; filename=reporting-encours-' . date('Y-m-d') . '.csv');
  120. $out = fopen('php://output', 'w');
  121. fprintf($out, "\xEF\xBB\xBF");
  122. fputcsv($out, ['client_id', 'nom', 'encours'], ';');
  123. foreach ($encoursParClient as $r) {
  124. fputcsv($out, [$r['client_id'], $r['nom'], $r['encours']], ';');
  125. }
  126. fclose($out);
  127. exit;
  128. }
  129. if ($format === 'factures_csv') {
  130. header('Content-Type: text/csv; charset=utf-8');
  131. header('Content-Disposition: attachment; filename=suivi-factures-' . date('Y-m-d') . '.csv');
  132. $out = fopen('php://output', 'w');
  133. fprintf($out, "\xEF\xBB\xBF");
  134. // En-têtes alignés sur l’onglet Factures Excel (EMGI_FTI_Suivi des Ventes) + Total payé, Encours
  135. $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'];
  136. fputcsv($out, $headers, ';');
  137. foreach ($factures as $f) {
  138. $nom = '';
  139. foreach ($clients as $c) {
  140. if ((int) $c['id'] === (int) $f['client_id']) { $nom = $c['nom']; break; }
  141. }
  142. $tp = 0;
  143. foreach ($paiements as $p) {
  144. if ((int) $p['facture_id'] === (int) $f['id']) $tp += (float) $p['montant'];
  145. }
  146. $enc = (float) $f['montant_ht'] - $tp;
  147. $cycle = $f['cycle'] ?? '';
  148. $exo = $cycle ? str_replace('-', '/', $cycle) : ''; // 25-26 -> 25/26
  149. $typeLabel = isset($f['type_produit']) ? (stripos($f['type_produit'], 'alevin') !== false ? 'Alevin Bar' : (stripos($f['type_produit'], 'larve') !== false ? 'Larve Bar' : $f['type_produit'])) : '';
  150. $dateFactur = !empty($f['date_facture']) ? (new DateTime($f['date_facture']))->format('d/m/Y') : '';
  151. $qteFact = $f['quantites'] ?? '';
  152. $qteLivree = $f['qte_livree'] ?? '';
  153. $caHt = $f['montant_ht'] ?? '';
  154. $pu = '';
  155. if (!empty($qteFact) && (float) $qteFact > 0 && $caHt !== '') {
  156. $pu = number_format((float) $caHt / (float) $qteFact, 2, ',', ' ');
  157. }
  158. if (is_numeric($caHt)) $caHt = number_format((float) $caHt, 2, ',', ' ');
  159. if (is_numeric($qteFact)) $qteFact = number_format((float) $qteFact, 0, ',', ' ');
  160. if (is_numeric($qteLivree)) $qteLivree = number_format((float) $qteLivree, 0, ',', ' ');
  161. if (is_numeric($tp)) $tp = number_format((float) $tp, 2, ',', ' ');
  162. if (is_numeric($enc)) $enc = number_format((float) $enc, 2, ',', ' ');
  163. fputcsv($out, [
  164. $exo,
  165. '', // Mois (non géré dans l’appli)
  166. $nom,
  167. '', // PAYS
  168. 'EMGI', // DEPART DEM
  169. $typeLabel,
  170. $typeLabel,
  171. $cycle,
  172. '', // produc
  173. '', // PAI
  174. $dateFactur,
  175. $f['n_vente'] ?? '',
  176. $f['n_facture'] ?? '',
  177. $qteFact,
  178. $qteLivree,
  179. '', // % GT
  180. $caHt,
  181. $pu,
  182. $tp,
  183. $enc,
  184. ], ';');
  185. }
  186. fclose($out);
  187. exit;
  188. }
  189. sendJson([
  190. 'date_rapport' => date('Y-m-d'),
  191. 'encours_total' => $encoursTotal,
  192. 'encours_par_client' => $encoursParClient,
  193. 'delai_moyen_paiement_j' => $delaiMoyenJ,
  194. 'echeances_a_venir' => $echeances,
  195. ]);
  196. exit;
  197. }
  198. $pdo = getPdo();
  199. $stmt = $pdo->query("
  200. SELECT c.id AS client_id, c.nom,
  201. COALESCE(SUM(f.montant_ht), 0) - COALESCE(SUM(p.montant), 0) AS encours
  202. FROM clients c
  203. LEFT JOIN factures f ON f.client_id = c.id
  204. LEFT JOIN paiements p ON p.facture_id = f.id
  205. GROUP BY c.id, c.nom
  206. HAVING encours > 0
  207. ORDER BY encours ASC
  208. ");
  209. $encoursParClient = $stmt->fetchAll();
  210. // Récupérer les factures avec encours et leurs écheanciers
  211. $stmt = $pdo->query("
  212. SELECT f.id AS facture_id, f.client_id, c.nom, f.n_facture, f.date_facture, f.montant_ht,
  213. COALESCE(SUM(p.montant), 0) AS total_paye,
  214. GREATEST(0, f.montant_ht - COALESCE(SUM(p.montant), 0)) AS encours_facture,
  215. e.id AS echeancier_id, e.nb_echeances, e.date_debut_echeancier, e.montant_par_echeance
  216. FROM factures f
  217. JOIN clients c ON c.id = f.client_id
  218. LEFT JOIN paiements p ON p.facture_id = f.id
  219. LEFT JOIN echeanciers e ON e.facture_id = f.id
  220. 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
  221. HAVING encours_facture > 0
  222. ORDER BY f.date_facture ASC
  223. ");
  224. $facturesAvecEncours = $stmt->fetchAll();
  225. $encoursTotal = 0;
  226. foreach ($encoursParClient as &$r) {
  227. $r['encours'] = (float) $r['encours'];
  228. $encoursTotal += $r['encours'];
  229. }
  230. $delaiMoyenJ = null;
  231. $stmtDelai = $pdo->query("
  232. SELECT AVG(DATEDIFF(p.date_paiement, f.date_facture)) AS delai_moyen
  233. FROM paiements p
  234. JOIN factures f ON f.id = p.facture_id
  235. ");
  236. $rowDelai = $stmtDelai->fetch();
  237. if ($rowDelai && $rowDelai['delai_moyen'] !== null) {
  238. $delaiMoyenJ = (int) round((float) $rowDelai['delai_moyen']);
  239. }
  240. // Calculer les échéances à partir des écheanciers
  241. $echeances = [];
  242. foreach ($facturesAvecEncours as $f) {
  243. $encoursF = (float) $f['encours_facture'];
  244. $factureId = (int) $f['facture_id'];
  245. // Si la facture a un écheancier, calculer les dates d'échéances réelles
  246. if ($f['echeancier_id'] !== null && $f['date_debut_echeancier'] !== null) {
  247. $dateDebut = new DateTime($f['date_debut_echeancier']);
  248. $nbEcheances = (int) $f['nb_echeances'];
  249. $montantParEch = $f['montant_par_echeance'] !== null ? (float) $f['montant_par_echeance'] : ($nbEcheances > 0 ? $encoursF / $nbEcheances : 0);
  250. // Créer une échéance pour chaque date prévue
  251. for ($i = 0; $i < $nbEcheances; $i++) {
  252. $dateEcheance = clone $dateDebut;
  253. $dateEcheance->modify("+{$i} months");
  254. $echeances[] = [
  255. 'facture_id' => $factureId,
  256. 'client_id' => (int) $f['client_id'],
  257. 'nom' => $f['nom'],
  258. 'n_facture' => $f['n_facture'],
  259. 'date_echeance' => $dateEcheance->format('Y-m-d'),
  260. 'montant_echeance' => $montantParEch,
  261. 'encours_facture' => $encoursF,
  262. 'echeance_num' => $i + 1,
  263. 'echeance_total' => $nbEcheances,
  264. ];
  265. }
  266. } else {
  267. // Pas d'écheancier : utiliser la date de facture
  268. $echeances[] = [
  269. 'facture_id' => $factureId,
  270. 'client_id' => (int) $f['client_id'],
  271. 'nom' => $f['nom'],
  272. 'n_facture' => $f['n_facture'],
  273. 'date_echeance' => $f['date_facture'],
  274. 'montant_echeance' => $encoursF,
  275. 'encours_facture' => $encoursF,
  276. 'echeance_num' => null,
  277. 'echeance_total' => null,
  278. ];
  279. }
  280. }
  281. usort($echeances, fn($a, $b) => strcmp($a['date_echeance'], $b['date_echeance']));
  282. // Filtrer pour ne garder que les échéances à venir (date >= aujourd'hui)
  283. $aujourdhui = date('Y-m-d');
  284. $echeances = array_filter($echeances, fn($e) => $e['date_echeance'] >= $aujourdhui);
  285. $echeances = array_values($echeances);
  286. if ($format === 'csv') {
  287. header('Content-Type: text/csv; charset=utf-8');
  288. header('Content-Disposition: attachment; filename=reporting-encours-' . date('Y-m-d') . '.csv');
  289. $out = fopen('php://output', 'w');
  290. fprintf($out, "\xEF\xBB\xBF"); // BOM UTF-8
  291. fputcsv($out, ['client_id', 'nom', 'encours'], ';');
  292. foreach ($encoursParClient as $r) {
  293. fputcsv($out, [$r['client_id'], $r['nom'], $r['encours']], ';');
  294. }
  295. fclose($out);
  296. exit;
  297. }
  298. if ($format === 'factures_csv') {
  299. $stmtF = $pdo->query("
  300. 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,
  301. COALESCE(SUM(p.montant), 0) AS total_paye
  302. FROM factures f
  303. JOIN clients c ON c.id = f.client_id
  304. LEFT JOIN paiements p ON p.facture_id = f.id
  305. 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
  306. ORDER BY f.date_facture DESC, c.nom
  307. ");
  308. $allFactures = $stmtF->fetchAll();
  309. header('Content-Type: text/csv; charset=utf-8');
  310. header('Content-Disposition: attachment; filename=suivi-factures-' . date('Y-m-d') . '.csv');
  311. $out = fopen('php://output', 'w');
  312. fprintf($out, "\xEF\xBB\xBF");
  313. // En-têtes alignés sur l’onglet Factures Excel (EMGI_FTI_Suivi des Ventes) + Total payé, Encours
  314. $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'];
  315. fputcsv($out, $headers, ';');
  316. foreach ($allFactures as $f) {
  317. $totalPaye = (float) $f['total_paye'];
  318. $enc = (float) $f['montant_ht'] - $totalPaye;
  319. $cycle = $f['cycle'] ?? '';
  320. $exo = $cycle ? str_replace('-', '/', $cycle) : '';
  321. $typeLabel = isset($f['type_produit']) ? (stripos($f['type_produit'], 'alevin') !== false ? 'Alevin Bar' : (stripos($f['type_produit'], 'larve') !== false ? 'Larve Bar' : $f['type_produit'])) : '';
  322. $dateFactur = !empty($f['date_facture']) ? (new DateTime($f['date_facture']))->format('d/m/Y') : '';
  323. $qteFact = $f['quantites'] ?? '';
  324. $qteLivree = $f['qte_livree'] ?? '';
  325. $caHt = $f['montant_ht'];
  326. $pu = '';
  327. if ($qteFact !== '' && $qteFact !== null && (float) $qteFact > 0) {
  328. $pu = number_format((float) $caHt / (float) $qteFact, 2, ',', ' ');
  329. }
  330. $caHt = number_format((float) $caHt, 2, ',', ' ');
  331. if ($qteFact !== '' && $qteFact !== null) $qteFact = number_format((float) $qteFact, 0, ',', ' ');
  332. if ($qteLivree !== '' && $qteLivree !== null) $qteLivree = number_format((float) $qteLivree, 0, ',', ' ');
  333. $totalPayeStr = number_format($totalPaye, 2, ',', ' ');
  334. $encStr = number_format($enc, 2, ',', ' ');
  335. fputcsv($out, [
  336. $exo,
  337. '',
  338. $f['nom'],
  339. '',
  340. 'EMGI',
  341. $typeLabel,
  342. $typeLabel,
  343. $cycle ?? '',
  344. '',
  345. '',
  346. $dateFactur,
  347. $f['n_vente'] ?? '',
  348. $f['n_facture'],
  349. $qteFact,
  350. $qteLivree,
  351. '',
  352. $caHt,
  353. $pu,
  354. $totalPayeStr,
  355. $encStr,
  356. ], ';');
  357. }
  358. fclose($out);
  359. exit;
  360. }
  361. sendJson([
  362. 'date_rapport' => date('Y-m-d'),
  363. 'encours_total' => $encoursTotal,
  364. 'encours_par_client' => $encoursParClient,
  365. 'delai_moyen_paiement_j' => $delaiMoyenJ,
  366. 'echeances_a_venir' => $echeances,
  367. ]);