dolibarr 20.0.4
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-2022 Alexandre Spangaro <aspangaro@open-dsi.fr>
4 * Copyright (C) 2015-2020 Florian Henry <florian.henry@open-concept.pro>
5 * Copyright (C) 2018-2024 Frédéric France <frederic.france@free.fr>
6 * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
7 * Copyright (C) 2024 Jose MARTINEZ <jose.martinez@pichinov.com>
8 *
9 * This program is free software; you can redistribute it and/or modify
10 * it under the terms of the GNU General Public License as published by
11 * the Free Software Foundation; either version 3 of the License, or
12 * (at your option) any later version.
13 *
14 * This program is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
18 *
19 * You should have received a copy of the GNU General Public License
20 * along with this program. If not, see <https://www.gnu.org/licenses/>.
21 */
22
29// Class
30require_once DOL_DOCUMENT_ROOT.'/core/class/commonobject.class.php';
31require_once DOL_DOCUMENT_ROOT.'/core/class/commonobjectline.class.php';
32require_once DOL_DOCUMENT_ROOT.'/core/class/fiscalyear.class.php';
33require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingjournal.class.php';
34require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingaccount.class.php';
35
40{
44 public $element = 'accountingbookkeeping';
45
49 public $table_element = 'accounting_bookkeeping';
50
54 public $entity;
55
59 public $lines = array();
60
64 public $id;
65
69 public $doc_date;
70
74 public $date_lim_reglement;
75
79 public $doc_type;
80
84 public $doc_ref;
85
89 public $fk_doc;
90
94 public $fk_docdet;
95
99 public $thirdparty_code;
100
104 public $subledger_account;
105
109 public $subledger_label;
110
114 public $numero_compte;
115
119 public $label_compte;
120
124 public $label_operation;
125
129 public $debit;
130
134 public $credit;
135
140 public $montant;
141
146 public $amount;
147
151 public $sens;
152
156 public $fk_user_author;
157
161 public $import_key;
162
166 public $code_journal;
167
171 public $journal_label;
172
176 public $piece_num;
177
181 public $linesmvt = array();
182
186 public $linesexport = array();
187
191 public $date_validation;
192
196 public $date_export;
197
201 public $picto = 'generic';
202
206 public static $can_modify_bookkeeping_sql_cached;
207
208
214 public function __construct(DoliDB $db)
215 {
216 $this->db = $db;
217 }
218
226 public function create(User $user, $notrigger = 0)
227 {
228 global $conf, $langs;
229
230 dol_syslog(__METHOD__, LOG_DEBUG);
231
232 $error = 0;
233
234 // Clean parameters</center>
235 if (isset($this->doc_type)) {
236 $this->doc_type = trim($this->doc_type);
237 }
238 if (isset($this->doc_ref)) {
239 $this->doc_ref = trim($this->doc_ref);
240 }
241 if (isset($this->fk_doc)) {
242 $this->fk_doc = (int) $this->fk_doc;
243 }
244 if (isset($this->fk_docdet)) {
245 $this->fk_docdet = (int) $this->fk_docdet;
246 }
247 if (isset($this->thirdparty_code)) {
248 $this->thirdparty_code = trim($this->thirdparty_code);
249 }
250 if (isset($this->subledger_account)) {
251 $this->subledger_account = trim($this->subledger_account);
252 }
253 if (isset($this->subledger_label)) {
254 $this->subledger_label = trim($this->subledger_label);
255 }
256 if (isset($this->numero_compte)) {
257 $this->numero_compte = trim($this->numero_compte);
258 }
259 if (isset($this->label_compte)) {
260 $this->label_compte = trim($this->label_compte);
261 }
262 if (isset($this->label_operation)) {
263 $this->label_operation = trim($this->label_operation);
264 }
265 if (isset($this->debit)) {
266 $this->debit = (float) $this->debit;
267 }
268 if (isset($this->credit)) {
269 $this->credit = (float) $this->credit;
270 }
271 if (isset($this->montant)) {
272 $this->montant = (float) $this->montant;
273 }
274 if (isset($this->amount)) {
275 $this->amount = (float) $this->amount;
276 }
277 if (isset($this->sens)) {
278 $this->sens = trim($this->sens);
279 }
280 if (isset($this->import_key)) {
281 $this->import_key = trim($this->import_key);
282 }
283 if (isset($this->code_journal)) {
284 $this->code_journal = trim($this->code_journal);
285 }
286 if (isset($this->journal_label)) {
287 $this->journal_label = trim($this->journal_label);
288 }
289 if (isset($this->piece_num)) {
290 $this->piece_num = (int) $this->piece_num;
291 }
292 if (empty($this->debit)) {
293 $this->debit = 0.0;
294 }
295 if (empty($this->credit)) {
296 $this->credit = 0.0;
297 }
298
299 $result = $this->validBookkeepingDate($this->doc_date);
300 if ($result < 0) {
301 return -1;
302 } elseif ($result == 0) {
303 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
304 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod');
305 } else {
306 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod');
307 }
308 return -1;
309 }
310
311 // Check parameters
312 if (($this->numero_compte == "") || $this->numero_compte == '-1' || $this->numero_compte == 'NotDefined') {
313 $langs->loadLangs(array("errors"));
314 if (in_array($this->doc_type, array('bank', 'expense_report'))) {
315 $this->errors[] = $langs->trans('ErrorFieldAccountNotDefinedForBankLine', $this->fk_docdet, $this->doc_type);
316 } else {
317 //$this->errors[]=$langs->trans('ErrorFieldAccountNotDefinedForInvoiceLine', $this->doc_ref, $this->label_compte);
318 $mesg = $this->doc_ref.', '.$langs->trans("AccountAccounting").': '.($this->numero_compte != -1 ? $this->numero_compte : $langs->trans("Unknown"));
319 if ($this->subledger_account && $this->subledger_account != $this->numero_compte) {
320 $mesg .= ', '.$langs->trans("SubledgerAccount").': '.$this->subledger_account;
321 }
322 $this->errors[] = $langs->trans('ErrorFieldAccountNotDefinedForLine', $mesg);
323 }
324
325 return -1;
326 }
327
328 $this->db->begin();
329
330 $this->piece_num = 0;
331
332 // First check if line not yet already in bookkeeping.
333 // 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
334 // with same doc_type, fk_doc, numero_compte for 1 invoice line when using localtaxes with same account)
335 // WARNING: This is not reliable, label may have been modified. This is just a small protection.
336 // The page that make transfer make the test on couple (doc_type - fk_doc) only.
337 $sql = "SELECT count(*) as nb";
338 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
339 $sql .= " WHERE doc_type = '".$this->db->escape($this->doc_type)."'";
340 $sql .= " AND fk_doc = ".((int) $this->fk_doc);
341 if (getDolGlobalString('ACCOUNTANCY_ENABLE_FKDOCDET')) {
342 // DO NOT USE THIS IN PRODUCTION. This will generate a lot of trouble into reports and will corrupt database (by generating duplicate entries.
343 $sql .= " AND fk_docdet = ".((int) $this->fk_docdet); // This field can be 0 if record is for several lines
344 }
345 $sql .= " AND numero_compte = '".$this->db->escape($this->numero_compte)."'";
346 $sql .= " AND label_operation = '".$this->db->escape($this->label_operation)."'";
347 if (!empty($this->subledger_account)) {
348 $sql .= " AND subledger_account = '".$this->db->escape($this->subledger_account)."'";
349 }
350 $sql .= " AND entity = ".$conf->entity; // Do not use getEntity for accounting features
351
352 $resql = $this->db->query($sql);
353
354 if ($resql) {
355 $row = $this->db->fetch_object($resql);
356 if ($row->nb == 0) { // Not already into bookkeeping
357 // Check to know if piece_num already exists for data we try to insert to reuse the same value
358 $sqlnum = "SELECT piece_num";
359 $sqlnum .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
360 $sqlnum .= " WHERE doc_type = '".$this->db->escape($this->doc_type)."'"; // For example doc_type = 'bank'
361 $sqlnum .= " AND fk_doc = ".((int) $this->fk_doc);
362 if (getDolGlobalString('ACCOUNTANCY_ENABLE_FKDOCDET')) {
363 // fk_docdet is rowid into llx_bank or llx_facturedet or llx_facturefourndet, or ...
364 $sqlnum .= " AND fk_docdet = ".((int) $this->fk_docdet);
365 }
366 $sqlnum .= " AND doc_ref = '".$this->db->escape($this->doc_ref)."'"; // ref of source object
367 $sqlnum .= " AND entity = ".$conf->entity; // Do not use getEntity for accounting features
368
369 dol_syslog(get_class($this).":: create sqlnum=".$sqlnum, LOG_DEBUG);
370 $resqlnum = $this->db->query($sqlnum);
371 if ($resqlnum) {
372 $objnum = $this->db->fetch_object($resqlnum);
373 $this->piece_num = $objnum->piece_num;
374 }
375
376 dol_syslog(get_class($this)."::create this->piece_num=".$this->piece_num, LOG_DEBUG);
377 if (empty($this->piece_num)) {
378 $sqlnum = "SELECT MAX(piece_num)+1 as maxpiecenum";
379 $sqlnum .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
380 $sqlnum .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
381
382 $resqlnum = $this->db->query($sqlnum);
383 if ($resqlnum) {
384 $objnum = $this->db->fetch_object($resqlnum);
385 $this->piece_num = $objnum->maxpiecenum;
386 }
387 dol_syslog(get_class($this).":: create now this->piece_num=".$this->piece_num, LOG_DEBUG);
388 }
389 if (empty($this->piece_num)) {
390 $this->piece_num = 1;
391 }
392
393 $now = dol_now();
394
395 $sql = "INSERT INTO ".MAIN_DB_PREFIX.$this->table_element." (";
396 $sql .= "doc_date";
397 $sql .= ", date_lim_reglement";
398 $sql .= ", doc_type";
399 $sql .= ", doc_ref";
400 $sql .= ", fk_doc";
401 $sql .= ", fk_docdet";
402 $sql .= ", thirdparty_code";
403 $sql .= ", subledger_account";
404 $sql .= ", subledger_label";
405 $sql .= ", numero_compte";
406 $sql .= ", label_compte";
407 $sql .= ", label_operation";
408 $sql .= ", debit";
409 $sql .= ", credit";
410 $sql .= ", montant";
411 $sql .= ", sens";
412 $sql .= ", fk_user_author";
413 $sql .= ", date_creation";
414 $sql .= ", code_journal";
415 $sql .= ", journal_label";
416 $sql .= ", piece_num";
417 $sql .= ', entity';
418 $sql .= ") VALUES (";
419 $sql .= "'".$this->db->idate($this->doc_date)."'";
420 $sql .= ", ".(!isset($this->date_lim_reglement) || dol_strlen($this->date_lim_reglement) == 0 ? 'NULL' : "'".$this->db->idate($this->date_lim_reglement)."'");
421 $sql .= ", '".$this->db->escape($this->doc_type)."'";
422 $sql .= ", '".$this->db->escape($this->doc_ref)."'";
423 $sql .= ", ".((int) $this->fk_doc);
424 $sql .= ", ".((int) $this->fk_docdet);
425 $sql .= ", ".(!empty($this->thirdparty_code) ? ("'".$this->db->escape($this->thirdparty_code)."'") : "NULL");
426 $sql .= ", ".(!empty($this->subledger_account) ? ("'".$this->db->escape($this->subledger_account)."'") : "NULL");
427 $sql .= ", ".(!empty($this->subledger_label) ? ("'".$this->db->escape($this->subledger_label)."'") : "NULL");
428 $sql .= ", '".$this->db->escape($this->numero_compte)."'";
429 $sql .= ", ".(!empty($this->label_compte) ? ("'".$this->db->escape($this->label_compte)."'") : "NULL");
430 $sql .= ", '".$this->db->escape($this->label_operation)."'";
431 $sql .= ", ".((float) $this->debit);
432 $sql .= ", ".((float) $this->credit);
433 $sql .= ", ".((float) $this->montant);
434 $sql .= ", ".(!empty($this->sens) ? ("'".$this->db->escape($this->sens)."'") : "NULL");
435 $sql .= ", '".$this->db->escape($this->fk_user_author)."'";
436 $sql .= ", '".$this->db->idate($now)."'";
437 $sql .= ", '".$this->db->escape($this->code_journal)."'";
438 $sql .= ", ".(!empty($this->journal_label) ? ("'".$this->db->escape($this->journal_label)."'") : "NULL");
439 $sql .= ", ".((int) $this->piece_num);
440 $sql .= ", ".(!isset($this->entity) ? $conf->entity : $this->entity);
441 $sql .= ")";
442
443 $resql = $this->db->query($sql);
444 if ($resql) {
445 $id = $this->db->last_insert_id(MAIN_DB_PREFIX.$this->table_element);
446
447 if ($id > 0) {
448 $this->id = $id;
449 $result = 0;
450 } else {
451 $result = -2;
452 $error++;
453 $this->errors[] = 'Error Create Error '.$result.' lecture ID';
454 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
455 }
456 } else {
457 $result = -1;
458 $error++;
459 $this->errors[] = 'Error '.$this->db->lasterror();
460 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
461 }
462 } else { // Already exists
463 $result = -3;
464 $error++;
465 $this->error = 'BookkeepingRecordAlreadyExists';
466 dol_syslog(__METHOD__.' '.$this->error, LOG_WARNING);
467 }
468 } else {
469 $result = -5;
470 $error++;
471 $this->errors[] = 'Error '.$this->db->lasterror();
472 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
473 }
474
475 // Uncomment this and change MYOBJECT to your own tag if you
476 // want this action to call a trigger.
477 //if (! $error && ! $notrigger) {
478
479 // // Call triggers
480 // $result=$this->call_trigger('MYOBJECT_CREATE',$user);
481 // if ($result < 0) $error++;
482 // // End call triggers
483 //}
484
485 // Commit or rollback
486 if ($error) {
487 $this->db->rollback();
488 return -1 * $error;
489 } else {
490 $this->db->commit();
491 return $result;
492 }
493 }
494
505 public function getNomUrl($withpicto = 0, $option = '', $notooltip = 0, $morecss = '', $save_lastsearch_value = -1)
506 {
507 global $db, $conf, $langs;
508 global $dolibarr_main_authentication, $dolibarr_main_demo;
509 global $menumanager, $hookmanager;
510
511 if (!empty($conf->dol_no_mouse_hover)) {
512 $notooltip = 1; // Force disable tooltips
513 }
514
515 $result = '';
516 $companylink = '';
517
518 $label = '<u>'.$langs->trans("Transaction").'</u>';
519 $label .= '<br>';
520 $label .= '<b>'.$langs->trans('Ref').':</b> '.$this->piece_num;
521
522 $url = DOL_URL_ROOT.'/accountancy/bookkeeping/card.php?piece_num='.$this->piece_num;
523
524 if ($option != 'nolink') {
525 // Add param to save lastsearch_values or not
526 $add_save_lastsearch_values = ($save_lastsearch_value == 1 ? 1 : 0);
527 if ($save_lastsearch_value == -1 && isset($_SERVER["PHP_SELF"]) && preg_match('/list\.php/', $_SERVER["PHP_SELF"])) {
528 $add_save_lastsearch_values = 1;
529 }
530 if ($add_save_lastsearch_values) {
531 $url .= '&save_lastsearch_values=1';
532 }
533 }
534
535 $linkclose = '';
536 if (empty($notooltip)) {
537 if (getDolGlobalString('MAIN_OPTIMIZEFORTEXTBROWSER')) {
538 $label = $langs->trans("ShowTransaction");
539 $linkclose .= ' alt="'.dol_escape_htmltag($label, 1).'"';
540 }
541 $linkclose .= ' title="'.dol_escape_htmltag($label, 1).'"';
542 $linkclose .= ' class="classfortooltip'.($morecss ? ' '.$morecss : '').'"';
543 } else {
544 $linkclose = ($morecss ? ' class="'.$morecss.'"' : '');
545 }
546
547 $linkstart = '<a href="'.$url.'"';
548 $linkstart .= $linkclose.'>';
549 $linkend = '</a>';
550
551 $result .= $linkstart;
552 if ($withpicto) {
553 $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);
554 }
555 if ($withpicto != 2) {
556 $result .= $this->piece_num;
557 }
558 $result .= $linkend;
559 //if ($withpicto != 2) $result.=(($addlabel && $this->label) ? $sep . dol_trunc($this->label, ($addlabel > 1 ? $addlabel : 0)) : '');
560
561 global $action;
562 $hookmanager->initHooks(array($this->element . 'dao'));
563 $parameters = array('id' => $this->id, 'getnomurl' => &$result);
564 $reshook = $hookmanager->executeHooks('getNomUrl', $parameters, $this, $action); // Note that $action and $object may have been modified by some hooks
565 if ($reshook > 0) {
566 $result = $hookmanager->resPrint;
567 } else {
568 $result .= $hookmanager->resPrint;
569 }
570 return $result;
571 }
572
581 public function createStd(User $user, $notrigger = 0, $mode = '')
582 {
583 global $conf, $langs;
584
585 $langs->loadLangs(array("accountancy", "bills", "compta"));
586
587 dol_syslog(__METHOD__, LOG_DEBUG);
588
589 $error = 0;
590
591 // Clean parameters
592 if (isset($this->doc_type)) {
593 $this->doc_type = trim($this->doc_type);
594 }
595 if (isset($this->doc_ref)) {
596 $this->doc_ref = trim($this->doc_ref);
597 }
598 if (isset($this->fk_doc)) {
599 $this->fk_doc = (int) $this->fk_doc;
600 }
601 if (isset($this->fk_docdet)) {
602 $this->fk_docdet = (int) $this->fk_docdet;
603 }
604 if (isset($this->thirdparty_code)) {
605 $this->thirdparty_code = trim($this->thirdparty_code);
606 }
607 if (isset($this->subledger_account)) {
608 $this->subledger_account = trim($this->subledger_account);
609 }
610 if (isset($this->subledger_label)) {
611 $this->subledger_label = trim($this->subledger_label);
612 }
613 if (isset($this->numero_compte)) {
614 $this->numero_compte = trim($this->numero_compte);
615 }
616 if (isset($this->label_compte)) {
617 $this->label_compte = trim($this->label_compte);
618 }
619 if (isset($this->label_operation)) {
620 $this->label_operation = trim($this->label_operation);
621 }
622 if (isset($this->sens)) {
623 $this->sens = trim($this->sens);
624 }
625 if (isset($this->import_key)) {
626 $this->import_key = trim($this->import_key);
627 }
628 if (isset($this->code_journal)) {
629 $this->code_journal = trim($this->code_journal);
630 }
631 if (isset($this->journal_label)) {
632 $this->journal_label = trim($this->journal_label);
633 }
634 if (isset($this->piece_num)) {
635 $this->piece_num = (int) $this->piece_num;
636 }
637 if (empty($this->debit)) {
638 $this->debit = 0;
639 }
640 if (empty($this->credit)) {
641 $this->credit = 0;
642 }
643 if (empty($this->montant)) {
644 $this->montant = 0;
645 }
646
647 $result = $this->validBookkeepingDate($this->doc_date);
648 if ($result < 0) {
649 return -1;
650 } elseif ($result == 0) {
651 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
652 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod');
653 } else {
654 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod');
655 }
656 return -1;
657 }
658
659 $this->debit = (float) price2num($this->debit, 'MT');
660 $this->credit = (float) price2num($this->credit, 'MT');
661 $this->montant = (float) price2num($this->montant, 'MT');
662
663 $now = dol_now();
664
665 // Check parameters
666 $this->journal_label = $langs->trans($this->journal_label);
667
668 // Insert request
669 $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.$mode.' (';
670 $sql .= 'doc_date,';
671 $sql .= 'date_lim_reglement,';
672 $sql .= 'doc_type,';
673 $sql .= 'doc_ref,';
674 $sql .= 'fk_doc,';
675 $sql .= 'fk_docdet,';
676 $sql .= 'thirdparty_code,';
677 $sql .= 'subledger_account,';
678 $sql .= 'subledger_label,';
679 $sql .= 'numero_compte,';
680 $sql .= 'label_compte,';
681 $sql .= 'label_operation,';
682 $sql .= 'debit,';
683 $sql .= 'credit,';
684 $sql .= 'montant,';
685 $sql .= 'sens,';
686 $sql .= 'fk_user_author,';
687 $sql .= 'date_creation,';
688 $sql .= 'code_journal,';
689 $sql .= 'journal_label,';
690 $sql .= 'piece_num,';
691 $sql .= 'entity';
692 $sql .= ') VALUES (';
693 $sql .= ' '.(!isset($this->doc_date) || dol_strlen($this->doc_date) == 0 ? 'NULL' : "'".$this->db->idate($this->doc_date)."'").',';
694 $sql .= ' '.(!isset($this->date_lim_reglement) || dol_strlen($this->date_lim_reglement) == 0 ? 'NULL' : "'".$this->db->idate($this->date_lim_reglement)."'").',';
695 $sql .= ' '.(!isset($this->doc_type) ? 'NULL' : "'".$this->db->escape($this->doc_type)."'").',';
696 $sql .= ' '.(!isset($this->doc_ref) ? 'NULL' : "'".$this->db->escape($this->doc_ref)."'").',';
697 $sql .= ' '.(empty($this->fk_doc) ? '0' : (int) $this->fk_doc).',';
698 $sql .= ' '.(empty($this->fk_docdet) ? '0' : (int) $this->fk_docdet).',';
699 $sql .= ' '.(!isset($this->thirdparty_code) ? 'NULL' : "'".$this->db->escape($this->thirdparty_code)."'").',';
700 $sql .= ' '.(!isset($this->subledger_account) ? 'NULL' : "'".$this->db->escape($this->subledger_account)."'").',';
701 $sql .= ' '.(!isset($this->subledger_label) ? 'NULL' : "'".$this->db->escape($this->subledger_label)."'").',';
702 $sql .= ' '.(!isset($this->numero_compte) ? 'NULL' : "'".$this->db->escape($this->numero_compte)."'").',';
703 $sql .= ' '.(!isset($this->label_compte) ? 'NULL' : "'".$this->db->escape($this->label_compte)."'").',';
704 $sql .= ' '.(!isset($this->label_operation) ? 'NULL' : "'".$this->db->escape($this->label_operation)."'").',';
705 $sql .= ' '.(!isset($this->debit) ? 'NULL' : $this->debit).',';
706 $sql .= ' '.(!isset($this->credit) ? 'NULL' : $this->credit).',';
707 $sql .= ' '.(!isset($this->montant) ? 'NULL' : $this->montant).',';
708 $sql .= ' '.(!isset($this->sens) ? 'NULL' : "'".$this->db->escape($this->sens)."'").',';
709 $sql .= ' '.((int) $user->id).',';
710 $sql .= ' '."'".$this->db->idate($now)."',";
711 $sql .= ' '.(empty($this->code_journal) ? 'NULL' : "'".$this->db->escape($this->code_journal)."'").',';
712 $sql .= ' '.(empty($this->journal_label) ? 'NULL' : "'".$this->db->escape($this->journal_label)."'").',';
713 $sql .= ' '.(empty($this->piece_num) ? 'NULL' : $this->db->escape($this->piece_num)).',';
714 $sql .= ' '.(!isset($this->entity) ? $conf->entity : $this->entity);
715 $sql .= ')';
716
717 $this->db->begin();
718
719 $resql = $this->db->query($sql);
720 if (!$resql) {
721 $error++;
722 $this->errors[] = 'Error '.$this->db->lasterror();
723 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
724 }
725
726 if (!$error) {
727 $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX.$this->table_element.$mode);
728 }
729
730 // Commit or rollback
731 if ($error) {
732 $this->db->rollback();
733
734 return -1 * $error;
735 } else {
736 $this->db->commit();
737
738 return $this->id;
739 }
740 }
741
750 public function fetch($id, $ref = null, $mode = '')
751 {
752 global $conf;
753
754 dol_syslog(__METHOD__, LOG_DEBUG);
755
756 $sql = 'SELECT';
757 $sql .= ' t.rowid,';
758 $sql .= " t.doc_date,";
759 $sql .= " t.date_lim_reglement,";
760 $sql .= " t.doc_type,";
761 $sql .= " t.doc_ref,";
762 $sql .= " t.fk_doc,";
763 $sql .= " t.fk_docdet,";
764 $sql .= " t.thirdparty_code,";
765 $sql .= " t.subledger_account,";
766 $sql .= " t.subledger_label,";
767 $sql .= " t.numero_compte,";
768 $sql .= " t.label_compte,";
769 $sql .= " t.label_operation,";
770 $sql .= " t.debit,";
771 $sql .= " t.credit,";
772 $sql .= " t.montant as amount,";
773 $sql .= " t.sens,";
774 $sql .= " t.fk_user_author,";
775 $sql .= " t.import_key,";
776 $sql .= " t.code_journal,";
777 $sql .= " t.journal_label,";
778 $sql .= " t.piece_num,";
779 $sql .= " t.date_creation,";
780 // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
781 if ($mode != "_tmp") {
782 $sql .= " t.date_export,";
783 }
784 $sql .= " t.date_validated as date_validation";
785 $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.$mode.' as t';
786 $sql .= ' WHERE 1 = 1';
787 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
788 if (null !== $ref) {
789 $sql .= " AND t.rowid = ".((int) $ref);
790 } else {
791 $sql .= " AND t.rowid = ".((int) $id);
792 }
793
794 $resql = $this->db->query($sql);
795 if ($resql) {
796 $numrows = $this->db->num_rows($resql);
797 if ($numrows) {
798 $obj = $this->db->fetch_object($resql);
799
800 $this->id = $obj->rowid;
801
802 $this->doc_date = $this->db->jdate($obj->doc_date);
803 $this->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
804 $this->doc_type = $obj->doc_type;
805 $this->doc_ref = $obj->doc_ref;
806 $this->fk_doc = $obj->fk_doc;
807 $this->fk_docdet = $obj->fk_docdet;
808 $this->thirdparty_code = $obj->thirdparty_code;
809 $this->subledger_account = $obj->subledger_account;
810 $this->subledger_label = $obj->subledger_label;
811 $this->numero_compte = $obj->numero_compte;
812 $this->label_compte = $obj->label_compte;
813 $this->label_operation = $obj->label_operation;
814 $this->debit = $obj->debit;
815 $this->credit = $obj->credit;
816 $this->montant = $obj->amount;
817 $this->amount = $obj->amount;
818 $this->sens = $obj->sens;
819 $this->fk_user_author = $obj->fk_user_author;
820 $this->import_key = $obj->import_key;
821 $this->code_journal = $obj->code_journal;
822 $this->journal_label = $obj->journal_label;
823 $this->piece_num = $obj->piece_num;
824 $this->date_creation = $this->db->jdate($obj->date_creation);
825 $this->date_export = $this->db->jdate($obj->date_export);
826 $this->date_validation = isset($obj->date_validation) ? $this->db->jdate($obj->date_validation) : '';
827 }
828 $this->db->free($resql);
829
830 if ($numrows) {
831 return 1;
832 } else {
833 return 0;
834 }
835 } else {
836 $this->errors[] = 'Error '.$this->db->lasterror();
837 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
838
839 return -1;
840 }
841 }
842
843
857 public function fetchAllByAccount($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, array $filter = array(), $filtermode = 'AND', $option = 0, $countonly = 0)
858 {
859 global $conf;
860
861 dol_syslog(__METHOD__, LOG_DEBUG);
862
863 $this->lines = array();
864 $num = 0;
865
866 $sql = 'SELECT';
867 if ($countonly) {
868 $sql .= ' COUNT(t.rowid) as nb';
869 } else {
870 $sql .= ' t.rowid,';
871 $sql .= " t.doc_date,";
872 $sql .= " t.doc_type,";
873 $sql .= " t.doc_ref,";
874 $sql .= " t.fk_doc,";
875 $sql .= " t.fk_docdet,";
876 $sql .= " t.thirdparty_code,";
877 $sql .= " t.subledger_account,";
878 $sql .= " t.subledger_label,";
879 $sql .= " t.numero_compte,";
880 $sql .= " t.label_compte,";
881 $sql .= " t.label_operation,";
882 $sql .= " t.debit,";
883 $sql .= " t.credit,";
884 $sql .= " t.montant as amount,";
885 $sql .= " t.sens,";
886 $sql .= " t.multicurrency_amount,";
887 $sql .= " t.multicurrency_code,";
888 $sql .= " t.lettering_code,";
889 $sql .= " t.date_lettering,";
890 $sql .= " t.fk_user_author,";
891 $sql .= " t.import_key,";
892 $sql .= " t.code_journal,";
893 $sql .= " t.journal_label,";
894 $sql .= " t.piece_num,";
895 $sql .= " t.date_creation,";
896 $sql .= " t.date_export,";
897 $sql .= " t.date_validated as date_validation,";
898 $sql .= " t.import_key";
899 }
900 // Manage filter
901 $sqlwhere = array();
902 if (count($filter) > 0) {
903 foreach ($filter as $key => $value) {
904 if ($key == 't.doc_date>=') {
905 $sqlwhere[] = "t.doc_date >= '".$this->db->idate($value)."'";
906 } elseif ($key == 't.doc_date<=') {
907 $sqlwhere[] = "t.doc_date <= '".$this->db->idate($value)."'";
908 } elseif ($key == 't.doc_date>') {
909 $sqlwhere[] = "t.doc_date > '".$this->db->idate($value)."'";
910 } elseif ($key == 't.doc_date<') {
911 $sqlwhere[] = "t.doc_date < '".$this->db->idate($value)."'";
912 } elseif ($key == 't.numero_compte>=') {
913 $sqlwhere[] = "t.numero_compte >= '".$this->db->escape($value)."'";
914 } elseif ($key == 't.numero_compte<=') {
915 $sqlwhere[] = "t.numero_compte <= '".$this->db->escape($value)."'";
916 } elseif ($key == 't.subledger_account>=') {
917 $sqlwhere[] = "t.subledger_account >= '".$this->db->escape($value)."'";
918 } elseif ($key == 't.subledger_account<=') {
919 $sqlwhere[] = "t.subledger_account <= '".$this->db->escape($value)."'";
920 } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
921 $sqlwhere[] = $this->db->sanitize($key).' = '.((int) $value);
922 } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
923 $sqlwhere[] = $this->db->sanitize($key).' LIKE \''.$this->db->escape($this->db->escapeforlike($value)).'%\'';
924 } elseif ($key == 't.date_creation>=') {
925 $sqlwhere[] = 't.date_creation >= \''.$this->db->idate($value).'\'';
926 } elseif ($key == 't.date_creation<=') {
927 $sqlwhere[] = 't.date_creation <= \''.$this->db->idate($value).'\'';
928 } elseif ($key == 't.date_export>=') {
929 $sqlwhere[] = 't.date_export >= \''.$this->db->idate($value).'\'';
930 } elseif ($key == 't.date_export<=') {
931 $sqlwhere[] = 't.date_export <= \''.$this->db->idate($value).'\'';
932 } elseif ($key == 't.date_validated>=') {
933 $sqlwhere[] = 't.date_validated >= \''.$this->db->idate($value).'\'';
934 } elseif ($key == 't.date_validated<=') {
935 $sqlwhere[] = 't.date_validated <= \''.$this->db->idate($value).'\'';
936 } elseif ($key == 't.credit' || $key == 't.debit') {
937 $sqlwhere[] = natural_search($key, $value, 1, 1);
938 } elseif ($key == 't.reconciled_option') {
939 $sqlwhere[] = 't.lettering_code IS NULL';
940 } elseif ($key == 't.code_journal' && !empty($value)) {
941 if (is_array($value)) {
942 $sqlwhere[] = natural_search("t.code_journal", implode(',', $value), 3, 1);
943 } else {
944 $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
945 }
946 } elseif ($key == 't.search_accounting_code_in' && !empty($value)) {
947 $sqlwhere[] = 't.numero_compte IN ('.$this->db->sanitize($value, 1).')';
948 } else {
949 $sqlwhere[] = natural_search($key, $value, 0, 1);
950 }
951 }
952 }
953 $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
954 $sql .= ' WHERE entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
955 if (count($sqlwhere) > 0) {
956 $sql .= " AND ".implode(" ".$this->db->sanitize($filtermode)." ", $sqlwhere);
957 }
958 // Filter by ledger account or subledger account
959 if (!empty($option)) {
960 $sql .= " AND t.subledger_account IS NOT NULL";
961 $sql .= " AND t.subledger_account <> ''";
962 $sortfield = 't.subledger_account'.($sortfield ? ','.$sortfield : '');
963 $sortorder = 'ASC'.($sortfield ? ','.$sortfield : '');
964 } else {
965 $sortfield = 't.numero_compte'.($sortfield ? ','.$sortfield : '');
966 $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
967 }
968
969 if (!$countonly) {
970 $sql .= $this->db->order($sortfield, $sortorder);
971 if (!empty($limit)) {
972 $sql .= $this->db->plimit($limit + 1, $offset);
973 }
974 }
975
976 $resql = $this->db->query($sql);
977 if ($resql) {
978 if ($countonly) {
979 $obj = $this->db->fetch_object($resql);
980 if ($obj) {
981 $num = $obj->nb;
982 }
983 } else {
984 $num = $this->db->num_rows($resql);
985
986 $i = 0;
987 while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
988 $line = new BookKeepingLine($this->db);
989
990 $line->id = $obj->rowid;
991
992 $line->doc_date = $this->db->jdate($obj->doc_date);
993 $line->doc_type = $obj->doc_type;
994 $line->doc_ref = $obj->doc_ref;
995 $line->fk_doc = $obj->fk_doc;
996 $line->fk_docdet = $obj->fk_docdet;
997 $line->thirdparty_code = $obj->thirdparty_code;
998 $line->subledger_account = $obj->subledger_account;
999 $line->subledger_label = $obj->subledger_label;
1000 $line->numero_compte = $obj->numero_compte;
1001 $line->label_compte = $obj->label_compte;
1002 $line->label_operation = $obj->label_operation;
1003 $line->debit = $obj->debit;
1004 $line->credit = $obj->credit;
1005 $line->montant = $obj->amount; // deprecated
1006 $line->amount = $obj->amount;
1007 $line->sens = $obj->sens;
1008 $line->multicurrency_amount = $obj->multicurrency_amount;
1009 $line->multicurrency_code = $obj->multicurrency_code;
1010 $line->lettering_code = $obj->lettering_code;
1011 $line->date_lettering = $obj->date_lettering;
1012 $line->fk_user_author = $obj->fk_user_author;
1013 $line->import_key = $obj->import_key;
1014 $line->code_journal = $obj->code_journal;
1015 $line->journal_label = $obj->journal_label;
1016 $line->piece_num = $obj->piece_num;
1017 $line->date_creation = $this->db->jdate($obj->date_creation);
1018 $line->date_export = $this->db->jdate($obj->date_export);
1019 $line->date_validation = $this->db->jdate($obj->date_validation);
1020 $line->import_key = $obj->import_key;
1021
1022 $this->lines[] = $line;
1023
1024 $i++;
1025 }
1026 }
1027 $this->db->free($resql);
1028
1029 return $num;
1030 } else {
1031 $this->errors[] = 'Error '.$this->db->lasterror();
1032 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1033
1034 return -1;
1035 }
1036 }
1037
1050 public function fetchAll($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, $filter = '', $filtermode = 'AND', $showAlreadyExportMovements = 1)
1051 {
1052 global $conf;
1053
1054 dol_syslog(__METHOD__, LOG_DEBUG);
1055
1056 $sql = 'SELECT';
1057 $sql .= ' t.rowid,';
1058 $sql .= " t.doc_date,";
1059 $sql .= " t.doc_type,";
1060 $sql .= " t.doc_ref,";
1061 $sql .= " t.fk_doc,";
1062 $sql .= " t.fk_docdet,";
1063 $sql .= " t.thirdparty_code,";
1064 $sql .= " t.subledger_account,";
1065 $sql .= " t.subledger_label,";
1066 $sql .= " t.numero_compte,";
1067 $sql .= " t.label_compte,";
1068 $sql .= " t.label_operation,";
1069 $sql .= " t.debit,";
1070 $sql .= " t.credit,";
1071 $sql .= " t.lettering_code,";
1072 $sql .= " t.date_lettering,";
1073 $sql .= " t.montant as amount,";
1074 $sql .= " t.sens,";
1075 $sql .= " t.fk_user_author,";
1076 $sql .= " t.import_key,";
1077 $sql .= " t.code_journal,";
1078 $sql .= " t.journal_label,";
1079 $sql .= " t.piece_num,";
1080 $sql .= " t.date_creation,";
1081 $sql .= " t.date_lim_reglement,";
1082 $sql .= " t.tms as date_modification,";
1083 $sql .= " t.date_export,";
1084 $sql .= " t.date_validated as date_validation";
1085 $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
1086
1087 $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1088 if ($showAlreadyExportMovements == 0) {
1089 $sql .= " AND t.date_export IS NULL";
1090 }
1091
1092 // Manage filter
1093 if (is_array($filter)) { // deprecated, use $filter = USF syntax
1094 dol_syslog("You are using a deprecated use of fetchAll. filter parameter mus be an USF string now.", LOG_WARNING);
1095 $sqlwhere = array();
1096 if (count($filter) > 0) {
1097 foreach ($filter as $key => $value) {
1098 if ($key == 't.doc_date') {
1099 $sqlwhere[] = $this->db->sanitize($key).' = \''.$this->db->idate($value).'\'';
1100 } elseif ($key == 't.doc_date>=') {
1101 $sqlwhere[] = "t.doc_date >= '".$this->db->idate($value)."'";
1102 } elseif ($key == 't.doc_date<=') {
1103 $sqlwhere[] = "t.doc_date <= '".$this->db->idate($value)."'";
1104 } elseif ($key == 't.doc_date>') {
1105 $sqlwhere[] = "t.doc_date > '".$this->db->idate($value)."'";
1106 } elseif ($key == 't.doc_date<') {
1107 $sqlwhere[] = "t.doc_date < '".$this->db->idate($value)."'";
1108 } elseif ($key == 't.numero_compte>=') {
1109 $sqlwhere[] = "t.numero_compte >= '".$this->db->escape($value)."'";
1110 } elseif ($key == 't.numero_compte<=') {
1111 $sqlwhere[] = "t.numero_compte <= '".$this->db->escape($value)."'";
1112 } elseif ($key == 't.subledger_account>=') {
1113 $sqlwhere[] = "t.subledger_account >= '".$this->db->escape($value)."'";
1114 } elseif ($key == 't.subledger_account<=') {
1115 $sqlwhere[] = "t.subledger_account <= '".$this->db->escape($value)."'";
1116 } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1117 $sqlwhere[] = $this->db->sanitize($key).' = '.((int) $value);
1118 } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1119 $sqlwhere[] = $this->db->sanitize($key).' LIKE \''.$this->db->escape($value).'%\'';
1120 } elseif ($key == 't.date_creation>=') {
1121 $sqlwhere[] = 't.date_creation >= \''.$this->db->idate($value).'\'';
1122 } elseif ($key == 't.date_creation<=') {
1123 $sqlwhere[] = 't.date_creation <= \''.$this->db->idate($value).'\'';
1124 } elseif ($key == 't.tms>=') {
1125 $sqlwhere[] = 't.tms >= \''.$this->db->idate($value).'\'';
1126 } elseif ($key == 't.tms<=') {
1127 $sqlwhere[] = 't.tms <= \''.$this->db->idate($value).'\'';
1128 } elseif ($key == 't.date_export>=') {
1129 $sqlwhere[] = 't.date_export >= \''.$this->db->idate($value).'\'';
1130 } elseif ($key == 't.date_export<=') {
1131 $sqlwhere[] = 't.date_export <= \''.$this->db->idate($value).'\'';
1132 } elseif ($key == 't.date_validated>=') {
1133 $sqlwhere[] = 't.date_validated >= \''.$this->db->idate($value).'\'';
1134 } elseif ($key == 't.date_validated<=') {
1135 $sqlwhere[] = 't.date_validated <= \''.$this->db->idate($value).'\'';
1136 } elseif ($key == 't.credit' || $key == 't.debit') {
1137 $sqlwhere[] = natural_search($key, $value, 1, 1);
1138 } elseif ($key == 't.code_journal' && !empty($value)) {
1139 if (is_array($value)) {
1140 $sqlwhere[] = natural_search("t.code_journal", implode(',', $value), 3, 1);
1141 } else {
1142 $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1143 }
1144 } else {
1145 $sqlwhere[] = natural_search($key, $value, 0, 1);
1146 }
1147 }
1148 }
1149 if (count($sqlwhere) > 0) {
1150 $sql .= ' AND '.implode(" ".$this->db->sanitize($filtermode)." ", $sqlwhere);
1151 }
1152
1153 $filter = '';
1154 }
1155
1156 // Manage filter
1157 $errormessage = '';
1158 $sql .= forgeSQLFromUniversalSearchCriteria($filter, $errormessage);
1159 if ($errormessage) {
1160 $this->errors[] = $errormessage;
1161 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1162 return -1;
1163 }
1164
1165 if (!empty($sortfield)) {
1166 $sql .= $this->db->order($sortfield, $sortorder);
1167 }
1168 if (!empty($limit)) {
1169 $sql .= $this->db->plimit($limit + 1, $offset);
1170 }
1171 $this->lines = array();
1172
1173 $resql = $this->db->query($sql);
1174 if ($resql) {
1175 $num = $this->db->num_rows($resql);
1176
1177 $i = 0;
1178 while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1179 $line = new BookKeepingLine($this->db);
1180
1181 $line->id = $obj->rowid;
1182
1183 $line->doc_date = $this->db->jdate($obj->doc_date);
1184 $line->doc_type = $obj->doc_type;
1185 $line->doc_ref = $obj->doc_ref;
1186 $line->fk_doc = $obj->fk_doc;
1187 $line->fk_docdet = $obj->fk_docdet;
1188 $line->thirdparty_code = $obj->thirdparty_code;
1189 $line->subledger_account = $obj->subledger_account;
1190 $line->subledger_label = $obj->subledger_label;
1191 $line->numero_compte = $obj->numero_compte;
1192 $line->label_compte = $obj->label_compte;
1193 $line->label_operation = $obj->label_operation;
1194 $line->debit = $obj->debit;
1195 $line->credit = $obj->credit;
1196 $line->montant = $obj->amount; // deprecated
1197 $line->amount = $obj->amount;
1198 $line->sens = $obj->sens;
1199 $line->lettering_code = $obj->lettering_code;
1200 $line->date_lettering = $obj->date_lettering;
1201 $line->fk_user_author = $obj->fk_user_author;
1202 $line->import_key = $obj->import_key;
1203 $line->code_journal = $obj->code_journal;
1204 $line->journal_label = $obj->journal_label;
1205 $line->piece_num = $obj->piece_num;
1206 $line->date_creation = $this->db->jdate($obj->date_creation);
1207 $line->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
1208 $line->date_modification = $this->db->jdate($obj->date_modification);
1209 $line->date_export = $this->db->jdate($obj->date_export);
1210 $line->date_validation = $this->db->jdate($obj->date_validation);
1211
1212 $this->lines[] = $line;
1213
1214 $i++;
1215 }
1216 $this->db->free($resql);
1217
1218 return $num;
1219 } else {
1220 $this->errors[] = 'Error '.$this->db->lasterror();
1221 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1222 return -1;
1223 }
1224 }
1225
1238 public function fetchAllBalance($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, $filter = '', $filtermode = 'AND', $option = 0)
1239 {
1240 global $conf;
1241
1242 $this->lines = array();
1243
1244 dol_syslog(__METHOD__, LOG_DEBUG);
1245
1246 $sql = 'SELECT';
1247 $sql .= " t.numero_compte,";
1248 if (!empty($option)) {
1249 $sql .= " t.subledger_account,";
1250 $sql .= " t.subledger_label,";
1251 }
1252 $sql .= " SUM(t.debit) as debit,";
1253 $sql .= " SUM(t.credit) as credit";
1254 $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
1255 $sql .= ' WHERE entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1256
1257 // Manage filter
1258 if (is_array($filter)) {
1259 $sqlwhere = array();
1260 if (count($filter) > 0) {
1261 foreach ($filter as $key => $value) {
1262 if ($key == 't.doc_date') {
1263 $sqlwhere[] = $this->db->sanitize($key)." = '".$this->db->idate($value)."'";
1264 } elseif ($key == 't.doc_date>=') {
1265 $sqlwhere[] = "t.doc_date >= '".$this->db->idate($value)."'";
1266 } elseif ($key == 't.doc_date<=') {
1267 $sqlwhere[] = "t.doc_date <= '".$this->db->idate($value)."'";
1268 } elseif ($key == 't.doc_date>') {
1269 $sqlwhere[] = "t.doc_date > '".$this->db->idate($value)."'";
1270 } elseif ($key == 't.doc_date<') {
1271 $sqlwhere[] = "t.doc_date < '".$this->db->idate($value)."'";
1272 } elseif ($key == 't.numero_compte>=') {
1273 $sqlwhere[] = "t.numero_compte >= '".$this->db->escape($value)."'";
1274 } elseif ($key == 't.numero_compte<=') {
1275 $sqlwhere[] = "t.numero_compte <= '".$this->db->escape($value)."'";
1276 } elseif ($key == 't.subledger_account>=') {
1277 $sqlwhere[] = "t.subledger_account >= '".$this->db->escape($value)."'";
1278 } elseif ($key == 't.subledger_account<=') {
1279 $sqlwhere[] = "t.subledger_account <= '".$this->db->escape($value)."'";
1280 } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1281 $sqlwhere[] = $this->db->sanitize($key)." = ".((int) $value);
1282 } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1283 $sqlwhere[] = $this->db->sanitize($key)." LIKE '".$this->db->escape($value)."%'";
1284 } elseif ($key == 't.subledger_label') {
1285 $sqlwhere[] = $this->db->sanitize($key)." LIKE '".$this->db->escape($value)."%'";
1286 } elseif ($key == 't.code_journal' && !empty($value)) {
1287 if (is_array($value)) {
1288 $sqlwhere[] = natural_search("t.code_journal", implode(',', $value), 3, 1);
1289 } else {
1290 $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1291 }
1292 } elseif ($key == 't.reconciled_option') {
1293 $sqlwhere[] = 't.lettering_code IS NULL';
1294 } else {
1295 $sqlwhere[] = $this->db->sanitize($key)." LIKE '%".$this->db->escape($this->db->escapeforlike($value))."%'";
1296 }
1297 }
1298 }
1299 if (count($sqlwhere) > 0) {
1300 $sql .= " AND ".implode(" ".$this->db->sanitize($filtermode)." ", $sqlwhere);
1301 }
1302
1303 $filter = '';
1304 }
1305
1306 // Manage filter
1307 $errormessage = '';
1308 $sql .= forgeSQLFromUniversalSearchCriteria($filter, $errormessage);
1309 if ($errormessage) {
1310 $this->errors[] = $errormessage;
1311 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1312 return -1;
1313 }
1314
1315 if (!empty($option)) {
1316 $sql .= " AND t.subledger_account IS NOT NULL";
1317 $sql .= " AND t.subledger_account <> ''";
1318 $sql .= " GROUP BY t.numero_compte, t.subledger_account, t.subledger_label";
1319 $sortfield = 't.subledger_account'.($sortfield ? ','.$sortfield : '');
1320 $sortorder = 'ASC'.($sortfield ? ','.$sortfield : '');
1321 } else {
1322 $sql .= ' GROUP BY t.numero_compte';
1323 $sortfield = 't.numero_compte'.($sortfield ? ','.$sortfield : '');
1324 $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
1325 }
1326
1327 if (!empty($sortfield)) {
1328 $sql .= $this->db->order($sortfield, $sortorder);
1329 }
1330 if (!empty($limit)) {
1331 $sql .= $this->db->plimit($limit + 1, $offset);
1332 }
1333
1334 //print $sql;
1335 $resql = $this->db->query($sql);
1336
1337 if ($resql) {
1338 $num = $this->db->num_rows($resql);
1339
1340 $i = 0;
1341 while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1342 $line = new BookKeepingLine($this->db);
1343
1344 $line->numero_compte = $obj->numero_compte;
1345 //$line->label_compte = $obj->label_compte;
1346 if (!empty($option)) {
1347 $line->subledger_account = $obj->subledger_account;
1348 $line->subledger_label = $obj->subledger_label;
1349 }
1350 $line->debit = $obj->debit;
1351 $line->credit = $obj->credit;
1352
1353 $this->lines[] = $line;
1354
1355 $i++;
1356 }
1357 $this->db->free($resql);
1358
1359 return $num;
1360 } else {
1361 $this->errors[] = 'Error '.$this->db->lasterror();
1362 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1363
1364 return -1;
1365 }
1366 }
1367
1376 public function update(User $user, $notrigger = 0, $mode = '')
1377 {
1378 global $langs;
1379 $error = 0;
1380
1381 dol_syslog(__METHOD__, LOG_DEBUG);
1382
1383 // Clean parameters
1384 if (isset($this->doc_type)) {
1385 $this->doc_type = trim($this->doc_type);
1386 }
1387 if (isset($this->doc_ref)) {
1388 $this->doc_ref = trim($this->doc_ref);
1389 }
1390 if (isset($this->fk_doc)) {
1391 $this->fk_doc = (int) $this->fk_doc;
1392 }
1393 if (isset($this->fk_docdet)) {
1394 $this->fk_docdet = (int) $this->fk_docdet;
1395 }
1396 if (isset($this->thirdparty_code)) {
1397 $this->thirdparty_code = trim($this->thirdparty_code);
1398 }
1399 if (isset($this->subledger_account)) {
1400 $this->subledger_account = trim($this->subledger_account);
1401 }
1402 if (isset($this->subledger_label)) {
1403 $this->subledger_label = trim($this->subledger_label);
1404 }
1405 if (isset($this->numero_compte)) {
1406 $this->numero_compte = trim($this->numero_compte);
1407 }
1408 if (isset($this->label_compte)) {
1409 $this->label_compte = trim($this->label_compte);
1410 }
1411 if (isset($this->label_operation)) {
1412 $this->label_operation = trim($this->label_operation);
1413 }
1414 if (isset($this->sens)) {
1415 $this->sens = trim($this->sens);
1416 }
1417 if (isset($this->import_key)) {
1418 $this->import_key = trim($this->import_key);
1419 }
1420 if (isset($this->code_journal)) {
1421 $this->code_journal = trim($this->code_journal);
1422 }
1423 if (isset($this->journal_label)) {
1424 $this->journal_label = trim($this->journal_label);
1425 }
1426 if (isset($this->piece_num)) {
1427 $this->piece_num = (int) $this->piece_num;
1428 }
1429
1430 $result = $this->canModifyBookkeeping($this->id, $mode);
1431 if ($result < 0) {
1432 return -1;
1433 } elseif ($result == 0) {
1434 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
1435 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod');
1436 } else {
1437 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod');
1438 }
1439 return -1;
1440 }
1441
1442 $this->debit = (float) price2num($this->debit, 'MT');
1443 $this->credit = (float) price2num($this->credit, 'MT');
1444 $this->montant = (float) price2num($this->montant, 'MT');
1445
1446 // Check parameters
1447 // Put here code to add a control on parameters values
1448
1449 // Update request
1450 $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element.$mode.' SET';
1451 $sql .= ' doc_date = '.(!isset($this->doc_date) || dol_strlen($this->doc_date) != 0 ? "'".$this->db->idate($this->doc_date)."'" : 'null').',';
1452 $sql .= ' doc_type = '.(isset($this->doc_type) ? "'".$this->db->escape($this->doc_type)."'" : "null").',';
1453 $sql .= ' doc_ref = '.(isset($this->doc_ref) ? "'".$this->db->escape($this->doc_ref)."'" : "null").',';
1454 $sql .= ' fk_doc = '.(isset($this->fk_doc) ? $this->fk_doc : "null").',';
1455 $sql .= ' fk_docdet = '.(isset($this->fk_docdet) ? $this->fk_docdet : "null").',';
1456 $sql .= ' thirdparty_code = '.(isset($this->thirdparty_code) ? "'".$this->db->escape($this->thirdparty_code)."'" : "null").',';
1457 $sql .= ' subledger_account = '.(isset($this->subledger_account) ? "'".$this->db->escape($this->subledger_account)."'" : "null").',';
1458 $sql .= ' subledger_label = '.(isset($this->subledger_label) ? "'".$this->db->escape($this->subledger_label)."'" : "null").',';
1459 $sql .= ' numero_compte = '.(isset($this->numero_compte) ? "'".$this->db->escape($this->numero_compte)."'" : "null").',';
1460 $sql .= ' label_compte = '.(isset($this->label_compte) ? "'".$this->db->escape($this->label_compte)."'" : "null").',';
1461 $sql .= ' label_operation = '.(isset($this->label_operation) ? "'".$this->db->escape($this->label_operation)."'" : "null").',';
1462 $sql .= ' debit = '.(isset($this->debit) ? $this->debit : "null").',';
1463 $sql .= ' credit = '.(isset($this->credit) ? $this->credit : "null").',';
1464 $sql .= ' montant = '.(isset($this->montant) ? $this->montant : "null").',';
1465 $sql .= ' sens = '.(isset($this->sens) ? "'".$this->db->escape($this->sens)."'" : "null").',';
1466 $sql .= ' fk_user_author = '.(isset($this->fk_user_author) ? $this->fk_user_author : "null").',';
1467 $sql .= ' import_key = '.(isset($this->import_key) ? "'".$this->db->escape($this->import_key)."'" : "null").',';
1468 $sql .= ' code_journal = '.(isset($this->code_journal) ? "'".$this->db->escape($this->code_journal)."'" : "null").',';
1469 $sql .= ' journal_label = '.(isset($this->journal_label) ? "'".$this->db->escape($this->journal_label)."'" : "null").',';
1470 $sql .= ' piece_num = '.(isset($this->piece_num) ? $this->piece_num : "null");
1471 $sql .= ' WHERE rowid='.((int) $this->id);
1472
1473 $this->db->begin();
1474
1475 $resql = $this->db->query($sql);
1476 if (!$resql) {
1477 $error++;
1478 $this->errors[] = 'Error '.$this->db->lasterror();
1479 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1480 }
1481
1482 // Uncomment this and change MYOBJECT to your own tag if you
1483 // want this action calls a trigger.
1484 //if (! $error && ! $notrigger) {
1485
1486 // // Call triggers
1487 // $result=$this->call_trigger('MYOBJECT_MODIFY',$user);
1488 // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1489 // // End call triggers
1490 //}
1491
1492 // Commit or rollback
1493 if ($error) {
1494 $this->db->rollback();
1495
1496 return -1 * $error;
1497 } else {
1498 $this->db->commit();
1499
1500 return 1;
1501 }
1502 }
1503
1513 public function updateByMvt($piece_num = '', $field = '', $value = '', $mode = '')
1514 {
1515 $error = 0;
1516
1517 $sql_filter = $this->getCanModifyBookkeepingSQL();
1518 if (!isset($sql_filter)) {
1519 return -1;
1520 }
1521
1522 $this->db->begin();
1523
1524 $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element.$mode;
1525 $sql .= " SET ".$this->db->sanitize($field)." = ".(is_numeric($value) ? ((float) $value) : "'".$this->db->escape($value)."'");
1526 $sql .= " WHERE piece_num = ".((int) $piece_num);
1527 $sql .= $sql_filter;
1528
1529 $resql = $this->db->query($sql);
1530
1531 if (!$resql) {
1532 $error++;
1533 $this->errors[] = 'Error '.$this->db->lasterror();
1534 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1535 }
1536 if ($error) {
1537 $this->db->rollback();
1538
1539 return -1 * $error;
1540 } else {
1541 $this->db->commit();
1542
1543 return 1;
1544 }
1545 }
1546
1555 public function delete(User $user, $notrigger = 0, $mode = '')
1556 {
1557 global $langs;
1558
1559 dol_syslog(__METHOD__, LOG_DEBUG);
1560
1561 $result = $this->canModifyBookkeeping($this->id, $mode);
1562 if ($result < 0) {
1563 return -1;
1564 } elseif ($result == 0) {
1565 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
1566 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod');
1567 } else {
1568 $this->errors[] = $langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod');
1569 }
1570 return -1;
1571 }
1572
1573 $error = 0;
1574
1575 $this->db->begin();
1576
1577 // Uncomment this and change MYOBJECT to your own tag if you
1578 // want this action calls a trigger.
1579 //if (! $error && ! $notrigger) {
1580
1581 // // Call triggers
1582 // $result=$this->call_trigger('MYOBJECT_DELETE',$user);
1583 // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1584 // // End call triggers
1585 //}
1586
1587 if (!$error) {
1588 $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.$mode;
1589 $sql .= ' WHERE rowid='.((int) $this->id);
1590
1591 $resql = $this->db->query($sql);
1592 if (!$resql) {
1593 $error++;
1594 $this->errors[] = 'Error '.$this->db->lasterror();
1595 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1596 }
1597 }
1598
1599 // Commit or rollback
1600 if ($error) {
1601 $this->db->rollback();
1602
1603 return -1 * $error;
1604 } else {
1605 $this->db->commit();
1606
1607 return 1;
1608 }
1609 }
1610
1618 public function deleteByImportkey($importkey, $mode = '')
1619 {
1620 $this->db->begin();
1621
1622 $sql_filter = $this->getCanModifyBookkeepingSQL();
1623 if (!isset($sql_filter)) {
1624 return -1;
1625 }
1626
1627 // first check if line not yet in bookkeeping
1628 $sql = "DELETE";
1629 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1630 $sql .= " WHERE import_key = '".$this->db->escape($importkey)."'";
1631 $sql .= $sql_filter;
1632
1633 $resql = $this->db->query($sql);
1634
1635 if (!$resql) {
1636 $this->errors[] = "Error ".$this->db->lasterror();
1637 dol_syslog(get_class($this)."::delete Error ".$this->db->lasterror(), LOG_ERR);
1638 $this->db->rollback();
1639 return -1;
1640 }
1641
1642 $this->db->commit();
1643 return 1;
1644 }
1645
1655 public function deleteByYearAndJournal($delyear = 0, $journal = '', $mode = '', $delmonth = 0)
1656 {
1657 global $conf, $langs;
1658
1659 if (empty($delyear) && empty($journal)) {
1660 $this->error = 'ErrorOneFieldRequired';
1661 return -1;
1662 }
1663 if (!empty($delmonth) && empty($delyear)) {
1664 $this->error = 'YearRequiredIfMonthDefined';
1665 return -2;
1666 }
1667
1668 $sql_filter = $this->getCanModifyBookkeepingSQL();
1669 if (!isset($sql_filter)) {
1670 return -1;
1671 }
1672
1673 $this->db->begin();
1674
1675 // Delete record in bookkeeping
1676 $sql = "DELETE";
1677 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1678 $sql .= " WHERE 1 = 1";
1679 $sql .= dolSqlDateFilter('doc_date', 0, $delmonth, $delyear);
1680 if (!empty($journal)) {
1681 $sql .= " AND code_journal = '".$this->db->escape($journal)."'";
1682 }
1683 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1684 // Exclusion of validated entries at the time of deletion
1685 $sql .= " AND date_validated IS NULL";
1686 $sql .= $sql_filter;
1687
1688 // TODO: In a future we must forbid deletion if record is inside a closed fiscal period.
1689
1690 $resql = $this->db->query($sql);
1691
1692 if (!$resql) {
1693 $this->errors[] = "Error ".$this->db->lasterror();
1694 foreach ($this->errors as $errmsg) {
1695 dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1696 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1697 }
1698 $this->db->rollback();
1699 return -1;
1700 }
1701
1702 $this->db->commit();
1703 return 1;
1704 }
1705
1713 public function deleteMvtNum($piecenum, $mode = '')
1714 {
1715 global $conf;
1716
1717 $sql_filter = $this->getCanModifyBookkeepingSQL();
1718 if (!isset($sql_filter)) {
1719 return -1;
1720 }
1721
1722 $nbprocessed = 0;
1723
1724 $this->db->begin();
1725
1726 // first check if line not yet in bookkeeping
1727 $sql = "DELETE";
1728 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1729 $sql .= " WHERE piece_num = ".(int) $piecenum;
1730 $sql .= " AND date_validated IS NULL"; // For security, exclusion of validated entries at the time of deletion
1731 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1732 $sql .= $sql_filter;
1733
1734 $resql = $this->db->query($sql);
1735
1736 if (!$resql) {
1737 $this->errors[] = "Error ".$this->db->lasterror();
1738 foreach ($this->errors as $errmsg) {
1739 dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1740 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1741 }
1742 $this->db->rollback();
1743 return -1;
1744 } else {
1745 $nbprocessed = $this->db->affected_rows($resql);
1746 }
1747
1748 $this->db->commit();
1749
1750 return $nbprocessed;
1751 }
1752
1760 public function createFromClone(User $user, $fromid)
1761 {
1762 dol_syslog(__METHOD__, LOG_DEBUG);
1763
1764 $error = 0;
1765 $object = new BookKeeping($this->db);
1766
1767 $this->db->begin();
1768
1769 // Load source object
1770 $object->fetch($fromid);
1771 // Reset object
1772 $object->id = 0;
1773
1774 // Clear fields
1775 // ...
1776
1777 // Create clone
1778 $object->context['createfromclone'] = 'createfromclone';
1779 $result = $object->create($user);
1780
1781 // Other options
1782 if ($result < 0) {
1783 $error++;
1784 $this->errors = $object->errors;
1785 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1786 }
1787
1788 unset($object->context['createfromclone']);
1789
1790 // End
1791 if (!$error) {
1792 $this->db->commit();
1793
1794 return $object->id;
1795 } else {
1796 $this->db->rollback();
1797
1798 return -1;
1799 }
1800 }
1801
1808 public function initAsSpecimen()
1809 {
1810 global $user;
1811
1812 $now = dol_now();
1813
1814 $this->id = 0;
1815 $this->doc_date = $now;
1816 $this->doc_type = '';
1817 $this->doc_ref = '';
1818 $this->fk_doc = 0;
1819 $this->fk_docdet = 0;
1820 $this->thirdparty_code = 'CU001';
1821 $this->subledger_account = '41100001';
1822 $this->subledger_label = 'My customer company';
1823 $this->numero_compte = '411';
1824 $this->label_compte = 'Customer';
1825 $this->label_operation = 'Sales of pea';
1826 $this->debit = 99.9;
1827 $this->credit = 0.0;
1828 $this->amount = 0.0;
1829 $this->sens = 'D';
1830 $this->fk_user_author = $user->id;
1831 $this->import_key = '20201027';
1832 $this->code_journal = 'VT';
1833 $this->journal_label = 'Journal de vente';
1834 $this->piece_num = 1234;
1835 $this->date_creation = $now;
1836
1837 return 1;
1838 }
1839
1847 public function fetchPerMvt($piecenum, $mode = '')
1848 {
1849 global $conf;
1850
1851 $sql = "SELECT piece_num, doc_date, code_journal, journal_label, doc_ref, doc_type,";
1852 $sql .= " date_creation, tms as date_modification, date_validated as date_validation, import_key";
1853 // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1854 if ($mode != "_tmp") {
1855 $sql .= ", date_export";
1856 }
1857 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1858 $sql .= " WHERE piece_num = ".((int) $piecenum);
1859 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1860
1861 dol_syslog(__METHOD__, LOG_DEBUG);
1862 $result = $this->db->query($sql);
1863 if ($result) {
1864 $obj = $this->db->fetch_object($result);
1865
1866 $this->piece_num = $obj->piece_num;
1867 $this->code_journal = $obj->code_journal;
1868 $this->journal_label = $obj->journal_label;
1869 $this->doc_date = $this->db->jdate($obj->doc_date);
1870 $this->doc_ref = $obj->doc_ref;
1871 $this->doc_type = $obj->doc_type;
1872 $this->date_creation = $this->db->jdate($obj->date_creation);
1873 $this->date_modification = $this->db->jdate($obj->date_modification);
1874 if ($mode != "_tmp") {
1875 $this->date_export = $this->db->jdate($obj->date_export);
1876 }
1877 $this->date_validation = $this->db->jdate($obj->date_validation);
1878 $this->import_key = $obj->import_key;
1879 } else {
1880 $this->error = "Error ".$this->db->lasterror();
1881 dol_syslog(__METHOD__.$this->error, LOG_ERR);
1882 return -1;
1883 }
1884
1885 return 1;
1886 }
1887
1894 public function getNextNumMvt($mode = '')
1895 {
1896 global $conf;
1897
1898 $sql = "SELECT MAX(piece_num)+1 as max FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1899 $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1900
1901 dol_syslog(get_class($this)."::getNextNumMvt", LOG_DEBUG);
1902
1903 $result = $this->db->query($sql);
1904
1905 if ($result) {
1906 $obj = $this->db->fetch_object($result);
1907 if ($obj) {
1908 $result = $obj->max;
1909 }
1910 if (empty($result)) {
1911 $result = 1;
1912 }
1913 return $result;
1914 } else {
1915 $this->error = "Error ".$this->db->lasterror();
1916 dol_syslog(get_class($this)."::getNextNumMvt ".$this->error, LOG_ERR);
1917 return -1;
1918 }
1919 }
1920
1928 public function fetchAllPerMvt($piecenum, $mode = '')
1929 {
1930 global $conf;
1931
1932 $sql = "SELECT rowid, doc_date, doc_type,";
1933 $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1934 $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1935 $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, journal_label, piece_num,";
1936 $sql .= " date_creation, tms as date_modification, date_validated as date_validation";
1937 // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1938 if ($mode != "_tmp") {
1939 $sql .= ", date_export";
1940 }
1941 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1942 $sql .= " WHERE piece_num = ".((int) $piecenum);
1943 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1944
1945 dol_syslog(__METHOD__, LOG_DEBUG);
1946 $result = $this->db->query($sql);
1947 if ($result) {
1948 while ($obj = $this->db->fetch_object($result)) {
1949 $line = new BookKeepingLine($this->db);
1950
1951 $line->id = $obj->rowid;
1952
1953 $line->doc_date = $this->db->jdate($obj->doc_date);
1954 $line->doc_type = $obj->doc_type;
1955 $line->doc_ref = $obj->doc_ref;
1956 $line->fk_doc = $obj->fk_doc;
1957 $line->fk_docdet = $obj->fk_docdet;
1958 $line->thirdparty_code = $obj->thirdparty_code;
1959 $line->subledger_account = $obj->subledger_account;
1960 $line->subledger_label = $obj->subledger_label;
1961 $line->numero_compte = $obj->numero_compte;
1962 $line->label_compte = $obj->label_compte;
1963 $line->label_operation = $obj->label_operation;
1964 $line->debit = $obj->debit;
1965 $line->credit = $obj->credit;
1966 $line->montant = $obj->amount;
1967 $line->amount = $obj->amount;
1968 $line->sens = $obj->sens;
1969 $line->code_journal = $obj->code_journal;
1970 $line->journal_label = $obj->journal_label;
1971 $line->piece_num = $obj->piece_num;
1972 $line->date_creation = $obj->date_creation;
1973 $line->date_modification = $obj->date_modification;
1974 if ($mode != "_tmp") {
1975 $line->date_export = $obj->date_export;
1976 }
1977 $line->date_validation = $obj->date_validation;
1978
1979 $this->linesmvt[] = $line;
1980 }
1981 } else {
1982 $this->error = "Error ".$this->db->lasterror();
1983 dol_syslog(__METHOD__.$this->error, LOG_ERR);
1984 return -1;
1985 }
1986
1987 return 1;
1988 }
1989
1990 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1997 public function export_bookkeeping($model = 'ebp')
1998 {
1999 // phpcs:enable
2000 global $conf;
2001
2002 $sql = "SELECT rowid, doc_date, doc_type,";
2003 $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
2004 $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
2005 $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, piece_num,";
2006 $sql .= " date_validated as date_validation";
2007 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
2008 $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2009
2010 dol_syslog(get_class($this)."::export_bookkeeping", LOG_DEBUG);
2011
2012 $resql = $this->db->query($sql);
2013
2014 if ($resql) {
2015 $this->linesexport = array();
2016
2017 $num = $this->db->num_rows($resql);
2018 while ($obj = $this->db->fetch_object($resql)) {
2019 $line = new BookKeepingLine($this->db);
2020
2021 $line->id = $obj->rowid;
2022
2023 $line->doc_date = $this->db->jdate($obj->doc_date);
2024 $line->doc_type = $obj->doc_type;
2025 $line->doc_ref = $obj->doc_ref;
2026 $line->fk_doc = $obj->fk_doc;
2027 $line->fk_docdet = $obj->fk_docdet;
2028 $line->thirdparty_code = $obj->thirdparty_code;
2029 $line->subledger_account = $obj->subledger_account;
2030 $line->subledger_label = $obj->subledger_label;
2031 $line->numero_compte = $obj->numero_compte;
2032 $line->label_compte = $obj->label_compte;
2033 $line->label_operation = $obj->label_operation;
2034 $line->debit = $obj->debit;
2035 $line->credit = $obj->credit;
2036 $line->montant = $obj->amount;
2037 $line->amount = $obj->amount;
2038 $line->sens = $obj->sens;
2039 $line->code_journal = $obj->code_journal;
2040 $line->piece_num = $obj->piece_num;
2041 $line->date_validation = $obj->date_validation;
2042
2043 $this->linesexport[] = $line;
2044 }
2045 $this->db->free($resql);
2046
2047 return $num;
2048 } else {
2049 $this->error = "Error ".$this->db->lasterror();
2050 dol_syslog(get_class($this)."::export_bookkeeping ".$this->error, LOG_ERR);
2051 return -1;
2052 }
2053 }
2054
2062 public function transformTransaction($direction = 0, $piece_num = '')
2063 {
2064 global $conf;
2065
2066 $error = 0;
2067
2068 $sql_filter = $this->getCanModifyBookkeepingSQL();
2069
2070 if (!isset($sql_filter)) {
2071 return -1;
2072 }
2073
2074 $this->db->begin();
2075
2076 if ($direction == 0) {
2077 $next_piecenum = $this->getNextNumMvt();
2078 $now = dol_now();
2079
2080 if ($next_piecenum < 0) {
2081 $error++;
2082 }
2083
2084 if (!$error) {
2085 // Delete if there is an empty line
2086 $sql = 'DELETE FROM '.MAIN_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";
2087 $resql = $this->db->query($sql);
2088 if (!$resql) {
2089 $error++;
2090 $this->errors[] = 'Error '.$this->db->lasterror();
2091 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2092 }
2093 }
2094
2095 if (!$error) {
2096 $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.' (doc_date, doc_type,';
2097 $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
2098 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2099 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num, date_creation)';
2100 $sql .= ' SELECT doc_date, doc_type,';
2101 $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
2102 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2103 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, '.((int) $next_piecenum).", '".$this->db->idate($now)."'";
2104 $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND numero_compte IS NOT NULL AND entity = ' .((int) $conf->entity);
2105 $sql .= $sql_filter;
2106 $resql = $this->db->query($sql);
2107 if (!$resql) {
2108 $error++;
2109 $this->errors[] = 'Error '.$this->db->lasterror();
2110 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2111 }
2112 }
2113
2114 if (!$error) {
2115 $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2116 $resql = $this->db->query($sql);
2117 if (!$resql) {
2118 $error++;
2119 $this->errors[] = 'Error '.$this->db->lasterror();
2120 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2121 }
2122 }
2123 } elseif ($direction == 1) {
2124 if (!$error) {
2125 $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2126 $resql = $this->db->query($sql);
2127 if (!$resql) {
2128 $error++;
2129 $this->errors[] = 'Error '.$this->db->lasterror();
2130 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2131 }
2132 }
2133
2134 if (!$error) {
2135 $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.'_tmp (doc_date, doc_type,';
2136 $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
2137 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2138 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num)';
2139 $sql .= ' SELECT doc_date, doc_type,';
2140 $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
2141 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2142 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num';
2143 $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2144 $sql .= $sql_filter;
2145 $resql = $this->db->query($sql);
2146 if (!$resql) {
2147 $error++;
2148 $this->errors[] = 'Error '.$this->db->lasterror();
2149 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2150 }
2151 }
2152
2153 if (!$error) {
2154 $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2155 $sql .= $sql_filter;
2156 $resql = $this->db->query($sql);
2157 if (!$resql) {
2158 $error++;
2159 $this->errors[] = 'Error '.$this->db->lasterror();
2160 dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2161 }
2162 }
2163 }
2164 if (!$error) {
2165 $this->db->commit();
2166 return 1;
2167 } else {
2168 $this->db->rollback();
2169 return -1;
2170 }
2171 /*
2172 $sql = "DELETE FROM ";
2173 $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as ab";
2174 $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as aa ON aa.account_number = ab.numero_compte";
2175 $sql .= " AND aa.active = 1";
2176 $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2177 $sql .= " AND asy.rowid = " . ((int) $pcgver);
2178 $sql .= " AND ab.entity IN (" . getEntity('accountancy') . ")";
2179 $sql .= " ORDER BY account_number ASC";
2180 */
2181 }
2182
2183 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2196 public function select_account($selectid, $htmlname = 'account', $showempty = 0, $event = array(), $select_in = 0, $select_out = 0, $aabase = '')
2197 {
2198 // phpcs:enable
2199 global $conf;
2200
2201 require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
2202
2203 $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2204
2205 $sql = "SELECT DISTINCT ab.numero_compte as account_number, aa.label as label, aa.rowid as rowid, aa.fk_pcg_version";
2206 $sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping as ab";
2207 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as aa ON aa.account_number = ab.numero_compte";
2208 $sql .= " AND aa.active = 1";
2209 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2210 $sql .= " AND asy.rowid = ".((int) $pcgver);
2211 $sql .= " AND ab.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2212 $sql .= " ORDER BY account_number ASC";
2213
2214 dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2215 $resql = $this->db->query($sql);
2216
2217 if (!$resql) {
2218 $this->error = "Error ".$this->db->lasterror();
2219 dol_syslog(get_class($this)."::select_account ".$this->error, LOG_ERR);
2220 return "-1";
2221 }
2222
2223 $out = ajax_combobox($htmlname, $event);
2224
2225 $options = array();
2226 $selected = null;
2227
2228 while ($obj = $this->db->fetch_object($resql)) {
2229 $label = length_accountg($obj->account_number).' - '.$obj->label;
2230
2231 $select_value_in = $obj->rowid;
2232 $select_value_out = $obj->rowid;
2233
2234 if ($select_in == 1) {
2235 $select_value_in = $obj->account_number;
2236 }
2237 if ($select_out == 1) {
2238 $select_value_out = $obj->account_number;
2239 }
2240
2241 // Remember guy's we store in database llx_facturedet the rowid of accounting_account and not the account_number
2242 // Because same account_number can be share between different accounting_system and do have the same meaning
2243 if (($selectid != '') && $selectid == $select_value_in) {
2244 $selected = $select_value_out;
2245 }
2246
2247 $options[$select_value_out] = $label;
2248 }
2249
2250 $out .= Form::selectarray($htmlname, $options, $selected, $showempty, 0, 0, '', 0, 0, 0, '', 'maxwidth300');
2251 $this->db->free($resql);
2252 return $out;
2253 }
2254
2262 public function getRootAccount($account = null)
2263 {
2264 global $conf;
2265 $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2266
2267 $sql = "SELECT root.rowid, root.account_number, root.label as label,";
2268 $sql .= " parent.rowid as parent_rowid, parent.account_number as parent_account_number, parent.label as parent_label";
2269 $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa";
2270 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2271 $sql .= " AND asy.rowid = ".((int) $pcgver);
2272 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as parent ON aa.account_parent = parent.rowid AND parent.active = 1";
2273 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as root ON parent.account_parent = root.rowid AND root.active = 1";
2274 $sql .= " WHERE aa.account_number = '".$this->db->escape($account)."'";
2275 $sql .= " AND aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2276
2277 dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2278 $resql = $this->db->query($sql);
2279 if ($resql) {
2280 $obj = '';
2281 if ($this->db->num_rows($resql)) {
2282 $obj = $this->db->fetch_object($resql);
2283 }
2284
2285 $result = array('id' => $obj->rowid, 'account_number' => $obj->account_number, 'label' => $obj->label);
2286 return $result;
2287 } else {
2288 $this->error = "Error ".$this->db->lasterror();
2289 dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2290
2291 return -1;
2292 }
2293 }
2294
2295 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2302 public function get_compte_desc($account = null)
2303 {
2304 // phpcs:enable
2305 global $conf;
2306
2307 $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2308 $sql = "SELECT aa.account_number, aa.label, aa.rowid, aa.fk_pcg_version, cat.label as category";
2309 $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa ";
2310 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2311 $sql .= " AND aa.account_number = '".$this->db->escape($account)."'";
2312 $sql .= " AND asy.rowid = ".((int) $pcgver);
2313 $sql .= " AND aa.active = 1";
2314 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_accounting_category as cat ON aa.fk_accounting_category = cat.rowid";
2315 $sql .= " WHERE aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2316
2317 dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2318 $resql = $this->db->query($sql);
2319 if ($resql) {
2320 $obj = '';
2321 if ($this->db->num_rows($resql)) {
2322 $obj = $this->db->fetch_object($resql);
2323 }
2324 if (empty($obj->category)) {
2325 return $obj->label;
2326 } else {
2327 return $obj->label.' ('.$obj->category.')';
2328 }
2329 } else {
2330 $this->error = "Error ".$this->db->lasterror();
2331 dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2332 return "-1";
2333 }
2334 }
2335
2343 public function getCanModifyBookkeepingSQL($alias = '', $force = false)
2344 {
2345 global $conf;
2346
2347 $alias = trim($alias);
2348 $alias = !empty($alias) && strpos($alias, '.') < 0 ? $alias . "." : $alias;
2349
2350 if (!isset(self::$can_modify_bookkeeping_sql_cached[$alias]) || $force) {
2351 $result = $this->loadFiscalPeriods($force, 'active');
2352 if ($result < 0) {
2353 return null;
2354 }
2355
2356 $sql_list = array();
2357 if (!empty($conf->cache['active_fiscal_period_cached']) && is_array($conf->cache['active_fiscal_period_cached'])) {
2358 $i = 0;
2359 foreach ($conf->cache['active_fiscal_period_cached'] as $fiscal_period) {
2360 $sql_list[$i] = "(";
2361 $sql_list[$i] .= "'".$this->db->idate($fiscal_period['date_start']) . "' <= ".$this->db->sanitize($alias)."doc_date";
2362 if (!empty($fiscal_period['date_end'])) {
2363 $sql_list[$i] .= " AND ";
2364 $sql_list[$i] .= $this->db->sanitize($alias)."doc_date <= '" . $this->db->idate($fiscal_period['date_end'])."'";
2365 }
2366 $sql_list[$i] .= ")";
2367 $i++;
2368 }
2369 }
2370 $sqlsanitized = implode(' OR ', $sql_list);
2371 self::$can_modify_bookkeeping_sql_cached[$alias] = empty($sql_list) ? "" : " AND (".$sqlsanitized.")";
2372 }
2373
2374 return self::$can_modify_bookkeeping_sql_cached[$alias];
2375 }
2376
2384 public function canModifyBookkeeping($id, $mode = '')
2385 {
2386 global $conf;
2387
2388 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
2389 $result = $this->loadFiscalPeriods(false, 'closed');
2390
2391 if ($result < 0) {
2392 return -1;
2393 }
2394
2395 $bookkeeping = new BookKeeping($this->db);
2396 $result = $bookkeeping->fetch($id, null, $mode);
2397 if ($result <= 0) {
2398 return $result;
2399 }
2400
2401 if (!empty($conf->cache['closed_fiscal_period_cached']) && is_array($conf->cache['closed_fiscal_period_cached'])) {
2402 foreach ($conf->cache['closed_fiscal_period_cached'] as $fiscal_period) {
2403 if ($fiscal_period['date_start'] <= $bookkeeping->doc_date && $bookkeeping->doc_date <= $fiscal_period['date_end']) {
2404 return 0;
2405 }
2406 }
2407 }
2408
2409 return 1;
2410 } else {
2411 $result = $this->loadFiscalPeriods(false, 'active');
2412 if ($result < 0) {
2413 return -1;
2414 }
2415
2416 $bookkeeping = new BookKeeping($this->db);
2417 $result = $bookkeeping->fetch($id, null, $mode);
2418
2419 if ($result <= 0) {
2420 return $result;
2421 }
2422 if (!empty($conf->cache['active_fiscal_period_cached']) && is_array($conf->cache['active_fiscal_period_cached'])) {
2423 foreach ($conf->cache['active_fiscal_period_cached'] as $fiscal_period) {
2424 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'])) {
2425 return 1;
2426 }
2427 }
2428 }
2429
2430 return 0;
2431 }
2432 }
2433
2440 public function validBookkeepingDate($date)
2441 {
2442 global $conf;
2443
2444 if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
2445 $result = $this->loadFiscalPeriods(false, 'closed');
2446
2447 if ($result < 0) {
2448 return -1;
2449 }
2450
2451 if (!empty($conf->cache['closed_fiscal_period_cached']) && is_array($conf->cache['closed_fiscal_period_cached'])) {
2452 foreach ($conf->cache['closed_fiscal_period_cached'] as $fiscal_period) {
2453 if ($fiscal_period['date_start'] <= $date && $date <= $fiscal_period['date_end']) {
2454 return 0;
2455 }
2456 }
2457 }
2458
2459 return 1;
2460 } else {
2461 $result = $this->loadFiscalPeriods(false, 'active');
2462 if ($result < 0) {
2463 return -1;
2464 }
2465
2466 if (!empty($conf->cache['active_fiscal_period_cached']) && is_array($conf->cache['active_fiscal_period_cached'])) {
2467 foreach ($conf->cache['active_fiscal_period_cached'] as $fiscal_period) {
2468 if (!empty($fiscal_period['date_start']) && $fiscal_period['date_start'] <= $date && (empty($fiscal_period['date_end']) || $date <= $fiscal_period['date_end'])) {
2469 return 1;
2470 }
2471 }
2472 }
2473
2474 return 0;
2475 }
2476 }
2477
2485 public function loadFiscalPeriods($force = false, $mode = 'active')
2486 {
2487 global $conf;
2488
2489 if ($mode == 'active') {
2490 if (!isset($conf->cache['active_fiscal_period_cached']) || $force) {
2491 $sql = "SELECT date_start, date_end";
2492 $sql .= " FROM " . $this->db->prefix() . "accounting_fiscalyear";
2493 $sql .= " WHERE entity = " . ((int) $conf->entity);
2494 $sql .= " AND statut = 0";
2495
2496 $resql = $this->db->query($sql);
2497 if (!$resql) {
2498 $this->errors[] = $this->db->lasterror();
2499 return -1;
2500 }
2501
2502 $list = array();
2503 while ($obj = $this->db->fetch_object($resql)) {
2504 $list[] = array(
2505 'date_start' => $this->db->jdate($obj->date_start),
2506 'date_end' => $this->db->jdate($obj->date_end),
2507 );
2508 }
2509 $conf->cache['active_fiscal_period_cached'] = $list;
2510 }
2511 }
2512 if ($mode == 'closed') {
2513 if (!isset($conf->cache['closed_fiscal_period_cached']) || $force) {
2514 $sql = "SELECT date_start, date_end";
2515 $sql .= " FROM " . $this->db->prefix() . "accounting_fiscalyear";
2516 $sql .= " WHERE entity = " . ((int) $conf->entity);
2517 $sql .= " AND statut = 1";
2518
2519 $resql = $this->db->query($sql);
2520 if (!$resql) {
2521 $this->errors[] = $this->db->lasterror();
2522 return -1;
2523 }
2524
2525 $list = array();
2526 while ($obj = $this->db->fetch_object($resql)) {
2527 $list[] = array(
2528 'date_start' => $this->db->jdate($obj->date_start),
2529 'date_end' => $this->db->jdate($obj->date_end),
2530 );
2531 }
2532 $conf->cache['closed_fiscal_period_cached'] = $list;
2533 }
2534 }
2535
2536 return 1;
2537 }
2538
2545 public function getFiscalPeriods($filter = '')
2546 {
2547 global $conf;
2548 $list = array();
2549
2550 $sql = "SELECT rowid, label, date_start, date_end, statut";
2551 $sql .= " FROM " . $this->db->prefix() . "accounting_fiscalyear";
2552 $sql .= " WHERE entity = " . ((int) $conf->entity);
2553 if (!empty($filter)) {
2554 $sql .= " AND (" . $this->db->sanitize($filter, 1, 1, 1) . ')';
2555 }
2556 $sql .= $this->db->order('date_start', 'ASC');
2557
2558 $resql = $this->db->query($sql);
2559 if (!$resql) {
2560 $this->errors[] = $this->db->lasterror();
2561 return -1;
2562 }
2563
2564 while ($obj = $this->db->fetch_object($resql)) {
2565 $list[$obj->rowid] = array(
2566 'id' => (int) $obj->rowid,
2567 'label' => $obj->label,
2568 'date_start' => $this->db->jdate($obj->date_start),
2569 'date_end' => $this->db->jdate($obj->date_end),
2570 'status' => (int) $obj->statut,
2571 );
2572 }
2573
2574 return $list;
2575 }
2576
2584 public function getCountByMonthForFiscalPeriod($date_start, $date_end)
2585 {
2586 $total = 0;
2587 $list = array();
2588
2589 $sql = "SELECT YEAR(b.doc_date) as year";
2590 for ($i = 1; $i <= 12; $i++) {
2591 $sql .= ", SUM(".$this->db->ifsql("MONTH(b.doc_date) = ".((int) $i), "1", "0") . ") AS month".((int) $i);
2592 }
2593 $sql .= ", COUNT(b.rowid) as total";
2594 $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as b";
2595 $sql .= " WHERE b.doc_date >= '" . $this->db->idate($date_start) . "'";
2596 $sql .= " AND b.doc_date <= '" . $this->db->idate($date_end) . "'";
2597 $sql .= " AND b.entity IN (" . getEntity('bookkeeping', 0) . ")"; // We don't share object for accountancy
2598
2599 // Get count for each month into the fiscal period
2600 if (getDolGlobalString("ACCOUNTANCY_DISABLE_CLOSURE_LINE_BY_LINE")) {
2601 // TODO Analyse is done by finding record not into a closed period
2602 // Loop on each closed period
2603 $sql .= " AND b.doc_date BETWEEN 0 AND 0";
2604 } else {
2605 // Analyse closed record using the unitary flag/date on each record
2606 $sql .= " AND date_validated IS NULL";
2607 }
2608
2609 $sql .= " GROUP BY YEAR(b.doc_date)";
2610 $sql .= $this->db->order("year", 'ASC');
2611
2612 dol_syslog(__METHOD__, LOG_DEBUG);
2613 $resql = $this->db->query($sql);
2614 if (!$resql) {
2615 $this->errors[] = $this->db->lasterror();
2616 return -1;
2617 }
2618
2619 while ($obj = $this->db->fetch_object($resql)) {
2620 $total += (int) $obj->total;
2621 $year_list = array(
2622 'year' => (int) $obj->year,
2623 'count' => array(),
2624 'total' => (int) $obj->total,
2625 );
2626 for ($i = 1; $i <= 12; $i++) {
2627 $year_list['count'][$i] = (int) $obj->{'month' . $i};
2628 }
2629
2630 $list[] = $year_list;
2631 }
2632
2633 $this->db->free($resql);
2634
2635 return array(
2636 'total' => $total,
2637 'list' => $list,
2638 );
2639 }
2640
2648 public function validateMovementForFiscalPeriod($date_start, $date_end)
2649 {
2650 global $conf;
2651
2652 $now = dol_now();
2653
2654 // Specify as export : update field date_validated on selected month/year
2655 $sql = " UPDATE " . MAIN_DB_PREFIX . "accounting_bookkeeping";
2656 $sql .= " SET date_validated = '" . $this->db->idate($now) . "'";
2657 $sql .= " WHERE entity = " . ((int) $conf->entity);
2658 $sql .= " AND DATE(doc_date) >= '" . $this->db->idate($date_start) . "'";
2659 $sql .= " AND DATE(doc_date) <= '" . $this->db->idate($date_end) . "'";
2660 $sql .= " AND date_validated IS NULL";
2661
2662 dol_syslog(__METHOD__, LOG_DEBUG);
2663 $resql = $this->db->query($sql);
2664 if (!$resql) {
2665 $this->errors[] = $this->db->lasterror();
2666 return -1;
2667 }
2668
2669 return 1;
2670 }
2671
2679 public function accountingResult($date_start, $date_end)
2680 {
2681 global $conf;
2682
2683 $this->db->begin();
2684
2685 $income_statement_amount = 0;
2686
2687 if (getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_INCOME_STATEMENT')) {
2688 $accounting_groups_used_for_income_statement = array_filter(array_map('trim', explode(',', getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_INCOME_STATEMENT'))), 'strlen');
2689
2690 $pcg_type_filter = array();
2691 foreach ($accounting_groups_used_for_income_statement as $item) {
2692 $pcg_type_filter[] = "'" . $this->db->escape($item) . "'";
2693 }
2694
2695 $sql = 'SELECT';
2696 $sql .= " t.numero_compte,";
2697 $sql .= " aa.pcg_type,";
2698 $sql .= " (SUM(t.credit) - SUM(t.debit)) as accounting_result";
2699 $sql .= ' FROM ' . MAIN_DB_PREFIX . $this->table_element . ' as t';
2700 $sql .= ' LEFT JOIN ' . MAIN_DB_PREFIX . 'accounting_account as aa ON aa.account_number = t.numero_compte';
2701 $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
2702 $sql .= " AND aa.entity = " . ((int) $conf->entity);
2703 $sql .= ' AND aa.fk_pcg_version IN (SELECT pcg_version FROM ' . MAIN_DB_PREFIX . 'accounting_system WHERE rowid = ' . ((int) getDolGlobalInt('CHARTOFACCOUNTS')) . ')';
2704 $sql .= ' AND aa.pcg_type IN (' . $this->db->sanitize(implode(',', $pcg_type_filter), 1) . ')';
2705 $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($date_start) . "'";
2706 $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($date_end) . "'";
2707 $sql .= ' GROUP BY t.numero_compte, aa.pcg_type';
2708
2709 $resql = $this->db->query($sql);
2710 if (!$resql) {
2711 $this->errors[] = 'Error ' . $this->db->lasterror();
2712 dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
2713 } else {
2714 while ($obj = $this->db->fetch_object($resql)) {
2715 $income_statement_amount += $obj->accounting_result;
2716 }
2717 }
2718 }
2719
2720 return (string) $income_statement_amount;
2721 }
2722
2732 public function closeFiscalPeriod($fiscal_period_id, $new_fiscal_period_id, $separate_auxiliary_account = false, $generate_bookkeeping_records = true)
2733 {
2734 global $conf, $langs, $user;
2735
2736 // Current fiscal period
2737 $fiscal_period_id = max(0, $fiscal_period_id);
2738 if (empty($fiscal_period_id)) {
2739 $langs->load('errors');
2740 $this->errors[] = $langs->trans('ErrorBadParameters');
2741 return -1;
2742 }
2743 $fiscal_period = new Fiscalyear($this->db);
2744 $result = $fiscal_period->fetch($fiscal_period_id);
2745 if ($result < 0) {
2746 $this->error = $fiscal_period->error;
2747 $this->errors = $fiscal_period->errors;
2748 return -1;
2749 } elseif (empty($fiscal_period->id)) {
2750 $langs->loadLangs(array('errors', 'compta'));
2751 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $fiscal_period_id . ')';
2752 return -1;
2753 }
2754
2755 // New fiscal period
2756 $new_fiscal_period_id = max(0, $new_fiscal_period_id);
2757 if (empty($new_fiscal_period_id)) {
2758 $langs->load('errors');
2759 $this->errors[] = $langs->trans('ErrorBadParameters');
2760 return -1;
2761 }
2762 $new_fiscal_period = new Fiscalyear($this->db);
2763 $result = $new_fiscal_period->fetch($new_fiscal_period_id);
2764 if ($result < 0) {
2765 $this->error = $new_fiscal_period->error;
2766 $this->errors = $new_fiscal_period->errors;
2767 return -1;
2768 } elseif (empty($new_fiscal_period->id)) {
2769 $langs->loadLangs(array('errors', 'compta'));
2770 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $new_fiscal_period_id . ')';
2771 return -1;
2772 }
2773
2774 $error = 0;
2775 $this->db->begin();
2776
2777 $fiscal_period->statut = Fiscalyear::STATUS_CLOSED;
2778 $fiscal_period->status = Fiscalyear::STATUS_CLOSED; // Actually not used
2779 $result = $fiscal_period->update($user);
2780 if ($result < 0) {
2781 $this->error = $fiscal_period->error;
2782 $this->errors = $fiscal_period->errors;
2783 $error++;
2784 }
2785
2786 if (!$error && !empty($generate_bookkeeping_records)) {
2787 $journal_id = max(0, getDolGlobalString('ACCOUNTING_CLOSURE_DEFAULT_JOURNAL'));
2788 if (empty($journal_id)) {
2789 $langs->loadLangs(array('errors', 'accountancy'));
2790 $this->errors[] = $langs->trans('ErrorBadParameters') . ' - ' . $langs->trans('Codejournal') . ' (' . $langs->trans('AccountingJournalType9') . ')';
2791 $error++;
2792 }
2793
2794 // Fetch journal
2795 if (!$error) {
2796 $journal = new AccountingJournal($this->db);
2797 $result = $journal->fetch($journal_id);
2798 if ($result < 0) {
2799 $this->error = $journal->error;
2800 $this->errors = $journal->errors;
2801 $error++;
2802 } elseif ($result == 0) {
2803 $langs->loadLangs(array('errors', 'accountancy'));
2804 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('Codejournal') . ' (' . $langs->trans('AccountingJournalType9') . ')';
2805 $error++;
2806 }
2807 }
2808
2809 if (!$error) {
2810 $accounting_groups_used_for_balance_sheet_account = array_filter(array_map('trim', explode(',', getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_BALANCE_SHEET_ACCOUNT'))), 'strlen');
2811 $accounting_groups_used_for_income_statement = array_filter(array_map('trim', explode(',', getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_INCOME_STATEMENT'))), 'strlen');
2812
2813 $pcg_type_filter = array();
2814 $tmp = array_merge($accounting_groups_used_for_balance_sheet_account, $accounting_groups_used_for_income_statement);
2815 foreach ($tmp as $item) {
2816 $pcg_type_filter[] = "'" . $this->db->escape($item) . "'";
2817 }
2818
2819 $sql = 'SELECT';
2820 $sql .= " t.numero_compte,";
2821 if ($separate_auxiliary_account) {
2822 $sql .= " NULLIF(t.subledger_account, '') as subledger_account,"; // fix db issues with Null or "" values
2823 }
2824 $sql .= " aa.pcg_type,";
2825 $sql .= " (SUM(t.credit) - SUM(t.debit)) as opening_balance";
2826 $sql .= ' FROM ' . MAIN_DB_PREFIX . $this->table_element . ' as t';
2827 $sql .= ' LEFT JOIN ' . MAIN_DB_PREFIX . 'accounting_account as aa ON aa.account_number = t.numero_compte';
2828 $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
2829 $sql .= " AND aa.entity = ". ((int) $conf->entity);
2830 $sql .= ' AND aa.fk_pcg_version IN (SELECT pcg_version FROM '.MAIN_DB_PREFIX.'accounting_system WHERE rowid = '.((int) getDolGlobalInt('CHARTOFACCOUNTS')).')';
2831 $sql .= ' AND aa.pcg_type IN (' . $this->db->sanitize(implode(',', $pcg_type_filter), 1) . ')';
2832 $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($fiscal_period->date_start) . "'";
2833 $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($fiscal_period->date_end) . "'";
2834 $sql .= ' GROUP BY t.numero_compte, aa.pcg_type';
2835 if ($separate_auxiliary_account) {
2836 $sql .= " , NULLIF(t.subledger_account, '')";
2837 }
2838 $sql .= ' HAVING (SUM(t.credit) - SUM(t.debit)) != 0 '; // Exclude rows with opening_balance = 0
2839 $sql .= $this->db->order("t.numero_compte", "ASC");
2840
2841 $resql = $this->db->query($sql);
2842 if (!$resql) {
2843 $this->errors[] = 'Error ' . $this->db->lasterror();
2844 dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
2845
2846 $error++;
2847 } else {
2848 $now = dol_now();
2849 $income_statement_amount = 0;
2850 while ($obj = $this->db->fetch_object($resql)) {
2851 if (in_array($obj->pcg_type, $accounting_groups_used_for_income_statement)) {
2852 $income_statement_amount += $obj->opening_balance;
2853 } else {
2854 // Insert bookkeeping record for balance sheet account
2855 $mt = $obj->opening_balance;
2856
2857 $bookkeeping = new BookKeeping($this->db);
2858 $bookkeeping->doc_date = $new_fiscal_period->date_start;
2859
2860 $bookkeeping->date_lim_reglement = '';
2861 $bookkeeping->doc_ref = $fiscal_period->label;
2862
2863 $bookkeeping->date_creation = $now;
2864 $bookkeeping->doc_type = 'closure';
2865 $bookkeeping->fk_doc = $fiscal_period->id;
2866 $bookkeeping->fk_docdet = 0; // Useless, can be several lines that are source of this record to add
2867 $bookkeeping->thirdparty_code = '';
2868
2869 if ($separate_auxiliary_account) {
2870 $bookkeeping->subledger_account = $obj->subledger_account;
2871 $sql = 'SELECT';
2872 $sql .= " subledger_label";
2873 $sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
2874 $sql .= " WHERE subledger_account = '" . $this->db->escape($obj->subledger_account) . "'";
2875 $sql .= " ORDER BY doc_date DESC";
2876 $sql .= " LIMIT 1";
2877 $result = $this->db->query($sql);
2878 if (!$result) {
2879 $this->errors[] = 'Error: ' . $this->db->lasterror();
2880 dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
2881 $error++;
2882 }
2883 $objtmp = $this->db->fetch_object($result);
2884 $bookkeeping->subledger_label = $objtmp->subledger_label; // latest subledger label used
2885 } else {
2886 $bookkeeping->subledger_account = null;
2887 $bookkeeping->subledger_label = null;
2888 }
2889
2890 $bookkeeping->numero_compte = $obj->numero_compte;
2891 $accountingaccount = new AccountingAccount($this->db);
2892 $accountingaccount->fetch('', $obj->numero_compte);
2893 $bookkeeping->label_compte = $accountingaccount->label; // latest account label used
2894
2895 $bookkeeping->label_operation = $new_fiscal_period->label;
2896 $bookkeeping->montant = $mt;
2897 $bookkeeping->sens = ($mt >= 0) ? 'C' : 'D';
2898 $bookkeeping->debit = ($mt < 0) ? -$mt : 0;
2899 $bookkeeping->credit = ($mt >= 0) ? $mt : 0;
2900 $bookkeeping->code_journal = $journal->code;
2901 $bookkeeping->journal_label = $langs->transnoentities($journal->label);
2902 $bookkeeping->fk_user_author = $user->id;
2903 $bookkeeping->entity = $conf->entity;
2904
2905 $result = $bookkeeping->create($user);
2906 if ($result < 0) {
2907 $this->error = $bookkeeping->error;
2908 $this->errors = $bookkeeping->errors;
2909 $error++;
2910 break;
2911 }
2912 }
2913 }
2914
2915 // Insert bookkeeping record for income statement
2916 if (!$error && $income_statement_amount != 0) {
2917 $mt = $income_statement_amount;
2918 $accountingaccount = new AccountingAccount($this->db);
2919 $accountingaccount->fetch(null, getDolGlobalString($income_statement_amount < 0 ? 'ACCOUNTING_RESULT_LOSS' : 'ACCOUNTING_RESULT_PROFIT'), true);
2920
2921 $bookkeeping = new BookKeeping($this->db);
2922 $bookkeeping->doc_date = $new_fiscal_period->date_start;
2923
2924 $bookkeeping->date_lim_reglement = '';
2925 $bookkeeping->doc_ref = $fiscal_period->label;
2926
2927 $bookkeeping->date_creation = $now;
2928 $bookkeeping->doc_type = 'closure';
2929 $bookkeeping->fk_doc = $fiscal_period->id;
2930 $bookkeeping->fk_docdet = 0; // Useless, can be several lines that are source of this record to add
2931 $bookkeeping->thirdparty_code = '';
2932
2933 if ($separate_auxiliary_account) {
2934 $bookkeeping->subledger_account = $obj->subledger_account;
2935 $sql = 'SELECT';
2936 $sql .= " subledger_label";
2937 $sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
2938 $sql .= " WHERE subledger_account = '" . $this->db->escape($obj->subledger_account) . "'";
2939 $sql .= " ORDER BY doc_date DESC";
2940 $sql .= " LIMIT 1";
2941 $result = $this->db->query($sql);
2942 if (!$result) {
2943 $this->errors[] = 'Error: ' . $this->db->lasterror();
2944 dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
2945 $error++;
2946 }
2947 $objtmp = $this->db->fetch_object($result);
2948 $bookkeeping->subledger_label = $objtmp->subledger_label; // latest subledger label used
2949 } else {
2950 $bookkeeping->subledger_account = null;
2951 $bookkeeping->subledger_label = null;
2952 }
2953
2954 $bookkeeping->numero_compte = $accountingaccount->account_number;
2955 $bookkeeping->label_compte = $accountingaccount->label;
2956
2957 $bookkeeping->label_operation = $new_fiscal_period->label;
2958 $bookkeeping->montant = $mt;
2959 $bookkeeping->sens = ($mt >= 0) ? 'C' : 'D';
2960 $bookkeeping->debit = ($mt < 0) ? -$mt : 0;
2961 $bookkeeping->credit = ($mt >= 0) ? $mt : 0;
2962 $bookkeeping->code_journal = $journal->code;
2963 $bookkeeping->journal_label = $langs->transnoentities($journal->label);
2964 $bookkeeping->fk_user_author = $user->id;
2965 $bookkeeping->entity = $conf->entity;
2966
2967 $result = $bookkeeping->create($user);
2968 if ($result < 0) {
2969 $this->error = $bookkeeping->error;
2970 $this->errors = $bookkeeping->errors;
2971 $error++;
2972 }
2973 }
2974 $this->db->free($resql);
2975 }
2976 }
2977 }
2978
2979 if ($error) {
2980 $this->db->rollback();
2981 return -1;
2982 } else {
2983 $this->db->commit();
2984 return 1;
2985 }
2986 }
2987
2998 public function insertAccountingReversal($fiscal_period_id, $inventory_journal_id, $new_fiscal_period_id, $date_start, $date_end)
2999 {
3000 global $conf, $langs, $user;
3001
3002 // Current fiscal period
3003 $fiscal_period_id = max(0, $fiscal_period_id);
3004 if (empty($fiscal_period_id)) {
3005 $langs->load('errors');
3006 $this->errors[] = $langs->trans('ErrorBadParameters');
3007 return -1;
3008 }
3009 $fiscal_period = new Fiscalyear($this->db);
3010 $result = $fiscal_period->fetch($fiscal_period_id);
3011 if ($result < 0) {
3012 $this->error = $fiscal_period->error;
3013 $this->errors = $fiscal_period->errors;
3014 return -1;
3015 } elseif (empty($fiscal_period->id)) {
3016 $langs->loadLangs(array('errors', 'compta'));
3017 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $fiscal_period_id . ')';
3018 return -1;
3019 }
3020
3021 // New fiscal period
3022 $new_fiscal_period_id = max(0, $new_fiscal_period_id);
3023 if (empty($new_fiscal_period_id)) {
3024 $langs->load('errors');
3025 $this->errors[] = $langs->trans('ErrorBadParameters');
3026 return -1;
3027 }
3028 $new_fiscal_period = new Fiscalyear($this->db);
3029 $result = $new_fiscal_period->fetch($new_fiscal_period_id);
3030 if ($result < 0) {
3031 $this->error = $new_fiscal_period->error;
3032 $this->errors = $new_fiscal_period->errors;
3033 return -1;
3034 } elseif (empty($new_fiscal_period->id)) {
3035 $langs->loadLangs(array('errors', 'compta'));
3036 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $new_fiscal_period_id . ')';
3037 return -1;
3038 }
3039
3040 // Inventory journal
3041 $inventory_journal_id = max(0, $inventory_journal_id);
3042 if (empty($inventory_journal_id)) {
3043 $langs->load('errors');
3044 $this->errors[] = $langs->trans('ErrorBadParameters');
3045 return -1;
3046 }
3047 // Fetch journal
3048 $inventory_journal = new AccountingJournal($this->db);
3049 $result = $inventory_journal->fetch($inventory_journal_id);
3050 if ($result < 0) {
3051 $this->error = $inventory_journal->error;
3052 $this->errors = $inventory_journal->errors;
3053 return -1;
3054 } elseif ($result == 0) {
3055 $langs->loadLangs(array('errors', 'accountancy'));
3056 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('InventoryJournal');
3057 return -1;
3058 }
3059
3060 $error = 0;
3061 $this->db->begin();
3062
3063 $sql = 'SELECT t.rowid';
3064 $sql .= ' FROM ' . MAIN_DB_PREFIX . $this->table_element . ' as t';
3065 $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
3066 $sql .= " AND code_journal = '" . $this->db->escape($inventory_journal->code) . "'";
3067 $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($date_start) . "'";
3068 $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($date_end) . "'";
3069 $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($fiscal_period->date_start) . "'";
3070 $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($fiscal_period->date_end) . "'";
3071
3072 $resql = $this->db->query($sql);
3073 if (!$resql) {
3074 $this->errors[] = 'Error ' . $this->db->lasterror();
3075 dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
3076
3077 $error++;
3078 } else {
3079 $now = dol_now();
3080 while ($obj = $this->db->fetch_object($resql)) {
3081 $bookkeeping = new BookKeeping($this->db);
3082 $result = $bookkeeping->fetch($obj->rowid);
3083 if ($result < 0) {
3084 $this->error = $inventory_journal->error;
3085 $this->errors = $inventory_journal->errors;
3086 $error++;
3087 break;
3088 } elseif ($result == 0) {
3089 $langs->loadLangs(array('errors', 'accountancy'));
3090 $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('LineId') . ': ' . $obj->rowid;
3091 $error++;
3092 break;
3093 }
3094
3095 $bookkeeping->id = 0;
3096 $bookkeeping->doc_date = $new_fiscal_period->date_start;
3097 $bookkeeping->doc_ref = $new_fiscal_period->label;
3098 $bookkeeping->date_creation = $now;
3099 $bookkeeping->doc_type = 'accounting_reversal';
3100 $bookkeeping->fk_doc = $new_fiscal_period->id;
3101 $bookkeeping->fk_docdet = 0; // Useless, can be several lines that are source of this record to add
3102
3103 $bookkeeping->montant = -$bookkeeping->montant;
3104 $bookkeeping->sens = ($bookkeeping->montant >= 0) ? 'C' : 'D';
3105 $old_debit = $bookkeeping->debit;
3106 $bookkeeping->debit = $bookkeeping->credit;
3107 $bookkeeping->credit = $old_debit;
3108
3109 $bookkeeping->fk_user_author = $user->id;
3110 $bookkeeping->entity = $conf->entity;
3111
3112 $result = $bookkeeping->create($user);
3113 if ($result < 0) {
3114 $this->error = $bookkeeping->error;
3115 $this->errors = $bookkeeping->errors;
3116 $error++;
3117 break;
3118 }
3119 }
3120 $this->db->free($resql);
3121 }
3122
3123 if ($error) {
3124 $this->db->rollback();
3125 return -1;
3126 } else {
3127 $this->db->commit();
3128 return 1;
3129 }
3130 }
3131}
3132
3137{
3141 public $id;
3142
3143 public $doc_date = null;
3144 public $doc_type;
3145 public $doc_ref;
3146
3150 public $fk_doc;
3151
3155 public $fk_docdet;
3156
3157 public $thirdparty_code;
3158 public $subledger_account;
3159 public $subledger_label;
3160 public $numero_compte;
3161 public $label_compte;
3162 public $label_operation;
3163 public $debit;
3164 public $credit;
3165
3170 public $montant;
3171
3175 public $amount;
3176
3180 public $multicurrency_amount;
3181
3185 public $multicurrency_code;
3186
3190 public $sens;
3191 public $lettering_code;
3192 public $date_lettering;
3193
3197 public $fk_user_author;
3198
3199 public $import_key;
3200 public $code_journal;
3201 public $journal_label;
3205 public $piece_num;
3206
3210 public $date_creation;
3211
3215 public $date_modification;
3216
3220 public $date_export;
3221
3225 public $date_validation;
3226
3230 public $date_lim_reglement;
3231}
if( $user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition card.php:58
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous)
ajax_combobox($htmlname, $events=array(), $minLengthToAutocomplete=0, $forcefocus=0, $widthTypeOfAutocomplete='resolve', $idforemptyvalue='-1', $morecss='')
Convert a html select field into an ajax combobox.
Definition ajax.lib.php:456
Class to manage accounting accounts.
Class to manage accounting journals.
Class to manage Ledger (General Ledger and Subledger)
closeFiscalPeriod($fiscal_period_id, $new_fiscal_period_id, $separate_auxiliary_account=false, $generate_bookkeeping_records=true)
Close fiscal period.
getCountByMonthForFiscalPeriod($date_start, $date_end)
Get list of count by month into the fiscal period.
fetchAllBalance($sortorder='', $sortfield='', $limit=0, $offset=0, $filter='', $filtermode='AND', $option=0)
Load object in memory from the database.
getNomUrl($withpicto=0, $option='', $notooltip=0, $morecss='', $save_lastsearch_value=-1)
Return a link to the object card (with optionally the picto)
select_account($selectid, $htmlname='account', $showempty=0, $event=array(), $select_in=0, $select_out=0, $aabase='')
Return list of accounts with label by chart of accounts.
getNextNumMvt($mode='')
Return next movement number.
get_compte_desc($account=null)
Description of accounting account.
__construct(DoliDB $db)
Constructor.
createFromClone(User $user, $fromid)
Load an object from its id and create a new one in database.
deleteByImportkey($importkey, $mode='')
Delete bookkeeping by importkey.
getRootAccount($account=null)
Return id and description of a root accounting account.
transformTransaction($direction=0, $piece_num='')
Transform transaction.
insertAccountingReversal($fiscal_period_id, $inventory_journal_id, $new_fiscal_period_id, $date_start, $date_end)
Insert accounting reversal into the inventory journal of the new fiscal period.
fetchPerMvt($piecenum, $mode='')
Load an accounting document into memory from database.
create(User $user, $notrigger=0)
Create object into database.
updateByMvt($piece_num='', $field='', $value='', $mode='')
Update accounting movement.
createStd(User $user, $notrigger=0, $mode='')
Create object into database.
update(User $user, $notrigger=0, $mode='')
Update object into database.
deleteMvtNum($piecenum, $mode='')
Delete bookkeeping by piece number.
getFiscalPeriods($filter='')
Get list of fiscal period ordered by start date.
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.
initAsSpecimen()
Initialise object with example values Id must be 0 if object instance is a specimen.
getCanModifyBookkeepingSQL($alias='', $force=false)
Get SQL string for check if the bookkeeping can be modified or deleted ? (cached)
fetch($id, $ref=null, $mode='')
Load object in memory from the database.
fetchAll($sortorder='', $sortfield='', $limit=0, $offset=0, $filter='', $filtermode='AND', $showAlreadyExportMovements=1)
Load object in memory from the database.
accountingResult($date_start, $date_end)
Define accounting result.
canModifyBookkeeping($id, $mode='')
Is the bookkeeping can be modified or deleted ?
fetchAllPerMvt($piecenum, $mode='')
Load all accounting lines related to a given transaction ID $piecenum.
validateMovementForFiscalPeriod($date_start, $date_end)
Validate all movement between the specified dates.
export_bookkeeping($model='ebp')
Export bookkeeping.
Class BookKeepingLine.
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Parent class for class inheritance lines of business objects This class is useless for the moment so ...
Class to manage Dolibarr database access.
Class to manage fiscal year.
static selectarray($htmlname, $array, $id='', $show_empty=0, $key_in_label=0, $value_as_key=0, $moreparam='', $translate=0, $maxlen=0, $disabled=0, $sort='', $morecss='minwidth75', $addjscombo=1, $moreparamonempty='', $disablebademail=0, $nohtmlescape=0)
Return a HTML select string, built from an array of key+value.
Class to manage Dolibarr users.
dolSqlDateFilter($datefield, $day_date, $month_date, $year_date, $excludefirstand=0, $gm=false)
Generate a SQL string to make a filter into a range (for second of date until last second of date).
Definition date.lib.php:378
img_object($titlealt, $picto, $moreatt='', $pictoisfullpath=0, $srconly=0, $notitle=0)
Show a picto called object_picto (generic function)
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
natural_search($fields, $value, $mode=0, $nofirstand=0)
Generate natural SQL search string for a criteria (this criteria can be tested on one or several fiel...
dol_strlen($string, $stringencoding='UTF-8')
Make a strlen call.
forgeSQLFromUniversalSearchCriteria($filter, &$errorstr='', $noand=0, $nopar=0, $noerror=0)
forgeSQLFromUniversalSearchCriteria
dol_now($mode='auto')
Return date for now.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
getDolGlobalString($key, $default='')
Return 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.