AccountancyCategory::getCatsCal() - Code Metrics - Inspection of "Daily Inspection: Debug report for accounting modu..." - Dolibarr/dolibarr - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Branch — develop (48c526)

unknown

created 2017-09-05 16:08 UTC

AccountancyCategory::getCatsCal() C

↳ Parent: AccountancyCategory

Complexity

Conditions	7
Paths	7

Size

Total Lines	50
Code Lines	39

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	7
eloc	39
nc	7
nop	0
dl	0
loc	50
rs	6.7272
c	0
b	0
f	0

<?php
/* Copyright (C) 2016		Jamal Elbaz			<[email protected]>
 * Copyright (C) 2016-2017	Alexandre Spangaro	<[email protected]>
 *
 * 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 <http://www.gnu.org/licenses/>.
 */

/**
 * \file	htdocs/accountancy/class/accountancycategory.class.php
 * \ingroup Advanced accountancy
 * \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
{
	private $db;
	public $error;
	public $errors = array ();
	public $element = 'accounting_category';
	public $table_element = 'c_accounting_category';
	public $id;
	public $lines_cptbk;
	public $lines_display;
	public $sdc;

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

	/**
	 * Function to select all accounting accounts from an accounting category
	 *
	 * @param int $id Id
	 *
	 * @return int <0 if KO, 0 if not found, >0 if OK
	 */
	public function display($id) {
		$sql = "SELECT t.rowid, t.account_number, t.label";
		$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as t";
		$sql .= " WHERE t.fk_accounting_category = " . $id;

		$this->lines_display = array ();

		dol_syslog(__METHOD__ . " sql=" . $sql, 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 select accounting category of an accounting account present in chart of accounts
	 *
	 * @param int $id Id category
	 *
	 * @return int <0 if KO, 0 if not found, >0 if OK
	 */
	public function getCptBK($id) {
		global $conf;

		$sql = "SELECT t.numero_compte, t.label_operation, t.doc_ref";
		$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as t";
		$sql .= " WHERE t.numero_compte NOT IN (";
		$sql .= " SELECT t.account_number";
		$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as t";
		$sql .= " WHERE t.fk_accounting_category = " . $id . ")";
		$sql .= " AND t.numero_compte IN (";
		$sql .= " SELECT DISTINCT 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 = " . $conf->global->CHARTOFACCOUNTS;
		$sql .= " AND aa.active = 1)";
		$sql .= " GROUP BY t.numero_compte, t.label_operation, t.doc_ref";
		$sql .= " ORDER BY t.numero_compte";

		$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 select accounting category of an accounting account present in chart of accounts
	 *
	 * @param int $id Id category
	 *
	 * @return int <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 != ".$id." OR aa.fk_accounting_category IS NULL)";
	    $sql .= " AND asy.rowid = " . $conf->global->CHARTOFACCOUNTS;
	    $sql .= " AND aa.active = 1";
	    $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 <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 = " . $conf->global->CHARTOFACCOUNTS;
		$sql .= " AND aa.active = 1";

		$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;
		}

		while ( $obj = $this->db->fetch_object($resql))
		{
			if (array_key_exists(length_accountg($obj->account_number), $cpts))
			{
				$sql = "UPDATE " . MAIN_DB_PREFIX . "accounting_account";
				$sql .= " SET fk_accounting_category=" . $id_cat;
				$sql .= " WHERE rowid=".$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 <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= " . $cpt_id;
		$this->db->begin();

		dol_syslog(__METHOD__ . " sql=" . $sql, 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 know all category from accounting account
	 *
	 * @return array       Result in table
	 */
	public function getCatsCpts() {
		global $mysoc;
		$sql = "";

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

		if (! empty($mysoc->country_id)) {
			$sql = "SELECT t.rowid, t.account_number, t.label as account_label, cat.code, cat.position, cat.label as name_cat, cat.sens ";
			$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.fk_country = " . $mysoc->country_id . ")";
			$sql .= " AND cat.rowid = t.fk_accounting_category";
			$sql .= " ORDER BY cat.position ASC";
		} else {
			$sql = "SELECT c.rowid, c.code, c.label, c.category_type ";
			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c, " . MAIN_DB_PREFIX . "c_country as co";
			$sql .= " WHERE c.active = 1 AND c.fk_country = co.rowid";
			$sql .= " AND co.code = '" . $mysoc->country_code . "'";
			$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 ( $obj = $this->db->fetch_object($resql) ) {
					$name_cat = $obj->name_cat;
					$data[$name_cat][$i] = array (
							'id' => $obj->rowid,
							'code' => $obj->code,
							'position' => $obj->position,
							'account_number' => $obj->account_number,
							'account_label' => $obj->account_label,
							'sens' => $obj->sens
					);
					$i ++;
				}
			}
			return $data;
		} else {
			$this->error = "Error " . $this->db->lasterror();
			dol_syslog(__METHOD__ . " " . $this->error, LOG_ERR);

			return -1;
		}
	}

	/**
	 * Function to show result of an accounting account from the ledger with a direction and a period
	 *
	 * @param int 		$cpt 				Id accounting account
	 * @param string 	$month 				Specifig month - Can be empty
	 * @param string 	$date_start			Date start
	 * @param string 	$date_end			Date end
	 * @param int 		$sens 				Sens of the account:  0: credit - debit, 1: debit - credit
	 * @param string	$thirdparty_code	Thirdparty code
	 * @return integer 						Result in table
	 */
	public function getResult($cpt, $month, $date_start, $date_end, $sens, $thirdparty_code='nofilter')
	{
		$sql = "SELECT SUM(t.debit) as debit, SUM(t.credit) as credit";
		$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as t";
		$sql .= " WHERE t.numero_compte = '" . $cpt."'";
		if (! empty($date_start) && ! empty($date_end))
			$sql.= " AND t.doc_date >= '".$this->db->idate($date_start)."' AND t.doc_date <= '".$this->db->idate($date_end)."'";
		if (! empty($month)) {
			$sql .= " AND MONTH(t.doc_date) = " . $month;
		}
		if ($thirdparty_code != 'nofilter')
		{
			$sql .= " AND thirdparty_code = '".$ths->db->escape($thirdparty_code)."'";

		}

		dol_syslog(__METHOD__ . " sql=" . $sql, LOG_DEBUG);
		$resql = $this->db->query($sql);

		if ($resql) {
			$num = $this->db->num_rows($resql);
			$this->sdc = 0;
			if ($num) {
				$obj = $this->db->fetch_object($resql);
				if ($sens == 1) {
					$this->sdc = $obj->debit - $obj->credit;
				} else {
					$this->sdc = $obj->credit - $obj->debit;
				}
			}
			return $num;
		} else {
			$this->error = "Error " . $this->db->lasterror();
			dol_syslog(__METHOD__ . " " . $this->error, LOG_ERR);

			return - 1;
		}
	}

	/**
	 * Return list of personalized groups
	 *
	 * @return	int			$categorytype		-1=All, 0=Only non computed groups, 1=Only computed groups
	 * @return	array|int						Array of personalized groups or < 0 if KO
	 */
	public function getCats($categorytype=-1)
	{
		global $db, $langs, $user, $mysoc;

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

		if (! empty($mysoc->country_id)) {
			$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position, c.category_type";
			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c";
			$sql .= " WHERE c.active = 1 ";
			if ($categorytype >= 0) $sql.=" AND c.category_type = 1";
			$sql .= " AND c.fk_country = " . $mysoc->country_id;
			$sql .= " ORDER BY c.position ASC";
		} else {	// Note: this should not happen
			$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position, c.category_type";
			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c, " . MAIN_DB_PREFIX . "c_country as co";
			$sql .= " WHERE c.active = 1 AND c.fk_country = co.rowid";
			if ($categorytype >= 0) $sql.=" AND c.category_type = 1";
			$sql .= " AND co.code = '" . $mysoc->country_code . "'";
			$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,
							'formula' => $obj->formula,
							'position' => $obj->position,
							'category_type' => $obj->category_type
					);
					$i ++;
				}
			}
			return $data;
		} else {
			$this->error = "Error " . $this->db->lasterror();
			$this->errors[] = $this->error;
			dol_syslog(__METHOD__ . " " . implode(',', $this->errors), LOG_ERR);

			return - 1;
		}
	}


	// calcule

	/* I try to replace this with dol_eval()

	const PATTERN = '/(?:\-?\d+(?:\.?\d+)?[\+\-\*\/])+\-?\d+(?:\.?\d+)?/';

	const PARENTHESIS_DEPTH = 10;

	public function calculate($input)
	{
		global $langs;

		if(strpos($input, '+') != null || strpos($input, '-') != null || strpos($input, '/') != null || strpos($input, '*') != null){
			//  Remove white spaces and invalid math chars
			$input = str_replace($langs->trans("ThousandSeparator"), '', $input);
			$input = str_replace(',', '.', $input);
			$input = preg_replace('[^0-9\.\+\-\*\/\(\)]', '', $input);

			//  Calculate each of the parenthesis from the top
			$i = 0;
			while(strpos($input, '(') || strpos($input, ')')){
				$input = preg_replace_callback('/\(([^\(\)]+)\)/', 'self::callback', $input);

				$i++;
				if($i > self::PARENTHESIS_DEPTH){
					break;
				}
			}

			//  Calculate the result
			if(preg_match(self::PATTERN, $input, $match)){
				return $this->compute($match[0]);
			}

			return 0;
		}

		return $input;
	}

	private function compute($input){
		$compute = create_function('', 'return '.$input.';');

		return 0 + $compute();
	}

	private function callback($input){
		if(is_numeric($input[1])){
			return $input[1];
		}
		elseif(preg_match(self::PATTERN, $input[1], $match)){
			return $this->compute($match[0]);
		}

		return 0;
	}
	*/


	/**
	 * Get all accounting account of a group.
	 * 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 	$predefinedgroup 	Sql criteria filter to select accounting accounts
	 * @return 	array       				Array of accounting accounts
	 */
	public function getCptsCat($cat_id, $predefinedgroupwhere='')
	{
		global $mysoc;
		$sql = '';

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

		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 = ".$cat_id;
			$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 .= " ORDER BY t.account_number ";
		}
		//echo $sql;

		$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))
				{
					$name_cat = $obj->name_cat;
					$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;
		}
	}

}


