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 19/** 20 * \file htdocs/loan/class/loanschedule.class.php 21 * \ingroup loan 22 * \brief File of class to manage schedule of loans 23 */ 24 25require_once DOL_DOCUMENT_ROOT.'/core/class/commonobject.class.php'; 26 27 28/** 29 * Class to manage Schedule of loans 30 */ 31class LoanSchedule extends CommonObject 32{ 33 /** 34 * @var string ID to identify managed object 35 */ 36 public $element = 'loan_schedule'; 37 38 /** 39 * @var string Name of table without prefix where object is stored 40 */ 41 public $table_element = 'loan_schedule'; 42 43 /** 44 * @var int Loan ID 45 */ 46 public $fk_loan; 47 48 /** 49 * @var string Create date 50 */ 51 public $datec; 52 public $tms; 53 54 /** 55 * @var string Payment date 56 */ 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 64 /** 65 * @var int Payment Type ID 66 */ 67 public $fk_typepayment; 68 69 /** 70 * @var int Payment ID 71 */ 72 public $num_payment; 73 74 /** 75 * @var int Bank ID 76 */ 77 public $fk_bank; 78 79 /** 80 * @var int Loan Payment ID 81 */ 82 public $fk_payment_loan; 83 84 /** 85 * @var int Bank ID 86 */ 87 public $fk_user_creat; 88 89 /** 90 * @var int User ID 91 */ 92 public $fk_user_modif; 93 94 public $lines = array(); 95 96 /** 97 * @deprecated 98 * @see $amount, $amounts 99 */ 100 public $total; 101 102 public $type_code; 103 public $type_label; 104 105 106 /** 107 * Constructor 108 * 109 * @param DoliDB $db Database handler 110 */ 111 public function __construct($db) 112 { 113 $this->db = $db; 114 } 115 116 /** 117 * Create payment of loan into database. 118 * Use this->amounts to have list of lines for the payment 119 * 120 * @param User $user User making payment 121 * @return int <0 if KO, id of payment if OK 122 */ 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 { 134 $this->error = 'ErrorBadValueForParameter'; 135 return -1; 136 } 137 138 // Clean parameters 139 if (isset($this->fk_loan)) $this->fk_loan = (int) $this->fk_loan; 140 if (isset($this->amount_capital)) $this->amount_capital = trim($this->amount_capital ? $this->amount_capital : 0); 141 if (isset($this->amount_insurance)) $this->amount_insurance = trim($this->amount_insurance ? $this->amount_insurance : 0); 142 if (isset($this->amount_interest)) $this->amount_interest = trim($this->amount_interest ? $this->amount_interest : 0); 143 if (isset($this->fk_typepayment)) $this->fk_typepayment = (int) $this->fk_typepayment; 144 if (isset($this->fk_bank)) $this->fk_bank = (int) $this->fk_bank; 145 if (isset($this->fk_user_creat)) $this->fk_user_creat = (int) $this->fk_user_creat; 146 if (isset($this->fk_user_modif)) $this->fk_user_modif = (int) $this->fk_user_modif; 147 148 $totalamount = $this->amount_capital + $this->amount_insurance + $this->amount_interest; 149 $totalamount = price2num($totalamount); 150 151 // Check parameters 152 if ($totalamount == 0) { 153 $this->errors[] = 'step1'; 154 return -1; // Negative amounts are accepted for reject prelevement but not null 155 } 156 157 158 $this->db->begin(); 159 160 if ($totalamount != 0) 161 { 162 $sql = "INSERT INTO ".MAIN_DB_PREFIX.$this->table_element." (fk_loan, datec, datep, amount_capital, amount_insurance, amount_interest,"; 163 $sql .= " fk_typepayment, fk_user_creat, fk_bank)"; 164 $sql .= " VALUES (".$this->fk_loan.", '".$this->db->idate($now)."',"; 165 $sql .= " '".$this->db->idate($this->datep)."',"; 166 $sql .= " ".$this->amount_capital.","; 167 $sql .= " ".$this->amount_insurance.","; 168 $sql .= " ".$this->amount_interest.","; 169 $sql .= " ".$this->fk_typepayment.", "; 170 $sql .= " ".$user->id.","; 171 $sql .= " ".$this->fk_bank.")"; 172 173 dol_syslog(get_class($this)."::create", LOG_DEBUG); 174 $resql = $this->db->query($sql); 175 if ($resql) 176 { 177 $this->id = $this->db->last_insert_id(MAIN_DB_PREFIX."payment_loan"); 178 } else { 179 $this->error = $this->db->lasterror(); 180 $error++; 181 } 182 } 183 184 if ($totalamount != 0 && !$error) 185 { 186 $this->amount_capital = $totalamount; 187 $this->db->commit(); 188 return $this->id; 189 } else { 190 $this->errors[] = $this->db->lasterror(); 191 $this->db->rollback(); 192 return -1; 193 } 194 } 195 196 /** 197 * Load object in memory from database 198 * 199 * @param int $id Id object 200 * @return int <0 if KO, >0 if OK 201 */ 202 public function fetch($id) 203 { 204 global $langs; 205 $sql = "SELECT"; 206 $sql .= " t.rowid,"; 207 $sql .= " t.fk_loan,"; 208 $sql .= " t.datec,"; 209 $sql .= " t.tms,"; 210 $sql .= " t.datep,"; 211 $sql .= " t.amount_capital,"; 212 $sql .= " t.amount_insurance,"; 213 $sql .= " t.amount_interest,"; 214 $sql .= " t.fk_typepayment,"; 215 $sql .= " t.num_payment,"; 216 $sql .= " t.note_private,"; 217 $sql .= " t.note_public,"; 218 $sql .= " t.fk_bank,"; 219 $sql .= " t.fk_payment_loan,"; 220 $sql .= " t.fk_user_creat,"; 221 $sql .= " t.fk_user_modif,"; 222 $sql .= " pt.code as type_code, pt.libelle as type_label,"; 223 $sql .= ' b.fk_account'; 224 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element." as t"; 225 $sql .= " LEFT JOIN ".MAIN_DB_PREFIX."c_paiement as pt ON t.fk_typepayment = pt.id"; 226 $sql .= ' LEFT JOIN '.MAIN_DB_PREFIX.'bank as b ON t.fk_bank = b.rowid'; 227 $sql .= " WHERE t.rowid = ".$id; 228 229 dol_syslog(get_class($this)."::fetch", LOG_DEBUG); 230 $resql = $this->db->query($sql); 231 if ($resql) { 232 if ($this->db->num_rows($resql)) { 233 $obj = $this->db->fetch_object($resql); 234 235 $this->id = $obj->rowid; 236 $this->ref = $obj->rowid; 237 238 $this->fk_loan = $obj->fk_loan; 239 $this->datec = $this->db->jdate($obj->datec); 240 $this->tms = $this->db->jdate($obj->tms); 241 $this->datep = $this->db->jdate($obj->datep); 242 $this->amount_capital = $obj->amount_capital; 243 $this->amount_insurance = $obj->amount_insurance; 244 $this->amount_interest = $obj->amount_interest; 245 $this->fk_typepayment = $obj->fk_typepayment; 246 $this->num_payment = $obj->num_payment; 247 $this->note_private = $obj->note_private; 248 $this->note_public = $obj->note_public; 249 $this->fk_bank = $obj->fk_bank; 250 $this->fk_payment_loan = $obj->fk_payment_loan; 251 $this->fk_user_creat = $obj->fk_user_creat; 252 $this->fk_user_modif = $obj->fk_user_modif; 253 254 $this->type_code = $obj->type_code; 255 $this->type_label = $obj->type_label; 256 257 $this->bank_account = $obj->fk_account; 258 $this->bank_line = $obj->fk_bank; 259 } 260 $this->db->free($resql); 261 262 return 1; 263 } else { 264 $this->error = "Error ".$this->db->lasterror(); 265 return -1; 266 } 267 } 268 269 270 /** 271 * Update database 272 * 273 * @param User $user User that modify 274 * @param int $notrigger 0=launch triggers after, 1=disable triggers 275 * @return int <0 if KO, >0 if OK 276 */ 277 public function update($user = 0, $notrigger = 0) 278 { 279 global $conf, $langs; 280 $error = 0; 281 282 // Clean parameters 283 if (isset($this->amount_capital)) $this->amount_capital = trim($this->amount_capital); 284 if (isset($this->amount_insurance)) $this->amount_insurance = trim($this->amount_insurance); 285 if (isset($this->amount_interest)) $this->amount_interest = trim($this->amount_interest); 286 if (isset($this->num_payment)) $this->num_payment = trim($this->num_payment); 287 if (isset($this->note_private)) $this->note_private = trim($this->note_private); 288 if (isset($this->note_public)) $this->note_public = trim($this->note_public); 289 if (isset($this->fk_bank)) $this->fk_bank = trim($this->fk_bank); 290 if (isset($this->fk_payment_loan)) $this->fk_payment_loan = (int) $this->fk_payment_loan; 291 292 // Check parameters 293 // Put here code to add control on parameters values 294 295 // Update request 296 $sql = "UPDATE ".MAIN_DB_PREFIX.$this->table_element." SET"; 297 298 $sql .= " fk_loan=".(isset($this->fk_loan) ? $this->fk_loan : "null").","; 299 $sql .= " datec=".(dol_strlen($this->datec) != 0 ? "'".$this->db->idate($this->datec)."'" : 'null').","; 300 $sql .= " tms=".(dol_strlen($this->tms) != 0 ? "'".$this->db->idate($this->tms)."'" : 'null').","; 301 $sql .= " datep=".(dol_strlen($this->datep) != 0 ? "'".$this->db->idate($this->datep)."'" : 'null').","; 302 $sql .= " amount_capital=".(isset($this->amount_capital) ? $this->amount_capital : "null").","; 303 $sql .= " amount_insurance=".(isset($this->amount_insurance) ? $this->amount_insurance : "null").","; 304 $sql .= " amount_interest=".(isset($this->amount_interest) ? $this->amount_interest : "null").","; 305 $sql .= " fk_typepayment=".(isset($this->fk_typepayment) ? $this->fk_typepayment : "null").","; 306 $sql .= " num_payment=".(isset($this->num_payment) ? "'".$this->db->escape($this->num_payment)."'" : "null").","; 307 $sql .= " note_private=".(isset($this->note_private) ? "'".$this->db->escape($this->note_private)."'" : "null").","; 308 $sql .= " note_public=".(isset($this->note_public) ? "'".$this->db->escape($this->note_public)."'" : "null").","; 309 $sql .= " fk_bank=".(isset($this->fk_bank) ? $this->fk_bank : "null").","; 310 $sql .= " fk_payment_loan=".(isset($this->fk_payment_loan) ? $this->fk_payment_loan : "null").","; 311 $sql .= " fk_user_creat=".(isset($this->fk_user_creat) ? $this->fk_user_creat : "null").","; 312 $sql .= " fk_user_modif=".(isset($this->fk_user_modif) ? $this->fk_user_modif : "null").""; 313 314 $sql .= " WHERE rowid=".$this->id; 315 316 $this->db->begin(); 317 318 dol_syslog(get_class($this)."::update", LOG_DEBUG); 319 $resql = $this->db->query($sql); 320 if (!$resql) { $error++; $this->errors[] = "Error ".$this->db->lasterror(); } 321 322 // Commit or rollback 323 if ($error) 324 { 325 $this->db->rollback(); 326 return -1 * $error; 327 } else { 328 $this->db->commit(); 329 return 1; 330 } 331 } 332 333 334 /** 335 * Delete object in database 336 * 337 * @param User $user User that delete 338 * @param int $notrigger 0=launch triggers after, 1=disable triggers 339 * @return int <0 if KO, >0 if OK 340 */ 341 public function delete($user, $notrigger = 0) 342 { 343 global $conf, $langs; 344 $error = 0; 345 346 $this->db->begin(); 347 348 if (!$error) { 349 $sql = "DELETE FROM ".MAIN_DB_PREFIX.$this->table_element; 350 $sql .= " WHERE rowid=".$this->id; 351 352 dol_syslog(get_class($this)."::delete", LOG_DEBUG); 353 $resql = $this->db->query($sql); 354 if (!$resql) { $error++; $this->errors[] = "Error ".$this->db->lasterror(); } 355 } 356 357 // Commit or rollback 358 if ($error) 359 { 360 foreach ($this->errors as $errmsg) 361 { 362 dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR); 363 $this->error .= ($this->error ? ', '.$errmsg : $errmsg); 364 } 365 $this->db->rollback(); 366 return -1 * $error; 367 } else { 368 $this->db->commit(); 369 return 1; 370 } 371 } 372 373 /** 374 * Calculate Monthly Payments 375 * 376 * @param double $capital Capital 377 * @param double $rate rate 378 * @param int $nbterm nb term 379 * @return double mensuality 380 */ 381 public function calcMonthlyPayments($capital, $rate, $nbterm) 382 { 383 $result = ''; 384 385 if (!empty($capital) && !empty($rate) && !empty($nbterm)) { 386 $result = ($capital * ($rate / 12)) / (1 - pow((1 + ($rate / 12)), ($nbterm * -1))); 387 } 388 389 return $result; 390 } 391 392 393 /** 394 * Load all object in memory from database 395 * 396 * @param int $loanid Id object 397 * @return int <0 if KO, >0 if OK 398 */ 399 public function fetchAll($loanid) 400 { 401 global $langs; 402 403 $sql = "SELECT"; 404 $sql .= " t.rowid,"; 405 $sql .= " t.fk_loan,"; 406 $sql .= " t.datec,"; 407 $sql .= " t.tms,"; 408 $sql .= " t.datep,"; 409 $sql .= " t.amount_capital,"; 410 $sql .= " t.amount_insurance,"; 411 $sql .= " t.amount_interest,"; 412 $sql .= " t.fk_typepayment,"; 413 $sql .= " t.num_payment,"; 414 $sql .= " t.note_private,"; 415 $sql .= " t.note_public,"; 416 $sql .= " t.fk_bank,"; 417 $sql .= " t.fk_payment_loan,"; 418 $sql .= " t.fk_user_creat,"; 419 $sql .= " t.fk_user_modif"; 420 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element." as t"; 421 $sql .= " WHERE t.fk_loan = ".$loanid; 422 423 dol_syslog(get_class($this)."::fetchAll", LOG_DEBUG); 424 $resql = $this->db->query($sql); 425 426 if ($resql) 427 { 428 while ($obj = $this->db->fetch_object($resql)) 429 { 430 $line = new LoanSchedule($this->db); 431 $line->id = $obj->rowid; 432 $line->ref = $obj->rowid; 433 434 $line->fk_loan = $obj->fk_loan; 435 $line->datec = $this->db->jdate($obj->datec); 436 $line->tms = $this->db->jdate($obj->tms); 437 $line->datep = $this->db->jdate($obj->datep); 438 $line->amount_capital = $obj->amount_capital; 439 $line->amount_insurance = $obj->amount_insurance; 440 $line->amount_interest = $obj->amount_interest; 441 $line->fk_typepayment = $obj->fk_typepayment; 442 $line->num_payment = $obj->num_payment; 443 $line->note_private = $obj->note_private; 444 $line->note_public = $obj->note_public; 445 $line->fk_bank = $obj->fk_bank; 446 $line->fk_payment_loan = $obj->fk_payment_loan; 447 $line->fk_user_creat = $obj->fk_user_creat; 448 $line->fk_user_modif = $obj->fk_user_modif; 449 450 $this->lines[] = $line; 451 } 452 $this->db->free($resql); 453 return 1; 454 } else { 455 $this->error = "Error ".$this->db->lasterror(); 456 return -1; 457 } 458 } 459 460 /** 461 * transPayment 462 * 463 * @return void 464 */ 465 private function transPayment() 466 { 467 require_once DOL_DOCUMENT_ROOT.'/loan/class/loan.class.php'; 468 require_once DOL_DOCUMENT_ROOT.'/core/lib/loan.lib.php'; 469 require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php'; 470 471 $toinsert = array(); 472 473 $sql = "SELECT l.rowid"; 474 $sql .= " FROM ".MAIN_DB_PREFIX."loan as l "; 475 $sql .= " WHERE l.paid = 0"; 476 $resql = $this->db->query($sql); 477 478 if ($resql) { 479 while ($obj = $this->db->fetch_object($resql)) { 480 $lastrecorded = $this->lastPayment($obj->rowid); 481 $toinsert = $this->paimenttorecord($obj->rowid, $lastrecorded); 482 if (count($toinsert) > 0) { 483 foreach ($toinsert as $echid) { 484 $this->db->begin(); 485 $sql = "INSERT INTO ".MAIN_DB_PREFIX."payment_loan "; 486 $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) "; 487 $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 FROM ".MAIN_DB_PREFIX."loan_schedule WHERE rowid =".$echid; 488 $res = $this->db->query($sql); 489 if ($res) { 490 $this->db->commit(); 491 } else { 492 $this->db->rollback(); 493 } 494 } 495 } 496 } 497 } 498 } 499 500 501 /** 502 * lastpayment 503 * 504 * @param int $loanid Loan id 505 * @return int < 0 if KO, Date > 0 if OK 506 */ 507 private function lastPayment($loanid) 508 { 509 $sql = "SELECT p.datep"; 510 $sql .= " FROM ".MAIN_DB_PREFIX."payment_loan as p "; 511 $sql .= " WHERE p.fk_loan = ".$loanid; 512 $sql .= " ORDER BY p.datep DESC "; 513 $sql .= " LIMIT 1 "; 514 515 $resql = $this->db->query($sql); 516 517 if ($resql) { 518 $obj = $this->db->fetch_object($resql); 519 return $this->db->jdate($obj->datep); 520 } else { 521 return -1; 522 } 523 } 524 525 /** 526 * paimenttorecord 527 * 528 * @param int $loanid Loan id 529 * @param int $datemax Date max 530 * @return array Array of id 531 */ 532 public function paimenttorecord($loanid, $datemax) 533 { 534 535 $result = array(); 536 537 $sql = "SELECT p.rowid"; 538 $sql .= " FROM ".MAIN_DB_PREFIX.$this->table_element." as p "; 539 $sql .= " WHERE p.fk_loan = ".$loanid; 540 if (!empty($datemax)) { $sql .= " AND p.datep > '".$this->db->idate($datemax)."'"; } 541 $sql .= " AND p.datep <= '".$this->db->idate(dol_now())."'"; 542 543 $resql = $this->db->query($sql); 544 545 if ($resql) { 546 while ($obj = $this->db->fetch_object($resql)) 547 { 548 $result[] = $obj->rowid; 549 } 550 } 551 552 return $result; 553 } 554} 555