dolibarr/htdocs/ai/tools/reports.class.php

<?php
/* Copyright (C) 2026	Laurent Destailleur		<eldy@users.sourceforge.net>
 * Copyright (C) 2026	Nick Fragoulis
 * Copyright (C) 2026	Jose Martinez			<jose.martinez@pichinov.com>
 * Copyright (C) 2026		MDW						<mdeweerd@users.noreply.github.com>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
 */

/**
 * \file htdocs/ai/tools/reports.php
 * \ingroup ai
 * \brief MCP Server tool for reports.
 *
 * This tool provides functionalities to generate various reports related to
 * thirdparty transactions, sales, purchases, inventory, and other essential
 * ERP/CRM reports.
 */

require_once DOL_DOCUMENT_ROOT . '/societe/class/societe.class.php';
require_once DOL_DOCUMENT_ROOT . '/core/lib/date.lib.php';

/**
 * Class ToolReports
 *
 * Provides various tools related to Dolibarr reports.
 */
class ToolReports extends McpTool
{
	/**
	 * 	Constructor
	 *
	 * 	Aligned with McpHandler's instantiation contract: new $className($db, $user, $conf).
	 *
	 * 	@param	DoliDB		$db			Database handler
	 * 	@param	User|null	$user		Service user provided by McpHandler (from AI_MCP_USER_ID)
	 * 	@param	Conf|null	$conf		Dolibarr config (optional)
	 */
	public function __construct(DoliDB $db, $user = null, $conf = null)
	{
		$this->db = $db;
		$this->user = $user;
		if ($conf !== null) {
			$this->conf = $conf;
		}
	}

	/**
	 * Returns an array of tool definitions.
	 *
	 * @return list<array<string, mixed>> Array of tool definitions.
	 */
	public function getDefinitions(): array
	{
		return [
			[
				"name" => "get_thirdparty_transactions",
				"description" => "Generate a list of raw transactions (Invoices, Orders) for a specific thirdparty.",
				"inputSchema" => [
					"type" => "object",
					"properties" => [
						"thirdparty_id" => ["type" => "integer", "description" => "The unique ID of the thirdparty."],
						"thirdparty_name" => ["type" => "string", "description" => "The name of the thirdparty."],
						"date_start" => ["type" => "string", "description" => "Start date (YYYY-MM-DD)."],
						"date_end" => ["type" => "string", "description" => "End date (YYYY-MM-DD)."],
						"transaction_type" => [
							"type" => "string",
							"enum" => ["all", "invoices", "orders", "proposals"],
							"description" => "Filter by type.",
							"default" => "all"
						]
					],
					"oneOf" => [
						["required" => ["thirdparty_id"]],
						["required" => ["thirdparty_name"]]
					]
				]
			],
			[
				"name" => "get_sales_report",
				"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.",
				"inputSchema" => [
					"type" => "object",
					"properties" => [
						"thirdparty_id" => [
							"type" => "integer",
							"description" => "Optional: The ID of the customer."
						],
						"date_start" => ["type" => "string", "description" => "Start date (YYYY-MM-DD)."],
						"date_end" => ["type" => "string", "description" => "End date (YYYY-MM-DD)."],
						"group_by" => [
							"type" => "string",
							"enum" => ["thirdparty", "product", "month"],
							"description" => "Only used if no Thirdparty is specified. Groups global results.",
							"default" => "thirdparty"
						]
					],
					"required" => ["date_start", "date_end"]
				]
			],
			[
				"name" => "get_purchase_report",
				"description" => "Generate a purchase/expense report. If a Supplier is provided, it returns a detailed breakdown. Otherwise, it returns a global summary.",
				"inputSchema" => [
					"type" => "object",
					"properties" => [
						"thirdparty_id" => [
							"type" => "integer",
							"description" => "Optional: The ID of the supplier."
						],
						"date_start" => ["type" => "string", "description" => "Start date (YYYY-MM-DD)."],
						"date_end" => ["type" => "string", "description" => "End date (YYYY-MM-DD)."],
						"group_by" => [
							"type" => "string",
							"enum" => ["supplier", "product", "month"],
							"description" => "Only used if no Supplier is specified. Groups global results.",
							"default" => "supplier"
						]
					],
					"required" => ["date_start", "date_end"]
				]
			],
			[
				"name" => "get_inventory_report",
				"description" => "Generate an inventory report showing current stock levels and valuation.",
				"inputSchema" => [
					"type" => "object",
					"properties" => [
						"category_id" => ["type" => "integer", "description" => "Filter by category ID."],
						"warehouse_id" => ["type" => "integer", "description" => "Filter by warehouse ID."],
						"include_zero_stock" => ["type" => "boolean", "default" => false]
					]
				]
			],
			[
				"name" => "get_financial_report",
				"description" => "Generate a summary financial report (Income vs Expense) for a period.",
				"inputSchema" => [
					"type" => "object",
					"properties" => [
						"date_start" => ["type" => "string", "description" => "Start date (YYYY-MM-DD)."],
						"date_end" => ["type" => "string", "description" => "End date (YYYY-MM-DD)."]
					],
					"required" => ["date_start", "date_end"]
				]
			],
		];
	}

