dolibarr  20.0.0-alpha
bookkeeping.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2014-2017 Olivier Geffroy <jeff@jeffinfo.com>
3  * Copyright (C) 2015-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
29 require_once DOL_DOCUMENT_ROOT.'/core/class/commonobject.class.php';
30 require_once DOL_DOCUMENT_ROOT.'/core/class/commonobjectline.class.php';
31 require_once DOL_DOCUMENT_ROOT.'/core/class/fiscalyear.class.php';
32 require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingjournal.class.php';
33 require_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountingaccount.class.php';
34 
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_validate >= \''.$this->db->idate($value).'\'';
930  } elseif ($key == 't.date_validated<=') {
931  $sqlwhere[] = 't;date_validate <= \''.$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  $sqlwhere = array();
1091  if (count($filter) > 0) {
1092  foreach ($filter as $key => $value) {
1093  if ($key == 't.doc_date') {
1094  $sqlwhere[] = $this->db->sanitize($key).' = \''.$this->db->idate($value).'\'';
1095  } elseif ($key == 't.doc_date>=') {
1096  $sqlwhere[] = "t.doc_date >= '".$this->db->idate($value)."'";
1097  } elseif ($key == 't.doc_date<=') {
1098  $sqlwhere[] = "t.doc_date <= '".$this->db->idate($value)."'";
1099  } elseif ($key == 't.doc_date>') {
1100  $sqlwhere[] = "t.doc_date > '".$this->db->idate($value)."'";
1101  } elseif ($key == 't.doc_date<') {
1102  $sqlwhere[] = "t.doc_date < '".$this->db->idate($value)."'";
1103  } elseif ($key == 't.numero_compte>=') {
1104  $sqlwhere[] = "t.numero_compte >= '".$this->db->escape($value)."'";
1105  } elseif ($key == 't.numero_compte<=') {
1106  $sqlwhere[] = "t.numero_compte <= '".$this->db->escape($value)."'";
1107  } elseif ($key == 't.subledger_account>=') {
1108  $sqlwhere[] = "t.subledger_account >= '".$this->db->escape($value)."'";
1109  } elseif ($key == 't.subledger_account<=') {
1110  $sqlwhere[] = "t.subledger_account <= '".$this->db->escape($value)."'";
1111  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1112  $sqlwhere[] = $this->db->sanitize($key).' = '.((int) $value);
1113  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1114  $sqlwhere[] = $this->db->sanitize($key).' LIKE \''.$this->db->escape($value).'%\'';
1115  } elseif ($key == 't.date_creation>=') {
1116  $sqlwhere[] = 't.date_creation >= \''.$this->db->idate($value).'\'';
1117  } elseif ($key == 't.date_creation<=') {
1118  $sqlwhere[] = 't.date_creation <= \''.$this->db->idate($value).'\'';
1119  } elseif ($key == 't.tms>=') {
1120  $sqlwhere[] = 't.tms >= \''.$this->db->idate($value).'\'';
1121  } elseif ($key == 't.tms<=') {
1122  $sqlwhere[] = 't.tms <= \''.$this->db->idate($value).'\'';
1123  } elseif ($key == 't.date_export>=') {
1124  $sqlwhere[] = 't.date_export >= \''.$this->db->idate($value).'\'';
1125  } elseif ($key == 't.date_export<=') {
1126  $sqlwhere[] = 't.date_export <= \''.$this->db->idate($value).'\'';
1127  } elseif ($key == 't.date_validated>=') {
1128  $sqlwhere[] = 't.date_validated >= \''.$this->db->idate($value).'\'';
1129  } elseif ($key == 't.date_validated<=') {
1130  $sqlwhere[] = 't.date_validated <= \''.$this->db->idate($value).'\'';
1131  } elseif ($key == 't.credit' || $key == 't.debit') {
1132  $sqlwhere[] = natural_search($key, $value, 1, 1);
1133  } elseif ($key == 't.code_journal' && !empty($value)) {
1134  if (is_array($value)) {
1135  $sqlwhere[] = natural_search("t.code_journal", implode(',', $value), 3, 1);
1136  } else {
1137  $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1138  }
1139  } else {
1140  $sqlwhere[] = natural_search($key, $value, 0, 1);
1141  }
1142  }
1143  }
1144  if (count($sqlwhere) > 0) {
1145  $sql .= ' AND '.implode(" ".$this->db->sanitize($filtermode)." ", $sqlwhere);
1146  }
1147 
1148  $filter = '';
1149  }
1150 
1151  // Manage filter
1152  $errormessage = '';
1153  $sql .= forgeSQLFromUniversalSearchCriteria($filter, $errormessage);
1154  if ($errormessage) {
1155  $this->errors[] = $errormessage;
1156  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1157  return -1;
1158  }
1159 
1160  if (!empty($sortfield)) {
1161  $sql .= $this->db->order($sortfield, $sortorder);
1162  }
1163  if (!empty($limit)) {
1164  $sql .= $this->db->plimit($limit + 1, $offset);
1165  }
1166  $this->lines = array();
1167 
1168  $resql = $this->db->query($sql);
1169  if ($resql) {
1170  $num = $this->db->num_rows($resql);
1171 
1172  $i = 0;
1173  while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1174  $line = new BookKeepingLine($this->db);
1175 
1176  $line->id = $obj->rowid;
1177 
1178  $line->doc_date = $this->db->jdate($obj->doc_date);
1179  $line->doc_type = $obj->doc_type;
1180  $line->doc_ref = $obj->doc_ref;
1181  $line->fk_doc = $obj->fk_doc;
1182  $line->fk_docdet = $obj->fk_docdet;
1183  $line->thirdparty_code = $obj->thirdparty_code;
1184  $line->subledger_account = $obj->subledger_account;
1185  $line->subledger_label = $obj->subledger_label;
1186  $line->numero_compte = $obj->numero_compte;
1187  $line->label_compte = $obj->label_compte;
1188  $line->label_operation = $obj->label_operation;
1189  $line->debit = $obj->debit;
1190  $line->credit = $obj->credit;
1191  $line->montant = $obj->amount; // deprecated
1192  $line->amount = $obj->amount;
1193  $line->sens = $obj->sens;
1194  $line->lettering_code = $obj->lettering_code;
1195  $line->date_lettering = $obj->date_lettering;
1196  $line->fk_user_author = $obj->fk_user_author;
1197  $line->import_key = $obj->import_key;
1198  $line->code_journal = $obj->code_journal;
1199  $line->journal_label = $obj->journal_label;
1200  $line->piece_num = $obj->piece_num;
1201  $line->date_creation = $this->db->jdate($obj->date_creation);
1202  $line->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
1203  $line->date_modification = $this->db->jdate($obj->date_modification);
1204  $line->date_export = $this->db->jdate($obj->date_export);
1205  $line->date_validation = $this->db->jdate($obj->date_validation);
1206 
1207  $this->lines[] = $line;
1208 
1209  $i++;
1210  }
1211  $this->db->free($resql);
1212 
1213  return $num;
1214  } else {
1215  $this->errors[] = 'Error '.$this->db->lasterror();
1216  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1217  return -1;
1218  }
1219  }
1220 
1233  public function fetchAllBalance($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, $filter = '', $filtermode = 'AND', $option = 0)
1234  {
1235  global $conf;
1236 
1237  $this->lines = array();
1238 
1239  dol_syslog(__METHOD__, LOG_DEBUG);
1240 
1241  $sql = 'SELECT';
1242  $sql .= " t.numero_compte,";
1243  if (!empty($option)) {
1244  $sql .= " t.subledger_account,";
1245  $sql .= " t.subledger_label,";
1246  }
1247  $sql .= " SUM(t.debit) as debit,";
1248  $sql .= " SUM(t.credit) as credit";
1249  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
1250  $sql .= ' WHERE entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1251 
1252  // Manage filter
1253  if (is_array($filter)) {
1254  $sqlwhere = array();
1255  if (count($filter) > 0) {
1256  foreach ($filter as $key => $value) {
1257  if ($key == 't.doc_date') {
1258  $sqlwhere[] = $this->db->sanitize($key)." = '".$this->db->idate($value)."'";
1259  } elseif ($key == 't.doc_date>=') {
1260  $sqlwhere[] = "t.doc_date >= '".$this->db->idate($value)."'";
1261  } elseif ($key == 't.doc_date<=') {
1262  $sqlwhere[] = "t.doc_date <= '".$this->db->idate($value)."'";
1263  } elseif ($key == 't.doc_date>') {
1264  $sqlwhere[] = "t.doc_date > '".$this->db->idate($value)."'";
1265  } elseif ($key == 't.doc_date<') {
1266  $sqlwhere[] = "t.doc_date < '".$this->db->idate($value)."'";
1267  } elseif ($key == 't.numero_compte>=') {
1268  $sqlwhere[] = "t.numero_compte >= '".$this->db->escape($value)."'";
1269  } elseif ($key == 't.numero_compte<=') {
1270  $sqlwhere[] = "t.numero_compte <= '".$this->db->escape($value)."'";
1271  } elseif ($key == 't.subledger_account>=') {
1272  $sqlwhere[] = "t.subledger_account >= '".$this->db->escape($value)."'";
1273  } elseif ($key == 't.subledger_account<=') {
1274  $sqlwhere[] = "t.subledger_account <= '".$this->db->escape($value)."'";
1275  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1276  $sqlwhere[] = $this->db->sanitize($key)." = ".((int) $value);
1277  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1278  $sqlwhere[] = $this->db->sanitize($key)." LIKE '".$this->db->escape($value)."%'";
1279  } elseif ($key == 't.subledger_label') {
1280  $sqlwhere[] = $this->db->sanitize($key)." LIKE '".$this->db->escape($value)."%'";
1281  } elseif ($key == 't.code_journal' && !empty($value)) {
1282  if (is_array($value)) {
1283  $sqlwhere[] = natural_search("t.code_journal", implode(',', $value), 3, 1);
1284  } else {
1285  $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1286  }
1287  } elseif ($key == 't.reconciled_option') {
1288  $sqlwhere[] = 't.lettering_code IS NULL';
1289  } else {
1290  $sqlwhere[] = $this->db->sanitize($key)." LIKE '%".$this->db->escape($this->db->escapeforlike($value))."%'";
1291  }
1292  }
1293  }
1294  if (count($sqlwhere) > 0) {
1295  $sql .= " AND ".implode(" ".$this->db->sanitize($filtermode)." ", $sqlwhere);
1296  }
1297 
1298  $filter = '';
1299  }
1300 
1301  // Manage filter
1302  $errormessage = '';
1303  $sql .= forgeSQLFromUniversalSearchCriteria($filter, $errormessage);
1304  if ($errormessage) {
1305  $this->errors[] = $errormessage;
1306  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1307  return -1;
1308  }
1309 
1310  if (!empty($option)) {
1311  $sql .= " AND t.subledger_account IS NOT NULL";
1312  $sql .= " AND t.subledger_account <> ''";
1313  $sql .= " GROUP BY t.numero_compte, t.subledger_account, t.subledger_label";
1314  $sortfield = 't.subledger_account'.($sortfield ? ','.$sortfield : '');
1315  $sortorder = 'ASC'.($sortfield ? ','.$sortfield : '');
1316  } else {
1317  $sql .= ' GROUP BY t.numero_compte';
1318  $sortfield = 't.numero_compte'.($sortfield ? ','.$sortfield : '');
1319  $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
1320  }
1321 
1322  if (!empty($sortfield)) {
1323  $sql .= $this->db->order($sortfield, $sortorder);
1324  }
1325  if (!empty($limit)) {
1326  $sql .= $this->db->plimit($limit + 1, $offset);
1327  }
1328 
1329  //print $sql;
1330  $resql = $this->db->query($sql);
1331 
1332  if ($resql) {
1333  $num = $this->db->num_rows($resql);
1334 
1335  $i = 0;
1336  while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1337  $line = new BookKeepingLine($this->db);
1338 
1339  $line->numero_compte = $obj->numero_compte;
1340  //$line->label_compte = $obj->label_compte;
1341  if (!empty($option)) {
1342  $line->subledger_account = $obj->subledger_account;
1343  $line->subledger_label = $obj->subledger_label;
1344  }
1345  $line->debit = $obj->debit;
1346  $line->credit = $obj->credit;
1347 
1348  $this->lines[] = $line;
1349 
1350  $i++;
1351  }
1352  $this->db->free($resql);
1353 
1354  return $num;
1355  } else {
1356  $this->errors[] = 'Error '.$this->db->lasterror();
1357  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1358 
1359  return -1;
1360  }
1361  }
1362 
1371  public function update(User $user, $notrigger = 0, $mode = '')
1372  {
1373  global $langs;
1374  $error = 0;
1375 
1376  dol_syslog(__METHOD__, LOG_DEBUG);
1377 
1378  // Clean parameters
1379  if (isset($this->doc_type)) {
1380  $this->doc_type = trim($this->doc_type);
1381  }
1382  if (isset($this->doc_ref)) {
1383  $this->doc_ref = trim($this->doc_ref);
1384  }
1385  if (isset($this->fk_doc)) {
1386  $this->fk_doc = (int) $this->fk_doc;
1387  }
1388  if (isset($this->fk_docdet)) {
1389  $this->fk_docdet = (int) $this->fk_docdet;
1390  }
1391  if (isset($this->thirdparty_code)) {
1392  $this->thirdparty_code = trim($this->thirdparty_code);
1393  }
1394  if (isset($this->subledger_account)) {
1395  $this->subledger_account = trim($this->subledger_account);
1396  }
1397  if (isset($this->subledger_label)) {
1398  $this->subledger_label = trim($this->subledger_label);
1399  }
1400  if (isset($this->numero_compte)) {
1401  $this->numero_compte = trim($this->numero_compte);
1402  }
1403  if (isset($this->label_compte)) {
1404  $this->label_compte = trim($this->label_compte);
1405  }
1406  if (isset($this->label_operation)) {
1407  $this->label_operation = trim($this->label_operation);
1408  }
1409  if (isset($this->sens)) {
1410  $this->sens = trim($this->sens);
1411  }
1412  if (isset($this->import_key)) {
1413  $this->import_key = trim($this->import_key);
1414  }
1415  if (isset($this->code_journal)) {
1416  $this->code_journal = trim($this->code_journal);
1417  }
1418  if (isset($this->journal_label)) {
1419  $this->journal_label = trim($this->journal_label);
1420  }
1421  if (isset($this->piece_num)) {
1422  $this->piece_num = (int) $this->piece_num;
1423  }
1424 
1425  $result = $this->canModifyBookkeeping($this->id, $mode);
1426  if ($result < 0) {
1427  return -1;
1428  } elseif ($result == 0) {
1429  if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
1430  $this->errors[] = $langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod');
1431  } else {
1432  $this->errors[] = $langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod');
1433  }
1434  return -1;
1435  }
1436 
1437  $this->debit = (float) price2num($this->debit, 'MT');
1438  $this->credit = (float) price2num($this->credit, 'MT');
1439  $this->montant = (float) price2num($this->montant, 'MT');
1440 
1441  // Check parameters
1442  // Put here code to add a control on parameters values
1443 
1444  // Update request
1445  $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element.$mode.' SET';
1446  $sql .= ' doc_date = '.(!isset($this->doc_date) || dol_strlen($this->doc_date) != 0 ? "'".$this->db->idate($this->doc_date)."'" : 'null').',';
1447  $sql .= ' doc_type = '.(isset($this->doc_type) ? "'".$this->db->escape($this->doc_type)."'" : "null").',';
1448  $sql .= ' doc_ref = '.(isset($this->doc_ref) ? "'".$this->db->escape($this->doc_ref)."'" : "null").',';
1449  $sql .= ' fk_doc = '.(isset($this->fk_doc) ? $this->fk_doc : "null").',';
1450  $sql .= ' fk_docdet = '.(isset($this->fk_docdet) ? $this->fk_docdet : "null").',';
1451  $sql .= ' thirdparty_code = '.(isset($this->thirdparty_code) ? "'".$this->db->escape($this->thirdparty_code)."'" : "null").',';
1452  $sql .= ' subledger_account = '.(isset($this->subledger_account) ? "'".$this->db->escape($this->subledger_account)."'" : "null").',';
1453  $sql .= ' subledger_label = '.(isset($this->subledger_label) ? "'".$this->db->escape($this->subledger_label)."'" : "null").',';
1454  $sql .= ' numero_compte = '.(isset($this->numero_compte) ? "'".$this->db->escape($this->numero_compte)."'" : "null").',';
1455  $sql .= ' label_compte = '.(isset($this->label_compte) ? "'".$this->db->escape($this->label_compte)."'" : "null").',';
1456  $sql .= ' label_operation = '.(isset($this->label_operation) ? "'".$this->db->escape($this->label_operation)."'" : "null").',';
1457  $sql .= ' debit = '.(isset($this->debit) ? $this->debit : "null").',';
1458  $sql .= ' credit = '.(isset($this->credit) ? $this->credit : "null").',';
1459  $sql .= ' montant = '.(isset($this->montant) ? $this->montant : "null").',';
1460  $sql .= ' sens = '.(isset($this->sens) ? "'".$this->db->escape($this->sens)."'" : "null").',';
1461  $sql .= ' fk_user_author = '.(isset($this->fk_user_author) ? $this->fk_user_author : "null").',';
1462  $sql .= ' import_key = '.(isset($this->import_key) ? "'".$this->db->escape($this->import_key)."'" : "null").',';
1463  $sql .= ' code_journal = '.(isset($this->code_journal) ? "'".$this->db->escape($this->code_journal)."'" : "null").',';
1464  $sql .= ' journal_label = '.(isset($this->journal_label) ? "'".$this->db->escape($this->journal_label)."'" : "null").',';
1465  $sql .= ' piece_num = '.(isset($this->piece_num) ? $this->piece_num : "null");
1466  $sql .= ' WHERE rowid='.((int) $this->id);
1467 
1468  $this->db->begin();
1469 
1470  $resql = $this->db->query($sql);
1471  if (!$resql) {
1472  $error++;
1473  $this->errors[] = 'Error '.$this->db->lasterror();
1474  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1475  }
1476 
1477  // Uncomment this and change MYOBJECT to your own tag if you
1478  // want this action calls a trigger.
1479  //if (! $error && ! $notrigger) {
1480 
1481  // // Call triggers
1482  // $result=$this->call_trigger('MYOBJECT_MODIFY',$user);
1483  // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1484  // // End call triggers
1485  //}
1486 
1487  // Commit or rollback
1488  if ($error) {
1489  $this->db->rollback();
1490 
1491  return -1 * $error;
1492  } else {
1493  $this->db->commit();
1494 
1495  return 1;
1496  }
1497  }
1498 
1508  public function updateByMvt($piece_num = '', $field = '', $value = '', $mode = '')
1509  {
1510  $error = 0;
1511 
1512  $sql_filter = $this->getCanModifyBookkeepingSQL();
1513  if (!isset($sql_filter)) {
1514  return -1;
1515  }
1516 
1517  $this->db->begin();
1518 
1519  $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element.$mode;
1520  $sql .= " SET ".$this->db->sanitize($field)." = ".(is_numeric($value) ? ((float) $value) : "'".$this->db->escape($value)."'");
1521  $sql .= " WHERE piece_num = ".((int) $piece_num);
1522  $sql .= $sql_filter;
1523 
1524  $resql = $this->db->query($sql);
1525 
1526  if (!$resql) {
1527  $error++;
1528  $this->errors[] = 'Error '.$this->db->lasterror();
1529  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1530  }
1531  if ($error) {
1532  $this->db->rollback();
1533 
1534  return -1 * $error;
1535  } else {
1536  $this->db->commit();
1537 
1538  return 1;
1539  }
1540  }
1541 
1550  public function delete(User $user, $notrigger = 0, $mode = '')
1551  {
1552  global $langs;
1553 
1554  dol_syslog(__METHOD__, LOG_DEBUG);
1555 
1556  $result = $this->canModifyBookkeeping($this->id, $mode);
1557  if ($result < 0) {
1558  return -1;
1559  } elseif ($result == 0) {
1560  if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
1561  $this->errors[] = $langs->trans('ErrorBookkeepingDocDateIsOnAClosedFiscalPeriod');
1562  } else {
1563  $this->errors[] = $langs->trans('ErrorBookkeepingDocDateNotOnActiveFiscalPeriod');
1564  }
1565  return -1;
1566  }
1567 
1568  $error = 0;
1569 
1570  $this->db->begin();
1571 
1572  // Uncomment this and change MYOBJECT to your own tag if you
1573  // want this action calls a trigger.
1574  //if (! $error && ! $notrigger) {
1575 
1576  // // Call triggers
1577  // $result=$this->call_trigger('MYOBJECT_DELETE',$user);
1578  // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1579  // // End call triggers
1580  //}
1581 
1582  if (!$error) {
1583  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.$mode;
1584  $sql .= ' WHERE rowid='.((int) $this->id);
1585 
1586  $resql = $this->db->query($sql);
1587  if (!$resql) {
1588  $error++;
1589  $this->errors[] = 'Error '.$this->db->lasterror();
1590  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1591  }
1592  }
1593 
1594  // Commit or rollback
1595  if ($error) {
1596  $this->db->rollback();
1597 
1598  return -1 * $error;
1599  } else {
1600  $this->db->commit();
1601 
1602  return 1;
1603  }
1604  }
1605 
1613  public function deleteByImportkey($importkey, $mode = '')
1614  {
1615  $this->db->begin();
1616 
1617  $sql_filter = $this->getCanModifyBookkeepingSQL();
1618  if (!isset($sql_filter)) {
1619  return -1;
1620  }
1621 
1622  // first check if line not yet in bookkeeping
1623  $sql = "DELETE";
1624  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1625  $sql .= " WHERE import_key = '".$this->db->escape($importkey)."'";
1626  $sql .= $sql_filter;
1627 
1628  $resql = $this->db->query($sql);
1629 
1630  if (!$resql) {
1631  $this->errors[] = "Error ".$this->db->lasterror();
1632  dol_syslog(get_class($this)."::delete Error ".$this->db->lasterror(), LOG_ERR);
1633  $this->db->rollback();
1634  return -1;
1635  }
1636 
1637  $this->db->commit();
1638  return 1;
1639  }
1640 
1650  public function deleteByYearAndJournal($delyear = 0, $journal = '', $mode = '', $delmonth = 0)
1651  {
1652  global $conf, $langs;
1653 
1654  if (empty($delyear) && empty($journal)) {
1655  $this->error = 'ErrorOneFieldRequired';
1656  return -1;
1657  }
1658  if (!empty($delmonth) && empty($delyear)) {
1659  $this->error = 'YearRequiredIfMonthDefined';
1660  return -2;
1661  }
1662 
1663  $sql_filter = $this->getCanModifyBookkeepingSQL();
1664  if (!isset($sql_filter)) {
1665  return -1;
1666  }
1667 
1668  $this->db->begin();
1669 
1670  // Delete record in bookkeeping
1671  $sql = "DELETE";
1672  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1673  $sql .= " WHERE 1 = 1";
1674  $sql .= dolSqlDateFilter('doc_date', 0, $delmonth, $delyear);
1675  if (!empty($journal)) {
1676  $sql .= " AND code_journal = '".$this->db->escape($journal)."'";
1677  }
1678  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1679  // Exclusion of validated entries at the time of deletion
1680  $sql .= " AND date_validated IS NULL";
1681  $sql .= $sql_filter;
1682 
1683  // TODO: In a future we must forbid deletion if record is inside a closed fiscal period.
1684 
1685  $resql = $this->db->query($sql);
1686 
1687  if (!$resql) {
1688  $this->errors[] = "Error ".$this->db->lasterror();
1689  foreach ($this->errors as $errmsg) {
1690  dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1691  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1692  }
1693  $this->db->rollback();
1694  return -1;
1695  }
1696 
1697  $this->db->commit();
1698  return 1;
1699  }
1700 
1708  public function deleteMvtNum($piecenum, $mode = '')
1709  {
1710  global $conf;
1711 
1712  $sql_filter = $this->getCanModifyBookkeepingSQL();
1713  if (!isset($sql_filter)) {
1714  return -1;
1715  }
1716 
1717  $this->db->begin();
1718 
1719  // first check if line not yet in bookkeeping
1720  $sql = "DELETE";
1721  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1722  $sql .= " WHERE piece_num = ".(int) $piecenum;
1723  $sql .= " AND date_validated IS NULL"; // For security, exclusion of validated entries at the time of deletion
1724  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1725  $sql .= $sql_filter;
1726 
1727  $resql = $this->db->query($sql);
1728 
1729  if (!$resql) {
1730  $this->errors[] = "Error ".$this->db->lasterror();
1731  foreach ($this->errors as $errmsg) {
1732  dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1733  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1734  }
1735  $this->db->rollback();
1736  return -1;
1737  }
1738 
1739  $this->db->commit();
1740  return 1;
1741  }
1742 
1750  public function createFromClone(User $user, $fromid)
1751  {
1752  dol_syslog(__METHOD__, LOG_DEBUG);
1753 
1754  $error = 0;
1755  $object = new BookKeeping($this->db);
1756 
1757  $this->db->begin();
1758 
1759  // Load source object
1760  $object->fetch($fromid);
1761  // Reset object
1762  $object->id = 0;
1763 
1764  // Clear fields
1765  // ...
1766 
1767  // Create clone
1768  $object->context['createfromclone'] = 'createfromclone';
1769  $result = $object->create($user);
1770 
1771  // Other options
1772  if ($result < 0) {
1773  $error++;
1774  $this->errors = $object->errors;
1775  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
1776  }
1777 
1778  unset($object->context['createfromclone']);
1779 
1780  // End
1781  if (!$error) {
1782  $this->db->commit();
1783 
1784  return $object->id;
1785  } else {
1786  $this->db->rollback();
1787 
1788  return -1;
1789  }
1790  }
1791 
1798  public function initAsSpecimen()
1799  {
1800  global $user;
1801 
1802  $now = dol_now();
1803 
1804  $this->id = 0;
1805  $this->doc_date = $now;
1806  $this->doc_type = '';
1807  $this->doc_ref = '';
1808  $this->fk_doc = 0;
1809  $this->fk_docdet = 0;
1810  $this->thirdparty_code = 'CU001';
1811  $this->subledger_account = '41100001';
1812  $this->subledger_label = 'My customer company';
1813  $this->numero_compte = '411';
1814  $this->label_compte = 'Customer';
1815  $this->label_operation = 'Sales of pea';
1816  $this->debit = 99.9;
1817  $this->credit = 0.0;
1818  $this->amount = 0.0;
1819  $this->sens = 'D';
1820  $this->fk_user_author = $user->id;
1821  $this->import_key = '20201027';
1822  $this->code_journal = 'VT';
1823  $this->journal_label = 'Journal de vente';
1824  $this->piece_num = 1234;
1825  $this->date_creation = $now;
1826 
1827  return 1;
1828  }
1829 
1837  public function fetchPerMvt($piecenum, $mode = '')
1838  {
1839  global $conf;
1840 
1841  $sql = "SELECT piece_num, doc_date, code_journal, journal_label, doc_ref, doc_type,";
1842  $sql .= " date_creation, tms as date_modification, date_validated as date_validation, import_key";
1843  // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1844  if ($mode != "_tmp") {
1845  $sql .= ", date_export";
1846  }
1847  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1848  $sql .= " WHERE piece_num = ".((int) $piecenum);
1849  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1850 
1851  dol_syslog(__METHOD__, LOG_DEBUG);
1852  $result = $this->db->query($sql);
1853  if ($result) {
1854  $obj = $this->db->fetch_object($result);
1855 
1856  $this->piece_num = $obj->piece_num;
1857  $this->code_journal = $obj->code_journal;
1858  $this->journal_label = $obj->journal_label;
1859  $this->doc_date = $this->db->jdate($obj->doc_date);
1860  $this->doc_ref = $obj->doc_ref;
1861  $this->doc_type = $obj->doc_type;
1862  $this->date_creation = $this->db->jdate($obj->date_creation);
1863  $this->date_modification = $this->db->jdate($obj->date_modification);
1864  if ($mode != "_tmp") {
1865  $this->date_export = $this->db->jdate($obj->date_export);
1866  }
1867  $this->date_validation = $this->db->jdate($obj->date_validation);
1868  $this->import_key = $obj->import_key;
1869  } else {
1870  $this->error = "Error ".$this->db->lasterror();
1871  dol_syslog(__METHOD__.$this->error, LOG_ERR);
1872  return -1;
1873  }
1874 
1875  return 1;
1876  }
1877 
1884  public function getNextNumMvt($mode = '')
1885  {
1886  global $conf;
1887 
1888  $sql = "SELECT MAX(piece_num)+1 as max FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1889  $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1890 
1891  dol_syslog(get_class($this)."::getNextNumMvt", LOG_DEBUG);
1892 
1893  $result = $this->db->query($sql);
1894 
1895  if ($result) {
1896  $obj = $this->db->fetch_object($result);
1897  if ($obj) {
1898  $result = $obj->max;
1899  }
1900  if (empty($result)) {
1901  $result = 1;
1902  }
1903  return $result;
1904  } else {
1905  $this->error = "Error ".$this->db->lasterror();
1906  dol_syslog(get_class($this)."::getNextNumMvt ".$this->error, LOG_ERR);
1907  return -1;
1908  }
1909  }
1910 
1918  public function fetchAllPerMvt($piecenum, $mode = '')
1919  {
1920  global $conf;
1921 
1922  $sql = "SELECT rowid, doc_date, doc_type,";
1923  $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1924  $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1925  $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, journal_label, piece_num,";
1926  $sql .= " date_creation, tms as date_modification, date_validated as date_validation";
1927  // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1928  if ($mode != "_tmp") {
1929  $sql .= ", date_export";
1930  }
1931  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1932  $sql .= " WHERE piece_num = ".((int) $piecenum);
1933  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1934 
1935  dol_syslog(__METHOD__, LOG_DEBUG);
1936  $result = $this->db->query($sql);
1937  if ($result) {
1938  while ($obj = $this->db->fetch_object($result)) {
1939  $line = new BookKeepingLine($this->db);
1940 
1941  $line->id = $obj->rowid;
1942 
1943  $line->doc_date = $this->db->jdate($obj->doc_date);
1944  $line->doc_type = $obj->doc_type;
1945  $line->doc_ref = $obj->doc_ref;
1946  $line->fk_doc = $obj->fk_doc;
1947  $line->fk_docdet = $obj->fk_docdet;
1948  $line->thirdparty_code = $obj->thirdparty_code;
1949  $line->subledger_account = $obj->subledger_account;
1950  $line->subledger_label = $obj->subledger_label;
1951  $line->numero_compte = $obj->numero_compte;
1952  $line->label_compte = $obj->label_compte;
1953  $line->label_operation = $obj->label_operation;
1954  $line->debit = $obj->debit;
1955  $line->credit = $obj->credit;
1956  $line->montant = $obj->amount;
1957  $line->amount = $obj->amount;
1958  $line->sens = $obj->sens;
1959  $line->code_journal = $obj->code_journal;
1960  $line->journal_label = $obj->journal_label;
1961  $line->piece_num = $obj->piece_num;
1962  $line->date_creation = $obj->date_creation;
1963  $line->date_modification = $obj->date_modification;
1964  if ($mode != "_tmp") {
1965  $line->date_export = $obj->date_export;
1966  }
1967  $line->date_validation = $obj->date_validation;
1968 
1969  $this->linesmvt[] = $line;
1970  }
1971  } else {
1972  $this->error = "Error ".$this->db->lasterror();
1973  dol_syslog(__METHOD__.$this->error, LOG_ERR);
1974  return -1;
1975  }
1976 
1977  return 1;
1978  }
1979 
1980  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1987  public function export_bookkeeping($model = 'ebp')
1988  {
1989  // phpcs:enable
1990  global $conf;
1991 
1992  $sql = "SELECT rowid, doc_date, doc_type,";
1993  $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1994  $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1995  $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, piece_num,";
1996  $sql .= " date_validated as date_validation";
1997  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
1998  $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1999 
2000  dol_syslog(get_class($this)."::export_bookkeeping", LOG_DEBUG);
2001 
2002  $resql = $this->db->query($sql);
2003 
2004  if ($resql) {
2005  $this->linesexport = array();
2006 
2007  $num = $this->db->num_rows($resql);
2008  while ($obj = $this->db->fetch_object($resql)) {
2009  $line = new BookKeepingLine($this->db);
2010 
2011  $line->id = $obj->rowid;
2012 
2013  $line->doc_date = $this->db->jdate($obj->doc_date);
2014  $line->doc_type = $obj->doc_type;
2015  $line->doc_ref = $obj->doc_ref;
2016  $line->fk_doc = $obj->fk_doc;
2017  $line->fk_docdet = $obj->fk_docdet;
2018  $line->thirdparty_code = $obj->thirdparty_code;
2019  $line->subledger_account = $obj->subledger_account;
2020  $line->subledger_label = $obj->subledger_label;
2021  $line->numero_compte = $obj->numero_compte;
2022  $line->label_compte = $obj->label_compte;
2023  $line->label_operation = $obj->label_operation;
2024  $line->debit = $obj->debit;
2025  $line->credit = $obj->credit;
2026  $line->montant = $obj->amount;
2027  $line->amount = $obj->amount;
2028  $line->sens = $obj->sens;
2029  $line->code_journal = $obj->code_journal;
2030  $line->piece_num = $obj->piece_num;
2031  $line->date_validation = $obj->date_validation;
2032 
2033  $this->linesexport[] = $line;
2034  }
2035  $this->db->free($resql);
2036 
2037  return $num;
2038  } else {
2039  $this->error = "Error ".$this->db->lasterror();
2040  dol_syslog(get_class($this)."::export_bookkeeping ".$this->error, LOG_ERR);
2041  return -1;
2042  }
2043  }
2044 
2052  public function transformTransaction($direction = 0, $piece_num = '')
2053  {
2054  global $conf;
2055 
2056  $error = 0;
2057 
2058  $sql_filter = $this->getCanModifyBookkeepingSQL();
2059 
2060  if (!isset($sql_filter)) {
2061  return -1;
2062  }
2063 
2064  $this->db->begin();
2065 
2066  if ($direction == 0) {
2067  $next_piecenum = $this->getNextNumMvt();
2068  $now = dol_now();
2069 
2070  if ($next_piecenum < 0) {
2071  $error++;
2072  }
2073 
2074  if (!$error) {
2075  // Delete if there is an empty line
2076  $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";
2077  $resql = $this->db->query($sql);
2078  if (!$resql) {
2079  $error++;
2080  $this->errors[] = 'Error '.$this->db->lasterror();
2081  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2082  }
2083  }
2084 
2085  if (!$error) {
2086  $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.' (doc_date, doc_type,';
2087  $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
2088  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2089  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num, date_creation)';
2090  $sql .= ' SELECT doc_date, doc_type,';
2091  $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
2092  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2093  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, '.((int) $next_piecenum).", '".$this->db->idate($now)."'";
2094  $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);
2095  $sql .= $sql_filter;
2096  $resql = $this->db->query($sql);
2097  if (!$resql) {
2098  $error++;
2099  $this->errors[] = 'Error '.$this->db->lasterror();
2100  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2101  }
2102  }
2103 
2104  if (!$error) {
2105  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2106  $resql = $this->db->query($sql);
2107  if (!$resql) {
2108  $error++;
2109  $this->errors[] = 'Error '.$this->db->lasterror();
2110  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2111  }
2112  }
2113  } elseif ($direction == 1) {
2114  if (!$error) {
2115  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2116  $resql = $this->db->query($sql);
2117  if (!$resql) {
2118  $error++;
2119  $this->errors[] = 'Error '.$this->db->lasterror();
2120  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2121  }
2122  }
2123 
2124  if (!$error) {
2125  $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.'_tmp (doc_date, doc_type,';
2126  $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
2127  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2128  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num)';
2129  $sql .= ' SELECT doc_date, doc_type,';
2130  $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
2131  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
2132  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num';
2133  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2134  $sql .= $sql_filter;
2135  $resql = $this->db->query($sql);
2136  if (!$resql) {
2137  $error++;
2138  $this->errors[] = 'Error '.$this->db->lasterror();
2139  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2140  }
2141  }
2142 
2143  if (!$error) {
2144  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
2145  $sql .= $sql_filter;
2146  $resql = $this->db->query($sql);
2147  if (!$resql) {
2148  $error++;
2149  $this->errors[] = 'Error '.$this->db->lasterror();
2150  dol_syslog(__METHOD__.' '.implode(',', $this->errors), LOG_ERR);
2151  }
2152  }
2153  }
2154  if (!$error) {
2155  $this->db->commit();
2156  return 1;
2157  } else {
2158  $this->db->rollback();
2159  return -1;
2160  }
2161  /*
2162  $sql = "DELETE FROM ";
2163  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as ab";
2164  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as aa ON aa.account_number = ab.numero_compte";
2165  $sql .= " AND aa.active = 1";
2166  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2167  $sql .= " AND asy.rowid = " . ((int) $pcgver);
2168  $sql .= " AND ab.entity IN (" . getEntity('accountancy') . ")";
2169  $sql .= " ORDER BY account_number ASC";
2170  */
2171  }
2172 
2173  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2186  public function select_account($selectid, $htmlname = 'account', $showempty = 0, $event = array(), $select_in = 0, $select_out = 0, $aabase = '')
2187  {
2188  // phpcs:enable
2189  global $conf;
2190 
2191  require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
2192 
2193  $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2194 
2195  $sql = "SELECT DISTINCT ab.numero_compte as account_number, aa.label as label, aa.rowid as rowid, aa.fk_pcg_version";
2196  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping as ab";
2197  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as aa ON aa.account_number = ab.numero_compte";
2198  $sql .= " AND aa.active = 1";
2199  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2200  $sql .= " AND asy.rowid = ".((int) $pcgver);
2201  $sql .= " AND ab.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2202  $sql .= " ORDER BY account_number ASC";
2203 
2204  dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2205  $resql = $this->db->query($sql);
2206 
2207  if (!$resql) {
2208  $this->error = "Error ".$this->db->lasterror();
2209  dol_syslog(get_class($this)."::select_account ".$this->error, LOG_ERR);
2210  return "-1";
2211  }
2212 
2213  $out = ajax_combobox($htmlname, $event);
2214 
2215  $options = array();
2216  $selected = null;
2217 
2218  while ($obj = $this->db->fetch_object($resql)) {
2219  $label = length_accountg($obj->account_number).' - '.$obj->label;
2220 
2221  $select_value_in = $obj->rowid;
2222  $select_value_out = $obj->rowid;
2223 
2224  if ($select_in == 1) {
2225  $select_value_in = $obj->account_number;
2226  }
2227  if ($select_out == 1) {
2228  $select_value_out = $obj->account_number;
2229  }
2230 
2231  // Remember guy's we store in database llx_facturedet the rowid of accounting_account and not the account_number
2232  // Because same account_number can be share between different accounting_system and do have the same meaning
2233  if (($selectid != '') && $selectid == $select_value_in) {
2234  $selected = $select_value_out;
2235  }
2236 
2237  $options[$select_value_out] = $label;
2238  }
2239 
2240  $out .= Form::selectarray($htmlname, $options, $selected, $showempty, 0, 0, '', 0, 0, 0, '', 'maxwidth300');
2241  $this->db->free($resql);
2242  return $out;
2243  }
2244 
2252  public function getRootAccount($account = null)
2253  {
2254  global $conf;
2255  $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2256 
2257  $sql = "SELECT root.rowid, root.account_number, root.label as label,";
2258  $sql .= " parent.rowid as parent_rowid, parent.account_number as parent_account_number, parent.label as parent_label";
2259  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa";
2260  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2261  $sql .= " AND asy.rowid = ".((int) $pcgver);
2262  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as parent ON aa.account_parent = parent.rowid AND parent.active = 1";
2263  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as root ON parent.account_parent = root.rowid AND root.active = 1";
2264  $sql .= " WHERE aa.account_number = '".$this->db->escape($account)."'";
2265  $sql .= " AND aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2266 
2267  dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2268  $resql = $this->db->query($sql);
2269  if ($resql) {
2270  $obj = '';
2271  if ($this->db->num_rows($resql)) {
2272  $obj = $this->db->fetch_object($resql);
2273  }
2274 
2275  $result = array('id' => $obj->rowid, 'account_number' => $obj->account_number, 'label' => $obj->label);
2276  return $result;
2277  } else {
2278  $this->error = "Error ".$this->db->lasterror();
2279  dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2280 
2281  return -1;
2282  }
2283  }
2284 
2285  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2292  public function get_compte_desc($account = null)
2293  {
2294  // phpcs:enable
2295  global $conf;
2296 
2297  $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2298  $sql = "SELECT aa.account_number, aa.label, aa.rowid, aa.fk_pcg_version, cat.label as category";
2299  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa ";
2300  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2301  $sql .= " AND aa.account_number = '".$this->db->escape($account)."'";
2302  $sql .= " AND asy.rowid = ".((int) $pcgver);
2303  $sql .= " AND aa.active = 1";
2304  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_accounting_category as cat ON aa.fk_accounting_category = cat.rowid";
2305  $sql .= " WHERE aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2306 
2307  dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2308  $resql = $this->db->query($sql);
2309  if ($resql) {
2310  $obj = '';
2311  if ($this->db->num_rows($resql)) {
2312  $obj = $this->db->fetch_object($resql);
2313  }
2314  if (empty($obj->category)) {
2315  return $obj->label;
2316  } else {
2317  return $obj->label.' ('.$obj->category.')';
2318  }
2319  } else {
2320  $this->error = "Error ".$this->db->lasterror();
2321  dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2322  return "-1";
2323  }
2324  }
2325 
2333  public function getCanModifyBookkeepingSQL($alias = '', $force = false)
2334  {
2335  global $conf;
2336 
2337  $alias = trim($alias);
2338  $alias = !empty($alias) && strpos($alias, '.') < 0 ? $alias . "." : $alias;
2339 
2340  if (!isset(self::$can_modify_bookkeeping_sql_cached[$alias]) || $force) {
2341  $result = $this->loadFiscalPeriods($force, 'active');
2342  if ($result < 0) {
2343  return null;
2344  }
2345 
2346  $sql_list = array();
2347  if (!empty($conf->cache['active_fiscal_period_cached']) && is_array($conf->cache['active_fiscal_period_cached'])) {
2348  foreach ($conf->cache['active_fiscal_period_cached'] as $fiscal_period) {
2349  $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']) . "')";
2350  }
2351  }
2352  $sqlsanitized = implode(' OR ', $sql_list);
2353  self::$can_modify_bookkeeping_sql_cached[$alias] = !empty($sql_list) ? " AND (".$sqlsanitized.")" : "";
2354  }
2355 
2356  return self::$can_modify_bookkeeping_sql_cached[$alias];
2357  }
2358 
2366  public function canModifyBookkeeping($id, $mode = '')
2367  {
2368  global $conf;
2369 
2370  if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
2371  $result = $this->loadFiscalPeriods(false, 'closed');
2372 
2373  if ($result < 0) {
2374  return -1;
2375  }
2376 
2377  $bookkeeping = new BookKeeping($this->db);
2378  $result = $bookkeeping->fetch($id, null, $mode);
2379  if ($result <= 0) {
2380  return $result;
2381  }
2382 
2383  if (!empty($conf->cache['closed_fiscal_period_cached']) && is_array($conf->cache['closed_fiscal_period_cached'])) {
2384  foreach ($conf->cache['closed_fiscal_period_cached'] as $fiscal_period) {
2385  if ($fiscal_period['date_start'] <= $bookkeeping->doc_date && $bookkeeping->doc_date <= $fiscal_period['date_end']) {
2386  return 0;
2387  }
2388  }
2389  }
2390 
2391  return 1;
2392  } else {
2393  $result = $this->loadFiscalPeriods(false, 'active');
2394  if ($result < 0) {
2395  return -1;
2396  }
2397 
2398  $bookkeeping = new BookKeeping($this->db);
2399  $result = $bookkeeping->fetch($id, null, $mode);
2400 
2401  if ($result <= 0) {
2402  return $result;
2403  }
2404  if (!empty($conf->cache['active_fiscal_period_cached']) && is_array($conf->cache['active_fiscal_period_cached'])) {
2405  foreach ($conf->cache['active_fiscal_period_cached'] as $fiscal_period) {
2406  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'])) {
2407  return 1;
2408  }
2409  }
2410  }
2411 
2412  return 0;
2413  }
2414  }
2415 
2422  public function validBookkeepingDate($date)
2423  {
2424  global $conf;
2425 
2426  if (getDolGlobalString('ACCOUNTANCY_FISCAL_PERIOD_MODE') == 'blockedonclosed') {
2427  $result = $this->loadFiscalPeriods(false, 'closed');
2428 
2429  if ($result < 0) {
2430  return -1;
2431  }
2432 
2433  if (!empty($conf->cache['closed_fiscal_period_cached']) && is_array($conf->cache['closed_fiscal_period_cached'])) {
2434  foreach ($conf->cache['closed_fiscal_period_cached'] as $fiscal_period) {
2435  if ($fiscal_period['date_start'] <= $date && $date <= $fiscal_period['date_end']) {
2436  return 0;
2437  }
2438  }
2439  }
2440 
2441  return 1;
2442  } else {
2443  $result = $this->loadFiscalPeriods(false, 'active');
2444  if ($result < 0) {
2445  return -1;
2446  }
2447 
2448  if (!empty($conf->cache['active_fiscal_period_cached']) && is_array($conf->cache['active_fiscal_period_cached'])) {
2449  foreach ($conf->cache['active_fiscal_period_cached'] as $fiscal_period) {
2450  if (!empty($fiscal_period['date_start']) && $fiscal_period['date_start'] <= $date && (empty($fiscal_period['date_end']) || $date <= $fiscal_period['date_end'])) {
2451  return 1;
2452  }
2453  }
2454  }
2455 
2456  return 0;
2457  }
2458  }
2459 
2467  public function loadFiscalPeriods($force = false, $mode = 'active')
2468  {
2469  global $conf;
2470 
2471  if ($mode == 'active') {
2472  if (!isset($conf->cache['active_fiscal_period_cached']) || $force) {
2473  $sql = "SELECT date_start, date_end";
2474  $sql .= " FROM " . $this->db->prefix() . "accounting_fiscalyear";
2475  $sql .= " WHERE entity = " . ((int) $conf->entity);
2476  $sql .= " AND statut = 0";
2477 
2478  $resql = $this->db->query($sql);
2479  if (!$resql) {
2480  $this->errors[] = $this->db->lasterror();
2481  return -1;
2482  }
2483 
2484  $list = array();
2485  while ($obj = $this->db->fetch_object($resql)) {
2486  $list[] = array(
2487  'date_start' => $this->db->jdate($obj->date_start),
2488  'date_end' => $this->db->jdate($obj->date_end),
2489  );
2490  }
2491  $conf->cache['active_fiscal_period_cached'] = $list;
2492  }
2493  }
2494  if ($mode == 'closed') {
2495  if (!isset($conf->cache['closed_fiscal_period_cached']) || $force) {
2496  $sql = "SELECT date_start, date_end";
2497  $sql .= " FROM " . $this->db->prefix() . "accounting_fiscalyear";
2498  $sql .= " WHERE entity = " . ((int) $conf->entity);
2499  $sql .= " AND statut = 1";
2500 
2501  $resql = $this->db->query($sql);
2502  if (!$resql) {
2503  $this->errors[] = $this->db->lasterror();
2504  return -1;
2505  }
2506 
2507  $list = array();
2508  while ($obj = $this->db->fetch_object($resql)) {
2509  $list[] = array(
2510  'date_start' => $this->db->jdate($obj->date_start),
2511  'date_end' => $this->db->jdate($obj->date_end),
2512  );
2513  }
2514  $conf->cache['closed_fiscal_period_cached'] = $list;
2515  }
2516  }
2517 
2518  return 1;
2519  }
2520 
2527  public function getFiscalPeriods($filter = '')
2528  {
2529  global $conf;
2530  $list = array();
2531 
2532  $sql = "SELECT rowid, label, date_start, date_end, statut";
2533  $sql .= " FROM " . $this->db->prefix() . "accounting_fiscalyear";
2534  $sql .= " WHERE entity = " . ((int) $conf->entity);
2535  if (!empty($filter)) {
2536  $sql .= " AND (" . $this->db->sanitize($filter, 1, 1, 1) . ')';
2537  }
2538  $sql .= $this->db->order('date_start', 'ASC');
2539 
2540  $resql = $this->db->query($sql);
2541  if (!$resql) {
2542  $this->errors[] = $this->db->lasterror();
2543  return -1;
2544  }
2545 
2546  while ($obj = $this->db->fetch_object($resql)) {
2547  $list[$obj->rowid] = array(
2548  'id' => (int) $obj->rowid,
2549  'label' => $obj->label,
2550  'date_start' => $this->db->jdate($obj->date_start),
2551  'date_end' => $this->db->jdate($obj->date_end),
2552  'status' => (int) $obj->statut,
2553  );
2554  }
2555 
2556  return $list;
2557  }
2558 
2566  public function getCountByMonthForFiscalPeriod($date_start, $date_end)
2567  {
2568  $total = 0;
2569  $list = array();
2570 
2571  $sql = "SELECT YEAR(b.doc_date) as year";
2572  for ($i = 1; $i <= 12; $i++) {
2573  $sql .= ", SUM(".$this->db->ifsql("MONTH(b.doc_date) = ".((int) $i), "1", "0") . ") AS month".((int) $i);
2574  }
2575  $sql .= ", COUNT(b.rowid) as total";
2576  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as b";
2577  $sql .= " WHERE b.doc_date >= '" . $this->db->idate($date_start) . "'";
2578  $sql .= " AND b.doc_date <= '" . $this->db->idate($date_end) . "'";
2579  $sql .= " AND b.entity IN (" . getEntity('bookkeeping', 0) . ")"; // We don't share object for accountancy
2580 
2581  // Get count for each month into the fiscal period
2582  if (getDolGlobalString("ACCOUNTANCY_DISABLE_CLOSURE_LINE_BY_LINE")) {
2583  // TODO Analyse is done by finding record not into a closed period
2584  // Loop on each closed period
2585  $sql .= " AND b.doc_date BETWEEN 0 AND 0";
2586  } else {
2587  // Analyse closed record using the unitary flag/date on each record
2588  $sql .= " AND date_validated IS NULL";
2589  }
2590 
2591  $sql .= " GROUP BY YEAR(b.doc_date)";
2592  $sql .= $this->db->order("year", 'ASC');
2593 
2594  dol_syslog(__METHOD__, LOG_DEBUG);
2595  $resql = $this->db->query($sql);
2596  if (!$resql) {
2597  $this->errors[] = $this->db->lasterror();
2598  return -1;
2599  }
2600 
2601  while ($obj = $this->db->fetch_object($resql)) {
2602  $total += (int) $obj->total;
2603  $year_list = array(
2604  'year' => (int) $obj->year,
2605  'count' => array(),
2606  'total' => (int) $obj->total,
2607  );
2608  for ($i = 1; $i <= 12; $i++) {
2609  $year_list['count'][$i] = (int) $obj->{'month' . $i};
2610  }
2611 
2612  $list[] = $year_list;
2613  }
2614 
2615  $this->db->free($resql);
2616 
2617  return array(
2618  'total' => $total,
2619  'list' => $list,
2620  );
2621  }
2622 
2630  public function validateMovementForFiscalPeriod($date_start, $date_end)
2631  {
2632  global $conf;
2633 
2634  $now = dol_now();
2635 
2636  // Specify as export : update field date_validated on selected month/year
2637  $sql = " UPDATE " . MAIN_DB_PREFIX . "accounting_bookkeeping";
2638  $sql .= " SET date_validated = '" . $this->db->idate($now) . "'";
2639  $sql .= " WHERE entity = " . ((int) $conf->entity);
2640  $sql .= " AND DATE(doc_date) >= '" . $this->db->idate($date_start) . "'";
2641  $sql .= " AND DATE(doc_date) <= '" . $this->db->idate($date_end) . "'";
2642  $sql .= " AND date_validated IS NULL";
2643 
2644  dol_syslog(__METHOD__, LOG_DEBUG);
2645  $resql = $this->db->query($sql);
2646  if (!$resql) {
2647  $this->errors[] = $this->db->lasterror();
2648  return -1;
2649  }
2650 
2651  return 1;
2652  }
2653 
2661  public function accountingResult($date_start, $date_end)
2662  {
2663  global $conf;
2664 
2665  $this->db->begin();
2666 
2667  $income_statement_amount = 0;
2668 
2669  if (getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_INCOME_STATEMENT')) {
2670  $accounting_groups_used_for_income_statement = array_filter(array_map('trim', explode(',', getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_INCOME_STATEMENT'))), 'strlen');
2671 
2672  $pcg_type_filter = array();
2673  foreach ($accounting_groups_used_for_income_statement as $item) {
2674  $pcg_type_filter[] = "'" . $this->db->escape($item) . "'";
2675  }
2676 
2677  $sql = 'SELECT';
2678  $sql .= " t.numero_compte,";
2679  $sql .= " aa.pcg_type,";
2680  $sql .= " (SUM(t.credit) - SUM(t.debit)) as accounting_result";
2681  $sql .= ' FROM ' . MAIN_DB_PREFIX . $this->table_element . ' as t';
2682  $sql .= ' LEFT JOIN ' . MAIN_DB_PREFIX . 'accounting_account as aa ON aa.account_number = t.numero_compte';
2683  $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
2684  $sql .= " AND aa.entity = " . ((int) $conf->entity);
2685  $sql .= ' AND aa.fk_pcg_version IN (SELECT pcg_version FROM ' . MAIN_DB_PREFIX . 'accounting_system WHERE rowid = ' . ((int) getDolGlobalInt('CHARTOFACCOUNTS')) . ')';
2686  $sql .= ' AND aa.pcg_type IN (' . $this->db->sanitize(implode(',', $pcg_type_filter), 1) . ')';
2687  $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($date_start) . "'";
2688  $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($date_end) . "'";
2689  $sql .= ' GROUP BY t.numero_compte, aa.pcg_type';
2690 
2691  $resql = $this->db->query($sql);
2692  if (!$resql) {
2693  $this->errors[] = 'Error ' . $this->db->lasterror();
2694  dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
2695  } else {
2696  while ($obj = $this->db->fetch_object($resql)) {
2697  $income_statement_amount += $obj->accounting_result;
2698  }
2699  }
2700  }
2701 
2702  return (string) $income_statement_amount;
2703  }
2704 
2714  public function closeFiscalPeriod($fiscal_period_id, $new_fiscal_period_id, $separate_auxiliary_account = false, $generate_bookkeeping_records = true)
2715  {
2716  global $conf, $langs, $user;
2717 
2718  // Current fiscal period
2719  $fiscal_period_id = max(0, $fiscal_period_id);
2720  if (empty($fiscal_period_id)) {
2721  $langs->load('errors');
2722  $this->errors[] = $langs->trans('ErrorBadParameters');
2723  return -1;
2724  }
2725  $fiscal_period = new Fiscalyear($this->db);
2726  $result = $fiscal_period->fetch($fiscal_period_id);
2727  if ($result < 0) {
2728  $this->error = $fiscal_period->error;
2729  $this->errors = $fiscal_period->errors;
2730  return -1;
2731  } elseif (empty($fiscal_period->id)) {
2732  $langs->loadLangs(array('errors', 'compta'));
2733  $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $fiscal_period_id . ')';
2734  return -1;
2735  }
2736 
2737  // New fiscal period
2738  $new_fiscal_period_id = max(0, $new_fiscal_period_id);
2739  if (empty($new_fiscal_period_id)) {
2740  $langs->load('errors');
2741  $this->errors[] = $langs->trans('ErrorBadParameters');
2742  return -1;
2743  }
2744  $new_fiscal_period = new Fiscalyear($this->db);
2745  $result = $new_fiscal_period->fetch($new_fiscal_period_id);
2746  if ($result < 0) {
2747  $this->error = $new_fiscal_period->error;
2748  $this->errors = $new_fiscal_period->errors;
2749  return -1;
2750  } elseif (empty($new_fiscal_period->id)) {
2751  $langs->loadLangs(array('errors', 'compta'));
2752  $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $new_fiscal_period_id . ')';
2753  return -1;
2754  }
2755 
2756  $error = 0;
2757  $this->db->begin();
2758 
2759  $fiscal_period->statut = Fiscalyear::STATUS_CLOSED;
2760  $fiscal_period->status = Fiscalyear::STATUS_CLOSED; // Actually not used
2761  $result = $fiscal_period->update($user);
2762  if ($result < 0) {
2763  $this->error = $fiscal_period->error;
2764  $this->errors = $fiscal_period->errors;
2765  $error++;
2766  }
2767 
2768  if (!$error && !empty($generate_bookkeeping_records)) {
2769  $journal_id = max(0, getDolGlobalString('ACCOUNTING_CLOSURE_DEFAULT_JOURNAL'));
2770  if (empty($journal_id)) {
2771  $langs->loadLangs(array('errors', 'accountancy'));
2772  $this->errors[] = $langs->trans('ErrorBadParameters') . ' - ' . $langs->trans('Codejournal') . ' (' . $langs->trans('AccountingJournalType9') . ')';
2773  $error++;
2774  }
2775 
2776  // Fetch journal
2777  if (!$error) {
2778  $journal = new AccountingJournal($this->db);
2779  $result = $journal->fetch($journal_id);
2780  if ($result < 0) {
2781  $this->error = $journal->error;
2782  $this->errors = $journal->errors;
2783  $error++;
2784  } elseif ($result == 0) {
2785  $langs->loadLangs(array('errors', 'accountancy'));
2786  $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('Codejournal') . ' (' . $langs->trans('AccountingJournalType9') . ')';
2787  $error++;
2788  }
2789  }
2790 
2791  if (!$error) {
2792  $accounting_groups_used_for_balance_sheet_account = array_filter(array_map('trim', explode(',', getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_BALANCE_SHEET_ACCOUNT'))), 'strlen');
2793  $accounting_groups_used_for_income_statement = array_filter(array_map('trim', explode(',', getDolGlobalString('ACCOUNTING_CLOSURE_ACCOUNTING_GROUPS_USED_FOR_INCOME_STATEMENT'))), 'strlen');
2794 
2795  $pcg_type_filter = array();
2796  $tmp = array_merge($accounting_groups_used_for_balance_sheet_account, $accounting_groups_used_for_income_statement);
2797  foreach ($tmp as $item) {
2798  $pcg_type_filter[] = "'" . $this->db->escape($item) . "'";
2799  }
2800 
2801  $sql = 'SELECT';
2802  $sql .= " t.numero_compte,";
2803  $sql .= " t.label_compte,";
2804  if ($separate_auxiliary_account) {
2805  $sql .= " t.subledger_account,";
2806  $sql .= " t.subledger_label,";
2807  }
2808  $sql .= " aa.pcg_type,";
2809  $sql .= " (SUM(t.credit) - SUM(t.debit)) as opening_balance";
2810  $sql .= ' FROM ' . MAIN_DB_PREFIX . $this->table_element . ' as t';
2811  $sql .= ' LEFT JOIN ' . MAIN_DB_PREFIX . 'accounting_account as aa ON aa.account_number = t.numero_compte';
2812  $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
2813  $sql .= " AND aa.entity = ". ((int) $conf->entity);
2814  $sql .= ' AND aa.fk_pcg_version IN (SELECT pcg_version FROM '.MAIN_DB_PREFIX.'accounting_system WHERE rowid = '.((int) getDolGlobalInt('CHARTOFACCOUNTS')).')';
2815  $sql .= ' AND aa.pcg_type IN (' . $this->db->sanitize(implode(',', $pcg_type_filter), 1) . ')';
2816  $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($fiscal_period->date_start) . "'";
2817  $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($fiscal_period->date_end) . "'";
2818  $sql .= ' GROUP BY t.numero_compte, t.label_compte, aa.pcg_type';
2819  if ($separate_auxiliary_account) {
2820  $sql .= ' ,t.subledger_account, t.subledger_label';
2821  }
2822  $sql .= $this->db->order("t.numero_compte", "ASC");
2823 
2824  $resql = $this->db->query($sql);
2825  if (!$resql) {
2826  $this->errors[] = 'Error ' . $this->db->lasterror();
2827  dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
2828 
2829  $error++;
2830  } else {
2831  $now = dol_now();
2832  $income_statement_amount = 0;
2833  while ($obj = $this->db->fetch_object($resql)) {
2834  if (in_array($obj->pcg_type, $accounting_groups_used_for_income_statement)) {
2835  $income_statement_amount += $obj->opening_balance;
2836  } else {
2837  // Insert bookkeeping record for balance sheet account
2838  $mt = $obj->opening_balance;
2839 
2840  $bookkeeping = new BookKeeping($this->db);
2841  $bookkeeping->doc_date = $new_fiscal_period->date_start;
2842  $bookkeeping->date_lim_reglement = 0;
2843  $bookkeeping->doc_ref = $new_fiscal_period->label;
2844  $bookkeeping->date_creation = $now;
2845  $bookkeeping->doc_type = 'closure';
2846  $bookkeeping->fk_doc = $new_fiscal_period->id;
2847  $bookkeeping->fk_docdet = 0; // Useless, can be several lines that are source of this record to add
2848  $bookkeeping->thirdparty_code = '';
2849 
2850  if ($separate_auxiliary_account) {
2851  $bookkeeping->subledger_account = $obj->subledger_account;
2852  $bookkeeping->subledger_label = $obj->subledger_label;
2853  } else {
2854  $bookkeeping->subledger_account = '';
2855  $bookkeeping->subledger_label = '';
2856  }
2857 
2858  $bookkeeping->numero_compte = $obj->numero_compte;
2859  $bookkeeping->label_compte = $obj->label_compte;
2860 
2861  $bookkeeping->label_operation = $new_fiscal_period->label;
2862  $bookkeeping->montant = $mt;
2863  $bookkeeping->sens = ($mt >= 0) ? 'C' : 'D';
2864  $bookkeeping->debit = ($mt < 0) ? -$mt : 0;
2865  $bookkeeping->credit = ($mt >= 0) ? $mt : 0;
2866  $bookkeeping->code_journal = $journal->code;
2867  $bookkeeping->journal_label = $langs->transnoentities($journal->label);
2868  $bookkeeping->fk_user_author = $user->id;
2869  $bookkeeping->entity = $conf->entity;
2870 
2871  $result = $bookkeeping->create($user);
2872  if ($result < 0) {
2873  $this->error = $bookkeeping->error;
2874  $this->errors = $bookkeeping->errors;
2875  $error++;
2876  break;
2877  }
2878  }
2879  }
2880 
2881  // Insert bookkeeping record for income statement
2882  if (!$error && $income_statement_amount != 0) {
2883  $mt = $income_statement_amount;
2884  $accountingaccount = new AccountingAccount($this->db);
2885  $accountingaccount->fetch(null, getDolGlobalString($income_statement_amount < 0 ? 'ACCOUNTING_RESULT_LOSS' : 'ACCOUNTING_RESULT_PROFIT'), true);
2886 
2887  $bookkeeping = new BookKeeping($this->db);
2888  $bookkeeping->doc_date = $new_fiscal_period->date_start;
2889  $bookkeeping->date_lim_reglement = 0;
2890  $bookkeeping->doc_ref = $new_fiscal_period->label;
2891  $bookkeeping->date_creation = $now;
2892  $bookkeeping->doc_type = 'closure';
2893  $bookkeeping->fk_doc = $new_fiscal_period->id;
2894  $bookkeeping->fk_docdet = 0; // Useless, can be several lines that are source of this record to add
2895  $bookkeeping->thirdparty_code = '';
2896 
2897  if ($separate_auxiliary_account) {
2898  $bookkeeping->subledger_label = '';
2899  $bookkeeping->subledger_account = $obj->subledger_account;
2900  $bookkeeping->subledger_label = $obj->subledger_label;
2901  } else {
2902  $bookkeeping->subledger_account = '';
2903  $bookkeeping->subledger_label = '';
2904  }
2905 
2906  $bookkeeping->numero_compte = $accountingaccount->account_number;
2907  $bookkeeping->label_compte = $accountingaccount->label;
2908 
2909  $bookkeeping->label_operation = $new_fiscal_period->label;
2910  $bookkeeping->montant = $mt;
2911  $bookkeeping->sens = ($mt >= 0) ? 'C' : 'D';
2912  $bookkeeping->debit = ($mt < 0) ? -$mt : 0;
2913  $bookkeeping->credit = ($mt >= 0) ? $mt : 0;
2914  $bookkeeping->code_journal = $journal->code;
2915  $bookkeeping->journal_label = $langs->transnoentities($journal->label);
2916  $bookkeeping->fk_user_author = $user->id;
2917  $bookkeeping->entity = $conf->entity;
2918 
2919  $result = $bookkeeping->create($user);
2920  if ($result < 0) {
2921  $this->error = $bookkeeping->error;
2922  $this->errors = $bookkeeping->errors;
2923  $error++;
2924  }
2925  }
2926  $this->db->free($resql);
2927  }
2928  }
2929  }
2930 
2931  if ($error) {
2932  $this->db->rollback();
2933  return -1;
2934  } else {
2935  $this->db->commit();
2936  return 1;
2937  }
2938  }
2939 
2950  public function insertAccountingReversal($fiscal_period_id, $inventory_journal_id, $new_fiscal_period_id, $date_start, $date_end)
2951  {
2952  global $conf, $langs, $user;
2953 
2954  // Current fiscal period
2955  $fiscal_period_id = max(0, $fiscal_period_id);
2956  if (empty($fiscal_period_id)) {
2957  $langs->load('errors');
2958  $this->errors[] = $langs->trans('ErrorBadParameters');
2959  return -1;
2960  }
2961  $fiscal_period = new Fiscalyear($this->db);
2962  $result = $fiscal_period->fetch($fiscal_period_id);
2963  if ($result < 0) {
2964  $this->error = $fiscal_period->error;
2965  $this->errors = $fiscal_period->errors;
2966  return -1;
2967  } elseif (empty($fiscal_period->id)) {
2968  $langs->loadLangs(array('errors', 'compta'));
2969  $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $fiscal_period_id . ')';
2970  return -1;
2971  }
2972 
2973  // New fiscal period
2974  $new_fiscal_period_id = max(0, $new_fiscal_period_id);
2975  if (empty($new_fiscal_period_id)) {
2976  $langs->load('errors');
2977  $this->errors[] = $langs->trans('ErrorBadParameters');
2978  return -1;
2979  }
2980  $new_fiscal_period = new Fiscalyear($this->db);
2981  $result = $new_fiscal_period->fetch($new_fiscal_period_id);
2982  if ($result < 0) {
2983  $this->error = $new_fiscal_period->error;
2984  $this->errors = $new_fiscal_period->errors;
2985  return -1;
2986  } elseif (empty($new_fiscal_period->id)) {
2987  $langs->loadLangs(array('errors', 'compta'));
2988  $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('FiscalPeriod') . ' (' . $new_fiscal_period_id . ')';
2989  return -1;
2990  }
2991 
2992  // Inventory journal
2993  $inventory_journal_id = max(0, $inventory_journal_id);
2994  if (empty($inventory_journal_id)) {
2995  $langs->load('errors');
2996  $this->errors[] = $langs->trans('ErrorBadParameters');
2997  return -1;
2998  }
2999  // Fetch journal
3000  $inventory_journal = new AccountingJournal($this->db);
3001  $result = $inventory_journal->fetch($inventory_journal_id);
3002  if ($result < 0) {
3003  $this->error = $inventory_journal->error;
3004  $this->errors = $inventory_journal->errors;
3005  return -1;
3006  } elseif ($result == 0) {
3007  $langs->loadLangs(array('errors', 'accountancy'));
3008  $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('InventoryJournal');
3009  return -1;
3010  }
3011 
3012  $error = 0;
3013  $this->db->begin();
3014 
3015  $sql = 'SELECT t.rowid';
3016  $sql .= ' FROM ' . MAIN_DB_PREFIX . $this->table_element . ' as t';
3017  $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
3018  $sql .= " AND code_journal = '" . $this->db->escape($inventory_journal->code) . "'";
3019  $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($date_start) . "'";
3020  $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($date_end) . "'";
3021  $sql .= " AND DATE(t.doc_date) >= '" . $this->db->idate($fiscal_period->date_start) . "'";
3022  $sql .= " AND DATE(t.doc_date) <= '" . $this->db->idate($fiscal_period->date_end) . "'";
3023 
3024  $resql = $this->db->query($sql);
3025  if (!$resql) {
3026  $this->errors[] = 'Error ' . $this->db->lasterror();
3027  dol_syslog(__METHOD__ . ' ' . implode(',', $this->errors), LOG_ERR);
3028 
3029  $error++;
3030  } else {
3031  $now = dol_now();
3032  while ($obj = $this->db->fetch_object($resql)) {
3033  $bookkeeping = new BookKeeping($this->db);
3034  $result = $bookkeeping->fetch($obj->rowid);
3035  if ($result < 0) {
3036  $this->error = $inventory_journal->error;
3037  $this->errors = $inventory_journal->errors;
3038  $error++;
3039  break;
3040  } elseif ($result == 0) {
3041  $langs->loadLangs(array('errors', 'accountancy'));
3042  $this->errors[] = $langs->trans('ErrorRecordNotFound') . ' - ' . $langs->trans('LineId') . ': ' . $obj->rowid;
3043  $error++;
3044  break;
3045  }
3046 
3047  $bookkeeping->id = 0;
3048  $bookkeeping->doc_date = $new_fiscal_period->date_start;
3049  $bookkeeping->doc_ref = $new_fiscal_period->label;
3050  $bookkeeping->date_creation = $now;
3051  $bookkeeping->doc_type = 'accounting_reversal';
3052  $bookkeeping->fk_doc = $new_fiscal_period->id;
3053  $bookkeeping->fk_docdet = 0; // Useless, can be several lines that are source of this record to add
3054 
3055  $bookkeeping->montant = -$bookkeeping->montant;
3056  $bookkeeping->sens = ($bookkeeping->montant >= 0) ? 'C' : 'D';
3057  $old_debit = $bookkeeping->debit;
3058  $bookkeeping->debit = $bookkeeping->credit;
3059  $bookkeeping->credit = $old_debit;
3060 
3061  $bookkeeping->fk_user_author = $user->id;
3062  $bookkeeping->entity = $conf->entity;
3063 
3064  $result = $bookkeeping->create($user);
3065  if ($result < 0) {
3066  $this->error = $bookkeeping->error;
3067  $this->errors = $bookkeeping->errors;
3068  $error++;
3069  break;
3070  }
3071  }
3072  $this->db->free($resql);
3073  }
3074 
3075  if ($error) {
3076  $this->db->rollback();
3077  return -1;
3078  } else {
3079  $this->db->commit();
3080  return 1;
3081  }
3082  }
3083 }
3084 
3089 {
3093  public $id;
3094 
3095  public $doc_date = null;
3096  public $doc_type;
3097  public $doc_ref;
3098 
3102  public $fk_doc;
3103 
3107  public $fk_docdet;
3108 
3109  public $thirdparty_code;
3110  public $subledger_account;
3111  public $subledger_label;
3112  public $numero_compte;
3113  public $label_compte;
3114  public $label_operation;
3115  public $debit;
3116  public $credit;
3117 
3122  public $montant;
3123 
3127  public $amount;
3128 
3132  public $multicurrency_amount;
3133 
3137  public $multicurrency_code;
3138 
3142  public $sens;
3143  public $lettering_code;
3144  public $date_lettering;
3145 
3149  public $fk_user_author;
3150 
3151  public $import_key;
3152  public $code_journal;
3153  public $journal_label;
3157  public $piece_num;
3158 
3162  public $date_creation;
3163 
3167  public $date_modification;
3168 
3172  public $date_export;
3173 
3177  public $date_validation;
3178 
3182  public $date_lim_reglement;
3183 }
if($user->socid > 0) if(! $user->hasRight('accounting', 'chartofaccount')) $object
Definition: card.php:58
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous)
ajax_combobox($htmlname, $events=array(), $minLengthToAutocomplete=0, $forcefocus=0, $widthTypeOfAutocomplete='resolve', $idforemptyvalue='-1', $morecss='')
Convert a html select field into an ajax combobox.
Definition: ajax.lib.php:463
Class to manage accounting accounts.
Class to manage accounting journals.
Class to manage Ledger (General Ledger and Subledger)
closeFiscalPeriod($fiscal_period_id, $new_fiscal_period_id, $separate_auxiliary_account=false, $generate_bookkeeping_records=true)
Close fiscal period.
getCountByMonthForFiscalPeriod($date_start, $date_end)
Get list of count by month into the fiscal period.
fetchAllBalance($sortorder='', $sortfield='', $limit=0, $offset=0, $filter='', $filtermode='AND', $option=0)
Load object in memory from the database.
getNomUrl($withpicto=0, $option='', $notooltip=0, $morecss='', $save_lastsearch_value=-1)
Return a link to the object card (with optionally the picto)
select_account($selectid, $htmlname='account', $showempty=0, $event=array(), $select_in=0, $select_out=0, $aabase='')
Return list of accounts with label by chart of accounts.
getNextNumMvt($mode='')
Return next movement number.
get_compte_desc($account=null)
Description of accounting account.
__construct(DoliDB $db)
Constructor.
createFromClone(User $user, $fromid)
Load an object from its id and create a new one in database.
deleteByImportkey($importkey, $mode='')
Delete bookkeeping by importkey.
getRootAccount($account=null)
Return id and description of a root accounting account.
transformTransaction($direction=0, $piece_num='')
Transform transaction.
insertAccountingReversal($fiscal_period_id, $inventory_journal_id, $new_fiscal_period_id, $date_start, $date_end)
Insert accounting reversal into the inventory journal of the new fiscal period.
fetchPerMvt($piecenum, $mode='')
Load an accounting document into memory from database.
create(User $user, $notrigger=0)
Create object into database.
updateByMvt($piece_num='', $field='', $value='', $mode='')
Update accounting movement.
createStd(User $user, $notrigger=0, $mode='')
Create object into database.
update(User $user, $notrigger=0, $mode='')
Update object into database.
deleteMvtNum($piecenum, $mode='')
Delete bookkeeping by piece number.
getFiscalPeriods($filter='')
Get list of fiscal period.
validBookkeepingDate($date)
Is the bookkeeping date valid (on an open period or not on a closed period) ?
deleteByYearAndJournal($delyear=0, $journal='', $mode='', $delmonth=0)
Delete bookkeeping by year.
loadFiscalPeriods($force=false, $mode='active')
Load list of active fiscal period.
fetchAllByAccount($sortorder='', $sortfield='', $limit=0, $offset=0, array $filter=array(), $filtermode='AND', $option=0, $countonly=0)
Load object in memory from the database in ->lines.
initAsSpecimen()
Initialise object with example values Id must be 0 if object instance is a specimen.
getCanModifyBookkeepingSQL($alias='', $force=false)
Get SQL string for check if the bookkeeping can be modified or deleted ? (cached)
fetch($id, $ref=null, $mode='')
Load object in memory from the database.
fetchAll($sortorder='', $sortfield='', $limit=0, $offset=0, $filter='', $filtermode='AND', $showAlreadyExportMovements=1)
Load object in memory from the database.
accountingResult($date_start, $date_end)
Define accounting result.
canModifyBookkeeping($id, $mode='')
Is the bookkeeping can be modified or deleted ?
fetchAllPerMvt($piecenum, $mode='')
Load all accounting lines related to a given transaction ID $piecenum.
validateMovementForFiscalPeriod($date_start, $date_end)
Validate all movement between the specified dates.
export_bookkeeping($model='ebp')
Export bookkeeping.
Class BookKeepingLine.
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Parent class for class inheritance lines of business objects This class is useless for the moment so ...
Class to manage Dolibarr database access.
Class to manage fiscal year.
static selectarray($htmlname, $array, $id='', $show_empty=0, $key_in_label=0, $value_as_key=0, $moreparam='', $translate=0, $maxlen=0, $disabled=0, $sort='', $morecss='minwidth75', $addjscombo=1, $moreparamonempty='', $disablebademail=0, $nohtmlescape=0)
Return a HTML select string, built from an array of key+value.
Class to manage Dolibarr users.
Definition: user.class.php:50
if(isModEnabled('invoice') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&!getDolGlobalString('MAIN_USE_NEW_SUPPLIERMOD') && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') && $user->hasRight('don', 'lire')) if(isModEnabled('tax') && $user->hasRight('tax', 'charges', 'lire')) if(isModEnabled('invoice') &&isModEnabled('order') && $user->hasRight("commande", "lire") &&!getDolGlobalString('WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER')) $sql
Social contributions to pay.
Definition: index.php:744
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($titlealt, $picto, $moreatt='', $pictoisfullpath=0, $srconly=0, $notitle=0)
Show a picto called object_picto (generic function)
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
natural_search($fields, $value, $mode=0, $nofirstand=0)
Generate natural SQL search string for a criteria (this criteria can be tested on one or several fiel...
dol_strlen($string, $stringencoding='UTF-8')
Make a strlen call.
forgeSQLFromUniversalSearchCriteria($filter, &$errorstr='', $noand=0, $nopar=0, $noerror=0)
forgeSQLFromUniversalSearchCriteria
dol_now($mode='auto')
Return date for now.
getDolGlobalInt($key, $default=0)
Return a Dolibarr global constant int value.
getDolGlobalString($key, $default='')
Return dolibarr global constant string value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
getEntity($element, $shared=1, $currentobject=null)
Get list of entity id to use.
td amount
Definition: global.inc.php:554
div float
Buy price without taxes.
Definition: style.css.php:959