dolibarr  17.0.4
loanschedule.class.php
Go to the documentation of this file.
1 <?php
2 /* Copyright (C) 2017 Florian HENRY <florian.henry@atm-consulting.fr>
3  * Copyright (C) 2018 Frédéric France <frederic.france@netlogic.fr>
4  *
5  * This program is free software; you can redistribute it and/or modify
6  * it under the terms of the GNU General Public License as published by
7  * the Free Software Foundation; either version 3 of the License, or
8  * (at your option) any later version.
9  *
10  * This program is distributed in the hope that it will be useful,
11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13  * GNU General Public License for more details.
14  *
15  * You should have received a copy of the GNU General Public License
16  * along with this program. If not, see <https://www.gnu.org/licenses/>.
17  */
18 
25 require_once DOL_DOCUMENT_ROOT.'/core/class/commonobject.class.php';
26 
27 
32 {
36  public $element = 'loan_schedule';
37 
41  public $table_element = 'loan_schedule';
42 
46  public $fk_loan;
47 
51  public $datec;
52  public $tms;
53 
57  public $datep;
58 
59  public $amounts = array(); // Array of amounts
60  public $amount_capital; // Total amount of payment
61  public $amount_insurance;
62  public $amount_interest;
63 
67  public $fk_typepayment;
68 
72  public $num_payment;
73 
77  public $fk_bank;
78 
82  public $fk_payment_loan;
83 
87  public $fk_user_creat;
88 
92  public $fk_user_modif;
93 
94  public $lines = array();
95 
100  public $total;
101 
102  public $type_code;
103  public $type_label;
104 
105 
111  public function __construct($db)
112  {
113  $this->db = $db;
114  }
115 
123  public function create($user)
124  {
125  global $conf, $langs;
126 
127  $error = 0;
128 
129  $now = dol_now();
130 
131  // Validate parameters
132  if (!$this->datep) {
133  $this->error = 'ErrorBadValueForParameter';
134  return -1;
135  }
136 
137  // Clean parameters
138  if (isset($this->fk_loan)) {
139  $this->fk_loan = (int) $this->fk_loan;
140  }
141  if (isset($this->amount_capital)) {
142  $this->amount_capital = trim($this->amount_capital ? $this->amount_capital : 0);
143  }
144  if (isset($this->amount_insurance)) {
145  $this->amount_insurance = trim($this->amount_insurance ? $this->amount_insurance : 0);
146  }
147  if (isset($this->amount_interest)) {
148  $this->amount_interest = trim($this->amount_interest ? $this->amount_interest : 0);
149  }
150  if (isset($this->fk_typepayment)) {
151  $this->fk_typepayment = (int) $this->fk_typepayment;
152  }
153  if (isset($this->fk_bank)) {
154  $this->fk_bank = (int) $this->fk_bank;
155  }
156  if (isset($this->fk_user_creat)) {
157  $this->fk_user_creat = (int) $this->fk_user_creat;
158  }
159  if (isset($this->fk_user_modif)) {
160  $this->fk_user_modif = (int) $this->fk_user_modif;
161  }
162 
163  $totalamount = $this->amount_capital + $this->amount_insurance + $this->amount_interest;
164  $totalamount = price2num($totalamount);
165 
166  // Check parameters
167  if ($totalamount == 0) {
168  $this->errors[] = 'step1';
169  return -1; // Negative amounts are accepted for reject prelevement but not null
170  }
171 
172 
173  $this->db->begin();
174 
175  if ($totalamount != 0) {
176  $sql = "INSERT INTO ".MAIN_DB_PREFIX.$this->table_element." (fk_loan, datec, datep, amount_capital, amount_insurance, amount_interest,";
177  $sql .= " fk_typepayment, fk_user_creat, fk_bank)";
178  $sql .= " VALUES (".$this->fk_loan.", '".$this->db->idate($now)."',";
179  $sql .= " '".$this->db->idate($this->datep)."',";
180  $sql .= " ".price2num($this->amount_capital).",";
181  $sql .= " ".price2num($this->amount_insurance).",";
182  $sql .= " ".price2num($this->amount_interest).",";
183  $sql .= " ".price2num($this->fk_typepayment).", ";
184  $sql .= " ".((int) $user->id).",";
185  $sql .= " ".((int) $this->fk_bank).")";
186 
187  dol_syslog(get_class($this)."::create", LOG_DEBUG);
188  $resql = $this->db->query($sql);
189  if ($resql) {
190  $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX."payment_loan");
191  } else {
192  $this->error = $this->db->lasterror();
193  $error++;
194  }
195  }
196 
197  if ($totalamount != 0 && !$error) {
198  $this->amount_capital = $totalamount;
199  $this->db->commit();
200  return $this->id;
201  } else {
202  $this->errors[] = $this->db->lasterror();
203  $this->db->rollback();
204  return -1;
205  }
206  }
207 
214  public function fetch($id)
215  {
216  global $langs;
217  $sql = "SELECT";
218  $sql .= " t.rowid,";
219  $sql .= " t.fk_loan,";
220  $sql .= " t.datec,";
221  $sql .= " t.tms,";
222  $sql .= " t.datep,";
223  $sql .= " t.amount_capital,";
224  $sql .= " t.amount_insurance,";
225  $sql .= " t.amount_interest,";
226  $sql .= " t.fk_typepayment,";
227  $sql .= " t.num_payment,";
228  $sql .= " t.note_private,";
229  $sql .= " t.note_public,";
230  $sql .= " t.fk_bank,";
231  $sql .= " t.fk_payment_loan,";
232  $sql .= " t.fk_user_creat,";
233  $sql .= " t.fk_user_modif,";
234  $sql .= " pt.code as type_code, pt.libelle as type_label,";
235  $sql .= ' b.fk_account';
236  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element." as t";
237  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_paiement as pt ON t.fk_typepayment = pt.id";
238  $sql .= ' LEFT JOIN '.MAIN_DB_PREFIX.'bank as b ON t.fk_bank = b.rowid';
239  $sql .= " WHERE t.rowid = ".((int) $id);
240 
241  dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
242  $resql = $this->db->query($sql);
243  if ($resql) {
244  if ($this->db->num_rows($resql)) {
245  $obj = $this->db->fetch_object($resql);
246 
247  $this->id = $obj->rowid;
248  $this->ref = $obj->rowid;
249 
250  $this->fk_loan = $obj->fk_loan;
251  $this->datec = $this->db->jdate($obj->datec);
252  $this->tms = $this->db->jdate($obj->tms);
253  $this->datep = $this->db->jdate($obj->datep);
254  $this->amount_capital = $obj->amount_capital;
255  $this->amount_insurance = $obj->amount_insurance;
256  $this->amount_interest = $obj->amount_interest;
257  $this->fk_typepayment = $obj->fk_typepayment;
258  $this->num_payment = $obj->num_payment;
259  $this->note_private = $obj->note_private;
260  $this->note_public = $obj->note_public;
261  $this->fk_bank = $obj->fk_bank;
262  $this->fk_payment_loan = $obj->fk_payment_loan;
263  $this->fk_user_creat = $obj->fk_user_creat;
264  $this->fk_user_modif = $obj->fk_user_modif;
265 
266  $this->type_code = $obj->type_code;
267  $this->type_label = $obj->type_label;
268 
269  $this->bank_account = $obj->fk_account;
270  $this->bank_line = $obj->fk_bank;
271  }
272  $this->db->free($resql);
273 
274  return 1;
275  } else {
276  $this->error = "Error ".$this->db->lasterror();
277  return -1;
278  }
279  }
280 
281 
289  public function update($user = 0, $notrigger = 0)
290  {
291  global $conf, $langs;
292  $error = 0;
293 
294  // Clean parameters
295  if (isset($this->amount_capital)) {
296  $this->amount_capital = trim($this->amount_capital);
297  }
298  if (isset($this->amount_insurance)) {
299  $this->amount_insurance = trim($this->amount_insurance);
300  }
301  if (isset($this->amount_interest)) {
302  $this->amount_interest = trim($this->amount_interest);
303  }
304  if (isset($this->num_payment)) {
305  $this->num_payment = trim($this->num_payment);
306  }
307  if (isset($this->note_private)) {
308  $this->note_private = trim($this->note_private);
309  }
310  if (isset($this->note_public)) {
311  $this->note_public = trim($this->note_public);
312  }
313  if (isset($this->fk_bank)) {
314  $this->fk_bank = trim($this->fk_bank);
315  }
316  if (isset($this->fk_payment_loan)) {
317  $this->fk_payment_loan = (int) $this->fk_payment_loan;
318  }
319 
320  // Check parameters
321  // Put here code to add control on parameters values
322 
323  // Update request
324  $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element." SET";
325 
326  $sql .= " fk_loan=".(isset($this->fk_loan) ? $this->fk_loan : "null").",";
327  $sql .= " datec=".(dol_strlen($this->datec) != 0 ? "'".$this->db->idate($this->datec)."'" : 'null').",";
328  $sql .= " tms=".(dol_strlen($this->tms) != 0 ? "'".$this->db->idate($this->tms)."'" : 'null').",";
329  $sql .= " datep=".(dol_strlen($this->datep) != 0 ? "'".$this->db->idate($this->datep)."'" : 'null').",";
330  $sql .= " amount_capital=".(isset($this->amount_capital) ? $this->amount_capital : "null").",";
331  $sql .= " amount_insurance=".(isset($this->amount_insurance) ? $this->amount_insurance : "null").",";
332  $sql .= " amount_interest=".(isset($this->amount_interest) ? $this->amount_interest : "null").",";
333  $sql .= " fk_typepayment=".(isset($this->fk_typepayment) ? $this->fk_typepayment : "null").",";
334  $sql .= " num_payment=".(isset($this->num_payment) ? "'".$this->db->escape($this->num_payment)."'" : "null").",";
335  $sql .= " note_private=".(isset($this->note_private) ? "'".$this->db->escape($this->note_private)."'" : "null").",";
336  $sql .= " note_public=".(isset($this->note_public) ? "'".$this->db->escape($this->note_public)."'" : "null").",";
337  $sql .= " fk_bank=".(isset($this->fk_bank) ? $this->fk_bank : "null").",";
338  $sql .= " fk_payment_loan=".(isset($this->fk_payment_loan) ? $this->fk_payment_loan : "null").",";
339  $sql .= " fk_user_creat=".(isset($this->fk_user_creat) ? $this->fk_user_creat : "null").",";
340  $sql .= " fk_user_modif=".(isset($this->fk_user_modif) ? $this->fk_user_modif : "null")."";
341 
342  $sql .= " WHERE rowid=".((int) $this->id);
343 
344  $this->db->begin();
345 
346  dol_syslog(get_class($this)."::update", LOG_DEBUG);
347  $resql = $this->db->query($sql);
348  if (!$resql) {
349  $error++; $this->errors[] = "Error ".$this->db->lasterror();
350  }
351 
352  // Commit or rollback
353  if ($error) {
354  $this->db->rollback();
355  return -1 * $error;
356  } else {
357  $this->db->commit();
358  return 1;
359  }
360  }
361 
362 
370  public function delete($user, $notrigger = 0)
371  {
372  global $conf, $langs;
373  $error = 0;
374 
375  $this->db->begin();
376 
377  if (!$error) {
378  $sql = "DELETE FROM ".MAIN_DB_PREFIX.$this->table_element;
379  $sql .= " WHERE rowid=".((int) $this->id);
380 
381  dol_syslog(get_class($this)."::delete", LOG_DEBUG);
382  $resql = $this->db->query($sql);
383  if (!$resql) {
384  $error++; $this->errors[] = "Error ".$this->db->lasterror();
385  }
386  }
387 
388  // Commit or rollback
389  if ($error) {
390  foreach ($this->errors as $errmsg) {
391  dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
392  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
393  }
394  $this->db->rollback();
395  return -1 * $error;
396  } else {
397  $this->db->commit();
398  return 1;
399  }
400  }
401 
410  public function calcMonthlyPayments($capital, $rate, $nbterm)
411  {
412  $result = '';
413 
414  if (!empty($capital) && !empty($rate) && !empty($nbterm)) {
415  $result = ($capital * ($rate / 12)) / (1 - pow((1 + ($rate / 12)), ($nbterm * -1)));
416  }
417 
418  return $result;
419  }
420 
421 
428  public function fetchAll($loanid)
429  {
430  global $langs;
431 
432  $sql = "SELECT";
433  $sql .= " t.rowid,";
434  $sql .= " t.fk_loan,";
435  $sql .= " t.datec,";
436  $sql .= " t.tms,";
437  $sql .= " t.datep,";
438  $sql .= " t.amount_capital,";
439  $sql .= " t.amount_insurance,";
440  $sql .= " t.amount_interest,";
441  $sql .= " t.fk_typepayment,";
442  $sql .= " t.num_payment,";
443  $sql .= " t.note_private,";
444  $sql .= " t.note_public,";
445  $sql .= " t.fk_bank,";
446  $sql .= " t.fk_payment_loan,";
447  $sql .= " t.fk_user_creat,";
448  $sql .= " t.fk_user_modif";
449  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element." as t";
450  $sql .= " WHERE t.fk_loan = ".((int) $loanid);
451 
452  dol_syslog(get_class($this)."::fetchAll", LOG_DEBUG);
453  $resql = $this->db->query($sql);
454 
455  if ($resql) {
456  while ($obj = $this->db->fetch_object($resql)) {
457  $line = new LoanSchedule($this->db);
458  $line->id = $obj->rowid;
459  $line->ref = $obj->rowid;
460 
461  $line->fk_loan = $obj->fk_loan;
462  $line->datec = $this->db->jdate($obj->datec);
463  $line->tms = $this->db->jdate($obj->tms);
464  $line->datep = $this->db->jdate($obj->datep);
465  $line->amount_capital = $obj->amount_capital;
466  $line->amount_insurance = $obj->amount_insurance;
467  $line->amount_interest = $obj->amount_interest;
468  $line->fk_typepayment = $obj->fk_typepayment;
469  $line->num_payment = $obj->num_payment;
470  $line->note_private = $obj->note_private;
471  $line->note_public = $obj->note_public;
472  $line->fk_bank = $obj->fk_bank;
473  $line->fk_payment_loan = $obj->fk_payment_loan;
474  $line->fk_user_creat = $obj->fk_user_creat;
475  $line->fk_user_modif = $obj->fk_user_modif;
476 
477  $this->lines[] = $line;
478  }
479  $this->db->free($resql);
480  return 1;
481  } else {
482  $this->error = "Error ".$this->db->lasterror();
483  return -1;
484  }
485  }
486 
492  private function transPayment()
493  {
494  require_once DOL_DOCUMENT_ROOT.'/loan/class/loan.class.php';
495  require_once DOL_DOCUMENT_ROOT.'/core/lib/loan.lib.php';
496  require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
497 
498  $toinsert = array();
499 
500  $sql = "SELECT l.rowid";
501  $sql .= " FROM ".MAIN_DB_PREFIX."loan as l";
502  $sql .= " WHERE l.paid = 0";
503  $resql = $this->db->query($sql);
504 
505  if ($resql) {
506  while ($obj = $this->db->fetch_object($resql)) {
507  $lastrecorded = $this->lastPayment($obj->rowid);
508  $toinsert = $this->paimenttorecord($obj->rowid, $lastrecorded);
509  if (count($toinsert) > 0) {
510  foreach ($toinsert as $echid) {
511  $this->db->begin();
512  $sql = "INSERT INTO ".MAIN_DB_PREFIX."payment_loan ";
513  $sql .= "(fk_loan,datec,tms,datep,amount_capital,amount_insurance,amount_interest,fk_typepayment,num_payment,note_private,note_public,fk_bank,fk_user_creat,fk_user_modif) ";
514  $sql .= "SELECT fk_loan,datec,tms,datep,amount_capital,amount_insurance,amount_interest,fk_typepayment,num_payment,note_private,note_public,fk_bank,fk_user_creat,fk_user_modif";
515  $sql .= " FROM ".MAIN_DB_PREFIX."loan_schedule WHERE rowid =".((int) $echid);
516  $res = $this->db->query($sql);
517  if ($res) {
518  $this->db->commit();
519  } else {
520  $this->db->rollback();
521  }
522  }
523  }
524  }
525  }
526  }
527 
528 
535  private function lastPayment($loanid)
536  {
537  $sql = "SELECT p.datep";
538  $sql .= " FROM ".MAIN_DB_PREFIX."payment_loan as p ";
539  $sql .= " WHERE p.fk_loan = ".((int) $loanid);
540  $sql .= " ORDER BY p.datep DESC ";
541  $sql .= " LIMIT 1 ";
542 
543  $resql = $this->db->query($sql);
544 
545  if ($resql) {
546  $obj = $this->db->fetch_object($resql);
547  return $this->db->jdate($obj->datep);
548  } else {
549  return -1;
550  }
551  }
552 
560  public function paimenttorecord($loanid, $datemax)
561  {
562 
563  $result = array();
564 
565  $sql = "SELECT p.rowid";
566  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element." as p ";
567  $sql .= " WHERE p.fk_loan = ".((int) $loanid);
568  if (!empty($datemax)) {
569  $sql .= " AND p.datep > '".$this->db->idate($datemax)."'";
570  }
571  $sql .= " AND p.datep <= '".$this->db->idate(dol_now())."'";
572 
573  $resql = $this->db->query($sql);
574 
575  if ($resql) {
576  while ($obj = $this->db->fetch_object($resql)) {
577  $result[] = $obj->rowid;
578  }
579  }
580 
581  return $result;
582  }
583 }
$object ref
Definition: info.php:78
Parent class of all other business classes (invoices, contracts, proposals, orders,...
Class to manage Schedule of loans.
__construct($db)
Constructor.
fetchAll($loanid)
Load all object in memory from database.
calcMonthlyPayments($capital, $rate, $nbterm)
Calculate Monthly Payments.
create($user)
Create payment of loan into database.
fetch($id)
Load object in memory from database.
paimenttorecord($loanid, $datemax)
paimenttorecord
lastPayment($loanid)
lastpayment
update($user=0, $notrigger=0)
Update database.
transPayment()
transPayment
if(isModEnabled('facture') &&!empty($user->rights->facture->lire)) if((isModEnabled('fournisseur') &&empty($conf->global->MAIN_USE_NEW_SUPPLIERMOD) && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') &&!empty($user->rights->don->lire)) if(isModEnabled('tax') &&!empty($user->rights->tax->charges->lire)) if(isModEnabled('facture') &&isModEnabled('commande') && $user->hasRight("commande", "lire") &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) $resql
Social contributions to pay.
Definition: index.php:745
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
dol_now($mode='auto')
Return date for now.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
$conf db
API class for accounts.
Definition: inc.php:41