<?php
/* Copyright (C) 2016       Jamal Elbaz             <jamelbaz@gmail.pro>
 * Copyright (C) 2016-2026	Alexandre Spangaro      <alexandre@inovea-conseil.com>
 * Copyright (C) 2018-2025  Frédéric France         <frederic.france@free.fr>
 * Copyright (C) 2024-2025  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/accountancy/class/accountancycategory.class.php
 * \ingroup Accountancy (Double entries)
 * \brief	File of class to manage categories of an accounting category_type
 */

// Class
require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';

/**
 * Class to manage categories of an accounting account
 */
class AccountancyCategory // extends CommonObject
{
	/**
	 * @var DoliDB Database handler.
	 */
	public $db;

	/**
	 * @var string 		Error string
	 */
	public $error;

	/**
	 * @var string[] Error codes (or messages)
	 */
	public $errors = array();

	/**
	 * @var string ID to identify managed object
	 */
	public $element = 'c_accounting_category';

	/**
	 * @var string Name of table without prefix where object is stored
	 */
	public $table_element = 'c_accounting_category';

	/**
	 * @var int ID
	 * @deprecated
	 */
	public $rowid;

	/**
	 * @var int ID
	 */
	public $id;

	/**
	 * @var ?string Accountancy code
	 */
	public $code;

	/**
	 * @var ?string Accountancy Category label
	 */
	public $label;

	/**
	 * @var ?string Accountancy range account
	 */
	public $range_account;

	/**
	 * @var ?int Sens of the account:  0: credit - debit, 1: debit - credit
	 */
	public $sens;

	/**
	 * @var ?int Category type of accountancy
	 */
	public $category_type;

	/**
	 * @var ?string Formula
	 */
	public $formula;

	/**
	 * @var ?int     Position
	 */
	public $position;

	/**
	 * @var ?int country id
	 */
	public $fk_country;

	/**
	 * @var ?int Is active
	 */
	public $active;

	/**
	 * @var Object[] Lines cptbk
	 */
	public $lines_cptbk;

	/**
	 * @var Object[] Lines display
	 */
	public $lines_display;

	/**
	 * @var mixed Sum debit credit
	 */
	public $sdc;

	/**
	 * @var array<string,float> Sum debit credit per month
	 */
	public $sdcpermonth;

	/**
	 * @var array<string,float> Sum debit credit per account
	 */
	public $sdcperaccount;

	/**
	 *  Constructor
	 *
	 *  @param      DoliDB		$db      Database handler
	 */
	public function __construct($db)
	{
		$this->db = $db;
	}


	/**
	 *  Create object into database
	 *
	 *  @param      User	$user        User that create
	 *  @param      int		$notrigger   0=launch triggers after, 1=disable triggers
	 *  @return     int      		   	 Return integer <0 if KO, Id of created object if OK
	 */
	public function create($user, $notrigger = 0)
	{
		global $conf, $langs;
		$error = 0;

		// Clean parameters
		if (isset($this->code)) {
			$this->code = trim($this->code);
		}
		if (isset($this->label)) {
			$this->label = trim($this->label);
		}
		if (isset($this->range_account)) {
			$this->range_account = trim($this->range_account);
		}
		if (isset($this->sens)) {
			$this->sens = (int) $this->sens;
		}
		if (isset($this->category_type)) {
			$this->category_type = (int) $this->category_type;
		}
		if (isset($this->formula)) {
			$this->formula = trim($this->formula);
		}
		if (isset($this->position)) {
			$this->position = (int) $this->position;
		}
		if (isset($this->fk_country)) {
			$this->fk_country = (int) $this->fk_country;
		}
		if (isset($this->active)) {
			$this->active = (int) $this->active;
		}

		// Check parameters
		// Put here code to add control on parameters values

		// Insert request
		$sql = "INSERT INTO ".$this->db->prefix().$this->table_element." (";
		if ($this->rowid > 0) {
			$sql .= "rowid, ";
		}
		$sql .= "code, ";
		$sql .= "label, ";
		$sql .= "range_account, ";
		$sql .= "sens, ";
		$sql .= "category_type, ";
		$sql .= "formula, ";
		$sql .= "position, ";
		$sql .= "fk_country, ";
		$sql .= "active, ";
		$sql .= "entity";
		$sql .= ") VALUES (";
		if ($this->rowid > 0) {
			$sql .= " ".((int) $this->rowid).",";
		}
		$sql .= " ".(!isset($this->code) ? "NULL" : "'".$this->db->escape($this->code)."'").",";
		$sql .= " ".(!isset($this->label) ? 'NULL' : "'".$this->db->escape($this->label)."'").",";
		$sql .= " ".(!isset($this->range_account) ? 'NULL' : "'".$this->db->escape($this->range_account)."'").",";
		$sql .= " ".(!isset($this->sens) ? 'NULL' : "'".$this->db->escape((string) $this->sens)."'").",";
		$sql .= " ".(!isset($this->category_type) ? 'NULL' : "'".$this->db->escape((string) $this->category_type)."'").",";
		$sql .= " ".(!isset($this->formula) ? 'NULL' : "'".$this->db->escape($this->formula)."'").",";
		$sql .= " ".(!isset($this->position) ? 'NULL' : ((int) $this->position)).",";
		$sql .= " ".(!isset($this->fk_country) ? 'NULL' : ((int) $this->fk_country)).",";
		$sql .= " ".(!isset($this->active) ? 'NULL' : ((int) $this->active));
		$sql .= ", ".((int) $conf->entity);
		$sql .= ")";

		$this->db->begin();

		dol_syslog(get_class($this)."::create", LOG_DEBUG);
		$resql = $this->db->query($sql);
		if (!$resql) {
			$error++;
			$this->errors[] = "Error ".$this->db->lasterror();
		}

		// Commit or rollback
		if ($error) {
			foreach ($this->errors as $errmsg) {
				dol_syslog(get_class($this)."::create ".$errmsg, LOG_ERR);
				$this->error .= ($this->error ? ', '.$errmsg : $errmsg);
			}
			$this->db->rollback();
			return -1 * $error;
		} else {
			$this->db->commit();
			return $this->id;
		}
	}