	/**
	 * Return categories this tool belongs to.
	 * Used by the intent parser to filter available tools.
	 *
	 * @return array<string> List of categories (e.g., ['billing', 'commercial'])
	 */
	public function getCategories(): array
	{
		return ['reporting', 'commercial', 'billing', 'stock'];
	}

	/**
	 * Executes the requested tool function based on its name.
	 *
	 * @param string $name The name of the tool to execute.
	 * @param array<string, mixed> $args The arguments for the tool (key-value pairs).
	 * @return mixed The result of the tool execution (usually an array) or an error array.
	 */
	public function execute(string $name, array $args)
	{
		switch ($name) {
			case 'get_thirdparty_transactions':
				return $this->getThirdpartyTransactions($args);
			case 'get_sales_report':
				return $this->getSalesReport($args);
			case 'get_purchase_report':
				return $this->getPurchaseReport($args);
			case 'get_inventory_report':
				return $this->getInventoryReport($args);
			case 'get_financial_report':
				return $this->getFinancialReport($args);
			default:
				return ["error" => "Tool function '$name' not found."];
		}
	}

	/**
	 * Resolves a Thirdparty ID from either an ID or a name.
	 *
	 * If `thirdparty_id` is provided, it is returned directly.
	 * Otherwise, if `thirdparty_name` is provided, the function searches
	 * the Dolibarr societe table using a LIKE match and returns the first match.
	 *
	 * @param array<string, mixed> $args Input parameters (thirdparty_id, thirdparty_name)
	 *
	 * @return int|null Thirdparty ID if found, otherwise null.
	 */
	private function resolveThirdparty($args)
	{

		if (!empty($args['thirdparty_id'])) {
			return (int) $args['thirdparty_id'];
		}

		if (!empty($args['thirdparty_name'])) {
			$sqlName = $this->db->escape($args['thirdparty_name']);

			$sql = "SELECT rowid FROM " . MAIN_DB_PREFIX . "societe
			WHERE nom LIKE '%" . $sqlName . "%'
			AND entity IN (" . getEntity('societe') . ")
			LIMIT 1";

			$resql = $this->db->query($sql);
			if ($resql && $obj = $this->db->fetch_object($resql)) {
				return $obj->rowid;
			}
		}

		return null;
	}

