-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2016-06-28
-- Description:	Returns a summary of the debtors assisted report
-- =============================================
-- Author: Charlene van Heerden
-- Create date: 2016-09-05 
-- Description: Add VAT columns
-- =============================================
CREATE PROCEDURE [dbo].[sp_DebtorsAssistedReportSummary] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT 
		CASE WHEN LTRIM(RTRIM(ac.modifier2)) = '' THEN LTRIM(RTRIM(ac.modifier3)) ELSE 
									  CASE WHEN LTRIM(RTRIM(ac.modifier3)) = '' THEN LTRIM(RTRIM(ac.modifier2)) 
											 ELSE LTRIM(RTRIM(ac.modifier3)) + ' - ' + LTRIM(RTRIM(ac.modifier2)) END END AS 'Assistant_Specialist',
		SUM(ISNULL(ac.amount,0) - ISNULL(ac.allocatedAssist,0)) AS DueToAssistantIncl, 
		CONVERT(DECIMAL(18,2), (SUM( (ISNULL(ac.amount,0) - ISNULL(ac.allocatedAssist,0)) - (ISNULL(ac.amount,0) - ISNULL(ac.allocatedAssist,0)) * (100 / (100 + ac.vatRate))) )) AS VAT,
		CONVERT(DECIMAL(18,2), (SUM( (ISNULL(ac.amount,0) - ISNULL(ac.allocatedAssist,0)) * (100 / (100 + ac.vatRate))  )) ) AS DueToAssistantExcl,
		CONVERT(DECIMAL(18,2), (SUM(CASE WHEN ISNULL(ag.[dTotal],0) <= 0 
			THEN (ISNULL(ac.amount,0) - ISNULL(ac.allocatedAssist,0)) * (100 / (100 + ac.vatRate)) 
			ELSE 0 END))) AS PayableToAssistant, 
		SUM(dbo.GetAssistantAllocatedPaymentYTD(ac.recId, dbo.GetFiscalYear(GETDATE()))) AS PaidYTD
	FROM pal_Account ac 
	LEFT OUTER JOIN pal_Ageing ag ON ag.patientNum = ac.patientNumber 
	WHERE ac.isVisible = 1 
	AND ac.modifier1 IN ('0008', '0009') 
	GROUP BY CASE WHEN LTRIM(RTRIM(ac.modifier2)) = '' THEN LTRIM(RTRIM(ac.modifier3)) ELSE 
									  CASE WHEN LTRIM(RTRIM(ac.modifier3)) = '' THEN LTRIM(RTRIM(ac.modifier2)) 
											 ELSE LTRIM(RTRIM(ac.modifier3)) + ' - ' + LTRIM(RTRIM(ac.modifier2)) END END
	HAVING SUM(ISNULL(ac.amount,0) - ISNULL(ac.allocatedAssist,0)) > 0 
		OR SUM(CASE WHEN ISNULL(ac.amount,0) = ISNULL(ac.allocated,0) AND ISNULL(ag.[dTotal],0) <= 0 THEN ISNULL(ac.amount,0) - ISNULL(ac.allocatedAssist,0) ELSE 0 END) > 0 

END



