dolibarr  7.0.0-beta
accountancycategory.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2016 Jamal Elbaz <jamelbaz@gmail.pro>
3  * Copyright (C) 2016-2017 Alexandre Spangaro <aspangaro@zendsi.com>
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 
25 // Class
26 require_once DOL_DOCUMENT_ROOT . '/core/lib/accounting.lib.php';
27 
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 
48  public function __construct($db) {
49  $this->db = $db;
50  }
51 
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 
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 
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 
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 
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 
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 
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 = '".$this->db->escape($thirdparty_code)."'";
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 
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 
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 }
getCats($categorytype=-1)
Return list of personalized groups.
dol_print_error($db='', $error='', $errors=null)
Affiche message erreur system avec toutes les informations pour faciliter le diagnostic et la remonte...
Class to manage categories of an accounting account.
getResult($cpt, $month, $date_start, $date_end, $sens, $thirdparty_code='nofilter')
Function to show result of an accounting account from the ledger with a direction and a period...
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
getCptBK($id)
Function to select accounting category of an accounting account present in chart of accounts...
getCatsCpts()
Function to know all category from accounting account.
updateAccAcc($id_cat, $cpts=array())
Function to add an accounting account in an accounting category.
getCptsCat($cat_id, $predefinedgroupwhere='')
Get all accounting account of a group.
display($id)
Function to select all accounting accounts from an accounting category.
if(!empty($conf->facture->enabled)&&$user->rights->facture->lire) if(!empty($conf->fournisseur->enabled)&&$user->rights->fournisseur->facture->lire) if(!empty($conf->don->enabled)&&$user->rights->societe->lire) if(!empty($conf->tax->enabled)&&$user->rights->tax->charges->lire) if(!empty($conf->facture->enabled)&&!empty($conf->commande->enabled)&&$user->rights->commande->lire &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) if(!empty($conf->facture->enabled)&&$user->rights->facture->lire) if(!empty($conf->fournisseur->enabled)&&$user->rights->fournisseur->facture->lire) $resql
Social contributions to pay.
Definition: index.php:1013
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous) ...
deleteCptCat($cpt_id)
Function to delete an accounting account from an accounting category.
getAccountsWithNoCategory($id)
Function to select accounting category of an accounting account present in chart of accounts...