	/**
	 *  Load object in memory from database
	 *
	 *  @param      int		$id    	Id object
	 *  @param		string	$code	Code
	 *  @param		string	$label	Label
	 *  @return     int          	Return integer <0 if KO, >0 if OK
	 */
	public function fetch($id, $code = '', $label = '')
	{
		$sql = "SELECT";
		$sql .= " t.rowid,";
		$sql .= " t.code,";
		$sql .= " t.label,";
		$sql .= " t.range_account,";
		$sql .= " t.sens,";
		$sql .= " t.category_type,";
		$sql .= " t.formula,";
		$sql .= " t.position,";
		$sql .= " t.fk_country,";
		$sql .= " t.active";
		$sql .= " FROM ".$this->db->prefix().$this->table_element." as t";
		if ($id) {
			$sql .= " WHERE t.rowid = ".((int) $id);
		} else {
			$sql .= " WHERE t.entity IN (".getEntity('c_accounting_category').")"; // Don't use entity if you use rowid
			if ($code) {
				$sql .= " AND t.code = '".$this->db->escape($code)."'";
			} elseif ($label) {
				$sql .= " AND t.label = '".$this->db->escape($label)."'";
			}
		}

		dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
		$resql = $this->db->query($sql);
		if ($resql) {
			if ($this->db->num_rows($resql)) {
				$obj = $this->db->fetch_object($resql);

				$this->id            = $obj->rowid;
				$this->code          = $obj->code;
				$this->label         = $obj->label;
				$this->range_account = $obj->range_account;
				$this->sens          = $obj->sens;
				$this->category_type = $obj->category_type;
				$this->formula       = $obj->formula;
				$this->position      = $obj->position;
				$this->fk_country    = $obj->fk_country;
				$this->active        = $obj->active;
			}
			$this->db->free($resql);

			return 1;
		} else {
			$this->error = "Error ".$this->db->lasterror();
			return -1;
		}
	}


	/**
	 *  Update object into database
	 *
	 *  @param      User	$user        User that modify
	 *  @param      int		$notrigger	 0=launch triggers after, 1=disable triggers
	 *  @return     int     		   	 Return integer <0 if KO, >0 if OK
	 */
	public function update($user = null, $notrigger = 0)
	{
		global $conf, $langs;
		$error = 0;

		// Clean parameters
		if (isset($this->code)) {
			$this->code = trim($this->code);
		}
		if (isset($this->label)) {
			$this->label = trim($this->label);
		}
		if (isset($this->range_account)) {
			$this->range_account = trim($this->range_account);
		}
		if (isset($this->sens)) {
			$this->sens = (int) $this->sens;
		}
		if (isset($this->category_type)) {
			$this->category_type = (int) $this->category_type;
		}
		if (isset($this->formula)) {
			$this->formula = trim($this->formula);
		}
		if (isset($this->position)) {
			$this->position = (int) $this->position;
		}
		if (isset($this->fk_country)) {
			$this->fk_country = (int) $this->fk_country;
		}
		if (isset($this->active)) {
			$this->active = (int) $this->active;
		}


		// Check parameters
		// Put here code to add control on parameters values

		// Update request
		$sql = "UPDATE ".$this->db->prefix().$this->table_element." SET";
		$sql .= " code=".(isset($this->code) ? "'".$this->db->escape($this->code)."'" : "null").",";
		$sql .= " label=".(isset($this->label) ? "'".$this->db->escape($this->label)."'" : "null").",";
		$sql .= " range_account=".(isset($this->range_account) ? "'".$this->db->escape($this->range_account)."'" : "null").",";
		$sql .= " sens=".(isset($this->sens) ? ((int) $this->sens) : "null").",";
		$sql .= " category_type=".(isset($this->category_type) ? ((int) $this->category_type) : "null").",";
		$sql .= " formula=".(isset($this->formula) ? "'".$this->db->escape($this->formula)."'" : "null").",";
		$sql .= " position=".(isset($this->position) ? ((int) $this->position) : "null").",";
		$sql .= " fk_country=".(isset($this->fk_country) ? ((int) $this->fk_country) : "null").",";
		$sql .= " active=".(isset($this->active) ? ((int) $this->active) : "null");
		$sql .= " WHERE rowid=".((int) $this->id);

		$this->db->begin();

		dol_syslog(get_class($this)."::update", LOG_DEBUG);
		$resql = $this->db->query($sql);
		if (!$resql) {
			$error++;
			$this->errors[] = "Error ".$this->db->lasterror();
		}

		// Commit or rollback
		if ($error) {
			foreach ($this->errors as $errmsg) {
				dol_syslog(get_class($this)."::update ".$errmsg, LOG_ERR);
				$this->error .= ($this->error ? ', '.$errmsg : $errmsg);
			}
			$this->db->rollback();
			return -1 * $error;
		} else {
			$this->db->commit();
			return 1;
		}
	}


