dolibarr  20.0.0-alpha
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-2024 Frédéric France <frederic.france@free.fr>
4  * Copyright (C) 2024 MDW <mdeweerd@users.noreply.github.com>
5  *
6  * This program is free software; you can redistribute it and/or modify
7  * it under the terms of the GNU General Public License as published by
8  * the Free Software Foundation; either version 3 of the License, or
9  * (at your option) any later version.
10  *
11  * This program is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program. If not, see <https://www.gnu.org/licenses/>.
18  */
19 
26 require_once DOL_DOCUMENT_ROOT.'/core/class/commonobject.class.php';
27 
28 
33 {
37  public $element = 'loan_schedule';
38 
42  public $table_element = 'loan_schedule';
43 
47  public $fk_loan;
48 
49  public $bank_account;
50  public $bank_line;
51 
55  public $datec;
56 
60  public $datep;
61 
62  public $amounts = array(); // Array of amounts
63  public $amount_capital; // Total amount of payment
64  public $amount_insurance;
65  public $amount_interest;
66 
70  public $fk_typepayment;
71 
76  public $num_payment;
77 
81  public $fk_bank;
82 
86  public $fk_payment_loan;
87 
91  public $fk_user_creat;
92 
96  public $fk_user_modif;
97 
102  public $lines = array();
103 
108  public $total;
109 
110  public $type_code;
111  public $type_label;
112 
113 
119  public function __construct($db)
120  {
121  $this->db = $db;
122  }
123 
131  public function create($user)
132  {
133  global $conf, $langs;
134 
135  $error = 0;
136 
137  $now = dol_now();
138 
139  // Validate parameters
140  if (!$this->datep) {
141  $this->error = 'ErrorBadValueForParameter';
142  return -1;
143  }
144 
145  // Clean parameters
146  if (isset($this->fk_loan)) {
147  $this->fk_loan = (int) $this->fk_loan;
148  }
149  if (isset($this->amount_capital)) {
150  $this->amount_capital = trim($this->amount_capital ? $this->amount_capital : 0);
151  }
152  if (isset($this->amount_insurance)) {
153  $this->amount_insurance = trim($this->amount_insurance ? $this->amount_insurance : 0);
154  }
155  if (isset($this->amount_interest)) {
156  $this->amount_interest = trim($this->amount_interest ? $this->amount_interest : 0);
157  }
158  if (isset($this->fk_typepayment)) {
159  $this->fk_typepayment = (int) $this->fk_typepayment;
160  }
161  if (isset($this->fk_bank)) {
162  $this->fk_bank = (int) $this->fk_bank;
163  }
164  if (isset($this->fk_user_creat)) {
165  $this->fk_user_creat = (int) $this->fk_user_creat;
166  }
167  if (isset($this->fk_user_modif)) {
168  $this->fk_user_modif = (int) $this->fk_user_modif;
169  }
170 
171  $totalamount = (float) $this->amount_capital + (float) $this->amount_insurance + (float) $this->amount_interest;
172  $totalamount = price2num($totalamount);
173 
174  // Check parameters
175  if ($totalamount == 0) {
176  $this->errors[] = 'step1';
177  return -1; // Negative amounts are accepted for reject prelevement but not null
178  }
179 
180 
181  $this->db->begin();
182 
183  if ($totalamount != 0) {
184  $sql = "INSERT INTO ".MAIN_DB_PREFIX.$this->table_element." (fk_loan, datec, datep, amount_capital, amount_insurance, amount_interest,";
185  $sql .= " fk_typepayment, fk_user_creat, fk_bank)";
186  $sql .= " VALUES (".$this->fk_loan.", '".$this->db->idate($now)."',";
187  $sql .= " '".$this->db->idate($this->datep)."',";
188  $sql .= " ".price2num($this->amount_capital).",";
189  $sql .= " ".price2num($this->amount_insurance).",";
190  $sql .= " ".price2num($this->amount_interest).",";
191  $sql .= " ".price2num($this->fk_typepayment).", ";
192  $sql .= " ".((int) $user->id).",";
193  $sql .= " ".((int) $this->fk_bank).")";
194 
195  dol_syslog(get_class($this)."::create", LOG_DEBUG);
196  $resql = $this->db->query($sql);
197  if ($resql) {
198  $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX."payment_loan");
199  } else {
200  $this->error = $this->db->lasterror();
201  $error++;
202  }
203  }
204 
205  if ($totalamount != 0 && !$error) {
206  $this->amount_capital = $totalamount;
207  $this->db->commit();
208  return $this->id;
209  } else {
210  $this->errors[] = $this->db->lasterror();
211  $this->db->rollback();
212  return -1;
213  }
214  }
215 
222  public function fetch($id)
223  {
224  global $langs;
225  $sql = "SELECT";
226  $sql .= " t.rowid,";
227  $sql .= " t.fk_loan,";
228  $sql .= " t.datec,";
229  $sql .= " t.tms,";
230  $sql .= " t.datep,";
231  $sql .= " t.amount_capital,";
232  $sql .= " t.amount_insurance,";
233  $sql .= " t.amount_interest,";
234  $sql .= " t.fk_typepayment,";
235  $sql .= " t.num_payment,";
236  $sql .= " t.note_private,";
237  $sql .= " t.note_public,";
238  $sql .= " t.fk_bank,";
239  $sql .= " t.fk_payment_loan,";
240  $sql .= " t.fk_user_creat,";
241  $sql .= " t.fk_user_modif,";
242  $sql .= " pt.code as type_code, pt.libelle as type_label,";
243  $sql .= ' b.fk_account';
244  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element." as t";
245  $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_paiement as pt ON t.fk_typepayment = pt.id";
246  $sql .= ' LEFT JOIN '.MAIN_DB_PREFIX.'bank as b ON t.fk_bank = b.rowid';
247  $sql .= " WHERE t.rowid = ".((int) $id);
248 
249  dol_syslog(get_class($this)."::fetch", LOG_DEBUG);
250  $resql = $this->db->query($sql);
251  if ($resql) {
252  if ($this->db->num_rows($resql)) {
253  $obj = $this->db->fetch_object($resql);
254 
255  $this->id = $obj->rowid;
256  $this->ref = $obj->rowid;
257 
258  $this->fk_loan = $obj->fk_loan;
259  $this->datec = $this->db->jdate($obj->datec);
260  $this->tms = $this->db->jdate($obj->tms);
261  $this->datep = $this->db->jdate($obj->datep);
262  $this->amount_capital = $obj->amount_capital;
263  $this->amount_insurance = $obj->amount_insurance;
264  $this->amount_interest = $obj->amount_interest;
265  $this->fk_typepayment = $obj->fk_typepayment;
266  $this->num_payment = $obj->num_payment;
267  $this->note_private = $obj->note_private;
268  $this->note_public = $obj->note_public;
269  $this->fk_bank = $obj->fk_bank;
270  $this->fk_payment_loan = $obj->fk_payment_loan;
271  $this->fk_user_creat = $obj->fk_user_creat;
272  $this->fk_user_modif = $obj->fk_user_modif;
273 
274  $this->type_code = $obj->type_code;
275  $this->type_label = $obj->type_label;
276 
277  $this->bank_account = $obj->fk_account;
278  $this->bank_line = $obj->fk_bank;
279  }
280  $this->db->free($resql);
281 
282  return 1;
283  } else {
284  $this->error = "Error ".$this->db->lasterror();
285  return -1;
286  }
287  }
288 
289 
297  public function update($user = null, $notrigger = 0)
298  {
299  global $conf, $langs;
300  $error = 0;
301 
302  // Clean parameters
303  if (isset($this->amount_capital)) {
304  $this->amount_capital = trim($this->amount_capital);
305  }
306  if (isset($this->amount_insurance)) {
307  $this->amount_insurance = trim($this->amount_insurance);
308  }
309  if (isset($this->amount_interest)) {
310  $this->amount_interest = trim($this->amount_interest);
311  }
312  if (isset($this->num_payment)) {
313  $this->num_payment = trim($this->num_payment);
314  }
315  if (isset($this->note_private)) {
316  $this->note_private = trim($this->note_private);
317  }
318  if (isset($this->note_public)) {
319  $this->note_public = trim($this->note_public);
320  }
321  if (isset($this->fk_bank)) {
322  $this->fk_bank = (int) $this->fk_bank;
323  }
324  if (isset($this->fk_payment_loan)) {
325  $this->fk_payment_loan = (int) $this->fk_payment_loan;
326  }
327 
328  // Check parameters
329  // Put here code to add control on parameters values
330 
331  // Update request
332  $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element." SET";
333 
334  $sql .= " fk_loan=".(isset($this->fk_loan) ? $this->fk_loan : "null").",";
335  $sql .= " datec=".(dol_strlen($this->datec) != 0 ? "'".$this->db->idate($this->datec)."'" : 'null').",";
336  $sql .= " tms=".(dol_strlen($this->tms) != 0 ? "'".$this->db->idate($this->tms)."'" : 'null').",";
337  $sql .= " datep=".(dol_strlen($this->datep) != 0 ? "'".$this->db->idate($this->datep)."'" : 'null').",";
338  $sql .= " amount_capital=".(isset($this->amount_capital) ? $this->amount_capital : "null").",";
339  $sql .= " amount_insurance=".(isset($this->amount_insurance) ? $this->amount_insurance : "null").",";
340  $sql .= " amount_interest=".(isset($this->amount_interest) ? $this->amount_interest : "null").",";
341  $sql .= " fk_typepayment=".(isset($this->fk_typepayment) ? $this->fk_typepayment : "null").",";
342  $sql .= " num_payment=".(isset($this->num_payment) ? "'".$this->db->escape($this->num_payment)."'" : "null").",";
343  $sql .= " note_private=".(isset($this->note_private) ? "'".$this->db->escape($this->note_private)."'" : "null").",";
344  $sql .= " note_public=".(isset($this->note_public) ? "'".$this->db->escape($this->note_public)."'" : "null").",";
345  $sql .= " fk_bank=".(isset($this->fk_bank) ? ((int) $this->fk_bank) : "null").",";
346  $sql .= " fk_payment_loan=".(isset($this->fk_payment_loan) ? ((int) $this->fk_payment_loan) : "null").",";
347  $sql .= " fk_user_creat=".(isset($this->fk_user_creat) ? ((int) $this->fk_user_creat) : "null").",";
348  $sql .= " fk_user_modif=".(isset($this->fk_user_modif) ? ((int) $this->fk_user_modif) : "null");
349 
350  $sql .= " WHERE rowid=".((int) $this->id);
351 
352  $this->db->begin();
353 
354  dol_syslog(get_class($this)."::update", LOG_DEBUG);
355  $resql = $this->db->query($sql);
356  if (!$resql) {
357  $error++;
358  $this->errors[] = "Error ".$this->db->lasterror();
359  }
360 
361  // Commit or rollback
362  if ($error) {
363  $this->db->rollback();
364  return -1 * $error;
365  } else {
366  $this->db->commit();
367  return 1;
368  }
369  }
370 
371 
379  public function delete($user, $notrigger = 0)
380  {
381  global $conf, $langs;
382  $error = 0;
383 
384  $this->db->begin();
385 
386  if (!$error) {
387  $sql = "DELETE FROM ".MAIN_DB_PREFIX.$this->table_element;
388  $sql .= " WHERE rowid=".((int) $this->id);
389 
390  dol_syslog(get_class($this)."::delete", LOG_DEBUG);
391  $resql = $this->db->query($sql);
392  if (!$resql) {
393  $error++;
394  $this->errors[] = "Error ".$this->db->lasterror();
395  }
396  }
397 
398  // Commit or rollback
399  if ($error) {
400  foreach ($this->errors as $errmsg) {
401  dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR);
402  $this->error .= ($this->error ? ', '.$errmsg : $errmsg);
403  }
404  $this->db->rollback();
405  return -1 * $error;
406  } else {
407  $this->db->commit();
408  return 1;
409  }
410  }
411 
420  public function calcMonthlyPayments($capital, $rate, $nbterm)
421  {
422  $result = '';
423 
424  if (!empty($capital) && !empty($rate) && !empty($nbterm)) {
425  $result = ($capital * ($rate / 12)) / (1 - pow((1 + ($rate / 12)), ($nbterm * -1)));
426  }
427 
428  return $result;
429  }
430 
431 
438  public function fetchAll($loanid)
439  {
440  $sql = "SELECT";
441  $sql .= " t.rowid,";
442  $sql .= " t.fk_loan,";
443  $sql .= " t.datec,";
444  $sql .= " t.tms,";
445  $sql .= " t.datep,";
446  $sql .= " t.amount_capital,";
447  $sql .= " t.amount_insurance,";
448  $sql .= " t.amount_interest,";
449  $sql .= " t.fk_typepayment,";
450  $sql .= " t.num_payment,";
451  $sql .= " t.note_private,";
452  $sql .= " t.note_public,";
453  $sql .= " t.fk_bank,";
454  $sql .= " t.fk_payment_loan,";
455  $sql .= " t.fk_user_creat,";
456  $sql .= " t.fk_user_modif";
457  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element." as t";
458  $sql .= " WHERE t.fk_loan = ".((int) $loanid);
459 
460  dol_syslog(get_class($this)."::fetchAll", LOG_DEBUG);
461  $resql = $this->db->query($sql);
462 
463  if ($resql) {
464  while ($obj = $this->db->fetch_object($resql)) {
465  $line = new LoanSchedule($this->db);
466  $line->id = $obj->rowid;
467  $line->ref = $obj->rowid;
468 
469  $line->fk_loan = $obj->fk_loan;
470  $line->datec = $this->db->jdate($obj->datec);
471  $line->tms = $this->db->jdate($obj->tms);
472  $line->datep = $this->db->jdate($obj->datep);
473  $line->amount_capital = $obj->amount_capital;
474  $line->amount_insurance = $obj->amount_insurance;
475  $line->amount_interest = $obj->amount_interest;
476  $line->fk_typepayment = $obj->fk_typepayment;
477  $line->num_payment = $obj->num_payment;
478  $line->note_private = $obj->note_private;
479  $line->note_public = $obj->note_public;
480  $line->fk_bank = $obj->fk_bank;
481  $line->fk_payment_loan = $obj->fk_payment_loan;
482  $line->fk_user_creat = $obj->fk_user_creat;
483  $line->fk_user_modif = $obj->fk_user_modif;
484 
485  $this->lines[] = $line;
486  }
487  $this->db->free($resql);
488  return 1;
489  } else {
490  $this->error = "Error ".$this->db->lasterror();
491  return -1;
492  }
493  }
494 
500  private function transPayment()
501  {
502  require_once DOL_DOCUMENT_ROOT.'/loan/class/loan.class.php';
503  require_once DOL_DOCUMENT_ROOT.'/core/lib/loan.lib.php';
504  require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
505 
506  $toinsert = array();
507 
508  $sql = "SELECT l.rowid";
509  $sql .= " FROM ".MAIN_DB_PREFIX."loan as l";
510  $sql .= " WHERE l.paid = 0";
511  $resql = $this->db->query($sql);
512 
513  if ($resql) {
514  while ($obj = $this->db->fetch_object($resql)) {
515  $lastrecorded = $this->lastPayment($obj->rowid);
516  $toinsert = $this->paimenttorecord($obj->rowid, $lastrecorded);
517  if (count($toinsert) > 0) {
518  foreach ($toinsert as $echid) {
519  $this->db->begin();
520  $sql = "INSERT INTO ".MAIN_DB_PREFIX."payment_loan ";
521  $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) ";
522  $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";
523  $sql .= " FROM ".MAIN_DB_PREFIX."loan_schedule WHERE rowid =".((int) $echid);
524  $res = $this->db->query($sql);
525  if ($res) {
526  $this->db->commit();
527  } else {
528  $this->db->rollback();
529  }
530  }
531  }
532  }
533  }
534  }
535 
536 
543  private function lastPayment($loanid)
544  {
545  $sql = "SELECT p.datep";
546  $sql .= " FROM ".MAIN_DB_PREFIX."payment_loan as p ";
547  $sql .= " WHERE p.fk_loan = ".((int) $loanid);
548  $sql .= " ORDER BY p.datep DESC ";
549  $sql .= " LIMIT 1 ";
550 
551  $resql = $this->db->query($sql);
552 
553  if ($resql) {
554  $obj = $this->db->fetch_object($resql);
555  return $this->db->jdate($obj->datep);
556  } else {
557  return -1;
558  }
559  }
560 
568  public function paimenttorecord($loanid, $datemax)
569  {
570  $result = array();
571 
572  $sql = "SELECT p.rowid";
573  $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element." as p ";
574  $sql .= " WHERE p.fk_loan = ".((int) $loanid);
575  if (!empty($datemax)) {
576  $sql .= " AND p.datep > '".$this->db->idate($datemax)."'";
577  }
578  $sql .= " AND p.datep <= '".$this->db->idate(dol_now())."'";
579 
580  $resql = $this->db->query($sql);
581 
582  if ($resql) {
583  while ($obj = $this->db->fetch_object($resql)) {
584  $result[] = $obj->rowid;
585  }
586  }
587 
588  return $result;
589  }
590 }
$object ref
Definition: info.php:79
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
update($user=null, $notrigger=0)
Update database.
lastPayment($loanid)
lastpayment
transPayment()
transPayment
if(isModEnabled('invoice') && $user->hasRight('facture', 'lire')) if((isModEnabled('fournisseur') &&!getDolGlobalString('MAIN_USE_NEW_SUPPLIERMOD') && $user->hasRight("fournisseur", "facture", "lire"))||(isModEnabled('supplier_invoice') && $user->hasRight("supplier_invoice", "lire"))) if(isModEnabled('don') && $user->hasRight('don', 'lire')) if(isModEnabled('tax') && $user->hasRight('tax', 'charges', 'lire')) if(isModEnabled('invoice') &&isModEnabled('order') && $user->hasRight("commande", "lire") &&!getDolGlobalString('WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER')) $sql
Social contributions to pay.
Definition: index.php:744
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.
div float
Buy price without taxes.
Definition: style.css.php:959