1			<?php
2			/* Copyright (C) 2016 Jamal Elbaz <[email protected]>
3			* Copyright (C) 2016-2017 Alexandre Spangaro <[email protected]>
4			*
5			* This program is free software; you can redistribute it and/or modify
6			* it under the terms of the GNU General Public License as published by
7			* the Free Software Foundation; either version 3 of the License, or
8			* (at your option) any later version.
9			*
10			* This program is distributed in the hope that it will be useful,
11			* but WITHOUT ANY WARRANTY; without even the implied warranty of
12			* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13			* GNU General Public License for more details.
14			*
15			* You should have received a copy of the GNU General Public License
16			* along with this program. If not, see <http://www.gnu.org/licenses/>.
17			*/
18
19			/**
20			* \file htdocs/accountancy/class/accountancycategory.class.php
21			* \ingroup Advanced accountancy
22			* \brief File of class to manage categories of an accounting category_type
23			*/
24
25			// Class
26			require_once DOL_DOCUMENT_ROOT . '/core/lib/accounting.lib.php';
27
28			/**
29			* Class to manage categories of an accounting account
30			*/
31			class AccountancyCategory
32			{
33			private $db;
34			public $error;
35			public $errors = array ();
36			public $element = 'accounting_category';
37			public $table_element = 'c_accounting_category';
38			public $id;
39			public $lines_cptbk;
40			public $lines_display;
41			public $sdc;
42
43			/**
44			* Constructor
45			*
46			* @param DoliDB $db Database handler
47			*/
48			public function __construct($db) {
49			$this->db = $db;
50			}
51
52			/**
53			* Function to select all accounting accounts from an accounting category
54			*
55			* @param int $id Id
56			*
57			* @return int <0 if KO, 0 if not found, >0 if OK
58			*/
59			public function display($id) {
60			$sql = "SELECT t.rowid, t.account_number, t.label";
61			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as t";
62			$sql .= " WHERE t.fk_accounting_category = " . $id;
63
64			$this->lines_display = array ();
65
66			dol_syslog(__METHOD__ . " sql=" . $sql, LOG_DEBUG);
67			$resql = $this->db->query($sql);
68			if ($resql) {
69			$num = $this->db->num_rows($resql);
70			if ($num) {
71			while ( $obj = $this->db->fetch_object($resql) ) {
72			$this->lines_display[] = $obj;
73			}
74			}
75			return $num;
76			} else {
77			$this->error = "Error " . $this->db->lasterror();
78			$this->errors[] = $this->error;
79			dol_syslog(__METHOD__ . " " . implode(',' . $this->errors), LOG_ERR);
80
81			return - 1;
82			}
83			}
84
85			/**
86			* Function to select accounting category of an accounting account present in chart of accounts
87			*
88			* @param int $id Id category
89			*
90			* @return int <0 if KO, 0 if not found, >0 if OK
91			*/
92			public function getCptBK($id) {
93			global $conf;
94
95			$sql = "SELECT t.numero_compte, t.label_operation, t.doc_ref";
96			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as t";
97			$sql .= " WHERE t.numero_compte NOT IN (";
98			$sql .= " SELECT t.account_number";
99			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as t";
100			$sql .= " WHERE t.fk_accounting_category = " . $id . ")";
101			$sql .= " AND t.numero_compte IN (";
102			$sql .= " SELECT DISTINCT aa.account_number";
103			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as aa";
104			$sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
105			$sql .= " AND asy.rowid = " . $conf->global->CHARTOFACCOUNTS;
106			$sql .= " AND aa.active = 1)";
107			$sql .= " GROUP BY t.numero_compte, t.label_operation, t.doc_ref";
108			$sql .= " ORDER BY t.numero_compte";
109
110			$this->lines_CptBk = array ();
111
112			dol_syslog(__METHOD__, LOG_DEBUG);
113			$resql = $this->db->query($sql);
114			if ($resql) {
115			$num = $this->db->num_rows($resql);
116			if ($num) {
117			while ( $obj = $this->db->fetch_object($resql) ) {
118			$this->lines_cptbk[] = $obj;
119			}
120			}
121
122			return $num;
123			} else {
124			$this->error = "Error " . $this->db->lasterror();
125			$this->errors[] = $this->error;
126			dol_syslog(__METHOD__ . " " . implode(',' . $this->errors), LOG_ERR);
127
128			return - 1;
129			}
130			}
131
132			/**
133			* Function to select accounting category of an accounting account present in chart of accounts
134			*
135			* @param int $id Id category
136			*
137			* @return int <0 if KO, 0 if not found, >0 if OK
138			*/
139			public function getAccountsWithNoCategory($id) {
140			global $conf;
141
142			$sql = "SELECT aa.account_number as numero_compte, aa.label as label_compte";
143			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as aa";
144			$sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
145			$sql .= " WHERE (aa.fk_accounting_category != ".$id." OR aa.fk_accounting_category IS NULL)";
146			$sql .= " AND asy.rowid = " . $conf->global->CHARTOFACCOUNTS;
147			$sql .= " AND aa.active = 1";
148			$sql .= " GROUP BY aa.account_number, aa.label";
149			$sql .= " ORDER BY aa.account_number, aa.label";
150
151			$this->lines_CptBk = array ();
152
153			dol_syslog(__METHOD__, LOG_DEBUG);
154			$resql = $this->db->query($sql);
155			if ($resql) {
156			$num = $this->db->num_rows($resql);
157			if ($num) {
158			while ( $obj = $this->db->fetch_object($resql) ) {
159			$this->lines_cptbk[] = $obj;
160			}
161			}
162
163			return $num;
164			} else {
165			$this->error = "Error " . $this->db->lasterror();
166			$this->errors[] = $this->error;
167			dol_syslog(__METHOD__ . " " . implode(',' . $this->errors), LOG_ERR);
168
169			return - 1;
170			}
171			}
172
173			/**
174			* Function to add an accounting account in an accounting category
175			*
176			* @param int $id_cat Id category
177			* @param array $cpts list of accounts array
178			*
179			* @return int <0 if KO, >0 if OK
180			*/
181			public function updateAccAcc($id_cat, $cpts = array()) {
182			global $conf;
183			$error = 0;
184
185			require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
186
187			$sql = "SELECT aa.rowid,aa.account_number ";
188			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as aa";
189			$sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
190			$sql .= " AND asy.rowid = " . $conf->global->CHARTOFACCOUNTS;
191			$sql .= " AND aa.active = 1";
192
193			$this->db->begin();
194
195			dol_syslog(__METHOD__, LOG_DEBUG);
196			$resql = $this->db->query($sql);
197			if (! $resql) {
198			$error ++;
199			$this->errors[] = "Error " . $this->db->lasterror();
200			$this->db->rollback();
201			return -1;
202			}
203
204			while ( $obj = $this->db->fetch_object($resql))
205			{
206			if (array_key_exists(length_accountg($obj->account_number), $cpts))
207			{
208			$sql = "UPDATE " . MAIN_DB_PREFIX . "accounting_account";
209			$sql .= " SET fk_accounting_category=" . $id_cat;
210			$sql .= " WHERE rowid=".$obj->rowid;
211			dol_syslog(__METHOD__, LOG_DEBUG);
212			$resqlupdate = $this->db->query($sql);
213			if (! $resqlupdate) {
214			$error ++;
215			$this->errors[] = "Error " . $this->db->lasterror();
216			}
217			}
218			}
219
220			// Commit or rollback
221			if ($error) {
222			foreach ( $this->errors as $errmsg ) {
223			dol_syslog(__METHOD__ . " " . $errmsg, LOG_ERR);
224			$this->error .= ($this->error ? ', ' . $errmsg : $errmsg);
225			}
226			$this->db->rollback();
227
228			return - 1 * $error;
229			} else {
230			$this->db->commit();
231
232			return 1;
233			}
234			}
235
236			/**
237			* Function to delete an accounting account from an accounting category
238			*
239			* @param int $cpt_id Id of accounting account
240			*
241			* @return int <0 if KO, >0 if OK
242			*/
243			public function deleteCptCat($cpt_id) {
244			$error = 0;
245
246			$sql = "UPDATE " . MAIN_DB_PREFIX . "accounting_account as aa";
247			$sql .= " SET fk_accounting_category= 0";
248			$sql .= " WHERE aa.rowid= " . $cpt_id;
249			$this->db->begin();
250
251			dol_syslog(__METHOD__ . " sql=" . $sql, LOG_DEBUG);
252			$resql = $this->db->query($sql);
253			if (! $resql) {
254			$error ++;
255			$this->errors[] = "Error " . $this->db->lasterror();
256			}
257
258			// Commit or rollback
259			if ($error) {
260			foreach ( $this->errors as $errmsg ) {
261			dol_syslog(__METHOD__ . " " . $errmsg, LOG_ERR);
262			$this->error .= ($this->error ? ', ' . $errmsg : $errmsg);
263			}
264			$this->db->rollback();
265
266			return - 1 * $error;
267			} else {
268			$this->db->commit();
269
270			return 1;
271			}
272			}
273
274			/**
275			* Function to know all category from accounting account
276			*
277			* @return array Result in table
278			*/
279			public function getCatsCpts() {
280			global $mysoc;
281			$sql = "";
282
283			if (empty($mysoc->country_id) && empty($mysoc->country_code)) {
284			dol_print_error('', 'Call to select_accounting_account with mysoc country not yet defined');
285			exit();
286			}
287
288			if (! empty($mysoc->country_id)) {
289			$sql = "SELECT t.rowid, t.account_number, t.label as account_label, cat.code, cat.position, cat.label as name_cat, cat.sens ";
290			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as t, " . MAIN_DB_PREFIX . "c_accounting_category as cat";
291			$sql .= " WHERE t.fk_accounting_category IN ( SELECT c.rowid ";
292			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c";
293			$sql .= " WHERE c.active = 1";
294			$sql .= " AND c.fk_country = " . $mysoc->country_id . ")";
295			$sql .= " AND cat.rowid = t.fk_accounting_category";
296			$sql .= " ORDER BY cat.position ASC";
297			} else {
298			$sql = "SELECT c.rowid, c.code, c.label, c.category_type ";
299			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c, " . MAIN_DB_PREFIX . "c_country as co";
300			$sql .= " WHERE c.active = 1 AND c.fk_country = co.rowid";
301			$sql .= " AND co.code = '" . $mysoc->country_code . "'";
302			$sql .= " ORDER BY c.position ASC";
303			}
304
305			$resql = $this->db->query($sql);
306			if ($resql) {
307			$i = 0;
308			$obj = '';
309			$num = $this->db->num_rows($resql);
310			$data = array ();
311			if ($num) {
312			while ( $obj = $this->db->fetch_object($resql) ) {
313			$name_cat = $obj->name_cat;
314			$data[$name_cat][$i] = array (
315			'id' => $obj->rowid,
316			'code' => $obj->code,
317			'position' => $obj->position,
318			'account_number' => $obj->account_number,
319			'account_label' => $obj->account_label,
320			'sens' => $obj->sens
321			);
322			$i ++;
323			}
324			}
325			return $data;
326			} else {
327			$this->error = "Error " . $this->db->lasterror();
328			dol_syslog(__METHOD__ . " " . $this->error, LOG_ERR);
329
330			return -1;
331			}
332			}
333
334			/**
335			* Function to show result of an accounting account from the ledger with a direction and a period
336			*
337			* @param int $cpt Id accounting account
338			* @param string $month Specifig month - Can be empty
339			* @param string $date_start Date start
340			* @param string $date_end Date end
341			* @param int $sens Sens of the account: 0: credit - debit, 1: debit - credit
342			* @param string $thirdparty_code Thirdparty code
343			* @return integer Result in table
344			*/
345			public function getResult($cpt, $month, $date_start, $date_end, $sens, $thirdparty_code='nofilter')
346			{
347			$sql = "SELECT SUM(t.debit) as debit, SUM(t.credit) as credit";
348			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as t";
349			$sql .= " WHERE t.numero_compte = '" . $cpt."'";
350			if (! empty($date_start) && ! empty($date_end))
351			$sql.= " AND t.doc_date >= '".$this->db->idate($date_start)."' AND t.doc_date <= '".$this->db->idate($date_end)."'";
352			if (! empty($month)) {
353			$sql .= " AND MONTH(t.doc_date) = " . $month;
354			}
355			if ($thirdparty_code != 'nofilter')
356			{
357			$sql .= " AND thirdparty_code = '".$ths->db->escape($thirdparty_code)."'";
			0 ignored issues – show Bug introduced 2017-09-05 16:38 UTC by Report Bug Copy Issue Report The variable `$ths` does not exist. Did you forget to declare it? This check marks access to variables or properties that have not been declared yet. While PHP has no explicit notion of declaring a variable, accessing it before a value is assigned to it is most likely a bug. Loading history...
358			}
359
360			dol_syslog(__METHOD__ . " sql=" . $sql, LOG_DEBUG);
361			$resql = $this->db->query($sql);
362
363			if ($resql) {
364			$num = $this->db->num_rows($resql);
365			$this->sdc = 0;
366			if ($num) {
367			$obj = $this->db->fetch_object($resql);
368			if ($sens == 1) {
369			$this->sdc = $obj->debit - $obj->credit;
370			} else {
371			$this->sdc = $obj->credit - $obj->debit;
372			}
373			}
374			return $num;
375			} else {
376			$this->error = "Error " . $this->db->lasterror();
377			dol_syslog(__METHOD__ . " " . $this->error, LOG_ERR);
378
379			return - 1;
380			}
381			}
382
383			/**
384			* Return list of personalized groups
385			*
386			* @return int $categorytype -1=All, 0=Only non computed groups, 1=Only computed groups
387			* @return array\|int Array of personalized groups or < 0 if KO
388			*/
389			public function getCats($categorytype=-1)
390			{
391			global $db, $langs, $user, $mysoc;
392
393			if (empty($mysoc->country_id) && empty($mysoc->country_code)) {
394			dol_print_error('', 'Call to select_accounting_account with mysoc country not yet defined');
395			exit();
396			}
397
398			if (! empty($mysoc->country_id)) {
399			$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position, c.category_type";
400			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c";
401			$sql .= " WHERE c.active = 1 ";
402			if ($categorytype >= 0) $sql.=" AND c.category_type = 1";
403			$sql .= " AND c.fk_country = " . $mysoc->country_id;
404			$sql .= " ORDER BY c.position ASC";
405			} else { // Note: this should not happen
406			$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position, c.category_type";
407			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c, " . MAIN_DB_PREFIX . "c_country as co";
408			$sql .= " WHERE c.active = 1 AND c.fk_country = co.rowid";
409			if ($categorytype >= 0) $sql.=" AND c.category_type = 1";
410			$sql .= " AND co.code = '" . $mysoc->country_code . "'";
411			$sql .= " ORDER BY c.position ASC";
412			}
413
414			$resql = $this->db->query($sql);
415			if ($resql) {
416			$i = 0;
417			$obj = '';
418			$num = $this->db->num_rows($resql);
419			$data = array ();
420			if ($num) {
421			while ( $i < $num ) {
422			$obj = $this->db->fetch_object($resql);
423
424			$data[] = array (
425			'rowid' => $obj->rowid,
426			'code' => $obj->code,
427			'label' => $obj->label,
428			'formula' => $obj->formula,
429			'position' => $obj->position,
430			'category_type' => $obj->category_type
431			);
432			$i ++;
433			}
434			}
435			return $data;
436			} else {
437			$this->error = "Error " . $this->db->lasterror();
438			$this->errors[] = $this->error;
439			dol_syslog(__METHOD__ . " " . implode(',', $this->errors), LOG_ERR);
440
441			return - 1;
442			}
443			}
444
445
446			// calcule
447
448			/* I try to replace this with dol_eval()
449
450			const PATTERN = '/(?:\-?\d+(?:\.?\d+)?[\+\-\*\/])+\-?\d+(?:\.?\d+)?/';
451
452			const PARENTHESIS_DEPTH = 10;
453
454			public function calculate($input)
455			{
456			global $langs;
457
458			if(strpos($input, '+') != null \|\| strpos($input, '-') != null \|\| strpos($input, '/') != null \|\| strpos($input, '*') != null){
459			// Remove white spaces and invalid math chars
460			$input = str_replace($langs->trans("ThousandSeparator"), '', $input);
461			$input = str_replace(',', '.', $input);
462			$input = preg_replace('[^0-9\.\+\-\*\/\(\)]', '', $input);
463
464			// Calculate each of the parenthesis from the top
465			$i = 0;
466			while(strpos($input, '(') \|\| strpos($input, ')')){
467			$input = preg_replace_callback('/\(([^\(\)]+)\)/', 'self::callback', $input);
468
469			$i++;
470			if($i > self::PARENTHESIS_DEPTH){
471			break;
472			}
473			}
474
475			// Calculate the result
476			if(preg_match(self::PATTERN, $input, $match)){
477			return $this->compute($match[0]);
478			}
479
480			return 0;
481			}
482
483			return $input;
484			}
485
486			private function compute($input){
487			$compute = create_function('', 'return '.$input.';');
488
489			return 0 + $compute();
490			}
491
492			private function callback($input){
493			if(is_numeric($input[1])){
494			return $input[1];
495			}
496			elseif(preg_match(self::PATTERN, $input[1], $match)){
497			return $this->compute($match[0]);
498			}
499
500			return 0;
501			}
502			*/
503
504
505			/**
506			* Get all accounting account of a group.
507			* You must choose between first parameter (personalized group) or the second (free criteria filter)
508			*
509			* @param int $cat_id Id if personalized accounting group/category
510			* @param string $predefinedgroup Sql criteria filter to select accounting accounts
511			* @return array Array of accounting accounts
512			*/
513			public function getCptsCat($cat_id, $predefinedgroupwhere='')
514			{
515			global $mysoc;
516			$sql = '';
517
518			if (empty($mysoc->country_id) && empty($mysoc->country_code)) {
519			dol_print_error('', 'Call to select_accounting_account with mysoc country not yet defined');
520			exit();
521			}
522
523			if (! empty($cat_id))
524			{
525			$sql = "SELECT t.rowid, t.account_number, t.label as account_label";
526			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as t";
527			$sql .= " WHERE t.fk_accounting_category = ".$cat_id;
528			$sql .= " ORDER BY t.account_number ";
529			}
530			else
531			{
532			$sql = "SELECT t.rowid, t.account_number, t.label as account_label";
533			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as t";
534			$sql .= " WHERE ".$predefinedgroupwhere;
535			$sql .= " ORDER BY t.account_number ";
536			}
537			//echo $sql;
538
539			$resql = $this->db->query($sql);
540			if ($resql) {
541			$i = 0;
542			$obj = '';
543			$num = $this->db->num_rows($resql);
544			$data = array();
545			if ($num) {
546			while ($obj = $this->db->fetch_object($resql))
547			{
548			$name_cat = $obj->name_cat;
549			$data[] = array (
550			'id' => $obj->rowid,
551			'account_number' => $obj->account_number,
552			'account_label' => $obj->account_label,
553			);
554			$i ++;
555			}
556			}
557			return $data;
558			} else {
559			$this->error = "Error " . $this->db->lasterror();
560			dol_syslog(__METHOD__ . " " . $this->error, LOG_ERR);
561
562			return -1;
563			}
564			}
565
566			}
567

Dolibarr / dolibarr

Branch — develop (48c526)

AccountancyCategory::getCatsCal() C

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like