CREATE PROCEDURE [dbo].[sp_DebtorsAgeReportResult]
	-- Add the parameters for the stored procedure here
	@ServiceDate DateTime, 
	@BalanceFilter int	--1 = ALL  2 = Debit balances only 3 = Credit balances only 4 = All Accounts
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

-- Insert statements for procedure here
	IF OBJECT_ID('tempdb..#tmpTrans') IS NOT NULL DROP Table #tmpTrans
	IF OBJECT_ID('tempdb..#tmpAccount') IS NOT NULL DROP Table #tmpAccount  
	IF OBJECT_ID('tempdb..#Temp_Ageing') IS NOT NULL DROP Table #Temp_Ageing

  CREATE TABLE #Temp_Ageing(
	[pracNum] [int] NOT NULL,
	[patientNum] [int] NOT NULL,
	[current] [decimal](18, 2) NULL,
	[d30] [decimal](18, 2) NULL,
	[d60] [decimal](18, 2) NULL,
	[d90] [decimal](18, 2) NULL,
	[d120] [decimal](18, 2) NULL,
	[d150] [decimal](18, 2) NULL,
	[d180+] [decimal](18, 2) NULL,
	[dTotal] [decimal](18, 2) NULL,
	[medicalAid] [decimal](18, 2) NULL,
	[patient] [decimal](18, 2) NULL,
	[balanceFilter] [int] NULL,
	[invoiced] [decimal](18, 2) NULL,
	[Paid] [decimal](18, 2) NULL
)
	
	SET NOCOUNT ON;      
	DECLARE @BalancePerPractice DEC(18, 4) = 0       
	DECLARE @patientNumber int  
	DECLARE @PracNum int
	DECLARE @Current DEC(18, 4)      
	DECLARE @D30 DEC(18, 4)      
	DECLARE @D60 DEC(18, 4)      
	DECLARE @D90 DEC(18, 4)      
	DECLARE @D120 DEC(18, 4)      
	DECLARE @D150 DEC(18, 4)    
	DECLARE @D180 DEC(18, 4)    
	DECLARE @DTotal DEC(18, 4)      
	DECLARE @CRTotal DEC(18, 4)   
	DECLARE @INVTotal DEC(18, 4) 
	DECLARE @PTotal DEC(18, 4)     
	DECLARE @PatientLiable DEC(18, 4)      
	DECLARE @MedicalLiable DEC(18, 4)      
	DECLARE @CustomAccQuery varchar(MAX)  -- Custom Pat Query
	DECLARE @CustomTransQuery varchar(MAX) -- Custom Acc Query


      
	SET @DTotal = 0        
	SET @Current = 0        
	SET @D30 = 0        
	SET @D60 = 0        
	SET @D90 = 0        
	SET @D120 = 0        
	SET @D150 = 0      
	SET @D180 = 0      
	SET @DTotal = 0        
	SET @CRTotal = 0  
	SET @INVTotal = 0
	SET @PTotal = 0    
	SET @PatientLiable = 0    
	SET @MedicalLiable = 0    

	--create temp table of Account records
	SELECT patientNumber, SUM (amount)[Balance]
	INTO #tmpAccount
	FROM pal_Account
	WHERE dateOfService <= @ServiceDate 
	AND procedureType <> 'PA' AND procedureType <> 'CA'
	AND isVisible = 1
	Group By patientNumber
	ORDER By patientNumber  
	
	--Setup filter	
	IF @BalanceFilter = 1 -- Debits and Credits
		BEGIN  
			DECLARE AccCur CURSOR FOR
			SELECT patientNumber FROM #tmpAccount WHERE [Balance] <> 0
		END
	ELSE IF @BalanceFilter = 2 -- only accounts with a DEBIT balance
		BEGIN  
			DECLARE AccCur CURSOR FOR
			SELECT patientNumber FROM #tmpAccount WHERE [Balance] > 0
		END
	ELSE IF @BalanceFilter = 3 -- only accounts with a CREDIT balance
		BEGIN  
			DECLARE AccCur CURSOR FOR
			SELECT patientNumber FROM #tmpAccount WHERE [Balance] < 0
		END
	ELSE -- All including zero balances
		BEGIN
			DECLARE AccCur CURSOR FOR
			SELECT patientNumber FROM #tmpAccount
		END
		

