dolibarr 18.0.6
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
28require_once DOL_DOCUMENT_ROOT.'/core/class/commonobject.class.php';
29
33class 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 != -1 ? $this->numero_compte : $langs->trans("Unknown"));
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_validation) ? $this->db->jdate($obj->date_validation) : '';
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 if (!empty($option)) {
1171 $sql .= " t.subledger_account,";
1172 $sql .= " t.subledger_label,";
1173 }
1174 $sql .= " SUM(t.debit) as debit,";
1175 $sql .= " SUM(t.credit) as credit";
1176 $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' as t';
1177 // Manage filter
1178 $sqlwhere = array();
1179 if (count($filter) > 0) {
1180 foreach ($filter as $key => $value) {
1181 if ($key == 't.doc_date') {
1182 $sqlwhere[] = $key." = '".$this->db->idate($value)."'";
1183 } elseif ($key == 't.doc_date>=' || $key == 't.doc_date<=' || $key == 't.doc_date>' || $key == 't.doc_date<') {
1184 $sqlwhere[] = $key."'".$this->db->idate($value)."'";
1185 } elseif ($key == 't.numero_compte>=' || $key == 't.numero_compte<=' || $key == 't.subledger_account>=' || $key == 't.subledger_account<=') {
1186 $sqlwhere[] = $key."'".$this->db->escape($value)."'";
1187 } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
1188 $sqlwhere[] = $key." = ".((int) $value);
1189 } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
1190 $sqlwhere[] = $key." LIKE '".$this->db->escape($value)."%'";
1191 } elseif ($key == 't.subledger_label') {
1192 $sqlwhere[] = $key." LIKE '".$this->db->escape($value)."%'";
1193 } elseif ($key == 't.code_journal' && !empty($value)) {
1194 if (is_array($value)) {
1195 $sqlwhere[] = natural_search("t.code_journal", join(',', $value), 3, 1);
1196 } else {
1197 $sqlwhere[] = natural_search("t.code_journal", $value, 3, 1);
1198 }
1199 } elseif ($key == 't.reconciled_option') {
1200 $sqlwhere[] = 't.lettering_code IS NULL';
1201 } else {
1202 $sqlwhere[] = $key." LIKE '%".$this->db->escape($value)."%'";
1203 }
1204 }
1205 }
1206 $sql .= ' WHERE entity = ' . ((int) $conf->entity); // Do not use getEntity for accounting features
1207 if (count($sqlwhere) > 0) {
1208 $sql .= " AND ".implode(" ".$filtermode." ", $sqlwhere);
1209 }
1210
1211 if (!empty($option)) {
1212 $sql .= ' AND t.subledger_account IS NOT NULL';
1213 $sql .= ' AND t.subledger_account != ""';
1214 $sql .= ' GROUP BY t.numero_compte, t.subledger_account, t.subledger_label';
1215 $sortfield = 't.subledger_account'.($sortfield ? ','.$sortfield : '');
1216 $sortorder = 'ASC'.($sortfield ? ','.$sortfield : '');
1217 } else {
1218 $sql .= ' GROUP BY t.numero_compte';
1219 $sortfield = 't.numero_compte'.($sortfield ? ','.$sortfield : '');
1220 $sortorder = 'ASC'.($sortorder ? ','.$sortorder : '');
1221 }
1222
1223 if (!empty($sortfield)) {
1224 $sql .= $this->db->order($sortfield, $sortorder);
1225 }
1226 if (!empty($limit)) {
1227 $sql .= $this->db->plimit($limit + 1, $offset);
1228 }
1229
1230 $resql = $this->db->query($sql);
1231 if ($resql) {
1232 $num = $this->db->num_rows($resql);
1233
1234 $i = 0;
1235 while (($obj = $this->db->fetch_object($resql)) && (empty($limit) || $i < min($limit, $num))) {
1236 $line = new BookKeepingLine();
1237
1238 $line->numero_compte = $obj->numero_compte;
1239 $line->label_compte = $obj->label_compte;
1240 $line->subledger_account = $obj->subledger_account;
1241 $line->subledger_label = $obj->subledger_label;
1242 $line->debit = $obj->debit;
1243 $line->credit = $obj->credit;
1244
1245 $this->lines[] = $line;
1246
1247 $i++;
1248 }
1249 $this->db->free($resql);
1250
1251 return $num;
1252 } else {
1253 $this->errors[] = 'Error '.$this->db->lasterror();
1254 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1255
1256 return -1;
1257 }
1258 }
1259
1268 public function update(User $user, $notrigger = false, $mode = '')
1269 {
1270 $error = 0;
1271
1272 dol_syslog(__METHOD__, LOG_DEBUG);
1273
1274 // Clean parameters
1275 if (isset($this->doc_type)) {
1276 $this->doc_type = trim($this->doc_type);
1277 }
1278 if (isset($this->doc_ref)) {
1279 $this->doc_ref = trim($this->doc_ref);
1280 }
1281 if (isset($this->fk_doc)) {
1282 $this->fk_doc = (int) $this->fk_doc;
1283 }
1284 if (isset($this->fk_docdet)) {
1285 $this->fk_docdet = (int) $this->fk_docdet;
1286 }
1287 if (isset($this->thirdparty_code)) {
1288 $this->thirdparty_code = trim($this->thirdparty_code);
1289 }
1290 if (isset($this->subledger_account)) {
1291 $this->subledger_account = trim($this->subledger_account);
1292 }
1293 if (isset($this->subledger_label)) {
1294 $this->subledger_label = trim($this->subledger_label);
1295 }
1296 if (isset($this->numero_compte)) {
1297 $this->numero_compte = trim($this->numero_compte);
1298 }
1299 if (isset($this->label_compte)) {
1300 $this->label_compte = trim($this->label_compte);
1301 }
1302 if (isset($this->label_operation)) {
1303 $this->label_operation = trim($this->label_operation);
1304 }
1305 if (isset($this->debit)) {
1306 $this->debit = trim($this->debit);
1307 }
1308 if (isset($this->credit)) {
1309 $this->credit = trim($this->credit);
1310 }
1311 if (isset($this->amount)) {
1312 $this->amount = trim($this->amount);
1313 }
1314 if (isset($this->sens)) {
1315 $this->sens = trim($this->sens);
1316 }
1317 if (isset($this->import_key)) {
1318 $this->import_key = trim($this->import_key);
1319 }
1320 if (isset($this->code_journal)) {
1321 $this->code_journal = trim($this->code_journal);
1322 }
1323 if (isset($this->journal_label)) {
1324 $this->journal_label = trim($this->journal_label);
1325 }
1326 if (isset($this->piece_num)) {
1327 $this->piece_num = trim($this->piece_num);
1328 }
1329
1330 $this->debit = price2num($this->debit, 'MT');
1331 $this->credit = price2num($this->credit, 'MT');
1332
1333 // Check parameters
1334 // Put here code to add a control on parameters values
1335
1336 // Update request
1337 $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element.$mode.' SET';
1338 $sql .= ' doc_date = '.(!isset($this->doc_date) || dol_strlen($this->doc_date) != 0 ? "'".$this->db->idate($this->doc_date)."'" : 'null').',';
1339 $sql .= ' doc_type = '.(isset($this->doc_type) ? "'".$this->db->escape($this->doc_type)."'" : "null").',';
1340 $sql .= ' doc_ref = '.(isset($this->doc_ref) ? "'".$this->db->escape($this->doc_ref)."'" : "null").',';
1341 $sql .= ' fk_doc = '.(isset($this->fk_doc) ? $this->fk_doc : "null").',';
1342 $sql .= ' fk_docdet = '.(isset($this->fk_docdet) ? $this->fk_docdet : "null").',';
1343 $sql .= ' thirdparty_code = '.(isset($this->thirdparty_code) ? "'".$this->db->escape($this->thirdparty_code)."'" : "null").',';
1344 $sql .= ' subledger_account = '.(isset($this->subledger_account) ? "'".$this->db->escape($this->subledger_account)."'" : "null").',';
1345 $sql .= ' subledger_label = '.(isset($this->subledger_label) ? "'".$this->db->escape($this->subledger_label)."'" : "null").',';
1346 $sql .= ' numero_compte = '.(isset($this->numero_compte) ? "'".$this->db->escape($this->numero_compte)."'" : "null").',';
1347 $sql .= ' label_compte = '.(isset($this->label_compte) ? "'".$this->db->escape($this->label_compte)."'" : "null").',';
1348 $sql .= ' label_operation = '.(isset($this->label_operation) ? "'".$this->db->escape($this->label_operation)."'" : "null").',';
1349 $sql .= ' debit = '.(isset($this->debit) ? $this->debit : "null").',';
1350 $sql .= ' credit = '.(isset($this->credit) ? $this->credit : "null").',';
1351 $sql .= ' montant = '.(isset($this->montant) ? $this->montant : "null").',';
1352 $sql .= ' sens = '.(isset($this->sens) ? "'".$this->db->escape($this->sens)."'" : "null").',';
1353 $sql .= ' fk_user_author = '.(isset($this->fk_user_author) ? $this->fk_user_author : "null").',';
1354 $sql .= ' import_key = '.(isset($this->import_key) ? "'".$this->db->escape($this->import_key)."'" : "null").',';
1355 $sql .= ' code_journal = '.(isset($this->code_journal) ? "'".$this->db->escape($this->code_journal)."'" : "null").',';
1356 $sql .= ' journal_label = '.(isset($this->journal_label) ? "'".$this->db->escape($this->journal_label)."'" : "null").',';
1357 $sql .= ' piece_num = '.(isset($this->piece_num) ? $this->piece_num : "null");
1358 $sql .= ' WHERE rowid='.((int) $this->id);
1359
1360 $this->db->begin();
1361
1362 $resql = $this->db->query($sql);
1363 if (!$resql) {
1364 $error++;
1365 $this->errors[] = 'Error '.$this->db->lasterror();
1366 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1367 }
1368
1369 // Uncomment this and change MYOBJECT to your own tag if you
1370 // want this action calls a trigger.
1371 //if (! $error && ! $notrigger) {
1372
1373 // // Call triggers
1374 // $result=$this->call_trigger('MYOBJECT_MODIFY',$user);
1375 // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1376 // // End call triggers
1377 //}
1378
1379 // Commit or rollback
1380 if ($error) {
1381 $this->db->rollback();
1382
1383 return -1 * $error;
1384 } else {
1385 $this->db->commit();
1386
1387 return 1;
1388 }
1389 }
1390
1400 public function updateByMvt($piece_num = '', $field = '', $value = '', $mode = '')
1401 {
1402 $error = 0;
1403
1404 $this->db->begin();
1405
1406 $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element.$mode;
1407 $sql .= " SET ".$field." = ".(is_numeric($value) ? ((float) $value) : "'".$this->db->escape($value)."'");
1408 $sql .= " WHERE piece_num = ".((int) $piece_num);
1409
1410 $resql = $this->db->query($sql);
1411
1412 if (!$resql) {
1413 $error++;
1414 $this->errors[] = 'Error '.$this->db->lasterror();
1415 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1416 }
1417 if ($error) {
1418 $this->db->rollback();
1419
1420 return -1 * $error;
1421 } else {
1422 $this->db->commit();
1423
1424 return 1;
1425 }
1426 }
1427
1436 public function delete(User $user, $notrigger = false, $mode = '')
1437 {
1438 dol_syslog(__METHOD__, LOG_DEBUG);
1439
1440 $error = 0;
1441
1442 $this->db->begin();
1443
1444 // Uncomment this and change MYOBJECT to your own tag if you
1445 // want this action calls a trigger.
1446 //if (! $error && ! $notrigger) {
1447
1448 // // Call triggers
1449 // $result=$this->call_trigger('MYOBJECT_DELETE',$user);
1450 // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1451 // // End call triggers
1452 //}
1453
1454 if (!$error) {
1455 $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.$mode;
1456 $sql .= ' WHERE rowid='.((int) $this->id);
1457
1458 $resql = $this->db->query($sql);
1459 if (!$resql) {
1460 $error++;
1461 $this->errors[] = 'Error '.$this->db->lasterror();
1462 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1463 }
1464 }
1465
1466 // Commit or rollback
1467 if ($error) {
1468 $this->db->rollback();
1469
1470 return -1 * $error;
1471 } else {
1472 $this->db->commit();
1473
1474 return 1;
1475 }
1476 }
1477
1484 public function deleteByImportkey($importkey)
1485 {
1486 $this->db->begin();
1487
1488 // first check if line not yet in bookkeeping
1489 $sql = "DELETE";
1490 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
1491 $sql .= " WHERE import_key = '".$this->db->escape($importkey)."'";
1492
1493 $resql = $this->db->query($sql);
1494
1495 if (!$resql) {
1496 $this->errors[] = "Error ".$this->db->lasterror();
1497 dol_syslog(get_class($this)."::delete Error ".$this->db->lasterror(), LOG_ERR);
1498 $this->db->rollback();
1499 return -1;
1500 }
1501
1502 $this->db->commit();
1503 return 1;
1504 }
1505
1515 public function deleteByYearAndJournal($delyear = 0, $journal = '', $mode = '', $delmonth = 0)
1516 {
1517 global $conf, $langs;
1518
1519 if (empty($delyear) && empty($journal)) {
1520 $this->error = 'ErrorOneFieldRequired';
1521 return -1;
1522 }
1523 if (!empty($delmonth) && empty($delyear)) {
1524 $this->error = 'YearRequiredIfMonthDefined';
1525 return -2;
1526 }
1527
1528 $this->db->begin();
1529
1530 // Delete record in bookkeeping
1531 $sql = "DELETE";
1532 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1533 $sql .= " WHERE 1 = 1";
1534 $sql .= dolSqlDateFilter('doc_date', 0, $delmonth, $delyear);
1535 if (!empty($journal)) {
1536 $sql .= " AND code_journal = '".$this->db->escape($journal)."'";
1537 }
1538 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1539 // Exclusion of validated entries at the time of deletion
1540 $sql .= " AND date_validated IS NULL";
1541
1542 // TODO: In a future we must forbid deletion if record is inside a closed fiscal period.
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
1566 public function deleteMvtNum($piecenum)
1567 {
1568 global $conf;
1569
1570 $this->db->begin();
1571
1572 // first check if line not yet in bookkeeping
1573 $sql = "DELETE";
1574 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
1575 $sql .= " WHERE piece_num = ".(int) $piecenum;
1576 $sql .= " AND date_validated IS NULL"; // For security, exclusion of validated entries at the time of deletion
1577 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1578
1579 $resql = $this->db->query($sql);
1580
1581 if (!$resql) {
1582 $this->errors[] = "Error ".$this->db->lasterror();
1583 foreach ($this->errors as $errmsg) {
1584 dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
1585 $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
1586 }
1587 $this->db->rollback();
1588 return -1;
1589 }
1590
1591 $this->db->commit();
1592 return 1;
1593 }
1594
1602 public function createFromClone(User $user, $fromid)
1603 {
1604 dol_syslog(__METHOD__, LOG_DEBUG);
1605
1606 $error = 0;
1607 $object = new BookKeeping($this->db);
1608
1609 $this->db->begin();
1610
1611 // Load source object
1612 $object->fetch($fromid);
1613 // Reset object
1614 $object->id = 0;
1615
1616 // Clear fields
1617 // ...
1618
1619 // Create clone
1620 $object->context['createfromclone'] = 'createfromclone';
1621 $result = $object->create($user);
1622
1623 // Other options
1624 if ($result < 0) {
1625 $error++;
1626 $this->errors = $object->errors;
1627 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1628 }
1629
1630 unset($object->context['createfromclone']);
1631
1632 // End
1633 if (!$error) {
1634 $this->db->commit();
1635
1636 return $object->id;
1637 } else {
1638 $this->db->rollback();
1639
1640 return -1;
1641 }
1642 }
1643
1650 public function initAsSpecimen()
1651 {
1652 global $user;
1653
1654 $now = dol_now();
1655
1656 $this->id = 0;
1657 $this->doc_date = $now;
1658 $this->doc_type = '';
1659 $this->doc_ref = '';
1660 $this->fk_doc = 0;
1661 $this->fk_docdet = 0;
1662 $this->thirdparty_code = 'CU001';
1663 $this->subledger_account = '41100001';
1664 $this->subledger_label = 'My customer company';
1665 $this->numero_compte = '411';
1666 $this->label_compte = 'Customer';
1667 $this->label_operation = 'Sales of pea';
1668 $this->debit = 99.9;
1669 $this->credit = 0.0;
1670 $this->amount = 0.0;
1671 $this->sens = 'D';
1672 $this->fk_user_author = $user->id;
1673 $this->import_key = '20201027';
1674 $this->code_journal = 'VT';
1675 $this->journal_label = 'Journal de vente';
1676 $this->piece_num = 1234;
1677 $this->date_creation = $now;
1678 }
1679
1687 public function fetchPerMvt($piecenum, $mode = '')
1688 {
1689 global $conf;
1690
1691 $sql = "SELECT piece_num, doc_date,code_journal, journal_label, doc_ref, doc_type,";
1692 $sql .= " date_creation, tms as date_modification, date_validated as date_validation";
1693 // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1694 if ($mode != "_tmp") {
1695 $sql .= ", date_export";
1696 }
1697 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1698 $sql .= " WHERE piece_num = ".((int) $piecenum);
1699 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1700
1701 dol_syslog(__METHOD__, LOG_DEBUG);
1702 $result = $this->db->query($sql);
1703 if ($result) {
1704 $obj = $this->db->fetch_object($result);
1705
1706 $this->piece_num = $obj->piece_num;
1707 $this->code_journal = $obj->code_journal;
1708 $this->journal_label = $obj->journal_label;
1709 $this->doc_date = $this->db->jdate($obj->doc_date);
1710 $this->doc_ref = $obj->doc_ref;
1711 $this->doc_type = $obj->doc_type;
1712 $this->date_creation = $this->db->jdate($obj->date_creation);
1713 $this->date_modification = $this->db->jdate($obj->date_modification);
1714 if ($mode != "_tmp") {
1715 $this->date_export = $this->db->jdate($obj->date_export);
1716 }
1717 $this->date_validation = $this->db->jdate($obj->date_validation);
1718 } else {
1719 $this->error = "Error ".$this->db->lasterror();
1720 dol_syslog(__METHOD__.$this->error, LOG_ERR);
1721 return -1;
1722 }
1723
1724 return 1;
1725 }
1726
1733 public function getNextNumMvt($mode = '')
1734 {
1735 global $conf;
1736
1737 $sql = "SELECT MAX(piece_num)+1 as max FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1738 $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1739
1740 dol_syslog(get_class($this)."::getNextNumMvt", LOG_DEBUG);
1741
1742 $result = $this->db->query($sql);
1743
1744 if ($result) {
1745 $obj = $this->db->fetch_object($result);
1746 if ($obj) {
1747 $result = $obj->max;
1748 }
1749 if (empty($result)) {
1750 $result = 1;
1751 }
1752 return $result;
1753 } else {
1754 $this->error = "Error ".$this->db->lasterror();
1755 dol_syslog(get_class($this)."::getNextNumMvt ".$this->error, LOG_ERR);
1756 return -1;
1757 }
1758 }
1759
1767 public function fetchAllPerMvt($piecenum, $mode = '')
1768 {
1769 global $conf;
1770
1771 $sql = "SELECT rowid, doc_date, doc_type,";
1772 $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1773 $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1774 $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, journal_label, piece_num,";
1775 $sql .= " date_creation, tms as date_modification, date_validated as date_validation";
1776 // In llx_accounting_bookkeeping_tmp, field date_export doesn't exist
1777 if ($mode != "_tmp") {
1778 $sql .= ", date_export";
1779 }
1780 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element.$mode;
1781 $sql .= " WHERE piece_num = ".((int) $piecenum);
1782 $sql .= " AND entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1783
1784 dol_syslog(__METHOD__, LOG_DEBUG);
1785 $result = $this->db->query($sql);
1786 if ($result) {
1787 while ($obj = $this->db->fetch_object($result)) {
1788 $line = new BookKeepingLine();
1789
1790 $line->id = $obj->rowid;
1791
1792 $line->doc_date = $this->db->jdate($obj->doc_date);
1793 $line->doc_type = $obj->doc_type;
1794 $line->doc_ref = $obj->doc_ref;
1795 $line->fk_doc = $obj->fk_doc;
1796 $line->fk_docdet = $obj->fk_docdet;
1797 $line->thirdparty_code = $obj->thirdparty_code;
1798 $line->subledger_account = $obj->subledger_account;
1799 $line->subledger_label = $obj->subledger_label;
1800 $line->numero_compte = $obj->numero_compte;
1801 $line->label_compte = $obj->label_compte;
1802 $line->label_operation = $obj->label_operation;
1803 $line->debit = $obj->debit;
1804 $line->credit = $obj->credit;
1805 $line->montant = $obj->amount;
1806 $line->amount = $obj->amount;
1807 $line->sens = $obj->sens;
1808 $line->code_journal = $obj->code_journal;
1809 $line->journal_label = $obj->journal_label;
1810 $line->piece_num = $obj->piece_num;
1811 $line->date_creation = $obj->date_creation;
1812 $line->date_modification = $obj->date_modification;
1813 if ($mode != "_tmp") {
1814 $line->date_export = $obj->date_export;
1815 }
1816 $line->date_validation = $obj->date_validation;
1817
1818 $this->linesmvt[] = $line;
1819 }
1820 } else {
1821 $this->error = "Error ".$this->db->lasterror();
1822 dol_syslog(__METHOD__.$this->error, LOG_ERR);
1823 return -1;
1824 }
1825
1826 return 1;
1827 }
1828
1829 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
1836 public function export_bookkeeping($model = 'ebp')
1837 {
1838 // phpcs:enable
1839 global $conf;
1840
1841 $sql = "SELECT rowid, doc_date, doc_type,";
1842 $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1843 $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1844 $sql .= " montant as amount, sens, fk_user_author, import_key, code_journal, piece_num,";
1845 $sql .= " date_validated as date_validation";
1846 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element;
1847 $sql .= " WHERE entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
1848
1849 dol_syslog(get_class($this)."::export_bookkeeping", LOG_DEBUG);
1850
1851 $resql = $this->db->query($sql);
1852
1853 if ($resql) {
1854 $this->linesexport = array();
1855
1856 $num = $this->db->num_rows($resql);
1857 while ($obj = $this->db->fetch_object($resql)) {
1858 $line = new BookKeepingLine();
1859
1860 $line->id = $obj->rowid;
1861
1862 $line->doc_date = $this->db->jdate($obj->doc_date);
1863 $line->doc_type = $obj->doc_type;
1864 $line->doc_ref = $obj->doc_ref;
1865 $line->fk_doc = $obj->fk_doc;
1866 $line->fk_docdet = $obj->fk_docdet;
1867 $line->thirdparty_code = $obj->thirdparty_code;
1868 $line->subledger_account = $obj->subledger_account;
1869 $line->subledger_label = $obj->subledger_label;
1870 $line->numero_compte = $obj->numero_compte;
1871 $line->label_compte = $obj->label_compte;
1872 $line->label_operation = $obj->label_operation;
1873 $line->debit = $obj->debit;
1874 $line->credit = $obj->credit;
1875 $line->montant = $obj->amount;
1876 $line->amount = $obj->amount;
1877 $line->sens = $obj->sens;
1878 $line->code_journal = $obj->code_journal;
1879 $line->piece_num = $obj->piece_num;
1880 $line->date_validation = $obj->date_validation;
1881
1882 $this->linesexport[] = $line;
1883 }
1884 $this->db->free($resql);
1885
1886 return $num;
1887 } else {
1888 $this->error = "Error ".$this->db->lasterror();
1889 dol_syslog(get_class($this)."::export_bookkeeping ".$this->error, LOG_ERR);
1890 return -1;
1891 }
1892 }
1893
1901 public function transformTransaction($direction = 0, $piece_num = '')
1902 {
1903 global $conf;
1904
1905 $error = 0;
1906
1907 $this->db->begin();
1908
1909 if ($direction == 0) {
1910 $next_piecenum = $this->getNextNumMvt();
1911 $now = dol_now();
1912
1913 if ($next_piecenum < 0) {
1914 $error++;
1915 }
1916
1917 if (!$error) {
1918 // Delete if there is an empty line
1919 $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";
1920 $resql = $this->db->query($sql);
1921 if (!$resql) {
1922 $error++;
1923 $this->errors[] = 'Error '.$this->db->lasterror();
1924 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1925 }
1926 }
1927
1928 if (!$error) {
1929 $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.' (doc_date, doc_type,';
1930 $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
1931 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1932 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num, date_creation)';
1933 $sql .= ' SELECT doc_date, doc_type,';
1934 $sql .= ' doc_ref, fk_doc, fk_docdet, entity, thirdparty_code, subledger_account, subledger_label,';
1935 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1936 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, '.((int) $next_piecenum).", '".$this->db->idate($now)."'";
1937 $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);
1938 $resql = $this->db->query($sql);
1939 if (!$resql) {
1940 $error++;
1941 $this->errors[] = 'Error '.$this->db->lasterror();
1942 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1943 }
1944 }
1945
1946 if (!$error) {
1947 $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1948 $resql = $this->db->query($sql);
1949 if (!$resql) {
1950 $error++;
1951 $this->errors[] = 'Error '.$this->db->lasterror();
1952 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1953 }
1954 }
1955 } elseif ($direction == 1) {
1956 if (!$error) {
1957 $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1958 $resql = $this->db->query($sql);
1959 if (!$resql) {
1960 $error++;
1961 $this->errors[] = 'Error '.$this->db->lasterror();
1962 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1963 }
1964 }
1965
1966 if (!$error) {
1967 $sql = 'INSERT INTO '.MAIN_DB_PREFIX.$this->table_element.'_tmp (doc_date, doc_type,';
1968 $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1969 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1970 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num)';
1971 $sql .= ' SELECT doc_date, doc_type,';
1972 $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1973 $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1974 $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num';
1975 $sql .= ' FROM '.MAIN_DB_PREFIX.$this->table_element.' WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1976 $resql = $this->db->query($sql);
1977 if (!$resql) {
1978 $error++;
1979 $this->errors[] = 'Error '.$this->db->lasterror();
1980 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1981 }
1982 }
1983
1984 if (!$error) {
1985 $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.'_tmp WHERE piece_num = '.((int) $piece_num).' AND entity = ' .((int) $conf->entity);
1986 $resql = $this->db->query($sql);
1987 if (!$resql) {
1988 $error++;
1989 $this->errors[] = 'Error '.$this->db->lasterror();
1990 dol_syslog(__METHOD__.' '.join(',', $this->errors), LOG_ERR);
1991 }
1992 }
1993 }
1994 if (!$error) {
1995 $this->db->commit();
1996 return 1;
1997 } else {
1998 $this->db->rollback();
1999 return -1;
2000 }
2001 /*
2002 $sql = "DELETE FROM ";
2003 $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as ab";
2004 $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as aa ON aa.account_number = ab.numero_compte";
2005 $sql .= " AND aa.active = 1";
2006 $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2007 $sql .= " AND asy.rowid = " . ((int) $pcgver);
2008 $sql .= " AND ab.entity IN (" . getEntity('accountancy') . ")";
2009 $sql .= " ORDER BY account_number ASC";
2010 */
2011 }
2012
2013 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2026 public function select_account($selectid, $htmlname = 'account', $showempty = 0, $event = array(), $select_in = 0, $select_out = 0, $aabase = '')
2027 {
2028 // phpcs:enable
2029 global $conf;
2030
2031 require_once DOL_DOCUMENT_ROOT.'/core/lib/accounting.lib.php';
2032
2033 $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2034
2035 $sql = "SELECT DISTINCT ab.numero_compte as account_number, aa.label as label, aa.rowid as rowid, aa.fk_pcg_version";
2036 $sql .= " FROM ".MAIN_DB_PREFIX."accounting_bookkeeping as ab";
2037 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as aa ON aa.account_number = ab.numero_compte";
2038 $sql .= " AND aa.active = 1";
2039 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2040 $sql .= " AND asy.rowid = ".((int) $pcgver);
2041 $sql .= " AND ab.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2042 $sql .= " ORDER BY account_number ASC";
2043
2044 dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2045 $resql = $this->db->query($sql);
2046
2047 if (!$resql) {
2048 $this->error = "Error ".$this->db->lasterror();
2049 dol_syslog(get_class($this)."::select_account ".$this->error, LOG_ERR);
2050 return -1;
2051 }
2052
2053 $out = ajax_combobox($htmlname, $event);
2054
2055 $options = array();
2056 $selected = null;
2057
2058 while ($obj = $this->db->fetch_object($resql)) {
2059 $label = length_accountg($obj->account_number).' - '.$obj->label;
2060
2061 $select_value_in = $obj->rowid;
2062 $select_value_out = $obj->rowid;
2063
2064 if ($select_in == 1) {
2065 $select_value_in = $obj->account_number;
2066 }
2067 if ($select_out == 1) {
2068 $select_value_out = $obj->account_number;
2069 }
2070
2071 // Remember guy's we store in database llx_facturedet the rowid of accounting_account and not the account_number
2072 // Because same account_number can be share between different accounting_system and do have the same meaning
2073 if (($selectid != '') && $selectid == $select_value_in) {
2074 $selected = $select_value_out;
2075 }
2076
2077 $options[$select_value_out] = $label;
2078 }
2079
2080 $out .= Form::selectarray($htmlname, $options, $selected, $showempty, 0, 0, '', 0, 0, 0, '', 'maxwidth300');
2081 $this->db->free($resql);
2082 return $out;
2083 }
2084
2092 public function getRootAccount($account = null)
2093 {
2094 global $conf;
2095 $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2096
2097 $sql = "SELECT root.rowid, root.account_number, root.label as label,";
2098 $sql .= " parent.rowid as parent_rowid, parent.account_number as parent_account_number, parent.label as parent_label";
2099 $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa";
2100 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2101 $sql .= " AND asy.rowid = ".((int) $pcgver);
2102 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as parent ON aa.account_parent = parent.rowid AND parent.active = 1";
2103 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."accounting_account as root ON parent.account_parent = root.rowid AND root.active = 1";
2104 $sql .= " WHERE aa.account_number = '".$this->db->escape($account)."'";
2105 $sql .= " AND aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2106
2107 dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2108 $resql = $this->db->query($sql);
2109 if ($resql) {
2110 $obj = '';
2111 if ($this->db->num_rows($resql)) {
2112 $obj = $this->db->fetch_object($resql);
2113 }
2114
2115 $result = array('id'=>$obj->rowid, 'account_number'=>$obj->account_number, 'label'=>$obj->label);
2116 return $result;
2117 } else {
2118 $this->error = "Error ".$this->db->lasterror();
2119 dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2120
2121 return -1;
2122 }
2123 }
2124
2125 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
2132 public function get_compte_desc($account = null)
2133 {
2134 // phpcs:enable
2135 global $conf;
2136
2137 $pcgver = getDolGlobalInt('CHARTOFACCOUNTS');
2138 $sql = "SELECT aa.account_number, aa.label, aa.rowid, aa.fk_pcg_version, cat.label as category";
2139 $sql .= " FROM ".MAIN_DB_PREFIX."accounting_account as aa ";
2140 $sql .= " INNER JOIN ".MAIN_DB_PREFIX."accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
2141 $sql .= " AND aa.account_number = '".$this->db->escape($account)."'";
2142 $sql .= " AND asy.rowid = ".((int) $pcgver);
2143 $sql .= " AND aa.active = 1";
2144 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_accounting_category as cat ON aa.fk_accounting_category = cat.rowid";
2145 $sql .= " WHERE aa.entity = " . ((int) $conf->entity); // Do not use getEntity for accounting features
2146
2147 dol_syslog(get_class($this)."::select_account", LOG_DEBUG);
2148 $resql = $this->db->query($sql);
2149 if ($resql) {
2150 $obj = '';
2151 if ($this->db->num_rows($resql)) {
2152 $obj = $this->db->fetch_object($resql);
2153 }
2154 if (empty($obj->category)) {
2155 return $obj->label;
2156 } else {
2157 return $obj->label.' ('.$obj->category.')';
2158 }
2159 } else {
2160 $this->error = "Error ".$this->db->lasterror();
2161 dol_syslog(__METHOD__." ".$this->error, LOG_ERR);
2162 return -1;
2163 }
2164 }
2165}
2166
2170class BookKeepingLine
2171{
2175 public $id;
2176
2177 public $doc_date = '';
2178 public $doc_type;
2179 public $doc_ref;
2180
2184 public $fk_doc;
2185
2189 public $fk_docdet;
2190
2191 public $thirdparty_code;
2192 public $subledger_account;
2193 public $subledger_label;
2194 public $numero_compte;
2195 public $label_compte;
2196 public $label_operation;
2197 public $debit;
2198 public $credit;
2199
2204 public $montant;
2205
2209 public $amount;
2210
2214 public $multicurrency_amount;
2215
2219 public $multicurrency_code;
2220
2224 public $sens;
2225 public $lettering_code;
2226 public $date_lettering;
2227
2231 public $fk_user_author;
2232
2233 public $import_key;
2234 public $code_journal;
2235 public $journal_label;
2236 public $piece_num;
2237
2241 public $date_creation;
2242
2246 public $date_modification;
2247
2251 public $date_export;
2252
2256 public $date_validation;
2257
2261 public $date_lim_reglement;
2262}
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:464
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:1484
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:2026
BookKeeping\update
update(User $user, $notrigger=false, $mode='')
Update object into database.
Definition bookkeeping.class.php:1268
BookKeeping\getNextNumMvt
getNextNumMvt($mode='')
Return next number movement.
Definition bookkeeping.class.php:1733
BookKeeping\get_compte_desc
get_compte_desc($account=null)
Description of accounting account.
Definition bookkeeping.class.php:2132
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:1602
BookKeeping\getRootAccount
getRootAccount($account=null)
Return id and description of a root accounting account.
Definition bookkeeping.class.php:2092
BookKeeping\transformTransaction
transformTransaction($direction=0, $piece_num='')
Transform transaction.
Definition bookkeeping.class.php:1901
BookKeeping\fetchPerMvt
fetchPerMvt($piecenum, $mode='')
Load an accounting document into memory from database.
Definition bookkeeping.class.php:1687
BookKeeping\updateByMvt
updateByMvt($piece_num='', $field='', $value='', $mode='')
Update accounting movement.
Definition bookkeeping.class.php:1400
BookKeeping\deleteMvtNum
deleteMvtNum($piecenum)
Delete bookkeeping by piece number.
Definition bookkeeping.class.php:1566
BookKeeping\deleteByYearAndJournal
deleteByYearAndJournal($delyear=0, $journal='', $mode='', $delmonth=0)
Delete bookkeeping by year.
Definition bookkeeping.class.php:1515
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:1650
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:1767
BookKeeping\export_bookkeeping
export_bookkeeping($model='ebp')
Export bookkeeping.
Definition bookkeeping.class.php:1836
BookKeepingLine
Class BookKeepingLine.
Definition bookkeeping.class.php:2171
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:8203
User
Class to manage Dolibarr users.
Definition user.class.php:48
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:361
price2num
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
Definition functions.lib.php:6020
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:4535
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:10108
dol_strlen
dol_strlen($string, $stringencoding='UTF-8')
Make a strlen call.
Definition functions.lib.php:4059
dol_now
dol_now($mode='auto')
Return date for now.
Definition functions.lib.php:3118
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:1788
Generated on Fri May 2 2025 01:00:16 for dolibarr by Doxygen 1.11.0