27 require_once DOL_DOCUMENT_ROOT.
'/core/lib/accounting.lib.php';
47 public $errors = array();
52 public $element =
'c_accounting_category';
57 public $table_element =
'c_accounting_category';
83 public $range_account;
93 public $category_type;
123 public $lines_display;
150 public function create($user, $notrigger = 0)
152 global $conf, $langs;
156 if (isset($this->
code)) {
159 if (isset($this->label)) {
160 $this->label = trim($this->label);
162 if (isset($this->range_account)) {
163 $this->range_account = trim($this->range_account);
165 if (isset($this->sens)) {
166 $this->sens = (int) $this->sens;
168 if (isset($this->category_type)) {
169 $this->category_type = (int) $this->category_type;
171 if (isset($this->formula)) {
172 $this->formula = trim($this->formula);
174 if (isset($this->position)) {
175 $this->position = (int) $this->position;
177 if (isset($this->fk_country)) {
178 $this->fk_country = (int) $this->fk_country;
180 if (isset($this->active)) {
181 $this->active = (int) $this->active;
188 $sql =
"INSERT INTO ".MAIN_DB_PREFIX.
"c_accounting_category(";
189 if ($this->
rowid > 0) {
194 $sql .=
"range_account, ";
196 $sql .=
"category_type, ";
198 $sql .=
"position, ";
199 $sql .=
"fk_country, ";
202 $sql .=
") VALUES (";
203 if ($this->
rowid > 0) {
204 $sql .=
" ".((int) $this->
rowid).
",";
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);
220 dol_syslog(get_class($this).
"::create", LOG_DEBUG);
223 $error++; $this->errors[] =
"Error ".$this->db->lasterror();
228 foreach ($this->errors as $errmsg) {
229 dol_syslog(get_class($this).
"::create ".$errmsg, LOG_ERR);
230 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
232 $this->
db->rollback();
249 public function fetch($id, $code =
'', $label =
'')
255 $sql .=
" t.range_account,";
257 $sql .=
" t.category_type,";
258 $sql .=
" t.formula,";
259 $sql .=
" t.position,";
260 $sql .=
" t.fk_country,";
262 $sql .=
" FROM ".MAIN_DB_PREFIX.
"c_accounting_category as t";
264 $sql .=
" WHERE t.rowid = ".((int) $id);
266 $sql .=
" WHERE t.entity IN (".getEntity(
'c_accounting_category').
")";
268 $sql .=
" AND t.code = '".$this->db->escape($code).
"'";
270 $sql .=
" AND t.label = '".$this->db->escape($label).
"'";
274 dol_syslog(get_class($this).
"::fetch", LOG_DEBUG);
278 $obj = $this->
db->fetch_object(
$resql);
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;
295 $this->error =
"Error ".$this->db->lasterror();
308 public function update($user =
null, $notrigger = 0)
310 global $conf, $langs;
314 if (isset($this->
code)) {
317 if (isset($this->label)) {
318 $this->label = trim($this->label);
320 if (isset($this->range_account)) {
321 $this->range_account = trim($this->range_account);
323 if (isset($this->sens)) {
324 $this->sens = (int) $this->sens;
326 if (isset($this->category_type)) {
327 $this->category_type = (int) $this->category_type;
329 if (isset($this->formula)) {
330 $this->formula = trim($this->formula);
332 if (isset($this->position)) {
333 $this->position = (int) $this->position;
335 if (isset($this->fk_country)) {
336 $this->fk_country = (int) $this->fk_country;
338 if (isset($this->active)) {
339 $this->active = (int) $this->active;
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);
361 dol_syslog(get_class($this).
"::update", LOG_DEBUG);
364 $error++; $this->errors[] =
"Error ".$this->db->lasterror();
369 foreach ($this->errors as $errmsg) {
370 dol_syslog(get_class($this).
"::update ".$errmsg, LOG_ERR);
371 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
373 $this->
db->rollback();
389 public function delete($user, $notrigger = 0)
391 global $conf, $langs;
394 $sql =
"DELETE FROM ".MAIN_DB_PREFIX.
"c_accounting_category";
395 $sql .=
" WHERE rowid=".((int) $this->
id);
399 dol_syslog(get_class($this).
"::delete", LOG_DEBUG);
402 $error++; $this->errors[] =
"Error ".$this->db->lasterror();
407 foreach ($this->errors as $errmsg) {
408 dol_syslog(get_class($this).
"::delete ".$errmsg, LOG_ERR);
409 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
411 $this->
db->rollback();
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;
434 $this->lines_display = array();
441 while ($obj = $this->
db->fetch_object(
$resql)) {
442 $this->lines_display[] = $obj;
447 $this->error =
"Error ".$this->db->lasterror();
448 $this->errors[] = $this->error;
449 dol_syslog(__METHOD__.
" ".implode(
','.$this->errors), LOG_ERR);
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";
525 $this->lines_cptbk = array();
532 while ($obj = $this->
db->fetch_object(
$resql)) {
533 $this->lines_cptbk[] = $obj;
539 $this->error =
"Error ".$this->db->lasterror();
540 $this->errors[] = $this->error;
541 dol_syslog(__METHOD__.
" ".implode(
','.$this->errors), LOG_ERR);
560 require_once DOL_DOCUMENT_ROOT.
'/core/lib/accounting.lib.php';
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;";
576 $this->errors[] =
"Error ".$this->db->lasterror();
577 $this->
db->rollback();
581 $accountincptsadded = array();
582 while ($obj = $this->
db->fetch_object(
$resql)) {
584 if (!empty($accountincptsadded[$account_number_formated])) {
588 if (array_key_exists($account_number_formated, $cpts)) {
589 $accountincptsadded[$account_number_formated] = 1;
591 $sql =
"UPDATE ".MAIN_DB_PREFIX.
"accounting_account";
592 $sql .=
" SET fk_accounting_category=".((int) $id_cat);
593 $sql .=
" WHERE rowid=".((int) $obj->rowid);
595 $resqlupdate = $this->
db->query($sql);
598 $this->errors[] =
"Error ".$this->db->lasterror();
605 foreach ($this->errors as $errmsg) {
607 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
609 $this->
db->rollback();
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);
639 $this->errors[] =
"Error ".$this->db->lasterror();
644 foreach ($this->errors as $errmsg) {
646 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
648 $this->
db->rollback();
665 global $mysoc, $conf;
667 if (empty($mysoc->country_id)) {
668 dol_print_error(
'',
'Call to select_accounting_account with mysoc country not yet defined');
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";
690 while ($obj = $this->
db->fetch_object(
$resql)) {
691 $name_cat = $obj->name_cat;
692 $data[$name_cat][$i] = array(
694 'code' => $obj->code,
695 'position' => $obj->position,
696 'account_number' => $obj->account_number,
697 'account_label' => $obj->account_label,
705 $this->error =
"Error ".$this->db->lasterror();
706 dol_syslog(__METHOD__.
" ".$this->error, LOG_ERR);
724 public function getSumDebitCredit($cpt, $date_start, $date_end, $sens, $thirdparty_code =
'nofilter', $month = 0, $year = 0)
729 $this->sdcpermonth = array();
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";
735 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_bookkeeping as t";
737 $sql .=
" WHERE t.entity = ".$conf->entity;
738 if (is_array($cpt)) {
740 foreach ($cpt as $cptcursor) {
741 if ($listofaccount) {
742 $listofaccount .=
",";
744 $listofaccount .=
"'".$cptcursor.
"'";
746 $sql .=
" AND t.numero_compte IN (".$this->db->sanitize($listofaccount, 1).
")";
748 $sql .=
" AND t.numero_compte = '".$this->db->escape($cpt).
"'";
750 if (!empty($date_start) && !empty($date_end) && (empty($month) || empty($year))) {
751 $sql .=
" AND (t.doc_date BETWEEN '".$this->db->idate($date_start).
"' AND '".$this->
db->idate($date_end).
"')";
753 if (!empty($month) && !empty($year)) {
756 if ($thirdparty_code !=
'nofilter') {
757 $sql .=
" AND t.thirdparty_code = '".$this->db->escape($thirdparty_code).
"'";
759 if (is_array($cpt)) {
760 $sql .=
" GROUP BY t.numero_compte";
768 $obj = $this->
db->fetch_object(
$resql);
770 $this->sdc = $obj->debit - $obj->credit;
772 $this->sdc = $obj->credit - $obj->debit;
774 if (is_array($cpt)) {
775 $this->sdcperaccount[$obj->accountancy_account] = $this->sdc;
780 $this->error =
"Error ".$this->db->lasterror();
781 $this->errors[] = $this->error;
782 dol_syslog(__METHOD__.
" ".$this->error, LOG_ERR);
794 public function getCats($categorytype = -1, $active = 1)
796 global $conf, $mysoc;
798 if (empty($mysoc->country_id)) {
799 dol_print_error(
'',
'Call to select_accounting_account with mysoc country not yet defined');
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";
810 $sql .=
" AND (c.fk_country = ".((int) $mysoc->country_id).
" OR c.fk_country = 0)";
811 $sql .=
" ORDER BY c.position ASC";
821 $obj = $this->
db->fetch_object(
$resql);
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,
837 $this->error =
"Error ".$this->db->lasterror();
838 $this->errors[] = $this->error;
839 dol_syslog(__METHOD__.
" ".implode(
',', $this->errors), LOG_ERR);
854 public function getCptsCat($cat_id, $predefinedgroupwhere =
'')
856 global $conf, $mysoc;
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');
864 $pcgverid = $conf->global->CHARTOFACCOUNTS;
865 $pcgvercode =
dol_getIdFromCode($this->
db, $pcgverid,
'accounting_system',
'rowid',
'pcg_version');
866 if (empty($pcgvercode)) {
867 $pcgvercode = $pcgverid;
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";
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";
895 while ($obj = $this->
db->fetch_object(
$resql)) {
898 'account_number' => $obj->account_number,
899 'account_label' => $obj->account_label,
906 $this->error =
"Error ".$this->db->lasterror();
907 dol_syslog(__METHOD__.
" ".$this->error, LOG_ERR);