	/**
	 * Generate a sales/revenue report.
	 *
	 * @param array<string, mixed> $args Input arguments (date_start, date_end, limit, etc.)
	 * @return array<int, array<string, string>> List of sales with localized keys and values.
	 */
	private function getSalesReport(array $args): array
	{
		global $langs;

		$langs->loadLangs(array("main", "bills", "companies", "products"));

		$limit     = isset($args['limit']) ? (int) $args['limit'] : 50;
		$dateStart = dol_stringtotime($args['date_start']);
		$dateEnd   = dol_stringtotime($args['date_end']);
		$socid     = $this->resolveThirdparty($args);
		$groupBy   = isset($args['group_by']) ? (string) $args['group_by'] : 'thirdparty';

		$list      = [];
		$totalSum  = 0.0;
		// Status Filter: Valid (1) and Paid (2). Exclude Draft (0) and Abandoned (3).
		$dateRange = " AND f.datef >= '" . $this->db->idate($dateStart)
			. "' AND f.datef <= '" . $this->db->idate($dateEnd)
			. "' AND f.fk_statut IN (1, 2)";

		// CASE 1 -- Detailed list for a specific thirdparty.
		if ($socid) {
			$sql = "SELECT f.rowid, f.ref, f.total_ttc, f.fk_statut, f.paye, f.datef, s.nom FROM "
				. MAIN_DB_PREFIX . "facture as f LEFT JOIN "
				. MAIN_DB_PREFIX . "societe as s ON f.fk_soc = s.rowid WHERE f.entity IN ("
				. getEntity('facture') . ")"
				. $dateRange
				. " AND f.fk_soc = " . (int) $socid
				. " ORDER BY f.datef DESC LIMIT " . ((int) $limit);

			$resql = $this->db->query($sql);
			if ($resql) {
				while ($r = $this->db->fetch_object($resql)) {
					$totalSum += (float) $r->total_ttc;

					$statusLabel = $langs->transnoentitiesnoconv("Unknown");
					if ($r->fk_statut == 1 && $r->paye == 0) {
						$statusLabel = $langs->transnoentitiesnoconv("BillStatusNotPaid");
					} elseif ($r->fk_statut == 1 && $r->paye == 1) {
						$statusLabel = $langs->transnoentitiesnoconv("BillStatusStarted");
					} elseif ($r->fk_statut == 2) {
						$statusLabel = $langs->transnoentitiesnoconv("BillStatusPaid");
					}

					$url     = DOL_URL_ROOT . "/compta/facture/card.php?id=" . $r->rowid;
					$refHtml = '<a href="' . $url . '">' . $r->ref . '</a>';

					$list[] = [
						$langs->transnoentitiesnoconv("Ref")      => $refHtml,
						$langs->transnoentitiesnoconv("Date")     => dol_print_date($this->db->jdate($r->datef), 'day'),
						$langs->transnoentitiesnoconv("Customer") => $r->nom,
						$langs->transnoentitiesnoconv("Amount")   => price($r->total_ttc),
						$langs->transnoentitiesnoconv("Status")   => $statusLabel
					];
				}
				$this->db->free($resql);
			}
		} else {
			// CASE 2 -- Global grouped report.
			// Mirrors the pattern already used by getPurchaseReport(); previous implementation
			// of getSalesReport() ignored $groupBy entirely and always returned a flat list.
			$sanitizedSqlGroup = '';
			$colName           = '';
			$sqlJoin           = " LEFT JOIN " . MAIN_DB_PREFIX . "societe as s ON f.fk_soc = s.rowid";

			if ($groupBy === 'month') {
				$sanitizedSqlGroup = "DATE_FORMAT(f.datef, '%Y-%m')";
				$colName           = $langs->transnoentitiesnoconv("Month");
			} elseif ($groupBy === 'product') {
				// Aggregate on product line items. Lines without product_id fall back to their description.
				$sanitizedSqlGroup = "COALESCE(p.ref, fd.description, '?')";
				$colName           = $langs->transnoentitiesnoconv("Product");
				$sqlJoin .= " INNER JOIN " . MAIN_DB_PREFIX . "facturedet as fd ON fd.fk_facture = f.rowid LEFT JOIN "
					. MAIN_DB_PREFIX . "product as p ON fd.fk_product = p.rowid";
			} else {
				// Default: group by customer
				$sanitizedSqlGroup = "s.nom";
				$colName           = $langs->transnoentitiesnoconv("Customer");
			}

			// For product grouping we sum line totals (more accurate per-product);
			// otherwise we sum the invoice total_ttc.
			$amountExpr = ($groupBy === 'product') ? "SUM(fd.total_ttc)" : "SUM(f.total_ttc)";
			$countExpr  = ($groupBy === 'product') ? "COUNT(DISTINCT f.rowid)" : "COUNT(f.rowid)";

			$sql = "SELECT " . $sanitizedSqlGroup . " as group_key, "
				. $amountExpr . " as total_amount, "
				. $countExpr . " as count_inv FROM "
				. MAIN_DB_PREFIX . "facture as f"
				. $sqlJoin
				. " WHERE f.entity IN (" . getEntity('facture') . ")"
				. $dateRange
				. " GROUP BY group_key ORDER BY total_amount DESC LIMIT "
				. ((int) max(1, $limit));

			$resql = $this->db->query($sql);
			if ($resql) {
				while ($r = $this->db->fetch_object($resql)) {
					$totalSum += (float) $r->total_amount;
					$list[] = [
						$colName                                  => $r->group_key ? $r->group_key : $langs->transnoentitiesnoconv('Unknown'),
						$langs->transnoentitiesnoconv("Number")   => (int) $r->count_inv,
						$langs->transnoentitiesnoconv("Amount")   => price($r->total_amount)
					];
				}
				$this->db->free($resql);
			}
		}

		if (empty($list)) {
			return [[$langs->transnoentitiesnoconv("Info") => $langs->transnoentitiesnoconv("NoRecordFound")]];
		}

		// Append Total Row (shape depends on detailed-vs-grouped path)
		if ($socid) {
			$list[] = [
				$langs->transnoentitiesnoconv("Ref")      => $langs->transnoentitiesnoconv("Total"),
				$langs->transnoentitiesnoconv("Date")     => "",
				$langs->transnoentitiesnoconv("Customer") => "",
				$langs->transnoentitiesnoconv("Amount")   => price($totalSum),
				$langs->transnoentitiesnoconv("Status")   => ""
			];
		} else {
			$list[] = [
				$langs->transnoentitiesnoconv("Total")  => $langs->transnoentitiesnoconv("Total"),
				$langs->transnoentitiesnoconv("Amount") => price($totalSum)
			];
		}

		return $list;
	}

