做项目时经常碰到的业务:根据合同指定的起租日期和终止日期以及缴费方式,计算出两个日期之间的缴费记录,以便提醒缴费。
说明:
1、例子中的两个日期、缴费方式从合同中获取。
2、缴费方式主要有:月份、季付、半年付、年付,且以汉字的形式保存在数据库。
思路:根据终止日期和起租日期,计算出两个日期之间相差多少个月份,再根据缴费方式进行月份叠加,即
月付*1,季付*3,半年付*6,年付*12,如此计算出每个月缴费的日期以及下个月缴费的日期,再计算这两个日期之间的天数,最后乘以每天的房租费用,就可以得到每个月应该缴纳的总房租。
SQL如下:
CREATE PROCEDURE [dbo].[calcuteContractPay]
@id int --合同序号
AS
BEGIN
SET NOCOUNT ON;
--1、校验合同序号是否存在
if @id is null
begin
raiserror('必须告知合同序号',16,1);
return ;
end
--2、获取缴费方式
declare @field12 varchar(20);--缴费方式
declare @field39 datetime;--起租日期
declare @field40 datetime;--截止日期
declare @field66 numeric(18,2);--每天总租金
select @field12 = field12,@field39=field39,@field40=field40,@field66=isnull(field66,0) from table3 where field1=@id;
if @field12 is null or len(@field12)<=0
begin
raiserror('必须告知缴费方式',16,1);
return ;
end
--3、校验日期是否合法
declare @day int;--两者之间的天数
set @day = datediff(day,@field39,@field40);
if(@day<=0)
begin
raiserror('终止日期必须大于起租日期',16,1);
return ;
end
--4、根据缴费方式判断总共要输入多少条缴费记录
set @field12 = rtrim(ltrim(@field12));--去除空格
declare @monthes int;
declare @totalRent numeric(18,2);--总租金
declare @type int;--缴费类型
if(@field12='月付') set @type=1;
else if(@field12='季付') set @type=2;
else if(@field12='半年付') set @type=3;
else if(@field12='年付') set @type=4;
--4、1 计算开始日期与终止之间有多少个月份
set @monthes = datediff(month,@field39,@field40);--月份
if @monthes=0 --说明是在当月/当季/半年之内/当年起租、终止,那么只需要直接计算两者之间的天数
begin
set @totalRent = @day*@field66;--天数*每天的总价
begin
--添加缴费记录
insert into table6
select field1,field2,field4,field5,field12,field39,@totalRent,field42,field64,field11,0,NULL,0,@totalRent,@totalRent,0, field13,@day,field40
from table3 where field1=@id;
end
end
else --说明至少有一个月
begin
declare @startTime datetime;--开始日期
declare @endTime datetime;--结束日期
declare @num int;
set @num=0;
while(@num<=@monthes)
begin
--print @num;
set @startTime = dateadd(month,@num,@field39);--计算每个月应缴费日期
if(@type=1) set @num=@num+1;--月付
else if(@type=2) set @num=@num+3;--季付
else if(@type=3) set @num=@num+6;--半年付
else if(@type=4) set @num=@num+12;--年付
set @endTime = dateadd(month,@num,@field39);--下个月的缴费日期
--终止日期减去下一个月的日期,如果终止日期比下一个月的日期大,说明还需要再缴一个月,那么使用下一个月的日期,否则使用终止日期
set @day = datediff(day,@endTime,@field40);
if(@day<0) set @endTime=@field40;
--计算开始日期和结束日期之间的天数
set @day = datediff(day,@startTime,@endTime);
set @totalRent = @day*@field66;--天数*每天的总价
if(@totalRent>0)
begin
insert into table6
select field1,field2,field4,field5,field12,@startTime,@totalRent,field42,field64,field11,0,NULL,0,@totalRent,@totalRent,0, field13,@day,@endTime
from table3 where field1=@id;
end
end
end
END
GO