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