	/**
	 * Generate a list of raw transactions (Invoices, Orders, Proposals).
	 *
	 * @param array<string, mixed> $args Input arguments.
	 * @return array<int, array<string, string>> Combined list of transactions.
	 */
	private function getThirdpartyTransactions(array $args): array
	{
		global $langs;

		$langs->loadLangs(array("main", "bills", "orders", "propal"));

		$dateStart = dol_stringtotime($args['date_start']);
		$dateEnd = dol_stringtotime($args['date_end']);
		$type = isset($args['transaction_type']) ? (string) $args['transaction_type'] : 'all';

		$socid = $this->resolveThirdparty($args);
		if (!$socid) {
			$langs->load("errors");
			return [[$langs->transnoentitiesnoconv("Error") => $langs->transnoentitiesnoconv("ErrorThirdPartyNotFound")]];
		}

		$sqlQueries = [];

		// Invoices
		if ($type == 'all' || $type == 'invoices') {
			$sqlQueries[] = "SELECT 'Invoice' as source_type, rowid, ref, total_ttc as amount, datef as date_entry, fk_statut
						  FROM " . MAIN_DB_PREFIX . "facture
						  WHERE fk_soc = " . (int) $socid . " AND entity IN (" . getEntity('facture') . ")
						  AND fk_statut IN (1, 2)";
		}

		// Orders
		if ($type == 'all' || $type == 'orders') {
			$sqlQueries[] = "SELECT 'Order' as source_type, rowid, ref, total_ttc as amount, date_commande as date_entry, fk_statut
						  FROM " . MAIN_DB_PREFIX . "commande
						  WHERE fk_soc = " . (int) $socid . " AND entity IN (" . getEntity('commande') . ")
						  AND fk_statut > 0";
		}

		// Proposals
		if ($type == 'all' || $type == 'proposals') {
			$sqlQueries[] = "SELECT 'Proposal' as source_type, rowid, ref, total_ttc as amount, datep as date_entry, fk_statut
						  FROM " . MAIN_DB_PREFIX . "propal
						  WHERE fk_soc = " . (int) $socid . " AND entity IN (" . getEntity('propal') . ")
						  AND fk_statut > 0";
		}

		if (empty($sqlQueries)) {
			return [[$langs->transnoentitiesnoconv("Error") => "Invalid transaction type"]];
		}

		$sql = "SELECT * FROM (";
		$sql .= implode(" UNION ", $sqlQueries);
		$sql .= ") as combined_transactions ";
		$whereParts = [];
		if ($dateStart > 0) {
			$whereParts[] = "date_entry >= '" . $this->db->idate($dateStart) . "'";
		}
		if ($dateEnd > 0) {
			$whereParts[] = "date_entry <= '" . $this->db->idate($dateEnd) . "'";
		}

		if (!empty($whereParts)) {
			$sql .= " WHERE " . implode(" AND ", $whereParts);
		}

		$sql .= " ORDER BY date_entry DESC";

		$resql = $this->db->query($sql);
		$list = [];
		$totalAmt = 0.0;

		if ($resql) {
			while ($r = $this->db->fetch_object($resql)) {
				$totalAmt += (float) $r->amount;

				$statusTxt = "";
				$urlPath = "";

				if ($r->source_type === 'Invoice') {
					$urlPath = "/compta/facture/card.php?id=" . $r->rowid;
					if ($r->fk_statut == 2) {
						$statusTxt = $langs->transnoentitiesnoconv("BillStatusPaid");
					} elseif ($r->fk_statut == 1) {
						$statusTxt = $langs->transnoentitiesnoconv("BillStatusNotPaid");
					}
				} elseif ($r->source_type === 'Order') {
					$urlPath = "/commande/card.php?id=" . $r->rowid;
					if ($r->fk_statut == 1) {
						$statusTxt = $langs->transnoentitiesnoconv("StatusOrderValidated");
					} elseif ($r->fk_statut == 2) {
						$statusTxt = $langs->transnoentitiesnoconv("StatusOrderOnProcess");
					} elseif ($r->fk_statut == 3) {
						$statusTxt = $langs->transnoentitiesnoconv("StatusOrderDelivered");
					}
				} elseif ($r->source_type === 'Proposal') {
					$urlPath = "/comm/propal/card.php?id=" . $r->rowid;
					if ($r->fk_statut == 1) {
						$statusTxt = $langs->transnoentitiesnoconv("PropalStatusValidated");
					} elseif ($r->fk_statut == 2) {
						$statusTxt = $langs->transnoentitiesnoconv("PropalStatusSigned");
					} elseif ($r->fk_statut == 3) {
						$statusTxt = $langs->transnoentitiesnoconv("PropalStatusNotSigned");
					} elseif ($r->fk_statut == 4) {
						$statusTxt = $langs->transnoentitiesnoconv("PropalStatusBilled");
					}
				}

				$fullUrl = $urlPath ? DOL_URL_ROOT . $urlPath : "";
				$refHtml = $fullUrl ? '<a href="' . $fullUrl . '">' . $r->ref . '</a>' : $r->ref;

				$list[] = [
					$langs->transnoentitiesnoconv("Type") => $langs->transnoentitiesnoconv($r->source_type),
					$langs->transnoentitiesnoconv("Ref") => $refHtml,
					$langs->transnoentitiesnoconv("Date") => dol_print_date($this->db->jdate($r->date_entry), 'day'),
					$langs->transnoentitiesnoconv("Amount") => price($r->amount),
					$langs->transnoentitiesnoconv("Status") => $statusTxt
				];
			}
			$this->db->free($resql);
		}

		if (empty($list)) {
			return [[$langs->transnoentitiesnoconv("Info") => $langs->transnoentitiesnoconv("NoRecordFound")]];
		}

		// Summary
		$list[] = [
			$langs->transnoentitiesnoconv("Type") => $langs->transnoentitiesnoconv("Total"),
			$langs->transnoentitiesnoconv("Ref") => "",
			$langs->transnoentitiesnoconv("Date") => "",
			$langs->transnoentitiesnoconv("Amount") => price($totalAmt),
			$langs->transnoentitiesnoconv("Status") => ""
		];

		return $list;
	}

