dolibarr  7.0.0-beta
expensereport.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2011 Dimitri Mouillard <dmouillard@teclib.com>
3  * Copyright (C) 2015 Laurent Destailleur <eldy@users.sourceforge.net>
4  * Copyright (C) 2015 Alexandre Spangaro <aspangaro@zendsi.com>
5  * Copyright (C) 2016 Ferran Marcet <fmarcet@2byte.es>
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 <http://www.gnu.org/licenses/>.
19  */
20 
26 require_once DOL_DOCUMENT_ROOT .'/core/class/commonobject.class.php';
27 require_once DOL_DOCUMENT_ROOT .'/expensereport/class/expensereport_ik.class.php';
28 require_once DOL_DOCUMENT_ROOT .'/expensereport/class/expensereport_rule.class.php';
29 
34 {
35  var $element='expensereport';
36  var $table_element='expensereport';
37  var $table_element_line = 'expensereport_det';
38  var $fk_element = 'fk_expensereport';
39  var $picto = 'trip';
40 
41  var $lines=array();
42 
43  public $date_debut;
44 
45  public $date_fin;
46 
47  var $fk_user_validator;
48  var $status;
49  var $fk_statut; // -- 0=draft, 2=validated (attente approb), 4=canceled, 5=approved, 6=payed, 99=denied
50  var $fk_c_paiement;
51  var $paid;
52 
53  var $user_author_infos;
54  var $user_validator_infos;
55 
56  var $fk_typepayment;
57  var $num_payment;
58  var $code_paiement;
59  var $code_statut;
60 
61  // ACTIONS
62 
63  // Create
64  var $date_create;
65  var $fk_user_author; // Note fk_user_author is not the 'author' but the guy the expense report is for.
66 
67  // Update
68  var $date_modif;
69  var $fk_user_modif;
70 
71  // Refus
72  var $date_refuse;
73  var $detail_refuse;
74  var $fk_user_refuse;
75 
76  // Annulation
77  var $date_cancel;
78  var $detail_cancel;
79  var $fk_user_cancel;
80 
81  // Validation
82  var $date_valid;
83  var $fk_user_valid;
84  var $user_valid_infos;
85 
86  // Approve
87  var $date_approve;
88  var $fk_user_approve;
89 
90  // Paiement
91  var $user_paid_infos;
92 
93  /*
94  END ACTIONS
95  */
96 
100  const STATUS_DRAFT = 0;
101 
105  const STATUS_VALIDATED = 2;
106 
110  const STATUS_APPROVED = 5;
111 
115  const STATUS_REFUSED = 99;
116 
120  const STATUS_CLOSED = 6;
121 
122 
123 
129  function __construct($db)
130  {
131  $this->db = $db;
132  $this->total_ht = 0;
133  $this->total_ttc = 0;
134  $this->total_tva = 0;
135  $this->modepaymentid = 0;
136 
137  // List of language codes for status
138  $this->statuts_short = array(0 => 'Draft', 2 => 'Validated', 4 => 'Canceled', 5 => 'Approved', 6 => 'Paid', 99 => 'Refused');
139  $this->statuts = array(0 => 'Draft', 2 => 'ValidatedWaitingApproval', 4 => 'Canceled', 5 => 'Approved', 6 => 'Paid', 99 => 'Refused');
140  $this->statuts_logo = array(0 => 'statut0', 2 => 'statut1', 4 => 'statut5', 5 => 'statut3', 6 => 'statut6', 99 => 'statut8');
141 
142  return 1;
143  }
144 
152  function create($user, $notrigger=0)
153  {
154  global $conf;
155 
156  $now = dol_now();
157 
158  $error = 0;
159 
160  // Check parameters
161  if (empty($this->date_debut) || empty($this->date_fin))
162  {
163  $this->error='ErrorFieldRequired';
164  return -1;
165  }
166 
167  $fuserid = $this->fk_user_author; // Note fk_user_author is not the 'author' but the guy the expense report is for.
168  if (empty($fuserid)) $fuserid = $user->id;
169 
170  $this->db->begin();
171 
172  $sql = "INSERT INTO ".MAIN_DB_PREFIX.$this->table_element." (";
173  $sql.= "ref";
174  $sql.= ",total_ht";
175  $sql.= ",total_ttc";
176  $sql.= ",total_tva";
177  $sql.= ",date_debut";
178  $sql.= ",date_fin";
179  $sql.= ",date_create";
180  $sql.= ",fk_user_author";
181  $sql.= ",fk_user_validator";
182  $sql.= ",fk_user_modif";
183  $sql.= ",fk_statut";
184  $sql.= ",fk_c_paiement";
185  $sql.= ",paid";
186  $sql.= ",note_public";
187  $sql.= ",note_private";
188  $sql.= ",entity";
189  $sql.= ") VALUES(";
190  $sql.= "'(PROV)'";
191  $sql.= ", ".$this->total_ht;
192  $sql.= ", ".$this->total_ttc;
193  $sql.= ", ".$this->total_tva;
194  $sql.= ", '".$this->db->idate($this->date_debut)."'";
195  $sql.= ", '".$this->db->idate($this->date_fin)."'";
196  $sql.= ", '".$this->db->idate($now)."'";
197  $sql.= ", ".$fuserid;
198  $sql.= ", ".($this->fk_user_validator > 0 ? $this->fk_user_validator:"null");
199  $sql.= ", ".($this->fk_user_modif > 0 ? $this->fk_user_modif:"null");
200  $sql.= ", ".($this->fk_statut > 1 ? $this->fk_statut:0);
201  $sql.= ", ".($this->modepaymentid?$this->modepaymentid:"null");
202  $sql.= ", 0";
203  $sql.= ", ".($this->note_public?"'".$this->db->escape($this->note_public)."'":"null");
204  $sql.= ", ".($this->note_private?"'".$this->db->escape($this->note_private)."'":"null");
205  $sql.= ", ".$conf->entity;
206  $sql.= ")";
207 
208  $result = $this->db->query($sql);
209  if ($result)
210  {
211  $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX.$this->table_element);
212  $this->ref='(PROV'.$this->id.')';
213 
214  $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element." SET ref='".$this->db->escape($this->ref)."' WHERE rowid=".$this->id;
215  $resql=$this->db->query($sql);
216  if (!$resql) $error++;
217 
218  if (is_array($this->lines) && count($this->lines)>0)
219  {
220  foreach ($this->lines as $i => $val)
221  {
222  $newndfline=new ExpenseReportLine($this->db);
223  $newndfline=$this->lines[$i];
224  $newndfline->fk_expensereport=$this->id;
225  if ($result >= 0)
226  {
227  $result=$newndfline->insert();
228  }
229  if ($result < 0)
230  {
231  $error++;
232  break;
233  }
234  }
235  }
236 
237  if (! $error)
238  {
239  $result=$this->insertExtraFields();
240  if ($result < 0) $error++;
241  }
242 
243  if (! $error)
244  {
245  $result=$this->update_price();
246  if ($result > 0)
247  {
248 
249  if (!$notrigger)
250  {
251  // Call trigger
252  $result=$this->call_trigger('EXPENSE_REPORT_CREATE',$user);
253 
254  if ($result < 0) {
255  $error++;
256  }
257  // End call triggers
258  }
259 
260  if (empty($error))
261  {
262  $this->db->commit();
263  return $this->id;
264  }
265  else
266  {
267  $this->db->rollback();
268  return -4;
269  }
270  }
271  else
272  {
273  $this->db->rollback();
274  return -3;
275  }
276  }
277  else
278  {
279  dol_syslog(get_class($this)."::create error ".$this->error, LOG_ERR);
280  $this->db->rollback();
281  return -2;
282  }
283  }
284  else
285  {
286  $this->error=$this->db->lasterror()." sql=".$sql;
287  $this->db->rollback();
288  return -1;
289  }
290  }
291 
292 
299  function createFromClone($fk_user_author)
300  {
301  global $user,$hookmanager;
302 
303  $error=0;
304 
305  if (empty($fk_user_author)) $fk_user_author = $user->id;
306 
307  $this->context['createfromclone'] = 'createfromclone';
308 
309  $this->db->begin();
310 
311  // get extrafields so they will be clone
312  //foreach($this->lines as $line)
313  //$line->fetch_optionals($line->rowid);
314 
315  // Load source object
316  $objFrom = clone $this;
317 
318  $this->id=0;
319  $this->ref = '';
320  $this->status=0;
321  $this->fk_statut=0;
322 
323  // Clear fields
324  $this->fk_user_author = $fk_user_author; // Note fk_user_author is not the 'author' but the guy the expense report is for.
325  $this->fk_user_valid = '';
326  $this->date_create = '';
327  $this->date_creation = '';
328  $this->date_validation = '';
329 
330  // Create clone
331  $result=$this->create($user);
332  if ($result < 0) $error++;
333 
334  if (! $error)
335  {
336  // Hook of thirdparty module
337  if (is_object($hookmanager))
338  {
339  $parameters=array('objFrom'=>$objFrom);
340  $action='';
341  $reshook=$hookmanager->executeHooks('createFrom',$parameters,$this,$action); // Note that $action and $object may have been modified by some hooks
342  if ($reshook < 0) $error++;
343  }
344 
345  // Call trigger
346  $result=$this->call_trigger('EXPENSEREPORT_CLONE',$user);
347  if ($result < 0) $error++;
348  // End call triggers
349  }
350 
351  unset($this->context['createfromclone']);
352 
353  // End
354  if (! $error)
355  {
356  $this->db->commit();
357  return $this->id;
358  }
359  else
360  {
361  $this->db->rollback();
362  return -1;
363  }
364  }
365 
366 
375  function update($user, $notrigger = 0, $userofexpensereport=null)
376  {
377  global $langs;
378 
379  $error = 0;
380  $this->db->begin();
381 
382  $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element." SET";
383  $sql.= " total_ht = ".$this->total_ht;
384  $sql.= " , total_ttc = ".$this->total_ttc;
385  $sql.= " , total_tva = ".$this->total_tva;
386  $sql.= " , date_debut = '".$this->db->idate($this->date_debut)."'";
387  $sql.= " , date_fin = '".$this->db->idate($this->date_fin)."'";
388  if ($userofexpensereport && is_object($userofexpensereport))
389  {
390  $sql.= " , fk_user_author = ".($userofexpensereport->id > 0 ? "'".$userofexpensereport->id."'":"null"); // Note fk_user_author is not the 'author' but the guy the expense report is for.
391  }
392  $sql.= " , fk_user_validator = ".($this->fk_user_validator > 0 ? $this->fk_user_validator:"null");
393  $sql.= " , fk_user_valid = ".($this->fk_user_valid > 0 ? $this->fk_user_valid:"null");
394  $sql.= " , fk_user_modif = ".$user->id;
395  $sql.= " , fk_statut = ".($this->fk_statut >= 0 ? $this->fk_statut:'0');
396  $sql.= " , fk_c_paiement = ".($this->fk_c_paiement > 0 ? $this->fk_c_paiement:"null");
397  $sql.= " , note_public = ".(!empty($this->note_public)?"'".$this->db->escape($this->note_public)."'":"''");
398  $sql.= " , note_private = ".(!empty($this->note_private)?"'".$this->db->escape($this->note_private)."'":"''");
399  $sql.= " , detail_refuse = ".(!empty($this->detail_refuse)?"'".$this->db->escape($this->detail_refuse)."'":"''");
400  $sql.= " WHERE rowid = ".$this->id;
401 
402  dol_syslog(get_class($this)."::update sql=".$sql, LOG_DEBUG);
403  $result = $this->db->query($sql);
404  if ($result)
405  {
406  if (!$notrigger)
407  {
408  // Call trigger
409  $result=$this->call_trigger('EXPENSE_REPORT_UPDATE',$user);
410 
411  if ($result < 0) {
412  $error++;
413  }
414  // End call triggers
415  }
416 
417  if (empty($error))
418  {
419  $this->db->commit();
420  return 1;
421  }
422  else
423  {
424  $this->db->rollback();
425  $this->error=$this->db->error();
426  return -2;
427  }
428  }
429  else
430  {
431  $this->db->rollback();
432  $this->error=$this->db->error();
433  return -1;
434  }
435  }
436 
444  function fetch($id, $ref='')
445  {
446  global $conf;
447 
448  $sql = "SELECT d.rowid, d.ref, d.note_public, d.note_private,"; // DEFAULT
449  $sql.= " d.detail_refuse, d.detail_cancel, d.fk_user_refuse, d.fk_user_cancel,"; // ACTIONS
450  $sql.= " d.date_refuse, d.date_cancel,"; // ACTIONS
451  $sql.= " d.total_ht, d.total_ttc, d.total_tva,"; // TOTAUX (int)
452  $sql.= " d.date_debut, d.date_fin, d.date_create, d.tms as date_modif, d.date_valid, d.date_approve,"; // DATES (datetime)
453  $sql.= " d.fk_user_author, d.fk_user_modif, d.fk_user_validator,";
454  $sql.= " d.fk_user_valid, d.fk_user_approve,";
455  $sql.= " d.fk_statut as status, d.fk_c_paiement,";
456  $sql.= " dp.libelle as libelle_paiement, dp.code as code_paiement"; // INNER JOIN paiement
457  $sql.= " FROM ".MAIN_DB_PREFIX.$this->table_element." as d";
458  $sql.= " LEFT JOIN ".MAIN_DB_PREFIX."c_paiement as dp ON d.fk_c_paiement = dp.id AND dp.entity IN (".getEntity('c_paiement').")";
459  if ($ref) $sql.= " WHERE d.ref = '".$this->db->escape($ref)."'";
460  else $sql.= " WHERE d.rowid = ".$id;
461  //$sql.= $restrict;
462 
463  dol_syslog(get_class($this)."::fetch sql=".$sql, LOG_DEBUG);
464  $resql = $this->db->query($sql) ;
465  if ($resql)
466  {
467  $obj = $this->db->fetch_object($resql);
468  if ($obj)
469  {
470  $this->id = $obj->rowid;
471  $this->ref = $obj->ref;
472  $this->total_ht = $obj->total_ht;
473  $this->total_tva = $obj->total_tva;
474  $this->total_ttc = $obj->total_ttc;
475  $this->note_public = $obj->note_public;
476  $this->note_private = $obj->note_private;
477  $this->detail_refuse = $obj->detail_refuse;
478  $this->detail_cancel = $obj->detail_cancel;
479 
480  $this->date_debut = $this->db->jdate($obj->date_debut);
481  $this->date_fin = $this->db->jdate($obj->date_fin);
482  $this->date_valid = $this->db->jdate($obj->date_valid);
483  $this->date_approve = $this->db->jdate($obj->date_approve);
484  $this->date_create = $this->db->jdate($obj->date_create);
485  $this->date_modif = $this->db->jdate($obj->date_modif);
486  $this->date_refuse = $this->db->jdate($obj->date_refuse);
487  $this->date_cancel = $this->db->jdate($obj->date_cancel);
488 
489  $this->fk_user_author = $obj->fk_user_author; // Note fk_user_author is not the 'author' but the guy the expense report is for.
490  $this->fk_user_modif = $obj->fk_user_modif;
491  $this->fk_user_validator = $obj->fk_user_validator;
492  $this->fk_user_valid = $obj->fk_user_valid;
493  $this->fk_user_refuse = $obj->fk_user_refuse;
494  $this->fk_user_cancel = $obj->fk_user_cancel;
495  $this->fk_user_approve = $obj->fk_user_approve;
496 
497  $user_author = new User($this->db);
498  if ($this->fk_user_author > 0) $user_author->fetch($this->fk_user_author);
499 
500  $this->user_author_infos = dolGetFirstLastname($user_author->firstname, $user_author->lastname);
501 
502  $user_approver = new User($this->db);
503  if ($this->fk_user_validator > 0) $user_approver->fetch($this->fk_user_validator);
504  $this->user_validator_infos = dolGetFirstLastname($user_approver->firstname, $user_approver->lastname);
505 
506  $this->fk_statut = $obj->status;
507  $this->status = $obj->status;
508  $this->fk_c_paiement = $obj->fk_c_paiement;
509  $this->paid = $obj->paid;
510 
511  if ($this->fk_statut==5 || $this->fk_statut==6)
512  {
513  $user_valid = new User($this->db);
514  if ($this->fk_user_valid > 0) $user_valid->fetch($this->fk_user_valid);
515  $this->user_valid_infos = dolGetFirstLastname($user_valid->firstname, $user_valid->lastname);
516  }
517 
518  $this->libelle_statut = $obj->libelle_statut;
519  $this->libelle_paiement = $obj->libelle_paiement;
520  $this->code_statut = $obj->code_statut;
521  $this->code_paiement = $obj->code_paiement;
522 
523  $this->lines = array();
524 
525  $result=$this->fetch_lines();
526 
527  return $result;
528  }
529  else
530  {
531  return 0;
532  }
533  }
534  else
535  {
536  $this->error=$this->db->lasterror();
537  return -1;
538  }
539  }
540 
549  function set_paid($id, $fuser, $notrigger = 0)
550  {
551  $error = 0;
552  $this->db->begin();
553 
554  $sql = "UPDATE ".MAIN_DB_PREFIX."expensereport";
555  $sql.= " SET fk_statut = 6, paid=1";
556  $sql.= " WHERE rowid = ".$id." AND fk_statut = 5";
557 
558  dol_syslog(get_class($this)."::set_paid sql=".$sql, LOG_DEBUG);
559  $resql=$this->db->query($sql);
560  if ($resql)
561  {
562  if ($this->db->affected_rows($resql))
563  {
564  if (!$notrigger)
565  {
566  // Call trigger
567  $result=$this->call_trigger('EXPENSE_REPORT_PAID',$fuser);
568 
569  if ($result < 0) {
570  $error++;
571  }
572  // End call triggers
573  }
574 
575  if (empty($error))
576  {
577  $this->db->commit();
578  return 1;
579  }
580  else
581  {
582  $this->db->rollback();
583  $this->error=$this->db->error();
584  return -2;
585  }
586  }
587  else
588  {
589  $this->db->commit();
590  return 0;
591  }
592  }
593  else
594  {
595  $this->db->rollback();
596  dol_print_error($this->db);
597  return -1;
598  }
599  }
600 
607  function getLibStatut($mode=0)
608  {
609  return $this->LibStatut($this->status,$mode);
610  }
611 
619  function LibStatut($status,$mode=0)
620  {
621  global $langs;
622 
623  if ($mode == 0)
624  return $langs->transnoentities($this->statuts[$status]);
625 
626  if ($mode == 1)
627  return $langs->transnoentities($this->statuts_short[$status]);
628 
629  if ($mode == 2)
630  return img_picto($langs->transnoentities($this->statuts_short[$status]), $this->statuts_logo[$status]).' '.$langs->transnoentities($this->statuts_short[$status]);
631 
632  if ($mode == 3)
633  return img_picto($langs->transnoentities($this->statuts_short[$status]), $this->statuts_logo[$status]);
634 
635  if ($mode == 4)
636  return img_picto($langs->transnoentities($this->statuts_short[$status]),$this->statuts_logo[$status]).' '.$langs->transnoentities($this->statuts[$status]);
637 
638  if ($mode == 5)
639  return '<span class="hideonsmartphone">'.$langs->transnoentities($this->statuts_short[$status]).' </span>'.img_picto($langs->transnoentities($this->statuts_short[$status]),$this->statuts_logo[$status]);
640 
641  if ($mode == 6)
642  return $langs->transnoentities($this->statuts[$status]).' '.img_picto($langs->transnoentities($this->statuts_short[$status]),$this->statuts_logo[$status]);
643  }
644 
645 
652  function info($id)
653  {
654  global $conf;
655 
656  $sql = "SELECT f.rowid,";
657  $sql.= " f.date_create as datec,";
658  $sql.= " f.tms as date_modification,";
659  $sql.= " f.date_valid as datev,";
660  $sql.= " f.date_approve as datea,";
661  //$sql.= " f.fk_user_author as fk_user_creation,"; // This is not user of creation but user the expense is for.
662  $sql.= " f.fk_user_modif as fk_user_modification,";
663  $sql.= " f.fk_user_valid,";
664  $sql.= " f.fk_user_approve";
665  $sql.= " FROM ".MAIN_DB_PREFIX."expensereport as f";
666  $sql.= " WHERE f.rowid = ".$id;
667  $sql.= " AND f.entity = ".$conf->entity;
668 
669  $resql = $this->db->query($sql);
670  if ($resql)
671  {
672  if ($this->db->num_rows($resql))
673  {
674  $obj = $this->db->fetch_object($resql);
675 
676  $this->id = $obj->rowid;
677 
678  $this->date_creation = $this->db->jdate($obj->datec);
679  $this->date_modification = $this->db->jdate($obj->date_modification);
680  $this->date_validation = $this->db->jdate($obj->datev);
681  $this->date_approbation = $this->db->jdate($obj->datea);
682 
683  $cuser = new User($this->db);
684  $cuser->fetch($obj->fk_user_author);
685  $this->user_creation = $cuser;
686 
687  if ($obj->fk_user_creation)
688  {
689  $cuser = new User($this->db);
690  $cuser->fetch($obj->fk_user_creation);
691  $this->user_creation = $cuser;
692  }
693  if ($obj->fk_user_valid)
694  {
695  $vuser = new User($this->db);
696  $vuser->fetch($obj->fk_user_valid);
697  $this->user_validation = $vuser;
698  }
699  if ($obj->fk_user_modification)
700  {
701  $muser = new User($this->db);
702  $muser->fetch($obj->fk_user_modification);
703  $this->user_modification = $muser;
704  }
705  if ($obj->fk_user_approve)
706  {
707  $auser = new User($this->db);
708  $auser->fetch($obj->fk_user_approve);
709  $this->user_approve = $auser;
710  }
711 
712  }
713  $this->db->free($resql);
714  }
715  else
716  {
717  dol_print_error($this->db);
718  }
719  }
720 
721 
722 
730  function initAsSpecimen()
731  {
732  global $user,$langs,$conf;
733 
734  $now=dol_now();
735 
736  // Initialise parametres
737  $this->id=0;
738  $this->ref = 'SPECIMEN';
739  $this->specimen=1;
740  $this->date_create = $now;
741  $this->date_debut = $now;
742  $this->date_fin = $now;
743  $this->date_valid = $now;
744  $this->date_approve = $now;
745 
746  $type_fees_id = 2; // TF_TRIP
747 
748  $this->status = 5;
749  $this->fk_statut = 5;
750 
751  $this->fk_user_author = $user->id;
752  $this->fk_user_valid = $user->id;
753  $this->fk_user_approve = $user->id;
754  $this->fk_user_validator = $user->id;
755 
756  $this->note_private='Private note';
757  $this->note_public='SPECIMEN';
758  $nbp = 5;
759  $xnbp = 0;
760  while ($xnbp < $nbp)
761  {
762  $line=new ExpenseReportLine($this->db);
763  $line->comments=$langs->trans("Comment")." ".$xnbp;
764  $line->date=($now-3600*(1+$xnbp));
765  $line->total_ht=100;
766  $line->total_tva=20;
767  $line->total_ttc=120;
768  $line->qty=1;
769  $line->vatrate=20;
770  $line->value_unit=120;
771  $line->fk_expensereport=0;
772  $line->type_fees_code='TRA';
773  $line->fk_c_type_fees=$type_fees_id;
774 
775  $line->projet_ref = 'ABC';
776 
777  $this->lines[$xnbp]=$line;
778  $xnbp++;
779 
780  $this->total_ht+=$line->total_ht;
781  $this->total_tva+=$line->total_tva;
782  $this->total_ttc+=$line->total_ttc;
783  }
784  }
785 
793  function fetch_line_by_project($projectid,$user='')
794  {
795  global $conf,$db,$langs;
796 
797  $langs->load('trips');
798 
799  if ($user->rights->expensereport->lire) {
800 
801  $sql = "SELECT de.fk_expensereport, de.date, de.comments, de.total_ht, de.total_ttc";
802  $sql.= " FROM ".MAIN_DB_PREFIX."expensereport_det as de";
803  $sql.= " WHERE de.fk_projet = ".$projectid;
804 
805  dol_syslog(get_class($this)."::fetch sql=".$sql, LOG_DEBUG);
806  $result = $db->query($sql) ;
807  if ($result)
808  {
809  $num = $db->num_rows($result);
810  $i = 0;
811  $total_HT = 0;
812  $total_TTC = 0;
813 
814  while ($i < $num)
815  {
816 
817  $objp = $db->fetch_object($result);
818 
819  $sql2 = "SELECT d.rowid, d.fk_user_author, d.ref, d.fk_statut";
820  $sql2.= " FROM ".MAIN_DB_PREFIX."expensereport as d";
821  $sql2.= " WHERE d.rowid = '".$objp->fk_expensereport."'";
822 
823  $result2 = $db->query($sql2);
824  $obj = $db->fetch_object($result2);
825 
826  $objp->fk_user_author = $obj->fk_user_author;
827  $objp->ref = $obj->ref;
828  $objp->fk_c_expensereport_status = $obj->fk_statut;
829  $objp->rowid = $obj->rowid;
830 
831  $total_HT = $total_HT + $objp->total_ht;
832  $total_TTC = $total_TTC + $objp->total_ttc;
833  $author = new User($db);
834  $author->fetch($objp->fk_user_author);
835 
836  print '<tr>';
837  print '<td><a href="'.DOL_URL_ROOT.'/expensereport/card.php?id='.$objp->rowid.'">'.$objp->ref_num.'</a></td>';
838  print '<td align="center">'.dol_print_date($objp->date,'day').'</td>';
839  print '<td>'.$author->getNomUrl(1).'</td>';
840  print '<td>'.$objp->comments.'</td>';
841  print '<td align="right">'.price($objp->total_ht).'</td>';
842  print '<td align="right">'.price($objp->total_ttc).'</td>';
843  print '<td align="right">';
844 
845  switch($objp->fk_c_expensereport_status) {
846  case 4:
847  print img_picto($langs->trans('StatusOrderCanceled'),'statut5');
848  break;
849  case 1:
850  print $langs->trans('Draft').' '.img_picto($langs->trans('Draft'),'statut0');
851  break;
852  case 2:
853  print $langs->trans('TripForValid').' '.img_picto($langs->trans('TripForValid'),'statut3');
854  break;
855  case 5:
856  print $langs->trans('TripForPaid').' '.img_picto($langs->trans('TripForPaid'),'statut3');
857  break;
858  case 6:
859  print $langs->trans('TripPaid').' '.img_picto($langs->trans('TripPaid'),'statut4');
860  break;
861  }
862  /*
863  if ($status==4) return img_picto($langs->trans('StatusOrderCanceled'),'statut5');
864  if ($status==1) return img_picto($langs->trans('StatusOrderDraft'),'statut0');
865  if ($status==2) return img_picto($langs->trans('StatusOrderValidated'),'statut1');
866  if ($status==2) return img_picto($langs->trans('StatusOrderOnProcess'),'statut3');
867  if ($status==5) return img_picto($langs->trans('StatusOrderToBill'),'statut4');
868  if ($status==6) return img_picto($langs->trans('StatusOrderOnProcess'),'statut6');
869  */
870  print '</td>';
871  print '</tr>';
872 
873  $i++;
874  }
875 
876  print '<tr class="liste_total"><td colspan="4">'.$langs->trans("Number").': '.$i.'</td>';
877  print '<td align="right" width="100">'.$langs->trans("TotalHT").' : '.price($total_HT).'</td>';
878  print '<td align="right" width="100">'.$langs->trans("TotalTTC").' : '.price($total_TTC).'</td>';
879  print '<td>&nbsp;</td>';
880  print '</tr>';
881 
882  }
883  else
884  {
885  $this->error=$db->lasterror();
886  return -1;
887  }
888  }
889 
890  }
891 
899  function recalculer($id)
900  {
901  $sql = 'SELECT tt.total_ht, tt.total_ttc, tt.total_tva';
902  $sql.= ' FROM '.MAIN_DB_PREFIX.$this->table_element_line.' as tt';
903  $sql.= ' WHERE tt.'.$this->fk_element.' = '.$id;
904 
905  $total_ht = 0; $total_tva = 0; $total_ttc = 0;
906 
907  $result = $this->db->query($sql);
908  if($result)
909  {
910  $num = $this->db->num_rows($result);
911  $i = 0;
912  while ($i < $num):
913  $objp = $this->db->fetch_object($result);
914  $total_ht+=$objp->total_ht;
915  $total_tva+=$objp->total_tva;
916  $i++;
917  endwhile;
918 
919  $total_ttc = $total_ht + $total_tva;
920  $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element." SET";
921  $sql.= " total_ht = ".$total_ht;
922  $sql.= " , total_ttc = ".$total_ttc;
923  $sql.= " , total_tva = ".$total_tva;
924  $sql.= " WHERE rowid = ".$id;
925  $result = $this->db->query($sql);
926  if($result):
927  $this->db->free($result);
928  return 1;
929  else:
930  $this->error=$this->db->lasterror();
931  dol_syslog(get_class($this)."::recalculer: Error ".$this->error,LOG_ERR);
932  return -3;
933  endif;
934  }
935  else
936  {
937  $this->error=$this->db->lasterror();
938  dol_syslog(get_class($this)."::recalculer: Error ".$this->error,LOG_ERR);
939  return -3;
940  }
941  }
942 
948  function fetch_lines()
949  {
950  $this->lines=array();
951 
952  $sql = ' SELECT de.rowid, de.comments, de.qty, de.value_unit, de.date, de.rang,';
953  $sql.= ' de.'.$this->fk_element.', de.fk_c_type_fees, de.fk_c_exp_tax_cat, de.fk_projet, de.tva_tx,';
954  $sql.= ' de.total_ht, de.total_tva, de.total_ttc,';
955  $sql.= ' ctf.code as code_type_fees, ctf.label as libelle_type_fees,';
956  $sql.= ' p.ref as ref_projet, p.title as title_projet';
957  $sql.= ' FROM '.MAIN_DB_PREFIX.$this->table_element_line.' as de';
958  $sql.= ' LEFT JOIN '.MAIN_DB_PREFIX.'c_type_fees as ctf ON de.fk_c_type_fees = ctf.id';
959  $sql.= ' LEFT JOIN '.MAIN_DB_PREFIX.'projet as p ON de.fk_projet = p.rowid';
960  $sql.= ' WHERE de.'.$this->fk_element.' = '.$this->id;
961  if (! empty($conf->global->EXPENSEREPORT_LINES_SORTED_BY_ROWID))
962  {
963  $sql.= ' ORDER BY de.rang ASC, de.rowid ASC';
964  }
965  else
966  {
967  $sql.= ' ORDER BY de.rang ASC, de.date ASC';
968  }
969 
970  $resql = $this->db->query($sql);
971  if ($resql)
972  {
973  $num = $this->db->num_rows($resql);
974  $i = 0;
975  while ($i < $num)
976  {
977  $objp = $this->db->fetch_object($resql);
978 
979  $deplig = new ExpenseReportLine($this->db);
980 
981  $deplig->rowid = $objp->rowid;
982  $deplig->id = $objp->id;
983  $deplig->comments = $objp->comments;
984  $deplig->qty = $objp->qty;
985  $deplig->value_unit = $objp->value_unit;
986  $deplig->date = $objp->date;
987  $deplig->dates = $this->db->jdate($objp->date);
988 
989  $deplig->fk_expensereport = $objp->fk_expensereport;
990  $deplig->fk_c_type_fees = $objp->fk_c_type_fees;
991  $deplig->fk_c_exp_tax_cat = $objp->fk_c_exp_tax_cat;
992  $deplig->fk_projet = $objp->fk_projet;
993 
994  $deplig->total_ht = $objp->total_ht;
995  $deplig->total_tva = $objp->total_tva;
996  $deplig->total_ttc = $objp->total_ttc;
997 
998  $deplig->type_fees_code = empty($objp->code_type_fees)?'TF_OTHER':$objp->code_type_fees;
999  $deplig->type_fees_libelle = $objp->libelle_type_fees;
1000  $deplig->tva_tx = $objp->tva_tx;
1001  $deplig->vatrate = $objp->tva_tx;
1002  $deplig->projet_ref = $objp->ref_projet;
1003  $deplig->projet_title = $objp->title_projet;
1004 
1005  $deplig->rang = $objp->rang;
1006 
1007  $this->lines[$i] = $deplig;
1008 
1009  $i++;
1010  }
1011  $this->db->free($resql);
1012  return 1;
1013  }
1014  else
1015  {
1016  $this->error=$this->db->lasterror();
1017  dol_syslog(get_class($this)."::fetch_lines: Error ".$this->error, LOG_ERR);
1018  return -3;
1019  }
1020  }
1021 
1022 
1029  function delete(User $fuser=null)
1030  {
1031  global $user,$langs,$conf;
1032 
1033  if (! $rowid) $rowid=$this->id;
1034 
1035  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element_line.' WHERE '.$this->fk_element.' = '.$rowid;
1036  if ($this->db->query($sql))
1037  {
1038  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element.' WHERE rowid = '.$rowid;
1039  $resql=$this->db->query($sql);
1040  if ($resql)
1041  {
1042  $this->db->commit();
1043  return 1;
1044  }
1045  else
1046  {
1047  $this->error=$this->db->error()." sql=".$sql;
1048  dol_syslog(get_class($this)."::delete ".$this->error, LOG_ERR);
1049  $this->db->rollback();
1050  return -6;
1051  }
1052  }
1053  else
1054  {
1055  $this->error=$this->db->error()." sql=".$sql;
1056  dol_syslog(get_class($this)."::delete ".$this->error, LOG_ERR);
1057  $this->db->rollback();
1058  return -4;
1059  }
1060  }
1061 
1069  function setValidate($fuser, $notrigger=0)
1070  {
1071  global $conf,$langs,$user;
1072 
1073  $error = 0;
1074  $now = dol_now();
1075 
1076  // Protection
1077  if ($this->statut == self::STATUS_VALIDATED)
1078  {
1079  dol_syslog(get_class($this)."::valid action abandonned: already validated", LOG_WARNING);
1080  return 0;
1081  }
1082 
1083  $this->date_valid = $now; // Required for the getNextNum later.
1084 
1085  // Define new ref
1086  if (! $error && (preg_match('/^[\(]?PROV/i', $this->ref) || empty($this->ref))) // empty should not happened, but when it occurs, the test save life
1087  {
1088  $num = $this->getNextNumRef();
1089  }
1090  else
1091  {
1092  $num = $this->ref;
1093  }
1094  if (empty($num)) return -1;
1095 
1096  $this->newref = $num;
1097 
1098  $this->db->begin();
1099 
1100  // Validate
1101  $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element;
1102  $sql.= " SET ref = '".$num."',";
1103  $sql.= " fk_statut = ".self::STATUS_VALIDATED.",";
1104  $sql.= " date_valid='".$this->db->idate($this->date_valid)."',";
1105  $sql.= " fk_user_valid = ".$user->id;
1106  $sql.= " WHERE rowid = ".$this->id;
1107 
1108  $resql=$this->db->query($sql);
1109  if ($resql)
1110  {
1111  if (!$notrigger)
1112  {
1113  // Call trigger
1114  $result=$this->call_trigger('EXPENSE_REPORT_VALIDATE',$fuser);
1115 
1116  if ($result < 0) {
1117  $error++;
1118  }
1119  // End call triggers
1120  }
1121 
1122  if (! $error)
1123  {
1124  $this->oldref = $this->ref;
1125 
1126  // Rename directory if dir was a temporary ref
1127  if (preg_match('/^[\(]?PROV/i', $this->ref))
1128  {
1129  require_once DOL_DOCUMENT_ROOT.'/core/lib/files.lib.php';
1130 
1131  // On renomme repertoire ($this->ref = ancienne ref, $num = nouvelle ref)
1132  // in order not to lose the attachments
1133  $oldref = dol_sanitizeFileName($this->ref);
1134  $newref = dol_sanitizeFileName($num);
1135  $dirsource = $conf->expensereport->dir_output.'/'.$oldref;
1136  $dirdest = $conf->expensereport->dir_output.'/'.$newref;
1137  if (file_exists($dirsource))
1138  {
1139  dol_syslog(get_class($this)."::valid() rename dir ".$dirsource." into ".$dirdest);
1140 
1141  if (@rename($dirsource, $dirdest))
1142  {
1143  dol_syslog("Rename ok");
1144  // Rename docs starting with $oldref with $newref
1145  $listoffiles=dol_dir_list($conf->expensereport->dir_output.'/'.$newref, 'files', 1, '^'.preg_quote($oldref,'/'));
1146  foreach($listoffiles as $fileentry)
1147  {
1148  $dirsource=$fileentry['name'];
1149  $dirdest=preg_replace('/^'.preg_quote($oldref,'/').'/',$newref, $dirsource);
1150  $dirsource=$fileentry['path'].'/'.$dirsource;
1151  $dirdest=$fileentry['path'].'/'.$dirdest;
1152  @rename($dirsource, $dirdest);
1153  }
1154  }
1155  }
1156  }
1157  }
1158 
1159  // Set new ref and current status
1160  if (! $error)
1161  {
1162  $this->ref = $num;
1163  $this->statut = self::STATUS_VALIDATED;
1164  }
1165 
1166  if (empty($error))
1167  {
1168  $this->db->commit();
1169  return 1;
1170  }
1171  else
1172  {
1173  $this->db->rollback();
1174  $this->error=$this->db->error();
1175  return -2;
1176  }
1177  }
1178  else
1179  {
1180  $this->db->rollback();
1181  $this->error=$this->db->lasterror();
1182  return -1;
1183  }
1184  }
1185 
1192  function set_save_from_refuse($fuser)
1193  {
1194  global $conf,$langs;
1195 
1196  // Sélection de la date de début de la NDF
1197  $sql = 'SELECT date_debut';
1198  $sql.= ' FROM '.MAIN_DB_PREFIX.$this->table_element;
1199  $sql.= ' WHERE rowid = '.$this->id;
1200 
1201  $result = $this->db->query($sql);
1202 
1203  $objp = $this->db->fetch_object($result);
1204 
1205  $this->date_debut = $this->db->jdate($objp->date_debut);
1206 
1207  if ($this->fk_statut != 2)
1208  {
1209  $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element;
1210  $sql.= " SET fk_statut = 2";
1211  $sql.= ' WHERE rowid = '.$this->id;
1212 
1213  dol_syslog(get_class($this)."::set_save_from_refuse sql=".$sql, LOG_DEBUG);
1214 
1215  if ($this->db->query($sql))
1216  {
1217  return 1;
1218  }
1219  else
1220  {
1221  $this->error=$this->db->lasterror();
1222  return -1;
1223  }
1224  }
1225  else
1226  {
1227  dol_syslog(get_class($this)."::set_save_from_refuse expensereport already with save status", LOG_WARNING);
1228  }
1229  }
1230 
1238  function setApproved($fuser, $notrigger=0)
1239  {
1240  $now=dol_now();
1241  $error = 0;
1242 
1243  // date approval
1244  $this->date_approve = $this->db->idate($now);
1245  if ($this->fk_statut != 5)
1246  {
1247  $this->db->begin();
1248 
1249  $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element;
1250  $sql.= " SET ref = '".$this->db->escape($this->ref)."', fk_statut = 5, fk_user_approve = ".$fuser->id.",";
1251  $sql.= " date_approve='".$this->db->idate($this->date_approve)."'";
1252  $sql.= ' WHERE rowid = '.$this->id;
1253  if ($this->db->query($sql))
1254  {
1255  if (!$notrigger)
1256  {
1257  // Call trigger
1258  $result=$this->call_trigger('EXPENSE_REPORT_APPROVE',$fuser);
1259 
1260  if ($result < 0) {
1261  $error++;
1262  }
1263  // End call triggers
1264  }
1265 
1266  if (empty($error))
1267  {
1268  $this->db->commit();
1269  return 1;
1270  }
1271  else
1272  {
1273  $this->db->rollback();
1274  $this->error=$this->db->error();
1275  return -2;
1276  }
1277  }
1278  else
1279  {
1280  $this->db->rollback();
1281  $this->error=$this->db->lasterror();
1282  return -1;
1283  }
1284  }
1285  else
1286  {
1287  dol_syslog(get_class($this)."::setApproved expensereport already with approve status", LOG_WARNING);
1288  }
1289 
1290  return 0;
1291  }
1292 
1300  function setDeny($fuser,$details,$notrigger=0)
1301  {
1302  $now = dol_now();
1303  $error = 0;
1304 
1305  // date de refus
1306  if ($this->fk_statut != 99)
1307  {
1308  $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element;
1309  $sql.= " SET ref = '".$this->db->escape($this->ref)."', fk_statut = 99, fk_user_refuse = ".$fuser->id.",";
1310  $sql.= " date_refuse='".$this->db->idate($now)."',";
1311  $sql.= " detail_refuse='".$this->db->escape($details)."',";
1312  $sql.= " fk_user_approve = NULL";
1313  $sql.= ' WHERE rowid = '.$this->id;
1314  if ($this->db->query($sql))
1315  {
1316  $this->fk_statut = 99;
1317  $this->fk_user_refuse = $fuser->id;
1318  $this->detail_refuse = $details;
1319  $this->date_refuse = $now;
1320 
1321  if (!$notrigger)
1322  {
1323  // Call trigger
1324  $result=$this->call_trigger('EXPENSE_REPORT_DENY',$fuser);
1325 
1326  if ($result < 0) {
1327  $error++;
1328  }
1329  // End call triggers
1330  }
1331 
1332  if (empty($error))
1333  {
1334  $this->db->commit();
1335  return 1;
1336  }
1337  else
1338  {
1339  $this->db->rollback();
1340  $this->error=$this->db->error();
1341  return -2;
1342  }
1343  }
1344  else
1345  {
1346  $this->db->rollback();
1347  $this->error=$this->db->lasterror();
1348  return -1;
1349  }
1350  }
1351  else
1352  {
1353  dol_syslog(get_class($this)."::setDeny expensereport already with refuse status", LOG_WARNING);
1354  }
1355  }
1356 
1364  function set_unpaid($fuser, $notrigger = 0)
1365  {
1366  $error = 0;
1367 
1368  if ($this->fk_c_deplacement_statuts != 5)
1369  {
1370  $this->db->begin();
1371 
1372  $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element;
1373  $sql.= " SET fk_statut = 5";
1374  $sql.= ' WHERE rowid = '.$this->id;
1375 
1376  dol_syslog(get_class($this)."::set_unpaid sql=".$sql, LOG_DEBUG);
1377 
1378  if ($this->db->query($sql))
1379  {
1380  if (!$notrigger)
1381  {
1382  // Call trigger
1383  $result=$this->call_trigger('EXPENSE_REPORT_UNPAID',$fuser);
1384 
1385  if ($result < 0) {
1386  $error++;
1387  }
1388  // End call triggers
1389  }
1390 
1391  if (empty($error))
1392  {
1393  $this->db->commit();
1394  return 1;
1395  }
1396  else
1397  {
1398  $this->db->rollback();
1399  $this->error=$this->db->error();
1400  return -2;
1401  }
1402  }
1403  else
1404  {
1405  $this->db->rollback();
1406  $this->error=$this->db->error();
1407  return -1;
1408  }
1409  }
1410  else
1411  {
1412  dol_syslog(get_class($this)."::set_unpaid expensereport already with unpaid status", LOG_WARNING);
1413  }
1414  }
1415 
1424  function set_cancel($fuser,$detail, $notrigger=0)
1425  {
1426  $error = 0;
1427  $this->date_cancel = $this->db->idate(gmmktime());
1428  if ($this->fk_statut != 4)
1429  {
1430  $this->db->begin();
1431 
1432  $sql = 'UPDATE '.MAIN_DB_PREFIX.$this->table_element;
1433  $sql.= " SET fk_statut = 4, fk_user_cancel = ".$fuser->id;
1434  $sql.= ", date_cancel='".$this->db->idate($this->date_cancel)."'";
1435  $sql.= " ,detail_cancel='".$this->db->escape($detail)."'";
1436  $sql.= ' WHERE rowid = '.$this->id;
1437 
1438  dol_syslog(get_class($this)."::set_cancel sql=".$sql, LOG_DEBUG);
1439 
1440  if ($this->db->query($sql))
1441  {
1442  if (!$notrigger)
1443  {
1444  // Call trigger
1445  $result=$this->call_trigger('EXPENSE_REPORT_CANCEL',$fuser);
1446 
1447  if ($result < 0) {
1448  $error++;
1449  }
1450  // End call triggers
1451  }
1452 
1453  if (empty($error))
1454  {
1455  $this->db->commit();
1456  return 1;
1457  }
1458  else
1459  {
1460  $this->db->rollback();
1461  $this->error=$this->db->error();
1462  return -2;
1463  }
1464  }
1465  else
1466  {
1467  $this->db->rollback();
1468  $this->error=$this->db->error();
1469  return -1;
1470  }
1471  }
1472  else
1473  {
1474  dol_syslog(get_class($this)."::set_cancel expensereport already with cancel status", LOG_WARNING);
1475  }
1476  }
1477 
1483  function getNextNumRef()
1484  {
1485  global $langs, $conf;
1486  $langs->load("trips");
1487 
1488  if (! empty($conf->global->EXPENSEREPORT_ADDON))
1489  {
1490  $mybool=false;
1491 
1492  $file = $conf->global->EXPENSEREPORT_ADDON.".php";
1493  $classname = $conf->global->EXPENSEREPORT_ADDON;
1494 
1495  // Include file with class
1496  $dirmodels=array_merge(array('/'),(array) $conf->modules_parts['models']);
1497  foreach ($dirmodels as $reldir)
1498  {
1499  $dir = dol_buildpath($reldir."core/modules/expensereport/");
1500 
1501  // Load file with numbering class (if found)
1502  $mybool|=@include_once $dir.$file;
1503  }
1504 
1505  if (! $mybool)
1506  {
1507  dol_print_error('',"Failed to include file ".$file);
1508  return '';
1509  }
1510 
1511  $obj = new $classname();
1512  $numref = $obj->getNextValue($this);
1513 
1514  if ($numref != "")
1515  {
1516  return $numref;
1517  }
1518  else
1519  {
1520  $this->error=$obj->error;
1521  $this->errors=$obj->errors;
1522  //dol_print_error($this->db,get_class($this)."::getNextNumRef ".$obj->error);
1523  return -1;
1524  }
1525  }
1526  else
1527  {
1528  $this->error = "Error_EXPENSEREPORT_ADDON_NotDefined";
1529  return -2;
1530  }
1531  }
1532 
1544  function getNomUrl($withpicto=0, $max=0, $short=0, $moretitle='', $notooltip=0, $save_lastsearch_value=-1)
1545  {
1546  global $langs, $conf;
1547 
1548  $result='';
1549 
1550  $url = DOL_URL_ROOT.'/expensereport/card.php?id='.$this->id;
1551 
1552  if ($short) return $url;
1553 
1554  $label = '<u>' . $langs->trans("ShowExpenseReport") . '</u>';
1555  if (! empty($this->ref))
1556  $label .= '<br><b>' . $langs->trans('Ref') . ':</b> ' . $this->ref;
1557  if (! empty($this->total_ht))
1558  $label.= '<br><b>' . $langs->trans('AmountHT') . ':</b> ' . price($this->total_ht, 0, $langs, 0, -1, -1, $conf->currency);
1559  if (! empty($this->total_tva))
1560  $label.= '<br><b>' . $langs->trans('VAT') . ':</b> ' . price($this->total_tva, 0, $langs, 0, -1, -1, $conf->currency);
1561  if (! empty($this->total_ttc))
1562  $label.= '<br><b>' . $langs->trans('AmountTTC') . ':</b> ' . price($this->total_ttc, 0, $langs, 0, -1, -1, $conf->currency);
1563  if ($moretitle) $label.=' - '.$moretitle;
1564 
1565  //if ($option != 'nolink')
1566  //{
1567  // Add param to save lastsearch_values or not
1568  $add_save_lastsearch_values=($save_lastsearch_value == 1 ? 1 : 0);
1569  if ($save_lastsearch_value == -1 && preg_match('/list\.php/',$_SERVER["PHP_SELF"])) $add_save_lastsearch_values=1;
1570  if ($add_save_lastsearch_values) $url.='&save_lastsearch_values=1';
1571  //}
1572 
1573  $ref=$this->ref;
1574  if (empty($ref)) $ref=$this->id;
1575 
1576  $linkclose='';
1577  if (empty($notooltip))
1578  {
1579  if (! empty($conf->global->MAIN_OPTIMIZEFORTEXTBROWSER))
1580  {
1581  $label=$langs->trans("ShowExpenseReport");
1582  $linkclose.=' alt="'.dol_escape_htmltag($label, 1).'"';
1583  }
1584  $linkclose.= ' title="'.dol_escape_htmltag($label, 1).'"';
1585  $linkclose.=' class="classfortooltip"';
1586  }
1587 
1588  $linkstart = '<a href="'.$url.'"';
1589  $linkstart.=$linkclose.'>';
1590  $linkend='</a>';
1591 
1592  $result .= $linkstart;
1593  if ($withpicto) $result.=img_object(($notooltip?'':$label), $this->picto, ($notooltip?(($withpicto != 2) ? 'class="paddingright"' : ''):'class="'.(($withpicto != 2) ? 'paddingright ' : '').'classfortooltip"'), 0, 0, $notooltip?0:1);
1594  if ($withpicto != 2) $result.=($max?dol_trunc($ref,$max):$ref);
1595  $result .= $linkend;
1596 
1597  return $result;
1598  }
1599 
1607  function update_totaux_add($ligne_total_ht,$ligne_total_tva)
1608  {
1609  $this->total_ht = $this->total_ht + $ligne_total_ht;
1610  $this->total_tva = $this->total_tva + $ligne_total_tva;
1611  $this->total_ttc = $this->total_ht + $this->total_tva;
1612 
1613  $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element." SET";
1614  $sql.= " total_ht = ".$this->total_ht;
1615  $sql.= " , total_ttc = ".$this->total_ttc;
1616  $sql.= " , total_tva = ".$this->total_tva;
1617  $sql.= " WHERE rowid = ".$this->id;
1618 
1619  $result = $this->db->query($sql);
1620  if ($result):
1621  return 1;
1622  else:
1623  $this->error=$this->db->error();
1624  return -1;
1625  endif;
1626  }
1627 
1635  function update_totaux_del($ligne_total_ht,$ligne_total_tva)
1636  {
1637  $this->total_ht = $this->total_ht - $ligne_total_ht;
1638  $this->total_tva = $this->total_tva - $ligne_total_tva;
1639  $this->total_ttc = $this->total_ht + $this->total_tva;
1640 
1641  $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element." SET";
1642  $sql.= " total_ht = ".$this->total_ht;
1643  $sql.= " , total_ttc = ".$this->total_ttc;
1644  $sql.= " , total_tva = ".$this->total_tva;
1645  $sql.= " WHERE rowid = ".$this->id;
1646 
1647  $result = $this->db->query($sql);
1648  if ($result):
1649  return 1;
1650  else:
1651  $this->error=$this->db->error();
1652  return -1;
1653  endif;
1654  }
1655 
1670  function addline($qty=0, $up=0, $fk_c_type_fees=0, $vatrate=0, $date='', $comments='', $fk_project=0, $fk_c_exp_tax_cat=0, $type=0)
1671  {
1672  global $conf,$langs;
1673 
1674  dol_syslog(get_class($this)."::addline qty=$qty, up=$up, fk_c_type_fees=$fk_c_type_fees, vatrate=$vatrate, date=$date, fk_project=$fk_project, type=$type, comments=$comments", LOG_DEBUG);
1675 
1676  if (empty($qty)) $qty = 0;
1677  if (empty($fk_c_type_fees) || $fk_c_type_fees < 0) $fk_c_type_fees = 0;
1678  if (empty($fk_c_exp_tax_cat) || $fk_c_exp_tax_cat < 0) $fk_c_exp_tax_cat = 0;
1679  if (empty($vatrate) || $vatrate < 0) $vatrate = 0;
1680  if (empty($date)) $date = '';
1681  if (empty($fk_project)) $fk_project = 0;
1682 
1683  $qty = price2num($qty);
1684  $vatrate = price2num($vatrate);
1685  $up = price2num($up);
1686 
1687  if ($this->fk_statut == self::STATUS_DRAFT)
1688  {
1689  $this->db->begin();
1690 
1691  $this->line = new ExpenseReportLine($this->db);
1692 
1693  $seller = ''; // seller is unknown
1694  $tmp = calcul_price_total($qty, $up, 0, $vatrate, 0, 0, 0, 'TTC', 0, $type, $seller);
1695 
1696  $this->line->value_unit = $up;
1697  $this->line->vatrate = price2num($vatrate);
1698  $this->line->total_ttc = $tmp[2];
1699  $this->line->total_ht = $tmp[0];
1700  $this->line->total_tva = $tmp[1];
1701 
1702  $this->line->fk_expensereport = $this->id;
1703  $this->line->qty = $qty;
1704  $this->line->date = $date;
1705  $this->line->fk_c_type_fees = $fk_c_type_fees;
1706  $this->line->fk_c_exp_tax_cat = $fk_c_exp_tax_cat;
1707  $this->line->comments = $comments;
1708  $this->line->fk_projet = $fk_project;
1709 
1710  $this->applyOffset();
1711  $this->checkRules($type, $seller);
1712 
1713  $result=$this->line->insert(0, true);
1714  if ($result > 0)
1715  {
1716  $result=$this->update_price(); // This method is designed to add line from user input so total calculation must be done using 'auto' mode.
1717  if ($result > 0)
1718  {
1719  $this->db->commit();
1720  return $this->line->rowid;
1721  }
1722  else
1723  {
1724  $this->db->rollback();
1725  return -1;
1726  }
1727  }
1728  else
1729  {
1730  $this->error=$this->line->error;
1731  dol_syslog(get_class($this)."::addline error=".$this->error, LOG_ERR);
1732  $this->db->rollback();
1733  return -2;
1734  }
1735  }
1736  else
1737  {
1738  dol_syslog(get_class($this)."::addline status of expense report must be Draft to allow use of ->addline()", LOG_ERR);
1739  $this->error = 'ErrorExpenseNotDraft';
1740  return -3;
1741  }
1742 
1743 
1744  }
1745 
1753  function checkRules($type=0, $seller='')
1754  {
1755  global $user,$conf,$db,$langs;
1756 
1757  $langs->load('trips');
1758 
1759  if (empty($conf->global->MAIN_USE_EXPENSE_RULE)) return true; // if don't use rules
1760 
1761  $rulestocheck = ExpenseReportRule::getAllRule($this->line->fk_c_type_fees, $this->line->date, $this->fk_user_author);
1762 
1763  $violation = 0;
1764  $rule_warning_message_tab = array();
1765 
1766  $current_total_ttc = $this->line->total_ttc;
1767  $new_current_total_ttc = $this->line->total_ttc;
1768 
1769  // check if one is violated
1770  foreach ($rulestocheck as $rule)
1771  {
1772  if (in_array($rule->code_expense_rules_type, array('EX_DAY', 'EX_MON', 'EX_YEA'))) $amount_to_test = $this->line->getExpAmount($rule, $this->fk_user_author, $rule->code_expense_rules_type);
1773  else $amount_to_test = $current_total_ttc; // EX_EXP
1774 
1775  $amount_to_test = $amount_to_test - $current_total_ttc + $new_current_total_ttc; // if amount as been modified by a previous rule
1776 
1777  if ($amount_to_test > $rule->amount)
1778  {
1779  $violation++;
1780 
1781  if ($rule->restrictive)
1782  {
1783  $this->error = 'ExpenseReportConstraintViolationError';
1784  $this->errors[] = $this->error;
1785 
1786  $new_current_total_ttc -= $amount_to_test - $rule->amount; // ex, entered 16€, limit 12€, subtracts 4€;
1787  $rule_warning_message_tab[] = $langs->trans('ExpenseReportConstraintViolationError', $rule->id, price($amount_to_test,0,$langs,1,-1,-1,$conf->currency), price($rule->amount,0,$langs,1,-1,-1,$conf->currency), $langs->trans('by'.$rule->code_expense_rules_type, price($new_current_total_ttc,0,$langs,1,-1,-1,$conf->currency)));
1788  }
1789  else
1790  {
1791  $this->error = 'ExpenseReportConstraintViolationWarning';
1792  $this->errors[] = $this->error;
1793 
1794  $rule_warning_message_tab[] = $langs->trans('ExpenseReportConstraintViolationWarning', $rule->id, price($amount_to_test,0,$langs,1,-1,-1,$conf->currency), price($rule->amount,0,$langs,1,-1,-1,$conf->currency), $langs->trans('nolimitby'.$rule->code_expense_rules_type));
1795  }
1796 
1797  // No break, we sould test if another rule is violated
1798  }
1799  }
1800 
1801  $this->line->rule_warning_message = implode('\n', $rule_warning_message_tab);
1802 
1803  if ($violation > 0)
1804  {
1805  $tmp = calcul_price_total($this->line->qty, $new_current_total_ttc/$this->line->qty, 0, $this->line->vatrate, 0, 0, 0, 'TTC', 0, $type, $seller);
1806 
1807  $this->line->value_unit = $tmp[5];
1808  $this->line->total_ttc = $tmp[2];
1809  $this->line->total_ht = $tmp[0];
1810  $this->line->total_tva = $tmp[1];
1811 
1812  return false;
1813  }
1814  else return true;
1815  }
1816 
1822  function applyOffset()
1823  {
1824  global $conf;
1825 
1826  if (empty($conf->global->MAIN_USE_EXPENSE_IK)) return false;
1827 
1828  $userauthor = new User($this->db);
1829  if ($userauthor->fetch($this->fk_user_author) <= 0)
1830  {
1831  $this->error = 'ErrorCantFetchUser';
1832  $this->errors[] = 'ErrorCantFetchUser';
1833  return false;
1834  }
1835 
1836  $range = ExpenseReportIk::getRangeByUser($userauthor, $this->line->fk_c_exp_tax_cat);
1837 
1838  if (empty($range))
1839  {
1840  $this->error = 'ErrorNoRangeAvailable';
1841  $this->errors[] = 'ErrorNoRangeAvailable';
1842  return false;
1843  }
1844 
1845  if (!empty($conf->global->MAIN_EXPENSE_APPLY_ENTIRE_OFFSET)) $ikoffset = $range->ikoffset;
1846  else $ikoffset = $range->ikoffset / 12; // The amount of offset is a global value for the year
1847 
1848  // Test if ikoffset has been applied for the current month
1849  if (!$this->offsetAlreadyGiven())
1850  {
1851  $new_up = $range->coef + ($ikoffset / $this->line->qty);
1852  $tmp = calcul_price_total($this->line->qty, $new_up, 0, $this->line->vatrate, 0, 0, 0, 'TTC', 0, $type, $seller);
1853 
1854  $this->line->value_unit = $tmp[5];
1855  $this->line->total_ttc = $tmp[2];
1856  $this->line->total_ht = $tmp[0];
1857  $this->line->total_tva = $tmp[1];
1858 
1859  return true;
1860  }
1861 
1862  return false;
1863  }
1864 
1871  {
1872  $sql = 'SELECT e.rowid FROM '.MAIN_DB_PREFIX.'expensereport e';
1873  $sql.= ' INNER JOIN '.MAIN_DB_PREFIX.'expensereport_det d ON (e.rowid = d.fk_expensereport)';
1874  $sql.= ' INNER JOIN '.MAIN_DB_PREFIX.'c_type_fees f ON (d.fk_c_type_fees = f.id AND f.code = "EX_KME")';
1875  $sql.= ' WHERE e.fk_user_author = '.(int) $this->fk_user_author;
1876  $sql.= ' AND YEAR(d.date) = "'.dol_print_date($this->line->date, '%Y').'" AND MONTH(d.date) = "'.dol_print_date($this->line->date, '%m').'"';
1877  if (!empty($this->line->id)) $sql.= ' AND d.rowid <> '.$this->line->id;
1878 
1879  dol_syslog(get_class($this)."::offsetAlreadyGiven sql=".$sql);
1880  $resql = $this->db->query($sql);
1881  if ($resql)
1882  {
1883  $num = $this->db->num_rows($resql);
1884  if ($num > 0) return true;
1885  }
1886  else
1887  {
1888  dol_print_error($this->db);
1889  }
1890 
1891  return false;
1892  }
1893 
1909  function updateline($rowid, $type_fees_id, $projet_id, $vatrate, $comments, $qty, $value_unit, $date, $expensereport_id, $fk_c_exp_tax_cat=0)
1910  {
1911  global $user, $mysoc;
1912 
1913  if ($this->fk_statut==0 || $this->fk_statut==99)
1914  {
1915  $this->db->begin();
1916 
1917  $type = 0; // TODO What if type is service ?
1918 
1919  // We don't know seller and buyer for expense reports
1920  $seller = $mysoc;
1921  $buyer = new Societe($this->db);
1922 
1923  $localtaxes_type=getLocalTaxesFromRate($vatrate,0,$buyer,$seller);
1924 
1925  // Clean vat code
1926  $vat_src_code='';
1927 
1928  if (preg_match('/\((.*)\)/', $vatrate, $reg))
1929  {
1930  $vat_src_code = $reg[1];
1931  $vatrate = preg_replace('/\s*\(.*\)/', '', $vatrate); // Remove code into vatrate.
1932  }
1933  $vatrate = preg_replace('/\*/','',$vatrate);
1934 
1935  $tmp = calcul_price_total($qty, $value_unit, 0, $vatrate, 0, 0, 0, 'TTC', 0, $type, $seller, $localtaxes_type);
1936 
1937  // calcul total of line
1938  //$total_ttc = price2num($qty*$value_unit, 'MT');
1939 
1940  $tx_tva = $vatrate / 100;
1941  $tx_tva = $tx_tva + 1;
1942  $total_ht = price2num($total_ttc/$tx_tva, 'MT');
1943 
1944  $total_tva = price2num($total_ttc - $total_ht, 'MT');
1945  // fin calculs
1946 
1947  $this->line = new ExpenseReportLine($this->db);
1948  $this->line->comments = $comments;
1949  $this->line->qty = $qty;
1950  $this->line->value_unit = $value_unit;
1951  $this->line->date = $date;
1952 
1953  $this->line->fk_expensereport= $expensereport_id;
1954  $this->line->fk_c_type_fees = $type_fees_id;
1955  $this->line->fk_c_exp_tax_cat = $fk_c_exp_tax_cat;
1956  $this->line->fk_projet = $projet_id;
1957 
1958  $this->line->vat_src_code = $vat_src_code;
1959  $this->line->vatrate = price2num($vatrate);
1960  $this->line->total_ttc = $tmp[2];
1961  $this->line->total_ht = $tmp[0];
1962  $this->line->total_tva = $tmp[1];
1963  $this->line->localtax1_tx = $localtaxes_type[1];
1964  $this->line->localtax2_tx = $localtaxes_type[3];
1965  $this->line->localtax1_type = $localtaxes_type[0];
1966  $this->line->localtax2_type = $localtaxes_type[2];
1967 
1968  $this->line->rowid = $rowid;
1969  $this->line->id = $rowid;
1970 
1971  // Select des infos sur le type fees
1972  $sql = "SELECT c.code as code_type_fees, c.label as libelle_type_fees";
1973  $sql.= " FROM ".MAIN_DB_PREFIX."c_type_fees as c";
1974  $sql.= " WHERE c.id = ".$type_fees_id;
1975  $result = $this->db->query($sql);
1976  $objp_fees = $this->db->fetch_object($result);
1977  $this->line->type_fees_code = $objp_fees->code_type_fees;
1978  $this->line->type_fees_libelle = $objp_fees->libelle_type_fees;
1979 
1980  // Select des informations du projet
1981  $sql = "SELECT p.ref as ref_projet, p.title as title_projet";
1982  $sql.= " FROM ".MAIN_DB_PREFIX."projet as p";
1983  $sql.= " WHERE p.rowid = ".$projet_id;
1984  $result = $this->db->query($sql);
1985  if ($result) {
1986  $objp_projet = $this->db->fetch_object($result);
1987  }
1988  $this->line->projet_ref = $objp_projet->ref_projet;
1989  $this->line->projet_title = $objp_projet->title_projet;
1990 
1991  $this->applyOffset();
1992  $this->checkRules();
1993 
1994  $result = $this->line->update($user);
1995  if ($result > 0)
1996  {
1997  $this->db->commit();
1998  return 1;
1999  }
2000  else
2001  {
2002  $this->error=$this->line->error;
2003  $this->errors=$this->line->errors;
2004  $this->db->rollback();
2005  return -2;
2006  }
2007  }
2008  }
2009 
2017  function deleteline($rowid, $fuser='')
2018  {
2019  $this->db->begin();
2020 
2021  $sql = 'DELETE FROM '.MAIN_DB_PREFIX.$this->table_element_line;
2022  $sql.= ' WHERE rowid = '.$rowid;
2023 
2024  dol_syslog(get_class($this)."::deleteline sql=".$sql);
2025  $result = $this->db->query($sql);
2026  if (!$result)
2027  {
2028  $this->error=$this->db->error();
2029  dol_syslog(get_class($this)."::deleteline Error ".$this->error, LOG_ERR);
2030  $this->db->rollback();
2031  return -1;
2032  }
2033 
2034  $this->db->commit();
2035 
2036  return 1;
2037  }
2038 
2047  function periode_existe($fuser, $date_debut, $date_fin)
2048  {
2049  $sql = "SELECT rowid, date_debut, date_fin";
2050  $sql.= " FROM ".MAIN_DB_PREFIX.$this->table_element;
2051  $sql.= " WHERE fk_user_author = '{$fuser->id}'";
2052 
2053  dol_syslog(get_class($this)."::periode_existe sql=".$sql);
2054  $result = $this->db->query($sql);
2055  if($result)
2056  {
2057  $num_lignes = $this->db->num_rows($result); $i = 0;
2058 
2059  if ($num_lignes>0)
2060  {
2061  $date_d_form = $date_debut;
2062  $date_f_form = $date_fin;
2063 
2064  $existe = false;
2065 
2066  while ($i < $num_lignes)
2067  {
2068  $objp = $this->db->fetch_object($result);
2069 
2070  $date_d_req = $this->db->jdate($objp->date_debut); // 3
2071  $date_f_req = $this->db->jdate($objp->date_fin); // 4
2072 
2073  if (!($date_f_form < $date_d_req || $date_d_form > $date_f_req)) $existe = true;
2074 
2075  $i++;
2076  }
2077 
2078  if($existe) return 1;
2079  else return 0;
2080  }
2081  else
2082  {
2083  return 0;
2084  }
2085  }
2086  else
2087  {
2088  $this->error=$this->db->lasterror();
2089  dol_syslog(get_class($this)."::periode_existe Error ".$this->error, LOG_ERR);
2090  return -1;
2091  }
2092  }
2093 
2094 
2102  {
2103  $users_validator=array();
2104 
2105  $sql = "SELECT DISTINCT ur.fk_user";
2106  $sql.= " FROM ".MAIN_DB_PREFIX."user_rights as ur, ".MAIN_DB_PREFIX."rights_def as rd";
2107  $sql.= " WHERE ur.fk_id = rd.id and rd.module = 'expensereport' AND rd.perms = 'approve'"; // Permission 'Approve';
2108  $sql.= "UNION";
2109  $sql.= " SELECT DISTINCT ugu.fk_user";
2110  $sql.= " FROM ".MAIN_DB_PREFIX."usergroup_user as ugu, ".MAIN_DB_PREFIX."usergroup_rights as ur, ".MAIN_DB_PREFIX."rights_def as rd";
2111  $sql.= " WHERE ugu.fk_usergroup = ur.fk_usergroup AND ur.fk_id = rd.id and rd.module = 'expensereport' AND rd.perms = 'approve'"; // Permission 'Approve';
2112  //print $sql;
2113 
2114  dol_syslog(get_class($this)."::fetch_users_approver_expensereport sql=".$sql);
2115  $result = $this->db->query($sql);
2116  if($result)
2117  {
2118  $num_lignes = $this->db->num_rows($result); $i = 0;
2119  while ($i < $num_lignes)
2120  {
2121  $objp = $this->db->fetch_object($result);
2122  array_push($users_validator,$objp->fk_user);
2123  $i++;
2124  }
2125  return $users_validator;
2126  }
2127  else
2128  {
2129  $this->error=$this->db->lasterror();
2130  dol_syslog(get_class($this)."::fetch_users_approver_expensereport Error ".$this->error, LOG_ERR);
2131  return -1;
2132  }
2133  }
2134 
2145  public function generateDocument($modele, $outputlangs, $hidedetails=0, $hidedesc=0, $hideref=0)
2146  {
2147  global $conf,$langs;
2148 
2149  $langs->load("trips");
2150 
2151  if (! dol_strlen($modele)) {
2152 
2153  $modele = 'standard';
2154 
2155  if ($this->modelpdf) {
2156  $modele = $this->modelpdf;
2157  } elseif (! empty($conf->global->EXPENSEREPORT_ADDON_PDF)) {
2158  $modele = $conf->global->EXPENSEREPORT_ADDON_PDF;
2159  }
2160  }
2161 
2162  $modelpath = "core/modules/expensereport/doc/";
2163 
2164  return $this->commonGenerateDocument($modelpath, $modele, $outputlangs, $hidedetails, $hidedesc, $hideref);
2165  }
2166 
2173  function listOfTypes($active=1)
2174  {
2175  global $langs;
2176  $ret=array();
2177  $sql = "SELECT id, code, label";
2178  $sql.= " FROM ".MAIN_DB_PREFIX."c_type_fees";
2179  $sql.= " WHERE active = ".$active;
2180  dol_syslog(get_class($this)."::listOfTypes", LOG_DEBUG);
2181  $result = $this->db->query($sql);
2182  if ( $result )
2183  {
2184  $num = $this->db->num_rows($result);
2185  $i=0;
2186  while ($i < $num)
2187  {
2188  $obj = $this->db->fetch_object($result);
2189  $ret[$obj->code]=(($langs->trans($obj->code)!=$obj->code)?$langs->trans($obj->code):$obj->label);
2190  $i++;
2191  }
2192  }
2193  else
2194  {
2195  dol_print_error($this->db);
2196  }
2197  return $ret;
2198  }
2199 
2205  function load_state_board()
2206  {
2207  global $conf;
2208 
2209  $this->nb=array();
2210 
2211  $sql = "SELECT count(ex.rowid) as nb";
2212  $sql.= " FROM ".MAIN_DB_PREFIX."expensereport as ex";
2213  $sql.= " WHERE ex.fk_statut > 0";
2214  $sql.= " AND ex.entity IN (".getEntity('expensereport').")";
2215 
2216  $resql=$this->db->query($sql);
2217  if ($resql)
2218  {
2219  while ($obj=$this->db->fetch_object($resql))
2220  {
2221  $this->nb["expensereports"]=$obj->nb;
2222  }
2223  $this->db->free($resql);
2224  return 1;
2225  }
2226  else
2227  {
2228  dol_print_error($this->db);
2229  $this->error=$this->db->error();
2230  return -1;
2231  }
2232  }
2233 
2241  function load_board($user, $option='topay')
2242  {
2243  global $conf, $langs;
2244 
2245  if ($user->societe_id) return -1; // protection pour eviter appel par utilisateur externe
2246 
2247  $now=dol_now();
2248 
2249  $userchildids = $user->getAllChildIds(1);
2250 
2251  $sql = "SELECT ex.rowid, ex.date_valid";
2252  $sql.= " FROM ".MAIN_DB_PREFIX."expensereport as ex";
2253  if ($option == 'toapprove') $sql.= " WHERE ex.fk_statut = 2";
2254  else $sql.= " WHERE ex.fk_statut = 5";
2255  $sql.= " AND ex.entity IN (".getEntity('expensereport').")";
2256  $sql.= " AND (ex.fk_user_author IN (".join(',',$userchildids).")";
2257  $sql.= " OR ex.fk_user_validator IN (".join(',',$userchildids)."))";
2258 
2259  $resql=$this->db->query($sql);
2260  if ($resql)
2261  {
2262  $langs->load("members");
2263 
2264  $response = new WorkboardResponse();
2265  if ($option == 'toapprove')
2266  {
2267  $response->warning_delay=$conf->expensereport->approve->warning_delay/60/60/24;
2268  $response->label=$langs->trans("ExpenseReportsToApprove");
2269  $response->url=DOL_URL_ROOT.'/expensereport/list.php?mainmenu=hrm&amp;statut=2';
2270  }
2271  else
2272  {
2273  $response->warning_delay=$conf->expensereport->payment->warning_delay/60/60/24;
2274  $response->label=$langs->trans("ExpenseReportsToPay");
2275  $response->url=DOL_URL_ROOT.'/expensereport/list.php?mainmenu=hrm&amp;statut=5';
2276  }
2277  $response->img=img_object('',"trip");
2278 
2279  while ($obj=$this->db->fetch_object($resql))
2280  {
2281  $response->nbtodo++;
2282 
2283  if ($option == 'toapprove')
2284  {
2285  if ($this->db->jdate($obj->date_valid) < ($now - $conf->expensereport->approve->warning_delay)) {
2286  $response->nbtodolate++;
2287  }
2288  }
2289  else
2290  {
2291  if ($this->db->jdate($obj->date_valid) < ($now - $conf->expensereport->payment->warning_delay)) {
2292  $response->nbtodolate++;
2293  }
2294  }
2295  }
2296 
2297  return $response;
2298  }
2299  else
2300  {
2301  dol_print_error($this->db);
2302  $this->error=$this->db->error();
2303  return -1;
2304  }
2305  }
2306 
2313  public function hasDelay($option)
2314  {
2315  global $conf;
2316 
2317  //Only valid members
2318  if ($option == 'toapprove' && $this->status != 2) return false;
2319  if ($option == 'topay' && $this->status != 5) return false;
2320 
2321  $now = dol_now();
2322  if ($option == 'toapprove')
2323  {
2324  return ($this->datevalid?$this->datevalid:$this->date_valid) < ($now - $conf->expensereport->approve->warning_delay);
2325  }
2326  else
2327  return ($this->datevalid?$this->datevalid:$this->date_valid) < ($now - $conf->expensereport->payment->warning_delay);
2328  }
2329 }
2330 
2331 
2336 {
2337  var $db;
2338  var $error;
2339 
2340  var $rowid;
2341  var $comments;
2342  var $qty;
2343  var $value_unit;
2344  var $date;
2345 
2346  var $fk_c_type_fees;
2347  var $fk_c_exp_tax_cat;
2348  var $fk_projet;
2349  var $fk_expensereport;
2350 
2351  var $type_fees_code;
2352  var $type_fees_libelle;
2353 
2354  var $projet_ref;
2355  var $projet_title;
2356 
2357  var $vatrate;
2358  var $total_ht;
2359  var $total_tva;
2360  var $total_ttc;
2361 
2367  function __construct($db)
2368  {
2369  $this->db= $db;
2370  }
2371 
2378  function fetch($rowid)
2379  {
2380  $sql = 'SELECT fde.rowid, fde.fk_expensereport, fde.fk_c_type_fees, fde.fk_c_exp_tax_cat, fde.fk_projet, fde.date,';
2381  $sql.= ' fde.tva_tx as vatrate, fde.vat_src_code, fde.comments, fde.qty, fde.value_unit, fde.total_ht, fde.total_tva, fde.total_ttc,';
2382  $sql.= ' ctf.code as type_fees_code, ctf.label as type_fees_libelle,';
2383  $sql.= ' pjt.rowid as projet_id, pjt.title as projet_title, pjt.ref as projet_ref';
2384  $sql.= ' FROM '.MAIN_DB_PREFIX.'expensereport_det as fde';
2385  $sql.= ' INNER JOIN '.MAIN_DB_PREFIX.'c_type_fees as ctf ON fde.fk_c_type_fees=ctf.id';
2386  $sql.= ' LEFT JOIN '.MAIN_DB_PREFIX.'projet as pjt ON fde.fk_projet=pjt.rowid';
2387  $sql.= ' WHERE fde.rowid = '.$rowid;
2388 
2389  $result = $this->db->query($sql);
2390 
2391  if($result)
2392  {
2393  $objp = $this->db->fetch_object($result);
2394 
2395  $this->rowid = $objp->rowid;
2396  $this->id = $obj->rowid;
2397  $this->ref = $obj->ref;
2398  $this->fk_expensereport = $objp->fk_expensereport;
2399  $this->comments = $objp->comments;
2400  $this->qty = $objp->qty;
2401  $this->date = $objp->date;
2402  $this->dates = $this->db->jdate($objp->date);
2403  $this->value_unit = $objp->value_unit;
2404  $this->fk_c_type_fees = $objp->fk_c_type_fees;
2405  $this->fk_c_exp_tax_cat = $objp->fk_c_exp_tax_cat;
2406  $this->fk_projet = $objp->fk_projet;
2407  $this->type_fees_code = $objp->type_fees_code;
2408  $this->type_fees_libelle = $objp->type_fees_libelle;
2409  $this->projet_ref = $objp->projet_ref;
2410  $this->projet_title = $objp->projet_title;
2411  $this->vatrate = $objp->vatrate;
2412  $this->vat_src_code = $objp->vat_src_code;
2413  $this->total_ht = $objp->total_ht;
2414  $this->total_tva = $objp->total_tva;
2415  $this->total_ttc = $objp->total_ttc;
2416 
2417  $this->db->free($result);
2418  } else {
2419  dol_print_error($this->db);
2420  }
2421  }
2422 
2430  function insert($notrigger=0,$fromaddline=false)
2431  {
2432  global $langs,$user,$conf;
2433 
2434  $error=0;
2435 
2436  dol_syslog("ExpenseReportLine::Insert rang=".$this->rang, LOG_DEBUG);
2437 
2438  // Clean parameters
2439  $this->comments=trim($this->comments);
2440  if (!$this->value_unit_HT) $this->value_unit_HT=0;
2441  $this->qty = price2num($this->qty);
2442  $this->vatrate = price2num($this->vatrate);
2443  if (empty($this->fk_c_exp_tax_cat)) $this->fk_c_exp_tax_cat = 0;
2444 
2445  $this->db->begin();
2446 
2447  $sql = 'INSERT INTO '.MAIN_DB_PREFIX.'expensereport_det';
2448  $sql.= ' (fk_expensereport, fk_c_type_fees, fk_projet,';
2449  $sql.= ' tva_tx, vat_src_code, comments, qty, value_unit, total_ht, total_tva, total_ttc, date, rule_warning_message, fk_c_exp_tax_cat)';
2450  $sql.= " VALUES (".$this->fk_expensereport.",";
2451  $sql.= " ".$this->fk_c_type_fees.",";
2452  $sql.= " ".($this->fk_projet>0?$this->fk_projet:'null').",";
2453  $sql.= " ".$this->vatrate.",";
2454  $sql.= " '".$this->db->escape($this->vat_src_code)."',";
2455  $sql.= " '".$this->db->escape($this->comments)."',";
2456  $sql.= " ".$this->qty.",";
2457  $sql.= " ".$this->value_unit.",";
2458  $sql.= " ".$this->total_ht.",";
2459  $sql.= " ".$this->total_tva.",";
2460  $sql.= " ".$this->total_ttc.",";
2461  $sql.= "'".$this->db->idate($this->date)."',";
2462  $sql.= " '".$this->db->escape($this->rule_warning_message)."',";
2463  $sql.= " ".$this->fk_c_exp_tax_cat;
2464  $sql.= ")";
2465 
2466  dol_syslog("ExpenseReportLine::insert sql=".$sql);
2467 
2468  $resql=$this->db->query($sql);
2469  if ($resql)
2470  {
2471  $this->rowid=$this->db->last_insert_id(MAIN_DB_PREFIX.'expensereport_det');
2472 
2473  if (! $fromaddline)
2474  {
2475  $tmpparent=new ExpenseReport($this->db);
2476  $tmpparent->fetch($this->fk_expensereport);
2477  $result = $tmpparent->update_price();
2478  if ($result < 0)
2479  {
2480  $error++;
2481  $this->error = $tmpparent->error;
2482  $this->errors = $tmpparent->errors;
2483  }
2484  }
2485  }
2486  else
2487  {
2488  $error++;
2489  }
2490 
2491  if (! $error)
2492  {
2493  $this->db->commit();
2494  return $this->rowid;
2495  }
2496  else
2497  {
2498  $this->error=$this->db->lasterror();
2499  dol_syslog("ExpenseReportLine::insert Error ".$this->error, LOG_ERR);
2500  $this->db->rollback();
2501  return -2;
2502  }
2503  }
2504 
2513  public function getExpAmount(ExpenseReportRule $rule, $fk_user, $mode='day')
2514  {
2515  $amount = 0;
2516 
2517  $sql = 'SELECT SUM(d.total_ttc) as total_amount';
2518  $sql .= ' FROM '.MAIN_DB_PREFIX.'expensereport_det d';
2519  $sql .= ' INNER JOIN '.MAIN_DB_PREFIX.'expensereport e ON (d.fk_expensereport = e.rowid)';
2520  $sql .= ' WHERE e.fk_user_author = '.$fk_user;
2521  if (!empty($this->id)) $sql.= ' AND d.rowid <> '.$this->id;
2522  $sql .= ' AND d.fk_c_type_fees = '.$rule->fk_c_type_fees;
2523  if ($mode == 'day' || $mode == 'EX_DAY') $sql .= ' AND d.date = \''.dol_print_date($this->date, '%Y-%m-%d').'\'';
2524  elseif ($mode == 'mon' || $mode == 'EX_MON') $sql .= ' AND DATE_FORMAT(d.date, \'%Y-%m\') = \''.dol_print_date($this->date, '%Y-%m').'\'';
2525  elseif ($mode == 'year' || $mode == 'EX_YEA') $sql .= ' AND DATE_FORMAT(d.date, \'%Y\') = \''.dol_print_date($this->date, '%Y').'\'';
2526 
2527  dol_syslog('ExpenseReportLine::getExpAmountByDay sql='.$sql);
2528 
2529  $resql = $this->db->query($sql);
2530  if ($resql)
2531  {
2532  $num = $this->db->num_rows($resql);
2533  if ($num > 0)
2534  {
2535  $obj = $this->db->fetch_object($resql);
2536  $amount = (double) $obj->total_amount;
2537  }
2538  }
2539  else
2540  {
2541  dol_print_error($this->db);
2542  }
2543 
2544 
2545  return $amount + $this->total_ttc;
2546  }
2547 
2554  function update($fuser)
2555  {
2556  global $fuser,$langs,$conf;
2557 
2558  $error=0;
2559 
2560  // Clean parameters
2561  $this->comments=trim($this->comments);
2562  $this->vatrate = price2num($this->vatrate);
2563  $this->value_unit = price2num($this->value_unit);
2564  if (empty($this->fk_c_exp_tax_cat)) $this->fk_c_exp_tax_cat = 0;
2565 
2566  $this->db->begin();
2567 
2568  // Update line in database
2569  $sql = "UPDATE ".MAIN_DB_PREFIX."expensereport_det SET";
2570  $sql.= " comments='".$this->db->escape($this->comments)."'";
2571  $sql.= ",value_unit=".$this->value_unit;
2572  $sql.= ",qty=".$this->qty;
2573  $sql.= ",date='".$this->db->idate($this->date)."'";
2574  $sql.= ",total_ht=".$this->total_ht."";
2575  $sql.= ",total_tva=".$this->total_tva."";
2576  $sql.= ",total_ttc=".$this->total_ttc."";
2577  $sql.= ",tva_tx=".$this->vatrate;
2578  $sql.= ",vat_src_code='".$this->db->escape($this->vat_src_code)."'";
2579  $sql.= ",rule_warning_message='".$this->db->escape($this->rule_warning_message)."'";
2580  $sql.= ",fk_c_exp_tax_cat=".$this->fk_c_exp_tax_cat;
2581  if ($this->fk_c_type_fees) $sql.= ",fk_c_type_fees=".$this->fk_c_type_fees;
2582  else $sql.= ",fk_c_type_fees=null";
2583  if ($this->fk_projet) $sql.= ",fk_projet=".$this->fk_projet;
2584  else $sql.= ",fk_projet=null";
2585  $sql.= " WHERE rowid = ".$this->rowid;
2586 
2587  dol_syslog("ExpenseReportLine::update sql=".$sql);
2588 
2589  $resql=$this->db->query($sql);
2590  if ($resql)
2591  {
2592  $tmpparent=new ExpenseReport($this->db);
2593  $result = $tmpparent->fetch($this->fk_expensereport);
2594  if ($result > 0)
2595  {
2596  $result = $tmpparent->update_price();
2597  if ($result < 0)
2598  {
2599  $error++;
2600  $this->error = $tmpparent->error;
2601  $this->errors = $tmpparent->errors;
2602  }
2603  }
2604  else
2605  {
2606  $error++;
2607  $this->error = $tmpparent->error;
2608  $this->errors = $tmpparent->errors;
2609  }
2610  }
2611  else
2612  {
2613  $error++;
2614  dol_print_error($this->db);
2615  }
2616 
2617  if (! $error)
2618  {
2619  $this->db->commit();
2620  return 1;
2621  }
2622  else
2623  {
2624  $this->error=$this->db->lasterror();
2625  dol_syslog("ExpenseReportLine::update Error ".$this->error, LOG_ERR);
2626  $this->db->rollback();
2627  return -2;
2628  }
2629  }
2630 }
2631 
2632 
2643 function select_expensereport_statut($selected='',$htmlname='fk_statut',$useempty=1, $useshortlabel=0)
2644 {
2645  global $db, $langs;
2646 
2647  $tmpep=new ExpenseReport($db);
2648 
2649  print '<select class="flat" name="'.$htmlname.'">';
2650  if ($useempty) print '<option value="-1">&nbsp;</option>';
2651  $arrayoflabels=$tmpep->statuts;
2652  if ($useshortlabel) $arrayoflabels=$tmpep->statuts_short;
2653  foreach ($arrayoflabels as $key => $val)
2654  {
2655  if ($selected != '' && $selected == $key)
2656  {
2657  print '<option value="'.$key.'" selected>';
2658  }
2659  else
2660  {
2661  print '<option value="'.$key.'">';
2662  }
2663  print $langs->trans($val);
2664  print '</option>';
2665  }
2666  print '</select>';
2667 }
2668 
2678 function select_type_fees_id($selected='',$htmlname='type',$showempty=0, $active=1)
2679 {
2680  global $db,$langs,$user;
2681  $langs->load("trips");
2682 
2683  print '<select class="flat" name="'.$htmlname.'">';
2684  if ($showempty)
2685  {
2686  print '<option value="-1"';
2687  if ($selected == -1) print ' selected';
2688  print '>&nbsp;</option>';
2689  }
2690 
2691  $sql = "SELECT c.id, c.code, c.label as type FROM ".MAIN_DB_PREFIX."c_type_fees as c";
2692  if ($active >= 0) $sql.= " WHERE c.active = ".$active;
2693  $sql.= " ORDER BY c.label ASC";
2694  $resql=$db->query($sql);
2695  if ($resql)
2696  {
2697  $num = $db->num_rows($resql);
2698  $i = 0;
2699 
2700  while ($i < $num)
2701  {
2702  $obj = $db->fetch_object($resql);
2703  print '<option value="'.$obj->id.'"';
2704  if ($obj->code == $selected || $obj->id == $selected) print ' selected';
2705  print '>';
2706  if ($obj->code != $langs->trans($obj->code)) print $langs->trans($obj->code);
2707  else print $langs->trans($obj->type);
2708  $i++;
2709  }
2710  }
2711  print '</select>';
2712 }
getNextNumRef()
Return next reference of expense report not already used.
select_expensereport_statut($selected='', $htmlname='fk_statut', $useempty=1, $useshortlabel=0)
Retourne la liste deroulante des differents etats d'une note de frais.
createFromClone($fk_user_author)
Load an object from its id and create a new one in database.
const STATUS_APPROVED
Classified approved.
img_picto($titlealt, $picto, $moreatt= '', $pictoisfullpath=false, $srconly=0, $notitle=0, $alt='', $morecss='')
Show picto whatever it's its name (generic function)
const STATUS_VALIDATED
Validated (need to be paid)
LibStatut($status, $mode=0)
Returns the label of a statut.
dol_trunc($string, $size=40, $trunc='right', $stringencoding='UTF-8', $nodot=0, $display=0)
Truncate a string to a particular length adding '...' if string larger than length.
getExpAmount(ExpenseReportRule $rule, $fk_user, $mode='day')
Function to get total amount in expense reports for a same rule.
info($id)
Load information on object.
Class of expense report details lines.
__construct($db)
Constructor.
applyOffset()
Method to apply the offset if needed.
load_board($user, $option='topay')
Load indicators for dashboard (this->nbtodo and this->nbtodolate)
dol_sanitizeFileName($str, $newstr='_', $unaccent=1)
Clean a string to use it as a file name.
select_type_fees_id($selected='', $htmlname='type', $showempty=0, $active=1)
Return list of types of notes with select value = id.
addline($qty=0, $up=0, $fk_c_type_fees=0, $vatrate=0, $date='', $comments='', $fk_project=0, $fk_c_exp_tax_cat=0, $type=0)
addline
Class to manage inventories.
Class to manage Dolibarr users.
Definition: user.class.php:39
const STATUS_CLOSED
Classified paid.
commonGenerateDocument($modelspath, $modele, $outputlangs, $hidedetails, $hidedesc, $hideref, $moreparams=null)
Common function for all objects extending CommonObject for generating documents.
setDeny($fuser, $details, $notrigger=0)
setDeny
fetch($rowid)
Fetch record for expense report detailed line.
dol_print_error($db='', $error='', $errors=null)
Affiche message erreur system avec toutes les informations pour faciliter le diagnostic et la remonte...
fetch_lines()
fetch_lines
periode_existe($fuser, $date_debut, $date_fin)
periode_existe
dol_buildpath($path, $type=0, $returnemptyifnotfound=0)
Return path of url or filesystem.
const STATUS_REFUSED
Classified refused.
dol_dir_list($path, $types="all", $recursive=0, $filter="", $excludefilter=null, $sortcriteria="name", $sortorder=SORT_ASC, $mode=0, $nohook=0, $relativename="")
Scan a directory and return a list of files/directories.
Definition: files.lib.php:58
set_unpaid($fuser, $notrigger=0)
set_unpaid
setValidate($fuser, $notrigger=0)
Set to status validate.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
Class to manage third parties objects (customers, suppliers, prospects...)
insertExtraFields($trigger='', $userused=null)
Add/Update all extra fields values for the current object.
update_totaux_del($ligne_total_ht, $ligne_total_tva)
Update total of an expense report when you delete a line.
checkRules($type=0, $seller='')
Check constraint of rules and update price if needed.
update_totaux_add($ligne_total_ht, $ligne_total_tva)
Update total of an expense report when you add a line.
fetch_users_approver_expensereport()
Return list of people with permission to validate expense reports.
update_price($exclspec=0, $roundingadjust='none', $nodatabaseupdate=0, $seller=null)
Update total_ht, total_ttc, total_vat, total_localtax1, total_localtax2 for an object (sum of lines)...
initAsSpecimen()
Initialise an instance with random values.
fetch_line_by_project($projectid, $user='')
fetch_line_by_project
set_paid($id, $fuser, $notrigger=0)
Classify the expense report as paid.
getEntity($element, $shared=1, $forceentity=null)
Get list of entity id to use.
img_object($titlealt, $picto, $moreatt= '', $pictoisfullpath=false, $srconly=0, $notitle=0)
Show a picto called object_picto (generic function)
dol_now($mode='gmt')
Return date for now.
updateline($rowid, $type_fees_id, $projet_id, $vatrate, $comments, $qty, $value_unit, $date, $expensereport_id, $fk_c_exp_tax_cat=0)
updateline
Class to manage Trips and Expenses.
listOfTypes($active=1)
List of types.
vatrate($rate, $addpercent=false, $info_bits=0, $usestarfornpr=0)
Return a string with VAT rate label formated for view output Used into pdf and HTML pages...
calcul_price_total($qty, $pu, $remise_percent_ligne, $txtva, $uselocaltax1_rate, $uselocaltax2_rate, $remise_percent_global, $price_base_type, $info_bits, $type, $seller= '', $localtaxes_array='', $progress=100, $multicurrency_tx=1, $pu_devise=0)
Calculate totals (net, vat, ...) of a line.
Definition: price.lib.php:85
price($amount, $form=0, $outlangs='', $trunc=1, $rounding=-1, $forcerounding=-1, $currency_code='')
Function to format a value into an amount for visual output Function used into PDF and HTML pages...
dol_print_date($time, $format='', $tzoutput='tzserver', $outputlangs='', $encodetooutput=false)
Output date in a string format according to outputlangs (or langs if not defined).
setApproved($fuser, $notrigger=0)
Set status to approved.
hasDelay($option)
Return if an expense report is late or not.
load_state_board()
Charge indicateurs this->nb pour le tableau de bord.
print
Draft customers invoices.
Definition: index.php:91
set_cancel($fuser, $detail, $notrigger=0)
set_cancel
static getAllRule($fk_c_type_fees='', $date='', $fk_user='')
Return all rules or filtered by something.
create($user, $notrigger=0)
Create object in database.
getNomUrl($withpicto=0, $max=0, $short=0, $moretitle='', $notooltip=0, $save_lastsearch_value=-1)
Return clicable name (with picto eventually)
insert($notrigger=0, $fromaddline=false)
insert
update($user, $notrigger=0, $userofexpensereport=null)
update
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
offsetAlreadyGiven()
If the sql find any rows then the ikoffset is already given (ikoffset is applied at the first expense...
call_trigger($trigger_name, $user)
Call trigger based on this instance.
__construct($db)
Constructor.
getLocalTaxesFromRate($vatrate, $local, $buyer, $seller, $firstparamisid=0)
Get type and rate of localtaxes for a particular vat rate/country of a thirdparty.
recalculer($id)
recalculer TODO Replace this with call to update_price if not already done
deleteline($rowid, $fuser='')
deleteline
getLibStatut($mode=0)
Returns the label status.
const STATUS_DRAFT
Draft.
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, ...)
set_save_from_refuse($fuser)
set_save_from_refuse
dolGetFirstLastname($firstname, $lastname, $nameorder=-1)
Return firstname and lastname in correct order.
generateDocument($modele, $outputlangs, $hidedetails=0, $hidedesc=0, $hideref=0)
Create a document onto disk accordign to template module.