	/**
	 *  Delete object in database
	 *
	 *	@param  User	$user        User that delete
	 *  @param	int		$notrigger	 0=launch triggers after, 1=disable triggers
	 *  @return	int					 Return integer <0 if KO, >0 if OK
	 */
	public function delete($user, $notrigger = 0)
	{
		$error = 0;

		$sql = "DELETE FROM ".$this->db->prefix().$this->table_element;
		$sql .= " WHERE rowid=".((int) $this->id);

		$this->db->begin();

		dol_syslog(get_class($this)."::delete", LOG_DEBUG);
		$resql = $this->db->query($sql);
		if (!$resql) {
			$error++;
			$this->errors[] = "Error ".$this->db->lasterror();
		}

		// Commit or rollback
		if ($error) {
			foreach ($this->errors as $errmsg) {
				dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
				$this->error .= ($this->error ? ', '.$errmsg : $errmsg);
			}
			$this->db->rollback();
			return -1 * $error;
		} else {
			$this->db->commit();
			return 1;
		}
	}


	/**
	 * Function to select into ->lines_display all accounting accounts for a given custom accounting group
	 *
	 * @param 	int 	$id 	Id
	 * @return 	int 			Return integer <0 if KO, 0 if not found, >0 if OK
	 */
	public function display($id)
	{
		global $conf;

		$pcgver = getDolGlobalInt('CHARTOFACCOUNTS');

		$sql = "SELECT t.rowid, t.account_number, t.label";
		$sql .= " FROM ".$this->db->prefix()."accounting_account as t";
		$sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON t.fk_pcg_version = asy.pcg_version AND asy.rowid = ".((int) $pcgver);
		$sql .= " WHERE t.fk_accounting_category = ".((int) $id);
		$sql .= " AND t.entity = ".$conf->entity;

		$this->lines_display = array();

		dol_syslog(__METHOD__, LOG_DEBUG);
		$resql = $this->db->query($sql);
		if ($resql) {
			$num = $this->db->num_rows($resql);
			if ($num) {
				while ($obj = $this->db->fetch_object($resql)) {
					$this->lines_display[] = $obj;
				}
			}
			return $num;
		} else {
			$this->error = "Error ".$this->db->lasterror();
			$this->errors[] = $this->error;
			dol_syslog(__METHOD__." ".implode(',', $this->errors), LOG_ERR);

			return -1;
		}
	}

	/**
	 * Function to fill ->lines_cptbk with accounting account (defined in chart of account) and not yet into a custom group
	 *
	 * @param 	int $id     Id of category to know which account to exclude
	 * @return 	int 		Return integer <0 if KO, 0 if not found, >0 if OK
	 */
	public function getAccountsWithNoCategory($id)
	{
		global $conf;

		$sql = "SELECT aa.account_number as numero_compte, aa.label as label_compte";
		$sql .= " FROM ".$this->db->prefix()."accounting_account as aa";
		$sql .= " INNER JOIN ".$this->db->prefix()."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
		$sql .= " WHERE (aa.fk_accounting_category <> ".((int) $id)." OR aa.fk_accounting_category IS NULL)";
		$sql .= " AND asy.rowid = ".((int) getDolGlobalInt('CHARTOFACCOUNTS'));
		$sql .= " AND aa.active = 1";
		$sql .= " AND aa.entity = ".$conf->entity;
		$sql .= " GROUP BY aa.account_number, aa.label";
		$sql .= " ORDER BY aa.account_number, aa.label";

		$this->lines_cptbk = array();

		dol_syslog(__METHOD__, LOG_DEBUG);
		$resql = $this->db->query($sql);
		if ($resql) {
			$num = $this->db->num_rows($resql);
			if ($num) {
				while ($obj = $this->db->fetch_object($resql)) {
					$this->lines_cptbk[] = $obj;
				}
			}

			return $num;
		} else {
			$this->error = "Error ".$this->db->lasterror();
			$this->errors[] = $this->error;
			dol_syslog(__METHOD__." ".implode(',', $this->errors), LOG_ERR);

			return -1;
		}
	}