OPEN AccCur      
FETCH NEXT FROM AccCur INTO @patientNumber     
    
WHILE @@FETCH_STATUS=0 BEGIN      
		--Create Temp Transaction Table
		select practiceNo, amount, patientNumber, liableMed, liablePat, allocated, procedureType, dateOfService, allocatedReference    
		into #tmpTrans from pal_Account
		Where patientNumber = @patientNumber 
		AND procedureType <> 'PA' AND procedureType <> 'CA'
			And isVisible = 1

	
		DECLARE TransacPerPracticeCurr CURSOR FOR 
		SELECT practiceNo, SUM(amount)
		FROM #tmpTrans
		WHERE #tmpTrans.patientNumber = @patientNumber	
		GROUP BY practiceNo, patientNumber
		ORDER BY practiceNo
	
	
	OPEN TransacPerPracticeCurr
	FETCH NEXT FROM TransacPerPracticeCurr INTO @PracNum, @BalancePerPractice
	WHILE @@FETCH_STATUS=0 BEGIN      
	         
	    SET @PatientLiable  = 0
	    SET @MedicalLiable = 0
		        
		SET @MedicalLiable  = 
			(SELECT SUM([liableMed]) 
			FROM #tmpTrans t 
			WHERE practiceNo = @PracNum AND t.patientNumber = @patientNumber
			AND t.dateOfService <=  @ServiceDate AND t.procedureType <> 'PM')

			

		SET @PatientLiable =
				(SELECT SUM([liablePat])
				FROM #tmpTrans t 
				WHERE practiceNo = @PracNum AND t.patientNumber = @patientNumber
				AND t.dateOfService <=  @ServiceDate AND t.procedureType <> 'PM')
		
		SELECT      
		
		@INVTotal =         
		SUM(CASE WHEN t.amount>0 AND t.procedureType <> 'PM'		    
		AND (dateOfService <= @ServiceDate)
		THEN (T.amount) ELSE 0 END), 
		
		@PTotal =         
		SUM(CASE WHEN t.amount<0 AND procedureType <> 'CN'  		    
		AND (dateOfService <= @ServiceDate) 
		THEN T.amount ELSE 0 END),

		@Current =         
		SUM(CASE WHEN t.amount>0 AND t.procedureType <> 'PM' AND procedureType <> 'CN'
		AND year(dateOfService) = year(@ServiceDate)     
		AND (dateOfService > DATEADD(DD,-30,@ServiceDate) AND dateOfService <= @ServiceDate)
		THEN (T.amount - dbo.GetAccountAllocatedAmountForDate(T.allocatedReference, @ServiceDate) ) ELSE 0 END),      

		@D30 =         
		SUM(CASE WHEN t.amount>0 AND t.procedureType <> 'PM' AND procedureType <> 'CN'          
		AND year(dateOfService) = year(DATEADD(mm,-1,@ServiceDate))     
		AND (dateOfService > DATEADD(DD,-60,@ServiceDate) AND dateOfService <= DATEADD(DD,-30,@ServiceDate))        
		THEN (T.amount - dbo.GetAccountAllocatedAmountForDate(T.allocatedReference, @ServiceDate) ) ELSE 0 END),        

		@D60 =         
		SUM(CASE WHEN t.amount>0   AND t.procedureType <> 'PM' AND procedureType <> 'CN'       
		AND year(dateOfService) = year(DATEADD(mm,-2,@ServiceDate))     
		AND (dateOfService > DATEADD(DD,-90,@ServiceDate) AND dateOfService <= DATEADD(DD,-60,@ServiceDate))         
		THEN (T.amount - dbo.GetAccountAllocatedAmountForDate(T.allocatedReference, @ServiceDate) ) ELSE 0 END),         

		@D90 =         
		SUM(CASE WHEN t.amount>0  AND t.procedureType <> 'PM' AND procedureType <> 'CN'       
		AND year(dateOfService) = year(DATEADD(mm,-3,@ServiceDate))     
		AND (dateOfService > DATEADD(DD,-120,@ServiceDate) AND dateOfService <= DATEADD(DD,-90,@ServiceDate))         
		THEN (T.amount - dbo.GetAccountAllocatedAmountForDate(T.allocatedReference, @ServiceDate) )  ELSE 0 END),         
		  
		@D120 =         
		SUM(CASE WHEN t.amount>0   AND t.procedureType <> 'PM' AND procedureType <> 'CN'       
		AND year(dateOfService) = year(DATEADD(mm,-4,@ServiceDate))     
		AND (dateOfService > DATEADD(DD,-150,@ServiceDate) AND dateOfService <= DATEADD(DD,-120,@ServiceDate))       
		THEN (T.amount - dbo.GetAccountAllocatedAmountForDate(T.allocatedReference, @ServiceDate) )  ELSE 0 END),        

		@D150 =         
		SUM(CASE WHEN t.amount>0   AND t.procedureType <> 'PM' AND procedureType <> 'CN'      
		AND year(dateOfService) = year(DATEADD(mm,-5,@ServiceDate))     
		AND (dateOfService > DATEADD(DD,-180,@ServiceDate) AND dateOfService <= DATEADD(DD,-150,@ServiceDate))      
		THEN (T.amount - dbo.GetAccountAllocatedAmountForDate(T.allocatedReference, @ServiceDate) )  ELSE 0 END),    
		  
		@D180 =         
		SUM(CASE WHEN t.amount>0  AND t.procedureType <> 'PM' AND procedureType <> 'CN'      
		AND (year(dateOfService) < year(DATEADD(mm,-6,@ServiceDate))     
		OR (year(dateOfService) = year(DATEADD(mm,-6,@ServiceDate))     
		AND (dateOfService <= DATEADD(DD,-180,@ServiceDate)))) 
		THEN (T.amount - dbo.GetAccountAllocatedAmountForDate(T.allocatedReference, @ServiceDate) ) ELSE 0 END),      

		@DTotal = 
		SUM(CASE WHEN (year(dateOfService) < year(@ServiceDate)     
		OR (year(dateOfService) = year(@ServiceDate)     
		AND month(dateOfService) <= month(@ServiceDate)))         
		THEN T.amount ELSE 0 END) ,  		

		@CRTotal =         
		(SUM(CASE WHEN ((t.amount<0  AND t.procedureType <> 'PM') OR (procedureType = 'PM'))  		    
		AND (year(dateOfService) < year(@ServiceDate)     
		OR (year(dateOfService) = year(@ServiceDate)     
		AND month(dateOfService) <= month(@ServiceDate))) 
		THEN T.amount ELSE 0 END * -1)
		-
		SUM(CASE WHEN  t.amount>0  AND t.procedureType <> 'PM' AND procedureType <> 'CN'
		AND (year(dateOfService) < year(@ServiceDate)     
		OR (year(dateOfService) = year(@ServiceDate)     
		AND month(dateOfService) <= month(@ServiceDate))) 
		THEN dbo.GetAccountAllocatedAmountForDate(T.allocatedReference, @ServiceDate)  ELSE 0 END))   


		FROM #tmpTrans T         
		WHERE t.patientNumber = @patientNumber   
		AND t.practiceNo = @PracNum		   
		AND t.dateOfService <=  @ServiceDate
				
		--if there is an unallocated payment amount, subtract it from the earliest month balance
		IF @CRTotal > 0      
		   BEGIN      
		   IF @D180 > 0        
		   IF @D180 <= @CRTotal        
		   BEGIN        
			SET @D180 = @CRTotal - @D180         
			SET @CRTotal = @D180        
			SET @D180 = 0        
		   END        
		   ELSE         
		   BEGIN        
			SET @D180 = @CRTotal - @D180         
			SET @CRTotal = @D180        
		   END        
		                  
		  IF @D180 <0        
		   BEGIN        
			SET @D180 = @D180 * -1        
			Goto Done        
		   END             
		  IF @D150 > 0        
		   IF @D150 <= @CRTotal        
		   BEGIN        
			SET @D150 = @CRTotal - @D150         
			SET @CRTotal = @D150        
			SET @D150 = 0        
		   END        
		   ELSE         
		   BEGIN        
			SET @D150 = @CRTotal - @D150         
			SET @CRTotal = @D150        
		   END        
		                  
		  IF @D150 <0        
		   BEGIN        
			SET @D150 = @D150 * -1        
			Goto Done        
		   END             
		  IF @D120 > 0        
			IF @D120 <= @CRTotal        
			 BEGIN          
			  SET @D120 = @CRTotal - @D120         
			  SET @CRTotal = @D120        
			  SET @D120 = 0        
			 END        
			 ELSE        
			 BEGIN        
			  SET @D120 = @CRTotal - @D120         
			  SET @CRTotal = @D120       
			END        
		                  
		  IF @D120 <0        
		   BEGIN        
			SET @D120 = @D120 * -1      
			Goto Done        
		   END        
		          
		                   
		  IF @D90 > 0        
		   IF @D90 <= @CRTotal        
		   BEGIN        
			SET @D90 = @CRTotal - @D90         
			SET @CRTotal = @D90        
			SET @D90 = 0        
		   END        
		   ELSE         
		   BEGIN        
			SET @D90 = @CRTotal - @D90         
			SET @CRTotal = @D90        
		   END        
		                  
		  IF @D90 <0        
		   BEGIN        
			SET @D90 = @D90 * -1        
			Goto Done        
		   END                     
		         
		  IF @D60>0        
		   IF @D60 <= @CRTotal        
		   BEGIN        
			SET @D60 = @CRTotal - @D60        
			SET @CRTotal = @D60        
			SET @D60 = 0        
		   END        
		   ELSE        
		   BEGIN        
			SET @D60 = @CRTotal - @D60        
			SET @CRTotal = @D60        
		   END        
		                  
		  IF @D60 <0        
		   BEGIN        
			SET @D60 = @D60 * -1        
			Goto Done        
		   END        
		                 
		                 
		  IF @D30 > 0        
		   IF @D30 <= @CRTotal        
		   BEGIN        
			SET @D30 = @CRTotal - @D30        
			SET @CRTotal = @D30        
			SET @D30 = 0        
		   END        
		   ELSE        
		   BEGIN        
			SET @D30 = @CRTotal - @D30        
			SET @CRTotal = @D30        
		   END        
		                  
		  IF @D30 <0        
		   BEGIN        
			SET @D30 = @D30 * -1        
			Goto Done        
		   END        
		                 
		  IF @Current > 0        
		   IF @Current <= @CRTotal        
		   BEGIN        
			Set @Current = @CRTotal - @Current        
			Set @CRTotal = @Current        
			SET @Current = 0        
		   END        
		   ELSE        
		   BEGIN           
			Set @Current = @CRTotal - @Current        
			Set @CRTotal = @Current        
		   END        
		                  
		  IF @Current <0        
		   BEGIN        
		   SET @Current = @Current * -1        
		   Goto Done        
		   END        
		  END         
		  IF @Dtotal - (@CRTotal * -1) = 0    
		   BEGIN    
			SET @Current = @DTotal    
			Goto Done    
		   END    
		      
		  IF @DTotal <0        
		   BEGIN        
			SET @Current = @DTotal        
		   END        
		     
		 ELSE    
		  IF @CRTotal < 0    
		   BEGIN    
			IF @Current <> 0    
			BEGIN    
			 SET @Current = @Current + (@CRTotal * -1)    
			 Goto Done    
			END    
			ELSE    
			 SET @Current = @CRTotal * -1    
			 Goto Done    
		   END      
		  ELSE    
		   BEGIN      
			Goto Done     
		 END   

		          
		          
		Done:    
			--Added Safety net to ensure we have matching balances
			IF @Current <> (@DTotal - @D30 - @D60 - @D90 - @D120 - @D150 - @D180)
			BEGIN
				SET @Current = (@DTotal - @D30 - @D60 - @D90 - @D120 - @D150 - @D180)
			END
			
				
				
		
				  
		  
		 INSERT INTO #Temp_Ageing    
		 SELECT @Pracnum AS PracNum, @patientNumber as [patientNum], @Current as [Current], @D30 as D30, 
			@D60 as D60, @D90 as D90, @D120 as D120,  @D150 as D150, @D180 as [D180+], @DTotal as DTotal, 
			@MedicalLiable as MedicalAid, @PatientLiable as Patient, @BalanceFilter as BalanceFilter, 
			@INVTotal as invoiced, @PTotal as paid   

		 SET @DTotal = 0        
		 SET @Current = 0        
		 SET @D30 = 0         
		 SET @D60 = 0         
		 SET @D90 = 0         
		 SET @D120 = 0         
		 SET @D150 = 0     
		 SET @D180 = 0     
		 SET @DTotal = 0      
		 SET @INVTotal = 0         
		 SET @PTotal = 0               
		 SET @PatientLiable = 0         
		 SET @MedicalLiable = 0     

	FETCH NEXT FROM TransacPerPracticeCurr INTO @PracNum, @BalancePerPractice
		
	END
	CLOSE TransacPerPracticeCurr
	DEALLOCATE TransacPerPracticeCurr
	Drop Table #tmpTrans
	
	FETCH NEXT FROM AccCur INTO @patientNumber     
       
