-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2016-12-05
-- Description:	Returns the value paid to the assistant for a particular invoice modifier line for the current financial year
-- =============================================


CREATE FUNCTION [dbo].[GetAssistantAllocatedPaymentYTD] 
		(
			 @assistantRecId INT,
			 @year INT
		)
		RETURNS DECIMAL(18,2)
		AS
		BEGIN
			-- Declare the return variable here
			DECLARE @paidAssist DECIMAL(18,4);

			SELECT 
				@paidAssist = ISNULL((SELECT CONVERT(DECIMAL(18,2), SUM(split.PayAmt * (100 / (100 + acPmt.vatRate))))
					FROM pal_Account acPmt 
					INNER JOIN (
						SELECT CASE WHEN CHARINDEX(':', splitdata) > 0 THEN SUBSTRING(splitdata, 0, CHARINDEX(':', splitdata)) ELSE splitdata END AS PayId, 
						CASE WHEN CHARINDEX(':', splitdata) > 0 THEN SUBSTRING(splitdata, CHARINDEX(':', splitdata) + 1, len(splitdata) - CHARINDEX(':', splitdata) +1 ) ELSE ac.allocatedAssist END AS PayAmt
						FROM dbo.SplitString((ac.allocatedAssistReference), ',')) split 
					ON split.PayId = acPmt.recId 
					WHERE dbo.GetFiscalYear(acPmt.dateOfService) = @year), 0)
			FROM pal_Account ac
			WHERE ac.recId = @assistantRecId



			-- Return the result of the function
			RETURN @paidAssist


		END