	/**
	 * Function to add an accounting account in an accounting category
	 *
	 * @param int					$id_cat		Id category
	 * @param array<string,?string> $cpts		list of accounts array
	 *
	 * @return int Return integer <0 if KO, >0 if OK
	 */
	public function updateAccAcc($id_cat, $cpts = array())
	{
		global $conf;
		$error = 0;

		require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';

		$sql = "SELECT aa.rowid, aa.account_number";
		$sql .= " FROM ".$this->db->prefix()."accounting_account as aa";
		$sql .= " INNER JOIN ".$this->db->prefix()."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
		$sql .= " AND asy.rowid = ".((int) getDolGlobalInt('CHARTOFACCOUNTS'));
		$sql .= " AND aa.active = 1";
		$sql .= " AND aa.entity = ".$conf->entity;
		$sql .= " ORDER BY LENGTH(aa.account_number) DESC;"; // LENGTH is ok with mysql and postgresql

		$this->db->begin();

		dol_syslog(__METHOD__, LOG_DEBUG);
		$resql = $this->db->query($sql);
		if (!$resql) {
			$error++;
			$this->errors[] = "Error ".$this->db->lasterror();
			$this->db->rollback();
			return -1;
		}

		$accountincptsadded = array();
		while ($obj = $this->db->fetch_object($resql)) {
			$account_number_formated = length_accountg($obj->account_number);
			if (!empty($accountincptsadded[$account_number_formated])) {
				continue;
			}

			if (array_key_exists($account_number_formated, $cpts)) {
				$accountincptsadded[$account_number_formated] = 1;
				// We found an account number that is in list $cpts of account to add
				$sql = "UPDATE ".$this->db->prefix()."accounting_account";
				$sql .= " SET fk_accounting_category=".((int) $id_cat);
				$sql .= " WHERE rowid=".((int) $obj->rowid);
				dol_syslog(__METHOD__, LOG_DEBUG);
				$resqlupdate = $this->db->query($sql);
				if (!$resqlupdate) {
					$error++;
					$this->errors[] = "Error ".$this->db->lasterror();
				}
			}
		}

		// Commit or rollback
		if ($error) {
			foreach ($this->errors as $errmsg) {
				dol_syslog(__METHOD__." ".$errmsg, LOG_ERR);
				$this->error .= ($this->error ? ', '.$errmsg : $errmsg);
			}
			$this->db->rollback();

			return -1 * $error;
		} else {
			$this->db->commit();

			return 1;
		}
	}

	/**
	 * Function to delete an accounting account from an accounting category
	 *
	 * @param int $cpt_id Id of accounting account
	 *
	 * @return int Return integer <0 if KO, >0 if OK
	 */
	public function deleteCptCat($cpt_id)
	{
		$error = 0;

		$sql = "UPDATE ".$this->db->prefix()."accounting_account as aa";
		$sql .= " SET fk_accounting_category= 0";
		$sql .= " WHERE aa.rowid = ".((int) $cpt_id);
		$this->db->begin();

		dol_syslog(__METHOD__, LOG_DEBUG);
		$resql = $this->db->query($sql);
		if (!$resql) {
			$error++;
			$this->errors[] = "Error ".$this->db->lasterror();
		}

		// Commit or rollback
		if ($error) {
			foreach ($this->errors as $errmsg) {
				dol_syslog(__METHOD__." ".$errmsg, LOG_ERR);
				$this->error .= ($this->error ? ', '.$errmsg : $errmsg);
			}
			$this->db->rollback();

			return -1 * $error;
		} else {
			$this->db->commit();

			return 1;
		}
	}

	/**
	 * Function to set the property ->sdc (and ->sdcperaccount) that is the result of an accounting account from the ledger with a direction and a period
	 *
	 * @param int|array<?string>	$cpt 	Accounting account or array of accounting account
	 * @param int 		$date_start			Date start
	 * @param int	 	$date_end			Date end
	 * @param int<0,1>	$sens 				Sens of the account:  0: credit - debit (use this by default), 1: debit - credit
	 * @param string	$thirdparty_code	Third party code
	 * @param int       $month 				Specific month - Can be empty
	 * @param int       $year 				Specific year - Can be empty
	 * @return integer 						Return integer <0 if KO, >= 0 if OK
	 */
	public function getSumDebitCredit($cpt, $date_start, $date_end, $sens, $thirdparty_code = 'nofilter', $month = 0, $year = 0)
	{
		global $conf;

		$this->sdc = 0;
		$this->sdcpermonth = array();

		$listofaccount = '';

		if (is_array($cpt)) {
			foreach ($cpt as $cptcursor) {
				if (! is_null($cptcursor)) {
					if ($listofaccount) {
						$listofaccount .= ",";
					}
					$listofaccount .= "'".$cptcursor."'";
				}
			}
			if (empty($listofaccount)) {
				// List of account is empty, so we do no try sql request, we can say result is empty.
				return 0;
			}
		}

		$sql = "SELECT SUM(t.debit) as debit, SUM(t.credit) as credit";
		if (is_array($cpt)) {
			$sql .= ", t.numero_compte as accountancy_account";
		}
		$sql .= " FROM ".$this->db->prefix()."accounting_bookkeeping as t";
		//if (in_array($this->db->type, array('mysql', 'mysqli'))) $sql.=' USE INDEX idx_accounting_bookkeeping_doc_date';
		$sql .= " WHERE t.entity = ".((int) $conf->entity);
		if (is_array($cpt)) {
			$sql .= " AND t.numero_compte IN (".$this->db->sanitize($listofaccount, 1).")";
		} else {
			$sql .= " AND t.numero_compte = '".$this->db->escape((string) $cpt)."'";
		}
		if (!empty($date_start) && !empty($date_end) && (empty($month) || empty($year))) {	// If month/year provided, it is stronger than filter date_start/date_end
			$sql .= " AND (t.doc_date BETWEEN '".$this->db->idate($date_start)."' AND '".$this->db->idate($date_end)."')";
		}
		if (!empty($month) && !empty($year)) {
			$sql .= " AND (t.doc_date BETWEEN '".$this->db->idate(dol_get_first_day($year, $month))."' AND '".$this->db->idate(dol_get_last_day($year, $month))."')";
		}
		if ($thirdparty_code != 'nofilter') {
			$sql .= " AND t.thirdparty_code = '".$this->db->escape($thirdparty_code)."'";
		}
		if (is_array($cpt)) {
			$sql .= " GROUP BY t.numero_compte";
		}

		$resql = $this->db->query($sql);
		if ($resql) {
			$num = $this->db->num_rows($resql);
			if ($num) {
				$i = 0;
				while ($i < $num) {
					$obj = $this->db->fetch_object($resql);
					if ($obj) {
						if ($sens == 1) {
							$this->sdc = $obj->debit - $obj->credit;
						} else {
							$this->sdc = $obj->credit - $obj->debit;
						}
						if (is_array($cpt)) {
							$this->sdcperaccount[$obj->accountancy_account] = $this->sdc;
						}
					}
					$i++;
				}
			}

			return $num;
		} else {
			$this->error = "Error ".$this->db->lasterror();
			$this->errors[] = $this->error;
			dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
			return -1;
		}
	}

