写一个MySql存储过程实现房贷等额本息还款计算(另外附javascript代码)

写一个MySql存储过程实现房贷等额本息还款计算

MySql存储过程代码如下：

DROP procedure IF EXISTS `calc_equal_interest_proc`;DELIMITER $$#USE `fn`$$CREATE PROCEDURE `calc_equal_interest_proc` (khbh varchar(50))LANGUAGE SQLNOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT '等额本息计算'BEGINdeclare 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);