END      
	CLOSE AccCur      
	DEALLOCATE AccCur      
	Drop Table #tmpAccount


	Select 
	ag.PatientNum,
	(Select Top 1 patientSurname FROM pal_Account Where patientNumber = ag.PatientNum ORDER By recId DESC) [Surname] ,
	(Select Top 1 patientName FROM pal_Account Where patientNumber = ag.PatientNum ORDER By recId DESC) [Name] , 
	
	
	(Select [name] from pal_MedicalScheme Where code = (Select surfaceFieldValueChar FROM [dbo].[pal_SurfaceFieldData] 
	where [surfaceFieldID] = (Select recId FROM pal_SurfaceField
								Where surfaceFieldName = 'PatientInformation_MedicalAidIfapplicable_MedicalAidSchemeName') 
	And surfaceItemID = (Select top 1 surfaceItemId FROM pal_Account Where patientNumber = ag.PatientNum)))[MedicalAid],

	(Select surfaceFieldValueChar FROM [dbo].[pal_SurfaceFieldData] 
	where [surfaceFieldID] = (Select recId FROM pal_SurfaceField
								Where surfaceFieldName = 'PatientInformation_MedicalAidIfapplicable_MembershipNumber')
								And surfaceItemID = (Select top 1 surfaceItemId FROM pal_Account Where patientNumber = ag.PatientNum)) [MedicalAidNumber],	 
	ag.patient [LiableP],
	ag.medicalAid [LiableS],
	(CASE WHEN (ag.[current] + ag.[d30] + ag.[d60] + ag.[d90] + ag.[d120] + ag.[d150] + ag.[d180+]) < (ag.patient + ag.medicalAid) 
		THEN  
			(ag.patient + ag.medicalAid) - (ag.[current] + ag.[d30] + ag.[d60] + ag.[d90] + ag.[d120] + ag.[d150] + ag.[d180+])
		ELSE 0 END) [Unallocated],
	ag.[invoiced] [Invoiced],
	ag.[paid] [Paid],
	ag.[dTotal] [Due],
	ag.[Current],
	ag.[d30],
	ag.[d60],
	ag.[d90],
	(ag.[d120] + ag.[d150] + ag.[d180+]) [d120],
	(Select Top 1 dateOfService FROM pal_Account Where patientNumber = ag.PatientNum and procedureType = 'TI' and isVisible = 1 and dateOfService <= @ServiceDate ORDER By dateOfService DESC)[LastInvoicedDate],
	(Select Top 1 dateOfService FROM pal_Account Where patientNumber = ag.PatientNum and procedureType = 'PM' and isVisible = 1 and dateOfService <= @ServiceDate ORDER By dateOfService DESC)[LastPaidDate]
	FROM #Temp_Ageing ag 
	ORDER BY ag.patientNum

END