生成费用报表存储过程

 

/**//*
Add by litao 2006-6-20
功能：生成费用报表

*/


CREATE PROCEDURE  SP_CreateCharge
@ContractGuid varchar(50)
 AS


--declare @ContractGuid varchar(50)  --合同Guid 
declare @startDate datetime  --合同开始时间
declare @endDate datetime    --合同结束时间
declare @pMonth  int         --缴费周期

--set @ContractGuid='0e835c82-3543-4b7a-969d-a270c984d21f'


declare @TmpDate datetime
declare @TmpStartDate datetime
declare @TmpEndDate datetime

select @startDate=ContractStartDate,@endDate=ContractEndDate,@pMonth = PayCyc  from Contract_Info WHERE 1=1 and Guid=@ContractGuid

--select *   from Contract_Info WHERE 1=1 and Guid='0e835c82-3543-4b7a-969d-a270c984d21f'
--
--if @ContractStartDate >@endDate
--    goto HH
/**//*

--select ContractStartDate=@ContractStartDate
--select ContractEndDate=@ContractEndDate


if (@ContractStartDate<=@endDate and @ContractStartDate>=@startDate )
    set @startDate=@ContractStartDate

--if (@TmpEndDate>@endDate and @ContractStartDat<@startDate )

if (@ContractEndDate>@startDate and @ContractEndDate<@endDate )
    set @endDate=@ContractEndDate

--if (@startDate >@ContractEndDate)
--    goto HH

*/


--select @startDate

--select @endDate

--set @pMonth=3  

set @TmpDate=@startDate


    --select  @pMonth
    while @TmpDate<=@endDate
    begin
        
        set @TmpStartDate=@TmpDate
        set @TmpEndDate=dateadd(Month,@pMonth,@TmpStartDate)-1
        
        if ( @TmpEndDate<=@endDate    )    --整月
            begin
                     
                  
                
                insert into ChargeList (ContractGuid,ChargeYear,ChargeMonth,DateRange,ChargeDate,Rent,WYMoney,KTSBMoney,other1,other2,Derate,OweMoney,NextChargeDate)

                select 
                    
                    @ContractGuid as ContractGuid,
                    convert(varchar(4),@TmpStartDate,112) as ChargeYear ,
                    right(convert(varchar(6),@TmpStartDate,112),2) as ChargeMonth,
                    --convert(varchar(16),@TmpStartDate,111)+'->'+ convert(varchar(16),@TmpEndDate-1,111) as DateRange,
                    --right(convert(varchar(6),@TmpStartDate,112),2)+'->'+right(convert(varchar(6),@TmpEndDate,112),2) as DateRange,
                    right(convert(varchar(10),@TmpStartDate,111),10)+'-'+right(convert(varchar(10),@TmpEndDate,111),10) as DateRange,
                    convert(datetime,dateadd(Month,@pMonth,@TmpDate),112) as ChargeDate ,
                    租金=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,1,@ContractGuid,1),
                    物业费=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,2,@ContractGuid,1),
                    空调设备租用费=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,3,@ContractGuid,1),
                    其他1=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,4,@ContractGuid,1),
                    其他2=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,5,@ContractGuid,1),
                    减免费用=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,6,@ContractGuid,1),

                    OweMoney = SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,1,@ContractGuid,1) --租金
                        +  SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,2,@ContractGuid,1) --物业费
                        +  SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,3,@ContractGuid,1) -- 空调设备租用费
                        +  SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,4,@ContractGuid,1) --其他1
                        +  SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,5,@ContractGuid,1) --其他2
                        -  SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,6,@ContractGuid,1), --减免费用

                    NextChargeDate=dateadd(Month,@pMonth*2,@TmpDate)



                from dbo.Contract_Info  
                where guid=@ContractGuid
            end
        else if (@TmpStartDate<@endDate and @TmpEndDate>@endDate)        --不满整月
            begin 
                
                     insert into ChargeList (ContractGuid,ChargeYear,ChargeMonth,DateRange,ChargeDate,Rent,WYMoney,KTSBMoney,other1,other2,Derate,OweMoney,NextChargeDate)

                    select  
                    @ContractGuid as ContractGuid,
                    convert(varchar(4),@TmpStartDate,112),
                    right(convert(varchar(6),@TmpStartDate,112),2),
                    --convert(varchar(16),@TmpStartDate,111)+'->'+convert(varchar(16),@endDate-1,111),
                    --right(convert(varchar(6),@TmpStartDate,112),2)+'->'+right(convert(varchar(6),@TmpEndDate,112),2) as DateRange,
                    right(convert(varchar(10),@TmpStartDate,111),10)+'-'+right(convert(varchar(10),@endDate,111),10) as DateRange,
                    convert(datetime,dateadd(Month,@pMonth,@TmpDate),112) as ChargeDate ,
                    租金=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@endDate,1,@ContractGuid,1),
                    物业费=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@endDate,2,@ContractGuid,1),
                    空调设备租用费=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@endDate,3,@ContractGuid,1),
                    其他1=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@endDate,4,@ContractGuid,1),
                    其他2=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@endDate,5,@ContractGuid,1),
                    减免费用=SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@endDate,5,@ContractGuid,1),

                    OweMoney = SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,1,@ContractGuid,1) --租金
                        +  SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,2,@ContractGuid,1) --物业费
                        +  SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,3,@ContractGuid,1) -- 空调设备租用费
                        +  SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,4,@ContractGuid,1) --其他1
                        +  SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,5,@ContractGuid,1) --其他2
                        -  SSTTuser.sp_GetEachPartMoney(@TmpStartDate,@TmpEndDate,6,@ContractGuid,1), --减免费用

                    NextChargeDate=dateadd(Month,@pMonth * 2,@TmpDate)



                from dbo.Contract_Info  
                where guid=@ContractGuid
            end
        set @TmpDate=dateadd(Month,@pMonth,@TmpDate)

    end