	/**
	 * Function to get an array of all active custom groups (llx_c_accunting_categories) with their accounts from the chart of account (ll_accounting_acount)
	 *
	 * @param	int				$catid		Custom group ID
	 * @return array<string,array<int,array{id:int,code:string,label:string,position:string,category_type:string,formula:string,sens:string,dc:string,account_number:string,account_label:string}>>|int<-1,-1>   		    Result in table (array), -1 if KO
	 * @see getCats(), getCptsCat()
	 */
	public function getCatsCpts($catid = 0)
	{
		global $mysoc, $conf;

		if (empty($mysoc->country_id)) {
			$this->error = "Error ".$this->db->lasterror();
			dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
			return -1;
		}

		$sql = "SELECT t.rowid, t.account_number, t.label as account_label,";
		$sql .= " cat.code, cat.position, cat.label as name_cat, cat.sens, cat.category_type, cat.formula";
		$sql .= " FROM ".$this->db->prefix()."accounting_account as t, ".$this->db->prefix()."c_accounting_category as cat";
		$sql .= " WHERE t.fk_accounting_category IN (SELECT c.rowid";
		$sql .= " FROM ".$this->db->prefix().$this->table_element." as c";
		$sql .= " WHERE c.active = 1";
		$sql .= " AND c.entity = ".$conf->entity;
		$sql .= " AND (c.fk_country = ".((int) $mysoc->country_id)." OR c.fk_country = 0)";
		$sql .= " AND cat.rowid = t.fk_accounting_category";
		$sql .= " AND t.entity = ".$conf->entity;
		if ($catid > 0) {
			$sql .= " AND cat.rowid = ".((int) $catid);
		}
		$sql .= " ORDER BY cat.position ASC";

		$resql = $this->db->query($sql);
		if ($resql) {
			$obj = '';
			$num = $this->db->num_rows($resql);
			$data = array();
			if ($num) {
				while ($obj = $this->db->fetch_object($resql)) {
					$name_cat = $obj->name_cat;
					$data[$name_cat][$obj->rowid] = array(
						'id' => $obj->rowid,
						'code' => $obj->code,
						'label' => $obj->label,
						'position' => $obj->position,
						'category_type' => $obj->category_type,
						'formula' => $obj->formula,
						'sens' => $obj->sens,
						'dc' => $obj->sens,
						'account_number' => $obj->account_number,
						'account_label' => $obj->account_label
					);
				}
			}
			return $data;
		} else {
			$this->error = "Error ".$this->db->lasterror();
			dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
			return -1;
		}
	}

