902 lines
26 KiB
PHP
902 lines
26 KiB
PHP
<?php
|
|
/* Copyright (C) 2016 Jamal Elbaz <jamelbaz@gmail.pro>
|
|
* Copyright (C) 2016-2017 Alexandre Spangaro <aspangaro@open-dsi.fr>
|
|
* Copyright (C) 2018-2024 Frédéric France <frederic.france@free.fr>
|
|
*
|
|
* 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 array Lines cptbk
|
|
*/
|
|
public $lines_cptbk;
|
|
|
|
/**
|
|
* @var array Lines display
|
|
*/
|
|
public $lines_display;
|
|
|
|
/**
|
|
* @var mixed Sum debit credit
|
|
*/
|
|
public $sdc;
|
|
|
|
/**
|
|
* @var array Sum debit credit per month
|
|
*/
|
|
public $sdcpermonth;
|
|
|
|
/**
|
|
* @var array 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 ".MAIN_DB_PREFIX."c_accounting_category(";
|
|
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($this->sens)."'").",";
|
|
$sql .= " ".(!isset($this->category_type) ? 'NULL' : "'".$this->db->escape($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 ".MAIN_DB_PREFIX."c_accounting_category 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 ".MAIN_DB_PREFIX."c_accounting_category 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)
|
|
{
|
|
global $conf, $langs;
|
|
$error = 0;
|
|
|
|
$sql = "DELETE FROM ".MAIN_DB_PREFIX."c_accounting_category";
|
|
$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;
|
|
$sql = "SELECT t.rowid, t.account_number, t.label";
|
|
$sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as t";
|
|
$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 ".MAIN_DB_PREFIX."accounting_account as aa";
|
|
$sql .= " INNER JOIN ".MAIN_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 $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 ".MAIN_DB_PREFIX."accounting_account as aa";
|
|
$sql .= " INNER JOIN ".MAIN_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 ".MAIN_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 ".MAIN_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 show result of an accounting account from the ledger with a direction and a period
|
|
*
|
|
* @param int|array $cpt Accounting account or array of accounting account
|
|
* @param string $date_start Date start
|
|
* @param string $date_end Date end
|
|
* @param int $sens Sens of the account: 0: credit - debit (use this by default), 1: debit - credit
|
|
* @param string $thirdparty_code Thirdparty 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();
|
|
|
|
if (is_array($cpt)) {
|
|
$listofaccount = '';
|
|
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 ".MAIN_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($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|integer 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 ".MAIN_DB_PREFIX."accounting_account as t, ".MAIN_DB_PREFIX."c_accounting_category as cat";
|
|
$sql .= " WHERE t.fk_accounting_category IN (SELECT c.rowid";
|
|
$sql .= " FROM ".MAIN_DB_PREFIX."c_accounting_category 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,
|
|
'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
|
|
* @return array<array{code:string,label:string,formula:string,position:string,category_type:string,sens:string,bc:string}>|int Array of groups or -1 if error
|
|
* @see getCatsCpts(), getCptsCat()
|
|
*/
|
|
public function getCats($categorytype = -1, $active = 1)
|
|
{
|
|
global $conf, $mysoc;
|
|
|
|
if (empty($mysoc->country_id)) {
|
|
dol_print_error(null, 'Call to select_accounting_account with mysoc country not yet defined');
|
|
exit();
|
|
}
|
|
|
|
$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position, c.category_type, c.sens";
|
|
$sql .= " FROM ".MAIN_DB_PREFIX."c_accounting_category as c";
|
|
$sql .= " WHERE c.active = " . (int) $active;
|
|
$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,
|
|
'bc' => $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 $predefinedgroupwhere 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 array|int Array of accounting accounts or -1 if error
|
|
* @see getCats(), getCatsCpts()
|
|
*/
|
|
public function getCptsCat($cat_id, $predefinedgroupwhere = '')
|
|
{
|
|
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 ".MAIN_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 ".MAIN_DB_PREFIX."accounting_account as t";
|
|
$sql .= " WHERE ".$predefinedgroupwhere;
|
|
$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;
|
|
}
|
|
}
|
|
}
|