	/**
	 * Generate a purchase/expense report.
	 *
	 * @param array<string, mixed> $args Input arguments.
	 * @return array<int, array<string, string>> List of purchases or groups.
	 */
	private function getPurchaseReport(array $args): array
	{
		global $langs;

		$langs->loadLangs(array("main", "bills", "companies"));

		$socid = $this->resolveThirdparty($args);
		$groupBy = isset($args['group_by']) ? (string) $args['group_by'] : 'supplier';
		$dateStart = dol_stringtotime($args['date_start']);
		$dateEnd = dol_stringtotime($args['date_end']);
		$list = [];
		$totalSum = 0.0;

		// Detailed report for a specific Supplier
		if ($socid) {
			$sql = "SELECT f.rowid, f.ref, f.total_ttc, f.datef, s.nom
					FROM " . MAIN_DB_PREFIX . "facture_fourn as f
					LEFT JOIN " . MAIN_DB_PREFIX . "societe as s ON f.fk_soc = s.rowid
					WHERE f.entity IN (" . getEntity('facture_fourn') . ")
					AND f.fk_soc = " . (int) $socid . "
					AND f.datef >= '" . $this->db->idate($dateStart) . "'
					AND f.datef <= '" . $this->db->idate($dateEnd) . "'
					AND f.fk_statut > 0
					ORDER BY f.datef DESC";

			$resql = $this->db->query($sql);
			if ($resql) {
				while ($r = $this->db->fetch_object($resql)) {
					$totalSum += (float) $r->total_ttc;

					$url = DOL_URL_ROOT . "/fourn/facture/card.php?id=" . $r->rowid;
					$refHtml = '<a href="' . $url . '">' . $r->ref . '</a>';

					$list[] = [
						$langs->transnoentitiesnoconv("Ref") => $refHtml,
						$langs->transnoentitiesnoconv("Date") => dol_print_date($this->db->jdate($r->datef), 'day'),
						$langs->transnoentitiesnoconv("Supplier") => $r->nom,
						$langs->transnoentitiesnoconv("Amount") => price($r->total_ttc)
					];
				}
				$this->db->free($resql);
			}
		} else {  // Global Grouped Report
			$sanitizedSqlGroup = "";
			$colName = "";

			if ($groupBy === 'month') {
				$sanitizedSqlGroup = "DATE_FORMAT(f.datef, '%Y-%m')";
				$colName = $langs->transnoentitiesnoconv("Month");
			} else {
				$sanitizedSqlGroup = "s.nom";
				$colName = $langs->transnoentitiesnoconv("Supplier");
			}

			$sql = "SELECT " . $sanitizedSqlGroup . " as group_key, SUM(f.total_ttc) as total_amount, COUNT(f.rowid) as count_inv
				FROM " . MAIN_DB_PREFIX . "facture_fourn as f
				LEFT JOIN " . MAIN_DB_PREFIX . "societe as s ON f.fk_soc = s.rowid
				WHERE f.entity IN (" . getEntity('facture_fourn') . ")
				AND f.datef >= '" . $this->db->idate($dateStart) . "'
				AND f.datef <= '" . $this->db->idate($dateEnd) . "'
				AND f.fk_statut > 0
				GROUP BY group_key
				ORDER BY total_amount DESC";

			$resql = $this->db->query($sql);
			if ($resql) {
				while ($r = $this->db->fetch_object($resql)) {
					$totalSum += (float) $r->total_amount;
					$list[] = [
						$colName => $r->group_key ? $r->group_key : $langs->transnoentitiesnoconv('Unknown'),
						$langs->transnoentitiesnoconv("Number") => $r->count_inv,
						$langs->transnoentitiesnoconv("Amount") => price($r->total_amount)
					];
				}
				$this->db->free($resql);
			}
		}

		if (empty($list)) {
			return [[$langs->transnoentitiesnoconv("Info") => $langs->transnoentitiesnoconv("NoRecordFound")]];
		}

		// Summary Row
		$summary = [
			$langs->transnoentitiesnoconv("Amount") => price($totalSum)
		];

		if ($socid) {
			$summary[$langs->transnoentitiesnoconv("Ref")] = $langs->transnoentitiesnoconv("Total");
			$summary[$langs->transnoentitiesnoconv("Date")] = "";
			$summary[$langs->transnoentitiesnoconv("Supplier")] = "";
		} else {
			$summary[($groupBy === 'month' ? $langs->transnoentitiesnoconv("Month") : $langs->transnoentitiesnoconv("Supplier"))] = $langs->transnoentitiesnoconv("Total");
			$summary[$langs->transnoentitiesnoconv("Number")] = "";
		}
		$list[] = $summary;

		return $list;
	}