	/**
	 * Return list of custom groups.
	 * For list + detail of accounting account, see getCatsCpt()
	 *
	 * @param	int			$categorytype		-1=All, 0=Only non computed groups, 1=Only computed groups
	 * @param	int			$active				1= active, 0=not active
	 * @param	int			$id_report			id of the report
	 * @return	never|array<array{rowid:string,code:string,label:string,formula:string,position:string,category_type:string,sens:string,dc:string}>|int	Array of groups or -1 if error
	 * @see getCatsCpts(), getCptsCat()
	 */
	public function getCats($categorytype = -1, $active = 1, $id_report = 1)
	{
		global $conf, $mysoc;

		if (empty($mysoc->country_id)) {
			dol_print_error(null, 'Call to getCats with mysoc country not yet defined');
			exit();
		}

		$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position, c.category_type, c.sens, c.fk_report";
		$sql .= " FROM ".$this->db->prefix().$this->table_element." as c";
		$sql .= " WHERE c.active = " . (int) $active;
		$sql .= " AND c.fk_report=".((int) $id_report);
		$sql .= " AND c.entity = ".$conf->entity;
		if ($categorytype >= 0) {
			$sql .= " AND c.category_type = 1";
		}
		$sql .= " AND (c.fk_country = ".((int) $mysoc->country_id)." OR c.fk_country = 0)";
		$sql .= " ORDER BY c.position ASC";

		$resql = $this->db->query($sql);
		if ($resql) {
			$i = 0;
			$obj = '';
			$num = $this->db->num_rows($resql);
			$data = array();
			if ($num) {
				while ($i < $num) {
					$obj = $this->db->fetch_object($resql);

					$data[] = array(
							'rowid' => $obj->rowid,
							'code' => $obj->code,
							'label' => $obj->label,
							'position' => $obj->position,
							'category_type' => $obj->category_type,
							'formula' => $obj->formula,
							'sens' => $obj->sens,
							'dc' => $obj->sens
					);
					$i++;
				}
			}
			return $data;
		} else {
			$this->error = "Error ".$this->db->lasterror();
			$this->errors[] = $this->error;
			dol_syslog(__METHOD__." ".implode(',', $this->errors), LOG_ERR);

			return -1;
		}
	}


	/**
	 * Get all accounting account of a given custom group (or a list of custom groups).
	 * You must choose between first parameter (personalized group) or the second (free criteria filter)
	 *
	 * @param 	int 		$cat_id 						Id if personalized accounting group/category
	 * @param 	string 		$sanitizedpredefinedgroupwhere 	Sql criteria filter to select accounting accounts. This value must be sanitized and not come from an input of a user.
	 * 														Example: "pcg_type = 'EXPENSE' AND fk_pcg_version = 'xx'"
	 * 														Example: "fk_accounting_category = 99"
	 * @return	never|array<array{id:int,account_number:string,account_label:string}>|int<-1,-1>		Array of accounting accounts or -1 if error
	 * @see getCats(), getCatsCpts()
	 */
	public function getCptsCat($cat_id, $sanitizedpredefinedgroupwhere = '')
	{
		global $conf, $mysoc;
		$sql = '';

		if (empty($mysoc->country_id) && empty($mysoc->country_code)) {
			dol_print_error(null, 'Call to select_accounting_account with mysoc country not yet defined');
			exit();
		}

		$pcgverid = getDolGlobalInt('CHARTOFACCOUNTS');
		$pcgvercode = dol_getIdFromCode($this->db, (string) $pcgverid, 'accounting_system', 'rowid', 'pcg_version');
		if (empty($pcgvercode)) {
			$pcgvercode = $pcgverid;
		}

		if (!empty($cat_id)) {
			$sql = "SELECT t.rowid, t.account_number, t.label as account_label";
			$sql .= " FROM ".$this->db->prefix()."accounting_account as t";
			$sql .= " WHERE t.fk_accounting_category = ".((int) $cat_id);
			$sql .= " AND t.entity = ".$conf->entity;
			$sql .= " AND t.active = 1";
			$sql .= " AND t.fk_pcg_version = '".$this->db->escape($pcgvercode)."'";
			$sql .= " ORDER BY t.account_number";
		} else {
			$sql = "SELECT t.rowid, t.account_number, t.label as account_label";
			$sql .= " FROM ".$this->db->prefix()."accounting_account as t";
			$sql .= " WHERE ".$sanitizedpredefinedgroupwhere;
			$sql .= " AND t.entity = ".$conf->entity;
			$sql .= ' AND t.active = 1';
			$sql .= " AND t.fk_pcg_version = '".$this->db->escape($pcgvercode)."'";
			$sql .= " ORDER BY t.account_number";
		}

		$resql = $this->db->query($sql);
		if ($resql) {
			$i = 0;
			$obj = '';
			$num = $this->db->num_rows($resql);
			$data = array();
			if ($num) {
				while ($obj = $this->db->fetch_object($resql)) {
					$data[] = array(
							'id' => $obj->rowid,
							'account_number' => $obj->account_number,
							'account_label' => $obj->account_label,
					);
					$i++;
				}
			}
			return $data;
		} else {
			$this->error = "Error ".$this->db->lasterror();
			dol_syslog(__METHOD__." ".$this->error, LOG_ERR);

			return -1;
		}
	}


	// ====================================================================
	// MULTI REPORT METHODS (New system with llx_accounting_category_account)
	// Requires constant ACCOUNTING_ENABLE_MULTI_REPORT
	// ====================================================================

	/**
	 * Link an accounting account to this category (multi-report system)
	 *
	 * @param   int     $fkAccountingAccount    ID of the accounting account to link
	 * @return  int<-1,1>                       <0 if KO, 0 if already exists, >0 if OK
	 */
	public function addAccountToCategory($fkAccountingAccount)
	{
		// Check if relation already exists
		if ($this->isAccountInCategory($fkAccountingAccount)) {
			return 0;
		}

		$sql = "INSERT INTO ".MAIN_DB_PREFIX."accounting_category_account";
		$sql .= " (fk_accounting_category, fk_accounting_account)";
		$sql .= " VALUES (".((int) $this->id).", ".((int) $fkAccountingAccount).")";

		dol_syslog(get_class($this)."::addAccountToCategory", LOG_DEBUG);

		$this->db->begin();

		$resql = $this->db->query($sql);
		if ($resql) {
			$this->db->commit();
			return 1;
		} else {
			$this->error = $this->db->lasterror();
			$this->errors[] = $this->error;
			$this->db->rollback();
			return -1;
		}
	}

