-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2016-06-29
-- Description:	Accepts a pal_Account.allocatedReference and returns the allocated amount up to the given date (used in Ageing report)
-- =============================================
CREATE FUNCTION [dbo].[GetAccountAllocatedAmountForDate] 
(
	@allocatedReference NVARCHAR(MAX), -- '1665:527.44,843:1462.90,845:729.15'
	@AgeDate DATE
)
RETURNS DECIMAL(18,4)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @allocatedAmount DECIMAL(18,4);

	DECLARE @Delimiter1 NVARCHAR(255) = ',', @Delimiter2 NVARCHAR(255) = ':'

	SELECT @allocatedAmount = SUM(t.AllocatedAmount) FROM (
			--select split string value, if it is integer, it's just a recId, not recId:allocatedAmount
		SELECT CASE WHEN TRY_CONVERT(int, y.i.value('(./text())[1]', 'nvarchar(4000)')) IS NULL 
			THEN 
			--partial amount allocated, if valid date, only allocate partial amount
				ISNULL((SELECT SUBSTRING(y.i.value('(./text())[1]', 'nvarchar(4000)'), CHARINDEX(@Delimiter2, y.i.value('(./text())[1]', 'nvarchar(4000)')) + 1, LEN(y.i.value('(./text())[1]', 'nvarchar(4000)')) - CHARINDEX(@Delimiter2, y.i.value('(./text())[1]', 'nvarchar(4000)')))
					FROM pal_Account a WHERE a.recId = SUBSTRING(y.i.value('(./text())[1]', 'nvarchar(4000)'), 0, CHARINDEX(@Delimiter2, y.i.value('(./text())[1]', 'nvarchar(4000)')))
					AND a.dateOfService <= @AgeDate
				 ), 0)
			ELSE 
			--full amount allocated, if valid date allocate full amount
				ISNULL((SELECT SUM(a.amount) FROM pal_Account a WHERE a.recId = CONVERT(int, y.i.value('(./text())[1]', 'nvarchar(4000)')) AND a.dateOfService <= @AgeDate ), 0)
			END AS AllocatedAmount
		FROM 
		( --split string
		SELECT x = CONVERT(XML, '<i>' 
			+ REPLACE(@allocatedReference, @Delimiter1, '</i><i>') 
			+ '</i>').query('.')
		) AS a CROSS APPLY x.nodes('i') AS y(i)
	) t

	-- Return the result of the function
	RETURN @allocatedAmount

END
