银行贷款

USE [dyzgclw]
GO
/** Object: StoredProcedure [dbo].[Sp_GetLoanPlan] Script Date: 10/23/2021 14:29:58 **/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/
生成贷款计划 qzf 2018-2-8 11:41:25
—表变量结果
declare @msg varchar(50)
exec Sp_GetLoanPlan ‘D001’,’2018-01-26’,’等额本息’,70000,3.5,6,20,0,@msg out
print @msg
—临时表结果
if(object_id(‘tempdb..#LoanPlan’)>0) drop table #LoanPlan
create table #LoanPlan (
rid int,
LoanNo VARCHAR(50) NOT NULL,
LoanNoNum VARCHAR(50) NOT NULL DEFAULT ‘’,
PayDate datetime,
Debit numeric(24, 10) default 0 NOT NULL,
Credit numeric(24, 10) default 0 NOT NULL,
Principal numeric(24, 10) DEFAULT 0 NOT NULL,
Rate numeric(24, 10) default 0 NOT NULL,
Interest numeric(24, 10) default 0 NOT NULL,
Balance numeric(24, 10) DEFAULT 0 NOT NULL,
CurrentNum INT DEFAULT 0 NOT NULL
)
declare @msg varchar(50)
insert into #LoanPlan exec Sp_GetLoanPlan ‘D001’,’2018-02-08’,’等额本息’,60000,3.5,6,12,0,@msg out
print @msg
select sum(principal),sum(round(principal,2)),sum(debit),sum(interest),sum(debit-interest) from #LoanPlan

/
ALTER procedure [dbo].Sp_GetLoanPlan, —贷款单号
@loandate DATETIME, —贷款日期
@LoanWay varchar(20), —等额本息，等额本金
@LoanSum decimal(20,2), —贷款金额
@Rate numeric(24,10), —利率

@LoanMonths INT, —期数
@payday int, —还款日
@firstMonthPay int, —0：下月还款 1：当月还款，已过当月还款日，推到下月
@msg VARCHAR(200) out
)
AS
declare @ExistTranCount INT
DECLARE @LoanPlan TABLE(
rid numeric IDENTITY(1,1) ,
loanno VARCHAR(50) NOT NULL,
loannonum VARCHAR(50) NOT NULL DEFAULT ‘’,
paydate datetime,
debit numeric(24, 10) default 0 NOT NULL,
credit numeric(24, 10) default 0 NOT NULL,
principal numeric(24, 10) DEFAULT 0 NOT NULL,
rate numeric(24, 10) default 0 NOT NULL,
interest numeric(24, 10) default 0 NOT NULL,
balance numeric(24, 10) DEFAULT 0 NOT NULL,
currentnum INT DEFAULT 0 NOT NULL
)
——贷款计划
declare @MonthsRate numeric(24,10) —月利率
declare @Debit decimal(20,3) —应还
declare @Principal decimal(20,3) —本金
declare @Interest decimal(20,3) —利息
declare @Balance decimal(20,3) —剩余金额
declare @DebitL decimal(20,3) —保留两位小数
declare @DebitYu decimal(20,3) —保留两位小数
—-贷款
declare @LoanInterset numeric(24,10) — 利息总和
declare @q numeric(24,10) —利率 + 1
declare @xishu numeric(24,10) —用于计算第一次等额本息
declare @i int — 计次数

declare @hk_rq datetime
declare @yihuanbenjin decimal(20,3) —已还本金
begin
SET NOCOUNT ON
SET XACT_ABORT ON
if(isnull(@loanno,’’) = ‘’)
BEGIN
SET @msg = ‘贷款单号不能为空’
GOTO errCheck
END
if(isnull(@loandate,’’) = ‘’)
BEGIN
SET @msg = ‘贷款日期不能为空’
GOTO errCheck
END

if(isnull(@LoanWay,’’) = ‘’)
BEGIN
SET @msg = ‘贷款方式不能为空’
GOTO errCheck
END

if(isnull(@LoanSum,0) = 0)
BEGIN
SET @msg = ‘贷款金额不能为0’
GOTO errCheck
END
if(isnull(@Rate,0) = 0)
BEGIN
SET @msg = ‘贷款利率不能为0’
GOTO errCheck
END
if(isnull(@LoanMonths,0) = 0)
BEGIN
SET @msg = ‘贷款月数不能为0’
GOTO errCheck
END
if(isnull(@payday,0) = 0)
BEGIN
SET @msg = ‘还款日不能为0’
GOTO errCheck
END

select @ExistTranCount = @@trancount
if @ExistTranCount > 0
save transaction Tran_GetLoanPlan
else
BEGIN
begin transaction Tran_GetLoanPlan
end

set @rate = @rate / 100
set @Rate=round(@Rate1000/120.001,10,1) —liuliang2011-05-09利率为年利率，这里换算为月利率
select @hk_rq = cast( cast(DATEPART(year,@loandate) as varchar(4))+’-‘+cast(DATEPART(month,@loandate) as varchar(2))+’-‘+cast(@payday as varchar(2)) as datetime)
IF(@firstMonthPay = 0 OR
@firstMonthPay = 1 and @hk_rq < @loandate)
BEGIN
set @hk_rq = dateadd(month,1,@hk_rq)
END