	/**
	 * Generate an inventory report.
	 *
	 * @param array<string, mixed> $args Input arguments.
	 * @return array<int, array<string, string>> Stock list.
	 */
	private function getInventoryReport(array $args): array
	{
		global $langs;

		$langs->loadLangs(array("products", "stocks"));

		$catId = isset($args['category_id']) ? (int) $args['category_id'] : 0;
		$warehouseId = isset($args['warehouse_id']) ? (int) $args['warehouse_id'] : 0;
		$includeZero = isset($args['include_zero_stock']) ? (bool) $args['include_zero_stock'] : false;

		$sql = "SELECT p.rowid, p.ref, p.label, p.pmp, ";

		if ($warehouseId > 0) {
			$sql .= " ps.reel as stock_level ";
			$sql .= " FROM " . MAIN_DB_PREFIX . "product as p ";
			$sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "product_stock as ps ON p.rowid = ps.fk_product ";
			$sql .= " WHERE ps.fk_entrepot = " . (int) $warehouseId;
		} else {
			$sql .= " p.stock as stock_level ";
			$sql .= " FROM " . MAIN_DB_PREFIX . "product as p ";
			$sql .= " WHERE 1=1 ";
		}

		$sql .= " AND p.entity IN (" . getEntity('product') . ")";

		if ($catId > 0) {
			$sql .= " AND p.rowid IN (SELECT fk_product FROM " . MAIN_DB_PREFIX . "categorie_product WHERE fk_categorie = " . (int) $catId . ")";
		}

		if (!$includeZero) {
			if ($warehouseId > 0) {
				$sql .= " AND ps.reel > 0";
			} else {
				$sql .= " AND p.stock > 0";
			}
		}

		$sql .= " ORDER BY p.ref ASC LIMIT 200";

		$resql = $this->db->query($sql);
		$list = [];
		$totalValuation = 0.0;
		$totalItems = 0;

		if ($resql) {
			while ($r = $this->db->fetch_object($resql)) {
				$stockVal = $r->stock_level * $r->pmp;
				$totalValuation += $stockVal;
				$totalItems += (int) $r->stock_level;

				$url = DOL_URL_ROOT . "/product/card.php?id=" . $r->rowid;
				$refHtml = '<a href="' . $url . '">' . $r->ref . '</a>';

				$list[] = [
					$langs->transnoentitiesnoconv("Ref") => $refHtml,
					$langs->transnoentitiesnoconv("Label") => $r->label,
					$langs->transnoentitiesnoconv("Stock") => $r->stock_level,
					$langs->transnoentitiesnoconv("PMPValue") => price($r->pmp),
					$langs->transnoentitiesnoconv("TotalValue") => price($stockVal)
				];
			}
			$this->db->free($resql);
		}

		if (empty($list)) {
			return [[$langs->transnoentitiesnoconv("Info") => $langs->transnoentitiesnoconv("NoRecordFound")]];
		}

		$list[] = [
			$langs->transnoentitiesnoconv("Ref") => $langs->transnoentitiesnoconv("Total"),
			$langs->transnoentitiesnoconv("Label") => "",
			$langs->transnoentitiesnoconv("Stock") => $totalItems,
			$langs->transnoentitiesnoconv("PMPValue") => "",
			$langs->transnoentitiesnoconv("TotalValue") => price($totalValuation)
		];

		return $list;
	}

