-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2018-01-03
-- Description:	Calculate the total amount paid by medical aid for the given invoice allocatedReference
-- =============================================
CREATE FUNCTION GetAccountPaidByMedicalAidAmount 
(
	@allocatedReference NVARCHAR(MAX)
)
RETURNS DECIMAL(18,2)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @medicalAidPaidAmount DECIMAL(18,2);

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

	SELECT @medicalAidPaidAmount = 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.procedureCode = 'M-EFT'
				 ), 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.procedureCode = 'M-EFT' ), 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 @medicalAidPaidAmount

END


