dolibarr  16.0.5
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@open-dsi.fr>
4  * Copyright (C) 2018-2019 Frédéric France <frederic.france@netlogic.fr>
5  *
6  * This program is free software; you can redistribute it and/or modify
7  * it under the terms of the GNU General Public License as published by
8  * the Free Software Foundation; either version 3 of the License, or
9  * (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program. If not, see <https://www.gnu.org/licenses/>.
18  */
19 
26 // Class
27 require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
28 
32 class AccountancyCategory // extends CommonObject
33 {
37  public $db;
38 
42  public $error;
43 
47  public $errors = array();
48 
52  public $element = 'c_accounting_category';
53 
57  public $table_element = 'c_accounting_category';
58 
63  public $rowid;
64 
68  public $id;
69 
73  public $code;
74 
78  public $label;
79 
83  public $range_account;
84 
88  public $sens;
89 
93  public $category_type;
94 
98  public $formula;
99 
103  public $position;
104 
108  public $fk_country;
109 
113  public $active;
114 
118  public $lines_cptbk;
119 
123  public $lines_display;
124 
128  public $sdc;
129 
130 
131 
137  public function __construct($db)
138  {
139  $this->db = $db;
140  }
141 
142 
150  public function create($user, $notrigger = 0)
151  {
152  global $conf, $langs;
153  $error = 0;
154 
155  // Clean parameters
156  if (isset($this->code)) {
157  $this->code = trim($this->code);
158  }
159  if (isset($this->label)) {
160  $this->label = trim($this->label);
161  }
162  if (isset($this->range_account)) {
163  $this->range_account = trim($this->range_account);
164  }
165  if (isset($this->sens)) {
166  $this->sens = (int) $this->sens;
167  }
168  if (isset($this->category_type)) {
169  $this->category_type = (int) $this->category_type;
170  }
171  if (isset($this->formula)) {
172  $this->formula = trim($this->formula);
173  }
174  if (isset($this->position)) {
175  $this->position = (int) $this->position;
176  }
177  if (isset($this->fk_country)) {
178  $this->fk_country = (int) $this->fk_country;
179  }
180  if (isset($this->active)) {
181  $this->active = (int) $this->active;
182  }
183 
184  // Check parameters
185  // Put here code to add control on parameters values
186 
187  // Insert request
188  $sql = "INSERT INTO ".MAIN_DB_PREFIX."c_accounting_category(";
189  if ($this->rowid > 0) {
190  $sql .= "rowid, ";
191  }
192  $sql .= "code, ";
193  $sql .= "label, ";
194  $sql .= "range_account, ";
195  $sql .= "sens, ";
196  $sql .= "category_type, ";
197  $sql .= "formula, ";
198  $sql .= "position, ";
199  $sql .= "fk_country, ";
200  $sql .= "active, ";
201  $sql .= "entity";
202  $sql .= ") VALUES (";
203  if ($this->rowid > 0) {
204  $sql .= " ".((int) $this->rowid).",";
205  }
206  $sql .= " ".(!isset($this->code) ? 'NULL' : "'".$this->db->escape($this->code)."'").",";
207  $sql .= " ".(!isset($this->label) ? 'NULL' : "'".$this->db->escape($this->label)."'").",";
208  $sql .= " ".(!isset($this->range_account) ? 'NULL' : "'".$this->db->escape($this->range_account)."'").",";
209  $sql .= " ".(!isset($this->sens) ? 'NULL' : "'".$this->db->escape($this->sens)."'").",";
210  $sql .= " ".(!isset($this->category_type) ? 'NULL' : "'".$this->db->escape($this->category_type)."'").",";
211  $sql .= " ".(!isset($this->formula) ? 'NULL' : "'".$this->db->escape($this->formula)."'").",";
212  $sql .= " ".(!isset($this->position) ? 'NULL' : ((int) $this->position)).",";
213  $sql .= " ".(!isset($this->fk_country) ? 'NULL' : ((int) $this->fk_country)).",";
214  $sql .= " ".(!isset($this->active) ? 'NULL' : ((int) $this->active));
215  $sql .= ", ".((int) $conf->entity);
216  $sql .= ")";
217 
218  $this->db->begin();
219 
220  dol_syslog(get_class($this)."::create", LOG_DEBUG);
221  $resql = $this->db->query($sql);
222  if (!$resql) {
223  $error++; $this->errors[] = "Error ".$this->db->lasterror();
224  }
225 
226  // Commit or rollback
227  if ($error) {
228  foreach ($this->errors as $errmsg) {
229  dol_syslog(get_class($this)."::create ".$errmsg, LOG_ERR);
230  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
231  }
232  $this->db->rollback();
233  return -1 * $error;
234  } else {
235  $this->db->commit();
236  return $this->id;
237  }
238  }
239 
240 
249  public function fetch($id, $code = '', $label = '')
250  {
251  $sql = "SELECT";
252  $sql .= " t.rowid,";
253  $sql .= " t.code,";
254  $sql .= " t.label,";
255  $sql .= " t.range_account,";
256  $sql .= " t.sens,";
257  $sql .= " t.category_type,";
258  $sql .= " t.formula,";
259  $sql .= " t.position,";
260  $sql .= " t.fk_country,";
261  $sql .= " t.active";
262  $sql .= " FROM ".MAIN_DB_PREFIX."c_accounting_category as t";
263  if ($id) {
264  $sql .= " WHERE t.rowid = ".((int) $id);
265  } else {
266  $sql .= " WHERE t.entity IN (".getEntity('c_accounting_category').")"; // Don't use entity if you use rowid
267  if ($code) {
268  $sql .= " AND t.code = '".$this->db->escape($code)."'";
269  } elseif ($label) {
270  $sql .= " AND t.label = '".$this->db->escape($label)."'";
271  }
272  }
273 
274  dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
275  $resql = $this->db->query($sql);
276  if ($resql) {
277  if ($this->db->num_rows($resql)) {
278  $obj = $this->db->fetch_object($resql);
279 
280  $this->id = $obj->rowid;
281  $this->code = $obj->code;
282  $this->label = $obj->label;
283  $this->range_account = $obj->range_account;
284  $this->sens = $obj->sens;
285  $this->category_type = $obj->category_type;
286  $this->formula = $obj->formula;
287  $this->position = $obj->position;
288  $this->fk_country = $obj->fk_country;
289  $this->active = $obj->active;
290  }
291  $this->db->free($resql);
292 
293  return 1;
294  } else {
295  $this->error = "Error ".$this->db->lasterror();
296  return -1;
297  }
298  }
299 
300 
308  public function update($user = null, $notrigger = 0)
309  {
310  global $conf, $langs;
311  $error = 0;
312 
313  // Clean parameters
314  if (isset($this->code)) {
315  $this->code = trim($this->code);
316  }
317  if (isset($this->label)) {
318  $this->label = trim($this->label);
319  }
320  if (isset($this->range_account)) {
321  $this->range_account = trim($this->range_account);
322  }
323  if (isset($this->sens)) {
324  $this->sens = (int) $this->sens;
325  }
326  if (isset($this->category_type)) {
327  $this->category_type = (int) $this->category_type;
328  }
329  if (isset($this->formula)) {
330  $this->formula = trim($this->formula);
331  }
332  if (isset($this->position)) {
333  $this->position = (int) $this->position;
334  }
335  if (isset($this->fk_country)) {
336  $this->fk_country = (int) $this->fk_country;
337  }
338  if (isset($this->active)) {
339  $this->active = (int) $this->active;
340  }
341 
342 
343  // Check parameters
344  // Put here code to add control on parameters values
345 
346  // Update request
347  $sql = "UPDATE ".MAIN_DB_PREFIX."c_accounting_category SET";
348  $sql .= " code=".(isset($this->code) ? "'".$this->db->escape($this->code)."'" : "null").",";
349  $sql .= " label=".(isset($this->label) ? "'".$this->db->escape($this->label)."'" : "null").",";
350  $sql .= " range_account=".(isset($this->range_account) ? "'".$this->db->escape($this->range_account)."'" : "null").",";
351  $sql .= " sens=".(isset($this->sens) ? $this->sens : "null").",";
352  $sql .= " category_type=".(isset($this->category_type) ? $this->category_type : "null").",";
353  $sql .= " formula=".(isset($this->formula) ? "'".$this->db->escape($this->formula)."'" : "null").",";
354  $sql .= " position=".(isset($this->position) ? $this->position : "null").",";
355  $sql .= " fk_country=".(isset($this->fk_country) ? $this->fk_country : "null").",";
356  $sql .= " active=".(isset($this->active) ? $this->active : "null")."";
357  $sql .= " WHERE rowid=".((int) $this->id);
358 
359  $this->db->begin();
360 
361  dol_syslog(get_class($this)."::update", LOG_DEBUG);
362  $resql = $this->db->query($sql);
363  if (!$resql) {
364  $error++; $this->errors[] = "Error ".$this->db->lasterror();
365  }
366 
367  // Commit or rollback
368  if ($error) {
369  foreach ($this->errors as $errmsg) {
370  dol_syslog(get_class($this)."::update ".$errmsg, LOG_ERR);
371  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
372  }
373  $this->db->rollback();
374  return -1 * $error;
375  } else {
376  $this->db->commit();
377  return 1;
378  }
379  }
380 
381 
389  public function delete($user, $notrigger = 0)
390  {
391  global $conf, $langs;
392  $error = 0;
393 
394  $sql = "DELETE FROM ".MAIN_DB_PREFIX."c_accounting_category";
395  $sql .= " WHERE rowid=".((int) $this->id);
396 
397  $this->db->begin();
398 
399  dol_syslog(get_class($this)."::delete", LOG_DEBUG);
400  $resql = $this->db->query($sql);
401  if (!$resql) {
402  $error++; $this->errors[] = "Error ".$this->db->lasterror();
403  }
404 
405  // Commit or rollback
406  if ($error) {
407  foreach ($this->errors as $errmsg) {
408  dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
409  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
410  }
411  $this->db->rollback();
412  return -1 * $error;
413  } else {
414  $this->db->commit();
415  return 1;
416  }
417  }
418 
419 
426  public function display($id)
427  {
428  global $conf;
429  $sql = "SELECT t.rowid, t.account_number, t.label";
430  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as t";
431  $sql .= " WHERE t.fk_accounting_category = ".((int) $id);
432  $sql .= " AND t.entity = ".$conf->entity;
433 
434  $this->lines_display = array();
435 
436  dol_syslog(__METHOD__, LOG_DEBUG);
437  $resql = $this->db->query($sql);
438  if ($resql) {
439  $num = $this->db->num_rows($resql);
440  if ($num) {
441  while ($obj = $this->db->fetch_object($resql)) {
442  $this->lines_display[] = $obj;
443  }
444  }
445  return $num;
446  } else {
447  $this->error = "Error ".$this->db->lasterror();
448  $this->errors[] = $this->error;
449  dol_syslog(__METHOD__." ".implode(','.$this->errors), LOG_ERR);
450 
451  return -1;
452  }
453  }
454 
461  /*
462  public function getCptBK($id)
463  {
464  global $conf;
465 
466  $sql = "SELECT DISTINCT t.numero_compte, t.label_operation, t.doc_ref";
467  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping as t";
468  $sql .= " WHERE t.numero_compte NOT IN ("; // account not into a custom group
469  $sql .= " SELECT t.account_number";
470  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as t";
471  $sql .= " WHERE t.fk_accounting_category = ".((int) $id)." AND t.entity = ".$conf->entity.")";
472  $sql .= " AND t.numero_compte IN ("; // account into current chart of account
473  $sql .= " SELECT DISTINCT aa.account_number";
474  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa";
475  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
476  $sql .= " AND asy.rowid = ".((int) $conf->global->CHARTOFACCOUNTS);
477  $sql .= " AND aa.active = 1";
478  $sql .= " AND aa.entity = ".$conf->entity.")";
479  $sql .= " GROUP BY t.numero_compte, t.label_operation, t.doc_ref";
480  $sql .= " ORDER BY t.numero_compte";
481 
482  $this->lines_cptbk = array();
483 
484  dol_syslog(__METHOD__, LOG_DEBUG);
485  $resql = $this->db->query($sql);
486  if ($resql) {
487  $num = $this->db->num_rows($resql);
488  if ($num) {
489  while ($obj = $this->db->fetch_object($resql)) {
490  $this->lines_cptbk[] = $obj;
491  }
492  }
493 
494  return $num;
495  } else {
496  $this->error = "Error ".$this->db->lasterror();
497  $this->errors[] = $this->error;
498  dol_syslog(__METHOD__." ".implode(','.$this->errors), LOG_ERR);
499 
500  return -1;
501  }
502  }
503  */
504 
511  public function getAccountsWithNoCategory($id)
512  {
513  global $conf;
514 
515  $sql = "SELECT aa.account_number as numero_compte, aa.label as label_compte";
516  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa";
517  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
518  $sql .= " WHERE (aa.fk_accounting_category <> ".((int) $id)." OR aa.fk_accounting_category IS NULL)";
519  $sql .= " AND asy.rowid = ".((int) $conf->global->CHARTOFACCOUNTS);
520  $sql .= " AND aa.active = 1";
521  $sql .= " AND aa.entity = ".$conf->entity;
522  $sql .= " GROUP BY aa.account_number, aa.label";
523  $sql .= " ORDER BY aa.account_number, aa.label";
524 
525  $this->lines_cptbk = array();
526 
527  dol_syslog(__METHOD__, LOG_DEBUG);
528  $resql = $this->db->query($sql);
529  if ($resql) {
530  $num = $this->db->num_rows($resql);
531  if ($num) {
532  while ($obj = $this->db->fetch_object($resql)) {
533  $this->lines_cptbk[] = $obj;
534  }
535  }
536 
537  return $num;
538  } else {
539  $this->error = "Error ".$this->db->lasterror();
540  $this->errors[] = $this->error;
541  dol_syslog(__METHOD__." ".implode(','.$this->errors), LOG_ERR);
542 
543  return -1;
544  }
545  }
546 
555  public function updateAccAcc($id_cat, $cpts = array())
556  {
557  global $conf;
558  $error = 0;
559 
560  require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
561 
562  $sql = "SELECT aa.rowid, aa.account_number";
563  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa";
564  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
565  $sql .= " AND asy.rowid = ".((int) $conf->global->CHARTOFACCOUNTS);
566  $sql .= " AND aa.active = 1";
567  $sql .= " AND aa.entity = ".$conf->entity;
568  $sql .= " ORDER BY LENGTH(aa.account_number) DESC;"; // LENGTH is ok with mysql and postgresql
569 
570  $this->db->begin();
571 
572  dol_syslog(__METHOD__, LOG_DEBUG);
573  $resql = $this->db->query($sql);
574  if (!$resql) {
575  $error++;
576  $this->errors[] = "Error ".$this->db->lasterror();
577  $this->db->rollback();
578  return -1;
579  }
580 
581  $accountincptsadded = array();
582  while ($obj = $this->db->fetch_object($resql)) {
583  $account_number_formated = length_accountg($obj->account_number);
584  if (!empty($accountincptsadded[$account_number_formated])) {
585  continue;
586  }
587 
588  if (array_key_exists($account_number_formated, $cpts)) {
589  $accountincptsadded[$account_number_formated] = 1;
590  // We found an account number that is in list $cpts of account to add
591  $sql = "UPDATE ".MAIN_DB_PREFIX."accounting_account";
592  $sql .= " SET fk_accounting_category=".((int) $id_cat);
593  $sql .= " WHERE rowid=".((int) $obj->rowid);
594  dol_syslog(__METHOD__, LOG_DEBUG);
595  $resqlupdate = $this->db->query($sql);
596  if (!$resqlupdate) {
597  $error++;
598  $this->errors[] = "Error ".$this->db->lasterror();
599  }
600  }
601  }
602 
603  // Commit or rollback
604  if ($error) {
605  foreach ($this->errors as $errmsg) {
606  dol_syslog(__METHOD__." ".$errmsg, LOG_ERR);
607  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
608  }
609  $this->db->rollback();
610 
611  return -1 * $error;
612  } else {
613  $this->db->commit();
614 
615  return 1;
616  }
617  }
618 
626  public function deleteCptCat($cpt_id)
627  {
628  $error = 0;
629 
630  $sql = "UPDATE ".MAIN_DB_PREFIX."accounting_account as aa";
631  $sql .= " SET fk_accounting_category= 0";
632  $sql .= " WHERE aa.rowid = ".((int) $cpt_id);
633  $this->db->begin();
634 
635  dol_syslog(__METHOD__, LOG_DEBUG);
636  $resql = $this->db->query($sql);
637  if (!$resql) {
638  $error++;
639  $this->errors[] = "Error ".$this->db->lasterror();
640  }
641 
642  // Commit or rollback
643  if ($error) {
644  foreach ($this->errors as $errmsg) {
645  dol_syslog(__METHOD__." ".$errmsg, LOG_ERR);
646  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
647  }
648  $this->db->rollback();
649 
650  return -1 * $error;
651  } else {
652  $this->db->commit();
653 
654  return 1;
655  }
656  }
657 
663  public function getCatsCpts()
664  {
665  global $mysoc, $conf;
666 
667  if (empty($mysoc->country_id)) {
668  dol_print_error('', 'Call to select_accounting_account with mysoc country not yet defined');
669  exit();
670  }
671 
672  $sql = "SELECT t.rowid, t.account_number, t.label as account_label, cat.code, cat.position, cat.label as name_cat, cat.sens ";
673  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as t, ".MAIN_DB_PREFIX."c_accounting_category as cat";
674  $sql .= " WHERE t.fk_accounting_category IN ( SELECT c.rowid ";
675  $sql .= " FROM ".MAIN_DB_PREFIX."c_accounting_category as c";
676  $sql .= " WHERE c.active = 1";
677  $sql .= " AND c.entity = ".$conf->entity;
678  $sql .= " AND (c.fk_country = ".((int) $mysoc->country_id)." OR c.fk_country = 0)";
679  $sql .= " AND cat.rowid = t.fk_accounting_category";
680  $sql .= " AND t.entity = ".$conf->entity;
681  $sql .= " ORDER BY cat.position ASC";
682 
683  $resql = $this->db->query($sql);
684  if ($resql) {
685  $i = 0;
686  $obj = '';
687  $num = $this->db->num_rows($resql);
688  $data = array();
689  if ($num) {
690  while ($obj = $this->db->fetch_object($resql)) {
691  $name_cat = $obj->name_cat;
692  $data[$name_cat][$i] = array(
693  'id' => $obj->rowid,
694  'code' => $obj->code,
695  'position' => $obj->position,
696  'account_number' => $obj->account_number,
697  'account_label' => $obj->account_label,
698  'sens' => $obj->sens
699  );
700  $i++;
701  }
702  }
703  return $data;
704  } else {
705  $this->error = "Error ".$this->db->lasterror();
706  dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
707 
708  return -1;
709  }
710  }
711 
724  public function getSumDebitCredit($cpt, $date_start, $date_end, $sens, $thirdparty_code = 'nofilter', $month = 0, $year = 0)
725  {
726  global $conf;
727 
728  $this->sdc = 0;
729  $this->sdcpermonth = array();
730 
731  $sql = "SELECT SUM(t.debit) as debit, SUM(t.credit) as credit";
732  if (is_array($cpt)) {
733  $sql .= ", t.numero_compte as accountancy_account";
734  }
735  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping as t";
736  //if (in_array($this->db->type, array('mysql', 'mysqli'))) $sql.=' USE INDEX idx_accounting_bookkeeping_doc_date';
737  $sql .= " WHERE t.entity = ".$conf->entity;
738  if (is_array($cpt)) {
739  $listofaccount = '';
740  foreach ($cpt as $cptcursor) {
741  if ($listofaccount) {
742  $listofaccount .= ",";
743  }
744  $listofaccount .= "'".$cptcursor."'";
745  }
746  $sql .= " AND t.numero_compte IN (".$this->db->sanitize($listofaccount, 1).")";
747  } else {
748  $sql .= " AND t.numero_compte = '".$this->db->escape($cpt)."'";
749  }
750  if (!empty($date_start) && !empty($date_end) && (empty($month) || empty($year))) { // If month/year provided, it is stronger than filter date_start/date_end
751  $sql .= " AND (t.doc_date BETWEEN '".$this->db->idate($date_start)."' AND '".$this->db->idate($date_end)."')";
752  }
753  if (!empty($month) && !empty($year)) {
754  $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))."')";
755  }
756  if ($thirdparty_code != 'nofilter') {
757  $sql .= " AND t.thirdparty_code = '".$this->db->escape($thirdparty_code)."'";
758  }
759  if (is_array($cpt)) {
760  $sql .= " GROUP BY t.numero_compte";
761  }
762  //print $sql;
763 
764  $resql = $this->db->query($sql);
765  if ($resql) {
766  $num = $this->db->num_rows($resql);
767  if ($num) {
768  $obj = $this->db->fetch_object($resql);
769  if ($sens == 1) {
770  $this->sdc = $obj->debit - $obj->credit;
771  } else {
772  $this->sdc = $obj->credit - $obj->debit;
773  }
774  if (is_array($cpt)) {
775  $this->sdcperaccount[$obj->accountancy_account] = $this->sdc;
776  }
777  }
778  return $num;
779  } else {
780  $this->error = "Error ".$this->db->lasterror();
781  $this->errors[] = $this->error;
782  dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
783  return -1;
784  }
785  }
786 
794  public function getCats($categorytype = -1, $active = 1)
795  {
796  global $conf, $mysoc;
797 
798  if (empty($mysoc->country_id)) {
799  dol_print_error('', 'Call to select_accounting_account with mysoc country not yet defined');
800  exit();
801  }
802 
803  $sql = "SELECT c.rowid, c.code, c.label, c.formula, c.position, c.category_type, c.sens";
804  $sql .= " FROM ".MAIN_DB_PREFIX."c_accounting_category as c";
805  $sql .= " WHERE c.active = " . (int) $active;
806  $sql .= " AND c.entity = ".$conf->entity;
807  if ($categorytype >= 0) {
808  $sql .= " AND c.category_type = 1";
809  }
810  $sql .= " AND (c.fk_country = ".((int) $mysoc->country_id)." OR c.fk_country = 0)";
811  $sql .= " ORDER BY c.position ASC";
812 
813  $resql = $this->db->query($sql);
814  if ($resql) {
815  $i = 0;
816  $obj = '';
817  $num = $this->db->num_rows($resql);
818  $data = array();
819  if ($num) {
820  while ($i < $num) {
821  $obj = $this->db->fetch_object($resql);
822 
823  $data[] = array(
824  'rowid' => $obj->rowid,
825  'code' => $obj->code,
826  'label' => $obj->label,
827  'formula' => $obj->formula,
828  'position' => $obj->position,
829  'category_type' => $obj->category_type,
830  'bc' => $obj->sens
831  );
832  $i++;
833  }
834  }
835  return $data;
836  } else {
837  $this->error = "Error ".$this->db->lasterror();
838  $this->errors[] = $this->error;
839  dol_syslog(__METHOD__." ".implode(',', $this->errors), LOG_ERR);
840 
841  return -1;
842  }
843  }
844 
845 
854  public function getCptsCat($cat_id, $predefinedgroupwhere = '')
855  {
856  global $conf, $mysoc;
857  $sql = '';
858 
859  if (empty($mysoc->country_id) && empty($mysoc->country_code)) {
860  dol_print_error('', 'Call to select_accounting_account with mysoc country not yet defined');
861  exit();
862  }
863 
864  $pcgverid = $conf->global->CHARTOFACCOUNTS;
865  $pcgvercode = dol_getIdFromCode($this->db, $pcgverid, 'accounting_system', 'rowid', 'pcg_version');
866  if (empty($pcgvercode)) {
867  $pcgvercode = $pcgverid;
868  }
869 
870  if (!empty($cat_id)) {
871  $sql = "SELECT t.rowid, t.account_number, t.label as account_label";
872  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as t";
873  $sql .= " WHERE t.fk_accounting_category = ".((int) $cat_id);
874  $sql .= " AND t.entity = ".$conf->entity;
875  $sql .= " AND t.active = 1";
876  $sql .= " AND t.fk_pcg_version = '".$this->db->escape($pcgvercode)."'";
877  $sql .= " ORDER BY t.account_number";
878  } else {
879  $sql = "SELECT t.rowid, t.account_number, t.label as account_label";
880  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as t";
881  $sql .= " WHERE ".$predefinedgroupwhere;
882  $sql .= " AND t.entity = ".$conf->entity;
883  $sql .= ' AND t.active = 1';
884  $sql .= " AND t.fk_pcg_version = '".$this->db->escape($pcgvercode)."'";
885  $sql .= " ORDER BY t.account_number";
886  }
887 
888  $resql = $this->db->query($sql);
889  if ($resql) {
890  $i = 0;
891  $obj = '';
892  $num = $this->db->num_rows($resql);
893  $data = array();
894  if ($num) {
895  while ($obj = $this->db->fetch_object($resql)) {
896  $data[] = array(
897  'id' => $obj->rowid,
898  'account_number' => $obj->account_number,
899  'account_label' => $obj->account_label,
900  );
901  $i++;
902  }
903  }
904  return $data;
905  } else {
906  $this->error = "Error ".$this->db->lasterror();
907  dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
908 
909  return -1;
910  }
911  }
912 }
db
$conf db
API class for accounts.
Definition: inc.php:41
AccountancyCategory\create
create($user, $notrigger=0)
Create object into database.
Definition: accountancycategory.class.php:150
dol_print_error
dol_print_error($db='', $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
Definition: functions.lib.php:4844
AccountancyCategory
Class to manage categories of an accounting account.
Definition: accountancycategory.class.php:32
rowid
print *****$script_file(".$version.") pid c cd cd cd description as p label as s rowid
Definition: email_expire_services_to_representatives.php:79
AccountancyCategory\getCatsCpts
getCatsCpts()
Function to know all custom groupd from an accounting account.
Definition: accountancycategory.class.php:663
AccountancyCategory\getCptsCat
getCptsCat($cat_id, $predefinedgroupwhere='')
Get all accounting account of a custom group (or a list of custom groups).
Definition: accountancycategory.class.php:854
AccountancyCategory\fetch
fetch($id, $code='', $label='')
Load object in memory from database.
Definition: accountancycategory.class.php:249
dol_getIdFromCode
dol_getIdFromCode($db, $key, $tablename, $fieldkey='code', $fieldid='id', $entityfilter=0, $filters='')
Return an id or code from a code or id.
Definition: functions.lib.php:8535
code
print *****$script_file(".$version.") pid code
! Closing after partial payment: discount_vat, badcustomer or badsupplier, bankcharge,...
Definition: sync_members_ldap2dolibarr.php:60
AccountancyCategory\getSumDebitCredit
getSumDebitCredit($cpt, $date_start, $date_end, $sens, $thirdparty_code='nofilter', $month=0, $year=0)
Function to show result of an accounting account from the ledger with a direction and a period.
Definition: accountancycategory.class.php:724
length_accountg
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous)
Definition: accounting.lib.php:94
AccountancyCategory\deleteCptCat
deleteCptCat($cpt_id)
Function to delete an accounting account from an accounting category.
Definition: accountancycategory.class.php:626
AccountancyCategory\update
update($user=null, $notrigger=0)
Update object into database.
Definition: accountancycategory.class.php:308
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition: functions.lib.php:1603
AccountancyCategory\getAccountsWithNoCategory
getAccountsWithNoCategory($id)
Function to fill ->lines_cptbk with accounting account used (into bookkeeping) and not yet into a cus...
Definition: accountancycategory.class.php:511
AccountancyCategory\getCats
getCats($categorytype=-1, $active=1)
Return list of custom groups.
Definition: accountancycategory.class.php:794
AccountancyCategory\display
display($id)
Function to select into ->lines_display all accounting accounts for a given custom accounting group.
Definition: accountancycategory.class.php:426
dol_get_first_day
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
Definition: date.lib.php:551
dol_get_last_day
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
Definition: date.lib.php:570
AccountancyCategory\updateAccAcc
updateAccAcc($id_cat, $cpts=array())
Function to add an accounting account in an accounting category.
Definition: accountancycategory.class.php:555
$resql
if(isModEnabled('facture') &&!empty($user->rights->facture->lire)) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->rights->fournisseur->facture->lire)||(isModEnabled('supplier_invoice') && $user->rights->supplier_invoice->lire)) if(isModEnabled('don') &&!empty($user->rights->don->lire)) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->rights->commande->lire &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $resql
Social contributions to pay.
Definition: index.php:742
AccountancyCategory\__construct
__construct($db)
Constructor.
Definition: accountancycategory.class.php:137