dolibarr 24.0.3
bookkeeping.class.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2014-2017 Olivier Geffroy <jeff@jeffinfo.com>
3 * Copyright (C) 2015-2026 Alexandre Spangaro <alexandre@inovea-conseil.com>
4 * Copyright (C) 2015-2020 Florian Henry <florian.henry@open-concept.pro>
5 * Copyright (C) 2018-2025 Frédéric France <frederic.france@free.fr>
6 * Copyright (C) 2024-2026 MDW <mdeweerd@users.noreply.github.com>
7 * Copyright (C) 2024 Jose MARTINEZ <jose.martinez@pichinov.com>
8 * Copyright (C) 2025 Nicolas Barrouillet <nicolas@pragma-tech.fr>
9 *
10 * This program is free software; you can redistribute it and/or modify
11 * it under the terms of the GNU General Public License as published by
12 * the Free Software Foundation; either version 3 of the License, or
13 * (at your option) any later version.
14 *
15 * This program is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
19 *
20 * You should have received a copy of the GNU General Public License
21 * along with this program. If not, see <https://www.gnu.org/licenses/>.
22 */
23
30// Class
31require_once DOL_DOCUMENT_ROOT.'/core/class/commonobject.class.php';
32require_once DOL_DOCUMENT_ROOT.'/core/class/commonobjectline.class.php';
33require_once DOL_DOCUMENT_ROOT.'/core/class/fiscalyear.class.php';
34require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingjournal.class.php';
35require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingaccount.class.php';
36
41{
45 public $element = 'accountingbookkeeping';
46
50 public $table_element = 'accounting_bookkeeping';
51
55 public $lines = array();
56
60 public $id;
61
65 public $doc_date;
66
70 public $date_lim_reglement;
71
75 public $doc_type;
76
80 public $doc_ref;
81
85 public $fk_doc;
86
90 public $fk_docdet;
91
95 public $thirdparty_code;
96
100 public $subledger_account;
101
105 public $subledger_label;
106
110 public $numero_compte;
111
115 public $label_compte;
116
120 public $label_operation;
121
125 public $debit;
126
130 public $credit;
131
136 public $montant;
137
142 public $amount;
143
147 public $sens;
148
152 public $fk_user_author;
153
157 public $import_key;
158
162 public $code_journal;
163
167 public $journal_label;
168
172 public $piece_num;
173
177 public $ref;
178
182 public $linesmvt = array();
183
187 public $linesexport = array();
188
192 public $date_export;
193
197 public $picto = 'generic';
198
202 public static $can_modify_bookkeeping_sql_cached;
203
207 public $warnings = array();
208
209
215 public function __construct(DoliDB $db)
216 {
217 $this->db = $db;
218 }
219
227 public function create(User $user, $notrigger = 0)
228 {
229 global $conf, $langs;
230
231 dol_syslog(__METHOD__, LOG_DEBUG);
232
233 $error = 0;
234
235 // Clean parameters</center>
236 if (isset($this->doc_type)) {
237 $this->doc_type = trim($this->doc_type);
238 }
239 if (isset($this->doc_ref)) {
240 $this->doc_ref = trim($this->doc_ref);
241 $this->doc_ref = dol_trunc($this->doc_ref, 300); // We limit to 300 chars to avoid problems with too long ref in DB
242 }
243 if (isset($this->fk_doc)) {
244 $this->fk_doc = (int) $this->fk_doc;
245 }
246 if (isset($this->fk_docdet)) {
247 $this->fk_docdet = (int) $this->fk_docdet;
248 }
249 if (isset($this->thirdparty_code)) {
250 $this->thirdparty_code = trim($this->thirdparty_code);
251 }
252 if (isset($this->subledger_account)) {
253 $this->subledger_account = trim($this->subledger_account);
254 }
255 if (isset($this->subledger_label)) {
256 $this->subledger_label = trim($this->subledger_label);
257 }
258 if (isset($this->numero_compte)) {
259 $this->numero_compte = trim($this->numero_compte);
260 }
261 if (isset($this->label_compte)) {
262 $this->label_compte = trim($this->label_compte);
263 }
264 if (isset($this->label_operation)) {
265 $this->label_operation = trim($this->label_operation);
266 }
267 if (isset($this->debit)) {
268 $this->debit = (float) $this->debit;
269 }
270 if (isset($this->credit)) {
271 $this->credit = (float) $this->credit;
272 }
273 if (isset($this->montant)) {
274 $this->montant = (float) $this->montant;
275 }
276 if (isset($this->amount)) {
277 $this->amount = (float) $this->amount;
278 }
279 if (isset($this->sens)) {
280 $this->sens = trim($this->sens);
281 }
282 if (isset($this->import_key)) {
283 $this->import_key = trim($this->import_key);
284 }
285 if (isset($this->code_journal)) {
286 $this->code_journal = trim($this->code_journal);
287 }
288 if (isset($this->journal_label)) {
289 $this->journal_label = trim($this->journal_label);
290 }
291 if (isset($this->piece_num)) {
292 $this->piece_num = (int) $this->piece_num;
293 }
294 if (empty($this->debit)) {
295 $this->debit = 0.0;
296 }
297 if (empty($this->credit)) {
298 $this->credit = 0.0;
299 }
300
301 $result = $this->validBookkeepingDate($this->doc_date); // Check date according to ACCOUNTANCY_FISCAL_PERIOD_MODE.
302 if ($result < 0) {
303 return -1;
304 } elseif ($result == 0) {
305 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
306 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod');
307 } else {
308 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod');
309 }
310 return -1;
311 }
312
313 // Check parameters
314 if (($this->numero_compte == "") || $this->numero_compte == '-1' || $this->numero_compte == 'NotDefined') {
315 $langs->loadLangs(array("errors"));
316 if (in_array($this->doc_type, array('bank', 'expense_report'))) {
317 $this->errors[] = $langs->trans('ErrorFieldAccountNotDefinedForBankLine', $this->fk_docdet, $this->doc_type);
318 } else {
319 //$this->errors[]=$langs->trans('ErrorFieldAccountNotDefinedForInvoiceLine', $this->doc_ref, $this->label_compte);
320 $mesg = $this->doc_ref.', '.$langs->trans("AccountAccounting").': '.($this->numero_compte != -1 ? $this->numero_compte : $langs->trans("Unknown"));
321 if ($this->subledger_account && $this->subledger_account != $this->numero_compte) {
322 $mesg .= ', '.$langs->trans("SubledgerAccount").': '.$this->subledger_account;
323 }
324 $this->errors[] = $langs->trans('ErrorFieldAccountNotDefinedForLine', $mesg);
325 }
326
327 return -1;
328 }
329
330 $this->db->begin();
331
332 $this->piece_num = 0;
333 $this->ref = '';
334
335 // First check if line not yet already in bookkeeping.
336 // Note that we must include 'doc_type - fk_doc - numero_compte - label - subledger_account (if not empty)' to be sure to have unicity of line (because we may have several lines
337 // with same doc_type, fk_doc, numero_compte for 1 invoice line when using localtaxes with same account)
338 // WARNING: This is not reliable, label may have been modified. This is just a small protection.
339 // The page that make transfer make the test on couple (doc_type - fk_doc) only.
340 $sql = "SELECT count(*) as nb";
341 $sql .= " FROM ".$this->db->prefix().$this->table_element;
342 $sql .= " WHERE doc_type = '".$this->db->escape($this->doc_type)."'";
343 $sql .= " AND fk_doc = ".((int) $this->fk_doc);
344 if (getDolGlobalString('ACCOUNTANCY_ENABLE_FKDOCDET')) {
345 // DO NOT USE THIS IN PRODUCTION. This will generate a lot of trouble into reports and will corrupt database (by generating duplicate entries.
346 $sql .= " AND fk_docdet = ".((int) $this->fk_docdet); // This field can be 0 if record is for several lines
347 }
348 $sql .= " AND numero_compte = '".$this->db->escape($this->numero_compte)."'";
349 $sql .= " AND label_operation = '".$this->db->escape($this->label_operation)."'";
350 if (!empty($this->subledger_account)) {
351 $sql .= " AND subledger_account = '".$this->db->escape($this->subledger_account)."'";
352 }
353 $sql .= " AND entity = ".((int) $conf->entity); // Do not use getEntity for accounting features
354
355 // Allow duplicates in incomes or loss statements
356 $accountProfit = getDolGlobalString('ACCOUNTING_RESULT_PROFIT');
357 $accountLoss = getDolGlobalString('ACCOUNTING_RESULT_LOSS');
358
359 if (($accountProfit && $this->numero_compte === trim($accountProfit)) ||
360 ($accountLoss && $this->numero_compte === trim($accountLoss))) {
361 // If the account being processed corresponds to the “Profit” or “Loss” constant, the detection is bypassed
362 $sql .= " AND 1 = 2";
363 }
364
365 $resql = $this->db->query($sql);
366
367 if ($resql) {
368 $row = $this->db->fetch_object($resql);
369 if ($row->nb == 0) { // Not already into bookkeeping
370 // Check to know if piece_num already exists for data we try to insert to reuse the same value
371 $sqlnum = "SELECT piece_num, ref";
372 $sqlnum .= " FROM ".$this->db->prefix().$this->table_element;
373 $sqlnum .= " WHERE doc_type = '".$this->db->escape($this->doc_type)."'"; // For example doc_type = 'bank'
374 $sqlnum .= " AND fk_doc = ".((int) $this->fk_doc);
375 if (getDolGlobalString('ACCOUNTANCY_ENABLE_FKDOCDET')) {
376 // fk_docdet is rowid into llx_bank or llx_facturedet or llx_facturefourndet, or ...
377 $sqlnum .= " AND fk_docdet = ".((int) $this->fk_docdet);
378 }
379 $sqlnum .= " AND doc_ref = '".$this->db->escape($this->doc_ref)."'"; // ref of source object
380 $sqlnum .= " AND entity = ".((int) $conf->entity); // Do not use getEntity for accounting features
381
382 dol_syslog(get_class($this).":: create sqlnum=".$sqlnum, LOG_DEBUG);
383 $resqlnum = $this->db->query($sqlnum);
384 if ($resqlnum) {
385 $num = $this->db->num_rows($resqlnum);
386 if ($num > 0) {
387 $objnum = $this->db->fetch_object($resqlnum);
388 $this->piece_num = $objnum->piece_num;
389 $this->ref = $objnum->ref;
390 } else {
391 $this->piece_num = 0;
392 $this->ref = '';
393 }
394 }
395
396 dol_syslog(get_class($this)."::create this->piece_num=".$this->piece_num, LOG_DEBUG);
397 if (empty($this->piece_num)) {
398 $sqlnum = "SELECT MAX(piece_num)+1 as maxpiecenum";
399 $sqlnum .= " FROM ".$this->db->prefix().$this->table_element;
400 $sqlnum .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
401
402 $resqlnum = $this->db->query($sqlnum);
403 if ($resqlnum) {
404 $objnum = $this->db->fetch_object($resqlnum);
405 $this->piece_num = $objnum->maxpiecenum;
406 }
407
408 $this->ref = $this->getNextNumRef();
409 dol_syslog(get_class($this).":: create now this->piece_num={$this->piece_num}, this->ref={$this->ref}", LOG_DEBUG);
410 }
411 if (empty($this->piece_num)) {
412 $this->piece_num = 1;
413 }
414
415 $now = dol_now();
416
417 $sql = "INSERT INTO ".$this->db->prefix().$this->table_element." (";
418 $sql .= "doc_date";
419 $sql .= ", date_lim_reglement";
420 $sql .= ", doc_type";
421 $sql .= ", doc_ref";
422 $sql .= ", fk_doc";
423 $sql .= ", fk_docdet";
424 $sql .= ", thirdparty_code";
425 $sql .= ", subledger_account";
426 $sql .= ", subledger_label";
427 $sql .= ", numero_compte";
428 $sql .= ", label_compte";
429 $sql .= ", label_operation";
430 $sql .= ", debit";
431 $sql .= ", credit";
432 $sql .= ", montant";
433 $sql .= ", sens";
434 $sql .= ", fk_user_author";
435 $sql .= ", date_creation";
436 $sql .= ", code_journal";
437 $sql .= ", journal_label";
438 $sql .= ", piece_num";
439 $sql .= ", ref";
440 $sql .= ', entity';
441 $sql .= ") VALUES (";
442 $sql .= "'".$this->db->idate($this->doc_date)."'";
443 $sql .= ", ".(isDolTms($this->date_lim_reglement) ? "'".$this->db->idate($this->date_lim_reglement)."'" : 'NULL');
444 $sql .= ", '".$this->db->escape($this->doc_type)."'";
445 $sql .= ", '".$this->db->escape($this->doc_ref)."'";
446 $sql .= ", ".((int) $this->fk_doc);
447 $sql .= ", ".((int) $this->fk_docdet);
448 $sql .= ", ".(!empty($this->thirdparty_code) ? ("'".$this->db->escape($this->thirdparty_code)."'") : "NULL");
449 $sql .= ", ".(!empty($this->subledger_account) ? ("'".$this->db->escape($this->subledger_account)."'") : "NULL");
450 $sql .= ", ".(!empty($this->subledger_label) ? ("'".$this->db->escape($this->subledger_label)."'") : "NULL");
451 $sql .= ", '".$this->db->escape($this->numero_compte)."'";
452 $sql .= ", ".(!empty($this->label_compte) ? ("'".$this->db->escape($this->label_compte)."'") : "NULL");
453 $sql .= ", '".$this->db->escape($this->label_operation)."'";
454 $sql .= ", ".((float) $this->debit);
455 $sql .= ", ".((float) $this->credit);
456 $sql .= ", ".((float) $this->montant);
457 $sql .= ", ".(!empty($this->sens) ? ("'".$this->db->escape($this->sens)."'") : "NULL");
458 $sql .= ", '".$this->db->escape((string) $this->fk_user_author)."'";
459 $sql .= ", '".$this->db->idate($now)."'";
460 $sql .= ", '".$this->db->escape($this->code_journal)."'";
461 $sql .= ", ".(!empty($this->journal_label) ? ("'".$this->db->escape($this->journal_label)."'") : "NULL");
462 $sql .= ", ".((int) $this->piece_num);
463 $sql .= ", '".$this->db->escape($this->ref)."'";
464 $sql .= ", ".(!isset($this->entity) ? ((int) $conf->entity) : ((int) $this->entity));
465 $sql .= ")";
466
467 $resql = $this->db->query($sql);
468 if ($resql) {
469 $id = $this->db->last_insert_id($this->db->prefix().$this->table_element);
470
471 if ($id > 0) {
472 $this->id = $id;
473 $result = 0;
474 } else {
475 $result = -2;
476 $error++;
477 $this->errors[] = 'Error Create Error '.$result.' lecture ID';
478 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
479 }
480 } else {
481 $result = -1;
482 $error++;
483 $this->errors[] = 'Error '.$this->db->lasterror();
484 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
485 }
486 } else { // Already exists
487 $result = -3;
488 $error++;
489 $this->error = 'BookkeepingRecordAlreadyExists';
490 $this->errors[] = $langs->trans('WarningBookkeepingRecordAlreadyExists', $this->doc_type, $this->fk_doc, $this->fk_docdet);
491
492 dol_syslog(get_class($this).":: create duplicate record detected: doc_type={$this->doc_type}, doc_ref={$this->doc_ref}, fk_doc={((int) $this->fk_doc)}, fk_docdet={((int) $this->fk_docdet)}", LOG_WARNING);
493 }
494 } else {
495 $result = -5;
496 $error++;
497 $this->errors[] = 'Error '.$this->db->lasterror();
498 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
499 }
500
501 // Call triggers
502 if (! $error && ! $notrigger) {
503 $result = $this->call_trigger('BOOKKEEPING_CREATE', $user);
504 if ($result < 0) {
505 $error++;
506 }
507 }
508
509 // Commit or rollback
510 if ($error) {
511 $this->db->rollback();
512 return -1 * $error;
513 } else {
514 $this->db->commit();
515 return $result;
516 }
517 }
518
536 public function createFromValues($doc_date, $doc_ref, $doc_type, $fk_doc, $fk_docdet, $numero_compte, $label_compte, $label_operation, $amount, $code_journal, $journal_label, $subledger_account)
537 {
538 global $conf, $langs, $user;
539
540 $result = 0;
541
542 if (!empty($amount)) {
543 $this->doc_date = $doc_date;
544 $this->doc_ref = $doc_ref;
545 $this->doc_type = $doc_type;
546 $this->fk_doc = $fk_doc;
547 $this->fk_docdet = $fk_docdet;
548
549 $this->numero_compte = $numero_compte;
550 $this->label_compte = $label_compte;
551
552 $this->label_operation = $label_operation;
553 $this->subledger_account = $subledger_account;
554
555 $this->montant = $amount;
556 $this->sens = ($amount >= 0) ? 'D' : 'C';
557 $this->debit = ($amount >= 0 ? $amount : 0);
558 $this->credit = ($amount < 0 ? -$amount : 0);
559
560 $this->code_journal = $code_journal;
561 $this->journal_label = $journal_label;
562
563 $this->fk_user_author = $user->id;
564 $this->entity = $conf->entity;
565
566 $result = $this->create($user);
567 if ($result < 0) {
568 if ($this->error == 'BookkeepingRecordAlreadyExists') {
569 $warning = $langs->trans('WarningBookkeepingRecordAlreadyExists', $this->doc_type, $this->fk_doc, $this->fk_docdet);
570 $this->warnings[] = $warning;
571 dol_syslog(__METHOD__.' '.$warning, LOG_WARNING);
572 } else {
573 dol_syslog(__METHOD__.' '.$this->errorsToString(), LOG_ERR);
574 }
575 }
576 }
577
578 return $result;
579 }
580
591 public function getNomUrl($withpicto = 0, $option = '', $notooltip = 0, $morecss = '', $save_lastsearch_value = -1)
592 {
593 global $db, $conf, $langs;
594 global $dolibarr_main_authentication, $dolibarr_main_demo;
595 global $menumanager, $hookmanager;
596
597 if (!empty($conf->dol_no_mouse_hover)) {
598 $notooltip = 1; // Force disable tooltips
599 }
600
601 $result = '';
602 $companylink = '';
603
604 $label = '<u>'.$langs->trans("Transaction").'</u>';
605 $label .= '<br>';
606 $label .= '<b>'.$langs->trans('NumberingShort').':</b> '.$this->piece_num;
607 $label .= '<br>';
608 $label .= '<b>'.$langs->trans('Ref').':</b> '.$this->ref;
609
610 $baseurl = DOL_URL_ROOT.'/accountancy/bookkeeping/card.php';
611 $query = ['piece_num' => $this->piece_num];
612
613 if ($option != 'nolink') {
614 // Add param to save lastsearch_values or not
615 $add_save_lastsearch_values = ($save_lastsearch_value == 1 ? 1 : 0);
616 if ($save_lastsearch_value == -1 && isset($_SERVER["PHP_SELF"]) && preg_match('/list\.php/', $_SERVER["PHP_SELF"])) {
617 $add_save_lastsearch_values = 1;
618 }
619 if ($add_save_lastsearch_values) {
620 $query += ['save_lastsearch_values' => 1];
621 }
622 }
623 $url = dolBuildUrl($baseurl, $query);
624
625 $linkclose = '';
626 if (empty($notooltip)) {
627 if (getDolGlobalString('MAIN_OPTIMIZEFORTEXTBROWSER')) {
628 $label = $langs->trans("ShowTransaction");
629 $linkclose .= ' alt="'.dolPrintHTMLForAttribute($label).'"';
630 }
631 $linkclose .= ' title="'.dolPrintHTMLForAttribute($label).'"';
632 $linkclose .= ' class="classfortooltip'.($morecss ? ' '.$morecss : '').'"';
633 } else {
634 $linkclose = ($morecss ? ' class="'.$morecss.'"' : '');
635 }
636
637 $linkstart = '<a href="'.$url.'"';
638 $linkstart .= $linkclose.'>';
639 $linkend = '</a>';
640
641 $result .= $linkstart;
642 if ($withpicto) {
643 $result .= img_object(($notooltip ? '' : $label), ($this->picto ? $this->picto : 'generic'), ($notooltip ? (($withpicto != 2) ? 'class="paddingright"' : '') : 'class="'.(($withpicto != 2) ? 'paddingright ' : '').'classfortooltip"'), 0, 0, $notooltip ? 0 : 1);
644 }
645 if ($withpicto != 2) {
646 $result .= $this->piece_num;
647 }
648 $result .= $linkend;
649 //if ($withpicto != 2) $result.=(($addlabel && $this->label) ? $sep . dol_trunc($this->label, ($addlabel > 1 ? $addlabel : 0)) : '');
650
651 global $action;
652 $hookmanager->initHooks(array($this->element . 'dao'));
653 $parameters = array('id' => $this->id, 'getnomurl' => &$result);
654 $reshook = $hookmanager->executeHooks('getNomUrl', $parameters, $this, $action); // Note that $action and $object may have been modified by some hooks
655 if ($reshook > 0) {
656 $result = $hookmanager->resPrint;
657 } else {
658 $result .= $hookmanager->resPrint;
659 }
660 return $result;
661 }
662
671 public function createStd(User $user, $notrigger = 0, $mode = '')
672 {
673 global $conf, $langs;
674
675 $langs->loadLangs(array("accountancy", "bills", "compta"));
676
677 dol_syslog(__METHOD__, LOG_DEBUG);
678
679 $error = 0;
680
681 // Clean parameters
682 if (isset($this->doc_type)) {
683 $this->doc_type = trim($this->doc_type);
684 }
685 if (isset($this->doc_ref)) {
686 $this->doc_ref = trim($this->doc_ref);
687 }
688 if (isset($this->fk_doc)) {
689 $this->fk_doc = (int) $this->fk_doc;
690 }
691 if (isset($this->fk_docdet)) {
692 $this->fk_docdet = (int) $this->fk_docdet;
693 }
694 if (isset($this->thirdparty_code)) {
695 $this->thirdparty_code = trim($this->thirdparty_code);
696 }
697 if (isset($this->subledger_account)) {
698 $this->subledger_account = trim($this->subledger_account);
699 }
700 if (isset($this->subledger_label)) {
701 $this->subledger_label = trim($this->subledger_label);
702 }
703 if (isset($this->numero_compte)) {
704 $this->numero_compte = trim($this->numero_compte);
705 }
706 if (isset($this->label_compte)) {
707 $this->label_compte = trim($this->label_compte);
708 }
709 if (isset($this->label_operation)) {
710 $this->label_operation = trim($this->label_operation);
711 }
712 if (isset($this->sens)) {
713 $this->sens = trim($this->sens);
714 }
715 if (isset($this->import_key)) {
716 $this->import_key = trim($this->import_key);
717 }
718 if (isset($this->code_journal)) {
719 $this->code_journal = trim($this->code_journal);
720 }
721 if (isset($this->journal_label)) {
722 $this->journal_label = trim($this->journal_label);
723 }
724 if (isset($this->piece_num)) {
725 $this->piece_num = (int) $this->piece_num;
726 }
727 if (empty($this->debit)) {
728 $this->debit = 0;
729 }
730 if (empty($this->credit)) {
731 $this->credit = 0;
732 }
733 if (empty($this->montant)) {
734 $this->montant = 0;
735 }
736
737 $result = $this->validBookkeepingDate($this->doc_date);
738 if ($result < 0) {
739 return -1;
740 } elseif ($result == 0) {
741 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
742 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod');
743 } else {
744 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod');
745 }
746 return -1;
747 }
748
749 $this->debit = (float) price2num($this->debit, 'MT');
750 $this->credit = (float) price2num($this->credit, 'MT');
751 $this->montant = (float) price2num($this->montant, 'MT');
752
753 $now = dol_now();
754
755 // Check parameters
756 $this->journal_label = $langs->trans($this->journal_label);
757
758 // Insert request
759 $sql = 'INSERT INTO '.$this->db->prefix().$this->table_element.$mode.' (';
760 $sql .= 'doc_date,';
761 $sql .= 'date_lim_reglement,';
762 $sql .= 'doc_type,';
763 $sql .= 'doc_ref,';
764 $sql .= 'fk_doc,';
765 $sql .= 'fk_docdet,';
766 $sql .= 'thirdparty_code,';
767 $sql .= 'subledger_account,';
768 $sql .= 'subledger_label,';
769 $sql .= 'numero_compte,';
770 $sql .= 'label_compte,';
771 $sql .= 'label_operation,';
772 $sql .= 'debit,';
773 $sql .= 'credit,';
774 $sql .= 'montant,';
775 $sql .= 'sens,';
776 $sql .= 'fk_user_author,';
777 $sql .= 'date_creation,';
778 $sql .= 'code_journal,';
779 $sql .= 'journal_label,';
780 $sql .= 'piece_num,';
781 $sql .= 'ref,';
782 $sql .= 'entity';
783 $sql .= ') VALUES (';
784 $sql .= ' '.(isDolTms($this->doc_date) ? "'".$this->db->idate($this->doc_date)."'" : 'NULL').',';
785 $sql .= ' '.(isDolTms($this->date_lim_reglement) ? "'".$this->db->idate($this->date_lim_reglement)."'" : 'NULL').',';
786 $sql .= ' '.(!isset($this->doc_type) ? 'NULL' : "'".$this->db->escape($this->doc_type)."'").',';
787 $sql .= ' '.(!isset($this->doc_ref) ? 'NULL' : "'".$this->db->escape($this->doc_ref)."'").',';
788 $sql .= ' '.(empty($this->fk_doc) ? '0' : (int) $this->fk_doc).',';
789 $sql .= ' '.(empty($this->fk_docdet) ? '0' : (int) $this->fk_docdet).',';
790 $sql .= ' '.(!isset($this->thirdparty_code) ? 'NULL' : "'".$this->db->escape($this->thirdparty_code)."'").',';
791 $sql .= ' '.(!isset($this->subledger_account) ? 'NULL' : "'".$this->db->escape($this->subledger_account)."'").',';
792 $sql .= ' '.(!isset($this->subledger_label) ? 'NULL' : "'".$this->db->escape($this->subledger_label)."'").',';
793 $sql .= ' '.(!isset($this->numero_compte) ? 'NULL' : "'".$this->db->escape($this->numero_compte)."'").',';
794 $sql .= ' '.(!isset($this->label_compte) ? 'NULL' : "'".$this->db->escape($this->label_compte)."'").',';
795 $sql .= ' '.(!isset($this->label_operation) ? 'NULL' : "'".$this->db->escape($this->label_operation)."'").',';
796 $sql .= ' '.(!isset($this->debit) ? 'NULL' : ((float) $this->debit)).',';
797 $sql .= ' '.(!isset($this->credit) ? 'NULL' : ((float) $this->credit)).',';
798 $sql .= ' '.(!isset($this->montant) ? 'NULL' : ((float) $this->montant)).',';
799 $sql .= ' '.(!isset($this->sens) ? 'NULL' : "'".$this->db->escape($this->sens)."'").',';
800 $sql .= ' '.((int) $user->id).',';
801 $sql .= ' '."'".$this->db->idate($now)."',";
802 $sql .= ' '.(empty($this->code_journal) ? 'NULL' : "'".$this->db->escape($this->code_journal)."'").',';
803 $sql .= ' '.(empty($this->journal_label) ? 'NULL' : "'".$this->db->escape($this->journal_label)."'").',';
804 $sql .= ' '.(empty($this->piece_num) ? 'NULL' : $this->db->escape((string) $this->piece_num)).',';
805 $sql .= ' '.(empty($this->ref) ? "''" : "'".$this->db->escape($this->ref)."'").',';
806 $sql .= ' '.(!isset($this->entity) ? ((int) $conf->entity) : ((int) $this->entity));
807 $sql .= ')';
808
809 $this->db->begin();
810
811 $resql = $this->db->query($sql);
812 if (!$resql) {
813 $error++;
814 $this->errors[] = 'Error '.$this->db->lasterror();
815 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
816 }
817
818 if (!$error) {
819 $this->id = $this->db->last_insert_id($this->db->prefix().$this->table_element.$mode);
820 // Call triggers
821 if (! $notrigger) {
822 $result = $this->call_trigger('BOOKKEEPING_CREATE', $user);
823 if ($result < 0) {
824 $error++;
825 }
826 }
827 }
828
829 // Commit or rollback
830 if ($error) {
831 $this->db->rollback();
832
833 return -1 * $error;
834 } else {
835 $this->db->commit();
836
837 return $this->id;
838 }
839 }
840
849 public function fetch($id, $ref = null, $mode = '')
850 {
851 global $conf;
852
853 dol_syslog(__METHOD__, LOG_DEBUG);
854
855 $sql = 'SELECT';
856 $sql .= ' t.rowid,';
857 $sql .= " t.doc_date,";
858 $sql .= " t.date_lim_reglement,";
859 $sql .= " t.doc_type,";
860 $sql .= " t.doc_ref,";
861 $sql .= " t.fk_doc,";
862 $sql .= " t.fk_docdet,";
863 $sql .= " t.thirdparty_code,";
864 $sql .= " t.subledger_account,";
865 $sql .= " t.subledger_label,";
866 $sql .= " t.numero_compte,";
867 $sql .= " t.label_compte,";
868 $sql .= " t.label_operation,";
869 $sql .= " t.debit,";
870 $sql .= " t.credit,";
871 $sql .= " t.montant as amount,";
872 $sql .= " t.sens,";
873 $sql .= " t.fk_user_author,";
874 $sql .= " t.import_key,";
875 $sql .= " t.code_journal,";
876 $sql .= " t.journal_label,";
877 $sql .= " t.piece_num,";
878 $sql .= " t.ref,";
879 $sql .= " t.date_creation,";
880 // In llx_accounting_bookkeeping_tmp, date_export
881 if (!$mode) {
882 $sql .= " t.date_export,";
883 }
884 $sql .= " t.date_validated as date_validation";
885 $sql .= ' FROM '.$this->db->prefix().$this->table_element.$mode.' as t';
886 $sql .= ' WHERE 1 = 1';
887 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
888 if (null !== $ref) {
889 $sql .= " AND t.rowid = ".((int) $ref);
890 } else {
891 $sql .= " AND t.rowid = ".((int) $id);
892 }
893
894 $resql = $this->db->query($sql);
895 if ($resql) {
896 $numrows = $this->db->num_rows($resql);
897 if ($numrows) {
898 $obj = $this->db->fetch_object($resql);
899
900 $this->id = $obj->rowid;
901
902 $this->doc_date = $this->db->jdate($obj->doc_date);
903 $this->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
904 $this->doc_type = $obj->doc_type;
905 $this->doc_ref = $obj->doc_ref;
906 $this->fk_doc = $obj->fk_doc;
907 $this->fk_docdet = $obj->fk_docdet;
908 $this->thirdparty_code = $obj->thirdparty_code;
909 $this->subledger_account = $obj->subledger_account;
910 $this->subledger_label = $obj->subledger_label;
911 $this->numero_compte = $obj->numero_compte;
912 $this->label_compte = $obj->label_compte;
913 $this->label_operation = $obj->label_operation;
914 $this->debit = $obj->debit;
915 $this->credit = $obj->credit;
916 $this->montant = $obj->amount;
917 $this->amount = $obj->amount;
918 $this->sens = $obj->sens;
919 $this->fk_user_author = $obj->fk_user_author;
920 $this->import_key = $obj->import_key;
921 $this->code_journal = $obj->code_journal;
922 $this->journal_label = $obj->journal_label;
923 $this->piece_num = $obj->piece_num;
924 $this->date_creation = $this->db->jdate($obj->date_creation);
925 if (!$mode) {
926 $this->date_export = $this->db->jdate($obj->date_export);
927 }
928 $this->ref = $obj->ref;
929 $this->date_validation = isset($obj->date_validation) ? $this->db->jdate($obj->date_validation) : '';
930 }
931 $this->db->free($resql);
932
933 if ($numrows) {
934 return 1;
935 } else {
936 return 0;
937 }
938 } else {
939 $this->errors[] = 'Error '.$this->db->lasterror();
940 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
941
942 return -1;
943 }
944 }
945
946
960 public function fetchAllByAccount($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, array $filter = array(), $filtermode = 'AND', $option = 0, $countonly = 0)
961 {
962 global $conf;
963
964 dol_syslog(__METHOD__, LOG_DEBUG);
965
966 $this->lines = array();
967 $num = 0;
968
969 $sql = 'SELECT';
970 if ($countonly) {
971 $sql .= ' COUNT(t.rowid) as nb';
972 } else {
973 $sql .= ' t.rowid,';
974 $sql .= " t.doc_date,";
975 $sql .= " t.doc_type,";
976 $sql .= " t.doc_ref,";
977 $sql .= " t.fk_doc,";
978 $sql .= " t.fk_docdet,";
979 $sql .= " t.thirdparty_code,";
980 $sql .= " t.subledger_account,";
981 $sql .= " t.subledger_label,";
982 $sql .= " t.numero_compte,";
983 $sql .= " t.label_compte,";
984 $sql .= " t.label_operation,";
985 $sql .= " t.debit,";
986 $sql .= " t.credit,";
987 $sql .= " t.montant as amount,";
988 $sql .= " t.sens,";
989 $sql .= " t.multicurrency_amount,";
990 $sql .= " t.multicurrency_code,";
991 $sql .= " t.matching_general,";
992 $sql .= " t.lettering_code,";
993 $sql .= " t.date_lettering,";
994 $sql .= " t.fk_user_author,";
995 $sql .= " t.import_key,";
996 $sql .= " t.code_journal,";
997 $sql .= " t.journal_label,";
998 $sql .= " t.piece_num,";
999 $sql .= " t.ref,";
1000 $sql .= " t.date_creation,";
1001 $sql .= " t.date_export,";
1002 $sql .= " t.date_validated as date_validation,";
1003 $sql .= " t.date_lim_reglement,";
1004 $sql .= " t.import_key";
1005 }
1006 // Manage filter
1007 $sqlwhere = array();
1008 if (count($filter) > 0) {
1009 foreach ($filter as $key => $value) {
1010 if ($key == 't.doc_date>=') {
1011 $sqlwhere[] = "t.doc_date >= '".$this->db->idate((int) $value)."'";
1012 } elseif ($key == 't.doc_date<=') {
1013 $sqlwhere[] = "t.doc_date <= '".$this->db->idate((int) $value)."'";
1014 } elseif ($key == 't.doc_date>') {
1015 $sqlwhere[] = "t.doc_date > '".$this->db->idate((int) $value)."'";
1016 } elseif ($key == 't.doc_date<') {
1017 $sqlwhere[] = "t.doc_date < '".$this->db->idate((int) $value)."'";
1018 } elseif ($key == 't.numero_compte>=') {
1019 $sqlwhere[] = "t.numero_compte >= '".$this->db->escape($value)."'";
1020 } elseif ($key == 't.numero_compte<=') {
1021 $sqlwhere[] = "t.numero_compte <= '".$this->db->escape($value)."'";
1022 } elseif ($key == 't.subledger_account>=') {
1023 $sqlwhere[] = "t.subledger_account >= '".$this->db->escape($value)."'";
1024 } elseif ($key == 't.subledger_account<=') {
1025 $sqlwhere[] = "t.subledger_account <= '".$this->db->escape($value)."'";
1026 } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1027 $sqlwhere[] = $this->db->sanitize($key).' = '.((int) $value);
1028 } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1029 $sqlwhere[] = $this->db->sanitize($key)." LIKE '".$this->db->escape($this->db->escapeforlike($value))."%'";
1030 } elseif ($key == 't.date_creation>=') {
1031 $sqlwhere[] = "t.date_creation >= '".$this->db->idate((int) $value)."'";
1032 } elseif ($key == 't.date_creation<=') {
1033 $sqlwhere[] = "t.date_creation <= '".$this->db->idate((int) $value)."'";
1034 } elseif ($key == 't.date_export>=') {
1035 $sqlwhere[] = "t.date_export >= '".$this->db->idate((int) $value)."'";
1036 } elseif ($key == 't.date_export<=') {
1037 $sqlwhere[] = "t.date_export <= '".$this->db->idate((int) $value)."'";
1038 } elseif ($key == 't.date_validated>=') {
1039 $sqlwhere[] = "t.date_validated >= '".$this->db->idate((int) $value)."'";
1040 } elseif ($key == 't.date_validated<=') {
1041 $sqlwhere[] = "t.date_validated <= '".$this->db->idate((int) $value)."'";
1042 } elseif ($key == 't.date_lim_reglement>=') {
1043 $sqlwhere[] = "t.date_lim_reglement>='".$this->db->idate((int) $value)."'";
1044 } elseif ($key == 't.date_lim_reglement<=') {
1045 $sqlwhere[] = "t.date_lim_reglement<='".$this->db->idate((int) $value)."'";
1046 } elseif ($key == 't.credit' || $key == 't.debit') {
1047 $sqlwhere[] = natural_search($key, $value, 1, 1);
1048 } elseif ($key == 't.reconciled_option') {
1049 $sqlwhere[] = 't.lettering_code IS NULL';
1050 } elseif ($key == 't.code_journal' && !empty($value)) {
1051 if (is_array($value)) {
1052 $sqlwhere[] = natural_search("t.code_journal", implode(',', $value), 3, 1);
1053 } else {
1054 $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1055 }
1056 } elseif ($key == 't.search_accounting_code_in' && !empty($value)) {
1057 $sqlwhere[] = "t.numero_compte IN (".$this->db->sanitize($value, 1).")";
1058 } else {
1059 $sqlwhere[] = natural_search($key, $value, 0, 1);
1060 }
1061 }
1062 }
1063 $sql .= ' FROM '.$this->db->prefix().$this->table_element.' as t';
1064 $sql .= ' WHERE entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1065 if (count($sqlwhere) > 0) {
1066 $sql .= " AND ".implode(" ".$this->db->sanitize($filtermode)." ", $sqlwhere);
1067 }
1068 // Filter by ledger account or subledger account
1069 if (!empty($option)) {
1070 $sql .= " AND t.subledger_account IS NOT NULL";
1071 $sql .= " AND t.subledger_account <> ''";
1072 $sortfield = 't.subledger_account'.($sortfield ? ','.$sortfield : '');
1073 $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
1074 } else {
1075 $sortfield = 't.numero_compte'.($sortfield ? ','.$sortfield : '');
1076 $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
1077 }
1078
1079 if (!$countonly) {
1080 $sql .= $this->db->order($sortfield, $sortorder);
1081 if (!empty($limit)) {
1082 $sql .= $this->db->plimit($limit + 1, $offset);
1083 }
1084 }
1085
1086 $resql = $this->db->query($sql);
1087 if ($resql) {
1088 if ($countonly) {
1089 $obj = $this->db->fetch_object($resql);
1090 if ($obj) {
1091 $num = $obj->nb;
1092 }
1093 } else {
1094 $num = $this->db->num_rows($resql);
1095
1096 $i = 0;
1097 while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1098 $line = new BookKeepingLine($this->db);
1099
1100 $line->id = $obj->rowid;
1101
1102 $line->doc_date = $this->db->jdate($obj->doc_date);
1103 $line->doc_type = $obj->doc_type;
1104 $line->doc_ref = $obj->doc_ref;
1105 $line->fk_doc = $obj->fk_doc;
1106 $line->fk_docdet = $obj->fk_docdet;
1107 $line->thirdparty_code = $obj->thirdparty_code;
1108 $line->subledger_account = $obj->subledger_account;
1109 $line->subledger_label = $obj->subledger_label;
1110 $line->numero_compte = $obj->numero_compte;
1111 $line->label_compte = $obj->label_compte;
1112 $line->label_operation = $obj->label_operation;
1113 $line->debit = $obj->debit;
1114 $line->credit = $obj->credit;
1115 $line->montant = $obj->amount; // deprecated
1116 $line->amount = $obj->amount;
1117 $line->sens = $obj->sens;
1118 $line->multicurrency_amount = $obj->multicurrency_amount;
1119 $line->multicurrency_code = $obj->multicurrency_code;
1120 $line->matching_general = (bool) $obj->matching_general;
1121 $line->lettering_code = $obj->lettering_code;
1122 $line->date_lettering = $this->db->jdate($obj->date_lettering);
1123 $line->fk_user_author = $obj->fk_user_author;
1124 $line->import_key = $obj->import_key;
1125 $line->code_journal = $obj->code_journal;
1126 $line->journal_label = $obj->journal_label;
1127 $line->piece_num = $obj->piece_num;
1128 $line->ref = $obj->ref;
1129 $line->date_creation = $this->db->jdate($obj->date_creation);
1130 $line->date_export = $this->db->jdate($obj->date_export);
1131 $line->date_validation = $this->db->jdate($obj->date_validation);
1132 // Due date
1133 $line->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
1134 $line->import_key = $obj->import_key;
1135
1136 $this->lines[] = $line;
1137
1138 $i++;
1139 }
1140 }
1141 $this->db->free($resql);
1142
1143 return $num;
1144 } else {
1145 $this->errors[] = 'Error '.$this->db->lasterror();
1146 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1147
1148 return -1;
1149 }
1150 }
1151
1164 public function fetchAll($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, $filter = '', $filtermode = 'AND', $showAlreadyExportMovements = 1)
1165 {
1166 global $conf;
1167
1168 dol_syslog(__METHOD__, LOG_DEBUG);
1169
1170 $sql = 'SELECT';
1171 $sql .= ' t.rowid,';
1172 $sql .= " t.doc_date,";
1173 $sql .= " t.doc_type,";
1174 $sql .= " t.doc_ref,";
1175 $sql .= " t.fk_doc,";
1176 $sql .= " t.fk_docdet,";
1177 $sql .= " t.thirdparty_code,";
1178 $sql .= " t.subledger_account,";
1179 $sql .= " t.subledger_label,";
1180 $sql .= " t.numero_compte,";
1181 $sql .= " t.label_compte,";
1182 $sql .= " t.label_operation,";
1183 $sql .= " t.debit,";
1184 $sql .= " t.credit,";
1185 $sql .= " t.lettering_code,";
1186 $sql .= " t.date_lettering,";
1187 $sql .= " t.montant as amount,";
1188 $sql .= " t.sens,";
1189 $sql .= " t.fk_user_author,";
1190 $sql .= " t.import_key,";
1191 $sql .= " t.code_journal,";
1192 $sql .= " t.journal_label,";
1193 $sql .= " t.piece_num,";
1194 $sql .= " t.date_creation,";
1195 $sql .= " t.date_lim_reglement,";
1196 $sql .= " t.tms as date_modification,";
1197 $sql .= " t.date_export,";
1198 $sql .= " t.date_validated as date_validation";
1199 $sql .= ' FROM '.$this->db->prefix().$this->table_element.' as t';
1200 $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1201 if ($showAlreadyExportMovements == 0) {
1202 $sql .= " AND t.date_export IS NULL";
1203 }
1204
1205 // Manage filter
1206 if (is_array($filter)) { // deprecated, use $filter = USF syntax
1207 dol_syslog("You are using a deprecated use of fetchAll. filter parameter must be an USF string now.", LOG_WARNING);
1208 $sqlwhere = array();
1209 if (count($filter) > 0) {
1210 foreach ($filter as $key => $value) {
1211 if ($key == 't.doc_date') {
1212 $sqlwhere[] = $this->db->sanitize($key)." = '".$this->db->idate((int) $value)."'";
1213 } elseif ($key == 't.doc_date>=') {
1214 $sqlwhere[] = "t.doc_date >= '".$this->db->idate((int) $value)."'";
1215 } elseif ($key == 't.doc_date<=') {
1216 $sqlwhere[] = "t.doc_date <= '".$this->db->idate((int) $value)."'";
1217 } elseif ($key == 't.doc_date>') {
1218 $sqlwhere[] = "t.doc_date > '".$this->db->idate((int) $value)."'";
1219 } elseif ($key == 't.doc_date<') {
1220 $sqlwhere[] = "t.doc_date < '".$this->db->idate((int) $value)."'";
1221 } elseif ($key == 't.numero_compte>=') {
1222 $sqlwhere[] = "t.numero_compte >= '".$this->db->escape($value)."'";
1223 } elseif ($key == 't.numero_compte<=') {
1224 $sqlwhere[] = "t.numero_compte <= '".$this->db->escape($value)."'";
1225 } elseif ($key == 't.subledger_account>=') {
1226 $sqlwhere[] = "t.subledger_account >= '".$this->db->escape($value)."'";
1227 } elseif ($key == 't.subledger_account<=') {
1228 $sqlwhere[] = "t.subledger_account <= '".$this->db->escape($value)."'";
1229 } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1230 $sqlwhere[] = $this->db->sanitize($key).' = '.((int) $value);
1231 } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1232 $sqlwhere[] = $this->db->sanitize($key)." LIKE '".$this->db->escape($value)."%'";
1233 } elseif ($key == 't.date_creation>=') {
1234 $sqlwhere[] = "t.date_creation >= '".$this->db->idate((int) $value)."'";
1235 } elseif ($key == 't.date_creation<=') {
1236 $sqlwhere[] = "t.date_creation <= '".$this->db->idate((int) $value)."'";
1237 } elseif ($key == 't.tms>=') {
1238 $sqlwhere[] = "t.tms >= '".$this->db->idate((int) $value)."'";
1239 } elseif ($key == 't.tms<=') {
1240 $sqlwhere[] = "t.tms <= '".$this->db->idate((int) $value)."'";
1241 } elseif ($key == 't.date_export>=') {
1242 $sqlwhere[] = "t.date_export >= '".$this->db->idate((int) $value)."'";
1243 } elseif ($key == 't.date_export<=') {
1244 $sqlwhere[] = "t.date_export <= '".$this->db->idate((int) $value)."'";
1245 } elseif ($key == 't.date_validated>=') {
1246 $sqlwhere[] = "t.date_validated >= '".$this->db->idate((int) $value)."'";
1247 } elseif ($key == 't.date_validated<=') {
1248 $sqlwhere[] = "t.date_validated <= '".$this->db->idate((int) $value)."'";
1249 } elseif ($key == 't.credit' || $key == 't.debit') {
1250 $sqlwhere[] = natural_search($key, $value, 1, 1);
1251 } elseif ($key == 't.code_journal' && !empty($value)) {
1252 if (is_array($value)) {
1253 $sqlwhere[] = natural_search("t.code_journal", implode(',', $value), 3, 1);
1254 } else {
1255 $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1256 }
1257 } elseif ($key == 't.reconciled_option') {
1258 $sqlwhere[] = 't.lettering_code IS NULL';
1259 } else {
1260 $sqlwhere[] = natural_search($key, $value, 0, 1);
1261 }
1262 }
1263 }
1264 if (count($sqlwhere) > 0) {
1265 $sql .= " AND ".implode(" ".$this->db->sanitize($filtermode)." ", $sqlwhere);
1266 }
1267
1268 $filter = '';
1269 }
1270
1271 // Manage filter
1272 $errormessage = '';
1273 $sql .= forgeSQLFromUniversalSearchCriteria($filter, $errormessage);
1274 if ($errormessage) {
1275 $this->errors[] = $errormessage;
1276 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1277 return -1;
1278 }
1279
1280 if (!empty($sortfield)) {
1281 $sql .= $this->db->order($sortfield, $sortorder);
1282 }
1283 if (!empty($limit)) {
1284 $sql .= $this->db->plimit($limit + 1, $offset);
1285 }
1286 $this->lines = array();
1287
1288 $resql = $this->db->query($sql);
1289 if ($resql) {
1290 $num = $this->db->num_rows($resql);
1291
1292 $i = 0;
1293 while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1294 $line = new BookKeepingLine($this->db);
1295
1296 $line->id = $obj->rowid;
1297
1298 $line->doc_date = $this->db->jdate($obj->doc_date);
1299 $line->doc_type = $obj->doc_type;
1300 $line->doc_ref = $obj->doc_ref;
1301 $line->fk_doc = $obj->fk_doc;
1302 $line->fk_docdet = $obj->fk_docdet;
1303 $line->thirdparty_code = $obj->thirdparty_code;
1304 $line->subledger_account = $obj->subledger_account;
1305 $line->subledger_label = $obj->subledger_label;
1306 $line->numero_compte = $obj->numero_compte;
1307 $line->label_compte = $obj->label_compte;
1308 $line->label_operation = $obj->label_operation;
1309 $line->debit = $obj->debit;
1310 $line->credit = $obj->credit;
1311 $line->montant = $obj->amount; // deprecated
1312 $line->amount = $obj->amount;
1313 $line->sens = $obj->sens;
1314 $line->lettering_code = $obj->lettering_code;
1315 $line->date_lettering = $this->db->jdate($obj->date_lettering);
1316 $line->fk_user_author = $obj->fk_user_author;
1317 $line->import_key = $obj->import_key;
1318 $line->code_journal = $obj->code_journal;
1319 $line->journal_label = $obj->journal_label;
1320 $line->piece_num = $obj->piece_num;
1321 $line->date_creation = $this->db->jdate($obj->date_creation);
1322 $line->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
1323 $line->date_modification = $this->db->jdate($obj->date_modification);
1324 $line->date_export = $this->db->jdate($obj->date_export);
1325 $line->date_validation = $this->db->jdate($obj->date_validation);
1326
1327 $this->lines[] = $line;
1328
1329 $i++;
1330 }
1331 $this->db->free($resql);
1332
1333 return $num;
1334 } else {
1335 $this->errors[] = 'Error '.$this->db->lasterror();
1336 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1337 return -1;
1338 }
1339 }
1340
1353 public function fetchAllBalance($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, $filter = '', $filtermode = 'AND', $option = 0)
1354 {
1355 global $conf;
1356
1357 $this->lines = array();
1358
1359 dol_syslog(__METHOD__, LOG_DEBUG);
1360
1361 $sql = 'SELECT';
1362 $sql .= " t.numero_compte,";
1363 if (!empty($option)) {
1364 $sql .= " t.subledger_account,";
1365 $sql .= " t.subledger_label,";
1366 }
1367 $sql .= " SUM(t.debit) as debit,";
1368 $sql .= " SUM(t.credit) as credit";
1369 $sql .= ' FROM '.$this->db->prefix().$this->table_element.' as t';
1370 $sql .= ' WHERE entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1371
1372 // Manage filter
1373 if (is_array($filter)) {
1374 $sqlwhere = array();
1375 if (count($filter) > 0) {
1376 foreach ($filter as $key => $value) {
1377 if ($key == 't.doc_date') {
1378 $sqlwhere[] = $this->db->sanitize($key)." = '".$this->db->idate((int) $value)."'";
1379 } elseif ($key == 't.doc_date>=') {
1380 $sqlwhere[] = "t.doc_date >= '".$this->db->idate((int) $value)."'";
1381 } elseif ($key == 't.doc_date<=') {
1382 $sqlwhere[] = "t.doc_date <= '".$this->db->idate((int) $value)."'";
1383 } elseif ($key == 't.doc_date>') {
1384 $sqlwhere[] = "t.doc_date > '".$this->db->idate((int) $value)."'";
1385 } elseif ($key == 't.doc_date<') {
1386 $sqlwhere[] = "t.doc_date < '".$this->db->idate((int) $value)."'";
1387 } elseif ($key == 't.numero_compte>=') {
1388 $sqlwhere[] = "t.numero_compte >= '".$this->db->escape($value)."'";
1389 } elseif ($key == 't.numero_compte<=') {
1390 $sqlwhere[] = "t.numero_compte <= '".$this->db->escape($value)."'";
1391 } elseif ($key == 't.subledger_account>=') {
1392 $sqlwhere[] = "t.subledger_account >= '".$this->db->escape($value)."'";
1393 } elseif ($key == 't.subledger_account<=') {
1394 $sqlwhere[] = "t.subledger_account <= '".$this->db->escape($value)."'";
1395 } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1396 $sqlwhere[] = $this->db->sanitize($key)." = ".((int) $value);
1397 } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1398 $sqlwhere[] = $this->db->sanitize($key)." LIKE '".$this->db->escape($value)."%'";
1399 } elseif ($key == 't.subledger_label') {
1400 $sqlwhere[] = $this->db->sanitize($key)." LIKE '".$this->db->escape($value)."%'";
1401 } elseif ($key == 't.code_journal' && !empty($value)) {
1402 if (is_array($value)) {
1403 $sqlwhere[] = natural_search("t.code_journal", implode(',', $value), 3, 1);
1404 } else {
1405 $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1406 }
1407 } elseif ($key == 't.reconciled_option') {
1408 $sqlwhere[] = 't.lettering_code IS NULL';
1409 } else {
1410 $sqlwhere[] = $this->db->sanitize($key)." LIKE '%".$this->db->escape($this->db->escapeforlike($value))."%'";
1411 }
1412 }
1413 }
1414 if (count($sqlwhere) > 0) {
1415 $sql .= " AND ".implode(" ".$this->db->sanitize($filtermode)." ", $sqlwhere);
1416 }
1417
1418 $filter = '';
1419 }
1420
1421 // Manage filter
1422 $errormessage = '';
1423 $sql .= forgeSQLFromUniversalSearchCriteria($filter, $errormessage);
1424 if ($errormessage) {
1425 $this->errors[] = $errormessage;
1426 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1427 return -1;
1428 }
1429
1430 if (!empty($option)) {
1431 $sql .= " AND t.subledger_account IS NOT NULL";
1432 $sql .= " AND t.subledger_account <> ''";
1433 $sql .= " GROUP BY t.numero_compte, t.subledger_account, t.subledger_label";
1434 $sortfield = 't.subledger_account'.($sortfield ? ','.$sortfield : '');
1435 $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
1436 } else {
1437 $sql .= ' GROUP BY t.numero_compte';
1438 $sortfield = 't.numero_compte'.($sortfield ? ','.$sortfield : '');
1439 $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
1440 }
1441
1442 $sql .= $this->db->order($sortfield, $sortorder);
1443
1444 if (!empty($limit)) {
1445 $sql .= $this->db->plimit($limit + 1, $offset);
1446 }
1447
1448 //print $sql;
1449 $resql = $this->db->query($sql);
1450
1451 if ($resql) {
1452 $num = $this->db->num_rows($resql);
1453
1454 $i = 0;
1455 while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1456 $line = new BookKeepingLine($this->db);
1457
1458 $line->numero_compte = $obj->numero_compte;
1459 //$line->label_compte = $obj->label_compte;
1460 if (!empty($option)) {
1461 $line->subledger_account = $obj->subledger_account;
1462 $line->subledger_label = $obj->subledger_label;
1463 }
1464 $line->debit = $obj->debit;
1465 $line->credit = $obj->credit;
1466
1467 $this->lines[] = $line;
1468
1469 $i++;
1470 }
1471 $this->db->free($resql);
1472
1473 return $num;
1474 } else {
1475 $this->errors[] = 'Error '.$this->db->lasterror();
1476 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1477
1478 return -1;
1479 }
1480 }
1481
1490 public function update(User $user, $notrigger = 0, $mode = '')
1491 {
1492 global $langs;
1493 $error = 0;
1494
1495 dol_syslog(__METHOD__, LOG_DEBUG);
1496
1497 // Clean parameters
1498 if (isset($this->doc_type)) {
1499 $this->doc_type = trim($this->doc_type);
1500 }
1501 if (isset($this->doc_ref)) {
1502 $this->doc_ref = trim($this->doc_ref);
1503 }
1504 if (isset($this->fk_doc)) {
1505 $this->fk_doc = (int) $this->fk_doc;
1506 }
1507 if (isset($this->fk_docdet)) {
1508 $this->fk_docdet = (int) $this->fk_docdet;
1509 }
1510 if (isset($this->thirdparty_code)) {
1511 $this->thirdparty_code = trim($this->thirdparty_code);
1512 }
1513 if (isset($this->subledger_account)) {
1514 $this->subledger_account = trim($this->subledger_account);
1515 }
1516 if (isset($this->subledger_label)) {
1517 $this->subledger_label = trim($this->subledger_label);
1518 }
1519 if (isset($this->numero_compte)) {
1520 $this->numero_compte = trim($this->numero_compte);
1521 }
1522 if (isset($this->label_compte)) {
1523 $this->label_compte = trim($this->label_compte);
1524 }
1525 if (isset($this->label_operation)) {
1526 $this->label_operation = trim($this->label_operation);
1527 }
1528 if (isset($this->sens)) {
1529 $this->sens = trim($this->sens);
1530 }
1531 if (isset($this->import_key)) {
1532 $this->import_key = trim($this->import_key);
1533 }
1534 if (isset($this->code_journal)) {
1535 $this->code_journal = trim($this->code_journal);
1536 }
1537 if (isset($this->journal_label)) {
1538 $this->journal_label = trim($this->journal_label);
1539 }
1540 if (isset($this->piece_num)) {
1541 $this->piece_num = (int) $this->piece_num;
1542 }
1543
1544 $result = $this->canModifyBookkeeping($this->id, $mode);
1545 if ($result < 0) {
1546 return -1;
1547 } elseif ($result == 0) {
1548 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
1549 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod');
1550 } else {
1551 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod');
1552 }
1553 return -1;
1554 }
1555
1556 $this->debit = (float) price2num($this->debit, 'MT');
1557 $this->credit = (float) price2num($this->credit, 'MT');
1558 $this->montant = (float) price2num($this->montant, 'MT');
1559
1560 // Check parameters
1561 // Put here code to add a control on parameters values
1562
1563 // Update request
1564 $sql = 'UPDATE '.$this->db->prefix().$this->table_element.$mode.' SET';
1565 $sql .= ' doc_date = '.(isDolTms($this->doc_date) ? "'".$this->db->idate($this->doc_date)."'" : 'null').',';
1566 $sql .= ' doc_type = '.(isset($this->doc_type) ? "'".$this->db->escape($this->doc_type)."'" : "null").',';
1567 $sql .= ' doc_ref = '.(isset($this->doc_ref) ? "'".$this->db->escape($this->doc_ref)."'" : "null").',';
1568 $sql .= ' fk_doc = '.(isset($this->fk_doc) ? ((int) $this->fk_doc) : "null").',';
1569 $sql .= ' fk_docdet = '.(isset($this->fk_docdet) ? ((int) $this->fk_docdet) : "null").',';
1570 $sql .= ' thirdparty_code = '.(isset($this->thirdparty_code) ? "'".$this->db->escape($this->thirdparty_code)."'" : "null").',';
1571 $sql .= ' subledger_account = '.(isset($this->subledger_account) ? "'".$this->db->escape($this->subledger_account)."'" : "null").',';
1572 $sql .= ' subledger_label = '.(isset($this->subledger_label) ? "'".$this->db->escape($this->subledger_label)."'" : "null").',';
1573 $sql .= ' numero_compte = '.(isset($this->numero_compte) ? "'".$this->db->escape($this->numero_compte)."'" : "null").',';
1574 $sql .= ' label_compte = '.(isset($this->label_compte) ? "'".$this->db->escape($this->label_compte)."'" : "null").',';
1575 $sql .= ' label_operation = '.(isset($this->label_operation) ? "'".$this->db->escape($this->label_operation)."'" : "null").',';
1576 $sql .= ' debit = '.(isset($this->debit) ? ((float) $this->debit) : "null").',';
1577 $sql .= ' credit = '.(isset($this->credit) ? ((float) $this->credit) : "null").',';
1578 $sql .= ' montant = '.(isset($this->montant) ? ((float) $this->montant) : "null").',';
1579 $sql .= ' sens = '.(isset($this->sens) ? "'".$this->db->escape($this->sens)."'" : "null").',';
1580 $sql .= ' fk_user_author = '.(isset($this->fk_user_author) ? ((int) $this->fk_user_author) : "null").',';
1581 $sql .= ' import_key = '.(isset($this->import_key) ? "'".$this->db->escape($this->import_key)."'" : "null").',';
1582 $sql .= ' code_journal = '.(isset($this->code_journal) ? "'".$this->db->escape($this->code_journal)."'" : "null").',';
1583 $sql .= ' journal_label = '.(isset($this->journal_label) ? "'".$this->db->escape($this->journal_label)."'" : "null").',';
1584 $sql .= ' piece_num = '.(isset($this->piece_num) ? ((int) $this->piece_num) : "null");
1585 $sql .= ' WHERE rowid='.((int) $this->id);
1586
1587 $this->db->begin();
1588
1589 $resql = $this->db->query($sql);
1590 if (!$resql) {
1591 $error++;
1592 $this->errors[] = 'Error '.$this->db->lasterror();
1593 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1594 }
1595
1596 // Call triggers
1597 if (! $error && ! $notrigger) {
1598 $result = $this->call_trigger('BOOKKEEPING_MODIFY', $user);
1599 if ($result < 0) {
1600 $error++;
1601 }
1602 }
1603
1604 // Commit or rollback
1605 if ($error) {
1606 $this->db->rollback();
1607
1608 return -1 * $error;
1609 } else {
1610 $this->db->commit();
1611
1612 return 1;
1613 }
1614 }
1615
1625 public function updateByMvt($piece_num = '', $field = '', $value = '', $mode = '')
1626 {
1627 global $conf;
1628 $error = 0;
1629
1630 $sql_filter = $this->getCanModifyBookkeepingSQL();
1631 if (!isset($sql_filter)) {
1632 return -1;
1633 }
1634
1635 $this->db->begin();
1636
1637 $sql = "UPDATE ".$this->db->prefix().$this->table_element.$mode;
1638 $sql .= " SET ".$this->db->sanitize($field)." = ".(is_numeric($value) ? ((float) $value) : "'".$this->db->escape($value)."'");
1639 $sql .= " WHERE piece_num = ".((int) $piece_num);
1640 $sql .= " AND entity = " . ((int) $conf->entity);
1641 $sql .= $sql_filter;
1642
1643 $resql = $this->db->query($sql);
1644
1645 if (!$resql) {
1646 $error++;
1647 $this->errors[] = 'Error '.$this->db->lasterror();
1648 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1649 }
1650 if ($error) {
1651 $this->db->rollback();
1652
1653 return -1 * $error;
1654 } else {
1655 $this->db->commit();
1656
1657 return 1;
1658 }
1659 }
1660
1669 public function delete(User $user, $notrigger = 0, $mode = '')
1670 {
1671 global $langs;
1672
1673 dol_syslog(__METHOD__, LOG_DEBUG);
1674
1675 $result = $this->canModifyBookkeeping($this->id, $mode);
1676 if ($result < 0) {
1677 return -1;
1678 } elseif ($result == 0) {
1679 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
1680 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod');
1681 } else {
1682 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod');
1683 }
1684 return -1;
1685 }
1686
1687 $error = 0;
1688
1689 $this->db->begin();
1690
1691 // Call triggers
1692 if (!$notrigger) {
1693 $result = $this->call_trigger('BOOKKEEPING_DELETE', $user);
1694 if ($result < 0) {
1695 $error++;
1696 }
1697 }
1698
1699 if (!$error) {
1700 $sql = 'DELETE FROM '.$this->db->prefix().$this->table_element.$mode;
1701 $sql .= ' WHERE rowid='.((int) $this->id);
1702
1703 $resql = $this->db->query($sql);
1704 if (!$resql) {
1705 $error++;
1706 $this->errors[] = 'Error '.$this->db->lasterror();
1707 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1708 }
1709 }
1710
1711 // Commit or rollback
1712 if ($error) {
1713 $this->db->rollback();
1714
1715 return -1 * $error;
1716 } else {
1717 $this->db->commit();
1718
1719 return 1;
1720 }
1721 }
1722
1730 public function deleteByImportkey($importkey, $mode = '')
1731 {
1732 $this->db->begin();
1733
1734 $sql_filter = $this->getCanModifyBookkeepingSQL();
1735 if (!isset($sql_filter)) {
1736 return -1;
1737 }
1738
1739 // first check if line not yet in bookkeeping
1740 $sql = "DELETE";
1741 $sql .= " FROM ".$this->db->prefix().$this->table_element.$mode;
1742 $sql .= " WHERE import_key = '".$this->db->escape($importkey)."'";
1743 $sql .= $sql_filter;
1744
1745 $resql = $this->db->query($sql);
1746
1747 if (!$resql) {
1748 $this->errors[] = "Error ".$this->db->lasterror();
1749 dol_syslog(get_class($this)."::delete Error ".$this->db->lasterror(), LOG_ERR);
1750 $this->db->rollback();
1751 return -1;
1752 }
1753
1754 $this->db->commit();
1755 return 1;
1756 }
1757
1767 public function deleteByYearAndJournal($delyear = 0, $journal = '', $mode = '', $delmonth = 0)
1768 {
1769 global $conf, $langs;
1770
1771 if (empty($delyear) && empty($journal)) {
1772 $this->error = 'ErrorOneFieldRequired';
1773 return -1;
1774 }
1775 if (!empty($delmonth) && empty($delyear)) {
1776 $this->error = 'YearRequiredIfMonthDefined';
1777 return -2;
1778 }
1779
1780 $sql_filter = $this->getCanModifyBookkeepingSQL();
1781 if (!isset($sql_filter)) {
1782 return -1;
1783 }
1784
1785 $this->db->begin();
1786
1787 // Delete record in bookkeeping
1788 $sql = "DELETE";
1789 $sql .= " FROM ".$this->db->prefix().$this->table_element.$mode;
1790 $sql .= " WHERE 1 = 1";
1791 $sql .= dolSqlDateFilter('doc_date', 0, $delmonth, $delyear);
1792 if (!empty($journal)) {
1793 $sql .= " AND code_journal = '".$this->db->escape($journal)."'";
1794 }
1795 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1796 // Exclusion of validated entries at the time of deletion
1797 $sql .= " AND date_validated IS NULL";
1798 $sql .= $sql_filter;
1799
1800 // TODO: In a future we must forbid deletion if record is inside a closed fiscal period.
1801
1802 $resql = $this->db->query($sql);
1803
1804 if (!$resql) {
1805 $this->errors[] = "Error ".$this->db->lasterror();
1806 foreach ($this->errors as $errmsg) {
1807 dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1808 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1809 }
1810 $this->db->rollback();
1811 return -1;
1812 }
1813
1814 $this->db->commit();
1815 return 1;
1816 }
1817
1826 public function deleteMvtNum($piecenum, $mode = '', $notrigger = 0)
1827 {
1828 global $conf, $user;
1829
1830 $sql_filter = $this->getCanModifyBookkeepingSQL();
1831 if (!isset($sql_filter)) {
1832 return -1;
1833 }
1834
1835 $nbprocessed = 0;
1836 $error = 0;
1837
1838 $this->db->begin();
1839
1840 // Call triggers
1841 if (!$notrigger) {
1842 $result = $this->call_trigger('BOOKKEEPING_DELETE', $user);
1843 if ($result < 0) {
1844 $error++;
1845 }
1846 }
1847
1848 if (!$error) {
1849 // first check if line not yet in bookkeeping
1850 $sql = "DELETE";
1851 $sql .= " FROM ".$this->db->prefix().$this->table_element.$mode;
1852 $sql .= " WHERE piece_num = ".(int) $piecenum;
1853 $sql .= " AND date_validated IS NULL"; // For security, exclusion of validated entries at the time of deletion
1854 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1855 $sql .= $sql_filter;
1856
1857 $resql = $this->db->query($sql);
1858
1859 if (!$resql) {
1860 $this->errors[] = "Error ".$this->db->lasterror();
1861 foreach ($this->errors as $errmsg) {
1862 dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1863 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1864 }
1865 $this->db->rollback();
1866 return -1;
1867 } else {
1868 $nbprocessed = $this->db->affected_rows($resql);
1869 }
1870 }
1871
1872 // Commit or rollback
1873 if ($error) {
1874 $this->db->rollback();
1875 return -1 * $error;
1876 } else {
1877 $this->db->commit();
1878 return $nbprocessed;
1879 }
1880 }
1881
1889 public function createFromClone(User $user, $fromid)
1890 {
1891 dol_syslog(__METHOD__, LOG_DEBUG);
1892
1893 $error = 0;
1894 $object = new BookKeeping($this->db);
1895
1896 $this->db->begin();
1897
1898 // Load source object
1899 $object->fetch($fromid);
1900 // Reset object
1901 $object->id = 0;
1902
1903 // Clear fields
1904 // ...
1905
1906 // Create clone
1907 $object->context['createfromclone'] = 'createfromclone';
1908 $result = $object->create($user);
1909
1910 // Other options
1911 if ($result < 0) {
1912 $error++;
1913 $this->errors = $object->errors;
1914 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1915 }
1916
1917 unset($object->context['createfromclone']);
1918
1919 // End
1920 if (!$error) {
1921 $this->db->commit();
1922
1923 return $object->id;
1924 } else {
1925 $this->db->rollback();
1926
1927 return -1;
1928 }
1929 }
1930
1937 public function initAsSpecimen()
1938 {
1939 global $user;
1940
1941 $now = dol_now();
1942
1943 $this->id = 0;
1944 $this->doc_date = $now;
1945 $this->doc_type = '';
1946 $this->doc_ref = '';
1947 $this->fk_doc = 0;
1948 $this->fk_docdet = 0;
1949 $this->thirdparty_code = 'CU001';
1950 $this->subledger_account = '41100001';
1951 $this->subledger_label = 'My customer company';
1952 $this->numero_compte = '411';
1953 $this->label_compte = 'Customer';
1954 $this->label_operation = 'Sales of pea';
1955 $this->debit = 99.9;
1956 $this->credit = 0.0;
1957 $this->amount = 0.0;
1958 $this->sens = 'D';
1959 $this->fk_user_author = $user->id;
1960 $this->import_key = '20201027';
1961 $this->code_journal = 'VT';
1962 $this->journal_label = 'Journal de vente';
1963 $this->piece_num = 1234;
1964 $this->date_creation = $now;
1965
1966 return 1;
1967 }
1968
1976 public function fetchPerMvt($piecenum, $mode = '')
1977 {
1978 global $conf;
1979
1980 $sql = "SELECT piece_num, ref, doc_date, code_journal, journal_label, doc_ref, doc_type, fk_doc,";
1981 $sql .= " date_creation, tms as date_modification, date_validated as date_validation, date_lim_reglement, import_key";
1982 // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1983 if ($mode != "_tmp") {
1984 $sql .= ", date_export";
1985 }
1986 $sql .= " FROM ".$this->db->prefix().$this->table_element.$mode;
1987 $sql .= " WHERE piece_num = ".((int) $piecenum);
1988 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1989
1990 dol_syslog(__METHOD__, LOG_DEBUG);
1991 $result = $this->db->query($sql);
1992 if ($result) {
1993 $obj = $this->db->fetch_object($result);
1994
1995 $this->piece_num = $obj->piece_num;
1996 $this->ref = $obj->ref;
1997 $this->code_journal = $obj->code_journal;
1998 $this->journal_label = $obj->journal_label;
1999 $this->doc_date = $this->db->jdate($obj->doc_date);
2000 $this->doc_ref = $obj->doc_ref;
2001 $this->doc_type = $obj->doc_type;
2002 $this->fk_doc = $obj->fk_doc;
2003 $this->date_creation = $this->db->jdate($obj->date_creation);
2004 $this->date_modification = $this->db->jdate($obj->date_modification);
2005 if ($mode != "_tmp") {
2006 $this->date_export = $this->db->jdate($obj->date_export);
2007 }
2008 $this->date_validation = $this->db->jdate($obj->date_validation);
2009 $this->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
2010 $this->import_key = $obj->import_key;
2011 } else {
2012 $this->error = "Error ".$this->db->lasterror();
2013 dol_syslog(__METHOD__.$this->error, LOG_ERR);
2014 return -1;
2015 }
2016
2017 return 1;
2018 }
2019
2026 public function getNextNumMvt($mode = '')
2027 {
2028 global $conf;
2029
2030 $sql = "SELECT MAX(piece_num) + 1 as max FROM ".$this->db->prefix().$this->table_element.$mode;
2031 $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2032
2033 dol_syslog(get_class($this)."::getNextNumMvt", LOG_DEBUG);
2034
2035 $result = $this->db->query($sql);
2036
2037 if ($result) {
2038 $obj = $this->db->fetch_object($result);
2039 if ($obj) {
2040 $result = $obj->max;
2041 }
2042 if (empty($result)) {
2043 $result = 1;
2044 }
2045 return $result;
2046 } else {
2047 $this->error = "Error ".$this->db->lasterror();
2048 dol_syslog(get_class($this)."::getNextNumMvt ".$this->error, LOG_ERR);
2049 return -1;
2050 }
2051 }
2052
2059 public function getNextNumRef()
2060 {
2061 global $langs, $conf;
2062 $langs->load("accountancy");
2063
2064 if (getDolGlobalString('BOOKKEEPING_ADDON')) {
2065 $mybool = false;
2066
2067 $file = getDolGlobalString('BOOKKEEPING_ADDON') . ".php";
2068 $classname = getDolGlobalString('BOOKKEEPING_ADDON');
2069
2070 // Include file with class
2071 $dirmodels = array_merge(array('/'), (array) $conf->modules_parts['models']);
2072 foreach ($dirmodels as $reldir) {
2073 $dir = dol_buildpath($reldir."core/modules/accountancy/");
2074
2075 // Load file with numbering class (if found)
2076 $mybool = ((bool) @include_once $dir.$file) || $mybool;
2077 }
2078
2079 if (!$mybool) {
2080 dol_print_error(null, "Failed to include file ".$file);
2081 return '';
2082 }
2083
2084 $obj = new $classname();
2086 '@phan-var-force ModeleNumRefBookkeeping $obj';
2087
2088 $numref = $obj->getNextValue($this);
2089
2090 if ($numref != "") {
2091 return $numref;
2092 } else {
2093 $this->error = $obj->error;
2094 //dol_print_error($this->db,get_class($this)."::getNextNumRef ".$obj->error);
2095 return "";
2096 }
2097 } else {
2098 print $langs->trans("Error")." ".$langs->trans("Error_BOOKKEEPING_ADDON_NotDefined");
2099 return "";
2100 }
2101 }
2102
2103
2104
2112 public function fetchAllPerMvt($piecenum, $mode = '')
2113 {
2114 global $conf;
2115
2116 $sql = "SELECT rowid, doc_date, doc_type,";
2117 $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
2118 $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
2119 $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, journal_label, piece_num,";
2120 $sql .= " date_creation, tms as date_modification, date_validated as date_validation";
2121 // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
2122 if ($mode != "_tmp") {
2123 $sql .= ", date_export";
2124 }
2125 $sql .= " FROM ".$this->db->prefix().$this->table_element.$mode;
2126 $sql .= " WHERE piece_num = ".((int) $piecenum);
2127 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2128
2129 dol_syslog(__METHOD__, LOG_DEBUG);
2130 $result = $this->db->query($sql);
2131 if ($result) {
2132 while ($obj = $this->db->fetch_object($result)) {
2133 $line = new BookKeepingLine($this->db);
2134
2135 $line->id = $obj->rowid;
2136
2137 $line->doc_date = $this->db->jdate($obj->doc_date);
2138 $line->doc_type = $obj->doc_type;
2139 $line->doc_ref = $obj->doc_ref;
2140 $line->fk_doc = $obj->fk_doc;
2141 $line->fk_docdet = $obj->fk_docdet;
2142 $line->thirdparty_code = $obj->thirdparty_code;
2143 $line->subledger_account = $obj->subledger_account;
2144 $line->subledger_label = $obj->subledger_label;
2145 $line->numero_compte = $obj->numero_compte;
2146 $line->label_compte = $obj->label_compte;
2147 $line->label_operation = $obj->label_operation;
2148 $line->debit = $obj->debit;
2149 $line->credit = $obj->credit;
2150 $line->montant = $obj->amount;
2151 $line->amount = $obj->amount;
2152 $line->sens = $obj->sens;
2153 $line->code_journal = $obj->code_journal;
2154 $line->journal_label = $obj->journal_label;
2155 $line->piece_num = $obj->piece_num;
2156 $line->date_creation = $obj->date_creation;
2157 $line->date_modification = $obj->date_modification;
2158 if ($mode != "_tmp") {
2159 $line->date_export = $obj->date_export;
2160 }
2161 $line->date_validation = $obj->date_validation;
2162
2163 $this->linesmvt[] = $line;
2164 }
2165 } else {
2166 $this->error = "Error ".$this->db->lasterror();
2167 dol_syslog(__METHOD__.$this->error, LOG_ERR);
2168 return -1;
2169 }
2170
2171 return 1;
2172 }
2173
2174 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2181 public function export_bookkeeping($model = 'ebp')
2182 {
2183 // phpcs:enable
2184 global $conf;
2185
2186 $sql = "SELECT rowid, doc_date, doc_type,";
2187 $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
2188 $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
2189 $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, piece_num,";
2190 $sql .= " date_validated as date_validation";
2191 $sql .= " FROM ".$this->db->prefix().$this->table_element;
2192 $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2193
2194 dol_syslog(get_class($this)."::export_bookkeeping", LOG_DEBUG);
2195
2196 $resql = $this->db->query($sql);
2197
2198 if ($resql) {
2199 $this->linesexport = array();
2200
2201 $num = $this->db->num_rows($resql);
2202 while ($obj = $this->db->fetch_object($resql)) {
2203 $line = new BookKeepingLine($this->db);
2204
2205 $line->id = $obj->rowid;
2206
2207 $line->doc_date = $this->db->jdate($obj->doc_date);
2208 $line->doc_type = $obj->doc_type;
2209 $line->doc_ref = $obj->doc_ref;
2210 $line->fk_doc = $obj->fk_doc;
2211 $line->fk_docdet = $obj->fk_docdet;
2212 $line->thirdparty_code = $obj->thirdparty_code;
2213 $line->subledger_account = $obj->subledger_account;
2214 $line->subledger_label = $obj->subledger_label;
2215 $line->numero_compte = $obj->numero_compte;
2216 $line->label_compte = $obj->label_compte;
2217 $line->label_operation = $obj->label_operation;
2218 $line->debit = $obj->debit;
2219 $line->credit = $obj->credit;
2220 $line->montant = $obj->amount;
2221 $line->amount = $obj->amount;
2222 $line->sens = $obj->sens;
2223 $line->code_journal = $obj->code_journal;
2224 $line->piece_num = $obj->piece_num;
2225 $line->date_validation = $obj->date_validation;
2226
2227 $this->linesexport[] = $line;
2228 }
2229 $this->db->free($resql);
2230
2231 return $num;
2232 } else {
2233 $this->error = "Error ".$this->db->lasterror();
2234 dol_syslog(get_class($this)."::export_bookkeeping ".$this->error, LOG_ERR);
2235 return -1;
2236 }
2237 }
2238
2246 public function transformTransaction($direction = 0, $piece_num = '')
2247 {
2248 global $conf;
2249
2250 $error = 0;
2251
2252 $sql_filter = $this->getCanModifyBookkeepingSQL();
2253
2254 if (!isset($sql_filter)) {
2255 return -1;
2256 }
2257
2258 $this->db->begin();
2259
2260 $tmpBookkeeping = new self($this->db);
2261 $tmpData = $this->db->getRow("SELECT doc_date, code_journal, ref FROM {$this->db->prefix()}accounting_bookkeeping_tmp WHERE piece_num = '{$this->db->escape($piece_num)}' AND entity = {$conf->entity}");
2262 $tmpBookkeeping->doc_date = $this->db->jdate($tmpData->doc_date);
2263 $tmpBookkeeping->code_journal = $tmpData->code_journal;
2264
2265 // Ref is copied from tmp only if defined => free num ref model has been used
2266 $ref = $tmpData->ref ?: $tmpBookkeeping->getNextNumRef();
2267 if ($direction == 0) {
2268 $next_piecenum = $this->getNextNumMvt();
2269 $now = dol_now();
2270
2271 if ($next_piecenum < 0) {
2272 $error++;
2273 }
2274
2275 // Store the new piece_num so caller can use it after transformTransaction()
2276 $this->piece_num = $next_piecenum;
2277
2278 if (!$error) {
2279 // Delete if there is an empty line
2280 $sql = 'DELETE FROM '.$this->db->prefix().$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity)." AND numero_compte IS NULL AND debit = 0 AND credit = 0";
2281 $resql = $this->db->query($sql);
2282 if (!$resql) {
2283 $error++;
2284 $this->errors[] = 'Error '.$this->db->lasterror();
2285 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2286 }
2287 }
2288
2289 if (!$error) {
2290 $sql = 'INSERT INTO '.$this->db->prefix().$this->table_element.' (doc_date, doc_type, ref,';
2291 $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
2292 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2293 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num, date_creation)';
2294 $sql .= ' SELECT doc_date, doc_type,' . "'".$this->db->escape($ref)."',";
2295 $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
2296 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2297 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, '.((int) $next_piecenum).", '".$this->db->idate($now)."'";
2298 $sql .= ' FROM '.$this->db->prefix().$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND numero_compte IS NOT NULL AND entity = ' .((int) $conf->entity);
2299 $sql .= $sql_filter;
2300 $resql = $this->db->query($sql);
2301 if (!$resql) {
2302 $error++;
2303 $this->errors[] = 'Error '.$this->db->lasterror();
2304 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2305 }
2306 }
2307
2308 if (!$error) {
2309 $sql = 'DELETE FROM '.$this->db->prefix().$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2310 $resql = $this->db->query($sql);
2311 if (!$resql) {
2312 $error++;
2313 $this->errors[] = 'Error '.$this->db->lasterror();
2314 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2315 }
2316 }
2317 } elseif ($direction == 1) {
2318 $sql = 'DELETE FROM '.$this->db->prefix().$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2319 $resql = $this->db->query($sql);
2320 if (!$resql) {
2321 $error++;
2322 $this->errors[] = 'Error '.$this->db->lasterror();
2323 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2324 }
2325
2326 if (!$error) {
2327 $sql = 'INSERT INTO '.$this->db->prefix().$this->table_element.'_tmp (doc_date, doc_type, ref,';
2328 $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
2329 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2330 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num)';
2331 $sql .= ' SELECT doc_date, doc_type,' . "'".$this->db->escape($ref)."',";
2332 $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
2333 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2334 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num';
2335 $sql .= ' FROM '.$this->db->prefix().$this->table_element.' WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2336 $sql .= $sql_filter;
2337 $resql = $this->db->query($sql);
2338 if (!$resql) {
2339 $error++;
2340 $this->errors[] = 'Error '.$this->db->lasterror();
2341 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2342 }
2343 }
2344
2345 if (!$error) {
2346 $sql = 'DELETE FROM '.$this->db->prefix().$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2347 $sql .= $sql_filter;
2348 $resql = $this->db->query($sql);
2349 if (!$resql) {
2350 $error++;
2351 $this->errors[] = 'Error '.$this->db->lasterror();
2352 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2353 }
2354 }
2355 }
2356 if (!$error) {
2357 $this->db->commit();
2358 return 1;
2359 } else {
2360 $this->db->rollback();
2361 return -1;
2362 }
2363 /*
2364 $sql = "DELETE FROM ";
2365 $sql .= " FROM " . $this->db->prefix() . "accounting_bookkeeping as ab";
2366 $sql .= " LEFT JOIN " . $this->db->prefix() . "accounting_account as aa ON aa.account_number = ab.numero_compte";
2367 $sql .= " AND aa.active = 1";
2368 $sql .= " INNER JOIN " . $this->db->prefix() . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2369 $sql .= " AND asy.rowid = " . ((int) $pcgver);
2370 $sql .= " AND ab.entity IN (" . getEntity('accountancy') . ")";
2371 $sql .= " ORDER BY account_number ASC";
2372 */
2373 }
2374
2375 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2388 public function select_account($selectid, $htmlname = 'account', $showempty = 0, $event = array(), $select_in = 0, $select_out = 0, $aabase = '')
2389 {
2390 // phpcs:enable
2391 global $conf;
2392
2393 require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
2394
2395 $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2396
2397 $sql = "SELECT DISTINCT ab.numero_compte as account_number, aa.label as label, aa.rowid as rowid, aa.fk_pcg_version";
2398 $sql .= " FROM ".$this->db->prefix().$this->table_element." as ab";
2399 $sql .= " LEFT JOIN ".$this->db->prefix()."accounting_account as aa ON aa.account_number = ab.numero_compte";
2400 $sql .= " AND aa.active = 1";
2401 $sql .= " INNER JOIN ".$this->db->prefix()."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2402 $sql .= " AND asy.rowid = ".((int) $pcgver);
2403 $sql .= " AND ab.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2404 $sql .= " ORDER BY account_number ASC";
2405
2406 dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2407 $resql = $this->db->query($sql);
2408
2409 if (!$resql) {
2410 $this->error = "Error ".$this->db->lasterror();
2411 dol_syslog(get_class($this)."::select_account ".$this->error, LOG_ERR);
2412 return "-1";
2413 }
2414
2415 $out = ajax_combobox($htmlname, $event);
2416
2417 $options = array();
2418 $selected = 0;
2419
2420 while ($obj = $this->db->fetch_object($resql)) {
2421 $label = length_accountg($obj->account_number).' - '.$obj->label;
2422
2423 $select_value_in = $obj->rowid;
2424 $select_value_out = $obj->rowid;
2425
2426 if ($select_in == 1) {
2427 $select_value_in = $obj->account_number;
2428 }
2429 if ($select_out == 1) {
2430 $select_value_out = $obj->account_number;
2431 }
2432
2433 // Remember guy's we store in database llx_facturedet the rowid of accounting_account and not the account_number
2434 // Because same account_number can be share between different accounting_system and do have the same meaning
2435 if (($selectid != '') && $selectid == $select_value_in) {
2436 $selected = $select_value_out;
2437 }
2438
2439 $options[$select_value_out] = $label;
2440 }
2441
2442 $out .= Form::selectarray($htmlname, $options, $selected, $showempty, 0, 0, '', 0, 0, 0, '', 'maxwidth300');
2443 $this->db->free($resql);
2444 return $out;
2445 }
2446
2454 public function getRootAccount($account = null)
2455 {
2456 global $conf;
2457 $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2458
2459 $sql = "SELECT root.rowid, root.account_number, root.label as label,";
2460 $sql .= " parent.rowid as parent_rowid, parent.account_number as parent_account_number, parent.label as parent_label";
2461 $sql .= " FROM ".$this->db->prefix()."accounting_account as aa";
2462 $sql .= " INNER JOIN ".$this->db->prefix()."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2463 $sql .= " AND asy.rowid = ".((int) $pcgver);
2464 $sql .= " LEFT JOIN ".$this->db->prefix()."accounting_account as parent ON aa.account_parent = parent.rowid AND parent.active = 1";
2465 $sql .= " LEFT JOIN ".$this->db->prefix()."accounting_account as root ON parent.account_parent = root.rowid AND root.active = 1";
2466 $sql .= " WHERE aa.account_number = '".$this->db->escape($account)."'";
2467 $sql .= " AND aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2468
2469 dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2470 $resql = $this->db->query($sql);
2471 if ($resql) {
2472 $obj = '';
2473 if ($this->db->num_rows($resql)) {
2474 $obj = $this->db->fetch_object($resql);
2475 }
2476
2477 $result = array('id' => $obj->rowid, 'account_number' => $obj->account_number, 'label' => $obj->label);
2478 return $result;
2479 } else {
2480 $this->error = "Error ".$this->db->lasterror();
2481 dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2482
2483 return -1;
2484 }
2485 }
2486
2487 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2494 public function get_compte_desc($account = null)
2495 {
2496 // phpcs:enable
2497 global $conf;
2498
2499 $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2500 $sql = "SELECT aa.account_number, aa.label, aa.rowid, aa.fk_pcg_version, cat.label as category";
2501 $sql .= " FROM ".$this->db->prefix()."accounting_account as aa ";
2502 $sql .= " INNER JOIN ".$this->db->prefix()."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2503 $sql .= " AND aa.account_number = '".$this->db->escape($account)."'";
2504 $sql .= " AND asy.rowid = ".((int) $pcgver);
2505 $sql .= " AND aa.active = 1";
2506 $sql .= " LEFT JOIN ".$this->db->prefix()."c_accounting_category as cat ON aa.fk_accounting_category = cat.rowid";
2507 $sql .= " WHERE aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2508
2509 dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2510 $resql = $this->db->query($sql);
2511 if ($resql) {
2512 $obj = (object) array('label' => '');
2513 if ($this->db->num_rows($resql)) {
2514 $obj = $this->db->fetch_object($resql);
2515 }
2516 if (empty($obj->category)) {
2517 return $obj->label;
2518 } else {
2519 return $obj->label.' ('.$obj->category.')';
2520 }
2521 } else {
2522 $this->error = "Error ".$this->db->lasterror();
2523 dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2524 return "-1";
2525 }
2526 }
2527
2535 public function getCanModifyBookkeepingSQL($alias = '', $force = false)
2536 {
2537 global $conf;
2538
2539 $alias = trim($alias);
2540 $alias = !empty($alias) && strpos($alias, '.') === false ? $alias . "." : $alias;
2541
2542 if (!isset(self::$can_modify_bookkeeping_sql_cached[$alias]) || $force) {
2543 $result = $this->loadFiscalPeriods($force, 'active'); // This set $conf->cache['active_fiscal_period_cached']
2544 if ($result < 0) {
2545 return null;
2546 }
2547
2548 $sql_list = array();
2549 if (!empty($conf->cache['active_fiscal_period_cached']) && is_array($conf->cache['active_fiscal_period_cached'])) {
2550 $i = 0;
2551 foreach ($conf->cache['active_fiscal_period_cached'] as $fiscal_period) {
2552 $sql_list[$i] = "(";
2553 $sql_list[$i] .= "'".$this->db->idate($fiscal_period['date_start']) . "' <= ".$this->db->sanitize($alias)."doc_date";
2554 // @phan-suppress-next-line PhanTypeMismatchDimAssignment
2555 if (!empty($fiscal_period['date_end'])) {
2556 $sql_list[$i] .= " AND ";
2557 $sql_list[$i] .= $this->db->sanitize($alias)."doc_date <= '" . $this->db->idate((int) $fiscal_period['date_end'])."'";
2558 }
2559 $sql_list[$i] .= ")";
2560 $i++;
2561 }
2562 }
2563 $sqlsanitized = implode(' OR ', $sql_list);
2564 self::$can_modify_bookkeeping_sql_cached[$alias] = empty($sql_list) ? "" : " AND (".$sqlsanitized.")";
2565 }
2566
2567 return self::$can_modify_bookkeeping_sql_cached[$alias];
2568 }
2569
2577 public function canModifyBookkeeping($id, $mode = '')
2578 {
2579 global $conf;
2580
2581 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
2582 $result = $this->loadFiscalPeriods(false, 'closed');
2583
2584 if ($result < 0) {
2585 return -1;
2586 }
2587
2588 $bookkeeping = new BookKeeping($this->db);
2589 $result = $bookkeeping->fetch($id, null, $mode);
2590 if ($result <= 0) {
2591 return $result;
2592 }
2593
2594 if (!empty($conf->cache['closed_fiscal_period_cached']) && is_array($conf->cache['closed_fiscal_period_cached'])) {
2595 foreach ($conf->cache['closed_fiscal_period_cached'] as $fiscal_period) {
2596 if ($fiscal_period['date_start'] <= $bookkeeping->doc_date && $bookkeeping->doc_date <= $fiscal_period['date_end']) {
2597 return 0;
2598 }
2599 }
2600 }
2601
2602 return 1;
2603 } else {
2604 $result = $this->loadFiscalPeriods(false, 'active');
2605 if ($result < 0) {
2606 return -1;
2607 }
2608
2609 $bookkeeping = new BookKeeping($this->db);
2610 $result = $bookkeeping->fetch($id, null, $mode);
2611
2612 if ($result <= 0) {
2613 return $result;
2614 }
2615 if (!empty($conf->cache['active_fiscal_period_cached']) && is_array($conf->cache['active_fiscal_period_cached'])) {
2616 foreach ($conf->cache['active_fiscal_period_cached'] as $fiscal_period) {
2617 // @phan-suppress-next-line PhanTypeMismatchDimAssignment
2618 if (!empty($fiscal_period['date_start']) && $fiscal_period['date_start'] <= $bookkeeping->doc_date && (empty($fiscal_period['date_end']) || $bookkeeping->doc_date <= $fiscal_period['date_end'])) {
2619 return 1;
2620 }
2621 }
2622 }
2623
2624 return 0;
2625 }
2626 }
2627
2640 public function accountingLabelForOperation($thirdpartyname, $reference, $labelaccount, $full = 0)
2641 {
2642 global $hookmanager;
2643 $accountingLabelOperation = '';
2644
2645 if (!getDolGlobalInt('ACCOUNTING_LABEL_OPERATION_ON_TRANSFER')) {
2646 $truncThirdpartyName = 16;
2647 // Avoid trunc with dot in accountancy for the compatibility with another accounting software
2648 if (empty($full)) {
2649 $accountingLabelOperation = dol_trunc($thirdpartyname, $truncThirdpartyName, 'right', 'UTF-8', 1);
2650 } else {
2651 $accountingLabelOperation = $thirdpartyname;
2652 }
2653 if (!empty($reference)) {
2654 $accountingLabelOperation .= ' - '. $reference;
2655 }
2656 if (!empty($labelaccount)) {
2657 $accountingLabelOperation .= ' - '. $labelaccount;
2658 }
2659 } elseif (getDolGlobalInt('ACCOUNTING_LABEL_OPERATION_ON_TRANSFER') == 1) {
2660 $truncThirdpartyName = 32;
2661 // Avoid trunc with dot in accountancy for the compatibility with another accounting software
2662 if (empty($full)) {
2663 $accountingLabelOperation = dol_trunc($thirdpartyname, $truncThirdpartyName, 'right', 'UTF-8', 1);
2664 } else {
2665 $accountingLabelOperation = $thirdpartyname;
2666 }
2667 if (!empty($reference)) {
2668 $accountingLabelOperation .= ' - '. $reference;
2669 }
2670 } elseif (getDolGlobalInt('ACCOUNTING_LABEL_OPERATION_ON_TRANSFER') == 2) {
2671 $truncThirdpartyName = 64;
2672 // Avoid trunc with dot in accountancy for the compatibility with another accounting software
2673 if (empty($full)) {
2674 $accountingLabelOperation = dol_trunc($thirdpartyname, $truncThirdpartyName, 'right', 'UTF-8', 1);
2675 } else {
2676 $accountingLabelOperation = $thirdpartyname;
2677 }
2678 }
2679
2680 // Hook to allow overriding the label text
2681 $parameters = [
2682 'thirdpartyname' => $thirdpartyname,
2683 'reference' => $reference,
2684 'labelaccount' => $labelaccount,
2685 'accountingLabelOperation' => $accountingLabelOperation,
2686 ];
2687 $action = '';
2688 if (!isset($hookmanager->resPrint)) {
2689 $hookmanager->resPrint = '';
2690 }
2691
2692 $reshook = $hookmanager->executeHooks('accountingLabelForOperation', $parameters, $this, $action);
2693
2694 if ($reshook > 0) {
2695 $accountingLabelOperation = $hookmanager->resPrint;
2696 } elseif ($reshook == 0 && !empty($hookmanager->resPrint)) {
2697 $accountingLabelOperation .= $hookmanager->resPrint;
2698 }
2699
2700 dol_syslog(get_class($this) . "::accountingLabelForOperation: " . $accountingLabelOperation, LOG_DEBUG);
2701 return $accountingLabelOperation;
2702 }
2703
2710 public function validBookkeepingDate($date)
2711 {
2712 global $conf;
2713
2714 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
2715 $result = $this->loadFiscalPeriods(false, 'closed');
2716
2717 if ($result < 0) {
2718 return -1;
2719 }
2720
2721 if (!empty($conf->cache['closed_fiscal_period_cached']) && is_array($conf->cache['closed_fiscal_period_cached'])) {
2722 foreach ($conf->cache['closed_fiscal_period_cached'] as $fiscal_period) {
2723 if ($fiscal_period['date_start'] <= $date && $date <= $fiscal_period['date_end']) {
2724 return 0;
2725 }
2726 }
2727 }
2728
2729 return 1;
2730 } else {
2731 $result = $this->loadFiscalPeriods(false, 'active');
2732 if ($result < 0) {
2733 return -1;
2734 }
2735
2736 if (!empty($conf->cache['active_fiscal_period_cached']) && is_array($conf->cache['active_fiscal_period_cached'])) {
2737 foreach ($conf->cache['active_fiscal_period_cached'] as $fiscal_period) {
2738 // @phan-suppress-next-line PhanTypeMismatchDimAssignment
2739 if (!empty($fiscal_period['date_start']) && $fiscal_period['date_start'] <= $date && (empty($fiscal_period['date_end']) || $date <= $fiscal_period['date_end'])) {
2740 return 1;
2741 }
2742 }
2743 }
2744
2745 return 0;
2746 }
2747 }
2748
2756 public function loadFiscalPeriods($force = false, $mode = 'active')
2757 {
2758 global $conf;
2759
2760 if ($mode == 'active') {
2761 if (!isset($conf->cache['active_fiscal_period_cached']) || $force) {
2762 $sql = "SELECT date_start, date_end";
2763 $sql .= " FROM " . $this->db->prefix() . "accounting_fiscalyear";
2764 $sql .= " WHERE entity = " . ((int) $conf->entity);
2765 $sql .= " AND statut = 0";
2766
2767 $resql = $this->db->query($sql);
2768 if (!$resql) {
2769 $this->errors[] = $this->db->lasterror();
2770 return -1;
2771 }
2772
2773 $list = array();
2774 while ($obj = $this->db->fetch_object($resql)) {
2775 $date_start = $this->db->jdate($obj->date_start);
2776 $date_end_base = $this->db->jdate($obj->date_end);
2777 $date_end = dol_get_last_hour($date_end_base);
2778 $list[] = array(
2779 'date_start' => $date_start,
2780 'date_end' => $date_end,
2781 );
2782 }
2783 $conf->cache['active_fiscal_period_cached'] = $list;
2784 }
2785 }
2786 if ($mode == 'closed') {
2787 if (!isset($conf->cache['closed_fiscal_period_cached']) || $force) {
2788 $sql = "SELECT date_start, date_end";
2789 $sql .= " FROM " . $this->db->prefix() . "accounting_fiscalyear";
2790 $sql .= " WHERE entity = " . ((int) $conf->entity);
2791 $sql .= " AND statut = 1";
2792
2793 $resql = $this->db->query($sql);
2794 if (!$resql) {
2795 $this->errors[] = $this->db->lasterror();
2796 return -1;
2797 }
2798
2799 $list = array();
2800 while ($obj = $this->db->fetch_object($resql)) {
2801 $date_start = $this->db->jdate($obj->date_start);
2802 $date_end_base = $this->db->jdate($obj->date_end);
2803 $date_end = dol_get_last_hour($date_end_base);
2804
2805 $list[] = array(
2806 'date_start' => $date_start,
2807 'date_end' => $date_end,
2808 );
2809 }
2810 $conf->cache['closed_fiscal_period_cached'] = $list;
2811 }
2812 }
2813
2814 return 1;
2815 }
2816
2822 public function getFiscalPeriods()
2823 {
2824 global $conf;
2825 $list = array();
2826
2827 $sql = "SELECT rowid, label, date_start, date_end, statut";
2828 $sql .= " FROM " . $this->db->prefix() . "accounting_fiscalyear";
2829 $sql .= " WHERE entity = " . ((int) $conf->entity);
2830 $sql .= $this->db->order('date_start', 'ASC');
2831
2832 $resql = $this->db->query($sql);
2833 if (!$resql) {
2834 $this->errors[] = $this->db->lasterror();
2835 return -1;
2836 }
2837
2838 while ($obj = $this->db->fetch_object($resql)) {
2839 $list[$obj->rowid] = array(
2840 'id' => (int) $obj->rowid,
2841 'label' => $obj->label,
2842 'date_start' => $this->db->jdate($obj->date_start),
2843 'date_end' => $this->db->jdate($obj->date_end),
2844 'status' => (int) $obj->statut,
2845 );
2846 }
2847
2848 return $list;
2849 }
2850
2860 {
2861 global $conf;
2862
2863 $total = 0;
2864 $list = array();
2865
2866 $sql = "SELECT YEAR(b.doc_date) as year";
2867 for ($i = 1; $i <= 12; $i++) {
2868 $sql .= ", SUM(".$this->db->ifsql("MONTH(b.doc_date) = ".((int) $i), "1", "0") . ") AS month".((int) $i);
2869 }
2870 $sql .= ", COUNT(b.rowid) as total";
2871 $sql .= " FROM " . $this->db->prefix() . $this->table_element . " as b";
2872 $sql .= " WHERE b.doc_date >= '" . $this->db->idate($date_start) . "'";
2873 $sql .= " AND b.doc_date <= '" . $this->db->idate($date_end) . "'";
2874 $sql .= " AND b.entity IN (" . getEntity('bookkeeping', 0) . ")"; // We don't share object for accountancy
2875
2876 // Get count for each month into the fiscal period
2877 if (getDolGlobalString("ACCOUNTANCY_DISABLE_CLOSURE_LINE_BY_LINE")) {
2878 // Loop on each closed period
2879 $sql .= " AND NOT EXISTS (SELECT rowid FROM ".MAIN_DB_PREFIX.'accounting_fiscalyear as af WHERE b.doc_date >= af.date_start AND b.doc_date <= af.date_end AND af.entity = '.((int) $conf->entity)." AND af.statut = 1)";
2880 } else {
2881 // Filter on the unitary flag/date lock on each record
2882 $sql .= " AND date_validated IS NULL"; // not locked
2883 }
2884
2885 $sql .= " GROUP BY YEAR(b.doc_date)";
2886 $sql .= $this->db->order("year", 'ASC');
2887
2888 dol_syslog(__METHOD__, LOG_DEBUG);
2889 $resql = $this->db->query($sql);
2890 if (!$resql) {
2891 $this->errors[] = $this->db->lasterror();
2892 return -1;
2893 }
2894
2895 while ($obj = $this->db->fetch_object($resql)) {
2896 $total += (int) $obj->total;
2897 $year_list = array(
2898 'year' => (int) $obj->year,
2899 'count' => array(),
2900 'total' => (int) $obj->total,
2901 );
2902 for ($i = 1; $i <= 12; $i++) {
2903 $year_list['count'][$i] = (int) $obj->{'month' . $i};
2904 }
2905
2906 $list[] = $year_list;
2907 }
2908
2909 $this->db->free($resql);
2910
2911 return array(
2912 'total' => $total,
2913 'list' => $list,
2914 );
2915 }
2916
2925 {
2926 global $conf;
2927
2928 $now = dol_now();
2929
2930 // Specify as export : update field date_validated on selected month/year
2931 $sql = " UPDATE " . $this->db->prefix() . $this->table_element;
2932 $sql .= " SET date_validated = '" . $this->db->idate($now) . "'";
2933 $sql .= " WHERE entity = " . ((int) $conf->entity);
2934 $sql .= " AND DATE(doc_date) >= '" . $this->db->idate($date_start) . "'";
2935 $sql .= " AND DATE(doc_date) <= '" . $this->db->idate($date_end) . "'";
2936 $sql .= " AND date_validated IS NULL";
2937
2938 dol_syslog(__METHOD__, LOG_DEBUG);
2939 $resql = $this->db->query($sql);
2940 if (!$resql) {
2941 $this->errors[] = $this->db->lasterror();
2942 return -1;
2943 }
2944
2945 return 1;
2946 }
2947
2955 public function accountingResult($date_start, $date_end)
2956 {
2957 global $conf;
2958
2959 $this->db->begin();
2960
2961 $income_statement_amount = 0;
2962
2963 if (getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_INCOME_STATEMENT')) {
2964 $accounting_groups_used_for_income_statement = array_filter(array_map('trim', explode(',', getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_INCOME_STATEMENT'))), 'strlen'); // @phpstan-ignore argument.type
2965
2966 $pcg_type_filter = array();
2967 foreach ($accounting_groups_used_for_income_statement as $item) {
2968 $pcg_type_filter[] = "'" . $this->db->escape($item) . "'";
2969 }
2970
2971 $sql = 'SELECT';
2972 $sql .= " t.numero_compte,";
2973 $sql .= " aa.pcg_type,";
2974 $sql .= " (SUM(t.credit) - SUM(t.debit)) as accounting_result";
2975 $sql .= ' FROM ' . $this->db->prefix() . $this->table_element . ' as t';
2976 $sql .= ' LEFT JOIN ' . $this->db->prefix() . 'accounting_account as aa ON aa.account_number = t.numero_compte';
2977 $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
2978 $sql .= " AND aa.entity = " . ((int) $conf->entity);
2979 $sql .= ' AND aa.fk_pcg_version IN (SELECT pcg_version FROM ' . $this->db->prefix() . 'accounting_system WHERE rowid = ' . ((int) getDolGlobalInt('CHARTOFACCOUNTS')) . ')';
2980 $sql .= ' AND aa.pcg_type IN (' . $this->db->sanitize(implode(',', $pcg_type_filter), 1) . ')';
2981 $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($date_start) . "'";
2982 $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($date_end) . "'";
2983 $sql .= ' GROUP BY t.numero_compte, aa.pcg_type';
2984
2985 $resql = $this->db->query($sql);
2986 if (!$resql) {
2987 $this->errors[] = 'Error ' . $this->db->lasterror();
2988 dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
2989 } else {
2990 while ($obj = $this->db->fetch_object($resql)) {
2991 $income_statement_amount += $obj->accounting_result;
2992 }
2993 }
2994 }
2995
2996 return (string) $income_statement_amount;
2997 }
2998
3008 public function closeFiscalPeriod($fiscal_period_id, $new_fiscal_period_id, $separate_auxiliary_account = false, $generate_bookkeeping_records = true)
3009 {
3010 global $conf, $langs, $user;
3011
3012 // Current fiscal period
3013 $fiscal_period_id = max(0, $fiscal_period_id);
3014
3015 if (empty($fiscal_period_id)) {
3016 $langs->load('errors');
3017 $this->errors[] = $langs->trans('ErrorBadParameters');
3018 return -1;
3019 }
3020
3021 $fiscal_period = new Fiscalyear($this->db);
3022 $result = $fiscal_period->fetch($fiscal_period_id);
3023 if ($result < 0) {
3024 $this->error = $fiscal_period->error;
3025 $this->errors = $fiscal_period->errors;
3026 return -1;
3027 } elseif (empty($fiscal_period->id)) {
3028 $langs->loadLangs(array('errors', 'compta'));
3029 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $fiscal_period_id . ')';
3030 return -1;
3031 }
3032 if (empty($fiscal_period->date_end)) {
3033 $langs->load('errors');
3034 $this->errors[] = $langs->trans('ErrorAccountancyClosureNoEndDate');
3035 return -1;
3036 }
3037
3038 // New fiscal period
3039 $new_fiscal_period_id = max(0, $new_fiscal_period_id);
3040 if (empty($new_fiscal_period_id)) {
3041 $langs->load('errors');
3042 $this->errors[] = $langs->trans('ErrorBadParameters').' - '.$langs->trans('AccountancyClosureStep3NewFiscalPeriod');
3043 return -1;
3044 }
3045 $new_fiscal_period = new Fiscalyear($this->db);
3046 $result = $new_fiscal_period->fetch($new_fiscal_period_id);
3047 if ($result < 0) {
3048 $this->error = $new_fiscal_period->error;
3049 $this->errors = $new_fiscal_period->errors;
3050 return -1;
3051 } elseif (empty($new_fiscal_period->id)) {
3052 $langs->loadLangs(array('errors', 'compta'));
3053 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $new_fiscal_period_id . ')';
3054 return -1;
3055 }
3056 if ($new_fiscal_period->date_start <= $fiscal_period->date_end) {
3057 $langs->load('errors');
3058 $this->errors[] = $langs->trans('ErrorAccountancyClosureNoNextFiscalPeriod');
3059 return -1;
3060 }
3061
3062 $error = 0;
3063 $this->db->begin();
3064
3065 $fiscal_period->statut = Fiscalyear::STATUS_CLOSED;
3066 $fiscal_period->status = Fiscalyear::STATUS_CLOSED; // Actually not used
3067 $result = $fiscal_period->update($user);
3068 if ($result < 0) {
3069 $this->error = $fiscal_period->error;
3070 $this->errors = $fiscal_period->errors;
3071 $error++;
3072 }
3073
3074 if (!$error && !empty($generate_bookkeeping_records)) {
3075 $journal_id = max(0, getDolGlobalString('ACCOUNTING_CLOSURE_DEFAULT_JOURNAL'));
3076 if (empty($journal_id)) {
3077 $langs->loadLangs(array('errors', 'accountancy'));
3078 $this->errors[] = $langs->trans('ErrorBadParameters') . ' - ' . $langs->trans('Codejournal') . ' (' . $langs->trans('AccountingJournalType9') . ')';
3079 $error++;
3080 }
3081
3082 // Fetch journal
3083 if (!$error) {
3084 $journal = new AccountingJournal($this->db);
3085 $result = $journal->fetch($journal_id);
3086 if ($result < 0) {
3087 $this->error = $journal->error;
3088 $this->errors = $journal->errors;
3089 $error++;
3090 } elseif ($result == 0) {
3091 $langs->loadLangs(array('errors', 'accountancy'));
3092 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('Codejournal') . ' (' . $langs->trans('AccountingJournalType9') . ')';
3093 $error++;
3094 }
3095 } else {
3096 $journal = null;
3097 }
3098
3099 if (!$error && is_object($journal)) {
3100 $accounting_groups_used_for_balance_sheet_account = array_filter(array_map('trim', explode(',', getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_BALANCE_SHEET_ACCOUNT'))), 'strlen'); // @phpstan-ignore argument.type
3101 $accounting_groups_used_for_income_statement = array_filter(array_map('trim', explode(',', getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_INCOME_STATEMENT'))), 'strlen'); // @phpstan-ignore argument.type
3102
3103 $pcg_type_filter = array();
3104 $tmp = array_merge($accounting_groups_used_for_balance_sheet_account, $accounting_groups_used_for_income_statement);
3105 foreach ($tmp as $item) {
3106 $pcg_type_filter[] = "'" . $this->db->escape($item) . "'";
3107 }
3108
3109 $sql = 'SELECT';
3110 $sql .= " t.numero_compte,";
3111 if ($separate_auxiliary_account) {
3112 $sql .= " NULLIF(t.subledger_account, '') as subledger_account,"; // fix db issues with Null or "" values
3113 }
3114 $sql .= " aa.pcg_type,";
3115 $sql .= " (SUM(t.credit) - SUM(t.debit)) as opening_balance";
3116 $sql .= ' FROM ' . $this->db->prefix() . $this->table_element . ' as t';
3117 $sql .= ' LEFT JOIN ' . $this->db->prefix() . 'accounting_account as aa ON aa.account_number = t.numero_compte';
3118 $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
3119 $sql .= " AND aa.entity = ". ((int) $conf->entity);
3120 $sql .= ' AND aa.fk_pcg_version IN (SELECT pcg_version FROM '.$this->db->prefix().'accounting_system WHERE rowid = '.((int) getDolGlobalInt('CHARTOFACCOUNTS')).')';
3121 $sql .= ' AND aa.pcg_type IN (' . $this->db->sanitize(implode(',', $pcg_type_filter), 1) . ')';
3122 $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($fiscal_period->date_start) . "'";
3123 $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($fiscal_period->date_end) . "'";
3124 $sql .= ' GROUP BY t.numero_compte, aa.pcg_type';
3125 if ($separate_auxiliary_account) {
3126 $sql .= " , NULLIF(t.subledger_account, '')";
3127 }
3128 $sql .= ' HAVING (SUM(t.credit) - SUM(t.debit)) != 0 '; // Exclude rows with opening_balance = 0
3129 $sql .= $this->db->order("t.numero_compte", "ASC");
3130
3131 $resql = $this->db->query($sql);
3132 if (!$resql) {
3133 $this->errors[] = 'Error ' . $this->db->lasterror();
3134 dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
3135
3136 $error++;
3137 } else {
3138 $now = dol_now();
3139 $income_statement_amount = 0;
3140 while ($obj = $this->db->fetch_object($resql)) {
3141 if (in_array($obj->pcg_type, $accounting_groups_used_for_income_statement)) {
3142 $income_statement_amount += $obj->opening_balance;
3143 } else {
3144 // Insert bookkeeping record for balance sheet account
3145 $mt = $obj->opening_balance;
3146
3147 $bookkeeping = new BookKeeping($this->db);
3148 $bookkeeping->doc_date = $new_fiscal_period->date_start;
3149
3150 $bookkeeping->date_lim_reglement = '';
3151 $bookkeeping->doc_ref = $fiscal_period->label;
3152
3153 $bookkeeping->date_creation = $now;
3154 $bookkeeping->doc_type = 'closure';
3155 $bookkeeping->fk_doc = $fiscal_period->id;
3156 $bookkeeping->fk_docdet = 0; // Useless, can be several lines that are source of this record to add
3157 $bookkeeping->thirdparty_code = '';
3158
3159 if ($separate_auxiliary_account) {
3160 $bookkeeping->subledger_account = $obj->subledger_account;
3161 $sql = 'SELECT';
3162 $sql .= " subledger_label";
3163 $sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
3164 $sql .= " WHERE subledger_account = '" . $this->db->escape($obj->subledger_account) . "'";
3165 $sql .= " ORDER BY doc_date DESC";
3166 $sql .= " LIMIT 1";
3167 $result = $this->db->query($sql);
3168 if (!$result) {
3169 $this->errors[] = 'Error: ' . $this->db->lasterror();
3170 dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
3171 $error++;
3172 }
3173 $objtmp = $this->db->fetch_object($result);
3174 $bookkeeping->subledger_label = $objtmp->subledger_label; // latest subledger label used
3175 } else {
3176 $bookkeeping->subledger_account = null;
3177 $bookkeeping->subledger_label = null;
3178 }
3179
3180 $bookkeeping->numero_compte = $obj->numero_compte;
3181 $accountingaccount = new AccountingAccount($this->db);
3182 $accountingaccount->fetch(0, $obj->numero_compte);
3183 $bookkeeping->label_compte = $accountingaccount->label; // latest account label used
3184
3185 $bookkeeping->label_operation = $new_fiscal_period->label;
3186 $bookkeeping->montant = $mt;
3187 $bookkeeping->sens = ($mt >= 0) ? 'C' : 'D';
3188 $bookkeeping->debit = ($mt < 0) ? -$mt : 0;
3189 $bookkeeping->credit = ($mt >= 0) ? $mt : 0;
3190 $bookkeeping->code_journal = $journal->code;
3191 $bookkeeping->journal_label = $langs->transnoentities($journal->label);
3192 $bookkeeping->fk_user_author = $user->id;
3193 $bookkeeping->entity = $conf->entity;
3194
3195 $result = $bookkeeping->create($user);
3196 if ($result < 0) {
3197 $this->setErrorsFromObject($bookkeeping);
3198 $error++;
3199 break;
3200 }
3201 }
3202 }
3203
3204 // Insert bookkeeping record for income statement (loss or profit when closing)
3205 if (!$error && $income_statement_amount != 0) {
3206 $mt = $income_statement_amount;
3207 $accountingaccount = new AccountingAccount($this->db);
3208 $accountingaccount->fetch(0, getDolGlobalString($income_statement_amount < 0 ? 'ACCOUNTING_RESULT_LOSS' : 'ACCOUNTING_RESULT_PROFIT'), true);
3209
3210 $bookkeeping = new BookKeeping($this->db);
3211 $bookkeeping->doc_date = $new_fiscal_period->date_start;
3212
3213 $bookkeeping->date_lim_reglement = '';
3214 $bookkeeping->doc_ref = $fiscal_period->label;
3215
3216 $bookkeeping->date_creation = $now;
3217 $bookkeeping->doc_type = 'closure';
3218 $bookkeeping->fk_doc = $fiscal_period->id;
3219 $bookkeeping->fk_docdet = 0; // Useless, can be several lines that are source of this record to add
3220 $bookkeeping->thirdparty_code = '';
3221
3222 /* $obj->subledger_account is not defined, so all code into if do the same then in else
3223 if ($separate_auxiliary_account) {
3224 $bookkeeping->subledger_account = $obj->subledger_account;
3225 $sql = 'SELECT';
3226 $sql .= " subledger_label";
3227 $sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
3228 $sql .= " WHERE subledger_account = '" . $this->db->escape($obj->subledger_account) . "'";
3229 $sql .= " ORDER BY doc_date DESC";
3230 $sql .= " LIMIT 1";
3231 $result = $this->db->query($sql);
3232 if (!$result) {
3233 $this->errors[] = 'Error: ' . $this->db->lasterror();
3234 dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
3235 $error++;
3236 }
3237 $objtmp = $this->db->fetch_object($result);
3238 $bookkeeping->subledger_label = $objtmp->subledger_label ?? null; // latest subledger label used
3239
3240 $bookkeeping->subledger_account = null;
3241 $bookkeeping->subledger_label = null;
3242 } else {
3243 */
3244 $bookkeeping->subledger_account = null;
3245 $bookkeeping->subledger_label = null;
3246 //}
3247
3248 $bookkeeping->numero_compte = $accountingaccount->account_number;
3249 $bookkeeping->label_compte = $accountingaccount->label;
3250
3251 $bookkeeping->label_operation = $new_fiscal_period->label;
3252 $bookkeeping->montant = $mt;
3253 $bookkeeping->sens = ($mt >= 0) ? 'C' : 'D';
3254 $bookkeeping->debit = ($mt < 0) ? -$mt : 0;
3255 $bookkeeping->credit = ($mt >= 0) ? $mt : 0;
3256 $bookkeeping->code_journal = $journal->code;
3257 $bookkeeping->journal_label = $langs->transnoentities($journal->label);
3258 $bookkeeping->fk_user_author = $user->id;
3259 $bookkeeping->entity = $conf->entity;
3260
3261 $result = $bookkeeping->create($user);
3262 if ($result < 0) {
3263 $this->setErrorsFromObject($bookkeeping);
3264 $error++;
3265 }
3266 }
3267 $this->db->free($resql);
3268 }
3269 }
3270 }
3271
3272 if ($error) {
3273 $this->db->rollback();
3274 return -1;
3275 } else {
3276 $this->db->commit();
3277 return 1;
3278 }
3279 }
3280
3291 public function insertAccountingReversal($fiscal_period_id, $inventory_journal_id, $new_fiscal_period_id, $date_start, $date_end)
3292 {
3293 global $conf, $langs, $user;
3294
3295 // Current fiscal period
3296 $fiscal_period_id = max(0, $fiscal_period_id);
3297 if (empty($fiscal_period_id)) {
3298 $langs->load('errors');
3299 $this->errors[] = $langs->trans('ErrorBadParameters');
3300 return -1;
3301 }
3302 $fiscal_period = new Fiscalyear($this->db);
3303 $result = $fiscal_period->fetch($fiscal_period_id);
3304 if ($result < 0) {
3305 $this->error = $fiscal_period->error;
3306 $this->errors = $fiscal_period->errors;
3307 return -1;
3308 } elseif (empty($fiscal_period->id)) {
3309 $langs->loadLangs(array('errors', 'compta'));
3310 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $fiscal_period_id . ')';
3311 return -1;
3312 }
3313
3314 // New fiscal period
3315 $new_fiscal_period_id = max(0, $new_fiscal_period_id);
3316 if (empty($new_fiscal_period_id)) {
3317 $langs->load('errors');
3318 $this->errors[] = $langs->trans('ErrorBadParameters');
3319 return -1;
3320 }
3321 $new_fiscal_period = new Fiscalyear($this->db);
3322 $result = $new_fiscal_period->fetch($new_fiscal_period_id);
3323 if ($result < 0) {
3324 $this->error = $new_fiscal_period->error;
3325 $this->errors = $new_fiscal_period->errors;
3326 return -1;
3327 } elseif (empty($new_fiscal_period->id)) {
3328 $langs->loadLangs(array('errors', 'compta'));
3329 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $new_fiscal_period_id . ')';
3330 return -1;
3331 }
3332
3333 // Inventory journal
3334 $inventory_journal_id = max(0, $inventory_journal_id);
3335 if (empty($inventory_journal_id)) {
3336 $langs->load('errors');
3337 $this->errors[] = $langs->trans('ErrorBadParameters');
3338 return -1;
3339 }
3340 // Fetch journal
3341 $inventory_journal = new AccountingJournal($this->db);
3342 $result = $inventory_journal->fetch($inventory_journal_id);
3343 if ($result < 0) {
3344 $this->error = $inventory_journal->error;
3345 $this->errors = $inventory_journal->errors;
3346 return -1;
3347 } elseif ($result == 0) {
3348 $langs->loadLangs(array('errors', 'accountancy'));
3349 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('InventoryJournal');
3350 return -1;
3351 }
3352
3353 $error = 0;
3354 $this->db->begin();
3355
3356 $sql = 'SELECT t.rowid';
3357 $sql .= ' FROM ' . $this->db->prefix() . $this->table_element . ' as t';
3358 $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
3359 $sql .= " AND code_journal = '" . $this->db->escape($inventory_journal->code) . "'";
3360 $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($date_start) . "'";
3361 $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($date_end) . "'";
3362 $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($fiscal_period->date_start) . "'";
3363 $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($fiscal_period->date_end) . "'";
3364
3365 $resql = $this->db->query($sql);
3366 if (!$resql) {
3367 $this->errors[] = 'Error ' . $this->db->lasterror();
3368 dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
3369
3370 $error++;
3371 } else {
3372 $now = dol_now();
3373 while ($obj = $this->db->fetch_object($resql)) {
3374 $bookkeeping = new BookKeeping($this->db);
3375 $result = $bookkeeping->fetch($obj->rowid);
3376 if ($result < 0) {
3377 $this->error = $inventory_journal->error;
3378 $this->errors = $inventory_journal->errors;
3379 $error++;
3380 break;
3381 } elseif ($result == 0) {
3382 $langs->loadLangs(array('errors', 'accountancy'));
3383 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('LineId') . ': ' . $obj->rowid;
3384 $error++;
3385 break;
3386 }
3387
3388 $bookkeeping->id = 0;
3389 $bookkeeping->doc_date = $new_fiscal_period->date_start;
3390 $bookkeeping->doc_ref = $new_fiscal_period->label;
3391 $bookkeeping->date_creation = $now;
3392 $bookkeeping->doc_type = 'accounting_reversal';
3393 $bookkeeping->fk_doc = $new_fiscal_period->id;
3394 $bookkeeping->fk_docdet = 0; // Useless, can be several lines that are source of this record to add
3395
3396 $bookkeeping->montant = -$bookkeeping->montant;
3397 $bookkeeping->sens = ($bookkeeping->montant >= 0) ? 'C' : 'D';
3398 $old_debit = $bookkeeping->debit;
3399 $bookkeeping->debit = $bookkeeping->credit;
3400 $bookkeeping->credit = $old_debit;
3401
3402 $bookkeeping->fk_user_author = $user->id;
3403 $bookkeeping->entity = $conf->entity;
3404
3405 $result = $bookkeeping->create($user);
3406 if ($result < 0) {
3407 $this->error = $bookkeeping->error;
3408 $this->errors = $bookkeeping->errors;
3409 $error++;
3410 break;
3411 }
3412 }
3413 $this->db->free($resql);
3414 }
3415
3416 if ($error) {
3417 $this->db->rollback();
3418 return -1;
3419 } else {
3420 $this->db->commit();
3421 return 1;
3422 }
3423 }
3424
3432 public function assignAccountMass($toselect, $accounting_account = 0)
3433 {
3434 global $langs, $user;
3435
3436 $error = 0;
3437
3438 $this->db->begin();
3439
3440 $bookkeeping = new BookKeeping($this->db);
3441 $accountingaccount = new AccountingAccount($this->db);
3442 $nb = 0;
3443
3444 if ((int) $accounting_account > 0) {
3445 $accountingaccount->fetch($accounting_account);
3446 $echecT = [];
3447 foreach ($toselect as $id) {
3448 if ($bookkeeping->fetch($id)) {
3449 if (!getDolGlobalString('ACCOUNTING_ACCOUNT_CUSTOMER')) {
3450 $accountcustcode = '411';
3451 } else {
3452 $accountcustcode = getDolGlobalString('ACCOUNTING_ACCOUNT_CUSTOMER');
3453 }
3454
3455 if (!getDolGlobalString('ACCOUNTING_ACCOUNT_SUPPLIER')) {
3456 $accountsuppcode = '401';
3457 } else {
3458 $accountsuppcode = getDolGlobalString('ACCOUNTING_ACCOUNT_SUPPLIER');
3459 }
3460
3461 if (strpos($bookkeeping->numero_compte, $accountcustcode) === 0 || strpos($bookkeeping->numero_compte, $accountsuppcode) === 0) {
3462 $echecT[] = $bookkeeping->numero_compte;
3463 continue;
3464 }
3465
3466 $bookkeeping->numero_compte = $accountingaccount->account_number;
3467 $bookkeeping->label_compte = $accountingaccount->label;
3468
3469 $result = $bookkeeping->update($user);
3470
3471 if ($result > 0) {
3472 $nb++;
3473 } else {
3474 setEventMessages($bookkeeping->error, $bookkeeping->errors, 'errors');
3475 $error++;
3476 break;
3477 }
3478 }
3479 }
3480
3481 $echecImplode = implode(",", $echecT);
3482 } else {
3483 setEventMessages($langs->trans('NoAccountSelected'), null, 'errors');
3484 $error++;
3485 $this->db->rollback();
3486 }
3487
3488 if ($nb > 1) {
3489 setEventMessages($nb ." " . $langs->trans('AssignAccountsSuccess'), null, 'mesgs');
3490 } elseif ($nb > 0) {
3491 setEventMessages($nb ." " . $langs->trans('AssignAccountSuccess'), null, 'mesgs');
3492 } else {
3493 setEventMessages($langs->trans('AssignAccountError'), null, 'errors');
3494 $error++;
3495 }
3496
3497 if (!empty($echecImplode)) {
3498 $nbEchec = count(explode(',', $echecImplode));
3500 $nbEchec == 1 ? $langs->trans('NoAccountChangedWithAccountNumber') . ' ' . $echecImplode : $langs->trans('NoAccountsChangedWithAccountNumber') . ' ' . $echecImplode,
3501 null,
3502 'errors'
3503 );
3504 }
3505
3506 if ($error) {
3507 $this->db->rollback();
3508 return -1;
3509 } else {
3510 $this->db->commit();
3511 return 1;
3512 }
3513 }
3514
3523 public function newClone($piecenum, $code_journal, $docdate)
3524 {
3525 global $langs;
3526
3527 $error = 0;
3528
3529 $accountingJournal = new AccountingJournal($this->db);
3530 $accountingJournal->fetch(0, $code_journal);
3531
3532 $bookKeepingValid = new BookKeeping($this->db);
3533
3534 $periodeFiscal = $bookKeepingValid->validBookkeepingDate($docdate);
3535 if ($periodeFiscal < 0) {
3536 $error++;
3537 return -1;
3538 } elseif ($periodeFiscal == 0) {
3539 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
3540 setEventMessages($langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod'), null, 'errors');
3541 } else {
3542 setEventMessages($langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod'), null, 'errors');
3543 header("Location: " . $_SERVER['HTTP_REFERER']);
3544 }
3545 $error++;
3546 return -1;
3547 }
3548
3549 $this->db->begin();
3550 $bookKeepingInstance = new BookKeeping($this->db);
3551 $pieceNumNext = $bookKeepingInstance->getNextNumMvt();
3552
3553 $cloneId = [];
3554 $sqlRowidClone = "SELECT rowid FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping WHERE piece_num = ".((int) $piecenum);
3555 $resqlRowidClone = $this->db->query($sqlRowidClone);
3556
3557 if ($resqlRowidClone) {
3558 while ($objRowidClone = $this->db->fetch_object($resqlRowidClone)) {
3559 $cloneId[] = $objRowidClone->rowid;
3560 }
3561
3562 foreach ($cloneId as $toselectid) {
3563 $bookKeeping = new BookKeeping($this->db);
3564 if ($bookKeeping->fetch($toselectid)) {
3565 $code_journal = getDolGlobalString('ACCOUNTING_CLONING_ENABLE_INPUT_JOURNAL') ? $code_journal : $bookKeeping->code_journal;
3566 $journal_label = getDolGlobalString('ACCOUNTING_CLONING_ENABLE_INPUT_JOURNAL') ? $accountingJournal->label : $bookKeeping->journal_label;
3567
3568 $sql = "SELECT piece_num, label_operation, numero_compte, label_compte, doc_type, code_journal, fk_user_author, doc_ref,";
3569 $sql .= " fk_doc, fk_docdet, debit, credit, journal_label, sens, montant, subledger_account, subledger_label";
3570 $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping";
3571 $sql .= " WHERE rowid = " . ((int) $toselectid);
3572 $resql = $this->db->query($sql);
3573
3574 if ($resql) {
3575 while ($obj = $this->db->fetch_object($resql)) {
3576 $docRef = $obj->doc_ref;
3577 if (preg_match('/ \‍((\d+)\‍)$/', $docRef, $matches)) {
3578 $docRef = preg_replace('/ \‍(\d+\‍)$/', ' (' . (((int) $matches[1]) + 1) . ')', $docRef);
3579 } else {
3580 $docRef .= ' (2)';
3581 }
3582
3583 $sql_insert = "INSERT INTO " . MAIN_DB_PREFIX . "accounting_bookkeeping";
3584 $sql_insert .= " (piece_num, label_operation, numero_compte, label_compte, doc_type, code_journal, doc_date, fk_user_author, doc_ref,";
3585 $sql_insert .= " fk_doc, fk_docdet, debit, credit, date_creation, journal_label, sens, montant, subledger_account, subledger_label)";
3586 $sql_insert .= " VALUES";
3587 $sql_insert .= " (" . ((int) $pieceNumNext) . ", '" . $this->db->escape($obj->label_operation) . "', '" . $this->db->escape($obj->numero_compte) . "', '" . $this->db->escape($obj->label_compte) . "', '" . $this->db->escape($obj->doc_type) . "', '" . $this->db->escape($code_journal) . "', '" . $this->db->idate($docdate) . "', '" . $this->db->escape($obj->fk_user_author) . "', '" . $this->db->escape($docRef) . "', ";
3588 $sql_insert .= " ". ((int) $obj->fk_doc) . ", " . ((int) $obj->fk_docdet) . ", " . (float) $obj->debit . ", " . (float) $obj->credit . ", '" . $this->db->idate($docdate) . "', '" . $this->db->escape($journal_label) . "', '" . $this->db->escape($obj->sens) . "', " . (float) $obj->montant . ", '" . $this->db->escape($obj->subledger_account) . "', '" . $this->db->escape($obj->subledger_label) . "')";
3589
3590 $resqlInsert = $this->db->query($sql_insert);
3591
3592 if ($resqlInsert) {
3593 setEventMessages($langs->trans('CloningSuccess', $pieceNumNext), null, 'mesgs');
3594 } else {
3595 setEventMessages($langs->trans('CloningFailed') . $this->db->lasterror(), null, 'errors');
3596 $error++;
3597 }
3598 }
3599 }
3600 }
3601 }
3602 }
3603
3604 if ($error) {
3605 $this->db->rollback();
3606 return -1;
3607 } else {
3608 $this->db->commit();
3609 return 1;
3610 }
3611 }
3612
3621 public function newCloneMass($toselect, $code_journal, $docdate)
3622 {
3623 global $langs, $user;
3624
3625 $error = 0;
3626 $this->db->begin();
3627
3628 $now = dol_now();
3629 if (empty($docdate)) {
3630 $docdate = $now;
3631 }
3632
3633 $idImplodeSelect = implode(',', $toselect);
3634 $pieceNumT = [];
3635
3636 $sqlPieceNum = "SELECT DISTINCT(piece_num) FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping WHERE rowid IN (".$this->db->sanitize($idImplodeSelect).")";
3637 $resqlPieceNum = $this->db->query($sqlPieceNum);
3638
3639 if ($resqlPieceNum) {
3640 while ($objPieceNum = $this->db->fetch_object($resqlPieceNum)) {
3641 $pieceNumT[] = $objPieceNum->piece_num;
3642 }
3643
3644 foreach ($pieceNumT as $pieceNum) {
3645 $accountingJournal = new AccountingJournal($this->db);
3646 $accountingJournal->fetch(0, $code_journal);
3647 $bookKeepingValid = new BookKeeping($this->db);
3648 $periodeFiscal = $bookKeepingValid->validBookkeepingDate($docdate);
3649 if ($periodeFiscal < 0) {
3650 $error++;
3651 } elseif ($periodeFiscal == 0) {
3652 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
3653 setEventMessages($langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod'), null, 'errors');
3654 } else {
3655 setEventMessages($langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod'), null, 'errors');
3656 header("Location: " . $_SERVER['HTTP_REFERER']);
3657 }
3658 $error++;
3659 }
3660
3661 $bookKeepingInstance = new BookKeeping($this->db);
3662 $pieceNumNext = $bookKeepingInstance->getNextNumMvt();
3663 $cloneId = [];
3664 $sqlRowidClone = "SELECT rowid FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping WHERE piece_num = ".((int) $pieceNum);
3665 $resqlRowidClone = $this->db->query($sqlRowidClone);
3666
3667 if ($resqlRowidClone) {
3668 while ($objRowidClone = $this->db->fetch_object($resqlRowidClone)) {
3669 $cloneId[] = $objRowidClone->rowid;
3670 }
3671
3672 foreach ($cloneId as $toselectid) {
3673 $bookKeeping = new BookKeeping($this->db);
3674 if ($bookKeeping->fetch($toselectid)) {
3675 $code_journal = getDolGlobalString('ACCOUNTING_CLONING_ENABLE_INPUT_JOURNAL') ? $code_journal : $bookKeeping->code_journal;
3676 $journal_label = getDolGlobalString('ACCOUNTING_CLONING_ENABLE_INPUT_JOURNAL') ? $accountingJournal->label : $bookKeeping->journal_label;
3677 $sql = "SELECT piece_num, label_operation, numero_compte, label_compte, subledger_account, subledger_label, doc_type, code_journal, fk_user_author, doc_ref, fk_doc, fk_docdet, debit, credit, journal_label, sens, montant";
3678 $sql .= " FROM ".$this->db->prefix()."accounting_bookkeeping WHERE rowid = " . ((int) $toselectid);
3679
3680 $resql = $this->db->query($sql);
3681 if ($resql) {
3682 while ($obj = $this->db->fetch_object($resql)) {
3683 $docRef = $obj->doc_ref;
3684 if (preg_match('/ \‍((\d+)\‍)$/', $docRef, $matches)) {
3685 $docRef = preg_replace('/ \‍(\d+\‍)$/', ' (' . (((int) $matches[1]) + 1) . ')', $docRef);
3686 } else {
3687 $docRef .= ' (2)';
3688 }
3689
3690 $sql_insert = "INSERT INTO ".$this->db->prefix()."accounting_bookkeeping (";
3691 $sql_insert .= " piece_num";
3692 $sql_insert .= ", label_operation";
3693 $sql_insert .= ", numero_compte";
3694 $sql_insert .= ", label_compte";
3695 $sql_insert .= ", subledger_account";
3696 $sql_insert .= ", subledger_label";
3697 $sql_insert .= ", doc_type";
3698 $sql_insert .= ", code_journal";
3699 $sql_insert .= ", doc_date";
3700 $sql_insert .= ", date_creation";
3701 $sql_insert .= ", fk_user_author";
3702 $sql_insert .= ", doc_ref";
3703 $sql_insert .= ", fk_doc";
3704 $sql_insert .= ", fk_docdet";
3705 $sql_insert .= ", debit";
3706 $sql_insert .= ", credit";
3707 $sql_insert .= ", journal_label";
3708 $sql_insert .= ", sens";
3709 $sql_insert .= ", montant";
3710 $sql_insert .= ")";
3711 $sql_insert .= " VALUES (";
3712 $sql_insert .= ((int) $pieceNumNext);
3713 $sql_insert .= ", '" . $this->db->escape($obj->label_operation) . "'";
3714 $sql_insert .= ", '" . $this->db->escape($obj->numero_compte) . "'";
3715 $sql_insert .= ", '" . $this->db->escape($obj->label_compte) . "'";
3716 $sql_insert .= ", '" . $this->db->escape($obj->subledger_account) . "'";
3717 $sql_insert .= ", '" . $this->db->escape($obj->subledger_label) . "'";
3718 $sql_insert .= ", ''";
3719 $sql_insert .= ", '" . $this->db->escape($code_journal) . "'";
3720 $sql_insert .= ", '" . $this->db->idate($docdate)."'";
3721 $sql_insert .= ", '" . $this->db->idate($now)."'";
3722 $sql_insert .= ", ".($user->id > 0 ? ((int) $user->id) : "NULL");
3723 $sql_insert .= ", '" . $this->db->escape($docRef) . "'";
3724 $sql_insert .= ", 0";
3725 $sql_insert .= ", 0";
3726 $sql_insert .= ", " . (float) $obj->debit;
3727 $sql_insert .= ", " . (float) $obj->credit;
3728 $sql_insert .= ", '" . $this->db->escape($journal_label) . "'";
3729 $sql_insert .= ", '" . $this->db->escape($obj->sens) . "'";
3730 $sql_insert .= ", " . (float) $obj->montant;
3731 $sql_insert .= ")";
3732
3733 $resqlInsert = $this->db->query($sql_insert);
3734
3735 if ($resqlInsert) {
3736 setEventMessages($langs->trans('CloningSuccess', $pieceNumNext), null, 'mesgs');
3737 } else {
3738 setEventMessages($langs->trans('CloningFailed'), null, 'errors');
3739 $error++;
3740 }
3741 }
3742 }
3743 }
3744 }
3745 }
3746 }
3747 }
3748
3749 if ($error) {
3750 $this->db->rollback();
3751 return -1;
3752 } else {
3753 $this->db->commit();
3754 return 1;
3755 }
3756 }
3757
3766 public function newReturnAccount(array $toselect, $code_journal, $docdate)
3767 {
3768 global $langs, $user;
3769
3770 $error = 0;
3771
3772 $now = dol_now();
3773 if (empty($docdate)) {
3774 $docdate = $now;
3775 }
3776
3777 $accountingJournal = new AccountingJournal($this->db);
3778 $accountingJournal->fetch(0, $code_journal);
3779
3780 $this->db->begin();
3781 $sqlAlreadyExtourne = "SELECT DISTINCT(piece_num) FROM " .MAIN_DB_PREFIX. "accounting_bookkeeping WHERE label_operation LIKE '%Extourne%'";
3782 $resqlAlreadyExtourne = $this->db->query($sqlAlreadyExtourne);
3783 $alreadyExtourneT = array();
3784 if ($resqlAlreadyExtourne) {
3785 while ($obj4 = $this->db->fetch_object($resqlAlreadyExtourne)) {
3786 $alreadyExtourneT [] = $obj4->piece_num;
3787 }
3788 }
3789
3790 $idImplode = implode(',', $toselect);
3791 $sql1 = "SELECT DISTINCT(piece_num) from " . MAIN_DB_PREFIX . "accounting_bookkeeping WHERE rowid IN (".$this->db->sanitize($idImplode).")";
3792 $resql1 = $this->db->query($sql1);
3793 $pieceNumT = [];
3794
3795 if ($resql1) {
3796 while ($obj1 = $this->db->fetch_object($resql1)) {
3797 $pieceNumT [] = $obj1->piece_num;
3798 }
3799
3800 $i = mt_rand(0, 100);
3801 foreach ($pieceNumT as $pieceNum) {
3802 $newBookKeepingInstance = new BookKeeping($this->db);
3803 $pieceNumNext = $newBookKeepingInstance->getNextNumMvt();
3804 $extourneIds = [];
3805 $sql2 = "SELECT rowid FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping WHERE piece_num = ".((int) $pieceNum);
3806 $resql2 = $this->db->query($sql2);
3807
3808 if ($resql2) {
3809 while ($obj2 = $this->db->fetch_object($resql2)) {
3810 $extourneIds [] = $obj2->rowid;
3811 }
3812
3813 foreach ($extourneIds as $extourneId) {
3814 $newBookKeeping = new BookKeeping($this->db);
3815 $bookKeeping = new BookKeeping($this->db);
3816
3817 if ($bookKeeping->fetch($extourneId)) {
3818 if (in_array($bookKeeping->piece_num, $alreadyExtourneT)) {
3819 setEventMessages($langs->trans("AlreadyReturnedAccount", $bookKeeping->piece_num), null, 'errors');
3820 } else {
3821 $newBookKeeping->debit = $bookKeeping->credit;
3822 $newBookKeeping->credit = $bookKeeping->debit;
3823 if ($bookKeeping->sens == 'D') {
3824 $newBookKeeping->sens = 'C';
3825 } else {
3826 $newBookKeeping->sens = 'D';
3827 }
3828
3829 $newBookKeeping->label_operation = $langs->trans("ReturnAccount") . " " . $bookKeeping->piece_num . " - " . $bookKeeping->numero_compte . " - " . date('d/m/Y', dol_now()) . " - " . $i;
3830
3831 $newBookKeeping->numero_compte = $bookKeeping->numero_compte;
3832 $newBookKeeping->label_compte = $bookKeeping->label_compte;
3833 $newBookKeeping->doc_type = $bookKeeping->doc_type;
3834 $newBookKeeping->code_journal = $bookKeeping->code_journal;
3835 $newBookKeeping->doc_date = $docdate;
3836 $newBookKeeping->fk_user_author = $user->id;
3837 $newBookKeeping->doc_ref = $bookKeeping->doc_ref;
3838 $newBookKeeping->montant = $bookKeeping->montant;
3839 $newBookKeeping->journal_label = $bookKeeping->journal_label;
3840 $newBookKeeping->subledger_account = $bookKeeping->subledger_account;
3841 $newBookKeeping->subledger_label = $bookKeeping->subledger_label;
3842 }
3843 $createResult = $newBookKeeping->create($user);
3844
3845 if ($createResult >= 0) {
3846 $newBookKeeping->piece_num = $pieceNumNext;
3847 $newBookKeeping->fk_doc = $bookKeeping->fk_doc;
3848 $newBookKeeping->fk_docdet = $bookKeeping->fk_docdet;
3849 $newBookKeeping->update($user);
3850 setEventMessages($langs->trans("SuccessReturnedAccount", $bookKeeping->piece_num), null, 'mesgs');
3851 } else {
3852 setEventMessages($langs->trans("ErrorWhileCreating", $newBookKeeping->error), $newBookKeeping->errors, 'errors');
3853 $error++;
3854 }
3855 }
3856
3857 $i++;
3858 }
3859 }
3860 }
3861 }
3862
3863 if ($error) {
3864 $this->db->rollback();
3865 return -1;
3866 } else {
3867 $this->db->commit();
3868 return 1;
3869 }
3870 }
3871}
3872
3877{
3881 public $id;
3882
3886 public $doc_date = null;
3890 public $doc_type;
3891
3895 public $doc_ref;
3896
3900 public $fk_doc;
3901
3905 public $fk_docdet;
3906
3910 public $thirdparty_code;
3911
3915 public $subledger_account;
3916
3920 public $subledger_label;
3921
3925 public $numero_compte;
3926
3930 public $label_compte;
3931
3935 public $label_operation;
3936
3940 public $debit;
3941
3945 public $credit;
3946
3951 public $montant;
3952
3956 public $amount;
3957
3961 public $multicurrency_amount;
3962
3966 public $multicurrency_code;
3967
3971 public $sens;
3972
3976 public $matching_general;
3977
3981 public $lettering_code;
3982
3986 public $date_lettering;
3987
3991 public $fk_user_author;
3992
3993
3997 public $import_key;
3998
4002 public $code_journal;
4003
4007 public $journal_label;
4011 public $piece_num;
4012
4016 public $date_export;
4017
4021 public $date_lim_reglement;
4022
4026 public $code_tiers;
4027}
$id
Support class for third parties, contacts, members, users or resources.
Definition account.php:47
if(! $sortfield) if(! $sortorder) $object
Definition account.php:100
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous)
ajax_combobox($htmlname, $events=array(), $minLengthToAutocomplete=0, $forcefocus=0, $widthTypeOfAutocomplete='resolve', $idforemptyvalue='-1', $morecss='')
Convert a html select field into an ajax combobox.
Definition ajax.lib.php:476
$object ref
Definition info.php:90
Class to manage accounting accounts.
Class to manage accounting journals.
Class to manage Ledger (General Ledger and Subledger)
closeFiscalPeriod($fiscal_period_id, $new_fiscal_period_id, $separate_auxiliary_account=false, $generate_bookkeeping_records=true)
Close fiscal period.
getCountByMonthForFiscalPeriod($date_start, $date_end)
Get list of count by month into the fiscal period.
fetchAllBalance($sortorder='', $sortfield='', $limit=0, $offset=0, $filter='', $filtermode='AND', $option=0)
Load object in memory from the database.
getNomUrl($withpicto=0, $option='', $notooltip=0, $morecss='', $save_lastsearch_value=-1)
Return a link to the object card (with optionally the picto)
select_account($selectid, $htmlname='account', $showempty=0, $event=array(), $select_in=0, $select_out=0, $aabase='')
Return list of accounts with label by chart of accounts.
getFiscalPeriods()
Get list of fiscal period ordered by start date.
getNextNumMvt($mode='')
Return next bookkeeping piece number.
newCloneMass($toselect, $code_journal, $docdate)
Mass clone.
get_compte_desc($account=null)
Description of accounting account.
accountingLabelForOperation($thirdpartyname, $reference, $labelaccount, $full=0)
Generate label operation when operation is transferred into accounting according to ACCOUNTING_LABEL_...
__construct(DoliDB $db)
Constructor.
createFromClone(User $user, $fromid)
Load an object from its id and create a new one in database.
deleteByImportkey($importkey, $mode='')
Delete bookkeeping by importkey.
getRootAccount($account=null)
Return id and description of a root accounting account.
transformTransaction($direction=0, $piece_num='')
Transform transaction.
insertAccountingReversal($fiscal_period_id, $inventory_journal_id, $new_fiscal_period_id, $date_start, $date_end)
Insert accounting reversal into the inventory journal of the new fiscal period.
newReturnAccount(array $toselect, $code_journal, $docdate)
Mass ReturnAccount.
fetchPerMvt($piecenum, $mode='')
Load an accounting document into memory from database.
assignAccountMass($toselect, $accounting_account=0)
Mass account assignment.
create(User $user, $notrigger=0)
Create object into database.
updateByMvt($piece_num='', $field='', $value='', $mode='')
Update accounting movement.
createStd(User $user, $notrigger=0, $mode='')
Create object into database.
update(User $user, $notrigger=0, $mode='')
Update object into database.
validBookkeepingDate($date)
Is the bookkeeping date valid (on an open period or not on a closed period) ?
deleteByYearAndJournal($delyear=0, $journal='', $mode='', $delmonth=0)
Delete bookkeeping by year.
loadFiscalPeriods($force=false, $mode='active')
Load list of active fiscal period.
fetchAllByAccount($sortorder='', $sortfield='', $limit=0, $offset=0, array $filter=array(), $filtermode='AND', $option=0, $countonly=0)
Load object in memory from the database in ->lines.
createFromValues($doc_date, $doc_ref, $doc_type, $fk_doc, $fk_docdet, $numero_compte, $label_compte, $label_operation, $amount, $code_journal, $journal_label, $subledger_account)
Create a line in database from values as parameters.
initAsSpecimen()
Initialise object with example values Id must be 0 if object instance is a specimen.
deleteMvtNum($piecenum, $mode='', $notrigger=0)
Delete bookkeeping by piece number.
newClone($piecenum, $code_journal, $docdate)
Clone accounting entry.
getCanModifyBookkeepingSQL($alias='', $force=false)
Get SQL string for check if the bookkeeping can be modified or deleted ? (cached)
fetch($id, $ref=null, $mode='')
Load object in memory from the database.
fetchAll($sortorder='', $sortfield='', $limit=0, $offset=0, $filter='', $filtermode='AND', $showAlreadyExportMovements=1)
Load object in memory from the database.
accountingResult($date_start, $date_end)
Define accounting result.
canModifyBookkeeping($id, $mode='')
Is the bookkeeping can be modified or deleted ?
fetchAllPerMvt($piecenum, $mode='')
Load all accounting lines related to a given transaction ID $piecenum.
validateMovementForFiscalPeriod($date_start, $date_end)
Validate all movement between the specified dates.
export_bookkeeping($model='ebp')
Export bookkeeping.
Class BookKeepingLine.
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Parent class for class inheritance lines of business objects This class is useless for the moment so ...
Class to manage Dolibarr database access.
Class to manage fiscal year.
static selectarray($htmlname, $array, $id='', $show_empty=0, $key_in_label=0, $value_as_key=0, $moreparam='', $translate=0, $maxlen=0, $disabled=0, $sort='', $morecss='minwidth75', $addjscombo=1, $moreparamonempty='', $disablebademail=0, $nohtmlescape=0)
Return a HTML select string, built from an array of key+value.
Class to manage Dolibarr users.
dolSqlDateFilter($datefield, $day_date, $month_date, $year_date, $excludefirstand=0, $gm=false)
Generate a SQL string to make a filter into a range (for second of date until last second of date).
Definition date.lib.php:401
dol_get_last_hour($date, $gm='tzserver')
Return GMT time for last hour of a given GMT date (it replaces hours, min and second part to 23:59:59...
Definition date.lib.php:666
if(!isModEnabled('ai')||!getDolGlobalString('AI_ASSISTANT_ENABLED')) global $conf
The main.inc.php has been included so the following variable are now defined:
if(!isModEnabled('ai')||!getDolGlobalString('AI_ASSISTANT_ENABLED')) global $db
API class for accounts.
$date_start
Variables from include:
dol_now($mode='gmt')
Return date for now.
setEventMessages($mesg, $mesgs, $style='mesgs', $messagekey='', $noduplicate=0, $attop=0)
Set event messages in dol_events session object.
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
dolBuildUrl($url, $params=[], $addtoken=false, $anchor='')
Return path of url.
img_object($titlealt, $picto, $moreatt='', $pictoisfullpath=0, $srconly=0, $notitle=0, $allowothertags=array())
Show a picto called object_picto (generic function)
natural_search($fields, $value, $mode=0, $nofirstand=0, $sqltoadd='')
Generate natural SQL search string for a criteria (this criteria can be tested on one or several fiel...
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
forgeSQLFromUniversalSearchCriteria($filter, &$errorstr='', $noand=0, $nopar=0, $noerror=0, $forbiddenfields=array())
forgeSQLFromUniversalSearchCriteria
dol_buildpath($path, $type=0, $returnemptyifnotfound=0)
Return path of url or filesystem.
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
dol_trunc($string, $size=40, $trunc='right', $stringencoding='UTF-8', $nodot=0, $display=0)
Truncate a string to a particular length adding '...' if string larger than length.
getDolGlobalString($key, $default='')
Return a Dolibarr global constant string value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
print $langs trans('Date')." left Ref Label right Qty right Price right TotalHT right TotalTTC right right right right right right right right right centpercent right TotalHT right n right VAT right n right TotalVAT right n No sujeto a RE IRPF right TotalLT1 right n right TotalLT2 right n right TotalTTC right n takeposcustomercurrency takeposcustomercurrency takeposcustomercurrency takeposcustomercurrency right TotalTTC takeposcustomercurrency right takeposcustomercurrency n right Paid right PaymentTypeShortLIQ right SELECT p pos_change as p datep as p p num_paiement as f pf amount as amount
Definition receipt.php:494
print $langs trans('Date')." left Ref Label right Qty right Price right TotalHT right TotalTTC right right right right right right right right right centpercent right TotalHT right n right VAT right n right TotalVAT right n No sujeto a RE IRPF right TotalLT1 right n right TotalLT2 right n right TotalTTC right n takeposcustomercurrency takeposcustomercurrency takeposcustomercurrency takeposcustomercurrency right TotalTTC takeposcustomercurrency right takeposcustomercurrency n right Paid right PaymentTypeShortLIQ right SELECT p pos_change as p datep as date
Definition receipt.php:492