--Create proc SP_GenerateRentalAgreementDetail          
--AS           
--BEGIn          
        
Create table #HeaderId (Id int identity(1,1), HeaderId int,Frequency varchar(100))        
Insert into #HeaderId(HeaderId,Frequency)        
Select Distinct R.Id,R.Frequency From  tblRentalAgreement R /*LEFT JOIN tblRentalAgreementDetails RD ON RD.RentalAgreementId=R.Id        
Where RD.RentalAgreementId is null*/        
        
Declare @CountHeader int=1,@TotalHeader int,@HeaderId int,@Frequency varchar(100)        
Select @TotalHeader=Max(Id) from #HeaderId        
WHILE (@CountHeader<=@TotalHeader)        
BEGIN        
     Select @HeaderId= Headerid,@Frequency=Frequency From #HeaderId where Id=@CountHeader        
        
     Declare @FromMonth int,@FromYear int ,@ToMonth int,@ToMonthNew int,@ToYear int         
     ,@MonthDiff int,@YearDiff int,@Count int,@CurrentMonth int,@CurrentYear int ,@YearCount int =0       
            
     Select  /*Year(getdate())*/ @FromMonth=FromMonth,@FromYear=FromYear,@YearDiff=ToYear-FromYear, @ToMonth=ToMonth,        
     @ToYear=ToYear,@CurrentYear=@FromYear from tblRentalAgreement Where Id=@HeaderId         
          
     SET @ToMonthNew= CASE WHEN @FromYear=@ToYear THEN @ToMonth ELSE (12*@YearDiff)+@ToMonth END         
     SET @Count=  @FromMonth        
        
     WHILE (@Count<=@ToMonthNew)          
     BEGIn        
       Set @CurrentMonth=@Count        
       IF(@CurrentMonth>12)          
       BEGIn  
	      
       SET @CurrentYear=(@CurrentYear+(@CurrentMonth%12) ) 
	   SET @YearCount= CASE WHEN @CurrentMonth%12=0 THEN 0 ELSE  @YearCount+1   END 
	   SET @CurrentMonth= CASE WHEN @CurrentMonth%12=0 THEN 12 ELSE @CurrentMonth%12 END
	        
       END        
               
          
    IF (Upper(ISNULL(@Frequency,''))='M')     
    BEGIn      
	     Print cast(@HeaderId as varchar) + ' ,Month -' + cast(@CurrentMonth as varchar) +' year -' +cast(@CurrentYear as varchar)    
         --Insert into tblRentalAgreementDetails(RentalAgreementId,CurrentMonth,CurrentYear ,MonthlyRent,IsAssetCaptured,AssetCapturedBY          
         --,AssetCapturedOn ,AssetImagePath ,RouteCode ,CreatedOn ,CreatedBy ,ModifiedDate ,ModifiedTime,CurrentApproveLevel          
         -- ,CurrentApproveBy,ApprovedDate ,Status ,Remarks )          
                   
         Select distinct RH.ID RentalId,@CurrentMonth CurrentMonth,@CurrentYear CurrentYear,      
         CASE WHEN ISNULL(RH.Frequency,'')='Y' THEN RH.RentalCost      
         WHEN ISNULL(RH.Frequency,'')='Q' THEN RH.RentalCost*1.0/4      
         ELSE  RH.RentalCost*1.0/12 END Rent,0 IsAssetCaptured,'' AssetCapturedBY,          
         null AssetCapturedOn,'' AssetImagePath,ISNULL(UC.UserCode,C.RouteCode) RouteCode,getdate(),'KsaAdmin' CreatedBy,          
         VW.ModifiedDate,VW.ModifiedTime,0 CurrentApproveLevel,null CurrentApproveBy,null ApprovedDate,          
         null,null Remarks from tblRentalAgreement RH INNER JOIN tblCustomer C On C.Code=RH.CustomerCode          
         LEFT JOIN tblUserCustomer UC ON UC.CustomerCode=RH.CustomerCode          
         INNER JOIN vw_GetModifiedDateTime VW on 1=1           
         LEFT JOIN tblRentalAgreementDetails RD ON RD.RentalAgreementId=RH.ID          
         Where RD.RentalAgreementId is null and RH.Id=@HeaderId
		 and  Month(getdate())>=@CurrentMonth and @CurrentYear=year(getdate())
		  /*AND        
         ( @CurrentMonth between  RH.FromMonth and RH.ToMonth)  AND         
         (@CurrentYear between  RH.FromYear and RH.ToYear)*/          
       END      
    ELSE IF(UPPER(ISNULL(@Frequency,''))='Y')    
    BEGIn    
    --Insert into tblRentalAgreementDetails(RentalAgreementId,CurrentMonth,CurrentYear ,MonthlyRent,IsAssetCaptured,AssetCapturedBY          
    --     ,AssetCapturedOn ,AssetImagePath ,RouteCode ,CreatedOn ,CreatedBy ,ModifiedDate ,ModifiedTime,CurrentApproveLevel          
    --      ,CurrentApproveBy,ApprovedDate ,Status ,Remarks )          
                   
         Select distinct RH.ID RentalId,@CurrentMonth CurrentMonth,@CurrentYear CurrentYear,      
         CASE WHEN UPPER(ISNULL(RH.Frequency,''))='Y' THEN RH.RentalCost      
         WHEN UPPER(ISNULL(RH.Frequency,''))='Q' THEN RH.RentalCost*1.0/4      
         ELSE  RH.RentalCost*1.0/12 END Rent,0 IsAssetCaptured,'' AssetCapturedBY,          
         null AssetCapturedOn,'' AssetImagePath,ISNULL(UC.UserCode,C.RouteCode) RouteCode,getdate(),'KsaAdmin' CreatedBy,          
         VW.ModifiedDate,VW.ModifiedTime,0 CurrentApproveLevel,null CurrentApproveBy,null ApprovedDate,          
         null,null Remarks from tblRentalAgreement RH INNER JOIN tblCustomer C On C.Code=RH.CustomerCode          
         LEFT JOIN tblUserCustomer UC ON UC.CustomerCode=RH.CustomerCode          
         INNER JOIN vw_GetModifiedDateTime VW on 1=1           
         LEFT JOIN tblRentalAgreementDetails RD ON RD.RentalAgreementId=RH.ID          
         Where RD.RentalAgreementId is null AND  @CurrentYear=year(getdate())      
    END         
    
       SET @Count=@Count+1        
     END          
        
 SET @CountHeader=@CountHeader+1        
END        
Drop table #HeaderId        
          
--END