	/**
	 * Unlink an accounting account from this category (multi-report system)
	 *
	 * @param   int         $fkAccountingAccount    ID of the accounting account to unlink
	 * @return  int<-1,1>                           <0 if KO, >0 if OK
	 */
	public function deleteAccountFromCategory($fkAccountingAccount)
	{
		$sql = "DELETE FROM ".MAIN_DB_PREFIX."accounting_category_account";
		$sql .= " WHERE fk_accounting_category = ".((int) $this->id);
		$sql .= " AND fk_accounting_account = ".((int) $fkAccountingAccount);

		dol_syslog(get_class($this)."::deleteAccountFromCategory", LOG_DEBUG);

		$this->db->begin();

		$resql = $this->db->query($sql);
		if ($resql) {
			$this->db->commit();
			return 1;
		} else {
			$this->error = $this->db->lasterror();
			$this->errors[] = $this->error;
			$this->db->rollback();
			return -1;
		}
	}

	/**
	 * Get all accounting accounts linked to this category (multi-report system)
	 *
	 * @return array<int,array{id:int,account_number:string,label:string}> Array of accounting accounts indexed by account ID, or empty array if error
	 */
	public function getAccountsForCategory()
	{
		global $conf;

		$accounts = array();

		$sql = "SELECT aca.fk_accounting_account, aa.account_number, aa.label";
		$sql .= " FROM ".MAIN_DB_PREFIX."accounting_category_account as aca";
		$sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_account as aa ON aa.rowid = aca.fk_accounting_account";
		$sql .= " WHERE aca.fk_accounting_category = ".((int) $this->id);
		$sql .= " AND aa.entity = ".$conf->entity;
		$sql .= " ORDER BY aa.account_number ASC";

		dol_syslog(get_class($this)."::getAccountsForCategory", LOG_DEBUG);

		$resql = $this->db->query($sql);
		if ($resql) {
			$num = $this->db->num_rows($resql);
			$i = 0;
			while ($i < $num) {
				$obj = $this->db->fetch_object($resql);
				$accounts[$obj->fk_accounting_account] = array(
					'id' => $obj->fk_accounting_account,
					'account_number' => $obj->account_number,
					'label' => $obj->label
				);
				$i++;
			}
			$this->db->free($resql);
			return $accounts;
		} else {
			$this->error = $this->db->lasterror();
			$this->errors[] = $this->error;
			dol_syslog(get_class($this)."::getAccountsForCategory ".$this->error, LOG_ERR);
			return array();
		}
	}

	/**
	 * Get categories for a specific accounting account (multi-report system)
	 *
	 * @param   int     $fkAccountingAccount                                        ID of the accounting account
	 * @return  array<int,array{id:int,code:string,label:string,fk_report:int}>     Array of categories indexed by category ID, or empty array if error
	 */
	public function getCategoriesForAccount($fkAccountingAccount)
	{
		global $conf;

		$categories = array();

		$sql = "SELECT aca.fk_accounting_category, ac.code, ac.label, ac.fk_report";
		$sql .= " FROM ".MAIN_DB_PREFIX."accounting_category_account as aca";
		$sql .= " INNER JOIN ".MAIN_DB_PREFIX."c_accounting_category as ac ON ac.rowid = aca.fk_accounting_category";
		$sql .= " WHERE aca.fk_accounting_account = ".((int) $fkAccountingAccount);
		$sql .= " AND ac.entity = ".$conf->entity;
		$sql .= " ORDER BY ac.position ASC";

		dol_syslog(get_class($this)."::getCategoriesForAccount", LOG_DEBUG);

		$resql = $this->db->query($sql);
		if ($resql) {
			$num = $this->db->num_rows($resql);
			$i = 0;
			while ($i < $num) {
				$obj = $this->db->fetch_object($resql);
				$categories[$obj->fk_accounting_category] = array(
					'id' => $obj->fk_accounting_category,
					'code' => $obj->code,
					'label' => $obj->label,
					'fk_report' => $obj->fk_report
				);
				$i++;
			}
			$this->db->free($resql);
			return $categories;
		} else {
			$this->error = $this->db->lasterror();
			$this->errors[] = $this->error;
			dol_syslog(get_class($this)."::getCategoriesForAccount ".$this->error, LOG_ERR);
			return array();
		}
	}

