sql

USE [culcleasing]
GO
/****** Object:  StoredProcedure [dbo].[report_month_plan_accrued_UnMonth]    Script Date: 07/09/2018 23:12:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






/*
*利息计提拆分数据，作为代理作业，每月9号零晨执行，针对非月付
*By Jaffe 2012-6-28
*/
ALTER PROCEDURE [dbo].[report_month_plan_accrued_UnMonth]
AS
set ansi_warnings OFF--去除警告
 
--0.先删除历史数据  select * from report_month_plan_accrued_temp
Delete from report_month_plan_accrued_temp
WHERE
Cast(CONVERT(varchar(7),invyear_month,120)+'-01' AS datetime)>=
Cast(CONVERT(varchar(7),getdate(),120)+'-01' AS datetime)
AND Begin_id IN(
    SELECT begin_id FROM begin_info
    WHERE income_number_year>1    
)

--1.先查询合同集合
Declare @begin_id varchar(50)
Declare @period_type int
Declare @income_number int

Declare conid_cursor cursor
For

Select Distinct frp.begin_id,period_type,income_number_year
from fund_rent_plan frp
left join begin_info bi ON frp.begin_id=bi.begin_id
WHERE frp.plan_date>=cast
(CONVERT(varchar(7),getdate(),120)+'-01' AS datetime)
AND frp.Begin_id IN(
    SELECT begin_id FROM begin_info
    WHERE income_number_year>1    
)

--使用游标
Open conid_cursor
Fetch next from conid_cursor into @begin_id,@period_type,@income_number
while @@fetch_status=0
begin


Declare @plan_date2 varchar(50)
Declare id_cursor cursor/*定义游标*/  
For

Select distinct convert(varchar(7),plan_date,120) from fund_rent_plan where
plan_date>=cast(CONVERT(varchar(7),getdate(),120)+'-01' AS datetime)
and plan_date>=(select CONVERT(varchar(100), dateadd(mm,1,rent_start_date), 23) from begin_info where
    begin_id=@begin_id)
and plan_date<=(
    --select dateadd(mm,@period_type,max(plan_date))
    select max(plan_date)
    from fund_rent_plan where begin_id=@begin_id)

open id_cursor            /*打开游标*/
fetch next from id_cursor into @plan_date2   /*读出一条游标*/
while @@fetch_status=0    /*使用函数@@fetch_status函数报告上一个fetch语句的状态*/
begin
    
Insert into report_month_plan_accrued_temp(
      contract_id,begin_id,cust_name,project_name,plan_list,interest,invyear_month,detail_time)
select  contract_id,begin_id,cust_name,project_name,rent_list,interest,invyear_month,detail_time
from (
    
SELECT
 frp.[contract_id],frp.begin_id,vca.cust_name,project_name,
 (select fp.rent_list from fund_rent_plan fp
left join (select fp1.id,fp1.rent_list,fp1.contract_id, fp1.begin_id,
case isnull(fp2.plan_date,0) when 0  then DATEDIFF(mm,bi.start_date,fp1.plan_date)
else DATEDIFF(mm,fp2.plan_date,fp1.plan_date)end month_cha from fund_rent_plan  fp1
left join fund_rent_plan  fp2 on fp1.begin_id=fp2.begin_id  and fp2.rent_list=fp1.rent_list-1
left join begin_info  bi on fp1.begin_id=bi.begin_id) vi on vi.id=fp.id
where plan_date = (
select  min(plan_date)   from fund_rent_plan where   convert(varchar(7),plan_date,120) >=@plan_date2 and begin_id =@begin_id
)
and fp.begin_id=@begin_id
) as rent_list,    
    (select
    case when vi.month_cha=0 then'0'
    else fp.interest/ vi.month_cha end from fund_rent_plan fp
left join (select fp1.id,fp1.rent_list,fp1.contract_id, fp1.begin_id,
case isnull(fp2.plan_date,0) when 0  then DATEDIFF(mm,bi.rent_start_date,fp1.plan_date)
else DATEDIFF(mm,fp2.plan_date,fp1.plan_date)end month_cha from fund_rent_plan  fp1
left join fund_rent_plan  fp2 on fp1.begin_id=fp2.begin_id  and fp2.rent_list=fp1.rent_list-1
left join begin_info  bi on fp1.begin_id=bi.begin_id) vi on vi.id=fp.id
where plan_date = (
select  min(plan_date)   from fund_rent_plan where   convert(varchar(7),plan_date,120) >=@plan_date2 and begin_id =@begin_id

)
and fp.begin_id=@begin_id
) as interest,

    getdate() detail_time,
    @plan_date2+'-14' invyear_month
    FROM fund_rent_plan frp left join begin_info bi
    on frp.begin_id=bi.begin_id    
    Inner join
    (select begin_id,min(rent_list) rent_list from fund_rent_plan where
    plan_date>= convert(datetime, @plan_date2+'-01')
    group by begin_id) a on a.begin_id=frp.begin_id and a.rent_list=frp.rent_list  
    left join contract_info ci on frp.contract_id=ci.contract_id  left join
    vi_cust_all_info vca on ci.cust_id=vca.cust_id where frp.begin_id=@begin_id

) c


print @begin_id+'--'+@plan_date2
fetch next from id_cursor into @plan_date2
 /*读取一条游标*/

end
close id_cursor   /*关闭游标*/
deallocate id_cursor /*删除游标*/


fetch next from conid_cursor into @begin_id,@period_type,@income_number

end
close conid_cursor
deallocate conid_cursor