dolibarr 25.0.0-alpha
reports.class.php
1<?php
2/* Copyright (C) 2026 Laurent Destailleur <eldy@users.sourceforge.net>
3 * Copyright (C) 2026 Nick Fragoulis
4 * Copyright (C) 2026 Jose Martinez <jose.martinez@pichinov.com>
5 * Copyright (C) 2026 MDW <mdeweerd@users.noreply.github.com>
6 *
7 * This program is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program. If not, see <https://www.gnu.org/licenses/>.
19 */
20
31require_once DOL_DOCUMENT_ROOT . '/societe/class/societe.class.php';
32require_once DOL_DOCUMENT_ROOT . '/core/lib/date.lib.php';
33
39class ToolReports extends McpTool
40{
50 public function __construct(DoliDB $db, $user = null, $conf = null)
51 {
52 $this->db = $db;
53 $this->user = $user;
54 if ($conf !== null) {
55 $this->conf = $conf;
56 }
57 }
58
64 public function getDefinitions(): array
65 {
66 $listofreporttools = [];
67
68 /*
69 $listofreporttools[] =
70 [
71 "name" => "get_thirdparty_transactions",
72 "description" => "Generate a list of raw transactions (Invoices, Orders) for a specific thirdparty.",
73 "inputSchema" => [
74 "type" => "object",
75 "properties" => [
76 "thirdparty_id" => ["type" => "integer", "description" => "The unique ID of the thirdparty."],
77 "thirdparty_name" => ["type" => "string", "description" => "The name of the thirdparty."],
78 "date_start" => ["type" => "string", "description" => "Start date (YYYY-MM-DD). Compute it from the current date when the user says a relative period like this month."],
79 "date_end" => ["type" => "string", "description" => "End date (YYYY-MM-DD). Compute it from the current date when the user says a relative period."],
80 "transaction_type" => [
81 "type" => "string",
82 "enum" => ["all", "invoices", "orders", "proposals"],
83 "description" => "Filter by type.",
84 "default" => "all"
85 ]
86 ],
87 "oneOf" => [
88 ["required" => ["thirdparty_id"]],
89 ["required" => ["thirdparty_name"]]
90 ]
91 ]
92 ];
93 */
94
95 $listofreporttools[] = [
96 "name" => "get_sales_report",
97 "description" => "Generate a sales/revenue report. If a Thirdparty is provided, it returns a detailed breakdown for that customer. Otherwise, it returns a global summary.",
98 "inputSchema" => [
99 "type" => "object",
100 "properties" => [
101 "thirdparty_id" => [
102 "type" => "integer",
103 "description" => "Optional: The ID of the customer."
104 ],
105 "date_start" => ["type" => "string", "description" => "Start date (YYYY-MM-DD). Compute it from the current date when the user says a relative period like this month."],
106 "date_end" => ["type" => "string", "description" => "End date (YYYY-MM-DD). Compute it from the current date when the user says a relative period."],
107 "group_by" => [
108 "type" => "string",
109 "enum" => ["thirdparty", "product", "month"],
110 "description" => "Only used if no Thirdparty is specified. Groups global results.",
111 "default" => "thirdparty"
112 ]
113 ],
114 "required" => ["date_start", "date_end"]
115 ]
116 ];
117
118 $listofreporttools[] = [
119 "name" => "get_purchase_report",
120 "description" => "Generate a purchase/expense report. If a Supplier is provided, it returns a detailed breakdown. Otherwise, it returns a global summary.",
121 "inputSchema" => [
122 "type" => "object",
123 "properties" => [
124 "thirdparty_id" => [
125 "type" => "integer",
126 "description" => "Optional: The ID of the supplier."
127 ],
128 "date_start" => ["type" => "string", "description" => "Start date (YYYY-MM-DD). Compute it from the current date when the user says a relative period like this month."],
129 "date_end" => ["type" => "string", "description" => "End date (YYYY-MM-DD). Compute it from the current date when the user says a relative period."],
130 "group_by" => [
131 "type" => "string",
132 "enum" => ["supplier", "product", "month"],
133 "description" => "Only used if no Supplier is specified. Groups global results.",
134 "default" => "supplier"
135 ]
136 ],
137 "required" => ["date_start", "date_end"]
138 ]
139 ];
140
141 $listofreporttools[] = [
142 "name" => "get_inventory_report",
143 "description" => "Generate an inventory report showing current stock levels and valuation.",
144 "inputSchema" => [
145 "type" => "object",
146 "properties" => [
147 "category_id" => ["type" => "integer", "description" => "Filter by category ID."],
148 "warehouse_id" => ["type" => "integer", "description" => "Filter by warehouse ID."],
149 "include_zero_stock" => ["type" => "boolean", "default" => false]
150 ]
151 ]
152 ];
153
154 $listofreporttools[] = [
155 "name" => "get_financial_report",
156 "description" => "Generate a summary financial report (Income vs Expense) for a period.",
157 "inputSchema" => [
158 "type" => "object",
159 "properties" => [
160 "date_start" => ["type" => "string", "description" => "Start date (YYYY-MM-DD). Compute it from the current date when the user says a relative period like this month."],
161 "date_end" => ["type" => "string", "description" => "End date (YYYY-MM-DD). Compute it from the current date when the user says a relative period."]
162 ],
163 "required" => ["date_start", "date_end"]
164 ]
165 ];
166
167 return $listofreporttools;
168 }
169
176 public function getRequiredRights(string $toolName)
177 {
178 $map = array(
179 'get_sales_report' => array(array('facture', 'lire')),
180 'get_purchase_report' => array(array('fournisseur', 'facture', 'lire')),
181 'get_inventory_report' => array(array('produit', 'lire'), array('stock', 'lire')),
182 'get_financial_report' => array(array('facture', 'lire'), array('fournisseur', 'facture', 'lire')),
183 'get_thirdparty_transactions' => array(array('societe', 'lire'), array('facture', 'lire'))
184 );
185
186 return isset($map[$toolName]) ? $map[$toolName] : self::RIGHTS_UNDECLARED;
187 }
188
195 public function getCategories(): array
196 {
197 return ['reporting', 'commercial', 'billing', 'stock'];
198 }
199
207 public function execute(string $name, array $args)
208 {
209 switch ($name) {
210 /*case 'get_thirdparty_transactions':
211 return $this->getThirdpartyTransactions($args); */
212 case 'get_sales_report':
213 return $this->getSalesReport($args);
214 case 'get_purchase_report':
215 return $this->getPurchaseReport($args);
216 case 'get_inventory_report':
217 return $this->getInventoryReport($args);
218 case 'get_financial_report':
219 return $this->getFinancialReport($args);
220 default:
221 return ["error" => "Tool function '$name' not found."];
222 }
223 }
224
236 private function resolveThirdparty($args)
237 {
238
239 if (!empty($args['thirdparty_id'])) {
240 return (int) $args['thirdparty_id'];
241 }
242
243 if (!empty($args['thirdparty_name'])) {
244 $sqlName = $this->db->escape($args['thirdparty_name']);
245
246 $sql = "SELECT rowid FROM " . MAIN_DB_PREFIX . "societe
247 WHERE nom LIKE '%" . $sqlName . "%'
248 AND entity IN (" . getEntity('societe') . ")
249 LIMIT 1";
250
251 $resql = $this->db->query($sql);
252 if ($resql && $obj = $this->db->fetch_object($resql)) {
253 return $obj->rowid;
254 }
255 }
256
257 return null;
258 }
259
266 private function getSalesReport(array $args): array
267 {
268 global $langs;
269
270 $langs->loadLangs(array("main", "bills", "companies", "products"));
271
272 $limit = isset($args['limit']) ? (int) $args['limit'] : 50;
273 $dateStart = dol_stringtotime($args['date_start']);
274 $dateEnd = dol_stringtotime($args['date_end']);
275 $socid = $this->resolveThirdparty($args);
276 $groupBy = isset($args['group_by']) ? (string) $args['group_by'] : 'thirdparty';
277
278 $list = [];
279 $totalSum = 0.0;
280 $chartLabels = [];
281 $chartData = [];
282 // Status Filter: Valid (1) and Paid (2). Exclude Draft (0) and Abandoned (3).
283 $dateRange = " AND f.datef >= '" . $this->db->idate($dateStart)
284 . "' AND f.datef <= '" . $this->db->idate($dateEnd)
285 . "' AND f.fk_statut IN (1, 2)";
286
287 // CASE 1 -- Detailed list for a specific thirdparty.
288 if ($socid) {
289 $sql = "SELECT f.rowid, f.ref, f.total_ttc, f.fk_statut, f.paye, f.datef, s.nom FROM "
290 . MAIN_DB_PREFIX . "facture as f LEFT JOIN "
291 . MAIN_DB_PREFIX . "societe as s ON f.fk_soc = s.rowid WHERE f.entity IN ("
292 . getEntity('facture') . ")"
293 . $dateRange
294 . " AND f.fk_soc = " . (int) $socid
295 . " ORDER BY f.datef DESC LIMIT " . ((int) $limit);
296
297 $resql = $this->db->query($sql);
298 if ($resql) {
299 while ($r = $this->db->fetch_object($resql)) {
300 $totalSum += (float) $r->total_ttc;
301
302 $statusLabel = $langs->transnoentitiesnoconv("Unknown");
303 if ($r->fk_statut == 1 && $r->paye == 0) {
304 $statusLabel = $langs->transnoentitiesnoconv("BillStatusNotPaid");
305 } elseif ($r->fk_statut == 1 && $r->paye == 1) {
306 $statusLabel = $langs->transnoentitiesnoconv("BillStatusStarted");
307 } elseif ($r->fk_statut == 2) {
308 $statusLabel = $langs->transnoentitiesnoconv("BillStatusPaid");
309 }
310
311 $url = DOL_URL_ROOT . "/compta/facture/card.php?id=" . $r->rowid;
312 $refHtml = '<a href="' . $url . '">' . $r->ref . '</a>';
313
314 $list[] = [
315 $langs->transnoentitiesnoconv("Ref") => $refHtml,
316 $langs->transnoentitiesnoconv("Date") => dol_print_date($this->db->jdate($r->datef), 'day'),
317 $langs->transnoentitiesnoconv("Customer") => $r->nom,
318 $langs->transnoentitiesnoconv("Amount") => price($r->total_ttc),
319 $langs->transnoentitiesnoconv("Status") => $statusLabel
320 ];
321 }
322 $this->db->free($resql);
323 }
324 } else {
325 // CASE 2 -- Global grouped report.
326 // Mirrors the pattern already used by getPurchaseReport(); previous implementation
327 // of getSalesReport() ignored $groupBy entirely and always returned a flat list.
328 $sanitizedSqlGroup = '';
329 $colName = '';
330 $sqlJoin = " LEFT JOIN " . MAIN_DB_PREFIX . "societe as s ON f.fk_soc = s.rowid";
331
332 if ($groupBy === 'month') {
333 $sanitizedSqlGroup = "DATE_FORMAT(f.datef, '%Y-%m')";
334 $colName = $langs->transnoentitiesnoconv("Month");
335 } elseif ($groupBy === 'product') {
336 // Aggregate on product line items. Lines without product_id fall back to their description.
337 $sanitizedSqlGroup = "COALESCE(p.ref, fd.description, '?')";
338 $colName = $langs->transnoentitiesnoconv("Product");
339 $sqlJoin .= " INNER JOIN " . MAIN_DB_PREFIX . "facturedet as fd ON fd.fk_facture = f.rowid LEFT JOIN "
340 . MAIN_DB_PREFIX . "product as p ON fd.fk_product = p.rowid";
341 } else {
342 // Default: group by customer
343 $sanitizedSqlGroup = "s.nom";
344 $colName = $langs->transnoentitiesnoconv("Customer");
345 }
346
347 // For product grouping we sum line totals (more accurate per-product);
348 // otherwise we sum the invoice total_ttc.
349 $amountExpr = ($groupBy === 'product') ? "SUM(fd.total_ttc)" : "SUM(f.total_ttc)";
350 $countExpr = ($groupBy === 'product') ? "COUNT(DISTINCT f.rowid)" : "COUNT(f.rowid)";
351
352 $sql = "SELECT " . $sanitizedSqlGroup . " as group_key, "
353 . $amountExpr . " as total_amount, "
354 . $countExpr . " as count_inv FROM "
355 . MAIN_DB_PREFIX . "facture as f"
356 . $sqlJoin
357 . " WHERE f.entity IN (" . getEntity('facture') . ")"
358 . $dateRange
359 . " GROUP BY group_key ORDER BY total_amount DESC LIMIT "
360 . ((int) max(1, $limit));
361
362 $resql = $this->db->query($sql);
363 if ($resql) {
364 while ($r = $this->db->fetch_object($resql)) {
365 $totalSum += (float) $r->total_amount;
366 $chartLabels[] = $r->group_key ? $r->group_key : $langs->transnoentitiesnoconv('Unknown');
367 $chartData[] = (float) $r->total_amount;
368 $list[] = [
369 $colName => $r->group_key ? $r->group_key : $langs->transnoentitiesnoconv('Unknown'),
370 $langs->transnoentitiesnoconv("Number") => (int) $r->count_inv,
371 $langs->transnoentitiesnoconv("Amount") => price($r->total_amount)
372 ];
373 }
374 $this->db->free($resql);
375 }
376 }
377
378 if (empty($list)) {
379 return [[$langs->transnoentitiesnoconv("Info") => $langs->transnoentitiesnoconv("NoRecordFound")]];
380 }
381
382 // Append Total Row (shape depends on detailed-vs-grouped path)
383 if ($socid) {
384 $list[] = [
385 $langs->transnoentitiesnoconv("Ref") => $langs->transnoentitiesnoconv("Total"),
386 $langs->transnoentitiesnoconv("Date") => "",
387 $langs->transnoentitiesnoconv("Customer") => "",
388 $langs->transnoentitiesnoconv("Amount") => price($totalSum),
389 $langs->transnoentitiesnoconv("Status") => ""
390 ];
391 } else {
392 $list[] = [
393 $langs->transnoentitiesnoconv("Total") => $langs->transnoentitiesnoconv("Total"),
394 $langs->transnoentitiesnoconv("Amount") => price($totalSum)
395 ];
396 }
397
398 // Grouped results carry a chart payload the chat renders as a bar/line
399 // chart; the rows stay in 'rows' so the model and the table are unchanged.
400 if (!empty($chartData)) {
401 return array(
402 'rows' => $list,
403 'chart' => array(
404 'type' => ($groupBy === 'month' ? 'line' : 'bar'),
405 'labels' => $chartLabels,
406 'datasets' => array(array('label' => $langs->transnoentitiesnoconv('Amount'), 'data' => $chartData)),
407 ),
408 );
409 }
410
411 return $list;
412 }
413
420 /*
421 private function getThirdpartyTransactions(array $args): array
422 {
423 global $langs;
424
425 $langs->loadLangs(array("main", "bills", "orders", "propal"));
426
427 $dateStart = dol_stringtotime($args['date_start']);
428 $dateEnd = dol_stringtotime($args['date_end']);
429 $type = isset($args['transaction_type']) ? (string) $args['transaction_type'] : 'all';
430
431 $socid = $this->resolveThirdparty($args);
432 if (!$socid) {
433 $langs->load("errors");
434 return [[$langs->transnoentitiesnoconv("Error") => $langs->transnoentitiesnoconv("ErrorThirdPartyNotFound")]];
435 }
436
437 $sqlQueries = [];
438
439 // Invoices
440 if ($type == 'all' || $type == 'invoices') {
441 $sqlQueries[] = "SELECT 'Invoice' as source_type, rowid, ref, total_ttc as amount, datef as date_entry, fk_statut
442 FROM " . MAIN_DB_PREFIX . "facture
443 WHERE fk_soc = " . (int) $socid . " AND entity IN (" . getEntity('facture') . ")
444 AND fk_statut IN (1, 2)";
445 }
446
447 // Orders
448 if ($type == 'all' || $type == 'orders') {
449 $sqlQueries[] = "SELECT 'Order' as source_type, rowid, ref, total_ttc as amount, date_commande as date_entry, fk_statut
450 FROM " . MAIN_DB_PREFIX . "commande
451 WHERE fk_soc = " . (int) $socid . " AND entity IN (" . getEntity('commande') . ")
452 AND fk_statut > 0";
453 }
454
455 // Proposals
456 if ($type == 'all' || $type == 'proposals') {
457 $sqlQueries[] = "SELECT 'Proposal' as source_type, rowid, ref, total_ttc as amount, datep as date_entry, fk_statut
458 FROM " . MAIN_DB_PREFIX . "propal
459 WHERE fk_soc = " . (int) $socid . " AND entity IN (" . getEntity('propal') . ")
460 AND fk_statut > 0";
461 }
462
463 if (empty($sqlQueries)) {
464 return [[$langs->transnoentitiesnoconv("Error") => "Invalid transaction type"]];
465 }
466
467 $sql = "SELECT * FROM (";
468 $sql .= implode(" UNION ", $sqlQueries);
469 $sql .= ") as combined_transactions ";
470 $whereParts = [];
471 if ($dateStart > 0) {
472 $whereParts[] = "date_entry >= '" . $this->db->idate($dateStart) . "'";
473 }
474 if ($dateEnd > 0) {
475 $whereParts[] = "date_entry <= '" . $this->db->idate($dateEnd) . "'";
476 }
477
478 if (!empty($whereParts)) {
479 $sql .= " WHERE " . implode(" AND ", $whereParts);
480 }
481
482 $sql .= " ORDER BY date_entry DESC";
483
484 $resql = $this->db->query($sql);
485 $list = [];
486 $totalAmt = 0.0;
487
488 if ($resql) {
489 while ($r = $this->db->fetch_object($resql)) {
490 $totalAmt += (float) $r->amount;
491
492 $statusTxt = "";
493 $urlPath = "";
494
495 if ($r->source_type === 'Invoice') {
496 $urlPath = "/compta/facture/card.php?id=" . $r->rowid;
497 if ($r->fk_statut == 2) {
498 $statusTxt = $langs->transnoentitiesnoconv("BillStatusPaid");
499 } elseif ($r->fk_statut == 1) {
500 $statusTxt = $langs->transnoentitiesnoconv("BillStatusNotPaid");
501 }
502 } elseif ($r->source_type === 'Order') {
503 $urlPath = "/commande/card.php?id=" . $r->rowid;
504 if ($r->fk_statut == 1) {
505 $statusTxt = $langs->transnoentitiesnoconv("StatusOrderValidated");
506 } elseif ($r->fk_statut == 2) {
507 $statusTxt = $langs->transnoentitiesnoconv("StatusOrderOnProcess");
508 } elseif ($r->fk_statut == 3) {
509 $statusTxt = $langs->transnoentitiesnoconv("StatusOrderDelivered");
510 }
511 } elseif ($r->source_type === 'Proposal') {
512 $urlPath = "/comm/propal/card.php?id=" . $r->rowid;
513 if ($r->fk_statut == 1) {
514 $statusTxt = $langs->transnoentitiesnoconv("PropalStatusValidated");
515 } elseif ($r->fk_statut == 2) {
516 $statusTxt = $langs->transnoentitiesnoconv("PropalStatusSigned");
517 } elseif ($r->fk_statut == 3) {
518 $statusTxt = $langs->transnoentitiesnoconv("PropalStatusNotSigned");
519 } elseif ($r->fk_statut == 4) {
520 $statusTxt = $langs->transnoentitiesnoconv("PropalStatusBilled");
521 }
522 }
523
524 $fullUrl = $urlPath ? DOL_URL_ROOT . $urlPath : "";
525 $refHtml = $fullUrl ? '<a href="' . $fullUrl . '">' . $r->ref . '</a>' : $r->ref;
526
527 $list[] = [
528 $langs->transnoentitiesnoconv("Type") => $langs->transnoentitiesnoconv($r->source_type),
529 $langs->transnoentitiesnoconv("Ref") => $refHtml,
530 $langs->transnoentitiesnoconv("Date") => dol_print_date($this->db->jdate($r->date_entry), 'day'),
531 $langs->transnoentitiesnoconv("Amount") => price($r->amount),
532 $langs->transnoentitiesnoconv("Status") => $statusTxt
533 ];
534 }
535 $this->db->free($resql);
536 }
537
538 if (empty($list)) {
539 return [[$langs->transnoentitiesnoconv("Info") => $langs->transnoentitiesnoconv("NoRecordFound")]];
540 }
541
542 // Summary
543 $list[] = [
544 $langs->transnoentitiesnoconv("Type") => $langs->transnoentitiesnoconv("Total"),
545 $langs->transnoentitiesnoconv("Ref") => "",
546 $langs->transnoentitiesnoconv("Date") => "",
547 $langs->transnoentitiesnoconv("Amount") => price($totalAmt),
548 $langs->transnoentitiesnoconv("Status") => ""
549 ];
550
551 return $list;
552 }
553 */
554
561 private function getPurchaseReport(array $args): array
562 {
563 global $langs;
564
565 $langs->loadLangs(array("main", "bills", "companies"));
566
567 $socid = $this->resolveThirdparty($args);
568 $groupBy = isset($args['group_by']) ? (string) $args['group_by'] : 'supplier';
569 $dateStart = dol_stringtotime($args['date_start']);
570 $dateEnd = dol_stringtotime($args['date_end']);
571 $list = [];
572 $totalSum = 0.0;
573 $chartLabels = [];
574 $chartData = [];
575
576 // Detailed report for a specific Supplier
577 if ($socid) {
578 $sql = "SELECT f.rowid, f.ref, f.total_ttc, f.datef, s.nom
579 FROM " . MAIN_DB_PREFIX . "facture_fourn as f
580 LEFT JOIN " . MAIN_DB_PREFIX . "societe as s ON f.fk_soc = s.rowid
581 WHERE f.entity IN (" . getEntity('facture_fourn') . ")
582 AND f.fk_soc = " . (int) $socid . "
583 AND f.datef >= '" . $this->db->idate($dateStart) . "'
584 AND f.datef <= '" . $this->db->idate($dateEnd) . "'
585 AND f.fk_statut > 0
586 ORDER BY f.datef DESC";
587
588 $resql = $this->db->query($sql);
589 if ($resql) {
590 while ($r = $this->db->fetch_object($resql)) {
591 $totalSum += (float) $r->total_ttc;
592
593 $url = DOL_URL_ROOT . "/fourn/facture/card.php?id=" . $r->rowid;
594 $refHtml = '<a href="' . $url . '">' . $r->ref . '</a>';
595
596 $list[] = [
597 $langs->transnoentitiesnoconv("Ref") => $refHtml,
598 $langs->transnoentitiesnoconv("Date") => dol_print_date($this->db->jdate($r->datef), 'day'),
599 $langs->transnoentitiesnoconv("Supplier") => $r->nom,
600 $langs->transnoentitiesnoconv("Amount") => price($r->total_ttc)
601 ];
602 }
603 $this->db->free($resql);
604 }
605 } else { // Global Grouped Report
606 $sanitizedSqlGroup = "";
607 $colName = "";
608
609 if ($groupBy === 'month') {
610 $sanitizedSqlGroup = "DATE_FORMAT(f.datef, '%Y-%m')";
611 $colName = $langs->transnoentitiesnoconv("Month");
612 } else {
613 $sanitizedSqlGroup = "s.nom";
614 $colName = $langs->transnoentitiesnoconv("Supplier");
615 }
616
617 $sql = "SELECT " . $sanitizedSqlGroup . " as group_key, SUM(f.total_ttc) as total_amount, COUNT(f.rowid) as count_inv
618 FROM " . MAIN_DB_PREFIX . "facture_fourn as f
619 LEFT JOIN " . MAIN_DB_PREFIX . "societe as s ON f.fk_soc = s.rowid
620 WHERE f.entity IN (" . getEntity('facture_fourn') . ")
621 AND f.datef >= '" . $this->db->idate($dateStart) . "'
622 AND f.datef <= '" . $this->db->idate($dateEnd) . "'
623 AND f.fk_statut > 0
624 GROUP BY group_key
625 ORDER BY total_amount DESC";
626
627 $resql = $this->db->query($sql);
628 if ($resql) {
629 while ($r = $this->db->fetch_object($resql)) {
630 $totalSum += (float) $r->total_amount;
631 $chartLabels[] = $r->group_key ? $r->group_key : $langs->transnoentitiesnoconv('Unknown');
632 $chartData[] = (float) $r->total_amount;
633 $list[] = [
634 $colName => $r->group_key ? $r->group_key : $langs->transnoentitiesnoconv('Unknown'),
635 $langs->transnoentitiesnoconv("Number") => $r->count_inv,
636 $langs->transnoentitiesnoconv("Amount") => price($r->total_amount)
637 ];
638 }
639 $this->db->free($resql);
640 }
641 }
642
643 if (empty($list)) {
644 return [[$langs->transnoentitiesnoconv("Info") => $langs->transnoentitiesnoconv("NoRecordFound")]];
645 }
646
647 // Summary Row
648 $summary = [
649 $langs->transnoentitiesnoconv("Amount") => price($totalSum)
650 ];
651
652 if ($socid) {
653 $summary[$langs->transnoentitiesnoconv("Ref")] = $langs->transnoentitiesnoconv("Total");
654 $summary[$langs->transnoentitiesnoconv("Date")] = "";
655 $summary[$langs->transnoentitiesnoconv("Supplier")] = "";
656 } else {
657 $summary[($groupBy === 'month' ? $langs->transnoentitiesnoconv("Month") : $langs->transnoentitiesnoconv("Supplier"))] = $langs->transnoentitiesnoconv("Total");
658 $summary[$langs->transnoentitiesnoconv("Number")] = "";
659 }
660 $list[] = $summary;
661
662 // Same contract as getSalesReport: grouped results carry a chart payload.
663 if (!empty($chartData)) {
664 return array(
665 'rows' => $list,
666 'chart' => array(
667 'type' => ($groupBy === 'month' ? 'line' : 'bar'),
668 'labels' => $chartLabels,
669 'datasets' => array(array('label' => $langs->transnoentitiesnoconv('Amount'), 'data' => $chartData)),
670 ),
671 );
672 }
673
674 return $list;
675 }
676
683 private function getInventoryReport(array $args): array
684 {
685 global $langs;
686
687 $langs->loadLangs(array("products", "stocks"));
688
689 $catId = isset($args['category_id']) ? (int) $args['category_id'] : 0;
690 $warehouseId = isset($args['warehouse_id']) ? (int) $args['warehouse_id'] : 0;
691 $includeZero = isset($args['include_zero_stock']) ? (bool) $args['include_zero_stock'] : false;
692
693 $sql = "SELECT p.rowid, p.ref, p.label, p.pmp, ";
694
695 if ($warehouseId > 0) {
696 $sql .= " ps.reel as stock_level ";
697 $sql .= " FROM " . MAIN_DB_PREFIX . "product as p ";
698 $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product_stock as ps ON p.rowid = ps.fk_product ";
699 $sql .= " WHERE ps.fk_entrepot = " . (int) $warehouseId;
700 } else {
701 $sql .= " p.stock as stock_level ";
702 $sql .= " FROM " . MAIN_DB_PREFIX . "product as p ";
703 $sql .= " WHERE 1=1 ";
704 }
705
706 $sql .= " AND p.entity IN (" . getEntity('product') . ")";
707
708 if ($catId > 0) {
709 $sql .= " AND p.rowid IN (SELECT fk_product FROM " . MAIN_DB_PREFIX . "categorie_product WHERE fk_categorie = " . (int) $catId . ")";
710 }
711
712 if (!$includeZero) {
713 if ($warehouseId > 0) {
714 $sql .= " AND ps.reel > 0";
715 } else {
716 $sql .= " AND p.stock > 0";
717 }
718 }
719
720 $sql .= " ORDER BY p.ref ASC LIMIT 200";
721
722 $resql = $this->db->query($sql);
723 $list = [];
724 $totalValuation = 0.0;
725 $totalItems = 0;
726
727 if ($resql) {
728 while ($r = $this->db->fetch_object($resql)) {
729 $stockVal = $r->stock_level * $r->pmp;
730 $totalValuation += $stockVal;
731 $totalItems += (int) $r->stock_level;
732
733 $url = DOL_URL_ROOT . "/product/card.php?id=" . $r->rowid;
734 $refHtml = '<a href="' . $url . '">' . $r->ref . '</a>';
735
736 $list[] = [
737 $langs->transnoentitiesnoconv("Ref") => $refHtml,
738 $langs->transnoentitiesnoconv("Label") => $r->label,
739 $langs->transnoentitiesnoconv("Stock") => $r->stock_level,
740 $langs->transnoentitiesnoconv("PMPValue") => price($r->pmp),
741 $langs->transnoentitiesnoconv("TotalValue") => price($stockVal)
742 ];
743 }
744 $this->db->free($resql);
745 }
746
747 if (empty($list)) {
748 return [[$langs->transnoentitiesnoconv("Info") => $langs->transnoentitiesnoconv("NoRecordFound")]];
749 }
750
751 $list[] = [
752 $langs->transnoentitiesnoconv("Ref") => $langs->transnoentitiesnoconv("Total"),
753 $langs->transnoentitiesnoconv("Label") => "",
754 $langs->transnoentitiesnoconv("Stock") => $totalItems,
755 $langs->transnoentitiesnoconv("PMPValue") => "",
756 $langs->transnoentitiesnoconv("TotalValue") => price($totalValuation)
757 ];
758
759 return $list;
760 }
761
768 private function getFinancialReport(array $args): array
769 {
770 global $langs;
771
772 $langs->loadLangs(array("compta", "bills"));
773 $dateStart = dol_stringtotime($args['date_start']);
774 $dateEnd = dol_stringtotime($args['date_end']);
775
776 // Income (Customer Invoices - Validated/Paid, no Drafts)
777 $sqlIncome = "SELECT SUM(total_ttc) as total FROM " . MAIN_DB_PREFIX . "facture
778 WHERE entity IN (" . getEntity('facture') . ")
779 AND datef >= '" . $this->db->idate($dateStart) . "'
780 AND datef <= '" . $this->db->idate($dateEnd) . "'
781 AND fk_statut IN (1, 2)";
782
783 $resIncome = $this->db->query($sqlIncome);
784 $objIncome = $this->db->fetch_object($resIncome);
785 $income = $objIncome && $objIncome->total ? (float) $objIncome->total : 0.0;
786
787 // Expenses (Supplier Invoices - Validated, no Drafts)
788 $sqlExpense = "SELECT SUM(total_ttc) as total FROM " . MAIN_DB_PREFIX . "facture_fourn
789 WHERE entity IN (" . getEntity('facture_fourn') . ")
790 AND datef >= '" . $this->db->idate($dateStart) . "'
791 AND datef <= '" . $this->db->idate($dateEnd) . "'
792 AND fk_statut > 0";
793
794 $resExpense = $this->db->query($sqlExpense);
795 $objExpense = $this->db->fetch_object($resExpense);
796 $expense = $objExpense && $objExpense->total ? (float) $objExpense->total : 0.0;
797
798 $net = $income - $expense;
799
800 $list = [
801 [
802 $langs->transnoentitiesnoconv("Category") => $langs->transnoentitiesnoconv("Income"),
803 $langs->transnoentitiesnoconv("Description") => $langs->transnoentitiesnoconv("BillsCustomers"),
804 $langs->transnoentitiesnoconv("Amount") => price($income)
805 ],
806 [
807 $langs->transnoentitiesnoconv("Category") => $langs->transnoentitiesnoconv("Expenses"),
808 $langs->transnoentitiesnoconv("Description") => $langs->transnoentitiesnoconv("BillsSuppliers"),
809 $langs->transnoentitiesnoconv("Amount") => price($expense)
810 ],
811 [
812 $langs->transnoentitiesnoconv("Category") => $langs->transnoentitiesnoconv("Total"),
813 $langs->transnoentitiesnoconv("Description") => $langs->transnoentitiesnoconv("Profit"),
814 $langs->transnoentitiesnoconv("Amount") => price($net)
815 ]
816 ];
817
818 return $list;
819 }
820}
Class to manage Dolibarr database access.
Abstract base class for all MCP (Model Context Protocol) tools.
const RIGHTS_UNDECLARED
Default of getRequiredRights(): the class never declared anything, deny.
Class ToolReports.
getCategories()
Return categories this tool belongs to.
execute(string $name, array $args)
Executes the requested tool function based on its name.
getDefinitions()
Returns an array of tool definitions.
getPurchaseReport(array $args)
Generate a list of raw transactions (Invoices, Orders, Proposals).
getFinancialReport(array $args)
Generate a summary financial report (Income vs Expense).
getSalesReport(array $args)
Generate a sales/revenue report.
resolveThirdparty($args)
Resolves a Thirdparty ID from either an ID or a name.
getRequiredRights(string $toolName)
Aggregated reports: the widest data reach of any tool class.
__construct(DoliDB $db, $user=null, $conf=null)
Constructor.
getInventoryReport(array $args)
Generate an inventory report.
dol_stringtotime($string, $gm=1, $processnotimeasnoon=0)
Convert a string date into a GM Timestamps date Warning: YYYY-MM-DDTHH:MM:SS+02:00 (RFC3339) is not s...
Definition date.lib.php:452
price($amount, $form=0, $outlangs='', $trunc=1, $rounding=-1, $forcerounding=-1, $currency_code='')
Function to format a value into an amount for visual output Function used into PDF and HTML pages.
dol_print_date($time, $format='', $tzoutput='auto', $outputlangs=null, $encodetooutput=false, $decorate=0)
Output date in a string format according to outputlangs (or langs if not defined).
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
conf($dolibarr_main_document_root, $realpathconf=null)
Load conf file (file must exists)
Definition inc.php:431
$conf db user
Active Directory does not allow anonymous connections.
Definition repair.php:166