dolibarr  16.0.5
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)
833  {
834  global $conf;
835 
836  dol_syslog(__METHOD__, LOG_DEBUG);
837 
838  $this->lines = array();
839 
840  $sql = 'SELECT';
841  $sql .= ' t.rowid,';
842  $sql .= " t.doc_date,";
843  $sql .= " t.doc_type,";
844  $sql .= " t.doc_ref,";
845  $sql .= " t.fk_doc,";
846  $sql .= " t.fk_docdet,";
847  $sql .= " t.thirdparty_code,";
848  $sql .= " t.subledger_account,";
849  $sql .= " t.subledger_label,";
850  $sql .= " t.numero_compte,";
851  $sql .= " t.label_compte,";
852  $sql .= " t.label_operation,";
853  $sql .= " t.debit,";
854  $sql .= " t.credit,";
855  $sql .= " t.montant as amount,";
856  $sql .= " t.sens,";
857  $sql .= " t.multicurrency_amount,";
858  $sql .= " t.multicurrency_code,";
859  $sql .= " t.lettering_code,";
860  $sql .= " t.date_lettering,";
861  $sql .= " t.fk_user_author,";
862  $sql .= " t.import_key,";
863  $sql .= " t.code_journal,";
864  $sql .= " t.journal_label,";
865  $sql .= " t.piece_num,";
866  $sql .= " t.date_creation,";
867  $sql .= " t.date_export,";
868  $sql .= " t.date_validated as date_validation,";
869  $sql .= " t.import_key";
870  // Manage filter
871  $sqlwhere = array();
872  if (count($filter) > 0) {
873  foreach ($filter as $key => $value) {
874  if ($key == 't.doc_date') {
875  $sqlwhere[] = $key.'=\''.$this->db->idate($value).'\'';
876  } elseif ($key == 't.doc_date>=' || $key == 't.doc_date<=') {
877  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
878  } elseif ($key == 't.numero_compte>=' || $key == 't.numero_compte<=' || $key == 't.subledger_account>=' || $key == 't.subledger_account<=') {
879  $sqlwhere[] = $key.'\''.$this->db->escape($value).'\'';
880  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
881  $sqlwhere[] = $key.'='.$value;
882  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
883  $sqlwhere[] = $key.' LIKE \''.$this->db->escape($value).'%\'';
884  } elseif ($key == 't.date_creation>=' || $key == 't.date_creation<=') {
885  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
886  } elseif ($key == 't.date_export>=' || $key == 't.date_export<=') {
887  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
888  } elseif ($key == 't.date_validated>=' || $key == 't.date_validated<=') {
889  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
890  } elseif ($key == 't.credit' || $key == 't.debit') {
891  $sqlwhere[] = natural_search($key, $value, 1, 1);
892  } elseif ($key == 't.reconciled_option') {
893  $sqlwhere[] = 't.lettering_code IS NULL';
894  } elseif ($key == 't.code_journal' && !empty($value)) {
895  if (is_array($value)) {
896  $sqlwhere[] = natural_search("t.code_journal", join(',', $value), 3, 1);
897  } else {
898  $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
899  }
900  } else {
901  $sqlwhere[] = natural_search($key, $value, 0, 1);
902  }
903  }
904  }
905  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
906  $sql .= ' WHERE 1 = 1';
907  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
908  if (count($sqlwhere) > 0) {
909  $sql .= " AND ".implode(" ".$filtermode." ", $sqlwhere);
910  }
911  // Affichage par compte comptable
912  if (!empty($option)) {
913  $sql .= " AND t.subledger_account IS NOT NULL";
914  $sql .= " AND t.subledger_account <> ''";
915  $sortfield = 't.subledger_account'.($sortfield ? ','.$sortfield : '');
916  $sortorder = 'ASC'.($sortfield ? ','.$sortfield : '');
917  } else {
918  $sortfield = 't.numero_compte'.($sortfield ? ','.$sortfield : '');
919  $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
920  }
921 
922  $sql .= $this->db->order($sortfield, $sortorder);
923  if (!empty($limit)) {
924  $sql .= $this->db->plimit($limit + 1, $offset);
925  }
926 
927  $resql = $this->db->query($sql);
928  if ($resql) {
929  $num = $this->db->num_rows($resql);
930 
931  $i = 0;
932  while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
933  $line = new BookKeepingLine();
934 
935  $line->id = $obj->rowid;
936 
937  $line->doc_date = $this->db->jdate($obj->doc_date);
938  $line->doc_type = $obj->doc_type;
939  $line->doc_ref = $obj->doc_ref;
940  $line->fk_doc = $obj->fk_doc;
941  $line->fk_docdet = $obj->fk_docdet;
942  $line->thirdparty_code = $obj->thirdparty_code;
943  $line->subledger_account = $obj->subledger_account;
944  $line->subledger_label = $obj->subledger_label;
945  $line->numero_compte = $obj->numero_compte;
946  $line->label_compte = $obj->label_compte;
947  $line->label_operation = $obj->label_operation;
948  $line->debit = $obj->debit;
949  $line->credit = $obj->credit;
950  $line->montant = $obj->amount; // deprecated
951  $line->amount = $obj->amount;
952  $line->sens = $obj->sens;
953  $line->multicurrency_amount = $obj->multicurrency_amount;
954  $line->multicurrency_code = $obj->multicurrency_code;
955  $line->lettering_code = $obj->lettering_code;
956  $line->date_lettering = $obj->date_lettering;
957  $line->fk_user_author = $obj->fk_user_author;
958  $line->import_key = $obj->import_key;
959  $line->code_journal = $obj->code_journal;
960  $line->journal_label = $obj->journal_label;
961  $line->piece_num = $obj->piece_num;
962  $line->date_creation = $this->db->jdate($obj->date_creation);
963  $line->date_export = $this->db->jdate($obj->date_export);
964  $line->date_validation = $this->db->jdate($obj->date_validation);
965  $line->import_key = $obj->import_key;
966 
967  $this->lines[] = $line;
968 
969  $i++;
970  }
971  $this->db->free($resql);
972 
973  return $num;
974  } else {
975  $this->errors[] = 'Error '.$this->db->lasterror();
976  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
977 
978  return -1;
979  }
980  }
981 
994  public function fetchAll($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, array $filter = array(), $filtermode = 'AND', $showAlreadyExportMovements = 1)
995  {
996  global $conf;
997 
998  dol_syslog(__METHOD__, LOG_DEBUG);
999 
1000  $sql = 'SELECT';
1001  $sql .= ' t.rowid,';
1002  $sql .= " t.doc_date,";
1003  $sql .= " t.doc_type,";
1004  $sql .= " t.doc_ref,";
1005  $sql .= " t.fk_doc,";
1006  $sql .= " t.fk_docdet,";
1007  $sql .= " t.thirdparty_code,";
1008  $sql .= " t.subledger_account,";
1009  $sql .= " t.subledger_label,";
1010  $sql .= " t.numero_compte,";
1011  $sql .= " t.label_compte,";
1012  $sql .= " t.label_operation,";
1013  $sql .= " t.debit,";
1014  $sql .= " t.credit,";
1015  $sql .= " t.lettering_code,";
1016  $sql .= " t.date_lettering,";
1017  $sql .= " t.montant as amount,";
1018  $sql .= " t.sens,";
1019  $sql .= " t.fk_user_author,";
1020  $sql .= " t.import_key,";
1021  $sql .= " t.code_journal,";
1022  $sql .= " t.journal_label,";
1023  $sql .= " t.piece_num,";
1024  $sql .= " t.date_creation,";
1025  $sql .= " t.date_lim_reglement,";
1026  $sql .= " t.tms as date_modification,";
1027  $sql .= " t.date_export,";
1028  $sql .= " t.date_validated as date_validation";
1029  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
1030  // Manage filter
1031  $sqlwhere = array();
1032  if (count($filter) > 0) {
1033  foreach ($filter as $key => $value) {
1034  if ($key == 't.doc_date') {
1035  $sqlwhere[] = $key.'=\''.$this->db->idate($value).'\'';
1036  } elseif ($key == 't.doc_date>=' || $key == 't.doc_date<=') {
1037  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
1038  } elseif ($key == 't.numero_compte>=' || $key == 't.numero_compte<=' || $key == 't.subledger_account>=' || $key == 't.subledger_account<=') {
1039  $sqlwhere[] = $key.'\''.$this->db->escape($value).'\'';
1040  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1041  $sqlwhere[] = $key.'='.((int) $value);
1042  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1043  $sqlwhere[] = $key.' LIKE \''.$this->db->escape($value).'%\'';
1044  } elseif ($key == 't.date_creation>=' || $key == 't.date_creation<=') {
1045  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
1046  } elseif ($key == 't.tms>=' || $key == 't.tms<=') {
1047  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
1048  } elseif ($key == 't.date_export>=' || $key == 't.date_export<=') {
1049  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
1050  } elseif ($key == 't.date_validated>=' || $key == 't.date_validated<=') {
1051  $sqlwhere[] = $key.'\''.$this->db->idate($value).'\'';
1052  } elseif ($key == 't.credit' || $key == 't.debit') {
1053  $sqlwhere[] = natural_search($key, $value, 1, 1);
1054  } elseif ($key == 't.code_journal' && !empty($value)) {
1055  if (is_array($value)) {
1056  $sqlwhere[] = natural_search("t.code_journal", join(',', $value), 3, 1);
1057  } else {
1058  $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1059  }
1060  } else {
1061  $sqlwhere[] = natural_search($key, $value, 0, 1);
1062  }
1063  }
1064  }
1065  $sql .= ' WHERE t.entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1066  if ($showAlreadyExportMovements == 0) {
1067  $sql .= " AND t.date_export IS NULL";
1068  }
1069  if (count($sqlwhere) > 0) {
1070  $sql .= ' AND '.implode(" ".$filtermode." ", $sqlwhere);
1071  }
1072  if (!empty($sortfield)) {
1073  $sql .= $this->db->order($sortfield, $sortorder);
1074  }
1075  if (!empty($limit)) {
1076  $sql .= $this->db->plimit($limit + 1, $offset);
1077  }
1078  $this->lines = array();
1079 
1080  $resql = $this->db->query($sql);
1081  if ($resql) {
1082  $num = $this->db->num_rows($resql);
1083 
1084  $i = 0;
1085  while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1086  $line = new BookKeepingLine();
1087 
1088  $line->id = $obj->rowid;
1089 
1090  $line->doc_date = $this->db->jdate($obj->doc_date);
1091  $line->doc_type = $obj->doc_type;
1092  $line->doc_ref = $obj->doc_ref;
1093  $line->fk_doc = $obj->fk_doc;
1094  $line->fk_docdet = $obj->fk_docdet;
1095  $line->thirdparty_code = $obj->thirdparty_code;
1096  $line->subledger_account = $obj->subledger_account;
1097  $line->subledger_label = $obj->subledger_label;
1098  $line->numero_compte = $obj->numero_compte;
1099  $line->label_compte = $obj->label_compte;
1100  $line->label_operation = $obj->label_operation;
1101  $line->debit = $obj->debit;
1102  $line->credit = $obj->credit;
1103  $line->montant = $obj->amount; // deprecated
1104  $line->amount = $obj->amount;
1105  $line->sens = $obj->sens;
1106  $line->lettering_code = $obj->lettering_code;
1107  $line->date_lettering = $obj->date_lettering;
1108  $line->fk_user_author = $obj->fk_user_author;
1109  $line->import_key = $obj->import_key;
1110  $line->code_journal = $obj->code_journal;
1111  $line->journal_label = $obj->journal_label;
1112  $line->piece_num = $obj->piece_num;
1113  $line->date_creation = $this->db->jdate($obj->date_creation);
1114  $line->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
1115  $line->date_modification = $this->db->jdate($obj->date_modification);
1116  $line->date_export = $this->db->jdate($obj->date_export);
1117  $line->date_validation = $this->db->jdate($obj->date_validation);
1118 
1119  $this->lines[] = $line;
1120 
1121  $i++;
1122  }
1123  $this->db->free($resql);
1124 
1125  return $num;
1126  } else {
1127  $this->errors[] = 'Error '.$this->db->lasterror();
1128  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1129  return -1;
1130  }
1131  }
1132 
1144  public function fetchAllBalance($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, array $filter = array(), $filtermode = 'AND')
1145  {
1146  global $conf;
1147 
1148  $this->lines = array();
1149 
1150  dol_syslog(__METHOD__, LOG_DEBUG);
1151 
1152  $sql = 'SELECT';
1153  $sql .= " t.numero_compte,";
1154  $sql .= " SUM(t.debit) as debit,";
1155  $sql .= " SUM(t.credit) as credit";
1156  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
1157  // Manage filter
1158  $sqlwhere = array();
1159  if (count($filter) > 0) {
1160  foreach ($filter as $key => $value) {
1161  if ($key == 't.doc_date') {
1162  $sqlwhere[] = $key." = '".$this->db->idate($value)."'";
1163  } elseif ($key == 't.doc_date>=' || $key == 't.doc_date<=' || $key == 't.doc_date>' || $key == 't.doc_date<') {
1164  $sqlwhere[] = $key."'".$this->db->idate($value)."'";
1165  } elseif ($key == 't.numero_compte>=' || $key == 't.numero_compte<=' || $key == 't.subledger_account>=' || $key == 't.subledger_account<=') {
1166  $sqlwhere[] = $key."'".$this->db->escape($value)."'";
1167  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1168  $sqlwhere[] = $key." = ".((int) $value);
1169  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1170  $sqlwhere[] = $key." LIKE '".$this->db->escape($value)."%'";
1171  } elseif ($key == 't.subledger_label') {
1172  $sqlwhere[] = $key." LIKE '".$this->db->escape($value)."%'";
1173  } elseif ($key == 't.code_journal' && !empty($value)) {
1174  if (is_array($value)) {
1175  $sqlwhere[] = natural_search("t.code_journal", join(',', $value), 3, 1);
1176  } else {
1177  $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1178  }
1179  } else {
1180  $sqlwhere[] = $key." LIKE '%".$this->db->escape($value)."%'";
1181  }
1182  }
1183  }
1184  $sql .= ' WHERE entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1185  if (count($sqlwhere) > 0) {
1186  $sql .= " AND ".implode(" ".$filtermode." ", $sqlwhere);
1187  }
1188 
1189  $sql .= ' GROUP BY t.numero_compte';
1190 
1191  if (!empty($sortfield)) {
1192  $sql .= $this->db->order($sortfield, $sortorder);
1193  }
1194  if (!empty($limit)) {
1195  $sql .= $this->db->plimit($limit + 1, $offset);
1196  }
1197 
1198  $resql = $this->db->query($sql);
1199  if ($resql) {
1200  $num = $this->db->num_rows($resql);
1201 
1202  $i = 0;
1203  while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1204  $line = new BookKeepingLine();
1205 
1206  $line->numero_compte = $obj->numero_compte;
1207  $line->debit = $obj->debit;
1208  $line->credit = $obj->credit;
1209 
1210  $this->lines[] = $line;
1211 
1212  $i++;
1213  }
1214  $this->db->free($resql);
1215 
1216  return $num;
1217  } else {
1218  $this->errors[] = 'Error '.$this->db->lasterror();
1219  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1220 
1221  return -1;
1222  }
1223  }
1224 
1233  public function update(User $user, $notrigger = false, $mode = '')
1234  {
1235  $error = 0;
1236 
1237  dol_syslog(__METHOD__, LOG_DEBUG);
1238 
1239  // Clean parameters
1240  if (isset($this->doc_type)) {
1241  $this->doc_type = trim($this->doc_type);
1242  }
1243  if (isset($this->doc_ref)) {
1244  $this->doc_ref = trim($this->doc_ref);
1245  }
1246  if (isset($this->fk_doc)) {
1247  $this->fk_doc = (int) $this->fk_doc;
1248  }
1249  if (isset($this->fk_docdet)) {
1250  $this->fk_docdet = (int) $this->fk_docdet;
1251  }
1252  if (isset($this->thirdparty_code)) {
1253  $this->thirdparty_code = trim($this->thirdparty_code);
1254  }
1255  if (isset($this->subledger_account)) {
1256  $this->subledger_account = trim($this->subledger_account);
1257  }
1258  if (isset($this->subledger_label)) {
1259  $this->subledger_label = trim($this->subledger_label);
1260  }
1261  if (isset($this->numero_compte)) {
1262  $this->numero_compte = trim($this->numero_compte);
1263  }
1264  if (isset($this->label_compte)) {
1265  $this->label_compte = trim($this->label_compte);
1266  }
1267  if (isset($this->label_operation)) {
1268  $this->label_operation = trim($this->label_operation);
1269  }
1270  if (isset($this->debit)) {
1271  $this->debit = trim($this->debit);
1272  }
1273  if (isset($this->credit)) {
1274  $this->credit = trim($this->credit);
1275  }
1276  if (isset($this->amount)) {
1277  $this->amount = trim($this->amount);
1278  }
1279  if (isset($this->sens)) {
1280  $this->sens = trim($this->sens);
1281  }
1282  if (isset($this->import_key)) {
1283  $this->import_key = trim($this->import_key);
1284  }
1285  if (isset($this->code_journal)) {
1286  $this->code_journal = trim($this->code_journal);
1287  }
1288  if (isset($this->journal_label)) {
1289  $this->journal_label = trim($this->journal_label);
1290  }
1291  if (isset($this->piece_num)) {
1292  $this->piece_num = trim($this->piece_num);
1293  }
1294 
1295  $this->debit = price2num($this->debit, 'MT');
1296  $this->credit = price2num($this->credit, 'MT');
1297 
1298  // Check parameters
1299  // Put here code to add a control on parameters values
1300 
1301  // Update request
1302  $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element.$mode.' SET';
1303  $sql .= ' doc_date = '.(!isset($this->doc_date) || dol_strlen($this->doc_date) != 0 ? "'".$this->db->idate($this->doc_date)."'" : 'null').',';
1304  $sql .= ' doc_type = '.(isset($this->doc_type) ? "'".$this->db->escape($this->doc_type)."'" : "null").',';
1305  $sql .= ' doc_ref = '.(isset($this->doc_ref) ? "'".$this->db->escape($this->doc_ref)."'" : "null").',';
1306  $sql .= ' fk_doc = '.(isset($this->fk_doc) ? $this->fk_doc : "null").',';
1307  $sql .= ' fk_docdet = '.(isset($this->fk_docdet) ? $this->fk_docdet : "null").',';
1308  $sql .= ' thirdparty_code = '.(isset($this->thirdparty_code) ? "'".$this->db->escape($this->thirdparty_code)."'" : "null").',';
1309  $sql .= ' subledger_account = '.(isset($this->subledger_account) ? "'".$this->db->escape($this->subledger_account)."'" : "null").',';
1310  $sql .= ' subledger_label = '.(isset($this->subledger_label) ? "'".$this->db->escape($this->subledger_label)."'" : "null").',';
1311  $sql .= ' numero_compte = '.(isset($this->numero_compte) ? "'".$this->db->escape($this->numero_compte)."'" : "null").',';
1312  $sql .= ' label_compte = '.(isset($this->label_compte) ? "'".$this->db->escape($this->label_compte)."'" : "null").',';
1313  $sql .= ' label_operation = '.(isset($this->label_operation) ? "'".$this->db->escape($this->label_operation)."'" : "null").',';
1314  $sql .= ' debit = '.(isset($this->debit) ? $this->debit : "null").',';
1315  $sql .= ' credit = '.(isset($this->credit) ? $this->credit : "null").',';
1316  $sql .= ' montant = '.(isset($this->montant) ? $this->montant : "null").',';
1317  $sql .= ' sens = '.(isset($this->sens) ? "'".$this->db->escape($this->sens)."'" : "null").',';
1318  $sql .= ' fk_user_author = '.(isset($this->fk_user_author) ? $this->fk_user_author : "null").',';
1319  $sql .= ' import_key = '.(isset($this->import_key) ? "'".$this->db->escape($this->import_key)."'" : "null").',';
1320  $sql .= ' code_journal = '.(isset($this->code_journal) ? "'".$this->db->escape($this->code_journal)."'" : "null").',';
1321  $sql .= ' journal_label = '.(isset($this->journal_label) ? "'".$this->db->escape($this->journal_label)."'" : "null").',';
1322  $sql .= ' piece_num = '.(isset($this->piece_num) ? $this->piece_num : "null");
1323  $sql .= ' WHERE rowid='.((int) $this->id);
1324 
1325  $this->db->begin();
1326 
1327  $resql = $this->db->query($sql);
1328  if (!$resql) {
1329  $error++;
1330  $this->errors[] = 'Error '.$this->db->lasterror();
1331  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1332  }
1333 
1334  // Uncomment this and change MYOBJECT to your own tag if you
1335  // want this action calls a trigger.
1336  //if (! $error && ! $notrigger) {
1337 
1338  // // Call triggers
1339  // $result=$this->call_trigger('MYOBJECT_MODIFY',$user);
1340  // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1341  // // End call triggers
1342  //}
1343 
1344  // Commit or rollback
1345  if ($error) {
1346  $this->db->rollback();
1347 
1348  return -1 * $error;
1349  } else {
1350  $this->db->commit();
1351 
1352  return 1;
1353  }
1354  }
1355 
1365  public function updateByMvt($piece_num = '', $field = '', $value = '', $mode = '')
1366  {
1367  $error = 0;
1368 
1369  $this->db->begin();
1370 
1371  $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element.$mode;
1372  $sql .= " SET ".$field." = ".(is_numeric($value) ? ((float) $value) : "'".$this->db->escape($value)."'");
1373  $sql .= " WHERE piece_num = ".((int) $piece_num);
1374 
1375  $resql = $this->db->query($sql);
1376 
1377  if (!$resql) {
1378  $error++;
1379  $this->errors[] = 'Error '.$this->db->lasterror();
1380  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1381  }
1382  if ($error) {
1383  $this->db->rollback();
1384 
1385  return -1 * $error;
1386  } else {
1387  $this->db->commit();
1388 
1389  return 1;
1390  }
1391  }
1392 
1401  public function delete(User $user, $notrigger = false, $mode = '')
1402  {
1403  dol_syslog(__METHOD__, LOG_DEBUG);
1404 
1405  $error = 0;
1406 
1407  $this->db->begin();
1408 
1409  // Uncomment this and change MYOBJECT to your own tag if you
1410  // want this action calls a trigger.
1411  //if (! $error && ! $notrigger) {
1412 
1413  // // Call triggers
1414  // $result=$this->call_trigger('MYOBJECT_DELETE',$user);
1415  // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1416  // // End call triggers
1417  //}
1418 
1419  if (!$error) {
1420  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.$mode;
1421  $sql .= ' WHERE rowid='.((int) $this->id);
1422 
1423  $resql = $this->db->query($sql);
1424  if (!$resql) {
1425  $error++;
1426  $this->errors[] = 'Error '.$this->db->lasterror();
1427  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1428  }
1429  }
1430 
1431  // Commit or rollback
1432  if ($error) {
1433  $this->db->rollback();
1434 
1435  return -1 * $error;
1436  } else {
1437  $this->db->commit();
1438 
1439  return 1;
1440  }
1441  }
1442 
1449  public function deleteByImportkey($importkey)
1450  {
1451  $this->db->begin();
1452 
1453  // first check if line not yet in bookkeeping
1454  $sql = "DELETE";
1455  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
1456  $sql .= " WHERE import_key = '".$this->db->escape($importkey)."'";
1457 
1458  $resql = $this->db->query($sql);
1459 
1460  if (!$resql) {
1461  $this->errors[] = "Error ".$this->db->lasterror();
1462  dol_syslog(get_class($this)."::delete Error ".$this->db->lasterror(), LOG_ERR);
1463  $this->db->rollback();
1464  return -1;
1465  }
1466 
1467  $this->db->commit();
1468  return 1;
1469  }
1470 
1480  public function deleteByYearAndJournal($delyear = 0, $journal = '', $mode = '', $delmonth = 0)
1481  {
1482  global $conf, $langs;
1483 
1484  if (empty($delyear) && empty($journal)) {
1485  $this->error = 'ErrorOneFieldRequired';
1486  return -1;
1487  }
1488  if (!empty($delmonth) && empty($delyear)) {
1489  $this->error = 'YearRequiredIfMonthDefined';
1490  return -2;
1491  }
1492 
1493  $this->db->begin();
1494 
1495  // Delete record in bookkeeping
1496  $sql = "DELETE";
1497  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1498  $sql .= " WHERE 1 = 1";
1499  $sql .= dolSqlDateFilter('doc_date', 0, $delmonth, $delyear);
1500  if (!empty($journal)) {
1501  $sql .= " AND code_journal = '".$this->db->escape($journal)."'";
1502  }
1503  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1504  // Exclusion of validated entries at the time of deletion
1505  $sql .= " AND date_validated IS NULL";
1506 
1507  // TODO: In a future we must forbid deletion if record is inside a closed fiscal period.
1508 
1509  $resql = $this->db->query($sql);
1510 
1511  if (!$resql) {
1512  $this->errors[] = "Error ".$this->db->lasterror();
1513  foreach ($this->errors as $errmsg) {
1514  dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1515  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1516  }
1517  $this->db->rollback();
1518  return -1;
1519  }
1520 
1521  $this->db->commit();
1522  return 1;
1523  }
1524 
1531  public function deleteMvtNum($piecenum)
1532  {
1533  global $conf;
1534 
1535  $this->db->begin();
1536 
1537  // first check if line not yet in bookkeeping
1538  $sql = "DELETE";
1539  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
1540  $sql .= " WHERE piece_num = ".(int) $piecenum;
1541  $sql .= " AND date_validated IS NULL"; // For security, exclusion of validated entries at the time of deletion
1542  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1543 
1544  $resql = $this->db->query($sql);
1545 
1546  if (!$resql) {
1547  $this->errors[] = "Error ".$this->db->lasterror();
1548  foreach ($this->errors as $errmsg) {
1549  dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1550  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1551  }
1552  $this->db->rollback();
1553  return -1;
1554  }
1555 
1556  $this->db->commit();
1557  return 1;
1558  }
1559 
1567  public function createFromClone(User $user, $fromid)
1568  {
1569  dol_syslog(__METHOD__, LOG_DEBUG);
1570 
1571  $error = 0;
1572  $object = new BookKeeping($this->db);
1573 
1574  $this->db->begin();
1575 
1576  // Load source object
1577  $object->fetch($fromid);
1578  // Reset object
1579  $object->id = 0;
1580 
1581  // Clear fields
1582  // ...
1583 
1584  // Create clone
1585  $object->context['createfromclone'] = 'createfromclone';
1586  $result = $object->create($user);
1587 
1588  // Other options
1589  if ($result < 0) {
1590  $error++;
1591  $this->errors = $object->errors;
1592  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1593  }
1594 
1595  unset($object->context['createfromclone']);
1596 
1597  // End
1598  if (!$error) {
1599  $this->db->commit();
1600 
1601  return $object->id;
1602  } else {
1603  $this->db->rollback();
1604 
1605  return -1;
1606  }
1607  }
1608 
1615  public function initAsSpecimen()
1616  {
1617  global $user;
1618 
1619  $now = dol_now();
1620 
1621  $this->id = 0;
1622  $this->doc_date = $now;
1623  $this->doc_type = '';
1624  $this->doc_ref = '';
1625  $this->fk_doc = 0;
1626  $this->fk_docdet = 0;
1627  $this->thirdparty_code = 'CU001';
1628  $this->subledger_account = '41100001';
1629  $this->subledger_label = 'My customer company';
1630  $this->numero_compte = '411';
1631  $this->label_compte = 'Customer';
1632  $this->label_operation = 'Sales of pea';
1633  $this->debit = 99.9;
1634  $this->credit = 0.0;
1635  $this->amount = 0.0;
1636  $this->sens = 'D';
1637  $this->fk_user_author = $user->id;
1638  $this->import_key = '20201027';
1639  $this->code_journal = 'VT';
1640  $this->journal_label = 'Journal de vente';
1641  $this->piece_num = 1234;
1642  $this->date_creation = $now;
1643  }
1644 
1652  public function fetchPerMvt($piecenum, $mode = '')
1653  {
1654  global $conf;
1655 
1656  $sql = "SELECT piece_num, doc_date,code_journal, journal_label, doc_ref, doc_type,";
1657  $sql .= " date_creation, tms as date_modification, date_validated as date_validation";
1658  // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1659  if ($mode != "_tmp") {
1660  $sql .= ", date_export";
1661  }
1662  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1663  $sql .= " WHERE piece_num = ".((int) $piecenum);
1664  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1665 
1666  dol_syslog(__METHOD__, LOG_DEBUG);
1667  $result = $this->db->query($sql);
1668  if ($result) {
1669  $obj = $this->db->fetch_object($result);
1670 
1671  $this->piece_num = $obj->piece_num;
1672  $this->code_journal = $obj->code_journal;
1673  $this->journal_label = $obj->journal_label;
1674  $this->doc_date = $this->db->jdate($obj->doc_date);
1675  $this->doc_ref = $obj->doc_ref;
1676  $this->doc_type = $obj->doc_type;
1677  $this->date_creation = $this->db->jdate($obj->date_creation);
1678  $this->date_modification = $this->db->jdate($obj->date_modification);
1679  if ($mode != "_tmp") {
1680  $this->date_export = $this->db->jdate($obj->date_export);
1681  }
1682  $this->date_validation = $this->db->jdate($obj->date_validation);
1683  } else {
1684  $this->error = "Error ".$this->db->lasterror();
1685  dol_syslog(__METHOD__.$this->error, LOG_ERR);
1686  return -1;
1687  }
1688 
1689  return 1;
1690  }
1691 
1698  public function getNextNumMvt($mode = '')
1699  {
1700  global $conf;
1701 
1702  $sql = "SELECT MAX(piece_num)+1 as max FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1703  $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1704 
1705  dol_syslog(get_class($this)."::getNextNumMvt", LOG_DEBUG);
1706 
1707  $result = $this->db->query($sql);
1708 
1709  if ($result) {
1710  $obj = $this->db->fetch_object($result);
1711  if ($obj) {
1712  $result = $obj->max;
1713  }
1714  if (empty($result)) {
1715  $result = 1;
1716  }
1717  return $result;
1718  } else {
1719  $this->error = "Error ".$this->db->lasterror();
1720  dol_syslog(get_class($this)."::getNextNumMvt ".$this->error, LOG_ERR);
1721  return -1;
1722  }
1723  }
1724 
1732  public function fetchAllPerMvt($piecenum, $mode = '')
1733  {
1734  global $conf;
1735 
1736  $sql = "SELECT rowid, doc_date, doc_type,";
1737  $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1738  $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1739  $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, journal_label, piece_num,";
1740  $sql .= " date_creation, tms as date_modification, date_validated as date_validation";
1741  // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1742  if ($mode != "_tmp") {
1743  $sql .= ", date_export";
1744  }
1745  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1746  $sql .= " WHERE piece_num = ".((int) $piecenum);
1747  $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1748 
1749  dol_syslog(__METHOD__, LOG_DEBUG);
1750  $result = $this->db->query($sql);
1751  if ($result) {
1752  while ($obj = $this->db->fetch_object($result)) {
1753  $line = new BookKeepingLine();
1754 
1755  $line->id = $obj->rowid;
1756 
1757  $line->doc_date = $this->db->jdate($obj->doc_date);
1758  $line->doc_type = $obj->doc_type;
1759  $line->doc_ref = $obj->doc_ref;
1760  $line->fk_doc = $obj->fk_doc;
1761  $line->fk_docdet = $obj->fk_docdet;
1762  $line->thirdparty_code = $obj->thirdparty_code;
1763  $line->subledger_account = $obj->subledger_account;
1764  $line->subledger_label = $obj->subledger_label;
1765  $line->numero_compte = $obj->numero_compte;
1766  $line->label_compte = $obj->label_compte;
1767  $line->label_operation = $obj->label_operation;
1768  $line->debit = $obj->debit;
1769  $line->credit = $obj->credit;
1770  $line->montant = $obj->amount;
1771  $line->amount = $obj->amount;
1772  $line->sens = $obj->sens;
1773  $line->code_journal = $obj->code_journal;
1774  $line->journal_label = $obj->journal_label;
1775  $line->piece_num = $obj->piece_num;
1776  $line->date_creation = $obj->date_creation;
1777  $line->date_modification = $obj->date_modification;
1778  if ($mode != "_tmp") {
1779  $line->date_export = $obj->date_export;
1780  }
1781  $line->date_validation = $obj->date_validation;
1782 
1783  $this->linesmvt[] = $line;
1784  }
1785  } else {
1786  $this->error = "Error ".$this->db->lasterror();
1787  dol_syslog(__METHOD__.$this->error, LOG_ERR);
1788  return -1;
1789  }
1790 
1791  return 1;
1792  }
1793 
1794  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1801  public function export_bookkeeping($model = 'ebp')
1802  {
1803  // phpcs:enable
1804  global $conf;
1805 
1806  $sql = "SELECT rowid, doc_date, doc_type,";
1807  $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1808  $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1809  $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, piece_num,";
1810  $sql .= " date_validated as date_validation";
1811  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
1812  $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1813 
1814  dol_syslog(get_class($this)."::export_bookkeeping", LOG_DEBUG);
1815 
1816  $resql = $this->db->query($sql);
1817 
1818  if ($resql) {
1819  $this->linesexport = array();
1820 
1821  $num = $this->db->num_rows($resql);
1822  while ($obj = $this->db->fetch_object($resql)) {
1823  $line = new BookKeepingLine();
1824 
1825  $line->id = $obj->rowid;
1826 
1827  $line->doc_date = $this->db->jdate($obj->doc_date);
1828  $line->doc_type = $obj->doc_type;
1829  $line->doc_ref = $obj->doc_ref;
1830  $line->fk_doc = $obj->fk_doc;
1831  $line->fk_docdet = $obj->fk_docdet;
1832  $line->thirdparty_code = $obj->thirdparty_code;
1833  $line->subledger_account = $obj->subledger_account;
1834  $line->subledger_label = $obj->subledger_label;
1835  $line->numero_compte = $obj->numero_compte;
1836  $line->label_compte = $obj->label_compte;
1837  $line->label_operation = $obj->label_operation;
1838  $line->debit = $obj->debit;
1839  $line->credit = $obj->credit;
1840  $line->montant = $obj->amount;
1841  $line->amount = $obj->amount;
1842  $line->sens = $obj->sens;
1843  $line->code_journal = $obj->code_journal;
1844  $line->piece_num = $obj->piece_num;
1845  $line->date_validation = $obj->date_validation;
1846 
1847  $this->linesexport[] = $line;
1848  }
1849  $this->db->free($resql);
1850 
1851  return $num;
1852  } else {
1853  $this->error = "Error ".$this->db->lasterror();
1854  dol_syslog(get_class($this)."::export_bookkeeping ".$this->error, LOG_ERR);
1855  return -1;
1856  }
1857  }
1858 
1866  public function transformTransaction($direction = 0, $piece_num = '')
1867  {
1868  global $conf;
1869 
1870  $error = 0;
1871 
1872  $this->db->begin();
1873 
1874  if ($direction == 0) {
1875  $next_piecenum = $this->getNextNumMvt();
1876  $now = dol_now();
1877 
1878  if ($next_piecenum < 0) {
1879  $error++;
1880  }
1881 
1882  if (!$error) {
1883  // Delete if there is an empty line
1884  $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";
1885  $resql = $this->db->query($sql);
1886  if (!$resql) {
1887  $error++;
1888  $this->errors[] = 'Error '.$this->db->lasterror();
1889  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1890  }
1891  }
1892 
1893  if (!$error) {
1894  $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.' (doc_date, doc_type,';
1895  $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
1896  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1897  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num, date_creation)';
1898  $sql .= ' SELECT doc_date, doc_type,';
1899  $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
1900  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1901  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, '.((int) $next_piecenum).", '".$this->db->idate($now)."'";
1902  $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);
1903  $resql = $this->db->query($sql);
1904  if (!$resql) {
1905  $error++;
1906  $this->errors[] = 'Error '.$this->db->lasterror();
1907  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1908  }
1909  }
1910 
1911  if (!$error) {
1912  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1913  $resql = $this->db->query($sql);
1914  if (!$resql) {
1915  $error++;
1916  $this->errors[] = 'Error '.$this->db->lasterror();
1917  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1918  }
1919  }
1920  } elseif ($direction == 1) {
1921  if (!$error) {
1922  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1923  $resql = $this->db->query($sql);
1924  if (!$resql) {
1925  $error++;
1926  $this->errors[] = 'Error '.$this->db->lasterror();
1927  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1928  }
1929  }
1930 
1931  if (!$error) {
1932  $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.'_tmp (doc_date, doc_type,';
1933  $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1934  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1935  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num)';
1936  $sql .= ' SELECT doc_date, doc_type,';
1937  $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1938  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1939  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num';
1940  $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1941  $resql = $this->db->query($sql);
1942  if (!$resql) {
1943  $error++;
1944  $this->errors[] = 'Error '.$this->db->lasterror();
1945  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1946  }
1947  }
1948 
1949  if (!$error) {
1950  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1951  $resql = $this->db->query($sql);
1952  if (!$resql) {
1953  $error++;
1954  $this->errors[] = 'Error '.$this->db->lasterror();
1955  dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1956  }
1957  }
1958  }
1959  if (!$error) {
1960  $this->db->commit();
1961  return 1;
1962  } else {
1963  $this->db->rollback();
1964  return -1;
1965  }
1966  /*
1967  $sql = "DELETE FROM ";
1968  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as ab";
1969  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as aa ON aa.account_number = ab.numero_compte";
1970  $sql .= " AND aa.active = 1";
1971  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
1972  $sql .= " AND asy.rowid = " . ((int) $pcgver);
1973  $sql .= " AND ab.entity IN (" . getEntity('accountancy') . ")";
1974  $sql .= " ORDER BY account_number ASC";
1975  */
1976  }
1977 
1978  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1991  public function select_account($selectid, $htmlname = 'account', $showempty = 0, $event = array(), $select_in = 0, $select_out = 0, $aabase = '')
1992  {
1993  // phpcs:enable
1994  global $conf;
1995 
1996  require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
1997 
1998  $pcgver = $conf->global->CHARTOFACCOUNTS;
1999 
2000  $sql = "SELECT DISTINCT ab.numero_compte as account_number, aa.label as label, aa.rowid as rowid, aa.fk_pcg_version";
2001  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping as ab";
2002  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as aa ON aa.account_number = ab.numero_compte";
2003  $sql .= " AND aa.active = 1";
2004  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2005  $sql .= " AND asy.rowid = ".((int) $pcgver);
2006  $sql .= " AND ab.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2007  $sql .= " ORDER BY account_number ASC";
2008 
2009  dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2010  $resql = $this->db->query($sql);
2011 
2012  if (!$resql) {
2013  $this->error = "Error ".$this->db->lasterror();
2014  dol_syslog(get_class($this)."::select_account ".$this->error, LOG_ERR);
2015  return -1;
2016  }
2017 
2018  $out = ajax_combobox($htmlname, $event);
2019 
2020  $options = array();
2021  $selected = null;
2022 
2023  while ($obj = $this->db->fetch_object($resql)) {
2024  $label = length_accountg($obj->account_number).' - '.$obj->label;
2025 
2026  $select_value_in = $obj->rowid;
2027  $select_value_out = $obj->rowid;
2028 
2029  if ($select_in == 1) {
2030  $select_value_in = $obj->account_number;
2031  }
2032  if ($select_out == 1) {
2033  $select_value_out = $obj->account_number;
2034  }
2035 
2036  // Remember guy's we store in database llx_facturedet the rowid of accounting_account and not the account_number
2037  // Because same account_number can be share between different accounting_system and do have the same meaning
2038  if (($selectid != '') && $selectid == $select_value_in) {
2039  $selected = $select_value_out;
2040  }
2041 
2042  $options[$select_value_out] = $label;
2043  }
2044 
2045  $out .= Form::selectarray($htmlname, $options, $selected, $showempty, 0, 0, '', 0, 0, 0, '', 'maxwidth300');
2046  $this->db->free($resql);
2047  return $out;
2048  }
2049 
2057  public function getRootAccount($account = null)
2058  {
2059  global $conf;
2060  $pcgver = $conf->global->CHARTOFACCOUNTS;
2061 
2062  $sql = "SELECT root.rowid, root.account_number, root.label as label,";
2063  $sql .= " parent.rowid as parent_rowid, parent.account_number as parent_account_number, parent.label as parent_label";
2064  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa";
2065  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2066  $sql .= " AND asy.rowid = ".((int) $pcgver);
2067  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as parent ON aa.account_parent = parent.rowid AND parent.active = 1";
2068  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as root ON parent.account_parent = root.rowid AND root.active = 1";
2069  $sql .= " WHERE aa.account_number = '".$this->db->escape($account)."'";
2070  $sql .= " AND aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2071 
2072  dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2073  $resql = $this->db->query($sql);
2074  if ($resql) {
2075  $obj = '';
2076  if ($this->db->num_rows($resql)) {
2077  $obj = $this->db->fetch_object($resql);
2078  }
2079 
2080  $result = array('id'=>$obj->rowid, 'account_number'=>$obj->account_number, 'label'=>$obj->label);
2081  return $result;
2082  } else {
2083  $this->error = "Error ".$this->db->lasterror();
2084  dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2085 
2086  return -1;
2087  }
2088  }
2089 
2090  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2097  public function get_compte_desc($account = null)
2098  {
2099  // phpcs:enable
2100  global $conf;
2101 
2102  $pcgver = $conf->global->CHARTOFACCOUNTS;
2103  $sql = "SELECT aa.account_number, aa.label, aa.rowid, aa.fk_pcg_version, cat.label as category";
2104  $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa ";
2105  $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2106  $sql .= " AND aa.account_number = '".$this->db->escape($account)."'";
2107  $sql .= " AND asy.rowid = ".((int) $pcgver);
2108  $sql .= " AND aa.active = 1";
2109  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_accounting_category as cat ON aa.fk_accounting_category = cat.rowid";
2110  $sql .= " WHERE aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2111 
2112  dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2113  $resql = $this->db->query($sql);
2114  if ($resql) {
2115  $obj = '';
2116  if ($this->db->num_rows($resql)) {
2117  $obj = $this->db->fetch_object($resql);
2118  }
2119  if (empty($obj->category)) {
2120  return $obj->label;
2121  } else {
2122  return $obj->label.' ('.$obj->category.')';
2123  }
2124  } else {
2125  $this->error = "Error ".$this->db->lasterror();
2126  dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2127  return -1;
2128  }
2129  }
2130 }
2131 
2135 class BookKeepingLine
2136 {
2140  public $id;
2141 
2142  public $doc_date = '';
2143  public $doc_type;
2144  public $doc_ref;
2145 
2149  public $fk_doc;
2150 
2154  public $fk_docdet;
2155 
2156  public $thirdparty_code;
2157  public $subledger_account;
2158  public $subledger_label;
2159  public $numero_compte;
2160  public $label_compte;
2161  public $label_operation;
2162  public $debit;
2163  public $credit;
2164 
2169  public $montant;
2170 
2174  public $amount;
2175 
2179  public $multicurrency_amount;
2180 
2184  public $multicurrency_code;
2185 
2189  public $sens;
2190  public $lettering_code;
2191  public $date_lettering;
2192 
2196  public $fk_user_author;
2197 
2198  public $import_key;
2199  public $code_journal;
2200  public $journal_label;
2201  public $piece_num;
2202 
2206  public $date_creation;
2207 
2211  public $date_modification;
2212 
2216  public $date_export;
2217 
2221  public $date_validation;
2222 
2226  public $date_lim_reglement;
2227 }
BookKeeping\export_bookkeeping
export_bookkeeping($model='ebp')
Export bookkeeping.
Definition: bookkeeping.class.php:1801
ajax_combobox
ajax_combobox($htmlname, $events=array(), $minLengthToAutocomplete=0, $forcefocus=0, $widthTypeOfAutocomplete='resolve', $idforemptyvalue='-1')
Convert a html select field into an ajax combobox.
Definition: ajax.lib.php:438
db
$conf db
API class for accounts.
Definition: inc.php:41
DoliDB
Class to manage Dolibarr database access.
Definition: DoliDB.class.php:30
BookKeeping\fetchAllByAccount
fetchAllByAccount($sortorder='', $sortfield='', $limit=0, $offset=0, array $filter=array(), $filtermode='AND', $option=0)
Load object in memory from the database.
Definition: bookkeeping.class.php:832
BookKeeping\fetchPerMvt
fetchPerMvt($piecenum, $mode='')
Load an accounting document into memory from database.
Definition: bookkeeping.class.php:1652
BookKeepingLine
Class BookKeepingLine.
Definition: bookkeeping.class.php:2135
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:1449
BookKeeping\transformTransaction
transformTransaction($direction=0, $piece_num='')
Transform transaction.
Definition: bookkeeping.class.php:1866
BookKeeping\fetch
fetch($id, $ref=null, $mode='')
Load object in memory from the database.
Definition: bookkeeping.class.php:725
CommonObject
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Definition: commonobject.class.php:44
price2num
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
Definition: functions.lib.php:5661
BookKeeping\deleteByYearAndJournal
deleteByYearAndJournal($delyear=0, $journal='', $mode='', $delmonth=0)
Delete bookkeeping by year.
Definition: bookkeeping.class.php:1480
BookKeeping\createFromClone
createFromClone(User $user, $fromid)
Load an object from its id and create a new one in database.
Definition: bookkeeping.class.php:1567
BookKeeping\create
create(User $user, $notrigger=false)
Create object into database.
Definition: bookkeeping.class.php:205
BookKeeping\getNextNumMvt
getNextNumMvt($mode='')
Return next number movement.
Definition: bookkeeping.class.php:1698
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
length_accountg
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous)
Definition: accounting.lib.php:94
dol_syslog
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
Definition: functions.lib.php:1603
BookKeeping\deleteMvtNum
deleteMvtNum($piecenum)
Delete bookkeeping by piece number.
Definition: bookkeeping.class.php:1531
BookKeeping\updateByMvt
updateByMvt($piece_num='', $field='', $value='', $mode='')
Update accounting movement.
Definition: bookkeeping.class.php:1365
BookKeeping\update
update(User $user, $notrigger=false, $mode='')
Update object into database.
Definition: bookkeeping.class.php:1233
dol_strlen
dol_strlen($string, $stringencoding='UTF-8')
Make a strlen call.
Definition: functions.lib.php:3747
BookKeeping
Class to manage Ledger (General Ledger and Subledger)
Definition: bookkeeping.class.php:33
User
Class to manage Dolibarr users.
Definition: user.class.php:44
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:9420
BookKeeping\fetchAllBalance
fetchAllBalance($sortorder='', $sortfield='', $limit=0, $offset=0, array $filter=array(), $filtermode='AND')
Load object in memory from the database.
Definition: bookkeeping.class.php:1144
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:4211
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='', $addjscombo=1, $moreparamonempty='', $disablebademail=0, $nohtmlescape=0)
Return a HTML select string, built from an array of key+value.
Definition: html.form.class.php:7879
BookKeeping\__construct
__construct(DoliDB $db)
Constructor.
Definition: bookkeeping.class.php:193
dol_now
dol_now($mode='auto')
Return date for now.
Definition: functions.lib.php:2845
$resql
if(isModEnabled('facture') &&!empty($user->rights->facture->lire)) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->rights->fournisseur->facture->lire)||(isModEnabled('supplier_invoice') && $user->rights->supplier_invoice->lire)) if(isModEnabled('don') &&!empty($user->rights->don->lire)) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->rights->commande->lire &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $resql
Social contributions to pay.
Definition: index.php:742
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:334
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:994
BookKeeping\get_compte_desc
get_compte_desc($account=null)
Description of accounting account.
Definition: bookkeeping.class.php:2097
BookKeeping\fetchAllPerMvt
fetchAllPerMvt($piecenum, $mode='')
Load all informations of accountancy document.
Definition: bookkeeping.class.php:1732
BookKeeping\getRootAccount
getRootAccount($account=null)
Return id and description of a root accounting account.
Definition: bookkeeping.class.php:2057
BookKeeping\initAsSpecimen
initAsSpecimen()
Initialise object with example values Id must be 0 if object instance is a specimen.
Definition: bookkeeping.class.php:1615
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:1991
float
div float
Buy price without taxes.
Definition: style.css.php:809
Generated on Sun Feb 4 2024 01:00:42 for dolibarr by Doxygen 1.8.17