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