if (@LoanWay = ‘等额本息’)
begin
select @q = (1+convert(numeric(24,10), @Rate)) — 月利率 +1



select @MonthsRate = round(@Rate,10,1) —月利率



select @xishu =round((@MonthsRate/(round(power((1+@MonthsRate),@LoanMonths),10,1) - 1)),10,1) —- 系数 power(X,Y）函数是计算X的Y次幂的！例如：power（2,5）= 32 @fk_nx 期数
—- @xishu 用于计算第一个月应该还多少本金
set @i=1


/
==================定额还款公式==========================
第一期本金 ＝ 贷款额×利率/[（1＋利率）×（1＋利率）..n.- 1 ]
第一期利息 ＝ 贷款额×利率
以后各期本金 ＝ 上期本金×（1＋利率）
以后各期利息 ＝ 剩余本金×利率
剩余本金的第一期金额 ＝ 贷款额
剩余本金以后各期金额 ＝ 贷款额 － 已还本金累计和（方法1）
剩余本金以后各期金额 ＝ 上期剩余本金金额 －上期本金金额（方法2）
(注意:小数都上截断)
/



if @i <= @LoanMonths
begin
select @Principal = @LoanSum @xishu — 贷款金额 系数 == 第一次的本金

select @Interest = @LoanSum @MonthsRate —贷款金额 月利率 == 贷款利息
select @Debit = @Principal + @Interest — 本金 + 贷款利息 == mqfkje 应还
select @Balance = @LoanSum



select @Balance = @Balance - @Principal — 剩余本金
——取两位小数
select @DebitL = @Debit 1000
select @DebitYu = @DebitL % 10
if (@DebitYu > 0)
BEGIN
select @DebitL = (@DebitL - @DebitL % 10 + 10 1.0)/1000
end
else
begin
select @DebitL = @DebitL 1.0 / 1000
end
——————————
——插入贷款计划
insert into @LoanPlan(LoanNo,LoanNoNum, PayDate, Debit, Principal, Rate, Interest, Balance,currentnum)
values(@loanno,@loanno +’-‘+ convert(VARCHAR,@i), @hk_rq, @DebitL, @Principal, @MonthsRate, @Interest, @Balance,@i)
if @@error > 0 goto errInsert
set @i = @i + 1
set @hk_rq=dateadd(mm,1,@hk_rq)——日期增加一个月

—第二月的本金和

if @i <= (@LoanMonths - 1)
begin
select @Principal = @Principal @q
select @Interest = @Balance @MonthsRate
select @Debit = @Principal + @Interest
select @Balance = @Balance - @Principal — 剩余本金
——取两位小数
select @DebitL = @Debit 1000
select @DebitYu = @DebitL % 10
if (@DebitYu > 0)
begin
select @DebitL = (@DebitL - @DebitL % 10 + 10 1.0)/1000
end
else
begin
select @DebitL = @DebitL 1.0 / 1000
end
——————————
——插入贷款计划
insert into @LoanPlan(LoanNo, LoanNoNum, PayDate, Debit, Principal, Rate, Interest, Balance,currentnum)
values(@loanno, @loanno +’-‘+ convert(VARCHAR,@i), @hk_rq, @DebitL, @Principal, @MonthsRate, @Interest, @Balance,@i)
if @@error > 0 goto errInsert
set @i = @i + 1
set @hk_rq=dateadd(mm,1,@hk_rq)——日期增加一个月


while @i<=(@LoanMonths-1) — @i = 3
begin
/
select @Principal = @Principal @q
select @Interest = @Balance @MonthsRate
select @Debit = @Principal + @Interest
select @Balance = @Balance - @Principal
/
—-博士的方式
select @Interest = @Balance @MonthsRate

select @Principal = @Debit - @Interest
select @Balance = @Balance - @Principal


——取两位小数
select @DebitL = @Debit 1000
select @DebitYu = @DebitL % 10
if (@DebitYu > 0)
begin
select @DebitL = (@DebitL - @DebitL % 10 + 10 1.0)/1000
end
else
begin
select @DebitL = @DebitL 1.0 / 1000
end
——————————

——插入贷款计划
insert into @LoanPlan(LoanNo,LoanNoNum, PayDate, Debit, Principal, Rate, Interest, Balance,currentnum)
values(@loanno,@loanno +’-‘+ convert(VARCHAR,@i), @hk_rq, @DebitL, @Principal, @MonthsRate, @Interest, @Balance,@i)
if @@error > 0 goto errInsert
set @hk_rq=dateadd(mm,1,@hk_rq)

set @i=@i+1


end
end
—最后一月
if @i = @LoanMonths
begin
select @yihuanbenjin = sum(principal) from @LoanPlan where LoanNo = @loanno
select @Principal = @LoanSum - @yihuanbenjin
select @Interest = @Principal @MonthsRate
select @Debit = @Principal + @Interest
select @Balance = @Balance - @Principal
——取两位小数
select @DebitL = @Debit 1000
select @DebitYu = @DebitL % 10
if (@DebitYu > 0)
begin
select @DebitL = (@DebitL - @DebitL % 10 + 10 1.0)/1000
end
else
begin
select @DebitL = @DebitL 1.0 / 1000
end
——————————

——插入贷款计划
insert into @LoanPlan(LoanNo,LoanNoNum, PayDate, Debit, Principal, Rate, Interest, Balance,currentnum)
values(@loanno, @loanno +’-‘+ convert(VARCHAR,@i), @hk_rq, @DebitL, @Principal, @MonthsRate, @Interest, @Balance,@i)
if @@error > 0 goto errInsert

end

end



end
else if (@LoanWay = ‘等额本金’)
begin
set @Principal = @LoanSum/@LoanMonths
set @i=1
select @Balance = @LoanSum
while @i<=(@LoanMonths-1)
begin
set @Balance=@Balance-@Principal
set @Debit = @Principal + @LoanSum @Rate
——取两位小数
select @DebitL = @Debit 1000
select @DebitYu = @DebitL % 10
if (@DebitYu > 0)
begin
select @DebitL = (@DebitL - @DebitL % 10 + 10 1.0)/1000
end
else
begin
select @DebitL = @DebitL 1.0 / 1000
end
——————————

——插入贷款计划
insert into @LoanPlan(LoanNo, PayDate, Debit, Principal, Rate, Interest, Balance,currentnum)
values(@loanno, @hk_rq, @DebitL, @Principal, @Rate, @LoanSum @Rate, @Balance,@i)
if @@error > 0 goto errInsert
—qzf 去掉set @LoanSum=@LoanSum-@Principal
set @hk_rq=dateadd(mm,1,@hk_rq)
set @i=@i+1


end

——最后一个月
set @Balance=@Balance-@Principal
set @Debit = @Principal + @LoanSum @Rate
——取两位小数
select @DebitL = @Debit 1000
select @DebitYu = @DebitL % 10
if (@DebitYu > 0)
begin
select @DebitL = (@DebitL - @DebitL % 10 + 10 1.0)/1000
end
else
begin
select @DebitL = @DebitL 1.0 / 1000
end
——————————

if @i=@LoanMonths
begin

insert into @LoanPlan(LoanNo, PayDate, Debit, Principal, Rate, Interest, Balance,currentnum)
values(@loanno, @hk_rq, @DebitL , @Principal, @Rate, @LoanSum @Rate, @Balance,@i)
if @@error > 0 goto errInsert
if @@error > 0 goto errInsert

end

end

—qzf 2018-2-9 11:18:59 本金，利息更新为 2位小数
DECLARE @diff NUMERIC(24,2)

UPDATE @LoanPlan SET Principal = ROUND(Principal,2)
UPDATE @LoanPlan SET Interest = debit - principal


SET @diff = @LoanSum - (SELECT SUM(Principal) FROM @LoanPlan)
PRINT @diff
if(@diff <> 0)
BEGIN
UPDATE @LoanPlan SET Principal = Principal + @diff,Interest = Interest - @diff WHERE CurrentNum = @LoanMonths

END


SELECT * FROM @LoanPlan
if @ExistTranCount = 0
BEGIN
commit tran Tran_GetLoanPlan
end
return 0
END
errCheck:
begin
return(2001)
end

errUpdate:
begin
ROLLBACK TRAN Tran_GetLoanPlan
set @Msg = ‘更新出错’
return(1001)
end

errInsert:
begin
ROLLBACK TRAN Tran_GetLoanPlan
set @Msg = ‘插入出错’
return(1002)
end

errDelete:
begin
ROLLBACK TRAN Tran_GetLoanPlan
set @Msg = ‘删除出错’
return(1003)
end
errNewList:
begin
ROLLBACK TRAN Tran_GetLoanPlan
set @Msg = ‘新建单号出错’
return(1004)
end

//调用过程——————————————————————
—获取还款计划
create table #LoanPlan (
rid int,
LoanNo VARCHAR(50) NOT NULL,
LoanNoNum VARCHAR(50) NOT NULL DEFAULT ‘’,
PayDate datetime,
Debit numeric(24, 10) default 0 NOT NULL,
Credit numeric(24, 10) default 0 NOT NULL,
Principal numeric(24, 10) DEFAULT 0 NOT NULL,
Rate numeric(24, 10) default 0 NOT NULL,
Interest numeric(24, 10) default 0 NOT NULL,
Balance numeric(24, 10) DEFAULT 0 NOT NULL,
CurrentNum INT DEFAULT 0 NOT NULL
)
insert into #LoanPlan exec Sp_GetLoanPlan @loanno,@loandate,@LoanWay,@LoanSum,@Rate,@LoanMonths,@payday,1,@msg out
IF(@@error <> 0 OR @msg <> ‘’) GOTO errProc