dolibarr  19.0.0-dev
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-2020 Frédéric France <frederic.france@netlogic.fr>
6  *
7  * This program is free software; you can redistribute it and/or modify
8  * it under the terms of the GNU General Public License as published by
9  * the Free Software Foundation; either version 3 of the License, or
10  * (at your option) any later version.
11  *
12  * This program is distributed in the hope that it will be useful,
13  * but WITHOUT ANY WARRANTY; without even the implied warranty of
14  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15  * GNU General Public License for more details.
16  *
17  * You should have received a copy of the GNU General Public License
18  * along with this program. If not, see <https://www.gnu.org/licenses/>.
19  */
20 
27 // Class
28 require_once DOL_DOCUMENT_ROOT.'/core/class/commonobject.class.php';
29 
33 class BookKeeping extends CommonObject
34 {
38  public $element = 'accountingbookkeeping';
39 
43  public $table_element = 'accounting_bookkeeping';
44 
48  public $entity;
49 
53  public $lines = array();
54 
58  public $id;
59 
63  public $doc_date;
64 
68  public $date_lim_reglement;
69 
73  public $doc_type;
74 
78  public $doc_ref;
79 
83  public $fk_doc;
84 
88  public $fk_docdet;
89 
93  public $thirdparty_code;
94 
98  public $subledger_account;
99 
103  public $subledger_label;
104 
108  public $numero_compte;
109 
113  public $label_compte;
114 
118  public $label_operation;
119 
123  public $debit;
124 
128  public $credit;
129 
134  public $montant;
135 
140  public $amount;
141 
145  public $sens;
146 
150  public $fk_user_author;
151 
155  public $import_key;
156 
160  public $code_journal;
161 
165  public $journal_label;
166 
170  public $piece_num;
171 
175  public $date_validation;
176 
180  public $date_export;
181 
185  public $picto = 'generic';
186 
187 
193  public function __construct(DoliDB $db)
194  {
195  $this->db = $db;
196  }
197 
205  public function create(User $user, $notrigger = false)
206  {
207  global $conf, $langs;
208 
209  dol_syslog(__METHOD__, LOG_DEBUG);
210 
211  $error = 0;
212 
213  // Clean parameters</center>
214  if (isset($this->doc_type)) {
215  $this->doc_type = trim($this->doc_type);
216  }
217  if (isset($this->doc_ref)) {
218  $this->doc_ref = trim($this->doc_ref);
219  }
220  if (isset($this->fk_doc)) {
221  $this->fk_doc = (int) $this->fk_doc;
222  }
223  if (isset($this->fk_docdet)) {
224  $this->fk_docdet = (int) $this->fk_docdet;
225  }
226  if (isset($this->thirdparty_code)) {
227  $this->thirdparty_code = trim($this->thirdparty_code);
228  }
229  if (isset($this->subledger_account)) {
230  $this->subledger_account = trim($this->subledger_account);
231  }
232  if (isset($this->subledger_label)) {
233  $this->subledger_label = trim($this->subledger_label);
234  }
235  if (isset($this->numero_compte)) {
236  $this->numero_compte = trim($this->numero_compte);
237  }
238  if (isset($this->label_compte)) {
239  $this->label_compte = trim($this->label_compte);
240  }
241  if (isset($this->label_operation)) {
242  $this->label_operation = trim($this->label_operation);
243  }
244  if (isset($this->debit)) {
245  $this->debit = (float) $this->debit;
246  }
247  if (isset($this->credit)) {
248  $this->credit = (float) $this->credit;
249  }
250  if (isset($this->montant)) {
251  $this->montant = (float) $this->montant;
252  }
253  if (isset($this->amount)) {
254  $this->amount = (float) $this->amount;
255  }
256  if (isset($this->sens)) {
257  $this->sens = trim($this->sens);
258  }
259  if (isset($this->import_key)) {
260  $this->import_key = trim($this->import_key);
261  }
262  if (isset($this->code_journal)) {
263  $this->code_journal = trim($this->code_journal);
264  }
265  if (isset($this->journal_label)) {
266  $this->journal_label = trim($this->journal_label);
267  }
268  if (isset($this->piece_num)) {
269  $this->piece_num = trim($this->piece_num);
270  }
271  if (empty($this->debit)) {
272  $this->debit = 0.0;
273  }
274  if (empty($this->credit)) {
275  $this->credit = 0.0;
276  }
277 
278  // Check parameters
279  if (($this->numero_compte == "") || $this->numero_compte == '-1' || $this->numero_compte == 'NotDefined') {
280  $langs->loadLangs(array("errors"));
281  if (in_array($this->doc_type, array('bank', 'expense_report'))) {
282  $this->errors[] = $langs->trans('ErrorFieldAccountNotDefinedForBankLine', $this->fk_docdet, $this->doc_type);
283  } else {
284  //$this->errors[]=$langs->trans('ErrorFieldAccountNotDefinedForInvoiceLine', $this->doc_ref, $this->label_compte);
285  $mesg = $this->doc_ref.', '.$langs->trans("AccountAccounting").': '.$this->numero_compte;
286  if ($this->subledger_account && $this->subledger_account != $this->numero_compte) {
287  $mesg .= ', '.$langs->trans("SubledgerAccount").': '.$this->subledger_account;
288  }
289  $this->errors[] = $langs->trans('ErrorFieldAccountNotDefinedForLine', $mesg);
290  }
291 
292  return -1;
293  }
294 
295  $this->db->begin();
296 
297  $this->piece_num = 0;
298 
299  // First check if line not yet already in bookkeeping.
300  // 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
301  // with same doc_type, fk_doc, numero_compte for 1 invoice line when using localtaxes with same account)
302  // WARNING: This is not reliable, label may have been modified. This is just a small protection.
303  // The page that make transfer make the test on couple (doc_type - fk_doc) only.
304  $sql = "SELECT count(*) as nb";
305  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
306  $sql .= " WHERE doc_type = '".$this->db->escape($this->doc_type)."'";
307  $sql .= " AND fk_doc = ".((int) $this->fk_doc);
308  if (!empty($conf->global->ACCOUNTANCY_ENABLE_FKDOCDET)) {
309  // DO NOT USE THIS IN PRODUCTION. This will generate a lot of trouble into reports and will corrupt database (by generating duplicate entries.
310  $sql .= " AND fk_docdet = ".((int) $this->fk_docdet); // This field can be 0 if record is for several lines
311  }
312  $sql .= " AND numero_compte = '".$this->db->escape($this->numero_compte)."'";
313  $sql .= " AND label_operation = '".$this->db->escape($this->label_operation)."'";
314  $sql .= " AND entity = ".$conf->entity; // Do not use getEntity for accounting features
315 
316  $resql = $this->db->query($sql);
317 
318  if ($resql) {
319  $row = $this->db->fetch_object($resql);
320  if ($row->nb == 0) { // Not already into bookkeeping
321  // Check to know if piece_num already exists for data we try to insert to reuse the same value
322  $sqlnum = "SELECT piece_num";
323  $sqlnum .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
324  $sqlnum .= " WHERE doc_type = '".$this->db->escape($this->doc_type)."'"; // For example doc_type = 'bank'
325  $sqlnum .= " AND fk_doc = ".((int) $this->fk_doc);
326  if (!empty($conf->global->ACCOUNTANCY_ENABLE_FKDOCDET)) {
327  // fk_docdet is rowid into llx_bank or llx_facturedet or llx_facturefourndet, or ...
328  $sqlnum .= " AND fk_docdet = ".((int) $this->fk_docdet);
329  }
330  $sqlnum .= " AND doc_ref = '".$this->db->escape($this->doc_ref)."'"; // ref of source object
331  $sqlnum .= " AND entity = ".$conf->entity; // Do not use getEntity for accounting features
332 
333  dol_syslog(get_class($this).":: create sqlnum=".$sqlnum, LOG_DEBUG);
334  $resqlnum = $this->db->query($sqlnum);
335  if ($resqlnum) {
336  $objnum = $this->db->fetch_object($resqlnum);
337  $this->piece_num = $objnum->piece_num;
338  }
339 
340  dol_syslog(get_class($this).":: create this->piece_num=".$this->piece_num, LOG_DEBUG);
341  if (empty($this->piece_num)) {
342  $sqlnum = "SELECT MAX(piece_num)+1 as maxpiecenum";
343  $sqlnum .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
344  $sqlnum .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
345 
346  $resqlnum = $this->db->query($sqlnum);
347  if ($resqlnum) {
348  $objnum = $this->db->fetch_object($resqlnum);
349  $this->piece_num = $objnum->maxpiecenum;
350  }
351  dol_syslog(get_class($this).":: create now this->piece_num=".$this->piece_num, LOG_DEBUG);
352  }
353  if (empty($this->piece_num)) {
354  $this->piece_num = 1;
355  }
356 
357  $now = dol_now();
358 
359  $sql = "INSERT INTO ".MAIN_DB_PREFIX.$this->table_element." (";
360  $sql .= "doc_date";
361  $sql .= ", date_lim_reglement";
362  $sql .= ", doc_type";
363  $sql .= ", doc_ref";
364  $sql .= ", fk_doc";
365  $sql .= ", fk_docdet";
366  $sql .= ", thirdparty_code";
367  $sql .= ", subledger_account";
368  $sql .= ", subledger_label";
369  $sql .= ", numero_compte";
370  $sql .= ", label_compte";
371  $sql .= ", label_operation";
372  $sql .= ", debit";
373  $sql .= ", credit";
374  $sql .= ", montant";
375  $sql .= ", sens";
376  $sql .= ", fk_user_author";
377  $sql .= ", date_creation";
378  $sql .= ", code_journal";
379  $sql .= ", journal_label";
380  $sql .= ", piece_num";
381  $sql .= ', entity';
382  $sql .= ") VALUES (";
383  $sql .= "'".$this->db->idate($this->doc_date)."'";
384  $sql .= ", ".(!isset($this->date_lim_reglement) || dol_strlen($this->date_lim_reglement) == 0 ? 'NULL' : "'".$this->db->idate($this->date_lim_reglement)."'");
385  $sql .= ", '".$this->db->escape($this->doc_type)."'";
386  $sql .= ", '".$this->db->escape($this->doc_ref)."'";
387  $sql .= ", ".((int) $this->fk_doc);
388  $sql .= ", ".((int) $this->fk_docdet);
389  $sql .= ", ".(!empty($this->thirdparty_code) ? ("'".$this->db->escape($this->thirdparty_code)."'") : "NULL");
390  $sql .= ", ".(!empty($this->subledger_account) ? ("'".$this->db->escape($this->subledger_account)."'") : "NULL");
391  $sql .= ", ".(!empty($this->subledger_label) ? ("'".$this->db->escape($this->subledger_label)."'") : "NULL");
392  $sql .= ", '".$this->db->escape($this->numero_compte)."'";
393  $sql .= ", ".(!empty($this->label_compte) ? ("'".$this->db->escape($this->label_compte)."'") : "NULL");
394  $sql .= ", '".$this->db->escape($this->label_operation)."'";
395  $sql .= ", ".((float) $this->debit);
396  $sql .= ", ".((float) $this->credit);
397  $sql .= ", ".((float) $this->montant);
398  $sql .= ", ".(!empty($this->sens) ? ("'".$this->db->escape($this->sens)."'") : "NULL");
399  $sql .= ", '".$this->db->escape($this->fk_user_author)."'";
400  $sql .= ", '".$this->db->idate($now)."'";
401  $sql .= ", '".$this->db->escape($this->code_journal)."'";
402  $sql .= ", ".(!empty($this->journal_label) ? ("'".$this->db->escape($this->journal_label)."'") : "NULL");
403  $sql .= ", ".((int) $this->piece_num);
404  $sql .= ", ".(!isset($this->entity) ? $conf->entity : $this->entity);
405  $sql .= ")";
406 
407  $resql = $this->db->query($sql);
408  if ($resql) {
409  $id = $this->db->last_insert_id(MAIN_DB_PREFIX.$this->table_element);
410 
411  if ($id > 0) {
412  $this->id = $id;
413  $result = 0;
414  } else {
415  $result = -2;
416  $error++;
417  $this->errors[] = 'Error Create Error '.$result.' lecture ID';
418  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
419  }
420  } else {
421  $result = -1;
422  $error++;
423  $this->errors[] = 'Error '.$this->db->lasterror();
424  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
425  }
426  } else { // Already exists
427  $result = -3;
428  $error++;
429  $this->error = 'BookkeepingRecordAlreadyExists';
430  dol_syslog(__METHOD__.' '.$this->error, LOG_WARNING);
431  }
432  } else {
433  $result = -5;
434  $error++;
435  $this->errors[] = 'Error '.$this->db->lasterror();
436  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
437  }
438 
439  // Uncomment this and change MYOBJECT to your own tag if you
440  // want this action to call a trigger.
441  //if (! $error && ! $notrigger) {
442 
443  // // Call triggers
444  // $result=$this->call_trigger('MYOBJECT_CREATE',$user);
445  // if ($result < 0) $error++;
446  // // End call triggers
447  //}
448 
449  // Commit or rollback
450  if ($error) {
451  $this->db->rollback();
452  return -1 * $error;
453  } else {
454  $this->db->commit();
455  return $result;
456  }
457  }
458 
469  public function getNomUrl($withpicto = 0, $option = '', $notooltip = 0, $morecss = '', $save_lastsearch_value = -1)
470  {
471  global $db, $conf, $langs;
472  global $dolibarr_main_authentication, $dolibarr_main_demo;
473  global $menumanager, $hookmanager;
474 
475  if (!empty($conf->dol_no_mouse_hover)) {
476  $notooltip = 1; // Force disable tooltips
477  }
478 
479  $result = '';
480  $companylink = '';
481 
482  $label = '<u>'.$langs->trans("Transaction").'</u>';
483  $label .= '<br>';
484  $label .= '<b>'.$langs->trans('Ref').':</b> '.$this->piece_num;
485 
486  $url = DOL_URL_ROOT.'/accountancy/bookkeeping/card.php?piece_num='.$this->piece_num;
487 
488  if ($option != 'nolink') {
489  // Add param to save lastsearch_values or not
490  $add_save_lastsearch_values = ($save_lastsearch_value == 1 ? 1 : 0);
491  if ($save_lastsearch_value == -1 && preg_match('/list\.php/', $_SERVER["PHP_SELF"])) {
492  $add_save_lastsearch_values = 1;
493  }
494  if ($add_save_lastsearch_values) {
495  $url .= '&save_lastsearch_values=1';
496  }
497  }
498 
499  $linkclose = '';
500  if (empty($notooltip)) {
501  if (!empty($conf->global->MAIN_OPTIMIZEFORTEXTBROWSER)) {
502  $label = $langs->trans("ShowTransaction");
503  $linkclose .= ' alt="'.dol_escape_htmltag($label, 1).'"';
504  }
505  $linkclose .= ' title="'.dol_escape_htmltag($label, 1).'"';
506  $linkclose .= ' class="classfortooltip'.($morecss ? ' '.$morecss : '').'"';
507  } else {
508  $linkclose = ($morecss ? ' class="'.$morecss.'"' : '');
509  }
510 
511  $linkstart = '<a href="'.$url.'"';
512  $linkstart .= $linkclose.'>';
513  $linkend = '</a>';
514 
515  $result .= $linkstart;
516  if ($withpicto) {
517  $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);
518  }
519  if ($withpicto != 2) {
520  $result .= $this->piece_num;
521  }
522  $result .= $linkend;
523  //if ($withpicto != 2) $result.=(($addlabel && $this->label) ? $sep . dol_trunc($this->label, ($addlabel > 1 ? $addlabel : 0)) : '');
524 
525  global $action;
526  $hookmanager->initHooks(array($this->element . 'dao'));
527  $parameters = array('id'=>$this->id, 'getnomurl' => &$result);
528  $reshook = $hookmanager->executeHooks('getNomUrl', $parameters, $this, $action); // Note that $action and $object may have been modified by some hooks
529  if ($reshook > 0) {
530  $result = $hookmanager->resPrint;
531  } else {
532  $result .= $hookmanager->resPrint;
533  }
534  return $result;
535  }
536 
545  public function createStd(User $user, $notrigger = false, $mode = '')
546  {
547  global $conf, $langs;
548 
549  $langs->loadLangs(array("accountancy", "bills", "compta"));
550 
551  dol_syslog(__METHOD__, LOG_DEBUG);
552 
553  $error = 0;
554 
555  // Clean parameters
556  if (isset($this->doc_type)) {
557  $this->doc_type = trim($this->doc_type);
558  }
559  if (isset($this->doc_ref)) {
560  $this->doc_ref = trim($this->doc_ref);
561  }
562  if (isset($this->fk_doc)) {
563  $this->fk_doc = (int) $this->fk_doc;
564  }
565  if (isset($this->fk_docdet)) {
566  $this->fk_docdet = (int) $this->fk_docdet;
567  }
568  if (isset($this->thirdparty_code)) {
569  $this->thirdparty_code = trim($this->thirdparty_code);
570  }
571  if (isset($this->subledger_account)) {
572  $this->subledger_account = trim($this->subledger_account);
573  }
574  if (isset($this->subledger_label)) {
575  $this->subledger_label = trim($this->subledger_label);
576  }
577  if (isset($this->numero_compte)) {
578  $this->numero_compte = trim($this->numero_compte);
579  }
580  if (isset($this->label_compte)) {
581  $this->label_compte = trim($this->label_compte);
582  }
583  if (isset($this->label_operation)) {
584  $this->label_operation = trim($this->label_operation);
585  }
586  if (isset($this->debit)) {
587  $this->debit = trim($this->debit);
588  }
589  if (isset($this->credit)) {
590  $this->credit = trim($this->credit);
591  }
592  if (isset($this->montant)) {
593  $this->montant = trim($this->montant);
594  }
595  if (isset($this->amount)) {
596  $this->amount = trim($this->amount);
597  }
598  if (isset($this->sens)) {
599  $this->sens = trim($this->sens);
600  }
601  if (isset($this->import_key)) {
602  $this->import_key = trim($this->import_key);
603  }
604  if (isset($this->code_journal)) {
605  $this->code_journal = trim($this->code_journal);
606  }
607  if (isset($this->journal_label)) {
608  $this->journal_label = trim($this->journal_label);
609  }
610  if (isset($this->piece_num)) {
611  $this->piece_num = trim($this->piece_num);
612  }
613  if (empty($this->debit)) {
614  $this->debit = 0;
615  }
616  if (empty($this->credit)) {
617  $this->credit = 0;
618  }
619  if (empty($this->montant)) {
620  $this->montant = 0;
621  }
622 
623  $this->debit = price2num($this->debit, 'MT');
624  $this->credit = price2num($this->credit, 'MT');
625  $this->montant = price2num($this->montant, 'MT');
626 
627  $now = dol_now();
628 
629  // Check parameters
630  $this->journal_label = $langs->trans($this->journal_label);
631 
632  // Insert request
633  $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.$mode.' (';
634  $sql .= 'doc_date,';
635  $sql .= 'date_lim_reglement,';
636  $sql .= 'doc_type,';
637  $sql .= 'doc_ref,';
638  $sql .= 'fk_doc,';
639  $sql .= 'fk_docdet,';
640  $sql .= 'thirdparty_code,';
641  $sql .= 'subledger_account,';
642  $sql .= 'subledger_label,';
643  $sql .= 'numero_compte,';
644  $sql .= 'label_compte,';
645  $sql .= 'label_operation,';
646  $sql .= 'debit,';
647  $sql .= 'credit,';
648  $sql .= 'montant,';
649  $sql .= 'sens,';
650  $sql .= 'fk_user_author,';
651  $sql .= 'date_creation,';
652  $sql .= 'code_journal,';
653  $sql .= 'journal_label,';
654  $sql .= 'piece_num,';
655  $sql .= 'entity';
656  $sql .= ') VALUES (';
657  $sql .= ' '.(!isset($this->doc_date) || dol_strlen($this->doc_date) == 0 ? 'NULL' : "'".$this->db->idate($this->doc_date)."'").',';
658  $sql .= ' '.(!isset($this->date_lim_reglement) || dol_strlen($this->date_lim_reglement) == 0 ? 'NULL' : "'".$this->db->idate($this->date_lim_reglement)."'").',';
659  $sql .= ' '.(!isset($this->doc_type) ? 'NULL' : "'".$this->db->escape($this->doc_type)."'").',';
660  $sql .= ' '.(!isset($this->doc_ref) ? 'NULL' : "'".$this->db->escape($this->doc_ref)."'").',';
661  $sql .= ' '.(empty($this->fk_doc) ? '0' : (int) $this->fk_doc).',';
662  $sql .= ' '.(empty($this->fk_docdet) ? '0' : (int) $this->fk_docdet).',';
663  $sql .= ' '.(!isset($this->thirdparty_code) ? 'NULL' : "'".$this->db->escape($this->thirdparty_code)."'").',';
664  $sql .= ' '.(!isset($this->subledger_account) ? 'NULL' : "'".$this->db->escape($this->subledger_account)."'").',';
665  $sql .= ' '.(!isset($this->subledger_label) ? 'NULL' : "'".$this->db->escape($this->subledger_label)."'").',';
666  $sql .= ' '.(!isset($this->numero_compte) ? 'NULL' : "'".$this->db->escape($this->numero_compte)."'").',';
667  $sql .= ' '.(!isset($this->label_compte) ? 'NULL' : "'".$this->db->escape($this->label_compte)."'").',';
668  $sql .= ' '.(!isset($this->label_operation) ? 'NULL' : "'".$this->db->escape($this->label_operation)."'").',';
669  $sql .= ' '.(!isset($this->debit) ? 'NULL' : $this->debit).',';
670  $sql .= ' '.(!isset($this->credit) ? 'NULL' : $this->credit).',';
671  $sql .= ' '.(!isset($this->montant) ? 'NULL' : $this->montant).',';
672  $sql .= ' '.(!isset($this->sens) ? 'NULL' : "'".$this->db->escape($this->sens)."'").',';
673  $sql .= ' '.((int) $user->id).',';
674  $sql .= ' '."'".$this->db->idate($now)."',";
675  $sql .= ' '.(empty($this->code_journal) ? 'NULL' : "'".$this->db->escape($this->code_journal)."'").',';
676  $sql .= ' '.(empty($this->journal_label) ? 'NULL' : "'".$this->db->escape($this->journal_label)."'").',';
677  $sql .= ' '.(empty($this->piece_num) ? 'NULL' : $this->db->escape($this->piece_num)).',';
678  $sql .= ' '.(!isset($this->entity) ? $conf->entity : $this->entity);
679  $sql .= ')';
680 
681  $this->db->begin();
682 
683  $resql = $this->db->query($sql);
684  if (!$resql) {
685  $error++;
686  $this->errors[] = 'Error '.$this->db->lasterror();
687  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
688  }
689 
690  if (!$error) {
691  $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX.$this->table_element.$mode);
692 
693  // Uncomment this and change MYOBJECT to your own tag if you
694  // want this action to call a trigger.
695  //if (! $notrigger) {
696 
697  // // Call triggers
698  // $result=$this->call_trigger('MYOBJECT_CREATE',$user);
699  // if ($result < 0) $error++;
700  // // End call triggers
701  //}
702  }
703 
704  // Commit or rollback
705  if ($error) {
706  $this->db->rollback();
707 
708  return -1 * $error;
709  } else {
710  $this->db->commit();
711 
712  return $this->id;
713  }
714  }
715 
725  public function fetch($id, $ref = null, $mode = '')
726  {
727  global $conf;
728 
729  dol_syslog(__METHOD__, LOG_DEBUG);
730 
731  $sql = 'SELECT';
732  $sql .= ' t.rowid,';
733  $sql .= " t.doc_date,";
734  $sql .= " t.date_lim_reglement,";
735  $sql .= " t.doc_type,";
736  $sql .= " t.doc_ref,";
737  $sql .= " t.fk_doc,";
738  $sql .= " t.fk_docdet,";
739  $sql .= " t.thirdparty_code,";
740  $sql .= " t.subledger_account,";
741  $sql .= " t.subledger_label,";
742  $sql .= " t.numero_compte,";
743  $sql .= " t.label_compte,";
744  $sql .= " t.label_operation,";
745  $sql .= " t.debit,";
746  $sql .= " t.credit,";
747  $sql .= " t.montant as amount,";
748  $sql .= " t.sens,";
749  $sql .= " t.fk_user_author,";
750  $sql .= " t.import_key,";
751  $sql .= " t.code_journal,";
752  $sql .= " t.journal_label,";
753  $sql .= " t.piece_num,";
754  $sql .= " t.date_creation,";
755  // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
756  if ($mode != "_tmp") {
757  $sql .= " t.date_export,";
758  }
759  $sql .= " t.date_validated as date_validation";
760  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.$mode.' as t';
761  $sql .= ' WHERE 1 = 1';
762  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
763  if (null !== $ref) {
764  $sql .= " AND t.ref = '".$this->db->escape($ref)."'";
765  } else {
766  $sql .= ' AND t.rowid = '.((int) $id);
767  }
768 
769  $resql = $this->db->query($sql);
770  if ($resql) {
771  $numrows = $this->db->num_rows($resql);
772  if ($numrows) {
773  $obj = $this->db->fetch_object($resql);
774 
775  $this->id = $obj->rowid;
776 
777  $this->doc_date = $this->db->jdate($obj->doc_date);
778  $this->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
779  $this->doc_type = $obj->doc_type;
780  $this->doc_ref = $obj->doc_ref;
781  $this->fk_doc = $obj->fk_doc;
782  $this->fk_docdet = $obj->fk_docdet;
783  $this->thirdparty_code = $obj->thirdparty_code;
784  $this->subledger_account = $obj->subledger_account;
785  $this->subledger_label = $obj->subledger_label;
786  $this->numero_compte = $obj->numero_compte;
787  $this->label_compte = $obj->label_compte;
788  $this->label_operation = $obj->label_operation;
789  $this->debit = $obj->debit;
790  $this->credit = $obj->credit;
791  $this->montant = $obj->amount;
792  $this->amount = $obj->amount;
793  $this->sens = $obj->sens;
794  $this->fk_user_author = $obj->fk_user_author;
795  $this->import_key = $obj->import_key;
796  $this->code_journal = $obj->code_journal;
797  $this->journal_label = $obj->journal_label;
798  $this->piece_num = $obj->piece_num;
799  $this->date_creation = $this->db->jdate($obj->date_creation);
800  $this->date_export = $this->db->jdate($obj->date_export);
801  $this->date_validation = isset($obj->date_validated) ? $this->db->jdate($obj->date_validated) : '';
802  }
803  $this->db->free($resql);
804 
805  if ($numrows) {
806  return 1;
807  } else {
808  return 0;
809  }
810  } else {
811  $this->errors[] = 'Error '.$this->db->lasterror();
812  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
813 
814  return -1;
815  }
816  }
817 
818 
832  public function fetchAllByAccount($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, array $filter = array(), $filtermode = 'AND', $option = 0, $countonly = 0)
833  {
834  global $conf;
835 
836  dol_syslog(__METHOD__, LOG_DEBUG);
837 
838  $this->lines = array();
839  $num = 0;
840 
841  $sql = 'SELECT';
842  if ($countonly) {
843  $sql .= ' COUNT(t.rowid) as nb';
844  } else {
845  $sql .= ' t.rowid,';
846  $sql .= " t.doc_date,";
847  $sql .= " t.doc_type,";
848  $sql .= " t.doc_ref,";
849  $sql .= " t.fk_doc,";
850  $sql .= " t.fk_docdet,";
851  $sql .= " t.thirdparty_code,";
852  $sql .= " t.subledger_account,";
853  $sql .= " t.subledger_label,";
854  $sql .= " t.numero_compte,";
855  $sql .= " t.label_compte,";
856  $sql .= " t.label_operation,";
857  $sql .= " t.debit,";
858  $sql .= " t.credit,";
859  $sql .= " t.montant as amount,";
860  $sql .= " t.sens,";
861  $sql .= " t.multicurrency_amount,";
862  $sql .= " t.multicurrency_code,";
863  $sql .= " t.lettering_code,";
864  $sql .= " t.date_lettering,";
865  $sql .= " t.fk_user_author,";
866  $sql .= " t.import_key,";
867  $sql .= " t.code_journal,";
868  $sql .= " t.journal_label,";
869  $sql .= " t.piece_num,";
870  $sql .= " t.date_creation,";
871  $sql .= " t.date_export,";
872  $sql .= " t.date_validated as date_validation,";
873  $sql .= " t.import_key";
874  }
875  // Manage filter
876  $sqlwhere = array();
877  if (count($filter) > 0) {
878  foreach ($filter as $key => $value) {
879  if ($key == 't.doc_date') {
880  $sqlwhere[] = $key.'=\''.$this->db->idate($value).'\'';
881  } elseif ($key == 't.doc_date>=' || $key == 't.doc_date<=') {
882  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
883  } elseif ($key == 't.numero_compte>=' || $key == 't.numero_compte<=' || $key == 't.subledger_account>=' || $key == 't.subledger_account<=') {
884  $sqlwhere[] = $key.'\''.$this->db->escape($value).'\'';
885  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
886  $sqlwhere[] = $key.'='.$value;
887  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
888  $sqlwhere[] = $key.' LIKE \''.$this->db->escapeforlike($this->db->escape($value)).'%\'';
889  } elseif ($key == 't.date_creation>=' || $key == 't.date_creation<=') {
890  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
891  } elseif ($key == 't.date_export>=' || $key == 't.date_export<=') {
892  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
893  } elseif ($key == 't.date_validated>=' || $key == 't.date_validated<=') {
894  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
895  } elseif ($key == 't.credit' || $key == 't.debit') {
896  $sqlwhere[] = natural_search($key, $value, 1, 1);
897  } elseif ($key == 't.reconciled_option') {
898  $sqlwhere[] = 't.lettering_code IS NULL';
899  } elseif ($key == 't.code_journal' && !empty($value)) {
900  if (is_array($value)) {
901  $sqlwhere[] = natural_search("t.code_journal", join(',', $value), 3, 1);
902  } else {
903  $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
904  }
905  } elseif ($key == 't.search_accounting_code_in' && !empty($value)) {
906  $sqlwhere[] = 't.numero_compte IN ('.$this->db->sanitize($value, 1).')';
907  } else {
908  $sqlwhere[] = natural_search($key, $value, 0, 1);
909  }
910  }
911  }
912  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
913  $sql .= ' WHERE entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
914  if (count($sqlwhere) > 0) {
915  $sql .= " AND ".implode(" ".$filtermode." ", $sqlwhere);
916  }
917  // Filter by ledger account or subledger account
918  if (!empty($option)) {
919  $sql .= " AND t.subledger_account IS NOT NULL";
920  $sql .= " AND t.subledger_account <> ''";
921  $sortfield = 't.subledger_account'.($sortfield ? ','.$sortfield : '');
922  $sortorder = 'ASC'.($sortfield ? ','.$sortfield : '');
923  } else {
924  $sortfield = 't.numero_compte'.($sortfield ? ','.$sortfield : '');
925  $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
926  }
927 
928  if (!$countonly) {
929  $sql .= $this->db->order($sortfield, $sortorder);
930  if (!empty($limit)) {
931  $sql .= $this->db->plimit($limit + 1, $offset);
932  }
933  }
934 
935  $resql = $this->db->query($sql);
936  if ($resql) {
937  if ($countonly) {
938  $obj = $this->db->fetch_object($resql);
939  if ($obj) {
940  $num = $obj->nb;
941  }
942  } else {
943  $num = $this->db->num_rows($resql);
944 
945  $i = 0;
946  while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
947  $line = new BookKeepingLine();
948 
949  $line->id = $obj->rowid;
950 
951  $line->doc_date = $this->db->jdate($obj->doc_date);
952  $line->doc_type = $obj->doc_type;
953  $line->doc_ref = $obj->doc_ref;
954  $line->fk_doc = $obj->fk_doc;
955  $line->fk_docdet = $obj->fk_docdet;
956  $line->thirdparty_code = $obj->thirdparty_code;
957  $line->subledger_account = $obj->subledger_account;
958  $line->subledger_label = $obj->subledger_label;
959  $line->numero_compte = $obj->numero_compte;
960  $line->label_compte = $obj->label_compte;
961  $line->label_operation = $obj->label_operation;
962  $line->debit = $obj->debit;
963  $line->credit = $obj->credit;
964  $line->montant = $obj->amount; // deprecated
965  $line->amount = $obj->amount;
966  $line->sens = $obj->sens;
967  $line->multicurrency_amount = $obj->multicurrency_amount;
968  $line->multicurrency_code = $obj->multicurrency_code;
969  $line->lettering_code = $obj->lettering_code;
970  $line->date_lettering = $obj->date_lettering;
971  $line->fk_user_author = $obj->fk_user_author;
972  $line->import_key = $obj->import_key;
973  $line->code_journal = $obj->code_journal;
974  $line->journal_label = $obj->journal_label;
975  $line->piece_num = $obj->piece_num;
976  $line->date_creation = $this->db->jdate($obj->date_creation);
977  $line->date_export = $this->db->jdate($obj->date_export);
978  $line->date_validation = $this->db->jdate($obj->date_validation);
979  $line->import_key = $obj->import_key;
980 
981  $this->lines[] = $line;
982 
983  $i++;
984  }
985  }
986  $this->db->free($resql);
987 
988  return $num;
989  } else {
990  $this->errors[] = 'Error '.$this->db->lasterror();
991  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
992 
993  return -1;
994  }
995  }
996 
1009  public function fetchAll($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, array $filter = array(), $filtermode = 'AND', $showAlreadyExportMovements = 1)
1010  {
1011  global $conf;
1012 
1013  dol_syslog(__METHOD__, LOG_DEBUG);
1014 
1015  $sql = 'SELECT';
1016  $sql .= ' t.rowid,';
1017  $sql .= " t.doc_date,";
1018  $sql .= " t.doc_type,";
1019  $sql .= " t.doc_ref,";
1020  $sql .= " t.fk_doc,";
1021  $sql .= " t.fk_docdet,";
1022  $sql .= " t.thirdparty_code,";
1023  $sql .= " t.subledger_account,";
1024  $sql .= " t.subledger_label,";
1025  $sql .= " t.numero_compte,";
1026  $sql .= " t.label_compte,";
1027  $sql .= " t.label_operation,";
1028  $sql .= " t.debit,";
1029  $sql .= " t.credit,";
1030  $sql .= " t.lettering_code,";
1031  $sql .= " t.date_lettering,";
1032  $sql .= " t.montant as amount,";
1033  $sql .= " t.sens,";
1034  $sql .= " t.fk_user_author,";
1035  $sql .= " t.import_key,";
1036  $sql .= " t.code_journal,";
1037  $sql .= " t.journal_label,";
1038  $sql .= " t.piece_num,";
1039  $sql .= " t.date_creation,";
1040  $sql .= " t.date_lim_reglement,";
1041  $sql .= " t.tms as date_modification,";
1042  $sql .= " t.date_export,";
1043  $sql .= " t.date_validated as date_validation";
1044  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
1045  // Manage filter
1046  $sqlwhere = array();
1047  if (count($filter) > 0) {
1048  foreach ($filter as $key => $value) {
1049  if ($key == 't.doc_date') {
1050  $sqlwhere[] = $key.'=\''.$this->db->idate($value).'\'';
1051  } elseif ($key == 't.doc_date>=' || $key == 't.doc_date<=') {
1052  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
1053  } elseif ($key == 't.numero_compte>=' || $key == 't.numero_compte<=' || $key == 't.subledger_account>=' || $key == 't.subledger_account<=') {
1054  $sqlwhere[] = $key.'\''.$this->db->escape($value).'\'';
1055  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1056  $sqlwhere[] = $key.'='.((int) $value);
1057  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1058  $sqlwhere[] = $key.' LIKE \''.$this->db->escape($value).'%\'';
1059  } elseif ($key == 't.date_creation>=' || $key == 't.date_creation<=') {
1060  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
1061  } elseif ($key == 't.tms>=' || $key == 't.tms<=') {
1062  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
1063  } elseif ($key == 't.date_export>=' || $key == 't.date_export<=') {
1064  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
1065  } elseif ($key == 't.date_validated>=' || $key == 't.date_validated<=') {
1066  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
1067  } elseif ($key == 't.credit' || $key == 't.debit') {
1068  $sqlwhere[] = natural_search($key, $value, 1, 1);
1069  } elseif ($key == 't.code_journal' && !empty($value)) {
1070  if (is_array($value)) {
1071  $sqlwhere[] = natural_search("t.code_journal", join(',', $value), 3, 1);
1072  } else {
1073  $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1074  }
1075  } else {
1076  $sqlwhere[] = natural_search($key, $value, 0, 1);
1077  }
1078  }
1079  }
1080  $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1081  if ($showAlreadyExportMovements == 0) {
1082  $sql .= " AND t.date_export IS NULL";
1083  }
1084  if (count($sqlwhere) > 0) {
1085  $sql .= ' AND '.implode(" ".$filtermode." ", $sqlwhere);
1086  }
1087  if (!empty($sortfield)) {
1088  $sql .= $this->db->order($sortfield, $sortorder);
1089  }
1090  if (!empty($limit)) {
1091  $sql .= $this->db->plimit($limit + 1, $offset);
1092  }
1093  $this->lines = array();
1094 
1095  $resql = $this->db->query($sql);
1096  if ($resql) {
1097  $num = $this->db->num_rows($resql);
1098 
1099  $i = 0;
1100  while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1101  $line = new BookKeepingLine();
1102 
1103  $line->id = $obj->rowid;
1104 
1105  $line->doc_date = $this->db->jdate($obj->doc_date);
1106  $line->doc_type = $obj->doc_type;
1107  $line->doc_ref = $obj->doc_ref;
1108  $line->fk_doc = $obj->fk_doc;
1109  $line->fk_docdet = $obj->fk_docdet;
1110  $line->thirdparty_code = $obj->thirdparty_code;
1111  $line->subledger_account = $obj->subledger_account;
1112  $line->subledger_label = $obj->subledger_label;
1113  $line->numero_compte = $obj->numero_compte;
1114  $line->label_compte = $obj->label_compte;
1115  $line->label_operation = $obj->label_operation;
1116  $line->debit = $obj->debit;
1117  $line->credit = $obj->credit;
1118  $line->montant = $obj->amount; // deprecated
1119  $line->amount = $obj->amount;
1120  $line->sens = $obj->sens;
1121  $line->lettering_code = $obj->lettering_code;
1122  $line->date_lettering = $obj->date_lettering;
1123  $line->fk_user_author = $obj->fk_user_author;
1124  $line->import_key = $obj->import_key;
1125  $line->code_journal = $obj->code_journal;
1126  $line->journal_label = $obj->journal_label;
1127  $line->piece_num = $obj->piece_num;
1128  $line->date_creation = $this->db->jdate($obj->date_creation);
1129  $line->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
1130  $line->date_modification = $this->db->jdate($obj->date_modification);
1131  $line->date_export = $this->db->jdate($obj->date_export);
1132  $line->date_validation = $this->db->jdate($obj->date_validation);
1133 
1134  $this->lines[] = $line;
1135 
1136  $i++;
1137  }
1138  $this->db->free($resql);
1139 
1140  return $num;
1141  } else {
1142  $this->errors[] = 'Error '.$this->db->lasterror();
1143  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1144  return -1;
1145  }
1146  }
1147 
1160  public function fetchAllBalance($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, array $filter = array(), $filtermode = 'AND', $option = 0)
1161  {
1162  global $conf;
1163 
1164  $this->lines = array();
1165 
1166  dol_syslog(__METHOD__, LOG_DEBUG);
1167 
1168  $sql = 'SELECT';
1169  $sql .= " t.numero_compte,";
1170  $sql .= " t.label_compte,";
1171  if (!empty($option)) {
1172  $sql .= " t.subledger_account,";
1173  $sql .= " t.subledger_label,";
1174  }
1175  $sql .= " SUM(t.debit) as debit,";
1176  $sql .= " SUM(t.credit) as credit";
1177  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
1178  // Manage filter
1179  $sqlwhere = array();
1180  if (count($filter) > 0) {
1181  foreach ($filter as $key => $value) {
1182  if ($key == 't.doc_date') {
1183  $sqlwhere[] = $key." = '".$this->db->idate($value)."'";
1184  } elseif ($key == 't.doc_date>=' || $key == 't.doc_date<=' || $key == 't.doc_date>' || $key == 't.doc_date<') {
1185  $sqlwhere[] = $key."'".$this->db->idate($value)."'";
1186  } elseif ($key == 't.numero_compte>=' || $key == 't.numero_compte<=' || $key == 't.subledger_account>=' || $key == 't.subledger_account<=') {
1187  $sqlwhere[] = $key."'".$this->db->escape($value)."'";
1188  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1189  $sqlwhere[] = $key." = ".((int) $value);
1190  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1191  $sqlwhere[] = $key." LIKE '".$this->db->escape($value)."%'";
1192  } elseif ($key == 't.subledger_label') {
1193  $sqlwhere[] = $key." LIKE '".$this->db->escape($value)."%'";
1194  } elseif ($key == 't.code_journal' && !empty($value)) {
1195  if (is_array($value)) {
1196  $sqlwhere[] = natural_search("t.code_journal", join(',', $value), 3, 1);
1197  } else {
1198  $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1199  }
1200  } elseif ($key == 't.reconciled_option') {
1201  $sqlwhere[] = 't.lettering_code IS NULL';
1202  } else {
1203  $sqlwhere[] = $key." LIKE '%".$this->db->escape($value)."%'";
1204  }
1205  }
1206  }
1207  $sql .= ' WHERE entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1208  if (count($sqlwhere) > 0) {
1209  $sql .= " AND ".implode(" ".$filtermode." ", $sqlwhere);
1210  }
1211 
1212  if (!empty($option)) {
1213  $sql .= ' AND t.subledger_account IS NOT NULL';
1214  $sql .= ' AND t.subledger_account != ""';
1215  $sql .= ' GROUP BY t.numero_compte, t.label_compte, t.subledger_account, t.subledger_label';
1216  $sortfield = 't.subledger_account'.($sortfield ? ','.$sortfield : '');
1217  $sortorder = 'ASC'.($sortfield ? ','.$sortfield : '');
1218  } else {
1219  $sql .= ' GROUP BY t.numero_compte, t.label_compte';
1220  $sortfield = 't.numero_compte'.($sortfield ? ','.$sortfield : '');
1221  $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
1222  }
1223 
1224  if (!empty($sortfield)) {
1225  $sql .= $this->db->order($sortfield, $sortorder);
1226  }
1227  if (!empty($limit)) {
1228  $sql .= $this->db->plimit($limit + 1, $offset);
1229  }
1230 
1231  $resql = $this->db->query($sql);
1232  if ($resql) {
1233  $num = $this->db->num_rows($resql);
1234 
1235  $i = 0;
1236  while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1237  $line = new BookKeepingLine();
1238 
1239  $line->numero_compte = $obj->numero_compte;
1240  $line->label_compte = $obj->label_compte;
1241  $line->subledger_account = $obj->subledger_account;
1242  $line->subledger_label = $obj->subledger_label;
1243  $line->debit = $obj->debit;
1244  $line->credit = $obj->credit;
1245 
1246  $this->lines[] = $line;
1247 
1248  $i++;
1249  }
1250  $this->db->free($resql);
1251 
1252  return $num;
1253  } else {
1254  $this->errors[] = 'Error '.$this->db->lasterror();
1255  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1256 
1257  return -1;
1258  }
1259  }
1260 
1269  public function update(User $user, $notrigger = false, $mode = '')
1270  {
1271  $error = 0;
1272 
1273  dol_syslog(__METHOD__, LOG_DEBUG);
1274 
1275  // Clean parameters
1276  if (isset($this->doc_type)) {
1277  $this->doc_type = trim($this->doc_type);
1278  }
1279  if (isset($this->doc_ref)) {
1280  $this->doc_ref = trim($this->doc_ref);
1281  }
1282  if (isset($this->fk_doc)) {
1283  $this->fk_doc = (int) $this->fk_doc;
1284  }
1285  if (isset($this->fk_docdet)) {
1286  $this->fk_docdet = (int) $this->fk_docdet;
1287  }
1288  if (isset($this->thirdparty_code)) {
1289  $this->thirdparty_code = trim($this->thirdparty_code);
1290  }
1291  if (isset($this->subledger_account)) {
1292  $this->subledger_account = trim($this->subledger_account);
1293  }
1294  if (isset($this->subledger_label)) {
1295  $this->subledger_label = trim($this->subledger_label);
1296  }
1297  if (isset($this->numero_compte)) {
1298  $this->numero_compte = trim($this->numero_compte);
1299  }
1300  if (isset($this->label_compte)) {
1301  $this->label_compte = trim($this->label_compte);
1302  }
1303  if (isset($this->label_operation)) {
1304  $this->label_operation = trim($this->label_operation);
1305  }
1306  if (isset($this->debit)) {
1307  $this->debit = trim($this->debit);
1308  }
1309  if (isset($this->credit)) {
1310  $this->credit = trim($this->credit);
1311  }
1312  if (isset($this->amount)) {
1313  $this->amount = trim($this->amount);
1314  }
1315  if (isset($this->sens)) {
1316  $this->sens = trim($this->sens);
1317  }
1318  if (isset($this->import_key)) {
1319  $this->import_key = trim($this->import_key);
1320  }
1321  if (isset($this->code_journal)) {
1322  $this->code_journal = trim($this->code_journal);
1323  }
1324  if (isset($this->journal_label)) {
1325  $this->journal_label = trim($this->journal_label);
1326  }
1327  if (isset($this->piece_num)) {
1328  $this->piece_num = trim($this->piece_num);
1329  }
1330 
1331  $this->debit = price2num($this->debit, 'MT');
1332  $this->credit = price2num($this->credit, 'MT');
1333 
1334  // Check parameters
1335  // Put here code to add a control on parameters values
1336 
1337  // Update request
1338  $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element.$mode.' SET';
1339  $sql .= ' doc_date = '.(!isset($this->doc_date) || dol_strlen($this->doc_date) != 0 ? "'".$this->db->idate($this->doc_date)."'" : 'null').',';
1340  $sql .= ' doc_type = '.(isset($this->doc_type) ? "'".$this->db->escape($this->doc_type)."'" : "null").',';
1341  $sql .= ' doc_ref = '.(isset($this->doc_ref) ? "'".$this->db->escape($this->doc_ref)."'" : "null").',';
1342  $sql .= ' fk_doc = '.(isset($this->fk_doc) ? $this->fk_doc : "null").',';
1343  $sql .= ' fk_docdet = '.(isset($this->fk_docdet) ? $this->fk_docdet : "null").',';
1344  $sql .= ' thirdparty_code = '.(isset($this->thirdparty_code) ? "'".$this->db->escape($this->thirdparty_code)."'" : "null").',';
1345  $sql .= ' subledger_account = '.(isset($this->subledger_account) ? "'".$this->db->escape($this->subledger_account)."'" : "null").',';
1346  $sql .= ' subledger_label = '.(isset($this->subledger_label) ? "'".$this->db->escape($this->subledger_label)."'" : "null").',';
1347  $sql .= ' numero_compte = '.(isset($this->numero_compte) ? "'".$this->db->escape($this->numero_compte)."'" : "null").',';
1348  $sql .= ' label_compte = '.(isset($this->label_compte) ? "'".$this->db->escape($this->label_compte)."'" : "null").',';
1349  $sql .= ' label_operation = '.(isset($this->label_operation) ? "'".$this->db->escape($this->label_operation)."'" : "null").',';
1350  $sql .= ' debit = '.(isset($this->debit) ? $this->debit : "null").',';
1351  $sql .= ' credit = '.(isset($this->credit) ? $this->credit : "null").',';
1352  $sql .= ' montant = '.(isset($this->montant) ? $this->montant : "null").',';
1353  $sql .= ' sens = '.(isset($this->sens) ? "'".$this->db->escape($this->sens)."'" : "null").',';
1354  $sql .= ' fk_user_author = '.(isset($this->fk_user_author) ? $this->fk_user_author : "null").',';
1355  $sql .= ' import_key = '.(isset($this->import_key) ? "'".$this->db->escape($this->import_key)."'" : "null").',';
1356  $sql .= ' code_journal = '.(isset($this->code_journal) ? "'".$this->db->escape($this->code_journal)."'" : "null").',';
1357  $sql .= ' journal_label = '.(isset($this->journal_label) ? "'".$this->db->escape($this->journal_label)."'" : "null").',';
1358  $sql .= ' piece_num = '.(isset($this->piece_num) ? $this->piece_num : "null");
1359  $sql .= ' WHERE rowid='.((int) $this->id);
1360 
1361  $this->db->begin();
1362 
1363  $resql = $this->db->query($sql);
1364  if (!$resql) {
1365  $error++;
1366  $this->errors[] = 'Error '.$this->db->lasterror();
1367  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1368  }
1369 
1370  // Uncomment this and change MYOBJECT to your own tag if you
1371  // want this action calls a trigger.
1372  //if (! $error && ! $notrigger) {
1373 
1374  // // Call triggers
1375  // $result=$this->call_trigger('MYOBJECT_MODIFY',$user);
1376  // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1377  // // End call triggers
1378  //}
1379 
1380  // Commit or rollback
1381  if ($error) {
1382  $this->db->rollback();
1383 
1384  return -1 * $error;
1385  } else {
1386  $this->db->commit();
1387 
1388  return 1;
1389  }
1390  }
1391 
1401  public function updateByMvt($piece_num = '', $field = '', $value = '', $mode = '')
1402  {
1403  $error = 0;
1404 
1405  $this->db->begin();
1406 
1407  $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element.$mode;
1408  $sql .= " SET ".$field." = ".(is_numeric($value) ? ((float) $value) : "'".$this->db->escape($value)."'");
1409  $sql .= " WHERE piece_num = ".((int) $piece_num);
1410 
1411  $resql = $this->db->query($sql);
1412 
1413  if (!$resql) {
1414  $error++;
1415  $this->errors[] = 'Error '.$this->db->lasterror();
1416  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1417  }
1418  if ($error) {
1419  $this->db->rollback();
1420 
1421  return -1 * $error;
1422  } else {
1423  $this->db->commit();
1424 
1425  return 1;
1426  }
1427  }
1428 
1437  public function delete(User $user, $notrigger = false, $mode = '')
1438  {
1439  dol_syslog(__METHOD__, LOG_DEBUG);
1440 
1441  $error = 0;
1442 
1443  $this->db->begin();
1444 
1445  // Uncomment this and change MYOBJECT to your own tag if you
1446  // want this action calls a trigger.
1447  //if (! $error && ! $notrigger) {
1448 
1449  // // Call triggers
1450  // $result=$this->call_trigger('MYOBJECT_DELETE',$user);
1451  // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1452  // // End call triggers
1453  //}
1454 
1455  if (!$error) {
1456  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.$mode;
1457  $sql .= ' WHERE rowid='.((int) $this->id);
1458 
1459  $resql = $this->db->query($sql);
1460  if (!$resql) {
1461  $error++;
1462  $this->errors[] = 'Error '.$this->db->lasterror();
1463  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1464  }
1465  }
1466 
1467  // Commit or rollback
1468  if ($error) {
1469  $this->db->rollback();
1470 
1471  return -1 * $error;
1472  } else {
1473  $this->db->commit();
1474 
1475  return 1;
1476  }
1477  }
1478 
1485  public function deleteByImportkey($importkey)
1486  {
1487  $this->db->begin();
1488 
1489  // first check if line not yet in bookkeeping
1490  $sql = "DELETE";
1491  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
1492  $sql .= " WHERE import_key = '".$this->db->escape($importkey)."'";
1493 
1494  $resql = $this->db->query($sql);
1495 
1496  if (!$resql) {
1497  $this->errors[] = "Error ".$this->db->lasterror();
1498  dol_syslog(get_class($this)."::delete Error ".$this->db->lasterror(), LOG_ERR);
1499  $this->db->rollback();
1500  return -1;
1501  }
1502 
1503  $this->db->commit();
1504  return 1;
1505  }
1506 
1516  public function deleteByYearAndJournal($delyear = 0, $journal = '', $mode = '', $delmonth = 0)
1517  {
1518  global $conf, $langs;
1519 
1520  if (empty($delyear) && empty($journal)) {
1521  $this->error = 'ErrorOneFieldRequired';
1522  return -1;
1523  }
1524  if (!empty($delmonth) && empty($delyear)) {
1525  $this->error = 'YearRequiredIfMonthDefined';
1526  return -2;
1527  }
1528 
1529  $this->db->begin();
1530 
1531  // Delete record in bookkeeping
1532  $sql = "DELETE";
1533  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1534  $sql .= " WHERE 1 = 1";
1535  $sql .= dolSqlDateFilter('doc_date', 0, $delmonth, $delyear);
1536  if (!empty($journal)) {
1537  $sql .= " AND code_journal = '".$this->db->escape($journal)."'";
1538  }
1539  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1540  // Exclusion of validated entries at the time of deletion
1541  $sql .= " AND date_validated IS NULL";
1542 
1543  // TODO: In a future we must forbid deletion if record is inside a closed fiscal period.
1544 
1545  $resql = $this->db->query($sql);
1546 
1547  if (!$resql) {
1548  $this->errors[] = "Error ".$this->db->lasterror();
1549  foreach ($this->errors as $errmsg) {
1550  dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1551  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1552  }
1553  $this->db->rollback();
1554  return -1;
1555  }
1556 
1557  $this->db->commit();
1558  return 1;
1559  }
1560 
1567  public function deleteMvtNum($piecenum)
1568  {
1569  global $conf;
1570 
1571  $this->db->begin();
1572 
1573  // first check if line not yet in bookkeeping
1574  $sql = "DELETE";
1575  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
1576  $sql .= " WHERE piece_num = ".(int) $piecenum;
1577  $sql .= " AND date_validated IS NULL"; // For security, exclusion of validated entries at the time of deletion
1578  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1579 
1580  $resql = $this->db->query($sql);
1581 
1582  if (!$resql) {
1583  $this->errors[] = "Error ".$this->db->lasterror();
1584  foreach ($this->errors as $errmsg) {
1585  dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1586  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1587  }
1588  $this->db->rollback();
1589  return -1;
1590  }
1591 
1592  $this->db->commit();
1593  return 1;
1594  }
1595 
1603  public function createFromClone(User $user, $fromid)
1604  {
1605  dol_syslog(__METHOD__, LOG_DEBUG);
1606 
1607  $error = 0;
1608  $object = new BookKeeping($this->db);
1609 
1610  $this->db->begin();
1611 
1612  // Load source object
1613  $object->fetch($fromid);
1614  // Reset object
1615  $object->id = 0;
1616 
1617  // Clear fields
1618  // ...
1619 
1620  // Create clone
1621  $object->context['createfromclone'] = 'createfromclone';
1622  $result = $object->create($user);
1623 
1624  // Other options
1625  if ($result < 0) {
1626  $error++;
1627  $this->errors = $object->errors;
1628  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1629  }
1630 
1631  unset($object->context['createfromclone']);
1632 
1633  // End
1634  if (!$error) {
1635  $this->db->commit();
1636 
1637  return $object->id;
1638  } else {
1639  $this->db->rollback();
1640 
1641  return -1;
1642  }
1643  }
1644 
1651  public function initAsSpecimen()
1652  {
1653  global $user;
1654 
1655  $now = dol_now();
1656 
1657  $this->id = 0;
1658  $this->doc_date = $now;
1659  $this->doc_type = '';
1660  $this->doc_ref = '';
1661  $this->fk_doc = 0;
1662  $this->fk_docdet = 0;
1663  $this->thirdparty_code = 'CU001';
1664  $this->subledger_account = '41100001';
1665  $this->subledger_label = 'My customer company';
1666  $this->numero_compte = '411';
1667  $this->label_compte = 'Customer';
1668  $this->label_operation = 'Sales of pea';
1669  $this->debit = 99.9;
1670  $this->credit = 0.0;
1671  $this->amount = 0.0;
1672  $this->sens = 'D';
1673  $this->fk_user_author = $user->id;
1674  $this->import_key = '20201027';
1675  $this->code_journal = 'VT';
1676  $this->journal_label = 'Journal de vente';
1677  $this->piece_num = 1234;
1678  $this->date_creation = $now;
1679  }
1680 
1688  public function fetchPerMvt($piecenum, $mode = '')
1689  {
1690  global $conf;
1691 
1692  $sql = "SELECT piece_num, doc_date,code_journal, journal_label, doc_ref, doc_type,";
1693  $sql .= " date_creation, tms as date_modification, date_validated as date_validation";
1694  // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1695  if ($mode != "_tmp") {
1696  $sql .= ", date_export";
1697  }
1698  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1699  $sql .= " WHERE piece_num = ".((int) $piecenum);
1700  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1701 
1702  dol_syslog(__METHOD__, LOG_DEBUG);
1703  $result = $this->db->query($sql);
1704  if ($result) {
1705  $obj = $this->db->fetch_object($result);
1706 
1707  $this->piece_num = $obj->piece_num;
1708  $this->code_journal = $obj->code_journal;
1709  $this->journal_label = $obj->journal_label;
1710  $this->doc_date = $this->db->jdate($obj->doc_date);
1711  $this->doc_ref = $obj->doc_ref;
1712  $this->doc_type = $obj->doc_type;
1713  $this->date_creation = $this->db->jdate($obj->date_creation);
1714  $this->date_modification = $this->db->jdate($obj->date_modification);
1715  if ($mode != "_tmp") {
1716  $this->date_export = $this->db->jdate($obj->date_export);
1717  }
1718  $this->date_validation = $this->db->jdate($obj->date_validation);
1719  } else {
1720  $this->error = "Error ".$this->db->lasterror();
1721  dol_syslog(__METHOD__.$this->error, LOG_ERR);
1722  return -1;
1723  }
1724 
1725  return 1;
1726  }
1727 
1734  public function getNextNumMvt($mode = '')
1735  {
1736  global $conf;
1737 
1738  $sql = "SELECT MAX(piece_num)+1 as max FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1739  $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1740 
1741  dol_syslog(get_class($this)."::getNextNumMvt", LOG_DEBUG);
1742 
1743  $result = $this->db->query($sql);
1744 
1745  if ($result) {
1746  $obj = $this->db->fetch_object($result);
1747  if ($obj) {
1748  $result = $obj->max;
1749  }
1750  if (empty($result)) {
1751  $result = 1;
1752  }
1753  return $result;
1754  } else {
1755  $this->error = "Error ".$this->db->lasterror();
1756  dol_syslog(get_class($this)."::getNextNumMvt ".$this->error, LOG_ERR);
1757  return -1;
1758  }
1759  }
1760 
1768  public function fetchAllPerMvt($piecenum, $mode = '')
1769  {
1770  global $conf;
1771 
1772  $sql = "SELECT rowid, doc_date, doc_type,";
1773  $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1774  $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1775  $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, journal_label, piece_num,";
1776  $sql .= " date_creation, tms as date_modification, date_validated as date_validation";
1777  // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1778  if ($mode != "_tmp") {
1779  $sql .= ", date_export";
1780  }
1781  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1782  $sql .= " WHERE piece_num = ".((int) $piecenum);
1783  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1784 
1785  dol_syslog(__METHOD__, LOG_DEBUG);
1786  $result = $this->db->query($sql);
1787  if ($result) {
1788  while ($obj = $this->db->fetch_object($result)) {
1789  $line = new BookKeepingLine();
1790 
1791  $line->id = $obj->rowid;
1792 
1793  $line->doc_date = $this->db->jdate($obj->doc_date);
1794  $line->doc_type = $obj->doc_type;
1795  $line->doc_ref = $obj->doc_ref;
1796  $line->fk_doc = $obj->fk_doc;
1797  $line->fk_docdet = $obj->fk_docdet;
1798  $line->thirdparty_code = $obj->thirdparty_code;
1799  $line->subledger_account = $obj->subledger_account;
1800  $line->subledger_label = $obj->subledger_label;
1801  $line->numero_compte = $obj->numero_compte;
1802  $line->label_compte = $obj->label_compte;
1803  $line->label_operation = $obj->label_operation;
1804  $line->debit = $obj->debit;
1805  $line->credit = $obj->credit;
1806  $line->montant = $obj->amount;
1807  $line->amount = $obj->amount;
1808  $line->sens = $obj->sens;
1809  $line->code_journal = $obj->code_journal;
1810  $line->journal_label = $obj->journal_label;
1811  $line->piece_num = $obj->piece_num;
1812  $line->date_creation = $obj->date_creation;
1813  $line->date_modification = $obj->date_modification;
1814  if ($mode != "_tmp") {
1815  $line->date_export = $obj->date_export;
1816  }
1817  $line->date_validation = $obj->date_validation;
1818 
1819  $this->linesmvt[] = $line;
1820  }
1821  } else {
1822  $this->error = "Error ".$this->db->lasterror();
1823  dol_syslog(__METHOD__.$this->error, LOG_ERR);
1824  return -1;
1825  }
1826 
1827  return 1;
1828  }
1829 
1830  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1837  public function export_bookkeeping($model = 'ebp')
1838  {
1839  // phpcs:enable
1840  global $conf;
1841 
1842  $sql = "SELECT rowid, doc_date, doc_type,";
1843  $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1844  $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1845  $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, piece_num,";
1846  $sql .= " date_validated as date_validation";
1847  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
1848  $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1849 
1850  dol_syslog(get_class($this)."::export_bookkeeping", LOG_DEBUG);
1851 
1852  $resql = $this->db->query($sql);
1853 
1854  if ($resql) {
1855  $this->linesexport = array();
1856 
1857  $num = $this->db->num_rows($resql);
1858  while ($obj = $this->db->fetch_object($resql)) {
1859  $line = new BookKeepingLine();
1860 
1861  $line->id = $obj->rowid;
1862 
1863  $line->doc_date = $this->db->jdate($obj->doc_date);
1864  $line->doc_type = $obj->doc_type;
1865  $line->doc_ref = $obj->doc_ref;
1866  $line->fk_doc = $obj->fk_doc;
1867  $line->fk_docdet = $obj->fk_docdet;
1868  $line->thirdparty_code = $obj->thirdparty_code;
1869  $line->subledger_account = $obj->subledger_account;
1870  $line->subledger_label = $obj->subledger_label;
1871  $line->numero_compte = $obj->numero_compte;
1872  $line->label_compte = $obj->label_compte;
1873  $line->label_operation = $obj->label_operation;
1874  $line->debit = $obj->debit;
1875  $line->credit = $obj->credit;
1876  $line->montant = $obj->amount;
1877  $line->amount = $obj->amount;
1878  $line->sens = $obj->sens;
1879  $line->code_journal = $obj->code_journal;
1880  $line->piece_num = $obj->piece_num;
1881  $line->date_validation = $obj->date_validation;
1882 
1883  $this->linesexport[] = $line;
1884  }
1885  $this->db->free($resql);
1886 
1887  return $num;
1888  } else {
1889  $this->error = "Error ".$this->db->lasterror();
1890  dol_syslog(get_class($this)."::export_bookkeeping ".$this->error, LOG_ERR);
1891  return -1;
1892  }
1893  }
1894 
1902  public function transformTransaction($direction = 0, $piece_num = '')
1903  {
1904  global $conf;
1905 
1906  $error = 0;
1907 
1908  $this->db->begin();
1909 
1910  if ($direction == 0) {
1911  $next_piecenum = $this->getNextNumMvt();
1912  $now = dol_now();
1913 
1914  if ($next_piecenum < 0) {
1915  $error++;
1916  }
1917 
1918  if (!$error) {
1919  // Delete if there is an empty line
1920  $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";
1921  $resql = $this->db->query($sql);
1922  if (!$resql) {
1923  $error++;
1924  $this->errors[] = 'Error '.$this->db->lasterror();
1925  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1926  }
1927  }
1928 
1929  if (!$error) {
1930  $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.' (doc_date, doc_type,';
1931  $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
1932  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1933  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num, date_creation)';
1934  $sql .= ' SELECT doc_date, doc_type,';
1935  $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
1936  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1937  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, '.((int) $next_piecenum).", '".$this->db->idate($now)."'";
1938  $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);
1939  $resql = $this->db->query($sql);
1940  if (!$resql) {
1941  $error++;
1942  $this->errors[] = 'Error '.$this->db->lasterror();
1943  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1944  }
1945  }
1946 
1947  if (!$error) {
1948  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1949  $resql = $this->db->query($sql);
1950  if (!$resql) {
1951  $error++;
1952  $this->errors[] = 'Error '.$this->db->lasterror();
1953  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1954  }
1955  }
1956  } elseif ($direction == 1) {
1957  if (!$error) {
1958  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1959  $resql = $this->db->query($sql);
1960  if (!$resql) {
1961  $error++;
1962  $this->errors[] = 'Error '.$this->db->lasterror();
1963  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1964  }
1965  }
1966 
1967  if (!$error) {
1968  $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.'_tmp (doc_date, doc_type,';
1969  $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1970  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1971  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num)';
1972  $sql .= ' SELECT doc_date, doc_type,';
1973  $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1974  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1975  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num';
1976  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1977  $resql = $this->db->query($sql);
1978  if (!$resql) {
1979  $error++;
1980  $this->errors[] = 'Error '.$this->db->lasterror();
1981  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1982  }
1983  }
1984 
1985  if (!$error) {
1986  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1987  $resql = $this->db->query($sql);
1988  if (!$resql) {
1989  $error++;
1990  $this->errors[] = 'Error '.$this->db->lasterror();
1991  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1992  }
1993  }
1994  }
1995  if (!$error) {
1996  $this->db->commit();
1997  return 1;
1998  } else {
1999  $this->db->rollback();
2000  return -1;
2001  }
2002  /*
2003  $sql = "DELETE FROM ";
2004  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as ab";
2005  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as aa ON aa.account_number = ab.numero_compte";
2006  $sql .= " AND aa.active = 1";
2007  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2008  $sql .= " AND asy.rowid = " . ((int) $pcgver);
2009  $sql .= " AND ab.entity IN (" . getEntity('accountancy') . ")";
2010  $sql .= " ORDER BY account_number ASC";
2011  */
2012  }
2013 
2014  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2027  public function select_account($selectid, $htmlname = 'account', $showempty = 0, $event = array(), $select_in = 0, $select_out = 0, $aabase = '')
2028  {
2029  // phpcs:enable
2030  global $conf;
2031 
2032  require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
2033 
2034  $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2035 
2036  $sql = "SELECT DISTINCT ab.numero_compte as account_number, aa.label as label, aa.rowid as rowid, aa.fk_pcg_version";
2037  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping as ab";
2038  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as aa ON aa.account_number = ab.numero_compte";
2039  $sql .= " AND aa.active = 1";
2040  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2041  $sql .= " AND asy.rowid = ".((int) $pcgver);
2042  $sql .= " AND ab.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2043  $sql .= " ORDER BY account_number ASC";
2044 
2045  dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2046  $resql = $this->db->query($sql);
2047 
2048  if (!$resql) {
2049  $this->error = "Error ".$this->db->lasterror();
2050  dol_syslog(get_class($this)."::select_account ".$this->error, LOG_ERR);
2051  return -1;
2052  }
2053 
2054  $out = ajax_combobox($htmlname, $event);
2055 
2056  $options = array();
2057  $selected = null;
2058 
2059  while ($obj = $this->db->fetch_object($resql)) {
2060  $label = length_accountg($obj->account_number).' - '.$obj->label;
2061 
2062  $select_value_in = $obj->rowid;
2063  $select_value_out = $obj->rowid;
2064 
2065  if ($select_in == 1) {
2066  $select_value_in = $obj->account_number;
2067  }
2068  if ($select_out == 1) {
2069  $select_value_out = $obj->account_number;
2070  }
2071 
2072  // Remember guy's we store in database llx_facturedet the rowid of accounting_account and not the account_number
2073  // Because same account_number can be share between different accounting_system and do have the same meaning
2074  if (($selectid != '') && $selectid == $select_value_in) {
2075  $selected = $select_value_out;
2076  }
2077 
2078  $options[$select_value_out] = $label;
2079  }
2080 
2081  $out .= Form::selectarray($htmlname, $options, $selected, $showempty, 0, 0, '', 0, 0, 0, '', 'maxwidth300');
2082  $this->db->free($resql);
2083  return $out;
2084  }
2085 
2093  public function getRootAccount($account = null)
2094  {
2095  global $conf;
2096  $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2097 
2098  $sql = "SELECT root.rowid, root.account_number, root.label as label,";
2099  $sql .= " parent.rowid as parent_rowid, parent.account_number as parent_account_number, parent.label as parent_label";
2100  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa";
2101  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2102  $sql .= " AND asy.rowid = ".((int) $pcgver);
2103  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as parent ON aa.account_parent = parent.rowid AND parent.active = 1";
2104  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as root ON parent.account_parent = root.rowid AND root.active = 1";
2105  $sql .= " WHERE aa.account_number = '".$this->db->escape($account)."'";
2106  $sql .= " AND aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2107 
2108  dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2109  $resql = $this->db->query($sql);
2110  if ($resql) {
2111  $obj = '';
2112  if ($this->db->num_rows($resql)) {
2113  $obj = $this->db->fetch_object($resql);
2114  }
2115 
2116  $result = array('id'=>$obj->rowid, 'account_number'=>$obj->account_number, 'label'=>$obj->label);
2117  return $result;
2118  } else {
2119  $this->error = "Error ".$this->db->lasterror();
2120  dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2121 
2122  return -1;
2123  }
2124  }
2125 
2126  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2133  public function get_compte_desc($account = null)
2134  {
2135  // phpcs:enable
2136  global $conf;
2137 
2138  $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2139  $sql = "SELECT aa.account_number, aa.label, aa.rowid, aa.fk_pcg_version, cat.label as category";
2140  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa ";
2141  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2142  $sql .= " AND aa.account_number = '".$this->db->escape($account)."'";
2143  $sql .= " AND asy.rowid = ".((int) $pcgver);
2144  $sql .= " AND aa.active = 1";
2145  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_accounting_category as cat ON aa.fk_accounting_category = cat.rowid";
2146  $sql .= " WHERE aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2147 
2148  dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2149  $resql = $this->db->query($sql);
2150  if ($resql) {
2151  $obj = '';
2152  if ($this->db->num_rows($resql)) {
2153  $obj = $this->db->fetch_object($resql);
2154  }
2155  if (empty($obj->category)) {
2156  return $obj->label;
2157  } else {
2158  return $obj->label.' ('.$obj->category.')';
2159  }
2160  } else {
2161  $this->error = "Error ".$this->db->lasterror();
2162  dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2163  return -1;
2164  }
2165  }
2166 }
2167 
2171 class BookKeepingLine
2172 {
2176  public $id;
2177 
2178  public $doc_date = '';
2179  public $doc_type;
2180  public $doc_ref;
2181 
2185  public $fk_doc;
2186 
2190  public $fk_docdet;
2191 
2192  public $thirdparty_code;
2193  public $subledger_account;
2194  public $subledger_label;
2195  public $numero_compte;
2196  public $label_compte;
2197  public $label_operation;
2198  public $debit;
2199  public $credit;
2200 
2205  public $montant;
2206 
2210  public $amount;
2211 
2215  public $multicurrency_amount;
2216 
2220  public $multicurrency_code;
2221 
2225  public $sens;
2226  public $lettering_code;
2227  public $date_lettering;
2228 
2232  public $fk_user_author;
2233 
2234  public $import_key;
2235  public $code_journal;
2236  public $journal_label;
2237  public $piece_num;
2238 
2242  public $date_creation;
2243 
2247  public $date_modification;
2248 
2252  public $date_export;
2253 
2257  public $date_validation;
2258 
2262  public $date_lim_reglement;
2263 }
length_accountg
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous)
Definition: accounting.lib.php:94
ajax_combobox
ajax_combobox($htmlname, $events=array(), $minLengthToAutocomplete=0, $forcefocus=0, $widthTypeOfAutocomplete='resolve', $idforemptyvalue='-1', $morecss='')
Convert a html select field into an ajax combobox.
Definition: ajax.lib.php:449
BookKeeping
Class to manage Ledger (General Ledger and Subledger)
Definition: bookkeeping.class.php:34
BookKeeping\fetchAllBalance
fetchAllBalance($sortorder='', $sortfield='', $limit=0, $offset=0, array $filter=array(), $filtermode='AND', $option=0)
Load object in memory from the database.
Definition: bookkeeping.class.php:1160
BookKeeping\create
create(User $user, $notrigger=false)
Create object into database.
Definition: bookkeeping.class.php:205
BookKeeping\createStd
createStd(User $user, $notrigger=false, $mode='')
Create object into database.
Definition: bookkeeping.class.php:545
BookKeeping\deleteByImportkey
deleteByImportkey($importkey)
Delete bookkeeping by importkey.
Definition: bookkeeping.class.php:1485
BookKeeping\getNomUrl
getNomUrl($withpicto=0, $option='', $notooltip=0, $morecss='', $save_lastsearch_value=-1)
Return a link to the object card (with optionally the picto)
Definition: bookkeeping.class.php:469
BookKeeping\select_account
select_account($selectid, $htmlname='account', $showempty=0, $event=array(), $select_in=0, $select_out=0, $aabase='')
Return list of accounts with label by chart of accounts.
Definition: bookkeeping.class.php:2027
BookKeeping\update
update(User $user, $notrigger=false, $mode='')
Update object into database.
Definition: bookkeeping.class.php:1269
BookKeeping\getNextNumMvt
getNextNumMvt($mode='')
Return next number movement.
Definition: bookkeeping.class.php:1734
BookKeeping\get_compte_desc
get_compte_desc($account=null)
Description of accounting account.
Definition: bookkeeping.class.php:2133
BookKeeping\__construct
__construct(DoliDB $db)
Constructor.
Definition: bookkeeping.class.php:193
BookKeeping\createFromClone
createFromClone(User $user, $fromid)
Load an object from its id and create a new one in database.
Definition: bookkeeping.class.php:1603
BookKeeping\getRootAccount
getRootAccount($account=null)
Return id and description of a root accounting account.
Definition: bookkeeping.class.php:2093
BookKeeping\transformTransaction
transformTransaction($direction=0, $piece_num='')
Transform transaction.
Definition: bookkeeping.class.php:1902
BookKeeping\fetchPerMvt
fetchPerMvt($piecenum, $mode='')
Load an accounting document into memory from database.
Definition: bookkeeping.class.php:1688
BookKeeping\updateByMvt
updateByMvt($piece_num='', $field='', $value='', $mode='')
Update accounting movement.
Definition: bookkeeping.class.php:1401
BookKeeping\deleteMvtNum
deleteMvtNum($piecenum)
Delete bookkeeping by piece number.
Definition: bookkeeping.class.php:1567
BookKeeping\deleteByYearAndJournal
deleteByYearAndJournal($delyear=0, $journal='', $mode='', $delmonth=0)
Delete bookkeeping by year.
Definition: bookkeeping.class.php:1516
BookKeeping\fetchAllByAccount
fetchAllByAccount($sortorder='', $sortfield='', $limit=0, $offset=0, array $filter=array(), $filtermode='AND', $option=0, $countonly=0)
Load object in memory from the database in ->lines.
Definition: bookkeeping.class.php:832
BookKeeping\initAsSpecimen
initAsSpecimen()
Initialise object with example values Id must be 0 if object instance is a specimen.
Definition: bookkeeping.class.php:1651
BookKeeping\fetch
fetch($id, $ref=null, $mode='')
Load object in memory from the database.
Definition: bookkeeping.class.php:725
BookKeeping\fetchAll
fetchAll($sortorder='', $sortfield='', $limit=0, $offset=0, array $filter=array(), $filtermode='AND', $showAlreadyExportMovements=1)
Load object in memory from the database.
Definition: bookkeeping.class.php:1009
BookKeeping\fetchAllPerMvt
fetchAllPerMvt($piecenum, $mode='')
Load all informations of accountancy document.
Definition: bookkeeping.class.php:1768
BookKeeping\export_bookkeeping
export_bookkeeping($model='ebp')
Export bookkeeping.
Definition: bookkeeping.class.php:1837
BookKeepingLine
Class BookKeepingLine.
Definition: bookkeeping.class.php:2172
CommonObject
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Definition: commonobject.class.php:46
DoliDB
Class to manage Dolibarr database access.
Definition: DoliDB.class.php:31
Form\selectarray
static selectarray($htmlname, $array, $id='', $show_empty=0, $key_in_label=0, $value_as_key=0, $moreparam='', $translate=0, $maxlen=0, $disabled=0, $sort='', $morecss='minwidth75', $addjscombo=1, $moreparamonempty='', $disablebademail=0, $nohtmlescape=0)
Return a HTML select string, built from an array of key+value.
Definition: html.form.class.php:8212
User
Class to manage Dolibarr users.
Definition: user.class.php:48
$sql
if(isModEnabled('facture') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&empty($conf->global->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') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $sql
Social contributions to pay.
Definition: index.php:746
dolSqlDateFilter
dolSqlDateFilter($datefield, $day_date, $month_date, $year_date, $excludefirstand=0, $gm=false)
Generate a SQL string to make a filter into a range (for second of date until last second of date).
Definition: date.lib.php:359
price2num
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
Definition: functions.lib.php:5955
img_object
img_object($titlealt, $picto, $moreatt='', $pictoisfullpath=false, $srconly=0, $notitle=0)
Show a picto called object_picto (generic function)
Definition: functions.lib.php:4473
natural_search
natural_search($fields, $value, $mode=0, $nofirstand=0)
Generate natural SQL search string for a criteria (this criteria can be tested on one or several fiel...
Definition: functions.lib.php:10024
dol_strlen
dol_strlen($string, $stringencoding='UTF-8')
Make a strlen call.
Definition: functions.lib.php:3997
dol_now
dol_now($mode='auto')
Return date for now.
Definition: functions.lib.php:3056
getDolGlobalInt
getDolGlobalInt($key, $default=0)
Return dolibarr global constant int value.
Definition: functions.lib.php:156
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition: functions.lib.php:1741
float
div float
Buy price without taxes.
Definition: style.css.php:921
Generated on Mon Apr 1 2024 01:00:38 for dolibarr by Doxygen 1.9.1