GO

CREATE FUNCTION sp_GetEachPartMoney
(
@startDate datetime , 
@endDate datetime , 
@pType  int  , --1:租金，2：，3：121
@pGuid varchar(50),    --合同编号
--@IsCharge  int              --是否收取　
@pTT int 

)  


RETURNS decimal(38,2) AS  
BEGIN 
-------------------计算分类的金额--------
--set @startDate='2006/01/10'
--set @endDate='2006/3/10'
--set @pGuid='0e835c82-3543-4b7a-969d-a270c984d21f'
--set @pType=1
--declare @pTT int 
--set @pTT=0
---------
declare @Select_StartDate  datetime
declare @Select_EndDate  datetime
declare @MoneyTotal  decimal(38,2) 
set @MoneyTotal=0.00
----金额计算类型----------------------------------------------------------


--租金

if @pType=1
 begin 
  --
  select @Select_StartDate=FZWY_StartDate,@Select_EndDate=FZWY_EndDate from Contract_Info WHERE Guid=@pGuid 
 end

--物业费

if @pType=2
 begin 
  --
  select @Select_StartDate=FZWY_StartDate,@Select_EndDate=FZWY_EndDate from Contract_Info WHERE Guid=@pGuid 
 end 

--空调设备租用费

if @pType=3
 begin 
  --
  select @Select_StartDate=KTSB_StartDate,@Select_EndDate=KTSB_EndDate from Contract_Info WHERE Guid=@pGuid  and KTSB_IsCharge=1
 end

--其他1

if @pType=4
 begin 
  --
  select @Select_StartDate=Other1_StartDate,@Select_EndDate=Other1_EndDate from Contract_Info WHERE Guid=@pGuid and Other1_IsCharge=1
 end

--其他2

if @pType=5
 begin 
  --
  select @Select_StartDate=Other2_StartDate,@Select_EndDate=Other2_EndDate from Contract_Info WHERE Guid=@pGuid and Other2_IsCharge=1
 end


--减免费用

if @pType=6
 begin 
  --
  select @Select_StartDate=JM_StartDate,@Select_EndDate=JM_EndDate from Contract_Info WHERE Guid=@pGuid and JM_IsCharge=1
 end
 