	/**
	 * Check if an account belongs to this category (multi-report system)
	 *
	 * @param   int     $fkAccountingAccount            ID of the accounting account
	 * @return  bool                                    True if account is in category, false otherwise
	 */
	public function isAccountInCategory($fkAccountingAccount)
	{
		$sql = "SELECT COUNT(*) as nb";
		$sql .= " FROM ".MAIN_DB_PREFIX."accounting_category_account";
		$sql .= " WHERE fk_accounting_category = ".((int) $this->id);
		$sql .= " AND fk_accounting_account = ".((int) $fkAccountingAccount);

		dol_syslog(get_class($this)."::isAccountInCategory", LOG_DEBUG);

		$resql = $this->db->query($sql);
		if ($resql) {
			$obj = $this->db->fetch_object($resql);
			return ($obj->nb > 0);
		}
		return false;
	}

	/**
	 * Get all accounting accounts NOT linked to this category (for selection)
	 *
	 * @return array<array{id:int,account_number:string,label:string}>  Array of available accounts, or empty array if error
	 */
	public function getAvailableAccountsForCategory()
	{
		global $conf;

		$accounts = array();

		$pcgverid = getDolGlobalInt('CHARTOFACCOUNTS');
		$pcgvercode = dol_getIdFromCode($this->db, (string) $pcgverid, 'accounting_system', 'rowid', 'pcg_version');
		if (empty($pcgvercode)) {
			$pcgvercode = $pcgverid;
		}

		$sql = "SELECT aa.rowid, aa.account_number, aa.label";
		$sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa";
		$sql .= " WHERE aa.rowid NOT IN (";
		$sql .= "   SELECT fk_accounting_account";
		$sql .= "   FROM ".MAIN_DB_PREFIX."accounting_category_account";
		$sql .= "   WHERE fk_accounting_category = ".((int) $this->id);
		$sql .= " )";
		$sql .= " AND aa.entity = ".$conf->entity;
		$sql .= " AND aa.active = 1";
		$sql .= " AND aa.fk_pcg_version = '".$this->db->escape($pcgvercode)."'";
		$sql .= " ORDER BY aa.account_number ASC";

		dol_syslog(get_class($this)."::getAvailableAccountsForCategory", LOG_DEBUG);

		$resql = $this->db->query($sql);
		if ($resql) {
			$num = $this->db->num_rows($resql);
			$i = 0;
			while ($i < $num) {
				$obj = $this->db->fetch_object($resql);
				$accounts[$obj->rowid] = array(
					'id' => $obj->rowid,
					'account_number' => $obj->account_number,
					'label' => $obj->label
				);
				$i++;
			}
			$this->db->free($resql);
			return $accounts;
		} else {
			$this->error = $this->db->lasterror();
			$this->errors[] = $this->error;
			dol_syslog(get_class($this)."::getAvailableAccountsForCategory ".$this->error, LOG_ERR);
			return array();
		}
	}

	/**
	 * Add multiple accounts to this category at once
	 *
	 * @param   array<int>      $accountIds     Array of accounting account IDs
	 * @return  int<-1,max>                     Number of accounts added, -1 if error
	 */
	public function addMultipleAccountsToCategory(array $accountIds)
	{
		$added = 0;
		$error = 0;

		$this->db->begin();

		foreach ($accountIds as $accountId) {
			if (!$this->isAccountInCategory($accountId)) {
				$sql = "INSERT INTO ".MAIN_DB_PREFIX."accounting_category_account";
				$sql .= " (fk_accounting_category, fk_accounting_account)";
				$sql .= " VALUES (".((int) $this->id).", ".((int) $accountId).")";

				$resql = $this->db->query($sql);
				if ($resql) {
					$added++;
				} else {
					$error++;
					$this->error = $this->db->lasterror();
					$this->errors[] = $this->error;
				}
			}
		}

		if ($error) {
			$this->db->rollback();
			return -1;
		} else {
			$this->db->commit();
			return $added;
		}
	}

	/**
	 * Remove all accounts from this category
	 *
	 * @return  int<-1,max>         Number of deleted relations if OK, -1 if error
	 */
	public function removeAllAccountsFromCategory()
	{
		$sql = "DELETE FROM ".MAIN_DB_PREFIX."accounting_category_account";
		$sql .= " WHERE fk_accounting_category = ".((int) $this->id);

		dol_syslog(get_class($this)."::removeAllAccountsFromCategory", LOG_DEBUG);

		$this->db->begin();

		$resql = $this->db->query($sql);
		if ($resql) {
			$affected = $this->db->affected_rows($resql);
			$this->db->commit();
			return $affected;
		} else {
			$this->error = $this->db->lasterror();
			$this->errors[] = $this->error;
			$this->db->rollback();
			return -1;
		}
	}

	/**
	 * Count number of accounts linked to this category
	 *
	 * @return int Number of accounts, <0 if error
	 */
	public function countAccountsInCategory()
	{
		$sql = "SELECT COUNT(*) as nb";
		$sql .= " FROM ".MAIN_DB_PREFIX."accounting_category_account";
		$sql .= " WHERE fk_accounting_category = ".((int) $this->id);

		dol_syslog(get_class($this)."::countAccountsInCategory", LOG_DEBUG);

		$resql = $this->db->query($sql);
		if ($resql) {
			$obj = $this->db->fetch_object($resql);
			return (int) $obj->nb;
		} else {
			$this->error = $this->db->lasterror();
			$this->errors[] = $this->error;
			return -1;
		}
	}
}
