27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE FUNCTION fn_Return_Should_Pay_list(@now datetime)
RETURNS @Should_Pay_list TABLE(
Cr_ID int IDENTITY PRIMARY KEY, --主键
Cr_PL_ID int NOT NULL, --外键
Cr_Charge_date_should smalldatetime NOT NULL, --应付日期
CR_left_time varchar(20) NOT NULL, --倒计时
Cr_fee_should money NOT NULL --应付金额
)
AS
BEGIN
DECLARE @Pl_ID INT,@PL_CL_ID INT,@PL_FTL_ID INT,@PL_avr_fee MONEY,@PL_inure_date DATETIME,@PL_end_date DATETIME
--声明游标
declare Pact_list_Cursor CURSOR FOR SELECT Pl_ID,PL_CL_ID,PL_FTL_ID,PL_avr_fee,PL_inure_date,PL_end_date from T_pact_list where PL_active=1
--打开游标
Open Pact_list_Cursor
FETCH NEXT FROM Pact_list_Cursor INTO @Pl_ID,@PL_CL_ID,@PL_FTL_ID,@PL_avr_fee,@PL_inure_date,@PL_end_date
WHILE(@@fetch_status=0)
BEGIN
--获取提醒天数
declare @Forward_Pay_Day int
select @Forward_Pay_Day=CTL_Forward_Pay_Day from T_Client_Type_List where CTL_ID =(select CL_CLT_ID from T_Client_List where CL_ID=
(select PL_CL_ID from T_pact_list where PL_ID=@PL_ID))
--获取付款类型
declare @Pay_type_str varchar(10),@Pay_type int
select @Pay_type_str=FTL_name from T_Fee_type_list where FTL_ID=@PL_FTL_ID
If @Pay_type_str='月付' Set @Pay_type=1
If @Pay_type_str='季付' Set @Pay_type=3
If @Pay_type_str='半年付' Set @Pay_type=6
If @Pay_type_str='年付(送产权)' Set @Pay_type=12
If @Pay_type_str='年付' Set @Pay_type=12
If @Pay_type_str='一次性支付' Set @Pay_type=13
--获取周期付款金额
declare @avr_fee money
select @avr_fee=@PL_avr_fee
--获取该合同生效日期
declare @inure_date datetime,@should_pay_date datetime
Select @inure_date=@PL_inure_date
--获取合同终止日期
declare @end_date datetime
Select @end_date=@PL_end_date
--向表中插入数据
If @Pay_type<>13 --非一次性支付
BEGIN
If @inure_date<=dateadd(day,@Forward_Pay_Day,@now) --如果生效日期小于等于当前日期+提醒天数
BEGIN
Set @should_pay_date=@inure_date --第一次应付日期为合同生效日期
WHILE @should_pay_date<=dateadd(day,@Forward_Pay_Day,@now) and @should_pay_date<=@end_date --如果生效日期小于等于当前日期+提醒天数并且小于等于终止日期
BEGIN
IF not exists(SELECT * FROM T_Charge_Record where Cr_PL_ID=@PL_ID and Cr_Charge_date_should=@should_pay_date)
BEGIN
INSERT INTO @Should_Pay_list SELECT @PL_ID,@should_pay_date,dbo.fn_TwoDateDiff(@should_pay_date,@now),@avr_fee
END
Set @should_pay_date=dateadd(month,@Pay_type,@should_pay_date)
END
END
END
ELSE --一次性支付
Begin
IF not exists(SELECT * FROM T_Charge_Record where Cr_PL_ID=@PL_ID and Cr_Charge_date_should=@inure_date)
BEGIN
INSERT INTO @Should_Pay_list SELECT @PL_ID,@inure_date,dbo.fn_TwoDateDiff(@inure_date,@now),@avr_fee
END
End
END
--关闭游标
CLOSE Pact_list_Cursor
DEALLOCATE Pact_list_Cursor
RETURN
END
Rs.MoveNext
CREATE FUNCTION fn_Return_Should_Pay_list(@now datetime)
RETURNS @Should_Pay_list TABLE(
Cr_ID int IDENTITY PRIMARY KEY, --主键
Cr_PL_ID int NOT NULL, --外键
Cr_Charge_date_should smalldatetime NOT NULL, --应付日期
CR_left_time varchar(20) NOT NULL, --倒计时
Cr_fee_should money NOT NULL --应付金额
)
AS
BEGIN
DECLARE @Pl_ID INT,@PL_CL_ID INT,@PL_FTL_ID INT,@PL_avr_fee MONEY,@PL_inure_date DATETIME,@PL_end_date DATETIME
--声明游标
declare Pact_list_Cursor CURSOR FOR SELECT Pl_ID,PL_CL_ID,PL_FTL_ID,PL_avr_fee,PL_inure_date,PL_end_date from T_pact_list where PL_active=1
--打开游标
Open Pact_list_Cursor
FETCH NEXT FROM Pact_list_Cursor INTO @Pl_ID,@PL_CL_ID,@PL_FTL_ID,@PL_avr_fee,@PL_inure_date,@PL_end_date
WHILE(@@fetch_status=0)
BEGIN
--获取提醒天数
declare @Forward_Pay_Day int
select @Forward_Pay_Day=CTL_Forward_Pay_Day from T_Client_Type_List where CTL_ID =(select CL_CLT_ID from T_Client_List where CL_ID=
(select PL_CL_ID from T_pact_list where PL_ID=@PL_ID))
--获取付款类型
declare @Pay_type_str varchar(10),@Pay_type int
select @Pay_type_str=FTL_name from T_Fee_type_list where FTL_ID=@PL_FTL_ID
If @Pay_type_str='月付' Set @Pay_type=1
If @Pay_type_str='季付' Set @Pay_type=3
If @Pay_type_str='半年付' Set @Pay_type=6
If @Pay_type_str='年付(送产权)' Set @Pay_type=12
If @Pay_type_str='年付' Set @Pay_type=12
If @Pay_type_str='一次性支付' Set @Pay_type=13
--获取周期付款金额
declare @avr_fee money
select @avr_fee=@PL_avr_fee
--获取该合同生效日期
declare @inure_date datetime,@should_pay_date datetime
Select @inure_date=@PL_inure_date
--获取合同终止日期
declare @end_date datetime
Select @end_date=@PL_end_date
--向表中插入数据
If @Pay_type<>13 --非一次性支付
BEGIN
If @inure_date<=dateadd(day,@Forward_Pay_Day,@now) --如果生效日期小于等于当前日期+提醒天数
BEGIN
Set @should_pay_date=@inure_date --第一次应付日期为合同生效日期
WHILE @should_pay_date<=dateadd(day,@Forward_Pay_Day,@now) and @should_pay_date<=@end_date --如果生效日期小于等于当前日期+提醒天数并且小于等于终止日期
BEGIN
IF not exists(SELECT * FROM T_Charge_Record where Cr_PL_ID=@PL_ID and Cr_Charge_date_should=@should_pay_date)
BEGIN
INSERT INTO @Should_Pay_list SELECT @PL_ID,@should_pay_date,dbo.fn_TwoDateDiff(@should_pay_date,@now),@avr_fee
END
Set @should_pay_date=dateadd(month,@Pay_type,@should_pay_date)
END
END
END
ELSE --一次性支付
Begin
IF not exists(SELECT * FROM T_Charge_Record where Cr_PL_ID=@PL_ID and Cr_Charge_date_should=@inure_date)
BEGIN
INSERT INTO @Should_Pay_list SELECT @PL_ID,@inure_date,dbo.fn_TwoDateDiff(@inure_date,@now),@avr_fee
END
End
FETCH NEXT FROM Pact_list_Cursor INTO @Pl_ID,@PL_CL_ID,@PL_FTL_ID,@PL_avr_fee,@PL_inure_date,@PL_end_date
END
--关闭游标
CLOSE Pact_list_Cursor
DEALLOCATE Pact_list_Cursor
RETURN
END
FETCH NEXT FROM Pact_list_Cursor INTO @Pl_ID,@PL_CL_ID,@PL_FTL_ID,@PL_avr_fee,@PL_inure_date,@PL_end_date