----设置时间开始结束-------------------------------------------------------
-----2种情况


-- add by litao



if(@Select_StartDate  is null)
begin
 set @MoneyTotal=0
 goto HH
end



if (@startDate<=@Select_StartDate and @Select_StartDate< @endDate )
 set @startDate=@Select_StartDate

if (@startDate<@Select_EndDate and @Select_EndDate<@endDate )
 set @endDate=@Select_EndDate
-----额外情况
if @endDate<@Select_StartDate
begin
 set @MoneyTotal=0
 goto HH
end


if @Select_EndDate<@startDate
begin
 set @MoneyTotal=0
 goto HH
end
if (@startDate<@Select_StartDate and @Select_EndDate<@endDate)
 begin 
  set @startDate=@Select_StartDate
  set @endDate=@Select_EndDate
 end 
-----得出金额本周期内部的金额-----------------
declare @TmpDate datetime
declare @TmpStartDate datetime
declare @TmpEndDate datetime
set @TmpDate=@startDate
while @TmpDate<=@endDate
 begin
  
  set @TmpStartDate=@TmpDate
  set @TmpEndDate=dateadd(Month,1,@TmpStartDate)-1
   if (@TmpEndDate<=@endDate)
    begin 

    
     --租金
     if @pType=1
      select @MoneyTotal=@MoneyTotal+FZWY_FZMonthCharge from  Contract_Info WHERE Guid=@pGuid 

   --物业费

     if @pType=2
      select @MoneyTotal=@MoneyTotal+FZWY_WYMonthCharge from  Contract_Info WHERE Guid=@pGuid 

    --空调设备租用费

     if @pType=3
      select @MoneyTotal=@MoneyTotal+KTSB_MonthCharge from  Contract_Info WHERE Guid=@pGuid 


      --其他1

     if @pType=4
      select @MoneyTotal=@MoneyTotal+Other1_MonthCharge from  Contract_Info WHERE Guid=@pGuid 


       --其他2

     if @pType=5
      select @MoneyTotal=@MoneyTotal+Other2_MonthCharge from  Contract_Info WHERE Guid=@pGuid 

       --减免费用

     if @pType=6
      select @MoneyTotal=@MoneyTotal+JM_MonthCharge from  Contract_Info WHERE Guid=@pGuid 
    
    end
   else if (@TmpStartDate<@endDate and @TmpEndDate>@endDate) 
    begin 
     if @pType=1
      select @MoneyTotal=@MoneyTotal+FZWY_FZMonthCharge/30*(datediff(day,@TmpStartDate,@endDate) + @pTT) from  Contract_Info WHERE Guid=@pGuid 
     if @pType=2
      select @MoneyTotal=@MoneyTotal+FZWY_WYMonthCharge/30*(datediff(day,@TmpStartDate,@endDate) +@pTT ) from  Contract_Info WHERE Guid=@pGuid 
     if @pType=3
      select @MoneyTotal=@MoneyTotal+KTSB_MonthCharge/30*(datediff(day,@TmpStartDate,@endDate) + @pTT) from  Contract_Info WHERE Guid=@pGuid 
      if @pType=4      select @MoneyTotal=@MoneyTotal+Other1_MonthCharge/30*(datediff(day,@TmpStartDate,@endDate) +@pTT) from  Contract_Info WHERE Guid=@pGuid 

      if @pType=5
      select @MoneyTotal=@MoneyTotal+Other2_MonthCharge/30*(datediff(day,@TmpStartDate,@endDate) +@pTT) from  Contract_Info WHERE Guid=@pGuid 

      if @pType=6
      select @MoneyTotal=@MoneyTotal+JM_MonthCharge/30*(datediff(day,@TmpStartDate,@endDate) +@pTT) from  Contract_Info WHERE Guid=@pGuid 
     
     
    
    end
  set @TmpDate=dateadd(Month,1,@TmpDate)
 
 end 
 
HH:  
return  @MoneyTotal
END