-- =============================================
-- Author: Graham Rook
-- Create date: 2016-02-24
-- Description:	Return Debtors Assisted Report Result Table
-- =============================================
-- Author: Charlene van Heerden
-- Modified date: 2016-07-06
-- Description:	Modified to include Assitant and Specialist Name
-- =============================================
CREATE PROCEDURE [dbo].[sp_DebtorsAssistedReportResult]
	-- 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;

	/*	CVH 2016-06-28 Need to include Assistant / Specialist name. Result must contain 1 line per patient 

	Select ag.PatientNum,
	(Select Top 1 name + ' ' + surname FROM pal_Account Where patientNumber = ag.PatientNum ORDER By recId DESC) [MainMember] , 	
	ag.[dTotal] [Due],
	ISNULL((Select IsNull(SUM(amount - ISNull(allocatedAssist,0)), 0) FROM pal_Account Where (modifier1 = '0009' OR modifier2 = '0009' OR modifier3 = '0009' OR modifier4 = '0009') AND patientNumber = ag.PatientNum AND isVisible = 1), 0) [DueAssistant],
	ISNULL((Select IsNull(SUM(amount - ISNull(allocatedAssist,0)), 0) FROM pal_Account Where (modifier1 = '0009' OR modifier2 = '0009' OR modifier3 = '0009' OR modifier4 = '0009') AND patientNumber = ag.PatientNum and amount = allocated AND isVisible = 1 AND ag.[dTotal] <= 0), 0) [PayAssistant],
	ISNULL((Select IsNull(SUM(amount - ISNull(allocatedAssist,0)), 0) FROM pal_Account Where (modifier1 = '0008' OR modifier2 = '0008' OR modifier3 = '0008' OR modifier4 = '0008') AND patientNumber = ag.PatientNum AND isVisible = 1), 0) [DueSpecialist],
	ISNULL((Select IsNull(SUM(amount - ISNull(allocatedAssist,0)), 0) FROM pal_Account Where (modifier1 = '0008' OR modifier2 = '0008' OR modifier3 = '0008' OR modifier4 = '0008') AND patientNumber = ag.PatientNum and amount = allocated AND isVisible = 1 AND ag.[dTotal] <= 0), 0) [PaySpecialist]
	FROM pal_Ageing ag
	WHERE (ISNULL((Select IsNull(SUM(amount - ISNull(allocatedAssist,0)), 0) FROM pal_Account Where (modifier1 = '0009' OR modifier2 = '0009' OR modifier3 = '0009' OR modifier4 = '0009') AND patientNumber = ag.PatientNum AND isVisible = 1), 0) > 0 OR 
	
	ISNULL((Select IsNull(SUM(amount - ISNull(allocatedAssist,0)), 0) FROM pal_Account Where (modifier1 = '0008' OR modifier2 = '0008' OR modifier3 = '0008' OR modifier4 = '0008') AND patientNumber = ag.PatientNum AND isVisible = 1), 0)> 0)
	*/

	SELECT t.PatientNum, t.MainMember, t.Due, SUM(t.DueAssistant) DueToAssistant, SUM(t.PayAssistant) PayableToAssistant, 
--there will always be only one assistant and one specialist per patient, but the system allows one to capture multiples
--need to cater for multiples, but still show 1 line per patient. If there are multiples, show the text 'Multiple' instead of assistant name
	CASE WHEN ISNULL((SELECT COUNT(DISTINCT( LTRIM(RTRIM(assist.modifier2)) + LTRIM(RTRIM(assist.modifier3)) ))
			FROM pal_Account assist WHERE assist.patientNumber = t.PatientNum AND assist.modifier1 = '0009' 
			AND (assist.modifier2 <> '' OR assist.modifier3 <> '') AND assist.isVisible = 1 
			AND ISNULL(assist.amount,0) - ISNULL(assist.allocatedAssist,0) > 0), '') > 1 THEN 'Multiple' ELSE
		ISNULL((SELECT TOP 1 CASE WHEN LTRIM(RTRIM(assist.modifier2)) = '' THEN LTRIM(RTRIM(assist.modifier3)) ELSE 
					CASE WHEN LTRIM(RTRIM(assist.modifier3)) = '' THEN LTRIM(RTRIM(assist.modifier2)) 
						ELSE LTRIM(RTRIM(assist.modifier3)) + ' - ' + LTRIM(RTRIM(assist.modifier2)) END END 
				FROM pal_Account assist WHERE assist.patientNumber = t.PatientNum AND assist.modifier1 = '0009' 
				AND (assist.modifier2 <> '' OR assist.modifier3 <> '') AND assist.isVisible = 1 
				AND ISNULL(assist.amount,0) - ISNULL(assist.allocatedAssist,0) > 0), '') END AS Assistant,
	SUM(t.DueSpecialist) DueToSpecialist, SUM(t.PaySpecialist) PayableToSpecialist,	
	CASE WHEN ISNULL((SELECT COUNT(DISTINCT( LTRIM(RTRIM(spec.modifier2)) + LTRIM(RTRIM(spec.modifier3)) ))
			FROM pal_Account spec WHERE spec.patientNumber = t.PatientNum AND spec.modifier1 = '0008' 
			AND (spec.modifier2 <> '' OR spec.modifier3 <> '') AND spec.isVisible = 1 
			AND ISNULL(spec.amount,0) - ISNULL(spec.allocatedAssist,0) > 0), '') > 1 THEN 'Multiple' ELSE
		ISNULL((SELECT TOP 1 CASE WHEN LTRIM(RTRIM(spec.modifier2)) = '' THEN LTRIM(RTRIM(spec.modifier3)) ELSE 
					CASE WHEN LTRIM(RTRIM(spec.modifier3)) = '' THEN LTRIM(RTRIM(spec.modifier2)) 
						ELSE LTRIM(RTRIM(spec.modifier3)) + ' - ' + LTRIM(RTRIM(spec.modifier2)) END END 
				FROM pal_Account spec WHERE spec.patientNumber = t.PatientNum AND spec.modifier1 = '0008' 
				AND (spec.modifier2 <> '' OR spec.modifier3 <> '') AND spec.isVisible = 1 
				AND ISNULL(spec.amount,0) - ISNULL(spec.allocatedAssist,0) > 0), '') END AS Specialist
	FROM (
	--get all assistant data to aggregate
		SELECT ac.patientNumber AS PatientNum, ac.name + ' ' + ac.surname AS [MainMember],
			ISNULL(ag.[dTotal],0) [Due], 
			CASE WHEN ac.modifier1 = '0009' THEN ISNULL(ac.amount,0) - ISNULL(ac.allocatedAssist,0) ELSE 0 END AS DueAssistant, 
			CASE WHEN ac.modifier1 = '0009' AND 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 AS PayAssistant,
			CASE WHEN ac.modifier1 = '0008' THEN ISNULL(ac.amount,0) - ISNULL(ac.allocatedAssist,0) ELSE 0 END AS DueSpecialist,
			CASE WHEN ac.modifier1 = '0008' AND 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 AS PaySpecialist
		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') 
	) t
	GROUP BY t.PatientNum, t.MainMember, t.Due
	HAVING SUM(t.DueAssistant) > 0 OR SUM(t.DueSpecialist) > 0

END
