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