27require_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;
138 public $sdcperaccount;
158 public function create($user, $notrigger = 0)
160 global $conf, $langs;
164 if (isset($this->code)) {
165 $this->code = trim($this->code);
167 if (isset($this->label)) {
168 $this->label = trim($this->label);
170 if (isset($this->range_account)) {
171 $this->range_account = trim($this->range_account);
173 if (isset($this->sens)) {
174 $this->sens = (int) $this->sens;
176 if (isset($this->category_type)) {
177 $this->category_type = (int) $this->category_type;
179 if (isset($this->formula)) {
180 $this->formula = trim($this->formula);
185 if (isset($this->fk_country)) {
186 $this->fk_country = (int) $this->fk_country;
188 if (isset($this->active)) {
189 $this->active = (int) $this->active;
196 $sql =
"INSERT INTO ".MAIN_DB_PREFIX.
"c_accounting_category(";
197 if ($this->
rowid > 0) {
202 $sql .=
"range_account, ";
204 $sql .=
"category_type, ";
206 $sql .=
"position, ";
207 $sql .=
"fk_country, ";
210 $sql .=
") VALUES (";
211 if ($this->
rowid > 0) {
212 $sql .=
" ".((int) $this->
rowid).
",";
214 $sql .=
" ".(!isset($this->code) ?
"NULL" :
"'".$this->db->escape($this->code).
"'").
",";
215 $sql .=
" ".(!isset($this->label) ?
'NULL' :
"'".$this->db->escape($this->label).
"'").
",";
216 $sql .=
" ".(!isset($this->range_account) ?
'NULL' :
"'".$this->db->escape($this->range_account).
"'").
",";
217 $sql .=
" ".(!isset($this->sens) ?
'NULL' :
"'".$this->db->escape($this->sens).
"'").
",";
218 $sql .=
" ".(!isset($this->category_type) ?
'NULL' :
"'".$this->db->escape($this->category_type).
"'").
",";
219 $sql .=
" ".(!isset($this->formula) ?
'NULL' :
"'".$this->db->escape($this->formula).
"'").
",";
220 $sql .=
" ".(!isset($this->
position) ?
'NULL' : ((int) $this->
position)).
",";
221 $sql .=
" ".(!isset($this->fk_country) ?
'NULL' : ((int) $this->fk_country)).
",";
222 $sql .=
" ".(!isset($this->active) ?
'NULL' : ((int) $this->active));
223 $sql .=
", ".((int) $conf->entity);
228 dol_syslog(get_class($this).
"::create", LOG_DEBUG);
229 $resql = $this->db->query($sql);
232 $this->errors[] =
"Error ".$this->db->lasterror();
237 foreach ($this->errors as $errmsg) {
238 dol_syslog(get_class($this).
"::create ".$errmsg, LOG_ERR);
239 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
241 $this->db->rollback();
258 public function fetch($id, $code =
'', $label =
'')
264 $sql .=
" t.range_account,";
266 $sql .=
" t.category_type,";
267 $sql .=
" t.formula,";
268 $sql .=
" t.position,";
269 $sql .=
" t.fk_country,";
271 $sql .=
" FROM ".MAIN_DB_PREFIX.
"c_accounting_category as t";
273 $sql .=
" WHERE t.rowid = ".((int) $id);
275 $sql .=
" WHERE t.entity IN (".getEntity(
'c_accounting_category').
")";
277 $sql .=
" AND t.code = '".$this->db->escape($code).
"'";
279 $sql .=
" AND t.label = '".$this->db->escape($label).
"'";
283 dol_syslog(get_class($this).
"::fetch", LOG_DEBUG);
284 $resql = $this->db->query($sql);
286 if ($this->db->num_rows($resql)) {
287 $obj = $this->db->fetch_object($resql);
289 $this->
id = $obj->rowid;
290 $this->code = $obj->code;
291 $this->label = $obj->label;
292 $this->range_account = $obj->range_account;
293 $this->sens = $obj->sens;
294 $this->category_type = $obj->category_type;
295 $this->formula = $obj->formula;
297 $this->fk_country = $obj->fk_country;
298 $this->active = $obj->active;
300 $this->db->free($resql);
304 $this->error =
"Error ".$this->db->lasterror();
317 public function update($user =
null, $notrigger = 0)
319 global $conf, $langs;
323 if (isset($this->code)) {
324 $this->code = trim($this->code);
326 if (isset($this->label)) {
327 $this->label = trim($this->label);
329 if (isset($this->range_account)) {
330 $this->range_account = trim($this->range_account);
332 if (isset($this->sens)) {
333 $this->sens = (int) $this->sens;
335 if (isset($this->category_type)) {
336 $this->category_type = (int) $this->category_type;
338 if (isset($this->formula)) {
339 $this->formula = trim($this->formula);
344 if (isset($this->fk_country)) {
345 $this->fk_country = (int) $this->fk_country;
347 if (isset($this->active)) {
348 $this->active = (int) $this->active;
356 $sql =
"UPDATE ".MAIN_DB_PREFIX.
"c_accounting_category SET";
357 $sql .=
" code=".(isset($this->code) ?
"'".$this->db->escape($this->code).
"'" :
"null").
",";
358 $sql .=
" label=".(isset($this->label) ?
"'".$this->db->escape($this->label).
"'" :
"null").
",";
359 $sql .=
" range_account=".(isset($this->range_account) ?
"'".$this->db->escape($this->range_account).
"'" :
"null").
",";
360 $sql .=
" sens=".(isset($this->sens) ? ((int) $this->sens) :
"null").
",";
361 $sql .=
" category_type=".(isset($this->category_type) ? ((int) $this->category_type) :
"null").
",";
362 $sql .=
" formula=".(isset($this->formula) ?
"'".$this->db->escape($this->formula).
"'" :
"null").
",";
363 $sql .=
" position=".(isset($this->
position) ? ((int) $this->
position) :
"null").
",";
364 $sql .=
" fk_country=".(isset($this->fk_country) ? ((int) $this->fk_country) :
"null").
",";
365 $sql .=
" active=".(isset($this->active) ? ((int) $this->active) :
"null");
366 $sql .=
" WHERE rowid=".((int) $this->
id);
370 dol_syslog(get_class($this).
"::update", LOG_DEBUG);
371 $resql = $this->db->query($sql);
374 $this->errors[] =
"Error ".$this->db->lasterror();
379 foreach ($this->errors as $errmsg) {
380 dol_syslog(get_class($this).
"::update ".$errmsg, LOG_ERR);
381 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
383 $this->db->rollback();
399 public function delete($user, $notrigger = 0)
401 global $conf, $langs;
404 $sql =
"DELETE FROM ".MAIN_DB_PREFIX.
"c_accounting_category";
405 $sql .=
" WHERE rowid=".((int) $this->
id);
409 dol_syslog(get_class($this).
"::delete", LOG_DEBUG);
410 $resql = $this->db->query($sql);
413 $this->errors[] =
"Error ".$this->db->lasterror();
418 foreach ($this->errors as $errmsg) {
419 dol_syslog(get_class($this).
"::delete ".$errmsg, LOG_ERR);
420 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
422 $this->db->rollback();
440 $sql =
"SELECT t.rowid, t.account_number, t.label";
441 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_account as t";
442 $sql .=
" WHERE t.fk_accounting_category = ".((int) $id);
443 $sql .=
" AND t.entity = ".$conf->entity;
445 $this->lines_display = array();
448 $resql = $this->db->query($sql);
450 $num = $this->db->num_rows($resql);
452 while ($obj = $this->db->fetch_object($resql)) {
453 $this->lines_display[] = $obj;
458 $this->error =
"Error ".$this->db->lasterror();
459 $this->errors[] = $this->error;
460 dol_syslog(__METHOD__.
" ".implode(
',', $this->errors), LOG_ERR);
476 $sql =
"SELECT aa.account_number as numero_compte, aa.label as label_compte";
477 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_account as aa";
478 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
479 $sql .=
" WHERE (aa.fk_accounting_category <> ".((int) $id).
" OR aa.fk_accounting_category IS NULL)";
480 $sql .=
" AND asy.rowid = ".((int)
getDolGlobalInt(
'CHARTOFACCOUNTS'));
481 $sql .=
" AND aa.active = 1";
482 $sql .=
" AND aa.entity = ".$conf->entity;
483 $sql .=
" GROUP BY aa.account_number, aa.label";
484 $sql .=
" ORDER BY aa.account_number, aa.label";
486 $this->lines_cptbk = array();
489 $resql = $this->db->query($sql);
491 $num = $this->db->num_rows($resql);
493 while ($obj = $this->db->fetch_object($resql)) {
494 $this->lines_cptbk[] = $obj;
500 $this->error =
"Error ".$this->db->lasterror();
501 $this->errors[] = $this->error;
502 dol_syslog(__METHOD__.
" ".implode(
',', $this->errors), LOG_ERR);
521 require_once DOL_DOCUMENT_ROOT.
'/core/lib/accounting.lib.php';
523 $sql =
"SELECT aa.rowid, aa.account_number";
524 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_account as aa";
525 $sql .=
" INNER JOIN ".MAIN_DB_PREFIX.
"accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
526 $sql .=
" AND asy.rowid = ".((int)
getDolGlobalInt(
'CHARTOFACCOUNTS'));
527 $sql .=
" AND aa.active = 1";
528 $sql .=
" AND aa.entity = ".$conf->entity;
529 $sql .=
" ORDER BY LENGTH(aa.account_number) DESC;";
534 $resql = $this->db->query($sql);
537 $this->errors[] =
"Error ".$this->db->lasterror();
538 $this->db->rollback();
542 $accountincptsadded = array();
543 while ($obj = $this->db->fetch_object($resql)) {
545 if (!empty($accountincptsadded[$account_number_formated])) {
549 if (array_key_exists($account_number_formated, $cpts)) {
550 $accountincptsadded[$account_number_formated] = 1;
552 $sql =
"UPDATE ".MAIN_DB_PREFIX.
"accounting_account";
553 $sql .=
" SET fk_accounting_category=".((int) $id_cat);
554 $sql .=
" WHERE rowid=".((int) $obj->rowid);
556 $resqlupdate = $this->db->query($sql);
559 $this->errors[] =
"Error ".$this->db->lasterror();
566 foreach ($this->errors as $errmsg) {
568 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
570 $this->db->rollback();
591 $sql =
"UPDATE ".MAIN_DB_PREFIX.
"accounting_account as aa";
592 $sql .=
" SET fk_accounting_category= 0";
593 $sql .=
" WHERE aa.rowid = ".((int) $cpt_id);
597 $resql = $this->db->query($sql);
600 $this->errors[] =
"Error ".$this->db->lasterror();
605 foreach ($this->errors as $errmsg) {
607 $this->error .= ($this->error ?
', '.$errmsg : $errmsg);
609 $this->db->rollback();
631 public function getSumDebitCredit($cpt, $date_start, $date_end, $sens, $thirdparty_code =
'nofilter', $month = 0, $year = 0)
636 $this->sdcpermonth = array();
638 if (is_array($cpt)) {
640 foreach ($cpt as $cptcursor) {
641 if (! is_null($cptcursor)) {
642 if ($listofaccount) {
643 $listofaccount .=
",";
645 $listofaccount .=
"'".$cptcursor.
"'";
648 if (empty($listofaccount)) {
654 $sql =
"SELECT SUM(t.debit) as debit, SUM(t.credit) as credit";
655 if (is_array($cpt)) {
656 $sql .=
", t.numero_compte as accountancy_account";
658 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_bookkeeping as t";
660 $sql .=
" WHERE t.entity = ".((int) $conf->entity);
661 if (is_array($cpt)) {
662 $sql .=
" AND t.numero_compte IN (".$this->db->sanitize($listofaccount, 1).
")";
664 $sql .=
" AND t.numero_compte = '".$this->db->escape($cpt).
"'";
666 if (!empty($date_start) && !empty($date_end) && (empty($month) || empty($year))) {
667 $sql .=
" AND (t.doc_date BETWEEN '".$this->db->idate($date_start).
"' AND '".$this->db->idate($date_end).
"')";
669 if (!empty($month) && !empty($year)) {
672 if ($thirdparty_code !=
'nofilter') {
673 $sql .=
" AND t.thirdparty_code = '".$this->db->escape($thirdparty_code).
"'";
675 if (is_array($cpt)) {
676 $sql .=
" GROUP BY t.numero_compte";
679 $resql = $this->db->query($sql);
681 $num = $this->db->num_rows($resql);
685 $obj = $this->db->fetch_object($resql);
688 $this->sdc = $obj->debit - $obj->credit;
690 $this->sdc = $obj->credit - $obj->debit;
692 if (is_array($cpt)) {
693 $this->sdcperaccount[$obj->accountancy_account] = $this->sdc;
702 $this->error =
"Error ".$this->db->lasterror();
703 $this->errors[] = $this->error;
704 dol_syslog(__METHOD__.
" ".$this->error, LOG_ERR);
718 global $mysoc, $conf;
720 if (empty($mysoc->country_id)) {
721 $this->error =
"Error ".$this->db->lasterror();
722 dol_syslog(__METHOD__.
" ".$this->error, LOG_ERR);
726 $sql =
"SELECT t.rowid, t.account_number, t.label as account_label,";
727 $sql .=
" cat.code, cat.position, cat.label as name_cat, cat.sens, cat.category_type, cat.formula";
728 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_account as t, ".MAIN_DB_PREFIX.
"c_accounting_category as cat";
729 $sql .=
" WHERE t.fk_accounting_category IN (SELECT c.rowid";
730 $sql .=
" FROM ".MAIN_DB_PREFIX.
"c_accounting_category as c";
731 $sql .=
" WHERE c.active = 1";
732 $sql .=
" AND c.entity = ".$conf->entity;
733 $sql .=
" AND (c.fk_country = ".((int) $mysoc->country_id).
" OR c.fk_country = 0)";
734 $sql .=
" AND cat.rowid = t.fk_accounting_category";
735 $sql .=
" AND t.entity = ".$conf->entity;
737 $sql .=
" AND cat.rowid = ".((int) $catid);
739 $sql .=
" ORDER BY cat.position ASC";
741 $resql = $this->db->query($sql);
744 $num = $this->db->num_rows($resql);
747 while ($obj = $this->db->fetch_object($resql)) {
748 $name_cat = $obj->name_cat;
749 $data[$name_cat][$obj->rowid] = array(
751 'code' => $obj->code,
752 'label' => $obj->label,
753 'position' => $obj->position,
754 'category_type' => $obj->category_type,
755 'formula' => $obj->formula,
756 'sens' => $obj->sens,
757 'account_number' => $obj->account_number,
758 'account_label' => $obj->account_label
764 $this->error =
"Error ".$this->db->lasterror();
765 dol_syslog(__METHOD__.
" ".$this->error, LOG_ERR);
779 public function getCats($categorytype = -1, $active = 1)
781 global $conf, $mysoc;
783 if (empty($mysoc->country_id)) {
784 dol_print_error(
null,
'Call to select_accounting_account with mysoc country not yet defined');
788 $sql =
"SELECT c.rowid, c.code, c.label, c.formula, c.position, c.category_type, c.sens";
789 $sql .=
" FROM ".MAIN_DB_PREFIX.
"c_accounting_category as c";
790 $sql .=
" WHERE c.active = " . (int) $active;
791 $sql .=
" AND c.entity = ".$conf->entity;
792 if ($categorytype >= 0) {
793 $sql .=
" AND c.category_type = 1";
795 $sql .=
" AND (c.fk_country = ".((int) $mysoc->country_id).
" OR c.fk_country = 0)";
796 $sql .=
" ORDER BY c.position ASC";
798 $resql = $this->db->query($sql);
802 $num = $this->db->num_rows($resql);
806 $obj = $this->db->fetch_object($resql);
809 'rowid' => $obj->rowid,
810 'code' => $obj->code,
811 'label' => $obj->label,
812 'position' => $obj->position,
813 'category_type' => $obj->category_type,
814 'formula' => $obj->formula,
815 'sens' => $obj->sens,
823 $this->error =
"Error ".$this->db->lasterror();
824 $this->errors[] = $this->error;
825 dol_syslog(__METHOD__.
" ".implode(
',', $this->errors), LOG_ERR);
843 public function getCptsCat($cat_id, $predefinedgroupwhere =
'')
845 global $conf, $mysoc;
848 if (empty($mysoc->country_id) && empty($mysoc->country_code)) {
849 dol_print_error(
null,
'Call to select_accounting_account with mysoc country not yet defined');
854 $pcgvercode =
dol_getIdFromCode($this->db, $pcgverid,
'accounting_system',
'rowid',
'pcg_version');
855 if (empty($pcgvercode)) {
856 $pcgvercode = $pcgverid;
859 if (!empty($cat_id)) {
860 $sql =
"SELECT t.rowid, t.account_number, t.label as account_label";
861 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_account as t";
862 $sql .=
" WHERE t.fk_accounting_category = ".((int) $cat_id);
863 $sql .=
" AND t.entity = ".$conf->entity;
864 $sql .=
" AND t.active = 1";
865 $sql .=
" AND t.fk_pcg_version = '".$this->db->escape($pcgvercode).
"'";
866 $sql .=
" ORDER BY t.account_number";
868 $sql =
"SELECT t.rowid, t.account_number, t.label as account_label";
869 $sql .=
" FROM ".MAIN_DB_PREFIX.
"accounting_account as t";
870 $sql .=
" WHERE ".$predefinedgroupwhere;
871 $sql .=
" AND t.entity = ".$conf->entity;
872 $sql .=
' AND t.active = 1';
873 $sql .=
" AND t.fk_pcg_version = '".$this->db->escape($pcgvercode).
"'";
874 $sql .=
" ORDER BY t.account_number";
877 $resql = $this->db->query($sql);
881 $num = $this->db->num_rows($resql);
884 while ($obj = $this->db->fetch_object($resql)) {
887 'account_number' => $obj->account_number,
888 'account_label' => $obj->account_label,
895 $this->error =
"Error ".$this->db->lasterror();
896 dol_syslog(__METHOD__.
" ".$this->error, LOG_ERR);
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous)
Class to manage categories of an accounting account.
getCptsCat($cat_id, $predefinedgroupwhere='')
Get all accounting account of a given custom group (or a list of custom groups).
update($user=null, $notrigger=0)
Update object into database.
getAccountsWithNoCategory($id)
Function to fill ->lines_cptbk with accounting account (defined in chart of account) and not yet into...
__construct($db)
Constructor.
create($user, $notrigger=0)
Create object into database.
getCats($categorytype=-1, $active=1)
Return list of custom groups.
getCatsCpts($catid=0)
Function to get an array of all active custom groups (llx_c_accunting_categories) with their accounts...
fetch($id, $code='', $label='')
Load object in memory from database.
deleteCptCat($cpt_id)
Function to delete an accounting account from an accounting category.
display($id)
Function to select into ->lines_display all accounting accounts for a given custom accounting group.
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.
updateAccAcc($id_cat, $cpts=array())
Function to add an accounting account in an accounting category.
dol_get_first_day($year, $month=1, $gm=false)
Return GMT time for first day of a month or year.
dol_get_last_day($year, $month=12, $gm=false)
Return GMT time for last day of a month or year.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
dol_getIdFromCode($db, $key, $tablename, $fieldkey='code', $fieldid='id', $entityfilter=0, $filters='')
Return an id or code from a code or id.
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
publicphonebutton2 phonegreen basiclayout basiclayout TotalHT VATCode TotalVAT TotalLT1 TotalLT2 TotalTTC TotalHT clearboth nowraponall TAKEPOS_SHOW_SUBPRICE right right right takeposterminal SELECT e rowid