	/**
	 * Generate a summary financial report (Income vs Expense).
	 *
	 * @param array<string, mixed> $args Input arguments.
	 * @return array<int, array<string, string>> Financial summary.
	 */
	private function getFinancialReport(array $args): array
	{
		global $langs;

		$langs->loadLangs(array("compta", "bills"));
		$dateStart = dol_stringtotime($args['date_start']);
		$dateEnd = dol_stringtotime($args['date_end']);

		// Income (Customer Invoices - Validated/Paid, no Drafts)
		$sqlIncome = "SELECT SUM(total_ttc) as total FROM " . MAIN_DB_PREFIX . "facture
					  WHERE entity IN (" . getEntity('facture') . ")
					  AND datef >= '" . $this->db->idate($dateStart) . "'
					  AND datef <= '" . $this->db->idate($dateEnd) . "'
					  AND fk_statut IN (1, 2)";

		$resIncome = $this->db->query($sqlIncome);
		$objIncome = $this->db->fetch_object($resIncome);
		$income = $objIncome && $objIncome->total ? (float) $objIncome->total : 0.0;

		// Expenses (Supplier Invoices - Validated, no Drafts)
		$sqlExpense = "SELECT SUM(total_ttc) as total FROM " . MAIN_DB_PREFIX . "facture_fourn
					   WHERE entity IN (" . getEntity('facture_fourn') . ")
					   AND datef >= '" . $this->db->idate($dateStart) . "'
					   AND datef <= '" . $this->db->idate($dateEnd) . "'
					   AND fk_statut > 0";

		$resExpense = $this->db->query($sqlExpense);
		$objExpense = $this->db->fetch_object($resExpense);
		$expense = $objExpense && $objExpense->total ? (float) $objExpense->total : 0.0;

		$net = $income - $expense;

		$list = [
			[
				$langs->transnoentitiesnoconv("Category") => $langs->transnoentitiesnoconv("Income"),
				$langs->transnoentitiesnoconv("Description") => $langs->transnoentitiesnoconv("BillsCustomers"),
				$langs->transnoentitiesnoconv("Amount") => price($income)
			],
			[
				$langs->transnoentitiesnoconv("Category") => $langs->transnoentitiesnoconv("Expenses"),
				$langs->transnoentitiesnoconv("Description") => $langs->transnoentitiesnoconv("BillsSuppliers"),
				$langs->transnoentitiesnoconv("Amount") => price($expense)
			],
			[
				$langs->transnoentitiesnoconv("Category") => $langs->transnoentitiesnoconv("Total"),
				$langs->transnoentitiesnoconv("Description") => $langs->transnoentitiesnoconv("Profit"),
				$langs->transnoentitiesnoconv("Amount") => price($net)
			]
		];

		return $list;
	}
}