AccountancyCategory::getResult() - Code Metrics - Inspection of "Daily Inspection: Fix payment suffix" - Dolibarr/dolibarr - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Branch — develop (30eefb)

unknown

created 2017-09-01 16:06 UTC

AccountancyCategory::getResult() C

↳ Parent: AccountancyCategory

Complexity

Conditions	7
Paths	16

Size

Total Lines	33
Code Lines	24

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	7
eloc	24
nc	16
nop	5
dl	0
loc	33
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 name_cpt, 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,
							'name_cpt' => $obj->name_cpt,
							'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
	 * @return integer 				Result in table
	 */
	public function getResult($cpt, $month, $date_start, $date_end, $sens)
	{
		$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;
		}

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

	/**
	 * Function to call category from a specific country
	 *
	 * @return array Result in table
	 */
	public function getCatsCal() {
		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";
			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c";
			$sql .= " WHERE c.active = 1 AND c.category_type = 1 ";
			$sql .= " AND c.fk_country = " . $mysoc->country_id;
			$sql .= " ORDER BY c.position ASC";
		} else {
			$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position";
			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c, " . MAIN_DB_PREFIX . "c_country as co";
			$sql .= " WHERE c.active = 1 AND c.category_type = 1 AND c.fk_country = co.rowid";
			$sql .= " AND co.code = '" . $mysoc->country_code . "'";
			$sql .= " ORDER BY c.position ASC";
		}

		dol_syslog(__METHOD__ . " sql=" . $sql, LOG_DEBUG);
		$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);
					$position = $obj->position;
					$data[$position] = array (
							'code' => $obj->code,
							'label' => $obj->label,
							'formula' => $obj->formula
					);
					$i ++;
				}
			}
			return $data;
		} else {
			$this->error = "Error " . $this->db->lasterror();
			$this->errors[] = $this->error;
			dol_syslog(__METHOD__ . " " . implode(',', $this->errors), LOG_ERR);

			return - 1;
		}
	}

	public function getCats() {
		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 ";
			$sql .= " AND c.fk_country = " . $mysoc->country_id;
			$sql .= " ORDER BY c.position ASC";
		} else {
			$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";
			$sql .= " AND co.code = '" . $mysoc->country_code . "'";
			$sql .= " ORDER BY c.position ASC";
		}

		dol_syslog(__METHOD__ . " sql=" . $sql, LOG_DEBUG);
		$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,
							'position' => $obj->position,
							'label' => $obj->label,
							'formula' => $obj->formula,
							'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

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

	const PARENTHESIS_DEPTH = 10;

	public function calculate($input){
		if(strpos($input, '+') != null || strpos($input, '-') != null || strpos($input, '/') != null || strpos($input, '*') != null){
			//  Remove white spaces and invalid math chars
			$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 cpts of category
	 *
	 * @param int $cat_id Id accounting account category
	 *
	 * @return array       Result in table
	 */
	public function getCptsCat($cat_id) {
		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();
		}

		$sql = "SELECT t.rowid, t.account_number, t.label as name_cpt";
		$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as t";
		$sql .= " WHERE t.fk_accounting_category = ".$cat_id;
		$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,
							'name_cpt' => $obj->name_cpt,
					);
					$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 name_cpt, 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			'name_cpt' => $obj->name_cpt,
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			* @return integer Result in table
343			*/
344			public function getResult($cpt, $month, $date_start, $date_end, $sens)
345			{
346			$sql = "SELECT SUM(t.debit) as debit, SUM(t.credit) as credit";
347			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as t";
348			$sql .= " WHERE t.numero_compte = '" . $cpt."'";
349			if (! empty($date_start) && ! empty($date_end))
350			$sql.= " AND t.doc_date >= '".$this->db->idate($date_start)."' AND t.doc_date <= '".$this->db->idate($date_end)."'";
351			if (! empty($month)) {
352			$sql .= " AND MONTH(t.doc_date) = " . $month;
353			}
354
355			dol_syslog(__METHOD__ . " sql=" . $sql, LOG_DEBUG);
356			$resql = $this->db->query($sql);
357
358			if ($resql) {
359			$num = $this->db->num_rows($resql);
360			$this->sdc = 0;
361			if ($num) {
362			$obj = $this->db->fetch_object($resql);
363			if ($sens == 1) {
364			$this->sdc = $obj->debit - $obj->credit;
365			} else {
366			$this->sdc = $obj->credit - $obj->debit;
367			}
368			}
369			return $num;
370			} else {
371			$this->error = "Error " . $this->db->lasterror();
372			dol_syslog(__METHOD__ . " " . $this->error, LOG_ERR);
373
374			return - 1;
375			}
376			}
377
378			/**
379			* Function to call category from a specific country
380			*
381			* @return array Result in table
382			*/
383			public function getCatsCal() {
384			global $db, $langs, $user, $mysoc;
385
386			if (empty($mysoc->country_id) && empty($mysoc->country_code)) {
387			dol_print_error('', 'Call to select_accounting_account with mysoc country not yet defined');
388			exit();
389			}
390
391			if (! empty($mysoc->country_id)) {
392			$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position";
393			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c";
394			$sql .= " WHERE c.active = 1 AND c.category_type = 1 ";
395			$sql .= " AND c.fk_country = " . $mysoc->country_id;
396			$sql .= " ORDER BY c.position ASC";
397			} else {
398			$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position";
399			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c, " . MAIN_DB_PREFIX . "c_country as co";
400			$sql .= " WHERE c.active = 1 AND c.category_type = 1 AND c.fk_country = co.rowid";
401			$sql .= " AND co.code = '" . $mysoc->country_code . "'";
402			$sql .= " ORDER BY c.position ASC";
403			}
404
405			dol_syslog(__METHOD__ . " sql=" . $sql, LOG_DEBUG);
406			$resql = $this->db->query($sql);
407			if ($resql) {
408			$i = 0;
409			$obj = '';
410			$num = $this->db->num_rows($resql);
411			$data = array ();
412			if ($num) {
413			while ( $i < $num ) {
414			$obj = $this->db->fetch_object($resql);
415			$position = $obj->position;
416			$data[$position] = array (
417			'code' => $obj->code,
418			'label' => $obj->label,
419			'formula' => $obj->formula
420			);
421			$i ++;
422			}
423			}
424			return $data;
425			} else {
426			$this->error = "Error " . $this->db->lasterror();
427			$this->errors[] = $this->error;
428			dol_syslog(__METHOD__ . " " . implode(',', $this->errors), LOG_ERR);
429
430			return - 1;
431			}
432			}
433
434			public function getCats() {
435			global $db, $langs, $user, $mysoc;
436
437			if (empty($mysoc->country_id) && empty($mysoc->country_code)) {
438			dol_print_error('', 'Call to select_accounting_account with mysoc country not yet defined');
439			exit();
440			}
441
442			if (! empty($mysoc->country_id)) {
443			$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position, c.category_type";
444			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c";
445			$sql .= " WHERE c.active = 1 ";
446			$sql .= " AND c.fk_country = " . $mysoc->country_id;
447			$sql .= " ORDER BY c.position ASC";
448			} else {
449			$sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position, c.category_type";
450			$sql .= " FROM " . MAIN_DB_PREFIX . "c_accounting_category as c, " . MAIN_DB_PREFIX . "c_country as co";
451			$sql .= " WHERE c.active = 1 AND c.fk_country = co.rowid";
452			$sql .= " AND co.code = '" . $mysoc->country_code . "'";
453			$sql .= " ORDER BY c.position ASC";
454			}
455
456			dol_syslog(__METHOD__ . " sql=" . $sql, LOG_DEBUG);
457			$resql = $this->db->query($sql);
458			if ($resql) {
459			$i = 0;
460			$obj = '';
461			$num = $this->db->num_rows($resql);
462			$data = array ();
463			if ($num) {
464			while ( $i < $num ) {
465			$obj = $this->db->fetch_object($resql);
466
467			$data[] = array (
468			'rowid' => $obj->rowid,
469			'code' => $obj->code,
470			'position' => $obj->position,
471			'label' => $obj->label,
472			'formula' => $obj->formula,
473			'category_type' => $obj->category_type
474			);
475			$i ++;
476			}
477			}
478			return $data;
479			} else {
480			$this->error = "Error " . $this->db->lasterror();
481			$this->errors[] = $this->error;
482			dol_syslog(__METHOD__ . " " . implode(',', $this->errors), LOG_ERR);
483
484			return - 1;
485			}
486			}
487
488
489			// calcule
490
491			const PATTERN = '/(?:\-?\d+(?:\.?\d+)?[\+\-\*\/])+\-?\d+(?:\.?\d+)?/';
492
493			const PARENTHESIS_DEPTH = 10;
494
495			public function calculate($input){
496			if(strpos($input, '+') != null \|\| strpos($input, '-') != null \|\| strpos($input, '/') != null \|\| strpos($input, '*') != null){
497			// Remove white spaces and invalid math chars
498			$input = str_replace(',', '.', $input);
499			$input = preg_replace('[^0-9\.\+\-\*\/\(\)]', '', $input);
500
501			// Calculate each of the parenthesis from the top
502			$i = 0;
503			while(strpos($input, '(') \|\| strpos($input, ')')){
504			$input = preg_replace_callback('/\(([^\(\)]+)\)/', 'self::callback', $input);
505
506			$i++;
507			if($i > self::PARENTHESIS_DEPTH){
508			break;
509			}
510			}
511
512			// Calculate the result
513			if(preg_match(self::PATTERN, $input, $match)){
514			return $this->compute($match[0]);
515			}
516
517			return 0;
518			}
519
520			return $input;
521			}
522
523			private function compute($input){
524			$compute = create_function('', 'return '.$input.';');
525
526			return 0 + $compute();
527			}
528
529			private function callback($input){
530			if(is_numeric($input[1])){
531			return $input[1];
532			}
533			elseif(preg_match(self::PATTERN, $input[1], $match)){
534			return $this->compute($match[0]);
535			}
536
537			return 0;
538			}
539
540			/**
541			* get cpts of category
542			*
543			* @param int $cat_id Id accounting account category
544			*
545			* @return array Result in table
546			*/
547			public function getCptsCat($cat_id) {
548			global $mysoc;
549			$sql = "";
550
551			if (empty($mysoc->country_id) && empty($mysoc->country_code)) {
552			dol_print_error('', 'Call to select_accounting_account with mysoc country not yet defined');
553			exit();
554			}
555
556			$sql = "SELECT t.rowid, t.account_number, t.label as name_cpt";
557			$sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as t";
558			$sql .= " WHERE t.fk_accounting_category = ".$cat_id;
559			$sql .= " ORDER BY t.account_number ";
560
561			//echo $sql;
562
563			$resql = $this->db->query($sql);
564			if ($resql) {
565			$i = 0;
566			$obj = '';
567			$num = $this->db->num_rows($resql);
568			$data = array ();
569			if ($num) {
570			while ( $obj = $this->db->fetch_object($resql) ) {
571			$name_cat = $obj->name_cat;
572			$data[] = array (
573			'id' => $obj->rowid,
574			'account_number' => $obj->account_number,
575			'name_cpt' => $obj->name_cpt,
576			);
577			$i ++;
578			}
579			}
580			return $data;
581			} else {
582			$this->error = "Error " . $this->db->lasterror();
583			dol_syslog(__METHOD__ . " " . $this->error, LOG_ERR);
584
585			return -1;
586			}
587			}
588
589			}
590

Dolibarr / dolibarr

Branch — develop (30eefb)

AccountancyCategory::getResult() C

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like