写一个MySql存储过程实现房贷等额本息还款计算
MySql存储过程代码如下：
DROP procedure IF EXISTS `calc_equal_interest_proc`;
DELIMITER $$
#USE `fn`$$
CREATE PROCEDURE `calc_equal_interest_proc` (khbh varchar(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT '等额本息计算'
BEGIN
declare khf0059 varchar(100); #客户产品编号
declare khf0067 varchar(100); #产品产品模板编号
declare vtkf0007 datetime; #提款日期
declare cpf0026 decimal(18,8) default 0.0; #产品金额
declare cpf0027 int default 0; #产品周期
declare cpf0028 decimal(18,8) default 0.0; #产品总利息
declare cpf0029 decimal(18,8) default 0.0; #产品银行利息
declare cpf0030 decimal(18,8) default 0.0; #产品风险金池
declare cpf0031 decimal(18,8) default 0.0; #产品科技及获客
declare cpf0038 int default 0; #产品等额本息是否计算是否足月
declare hky datetime;
declare i int default 0;
declare corpusLeft decimal(18,8) default 0.0; #等额还款之本金
declare corpusLeftMon decimal(18,8) default 0.0; #等额还款之剩余本金
declare ratePow decimal(18,8) default 0.0; #每期贷款利率
declare capMon decimal(18,8) default 0.0; #月供
declare varerestMon decimal(18,8) default 0.0; #月供利息
declare corpusMon decimal(18,8) default 0.0; #月供本金
declare capTotle decimal(18,8) default 0.0; #还款总额
declare varerestTotle decimal(18,8) default 0.0; #利息总额
declare corpusTotle decimal(18,8) default 0.0; #利息总额
declare re int default 0;
#set cpf0026,cpf0027 = (select ifnull(f0026,0),ifnull(f0027,0) from t0003 where f0002=vtkf0003 limit 0,1);
select f0059,f0067 into khf0059,khf0067 from t0002 where f0027=khbh limit 0,1;
select ifnull(f0026,0),ifnull(f0027,0),ifnull(f0028,0),ifnull(f0029,0),ifnull(f0030,0),ifnull(f0031,0)
into cpf0026, cpf0027, cpf0028, cpf0029, cpf0030, cpf0031 from t0003 where f0002=(select f0059 from t0002 where f0027=khbh limit 0,1) limit 0,1;
set corpusLeft = cpf0026;
set corpusLeftMon = cpf0026;
set ratePow = (select pow(cpf0028/12+1,cpf0027)); #每期贷款利率
set hky = now();
while i < cpf0027 do
set capMon=(corpusLeft*cpf0028/12*ratePow)/(ratePow-1);#月供
set varerestMon=(select convert(corpusLeftMon*cpf0028/12,decimal(18,8)));#月供利息
set corpusMon=(select convert(capMon-varerestMon,decimal(18,8)));#月供本金
set corpusLeftMon=(select convert(corpusLeftMon-corpusMon,decimal(18,8)));#本金余额
set capTotle=capTotle + capMon; #还款总额
set varerestTotle=varerestTotle + varerestMon;#利息总额
set corpusTotle= corpusTotle + corpusMon;#本金总额
set hky = (select date_format(date_add(hky,interval 1 month), '%Y-%m-20'));
#select capMon, corpusMon, varerestMon;
#select corpusLeftMon, convert(capMon,decimal(18,2)), convert(varerestMon,decimal(18,2)), convert(corpusMon,decimal(18,2));
#select corpusLeftMon, capMon, varerestMon, corpusMon;
set hky = (select date_format(date_add(hky,interval 1 month), '%Y-%m-20'));
#select capMon, corpusMon, varerestMon;
#select corpusLeftMon, convert(capMon,decimal(18,2)), convert(varerestMon,decimal(18,2)), convert(corpusMon,decimal(18,2));
#select corpusLeftMon, capMon, varerestMon, corpusMon;
set re = (select count(1) from t0302 where f0003=khbh and F0006=date_format(hky, '%Y-%m-20'));
if re<=0 then
insert into t0302 (f0003, f0004, f0005, f0006, f0007, f0009, f0010, f0011, f0012, f0013)
values(khbh, khf0059, khf0067, date_format(hky, '%Y-%m-20'), convert(capMon,decimal(18,2)),
convert(corpusMon,decimal(18,2)), convert(varerestMon,decimal(18,2)), convert(varerestMon*cpf0029/cpf0028,decimal(18,2)),
convert(varerestMon*cpf0030/cpf0028,decimal(18,2)), convert(varerestMon*cpf0031/cpf0028,decimal(18,2)));
#ON DUPLICATE KEY UPDATE f0003=khbh and F0006=date_format(hky, '%Y-%m-20');
END IF;
end while;
END$$
DELIMITER ;
#select * from t0302;
#call `calc_equal_interest_proc`('C20170725002');
JavaScript代码如下：
function calculateR(rates,rateMon,corpus)//利率、期限、本金
{
var limitTime=rateMon;//贷款期限
var timeLeft=limitTime;
var corpusLeft=corpus;//等额还款之本金
var corpusLeftMon=corpus;//等额还款之剩余本金
var capMon,corpusMon,varerestMon;
var capTotle=0,corpusTotle=0,varerestTotle=0;
var sbResultR;
var rate=rates/100/12;
//sbResultR=createTable(sbResultR);
for(var i=1;i<limitTime;i++)
{
var ratePow=Math.pow(rate+1,timeLeft);//每期贷款利率
capMon=(corpusLeft*rate*ratePow)/(ratePow -1);//月供
varerestMon=corpusLeftMon*rate;//月供利息
corpusMon=capMon-varerestMon;//月供本金
corpusLeftMon-=corpusMon;//本金余额
capTotle+=capMon;//还款总额
varerestTotle+=varerestMon;//利息总额
corpusTotle+=corpusMon;//本金总额
//sbResultR=output(sbResultR,i,capMon,varerestMon,corpusMon,corpusLeftMon,rate*12*100);
}
///最后一期
varerestMon=corpusLeftMon*rate;//月供利息
corpusMon=corpusLeftMon;//月供本金
capMon=varerestMon + corpusLeftMon;//月供
corpusLeftMon-=corpusMon;//本金余额
capTotle+=capMon;//还款总额
varerestTotle+=varerestMon;//利息总额
corpusTotle+=corpusMon;//本金总额
//sbResultR=output(sbResultR,limitTime,capMon,varerestMon,corpusMon,corpusLeftMon,rate*12*100);
//sbResultR=output(sbResultR,9999,capTotle,varerestTotle,corpusTotle,corpusLeftMon,0);
//sbResultR=endTable(sbResultR);
//document.getElementById("res01_total").value = (Math.round(capTotle*100))/100;//还款总额
//document.getElementById("res01_ratetotal").value = (Math.round(varerestTotle*100))/100;//利息总额
//return sbResultR;
}
calculateR(15, 15, 1000000);