dolibarr  7.0.0-beta
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-2017 Alexandre Spangaro <aspangaro@zendsi.com>
4  * Copyright (C) 2015-2017 Florian Henry <florian.henry@open-concept.pro>
5  *
6  * This program is free software; you can redistribute it and/or modify
7  * it under the terms of the GNU General Public License as published by
8  * the Free Software Foundation; either version 3 of the License, or
9  * (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program. If not, see <http://www.gnu.org/licenses/>.
18  */
19 
26 // Class
27 require_once DOL_DOCUMENT_ROOT . '/core/class/commonobject.class.php';
28 
33 {
37  public $error;
41  public $errors = array ();
45  public $element = 'accountingbookkeeping';
49  public $table_element = 'accounting_bookkeeping';
50 
51  public $entity = 1;
52 
56  public $lines = array ();
57 
61  public $id;
64  public $doc_date;
65  public $date_lim_reglement;
66  public $doc_type;
67  public $doc_ref;
68  public $fk_doc;
69  public $fk_docdet;
70  public $thirdparty_code;
71  public $subledger_account;
72  public $subledger_label;
73  public $numero_compte;
74  public $label_compte;
75  public $label_operation;
76  public $debit;
77  public $credit;
78  public $montant;
79  public $sens;
80  public $fk_user_author;
81  public $import_key;
82  public $code_journal;
83  public $journal_label;
84  public $piece_num;
85 
91  public function __construct(DoliDB $db) {
92  $this->db = $db;
93  }
94 
102  public function create(User $user, $notrigger = false) {
103  global $conf, $langs;
104 
105  dol_syslog(__METHOD__, LOG_DEBUG);
106 
107  $error = 0;
108 
109  // Clean parameters
110  if (isset($this->doc_type)) {
111  $this->doc_type = trim($this->doc_type);
112  }
113  if (isset($this->doc_ref)) {
114  $this->doc_ref = trim($this->doc_ref);
115  }
116  if (isset($this->fk_doc)) {
117  $this->fk_doc = trim($this->fk_doc);
118  }
119  if (isset($this->fk_docdet)) {
120  $this->fk_docdet = trim($this->fk_docdet);
121  }
122  if (isset($this->thirdparty_code)) {
123  $this->thirdparty_code = trim($this->thirdparty_code);
124  }
125  if (isset($this->subledger_account)) {
126  $this->subledger_account = trim($this->subledger_account);
127  }
128  if (isset($this->subledger_label)) {
129  $this->subledger_label = trim($this->subledger_label);
130  }
131  if (isset($this->numero_compte)) {
132  $this->numero_compte = trim($this->numero_compte);
133  }
134  if (isset($this->label_compte)) {
135  $this->label_compte = trim($this->label_compte);
136  }
137  if (isset($this->label_operation)) {
138  $this->label_operation = trim($this->label_operation);
139  }
140  if (isset($this->debit)) {
141  $this->debit = trim($this->debit);
142  }
143  if (isset($this->credit)) {
144  $this->credit = trim($this->credit);
145  }
146  if (isset($this->montant)) {
147  $this->montant = trim($this->montant);
148  }
149  if (isset($this->sens)) {
150  $this->sens = trim($this->sens);
151  }
152  if (isset($this->fk_user_author)) {
153  $this->fk_user_author = trim($this->fk_user_author);
154  }
155  if (isset($this->import_key)) {
156  $this->import_key = trim($this->import_key);
157  }
158  if (isset($this->code_journal)) {
159  $this->code_journal = trim($this->code_journal);
160  }
161  if (isset($this->journal_label)) {
162  $this->journal_label = trim($this->journal_label);
163  }
164  if (isset($this->piece_num)) {
165  $this->piece_num = trim($this->piece_num);
166  }
167  if (empty($this->debit)) $this->debit = 0;
168  if (empty($this->credit)) $this->credit = 0;
169 
170  // Check parameters
171  if (empty($this->numero_compte) || $this->numero_compte == '-1' || $this->numero_compte == 'NotDefined')
172  {
173  $langs->load("errors");
174  if (in_array($this->doc_type, array('bank', 'expense_report')))
175  {
176  $this->errors[]=$langs->trans('ErrorFieldAccountNotDefinedForBankLine', $this->fk_docdet, $this->doc_type);
177  }
178  else
179  {
180  //$this->errors[]=$langs->trans('ErrorFieldAccountNotDefinedForInvoiceLine', $this->doc_ref, $this->label_compte);
181  $mesg=$this->doc_ref.', '.$langs->trans("AccountAccounting").': '.$this->numero_compte;
182  if ($this->subledger_account && $this->subledger_account != $this->numero_compte)
183  {
184  $mesg.=', '.$langs->trans("SubledgerAccount").': '.$this->subledger_account;
185  }
186  $this->errors[]=$langs->trans('ErrorFieldAccountNotDefinedForLine', $mesg);
187  }
188 
189  return -1;
190  }
191 
192  $this->db->begin();
193 
194  $this->piece_num = 0;
195 
196  // First check if line not yet already in bookkeeping.
197  // Note that we must include doc_type - fk_doc - numero_compte - label to be sure to have unicity of line (we may have several lines
198  // with same doc_type, fk_odc, numero_compte for 1 invoice line when using localtaxes with same account)
199  // WARNING: This is not reliable, label may have been modified. This is just a small protection.
200  // The page to make journalization make the test on couple doc_type - fk_doc only.
201  $sql = "SELECT count(*) as nb";
202  $sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
203  $sql .= " WHERE doc_type = '" . $this->db->escape($this->doc_type) . "'";
204  $sql .= " AND fk_doc = " . $this->fk_doc;
205  //$sql .= " AND fk_docdet = " . $this->fk_docdet; // This field can be 0 if record is for several lines
206  $sql .= " AND numero_compte = '" . $this->db->escape($this->numero_compte) . "'";
207  $sql .= " AND label_operation = '" . $this->db->escape($this->label_operation) . "'";
208  $sql .= " AND entity IN (" . getEntity('accountancy') . ")";
209 
210  $resql = $this->db->query($sql);
211 
212  if ($resql) {
213  $row = $this->db->fetch_object($resql);
214  if ($row->nb == 0)
215  {
216  // Determine piece_num
217  $sqlnum = "SELECT piece_num";
218  $sqlnum .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
219  $sqlnum .= " WHERE doc_type = '" . $this->db->escape($this->doc_type) . "'"; // For example doc_type = 'bank'
220  $sqlnum .= " AND fk_docdet = " . $this->db->escape($this->fk_docdet); // fk_docdet is rowid into llx_bank or llx_facturedet or llx_facturefourndet, or ...
221  $sqlnum .= " AND doc_ref = '" . $this->db->escape($this->doc_ref) . "'"; // ref of source object
222  $sqlnum .= " AND entity IN (" . getEntity('accountancy') . ")";
223 
224  dol_syslog(get_class($this) . ":: create sqlnum=" . $sqlnum, LOG_DEBUG);
225  $resqlnum = $this->db->query($sqlnum);
226  if ($resqlnum) {
227  $objnum = $this->db->fetch_object($resqlnum);
228  $this->piece_num = $objnum->piece_num;
229  }
230  dol_syslog(get_class($this) . ":: create this->piece_num=" . $this->piece_num, LOG_DEBUG);
231  if (empty($this->piece_num)) {
232  $sqlnum = "SELECT MAX(piece_num)+1 as maxpiecenum";
233  $sqlnum .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
234  $sqlnum .= " WHERE entity IN (" . getEntity('accountancy') . ")";
235 
236  dol_syslog(get_class($this) . ":: create sqlnum=" . $sqlnum, LOG_DEBUG);
237  $resqlnum = $this->db->query($sqlnum);
238  if ($resqlnum) {
239  $objnum = $this->db->fetch_object($resqlnum);
240  $this->piece_num = $objnum->maxpiecenum;
241  }
242  }
243  dol_syslog(get_class($this) . ":: create this->piece_num=" . $this->piece_num, LOG_DEBUG);
244  if (empty($this->piece_num)) {
245  $this->piece_num = 1;
246  }
247 
248  $now = dol_now();
249  if (empty($this->date_create)) {
250  $this->date_create = $now;
251  }
252 
253  $sql = "INSERT INTO " . MAIN_DB_PREFIX . $this->table_element . " (";
254  $sql .= "doc_date";
255  $sql .= ", date_lim_reglement";
256  $sql .= ", doc_type";
257  $sql .= ", doc_ref";
258  $sql .= ", fk_doc";
259  $sql .= ", fk_docdet";
260  $sql .= ", thirdparty_code";
261  $sql .= ", subledger_account";
262  $sql .= ", subledger_label";
263  $sql .= ", numero_compte";
264  $sql .= ", label_compte";
265  $sql .= ", label_operation";
266  $sql .= ", debit";
267  $sql .= ", credit";
268  $sql .= ", montant";
269  $sql .= ", sens";
270  $sql .= ", fk_user_author";
271  $sql .= ", date_creation";
272  $sql .= ", code_journal";
273  $sql .= ", journal_label";
274  $sql .= ", piece_num";
275  $sql .= ', entity';
276  $sql .= ") VALUES (";
277  $sql .= "'" . $this->db->idate($this->doc_date) . "'";
278  $sql .= ", ".(! isset($this->date_lim_reglement) || dol_strlen($this->date_lim_reglement) == 0 ? 'NULL' : "'" . $this->db->idate($this->date_lim_reglement) . "'");
279  $sql .= ",'" . $this->db->escape($this->doc_type) . "'";
280  $sql .= ",'" . $this->db->escape($this->doc_ref) . "'";
281  $sql .= "," . $this->fk_doc;
282  $sql .= "," . $this->fk_docdet;
283  $sql .= ",'" . $this->db->escape($this->thirdparty_code) . "'";
284  $sql .= ",'" . $this->db->escape($this->subledger_account) . "'";
285  $sql .= ",'" . $this->db->escape($this->subledger_label) . "'";
286  $sql .= ",'" . $this->db->escape($this->numero_compte) . "'";
287  $sql .= ",'" . $this->db->escape($this->label_compte) . "'";
288  $sql .= ",'" . $this->db->escape($this->label_operation) . "'";
289  $sql .= "," . $this->debit;
290  $sql .= "," . $this->credit;
291  $sql .= "," . $this->montant;
292  $sql .= ",'" . $this->db->escape($this->sens) . "'";
293  $sql .= ",'" . $this->db->escape($this->fk_user_author) . "'";
294  $sql .= ",'" . $this->db->idate($this->date_create). "'";
295  $sql .= ",'" . $this->db->escape($this->code_journal) . "'";
296  $sql .= ",'" . $this->db->escape($this->journal_label) . "'";
297  $sql .= "," . $this->db->escape($this->piece_num);
298  $sql .= ", " . (! isset($this->entity) ? '1' : $this->entity);
299  $sql .= ")";
300 
301  dol_syslog(get_class($this) . ":: create sql=" . $sql, LOG_DEBUG);
302  $resql = $this->db->query($sql);
303  if ($resql) {
304  $id = $this->db->last_insert_id(MAIN_DB_PREFIX . $this->table_element);
305 
306  if ($id > 0) {
307  $this->id = $id;
308  $result = 0;
309  } else {
310  $result = -2;
311  $error ++;
312  $this->errors[] = 'Error Create Error ' . $result . ' lecture ID';
313  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
314  }
315  } else {
316  $result = -1;
317  $error ++;
318  $this->errors[] = 'Error ' . $this->db->lasterror();
319  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
320  }
321  } else { // Already exists
322  $result = -3;
323  $error++;
324  $this->error='BookkeepingRecordAlreadyExists';
325  dol_syslog(__METHOD__ . ' ' . $this->error, LOG_WARNING);
326  }
327  } else {
328  $result = -5;
329  $error ++;
330  $this->errors[] = 'Error ' . $this->db->lasterror();
331  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
332  }
333 
334  if (! $error) {
335 
336  if (! $notrigger) {
337  // Uncomment this and change MYOBJECT to your own tag if you
338  // want this action to call a trigger.
339 
340  // // Call triggers
341  // $result=$this->call_trigger('MYOBJECT_CREATE',$user);
342  // if ($result < 0) $error++;
343  // // End call triggers
344  }
345  }
346 
347  // Commit or rollback
348  if ($error) {
349  $this->db->rollback();
350  return -1 * $error;
351  } else {
352  $this->db->commit();
353  return $result;
354  }
355  }
356 
365  public function createStd(User $user, $notrigger = false, $mode='') {
366  dol_syslog(__METHOD__, LOG_DEBUG);
367 
368  $error = 0;
369 
370  // Clean parameters
371 
372  if (isset($this->doc_type)) {
373  $this->doc_type = trim($this->doc_type);
374  }
375  if (isset($this->doc_ref)) {
376  $this->doc_ref = trim($this->doc_ref);
377  }
378  if (isset($this->fk_doc)) {
379  $this->fk_doc = trim($this->fk_doc);
380  }
381  if (isset($this->fk_docdet)) {
382  $this->fk_docdet = trim($this->fk_docdet);
383  }
384  if (isset($this->thirdparty_code)) {
385  $this->thirdparty_code = trim($this->thirdparty_code);
386  }
387  if (isset($this->subledger_account)) {
388  $this->subledger_account = trim($this->subledger_account);
389  }
390  if (isset($this->subledger_label)) {
391  $this->subledger_label = trim($this->subledger_label);
392  }
393  if (isset($this->numero_compte)) {
394  $this->numero_compte = trim($this->numero_compte);
395  }
396  if (isset($this->label_compte)) {
397  $this->label_compte = trim($this->label_compte);
398  }
399  if (isset($this->label_operation)) {
400  $this->label_operation = trim($this->label_operation);
401  }
402  if (isset($this->debit)) {
403  $this->debit = trim($this->debit);
404  }
405  if (isset($this->credit)) {
406  $this->credit = trim($this->credit);
407  }
408  if (isset($this->montant)) {
409  $this->montant = trim($this->montant);
410  }
411  if (isset($this->sens)) {
412  $this->sens = trim($this->sens);
413  }
414  if (isset($this->fk_user_author)) {
415  $this->fk_user_author = trim($this->fk_user_author);
416  }
417  if (isset($this->import_key)) {
418  $this->import_key = trim($this->import_key);
419  }
420  if (isset($this->code_journal)) {
421  $this->code_journal = trim($this->code_journal);
422  }
423  if (isset($this->journal_label)) {
424  $this->journal_label = trim($this->journal_label);
425  }
426  if (isset($this->piece_num)) {
427  $this->piece_num = trim($this->piece_num);
428  }
429  if (empty($this->debit)) $this->debit = 0;
430  if (empty($this->credit)) $this->credit = 0;
431 
432  $this->debit = price2num($this->debit, 'MT');
433  $this->credit = price2num($this->credit, 'MT');
434 
435  $now = dol_now();
436  if (empty($this->date_create)) {
437  $this->date_create = $now;
438  }
439 
440  // Check parameters
441  // Put here code to add control on parameters values
442 
443  // Insert request
444  $sql = 'INSERT INTO ' . MAIN_DB_PREFIX . $this->table_element . $mode.'(';
445  $sql .= 'doc_date,';
446  $sql .= 'date_lim_reglement,';
447  $sql .= 'doc_type,';
448  $sql .= 'doc_ref,';
449  $sql .= 'fk_doc,';
450  $sql .= 'fk_docdet,';
451  $sql .= 'thirdparty_code,';
452  $sql .= 'subledger_account,';
453  $sql .= 'subledger_label,';
454  $sql .= 'numero_compte,';
455  $sql .= 'label_compte,';
456  $sql .= 'label_operation,';
457  $sql .= 'debit,';
458  $sql .= 'credit,';
459  $sql .= 'montant,';
460  $sql .= 'sens,';
461  $sql .= 'fk_user_author,';
462  $sql .= 'date_creation,';
463  $sql .= 'code_journal,';
464  $sql .= 'journal_label,';
465  $sql .= 'piece_num,';
466  $sql .= 'entity';
467  $sql .= ') VALUES (';
468  $sql .= ' ' . (! isset($this->doc_date) || dol_strlen($this->doc_date) == 0 ? 'NULL' : "'" . $this->db->idate($this->doc_date) . "'") . ',';
469  $sql .= ' ' . (! isset($this->date_lim_reglement) || dol_strlen($this->date_lim_reglement) == 0 ? 'NULL' : "'" . $this->db->idate($this->date_lim_reglement) . "'") . ',';
470  $sql .= ' ' . (! isset($this->doc_type) ? 'NULL' : "'" . $this->db->escape($this->doc_type) . "'") . ',';
471  $sql .= ' ' . (! isset($this->doc_ref) ? 'NULL' : "'" . $this->db->escape($this->doc_ref) . "'") . ',';
472  $sql .= ' ' . (empty($this->fk_doc) ? '0' : $this->fk_doc) . ',';
473  $sql .= ' ' . (empty($this->fk_docdet) ? '0' : $this->fk_docdet) . ',';
474  $sql .= ' ' . (! isset($this->thirdparty_code) ? 'NULL' : "'" . $this->db->escape($this->thirdparty_code) . "'") . ',';
475  $sql .= ' ' . (! isset($this->subledger_account) ? 'NULL' : "'" . $this->db->escape($this->subledger_account) . "'") . ',';
476  $sql .= ' ' . (! isset($this->subledger_label) ? 'NULL' : "'" . $this->db->escape($this->subledger_label) . "'") . ',';
477  $sql .= ' ' . (! isset($this->numero_compte) ? 'NULL' : "'" . $this->db->escape($this->numero_compte) . "'") . ',';
478  $sql .= ' ' . (! isset($this->label_compte) ? 'NULL' : "'" . $this->db->escape($this->label_compte) . "'") . ',';
479  $sql .= ' ' . (! isset($this->label_operation) ? 'NULL' : "'" . $this->db->escape($this->label_operation) . "'") . ',';
480  $sql .= ' ' . (! isset($this->debit) ? 'NULL' : $this->debit ). ',';
481  $sql .= ' ' . (! isset($this->credit) ? 'NULL' : $this->credit ). ',';
482  $sql .= ' ' . (! isset($this->montant) ? 'NULL' : $this->montant ). ',';
483  $sql .= ' ' . (! isset($this->sens) ? 'NULL' : "'" . $this->db->escape($this->sens) . "'") . ',';
484  $sql .= ' ' . $user->id . ',';
485  $sql .= ' ' . "'" . $this->db->idate($this->date_create) . "',";
486  $sql .= ' ' . (empty($this->code_journal) ? 'NULL' : "'" . $this->db->escape($this->code_journal) . "'") . ',';
487  $sql .= ' ' . (empty($this->journal_label) ? 'NULL' : "'" . $this->db->escape($this->journal_label) . "'") . ',';
488  $sql .= ' ' . (empty($this->piece_num) ? 'NULL' : $this->db->escape($this->piece_num)).',';
489  $sql .= ' ' . (! isset($this->entity) ? '1' : $this->entity);
490  $sql .= ')';
491 
492  $this->db->begin();
493 
494  $resql = $this->db->query($sql);
495  if (! $resql) {
496  $error ++;
497  $this->errors[] = 'Error ' . $this->db->lasterror();
498  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
499  }
500 
501  if (! $error) {
502  $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX . $this->table_element);
503 
504  if (! $notrigger) {
505  // Uncomment this and change MYOBJECT to your own tag if you
506  // want this action to call a trigger.
507 
508  // // Call triggers
509  // $result=$this->call_trigger('MYOBJECT_CREATE',$user);
510  // if ($result < 0) $error++;
511  // // End call triggers
512  }
513  }
514 
515  // Commit or rollback
516  if ($error) {
517  $this->db->rollback();
518 
519  return - 1 * $error;
520  } else {
521  $this->db->commit();
522 
523  return $this->id;
524  }
525  }
526 
536  public function fetch($id, $ref = null, $mode='') {
537  global $conf;
538 
539  dol_syslog(__METHOD__, LOG_DEBUG);
540 
541  $sql = 'SELECT';
542  $sql .= ' t.rowid,';
543  $sql .= " t.doc_date,";
544  $sql .= " t.date_lim_reglement,";
545  $sql .= " t.doc_type,";
546  $sql .= " t.doc_ref,";
547  $sql .= " t.fk_doc,";
548  $sql .= " t.fk_docdet,";
549  $sql .= " t.thirdparty_code,";
550  $sql .= " t.subledger_account,";
551  $sql .= " t.subledger_label,";
552  $sql .= " t.numero_compte,";
553  $sql .= " t.label_compte,";
554  $sql .= " t.label_operation,";
555  $sql .= " t.debit,";
556  $sql .= " t.credit,";
557  $sql .= " t.montant,";
558  $sql .= " t.sens,";
559  $sql .= " t.fk_user_author,";
560  $sql .= " t.import_key,";
561  $sql .= " t.code_journal,";
562  $sql .= " t.journal_label,";
563  $sql .= " t.piece_num,";
564  $sql .= " t.date_creation";
565  $sql .= ' FROM ' . MAIN_DB_PREFIX . $this->table_element.$mode. ' as t';
566  $sql .= ' WHERE 1 = 1';
567  $sql .= " AND entity IN (" . getEntity('accountancy') . ")";
568  if (null !== $ref) {
569  $sql .= ' AND t.ref = ' . '\'' . $ref . '\'';
570  } else {
571  $sql .= ' AND t.rowid = ' . $id;
572  }
573 
574  $resql = $this->db->query($sql);
575  if ($resql) {
576  $numrows = $this->db->num_rows($resql);
577  if ($numrows) {
578  $obj = $this->db->fetch_object($resql);
579 
580  $this->id = $obj->rowid;
581 
582  $this->doc_date = $this->db->jdate($obj->doc_date);
583  $this->date_lim_reglement = $this->db->jdate($obj->date_lim_reglement);
584  $this->doc_type = $obj->doc_type;
585  $this->doc_ref = $obj->doc_ref;
586  $this->fk_doc = $obj->fk_doc;
587  $this->fk_docdet = $obj->fk_docdet;
588  $this->thirdparty_code = $obj->thirdparty_code;
589  $this->subledger_account = $obj->subledger_account;
590  $this->subledger_label = $obj->subledger_label;
591  $this->numero_compte = $obj->numero_compte;
592  $this->label_compte = $obj->label_compte;
593  $this->label_operation = $obj->label_operation;
594  $this->debit = $obj->debit;
595  $this->credit = $obj->credit;
596  $this->montant = $obj->montant;
597  $this->sens = $obj->sens;
598  $this->fk_user_author = $obj->fk_user_author;
599  $this->import_key = $obj->import_key;
600  $this->code_journal = $obj->code_journal;
601  $this->journal_label = $obj->journal_label;
602  $this->piece_num = $obj->piece_num;
603  $this->date_creation = $this->db->jdate($obj->date_creation);
604  }
605  $this->db->free($resql);
606 
607  if ($numrows) {
608  return 1;
609  } else {
610  return 0;
611  }
612  } else {
613  $this->errors[] = 'Error ' . $this->db->lasterror();
614  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
615 
616  return - 1;
617  }
618  }
619 
620 
633  public function fetchAllByAccount($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, array $filter = array(), $filtermode = 'AND') {
634  global $conf;
635 
636  dol_syslog(__METHOD__, LOG_DEBUG);
637 
638  $this->lines = array();
639 
640  $sql = 'SELECT';
641  $sql .= ' t.rowid,';
642  $sql .= " t.doc_date,";
643  $sql .= " t.doc_type,";
644  $sql .= " t.doc_ref,";
645  $sql .= " t.fk_doc,";
646  $sql .= " t.fk_docdet,";
647  $sql .= " t.thirdparty_code,";
648  $sql .= " t.subledger_account,";
649  $sql .= " t.subledger_label,";
650  $sql .= " t.numero_compte,";
651  $sql .= " t.label_compte,";
652  $sql .= " t.label_operation,";
653  $sql .= " t.debit,";
654  $sql .= " t.credit,";
655  $sql .= " t.montant,";
656  $sql .= " t.sens,";
657  $sql .= " t.fk_user_author,";
658  $sql .= " t.import_key,";
659  $sql .= " t.code_journal,";
660  $sql .= " t.journal_label,";
661  $sql .= " t.piece_num,";
662  $sql .= " t.date_creation";
663  // Manage filter
664  $sqlwhere = array ();
665  if (count($filter) > 0) {
666  foreach ( $filter as $key => $value ) {
667  if ($key == 't.doc_date') {
668  $sqlwhere[] = $key . '=\'' . $this->db->idate($value) . '\'';
669  } elseif ($key == 't.doc_date>=' || $key == 't.doc_date<=') {
670  $sqlwhere[] = $key . '\'' . $this->db->idate($value) . '\'';
671  } elseif ($key == 't.numero_compte>=' || $key == 't.numero_compte<=' || $key == 't.subledger_account>=' || $key == 't.subledger_account<=') {
672  $sqlwhere[] = $key . '\'' . $this->db->escape($value) . '\'';
673  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
674  $sqlwhere[] = $key . '=' . $value;
675  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
676  $sqlwhere[] = $key . ' LIKE \'' . $this->db->escape($value) . '%\'';
677  } elseif ($key == 't.label_operation') {
678  $sqlwhere[] = $key . ' LIKE \'' . $this->db->escape($value) . '%\'';
679  } elseif ($key == 't.date_creation>=' || $key == 't.date_creation<=') {
680  $sqlwhere[] = $key . '\'' . $this->db->idate($value) . '\'';
681  } else {
682  $sqlwhere[] = $key . ' LIKE \'%' . $this->db->escape($value) . '%\'';
683  }
684  }
685  }
686  $sql .= ' FROM ' . MAIN_DB_PREFIX . $this->table_element . ' as t';
687  $sql .= ' WHERE 1 = 1';
688  $sql .= " AND entity IN (" . getEntity('accountancy') . ")";
689  if (count($sqlwhere) > 0) {
690  $sql .= ' AND ' . implode(' ' . $filtermode . ' ', $sqlwhere);
691  }
692  // Affichage par compte comptable
693  $sql .= ' ORDER BY t.numero_compte ASC';
694  if (! empty($sortfield)) {
695  $sql .= ', ' . $sortfield . ' ' .$sortorder;
696  }
697  if (! empty($limit)) {
698  $sql .= ' ' . $this->db->plimit($limit + 1, $offset);
699  }
700 
701  $resql = $this->db->query($sql);
702  if ($resql) {
703  $num = $this->db->num_rows($resql);
704 
705  while ( $obj = $this->db->fetch_object($resql) ) {
706  $line = new BookKeepingLine();
707 
708  $line->id = $obj->rowid;
709 
710  $line->doc_date = $this->db->jdate($obj->doc_date);
711  $line->doc_type = $obj->doc_type;
712  $line->doc_ref = $obj->doc_ref;
713  $line->fk_doc = $obj->fk_doc;
714  $line->fk_docdet = $obj->fk_docdet;
715  $line->thirdparty_code = $obj->thirdparty_code;
716  $line->subledger_account = $obj->subledger_account;
717  $line->subledger_label = $obj->subledger_label;
718  $line->numero_compte = $obj->numero_compte;
719  $line->label_compte = $obj->label_compte;
720  $line->label_operation = $obj->label_operation;
721  $line->debit = $obj->debit;
722  $line->credit = $obj->credit;
723  $line->montant = $obj->montant;
724  $line->sens = $obj->sens;
725  $line->fk_user_author = $obj->fk_user_author;
726  $line->import_key = $obj->import_key;
727  $line->code_journal = $obj->code_journal;
728  $line->journal_label = $obj->journal_label;
729  $line->piece_num = $obj->piece_num;
730  $line->date_creation = $obj->date_creation;
731 
732  $this->lines[] = $line;
733  }
734  $this->db->free($resql);
735 
736  return $num;
737  } else {
738  $this->errors[] = 'Error ' . $this->db->lasterror();
739  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
740 
741  return -1;
742  }
743  }
744 
757  public function fetchAll($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, array $filter = array(), $filtermode = 'AND') {
758  global $conf;
759 
760  dol_syslog(__METHOD__, LOG_DEBUG);
761 
762  $sql = 'SELECT';
763  $sql .= ' t.rowid,';
764  $sql .= " t.doc_date,";
765  $sql .= " t.doc_type,";
766  $sql .= " t.doc_ref,";
767  $sql .= " t.fk_doc,";
768  $sql .= " t.fk_docdet,";
769  $sql .= " t.thirdparty_code,";
770  $sql .= " t.subledger_account,";
771  $sql .= " t.subledger_label,";
772  $sql .= " t.numero_compte,";
773  $sql .= " t.label_compte,";
774  $sql .= " t.label_operation,";
775  $sql .= " t.debit,";
776  $sql .= " t.credit,";
777  $sql .= " t.montant,";
778  $sql .= " t.sens,";
779  $sql .= " t.fk_user_author,";
780  $sql .= " t.import_key,";
781  $sql .= " t.code_journal,";
782  $sql .= " t.journal_label,";
783  $sql .= " t.piece_num,";
784  $sql .= " t.date_creation";
785  $sql .= ' FROM ' . MAIN_DB_PREFIX . $this->table_element . ' as t';
786  // Manage filter
787  $sqlwhere = array ();
788  if (count($filter) > 0) {
789  foreach ( $filter as $key => $value ) {
790  if ($key == 't.doc_date') {
791  $sqlwhere[] = $key . '=\'' . $this->db->idate($value) . '\'';
792  } elseif ($key == 't.doc_date>=' || $key == 't.doc_date<=') {
793  $sqlwhere[] = $key . '\'' . $this->db->idate($value) . '\'';
794  } elseif ($key == 't.numero_compte>=' || $key == 't.numero_compte<=' || $key == 't.subledger_account>=' || $key == 't.subledger_account<=') {
795  $sqlwhere[] = $key . '\'' . $this->db->escape($value) . '\'';
796  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
797  $sqlwhere[] = $key . '=' . $value;
798  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
799  $sqlwhere[] = $key . ' LIKE \'' . $this->db->escape($value) . '%\'';
800  } elseif ($key == 't.date_creation>=' || $key == 't.date_creation<=') {
801  $sqlwhere[] = $key . '\'' . $this->db->idate($value) . '\'';
802  } elseif ($key == 't.tms>=' || $key == 't.tms<=') {
803  $sqlwhere[] = $key . '\'' . $this->db->idate($value) . '\'';
804  } else {
805  $sqlwhere[] = $key . ' LIKE \'%' . $this->db->escape($value) . '%\'';
806  }
807  }
808  }
809  $sql.= ' WHERE 1 = 1';
810  $sql .= " AND entity IN (" . getEntity('accountancy') . ")";
811  if (count($sqlwhere) > 0) {
812  $sql .= ' AND ' . implode(' ' . $filtermode . ' ', $sqlwhere);
813  }
814 
815  if (! empty($sortfield)) {
816  $sql .= $this->db->order($sortfield, $sortorder);
817  }
818  if (! empty($limit)) {
819  $sql .= ' ' . $this->db->plimit($limit + 1, $offset);
820  }
821  $this->lines = array();
822 
823  $resql = $this->db->query($sql);
824  if ($resql) {
825  $num = $this->db->num_rows($resql);
826 
827  while ( $obj = $this->db->fetch_object($resql) ) {
828  $line = new BookKeepingLine();
829 
830  $line->id = $obj->rowid;
831 
832  $line->doc_date = $this->db->jdate($obj->doc_date);
833  $line->doc_type = $obj->doc_type;
834  $line->doc_ref = $obj->doc_ref;
835  $line->fk_doc = $obj->fk_doc;
836  $line->fk_docdet = $obj->fk_docdet;
837  $line->thirdparty_code = $obj->thirdparty_code;
838  $line->subledger_account = $obj->subledger_account;
839  $line->subledger_label = $obj->subledger_label;
840  $line->numero_compte = $obj->numero_compte;
841  $line->label_compte = $obj->label_compte;
842  $line->label_operation = $obj->label_operation;
843  $line->debit = $obj->debit;
844  $line->credit = $obj->credit;
845  $line->montant = $obj->montant;
846  $line->sens = $obj->sens;
847  $line->fk_user_author = $obj->fk_user_author;
848  $line->import_key = $obj->import_key;
849  $line->code_journal = $obj->code_journal;
850  $line->journal_label = $obj->journal_label;
851  $line->piece_num = $obj->piece_num;
852  $line->date_creation = $obj->date_creation;
853 
854  $this->lines[] = $line;
855  }
856  $this->db->free($resql);
857 
858  return $num;
859  } else {
860  $this->errors[] = 'Error ' . $this->db->lasterror();
861  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
862 
863  return - 1;
864  }
865  }
866 
879  public function fetchAllBalance($sortorder = '', $sortfield = '', $limit = 0, $offset = 0, array $filter = array(), $filtermode = 'AND')
880  {
881  global $conf;
882 
883  $this->lines = array();
884 
885  dol_syslog(__METHOD__, LOG_DEBUG);
886 
887  $sql = 'SELECT';
888  $sql .= " t.numero_compte,";
889  $sql .= " SUM(t.debit) as debit,";
890  $sql .= " SUM(t.credit) as credit";
891  $sql .= ' FROM ' . MAIN_DB_PREFIX . $this->table_element . ' as t';
892  // Manage filter
893  $sqlwhere = array ();
894  if (count($filter) > 0) {
895  foreach ( $filter as $key => $value ) {
896  if ($key == 't.doc_date') {
897  $sqlwhere[] = $key . '=\'' . $this->db->idate($value) . '\'';
898  } elseif ($key == 't.doc_date>=' || $key == 't.doc_date<=') {
899  $sqlwhere[] = $key . '\'' . $this->db->idate($value) . '\'';
900  } elseif ($key == 't.numero_compte>=' || $key == 't.numero_compte<=' || $key == 't.subledger_account>=' || $key == 't.subledger_account<=') {
901  $sqlwhere[] = $key . '\'' . $this->db->escape($value) . '\'';
902  } elseif ($key == 't.fk_doc' || $key == 't.fk_docdet' || $key == 't.piece_num') {
903  $sqlwhere[] = $key . '=' . $value;
904  } elseif ($key == 't.subledger_account' || $key == 't.numero_compte') {
905  $sqlwhere[] = $key . ' LIKE \'' . $this->db->escape($value) . '%\'';
906  } elseif ($key == 't.subledger_label') {
907  $sqlwhere[] = $key . ' LIKE \'' . $this->db->escape($value) . '%\'';
908  } else {
909  $sqlwhere[] = $key . ' LIKE \'%' . $this->db->escape($value) . '%\'';
910  }
911  }
912  }
913  $sql.= ' WHERE 1 = 1';
914  $sql .= " AND entity IN (" . getEntity('accountancy') . ")";
915  if (count($sqlwhere) > 0) {
916  $sql .= ' AND ' . implode(' ' . $filtermode . ' ', $sqlwhere);
917  }
918 
919  $sql .= ' GROUP BY t.numero_compte';
920 
921  if (! empty($sortfield)) {
922  $sql .= $this->db->order($sortfield, $sortorder);
923  }
924  if (! empty($limit)) {
925  $sql .= ' ' . $this->db->plimit($limit + 1, $offset);
926  }
927 
928  $resql = $this->db->query($sql);
929  if ($resql) {
930  $num = $this->db->num_rows($resql);
931 
932  while ( $obj = $this->db->fetch_object($resql) ) {
933  $line = new BookKeepingLine();
934 
935  $line->numero_compte = $obj->numero_compte;
936  $line->debit = $obj->debit;
937  $line->credit = $obj->credit;
938  $this->lines[] = $line;
939  }
940  $this->db->free($resql);
941 
942  return $num;
943  } else {
944  $this->errors[] = 'Error ' . $this->db->lasterror();
945  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
946 
947  return - 1;
948  }
949  }
950 
959  public function update(User $user, $notrigger = false, $mode='') {
960  $error = 0;
961 
962  dol_syslog(__METHOD__, LOG_DEBUG);
963 
964  // Clean parameters
965  if (isset($this->doc_type)) {
966  $this->doc_type = trim($this->doc_type);
967  }
968  if (isset($this->doc_ref)) {
969  $this->doc_ref = trim($this->doc_ref);
970  }
971  if (isset($this->fk_doc)) {
972  $this->fk_doc = trim($this->fk_doc);
973  }
974  if (isset($this->fk_docdet)) {
975  $this->fk_docdet = trim($this->fk_docdet);
976  }
977  if (isset($this->thirdparty_code)) {
978  $this->thirdparty_code = trim($this->thirdparty_code);
979  }
980  if (isset($this->subledger_account)) {
981  $this->subledger_account = trim($this->subledger_account);
982  }
983  if (isset($this->subledger_label)) {
984  $this->subledger_label = trim($this->subledger_label);
985  }
986  if (isset($this->numero_compte)) {
987  $this->numero_compte = trim($this->numero_compte);
988  }
989  if (isset($this->label_compte)) {
990  $this->label_compte = trim($this->label_compte);
991  }
992  if (isset($this->label_operation)) {
993  $this->label_operation = trim($this->label_operation);
994  }
995  if (isset($this->debit)) {
996  $this->debit = trim($this->debit);
997  }
998  if (isset($this->credit)) {
999  $this->credit = trim($this->credit);
1000  }
1001  if (isset($this->montant)) {
1002  $this->montant = trim($this->montant);
1003  }
1004  if (isset($this->sens)) {
1005  $this->sens = trim($this->sens);
1006  }
1007  if (isset($this->fk_user_author)) {
1008  $this->fk_user_author = trim($this->fk_user_author);
1009  }
1010  if (isset($this->import_key)) {
1011  $this->import_key = trim($this->import_key);
1012  }
1013  if (isset($this->code_journal)) {
1014  $this->code_journal = trim($this->code_journal);
1015  }
1016  if (isset($this->journal_label)) {
1017  $this->journal_label = trim($this->journal_label);
1018  }
1019  if (isset($this->piece_num)) {
1020  $this->piece_num = trim($this->piece_num);
1021  }
1022 
1023  $this->debit = price2num($this->debit, 'MT');
1024  $this->credit = price2num($this->credit, 'MT');
1025 
1026  // Check parameters
1027  // Put here code to add a control on parameters values
1028 
1029  // Update request
1030  $sql = 'UPDATE ' . MAIN_DB_PREFIX . $this->table_element . $mode.' SET';
1031  $sql .= ' doc_date = ' . (! isset($this->doc_date) || dol_strlen($this->doc_date) != 0 ? "'" . $this->db->idate($this->doc_date) . "'" : 'null') . ',';
1032  $sql .= ' doc_type = ' . (isset($this->doc_type) ? "'" . $this->db->escape($this->doc_type) . "'" : "null") . ',';
1033  $sql .= ' doc_ref = ' . (isset($this->doc_ref) ? "'" . $this->db->escape($this->doc_ref) . "'" : "null") . ',';
1034  $sql .= ' fk_doc = ' . (isset($this->fk_doc) ? $this->fk_doc : "null") . ',';
1035  $sql .= ' fk_docdet = ' . (isset($this->fk_docdet) ? $this->fk_docdet : "null") . ',';
1036  $sql .= ' thirdparty_code = ' . (isset($this->thirdparty_code) ? "'" . $this->db->escape($this->thirdparty_code) . "'" : "null") . ',';
1037  $sql .= ' subledger_account = ' . (isset($this->subledger_account) ? "'" . $this->db->escape($this->subledger_account) . "'" : "null") . ',';
1038  $sql .= ' subledger_label = ' . (isset($this->subledger_label) ? "'" . $this->db->escape($this->subledger_label) . "'" : "null") . ',';
1039  $sql .= ' numero_compte = ' . (isset($this->numero_compte) ? "'" . $this->db->escape($this->numero_compte) . "'" : "null") . ',';
1040  $sql .= ' label_compte = ' . (isset($this->label_compte) ? "'" . $this->db->escape($this->label_compte) . "'" : "null") . ',';
1041  $sql .= ' label_operation = ' . (isset($this->label_operation) ? "'" . $this->db->escape($this->label_operation) . "'" : "null") . ',';
1042  $sql .= ' debit = ' . (isset($this->debit) ? $this->debit : "null") . ',';
1043  $sql .= ' credit = ' . (isset($this->credit) ? $this->credit : "null") . ',';
1044  $sql .= ' montant = ' . (isset($this->montant) ? $this->montant : "null") . ',';
1045  $sql .= ' sens = ' . (isset($this->sens) ? "'" . $this->db->escape($this->sens) . "'" : "null") . ',';
1046  $sql .= ' fk_user_author = ' . (isset($this->fk_user_author) ? $this->fk_user_author : "null") . ',';
1047  $sql .= ' import_key = ' . (isset($this->import_key) ? "'" . $this->db->escape($this->import_key) . "'" : "null") . ',';
1048  $sql .= ' code_journal = ' . (isset($this->code_journal) ? "'" . $this->db->escape($this->code_journal) . "'" : "null") . ',';
1049  $sql .= ' journal_label = ' . (isset($this->journal_label) ? "'" . $this->db->escape($this->journal_label) . "'" : "null") . ',';
1050  $sql .= ' piece_num = ' . (isset($this->piece_num) ? $this->piece_num : "null");
1051  $sql .= ' WHERE rowid=' . $this->id;
1052 
1053  $this->db->begin();
1054 
1055  $resql = $this->db->query($sql);
1056  if (! $resql) {
1057  $error ++;
1058  $this->errors[] = 'Error ' . $this->db->lasterror();
1059  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
1060  }
1061 
1062  if (! $error && ! $notrigger) {
1063  // Uncomment this and change MYOBJECT to your own tag if you
1064  // want this action calls a trigger.
1065 
1066  // // Call triggers
1067  // $result=$this->call_trigger('MYOBJECT_MODIFY',$user);
1068  // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1069  // // End call triggers
1070  }
1071 
1072  // Commit or rollback
1073  if ($error) {
1074  $this->db->rollback();
1075 
1076  return - 1 * $error;
1077  } else {
1078  $this->db->commit();
1079 
1080  return 1;
1081  }
1082  }
1083 
1093  public function updateByMvt($piece_num='', $field='', $value='', $mode='')
1094  {
1095  $error=0;
1096 
1097  $this->db->begin();
1098 
1099  $sql = "UPDATE " . MAIN_DB_PREFIX . $this->table_element . $mode . " as ab";
1100  $sql .= ' SET ab.' . $field . '=' . (is_numeric($value)?$value:"'".$value."'");
1101  $sql .= ' WHERE ab.piece_num=' . $piece_num ;
1102  $resql = $this->db->query($sql);
1103 
1104  if (! $resql) {
1105  $error++;
1106  $this->errors[] = 'Error ' . $this->db->lasterror();
1107  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
1108  }
1109  if ($error) {
1110  $this->db->rollback();
1111 
1112  return -1 * $error;
1113  } else {
1114  $this->db->commit();
1115 
1116  return 1;
1117  }
1118  }
1119 
1128  public function delete(User $user, $notrigger = false, $mode='') {
1129  dol_syslog(__METHOD__, LOG_DEBUG);
1130 
1131  $error = 0;
1132 
1133  $this->db->begin();
1134 
1135  if (! $error) {
1136  if (! $notrigger) {
1137  // Uncomment this and change MYOBJECT to your own tag if you
1138  // want this action calls a trigger.
1139 
1140  // // Call triggers
1141  // $result=$this->call_trigger('MYOBJECT_DELETE',$user);
1142  // if ($result < 0) { $error++; //Do also what you must do to rollback action if trigger fail}
1143  // // End call triggers
1144  }
1145  }
1146 
1147  if (! $error) {
1148  $sql = 'DELETE FROM ' . MAIN_DB_PREFIX . $this->table_element.$mode;
1149  $sql .= ' WHERE rowid=' . $this->id;
1150 
1151  $resql = $this->db->query($sql);
1152  if (! $resql) {
1153  $error ++;
1154  $this->errors[] = 'Error ' . $this->db->lasterror();
1155  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
1156  }
1157  }
1158 
1159  // Commit or rollback
1160  if ($error) {
1161  $this->db->rollback();
1162 
1163  return - 1 * $error;
1164  } else {
1165  $this->db->commit();
1166 
1167  return 1;
1168  }
1169  }
1170 
1177  function deleteByImportkey($importkey) {
1178  $this->db->begin();
1179 
1180  // first check if line not yet in bookkeeping
1181  $sql = "DELETE";
1182  $sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
1183  $sql .= " WHERE import_key = '" . $importkey . "'";
1184 
1185  $resql = $this->db->query($sql);
1186 
1187  if (! $resql) {
1188  $this->errors[] = "Error " . $this->db->lasterror();
1189  dol_syslog(get_class($this)."::delete Error " . $this->db->lasterror(), LOG_ERR);
1190  $this->db->rollback();
1191  return - 1;
1192  }
1193 
1194  $this->db->commit();
1195  return 1;
1196  }
1197 
1206  function deleteByYearAndJournal($delyear='', $journal='', $mode='') {
1207  global $conf;
1208 
1209  if (empty($delyear) && empty($journal))
1210  {
1211  return -1;
1212  }
1213 
1214  $this->db->begin();
1215 
1216  // first check if line not yet in bookkeeping
1217  $sql = "DELETE";
1218  $sql.= " FROM " . MAIN_DB_PREFIX . $this->table_element.$mode;
1219  $sql.= " WHERE 1 = 1";
1220  if (! empty($delyear)) $sql.= " AND YEAR(doc_date) = " . $delyear; // FIXME Must use between
1221  if (! empty($journal)) $sql.= " AND code_journal = '".$journal."'";
1222  $sql .= " AND entity IN (" . getEntity('accountancy') . ")";
1223  $resql = $this->db->query($sql);
1224 
1225  if (! $resql) {
1226  $this->errors[] = "Error " . $this->db->lasterror();
1227  foreach ( $this->errors as $errmsg ) {
1228  dol_syslog(get_class($this) . "::delete " . $errmsg, LOG_ERR);
1229  $this->error .= ($this->error ? ', ' . $errmsg : $errmsg);
1230  }
1231  $this->db->rollback();
1232  return -1;
1233  }
1234 
1235  $this->db->commit();
1236  return 1;
1237  }
1238 
1245  function deleteMvtNum($piecenum) {
1246  global $conf;
1247 
1248  $this->db->begin();
1249 
1250  // first check if line not yet in bookkeeping
1251  $sql = "DELETE";
1252  $sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
1253  $sql .= " WHERE piece_num = " . $piecenum;
1254  $sql .= " AND entity IN (" . getEntity('accountancy') . ")";
1255 
1256  $resql = $this->db->query($sql);
1257 
1258  if (! $resql) {
1259  $this->errors[] = "Error " . $this->db->lasterror();
1260  foreach ( $this->errors as $errmsg ) {
1261  dol_syslog(get_class($this) . "::delete " . $errmsg, LOG_ERR);
1262  $this->error .= ($this->error ? ', ' . $errmsg : $errmsg);
1263  }
1264  $this->db->rollback();
1265  return - 1;
1266  }
1267 
1268  $this->db->commit();
1269  return 1;
1270  }
1271 
1279  public function createFromClone($fromid) {
1280  dol_syslog(__METHOD__, LOG_DEBUG);
1281 
1282  global $user;
1283  $error = 0;
1284  $object = new BookKeeping($this->db);
1285 
1286  $this->db->begin();
1287 
1288  // Load source object
1289  $object->fetch($fromid);
1290  // Reset object
1291  $object->id = 0;
1292 
1293  // Clear fields
1294  // ...
1295 
1296  // Create clone
1297  $result = $object->create($user);
1298 
1299  // Other options
1300  if ($result < 0) {
1301  $error ++;
1302  $this->errors = $object->errors;
1303  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
1304  }
1305 
1306  // End
1307  if (! $error) {
1308  $this->db->commit();
1309 
1310  return $object->id;
1311  } else {
1312  $this->db->rollback();
1313 
1314  return - 1;
1315  }
1316  }
1317 
1324  public function initAsSpecimen() {
1325  global $user;
1326 
1327  $now=dol_now();
1328 
1329  $this->id = 0;
1330  $this->doc_date = $now;
1331  $this->doc_type = '';
1332  $this->doc_ref = '';
1333  $this->fk_doc = '';
1334  $this->fk_docdet = '';
1335  $this->thirdparty_code = 'CU001';
1336  $this->subledger_account = '41100001';
1337  $this->subledger_label = 'My customer company';
1338  $this->numero_compte = '411';
1339  $this->label_compte = 'Customer';
1340  $this->label_operation = 'Sales of pea';
1341  $this->debit = 99.9;
1342  $this->credit = '';
1343  $this->montant = '';
1344  $this->sens = 'D';
1345  $this->fk_user_author = $user->id;
1346  $this->import_key = '';
1347  $this->code_journal = 'VT';
1348  $this->journal_label = 'Journal de vente';
1349  $this->piece_num = '';
1350  $this->date_creation = $now;
1351  }
1352 
1360  public function fetchPerMvt($piecenum, $mode='') {
1361  global $conf;
1362 
1363  $sql = "SELECT piece_num,doc_date,code_journal,journal_label,doc_ref,doc_type,date_creation";
1364  $sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element.$mode;
1365  $sql .= " WHERE piece_num = " . $piecenum;
1366  $sql .= " AND entity IN (" . getEntity('accountancy') . ")";
1367 
1368  dol_syslog(get_class($this) . "::" . __METHOD__, LOG_DEBUG);
1369  $result = $this->db->query($sql);
1370  if ($result) {
1371  $obj = $this->db->fetch_object($result);
1372 
1373  $this->piece_num = $obj->piece_num;
1374  $this->code_journal = $obj->code_journal;
1375  $this->journal_label = $obj->journal_label;
1376  $this->doc_date = $this->db->jdate($obj->doc_date);
1377  $this->doc_ref = $obj->doc_ref;
1378  $this->doc_type = $obj->doc_type;
1379  $this->date_creation = $obj->date_creation;
1380  } else {
1381  $this->error = "Error " . $this->db->lasterror();
1382  dol_syslog(get_class($this) . "::" . __METHOD__ . $this->error, LOG_ERR);
1383  return - 1;
1384  }
1385 
1386  return 1;
1387  }
1388 
1395  public function getNextNumMvt($mode='')
1396  {
1397  global $conf;
1398 
1399  $sql = "SELECT MAX(piece_num)+1 as max FROM " . MAIN_DB_PREFIX . $this->table_element.$mode;
1400  $sql .= " WHERE entity IN (" . getEntity('accountancy') . ")";
1401 
1402  dol_syslog(get_class($this) . "getNextNumMvt sql=" . $sql, LOG_DEBUG);
1403  $result = $this->db->query($sql);
1404 
1405  if ($result) {
1406  $obj = $this->db->fetch_object($result);
1407  if ($obj) $result = $obj->max;
1408  if (empty($result)) $result = 1;
1409  return $result;
1410  } else {
1411  $this->error = "Error " . $this->db->lasterror();
1412  dol_syslog(get_class($this) . "::getNextNumMvt " . $this->error, LOG_ERR);
1413  return - 1;
1414  }
1415  }
1416 
1424  function fetchAllPerMvt($piecenum, $mode='') {
1425  global $conf;
1426 
1427  $sql = "SELECT rowid, doc_date, doc_type,";
1428  $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1429  $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1430  $sql .= " montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num, date_creation";
1431  $sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element.$mode;
1432  $sql .= " WHERE piece_num = " . $piecenum;
1433  $sql .= " AND entity IN (" . getEntity('accountancy') . ")";
1434 
1435  dol_syslog(get_class($this) . "::" . __METHOD__, LOG_DEBUG);
1436  $result = $this->db->query($sql);
1437  if ($result) {
1438 
1439  while ( $obj = $this->db->fetch_object($result) ) {
1440 
1441  $line = new BookKeepingLine();
1442 
1443  $line->id = $obj->rowid;
1444 
1445  $line->doc_date = $this->db->jdate($obj->doc_date);
1446  $line->doc_type = $obj->doc_type;
1447  $line->doc_ref = $obj->doc_ref;
1448  $line->fk_doc = $obj->fk_doc;
1449  $line->fk_docdet = $obj->fk_docdet;
1450  $line->thirdparty_code = $obj->thirdparty_code;
1451  $line->subledger_account = $obj->subledger_account;
1452  $line->subledger_label = $obj->subledger_label;
1453  $line->numero_compte = $obj->numero_compte;
1454  $line->label_compte = $obj->label_compte;
1455  $line->label_operation = $obj->label_operation;
1456  $line->debit = $obj->debit;
1457  $line->credit = $obj->credit;
1458  $line->montant = $obj->montant;
1459  $line->sens = $obj->sens;
1460  $line->code_journal = $obj->code_journal;
1461  $line->journal_label = $obj->journal_label;
1462  $line->piece_num = $obj->piece_num;
1463  $line->date_creation = $obj->date_creation;
1464 
1465  $this->linesmvt[] = $line;
1466  }
1467  } else {
1468  $this->error = "Error " . $this->db->lasterror();
1469  dol_syslog(get_class($this) . "::" . __METHOD__ . $this->error, LOG_ERR);
1470  return - 1;
1471  }
1472 
1473  return 1;
1474  }
1475 
1482  function export_bookkeping($model = 'ebp') {
1483  global $conf;
1484 
1485  $sql = "SELECT rowid, doc_date, doc_type,";
1486  $sql .= " doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,";
1487  $sql .= " numero_compte, label_compte, label_operation, debit, credit,";
1488  $sql .= " montant, sens, fk_user_author, import_key, code_journal, piece_num";
1489  $sql .= " FROM " . MAIN_DB_PREFIX . $this->table_element;
1490  $sql .= " WHERE entity IN (" . getEntity('accountancy') . ")";
1491 
1492  dol_syslog(get_class($this) . "::export_bookkeping", LOG_DEBUG);
1493 
1494  $resql = $this->db->query($sql);
1495 
1496  if ($resql) {
1497  $this->linesexport = array ();
1498 
1499  $num = $this->db->num_rows($resql);
1500  while ( $obj = $this->db->fetch_object($resql) ) {
1501  $line = new BookKeepingLine();
1502 
1503  $line->id = $obj->rowid;
1504 
1505  $line->doc_date = $this->db->jdate($obj->doc_date);
1506  $line->doc_type = $obj->doc_type;
1507  $line->doc_ref = $obj->doc_ref;
1508  $line->fk_doc = $obj->fk_doc;
1509  $line->fk_docdet = $obj->fk_docdet;
1510  $line->thirdparty_code = $obj->thirdparty_code;
1511  $line->subledger_account = $obj->subledger_account;
1512  $line->subledger_label = $obj->subledger_label;
1513  $line->numero_compte = $obj->numero_compte;
1514  $line->label_compte = $obj->label_compte;
1515  $line->label_operation = $obj->label_operation;
1516  $line->debit = $obj->debit;
1517  $line->credit = $obj->credit;
1518  $line->montant = $obj->montant;
1519  $line->sens = $obj->sens;
1520  $line->code_journal = $obj->code_journal;
1521  $line->piece_num = $obj->piece_num;
1522 
1523  $this->linesexport[] = $line;
1524  }
1525  $this->db->free($resql);
1526 
1527  return $num;
1528  } else {
1529  $this->error = "Error " . $this->db->lasterror();
1530  dol_syslog(get_class($this) . "::export_bookkeping " . $this->error, LOG_ERR);
1531  return - 1;
1532  }
1533  }
1534 
1542  public function transformTransaction($direction=0,$piece_num='')
1543  {
1544  $error = 0;
1545 
1546  $this->db->begin();
1547 
1548  if ($direction==0)
1549  {
1550  $next_piecenum=$this->getNextNumMvt();
1551  if ($next_piecenum < 0) {
1552  $error++;
1553  }
1554  $sql = 'INSERT INTO ' . MAIN_DB_PREFIX . $this->table_element.'(doc_date, doc_type,';
1555  $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1556  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1557  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num)';
1558  $sql .= 'SELECT doc_date, doc_type,';
1559  $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1560  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1561  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, '.$next_piecenum.'';
1562  $sql .= ' FROM '.MAIN_DB_PREFIX . $this->table_element.'_tmp WHERE piece_num = '.$piece_num;
1563  $resql = $this->db->query($sql);
1564  if (! $resql) {
1565  $error ++;
1566  $this->errors[] = 'Error ' . $this->db->lasterror();
1567  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
1568  }
1569  $sql = 'DELETE FROM '.MAIN_DB_PREFIX . $this->table_element.'_tmp WHERE piece_num = '.$piece_num;
1570  $resql = $this->db->query($sql);
1571  if (! $resql) {
1572  $error ++;
1573  $this->errors[] = 'Error ' . $this->db->lasterror();
1574  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
1575  }
1576  }
1577  if ($direction==1) {
1578  $sql = 'DELETE FROM ' . MAIN_DB_PREFIX . $this->table_element.'_tmp WHERE piece_num = '.$piece_num;
1579  $resql = $this->db->query($sql);
1580  if (! $resql) {
1581  $error ++;
1582  $this->errors[] = 'Error ' . $this->db->lasterror();
1583  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
1584  }
1585  $sql = 'INSERT INTO ' . MAIN_DB_PREFIX . $this->table_element.'_tmp(doc_date, doc_type,';
1586  $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1587  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1588  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num)';
1589  $sql .= 'SELECT doc_date, doc_type,';
1590  $sql .= ' doc_ref, fk_doc, fk_docdet, thirdparty_code, subledger_account, subledger_label,';
1591  $sql .= ' numero_compte, label_compte, label_operation, debit, credit,';
1592  $sql .= ' montant, sens, fk_user_author, import_key, code_journal, journal_label, piece_num';
1593  $sql .= ' FROM '.MAIN_DB_PREFIX . $this->table_element.' WHERE piece_num = '.$piece_num;
1594  $resql = $this->db->query($sql);
1595  if (! $resql) {
1596  $error ++;
1597  $this->errors[] = 'Error ' . $this->db->lasterror();
1598  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
1599  }
1600  $sql = 'DELETE FROM '.MAIN_DB_PREFIX . $this->table_element.'_tmp WHERE piece_num = '.$piece_num;
1601  $resql = $this->db->query($sql);
1602  if (! $resql) {
1603  $error ++;
1604  $this->errors[] = 'Error ' . $this->db->lasterror();
1605  dol_syslog(__METHOD__ . ' ' . join(',', $this->errors), LOG_ERR);
1606  }
1607  }
1608  if (! $error) {
1609  $this->db->commit();
1610  return 1;
1611  } else {
1612  $this->db->rollback();
1613  return - 1;
1614  }
1615  /*
1616  $sql = "DELETE FROM ";
1617  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as ab";
1618  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as aa ON aa.account_number = ab.numero_compte";
1619  $sql .= " AND aa.active = 1";
1620  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
1621  $sql .= " AND asy.rowid = " . $pcgver;
1622  $sql .= " AND ab.entity IN (" . getEntity('accountancy') . ")";
1623  $sql .= " ORDER BY account_number ASC";
1624  */
1625  }
1626 
1639  function select_account($selectid, $htmlname = 'account', $showempty = 0, $event = array(), $select_in = 0, $select_out = 0, $aabase = '') {
1640  global $conf;
1641 
1642  require_once DOL_DOCUMENT_ROOT . '/core/lib/accounting.lib.php';
1643 
1644  $pcgver = $conf->global->CHARTOFACCOUNTS;
1645 
1646  $sql = "SELECT DISTINCT ab.numero_compte as account_number, aa.label as label, aa.rowid as rowid, aa.fk_pcg_version";
1647  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_bookkeeping as ab";
1648  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as aa ON aa.account_number = ab.numero_compte";
1649  $sql .= " AND aa.active = 1";
1650  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
1651  $sql .= " AND asy.rowid = " . $pcgver;
1652  $sql .= " AND ab.entity IN (" . getEntity('accountancy') . ")";
1653  $sql .= " ORDER BY account_number ASC";
1654 
1655  dol_syslog(get_class($this) . "::select_account", LOG_DEBUG);
1656  $resql = $this->db->query($sql);
1657 
1658  if (! $resql) {
1659  $this->error = "Error " . $this->db->lasterror();
1660  dol_syslog(get_class($this) . "::select_account " . $this->error, LOG_ERR);
1661  return - 1;
1662  }
1663 
1664  $out = ajax_combobox($htmlname, $event);
1665 
1666  $options = array();
1667  $selected = null;
1668 
1669  while ($obj = $this->db->fetch_object($resql)) {
1670  $label = length_accountg($obj->account_number) . ' - ' . $obj->label;
1671 
1672  $select_value_in = $obj->rowid;
1673  $select_value_out = $obj->rowid;
1674 
1675  if ($select_in == 1) {
1676  $select_value_in = $obj->account_number;
1677  }
1678  if ($select_out == 1) {
1679  $select_value_out = $obj->account_number;
1680  }
1681 
1682  // Remember guy's we store in database llx_facturedet the rowid of accounting_account and not the account_number
1683  // Because same account_number can be share between different accounting_system and do have the same meaning
1684  if (($selectid != '') && $selectid == $select_value_in) {
1685  $selected = $select_value_out;
1686  }
1687 
1688  $options[$select_value_out] = $label;
1689  }
1690 
1691  $out .= Form::selectarray($htmlname, $options, $selected, $showempty, 0, 0, '', 0, 0, 0, '', 'maxwidth300');
1692  $this->db->free($resql);
1693  return $out;
1694  }
1695 
1702  function get_compte_racine($account = null)
1703  {
1704  global $conf;
1705  $pcgver = $conf->global->CHARTOFACCOUNTS;
1706 
1707  $sql = "SELECT root.account_number, root.label as label";
1708  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as aa";
1709  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
1710  $sql .= " AND asy.rowid = " . $pcgver;
1711  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as parent ON aa.account_parent = parent.rowid";
1712  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "accounting_account as root ON parent.account_parent = root.rowid";
1713  $sql .= " WHERE aa.account_number = '" . $account . "'";
1714  $sql .= " AND parent.active = 1";
1715  $sql .= " AND root.active = 1";
1716  $sql .= " AND aa.entity IN (" . getEntity('accountancy') . ")";
1717 
1718  dol_syslog(get_class($this) . "::select_account sql=" . $sql, LOG_DEBUG);
1719  $resql = $this->db->query($sql);
1720  if ($resql) {
1721  $obj = '';
1722  if ($this->db->num_rows($resql)) {
1723  $obj = $this->db->fetch_object($resql);
1724  }
1725 
1726  return $obj->label;
1727 
1728  } else {
1729  $this->error = "Error " . $this->db->lasterror();
1730  dol_syslog(__METHOD__ . " " . $this->error, LOG_ERR);
1731 
1732  return -1;
1733  }
1734  }
1735 
1742  function get_compte_desc($account = null)
1743  {
1744  global $conf;
1745 
1746  $pcgver = $conf->global->CHARTOFACCOUNTS;
1747  $sql = "SELECT aa.account_number, aa.label, aa.rowid, aa.fk_pcg_version, cat.label as category";
1748  $sql .= " FROM " . MAIN_DB_PREFIX . "accounting_account as aa ";
1749  $sql .= " INNER JOIN " . MAIN_DB_PREFIX . "accounting_system as asy ON aa.fk_pcg_version = asy.pcg_version";
1750  $sql .= " AND aa.account_number = '" . $account . "'";
1751  $sql .= " AND asy.rowid = " . $pcgver;
1752  $sql .= " AND aa.active = 1";
1753  $sql .= " LEFT JOIN " . MAIN_DB_PREFIX . "c_accounting_category as cat ON aa.fk_accounting_category = cat.rowid";
1754  $sql .= " WHERE aa.entity IN (" . getEntity('accountancy') . ")";
1755 
1756  dol_syslog(get_class($this) . "::select_account sql=" . $sql, LOG_DEBUG);
1757  $resql = $this->db->query($sql);
1758  if ($resql) {
1759  $obj = '';
1760  if ($this->db->num_rows($resql)) {
1761  $obj = $this->db->fetch_object($resql);
1762  }
1763  if(empty($obj->category)){
1764  return $obj->label;
1765  }else{
1766  return $obj->label.' ('.$obj->category.')';
1767  }
1768  } else {
1769  $this->error = "Error " . $this->db->lasterror();
1770  dol_syslog(__METHOD__ . " " . $this->error, LOG_ERR);
1771  return -1;
1772  }
1773  }
1774 }
1775 
1780 {
1781  public $id;
1782  public $doc_date = '';
1783  public $doc_type;
1784  public $doc_ref;
1785  public $fk_doc;
1786  public $fk_docdet;
1787  public $thirdparty_code;
1788  public $subledger_account;
1789  public $subledger_label;
1790  public $numero_compte;
1791  public $label_compte;
1792  public $label_operation;
1793  public $debit;
1794  public $credit;
1795  public $montant;
1796  public $sens;
1797  public $fk_user_author;
1798  public $import_key;
1799  public $code_journal;
1800  public $journal_label;
1801  public $piece_num;
1802  public $date_creation;
1803 }
deleteByImportkey($importkey)
Delete bookkepping by importkey.
ajax_combobox($htmlname, $events=array(), $minLengthToAutocomplete=0, $forcefocus=0, $widthTypeOfAutocomplete='resolve')
Convert a html select field into an ajax combobox.
Definition: ajax.lib.php:392
fetchAllByAccount($sortorder= '', $sortfield= '', $limit=0, $offset=0, array $filter=array(), $filtermode= 'AND')
Load object in memory from the database.
Class to manage Dolibarr users.
Definition: user.class.php:39
Class to manage Dolibarr database access.
deleteMvtNum($piecenum)
Delete bookkepping by piece number.
initAsSpecimen()
Initialise object with example values Id must be 0 if object instance is a specimen.
__construct(DoliDB $db)
Constructor.
deleteByYearAndJournal($delyear='', $journal='', $mode='')
Delete bookkepping by year.
getNextNumMvt($mode='')
Return next number movement.
Class to manage Ledger (General Ledger and Subledger)
get_compte_racine($account=null)
Description of a root accounting account.
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=0, $moreparamonempty='', $disablebademail=0, $nohtmlescape=0)
Return a HTML select string, built from an array of key+value.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
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.
fetchAllBalance($sortorder= '', $sortfield= '', $limit=0, $offset=0, array $filter=array(), $filtermode= 'AND')
Load object in memory from the database.
update(User $user, $notrigger=false, $mode='')
Update object into database.
getEntity($element, $shared=1, $forceentity=null)
Get list of entity id to use.
transformTransaction($direction=0, $piece_num='')
Transform transaction.
dol_now($mode='gmt')
Return date for now.
export_bookkeping($model= 'ebp')
Export bookkeping.
fetchPerMvt($piecenum, $mode='')
Load an accounting document into memory from database.
createFromClone($fromid)
Load an object from its id and create a new one in database.
fetchAllPerMvt($piecenum, $mode='')
Load all informations of accountancy document.
create(User $user, $notrigger=false)
Create object into database.
fetch($id, $ref=null, $mode='')
Load object in memory from the database.
if(!empty($conf->facture->enabled)&&$user->rights->facture->lire) if(!empty($conf->fournisseur->enabled)&&$user->rights->fournisseur->facture->lire) if(!empty($conf->don->enabled)&&$user->rights->societe->lire) if(!empty($conf->tax->enabled)&&$user->rights->tax->charges->lire) if(!empty($conf->facture->enabled)&&!empty($conf->commande->enabled)&&$user->rights->commande->lire &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) if(!empty($conf->facture->enabled)&&$user->rights->facture->lire) if(!empty($conf->fournisseur->enabled)&&$user->rights->fournisseur->facture->lire) $resql
Social contributions to pay.
Definition: index.php:1013
get_compte_desc($account=null)
Description of accounting account.
fetchAll($sortorder= '', $sortfield= '', $limit=0, $offset=0, array $filter=array(), $filtermode= 'AND')
Load object in memory from the database.
length_accountg($account)
Return General accounting account with defined length (used for product and miscellaneous) ...
updateByMvt($piece_num='', $field='', $value='', $mode='')
Update movement.
createStd(User $user, $notrigger=false, $mode='')
Create object into database.
Class BookKeepingLine.
price2num($amount, $rounding='', $alreadysqlnb=0)
Function that return a number with universal decimal format (decimal separator is '...
dol_strlen($string, $stringencoding='UTF-8')
Make a strlen call.
Parent class of all other business classes (invoices, contracts, proposals, orders, ...)